style24.doc/산출물/4.구축/마이그레이션/쿼리/06.그외정보.sql

-- 상품평/주문 맵핑
UPDATE TB_REVIEW A, TB_ORDER_DETAIL B, OLD_CAT_PRODUCTREVIEWRELORDER C
SET A.ORD_NO = B.ORD_NO
    , A.ORD_DTL_NO = B.ORD_DTL_NO
WHERE A.GOODS_CD = B.GOODS_CD
    AND A.REVIEW_SQ = C.REVIEWNO
    AND B.ORD_NO = C.ORDERNO
;


INSERT INTO TB_COUPON_NOTICE_SENDED
SELECT 'C'
     , CPN_ID
     , 9999
     , NOW()
FROM   TB_COUPON CP
WHERE  CP.CPN_STAT = 'G232_11'
AND    CP.DC_CD_GB = 'G233_00'
AND    CP.DOWN_STDT < NOW()
AND    CP.CPN_TYPE <> 'G230_10'
UNION ALL
SELECT 'W'
     , CPN_ID
     , 9999
     , NOW()
FROM   TB_COUPON CP
WHERE  CP.CPN_STAT = 'G232_11'
AND    CP.DC_CD_GB = 'G233_00'
AND    CP.DOWN_STDT < NOW()
AND    CP.CPN_TYPE <> 'G230_10';


TRUNCATE TABLE SHOPLINKER_STOCK;

-- 샵링커(재고배치후)
INSERT INTO SHOPLINKER_STOCK
(GOODS_CD
, OPT_CD
, STOCK_QTY
, SEND_YN
, SEND_DT
, REG_DT
, UPD_DT
)
SELECT B.GOODS_CD
    , B.OPT_CD
    , A.QTY
    -- , CASE WHEN A.APISUCCESS = 'True' THEN 'Y' ELSE 'N' END AS SEND_YN
	, 'N' AS SEND_YN
    , NOW()
    , NOW()
    , NOW()
FROM old_log_shoplinker A, TB_OPTION B
WHERE A.MASTERMODELNOORSKUCODE = B.OPT_CD
;



-- 휴면주문정보 생성
INSERT INTO tb_order_dormant
SELECT ORD_NO
   , A.CUST_NO
   , ORD_NM
   , ORD_PHNNO
   , ORD_TELNO
   , ORD_EMAIL
   , 2       AS REG_NO
   , NOW()          AS REG_DT
FROM   TB_ORDER A, TB_DORMANT_CUST B
WHERE  A.CUST_NO = B.CUST_NO
;

-- 주문정보 휴면처리
UPDATE TB_ORDER A, TB_DORMANT_CUST B
SET    ORD_NM = '휴면'
   , ORD_PHNNO = NULL
   , ORD_TELNO = NULL
   , ORD_EMAIL = NULL
WHERE  A.CUST_NO = B.CUST_NO
;