Open purchase orders
POs that are approved but not yet fully received or invoiced — commits open against budget.
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.
SELECT ph.po_header_id, ph.segment1 AS po_number, ph.type_lookup_code AS po_type, s.vendor_name,
ph.creation_date, ph.approved_date,
SUM(pl.quantity * pl.unit_price) AS total_amount,
SUM(NVL(pll.quantity_received, 0) * pl.unit_price) AS amount_received,
SUM(NVL(pll.quantity_billed, 0) * pl.unit_price) AS amount_billed
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 ph.approved_flag = 'Y' AND ph.closed_date IS NULL
GROUP BY ph.po_header_id, ph.segment1, ph.type_lookup_code, s.vendor_name, ph.creation_date, ph.approved_date
HAVING SUM(NVL(pll.quantity_received,0) * pl.unit_price) < SUM(pl.quantity * pl.unit_price)
ORDER BY total_amount DESCA 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.
poopenprocurement
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.