# Inventory Subledger to GL Reconciliation SQL Generator

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

## Short answer

Generates a SQL query that compares inventory subledger value with the GL inventory accounts and shows the difference by account and period.

## Category

cost-finance

## Audience

- Controller
- ERP finance analyst
- Database analyst
- Internal auditor

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Inventory balance table

**Key:** sub_table

**Type:** text

**Default:** inventory_valuation

**Help:** Generic name. Replace with your ERP table or view.

#### GL balance table

**Key:** gl_table

**Type:** text

**Default:** gl_period_balance

#### Period (YYYYMM)

**Key:** period

**Type:** text

**Default:** 202609

#### Tolerance amount

**Key:** tolerance

**Type:** number

**Default:** 1.00

**Help:** Differences at or below this are treated as matched.

#### Inventory GL accounts

**Key:** accounts

**Type:** text

**Default:** '1300','1310','1320'

**Help:** Quoted, comma separated.

**Blocks:**

#### Reconciliation query

**Body:** -- Inventory subledger vs GL by account for period {{period}}
-- Table and column names are generic: map them to your ERP.
WITH sub AS (
  SELECT inventory_account AS gl_account,
         SUM(extended_value) AS sub_value
  FROM {{sub_table}}
  WHERE period = '{{period}}'
    AND inventory_account IN ({{accounts}})
  GROUP BY inventory_account
),
gl AS (
  SELECT gl_account,
         SUM(ending_balance) AS gl_value
  FROM {{gl_table}}
  WHERE period = '{{period}}'
    AND gl_account IN ({{accounts}})
  GROUP BY gl_account
)
SELECT COALESCE(sub.gl_account, gl.gl_account) AS gl_account,
       COALESCE(sub.sub_value, 0) AS subledger_value,
       COALESCE(gl.gl_value, 0) AS gl_value,
       COALESCE(sub.sub_value, 0) - COALESCE(gl.gl_value, 0) AS difference,
       CASE WHEN ABS(COALESCE(sub.sub_value, 0) - COALESCE(gl.gl_value, 0)) <= {{tolerance}}
            THEN 'MATCHED' ELSE 'INVESTIGATE' END AS status
FROM sub
FULL OUTER JOIN gl ON gl.gl_account = sub.gl_account
ORDER BY ABS(COALESCE(sub.sub_value, 0) - COALESCE(gl.gl_value, 0)) DESC;

#### Drill-down for unmatched accounts

**Body:** -- Largest item-level values in the subledger for one account
SELECT item_number, warehouse, quantity_on_hand, unit_cost, extended_value
FROM {{sub_table}}
WHERE period = '{{period}}'
  AND inventory_account = '1300'
ORDER BY ABS(extended_value) DESC
FETCH FIRST 50 ROWS ONLY;

**Notes:** Table and column names here are placeholders. Map them to your ERP's inventory valuation and GL balance tables or views. FULL OUTER JOIN and FETCH FIRST work on PostgreSQL, Oracle 12c+ and SQL Server needs TOP instead. Run against a reporting copy, and take the extract at the same cut-off as the GL close.

## Content

**Intro:** Month-end inventory reconciliation is a join and a subtraction, but it is slow to do by hand across hundreds of items and several accounts. This generator writes the query so you can see differences by account and drill into the item-level detail.

**Sections:**

#### How to use the query

**Body:** Replace the generic table and column names with those from your ERP's inventory valuation view and GL period balance view, then set the period, the inventory control accounts and a tolerance. Run it after the subledger is closed and the GL period is final. The first query shows each account with subledger value, GL value and a status flag, and the second query lists the largest item-level values for a drill-down.

#### Why the two sides differ

**Body:** Frequent causes are receipts not yet matched to an invoice, manual journals posted directly to inventory accounts, production variances posted to inventory instead of variance accounts, timing differences in intercompany transfers, and standard cost revaluations posted in only one side. A difference that is identical to a single posting points to a missed or duplicated entry, while scattered small differences usually point to rounding or currency translation.

#### Reading the results

**Body:** A MATCHED status within tolerance is expected. An INVESTIGATE status should be traced to the period transactions. Sort by absolute difference, take the largest, and walk through receipts, issues, adjustments and revaluations. Document each reconciling item with an owner and a clearing date.

#### Common mistakes

**Body:** Comparing balances taken at different times, including non-inventory warehouses, ignoring in-transit stock accounts, and tolerances that are so large that real errors are hidden. Also remember that FULL OUTER JOIN is essential: a GL account with no subledger rows is exactly the case you need to see.

**Cta:** If your reconciliation is a monthly spreadsheet exercise, we can discuss automating the extract and exception review from your ERP, read-only.

**Faq:**

#### How do I reconcile inventory to the general ledger?

**Answer:** Total the inventory subledger value by GL account at the period cut-off, compare with the GL balance of the same account, and trace differences to specific transactions such as unmatched receipts, manual journals and revaluations.

#### What tolerance should I use?

**Answer:** Use a small amount for rounding, often a few currency units per account. Larger tolerances hide real issues and should be justified in writing for auditors.

#### Will this run on any ERP?

**Answer:** The pattern is universal, but table and column names differ. You must map them. Syntax such as FETCH FIRST also varies by database, so adjust for SQL Server or MySQL.

#### Can I schedule it?

**Answer:** Yes. Run it after each period close from a reporting database, store the result with the period, and review INVESTIGATE rows. Keep a history for audit evidence.

## Search terms

inventory to GL reconciliation SQL, inventory subledger reconciliation query, inventory GL tie out, inventory valuation vs general ledger, month end inventory reconciliation SQL, ERP inventory GL variance query, inventory control account reconciliation, reconcile perpetual inventory to GL

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