Source: DMF/PuC/AIF/DR1 scripts/DMF_PRICE_VALIDATIONS.sql
-- criar:
-- - ORI_PRICE_TMP
-- - ORI_PRICE_REJECTED
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Truncate validation tables
------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRUNCATE TABLE ORI_PRICE_REJECTED;
TRUNCATE TABLE ORI_PRICE_TMP;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_PRICE_REJECTED
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ORI_PRICE_REJECTED (
LOG_LVL, ERROR_MSG,
ITEM, ORG_NUM, DAY_DT, PRICE_CHANGE_TRAN_TYPE, STANDARD_UNIT_RTL_AMT_LCL, SELLING_UNIT_RTL_AMT_LCL, BASE_COST_AMT_LCL,
LOC_CURR_CODE, DOC_CURR_CODE, INSERTED_AT
)
---------------------------------------------------------------------------------
-- 1. Combine PRICE data from multiple sources
---------------------------------------------------------------------------------
-- initial price
WITH initial_price AS (SELECT ITEM AS ITEM
,STORE AS ORG_NUM
,START_DATE AS DAY_DT
,PRICE_CHANGE_TRAN_TYPE AS PRICE_CHANGE_TRAN_TYPE
,PRICE AS STANDARD_UNIT_RTL_AMT_LCL
,PRICE AS SELLING_UNIT_RTL_AMT_LCL
,BASE_COST AS BASE_COST_AMT_LCL
,'EUR' AS LOC_CURR_CODE
,DOC_CURR_CODE AS DOC_CURR_CODE
--,PRICE AS ORIG_SELLING_UNIT_RTL_AMT_LCL
--,NULL AS LST_REG_RTL_AMT_LCL
FROM INIT_PRICE_RAW)
-- historical price changes
, hist_price AS (SELECT ITEM AS ITEM
,STORE AS ORG_NUM
,START_DATE AS DAY_DT
,PRICE_CHANGE_TRAN_TYPE AS PRICE_CHANGE_TRAN_TYPE
,PRICE AS STANDARD_UNIT_RTL_AMT_LCL
,PRICE AS SELLING_UNIT_RTL_AMT_LCL
,BASE_COST AS BASE_COST_AMT_LCL
,'EUR' AS LOC_CURR_CODE
,DOC_CURR_CODE AS DOC_CURR_CODE
--,PRICE AS ORIG_SELLING_UNIT_RTL_AMT_LCL
--,NULL AS LST_REG_RTL_AMT_LCL
FROM PRICE_HISTORY_RAW)
-- combined price data
, full_price AS ( SELECT * FROM initial_price
UNION ALL
SELECT * FROM hist_price)
---------------------------------------------------------------------------------
-- 2. Filter Items and Locations not migrated
---------------------------------------------------------------------------------
-- ITEM
, error_rows_missing_items AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_ITEM' AS ERROR_MSG, p.*, SYSDATE AS INSERTED_AT FROM full_price p
WHERE NOT EXISTS (SELECT 1 FROM ITEM_MASTER_CTRL c WHERE c.ctrl_status = 'C' AND c.ITEM = p.ITEM )
)
-- LOCATION
, error_rows_missing_location AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_LOCATION' AS ERROR_MSG, p.*, SYSDATE AS INSERTED_AT FROM full_price p
WHERE NOT EXISTS (SELECT 1 FROM ( SELECT STORE AS LOC FROM STORE_ADD_CTRL WHERE ctrl_status = 'C'
UNION ALL SELECT WH AS LOC FROM WH_CTRL WHERE ctrl_status = 'C') c WHERE c.LOC = p.ORG_NUM )
)
-- ITEM/LOCATION
, error_rows_missing_item_location AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_ITEM_LOCATION' AS ERROR_MSG, p.*, SYSDATE AS INSERTED_AT FROM full_price p
WHERE NOT EXISTS (SELECT 1 FROM ITEM_LOC_CTRL c WHERE c.ctrl_status = 'C'
AND c.ITEM = p.ITEM
AND c.LOC = p.ORG_NUM)
)
, filtered_price AS (
SELECT p.* FROM full_price p
WHERE EXISTS (SELECT 1 FROM ITEM_MASTER_CTRL c WHERE c.ctrl_status = 'C' AND c.ITEM = p.ITEM )
AND EXISTS (SELECT 1 FROM (SELECT STORE AS LOC FROM STORE_ADD_CTRL WHERE ctrl_status = 'C' UNION ALL SELECT WH AS LOC FROM WH_CTRL WHERE ctrl_status = 'C') c WHERE c.LOC = p.ORG_NUM )
AND EXISTS (SELECT 1 FROM ITEM_LOC_CTRL c WHERE c.ctrl_status = 'C' AND c.ITEM = p.ITEM AND c.LOC = p.ORG_NUM)
)
-- SELECT COUNT(*) FROM filtered_price; -- 5 122 365
---------------------------------------------------------------------------------
-- 3. RAP formatting data types
---------------------------------------------------------------------------------
, error_rows_formating_dtype AS (
SELECT 'E' AS LOG_LVL, 'ERROR_DTYPE_ITEM' AS ERROR_MSG, r.*, SYSDATE AS INSERTED_AT FROM filtered_price r WHERE r.ITEM IS NOT NULL AND LENGTH(r.ITEM) > 80
UNION ALL
SELECT 'E' AS LOG_LVL, 'ERROR_DTYPE_ORG_NUM' AS ERROR_MSG, r.*, SYSDATE AS INSERTED_AT FROM filtered_price r WHERE r.ORG_NUM IS NOT NULL AND (r.ORG_NUM != TRUNC(r.ORG_NUM) OR LENGTH(TO_CHAR(TRUNC(r.ORG_NUM))) > 30)
UNION ALL
SELECT 'E' AS LOG_LVL, 'ERROR_DTYPE_DAY_DT' AS ERROR_MSG, r.*, SYSDATE AS INSERTED_AT FROM filtered_price r WHERE r.DAY_DT IS NOT NULL AND TRUNC(r.DAY_DT) <> r.DAY_DT
UNION ALL
SELECT 'E' AS LOG_LVL, 'ERROR_DTYPE_PRICE_CHANGE_TRAN_TYPE' AS ERROR_MSG, r.*, SYSDATE AS INSERTED_AT FROM filtered_price r WHERE r.PRICE_CHANGE_TRAN_TYPE IS NOT NULL AND LENGTH(r.PRICE_CHANGE_TRAN_TYPE) > 2
UNION ALL
SELECT 'E' AS LOG_LVL, 'ERROR_DTYPE_STANDARD_UNIT_RTL_AMT_LCL' AS ERROR_MSG, r.*, SYSDATE AS INSERTED_AT FROM filtered_price r WHERE r.STANDARD_UNIT_RTL_AMT_LCL IS NOT NULL AND (ABS(TRUNC(r.STANDARD_UNIT_RTL_AMT_LCL)) >= POWER(10,16) OR r.STANDARD_UNIT_RTL_AMT_LCL != ROUND(r.STANDARD_UNIT_RTL_AMT_LCL, 4))
UNION ALL
SELECT 'E' AS LOG_LVL, 'ERROR_DTYPE_SELLING_UNIT_RTL_AMT_LCL' AS ERROR_MSG, r.*, SYSDATE AS INSERTED_AT FROM filtered_price r WHERE r.SELLING_UNIT_RTL_AMT_LCL IS NOT NULL AND (ABS(TRUNC(r.SELLING_UNIT_RTL_AMT_LCL)) >= POWER(10,16) OR r.SELLING_UNIT_RTL_AMT_LCL != ROUND(r.SELLING_UNIT_RTL_AMT_LCL, 4))
UNION ALL
SELECT 'E' AS LOG_LVL, 'ERROR_DTYPE_BASE_COST_AMT_LCL' AS ERROR_MSG, r.*, SYSDATE AS INSERTED_AT FROM filtered_price r WHERE r.BASE_COST_AMT_LCL IS NOT NULL AND (ABS(TRUNC(r.BASE_COST_AMT_LCL)) >= POWER(10,16) OR r.BASE_COST_AMT_LCL != ROUND(r.BASE_COST_AMT_LCL, 4))
UNION ALL
SELECT 'E' AS LOG_LVL, 'ERROR_DTYPE_DOC_CURR_CODE' AS ERROR_MSG, r.*, SYSDATE AS INSERTED_AT FROM filtered_price r WHERE r.DOC_CURR_CODE IS NOT NULL AND LENGTH(r.DOC_CURR_CODE) > 30
UNION ALL
SELECT 'E' AS LOG_LVL, 'ERROR_DTYPE_LOC_CURR_CODE' AS ERROR_MSG, r.*, SYSDATE AS INSERTED_AT FROM filtered_price r WHERE r.LOC_CURR_CODE IS NOT NULL AND LENGTH(r.LOC_CURR_CODE) > 30
)
---------------------------------------------------------------------------------
-- 4. Validate mandatory fields are not null
---------------------------------------------------------------------------------
, error_rows_null_fields AS (
SELECT 'E' AS LOG_LVL, 'ERROR_INVALID_NULL' AS ERROR_MSG, p.*, SYSDATE AS INSERTED_AT FROM filtered_price p
WHERE ITEM IS NULL OR ORG_NUM IS NULL OR DAY_DT IS NULL OR PRICE_CHANGE_TRAN_TYPE IS NULL
)
---------------------------------------------------------------------------------
-- 5. Validate duplicate keys
---------------------------------------------------------------------------------
, error_rows_duplicate_keys AS (
SELECT 'E' AS LOG_LVL, 'ERROR_DUPLICATE_KEY' AS ERROR_MSG, p.ITEM, p.ORG_NUM, p.DAY_DT, p.PRICE_CHANGE_TRAN_TYPE,
p.STANDARD_UNIT_RTL_AMT_LCL, p.SELLING_UNIT_RTL_AMT_LCL, p.BASE_COST_AMT_LCL, p.LOC_CURR_CODE, p.DOC_CURR_CODE, SYSDATE AS INSERTED_AT
FROM (SELECT p.*, COUNT(*) OVER (PARTITION BY ITEM, ORG_NUM, DAY_DT) AS duplicate_cnt FROM filtered_price p) p WHERE p.duplicate_cnt > 1
)
---------------------------------------------------------------------------------
-- 6. Return all rows with errors/warnings
---------------------------------------------------------------------------------
, all_error_rows AS (
SELECT * FROM error_rows_missing_items
UNION ALL
SELECT * FROM error_rows_missing_location
UNION ALL
SELECT * FROM error_rows_missing_item_location
UNION ALL
SELECT * FROM error_rows_formating_dtype
UNION ALL
SELECT * FROM error_rows_null_fields
UNION ALL
SELECT * FROM error_rows_duplicate_keys
)
SELECT * FROM all_error_rows;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert valid rows to ORI_PRICE_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_PRICE_TMP
INSERT INTO ORI_PRICE_TMP (
ITEM, ORG_NUM, DAY_DT, PRICE_CHANGE_TRAN_TYPE, STANDARD_UNIT_RTL_AMT_LCL, SELLING_UNIT_RTL_AMT_LCL, BASE_COST_AMT_LCL,
LOC_CURR_CODE, DOC_CURR_CODE, INSERTED_AT
)
WITH full_price AS (SELECT ITEM AS ITEM
,STORE AS ORG_NUM
,START_DATE AS DAY_DT
,PRICE_CHANGE_TRAN_TYPE AS PRICE_CHANGE_TRAN_TYPE
,PRICE AS STANDARD_UNIT_RTL_AMT_LCL
,PRICE AS SELLING_UNIT_RTL_AMT_LCL
,BASE_COST AS BASE_COST_AMT_LCL
,'EUR' AS LOC_CURR_CODE
,DOC_CURR_CODE AS DOC_CURR_CODE
--,PRICE AS ORIG_SELLING_UNIT_RTL_AMT_LCL
--,NULL AS LST_REG_RTL_AMT_LCL
FROM INIT_PRICE_RAW
UNION ALL
SELECT ITEM AS ITEM
,STORE AS ORG_NUM
,START_DATE AS DAY_DT
,PRICE_CHANGE_TRAN_TYPE AS PRICE_CHANGE_TRAN_TYPE
,PRICE AS STANDARD_UNIT_RTL_AMT_LCL
,PRICE AS SELLING_UNIT_RTL_AMT_LCL
,BASE_COST AS BASE_COST_AMT_LCL
,'EUR' AS LOC_CURR_CODE
,DOC_CURR_CODE AS DOC_CURR_CODE
--,PRICE AS ORIG_SELLING_UNIT_RTL_AMT_LCL
--,NULL AS LST_REG_RTL_AMT_LCL
FROM PRICE_HISTORY_RAW
)
SELECT p.*, SYSDATE AS INSERTED_AT FROM full_price p
WHERE NOT EXISTS (SELECT 1 FROM ORI_PRICE_REJECTED r WHERE r.ITEM = p.ITEM AND r.ORG_NUM = p.ORG_NUM AND r.DAY_DT = p.DAY_DT);
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- REJECTION SUMMARY
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Retrieve an approximate summary by error type
SELECT
ERROR_MSG,
COUNT(*) AS num_records,
APPROX_COUNT_DISTINCT(item) AS num_items,
APPROX_COUNT_DISTINCT(org_num) AS num_locs
FROM ORI_PRICE_REJECTED
GROUP BY ERROR_MSG;
/*
ERROR_MSG NUM_RECORDS NUM_ITEMS NUM_LOCS
ERROR_MISSING_ITEM_LOCATION 795173637 2217394 288
ERROR_MISSING_ITEM 658460733 1897320 288
ERROR_MISSING_LOCATION 131273956 2217394 74
ERROR_DUPLICATE_KEY 358860 30812 139
*/