-- View: "v_stock_order"

DROP VIEW "v_stock_order";

CREATE OR REPLACE VIEW "v_stock_order"
AS
SELECT
    s.itemcd,
    s.structcd,
    s.stockcd,
    s.sellercd,
    s.stockstate,
    COALESCE(sum(o.count), 0) as count,
    (s.exhibitcount-COALESCE(sum(o.count), 0)) as exhibitcount

FROM
    t_ex_stock_detail s
    left join
      (select *, o.delflg as od_delflg from t_order_detail o
        left join t_forward_detail f
          on f.orderno=o.orderno
          and f.orderrowno=o.orderrowno
       where f.forwardstate is null
        or not (f.forwardstate is not null
         and (f.forwardstate=9 or (f.forwardstate=8 and f.returndate is not null))
             )
      ) as o
	on o.itemcd=s.itemcd
        AND o.structcd=s.structcd
        AND o.stockcd=s.stockcd
        AND o.od_delflg=FALSE
 
WHERE
    s.delflg=FALSE
    AND(
        (s.stockstate=1 AND s.exhibitcount=0)
        OR (s.stockstate=3 AND (s.exhibitcount-COALESCE(o.count, 0) > 0))
        OR (s.sellercd IS NOT NULL AND s.stockstate=3 AND (s.exhibitcount-COALESCE(count, 0) > 0))
        OR (s.stockstate = 5)
    )
GROUP BY
    s.itemcd, s.structcd, s.stockcd, s.sellercd, s.stockstate, s.exhibitcount
;