Source: DMF/PuC/AIF/BKP/Receipt_Hist_(old).sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_RECEIPTS_REJECTED
--
-- IBC PURCHASE ORDERS FOR REPLENISHMENT - PR1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('1. IBC PURCHASE ORDERS FOR REPLENISHMENT - VALIDATION/REJECTION');
END;
/
INSERT INTO ORI_RECEIPTS_REJECTED (
LOG_LVL, ERROR_MSG, ORDER_ID, ORDER_TYPE, LOCATION, LOCATION_TYPE
)
WITH PUC_IBC_PO_REPL as (
SELECT DISTINCT IBC_PO, IBC_WH
FROM PUC_IBC_PO_REPL_TMP
),
---------------------------------------------------------------------------------
-- 1. a) MISSING WH
---------------------------------------------------------------------------------
error_rows_missing_WH AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_WH_IBC_PO_REPL' AS ERROR_MSG, IBC_PO AS ORDER_ID, 'IBC_PO_REPL' AS ORDER_TYPE, IBC_WH AS LOCATION, 'IBC_WH' AS LOCATION_TYPE
FROM PUC_IBC_PO_REPL s
WHERE s.IBC_WH NOT IN (SELECT WH_SLT FROM XREF_WH)
),
---------------------------------------------------------------------------------
-- 2. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT * FROM error_rows_missing_WH
)
SELECT * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('1. IBC PURCHASE ORDERS FOR REPLENISHMENT - LOADING');
END;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_RECEIPTS_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_RECEIPTS_TMP
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_TMP (
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_PO_REPL as (
SELECT DISTINCT IBC_PO, IBC_WH
FROM PUC_IBC_PO_REPL_TMP),
RECEIPTS_IBC_PO_REPL 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,
'IBC_PO_REPL' AS ORDR_TYPE
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
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
AND NOT EXISTS (SELECT 1
FROM ORI_RECEIPTS_REJECTED r
WHERE LOG_LVL = 'E'
AND r.ORDER_ID = IBC_PO_REPL.IBC_PO
AND r.LOCATION = IBC_PO_REPL.IBC_WH)
)
SELECT s.*
FROM RECEIPTS_IBC_PO_REPL s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_RECEIPTS_REJECTED
--
-- IBC PURCHASE ORDERS FOR X-DOCKING - PR1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('2. IBC PURCHASE ORDERS FOR X-DOCKING - VALIDATION/REJECTION');
END;
/
INSERT INTO ORI_RECEIPTS_REJECTED (
LOG_LVL, ERROR_MSG, ORDER_ID, ORDER_TYPE, LOCATION, LOCATION_TYPE
)
WITH PUC_IBC_PO_XDOC as (
SELECT DISTINCT IBC_PO, IBC_WH
FROM PUC_IBC_PO_XDOC_TMP
),
---------------------------------------------------------------------------------
-- 1. a) MISSING WH
---------------------------------------------------------------------------------
error_rows_missing_WH AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_WH_IBC_PO_XDOC' AS ERROR_MSG, IBC_PO AS ORDER_ID, 'IBC_PO_XDOC' AS ORDER_TYPE, IBC_WH AS LOCATION, 'IBC_WH' AS LOCATION_TYPE
FROM PUC_IBC_PO_XDOC s
WHERE s.IBC_WH NOT IN (SELECT WH_SLT FROM XREF_WH)
),
---------------------------------------------------------------------------------
-- 2. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT * FROM error_rows_missing_WH
)
SELECT * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('2. IBC PURCHASE ORDERS FOR X-DOCKING - LOADING');
END;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_RECEIPTS_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_RECEIPTS_TMP
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_TMP (
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_PO_XDOC as (
SELECT DISTINCT IBC_PO, IBC_WH
FROM PUC_IBC_PO_XDOC_TMP),
RECEIPTS_IBC_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,
'IBC_PO_REPL' AS ORDR_TYPE
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
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
AND NOT EXISTS (SELECT 1
FROM ORI_RECEIPTS_REJECTED r
WHERE LOG_LVL = 'E'
AND r.ORDER_ID = IBC_PO_XDOC.IBC_PO
AND r.LOCATION = IBC_PO_XDOC.IBC_WH)
)
SELECT s.*
FROM RECEIPTS_IBC_PO_XDOC s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_RECEIPTS_REJECTED
--
-- IBC REPLNISHMENT ALLOCATION - LEG 1 - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('3. IBC REPLNISHMENT ALLOCATION - LEG 1 - VALIDATION/REJECTION');
END;
/
INSERT INTO ORI_RECEIPTS_REJECTED (
LOG_LVL, ERROR_MSG, ORDER_ID, ORDER_TYPE, LOCATION, LOCATION_TYPE
)
WITH PUC_IBC_ALLOC_REPL as (
SELECT DISTINCT ALLOC_1, BUYING_WH
FROM PUC_IBC_ALLOC_REPL_TMP
),
---------------------------------------------------------------------------------
-- 1. a) MISSING WH
---------------------------------------------------------------------------------
error_rows_missing_WH AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_WH_IBC_ALLOC_REPL_LEG1' AS ERROR_MSG, ALLOC_1 AS ORDER_ID, 'IBC_ALLOC_REPL_LEG1' AS ORDER_TYPE, BUYING_WH AS LOCATION, 'BUYING_WH' AS LOCATION_TYPE
FROM PUC_IBC_ALLOC_REPL s
WHERE s.BUYING_WH NOT IN (SELECT WH_SLT FROM XREF_WH)
),
---------------------------------------------------------------------------------
-- 2. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT * FROM error_rows_missing_WH
)
SELECT * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('3. IBC REPLNISHMENT ALLOCATION - LEG 1 - LOADING');
END;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_RECEIPTS_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_RECEIPTS_TMP
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_TMP (
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_WH
FROM PUC_IBC_ALLOC_REPL_TMP),
RECEIPTS_IBC_ALLOC_REPL_LEG1 as (
SELECT
EKPO.MATNR AS ITEM,
EKPO.WERKS 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
AND NOT EXISTS (SELECT 1
FROM ORI_RECEIPTS_REJECTED r
WHERE LOG_LVL = 'E'
AND r.ORDER_ID = IBC_ALLOC_REPL.ALLOC_1
AND r.LOCATION = IBC_ALLOC_REPL.BUYING_WH)
)
SELECT s.*
FROM RECEIPTS_IBC_ALLOC_REPL_LEG1 s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_RECEIPTS_REJECTED
--
-- IBC REPLNISHMENT ALLOCATION - LEG 2 - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('4. IBC REPLNISHMENT ALLOCATION - LEG 2 - VALIDATION/REJECTION');
END;
/
INSERT INTO ORI_RECEIPTS_REJECTED (
LOG_LVL, ERROR_MSG, ORDER_ID, ORDER_TYPE, LOCATION, LOCATION_TYPE
)
WITH PUC_IBC_ALLOC_REPL as (
SELECT DISTINCT ALLOC_2, STORE
FROM PUC_IBC_ALLOC_REPL_TMP
),
---------------------------------------------------------------------------------
-- 1. a) MISSING STORE
---------------------------------------------------------------------------------
error_rows_missing_STORE AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_STORE_IBC_ALLOC_REPL_LEG2' AS ERROR_MSG, ALLOC_2 AS ORDER_ID, 'IBC_ALLOC_REPL_LEG2' AS ORDER_TYPE, STORE AS LOCATION, 'STORE' AS LOCATION_TYPE
FROM PUC_IBC_ALLOC_REPL s
WHERE s.STORE NOT IN (SELECT STORE FROM STORE_ADD_CTRL WHERE CTRL_STATUS = 'C')
),
---------------------------------------------------------------------------------
-- 2. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT * FROM error_rows_missing_STORE
)
SELECT * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('4. IBC REPLNISHMENT ALLOCATION - LEG 2 - LOADING');
END;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_RECEIPTS_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_RECEIPTS_TMP
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_TMP (
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, STORE
FROM PUC_IBC_ALLOC_REPL_TMP),
RECEIPTS_IBC_ALLOC_REPL_LEG2 as (
SELECT
EKPO.MATNR AS ITEM,
EKPO.WERKS 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
AND NOT EXISTS (SELECT 1
FROM ORI_RECEIPTS_REJECTED r
WHERE LOG_LVL = 'E'
AND r.ORDER_ID = IBC_ALLOC_REPL.ALLOC_2
AND r.LOCATION = IBC_ALLOC_REPL.STORE)
)
SELECT s.*
FROM RECEIPTS_IBC_ALLOC_REPL_LEG2 s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_RECEIPTS_REJECTED
--
-- IBC X-DOCKING ALLOCATION - LEG 1 - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('5. IBC X-DOCKING ALLOCATION - LEG 1 - VALIDATION/REJECT');
END;
/
INSERT INTO ORI_RECEIPTS_REJECTED (
LOG_LVL, ERROR_MSG, ORDER_ID, ORDER_TYPE, LOCATION, LOCATION_TYPE
)
WITH PUC_IBC_ALLOC_XDOC as (
SELECT DISTINCT ALLOC_1, BUYING_WH
FROM PUC_IBC_ALLOC_XDOC_TMP
),
---------------------------------------------------------------------------------
-- 1. a) MISSING WH
---------------------------------------------------------------------------------
error_rows_missing_WH AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_WH_IBC_ALLOC_XDOC_LEG1' AS ERROR_MSG, ALLOC_1 AS ORDER_ID, 'IBC_ALLOC_XDOC_LEG1' AS ORDER_TYPE, BUYING_WH AS LOCATION, 'BUYING_WH' AS LOCATION_TYPE
FROM PUC_IBC_ALLOC_XDOC s
WHERE s.BUYING_WH NOT IN (SELECT WH_SLT FROM XREF_WH)
),
---------------------------------------------------------------------------------
-- 2. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT * FROM error_rows_missing_WH
)
SELECT * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('5. IBC X-DOCKING ALLOCATION - LEG 1 - LOADING');
END;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_RECEIPTS_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_RECEIPTS_TMP
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_TMP (
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_WH
FROM PUC_IBC_ALLOC_XDOC_TMP
),
RECEIPTS_IBC_ALLOC_XDOC_LEG1 as (
SELECT
EKPO.MATNR AS ITEM,
EKPO.WERKS 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
AND NOT EXISTS (SELECT 1
FROM ORI_RECEIPTS_REJECTED r
WHERE LOG_LVL = 'E'
AND r.ORDER_ID = IBC_ALLOC_XDOC.ALLOC_1
AND r.LOCATION = IBC_ALLOC_XDOC.BUYING_WH)
)
SELECT s.*
FROM RECEIPTS_IBC_ALLOC_XDOC_LEG1 s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_RECEIPTS_REJECTED
--
-- IBC REPLNISHMENT ALLOCATION - LEG 2 - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('6. IBC X-DOCKING ALLOCATION - LEG 2 - VALIDATION/REJECTION');
END;
/
INSERT INTO ORI_RECEIPTS_REJECTED (
LOG_LVL, ERROR_MSG, ORDER_ID, ORDER_TYPE, LOCATION, LOCATION_TYPE
)
WITH PUC_IBC_ALLOC_XDOC as (
SELECT DISTINCT ALLOC_2, STORE
FROM PUC_IBC_ALLOC_XDOC_TMP
),
---------------------------------------------------------------------------------
-- 1. a) MISSING STORE
---------------------------------------------------------------------------------
error_rows_missing_STORE AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_STORE_IBC_ALLOC_XDOC_LEG2' AS ERROR_MSG, ALLOC_2 AS ORDER_ID, 'IBC_ALLOC_XDOC_LEG2' AS ORDER_TYPE, STORE AS LOCATION, 'STORE' AS LOCATION_TYPE
FROM PUC_IBC_ALLOC_XDOC s
WHERE s.STORE NOT IN (SELECT STORE FROM STORE_ADD_CTRL WHERE CTRL_STATUS = 'C')
),
---------------------------------------------------------------------------------
-- 2. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT * FROM error_rows_missing_STORE
)
SELECT * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('6. IBC X-DOCKING ALLOCATION - LEG 2 - LOADING');
END;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_RECEIPTS_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_RECEIPTS_TMP
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_TMP (
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, STORE
FROM PUC_IBC_ALLOC_XDOC_TMP
),
RECEIPTS_IBC_ALLOC_XDOC_LEG2 as (
SELECT
EKPO.MATNR AS ITEM,
EKPO.WERKS 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
AND NOT EXISTS (SELECT 1
FROM ORI_RECEIPTS_REJECTED r
WHERE LOG_LVL = 'E'
AND r.ORDER_ID = IBC_ALLOC_XDOC.ALLOC_2
AND r.LOCATION = IBC_ALLOC_XDOC.STORE)
)
SELECT s.*
FROM RECEIPTS_IBC_ALLOC_XDOC_LEG2 s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_RECEIPTS_REJECTED
--
-- THIRD PARTY PURCHASE ORDER X-DOCKING - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('7. THIRD PARTY PURCHASE ORDER X-DOCKING - VALIDATION/REJECT');
END;
/
INSERT INTO ORI_RECEIPTS_REJECTED (
LOG_LVL, ERROR_MSG, ORDER_ID, ORDER_TYPE, LOCATION, LOCATION_TYPE
)
WITH PUC_EXCL_PO_ALLOC_XDOC as (
SELECT DISTINCT EXCL_PO, BUYING_WH
FROM PUC_EXCL_PO_ALLOC_XDOC_TMP
),
---------------------------------------------------------------------------------
-- 1. a) MISSING WH
---------------------------------------------------------------------------------
error_rows_missing_WH AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_WH_EXCL_PO_XDOC' AS ERROR_MSG, EXCL_PO AS ORDER_ID, 'EXCL_PO_XDOC' AS ORDER_TYPE, BUYING_WH AS LOCATION, 'BUYING_WH' AS LOCATION_TYPE
FROM PUC_EXCL_PO_ALLOC_XDOC s
WHERE s.BUYING_WH NOT IN (SELECT WH_SLT FROM XREF_WH)
),
---------------------------------------------------------------------------------
-- 2. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT * FROM error_rows_missing_WH
)
SELECT * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('7. THIRD PARTY PURCHASE ORDER X-DOCKING - LEG 1 - LOADING');
END;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_RECEIPTS_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_RECEIPTS_TMP
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_TMP (
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_WH
FROM PUC_EXCL_PO_ALLOC_XDOC_TMP
),
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
AND NOT EXISTS (SELECT 1
FROM ORI_RECEIPTS_REJECTED r
WHERE LOG_LVL = 'E'
AND r.ORDER_ID = EXCL_PO_XDOC.EXCL_PO
AND r.LOCATION = EXCL_PO_XDOC.BUYING_WH)
)
SELECT s.*
FROM RECEIPTS_EXCL_PO_XDOC s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_RECEIPTS_REJECTED
--
-- THIRD PARTY X-DOCKING ALLOCATION - LEG 1 - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('8. THIRD PARTY X-DOCKING ALLOCATION - LEG 1 - VALIDATION/REJECTION');
END;
/
INSERT INTO ORI_RECEIPTS_REJECTED (
LOG_LVL, ERROR_MSG, ORDER_ID, ORDER_TYPE, LOCATION, LOCATION_TYPE
)
WITH PUC_EXCL_PO_ALLOC_XDOC as (
SELECT DISTINCT ALLOC_1, STORE
FROM PUC_EXCL_PO_ALLOC_XDOC_TMP
),
---------------------------------------------------------------------------------
-- 1. a) MISSING STORE
---------------------------------------------------------------------------------
error_rows_missing_STORE AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_STORE_EXCL_ALLOC_XDOC_LEG1' AS ERROR_MSG, ALLOC_1 AS ORDER_ID, 'EXCL_ALLOC_XDOC_LEG1' AS ORDER_TYPE, STORE AS LOCATION, 'STORE' AS LOCATION_TYPE
FROM PUC_EXCL_PO_ALLOC_XDOC s
WHERE s.STORE NOT IN (SELECT STORE FROM STORE_ADD_CTRL WHERE CTRL_STATUS = 'C')
),
---------------------------------------------------------------------------------
-- 2. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT * FROM error_rows_missing_STORE
)
SELECT * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('8. IBC X-DOCKING ALLOCATION - LEG 1 - LOADING');
END;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_RECEIPTS_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_RECEIPTS_TMP
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_TMP (
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, STORE
FROM PUC_EXCL_PO_ALLOC_XDOC_TMP
),
RECEIPTS_EXCL_ALLOC_XDOC_LEG2 as (
SELECT
EKPO.MATNR AS ITEM,
EKPO.WERKS 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
AND NOT EXISTS (SELECT 1
FROM ORI_RECEIPTS_REJECTED r
WHERE LOG_LVL = 'E'
AND r.ORDER_ID = EXCL_ALLOC_XDOC.ALLOC_1
AND r.LOCATION = EXCL_ALLOC_XDOC.STORE)
)
SELECT s.*
FROM RECEIPTS_EXCL_ALLOC_XDOC_LEG2 s;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_RECEIPTS_REJECTED
--
-- TRANSFERS: STORE TO STORE AND ALL REVERSE LOGISTICS BEFORE RTV - PV1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('9. TRANSFERS - VALIDATION/REJECTION');
END;
/
INSERT INTO ORI_RECEIPTS_REJECTED (
LOG_LVL, ERROR_MSG, ORDER_ID, ORDER_TYPE, LOCATION, LOCATION_TYPE
)
WITH PUC_TSF as (
SELECT DISTINCT TSF, FROM_LOC, TO_LOC
FROM PUC_TSF_TMP
),
---------------------------------------------------------------------------------
-- 1. a) MISSING FROM LOC
---------------------------------------------------------------------------------
error_rows_missing_from_location AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_FROM_LOCATION_TSF' AS ERROR_MSG, TSF AS ORDER_ID, 'TRANSFERS' AS ORDER_TYPE, FROM_LOC AS LOCATION, 'STORE' AS LOCATION_TYPE
FROM PUC_TSF s
WHERE s.FROM_LOC NOT IN (SELECT STORE FROM STORE_ADD_CTRL WHERE CTRL_STATUS = 'C') AND s.FROM_LOC NOT IN (SELECT WH_SLT FROM XREF_WH)
),
---------------------------------------------------------------------------------
-- 2. a) MISSING TO LOC
---------------------------------------------------------------------------------
error_rows_missing_to_location AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_TO_LOCATION_TSF' AS ERROR_MSG, TSF AS ORDER_ID, 'TRANSFERS' AS ORDER_TYPE, TO_LOC AS LOCATION, 'STORE' AS LOCATION_TYPE
FROM PUC_TSF s
WHERE s.TO_LOC NOT IN (SELECT STORE FROM STORE_ADD_CTRL WHERE CTRL_STATUS = 'C') AND s.TO_LOC NOT IN (SELECT WH_SLT FROM XREF_WH)
),
---------------------------------------------------------------------------------
-- 3. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT * FROM error_rows_missing_from_location
union all
SELECT * FROM error_rows_missing_to_location
)
SELECT * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('9. TRANSFERS - LOADING');
END;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_RECEIPTS_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_RECEIPTS_TMP
INSERT INTO /*+ PARALLEL(24) */ ORI_RECEIPTS_TMP (
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, FROM_LOC, TO_LOC
FROM PUC_TSF_TMP
),
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_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
AND EKPO.WERKS = TSF.FROM_LOC
AND NOT EXISTS (SELECT 1
FROM ORI_RECEIPTS_REJECTED r
WHERE LOG_LVL = 'E'
AND r.ORDER_ID = TSF.TSF
AND r.LOCATION = TSF.FROM_LOC)
)
SELECT s.*
FROM RECEIPTS_TSF s;
COMMIT;