style24.doc/산출물/4.구축/마이그레이션/쿼리/05.주문.sql

##### 주문 #####

-- 배송정보
TRUNCATE TABLE tb_delivery_addr;

ALTER TABLE tb_delivery_addr AUTO_INCREMENT = 1;

INSERT INTO tb_delivery_addr (RECIP_NM, RECIP_PHNNO, RECIP_TELNO, RECIP_ZIPCODE, RECIP_BASE_ADDR, RECIP_DTL_ADDR, DELV_MEMO, OLD_ORD_NO, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT
     Receiver AS RECIP_NM
    , ReceiverCellNum AS RECIP_PHNNO
    , ReceiverTelNum AS RECIP_TELNO
    , IF(LENGTH(REPLACE(A.DeliveryPostalCode, '-', '')) > 6, NULL, REPLACE(A.DeliveryPostalCode, '-', '')) AS RECIP_ZIPCODE
    , CASE WHEN A.DeliveryAddr1 IS NOT NULL AND A.DeliveryAddr1 != '' THEN A.DeliveryAddr1 ELSE A.DeliveryAddrNew END AS RECIP_BASE_ADDR
    , A.DeliveryAddr2 AS RECIP_DTL_ADDR
    , A.DeliveryMemo AS DELV_MEMO
    , A.OrderNo AS OLD_ORD_NO
    , REG_NO
    , REG_DT
    , UPD_NO
    , UPD_DT
FROM old_ord_order A, tb_order B
WHERE A.OrderNo = B.ORD_NO
; -- 2분13초

SELECT MAX(DELV_ADDR_SQ) FROM tb_delivery_addr;
ALTER TABLE tb_delivery_addr AUTO_INCREMENT = 1522084;

-- 주문상세
TRUNCATE TABLE tb_order_detail;

INSERT INTO tb_order_detail
SELECT
    ORD_DTL_NO
  -- , ProductNo -- 삭제
  , ORD_NO             -- 주문번호
  , ORD_EXCH_GB
  , ORD_DTL_STAT       -- 주문접수
  , ORG_ORD_DTL_NO
  , SUPPLY_COMP_CD
  , GOODS_CD
  , DEAL_GOODS_CD
  , FORMAL_GB
  , GOODS_TYPE
  , LIST_PRICE         -- 정상가
  , CURR_PRICE         -- 현재판매가
  , IF(DC_RATE < 0, 0, DC_RATE) AS DC_RATE
  , OPT_ADD_PRICE
  , ORD_QTY
  , ORD_AMT            -- 주문금액(=(현재판매가 + 옵션추가가격) * 주문수량)
  , CNCL_RTN_QTY       -- 취소반품수량
  , CNCL_RTN_AMT       -- 취소반품금액
  , CPN1_CPN_SQ        -- 1차쿠폰(즉시할인쿠폰)일련번호
  , CPN1_DC_AMT        -- 1차쿠폰(즉시할인쿠폰)할인금액
  , TMTB1_SQ           -- 다다익선1일련번호(수량)
  , TMTB1_DC_AMT       -- 다다익선1할인금액(수량)
  , TMTB2_SQ           -- 다다익선2일련번호(금액)
  , TMTB2_DC_AMT       -- 다다익선2할인금액(금액)
  , GOODS_CPN_SQ       -- 상품쿠폰일련번호
  , GOODS_CPN_DC_AMT   -- 상품쿠폰할인금액
  , CART_CPN_SQ        -- 장바구니쿠폰일련번호
  , CASE WHEN ORDER_DETAIL_CNT > 1 AND RNK = ORDER_DETAIL_CNT
        THEN CouponDiscount - (
            SELECT SUM(CEIL(B.CouponDiscount * ((A.UnitPrice) * Qty / SUM_ORD_AMT)))
            FROM old_ord_order B, old_ord_orderitem A
            WHERE A.OrderGUID = B.OrderGUID
                  AND B.OrderNo = X.ORD_NO
            ) + CART_CPN_DC_AMT
        ELSE CART_CPN_DC_AMT END AS CART_CPN_DC_AMT    -- 장바구니쿠폰할인금액
  , BURDEN_RATE        -- 쿠폰분담율
  , CASE WHEN ORDER_DETAIL_CNT > 1 AND RNK = ORDER_DETAIL_CNT
        THEN UsedMileage - (
            SELECT SUM(CEIL(B.UsedMileage * ((A.UnitPrice) * Qty / SUM_ORD_AMT)))
            FROM old_ord_order B, old_ord_orderitem A
            WHERE A.OrderGUID = B.OrderGUID
                  AND B.OrderNo = X.ORD_NO
            ) + PNT_DC_AMT
        ELSE PNT_DC_AMT END AS PNT_DC_AMT         -- 포인트할인금액 !!! 분할해야함 !!!
  , PRE_PNT_DC_AMT     -- 선포인트할인금액
  , SAVE_PNT_AMT       -- 적립포인트금액
  , REAL_ORD_AMT       -- 실주문금액(주문금액 - 취소반품금액 - 1차쿠폰 - 다다익선1 - 다다익선2 - 상품쿠폰 - 장바구니쿠폰 - 포인트할인금액 - 선포인트할인금액)
  , CASE WHEN ORDER_DETAIL_CNT > 1 AND RNK = ORDER_DETAIL_CNT
        THEN TokenAmt - (
            SELECT SUM(CEIL(B.TokenAmt * ((A.UnitPrice) * Qty / SUM_ORD_AMT)))
            FROM old_ord_order B, old_ord_orderitem A
            WHERE A.OrderGUID = B.OrderGUID
                  AND B.OrderNo = X.ORD_NO
            ) + GFCD_USE_AMT
        ELSE GFCD_USE_AMT END AS GFCD_USE_AMT       -- 상품권사용금액
  , VENDOR_ID          -- 외부몰벤더ID(공통코드G003)
  , EXTMALL_ID         -- 외부몰ID(외부몰) ????
  , AGENT_ORDER_ID     -- 에이전트주문번호 ????
  , EXTMALL_ORDER_ID   -- 외부몰주문번호 ????
  , CHANGEABLE_YN      -- 교환가능여부(Y:교환가능)
  , CHANGE_FEE_FREE_YN -- 교환배송비무료여부(Y:교환배송비무료)
  , RETURNABLE_YN      -- 교환가능여부(Y:교환가능)
  , RETURN_FEE_FREE_YN -- 교환배송비무료여부(Y:교환배송비무료)
  , SOLDOUT_YN         -- 결품여부(Y:결품)
  , SOLDOUT_MEMO       -- 결품메모
  , SOLDOUT_REG_NO     -- 결품등록자번호
  , SOLDOUT_REG_DT     -- 결품등록일시
  , DELV_ADDR_SQ       -- 배송지일련번호 마이그레이션먼저!!!
  , IF(DELV_FEE_CD IS NULL , 'X', DELV_FEE_CD)  AS DELV_FEE_CD       -- 배송비정책코드
  , SHOT_DELV_YN       -- 총알배송여부
  , GIFT_PACK_YN       -- 선물포장여부
  , GIFT_ADDR_INP_YN   -- 선물주소입력여부
  , MAKE_GOODS_YN      -- 주문제작상품여부
  , ENTRY_NO           -- 통관번호
  , DELV_LOC_CD        -- 출고처코드
  , DELV_ASSIGN_DT     -- 출고지정일시
  , DELV_ASSIGN_STAT   -- 출고지정상태
  , DSTRBT_NOTE        -- 물류비고
  , DELV_STDT          -- 배송시작일시
  , DELV_EDDT          -- 배송완료일시
  , SHIP_COMP_CD       -- 배송업체코드
  , INVOICE_NO         -- 송장번호
  , INVOICE_SEND_YN    -- 송장전송여부
  , SELL_STORE_CD      -- 판매매장코드
  , IF((FLOOR(SELL_FEE_RATE * 100) / 100) < 0, 0, (FLOOR(SELL_FEE_RATE * 100) / 100)) AS SELL_FEE_RATE      -- 판매수수료율
  , AF_LINK_CD         -- 제휴링크코드
  , ITHR_CD            -- 유입경로(공통코드G027)
  , CONTENTS_LOC       -- 컨텐츠위치(공통코드G028)
  , PLAN_DTL_SQ        -- 기획전상세번호
  , SOCIAL_SQ          -- 소셜일련번호
  , NULL AS NPAY_ORD_DTL_NO
  , REG_NO
  , REG_DT
  , UPD_NO
  , UPD_DT
FROM (
         SELECT B.OrderItemNo                                                                                         AS ORD_DTL_NO
              -- , ProductNo -- 삭제
              , A.OrderNo                                                                                             AS ORD_NO             -- 주문번호
              , 'O'                                                                                                   AS ORD_EXCH_GB
              , CASE WHEN OrderStatusCd = '결제완료' THEN 'G013_20'
                  WHEN OrderStatusCd = '입금대기' THEN 'G013_10'
                  WHEN OrderStatusCd = '입금취소' THEN 'G013_98'
                  -- WHEN OrderStatusCd = '주문취소' THEN 'G013_99'
                  ELSE 'G013_00' END  AS ORD_DTL_STAT
              , NULL                                                                                                  AS ORG_ORD_DTL_NO
              , IF((SELECT SUPPLY_COMP_CD FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo) IS NULL, 'X',
                   (SELECT SUPPLY_COMP_CD
                    FROM tmp_tb_goods X
                    WHERE X.PRODUCT_NO = ProductNo))                                                                  AS SUPPLY_COMP_CD
              , IF((SELECT GOODS_CD FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo) IS NULL, 'X',
                   (SELECT GOODS_CD FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo))                              AS GOODS_CD
              , NULL                                                                                                  AS DEAL_GOODS_CD
              , IF((SELECT FORMAL_GB FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo) IS NULL, 'X',
                   (SELECT FORMAL_GB FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo))                             AS FORMAL_GB
              , IF((SELECT GOODS_TYPE FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo) IS NULL, 'X',
                   (SELECT GOODS_TYPE FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo))                            AS GOODS_TYPE
              , IF((SELECT LIST_PRICE FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo) IS NULL, 0,
                   (SELECT LIST_PRICE FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo))                            AS LIST_PRICE         -- 정상가
              , (UnitPrice + CouponPrice)                                                                   AS CURR_PRICE         -- 현재판매가
              , IF((SELECT LIST_PRICE FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo) IS NULL, 0, FLOOR(100 -
                                                                                                            ((UnitPrice + CouponPrice) /
                                                                                                             (SELECT LIST_PRICE FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo) *
                                                                                                             100)))   AS DC_RATE
              , 0                                                                                                     AS OPT_ADD_PRICE
              , B.Qty                                                                                                 AS ORD_QTY
              , (UnitPrice + CouponPrice) * B.Qty                                                           AS ORD_AMT            -- 주문금액(=(현재판매가 + 옵션추가가격) * 주문수량)
              , 0                                                                                                     AS CNCL_RTN_QTY       -- 취소반품수량
              , 0                                                                                                     AS CNCL_RTN_AMT       -- 취소반품금액
              , NULL                                                                                                  AS CPN1_CPN_SQ        -- 1차쿠폰(즉시할인쿠폰)일련번호
              , 0                                                                                                     AS CPN1_DC_AMT        -- 1차쿠폰(즉시할인쿠폰)할인금액
              , NULL                                                                                                  AS TMTB1_SQ           -- 다다익선1일련번호(수량)
              , 0                                                                                                     AS TMTB1_DC_AMT       -- 다다익선1할인금액(수량)
              , NULL                                                                                                  AS TMTB2_SQ           -- 다다익선2일련번호(금액)
              , 0                                                                                                     AS TMTB2_DC_AMT       -- 다다익선2할인금액(금액)
              , NULL                                                                                                  AS GOODS_CPN_SQ       -- 상품쿠폰일련번호
              , CouponPrice * B.Qty                                                                                   AS GOODS_CPN_DC_AMT   -- 상품쿠폰할인금액
              , NULL                                                                                                  AS CART_CPN_SQ        -- 장바구니쿠폰일련번호
              , (SELECT SUM((UnitPrice) * Qty) FROM old_ord_orderitem X WHERE X.OrderGUID = B.OrderGUID) AS SUM_ORD_AMT       -- 주문합계
              , CEIL(A.CouponDiscount * ((B.UnitPrice) * Qty / (SELECT SUM((UnitPrice) * Qty) FROM old_ord_orderitem X WHERE X.OrderGUID = B.OrderGUID)))               AS CART_CPN_DC_AMT    -- 장바구니쿠폰할인금액
              , 0                                                                                                     AS BURDEN_RATE        -- 쿠폰분담율
              , CEIL(A.UsedMileage * ((B.UnitPrice) * Qty / (SELECT SUM((UnitPrice) * Qty) FROM old_ord_orderitem X WHERE X.OrderGUID = B.OrderGUID)))                  AS PNT_DC_AMT         -- 포인트할인금액 !!! 분할해야함 !!!
              , 0                                                                                                     AS PRE_PNT_DC_AMT     -- 선포인트할인금액
              , PointSubTotal                                                                                         AS SAVE_PNT_AMT       -- 적립포인트금액
              , 0                                                                                                     AS REAL_ORD_AMT       -- 실주문금액(주문금액 - 취소반품금액 - 1차쿠폰 - 다다익선1 - 다다익선2 - 상품쿠폰 - 장바구니쿠폰 - 포인트할인금액 - 선포인트할인금액)
              , CEIL(A.TokenAmt * ((B.UnitPrice) * Qty / (SELECT SUM((UnitPrice) * Qty) FROM old_ord_orderitem X WHERE X.OrderGUID = B.OrderGUID)))                     AS GFCD_USE_AMT       -- 상품권사용금액
              , (SELECT VENDOR_ID FROM mig_extmall X WHERE X.CHANNEL_CD = A.ChannelCd)                                AS VENDOR_ID          -- 외부몰벤더ID(공통코드G003)
              , (SELECT EXTMALL_ID FROM mig_extmall X WHERE X.CHANNEL_CD = A.ChannelCd)                               AS EXTMALL_ID         -- 외부몰ID(외부몰) ????
              , NULL                                                                                                  AS AGENT_ORDER_ID     -- 에이전트주문번호 ????
              , VendorOrderNo                                                                                         AS EXTMALL_ORDER_ID   -- 외부몰주문번호 ????
              , IF((SELECT CHANGEABLE_YN FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo) IS NULL, 'N',
                   (SELECT CHANGEABLE_YN
                    FROM tmp_tb_goods X
                    WHERE X.PRODUCT_NO = ProductNo))                                                                  AS CHANGEABLE_YN      -- 교환가능여부(Y:교환가능)
              , IF((SELECT CHANGE_FEE_FREE_YN FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo) IS NULL, 'N',
                   (SELECT CHANGE_FEE_FREE_YN
                    FROM tmp_tb_goods X
                    WHERE X.PRODUCT_NO = ProductNo))                                                                  AS CHANGE_FEE_FREE_YN -- 교환배송비무료여부(Y:교환배송비무료)
              , IF((SELECT RETURNABLE_YN FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo) IS NULL, 'N',
                   (SELECT RETURNABLE_YN
                    FROM tmp_tb_goods X
                    WHERE X.PRODUCT_NO = ProductNo))                                                                  AS RETURNABLE_YN      -- 교환가능여부(Y:교환가능)
              , IF((SELECT RETURN_FEE_FREE_YN FROM tmp_tb_goods X WHERE X.PRODUCT_NO = ProductNo) IS NULL, 'N',
                   (SELECT RETURN_FEE_FREE_YN
                    FROM tmp_tb_goods X
                    WHERE X.PRODUCT_NO = ProductNo))                                                                  AS RETURN_FEE_FREE_YN -- 교환배송비무료여부(Y:교환배송비무료)
              , 'N'                                                                                                   AS SOLDOUT_YN         -- 결품여부(Y:결품)
              , NULL                                                                                                  AS SOLDOUT_MEMO       -- 결품메모
              , NULL                                                                                                  AS SOLDOUT_REG_NO     -- 결품등록자번호
              , NULL                                                                                                  AS SOLDOUT_REG_DT     -- 결품등록일시
              , (SELECT DELV_ADDR_SQ
                 FROM tb_delivery_addr X
                 WHERE X.OLD_ORD_NO = A.OrderNo)                                                                      AS DELV_ADDR_SQ       -- 배송지일련번호 마이그레이션먼저!!!
              , (SELECT DELV_FEE_CD
                 FROM tb_delv_fee_policy X
                 WHERE X.DELIVERY_UNIT_NO = B.DeliveryUnitNo
                   AND X.SUPPLY_COMP_CD = (SELECT SUPPLY_COMP_CD
                                           FROM tmp_tb_goods X
                                           WHERE X.PRODUCT_NO = ProductNo))                                           AS DELV_FEE_CD        -- 배송비정책코드
              , IF(DeliveryClassCd = '당일', 'Y', 'N')                                                                  AS SHOT_DELV_YN       -- 총알배송여부
              , IF(IsBeautyDelivery = 'True', 'Y', 'N')                                                               AS GIFT_PACK_YN       -- 선물포장여부
              , 'N'                                                                                                   AS GIFT_ADDR_INP_YN   -- 선물주소입력여부
              , 'N'                                                                                                   AS MAKE_GOODS_YN      -- 주문제작상품여부
              , NULL                                                                                                  AS ENTRY_NO           -- 통관번호
              , CASE
                    WHEN VendorNo IN (50, 1401, 2168, 3285, 3561) THEN NULL -- 'DL001'
                    ELSE (SELECT DELV_LOC_CD FROM tb_delivery_loc X WHERE X.SUPPLY_COMP_CD = B.ProviderNo)
             END                                                                                                      AS DELV_LOC_CD        -- 출고처코드
              , CASE
                    WHEN (SELECT MALL_GB FROM tb_order X WHERE ORD_NO = A.OrderNo) = 'G011_20' THEN REG_DT
                    ELSE NULL END                                                                                     AS DELV_ASSIGN_DT     -- 출고지정일시
              , CASE
                    WHEN (SELECT MALL_GB FROM tb_order X WHERE ORD_NO = A.OrderNo) = 'G011_10' THEN 'P'
                    ELSE 'Y' END                                                                                      AS DELV_ASSIGN_STAT   -- 출고지정상태
              , NULL                                                                                                  AS DSTRBT_NOTE        -- 물류비고
              , NULL                                                                                                  AS DELV_STDT          -- 배송시작일시
              , NULL                                                                                                  AS DELV_EDDT          -- 배송완료일시
              , NULL                                                                                                  AS SHIP_COMP_CD       -- 배송업체코드
              , NULL                                                                                                  AS INVOICE_NO         -- 송장번호
              , 'N'                                                                                                   AS INVOICE_SEND_YN    -- 송장전송여부
              , NULL                                                                                                  AS SELL_STORE_CD      -- 판매매장코드
              , (B.MarginSubTotal / ((UnitPrice + CouponPrice) * Qty) * 100)                                  AS SELL_FEE_RATE      -- 판매수수료율
              , (SELECT AF_LINK_CD FROM MIG_AF WHERE ASIS_CD = A.ChannelCd)                                           AS AF_LINK_CD         -- 제휴링크코드
              , NULL                                                                                                  AS ITHR_CD            -- 유입경로(공통코드G027)
              , NULL                                                                                                  AS CONTENTS_LOC       -- 컨텐츠위치(공통코드G028)
              , NULL                                                                                                  AS PLAN_DTL_SQ        -- 기획전상세번호
              , NULL                                                                                                  AS SOCIAL_SQ          -- 소셜일련번호
              , REG_NO
              , REG_DT
              , UPD_NO
              , UPD_DT
              , COUNT(1) OVER (PARTITION BY A.OrderNo) AS ORDER_DETAIL_CNT
              , RANK() over (PARTITION BY A.OrderNo ORDER BY OrderItemNo) AS RNK
              , A.CouponDiscount
              , A.UsedMileage
              , A.TokenAmt
         FROM old_ord_order A,
              tb_order C,
              old_ord_orderitem B
         WHERE A.OrderNo = C.ORD_NO
            AND A.OrderGUID = B.OrderGUID
            AND A.ItemTotal > 0
            AND B.UnitPrice > 0
     ) X
; --


UPDATE tb_order_detail
    SET REAL_ORD_AMT = (ORD_AMT -  CNCL_RTN_AMT - GOODS_CPN_DC_AMT - CART_CPN_DC_AMT - PNT_DC_AMT - GFCD_USE_AMT)
WHERE ORD_DTL_NO > 1000
;

-- 제휴링크 옛날거는 기타로
UPDATE TB_ORDER_DETAIL
SET AF_LINK_CD = 'AF999'
WHERE EXTMALL_ID IS NULL
    AND AF_LINK_CD IS NULL
;


SELECT MAX(ORD_DTL_NO) FROM tb_order_detail;
ALTER TABLE tb_order_detail AUTO_INCREMENT = 57317460;

-- 주문상세단품
TRUNCATE TABLE tb_order_detail_item;

INSERT INTO tb_order_detail_item (ORD_DTL_NO, ORD_NO, ITEM_CD, OPT_CD, OPT_CD1, OPT_CD2, SKU_MODEL_NO, PRODUCT_NO, PRODUCT_CODE, ITEM_QTY, ITEM_PRICE, OPT_ADD_PRICE, DISP_ORD, ORD_AMT, CNCL_RTN_AMT, CPN1_DC_AMT, TMTB1_DC_AMT, TMTB2_DC_AMT, GOODS_CPN_DC_AMT, CART_CPN_DC_AMT, PNT_DC_AMT, PRE_PNT_DC_AMT, SAVE_PNT_AMT, REAL_ORD_AMT, GFCD_USE_AMT, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT A.ORD_DTL_NO
    , A.ORD_NO
    , A.GOODS_CD
    , B.SKUCode AS OPT_CD
    , (SELECT OPT_CD1 FROM tb_option X WHERE X.GOODS_CD = A.GOODS_CD AND X.OPT_CD = B.SKUCode) AS OPT_CD1
    , (SELECT OPT_CD2 FROM tb_option X WHERE X.GOODS_CD = A.GOODS_CD AND X.OPT_CD = B.SKUCode) AS OPT_CD2
    , B.SKUModelNo
    , B.ProductNo
    , B.ProductCode
    , B.Qty
    , B.CouponDiscountPrice + B.CouponPrice AS ITEM_PRICE
    , 0 AS OPT_ADD_PRICE
    , 1 AS DISP_ORD
    , ORD_AMT
    , CNCL_RTN_AMT
    , CPN1_DC_AMT
    , TMTB1_DC_AMT
    , TMTB2_DC_AMT
    , GOODS_CPN_DC_AMT
    , CART_CPN_DC_AMT
    , PNT_DC_AMT
    , PRE_PNT_DC_AMT
    , SAVE_PNT_AMT
    , REAL_ORD_AMT
    , GFCD_USE_AMT
    , REG_NO
    , REG_DT
    , UPD_NO
    , UPD_DT
FROM tb_order_detail A, old_ord_orderitem B
WHERE A.ORD_DTL_NO = B.OrderItemNo
; --


-- 주문상세이력

TRUNCATE TABLE tb_order_detail_hst;
ALTER TABLE tb_order_detail_hst AUTO_INCREMENT = 1;

INSERT INTO tb_order_detail_hst (ORD_DTL_NO, ORD_NO, ORD_EXCH_GB, ORD_DTL_STAT, ORG_ORD_DTL_NO, SUPPLY_COMP_CD, GOODS_CD, DEAL_GOODS_CD, FORMAL_GB, GOODS_TYPE, LIST_PRICE, CURR_PRICE, DC_RATE, OPT_ADD_PRICE, ORD_QTY, ORD_AMT, CNCL_RTN_QTY, CNCL_RTN_AMT, CPN1_CPN_SQ, CPN1_DC_AMT, TMTB1_SQ, TMTB1_DC_AMT, TMTB2_SQ, TMTB2_DC_AMT, GOODS_CPN_SQ, GOODS_CPN_DC_AMT, CART_CPN_SQ, CART_CPN_DC_AMT, BURDEN_RATE, PNT_DC_AMT, PRE_PNT_DC_AMT, SAVE_PNT_AMT, REAL_ORD_AMT, GFCD_USE_AMT, VENDOR_ID, EXTMALL_ID, AGENT_ORDER_ID, EXTMALL_ORDER_ID, CHANGEABLE_YN, CHANGE_FEE_FREE_YN, RETURNABLE_YN, RETURN_FEE_FREE_YN, SOLDOUT_YN, SOLDOUT_MEMO, SOLDOUT_REG_NO, SOLDOUT_REG_DT, DELV_ADDR_SQ, DELV_FEE_CD, SHOT_DELV_YN, GIFT_PACK_YN, GIFT_ADDR_INP_YN, MAKE_GOODS_YN, ENTRY_NO, DELV_LOC_CD, DELV_ASSIGN_DT, DELV_ASSIGN_STAT, DSTRBT_NOTE, DELV_STDT, DELV_EDDT, SHIP_COMP_CD, INVOICE_NO, INVOICE_SEND_YN, SELL_STORE_CD, SELL_FEE_RATE, AF_LINK_CD, ITHR_CD, CONTENTS_LOC, PLAN_DTL_SQ, SOCIAL_SQ, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT * FROM tb_order_detail
;

/*
-- 주문상세단품이력
TRUNCATE TABLE tb_order_detail_item_hst;

ALTER TABLE tb_order_detail_item_hst AUTO_INCREMENT = 1;


INSERT INTO tb_order_detail_item_hst (ORD_DTL_ITEM_SQ, ORD_DTL_NO, ORD_NO, ITEM_CD, OPT_CD, OPT_CD1, OPT_CD2, SKU_MODEL_NO, PRODUCT_NO, PRODUCT_CODE, ITEM_QTY, ITEM_PRICE, OPT_ADD_PRICE, DISP_ORD, ORD_AMT, CNCL_RTN_AMT, CPN1_DC_AMT, TMTB1_DC_AMT, TMTB2_DC_AMT, GOODS_CPN_DC_AMT, CART_CPN_DC_AMT, PNT_DC_AMT, PRE_PNT_DC_AMT, SAVE_PNT_AMT, REAL_ORD_AMT, GFCD_USE_AMT, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT ORD_DTL_ITEM_SQ, ORD_DTL_NO, ORD_NO, ITEM_CD, OPT_CD, OPT_CD1, OPT_CD2, SKU_MODEL_NO, PRODUCT_NO, PRODUCT_CODE, ITEM_QTY, ITEM_PRICE, OPT_ADD_PRICE, DISP_ORD, ORD_AMT, CNCL_RTN_AMT, CPN1_DC_AMT, TMTB1_DC_AMT, TMTB2_DC_AMT, GOODS_CPN_DC_AMT, CART_CPN_DC_AMT, PNT_DC_AMT, PRE_PNT_DC_AMT, SAVE_PNT_AMT, REAL_ORD_AMT, GFCD_USE_AMT, REG_NO, REG_DT, UPD_NO, UPD_DT
FROM tb_order_detail_item
;
*/

-- 결제정보
TRUNCATE TABLE tb_payment;

INSERT INTO tb_payment
SELECT
    PaymentNo AS PAY_SQ
    , ORD_NO
    , DatePaid AS PAY_DT
    , CASE WHEN C.MALL_GB = 'G011_20' THEN 'G014_90'
        WHEN A.PaymentTypeCd = '신용카드' THEN 'G014_30'
        WHEN A.PaymentTypeCd = '휴대폰' THEN 'G014_60'
        WHEN A.PaymentTypeCd = '상품권' THEN 'G014_70'
        WHEN A.PaymentTypeCd = '즉시이체' THEN 'G014_10'
        WHEN A.PaymentTypeCd = '가상계좌' THEN 'G014_20'
        WHEN A.PaymentTypeCd = '네이버포인트' THEN 'G014_80'
        ELSE 'G014_30' END AS PAY_MEANS
    , Amount AS PAY_AMT
    , 0 AS PG_CPN_AMT
    , CASE WHEN A.PaymentTypeCd = '네이버포인트' THEN A.PGAmount
        ELSE 0 END AS NPAY_PNT_AMT
    , 'O' AS PAY_GB
    , CASE WHEN B.OrderStatusCd IN ('입금대기','입금취소') THEN 'G016_10' -- 입금취소건은 나중에 결제취소로 하나더 쌓아줘야함
        ELSE 'G016_30' -- 결제완료,주문취소 -- 주문취소건은 나중에 결제취소로 하나더 쌓아줘야함
        END AS PAY_STAT
    , CASE WHEN C.MALL_GB = 'G011_20' THEN NULL -- 외부몰
        WHEN B.PGType = 'KCP' THEN 'KCP'
        WHEN B.PGType = 'NPay' THEN 'NAVER'
        WHEN B.PGType = 'PAYCO' THEN 'PAYCO'
        ELSE 'ISTYLE' END AS PAY_GB
    , IF(PGTid = '', NULL, PGTid) AS PG_TID
    , IF((SELECT AuthCode FROM old_ord_pglog X WHERE X.PaymentNo = A.PaymentNo LIMIT 1) = '', NULL, (SELECT AuthCode FROM old_ord_pglog X WHERE X.PaymentNo = A.PaymentNo LIMIT 1)) AS PG_TRADE_NO
    , CASE WHEN B.PGType = 'NPay' THEN '8TSWSyJMMUvOLKUySQx6'
        WHEN B.PGType IN ('KCP','PAYCO') THEN 'U3476'
        ELSE NULL END  PG_SHOP_ID
    , CASE WHEN B.PGType = 'NPay' THEN 'oGXnO7cMD1'
        ELSE NULL END AS PG_SHOP_KEY
    , NULL AS CARD_TYPE
    , NULL AS CARD_KIND
    , NULL AS CARD_BANK
    , IF((SELECT CardCode FROM old_ord_pglog X WHERE X.PaymentNo = A.PaymentNo LIMIT 1) = '', NULL, (SELECT CardCode FROM old_ord_pglog X WHERE X.PaymentNo = A.PaymentNo LIMIT 1)) AS CARD_CD
    , (SELECT Y.CARD_NM FROM old_ord_pglog X, tb_pg_card Y
        WHERE Y.PG_GB = (CASE WHEN B.PGType IN ('KCP','PAYCO') THEN 'KCP' WHEN B.PGType = 'NPay' THEN 'NAVER' ELSE NULL END)
          AND X.CardCode = Y.CARD_CD
          AND X.PaymentNo = A.PaymentNo
        ) AS CARD_NM
    , (SELECT Y.COM_CARD_CD FROM old_ord_pglog X, tb_pg_card Y
        WHERE Y.PG_GB = (CASE WHEN B.PGType IN ('KCP','PAYCO') THEN 'KCP' WHEN B.PGType = 'NPay' THEN 'NAVER' ELSE NULL END)
          AND X.CardCode = Y.CARD_CD
          AND X.PaymentNo = A.PaymentNo
        ) AS COM_CARD_CD
    , IF((SELECT CardQuota FROM old_ord_pglog X WHERE X.PaymentNo = A.PaymentNo LIMIT 1) = '', NULL, (SELECT CardQuota FROM old_ord_pglog X WHERE X.PaymentNo = A.PaymentNo LIMIT 1)) AS CARD_MIPS
    , CASE WHEN A.PaymentTypeCd = '휴대폰' THEN 'N' ELSE 'Y' END AS CARD_PCABLE_YN
    , (SELECT Vacct FROM old_ord_pglog X WHERE X.PaymentNo = A.PaymentNo LIMIT 1) AS VA_NO
    , (SELECT NmVacct FROM old_ord_pglog X WHERE X.PaymentNo = A.PaymentNo LIMIT 1) AS VA_NM
    , (SELECT Vcdbank FROM old_ord_pglog X WHERE X.PaymentNo = A.PaymentNo LIMIT 1) AS VA_BANK
    , DATE_FORMAT(CONCAT(DATE_FORMAT(DATE_ADD(B.DateOrdered, INTERVAL 2 DAY), '%Y%m%d'), '235959'), '%Y-%m-%d %H:%s:%i') AS VA_DEADLINE
    , IF((SELECT MobileCompanyCode FROM old_ord_pglog X WHERE X.PaymentNo = A.PaymentNo LIMIT 1) = '', NULL, (SELECT MobileCompanyCode FROM old_ord_pglog X WHERE X.PaymentNo = A.PaymentNo LIMIT 1)) AS TELECOM
    , 'N' AS ESCROW_YN
    , (SELECT ApprovalNum FROM old_ord_cashreceiptlog X WHERE X.PaymentNo = A.PaymentNo AND ApprovalNum IS NOT NULL AND ApprovalNum != '' AND CashReceiptType != '상품권' LIMIT 1) AS CASH_AUTH_NO
    , (SELECT Tno FROM old_ord_cashreceiptlog X WHERE X.PaymentNo = A.PaymentNo AND ApprovalNum IS NOT NULL AND ApprovalNum != '' AND CashReceiptType != '상품권' LIMIT 1) AS CASH_TRADE_NO
    , NULL AS ORD_CHG_SQ
    , C.REG_NO
    , C.REG_DT
    , C.UPD_NO
    , C.UPD_DT
FROM old_ord_payment A, old_ord_order B, tb_order C
WHERE A.OrderGUID = B.OrderGUID
    AND B.OrderNo = C.ORD_NO
    AND OrderStatusCd != '주문접수'
; -- 14분20초

SELECT MAX(PAY_SQ) FROM tb_payment;
ALTER TABLE tb_payment AUTO_INCREMENT = 13089188;

-- 원주문배송비
TRUNCATE TABLE tb_delivery_fee;

ALTER TABLE tb_delivery_fee AUTO_INCREMENT = 1;

INSERT INTO tb_delivery_fee (PAY_SQ, ORD_NO, DELV_FEE_GB, DELV_FEE_CD, DELV_FEE, DELV_CPN_SQ, DELV_CPN_DC_AMT, REAL_DELV_AMT, ORD_CHG_SQ, SUPPLY_COMP_CD, DELV_USAC_YN, DELV_USAC_DT, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT
    DISTINCT
    (SELECT PAY_SQ FROM tb_payment X WHERE X.ORD_NO = C.ORD_NO) AS PAY_SQ
    , C.ORD_NO
    , 'G018_10' AS DELV_FEE_GB
    , C.DELV_FEE_CD
    , DeliveryCost AS DELV_FEE
    , NULL AS DELV_CPN_SQ
    , 0 AS DELV_CPN_DC_AMT
    , DeliveryCost AS REAL_DELV_AMT
    , NULL AS ORD_CHG_SQ
    , C.SUPPLY_COMP_CD
    , 'N' AS DELV_USAC_YN
    , NULL AS DELV_USAC_DT
    , C.REG_NO
    , C.REG_DT
    , C.REG_NO AS UPD_NO
    , C.REG_DT AS UPD_DT
FROM old_ord_orderdelivery A, old_ord_orderitem B, tb_order_detail C
WHERE A.OrderGUID = B.OrderGUID
    AND A.DeliveryUnitNo = B.DeliveryUnitNo
    AND B.OrderItemNo = C.ORD_DTL_NO
;

SELECT MAX(DELV_FEE_SQ) FROM tb_delivery_fee;
ALTER TABLE tb_delivery_fee AUTO_INCREMENT = 4078196;

-- 상품권현금영수증
TRUNCATE TABLE TB_GIFTCARD_RECEIPT;

INSERT INTO TB_GIFTCARD_RECEIPT
SELECT
    ORD_NO
    , TRADE_NO
    , AUTH_NO
    , AMT
    , REG_DT
    , REG_DT AS UPD_DT
FROM (
    SELECT
        B.ORD_NO
        , A.Tno AS TRADE_NO
        , A.ApprovalNum AS AUTH_NO
        , A.Amount AS AMT
        , A.DateCreated AS REG_DT
        , RANK() over (PARTITION BY PaymentNo ORDER BY DateCreated) AS RNK
    FROM old_ord_cashreceiptlog A, tb_payment B
    WHERE A.PaymentNo = B.PAY_SQ
        AND A.CashReceiptType = '상품권'
        AND ApprovalNum IS NOT NULL
        AND ApprovalNum != ''
        AND RegCd = '0000'
) A
WHERE RNK = 1
;

-- 송장
TRUNCATE TABLE TB_ORDER_DETAIL_INVOICE;

ALTER TABLE TB_ORDER_DETAIL_INVOICE AUTO_INCREMENT = 1;

INSERT INTO TB_ORDER_DETAIL_INVOICE (ORD_NO, ORD_DTL_NO, INVOICE_NO, ADD_INVOICE_YN, SWT_TRC_SEND_YN, DEL_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT C.ORD_NO
  , C.ORD_DTL_NO
  --  (SELECT ORD_NO FROM OLD_ORD_ORDERITEM X WHERE X.ORDERITEMNO = )
  -- , (SELECT OrderItemNo FROM old_ord_deliveryorderitem X WHERE X.DELIVERYORDERITEMNO)
  , A.InvoiceNo                                                               AS INVOICE_NO
  , 'N'                                                                       AS ADD_INVOICE_YN
  , 'N'                                                                       AS SWT_TRC_SEND_YN
  , 'N'                                                                       AS DEL_YN
  , 2                                                                         AS REG_NO
  , A.DateFirstCreated                                                        AS REG_DT
  , 2                                                                         AS UPD_NO
  , A.DateFirstCreated                                                        AS UPD_DT
FROM old_ord_deliveryinvoice A,
  old_ord_deliveryorderitem B,
  tb_order_detail C
WHERE A.DeliveryOrderItemNo = B.DeliveryOrderItemNo
AND B.OrderItemNo = C.ORD_DTL_NO
AND A.InvoiceNo REGEXP('^[0-9]+$')
AND B.IssueTypeCd = '정상'
-- LIMIT 1000000
-- LIMIT 1000000, 1000000
-- LIMIT 2000000, 1000000
-- LIMIT 3000000, 1000000
LIMIT 4000000, 1000000
;

/*
INSERT INTO TB_ORDER_DETAIL_INVOICE (ORD_NO, ORD_DTL_NO, INVOICE_NO, ADD_INVOICE_YN, SWT_TRC_SEND_YN, DEL_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT
    ORD_NO
    , ORD_DTL_NO
    , INVOICE_NO
    , ADD_INVOICE_YN
    , SWT_TRC_SEND_YN
    , DEL_YN
    , REG_NO
    , REG_DT
    , UPD_NO
    , UPD_DT
FROM (
         SELECT C.ORD_NO
              , C.ORD_DTL_NO
              , A.InvoiceNo                                                               AS INVOICE_NO
              , 'N'                                                                       AS ADD_INVOICE_YN
              , 'N'                                                                       AS SWT_TRC_SEND_YN
              , 'N'                                                                       AS DEL_YN
              , 2                                                                         AS REG_NO
              , A.DateFirstCreated                                                        AS REG_DT
              , 2                                                                         AS UPD_NO
              , A.DateFirstCreated                                                        AS UPD_DT
              , RANK() over (PARTITION BY C.ORD_NO, C.ORD_DTL_NO ORDER BY A.ItemNo DESC) AS RNK
         FROM old_ord_deliveryinvoice A,
              old_ord_deliveryorderitem B,
              tb_order_detail C
         WHERE A.DeliveryOrderItemNo = B.DeliveryOrderItemNo
           AND B.OrderItemNo = C.ORD_DTL_NO
           AND A.InvoiceNo REGEXP('^[0-9]+$')
           AND B.IssueTypeCd = '정상'
     ) A2
WHERE RNK = 1
; -- 4분40초*/

SELECT MAX(ORD_INVOICE_SQ) FROM TB_ORDER_DETAIL_INVOICE;
ALTER TABLE TB_ORDER_DETAIL_INVOICE AUTO_INCREMENT = 3982636;


-- 주문상세 배송관련 업데이트
UPDATE tb_order_detail X
SET DELV_STDT = (
    SELECT IFNULL(DateSent, A.DateFirstCreated) FROM old_ord_deliveryinvoice A, old_ord_deliveryorderitem B WHERE A.DeliveryOrderItemNo = B.DeliveryOrderItemNo AND A.InvoiceNo REGEXP('^[0-9]+$') AND B.IssueTypeCd = '정상' AND OrderItemNo = X.ORD_DTL_NO ORDER BY A.ItemNo DESC LIMIT 1
    )
    , DELV_EDDT = (
    SELECT DateReceived FROM old_ord_deliveryinvoice A, old_ord_deliveryorderitem B WHERE A.DeliveryOrderItemNo = B.DeliveryOrderItemNo AND A.InvoiceNo REGEXP('^[0-9]+$') AND B.IssueTypeCd = '정상' AND OrderItemNo = X.ORD_DTL_NO ORDER BY A.ItemNo DESC LIMIT 1
    )
    , SHIP_COMP_CD = (
        SELECT (CASE WHEN
                   (SELECT SHIP_COMP_CD
                FROM tb_ship_company
                WHERE SHIP_COMP_CD = CASE
                                         WHEN LENGTH(LogisticsNo) = 1 THEN CONCAT('D100', LogisticsNo)
                                         WHEN LENGTH(LogisticsNo) = 2 THEN CONCAT('D10', LogisticsNo)
                                         ELSE CONCAT('D1', LogisticsNo) END)
                IN ('D1013', 'D1018') THEN 'D1029' -- CJ대한통운
                ELSE (SELECT SHIP_COMP_CD
                FROM tb_ship_company
                WHERE SHIP_COMP_CD = CASE
                                         WHEN LENGTH(LogisticsNo) = 1 THEN CONCAT('D100', LogisticsNo)
                                         WHEN LENGTH(LogisticsNo) = 2 THEN CONCAT('D10', LogisticsNo)
                                         ELSE CONCAT('D1', LogisticsNo) END)
                END
               )
        FROM old_ord_deliveryinvoice A, old_ord_deliveryorderitem B
        WHERE A.DeliveryOrderItemNo = B.DeliveryOrderItemNo AND A.InvoiceNo REGEXP('^[0-9]+$') AND B.IssueTypeCd = '정상'
             AND OrderItemNo = X.ORD_DTL_NO ORDER BY A.ItemNo DESC LIMIT 1
    )
    , INVOICE_NO = (
        SELECT INVOICE_NO
        FROM tb_order_detail_invoice A
        WHERE A.ORD_DTL_NO = X.ORD_DTL_NO
            AND A.ORD_NO = X.ORD_NO
        ORDER BY A.REG_DT DESC
        LIMIT 1
    )
    , DELV_LOC_CD = (
        SELECT A.DELV_LOC_CD
        FROM tb_delivery_loc A
        WHERE A.SUPPLY_COMP_CD = X.SUPPLY_COMP_CD
        LIMIT 1
    )
WHERE 1 = 1
AND ORD_NO > 10000
; -- 29분3초

UPDATE tb_order_detail
SET DELV_STDT = DELV_EDDT
WHERE ORD_DTL_STAT = 'G013_20'
    AND INVOICE_NO IS NOT NULL
    AND DELV_STDT IS NULL
    AND ORD_NO > 10000
; -- 31초

-- 배송중으로 업데이트
UPDATE tb_order_detail A
SET ORD_DTL_STAT = 'G013_50'
    -- , UPD_NO = 0 -- 배치
    , UPD_DT = DELV_STDT
WHERE ORD_DTL_STAT = 'G013_20'
    AND INVOICE_NO IS NOT NULL
    AND ORD_NO > 10000
; -- 3분32초


-- 주문상세이력
/*
INSERT INTO tb_order_detail_hst (ORD_DTL_NO, ORD_NO, ORD_EXCH_GB, ORD_DTL_STAT, ORG_ORD_DTL_NO, SUPPLY_COMP_CD, GOODS_CD, DEAL_GOODS_CD, FORMAL_GB, GOODS_TYPE, LIST_PRICE, CURR_PRICE, DC_RATE, OPT_ADD_PRICE, ORD_QTY, ORD_AMT, CNCL_RTN_QTY, CNCL_RTN_AMT, CPN1_CPN_SQ, CPN1_DC_AMT, TMTB1_SQ, TMTB1_DC_AMT, TMTB2_SQ, TMTB2_DC_AMT, GOODS_CPN_SQ, GOODS_CPN_DC_AMT, CART_CPN_SQ, CART_CPN_DC_AMT, BURDEN_RATE, PNT_DC_AMT, PRE_PNT_DC_AMT, SAVE_PNT_AMT, REAL_ORD_AMT, GFCD_USE_AMT, VENDOR_ID, EXTMALL_ID, AGENT_ORDER_ID, EXTMALL_ORDER_ID, CHANGEABLE_YN, CHANGE_FEE_FREE_YN, RETURNABLE_YN, RETURN_FEE_FREE_YN, SOLDOUT_YN, SOLDOUT_MEMO, SOLDOUT_REG_NO, SOLDOUT_REG_DT, DELV_ADDR_SQ, DELV_FEE_CD, SHOT_DELV_YN, GIFT_PACK_YN, GIFT_ADDR_INP_YN, MAKE_GOODS_YN, ENTRY_NO, DELV_LOC_CD, DELV_ASSIGN_DT, DELV_ASSIGN_STAT, DSTRBT_NOTE, DELV_STDT, DELV_EDDT, SHIP_COMP_CD, INVOICE_NO, INVOICE_SEND_YN, SELL_STORE_CD, SELL_FEE_RATE, AF_LINK_CD, ITHR_CD, CONTENTS_LOC, PLAN_DTL_SQ, SOCIAL_SQ, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT * FROM tb_order_detail
WHERE ORD_DTL_STAT = 'G013_50'
;
*/

-- 배송완료로 업데이트
UPDATE tb_order_detail A
SET ORD_DTL_STAT = 'G013_60'
    -- , UPD_NO = 0 -- 배치
    , UPD_DT = DELV_EDDT
WHERE ORD_DTL_STAT = 'G013_50'
    AND INVOICE_NO IS NOT NULL
    AND DELV_EDDT IS NOT NULL
    AND ORD_NO > 10000
; -- 3분37초

-- 구매확정처리
-- Table : TB_ORD_DeliveryOrderItem
-- Column : IsConfirmed
/*
UPDATE tb_order_detail A, old_ord_deliveryorderitem B
SET ORD_DTL_STAT = 'G013_70'
WHERE A.ORD_DTL_NO = B.OrderItemNo
    AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98')
    AND B.IsConfirmed = 'True'
;
*/
UPDATE tb_order_detail A, old_ord_deliveryorderitem B
SET ORD_DTL_STAT = 'G013_70'
WHERE A.ORD_DTL_NO = B.OrderItemNo
    AND ORD_DTL_STAT != 'G013_70'
    AND B.IsConfirmed = 'True'
;

-- 주문상세이력
/*
INSERT INTO tb_order_detail_hst (ORD_DTL_NO, ORD_NO, ORD_EXCH_GB, ORD_DTL_STAT, ORG_ORD_DTL_NO, SUPPLY_COMP_CD, GOODS_CD, DEAL_GOODS_CD, FORMAL_GB, GOODS_TYPE, LIST_PRICE, CURR_PRICE, DC_RATE, OPT_ADD_PRICE, ORD_QTY, ORD_AMT, CNCL_RTN_QTY, CNCL_RTN_AMT, CPN1_CPN_SQ, CPN1_DC_AMT, TMTB1_SQ, TMTB1_DC_AMT, TMTB2_SQ, TMTB2_DC_AMT, GOODS_CPN_SQ, GOODS_CPN_DC_AMT, CART_CPN_SQ, CART_CPN_DC_AMT, BURDEN_RATE, PNT_DC_AMT, PRE_PNT_DC_AMT, SAVE_PNT_AMT, REAL_ORD_AMT, GFCD_USE_AMT, VENDOR_ID, EXTMALL_ID, AGENT_ORDER_ID, EXTMALL_ORDER_ID, CHANGEABLE_YN, CHANGE_FEE_FREE_YN, RETURNABLE_YN, RETURN_FEE_FREE_YN, SOLDOUT_YN, SOLDOUT_MEMO, SOLDOUT_REG_NO, SOLDOUT_REG_DT, DELV_ADDR_SQ, DELV_FEE_CD, SHOT_DELV_YN, GIFT_PACK_YN, GIFT_ADDR_INP_YN, MAKE_GOODS_YN, ENTRY_NO, DELV_LOC_CD, DELV_ASSIGN_DT, DELV_ASSIGN_STAT, DSTRBT_NOTE, DELV_STDT, DELV_EDDT, SHIP_COMP_CD, INVOICE_NO, INVOICE_SEND_YN, SELL_STORE_CD, SELL_FEE_RATE, AF_LINK_CD, ITHR_CD, CONTENTS_LOC, PLAN_DTL_SQ, SOCIAL_SQ, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT * FROM tb_order_detail
WHERE ORD_DTL_STAT = 'G013_60'
;
*/

-- 입금대기->취소
TRUNCATE TABLE tb_order_change;

ALTER TABLE tb_order_change AUTO_INCREMENT = 1;

INSERT INTO tb_order_change (CHG_GB, CHG_REASON, CHG_MEMO, WD_GB, CHGER_NM, CHGER_PHNNO, CHGER_TELNO, CHGER_EMAIL, CHGER_ZIPCODE, CHGER_BASE_ADDR, CHGER_DTL_ADDR, CHGER_RTN_MEMO, ADD_PAY_COST, ADD_PAY_AMT, WD_INVOICE_NO, WD_INVOICE_SEND_YN, SHIP_COMP_CD, WD_STDT, WD_EDDT, SWT_TRC_SEND_YN, WD_SHIP_STATE, WD_REASON_CD, WD_STATE_DT, WD_BF_SEND_YN, REG_NO, REG_DT, UPD_NO, UPD_DT, OLD_ORD_NO)
SELECT
    'G680_10' AS CHG_GB
    , 'G686_10' AS CHG_REASON
    , NULL AS CHG_MEMO
    , NULL AS WD_GB
    , NULL AS CHGER_NM
    , NULL AS CHGER_PHNNO
    , NULL AS CHGER_TELNO
    , NULL AS CHGER_EMAIL
    , NULL AS CHGER_ZIPCODE
    , NULL AS CHGER_BASE_ADDR
    , NULL AS CHGER_DTL_ADDR
    , NULL AS CHGER_RTN_MEMO
    , 0 AS ADD_PAY_COST
    , 0 AS ADD_PAY_AMT
    , NULL AS WD_INVOICE_NO
    , 'N' AS WD_INVOICE_SEND_YN
    , NULL AS SHIP_COMP_CD
    , NULL AS WD_STDT
    , NULL AS WD_EDDT
    , 'N' AS SWT_TRC_SEND_YN
    , NULL AS WD_SHIP_STATE
    , NULL AS WD_REASON_CD
    , NULL AS WD_STATE_DT
    , 'N' AS WD_BF_SEND_YN
    , REG_NO
    , VA_DEADLINE AS REG_DT
    , UPD_NO
    , VA_DEADLINE AS UPD_DT
    , ORD_NO
FROM old_ord_order A, tb_payment B
WHERE A.OrderNo = B.ORD_NO
    AND OrderStatusCd = '입금취소'
; -- 3초


TRUNCATE TABLE tb_order_change_detail;

INSERT INTO tb_order_change_detail
SELECT
    ORD_CHG_SQ
    , ORD_DTL_NO
    , ORD_QTY
    , 'G685_17' AS CHG_STAT
    , NULL AS CHG_ORD_DTL_NO
    , NULL AS WH_MEMO
    , NULL AS COMPLETE_DT
    , 'N' AS DEL_YN
    , REG_NO
    , A.REG_DT
    , UPD_NO
    , A.UPD_DT
FROM tb_order_change A, tb_order_detail B
WHERE A.OLD_ORD_NO = B.ORD_NO
    AND A.CHG_GB = 'G680_10'
    AND B.ORD_NO > 1000
; -- 3초


INSERT INTO tb_payment (ORD_NO, PAY_DT, PAY_MEANS, PAY_AMT, PG_CPN_AMT, NPAY_PNT_AMT, PAY_GB, PAY_STAT, PG_GB, PG_TID, PG_TRADE_NO, PG_SHOP_ID, PG_SHOP_KEY, CARD_TYPE, CARD_KIND, CARD_BANK, CARD_CD, CARD_NM, COM_CARD_CD, CARD_MIPS, CARD_PCABLE_YN, VA_NO, VA_NM, VA_BANK, VA_DEADLINE, TELECOM, ESCROW_YN, CASH_AUTH_NO, CASH_TRADE_NO, ORD_CHG_SQ, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT DISTINCT ORD_NO, PAY_DT, PAY_MEANS, (PAY_AMT * -1) AS PAY_AMT
     , PG_CPN_AMT, NPAY_PNT_AMT, PAY_GB, 'G016_98' AS PAY_STAT, PG_GB, PG_TID
     , PG_TRADE_NO, PG_SHOP_ID, PG_SHOP_KEY, CARD_TYPE, CARD_KIND
     , CARD_BANK, CARD_CD, CARD_NM, COM_CARD_CD, CARD_MIPS
     , CARD_PCABLE_YN, NULL AS VA_NO, NULL AS VA_NM, NULL AS VA_BANK, NULL AS VA_DEADLINE
     , TELECOM, ESCROW_YN, CASH_AUTH_NO, CASH_TRADE_NO
     , B.ORD_CHG_SQ, A.REG_NO, VA_DEADLINE AS REG_DT, A.UPD_NO, VA_DEADLINE AS UPD_DT
FROM tb_payment A, tb_order_change B
WHERE A.ORD_NO = B.OLD_ORD_NO
    AND EXISTS(
        SELECT 1
        FROM tb_order_detail B
        WHERE B.ORD_NO = A.ORD_NO
            AND ORD_DTL_STAT = 'G013_98'
    )
    AND A.ORD_NO > 1000
; -- 4초

UPDATE tb_order_detail A, tb_order_change_detail B
SET A.CNCL_RTN_QTY = A.ORD_QTY
    , A.CNCL_RTN_AMT = A.ORD_AMT
    , A.GOODS_CPN_DC_AMT = 0
    , A.CART_CPN_DC_AMT = 0
    , A.PNT_DC_AMT = 0
    , A.SAVE_PNT_AMT = 0
    , A.REAL_ORD_AMT = 0
    , A.GFCD_USE_AMT = 0
    -- , A.UPD_NO = 0
    , A.UPD_DT = B.REG_DT
WHERE A.ORD_DTL_NO = B.ORD_DTL_NO
    AND CHG_STAT = 'G685_17'
    AND A.ORD_NO > 1000
; -- 4초

-- DELETE FROM tb_order_change WHERE CHG_GB = 'G680_20';
-- 결제후주문취소(취소/반품 나눠서 처리해야함)
-- 전체취소
INSERT INTO tb_order_change (ORD_CHG_SQ, CHG_GB, CHG_REASON, CHG_MEMO, WD_GB, CHGER_NM, CHGER_PHNNO, CHGER_TELNO, CHGER_EMAIL, CHGER_ZIPCODE, CHGER_BASE_ADDR, CHGER_DTL_ADDR, CHGER_RTN_MEMO, ADD_PAY_COST, ADD_PAY_AMT, WD_INVOICE_NO, WD_INVOICE_SEND_YN, SHIP_COMP_CD, WD_STDT, WD_EDDT, SWT_TRC_SEND_YN, WD_SHIP_STATE, WD_REASON_CD, WD_STATE_DT, WD_BF_SEND_YN, REG_NO, REG_DT, UPD_NO, UPD_DT, OLD_ORD_NO, OLD_EXEPTION_NO)
SELECT DISTINCT
    B.ExceptionNo
    , 'G680_20' AS CHG_GB
    , CASE WHEN (SELECT MAX(ExceptionReasonCd) FROM old_ord_orderexceptionitem X WHERE X.ExceptionNo = B.ExceptionNo AND X.ExceptionTypeCd = '취소') = '취소' THEN 'G686_10'
            WHEN (SELECT MAX(ExceptionReasonCd) FROM old_ord_orderexceptionitem X WHERE X.ExceptionNo = B.ExceptionNo AND X.ExceptionTypeCd = '취소') = '고객변심' THEN 'G686_10'
            WHEN (SELECT MAX(ExceptionReasonCd) FROM old_ord_orderexceptionitem X WHERE X.ExceptionNo = B.ExceptionNo AND X.ExceptionTypeCd = '취소') = '품절' THEN 'G686_30'
            WHEN (SELECT MAX(ExceptionReasonCd) FROM old_ord_orderexceptionitem X WHERE X.ExceptionNo = B.ExceptionNo AND X.ExceptionTypeCd = '취소') = '상품불량' THEN 'G686_11'
            WHEN (SELECT MAX(ExceptionReasonCd) FROM old_ord_orderexceptionitem X WHERE X.ExceptionNo = B.ExceptionNo AND X.ExceptionTypeCd = '취소') = '오배송' THEN 'G686_12'
            WHEN (SELECT MAX(ExceptionReasonCd) FROM old_ord_orderexceptionitem X WHERE X.ExceptionNo = B.ExceptionNo AND X.ExceptionTypeCd = '취소') = '배송지연' THEN 'G686_13'
        ELSE 'G686_90' END AS CHG_REASON
    , B.AdminMemo AS CHG_MEMO
    , NULL AS WD_GB
    , NULL AS CHGER_NM
    , NULL AS CHGER_PHNNO
    , NULL AS CHGER_TELNO
    , NULL AS CHGER_EMAIL
    , NULL AS CHGER_ZIPCODE
    , NULL AS CHGER_BASE_ADDR
    , NULL AS CHGER_DTL_ADDR
    , NULL AS CHGER_RTN_MEMO
    , 0 AS ADD_PAY_COST
    , 0 AS ADD_PAY_AMT
    , NULL AS WD_INVOICE_NO
    , 'N' AS WD_INVOICE_SEND_YN
    , NULL AS SHIP_COMP_CD
    , NULL AS WD_STDT
    , NULL AS WD_EDDT
    , 'N' AS SWT_TRC_SEND_YN
    , NULL AS WD_SHIP_STATE
    , NULL AS WD_REASON_CD
    , NULL AS WD_STATE_DT
    , 'N' AS WD_BF_SEND_YN
    , C.REG_NO
    , B.DateCreated AS REG_DT
    , C.UPD_NO
    , B.DateCreated AS UPD_DT
    , A.OrderNo
    , B.ExceptionNo
FROM old_ord_order A, old_ord_orderexception B, tb_order C, old_ord_refund D
WHERE A.OrderNo = B.OrderNo
    AND A.OrderNo = C.ORD_NO
    AND B.ExceptionNo = D.ExceptionNo
    AND A.OrderStatusCd = '주문취소'
    AND B.ExceptionTypeCd = '취소'
    AND D.StatusCd = '환불완료'
    AND C.ORD_NO > 1000
; -- 30초

-- DELETE FROM tb_order_change_detail WHERE CHG_STAT = 'G685_18';
INSERT INTO tb_order_change_detail
SELECT DISTINCT -- old_ord_orderexceptionitem 데이터중복인게 있네
    ORD_CHG_SQ
    , ORD_DTL_NO
    , B.Qty
    , 'G685_18' AS CHG_STAT
    , NULL AS CHG_ORD_DTL_NO
    , NULL AS WH_MEMO
    , NULL AS COMPLETE_DT
    , 'N' AS DEL_YN
    , A.REG_NO
    , A.REG_DT
    , A.UPD_NO
    , A.UPD_DT
FROM tb_order_change A, old_ord_orderexceptionitem B, tb_order_detail C
WHERE A.ORD_CHG_SQ = B.ExceptionNo
    AND A.OLD_ORD_NO = C.ORD_NO
    AND B.OrderItemNo = C.ORD_DTL_NO
    AND A.CHG_GB = 'G680_20'
    AND B.ExceptionTypeCd = '취소'
    AND C.ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98')
    AND C.ORD_NO > 1000
; -- 12초


SELECT * FROM tb_order_detail WHERE ORD_NO = 14365690;
SELECT * FROM tb_order_change WHERE OLD_ORD_NO = 14365690;
SELECT * FROM old_ord_refund WHERE ExceptionNo = 1551870;
SELECT * FROM old_ord_orderexception WHERE ExceptionNo = 1551869;
SELECT * FROM old_ord_orderexception WHERE ExceptionNo = 1551870;
SELECT * FROM old_ord_orderexception WHERE ExceptionNo = 1547312;

SELECT DISTINCT StatusCd FROM old_ord_orderexception;

INSERT INTO tb_payment (ORD_NO, PAY_DT, PAY_MEANS, PAY_AMT, PG_CPN_AMT, NPAY_PNT_AMT, PAY_GB, PAY_STAT, PG_GB, PG_TID, PG_TRADE_NO, PG_SHOP_ID, PG_SHOP_KEY, CARD_TYPE, CARD_KIND, CARD_BANK, CARD_CD, CARD_NM, COM_CARD_CD, CARD_MIPS, CARD_PCABLE_YN, VA_NO, VA_NM, VA_BANK, VA_DEADLINE, TELECOM, ESCROW_YN, CASH_AUTH_NO, CASH_TRADE_NO, ORD_CHG_SQ, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT DISTINCT ORD_NO, C.DateRefund AS PAY_DT, PAY_MEANS, ((refundcash + refundCardCancel) * -1) AS PAY_AMT
     , PG_CPN_AMT, NPAY_PNT_AMT, PAY_GB, 'G016_99' AS PAY_STAT, PG_GB, PG_TID
     , PG_TRADE_NO, PG_SHOP_ID, PG_SHOP_KEY, CARD_TYPE, CARD_KIND
     , CARD_BANK, CARD_CD, CARD_NM, COM_CARD_CD, CARD_MIPS
     , CARD_PCABLE_YN, NULL AS VA_NO, NULL AS VA_NM, NULL AS VA_BANK, NULL AS VA_DEADLINE
     , TELECOM, ESCROW_YN, CASH_AUTH_NO, CASH_TRADE_NO
     , B.ORD_CHG_SQ, B.REG_NO, B.REG_DT, B.UPD_NO, B.UPD_DT
FROM tb_payment A, tb_order_change B, old_ord_refund C
WHERE A.ORD_NO = B.OLD_ORD_NO
    AND B.ORD_CHG_SQ = C.ExceptionNo
    AND EXISTS(
        SELECT 1
        FROM tb_order_detail B
        WHERE B.ORD_NO = A.ORD_NO
            AND B.ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98')
    )
    AND C.StatusCd = '환불완료'
    AND A.ORD_NO > 1000
; -- 16초

TRUNCATE TABLE tb_refund;

INSERT INTO tb_refund
SELECT
    A.ORD_NO
    , A.PAY_SQ
    , A.ORD_CHG_SQ
    , (refundcash + refundCardCancel) AS REFUND_AMT
	, 0 AS DEPOSIT_AMT
    , C.RefundAccount AS RA_NO
    , C.RefundBank AS RA_BANK
    , C.AccountUserName AS RA_NM
    , 0 AS RF_CPN1_AMT
    , 0 AS RF_TMTB1_AMT
    , 0 AS RF_TMTB2_AMT
    , 0 AS RF_GOODS_CPN_AMT
    , C.RefundCouponDiscount AS RF_CART_CPN_AMT
    , 0 AS RF_DELV_CPN_AMT
    , C.RefundMileage AS RF_PNT_AMT
    , 0 AS RF_PRE_PNT_AMT
    , C.RefundTokenAmt AS RF_GFCD_USE_AMT
	, 0 AS RF_DELV_GFCD_USE_AMT
    , ((refundcash + refundCardCancel + CustomerChargeDeliveryCost + refundTokenAmt + refundMileage + RefundCouponDiscount) - refundDeliveryCost) AS RF_CNCL_AMT
    , C.RefundDeliveryCost AS RF_DELIVERY_FEE
    , ((refundcash + refundCardCancel + CustomerChargeDeliveryCost) - refundDeliveryCost) AS RF_REAL_CNCL_AMT
    , CustomerChargeDeliveryCost AS CUST_DELV_COST
    , refundMemo AS RF_MEMO
    , B.REG_NO
    , B.REG_DT
    , 'N' AS ENC_UPD_YN
FROM tb_payment A, tb_order_change B, old_ord_refund C
WHERE A.ORD_NO = B.OLD_ORD_NO
    AND A.ORD_CHG_SQ = B.ORD_CHG_SQ
    AND B.ORD_CHG_SQ = C.ExceptionNo
    AND EXISTS(
        SELECT 1
        FROM tb_order_detail B
        WHERE B.ORD_NO = A.ORD_NO
            AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98')
    )
    AND C.StatusCd = '환불완료'
    AND A.ORD_NO > 1000
; -- 12초

UPDATE tb_order_detail A
SET CNCL_RTN_QTY = ORD_QTY
    , CNCL_RTN_AMT = ORD_AMT
    , GOODS_CPN_DC_AMT = 0
    , CART_CPN_DC_AMT = 0
    , PNT_DC_AMT = 0
    , SAVE_PNT_AMT = 0
    , REAL_ORD_AMT = 0
    , GFCD_USE_AMT = 0
    , ORD_DTL_STAT = 'G013_99'
    -- , UPD_NO = 0
    , UPD_DT = (SELECT MAX(X.REG_DT) FROM tb_order_change_detail X WHERE X.ORD_DTL_NO = A.ORD_DTL_NO)
WHERE EXISTS(
    SELECT 1
    FROM tb_order_change_detail B
    WHERE B.ORD_DTL_NO = A.ORD_DTL_NO
        AND CHG_STAT = 'G685_18'
)
AND A.ORD_NO > 1000
; -- 40초

-- 주문상세별 전체 취소
INSERT INTO tb_order_change (ORD_CHG_SQ, CHG_GB, CHG_REASON, CHG_MEMO, WD_GB, CHGER_NM, CHGER_PHNNO, CHGER_TELNO, CHGER_EMAIL, CHGER_ZIPCODE, CHGER_BASE_ADDR, CHGER_DTL_ADDR, CHGER_RTN_MEMO, ADD_PAY_COST, ADD_PAY_AMT, WD_INVOICE_NO, WD_INVOICE_SEND_YN, SHIP_COMP_CD, WD_STDT, WD_EDDT, SWT_TRC_SEND_YN, WD_SHIP_STATE, WD_REASON_CD, WD_STATE_DT, WD_BF_SEND_YN, REG_NO, REG_DT, UPD_NO, UPD_DT, OLD_ORD_NO, OLD_EXEPTION_NO)
SELECT DISTINCT
    C.ExceptionNo
    , CHG_GB
    , CHG_REASON
    , C.AdminMemo            AS CHG_MEMO
    , WD_GB
    , CHGER_NM
    , CHGER_PHNNO
    , CHGER_TELNO
    , CHGER_EMAIL
    , CHGER_ZIPCODE
    , CHGER_BASE_ADDR
    , CHGER_DTL_ADDR
    , CHGER_RTN_MEMO
    , ADD_PAY_COST
    , ADD_PAY_AMT
    , WD_INVOICE_NO
    , WD_INVOICE_SEND_YN
    , SHIP_COMP_CD
    , WD_STDT
    , WD_EDDT
    , SWT_TRC_SEND_YN
    , WD_SHIP_STATE
    , WD_REASON_CD
    , WD_STATE_DT
    , WD_BF_SEND_YN
    , REG_NO
    , C.DateCreated AS REG_DT
    , REG_NO
    , C.DateLastModified AS UPD_DT
    , ORD_NO
    , C.ExceptionNo
FROM (
         SELECT 'G680_20'              AS CHG_GB
              , CASE
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '취소') = '취소' THEN 'G686_10'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '취소') = '고객변심' THEN 'G686_10'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '취소') = '품절' THEN 'G686_30'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '취소') = '상품불량' THEN 'G686_11'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '취소') = '오배송' THEN 'G686_12'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '취소') = '배송지연' THEN 'G686_13'
                    ELSE 'G686_90' END AS CHG_REASON
              , NULL                   AS WD_GB
              , NULL                   AS CHGER_NM
              , NULL                   AS CHGER_PHNNO
              , NULL                   AS CHGER_TELNO
              , NULL                   AS CHGER_EMAIL
              , NULL                   AS CHGER_ZIPCODE
              , NULL                   AS CHGER_BASE_ADDR
              , NULL                   AS CHGER_DTL_ADDR
              , NULL                   AS CHGER_RTN_MEMO
              , 0                      AS ADD_PAY_COST
              , 0                      AS ADD_PAY_AMT
              , NULL                   AS WD_INVOICE_NO
              , 'N'                    AS WD_INVOICE_SEND_YN
              , NULL                   AS SHIP_COMP_CD
              , NULL                   AS WD_STDT
              , NULL                   AS WD_EDDT
              , 'N'                    AS SWT_TRC_SEND_YN
              , NULL                   AS WD_SHIP_STATE
              , NULL                   AS WD_REASON_CD
              , NULL                   AS WD_STATE_DT
              , 'N'                    AS WD_BF_SEND_YN
              , A.REG_NO
              , A.UPD_NO
              , A.ORD_NO
              , B.ExceptionNo
         FROM tb_order_detail A,
              old_ord_orderexceptionitem B
         WHERE A.ORD_DTL_NO = B.OrderItemNo
           AND A.ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
           AND B.ExceptionTypeCd = '취소'
           AND A.ORD_QTY = B.Qty
           AND B.StatusCd = '처리완료'
           AND A.ORD_NO > 1000
     ) AA, old_ord_orderexception C
WHERE AA.ExceptionNo = C.ExceptionNo
    -- AND AA.ExceptionNo = 1888671
; -- 7초

INSERT INTO tb_order_change_detail
SELECT DISTINCT -- old_ord_orderexceptionitem 데이터중복인게 있네
    ORD_CHG_SQ
    , ORD_DTL_NO
    , B.Qty
    , 'G685_18' AS CHG_STAT
    , NULL AS CHG_ORD_DTL_NO
    , NULL AS WH_MEMO
    , NULL AS COMPLETE_DT
    , 'N' AS DEL_YN
    , A.REG_NO
    , A.REG_DT
    , A.UPD_NO
    , A.UPD_DT
FROM tb_order_change A, old_ord_orderexceptionitem B, tb_order_detail C
WHERE A.ORD_CHG_SQ = B.ExceptionNo
    AND A.OLD_ORD_NO = C.ORD_NO
    AND B.OrderItemNo = C.ORD_DTL_NO
    AND A.CHG_GB = 'G680_20'
    AND B.ExceptionTypeCd = '취소'
    AND C.ORD_QTY = B.Qty
    AND C.ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
    AND B.StatusCd = '처리완료'
    AND C.ORD_NO > 1000
; -- 5초

UPDATE tb_order_detail A
SET CNCL_RTN_QTY = ORD_QTY
    , CNCL_RTN_AMT = ORD_AMT
    , GOODS_CPN_DC_AMT = 0
    , CART_CPN_DC_AMT = 0
    , PNT_DC_AMT = 0
    , SAVE_PNT_AMT = 0
    , REAL_ORD_AMT = 0
    , GFCD_USE_AMT = 0
    -- , UPD_NO = 0
    , UPD_DT = (SELECT MAX(X.REG_DT) FROM tb_order_change_detail X WHERE X.ORD_DTL_NO = A.ORD_DTL_NO)
    , ORD_DTL_STAT = 'G013_99'
WHERE EXISTS(
    SELECT 1
    FROM old_ord_orderexceptionitem B
    WHERE B.OrderItemNo = A.ORD_DTL_NO
        AND B.ExceptionTypeCd = '취소'
        AND A.ORD_QTY = B.Qty
        AND B.StatusCd = '처리완료'
)
AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
 --   AND ORD_DTL_STAT = 'G013_20'
; -- 9초

-- 수량부분취소
INSERT INTO tb_order_change_detail
SELECT DISTINCT -- old_ord_orderexceptionitem 데이터중복인게 있네
    ORD_CHG_SQ
    , ORD_DTL_NO
    , B.Qty
    , 'G685_XX' AS CHG_STAT
    , NULL AS CHG_ORD_DTL_NO
    , NULL AS WH_MEMO
    , NULL AS COMPLETE_DT
    , 'N' AS DEL_YN
    , A.REG_NO
    , A.REG_DT
    , A.UPD_NO
    , A.UPD_DT
FROM tb_order_change A, old_ord_orderexceptionitem B, tb_order_detail C
WHERE A.ORD_CHG_SQ = B.ExceptionNo
    AND A.OLD_ORD_NO = C.ORD_NO
    AND B.OrderItemNo = C.ORD_DTL_NO
    AND A.CHG_GB = 'G680_20'
    AND B.ExceptionTypeCd = '취소'
    AND C.ORD_QTY != B.Qty
    AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
    AND B.StatusCd = '처리완료'
    AND C.ORD_NO > 1000
;

INSERT INTO tb_order_change (ORD_CHG_SQ, CHG_GB, CHG_REASON, CHG_MEMO, WD_GB, CHGER_NM, CHGER_PHNNO, CHGER_TELNO, CHGER_EMAIL, CHGER_ZIPCODE, CHGER_BASE_ADDR, CHGER_DTL_ADDR, CHGER_RTN_MEMO, ADD_PAY_COST, ADD_PAY_AMT, WD_INVOICE_NO, WD_INVOICE_SEND_YN, SHIP_COMP_CD, WD_STDT, WD_EDDT, SWT_TRC_SEND_YN, WD_SHIP_STATE, WD_REASON_CD, WD_STATE_DT, WD_BF_SEND_YN, REG_NO, REG_DT, UPD_NO, UPD_DT, OLD_ORD_NO, OLD_EXEPTION_NO)
SELECT DISTINCT
    C.ExceptionNo
    , CHG_GB
    , CHG_REASON
    , C.AdminMemo            AS CHG_MEMO
    , WD_GB
    , CHGER_NM
    , CHGER_PHNNO
    , CHGER_TELNO
    , CHGER_EMAIL
    , CHGER_ZIPCODE
    , CHGER_BASE_ADDR
    , CHGER_DTL_ADDR
    , CHGER_RTN_MEMO
    , ADD_PAY_COST
    , ADD_PAY_AMT
    , WD_INVOICE_NO
    , WD_INVOICE_SEND_YN
    , SHIP_COMP_CD
    , WD_STDT
    , WD_EDDT
    , SWT_TRC_SEND_YN
    , WD_SHIP_STATE
    , WD_REASON_CD
    , WD_STATE_DT
    , WD_BF_SEND_YN
    , REG_NO
    , C.DateCreated AS REG_DT
    , UPD_NO
    , C.DateLastModified AS UPD_DT
    , ORD_NO
    , C.ExceptionNo
FROM (
         SELECT 'G680_20'              AS CHG_GB
              , CASE
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '취소') = '취소' THEN 'G686_10'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '취소') = '고객변심' THEN 'G686_10'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '취소') = '품절' THEN 'G686_30'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '취소') = '상품불량' THEN 'G686_11'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '취소') = '오배송' THEN 'G686_12'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '취소') = '배송지연' THEN 'G686_13'
                    ELSE 'G686_90' END AS CHG_REASON
              , NULL                   AS WD_GB
              , NULL                   AS CHGER_NM
              , NULL                   AS CHGER_PHNNO
              , NULL                   AS CHGER_TELNO
              , NULL                   AS CHGER_EMAIL
              , NULL                   AS CHGER_ZIPCODE
              , NULL                   AS CHGER_BASE_ADDR
              , NULL                   AS CHGER_DTL_ADDR
              , NULL                   AS CHGER_RTN_MEMO
              , 0                      AS ADD_PAY_COST
              , 0                      AS ADD_PAY_AMT
              , NULL                   AS WD_INVOICE_NO
              , 'N'                    AS WD_INVOICE_SEND_YN
              , NULL                   AS SHIP_COMP_CD
              , NULL                   AS WD_STDT
              , NULL                   AS WD_EDDT
              , 'N'                    AS SWT_TRC_SEND_YN
              , NULL                   AS WD_SHIP_STATE
              , NULL                   AS WD_REASON_CD
              , NULL                   AS WD_STATE_DT
              , 'N'                    AS WD_BF_SEND_YN
              , A.REG_NO
              , A.REG_DT
              , A.UPD_NO
              , A.UPD_DT
              , A.ORD_NO
              , B.ExceptionNo
         FROM tb_order_detail A,
              old_ord_orderexceptionitem B
         WHERE A.ORD_DTL_NO = B.OrderItemNo
           AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
           AND B.ExceptionTypeCd = '취소'
           AND A.ORD_QTY != B.Qty
           AND B.StatusCd = '처리완료'
           AND A.ORD_NO > 1000
     ) AA, old_ord_orderexception C
