price_clearance_delta.sql

Source: DMF/PuC/AIF/FelipeBkp/price_clearance_delta.sql

-- ============================================================================
-- PRICE CLEARANCE DELTA — Full Validation Pipeline
-- ============================================================================
-- Fact:           price_clearance_delta
-- Source:         CLEARANCE_HISTORY_DELTA_RAW
-- Rejected:       CLEARANCE_HISTORY_DELTA_REJECTED
-- STG (accepted): CLEARANCE_HISTORY_DELTA_STG
-- Rules:          50
-- 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-10T20:01:32.
-- 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_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_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__ORGANIZATION_CSV' AS dim, COUNT(*) FROM DMF_VALIDITY__ORGANIZATION_CSV;
SELECT 'DMF_VALIDITY__ORGANIZAT_A4FC2D' AS dim, COUNT(*) FROM DMF_VALIDITY__ORGANIZAT_A4FC2D;
 
-- ============================================================================
-- STEP 2 — Materialize normalized source into DMF_SOURCE__PRICE_CLEARANCE_DE
-- ============================================================================
-- 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_DE PURGE;  -- ignore ORA-00942 on first run
CREATE TABLE DMF_SOURCE__PRICE_CLEARANCE_DE 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 CLEARANCE_HISTORY_DELTA_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_DE (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_DE NOPARALLEL;
 
-- Sanity check:
SELECT COUNT(*) FROM DMF_SOURCE__PRICE_CLEARANCE_DE;
 
-- ============================================================================
-- STEP 3 — Materialize duplicate-key set into DMF_DUPKEYS__PRICE_CLEARANCE_D
-- ============================================================================
-- 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_D PURGE;  -- ignore ORA-00942 on first run
CREATE TABLE DMF_DUPKEYS__PRICE_CLEARANCE_D AS
SELECT ITEM, ORG_NUM, START_DATE, END_DATE, PRICE_CHANGE_TRAN_TYPE FROM DMF_SOURCE__PRICE_CLEARANCE_DE
 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_D (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_D;
 
-- ============================================================================
-- STEP 4 — Ensure CLEARANCE_HISTORY_DELTA_REJECTED exists, then empty it
-- ============================================================================
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE CLEARANCE_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),     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_DELTA_REJECTED;
-- If TRUNCATE fails with ORA-00054 (orphan lock), use DELETE instead:
-- DELETE FROM CLEARANCE_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 CLEARANCE_HISTORY_DELTA_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_DE
)
SELECT 'E', 'INVALID FORMAT: PROD_NUM', '20260610T200132_3665eb7d', 'ERROR_DTYPE_PROD_NUM', 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 PROD_NUM IS NOT NULL AND LENGTHB(TO_CHAR(PROD_NUM)) > 30
UNION ALL
SELECT 'E', 'PROD_NUM NOT FOUND: ITEM_MASTER_CTRL', '20260610T200132_3665eb7d', 'ERROR_MISSING_PROD_NUM', 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 PROD_NUM IS NOT NULL AND NOT EXISTS (SELECT 1 FROM ITEM_MASTER_CTRL d WHERE d.ITEM = s.PROD_NUM AND CTRL_STATUS = 'C')
UNION ALL
SELECT 'E', 'INVALID FORMAT: ORG_NUM', '20260610T200132_3665eb7d', 'ERROR_DTYPE_ORG_NUM', 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 LENGTHB(TO_CHAR(ORG_NUM)) > 30
UNION ALL
SELECT 'E', 'ORG_NUM NOT FOUND: STORE_ADD_CTRL', '20260610T200132_3665eb7d', 'ERROR_MISSING_ORG_NUM', 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__ORGANIZATION_CSV d WHERE d.ORG_NUM = s.ORG_NUM)
UNION ALL
SELECT 'E', 'INVALID FORMAT: SUPPLIER_NUM', '20260610T200132_3665eb7d', 'ERROR_DTYPE_SUPPLIER_NUM', 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 SUPPLIER_NUM IS NOT NULL AND LENGTHB(TO_CHAR(SUPPLIER_NUM)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: CLEARANCE_ID', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CLEARANCE_ID', 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_ID IS NOT NULL AND LENGTHB(TO_CHAR(CLEARANCE_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: EVENT_TYPE', '20260610T200132_3665eb7d', 'ERROR_DTYPE_EVENT_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 EVENT_TYPE IS NOT NULL AND LENGTHB(TO_CHAR(EVENT_TYPE)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: EFFECTIVE_FROM_DATE', '20260610T200132_3665eb7d', 'ERROR_DTYPE_EFFECTIVE_FROM_DATE', 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 EFFECTIVE_FROM_DATE IS NOT NULL AND TRUNC(EFFECTIVE_FROM_DATE) <> EFFECTIVE_FROM_DATE
UNION ALL
SELECT 'E', 'INVALID FORMAT: EFFECTIVE_TO_DATE', '20260610T200132_3665eb7d', 'ERROR_DTYPE_EFFECTIVE_TO_DATE', 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 EFFECTIVE_TO_DATE IS NOT NULL AND TRUNC(EFFECTIVE_TO_DATE) <> EFFECTIVE_TO_DATE
UNION ALL
SELECT 'E', 'INVALID FORMAT: RESET_FLG', '20260610T200132_3665eb7d', 'ERROR_DTYPE_RESET_FLG', 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 RESET_FLG IS NOT NULL AND LENGTHB(TO_CHAR(RESET_FLG)) > 1
UNION ALL
SELECT 'E', 'INVALID FORMAT: RESET_ID', '20260610T200132_3665eb7d', 'ERROR_DTYPE_RESET_ID', 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 RESET_ID IS NOT NULL AND LENGTHB(TO_CHAR(RESET_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: EXTRACTION_DATE', '20260610T200132_3665eb7d', 'ERROR_DTYPE_EXTRACTION_DATE', 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 EXTRACTION_DATE IS NOT NULL AND TRUNC(EXTRACTION_DATE) <> EXTRACTION_DATE
UNION ALL
SELECT 'E', 'INVALID FORMAT: CHANGE_AMOUNT', '20260610T200132_3665eb7d', 'ERROR_DTYPE_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 CHANGE_AMOUNT IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(CHANGE_AMOUNT))) >= POWER(10,16) OR TO_NUMBER(CHANGE_AMOUNT) != ROUND(TO_NUMBER(CHANGE_AMOUNT),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: CHANGE_UOM', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CHANGE_UOM', 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 CHANGE_UOM IS NOT NULL AND LENGTHB(TO_CHAR(CHANGE_UOM)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: CHANGE_CURR_CODE', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CHANGE_CURR_CODE', 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 CHANGE_CURR_CODE IS NOT NULL AND LENGTHB(TO_CHAR(CHANGE_CURR_CODE)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: APPROVAL_DATE', '20260610T200132_3665eb7d', 'ERROR_DTYPE_APPROVAL_DATE', 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 APPROVAL_DATE IS NOT NULL AND TRUNC(APPROVAL_DATE) <> APPROVAL_DATE
UNION ALL
SELECT 'E', 'INVALID FORMAT: CHANGE_TYPE', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CHANGE_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 CHANGE_TYPE IS NOT NULL AND LENGTHB(TO_CHAR(CHANGE_TYPE)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: CLEARANCE_DISPLAY_ID', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CLEARANCE_DISPLAY_ID', 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_DISPLAY_ID IS NOT NULL AND LENGTHB(TO_CHAR(CLEARANCE_DISPLAY_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: CLEARANCE_GROUP_ID', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CLEARANCE_GROUP_ID', 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_GROUP_ID IS NOT NULL AND LENGTHB(TO_CHAR(CLEARANCE_GROUP_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: CLEARANCE_GROUP_DESC', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CLEARANCE_GROUP_DESC', 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_GROUP_DESC IS NOT NULL AND LENGTHB(TO_CHAR(CLEARANCE_GROUP_DESC)) > 1000
UNION ALL
SELECT 'E', 'INVALID FORMAT: CLEARANCE_GROUP_DISPLAY_ID', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CLEARANCE_GROUP_DISPLAY_ID', 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_GROUP_DISPLAY_ID IS NOT NULL AND LENGTHB(TO_CHAR(CLEARANCE_GROUP_DISPLAY_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: CONFLICT_IND', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CONFLICT_IND', 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 CONFLICT_IND IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(CONFLICT_IND))) >= POWER(10,1) OR TO_NUMBER(CONFLICT_IND) != ROUND(TO_NUMBER(CONFLICT_IND),0))
UNION ALL
SELECT 'E', 'INVALID FORMAT: MARKDOWN_NBR', '20260610T200132_3665eb7d', 'ERROR_DTYPE_MARKDOWN_NBR', 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 MARKDOWN_NBR IS NOT NULL AND LENGTHB(TO_CHAR(MARKDOWN_NBR)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: MARKDOWN_NBR_DESC', '20260610T200132_3665eb7d', 'ERROR_DTYPE_MARKDOWN_NBR_DESC', 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 MARKDOWN_NBR_DESC IS NOT NULL AND LENGTHB(TO_CHAR(MARKDOWN_NBR_DESC)) > 250
UNION ALL
SELECT 'E', 'INVALID FORMAT: OUT_OF_STOCK_DATE', '20260610T200132_3665eb7d', 'ERROR_DTYPE_OUT_OF_STOCK_DATE', 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 OUT_OF_STOCK_DATE IS NOT NULL AND TRUNC(OUT_OF_STOCK_DATE) <> OUT_OF_STOCK_DATE
UNION ALL
SELECT 'E', 'INVALID FORMAT: REASON_CODE', '20260610T200132_3665eb7d', 'ERROR_DTYPE_REASON_CODE', 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 REASON_CODE IS NOT NULL AND LENGTHB(TO_CHAR(REASON_CODE)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: REASON_CODE_DESC', '20260610T200132_3665eb7d', 'ERROR_DTYPE_REASON_CODE_DESC', 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 REASON_CODE_DESC IS NOT NULL AND LENGTHB(TO_CHAR(REASON_CODE_DESC)) > 250
UNION ALL
SELECT 'E', 'INVALID FORMAT: STATE', '20260610T200132_3665eb7d', 'ERROR_DTYPE_STATE', 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 STATE IS NOT NULL AND LENGTHB(TO_CHAR(STATE)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: CREATED_BY_ID', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CREATED_BY_ID', 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 CREATED_BY_ID IS NOT NULL AND LENGTHB(TO_CHAR(CREATED_BY_ID)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: CHANGED_BY_ID', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CHANGED_BY_ID', 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 CHANGED_BY_ID IS NOT NULL AND LENGTHB(TO_CHAR(CHANGED_BY_ID)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: CREATED_ON_DT', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CREATED_ON_DT', 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 CREATED_ON_DT IS NOT NULL AND TRUNC(CREATED_ON_DT) <> CREATED_ON_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: CHANGED_ON_DT', '20260610T200132_3665eb7d', 'ERROR_DTYPE_CHANGED_ON_DT', 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 CHANGED_ON_DT IS NOT NULL AND TRUNC(CHANGED_ON_DT) <> CHANGED_ON_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: AUX1_CHANGED_ON_DT', '20260610T200132_3665eb7d', 'ERROR_DTYPE_AUX1_CHANGED_ON_DT', 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 AUX1_CHANGED_ON_DT IS NOT NULL AND TRUNC(AUX1_CHANGED_ON_DT) <> AUX1_CHANGED_ON_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: AUX2_CHANGED_ON_DT', '20260610T200132_3665eb7d', 'ERROR_DTYPE_AUX2_CHANGED_ON_DT', 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 AUX2_CHANGED_ON_DT IS NOT NULL AND TRUNC(AUX2_CHANGED_ON_DT) <> AUX2_CHANGED_ON_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: AUX3_CHANGED_ON_DT', '20260610T200132_3665eb7d', 'ERROR_DTYPE_AUX3_CHANGED_ON_DT', 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 AUX3_CHANGED_ON_DT IS NOT NULL AND TRUNC(AUX3_CHANGED_ON_DT) <> AUX3_CHANGED_ON_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: AUX4_CHANGED_ON_DT', '20260610T200132_3665eb7d', 'ERROR_DTYPE_AUX4_CHANGED_ON_DT', 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 AUX4_CHANGED_ON_DT IS NOT NULL AND TRUNC(AUX4_CHANGED_ON_DT) <> AUX4_CHANGED_ON_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: SRC_EFF_FROM_DT', '20260610T200132_3665eb7d', 'ERROR_DTYPE_SRC_EFF_FROM_DT', 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 SRC_EFF_FROM_DT IS NOT NULL AND TRUNC(SRC_EFF_FROM_DT) <> SRC_EFF_FROM_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: SRC_EFF_TO_DT', '20260610T200132_3665eb7d', 'ERROR_DTYPE_SRC_EFF_TO_DT', 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 SRC_EFF_TO_DT IS NOT NULL AND TRUNC(SRC_EFF_TO_DT) <> SRC_EFF_TO_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: DELETE_FLG', '20260610T200132_3665eb7d', 'ERROR_DTYPE_DELETE_FLG', 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 DELETE_FLG IS NOT NULL AND LENGTHB(TO_CHAR(DELETE_FLG)) > 1
UNION ALL
SELECT 'E', 'INVALID FORMAT: DATASOURCE_NUM_ID', '20260610T200132_3665eb7d', 'ERROR_DTYPE_DATASOURCE_NUM_ID', 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 DATASOURCE_NUM_ID IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(DATASOURCE_NUM_ID))) >= POWER(10,10) OR TO_NUMBER(DATASOURCE_NUM_ID) != ROUND(TO_NUMBER(DATASOURCE_NUM_ID),0))
UNION ALL
SELECT 'E', 'INVALID FORMAT: INTEGRATION_ID', '20260610T200132_3665eb7d', 'ERROR_DTYPE_INTEGRATION_ID', 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 INTEGRATION_ID IS NOT NULL AND LENGTHB(TO_CHAR(INTEGRATION_ID)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: TENANT_ID', '20260610T200132_3665eb7d', 'ERROR_DTYPE_TENANT_ID', 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 TENANT_ID IS NOT NULL AND LENGTHB(TO_CHAR(TENANT_ID)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: X_CUSTOM', '20260610T200132_3665eb7d', 'ERROR_DTYPE_X_CUSTOM', 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 X_CUSTOM IS NOT NULL AND LENGTHB(TO_CHAR(X_CUSTOM)) > 10
UNION ALL
SELECT 'E', 'ITEM IS NULL', '20260610T200132_3665eb7d', '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', '20260610T200132_3665eb7d', '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', '20260610T200132_3665eb7d', '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', '20260610T200132_3665eb7d', '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', '20260610T200132_3665eb7d', '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', '20260610T200132_3665eb7d', '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', '20260610T200132_3665eb7d', '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', '20260610T200132_3665eb7d', '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', '20260610T200132_3665eb7d', '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', '20260610T200132_3665eb7d', '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', '20260610T200132_3665eb7d', '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_D);
COMMIT;
 
-- ============================================================================
-- STEP 6 — Build typed CLEARANCE_HISTORY_DELTA_STG (drop-and-recreate)
-- ============================================================================
-- DDL types are pulled from the RAP spec (not VARCHAR2(4000) blanket).
DROP TABLE CLEARANCE_HISTORY_DELTA_STG PURGE;  -- ignore ORA-00942
CREATE TABLE CLEARANCE_HISTORY_DELTA_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_DELTA_STG (anti-join on SRC_ROWID; LOG_LVL='W' rows still flow through)
-- ============================================================================
INSERT INTO CLEARANCE_HISTORY_DELTA_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_DE
)
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_DELTA_REJECTED r
  WHERE r.VALIDATION_RUN_ID = '20260610T200132_3665eb7d'
    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(CLEARANCE_HISTORY_DELTA_RAW) minus distinct SRC_ROWIDs in CLEARANCE_HISTORY_DELTA_REJECTED where LOG_LVL='E'.
SELECT COUNT(*) AS stg_rows FROM CLEARANCE_HISTORY_DELTA_STG;
 
-- ============================================================================
-- STEP 8 — Build CLEARANCE_HISTORY_DELTA_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_DELTA_CTRL PURGE;  -- ignore ORA-00942
CREATE TABLE CLEARANCE_HISTORY_DELTA_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_DELTA_CTRL from CLEARANCE_HISTORY_DELTA_STG
-- ============================================================================
INSERT INTO CLEARANCE_HISTORY_DELTA_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_DELTA_STG s;
COMMIT;
SELECT COUNT(*) AS ctrl_rows FROM CLEARANCE_HISTORY_DELTA_CTRL;
 
-- ============================================================================
-- STEP 10 — Rejection summary by rule
-- ============================================================================
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
  FROM CLEARANCE_HISTORY_DELTA_REJECTED
 GROUP BY RULE_ID, LOG_LVL
 ORDER BY rejected_rows DESC;