Source: DMF/PuC/AIF/Receipt_Hist2.sql
/*
DROP TABLE ORI_RECEIPTS_RAW;
DROP TABLE ORI_RECEIPTS_REJECTED;
CREATE TABLE ORI_RECEIPTS_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
FROM ORI_RECEIPTS_STG S;
CREATE TABLE ORI_RECEIPTS_REJECTED NOLOGGING AS
SELECT CAST(NULL AS VARCHAR2(10)) AS LOG_LVL
,CAST(NULL AS VARCHAR2(1000)) AS ERROR_MSG
,t.*
FROM ORI_RECEIPTS_RAW t;
ALTER TABLE ORI_RECEIPTS_REJECTED ADD UPDATED_AT DATE;
ALTER TABLE ORI_RECEIPTS_REJECTED ADD INSERTED_AT DATE DEFAULT SYSDATE NOT NULL ENABLE;
TRUNCATE TABLE ORI_RECEIPTS_REJECTED;
TRUNCATE TABLE ORI_RECEIPTS_RAW;
TRUNCATE TABLE ORI_RECEIPTS_CTRL;
COMMIT;
*/
SET SERVEROUTPUT ON;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- IBC PURCHASE ORDERS FOR REPLENISHMENT - PR1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('1. IBC PURCHASE ORDERS FOR REPLENISHMENT - LOADING');
END;
/
TRUNCATE TABLE ORI_RECEIPTS_RAW;
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_RAW (
ITEM, ORG_NUM, DAY_DT, INVRC_TYPE_CODE, INVRC_QTY, INVRC_COST_AMT_LCL, LOC_CURR_CODE, DOC_CURR_CODE, DATASOURCE_NUM_ID, ORDER_ID , ORDER_TYPE, ITEM_PR1)
WITH PUC_IBC_PO_REPL as (
SELECT DISTINCT IBC_PO,IBC_ORACLE_WH
FROM PUC_IBC_PO_REPL_TMP where IBC_ORACLE_WH is not null),
RECEIPTS_IBC_PO_REPL as (
SELECT
XREF_ITEM.ITEM_PV1 AS ITEM,
--EKPO.WERKS AS ORG_NUM,
IBC_ORACLE_WH AS ORG_NUM,
TO_DATE(EKET.EINDT,'YYYYMMDD') AS DAY_DT,
'20' AS INVRC_TYPE_CODE,
EKET.WEMNG AS INVRC_QTY,
EKPO.NETPR*EKET.WEMNG AS INVRC_COST_AMT_LCL,
'EUR' AS LOC_CURR_CODE,
EKKO.WAERS AS DOC_CURR_CODE,
1 AS DATASOURCE_NUM_ID,
EKKO.EBELN AS ORDER_ID,
'IBC_PO_REPL' AS ORDR_TYPE,
EKPO.MATNR AS ITEM_PR1
FROM
sap_slt_prod_pr1.EKKO EKKO,
sap_slt_prod_pr1.EKPO EKPO,
sap_slt_prod_pr1.EKET EKET,
PUC_IBC_PO_REPL IBC_PO_REPL,
XREF_ITEM_IBC_RETAIL XREF_ITEM
where 1=1
-- JOIN PO HEADER AND DETAIL
AND EKKO.EBELN = EKPO.EBELN
-- MAIN ITEM AND NOT CANCELLED
AND EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' '
-- JOIN PO RECEIPT
and EKET.EBELN = EKPO.EBELN
AND EKET.EBELP = EKPO.EBELP
-- FILTER RECEIVED QTY > 0
AND EKET.WEMNG > 0
-- FILTER THE LIST OF PO/LOCATION FROM TEMP TABLE
AND EKKO.EBELN = IBC_PO_REPL.IBC_PO
--AND EKPO.WERKS = IBC_PO_REPL.IBC_WH
-- GET ITEM PV1 FROM XREF ITEM
AND EKPO.MATNR = XREF_ITEM.ITEM_PR1(+)
)
SELECT S.*
FROM RECEIPTS_IBC_PO_REPL s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- IBC PURCHASE ORDERS FOR X-DOCKING - PR1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('2. IBC PURCHASE ORDERS FOR X-DOCKING - LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_RAW (
ITEM, ORG_NUM, DAY_DT, INVRC_TYPE_CODE, INVRC_QTY, INVRC_COST_AMT_LCL, LOC_CURR_CODE, DOC_CURR_CODE, DATASOURCE_NUM_ID, ORDER_ID , ORDER_TYPE, ITEM_PR1)
WITH PUC_IBC_PO_XDOC as (
SELECT DISTINCT IBC_PO,IBC_ORACLE_WH --, IBC_WH
FROM PUC_IBC_PO_XDOC_STORE_TMP where IBC_PO is not null and IBC_ORACLE_WH is not null AND ALLOC_1 IS NOT NULL AND ALLOC_2 IS NOT NULL),
RECEIPTS_IBC_PO_XDOC as (
SELECT
XREF_ITEM.ITEM_PV1 AS ITEM,
--EKPO.WERKS AS ORG_NUM,
IBC_ORACLE_WH AS ORG_NUM,
TO_DATE(EKET.EINDT,'YYYYMMDD') AS DAY_DT, -- EKBE.BUDAT
'20' AS INVRC_TYPE_CODE,
EKET.WEMNG AS INVRC_QTY,
EKPO.NETPR*EKET.WEMNG AS INVRC_COST_AMT_LCL,
'EUR' AS LOC_CURR_CODE,
EKKO.WAERS AS DOC_CURR_CODE,
1 AS DATASOURCE_NUM_ID,
EKKO.EBELN AS ORDER_ID,
'IBC_PO_REPL' AS ORDR_TYPE,
EKPO.MATNR AS ITEM_PR1
FROM
sap_slt_prod_pr1.EKKO EKKO,
sap_slt_prod_pr1.EKPO EKPO,
sap_slt_prod_pr1.EKET EKET,
PUC_IBC_PO_XDOC IBC_PO_XDOC,
XREF_ITEM_IBC_RETAIL XREF_ITEM
where 1=1
-- JOIN PO HEADER AND DETAIL
AND EKKO.EBELN = EKPO.EBELN
-- MAIN ITEM AND NOT CANCELLED
AND EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' '
-- JOIN PO RECEIPT
and EKET.EBELN = EKPO.EBELN
AND EKET.EBELP = EKPO.EBELP
-- FILTER RECEIVED QTY > 0
AND EKET.WEMNG > 0
-- FILTER THE LIST OF PO/LOCATION FROM TEMP TABLE
AND EKKO.EBELN = IBC_PO_XDOC.IBC_PO
--AND EKPO.WERKS = IBC_PO_XDOC.IBC_WH
-- GET ITEM PV1 FROM XREF ITEM
AND EKPO.MATNR = XREF_ITEM.ITEM_PR1(+)
)
SELECT S.*
FROM RECEIPTS_IBC_PO_XDOC s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- IBC REPLNISHMENT ALLOCATION - LEG 1 - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('3. IBC REPLNISHMENT ALLOCATION - LEG 1 - LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_RAW (
ITEM, ORG_NUM, DAY_DT, INVRC_TYPE_CODE, INVRC_QTY, INVRC_COST_AMT_LCL, LOC_CURR_CODE, DOC_CURR_CODE, DATASOURCE_NUM_ID, ORDER_ID , ORDER_TYPE)
WITH PUC_IBC_ALLOC_REPL as (
SELECT DISTINCT ALLOC_1,BUYING_ORACLE_WH
FROM PUC_IBC_REPL_STORE_TMP where ALLOC_1 is not null and BUYING_ORACLE_WH is not null AND ALLOC_2 IS NOT NULL ),
RECEIPTS_IBC_ALLOC_REPL_LEG1 as (
SELECT
EKPO.MATNR AS ITEM,
--EKPO.WERKS AS ORG_NUM,
BUYING_ORACLE_WH AS ORG_NUM,
TO_DATE(EKET.EINDT,'YYYYMMDD') AS DAY_DT,
'44~A' AS INVRC_TYPE_CODE,
EKET.WEMNG AS INVRC_QTY,
EKPO.NETPR*EKET.WEMNG AS INVRC_COST_AMT_LCL,
'EUR' AS LOC_CURR_CODE,
EKKO.WAERS AS DOC_CURR_CODE,
1 AS DATASOURCE_NUM_ID,
EKKO.EBELN AS ORDER_ID,
'IBC_ALLOC_REPL_LEG1' AS ORDR_TYPE
FROM
sap_slt_prod_pv1.EKKO EKKO,
sap_slt_prod_pv1.EKPO EKPO,
sap_slt_prod_pv1.EKET EKET,
PUC_IBC_ALLOC_REPL IBC_ALLOC_REPL
where 1=1
-- JOIN PO HEADER AND DETAIL
AND EKKO.EBELN = EKPO.EBELN
-- MAIN ITEM AND NOT CANCELLED
AND EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' '
-- JOIN PO RECEIPT
and EKET.EBELN = EKPO.EBELN
AND EKET.EBELP = EKPO.EBELP
-- FILTER RECEIVED QTY > 0
AND EKET.WEMNG > 0
-- FILTER THE LIST OF PO/LOCATION FROM TEMP TABLE
AND EKKO.EBELN = IBC_ALLOC_REPL.ALLOC_1
--AND EKPO.WERKS = IBC_ALLOC_REPL.BUYING_WH
)
SELECT s.*
FROM RECEIPTS_IBC_ALLOC_REPL_LEG1 s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- IBC REPLNISHMENT ALLOCATION - LEG 2 - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('4. IBC REPLNISHMENT ALLOCATION - LEG 2 - LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_RAW (
ITEM, ORG_NUM, DAY_DT, INVRC_TYPE_CODE, INVRC_QTY, INVRC_COST_AMT_LCL, LOC_CURR_CODE, DOC_CURR_CODE, DATASOURCE_NUM_ID, ORDER_ID , ORDER_TYPE)
WITH PUC_IBC_ALLOC_REPL as (
SELECT DISTINCT ALLOC_2, ORACLE_STORE
FROM PUC_IBC_REPL_STORE_TMP where ALLOC_2 is not null and ORACLE_STORE is not null),
RECEIPTS_IBC_ALLOC_REPL_LEG2 as (
SELECT
EKPO.MATNR AS ITEM,
--EKPO.WERKS AS ORG_NUM,
ORACLE_STORE AS ORG_NUM,
TO_DATE(EKET.EINDT,'YYYYMMDD') AS DAY_DT,
'44~A' AS INVRC_TYPE_CODE,
EKET.WEMNG AS INVRC_QTY,
EKPO.NETPR*EKET.WEMNG AS INVRC_COST_AMT_LCL,
'EUR' AS LOC_CURR_CODE,
EKKO.WAERS AS DOC_CURR_CODE,
1 AS DATASOURCE_NUM_ID,
EKKO.EBELN AS ORDER_ID,
'IBC_ALLOC_REPL_LEG2' AS ORDR_TYPE
FROM
sap_slt_prod_pv1.EKKO EKKO,
sap_slt_prod_pv1.EKPO EKPO,
sap_slt_prod_pv1.EKET EKET,
PUC_IBC_ALLOC_REPL IBC_ALLOC_REPL
where 1=1
-- JOIN PO HEADER AND DETAIL
AND EKKO.EBELN = EKPO.EBELN
-- MAIN ITEM AND NOT CANCELLED
AND EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' '
-- JOIN PO RECEIPT
and EKET.EBELN = EKPO.EBELN
AND EKET.EBELP = EKPO.EBELP
-- FILTER RECEIVED QTY > 0
AND EKET.WEMNG > 0
-- FILTER THE LIST OF PO/LOCATION FROM TEMP TABLE
AND EKKO.EBELN = IBC_ALLOC_REPL.ALLOC_2
--AND EKPO.WERKS = IBC_ALLOC_REPL.STORE
)
SELECT s.*
FROM RECEIPTS_IBC_ALLOC_REPL_LEG2 s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- IBC X-DOCKING ALLOCATION - LEG 1 - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('5. IBC X-DOCKING ALLOCATION - LEG 1 - LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_RAW (
ITEM, ORG_NUM, DAY_DT, INVRC_TYPE_CODE, INVRC_QTY, INVRC_COST_AMT_LCL, LOC_CURR_CODE, DOC_CURR_CODE, DATASOURCE_NUM_ID, ORDER_ID , ORDER_TYPE)
WITH PUC_IBC_ALLOC_XDOC as (
SELECT DISTINCT ALLOC_1, BUYING_ORACLE_WH
FROM PUC_IBC_PO_XDOC_STORE_TMP where ALLOC_1 is not null and BUYING_ORACLE_WH is not null AND ALLOC_2 is not null
),
RECEIPTS_IBC_ALLOC_XDOC_LEG1 as (
SELECT
EKPO.MATNR AS ITEM,
--EKPO.WERKS AS ORG_NUM,
BUYING_ORACLE_WH AS ORG_NUM,
TO_DATE(EKET.EINDT,'YYYYMMDD') AS DAY_DT,
'44~A' AS INVRC_TYPE_CODE,
EKET.WEMNG AS INVRC_QTY,
EKPO.NETPR*EKET.WEMNG AS INVRC_COST_AMT_LCL,
'EUR' AS LOC_CURR_CODE,
EKKO.WAERS AS DOC_CURR_CODE,
1 AS DATASOURCE_NUM_ID,
EKKO.EBELN AS ORDER_ID,
'IBC_ALLOC_XDOC_LEG1' AS ORDR_TYPE
FROM
sap_slt_prod_pv1.EKKO EKKO,
sap_slt_prod_pv1.EKPO EKPO,
sap_slt_prod_pv1.EKET EKET,
PUC_IBC_ALLOC_XDOC IBC_ALLOC_XDOC
where 1=1
-- JOIN PO HEADER AND DETAIL
AND EKKO.EBELN = EKPO.EBELN
-- MAIN ITEM AND NOT CANCELLED
AND EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' '
-- JOIN PO RECEIPT
and EKET.EBELN = EKPO.EBELN
AND EKET.EBELP = EKPO.EBELP
-- FILTER RECEIVED QTY > 0
AND EKET.WEMNG > 0
-- FILTER THE LIST OF PO/LOCATION FROM TEMP TABLE
AND EKKO.EBELN = IBC_ALLOC_XDOC.ALLOC_1
--AND EKPO.WERKS = IBC_ALLOC_XDOC.BUYING_WH
)
SELECT s.*
FROM RECEIPTS_IBC_ALLOC_XDOC_LEG1 s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- IBC REPLNISHMENT ALLOCATION - LEG 2 - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('6. IBC X-DOCKING ALLOCATION - LEG 2 - LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_RAW (
ITEM, ORG_NUM, DAY_DT, INVRC_TYPE_CODE, INVRC_QTY, INVRC_COST_AMT_LCL, LOC_CURR_CODE, DOC_CURR_CODE, DATASOURCE_NUM_ID, ORDER_ID , ORDER_TYPE)
WITH PUC_IBC_ALLOC_XDOC as (
SELECT DISTINCT ALLOC_2, ORACLE_STORE
FROM PUC_IBC_PO_XDOC_STORE_TMP WHERE ALLOC_2 IS NOT NULL AND ORACLE_STORE IS NOT NULL
),
RECEIPTS_IBC_ALLOC_XDOC_LEG2 as (
SELECT
EKPO.MATNR AS ITEM,
--EKPO.WERKS AS ORG_NUM,
ORACLE_STORE AS ORG_NUM,
TO_DATE(EKET.EINDT,'YYYYMMDD') AS DAY_DT,
'44~A' AS INVRC_TYPE_CODE,
EKET.WEMNG AS INVRC_QTY,
EKPO.NETPR*EKET.WEMNG AS INVRC_COST_AMT_LCL,
'EUR' AS LOC_CURR_CODE,
EKKO.WAERS AS DOC_CURR_CODE,
1 AS DATASOURCE_NUM_ID,
EKKO.EBELN AS ORDER_ID,
'IBC_ALLOC_XDOC_LEG2' AS ORDR_TYPE
FROM
sap_slt_prod_pv1.EKKO EKKO,
sap_slt_prod_pv1.EKPO EKPO,
sap_slt_prod_pv1.EKET EKET,
PUC_IBC_ALLOC_XDOC IBC_ALLOC_XDOC
where 1=1
-- JOIN PO HEADER AND DETAIL
AND EKKO.EBELN = EKPO.EBELN
-- MAIN ITEM AND NOT CANCELLED
AND EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' '
-- JOIN PO RECEIPT
and EKET.EBELN = EKPO.EBELN
AND EKET.EBELP = EKPO.EBELP
-- FILTER RECEIVED QTY > 0
AND EKET.WEMNG > 0
-- FILTER THE LIST OF PO/LOCATION FROM TEMP TABLE
AND EKKO.EBELN = IBC_ALLOC_XDOC.ALLOC_2
--AND EKPO.WERKS = IBC_ALLOC_XDOC.STORE
)
SELECT s.*
FROM RECEIPTS_IBC_ALLOC_XDOC_LEG2 s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- THIRD PARTY PURCHASE ORDER FOR X-DOCKING - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('7. THIRD PARTY PURCHASE ORDER X-DOCKING - LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_RAW (
ITEM, ORG_NUM, DAY_DT, INVRC_TYPE_CODE, INVRC_QTY, INVRC_COST_AMT_LCL, LOC_CURR_CODE, DOC_CURR_CODE, DATASOURCE_NUM_ID, ORDER_ID , ORDER_TYPE)
WITH PUC_EXCL_PO_ALLOC_XDOC as (
SELECT DISTINCT EXCL_PO, BUYING_ORACLE_WH
FROM PUC_TRD_PARTY_PO_XDOC_STORE_TMP WHERE EXCL_PO IS NOT NULL AND BUYING_ORACLE_WH IS NOT NULL AND ALLOC_1 IS NOT NULL
),
RECEIPTS_EXCL_PO_XDOC as (
SELECT
EKPO.MATNR AS ITEM,
EKPO.WERKS AS ORG_NUM,
TO_DATE(EKET.EINDT,'YYYYMMDD') AS DAY_DT,
'20' AS INVRC_TYPE_CODE,
EKET.WEMNG AS INVRC_QTY,
EKPO.NETPR*EKET.WEMNG AS INVRC_COST_AMT_LCL,
'EUR' AS LOC_CURR_CODE,
EKKO.WAERS AS DOC_CURR_CODE,
1 AS DATASOURCE_NUM_ID,
EKKO.EBELN AS ORDER_ID,
'EXCL_PO_XDOC' AS ORDR_TYPE
FROM
sap_slt_prod_pv1.EKKO EKKO,
sap_slt_prod_pv1.EKPO EKPO,
sap_slt_prod_pv1.EKET EKET,
PUC_EXCL_PO_ALLOC_XDOC EXCL_PO_XDOC
where 1=1
-- JOIN PO HEADER AND DETAIL
AND EKKO.EBELN = EKPO.EBELN
-- MAIN ITEM AND NOT CANCELLED
AND EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' '
-- JOIN PO RECEIPT
and EKET.EBELN = EKPO.EBELN
AND EKET.EBELP = EKPO.EBELP
-- FILTER RECEIVED QTY > 0
AND EKET.WEMNG > 0
-- FILTER THE LIST OF PO/LOCATION FROM TEMP TABLE
AND EKKO.EBELN = EXCL_PO_XDOC.EXCL_PO
--AND EKPO.WERKS = EXCL_PO_XDOC.BUYING_WH
)
SELECT s.*
FROM RECEIPTS_EXCL_PO_XDOC s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- THIRD PARTY X-DOCKING ALLOCATION - LEG 1 - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('8.THIRD PARTY PURCHASE ORDER X-DOCKING - ALLOCATION - LEG 1 - LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_RAW (
ITEM, ORG_NUM, DAY_DT, INVRC_TYPE_CODE, INVRC_QTY, INVRC_COST_AMT_LCL, LOC_CURR_CODE, DOC_CURR_CODE, DATASOURCE_NUM_ID, ORDER_ID , ORDER_TYPE)
WITH PUC_EXCL_PO_ALLOC_XDOC as (
SELECT DISTINCT ALLOC_1, ORACLE_STORE
FROM PUC_TRD_PARTY_PO_XDOC_STORE_TMP WHERE ALLOC_1 IS NOT NULL AND ORACLE_STORE IS NOT NULL
),
RECEIPTS_EXCL_ALLOC_XDOC_LEG1 as (
SELECT
EKPO.MATNR AS ITEM,
--EKPO.WERKS AS ORG_NUM,
ORACLE_STORE AS ORG_NUM,
TO_DATE(EKET.EINDT,'YYYYMMDD') AS DAY_DT,
'44~A' AS INVRC_TYPE_CODE,
EKET.WEMNG AS INVRC_QTY,
EKPO.NETPR*EKET.WEMNG AS INVRC_COST_AMT_LCL,
'EUR' AS LOC_CURR_CODE,
EKKO.WAERS AS DOC_CURR_CODE,
1 AS DATASOURCE_NUM_ID,
EKKO.EBELN AS ORDER_ID,
'EXCL_ALLOC_XDOC_LEG1' AS ORDR_TYPE
FROM
sap_slt_prod_pv1.EKKO EKKO,
sap_slt_prod_pv1.EKPO EKPO,
sap_slt_prod_pv1.EKET EKET,
PUC_EXCL_PO_ALLOC_XDOC EXCL_ALLOC_XDOC
where 1=1
-- JOIN PO HEADER AND DETAIL
AND EKKO.EBELN = EKPO.EBELN
-- MAIN ITEM AND NOT CANCELLED
AND EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' '
-- JOIN PO RECEIPT
and EKET.EBELN = EKPO.EBELN
AND EKET.EBELP = EKPO.EBELP
-- FILTER RECEIVED QTY > 0
AND EKET.WEMNG > 0
-- FILTER THE LIST OF PO/LOCATION FROM TEMP TABLE
AND EKKO.EBELN = EXCL_ALLOC_XDOC.ALLOC_1
--AND EKPO.WERKS = EXCL_ALLOC_XDOC.STORE
)
SELECT s.*
FROM RECEIPTS_EXCL_ALLOC_XDOC_LEG1 s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- TRANSFERS: STORE TO STORE AND ALL REVERSE LOGISTICS BEFORE RTV - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('9. TRANSFERS - LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_RAW (
ITEM, ORG_NUM, DAY_DT, INVRC_TYPE_CODE, INVRC_QTY, INVRC_COST_AMT_LCL, LOC_CURR_CODE, DOC_CURR_CODE, DATASOURCE_NUM_ID, ORDER_ID , ORDER_TYPE)
WITH PUC_TSF as (
SELECT DISTINCT TSF, TO_LOC
FROM V_PUC_TSF WHERE TSF IS NOT NULL AND FROM_LOC IS NOT NULL AND TO_LOC IS NOT NULL
),
RECEIPTS_TSF as (
SELECT
EKPO.MATNR AS ITEM,
/*CASE
WHEN EKKO.BSART in ('ZBAB','ZWBU','ZWAW','ZWRK','ZWVR','ZWND','ZWOW','ZWVS','ZSBU','ZWVW','ZWVD','ZWSD') THEN
EKKO.RESWK
WHEN EKKO.BSART in ('ZEGR','ZESL','ZEGK','ZESK','ZLK') THEN
decode(EKKO.RESWK,' ','0157',EKPO.WERKS)
ELSE
EKPO.WERKS
END AS ORG_NUM,*/
--EKPO.WERKS AS ORG_NUM,
TO_LOC AS ORG_NUM,
TO_DATE(EKET.EINDT,'YYYYMMDD') AS DAY_DT,
'44~T' AS INVRC_TYPE_CODE,
EKET.WEMNG AS INVRC_QTY,
EKPO.NETPR*EKET.WEMNG AS INVRC_COST_AMT_LCL,
'EUR' AS LOC_CURR_CODE,
EKKO.WAERS AS DOC_CURR_CODE,
1 AS DATASOURCE_NUM_ID,
EKKO.EBELN AS ORDER_ID,
'TRANSFER' AS ORDR_TYPE
FROM
sap_slt_prod_pv1.EKKO EKKO,
sap_slt_prod_pv1.EKPO EKPO,
sap_slt_prod_pv1.EKET EKET,
PUC_TSF TSF
where 1=1
-- JOIN PO HEADER AND DETAIL
AND EKKO.EBELN = EKPO.EBELN
-- MAIN ITEM AND NOT CANCELLED
AND EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' '
-- JOIN PO RECEIPT
and EKET.EBELN = EKPO.EBELN
AND EKET.EBELP = EKPO.EBELP
-- FILTER RECEIVED QTY > 0
AND EKET.WEMNG > 0
-- FILTER THE LIST OF PO/LOCATION FROM TEMP TABLE
AND EKKO.EBELN = TSF.TSF
)
SELECT s.*
FROM RECEIPTS_TSF s;
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),
tabname => 'ORI_RECEIPTS_RAW',
cascade => TRUE);
END;
/
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_RECEIPTS_REJECTED
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_REJECTED (
LOG_LVL ,ERROR_MSG ,ITEM ,ORG_NUM ,DAY_DT ,INVRC_TYPE_CODE ,INVRC_QTY ,INVRC_COST_AMT_LCL ,INVRC_RTL_AMT_LCL ,
ETL_THREAD_VAL ,GLOBAL1_EXCHANGE_RATE ,GLOBAL2_EXCHANGE_RATE ,GLOBAL3_EXCHANGE_RATE ,LOC_EXCHANGE_RATE ,
LOC_CURR_CODE ,DOC_CURR_CODE ,DELETE_FLG ,DATASOURCE_NUM_ID ,FLEX1_NUM_VALUE ,FLEX2_NUM_VALUE ,FLEX3_NUM_VALUE ,
FLEX4_NUM_VALUE ,FLEX5_NUM_VALUE ,FLEX6_NUM_VALUE ,FLEX7_NUM_VALUE ,FLEX8_NUM_VALUE ,FLEX9_NUM_VALUE ,FLEX10_NUM_VALUE ,
FLEX11_NUM_VALUE ,FLEX12_NUM_VALUE ,FLEX13_NUM_VALUE ,FLEX14_NUM_VALUE ,FLEX15_NUM_VALUE ,FLEX16_NUM_VALUE ,
FLEX17_NUM_VALUE ,FLEX18_NUM_VALUE ,FLEX19_NUM_VALUE ,FLEX20_NUM_VALUE ,CREATE_ID ,CREATE_DATETIME ,
CTRL_STATUS ,ORDER_ID ,ORDER_TYPE ,ITEM_PR1 ,INSERTED_AT)
WITH receipts_hist as (
SELECT *
FROM ORI_RECEIPTS_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 receipts_hist s
WHERE LTRIM(s.ORG_NUM,'0') NOT IN (SELECT STORE FROM STORE_ADD_CTRL WHERE CTRL_STATUS = 'C') AND 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 receipts_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 receipts_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_STATS.GATHER_TABLE_STATS(ownname => SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),
tabname => 'ORI_RECEIPTS_REJECTED',
cascade => TRUE);
END;
/
-- Rows that passed validation are inserted in ORI_RECEIPTS_CTRL
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_CTRL
WITH receipts_hist as (
SELECT /*+ PARALLEL(24) */
*
FROM ORI_RECEIPTS_RAW
)
SELECT /*+ PARALLEL(24) */
ITEM,
LTRIM(ORG_NUM, '0') ORG_NUM,
DAY_DT ,INVRC_TYPE_CODE ,
SUM(INVRC_QTY) AS INVRC_QTY ,
SUM(INVRC_COST_AMT_LCL) AS INVRC_COST_AMT_LCL,
SUM(INVRC_RTL_AMT_LCL) AS INVRC_RTL_AMT_LCL ,
ETL_THREAD_VAL ,GLOBAL1_EXCHANGE_RATE ,GLOBAL2_EXCHANGE_RATE ,GLOBAL3_EXCHANGE_RATE ,LOC_EXCHANGE_RATE ,
LOC_CURR_CODE ,DOC_CURR_CODE ,DELETE_FLG ,DATASOURCE_NUM_ID ,FLEX1_NUM_VALUE ,FLEX2_NUM_VALUE ,FLEX3_NUM_VALUE ,
FLEX4_NUM_VALUE ,FLEX5_NUM_VALUE ,FLEX6_NUM_VALUE ,FLEX7_NUM_VALUE ,FLEX8_NUM_VALUE ,FLEX9_NUM_VALUE ,FLEX10_NUM_VALUE ,
FLEX11_NUM_VALUE ,FLEX12_NUM_VALUE ,FLEX13_NUM_VALUE ,FLEX14_NUM_VALUE ,FLEX15_NUM_VALUE ,FLEX16_NUM_VALUE ,
FLEX17_NUM_VALUE ,FLEX18_NUM_VALUE ,FLEX19_NUM_VALUE ,FLEX20_NUM_VALUE ,CREATE_ID ,CREATE_DATETIME ,
'I', SYSDATE, NULL
FROM receipts_hist s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_RECEIPTS_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
GROUP BY ITEM, ORG_NUM, DAY_DT, INVRC_TYPE_CODE, ETL_THREAD_VAL ,GLOBAL1_EXCHANGE_RATE ,GLOBAL2_EXCHANGE_RATE ,GLOBAL3_EXCHANGE_RATE ,LOC_EXCHANGE_RATE ,
LOC_CURR_CODE ,DOC_CURR_CODE ,DELETE_FLG ,DATASOURCE_NUM_ID ,FLEX1_NUM_VALUE ,FLEX2_NUM_VALUE ,FLEX3_NUM_VALUE ,
FLEX4_NUM_VALUE ,FLEX5_NUM_VALUE ,FLEX6_NUM_VALUE ,FLEX7_NUM_VALUE ,FLEX8_NUM_VALUE ,FLEX9_NUM_VALUE ,FLEX10_NUM_VALUE ,
FLEX11_NUM_VALUE ,FLEX12_NUM_VALUE ,FLEX13_NUM_VALUE ,FLEX14_NUM_VALUE ,FLEX15_NUM_VALUE ,FLEX16_NUM_VALUE ,
FLEX17_NUM_VALUE ,FLEX18_NUM_VALUE ,FLEX19_NUM_VALUE ,FLEX20_NUM_VALUE ,CREATE_ID ,CREATE_DATETIME ,
CTRL_STATUS;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- REJECTION SUMMARY
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Retrieve a summary by error type
SELECT ERROR_MSG, COUNT(*) AS NUM_RECORDS
FROM ORI_RECEIPTS_REJECTED GROUP BY ERROR_MSG
UNION ALL
SELECT 'ALL RECEIPTS' as ERROR_MSG, COUNT(*) AS NUM_RECORDS
FROM ORI_RECEIPTS_RAW
ORDER BY NUM_RECORDS DESC;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- VALIDATION FINAL CTRL TABLE SUMMARY
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- VALIDATE DUPLICATES
SELECT ITEM, ORG_NUM, DAY_DT, INVRC_TYPE_CODE FROM ORI_RECEIPTS_CTRL GROUP BY ITEM, ORG_NUM, DAY_DT, INVRC_TYPE_CODE HAVING COUNT(1) > 1;
-- SQL EXPORT FILE
SELECT
ITEM,
ORG_NUM,
TO_CHAR(DAY_DT, 'YYYYMMDD') AS DAY_DT, INVRC_TYPE_CODE,
INVRC_QTY,
INVRC_COST_AMT_LCL,
LOC_CURR_CODE,
DOC_CURR_CODE,
DATASOURCE_NUM_ID
FROM
ORI_RECEIPTS_CTRL
;