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

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.

SB1

SAP B1 Academy Editorial

Published • 3 min read

Save
Receivables aging buckets reconciled to the accounts receivable control account.
Receivables aging buckets reconciled to the accounts receivable control account. (Image source: SAP B1 Academy Lab)

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.

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