| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349 |
- <!-- 소셜상품 가격정보 원복(before) 업데이트 -->
- <update id="updateGoodsBeforePriceInfo">
- /* TsbSocial.updateGoodsBeforePriceInfo */
- UPDATE TB_GOODS A
- INNER JOIN TB_SOCIAL_GOODS B ON A.GOODS_CD = B.GOODS_CD
- INNER JOIN TB_SOCIAL C ON B.SOCIAL_SQ = C.SOCIAL_SQ
- SET A.GOODS_CD = B.GOODS_CD ,
- A.CURR_PRICE = B.CURR_BPRICE,
- A.DC_RATE = B.DC_BRATE,
- A.UPD_DT = NOW(),
- A.UPD_NO = #{updNo}
- WHERE C.USE_YN = 'Y'
- AND C.APPLY_GB = 'A'
- AND B.DEL_YN = 'N'
- AND C.SOCIAL_EDDT <![CDATA[<=]]> DATE_FORMAT(DATE_ADD(CONCAT(CURRENT_DATE(),' 23:59:59'), INTERVAL 0 DAY), '%Y%m%d%H%i%S')
- </update>
-
- <!-- 원복(before) 상품 이력 생성 -->
- <insert id="createBeforeGoodsHst" parameterType="Social">
- /* TsbSocial.createBeforeGoodsHst */
- INSERT INTO TB_GOODS_HST
- (
- GOODS_CD
- , BRAND_CD
- , ITEMKIND_CD
- , GOODS_NM
- , GOODS_TNM
- , GOODS_SNM
- , GOODS_SNM1
- , MAIN_COLOR_CD
- , STYLE_YEAR
- , SEASON_CD
- , SEX_GB
- , GOODS_NUM
- , GOODS_TYPE
- , TAG_PRICE
- , LIST_PRICE
- , CURR_PRICE
- , CURR_BPRICE
- , PRICE_UPD_DT
- , COST_PRICE
- , DC_RATE
- , GOODS_STAT
- , SELL_STDT
- , SELL_EDDT
- , SELF_MALL_YN
- , GOODS_GB
- , FOREIGN_BUY_YN
- , PARALLEL_IMPORT_YN
- , ORDER_MADE_YN
- , DISTRIBUTION_GB
- , SELF_GOODS_YN
- , SUPPLY_COMP_CD
- , SUPPLY_GOODS_CD
- , AGE_GRP_CD
- , DELV_FEE_CD
- , DELV_FEE
- , MIN_ORD_AMT
- , PNT_PRATE
- , PNT_MRATE
- , SELL_FEE_RATE
- , FORMAL_GB
- , CHANGEABLE_YN
- , RETURNABLE_YN
- , CHANGE_FEE_FREE_YN
- , RETURN_FEE_FREE_YN
- , PRE_PPNT_USABLE_YN
- , PRE_MPNT_USABLE_YN
- , MIN_ORD_QTY
- , MAX_ORD_QTY
- , DAY_MAX_ORD_QTY
- , GIFT_PACK_YN
- , FRST_CFRM_DT
- , ORIGIN_CD
- , MAKE_YMD
- , TAX_GB
- , ERP_PRICE_LINK_YN
- , ERP_STOCK_LINK_YN
- , NEW_CUST_ORD_YN
- , ADULT_YN
- , TOBE_FORM_YN
- , REG_NO
- , REG_DT
- , UPD_NO
- , UPD_DT
- )
- SELECT GOODS_CD
- , BRAND_CD
- , ITEMKIND_CD
- , GOODS_NM
- , GOODS_TNM
- , GOODS_SNM
- , GOODS_SNM1
- , MAIN_COLOR_CD
- , STYLE_YEAR
- , SEASON_CD
- , SEX_GB
- , GOODS_NUM
- , GOODS_TYPE
- , TAG_PRICE
- , LIST_PRICE
- , CURR_PRICE
- , CURR_BPRICE
- , PRICE_UPD_DT
- , COST_PRICE
- , DC_RATE
- , GOODS_STAT
- , SELL_STDT
- , SELL_EDDT
- , SELF_MALL_YN
- , GOODS_GB
- , FOREIGN_BUY_YN
- , PARALLEL_IMPORT_YN
- , ORDER_MADE_YN
- , DISTRIBUTION_GB
- , SELF_GOODS_YN
- , SUPPLY_COMP_CD
- , SUPPLY_GOODS_CD
- , AGE_GRP_CD
- , DELV_FEE_CD
- , DELV_FEE
- , MIN_ORD_AMT
- , PNT_PRATE
- , PNT_MRATE
- , SELL_FEE_RATE
- , FORMAL_GB
- , CHANGEABLE_YN
- , RETURNABLE_YN
- , CHANGE_FEE_FREE_YN
- , RETURN_FEE_FREE_YN
- , PRE_PPNT_USABLE_YN
- , PRE_MPNT_USABLE_YN
- , MIN_ORD_QTY
- , MAX_ORD_QTY
- , DAY_MAX_ORD_QTY
- , GIFT_PACK_YN
- , FRST_CFRM_DT
- , ORIGIN_CD
- , MAKE_YMD
- , TAX_GB
- , ERP_PRICE_LINK_YN
- , ERP_STOCK_LINK_YN
- , NEW_CUST_ORD_YN
- , ADULT_YN
- , TOBE_FORM_YN
- , #{regNo}
- , now()
- , #{updNo}
- , now()
- FROM TB_GOODS
- WHERE 1 = 1
- AND GOODS_CD IN (
- SELECT B.GOODS_CD
- FROM TB_SOCIAL A
- , TB_SOCIAL_GOODS B
- WHERE A.SOCIAL_SQ = B.SOCIAL_SQ
- AND A.USE_YN = 'Y'
- AND A.APPLY_GB = 'A'
- AND B.DEL_YN = 'N'
- AND A.SOCIAL_EDDT <![CDATA[<=]]> DATE_FORMAT(DATE_ADD(CONCAT(CURRENT_DATE(),' 23:59:59'), INTERVAL 0 DAY), '%Y%m%d%H%i%S')
- )
- </insert>
- <!-- 소셜 적용 여부 '종료'(적용->종료) 업데이트 -->
- <update id="updateSocialApplyGbtoF">
- /* TsbSocial.updateSocialApplyGbtoF */
- UPDATE TB_SOCIAL
- SET APPLY_GB = 'F' <!-- 종료상태 -->
- ,UPD_DT = NOW()
- ,UPD_NO = #{updNo}
- WHERE APPLY_GB = 'A' <!-- 적용상태 -->
- AND USE_YN = 'Y' <!-- 사용여부 -->
- AND SOCIAL_EDDT <![CDATA[<=]]> DATE_FORMAT(DATE_ADD(CONCAT(CURRENT_DATE(),' 23:59:59'), INTERVAL 0 DAY), '%Y%m%d%H%i%S')
- </update>
-
- <!-- 상품 가격정보 적용(after) -->
- <update id="updateGoodsAfterPriceInfo">
- /* TsbSocial.updateGoodsAfterPriceInfo */
- UPDATE TB_GOODS A
- INNER JOIN TB_SOCIAL_GOODS B ON A.GOODS_CD = B.GOODS_CD
- INNER JOIN TB_SOCIAL C ON B.SOCIAL_SQ = C.SOCIAL_SQ
- SET A.GOODS_CD = B.GOODS_CD ,
- A.CURR_PRICE = B.CURR_APRICE,
- A.DC_RATE = B.DC_ARATE,
- A.UPD_DT = NOW() ,
- A.UPD_NO = #{updNo}
- WHERE C.USE_YN = 'Y'
- AND C.APPLY_GB = 'P'
- AND B.DEL_YN = 'N'
- AND SOCIAL_STDT <![CDATA[>=]]> DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 0 DAY), '%Y%m%d%H%i%S')
- </update>
-
- <!-- 적용(after) 상품 이력 생성 -->
- <insert id="createAfterGoodsHst">
- /* TsbSocial.createAfterGoodsHst */
- INSERT INTO TB_GOODS_HST
- (
- GOODS_CD
- , BRAND_CD
- , ITEMKIND_CD
- , GOODS_NM
- , GOODS_TNM
- , GOODS_SNM
- , GOODS_SNM1
- , MAIN_COLOR_CD
- , STYLE_YEAR
- , SEASON_CD
- , SEX_GB
- , GOODS_NUM
- , GOODS_TYPE
- , TAG_PRICE
- , LIST_PRICE
- , CURR_PRICE
- , CURR_BPRICE
- , PRICE_UPD_DT
- , COST_PRICE
- , DC_RATE
- , GOODS_STAT
- , SELL_STDT
- , SELL_EDDT
- , SELF_MALL_YN
- , GOODS_GB
- , FOREIGN_BUY_YN
- , PARALLEL_IMPORT_YN
- , ORDER_MADE_YN
- , DISTRIBUTION_GB
- , SELF_GOODS_YN
- , SUPPLY_COMP_CD
- , SUPPLY_GOODS_CD
- , AGE_GRP_CD
- , DELV_FEE_CD
- , DELV_FEE
- , MIN_ORD_AMT
- , PNT_PRATE
- , PNT_MRATE
- , SELL_FEE_RATE
- , FORMAL_GB
- , CHANGEABLE_YN
- , RETURNABLE_YN
- , CHANGE_FEE_FREE_YN
- , RETURN_FEE_FREE_YN
- , PRE_PPNT_USABLE_YN
- , PRE_MPNT_USABLE_YN
- , MIN_ORD_QTY
- , MAX_ORD_QTY
- , DAY_MAX_ORD_QTY
- , GIFT_PACK_YN
- , FRST_CFRM_DT
- , ORIGIN_CD
- , MAKE_YMD
- , TAX_GB
- , ERP_PRICE_LINK_YN
- , ERP_STOCK_LINK_YN
- , NEW_CUST_ORD_YN
- , ADULT_YN
- , TOBE_FORM_YN
- , REG_NO
- , REG_DT
- , UPD_NO
- , UPD_DT
-
- )
- SELECT GOODS_CD
- , BRAND_CD
- , ITEMKIND_CD
- , GOODS_NM
- , GOODS_TNM
- , GOODS_SNM
- , GOODS_SNM1
- , MAIN_COLOR_CD
- , STYLE_YEAR
- , SEASON_CD
- , SEX_GB
- , GOODS_NUM
- , GOODS_TYPE
- , TAG_PRICE
- , LIST_PRICE
- , CURR_PRICE
- , CURR_BPRICE
- , PRICE_UPD_DT
- , COST_PRICE
- , DC_RATE
- , GOODS_STAT
- , SELL_STDT
- , SELL_EDDT
- , SELF_MALL_YN
- , GOODS_GB
- , FOREIGN_BUY_YN
- , PARALLEL_IMPORT_YN
- , ORDER_MADE_YN
- , DISTRIBUTION_GB
- , SELF_GOODS_YN
- , SUPPLY_COMP_CD
- , SUPPLY_GOODS_CD
- , AGE_GRP_CD
- , DELV_FEE_CD
- , DELV_FEE
- , MIN_ORD_AMT
- , PNT_PRATE
- , PNT_MRATE
- , SELL_FEE_RATE
- , FORMAL_GB
- , CHANGEABLE_YN
- , RETURNABLE_YN
- , CHANGE_FEE_FREE_YN
- , RETURN_FEE_FREE_YN
- , PRE_PPNT_USABLE_YN
- , PRE_MPNT_USABLE_YN
- , MIN_ORD_QTY
- , MAX_ORD_QTY
- , DAY_MAX_ORD_QTY
- , GIFT_PACK_YN
- , FRST_CFRM_DT
- , ORIGIN_CD
- , MAKE_YMD
- , TAX_GB
- , ERP_PRICE_LINK_YN
- , ERP_STOCK_LINK_YN
- , NEW_CUST_ORD_YN
- , ADULT_YN
- , TOBE_FORM_YN
- , #{regNo}
- , now()
- , #{updNo}
- , now()
- FROM TB_GOODS
- WHERE 1 = 1
- AND GOODS_CD IN (
- SELECT B.GOODS_CD
- FROM TB_SOCIAL A
- , TB_SOCIAL_GOODS B
- WHERE A.SOCIAL_SQ = B.SOCIAL_SQ
- AND A.USE_YN = 'Y'
- AND A.APPLY_GB = 'P'
- AND B.DEL_YN = 'N'
- AND A.SOCIAL_STDT <![CDATA[>=]]> DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 0 DAY), '%Y%m%d%H%i%S')
- )
- </insert>
-
- <!-- 소셜 적용 여부 '적용'(대기->적용) 업데이트 -->
- <update id="updateSocialApplyGbtoA">
- /* TsbSocial.updateSocialApplyGbtoA */
- UPDATE TB_SOCIAL
- SET APPLY_GB = 'A',
- UPD_DT = NOW(),
- UPD_NO = #{updNo}
- WHERE APPLY_GB = 'P'
- AND USE_YN = 'Y'
- AND SOCIAL_STDT <![CDATA[>=]]> DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 0 DAY), '%Y%m%d%H%i%S')
- </update>
|
