Source: DMF/PuC/AIF/FelipeBkp/sales_delta.sql
-- ============================================================================
-- SALES DELTA — Full Validation Pipeline
-- ============================================================================
-- Fact: sales_delta
-- Source: ORI_SALES_DELTA_RAW
-- Rejected: ORI_SALES_DELTA_REJECTED
-- STG (accepted): ORI_SALES_DELTA_STG
-- Rules: 67
-- 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-11T11:33:16.
-- 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);
-- 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 2 — Materialize normalized source into DMF_SOURCE__SALES_DELTA
-- ============================================================================
-- 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__SALES_DELTA PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_SOURCE__SALES_DELTA PARALLEL 4 AS
SELECT /*+ PARALLEL(4) */
ROWID AS SRC_ROWID,
UPDATED_AT AS UPDATED_AT,
ITEM AS ITEM,
LTRIM(ORG_NUM, '0') AS ORG_NUM,
DAY_DT AS DAY_DT,
SUBSTR(MIN_NUM,1,2) || SUBSTR(MIN_NUM,4,2) AS MIN_NUM,
IT_SEQ_NUM AS IT_SEQ_NUM,
RTL_TYPE_CODE AS RTL_TYPE_CODE,
SLS_TRX_ID AS SLS_TRX_ID,
VOUCHER_ID AS VOUCHER_ID,
PROMO_ID AS PROMO_ID,
PROMO_COMP_ID AS PROMO_COMP_ID,
ORIG_SLS_TRX_ID AS ORIG_SLS_TRX_ID,
ORIG_ORG_NUM AS ORIG_ORG_NUM,
CASHIER_ID AS CASHIER_ID,
REGISTER_ID AS REGISTER_ID,
SALES_PERSON_ID AS SALES_PERSON_ID,
EMPLOYEE_NUM AS EMPLOYEE_NUM,
CO_HEAD_ID AS CO_HEAD_ID,
CO_LINE_ID AS CO_LINE_ID,
CO_CREATE_DT AS CO_CREATE_DT,
CUSTOMER_NUM AS CUSTOMER_NUM,
CUSTOMER_TYPE AS CUSTOMER_TYPE,
SLS_QTY AS SLS_QTY,
SLS_AMT_LCL AS SLS_AMT_LCL,
SLS_PROFIT_AMT_LCL AS SLS_PROFIT_AMT_LCL,
SLS_EMP_DISC_AMT_LCL AS SLS_EMP_DISC_AMT_LCL,
SLS_MANUAL_MKDN_AMT_LCL AS SLS_MANUAL_MKDN_AMT_LCL,
SLS_MANUAL_MKUP_AMT_LCL AS SLS_MANUAL_MKUP_AMT_LCL,
SLS_TAX_AMT_LCL AS SLS_TAX_AMT_LCL,
SLSPR_DISC_AMT_LCL AS SLSPR_DISC_AMT_LCL,
RET_QTY AS RET_QTY,
RET_AMT_LCL AS RET_AMT_LCL,
RET_PROFIT_AMT_LCL AS RET_PROFIT_AMT_LCL,
RET_EMP_DISC_AMT_LCL AS RET_EMP_DISC_AMT_LCL,
RET_MANUAL_MKDN_AMT_LCL AS RET_MANUAL_MKDN_AMT_LCL,
RET_MANUAL_MKUP_AMT_LCL AS RET_MANUAL_MKUP_AMT_LCL,
RET_TAX_AMT_LCL AS RET_TAX_AMT_LCL,
RETPR_DISC_AMT_LCL AS RETPR_DISC_AMT_LCL,
SALES_TYPE AS SALES_TYPE,
REVISION_NUM AS REVISION_NUM,
RETURN_REASON_CODE AS RETURN_REASON_CODE,
RETURN_WH AS RETURN_WH,
POS_TRX_ID AS POS_TRX_ID,
RECEPT_IND AS RECEPT_IND,
TAXABLE_IND AS TAXABLE_IND,
TRAN_PROCESS_SYS AS TRAN_PROCESS_SYS,
TRAN_TYPE AS TRAN_TYPE,
LOC_CURR_CODE AS LOC_CURR_CODE,
LOC_EXCHANGE_RATE AS LOC_EXCHANGE_RATE,
DOC_CURR_CODE AS DOC_CURR_CODE,
FLEX1_CHAR_VALUE AS FLEX1_CHAR_VALUE,
FLEX7_CHAR_VALUE AS FLEX7_CHAR_VALUE,
FLEX8_CHAR_VALUE AS FLEX8_CHAR_VALUE,
FLEX9_CHAR_VALUE AS FLEX9_CHAR_VALUE,
FLEX10_CHAR_VALUE AS FLEX10_CHAR_VALUE,
FLEX16_CHAR_VALUE AS FLEX16_CHAR_VALUE,
FLEX17_CHAR_VALUE AS FLEX17_CHAR_VALUE
FROM ORI_SALES_DELTA_RAW;
-- Index on the row-identity tuple makes duplicate_key and the STG
-- anti-join cheap.
CREATE INDEX IX_DMF_SOURCE__SALES_DELTA_RID ON DMF_SOURCE__SALES_DELTA (ITEM, ORG_NUM, DAY_DT, MIN_NUM, RTL_TYPE_CODE, IT_SEQ_NUM, SLS_TRX_ID, VOUCHER_ID, CO_LINE_ID);
-- Reset parallel attribute so per-rule reads don't auto-parallelize.
ALTER TABLE DMF_SOURCE__SALES_DELTA NOPARALLEL;
-- Sanity check:
SELECT COUNT(*) FROM DMF_SOURCE__SALES_DELTA;
-- ============================================================================
-- STEP 3 — Materialize duplicate-key set into DMF_DUPKEYS__SALES_DELTA
-- ============================================================================
-- 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_DELTA PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_DUPKEYS__SALES_DELTA AS
SELECT ITEM, ORG_NUM, DAY_DT, MIN_NUM, RTL_TYPE_CODE, IT_SEQ_NUM, SLS_TRX_ID, VOUCHER_ID, CO_LINE_ID FROM DMF_SOURCE__SALES_DELTA
GROUP BY ITEM, ORG_NUM, DAY_DT, MIN_NUM, RTL_TYPE_CODE, IT_SEQ_NUM, SLS_TRX_ID, VOUCHER_ID, CO_LINE_ID
HAVING COUNT(*) > 1;
CREATE INDEX IX_DMF_DUPKEYS__SALES_DELTA_KEY ON DMF_DUPKEYS__SALES_DELTA (ITEM, ORG_NUM, DAY_DT, MIN_NUM, RTL_TYPE_CODE, IT_SEQ_NUM, SLS_TRX_ID, VOUCHER_ID, CO_LINE_ID);
-- Sanity check (size of the conflict set):
SELECT COUNT(*) FROM DMF_DUPKEYS__SALES_DELTA;
-- ============================================================================
-- STEP 4 — Ensure ORI_SALES_DELTA_REJECTED exists, then empty it
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ORI_SALES_DELTA_REJECTED ( LOG_LVL VARCHAR2(1), ERROR_MSG VARCHAR2(255), VALIDATION_RUN_ID VARCHAR2(80), RULE_ID VARCHAR2(120), SRC_ROWID VARCHAR2(64), UPDATED_AT VARCHAR2(4000), ITEM VARCHAR2(4000), ORG_NUM VARCHAR2(4000), DAY_DT VARCHAR2(4000), MIN_NUM VARCHAR2(4000), IT_SEQ_NUM VARCHAR2(4000), RTL_TYPE_CODE VARCHAR2(4000), SLS_TRX_ID VARCHAR2(4000), VOUCHER_ID VARCHAR2(4000), PROMO_ID VARCHAR2(4000), PROMO_COMP_ID VARCHAR2(4000), ORIG_SLS_TRX_ID VARCHAR2(4000), ORIG_ORG_NUM VARCHAR2(4000), CASHIER_ID VARCHAR2(4000), REGISTER_ID VARCHAR2(4000), SALES_PERSON_ID VARCHAR2(4000), EMPLOYEE_NUM VARCHAR2(4000), CO_HEAD_ID VARCHAR2(4000), CO_LINE_ID VARCHAR2(4000), CO_CREATE_DT VARCHAR2(4000), CUSTOMER_NUM VARCHAR2(4000), CUSTOMER_TYPE VARCHAR2(4000), SLS_QTY VARCHAR2(4000), SLS_AMT_LCL VARCHAR2(4000), SLS_PROFIT_AMT_LCL VARCHAR2(4000), SLS_EMP_DISC_AMT_LCL VARCHAR2(4000), SLS_MANUAL_MKDN_AMT_LCL VARCHAR2(4000), SLS_MANUAL_MKUP_AMT_LCL VARCHAR2(4000), SLS_TAX_AMT_LCL VARCHAR2(4000), SLSPR_DISC_AMT_LCL VARCHAR2(4000), RET_QTY VARCHAR2(4000), RET_AMT_LCL VARCHAR2(4000), RET_PROFIT_AMT_LCL VARCHAR2(4000), RET_EMP_DISC_AMT_LCL VARCHAR2(4000), RET_MANUAL_MKDN_AMT_LCL VARCHAR2(4000), RET_MANUAL_MKUP_AMT_LCL VARCHAR2(4000), RET_TAX_AMT_LCL VARCHAR2(4000), RETPR_DISC_AMT_LCL VARCHAR2(4000), SALES_TYPE VARCHAR2(4000), REVISION_NUM VARCHAR2(4000), RETURN_REASON_CODE VARCHAR2(4000), RETURN_WH VARCHAR2(4000), POS_TRX_ID VARCHAR2(4000), RECEPT_IND VARCHAR2(4000), TAXABLE_IND VARCHAR2(4000), TRAN_PROCESS_SYS VARCHAR2(4000), TRAN_TYPE VARCHAR2(4000), LOC_CURR_CODE VARCHAR2(4000), LOC_EXCHANGE_RATE VARCHAR2(4000), DOC_CURR_CODE VARCHAR2(4000), FLEX1_CHAR_VALUE VARCHAR2(4000), FLEX7_CHAR_VALUE VARCHAR2(4000), FLEX8_CHAR_VALUE VARCHAR2(4000), FLEX9_CHAR_VALUE VARCHAR2(4000), FLEX10_CHAR_VALUE VARCHAR2(4000), FLEX16_CHAR_VALUE VARCHAR2(4000), FLEX17_CHAR_VALUE 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_DELTA_REJECTED;
-- If TRUNCATE fails with ORA-00054 (orphan lock), use DELETE instead:
-- DELETE FROM ORI_SALES_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 ORI_SALES_DELTA_REJECTED (
LOG_LVL, ERROR_MSG, VALIDATION_RUN_ID, RULE_ID, SRC_ROWID, UPDATED_AT, ITEM, ORG_NUM, DAY_DT, MIN_NUM, IT_SEQ_NUM, RTL_TYPE_CODE, SLS_TRX_ID, VOUCHER_ID, PROMO_ID, PROMO_COMP_ID, ORIG_SLS_TRX_ID, ORIG_ORG_NUM, CASHIER_ID, REGISTER_ID, SALES_PERSON_ID, EMPLOYEE_NUM, CO_HEAD_ID, CO_LINE_ID, CO_CREATE_DT, CUSTOMER_NUM, CUSTOMER_TYPE, SLS_QTY, SLS_AMT_LCL, SLS_PROFIT_AMT_LCL, SLS_EMP_DISC_AMT_LCL, SLS_MANUAL_MKDN_AMT_LCL, SLS_MANUAL_MKUP_AMT_LCL, SLS_TAX_AMT_LCL, SLSPR_DISC_AMT_LCL, RET_QTY, RET_AMT_LCL, RET_PROFIT_AMT_LCL, RET_EMP_DISC_AMT_LCL, RET_MANUAL_MKDN_AMT_LCL, RET_MANUAL_MKUP_AMT_LCL, RET_TAX_AMT_LCL, RETPR_DISC_AMT_LCL, SALES_TYPE, REVISION_NUM, RETURN_REASON_CODE, RETURN_WH, POS_TRX_ID, RECEPT_IND, TAXABLE_IND, TRAN_PROCESS_SYS, TRAN_TYPE, LOC_CURR_CODE, LOC_EXCHANGE_RATE, DOC_CURR_CODE, FLEX1_CHAR_VALUE, FLEX7_CHAR_VALUE, FLEX8_CHAR_VALUE, FLEX9_CHAR_VALUE, FLEX10_CHAR_VALUE, FLEX16_CHAR_VALUE, FLEX17_CHAR_VALUE, INSERTED_AT
)
WITH s AS (
SELECT
SRC_ROWID,
UPDATED_AT,
ITEM,
ORG_NUM,
DAY_DT,
MIN_NUM,
IT_SEQ_NUM,
RTL_TYPE_CODE,
SLS_TRX_ID,
VOUCHER_ID,
PROMO_ID,
PROMO_COMP_ID,
ORIG_SLS_TRX_ID,
ORIG_ORG_NUM,
CASHIER_ID,
REGISTER_ID,
SALES_PERSON_ID,
EMPLOYEE_NUM,
CO_HEAD_ID,
CO_LINE_ID,
CO_CREATE_DT,
CUSTOMER_NUM,
CUSTOMER_TYPE,
SLS_QTY,
SLS_AMT_LCL,
SLS_PROFIT_AMT_LCL,
SLS_EMP_DISC_AMT_LCL,
SLS_MANUAL_MKDN_AMT_LCL,
SLS_MANUAL_MKUP_AMT_LCL,
SLS_TAX_AMT_LCL,
SLSPR_DISC_AMT_LCL,
RET_QTY,
RET_AMT_LCL,
RET_PROFIT_AMT_LCL,
RET_EMP_DISC_AMT_LCL,
RET_MANUAL_MKDN_AMT_LCL,
RET_MANUAL_MKUP_AMT_LCL,
RET_TAX_AMT_LCL,
RETPR_DISC_AMT_LCL,
SALES_TYPE,
REVISION_NUM,
RETURN_REASON_CODE,
RETURN_WH,
POS_TRX_ID,
RECEPT_IND,
TAXABLE_IND,
TRAN_PROCESS_SYS,
TRAN_TYPE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
DOC_CURR_CODE,
FLEX1_CHAR_VALUE,
FLEX7_CHAR_VALUE,
FLEX8_CHAR_VALUE,
FLEX9_CHAR_VALUE,
FLEX10_CHAR_VALUE,
FLEX16_CHAR_VALUE,
FLEX17_CHAR_VALUE
FROM DMF_SOURCE__SALES_DELTA
)
SELECT 'E', 'INVALID FORMAT: ITEM', '20260611T113316_ed913ec1', 'ERROR_DTYPE_ITEM', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE ITEM IS NOT NULL AND LENGTHB(TO_CHAR(ITEM)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: ORG_NUM', '20260611T113316_ed913ec1', 'ERROR_DTYPE_ORG_NUM', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE ORG_NUM IS NOT NULL AND LENGTHB(TO_CHAR(ORG_NUM)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: DAY_DT', '20260611T113316_ed913ec1', 'ERROR_DTYPE_DAY_DT', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE DAY_DT IS NOT NULL AND TRUNC(DAY_DT) <> DAY_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: MIN_NUM', '20260611T113316_ed913ec1', 'ERROR_DTYPE_MIN_NUM', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE MIN_NUM IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(MIN_NUM))) >= POWER(10,4) OR TO_NUMBER(MIN_NUM) != ROUND(TO_NUMBER(MIN_NUM),0))
UNION ALL
SELECT 'E', 'INVALID FORMAT: IT_SEQ_NUM', '20260611T113316_ed913ec1', 'ERROR_DTYPE_IT_SEQ_NUM', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE IT_SEQ_NUM IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(IT_SEQ_NUM))) >= POWER(10,4) OR TO_NUMBER(IT_SEQ_NUM) != ROUND(TO_NUMBER(IT_SEQ_NUM),0))
UNION ALL
SELECT 'E', 'INVALID FORMAT: RTL_TYPE_CODE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_RTL_TYPE_CODE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RTL_TYPE_CODE IS NOT NULL AND LENGTHB(TO_CHAR(RTL_TYPE_CODE)) > 50
UNION ALL
SELECT 'E', 'INVALID FORMAT: SLS_TRX_ID', '20260611T113316_ed913ec1', 'ERROR_DTYPE_SLS_TRX_ID', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SLS_TRX_ID IS NOT NULL AND LENGTHB(TO_CHAR(SLS_TRX_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: VOUCHER_ID', '20260611T113316_ed913ec1', 'ERROR_DTYPE_VOUCHER_ID', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE VOUCHER_ID IS NOT NULL AND LENGTHB(TO_CHAR(VOUCHER_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: PROMO_ID', '20260611T113316_ed913ec1', 'ERROR_DTYPE_PROMO_ID', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE PROMO_ID IS NOT NULL AND LENGTHB(TO_CHAR(PROMO_ID)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: PROMO_COMP_ID', '20260611T113316_ed913ec1', 'ERROR_DTYPE_PROMO_COMP_ID', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE PROMO_COMP_ID IS NOT NULL AND LENGTHB(TO_CHAR(PROMO_COMP_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: ORIG_SLS_TRX_ID', '20260611T113316_ed913ec1', 'ERROR_DTYPE_ORIG_SLS_TRX_ID', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE ORIG_SLS_TRX_ID IS NOT NULL AND LENGTHB(TO_CHAR(ORIG_SLS_TRX_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: ORIG_ORG_NUM', '20260611T113316_ed913ec1', 'ERROR_DTYPE_ORIG_ORG_NUM', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE ORIG_ORG_NUM IS NOT NULL AND LENGTHB(TO_CHAR(ORIG_ORG_NUM)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: CASHIER_ID', '20260611T113316_ed913ec1', 'ERROR_DTYPE_CASHIER_ID', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE CASHIER_ID IS NOT NULL AND LENGTHB(TO_CHAR(CASHIER_ID)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: REGISTER_ID', '20260611T113316_ed913ec1', 'ERROR_DTYPE_REGISTER_ID', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE REGISTER_ID IS NOT NULL AND LENGTHB(TO_CHAR(REGISTER_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: SALES_PERSON_ID', '20260611T113316_ed913ec1', 'ERROR_DTYPE_SALES_PERSON_ID', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SALES_PERSON_ID IS NOT NULL AND LENGTHB(TO_CHAR(SALES_PERSON_ID)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: EMPLOYEE_NUM', '20260611T113316_ed913ec1', 'ERROR_DTYPE_EMPLOYEE_NUM', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE EMPLOYEE_NUM IS NOT NULL AND LENGTHB(TO_CHAR(EMPLOYEE_NUM)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: CO_HEAD_ID', '20260611T113316_ed913ec1', 'ERROR_DTYPE_CO_HEAD_ID', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE CO_HEAD_ID IS NOT NULL AND LENGTHB(TO_CHAR(CO_HEAD_ID)) > 50
UNION ALL
SELECT 'E', 'INVALID FORMAT: CO_LINE_ID', '20260611T113316_ed913ec1', 'ERROR_DTYPE_CO_LINE_ID', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE CO_LINE_ID IS NOT NULL AND LENGTHB(TO_CHAR(CO_LINE_ID)) > 50
UNION ALL
SELECT 'E', 'INVALID FORMAT: CO_CREATE_DT', '20260611T113316_ed913ec1', 'ERROR_DTYPE_CO_CREATE_DT', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE CO_CREATE_DT IS NOT NULL AND TRUNC(CO_CREATE_DT) <> CO_CREATE_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: CUSTOMER_NUM', '20260611T113316_ed913ec1', 'ERROR_DTYPE_CUSTOMER_NUM', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE CUSTOMER_NUM IS NOT NULL AND LENGTHB(TO_CHAR(CUSTOMER_NUM)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: CUSTOMER_TYPE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_CUSTOMER_TYPE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE CUSTOMER_TYPE IS NOT NULL AND LENGTHB(TO_CHAR(CUSTOMER_TYPE)) > 6
UNION ALL
SELECT 'E', 'INVALID FORMAT: SLS_QTY', '20260611T113316_ed913ec1', 'ERROR_DTYPE_SLS_QTY', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SLS_QTY IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(SLS_QTY))) >= POWER(10,14) OR TO_NUMBER(SLS_QTY) != ROUND(TO_NUMBER(SLS_QTY),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: SLS_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_SLS_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SLS_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(SLS_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(SLS_AMT_LCL) != ROUND(TO_NUMBER(SLS_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: SLS_PROFIT_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_SLS_PROFIT_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SLS_PROFIT_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(SLS_PROFIT_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(SLS_PROFIT_AMT_LCL) != ROUND(TO_NUMBER(SLS_PROFIT_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: SLS_EMP_DISC_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_SLS_EMP_DISC_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SLS_EMP_DISC_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(SLS_EMP_DISC_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(SLS_EMP_DISC_AMT_LCL) != ROUND(TO_NUMBER(SLS_EMP_DISC_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: SLS_MANUAL_MKDN_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_SLS_MANUAL_MKDN_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SLS_MANUAL_MKDN_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(SLS_MANUAL_MKDN_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(SLS_MANUAL_MKDN_AMT_LCL) != ROUND(TO_NUMBER(SLS_MANUAL_MKDN_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: SLS_MANUAL_MKUP_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_SLS_MANUAL_MKUP_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SLS_MANUAL_MKUP_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(SLS_MANUAL_MKUP_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(SLS_MANUAL_MKUP_AMT_LCL) != ROUND(TO_NUMBER(SLS_MANUAL_MKUP_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: SLS_TAX_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_SLS_TAX_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SLS_TAX_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(SLS_TAX_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(SLS_TAX_AMT_LCL) != ROUND(TO_NUMBER(SLS_TAX_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: SLSPR_DISC_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_SLSPR_DISC_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SLSPR_DISC_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(SLSPR_DISC_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(SLSPR_DISC_AMT_LCL) != ROUND(TO_NUMBER(SLSPR_DISC_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: RET_QTY', '20260611T113316_ed913ec1', 'ERROR_DTYPE_RET_QTY', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RET_QTY IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(RET_QTY))) >= POWER(10,14) OR TO_NUMBER(RET_QTY) != ROUND(TO_NUMBER(RET_QTY),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: RET_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_RET_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RET_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(RET_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(RET_AMT_LCL) != ROUND(TO_NUMBER(RET_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: RET_PROFIT_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_RET_PROFIT_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RET_PROFIT_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(RET_PROFIT_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(RET_PROFIT_AMT_LCL) != ROUND(TO_NUMBER(RET_PROFIT_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: RET_EMP_DISC_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_RET_EMP_DISC_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RET_EMP_DISC_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(RET_EMP_DISC_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(RET_EMP_DISC_AMT_LCL) != ROUND(TO_NUMBER(RET_EMP_DISC_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: RET_MANUAL_MKDN_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_RET_MANUAL_MKDN_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RET_MANUAL_MKDN_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(RET_MANUAL_MKDN_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(RET_MANUAL_MKDN_AMT_LCL) != ROUND(TO_NUMBER(RET_MANUAL_MKDN_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: RET_MANUAL_MKUP_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_RET_MANUAL_MKUP_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RET_MANUAL_MKUP_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(RET_MANUAL_MKUP_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(RET_MANUAL_MKUP_AMT_LCL) != ROUND(TO_NUMBER(RET_MANUAL_MKUP_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: RET_TAX_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_RET_TAX_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RET_TAX_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(RET_TAX_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(RET_TAX_AMT_LCL) != ROUND(TO_NUMBER(RET_TAX_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: RETPR_DISC_AMT_LCL', '20260611T113316_ed913ec1', 'ERROR_DTYPE_RETPR_DISC_AMT_LCL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RETPR_DISC_AMT_LCL IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(RETPR_DISC_AMT_LCL))) >= POWER(10,16) OR TO_NUMBER(RETPR_DISC_AMT_LCL) != ROUND(TO_NUMBER(RETPR_DISC_AMT_LCL),4))
UNION ALL
SELECT 'E', 'INVALID FORMAT: SALES_TYPE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_SALES_TYPE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SALES_TYPE IS NOT NULL AND LENGTHB(TO_CHAR(SALES_TYPE)) > 1
UNION ALL
SELECT 'E', 'INVALID FORMAT: REVISION_NUM', '20260611T113316_ed913ec1', 'ERROR_DTYPE_REVISION_NUM', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE REVISION_NUM IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(REVISION_NUM))) >= POWER(10,3) OR TO_NUMBER(REVISION_NUM) != ROUND(TO_NUMBER(REVISION_NUM),0))
UNION ALL
SELECT 'E', 'INVALID FORMAT: RETURN_REASON_CODE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_RETURN_REASON_CODE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RETURN_REASON_CODE IS NOT NULL AND LENGTHB(TO_CHAR(RETURN_REASON_CODE)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: RETURN_WH', '20260611T113316_ed913ec1', 'ERROR_DTYPE_RETURN_WH', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RETURN_WH IS NOT NULL AND LENGTHB(TO_CHAR(RETURN_WH)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: POS_TRX_ID', '20260611T113316_ed913ec1', 'ERROR_DTYPE_POS_TRX_ID', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE POS_TRX_ID IS NOT NULL AND LENGTHB(TO_CHAR(POS_TRX_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: RECEPT_IND', '20260611T113316_ed913ec1', 'ERROR_DTYPE_RECEPT_IND', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RECEPT_IND IS NOT NULL AND LENGTHB(TO_CHAR(RECEPT_IND)) > 1
UNION ALL
SELECT 'E', 'INVALID FORMAT: TAXABLE_IND', '20260611T113316_ed913ec1', 'ERROR_DTYPE_TAXABLE_IND', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE TAXABLE_IND IS NOT NULL AND LENGTHB(TO_CHAR(TAXABLE_IND)) > 1
UNION ALL
SELECT 'E', 'INVALID FORMAT: TRAN_PROCESS_SYS', '20260611T113316_ed913ec1', 'ERROR_DTYPE_TRAN_PROCESS_SYS', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE TRAN_PROCESS_SYS IS NOT NULL AND LENGTHB(TO_CHAR(TRAN_PROCESS_SYS)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: TRAN_TYPE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_TRAN_TYPE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE TRAN_TYPE IS NOT NULL AND LENGTHB(TO_CHAR(TRAN_TYPE)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: LOC_CURR_CODE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_LOC_CURR_CODE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE LOC_CURR_CODE IS NOT NULL AND LENGTHB(TO_CHAR(LOC_CURR_CODE)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: LOC_EXCHANGE_RATE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_LOC_EXCHANGE_RATE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE LOC_EXCHANGE_RATE IS NOT NULL AND (ABS(TRUNC(TO_NUMBER(LOC_EXCHANGE_RATE))) >= POWER(10,15) OR TO_NUMBER(LOC_EXCHANGE_RATE) != ROUND(TO_NUMBER(LOC_EXCHANGE_RATE),7))
UNION ALL
SELECT 'E', 'INVALID FORMAT: DOC_CURR_CODE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_DOC_CURR_CODE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE DOC_CURR_CODE IS NOT NULL AND LENGTHB(TO_CHAR(DOC_CURR_CODE)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: FLEX1_CHAR_VALUE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_FLEX1_CHAR_VALUE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE FLEX1_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX1_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: FLEX7_CHAR_VALUE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_FLEX7_CHAR_VALUE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE FLEX7_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX7_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: FLEX8_CHAR_VALUE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_FLEX8_CHAR_VALUE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE FLEX8_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX8_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: FLEX9_CHAR_VALUE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_FLEX9_CHAR_VALUE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE FLEX9_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX9_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: FLEX10_CHAR_VALUE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_FLEX10_CHAR_VALUE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE FLEX10_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX10_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: FLEX16_CHAR_VALUE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_FLEX16_CHAR_VALUE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE FLEX16_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX16_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: FLEX17_CHAR_VALUE', '20260611T113316_ed913ec1', 'ERROR_DTYPE_FLEX17_CHAR_VALUE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE FLEX17_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX17_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E', 'ITEM IS NULL', '20260611T113316_ed913ec1', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE ITEM IS NULL
UNION ALL
SELECT 'E', 'ORG_NUM IS NULL', '20260611T113316_ed913ec1', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE ORG_NUM IS NULL
UNION ALL
SELECT 'E', 'DAY_DT IS NULL', '20260611T113316_ed913ec1', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE DAY_DT IS NULL
UNION ALL
SELECT 'E', 'MIN_NUM IS NULL', '20260611T113316_ed913ec1', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE MIN_NUM IS NULL
UNION ALL
SELECT 'E', 'RTL_TYPE_CODE IS NULL', '20260611T113316_ed913ec1', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RTL_TYPE_CODE IS NULL
UNION ALL
SELECT 'E', 'SLS_TRX_ID IS NULL', '20260611T113316_ed913ec1', 'ERROR_INVALID_NULL', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SLS_TRX_ID IS NULL
UNION ALL
SELECT 'E', 'INVALID RTL_TYPE_CODE: NOT IN ALLOWED VALUES', '20260611T113316_ed913ec1', 'ERROR_INVALID_RTL_TYPE_CODE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE RTL_TYPE_CODE NOT IN ('R', 'P', 'C')
UNION ALL
SELECT 'E', 'INVALID TRAN_TYPE: NOT IN ALLOWED VALUES', '20260611T113316_ed913ec1', 'ERROR_INVALID_TRAN_TYPE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE TRAN_TYPE NOT IN ('SALE', 'RETURN')
UNION ALL
SELECT 'W', 'INVALID SALES_TYPE: NOT IN ALLOWED VALUES', '20260611T113316_ed913ec1', 'WARN_INVALID_SALES_TYPE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE SALES_TYPE NOT IN ('I', 'E', 'R')
UNION ALL
SELECT 'W', 'INVALID TAXABLE_IND: NOT IN ALLOWED VALUES', '20260611T113316_ed913ec1', 'WARN_INVALID_TAXABLE_IND', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE TAXABLE_IND NOT IN ('Y', 'N')
UNION ALL
SELECT 'W', 'INVALID TRAN_PROCESS_SYS: NOT IN ALLOWED VALUES', '20260611T113316_ed913ec1', 'WARN_INVALID_TRAN_PROCESS_SYS', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE TRAN_PROCESS_SYS NOT IN ('POS', 'OMS', 'SIM')
UNION ALL
SELECT 'E', 'ITEM NOT FOUND: ITEM_MASTER_CTRL', '20260611T113316_ed913ec1', 'ERROR_MISSING_ITEM', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, 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', '20260611T113316_ed913ec1', 'ERROR_MISSING_STORE', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, 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 'W', 'WARN_NEGATIVE_SALES', '20260611T113316_ed913ec1', 'WARN_NEGATIVE_SALES', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE TRAN_TYPE = 'SALE' AND (SLS_QTY < 0 OR SLS_AMT_LCL < 0)
UNION ALL
SELECT 'W', 'WARN_NEGATIVE_RETURNS', '20260611T113316_ed913ec1', 'WARN_NEGATIVE_RETURNS', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE TRAN_TYPE = 'RETURN' AND (RET_QTY < 0 OR RET_AMT_LCL < 0)
UNION ALL
SELECT 'E', 'DUPLICATE KEY: ITEM, ORG_NUM, DAY_DT, MIN_NUM, RTL_TYPE_CODE, IT_SEQ_NUM, SLS_TRX_ID, VOUCHER_ID, CO_LINE_ID', '20260611T113316_ed913ec1', 'ERR_DUP_KEY_CONFLICT', s.SRC_ROWID, s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE FROM s WHERE (ITEM, ORG_NUM, DAY_DT, MIN_NUM, RTL_TYPE_CODE, IT_SEQ_NUM, SLS_TRX_ID, VOUCHER_ID, CO_LINE_ID) IN (SELECT ITEM, ORG_NUM, DAY_DT, MIN_NUM, RTL_TYPE_CODE, IT_SEQ_NUM, SLS_TRX_ID, VOUCHER_ID, CO_LINE_ID FROM DMF_DUPKEYS__SALES_DELTA);
COMMIT;
-- ============================================================================
-- STEP 6 — Build typed ORI_SALES_DELTA_STG (drop-and-recreate)
-- ============================================================================
-- DDL types are pulled from the RAP spec (not VARCHAR2(4000) blanket).
DROP TABLE ORI_SALES_DELTA_STG PURGE; -- ignore ORA-00942
CREATE TABLE ORI_SALES_DELTA_STG (
UPDATED_AT VARCHAR2(4000),
ITEM VARCHAR2(80 BYTE),
ORG_NUM VARCHAR2(80 BYTE),
DAY_DT DATE,
MIN_NUM NUMBER(4,0),
IT_SEQ_NUM NUMBER(4,0),
RTL_TYPE_CODE VARCHAR2(50 BYTE),
SLS_TRX_ID VARCHAR2(30 BYTE),
VOUCHER_ID VARCHAR2(30 BYTE),
PROMO_ID VARCHAR2(80 BYTE),
PROMO_COMP_ID VARCHAR2(30 BYTE),
ORIG_SLS_TRX_ID VARCHAR2(30 BYTE),
ORIG_ORG_NUM VARCHAR2(80 BYTE),
CASHIER_ID VARCHAR2(80 BYTE),
REGISTER_ID VARCHAR2(30 BYTE),
SALES_PERSON_ID VARCHAR2(80 BYTE),
EMPLOYEE_NUM VARCHAR2(80 BYTE),
CO_HEAD_ID VARCHAR2(50 BYTE),
CO_LINE_ID VARCHAR2(50 BYTE),
CO_CREATE_DT DATE,
CUSTOMER_NUM VARCHAR2(80 BYTE),
CUSTOMER_TYPE VARCHAR2(6 BYTE),
SLS_QTY NUMBER(18,4),
SLS_AMT_LCL NUMBER(20,4),
SLS_PROFIT_AMT_LCL NUMBER(20,4),
SLS_EMP_DISC_AMT_LCL NUMBER(20,4),
SLS_MANUAL_MKDN_AMT_LCL NUMBER(20,4),
SLS_MANUAL_MKUP_AMT_LCL NUMBER(20,4),
SLS_TAX_AMT_LCL NUMBER(20,4),
SLSPR_DISC_AMT_LCL NUMBER(20,4),
RET_QTY NUMBER(18,4),
RET_AMT_LCL NUMBER(20,4),
RET_PROFIT_AMT_LCL NUMBER(20,4),
RET_EMP_DISC_AMT_LCL NUMBER(20,4),
RET_MANUAL_MKDN_AMT_LCL NUMBER(20,4),
RET_MANUAL_MKUP_AMT_LCL NUMBER(20,4),
RET_TAX_AMT_LCL NUMBER(20,4),
RETPR_DISC_AMT_LCL NUMBER(20,4),
SALES_TYPE VARCHAR2(1 BYTE),
REVISION_NUM NUMBER(3,0),
RETURN_REASON_CODE VARCHAR2(30 BYTE),
RETURN_WH VARCHAR2(30 BYTE),
POS_TRX_ID VARCHAR2(30 BYTE),
RECEPT_IND VARCHAR2(4000),
TAXABLE_IND VARCHAR2(1 BYTE),
TRAN_PROCESS_SYS VARCHAR2(30 BYTE),
TRAN_TYPE VARCHAR2(30 BYTE),
LOC_CURR_CODE VARCHAR2(30 BYTE),
LOC_EXCHANGE_RATE NUMBER(22,7),
DOC_CURR_CODE VARCHAR2(30 BYTE),
FLEX1_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX7_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX8_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX9_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX10_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX16_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX17_CHAR_VALUE VARCHAR2(80 BYTE),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 7 — Populate ORI_SALES_DELTA_STG (anti-join on SRC_ROWID; LOG_LVL='W' rows still flow through)
-- ============================================================================
INSERT INTO ORI_SALES_DELTA_STG (UPDATED_AT, ITEM, ORG_NUM, DAY_DT, MIN_NUM, IT_SEQ_NUM, RTL_TYPE_CODE, SLS_TRX_ID, VOUCHER_ID, PROMO_ID, PROMO_COMP_ID, ORIG_SLS_TRX_ID, ORIG_ORG_NUM, CASHIER_ID, REGISTER_ID, SALES_PERSON_ID, EMPLOYEE_NUM, CO_HEAD_ID, CO_LINE_ID, CO_CREATE_DT, CUSTOMER_NUM, CUSTOMER_TYPE, SLS_QTY, SLS_AMT_LCL, SLS_PROFIT_AMT_LCL, SLS_EMP_DISC_AMT_LCL, SLS_MANUAL_MKDN_AMT_LCL, SLS_MANUAL_MKUP_AMT_LCL, SLS_TAX_AMT_LCL, SLSPR_DISC_AMT_LCL, RET_QTY, RET_AMT_LCL, RET_PROFIT_AMT_LCL, RET_EMP_DISC_AMT_LCL, RET_MANUAL_MKDN_AMT_LCL, RET_MANUAL_MKUP_AMT_LCL, RET_TAX_AMT_LCL, RETPR_DISC_AMT_LCL, SALES_TYPE, REVISION_NUM, RETURN_REASON_CODE, RETURN_WH, POS_TRX_ID, RECEPT_IND, TAXABLE_IND, TRAN_PROCESS_SYS, TRAN_TYPE, LOC_CURR_CODE, LOC_EXCHANGE_RATE, DOC_CURR_CODE, FLEX1_CHAR_VALUE, FLEX7_CHAR_VALUE, FLEX8_CHAR_VALUE, FLEX9_CHAR_VALUE, FLEX10_CHAR_VALUE, FLEX16_CHAR_VALUE, FLEX17_CHAR_VALUE, INSERTED_AT)
WITH s AS (
SELECT
SRC_ROWID,
UPDATED_AT,
ITEM,
ORG_NUM,
DAY_DT,
MIN_NUM,
IT_SEQ_NUM,
RTL_TYPE_CODE,
SLS_TRX_ID,
VOUCHER_ID,
PROMO_ID,
PROMO_COMP_ID,
ORIG_SLS_TRX_ID,
ORIG_ORG_NUM,
CASHIER_ID,
REGISTER_ID,
SALES_PERSON_ID,
EMPLOYEE_NUM,
CO_HEAD_ID,
CO_LINE_ID,
CO_CREATE_DT,
CUSTOMER_NUM,
CUSTOMER_TYPE,
SLS_QTY,
SLS_AMT_LCL,
SLS_PROFIT_AMT_LCL,
SLS_EMP_DISC_AMT_LCL,
SLS_MANUAL_MKDN_AMT_LCL,
SLS_MANUAL_MKUP_AMT_LCL,
SLS_TAX_AMT_LCL,
SLSPR_DISC_AMT_LCL,
RET_QTY,
RET_AMT_LCL,
RET_PROFIT_AMT_LCL,
RET_EMP_DISC_AMT_LCL,
RET_MANUAL_MKDN_AMT_LCL,
RET_MANUAL_MKUP_AMT_LCL,
RET_TAX_AMT_LCL,
RETPR_DISC_AMT_LCL,
SALES_TYPE,
REVISION_NUM,
RETURN_REASON_CODE,
RETURN_WH,
POS_TRX_ID,
RECEPT_IND,
TAXABLE_IND,
TRAN_PROCESS_SYS,
TRAN_TYPE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
DOC_CURR_CODE,
FLEX1_CHAR_VALUE,
FLEX7_CHAR_VALUE,
FLEX8_CHAR_VALUE,
FLEX9_CHAR_VALUE,
FLEX10_CHAR_VALUE,
FLEX16_CHAR_VALUE,
FLEX17_CHAR_VALUE
FROM DMF_SOURCE__SALES_DELTA
)
SELECT s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE
FROM s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_SALES_DELTA_REJECTED r
WHERE r.VALIDATION_RUN_ID = '20260611T113316_ed913ec1'
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.MIN_NUM IS NOT NULL
AND s.RTL_TYPE_CODE IS NOT NULL
AND s.SLS_TRX_ID IS NOT NULL;
COMMIT;
-- Row count check: should be COUNT(ORI_SALES_DELTA_RAW) minus distinct SRC_ROWIDs in ORI_SALES_DELTA_REJECTED where LOG_LVL='E'.
SELECT COUNT(*) AS stg_rows FROM ORI_SALES_DELTA_STG;
-- ============================================================================
-- STEP 8 — Build ORI_SALES_DELTA_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_DELTA_CTRL PURGE; -- ignore ORA-00942
CREATE TABLE ORI_SALES_DELTA_CTRL (
ITEM VARCHAR2(80 BYTE),
ORG_NUM VARCHAR2(80 BYTE),
DAY_DT DATE,
MIN_NUM NUMBER(4,0),
IT_SEQ_NUM NUMBER(4,0),
RTL_TYPE_CODE VARCHAR2(50 BYTE),
SLS_TRX_ID VARCHAR2(30 BYTE),
VOUCHER_ID VARCHAR2(30 BYTE),
PROMO_ID VARCHAR2(80 BYTE),
PROMO_COMP_ID VARCHAR2(30 BYTE),
ORIG_SLS_TRX_ID VARCHAR2(30 BYTE),
ORIG_ORG_NUM VARCHAR2(80 BYTE),
CASHIER_ID VARCHAR2(80 BYTE),
REGISTER_ID VARCHAR2(30 BYTE),
SALES_PERSON_ID VARCHAR2(80 BYTE),
EMPLOYEE_NUM VARCHAR2(80 BYTE),
CO_HEAD_ID VARCHAR2(50 BYTE),
CO_LINE_ID VARCHAR2(50 BYTE),
CO_CREATE_DT DATE,
CUSTOMER_NUM VARCHAR2(80 BYTE),
CUSTOMER_TYPE VARCHAR2(6 BYTE),
SLS_QTY NUMBER(18,4),
SLS_AMT_LCL NUMBER(20,4),
SLS_PROFIT_AMT_LCL NUMBER(20,4),
SLS_EMP_DISC_AMT_LCL NUMBER(20,4),
SLS_MANUAL_MKDN_AMT_LCL NUMBER(20,4),
SLS_MANUAL_MKUP_AMT_LCL NUMBER(20,4),
SLS_TAX_AMT_LCL NUMBER(20,4),
SLSPR_DISC_AMT_LCL NUMBER(20,4),
RET_QTY NUMBER(18,4),
RET_AMT_LCL NUMBER(20,4),
RET_PROFIT_AMT_LCL NUMBER(20,4),
RET_EMP_DISC_AMT_LCL NUMBER(20,4),
RET_MANUAL_MKDN_AMT_LCL NUMBER(20,4),
RET_MANUAL_MKUP_AMT_LCL NUMBER(20,4),
RET_TAX_AMT_LCL NUMBER(20,4),
RETPR_DISC_AMT_LCL NUMBER(20,4),
SALES_TYPE VARCHAR2(1 BYTE),
REVISION_NUM NUMBER(3,0),
RETURN_REASON_CODE VARCHAR2(30 BYTE),
RETURN_WH VARCHAR2(30 BYTE),
POS_TRX_ID VARCHAR2(30 BYTE),
RECEIPT_IND VARCHAR2(1 BYTE),
TAXABLE_IND VARCHAR2(1 BYTE),
TRAN_PROCESS_SYS VARCHAR2(30 BYTE),
TRAN_TYPE VARCHAR2(30 BYTE),
LOC_CURR_CODE VARCHAR2(30 BYTE),
LOC_EXCHANGE_RATE NUMBER(22,7),
DOC_CURR_CODE VARCHAR2(30 BYTE),
FLEX1_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX7_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX8_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX9_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX10_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX16_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX17_CHAR_VALUE VARCHAR2(80 BYTE),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 9 — Populate ORI_SALES_DELTA_CTRL from ORI_SALES_DELTA_STG
-- ============================================================================
INSERT INTO ORI_SALES_DELTA_CTRL (ITEM, ORG_NUM, DAY_DT, MIN_NUM, IT_SEQ_NUM, RTL_TYPE_CODE, SLS_TRX_ID, VOUCHER_ID, PROMO_ID, PROMO_COMP_ID, ORIG_SLS_TRX_ID, ORIG_ORG_NUM, CASHIER_ID, REGISTER_ID, SALES_PERSON_ID, EMPLOYEE_NUM, CO_HEAD_ID, CO_LINE_ID, CO_CREATE_DT, CUSTOMER_NUM, CUSTOMER_TYPE, SLS_QTY, SLS_AMT_LCL, SLS_PROFIT_AMT_LCL, SLS_EMP_DISC_AMT_LCL, SLS_MANUAL_MKDN_AMT_LCL, SLS_MANUAL_MKUP_AMT_LCL, SLS_TAX_AMT_LCL, SLSPR_DISC_AMT_LCL, RET_QTY, RET_AMT_LCL, RET_PROFIT_AMT_LCL, RET_EMP_DISC_AMT_LCL, RET_MANUAL_MKDN_AMT_LCL, RET_MANUAL_MKUP_AMT_LCL, RET_TAX_AMT_LCL, RETPR_DISC_AMT_LCL, SALES_TYPE, REVISION_NUM, RETURN_REASON_CODE, RETURN_WH, POS_TRX_ID, RECEIPT_IND, TAXABLE_IND, TRAN_PROCESS_SYS, TRAN_TYPE, LOC_CURR_CODE, LOC_EXCHANGE_RATE, DOC_CURR_CODE, FLEX1_CHAR_VALUE, FLEX7_CHAR_VALUE, FLEX8_CHAR_VALUE, FLEX9_CHAR_VALUE, FLEX10_CHAR_VALUE, FLEX16_CHAR_VALUE, FLEX17_CHAR_VALUE, INSERTED_AT)
SELECT s.ITEM, s.ORG_NUM, s.DAY_DT, CASE WHEN s.MIN_NUM IS NULL OR ABS(TRUNC(s.MIN_NUM)) < 10000 THEN s.MIN_NUM ELSE NULL END, CASE WHEN s.IT_SEQ_NUM IS NULL OR ABS(TRUNC(s.IT_SEQ_NUM)) < 10000 THEN s.IT_SEQ_NUM ELSE NULL END, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, CASE WHEN s.SLS_QTY IS NULL OR ABS(TRUNC(s.SLS_QTY)) < 100000000000000 THEN s.SLS_QTY ELSE NULL END, CASE WHEN s.SLS_AMT_LCL IS NULL OR ABS(TRUNC(s.SLS_AMT_LCL)) < 10000000000000000 THEN s.SLS_AMT_LCL ELSE NULL END, CASE WHEN s.SLS_PROFIT_AMT_LCL IS NULL OR ABS(TRUNC(s.SLS_PROFIT_AMT_LCL)) < 10000000000000000 THEN s.SLS_PROFIT_AMT_LCL ELSE NULL END, CASE WHEN s.SLS_EMP_DISC_AMT_LCL IS NULL OR ABS(TRUNC(s.SLS_EMP_DISC_AMT_LCL)) < 10000000000000000 THEN s.SLS_EMP_DISC_AMT_LCL ELSE NULL END, CASE WHEN s.SLS_MANUAL_MKDN_AMT_LCL IS NULL OR ABS(TRUNC(s.SLS_MANUAL_MKDN_AMT_LCL)) < 10000000000000000 THEN s.SLS_MANUAL_MKDN_AMT_LCL ELSE NULL END, CASE WHEN s.SLS_MANUAL_MKUP_AMT_LCL IS NULL OR ABS(TRUNC(s.SLS_MANUAL_MKUP_AMT_LCL)) < 10000000000000000 THEN s.SLS_MANUAL_MKUP_AMT_LCL ELSE NULL END, CASE WHEN s.SLS_TAX_AMT_LCL IS NULL OR ABS(TRUNC(s.SLS_TAX_AMT_LCL)) < 10000000000000000 THEN s.SLS_TAX_AMT_LCL ELSE NULL END, CASE WHEN s.SLSPR_DISC_AMT_LCL IS NULL OR ABS(TRUNC(s.SLSPR_DISC_AMT_LCL)) < 10000000000000000 THEN s.SLSPR_DISC_AMT_LCL ELSE NULL END, CASE WHEN s.RET_QTY IS NULL OR ABS(TRUNC(s.RET_QTY)) < 100000000000000 THEN s.RET_QTY ELSE NULL END, CASE WHEN s.RET_AMT_LCL IS NULL OR ABS(TRUNC(s.RET_AMT_LCL)) < 10000000000000000 THEN s.RET_AMT_LCL ELSE NULL END, CASE WHEN s.RET_PROFIT_AMT_LCL IS NULL OR ABS(TRUNC(s.RET_PROFIT_AMT_LCL)) < 10000000000000000 THEN s.RET_PROFIT_AMT_LCL ELSE NULL END, CASE WHEN s.RET_EMP_DISC_AMT_LCL IS NULL OR ABS(TRUNC(s.RET_EMP_DISC_AMT_LCL)) < 10000000000000000 THEN s.RET_EMP_DISC_AMT_LCL ELSE NULL END, CASE WHEN s.RET_MANUAL_MKDN_AMT_LCL IS NULL OR ABS(TRUNC(s.RET_MANUAL_MKDN_AMT_LCL)) < 10000000000000000 THEN s.RET_MANUAL_MKDN_AMT_LCL ELSE NULL END, CASE WHEN s.RET_MANUAL_MKUP_AMT_LCL IS NULL OR ABS(TRUNC(s.RET_MANUAL_MKUP_AMT_LCL)) < 10000000000000000 THEN s.RET_MANUAL_MKUP_AMT_LCL ELSE NULL END, CASE WHEN s.RET_TAX_AMT_LCL IS NULL OR ABS(TRUNC(s.RET_TAX_AMT_LCL)) < 10000000000000000 THEN s.RET_TAX_AMT_LCL ELSE NULL END, CASE WHEN s.RETPR_DISC_AMT_LCL IS NULL OR ABS(TRUNC(s.RETPR_DISC_AMT_LCL)) < 10000000000000000 THEN s.RETPR_DISC_AMT_LCL ELSE NULL END, s.SALES_TYPE, CASE WHEN s.REVISION_NUM IS NULL OR ABS(TRUNC(s.REVISION_NUM)) < 1000 THEN s.REVISION_NUM ELSE NULL END, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, CASE WHEN s.LOC_EXCHANGE_RATE IS NULL OR ABS(TRUNC(s.LOC_EXCHANGE_RATE)) < 1000000000000000 THEN s.LOC_EXCHANGE_RATE ELSE NULL END, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE
FROM ORI_SALES_DELTA_STG s;
COMMIT;
SELECT COUNT(*) AS ctrl_rows FROM ORI_SALES_DELTA_CTRL;
-- ============================================================================
-- STEP 10 — Rejection summary by rule
-- ============================================================================
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM ORI_SALES_DELTA_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;