style24.doc/산출물/4.구축/마이그레이션/쿼리/01.공급업체.sql

-- 공급업체벤더
TRUNCATE TABLE tb_supply_vendor;

INSERT INTO tb_supply_vendor
SELECT CASE WHEN LENGTH(RN) = 1 THEN CONCAT('SV000', RN)
            WHEN LENGTH(RN) = 2 THEN CONCAT('SV00', RN)
            WHEN LENGTH(RN) = 3 THEN CONCAT('SV0', RN)
            ELSE CONCAT( 'SV' , RN) END AS SUPPLY_VENDOR_CD
    , A.VendorName AS SUPPLY_VENDOR_NM
    , IF(A.VendorTypeCd = '법인', 'C', 'P') AS BIZ_GB
    , A.BizNumber AS BIZ_NO
    , A.IndustryType AS BIZ_KIND
    , A.BusinessCategory AS BIZ_TYPE
    , A.Representative AS OWNER_NM
    , REPLACE(A.OfficePostalCode, '-', '') AS BIZ_ZIPCODE
    , IF(A.OfficeAddressNew <> '', A.OfficeAddressNew, A.OfficeAddress) AS BIZ_BASE_ADDR
    , A.OfficeAddress2 AS BIZ_DTL_ADDR
    , A.TelNum AS MAIN_TELNO
    , A.Fax AS MAIN_FAXNO
    , A.HomepageUrl AS HOMEPAGE_URL
    , A.VendorNo AS VENDOR_NO
    , IF(A.StatusCd ='정상', 'Y', 'N') AS USE_YN
    , 2
    , A.DateCreated AS REG_DT
    , 2
    , NOW() AS UPD_DT
FROM (
         SELECT @ROWNUM := @ROWNUM + 1 AS RN, A.*
         FROM old_ven_vendor A,
              (SELECT @ROWNUM := 0) B
         WHERE VendorNo IN(3561, 2168, 50, 1401, 3285)
         ORDER BY VendorNo
     ) A
;

INSERT INTO tb_supply_vendor
SELECT CASE WHEN LENGTH(RN) = 1 THEN CONCAT('SV000', RN)
            WHEN LENGTH(RN) = 2 THEN CONCAT('SV00', RN)
            WHEN LENGTH(RN) = 3 THEN CONCAT('SV0', RN)
            ELSE CONCAT( 'SV' , RN) END AS SUPPLY_VENDOR_CD
    , A.VendorName AS SUPPLY_VENDOR_NM
    , IF(A.VendorTypeCd = '법인', 'C', 'P') AS BIZ_GB
    , A.BizNumber AS BIZ_NO
    , A.IndustryType AS BIZ_KIND
    , A.BusinessCategory AS BIZ_TYPE
    , A.Representative AS OWNER_NM
    , REPLACE(A.OfficePostalCode, '-', '') AS BIZ_ZIPCODE
    , IF(A.OfficeAddressNew <> '', A.OfficeAddressNew, A.OfficeAddress) AS BIZ_BASE_ADDR
    , A.OfficeAddress2 AS BIZ_DTL_ADDR
    , A.TelNum AS MAIN_TELNO
    , A.Fax AS MAIN_FAXNO
    , A.HomepageUrl AS HOMEPAGE_URL
    , A.VendorNo AS VENDOR_NO
    , IF(A.StatusCd ='정상', 'Y', 'N') AS USE_YN
    , 2
    , A.DateCreated AS REG_DT
    , 2
    , NOW() AS UPD_DT
FROM (
         SELECT @ROWNUM := @ROWNUM + 1 AS RN, A.*
         FROM old_ven_vendor A,
              (SELECT @ROWNUM := 5) B
         WHERE VendorNo NOT IN(3561, 2168, 50, 1401, 3285)
         ORDER BY VendorNo
     ) A
;

SELECT * FROM tb_supply_vendor;
COMMIT;

-- 공급업체
TRUNCATE TABLE tb_supply_company;

SELECT *
FROM old_ven_provider
WHERE VendorNo IN(3561, 2168, 50, 1401, 3285)
;

