style24.doc/산출물/4.구축/데이터처리/정산_상품정산_기준으로_정산확정_데이터_집계.sql

/* TsaSettle.getGoodsSettleList */
SELECT ODIH.ORD_NO                                                                                     /*주문번호*/
     , ODIH.ORD_DTL_NO                                                                                 /*주문상세번호*/
     , ODIH.MALL_GB                                                                                    /*몰구분*/
     , ODIH.MALL_GB_NM                                                                                 /*몰구분명*/
     , ODIH.EXTMALL_ORDER_ID                                                                           /*제휴몰주문번호*/
     , ODIH.EXTMALL_ID                                                                                 /*제휴몰ID*/
     , ODIH.EXTMALL_NM                                                                                 /*제휴몰명*/
     , ODIH.SUPPLY_VENDOR_CD                                                                           /*공급벤더코드*/
     , ODIH.SUPPLY_VENDOR_NM                                                                           /*공급벤더명*/
     , ODIH.SUPPLY_COMP_CD                                                                             /*공급업체코드*/
     , ODIH.SUPPLY_COMP_NM                                                                             /*공급업체명*/
     , ODIH.DISTRIBUTION_GB                                                                            /*유통구분*/
     , ODIH.DISTRIBUTION_GB_NM                                                                         /*유통구분명*/
     , ODIH.SETTLE_DAY                                                                                 /*정산주기*/
     , ODIH.SETTLE_DAY_NM                                                                              /*정산주기명*/
     , ODIH.ORD_DT                                                                                     /*주문일시*/
     , ODIH.SETTLE_GB                                                                                  /*정산구분*/
     , ODIH.SETTLE_GB_NM                                                                               /*정산구분명*/
     , ODIH.OCCUR_DT                                                                                   /*발생일시*/
     , ODIH.GOODS_CD                                                                                   /*상품코드*/
     , ODIH.GOODS_NM                                                                                   /*상품명*/
     , ODIH.BRAND_CD                                                                                   /*브랜드코드*/
     , ODIH.BRAND_ENM                                                                                  /*브랜드명*/
     , ODIH.MD_NM                                                                                      /*담당MD*/
     , ODIH.ITEM_CD                                                                                    /*단품코드*/
     , ODIH.OPT_CD1                                                                                    /*옵션코드1*/
     , ODIH.OPT_CD2                                                                                    /*옵션코드2*/
     , ODIH.MALL_PRICE                                                                                 /*몰판매가*/
     , ODIH.SELL_PRICE                                                                                 /*판매가*/
     , ODIH.SELL_QTY                                                                                   /*판매수량*/
     , ODIH.SELL_AMT                                                                                   /*판매금액(ORD_AMT에는 CPN1_DC_AMT가 빠진 금액이 들어가 있음)*/
     , IF(ODIH.CPN1_DC_AMT = -0,0,ODIH.CPN1_DC_AMT)
       + IF(ODIH.GOODS_CPN_DC_AMT = -0,0,ODIH.GOODS_CPN_DC_AMT)
       + IF(ODIH.CART_CPN_DC_AMT = -0,0,ODIH.CART_CPN_DC_AMT)               AS CPN_DC_AMT              /*쿠폰할인금액*/
     , IF(ODIH.CPN1_DC_AMT = -0,0,ODIH.CPN1_DC_AMT)                         AS CPN1_DC_AMT             /*즉시사용쿠폰금액*/
     , IF(ODIH.GOODS_CPN_DC_AMT = -0,0,ODIH.GOODS_CPN_DC_AMT)               AS GOODS_CPN_DC_AMT        /*상품쿠폰사용금액*/
     , IF(ODIH.CART_CPN_DC_AMT = -0,0,ODIH.CART_CPN_DC_AMT)                 AS CART_CPN_DC_AMT         /*주문서쿠폰사용금액*/
     , IF(ODIH.PNT_DC_AMT = -0,0,ODIH.PNT_DC_AMT)                           AS PNT_DC_AMT              /*포인트사용금액*/
     , IF(ODIH.TMTB_DC_AMT = -0,0,ODIH.TMTB_DC_AMT)                         AS TMTB_DC_AMT             /*다다익선할인금액*/
     , IF(ODIH.SELF_TMTB_DC_AMT = -0,0,ODIH.SELF_TMTB_DC_AMT)               AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
     , IF(ODIH.SUPPLY_COMP_TMTB_DC_AMT = -0,0,ODIH.SUPPLY_COMP_TMTB_DC_AMT) AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
     , IF(ODIH.GFCD_USE_AMT = -0,0,ODIH.GFCD_USE_AMT)                       AS GFCD_USE_AMT            /*상품권사용금액*/
     , IF(ODIH.SELF_CPN_DC_AMT = -0,0,ODIH.SELF_CPN_DC_AMT)                 AS SELF_CPN_DC_AMT         /*자사쿠폰분담액*/
     , IF(ODIH.SUPPLY_COMP_CPN_DC_AMT = -0,0,ODIH.SUPPLY_COMP_CPN_DC_AMT)   AS SUPPLY_COMP_CPN_DC_AMT  /*입점쿠폰분담액*/
     , CASE WHEN ODIH.SELL_QTY = 0 THEN 0
            ELSE
                (ODIH.SELL_AMT
                 - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
                 - ODIH.TMTB_DC_AMT
                ) / ODIH.SELL_QTY
       END                                                                  AS REAL_SELL_PRICE         /*실판매가*/
     , ODIH.SELL_AMT
       - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
       - ODIH.TMTB_DC_AMT                                                   AS REAL_SELL_AMT           /*실판매금액(=상품총액. 쿠폰과 다다익선만 차감. 상품권과 포인트는 포함)*/
     , ODIH.SELL_FEE_RATE                                                                              /*판매수수료율*/
     , ROUND((ODIH.SELL_AMT
              - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
              - ODIH.TMTB_DC_AMT
             ) * (ODIH.SELL_FEE_RATE / 100),0)                              AS SELL_FEE_AMT            /*수수료*/
     , (ODIH.SELL_AMT
        - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
        - ODIH.TMTB_DC_AMT)
       - ROUND((ODIH.SELL_AMT
                - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
                - ODIH.TMTB_DC_AMT
               ) * (ODIH.SELL_FEE_RATE / 100),0)
       + (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)                     AS SETTLE_AMT              /*정산대상액(실판매금액 - 수수료 + 자사다다익선분담액 + 자사쿠폰분담액)*/
