-- View: "v_stock_list"

DROP VIEW "v_stock_list";

CREATE OR REPLACE VIEW "v_stock_list"
AS
SELECT
    s.itemcd,
    i.itemnm,
    s.structcd,
    s.stockcd,
    s.sellercd,
    case
        when m.nicknm IS NULL or m.nicknm='' then m.lastnm || ' ' || m.firstnm
        when m.nicknm IS NOT NULL then m.nicknm
    end as sellernm,
	m.memberstate,
    COALESCE(se.takkyubinprc,0) as takkyubinprc,
    se.news,
    COALESCE(se.sellersouryou,0) as sellersouryou,
    COALESCE(se.sellersouryoumax,0) as sellersouryoumax,
    s.stocktitlecd,
    t.orderno,
    t.orderdate,
    t.suppliercd,
    t.supplidiv,
    n1.name AS supplidivnm,
    so.exhibitcount,
    s.condition,
    n2.name AS conditionnm,
    s.sellingprice,
    s.souryouprc,
    s.stockstate,
    n3.name AS stockstatenm,
    s.scdstartdate,
    s.scdcompdate,
    s.itemexp,
    s.rackid,
    s.managecd,
    s.costprice,
    s.ntcostprice,
    s.consignment,
    s.arrivaldate,
    s.upddate,
    s.updcnt,
    case
	    when (s.stockstate=1
                  and (w.releasedate <= CURRENT_DATE
                  and st.salestartdate > CURRENT_DATE
                  and st.orderclosedate >= CURRENT_DATE)) then 1
	    else  0
	end as reserveflg,
	st.salestartdate,
	st.orderclosedate,
	w.releasedate

FROM
    t_ex_stock_detail s
    LEFT JOIN t_member_basic_attr m
        ON m.membercd = s.sellercd
        AND m.delflg = FALSE
    LEFT JOIN t_seller_attr se
        ON se.membercd = s.sellercd
        AND se.delflg = FALSE
    LEFT JOIN t_stock_title t
        ON t.stocktitlecd = s.stocktitlecd
        AND t.delflg = FALSE
    LEFT JOIN t_name n1
        ON n1.namecd = t.supplidiv
        AND n1.namediv = 8  -- 仕入区分
    LEFT JOIN t_name n2
        ON n2.namecd = s.condition
        AND n2.namediv = 9  -- コンディション
    LEFT JOIN t_name n3
        ON n3.namecd = s.stockstate
        AND n3.namediv = 10  -- 在庫状態
    INNER JOIN t_item_basic_attr i
        ON i.itemcd = s.itemcd
        AND i.delflg = FALSE 
    INNER JOIN t_item_struct st
    	ON st.itemcd=s.itemcd
	    AND st.structcd=s.structcd
	    AND st.delflg =FALSE
	INNER JOIN t_item_web_attr w
	    ON w.itemcd=s.itemcd
	    AND w.delflg =FALSE
	INNER JOIN v_stock_order so
        ON so.itemcd = s.itemcd
        AND so.structcd = s.structcd
        AND so.stockcd = s.stockcd
WHERE
    s.delflg = FALSE
	AND w.releasedate <= CURRENT_DATE
    AND COALESCE(se.itemexhibitstop, FALSE) = FALSE
    AND ((s.sellercd IS NOT NULL AND m.memberstate=1) OR (s.sellercd=9999) OR (s.sellercd IS NULL))
;