Three-way match is the control every AP team assumes is running correctly until an audit asks them to prove it: does the purchase order agree with what was received, and does what was received agree with what was invoiced? In Yardi Voyager, the answer lives across the MM2 purchasing tables and the AP invoice register — and the join path between them has a couple of traps that will quietly produce a match report that looks clean and isn't.
Here's how the relationship is actually shaped, and where a first-draft query goes wrong.
The three things being matched
A three-way match compares, at the PO line level:
- Ordered — the quantity and cost on the purchase order line itself.
- Received — the quantity a receipt was logged against that line.
- Invoiced — the quantity and amount an AP invoice line applied against that line.
The match "passes" when those three agree within tolerance. It fails — or needs a human look — when a PO line has been invoiced without a matching receipt, received without an invoice yet, or only partially received against what was ordered.
Why this isn't a straight-line chain
The instinct is to model procure-to-pay as a linear pipeline: requisition → PO → receipt → invoice → payment, one stage feeding cleanly into the next. Voyager's actual structure is closer to a hub around the payable than a straight line. The requisition stage is optional — plenty of installs cut purchase orders directly, with no linked requisition. The receiving stage is optional too, present only where the client actually uses receiving workflows. And a payable doesn't have to originate from an invoice register entry at all — it can be entered standalone, or generated through a construction/project payment-certificate path entirely separate from the standard PO-to-invoice flow.
The one edge in that chain that's reliably present: once an AP invoice is posted, it becomes a payable. Everything upstream of the invoice — requisition, PO, receipt — is conditional on how a given client actually uses the purchasing module, which is worth confirming before you assume a PO trail exists at all.
The trap: invoice-to-PO matching happens at the line, not the header
This is the one that breaks match reports most often. It's tempting to join an AP invoice header straight to a purchase order header and call it matched. Don't — the header-level PO reference on an invoice is frequently unpopulated or unreliable. The real, dependable link between an invoice and the purchase order it's paying against sits on the invoice distribution line, which carries a direct pointer to the specific PO line it applies to.
That has two consequences for a three-way match query:
- Match at the line, not the header. A single invoice can apply against several different PO lines (or none). Rolling everything up to invoice-header level before comparing to PO quantities will misstate the match — some lines will look invoiced when they aren't, and vice versa.
- The invoice number itself is commonly a header-only field. Line-level records won't reliably carry their own invoice number — pull it from the header and carry it down through the join, don't expect to find it repeated on every distribution row.
The trap: receiving status lives on the PO line, not a separate receipt table
The natural assumption — that there's a dedicated "goods received note" table sitting between the PO and the invoice — doesn't hold in Voyager's standard MM2 structure. Receiving status is typically tracked directly on the PO line itself: a received-quantity field compared against the ordered-quantity field, alongside a received-date stamp. There's no separate receiving-header table in the standard module; what looks like a plausible candidate for that role is actually the recurring-PO/recurring-WO template mechanism, not a receipt log.
Practically, that means your match logic derives receiving state with a simple comparison rather than a join to a receipt entity:
- Received quantity is zero → not received
- Received quantity is less than ordered → partially received
- Received quantity meets or exceeds ordered → fully received
Building the exception report, not just the match
A three-way match query that only lists matched lines isn't useful — the value is in the exceptions. The shape that actually gets used day to day is a report of open PO lines flagged by what's missing:
- Ordered, not invoiced — no invoice distribution line references this PO line yet.
- Invoiced, not received — an invoice line points at this PO line, but the received quantity is still zero. This is the gap worth chasing first, since it means AP is paying against goods that, per the system, never showed up.
- Partial receipt — received quantity is below ordered; the line isn't closed out.
Structuring the query this way — one row per open PO line, with a computed status column driven by the receipt and invoice joins — turns a wall of transactional data into a worklist an AP analyst can actually act on.
One more grain trap worth knowing before you sum anything
Once you bring the payable and payment side into the query — to see whether a matched, invoiced line has actually been paid — watch the grain. Payable and payment amounts sit at the invoice-header level, not the line level, which means they'll repeat identically across every PO line that a single invoice happens to cover. Sum those header-grain amounts across joined rows and you'll double- or triple-count real dollars. Sum the line-level distribution amounts instead if you need a total that ties out.
Why this is worth getting right in ySQL specifically
None of these traps are exotic — they're the kind of thing a Voyager admin who's built this report five times just knows. The problem is the sixth person who builds it, working from scratch, with no reference that names the actual tables and columns involved. Generic "Yardi SQL scripting" guides describe the concept of procure-to-pay; they don't tell you where receiving status actually lives or that invoice-to-PO matching happens on the distribution line.
PropETL's SQL Assistant carries this as execution-verified schema knowledge — the hub-and-spoke shape of Voyager's procure-to-pay chain, the invoice-register traps, the MM2 purchasing conventions — searchable and ready-made ySQL patterns for both a full procure-to-pay trace and a standalone three-way-match exception report, right inside Claude. It drafts the query grounded in real schema facts and tells you what to verify; you run it in your own ySQL surface.
Start the free 7-day trial — no credit card — and ask it to draft a three-way match report against your own module list.