style24.doc/산출물/4.구축/데이터추출/BOS_주문상세_주문결제내역.sql

		    SELECT ORD_NO
		         , CASE WHEN PG_GB = 'KCP' THEN FN_GET_CODE_NM('G014',PAY_MEANS)
		                WHEN PG_GB = 'NAVER' THEN
		                    CASE WHEN PAY_MEANS = 'G014_10' THEN '네이버페이(계좌)'
		                         WHEN PAY_MEANS = 'G014_30' THEN '네이버페이(신용)'
		                         ELSE '네이버페이'
		                    END
		                WHEN PG_GB = 'NAVER_ORDER' THEN '네이버페이주문형'
		                WHEN PG_GB = 'KAKAO' THEN
		                    CASE WHEN PAY_MEANS = 'G014_10' THEN '카카오페이(계좌)'
		                         WHEN PAY_MEANS = 'G014_30' THEN '카카오페이(신용)'
		                         ELSE '카카오페이'
		                    END
		                WHEN PG_GB = 'PAYCO' THEN
		                    CASE WHEN PAY_MEANS = 'G014_10' THEN '페이코(계좌)'
		                         WHEN PAY_MEANS = 'G014_30' THEN '페이코(신용)'
		                         ELSE '페이코'
		                    END
		                ELSE ''
		           END           AS PAY_MEANS_NM  /*결제수단명*/
		         , CASE WHEN PG_GB = 'KCP' THEN
		                    CASE WHEN PAY_MEANS = 'G014_10' THEN CONCAT(FN_GET_CODE_NM('G940',VA_BANK),' / ',VA_NO)
		                         WHEN PAY_MEANS = 'G014_20' THEN CONCAT(FN_GET_CODE_NM('G940',VA_BANK),' / ',VA_NO,' / ',VA_NM,' / ',IFNULL(DATE_FORMAT(PAY_DT,'%Y-%m-%d %H:%i:%S'),''))
		                         WHEN PAY_MEANS = 'G014_30' THEN CONCAT(CARD_NM,' / ',CASE WHEN CAST(CARD_MIPS AS UNSIGNED) = 0 THEN '일시불' ELSE CONCAT(CAST(CARD_MIPS AS UNSIGNED),'개월') END)
		                         ELSE ''
		                    END
		                WHEN PG_GB IN ('NAVER','KAKAO','PAYCO') THEN
		                    CASE WHEN PAY_MEANS = 'G014_30' THEN CONCAT(CARD_NM,' / ',CASE WHEN CAST(CARD_MIPS AS UNSIGNED) = 0 THEN '일시불' ELSE CONCAT(CAST(CARD_MIPS AS UNSIGNED),'개월') END)
		                         ELSE ''
		                    END
		                ELSE ''
		           END           AS PAY_MEANS_DESC     /*결제수단설명*/
		         , PG_CPN_AMT                          /*PG쿠폰금액*/
		         , NPAY_PNT_AMT                        /*포인트금액(마일리지)*/
		         , CASE WHEN NPAY_PNT_AMT > 0 AND PAY_MEANS IN ('G014_80','G014_81','G014_82','G014_83') THEN FN_GET_CODE_NM('G014',PAY_MEANS)
		                ELSE ''
		           END           AS NPAY_PNT_DESC      /*포인트(마일리지)설명*/
		         , CASE WHEN CASH_AUTH_NO IS NOT NULL AND CASH_TRADE_NO IS NOT NULL THEN 'Y'
		                ELSE 'N'
		           END           AS CASH_RECEIP_REQ_YN /*현금영수증신청여부*/
		    FROM   TB_PAYMENT
		    WHERE  ORD_NO =  5791 /**P*/
		    AND    PAY_GB = 'O' /*주문시결제*/
		    AND    PAY_STAT IN ('G016_30','G016_10') /*결제완료,무통장입금관련*/
		    ;
		   
SELECT *
FROM   TB_PAYMENT
WHERE  ORD_NO =  5791 /**P*/
;

