Closing 1.8 Million Dead Invoices in NetSuite

For years, an upstream fulfillment system had been writing invoices into NetSuite that were never meant to collect against the customer balance. They accumulated. By the time we were brought in, the client was carrying on the order of 1.8 million open invoices that existed only as dead weight — and the integration kept adding more every day.

The cost was a tax on every accounting process, paid continuously: saved searches paginate through every open row, aging reports sum it, the collections team scrolls past it, statement runs render it, and every month-end reconciliation has to account for it before it can tie out. The receivables ledger is supposed to answer one question — what do customers actually owe? — and the honest answer was buried under millions of records that would never be paid. The real AR was a rounding error against the noise.

The brief was easy to state and hard to do:

• Close the in-scope invoices to a zero balance
• Leave the general ledger net-neutral — the cleanup must not move the P&L or the balance sheet
• Preserve a complete audit trail — every closure traceable, every run reproducible
• Don't take production down — this is a live ERP the business runs on every day

The story of the engagement is really the story of how — because the obvious ways to do it, the platform simply refused to allow.

Attempt One: the Textbook Approach — and the Wall It Hit

The standard NetSuite pattern is a credit memo per group of invoices, applied through the record API. We built exactly that as a Map/Reduce: scope the open invoices with a tightly filtered saved search, group by customer and AR account, create credit memos, mark each invoice applied. It worked cleanly in test on small populations. Against the real data, it ran into a stack of platform behavior — each limit discovered by hitting it:

• The apply sublist filters by mainline AR account, but correct accounting credits the account each invoice actually posted to — so valid invoices silently never appear on the credit memo built to close them
• Dynamic mode is mandatory — in standard mode the apply sublist silently returns zero lines and a "successful" save closes nothing
• Large credit memos degrade past ~1,000 lines, with a hard 5,000-line submission ceiling
• The apply sublist displays at most 10,000 lines. The client's two largest customers each carried more than 10,000 older open invoices that filled the visible window first — so the in-scope invoices could never appear at all, and there is no SuiteScript path past it

For the two customers that mattered most, the record-API approach could not reach the target invoices on any number of runs. The design was sound; the platform ceiling made it the wrong tool. Recognizing that before forcing a doomed approach into production was the turning point of the engagement, not a setback.

The Approach That Worked: CSV Import by Reference

NetSuite's CSV Import applies a credit line to a specific invoice by internal ID, written directly into a file. It never renders the interactive apply sublist — so the 10,000-line display window, the line-count ceiling, the mainline-account filtering, and the dynamic-mode trap all simply don't exist for it. The script stopped creating credit memos through the record API and started generating the CSV files a saved import map ingests, with an identical accounting outcome.

Each credit memo becomes three linked files — a body file (one row per memo), an item file (one row per memo), and an apply file (one row per invoice being closed) — stitched together by a deterministic External ID derived from the data: customer, AR account, subsidiary, run tag, index. Because identity is derived rather than assigned, any run can be reconstructed after the fact from its IDs alone, and an accidental re-run collides on External ID instead of silently double-closing invoices.

The One Number That Decides Everything

CSV import is atomic per credit memo: one bad apply line — typically an invoice that changed between search and import — rolls back the entire memo with only a generic error. That turns batch size into a blast-radius decision. At 4,999 applies per memo, a 0.1% stale-data rate produced roughly a 99% per-memo failure rate; at 100 applies per memo, the same stale rate inverts to about 90% success, because one bad line now costs 100 closures instead of 5,000. The pipeline sizes every ceiling the same way — 24,000 apply rows per file against the 25,000-line CSV cap, with a file-size fallback behind it: find the platform's real limit, then sit deliberately below it.

Net-Zero by Design

Each closure memo carries a single dedicated "AR Invoice Closure" item posting Dr Invoice-Correction-Revenue / Cr AR; applying it zeroes the invoice, and a manual journal entry washes the contra side, holding net GL impact at zero. The input search reads the AR posting line, so each credit lands on the exact receivable account the invoice posted to, and per-penny rounding at capture guarantees every memo's apply lines total exactly to the memo amount.

Safe to Operate, Not Just Correct

Both scripts run a test mode that produces the exact CSVs a live run would — reviewed first, then authorized. Runs halt cleanly between batches and resume from the remaining set. A dedicated rollback script finds closure memos by their dedicated item, deletes them (automatically re-opening the invoices), and writes its own audit CSV. Every rollout followed the same staircase: test-mode dry run, scoped live run, reconciliation against the GL, then widen.

The full in-scope population — roughly 1.8 million invoices — was closed through this pipeline, with the general ledger held net-neutral, a per-run audit trail — generated CSVs kept in the File Cabinet, a JSON manifest per run, structured execution logs, and an email summary per run — and a rollback path that was exercised, not just theorized. A small tail of edge-case invoices was resolved manually to finish the sweep, and a handful of legitimately disputed invoices were deliberately left open by design.

The downstream effect is the part the business actually feels: every invoice closed is one fewer row dragging on saved searches, aging reports, collections, statement runs, and the monthly close. The receivables ledger is back to being what it's supposed to be — a statement of what customers actually owe — instead of a haystack with the real balances hidden inside it.