OracleFree generatorPDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS P2P Query: PO to Receipt to Invoice to Payment

This generator writes the procure-to-pay trace Oracle E-Business Suite R12 users ask for when a supplier calls or an auditor samples: purchase order distribution to receipt delivery to AP invoice distribution to payment, plus a non-duplicating summary and a supplier statement. It works on 12.1 and 12.2 with the R12 AP_SUPPLIERS model.

Fill in

Operating unit ORG_ID

PO number LIKE

Supplier name LIKE

Invoice date from

Invoice date to

Generated code

Download:

Query 1: detail trace (one row per PO distribution x delivery x invoice distribution x payment)

SELECT ph.segment1                AS po_number,
       s.vendor_name,
       ss.vendor_site_code,
       pl.line_num,
       pll.shipment_num,
       pd.distribution_num,
       pd.quantity_ordered,
       pd.quantity_delivered,
       pd.quantity_billed,
       CASE WHEN pll.receipt_required_flag = 'Y' AND pll.inspection_required_flag = 'Y' THEN '4-way'
            WHEN pll.receipt_required_flag = 'Y' THEN '3-way'
            ELSE '2-way' END        AS match_level,
       rsh.receipt_num,
       rt.transaction_date          AS delivery_date,
       rt.quantity                  AS delivered_qty,
       ai.invoice_num,
       ai.invoice_date,
       ai.invoice_amount,
       aid.amount                   AS invoice_dist_amount,
       ai.payment_status_flag,      -- Y paid, N unpaid, P partially paid
       ac.check_number,
       ac.check_date,
       ac.void_date,
       aip.amount                   AS paid_amount
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   po_distributions_all pd    ON pd.line_location_id   = pll.line_location_id
JOIN   ap_suppliers s             ON s.vendor_id           = ph.vendor_id
JOIN   ap_supplier_sites_all ss   ON ss.vendor_site_id     = ph.vendor_site_id
LEFT JOIN rcv_transactions rt     ON rt.po_distribution_id = pd.po_distribution_id
                                 AND rt.transaction_type   = 'DELIVER'
LEFT JOIN rcv_shipment_headers rsh ON rsh.shipment_header_id = rt.shipment_header_id
LEFT JOIN ap_invoice_distributions_all aid ON aid.po_distribution_id = pd.po_distribution_id
LEFT JOIN ap_invoices_all ai      ON ai.invoice_id         = aid.invoice_id
LEFT JOIN ap_invoice_payments_all aip ON aip.invoice_id    = ai.invoice_id
LEFT JOIN ap_checks_all ac        ON ac.check_id           = aip.check_id
WHERE  ph.org_id = 204
AND    ph.segment1 LIKE '%'
AND    s.vendor_name LIKE 'Advanced%'
AND    (ai.invoice_id IS NULL
        OR ai.invoice_date BETWEEN TO_DATE('2026-07-01', 'YYYY-MM-DD') AND TO_DATE('2026-09-30', 'YYYY-MM-DD'))
ORDER  BY ph.segment1, pl.line_num, pll.shipment_num, pd.distribution_num, ai.invoice_num;

Query 2: summary per PO distribution (no fan-out duplication)

SELECT ph.segment1 AS po_number,
       s.vendor_name,
       pl.line_num,
       pll.shipment_num,
       pd.distribution_num,
       pd.quantity_ordered,
       pd.quantity_delivered,
       pd.quantity_billed,
       (SELECT COUNT(DISTINCT aid.invoice_id)
        FROM   ap_invoice_distributions_all aid
        WHERE  aid.po_distribution_id = pd.po_distribution_id) AS invoice_count,
       (SELECT NVL(SUM(aid.amount), 0)
        FROM   ap_invoice_distributions_all aid
        WHERE  aid.po_distribution_id = pd.po_distribution_id) AS invoiced_amount,
       (SELECT MAX(rt.transaction_date)
        FROM   rcv_transactions rt
        WHERE  rt.po_distribution_id = pd.po_distribution_id
        AND    rt.transaction_type   = 'DELIVER') AS last_delivery_date
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   po_distributions_all pd   ON pd.line_location_id = pll.line_location_id
JOIN   ap_suppliers s            ON s.vendor_id         = ph.vendor_id
WHERE  ph.org_id = 204
AND    ph.segment1 LIKE '%'
AND    s.vendor_name LIKE 'Advanced%'
ORDER  BY ph.segment1, pl.line_num, pll.shipment_num, pd.distribution_num;

Query 3: supplier statement (invoices, then payments)

SELECT s.vendor_name,
       ss.vendor_site_code,
       ai.invoice_num,
       ai.invoice_date,
       ai.invoice_currency_code,
       ai.invoice_amount,
       NVL(ai.amount_paid, 0) AS amount_paid,
       (SELECT NVL(SUM(aps.amount_remaining), 0)
        FROM   ap_payment_schedules_all aps
        WHERE  aps.invoice_id = ai.invoice_id) AS amount_remaining,
       ai.payment_status_flag
FROM   ap_invoices_all ai
JOIN   ap_suppliers s           ON s.vendor_id       = ai.vendor_id
JOIN   ap_supplier_sites_all ss ON ss.vendor_site_id = ai.vendor_site_id
WHERE  ai.org_id = 204
AND    s.vendor_name LIKE 'Advanced%'
AND    ai.invoice_date BETWEEN TO_DATE('2026-07-01', 'YYYY-MM-DD') AND TO_DATE('2026-09-30', 'YYYY-MM-DD')
AND    ai.cancelled_date IS NULL
ORDER  BY s.vendor_name, ai.invoice_date, ai.invoice_num;