WITH ORIGINAL_ORD AS (
	SELECT ODH.ORD_NO           /*주문번호*/
	     , ODH.ORD_DTL_NO       /*주문상세번호*/
-- 	     , ODH.ORD_AMT          /*주문금액*/
	     , ODH.REAL_ORD_AMT     /*실주문금액*/
-- 	     , (ODH.LIST_PRICE + ODH.OPT_ADD_PRICE) * (ODH.ORD_QTY - ODH.CNCL_RTN_QTY) AS ORD_LIST_AMT /*주문정상금액*/
	     , (ODH.CURR_PRICE + ODH.OPT_ADD_PRICE) * (ODH.ORD_QTY - ODH.CNCL_RTN_QTY) AS ORD_CURR_AMT /*주문상품금액*/
	     , ODH.CPN1_CPN_SQ      /*즉시할인쿠폰번호*/
	     , ODH.CPN1_DC_AMT      /*즉시할인쿠폰금액*/
	     , ODH.TMTB1_SQ         /*다다익선수량할인번호*/
	     , ODH.TMTB1_DC_AMT     /*다다익선수량할인금액*/
	     , ODH.TMTB2_SQ         /*다다익선금액할인번호*/
	     , ODH.TMTB2_DC_AMT     /*다다익선금액할인금액*/
	     , ODH.GOODS_CPN_SQ     /*상품쿠폰번호*/
	     , ODH.GOODS_CPN_DC_AMT /*상품쿠폰할인금액*/
	     , ODH.CART_CPN_SQ      /*장바구니쿠폰번호*/
	     , ODH.CART_CPN_DC_AMT  /*장바구니쿠폰할인금액*/
	     , ODH.PRE_PNT_DC_AMT   /*선포인트할인금액*/
	     , ODH.GFCD_USE_AMT     /*상품권사용금액*/
	     , ODH.PNT_DC_AMT       /*포인트할인금액*/
	     , ODH.SAVE_PNT_AMT     /*적립포인트금액*/
	FROM   TB_ORDER_DETAIL_HST ODH
	WHERE  ODH.ORD_NO = 6005
	AND    ODH.ORD_EXCH_GB = 'O' /*원주문*/
	AND    ODH.ORD_DTL_STAT = 'G013_00' /*주문접수*/
)
, ORIGINAL_DELV_FEE AS (
	SELECT DF.DELV_FEE_CD                                             /*배송비정책코드*/
	     , DF.SUPPLY_COMP_CD                                          /*공급업체코드*/
	     , FN_GET_SUPPLY_COMP_NM(DF.SUPPLY_COMP_CD) AS SUPPLY_COMP_NM /*공급업체명*/
	     , DF.DELV_FEE                                                /*배송비*/
	     , DF.DELV_CPN_SQ                                             /*배송비쿠폰번호*/
	     , DF.DELV_CPN_DC_AMT                                         /*배송비쿠폰할인금액*/
	     , DF.DELV_GFCD_USE_AMT                                       /*배송비상품권사용금액*/
	     , DF.REAL_DELV_AMT                                           /*실배송비금액*/
	FROM   TB_DELIVERY_FEE DF
	WHERE  DF.ORD_NO = 6005
	AND    DF.DELV_FEE_GB = 'G018_10' /*원주문배송비*/
)
, ORIGINAL_PAYMENT AS (
    SELECT ORD_NO
         , CASE WHEN PG_GB = 'KCP' THEN FN_GET_CODE_NM('G014', PAY_MEANS)
	            WHEN PG_GB = 'NAVER' THEN '네이버페이'
	            WHEN PG_GB = 'NAVER_ORDER' THEN '네이버페이주문형'
	            WHEN PG_GB = 'KAKAO' THEN '카카오페이'
	            ELSE ''
	       END           AS PAY_MEANS_NM  /*결제수단명*/
	     , CASE WHEN PG_GB = 'KCP' THEN
	                CASE WHEN PAY_MEANS = 'G014_10' THEN CONCAT(FN_GET_CODE_NM('G940',VA_BANK),' / ',VA_NO)
	                     WHEN PAY_MEANS = 'G014_20' THEN CONCAT(FN_GET_CODE_NM('G940',VA_BANK),' / ',VA_NO,' / ',VA_NM,' / ',DATE_FORMAT(VA_DEADLINE,'%Y-%m-%d %H:%i:%S'))
	                     WHEN PAY_MEANS = 'G014_30' THEN CONCAT(CARD_NM,' / ',CASE WHEN CAST(CARD_MIPS AS UNSIGNED) = 0 THEN '일시불' ELSE CONCAT(CAST(CARD_MIPS AS UNSIGNED),'개월') END)
	                     ELSE ''
	                END
	            WHEN PG_GB IN ('NAVER','KAKAO') THEN
	                CASE WHEN PAY_MEANS = 'G014_10' THEN CONCAT(FN_GET_CODE_NM('G940',VA_BANK),' / ',VA_NO)
	                     WHEN PAY_MEANS = 'G014_30' THEN CONCAT(CARD_NM,' / ',CASE WHEN CAST(CARD_MIPS AS UNSIGNED) = 0 THEN '일시불' ELSE CONCAT(CAST(CARD_MIPS AS UNSIGNED),'개월') END)
	                     ELSE ''
	                END
	       END           AS PAY_MEANS_DESC /*결제수단설명*/
	     , PG_CPN_AMT                      /*PG쿠폰금액*/
	     , NPAY_PNT_AMT                    /*네이버페이포인트금액(마일리지)*/
	     , CASE WHEN CASH_AUTH_NO IS NOT NULL AND CASH_TRADE_NO IS NOT NULL THEN 'Y'
	            ELSE 'N'
	       END           AS CASH_RECEIP_REQ_YN /*현금영수증신청여부*/
	FROM   TB_PAYMENT
	WHERE  ORD_NO = 6005
	AND    PAY_GB = 'O' /*주문시결제*/
	AND    PAY_STAT IN ('G016_30','G016_10') /*결제완료,무통장입금관련*/
)
SELECT OO.ORD_NO                                                   /*주문번호*/
     , SUM(OO.ORD_CURR_AMT)                   AS ORD_CURR_AMT      /*주문상품금액*/
     , IFNULL((SELECT SUM(REAL_DELV_AMT)
               FROM   ORIGINAL_DELV_FEE
              ),0)                            AS REAL_DELV_AMT     /*실배송비금액*/
     , (SELECT CONCAT((SELECT CONCAT('자사 : ',FORMAT(SUM(DF.DELV_FEE),0),'원')
                       FROM   ORIGINAL_DELV_FEE DF
                       WHERE  DF.DELV_FEE_CD = 'WMS' /*자사*/
                      ),
		              (SELECT CONCAT(' / 업체 : ',GROUP_CONCAT(DELV_FEE_DESC SEPARATOR ', '))
		               FROM   (
		                       SELECT CONCAT(DF.SUPPLY_COMP_CD,'/',FN_GET_SUPPLY_COMP_NM(DF.SUPPLY_COMP_CD),' ',FORMAT(SUM(DF.DELV_FEE),0),'원') AS DELV_FEE_DESC
		                       FROM   ORIGINAL_DELV_FEE DF
		                       WHERE  DF.DELV_FEE_CD != 'WMS' /*입점업체*/
		                       GROUP  BY DF.SUPPLY_COMP_CD
		                      ) Z
		              )) AS DELV_FEE_DESC
		FROM   DUAL
	   )                                      AS DELV_FEE_DESC     /*배송비설명*/
	 , (SELECT GROUP_CONCAT(DISTINCT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM,' ',FORMAT(DF.DELV_CPN_DC_AMT,0),'원') SEPARATOR ', ') AS DELV_FEE_CPN_DESC
        FROM   ORIGINAL_DELV_FEE DF
        INNER JOIN TB_CUST_COUPON CC ON DF.DELV_CPN_SQ = CC.CUST_CPN_SQ
        INNER JOIN TB_COUPON C ON CC.CPN_ID = C.CPN_ID
        WHERE  DF.DELV_CPN_SQ > 0
       )                                      AS DELV_FEE_CPN_DESC /*배송비쿠폰설명*/
     , SUM(OO.CPN1_DC_AMT +
           OO.TMTB1_DC_AMT + OO.TMTB2_DC_AMT +
           OO.GOODS_CPN_DC_AMT + OO.CART_CPN_DC_AMT +
           OO.PRE_PNT_DC_AMT)                 AS TOT_DC_AMT        /*총할인금액*/
     , SUM(OO.CPN1_DC_AMT)                    AS CPN1_DC_AMT       /*즉시할인쿠폰금액*/
     , (SELECT GROUP_CONCAT(DISTINCT CONCAT('쿠폰번호 : ',CPN_ID,' / ',CPN_NM) SEPARATOR '!@!') AS CPN_DESC
        FROM   TB_COUPON
        WHERE  CPN_ID IN (SELECT DISTINCT CPN1_CPN_SQ FROM ORIGINAL_ORD)
       )                                      AS CPN1_CPN_DC_DESC  /*즉시할인쿠폰설명*/
     , SUM(OO.TMTB1_DC_AMT + OO.TMTB2_DC_AMT) AS TMTB_DC_AMT       /*다다익선인금액*/
     , SUM(OO.TMTB1_DC_AMT)                   AS TMTB1_DC_AMT      /*다다익선수량할인금액*/
     , (SELECT GROUP_CONCAT(DISTINCT CONCAT('프로모션번호 : ',TMTB_SQ,' / ',TMTB_NM) SEPARATOR '!@!') AS CPN_DESC
        FROM   TB_TMTB
        WHERE  TMTB_SQ IN (SELECT DISTINCT TMTB1_SQ FROM ORIGINAL_ORD)
       )                                      AS TMTB1_DESC        /*다다익선수량할인설명*/
     , SUM(OO.TMTB2_DC_AMT)                   AS TMTB2_DC_AMT      /*다다익선금액할인금액*/
     , (SELECT GROUP_CONCAT(DISTINCT CONCAT('프로모션번호 : ',TMTB_SQ,' / ',TMTB_NM) SEPARATOR '!@!') AS CPN_DESC
        FROM   TB_TMTB
        WHERE  TMTB_SQ IN (SELECT DISTINCT TMTB2_SQ FROM ORIGINAL_ORD)
       )                                      AS TMTB2_DESC        /*다다익선금액할인설명*/
     , SUM(OO.GOODS_CPN_DC_AMT)               AS GOODS_CPN_DC_AMT  /*상품쿠폰할인금액*/
     , (SELECT GROUP_CONCAT(DISTINCT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM) SEPARATOR '!@!') AS CPN_DESC
        FROM   TB_CUST_COUPON CC
        INNER JOIN TB_COUPON C ON CC.CPN_ID = C.CPN_ID
        WHERE  CC.CUST_CPN_SQ IN (SELECT DISTINCT GOODS_CPN_SQ FROM ORIGINAL_ORD)
       )                                      AS GOODS_CPN_DC_DESC /*상품쿠폰할인설명*/
     , SUM(OO.CART_CPN_DC_AMT)                AS CART_CPN_DC_AMT   /*장바구니쿠폰할인금액*/
     , (SELECT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM,
		              ' / 구매금액 ',
		              CASE WHEN C.BUY_LIMIT_AMT = 0 THEN '제한없음'
		                   ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT,0),'원 이상')
		              END,
		              ' / ',
		              FORMAT(C.DC_PVAL,0),
		              CASE WHEN C.DC_WAY = 'G240_10' THEN '원'
		                   ELSE '%'
		              END,
		              ' 할인 / 최대할인 ',
		              CASE WHEN C.MAX_DC_AMT = 0 THEN '제한없음'
		                   ELSE CONCAT(FORMAT(C.MAX_DC_AMT,0),'원')
		              END
		             ) AS CPN_DESC
		FROM   TB_CUST_COUPON CC
		INNER JOIN TB_COUPON C ON CC.CPN_ID = C.CPN_ID
		WHERE  CC.CUST_CPN_SQ = (SELECT DISTINCT CART_CPN_SQ FROM ORIGINAL_ORD)
       )                                      AS CART_CPN_DC_DESC  /*장바구니쿠폰할인설명*/
     , SUM(OO.PRE_PNT_DC_AMT)                 AS PRE_PNT_DC_AMT    /*선포인트할인금액*/
     , SUM(OO.ORD_CURR_AMT)
       + IFNULL((SELECT SUM(REAL_DELV_AMT)
                 FROM   ORIGINAL_DELV_FEE
                ),0)
       - SUM(OO.CPN1_DC_AMT +
             OO.TMTB1_DC_AMT + OO.TMTB2_DC_AMT +
             OO.GOODS_CPN_DC_AMT + OO.CART_CPN_DC_AMT +
             OO.PRE_PNT_DC_AMT)               AS TOT_PAY_AMT       /*총결제금액*/
     , SUM(OO.REAL_ORD_AMT)
       + IFNULL((SELECT SUM(REAL_DELV_AMT)
                 FROM   ORIGINAL_DELV_FEE
                ),0)                          AS REAL_ORD_AMT      /*실결제금액*/
     , MAX(P.PAY_MEANS_NM)                    AS PAY_MEANS_NM      /*결제수단명*/
     , MAX(P.PAY_MEANS_DESC)                  AS PAY_MEANS_DESC     /*결제수단설명*/
	 , SUM(OO.GFCD_USE_AMT)                   AS GFCD_USE_AMT       /*상품권사용금액*/
     , (SELECT GROUP_CONCAT(GFCD_USE_DESC SEPARATOR '!@!') AS GFCD_USE_DESC
		FROM   (
		        SELECT CONCAT(FORMAT(SUM(CGH.GFCD_AMT),0),'원 / ',CG.GFCD_NO) AS GFCD_USE_DESC
		        FROM   TB_CUST_GIFTCARD_HST CGH
		        INNER JOIN TB_CUST_GIFTCARD CG ON CGH.CUST_GFCD_SQ = CG.CUST_GFCD_SQ
		        WHERE  CGH.ORD_NO = OO.ORD_NO
		        AND    CGH.OCCUR_GB = 'G074_12' /*상품권사용*/
		        GROUP  BY CG.GFCD_NO
		       ) Z
       )                                      AS GFCD_USE_DESC      /*상품권사용설명*/
     , SUM(OO.PNT_DC_AMT)                     AS PNT_DC_AMT         /*포인트할인금액*/
     , MAX(P.PG_CPN_AMT)                      AS PG_CPN_AMT         /*PG쿠폰금액(KCP쿠폰금액)*/
     , MAX(P.NPAY_PNT_AMT)                    AS NPAY_PNT_AMT       /*네이버페이포인트금액(마일리지)*/
     , SUM(OO.SAVE_PNT_AMT)                   AS SAVE_PNT_AMT       /*적립포인트금액*/
	 , MAX(P.CASH_RECEIP_REQ_YN)              AS CASH_RECEIP_REQ_YN /*현금영수증신청여부*/
