How to Run an AP Recovery Audit Inside NetSuite

The uncomfortable truth about accounts payable is that it is optimised for throughput, not accuracy. A team's performance is measured on invoices processed and suppliers paid on time. Nothing in that incentive structure rewards someone for noticing that invoice 88214 was also entered as 88214-A six weeks later, or that a supplier has been billing £4.10 for a part the purchase order says costs £3.85.

Recovery audit is a mature industry precisely because those errors are predictable and persistent. The usual external model is contingency-based: a firm takes 20–30% of whatever they recover. That works, but it means a third of your own money buys you nothing you couldn't have found in your own ERP — and the firm leaves with the method.

If you run NetSuite, you already hold every artefact the audit needs: the purchase order, the item receipt, the vendor bill, the payment, the credit memo and the vendor record, all linked. What follows is how to interrogate them.

The four things an AP recovery audit checks

Nearly every recoverable pound falls into one of four patterns:

The first two are the ones you can find today with a query. The second two need contract terms that usually live outside the ERP, so treat them as a manual follow-up once you've proven the method works.

Why NetSuite doesn't catch them on its own

NetSuite does have a duplicate check on vendor bills. It compares the reference number on the bill you're entering against existing bills for that vendor. It's genuinely useful and it stops the obvious cases.

The cases it misses are the ones that survive to become recoverable money:

• The reference differs by a character. INV-88214 and INV88214 are not the same string, so the check doesn't fire.
• The duplicate is entered against a different subsidiary. In a OneWorld account, the check is scoped in a way that routinely lets cross-subsidiary duplicates through — and shared suppliers billing multiple entities are exactly where this happens.
• The currency differs. The same invoice booked once in EUR and once in GBP won't match on amount, and often won't match on reference either.
• The second entry came from a statement, not an invoice. Supplier statements get reconciled by entering "missing" items. Some of those aren't missing.

Similarly, NetSuite will post a bill price variance if you have three-way matching configured. But posting a variance to a GL account is not the same as recovering the money. The variance is recorded, absorbed into cost of sales, and never looked at again.

Query 1 — duplicate payments across subsidiaries

This is the highest-yield query to run first. The signature of a true duplicate is: same vendor, same amount, same currency, different bill reference, close in time. Deliberately do not match on reference — that's the field the native check already covers, and the field real duplicates differ on.

Run this in a SuiteQL query tool or a RESTlet:

-- Candidate duplicate vendor bills: same vendor + amount + currency,
-- different reference, within 90 days of each other.
WITH bills AS (
  SELECT
    t.id                          AS bill_id,
    t.tranid                      AS bill_ref,
    t.trandate                    AS bill_date,
    t.entity                      AS vendor_id,
    BUILTIN.DF(t.entity)         AS vendor_name,
    BUILTIN.DF(t.subsidiary)     AS subsidiary,
    t.currency                    AS currency_id,
    ABS(t.foreigntotal)          AS bill_amount
  FROM transaction t
  WHERE t.type   = 'VendBill'
    AND t.voided = 'F'
    AND t.trandate >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
)
SELECT
  a.vendor_name,
  a.subsidiary                    AS subsidiary_a,
  b.subsidiary                    AS subsidiary_b,
  a.bill_ref                      AS bill_a,
  b.bill_ref                      AS bill_b,
  a.bill_date                     AS date_a,
  b.bill_date                     AS date_b,
  a.bill_amount                   AS amount_at_risk,
  ABS(a.bill_date - b.bill_date) AS days_apart
FROM bills a
JOIN bills b
  ON  a.vendor_id   = b.vendor_id
  AND a.currency_id = b.currency_id
  AND a.bill_amount = b.bill_amount
  AND a.bill_id     < b.bill_id          -- each pair once, not twice
  AND a.bill_ref   <> b.bill_ref
  AND ABS(a.bill_date - b.bill_date) <= 90
ORDER BY a.bill_amount DESC

Test in a sandbox first. Field availability varies by account configuration — if you don't use multi-currency, drop currency_id and use t.foreigntotal or t.total as appropriate.

The a.bill_id < b.bill_id condition is what stops every pair appearing twice, once in each direction. The 90-day window is a starting point: widen it to 365 for suppliers you pay irregularly, tighten it to 30 for high-volume ones.

