When three-way match does not tie
Order, receipt, and invoice are supposed to agree. Oracle lists the invoices on matching hold, but no delivered view starts from the order, so receipts nobody invoiced and order lines nobody received stay out of sight.
◆ Five documents, no single viewthe process spans subject areas that combine only on shared dimensions.
Requisition, purchase order, receipt, invoice, payment: the process is one chain, and the delivered reporting splits it across subject areas with no single one spanning it. Combining them means a cross-subject-area analysis, and those break on non-conforming dimensions: fields that exist in one subject area and not the other.
The failure modes are empty results and wrong results, produced without a usable error. This is not folklore; Oracle’s own authoring guidance for cross-subject-area analyses documents the error modes and the unexpected results.
So the question every controller asks gets half an answer. The Payables Matching Hold Detail Report lists invoices with matching holds and their releases. It starts from the invoice, so a receipt nobody invoiced or an order line nobody received never appears on it, and each run shows only the holds standing that day.
◆ The workarounds and their wallseach accepted answer stops short.
| Workaround | Where it stops |
|---|---|
| Cross-subject-area analysis | Breaks on non-conforming dimensions, returning empty or wrong without a usable error. The documented outcome, not an edge case. |
| Hand-written SQL in the publisher tool | The accepted community answer: a multi-table query someone now maintains, subject to the caps on the export ceiling. |
| Export each document type, join in spreadsheets | Five extracts, hand-matched keys, and a workbook that is wrong the day after it is finished. |
| Run the Payables Matching Hold Detail Report | Lists every invoice on matching hold. Receipts and order lines with no invoice never appear, and yesterday’s holds are gone once released. |
| Ask payables to research one invoice | Works for one invoice. The population question stays unanswered, which is where the money is. |
◆ The owned spineone grain, every document attached, exceptions as a filter.
The build is a procure-to-pay spine in your warehouse at purchase-order distribution grain: one row per distribution, carrying its requisition reference, ordered and received quantities, matched invoices, and payment status. Match status, receipt quantity variance, and price variance are columns on the row. Three-way-match exceptions stop being an investigation and become a filter.
-- the exception population, one filter
SELECT po_number, line_number, match_status,
receipt_qty_variance, price_variance, payment_status
FROM fct_procure_to_pay
WHERE match_status <> 'matched'
ORDER BY price_variance DESC
The three documents join on fct_document_line, drawn on the Oracle owned-model reference.
The finance side of the same discipline, tying the payables subledger to the ledger, has its own page: payables does not tie. Same spine, other end of the chain.
- ERP
- Enterprise resource planning, the system of record for operations and finance.
- OTBI
- Oracle Transactional Business Intelligence, the embedded reporting tool inside Fusion.
- SQL
- Structured query language, the language the hand-written workaround is maintained in.
- subject area
- A packaged set of fields the embedded reporting tool can query.
- non-conforming dimension
- A field present in one subject area but not another, where cross-area joins break.
- three-way match
- Checking that order, receipt, and invoice agree before paying.
- distribution
- The accounting-level line of a purchase order. The finest useful grain.
- price variance
- Invoice price minus order price, times quantity.
- spine
- One table at one grain that every related document attaches to.