Accounts receivable ageing with partial payments

Age the outstanding invoice balance at a defined cutoff, not the original invoice amount. Partial payments and applied credits reduce the balance, while transactions posted after the cutoff must not change a historical report.

Calculate the balance first, then classify it by days past due. Keep negative balances or overapplications visible rather than silently setting them to zero and losing reconciliation.

Define the historical snapshot

The original synthetic receivables invoices, payment applications and credits use a January 31, 2026 reporting date. Include activity before February 1, exclusive.

This is an analytical teaching ledger. It does not prescribe an accounting policy or represent actual NeuraPath receivables. The payment table contains amounts applied to particular invoices, not every cash receipt in the business.

For F1, the original 100,000 paise invoice has two applied payments totaling 50,000 and a 10,000 credit. Its remaining balance is 40,000, sixteen days past due.

Aggregate each child ledger before joining

WITH paid AS (
 SELECT invoice_id,SUM(amount_paise) AS amount
 FROM ar_payments WHERE applied_at<'2026-02-01' GROUP BY invoice_id
), credited AS (
 SELECT invoice_id,SUM(amount_paise) AS amount
 FROM ar_credits WHERE posted_at<'2026-02-01' GROUP BY invoice_id
)
SELECT i.invoice_id,i.due_at,
 i.amount_paise-COALESCE(p.amount,0)-COALESCE(c.amount,0) AS balance_paise,
 CAST(julianday('2026-01-31')-julianday(i.due_at) AS INTEGER) AS days_past_due
FROM ar_invoices i
LEFT JOIN paid p USING(invoice_id) LEFT JOIN credited c USING(invoice_id)
WHERE i.issued_at<'2026-02-01' ORDER BY i.invoice_id;

Joining raw payments and raw credits directly can multiply rows when an invoice has several of each. Preaggregation keeps one balance row per invoice.

Expected balances are F1 at 40,000, F2 at 50,000, F3 at 5,000 and F4 at negative 5,000 paise. F2's February payment is excluded even though it exists in the extract.

Keep ageing totals and credit positions reconcilable

from datetime import date
from build_and_verify import database

db = database()
balances = {}
buckets = {'current':0,'1-30':0,'31-60':0,'61+':0}
credit_positions = 0
for invoice,customer,issued,due,amount in db.execute("SELECT * FROM ar_invoices WHERE issued_at<'2026-02-01'"):
    paid = db.execute("SELECT COALESCE(SUM(amount_paise),0) FROM ar_payments WHERE invoice_id=? AND applied_at<'2026-02-01'",(invoice,)).fetchone()[0]
    credit = db.execute("SELECT COALESCE(SUM(amount_paise),0) FROM ar_credits WHERE invoice_id=? AND posted_at<'2026-02-01'",(invoice,)).fetchone()[0]
    balance = amount-paid-credit
    balances[invoice] = balance
    if balance < 0:
        credit_positions += -balance
    elif balance > 0:
        age = (date(2026,1,31)-date.fromisoformat(due)).days
        band = 'current' if age<=0 else '1-30' if age<=30 else '31-60' if age<=60 else '61+'
        buckets[band] += balance
db.close()
assert balances == {'F1':40000,'F2':50000,'F3':5000,'F4':-5000}
assert buckets == {'current':50000,'1-30':40000,'31-60':5000,'61+':0}
assert credit_positions == 5000
assert sum(buckets.values())-credit_positions == sum(balances.values()) == 90000
print({'positive_balance_buckets':buckets,'credit_positions':credit_positions,'net_balance':90000})

The positive receivable total is 95,000 paise. The separate credit position is 5,000, leaving a net ledger balance of 90,000. F4's negative balance requires review; the example does not automatically reallocate it to another invoice.

Match the report's credit treatment

Oracle's receivables reporting guide documents options for displaying and ageing credit items. Different report settings can legitimately produce different bucket presentations. Reconcile their scope before assuming that one total is wrong.

Decide whether a due-today invoice is current or overdue under the chosen convention. Here, age zero is current. Separate disputed invoices, unapplied cash and write-offs when those states affect the business question; they are not modeled in this compact fixture.

Historical payment application can also differ from payment receipt time. A cash receipt may arrive before it is allocated to an invoice. Store both when needed, rather than assigning one date to two different events.

Exercise: add a second credit to F1 and verify that the preaggregated query still returns one invoice row. Then move a payment across the cutoff and explain which historical balance changes.

NeuraPath's Data Analytics with Generative AI course connects SQL reconciliation with finance operations. A useful ageing report preserves the balance bridge and makes cutoff and credit rules explicit.

Continue learning

This article is part of the Domain analytics and business cases sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Procurement spend analysis with inconsistent vendor names.
• Continue with Reconcile invoice, payment and refund records.