FROM   (
        SELECT ODIH.ORD_DTL_ITEM_HST_SQ                                                                                    /*주문상세단품이력일련번호*/
             , ODIH.ORD_NO                                                                                                 /*주문번호*/
             , ODIH.ORD_DTL_NO                                                                                             /*주문상세번호*/
             , O.MALL_GB                                                                                                   /*몰구분*/
             , FN_GET_CODE_NM('G011',O.MALL_GB)                                                 AS MALL_GB_NM              /*몰구분명*/
             , OD.EXTMALL_ORDER_ID                                                                                         /*제휴몰주문번호*/
             , OD.EXTMALL_ID                                                                                               /*제휴몰ID*/
             , E.EXTMALL_NM                                                                                                /*제휴몰명*/
             , SC.SUPPLY_VENDOR_CD                                                                                         /*공급벤더코드*/
             , SV.SUPPLY_VENDOR_NM                                                                                         /*공급벤더명*/
             , OD.SUPPLY_COMP_CD                                                                                           /*공급업체코드*/
             , SC.SUPPLY_COMP_NM                                                                                           /*공급업체명*/
             , SC.DISTRIBUTION_GB                                                                                          /*유통구분*/
             , FN_GET_CODE_NM('G065',SC.DISTRIBUTION_GB)                                        AS DISTRIBUTION_GB_NM      /*유통구분명*/
             , SV.SETTLE_DAY                                                                                               /*정산주기*/
             , FN_GET_CODE_NM('G075',SV.SETTLE_DAY)                                             AS SETTLE_DAY_NM           /*정산주기명*/
             , DATE_FORMAT(O.PAY_DT,'%Y-%m-%d %H:%i:%S')                                        AS ORD_DT                  /*주문일시*/
             , ODIH.ORD_DTL_STAT                                                                AS SETTLE_GB               /*정산구분*/
             , FN_GET_CODE_NM('G720',ODIH.ORD_DTL_STAT)                                         AS SETTLE_GB_NM            /*정산구분명*/
             , DATE_FORMAT(ODIH.REG_DT,'%Y-%m-%d %H:%i:%S')                                     AS OCCUR_DT                /*발생일시*/
             , OD.GOODS_CD                                                                                                 /*상품코드*/
             , G.GOODS_NM                                                                                                  /*상품명*/
             , G.BRAND_CD                                                                                                  /*브랜드코드*/
             , B.BRAND_ENM                                                                                                 /*브랜드명*/
             , (SELECT U.USER_NM
                FROM   TB_BRAND_MD BM
                     , TB_USER U
                WHERE  BM.MD_NO = U.USER_NO
                AND    BM.BRAND_CD = G.BRAND_CD
                AND    BM.FORMAL_GB = G.FORMAL_GB
                LIMIT 1)                                                                        AS MD_NM                   /*담당MD*/
             , ODIH.ITEM_CD                                                                                                /*단품코드*/
             , ODIH.OPT_CD1                                                                                                /*옵션코드1*/
             , ODIH.OPT_CD2                                                                                                /*옵션코드2*/
             , ODIH.ITEM_PRICE                                                                  AS MALL_PRICE              /*몰판매가*/
             , CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
                        IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
                    ELSE
                        ODIH.ITEM_PRICE
               END                                                                              AS SELL_PRICE              /*판매가(정산용)*/
             , (CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN 1 ELSE -1 END)
               *
               CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20'/*판매-배송중*/ THEN
                             ODIH.ORD_QTY - ODIH.CNCL_RTN_QTY
                         ELSE /*환입-반품완료,환입-교환완료*/
                             ODIH.CNCL_RTN_QTY
                    END AS SIGNED INT)                                                          AS SELL_QTY                /*판매수량*/
             , (CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN 1 ELSE -1 END)
               *
               CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20'/*판매-배송중*/ THEN
                             ((ODIH.ORD_QTY - ODIH.CNCL_RTN_QTY) * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
                                                                             IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
                                                                         ELSE
                                                                             ODIH.ITEM_PRICE
                                                                    END))
                         ELSE /*환입-반품완료,환입-교환완료*/
                             (ODIH.CNCL_RTN_QTY * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
                                                            IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
                                                        ELSE
                                                            ODIH.ITEM_PRICE
                                                   END))
                    END AS SIGNED INT)                                                          AS SELL_AMT                /*판매금액*/
             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.CPN1_DC_AMT                        AS CPN1_DC_AMT             /*즉시사용쿠폰금액*/
             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.GOODS_CPN_DC_AMT                   AS GOODS_CPN_DC_AMT        /*상품쿠폰사용금액*/
             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.CART_CPN_DC_AMT                    AS CART_CPN_DC_AMT         /*주문서쿠폰사용금액*/
             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.PNT_DC_AMT                         AS PNT_DC_AMT              /*포인트사용금액*/
             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * (ODIH.TMTB1_DC_AMT + ODIH.TMTB2_DC_AMT) AS TMTB_DC_AMT             /*다다익선할인금액*/
             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) *
               (((100 - IFNULL(TB1.TMTB_BURDEN_RATE,0)) / 100) * ODIH.TMTB1_DC_AMT
                + ((100 - IFNULL(TB2.TMTB_BURDEN_RATE,0)) / 100) * ODIH.TMTB2_DC_AMT)           AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) *
               ((IFNULL(TB1.TMTB_BURDEN_RATE,0) / 100) * ODIH.TMTB1_DC_AMT
                + (IFNULL(TB2.TMTB_BURDEN_RATE,0) / 100) * ODIH.TMTB2_DC_AMT)                   AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.GFCD_USE_AMT                       AS GFCD_USE_AMT            /*상품권사용금액*/
             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) *
               (((100 - IFNULL(CB1.BURDEN_RATE,0)) / 100) * ODIH.CPN1_DC_AMT
                + ((100 - IFNULL(CB2.BURDEN_RATE,0)) / 100) * ODIH.GOODS_CPN_DC_AMT
                + ((100 - IFNULL(CB3.BURDEN_RATE,0)) / 100) * ODIH.CART_CPN_DC_AMT)             AS SELF_CPN_DC_AMT         /*자사쿠폰분담액*/
             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) *
               ((IFNULL(CB1.BURDEN_RATE,0) / 100) * ODIH.CPN1_DC_AMT
                + (IFNULL(CB2.BURDEN_RATE,0) / 100) * ODIH.GOODS_CPN_DC_AMT
                + (IFNULL(CB3.BURDEN_RATE,0) / 100) * ODIH.CART_CPN_DC_AMT)                     AS SUPPLY_COMP_CPN_DC_AMT  /*입점쿠폰분담액*/
             , CASE WHEN O.MALL_GB = 'G011_10' /*자사몰*/ THEN
                        OD.SELL_FEE_RATE
                    ELSE /*제휴몰*/
                        IFNULL(E.SELL_FEE_RATE,0)
               END                                                                              AS SELL_FEE_RATE           /*판매수수료율*/
        FROM   TB_ORDER_DETAIL_ITEM_HST ODIH
        INNER JOIN TB_ORDER_DETAIL OD ON ODIH.ORD_DTL_NO = OD.ORD_DTL_NO
        INNER JOIN TB_ORDER O ON ODIH.ORD_NO = O.ORD_NO
        INNER JOIN TB_SUPPLY_COMPANY SC ON OD.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
        INNER JOIN TB_SUPPLY_VENDOR SV ON SC.SUPPLY_VENDOR_CD = SV.SUPPLY_VENDOR_CD
        INNER JOIN TB_GOODS G ON OD.GOODS_CD = G.GOODS_CD
        INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
        LEFT OUTER JOIN TB_EXTMALL E ON OD.EXTMALL_ID = E.EXTMALL_ID
        LEFT OUTER JOIN TB_COUPON_BURDEN CB1 ON OD.CPN1_CPN_SQ = CB1.CPN_ID
                                            AND OD.SUPPLY_COMP_CD = CB1.SUPPLY_COMP_CD
        LEFT OUTER JOIN TB_COUPON_BURDEN CB2 ON OD.GOODS_CPN_SQ = CB2.CPN_ID
                                            AND OD.SUPPLY_COMP_CD = CB2.SUPPLY_COMP_CD
        LEFT OUTER JOIN TB_COUPON_BURDEN CB3 ON OD.CART_CPN_SQ = CB3.CPN_ID
                                            AND OD.SUPPLY_COMP_CD = CB3.SUPPLY_COMP_CD
        LEFT OUTER JOIN TB_TMTB_BURDEN TB1 ON OD.TMTB1_SQ = TB1.TMTB_SQ
                                          AND OD.SUPPLY_COMP_CD = TB1.TMTB_SUPPLY_CD
        LEFT OUTER JOIN TB_TMTB_BURDEN TB2 ON OD.TMTB2_SQ = TB2.TMTB_SQ
                                          AND OD.SUPPLY_COMP_CD = TB2.TMTB_SUPPLY_CD
        LEFT OUTER JOIN TB_EXTMALL_USAC_PRICE EUP ON ODIH.ORD_DTL_ITEM_HST_SQ = EUP.ORD_DTL_ITEM_HST_SQ
        WHERE  1 = 1
        AND    ODIH.REG_DT >= STR_TO_DATE('2021-09-01','%Y-%m-%d')
        AND    ODIH.REG_DT < DATE_ADD(STR_TO_DATE('2021-09-30','%Y-%m-%d'), INTERVAL 1 DAY)
        AND    ODIH.ORD_DTL_STAT IN ('G720_20','G720_50','G720_60') /*판매-배송중,환입-반품완료,환입-교환완료*/
        AND    SV.SUPPLY_VENDOR_CD = 'SV0004'
       ) ODIH
