Developer ToolsFree generatorPDF, Excel, Word export

ERP Cutover Reconciliation SQL Generator (GL, AR and Inventory)

After every trial load, someone must prove the new ERP holds the same balances as the old one. This generator writes the comparison queries for general ledger, open receivables and inventory so you can run them after each mock cutover and show clean variances to Finance.

Fill in

Legacy GL balance table

New ERP GL balance table

Account column

Period column

Balance amount column

Cutover period

Legacy open AR table

New open AR table

Customer column

Open amount column

Legacy inventory table

New inventory table

Item column

Quantity column

Tolerance

Generated code

Download:

1. Trial balance variance by account

SELECT COALESCE(l.account_no, n.account_no) AS account_no,
       COALESCE(l.total_amt, 0) AS legacy_amt,
       COALESCE(n.total_amt, 0) AS new_amt,
       COALESCE(n.total_amt, 0) - COALESCE(l.total_amt, 0) AS variance
FROM (SELECT account_no, SUM(balance_amt) AS total_amt
      FROM legacy.gl_balances
      WHERE fiscal_period = '2026-12'
      GROUP BY account_no) l
FULL OUTER JOIN (SELECT account_no, SUM(balance_amt) AS total_amt
      FROM erp.gl_balances
      WHERE fiscal_period = '2026-12'
      GROUP BY account_no) n
  ON l.account_no = n.account_no
WHERE ABS(COALESCE(n.total_amt, 0) - COALESCE(l.total_amt, 0)) > 0.01
ORDER BY account_no;

2. Open AR variance by customer

SELECT COALESCE(l.customer_no, n.customer_no) AS customer_no,
       COALESCE(l.total_open, 0) AS legacy_open,
       COALESCE(n.total_open, 0) AS new_open,
       COALESCE(n.total_open, 0) - COALESCE(l.total_open, 0) AS variance
FROM (SELECT customer_no, SUM(open_amt) AS total_open
      FROM legacy.ar_open_items GROUP BY customer_no) l
FULL OUTER JOIN (SELECT customer_no, SUM(open_amt) AS total_open
      FROM erp.ar_open_items GROUP BY customer_no) n
  ON l.customer_no = n.customer_no
WHERE ABS(COALESCE(n.total_open, 0) - COALESCE(l.total_open, 0)) > 0.01
ORDER BY customer_no;

3. Inventory on-hand variance by item

SELECT COALESCE(l.item_no, n.item_no) AS item_no,
       COALESCE(l.total_qty, 0) AS legacy_qty,
       COALESCE(n.total_qty, 0) AS new_qty,
       COALESCE(n.total_qty, 0) - COALESCE(l.total_qty, 0) AS variance
FROM (SELECT item_no, SUM(qty_onhand) AS total_qty
      FROM legacy.inv_onhand GROUP BY item_no) l
FULL OUTER JOIN (SELECT item_no, SUM(qty_onhand) AS total_qty
      FROM erp.inv_onhand GROUP BY item_no) n
  ON l.item_no = n.item_no
WHERE ABS(COALESCE(n.total_qty, 0) - COALESCE(l.total_qty, 0)) > 0.01
ORDER BY item_no;

4. Row count check

SELECT 'GL' AS object_name,
       (SELECT COUNT(*) FROM legacy.gl_balances WHERE fiscal_period = '2026-12') AS legacy_rows,
       (SELECT COUNT(*) FROM erp.gl_balances WHERE fiscal_period = '2026-12') AS new_rows
UNION ALL
SELECT 'Open AR', (SELECT COUNT(*) FROM legacy.ar_open_items), (SELECT COUNT(*) FROM erp.ar_open_items)
UNION ALL
SELECT 'Inventory', (SELECT COUNT(*) FROM legacy.inv_onhand), (SELECT COUNT(*) FROM erp.inv_onhand);

Table and column names are placeholders: replace them with the real names in your legacy and target databases. If the two systems sit in different databases, extract one side to a staging schema first. FULL OUTER JOIN is not available in every engine (MySQL needs UNION of left and right joins). Run on a copy first and agree the tolerance with Finance.

Automate reconciliation on every mock load

Optional: we can adapt these checks to your real schemas and run them after each rehearsal.

Optional. The tool and every export work without this. No spam.

How to use it

Enter the table and column names for both sides, the cutover period and a tolerance for rounding. Copy the generated SQL into your database client. Each query returns only rows that differ, so an empty result means the object reconciles. Save the output of every rehearsal with a date so the sign-off pack shows the trend from the first mock load to the final one.

The method

Each query aggregates both sides to the same grain, then joins with a full outer join so a record missing on either side still shows up as a variance. COALESCE turns the missing side into zero. This catches three failure types: values that differ, rows loaded in the new system only, and rows that failed to load.

Grain must match: account, customer or item
Tolerance handles rounding and currency conversion
Row counts catch silent load rejections
Always filter to the same period or snapshot date

Reading the results

A GL variance on a control account usually means the subledger did not reconcile, so check AR and inventory first. Many small AR variances on one customer often point to a payment term or tax difference. Inventory variances in a single location suggest a freeze or counting timing issue, not a load defect.

Common mistakes

Comparing different snapshot times is the biggest cause of false variances: freeze the legacy system before extracting. Another is comparing transaction currency against local currency. Finally, do not hide differences by raising the tolerance; document accepted variances individually with an owner and a reason.

Frequently Asked Questions

Does this work for any ERP?

It works for any ERP whose data you can query with SQL, as long as you supply the actual table and column names. The queries are generic and do not assume a vendor schema, so adjust for multi-currency or multi-company columns.

What tolerance should I use?

For money, 0.01 per account catches rounding while exposing real differences. For quantities, use 0 for discrete items and a small value for bulk units. Agree the values with Finance and Operations before the first rehearsal.

What if the legacy and new systems are in different databases?

Extract one side into a staging table in the other database, or load both extracts into a scratch schema. The generated SQL then runs unchanged on the staged tables.

Why use a full outer join?

An inner join hides records that exist on only one side. A full outer join shows missing and extra records as variances, which is exactly what you need to prove a complete load.

If you need these checks automated on every mock load against your real schemas, Netray can build them into a repeatable validation pack.

Talk to an Expert Explore More Free Tools

Related Tools

ERP Migration & Selection

More Developer Tools tools

See all

Back to All Tools