Source: DMF/PuC/AIF/ItemLoc.sql
select count(1) from ITEM_LOC_CTRL where loc_type = 'S'; -- item loc store
SELECT * FROM SAP_SLT_PROD_PV1.MARD where matnr = '000000199643010030' and werks = '0003';
select ctrl_status from ITEM_LOC_CTRL group by ctrl_status;
-- item loc for fashion item and bags only stores
/*INSERT INTO /*+ PARALLEL(24) */ /*ITEM_LOC_CTRL (
item,
loc,
item_parent,
item_grandparent,
loc_type,
unit_retail,
regular_unit_retail,
multi_units,
multi_unit_retail,
multi_selling_uom,
selling_unit_retail,
selling_uom,
promo_retail,
promo_selling_retail,
promo_selling_uom,
clear_ind,
taxable_ind,
local_item_desc,
local_short_desc,
ti,
hi,
store_ord_mult,
status,
status_update_date,
daily_waste_pct,
meas_of_each,
meas_of_price,
uom_of_price,
primary_variant,
primary_cost_pack,
primary_supp,
primary_cntry,
receive_as_type,
inbound_handling_days,
source_method,
source_wh,
store_price_ind,
rpm_ind,
uin_type,
uin_label,
capture_time,
ext_uin_ind,
ranged_ind,
costing_loc,
costing_loc_type,
promotable_ind,
rfid_ind,
ctrl_status,
ctrl_datetime
)*/
WITH skus AS (SELECT *
FROM ITEM_MASTER_CTRL
WHERE item_level = tran_level
AND ctrl_status = 'C'
),
lref AS (SELECT DISTINCT
SAP_ID loc
,ORACLE_ID ORACLE_ID
,ORACLE_TYPE loc_type
FROM XREF_LOCATION
WHERE ORACLE_TYPE = 'S'),
cp AS (SELECT skus.item, skus.item_parent, lref.loc, lref.loc_type, lref.oracle_id, r.price
FROM INIT_PRICE_RAW r,
skus,
lref
WHERE PRICE_CHANGE_TRAN_TYPE = 0
and r.item = skus.item
and r.store = lref.oracle_id),
sup AS (SELECT item
,supplier
,origin_country_id
FROM ITEM_SUPP_COUNTRY_CTRL
WHERE primary_supp_ind = 'Y'
AND primary_country_ind = 'Y'),
item_loc AS (SELECT mard.matnr item
,cp.ORACLE_ID loc
,cp.item_parent item_parent
,NULL item_grandparent
,cp.loc_type loc_type
,cp.price unit_retail
,cp.price regular_unit_retail
,NULL multi_units
,NULL multi_unit_retail
,NULL multi_selling_uom
,cp.price selling_unit_retail
,'EA' selling_uom
,NULL promo_retail
,NULL promo_selling_retail
,NULL promo_selling_uom
,'N' clear_ind
,CASE
WHEN mara.taklv = 1
THEN 'Y'
ELSE 'N'
END taxable_ind
,COALESCE((SELECT mk1.maktx
FROM sap_slt_prod_pv1.makt mk1
WHERE mk1.mandt = mara.mandt
AND mk1.matnr = mara.matnr
AND mk1.spras = 'E'
AND ROWNUM = 1),
(SELECT mk2.maktx
FROM sap_slt_prod_pv1.makt mk2
WHERE mk2.mandt = mara.mandt
AND mk2.matnr = mara.matnr
AND mk2.spras = 'D'
AND ROWNUM = 1)) AS local_item_desc
,NULL local_short_desc
,1 ti
,1 hi
,'E' store_ord_mult
,CASE
WHEN mara.zzaltware = '1'
THEN 'C'
ELSE 'A'
END status
,SYSDATE status_update_date
,NULL daily_waste_pct
,NULL meas_of_each
,NULL meas_of_price
,NULL uom_of_price
,NULL primary_variant
,NULL primary_cost_pack
,sup.supplier primary_supp
,sup.origin_country_id primary_cntry
,NULL receive_as_type
,NULL inbound_handling_days
,'W' source_method
,NULL source_wh
,'N' store_price_ind
,'N' rpm_ind
,NULL uin_type
,NULL uin_label
,NULL capture_time
,'N' ext_uin_ind
,'Y' ranged_ind
,NULL costing_loc
,NULL costing_loc_type
,'Y' promotable_ind
,'N' rfid_ind
--,cp.loc as sap_id
--,cp.oracle_id
,'S' AS ctrl_status
,SYSDATE AS create_datetime
FROM (SELECT matnr, werks
FROM SAP_SLT_PROD_PV1.MARD
WHERE 1 = 1
AND REGEXP_LIKE(werks, '^[0-9]') -- Only werks represented by numbers
GROUP BY matnr, werks) mard
,cp
,sup
,SAP_SLT_PROD_PV1.MARA mara
WHERE 1 = 1
AND mard.matnr = mara.matnr
AND mara.matnr = cp.item
AND mara.mtart IN ('ZMOD', 'ZNLA')
AND mard.werks = cp.loc
AND mara.matnr = sup.item
)
SELECT /*+ PARALLEL(24) */ distinct * FROM item_loc
;
-- item loc for fashion item and bags only buying whs
INSERT /*+ PARALLEL(24) */ INTO ITEM_LOC_CTRL (item,
loc,
item_parent,
item_grandparent,
loc_type,
unit_retail,
regular_unit_retail,
multi_units,
multi_unit_retail,
multi_selling_uom,
selling_unit_retail,
selling_uom,
promo_retail,
promo_selling_retail,
promo_selling_uom,
clear_ind,
taxable_ind,
local_item_desc,
local_short_desc,
ti,
hi,
store_ord_mult,
status,
status_update_date,
daily_waste_pct,
meas_of_each,
meas_of_price,
uom_of_price,
primary_variant,
primary_cost_pack,
primary_supp,
primary_cntry,
receive_as_type,
inbound_handling_days,
source_method,
source_wh,
store_price_ind,
rpm_ind,
uin_type,
uin_label,
capture_time,
ext_uin_ind,
ranged_ind,
costing_loc,
costing_loc_type,
promotable_ind,
rfid_ind,
ctrl_status,
ctrl_datetime
)
WITH skus AS (SELECT *
FROM ITEM_MASTER_CTRL
WHERE item_level = tran_level
AND ctrl_status = 'C'
),
lref AS (SELECT ORACLE_ID_BY_CHANNEL loc
,SAP_ID loc_sap
,ORACLE_TYPE loc_type
,ORACLE_ID
FROM XREF_LOCATION
WHERE ORACLE_TYPE = 'W'),
sup AS (SELECT item
,supplier
,origin_country_id
FROM ITEM_SUPP_COUNTRY_CTRL
WHERE primary_supp_ind = 'Y'
AND primary_country_ind = 'Y'),
item_loc AS (SELECT mard.matnr item
,lref.loc loc
,skus.item_parent item_parent
,NULL item_grandparent
,lref.loc_type loc_type
,NULL unit_retail
,NULL regular_unit_retail
,NULL multi_units
,NULL multi_unit_retail
,NULL multi_selling_uom
,NULL selling_unit_retail
,NULL selling_uom
,NULL promo_retail
,NULL promo_selling_retail
,NULL promo_selling_uom
,'N' clear_ind
,CASE
WHEN mara.taklv = 1
THEN 'Y'
ELSE 'N'
END taxable_ind
,COALESCE((SELECT mk1.maktx
FROM sap_slt_prod_pv1.makt mk1
WHERE mk1.mandt = mara.mandt
AND mk1.matnr = mara.matnr
AND mk1.spras = 'E'
AND ROWNUM = 1),
(SELECT mk2.maktx
FROM sap_slt_prod_pv1.makt mk2
WHERE mk2.mandt = mara.mandt
AND mk2.matnr = mara.matnr
AND mk2.spras = 'D'
AND ROWNUM = 1)) AS local_item_desc
,NULL local_short_desc
,1 ti
,1 hi
,'E' store_ord_mult
,CASE
WHEN mara.zzaltware = '1'
THEN 'C'
ELSE 'A'
END status
,SYSDATE status_update_date
,NULL daily_waste_pct
,NULL meas_of_each
,NULL meas_of_price
,NULL uom_of_price
,NULL primary_variant
,NULL primary_cost_pack
,sup.supplier primary_supp
,sup.origin_country_id primary_cntry
,NULL receive_as_type
,NULL inbound_handling_days
,'W' source_method
,NULL source_wh
,'N' store_price_ind
,'N' rpm_ind
,NULL uin_type
,NULL uin_label
,NULL capture_time
,'N' ext_uin_ind
,'Y' ranged_ind
,NULL costing_loc
,NULL costing_loc_type
,'Y' promotable_ind
,'N' rfid_ind
,'B' AS ctrl_status
,SYSDATE AS create_datetime
,LREF.LOC AS ORALCLE_ID_BY_CHANLEL
,LREF.LOC_SAP AS SAP_Id
,LREF.ORACLE_ID
FROM (SELECT matnr, werks
FROM SAP_SLT_PROD_PV1.MARD
WHERE 1 = 1
AND REGEXP_LIKE(werks, '^[0-9]') -- Only werks represented by numbers
GROUP BY matnr, werks) mard
,SAP_SLT_PROD_PV1.MARA mara
,lref
,skus
,sup
WHERE 1 = 1
AND mard.matnr = mara.matnr
AND mara.mtart IN ('ZMOD', 'ZNLA')
AND mard.matnr = skus.item
AND mard.werks = lref.loc_sap
AND mara.matnr = sup.item)
SELECT /*+ PARALLEL(24) */ DISTINCT * FROM item_loc;
-- item loc for fashion item and bags only ibs whs (PR1)
INSERT INTO /*+ PARALLEL(24) */ ITEM_LOC_CTRL (item,
loc,
item_parent,
item_grandparent,
loc_type,
unit_retail,
regular_unit_retail,
multi_units,
multi_unit_retail,
multi_selling_uom,
selling_unit_retail,
selling_uom,
promo_retail,
promo_selling_retail,
promo_selling_uom,
clear_ind,
taxable_ind,
local_item_desc,
local_short_desc,
ti,
hi,
store_ord_mult,
status,
status_update_date,
daily_waste_pct,
meas_of_each,
meas_of_price,
uom_of_price,
primary_variant,
primary_cost_pack,
primary_supp,
primary_cntry,
receive_as_type,
inbound_handling_days,
source_method,
source_wh,
store_price_ind,
rpm_ind,
uin_type,
uin_label,
capture_time,
ext_uin_ind,
ranged_ind,
costing_loc,
costing_loc_type,
promotable_ind,
rfid_ind,
ctrl_status,
ctrl_datetime
)
WITH skus AS (SELECT *
FROM ITEM_MASTER_CTRL
WHERE item_level = tran_level
AND ctrl_status = 'C'
),
lref AS (SELECT ORACLE_ID_BY_CHANNEL loc
,SAP_ID loc_sap
,ORACLE_TYPE loc_type
,ORACLE_ID
FROM XREF_LOCATION
WHERE ORACLE_TYPE = 'W'),
sup AS (SELECT item
,supplier
,origin_country_id
FROM ITEM_SUPP_COUNTRY_CTRL
WHERE primary_supp_ind = 'Y'
AND primary_country_ind = 'Y'),
item_loc AS (SELECT mara.matnr item
,lref.loc loc
,skus.cp.item_parent item_parent
,NULL item_grandparent
,lref.loc_type loc_type
,NULL unit_retail
,NULL regular_unit_retail
,NULL multi_units
,NULL multi_unit_retail
,NULL multi_selling_uom
,NULL selling_unit_retail
,NULL selling_uom
,NULL promo_retail
,NULL promo_selling_retail
,NULL promo_selling_uom
,'N' clear_ind
,CASE
WHEN mara.taklv = 1
THEN 'Y'
ELSE 'N'
END taxable_ind
,COALESCE((SELECT mk1.maktx
FROM sap_slt_prod_pv1.makt mk1
WHERE mk1.mandt = mara.mandt
AND mk1.matnr = mara.matnr
AND mk1.spras = 'E'
AND ROWNUM = 1),
(SELECT mk2.maktx
FROM sap_slt_prod_pv1.makt mk2
WHERE mk2.mandt = mara.mandt
AND mk2.matnr = mara.matnr
AND mk2.spras = 'D'
AND ROWNUM = 1)) AS local_item_desc
,NULL local_short_desc
,1 ti
,1 hi
,'E' store_ord_mult
,CASE
WHEN mara.zzaltware = '1'
THEN 'C'
ELSE 'A'
END status
,SYSDATE status_update_date
,NULL daily_waste_pct
,NULL meas_of_each
,NULL meas_of_price
,NULL uom_of_price
,NULL primary_variant
,NULL primary_cost_pack
,sup.supplier primary_supp
,sup.origin_country_id primary_cntry
,NULL receive_as_type
,NULL inbound_handling_days
,'W' source_method
,NULL source_wh
,'N' store_price_ind
,'N' rpm_ind
,NULL uin_type
,NULL uin_label
,NULL capture_time
,'N' ext_uin_ind
,'Y' ranged_ind
,NULL costing_loc
,NULL costing_loc_type
,'Y' promotable_ind
,'N' rfid_ind
,'C' AS ctrl_status
,SYSDATE AS create_datetime
,LREF.LOC AS ORALCLE_ID_BY_CHANLEL
,LREF.LOC_SAP AS SAP_Id
,LREF.OR_ID
FROM (SELECT matnr, werks
FROM SAP_SLT_PROD_PR1.MARD
WHERE 1 = 1
AND REGEXP_LIKE(werks, '^[0-9]') -- Only werks represented by numbers
GROUP BY matnr, werks) mard
,SAP_SLT_PROD_PV1.MARA mara
,XREF_ITEM_IBC_RETAIL xref -- xref to transform PR1 into PV1
,lref
,skus
,sup
WHERE 1 = 1
AND mard.matnr = xref.item_pr1
AND mara.matnr = xref.item_pv1
and mara.mtart IN ('ZMOD', 'ZNLA')
AND mara.matnr = skus.item
AND mard.werks = lref.loc_sap
AND mara.matnr = sup.item)
SELECT /*+ PARALLEL(24) */ DISTINCT * FROM item_loc;
-- item loc for service item explode for all stores
INSERT INTO /*+ PARALLEL(24) */ ITEM_LOC_CTRL (
item,
loc,
item_parent,
item_grandparent,
loc_type,
unit_retail,
regular_unit_retail,
multi_units,
multi_unit_retail,
multi_selling_uom,
selling_unit_retail,
selling_uom,
promo_retail,
promo_selling_retail,
promo_selling_uom,
clear_ind,
taxable_ind,
local_item_desc,
local_short_desc,
ti,
hi,
store_ord_mult,
status,
status_update_date,
daily_waste_pct,
meas_of_each,
meas_of_price,
uom_of_price,
primary_variant,
primary_cost_pack,
primary_supp,
primary_cntry,
receive_as_type,
inbound_handling_days,
source_method,
source_wh,
store_price_ind,
rpm_ind,
uin_type,
uin_label,
capture_time,
ext_uin_ind,
ranged_ind,
costing_loc,
costing_loc_type,
promotable_ind,
rfid_ind,
ctrl_status,
ctrl_datetime
)
WITH skus AS (SELECT *
FROM ITEM_MASTER_CTRL
WHERE item_level = tran_level
AND ctrl_status = 'C'
),
lref AS (SELECT DISTINCT
SAP_ID loc_sap
,ORACLE_ID ORACLE_ID
,ORACLE_TYPE loc_type
FROM XREF_LOCATION
WHERE ORACLE_TYPE = 'S'),
item_loc AS (SELECT SKUS.ITEM item
,LREF.ORACLE_ID loc
,skus.item_parent item_parent
,NULL item_grandparent
,lref.loc_type loc_type
,0.01 unit_retail --Make sure to revise value in other table
,0.01 regular_unit_retail
,NULL multi_units
,NULL multi_unit_retail
,NULL multi_selling_uom
,0.01 selling_unit_retail
,'EA' selling_uom
,NULL promo_retail
,NULL promo_selling_retail
,NULL promo_selling_uom
,'N' clear_ind
,CASE
WHEN mara.taklv = 1
THEN 'Y'
ELSE 'N'
END taxable_ind
,COALESCE((SELECT mk1.maktx
FROM sap_slt_prod_pv1.makt mk1
WHERE mk1.mandt = mara.mandt
AND mk1.matnr = mara.matnr
AND mk1.spras = 'E'
AND ROWNUM = 1),
(SELECT mk2.maktx
FROM sap_slt_prod_pv1.makt mk2
WHERE mk2.mandt = mara.mandt
AND mk2.matnr = mara.matnr
AND mk2.spras = 'D'
AND ROWNUM = 1)) AS local_item_desc
,NULL local_short_desc
,1 ti
,1 hi
,'E' store_ord_mult
,CASE
WHEN mara.zzaltware = '1'
THEN 'C'
ELSE 'A'
END status
,SYSDATE status_update_date
,NULL daily_waste_pct
,NULL meas_of_each
,NULL meas_of_price
,NULL uom_of_price
,NULL primary_variant
,NULL primary_cost_pack
,NULL primary_supp
,NULL primary_cntry
,NULL receive_as_type
,NULL inbound_handling_days
,'W' source_method
,NULL source_wh
,'N' store_price_ind
,'N' rpm_ind
,NULL uin_type
,NULL uin_label
,NULL capture_time
,'N' ext_uin_ind
,'Y' ranged_ind
,NULL costing_loc
,NULL costing_loc_type
,'Y' promotable_ind
,'N' rfid_ind
,'O' ctrl_status
,SYSDATE ctrl_datetime
FROM SAP_SLT_PROD_PV1.MARA mara
,lref
,skus
WHERE mara.matnr = skus.item
AND mara.mtart IN ( 'ZDIE')) --'VERP',
SELECT /*+ PARALLEL(24) */ distinct * FROM item_loc;
-- item loc for transportation item explode for all WHs.
INSERT INTO /*+ PARALLEL(24) */ ITEM_LOC_CTRL (
item,
loc,
item_parent,
item_grandparent,
loc_type,
unit_retail,
regular_unit_retail,
multi_units,
multi_unit_retail,
multi_selling_uom,
selling_unit_retail,
selling_uom,
promo_retail,
promo_selling_retail,
promo_selling_uom,
clear_ind,
taxable_ind,
local_item_desc,
local_short_desc,
ti,
hi,
store_ord_mult,
status,
status_update_date,
daily_waste_pct,
meas_of_each,
meas_of_price,
uom_of_price,
primary_variant,
primary_cost_pack,
primary_supp,
primary_cntry,
receive_as_type,
inbound_handling_days,
source_method,
source_wh,
store_price_ind,
rpm_ind,
uin_type,
uin_label,
capture_time,
ext_uin_ind,
ranged_ind,
costing_loc,
costing_loc_type,
promotable_ind,
rfid_ind,
ctrl_status,
ctrl_datetime
)
WITH skus AS (SELECT *
FROM ITEM_MASTER_CTRL
WHERE item_level = tran_level
AND ctrl_status = 'C'
),
lref AS (SELECT DISTINCT
SAP_ID loc_sap
,ORACLE_ID ORACLE_ID
,ORACLE_TYPE loc_type
FROM XREF_LOCATION
WHERE ORACLE_TYPE = 'W'),
item_loc AS (SELECT SKUS.ITEM item
,LREF.ORACLE_ID loc
,skus.item_parent item_parent
,NULL item_grandparent
,lref.loc_type loc_type
,0.01 unit_retail --Make sure to revise value in other table
,0.01 regular_unit_retail
,NULL multi_units
,NULL multi_unit_retail
,NULL multi_selling_uom
,0.01 selling_unit_retail
,'EA' selling_uom
,NULL promo_retail
,NULL promo_selling_retail
,NULL promo_selling_uom
,'N' clear_ind
,CASE
WHEN mara.taklv = 1
THEN 'Y'
ELSE 'N'
END taxable_ind
,COALESCE((SELECT mk1.maktx
FROM sap_slt_prod_pv1.makt mk1
WHERE mk1.mandt = mara.mandt
AND mk1.matnr = mara.matnr
AND mk1.spras = 'E'
AND ROWNUM = 1),
(SELECT mk2.maktx
FROM sap_slt_prod_pv1.makt mk2
WHERE mk2.mandt = mara.mandt
AND mk2.matnr = mara.matnr
AND mk2.spras = 'D'
AND ROWNUM = 1)) AS local_item_desc
,NULL local_short_desc
,1 ti
,1 hi
,'E' store_ord_mult
,CASE
WHEN mara.zzaltware = '1'
THEN 'C'
ELSE 'A'
END status
,SYSDATE status_update_date
,NULL daily_waste_pct
,NULL meas_of_each
,NULL meas_of_price
,NULL uom_of_price
,NULL primary_variant
,NULL primary_cost_pack
,NULL primary_supp
,NULL primary_cntry
,NULL receive_as_type
,NULL inbound_handling_days
,'W' source_method
,NULL source_wh
,'N' store_price_ind
,'N' rpm_ind
,NULL uin_type
,NULL uin_label
,NULL capture_time
,'N' ext_uin_ind
,'Y' ranged_ind
,NULL costing_loc
,NULL costing_loc_type
,'Y' promotable_ind
,'N' rfid_ind
,'V' ctrl_status
,SYSDATE ctrl_datetime
FROM SAP_SLT_PROD_PV1.MARA mara
,lref
,skus
WHERE mara.matnr = skus.item
--AND mard.werks = lref.loc_sap
AND mara.mtart IN ('VERP'))
SELECT /*+ PARALLEL(24) */ distinct * FROM item_loc;
update item_loc_ctrl set status = 'O';
-- ctrl status
/*
O --> servico
S --> stores
V --> transporte
B --> BUYING WHS
C --> IBC WHs
*/