Reading the output. Not every row is a duplicate — and you should expect a lot of rows. On a typical mid-size account, a two-year window returns somewhere in the region of 200–400 candidate pairs, and only a small fraction are genuine. Recurring identical charges — rent, monthly retainers, standing licence fees — will fill most of the list and are entirely legitimate. Sort by amount, work down from the top, and exclude vendors whose billing is genuinely fixed and periodic. The real finds cluster in the top 5% of rows by value, which is why the sort order matters more than the filter.

That sifting is the honest cost of doing this yourself, and it's worth being clear-eyed about it: the query takes ten minutes to run and the review takes a day or two. Doing it once is very achievable. Doing it every week is where it stops being a spare-afternoon job.

Query 2 — supplier overcharges against the PO

The second pattern is a supplier billing above the price on the purchase order. This one compounds: an unnoticed 6% rise on a regularly-purchased part costs you on every future order, so finding it is worth more than the historical claim alone.

NetSuite links bill lines back to PO lines through nexttransactionlinelink, which is what makes this query possible:

-- Vendor bill lines billed above the linked purchase order rate.
SELECT
  BUILTIN.DF(t.entity)              AS vendor,
  BUILTIN.DF(bl.item)               AS item,
  t.tranid                          AS bill_ref,
  t.trandate                        AS bill_date,
  bl.quantity,
  pl.rate                           AS po_rate,
  bl.rate                           AS billed_rate,
  (bl.rate - pl.rate)               AS rate_variance,
  ROUND((bl.rate - pl.rate) * bl.quantity, 2) AS value_at_stake
FROM transaction t
JOIN transactionline bl
  ON bl.transaction = t.id
JOIN nexttransactionlinelink ln
  ON ln.nextdoc = t.id AND ln.nextline = bl.id
JOIN transactionline pl
  ON pl.transaction = ln.previousdoc AND pl.id = ln.previousline
WHERE t.type   = 'VendBill'
  AND t.voided = 'F'
  AND bl.rate  > pl.rate
  AND (bl.rate - pl.rate) * bl.quantity > 100   -- materiality floor
ORDER BY value_at_stake DESC

The materiality floor of 100 is arbitrary — set it where a claim is worth the effort of making. Below roughly £50 per line, the admin usually costs more than the recovery.

Run the same query grouped by vendor and item rather than by bill, and you get something more useful than a list of claims: a picture of which suppliers drift on price systematically. That's a negotiation input, not just a recovery one.

Turning a finding into a claim your supplier will honour

This is where most internal recovery efforts quietly die. A query result is not a claim. A supplier's accounts team receiving "we think you overcharged us" will ask for specifics, and if the answer takes three days to assemble, the chase stops.

A claim that gets paid contains five things:

1. The transactions. Both bill numbers, both dates, both amounts, both internal IDs.
2. The contradiction. "PO 4412 line 3 states £3.85. Bill INV-88214 line 3 billed £4.10." One sentence.
3. The value. The exact figure claimed, and how it was calculated.
4. The remedy requested. Credit note, refund, or offset against the next invoice — say which.
5. Your reference and a date. So it can be chased, and so it ages visibly if it isn't answered.

Then track it. Identified → validated → submitted → agreed → credited. Findings that aren't tracked to the credit note don't become cash, and the recovery rate on an untracked claim pack tends toward zero within about six weeks.

One-off audit vs continuous assurance

Run the queries above once and you'll find a stock of historical error — two or three years' worth, recoverable in a single push. That's a real number and usually a pleasant surprise.

The problem is that it regenerates. The same supplier that drifted on price will drift again; the same statement-reconciliation habit that created a duplicate will create the next one. A one-off audit recovers the stock. It does nothing about the flow.

Running these checks on a schedule — weekly against the last 90 days — changes the economics substantially. Findings surface while the invoice is still fresh in the supplier's memory, claims are settled faster, and the recovery rate is materially higher than on a two-year-old transaction. It also shifts the conversation from recovery to prevention, which is where the larger number is.

That's the difference between an audit and assurance, and it's worth being deliberate about which one you're actually setting up.