Source: DMF/PuC/AIF/DR3/SUPPLIER_INVOICES_HIST.sql
-- ============================================================================
-- SUPPLIER INVOICES DELTA — Full Validation Pipeline
-- ============================================================================
-- Fact: supplier_invoices
-- Rejected: ORI_SUPPLIER_INVOICES_REJECTED
-- STG (accepted): ORI_SUPPLIER_INVOICES_TMP
-- Rules:
-- 1. Invalid format
-- 2. Mandatory / NULL validations
-- 3. Missing Item
-- 4. Duplicate Key
-- Pipeline: RAW → REJECTED → STG → CTRL
-- ============================================================================
ALTER SESSION SET DDL_LOCK_TIMEOUT = 300;
-- ============================================================================
-- STEP 0 — SAP SLT EXTRACT
-- ============================================================================
-- ============================================================================
-- PR1 HEADER
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE
'DROP TABLE PR1_SUPPLIER_INVOICES_HEADER_RAW_BKP PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE
'RENAME PR1_SUPPLIER_INVOICES_HEADER_RAW
TO PR1_SUPPLIER_INVOICES_HEADER_RAW_BKP';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
DROP TABLE PR1_SUPPLIER_INVOICES_HEADER_RAW;
CREATE TABLE PR1_SUPPLIER_INVOICES_HEADER_RAW
NOLOGGING
PARALLEL 16
AS
SELECT /*+ PARALLEL(16) */
RBKP.*
FROM SAP_SLT_PROD_PR1.RBKP RBKP
WHERE RBKP.BLDAT >= '20241027'
AND RBKP.BLART = '10'
AND RBKP.RBSTAT = '5';
-- ============================================================================
-- PV1 HEADER
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE
'DROP TABLE PV1_SUPPLIER_INVOICES_HEADER_RAW_BKP PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE
'RENAME PV1_SUPPLIER_INVOICES_HEADER_RAW
TO PV1_SUPPLIER_INVOICES_HEADER_RAW_BKP';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
DROP TABLE PV1_SUPPLIER_INVOICES_HEADER_RAW;
CREATE TABLE PV1_SUPPLIER_INVOICES_HEADER_RAW
NOLOGGING
PARALLEL 16
AS
SELECT /*+ PARALLEL(16) */
RBKP.*
FROM SAP_SLT_PROD_PV1.RBKP RBKP
WHERE RBKP.BLDAT >= '20241027'
AND RBKP.BLART = '10'
AND RBKP.RBSTAT = '5'
AND RBKP.LIFNR <> '0000940506';
-- ============================================================================
-- PR1 DETAIL
-- RSEG + EKPO.NETPR / PEINH
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE
'DROP TABLE PR1_SUPPLIER_INVOICES_DETAIL_RAW_BKP PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE
'RENAME PR1_SUPPLIER_INVOICES_DETAIL_RAW
TO PR1_SUPPLIER_INVOICES_DETAIL_RAW_BKP';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
DROP TABLE PR1_SUPPLIER_INVOICES_DETAIL_RAW;
CREATE TABLE PR1_SUPPLIER_INVOICES_DETAIL_RAW
NOLOGGING
PARALLEL 16
AS
SELECT /*+ PARALLEL(16) */
RSEG.*,
EKPO.NETPR,
EKPO.PEINH
FROM SAP_SLT_PROD_PR1.RBKP RBKP
JOIN SAP_SLT_PROD_PR1.RSEG RSEG
ON RSEG.BELNR = RBKP.BELNR
AND RSEG.GJAHR = RBKP.GJAHR
LEFT JOIN SAP_SLT_PROD_PR1.EKPO EKPO
ON RSEG.EBELN = EKPO.EBELN
AND RSEG.EBELP = EKPO.EBELP
AND EKPO.UEBPO = '00000'
WHERE RBKP.BLDAT >= '20241027'
AND RBKP.BLART = '10'
AND RBKP.RBSTAT = '5';
-- ============================================================================
-- PV1 DETAIL
-- RSEG + EKPO.NETPR / PEINH
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE
'DROP TABLE PV1_SUPPLIER_INVOICES_DETAIL_RAW_BKP PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE
'RENAME PV1_SUPPLIER_INVOICES_DETAIL_RAW
TO PV1_SUPPLIER_INVOICES_DETAIL_RAW_BKP';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
DROP TABLE PV1_SUPPLIER_INVOICES_DETAIL_RAW;
CREATE TABLE PV1_SUPPLIER_INVOICES_DETAIL_RAW
NOLOGGING
PARALLEL 16
AS
SELECT /*+ PARALLEL(16) */
RSEG.*,
EKPO.NETPR,
EKPO.PEINH
FROM SAP_SLT_PROD_PV1.RBKP RBKP
JOIN SAP_SLT_PROD_PV1.RSEG RSEG
ON RSEG.BELNR = RBKP.BELNR
AND RSEG.GJAHR = RBKP.GJAHR
LEFT JOIN SAP_SLT_PROD_PV1.EKPO EKPO
ON RSEG.EBELN = EKPO.EBELN
AND RSEG.EBELP = EKPO.EBELP
AND EKPO.UEBPO = '00000'
WHERE RBKP.BLDAT >= '20241027'
AND RBKP.BLART = '10'
AND RBKP.RBSTAT = '5'
AND RBKP.LIFNR <> '0000940506';
-- ============================================================================
-- EXTRACT COUNTS
-- ============================================================================
SELECT 'PR1_HDR' AS T,
COUNT(*) AS N
FROM PR1_SUPPLIER_INVOICES_HEADER_RAW
UNION ALL
SELECT 'PV1_HDR',
COUNT(*)
FROM PV1_SUPPLIER_INVOICES_HEADER_RAW
UNION ALL
SELECT 'PR1_DTL',
COUNT(*)
FROM PR1_SUPPLIER_INVOICES_DETAIL_RAW
UNION ALL
SELECT 'PV1_DTL',
COUNT(*)
FROM PV1_SUPPLIER_INVOICES_DETAIL_RAW;
-- ============================================================================
-- STEP 1 — MATERIALIZE NORMALIZED SOURCE
-- ============================================================================
DROP TABLE DMF_SOURCE__SUPPLIER_INVOICES PURGE;
CREATE TABLE DMF_SOURCE__SUPPLIER_INVOICES
PARALLEL 4
AS
WITH src AS (
-- ========================================================================
-- PR1
-- ========================================================================
SELECT
'PR1~' || d.ROWID AS SRC_ROWID,
loc.ORACLE_ID AS ORG_NUM,
xit.ITEM_PV1 AS PROD_IT_NUM,
TO_DATE(
NULLIF(TRIM(h.BLDAT), '00000000'),
'YYYYMMDD'
) AS DAY_DT,
xs.ORACLE_ID AS SUPPLIER_NUM,
d.EBELN AS PURCHASE_ORDER_ID,
h.BELNR AS INVOICE_ID,
d.MENGE AS INVOICE_QTY,
CASE
WHEN NVL(d.MENGE, 0) <> 0
THEN d.WRBTR / d.MENGE
END AS INVOICE_UNIT_COST_AMT_LCL,
CASE
WHEN NVL(d.PEINH, 0) <> 0
THEN d.NETPR / d.PEINH
ELSE d.NETPR
END AS PO_UNIT_COST_AMT_LCL,
1 AS DATASOURCE_NUM_ID,
h.WAERS AS DOC_CURR_CODE,
'EUR' AS LOC_CURR_CODE
FROM PR1_SUPPLIER_INVOICES_DETAIL_RAW d
JOIN PR1_SUPPLIER_INVOICES_HEADER_RAW h
ON h.BELNR = d.BELNR
AND h.GJAHR = d.GJAHR
LEFT JOIN XREF_LOCATION loc
ON loc.SAP_ID = d.WERKS
AND loc.ORACLE_TYPE = 'W'
LEFT JOIN XREF_ITEM_IBC_RETAIL xit
ON xit.ITEM_PR1 = d.MATNR
LEFT JOIN XREF_SUPS xs
ON xs.SAP_ID = h.LIFNR
AND xs.SAP_ORG_UNIT = h.BUKRS
UNION ALL
-- ========================================================================
-- PV1
-- ========================================================================
SELECT
'PV1~' || d.ROWID AS SRC_ROWID,
loc.ORACLE_ID AS ORG_NUM,
d.MATNR AS PROD_IT_NUM,
TO_DATE(
NULLIF(TRIM(h.BLDAT), '00000000'),
'YYYYMMDD'
) AS DAY_DT,
xs.ORACLE_ID AS SUPPLIER_NUM,
d.EBELN AS PURCHASE_ORDER_ID,
h.BELNR AS INVOICE_ID,
d.MENGE AS INVOICE_QTY,
CASE
WHEN NVL(d.MENGE, 0) <> 0
THEN d.WRBTR / d.MENGE
END AS INVOICE_UNIT_COST_AMT_LCL,
CASE
WHEN NVL(d.PEINH, 0) <> 0
THEN d.NETPR / d.PEINH
ELSE d.NETPR
END AS PO_UNIT_COST_AMT_LCL,
1 AS DATASOURCE_NUM_ID,
h.WAERS AS DOC_CURR_CODE,
'EUR' AS LOC_CURR_CODE
FROM PV1_SUPPLIER_INVOICES_DETAIL_RAW d
JOIN PV1_SUPPLIER_INVOICES_HEADER_RAW h
ON h.BELNR = d.BELNR
AND h.GJAHR = d.GJAHR
LEFT JOIN XREF_LOCATION loc
ON loc.SAP_ID = d.WERKS
AND loc.ORACLE_TYPE = 'W'
LEFT JOIN XREF_SUPS xs
ON xs.SAP_ID = h.LIFNR
AND xs.SAP_ORG_UNIT = h.BUKRS
)
SELECT
src.SRC_ROWID,
src.ORG_NUM,
src.PROD_IT_NUM,
src.DAY_DT,
src.SUPPLIER_NUM,
src.PURCHASE_ORDER_ID,
src.INVOICE_ID,
src.INVOICE_QTY,
src.INVOICE_UNIT_COST_AMT_LCL,
src.PO_UNIT_COST_AMT_LCL,
src.DATASOURCE_NUM_ID,
src.DOC_CURR_CODE,
src.PROD_IT_NUM
|| '~' || src.SUPPLIER_NUM
|| '~' || src.INVOICE_ID
|| '~' || src.PURCHASE_ORDER_ID
|| '~' || src.ORG_NUM AS INTEGRATION_ID,
src.LOC_CURR_CODE
FROM src;
-- ============================================================================
-- SOURCE INDEX
-- ============================================================================
CREATE INDEX IX_DMF_SOURCE__SUPPLIER_INVO_RID
ON DMF_SOURCE__SUPPLIER_INVOICES (
PROD_IT_NUM,
SUPPLIER_NUM,
INVOICE_ID,
PURCHASE_ORDER_ID,
ORG_NUM
);
ALTER TABLE DMF_SOURCE__SUPPLIER_INVOICES NOPARALLEL;
-- Sanity check
SELECT COUNT(*)
FROM DMF_SOURCE__SUPPLIER_INVOICES;
-- ============================================================================
-- STEP 2 — MATERIALIZE DUPLICATE KEYS
-- ============================================================================
DROP TABLE DMF_DUPKEYS__SUPPLIER_INVOICES PURGE;
CREATE TABLE DMF_DUPKEYS__SUPPLIER_INVOICES AS
SELECT
PROD_IT_NUM,
SUPPLIER_NUM,
INVOICE_ID,
PURCHASE_ORDER_ID,
ORG_NUM
FROM DMF_SOURCE__SUPPLIER_INVOICES
GROUP BY
PROD_IT_NUM,
SUPPLIER_NUM,
INVOICE_ID,
PURCHASE_ORDER_ID,
ORG_NUM
HAVING COUNT(*) > 1;
CREATE INDEX IX_DMF_DUPKEYS__SUPPLIER_INV_KEY
ON DMF_DUPKEYS__SUPPLIER_INVOICES (
PROD_IT_NUM,
SUPPLIER_NUM,
INVOICE_ID,
PURCHASE_ORDER_ID,
ORG_NUM
);
-- Sanity check
SELECT COUNT(*)
FROM DMF_DUPKEYS__SUPPLIER_INVOICES;
-- ============================================================================
-- STEP 3 — REJECTED TABLE
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE
'DROP TABLE ORI_SUPPLIER_INVOICES_REJECTED PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
CREATE TABLE ORI_SUPPLIER_INVOICES_REJECTED (
LOG_LVL VARCHAR2(1),
ERROR_MSG VARCHAR2(255),
VALIDATION_RUN_ID VARCHAR2(80),
RULE_ID VARCHAR2(120),
SRC_ROWID VARCHAR2(64),
ORG_NUM VARCHAR2(4000),
PROD_IT_NUM VARCHAR2(4000),
DAY_DT VARCHAR2(4000),
SUPPLIER_NUM VARCHAR2(4000),
PURCHASE_ORDER_ID VARCHAR2(4000),
INVOICE_ID VARCHAR2(4000),
INVOICE_QTY VARCHAR2(4000),
INVOICE_UNIT_COST_AMT_LCL VARCHAR2(4000),
PO_UNIT_COST_AMT_LCL VARCHAR2(4000),
DATASOURCE_NUM_ID VARCHAR2(4000),
DOC_CURR_CODE VARCHAR2(4000),
INTEGRATION_ID VARCHAR2(4000),
LOC_CURR_CODE VARCHAR2(4000),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 4 — VALIDATION / REJECTIONS
-- ============================================================================
INSERT INTO ORI_SUPPLIER_INVOICES_REJECTED (
LOG_LVL,
ERROR_MSG,
VALIDATION_RUN_ID,
RULE_ID,
ORG_NUM,
PROD_IT_NUM,
DAY_DT,
SUPPLIER_NUM,
PURCHASE_ORDER_ID,
INVOICE_ID,
INVOICE_QTY,
INVOICE_UNIT_COST_AMT_LCL,
PO_UNIT_COST_AMT_LCL,
DATASOURCE_NUM_ID,
DOC_CURR_CODE,
INTEGRATION_ID,
LOC_CURR_CODE,
INSERTED_AT
)
WITH s AS (
SELECT
ORG_NUM,
PROD_IT_NUM,
DAY_DT,
SUPPLIER_NUM,
PURCHASE_ORDER_ID,
INVOICE_ID,
INVOICE_QTY,
INVOICE_UNIT_COST_AMT_LCL,
PO_UNIT_COST_AMT_LCL,
DATASOURCE_NUM_ID,
DOC_CURR_CODE,
INTEGRATION_ID,
LOC_CURR_CODE
FROM DMF_SOURCE__SUPPLIER_INVOICES
)
-- ============================================================================
-- INVALID FORMAT — INVOICE ID
-- ============================================================================
SELECT
'E',
'INVALID FORMAT: INVOICE_ID',
'20260611T015538_57e0c98c',
'ERROR_DTYPE_INVOICE_ID',
s.ORG_NUM,
s.PROD_IT_NUM,
s.DAY_DT,
s.SUPPLIER_NUM,
s.PURCHASE_ORDER_ID,
s.INVOICE_ID,
s.INVOICE_QTY,
s.INVOICE_UNIT_COST_AMT_LCL,
s.PO_UNIT_COST_AMT_LCL,
s.DATASOURCE_NUM_ID,
s.DOC_CURR_CODE,
s.INTEGRATION_ID,
s.LOC_CURR_CODE,
SYSDATE
FROM s
WHERE s.INVOICE_ID IS NOT NULL
AND LENGTHB(TO_CHAR(s.INVOICE_ID)) > 30
UNION ALL
-- ============================================================================
-- PROD_IT_NUM NULL
-- ============================================================================
SELECT
'E',
'PROD_IT_NUM IS NULL',
'20260611T015538_57e0c98c',
'ERROR_INVALID_NULL',
s.ORG_NUM,
s.PROD_IT_NUM,
s.DAY_DT,
s.SUPPLIER_NUM,
s.PURCHASE_ORDER_ID,
s.INVOICE_ID,
s.INVOICE_QTY,
s.INVOICE_UNIT_COST_AMT_LCL,
s.PO_UNIT_COST_AMT_LCL,
s.DATASOURCE_NUM_ID,
s.DOC_CURR_CODE,
s.INTEGRATION_ID,
s.LOC_CURR_CODE,
SYSDATE
FROM s
WHERE s.PROD_IT_NUM IS NULL
UNION ALL
-- ============================================================================
-- MISSING ITEM
-- ============================================================================
SELECT
'E',
'ITEM NOT FOUND: ITEM_MASTER_CTRL',
'20260611T015538_57e0c98c',
'ERROR_MISSING_ITEM',
s.ORG_NUM,
s.PROD_IT_NUM,
s.DAY_DT,
s.SUPPLIER_NUM,
s.PURCHASE_ORDER_ID,
s.INVOICE_ID,
s.INVOICE_QTY,
s.INVOICE_UNIT_COST_AMT_LCL,
s.PO_UNIT_COST_AMT_LCL,
s.DATASOURCE_NUM_ID,
s.DOC_CURR_CODE,
s.INTEGRATION_ID,
s.LOC_CURR_CODE,
SYSDATE
FROM s
WHERE s.PROD_IT_NUM IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM ITEM_MASTER_CTRL d
WHERE d.ITEM = s.PROD_IT_NUM
AND d.CTRL_STATUS = 'C'
)
UNION ALL
-- ============================================================================
-- SUPPLIER NULL
-- ============================================================================
SELECT
'E',
'SUPPLIER_NUM IS NULL',
'20260611T015538_57e0c98c',
'ERROR_INVALID_NULL',
s.ORG_NUM,
s.PROD_IT_NUM,
s.DAY_DT,
s.SUPPLIER_NUM,
s.PURCHASE_ORDER_ID,
s.INVOICE_ID,
s.INVOICE_QTY,
s.INVOICE_UNIT_COST_AMT_LCL,
s.PO_UNIT_COST_AMT_LCL,
s.DATASOURCE_NUM_ID,
s.DOC_CURR_CODE,
s.INTEGRATION_ID,
s.LOC_CURR_CODE,
SYSDATE
FROM s
WHERE s.SUPPLIER_NUM IS NULL
UNION ALL
-- ============================================================================
-- INVOICE ID NULL
-- ============================================================================
SELECT
'E',
'INVOICE_ID IS NULL',
'20260611T015538_57e0c98c',
'ERROR_INVALID_NULL',
s.ORG_NUM,
s.PROD_IT_NUM,
s.DAY_DT,
s.SUPPLIER_NUM,
s.PURCHASE_ORDER_ID,
s.INVOICE_ID,
s.INVOICE_QTY,
s.INVOICE_UNIT_COST_AMT_LCL,
s.PO_UNIT_COST_AMT_LCL,
s.DATASOURCE_NUM_ID,
s.DOC_CURR_CODE,
s.INTEGRATION_ID,
s.LOC_CURR_CODE,
SYSDATE
FROM s
WHERE s.INVOICE_ID IS NULL
UNION ALL
-- ============================================================================
-- PURCHASE ORDER NULL
-- ============================================================================
SELECT
'E',
'PURCHASE_ORDER_ID IS NULL',
'20260611T015538_57e0c98c',
'ERROR_INVALID_NULL',
s.ORG_NUM,
s.PROD_IT_NUM,
s.DAY_DT,
s.SUPPLIER_NUM,
s.PURCHASE_ORDER_ID,
s.INVOICE_ID,
s.INVOICE_QTY,
s.INVOICE_UNIT_COST_AMT_LCL,
s.PO_UNIT_COST_AMT_LCL,
s.DATASOURCE_NUM_ID,
s.DOC_CURR_CODE,
s.INTEGRATION_ID,
s.LOC_CURR_CODE,
SYSDATE
FROM s
WHERE s.PURCHASE_ORDER_ID IS NULL
UNION ALL
-- ============================================================================
-- LOCATION NULL
-- ============================================================================
SELECT
'E',
'ORG_NUM IS NULL',
'20260611T015538_57e0c98c',
'ERROR_INVALID_NULL',
s.ORG_NUM,
s.PROD_IT_NUM,
s.DAY_DT,
s.SUPPLIER_NUM,
s.PURCHASE_ORDER_ID,
s.INVOICE_ID,
s.INVOICE_QTY,
s.INVOICE_UNIT_COST_AMT_LCL,
s.PO_UNIT_COST_AMT_LCL,
s.DATASOURCE_NUM_ID,
s.DOC_CURR_CODE,
s.INTEGRATION_ID,
s.LOC_CURR_CODE,
SYSDATE
FROM s
WHERE s.ORG_NUM IS NULL
UNION ALL
-- ============================================================================
-- DUPLICATE KEY
-- ============================================================================
SELECT
'E',
'DUPLICATE KEY: PROD_IT_NUM, SUPPLIER_NUM, INVOICE_ID, PURCHASE_ORDER_ID, ORG_NUM',
'20260611T015538_57e0c98c',
'ERR_DUP_KEY_CONFLICT',
s.ORG_NUM,
s.PROD_IT_NUM,
s.DAY_DT,
s.SUPPLIER_NUM,
s.PURCHASE_ORDER_ID,
s.INVOICE_ID,
s.INVOICE_QTY,
s.INVOICE_UNIT_COST_AMT_LCL,
s.PO_UNIT_COST_AMT_LCL,
s.DATASOURCE_NUM_ID,
s.DOC_CURR_CODE,
s.INTEGRATION_ID,
s.LOC_CURR_CODE,
SYSDATE
FROM s
WHERE (
s.PROD_IT_NUM,
s.SUPPLIER_NUM,
s.INVOICE_ID,
s.PURCHASE_ORDER_ID,
s.ORG_NUM
) IN (
SELECT
PROD_IT_NUM,
SUPPLIER_NUM,
INVOICE_ID,
PURCHASE_ORDER_ID,
ORG_NUM
FROM DMF_DUPKEYS__SUPPLIER_INVOICES
);
COMMIT;
-- ============================================================================
-- STEP 5 — BUILD ORI_SUPPLIER_INVOICES_TMP
-- ============================================================================
DROP TABLE ORI_SUPPLIER_INVOICES_TMP PURGE;
CREATE TABLE ORI_SUPPLIER_INVOICES_TMP (
ORG_NUM VARCHAR2(80),
PROD_IT_NUM VARCHAR2(80),
DAY_DT DATE,
SUPPLIER_NUM VARCHAR2(80),
PURCHASE_ORDER_ID VARCHAR2(30),
INVOICE_ID VARCHAR2(30),
INVOICE_QTY NUMBER,
INVOICE_UNIT_COST_AMT_LCL NUMBER,
PO_UNIT_COST_AMT_LCL NUMBER,
DATASOURCE_NUM_ID NUMBER,
DOC_CURR_CODE VARCHAR2(30),
INTEGRATION_ID VARCHAR2(80),
LOC_CURR_CODE VARCHAR2(30),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 6 — POPULATE TMP
-- ============================================================================
INSERT INTO ORI_SUPPLIER_INVOICES_TMP (
ORG_NUM,
PROD_IT_NUM,
DAY_DT,
SUPPLIER_NUM,
PURCHASE_ORDER_ID,
INVOICE_ID,
INVOICE_QTY,
INVOICE_UNIT_COST_AMT_LCL,
PO_UNIT_COST_AMT_LCL,
DATASOURCE_NUM_ID,
DOC_CURR_CODE,
INTEGRATION_ID,
LOC_CURR_CODE,
INSERTED_AT
)
WITH s AS (
SELECT
SRC_ROWID,
ORG_NUM,
PROD_IT_NUM,
DAY_DT,
SUPPLIER_NUM,
PURCHASE_ORDER_ID,
INVOICE_ID,
INVOICE_QTY,
INVOICE_UNIT_COST_AMT_LCL,
PO_UNIT_COST_AMT_LCL,
DATASOURCE_NUM_ID,
DOC_CURR_CODE,
INTEGRATION_ID,
LOC_CURR_CODE
FROM DMF_SOURCE__SUPPLIER_INVOICES
)
SELECT
s.ORG_NUM,
s.PROD_IT_NUM,
s.DAY_DT,
s.SUPPLIER_NUM,
s.PURCHASE_ORDER_ID,
s.INVOICE_ID,
s.INVOICE_QTY,
s.INVOICE_UNIT_COST_AMT_LCL,
s.PO_UNIT_COST_AMT_LCL,
s.DATASOURCE_NUM_ID,
s.DOC_CURR_CODE,
s.INTEGRATION_ID,
s.LOC_CURR_CODE,
SYSDATE
FROM s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_SUPPLIER_INVOICES_REJECTED r
WHERE r.VALIDATION_RUN_ID = '20260611T015538_57e0c98c'
AND r.LOG_LVL = 'E'
AND r.SRC_ROWID = s.SRC_ROWID
)
AND s.PROD_IT_NUM IS NOT NULL
AND s.SUPPLIER_NUM IS NOT NULL
AND s.INVOICE_ID IS NOT NULL
AND s.PURCHASE_ORDER_ID IS NOT NULL
AND s.ORG_NUM IS NOT NULL;
COMMIT;
SELECT COUNT(*) AS STG_ROWS
FROM ORI_SUPPLIER_INVOICES_TMP;
-- ============================================================================
-- STEP 7 — BUILD CTRL
-- ============================================================================
DROP TABLE ORI_SUPPLIER_INVOICES_CTRL PURGE;
CREATE TABLE ORI_SUPPLIER_INVOICES_CTRL (
ORG_NUM VARCHAR2(80),
PROD_IT_NUM VARCHAR2(80),
DAY_DT DATE,
SUPPLIER_NUM VARCHAR2(80),
PURCHASE_ORDER_ID VARCHAR2(30),
INVOICE_ID VARCHAR2(30),
INVOICE_QTY NUMBER,
INVOICE_UNIT_COST_AMT_LCL NUMBER,
PO_UNIT_COST_AMT_LCL NUMBER,
EXCHANGE_DT DATE,
AUX1_CHANGED_ON_DT DATE,
AUX2_CHANGED_ON_DT DATE,
AUX3_CHANGED_ON_DT DATE,
AUX4_CHANGED_ON_DT DATE,
CHANGED_BY_ID VARCHAR2(80),
CHANGED_ON_DT DATE,
CREATED_BY_ID VARCHAR2(80),
CREATED_ON_DT DATE,
DATASOURCE_NUM_ID NUMBER,
DELETE_FLG CHAR(1),
DOC_CURR_CODE VARCHAR2(30),
ETL_THREAD_VAL NUMBER,
GLOBAL1_EXCHANGE_RATE NUMBER,
GLOBAL2_EXCHANGE_RATE NUMBER,
GLOBAL3_EXCHANGE_RATE NUMBER,
INTEGRATION_ID VARCHAR2(80),
LOC_CURR_CODE VARCHAR2(30),
LOC_EXCHANGE_RATE NUMBER,
TENANT_ID VARCHAR2(80),
X_CUSTOM VARCHAR2(10),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 8 — POPULATE CTRL
-- ============================================================================
INSERT INTO ORI_SUPPLIER_INVOICES_CTRL (
ORG_NUM,
PROD_IT_NUM,
DAY_DT,
SUPPLIER_NUM,
PURCHASE_ORDER_ID,
INVOICE_ID,
INVOICE_QTY,
INVOICE_UNIT_COST_AMT_LCL,
PO_UNIT_COST_AMT_LCL,
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,
INSERTED_AT
)
SELECT
s.ORG_NUM,
s.PROD_IT_NUM,
s.DAY_DT,
s.SUPPLIER_NUM,
s.PURCHASE_ORDER_ID,
s.INVOICE_ID,
s.INVOICE_QTY,
s.INVOICE_UNIT_COST_AMT_LCL,
s.PO_UNIT_COST_AMT_LCL,
CAST(NULL AS DATE) AS EXCHANGE_DT,
CAST(NULL AS DATE) AS AUX1_CHANGED_ON_DT,
CAST(NULL AS DATE) AS AUX2_CHANGED_ON_DT,
CAST(NULL AS DATE) AS AUX3_CHANGED_ON_DT,
CAST(NULL AS DATE) AS AUX4_CHANGED_ON_DT,
CAST(NULL AS VARCHAR2(80)) AS CHANGED_BY_ID,
CAST(NULL AS DATE) AS CHANGED_ON_DT,
CAST(NULL AS VARCHAR2(80)) AS CREATED_BY_ID,
CAST(NULL AS DATE) AS CREATED_ON_DT,
s.DATASOURCE_NUM_ID,
CAST(NULL AS CHAR(1)) AS DELETE_FLG,
s.DOC_CURR_CODE,
CAST(NULL AS NUMBER) AS ETL_THREAD_VAL,
CAST(NULL AS NUMBER) AS GLOBAL1_EXCHANGE_RATE,
CAST(NULL AS NUMBER) AS GLOBAL2_EXCHANGE_RATE,
CAST(NULL AS NUMBER) AS GLOBAL3_EXCHANGE_RATE,
s.INTEGRATION_ID,
s.LOC_CURR_CODE,
CAST(NULL AS NUMBER) AS LOC_EXCHANGE_RATE,
CAST(NULL AS VARCHAR2(80)) AS TENANT_ID,
CAST(NULL AS VARCHAR2(10)) AS X_CUSTOM,
SYSDATE
FROM ORI_SUPPLIER_INVOICES_TMP s;
COMMIT;
SELECT COUNT(*) AS CTRL_ROWS
FROM ORI_SUPPLIER_INVOICES_CTRL;
-- ============================================================================
-- STEP 9 — REJECTION SUMMARY
-- ============================================================================
SELECT
RULE_ID,
LOG_LVL,
COUNT(*) AS REJECTED_ROWS
FROM ORI_SUPPLIER_INVOICES_REJECTED
GROUP BY
RULE_ID,
LOG_LVL
ORDER BY
REJECTED_ROWS DESC;
-- ============================================================================
-- OPTIONAL — REJECTION DETAILS
-- ============================================================================
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM ORI_SUPPLIER_INVOICES_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;