Source: DMF/PuC/AIF/Price history.sql
-- ============================================================================
-- RAW → REJECTED → CTRL (STG bypassed)
-- ============================================================================
-- Fact: price_history
-- Source: PRICE_HISTORY_RAW
-- Rejected: PRICE_HISTORY_REJECTED
-- CTRL (export): PRICE_HISTORY_CTRL
-- Rules: 7 (10 UNION ALL branches — ERROR_INVALID_NULL covers 4 fields)
-- Dimensions: item_valid, organization_wh_direct
-- Run order: SETUP → PHASE 1 → PHASE 2.
-- Recommended preamble:
ALTER SESSION SET DDL_LOCK_TIMEOUT = 300;
-- ############################################################################
-- 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
-- Accepts an item that is either controlled directly in ITEM_MASTER_CTRL, or
-- reachable through the IBC↔retail cross-reference (item_pv1 → item_pr1).
-- Same definition as the inventory fact, so both validate items identically.
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 1b — Materialize normalized source into DMF_SOURCE__PRICE_HISTORY
-- ============================================================================
-- One scan of RAW now → all subsequent rules read from a smaller, hotter,
-- indexed snapshot. Source CTAS override applied (column renames / JOINs).
--
-- NOTE: with STG removed, this snapshot is also the input to the CTRL build.
-- Do not drop it until PHASE 2 has committed.
DROP TABLE DMF_SOURCE__PRICE_HISTORY PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_SOURCE__PRICE_HISTORY PARALLEL 4 AS
SELECT /*+ PARALLEL(4) */
ROWID AS SRC_ROWID,
r.ITEM,
TO_CHAR(r.STORE) AS ORG_NUM,
r.START_DATE AS DAY_DT,
r.PRICE_CHANGE_TRAN_TYPE,
r.PRICE AS STANDARD_UNIT_RTL_AMT_LCL,
r.PRICE AS SELLING_UNIT_RTL_AMT_LCL,
r.BASE_COST AS BASE_COST_AMT_LCL,
'EUR' AS LOC_CURR_CODE,
r.DOC_CURR_CODE
FROM PRICE_HISTORY_RAW r;
-- Index on the row-identity tuple makes duplicate_key cheap.
CREATE INDEX IX_DMF_SOURCE__PRICE_HISTORY_RID ON DMF_SOURCE__PRICE_HISTORY (ITEM, ORG_NUM, DAY_DT);
-- Reset parallel attribute so per-rule reads don't auto-parallelize.
ALTER TABLE DMF_SOURCE__PRICE_HISTORY NOPARALLEL;
-- Sanity check:
SELECT COUNT(*) FROM DMF_SOURCE__PRICE_HISTORY;
-- ============================================================================
-- STEP 1c — Materialize duplicate-key set into DMF_DUPKEYS__PRICE_HISTORY
-- ============================================================================
-- 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_HISTORY PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_DUPKEYS__PRICE_HISTORY AS
SELECT ITEM, ORG_NUM, DAY_DT FROM DMF_SOURCE__PRICE_HISTORY
GROUP BY ITEM, ORG_NUM, DAY_DT
HAVING COUNT(*) > 1;
CREATE INDEX IX_DMF_DUPKEYS__PRICE_HISTOR_KEY ON DMF_DUPKEYS__PRICE_HISTORY (ITEM, ORG_NUM, DAY_DT);
-- Sanity check (size of the conflict set):
SELECT COUNT(*) FROM DMF_DUPKEYS__PRICE_HISTORY;
-- ############################################################################
-- PHASE 1 — RAW → REJECTED (rule evaluation, no CTRL touched)
-- ############################################################################
-- ============================================================================
-- STEP 2 — Ensure PRICE_HISTORY_REJECTED exists, then empty it
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE PRICE_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), PRICE_CHANGE_TRAN_TYPE VARCHAR2(4000), STANDARD_UNIT_RTL_AMT_LCL VARCHAR2(4000), SELLING_UNIT_RTL_AMT_LCL VARCHAR2(4000), BASE_COST_AMT_LCL VARCHAR2(4000), LOC_CURR_CODE VARCHAR2(4000), DOC_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 PRICE_HISTORY_REJECTED;
-- If TRUNCATE fails with ORA-00054 (orphan lock), use DELETE instead:
-- DELETE FROM PRICE_HISTORY_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__PRICE_HISTORY 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 PRICE_HISTORY_REJECTED (
LOG_LVL, ERROR_MSG, VALIDATION_RUN_ID, RULE_ID, SRC_ROWID, ITEM, ORG_NUM, DAY_DT, PRICE_CHANGE_TRAN_TYPE, STANDARD_UNIT_RTL_AMT_LCL, SELLING_UNIT_RTL_AMT_LCL, BASE_COST_AMT_LCL, LOC_CURR_CODE, DOC_CURR_CODE, INSERTED_AT
)
-- RULE 1/7: 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.PRICE_CHANGE_TRAN_TYPE, s.STANDARD_UNIT_RTL_AMT_LCL, s.SELLING_UNIT_RTL_AMT_LCL, s.BASE_COST_AMT_LCL, s.LOC_CURR_CODE, s.DOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__PRICE_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.PRICE_CHANGE_TRAN_TYPE, s.STANDARD_UNIT_RTL_AMT_LCL, s.SELLING_UNIT_RTL_AMT_LCL, s.BASE_COST_AMT_LCL, s.LOC_CURR_CODE, s.DOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__PRICE_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.PRICE_CHANGE_TRAN_TYPE, s.STANDARD_UNIT_RTL_AMT_LCL, s.SELLING_UNIT_RTL_AMT_LCL, s.BASE_COST_AMT_LCL, s.LOC_CURR_CODE, s.DOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__PRICE_HISTORY s WHERE DAY_DT 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.DAY_DT, s.PRICE_CHANGE_TRAN_TYPE, s.STANDARD_UNIT_RTL_AMT_LCL, s.SELLING_UNIT_RTL_AMT_LCL, s.BASE_COST_AMT_LCL, s.LOC_CURR_CODE, s.DOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__PRICE_HISTORY s WHERE PRICE_CHANGE_TRAN_TYPE IS NULL
UNION ALL
-- RULE 2/7: ERROR_INVALID_PRICE_CHANGE_TRAN_TYPE (type=allowed_values, severity=E)
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.DAY_DT, s.PRICE_CHANGE_TRAN_TYPE, s.STANDARD_UNIT_RTL_AMT_LCL, s.SELLING_UNIT_RTL_AMT_LCL, s.BASE_COST_AMT_LCL, s.LOC_CURR_CODE, s.DOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__PRICE_HISTORY s WHERE PRICE_CHANGE_TRAN_TYPE NOT IN ('0', '2', '4', '8', '9', '10', '11')
UNION ALL
-- RULE 3/7: ERROR_MISSING_ITEM (type=dimension_exists, severity=E)
-- Was a direct NOT EXISTS against ITEM_MASTER_CTRL; now uses the materialized
-- item_valid dimension, so xref-reachable items are accepted too.
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.PRICE_CHANGE_TRAN_TYPE, s.STANDARD_UNIT_RTL_AMT_LCL, s.SELLING_UNIT_RTL_AMT_LCL, s.BASE_COST_AMT_LCL, s.LOC_CURR_CODE, s.DOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__PRICE_HISTORY 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 4/7: 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.PRICE_CHANGE_TRAN_TYPE, s.STANDARD_UNIT_RTL_AMT_LCL, s.SELLING_UNIT_RTL_AMT_LCL, s.BASE_COST_AMT_LCL, s.LOC_CURR_CODE, s.DOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__PRICE_HISTORY 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
-- RULE 5/7: WARN_MISSING_ITEM_LOCATION (type=condition, severity=W)
SELECT 'W', 'WARN_MISSING_ITEM_LOCATION', 'MANUAL_RUN', 'WARN_MISSING_ITEM_LOCATION', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.PRICE_CHANGE_TRAN_TYPE, s.STANDARD_UNIT_RTL_AMT_LCL, s.SELLING_UNIT_RTL_AMT_LCL, s.BASE_COST_AMT_LCL, s.LOC_CURR_CODE, s.DOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__PRICE_HISTORY 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
-- RULE 6/7: WARN_ZERO_PRICE (type=condition, severity=W)
SELECT 'W', 'WARN_ZERO_PRICE', 'MANUAL_RUN', 'WARN_ZERO_PRICE', s.SRC_ROWID, s.ITEM, s.ORG_NUM, s.DAY_DT, s.PRICE_CHANGE_TRAN_TYPE, s.STANDARD_UNIT_RTL_AMT_LCL, s.SELLING_UNIT_RTL_AMT_LCL, s.BASE_COST_AMT_LCL, s.LOC_CURR_CODE, s.DOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__PRICE_HISTORY s WHERE SELLING_UNIT_RTL_AMT_LCL = 0
UNION ALL
-- RULE 7/7: 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.PRICE_CHANGE_TRAN_TYPE, s.STANDARD_UNIT_RTL_AMT_LCL, s.SELLING_UNIT_RTL_AMT_LCL, s.BASE_COST_AMT_LCL, s.LOC_CURR_CODE, s.DOC_CURR_CODE, SYSDATE FROM DMF_SOURCE__PRICE_HISTORY s WHERE (ITEM, ORG_NUM, DAY_DT) IN (SELECT ITEM, ORG_NUM, DAY_DT FROM DMF_DUPKEYS__PRICE_HISTORY);
COMMIT;
-- The CTRL anti-join in STEP 5 probes PRICE_HISTORY_REJECTED by SRC_ROWID.
-- Build the index now (cheap here, expensive to miss during the anti-join).
CREATE INDEX IX_PRICE_HISTORY_REJ_ROWID ON PRICE_HISTORY_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 PRICE_HISTORY_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__PRICE_HISTORY)
- (SELECT COUNT(DISTINCT SRC_ROWID) FROM PRICE_HISTORY_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 PRICE_HISTORY_CTRL (drop-and-recreate)
-- ============================================================================
-- Final Oracle export shape. Column projection comes from
-- `[ctrl_projection]` in the override; default = identity from the source.
DROP TABLE PRICE_HISTORY_CTRL PURGE; -- ignore ORA-00942
CREATE TABLE PRICE_HISTORY_CTRL (
ITEM VARCHAR2(4000),
ORG_NUM VARCHAR2(4000),
DAY_DT VARCHAR2(4000),
PRICE_CHANGE_TRAN_TYPE VARCHAR2(4000),
MULTI_SELLING_UOM VARCHAR2(4000),
SELLING_UOM VARCHAR2(4000),
MULTI_UNIT_QTY VARCHAR2(4000),
MULTI_UNIT_RTL_AMT_LCL VARCHAR2(4000),
STANDARD_UNIT_RTL_AMT_LCL VARCHAR2(4000),
SELLING_UNIT_RTL_AMT_LCL VARCHAR2(4000),
BASE_COST_AMT_LCL 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),
DELETE_FLG VARCHAR2(4000),
DATASOURCE_NUM_ID VARCHAR2(4000),
ORIG_SELLING_UNIT_RTL_AMT_LCL VARCHAR2(4000),
LST_REG_RTL_AMT_LCL VARCHAR2(4000),
CREATE_ID VARCHAR2(4000),
CREATE_DATETIME VARCHAR2(4000),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 5 — Populate PRICE_HISTORY_CTRL directly from DMF_SOURCE__PRICE_HISTORY
-- ============================================================================
-- 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.
--
-- NLS WARNING: DAY_DT is DATE in the source and VARCHAR2 in CTRL; the numeric
-- columns are NUMBER in the source and VARCHAR2 in CTRL. The conversions below
-- are implicit and therefore depend on the session's NLS_DATE_FORMAT and
-- NLS_NUMERIC_CHARACTERS. See the note at the foot of this file.
INSERT /*+ APPEND */ INTO PRICE_HISTORY_CTRL (ITEM, ORG_NUM, DAY_DT, PRICE_CHANGE_TRAN_TYPE, MULTI_SELLING_UOM, SELLING_UOM, MULTI_UNIT_QTY, MULTI_UNIT_RTL_AMT_LCL, STANDARD_UNIT_RTL_AMT_LCL, SELLING_UNIT_RTL_AMT_LCL, BASE_COST_AMT_LCL, GLOBAL1_EXCHANGE_RATE, GLOBAL2_EXCHANGE_RATE, GLOBAL3_EXCHANGE_RATE, LOC_CURR_CODE, LOC_EXCHANGE_RATE, DOC_CURR_CODE, ETL_THREAD_VAL, DELETE_FLG, DATASOURCE_NUM_ID, ORIG_SELLING_UNIT_RTL_AMT_LCL, LST_REG_RTL_AMT_LCL, CREATE_ID, CREATE_DATETIME, INSERTED_AT)
SELECT s.ITEM, s.ORG_NUM, s.DAY_DT, s.PRICE_CHANGE_TRAN_TYPE, CAST(NULL AS VARCHAR2(4)), CAST(NULL AS VARCHAR2(4)), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), s.STANDARD_UNIT_RTL_AMT_LCL, s.SELLING_UNIT_RTL_AMT_LCL, s.BASE_COST_AMT_LCL, CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), s.LOC_CURR_CODE, CAST(NULL AS NUMBER), s.DOC_CURR_CODE, CAST(NULL AS NUMBER), CAST(NULL AS CHAR(1)), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), CAST(NULL AS NUMBER), 'DMF', SYSDATE, SYSDATE
FROM DMF_SOURCE__PRICE_HISTORY s
WHERE NOT EXISTS (
SELECT 1
FROM PRICE_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
AND s.PRICE_CHANGE_TRAN_TYPE IS NOT NULL;
COMMIT;
-- Compare against expected_ctrl_rows from the end of PHASE 1:
SELECT COUNT(*) AS ctrl_rows FROM PRICE_HISTORY_CTRL;
-- ############################################################################
-- FINAL — Rejection summary by rule
-- ############################################################################
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM PRICE_HISTORY_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;
-- ============================================================================
-- Replicate PRICE_HISTORY_CTRL from reference org 446 → online stores + 546
-- ============================================================================
-- Targets: STORE where online_store_ind = 'Y', plus 546, minus 446 itself.
-- ORG_NUM is VARCHAR2 and STORE is numeric, so targets use TO_CHAR(STORE).
-- Re-runnable: STEP C clears the targets before STEP D repopulates them.
-- ============================================================================
-- STEP A — Inspect
-- ============================================================================
-- A1. Reference volume.
SELECT COUNT(*) AS ref_rows,
COUNT(DISTINCT ITEM) AS ref_items,
MIN(DAY_DT) AS min_day_dt,
MAX(DAY_DT) AS max_day_dt
FROM PRICE_HISTORY_CTRL
WHERE ORG_NUM = '446';
-- A2. Targets resolved, and what STEP C will delete.
SELECT t.ORG_NUM,
(SELECT COUNT(*) FROM PRICE_HISTORY_CTRL p WHERE p.ORG_NUM = t.ORG_NUM) AS rows_today
FROM (
SELECT TO_CHAR(STORE) AS ORG_NUM FROM STORE_ADD_CTRL WHERE online_store_ind = 'Y'
UNION
SELECT '546' FROM DUAL
MINUS
SELECT '446' FROM DUAL
) t
ORDER BY TO_NUMBER(t.ORG_NUM);
-- A3. Targets that ERROR_INVALID_ORG would reject. Empty = safe to proceed.
SELECT t.ORG_NUM AS target_not_in_validity
FROM (
SELECT TO_CHAR(STORE) AS ORG_NUM FROM STORE_ADD_CTRL WHERE online_store_ind = 'Y'
UNION
SELECT '546' FROM DUAL
MINUS
SELECT '446' FROM DUAL
) t
WHERE NOT EXISTS (SELECT 1 FROM DMF_VALIDITY__ORGANIZAT_A4FC2D d WHERE d.ORG_NUM = t.ORG_NUM);
-- ============================================================================
-- STEP B — Backup (undo source for STEP C/D)
-- ============================================================================
DROP TABLE PRICE_HISTORY_CTRL_BKP_446 PURGE; -- ignore ORA-00942 on first run
CREATE TABLE PRICE_HISTORY_CTRL_BKP_446 AS
SELECT * FROM PRICE_HISTORY_CTRL
WHERE ORG_NUM IN (
SELECT TO_CHAR(STORE) FROM STORE_ADD_CTRL WHERE online_store_ind = 'Y'
UNION
SELECT '546' FROM DUAL
UNION
SELECT '446' FROM DUAL
);
-- ============================================================================
-- STEP C — Clear the target orgs
-- ============================================================================
-- Removes every existing row for the targets. 446 is untouched.
DELETE FROM PRICE_HISTORY_CTRL
WHERE ORG_NUM IN (
SELECT TO_CHAR(STORE) AS ORG_NUM FROM STORE_ADD_CTRL WHERE online_store_ind = 'Y'
UNION
SELECT '546' FROM DUAL
MINUS
SELECT '446' FROM DUAL
);
COMMIT;
-- ============================================================================
-- STEP D — Replicate 446's rows onto every target org
-- ============================================================================
-- ORG_NUM is substituted; INSERTED_AT is stamped now. To mark the rows as
-- newly created, replace ref.CREATE_ID / ref.CREATE_DATETIME with 'DMF' / SYSDATE.
INSERT INTO PRICE_HISTORY_CTRL (
ITEM, ORG_NUM, DAY_DT, PRICE_CHANGE_TRAN_TYPE, MULTI_SELLING_UOM, SELLING_UOM,
MULTI_UNIT_QTY, MULTI_UNIT_RTL_AMT_LCL, STANDARD_UNIT_RTL_AMT_LCL,
SELLING_UNIT_RTL_AMT_LCL, BASE_COST_AMT_LCL, GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE, GLOBAL3_EXCHANGE_RATE, LOC_CURR_CODE, LOC_EXCHANGE_RATE,
DOC_CURR_CODE, ETL_THREAD_VAL, DELETE_FLG, DATASOURCE_NUM_ID,
ORIG_SELLING_UNIT_RTL_AMT_LCL, LST_REG_RTL_AMT_LCL, CREATE_ID, CREATE_DATETIME,
INSERTED_AT
)
SELECT
ref.ITEM,
tgt.ORG_NUM,
ref.DAY_DT,
ref.PRICE_CHANGE_TRAN_TYPE,
ref.MULTI_SELLING_UOM,
ref.SELLING_UOM,
ref.MULTI_UNIT_QTY,
ref.MULTI_UNIT_RTL_AMT_LCL,
ref.STANDARD_UNIT_RTL_AMT_LCL,
ref.SELLING_UNIT_RTL_AMT_LCL,
ref.BASE_COST_AMT_LCL,
ref.GLOBAL1_EXCHANGE_RATE,
ref.GLOBAL2_EXCHANGE_RATE,
ref.GLOBAL3_EXCHANGE_RATE,
ref.LOC_CURR_CODE,
ref.LOC_EXCHANGE_RATE,
ref.DOC_CURR_CODE,
ref.ETL_THREAD_VAL,
ref.DELETE_FLG,
ref.DATASOURCE_NUM_ID,
ref.ORIG_SELLING_UNIT_RTL_AMT_LCL,
ref.LST_REG_RTL_AMT_LCL,
ref.CREATE_ID,
ref.CREATE_DATETIME,
SYSDATE
FROM PRICE_HISTORY_CTRL ref
CROSS JOIN (
SELECT TO_CHAR(STORE) AS ORG_NUM FROM STORE_ADD_CTRL WHERE online_store_ind = 'Y'
UNION
SELECT '546' FROM DUAL
MINUS
SELECT '446' FROM DUAL
) tgt
WHERE ref.ORG_NUM = '446';
COMMIT;
-- ============================================================================
-- STEP E — Verify
-- ============================================================================
-- E1. Every target should now match ref_rows from A1.
SELECT ORG_NUM, COUNT(*) AS rows_now
FROM PRICE_HISTORY_CTRL
WHERE ORG_NUM IN (
SELECT TO_CHAR(STORE) FROM STORE_ADD_CTRL WHERE online_store_ind = 'Y'
UNION
SELECT '546' FROM DUAL
UNION
SELECT '446' FROM DUAL
)
GROUP BY ORG_NUM
ORDER BY TO_NUMBER(ORG_NUM);
-- E2. No duplicate business keys. Empty = clean.
SELECT ITEM, ORG_NUM, DAY_DT, COUNT(*) AS dupes
FROM PRICE_HISTORY_CTRL
GROUP BY ITEM, ORG_NUM, DAY_DT
HAVING COUNT(*) > 1;
-- ============================================================================
-- STEP F — Drop the STEP B backup
-- ============================================================================
-- Only after E1 and E2 are correct. Undo is unavailable once this runs.
DROP TABLE PRICE_HISTORY_CTRL_BKP_446 PURGE;