-- G720_10 판매-결제완료 -- G720_20 판매-배송중 -- G720_30 환입-취소완료 -- G720_40 환입-품절취소 -- G720_50 환입_반품완료 -- G720_60 환입-교환완료 /* 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_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.SELL_PRICE /*판매가*/ , ODIH.SELL_QTY /*판매수량*/ , ODIH.SELL_AMT /*판매금액(ORD_AMT에는 CPN1_DC_AMT가 빠진 금액이 들어가 있음)*/ , ODIH.CPN1_DC_AMT /*즉시사용쿠폰금액*/ , ODIH.GOODS_CPN_DC_AMT /*상품쿠폰사용금액*/ , ODIH.CART_CPN_DC_AMT /*주문서쿠폰사용금액*/ , ODIH.PNT_DC_AMT /*포인트사용금액*/ , ODIH.TMTB_DC_AMT /*다다익선할인금액*/ , ODIH.GFCD_USE_AMT /*상품권사용금액*/ , ODIH.SELF_CPN_DC_AMT /*자사쿠폰분담액*/ , ODIH.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) 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 /*제휴몰명*/ , OD.SUPPLY_COMP_CD /*공급업체코드*/ , SC.SUPPLY_COMP_NM /*공급업체명*/ , SC.DISTRIBUTION_GB /*유통구분*/ , FN_GET_CODE_NM('G065',SC.DISTRIBUTION_GB) AS DISTRIBUTION_GB_NM /*유통구분명*/ , SC.SETTLE_DAY /*정산주기*/ , FN_GET_CODE_NM('G075',SC.SETTLE_DAY) AS SETTLE_DAY_NM /*정산주기명*/ , DATE_FORMAT(O.ORD_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 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_AMT - ODIH.CNCL_RTN_AMT + ODIH.CPN1_DC_AMT ELSE /*환입-반품완료,환입-교환완료*/ ODIH.CNCL_RTN_AMT + ODIH.CPN1_DC_AMT END AS SIGNED INT) AS SELL_AMT /*판매금액(ORD_AMT에는 CPN1_DC_AMT가 빠진 금액이 들어가 있음)*/ , ODIH.CPN1_DC_AMT /*즉시사용쿠폰금액*/ , ODIH.GOODS_CPN_DC_AMT /*상품쿠폰사용금액*/ , ODIH.CART_CPN_DC_AMT /*주문서쿠폰사용금액*/ , ODIH.PNT_DC_AMT /*포인트사용금액*/ , ODIH.TMTB1_DC_AMT + ODIH.TMTB2_DC_AMT AS TMTB_DC_AMT /*다다익선할인금액*/ , ODIH.GFCD_USE_AMT /*상품권사용금액*/ , ((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 /*자사쿠폰분담액*/ , (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 SC.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_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 = CB1.CPN_ID AND OD.SUPPLY_COMP_CD = CB1.SUPPLY_COMP_CD LEFT OUTER JOIN TB_COUPON_BURDEN CB3 ON OD.CART_CPN_SQ = CB1.CPN_ID AND OD.SUPPLY_COMP_CD = CB1.SUPPLY_COMP_CD WHERE 1 = 1 AND ODIH.REG_DT >= STR_TO_DATE( '2021-06-07' /**P*/,'%Y-%m-%d') AND ODIH.REG_DT < DATE_ADD(STR_TO_DATE( '2021-07-06' /**P*/,'%Y-%m-%d'), INTERVAL 1 DAY) AND ODIH.ORD_DTL_STAT IN ('G720_20','G720_50','G720_60') /*판매-배송중,환입-반품완료,환입-교환완료*/ ) ODIH ORDER BY ODIH.ORD_DTL_ITEM_HST_SQ