ORDER  BY ODIH.ORD_DTL_ITEM_HST_SQ
;

/*정산확정 데이터 목록*/
/* TsaSettle.createSettleConfirm */
-- 1.AS-IS. 수정 전
SELECT ODIH.USAC_DT                                                       /*정산일*/
     , SC.SUPPLY_VENDOR_CD                                                /*공급벤더코드*/
     , SUM(ODIH.SELL_QTY)                      AS SELL_QTY                /*판매수량*/
     , SUM(ODIH.REAL_SELL_AMT)                 AS REAL_SELL_AMT           /*실판매금액(=상품총액)*/
     , SUM(ODIH.SELL_FEE_AMT)                  AS SELL_FEE_AMT            /*수수료*/
     , SUM(ODIH.CPN_DC_AMT)                    AS CPN_DC_AMT              /*쿠폰할인금액*/
     , SUM(ODIH.SELF_CPN_DC_AMT)               AS SELF_CPN_DC_AMT         /*자사쿠폰분담액*/
     , SUM(ODIH.SUPPLY_COMP_CPN_DC_AMT)        AS SUPPLY_COMP_CPN_DC_AMT  /*입점쿠폰분담액*/
     , SUM(ODIH.TMTB_DC_AMT)                   AS TMTB_DC_AMT             /*다다익선할인금액*/
     , SUM(ODIH.SELF_TMTB_DC_AMT)              AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
     , SUM(ODIH.SUPPLY_COMP_TMTB_DC_AMT)       AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
     , SUM(CASE WHEN ODIH.SELL_FEE_AMT > 0 THEN
                    CASE WHEN ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) < 0 THEN 0
                         ELSE ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
                    END
                ELSE
                    ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
           END)                                AS BILL_AMT               /*계산서발행금액*/
     , TRUNCATE(SUM(CASE WHEN ODIH.SELL_FEE_AMT > 0 THEN
                             CASE WHEN ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) < 0 THEN 0
                                  ELSE ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
                             END
                         ELSE
                             ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
                    END) / 1.1,0)              AS SUPPLY_AMT             /*공급가액*/
     , SUM(CASE WHEN ODIH.SELL_FEE_AMT > 0 THEN
                    CASE WHEN ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) < 0 THEN 0
                         ELSE ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
                    END
                ELSE
                    ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
           END) - TRUNCATE(SUM(CASE WHEN ODIH.SELL_FEE_AMT > 0 THEN
                                        CASE WHEN ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) < 0 THEN 0
                                             ELSE ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
                                        END
                                    ELSE
                                        ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
                               END) / 1.1,0)   AS TAX_AMT                /*세액*/
