Source: DMF/PuC/AIF/Sales history.sql
-- ============================================================================
-- SALES — RAW → REJECTED → CTRL (STG bypassed)
-- ============================================================================
-- Source: ORI_SALES_RAW | Rejected: ORI_SALES_REJECTED | CTRL: ORI_SALES_CTRL
-- Rules: 67 (72 UNION ALL branches — ERROR_INVALID_NULL covers 6 fields)
--
-- STG removed (identical DDL to CTRL); its anti-join is folded into STEP 5.
-- Regenerated by `validate-cross run`; change overrides/facts/sales.toml, not this.
--
-- Run order: SETUP → PHASE 1 → PHASE 2.
ALTER SESSION SET DDL_LOCK_TIMEOUT = 300;
-- ############################################################################
-- SETUP
-- ############################################################################
-- ============================================================================
-- STEP 1 — Validity materialization (RUN ONCE / when master data reloads)
-- ============================================================================
-- 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'
UNION
SELECT TO_CHAR(WH) FROM WH_CTRL
WHERE TO_CHAR(WH) = '546'
CREATE INDEX IX_DMF_VALIDITY__ORGANIZATION_ ON DMF_VALIDITY__ORGANIZATION_CSV (ORG_NUM);
SELECT 'DMF_VALIDITY__ITEM_VALID' AS dim, COUNT(*) FROM DMF_VALIDITY__ITEM_VALID;
SELECT 'DMF_VALIDITY__ORGANIZATION_CSV' AS dim, COUNT(*) FROM DMF_VALIDITY__ORGANIZATION_CSV;
-- ============================================================================
-- STEP 1b — Normalized source snapshot
-- ============================================================================
DROP TABLE DMF_SOURCE__SALES PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_SOURCE__SALES 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_RAW;
CREATE INDEX IX_DMF_SOURCE__SALES_RID ON DMF_SOURCE__SALES (ITEM, ORG_NUM, DAY_DT, MIN_NUM, RTL_TYPE_CODE, IT_SEQ_NUM, SLS_TRX_ID, VOUCHER_ID, CO_LINE_ID);
ALTER TABLE DMF_SOURCE__SALES NOPARALLEL;
SELECT COUNT(*) FROM DMF_SOURCE__SALES;
-- ============================================================================
-- STEP 1c — Duplicate-key set
-- ============================================================================
DROP TABLE DMF_DUPKEYS__SALES PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_DUPKEYS__SALES 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
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_KEY ON DMF_DUPKEYS__SALES (ITEM, ORG_NUM, DAY_DT, MIN_NUM, RTL_TYPE_CODE, IT_SEQ_NUM, SLS_TRX_ID, VOUCHER_ID, CO_LINE_ID);
SELECT COUNT(*) FROM DMF_DUPKEYS__SALES;
-- ############################################################################
-- PHASE 1 — RAW → REJECTED
-- ############################################################################
-- ============================================================================
-- STEP 2 — Ensure ORI_SALES_REJECTED exists, then empty it
-- ============================================================================
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ORI_SALES_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_REJECTED;
-- ============================================================================
-- STEP 3 — Unified UNION ALL INSERT
-- ============================================================================
-- Per-rule variant is in the APPENDIX; do NOT run it in the same pass.
INSERT INTO ORI_SALES_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
)
SELECT 'E', 'INVALID FORMAT: ITEM', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE ITEM IS NOT NULL AND LENGTHB(TO_CHAR(ITEM)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: ORG_NUM', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE ORG_NUM IS NOT NULL AND LENGTHB(TO_CHAR(ORG_NUM)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: DAY_DT', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE DAY_DT IS NOT NULL AND TRUNC(DAY_DT) <> DAY_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: MIN_NUM', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE SLS_TRX_ID IS NOT NULL AND LENGTHB(TO_CHAR(SLS_TRX_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: VOUCHER_ID', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE VOUCHER_ID IS NOT NULL AND LENGTHB(TO_CHAR(VOUCHER_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: PROMO_ID', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE PROMO_ID IS NOT NULL AND LENGTHB(TO_CHAR(PROMO_ID)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: PROMO_COMP_ID', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE ORIG_ORG_NUM IS NOT NULL AND LENGTHB(TO_CHAR(ORIG_ORG_NUM)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: CASHIER_ID', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE CASHIER_ID IS NOT NULL AND LENGTHB(TO_CHAR(CASHIER_ID)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: REGISTER_ID', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE REGISTER_ID IS NOT NULL AND LENGTHB(TO_CHAR(REGISTER_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: SALES_PERSON_ID', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE SALES_PERSON_ID IS NOT NULL AND LENGTHB(TO_CHAR(SALES_PERSON_ID)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: EMPLOYEE_NUM', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE EMPLOYEE_NUM IS NOT NULL AND LENGTHB(TO_CHAR(EMPLOYEE_NUM)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: CO_HEAD_ID', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE CO_CREATE_DT IS NOT NULL AND TRUNC(CO_CREATE_DT) <> CO_CREATE_DT
UNION ALL
SELECT 'E', 'INVALID FORMAT: CUSTOMER_NUM', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE CUSTOMER_NUM IS NOT NULL AND LENGTHB(TO_CHAR(CUSTOMER_NUM)) > 80
UNION ALL
SELECT 'E', 'INVALID FORMAT: CUSTOMER_TYPE', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE CUSTOMER_TYPE IS NOT NULL AND LENGTHB(TO_CHAR(CUSTOMER_TYPE)) > 6
UNION ALL
SELECT 'E', 'INVALID FORMAT: SLS_QTY', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE SALES_TYPE IS NOT NULL AND LENGTHB(TO_CHAR(SALES_TYPE)) > 1
UNION ALL
SELECT 'E', 'INVALID FORMAT: REVISION_NUM', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE RETURN_REASON_CODE IS NOT NULL AND LENGTHB(TO_CHAR(RETURN_REASON_CODE)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: RETURN_WH', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE RETURN_WH IS NOT NULL AND LENGTHB(TO_CHAR(RETURN_WH)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: POS_TRX_ID', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE POS_TRX_ID IS NOT NULL AND LENGTHB(TO_CHAR(POS_TRX_ID)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: RECEPT_IND', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE RECEPT_IND IS NOT NULL AND LENGTHB(TO_CHAR(RECEPT_IND)) > 1
UNION ALL
SELECT 'E', 'INVALID FORMAT: TAXABLE_IND', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE TAXABLE_IND IS NOT NULL AND LENGTHB(TO_CHAR(TAXABLE_IND)) > 1
UNION ALL
SELECT 'E', 'INVALID FORMAT: TRAN_PROCESS_SYS', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE TRAN_PROCESS_SYS IS NOT NULL AND LENGTHB(TO_CHAR(TRAN_PROCESS_SYS)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: TRAN_TYPE', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE TRAN_TYPE IS NOT NULL AND LENGTHB(TO_CHAR(TRAN_TYPE)) > 30
UNION ALL
SELECT 'E', 'INVALID FORMAT: LOC_CURR_CODE', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE FLEX17_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX17_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E', 'ITEM IS NULL', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE ITEM IS NULL
UNION ALL
SELECT 'E', 'ORG_NUM IS NULL', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE ORG_NUM IS NULL
UNION ALL
SELECT 'E', 'DAY_DT IS NULL', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE DAY_DT IS NULL
UNION ALL
SELECT 'E', 'MIN_NUM IS NULL', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE MIN_NUM IS NULL
UNION ALL
SELECT 'E', 'RTL_TYPE_CODE IS NULL', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE RTL_TYPE_CODE IS NULL
UNION ALL
SELECT 'E', 'SLS_TRX_ID IS NULL', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE SLS_TRX_ID IS NULL
UNION ALL
SELECT 'E', 'INVALID RTL_TYPE_CODE: NOT IN ALLOWED VALUES', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE RTL_TYPE_CODE NOT IN ('R', 'P', 'C')
UNION ALL
SELECT 'E', 'INVALID TRAN_TYPE: NOT IN ALLOWED VALUES', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE TRAN_TYPE NOT IN ('SALE', 'RETURN')
UNION ALL
SELECT 'W', 'INVALID SALES_TYPE: NOT IN ALLOWED VALUES', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE SALES_TYPE NOT IN ('I', 'E', 'R')
UNION ALL
SELECT 'W', 'INVALID TAXABLE_IND: NOT IN ALLOWED VALUES', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE TAXABLE_IND NOT IN ('Y', 'N')
UNION ALL
SELECT 'W', 'INVALID TRAN_PROCESS_SYS: NOT IN ALLOWED VALUES', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE TRAN_PROCESS_SYS NOT IN ('POS', 'OMS', 'SIM')
UNION ALL
SELECT 'E', 'ITEM NOT FOUND: ITEM_MASTER_CTRL', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES s WHERE TRAN_TYPE = 'SALE' AND (SLS_QTY < 0 OR SLS_AMT_LCL < 0)
UNION ALL
SELECT 'W', 'WARN_NEGATIVE_RETURNS', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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', 'MANUAL_RUN', '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 DMF_SOURCE__SALES 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);
COMMIT;
-- ############################################################################
-- PHASE 2 — REJECTED → CTRL (STG bypassed)
-- ############################################################################
-- ============================================================================
-- STEP 4 — Build ORI_SALES_CTRL
-- ============================================================================
DROP TABLE ORI_SALES_CTRL PURGE; -- ignore ORA-00942
CREATE TABLE ORI_SALES_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,
CO_LINE_FL_ID VARCHAR2(80 BYTE),
CO_LINE_FL_LOC_ID VARCHAR2(30 BYTE),
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_COUNT NUMBER(18,4),
SLS_MANUAL_MKDN_AMT_LCL NUMBER(20,4),
SLS_MANUAL_MKUP_AMT_LCL NUMBER(20,4),
SLS_SCAN_COUNT NUMBER(18,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_COUNT NUMBER(18,4),
RET_MANUAL_MKDN_AMT_LCL NUMBER(20,4),
RET_MANUAL_MKUP_AMT_LCL NUMBER(20,4),
RET_SCAN_COUNT NUMBER(18,4),
RET_TAX_AMT_LCL NUMBER(20,4),
RETPR_DISC_AMT_LCL NUMBER(20,4),
LIA_QTY NUMBER(18,4),
LIA_AMT_LCL NUMBER(20,4),
LIA_CAN_AMT_LCL NUMBER(20,4),
LIA_CAN_PROFIT_AMT_LCL NUMBER(20,4),
LIA_CAN_QTY NUMBER(18,4),
LIA_PROFIT_AMT_LCL NUMBER(20,4),
BANNER_NUM VARCHAR2(30 BYTE),
SALES_TYPE VARCHAR2(1 BYTE),
SELLING_ITEM_NUM VARCHAR2(25 BYTE),
REVISION_NUM NUMBER(3,0),
RETURN_DISPOSITION VARCHAR2(30 BYTE),
RETURN_REASON_CODE VARCHAR2(30 BYTE),
RETURN_WH VARCHAR2(30 BYTE),
POS_TRX_ID VARCHAR2(30 BYTE),
DROP_SHIP_IND VARCHAR2(1 BYTE),
NO_INV_RETURN_IND VARCHAR2(1 BYTE),
OVERRIDE_REASON VARCHAR2(30 BYTE),
PVOID_REG_ID VARCHAR2(30 BYTE),
PVOID_TRAN_NO VARCHAR2(30 BYTE),
REASON_CD VARCHAR2(30 BYTE),
RECEIPT_IND VARCHAR2(1 BYTE),
REF_ITEM VARCHAR2(30 BYTE),
REJECT_FLG CHAR(1 BYTE),
SUB_TRAN_TYPE VARCHAR2(30 BYTE),
TAXABLE_IND VARCHAR2(1 BYTE),
TRAN_PROCESS_SYS VARCHAR2(30 BYTE),
TRAN_TYPE VARCHAR2(30 BYTE),
VENDOR_NO VARCHAR2(30 BYTE),
LINE_MEDIA_NUM VARCHAR2(30 BYTE),
GLOBAL1_EXCHANGE_RATE NUMBER(22,7),
GLOBAL2_EXCHANGE_RATE NUMBER(22,7),
GLOBAL3_EXCHANGE_RATE NUMBER(22,7),
LOC_CURR_CODE VARCHAR2(30 BYTE),
LOC_EXCHANGE_RATE NUMBER(22,7),
DOC_CURR_CODE VARCHAR2(30 BYTE),
ETL_THREAD_VAL NUMBER(4,0),
DELETE_FLG CHAR(1 BYTE),
FLEX1_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX2_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX3_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX4_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX5_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX6_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),
FLEX11_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX12_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX13_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX14_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX15_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX16_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX17_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX18_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX19_CHAR_VALUE VARCHAR2(80 BYTE),
DATASOURCE_NUM_ID NUMBER(10,0),
FLEX1_NUM_VALUE NUMBER(18,4),
FLEX2_NUM_VALUE NUMBER(18,4),
FLEX3_NUM_VALUE NUMBER(18,4),
FLEX4_NUM_VALUE NUMBER(18,4),
FLEX5_NUM_VALUE NUMBER(18,4),
FLEX6_NUM_VALUE NUMBER(18,4),
FLEX7_NUM_VALUE NUMBER(18,4),
FLEX8_NUM_VALUE NUMBER(18,4),
FLEX9_NUM_VALUE NUMBER(18,4),
FLEX10_NUM_VALUE NUMBER(18,4),
FLEX11_NUM_VALUE NUMBER(18,4),
FLEX12_NUM_VALUE NUMBER(18,4),
FLEX13_NUM_VALUE NUMBER(18,4),
FLEX14_NUM_VALUE NUMBER(18,4),
FLEX15_NUM_VALUE NUMBER(18,4),
FLEX16_NUM_VALUE NUMBER(18,4),
FLEX17_NUM_VALUE NUMBER(18,4),
FLEX18_NUM_VALUE NUMBER(18,4),
FLEX19_NUM_VALUE NUMBER(18,4),
FLEX20_NUM_VALUE NUMBER(18,4),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 5 — Populate CTRL directly from DMF_SOURCE__SALES
-- ============================================================================
-- LOG_LVL='W' rows pass. The CASE guards are unreachable — the precision rules
-- already reject at the same thresholds in PHASE 1 — and are kept as emitted.
INSERT /*+ APPEND */ INTO ORI_SALES_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, CO_LINE_FL_ID, CO_LINE_FL_LOC_ID, CUSTOMER_NUM, CUSTOMER_TYPE, SLS_QTY, SLS_AMT_LCL, SLS_PROFIT_AMT_LCL, SLS_EMP_DISC_AMT_LCL, SLS_MANUAL_COUNT, SLS_MANUAL_MKDN_AMT_LCL, SLS_MANUAL_MKUP_AMT_LCL, SLS_SCAN_COUNT, SLS_TAX_AMT_LCL, SLSPR_DISC_AMT_LCL, RET_QTY, RET_AMT_LCL, RET_PROFIT_AMT_LCL, RET_EMP_DISC_AMT_LCL, RET_MANUAL_COUNT, RET_MANUAL_MKDN_AMT_LCL, RET_MANUAL_MKUP_AMT_LCL, RET_SCAN_COUNT, RET_TAX_AMT_LCL, RETPR_DISC_AMT_LCL, LIA_QTY, LIA_AMT_LCL, LIA_CAN_AMT_LCL, LIA_CAN_PROFIT_AMT_LCL, LIA_CAN_QTY, LIA_PROFIT_AMT_LCL, BANNER_NUM, SALES_TYPE, SELLING_ITEM_NUM, REVISION_NUM, RETURN_DISPOSITION, RETURN_REASON_CODE, RETURN_WH, POS_TRX_ID, DROP_SHIP_IND, NO_INV_RETURN_IND, OVERRIDE_REASON, PVOID_REG_ID, PVOID_TRAN_NO, REASON_CD, RECEIPT_IND, REF_ITEM, REJECT_FLG, SUB_TRAN_TYPE, TAXABLE_IND, TRAN_PROCESS_SYS, TRAN_TYPE, VENDOR_NO, LINE_MEDIA_NUM, GLOBAL1_EXCHANGE_RATE, GLOBAL2_EXCHANGE_RATE, GLOBAL3_EXCHANGE_RATE, LOC_CURR_CODE, LOC_EXCHANGE_RATE, DOC_CURR_CODE, ETL_THREAD_VAL, DELETE_FLG, FLEX1_CHAR_VALUE, FLEX2_CHAR_VALUE, FLEX3_CHAR_VALUE, FLEX4_CHAR_VALUE, FLEX5_CHAR_VALUE, FLEX6_CHAR_VALUE, FLEX7_CHAR_VALUE, FLEX8_CHAR_VALUE, FLEX9_CHAR_VALUE, FLEX10_CHAR_VALUE, FLEX11_CHAR_VALUE, FLEX12_CHAR_VALUE, FLEX13_CHAR_VALUE, FLEX14_CHAR_VALUE, FLEX15_CHAR_VALUE, FLEX16_CHAR_VALUE, FLEX17_CHAR_VALUE, FLEX18_CHAR_VALUE, FLEX19_CHAR_VALUE, DATASOURCE_NUM_ID, FLEX1_NUM_VALUE, FLEX2_NUM_VALUE, FLEX3_NUM_VALUE, FLEX4_NUM_VALUE, FLEX5_NUM_VALUE, FLEX6_NUM_VALUE, FLEX7_NUM_VALUE, FLEX8_NUM_VALUE, FLEX9_NUM_VALUE, FLEX10_NUM_VALUE, FLEX11_NUM_VALUE, FLEX12_NUM_VALUE, FLEX13_NUM_VALUE, FLEX14_NUM_VALUE, FLEX15_NUM_VALUE, FLEX16_NUM_VALUE, FLEX17_NUM_VALUE, FLEX18_NUM_VALUE, FLEX19_NUM_VALUE, FLEX20_NUM_VALUE, 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, CAST(NULL AS VARCHAR2(80 BYTE)), CAST(NULL AS VARCHAR2(30 BYTE)), 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, CAST(NULL AS NUMBER(18,4)), 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, CAST(NULL AS NUMBER(18,4)), 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, CAST(NULL AS NUMBER(18,4)), 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, CAST(NULL AS NUMBER(18,4)), 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, CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(20,4)), CAST(NULL AS VARCHAR2(30 BYTE)), s.SALES_TYPE, CAST(NULL AS VARCHAR2(25 BYTE)), CASE WHEN s.REVISION_NUM IS NULL OR ABS(TRUNC(s.REVISION_NUM)) < 1000 THEN s.REVISION_NUM ELSE NULL END, CAST(NULL AS VARCHAR2(30 BYTE)), s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, CAST(NULL AS VARCHAR2(1 BYTE)), CAST(NULL AS VARCHAR2(1 BYTE)), CAST(NULL AS VARCHAR2(30 BYTE)), CAST(NULL AS VARCHAR2(30 BYTE)), CAST(NULL AS VARCHAR2(30 BYTE)), CAST(NULL AS VARCHAR2(30 BYTE)), CAST(NULL AS VARCHAR2(1 BYTE)), CAST(NULL AS VARCHAR2(30 BYTE)), CAST(NULL AS CHAR(1 BYTE)), CAST(NULL AS VARCHAR2(30 BYTE)), s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, CAST(NULL AS VARCHAR2(30 BYTE)), CAST(NULL AS VARCHAR2(30 BYTE)), CAST(NULL AS NUMBER(22,7)), CAST(NULL AS NUMBER(22,7)), CAST(NULL AS NUMBER(22,7)), 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, CAST(NULL AS NUMBER(4,0)), CAST(NULL AS CHAR(1 BYTE)), s.FLEX1_CHAR_VALUE, CAST(NULL AS VARCHAR2(80 BYTE)), CAST(NULL AS VARCHAR2(80 BYTE)), CAST(NULL AS VARCHAR2(80 BYTE)), CAST(NULL AS VARCHAR2(80 BYTE)), CAST(NULL AS VARCHAR2(80 BYTE)), s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, CAST(NULL AS VARCHAR2(80 BYTE)), CAST(NULL AS VARCHAR2(80 BYTE)), CAST(NULL AS VARCHAR2(80 BYTE)), CAST(NULL AS VARCHAR2(80 BYTE)), CAST(NULL AS VARCHAR2(80 BYTE)), s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, CAST(NULL AS VARCHAR2(80 BYTE)), CAST(NULL AS VARCHAR2(80 BYTE)), CAST(NULL AS NUMBER(10,0)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), CAST(NULL AS NUMBER(18,4)), SYSDATE
FROM DMF_SOURCE__SALES s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_SALES_REJECTED r
WHERE r.VALIDATION_RUN_ID = 'MANUAL_RUN'
AND r.LOG_LVL = 'E'
AND r.SRC_ROWID = s.SRC_ROWID
)
AND s.ITEM IS NOT NULL
AND s.ORG_NUM IS NOT NULL
AND s.DAY_DT IS NOT NULL
AND s.MIN_NUM IS NOT NULL
AND s.RTL_TYPE_CODE IS NOT NULL
AND s.SLS_TRX_ID IS NOT NULL;
COMMIT;
SELECT COUNT(*) AS ctrl_rows FROM ORI_SALES_CTRL;
-- ############################################################################
-- FINAL — Rejection summary by rule
-- ############################################################################
SELECT RULE_ID, LOG_LVL, COUNT(*) AS rejected_rows
FROM ORI_SALES_REJECTED
GROUP BY RULE_ID, LOG_LVL
ORDER BY rejected_rows DESC;