Source: DMF/PuC/AIF/FelipeBkp/supplier_invoices_delta.sql
-- ============================================================================
-- SUPPLIER INVOICES DELTA — Full Validation Pipeline
-- ============================================================================
-- Fact: supplier_invoices_delta
-- Source: PR1_SUPPLIER_INVOICES_HEADER_DELTA_RAW
-- Rejected: ORI_SUPPLIER_INVOICES_DELTA_REJECTED
-- STG (accepted): ORI_SUPPLIER_INVOICES_DELTA_TMP
-- Rules: 3
-- Pipeline: RAW → REJECTED → STG → CTRL
--
-- How to run in SQL Developer:
-- Open this file → F5 (Run as Script)
-- Each step can also be highlighted and run independently with F9.
--
-- Prerequisites:
-- SAP SLT schemas SAP_SLT_PROD_PR1 / SAP_SLT_PROD_PV1 reachable
-- STEP 0 below lands the four *_DELTA_RAW tables — it is the only manual step
-- XREF_ITEM_IBC_RETAIL built by ../ItemPR1_PV1.sql
-- XREF_LOCATION built by ../Stores_WH.sql
-- XREF_SUPS built by ../Supplier_NonMerch.sql
--
-- Collapsed from phase1/phase2/phase3 playbooks generated 2026-06-11T01:55:38.
-- Phase 3 contained phases 1 and 2 verbatim; the per-rule DIAGNOSTIC
-- ALTERNATIVE block duplicated the unified INSERT above it and was removed.
-- ============================================================================
ALTER SESSION SET DDL_LOCK_TIMEOUT = 300;
-- ============================================================================
-- STEP 0 — SAP SLT extract (external prerequisite; lands the *_DELTA_RAW tables)
-- ============================================================================
-- BACKUP-BEFORE-REPLACE: each RAW table is RENAMEd to <name>_BKP before the
-- fresh load, so the previous wave is always retained (one generation). The
-- prior _BKP is dropped first. Each step runs in a PL/SQL block that ignores
-- ORA-00942 (table absent on first run), so the whole file is F5-safe.
-- ---------------------------------------------------------------------------
-- PR1 header
-- ---------------------------------------------------------------------------
BEGIN EXECUTE IMMEDIATE 'DROP TABLE PR1_SUPPLIER_INVOICES_HEADER_DELTA_RAW_BKP PURGE';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/
BEGIN EXECUTE IMMEDIATE 'RENAME PR1_SUPPLIER_INVOICES_HEADER_DELTA_RAW TO PR1_SUPPLIER_INVOICES_HEADER_DELTA_RAW_BKP';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/
CREATE TABLE PR1_SUPPLIER_INVOICES_HEADER_DELTA_RAW NOLOGGING PARALLEL 16 AS
SELECT /*+ PARALLEL(16) */ RBKP.*
FROM SAP_SLT_PROD_PR1.RBKP RBKP
WHERE RBKP.BLDAT >= '20241027';
-- ---------------------------------------------------------------------------
-- PV1 header
-- ---------------------------------------------------------------------------
BEGIN EXECUTE IMMEDIATE 'DROP TABLE PV1_SUPPLIER_INVOICES_HEADER_DELTA_RAW_BKP PURGE';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/
BEGIN EXECUTE IMMEDIATE 'RENAME PV1_SUPPLIER_INVOICES_HEADER_DELTA_RAW TO PV1_SUPPLIER_INVOICES_HEADER_DELTA_RAW_BKP';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/
CREATE TABLE PV1_SUPPLIER_INVOICES_HEADER_DELTA_RAW NOLOGGING PARALLEL 16 AS
SELECT /*+ PARALLEL(16) */ RBKP.*
FROM SAP_SLT_PROD_PV1.RBKP RBKP
WHERE RBKP.BLDAT >= '20241027';
-- ---------------------------------------------------------------------------
-- PR1 detail (RSEG + EKPO.NETPR / PEINH)
-- ---------------------------------------------------------------------------
BEGIN EXECUTE IMMEDIATE 'DROP TABLE PR1_SUPPLIER_INVOICES_DETAIL_DELTA_RAW_BKP PURGE';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/
BEGIN EXECUTE IMMEDIATE 'RENAME PR1_SUPPLIER_INVOICES_DETAIL_DELTA_RAW TO PR1_SUPPLIER_INVOICES_DETAIL_DELTA_RAW_BKP';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/
CREATE TABLE PR1_SUPPLIER_INVOICES_DETAIL_DELTA_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
WHERE RBKP.BLDAT >= '20241027';
-- ---------------------------------------------------------------------------
-- PV1 detail (RSEG + EKPO.NETPR / PEINH) [was mislabeled PR1 in the source note]
-- ---------------------------------------------------------------------------
BEGIN EXECUTE IMMEDIATE 'DROP TABLE PV1_SUPPLIER_INVOICES_DETAIL_DELTA_RAW_BKP PURGE';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/
BEGIN EXECUTE IMMEDIATE 'RENAME PV1_SUPPLIER_INVOICES_DETAIL_DELTA_RAW TO PV1_SUPPLIER_INVOICES_DETAIL_DELTA_RAW_BKP';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/
CREATE TABLE PV1_SUPPLIER_INVOICES_DETAIL_DELTA_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
WHERE RBKP.BLDAT >= '20260427';
-- ---------------------------------------------------------------------------
-- Row counts: fresh extract vs retained backup
-- ---------------------------------------------------------------------------
SELECT 'PR1_HDR' t, COUNT(*) n FROM PR1_SUPPLIER_INVOICES_HEADER_DELTA_RAW
UNION ALL SELECT 'PV1_HDR', COUNT(*) FROM PV1_SUPPLIER_INVOICES_HEADER_DELTA_RAW
UNION ALL SELECT 'PR1_DTL', COUNT(*) FROM PR1_SUPPLIER_INVOICES_DETAIL_DELTA_RAW
UNION ALL SELECT 'PV1_DTL', COUNT(*) FROM PV1_SUPPLIER_INVOICES_DETAIL_DELTA_RAW;
-- ============================================================================
-- STEP 1 — Materialize normalized source into DMF_SOURCE__SUPPLIER_INVOICES_
-- ============================================================================
-- One scan of RAW now → all subsequent rules read from a smaller, hotter,
-- indexed snapshot. Source CTAS override applied (column renames / JOINs).
DROP TABLE DMF_SOURCE__SUPPLIER_INVOICES_ PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_SOURCE__SUPPLIER_INVOICES_ PARALLEL 4 AS
WITH src AS (
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_DELTA_RAW d
JOIN PR1_SUPPLIER_INVOICES_HEADER_DELTA_RAW h ON h.BELNR = d.BELNR AND h.GJAHR = d.GJAHR
--LEFT JOIN
JOIN XREF_LOCATION loc ON loc.SAP_ID = d.WERKS AND loc.ORACLE_TYPE = 'W'
JOIN XREF_ITEM_IBC_RETAIL xit ON xit.ITEM_PR1 = d.MATNR
JOIN XREF_SUPS xs ON xs.SAP_ID = h.LIFNR AND xs.SAP_ORG_UNIT = h.BUKRS
UNION ALL
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_DELTA_RAW d
JOIN PV1_SUPPLIER_INVOICES_HEADER_DELTA_RAW h ON h.BELNR = d.BELNR AND h.GJAHR = d.GJAHR
--LEFT JOIN
JOIN XREF_LOCATION loc ON loc.SAP_ID = d.WERKS AND loc.ORACLE_TYPE = 'W'
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;
-- Index on the row-identity tuple makes duplicate_key and the STG
-- anti-join cheap.
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);
-- Reset parallel attribute so per-rule reads don't auto-parallelize.
ALTER TABLE DMF_SOURCE__SUPPLIER_INVOICES_ NOPARALLEL;
-- Sanity check:
SELECT COUNT(*) FROM DMF_SOURCE__SUPPLIER_INVOICES_;
-- ============================================================================
-- STEP 2 — Materialize duplicate-key set into DMF_DUPKEYS__SUPPLIER_INVOICES
-- ============================================================================
-- One GROUP BY HAVING over the source now → the duplicate_key rule
-- becomes a cheap semi-join against this small indexed table.
DROP TABLE DMF_DUPKEYS__SUPPLIER_INVOICES PURGE; -- ignore ORA-00942 on first run
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 (size of the conflict set):
SELECT COUNT(*) FROM DMF_DUPKEYS__SUPPLIER_INVOICES;
-- ============================================================================
-- STEP 3 — Ensure ORI_SUPPLIER_INVOICES_DELTA_REJECTED exists, then empty it
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ORI_SUPPLIER_INVOICES_DELTA_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) )';
EXCEPTION WHEN OTHERS THEN
IF SQLCODE != -955 THEN RAISE; END IF; -- -955 = table already exists
END;
/
TRUNCATE TABLE ORI_SUPPLIER_INVOICES_DELTA_REJECTED;
-- If TRUNCATE fails with ORA-00054 (orphan lock), use DELETE instead:
-- DELETE FROM ORI_SUPPLIER_INVOICES_DELTA_REJECTED;
-- COMMIT;
-- ============================================================================
-- STEP 4 — Unified UNION ALL INSERT (single source scan)
-- ============================================================================
-- One INSERT covers every rule via UNION ALL. With the source CTE
-- materialized into DMF_SOURCE__<fact>, Oracle scans the snapshot once
-- and evaluates each rule's predicate against the buffered table.
INSERT INTO ORI_SUPPLIER_INVOICES_DELTA_REJECTED (
LOG_LVL, ERROR_MSG, VALIDATION_RUN_ID, RULE_ID, 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, 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 'E', 'INVALID FORMAT: INVOICE_ID', '20260611T015538_57e0c98c', 'ERROR_DTYPE_INVOICE_ID', s.SRC_ROWID, 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 INVOICE_ID IS NOT NULL AND LENGTHB(TO_CHAR(INVOICE_ID)) > 30
UNION ALL
SELECT 'E', 'PROD_IT_NUM IS NULL', '20260611T015538_57e0c98c', 'ERROR_INVALID_NULL', s.SRC_ROWID, 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 PROD_IT_NUM IS NULL
UNION ALL
SELECT 'E', 'SUPPLIER_NUM IS NULL', '20260611T015538_57e0c98c', 'ERROR_INVALID_NULL', s.SRC_ROWID, 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 SUPPLIER_NUM IS NULL
UNION ALL
SELECT 'E', 'INVOICE_ID IS NULL', '20260611T015538_57e0c98c', 'ERROR_INVALID_NULL', s.SRC_ROWID, 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 INVOICE_ID IS NULL
UNION ALL
SELECT 'E', 'PURCHASE_ORDER_ID IS NULL', '20260611T015538_57e0c98c', 'ERROR_INVALID_NULL', s.SRC_ROWID, 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 PURCHASE_ORDER_ID IS NULL
UNION ALL
SELECT 'E', 'ORG_NUM IS NULL', '20260611T015538_57e0c98c', 'ERROR_INVALID_NULL', s.SRC_ROWID, 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 ORG_NUM IS NULL
UNION ALL
SELECT 'E', 'DUPLICATE KEY: PROD_IT_NUM, SUPPLIER_NUM, INVOICE_ID, PURCHASE_ORDER_ID, ORG_NUM', '20260611T015538_57e0c98c', 'ERR_DUP_KEY_CONFLICT', s.SRC_ROWID, 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 (PROD_IT_NUM, SUPPLIER_NUM, INVOICE_ID, PURCHASE_ORDER_ID, 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 typed ORI_SUPPLIER_INVOICES_DELTA_TMP (drop-and-recreate)
-- ============================================================================
-- DDL types are pulled from the RAP spec (not VARCHAR2(4000) blanket).
DROP TABLE ORI_SUPPLIER_INVOICES_DELTA_TMP PURGE; -- ignore ORA-00942
CREATE TABLE ORI_SUPPLIER_INVOICES_DELTA_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 ORI_SUPPLIER_INVOICES_DELTA_TMP (anti-join on SRC_ROWID; LOG_LVL='W' rows still flow through)
-- ============================================================================
INSERT INTO ORI_SUPPLIER_INVOICES_DELTA_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_DELTA_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;
-- Row count check: should be COUNT(PR1_SUPPLIER_INVOICES_HEADER_DELTA_RAW) minus distinct SRC_ROWIDs in ORI_SUPPLIER_INVOICES_DELTA_REJECTED where LOG_LVL='E'.
SELECT COUNT(*) AS stg_rows FROM ORI_SUPPLIER_INVOICES_DELTA_TMP;
-- ============================================================================
-- STEP 7 — Build ORI_SUPPLIER_INVOICES_DELTA_CTRL (drop-and-recreate)
-- ============================================================================
-- Final Oracle export shape. Column projection comes from
-- `[ctrl_projection]` in the override; default = identity from STG.
DROP TABLE ORI_SUPPLIER_INVOICES_DELTA_CTRL PURGE; -- ignore ORA-00942
CREATE TABLE ORI_SUPPLIER_INVOICES_DELTA_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 ORI_SUPPLIER_INVOICES_DELTA_CTRL from ORI_SUPPLIER_INVOICES_DELTA_TMP
-- ============================================================================
INSERT INTO ORI_SUPPLIER_INVOICES_DELTA_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), CAST(NULL AS DATE), CAST(NULL AS DATE), CAST(NULL AS DATE), CAST(NULL AS DATE), CAST(NULL AS VARCHAR2(80)), CAST(NULL AS DATE), CAST(NULL AS VARCHAR2(80)), CAST(NULL AS DATE), s.DATASOURCE_NUM_ID, CAST(NULL AS CHAR(1)), s.DOC_CURR_CODE, CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), s.INTEGRATION_ID, s.LOC_CURR_CODE, CAST(NULL AS NUMBER), CAST(NULL AS VARCHAR2(80)), CAST(NULL AS VARCHAR2(10)), SYSDATE
FROM ORI_SUPPLIER_INVOICES_DELTA_TMP s;
COMMIT;
SELECT COUNT(*) AS ctrl_rows FROM ORI_SUPPLIER_INVOICES_DELTA_CTRL;
-- ============================================================================
-- STEP 9 — Rejection summary by rule
-- ============================================================================
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM ORI_SUPPLIER_INVOICES_DELTA_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;