Source: DMF/PuC/AIF/Scripts/item_loc_query3.sql
-- item loc stores
INSERT INTO ITEM_LOC_STG (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)
WITH skus AS (SELECT *
FROM ITEM_MASTER_CTRL
WHERE item_level = tran_level
AND ctrl_status = 'C'
),
lref AS (SELECT store loc
,'S' loc_type
FROM STORE_ADD_CTRL),
cp AS (SELECT skus.item, mcp.store, mcp.initial_price, mcp.clearance_flag, lref.loc_type
FROM MV_CURRENT_PRICE mcp,
skus,
lref
WHERE skus.item = mcp.item
AND mcp.store = lref.loc),
item_loc AS (SELECT mard.matnr item
,cp.store loc
,NULL item_parent
,NULL item_grandparent
,cp.loc_type loc_type
,cp.initial_price unit_retail --Make sure to revise value in other table
,NULL regular_unit_retail
,NULL multi_units
,NULL multi_unit_retail
,NULL multi_selling_uom
,cp.initial_price selling_unit_retail
,NULL selling_uom
,NULL promo_retail
,NULL promo_selling_retail
,NULL promo_selling_uom
,'N' clear_ind --Check how to know if it is red price or black price
,CASE
WHEN mara.taklv = 1
THEN 'Y'
ELSE 'N'
END taxable_ind
,makt.maktx local_item_desc
,NULL local_short_desc
,NULL ti
,NULL hi
,'E' store_ord_mult
,CASE
WHEN mara.zzaltware = '1'
THEN 'D'
ELSE 'A'
END status
,SYSDATE status_update_date --temp
,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
,'E' receive_as_type
,NULL inbound_handling_days
,NULL source_method
,NULL source_wh
,'N' store_price_ind --temp
,'N' rpm_ind --temp
,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
FROM (SELECT matnr, werks
FROM SAP_SLT_PROD_PV1.MARD
WHERE werks != 'Z120'
GROUP BY matnr, werks) mard
,SAP_SLT_PROD_PV1.MARA mara
,SAP_SLT_PROD_PV1.MAKT makt
,cp
WHERE mard.matnr = mara.matnr
AND mara.mandt = makt.mandt
AND mara.matnr = makt.matnr
AND makt.spras = 'E'
AND mara.matnr = cp.item
AND mard.werks = TO_CHAR(cp.store, 'FM0000')
AND mara.mtart NOT IN ('VERP', 'ZNLA', 'ZDIE'))
SELECT * FROM item_loc;
-- item loc buying whs
INSERT INTO ITEM_LOC_STG (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)
WITH skus AS (SELECT *
FROM ITEM_MASTER_CTRL
WHERE item_level = tran_level
--AND ctrl_status = 'C'
),
lref AS (SELECT v_wh loc
,wh_slt loc_sap
,'W' loc_type
FROM XREF_WH
WHERE v_wh IS NOT NULL),
item_loc AS (SELECT mard.matnr item
,lref.loc loc
,NULL item_parent
,NULL item_grandparent
,lref.loc_type loc_type
,1 unit_retail --Make sure to revise value in other table
,NULL regular_unit_retail
,NULL multi_units
,NULL multi_unit_retail
,NULL multi_selling_uom
,1 selling_unit_retail
,NULL selling_uom
,NULL promo_retail
,NULL promo_selling_retail
,NULL promo_selling_uom
,'N' clear_ind --Check how to know if it is red price or black price
,CASE
WHEN mara.taklv = 1
THEN 'Y'
ELSE 'N'
END taxable_ind
,makt.maktx local_item_desc
,NULL local_short_desc
,NULL ti
,NULL hi
,NULL store_ord_mult
,CASE
WHEN mara.zzaltware = '1'
THEN 'D'
ELSE 'A'
END status
,SYSDATE status_update_date --temp
,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
,'E' receive_as_type
,NULL inbound_handling_days
,NULL source_method
,NULL source_wh
,'N' store_price_ind --temp
,'N' rpm_ind --temp
,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
FROM (SELECT matnr, werks
FROM SAP_SLT_PROD_PV1.MARD
WHERE werks != 'Z120'
GROUP BY matnr, werks) mard
,SAP_SLT_PROD_PV1.MARA mara
,SAP_SLT_PROD_PV1.MAKT makt
,lref
,skus
WHERE mard.matnr = mara.matnr
AND mara.mandt = makt.mandt
AND mara.matnr = makt.matnr
AND makt.spras = 'E'
AND mard.matnr = skus.item
AND mard.werks = lref.loc_sap)
SELECT * FROM item_loc;
-- item loc IBC whs
INSERT INTO ITEM_LOC_STG (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)
WITH skus AS (SELECT *
FROM ITEM_MASTER_CTRL
WHERE item_level = tran_level
--AND ctrl_status = 'C'
),
item_loc AS (SELECT mara.matnr item
,1571 loc
,NULL item_parent
,NULL item_grandparent
,'W' loc_type
,10 unit_retail --Make sure to revise value in other table
,NULL regular_unit_retail
,NULL multi_units
,NULL multi_unit_retail
,NULL multi_selling_uom
,10 selling_unit_retail
,NULL selling_uom
,NULL promo_retail
,NULL promo_selling_retail
,NULL promo_selling_uom
,'N' clear_ind --Check how to know if it is red price or black price
,CASE
WHEN mara.taklv = 1
THEN 'Y'
ELSE 'N'
END taxable_ind
,makt.maktx local_item_desc
,NULL local_short_desc
,NULL ti
,NULL hi
,NULL store_ord_mult
,CASE
WHEN mara.zzaltware = '1'
THEN 'D'
ELSE 'A'
END status
,SYSDATE status_update_date --temp
,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
,'E' receive_as_type
,NULL inbound_handling_days
,NULL source_method
,NULL source_wh
,'N' store_price_ind --temp
,'N' rpm_ind --temp
,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
FROM SAP_SLT_PROD_PV1.MARA mara
,SAP_SLT_PROD_PV1.MAKT makt
,SAP_SLT_PROD_PV1.wrf_brands wb
,skus
WHERE 1=1
AND mara.mandt = makt.mandt
AND mara.matnr = makt.matnr
AND makt.spras = 'E'
AND mara.matnr = skus.item
AND mara.brand_id = wb.brand_id
AND mara.mandt = wb.mandt
AND wb.brand_type = '1')
SELECT * FROM item_loc;
-- items de servicos, bags and transportations items
INSERT INTO ITEM_LOC_STG (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)
WITH skus AS (SELECT *
FROM ITEM_MASTER_CTRL
WHERE item_level = tran_level
AND ctrl_status = 'C'
),
lref AS (SELECT store loc
,'S' loc_type
FROM STORE_ADD_CTRL),
cp AS (SELECT skus.item, ils.location, ils.unit_retail, lref.loc_type
FROM MV_ITEM_LAST_SALE ils,
skus,
lref
WHERE skus.item = ils.item
AND ils.location = lref.loc),
item_loc AS (SELECT mard.matnr item
,to_number(cp.location) loc
,NULL item_parent
,NULL item_grandparent
,cp.loc_type loc_type
,cp.unit_retail unit_retail --Make sure to revise value in other table
,NULL regular_unit_retail
,NULL multi_units
,NULL multi_unit_retail
,NULL multi_selling_uom
,cp.unit_retail 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
,makt.maktx local_item_desc
,NULL local_short_desc
,NULL ti
,NULL hi
,'E' store_ord_mult
,CASE
WHEN mara.zzaltware = '1'
THEN 'D'
ELSE 'A'
END status
,SYSDATE status_update_date --temp
,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
,'E' receive_as_type
,NULL inbound_handling_days
,NULL source_method
,NULL source_wh
,'N' store_price_ind --temp
,'N' rpm_ind --temp
,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
FROM (SELECT matnr, werks
FROM SAP_SLT_PROD_PV1.MARD
WHERE werks != 'Z120'
GROUP BY matnr, werks) mard
,SAP_SLT_PROD_PV1.MARA mara
,SAP_SLT_PROD_PV1.MAKT makt
,cp
WHERE mard.matnr = mara.matnr
AND mara.mandt = makt.mandt
AND mara.matnr = makt.matnr
AND makt.spras = 'E'
AND mara.matnr = cp.item
AND mard.werks = location
AND mara.mtart IN ('VERP', 'ZNLA', 'ZDIE'))
SELECT * FROM item_loc;