-- View: "v_stock_stocktitle_list"

DROP VIEW "v_stock_stocktitle_list";

CREATE OR REPLACE VIEW "v_stock_stocktitle_list"
AS
SELECT DISTINCT on (s.stocktitlecd, s.itemcd, s.structcd)
    s.itemcd,
    i.itemnm,
    s.structcd,
    s.stockcd,
    s.sellercd,
    s.stocktitlecd,
    o.itemcd AS orderitemcd,
    o.structcd AS orderstructcd,
    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.updcnt,
    COALESCE(s_cnt.stocktitlecdcont, 0) as stocktitlecdcont
    FROM
    t_ex_stock_detail s
    LEFT JOIN t_order_detail o
        ON o.itemcd = s.itemcd
        AND o.structcd = s.structcd
        AND o.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 v_stock_order so
        ON so.itemcd = s.itemcd
        AND so.structcd = s.structcd
        AND so.stockcd = s.stockcd
    LEFT JOIN (SELECT t_s.itemcd, t_s.structcd, t_s.stocktitlecd, count(t_s.itemcd) as stocktitlecdcont
        FROM t_ex_stock_detail t_s
        left join
          (select * from t_order_detail t_o
              left join t_forward_detail t_f
               on t_f.orderno=t_o.orderno
                   and t_f.orderrowno=t_o.orderrowno
             where t_f.forwardstate is null
                       or not (t_f.forwardstate is not null
                       and (t_f.forwardstate=9
                       or (t_f.forwardstate=8 and t_f.returndate is not null)))
          ) t_o
          on t_o.itemcd = t_s.itemcd AND t_o.structcd = t_s.structcd and t_s.stockcd = t_o.stockcd
        WHERE t_o.itemcd IS NULL
        AND   t_s.stockstate = 3
        AND   (t_s.sellercd IS NULL OR t_s.sellercd = 9999)
        AND   t_s.delflg = false
        GROUP BY t_s.itemcd, t_s.structcd, t_s.stocktitlecd
        ) AS s_cnt
        ON s_cnt.itemcd = s.itemcd
        AND s_cnt.structcd = s.structcd
        AND s_cnt.stocktitlecd = s.stocktitlecd
WHERE
    s.delflg = FALSE
    AND (s.sellercd IS NULL OR s.sellercd = 9999)
;