FROM   (
        SELECT DATE_FORMAT(ODIH.OCCUR_DT,'%Y%m%d')          AS USAC_DT                 /*정산일*/
             , ODIH.SUPPLY_COMP_CD                                                     /*공급업체코드*/
             , SUM(ODIH.SELL_QTY)                           AS SELL_QTY                /*판매수량*/
             , SUM(ODIH.SELL_AMT
                   - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
                   - ODIH.TMTB_DC_AMT)                      AS REAL_SELL_AMT           /*실판매금액(=상품총액. 쿠폰과 다다익선만 차감. 상품권과 포인트는 포함)*/
             , ROUND(SUM((ODIH.SELL_AMT
                          - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
                          - ODIH.TMTB_DC_AMT
                         ) * (ODIH.SELL_FEE_RATE / 100)),0) AS SELL_FEE_AMT            /*수수료*/
             , SUM(ODIH.CPN1_DC_AMT +
                   ODIH.GOODS_CPN_DC_AMT +
                   ODIH.CART_CPN_DC_AMT)                    AS CPN_DC_AMT              /*쿠폰할인금액*/
             , SUM(ODIH.SELF_CPN_DC_AMT)                    AS SELF_CPN_DC_AMT         /*자사쿠폰분담액*/
             , SUM(ODIH.SUPPLY_COMP_CPN_DC_AMT)             AS SUPPLY_COMP_CPN_DC_AMT  /*입점쿠폰분담액*/
             , SUM(ODIH.TMTB_DC_AMT)                        AS TMTB_DC_AMT             /*다다익선할인금액*/
             , SUM(ODIH.SELF_TMTB_DC_AMT)                   AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
             , SUM(ODIH.SUPPLY_COMP_TMTB_DC_AMT)            AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
        FROM   (
                /* 이 부분 대신 상품정산목록 조회 쿼리문으로 대체. TO-BE 쿼리문 참조 START */
                SELECT ODIH.REG_DT                                                                      AS OCCUR_DT                /*발생일시*/
                     , OD.SUPPLY_COMP_CD                                                                                           /*공급업체코드*/
                     , (CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN 1 ELSE -1 END)
                       *
                       CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20'/*판매-배송중*/ THEN
                                     ODIH.ORD_QTY - ODIH.CNCL_RTN_QTY
                                 ELSE /*환입-반품완료,환입-교환완료*/
                                     ODIH.CNCL_RTN_QTY
                            END AS SIGNED INT)                                                          AS SELL_QTY                /*판매수량*/
                     , (CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN 1 ELSE -1 END)
                       *
                       CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20'/*판매-배송중*/ THEN
                                     ((ODIH.ORD_QTY - ODIH.CNCL_RTN_QTY) * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
                                                                                     IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
                                                                                 ELSE
                                                                                     ODIH.ITEM_PRICE
                                                                            END))
                                 ELSE /*환입-반품완료,환입-교환완료*/
                                     (ODIH.CNCL_RTN_QTY * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
                                                                    IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
                                                                ELSE
                                                                    ODIH.ITEM_PRICE
                                                           END))
                            END AS SIGNED INT)                                                          AS SELL_AMT                /*판매금액*/
                     , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.CPN1_DC_AMT                        AS CPN1_DC_AMT             /*즉시사용쿠폰금액*/
                     , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.GOODS_CPN_DC_AMT                   AS GOODS_CPN_DC_AMT        /*상품쿠폰사용금액*/
                     , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.CART_CPN_DC_AMT                    AS CART_CPN_DC_AMT         /*주문서쿠폰사용금액*/
                     , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.PNT_DC_AMT                         AS PNT_DC_AMT              /*포인트사용금액*/
                     , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * (ODIH.TMTB1_DC_AMT + ODIH.TMTB2_DC_AMT) AS TMTB_DC_AMT             /*다다익선할인금액*/
                     , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) *
                       (((100 - IFNULL(TB1.TMTB_BURDEN_RATE,0)) / 100) * ODIH.TMTB1_DC_AMT
                         + ((100 - IFNULL(TB2.TMTB_BURDEN_RATE,0)) / 100) * ODIH.TMTB2_DC_AMT)          AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
                     , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) *
                       ((IFNULL(TB1.TMTB_BURDEN_RATE,0) / 100) * ODIH.TMTB1_DC_AMT
                        + (IFNULL(TB2.TMTB_BURDEN_RATE,0) / 100) * ODIH.TMTB2_DC_AMT)                   AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
                     , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) *
                       (((100 - IFNULL(CB1.BURDEN_RATE,0)) / 100) * ODIH.CPN1_DC_AMT
                         + ((100 - IFNULL(CB2.BURDEN_RATE,0)) / 100) * ODIH.GOODS_CPN_DC_AMT
                         + ((100 - IFNULL(CB3.BURDEN_RATE,0)) / 100) * ODIH.CART_CPN_DC_AMT)            AS SELF_CPN_DC_AMT         /*자사쿠폰분담액*/
                     , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) *
                       ((IFNULL(CB1.BURDEN_RATE,0) / 100) * ODIH.CPN1_DC_AMT
                         + (IFNULL(CB2.BURDEN_RATE,0) / 100) * ODIH.GOODS_CPN_DC_AMT
                         + (IFNULL(CB3.BURDEN_RATE,0) / 100) * ODIH.CART_CPN_DC_AMT)                    AS SUPPLY_COMP_CPN_DC_AMT  /*입점쿠폰분담액*/
                     , CASE WHEN O.MALL_GB = 'G011_10' /*자사몰*/ THEN
                                OD.SELL_FEE_RATE
                            ELSE /*제휴몰*/
                                IFNULL(E.SELL_FEE_RATE,0)
                       END                                                                              AS SELL_FEE_RATE           /*판매수수료율*/
                FROM   TB_ORDER_DETAIL_ITEM_HST ODIH
                INNER JOIN TB_ORDER_DETAIL OD ON ODIH.ORD_DTL_NO = OD.ORD_DTL_NO
                INNER JOIN TB_ORDER O ON ODIH.ORD_NO = O.ORD_NO
                LEFT OUTER JOIN TB_EXTMALL E ON OD.EXTMALL_ID = E.EXTMALL_ID
                LEFT OUTER JOIN TB_COUPON_BURDEN CB1 ON OD.CPN1_CPN_SQ = CB1.CPN_ID
                                                    AND OD.SUPPLY_COMP_CD = CB1.SUPPLY_COMP_CD
                LEFT OUTER JOIN TB_COUPON_BURDEN CB2 ON OD.GOODS_CPN_SQ = CB2.CPN_ID
                                                    AND OD.SUPPLY_COMP_CD = CB2.SUPPLY_COMP_CD
                LEFT OUTER JOIN TB_COUPON_BURDEN CB3 ON OD.CART_CPN_SQ = CB3.CPN_ID
                                                    AND OD.SUPPLY_COMP_CD = CB3.SUPPLY_COMP_CD
                LEFT OUTER JOIN TB_TMTB_BURDEN TB1 ON OD.TMTB1_SQ = TB1.TMTB_SQ
                                                  AND OD.SUPPLY_COMP_CD = TB1.TMTB_SUPPLY_CD
                LEFT OUTER JOIN TB_TMTB_BURDEN TB2 ON OD.TMTB2_SQ = TB2.TMTB_SQ
                                                  AND OD.SUPPLY_COMP_CD = TB2.TMTB_SUPPLY_CD
                LEFT OUTER JOIN TB_EXTMALL_USAC_PRICE EUP ON ODIH.ORD_DTL_ITEM_HST_SQ = EUP.ORD_DTL_ITEM_HST_SQ
                WHERE  1 = 1
                AND    ODIH.REG_DT >= STR_TO_DATE('2021-09-01','%Y-%m-%d')
                AND    ODIH.REG_DT < DATE_ADD(STR_TO_DATE('2021-09-30','%Y-%m-%d'), INTERVAL 1 DAY)
                AND    ODIH.ORD_DTL_STAT IN ('G720_20','G720_50','G720_60') /*판매-배송중,환입-반품완료,환입-교환완료*/
               ) ODIH
        GROUP  BY DATE_FORMAT(ODIH.OCCUR_DT,'%Y%m%d')
                , ODIH.SUPPLY_COMP_CD
       ) ODIH
     , TB_SUPPLY_COMPANY SC