-- 자사 엠케이, 드림
INSERT INTO tb_supply_company VALUES ('1', '아이스타일이십사(주)', 'SV0001', 1, '김유미', '02-3270-7622', 'MD', '02-3270-7660', '', 'G065_10', 'Y', 'G010_30', 35, 'G075_12', null, null, null, null, 'N', null, null, 'Y', 2, NOW(), 2, NOW());
INSERT INTO tb_supply_company VALUES ('2', '한세실업주식회사', 'SV0002', 2, '신선미', '3270-7657', '황재필', '3270-7657', '', 'G065_12', 'Y', 'G010_30', 35, 'G075_12', null, null, null, null, 'N', null, null, 'Y', 2, NOW(), 2, NOW());
INSERT INTO tb_supply_company VALUES ('3', '한세드림', 'SV0003', 3, '김승환', '02-3215-0046', '강미나', '02-3215-0030', '', 'G065_12', 'Y', 'G010_30', 35, 'G075_12', null, null, null, null, 'N', null, null, 'Y', 2, NOW(), 2, NOW());
INSERT INTO tb_supply_company VALUES ('4', '(주)에프알제이', 'SV0004', 4, '김지원', '010-5023-2050', '김지원', '010-5023-2050', '', 'G065_11', 'Y', 'G010_30', 35, 'G075_12', null, null, null, null, 'N', null, null, 'Y', 2, NOW(), 2, NOW());
INSERT INTO tb_supply_company VALUES ('5', '(주) 엠케이트렌드', 'SV0005', 5, '서아람 차장', '010-3795-3871', '김민석 과장', '02-2142-5043', 'mskim2@hansaemk.com', 'G065_12', 'Y', 'G010_30', 24, 'G075_12', null, null, null, null, 'N', null, null, 'Y', 2, NOW(), 2, NOW());

SELECT MIN(ProviderNo), MAX(ProviderNo) FROM old_ven_provider;

INSERT INTO tb_supply_company
SELECT A.ProviderNo AS SUPPLY_COMP_CD
    , A.ProviderName
    , B.SUPPLY_VENDOR_CD
    , A.ProviderNo AS PROVIDER_NO
    , A.CSChargeName AS CS_CHARGE_NM
    , A.CSChargeTelNum AS CS_CHARGE_TELNO
    , A.SettleChargeName AS SETTLE_CHARGE_NM
    , A.SettleChargeTelNum AS SETTLE_CHARGE_TELNO
    , A.SettleChargeEmail AS SETTLE_CHARGE_EMAIL
    , (CASE WHEN A.DistributionCd = '자사' THEN 'G065_10' WHEN A.DistributionCd = '사입' THEN 'G065_11' WHEN A.DistributionCd = '위탁' THEN 'G065_12' ELSE 'G065_20' END) AS DISTRIBUTION_GB
    , (CASE WHEN A.DistributionCd IN ('자사', '사입', '위탁') THEN 'Y' ELSE 'N' END) AS SHOT_DELV_YN
    , 'G010_30' AS SUPPLY_STAT
    , A.MarginRate AS SELL_FEE_RATE
    , IF(A.SettleTypeCd = '월말정산', 'G075_12', 'G075_11') AS SETTLE_DAY
    , NULL AS BANK_CD
    , NULL AS ACCOUNT_NO
    , NULL AS DEPOSITOR_NM
    , NULL AS BILL_EMAIL
    , IF(A.IsBillTrans = 'True', 'Y', 'N') AS ECONTRACT_YN
    , A.AdminMemo AS REMARKS
    , NULL AS NOTE
    , IF(A.StatusCd ='정상', 'Y', 'N') AS USE_YN
    , 2
    , A.DateCreated AS REG_DT
    , 2
    , IFNULL(A.DateLastModified = NULL, A.DateCreated) AS UPD_DT
FROM old_ven_provider A, tb_supply_vendor B
WHERE A.VendorNo = B.VENDOR_NO
   AND A.DistributionCd NOT IN('자사','사입','위탁')
;

SELECT * FROM tb_supply_company WHERE SUPPLY_COMP_NM LIKE '%한세%';

COMMIT;

