Source: DMF/PuC/AIF/Wholesales_Hist.sql
/*
DROP TABLE ORI_WHOLESALES_RAW;
DROP TABLE ORI_WHOLESALES_REJECTED;
CREATE TABLE ORI_WHOLESALES_RAW NOLOGGING AS
SELECT S.*,
CAST(NULL AS VARCHAR2(10)) AS SOURCE_SYSTEM,
CAST(NULL AS VARCHAR2(18)) AS ITEM_PR1
FROM ORI_WHOLESALES_CTRL S;
CREATE TABLE ORI_WHOLESALES_REJECTED NOLOGGING AS
SELECT CAST(NULL AS VARCHAR2(10)) AS LOG_LVL
,CAST(NULL AS VARCHAR2(1000)) AS ERROR_MSG
,t.*
FROM ORI_WHOLESALES_RAW t;
ALTER TABLE ORI_WHOLESALES_REJECTED ADD INSERTED_AT DATE DEFAULT SYSDATE NOT NULL ENABLE;
TRUNCATE TABLE ORI_WHOLESALES_REJECTED;
TRUNCATE TABLE ORI_WHOLESALES_RAW;
TRUNCATE TABLE ORI_WHOLESALES_CTRL;
COMMIT;
*/
SET SERVEROUTPUT ON;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- IBC WHOLESALES Magazin du Nord IBC customer - PR1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('1. IBC WHOLESALES Magazin du Nord - LOADING');
END;
/
insert into ORI_WHOLESALES_RAW
(item ,
org_num ,
day_dt ,
slswf_qty ,
slswf_amt_lcl ,
slswf_tax_amt_lcl ,
slswf_acq_cost_amt_lcl,
slswf_mkdn_qty ,
slswf_mkdn_amt_lcl ,
slswf_mkup_qty ,
slswf_mkup_amt_lcl ,
retwf_qty ,
retwf_amt_lcl ,
retwf_tax_amt_lcl ,
retwf_acq_cost_amt_lcl,
retwf_rstk_fee_amt_lcl,
retwf_mkdn_amt_lcl ,
retwf_mkdn_qty ,
retwf_mkup_amt_lcl ,
retwf_mkup_qty ,
global1_exchange_rate ,
global2_exchange_rate ,
global3_exchange_rate ,
loc_curr_code ,
loc_exchange_rate ,
doc_curr_code ,
etl_thread_val ,
delete_flg ,
datasource_num_id ,
create_id ,
create_datetime ,
ctrl_status ,
ctrl_datetime ,
ctrl_error_msg ,
source_system ,
item_pr1 )
--
select xref_item.item_pv1 as item ,
vbak.kunnr as org_num ,
TO_DATE(vbak.erdat,'YYYYMMDD') as day_dt ,
vbap.kwmeng as slswf_qty ,
vbap.netwr as slswf_amt_lcl ,
vbap.mwsbp as slswf_tax_amt_lcl ,
vbfa.rfwrt as slswf_acq_cost_amt_lcl,
null as slswf_mkdn_qty ,
null as slswf_mkdn_amt_lcl ,
null as slswf_mkup_qty ,
null as slswf_mkup_amt_lcl ,
null as retwf_qty ,
null as retwf_amt_lcl ,
null as retwf_tax_amt_lcl ,
null as retwf_acq_cost_amt_lcl,
null as retwf_rstk_fee_amt_lcl,
null as retwf_mkdn_amt_lcl ,
null as retwf_mkdn_qty ,
null as retwf_mkup_amt_lcl ,
null as retwf_mkup_qty ,
null as global1_exchange_rate ,
null as global2_exchange_rate ,
null as global3_exchange_rate ,
'EUR' as loc_curr_code ,
null as loc_exchange_rate ,
vbak.WAERK as doc_curr_code ,
null as etl_thread_val ,
null as delete_flg ,
1 as datasource_num_id ,
user as create_id ,
sysdate as create_datetime ,
'I' as ctrl_status ,
sysdate as ctrl_datetime ,
null as ctrl_error_msg ,
'PR1' as source_system ,
vbap.matnr as item_pr1
--vbap.matnr, vbak.kunnr, vbak.erdat
from sap_slt_prod_pr1.vbak,
sap_slt_prod_pr1.vbap,
sap_slt_prod_pr1.vbfa,
xref_item_ibc_retail xref_item
--
where vbak.vbeln = vbap.vbeln
--
and vbap.vbeln = vbfa.vbelv
and vbap.posnr = vbfa.posnv
and vbfa.bwart = '601'
and vbfa.vbtyp_n = 'R'
--
and vbak.auart = 'ZWHE'
and vbak.erdat >= '20240101'
and vbak.kunnr = '0000340280'
--
and vbap.matnr = xref_item.item_pr1(+)
;
commit;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Buying Wh - Supplier Aufkaefer (discounters) P&C Buying customers like TkMax and others - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('2. 3rd PARTY WHOLESALES Aufkaefer (discounters) - LOADING');
END;
/
insert into ori_wholesales_raw
(item ,
org_num ,
day_dt ,
slswf_qty ,
slswf_amt_lcl ,
slswf_tax_amt_lcl ,
slswf_acq_cost_amt_lcl,
slswf_mkdn_qty ,
slswf_mkdn_amt_lcl ,
slswf_mkup_qty ,
slswf_mkup_amt_lcl ,
retwf_qty ,
retwf_amt_lcl ,
retwf_tax_amt_lcl ,
retwf_acq_cost_amt_lcl,
retwf_rstk_fee_amt_lcl,
retwf_mkdn_amt_lcl ,
retwf_mkdn_qty ,
retwf_mkup_amt_lcl ,
retwf_mkup_qty ,
global1_exchange_rate ,
global2_exchange_rate ,
global3_exchange_rate ,
loc_curr_code ,
loc_exchange_rate ,
doc_curr_code ,
etl_thread_val ,
delete_flg ,
datasource_num_id ,
create_id ,
create_datetime ,
ctrl_status ,
ctrl_datetime ,
ctrl_error_msg ,
source_system ,
item_pr1 )
--
select vbap.matnr as item ,
vbak.kunnr as org_num ,
TO_DATE(vbak.erdat,'YYYYMMDD') as day_dt ,
vbap.kwmeng as slswf_qty ,
vbap.netwr as slswf_amt_lcl ,
vbap.mwsbp as slswf_tax_amt_lcl ,
vbfa.rfwrt as slswf_acq_cost_amt_lcl,
null as slswf_mkdn_qty ,
null as slswf_mkdn_amt_lcl ,
null as slswf_mkup_qty ,
null as slswf_mkup_amt_lcl ,
null as retwf_qty ,
null as retwf_amt_lcl ,
null as retwf_tax_amt_lcl ,
null as retwf_acq_cost_amt_lcl,
null as retwf_rstk_fee_amt_lcl,
null as retwf_mkdn_amt_lcl ,
null as retwf_mkdn_qty ,
null as retwf_mkup_amt_lcl ,
null as retwf_mkup_qty ,
null as global1_exchange_rate ,
null as global2_exchange_rate ,
null as global3_exchange_rate ,
'EUR' as loc_curr_code ,
null as loc_exchange_rate ,
vbak.WAERK as doc_curr_code ,
null as etl_thread_val ,
null as delete_flg ,
1 as datasource_num_id ,
user as create_id ,
sysdate as create_datetime ,
'I' as ctrl_status ,
sysdate as ctrl_datetime ,
null as ctrl_error_msg ,
'PV1' as source_system ,
null as item_pr1
--
from sap_slt_prod_pv1.vbak,
sap_slt_prod_pv1.vbap,
sap_slt_prod_pv1.vbfa
--
where vbak.vbeln = vbap.vbeln
--
and vbap.vbeln = vbfa.vbelv
and vbap.posnr = vbfa.posnv
and vbfa.bwart = '901'
and vbfa.vbtyp_n = 'R'
--
and vbak.auart = 'ZSOA'
and vbak.erdat >= '20240101'
;
commit;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),
tabname => 'ORI_WHOLESALES_RAW',
cascade => TRUE);
END;
/
BEGIN
DBMS_OUTPUT.PUT_LINE('3. STARTING WHOLESALES VALIDATIONS - REJECTED LOADING');
END;
/
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_WHOLESALES_REJECTED
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO /*+ PARALLEL(24) */ ORI_WHOLESALES_REJECTED
WITH wholesales_hist as (
SELECT *
FROM ORI_WHOLESALES_RAW
),
---------------------------------------------------------------------------------
-- 1. a) Validate ORG_NUM is a migrated store or wh
---------------------------------------------------------------------------------
error_rows_missing_location AS (
SELECT /*+ PARALLEL(24) */
'E' AS LOG_LVL, 'ERROR_MISSING_LOCATION' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM wholesales_hist s
WHERE LTRIM(s.ORG_NUM,'0') NOT IN (SELECT STORE FROM STORE_ADD_CTRL WHERE CTRL_STATUS = 'C')
),
---------------------------------------------------------------------------------
-- 2. a) Validate items exist in master data
---------------------------------------------------------------------------------
error_rows_missing_items AS (
SELECT /*+ PARALLEL(24) */
'E' AS LOG_LVL, 'ERROR_MISSING_ITEM_MASTER' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM wholesales_hist s
WHERE s.ITEM NOT IN (SELECT ITEM FROM ITEM_MASTER_CTRL WHERE ctrl_status = 'C')
),
---------------------------------------------------------------------------------
-- 3. a) Validate items exist in PR1/PV1 XREF
-- FOR IBC scenarios from PR1 at the moment to insert into ORI_RECEIPTS_RAW
-- the conversion from PR1 TO PV1 SKU is done.
---------------------------------------------------------------------------------
error_rows_missing_items_ibc_xref AS (
SELECT /*+ PARALLEL(24) */
'E' AS LOG_LVL, 'ERROR_MISSING_ITEM_IBC_XREF' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM wholesales_hist s
WHERE s.ITEM IS NULL
),
---------------------------------------------------------------------------------
-- 4. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT /*+ PARALLEL(24) */ * FROM error_rows_missing_location
union all
SELECT /*+ PARALLEL(24) */ * FROM error_rows_missing_items
union all
SELECT /*+ PARALLEL(24) */ * FROM error_rows_missing_items_ibc_xref
)
SELECT /*+ PARALLEL(24) */ * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('4. WHOLESALES - LOADING CTRL TABLE ');
END;
/
-- Rows that passed validation are inserted in ORI_RTV_CTRL
INSERT INTO /*+ PARALLEL(24) */ ORI_WHOLESALES_CTRL
WITH wholesales_hist as (
SELECT /*+ PARALLEL(24) */
*
FROM ORI_WHOLESALES_RAW
)
SELECT /*+ PARALLEL(24) */
item ,
org_num ,
day_dt ,
sum(slswf_qty) ,
sum(slswf_amt_lcl) ,
sum(slswf_tax_amt_lcl) ,
sum(slswf_acq_cost_amt_lcl) ,
sum(slswf_mkdn_qty) ,
sum(slswf_mkdn_amt_lcl) ,
sum(slswf_mkup_qty) ,
sum(slswf_mkup_amt_lcl) ,
sum(retwf_qty) ,
sum(retwf_amt_lcl) ,
sum(retwf_tax_amt_lcl) ,
sum(retwf_acq_cost_amt_lcl) ,
sum(retwf_rstk_fee_amt_lcl) ,
sum(retwf_mkdn_amt_lcl) ,
sum(retwf_mkdn_qty) ,
sum(retwf_mkup_amt_lcl) ,
sum(retwf_mkup_qty) ,
global1_exchange_rate ,
global2_exchange_rate ,
global3_exchange_rate ,
loc_curr_code ,
loc_exchange_rate ,
doc_curr_code ,
etl_thread_val ,
delete_flg ,
datasource_num_id ,
create_id ,
create_datetime ,
ctrl_status ,
ctrl_datetime ,
ctrl_error_msg
FROM wholesales_hist s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_WHOLESALES_REJECTED r
WHERE LOG_LVL = 'E'
AND NVL(s.ITEM, '-1') = NVL(r.ITEM, '-1')
AND NVL(s.ORG_NUM, '-1') = NVL(r.ORG_NUM, '-1')
)
AND ITEM IS NOT NULL
GROUP BY item, org_num, day_dt, global1_exchange_rate,global2_exchange_rate,global3_exchange_rate,
loc_curr_code,loc_exchange_rate,doc_curr_code,etl_thread_val,delete_flg,
datasource_num_id,create_id,create_datetime,ctrl_status,ctrl_datetime,ctrl_error_msg;
COMMIT;
--------------------------
select 'TOTAL OF LINES', count(1) from ORI_WHOLESALES_raw
union all
SELECT error_msg, count(1) from ORI_WHOLESALES_REJECTED group by error_msg;
select *
from sap_slt_prod_pr1.VBAK
inner join sap_slt_prod_pr1.VBAP on VBAK.VBELN = VBAP.VBELN
INNER JOIN sap_slt_prod_pr1.VBFA ON VBAP.VBELN = VBFA.VBELV AND VBAP.POSNR = VBFA.POSNV AND VBFA.BWART = '601' --and vbfa.vbtyp_n = 'R'
where VBAK.AUART = 'ZWHE' AND VBAK.KUNNR = '0000340280' and VBAK.ERDAT >= '20240101' and vbap.matnr = '000014451046000005';
select * --vbak.VBELN, vbap.matnr, vbak.kunnr, vbak.erdat
from sap_slt_prod_pv1.VBAK
inner join sap_slt_prod_pv1.VBAP on VBAK.VBELN = VBAP.VBELN
INNER JOIN sap_slt_prod_pv1.VBFA ON VBAP.VBELN = VBFA.VBELV AND VBAP.POSNR = VBFA.POSNV AND VBFA.BWART = '901' and vbfa.vbtyp_n = 'R'
where VBAK.AUART = 'ZSOA' and VBAK.ERDAT >= '20240101' and vbap.matnr = '000000136608818110'
group by vbak.VBELN, vbap.matnr, vbak.kunnr, vbak.erdat having count(1) > 1
;