Back to Tutorials
SQL & HANA•9 min read
TUTORIAL • FREE MEMBER

Automating Month-End Inventory Reconciliation with SAP HANA SQL Queries

Discrepancies between the Inventory Audit Report (OIVL) and the General Ledger (JDT1) can delay month-end closing by days. Use this diagnostic framework and production HANA query to isolate root causes in minutes.

SB1

SAP B1 Academy Editorial

Published • 9 min read

Save
Inventory valuation & G/L reconciliation workflow analysis.
Inventory valuation & G/L reconciliation workflow analysis. (Image source: SAP B1 Academy Lab)

One of the most stressful moments for a finance controller or SAP Business One support consultant occurs during month-end close: the Inventory Audit Report balance does not match the corresponding Inventory G/L account in the Trial Balance.

Because SAP Business One uses perpetual inventory accounting, every Goods Receipt PO, Delivery, Inventory Transfer, or Goods Issue posts simultaneous entries to the inventory subledger (OIVL) and the General Ledger (OJDT / JDT1). When those two numbers diverge, manual journal entries posted directly to stock accounts are usually the culprit.

Why the Inventory Subledger & G/L Drift Apart

In the majority of client audits, subledger-to-G/L variances trace back to one of three configuration oversights:

  • Manual journal entries to control accounts: An accountant posts a manual adjustment directly to the inventory G/L account because the Control Account checkbox was left unchecked in the Chart of Accounts.
  • Revaluation timing mismatches: Inventory revaluation documents posted with a backdated posting date after subsequent goods issues were already recorded.
  • Landed costs closed into the wrong period: Landed cost documents that close after the period lock, redistributing value across already-reported months.
Free Member Content

Create a free account to unlock the full step-by-step guide & copy the production SQL script.

Join SAP Business One consultants and key users worldwide. Free forever, no credit card required.

or use a magic link