Source: DMF/PuC/AIF/BKP/RTV_Hist.sql
/*
DROP TABLE ORI_RTV_RAW;
DROP TABLE ORI_RTV_REJECTED;
CREATE TABLE ORI_RTV_RAW NOLOGGING AS
SELECT S.*,
CAST(NULL AS VARCHAR2(10)) AS ORDER_ID,
CAST(NULL AS VARCHAR2(20)) AS ORDER_TYPE,
CAST(NULL AS VARCHAR2(18)) AS ITEM_PR1,
CAST(NULL AS VARCHAR2(10)) AS SUPPLIER
FROM ORI_RTV_STG S;
CREATE TABLE ORI_RTV_REJECTED NOLOGGING AS
SELECT CAST(NULL AS VARCHAR2(10)) AS LOG_LVL
,CAST(NULL AS VARCHAR2(1000)) AS ERROR_MSG
,t.*
FROM ORI_RTV_RAW t;
ALTER TABLE ORI_RTV_REJECTED ADD INSERTED_AT DATE DEFAULT SYSDATE NOT NULL ENABLE;
TRUNCATE TABLE ORI_RTV_REJECTED;
TRUNCATE TABLE ORI_RTV_RAW;
TRUNCATE TABLE ORI_RTV_CTRL;
COMMIT;
*/
SET SERVEROUTPUT ON;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- IBC RTVs - PR1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('1. IBC RTVs - LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_RTV_RAW (
ITEM, ORG_NUM, DAY_DT, SUPPLIER_NUM, REASON_CODE, INV_STATUS, RTV_QTY, RTV_COST_AMT_LCL, LOC_CURR_CODE, DOC_CURR_CODE, DATASOURCE_NUM_ID, ORDER_ID , ORDER_TYPE, ITEM_PR1, SUPPLIER)
WITH IBC_RTV_HEADER AS (
SELECT /*+ PARALLEL(24) */
EKKO.BSART,
EKKO.EBELN,
EKKO.LIFNR,
EKKO.WAERS
FROM SAP_SLT_PROD_PR1.EKKO EKKO
WHERE EKKO.BSART in ('ZGLR')
AND EKKO.BEDAT >= '20240101'),
IBC_RTV_DETAIL AS (
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.*,
--EKPO.WERKS,
EKPO.EBELP,
EKPO.NETPR,
EKPO.BSGRU
FROM IBC_RTV_HEADER EKKO
JOIN (SELECT EKPO.* --DISTINCT EKPO.EBELN, EKPO.WERKS, EKPO.UEBPO, EKPO.LOEKZ, EKPO.EBELP
FROM SAP_SLT_PROD_PR1.EKPO EKPO
WHERE EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' ') EKPO on EKKO.EBELN = EKPO.EBELN ),
IBC_RTV_SHIPMENT_DETAIL AS (
SELECT /*+ PARALLEL(24) */
DISTINCT RTV.*,
LIPS.VBELN,
LIPS.VGBEL,
LIPS.WERKS,
LIPS.MATNR,
LIPS.LFIMG
FROM IBC_RTV_DETAIL RTV
JOIN SAP_SLT_PROD_PR1.LIPS LIPS on LIPS.VGBEL = RTV.EBELN AND TO_NUMBER(LIPS.VGPOS) = TO_NUMBER(RTV.EBELP)),
IBC_RTV_SHIPMENT_HEADER AS (
SELECT /*+ PARALLEL(24) */
DISTINCT --RTV.*,
--RTV.WERKS,
RTV_SHIP.*,
LIKP.KUNNR,
LIKP.WADAT_IST,
LIKP.LFDAT
FROM IBC_RTV_SHIPMENT_DETAIL RTV_SHIP
JOIN SAP_SLT_PROD_PR1.LIKP LIKP on LIKP.VBELN = RTV_SHIP.VBELN)
SELECT /*+ PARALLEL(i 24) */
--RTV.BSART RTV_TYPE,
--RTV.EBELN RTV_ID,
XREF_ITEM.ITEM_PV1 ITEM,
RTV.WERKS ORG_NUM,
TO_DATE(RTV.LFDAT,'YYYYMMDD') DAY_DT,
RTV.LIFNR SUPPLIER_NUM,
RTV.BSGRU REASON_CODE,
'-1' INV_STATUS,
RTV.LFIMG RTV_QTY,
RTV.NETPR * RTV.LFIMG RTV_COST_AMT_LCL,
'EUR' LOC_CURR_CODE,
RTV.WAERS DOC_CURR_CODE,
1 DATASOURCE_NUM_ID,
EBELN ORDER_ID,
'IBC_RTV' ORDER_TYPE,
RTV.MATNR ITEM_PR1,
XREF_SUPS.ORACLE_ID SUPPLIER
FROM IBC_RTV_SHIPMENT_HEADER RTV,
XREF_ITEM_IBC_RETAIL XREF_ITEM,
XREF_SUPS XREF_SUPS
WHERE RTV.MATNR = XREF_ITEM.ITEM_PR1(+)
AND RTV.LIFNR = XREF_SUPS.SAP_ID(+)
;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 3rd PARTY - EXCLUSIVE BRANDS - RTVs - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('2. 3rd PARTY - EXCLUSIVE BRANDS - RTVs - LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_RTV_RAW (
ITEM, ORG_NUM, DAY_DT, SUPPLIER_NUM, REASON_CODE, INV_STATUS, RTV_QTY, RTV_COST_AMT_LCL, LOC_CURR_CODE, DOC_CURR_CODE, DATASOURCE_NUM_ID, ORDER_ID , ORDER_TYPE, ITEM_PR1, SUPPLIER)
WITH EXCL_RTV_HEADER AS (
SELECT /*+ PARALLEL(24) */
EKKO.BSART,
EKKO.EBELN,
EKKO.LIFNR,
EKKO.WAERS
FROM SAP_SLT_PROD_PV1.EKKO EKKO
WHERE EKKO.BSART in ('ZLK')
AND EKKO.BEDAT >= '20240101'
AND EKKO.LIFNR <> '0000940506'),
EXCL_RTV_DETAIL AS (
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.*,
--EKPO.WERKS,
EKPO.EBELP,
EKPO.NETPR,
EKPO.BSGRU
FROM EXCL_RTV_HEADER EKKO
JOIN (SELECT EKPO.* --DISTINCT EKPO.EBELN, EKPO.WERKS, EKPO.UEBPO, EKPO.LOEKZ, EKPO.EBELP
FROM SAP_SLT_PROD_PV1.EKPO EKPO
WHERE EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' ') EKPO on EKKO.EBELN = EKPO.EBELN ),
EXCL_RTV_SHIPMENT_DETAIL AS (
SELECT /*+ PARALLEL(24) */
DISTINCT RTV.*,
LIPS.VBELN,
LIPS.VGBEL,
LIPS.WERKS,
LIPS.MATNR,
LIPS.LFIMG
FROM EXCL_RTV_DETAIL RTV
JOIN SAP_SLT_PROD_PV1.LIPS LIPS on LIPS.VGBEL = RTV.EBELN AND to_number(LIPS.VGPOS) = to_number(RTV.EBELP)),
EXCL_RTV_SHIPMENT_HEADER AS (
SELECT /*+ PARALLEL(24) */
DISTINCT --RTV.*,
--RTV.WERKS,
RTV_SHIP.*,
LIKP.KUNNR,
LIKP.WADAT_IST,
LIKP.LFDAT
FROM EXCL_RTV_SHIPMENT_DETAIL RTV_SHIP
JOIN SAP_SLT_PROD_PV1.LIKP LIKP on LIKP.VBELN = RTV_SHIP.VBELN)
SELECT /*+ PARALLEL(i 24) */
--RTV.BSART RTV_TYPE,
--RTV.EBELN RTV_ID,
RTV.MATNR ITEM,
RTV.WERKS ORG_NUM,
TO_DATE(LFDAT,'YYYYMMDD') DAY_DT,
RTV.LIFNR SUPPLIER_NUM,
RTV.BSGRU REASON_CODE,
'-1' INV_STATUS,
RTV.LFIMG RTV_QTY,
RTV.NETPR * RTV.LFIMG RTV_COST_AMT_LCL,
'EUR' LOC_CURR_CODE,
RTV.WAERS DOC_CURR_CODE,
1 DATASOURCE_NUM_ID,
EBELN ORDER_ID,
'EXCL_RTV' ORDER_TYPE,
NULL ITEM_PR1,
XREF_SUPS.ORACLE_ID SUPPLIER
FROM EXCL_RTV_SHIPMENT_HEADER RTV,
XREF_SUPS XREF_SUPS
WHERE RTV.LIFNR = XREF_SUPS.SAP_ID(+);
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),
tabname => 'ORI_RTV_RAW',
cascade => TRUE);
END;
/
BEGIN
DBMS_OUTPUT.PUT_LINE('3. STARTING RTV VALIDATIONS - REJECTED LOADING');
END;
/
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_RTV_REJECTED
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO /*+ PARALLEL(24) */ ORI_RTV_REJECTED
WITH rtv_hist as (
SELECT *
FROM ORI_RTV_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 rtv_hist s
WHERE s.ORG_NUM NOT IN (SELECT WH_SLT FROM XREF_WH)
),
---------------------------------------------------------------------------------
-- 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 rtv_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 rtv_hist s
WHERE s.ITEM IS NULL
),
---------------------------------------------------------------------------------
-- 4. a) Validate supplier exist in XREF
---------------------------------------------------------------------------------
error_rows_missing_supplier AS (
SELECT /*+ PARALLEL(24) */
'E' AS LOG_LVL, 'ERROR_MISSING_SUPPLIER' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM rtv_hist s
WHERE s.SUPPLIER IS NULL
),
---------------------------------------------------------------------------------
-- 5. 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
union all
SELECT /*+ PARALLEL(24) */ * FROM error_rows_missing_supplier
)
SELECT /*+ PARALLEL(24) */ * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('4. RTV - LOADING CTRL TABLE ');
END;
/
-- Rows that passed validation are inserted in ORI_RTV_CTRL
INSERT INTO /*+ PARALLEL(24) */ ORI_RTV_CTRL
WITH rtv_hist as (
SELECT /*+ PARALLEL(24) */
*
FROM ORI_RTV_RAW
)
SELECT /*+ PARALLEL(24) */
s.ITEM,
xref.v_wh AS ORG_NUM,
s.DAY_DT ,
s.SUPPLIER AS SUPPLIER_NUM,
s.REASON_CODE,
s.INV_STATUS,
SUM(s.RTV_QTY),
SUM(s.RTV_COST_AMT_LCL),
SUM(s.RTV_RTL_AMT_LCL),
SUM(s.RTV_CAN_QTY),
SUM(s.RTV_CAN_COST_AMT_LCL),
SUM(s.RTV_CAN_RTL_AMT_LCL),
s.CLEARANCE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE,
s.LOC_EXCHANGE_RATE, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.CREATE_ID, s.CREATE_DATETIME,
'I', SYSDATE, NULL
FROM rtv_hist s
JOIN XREF_WH xref on xref.wh_slt = s.org_num
WHERE NOT EXISTS (
SELECT 1
FROM ORI_RTV_REJECTED r
WHERE LOG_LVL = 'E'
AND NVL(s.ORDER_ID, '-1') = NVL(r.ORDER_ID, '-1')
--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
AND SUPPLIER IS NOT NULL
GROUP BY s.ITEM, xref.v_wh, s.DAY_DT, s.SUPPLIER, s.REASON_CODE, s.INV_STATUS, s.CLEARANCE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE,
s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID,
s.CREATE_ID, s.CREATE_DATETIME, s.CTRL_STATUS;
COMMIT;
--------------------
-- File Format
-- 33.541
SELECT
ITEM,
ORG_NUM,
TO_CHAR(DAY_DT, 'YYYYMMDD') AS DAY_DT,
SUPPLIER_NUM,
REASON_CODE,
INV_STATUS,
RTV_QTY,
RTV_COST_AMT_LCL,
RTV_RTL_AMT_LCL,
LOC_CURR_CODE,
DOC_CURR_CODE,
DATASOURCE_NUM_ID
FROM
ORI_RTV_CTRL
;
update ORI_RTV_CTRL set REASON_CODE = '099' where REASON_CODE = '053';
select 'TOTAL OF LINES', count(1) from ORI_RTV_raw
union all
SELECT error_msg, count(1) from ORI_RTV_REJECTED group by error_msg;
select distinct order_id from ORI_RTV_RAW where order_type = 'IBC_RTV'