WHERE  ODIH.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
GROUP  BY ODIH.USAC_DT
        , SC.SUPPLY_VENDOR_CD
;

-- TO-BE. 수정 후 (상품정산 데이터를 기준으로 집계하도록 ...)
SELECT ODIH.USAC_DT                                                       /*정산일*/
     , SC.SUPPLY_VENDOR_CD                                                /*공급벤더코드*/
     , SUM(ODIH.SELL_QTY)                      AS SELL_QTY                /*판매수량*/
     , SUM(ODIH.REAL_SELL_AMT)                 AS REAL_SELL_AMT           /*실판매금액(=상품총액)*/
     , SUM(ODIH.SELL_FEE_AMT)                  AS SELL_FEE_AMT            /*수수료*/
     , SUM(ODIH.CPN_DC_AMT)                    AS CPN_DC_AMT              /*쿠폰할인금액*/
     , SUM(ODIH.SELF_CPN_DC_AMT)               AS SELF_CPN_DC_AMT         /*자사쿠폰분담액*/
     , SUM(ODIH.SUPPLY_COMP_CPN_DC_AMT)        AS SUPPLY_COMP_CPN_DC_AMT  /*입점쿠폰분담액*/
     , SUM(ODIH.TMTB_DC_AMT)                   AS TMTB_DC_AMT             /*다다익선할인금액*/
     , SUM(ODIH.SELF_TMTB_DC_AMT)              AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
     , SUM(ODIH.SUPPLY_COMP_TMTB_DC_AMT)       AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
     , SUM(CASE WHEN ODIH.SELL_FEE_AMT > 0 THEN
                    CASE WHEN ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) < 0 THEN 0
                         ELSE ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
                    END
                ELSE
                    ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
           END)                                AS BILL_AMT               /*계산서발행금액*/
     , TRUNCATE(SUM(CASE WHEN ODIH.SELL_FEE_AMT > 0 THEN
                             CASE WHEN ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) < 0 THEN 0
                                  ELSE ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
                             END
                         ELSE
                             ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
                    END) / 1.1,0)              AS SUPPLY_AMT             /*공급가액*/
     , SUM(CASE WHEN ODIH.SELL_FEE_AMT > 0 THEN
                    CASE WHEN ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) < 0 THEN 0
                         ELSE ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
                    END
                ELSE
                    ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
           END) - TRUNCATE(SUM(CASE WHEN ODIH.SELL_FEE_AMT > 0 THEN
                                        CASE WHEN ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) < 0 THEN 0
                                             ELSE ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
                                        END
                                    ELSE
                                        ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
                               END) / 1.1,0)   AS TAX_AMT                /*세액*/