FROM   ORIGINAL_ORD OO
INNER JOIN TB_ORDER O ON OO.ORD_NO = O.ORD_NO
INNER JOIN ORIGINAL_PAYMENT P ON OO.ORD_NO = P.ORD_NO
GROUP  BY OO.ORD_NO
;

-- 상품쿠폰내역 목록 가져오기
SELECT GROUP_CONCAT(DISTINCT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM) SEPARATOR '!@!') AS CPN_DESC
FROM   TB_CUST_COUPON CC
INNER JOIN TB_COUPON C ON CC.CPN_ID = C.CPN_ID
WHERE  CC.CUST_CPN_SQ IN (3465,3575,3298,3456,10,3581)
;

-- 장바구니쿠폰내역 가져오기
SELECT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM,
              ' / 구매금액 ',
              CASE WHEN C.BUY_LIMIT_AMT = 0 THEN '제한없음'
                   ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT,0),'원 이상')
              END,
              ' / ',
              FORMAT(C.DC_PVAL,0),
              CASE WHEN C.DC_WAY = 'G240_10' THEN '원'
                   ELSE '%'
              END,
              ' 할인 / 최대할인 ',
              CASE WHEN C.MAX_DC_AMT = 0 THEN '제한없음'
                   ELSE CONCAT(FORMAT(C.MAX_DC_AMT,0),'원')
              END
             )
FROM   TB_CUST_COUPON CC
INNER JOIN TB_COUPON C ON CC.CPN_ID = C.CPN_ID
WHERE  CC.CUST_CPN_SQ IN (3473)
;

-- 상품권사용내역 목록
SELECT GROUP_CONCAT(GFCD_USE_DESC SEPARATOR '!@!') AS GFCD_USE_DESC
FROM   (
        SELECT CONCAT(FORMAT(SUM(CGH.GFCD_AMT),0),'원 / ',CG.GFCD_NO) AS GFCD_USE_DESC
            FROM   TB_CUST_GIFTCARD_HST CGH
                 , TB_CUST_GIFTCARD CG
            WHERE  CGH.CUST_GFCD_SQ = CG.CUST_GFCD_SQ
            AND    CGH.ORD_NO = 6005
            AND    CGH.OCCUR_GB = 'G074_12' /*상품권사용*/
            GROUP  BY CG.GFCD_NO
           ) Z
;

SELECT DF.DELV_FEE_CD       /*배송정책코드*/
     , DF.DELV_FEE          /*배송비*/
     , DF.DELV_CPN_SQ       /*배송비쿠폰번호*/
     , DF.DELV_CPN_DC_AMT   /*배송비쿠폰할인금액*/
     , DF.DELV_GFCD_USE_AMT /*배송비상품권사용금액*/
     , DF.SUPPLY_COMP_CD    /*공급업체코드*/
FROM   TB_DELIVERY_FEE DF
WHERE  DF.ORD_NO =  6005 /**P*/
AND    DF.DELV_FEE_GB = 'G018_10' /*원주문배송비*/
-- GROUP  BY DF.DELV_FEE_CD
;

WITH ORIGINAL_DELV_FEE AS (
	SELECT DF.DELV_FEE_CD                                             /*배송비정책코드*/
	     , DF.SUPPLY_COMP_CD                                          /*공급업체코드*/
	     , FN_GET_SUPPLY_COMP_NM(DF.SUPPLY_COMP_CD) AS SUPPLY_COMP_NM /*공급업체명*/
	     , DF.DELV_FEE                                                /*배송비*/
	     , DF.DELV_CPN_SQ                                             /*배송비쿠폰번호*/
	     , DF.DELV_CPN_DC_AMT                                         /*배송비쿠폰할인금액*/
	     , DF.DELV_GFCD_USE_AMT                                       /*배송비상품권사용금액*/
	FROM   TB_DELIVERY_FEE DF
	WHERE  DF.ORD_NO = 6005
	AND    DF.DELV_FEE_GB = 'G018_10' /*원주문배송비*/
)
SELECT CONCAT((SELECT CONCAT('자사 : ',FORMAT(SUM(DF.DELV_FEE),0),'원') AS DELV_FEE_DESC
               FROM   ORIGINAL_DELV_FEE DF
               WHERE  DF.DELV_FEE_CD = 'WMS' /*자사*/
              ),
              (SELECT CONCAT(' / 업체 : ',GROUP_CONCAT(DELV_FEE_DESC SEPARATOR ', ')) AS DELV_FEE_DESC
               FROM   (
                       SELECT CONCAT(DF.SUPPLY_COMP_CD,'/',FN_GET_SUPPLY_COMP_NM(DF.SUPPLY_COMP_CD),' ',FORMAT(SUM(DF.DELV_FEE),0),'원') AS DELV_FEE_DESC
                       FROM   ORIGINAL_DELV_FEE DF
                       WHERE  DF.DELV_FEE_CD != 'WMS' /*입점업체*/
                       GROUP  BY DF.SUPPLY_COMP_CD
                      ) Z
              )) AS DELV_FEE_DESC
