Why Your NetSuite AP Aging Never Ties to the General Ledger

MokuHubAugust 6, 20269 min read

netsuiteaccounts-payablesuiteqlfinancial-reportingclose

Two numbers are supposed to be the same number.

The accounts payable balance on the balance sheet says you owe 820,176.55. The AP Aging Summary, added up across every vendor, says 760,442.18. Somewhere in there is 59,734.37 that exists in the general ledger and does not appear on any vendor's line.

The instinct is to go looking for a missing bill. That instinct is wrong, and following it costs people days during close. There is no missing bill. The two reports are answering different questions, and once you see which questions, the difference stops being a mystery and becomes a list you can produce in about thirty seconds.

Start by asking what actually posts to payables

Before anything else, look at what touches the AP account. Not what should touch it. What does.

SELECT T.type,
       SUM(TAL.amount) AS gl_amount,
       COUNT(DISTINCT T.id) AS txn_count
FROM TransactionAccountingLine TAL
JOIN Account A ON TAL.account = A.id
JOIN Transaction T ON TAL.transaction = T.id
WHERE TAL.posting = 'T'
  AND A.accttype = 'AcctPay'
GROUP BY T.type
ORDER BY ABS(SUM(TAL.amount)) DESC

On a mid-sized account that has been live a few years, the answer is not two transaction types. It looks more like this:

VendBill    -19,240,118.55    5,104
VendPymt     18,602,447.10    4,338
ExpRept        -712,905.44      868
VendCred        604,330.28      371
Commissn        -61,004.19       94
Journal         -12,880.75      142
Deposit             -45.00        1

Seven types. Add that column up and you get -820,176.55, which is the balance sheet figure exactly, to the cent. The general ledger is fine. It is always fine, because that column is the general ledger.

The negative sign is the first thing that catches people out and it is not an error. Payables is a liability with a credit balance, so summing amount on it gives you a negative number. If you have ever queried this and assumed the sign meant your join was wrong, it did not.

Now look at what is in that list. Expense reports post to payables, because an unreimbursed expense report is money you owe an employee and it lives in the same account as money you owe a vendor. Commissions post there. And 142 journal entries post directly to payables with no vendor bill behind them at all.

None of those appear on an AP Aging report, because an AP Aging report is organised by vendor and those transactions have no vendor to organise them under. They are not missing. They were never in scope.

Trap one: `amount` and `amountunpaid` answer different questions

The obvious fix is to widen the aging query to include the other types. That fix makes things worse, and watching it fail is the fastest way to understand what is really going on.

Aging works from a different field. Not amount, which is a posting, but amountunpaid, which is the open balance remaining on a document.

A practical note before you write the query, because this one costs an hour if you get it backwards: amountunpaid lives on TransactionAccountingLine, not on transactionLine. Reaching for the transaction line table is the natural move and it fails with an unsupported field error.

So, open balance grouped by type, restricted to the payables account:

SELECT T.type,
       SUM(COALESCE(TAL.amountunpaid, 0)) AS open_amount,
       COUNT(DISTINCT T.id) AS txn_count
FROM TransactionAccountingLine TAL
JOIN Transaction T ON TAL.transaction = T.id
JOIN Account A ON TAL.account = A.id
WHERE TAL.posting = 'T'
  AND A.accttype = 'AcctPay'
  AND T.voided = 'F'
GROUP BY T.type
ORDER BY ABS(SUM(COALESCE(TAL.amountunpaid, 0))) DESC

VendBill    760,442.18    5,104
Journal     138,229.60      142
ExpRept      66,715.03      868
Commissn        198.45       94
VendCred          0.00      371
VendPymt          0.00    4,291
Deposit           0.00        1

That totals 965,585.26. The balance sheet said 820,176.55. Including every type does not close the gap of 59,734.37, it opens a new one of 145,408.71 in the opposite direction.

Which is the actual finding, and it is worth stating plainly because most of the guidance on this topic never gets to it:

amountunpaid does not reconcile to the general ledger. It is not supposed to. It is a document-level open balance, not a posting. Summing postings gives you a ledger balance. Summing open balances gives you the total of what is outstanding on documents, and those two things describe the same debt from different angles and are not obliged to agree.

If you need a number that ties to the balance sheet, aggregate amount. If you need to know who you owe and how late you are, aggregate amountunpaid. Deciding which question you are asking is the whole job, and the reason people lose days to this is that both queries look like they are asking the same one.

Look at the vendor credits row while you are here. 371 credits, open amount zero, every one of them. In the general ledger those same credits move 604,330.28. A credit debits payables the moment it posts, but it does not reduce anybody's open balance until somebody applies it to a specific bill. Between posting and application it is fully visible to the ledger and completely invisible to aging.

Trap two: journal entries carry an open balance

The Journal row above deserves its own paragraph, because it surprises people who have been in NetSuite for years.

142 journal entries posted to payables, carrying 138,229.60 of open balance between them. Not zero. Journals have an amountunpaid and it is populated.

There is no vendor-facing report anywhere that will show you those. They have no vendor, so they are not on the aging. They are usually accruals, reclasses and close adjustments, which means they are also the entries most likely to be posted in a hurry in the last two days of the month by whoever is closest to the deadline.

