Source: DMF/PuC/AIF/Markdown history.sql
-- ============================================================================
-- MARKDOWN_HISTORY — RAW → REJECTED → CTRL (STG bypassed)
-- ============================================================================
-- Source: ORI_MARKDOWN_HIST_RAW | Rejected: ORI_MARKDOWN_HIST_REJECTED
-- CTRL: ORI_MARKDOWN_HIST_CTRL
-- Rules: 6 (9 UNION ALL branches — ERROR_INVALID_NULL covers 4 fields)
--
-- STG removed: it declared every carried column VARCHAR2(4000), same as CTRL,
-- and applied no transform. Its anti-join is folded into STEP 5.
--
-- Run order: SETUP → PHASE 1 → PHASE 2.
-- ############################################################################
-- SETUP
-- ############################################################################
-- ============================================================================
-- STEP 1 — Validity materialization (RUN ONCE / when master data reloads)
-- ============================================================================
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'
UNION
SELECT TO_CHAR(WH) FROM WH_CTRL
WHERE TO_CHAR(WH) = '546';
CREATE INDEX IX_DMF_VALIDITY__ORGANIZATION_ ON DMF_VALIDITY__ORGANIZATION_CSV (ORG_NUM);
-- Expect controlled stores + 1 row for warehouse 546 (0 if 546 is absent from WH_CTRL).
SELECT 'DMF_VALIDITY__ORGANIZATION_CSV' AS dim, COUNT(*) FROM DMF_VALIDITY__ORGANIZATION_CSV;
SELECT org_num FROM DMF_VALIDITY__ORGANIZATION_CSV WHERE org_num = '546';
-- ============================================================================
-- STEP 1b — Normalized source snapshot
-- ============================================================================
-- Also the input to the CTRL build; do not drop until PHASE 2 has committed.
DROP TABLE DMF_SOURCE__MARKDOWN_HISTORY PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_SOURCE__MARKDOWN_HISTORY PARALLEL 4 AS
SELECT /*+ PARALLEL(4) */
ROWID AS SRC_ROWID,
ITEM AS ITEM,
LTRIM(ORG_NUM, '0') AS ORG_NUM,
DAY_DT AS DAY_DT,
RTL_TYPE_CODE AS RTL_TYPE_CODE,
MKDN_AMT_LCL AS MKDN_AMT_LCL,
DOC_CURR_CODE AS DOC_CURR_CODE,
LOC_CURR_CODE AS LOC_CURR_CODE
FROM ORI_MARKDOWN_HIST_RAW;
-- Index on the row-identity tuple makes duplicate_key cheap.
CREATE INDEX IX_DMF_SOURCE__MARKDOWN_HIST_RID ON DMF_SOURCE__MARKDOWN_HISTORY (ITEM, ORG_NUM, DAY_DT, RTL_TYPE_CODE);
ALTER TABLE DMF_SOURCE__MARKDOWN_HISTORY NOPARALLEL;
SELECT COUNT(*) FROM DMF_SOURCE__MARKDOWN_HISTORY;
-- ============================================================================
-- STEP 1c — Duplicate-key set
-- ============================================================================
DROP TABLE DMF_DUPKEYS__MARKDOWN_HISTORY PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_DUPKEYS__MARKDOWN_HISTORY AS
SELECT ITEM, ORG_NUM, DAY_DT, RTL_TYPE_CODE FROM DMF_SOURCE__MARKDOWN_HISTORY
GROUP BY ITEM, ORG_NUM, DAY_DT, RTL_TYPE_CODE
HAVING COUNT(*) > 1;
CREATE INDEX IX_DMF_DUPKEYS__MARKDOWN_HIS_KEY ON DMF_DUPKEYS__MARKDOWN_HISTORY (ITEM, ORG_NUM, DAY_DT, RTL_TYPE_CODE);
SELECT COUNT(*) FROM DMF_DUPKEYS__MARKDOWN_HISTORY;
-- ############################################################################
-- PHASE 1 — RAW → REJECTED
-- ############################################################################
-- ============================================================================
-- STEP 2 — Ensure ORI_MARKDOWN_HIST_REJECTED exists, then empty it
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ORI_MARKDOWN_HIST_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), RTL_TYPE_CODE VARCHAR2(4000), MKDN_AMT_LCL VARCHAR2(4000), DOC_CURR_CODE 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_MARKDOWN_HIST_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_MARKDOWN_HIST_REJECTED (
LOG_LVL, ERROR_MSG, VALIDATION_RUN_ID, RULE_ID, SRC_ROWID, ITEM, ORG_NUM, DAY_DT, RTL_TYPE_CODE, MKDN_AMT_LCL, DOC_CURR_CODE, LOC_CURR_CODE, INSERTED_AT
)
-- RULE 1/6: ERROR_INVALID_NULL (required_fields, 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.RTL_TYPE_CODE, s.MKDN_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__MARKDOWN_HISTORY 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.RTL_TYPE_CODE, s.MKDN_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__MARKDOWN_HISTORY 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.RTL_TYPE_CODE, s.MKDN_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__MARKDOWN_HISTORY s WHERE DAY_DT IS NULL
UNION ALL
SELECT 'E', 'RTL_TYPE_CODE IS NULL', 'MANUAL_RUN', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.RTL_TYPE_CODE, s.MKDN_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__MARKDOWN_HISTORY s WHERE RTL_TYPE_CODE IS NULL
UNION ALL
-- RULE 2/6: ERROR_INVALID_RTL_TYPE_CODE (allowed_values, E)
SELECT 'E', 'INVALID RTL_TYPE_CODE: NOT IN ALLOWED VALUES', 'MANUAL_RUN', 'ERROR_INVALID_RTL_TYPE_CODE', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.RTL_TYPE_CODE, s.MKDN_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__MARKDOWN_HISTORY s WHERE RTL_TYPE_CODE NOT IN ('R', 'P', 'C')
UNION ALL
-- RULE 3/6: ERROR_MISSING_ITEM (dimension_exists, 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.RTL_TYPE_CODE, s.MKDN_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__MARKDOWN_HISTORY 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 4/6: ERROR_MISSING_STORE (dimension_exists, E)
SELECT 'E', 'ORG_NUM NOT FOUND: STORE_ADD_CTRL', 'MANUAL_RUN', 'ERROR_MISSING_STORE', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.RTL_TYPE_CODE, s.MKDN_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__MARKDOWN_HISTORY 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
-- RULE 5/6: WARN_NEGATIVE_MKDN_AMT (condition, W)
SELECT 'W', 'WARN_NEGATIVE_MKDN_AMT', 'MANUAL_RUN', 'WARN_NEGATIVE_MKDN_AMT', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.RTL_TYPE_CODE, s.MKDN_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__MARKDOWN_HISTORY s WHERE MKDN_AMT_LCL IS NOT NULL AND MKDN_AMT_LCL < 0
UNION ALL
-- RULE 6/6: ERR_DUP_KEY_CONFLICT (duplicate_key, E)
SELECT 'E', 'DUPLICATE KEY: ITEM, ORG_NUM, DAY_DT, RTL_TYPE_CODE', 'MANUAL_RUN', 'ERR_DUP_KEY_CONFLICT', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.RTL_TYPE_CODE, s.MKDN_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__MARKDOWN_HISTORY s WHERE (ITEM, ORG_NUM, DAY_DT, RTL_TYPE_CODE) IN (SELECT ITEM, ORG_NUM, DAY_DT, RTL_TYPE_CODE FROM DMF_DUPKEYS__MARKDOWN_HISTORY);
COMMIT;
-- Probed by the STEP 5 anti-join.
CREATE INDEX IX_ORI_MKDN_HIST_REJ_ROWID ON ORI_MARKDOWN_HIST_REJECTED (SRC_ROWID, LOG_LVL, VALIDATION_RUN_ID);
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM ORI_MARKDOWN_HIST_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__MARKDOWN_HISTORY)
- (SELECT COUNT(DISTINCT SRC_ROWID) FROM ORI_MARKDOWN_HIST_REJECTED
WHERE VALIDATION_RUN_ID = 'MANUAL_RUN' AND LOG_LVL = 'E')
AS expected_ctrl_rows
FROM DUAL;
-- ############################################################################
-- PHASE 2 — REJECTED → CTRL
-- ############################################################################
-- ============================================================================
-- STEP 4 — Build ORI_MARKDOWN_HIST_CTRL
-- ============================================================================
DROP TABLE ORI_MARKDOWN_HIST_CTRL PURGE; -- ignore ORA-00942
CREATE TABLE ORI_MARKDOWN_HIST_CTRL (
ITEM VARCHAR2(4000),
ORG_NUM VARCHAR2(4000),
DAY_DT VARCHAR2(4000),
RTL_TYPE_CODE VARCHAR2(4000),
MKDN_QTY VARCHAR2(4000),
MKDN_AMT_LCL VARCHAR2(4000),
MKUP_QTY VARCHAR2(4000),
MKUP_AMT_LCL VARCHAR2(4000),
MKDN_CAN_QTY VARCHAR2(4000),
MKDN_CAN_AMT_LCL VARCHAR2(4000),
MKUP_CAN_QTY VARCHAR2(4000),
MKUP_CAN_AMT_LCL VARCHAR2(4000),
DOC_CURR_CODE VARCHAR2(4000),
LOC_CURR_CODE VARCHAR2(4000),
GLOBAL1_EXCHANGE_RATE VARCHAR2(4000),
GLOBAL2_EXCHANGE_RATE VARCHAR2(4000),
GLOBAL3_EXCHANGE_RATE VARCHAR2(4000),
LOC_EXCHANGE_RATE VARCHAR2(4000),
REF_NO_1 VARCHAR2(4000),
REF_NO_2 VARCHAR2(4000),
DELETE_FLG 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),
CREATE_ID VARCHAR2(4000),
CREATE_DATETIME VARCHAR2(4000),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 5 — Populate CTRL directly from DMF_SOURCE__MARKDOWN_HISTORY
-- ============================================================================
-- Old STEP 5 anti-join + old STEP 7 projection, merged. LOG_LVL='W' rows pass.
INSERT /*+ APPEND */ INTO ORI_MARKDOWN_HIST_CTRL (ITEM, ORG_NUM, DAY_DT, RTL_TYPE_CODE, MKDN_QTY, MKDN_AMT_LCL, MKUP_QTY, MKUP_AMT_LCL, MKDN_CAN_QTY, MKDN_CAN_AMT_LCL, MKUP_CAN_QTY, MKUP_CAN_AMT_LCL, DOC_CURR_CODE, LOC_CURR_CODE, GLOBAL1_EXCHANGE_RATE, GLOBAL2_EXCHANGE_RATE, GLOBAL3_EXCHANGE_RATE, LOC_EXCHANGE_RATE, REF_NO_1, REF_NO_2, DELETE_FLG, 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, CREATE_ID, CREATE_DATETIME, INSERTED_AT)
SELECT s.ITEM, s.ORG_NUM, s.DAY_DT, s.RTL_TYPE_CODE, CAST(NULL AS NUMBER), s.MKDN_AMT_LCL, CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), s.DOC_CURR_CODE, s.LOC_CURR_CODE, CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS VARCHAR2(48)), CAST(NULL AS VARCHAR2(250)), CAST(NULL AS CHAR(1)), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), 'DMF', SYSDATE, SYSDATE
FROM DMF_SOURCE__MARKDOWN_HISTORY s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_MARKDOWN_HIST_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
AND s.RTL_TYPE_CODE IS NOT NULL;
COMMIT;
SELECT COUNT(*) AS ctrl_rows FROM ORI_MARKDOWN_HIST_CTRL;
-- ############################################################################
-- FINAL — Rejection summary by rule
-- ############################################################################
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM ORI_MARKDOWN_HIST_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;