WHERE AA.ExceptionNo = C.ExceptionNo
    AND NOT EXISTS(
       SELECT 1
       FROM tb_order_change X
       WHERE X.ORD_CHG_SQ = C.ExceptionNo
     )
    -- AND C.ExceptionNo = 1550590
;

INSERT INTO tb_order_change_detail
SELECT DISTINCT -- old_ord_orderexceptionitem 데이터중복인게 있네
    ORD_CHG_SQ
    , ORD_DTL_NO
    , B.Qty
    , 'G685_XX' AS CHG_STAT
    , NULL AS CHG_ORD_DTL_NO
    , NULL AS WH_MEMO
    , NULL AS COMPLETE_DT
    , 'N' AS DEL_YN
    , A.REG_NO
    , A.REG_DT
    , A.UPD_NO
    , A.UPD_DT
FROM tb_order_change A, old_ord_orderexceptionitem B, tb_order_detail C
WHERE A.ORD_CHG_SQ = B.ExceptionNo
    AND A.OLD_ORD_NO = C.ORD_NO
    AND B.OrderItemNo = C.ORD_DTL_NO
    AND A.CHG_GB = 'G680_20'
    AND B.ExceptionTypeCd = '취소'
    AND C.ORD_QTY != B.Qty
    AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
    AND B.StatusCd = '처리완료'
    AND C.ORD_NO > 1000
    AND NOT EXISTS(
       SELECT 1
       FROM tb_order_change_detail X
       WHERE X.ORD_CHG_SQ = B.ExceptionNo
            AND X.ORD_DTL_NO = B.OrderItemNo
     )
