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

> Source: https://netray.co/tools/erp-cutover-reconciliation-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your table and column names and get ready-to-run comparison queries for legacy versus new ERP balances.

## Category

developer

## Audience

- Data migration lead
- ERP developer
- Finance controller

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Legacy GL balance table

**Key:** legacy_gl

**Type:** text

**Default:** legacy.gl_balances

#### New ERP GL balance table

**Key:** new_gl

**Type:** text

**Default:** erp.gl_balances

#### Account column

**Key:** acct_col

**Type:** text

**Default:** account_no

#### Period column

**Key:** period_col

**Type:** text

**Default:** fiscal_period

#### Balance amount column

**Key:** amt_col

**Type:** text

**Default:** balance_amt

#### Cutover period

**Key:** period

**Type:** text

**Default:** 2026-12

#### Legacy open AR table

**Key:** legacy_ar

**Type:** text

**Default:** legacy.ar_open_items

#### New open AR table

**Key:** new_ar

**Type:** text

**Default:** erp.ar_open_items

#### Customer column

**Key:** cust_col

**Type:** text

**Default:** customer_no

#### Open amount column

**Key:** ar_amt_col

**Type:** text

**Default:** open_amt

#### Legacy inventory table

**Key:** legacy_inv

**Type:** text

**Default:** legacy.inv_onhand

#### New inventory table

**Key:** new_inv

**Type:** text

**Default:** erp.inv_onhand

#### Item column

**Key:** item_col

**Type:** text

**Default:** item_no

#### Quantity column

**Key:** qty_col

**Type:** text

**Default:** qty_onhand

#### Tolerance

**Key:** tolerance

**Type:** number

**Default:** 0.01

**Blocks:**

#### 1. Trial balance variance by account

**Body:** SELECT COALESCE(l.{{acct_col}}, n.{{acct_col}}) 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 {{acct_col}}, SUM({{amt_col}}) AS total_amt
      FROM {{legacy_gl}}
      WHERE {{period_col}} = '{{period}}'
      GROUP BY {{acct_col}}) l
FULL OUTER JOIN (SELECT {{acct_col}}, SUM({{amt_col}}) AS total_amt
      FROM {{new_gl}}
      WHERE {{period_col}} = '{{period}}'
      GROUP BY {{acct_col}}) n
  ON l.{{acct_col}} = n.{{acct_col}}
WHERE ABS(COALESCE(n.total_amt, 0) - COALESCE(l.total_amt, 0)) > {{tolerance}}
ORDER BY account_no;

#### 2. Open AR variance by customer

**Body:** SELECT COALESCE(l.{{cust_col}}, n.{{cust_col}}) 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 {{cust_col}}, SUM({{ar_amt_col}}) AS total_open
      FROM {{legacy_ar}} GROUP BY {{cust_col}}) l
FULL OUTER JOIN (SELECT {{cust_col}}, SUM({{ar_amt_col}}) AS total_open
      FROM {{new_ar}} GROUP BY {{cust_col}}) n
  ON l.{{cust_col}} = n.{{cust_col}}
WHERE ABS(COALESCE(n.total_open, 0) - COALESCE(l.total_open, 0)) > {{tolerance}}
ORDER BY customer_no;

#### 3. Inventory on-hand variance by item

**Body:** SELECT COALESCE(l.{{item_col}}, n.{{item_col}}) 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_col}}, SUM({{qty_col}}) AS total_qty
      FROM {{legacy_inv}} GROUP BY {{item_col}}) l
FULL OUTER JOIN (SELECT {{item_col}}, SUM({{qty_col}}) AS total_qty
      FROM {{new_inv}} GROUP BY {{item_col}}) n
  ON l.{{item_col}} = n.{{item_col}}
WHERE ABS(COALESCE(n.total_qty, 0) - COALESCE(l.total_qty, 0)) > {{tolerance}}
ORDER BY item_no;

#### 4. Row count check

**Body:** SELECT 'GL' AS object_name,
       (SELECT COUNT(*) FROM {{legacy_gl}} WHERE {{period_col}} = '{{period}}') AS legacy_rows,
       (SELECT COUNT(*) FROM {{new_gl}} WHERE {{period_col}} = '{{period}}') AS new_rows
UNION ALL
SELECT 'Open AR', (SELECT COUNT(*) FROM {{legacy_ar}}), (SELECT COUNT(*) FROM {{new_ar}})
UNION ALL
SELECT 'Inventory', (SELECT COUNT(*) FROM {{legacy_inv}}), (SELECT COUNT(*) FROM {{new_inv}});

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use it

**Body:** 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

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

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

**Faq:**

#### Does this work for any ERP?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

ERP data migration reconciliation SQL, compare trial balance legacy and new ERP, cutover reconciliation query, open AR reconciliation SQL, inventory on-hand reconciliation SQL, full outer join variance query, data migration validation SQL

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json