Reconciling Short-Pays Against Carrier Remittance

You short-paid an invoice by the disputed amount, and weeks later the carrier’s remittance advice arrives referencing different identifiers and partial amounts — matching the two is a many-to-many problem, and until it is solved your ledger shows phantom open balances that were actually settled.

The failure you are hitting

The short-pay path looks clean when you file it: an invoice bills $3,240, your audit says $2,828 is correct, so you remit $2,828 and book a $412 deduction as an open claim against the carrier. The reconciliation problem starts when the carrier responds. Their remittance advice or credit memo does not say “here is your $412 for invoice INV-88421.” It says something like:

• a credit line for $187.50 referencing claim CLM-5567, which maps to part of your deduction;
• a second credit for $224.50 referencing INV88421-R (their re-billed invoice number, not yours);
• and a lump adjustment of $412.00 on a different remittance that references a batch id covering nine of your deductions at once.

None of these carries your dispute_id. The amounts are partial. The references are the carrier’s, not yours. So the deduction sits in DISPUTES_RECV as an open $412 forever, even though the carrier settled it in two pieces under names you never assigned. Multiply that by thousands of deductions a month and the receivable balance is dominated by phantom open claims — money that already came back but that the ledger cannot see was closed.

This reconciliation is the close-out step for Reconciliation Ledger Schema: the deduction opened a receivable, and this is where the receivable is drawn down against what the carrier actually credited.

Root cause analysis

The matching is hard because three independent things do not line up between the deduction you booked and the remittance the carrier sends.

1. No shared match key. You keyed the deduction on your invoice_id and dispute_id. The carrier keys its credit on its own claim number, its re-bill number, or a remittance batch id. There is no column that is equal on both sides, so a straight join returns nothing and every deduction looks unpaid.
2. Partial and split credits. A single deduction is frequently settled in two or three smaller credits — the carrier agrees to part of the claim now and the rest after review. A one-to-one matcher that expects the credit to equal the deduction rejects every partial, leaving both the deduction and the credits stranded.
3. Timing gaps. The credit can arrive before or after the deduction is fully booked, and remittances batch many claims into one advice line. A matcher that assumes same-day, one-line settlement misses anything that crosses a remittance boundary, and the residual — a few dollars of rounding or a disputed remainder — has nowhere to go.

Reproducible diagnostic

Before writing a matcher, confirm the phantom-balance size and that the references genuinely do not join. This snippet quantifies how much of the open receivable already has a plausible credit waiting on the remittance side.

import polars as pl

deductions = pl.read_csv("deductions.csv")     # your open short-pays
remittance = pl.read_csv("remittance.csv")      # carrier credit lines

# Naive join on the natural key returns almost nothing — that is the problem.
naive = deductions.join(remittance, left_on="dispute_id", right_on="carrier_ref", how="inner")
print("naive dispute_id join matches:", naive.height)

# But the totals are close, which means the money is there under other names.
open_total  = deductions["amount"].sum()
credit_total = remittance["amount"].sum()
print(f"open receivable={open_total:.2f}  remittance credits={credit_total:.2f}")
print(f"coverage ratio={credit_total / open_total:.1%}")

Read the result as a decision table:

A near-100% coverage ratio with a near-zero naive join is the signature of the phantom-balance problem: the cash came back, the ledger just cannot see it under the carrier’s names.

Resolution path

The matcher normalizes both sides to a common reference shape, greedily assigns remittance credits to deductions within a tolerance, and routes whatever is left. Pin dependencies:

# requirements.txt
polars==1.12.0
rapidfuzz==3.10.1

Step 1 — Normalize references on both sides

Strip the noise the carrier adds — suffixes like -R, batch prefixes, punctuation — down to a comparable core, and extract any embedded invoice number. Normalizing both sides is what turns INV88421-R and INV-88421 into the same key.

import re
import polars as pl


def canon_ref(col: str) -> pl.Expr:
    """Uppercase, strip non-alphanumerics, drop a trailing re-bill suffix."""
    return (
        pl.col(col)
        .cast(pl.String)
        .str.to_uppercase()
        .str.replace_all(r"[^A-Z0-9]", "")     # 'INV-88421' -> 'INV88421'
        .str.replace(r"R$", "")                 # drop trailing re-bill marker
        .alias(f"{col}_key")
    )


def embedded_invoice(col: str) -> pl.Expr:
    """Pull a bare invoice number out of a carrier reference when present."""
    return pl.col(col).cast(pl.String).str.extract(r"(\d{5,})", 1).alias("inv_hint")


