Source: DMF/PuC/AIF/Scripts/DMF_INVENTORY_VALIDATIONS_v2.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,
LTRIM(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
),
---------------------------------------------------------------------------------
-- 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 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 inv_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_inv
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_INVENTORY_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_INVENTORY_TMP
INSERT INTO ORI_INVENTORY_TMP
WITH inv_hist as (SELECT ITEM,
LTRIM(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
)
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
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;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 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
ORDER BY NUM_RECORDS DESC;