##### 주문 ##### -- 배송정보 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' ;