Source: DMF/PuC/AIF/FelipeBkp/sales_tender.sql
-- ============================================================================
-- SALES TENDER — Full Validation Pipeline
-- ============================================================================
-- Fact: sales_tender
-- Source: ORI_SALES_TENDER_RAW
-- BQ source: `puc-p-dataf-common`.export_oracle_migration.sales_tender_migration
-- Rejected: ORI_SALES_TENDER_REJECTED
-- STG (accepted): ORI_SALES_TENDER_STG
-- Rules: 9
-- 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:
-- ORI_SALES_TENDER_RAW populated
-- ORI_SALES_CTRL populated (cross-check on SLS_TRX_ID + ORG_NUM + DAY_DT)
-- STORE_ADD_CTRL populated (ctrl_status = 'C' rows)
--
-- Collapsed from phase1/phase2/phase3 playbooks generated 2026-06-07T17:56:07.
-- 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 0 — Create and populate XREF_TENDER_TYPE (prerequisite)
-- ============================================================================
-- Maps SAP tender type codes (ORI_SALES_TENDER_RAW.TENDER_TYPE_ID) to Oracle
-- numeric IDs and group labels used in the RAP target spec.
-- If the table already exists, the DROP will fail with ORA-00942 — ignore it
-- and go straight to TRUNCATE + INSERT.
DROP TABLE XREF_TENDER_TYPE PURGE;
CREATE TABLE XREF_TENDER_TYPE (
TENDER_TYPE_ID_SAP VARCHAR2(10) NOT NULL,
TENDER_TYPE_ID_ORACLE NUMBER(10) NOT NULL,
TENDER_TYPE_GROUP_ORACLE VARCHAR2(20) NOT NULL,
CONSTRAINT PK_XREF_TENDER_TYPE PRIMARY KEY (TENDER_TYPE_ID_SAP)
);
INSERT INTO XREF_TENDER_TYPE VALUES ('ALIP',1,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('AMEP',2,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('AMEX',3020,'CCARD');
INSERT INTO XREF_TENDER_TYPE VALUES ('BANC',3,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('BANK',4,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('BONU',10003,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('CAAL',5,'CCARD');
INSERT INTO XREF_TENDER_TYPE VALUES ('CASH',1000,'CASH');
INSERT INTO XREF_TENDER_TYPE VALUES ('CCOD',10007,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('CHEQ',1000,'CASH');
INSERT INTO XREF_TENDER_TYPE VALUES ('COUP',6,'COUPON');
INSERT INTO XREF_TENDER_TYPE VALUES ('CUPY',7,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('DINE',8,'CCARD');
INSERT INTO XREF_TENDER_TYPE VALUES ('DIRE',9,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('DIV.',10,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('EC',11,'CCARD');
INSERT INTO XREF_TENDER_TYPE VALUES ('ECMC',3010,'CCARD');
INSERT INTO XREF_TENDER_TYPE VALUES ('FORC',1010,'CASH');
INSERT INTO XREF_TENDER_TYPE VALUES ('GCGT',12,'VOUCH');
INSERT INTO XREF_TENDER_TYPE VALUES ('GICA',13,'VOUCH');
INSERT INTO XREF_TENDER_TYPE VALUES ('GKAK',14,'VOUCH');
INSERT INTO XREF_TENDER_TYPE VALUES ('GUTS',15,'VOUCH');
INSERT INTO XREF_TENDER_TYPE VALUES ('IDEA',10000,'PAYPAL');
INSERT INTO XREF_TENDER_TYPE VALUES ('INVO',16,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('JCB',17,'CCARD');
INSERT INTO XREF_TENDER_TYPE VALUES ('MAST',3010,'CCARD');
INSERT INTO XREF_TENDER_TYPE VALUES ('MMO',18,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('OGKF',19,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('OTHE',20,'CCARD');
INSERT INTO XREF_TENDER_TYPE VALUES ('PAYP',10000,'PAYPAL');
INSERT INTO XREF_TENDER_TYPE VALUES ('PIN',21,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('PR24',22,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('RUCK',23,'CASH');
INSERT INTO XREF_TENDER_TYPE VALUES ('RUND',24,'CASH');
INSERT INTO XREF_TENDER_TYPE VALUES ('VISA',3000,'CCARD');
INSERT INTO XREF_TENDER_TYPE VALUES ('VPAY',25,'CCARD');
INSERT INTO XREF_TENDER_TYPE VALUES ('WCPY',26,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('AVSG',27,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('BAK1',28,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('BAK2',29,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('CAS2',30,'OTHERS');
INSERT INTO XREF_TENDER_TYPE VALUES ('TWNT',31,'OTHERS');
COMMIT;
SELECT COUNT(*) AS xref_rows FROM XREF_TENDER_TYPE;
-- ============================================================================
-- 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 '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 'tender_type' → DMF_VALIDITY__TENDER_TYPE
DROP TABLE DMF_VALIDITY__TENDER_TYPE PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_VALIDITY__TENDER_TYPE AS
SELECT DISTINCT TO_CHAR(TENDER_TYPE_ID_ORACLE) AS tender_type_id
FROM XREF_TENDER_TYPE
WHERE TENDER_TYPE_ID_ORACLE IS NOT NULL;
CREATE INDEX IX_DMF_VALIDITY__TENDER_TYPE ON DMF_VALIDITY__TENDER_TYPE (TENDER_TYPE_ID);
-- Verify the validity tables populated:
SELECT 'DMF_VALIDITY__ORGANIZATION_CSV' AS dim, COUNT(*) FROM DMF_VALIDITY__ORGANIZATION_CSV;
SELECT 'DMF_VALIDITY__TENDER_TYPE' AS dim, COUNT(*) FROM DMF_VALIDITY__TENDER_TYPE;
-- ============================================================================
-- STEP 2 — Materialize normalized source into DMF_SOURCE__SALES_TENDER
-- ============================================================================
-- 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__SALES_TENDER PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_SOURCE__SALES_TENDER PARALLEL 4 AS
WITH xref_tndr AS (
-- Deduplicating CTE: guarantees at most one Oracle tender type per SAP ID.
-- Uses MIN() aggregation so a plain LEFT JOIN on the main query cannot
-- produce more output rows than there are rows in ORI_SALES_TENDER_RAW.
-- Mirrors the wh_map pattern in deals.toml.
SELECT TENDER_TYPE_ID_SAP,
MIN(TENDER_TYPE_ID_ORACLE) AS TENDER_TYPE_ID_ORACLE,
MIN(TENDER_TYPE_GROUP_ORACLE) AS TENDER_TYPE_GROUP_ORACLE
FROM XREF_TENDER_TYPE
GROUP BY TENDER_TYPE_ID_SAP
)
SELECT /*+ PARALLEL(4) */
r.ROWID AS SRC_ROWID,
r.SLS_TRX_ID,
x.TENDER_TYPE_ID_ORACLE AS TENDER_TYPE_ID,
LTRIM(r.ORG_NUM, '0') AS ORG_NUM,
r.DAY_DT,
r.REVISION_NUM,
x.TENDER_TYPE_GROUP_ORACLE AS TENDER_TYPE_GROUP,
'-1' AS CASHIER_ID,
NVL(LTRIM(r.REGISTER_ID, '0'), '0') AS REGISTER_ID,
r.VOUCHER_NUM,
r.VOUCHER_AGE,
r.COUPON_NUM,
r.COUPON_REF_NUM,
r.TNDR_SLS_AMT_LCL,
r.TNDR_RET_AMT_LCL,
r.EXCHANGE_DT,
r.AUX1_CHANGED_ON_DT,
r.AUX2_CHANGED_ON_DT,
r.AUX3_CHANGED_ON_DT,
r.AUX4_CHANGED_ON_DT,
r.CHANGED_BY_ID,
r.CHANGED_ON_DT,
r.CREATED_BY_ID,
r.CREATED_ON_DT,
1 AS DATASOURCE_NUM_ID,
r.DELETE_FLG,
r.DOC_CURR_CODE,
r.ETL_THREAD_VAL,
r.GLOBAL1_EXCHANGE_RATE,
r.GLOBAL2_EXCHANGE_RATE,
r.GLOBAL3_EXCHANGE_RATE,
r.SLS_TRX_ID || '~'
|| NVL(TO_CHAR(x.TENDER_TYPE_ID_ORACLE), 'NOXREF') || '~'
|| LTRIM(r.ORG_NUM, '0') || '~'
|| TO_CHAR(r.DAY_DT, 'YYYYMMDD') AS INTEGRATION_ID,
'EUR' AS LOC_CURR_CODE,
r.LOC_EXCHANGE_RATE,
r.TENANT_ID,
r.X_CUSTOM,
r.FLEX1_CHAR_VALUE,
r.FLEX2_CHAR_VALUE,
r.FLEX3_CHAR_VALUE,
r.FLEX4_CHAR_VALUE,
CAST(NULL AS DATE) AS UPDATED_AT
FROM ORI_SALES_TENDER_RAW r
LEFT JOIN xref_tndr x ON x.TENDER_TYPE_ID_SAP = r.TENDER_TYPE_ID;
-- Index on the row-identity tuple makes duplicate_key and the STG
-- anti-join cheap.
CREATE INDEX IX_DMF_SOURCE__SALES_TENDER_RID ON DMF_SOURCE__SALES_TENDER (SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT);
-- Reset parallel attribute so per-rule reads don't auto-parallelize.
ALTER TABLE DMF_SOURCE__SALES_TENDER NOPARALLEL;
-- Sanity check:
SELECT COUNT(*) FROM DMF_SOURCE__SALES_TENDER;
-- ============================================================================
-- STEP 3 — Materialize duplicate-key set into DMF_DUPKEYS__SALES_TENDER
-- ============================================================================
-- 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__SALES_TENDER PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_DUPKEYS__SALES_TENDER AS
SELECT SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT FROM DMF_SOURCE__SALES_TENDER
GROUP BY SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT
HAVING COUNT(*) > 1;
CREATE INDEX IX_DMF_DUPKEYS__SALES_TENDER_KEY ON DMF_DUPKEYS__SALES_TENDER (SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT);
-- Sanity check (size of the conflict set):
SELECT COUNT(*) FROM DMF_DUPKEYS__SALES_TENDER;
-- ============================================================================
-- STEP 4 — Ensure ORI_SALES_TENDER_REJECTED exists, then empty it
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ORI_SALES_TENDER_REJECTED ( LOG_LVL VARCHAR2(1), ERROR_MSG VARCHAR2(255), VALIDATION_RUN_ID VARCHAR2(80), RULE_ID VARCHAR2(120), SRC_ROWID VARCHAR2(64), SLS_TRX_ID VARCHAR2(4000), TENDER_TYPE_ID VARCHAR2(4000), ORG_NUM VARCHAR2(4000), DAY_DT VARCHAR2(4000), REVISION_NUM VARCHAR2(4000), TENDER_TYPE_GROUP VARCHAR2(4000), CASHIER_ID VARCHAR2(4000), REGISTER_ID VARCHAR2(4000), VOUCHER_NUM VARCHAR2(4000), VOUCHER_AGE VARCHAR2(4000), COUPON_NUM VARCHAR2(4000), COUPON_REF_NUM VARCHAR2(4000), TNDR_SLS_AMT_LCL VARCHAR2(4000), TNDR_RET_AMT_LCL VARCHAR2(4000), EXCHANGE_DT VARCHAR2(4000), AUX1_CHANGED_ON_DT VARCHAR2(4000), AUX2_CHANGED_ON_DT VARCHAR2(4000), AUX3_CHANGED_ON_DT VARCHAR2(4000), AUX4_CHANGED_ON_DT VARCHAR2(4000), CHANGED_BY_ID VARCHAR2(4000), CHANGED_ON_DT VARCHAR2(4000), CREATED_BY_ID VARCHAR2(4000), CREATED_ON_DT VARCHAR2(4000), DATASOURCE_NUM_ID VARCHAR2(4000), DELETE_FLG VARCHAR2(4000), DOC_CURR_CODE VARCHAR2(4000), ETL_THREAD_VAL VARCHAR2(4000), GLOBAL1_EXCHANGE_RATE VARCHAR2(4000), GLOBAL2_EXCHANGE_RATE VARCHAR2(4000), GLOBAL3_EXCHANGE_RATE VARCHAR2(4000), INTEGRATION_ID VARCHAR2(4000), LOC_CURR_CODE VARCHAR2(4000), LOC_EXCHANGE_RATE VARCHAR2(4000), TENANT_ID VARCHAR2(4000), X_CUSTOM VARCHAR2(4000), FLEX1_CHAR_VALUE VARCHAR2(4000), FLEX2_CHAR_VALUE VARCHAR2(4000), FLEX3_CHAR_VALUE VARCHAR2(4000), FLEX4_CHAR_VALUE 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_SALES_TENDER_REJECTED;
-- If TRUNCATE fails with ORA-00054 (orphan lock), use DELETE instead:
-- DELETE FROM ORI_SALES_TENDER_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 ORI_SALES_TENDER_REJECTED (
LOG_LVL, ERROR_MSG, VALIDATION_RUN_ID, RULE_ID, SRC_ROWID, SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT, REVISION_NUM, TENDER_TYPE_GROUP, CASHIER_ID, REGISTER_ID, VOUCHER_NUM, VOUCHER_AGE, COUPON_NUM, COUPON_REF_NUM, TNDR_SLS_AMT_LCL, TNDR_RET_AMT_LCL, EXCHANGE_DT, AUX1_CHANGED_ON_DT, AUX2_CHANGED_ON_DT, AUX3_CHANGED_ON_DT, AUX4_CHANGED_ON_DT, CHANGED_BY_ID, CHANGED_ON_DT, CREATED_BY_ID, CREATED_ON_DT, DATASOURCE_NUM_ID, DELETE_FLG, DOC_CURR_CODE, ETL_THREAD_VAL, GLOBAL1_EXCHANGE_RATE, GLOBAL2_EXCHANGE_RATE, GLOBAL3_EXCHANGE_RATE, INTEGRATION_ID, LOC_CURR_CODE, LOC_EXCHANGE_RATE, TENANT_ID, X_CUSTOM, FLEX1_CHAR_VALUE, FLEX2_CHAR_VALUE, FLEX3_CHAR_VALUE, FLEX4_CHAR_VALUE, UPDATED_AT, INSERTED_AT
)
WITH s AS (
SELECT
SRC_ROWID,
SLS_TRX_ID,
TENDER_TYPE_ID,
ORG_NUM,
DAY_DT,
REVISION_NUM,
TENDER_TYPE_GROUP,
CASHIER_ID,
REGISTER_ID,
VOUCHER_NUM,
VOUCHER_AGE,
COUPON_NUM,
COUPON_REF_NUM,
TNDR_SLS_AMT_LCL,
TNDR_RET_AMT_LCL,
EXCHANGE_DT,
AUX1_CHANGED_ON_DT,
AUX2_CHANGED_ON_DT,
AUX3_CHANGED_ON_DT,
AUX4_CHANGED_ON_DT,
CHANGED_BY_ID,
CHANGED_ON_DT,
CREATED_BY_ID,
CREATED_ON_DT,
DATASOURCE_NUM_ID,
DELETE_FLG,
DOC_CURR_CODE,
ETL_THREAD_VAL,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
INTEGRATION_ID,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
TENANT_ID,
X_CUSTOM,
FLEX1_CHAR_VALUE,
FLEX2_CHAR_VALUE,
FLEX3_CHAR_VALUE,
FLEX4_CHAR_VALUE,
UPDATED_AT
FROM DMF_SOURCE__SALES_TENDER
)
SELECT 'E', 'SLS_TRX_ID IS NULL', 'MANUAL_RUN', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, SYSDATE FROM s WHERE SLS_TRX_ID IS NULL
UNION ALL
SELECT 'E', 'ORG_NUM IS NULL', 'MANUAL_RUN', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, SYSDATE FROM s WHERE ORG_NUM IS NULL
UNION ALL
SELECT 'E', 'DAY_DT IS NULL', 'MANUAL_RUN', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, SYSDATE FROM s WHERE DAY_DT IS NULL
UNION ALL
SELECT 'E', 'REVISION_NUM IS NULL', 'MANUAL_RUN', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, SYSDATE FROM s WHERE REVISION_NUM IS NULL
UNION ALL
SELECT 'E', 'ERROR_MISSING_TENDER_TYPE_XREF', 'MANUAL_RUN', 'ERROR_MISSING_TENDER_TYPE_XREF', s.SRC_ROWID, s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, SYSDATE FROM s WHERE TENDER_TYPE_ID IS NULL
UNION ALL
SELECT 'E', 'TENDER_TYPE_ID NOT FOUND: XREF_TENDER_TYPE', 'MANUAL_RUN', 'ERROR_MISSING_TENDER_TYPE', s.SRC_ROWID, s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, SYSDATE FROM s WHERE TENDER_TYPE_ID IS NOT NULL AND NOT EXISTS (SELECT 1 FROM DMF_VALIDITY__TENDER_TYPE d WHERE d.TENDER_TYPE_ID = s.TENDER_TYPE_ID)
UNION ALL
SELECT 'E', 'ORG_NUM NOT FOUND: STORE_ADD_CTRL', 'MANUAL_RUN', 'ERROR_NO_VALID_STORE', s.SRC_ROWID, s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, 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', 'ERROR_TNDR_AMT_BOTH_FILLED', 'MANUAL_RUN', 'ERROR_TNDR_AMT_BOTH_FILLED', s.SRC_ROWID, s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, SYSDATE FROM s WHERE NVL(TNDR_SLS_AMT_LCL, 0) <> 0 AND NVL(TNDR_RET_AMT_LCL, 0) <> 0
UNION ALL
SELECT 'E', 'ERROR_TNDR_AMT_NOT_POSITIVE', 'MANUAL_RUN', 'ERROR_TNDR_AMT_NOT_POSITIVE', s.SRC_ROWID, s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, SYSDATE FROM s WHERE NVL(TNDR_SLS_AMT_LCL, 0) <= 0 AND NVL(TNDR_RET_AMT_LCL, 0) <= 0
UNION ALL
SELECT 'E', 'ERROR_NO_ORI_SALES_CTRL', 'MANUAL_RUN', 'ERROR_NO_ORI_SALES_CTRL', s.SRC_ROWID, s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, SYSDATE FROM s WHERE (NVL(TNDR_SLS_AMT_LCL, 0) <> 0 AND NVL(TNDR_RET_AMT_LCL, 0) = 0 OR NVL(TNDR_RET_AMT_LCL, 0) <> 0 AND NVL(TNDR_SLS_AMT_LCL, 0) = 0) AND NOT EXISTS (SELECT 1 FROM ORI_SALES_CTRL c WHERE c.SLS_TRX_ID = s.SLS_TRX_ID AND c.ORG_NUM = s.ORG_NUM AND c.DAY_DT = s.DAY_DT)
UNION ALL
SELECT 'E', 'ERROR_TRAN_TYPE_MISMATCH', 'MANUAL_RUN', 'ERROR_TRAN_TYPE_MISMATCH', s.SRC_ROWID, s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, SYSDATE FROM s WHERE (NVL(TNDR_SLS_AMT_LCL, 0) <> 0 AND NVL(TNDR_RET_AMT_LCL, 0) = 0 OR NVL(TNDR_RET_AMT_LCL, 0) <> 0 AND NVL(TNDR_SLS_AMT_LCL, 0) = 0) AND EXISTS (SELECT 1 FROM ORI_SALES_CTRL c WHERE c.SLS_TRX_ID = s.SLS_TRX_ID AND c.ORG_NUM = s.ORG_NUM AND c.DAY_DT = s.DAY_DT AND UPPER(TRIM(c.TRAN_TYPE)) <> CASE WHEN NVL(TNDR_SLS_AMT_LCL, 0) <> 0 AND NVL(TNDR_RET_AMT_LCL, 0) = 0 THEN 'SALE' WHEN NVL(TNDR_RET_AMT_LCL, 0) <> 0 AND NVL(TNDR_SLS_AMT_LCL, 0) = 0 THEN 'RETURN' END)
UNION ALL
SELECT 'E', 'DUPLICATE KEY: SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT', 'MANUAL_RUN', 'ERR_DUP_KEY_CONFLICT', s.SRC_ROWID, s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, SYSDATE FROM s WHERE (SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT) IN (SELECT SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT FROM DMF_DUPKEYS__SALES_TENDER);
COMMIT;
-- ============================================================================
-- STEP 6 — Build typed ORI_SALES_TENDER_STG (drop-and-recreate)
-- ============================================================================
-- DDL types are pulled from the RAP spec (not VARCHAR2(4000) blanket).
DROP TABLE ORI_SALES_TENDER_STG PURGE; -- ignore ORA-00942
CREATE TABLE ORI_SALES_TENDER_STG (
SLS_TRX_ID VARCHAR2(4000),
TENDER_TYPE_ID VARCHAR2(4000),
ORG_NUM VARCHAR2(4000),
DAY_DT VARCHAR2(4000),
REVISION_NUM VARCHAR2(4000),
TENDER_TYPE_GROUP VARCHAR2(4000),
CASHIER_ID VARCHAR2(4000),
REGISTER_ID VARCHAR2(4000),
VOUCHER_NUM VARCHAR2(4000),
VOUCHER_AGE VARCHAR2(4000),
COUPON_NUM VARCHAR2(4000),
COUPON_REF_NUM VARCHAR2(4000),
TNDR_SLS_AMT_LCL VARCHAR2(4000),
TNDR_RET_AMT_LCL VARCHAR2(4000),
EXCHANGE_DT VARCHAR2(4000),
AUX1_CHANGED_ON_DT VARCHAR2(4000),
AUX2_CHANGED_ON_DT VARCHAR2(4000),
AUX3_CHANGED_ON_DT VARCHAR2(4000),
AUX4_CHANGED_ON_DT VARCHAR2(4000),
CHANGED_BY_ID VARCHAR2(4000),
CHANGED_ON_DT VARCHAR2(4000),
CREATED_BY_ID VARCHAR2(4000),
CREATED_ON_DT VARCHAR2(4000),
DATASOURCE_NUM_ID VARCHAR2(4000),
DELETE_FLG VARCHAR2(4000),
DOC_CURR_CODE VARCHAR2(4000),
ETL_THREAD_VAL VARCHAR2(4000),
GLOBAL1_EXCHANGE_RATE VARCHAR2(4000),
GLOBAL2_EXCHANGE_RATE VARCHAR2(4000),
GLOBAL3_EXCHANGE_RATE VARCHAR2(4000),
INTEGRATION_ID VARCHAR2(4000),
LOC_CURR_CODE VARCHAR2(4000),
LOC_EXCHANGE_RATE VARCHAR2(4000),
TENANT_ID VARCHAR2(4000),
X_CUSTOM VARCHAR2(4000),
FLEX1_CHAR_VALUE VARCHAR2(4000),
FLEX2_CHAR_VALUE VARCHAR2(4000),
FLEX3_CHAR_VALUE VARCHAR2(4000),
FLEX4_CHAR_VALUE VARCHAR2(4000),
UPDATED_AT VARCHAR2(4000),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 7 — Populate ORI_SALES_TENDER_STG (anti-join on SRC_ROWID; LOG_LVL='W' rows still flow through)
-- ============================================================================
INSERT INTO ORI_SALES_TENDER_STG (SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT, REVISION_NUM, TENDER_TYPE_GROUP, CASHIER_ID, REGISTER_ID, VOUCHER_NUM, VOUCHER_AGE, COUPON_NUM, COUPON_REF_NUM, TNDR_SLS_AMT_LCL, TNDR_RET_AMT_LCL, EXCHANGE_DT, AUX1_CHANGED_ON_DT, AUX2_CHANGED_ON_DT, AUX3_CHANGED_ON_DT, AUX4_CHANGED_ON_DT, CHANGED_BY_ID, CHANGED_ON_DT, CREATED_BY_ID, CREATED_ON_DT, DATASOURCE_NUM_ID, DELETE_FLG, DOC_CURR_CODE, ETL_THREAD_VAL, GLOBAL1_EXCHANGE_RATE, GLOBAL2_EXCHANGE_RATE, GLOBAL3_EXCHANGE_RATE, INTEGRATION_ID, LOC_CURR_CODE, LOC_EXCHANGE_RATE, TENANT_ID, X_CUSTOM, FLEX1_CHAR_VALUE, FLEX2_CHAR_VALUE, FLEX3_CHAR_VALUE, FLEX4_CHAR_VALUE, UPDATED_AT, INSERTED_AT)
WITH s AS (
SELECT
SRC_ROWID,
SLS_TRX_ID,
TENDER_TYPE_ID,
ORG_NUM,
DAY_DT,
REVISION_NUM,
TENDER_TYPE_GROUP,
CASHIER_ID,
REGISTER_ID,
VOUCHER_NUM,
VOUCHER_AGE,
COUPON_NUM,
COUPON_REF_NUM,
TNDR_SLS_AMT_LCL,
TNDR_RET_AMT_LCL,
EXCHANGE_DT,
AUX1_CHANGED_ON_DT,
AUX2_CHANGED_ON_DT,
AUX3_CHANGED_ON_DT,
AUX4_CHANGED_ON_DT,
CHANGED_BY_ID,
CHANGED_ON_DT,
CREATED_BY_ID,
CREATED_ON_DT,
DATASOURCE_NUM_ID,
DELETE_FLG,
DOC_CURR_CODE,
ETL_THREAD_VAL,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
INTEGRATION_ID,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
TENANT_ID,
X_CUSTOM,
FLEX1_CHAR_VALUE,
FLEX2_CHAR_VALUE,
FLEX3_CHAR_VALUE,
FLEX4_CHAR_VALUE,
UPDATED_AT
FROM DMF_SOURCE__SALES_TENDER
)
SELECT s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, SYSDATE
FROM s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_SALES_TENDER_REJECTED r
WHERE r.VALIDATION_RUN_ID = 'MANUAL_RUN'
AND r.LOG_LVL = 'E'
AND r.SRC_ROWID = s.SRC_ROWID
)
AND s.SLS_TRX_ID IS NOT NULL
AND s.ORG_NUM IS NOT NULL
AND s.DAY_DT IS NOT NULL
AND s.REVISION_NUM IS NOT NULL;
COMMIT;
-- Row count check: should be COUNT(ORI_SALES_TENDER_RAW) minus distinct SRC_ROWIDs in ORI_SALES_TENDER_REJECTED where LOG_LVL='E'.
SELECT COUNT(*) AS stg_rows FROM ORI_SALES_TENDER_STG;
-- ============================================================================
-- STEP 8 — Build ORI_SALES_TENDER_CTRL (drop-and-recreate)
-- ============================================================================
-- Final Oracle export shape. Column projection comes from
-- `[ctrl_projection]` in the override; default = identity from STG.
DROP TABLE ORI_SALES_TENDER_CTRL PURGE; -- ignore ORA-00942
CREATE TABLE ORI_SALES_TENDER_CTRL (
SLS_TRX_ID VARCHAR2(4000),
TENDER_TYPE_ID VARCHAR2(4000),
ORG_NUM VARCHAR2(4000),
DAY_DT VARCHAR2(4000),
REVISION_NUM VARCHAR2(4000),
TENDER_TYPE_GROUP VARCHAR2(4000),
CASHIER_ID VARCHAR2(4000),
REGISTER_ID VARCHAR2(4000),
VOUCHER_NUM VARCHAR2(4000),
VOUCHER_AGE VARCHAR2(4000),
COUPON_NUM VARCHAR2(4000),
COUPON_REF_NUM VARCHAR2(4000),
TNDR_SLS_AMT_LCL VARCHAR2(4000),
TNDR_RET_AMT_LCL VARCHAR2(4000),
EXCHANGE_DT VARCHAR2(4000),
AUX1_CHANGED_ON_DT VARCHAR2(4000),
AUX2_CHANGED_ON_DT VARCHAR2(4000),
AUX3_CHANGED_ON_DT VARCHAR2(4000),
AUX4_CHANGED_ON_DT VARCHAR2(4000),
CHANGED_BY_ID VARCHAR2(4000),
CHANGED_ON_DT VARCHAR2(4000),
CREATED_BY_ID VARCHAR2(4000),
CREATED_ON_DT VARCHAR2(4000),
DATASOURCE_NUM_ID VARCHAR2(4000),
DELETE_FLG VARCHAR2(4000),
DOC_CURR_CODE VARCHAR2(4000),
ETL_THREAD_VAL VARCHAR2(4000),
GLOBAL1_EXCHANGE_RATE VARCHAR2(4000),
GLOBAL2_EXCHANGE_RATE VARCHAR2(4000),
GLOBAL3_EXCHANGE_RATE VARCHAR2(4000),
INTEGRATION_ID VARCHAR2(4000),
LOC_CURR_CODE VARCHAR2(4000),
LOC_EXCHANGE_RATE VARCHAR2(4000),
TENANT_ID VARCHAR2(4000),
X_CUSTOM VARCHAR2(4000),
FLEX1_CHAR_VALUE VARCHAR2(4000),
FLEX2_CHAR_VALUE VARCHAR2(4000),
FLEX3_CHAR_VALUE VARCHAR2(4000),
FLEX4_CHAR_VALUE VARCHAR2(4000),
UPDATED_AT VARCHAR2(4000),
CREATE_ID VARCHAR2(4000),
CREATE_DATETIME VARCHAR2(4000),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 9 — Populate ORI_SALES_TENDER_CTRL from ORI_SALES_TENDER_STG
-- ============================================================================
INSERT INTO ORI_SALES_TENDER_CTRL (SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT, REVISION_NUM, TENDER_TYPE_GROUP, CASHIER_ID, REGISTER_ID, VOUCHER_NUM, VOUCHER_AGE, COUPON_NUM, COUPON_REF_NUM, TNDR_SLS_AMT_LCL, TNDR_RET_AMT_LCL, EXCHANGE_DT, AUX1_CHANGED_ON_DT, AUX2_CHANGED_ON_DT, AUX3_CHANGED_ON_DT, AUX4_CHANGED_ON_DT, CHANGED_BY_ID, CHANGED_ON_DT, CREATED_BY_ID, CREATED_ON_DT, DATASOURCE_NUM_ID, DELETE_FLG, DOC_CURR_CODE, ETL_THREAD_VAL, GLOBAL1_EXCHANGE_RATE, GLOBAL2_EXCHANGE_RATE, GLOBAL3_EXCHANGE_RATE, INTEGRATION_ID, LOC_CURR_CODE, LOC_EXCHANGE_RATE, TENANT_ID, X_CUSTOM, FLEX1_CHAR_VALUE, FLEX2_CHAR_VALUE, FLEX3_CHAR_VALUE, FLEX4_CHAR_VALUE, UPDATED_AT, CREATE_ID, CREATE_DATETIME, INSERTED_AT)
SELECT s.SLS_TRX_ID, s.TENDER_TYPE_ID, s.ORG_NUM, s.DAY_DT, s.REVISION_NUM, s.TENDER_TYPE_GROUP, s.CASHIER_ID, s.REGISTER_ID, s.VOUCHER_NUM, s.VOUCHER_AGE, s.COUPON_NUM, s.COUPON_REF_NUM, s.TNDR_SLS_AMT_LCL, s.TNDR_RET_AMT_LCL, s.EXCHANGE_DT, s.AUX1_CHANGED_ON_DT, s.AUX2_CHANGED_ON_DT, s.AUX3_CHANGED_ON_DT, s.AUX4_CHANGED_ON_DT, s.CHANGED_BY_ID, s.CHANGED_ON_DT, s.CREATED_BY_ID, s.CREATED_ON_DT, s.DATASOURCE_NUM_ID, s.DELETE_FLG, s.DOC_CURR_CODE, s.ETL_THREAD_VAL, s.GLOBAL1_EXCHANGE_RATE, s.GLOBAL2_EXCHANGE_RATE, s.GLOBAL3_EXCHANGE_RATE, s.INTEGRATION_ID, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.TENANT_ID, s.X_CUSTOM, s.FLEX1_CHAR_VALUE, s.FLEX2_CHAR_VALUE, s.FLEX3_CHAR_VALUE, s.FLEX4_CHAR_VALUE, s.UPDATED_AT, 'DMF', SYSDATE, SYSDATE
FROM ORI_SALES_TENDER_STG s;
COMMIT;
SELECT COUNT(*) AS ctrl_rows FROM ORI_SALES_TENDER_CTRL;
-- ============================================================================
-- STEP 10 — Rejection summary by rule
-- ============================================================================
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM ORI_SALES_TENDER_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;