Source: DMF/PuC/AIF/Stock.sql
select oracle_id from xref_location where oracle_type = 'S' group by oracle_id having count(1) > 1;
select * from xref_location where oracle_id = 78;
-- item loc soh for stores (PV1)
-- pre validation
-- check duplicated items,stores in SAP table
select MATNR,bwkey from SAP_SLT_PROD_PV1.MBEW where lbkum > 0 GROUP BY MATNR,bwkey HAVING COUNT(1) > 1;
-- for merged store 0078 check items with diff avg cost (missing join with item loc ctrl)
select MATNR from SAP_SLT_PROD_PV1.MBEW where mbew.bwkey in ('0078','0079','0179') and lbkum > 0 GROUP BY MATNR HAVING COUNT(1) > 1;
select count(1) from (select MATNR,bwkey, lbkum from SAP_SLT_PROD_PV1.MBEW m, xref_location x where lbkum < 0 and x.sap_id = m.bwkey and oracle_type = 'W');
--select count(1) from (
select MATNR AS ITEM, bwkey AS SAP_LOCATION_ID, decode(sap_type,'S','STORE','WAREHOUSE') AS SAP_LOC_TYPE, lbkum AS STOCK, ORACLE_ID AS ORACLE_LOCATION_ID, decode(oracle_type,'S','STORE','WAREHOUSE') AS ORACLE_LOC_TYPE from SAP_SLT_PROD_PV1.MBEW m, xref_location x where lbkum < 0 and x.sap_id = m.bwkey
--)
;
--select count(1) from (
select MATNR as item, bwkey as location_id, lbkum, decode(sap_type,'S','STORE','WAREHOUSE') AS SAP_LOC_TYPE, decode(oracle_type,'S','STORE','WAREHOUSE') AS ORACLE_LOC_TYPE
from SAP_SLT_PROD_PV1.MBEW m, xref_location x, item_master_ctrl im where lbkum < 0 and x.sap_id = m.bwkey and oracle_type = 'S' and im.item = MATNR
--)
;
select MATNR,bwkey from SAP_SLT_PROD_PR1.MBEW where lbkum < 0 ;
select MATNR,bwkey from SAP_SLT_PROD_PV1.MBEW m where lbkum < 0 ;
select * from xref_location;
select count(1) from ITEM_LOC_SOH_CTRL;
truncate table ITEM_LOC_SOH_CTRL;
INSERT INTO ITEM_LOC_SOH_CTRL(item
,item_parent
,item_grandparent
,loc
,loc_type
,av_cost
,unit_cost
,stock_on_hand
,soh_update_datetime
,last_hist_export_date
,in_transit_qty
,pack_comp_intran
,pack_comp_soh
,tsf_reserved_qty
,pack_comp_resv
,tsf_expected_qty
,pack_comp_exp
,rtv_qty
,non_sellable_qty
,customer_resv
,customer_backorder
,pack_comp_cust_resv
,pack_comp_cust_back
,first_received
,last_received
,qty_received
,first_sold
,last_sold
,qty_sold
,primary_supp
,primary_cntry
,average_weight
,finisher_av_retail
,finisher_units
,pack_comp_non_sellable
,in_progress_sales_qty
,ctrl_status)
SELECT s.item item
,s.item_parent item_parent
,NULL item_grandparent
,s.store loc
,'S' loc_type --mandatory
,s.av_cost av_cost
,NULL unit_cost
,s.soh stock_on_hand
,NULL soh_update_datetime
,NULL last_hist_export_date
,0 in_transit_qty --mandatory
,0 pack_comp_intran --mandatory
,0 pack_comp_soh --mandatory
,0 tsf_reserved_qty --mandatory
,0 pack_comp_resv --mandatory
,0 tsf_expected_qty --mandatory
,0 pack_comp_exp --mandatory
,0 rtv_qty --mandatory
,0 non_sellable_qty --mandatory
,0 customer_resv --mandatory
,0 customer_backorder --mandatory
,0 pack_comp_cust_resv --mandatory
,0 pack_comp_cust_back --mandatory
,NULL first_received
,NULL last_received
,NULL qty_received
,NULL first_sold
,NULL last_sold
,NULL qty_sold
,NULL primary_supp
,NULL primary_cntry
,NULL average_weight
,NULL finisher_av_retail
,NULL finisher_units
,0 pack_comp_non_sellable --mandatory
,0 in_progress_sales_qty --mandatory
,'I' ctrl_status
FROM
(SELECT IL.ITEM, il.item_parent, x.oracle_id as store, avg(mbew.verpr) as av_cost, sum(mbew.lbkum) as soh
FROM SAP_SLT_PROD_PV1.MBEW mbew
JOIN XREF_LOCATION x on ORACLE_TYPE = 'S' and x.SAP_ID = mbew.bwkey
JOIN ITEM_LOC_CTRL il on il.item = mbew.matnr and il.loc = ORACLE_ID and ctrl_status = 'C'
WHERE mbew.lbkum > 0
GROUP BY IL.ITEM, il.item_parent, x.oracle_id) s;
---------------
select item, loc from ITEM_LOC_SOH_CTRL group by item, loc having count(1) > 1;
select * from ITEM_LOC_SOH_CTRL where ctrl_status <> 'A';
create table ITEM_LOC_SOH_ctrl_error as select * from ITEM_LOC_SOH_CTRL where rownum = 1;
UPDATE ITEM_LOC_ctrl ils1 SET CTRL_STATUS = 'E' where exists (select 1 from ITEM_LOC_SOH_ctrl_error ILS WHERE ILs1.ITEM = ILS.ITEM_parent AND ILs1.LOC = ILS.LOC );
--59749
select * from ITEM_LOC_SOH_ctrl where ctrl_status = 'E';
select * from item_loc_ctrl where item = '000000204901603132' and ctrl_status = 'E';
update item_loc_ctrl il1 set ctrl_status = 'E'
WHERE loc_type = 'W' and exists (select 1 from item_loc_ctrl il2 where il1.item = il2.item and il1.loc = il2.source_wh );
select * from ITEM_LOC_SOH_ctrl_error;
update ITEM_LOC_soh_CTRL SET CTRL_STATUS = 'C';
---------------
INSERT INTO ITEM_LOC_SOH_CTRL(item
,item_parent
,item_grandparent
,loc
,loc_type
,av_cost
,unit_cost
,stock_on_hand
,soh_update_datetime
,last_hist_export_date
,in_transit_qty
,pack_comp_intran
,pack_comp_soh
,tsf_reserved_qty
,pack_comp_resv
,tsf_expected_qty
,pack_comp_exp
,rtv_qty
,non_sellable_qty
,customer_resv
,customer_backorder
,pack_comp_cust_resv
,pack_comp_cust_back
,first_received
,last_received
,qty_received
,first_sold
,last_sold
,qty_sold
,primary_supp
,primary_cntry
,average_weight
,finisher_av_retail
,finisher_units
,pack_comp_non_sellable
,in_progress_sales_qty
,ctrl_status)
with OWN_BRANDS_SKU_LOC AS (
select ITEM_PV1, ITEM_PR1, C.LOC, w.wh, w.wh_slt
from XREF_ITEM_IBC_RETAIL xref,
item_loc_ctrl c,
(select c.wh, x.wh_slt, x.v_wh
from wh_ctrl c,
xref_wh x
where x.wh = c.wh
and c.ctrl_status = 'C') w
where c.item = xref.ITEM_PV1
and c.ctrl_status = 'C'
and w.v_wh = c.loc
),
PR1_SOH AS (
SELECT mbew.matnr item
,'1571' loc
,avg(mbew.verpr) wac
,sum(mbew.lbkum) soh
FROM SAP_SLT_PROD_PR1.MBEW mbew
WHERE mbew.bwkey IN ('0322', '0157')
GROUP BY mbew.matnr
)
SELECT ob.item_pv1 item
,NULL item_parent
,NULL item_grandparent
,p.loc loc --Add ibc.wh filter
,'W' loc_type --Mandatory, might not be the right one
,p.wac av_cost
,NULL unit_cost
,p.soh stock_on_hand
,NULL soh_update_datetime
,NULL last_hist_export_date
,0 in_transit_qty --mandatory
,0 pack_comp_intran --mandatory
,0 pack_comp_soh --mandatory
,0 tsf_reserved_qty --mandatory
,0 pack_comp_resv --mandatory
,0 tsf_expected_qty --mandatory
,0 pack_comp_exp --mandatory
,0 rtv_qty --mandatory
,0 non_sellable_qty --mandatory
,0 customer_resv --mandatory
,0 customer_backorder --mandatory
,0 pack_comp_cust_resv --mandatory
,0 pack_comp_cust_back --mandatory
,NULL first_received
,NULL last_received
,NULL qty_received
,NULL first_sold
,NULL last_sold
,NULL qty_sold
,NULL primary_supp
,NULL primary_cntry
,NULL average_weight
,NULL finisher_av_retail
,NULL finisher_units
,0 pack_comp_non_sellable --mandatory
,0 in_progress_sales_qty --mandatory
,'I' ctrl_status
FROM PR1_SOH p,
OWN_BRANDS_SKU_LOC ob
WHERE 1=1
and ob.loc = p.loc
AND ob.ITEM_PR1 = p.item;
--- PR1
select ms.bwkey, count(matnr), sum(lbkum)
from sap_slt_prod_pr1.mbew ms,
SAP_SLT_PROD_PV1.t001w
WHERE lbkum >0 and VLFKZ = 'B'
and t001w.werks = ms.bwkey
group by ms.bwkey; -- whs
--- PV1
select ms.bwkey, count(matnr), sum(lbkum)
from sap_slt_prod_pv1.mbew ms,
SAP_SLT_PROD_PV1.t001w
WHERE lbkum >0 and VLFKZ = 'B'
and t001w.werks = ms.bwkey
group by ms.bwkey; -- whs