-- View: "v_order_detail_list"

DROP VIEW v_order_detail_list;

CREATE OR REPLACE VIEW v_order_detail_list AS
SELECT
    odr_detail.orderno,
    odr_detail.orderrowno,
    odr_detail.itemcd,
    odr_detail.structcd,
    stk_detail.stockcd,
    stk_detail.sellercd,
    itm_struct.makeritemcd,
    itm_struct.jancd,
    itm_struct.variationnm,
    itm_basic.itemnm,
    odr_detail.typediv,
    n5.name AS orderdetailtypename,
    odr_detail.price,
    odr_detail.count,
    stk_detail.rackid,
    itm_struct.salestartdate,
    odr_title.orderdate,
    odr_title.ordermember,
    odr_title.takkyubin,
    odr_title.enquiryno,
    odr_title.note,
    mbr_basic.lastnm,
    mbr_basic.firstnm,
    mbr_basic.mailaddr,
    odr_title.sendmailaddr,
    odr_title.sendbuyer,
    odr_title.addressee,
    odr_title.sendpostalcd,
    pref.prefnm,
    odr_title.sendaddr1,
    odr_title.sendaddr2,
    odr_title.sendtelno1,
    fwd_detail.forwardstate,
    n.name AS fwstate,
    deposit.depositstate,
    nn.name AS depostate,
    deposit.depositdiv,
    nnn.name AS depositdivname,
    deposit.cardcompany,
    deposit.cardnumber,
    deposit.cardname,
    deposit.cardexp,
    deposit.shitennumberkey,
    deposit.kouzanumberkey,
    deposit.cvsinfo,
    deposit.remiseinfo,
    stk_detail.stockstate,
    nnnn.name AS syouhinstatename,
    fwd_detail.updcnt AS fwd_detail_updcnt,
    deposit.updcnt AS deposit_updcnt
FROM
    t_order_title odr_title
    LEFT JOIN t_order_detail odr_detail 
              ON odr_title.orderno = odr_detail.orderno
              AND odr_detail.delflg = false
    LEFT JOIN t_forward_detail fwd_detail
              ON odr_detail.orderno = fwd_detail.orderno
              AND odr_detail.orderrowno = fwd_detail.orderrowno
--            AND fwd_detail.delflg = false
    LEFT JOIN t_item_basic_attr itm_basic
              ON odr_detail.itemcd = itm_basic.itemcd
              AND itm_basic.delflg = false
    LEFT JOIN t_item_struct itm_struct
              ON itm_basic.itemcd = itm_struct.itemcd
              AND itm_struct.structcd = odr_detail.structcd
              AND itm_struct.delflg = false
    LEFT JOIN t_member_basic_attr mbr_basic
              ON odr_title.ordermember = mbr_basic.membercd
--            AND mbr_basic.delflg = false
    LEFT JOIN t_deposit deposit
              ON odr_title.orderno = deposit.orderno
--            AND deposit.delflg = false
    LEFT JOIN t_pref pref
              ON odr_title.sendprefcd = pref.prefcd
--            AND pref.delflg = false
    LEFT JOIN t_ex_stock_detail stk_detail
              ON odr_detail.itemcd = stk_detail.itemcd
              AND odr_detail.structcd = stk_detail.structcd
              AND odr_detail.stockcd = stk_detail.stockcd
--            AND stk_detail.delflg = false
    LEFT JOIN t_ex_stock_detail d
              ON odr_detail.itemcd = d.itemcd
              AND odr_detail.structcd = d.structcd
              AND odr_detail.stockcd = d.stockcd
              AND d.delflg = FALSE
    LEFT JOIN t_name n5
              ON n5.namediv = 11 AND n5.namecd = odr_detail.typediv
    LEFT JOIN t_name nnnn
              ON nnnn.namediv = 10 AND nnnn.namecd = d.stockstate
    LEFT JOIN t_name nnn
              ON nnn.namediv = 13 AND nnn.namecd = deposit.depositdiv
    LEFT JOIN t_name nn
              ON nn.namediv = 14 AND nn.namecd = deposit.depositstate
    LEFT JOIN t_name n
              ON n.namediv = 12 AND n.namecd = fwd_detail.forwardstate

WHERE
    odr_title.delflg = false AND odr_title.preorderflg = FALSE

ORDER BY odr_detail.orderno, odr_detail.typediv, odr_detail.orderrowno;
