Source: DMF/PuC/AIF/FelipeBkp/deals_delta.sql
-- ============================================================================
-- DEALS DELTA — Full Validation Pipeline
-- ============================================================================
-- Fact: deals_delta
-- Source: ORI_DEALS_HISTORY_DELTA_RAW
-- Rejected: ORI_DEALS_HISTORY_DELTA_REJECTED
-- STG (accepted): ORI_DEAL_INCOME_DELTA_STG
-- Rules: 12
-- 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.
--
-- Collapsed from phase1/phase2/phase3 playbooks generated 2026-06-10T21:07:55.
-- 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 1 — Rebuild validity materialization tables (run once / when stale)
-- ============================================================================
-- The framework caches these tables across runs (the underlying
-- master-data joins are expensive but the master data changes rarely).
-- ONLY run this block when:
-- • first build on a fresh schema,
-- • the [dimensions.<name>] validity_query was edited,
-- • the master-data tables were reloaded.
-- Dimension 'organization_csv' → DMF_VALIDITY__ORGANIZATION_CSV
DROP TABLE DMF_VALIDITY__ORGANIZATION_CSV PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_VALIDITY__ORGANIZATION_CSV AS
SELECT DISTINCT TO_CHAR(STORE) AS org_num
FROM STORE_ADD_CTRL
WHERE ctrl_status = 'C';
CREATE INDEX IX_DMF_VALIDITY__ORGANIZATION_ ON DMF_VALIDITY__ORGANIZATION_CSV (ORG_NUM);
-- Dimension 'organization_deals' → DMF_VALIDITY__ORGANIZAT_5EBC71
DROP TABLE DMF_VALIDITY__ORGANIZAT_5EBC71 PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_VALIDITY__ORGANIZAT_5EBC71 AS
SELECT DISTINCT TO_CHAR(store) AS org_num
FROM STORE_ADD_CTRL
WHERE ctrl_status = 'C'
UNION
SELECT DISTINCT TO_CHAR(wc.WH)
FROM wh_ctrl wc, xref_wh wx
WHERE wc.wh = LTRIM(TO_CHAR(wx.WH_SLT), '0');
CREATE INDEX IX_DMF_VALIDITY__ORGANIZAT_5EB ON DMF_VALIDITY__ORGANIZAT_5EBC71 (ORG_NUM);
-- Verify the validity tables populated:
SELECT 'DMF_VALIDITY__ORGANIZATION_CSV' AS dim, COUNT(*) FROM DMF_VALIDITY__ORGANIZATION_CSV;
SELECT 'DMF_VALIDITY__ORGANIZAT_5EBC71' AS dim, COUNT(*) FROM DMF_VALIDITY__ORGANIZAT_5EBC71;
-- ============================================================================
-- STEP 2 — Materialize normalized source into DMF_SOURCE__DEALS_DELTA
-- ============================================================================
-- 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__DEALS_DELTA PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_SOURCE__DEALS_DELTA PARALLEL 4 AS
WITH raw_src AS (
SELECT /*+ PARALLEL(4) */
ROWID AS SRC_ROWID,
r.ITEM,
LTRIM(r.ORG_NUM, '0') AS ORG_NUM_KEY,
r.DAY_DT,
r.DEAL_PURCH_QTY,
r.DEAL_PURCH_COST_AMT_LCL,
r.COST_VARIANCE_AMOUNT,
r.INVENTORY_AMOUNT,
r.COST_VARIANCE_QTY,
r.INVENTORY_QTY,
r.LOC_CURR_CODE,
r.DOC_CURR_CODE
FROM ORI_DEALS_HISTORY_DELTA_RAW r
),
wh_map AS (
SELECT DISTINCT
wc.WH AS WH_FINAL,
LTRIM(TO_CHAR(wx.WH_SLT), '0') AS WH_KEY
FROM wh_ctrl wc, xref_wh wx
WHERE wc.wh = LTRIM(TO_CHAR(wx.WH_SLT), '0')
)
SELECT
s.SRC_ROWID,
s.ITEM,
CASE WHEN w.WH_FINAL IS NOT NULL
THEN TO_CHAR(w.WH_FINAL)
ELSE s.ORG_NUM_KEY
END AS ORG_NUM,
s.DAY_DT,
CAST(NULL AS NUMBER) AS DEAL_QTY,
CAST(NULL AS NUMBER) AS DEAL_COST_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_RTL_AMT_LCL,
s.DEAL_PURCH_QTY,
s.DEAL_PURCH_COST_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_PURCH_RTL_AMT_LCL,
CAST(NULL AS VARCHAR2(1)) AS DELETE_FLG,
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.LOC_CURR_CODE,
CAST(NULL AS NUMBER) AS LOC_EXCHANGE_RATE,
s.DOC_CURR_CODE,
CAST(NULL AS NUMBER) AS ETL_THREAD_VAL,
CAST(NULL AS NUMBER) AS DATASOURCE_NUM_ID,
s.COST_VARIANCE_AMOUNT AS FLEX1_NUM_VALUE,
s.INVENTORY_AMOUNT AS FLEX2_NUM_VALUE,
s.COST_VARIANCE_QTY AS FLEX3_NUM_VALUE,
s.INVENTORY_QTY AS FLEX4_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX5_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX6_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX7_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX8_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX9_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX10_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX11_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX12_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX13_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX14_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX15_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX16_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX17_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX18_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX19_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX20_NUM_VALUE,
CAST(NULL AS VARCHAR2(30)) AS DEAL_ID,
CAST(NULL AS NUMBER) AS DEAL_FIXED_QTY,
CAST(NULL AS NUMBER) AS DEAL_FIXED_COST_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_FIXED_RTL_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_ISSUE_QTY,
CAST(NULL AS NUMBER) AS DEAL_ISSUE_COST_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_ISSUE_RTL_AMT_LCL
FROM raw_src s
LEFT JOIN wh_map w ON w.WH_KEY = s.ORG_NUM_KEY;
-- Index on the row-identity tuple makes duplicate_key and the STG
-- anti-join cheap.
CREATE INDEX IX_DMF_SOURCE__DEALS_DELTA_RID ON DMF_SOURCE__DEALS_DELTA (ITEM, ORG_NUM, DAY_DT);
-- Reset parallel attribute so per-rule reads don't auto-parallelize.
ALTER TABLE DMF_SOURCE__DEALS_DELTA NOPARALLEL;
-- Sanity check:
SELECT COUNT(*) FROM DMF_SOURCE__DEALS_DELTA;
-- ============================================================================
-- STEP 3 — Materialize duplicate-key set into DMF_DUPKEYS__DEALS_DELTA
-- ============================================================================
-- 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__DEALS_DELTA PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_DUPKEYS__DEALS_DELTA AS
SELECT ITEM, ORG_NUM, DAY_DT FROM DMF_SOURCE__DEALS_DELTA
GROUP BY ITEM, ORG_NUM, DAY_DT
HAVING COUNT(*) > 1;
CREATE INDEX IX_DMF_DUPKEYS__DEALS_DELTA_KEY ON DMF_DUPKEYS__DEALS_DELTA (ITEM, ORG_NUM, DAY_DT);
-- Sanity check (size of the conflict set):
SELECT COUNT(*) FROM DMF_DUPKEYS__DEALS_DELTA;
-- ============================================================================
-- STEP 4 — Ensure ORI_DEALS_HISTORY_DELTA_REJECTED exists, then empty it
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ORI_DEALS_HISTORY_DELTA_REJECTED ( LOG_LVL VARCHAR2(1), ERROR_MSG VARCHAR2(255), VALIDATION_RUN_ID VARCHAR2(80), RULE_ID VARCHAR2(120), SRC_ROWID VARCHAR2(64), ITEM VARCHAR2(4000), ORG_NUM VARCHAR2(4000), DAY_DT VARCHAR2(4000), DEAL_QTY VARCHAR2(4000), DEAL_COST_AMT_LCL VARCHAR2(4000), DEAL_RTL_AMT_LCL VARCHAR2(4000), DEAL_PURCH_QTY VARCHAR2(4000), DEAL_PURCH_COST_AMT_LCL VARCHAR2(4000), DEAL_PURCH_RTL_AMT_LCL VARCHAR2(4000), DELETE_FLG VARCHAR2(4000), GLOBAL1_EXCHANGE_RATE VARCHAR2(4000), GLOBAL2_EXCHANGE_RATE VARCHAR2(4000), GLOBAL3_EXCHANGE_RATE VARCHAR2(4000), LOC_CURR_CODE VARCHAR2(4000), LOC_EXCHANGE_RATE VARCHAR2(4000), DOC_CURR_CODE VARCHAR2(4000), ETL_THREAD_VAL VARCHAR2(4000), DATASOURCE_NUM_ID VARCHAR2(4000), FLEX1_NUM_VALUE VARCHAR2(4000), FLEX2_NUM_VALUE VARCHAR2(4000), FLEX3_NUM_VALUE VARCHAR2(4000), FLEX4_NUM_VALUE VARCHAR2(4000), FLEX5_NUM_VALUE VARCHAR2(4000), FLEX6_NUM_VALUE VARCHAR2(4000), FLEX7_NUM_VALUE VARCHAR2(4000), FLEX8_NUM_VALUE VARCHAR2(4000), FLEX9_NUM_VALUE VARCHAR2(4000), FLEX10_NUM_VALUE VARCHAR2(4000), FLEX11_NUM_VALUE VARCHAR2(4000), FLEX12_NUM_VALUE VARCHAR2(4000), FLEX13_NUM_VALUE VARCHAR2(4000), FLEX14_NUM_VALUE VARCHAR2(4000), FLEX15_NUM_VALUE VARCHAR2(4000), FLEX16_NUM_VALUE VARCHAR2(4000), FLEX17_NUM_VALUE VARCHAR2(4000), FLEX18_NUM_VALUE VARCHAR2(4000), FLEX19_NUM_VALUE VARCHAR2(4000), FLEX20_NUM_VALUE VARCHAR2(4000), DEAL_ID VARCHAR2(4000), DEAL_FIXED_QTY VARCHAR2(4000), DEAL_FIXED_COST_AMT_LCL VARCHAR2(4000), DEAL_FIXED_RTL_AMT_LCL VARCHAR2(4000), DEAL_ISSUE_QTY VARCHAR2(4000), DEAL_ISSUE_COST_AMT_LCL VARCHAR2(4000), DEAL_ISSUE_RTL_AMT_LCL 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_DEALS_HISTORY_DELTA_REJECTED;
-- If TRUNCATE fails with ORA-00054 (orphan lock), use DELETE instead:
-- DELETE FROM ORI_DEALS_HISTORY_DELTA_REJECTED;
-- COMMIT;
-- ============================================================================
-- STEP 5 — 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_DEALS_HISTORY_DELTA_REJECTED (
LOG_LVL, ERROR_MSG, VALIDATION_RUN_ID, RULE_ID, SRC_ROWID, ITEM, ORG_NUM, DAY_DT, DEAL_QTY, DEAL_COST_AMT_LCL, DEAL_RTL_AMT_LCL, DEAL_PURCH_QTY, DEAL_PURCH_COST_AMT_LCL, DEAL_PURCH_RTL_AMT_LCL, DELETE_FLG, GLOBAL1_EXCHANGE_RATE, GLOBAL2_EXCHANGE_RATE, GLOBAL3_EXCHANGE_RATE, LOC_CURR_CODE, LOC_EXCHANGE_RATE, DOC_CURR_CODE, ETL_THREAD_VAL, 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, DEAL_ID, DEAL_FIXED_QTY, DEAL_FIXED_COST_AMT_LCL, DEAL_FIXED_RTL_AMT_LCL, DEAL_ISSUE_QTY, DEAL_ISSUE_COST_AMT_LCL, DEAL_ISSUE_RTL_AMT_LCL, INSERTED_AT
)
WITH s AS (
SELECT
SRC_ROWID,
ITEM,
ORG_NUM,
DAY_DT,
DEAL_QTY,
DEAL_COST_AMT_LCL,
DEAL_RTL_AMT_LCL,
DEAL_PURCH_QTY,
DEAL_PURCH_COST_AMT_LCL,
DEAL_PURCH_RTL_AMT_LCL,
DELETE_FLG,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
DOC_CURR_CODE,
ETL_THREAD_VAL,
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,
DEAL_ID,
DEAL_FIXED_QTY,
DEAL_FIXED_COST_AMT_LCL,
DEAL_FIXED_RTL_AMT_LCL,
DEAL_ISSUE_QTY,
DEAL_ISSUE_COST_AMT_LCL,
DEAL_ISSUE_RTL_AMT_LCL
FROM DMF_SOURCE__DEALS_DELTA
)
SELECT 'E', 'INVALID FORMAT: ITEM', '20260610T210755_f3eb717d', 'ERROR_DTYPE_ITEM', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE ITEM IS NOT NULL AND LENGTHB(TO_CHAR(ITEM)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: ORG_NUM', '20260610T210755_f3eb717d', 'ERROR_DTYPE_ORG_NUM', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE ORG_NUM IS NOT NULL AND LENGTHB(TO_CHAR(ORG_NUM)) > 30
UNION ALL
SELECT 'E', 'ORG_NUM NOT FOUND: STORE_ADD_CTRL', '20260610T210755_f3eb717d', 'ERROR_MISSING_ORG_NUM', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE ORG_NUM IS NOT NULL AND NOT EXISTS (SELECT 1 FROM DMF_VALIDITY__ORGANIZATION_CSV d WHERE d.ORG_NUM = s.ORG_NUM)
UNION ALL
SELECT 'E', 'INVALID FORMAT: DAY_DT', '20260610T210755_f3eb717d', 'ERROR_DTYPE_DAY_DT', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE DAY_DT IS NOT NULL AND TRUNC(DAY_DT) <> DAY_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: DEAL_PURCH_QTY', '20260610T210755_f3eb717d', 'ERROR_DTYPE_DEAL_PURCH_QTY', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE DEAL_PURCH_QTY IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(DEAL_PURCH_QTY))) >= POWER(10,26) OR TO_NUMBER(DEAL_PURCH_QTY) != ROUND(TO_NUMBER(DEAL_PURCH_QTY),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: DEAL_PURCH_COST_AMT_LCL', '20260610T210755_f3eb717d', 'ERROR_DTYPE_DEAL_PURCH_COST_AMT_LCL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE DEAL_PURCH_COST_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(DEAL_PURCH_COST_AMT_LCL))) >= POWER(10,26) OR TO_NUMBER(DEAL_PURCH_COST_AMT_LCL) != ROUND(TO_NUMBER(DEAL_PURCH_COST_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: LOC_CURR_CODE', '20260610T210755_f3eb717d', 'ERROR_DTYPE_LOC_CURR_CODE', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE LOC_CURR_CODE IS NOT NULL AND LENGTHB(TO_CHAR(LOC_CURR_CODE)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: DOC_CURR_CODE', '20260610T210755_f3eb717d', 'ERROR_DTYPE_DOC_CURR_CODE', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE DOC_CURR_CODE IS NOT NULL AND LENGTHB(TO_CHAR(DOC_CURR_CODE)) > 30
UNION ALL
SELECT 'E', 'ITEM IS NULL', '20260610T210755_f3eb717d', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE ITEM IS NULL
UNION ALL
SELECT 'E', 'ORG_NUM IS NULL', '20260610T210755_f3eb717d', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE ORG_NUM IS NULL
UNION ALL
SELECT 'E', 'DAY_DT IS NULL', '20260610T210755_f3eb717d', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE DAY_DT IS NULL
UNION ALL
SELECT 'E', 'ITEM NOT FOUND: ITEM_MASTER_CTRL', '20260610T210755_f3eb717d', 'ERROR_MISSING_ITEM', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE ITEM IS NOT NULL AND NOT EXISTS (SELECT 1 FROM ITEM_MASTER_CTRL d WHERE d.ITEM = s.ITEM AND CTRL_STATUS = 'C')
UNION ALL
SELECT 'E', 'ORG_NUM NOT FOUND: STORE_ADD_CTRL, WH_CTRL', '20260610T210755_f3eb717d', 'ERROR_INVALID_ORG', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE ORG_NUM IS NOT NULL AND NOT EXISTS (SELECT 1 FROM DMF_VALIDITY__ORGANIZAT_5EBC71 d WHERE d.ORG_NUM = s.ORG_NUM)
UNION ALL
SELECT 'E', 'DUPLICATE KEY: ITEM, ORG_NUM, DAY_DT', '20260610T210755_f3eb717d', 'ERR_DUP_KEY_CONFLICT', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE FROM s WHERE (ITEM, ORG_NUM, DAY_DT) IN (SELECT ITEM, ORG_NUM, DAY_DT FROM DMF_DUPKEYS__DEALS_DELTA);
COMMIT;
-- ============================================================================
-- STEP 6 — Build typed ORI_DEAL_INCOME_DELTA_STG (drop-and-recreate)
-- ============================================================================
-- DDL types are pulled from the RAP spec (not VARCHAR2(4000) blanket).
DROP TABLE ORI_DEAL_INCOME_DELTA_STG PURGE; -- ignore ORA-00942
CREATE TABLE ORI_DEAL_INCOME_DELTA_STG (
ITEM VARCHAR2(80 CHAR),
ORG_NUM VARCHAR2(30 CHAR),
DAY_DT DATE,
DEAL_QTY NUMBER(30,4),
DEAL_COST_AMT_LCL NUMBER(30,4),
DEAL_RTL_AMT_LCL NUMBER(30,4),
DEAL_PURCH_QTY NUMBER(30,4),
DEAL_PURCH_COST_AMT_LCL NUMBER(30,4),
DEAL_PURCH_RTL_AMT_LCL NUMBER(30,4),
DELETE_FLG CHAR(1 CHAR),
GLOBAL1_EXCHANGE_RATE NUMBER(22,7),
GLOBAL2_EXCHANGE_RATE NUMBER(22,7),
GLOBAL3_EXCHANGE_RATE NUMBER(22,7),
LOC_CURR_CODE VARCHAR2(30 CHAR),
LOC_EXCHANGE_RATE NUMBER(22,7),
DOC_CURR_CODE VARCHAR2(30 CHAR),
ETL_THREAD_VAL NUMBER(4,0),
DATASOURCE_NUM_ID NUMBER(10,0),
FLEX1_NUM_VALUE NUMBER(18,4),
FLEX2_NUM_VALUE NUMBER(18,4),
FLEX3_NUM_VALUE NUMBER(18,4),
FLEX4_NUM_VALUE NUMBER(18,4),
FLEX5_NUM_VALUE NUMBER(18,4),
FLEX6_NUM_VALUE NUMBER(18,4),
FLEX7_NUM_VALUE NUMBER(18,4),
FLEX8_NUM_VALUE NUMBER(18,4),
FLEX9_NUM_VALUE NUMBER(18,4),
FLEX10_NUM_VALUE NUMBER(18,4),
FLEX11_NUM_VALUE NUMBER(18,4),
FLEX12_NUM_VALUE NUMBER(18,4),
FLEX13_NUM_VALUE NUMBER(18,4),
FLEX14_NUM_VALUE NUMBER(18,4),
FLEX15_NUM_VALUE NUMBER(18,4),
FLEX16_NUM_VALUE NUMBER(18,4),
FLEX17_NUM_VALUE NUMBER(18,4),
FLEX18_NUM_VALUE NUMBER(18,4),
FLEX19_NUM_VALUE NUMBER(18,4),
FLEX20_NUM_VALUE NUMBER(18,4),
DEAL_ID VARCHAR2(30 CHAR),
DEAL_FIXED_QTY NUMBER(18,4),
DEAL_FIXED_COST_AMT_LCL NUMBER(20,4),
DEAL_FIXED_RTL_AMT_LCL NUMBER(20,4),
DEAL_ISSUE_QTY NUMBER(18,4),
DEAL_ISSUE_COST_AMT_LCL NUMBER(20,4),
DEAL_ISSUE_RTL_AMT_LCL NUMBER(20,4),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 7 — Populate ORI_DEAL_INCOME_DELTA_STG (anti-join on SRC_ROWID; LOG_LVL='W' rows still flow through)
-- ============================================================================
INSERT INTO ORI_DEAL_INCOME_DELTA_STG (ITEM, ORG_NUM, DAY_DT, DEAL_QTY, DEAL_COST_AMT_LCL, DEAL_RTL_AMT_LCL, DEAL_PURCH_QTY, DEAL_PURCH_COST_AMT_LCL, DEAL_PURCH_RTL_AMT_LCL, DELETE_FLG, GLOBAL1_EXCHANGE_RATE, GLOBAL2_EXCHANGE_RATE, GLOBAL3_EXCHANGE_RATE, LOC_CURR_CODE, LOC_EXCHANGE_RATE, DOC_CURR_CODE, ETL_THREAD_VAL, 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, DEAL_ID, DEAL_FIXED_QTY, DEAL_FIXED_COST_AMT_LCL, DEAL_FIXED_RTL_AMT_LCL, DEAL_ISSUE_QTY, DEAL_ISSUE_COST_AMT_LCL, DEAL_ISSUE_RTL_AMT_LCL, INSERTED_AT)
WITH s AS (
SELECT
SRC_ROWID,
ITEM,
ORG_NUM,
DAY_DT,
DEAL_QTY,
DEAL_COST_AMT_LCL,
DEAL_RTL_AMT_LCL,
DEAL_PURCH_QTY,
DEAL_PURCH_COST_AMT_LCL,
DEAL_PURCH_RTL_AMT_LCL,
DELETE_FLG,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
DOC_CURR_CODE,
ETL_THREAD_VAL,
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,
DEAL_ID,
DEAL_FIXED_QTY,
DEAL_FIXED_COST_AMT_LCL,
DEAL_FIXED_RTL_AMT_LCL,
DEAL_ISSUE_QTY,
DEAL_ISSUE_COST_AMT_LCL,
DEAL_ISSUE_RTL_AMT_LCL
FROM DMF_SOURCE__DEALS_DELTA
)
SELECT s.ITEM, s.ORG_NUM, s.DAY_DT, s.DEAL_QTY, s.DEAL_COST_AMT_LCL, s.DEAL_RTL_AMT_LCL, s.DEAL_PURCH_QTY, s.DEAL_PURCH_COST_AMT_LCL, s.DEAL_PURCH_RTL_AMT_LCL, s.DELETE_FLG, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.DATASOURCE_NUM_ID, s.FLEX1_NUM_VALUE, s.FLEX2_NUM_VALUE, s.FLEX3_NUM_VALUE, s.FLEX4_NUM_VALUE, s.FLEX5_NUM_VALUE, s.FLEX6_NUM_VALUE, s.FLEX7_NUM_VALUE, s.FLEX8_NUM_VALUE, s.FLEX9_NUM_VALUE, s.FLEX10_NUM_VALUE, s.FLEX11_NUM_VALUE, s.FLEX12_NUM_VALUE, s.FLEX13_NUM_VALUE, s.FLEX14_NUM_VALUE, s.FLEX15_NUM_VALUE, s.FLEX16_NUM_VALUE, s.FLEX17_NUM_VALUE, s.FLEX18_NUM_VALUE, s.FLEX19_NUM_VALUE, s.FLEX20_NUM_VALUE, s.DEAL_ID, s.DEAL_FIXED_QTY, s.DEAL_FIXED_COST_AMT_LCL, s.DEAL_FIXED_RTL_AMT_LCL, s.DEAL_ISSUE_QTY, s.DEAL_ISSUE_COST_AMT_LCL, s.DEAL_ISSUE_RTL_AMT_LCL, SYSDATE
FROM s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_DEALS_HISTORY_DELTA_REJECTED r
WHERE r.VALIDATION_RUN_ID = '20260610T210755_f3eb717d'
AND r.LOG_LVL = 'E'
AND r.SRC_ROWID = s.SRC_ROWID
)
AND s.ITEM IS NOT NULL
AND s.ORG_NUM IS NOT NULL
AND s.DAY_DT IS NOT NULL;
COMMIT;
-- Row count check: should be COUNT(ORI_DEALS_HISTORY_DELTA_RAW) minus distinct SRC_ROWIDs in ORI_DEALS_HISTORY_DELTA_REJECTED where LOG_LVL='E'.
SELECT COUNT(*) AS stg_rows FROM ORI_DEAL_INCOME_DELTA_STG;
-- ============================================================================
-- STEP 8 — Build ORI_DEAL_INCOME_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_DEAL_INCOME_DELTA_CTRL PURGE; -- ignore ORA-00942
CREATE TABLE ORI_DEAL_INCOME_DELTA_CTRL (
ITEM VARCHAR2(80 CHAR),
ORG_NUM VARCHAR2(30 CHAR),
DAY_DT DATE,
DEAL_QTY NUMBER(30,4),
DEAL_COST_AMT_LCL NUMBER(30,4),
DEAL_RTL_AMT_LCL NUMBER(30,4),
DEAL_PURCH_QTY NUMBER(30,4),
DEAL_PURCH_COST_AMT_LCL NUMBER(30,4),
DEAL_PURCH_RTL_AMT_LCL NUMBER(30,4),
DELETE_FLG CHAR(1 CHAR),
GLOBAL1_EXCHANGE_RATE NUMBER(22,7),
GLOBAL2_EXCHANGE_RATE NUMBER(22,7),
GLOBAL3_EXCHANGE_RATE NUMBER(22,7),
LOC_CURR_CODE VARCHAR2(30 CHAR),
LOC_EXCHANGE_RATE NUMBER(22,7),
DOC_CURR_CODE VARCHAR2(30 CHAR),
ETL_THREAD_VAL NUMBER(4,0),
DATASOURCE_NUM_ID NUMBER(10,0),
FLEX1_NUM_VALUE NUMBER(18,4),
FLEX2_NUM_VALUE NUMBER(18,4),
FLEX3_NUM_VALUE NUMBER(18,4),
FLEX4_NUM_VALUE NUMBER(18,4),
FLEX5_NUM_VALUE NUMBER(18,4),
FLEX6_NUM_VALUE NUMBER(18,4),
FLEX7_NUM_VALUE NUMBER(18,4),
FLEX8_NUM_VALUE NUMBER(18,4),
FLEX9_NUM_VALUE NUMBER(18,4),
FLEX10_NUM_VALUE NUMBER(18,4),
FLEX11_NUM_VALUE NUMBER(18,4),
FLEX12_NUM_VALUE NUMBER(18,4),
FLEX13_NUM_VALUE NUMBER(18,4),
FLEX14_NUM_VALUE NUMBER(18,4),
FLEX15_NUM_VALUE NUMBER(18,4),
FLEX16_NUM_VALUE NUMBER(18,4),
FLEX17_NUM_VALUE NUMBER(18,4),
FLEX18_NUM_VALUE NUMBER(18,4),
FLEX19_NUM_VALUE NUMBER(18,4),
FLEX20_NUM_VALUE NUMBER(18,4),
DEAL_ID VARCHAR2(30 CHAR),
DEAL_FIXED_QTY NUMBER(18,4),
DEAL_FIXED_COST_AMT_LCL NUMBER(20,4),
DEAL_FIXED_RTL_AMT_LCL NUMBER(20,4),
DEAL_ISSUE_QTY NUMBER(18,4),
DEAL_ISSUE_COST_AMT_LCL NUMBER(20,4),
DEAL_ISSUE_RTL_AMT_LCL NUMBER(20,4),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 9 — Populate ORI_DEAL_INCOME_DELTA_CTRL from ORI_DEAL_INCOME_DELTA_STG
-- ============================================================================
INSERT INTO ORI_DEAL_INCOME_DELTA_CTRL (ITEM, ORG_NUM, DAY_DT, DEAL_QTY, DEAL_COST_AMT_LCL, DEAL_RTL_AMT_LCL, DEAL_PURCH_QTY, DEAL_PURCH_COST_AMT_LCL, DEAL_PURCH_RTL_AMT_LCL, DELETE_FLG, GLOBAL1_EXCHANGE_RATE, GLOBAL2_EXCHANGE_RATE, GLOBAL3_EXCHANGE_RATE, LOC_CURR_CODE, LOC_EXCHANGE_RATE, DOC_CURR_CODE, ETL_THREAD_VAL, 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, DEAL_ID, DEAL_FIXED_QTY, DEAL_FIXED_COST_AMT_LCL, DEAL_FIXED_RTL_AMT_LCL, DEAL_ISSUE_QTY, DEAL_ISSUE_COST_AMT_LCL, DEAL_ISSUE_RTL_AMT_LCL, INSERTED_AT)
SELECT s.ITEM, s.ORG_NUM, s.DAY_DT, CASE WHEN s.DEAL_QTY IS NULL OR ABS(TRUNC(s.DEAL_QTY)) < 100000000000000000000000000 THEN s.DEAL_QTY ELSE NULL END, CASE WHEN s.DEAL_COST_AMT_LCL IS NULL OR ABS(TRUNC(s.DEAL_COST_AMT_LCL)) < 100000000000000000000000000 THEN s.DEAL_COST_AMT_LCL ELSE NULL END, CASE WHEN s.DEAL_RTL_AMT_LCL IS NULL OR ABS(TRUNC(s.DEAL_RTL_AMT_LCL)) < 100000000000000000000000000 THEN s.DEAL_RTL_AMT_LCL ELSE NULL END, CASE WHEN s.DEAL_PURCH_QTY IS NULL OR ABS(TRUNC(s.DEAL_PURCH_QTY)) < 100000000000000000000000000 THEN s.DEAL_PURCH_QTY ELSE NULL END, CASE WHEN s.DEAL_PURCH_COST_AMT_LCL IS NULL OR ABS(TRUNC(s.DEAL_PURCH_COST_AMT_LCL)) < 100000000000000000000000000 THEN s.DEAL_PURCH_COST_AMT_LCL ELSE NULL END, CASE WHEN s.DEAL_PURCH_RTL_AMT_LCL IS NULL OR ABS(TRUNC(s.DEAL_PURCH_RTL_AMT_LCL)) < 100000000000000000000000000 THEN s.DEAL_PURCH_RTL_AMT_LCL ELSE NULL END, s.DELETE_FLG, CASE WHEN s.GLOBAL1_EXCHANGE_RATE IS NULL OR ABS(TRUNC(s.GLOBAL1_EXCHANGE_RATE)) < 1000000000000000 THEN s.GLOBAL1_EXCHANGE_RATE ELSE NULL END, CASE WHEN s.GLOBAL2_EXCHANGE_RATE IS NULL OR ABS(TRUNC(s.GLOBAL2_EXCHANGE_RATE)) < 1000000000000000 THEN s.GLOBAL2_EXCHANGE_RATE ELSE NULL END, CASE WHEN s.GLOBAL3_EXCHANGE_RATE IS NULL OR ABS(TRUNC(s.GLOBAL3_EXCHANGE_RATE)) < 1000000000000000 THEN s.GLOBAL3_EXCHANGE_RATE ELSE NULL END, s.LOC_CURR_CODE, CASE WHEN s.LOC_EXCHANGE_RATE IS NULL OR ABS(TRUNC(s.LOC_EXCHANGE_RATE)) < 1000000000000000 THEN s.LOC_EXCHANGE_RATE ELSE NULL END, s.DOC_CURR_CODE, CASE WHEN s.ETL_THREAD_VAL IS NULL OR ABS(TRUNC(s.ETL_THREAD_VAL)) < 10000 THEN s.ETL_THREAD_VAL ELSE NULL END, CASE WHEN s.DATASOURCE_NUM_ID IS NULL OR ABS(TRUNC(s.DATASOURCE_NUM_ID)) < 10000000000 THEN s.DATASOURCE_NUM_ID ELSE NULL END, CASE WHEN s.FLEX1_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX1_NUM_VALUE)) < 100000000000000 THEN s.FLEX1_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX2_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX2_NUM_VALUE)) < 100000000000000 THEN s.FLEX2_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX3_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX3_NUM_VALUE)) < 100000000000000 THEN s.FLEX3_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX4_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX4_NUM_VALUE)) < 100000000000000 THEN s.FLEX4_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX5_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX5_NUM_VALUE)) < 100000000000000 THEN s.FLEX5_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX6_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX6_NUM_VALUE)) < 100000000000000 THEN s.FLEX6_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX7_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX7_NUM_VALUE)) < 100000000000000 THEN s.FLEX7_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX8_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX8_NUM_VALUE)) < 100000000000000 THEN s.FLEX8_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX9_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX9_NUM_VALUE)) < 100000000000000 THEN s.FLEX9_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX10_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX10_NUM_VALUE)) < 100000000000000 THEN s.FLEX10_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX11_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX11_NUM_VALUE)) < 100000000000000 THEN s.FLEX11_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX12_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX12_NUM_VALUE)) < 100000000000000 THEN s.FLEX12_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX13_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX13_NUM_VALUE)) < 100000000000000 THEN s.FLEX13_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX14_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX14_NUM_VALUE)) < 100000000000000 THEN s.FLEX14_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX15_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX15_NUM_VALUE)) < 100000000000000 THEN s.FLEX15_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX16_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX16_NUM_VALUE)) < 100000000000000 THEN s.FLEX16_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX17_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX17_NUM_VALUE)) < 100000000000000 THEN s.FLEX17_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX18_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX18_NUM_VALUE)) < 100000000000000 THEN s.FLEX18_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX19_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX19_NUM_VALUE)) < 100000000000000 THEN s.FLEX19_NUM_VALUE ELSE NULL END, CASE WHEN s.FLEX20_NUM_VALUE IS NULL OR ABS(TRUNC(s.FLEX20_NUM_VALUE)) < 100000000000000 THEN s.FLEX20_NUM_VALUE ELSE NULL END, s.DEAL_ID, CASE WHEN s.DEAL_FIXED_QTY IS NULL OR ABS(TRUNC(s.DEAL_FIXED_QTY)) < 100000000000000 THEN s.DEAL_FIXED_QTY ELSE NULL END, CASE WHEN s.DEAL_FIXED_COST_AMT_LCL IS NULL OR ABS(TRUNC(s.DEAL_FIXED_COST_AMT_LCL)) < 10000000000000000 THEN s.DEAL_FIXED_COST_AMT_LCL ELSE NULL END, CASE WHEN s.DEAL_FIXED_RTL_AMT_LCL IS NULL OR ABS(TRUNC(s.DEAL_FIXED_RTL_AMT_LCL)) < 10000000000000000 THEN s.DEAL_FIXED_RTL_AMT_LCL ELSE NULL END, CASE WHEN s.DEAL_ISSUE_QTY IS NULL OR ABS(TRUNC(s.DEAL_ISSUE_QTY)) < 100000000000000 THEN s.DEAL_ISSUE_QTY ELSE NULL END, CASE WHEN s.DEAL_ISSUE_COST_AMT_LCL IS NULL OR ABS(TRUNC(s.DEAL_ISSUE_COST_AMT_LCL)) < 10000000000000000 THEN s.DEAL_ISSUE_COST_AMT_LCL ELSE NULL END, CASE WHEN s.DEAL_ISSUE_RTL_AMT_LCL IS NULL OR ABS(TRUNC(s.DEAL_ISSUE_RTL_AMT_LCL)) < 10000000000000000 THEN s.DEAL_ISSUE_RTL_AMT_LCL ELSE NULL END, SYSDATE
FROM ORI_DEAL_INCOME_DELTA_STG s;
COMMIT;
SELECT COUNT(*) AS ctrl_rows FROM ORI_DEAL_INCOME_DELTA_CTRL;
-- ============================================================================
-- STEP 10 — Rejection summary by rule
-- ============================================================================
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM ORI_DEALS_HISTORY_DELTA_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;