Ask a Voyager admin "what do we owe each vendor right now" and they can usually get you an answer from the AP module's UI in a few clicks. Ask the same question in ySQL and the first attempt almost always goes looking for a table called something like APPAYABLE. It doesn't exist. Payables aren't a separate table — they're a row type inside the one table that carries most of Voyager's financial activity.
Payables live in TRANS, not an AP-specific table
TRANS is Voyager's general transaction table, and a payable is TRANS where iType = 3, one value out of the complete 14-code iType map Voyager uses to distinguish charges, receipts, payables, payments, and the rest inside the same table. If your query doesn't filter iType, you're not looking at payables — you're looking at every transaction type Voyager records, blended together.
The post-period bucket is TRANS.UPOSTDATE, and that's what you window a "vendor balance for this period" query on — not the document date, the post date.
Vendor identity runs through a polymorphic join
The vendor on a payable doesn't sit on TRANS as a plain name field. TRANS.HPERSON points at VENDOR.HMYPERSON, and VENDOR in turn resolves to a name via PERSON.HMY. This is Voyager's broader HMYPERSON subtype trap showing up in AP specifically: person-shaped entities (vendors, tenants, owners, employees) are subtypes hung off a shared person table, and skipping the subtype join or joining TRANS straight at PERSON gets you either nothing or the wrong entity.
What "open" actually has to mean
An open balance isn't a status flag you filter on directly — it's arithmetic. STOTALAMOUNT is what was billed, SAMOUNTPAID is what's been paid against it, and the open balance is the difference. A verified vendor rollup sums both sides and keeps only vendors where that difference is nonzero, rather than trusting a single "open" column to already mean what you want it to mean. (There is a separate BOPEN flag on the payable itself for single-document open/settled status — useful for a document-level view, but a vendor rollup wants the summed arithmetic, not the flag from one row.)
Negative rows aren't bugs
Credit notes and vendor advances post as negative billed or paid amounts against the same vendor. A query that filters them out to "clean up the numbers" is deleting real AP activity — a vendor with a return or an advance on file is supposed to show a negative line, and the rollup should let it net against the rest.
The double-counting trap: know what grain you're summing
Voyager's procure-to-pay chain isn't a straight line — a payable can originate from the AP invoice register, from a standalone direct AP entry with no invoice at all, or (on the construction/projects side) from a payment-certificate layer. That matters for a vendor rollup because the moment you go looking for the invoice or PO detail behind a payable, you drop to a finer grain — and invoice and payment amounts at that finer level repeat per PO line an invoice covers. Summing those repeated line-level joins back up double-counts the payable. A clean payable-by-vendor rollup stays at the TRANS grain and doesn't join out to invoice or PO detail at all; that join only belongs in a different report, one built specifically to drill into a single vendor's documents.
The verified pattern
SELECT RTRIM(CONCAT(per.SFIRSTNAME, ' ', v.ULASTNAME)) AS vendor,
COUNT(*) AS payable_docs, SUM(t.STOTALAMOUNT) AS total_billed,
SUM(t.SAMOUNTPAID) AS total_paid, SUM(t.STOTALAMOUNT - t.SAMOUNTPAID) AS open_balance
FROM TRANS t
JOIN VENDOR v ON t.HPERSON = v.HMYPERSON
JOIN PERSON per ON v.HMYPERSON = per.HMY
WHERE t.ITYPE = 3 AND t.UPOSTDATE >= '{PERIOD_START}' AND t.UPOSTDATE < '{PERIOD_END}'
GROUP BY per.SFIRSTNAME, v.ULASTNAME
HAVING SUM(t.STOTALAMOUNT - t.SAMOUNTPAID) <> 0
ORDER BY open_balance DESC
Filter by iType, join vendor through the person subtype, bucket by post date, keep the negatives, and stop at the TRANS grain. That's the whole pattern — and it's the difference between a report that matches the AP module's own numbers and one that's close but wrong in a way nobody notices until reconciliation.
This query pattern — along with the rest of Voyager's procure-to-pay chain, the iType map, and the join conventions behind them — is one of the execution-verified templates in the PropETL SQL Query Assistant, queryable in plain English inside Claude. It ships in every tier, including the free 7-day trial. More on what it covers at the SQL Assistant page.