FROM   DUAL
;

SELECT CONCAT('업체 : ',GROUP_CONCAT(CONCAT(DF.SUPPLY_COMP_CD,'/',FN_GET_SUPPLY_COMP_NM(DF.SUPPLY_COMP_CD),' ',FORMAT(DF.DELV_FEE,0),'원') SEPARATOR ', ')) AS DELV_FEE_DESC
--      , DF.DELV_CPN_SQ       /*배송비쿠폰번호*/
--      , DF.DELV_CPN_DC_AMT   /*배송비쿠폰할인금액*/
--      , DF.DELV_GFCD_USE_AMT /*배송비상품권사용금액*/
FROM   ORIGINAL_DELV_FEE DF
WHERE  DF.DELV_FEE_CD != 'WMS' /*입점업체*/
;

-- 배송비쿠폰내역 가져오기
SELECT GROUP_CONCAT(DISTINCT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM) SEPARATOR ', ') AS CPN_DESC
FROM   TB_CUST_COUPON CC
INNER JOIN TB_COUPON C ON CC.CPN_ID = C.CPN_ID
WHERE  CC.CUST_CPN_SQ IN (13)
;

SELECT *
FROM   TB_CUST_GIFTCARD_HST CGH
WHERE  CGH.ORD_NO = 6005
AND    CGH.OCCUR_GB = 'G074_12' /*상품권사용*/
;
           
SELECT *
FROM   TB_COMMON_CODE
WHERE  CD_GB = 'G720'
;

-- G720_10	판매-결제완료
-- G720_20	판매-배송중
-- G720_30	환입-취소완료
-- G720_40	환입-품절취소
-- G720_50	환입_반품완료
-- G720_60	환입-교환완료

-- G014_10	실시간계좌이체(KCP)
-- G014_20	무통장입금(KCP)
-- G014_30	신용카드(KCP)
-- G014_40	포인트
-- G014_50	쿠폰
-- G014_60	휴대전화(KCP)
-- G014_70	상품권
-- G014_80	네이버포인트
-- G014_81	카카오포인트
-- G014_82	PAYCO포인트
-- G014_83	카카오머니
-- G014_90	외부몰입금

SELECT *
FROM   TB_PAYMENT
WHERE  1 = 1
AND    PG_GB = 'KCP'
AND    PAY_MEANS = 'G014_20'
AND    PAY_GB = 'O'
AND    PAY_STAT IN ('G016_30','G016_10') /*결제완료,무통장입금관련*/
;

SELECT *
FROM   TB_ORDER
WHERE  ORD_NO = 3357
;

-- 5791	2021-09-03 20:10:11

