Source: DMF/PuC/AIF/FelipeBkp/sales.sql
-- ============================================================================
-- SALES — Full Validation Pipeline
-- ============================================================================
-- Fact: sales
-- Source: ORI_SALES_REGULAR_RAW
-- BQ source: `puc-p-dataf-common`.export_oracle_migration.migration_sales_regular_hist
-- Rejected: ORI_SALES_REJECTED
-- STG (accepted): ORI_SALES_STG
-- Rules: 67
-- Pipeline: RAW → REJECTED → STG → CTRL
--
-- How to run in SQL Developer:
-- Open this file → F5 (Run as Script)
-- Each step can also be highlighted and run independently with F9.
--
-- Prerequisites:
-- XREF_ITEM_IBC_RETAIL built by ../ItemPR1_PV1.sql
--
-- Collapsed from phase1/phase2/phase3 playbooks generated 2026-06-07T17:56:07.
-- Phase 3 contained phases 1 and 2 verbatim; the per-rule DIAGNOSTIC
-- ALTERNATIVE block duplicated the unified INSERT above it and was removed.
-- ============================================================================
ALTER SESSION SET DDL_LOCK_TIMEOUT = 300;
-- ============================================================================
-- STEP 1 — Rebuild validity materialization tables (run once / when stale)
-- ============================================================================
-- The framework caches these tables across runs (the underlying
-- master-data joins are expensive but the master data changes rarely).
-- ONLY run this block when:
-- • first build on a fresh schema,
-- • the [dimensions.<name>] validity_query was edited,
-- • the master-data tables were reloaded.
-- Dimension 'item_valid' → DMF_VALIDITY__ITEM_VALID
DROP TABLE DMF_VALIDITY__ITEM_VALID PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_VALIDITY__ITEM_VALID AS
SELECT DISTINCT imc.item AS item
FROM item_master_ctrl imc
WHERE imc.ctrl_status = 'C'
UNION
SELECT DISTINCT xi.item_pr1 AS item
FROM item_master_ctrl imc
JOIN XREF_ITEM_IBC_RETAIL xi ON xi.item_pv1 = imc.item
WHERE imc.ctrl_status = 'C'
AND xi.item_pr1 IS NOT NULL;
CREATE INDEX IX_DMF_VALIDITY__ITEM_VALID ON DMF_VALIDITY__ITEM_VALID (ITEM);
-- Dimension 'organization_csv' → DMF_VALIDITY__ORGANIZATION_CSV
DROP TABLE DMF_VALIDITY__ORGANIZATION_CSV PURGE; -- ignore ORA-00942 on first run
CREATE TABLE DMF_VALIDITY__ORGANIZATION_CSV AS
SELECT DISTINCT TO_CHAR(STORE) AS org_num
FROM STORE_ADD_CTRL
WHERE ctrl_status = 'C';
CREATE INDEX IX_DMF_VALIDITY__ORGANIZATION_ ON DMF_VALIDITY__ORGANIZATION_CSV (ORG_NUM);
-- Verify the validity tables populated:
SELECT 'DMF_VALIDITY__ITEM_VALID' AS dim, COUNT(*) FROM DMF_VALIDITY__ITEM_VALID;
SELECT 'DMF_VALIDITY__ORGANIZATION_CSV' AS dim, COUNT(*) FROM DMF_VALIDITY__ORGANIZATION_CSV;
-- ============================================================================
-- STEP 2 — Materialize normalized source into DMF_SOURCE__SALES
-- ============================================================================
-- One scan of RAW now → all subsequent rules read from a smaller, hotter,
-- indexed snapshot. Drops the per-rule N-scan cost on wide RAW tables.
DROP TABLE DMF_SOURCE__SALES 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_REGULAR_RAW;
-- Index on the row-identity tuple makes duplicate_key and the STG
-- anti-join cheap.
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);
-- Reset parallel attribute so per-rule reads don't auto-parallelize.
ALTER TABLE DMF_SOURCE__SALES NOPARALLEL;
-- Sanity check:
SELECT COUNT(*) FROM DMF_SOURCE__SALES;
-- ============================================================================
-- STEP 3 — Materialize duplicate-key set into DMF_DUPKEYS__SALES
-- ============================================================================
-- One GROUP BY HAVING over the source now → the duplicate_key rule
-- becomes a cheap semi-join against this small indexed table.
DROP TABLE DMF_DUPKEYS__SALES 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);
-- Sanity check (size of the conflict set):
SELECT COUNT(*) FROM DMF_DUPKEYS__SALES;
-- ============================================================================
-- STEP 4 — 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;
-- If TRUNCATE fails with ORA-00054 (orphan lock), use DELETE instead:
-- DELETE FROM ORI_SALES_REJECTED;
-- COMMIT;
-- ============================================================================
-- STEP 5 — Unified UNION ALL INSERT (single source scan)
-- ============================================================================
-- One INSERT covers every rule via UNION ALL. With the source CTE
-- materialized into DMF_SOURCE__<fact>, Oracle scans the snapshot once
-- and evaluates each rule's predicate against the buffered table.
INSERT INTO ORI_SALES_REJECTED (
LOG_LVL, ERROR_MSG, VALIDATION_RUN_ID, RULE_ID, SRC_ROWID, UPDATED_AT, ITEM, ORG_NUM, DAY_DT, MIN_NUM, IT_SEQ_NUM, RTL_TYPE_CODE, SLS_TRX_ID, VOUCHER_ID, PROMO_ID, PROMO_COMP_ID, ORIG_SLS_TRX_ID, ORIG_ORG_NUM, CASHIER_ID, REGISTER_ID, SALES_PERSON_ID, EMPLOYEE_NUM, CO_HEAD_ID, CO_LINE_ID, CO_CREATE_DT, CUSTOMER_NUM, CUSTOMER_TYPE, SLS_QTY, SLS_AMT_LCL, SLS_PROFIT_AMT_LCL, SLS_EMP_DISC_AMT_LCL, SLS_MANUAL_MKDN_AMT_LCL, SLS_MANUAL_MKUP_AMT_LCL, SLS_TAX_AMT_LCL, SLSPR_DISC_AMT_LCL, RET_QTY, RET_AMT_LCL, RET_PROFIT_AMT_LCL, RET_EMP_DISC_AMT_LCL, RET_MANUAL_MKDN_AMT_LCL, RET_MANUAL_MKUP_AMT_LCL, RET_TAX_AMT_LCL, RETPR_DISC_AMT_LCL, SALES_TYPE, REVISION_NUM, RETURN_REASON_CODE, RETURN_WH, POS_TRX_ID, RECEPT_IND, TAXABLE_IND, TRAN_PROCESS_SYS, TRAN_TYPE, LOC_CURR_CODE, LOC_EXCHANGE_RATE, DOC_CURR_CODE, FLEX1_CHAR_VALUE, FLEX7_CHAR_VALUE, FLEX8_CHAR_VALUE, FLEX9_CHAR_VALUE, FLEX10_CHAR_VALUE, FLEX16_CHAR_VALUE, FLEX17_CHAR_VALUE, INSERTED_AT
)
WITH s AS (
SELECT
SRC_ROWID,
UPDATED_AT,
ITEM,
ORG_NUM,
DAY_DT,
MIN_NUM,
IT_SEQ_NUM,
RTL_TYPE_CODE,
SLS_TRX_ID,
VOUCHER_ID,
PROMO_ID,
PROMO_COMP_ID,
ORIG_SLS_TRX_ID,
ORIG_ORG_NUM,
CASHIER_ID,
REGISTER_ID,
SALES_PERSON_ID,
EMPLOYEE_NUM,
CO_HEAD_ID,
CO_LINE_ID,
CO_CREATE_DT,
CUSTOMER_NUM,
CUSTOMER_TYPE,
SLS_QTY,
SLS_AMT_LCL,
SLS_PROFIT_AMT_LCL,
SLS_EMP_DISC_AMT_LCL,
SLS_MANUAL_MKDN_AMT_LCL,
SLS_MANUAL_MKUP_AMT_LCL,
SLS_TAX_AMT_LCL,
SLSPR_DISC_AMT_LCL,
RET_QTY,
RET_AMT_LCL,
RET_PROFIT_AMT_LCL,
RET_EMP_DISC_AMT_LCL,
RET_MANUAL_MKDN_AMT_LCL,
RET_MANUAL_MKUP_AMT_LCL,
RET_TAX_AMT_LCL,
RETPR_DISC_AMT_LCL,
SALES_TYPE,
REVISION_NUM,
RETURN_REASON_CODE,
RETURN_WH,
POS_TRX_ID,
RECEPT_IND,
TAXABLE_IND,
TRAN_PROCESS_SYS,
TRAN_TYPE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
DOC_CURR_CODE,
FLEX1_CHAR_VALUE,
FLEX7_CHAR_VALUE,
FLEX8_CHAR_VALUE,
FLEX9_CHAR_VALUE,
FLEX10_CHAR_VALUE,
FLEX16_CHAR_VALUE,
FLEX17_CHAR_VALUE
FROM DMF_SOURCE__SALES
)
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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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;
-- ============================================================================
-- STEP 6 — Build typed ORI_SALES_STG (drop-and-recreate)
-- ============================================================================
-- DDL types are pulled from the RAP spec (not VARCHAR2(4000) blanket).
DROP TABLE ORI_SALES_STG PURGE; -- ignore ORA-00942
CREATE TABLE ORI_SALES_STG (
UPDATED_AT VARCHAR2(4000),
ITEM VARCHAR2(80 BYTE),
ORG_NUM VARCHAR2(80 BYTE),
DAY_DT DATE,
MIN_NUM NUMBER(4,0),
IT_SEQ_NUM NUMBER(4,0),
RTL_TYPE_CODE VARCHAR2(50 BYTE),
SLS_TRX_ID VARCHAR2(30 BYTE),
VOUCHER_ID VARCHAR2(30 BYTE),
PROMO_ID VARCHAR2(80 BYTE),
PROMO_COMP_ID VARCHAR2(30 BYTE),
ORIG_SLS_TRX_ID VARCHAR2(30 BYTE),
ORIG_ORG_NUM VARCHAR2(80 BYTE),
CASHIER_ID VARCHAR2(80 BYTE),
REGISTER_ID VARCHAR2(30 BYTE),
SALES_PERSON_ID VARCHAR2(80 BYTE),
EMPLOYEE_NUM VARCHAR2(80 BYTE),
CO_HEAD_ID VARCHAR2(50 BYTE),
CO_LINE_ID VARCHAR2(50 BYTE),
CO_CREATE_DT DATE,
CUSTOMER_NUM VARCHAR2(80 BYTE),
CUSTOMER_TYPE VARCHAR2(6 BYTE),
SLS_QTY NUMBER(18,4),
SLS_AMT_LCL NUMBER(20,4),
SLS_PROFIT_AMT_LCL NUMBER(20,4),
SLS_EMP_DISC_AMT_LCL NUMBER(20,4),
SLS_MANUAL_MKDN_AMT_LCL NUMBER(20,4),
SLS_MANUAL_MKUP_AMT_LCL NUMBER(20,4),
SLS_TAX_AMT_LCL NUMBER(20,4),
SLSPR_DISC_AMT_LCL NUMBER(20,4),
RET_QTY NUMBER(18,4),
RET_AMT_LCL NUMBER(20,4),
RET_PROFIT_AMT_LCL NUMBER(20,4),
RET_EMP_DISC_AMT_LCL NUMBER(20,4),
RET_MANUAL_MKDN_AMT_LCL NUMBER(20,4),
RET_MANUAL_MKUP_AMT_LCL NUMBER(20,4),
RET_TAX_AMT_LCL NUMBER(20,4),
RETPR_DISC_AMT_LCL NUMBER(20,4),
SALES_TYPE VARCHAR2(1 BYTE),
REVISION_NUM NUMBER(3,0),
RETURN_REASON_CODE VARCHAR2(30 BYTE),
RETURN_WH VARCHAR2(30 BYTE),
POS_TRX_ID VARCHAR2(30 BYTE),
RECEPT_IND VARCHAR2(4000),
TAXABLE_IND VARCHAR2(1 BYTE),
TRAN_PROCESS_SYS VARCHAR2(30 BYTE),
TRAN_TYPE VARCHAR2(30 BYTE),
LOC_CURR_CODE VARCHAR2(30 BYTE),
LOC_EXCHANGE_RATE NUMBER(22,7),
DOC_CURR_CODE VARCHAR2(30 BYTE),
FLEX1_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX7_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX8_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX9_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX10_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX16_CHAR_VALUE VARCHAR2(80 BYTE),
FLEX17_CHAR_VALUE VARCHAR2(80 BYTE),
INSERTED_AT TIMESTAMP(6)
);
-- ============================================================================
-- STEP 7 — Populate ORI_SALES_STG (anti-join on SRC_ROWID; LOG_LVL='W' rows still flow through)
-- ============================================================================
INSERT INTO ORI_SALES_STG (UPDATED_AT, ITEM, ORG_NUM, DAY_DT, MIN_NUM, IT_SEQ_NUM, RTL_TYPE_CODE, SLS_TRX_ID, VOUCHER_ID, PROMO_ID, PROMO_COMP_ID, ORIG_SLS_TRX_ID, ORIG_ORG_NUM, CASHIER_ID, REGISTER_ID, SALES_PERSON_ID, EMPLOYEE_NUM, CO_HEAD_ID, CO_LINE_ID, CO_CREATE_DT, CUSTOMER_NUM, CUSTOMER_TYPE, SLS_QTY, SLS_AMT_LCL, SLS_PROFIT_AMT_LCL, SLS_EMP_DISC_AMT_LCL, SLS_MANUAL_MKDN_AMT_LCL, SLS_MANUAL_MKUP_AMT_LCL, SLS_TAX_AMT_LCL, SLSPR_DISC_AMT_LCL, RET_QTY, RET_AMT_LCL, RET_PROFIT_AMT_LCL, RET_EMP_DISC_AMT_LCL, RET_MANUAL_MKDN_AMT_LCL, RET_MANUAL_MKUP_AMT_LCL, RET_TAX_AMT_LCL, RETPR_DISC_AMT_LCL, SALES_TYPE, REVISION_NUM, RETURN_REASON_CODE, RETURN_WH, POS_TRX_ID, RECEPT_IND, TAXABLE_IND, TRAN_PROCESS_SYS, TRAN_TYPE, LOC_CURR_CODE, LOC_EXCHANGE_RATE, DOC_CURR_CODE, FLEX1_CHAR_VALUE, FLEX7_CHAR_VALUE, FLEX8_CHAR_VALUE, FLEX9_CHAR_VALUE, FLEX10_CHAR_VALUE, FLEX16_CHAR_VALUE, FLEX17_CHAR_VALUE, INSERTED_AT)
WITH s AS (
SELECT
SRC_ROWID,
UPDATED_AT,
ITEM,
ORG_NUM,
DAY_DT,
MIN_NUM,
IT_SEQ_NUM,
RTL_TYPE_CODE,
SLS_TRX_ID,
VOUCHER_ID,
PROMO_ID,
PROMO_COMP_ID,
ORIG_SLS_TRX_ID,
ORIG_ORG_NUM,
CASHIER_ID,
REGISTER_ID,
SALES_PERSON_ID,
EMPLOYEE_NUM,
CO_HEAD_ID,
CO_LINE_ID,
CO_CREATE_DT,
CUSTOMER_NUM,
CUSTOMER_TYPE,
SLS_QTY,
SLS_AMT_LCL,
SLS_PROFIT_AMT_LCL,
SLS_EMP_DISC_AMT_LCL,
SLS_MANUAL_MKDN_AMT_LCL,
SLS_MANUAL_MKUP_AMT_LCL,
SLS_TAX_AMT_LCL,
SLSPR_DISC_AMT_LCL,
RET_QTY,
RET_AMT_LCL,
RET_PROFIT_AMT_LCL,
RET_EMP_DISC_AMT_LCL,
RET_MANUAL_MKDN_AMT_LCL,
RET_MANUAL_MKUP_AMT_LCL,
RET_TAX_AMT_LCL,
RETPR_DISC_AMT_LCL,
SALES_TYPE,
REVISION_NUM,
RETURN_REASON_CODE,
RETURN_WH,
POS_TRX_ID,
RECEPT_IND,
TAXABLE_IND,
TRAN_PROCESS_SYS,
TRAN_TYPE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
DOC_CURR_CODE,
FLEX1_CHAR_VALUE,
FLEX7_CHAR_VALUE,
FLEX8_CHAR_VALUE,
FLEX9_CHAR_VALUE,
FLEX10_CHAR_VALUE,
FLEX16_CHAR_VALUE,
FLEX17_CHAR_VALUE
FROM DMF_SOURCE__SALES
)
SELECT s.UPDATED_AT, s.ITEM, s.ORG_NUM, s.DAY_DT, s.MIN_NUM, s.IT_SEQ_NUM, s.RTL_TYPE_CODE, s.SLS_TRX_ID, s.VOUCHER_ID, s.PROMO_ID, s.PROMO_COMP_ID, s.ORIG_SLS_TRX_ID, s.ORIG_ORG_NUM, s.CASHIER_ID, s.REGISTER_ID, s.SALES_PERSON_ID, s.EMPLOYEE_NUM, s.CO_HEAD_ID, s.CO_LINE_ID, s.CO_CREATE_DT, s.CUSTOMER_NUM, s.CUSTOMER_TYPE, s.SLS_QTY, s.SLS_AMT_LCL, s.SLS_PROFIT_AMT_LCL, s.SLS_EMP_DISC_AMT_LCL, s.SLS_MANUAL_MKDN_AMT_LCL, s.SLS_MANUAL_MKUP_AMT_LCL, s.SLS_TAX_AMT_LCL, s.SLSPR_DISC_AMT_LCL, s.RET_QTY, s.RET_AMT_LCL, s.RET_PROFIT_AMT_LCL, s.RET_EMP_DISC_AMT_LCL, s.RET_MANUAL_MKDN_AMT_LCL, s.RET_MANUAL_MKUP_AMT_LCL, s.RET_TAX_AMT_LCL, s.RETPR_DISC_AMT_LCL, s.SALES_TYPE, s.REVISION_NUM, s.RETURN_REASON_CODE, s.RETURN_WH, s.POS_TRX_ID, s.RECEPT_IND, s.TAXABLE_IND, s.TRAN_PROCESS_SYS, s.TRAN_TYPE, s.LOC_CURR_CODE, s.LOC_EXCHANGE_RATE, s.DOC_CURR_CODE, s.FLEX1_CHAR_VALUE, s.FLEX7_CHAR_VALUE, s.FLEX8_CHAR_VALUE, s.FLEX9_CHAR_VALUE, s.FLEX10_CHAR_VALUE, s.FLEX16_CHAR_VALUE, s.FLEX17_CHAR_VALUE, SYSDATE
FROM s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_SALES_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;
-- Row count check: should be COUNT(ORI_SALES_REGULAR_RAW) minus distinct SRC_ROWIDs in ORI_SALES_REJECTED where LOG_LVL='E'.
SELECT COUNT(*) AS stg_rows FROM ORI_SALES_STG;
-- ============================================================================
-- STEP 8 — Build ORI_SALES_CTRL (drop-and-recreate)
-- ============================================================================
-- Final Oracle export shape. Column projection comes from
-- `[ctrl_projection]` in the override; default = identity from STG.
DROP TABLE ORI_SALES_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 9 — Populate ORI_SALES_CTRL from ORI_SALES_STG
-- ============================================================================
INSERT 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 ORI_SALES_STG s;
COMMIT;
SELECT COUNT(*) AS ctrl_rows FROM ORI_SALES_CTRL;
-- ============================================================================
-- STEP 10 — 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;