Source: DMF/PuC/AIF/Deal income.sql
-- ============================================================================
-- DEALS — RAW → REJECTED → CTRL (STG bypassed)
-- ============================================================================
-- Source: ORI_DEALS_HISTORY_RAW | Rejected: ORI_DEALS_HISTORY_REJECTED
-- CTRL: ORI_DEAL_INCOME_CTRL | Rules: 4 (6 UNION ALL branches)
--
-- Run order: SETUP → PHASE 1 → PHASE 2.
-- ############################################################################
-- SETUP
-- ############################################################################
-- ============================================================================
-- STEP 1 — Validity materialization (RUN ONCE / when master data reloads)
-- ============================================================================
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);
SELECT 'DMF_VALIDITY__ORGANIZAT_5EBC71' AS dim, COUNT(*) FROM DMF_VALIDITY__ORGANIZAT_5EBC71;
-- ============================================================================
-- STEP 1b — Normalized source snapshot
-- ============================================================================
-- Also the input to the CTRL build; do not drop until PHASE 2 has committed.
DROP TABLE DMF_SOURCE__DEALS PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_SOURCE__DEALS 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_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 cheap.
CREATE INDEX IX_DMF_SOURCE__DEALS_RID ON DMF_SOURCE__DEALS (ITEM, ORG_NUM, DAY_DT);
ALTER TABLE DMF_SOURCE__DEALS NOPARALLEL;
SELECT COUNT(*) FROM DMF_SOURCE__DEALS;
-- ============================================================================
-- STEP 1c — Duplicate-key set
-- ============================================================================
DROP TABLE DMF_DUPKEYS__DEALS PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_DUPKEYS__DEALS AS
SELECT ITEM, ORG_NUM, DAY_DT FROM DMF_SOURCE__DEALS
GROUP BY ITEM, ORG_NUM, DAY_DT
HAVING COUNT(*) > 1;
CREATE INDEX IX_DMF_DUPKEYS__DEALS_KEY ON DMF_DUPKEYS__DEALS (ITEM, ORG_NUM, DAY_DT);
SELECT COUNT(*) FROM DMF_DUPKEYS__DEALS;
-- ############################################################################
-- PHASE 1 — RAW → REJECTED
-- ############################################################################
-- ============================================================================
-- STEP 2 — Ensure ORI_DEALS_HISTORY_REJECTED exists, then empty it
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ORI_DEALS_HISTORY_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_REJECTED;
-- On ORA-00054 (orphan lock) use DELETE + COMMIT instead.
-- ============================================================================
-- STEP 3 — Unified UNION ALL INSERT (single source scan)
-- ============================================================================
-- Per-rule variant is in the APPENDIX; do NOT run it in the same pass.
INSERT INTO ORI_DEALS_HISTORY_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
)
-- RULE 1/4: ERROR_INVALID_NULL (type=required_fields, severity=E) — one branch per required field
SELECT 'E', 'ITEM IS NULL', 'MANUAL_RUN', '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 DMF_SOURCE__DEALS s WHERE ITEM IS NULL
UNION ALL
SELECT 'E', 'ORG_NUM IS NULL', 'MANUAL_RUN', '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 DMF_SOURCE__DEALS s WHERE ORG_NUM IS NULL
UNION ALL
SELECT 'E', 'DAY_DT IS NULL', 'MANUAL_RUN', '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 DMF_SOURCE__DEALS s WHERE DAY_DT IS NULL
UNION ALL
-- RULE 2/4: ERROR_MISSING_ITEM (type=dimension_exists, severity=E)
SELECT 'E', 'ITEM NOT FOUND: ITEM_MASTER_CTRL', 'MANUAL_RUN', '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 DMF_SOURCE__DEALS 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
-- RULE 3/4: ERROR_INVALID_ORG (type=dimension_exists, severity=E)
SELECT 'E', 'ORG_NUM NOT FOUND: STORE_ADD_CTRL, WH_CTRL', 'MANUAL_RUN', '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 DMF_SOURCE__DEALS 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
-- RULE 4/4: ERR_DUP_KEY_CONFLICT (type=duplicate_key, severity=E)
SELECT 'E', 'DUPLICATE KEY: ITEM, ORG_NUM, DAY_DT', 'MANUAL_RUN', '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 DMF_SOURCE__DEALS s WHERE (ITEM, ORG_NUM, DAY_DT) IN (SELECT ITEM, ORG_NUM, DAY_DT FROM DMF_DUPKEYS__DEALS);
COMMIT;
-- Probed by the STEP 5 anti-join.
CREATE INDEX IX_ORI_DEALS_HIST_REJ_ROWID ON ORI_DEALS_HISTORY_REJECTED (SRC_ROWID, LOG_LVL, VALIDATION_RUN_ID);
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM ORI_DEALS_HISTORY_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;
-- Compare against ctrl_rows after STEP 5.
SELECT (SELECT COUNT(*) FROM DMF_SOURCE__DEALS)
- (SELECT COUNT(DISTINCT SRC_ROWID) FROM ORI_DEALS_HISTORY_REJECTED
WHERE VALIDATION_RUN_ID = 'MANUAL_RUN' AND LOG_LVL = 'E')
AS expected_ctrl_rows
FROM DUAL;
-- ############################################################################
-- PHASE 2 — REJECTED → CTRL
-- ############################################################################
-- ============================================================================
-- STEP A3 — Pre-flight. RUN THIS FIRST; all counts should be 0.
-- ============================================================================
-- No rule in this fact checks size or type, so nothing catches these earlier.
-- would_error_* → ORA-12899, aborts STEP 5.
-- would_null_* → silently NULLed by the CASE guards (was ORA-01438 via STG).
SELECT
COUNT(CASE WHEN LENGTHB(ITEM) > 80 THEN 1 END) AS would_error_item_len,
COUNT(CASE WHEN LENGTHB(ORG_NUM) > 30 THEN 1 END) AS would_error_org_len,
COUNT(CASE WHEN LENGTHB(LOC_CURR_CODE) > 30 THEN 1 END) AS would_error_loc_curr_len,
COUNT(CASE WHEN LENGTHB(DOC_CURR_CODE) > 30 THEN 1 END) AS would_error_doc_curr_len,
COUNT(CASE WHEN ABS(TRUNC(DEAL_PURCH_QTY)) >= POWER(10,26) THEN 1 END) AS would_null_purch_qty,
COUNT(CASE WHEN ABS(TRUNC(DEAL_PURCH_COST_AMT_LCL)) >= POWER(10,26) THEN 1 END) AS would_null_purch_cost,
COUNT(CASE WHEN ABS(TRUNC(FLEX1_NUM_VALUE)) >= POWER(10,14) THEN 1 END) AS would_null_flex1,
COUNT(CASE WHEN ABS(TRUNC(FLEX2_NUM_VALUE)) >= POWER(10,14) THEN 1 END) AS would_null_flex2,
COUNT(CASE WHEN ABS(TRUNC(FLEX3_NUM_VALUE)) >= POWER(10,14) THEN 1 END) AS would_null_flex3,
COUNT(CASE WHEN ABS(TRUNC(FLEX4_NUM_VALUE)) >= POWER(10,14) THEN 1 END) AS would_null_flex4
FROM DMF_SOURCE__DEALS;
-- Non-zero would_null_*: either accept the NULLs and record the counts, or add
-- number_precision_scale rules to overrides/facts/deals.toml so the rows are
-- rejected in PHASE 1 (this also closes the VARCHAR2 gap), or strip the CASE
-- wrappers from STEP 5 to restore the ORA-01438 failure.
-- ============================================================================
-- STEP 4 — Build ORI_DEAL_INCOME_CTRL
-- ============================================================================
DROP TABLE ORI_DEAL_INCOME_CTRL PURGE; -- ignore ORA-00942
CREATE TABLE ORI_DEAL_INCOME_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 5 — Populate CTRL from DMF_SOURCE__DEALS
-- ============================================================================
-- Old STEP 5 anti-join + old STEP 7 projection, merged. LOG_LVL='W' rows pass.
INSERT /*+ APPEND */ INTO ORI_DEAL_INCOME_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 DMF_SOURCE__DEALS s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_DEALS_HISTORY_REJECTED r
WHERE r.VALIDATION_RUN_ID = 'MANUAL_RUN'
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;
SELECT COUNT(*) AS ctrl_rows FROM ORI_DEAL_INCOME_CTRL;
-- ############################################################################
-- FINAL — Rejection summary by rule
-- ############################################################################
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM ORI_DEALS_HISTORY_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;