-- 벤더 추가
INSERT INTO TB_SUPPLY_VENDOR
SELECT CASE WHEN LENGTH(RN) = 1 THEN CONCAT('SV000', RN)
            WHEN LENGTH(RN) = 2 THEN CONCAT('SV00', RN)
            WHEN LENGTH(RN) = 3 THEN CONCAT('SV0', RN)
            ELSE CONCAT( 'SV' , RN) END AS SUPPLY_VENDOR_CD
    , A.VendorName AS SUPPLY_VENDOR_NM
    , IF(A.VendorTypeCd = '법인', 'C', 'P') AS BIZ_GB
    , A.BizNumber AS BIZ_NO
    , A.IndustryType AS BIZ_KIND
    , A.BusinessCategory AS BIZ_TYPE
    , A.Representative AS OWNER_NM
    , REPLACE(A.OfficePostalCode, '-', '') AS BIZ_ZIPCODE
    , IF(A.OfficeAddressNew <> '', A.OfficeAddressNew, A.OfficeAddress) AS BIZ_BASE_ADDR
    , A.OfficeAddress2 AS BIZ_DTL_ADDR
    , A.TelNum AS MAIN_TELNO
    , A.Fax AS MAIN_FAXNO
    , A.HomepageUrl AS HOMEPAGE_URL
    , A.VendorNo AS VENDOR_NO
    , IF(A.StatusCd ='정상', 'Y', 'N') AS USE_YN
    , 2
    , A.DateCreated AS REG_DT
    , 2
    , NOW() AS UPD_DT
FROM (
         SELECT @ROWNUM := @ROWNUM + 1 AS RN, A.*
         FROM old_ven_vendor A,
              (SELECT @ROWNUM := (SELECT MAX(SUBSTRING(SUPPLY_VENDOR_CD, 3)) FROM TB_SUPPLY_VENDOR)) B
         WHERE -- VendorNo NOT IN(3561, 2168, 50, 1401, 3285)
            NOT EXISTS(
                SELECT 1 FROM TB_SUPPLY_VENDOR X WHERE X.VENDOR_NO = A.VENDORNO
                )
         ORDER BY VendorNo
     ) A
;

-- 공급업체추가
INSERT INTO TB_SUPPLY_COMPANY
SELECT A.ProviderNo AS SUPPLY_COMP_CD
    , A.ProviderName
    , B.SUPPLY_VENDOR_CD
    , A.ProviderNo AS PROVIDER_NO
    , A.CSChargeName AS CS_CHARGE_NM
    , A.CSChargeTelNum AS CS_CHARGE_TELNO
    , A.SettleChargeName AS SETTLE_CHARGE_NM
    , A.SettleChargeTelNum AS SETTLE_CHARGE_TELNO
    , A.SettleChargeEmail AS SETTLE_CHARGE_EMAIL
    , (CASE WHEN A.DistributionCd = '자사' THEN 'G065_10' WHEN A.DistributionCd = '사입' THEN 'G065_11' WHEN A.DistributionCd = '위탁' THEN 'G065_12' ELSE 'G065_20' END) AS DISTRIBUTION_GB
    , (CASE WHEN A.DistributionCd IN ('자사', '사입', '위탁') THEN 'Y' ELSE 'N' END) AS SHOT_DELV_YN
    , 'G010_30' AS SUPPLY_STAT
    , A.MarginRate AS SELL_FEE_RATE
    , IF(A.SettleTypeCd = '월말정산', 'G075_12', 'G075_11') AS SETTLE_DAY
    , NULL AS BANK_CD
    , NULL AS ACCOUNT_NO
    , NULL AS DEPOSITOR_NM
    , NULL AS BILL_EMAIL
    , IF(A.IsBillTrans = 'True', 'Y', 'N') AS ECONTRACT_YN
    , A.AdminMemo AS REMARKS
    , NULL AS NOTE
    , IF(A.StatusCd ='정상', 'Y', 'N') AS USE_YN
    , 2
    , A.DateCreated AS REG_DT
    , 2
    , IFNULL(A.DateLastModified = NULL, A.DateCreated) AS UPD_DT
FROM old_ven_provider A, tb_supply_vendor B
WHERE A.VendorNo = B.VENDOR_NO
   AND A.DistributionCd NOT IN('자사','사입','위탁')
    AND NOT EXISTS(
        SELECT 1
        FROM TB_SUPPLY_COMPANY X
        WHERE X.SUPPLY_COMP_CD = A.PROVIDERNO
    )
;




