--
-- View: v_stock_item

-- DROP VIEW v_stock_item;
CREATE OR REPLACE VIEW v_stock_item AS
SELECT
-- t_ex_stock_detail
    es.itemcd,
    es.structcd,
    es.stockcd,
    es.sellercd,
    es.stocktitlecd,
    es.exhibitcount,
    es.condition,
    es.sellingprice,
    es.souryouprc,
    es.stockstate,
    es.scdstartdate,
    es.scdcompdate,
    es.itemexp,
    es.rackid,
    es.managecd,
    es.costprice,
    es.ntcostprice,
    es.consignment,
    es.arrivaldate,
-- t_item_struct
    it.makeritemcd,
    it.jancd,
    it.variationnm,
    it.salestartdate,
    it.orderclosedate,
    it.comment,
-- t_item_basic_attr
     ib.itemnm,
    ib.itemnmkana,
    ib.makercd,
-- t_order_detail
    od.orderno as od_orderno,
    od.orderrowno as od_orderrowno,
    od.typediv,
    od.price,
    od.count,
-- t_forward_detail
    fd.orderno as fd_orderno,
    fd.orderrowno as fd_orderrowno,
    fd.forwarddate,
    fd.forwardstate,
    fd.returnreason,
-- t_stock_title
    st.orderno as st_orderno,
    st.orderdate,
    st.suppliercd,
    st.supplidiv,
-- t_supplier
    sp.suppliernm,
    sp.disppriority,
-- updcnt
    es.updcnt as es_updcnt,
    od.updcnt as od_updcnt,
    fd.updcnt as fd_updcnt,
    st.updcnt as st_updcnt,
    sp.updcnt as sp_updcnt,
    it.updcnt as it_updcnt,
    ib.updcnt as ib_updcnt
FROM t_ex_stock_detail es
    LEFT JOIN t_order_detail od
        ON es.itemcd = od.itemcd AND es.structcd = od.structcd AND es.stockcd = od.stockcd
    LEFT JOIN t_forward_detail fd 
        ON od.orderno = fd.orderno AND od.orderrowno = fd.orderrowno
    LEFT JOIN t_stock_title st
        ON es.stocktitlecd = st.stocktitlecd
    LEFT JOIN t_supplier sp
        ON st.suppliercd = sp.suppliercd
    LEFT JOIN t_item_struct it
        ON it.itemcd = es.itemcd AND it.structcd = es.structcd
    LEFT JOIN t_item_basic_attr ib
        ON ib.itemcd = es.itemcd
WHERE
    es.delflg=FALSE
;