def normalize(ded: pl.DataFrame, rem: pl.DataFrame) -> tuple[pl.DataFrame, pl.DataFrame]:
    ded = ded.with_columns([canon_ref("invoice_id"), embedded_invoice("invoice_id")])
    rem = rem.with_columns([canon_ref("carrier_ref"), embedded_invoice("carrier_ref")])
    return ded, rem

Step 2 — Greedily match credits to deductions

Because one deduction can absorb several credits, matching is an accumulation, not a join. Sort candidate credits by reference-similarity to the deduction and consume them until the deduction is covered. rapidfuzz scores the fuzzy reference agreement so a strong amount match with a weak reference still links, but a coincidental amount with no reference overlap does not.

from decimal import Decimal
from rapidfuzz import fuzz


def match_deduction(deduction: dict, credits: list[dict],
                    min_ref_score: int = 60) -> tuple[list[dict], Decimal]:
    """Greedily consume credits covering one deduction. Returns (used, remaining)."""
    target = Decimal(str(deduction["amount"]))
    # Rank credits by reference similarity, then by closeness of amount.
    ranked = sorted(
        credits,
        key=lambda c: (
            fuzz.token_set_ratio(deduction["invoice_id_key"], c["carrier_ref_key"]),
            -abs(Decimal(str(c["amount"])) - target),
        ),
        reverse=True,
    )
    used, remaining = [], target
    for c in ranked:
        if c.get("consumed"):
            continue
        score = fuzz.token_set_ratio(deduction["invoice_id_key"], c["carrier_ref_key"])
        inv_hint_ok = deduction.get("inv_hint") and deduction["inv_hint"] == c.get("inv_hint")
        if score < min_ref_score and not inv_hint_ok:
            continue                       # reference too weak, and no shared invoice number
        amt = Decimal(str(c["amount"]))
        if amt <= remaining + Decimal("0.02"):   # don't overshoot beyond tolerance
            used.append(c)
            c["consumed"] = True
            remaining -= amt
        if remaining <= Decimal("0.02"):
            break
    return used, remaining

Step 3 — Classify each deduction by its residual, within tolerance

The residual after greedy consumption decides the outcome. Within a small tolerance the deduction is fully settled; a meaningful positive residual is a genuine partial; a residual equal to the whole amount means nothing matched.

def classify(remaining: Decimal, target: Decimal,
             tol: Decimal = Decimal("0.02")) -> str:
    if remaining <= tol:
        return "MATCHED"            # fully covered within rounding tolerance
    if remaining < target:
        return "PARTIAL"            # some credits landed, balance still open
    return "UNMATCHED"              # no credit could be assigned

Step 4 — Post close-out legs and route the residual

Matched and partial deductions post a recovery entry for the amount actually credited, drawing DISPUTES_RECV down through the same posting API described in Designing a Double-Entry Reconciliation Ledger in Postgres. Unmatched deductions and unassigned credits route to a residual review queue rather than being force-matched.

from ledger.repository import Leg, post


def close_out(conn, deduction: dict, credited: Decimal) -> int | None:
    """Draw the receivable down by the amount actually credited."""
    if credited <= 0:
        return None                 # nothing to post; leave the claim open
    return post(
        conn,
        event_type="SHORT_PAY_CLOSEOUT",
        invoice_id=deduction["invoice_id"],
        dispute_id=deduction["dispute_id"],
        legs=[
            Leg(account_code="RECOVERY_INCOME", direction="CREDIT", amount=credited),
            Leg(account_code="DISPUTES_RECV",   direction="DEBIT",  amount=credited),
        ],
    )


def reconcile(conn, deductions: list[dict], credits: list[dict]) -> dict:
    counts = {"MATCHED": 0, "PARTIAL": 0, "UNMATCHED": 0}
    for d in deductions:
        target = Decimal(str(d["amount"]))
        used, remaining = match_deduction(d, credits)
        outcome = classify(remaining, target)
        counts[outcome] += 1
        credited = target - remaining
        if outcome in ("MATCHED", "PARTIAL"):
            close_out(conn, d, credited)
        # UNMATCHED and leftover credits fall through to the review queue.
    return counts

Verification

The reconciliation is correct when every dollar credited is accounted for exactly once and no credit is consumed twice. These assertions run against fixtures built from real remittances.

from decimal import Decimal


