Source: DMF/PuC/AIF/TSF_Hist.sql
/*
CREATE TABLE KONV_Z101 NOLOGGING AS SELECT * FROM SAP_SLT_PROD_PV1.KONV WHERE KONV.KSCHL = 'Z101';
CREATE INDEX KONV_Z101_IDX1 ON KONV_Z101 ('KSCHL','KNUMV',TO_NUMBER(KPOSN)) NOLOGGING;
CREATE TABLE KONV_ZWVH NOLOGGING AS SELECT * FROM SAP_SLT_PROD_PV1.KONV WHERE KONV.KSCHL = 'ZWVH';
CREATE INDEX KONV_ZWVH_IDX1 ON KONV_ZWVH ('KSCHL','KNUMV',TO_NUMBER(KPOSN)) NOLOGGING;
DROP TABLE ORI_TSF_HEADER_RAW;
DROP TABLE ORI_TSF_HEADER_REJECTED;
DROP TABLE ORI_TSF_DETAIL_RAW;
DROP TABLE ORI_TSF_DETAIL_REJECTED;
CREATE TABLE ORI_TSF_HEADER_RAW NOLOGGING AS
SELECT S.*,
CAST(NULL AS VARCHAR2(10)) AS LOCATION
FROM ORI_TSF_HEADER_CTRL S;
CREATE TABLE ORI_TSF_HEADER_REJECTED NOLOGGING AS
SELECT CAST(NULL AS VARCHAR2(10)) AS LOG_LVL
,CAST(NULL AS VARCHAR2(1000)) AS ERROR_MSG
,t.*
FROM ORI_TSF_HEADER_RAW t;
ALTER TABLE ORI_TSF_HEADER_REJECTED ADD INSERTED_AT DATE DEFAULT SYSDATE NOT NULL ENABLE;
CREATE TABLE ORI_TSF_DETAIL_RAW NOLOGGING AS
SELECT S.*,
CAST(NULL AS VARCHAR2(10)) AS LOCATION
FROM ORI_TSF_DETAIL_CTRL S;
CREATE TABLE ORI_TSF_DETAIL_REJECTED NOLOGGING AS
SELECT CAST(NULL AS VARCHAR2(10)) AS LOG_LVL
,CAST(NULL AS VARCHAR2(1000)) AS ERROR_MSG
,t.*
FROM ORI_TSF_DETAIL_RAW t;
ALTER TABLE ORI_TSF_DETAIL_REJECTED ADD INSERTED_AT DATE DEFAULT SYSDATE NOT NULL ENABLE;
TRUNCATE TABLE ORI_TSF_HEADER_REJECTED;
TRUNCATE TABLE ORI_TSF_DETAIL_REJECTED;
TRUNCATE TABLE ORI_TSF_HEADER_RAW;
TRUNCATE TABLE ORI_TSF_DETAIL_RAW;
TRUNCATE TABLE ORI_TSF_HEADER_CTRL;
TRUNCATE TABLE ORI_TSF_DETAIL_CTRL;
COMMIT;
-- TEMP TABLES
SELECT * FROM PUC_TSF_TMP;
SELECT * FROM V_PUC_TSF;
select * from ORI_TSF_HEADER_RAW;
select * from ORI_TSF_DETAIL_RAW;
select * from ORI_TSF_HEADER_CTRL;
select * from ORI_TSF_DETAIL_CTRL;
select * from ORI_TSF_HEADER_REJECTED;
select * from ORI_TSF_DETAIL_REJECTED;
*/
SET SERVEROUTPUT ON;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- TRANSFER HEADER RAW DATA
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('1. TRANSFER HEADER RAW DATA - LOADING');
END;
/
--TSF HEADER
INSERT INTO /*+ PARALLEL(24) */ ORI_TSF_HEADER_RAW (
TSF_NO,FROM_LOC_TYPE,FROM_LOC,EXP_DC_DATE,INVENTORY_TYPE,TSF_TYPE,STATUS,APPROVAL_DATE,CLOSE_DATE,DATASOURCE_NUM_ID,INTEGRATION_ID,CTRL_STATUS,CTRL_DATETIME,LOCATION
)
WITH PUC_TSF as (
SELECT /*+ PARALLEL(24) */
TSF_TYPE,
TSF_TYPE_SAP,
TSF,
FROM_LOC,
FROM_LOC_TYPE,
FROM_LOC_CHANNEL,
TO_LOC,
TO_LOC_TYPE,
TO_LOC_CHANNEL
FROM V_PUC_TSF
),
TSF_HEADER AS (
SELECT /*+ PARALLEL(24) */
EKKO.BEDAT, -- create date
T.*
FROM PUC_TSF T
JOIN SAP_SLT_PROD_PV1.EKKO EKKO ON EKKO.EBELN = T.TSF AND EKKO.BEDAT >= 20240101
),
TSF_DETAIL AS (
SELECT /*+ PARALLEL(24) */
TSF_TYPE,
TSF_TYPE_SAP,
TSF,
FROM_LOC,
FROM_LOC_TYPE,
FROM_LOC_CHANNEL,
TO_LOC,
TO_LOC_TYPE,
TO_LOC_CHANNEL,
BEDAT, -- creation date
MIN(STATUS) STATUS,
MAX(AEDAT) AEDAT -- update date
FROM (
SELECT /*+ PARALLEL(24) */
T.*,
CASE
WHEN EKPO.ELIKZ = 'X' THEN 'C'
WHEN EKPO.LOEKZ = 'L' THEN 'C'
WHEN EKPO.MENGE = EKET.WEMNG THEN 'C'
ELSE 'A'
END STATUS,
AEDAT
FROM TSF_HEADER t,
SAP_SLT_PROD_PV1.EKPO
LEFT JOIN SAP_SLT_PROD_PV1.EKET EKET on EKET.EBELN = EKPO.EBELN AND EKET.EBELP = EKPO.EBELP
WHERE 1=1
AND EKPO.ebeln = t.tsf
AND EKPO.UEBPO = '00000'
) t group by TSF_TYPE, TSF_TYPE_SAP, TSF, FROM_LOC, FROM_LOC_TYPE, FROM_LOC_CHANNEL, TO_LOC, TO_LOC_TYPE, TO_LOC_CHANNEL, BEDAT )
SELECT
--TSF AS TSF_NO,
CASE
WHEN TSF_TYPE = 'BT' THEN
99 || TSF
ELSE
TSF
END AS TSF_NO,
FROM_LOC_TYPE AS FROM_LOC_TYPE,
FROM_LOC AS FROM_LOC,
TO_DATE(BEDAT,'YYYYMMDD')+7 AS EXP_DC_DATE,
'A' AS INVENTORY_TYPE,
CASE
WHEN TSF_TYPE = 'BT' THEN
'BT'
WHEN INSTR(TSF_TYPE, 'RTV') > 0 THEN
'RV'
WHEN FROM_LOC_CHANNEL <> TO_LOC_CHANNEL THEN
'IC'
ELSE
'MR'
END AS TSF_TYPE,
STATUS AS STATUS,
TO_DATE(BEDAT,'YYYYMMDD') AS APPROVAL_DATE,
DECODE(STATUS,'C', TO_DATE(AEDAT,'YYYYMMDD'),NULL) AS CLOSE_DATE,
'1' AS DATASOURCE_NUM_ID,
TSF AS INTEGRATION_ID,
'I' AS CTRL_STATUS,
SYSDATE AS CTRL_DATETIME,
FROM_LOC AS LOCATION
FROM TSF_DETAIL s
;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- TRANSFER DETAIL RAW DATA
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('2. TRANSFER DETAIL RAW DATA - LOADING');
END;
/
--TSF DETAIL
INSERT INTO /*+ PARALLEL(24) */ ORI_TSF_DETAIL_RAW (
TSF_NO,PROD_IT_NUM,ORG_NUM,DAY_DT,SEQ_NO,ORDER_NO,INV_STATUS,IC_UNIT_COST_AMT_LCL,TSF_AVG_COST_AMT_LCL,TSF_QTY,SHIP_QTY,RECEIVED_QTY,CANCELLED_QTY,
UNIT_COST_AMT_LCL,DATASOURCE_NUM_ID,DOC_CURR_CODE,INTEGRATION_ID,LOC_CURR_CODE,CTRL_STATUS,CTRL_DATETIME,LOCATION
)
WITH PUC_TSF as (
SELECT /*+ PARALLEL(24) */
TSF_TYPE,
TSF_TYPE_SAP,
TSF,
FROM_LOC,
TO_LOC
FROM V_PUC_TSF
),
TSF_HEADER AS (
SELECT /*+ PARALLEL(24) */
T.*,
EKKO.KNUMV,
EKKO.WAERS,
EKKO.BEDAT
FROM PUC_TSF T
JOIN SAP_SLT_PROD_PV1.EKKO EKKO ON EKKO.EBELN = T.TSF AND EKKO.BEDAT >= 20240101
),
TSF_DETAIL AS (
SELECT /*+ PARALLEL(24) */
TSF_TYPE,
TSF_TYPE_SAP,
TSF,
WAERS,
BEDAT,
FROM_LOC,
TO_LOC,
MATNR,
EBELP,
GLD,
SELLING_PRICE,
NETPR,
SUM(tsf_qty) as tsf_qty,
SUM(cancel_qty) as cancel_qty,
SUM(ship_qty) as ship_qty ,
SUM(received_qty) as received_qty
FROM (
SELECT /*+ PARALLEL(24) */
T.*,
EKPO.MATNR,
EKPO.EBELP,
EKPO.NETPR,
decode(EKPO.LOEKZ,'L',0,EKPO.MENGE) tsf_qty,
decode(EKPO.LOEKZ,'L',EKPO.MENGE,0) cancel_qty,
nvl(SHIP.LFIMG,0) ship_qty,
EKET.WEMNG as received_qty,
KONV_Z101.KBETR AS GLD,
KONV_ZWVH.KBETR AS SELLING_PRICE
FROM TSF_HEADER t
JOIN SAP_SLT_PROD_PV1.EKPO EKPO ON EKPO.EBELN = t.TSF AND EKPO.UEBPO = '00000'
LEFT JOIN SAP_SLT_PROD_PV1.EKET EKET on EKET.EBELN = EKPO.EBELN AND EKET.EBELP = EKPO.EBELP
LEFT JOIN (SELECT LIPS.VGBEL,LIPS.VGPOS,MATNR,LIPS.WERKS,SUM(LIPS.LFIMG) AS LFIMG
FROM SAP_SLT_PROD_PV1.LIPS LIPS
JOIN SAP_SLT_PROD_PV1.LIKP LIKP on LIKP.VBELN = LIPS.VBELN
GROUP BY LIPS.VGBEL,LIPS.VGPOS,MATNR,LIPS.WERKS) SHIP on SHIP.VGBEL = EKPO.EBELN AND TO_NUMBER(SHIP.VGPOS) = TO_NUMBER(EKPO.EBELP) AND ekpo.WERKS = SHIP.WERKS
LEFT JOIN KONV_Z101 KONV_Z101 on KONV_Z101.KNUMV = T.KNUMV AND TO_NUMBER(KONV_Z101.KPOSN) = TO_NUMBER(EKPO.EBELP)
LEFT JOIN KONV_ZWVH KONV_ZWVH on KONV_ZWVH.KNUMV = T.KNUMV AND TO_NUMBER(KONV_ZWVH.KPOSN) = TO_NUMBER(EKPO.EBELP)
) t group by TSF_TYPE, TSF_TYPE_SAP,TSF,WAERS,BEDAT,FROM_LOC,TO_LOC,KNUMV,MATNR,EBELP,GLD,SELLING_PRICE,NETPR)
SELECT /*+ PARALLEL(24) */
--*
--TSF AS TSF_NO,
CASE
WHEN TSF_TYPE = 'BT' THEN
99 || TSF
ELSE
TSF
END AS TSF_NO
,MATNR AS PROD_IT_NUM
,TO_LOC AS ORG_NUM
,TO_DATE(BEDAT,'YYYYMMDD') AS DAY_DT
,TO_NUMBER(EBELP) AS SEQ_NO
,'-1' AS ORDER_NO
,'-1' AS INV_STATUS
,NULL AS IC_UNIT_COST_AMT_LCL
,NVL(GLD,SELLING_PRICE) AS TSF_AVG_COST_AMT_LCL
,tsf_qty AS TSF_QTY
,ship_qty AS SHIP_QTY
,received_qty AS RECEIVED_QTY
,cancel_qty AS CANCELLED_QTY
,NETPR AS UNIT_COST_AMT_LCL
,'1' AS DATASOURCE_NUM_ID
,WAERS AS DOC_CURR_CODE
,TSF || '~' || MATNR || '~' || BEDAT AS INTEGRATION_ID
,'EUR' AS LOC_CURR_CODE
,'I' AS CTRL_STATUS
,SYSDATE AS CTRL_DATETIME
,TO_LOC AS LOCATION
FROM TSF_DETAIL s
;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('3. STARTING TRANSFER HEADER VALIDATIONS - REJECTED LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_TSF_HEADER_REJECTED
WITH tsf_hist as (
SELECT *
FROM ORI_TSF_HEADER_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_FROM_LOCATION' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM tsf_hist s
WHERE s.FROM_LOC IS NULL
),
---------------------------------------------------------------------------------
-- 2. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT /*+ PARALLEL(24) */ * FROM error_rows_missing_location
)
SELECT /*+ PARALLEL(24) */ * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('4. STARTING TRANSFER DETAIL VALIDATIONS - REJECTED LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_TSF_DETAIL_REJECTED
WITH tsf_hist as (
SELECT *
FROM ORI_TSF_DETAIL_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_TO_LOCATION' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM tsf_hist s
WHERE s.ORG_NUM IS NULL
),
---------------------------------------------------------------------------------
-- 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 tsf_hist s
WHERE s.PROD_IT_NUM NOT IN (SELECT ITEM FROM dmf.ITEM_MASTER_CTRL WHERE ctrl_status = 'C')
),
---------------------------------------------------------------------------------
-- 3. 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
)
SELECT /*+ PARALLEL(24) */ * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('5. TRANSFER HEADER - LOADING CTRL TABLE ');
END;
/
-- Rows that passed validation are inserted in ORI_TSF_HEADER_CTRL
INSERT INTO /*+ PARALLEL(24) */ ORI_TSF_HEADER_CTRL
WITH tsf_hist as (
SELECT /*+ PARALLEL(24) */
*
FROM ORI_TSF_HEADER_RAW
)
SELECT /*+ PARALLEL(24) */
TSF_NO,
PARENT_TSF_NO,
FROM_LOC_TYPE,
LOCATION AS FROM_LOC,
EXP_DC_DATE,INVENTORY_TYPE,TSF_TYPE,STATUS,FREIGHT_CODE,ROUTING_CODE,CREATE_ID,APPROVAL_DATE,APPROVAL_ID,
DELIVERY_DATE,CLOSE_DATE,EXT_REF_NO,REPL_TSF_APPROVE_IND,COMMENT_DESC,EXP_DC_EOW_DATE,MRT_NO,NOT_AFTER_DATE,
CONTEXT_TYPE,CONTEXT_VALUE,RESTOCK_PCT,WF_NEED_DATE,DELIVERY_SLOT_ID,WF_ORDER_NO,RMA_NO,CREATED_BY_ID,
CHANGED_BY_ID,CREATED_ON_DT,CHANGED_ON_DT,AUX1_CHANGED_ON_DT,AUX2_CHANGED_ON_DT,AUX3_CHANGED_ON_DT,
AUX4_CHANGED_ON_DT,SRC_EFF_FROM_DT,SRC_EFF_TO_DT,DELETE_FLG,DATASOURCE_NUM_ID,INTEGRATION_ID,TENANT_ID,
X_CUSTOM,CTRL_CREATE_ID,CTRL_CREATE_DATETIME,CTRL_STATUS,CTRL_DATETIME,CTRL_ERROR_MSG
FROM tsf_hist s
WHERE (NOT EXISTS (
SELECT 1
FROM ORI_TSF_HEADER_REJECTED r
WHERE LOG_LVL = 'E'
AND NVL(s.TSF_NO, '-1') = NVL(r.TSF_NO, '-1')
)
AND
NOT EXISTS (
SELECT 1
FROM ORI_TSF_DETAIL_REJECTED r
WHERE LOG_LVL = 'E'
AND NVL(s.TSF_NO, '-1') = NVL(r.TSF_NO, '-1')
))
AND FROM_LOC_TYPE IS NOT NULL;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('6. TRANSFER DETAIL - LOADING CTRL TABLE ');
END;
/
-- Rows that passed validation are inserted in ORI_TSF_DETAIL_CTRL
INSERT INTO /*+ PARALLEL(24) */ ORI_TSF_DETAIL_CTRL
WITH tsf_hist as (
SELECT /*+ PARALLEL(24) */
*
FROM ORI_TSF_DETAIL_RAW
)
SELECT /*+ PARALLEL(24) */
TSF_NO,
PROD_IT_NUM,
LOCATION AS ORG_NUM,
DAY_DT,SEQ_NO,ORDER_NO,INV_STATUS,IC_UNIT_COST_AMT_LCL,TSF_AVG_COST_AMT_LCL,TSF_QTY,FILL_QTY,SHIP_QTY,RECEIVED_QTY,
RECONCILED_QTY,DISTRO_QTY,SELECTED_QTY,CANCELLED_QTY,SUPP_PACK_SIZE,TSF_PO_LINK_NO,DEFAULT_CHRGS_2_LEG_IND,MBR_PROCESSED_IND,
UNIT_COST_AMT_LCL,RESTOCK_PCT,FINISHER_AVG_RTL_AMT_LCL,FINISHER_UNITS,EXCHANGE_DT,AUX1_CHANGED_ON_DT,AUX2_CHANGED_ON_DT,
AUX3_CHANGED_ON_DT,AUX4_CHANGED_ON_DT,CHANGED_BY_ID,CHANGED_ON_DT,CREATED_BY_ID,CREATED_ON_DT,DATASOURCE_NUM_ID,DELETE_FLG,
DOC_CURR_CODE,ETL_THREAD_VAL,GLOBAL1_EXCHANGE_RATE,GLOBAL2_EXCHANGE_RATE,GLOBAL3_EXCHANGE_RATE,INTEGRATION_ID,LOC_CURR_CODE,
LOC_EXCHANGE_RATE,TENANT_ID,X_CUSTOM,CREATE_ID,CREATE_DATETIME,CTRL_STATUS,CTRL_DATETIME,CTRL_ERROR_MSG
FROM tsf_hist s
WHERE (NOT EXISTS (
SELECT 1
FROM ORI_TSF_HEADER_REJECTED r
WHERE LOG_LVL = 'E'
AND NVL(s.TSF_NO, '-1') = NVL(r.TSF_NO, '-1')
)
AND
NOT EXISTS (
SELECT 1
FROM ORI_TSF_DETAIL_REJECTED r
WHERE LOG_LVL = 'E'
AND NVL(s.TSF_NO, '-1') = NVL(r.TSF_NO, '-1')
));
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- REJECTION SUMMARY
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Retrieve a summary by error type
select 'TOTAL OF LINES', count(1) from ORI_tsf_header_raw
union all
SELECT error_msg, count(1) from ORI_tsf_header_rejected group by error_msg;
select 'TOTAL OF LINES', count(1) from ORI_tsf_detail_raw
union all
SELECT error_msg, count(1) from ORI_tsf_detail_rejected group by error_msg;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- VALIDATION FINAL CTRL TABLE SUMMARY
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- VALIDATE DUPLICATES
select tsf_no from ORI_tsf_header_ctrl group by tsf_no having count(1) > 1;
select tsf_no, PROD_IT_NUM, seq_no from ORI_tsf_detail_ctrl group by tsf_no, PROD_IT_NUM, seq_no having count(1) > 1;
-- File Format
SELECT
TSF_NO
,FROM_LOC_TYPE
,FROM_LOC
,TO_CHAR(EXP_DC_DATE, 'YYYYMMDD') as EXP_DC_DATE
,INVENTORY_TYPE
,TSF_TYPE
,STATUS
,TO_CHAR(APPROVAL_DATE, 'YYYYMMDD') as APPROVAL_DATE
,TO_CHAR(CLOSE_DATE, 'YYYYMMDD') as CLOSE_DATE
,DATASOURCE_NUM_ID
,INTEGRATION_ID
FROM
ORI_TSF_HEADER_CTRL
;
SELECT
TSF_NO
,PROD_IT_NUM
,ORG_NUM
,TO_CHAR(DAY_DT, 'YYYYMMDD') AS DAY_DT
,SEQ_NO
,ORDER_NO
,INV_STATUS
,IC_UNIT_COST_AMT_LCL
,TSF_AVG_COST_AMT_LCL
,TSF_QTY
,SHIP_QTY
,RECEIVED_QTY
,CANCELLED_QTY
,UNIT_COST_AMT_LCL
,DATASOURCE_NUM_ID
,DOC_CURR_CODE
,INTEGRATION_ID
,LOC_CURR_CODE
FROM
ORI_TSF_DETAIL_CTRL
;
select * from ORI_TSF_DETAIL_CTRL where TSF_NO > 990000000000;
select * from ORI_TSF_header_CTRL