When three-way match does not tie

Order, receipt, and invoice are supposed to agree, and finding the ones that do not takes a report spanning all three. No delivered view spans them. The tenant matches documents one at a time and offers no way to see the exceptions as a population.

RuleReport procure-to-pay from one spine at purchase-order distribution grain, so an exception is a filter instead of an investigation.

Nevertrust a cross-subject-area analysis that returned quickly and quietly. Empty and wrong are two of its documented outcomes.

◆ Five documents, no single viewthe process spans subject areas that do not join.

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, which invoices do not match their orders and receipts, has no delivered population-level answer. The match engine works document by document. The reporting layer cannot see the exceptions as a list.

◆ 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.
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 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.

Use case

Problem

No delivered subject area spans requisition to purchase order to receipt to invoice to payment, and cross-subject-area analyses fail with empty or wrong results and no usable error.

What we build

A procure-to-pay spine at purchase-order distribution grain, carrying match status, receipt quantity variance, price variance, and payment status on every row.

What you get

Three-way-match exceptions as a filterable population with money attached, refreshed daily, drillable from the variance total to the document.

Can you list every unmatched invoice right now?

We build the procure-to-pay spine that makes the list one filter.

Talk to us

Terms on this page

ERP: Enterprise resource planning, the system of record for operations and finance.
OTBI: Oracle Transaction 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.