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.
SAP B1 Academy Editorial
Published • 9 min read
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.
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.