def test_split_credits_close_one_deduction():
    ded = {"invoice_id": "INV-88421", "invoice_id_key": "INV88421",
           "dispute_id": "DSP-1", "amount": "412.00", "inv_hint": "88421"}
    credits = [
        {"carrier_ref_key": "CLM5567",   "amount": "187.50", "inv_hint": None},
        {"carrier_ref_key": "INV88421",  "amount": "224.50", "inv_hint": "88421"},
    ]
    used, remaining = match_deduction(ded, credits)
    assert remaining <= Decimal("0.02")          # fully covered
    assert sum(Decimal(c["amount"]) for c in used) == Decimal("412.00")


def test_credit_is_not_double_consumed():
    ded_a = {"invoice_id": "A", "invoice_id_key": "INV900", "dispute_id": "DA",
             "amount": "50.00", "inv_hint": "900"}
    ded_b = {"invoice_id": "B", "invoice_id_key": "INV900", "dispute_id": "DB",
             "amount": "50.00", "inv_hint": "900"}
    shared = [{"carrier_ref_key": "INV900", "amount": "50.00", "inv_hint": "900"}]
    match_deduction(ded_a, shared)
    _used_b, remaining_b = match_deduction(ded_b, shared)
    assert remaining_b == Decimal("50.00")       # already consumed by ded_a


def test_no_reference_overlap_stays_unmatched():
    ded = {"invoice_id": "INV-70001", "invoice_id_key": "INV70001",
           "dispute_id": "DX", "amount": "88.00", "inv_hint": "70001"}
    credits = [{"carrier_ref_key": "ZZWALLET", "amount": "88.00", "inv_hint": None}]
    _used, remaining = match_deduction(ded, credits)
    assert remaining == Decimal("88.00")         # amount alone is not a match

In production the health signal is the residual queue depth: a reconciliation run should close the large majority of deductions and leave a small, shrinking residual. A sudden jump in UNMATCHED usually means a carrier changed its remittance reference format — retune canon_ref, do not lower min_ref_score to force matches, because a coincidental amount match posts a wrong close-out into the ledger.

Preventive configuration

Encode the matcher’s guardrails so a future run cannot silently over-match:

short_pay_reconciler:
  amount_tolerance: 0.02          # cents of rounding absorbed on a full match
  min_ref_score: 60               # rapidfuzz token_set_ratio floor
  require_shared_invoice_hint: true   # amount-only matches are never accepted
  dedupe_remittance: true         # collapse duplicate credit lines before matching
  on_unmatched: RESIDUAL_QUEUE    # never force a match to zero the balance
  overcredit_action: REVIEW       # coverage ratio > 100% goes to a human

• Never amount-only match. Require either a reference score above the floor or a shared embedded invoice number; two unrelated $88 lines are not the same claim.
• Dedupe remittance first. Carriers re-send advices; a duplicated credit line double-closes a deduction. Collapse identical lines before matching.
• Alert on over-credit. A coverage ratio above 100% means the carrier credited more than you deducted — route it to review; do not let it post a negative residual.
• Idempotent close-out. Key the SHORT_PAY_CLOSEOUT entry on dispute_id plus the credited amount so a re-run does not post the recovery twice.

FAQ

Why does a plain join on my dispute_id match almost nothing?

Because the carrier never sees your dispute_id. Its remittance references its own claim numbers, re-bill invoice numbers, or batch ids, none of which equal your key. You have to normalize both sides to a comparable reference and match on that plus the amount, which is what Steps 1 and 2 do; a plain equality join is the wrong tool.

How do I handle one deduction that is settled by two or three credits?

Match greedily rather than one-to-one. Rank the candidate credits by reference similarity, consume them into the deduction until it is covered within tolerance, and mark each consumed credit so it cannot be reused. The deduction closes when its running residual falls to within the amount tolerance.

Should I force a match when the amounts are equal but the references do not agree?

No. An equal amount with no reference overlap is a coincidence far more often than a real settlement, and forcing it posts a wrong close-out that hides a genuinely open claim. Require a reference score above the floor or a shared embedded invoice number, and send amount-only candidates to the residual queue.

What happens to the residual when a credit does not fully cover a deduction?

Post a recovery for the amount actually credited so the receivable draws down, and leave the remaining balance open as a partial. The residual stays visible in the ledger and, if it never settles, is eventually written off through a separate reversal entry rather than being quietly zeroed.

Related

• Reconciliation Ledger Schema — the parent ledger whose receivable this reconciliation draws down.
• Designing a Double-Entry Reconciliation Ledger in Postgres — the posting API and balance invariant the close-out entries obey.
• Automating Short-Pay Deduction Codes in Python — where the deductions reconciled here are first generated and coded.
• Charge Variance Classification — the upstream classification that sizes each disputed amount.

Up one level: Reconciliation Ledger Schema · Section: Discrepancy Resolution & Dispute Routing