Customer Aging That Actually Matches the G/L: An A/R Aging Query for SAP B1
Standard aging reports can disagree with the control account when payments on account and journal entries are involved. This query ages open items straight from JDT1.
SAP B1 Academy Editorial
Published • 3 min read
Finance managers frequently ask why the Customer Receivables Aging report total does not match the A/R control account on the Trial Balance. The answer is almost always unreconciled items: payments on account, credit memos, and manual journal entries that were never internally reconciled against the invoices they settle.
Why Aging and the Trial Balance Disagree
- Payments on account: An incoming payment posted without selecting invoices stays open as a credit. Some report filters exclude it.
- Manual journal entries to the BP: Write-offs or reclassifications posted directly to the customer in a journal entry appear in the ledger but not on document-based reports.
- Different aging dates: Aging by posting date and by due date produces different buckets, and many users compare the wrong version.
The Reliable Approach: Age the Journal Lines
Every A/R posting, whatever the source document, creates a line in JDT1 with the customer code in ShortName. The open balance of that line lives in BalDueDeb and BalDueCred. Summing those open balances by due-date bucket gives an aging that ties to the control account by definition.
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.