-- View: "v_item_basic_term"

DROP VIEW v_item_basic_term;

CREATE OR REPLACE VIEW v_item_basic_term AS
SELECT
    s.itemcd,
    s.condition,
    s.stockstate,
	sum(s.exhibitcount) as exhibitcount

FROM
    t_item_basic_attr basic,
    (
    SELECT
        s.itemcd,
        s.structcd,
        s.stockcd,
        s.sellercd,
        s.condition,
        s.stockstate,
        COALESCE(sum(o.count), 0) as count,
        (s.exhibitcount-COALESCE(sum(o.count), 0)) as exhibitcount

    FROM
        t_item_struct st, t_ex_stock_detail s
        left join
        (select * 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
        left join t_member_basic_attr m
            on s.sellercd=m.membercd and m.delflg=false
        LEFT JOIN t_seller_attr se
            ON se.membercd = s.sellercd AND se.delflg = FALSE

    WHERE
	    st.delflg=FALSE
		AND st.itemcd=s.itemcd
		AND st.structcd=s.structcd
		AND s.delflg=FALSE
        AND(
            (s.stockstate=1 AND (s.exhibitcount-COALESCE(o.count, 0) = 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 not in (1,3))
        )
        AND ((s.sellercd IS NOT NULL AND m.memberstate=1 AND COALESCE(se.itemexhibitstop, FALSE) = FALSE) OR (s.sellercd IS NULL))
    GROUP BY
        s.itemcd, s.structcd, s.stockcd, s.sellercd, s.condition, s.stockstate, s.exhibitcount
    ) s
WHERE
        s.itemcd = basic.itemcd
        AND basic.delflg = FALSE
GROUP BY
    s.itemcd,
    s.condition,
    s.stockstate
;