Source: DMF/PuC/AIF/FelipeBkp/price_clearance.sql
-- ============================================================================
-- PRICE CLEARANCE — Full Validation Pipeline
-- ============================================================================
-- Fact: price_clearance
-- Source: ORI_CLEARANCE_RAW
-- BQ source: `puc-p-dataf-common`.export_oracle_migration.migration_clearance_history
-- Rejected: CLEARANCE_HISTORY_REJECTED
-- STG (accepted): CLEARANCE_HISTORY_STG
-- Rules: 7
-- 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:
-- XREF_ITEM_IBC_RETAIL built by ../ItemPR1_PV1.sql
--
-- Collapsed from phase1/phase2/phase3 playbooks generated 2026-06-10T19:53:31.
-- 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 'item_valid' → DMF_VALIDITY__ITEM_VALID
DROP TABLE DMF_VALIDITY__ITEM_VALID PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_VALIDITY__ITEM_VALID AS
SELECT DISTINCT imc.item AS item
FROM item_master_ctrl imc
WHERE imc.ctrl_status = 'C'
UNION
SELECT DISTINCT xi.item_pr1 AS item
FROM item_master_ctrl imc
JOIN XREF_ITEM_IBC_RETAIL xi ON xi.item_pv1 = imc.item
WHERE imc.ctrl_status = 'C'
AND xi.item_pr1 IS NOT NULL;
CREATE INDEX IX_DMF_VALIDITY__ITEM_VALID ON DMF_VALIDITY__ITEM_VALID (ITEM);
-- Dimension 'organization_wh_direct' → DMF_VALIDITY__ORGANIZAT_A4FC2D
DROP TABLE DMF_VALIDITY__ORGANIZAT_A4FC2D PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_VALIDITY__ORGANIZAT_A4FC2D AS
SELECT TO_CHAR(STORE) AS org_num FROM STORE_ADD_CTRL WHERE ctrl_status = 'C'
UNION ALL
SELECT TO_CHAR(WH) AS org_num FROM WH_CTRL WHERE ctrl_status = 'C';
CREATE INDEX IX_DMF_VALIDITY__ORGANIZAT_A4F ON DMF_VALIDITY__ORGANIZAT_A4FC2D (ORG_NUM);
-- Verify the validity tables populated:
SELECT 'DMF_VALIDITY__ITEM_VALID' AS dim, COUNT(*) FROM DMF_VALIDITY__ITEM_VALID;
SELECT 'DMF_VALIDITY__ORGANIZAT_A4FC2D' AS dim, COUNT(*) FROM DMF_VALIDITY__ORGANIZAT_A4FC2D;
-- ============================================================================
-- STEP 2 — Materialize normalized source into DMF_SOURCE__PRICE_CLEARANCE
-- ============================================================================
-- One scan of RAW now → all subsequent rules read from a smaller, hotter,
-- indexed snapshot. Drops the per-rule N-scan cost on wide RAW tables.
DROP TABLE DMF_SOURCE__PRICE_CLEARANCE PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_SOURCE__PRICE_CLEARANCE PARALLEL 4 AS
SELECT /*+ PARALLEL(4) */
ROWID AS SRC_ROWID,
PROD_NUM AS ITEM,
ORG_NUM AS ORG_NUM,
EFFECTIVE_FROM_DATE AS START_DATE,
EFFECTIVE_TO_DATE AS END_DATE,
CHANGE_TYPE AS PRICE_CHANGE_TRAN_TYPE,
CHANGE_AMOUNT AS CLEARANCE_CHANGE_AMOUNT,
CHANGE_CURR_CODE AS CURRENCY_CODE,
MARKDOWN_NBR_DESC AS CLEARANCE_MARKDOWN_NBR_DESC,
REASON_CODE_DESC AS CLEARANCE_REASON_CODE_DESC
FROM ORI_CLEARANCE_RAW;
-- Index on the row-identity tuple makes duplicate_key and the STG
-- anti-join cheap.
CREATE INDEX IX_DMF_SOURCE__PRICE_CLEARAN_RID ON DMF_SOURCE__PRICE_CLEARANCE (ITEM, ORG_NUM, START_DATE, END_DATE, PRICE_CHANGE_TRAN_TYPE);
-- Reset parallel attribute so per-rule reads don't auto-parallelize.
ALTER TABLE DMF_SOURCE__PRICE_CLEARANCE NOPARALLEL;
-- Sanity check:
SELECT COUNT(*) FROM DMF_SOURCE__PRICE_CLEARANCE;
-- ============================================================================
-- STEP 3 — Materialize duplicate-key set into DMF_DUPKEYS__PRICE_CLEARANCE
-- ============================================================================
-- 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__PRICE_CLEARANCE PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_DUPKEYS__PRICE_CLEARANCE AS
SELECT ITEM, ORG_NUM, START_DATE, END_DATE, PRICE_CHANGE_TRAN_TYPE FROM DMF_SOURCE__PRICE_CLEARANCE
GROUP BY ITEM, ORG_NUM, START_DATE, END_DATE, PRICE_CHANGE_TRAN_TYPE
HAVING COUNT(*) > 1;
CREATE INDEX IX_DMF_DUPKEYS__PRICE_CLEARA_KEY ON DMF_DUPKEYS__PRICE_CLEARANCE (ITEM, ORG_NUM, START_DATE, END_DATE, PRICE_CHANGE_TRAN_TYPE);
-- Sanity check (size of the conflict set):
SELECT COUNT(*) FROM DMF_DUPKEYS__PRICE_CLEARANCE;
-- ============================================================================
-- STEP 4 — Ensure CLEARANCE_HISTORY_REJECTED exists, then empty it
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE CLEARANCE_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), START_DATE VARCHAR2(4000), END_DATE VARCHAR2(4000), PRICE_CHANGE_TRAN_TYPE VARCHAR2(4000), CLEARANCE_CHANGE_AMOUNT VARCHAR2(4000), CURRENCY_CODE VARCHAR2(4000), CLEARANCE_MARKDOWN_NBR_DESC VARCHAR2(4000), CLEARANCE_REASON_CODE_DESC VARCHAR2(4000), INSERTED_AT TIMESTAMP(6) )';
EXCEPTION WHEN OTHERS THEN
IF SQLCODE != -955 THEN RAISE; END IF; -- -955 = table already exists
END;
/
TRUNCATE TABLE CLEARANCE_HISTORY_REJECTED;
-- If TRUNCATE fails with ORA-00054 (orphan lock), use DELETE instead:
-- DELETE FROM CLEARANCE_HISTORY_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 CLEARANCE_HISTORY_REJECTED (
LOG_LVL, ERROR_MSG, VALIDATION_RUN_ID, RULE_ID, SRC_ROWID, ITEM, ORG_NUM, START_DATE, END_DATE, PRICE_CHANGE_TRAN_TYPE, CLEARANCE_CHANGE_AMOUNT, CURRENCY_CODE, CLEARANCE_MARKDOWN_NBR_DESC, CLEARANCE_REASON_CODE_DESC, INSERTED_AT
)
WITH s AS (
SELECT
SRC_ROWID,
ITEM,
ORG_NUM,
START_DATE,
END_DATE,
PRICE_CHANGE_TRAN_TYPE,
CLEARANCE_CHANGE_AMOUNT,
CURRENCY_CODE,
CLEARANCE_MARKDOWN_NBR_DESC,
CLEARANCE_REASON_CODE_DESC
FROM DMF_SOURCE__PRICE_CLEARANCE
)
SELECT 'E', 'ITEM IS NULL', 'MANUAL_RUN', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.START_DATE, s.END_DATE, s.PRICE_CHANGE_TRAN_TYPE, s.CLEARANCE_CHANGE_AMOUNT, s.CURRENCY_CODE, s.CLEARANCE_MARKDOWN_NBR_DESC, s.CLEARANCE_REASON_CODE_DESC, SYSDATE FROM 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.START_DATE, s.END_DATE, s.PRICE_CHANGE_TRAN_TYPE, s.CLEARANCE_CHANGE_AMOUNT, s.CURRENCY_CODE, s.CLEARANCE_MARKDOWN_NBR_DESC, s.CLEARANCE_REASON_CODE_DESC, SYSDATE FROM s WHERE ORG_NUM IS NULL
UNION ALL
SELECT 'E', 'START_DATE IS NULL', 'MANUAL_RUN', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.START_DATE, s.END_DATE, s.PRICE_CHANGE_TRAN_TYPE, s.CLEARANCE_CHANGE_AMOUNT, s.CURRENCY_CODE, s.CLEARANCE_MARKDOWN_NBR_DESC, s.CLEARANCE_REASON_CODE_DESC, SYSDATE FROM s WHERE START_DATE IS NULL
UNION ALL
SELECT 'E', 'END_DATE IS NULL', 'MANUAL_RUN', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.START_DATE, s.END_DATE, s.PRICE_CHANGE_TRAN_TYPE, s.CLEARANCE_CHANGE_AMOUNT, s.CURRENCY_CODE, s.CLEARANCE_MARKDOWN_NBR_DESC, s.CLEARANCE_REASON_CODE_DESC, SYSDATE FROM s WHERE END_DATE IS NULL
UNION ALL
SELECT 'E', 'PRICE_CHANGE_TRAN_TYPE IS NULL', 'MANUAL_RUN', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.START_DATE, s.END_DATE, s.PRICE_CHANGE_TRAN_TYPE, s.CLEARANCE_CHANGE_AMOUNT, s.CURRENCY_CODE, s.CLEARANCE_MARKDOWN_NBR_DESC, s.CLEARANCE_REASON_CODE_DESC, SYSDATE FROM s WHERE PRICE_CHANGE_TRAN_TYPE IS NULL
UNION ALL
SELECT 'E', 'INVALID PRICE_CHANGE_TRAN_TYPE: NOT IN ALLOWED VALUES', 'MANUAL_RUN', 'ERROR_INVALID_PRICE_CHANGE_TRAN_TYPE', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.START_DATE, s.END_DATE, s.PRICE_CHANGE_TRAN_TYPE, s.CLEARANCE_CHANGE_AMOUNT, s.CURRENCY_CODE, s.CLEARANCE_MARKDOWN_NBR_DESC, s.CLEARANCE_REASON_CODE_DESC, SYSDATE FROM s WHERE PRICE_CHANGE_TRAN_TYPE NOT IN ('0', '2', '4', '8', '9', '10', '11')
UNION ALL
SELECT 'E', 'ITEM NOT FOUND: ITEM_MASTER_CTRL', 'MANUAL_RUN', 'ERROR_MISSING_ITEM', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.START_DATE, s.END_DATE, s.PRICE_CHANGE_TRAN_TYPE, s.CLEARANCE_CHANGE_AMOUNT, s.CURRENCY_CODE, s.CLEARANCE_MARKDOWN_NBR_DESC, s.CLEARANCE_REASON_CODE_DESC, SYSDATE FROM s WHERE ITEM IS NOT NULL AND NOT EXISTS (SELECT 1 FROM DMF_VALIDITY__ITEM_VALID d WHERE d.ITEM = s.ITEM)
UNION ALL
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.START_DATE, s.END_DATE, s.PRICE_CHANGE_TRAN_TYPE, s.CLEARANCE_CHANGE_AMOUNT, s.CURRENCY_CODE, s.CLEARANCE_MARKDOWN_NBR_DESC, s.CLEARANCE_REASON_CODE_DESC, SYSDATE FROM s WHERE ORG_NUM IS NOT NULL AND NOT EXISTS (SELECT 1 FROM DMF_VALIDITY__ORGANIZAT_A4FC2D d WHERE d.ORG_NUM = s.ORG_NUM)
UNION ALL
SELECT 'W', 'WARN_MISSING_ITEM_LOCATION', 'MANUAL_RUN', 'WARN_MISSING_ITEM_LOCATION', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.START_DATE, s.END_DATE, s.PRICE_CHANGE_TRAN_TYPE, s.CLEARANCE_CHANGE_AMOUNT, s.CURRENCY_CODE, s.CLEARANCE_MARKDOWN_NBR_DESC, s.CLEARANCE_REASON_CODE_DESC, SYSDATE FROM s WHERE NOT EXISTS (SELECT 1 FROM ITEM_LOC_CTRL c WHERE c.ctrl_status = 'C' AND c.ITEM = s.ITEM AND c.LOC = s.ORG_NUM)
UNION ALL
SELECT 'W', 'WARN_NEGATIVE_CHANGE_AMOUNT', 'MANUAL_RUN', 'WARN_NEGATIVE_CHANGE_AMOUNT', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.START_DATE, s.END_DATE, s.PRICE_CHANGE_TRAN_TYPE, s.CLEARANCE_CHANGE_AMOUNT, s.CURRENCY_CODE, s.CLEARANCE_MARKDOWN_NBR_DESC, s.CLEARANCE_REASON_CODE_DESC, SYSDATE FROM s WHERE CLEARANCE_CHANGE_AMOUNT IS NOT NULL AND CLEARANCE_CHANGE_AMOUNT < 0
UNION ALL
SELECT 'E', 'DUPLICATE KEY: ITEM, ORG_NUM, START_DATE, END_DATE, PRICE_CHANGE_TRAN_TYPE', 'MANUAL_RUN', 'ERR_DUP_KEY_CONFLICT', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.START_DATE, s.END_DATE, s.PRICE_CHANGE_TRAN_TYPE, s.CLEARANCE_CHANGE_AMOUNT, s.CURRENCY_CODE, s.CLEARANCE_MARKDOWN_NBR_DESC, s.CLEARANCE_REASON_CODE_DESC, SYSDATE FROM s WHERE (ITEM, ORG_NUM, START_DATE, END_DATE, PRICE_CHANGE_TRAN_TYPE) IN (SELECT ITEM, ORG_NUM, START_DATE, END_DATE, PRICE_CHANGE_TRAN_TYPE FROM DMF_DUPKEYS__PRICE_CLEARANCE);
COMMIT;
-- ============================================================================
-- STEP 6 — Build typed CLEARANCE_HISTORY_STG (drop-and-recreate)
-- ============================================================================
-- DDL types are pulled from the RAP spec (not VARCHAR2(4000) blanket).
DROP TABLE CLEARANCE_HISTORY_STG PURGE; -- ignore ORA-00942
CREATE TABLE CLEARANCE_HISTORY_STG (
ITEM VARCHAR2(4000),
ORG_NUM VARCHAR2(30 CHAR),
START_DATE VARCHAR2(4000),
END_DATE VARCHAR2(4000),
PRICE_CHANGE_TRAN_TYPE VARCHAR2(4000),
CLEARANCE_CHANGE_AMOUNT VARCHAR2(4000),
CURRENCY_CODE VARCHAR2(4000),
CLEARANCE_MARKDOWN_NBR_DESC VARCHAR2(4000),
CLEARANCE_REASON_CODE_DESC VARCHAR2(4000),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 7 — Populate CLEARANCE_HISTORY_STG (anti-join on SRC_ROWID; LOG_LVL='W' rows still flow through)
-- ============================================================================
INSERT INTO CLEARANCE_HISTORY_STG (ITEM, ORG_NUM, START_DATE, END_DATE, PRICE_CHANGE_TRAN_TYPE, CLEARANCE_CHANGE_AMOUNT, CURRENCY_CODE, CLEARANCE_MARKDOWN_NBR_DESC, CLEARANCE_REASON_CODE_DESC, INSERTED_AT)
WITH s AS (
SELECT
SRC_ROWID,
ITEM,
ORG_NUM,
START_DATE,
END_DATE,
PRICE_CHANGE_TRAN_TYPE,
CLEARANCE_CHANGE_AMOUNT,
CURRENCY_CODE,
CLEARANCE_MARKDOWN_NBR_DESC,
CLEARANCE_REASON_CODE_DESC
FROM DMF_SOURCE__PRICE_CLEARANCE
)
SELECT s.ITEM, s.ORG_NUM, s.START_DATE, s.END_DATE, s.PRICE_CHANGE_TRAN_TYPE, s.CLEARANCE_CHANGE_AMOUNT, s.CURRENCY_CODE, s.CLEARANCE_MARKDOWN_NBR_DESC, s.CLEARANCE_REASON_CODE_DESC, SYSDATE
FROM s
WHERE NOT EXISTS (
SELECT 1
FROM CLEARANCE_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.START_DATE IS NOT NULL
AND s.END_DATE IS NOT NULL
AND s.PRICE_CHANGE_TRAN_TYPE IS NOT NULL;
COMMIT;
-- Row count check: should be COUNT(ORI_CLEARANCE_RAW) minus distinct SRC_ROWIDs in CLEARANCE_HISTORY_REJECTED where LOG_LVL='E'.
SELECT COUNT(*) AS stg_rows FROM CLEARANCE_HISTORY_STG;
-- ============================================================================
-- STEP 8 — Build CLEARANCE_HISTORY_CTRL (drop-and-recreate)
-- ============================================================================
-- Final Oracle export shape. Column projection comes from
-- `[ctrl_projection]` in the override; default = identity from STG.
DROP TABLE CLEARANCE_HISTORY_CTRL PURGE; -- ignore ORA-00942
CREATE TABLE CLEARANCE_HISTORY_CTRL (
PROD_NUM VARCHAR2(30 CHAR),
ORG_NUM VARCHAR2(30 CHAR),
SUPPLIER_NUM VARCHAR2(30 CHAR),
CLEARANCE_ID VARCHAR2(30 CHAR),
EVENT_TYPE VARCHAR2(30 CHAR),
EFFECTIVE_FROM_DATE DATE,
EFFECTIVE_TO_DATE DATE,
RESET_FLG CHAR(1 CHAR),
RESET_ID VARCHAR2(30 CHAR),
EXTRACTION_DATE DATE,
CHANGE_AMOUNT NUMBER(20,4),
CHANGE_UOM VARCHAR2(30 CHAR),
CHANGE_CURR_CODE VARCHAR2(30 CHAR),
APPROVAL_DATE DATE,
CHANGE_TYPE VARCHAR2(30 CHAR),
CLEARANCE_DISPLAY_ID VARCHAR2(30 CHAR),
CLEARANCE_GROUP_ID VARCHAR2(30 CHAR),
CLEARANCE_GROUP_DESC VARCHAR2(1000 CHAR),
CLEARANCE_GROUP_DISPLAY_ID VARCHAR2(30 CHAR),
CONFLICT_IND NUMBER(1,0),
MARKDOWN_NBR VARCHAR2(30 CHAR),
MARKDOWN_NBR_DESC VARCHAR2(250 CHAR),
OUT_OF_STOCK_DATE DATE,
REASON_CODE VARCHAR2(30 CHAR),
REASON_CODE_DESC VARCHAR2(250 CHAR),
STATE VARCHAR2(30 CHAR),
CREATED_BY_ID VARCHAR2(80 CHAR),
CHANGED_BY_ID VARCHAR2(80 CHAR),
CREATED_ON_DT DATE,
CHANGED_ON_DT DATE,
AUX1_CHANGED_ON_DT DATE,
AUX2_CHANGED_ON_DT DATE,
AUX3_CHANGED_ON_DT DATE,
AUX4_CHANGED_ON_DT DATE,
SRC_EFF_FROM_DT DATE,
SRC_EFF_TO_DT DATE,
DELETE_FLG CHAR(1 CHAR),
DATASOURCE_NUM_ID NUMBER(10,0),
INTEGRATION_ID VARCHAR2(80 CHAR),
TENANT_ID VARCHAR2(80 CHAR),
X_CUSTOM VARCHAR2(10 CHAR),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 9 — Populate CLEARANCE_HISTORY_CTRL from CLEARANCE_HISTORY_STG
-- ============================================================================
INSERT INTO CLEARANCE_HISTORY_CTRL (PROD_NUM, ORG_NUM, SUPPLIER_NUM, CLEARANCE_ID, EVENT_TYPE, EFFECTIVE_FROM_DATE, EFFECTIVE_TO_DATE, RESET_FLG, RESET_ID, EXTRACTION_DATE, CHANGE_AMOUNT, CHANGE_UOM, CHANGE_CURR_CODE, APPROVAL_DATE, CHANGE_TYPE, CLEARANCE_DISPLAY_ID, CLEARANCE_GROUP_ID, CLEARANCE_GROUP_DESC, CLEARANCE_GROUP_DISPLAY_ID, CONFLICT_IND, MARKDOWN_NBR, MARKDOWN_NBR_DESC, OUT_OF_STOCK_DATE, REASON_CODE, REASON_CODE_DESC, STATE, CREATED_BY_ID, CHANGED_BY_ID, CREATED_ON_DT, CHANGED_ON_DT, AUX1_CHANGED_ON_DT, AUX2_CHANGED_ON_DT, AUX3_CHANGED_ON_DT, AUX4_CHANGED_ON_DT, SRC_EFF_FROM_DT, SRC_EFF_TO_DT, DELETE_FLG, DATASOURCE_NUM_ID, INTEGRATION_ID, TENANT_ID, X_CUSTOM, INSERTED_AT)
SELECT s.ITEM, s.ORG_NUM, '-1', 900000000000000 + ROW_NUMBER() OVER (ORDER BY s.ITEM, s.ORG_NUM, s.START_DATE) - 1, 'CRE', s.START_DATE, s.END_DATE, 'N', CAST(NULL AS VARCHAR2(30 CHAR)), CAST(NULL AS DATE), s.CLEARANCE_CHANGE_AMOUNT, 'EA', s.CURRENCY_CODE, CAST(NULL AS DATE), s.PRICE_CHANGE_TRAN_TYPE, 900000000000000 + ROW_NUMBER() OVER (ORDER BY s.ITEM, s.ORG_NUM, s.START_DATE) - 1, 900000000000000 + ROW_NUMBER() OVER (ORDER BY s.ITEM, s.ORG_NUM, s.START_DATE) - 1, CAST(NULL AS VARCHAR2(1000 CHAR)), CAST(NULL AS VARCHAR2(30 CHAR)), 0, ROW_NUMBER() OVER (PARTITION BY s.ITEM, s.ORG_NUM ORDER BY s.START_DATE), s.CLEARANCE_MARKDOWN_NBR_DESC, CAST(NULL AS DATE), CAST(NULL AS VARCHAR2(30 CHAR)), s.CLEARANCE_REASON_CODE_DESC, CAST(NULL AS VARCHAR2(30 CHAR)), CAST(NULL AS VARCHAR2(80 CHAR)), CAST(NULL AS VARCHAR2(80 CHAR)), SYSDATE, SYSDATE, CAST(NULL AS DATE), CAST(NULL AS DATE), CAST(NULL AS DATE), CAST(NULL AS DATE), CAST(NULL AS DATE), CAST(NULL AS DATE), CAST(NULL AS CHAR(1 CHAR)), 1, s.ITEM || '~' || s.ORG_NUM || '~' || (900000000000000 + ROW_NUMBER() OVER (ORDER BY s.ITEM, s.ORG_NUM, s.START_DATE) - 1) || '~' || 1, CAST(NULL AS VARCHAR2(80 CHAR)), CAST(NULL AS VARCHAR2(10 CHAR)), SYSDATE
FROM CLEARANCE_HISTORY_STG s;
COMMIT;
SELECT COUNT(*) AS ctrl_rows FROM CLEARANCE_HISTORY_CTRL;
-- ============================================================================
-- STEP 10 — Rejection summary by rule
-- ============================================================================
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM CLEARANCE_HISTORY_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;