Source: DMF/PuC/AIF/Inventory position.sql
# ORI_INVENTORY — RAW → REJECTED → CTRL (STG bypassed)
-- ============================================================================
-- RAW → REJECTED → CTRL (STG bypassed)
-- ============================================================================
-- Fact: inventory
-- Source: ORI_INVENTORY_RAW
-- Rejected: ORI_INVENTORY_REJECTED
-- CTRL (export): ORI_INVENTORY_CTRL
-- Rules: 17
--
-- Run order: SETUP → PHASE 1 → PHASE 2.
-- ############################################################################
-- SETUP — materialization tables (shared by both phases)
-- ############################################################################
-- ============================================================================
-- 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.
-- The runtime equivalent is `validate-cross run … --refresh-validity`.
-- Dimension 'item_valid' → DMF_VALIDITY__ITEM_VALID
DROP TABLE DMF_VALIDITY__ITEM_VALID PURGE;
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 → DMF_VALIDITY__ORGANIZATION
DROP TABLE DMF_VALIDITY__ORGANIZATION_CSV PURGE;
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);
-- 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;
-- ============================================================================
-- STEP 1b — Materialize normalized source into DMF_SOURCE__INVENTORY
-- ============================================================================
-- One scan of RAW now → all subsequent rules read from a smaller, hotter,
-- indexed snapshot. Source CTAS override applied (column renames / JOINs).
DROP TABLE DMF_SOURCE__INVENTORY PURGE;
CREATE TABLE DMF_SOURCE__INVENTORY PARALLEL 4 AS
SELECT /*+ PARALLEL(4) */
r.ROWID AS SRC_ROWID,
r.ITEM,
LTRIM(r.ORG_NUM, '0') AS ORG_NUM,
r.DAY_DT,
r.CLEARANCE_FLG,
r.INV_SOH_QTY,
r.INV_SOH_COST_AMT_LCL,
r.INV_SOH_RTL_AMT_LCL,
r.INV_UNIT_RTL_AMT_LCL,
r.INV_AVG_COST_AMT_LCL,
r.DOC_CURR_CODE,
r.LOC_CURR_CODE,
r.UPDATED_AT
FROM ORI_INVENTORY_RAW r;
-- Index on the row-identity tuple makes duplicate_key and the CTRL
-- anti-join cheap.
CREATE INDEX IX_DMF_SOURCE__INVENTORY_RID ON DMF_SOURCE__INVENTORY (ITEM, ORG_NUM, DAY_DT, CLEARANCE_FLG);
-- Reset parallel attribute so per-rule reads don't auto-parallelize.
ALTER TABLE DMF_SOURCE__INVENTORY NOPARALLEL;
-- Sanity check:
SELECT COUNT(*) FROM DMF_SOURCE__INVENTORY;
-- ============================================================================
-- STEP 1c — Materialize duplicate-key set into DMF_DUPKEYS__INVENTORY
-- ============================================================================
-- 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__INVENTORY PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_DUPKEYS__INVENTORY AS
SELECT ITEM, ORG_NUM, DAY_DT, CLEARANCE_FLG FROM DMF_SOURCE__INVENTORY
GROUP BY ITEM, ORG_NUM, DAY_DT, CLEARANCE_FLG
HAVING COUNT(*) > 1;
CREATE INDEX IX_DMF_DUPKEYS__INVENTORY_KEY ON DMF_DUPKEYS__INVENTORY (ITEM, ORG_NUM, DAY_DT, CLEARANCE_FLG);
-- Sanity check (size of the conflict set):
SELECT COUNT(*) FROM DMF_DUPKEYS__INVENTORY;
-- ############################################################################
-- PHASE 1 — RAW → REJECTED (rule evaluation, no CTRL touched)
-- ############################################################################
-- ============================================================================
-- STEP 2 — Ensure ORI_INVENTORY_REJECTED exists, then empty it
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ORI_INVENTORY_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), CLEARANCE_FLG VARCHAR2(4000), INV_SOH_QTY VARCHAR2(4000), INV_SOH_COST_AMT_LCL VARCHAR2(4000), INV_SOH_RTL_AMT_LCL VARCHAR2(4000), INV_UNIT_RTL_AMT_LCL VARCHAR2(4000), INV_AVG_COST_AMT_LCL VARCHAR2(4000), DOC_CURR_CODE VARCHAR2(4000), LOC_CURR_CODE VARCHAR2(4000), UPDATED_AT 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_INVENTORY_REJECTED;
-- If TRUNCATE fails with ORA-00054 (orphan lock), use DELETE instead:
-- DELETE FROM ORI_INVENTORY_REJECTED;
-- COMMIT;
-- ============================================================================
-- STEP 3 — Unified UNION ALL INSERT (single source scan; framework default)
-- ============================================================================
-- One INSERT covers every rule via UNION ALL. Oracle scans
-- DMF_SOURCE__INVENTORY once and evaluates each rule's predicate against the
-- buffered table. This is the path `validate-cross run` takes unless
-- --legacy-per-rule is set. For the per-rule variant see the APPENDIX at the
-- bottom of this file — do NOT run it in the same pass as this statement.
INSERT INTO ORI_INVENTORY_REJECTED (
LOG_LVL, ERROR_MSG, VALIDATION_RUN_ID, RULE_ID, SRC_ROWID, ITEM, ORG_NUM, DAY_DT, CLEARANCE_FLG, INV_SOH_QTY, INV_SOH_COST_AMT_LCL, INV_SOH_RTL_AMT_LCL, INV_UNIT_RTL_AMT_LCL, INV_AVG_COST_AMT_LCL, DOC_CURR_CODE, LOC_CURR_CODE, UPDATED_AT, INSERTED_AT
)
-- RULE 1/17: ERROR_DTYPE_ITEM (type=length, severity=E)
SELECT 'E', 'INVALID FORMAT: ITEM', 'MANUAL_RUN', 'ERROR_DTYPE_ITEM', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE ITEM IS NOT NULL AND LENGTHB(TO_CHAR(ITEM)) > 80
UNION ALL
-- RULE 2/17: ERROR_DTYPE_ORG_NUM (type=length, severity=E)
SELECT 'E', 'INVALID FORMAT: ORG_NUM', 'MANUAL_RUN', 'ERROR_DTYPE_ORG_NUM', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE ORG_NUM IS NOT NULL AND LENGTHB(TO_CHAR(ORG_NUM)) > 80
UNION ALL
-- RULE 3/17: ERROR_DTYPE_CLEARANCE_FLG (type=length, severity=E)
SELECT 'E', 'INVALID FORMAT: CLEARANCE_FLG', 'MANUAL_RUN', 'ERROR_DTYPE_CLEARANCE_FLG', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE CLEARANCE_FLG IS NOT NULL AND LENGTHB(TO_CHAR(CLEARANCE_FLG)) > 1
UNION ALL
-- RULE 4/17: ERROR_DTYPE_INV_SOH_QTY (type=number_precision_scale, severity=E)
SELECT 'E', 'INVALID FORMAT: INV_SOH_QTY', 'MANUAL_RUN', 'ERROR_DTYPE_INV_SOH_QTY', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE INV_SOH_QTY IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(INV_SOH_QTY))) >= POWER(10,14) OR TO_NUMBER(INV_SOH_QTY) != ROUND(TO_NUMBER(INV_SOH_QTY),4))
UNION ALL
-- RULE 5/17: ERROR_DTYPE_INV_SOH_COST_AMT_LCL (type=number_precision_scale, severity=E)
SELECT 'E', 'INVALID FORMAT: INV_SOH_COST_AMT_LCL', 'MANUAL_RUN', 'ERROR_DTYPE_INV_SOH_COST_AMT_LCL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE INV_SOH_COST_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(INV_SOH_COST_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(INV_SOH_COST_AMT_LCL) != ROUND(TO_NUMBER(INV_SOH_COST_AMT_LCL),4))
UNION ALL
-- RULE 6/17: ERROR_DTYPE_INV_SOH_RTL_AMT_LCL (type=number_precision_scale, severity=E)
SELECT 'E', 'INVALID FORMAT: INV_SOH_RTL_AMT_LCL', 'MANUAL_RUN', 'ERROR_DTYPE_INV_SOH_RTL_AMT_LCL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE INV_SOH_RTL_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(INV_SOH_RTL_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(INV_SOH_RTL_AMT_LCL) != ROUND(TO_NUMBER(INV_SOH_RTL_AMT_LCL),4))
UNION ALL
-- RULE 7/17: ERROR_DTYPE_INV_UNIT_RTL_AMT_LCL (type=number_precision_scale, severity=E)
SELECT 'E', 'INVALID FORMAT: INV_UNIT_RTL_AMT_LCL', 'MANUAL_RUN', 'ERROR_DTYPE_INV_UNIT_RTL_AMT_LCL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE INV_UNIT_RTL_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(INV_UNIT_RTL_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(INV_UNIT_RTL_AMT_LCL) != ROUND(TO_NUMBER(INV_UNIT_RTL_AMT_LCL),4))
UNION ALL
-- RULE 8/17: ERROR_DTYPE_INV_AVG_COST_AMT_LCL (type=number_precision_scale, severity=E)
SELECT 'E', 'INVALID FORMAT: INV_AVG_COST_AMT_LCL', 'MANUAL_RUN', 'ERROR_DTYPE_INV_AVG_COST_AMT_LCL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE INV_AVG_COST_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(INV_AVG_COST_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(INV_AVG_COST_AMT_LCL) != ROUND(TO_NUMBER(INV_AVG_COST_AMT_LCL),4))
UNION ALL
-- RULE 9/17: ERROR_DTYPE_DOC_CURR_CODE (type=length, severity=E)
SELECT 'E', 'INVALID FORMAT: DOC_CURR_CODE', 'MANUAL_RUN', 'ERROR_DTYPE_DOC_CURR_CODE', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE DOC_CURR_CODE IS NOT NULL AND LENGTHB(TO_CHAR(DOC_CURR_CODE)) > 30
UNION ALL
-- RULE 10/17: ERROR_DTYPE_LOC_CURR_CODE (type=length, severity=E)
SELECT 'E', 'INVALID FORMAT: LOC_CURR_CODE', 'MANUAL_RUN', 'ERROR_DTYPE_LOC_CURR_CODE', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE LOC_CURR_CODE IS NOT NULL AND LENGTHB(TO_CHAR(LOC_CURR_CODE)) > 30
UNION ALL
-- RULE 11/17: 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.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY 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.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY 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.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE DAY_DT IS NULL
UNION ALL
SELECT 'E', 'CLEARANCE_FLG IS NULL', 'MANUAL_RUN', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE CLEARANCE_FLG IS NULL
UNION ALL
-- RULE 12/17: ERROR_INVALID_CLEARANCE_FLG (type=allowed_values, severity=E)
SELECT 'E', 'INVALID CLEARANCE_FLG: NOT IN ALLOWED VALUES', 'MANUAL_RUN', 'ERROR_INVALID_CLEARANCE_FLG', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE CLEARANCE_FLG NOT IN ('Y', 'N')
UNION ALL
-- RULE 13/17: ERROR_DTYPE_DAY_DT (type=condition, severity=E)
SELECT 'E', 'ERROR_DTYPE_DAY_DT', 'MANUAL_RUN', 'ERROR_DTYPE_DAY_DT', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE DAY_DT IS NOT NULL AND TRUNC(DAY_DT) <> DAY_DT
UNION ALL
-- RULE 14/17: 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.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE ITEM IS NOT NULL AND NOT EXISTS (SELECT 1 FROM DMF_VALIDITY__ITEM_VALID d WHERE d.ITEM = s.ITEM)
UNION ALL
-- RULE 15/17: ERROR_MISSING_ORG_NUM (type=dimension_exists, severity=E)
SELECT 'E', 'ORG_NUM NOT FOUND: STORE_ADD_CTRL', 'MANUAL_RUN', 'ERROR_MISSING_ORG_NUM', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY 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 16/17: WARN_NEGATIVE_INV (type=condition, severity=W)
SELECT 'W', 'WARN_NEGATIVE_INV', 'MANUAL_RUN', 'WARN_NEGATIVE_INV', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE INV_SOH_QTY < 0 OR INV_SOH_COST_AMT_LCL < 0 OR INV_SOH_RTL_AMT_LCL < 0
UNION ALL
-- RULE 17/17: ERR_DUP_KEY_CONFLICT (type=duplicate_key, severity=E)
SELECT 'E', 'DUPLICATE KEY: ITEM, ORG_NUM, DAY_DT, CLEARANCE_FLG', 'MANUAL_RUN', 'ERR_DUP_KEY_CONFLICT', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, s.INV_SOH_QTY, s.INV_SOH_COST_AMT_LCL, s.INV_SOH_RTL_AMT_LCL, s.INV_UNIT_RTL_AMT_LCL, s.INV_AVG_COST_AMT_LCL, s.DOC_CURR_CODE, s.LOC_CURR_CODE, s.UPDATED_AT, SYSDATE FROM DMF_SOURCE__INVENTORY s WHERE (ITEM, ORG_NUM, DAY_DT, CLEARANCE_FLG) IN (SELECT ITEM, ORG_NUM, DAY_DT, CLEARANCE_FLG FROM DMF_DUPKEYS__INVENTORY);
COMMIT;
-- The CTRL anti-join in STEP 5 probes ORI_INVENTORY_REJECTED by SRC_ROWID.
-- Build the index now (cheap here, expensive to miss during the anti-join).
CREATE INDEX IX_ORI_INVENTORY_REJ_ROWID ON ORI_INVENTORY_REJECTED (SRC_ROWID, LOG_LVL, VALIDATION_RUN_ID);
-- Phase 1 checkpoint — rejection summary by rule:
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM ORI_INVENTORY_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;
-- Expected CTRL row count (compute now, compare after STEP 5):
SELECT (SELECT COUNT(*) FROM DMF_SOURCE__INVENTORY)
- (SELECT COUNT(DISTINCT SRC_ROWID) FROM ORI_INVENTORY_REJECTED
WHERE VALIDATION_RUN_ID = 'MANUAL_RUN' AND LOG_LVL = 'E')
AS expected_ctrl_rows
FROM DUAL;
-- ############################################################################
-- PHASE 2 — REJECTED → CTRL (export shape, STG bypassed)
-- ############################################################################
-- ============================================================================
-- STEP 4 — Build ORI_INVENTORY_CTRL (drop-and-recreate)
-- ============================================================================
-- Final Oracle export shape. Column projection comes from
-- `[ctrl_projection]` in the override; default = identity from the source.
DROP TABLE ORI_INVENTORY_CTRL PURGE; -- ignore ORA-00942
CREATE TABLE ORI_INVENTORY_CTRL (
ITEM VARCHAR2(80 BYTE),
ORG_NUM VARCHAR2(80 BYTE),
DAY_DT DATE,
CLEARANCE_FLG CHAR(1 BYTE),
INV_REPL_FLG CHAR(1 BYTE),
INV_REPL_METHOD_TYPE CHAR(2 BYTE),
INV_REPL_INCREMENT_PCT NUMBER(12,4),
INV_CO_RSV_QTY NUMBER(18,4),
INV_CO_BO_RSV_QTY NUMBER(18,4),
INV_SOH_QTY NUMBER(18,4),
INV_SOH_COST_AMT_LCL NUMBER(20,4),
INV_SOH_RTL_AMT_LCL NUMBER(20,4),
INV_ON_ORD_QTY NUMBER(18,4),
INV_ON_ORD_COST_AMT_LCL NUMBER(20,4),
INV_ON_ORD_RTL_AMT_LCL NUMBER(20,4),
INV_IN_TRAN_QTY NUMBER(18,4),
INV_IN_TRAN_COST_AMT_LCL NUMBER(20,4),
INV_IN_TRAN_RTL_AMT_LCL NUMBER(20,4),
INV_MAX_SOH_QTY NUMBER(18,4),
INV_MAX_SOH_COST_AMT_LCL NUMBER(20,4),
INV_MAX_SOH_RTL_AMT_LCL NUMBER(20,4),
INV_MIN_SOH_QTY NUMBER(18,4),
INV_MIN_SOH_COST_AMT_LCL NUMBER(20,4),
INV_MIN_SOH_RTL_AMT_LCL NUMBER(20,4),
INV_TSF_RESV_QTY NUMBER(18,4),
INV_TSF_RESV_COST_AMT_LCL NUMBER(20,4),
INV_TSF_RESV_RTL_AMT_LCL NUMBER(20,4),
INV_TSF_EXP_QTY NUMBER(18,4),
INV_TSF_EXP_COST_AMT_LCL NUMBER(20,4),
INV_TSF_EXP_RTL_AMT_LCL NUMBER(20,4),
INV_RTV_RESV_QTY NUMBER(18,4),
INV_RTV_RESV_COST_AMT_LCL NUMBER(20,4),
INV_RTV_RESV_RTL_AMT_LCL NUMBER(20,4),
INV_UNIT_RTL_AMT_LCL NUMBER(20,4),
INV_AVG_COST_AMT_LCL NUMBER(20,4),
INV_UNIT_COST_AMT_LCL NUMBER(20,4),
FINISHER_QTY NUMBER(18,4),
PURCH_TYPE_CODE VARCHAR2(50 BYTE),
DOC_CURR_CODE VARCHAR2(30 BYTE),
LOC_CURR_CODE VARCHAR2(30 BYTE),
LOC_EXCHANGE_RATE NUMBER(22,7),
GLOBAL1_EXCHANGE_RATE NUMBER(22,7),
GLOBAL2_EXCHANGE_RATE NUMBER(22,7),
GLOBAL3_EXCHANGE_RATE NUMBER(22,7),
DELETE_FLG CHAR(1 BYTE),
DATASOURCE_NUM_ID NUMBER(10,0),
CUM_MARKON_PCT NUMBER(18,4),
INV_IP_SLS_QTY NUMBER(12,4),
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),
INV_COMP_SOH_QTY NUMBER(18,4),
INV_COMP_IN_TRAN_QTY NUMBER(18,4),
INV_COMP_ON_ORD_QTY NUMBER(18,4),
INV_COMP_TSF_RESV_QTY NUMBER(18,4),
INV_COMP_TSF_EXP_QTY NUMBER(18,4),
INV_COMP_CO_RSV_QTY NUMBER(18,4),
INV_COMP_CO_BO_RSV_QTY NUMBER(18,4),
SELLING_PHASE_START_DT NUMBER(18,4),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 5 — Populate ORI_INVENTORY_CTRL directly from DMF_SOURCE__INVENTORY
-- ============================================================================
-- Merges the old STEP 5 (STG anti-join) and STEP 7 (CTRL projection) into one
-- statement. Anti-join on SRC_ROWID; LOG_LVL='W' rows still flow through.
INSERT /*+ APPEND */ INTO ORI_INVENTORY_CTRL (ITEM, ORG_NUM, DAY_DT, CLEARANCE_FLG, INV_REPL_FLG, INV_REPL_METHOD_TYPE, INV_REPL_INCREMENT_PCT, INV_CO_RSV_QTY, INV_CO_BO_RSV_QTY, INV_SOH_QTY, INV_SOH_COST_AMT_LCL, INV_SOH_RTL_AMT_LCL, INV_ON_ORD_QTY, INV_ON_ORD_COST_AMT_LCL, INV_ON_ORD_RTL_AMT_LCL, INV_IN_TRAN_QTY, INV_IN_TRAN_COST_AMT_LCL, INV_IN_TRAN_RTL_AMT_LCL, INV_MAX_SOH_QTY, INV_MAX_SOH_COST_AMT_LCL, INV_MAX_SOH_RTL_AMT_LCL, INV_MIN_SOH_QTY, INV_MIN_SOH_COST_AMT_LCL, INV_MIN_SOH_RTL_AMT_LCL, INV_TSF_RESV_QTY, INV_TSF_RESV_COST_AMT_LCL, INV_TSF_RESV_RTL_AMT_LCL, INV_TSF_EXP_QTY, INV_TSF_EXP_COST_AMT_LCL, INV_TSF_EXP_RTL_AMT_LCL, INV_RTV_RESV_QTY, INV_RTV_RESV_COST_AMT_LCL, INV_RTV_RESV_RTL_AMT_LCL, INV_UNIT_RTL_AMT_LCL, INV_AVG_COST_AMT_LCL, INV_UNIT_COST_AMT_LCL, FINISHER_QTY, PURCH_TYPE_CODE, DOC_CURR_CODE, LOC_CURR_CODE, LOC_EXCHANGE_RATE, GLOBAL1_EXCHANGE_RATE, GLOBAL2_EXCHANGE_RATE, GLOBAL3_EXCHANGE_RATE, DELETE_FLG, DATASOURCE_NUM_ID, CUM_MARKON_PCT, INV_IP_SLS_QTY, 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, INV_COMP_SOH_QTY, INV_COMP_IN_TRAN_QTY, INV_COMP_ON_ORD_QTY, INV_COMP_TSF_RESV_QTY, INV_COMP_TSF_EXP_QTY, INV_COMP_CO_RSV_QTY, INV_COMP_CO_BO_RSV_QTY, SELLING_PHASE_START_DT, INSERTED_AT)
SELECT s.ITEM, s.ORG_NUM, s.DAY_DT, s.CLEARANCE_FLG, CAST(NULL AS CHAR(1 BYTE)), CAST(NULL AS CHAR(2 BYTE)), CAST(NULL AS NUMBER(12,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CASE WHEN s.INV_SOH_QTY IS NULL OR ABS(TRUNC(s.INV_SOH_QTY)) < 100000000000000 THEN s.INV_SOH_QTY ELSE NULL END, CASE WHEN s.INV_SOH_COST_AMT_LCL IS NULL OR ABS(TRUNC(s.INV_SOH_COST_AMT_LCL)) < 10000000000000000 THEN s.INV_SOH_COST_AMT_LCL ELSE NULL END, CASE WHEN s.INV_SOH_RTL_AMT_LCL IS NULL OR ABS(TRUNC(s.INV_SOH_RTL_AMT_LCL)) < 10000000000000000 THEN s.INV_SOH_RTL_AMT_LCL ELSE NULL END, CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(20,4)), CASE WHEN s.INV_UNIT_RTL_AMT_LCL IS NULL OR ABS(TRUNC(s.INV_UNIT_RTL_AMT_LCL)) < 10000000000000000 THEN s.INV_UNIT_RTL_AMT_LCL ELSE NULL END, CASE WHEN s.INV_AVG_COST_AMT_LCL IS NULL OR ABS(TRUNC(s.INV_AVG_COST_AMT_LCL)) < 10000000000000000 THEN s.INV_AVG_COST_AMT_LCL ELSE NULL END, CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS VARCHAR2(50 BYTE)), s.DOC_CURR_CODE, s.LOC_CURR_CODE, CAST(NULL AS NUMBER(22,7)), CAST(NULL AS NUMBER(22,7)), CAST(NULL AS NUMBER(22,7)), CAST(NULL AS NUMBER(22,7)), CAST(NULL AS CHAR(1 BYTE)), CAST(NULL AS NUMBER(10,0)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(12,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), SYSDATE
FROM DMF_SOURCE__INVENTORY s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_INVENTORY_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.CLEARANCE_FLG IS NOT NULL;
COMMIT;
-- Compare against expected_ctrl_rows from the end of PHASE 1:
SELECT COUNT(*) AS ctrl_rows FROM ORI_INVENTORY_CTRL;
-- ############################################################################
-- FINAL — Rejection summary by rule
-- ############################################################################
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM ORI_INVENTORY_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;