Source: DMF/PuC/AIF/Stores_WH.sql
select * from store_add_ctrl where store = '291';
select * from xref_location where sap_id = '0446';
select * from xref_location where sap_id = '0738';
select * from store_add_ctrl where store = '0446';
select * from addr_ctrl where KEY_VALUE_1 IN '446';
select * from SAP_SLT_PROD_PV1.WRF1;
select * from store_cfa_ext_ctrl where group_id = 2001 and store in (259,195,147);
-- cfa 2001 - varchar2_3 --STR_CNT_MNGR -- code type: CTMG
select locnr, zzhausgrvklreg, (SELECT kx.description_long
FROM sap_slt_prod_pv1.zsta_kat k,
sap_slt_prod_pv1.zsta_ktx kx
WHERE k.mandt = wrf1.mandt
AND k.status = 2
AND k.funktcode <> 'SP'
AND k.kennung = '02035'
AND k.ident = LPAD(wrf1.zzhausgrvklreg, 7, '0')
AND kx.mandt = k.mandt
AND kx.status = k.status
AND kx.kennung = k.kennung
AND kx.ident = k.ident) AS STR_CNT_MNGR_DESC from SAP_SLT_PROD_PV1.WRF1 WRF1 where locnr in ('0000000259','0000000195','0000000147') ;
SELECT distinct kx.description_long, wrf1.zzhausgrvkl
FROM sap_slt_prod_pv1.zsta_kat k,
sap_slt_prod_pv1.zsta_ktx kx,
store_add_ctrl sc,
sap_slt_prod_pv1.wrf1 wrf1
WHERE LTRIM(wrf1.locnr,'0') = TO_CHAR(sc.store)
and k.mandt = wrf1.mandt
AND k.status = 2
AND k.funktcode <> 'SP'
AND k.kennung = '02030'
AND k.ident = LPAD(wrf1.zzhausgrvkl, 7, '0')
AND kx.mandt = k.mandt
AND kx.status = k.status
AND kx.kennung = k.kennung
AND kx.ident = k.ident
--AND LOCNR = '0000000003'
;
SELECT distinct kx.description_long, wrf1.zzhausgrvklreg
FROM sap_slt_prod_pv1.zsta_kat k,
sap_slt_prod_pv1.zsta_ktx kx,
store_add_ctrl sc,
sap_slt_prod_pv1.wrf1 wrf1
WHERE LTRIM(wrf1.locnr,'0') = TO_CHAR(sc.store)
AND k.status = 2
AND k.funktcode <> 'SP'
AND k.kennung = '02035'
AND k.ident = LPAD(wrf1.zzhausgrvklreg, 7, '0')
AND kx.mandt = k.mandt
AND kx.status = k.status
AND kx.kennung = k.kennung
AND kx.ident = k.ident
--AND LOCNR = '0000000003'
;
select *
from (
select sap_id, oracle_type, channel_id, oracle_id_by_channel,
RANK() OVER (
PARTITION BY sap_id
ORDER BY channel_id) AS r
from xref_location)
where r = 1;
insert into xref_location (select * from store_add_ctrl where store = '17';
--stores
select locnr, ZZ_MS_HAUSGRUPPE from SAP_SLT_PROD_PV1.WRF1 where locnr like '%0003';
select * from SAP_SLT_PROD_PV1.t001w WHERE VLFKZ = 'A' order by werks; -- stores
select * from SAP_SLT_PROD_PV1.t001w WHERE VLFKZ = 'B' order by werks; --wh
SELECT TO_NUMBER(wrf1.locnr) AS STORE,
t001w.name1 AS STORE_NAME,
wrf1.zzhauskuna AS STORE_NAME10,
SUBSTR(wrf1.zzhauskuna, 1, 3) AS STORE_NAME3,
'A' AS STORE_CLASS,
CASE WHEN wrf1.eroed != '00000000' THEN
TO_DATE(wrf1.eroed, 'YYYYMMDD')
ELSE NULL
END AS STORE_OPEN_DATE,
CASE WHEN wrf1.schld != '00000000' THEN
TO_DATE(wrf1.schld, 'YYYYMMDD')
ELSE NULL
END AS STORE_CLOSE_DATE,
wrf1.verfl AS SELLING_SQUARE_FT,
'' AS STORE_FORMAT,
wrf3.loclb AS DEFAULT_WH,
wrf1.posws AS CURRENCY_CODE,
'N' AS GIFT_WRAPPING_IND,
kna1.stceg AS TAX_ID,
SUBSTR(kna1.BBBNR || kna1.BBSNR || kna1.BUBKZ, 1, 9) AS DUNS_NUMBER,
SUBSTR(kna1.BBBNR || kna1.BBSNR || kna1.BUBKZ, -4) AS DUNS_LOC
FROM sap_slt_prod_pv1.wrf1 wrf1
JOIN sap_slt_prod_pv1.t001w t001w ON t001w.kunnr = wrf1.locnr
JOIN sap_slt_prod_pv1.wrf3 wrf3 ON wrf3.locnr = wrf1.locnr
JOIN sap_slt_prod_pv1.kna1 kna1 ON kna1.kunnr = t001w.kunnr
WHERE t001w.vlfkz = 'A'
and t001w.werks = '0017'
;
-- warehouses
WITH
SAP AS (
SELECT TO_NUMBER(t.werks) werks, t.vkorg
FROM sap_slt_prod_pv1.t001w t,
sap_slt_prod_pv1.wrf3 wrf3
WHERE t.vlfkz = 'A'
AND REGEXP_LIKE(t.werks, '^[0-9]')
AND wrf3.locnr = t.kunnr
)
SELECT werks, s.store
FROM STORE_ADD_CTRL s,
SAP
WHERE s.ctrl_status = 'C'
AND werks = s.store;
-----
SELECT * FROM wh_ctrl;
SELECT * FROM xref_wh;
select * from XREF_LOCATION WHERE ORACLE_ID_BY_CHANNEL IS NULL AND ORACLE_TYPE = 'W';
select * from XREF_LOCATION WHERE TO_NUMBER(SAP_ID) <> ORACLE_ID;
select * from XREF_LOCATION WHERE sap_type <> oracle_type;
select * from XREF_LOCATION WHERE sap_id = '0125';
---
INSERT INTO store_add_ctrl (
STORE,
STORE_NAME,
STORE_NAME10,
STORE_NAME3,
STORE_NAME_SECONDARY,
STORE_CLASS,
STORE_MGR_NAME,
STORE_OPEN_DATE,
STORE_CLOSE_DATE,
ACQUIRED_DATE,
REMODEL_DATE,
FAX_NUMBER,
PHONE_NUMBER,
EMAIL,
TOTAL_SQUARE_FT,
SELLING_SQUARE_FT,
LINEAR_DISTANCE,
VAT_REGION,
VAT_INCLUDE_IND,
STOCKHOLDING_IND,
CHANNEL_ID,
STORE_FORMAT,
MALL_NAME,
DISTRICT,
TRANSFER_ZONE,
DEFAULT_WH,
STOP_ORDER_DAYS,
START_ORDER_DAYS,
CURRENCY_CODE,
LANG,
PRICE_STORE,
TRAN_NO_GENERATED,
INTEGRATED_POS_IND,
DUNS_NUMBER,
DUNS_LOC,
SISTER_STORE,
TSF_ENTITY_ID,
ORG_UNIT_ID,
AUTO_RCV,
REMERCH_IND,
STORE_TYPE,
WF_CUSTOMER_ID,
TIMEZONE_NAME,
CUSTOMER_ORDER_LOC_IND,
CUSTOMER_ORDER_SHIP_IND,
GIFT_WRAPPING_IND,
TAX_ID,
ONLINE_STORE_IND,
CREATE_ID,
CREATE_DATETIME,
COPY_ACTIVITY_IND,
COPY_CLEARANCE_IND,
COPY_DLVRY_IND,
COPY_REPL_IND,
COST_LOCATION,
LIKE_STORE,
PROCESS_MODE,
PROCESS_STATUS,
RMS_ASYNC_ID,
CTRL_STATUS,
CTRL_DATETIME,
CTRL_ERROR_MSG
)
SELECT TO_NUMBER(wrf1.locnr) AS STORE,
t001w.name1 AS STORE_NAME,
wrf1.zzhauskuna AS STORE_NAME10,
SUBSTR(wrf1.zzhauskuna, 1, 3) AS STORE_NAME3,
null as STORE_NAME_SECONDARY,
'A' AS STORE_CLASS,
null as STORE_MGR_NAME,
CASE WHEN wrf1.eroed != '00000000' THEN TO_DATE(wrf1.eroed, 'YYYYMMDD') ELSE NULL END AS STORE_OPEN_DATE,
CASE WHEN wrf1.schld != '00000000' THEN TO_DATE(wrf1.schld, 'YYYYMMDD') ELSE NULL END AS STORE_CLOSE_DATE,
null as ACQUIRED_DATE,
null as REMODEL_DATE,
null as FAX_NUMBER,
null as PHONE_NUMBER,
null as EMAIL,
null as TOTAL_SQUARE_FT,
wrf1.verfl AS SELLING_SQUARE_FT,
null as LINEAR_DISTANCE,
1010 as VAT_REGION,
'Y' as VAT_INCLUDE_IND,
'Y' as STOCKHOLDING_IND,
1 as CHANNEL_ID,
'' AS STORE_FORMAT,
null AS MALL_NAME,
3 AS DISTRICT,
12 AS TRANSFER_ZONE,
1801 AS DEFAULT_WH,
null AS STOP_ORDER_DAYS,
1 AS START_ORDER_DAYS,
'EUR' AS CURRENCY_CODE,
2 AS LANG,
null AS PRICE_STORE,
'R' AS TRAN_NO_GENERATED,
'Y' AS INTEGRATED_POS_IND,
SUBSTR(kna1.BBBNR || kna1.BBSNR || kna1.BUBKZ, 1, 9) AS DUNS_NUMBER,
SUBSTR(kna1.BBBNR || kna1.BBSNR || kna1.BUBKZ, -4) AS DUNS_LOC,
null AS SISTER_STORE,
1001 AS TSF_ENTITY_ID,
1001 AS ORG_UNIT_ID,
'N' AS AUTO_RCV,
'N' AS REMERCH_IND,
'C' AS STORE_TYPE,
null AS WF_CUSTOMER_ID,
'Europe/Berlin' AS TIMEZONE_NAME,
'N' AS CUSTOMER_ORDER_LOC_IND,
'N' AS CUSTOMER_ORDER_SHIP_IND,
'N' AS GIFT_WRAPPING_IND,
kna1.stceg AS TAX_ID,
'N' AS ONLINE_STORE_IND,
'DMF' AS CREATE_ID,
SYSDATE AS CREATE_DATETIME,
null AS COPY_ACTIVITY_IND,
null AS COPY_CLEARANCE_IND,
null AS COPY_DLVRY_IND,
null AS COPY_REPL_IND,
null AS COST_LOCATION,
null AS LIKE_STORE,
null AS PROCESS_MODE,
null AS PROCESS_STATUS,
null AS RMS_ASYNC_ID,
'C' AS CTRL_STATUS,
sysdate AS CTRL_DATETIME,
null AS CTRL_ERROR_MSG
FROM sap_slt_prod_pv1.wrf1 wrf1
JOIN sap_slt_prod_pv1.t001w t001w ON t001w.kunnr = wrf1.locnr
JOIN sap_slt_prod_pv1.wrf3 wrf3 ON wrf3.locnr = wrf1.locnr
JOIN sap_slt_prod_pv1.kna1 kna1 ON kna1.kunnr = t001w.kunnr
WHERE t001w.vlfkz = 'A'
and t001w.werks = '0017'
;
SELECT * FROM STORE_ADD_CTRL WHERE STORE = 17;
SELECT * FROM XREF_LOCATION WHERE ORACLE_type = 'W' ;
INSERT INTO XREF_LOCATION (SELECT '0017','S', STORE_NAME, STORE, STORE_NAME, 'S',CHANNEL_ID, CHANNEL_ID FROM STORE_ADD_CTRL WHERE STORE = 17);
-- export by sql developer
-- there are more fields on DMF table than in MFCS
Insert into STORE (
STORE, STORE_NAME, STORE_NAME10, STORE_NAME3,
STORE_NAME_SECONDARY, STORE_CLASS,
STORE_MGR_NAME, STORE_OPEN_DATE,
STORE_CLOSE_DATE, ACQUIRED_DATE,
REMODEL_DATE, FAX_NUMBER, PHONE_NUMBER,
EMAIL, TOTAL_SQUARE_FT, SELLING_SQUARE_FT,
LINEAR_DISTANCE, VAT_REGION, VAT_INCLUDE_IND,
STOCKHOLDING_IND, CHANNEL_ID, STORE_FORMAT,
MALL_NAME, DISTRICT, TRANSFER_ZONE,
DEFAULT_WH, STOP_ORDER_DAYS, START_ORDER_DAYS,
CURRENCY_CODE, ORIG_CURRENCY_CODE,
LANG, TRAN_NO_GENERATED, INTEGRATED_POS_IND,
DUNS_NUMBER, DUNS_LOC, SISTER_STORE,
TSF_ENTITY_ID, ORG_UNIT_ID, AUTO_RCV,
REMERCH_IND, STORE_TYPE, WF_CUSTOMER_ID,
TIMEZONE_NAME, CUSTOMER_ORDER_LOC_IND,
CUSTOMER_ORDER_SHIP_IND, GIFT_WRAPPING_IND,
TAX_ID, ONLINE_STORE_IND
)
values
(
17,
'Essen II',
'ESS-II',
'ESS',
null,
'A',
'dummy',
to_date('25-MAR-21', 'DD-MON-RR'),
to_date('31-DEC-21', 'DD-MON-RR'),
null,
null,
null,
null,
null,
null,
1893,
null,
1010,
'Y',
'Y',
1,
null,
null,
3,
12,
1801,
null,
1,
'EUR',
'EUR',
2,
'R',
'Y',
'404280200',
'0174',
null,
1001,
1001,
'N',
'N',
'C',
null,
'Europe/Berlin',
'N',
'N',
'N',
'DE811215224',
'N'
);