Whether that 138,229.60 belongs in your payables at all is an accounting question rather than a query question, and the answer is usually that some of it does and some of it was a reclass that should have been reversed. The point is that until you run the query, nobody in the building knows the number exists.

Trap three: voided is not zero, and it moves a quarter of your balance

This is the one that will change how you write every payables query you write from now on.

Look at the two queries above again. The first has no voided filter. The second has T.voided = 'F', because filtering out voided transactions is basic hygiene and every SuiteQL guide tells you to do it.

Run the balance both ways.

SELECT T.voided,
       COUNT(DISTINCT T.id) AS cnt,
       SUM(TAL.amount) AS gl_amount
FROM TransactionAccountingLine TAL
JOIN Transaction T ON TAL.transaction = T.id
JOIN Account A ON TAL.account = A.id
WHERE TAL.posting = 'T'
  AND A.accttype = 'AcctPay'
GROUP BY T.voided

With voided transactions excluded, payables is 1,026,730.85. With them included, it is 820,176.55. One clause in the WHERE moved the balance by 206,554.30, which is a quarter of the number.

Narrow it to what the filter is discarding and it is a single row:

VendPymt    47    206,554.30

Forty-seven voided bill payments, still posting 206,554.30 of debits against payables. The void flag is set on the record. The accounting lines are still there and still posting, because voiding is not deletion and the ledger has to keep a record of both sides.

So the balance sheet, which includes those postings, and your query, which excludes them, are computing different things. Neither is wrong on its own. The mistake is applying the filter to one side of a comparison and not the other, which is easy to do because the two sides are usually written weeks apart by different people.

The 59,734.37 at the top of this article was measured exactly that way: a ledger balance that included voided postings, against an aging total that excluded them. Line the filters up and it becomes 61,145.59.

Do not read that as the corrected answer. Both figures are the difference between two quantities that were never required to match. What the exercise actually proves is that a difference which moves when you change a filter is not telling you anything about your payables. It is telling you about your query.

The query that ties

If what you want is a payables balance that agrees with the balance sheet, this is it, and there is nothing clever in it:

SELECT SUM(TAL.amount) AS ap_balance
FROM TransactionAccountingLine TAL
JOIN Account A ON TAL.account = A.id
JOIN Transaction T ON TAL.transaction = T.id
JOIN AccountingPeriod AP ON T.postingperiod = AP.id
WHERE TAL.posting = 'T'
  AND A.accttype = 'AcctPay'
  AND AP.enddate <= TO_DATE('2026-06-30', 'YYYY-MM-DD')

No voided filter, because voided documents still have postings and the balance sheet counts them. No transaction type filter, because all seven types are real payables. Cumulative up to the period end rather than within a range, because payables is a balance sheet account and balance sheet accounts accumulate from inception. Filter an income statement account the same way and you will overstate it by every year the company has existed.

One more field to watch. TAL.posting and T.posting are different columns on different tables and both exist. The one that decides whether an accounting line hit the ledger is on the accounting line. Filtering the transaction instead mostly works and quietly does not in the cases you care about.

Where this stops working

Some honest edges, because a reconciliation you half trust is worse than none.

One AP account is an assumption. Everything above filters on accttype = 'AcctPay', which is right, but if your chart of accounts has several payables accounts you want them broken out by acctnumber rather than added together, and a difference in one can hide inside the total of another.

Multi-currency is not covered here. In a single-currency account amount is unambiguous. Once you have foreign currency bills there is a base currency amount, a transaction currency amount and a revaluation that moves the balance without any document changing, and reconciling that is a longer article than this one.

Subsidiaries need to be in the grouping. A consolidated total that reconciles can be made of two subsidiaries that individually do not, and the elimination entries are exactly the ones nobody wants to explain in an audit.

There is a field trap in doing that, and it is the mirror image of the amountunpaid one. TransactionAccountingLine has no subsidiary column, so grouping by it fails outright rather than returning something wrong. Join back to the transaction line, on both the transaction and the line id:

JOIN transactionLine TL
  ON TL.transaction = TAL.transaction
 AND TL.id = TAL.transactionline

then group by BUILTIN.DF(TL.subsidiary). Joining on the transaction alone is the version that compiles, runs, and quietly multiplies every accounting line by the number of lines on the document.

None of this replaces the close process. It tells you where a difference is. It does not tell you which side of it is correct, and that is a judgement about your own books.

What to keep

Three things, and all three are yours whether you use any tooling of ours or not.

Decide which question you are asking before you write the query. amount reconciles to the ledger, amountunpaid describes open documents, and no amount of adjusting the second one will turn it into the first.

Run the decomposition by transaction type before investigating anything. It takes seconds, it costs nothing, and most of the time the difference is standing right there in a row somebody forgot payables could contain.

Apply the same filters to both sides of any comparison, especially voided. A quarter of a balance is a lot of money to lose to a WHERE clause that looked like good practice.

If you want this without writing SQL, the financial analysis in MokuBot does the same decomposition from a plain question and reads your own chart of accounts to do it, since it runs inside your NetSuite session under your own permissions. The queries above are the entire mechanism though, and they work in any SuiteQL console you already have open.

If this kind of thing is your week, the same class of problem in saved searches is written up here: results that are confidently wrong rather than obviously broken, which is the expensive kind.