Reconciling a Billing Run Against the General Ledger

A billing run can look perfect and still be wrong at the ledger. The subledger — the per-account roll-up of every charge, credit, and payment the run produced — is the utility’s own record of what should hit each general-ledger (GL) account. What actually posts to the GL can differ: a batch was partially rejected by the ERP, a reversal never posted, a rounding rule diverged, or an account exists on one side but not the other. Left undetected, that gap becomes a misstated fund balance and an audit finding. This page sits under GASB audit-trail compliance patterns, part of the broader Billing Reconciliation & Audit Logging subsystem, and builds the specific proof a reviewer asks for: a Decimal-safe variance report that compares subledger totals per GL account against what the GL received for the same run, keyed by account, with a per-account delta and an overall pass/fail verdict.

# Minimal Prerequisites

• Python 3.11+ — for precise typing and standard-library tooling; no third-party dependency is required for the reconciliation itself.
• decimal.Decimal for every amount — subledger totals, posted totals, deltas, and the tolerance are all Decimal. A float anywhere here manufactures phantom variances at the cent level.
• A stable GL account code as the join key on both sides — the same chart-of-accounts identifier the billing engine tags charges with and the ERP posts against.
• A shared run identifier so both sides describe the same billing run and the same posting period; reconciling across mismatched cutoffs is the most common source of false variances.

Data assumptions: the subledger side arrives as the run’s own totals per GL account (summed from the balanced journals it generated), and the posted side arrives as the GL’s totals per account for that run. Both are amounts in the same currency, already signed by normal accounting convention (debits positive, credits negative, or a consistent convention you control).

# Annotated Implementation

The reconciliation is a keyed comparison, not a row-by-row match. Total each side by GL account, take the union of account codes so nothing on either side is silently ignored, compute the signed delta per account, and apply a rounding tolerance before declaring a line balanced. The report is a list of per-account results plus a single verdict.

from __future__ import annotations

from dataclasses import dataclass
from decimal import Decimal
from enum import StrEnum

CENT = Decimal("0.01")
DEFAULT_TOLERANCE = Decimal("0.00")  # exact by default; widen only with justification


class LineStatus(StrEnum):
    MATCH = "match"
    VARIANCE = "variance"
    MISSING_IN_GL = "missing_in_gl"          # subledger has it; GL never posted it
    MISSING_IN_SUBLEDGER = "missing_in_sub"  # GL posted it; subledger did not expect it


@dataclass(frozen=True, slots=True)
class AccountVariance:
    account: str
    subledger_total: Decimal
    posted_total: Decimal
    delta: Decimal            # subledger - posted (positive = under-posted to GL)
    status: LineStatus


@dataclass(frozen=True, slots=True)
class ReconciliationReport:
    run_id: str
    lines: tuple[AccountVariance, ...]
    tolerance: Decimal

    @property
    def passed(self) -> bool:
        # The run reconciles only if every line is a match within tolerance.
        return all(line.status is LineStatus.MATCH for line in self.lines)

    @property
    def net_variance(self) -> Decimal:
        # A balanced run should net to zero; a non-zero net localizes the leak.
        return sum((line.delta for line in self.lines), Decimal("0"))


def reconcile_run(
    run_id: str,
    subledger: dict[str, Decimal],   # account -> total the run expected to post
    posted: dict[str, Decimal],      # account -> total the GL actually received
    tolerance: Decimal = DEFAULT_TOLERANCE,
) -> ReconciliationReport:
    """Compare per-account subledger totals against GL postings for one run."""
    # Union of accounts so an account present on only one side is never dropped.
    accounts = sorted(set(subledger) | set(posted))
    lines: list[AccountVariance] = []
    for account in accounts:
        sub = subledger.get(account)
        pos = posted.get(account)
        if sub is None:                      # GL posted to an account the run never touched
            lines.append(AccountVariance(
                account, Decimal("0"), pos, -pos, LineStatus.MISSING_IN_SUBLEDGER))
            continue
        if pos is None:                      # run expected a posting that never landed
            lines.append(AccountVariance(
                account, sub, Decimal("0"), sub, LineStatus.MISSING_IN_GL))
            continue
        delta = (sub - pos).quantize(CENT)   # Decimal subtraction, quantized once
        status = LineStatus.MATCH if abs(delta) <= tolerance else LineStatus.VARIANCE
        lines.append(AccountVariance(account, sub, pos, delta, status))
    return ReconciliationReport(run_id, tuple(lines), tolerance)