-- 배송업체
TRUNCATE TABLE TB_SHIP_COMPANY;
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1008', '사가와택배', 'http://www.sagawa-korea.co.kr/tracking/normal/index.htm?awblno=', null, '01', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1009', '우체국택배', 'http://service.epost.go.kr/trace.RetrieveRegiPrclDeliv.postal?sid1=', '01', '02', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1010', '삼성택배', 'http://www.cjgls.co.kr/kor/service/service02_02.asp?slipno=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1012', '로젠택배', 'http://d2d.ilogen.com/d2d/delivery/invoice_search_popup.jsp?viewType=type2&invoiceNum=', '06', '04', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1013', 'CJ대한통운', 'http://nexs.cjgls.com/web/info.jsp?slipno=', '04', '05', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1014', '아주택배', 'http://www.loexe.co.kr/customer/cus_trace_02_apis.asp?searchMethod=I&invc_no=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1015', '옐로우캡', 'http://www.yellowcap.co.kr/custom/inquiry_result.asp?INVOICE_NO=', null, '12', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1017', '한진택배', 'https://www.hanjin.co.kr/Delivery_html/inquiry/result_waybill.jsp?wbl_num=', '05', '08', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1018', 'CJ택배', 'http://nexs.cjgls.com/web/info.jsp?slipno=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1020', 'KGB택배', 'http://www.kgbls.co.kr/tracing.asp?number=', null, '10', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1021', '훼미리택배', 'https://www.dongbuexpress.co.kr/delivery/delivery_search_view.jsp?item_no=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1022', '세덱스', 'http://btob.sedex.co.kr/work/app/tm/tmtr01/tmtr01_s4.jsp?IC_INV_NO=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1023', 'KT로지스', 'http://www.loexe.co.kr/customer/cus_trace_02_apis.asp?searchMethod=I&invc_no=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1024', 'CJ HTH', 'http://nexs.cjgls.com/web/info.jsp?slipno=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1025', '하나로택배', 'http://www.hanarologis.com/branch/chase/listbody.html?a_gb=center&a_cd=4&a_item=0&fr_slipno=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1027', '정안씨엘에스', 'http://www.jacls.co.kr/', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1028', '삼성HTH택배', 'http://www.cjgls.co.kr/kor/service/service02_02.asp?slipno=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1029', 'CJ 대한통운', 'http://www.doortodoor.co.kr/jsp/cmn/Tracking_YES24.jsp?pTdNo=', '04', '05', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1032', '동부익스프레스 택배', 'https://www.dongbuexpress.co.kr/delivery/delivery_search_view.jsp?item_no=', null, '11', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1033', '네덱스', 'http://www.hanjinexpress.hanjin.net/customer/plsql/hddcw18_ms.tracking?w_num=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1034', '이노지스', 'http://www.innogis.net/Tracking/Tracking_view.asp?invoice=', null, '03', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1036', 'GTX택배', 'http://www.ddlogis.com/tracking/default_cs.asp?awblno=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1037', '스카이', 'http://www.ddlogis.com/tracking/default_cs.asp?awblno=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1038', '대신택배', 'http://www.ds3211.co.kr/freight/internalFreightSearch.ht?billno=', '22', null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1039', '일양로지스', 'http://www.ilyanglogis.com/functionality/tracking_result.asp?hawb_no=', '11', null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1048', '고려', 'http://www.klogis.kr/03_business/01_tracking_detail_bcno.asp?bcno=', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1049', '천일택배', 'http://www.chunil.co.kr/kor/taekbae/HTrace.jsp', '17', null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1050', '더플라워샵', 'http://image.yes24.com/momo/TopCate357/MidCate010/35699031.jpg', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1051', '합동택배', 'http://www.hdexp.co.kr/parcel/order_status.asp', '32', null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1052', '경동택배', 'http://www.kdexp.com/sub3_shipping.asp', '23', '09', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1053', '페덱스', 'https://www.fedex.com/apps/fedextrack/?action=track&cntry_code=kr', '21', null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1054', '건영택배', 'http://www.kunyoung.com/goods/goods_01.php', '18', null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1055', '편의점택배', 'http://www.cvsnet.co.kr/postbox/m_delivery/local/local.jsp?m_type=gnb3', '46', null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1056', '직배송', '직배송', null, '99', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1057', '기타', '기타', null, '90', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1058', 'KG로지스', 'http://www.kglogis.co.kr/delivery/popup_tracking.jsp?item_no=', null, '15', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1059', '착불', '.', null, null, 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
INSERT INTO tb_ship_company (SHIP_COMP_CD, SHIP_COMP_NM, TRACKING_URL, SWT_SHIP_COMP_CD, WMS_SHIP_COMP_CD, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT) VALUES ('D1060', '롯데택배', 'http://www.lotteglogis.com/open/tracking?invno=', '08', '06', 'Y', 2, '2021-03-08 06:49:11', 2, '2021-03-08 06:49:11');
COMMIT;

