Source: DMF/PuC/AIF/DR1 scripts/DMF_SUPS_INVOICES_VALIDATIONS.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Truncate validation tables
------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRUNCATE TABLE ORI_SUPPLIER_INVOICES_REJECTED;
TRUNCATE TABLE ORI_SUPPLIER_INVOICES_TMP;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_SUPPLIER_INVOICES_REJECTED
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ORI_SUPPLIER_INVOICES_REJECTED (
LOG_LVL, ERROR_MSG,
SOURCE_SYSTEM, ORG_NUM, PROD_IT_NUM, DAY_DT, SUPPLIER_NUM,
PURCHASE_ORDER_ID, INVOICE_ID, INVOICE_QTY,
INVOICE_UNIT_COST_AMT_LCL, PO_UNIT_COST_AMT_LCL,
DATASOURCE_NUM_ID, DOC_CURR_CODE, INTEGRATION_ID, LOC_CURR_CODE,
UPDATED_AT, INSERTED_AT
)
WITH
---------------------------------------------------------------------------------
-- 1) Base join (header/detail) + cálculo de campos (dataset “pronto”)
---------------------------------------------------------------------------------
inv_src AS (
SELECT
b.source_system AS source_system,
xw.wh AS org_num,
b.matnr AS prod_it_num,
TO_DATE(b.bldat,'YYYYMMDD') AS day_dt,
xrs.oracle_id AS supplier_num,
b.ebeln AS purchase_order_id,
b.belnr AS invoice_id,
/* invoice_qty: RBMNG se !=0 senão MENGE */
CASE WHEN NVL(b.rbmng,0) = 0 THEN b.menge ELSE b.rbmng END AS invoice_qty,
/* total_cost: RBWWR se !=0 senão WRBTR */
CASE WHEN NVL(b.rbwwr,0) = 0 THEN b.wrbtr ELSE b.rbwwr END AS total_cost,
b.netpr AS po_unit_cost_amt_lcl,
1 AS datasource_num_id,
b.waers AS doc_curr_code,
'EUR' AS loc_curr_code,
/* integration id: ORG~PROD~YYYYMMDD~SUP~INV */
xw.wh || '~'
|| b.matnr || '~'
|| TO_CHAR(TO_DATE(b.bldat,'YYYYMMDD'),'YYYYMMDD') || '~'
|| xrs.oracle_id || '~'
|| b.belnr AS integration_id,
CAST(NULL AS DATE) AS updated_at
/* auxiliares p/ validação */
/* CASE WHEN xw.wh IS NULL THEN 0 ELSE 1 END AS has_xref_wh,
CASE WHEN vwh.wh IS NULL THEN 0 ELSE 1 END AS is_wh_active,
CASE WHEN xrs.item IS NULL THEN 0 ELSE 1 END AS is_item_supplier_ok */
FROM (
SELECT
h.source_system, h.belnr, h.gjahr, h.bldat, h.lifnr, h.waers,
d.matnr, d.werks, d.ebeln, d.ebelp, d.rbmng, d.menge, d.rbwwr, d.wrbtr, d.netpr
FROM SUPPLIER_INVOICES_HEADER_RAW h
JOIN SUPPLIER_INVOICES_DETAIL_RAW d
ON d.source_system = h.source_system
AND d.belnr = h.belnr
AND d.gjahr = h.gjahr
WHERE h.bldat >= '20240101'
) b
LEFT JOIN xref_wh xw
ON xw.wh_slt = b.werks
LEFT JOIN xref_sups xrs
ON xrs.sap_id = b.lifnr
/* LEFT JOIN valid_wh vwh
ON vwh.wh = xw.wh
LEFT JOIN valid_im_sup xrs
ON xrs.item = LTRIM(b.matnr,'0')
AND xrs.sap_supplier = b.lifnr */
),
---------------------------------------------------------------------------------
-- 2) Dataset final (evita divisão por zero no cálculo do unit cost)
---------------------------------------------------------------------------------
inv_final AS (
SELECT
source_system, org_num, prod_it_num, day_dt, supplier_num,
purchase_order_id, invoice_id,
invoice_qty,
CASE
WHEN invoice_qty IS NULL OR invoice_qty = 0 THEN NULL
ELSE total_cost / invoice_qty
END AS invoice_unit_cost_amt_lcl,
po_unit_cost_amt_lcl,
datasource_num_id, doc_curr_code, integration_id, loc_curr_code,
updated_at
/* auxiliares */
/* has_xref_wh, is_wh_active, is_item_supplier_ok */
FROM inv_src
),
---------------------------------------------------------------------------------
-- 1. b) RAP formatting data types (tamanho/tipo) - conforme layout TMP
---------------------------------------------------------------------------------
error_rows_formatting_dtype AS (
SELECT 'E' AS LOG_LVL,'ERROR_DTYPE_SOURCE_SYSTEM' AS ERROR_MSG, r.*, SYSDATE AS inserted_at FROM inv_final r WHERE r.source_system IS NOT NULL AND LENGTHB(r.source_system) > 3
UNION ALL
SELECT 'E','ERROR_DTYPE_ORG_NUM',r.*,SYSDATE FROM inv_final r WHERE r.org_num IS NOT NULL AND LENGTHB(r.org_num) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_PROD_IT_NUM',r.*,SYSDATE FROM inv_final r WHERE r.prod_it_num IS NOT NULL AND LENGTHB(r.prod_it_num) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_DAY_DT',r.*,SYSDATE FROM inv_final r WHERE r.day_dt IS NULL OR TRUNC(r.day_dt) <> r.day_dt
UNION ALL
SELECT 'E','ERROR_DTYPE_SUPPLIER_NUM',r.*,SYSDATE FROM inv_final r WHERE r.supplier_num IS NOT NULL AND LENGTHB(r.supplier_num) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_PURCHASE_ORDER_ID',r.*,SYSDATE FROM inv_final r WHERE r.purchase_order_id IS NOT NULL AND LENGTHB(r.purchase_order_id) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_INVOICE_ID',r.*,SYSDATE FROM inv_final r WHERE r.invoice_id IS NOT NULL AND LENGTHB(r.invoice_id) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_INVOICE_QTY',r.*,SYSDATE FROM inv_final r WHERE r.invoice_qty IS NOT NULL AND (ABS(TRUNC(r.invoice_qty)) >= POWER(10,14) OR r.invoice_qty <> ROUND(r.invoice_qty,4))
UNION ALL
SELECT 'E','ERROR_DTYPE_INVOICE_UNIT_COST_AMT_LCL',r.*,SYSDATE FROM inv_final r WHERE r.invoice_unit_cost_amt_lcl IS NOT NULL AND (ABS(TRUNC(r.invoice_unit_cost_amt_lcl)) >= POWER(10,16) OR r.invoice_unit_cost_amt_lcl <> ROUND(r.invoice_unit_cost_amt_lcl,4))
UNION ALL
SELECT 'E','ERROR_DTYPE_PO_UNIT_COST_AMT_LCL',r.*,SYSDATE FROM inv_final r WHERE r.po_unit_cost_amt_lcl IS NOT NULL AND (ABS(TRUNC(r.po_unit_cost_amt_lcl)) >= POWER(10,16) OR r.po_unit_cost_amt_lcl <> ROUND(r.po_unit_cost_amt_lcl,4))
UNION ALL
SELECT 'E','ERROR_DTYPE_DATASOURCE_NUM_ID',r.*,SYSDATE FROM inv_final r WHERE r.datasource_num_id IS NOT NULL AND (ABS(TRUNC(r.datasource_num_id)) >= POWER(10,14) OR r.datasource_num_id <> TRUNC(r.datasource_num_id))
UNION ALL
SELECT 'E','ERROR_DTYPE_DOC_CURR_CODE',r.*,SYSDATE FROM inv_final r WHERE r.doc_curr_code IS NOT NULL AND LENGTHB(r.doc_curr_code) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_INTEGRATION_ID',r.*,SYSDATE FROM inv_final r WHERE r.integration_id IS NOT NULL AND LENGTHB(r.integration_id) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_LOC_CURR_CODE',r.*,SYSDATE FROM inv_final r WHERE r.loc_curr_code IS NOT NULL AND LENGTHB(r.loc_curr_code) > 30
),
---------------------------------------------------------------------------------
-- 2) Validate mandatory fields are not null (inclui DOC_CURR_CODE)
---------------------------------------------------------------------------------
error_rows_null_fields AS (
SELECT 'E' AS LOG_LVL, 'ERROR_INVALID_NULL' AS ERROR_MSG, f.*, SYSDATE AS inserted_at
FROM inv_final f
WHERE f.org_num IS NULL
OR f.prod_it_num IS NULL
OR f.supplier_num IS NULL
OR f.purchase_order_id IS NULL
OR f.invoice_id IS NULL
OR f.datasource_num_id IS NULL
OR f.integration_id IS NULL
OR f.doc_curr_code IS NULL
OR TRIM(f.doc_curr_code) IS NULL
),
---------------------------------------------------------------------------------
-- 5) Validate duplicate business key:
-- (PROD_IT_NUM, SUPPLIER_NUM, INVOICE_ID, PURCHASE_ORDER_ID, ORG_NUM)
---------------------------------------------------------------------------------
duplicate_keys AS (
SELECT
prod_it_num, supplier_num, invoice_id, purchase_order_id, org_num,
COUNT(*) AS duplicate_cnt
FROM inv_final
GROUP BY prod_it_num, supplier_num, invoice_id, purchase_order_id, org_num
HAVING COUNT(*) > 1
),
error_rows_duplicate_keys AS (
SELECT 'E' AS LOG_LVL, 'ERROR_DUPLICATE_BUSINESS_KEY' AS ERROR_MSG, f.*, SYSDATE AS inserted_at
FROM inv_final f
JOIN duplicate_keys d
ON NVL(f.prod_it_num,'-1') = NVL(d.prod_it_num,'-1')
AND NVL(f.supplier_num,'-1') = NVL(d.supplier_num,'-1')
AND NVL(f.invoice_id,'-1') = NVL(d.invoice_id,'-1')
AND NVL(f.purchase_order_id,'-1') = NVL(d.purchase_order_id,'-1')
AND NVL(f.org_num,'-1') = NVL(d.org_num,'-1')
),
---------------------------------------------------------------------------------
-- 5. a) Validate warehouse exist and is active
---------------------------------------------------------------------------------
error_rows_wh_missing AS (
SELECT 'E' AS LOG_LVL, 'ERROR_NO_XREF_WH' AS ERROR_MSG, f.*, SYSDATE AS inserted_at
FROM inv_final f
WHERE ORG_NUM NOT IN (select wh from wh_ctrl WHERE ctrl_status = 'C')
),
---------------------------------------------------------------------------------
-- 0) Validate item sup exist and is active
---------------------------------------------------------------------------------
error_rows_item_sup_missing AS (
SELECT 'E' AS LOG_LVL, 'ERROR_NO_VALID_ITEM_SUP' AS ERROR_MSG, f.*, SYSDATE AS inserted_at
FROM inv_final f
WHERE NOT EXISTS (
SELECT 1
FROM item_master_ctrl im
JOIN item_supplier_ctrl ims
ON ims.item = im.item
JOIN sups_ctrl ctrl
ON ctrl.supplier = ims.supplier
WHERE im.ctrl_status = 'C'
AND ctrl.ctrl_status = 'C'
AND im.item = f.prod_it_num
AND ims.supplier = f.supplier_num
)
),
---------------------------------------------------------------------------------
-- 4) Denominador não pode ser 0
---------------------------------------------------------------------------------
error_rows_qty_invalid AS (
SELECT 'E' AS LOG_LVL, 'ERROR_INVALID_INVOICE_QTY' AS ERROR_MSG, f.*, SYSDATE AS inserted_at
FROM inv_final f
WHERE f.invoice_qty IS NULL OR f.invoice_qty = 0
),
---------------------------------------------------------------------------------
-- 6) Return all rows with errors
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT * FROM error_rows_formatting_dtype
UNION ALL SELECT * FROM error_rows_null_fields
UNION ALL SELECT * FROM error_rows_duplicate_keys
UNION ALL SELECT * FROM error_rows_wh_missing
UNION ALL SELECT * FROM error_rows_item_sup_missing
UNION ALL SELECT * FROM error_rows_qty_invalid
)
SELECT
LOG_LVL, ERROR_MSG,
source_system, org_num, prod_it_num, day_dt, supplier_num,
purchase_order_id, invoice_id, invoice_qty,
invoice_unit_cost_amt_lcl, po_unit_cost_amt_lcl,
datasource_num_id, doc_curr_code, integration_id, loc_curr_code,
updated_at, inserted_at
FROM all_error_rows;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_SUPPLIER_INVOICES_TMP
-- (Rows que NÃO estão em REJECTED com LOG_LVL='E' pela business key)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ORI_SUPPLIER_INVOICES_TMP (
SOURCE_SYSTEM, ORG_NUM, PROD_IT_NUM, DAY_DT, SUPPLIER_NUM,
PURCHASE_ORDER_ID, INVOICE_ID, INVOICE_QTY,
INVOICE_UNIT_COST_AMT_LCL, PO_UNIT_COST_AMT_LCL,
DATASOURCE_NUM_ID, DOC_CURR_CODE, INTEGRATION_ID, LOC_CURR_CODE
)
WITH
---------------------------------------------------------------------------------
-- 1) Base join (header/detail) + cálculo de campos (IGUAL ao bloco de REJECTED)
---------------------------------------------------------------------------------
inv_src AS (
SELECT
b.source_system AS source_system,
xw.wh AS org_num,
b.matnr AS prod_it_num,
TO_DATE(b.bldat,'YYYYMMDD') AS day_dt,
xrs.oracle_id AS supplier_num,
b.ebeln AS purchase_order_id,
b.belnr AS invoice_id,
CASE WHEN NVL(b.rbmng,0) = 0 THEN b.menge ELSE b.rbmng END AS invoice_qty,
CASE WHEN NVL(b.rbwwr,0) = 0 THEN b.wrbtr ELSE b.rbwwr END AS total_cost,
b.netpr AS po_unit_cost_amt_lcl,
1 AS datasource_num_id,
b.waers AS doc_curr_code,
'EUR' AS loc_curr_code,
xw.wh || '~'
|| b.matnr || '~'
|| TO_CHAR(TO_DATE(b.bldat,'YYYYMMDD'),'YYYYMMDD') || '~'
|| xrs.oracle_id || '~'
|| b.belnr AS integration_id,
CAST(NULL AS DATE) AS updated_at
FROM (
SELECT
h.source_system, h.belnr, h.gjahr, h.bldat, h.lifnr, h.waers,
d.matnr, d.werks, d.ebeln, d.ebelp, d.rbmng, d.menge, d.rbwwr, d.wrbtr, d.netpr
FROM SUPPLIER_INVOICES_HEADER_RAW h
JOIN SUPPLIER_INVOICES_DETAIL_RAW d
ON d.source_system = h.source_system
AND d.belnr = h.belnr
AND d.gjahr = h.gjahr
WHERE h.bldat >= '20240101'
) b
LEFT JOIN xref_wh xw
ON xw.wh_slt = b.werks
LEFT JOIN xref_sups xrs
ON xrs.sap_id = b.lifnr
),
---------------------------------------------------------------------------------
-- 2) Dataset final (IGUAL ao bloco de REJECTED)
---------------------------------------------------------------------------------
inv_final AS (
SELECT
source_system, org_num, prod_it_num, day_dt, supplier_num,
purchase_order_id, invoice_id,
invoice_qty,
CASE
WHEN invoice_qty IS NULL OR invoice_qty = 0 THEN NULL
ELSE total_cost / invoice_qty
END AS invoice_unit_cost_amt_lcl,
po_unit_cost_amt_lcl,
datasource_num_id, doc_curr_code, integration_id, loc_curr_code,
updated_at
FROM inv_src
)
SELECT
f.source_system, f.org_num, f.prod_it_num, f.day_dt, f.supplier_num,
f.purchase_order_id, f.invoice_id, f.invoice_qty,
f.invoice_unit_cost_amt_lcl, f.po_unit_cost_amt_lcl,
f.datasource_num_id, f.doc_curr_code, f.integration_id, f.loc_curr_code
FROM inv_final f
WHERE NOT EXISTS (
SELECT 1
FROM ORI_SUPPLIER_INVOICES_REJECTED r
WHERE r.LOG_LVL = 'E'
AND NVL(f.invoice_id,'-1') = NVL(r.invoice_id,'-1')
);
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- REJECTION SUMMARY
------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT ERROR_MSG, COUNT(*) AS NUM_RECORDS, COUNT(DISTINCT INVOICE_ID) AS NUM_INVOICES
FROM ORI_SUPPLIER_INVOICES_REJECTED
GROUP BY ERROR_MSG
UNION ALL
SELECT 'ALL SUPPLIER INVOICES' AS ERROR_MSG, COUNT(*) AS NUM_RECORDS, COUNT(DISTINCT INVOICE_ID) AS NUM_INVOICES
FROM ORI_SUPPLIER_INVOICES_TMP
ORDER BY NUM_RECORDS DESC;