Source: DMF/PuC/AIF/DR1 scripts/DMF_INVENTORY_VALIDATIONS.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Truncate validation tables
------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRUNCATE TABLE ORI_INVENTORY_REJECTED;
TRUNCATE TABLE ORI_INVENTORY_TMP;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_INVENTORY_REJECTED
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ORI_INVENTORY_REJECTED (
LOG_LVL, ERROR_MSG,
ITEM, ORG_NUM, DAY_DT, CLEARANCE_FLG,
INV_SOH_QTY, INV_SOH_COST_AMT_LCL, INV_SOH_RTL_AMT_LCL, INV_UNIT_RTL_AMT_LCL, INV_AVG_COST_AMT_LCL,
DOC_CURR_CODE, LOC_CURR_CODE, UPDATED_AT, INSERTED_AT
)
WITH inv_hist as (SELECT ITEM,
COALESCE(TO_CHAR(x.V_WH), LTRIM(i.ORG_NUM, '0')) ORG_NUM,
DAY_DT, CLEARANCE_FLG,
INV_SOH_QTY, INV_SOH_COST_AMT_LCL, INV_SOH_RTL_AMT_LCL, INV_UNIT_RTL_AMT_LCL, INV_AVG_COST_AMT_LCL,
DOC_CURR_CODE, LOC_CURR_CODE, UPDATED_AT
FROM ORI_INVENTORY_RAW i
LEFT JOIN XREF_WH x ON i.ORG_NUM = x.WH_SLT
),
-- SELECT COUNT(*) FROM inv_hist where org_num NOT in (SELECT TO_CHAR(STORE) FROM STORE_ADD_CTRL WHERE ctrl_status = 'C'
-- UNION ALL SELECT TO_CHAR(WH) FROM WH_CTRL WHERE ctrl_status = 'C')
---------------------------------------------------------------------------------
-- 1. a) RAP formatting rules
---------------------------------------------------------------------------------
error_rows_formating_rules AS (
SELECT 'E' AS LOG_LVL, 'ERROR_INVALID_CLEARANCE_FLG' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM inv_hist s WHERE CLEARANCE_FLG NOT IN ('Y','N')
),
---------------------------------------------------------------------------------
-- 1. b) RAP formatting data types
---------------------------------------------------------------------------------
error_rows_formating_dtype AS (
-- Rows that violate formatting rules -- should be empty
SELECT 'E' AS LOG_LVL,'ERROR_DTYPE_ITEM' AS ERROR_MSG,r.*,SYSDATE AS INSERTED_AT FROM inv_hist r WHERE ITEM IS NOT NULL AND LENGTHB(ITEM) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_ORG_NUM',r.*,SYSDATE FROM inv_hist r WHERE ORG_NUM IS NOT NULL AND LENGTHB(ORG_NUM) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_DAY_DT',r.*,SYSDATE FROM inv_hist r WHERE DAY_DT IS NULL OR TRUNC(DAY_DT) <> DAY_DT
UNION ALL
SELECT 'E','ERROR_DTYPE_CLEARANCE_FLG',r.*,SYSDATE FROM inv_hist r WHERE CLEARANCE_FLG IS NOT NULL AND LENGTHB(CLEARANCE_FLG) != 1
UNION ALL
SELECT 'E','ERROR_DTYPE_INV_SOH_QTY',r.*,SYSDATE FROM inv_hist r WHERE INV_SOH_QTY IS NOT NULL AND (ABS(TRUNC(INV_SOH_QTY)) >= POWER(10,14) OR INV_SOH_QTY != ROUND(INV_SOH_QTY,4))
UNION ALL
SELECT 'E','ERROR_DTYPE_INV_SOH_COST_AMT_LCL',r.*,SYSDATE FROM inv_hist r WHERE INV_SOH_COST_AMT_LCL IS NOT NULL AND (ABS(TRUNC(INV_SOH_COST_AMT_LCL)) >= POWER(10,16) OR INV_SOH_COST_AMT_LCL != ROUND(INV_SOH_COST_AMT_LCL,4))
UNION ALL
SELECT 'E','ERROR_DTYPE_INV_SOH_RTL_AMT_LCL',r.*,SYSDATE FROM inv_hist r WHERE INV_SOH_RTL_AMT_LCL IS NOT NULL AND (ABS(TRUNC(INV_SOH_RTL_AMT_LCL)) >= POWER(10,16) OR INV_SOH_RTL_AMT_LCL != ROUND(INV_SOH_RTL_AMT_LCL,4))
UNION ALL
SELECT 'E','ERROR_DTYPE_INV_UNIT_RTL_AMT_LCL',r.*,SYSDATE FROM inv_hist r WHERE INV_UNIT_RTL_AMT_LCL IS NOT NULL AND (ABS(TRUNC(INV_UNIT_RTL_AMT_LCL)) >= POWER(10,16) OR INV_UNIT_RTL_AMT_LCL != ROUND(INV_UNIT_RTL_AMT_LCL,4))
UNION ALL
SELECT 'E','ERROR_DTYPE_INV_AVG_COST_AMT_LCL',r.*,SYSDATE FROM inv_hist r WHERE INV_AVG_COST_AMT_LCL IS NOT NULL AND (ABS(TRUNC(INV_AVG_COST_AMT_LCL)) >= POWER(10,16) OR INV_AVG_COST_AMT_LCL != ROUND(INV_AVG_COST_AMT_LCL,4))
UNION ALL
SELECT 'E','ERROR_DTYPE_DOC_CURR_CODE',r.*,SYSDATE FROM inv_hist r WHERE DOC_CURR_CODE IS NOT NULL AND LENGTHB(DOC_CURR_CODE) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_LOC_CURR_CODE',r.*,SYSDATE FROM inv_hist r WHERE LOC_CURR_CODE IS NOT NULL AND LENGTHB(LOC_CURR_CODE) > 30
),
---------------------------------------------------------------------------------
-- 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 inv_hist s
WHERE ITEM IS NULL OR ORG_NUM IS NULL OR DAY_DT IS NULL OR CLEARANCE_FLG IS NULL
),
---------------------------------------------------------------------------------
-- 3. Validate duplicate keys
---------------------------------------------------------------------------------
duplicate_keys as (
SELECT ITEM, ORG_NUM, DAY_DT, CLEARANCE_FLG, COUNT(*) AS duplicate_cnt
FROM inv_hist
GROUP BY ITEM, ORG_NUM, DAY_DT, CLEARANCE_FLG 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 inv_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.CLEARANCE_FLG,'X') = NVL(d.CLEARANCE_FLG,'X')
),
---------------------------------------------------------------------------------
-- 4. Validate negative inventory
---------------------------------------------------------------------------------
error_rows_negative_inv AS (
SELECT 'W' AS LOG_LVL, 'WARN_NEGATIVE_INV' as ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM inv_hist s
WHERE s.INV_SOH_QTY < 0 OR s.INV_SOH_COST_AMT_LCL < 0 OR s.INV_SOH_RTL_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 inv_hist s
WHERE s.ITEM NOT IN (SELECT ITEM FROM ITEM_MASTER_CTRL WHERE ctrl_status = 'C')
),
---------------------------------------------------------------------------------
-- 5. b) Validate location exist in master data
---------------------------------------------------------------------------------
error_rows_missing_location AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_LOCATION' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT FROM inv_hist s
WHERE s.ORG_NUM NOT IN (SELECT TO_CHAR(STORE) FROM STORE_ADD_CTRL WHERE ctrl_status = 'C'
UNION ALL
SELECT TO_CHAR(WH) FROM WH_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_inv
UNION ALL
SELECT * FROM error_rows_missing_items
UNION ALL
SELECT * FROM error_rows_missing_location
)
SELECT * FROM all_error_rows;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_INVENTORY_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_INVENTORY_TMP
INSERT INTO ORI_INVENTORY_TMP
WITH inv_hist as (SELECT ITEM,
COALESCE(TO_CHAR(x.V_WH), LTRIM(i.ORG_NUM, '0')) ORG_NUM,
DAY_DT, CLEARANCE_FLG,
INV_SOH_QTY, INV_SOH_COST_AMT_LCL, INV_SOH_RTL_AMT_LCL, INV_UNIT_RTL_AMT_LCL, INV_AVG_COST_AMT_LCL,
DOC_CURR_CODE, LOC_CURR_CODE, UPDATED_AT
FROM ORI_INVENTORY_RAW i
LEFT JOIN XREF_WH x ON i.ORG_NUM = x.WH_SLT
)
SELECT s.*, SYSDATE AS INSERTED_AT
FROM inv_hist s
WHERE NOT EXISTS (
SELECT 1
FROM ORI_INVENTORY_REJECTED r
WHERE 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.CLEARANCE_FLG, 'X') = NVL(r.CLEARANCE_FLG, 'X')
);
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- REJECTION SUMMARY
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Retrieve a summary by error type
SELECT ERROR_MSG, COUNT(*) AS NUM_RECORDS, COUNT(distinct item) AS NUM_ITEMS --, SUM(SLS_QTY) AS SUM_SLS_QTY, SUM(SLS_AMT_LCL) AS SUM_SLS_AMT_LCL, SUM(RET_QTY) AS SUM_RET_QTY, SUM(RET_AMT_LCL) AS SUM_RET_AMT_LCL
FROM ORI_INVENTORY_REJECTED GROUP BY ERROR_MSG
UNION ALL
SELECT 'ALL INVENTORY' as ERROR_MSG, COUNT(*) AS NUM_RECORDS, COUNT(distinct item) AS NUM_ITEMS --, SUM(SLS_QTY) AS SUM_SLS_QTY, SUM(SLS_AMT_LCL) AS SUM_SLS_AMT_LCL, SUM(RET_QTY) AS SUM_RET_QTY, SUM(RET_AMT_LCL) AS SUM_RET_AMT_LCL
FROM ORI_INVENTORY_RAW
UNION ALL
SELECT 'VALID INVENTORY' as ERROR_MSG, COUNT(*) AS NUM_RECORDS, COUNT(distinct item) AS NUM_ITEMS --, SUM(SLS_QTY) AS SUM_SLS_QTY, SUM(SLS_AMT_LCL) AS SUM_SLS_AMT_LCL, SUM(RET_QTY) AS SUM_RET_QTY, SUM(RET_AMT_LCL) AS SUM_RET_AMT_LCL
FROM ORI_INVENTORY_TMP
ORDER BY NUM_RECORDS DESC;