;

INSERT INTO tb_payment (ORD_NO, PAY_DT, PAY_MEANS, PAY_AMT, PG_CPN_AMT, NPAY_PNT_AMT, PAY_GB, PAY_STAT, PG_GB, PG_TID, PG_TRADE_NO, PG_SHOP_ID, PG_SHOP_KEY, CARD_TYPE, CARD_KIND, CARD_BANK, CARD_CD, CARD_NM, COM_CARD_CD, CARD_MIPS, CARD_PCABLE_YN, VA_NO, VA_NM, VA_BANK, VA_DEADLINE, TELECOM, ESCROW_YN, CASH_AUTH_NO, CASH_TRADE_NO, ORD_CHG_SQ, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT DISTINCT ORD_NO, C.DateRefund AS PAY_DT, PAY_MEANS, ((refundcash + refundCardCancel) * -1) AS PAY_AMT
     , PG_CPN_AMT, NPAY_PNT_AMT, PAY_GB, 'G016_99' AS PAY_STAT, PG_GB, PG_TID
     , PG_TRADE_NO, PG_SHOP_ID, PG_SHOP_KEY, CARD_TYPE, CARD_KIND
     , CARD_BANK, CARD_CD, CARD_NM, COM_CARD_CD, CARD_MIPS
     , CARD_PCABLE_YN, NULL AS VA_NO, NULL AS VA_NM, NULL AS VA_BANK, NULL AS VA_DEADLINE
     , TELECOM, ESCROW_YN, CASH_AUTH_NO, CASH_TRADE_NO
     , B.ORD_CHG_SQ, B.REG_NO, B.REG_DT, B.UPD_NO, B.UPD_DT
FROM tb_payment A, tb_order_change B, old_ord_refund C
WHERE A.ORD_NO = B.OLD_ORD_NO
    AND B.ORD_CHG_SQ = C.ExceptionNo
    AND B.CHG_GB = 'G680_20'
    AND C.StatusCd = '환불완료'
    AND A.ORD_NO > 1000
; -- 10초

INSERT INTO tb_refund
SELECT
    A.ORD_NO
    , A.PAY_SQ
    , A.ORD_CHG_SQ
    , (refundcash + refundCardCancel) AS REFUND_AMT
	, 0 AS DEPOSIT_AMT
    , C.RefundAccount AS RA_NO
    , C.RefundBank AS RA_BANK
    , C.AccountUserName AS RA_NM
    , 0 AS RF_CPN1_AMT
    , 0 AS RF_TMTB1_AMT
    , 0 AS RF_TMTB2_AMT
    , 0 AS RF_GOODS_CPN_AMT
    , C.RefundCouponDiscount AS RF_CART_CPN_AMT
    , 0 AS RF_DELV_CPN_AMT
    , C.RefundMileage AS RF_PNT_AMT
    , 0 AS RF_PRE_PNT_AMT
    , C.RefundTokenAmt AS RF_GFCD_USE_AMT
	, 0 AS RF_DELV_GFCD_USE_AMT
    , ((refundcash + refundCardCancel + CustomerChargeDeliveryCost + refundTokenAmt + refundMileage + RefundCouponDiscount) - refundDeliveryCost) AS RF_CNCL_AMT
    , C.RefundDeliveryCost AS RF_DELIVERY_FEE
    , ((refundcash + refundCardCancel + CustomerChargeDeliveryCost) - refundDeliveryCost) AS RF_REAL_CNCL_AMT
    , CustomerChargeDeliveryCost AS CUST_DELV_COST
    , refundMemo AS RF_MEMO
    , B.REG_NO
    , B.REG_DT
    , 'N' AS ENC_UPD_YN
FROM tb_payment A, tb_order_change B, old_ord_refund C
WHERE A.ORD_NO = B.OLD_ORD_NO
    AND A.ORD_CHG_SQ = B.ORD_CHG_SQ
    AND B.ORD_CHG_SQ = C.ExceptionNo
    AND B.CHG_GB = 'G680_20'
    AND C.StatusCd = '환불완료'
    AND A.ORD_NO > 1000
; -- 7초

-- 이후 배치 실행 TsbMigrationOrderTask cancelQtyOrderDetailJob

SELECT
    A.ORD_CHG_SQ
    , A.ORD_DTL_NO
    , B.ORD_QTY
    , B.ORD_AMT
    , B.CNCL_RTN_QTY
    , B.CNCL_RTN_AMT
    , B.GOODS_CPN_DC_AMT
    , B.CART_CPN_DC_AMT
    , B.PNT_DC_AMT
    , B.SAVE_PNT_AMT
    , B.REAL_ORD_AMT
    , B.GFCD_USE_AMT
    , A.CHG_QTY
FROM tb_order_change_detail A, tb_order_detail B
WHERE A.ORD_DTL_NO = B.ORD_DTL_NO
    AND A.CHG_STAT = 'G685_XX'
ORDER BY A.ORD_DTL_NO
;

-- 원주문배송비 환불
INSERT INTO tb_delivery_fee (PAY_SQ, ORD_NO, DELV_FEE_GB, DELV_FEE_CD, DELV_FEE, DELV_CPN_SQ, DELV_CPN_DC_AMT, REAL_DELV_AMT, ORD_CHG_SQ, SUPPLY_COMP_CD, DELV_USAC_YN, DELV_USAC_DT, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT
    A.PAY_SQ
    , A.ORD_NO
    , 'G018_10' AS DELV_FEE_GB
    , B.DELV_FEE_CD
    , RF_DELIVERY_FEE * -1 AS DELV_FEE
    , NULL AS DELV_CPN_SQ
    , 0 AS DELV_CPN_DC_AMT
    , RF_DELIVERY_FEE * -1 AS REAL_DELV_AMT
    , A.ORD_CHG_SQ
    , B.SUPPLY_COMP_CD
    , 'N' AS DELV_USAC_YN
    , NULL AS DELV_USAC_DT
    , A.REG_NO
    , A.REG_DT
    , A.REG_NO AS UPD_NO
    , A.REG_DT AS UPD_DT
FROM tb_refund A, tb_delivery_fee B
WHERE A.ORD_NO = B.ORD_NO
    AND A.PAY_SQ != B.PAY_SQ
    AND RF_DELIVERY_FEE > 0
;



-- 반품
-- 주문상세별 반품
INSERT INTO tb_order_change (ORD_CHG_SQ, CHG_GB, CHG_REASON, CHG_MEMO, WD_GB, CHGER_NM, CHGER_PHNNO, CHGER_TELNO, CHGER_EMAIL, CHGER_ZIPCODE, CHGER_BASE_ADDR, CHGER_DTL_ADDR, CHGER_RTN_MEMO, ADD_PAY_COST, ADD_PAY_AMT, WD_INVOICE_NO, WD_INVOICE_SEND_YN, SHIP_COMP_CD, WD_STDT, WD_EDDT, SWT_TRC_SEND_YN, WD_SHIP_STATE, WD_REASON_CD, WD_STATE_DT, WD_BF_SEND_YN, REG_NO, REG_DT, UPD_NO, UPD_DT, OLD_ORD_NO, OLD_EXEPTION_NO)
SELECT DISTINCT
    C.ExceptionNo
    , CHG_GB
    , CHG_REASON
    , C.AdminMemo            AS CHG_MEMO
    , WD_GB
    , CHGER_NM
    , CHGER_PHNNO
    , CHGER_TELNO
    , CHGER_EMAIL
    , CHGER_ZIPCODE
    , CHGER_BASE_ADDR
    , CHGER_DTL_ADDR
    , CHGER_RTN_MEMO
    , ADD_PAY_COST
    , ADD_PAY_AMT
    , WD_INVOICE_NO
    , WD_INVOICE_SEND_YN
    , SHIP_COMP_CD
    , WD_STDT
    , WD_EDDT
    , SWT_TRC_SEND_YN
    , WD_SHIP_STATE
    , WD_REASON_CD
    , WD_STATE_DT
    , WD_BF_SEND_YN
    , REG_NO
    , C.DateCreated AS REG_DT
    , REG_NO
    , C.DateLastModified AS UPD_DT
    , ORD_NO
    , C.ExceptionNo
FROM (
         SELECT 'G680_30'              AS CHG_GB
              , CASE
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '반품') = '고객변심' THEN 'G688_10'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '반품') = '상품불량' THEN 'G688_11'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '반품') = '품절' THEN 'G688_17'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '반품') = '오배송' THEN 'G688_12'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '반품') = '배송지연' THEN 'G688_13'
                    ELSE 'G688_90' END AS CHG_REASON
              , NULL                   AS WD_GB
              , (SELECT RECIP_NM FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_NM
              , (SELECT RECIP_PHNNO FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_PHNNO
              , (SELECT RECIP_TELNO FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_TELNO
              , (SELECT ORD_EMAIL FROM tb_order X WHERE X.ORD_NO = A.ORD_NO)                   AS CHGER_EMAIL
              , (SELECT RECIP_ZIPCODE FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_ZIPCODE
              , (SELECT RECIP_BASE_ADDR FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_BASE_ADDR
              , (SELECT RECIP_DTL_ADDR FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_DTL_ADDR
              , NULL                   AS CHGER_RTN_MEMO
              , 0                      AS ADD_PAY_COST
              , 0                      AS ADD_PAY_AMT
              , NULL                   AS WD_INVOICE_NO
              , 'N'                    AS WD_INVOICE_SEND_YN
              , NULL                   AS SHIP_COMP_CD
              , NULL                   AS WD_STDT
              , NULL                   AS WD_EDDT
              , 'N'                    AS SWT_TRC_SEND_YN
              , NULL                   AS WD_SHIP_STATE
              , NULL                   AS WD_REASON_CD
              , NULL                   AS WD_STATE_DT
              , 'N'                    AS WD_BF_SEND_YN
              , A.REG_NO
              , A.REG_DT
              , A.UPD_NO
              , A.UPD_DT
              , A.ORD_NO
              , B.ExceptionNo
         FROM tb_order_detail A,
              old_ord_orderexceptionitem B
         WHERE A.ORD_DTL_NO = B.OrderItemNo
           AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
           AND B.ExceptionTypeCd = '반품'
           AND (A.ORD_QTY - A.CNCL_RTN_QTY) = B.Qty
           AND B.StatusCd IS NOT NULL
     ) AA, old_ord_orderexception C
WHERE AA.ExceptionNo = C.ExceptionNo
-- AND C.ExceptionNo = 1548177
; --

-- 상세별전체반품
INSERT INTO tb_order_change_detail
SELECT DISTINCT -- old_ord_orderexceptionitem 데이터중복인게 있네
    ORD_CHG_SQ
    , ORD_DTL_NO
    , B.Qty
    , CASE WHEN B.StatusCd = '처리완료' THEN 'G685_60'
        WHEN B.StatusCd = '회수지시' THEN 'G685_30'
        WHEN B.StatusCd IN ('환불대기','회수완료','환불지시') THEN 'G685_33'
        ELSE 'G685_50' END AS CHG_STAT
    , NULL AS CHG_ORD_DTL_NO
    , NULL AS WH_MEMO
    , NULL AS COMPLETE_DT
    , 'N' AS DEL_YN
    , A.REG_NO
    , A.REG_DT
    , A.UPD_NO
    , A.UPD_DT
FROM tb_order_change A, old_ord_orderexceptionitem B, tb_order_detail C
WHERE A.ORD_CHG_SQ = B.ExceptionNo
    AND A.OLD_ORD_NO = C.ORD_NO
    AND B.OrderItemNo = C.ORD_DTL_NO
    AND A.CHG_GB = 'G680_30'
    AND B.ExceptionTypeCd = '반품'
    AND (C.ORD_QTY - C.CNCL_RTN_QTY) = B.Qty
    AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
    AND B.StatusCd IS NOT NULL
    AND C.ORD_NO > 1000
; --


UPDATE tb_order_detail A, tb_order_change_detail B
SET CNCL_RTN_QTY = ORD_QTY
    , CNCL_RTN_AMT = ORD_AMT
    , GOODS_CPN_DC_AMT = 0
    , CART_CPN_DC_AMT = 0
    , PNT_DC_AMT = 0
    , SAVE_PNT_AMT = 0
    , REAL_ORD_AMT = 0
    , GFCD_USE_AMT = 0
    , ORD_DTL_STAT = 'G013_99'
    -- , UPD_NO = 0
    , A.UPD_DT = (SELECT MAX(X.REG_DT) FROM tb_order_change_detail X WHERE X.ORD_DTL_NO = A.ORD_DTL_NO)
WHERE A.ORD_DTL_NO = B.ORD_DTL_NO
    AND B.CHG_STAT = 'G685_60'
    AND A.ORD_NO > 1000
; -- 40초

-- 수량부분 반품(처리완료)
INSERT INTO tb_order_change_detail
SELECT DISTINCT -- old_ord_orderexceptionitem 데이터중복인게 있네
    ORD_CHG_SQ
    , ORD_DTL_NO
    , B.Qty
    , 'G685_XX' AS CHG_STAT
    , NULL AS CHG_ORD_DTL_NO
    , NULL AS WH_MEMO
    , NULL AS COMPLETE_DT
    , 'N' AS DEL_YN
    , A.REG_NO
    , A.REG_DT
    , A.UPD_NO
    , A.UPD_DT
FROM tb_order_change A, old_ord_orderexceptionitem B, tb_order_detail C
WHERE A.ORD_CHG_SQ = B.ExceptionNo
    AND A.OLD_ORD_NO = C.ORD_NO
    AND B.OrderItemNo = C.ORD_DTL_NO
    AND A.CHG_GB = 'G680_30'
    AND B.ExceptionTypeCd = '반품'
    AND (C.ORD_QTY - C.CNCL_RTN_QTY) != B.Qty
    AND B.StatusCd IS NOT NULL
    AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
    AND B.StatusCd = '처리완료'
    AND C.ORD_NO > 1000
;

-- 수량부분 반품(처리중)
INSERT INTO tb_order_change_detail
SELECT DISTINCT -- old_ord_orderexceptionitem 데이터중복인게 있네
    ORD_CHG_SQ
    , ORD_DTL_NO
    , B.Qty
    , 'G685_XX' AS CHG_STAT
    , NULL AS CHG_ORD_DTL_NO
    , NULL AS WH_MEMO
    , NULL AS COMPLETE_DT
    , 'N' AS DEL_YN
    , A.REG_NO
    , A.REG_DT
    , A.UPD_NO
    , A.UPD_DT
FROM tb_order_change A, old_ord_orderexceptionitem B, tb_order_detail C
WHERE A.ORD_CHG_SQ = B.ExceptionNo
    AND A.OLD_ORD_NO = C.ORD_NO
    AND B.OrderItemNo = C.ORD_DTL_NO
    AND A.CHG_GB = 'G680_30'
    AND B.ExceptionTypeCd = '반품'
    AND (C.ORD_QTY - C.CNCL_RTN_QTY) != B.Qty
    AND B.StatusCd IS NOT NULL
    AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
    AND B.StatusCd != '처리완료'
    AND C.ORD_NO > 1000
;

-- 배치실행 TsbMigrationOrderTask returnQtyOrderDetailJob

-- 부분 반품만 있는것
INSERT INTO tb_order_change (ORD_CHG_SQ, CHG_GB, CHG_REASON, CHG_MEMO, WD_GB, CHGER_NM, CHGER_PHNNO, CHGER_TELNO, CHGER_EMAIL, CHGER_ZIPCODE, CHGER_BASE_ADDR, CHGER_DTL_ADDR, CHGER_RTN_MEMO, ADD_PAY_COST, ADD_PAY_AMT, WD_INVOICE_NO, WD_INVOICE_SEND_YN, SHIP_COMP_CD, WD_STDT, WD_EDDT, SWT_TRC_SEND_YN, WD_SHIP_STATE, WD_REASON_CD, WD_STATE_DT, WD_BF_SEND_YN, REG_NO, REG_DT, UPD_NO, UPD_DT, OLD_ORD_NO, OLD_EXEPTION_NO)
SELECT DISTINCT
    C.ExceptionNo
    , CHG_GB
    , CHG_REASON
    , C.AdminMemo            AS CHG_MEMO
    , WD_GB
    , CHGER_NM
    , CHGER_PHNNO
    , CHGER_TELNO
    , CHGER_EMAIL
    , CHGER_ZIPCODE
    , CHGER_BASE_ADDR
    , CHGER_DTL_ADDR
    , CHGER_RTN_MEMO
    , ADD_PAY_COST
    , ADD_PAY_AMT
    , WD_INVOICE_NO
    , WD_INVOICE_SEND_YN
    , SHIP_COMP_CD
    , WD_STDT
    , WD_EDDT
    , SWT_TRC_SEND_YN
    , WD_SHIP_STATE
    , WD_REASON_CD
    , WD_STATE_DT
    , WD_BF_SEND_YN
    , REG_NO
    , C.DateCreated AS REG_DT
    , REG_NO
    , C.DateLastModified AS UPD_DT
    , ORD_NO
    , C.ExceptionNo
FROM (
         SELECT 'G680_30'              AS CHG_GB
              , CASE
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '반품') = '고객변심' THEN 'G688_10'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '반품') = '상품불량' THEN 'G688_11'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '반품') = '품절' THEN 'G688_17'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '반품') = '오배송' THEN 'G688_12'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '반품') = '배송지연' THEN 'G688_13'
                    ELSE 'G688_90' END AS CHG_REASON
              , NULL                   AS WD_GB
              , (SELECT RECIP_NM FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_NM
              , (SELECT RECIP_PHNNO FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_PHNNO
              , (SELECT RECIP_TELNO FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_TELNO
              , (SELECT ORD_EMAIL FROM tb_order X WHERE X.ORD_NO = A.ORD_NO)                   AS CHGER_EMAIL
              , (SELECT RECIP_ZIPCODE FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_ZIPCODE
              , (SELECT RECIP_BASE_ADDR FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_BASE_ADDR
              , (SELECT RECIP_DTL_ADDR FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_DTL_ADDR
              , NULL                   AS CHGER_RTN_MEMO
              , 0                      AS ADD_PAY_COST
              , 0                      AS ADD_PAY_AMT
              , NULL                   AS WD_INVOICE_NO
              , 'N'                    AS WD_INVOICE_SEND_YN
              , NULL                   AS SHIP_COMP_CD
              , NULL                   AS WD_STDT
              , NULL                   AS WD_EDDT
              , 'N'                    AS SWT_TRC_SEND_YN
              , NULL                   AS WD_SHIP_STATE
              , NULL                   AS WD_REASON_CD
              , NULL                   AS WD_STATE_DT
              , 'N'                    AS WD_BF_SEND_YN
              , A.REG_NO
              , A.REG_DT
              , A.UPD_NO
              , A.UPD_DT
              , A.ORD_NO
              , B.ExceptionNo
         FROM tb_order_detail A,
              old_ord_orderexceptionitem B
         WHERE A.ORD_DTL_NO = B.OrderItemNo
           AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
           AND B.ExceptionTypeCd = '반품'
           AND (A.ORD_QTY - A.CNCL_RTN_QTY) != B.Qty
           AND B.StatusCd IS NOT NULL
     ) AA, old_ord_orderexception C
WHERE AA.ExceptionNo = C.ExceptionNo
    AND NOT EXISTS(
       SELECT 1
       FROM tb_order_change X
       WHERE X.ORD_CHG_SQ = C.ExceptionNo
     )
 -- AND C.ExceptionNo = 1553434
; --

-- 수량부분 반품(처리완료)
INSERT INTO tb_order_change_detail
SELECT DISTINCT -- old_ord_orderexceptionitem 데이터중복인게 있네
    ORD_CHG_SQ
    , ORD_DTL_NO
    , B.Qty
    , 'G685_XX' AS CHG_STAT
    , NULL AS CHG_ORD_DTL_NO
    , NULL AS WH_MEMO
    , NULL AS COMPLETE_DT
    , 'N' AS DEL_YN
    , A.REG_NO
    , A.REG_DT
    , A.UPD_NO
    , A.UPD_DT
FROM tb_order_change A, old_ord_orderexceptionitem B, tb_order_detail C
WHERE A.ORD_CHG_SQ = B.ExceptionNo
    AND A.OLD_ORD_NO = C.ORD_NO
    AND B.OrderItemNo = C.ORD_DTL_NO
    AND A.CHG_GB = 'G680_30'
    AND B.ExceptionTypeCd = '반품'
    AND (C.ORD_QTY - C.CNCL_RTN_QTY) != B.Qty
    AND B.StatusCd IS NOT NULL
    AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
    AND B.StatusCd = '처리완료'
    AND C.ORD_NO > 1000
    AND NOT EXISTS (
        SELECT 1
        FROM tb_order_change_detail X
        WHERE X.ORD_CHG_SQ = A.ORD_CHG_SQ
            AND X.ORD_DTL_NO = C.ORD_DTL_NO
    )
;

-- 수량부분 반품(처리중)
INSERT INTO tb_order_change_detail
SELECT DISTINCT -- old_ord_orderexceptionitem 데이터중복인게 있네
    ORD_CHG_SQ
    , ORD_DTL_NO
    , B.Qty
    , 'G685_XX' AS CHG_STAT
    , NULL AS CHG_ORD_DTL_NO
    , NULL AS WH_MEMO
    , NULL AS COMPLETE_DT
    , 'N' AS DEL_YN
    , A.REG_NO
    , A.REG_DT
    , A.UPD_NO
    , A.UPD_DT
FROM tb_order_change A, old_ord_orderexceptionitem B, tb_order_detail C
WHERE A.ORD_CHG_SQ = B.ExceptionNo
    AND A.OLD_ORD_NO = C.ORD_NO
    AND B.OrderItemNo = C.ORD_DTL_NO
    AND A.CHG_GB = 'G680_30'
    AND B.ExceptionTypeCd = '반품'
    AND (C.ORD_QTY - C.CNCL_RTN_QTY) != B.Qty
    AND B.StatusCd IS NOT NULL
    AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
    AND B.StatusCd != '처리완료'
    AND C.ORD_NO > 1000
    AND NOT EXISTS (
            SELECT 1
            FROM tb_order_change_detail X
            WHERE X.ORD_CHG_SQ = A.ORD_CHG_SQ
                AND X.ORD_DTL_NO = C.ORD_DTL_NO
        )
;

-- 결제내역
INSERT INTO tb_payment (ORD_NO, PAY_DT, PAY_MEANS, PAY_AMT, PG_CPN_AMT, NPAY_PNT_AMT, PAY_GB, PAY_STAT, PG_GB, PG_TID, PG_TRADE_NO, PG_SHOP_ID, PG_SHOP_KEY, CARD_TYPE, CARD_KIND, CARD_BANK, CARD_CD, CARD_NM, COM_CARD_CD, CARD_MIPS, CARD_PCABLE_YN, VA_NO, VA_NM, VA_BANK, VA_DEADLINE, TELECOM, ESCROW_YN, CASH_AUTH_NO, CASH_TRADE_NO, ORD_CHG_SQ, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT DISTINCT ORD_NO, C.DateRefund AS PAY_DT, PAY_MEANS, ((refundcash + refundCardCancel) * -1) AS PAY_AMT
     , PG_CPN_AMT, NPAY_PNT_AMT, PAY_GB, 'G016_99' AS PAY_STAT, PG_GB, PG_TID
     , PG_TRADE_NO, PG_SHOP_ID, PG_SHOP_KEY, CARD_TYPE, CARD_KIND
     , CARD_BANK, CARD_CD, CARD_NM, COM_CARD_CD, CARD_MIPS
     , CARD_PCABLE_YN, NULL AS VA_NO, NULL AS VA_NM, NULL AS VA_BANK, NULL AS VA_DEADLINE
     , TELECOM, ESCROW_YN, CASH_AUTH_NO, CASH_TRADE_NO
     , B.ORD_CHG_SQ, B.REG_NO, C.DateRefund AS REG_DT, B.UPD_NO, C.DateRefund AS UPD_DT
FROM tb_payment A, tb_order_change B, old_ord_refund C
WHERE A.ORD_NO = B.OLD_ORD_NO
    AND B.ORD_CHG_SQ = C.ExceptionNo
    AND B.CHG_GB = 'G680_30'
    /*AND EXISTS(
        SELECT 1
        FROM tb_order_change_detail X -- , old_ord_orderexceptionitem Y -- TB_ORDER_CHANGE_DETAIL 보면될듯
        WHERE X.ORD_CHG_SQ = B.ORD_CHG_SQ
            AND X.CHG_STAT = 'G685_XX'
    )*/
    AND C.StatusCd = '환불완료'
    AND A.ORD_NO > 1000
    -- AND B.ORD_CHG_SQ = 1548613
;

-- 완료건
INSERT INTO tb_refund
SELECT
    A.ORD_NO
    , A.PAY_SQ
    , A.ORD_CHG_SQ
    , (refundcash + refundCardCancel) AS REFUND_AMT
	, 0 AS DEPOSIT_AMT
    , C.RefundAccount AS RA_NO
    , C.RefundBank AS RA_BANK
    , C.AccountUserName AS RA_NM
    , 0 AS RF_CPN1_AMT
    , 0 AS RF_TMTB1_AMT
    , 0 AS RF_TMTB2_AMT
    , 0 AS RF_GOODS_CPN_AMT
    , C.RefundCouponDiscount AS RF_CART_CPN_AMT
    , 0 AS RF_DELV_CPN_AMT
    , C.RefundMileage AS RF_PNT_AMT
    , 0 AS RF_PRE_PNT_AMT
    , C.RefundTokenAmt AS RF_GFCD_USE_AMT
	, 0 AS RF_DELV_GFCD_USE_AMT
    , ((refundcash + refundCardCancel + CustomerChargeDeliveryCost + refundTokenAmt + refundMileage + RefundCouponDiscount) - refundDeliveryCost) AS RF_CNCL_AMT
    , C.RefundDeliveryCost AS RF_DELIVERY_FEE
    , ((refundcash + refundCardCancel + CustomerChargeDeliveryCost) - refundDeliveryCost) AS RF_REAL_CNCL_AMT
    , CustomerChargeDeliveryCost AS CUST_DELV_COST
    , refundMemo AS RF_MEMO
    , B.REG_NO
    , B.REG_DT
    , 'N' AS ENC_UPD_YN
FROM tb_payment A, tb_order_change B, old_ord_refund C
WHERE A.ORD_NO = B.OLD_ORD_NO
    AND A.ORD_CHG_SQ = B.ORD_CHG_SQ
    AND B.ORD_CHG_SQ = C.ExceptionNo
    AND B.CHG_GB = 'G680_30'
    /*AND EXISTS(
        SELECT 1
        FROM tb_order_change_detail X -- , old_ord_orderexceptionitem Y -- TB_ORDER_CHANGE_DETAIL 보면될듯
        WHERE X.ORD_CHG_SQ = B.ORD_CHG_SQ
            AND X.CHG_STAT = 'G685_XX'
    )*/
    AND C.StatusCd = '환불완료'
    AND B.OLD_ORD_NO > 1000
-- AND C.ExceptionNo = 1725163
; -- 7초

-- 취소/반품 완료일자 업데이트
UPDATE tb_order_change_detail A, OLD_ORD_REFUND B
SET COMPLETE_DT = DATEREFUND
WHERE A.ORD_CHG_SQ = B.EXCEPTIONNO
;

-- 반품진행중
INSERT INTO tb_refund
SELECT
    B.OLD_ORD_NO AS ORD_NO
    , 0 AS PAY_SQ
    , B.ORD_CHG_SQ
    , (refundcash + refundCardCancel) AS REFUND_AMT
	, 0 AS DEPOSIT_AMT
    , C.RefundAccount AS RA_NO
    , C.RefundBank AS RA_BANK
    , C.AccountUserName AS RA_NM
    , 0 AS RF_CPN1_AMT
    , 0 AS RF_TMTB1_AMT
    , 0 AS RF_TMTB2_AMT
    , 0 AS RF_GOODS_CPN_AMT
    , C.RefundCouponDiscount AS RF_CART_CPN_AMT
    , 0 AS RF_DELV_CPN_AMT
    , C.RefundMileage AS RF_PNT_AMT
    , 0 AS RF_PRE_PNT_AMT
    , C.RefundTokenAmt AS RF_GFCD_USE_AMT
	, 0 AS RF_DELV_GFCD_USE_AMT
    , ((refundcash + refundCardCancel + CustomerChargeDeliveryCost + refundTokenAmt + refundMileage + RefundCouponDiscount) - refundDeliveryCost) AS RF_CNCL_AMT
    , C.RefundDeliveryCost AS RF_DELIVERY_FEE
    , ((refundcash + refundCardCancel + CustomerChargeDeliveryCost) - refundDeliveryCost) AS RF_REAL_CNCL_AMT
    , CustomerChargeDeliveryCost AS CUST_DELV_COST
    , refundMemo AS RF_MEMO
    , B.REG_NO
    , B.REG_DT
    , 'N' AS ENC_UPD_YN
FROM tb_order_change B, old_ord_refund C
WHERE B.ORD_CHG_SQ = C.ExceptionNo
    AND B.CHG_GB = 'G680_30'
    /*AND EXISTS(
        SELECT 1
        FROM tb_order_change_detail X -- , old_ord_orderexceptionitem Y -- TB_ORDER_CHANGE_DETAIL 보면될듯
        WHERE X.ORD_CHG_SQ = B.ORD_CHG_SQ
            AND X.CHG_STAT = 'G685_XX'
    )*/
    AND C.StatusCd != '환불완료'
    AND B.OLD_ORD_NO > 1000
 -- AND C.ExceptionNo = 1548613
;

-- 배치실행 TsbMigrationOrderTask returnQtyOrderDetailJob

-- 반품배송비 (마지막)
INSERT INTO tb_delivery_fee ( PAY_SQ, ORD_NO, DELV_FEE_GB, DELV_FEE_CD, DELV_FEE, DELV_CPN_SQ, DELV_CPN_DC_AMT, REAL_DELV_AMT, ORD_CHG_SQ, SUPPLY_COMP_CD, DELV_USAC_YN, DELV_USAC_DT, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT
    (SELECT PAY_SQ FROM tb_payment X WHERE X.ORD_CHG_SQ = A.ORD_CHG_SQ LIMIT 1) AS PAY_SQ
    , A.OLD_ORD_NO
    , 'G018_40' AS DELV_FEE_GB
    , (SELECT DELV_FEE_CD FROM tb_delivery_fee X WHERE X.ORD_NO = A.OLD_ORD_NO LIMIT 1) AS DELV_FEE_CD
    , B.CustomerChargeDeliveryCost AS DELV_FEE
    , NULL AS DELV_CPN_SQ
    , 0 AS DELV_CPN_DC_AMT
    , B.CustomerChargeDeliveryCost AS REAL_DELV_AMT
    , A.ORD_CHG_SQ
    , (SELECT SUPPLY_COMP_CD FROM tb_delivery_fee X WHERE X.ORD_NO = A.OLD_ORD_NO LIMIT 1) AS SUPPLY_COMP_CD
    , 'N' AS DELV_USAC_YN
    , NULL AS DELV_USAC_DT
    , A.REG_NO
    , A.REG_DT
    , A.REG_NO AS UPD_NO
    , A.REG_DT AS UPD_DT
FROM tb_order_change A, old_ord_refund B
WHERE A.ORD_CHG_SQ = B.ExceptionNo
    AND A.CHG_GB = 'G680_30'
    AND B.StatusCd = '환불완료'
;

/*
INSERT INTO tb_delivery_fee ( PAY_SQ, ORD_NO, DELV_FEE_GB, DELV_FEE_CD, DELV_FEE, DELV_CPN_SQ, DELV_CPN_DC_AMT, REAL_DELV_AMT, ORD_CHG_SQ, SUPPLY_COMP_CD, DELV_USAC_YN, DELV_USAC_DT, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT
    A.PAY_SQ
    , A.ORD_NO
    , 'G018_40' AS DELV_FEE_GB
    , B.DELV_FEE_CD
    , RF_DELIVERY_FEE * -1 AS DELV_FEE
    , NULL AS DELV_CPN_SQ
    , 0 AS DELV_CPN_DC_AMT
    , RF_DELIVERY_FEE * -1 AS REAL_DELV_AMT
    , A.ORD_CHG_SQ
    , B.SUPPLY_COMP_CD
    , 'N' AS DELV_USAC_YN
    , NULL AS DELV_USAC_DT
    , A.REG_NO
    , A.REG_DT
    , A.REG_NO AS UPD_NO
    , A.REG_DT AS UPD_DT
FROM tb_refund A, tb_delivery_fee B
WHERE A.ORD_NO = B.ORD_NO
    AND A.PAY_SQ != B.PAY_SQ
    AND RF_DELIVERY_FEE > 0
;
*/

-- 교환
-- 주문상세별 교환
INSERT INTO tb_order_change (ORD_CHG_SQ, CHG_GB, CHG_REASON, CHG_MEMO, WD_GB, CHGER_NM, CHGER_PHNNO, CHGER_TELNO, CHGER_EMAIL, CHGER_ZIPCODE, CHGER_BASE_ADDR, CHGER_DTL_ADDR, CHGER_RTN_MEMO, ADD_PAY_COST, ADD_PAY_AMT, WD_INVOICE_NO, WD_INVOICE_SEND_YN, SHIP_COMP_CD, WD_STDT, WD_EDDT, SWT_TRC_SEND_YN, WD_SHIP_STATE, WD_REASON_CD, WD_STATE_DT, WD_BF_SEND_YN, REG_NO, REG_DT, UPD_NO, UPD_DT, OLD_ORD_NO, OLD_EXEPTION_NO)
SELECT DISTINCT
    C.ExceptionNo
    , CHG_GB
    , CHG_REASON
    , C.AdminMemo            AS CHG_MEMO
    , WD_GB
    , CHGER_NM
    , CHGER_PHNNO
    , CHGER_TELNO
    , CHGER_EMAIL
    , CHGER_ZIPCODE
    , CHGER_BASE_ADDR
    , CHGER_DTL_ADDR
    , CHGER_RTN_MEMO
    , ADD_PAY_COST
    , ADD_PAY_AMT
    , WD_INVOICE_NO
    , WD_INVOICE_SEND_YN
    , SHIP_COMP_CD
    , WD_STDT
    , WD_EDDT
    , SWT_TRC_SEND_YN
    , WD_SHIP_STATE
    , WD_REASON_CD
    , WD_STATE_DT
    , WD_BF_SEND_YN
    , REG_NO
    , C.DateCreated AS REG_DT
    , REG_NO
    , C.DateLastModified AS UPD_DT
    , ORD_NO
    , C.ExceptionNo
FROM (
         SELECT 'G680_40'              AS CHG_GB
              , CASE
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '교환') = '오배송' THEN 'G689_12'
                    WHEN (SELECT MAX(ExceptionReasonCd)
                          FROM old_ord_orderexceptionitem X
                          WHERE X.ExceptionNo = B.ExceptionNo
                            AND X.ExceptionTypeCd = '교환') = '상품불량' THEN 'G689_11'
                    ELSE 'G689_90' END AS CHG_REASON
              , NULL                   AS WD_GB
              , (SELECT RECIP_NM FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_NM
              , (SELECT RECIP_PHNNO FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_PHNNO
              , (SELECT RECIP_TELNO FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_TELNO
              , (SELECT ORD_EMAIL FROM tb_order X WHERE X.ORD_NO = A.ORD_NO)                   AS CHGER_EMAIL
              , (SELECT RECIP_ZIPCODE FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_ZIPCODE
              , (SELECT RECIP_BASE_ADDR FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_BASE_ADDR
              , (SELECT RECIP_DTL_ADDR FROM tb_delivery_addr X WHERE X.DELV_ADDR_SQ = A.DELV_ADDR_SQ)                   AS CHGER_DTL_ADDR
              , NULL                   AS CHGER_RTN_MEMO
              , 0                      AS ADD_PAY_COST
              , 0                      AS ADD_PAY_AMT
              , NULL                   AS WD_INVOICE_NO
              , 'N'                    AS WD_INVOICE_SEND_YN
              , NULL                   AS SHIP_COMP_CD
              , NULL                   AS WD_STDT
              , NULL                   AS WD_EDDT
              , 'N'                    AS SWT_TRC_SEND_YN
              , NULL                   AS WD_SHIP_STATE
              , NULL                   AS WD_REASON_CD
              , NULL                   AS WD_STATE_DT
              , 'N'                    AS WD_BF_SEND_YN
              , A.REG_NO
              , A.REG_DT
              , A.UPD_NO
              , A.UPD_DT
              , A.ORD_NO
              , B.ExceptionNo
         FROM tb_order_detail A,
              old_ord_orderexceptionitem B
         WHERE A.ORD_DTL_NO = B.OrderItemNo
           AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
           AND B.ExceptionTypeCd = '교환'
           AND B.StatusCd IS NOT NULL
     ) AA, old_ord_orderexception C
WHERE AA.ExceptionNo = C.ExceptionNo
-- AND C.ExceptionNo = 1548177
; --

-- 환불대기밖에 없음...
INSERT INTO tb_order_change_detail
SELECT DISTINCT -- old_ord_orderexceptionitem 데이터중복인게 있네
    ORD_CHG_SQ
    , ORD_DTL_NO
    , B.Qty
    , 'G685_40' AS CHG_STAT
    , NULL AS CHG_ORD_DTL_NO
    , NULL AS WH_MEMO
    , NULL AS COMPLETE_DT
    , 'N' AS DEL_YN
    , A.REG_NO
    , A.REG_DT
    , A.UPD_NO
    , A.UPD_DT
FROM tb_order_change A, old_ord_orderexceptionitem B, tb_order_detail C
WHERE A.ORD_CHG_SQ = B.ExceptionNo
    AND A.OLD_ORD_NO = C.ORD_NO
    AND B.OrderItemNo = C.ORD_DTL_NO
    AND A.CHG_GB = 'G680_40'
    AND B.ExceptionTypeCd = '교환'
    -- AND (C.ORD_QTY - C.CNCL_RTN_QTY) = B.Qty
    AND ORD_DTL_STAT NOT IN('G013_00', 'G013_10', 'G013_98', 'G013_99')
    AND B.StatusCd IS NOT NULL
    AND C.ORD_NO > 1000
; --

SELECT * FROM tb_common_code WHERE CD_GB = 'G685';

SELECT
  ORD_NO
  , 'E' AS ORD_EXCH_GB
  , ORD_DTL_STAT       VARCHAR(20)         NOT NULL DEFAULT 'G013_00' COMMENT '주문상세상태(공통코드G013)', -- 주문상세상태(공통코드G013)
  ORG_ORD_DTL_NO     INT UNSIGNED        NULL     COMMENT '원주문상세번호(주문상세). 교환 시에만 사용', -- 원주문상세번호
  SUPPLY_COMP_CD     VARCHAR(20)         NOT NULL COMMENT '공급업체코드', -- 공급업체코드
  GOODS_CD           VARCHAR(20)         NOT NULL COMMENT '상품코드', -- 상품코드
  DEAL_GOODS_CD      VARCHAR(20)         NULL     COMMENT '딜상품코드', -- 딜상품코드
  FORMAL_GB          VARCHAR(20)         NULL     DEFAULT 'G009_10' COMMENT '정상이월구분(공통코드G009)', -- 정상이월구분(공통코드G009)
  GOODS_TYPE         VARCHAR(20)         NOT NULL COMMENT '상품타입(공통코드G056)', -- 상품타입(공통코드G056)
  LIST_PRICE         INT                 NOT NULL COMMENT '정상가(최초판매가)', -- 정상가(최초판매가)
  CURR_PRICE         INT                 NOT NULL COMMENT '현재판매가', -- 현재판매가
  DC_RATE            FLOAT(5,2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '할인율', -- 할인율
  OPT_ADD_PRICE      INT                 NOT NULL DEFAULT 0 COMMENT '옵션추가가격. 주문상세단품 옵션 추가가격의 합', -- 옵션추가가격
  ORD_QTY            SMALLINT UNSIGNED   NOT NULL DEFAULT 0 COMMENT '주문수량', -- 주문수량
  ORD_AMT            INT                 NOT NULL DEFAULT 0 COMMENT '주문금액(=(현재판매가 + 옵션추가가격) * 주문수량)', -- 주문금액
  CNCL_RTN_QTY       SMALLINT UNSIGNED   NOT NULL DEFAULT 0 COMMENT '취소반품수량', -- 취소반품수량
  CNCL_RTN_AMT       INT                 NOT NULL DEFAULT 0 COMMENT '취소반품금액', -- 취소반품금액
  CPN1_CPN_SQ        INT UNSIGNED        NULL     COMMENT '1차쿠폰(즉시할인쿠폰)일련번호(고객보유쿠폰일련번호)', -- 1차쿠폰(즉시할인쿠폰)일련번호
  CPN1_DC_AMT        INT                 NOT NULL DEFAULT 0 COMMENT '1차쿠폰(즉시할인쿠폰)할인금액', -- 1차쿠폰(즉시할인쿠폰)할인금액
  TMTB1_SQ           INT UNSIGNED        NULL     COMMENT '다다익선1일련번호(수량)', -- 다다익선1일련번호(수량)
  TMTB1_DC_AMT       INT                 NOT NULL DEFAULT 0 COMMENT '다다익선1할인금액(수량)', -- 다다익선1할인금액(수량)
  TMTB2_SQ           INT UNSIGNED        NULL     COMMENT '다다익선2일련번호(금액)', -- 다다익선2일련번호(금액)
  TMTB2_DC_AMT       INT UNSIGNED        NOT NULL DEFAULT 0 COMMENT '다다익선2할인금액(금액)', -- 다다익선2할인금액(금액)
  GOODS_CPN_SQ       INT UNSIGNED        NULL     COMMENT '상품쿠폰일련번호(고객보유쿠폰일련번호)', -- 상품쿠폰일련번호
  GOODS_CPN_DC_AMT   INT                 NOT NULL DEFAULT 0 COMMENT '상품쿠폰할인금액', -- 상품쿠폰할인금액
  CART_CPN_SQ        INT UNSIGNED        NULL     COMMENT '장바구니쿠폰일련번호(고객보유쿠폰일련번호)', -- 장바구니쿠폰일련번호
  CART_CPN_DC_AMT    INT                 NOT NULL DEFAULT 0 COMMENT '장바구니쿠폰할인금액', -- 장바구니쿠폰할인금액
  BURDEN_RATE        FLOAT(5,2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '쿠폰분담율', -- 쿠폰분담율
  PNT_DC_AMT         INT                 NOT NULL DEFAULT 0 COMMENT '포인트할인금액', -- 포인트할인금액
  PRE_PNT_DC_AMT     INT                 NOT NULL DEFAULT 0 COMMENT '선포인트할인금액', -- 선포인트할인금액
  SAVE_PNT_AMT       INT                 NOT NULL DEFAULT 0 COMMENT '적립포인트금액', -- 적립포인트금액
  REAL_ORD_AMT       INT                 NOT NULL COMMENT '실주문금액(주문금액 - 취소반품금액 - 1차쿠폰 - 다다익선1 - 다다익선2 - 상품쿠폰 - 장바구니쿠폰 - 포인트할인금액 - 선포인트할인금액)', -- 실주문금액
  GFCD_USE_AMT       INT                 NOT NULL COMMENT '상품권사용금액', -- 상품권사용금액
  VENDOR_ID          VARCHAR(20)         NULL     COMMENT '외부몰벤더ID(공통코드G003)', -- 외부몰벤더ID(공통코드G003)
  EXTMALL_ID         VARCHAR(20)         NULL     COMMENT '외부몰ID(외부몰)', -- 외부몰ID(외부몰)
  AGENT_ORDER_ID     VARCHAR(30)         NULL     COMMENT '에이전트주문번호. 몰구분이 "G011_20:외부몰"일 때 사용', -- 에이전트주문번호
  EXTMALL_ORDER_ID   VARCHAR(50)         NULL     COMMENT '외부몰주문번호(=쇼핑몰주문번호). 몰구분이 "G011_20:외부몰"일 때 사용', -- 외부몰주문번호
  CHANGEABLE_YN       CHAR(1)             NOT NULL DEFAULT 'Y' COMMENT '교환가능여부(Y:교환가능)', -- 교환가능여부(Y:교환가능)
  CHANGE_FEE_FREE_YN CHAR(1)             NOT NULL DEFAULT 'N' COMMENT '교환배송비무료여부(Y:교환배송비무료)', -- 교환배송비무료여부(Y:교환배송비무료)
  RETURNABLE_YN      CHAR(1)             NOT NULL DEFAULT 'Y' COMMENT '반품가능여부(Y:반품가능)', -- 반품가능여부(Y:반품가능)
  RETURN_FEE_FREE_YN CHAR(1)             NOT NULL DEFAULT 'N' COMMENT '반품배송비무료여부(Y:반품배송비무료)', -- 반품배송비무료여부(Y:반품배송비무료)
  SOLDOUT_YN         CHAR(1)             NOT NULL DEFAULT 'N' COMMENT '결품여부(Y:결품)', -- 결품여부(Y:결품)
  SOLDOUT_MEMO       VARCHAR(500)        NULL     COMMENT '결품메모', -- 결품메모
  SOLDOUT_REG_NO     INT UNSIGNED        NULL     COMMENT '결품등록자번호', -- 결품등록자번호
  SOLDOUT_REG_DT     TIMESTAMP           NULL     COMMENT '결품등록일시', -- 결품등록일시
  DELV_ADDR_SQ       INT UNSIGNED        NOT NULL COMMENT '배송지일련번호', -- 배송지일련번호
  DELV_FEE_CD        VARCHAR(20)         NOT NULL COMMENT '배송비정책코드', -- 배송비정책코드
  SHOT_DELV_YN       CHAR(1)             NOT NULL DEFAULT 'N' COMMENT '총알배송여부', -- 총알배송여부
  GIFT_PACK_YN       CHAR(1)             NOT NULL DEFAULT 'N' COMMENT '선물포장여부', -- 선물포장여부
  GIFT_ADDR_INP_YN   CHAR(1)             NOT NULL DEFAULT 'N' COMMENT '선물주소입력여부', -- 선물주소입력여부
  MAKE_GOODS_YN      CHAR(1)             NOT NULL DEFAULT 'N' COMMENT '주문제작상품확인여부: 주문제작상품 주문 시 확인', -- 주문제작상품여부
  ENTRY_NO           VARCHAR(20)         NULL     COMMENT '통관번호', -- 통관번호
  DELV_LOC_CD        VARCHAR(20)         NULL     COMMENT '출고처코드', -- 출고처코드
  DELV_ASSIGN_DT     TIMESTAMP           NULL     COMMENT '출고지정일시', -- 출고지정일시
  DELV_ASSIGN_STAT   CHAR(1)             NOT NULL DEFAULT 'P' COMMENT '출고지정상태(P:대기,Y:수락,N:거부). 입점은 DEFAULT ''Y''', -- 출고지정상태(P:대기,Y:수락,N:거부)
  DSTRBT_NOTE        VARCHAR(1000)       NULL     COMMENT '물류비고(물류담당자에게 공지)', -- 물류비고(물류담당자에게 공지)
  DELV_STDT          TIMESTAMP           NULL     COMMENT '배송시작일시', -- 배송시작일시
  DELV_EDDT          TIMESTAMP           NULL     COMMENT '배송완료일시', -- 배송완료일시
  SHIP_COMP_CD       VARCHAR(20)         NULL     COMMENT '배송업체코드', -- 배송업체코드
  INVOICE_NO         VARCHAR(30)         NULL     COMMENT '송장번호', -- 송장번호
  INVOICE_SEND_YN    CHAR(1)             NOT NULL DEFAULT 'N' COMMENT '송장전송여부(Y:전송완료, N:전송미완료, X:전송할필요없음)', -- 송장전송여부
  SELL_STORE_CD      VARCHAR(20)         NULL     COMMENT '판매매장코드', -- 판매매장코드
  SELL_FEE_RATE      FLOAT(5,2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '판매수수료율', -- 판매수수료율
  AF_LINK_CD         VARCHAR(20)         NULL     COMMENT '제휴링크코드', -- 제휴링크코드
  ITHR_CD            VARCHAR(20)         NULL     COMMENT '유입경로(공통코드G027)', -- 유입경로(공통코드G027)
  CONTENTS_LOC       VARCHAR(20)         NULL     COMMENT '컨텐츠위치(공통코드G028)', -- 컨텐츠위치(공통코드G028)
  PLAN_DTL_SQ        INT UNSIGNED        NULL     COMMENT '기획전상세번호', -- 기획전상세번호
  SOCIAL_SQ          INT UNSIGNED        NULL     COMMENT '소셜일련번호', -- 소셜일련번호
  NPAY_ORD_DTL_NO    INT UNSIGNED        NULL     COMMENT '네이버페이_상품주문번호',  -- 네이버페이 상품주문번호(주문상세번호)
  REG_NO             INT UNSIGNED        NOT NULL COMMENT '등록자번호', -- 등록자번호
  REG_DT             TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록일시', -- 등록일시
  UPD_NO             INT UNSIGNED        NOT NULL COMMENT '수정자번호', -- 수정자번호
  UPD_DT
FROM tb_order_detail A, tb_order_change_detail B
WHERE A.ORD_DTL_NO = B.ORD_DTL_NO
    AND EXISTS(
        SELECT 1
        FROM tb_order_change X
        WHERE X.ORD_CHG_SQ = B.ORD_CHG_SQ
            AND X.CHG_GB = 'G680_40'
    )
;




SELECT * FROM tb_refund WHERE PAY_SQ = 0;

SELECT * FROM tb_common_code WHERE CD_GB = 'G018';

CHANGE_DETAIL

처리완료 - G685_60 (반품완료)

회수지시 - G685_30 (회수요청)
환불대기 - G685_33 (반품진행중)
회수완료 - G685_33 (반품진행중)
환불지시 - G685_33 (반품진행중)
접수 - G685_50 (반품접수)

SELECT * FROM old_ord_orderexception WHERE ExceptionNo = 1549015;
SELECT * FROM old_ord_orderexceptionitem WHERE ExceptionNo = 1549015;
SELECT DISTINCT StatusCd FROM old_ord_orderexception WHERE ExceptionTypeCd = '반품';
SELECT DISTINCT StatusCd FROM old_ord_orderexception WHERE ExceptionTypeCd = '취소';
SELECT * FROM old_ord_deliveryorder WHERE DeliveryOrderNo = 13150505;
SELECT * FROM old_ord_refund WHERE ExceptionNo = 1867057;
SELECT * FROM old_ord_orderexceptionitem WHERE ExceptionTypeCd = '반품' AND StatusCd IS NULL ORDER BY ExceptionItemNo DESC;
SELECT * FROM old_ord_orderexceptionitem WHERE ExceptionTypeCd = '취소' AND StatusCd = '접수';
SELECT * FROM old_ord_orderexception WHERE ExceptionTypeCd = '반품' AND StatusCd IS NULL;
SELECT * FROM old_ord_order WHERE OrderGUID = '36A73125-7C41-47B5-9EDD-787F31459A3E';
SELECT * FROM old_ord_orderexception WHERE ExceptionNo IN (
    SELECT ExceptionNo FROM old_ord_orderexceptionitem WHERE ExceptionTypeCd = '반품' AND StatusCd IS NULL
    )
AND StatusCd = '접수'
;

회수지시
접수




SELECT * FROM old_ord_refund WHERE ExceptionNo = 1920476;
SELECT * FROM tb_delivery_fee WHERE DELV_FEE < 0;
SELECT * FROM tb_order_detail WHERE ORD_NO = 19429594;
SELECT * FROM tb_payment WHERE ORD_NO = 19429594;
SELECT * FROM tb_order_change;

SELECT B.*
FROM tb_order_change A, old_ord_refund B
WHERE A.OLD_EXEPTION_NO = B.ExceptionNo
    AND CustomerChargeDeliveryCost > 0
;
SELECT * FROM tb_common_code WHERE CD_GB = 'G320';
SELECT * FROM tb_common_code WHERE CD_GB = 'G680';
SELECT * FROM tb_common_code WHERE CD_GB = 'G688';
SELECT * FROM tb_common_code WHERE CD_GB = 'G685';
SELECT * FROM tb_common_code WHERE CD_GB = 'G018';



-- 환불계좌 암호화 배치
SELECT * FROM tb_refund;
SELECT COUNT(1) FROM tb_refund
WHERE RA_NO IS NOT NULL
  AND RA_NO != ''
  AND ENC_UPD_YN = 'N'
;