FROM   (
        SELECT DATE_FORMAT(ODIH.OCCUR_DT,'%Y%m%d')          AS USAC_DT                 /*정산일*/
             , ODIH.SUPPLY_COMP_CD                                                     /*공급업체코드*/
             , SUM(ODIH.SELL_QTY)                           AS SELL_QTY                /*판매수량*/
             , SUM(ODIH.SELL_AMT
                   - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
                   - ODIH.TMTB_DC_AMT)                      AS REAL_SELL_AMT           /*실판매금액(=상품총액. 쿠폰과 다다익선만 차감. 상품권과 포인트는 포함)*/
             , ROUND(SUM((ODIH.SELL_AMT
                          - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
                          - ODIH.TMTB_DC_AMT
                         ) * (ODIH.SELL_FEE_RATE / 100)),0) AS SELL_FEE_AMT            /*수수료*/
             , SUM(ODIH.CPN1_DC_AMT +
                   ODIH.GOODS_CPN_DC_AMT +
                   ODIH.CART_CPN_DC_AMT)                    AS CPN_DC_AMT              /*쿠폰할인금액*/
             , SUM(ODIH.SELF_CPN_DC_AMT)                    AS SELF_CPN_DC_AMT         /*자사쿠폰분담액*/
             , SUM(ODIH.SUPPLY_COMP_CPN_DC_AMT)             AS SUPPLY_COMP_CPN_DC_AMT  /*입점쿠폰분담액*/
             , SUM(ODIH.TMTB_DC_AMT)                        AS TMTB_DC_AMT             /*다다익선할인금액*/
             , SUM(ODIH.SELF_TMTB_DC_AMT)                   AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
             , SUM(ODIH.SUPPLY_COMP_TMTB_DC_AMT)            AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
        FROM   (
                /* 이 부분 대신 상품정산목록 조회 쿼리문으로 대체함. */
                SELECT ODIH.ORD_NO                                                                                     /*주문번호*/
				     , ODIH.ORD_DTL_NO                                                                                 /*주문상세번호*/
				     , ODIH.MALL_GB                                                                                    /*몰구분*/
				     , ODIH.MALL_GB_NM                                                                                 /*몰구분명*/
				     , ODIH.EXTMALL_ORDER_ID                                                                           /*제휴몰주문번호*/
				     , ODIH.EXTMALL_ID                                                                                 /*제휴몰ID*/
				     , ODIH.EXTMALL_NM                                                                                 /*제휴몰명*/
				     , ODIH.SUPPLY_VENDOR_CD                                                                           /*공급벤더코드*/
				     , ODIH.SUPPLY_VENDOR_NM                                                                           /*공급벤더명*/
				     , ODIH.SUPPLY_COMP_CD                                                                             /*공급업체코드*/
				     , ODIH.SUPPLY_COMP_NM                                                                             /*공급업체명*/
				     , ODIH.DISTRIBUTION_GB                                                                            /*유통구분*/
				     , ODIH.DISTRIBUTION_GB_NM                                                                         /*유통구분명*/
				     , ODIH.SETTLE_DAY                                                                                 /*정산주기*/
				     , ODIH.SETTLE_DAY_NM                                                                              /*정산주기명*/
				     , ODIH.ORD_DT                                                                                     /*주문일시*/
				     , ODIH.SETTLE_GB                                                                                  /*정산구분*/
				     , ODIH.SETTLE_GB_NM                                                                               /*정산구분명*/
				     , ODIH.OCCUR_DT                                                                                   /*발생일시*/
				     , ODIH.GOODS_CD                                                                                   /*상품코드*/
				     , ODIH.GOODS_NM                                                                                   /*상품명*/
				     , ODIH.BRAND_CD                                                                                   /*브랜드코드*/
				     , ODIH.BRAND_ENM                                                                                  /*브랜드명*/
				     , ODIH.MD_NM                                                                                      /*담당MD*/
				     , ODIH.ITEM_CD                                                                                    /*단품코드*/
				     , ODIH.OPT_CD1                                                                                    /*옵션코드1*/
				     , ODIH.OPT_CD2                                                                                    /*옵션코드2*/
				     , ODIH.MALL_PRICE                                                                                 /*몰판매가*/
				     , ODIH.SELL_PRICE                                                                                 /*판매가*/
				     , ODIH.SELL_QTY                                                                                   /*판매수량*/
				     , ODIH.SELL_AMT                                                                                   /*판매금액(ORD_AMT에는 CPN1_DC_AMT가 빠진 금액이 들어가 있음)*/
				     , IF(ODIH.CPN1_DC_AMT = -0,0,ODIH.CPN1_DC_AMT)
				       + IF(ODIH.GOODS_CPN_DC_AMT = -0,0,ODIH.GOODS_CPN_DC_AMT)
				       + IF(ODIH.CART_CPN_DC_AMT = -0,0,ODIH.CART_CPN_DC_AMT)               AS CPN_DC_AMT              /*쿠폰할인금액*/
				     , IF(ODIH.CPN1_DC_AMT = -0,0,ODIH.CPN1_DC_AMT)                         AS CPN1_DC_AMT             /*즉시사용쿠폰금액*/
				     , IF(ODIH.GOODS_CPN_DC_AMT = -0,0,ODIH.GOODS_CPN_DC_AMT)               AS GOODS_CPN_DC_AMT        /*상품쿠폰사용금액*/
				     , IF(ODIH.CART_CPN_DC_AMT = -0,0,ODIH.CART_CPN_DC_AMT)                 AS CART_CPN_DC_AMT         /*주문서쿠폰사용금액*/
				     , IF(ODIH.PNT_DC_AMT = -0,0,ODIH.PNT_DC_AMT)                           AS PNT_DC_AMT              /*포인트사용금액*/
				     , IF(ODIH.TMTB_DC_AMT = -0,0,ODIH.TMTB_DC_AMT)                         AS TMTB_DC_AMT             /*다다익선할인금액*/
				     , IF(ODIH.SELF_TMTB_DC_AMT = -0,0,ODIH.SELF_TMTB_DC_AMT)               AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
				     , IF(ODIH.SUPPLY_COMP_TMTB_DC_AMT = -0,0,ODIH.SUPPLY_COMP_TMTB_DC_AMT) AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
				     , IF(ODIH.GFCD_USE_AMT = -0,0,ODIH.GFCD_USE_AMT)                       AS GFCD_USE_AMT            /*상품권사용금액*/
				     , IF(ODIH.SELF_CPN_DC_AMT = -0,0,ODIH.SELF_CPN_DC_AMT)                 AS SELF_CPN_DC_AMT         /*자사쿠폰분담액*/
				     , IF(ODIH.SUPPLY_COMP_CPN_DC_AMT = -0,0,ODIH.SUPPLY_COMP_CPN_DC_AMT)   AS SUPPLY_COMP_CPN_DC_AMT  /*입점쿠폰분담액*/
				     , CASE WHEN ODIH.SELL_QTY = 0 THEN 0
				            ELSE
				                (ODIH.SELL_AMT
				                 - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
				                 - ODIH.TMTB_DC_AMT
				                ) / ODIH.SELL_QTY
				       END                                                                  AS REAL_SELL_PRICE         /*실판매가*/
				     , ODIH.SELL_AMT
				       - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
				       - ODIH.TMTB_DC_AMT                                                   AS REAL_SELL_AMT           /*실판매금액(=상품총액. 쿠폰과 다다익선만 차감. 상품권과 포인트는 포함)*/
				     , ODIH.SELL_FEE_RATE                                                                              /*판매수수료율*/
				     , ROUND((ODIH.SELL_AMT
				              - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
				              - ODIH.TMTB_DC_AMT
				             ) * (ODIH.SELL_FEE_RATE / 100),0)                              AS SELL_FEE_AMT            /*수수료*/
				     , (ODIH.SELL_AMT
				        - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
				        - ODIH.TMTB_DC_AMT)
				       - ROUND((ODIH.SELL_AMT
				                - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
				                - ODIH.TMTB_DC_AMT
				               ) * (ODIH.SELL_FEE_RATE / 100),0)
				       + (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)                     AS SETTLE_AMT              /*정산대상액(실판매금액 - 수수료 + 자사다다익선분담액 + 자사쿠폰분담액)*/
				FROM   (
				        SELECT ODIH.ORD_DTL_ITEM_HST_SQ                                                                                    /*주문상세단품이력일련번호*/
				             , ODIH.ORD_NO                                                                                                 /*주문번호*/
				             , ODIH.ORD_DTL_NO                                                                                             /*주문상세번호*/
				             , O.MALL_GB                                                                                                   /*몰구분*/
				             , FN_GET_CODE_NM('G011',O.MALL_GB)                                                 AS MALL_GB_NM              /*몰구분명*/
				             , OD.EXTMALL_ORDER_ID                                                                                         /*제휴몰주문번호*/
				             , OD.EXTMALL_ID                                                                                               /*제휴몰ID*/
				             , E.EXTMALL_NM                                                                                                /*제휴몰명*/
				             , SC.SUPPLY_VENDOR_CD                                                                                         /*공급벤더코드*/
				             , SV.SUPPLY_VENDOR_NM                                                                                         /*공급벤더명*/
				             , OD.SUPPLY_COMP_CD                                                                                           /*공급업체코드*/
				             , SC.SUPPLY_COMP_NM                                                                                           /*공급업체명*/
				             , SC.DISTRIBUTION_GB                                                                                          /*유통구분*/
				             , FN_GET_CODE_NM('G065',SC.DISTRIBUTION_GB)                                        AS DISTRIBUTION_GB_NM      /*유통구분명*/
				             , SV.SETTLE_DAY                                                                                               /*정산주기*/
				             , FN_GET_CODE_NM('G075',SV.SETTLE_DAY)                                             AS SETTLE_DAY_NM           /*정산주기명*/
				             , DATE_FORMAT(O.PAY_DT,'%Y-%m-%d %H:%i:%S')                                        AS ORD_DT                  /*주문일시*/
				             , ODIH.ORD_DTL_STAT                                                                AS SETTLE_GB               /*정산구분*/
				             , FN_GET_CODE_NM('G720',ODIH.ORD_DTL_STAT)                                         AS SETTLE_GB_NM            /*정산구분명*/
				             , DATE_FORMAT(ODIH.REG_DT,'%Y-%m-%d %H:%i:%S')                                     AS OCCUR_DT                /*발생일시*/
				             , OD.GOODS_CD                                                                                                 /*상품코드*/
				             , G.GOODS_NM                                                                                                  /*상품명*/
				             , G.BRAND_CD                                                                                                  /*브랜드코드*/
				             , B.BRAND_ENM                                                                                                 /*브랜드명*/
				             , (SELECT U.USER_NM
				                FROM   TB_BRAND_MD BM
				                     , TB_USER U
				                WHERE  BM.MD_NO = U.USER_NO
				                AND    BM.BRAND_CD = G.BRAND_CD
				                AND    BM.FORMAL_GB = G.FORMAL_GB
				                LIMIT 1)                                                                        AS MD_NM                   /*담당MD*/
				             , ODIH.ITEM_CD                                                                                                /*단품코드*/
				             , ODIH.OPT_CD1                                                                                                /*옵션코드1*/
				             , ODIH.OPT_CD2                                                                                                /*옵션코드2*/
				             , ODIH.ITEM_PRICE                                                                  AS MALL_PRICE              /*몰판매가*/
				             , CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
				                        IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
				                    ELSE
				                        ODIH.ITEM_PRICE
				               END                                                                              AS SELL_PRICE              /*판매가(정산용)*/
				             , (CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN 1 ELSE -1 END)
				               *
				               CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20'/*판매-배송중*/ THEN
				                             ODIH.ORD_QTY - ODIH.CNCL_RTN_QTY
				                         ELSE /*환입-반품완료,환입-교환완료*/
				                             ODIH.CNCL_RTN_QTY
				                    END AS SIGNED INT)                                                          AS SELL_QTY                /*판매수량*/
				             , (CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN 1 ELSE -1 END)
				               *
				               CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20'/*판매-배송중*/ THEN
				                             ((ODIH.ORD_QTY - ODIH.CNCL_RTN_QTY) * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
				                                                                             IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
				                                                                         ELSE
				                                                                             ODIH.ITEM_PRICE
				                                                                    END))
				                         ELSE /*환입-반품완료,환입-교환완료*/
				                             (ODIH.CNCL_RTN_QTY * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
				                                                            IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
				                                                        ELSE
				                                                            ODIH.ITEM_PRICE
				                                                   END))
				                    END AS SIGNED INT)                                                          AS SELL_AMT                /*판매금액*/
				             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.CPN1_DC_AMT                        AS CPN1_DC_AMT             /*즉시사용쿠폰금액*/
				             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.GOODS_CPN_DC_AMT                   AS GOODS_CPN_DC_AMT        /*상품쿠폰사용금액*/
				             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.CART_CPN_DC_AMT                    AS CART_CPN_DC_AMT         /*주문서쿠폰사용금액*/
				             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.PNT_DC_AMT                         AS PNT_DC_AMT              /*포인트사용금액*/
				             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * (ODIH.TMTB1_DC_AMT + ODIH.TMTB2_DC_AMT) AS TMTB_DC_AMT             /*다다익선할인금액*/
				             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) *
				               (((100 - IFNULL(TB1.TMTB_BURDEN_RATE,0)) / 100) * ODIH.TMTB1_DC_AMT
				                + ((100 - IFNULL(TB2.TMTB_BURDEN_RATE,0)) / 100) * ODIH.TMTB2_DC_AMT)           AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
				             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) *
				               ((IFNULL(TB1.TMTB_BURDEN_RATE,0) / 100) * ODIH.TMTB1_DC_AMT
				                + (IFNULL(TB2.TMTB_BURDEN_RATE,0) / 100) * ODIH.TMTB2_DC_AMT)                   AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
				             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) * ODIH.GFCD_USE_AMT                       AS GFCD_USE_AMT            /*상품권사용금액*/
				             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) *
				               (((100 - IFNULL(CB1.BURDEN_RATE,0)) / 100) * ODIH.CPN1_DC_AMT
				                + ((100 - IFNULL(CB2.BURDEN_RATE,0)) / 100) * ODIH.GOODS_CPN_DC_AMT
				                + ((100 - IFNULL(CB3.BURDEN_RATE,0)) / 100) * ODIH.CART_CPN_DC_AMT)             AS SELF_CPN_DC_AMT         /*자사쿠폰분담액*/
				             , IF(ODIH.ORD_DTL_STAT = 'G720_20',1,-1) *
				               ((IFNULL(CB1.BURDEN_RATE,0) / 100) * ODIH.CPN1_DC_AMT
				                + (IFNULL(CB2.BURDEN_RATE,0) / 100) * ODIH.GOODS_CPN_DC_AMT
				                + (IFNULL(CB3.BURDEN_RATE,0) / 100) * ODIH.CART_CPN_DC_AMT)                     AS SUPPLY_COMP_CPN_DC_AMT  /*입점쿠폰분담액*/
				             , CASE WHEN O.MALL_GB = 'G011_10' /*자사몰*/ THEN
				                        OD.SELL_FEE_RATE
				                    ELSE /*제휴몰*/
				                        IFNULL(E.SELL_FEE_RATE,0)
				               END                                                                              AS SELL_FEE_RATE           /*판매수수료율*/
				        FROM   TB_ORDER_DETAIL_ITEM_HST ODIH
				        INNER JOIN TB_ORDER_DETAIL OD ON ODIH.ORD_DTL_NO = OD.ORD_DTL_NO
				        INNER JOIN TB_ORDER O ON ODIH.ORD_NO = O.ORD_NO
				        INNER JOIN TB_SUPPLY_COMPANY SC ON OD.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
				        INNER JOIN TB_SUPPLY_VENDOR SV ON SC.SUPPLY_VENDOR_CD = SV.SUPPLY_VENDOR_CD
				        INNER JOIN TB_GOODS G ON OD.GOODS_CD = G.GOODS_CD
				        INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
				        LEFT OUTER JOIN TB_EXTMALL E ON OD.EXTMALL_ID = E.EXTMALL_ID
				        LEFT OUTER JOIN TB_COUPON_BURDEN CB1 ON OD.CPN1_CPN_SQ = CB1.CPN_ID
				                                            AND OD.SUPPLY_COMP_CD = CB1.SUPPLY_COMP_CD
				        LEFT OUTER JOIN TB_COUPON_BURDEN CB2 ON OD.GOODS_CPN_SQ = CB2.CPN_ID
				                                            AND OD.SUPPLY_COMP_CD = CB2.SUPPLY_COMP_CD
				        LEFT OUTER JOIN TB_COUPON_BURDEN CB3 ON OD.CART_CPN_SQ = CB3.CPN_ID
				                                            AND OD.SUPPLY_COMP_CD = CB3.SUPPLY_COMP_CD
				        LEFT OUTER JOIN TB_TMTB_BURDEN TB1 ON OD.TMTB1_SQ = TB1.TMTB_SQ
				                                          AND OD.SUPPLY_COMP_CD = TB1.TMTB_SUPPLY_CD
				        LEFT OUTER JOIN TB_TMTB_BURDEN TB2 ON OD.TMTB2_SQ = TB2.TMTB_SQ
				                                          AND OD.SUPPLY_COMP_CD = TB2.TMTB_SUPPLY_CD
				        LEFT OUTER JOIN TB_EXTMALL_USAC_PRICE EUP ON ODIH.ORD_DTL_ITEM_HST_SQ = EUP.ORD_DTL_ITEM_HST_SQ
				        WHERE  1 = 1
				        AND    ODIH.REG_DT >= STR_TO_DATE('2021-09-01','%Y-%m-%d')
				        AND    ODIH.REG_DT < DATE_ADD(STR_TO_DATE('2021-09-30','%Y-%m-%d'), INTERVAL 1 DAY)
				        AND    ODIH.ORD_DTL_STAT IN ('G720_20','G720_50','G720_60') /*판매-배송중,환입-반품완료,환입-교환완료*/
				       ) ODIH
				/* 이 부분 대신 상품정산목록 조회 쿼리문으로 대체함 */
               ) ODIH
        GROUP  BY DATE_FORMAT(ODIH.OCCUR_DT,'%Y%m%d')
                , ODIH.SUPPLY_COMP_CD
       ) ODIH
     , TB_SUPPLY_COMPANY SC
WHERE  ODIH.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
GROUP  BY ODIH.USAC_DT
        , SC.SUPPLY_VENDOR_CD
;