-- 출고처
TRUNCATE TABLE tb_delivery_loc;

-- 자사창고
INSERT INTO tb_delivery_loc VALUES ('1', 'ST0001', '파주물류센터', 'G024_10', 100, null, 100, 0, '10857', '경기 파주시 탄현면 방촌로 1172-21', '아이스타일24 물류센터', '10857', '경기 파주시 탄현면 방촌로 1172-21', '아이스타일24 물류센터', null, '파주물류센터', null, 'D1029', 'Y', 1, '2020-10-16 13:09:57', 1, '2020-10-17 22:17:38', null);
INSERT INTO tb_delivery_loc VALUES ('2', 'ST0001', '파주물류센터', 'G024_10', 100, null, 100, 0, '10857', '경기 파주시 탄현면 방촌로 1172-21', '아이스타일24 물류센터', '10857', '경기 파주시 탄현면 방촌로 1172-21', '아이스타일24 물류센터', null, '파주물류센터', null, 'D1029', 'Y', 1, '2020-10-17 22:17:26', 1, '2020-10-17 22:17:26', null);
INSERT INTO tb_delivery_loc VALUES ('3', 'ST0001', '파주물류센터', 'G024_10', 100, null, 100, 0, '10857', '경기 파주시 탄현면 방촌로 1172-21', '아이스타일24 물류센터', '10857', '경기 파주시 탄현면 방촌로 1172-21', '아이스타일24 물류센터', null, '파주물류센터', null, 'D1029', 'Y', 1, '2020-10-17 22:17:26', 1, '2020-10-17 22:17:26', null);
INSERT INTO tb_delivery_loc VALUES ('4', 'ST0001', '파주물류센터', 'G024_10', 100, null, 100, 0, '10857', '경기 파주시 탄현면 방촌로 1172-21', '아이스타일24 물류센터', '10857', '경기 파주시 탄현면 방촌로 1172-21', '아이스타일24 물류센터', null, '파주물류센터', null, 'D1029', 'Y', 1, '2020-10-17 22:17:26', 1, '2020-10-17 22:17:26', null);
INSERT INTO tb_delivery_loc VALUES ('5', 'ST0001', '파주물류센터', 'G024_10', 100, null, 100, 0, '10857', '경기 파주시 탄현면 방촌로 1172-21', '아이스타일24 물류센터', '10857', '경기 파주시 탄현면 방촌로 1172-21', '아이스타일24 물류센터', null, '파주물류센터', null, 'D1029', 'Y', 1, '2020-10-17 22:17:26', 1, '2020-10-17 22:17:26', null);

SELECT * FROM tb_delivery_loc;
COMMIT;

-- 입점업체
TRUNCATE TABLE TMP_PROVIDER_DELIVERY;

INSERT INTO TMP_PROVIDER_DELIVERY
SELECT DISTINCT ProviderNo, DeliveryUnitNo
FROM old_cat_product
;

SELECT * FROM TMP_PROVIDER_DELIVERY;
COMMIT;

INSERT INTO tb_delivery_loc
SELECT
    SUPPLY_COMP_CD
    , CASE WHEN LENGTH(RN) = 1 THEN CONCAT('DL000', RN)
            WHEN LENGTH(RN) = 2 THEN CONCAT('DL00', RN)
            ELSE CONCAT('DL0', RN) END AS DELV_LOC_CD
    , DELV_LOC_NM
    , DELV_LOC_CLSF
    , DELV_ASSIGN_ORD
    , DELV_ASSIGN_GRADE
    , STOCK_APPL_RATE
    , DELV_FEE_RATE
    , DELV_LOC_ZIPCODE
    , DELV_LOC_BASE_ADDR
    , DELV_LOC_DTL_ADDR
    , RTN_LOC_ZIPCODE
    , RTN_LOC_BASE_ADDR
    , RTN_LOC_DTL_ADDR
    , RTN_LOC_TELNO
    , RTN_LOC_NM
    , INVOICE_PRINT_TYPE
    , SHIP_COMP_CD
    , USE_YN
    , REG_NO
    , REG_DT
    , UPD_NO
    , UPD_DT
    , DeliveryUnitNo