/* order.getOrderDetailList */
		SELECT A.ORD_NO
		     , A.ORD_EXCH_GB
		     , A.EXCH_GB_NM
		     , A.ORD_DTL_NO
		     , A.GOODS_CD
		     , A.GOODS_NM
		     , A.GOODS_TYPE
		     , A.GOODS_TYPE_NM
		     , A.SYS_IMG_NM
		     , A.BRAND_ENM
		     , GROUP_CONCAT(A.ITEM_NM ORDER BY A.ORD_DTL_ITEM_SQ SEPARATOR '!@!') AS ITEM_NM
		     , GROUP_CONCAT(A.ITEM_CD ORDER BY A.ORD_DTL_ITEM_SQ)                 AS ITEM_CD
		     , GROUP_CONCAT(A.OPT_CD ORDER BY A.ORD_DTL_ITEM_SQ)                  AS OPT_CD
		     , GROUP_CONCAT(A.OPT_CD1 ORDER BY A.ORD_DTL_ITEM_SQ)                 AS OPT_CD1
		     , GROUP_CONCAT(A.OPT_CD2 ORDER BY A.ORD_DTL_ITEM_SQ)                 AS OPT_CD2
		     , GROUP_CONCAT(A.ITEM_QTY ORDER BY A.ORD_DTL_ITEM_SQ)                AS ITEM_QTYR
		     , GROUP_CONCAT(A.COLOR_NM ORDER BY A.ORD_DTL_ITEM_SQ)                AS COLOR_NM
		     , A.LIST_PRICE
		     , A.CURR_PRICE
		     , A.ORD_QTY
		     , A.CNCL_RTN_QTY
		     , A.ORD_REQ_CHG_QTY
		     , A.CANCEL_REQUEST_QTY
		     , A.EXCHANGE_REQUEST_QTY
		     , A.RETURN_REQUEST_QTY
		     , SUM(A.OPT_ADD_PRICE)                                               AS OPT_ADD_PRICE
		     , (SELECT CONCAT('쿠폰번호 : ',CPN_ID,' / ',CPN_NM)
		        FROM   TB_COUPON
		        WHERE  CPN_ID = A.CPN1_CPN_SQ)                                    AS CPN1_CPN_DC_DESC
		     , A.CPN1_DC_AMT
		     , (SELECT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM)
		        FROM   TB_CUST_COUPON CC
		             , TB_COUPON C
		        WHERE  CC.CPN_ID = C.CPN_ID
		        AND    CC.CUST_NO = A.CUST_NO
		        AND    CC.CUST_CPN_SQ = A.GOODS_CPN_SQ)                           AS GOODS_CPN_DC_DESC
		     , A.GOODS_CPN_DC_AMT
		     , (SELECT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM,
		                      ' / 구매금액 ',
		                      CASE WHEN C.BUY_LIMIT_AMT = 0 THEN '제한없음'
		                           ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT,0),'원 이상')
		                      END,
		                      ' / ',
		                      FORMAT(C.DC_PVAL,0),
		                      CASE WHEN C.DC_WAY = 'G240_10' THEN '원'
		                           ELSE '%'
		                      END,
		                      ' 할인 / 최대할인 ',
		                      CASE WHEN C.MAX_DC_AMT = 0 THEN '제한없음'
		                           ELSE CONCAT(FORMAT(C.MAX_DC_AMT,0),'원')
		                      END
		                     )
		        FROM   TB_CUST_COUPON CC
		             , TB_COUPON C
		        WHERE  CC.CPN_ID = C.CPN_ID
		        AND    CC.CUST_NO = A.CUST_NO
		        AND    CC.CUST_CPN_SQ = A.CART_CPN_SQ)                            AS CART_CPN_DC_DESC
		     , A.CART_CPN_DC_AMT
		     , (SELECT CONCAT('프로모션번호 : ',TMTB_SQ,' / ',TMTB_NM) FROM TB_TMTB WHERE TMTB_SQ = A.TMTB1_SQ) AS TMTB1_DESC
		     , A.TMTB1_DC_AMT
		     , (SELECT CONCAT('프로모션번호 : ',TMTB_SQ,' / ',TMTB_NM) FROM TB_TMTB WHERE TMTB_SQ = A.TMTB2_SQ) AS TMTB2_DESC
		     , A.TMTB2_DC_AMT
		     , A.PNT_DC_AMT
		     , A.PRE_PNT_DC_AMT
		     , A.SAVE_PNT_AMT
		     , A.GFCD_USE_AMT
		     , (SELECT GROUP_CONCAT(GFCD_USE_DESC SEPARATOR '!@!') AS GFCD_USE_DESC
		        FROM   (
		                SELECT CONCAT(FORMAT(SUM(CGH.GFCD_AMT),0),'원 / ',CG.GFCD_NO) AS GFCD_USE_DESC
		                FROM   TB_CUST_GIFTCARD_HST CGH
		                     , TB_CUST_GIFTCARD CG
		                WHERE  CGH.CUST_GFCD_SQ = CG.CUST_GFCD_SQ
		                AND    CGH.ORD_NO = A.ORD_NO
		                GROUP  BY CG.GFCD_NO
		               ) Z
		       )                                                                  AS GFCD_USE_DESC
		     , A.ORD_AMT
		     , A.REAL_ORD_AMT
		     , A.CNCL_RTN_AMT
		     , A.ORD_DTL_STAT
		     , A.ORD_DTL_STAT_NM
		     , A.SOLDOUT_YN
		     , A.SOLDOUT_REG_NO
		     , A.SOLDOUT_MEMO
		     , A.INVOICE_NO
		     , A.DELV_LOC_CD
		     , A.DELV_ADDR_SQ
		     , A.RETURNABLE_YN
		     , A.CHANGEABLE_YN
		     , A.DELV_FEE_CD
		     , A.DELV_FEE_CD_GRP
		     , A.BRAND_GROUP_NM
		     , A.SHIP_COMP_CD
		     , A.SHIP_COMP_NM
		     , A.DELV_LOC_NM
		     , A.PG_GB
		     , A.SUPPLY_COMP_NM
		     , CASE WHEN IFNULL((SELECT COUNT(*)
		                         FROM   TB_GIFTCARD_RECEIPT
		                         WHERE  ORD_NO = A.ORD_NO 
		                         AND    TRADE_NO IS NOT NULL
		                         AND    AUTH_NO IS NOT NULL
		                         AND    AMT > 0
		                        ),0) = 0 THEN 'N' ELSE 'Y' END AS GCCR_REQ_YN /*상품권현금영수증신청여부*/
		FROM   (
		        SELECT O.CUST_NO
		             , OD.ORD_NO
		             , OD.ORD_EXCH_GB
		             , CASE WHEN OD.ORD_EXCH_GB = 'O' THEN '주문' ELSE '교환' END AS EXCH_GB_NM
		             , OD.ORD_DTL_NO
		             , ODI.ORD_DTL_ITEM_SQ
		             , G1.GOODS_CD
		             , FN_GET_GOODS_NM(G1.GOODS_NM, G1.GOODS_GB, G1.FOREIGN_BUY_YN, G1.PARALLEL_IMPORT_YN, G1.ORDER_MADE_YN) AS GOODS_NM
		             , G1.GOODS_TYPE
		             , FN_GET_CODE_NM('G056', G1.GOODS_TYPE)   AS GOODS_TYPE_NM
		             , CASE WHEN G1.GOODS_TYPE != 'G056_S' AND G1.SELF_GOODS_YN = 'Y' THEN (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G1.GOODS_CD AND COLOR_CD = ODI.OPT_CD1 AND DEFAULT_IMG_YN = 'Y')
		                    ELSE (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G1.GOODS_CD AND COLOR_CD = IFNULL(G1.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y')
		               END                                                        AS SYS_IMG_NM
		             , G1.BRAND_CD
		             , (SELECT Z.BRAND_ENM
		                FROM   TB_BRAND Z
		                WHERE  Z.BRAND_CD = G1.BRAND_CD)                          AS BRAND_ENM
		             , G2.GOODS_CD                                                AS ITEM_CD
		             , G2.GOODS_NM                                                AS ITEM_NM
		             , IFNULL((SELECT COLOR_KNM
		                       FROM   TB_COLOR
		                       WHERE  COLOR_CD = IFNULL(NULLIF(ODI.OPT_CD1,''), G1.MAIN_COLOR_CD)
		                       AND    USE_YN  = 'Y'), ODI.OPT_CD1)                AS COLOR_NM
		             , ODI.OPT_CD
		             , ODI.OPT_CD1
		             , ODI.OPT_CD2
		             , ODI.ITEM_QTY
		             , ODI.ITEM_PRICE
		             , OD.LIST_PRICE
		             , OD.CURR_PRICE
		             , OD.ORD_QTY
		             , OD.CNCL_RTN_QTY
		             , CASE WHEN OCD.ORD_REQ_CHG_QTY IS NULL THEN 0
		                    ELSE OCD.ORD_REQ_CHG_QTY
		               END                                                        AS ORD_REQ_CHG_QTY
		             , CASE WHEN OCD.CANCEL_REQUEST_QTY IS NULL THEN 0
		                    ELSE OCD.CANCEL_REQUEST_QTY
		               END                                                        AS CANCEL_REQUEST_QTY
		             , CASE WHEN OCD.EXCHANGE_REQUEST_QTY IS NULL THEN 0
		                    ELSE OCD.EXCHANGE_REQUEST_QTY
		               END                                                        AS EXCHANGE_REQUEST_QTY
		             , CASE WHEN OCD.RETURN_REQUEST_QTY IS NULL THEN 0
		                    ELSE OCD.RETURN_REQUEST_QTY
		               END                                                        AS RETURN_REQUEST_QTY
		             , ODI.OPT_ADD_PRICE
		             , OD.CPN1_CPN_SQ
		             , OD.CPN1_DC_AMT
		             , OD.GOODS_CPN_SQ
		             , OD.GOODS_CPN_DC_AMT
		             , OD.CART_CPN_SQ
		             , OD.CART_CPN_DC_AMT
		             , OD.TMTB1_SQ
		             , OD.TMTB1_DC_AMT
		             , OD.TMTB2_SQ
		             , OD.TMTB2_DC_AMT
		             , OD.PNT_DC_AMT
		             , OD.PRE_PNT_DC_AMT
		             , OD.SAVE_PNT_AMT
		             , OD.GFCD_USE_AMT
		             , OD.ORD_AMT
		             , OD.REAL_ORD_AMT
		             , OD.CNCL_RTN_AMT
		             , OD.ORD_DTL_STAT
		             , FN_GET_CODE_NM('G013', OD.ORD_DTL_STAT)                    AS ORD_DTL_STAT_NM
		             , OD.SOLDOUT_YN
		             , OD.SOLDOUT_REG_NO
		             , OD.SOLDOUT_MEMO
		             , OD.INVOICE_NO
		             , OD.DELV_LOC_CD
		             , OD.DELV_ADDR_SQ
		             , OD.RETURNABLE_YN
		             , OD.CHANGEABLE_YN
		             , OD.DELV_FEE_CD
		             , CASE WHEN G1.SELF_GOODS_YN = 'Y' THEN 'WMS'
		                    ELSE OD.DELV_FEE_CD
		               END                                                        AS DELV_FEE_CD_GRP
		             , CASE WHEN BG.DISP_NM_LANG = 'EN' THEN BG.BRAND_GROUP_ENM
		                    ELSE BG.BRAND_GROUP_KNM
		               END                                                        AS BRAND_GROUP_NM
		             , OD.SHIP_COMP_CD
		             , (SELECT SHIP_COMP_NM
		                FROM   TB_SHIP_COMPANY SC
		                WHERE  SC.SHIP_COMP_CD = OD.SHIP_COMP_CD)                 AS SHIP_COMP_NM
		             , (SELECT DL.DELV_LOC_NM
		                FROM   TB_DELIVERY_LOC DL
		                WHERE  DL.DELV_LOC_CD = OD.DELV_LOC_CD
		                AND    DL.USE_YN = 'Y'
		                LIMIT  1)                                                 AS DELV_LOC_NM
		             , P.PG_GB
		             , (SELECT Z.SUPPLY_COMP_NM
		                FROM   TB_SUPPLY_COMPANY Z
		                WHERE  Z.SUPPLY_COMP_CD = OD.SUPPLY_COMP_CD)              AS SUPPLY_COMP_NM
		        FROM   TB_ORDER O
		        INNER JOIN TB_ORDER_DETAIL OD
		           ON O.ORD_NO = OD.ORD_NO
		        INNER JOIN TB_GOODS G1
		           ON OD.GOODS_CD = G1.GOODS_CD
		        INNER JOIN TB_BRAND B
		           ON B.BRAND_CD = G1.BRAND_CD
		        INNER JOIN TB_BRAND_GROUP BG
		           ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		          AND BG.USE_YN = 'Y'
		        INNER JOIN TB_ORDER_DETAIL_ITEM ODI
		           ON OD.ORD_NO = ODI.ORD_NO
		          AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		        INNER JOIN TB_GOODS G2
		           ON ODI.ITEM_CD = G2.GOODS_CD
		        INNER JOIN TB_PAYMENT P
		           ON O.ORD_NO = P.ORD_NO
		          AND P.PAY_GB = 'O'
		          AND P.PAY_STAT IN ('G016_10', 'G016_30')
		        INNER JOIN TB_DELIVERY_ADDR DA
		           ON OD.DELV_ADDR_SQ = DA.DELV_ADDR_SQ
		        LEFT OUTER JOIN (SELECT OD.ORD_DTL_NO
		                              , SUM(CASE OC.WD_BF_SEND_YN
		                                         WHEN 'Y' THEN 0
		                                         ELSE OCD.CHG_QTY
		                                    END) AS ORD_REQ_CHG_QTY
		                              , SUM(CASE OCD.CHG_STAT
		                                         WHEN 'G685_10' THEN OCD.CHG_QTY
		                                         WHEN 'G685_11' THEN OCD.CHG_QTY
		                                         ELSE 0
		                                    END) AS CANCEL_REQUEST_QTY
		                              , SUM(CASE WHEN OCD.CHG_STAT = 'G685_20' THEN OCD.CHG_QTY
		                                         WHEN OCD.CHG_STAT = 'G685_21' THEN OCD.CHG_QTY
		                                         WHEN OCD.CHG_STAT = 'G685_30' AND OC.CHG_GB = 'G680_40' THEN OCD.CHG_QTY
		                                         WHEN OCD.CHG_STAT = 'G685_31' AND OC.CHG_GB = 'G680_40' THEN OCD.CHG_QTY
		                                         WHEN OCD.CHG_STAT = 'G685_32' AND OC.CHG_GB = 'G680_40' THEN OCD.CHG_QTY
		                                         ELSE 0
		                                    END) AS EXCHANGE_REQUEST_QTY
		                              , SUM(CASE WHEN OCD.CHG_STAT = 'G685_50' THEN OCD.CHG_QTY
		                                         WHEN OCD.CHG_STAT = 'G685_51' THEN OCD.CHG_QTY
		                                         WHEN OCD.CHG_STAT = 'G685_33' THEN OCD.CHG_QTY
		                                         WHEN OCD.CHG_STAT = 'G685_30' AND OC.CHG_GB = 'G680_30' THEN OCD.CHG_QTY
		                                         WHEN OCD.CHG_STAT = 'G685_31' AND OC.CHG_GB = 'G680_30' THEN OCD.CHG_QTY
		                                         WHEN OCD.CHG_STAT = 'G685_32' AND OC.CHG_GB = 'G680_30' THEN OCD.CHG_QTY
		                                         ELSE 0
		                                    END) AS RETURN_REQUEST_QTY
		                         FROM   TB_ORDER_CHANGE_DETAIL OCD
		                         INNER JOIN TB_ORDER_CHANGE OC
		                            ON OC.ORD_CHG_SQ = OCD.ORD_CHG_SQ
		                         INNER JOIN TB_ORDER_DETAIL OD
		                            ON OCD.ORD_DTL_NO = OD.ORD_DTL_NO
		                         WHERE 1 = 1
		                           AND OD.ORD_NO =  6005 /**P*/
		                           AND OCD.CHG_STAT IN ('G685_10', 'G685_11', 'G685_20', 'G685_21', 'G685_30', 'G685_31', 'G685_32', 'G685_33', 'G685_50', 'G685_51')
		                           AND OCD.DEL_YN = 'N'
		                         GROUP BY OCD.ORD_DTL_NO
		                        ) OCD
		                     ON OD.ORD_DTL_NO = OCD.ORD_DTL_NO
		        WHERE 1 = 1
		          AND OD.ORD_NO =  6005 /**P*/
		          AND OD.ORD_DTL_STAT NOT IN ('G013_00', 'G013_25', 'G013_97')
		       ) A
		GROUP  BY A.ORD_NO
		     , A.ORD_EXCH_GB
		     , A.EXCH_GB_NM
		     , A.ORD_DTL_NO
		     , A.GOODS_CD
		     , A.GOODS_NM
		     , A.GOODS_TYPE
		     , A.GOODS_TYPE_NM
		     , A.SYS_IMG_NM
		     , A.BRAND_ENM
		     , A.LIST_PRICE
		     , A.CURR_PRICE
		     , A.ORD_QTY
		     , A.CNCL_RTN_QTY
		     , A.ORD_REQ_CHG_QTY
		     , A.CANCEL_REQUEST_QTY
		     , A.EXCHANGE_REQUEST_QTY
		     , A.RETURN_REQUEST_QTY
		     , A.CPN1_DC_AMT
		     , A.GOODS_CPN_DC_AMT
		     , A.CART_CPN_DC_AMT
		     , A.TMTB1_DC_AMT
		     , A.TMTB2_DC_AMT
		     , A.PNT_DC_AMT
		     , A.PRE_PNT_DC_AMT
		     , A.SAVE_PNT_AMT
		     , A.GFCD_USE_AMT
		     , A.ORD_AMT
		     , A.REAL_ORD_AMT
		     , A.CNCL_RTN_AMT
		     , A.ORD_DTL_STAT
		     , A.ORD_DTL_STAT_NM
		     , A.SOLDOUT_YN
		     , A.SOLDOUT_REG_NO
		     , A.SOLDOUT_MEMO
		     , A.INVOICE_NO
		     , A.DELV_LOC_CD
		     , A.DELV_ADDR_SQ
		     , A.RETURNABLE_YN
		     , A.CHANGEABLE_YN
		     , A.DELV_FEE_CD
		     , A.DELV_FEE_CD_GRP
		     , A.BRAND_GROUP_NM
		     , A.SHIP_COMP_CD
		     , A.SHIP_COMP_NM
		     , A.DELV_LOC_NM
		     , A.PG_GB
		     , A.SUPPLY_COMP_NM
		 ORDER BY A.ORD_NO
		     , A.ORD_DTL_NO
;

SELECT OD.*
FROM   TB_ORDER O
-- WHERE  ORD_NO = 353 2223
INNER JOIN TB_ORDER_DETAIL OD
       ON O.ORD_NO = OD.ORD_NO
    INNER JOIN TB_GOODS G1
       ON OD.GOODS_CD = G1.GOODS_CD
    INNER JOIN TB_BRAND B
       ON B.BRAND_CD = G1.BRAND_CD
    INNER JOIN TB_BRAND_GROUP BG
       ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
      AND BG.USE_YN = 'Y'
    INNER JOIN TB_ORDER_DETAIL_ITEM ODI
       ON OD.ORD_NO = ODI.ORD_NO
      AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
    INNER JOIN TB_GOODS G2
       ON ODI.ITEM_CD = G2.GOODS_CD
    INNER JOIN TB_PAYMENT P
       ON O.ORD_NO = P.ORD_NO
      AND P.PAY_GB = 'O'
      AND P.PAY_STAT IN ('G016_10', 'G016_30')
    INNER JOIN TB_DELIVERY_ADDR DA
       ON OD.DELV_ADDR_SQ = DA.DELV_ADDR_SQ
WHERE  1 = 1
AND    OD.GOODS_CPN_SQ IS NOT NULL
AND    OD.GOODS_CPN_SQ > 0
AND    OD.CART_CPN_SQ IS NOT NULL
AND    OD.CART_CPN_SQ > 0
-- AND    TMTB1_SQ > 0
AND    OD.ORD_DTL_STAT = 'G013_20'
-- AND    OD.GOODS_CPN_SQ IN (SELECT CPN_ID FROM TB_COUPON WHERE CPN_TYPE = 'G230_11')
-- AND    OD.CART_CPN_SQ IN (SELECT CPN_ID FROM TB_COUPON WHERE CPN_TYPE = 'G230_20')
;

SELECT *
FROM   TB_COMMON_CODE
WHERE  CD_GB = 'G230'
;

SELECT *
FROM   TB_COUPON
WHERE  CPN_TYPE IN ('G230_11','G230_20')
;

SELECT *
FROM   TB_CUST_COUPON
;

SELECT *
FROM   TB_ORDER
;

-- G230_10	즉시할인쿠폰
-- G230_11	상품쿠폰
-- G230_12	브랜드쿠폰
-- G230_13	카테고리쿠폰
-- G230_14	공급처쿠폰
-- G230_20	주문서쿠폰
-- G230_30	배송비쿠폰

/* order.getOrderGiftcardHstList */
SELECT GROUP_CONCAT(GFCD_USE_DESC SEPARATOR '!@!')
FROM   (
		SELECT CONCAT(FORMAT(SUM(CGH.GFCD_AMT),0),'원 / ',CG.GFCD_NO) AS GFCD_USE_DESC
		  FROM TB_ORDER_DETAIL OD
		 INNER JOIN TB_CUST_GIFTCARD_HST CGH
		    ON OD.ORD_DTL_NO = CGH.ORD_DTL_NO
		   AND OD.ORD_NO = CGH.ORD_NO
		 INNER JOIN TB_CUST_GIFTCARD CG
		    ON CGH.CUST_GFCD_SQ = CG.CUST_GFCD_SQ
		 WHERE OD.ORD_NO =  1433 /**P*/
		 GROUP  BY CG.GFCD_NO
) Z
		   ;
		   
SELECT ORD_NO, ORD_DTL_NO, COUNT(DISTINCT CUST_GFCD_SQ) AS CNT
FROM   TB_CUST_GIFTCARD_HST
GROUP  BY ORD_NO, ORD_DTL_NO
HAVING COUNT(DISTINCT CUST_GFCD_SQ) > 1
;

SELECT *
FROM   TB_COMMON_CODE
WHERE  CD_GB = 'G016'
;

/* order.getDeliveryFeeList */
		SELECT Z.*
		  FROM (
		        SELECT DF.DELV_FEE_GB
		             , FN_GET_CODE_NM('G018', DF.DELV_FEE_GB)       AS DELV_FEE_GB_NM
		             , DF.DELV_FEE
		             , DF.REAL_DELV_AMT
		             , DF.DELV_USAC_YN
		             , DATE_FORMAT(DF.DELV_USAC_DT, '%Y%m%d%H%i%S') AS DELV_USAC_DT
		             , DF.SUPPLY_COMP_CD
		             , DF.DELV_FEE_SQ
		             , DF.DELV_CPN_SQ
		             , DF.DELV_CPN_DC_AMT
		             , DF.DELV_GFCD_USE_AMT
		             , DF.DELV_FEE_CD
		             , CASE WHEN DF.DELV_FEE_CD IN ('WMS')
		                    THEN '자사'
		                    ELSE (SELECT SUPPLY_COMP_NM FROM TB_SUPPLY_COMPANY X WHERE X.SUPPLY_COMP_CD = DF.SUPPLY_COMP_CD)
		                    END  SUPPLY_COMP_NM
		          FROM TB_DELIVERY_FEE DF
		         WHERE 1 = 1
		           AND DF.ORD_NO =  6005 /**P*/
		           AND DF.DELV_FEE_GB = 'G018_10' /*원주문배송비*/
		        UNION  ALL
		        SELECT DF.DELV_FEE_GB
		             , FN_GET_CODE_NM('G018', DF.DELV_FEE_GB)       AS DELV_FEE_GB_NM
		             , DF.DELV_FEE
		             , DF.REAL_DELV_AMT
		             , DF.DELV_USAC_YN
		             , DATE_FORMAT(DF.DELV_USAC_DT, '%Y%m%d%H%i%S') AS DELV_USAC_DT
		             , DF.SUPPLY_COMP_CD
		             , DF.DELV_FEE_SQ
		             , DF.DELV_CPN_SQ
		             , DF.DELV_CPN_DC_AMT
		             , DF.DELV_GFCD_USE_AMT
		             , DF.DELV_FEE_CD
		             , CASE WHEN DF.DELV_FEE_CD IN ('WMS')
		                    THEN '자사'
		                    ELSE (SELECT SUPPLY_COMP_NM FROM TB_SUPPLY_COMPANY X WHERE X.SUPPLY_COMP_CD = DF.SUPPLY_COMP_CD)
		                    END  SUPPLY_COMP_NM
		          FROM TB_DELIVERY_FEE DF
		         WHERE 1 = 1
		           AND DF.ORD_NO =  6005 /**P*/
		           AND EXISTS (SELECT 1
		                        FROM TB_ORDER_CHANGE_DETAIL OCD
		                       WHERE DF.ORD_CHG_SQ = OCD.ORD_CHG_SQ
		                         AND OCD.CHG_STAT IN ('G685_40', 'G685_60'))
		  ) Z
		 GROUP BY Z.DELV_FEE_GB
		        , Z.DELV_FEE_GB_NM
		        , Z.DELV_FEE
		        , Z.REAL_DELV_AMT
		        , Z.DELV_USAC_YN
		        , Z.DELV_USAC_DT
		        , Z.SUPPLY_COMP_CD
		        , Z.SUPPLY_COMP_NM
		        , Z.DELV_FEE_SQ
		        , Z.DELV_CPN_SQ
		        , Z.DELV_CPN_DC_AMT
		        , Z.DELV_GFCD_USE_AMT
		        , Z.DELV_FEE_CD
;

/* order.getOrderPaymentBasicInfoList */
		SELECT P.ESCROW_YN
		     , CASE WHEN O.MALL_GB IN ('G011_20')
		            THEN ''
		            ELSE P.PG_GB
		            END PG_GB
		     , CASE WHEN O.MALL_GB IN ('G011_20')
		            THEN ''
		            ELSE P.PAY_MEANS
		            END  PAY_MEANS
		     , CASE WHEN O.MALL_GB IN ('G011_20')
		            THEN ''
		            ELSE FN_GET_CODE_NM('G014', P.PAY_MEANS)
		            END  PAY_MEANS_NM
		     , CASE WHEN P.PAY_MEANS IN ('G014_20')
		            THEN P.VA_BANK
		            ELSE ''
		            END  VA_BANK
		     , CASE WHEN P.PAY_MEANS = 'G014_20' THEN FN_GET_CODE_NM('G940', P.VA_BANK)
		            WHEN P.PAY_MEANS = 'G014_10' THEN ''
		            ELSE P.CARD_NM
		        END		                    CARD_NM
		     , P.PG_TRADE_NO
		     , P.PG_TID
		     , P.VA_NO
		     , DATE_FORMAT(P.PAY_DT, '%Y%m%d%H%i%S')      AS PAY_DT
		     , P.PAY_STAT
		     , FN_GET_CODE_NM('G016', P.PAY_STAT)         AS PAY_STAT_NM
		     , P.PAY_AMT
		     , P.PG_CPN_AMT
		     , P.NPAY_PNT_AMT
		     , DATE_FORMAT(P.VA_DEADLINE, '%Y-%m-%d') AS VA_DEADLINE
		     , CARD_MIPS
		     , CARD_TYPE
		     , CASH_AUTH_NO
		     , CASH_TRADE_NO
		  FROM TB_PAYMENT P
		 INNER JOIN TB_ORDER O
		    ON P.ORD_NO = O.ORD_NO
		 WHERE 1 = 1
		   AND P.ORD_NO =  169 /**P*/
		   AND P.PAY_STAT IN ('G016_30', 'G016_10') /*결제완료,무통장입금관련*/
		 ORDER BY P.REG_DT
		 ;

-- G016_00	결제대기
-- G016_10	무통장입금전
-- G016_30	결제완료
-- G016_97	결제성공후DB실패
-- G016_98	결제전취소
-- G016_99	결제취소

SELECT *
FROM   TB_PAYMENT
WHERE  ORD_NO = 169
;

SELECT ORD_NO, COUNT(*) AS CNT
FROM   TB_PAYMENT
GROUP  BY ORD_NO
HAVING COUNT(*) > 1
;

SELECT CASE WHEN P.PG_GB = 'KCP' THEN FN_GET_CODE_NM('G014', P.PAY_MEANS)
            WHEN P.PG_GB = 'NAVER' THEN '네이버페이'
            WHEN P.PG_GB = 'NAVER_ORDER' THEN '네이버페이주문형'
            WHEN P.PG_GB = 'KAKAO' THEN '카카오페이'
            ELSE ''
       END           AS PAY_MEANS_NM  /*결제수단명*/
     , CASE WHEN P.PG_GB = 'KCP' THEN
                CASE WHEN P.PAY_MEANS = 'G014_10' THEN CONCAT(FN_GET_CODE_NM('G940',P.VA_BANK),' / ',P.VA_NO)
                     WHEN P.PAY_MEANS = 'G014_20' THEN CONCAT(FN_GET_CODE_NM('G940',P.VA_BANK),' / ',P.VA_NO,' / ',P.VA_NM,' / ',DATE_FORMAT(P.VA_DEADLINE,'%Y-%m-%d %H:%i:%S'))
                     WHEN P.PAY_MEANS = 'G014_30' THEN CONCAT(P.CARD_NM,' / ',CASE WHEN CAST(P.CARD_MIPS AS UNSIGNED) = 0 THEN '일시불' ELSE CONCAT(CAST(P.CARD_MIPS AS UNSIGNED),'개월') END)
                     ELSE ''
                END
            WHEN P.PG_GB IN ('NAVER','KAKAO') THEN
                CASE WHEN P.PAY_MEANS = 'G014_10' THEN CONCAT(FN_GET_CODE_NM('G940',P.VA_BANK),' / ',P.VA_NO)
                     WHEN P.PAY_MEANS = 'G014_30' THEN CONCAT(P.CARD_NM,' / ',CASE WHEN CAST(P.CARD_MIPS AS UNSIGNED) = 0 THEN '일시불' ELSE CONCAT(CAST(P.CARD_MIPS AS UNSIGNED),'개월') END)
                     ELSE ''
                END
       END           AS PAY_MEANS_DESC /*결제수단설명*/
     , PG_CPN_AMT                      /*PG쿠폰금액*/
     , NPAY_PNT_AMT                    /*네이버페이포인트금액(마일리지)*/
     , CASE WHEN CASH_AUTH_NO IS NOT NULL AND CASH_TRADE_NO IS NOT NULL THEN 'Y'
            ELSE 'N'
       END           AS CASH_RECEIP_REQ_YN /*현금영수증신청여부*/
FROM   TB_PAYMENT P
WHERE  P.ORD_NO = 6005
AND    P.PAY_GB = 'O' /*주문시결제*/
AND    P.PAY_STAT IN ('G016_30','G016_10') /*결제완료,무통장입금관련*/
;

style24-core Order.java
private Integer ordNo;
private int ordCurrAmt;			/*주문상품금액*/
private int realDelvAmt;		/*실배송비금액*/
private String delvFeeDesc;		/*배송비설명*/
private String delvFeeCpnDesc;	/*배송비쿠폰설명*/
private int totDcAmt;			/*총할인금액*/
private int cpn1DcAmt;			/*즉시할인쿠폰금액*/
private String cpn1CpnDcDesc;	/*즉시할인쿠폰설명*/
private int tmtbDcAmt;			/*다다익선인금액*/
private int tmtb1DcAmt;			/*다다익선수량할인금액*/
private String tmtb1Desc;		/*다다익선수량할인설명*/
private int tmtb2DcAmt;			/*다다익선금액할인금액*/
private String tmtb2Desc;		/*다다익선금액할인설명*/
private int goodsCpnDcAmt;		/*상품쿠폰할인금액*/
private String goodsCpnDcDesc;	/*상품쿠폰할인설명*/
private int cartCpnDcAmt;		/*장바구니쿠폰할인금액*/
private String cartCpnDcDesc;	/*장바구니쿠폰할인설명*/
private int prePntDcAmt;		/*선포인트할인금액*/
private int totPayAmt;			/*총결제금액*/
private int realOrdAmt;			/*실결제금액*/
private String payMeansNm;		/*결제수단명*/
private String payMeansDesc;	/*결제수단설명*/
private int gfcdUseAmt;			/*상품권사용금액*/
private String gfcdUseDesc;		/*상품권사용설명*/
private int pntDcAmt;			/*포인트할인금액*/
private int pgCpnAmt;			/*PG쿠폰금액(KCP쿠폰금액)*/
private int npayPntAmt;			/*네이버페이포인트금액(마일리지)*/
private int savePntAmt;			/*적립포인트금액*/
private String cashReceipReqYn;	/*현금영수증신청여부*/


<h4>주문결제내역</h4>
				<table class="frmStyle">
					<colgroup>
						<col width="10%"/>
						<col width="10%"/>
						<col width="20%"/>
						<col width="10%"/>
						<col width="10%"/>
						<col/>
					</colgroup>
					<thead>
						<tr>
							<th>구분</th>
							<th>합계</th>
							<th>내역</th>
							<th>구분</th>
							<th>합계</th>
							<th>내역</th>
						</tr>
					</thead>
					<tbody>
						<tr>
							<th>총결제금액(A+B+C)</th>
							<td class="totalPayAmt"></td>
							<td></td>
							<th>주문금액(A)</th>
							<td class="currSumAmt"></td>
							<td></td>
						</tr>
						<tr>
							<th class="pgGbNm"></th>
							<td class="realOrdSumAmt"></td>
							<td class="payMeansDesc"></td>
							<th>배송비 합산(B)</th>
							<td class="realDelvSumAmt"></td>
							<td>
								<ul class="notice">
									<li>배송비 : <span class="delvSumAmt"></span></li>
									<li>배송비 쿠폰 : <span class="delvCpnDcAmt"></span></li>
								</ul>
							</td>
						</tr>
						<tr>
							<th>상품권</th>
							<td class="gfcdUseSumAmt"></td>
							<td>
								<ul class="notice gfcdUseDesc">
								</ul>
							</td>
							<th>할인금액 총합(C)</th>
							<td class="cRed totDcAmt"></td>
							<td></td>
						</tr>
						<tr>
							<th>포인트</th>
							<td class="pntDcSumAmt"></td>
							<td></td>
							<th><i class="fa fa-level-up fa-rotate-90" aria-hidden="true"></i>&nbsp;즉시할인쿠폰</th>
							<td class="cRed cpn1DcSumAmt"></td>
							<td>
								<ul class="notice cpn1CpnDcDesc">
								</ul>
							</td>
						</tr>
						<tr>
							<th>KCP쿠폰</th>
							<td class="allianceDcAmt"></td>
							<td></td>
							<th><i class="fa fa-level-up fa-rotate-90" aria-hidden="true"></i>&nbsp;다다익선할인</th>
							<td class="tmtbDcSumAmt"></td>
							<td>
								<ul class="notice tmtbDcDesc">
								</ul>
							</td>
						</tr>
						<tr>
							<th>마일리지</th>
							<td class="mileageDcAmt"></td>
							<td></td>
							<th><i class="fa fa-level-up fa-rotate-90" aria-hidden="true"></i>&nbsp;상품쿠폰&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</th>
							<td class="cRed goodsCpnDcSumAmt"></td>
							<td>
								<ul class="notice goodsCpnDcDesc">
								</ul>
							</td>
						</tr>
						<tr>
							<th>적립포인트</th>
							<td class="savePntSumAmt"></td>
							<td></td>
							<th><i class="fa fa-level-up fa-rotate-90" aria-hidden="true"></i>&nbsp;장바구니쿠폰</th>
							<td class="cRed cartCpnDcSumAmt"></td>
							<td>
								<ul class="notice cartCpnDcDesc">
								</ul>
							</td>
						</tr>
						<tr>
							<th>현금영수증신청여부</th>
							<td class="gccrReqYn"></td>
							<td></td>
							<th><i class="fa fa-level-up fa-rotate-90" aria-hidden="true"></i>&nbsp;선포인트할인</th>
							<td class="cRed prePntDcSumAmt"></td>
							<td></td>
						</tr>
					</tbody>
				</table>