SELECT s.vendor_name,
       ac.check_number,
       ac.check_date,
       ac.currency_code,
       ac.amount,
       ac.status_lookup_code,
       ac.void_date
FROM   ap_checks_all ac
JOIN   ap_suppliers s ON s.vendor_id = ac.vendor_id
WHERE  ac.org_id = 204
AND    s.vendor_name LIKE 'Advanced%'
AND    ac.check_date BETWEEN TO_DATE('2026-07-01', 'YYYY-MM-DD') AND TO_DATE('2026-09-30', 'YYYY-MM-DD')
ORDER  BY s.vendor_name, ac.check_date;

Query 1 fans out: a distribution with two deliveries and two invoices returns four rows, so never SUM its amounts; use Query 2 for totals. AP_INVOICE_DISTRIBUTIONS_ALL rows for variances and tax can also carry PO_DISTRIBUTION_ID; check SELECT DISTINCT line_type_lookup_code on your instance and filter if you want the matched item amount only.

Get a P2P exception report built

Tell us which exceptions matter (received not billed, billed not received, paid on hold) and we scope the queries for your operating units.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Enter the operating unit, a PO number or %, a supplier name pattern and an invoice date range. Query 1 shows the full chain for each PO distribution: delivery transaction and receipt number from RCV_TRANSACTIONS and RCV_SHIPMENT_HEADERS, matched invoice distributions, the invoice header and every payment. Unreceived or uninvoiced distributions still appear because those joins are outer joins. Use Query 2 when you need one row per distribution with totals, and Query 3 for a supplier statement of invoices with remaining balance and payments in the period.

PO number is PO_HEADERS_ALL.SEGMENT1
Receipt number is RCV_SHIPMENT_HEADERS.RECEIPT_NUM
Payment document number is AP_CHECKS_ALL.CHECK_NUMBER

The P2P flow in tables

A purchase order has a header (PO_HEADERS_ALL), lines (PO_LINES_ALL), shipments (PO_LINE_LOCATIONS_ALL) and distributions (PO_DISTRIBUTIONS_ALL), where the charge account and the ordered, delivered and billed quantities live. Receiving writes RCV_TRANSACTIONS: RECEIVE at shipment level, then DELIVER at distribution level, under a receipt header. In R12, an invoice has header (AP_INVOICES_ALL), lines (AP_INVOICE_LINES_ALL) and distributions (AP_INVOICE_DISTRIBUTIONS_ALL), and matching stamps PO_DISTRIBUTION_ID on the distribution. Payments are AP_CHECKS_ALL, linked to invoices through AP_INVOICE_PAYMENTS_ALL, with installments and remaining amounts in AP_PAYMENT_SCHEDULES_ALL.

Worked example and reading the match level

For a supplier like Advanced%, a PO line for 100 units delivered in two receipts of 60 and 40 and billed on one invoice paid by one check returns two rows in Query 1, one per delivery, each repeating the same invoice and check. Query 2 returns one row: ordered 100, delivered 100, billed 100, one invoice and the invoiced amount. The match level column reads the shipment flags: receipt and inspection required means 4-way, receipt only 3-way, neither 2-way. Holds such as quantity received or price come from comparing these quantities and prices at validation.

Gotchas

Fan-out is the classic mistake: each extra one-to-many join multiplies rows, so totals from Query 1 are wrong whenever there are split receipts, multiple invoices or partial payments. Returns and corrections are separate RCV_TRANSACTIONS types, so the DELIVER rows alone can exceed the net delivered quantity; use PO_DISTRIBUTIONS_ALL.QUANTITY_DELIVERED for the net figure. Voided checks keep their AP_INVOICE_PAYMENTS_ALL rows with reversal entries, so look at VOID_DATE. Receipts matched at receipt level (match option R) still carry the PO distribution on invoice distributions, but also RCV_TRANSACTION_ID if you need the specific receipt.

Frequently Asked Questions

What is the P2P query in Oracle Apps R12?

Join PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL and PO_DISTRIBUTIONS_ALL, outer join RCV_TRANSACTIONS on po_distribution_id for deliveries, AP_INVOICE_DISTRIBUTIONS_ALL on po_distribution_id for matched invoices, then AP_INVOICES_ALL, AP_INVOICE_PAYMENTS_ALL and AP_CHECKS_ALL. Query 1 is that chain with supplier and site.

How does a PO link to an AP invoice in R12?

Through the invoice distribution: AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID points to PO_DISTRIBUTIONS_ALL. Invoice lines in AP_INVOICE_LINES_ALL also carry PO header, line and shipment IDs. Always link at distribution level when you need amounts, because a shipment can have several distributions.

What does PAYMENT_STATUS_FLAG mean on AP_INVOICES_ALL?

Y means fully paid, N unpaid and P partially paid. It reflects payments recorded against the invoice's payment schedules. For the open balance use AP_PAYMENT_SCHEDULES_ALL.AMOUNT_REMAINING summed per invoice, as Query 3 does, rather than invoice amount minus amount paid, which ignores discounts and withholding.

How do I build a vendor statement query?

List invoices for the supplier and period from AP_INVOICES_ALL with amount paid and the remaining amount from AP_PAYMENT_SCHEDULES_ALL, then list payments from AP_CHECKS_ALL for the same supplier and period, excluding cancelled invoices and showing void dates. Query 3 returns both lists for a reconciliation against the supplier's own statement.

If you want buyers and AP staff to ask where a PO is in the P2P chain and get the answer from your own EBS instance in plain English on your servers, we can set that up.

Talk to an Expert Explore More Free Tools