Source: DMF/PuC/AIF/DR1 scripts/DMF_SALES_VALIDATIONS.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Truncate validation tables
------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRUNCATE TABLE ORI_SALES_REJECTED;
TRUNCATE TABLE ORI_SALES_TMP;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_SALES_REJECTED
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ORI_SALES_REJECTED (
LOG_LVL, ERROR_MSG,
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,
UPDATED_AT,
INSERTED_AT
)
WITH sls_hist as (SELECT ITEM,
LTRIM(ORG_NUM, '0') ORG_NUM, -- remove leading 0
DAY_DT,
SUBSTR(MIN_NUM,1,2) || SUBSTR(MIN_NUM,4,2) MIN_NUM, -- transform to HHMM format
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,
UPDATED_AT
FROM ORI_SALES_RAW),
---------------------------------------------------------------------------------
-- 1. a) RAP formatting rules
---------------------------------------------------------------------------------
error_rows_formating_rules AS (
-- Rows that violate formatting rules -- should be empty
SELECT 'E' AS LOG_LVL, 'ERROR_INVALID_RTL_TYPE_CODE' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM sls_hist s WHERE RTL_TYPE_CODE NOT IN ('R','P','C')
UNION ALL
SELECT 'E' AS LOG_LVL, 'ERROR_INVALID_TRAN_TYPE' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM sls_hist s WHERE TRAN_TYPE NOT IN ('SALE','RETURN')
UNION ALL
SELECT 'W' AS LOG_LVL, 'WARN_INVALID_SALES_TYPE' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM sls_hist s WHERE SALES_TYPE NOT IN ('I','E','R')
UNION ALL
SELECT 'W' AS LOG_LVL, 'WARN_INVALID_TAXABLE_IND' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM sls_hist s WHERE TAXABLE_IND NOT IN ('Y','N')
UNION ALL
SELECT 'W' AS LOG_LVL, 'WARN_INVALID_TRAN_PROCESS_SYS' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM sls_hist s WHERE TRAN_PROCESS_SYS NOT IN ('POS','OMS','SIM')
),
---------------------------------------------------------------------------------
-- 1. b) RAP formatting data types
---------------------------------------------------------------------------------
error_rows_formating_dtype AS (
-- Rows that violate RAP data types -- should be empty
SELECT 'E' AS LOG_LVL,'ERROR_DTYPE_ITEM' AS ERROR_MSG,s.*,SYSDATE AS INSERTED_AT FROM sls_hist s WHERE ITEM IS NOT NULL AND LENGTHB(TO_CHAR(ITEM)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_ORG_NUM',s.*,SYSDATE FROM sls_hist s WHERE ORG_NUM IS NOT NULL AND LENGTHB(TO_CHAR(ORG_NUM)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_DAY_DT',s.*,SYSDATE FROM sls_hist s WHERE DAY_DT IS NOT NULL AND TRUNC(DAY_DT) <> DAY_DT
UNION ALL
SELECT 'E','ERROR_DTYPE_MIN_NUM',s.*,SYSDATE FROM sls_hist s WHERE MIN_NUM IS NOT NULL AND (ABS(TO_NUMBER(MIN_NUM)) >= 10000 OR TO_NUMBER(MIN_NUM) != TRUNC(TO_NUMBER(MIN_NUM)))
UNION ALL
SELECT 'E','ERROR_DTYPE_IT_SEQ_NUM',s.*,SYSDATE FROM sls_hist s WHERE IT_SEQ_NUM IS NOT NULL AND (ABS(TO_NUMBER(IT_SEQ_NUM)) >= 10000 OR TO_NUMBER(IT_SEQ_NUM) != TRUNC(TO_NUMBER(IT_SEQ_NUM)))
UNION ALL
SELECT 'E','ERROR_DTYPE_RTL_TYPE_CODE',s.*,SYSDATE FROM sls_hist s WHERE RTL_TYPE_CODE IS NOT NULL AND LENGTHB(TO_CHAR(RTL_TYPE_CODE)) > 50
UNION ALL
SELECT 'E','ERROR_DTYPE_SLS_TRX_ID',s.*,SYSDATE FROM sls_hist s WHERE SLS_TRX_ID IS NOT NULL AND LENGTHB(TO_CHAR(SLS_TRX_ID)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_VOUCHER_ID',s.*,SYSDATE FROM sls_hist s WHERE VOUCHER_ID IS NOT NULL AND LENGTHB(TO_CHAR(VOUCHER_ID)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_PROMO_ID',s.*,SYSDATE FROM sls_hist s WHERE PROMO_ID IS NOT NULL AND LENGTHB(TO_CHAR(PROMO_ID)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_PROMO_COMP_ID',s.*,SYSDATE FROM sls_hist s WHERE PROMO_COMP_ID IS NOT NULL AND LENGTHB(TO_CHAR(PROMO_COMP_ID)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_ORIG_SLS_TRX_ID',s.*,SYSDATE FROM sls_hist s WHERE ORIG_SLS_TRX_ID IS NOT NULL AND LENGTHB(TO_CHAR(ORIG_SLS_TRX_ID)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_ORIG_ORG_NUM',s.*,SYSDATE FROM sls_hist s WHERE ORIG_ORG_NUM IS NOT NULL AND LENGTHB(TO_CHAR(ORIG_ORG_NUM)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_CASHIER_ID',s.*,SYSDATE FROM sls_hist s WHERE CASHIER_ID IS NOT NULL AND LENGTHB(TO_CHAR(CASHIER_ID)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_REGISTER_ID',s.*,SYSDATE FROM sls_hist s WHERE REGISTER_ID IS NOT NULL AND LENGTHB(TO_CHAR(REGISTER_ID)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_SALES_PERSON_ID',s.*,SYSDATE FROM sls_hist s WHERE SALES_PERSON_ID IS NOT NULL AND LENGTHB(TO_CHAR(SALES_PERSON_ID)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_EMPLOYEE_NUM',s.*,SYSDATE FROM sls_hist s WHERE EMPLOYEE_NUM IS NOT NULL AND LENGTHB(TO_CHAR(EMPLOYEE_NUM)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_CO_HEAD_ID',s.*,SYSDATE FROM sls_hist s WHERE CO_HEAD_ID IS NOT NULL AND LENGTHB(TO_CHAR(CO_HEAD_ID)) > 50
UNION ALL
SELECT 'E','ERROR_DTYPE_CO_LINE_ID',s.*,SYSDATE FROM sls_hist s WHERE CO_LINE_ID IS NOT NULL AND LENGTHB(TO_CHAR(CO_LINE_ID)) > 50
UNION ALL
SELECT 'E','ERROR_DTYPE_CO_CREATE_DT',s.*,SYSDATE FROM sls_hist s WHERE CO_CREATE_DT IS NOT NULL AND TRUNC(CO_CREATE_DT) <> CO_CREATE_DT
UNION ALL
SELECT 'E','ERROR_DTYPE_CUSTOMER_NUM',s.*,SYSDATE FROM sls_hist s WHERE CUSTOMER_NUM IS NOT NULL AND LENGTHB(TO_CHAR(CUSTOMER_NUM)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_CUSTOMER_TYPE',s.*,SYSDATE FROM sls_hist s WHERE CUSTOMER_TYPE IS NOT NULL AND LENGTHB(TO_CHAR(CUSTOMER_TYPE)) > 6
UNION ALL
SELECT 'E','ERROR_DTYPE_SLS_QTY',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_SLS_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_SLS_PROFIT_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_SLS_EMP_DISC_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_SLS_MANUAL_MKDN_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_SLS_MANUAL_MKUP_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_SLS_TAX_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_SLSPR_DISC_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_RET_QTY',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_RET_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_RET_PROFIT_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_RET_EMP_DISC_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_RET_MANUAL_MKDN_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_RET_MANUAL_MKUP_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_RET_TAX_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_RETPR_DISC_AMT_LCL',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_SALES_TYPE',s.*,SYSDATE FROM sls_hist s WHERE SALES_TYPE IS NOT NULL AND LENGTHB(TO_CHAR(SALES_TYPE)) > 1
UNION ALL
SELECT 'E','ERROR_DTYPE_REVISION_NUM',s.*,SYSDATE FROM sls_hist s WHERE REVISION_NUM IS NOT NULL AND (ABS(TO_NUMBER(REVISION_NUM)) >= 1000 OR TO_NUMBER(REVISION_NUM) != TRUNC(TO_NUMBER(REVISION_NUM)))
UNION ALL
SELECT 'E','ERROR_DTYPE_RETURN_REASON_CODE',s.*,SYSDATE FROM sls_hist s WHERE RETURN_REASON_CODE IS NOT NULL AND LENGTHB(TO_CHAR(RETURN_REASON_CODE)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_RETURN_WH',s.*,SYSDATE FROM sls_hist s WHERE RETURN_WH IS NOT NULL AND LENGTHB(TO_CHAR(RETURN_WH)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_POS_TRX_ID',s.*,SYSDATE FROM sls_hist s WHERE POS_TRX_ID IS NOT NULL AND LENGTHB(TO_CHAR(POS_TRX_ID)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_RECEIPT_IND',s.*,SYSDATE FROM sls_hist s WHERE RECEPT_IND IS NOT NULL AND LENGTHB(TO_CHAR(RECEPT_IND)) != 1
UNION ALL
SELECT 'E','ERROR_DTYPE_TAXABLE_IND',s.*,SYSDATE FROM sls_hist s WHERE TAXABLE_IND IS NOT NULL AND LENGTHB(TO_CHAR(TAXABLE_IND)) != 1
UNION ALL
SELECT 'E','ERROR_DTYPE_TRAN_PROCESS_SYS',s.*,SYSDATE FROM sls_hist s WHERE TRAN_PROCESS_SYS IS NOT NULL AND LENGTHB(TO_CHAR(TRAN_PROCESS_SYS)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_TRAN_TYPE',s.*,SYSDATE FROM sls_hist s WHERE TRAN_TYPE IS NOT NULL AND LENGTHB(TO_CHAR(TRAN_TYPE)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_LOC_CURR_CODE',s.*,SYSDATE FROM sls_hist s WHERE LOC_CURR_CODE IS NOT NULL AND LENGTHB(TO_CHAR(LOC_CURR_CODE)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_LOC_EXCHANGE_RATE',s.*,SYSDATE FROM sls_hist 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','ERROR_DTYPE_DOC_CURR_CODE',s.*,SYSDATE FROM sls_hist s WHERE DOC_CURR_CODE IS NOT NULL AND LENGTHB(TO_CHAR(DOC_CURR_CODE)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_FLEX1_CHAR_VALUE',s.*,SYSDATE FROM sls_hist s WHERE FLEX1_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX1_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_FLEX7_CHAR_VALUE',s.*,SYSDATE FROM sls_hist s WHERE FLEX7_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX7_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_FLEX8_CHAR_VALUE',s.*,SYSDATE FROM sls_hist s WHERE FLEX8_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX8_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_FLEX9_CHAR_VALUE',s.*,SYSDATE FROM sls_hist s WHERE FLEX9_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX9_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_FLEX10_CHAR_VALUE',s.*,SYSDATE FROM sls_hist s WHERE FLEX10_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX10_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_FLEX16_CHAR_VALUE',s.*,SYSDATE FROM sls_hist s WHERE FLEX16_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX16_CHAR_VALUE)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_FLEX17_CHAR_VALUE',s.*,SYSDATE FROM sls_hist s WHERE FLEX17_CHAR_VALUE IS NOT NULL AND LENGTHB(TO_CHAR(FLEX17_CHAR_VALUE)) > 80
),
---------------------------------------------------------------------------------
-- 2. Validate mandatory fields are not null
---------------------------------------------------------------------------------
error_rows_null_fields AS (
SELECT 'E' AS LOG_LVL, 'ERROR_INVALID_NULL' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM sls_hist s
WHERE ITEM IS NULL OR ORG_NUM IS NULL OR DAY_DT IS NULL OR MIN_NUM IS NULL OR RTL_TYPE_CODE IS NULL OR SLS_TRX_ID IS NULL
),
---------------------------------------------------------------------------------
-- 3. Validate duplicate keys
---------------------------------------------------------------------------------
duplicate_keys as (
SELECT ITEM, ORG_NUM, DAY_DT, MIN_NUM, RTL_TYPE_CODE,IT_SEQ_NUM, SLS_TRX_ID, VOUCHER_ID, CO_LINE_ID, COUNT(*) AS duplicate_cnt
FROM sls_hist
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
),
error_rows_duplicate_keys AS (
SELECT
'E' AS LOG_LVL,
'ERROR_DUPLICATE_KEY' AS ERROR_MSG,
s.*,
SYSDATE AS INSERTED_AT
FROM sls_hist s
JOIN duplicate_keys d
ON NVL(s.ITEM, '-1') = NVL(d.ITEM, '-1')
AND NVL(s.ORG_NUM, '-1') = NVL(d.ORG_NUM, '-1')
AND NVL(s.DAY_DT, DATE '1900-01-01') = NVL(d.DAY_DT, DATE '1900-01-01')
AND NVL(s.MIN_NUM, '-1') = NVL(d.MIN_NUM, '-1')
AND NVL(s.RTL_TYPE_CODE, 'X')= NVL(d.RTL_TYPE_CODE, 'X')
AND NVL(s.IT_SEQ_NUM, -1) = NVL(d.IT_SEQ_NUM, -1)
AND NVL(s.SLS_TRX_ID, -1) = NVL(d.SLS_TRX_ID, -1)
AND NVL(s.VOUCHER_ID, -1) = NVL(d.VOUCHER_ID, -1)
AND NVL(s.CO_LINE_ID, '-1') = NVL(d.CO_LINE_ID, '-1')
-- ORDER BY ITEM, ORG_NUM, DAY_DT, MIN_NUM, RTL_TYPE_CODE, SLS_TRX_ID, CO_LINE_ID, IT_SEQ_NUM
),
---------------------------------------------------------------------------------
-- 4. a) Validate negative sales
---------------------------------------------------------------------------------
error_rows_negative_sales AS (
SELECT 'W' AS LOG_LVL, 'WARN_NEGATIVE_SALES' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM sls_hist s
WHERE TRAN_TYPE = 'SALE' AND (s.SLS_QTY< 0 OR s.SLS_AMT_LCL< 0)
),
---------------------------------------------------------------------------------
-- 4. b) Validate negative returns
---------------------------------------------------------------------------------
error_rows_negative_returns AS (
SELECT 'W' AS LOG_LVL, 'WARN_NEGATIVE_RETURNS' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM sls_hist s
WHERE TRAN_TYPE = 'RETURN' AND (s.RET_QTY< 0 OR s.RET_AMT_LCL< 0)
),
---------------------------------------------------------------------------------
-- 5. a) Validate items exist in master data
---------------------------------------------------------------------------------
error_rows_missing_items AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_ITEM' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM sls_hist s
WHERE s.ITEM NOT IN (SELECT ITEM FROM ITEM_MASTER_CTRL WHERE ctrl_status = 'C')
),
---------------------------------------------------------------------------------
-- 5. b) Validate stores exist in master data
---------------------------------------------------------------------------------
error_rows_missing_stores AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_STORE' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM sls_hist s
WHERE s.ORG_NUM NOT IN (SELECT TO_CHAR(STORE) FROM STORE_ADD_CTRL WHERE ctrl_status = 'C')
),
---------------------------------------------------------------------------------
-- 6. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT * FROM error_rows_formating_rules
UNION ALL
SELECT * FROM error_rows_formating_dtype
UNION ALL
SELECT * FROM error_rows_null_fields
UNION ALL
SELECT * FROM error_rows_duplicate_keys
UNION ALL
SELECT * FROM error_rows_negative_sales
UNION ALL
SELECT * FROM error_rows_negative_returns
UNION ALL
SELECT * FROM error_rows_missing_items
UNION ALL
SELECT * FROM error_rows_missing_stores
)
SELECT * FROM all_error_rows;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_SALES_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_SALES_TMP
INSERT INTO ORI_SALES_TMP
WITH sls_hist as (SELECT ITEM,
LTRIM(ORG_NUM, '0') ORG_NUM, -- remove leading 0
DAY_DT,
SUBSTR(MIN_NUM,1,2) || SUBSTR(MIN_NUM,4,2) MIN_NUM, -- transform to HHMM format
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,
UPDATED_AT
FROM ORI_SALES_RAW)
SELECT s.*, SYSDATE AS INSERTED_AT
FROM sls_hist s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_SALES_REJECTED r
WHERE r.LOG_LVL = 'E'
AND NVL(s.ITEM, -1) = NVL(r.ITEM, -1)
AND NVL(s.ORG_NUM, -1) = NVL(r.ORG_NUM, -1)
AND NVL(s.DAY_DT, DATE '1900-01-01') = NVL(r.DAY_DT, DATE '1900-01-01')
AND NVL(s.MIN_NUM, -1) = NVL(r.MIN_NUM, -1)
AND NVL(s.RTL_TYPE_CODE, 'X') = NVL(r.RTL_TYPE_CODE, 'X')
AND NVL(s.SLS_TRX_ID, -1) = NVL(r.SLS_TRX_ID, -1)
AND NVL(s.VOUCHER_ID, -1) = NVL(r.VOUCHER_ID, -1)
AND NVL(s.CO_LINE_ID, -1) = NVL(r.CO_LINE_ID, -1)
);
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- REJECTION SUMMARY
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Retrieve a summary by error type
WITH rejected AS (
SELECT LOG_LVL, ERROR_MSG,
COUNT(*) AS NUM_RECORDS, COUNT(DISTINCT item) AS NUM_ITEMS,
SUM(SLS_QTY) AS SLS_QTY, SUM(SLS_AMT_LCL) AS SLS_AMT_LCL,
SUM(RET_QTY) AS RET_QTY, SUM(RET_AMT_LCL) AS RET_AMT_LCL
FROM ORI_SALES_REJECTED
GROUP BY LOG_LVL, ERROR_MSG
),
totals AS (
SELECT COUNT(*) AS TOTAL_RECORDS, COUNT(DISTINCT item) AS TOTAL_ITEMS,
SUM(SLS_QTY) AS TOTAL_SLS_QTY, SUM(SLS_AMT_LCL) AS TOTAL_SLS_AMT_LCL,
SUM(RET_QTY) AS TOTAL_RET_QTY, SUM(RET_AMT_LCL) AS TOTAL_RET_AMT_LCL
FROM ORI_SALES_RAW
),
all_sales AS (
SELECT ' ' AS LOG_LVL, 'ALL SALES' AS ERROR_MSG,
COUNT(*) AS NUM_RECORDS, COUNT(DISTINCT item) AS NUM_ITEMS,
SUM(SLS_QTY) AS SLS_QTY, SUM(SLS_AMT_LCL) AS SLS_AMT_LCL,
SUM(RET_QTY) AS RET_QTY, SUM(RET_AMT_LCL) AS RET_AMT_LCL
FROM ORI_SALES_RAW
),
passed_sales AS (
SELECT ' ' AS LOG_LVL, 'VALID SALES' AS ERROR_MSG,
COUNT(*) AS NUM_RECORDS, COUNT(DISTINCT item) AS NUM_ITEMS,
SUM(SLS_QTY) AS SLS_QTY, SUM(SLS_AMT_LCL) AS SLS_AMT_LCL,
SUM(RET_QTY) AS RET_QTY, SUM(RET_AMT_LCL) AS RET_AMT_LCL
FROM ORI_SALES_TMP
)
SELECT r.LOG_LVL, r.ERROR_MSG,
r.NUM_RECORDS, ROUND(r.NUM_RECORDS / t.TOTAL_RECORDS * 100, 2) AS PCT_RECORDS,
r.NUM_ITEMS, ROUND(r.NUM_ITEMS / t.TOTAL_ITEMS * 100, 2) AS PCT_ITEMS,
r.SLS_QTY, ROUND(r.SLS_QTY / t.TOTAL_SLS_QTY * 100, 2) AS PCT_SLS_QTY,
r.SLS_AMT_LCL, ROUND(r.SLS_AMT_LCL / t.TOTAL_SLS_AMT_LCL * 100, 2) AS PCT_SLS_AMT_LCL,
r.RET_QTY, ROUND(r.RET_QTY / t.TOTAL_RET_QTY * 100, 2) AS PCT_RET_QTY,
r.RET_AMT_LCL, ROUND(r.RET_AMT_LCL / t.TOTAL_RET_AMT_LCL * 100, 2) AS PCT_RET_AMT_LCL
FROM rejected r
CROSS JOIN totals t
UNION ALL
SELECT a.LOG_LVL, a.ERROR_MSG,
a.NUM_RECORDS, 100 AS PCT_RECORDS,
a.NUM_ITEMS, 100 AS PCT_ITEMS,
a.SLS_QTY, 100 AS PCT_SLS_QTY,
a.SLS_AMT_LCL, 100 AS PCT_SLS_AMT_LCL,
a.RET_QTY, 100 AS PCT_RET_QTY,
a.RET_AMT_LCL, 100 AS PCT_RET_AMT_LCL
FROM all_sales a
UNION ALL
SELECT r.LOG_LVL, r.ERROR_MSG,
r.NUM_RECORDS, ROUND(r.NUM_RECORDS / t.TOTAL_RECORDS * 100, 2) AS PCT_RECORDS,
r.NUM_ITEMS, ROUND(r.NUM_ITEMS / t.TOTAL_ITEMS * 100, 2) AS PCT_ITEMS,
r.SLS_QTY, ROUND(r.SLS_QTY / t.TOTAL_SLS_QTY * 100, 2) AS PCT_SLS_QTY,
r.SLS_AMT_LCL, ROUND(r.SLS_AMT_LCL / t.TOTAL_SLS_AMT_LCL * 100, 2) AS PCT_SLS_AMT_LCL,
r.RET_QTY, ROUND(r.RET_QTY / t.TOTAL_RET_QTY * 100, 2) AS PCT_RET_QTY,
r.RET_AMT_LCL, ROUND(r.RET_AMT_LCL / t.TOTAL_RET_AMT_LCL * 100, 2) AS PCT_RET_AMT_LCL
FROM passed_sales r
CROSS JOIN totals t
ORDER BY LOG_LVL ASC, NUM_RECORDS DESC;