FROM (
         SELECT SUPPLY_COMP_CD
              , DELV_LOC_NM
              , DELV_LOC_CLSF
              , DELV_ASSIGN_ORD
              , DELV_ASSIGN_GRADE
              , STOCK_APPL_RATE
              , DELV_FEE_RATE
              , DELV_LOC_ZIPCODE
              , DELV_LOC_BASE_ADDR
              , DELV_LOC_DTL_ADDR
              , RTN_LOC_ZIPCODE
              , RTN_LOC_BASE_ADDR
              , RTN_LOC_DTL_ADDR
              , RTN_LOC_TELNO
              , RTN_LOC_NM
              , INVOICE_PRINT_TYPE
              , SHIP_COMP_CD
              , USE_YN
              , REG_NO
              , REG_DT
              , UPD_NO
              , UPD_DT
              , DeliveryUnitNo
              , @ROWNUM := @ROWNUM + 1 AS RN
         FROM (
                  SELECT RANK() OVER (PARTITION BY A.ProviderNo ORDER BY A.DeliveryUnitNo DESC)          AS RNK
                       , A.DeliveryUnitNo
                       , 'DELV_LOC_CD'                                                                   AS DELV_LOC_CD
                       , C.ProviderName                                                                  AS DELV_LOC_NM
                       , A.ProviderNo                                                                    AS SUPPLY_COMP_CD
                       , 'G024_30'                                                                       AS DELV_LOC_CLSF
                       , NULL                                                                            AS DELV_ASSIGN_ORD
                       , NULL                                                                            AS DELV_ASSIGN_GRADE
                       , NULL                                                                            AS STOCK_APPL_RATE
                       , NULL                                                                            AS DELV_FEE_RATE
                       , REPLACE(B.WarehousePostalCode, '-', '')                                         AS DELV_LOC_ZIPCODE
                       , IF(B.WarehouseAddressNew <> '', B.WarehouseAddressNew,
                            B.WarehouseAddress)                                                          AS DELV_LOC_BASE_ADDR
                       , B.WarehouseAddress2                                                             AS DELV_LOC_DTL_ADDR
                       , REPLACE(C.ReturnPostalCode, '-', '')                                            AS RTN_LOC_ZIPCODE
                       , IF(C.ReturnAddrNew <> '', C.ReturnAddrNew, C.ReturnAddr1)                       AS RTN_LOC_BASE_ADDR
                       , C.ReturnAddr1                                                                   AS RTN_LOC_DTL_ADDR
                       , C.ChargeTelNum                                                                  AS RTN_LOC_TELNO
                       , C.ProviderName                                                                  AS RTN_LOC_NM
                       , NULL                                                                            AS INVOICE_PRINT_TYPE
                       , CASE WHEN LENGTH(LogisticsNo) = 1 THEN CONCAT('D100', LogisticsNo)
                            WHEN LENGTH(LogisticsNo) = 2 THEN CONCAT('D10', LogisticsNo)
                            ELSE CONCAT('D1', LogisticsNo) END AS SHIP_COMP_CD
                       , 'Y'                                                                             AS USE_YN
                       , 2                                                                               AS REG_NO
                       , B.DateCreated                                                                   AS REG_DT
                       , 2                                                                               AS UPD_NO
                       , IFNULL(B.DateLastModified,B.DateCreated)                                                            AS UPD_DT
                  FROM TMP_PROVIDER_DELIVERY A,
                       old_ven_deliveryunit B,
                       old_ven_provider C
                  WHERE A.DeliveryUnitNo = B.DeliveryUnitNo
                    AND A.ProviderNo = C.ProviderNo
                    AND B.DeliveryTypeCd = 'SCM'
                    -- AND B.StatusCd = '정상'
                    -- AND B.DefaultDeliveryCost > 0
              ) A,
              (SELECT @ROWNUM := 5) B
         -- WHERE RNK = 1
         ORDER BY A.SUPPLY_COMP_CD
     ) A
;

-- CJ대한통운 통합
UPDATE tb_delivery_loc A
SET SHIP_COMP_CD = 'D1029'
WHERE SHIP_COMP_CD IN ('D1013', 'D1018')
;

SELECT * FROM tb_delivery_loc;

COMMIT;