Three decisions make the report trustworthy. Taking the union of account codes — not just iterating the subledger — is what surfaces an account the GL posted to but the run never expected (MISSING_IN_SUBLEDGER), a failure a one-sided loop would hide entirely. Computing delta as subledger - posted gives the variance a consistent sign, so a positive net across the report means the run under-posted to the GL and a negative net means it over-posted, which points the investigation in the right direction. And quantizing each delta to the cent with Decimal — never float — means the only variances reported are real accounting differences, not artifacts of binary representation. The subledger totals themselves come from the balanced journals produced upstream by double-entry journal and manifest generation, and the posted side reflects what the ERP general-ledger posting integration confirmed.

# Edge Cases and Billing Gotchas

The arithmetic is easy; reconciliation fails on the accounting realities around it. These are the ones that most often turn a clean run into a false variance — or hide a real one.

• Period cutoff and timing. The subledger and the GL must describe the same run and the same posting period. A charge that fell just after the cutoff on one side and just before it on the other creates a variance that is really a timing difference. Reconcile on a shared run_id and posting date, and hold cross-period items in a documented in-transit line rather than forcing them to net.
• Unposted reversals. A reversal recorded in the subledger but not yet posted to the GL (or vice versa) shows up as a MISSING_IN_GL line or an inflated delta. Treat a reversal as a first-class expected posting; if it is legitimately still in flight, it belongs on the in-transit line, not silently absorbed into tolerance.
• Rounding tolerance. Different rounding rules between the billing engine and the ERP can produce sub-cent drift that aggregates to a few cents per account. Set an explicit, small Decimal tolerance (often Decimal("0.00"), widened only with written justification) so genuine rounding is accepted while a real discrepancy still fails — never use a float tolerance, which reintroduces the drift you are trying to measure.
• Accounts present on only one side. An account the GL posted to but the run never expected (MISSING_IN_SUBLEDGER) usually means a misconfigured account mapping or a stray manual journal; an account the run expected but the GL never received (MISSING_IN_GL) usually means a rejected or dropped batch. The union-of-accounts logic surfaces both explicitly instead of dropping the unmatched side. When the underlying cause is missing rate data rather than a posting error, resolution routes through fallback routing for missing rate data.

# Verification Snippet

Exercise all four line statuses in one fixture and assert both the per-account classification and the overall verdict.

def test_reconcile_run_classifies_every_line() -> None:
    subledger = {
        "4100": Decimal("82140.00"),  # water sales — small rounding gap
        "4200": Decimal("15600.00"),  # sewer sales — exact match
        "2300": Decimal("1200.00"),   # deposits — expected but never posted
    }
    posted = {
        "4100": Decimal("82139.40"),  # 0.60 under-posted
        "4200": Decimal("15600.00"),  # exact
        "9999": Decimal("50.00"),     # GL posted to an account the run never touched
    }
    report = reconcile_run("RUN-2026-07", subledger, posted,
                           tolerance=Decimal("0.50"))

    by_acct = {line.account: line for line in report.lines}
    assert by_acct["4100"].status is LineStatus.VARIANCE          # 0.60 > 0.50 tol
    assert by_acct["4100"].delta == Decimal("0.60")              # under-posted, positive
    assert by_acct["4200"].status is LineStatus.MATCH
    assert by_acct["2300"].status is LineStatus.MISSING_IN_GL
    assert by_acct["9999"].status is LineStatus.MISSING_IN_SUBLEDGER

    assert report.passed is False                                # any non-match fails
    assert report.net_variance == Decimal("1810.60")            # localizes the leak

Running this against fixtures drawn from real historical runs — not synthetic totals — is what surfaces the account-mapping and cutoff quirks specific to a given ERP. A passing report should be recorded as a signed reconciliation event in the audit trail, so the sign-off itself is tamper-evident.

# FAQ

# Related Topics

• GASB Audit-Trail Compliance Patterns — the parent design where this reconciliation’s sign-off is captured as a tamper-evident event.
• Double-Entry Journal & Manifest Generation — the balanced journals whose per-account totals form the subledger side of this comparison.
• ERP General-Ledger Posting Integration — the posting boundary that produces the GL totals reconciled here.
• Hash-Total Validation Controls — the batch-level totals check that runs before a run reaches this account-level reconciliation.

Up: GASB Audit-Trail Compliance Patterns · Billing Reconciliation & Audit Logging