Vendor Statement Reconciliation in NetSuite: A Practical Method

Ask most AP teams about statement reconciliation and you'll get a slightly guilty answer. It's on the list. It gets done for the big suppliers, sometimes, when there's a quiet week. Nobody enjoys it and nobody can point to much that came of it.

That's a shame, because it's the only routine control that looks at your payables from the outside. Every other check you run interrogates your own records against your own records. A supplier statement is an independent second opinion, provided free, by the only other party who knows what you owe.

Why the usual method backfires

The standard approach goes like this:

1. Statement arrives.
2. Work down it line by line.
3. For each line, search NetSuite for the invoice number.
4. If it isn't found, enter it.

Step 3 is where it fails. You're searching on the supplier's reference format, against your own reference format, and those differ constantly — 4471 against INV-4471, a leading zero, a branch prefix your team strips on entry. The search returns nothing. The item looks missing. It gets entered.

It wasn't missing. It was recorded three weeks ago. You've now got two bills for one invoice, and because they have different references, NetSuite's duplicate check has no reason to object. As covered in detecting duplicate payments across subsidiaries, this workflow is the origin of more duplicates than every other cause combined.

Invert the process

Four steps, in this order:

1. Build your position first. Extract every open item for that supplier as at the statement date — bills, credits, payments, unapplied amounts — from NetSuite, before you look at the statement.
2. Reconcile the totals. Your closing balance against theirs. If they agree, you're finished in ninety seconds. This happens more often than people expect and it's why the method is quick.
3. Match on amount and date, not reference. Only when totals disagree. Amounts are unambiguous; references are a formatting lottery.
4. Classify every remaining gap before entering anything at all.

Building your side with SuiteQL

This query produces your ledger position for a supplier as at a chosen date — the document you compare the statement against.

-- Purchase ledger position for one vendor as at a statement date.
-- Bills, credits and payments in one list, oldest first.
SELECT
  CASE t.type
    WHEN 'VendBill' THEN 'Invoice'
    WHEN 'VendCred' THEN 'Credit'
    WHEN 'VendPymt' THEN 'Payment'
    ELSE t.type END                          AS doc_type,
  t.tranid                                    AS our_ref,
  t.trandate                                  AS doc_date,
  t.duedate,
  BUILTIN.DF(t.currency)                     AS currency,
  ROUND(ABS(t.foreigntotal), 2)               AS doc_amount,
  ROUND(NVL(ABS(t.foreignamountunpaid), 0), 2) AS still_open,
  CASE WHEN NVL(ABS(t.foreignamountunpaid), 0) = 0
       THEN 'Settled' ELSE 'Open' END            AS status
FROM transaction t
WHERE t.entity   = /* vendor internal id */ 1234
  AND t.voided   = 'F'
  AND t.type     IN ('VendBill', 'VendCred', 'VendPymt')
  AND t.trandate <= TO_DATE('2026-07-31', 'YYYY-MM-DD')
  AND t.trandate >= TO_DATE('2025-08-01', 'YYYY-MM-DD')
ORDER BY t.trandate, t.tranid

Replace the vendor internal id and the two dates. Twelve months of history is usually enough — most statement differences are recent, and older ones will have surfaced on a previous statement.

Add this second query to catch the thing statements most often reveal, and which your own ledger will never tell you about on its own:

-- Vendor credits raised but never applied — money already agreed,
-- not yet drawn down. Across all vendors, largest first.
SELECT
  BUILTIN.DF(t.entity)                       AS vendor,
  t.tranid                                    AS credit_ref,
  t.trandate,
  BUILTIN.DF(t.currency)                     AS currency,
  ROUND(ABS(t.foreigntotal), 2)               AS credit_value,
  ROUND(ABS(t.foreignamountunpaid), 2)       AS unapplied,
  ROUND(CURRENT_DATE - t.trandate)           AS days_outstanding
FROM transaction t
WHERE t.type   = 'VendCred'
  AND t.voided = 'F'
  AND ABS(NVL(t.foreignamountunpaid, 0)) > 0
ORDER BY unapplied DESC

Run this one across all vendors before you reconcile anything. It takes seconds and frequently finds more than the reconciliation itself.

Matching, and what the gaps mean

With your position in one column and the statement in the other, match on amount first, date second, reference last. Amount is the only field both parties record identically.

In a spreadsheet this is a lookup on rounded amount within a date tolerance of about ten days. Most lines match immediately. What's left is the interesting part, and it is usually a dozen rows or fewer.

The five gap types

Note that two of these five find money and one finds an error you made. The "in ledger, not on statement" column is uncomfortable but it's the most valuable single output of the exercise, because it's the only routine process that catches a duplicate before you pay it rather than two years afterwards.

Which suppliers are worth doing

Reconciling every supplier every month is a poor use of a finance team. Prioritise on where differences are likely and material:

• Top 20 by annual spend. Monthly. Non-negotiable — this is most of your exposure.
• Anyone with frequent credit notes. High credit volume means high unclaimed-credit risk.
• Anyone billing multiple subsidiaries. Cross-entity duplicates concentrate here.
• Anyone whose invoices arrive by email as PDFs and get manually keyed. Manual entry is where reference drift originates.
• Anyone you've had a pricing dispute with in the last year. Disputes leave residue.

That's typically 25–40 suppliers in a mid-market business, which is a manageable monthly rhythm — and it will cover something like 85% of the value.

The honest limitation of doing this manually is that it only ever reaches the suppliers on your list. The unclaimed credit sitting with supplier 214, who you spend £40,000 a year with and never reconcile, stays unclaimed indefinitely. That's the argument for automating the ledger-position half of the work: not because the method is hard, but because the coverage is what actually determines the total.