Source: DMF/PuC/AIF/Scripts/supplier_invoices.sql
---SUPPLIER INVOICES
WITH
/* ------------------- Validations ------------------- */
valid_im_sup AS (
/* item ativo + supplier ativo (com mapping Oracle->SAP via xref_sups.sap_id) */
SELECT im.item,
xref.sap_id AS sap_supplier,
xref.oracle_id AS oracle_id
FROM item_master_ctrl im,
item_supplier_ctrl ims,
xref_sups xref,
sups_ctrl ctrl
WHERE im.item = ims.item
AND im.ctrl_status = 'C'
AND xref.oracle_id = ims.supplier
AND ctrl.supplier = ims.supplier
AND ctrl.ctrl_status = 'C'
),
valid_wh AS (
/* WH existe e está ativo */
SELECT DISTINCT xw.wh
FROM xref_wh xw,
wh_ctrl wc
WHERE wc.wh = xw.wh
AND wc.ctrl_status = 'C'
),
/* ------------------- Base join RAW header/detail ------------------- */
base AS (
SELECT
h.source_system
,h.belnr
,h.gjahr
,h.bldat
,h.lifnr
,h.waers
,h.kursf
,d.matnr
,d.werks
,d.ebeln
,d.ebelp
,d.rbmng
,d.menge
,d.rbwwr
,d.wrbtr
,d.netpr
FROM SUPPLIER_INVOICES_HEADER_RAW h,
SUPPLIER_INVOICES_DETAIL_RAW d
WHERE d.source_system = h.source_system
AND d.belnr = h.belnr
AND d.gjahr = h.gjahr
/* este filtro já foi aplicado no RAW, mas manter aqui não faz mal */
AND h.bldat >= '20240101'
),
/* ------------------- Enrichment + flags + calculations ------------------- */
enriched AS (
SELECT
b.source_system
/* ORG_NUM: tem de existir em XREF_WH (campo WH) e estar ativo em WH_CTRL */
,xw.wh AS org_wh
,b.matnr AS prod_it_num
,TO_DATE(b.bldat,'YYYYMMDD') AS day_dt
,vis.oracle_id AS supplier_num
,b.ebeln AS purchase_order_id
,b.belnr AS invoice_id
/* helpers numéricos: só converte se for numérico (evita ORA-01722) */
,CASE
WHEN b.rbmng = 0
THEN b.menge
ELSE b.rbmng
END AS invoice_qty
,CASE
WHEN b.RBWWR = 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
*/
FROM base b
LEFT JOIN xref_wh xw
ON xw.wh = b.werks
LEFT JOIN valid_wh vwh
ON vwh.wh = xw.wh
LEFT JOIN valid_im_sup vis
ON vis.item = LTRIM(b.matnr,'0')
AND vis.sap_supplier = b.lifnr
),
final_rows AS (
SELECT
--e.source_system
LTRIM(e.org_wh,'0') AS org_num
,e.prod_it_num
,e.day_dt
,e.supplier_num
,e.purchase_order_id
,e.invoice_id
/* INVOICE_QTY: RBMNG se populado e !=0, senão MENGE se populado e !=0 */
,e.invoice_qty
/* INVOICE_UNIT_COST_AMT_LCL:
prioridade RBWWR, senão WRBTR; divisor RBMNG senão MENGE */
,e.total_cost / e.invoice_qty AS invoice_unit_cost_amt_lcl
,e.po_unit_cost_amt_lcl
,e.datasource_num_id
,e.doc_curr_code
/* INTEGRATION_ID: ORG_NUM~PROD_IT_NUM~DAY_DT~SUPPLIER_NUM~INVOICE_ID */
,LTRIM(e.org_wh,'0') || '~'
|| e.prod_it_num || '~'
|| TO_CHAR(e.day_dt,'YYYYMMDD') || '~'
|| e.supplier_num || '~'
|| e.invoice_id AS integration_id
,e.loc_curr_code
FROM enriched e
)SELECT * FROM final_rows;