Free Fusion SQLProcurementRead-only

Three-way match exceptions

POs where invoiced amount exceeds the lower of ordered or received amount — indicates control breach or pricing change.

Tables used

po_headers_all po_lines_all po_line_locations_all poz_suppliers_v

Bind parameters

None — the statement runs as written; edit the literals in the WHERE clause to narrow it.

How to run it

Paste it into a BI Publisher data model as a SQL data set, or into QueryWell's Query Studio and run it against your pod with a reporting account. Results come back to a grid you can profile, chart and export with a SHA-256 hash.

SQLdba-proc-three-way-match-exceptions
SELECT ph.segment1 AS po_number, s.vendor_name, pl.line_num, pl.item_description,
  pl.quantity AS ordered_qty, pll.quantity_received, pll.quantity_billed,
  pll.quantity_billed - LEAST(pl.quantity, pll.quantity_received) AS over_billed_qty, pl.unit_price
FROM po_headers_all ph
JOIN po_lines_all pl ON pl.po_header_id = ph.po_header_id
JOIN po_line_locations_all pll ON pll.po_line_id = pl.po_line_id
JOIN poz_suppliers_v s ON s.vendor_id = ph.vendor_id
WHERE pll.quantity_billed > LEAST(pl.quantity, NVL(pll.quantity_received, 0)) AND ph.approved_flag = 'Y'
ORDER BY over_billed_qty DESC

A single read-only SELECT against Oracle Fusion Cloud's supported reporting surface. Column names and views can differ by release and by the roles your reporting account holds, so validate the result against a report you already trust before relying on it.

pomatchexception

Related

More Procurement queries

Run it without the template cycle

QueryWell runs this and 81 other curated scripts against your own Fusion pod from one signed desktop app — grid, charts, profiling and hashed exports included. Perpetual licence, 14-day trial.