-- 1.1. WMS재고이력 데이터 삭제 (2개월전 데이터) DELETE FROM TB_WMS_STOCK_HST WHERE JOBDATE < DATE_ADD(DATE_FORMAT(NOW(), '%Y%m%d'), INTERVAL -2 MONTH) ; -- 1.2. WMS재고이력 생성 INSERT INTO TB_WMS_STOCK_HST ( JOBDATE , PRODUCT_NO , SKU_CODE , LOC_QTY , PICKING_QTY , EOUT_PICKING_QTY ) SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i%S') AS JOBDATE , PRODUCT_NO , SKU_CODE , LOC_QTY , PICKING_QTY , EOUT_PICKING_QTY FROM TB_WMS_STOCK ; -- 2.1. WMS 재고 truncate TRUNCATE TABLE TB_WMS_STOCK; -- 2.2. WMS 재고 수신 (MSSQL 접속. 배치로 처리해야 함) SELECT PRODUCT_NO , SKU_CODE , LOC_QTY , PICKING_QTY , EOUT_PICKING_QTY FROM ( SELECT PRODUCT_NO , SKU_CODE , SUM(LOC_QTY) AS LOC_QTY , SUM(PICKING_QTY) AS PICKING_QTY , SUM(EOUT_PICKING_QTY) AS EOUT_PICKING_QTY FROM iSTYLE24_WMS.DBO.VW_STOCK_BY_LOCCODE GROUP BY PRODUCT_NO, SKU_CODE ) Z WHERE 1 = 1 AND LOC_QTY - PICKING_QTY - EOUT_PICKING_QTY > 0 ; -- 3.1 매장재고 삭제 TRUNCATE TABLE TB_SHOP_STOCK; -- 3.2. 출고처별 판매비율을 적용한 매장재고 생성 INSERT INTO TB_SHOP_STOCK ( SUPPLY_COMP_CD , CD_SHOP , CD_STYLE , CD_COLOR , CD_SIZE , QT_STOCK , OPT_CD , OPT_CD1 , STORE_STOCK_QTY , UPD_DT ) SELECT A.SUPPLY_COMP_CD , A.CD_SHOP , A.CD_STYLE , A.CD_COLOR , A.CD_SIZE , A.QT_STOCK , E.OPT_CD , E.OPT_CD1 , ROUND(GREATEST(A.QT_STOCK, 0) * (IFNULL(D.STOCK_APPL_RATE, 0)/100)) AS STORE_STOCK_QTY , NOW() FROM (SELECT S.SUPPLY_COMP_CD , S.CD_SHOP , S.CD_STYLE , S.CD_COLOR , S.CD_SIZE , S.QT_STOCK , M.COLOR_CD AS OPT_CD1 FROM TB_SHOP_GOODS_STOCK S INNER JOIN TB_WMS_COLOR_MAPPING M ON S.SUPPLY_COMP_CD = M.SUPPLY_COMP_CD AND S.CD_COLOR = M.WMS_COLOR_CD AND M.USE_YN = 'Y') A INNER JOIN TB_OPTION E USE INDEX(PRIMARY) ON A.CD_STYLE = E.GOODS_CD AND A.OPT_CD1 = E.OPT_CD1 AND A.CD_SIZE = E.OPT_CD2 INNER JOIN TB_GOODS B ON A.CD_STYLE = B.GOODS_CD AND B.SELF_GOODS_YN = 'Y' AND B.ERP_STOCK_LINK_YN = 'Y' INNER JOIN TB_STOCK_SYNC_BASE C ON B.BRAND_CD = C.BRAND_CD AND C.STOCK_SYNC_YN = 'Y' AND A.CD_SHOP = C.DELV_LOC_CD INNER JOIN TB_DELIVERY_LOC D ON A.SUPPLY_COMP_CD = D.SUPPLY_COMP_CD AND A.CD_SHOP = D.DELV_LOC_CD AND D.USE_YN = 'Y' ; -- 3.3. 매장재고이력 삭제 (2개월전 데이터) DELETE FROM TB_SHOP_STOCK_HST WHERE JOBDATE #{jobdate} ; INSERT INTO TB_SHOP_STOCK_HST( JOBDATE , SUPPLY_COMP_CD , CD_SHOP , CD_STYLE , CD_COLOR , CD_SIZE , QT_STOCK , OPT_CD , OPT_CD1 , STORE_STOCK_QTY ) SELECT #{jobdate} SUPPLY_COMP_CD , CD_SHOP , CD_STYLE , CD_COLOR , CD_SIZE , QT_STOCK , OPT_CD , OPT_CD1 , STORE_STOCK_QTY FROM TB_SHOP_STOCK ; DELETE FROM TB_OPTION_SYNC_TMP; INSERT INTO TB_OPTION_SYNC_TMP ( GOODS_CD , OPT_CD , OPT_CD1 , OPT_CD2 , SKU_MODEL_NO , PRODUCT_NO , PRODUCT_CODE , BASE_STOCK_QTY , CURR_STOCK_QTY , STORE_STOCK_QTY , STORE_MAX_QTY , ADD_PRICE , SOLDOUT_YN , DISP_ORD , DISP_YN , ERP_STOCK_LINK_YN , REG_NO , REG_DT , UPD_NO , UPD_DT ) SELECT GOODS_CD , OPT_CD , OPT_CD1 , OPT_CD2 , SKU_MODEL_NO , PRODUCT_NO , PRODUCT_CODE , 0 AS BASE_STOCK_QTY , CURR_STOCK_QTY , 0 AS STORE_STOCK_QTY , 0 AS STORE_MAX_QTY , 0 AS ADD_PRICE , 'N' AS SOLDOUT_YN , DISP_ORD , 'Y' AS DISP_YN , ERP_STOCK_LINK_YN , 99999 AS REG_NO , NOW() AS REG_DT , 99999 AS UPD_NO , NOW() AS UPD_DT FROM ( SELECT B.GOODS_CD , A.SKU_CODE AS OPT_CD , E.OPT_CD1 , E.OPT_CD2 , E.SKU_MODEL_NO , E.PRODUCT_NO , E.PRODUCT_CODE , ROUND((GREATEST(A.LOC_QTY, 0) - GREATEST(A.PICKING_QTY, 0) - GREATEST(A.EOUT_PICKING_QTY, 0)) * (IFNULL(D.STOCK_APPL_RATE, 0)/100)) AS CURR_STOCK_QTY , E.STORE_STOCK_QTY , E.DISP_ORD , B.ERP_STOCK_LINK_YN FROM TB_WMS_STOCK A INNER JOIN TB_OPTION E ON A.PRODUCT_NO = E.PRODUCT_NO AND A.SKU_CODE = E.OPT_CD INNER JOIN TB_GOODS B ON E.GOODS_CD = B.GOODS_CD AND B.SELF_GOODS_YN = 'Y' AND B.ERP_STOCK_LINK_YN = 'Y' INNER JOIN TB_STOCK_SYNC_BASE C ON B.BRAND_CD = C.BRAND_CD AND C.STOCK_SYNC_YN = 'Y' AND C.DELV_LOC_CD = 'ST0001' -- WMS 매장코드 INNER JOIN TB_DELIVERY_LOC D ON B.SUPPLY_COMP_CD = D.SUPPLY_COMP_CD AND C.DELV_LOC_CD = D.DELV_LOC_CD AND D.USE_YN = 'Y' AND D.DELV_LOC_CD = 'ST0001' -- WMS 매장코드 ) Z ; INSERT INTO TB_OPTION_SYNC_TMP ( GOODS_CD , OPT_CD , OPT_CD1 , OPT_CD2 , SKU_MODEL_NO , PRODUCT_NO , PRODUCT_CODE , BASE_STOCK_QTY , CURR_STOCK_QTY , STORE_STOCK_QTY , STORE_MAX_QTY , ADD_PRICE , SOLDOUT_YN , DISP_ORD , DISP_YN , ERP_STOCK_LINK_YN , REG_NO , REG_DT , UPD_NO , UPD_DT ) SELECT GOODS_CD , OPT_CD , OPT_CD1 , OPT_CD2 , SKU_MODEL_NO , PRODUCT_NO , PRODUCT_CODE , 0 AS BASE_STOCK_QTY , 0 AS CURR_STOCK_QTY , STORE_STOCK_QTY , 0 AS STORE_MAX_QTY , 0 AS ADD_PRICE , 'N' AS SOLDOUT_YN , DISP_ORD , 'Y' AS DISP_YN , ERP_STOCK_LINK_YN , 99999 AS REG_NO , NOW() AS REG_DT , 99999 AS UPD_NO , NOW() AS UPD_DT FROM ( SELECT E.GOODS_CD , E.OPT_CD , E.OPT_CD1 , E.OPT_CD2 , E.SKU_MODEL_NO , E.PRODUCT_NO , E.PRODUCT_CODE , E.CURR_STOCK_QTY , SUM(A.STORE_STOCK_QTY) AS STORE_STOCK_QTY , B.ERP_STOCK_LINK_YN , E.DISP_ORD FROM TB_SHOP_STOCK A INNER JOIN TB_OPTION E ON A.CD_STYLE = E.GOODS_CD AND A.OPT_CD = E.OPT_CD INNER JOIN TB_GOODS B ON E.GOODS_CD = B.GOODS_CD AND B.SELF_GOODS_YN = 'Y' AND B.ERP_STOCK_LINK_YN = 'Y' GROUP BY GOODS_CD , OPT_CD , OPT_CD1 , OPT_CD2 , SKU_MODEL_NO , PRODUCT_NO , PRODUCT_CODE , CURR_STOCK_QTY , DISP_ORD , ERP_STOCK_LINK_YN ) Z ON DUPLICATE KEY UPDATE STORE_STOCK_QTY = Z.STORE_STOCK_QTY ; -- 입점상품 재고 동기화 -- 입점상품 SELL_QTY 목록 /* TsbGoods.getGoodsSelfNoStockList */ SELECT A.GOODS_CD , A.OPT_CD , SUM( (CASE A.SELL_GB WHEN '10' THEN 1 WHEN '20' THEN 1 ELSE -1 END) * A.SELL_QTY ) AS SALE_STOCK_QTY FROM TB_SELL_QTY A , TB_GOODS B WHERE A.GOODS_CD = B.GOODS_CD AND B.SELF_GOODS_YN = 'N' -- 입점상품 GROUP BY A.GOODS_CD, A.OPT_CD ; -- LOOP 돌면서 -- 이력생성 goodsDao.createStockHst(option); -- 재고 변경 goodsDao.updateStock(option); -- TB_OPTION = > TB_OPTION_SYNC 적용 TRUNCATE TABLE TB_OPTION_SYNC; -- TB_OPTION_SYNC.CURR_STOCK_QTY, STORE_STOCK_QTY, STORE_MAX_QTY 초기화 INSERT INTO TB_OPTION_SYNC ( GOODS_CD , OPT_CD , OPT_CD1 , OPT_CD2 , SKU_MODEL_NO , PRODUCT_NO , PRODUCT_CODE , BASE_STOCK_QTY , CURR_STOCK_QTY , STORE_STOCK_QTY , STORE_MAX_QTY , ADD_PRICE , SOLDOUT_YN , DISP_ORD , DISP_YN , ERP_STOCK_LINK_YN , REG_NO , REG_DT , UPD_NO , UPD_DT ) SELECT B.GOODS_CD , B.OPT_CD , B.OPT_CD1 , B.OPT_CD2 , B.SKU_MODEL_NO , B.PRODUCT_NO , B.PRODUCT_CODE , B.BASE_STOCK_QTY , 0 AS CURR_STOCK_QTY , 0 AS STORE_STOCK_QTY , 0 AS STORE_MAX_QTY , B.ADD_PRICE , B.SOLDOUT_YN , B.DISP_ORD , B.DISP_YN , A.ERP_STOCK_LINK_YN , B.REG_NO , B.REG_DT , B.UPD_NO , B.UPD_DT FROM TB_GOODS A, TB_OPTION B WHERE A.ERP_STOCK_LINK_YN = 'Y' AND A.SELF_GOODS_YN ='Y' AND A.GOODS_CD = B.GOODS_CD ; -- TB_OPTION_SYNC_TMP => TB_OPTION_SYNC 변경적용 /* TsbGoods.saveGoodsOptionSyncSync */ UPDATE TB_OPTION_SYNC A, (SELECT GOODS_CD , OPT_CD , CURR_STOCK_QTY , STORE_STOCK_QTY , ERP_STOCK_LINK_YN FROM TB_OPTION_SYNC_TMP WHERE ERP_STOCK_LINK_YN = 'Y' ) B SET A.CURR_STOCK_QTY = B.CURR_STOCK_QTY , A.STORE_STOCK_QTY = B.STORE_STOCK_QTY , A.UPD_NO = 99999 , A.UPD_DT = NOW() WHERE A.GOODS_CD = B.GOODS_CD AND A.OPT_CD = B.OPT_CD B.CURR_STOCK_QTY OR A.STORE_STOCK_QTY <> B.STORE_STOCK_QTY ) ]]> ; -- WMS, 매장별 최대 주문수량 적용 == > 작업합시다. -- TB_SHOP_STOCK과 TB_OPTION_SYNC.CURR_STOCK_QTY 비교하여 STORE_MAX_QTY에 적용 /* TsbGoods.updateGoodsOptionSyncSync */ UPDATE TB_OPTION_SYNC A, (SELECT GOODS_CD , OPT_CD , STORE_MAX_QTY FROM ( SELECT GOODS_CD , OPT_CD , GREATEST(CURR_STOCK_QTY, QT_STOCK) AS STORE_MAX_QTY FROM ( SELECT OS.GOODS_CD , OS.OPT_CD , GREATEST(OS.CURR_STOCK_QTY, 0) AS CURR_STOCK_QTY , GREATEST(IFNULL(SS.QT_STOCK, 0), 0) AS QT_STOCK FROM TB_OPTION_SYNC OS LEFT OUTER JOIN TB_SHOP_STOCK SS ON OS.GOODS_CD = SS.CD_STYLE AND OS.OPT_CD = SS.OPT_CD WHERE OS.ERP_STOCK_LINK_YN = 'Y' ) Z ) Y WHERE STORE_MAX_QTY > 0 ) B SET A.STORE_MAX_QTY = B.STORE_MAX_QTY , A.UPD_NO = 99999 , A.UPD_DT = NOW() WHERE A.GOODS_CD = B.GOODS_CD AND A.OPT_CD = B.OPT_CD B.STORE_MAX_QTY ]]> ; -- TB_OPTION_SYNC => TB_OPTION 적용 UPDATE TB_OPTION A, (SELECT GOODS_CD , OPT_CD , CURR_STOCK_QTY , STORE_STOCK_QTY , STORE_MAX_QTY , ERP_STOCK_LINK_YN FROM TB_OPTION_SYNC WHERE ERP_STOCK_LINK_YN = 'Y' ) B SET A.CURR_STOCK_QTY = B.CURR_STOCK_QTY , A.STORE_STOCK_QTY = B.STORE_STOCK_QTY , A.STORE_MAX_QTY = B.STORE_MAX_QTY , A.UPD_NO = 99999 , A.UPD_DT = NOW() WHERE A.GOODS_CD = B.GOODS_CD AND A.OPT_CD = B.OPT_CD B.CURR_STOCK_QTY OR A.STORE_STOCK_QTY <> B.STORE_STOCK_QTY OR A.STORE_MAX_QTY <> B.STORE_MAX_QTY ) ]]> ; // 자사상품중 주문상세상태가 입금대기, 결제완료, 교환대기 건은 TB_SELL_QTY 생성 -- TB_SELL_QTY_TEMP 삭제 TRUNCATE TABLE TB_SELL_QTY_TMP; -- TB_SELL_QTY_TEMP 생성 INSERT INTO TB_SELL_QTY_TMP ( GOODS_CD , OPT_CD , SELL_GB , ORD_DTL_NO , ORD_CHG_SQ , AGENT_ORDER_ID , EXTMALL_ORDER_ID , SELL_QTY , REG_NO , REG_DT ) SELECT GOODS_CD , OPT_CD , SELL_GB , ORD_DTL_NO , ORD_CHG_SQ , AGENT_ORDER_ID , EXTMALL_ORDER_ID , SELL_QTY , REG_NO , REG_DT FROM TB_SELL_QTY ; -- 초기화 DELETE FROM TB_SELL_QTY; -- TB_SELL_QTY_TEMP => TB_SELL_QTY 생성 INSERT INTO TB_SELL_QTY ( GOODS_CD , OPT_CD , SELL_GB , ORD_DTL_NO , ORD_CHG_SQ , AGENT_ORDER_ID , EXTMALL_ORDER_ID , SELL_QTY , REG_NO , REG_DT ) SELECT GOODS_CD , OPT_CD , SELL_GB , ORD_DTL_NO , ORD_CHG_SQ , AGENT_ORDER_ID , EXTMALL_ORDER_ID , SELL_QTY , REG_NO , REG_DT FROM TB_SELL_QTY_TMP WHERE ORD_DTL_NO IN ( SELECT OD.ORD_DTL_NO FROM TB_ORDER_DETAIL OD , TB_ORDER_DETAIL_ITEM ODI , TB_GOODS G WHERE OD.ORD_DTL_NO = ODI.ORD_DTL_NO AND OD.ORD_DTL_STAT IN ('G013_10','G013_20','G013_25','G013_30','G013_35','G013_40') -- 입금대기, 결제완료, 교환대기, 상품준비중,출고처지정,배송준비중 AND OD.GOODS_CD = G.GOODS_CD AND G.SELF_GOODS_YN ='Y' ) ; -- TB_ORDER_DETAIL => TB_SELL_QTY 생성 INSERT INTO TB_SELL_QTY ( GOODS_CD , OPT_CD , SELL_GB , ORD_DTL_NO , AGENT_ORDER_ID , EXTMALL_ORDER_ID , SELL_QTY , REG_NO , REG_DT ) SELECT ITEM_CD , OPT_CD , SELL_GB , ORD_DTL_NO , AGENT_ORDER_ID , EXTMALL_ORDER_ID , SELL_QTY , 0 , NOW() FROM ( SELECT ODI.ITEM_CD , ODI.OPT_CD , '10' AS SELL_GB -- 주문건 , OD.ORD_DTL_NO , OD.AGENT_ORDER_ID , OD.EXTMALL_ORDER_ID , SUM((OD.ORD_QTY - OD.CNCL_RTN_QTY) * ODI.ITEM_QTY) AS SELL_QTY FROM TB_ORDER_DETAIL OD , TB_ORDER_DETAIL_ITEM ODI WHERE OD.ORD_DTL_NO = ODI.ORD_DTL_NO AND OD.ORD_DTL_STAT IN ('G013_10','G013_20','G013_25','G013_30','G013_35','G013_40') -- 입금대기, 결제완료, 교환대기, 상품준비중,출고처지정,배송준비중 AND EXISTS (SELECT 1 FROM TB_OPTION X WHERE X.GOODS_CD = ODI.ITEM_CD AND X.OPT_CD = ODI.OPT_CD) GROUP BY ODI.ITEM_CD, ODI.OPT_CD, OD.ORD_DTL_NO, OD.AGENT_ORDER_ID, OD.EXTMALL_ORDER_ID ) A WHERE NOT EXISTS (SELECT 1 FROM TB_SELL_QTY Y WHERE Y.GOODS_CD = A.ITEM_CD AND Y.OPT_CD = A.OPT_CD AND Y.SELL_GB = A.SELL_GB AND Y.ORD_DTL_NO = A.ORD_DTL_NO ) ;