-- 배송비정책
TRUNCATE TABLE tb_delv_fee_policy;

INSERT INTO tb_delv_fee_policy VALUES ('WMS', '1', 'G078_10', 40000, 2500, 2500, 'Y', 2, NOW(), 2, NOW(), 27);
INSERT INTO tb_delv_fee_policy VALUES ('WMS_FREE', '1', 'G078_20', 0, 0, 2500, 'Y', 2, NOW(), 2, NOW(), NULL);
INSERT INTO tb_delv_fee_policy VALUES ('WMS', '2', 'G078_10', 40000, 2500, 2500, 'Y', 2, NOW(), 2, NOW(), 27);
INSERT INTO tb_delv_fee_policy VALUES ('WMS_FREE', '2', 'G078_20', 0, 0, 2500, 'Y', 2, NOW(), 2, NOW(), 27);
INSERT INTO tb_delv_fee_policy VALUES ('WMS', '3', 'G078_10', 40000, 2500, 2500, 'Y', 2, NOW(), 2, NOW(), 27);
INSERT INTO tb_delv_fee_policy VALUES ('WMS_FREE', '3', 'G078_20', 0, 0, 2500, 'Y', 2, NOW(), 2, NOW(), NULL);
INSERT INTO tb_delv_fee_policy VALUES ('WMS', '4', 'G078_10', 40000, 2500, 2500, 'Y', 2, NOW(), 2, NOW(), 27);
INSERT INTO tb_delv_fee_policy VALUES ('WMS_FREE', '4', 'G078_20', 0, 0, 2500, 'Y', 2, NOW(), 2, NOW(), NULL);
INSERT INTO tb_delv_fee_policy VALUES ('WMS', '5', 'G078_10', 40000, 2500, 2500, 'Y', 2, NOW(), 2, NOW(), 27);
INSERT INTO tb_delv_fee_policy VALUES ('WMS_FREE', '5', 'G078_20', 0, 0, 2500, 'Y', 2, NOW(), 2, NOW(), NULL);


-- 입점
INSERT INTO tb_delv_fee_policy
SELECT CASE WHEN RNK > 10 THEN CONCAT(SUPPLY_COMP_CD, '_', RNK)
            ELSE CONCAT(SUPPLY_COMP_CD, '_0', RNK) END AS DELV_FEE_CD
     , SUPPLY_COMP_CD
     , DELV_FEE_CRITE
     , MIN_ORD_AMT
     , DELV_FEE
     , RTN_DELV_FEE
     , USE_YN
     , REG_NO
     , REG_DT
     , UPD_NO
     , UPD_DT
     , DELIVERY_UNIT_NO
FROM (
         SELECT A.ProviderNo                                                    AS SUPPLY_COMP_CD
              , CASE
                    WHEN DefaultDeliveryCost < 1 THEN 'G078_20' -- 무료
                    WHEN DefaultDeliveryCost > 0 AND FreeDeliveryOrderAmount > 0 THEN 'G078_10' -- 조건부 무료
                    ELSE 'G078_30' -- 유료
             END                                                                AS DELV_FEE_CRITE
              , FreeDeliveryOrderAmount                                         AS MIN_ORD_AMT
              , DefaultDeliveryCost                                             AS DELV_FEE
              , DefaultDeliveryCost                                             AS RTN_DELV_FEE
              , IF(B.StatusCd = '정상', 'Y', 'N')                                 AS USE_YN
              , 2                                                               AS REG_NO
              , B.DateCreated                                                   AS REG_DT
              , 2                                                               AS UPD_NO
              , IF(B.DateLastModified IS NULL, B.DateCreated, B.DateLastModified)                AS UPD_DT
              , RANK() OVER (PARTITION BY A.ProviderNo ORDER BY A.DeliveryUnitNo) AS RNK
              , A.DeliveryUnitNo AS DELIVERY_UNIT_NO
         FROM TMP_PROVIDER_DELIVERY A,
              old_ven_deliveryunit B,
              old_ven_provider C
         WHERE A.DeliveryUnitNo = B.DeliveryUnitNo
           AND A.ProviderNo = C.ProviderNo
           AND B.DeliveryTypeCd = 'SCM'
           -- AND B.StatusCd = '정상'
     ) A
ORDER BY A.SUPPLY_COMP_CD, RNK
;

SELECT * FROM tb_delv_fee_policy;

COMMIT;