Classifying Overcharge vs Duplicate Billing in Python

A naive variance rule cannot tell a carrier’s legitimate corrected re-bill of the same PRO from a duplicate or an overcharge, so it pays duplicates and mis-routes real overcharges — and this page fixes that with a stable shipment identity and an ordered read of correction events.

The failure you are hitting

Your classifier compares each billed charge against the contracted rate and flags the delta. On a single invoice it works. Across a carrier’s real billing stream, where the same movement is invoiced more than once, it produces three wrong outcomes, none of which raises an exception:

• A carrier re-bills PRO EXLA55231 after a rate correction. Your rule sees a second charge for a PRO it already settled, computes a positive delta against the contract, and tags it LINEHAUL_OVERCHARGE. It routes to the carrier dispute API — where the carrier, who issued the correction on purpose, denies it. The metric shows a lost dispute; the reality is a mis-classification.
• The carrier redelivers a byte-identical invoice after a transmission retry. Your rule has no memory of the first, prices it again, finds it in tolerance, and approves it for payment. The same freight is now paid twice.
• A genuine duplicate — same PRO, same charges, different invoice number — slips through because your identity key was the invoice number, which the carrier changed. It is never flagged, and the overpayment stands.

Every one of these is a classification error inside Charge Variance Classification: the variance amount is computed correctly, but the type is wrong, so the case goes to the wrong recovery channel or none at all.

Root cause analysis

The classifier has two blind spots, and they compound:

1. No stable shipment identity. The PRO number is the movement’s identity; the invoice number is not. When identity is keyed on invoice number (or an auto-increment id, or arrival order), a re-bill of the same PRO looks like a brand-new shipment, and a duplicate under a fresh invoice number looks unrelated to the original.
2. No ordering of correction events. Two charges for one PRO are not automatically a duplicate — one may amend the other. Without reading why the second charge exists (a corrected total, a reweigh, a pure redelivery), the classifier cannot tell an AMENDED_REBILL that should replace the original from a DUPLICATE that should be short-paid or an OVERCHARGE that should be disputed.

The decision below is what a correct classifier walks for every charge that shares a PRO with something already in the ledger.

Reproducible diagnostic

Before changing the classifier, confirm you have the identity problem. This snippet groups a carrier’s charge lines by PRO and shows where a naive invoice-number key would have missed a duplicate or double-counted a re-bill:

import pandas as pd

df = pd.read_csv("carrier_charges.csv", dtype=str)
df["amount"] = df["amount"].astype(float)

# What a naive invoice-number key sees vs what a PRO key sees.
by_invoice = df["invoice_number"].nunique()
by_pro = df["pro_number"].nunique()

multi = df.groupby("pro_number").size()
repeat_pros = multi[multi > 1]

print(f"unique invoices={by_invoice}  unique PROs={by_pro}")
print(f"PROs billed more than once: {len(repeat_pros)}")
print(df[df["pro_number"].isin(repeat_pros.index)]
      .sort_values(["pro_number", "amount"])
      [["pro_number", "invoice_number", "charge_code", "amount", "amendment_ref"]]
      .head(12))

Read the output as a decision table:

If unique invoices exceeds unique PROs, the same freight is being billed under multiple invoice numbers — proof the identity key must be the PRO, not the invoice.

Resolution path

The fix is a stable fingerprint, a PRO-based dedup key, and a classifier that reads the amendment marker to order correction events.

Step 1 — Build a stable charge fingerprint and shipment identity

Identity is the PRO; the fingerprint is what makes a charge line comparable across redeliveries. Derive both deterministically so the same charge always hashes the same, regardless of invoice number or arrival order.

# dedup/identity.py
import hashlib
from decimal import Decimal


def shipment_identity(carrier_scac: str, pro_number: str) -> str:
    """The movement's identity — the PRO, never the invoice number."""
    return f"{carrier_scac.strip().upper()}|{pro_number.strip()}"


def charge_fingerprint(charge_code: str, amount: Decimal, service_date: str) -> str:
    """Stable across redelivery: identical charges hash identically."""
    raw = f"{charge_code.strip().upper()}|{amount:.2f}|{service_date.strip()}"
    return hashlib.sha256(raw.encode()).hexdigest()

Step 2 — Classify against the ledger of prior settlements

Walk the three-question decision. The prior-settlement lookup is keyed on the shipment identity, so a re-bill under a new invoice number still finds its original.

# dedup/classify.py
from decimal import Decimal
from enum import Enum


class ReBillType(str, Enum):
    OVERCHARGE = "OVERCHARGE"
    DUPLICATE = "DUPLICATE"
    AMENDED_REBILL = "AMENDED_REBILL"
    CLEAN = "CLEAN"


def classify(line: dict, ledger, contract_amount: Decimal) -> ReBillType:
    identity = shipment_identity(line["carrier_scac"], line["pro_number"])
    prior = ledger.prior_settlements(identity)   # [] if PRO never settled

    # Q1: no prior line -> independent, price against the contract.
    if not prior:
        delta = Decimal(str(line["amount"])) - contract_amount
        return ReBillType.OVERCHARGE if delta > Decimal("0.01") else ReBillType.CLEAN

    fp = charge_fingerprint(line["charge_code"], Decimal(str(line["amount"])),
                            line["service_date"])

    # Q2: fingerprint matches a settled charge -> byte-identical redelivery.
    if any(p["fingerprint"] == fp for p in prior):
        return ReBillType.DUPLICATE

    # Q3: fingerprint differs -> an amendment marker makes it a legitimate
    # correction; its absence makes it an unexplained second charge.
    if line.get("amendment_ref"):
        return ReBillType.AMENDED_REBILL
    return ReBillType.OVERCHARGE

Edge case: a carrier that re-bills with a lower amount and no marker is still an anomaly, not a gift — it returns OVERCHARGE so a human sees why the same PRO was billed twice, rather than silently accepting the second charge.

Step 3 — Apply the disposition per type

Each type routes differently: a duplicate is recovered by short-pay, an amended re-bill supersedes the original and re-enters validation, an overcharge goes to the dispute API.

# dedup/dispose.py
def dispose(rebill_type, line, ledger, revalidate, short_pay, dispute):
    if rebill_type == ReBillType.DUPLICATE:
        # Never pay it again; deduct against the carrier's next remittance.
        short_pay.open_deduction(line, reason="DUPLICATE_BILLING")
    elif rebill_type == ReBillType.AMENDED_REBILL:
        # Supersede the prior settled charge, then re-price the correction.
        ledger.supersede(line["pro_number"], line)
        revalidate.enqueue(line)
    elif rebill_type == ReBillType.OVERCHARGE:
        dispute.open_claim(line, variance_type="LINEHAUL_OVERCHARGE")
    # CLEAN falls through to normal settlement.

The redelivery-versus-correction distinction here is the same idempotency problem the ingestion tier solves when the same payload arrives twice; the batch-processing angle of that is covered in Implementing Async Batch Invoice Processing with Celery.

Verification

Assert each of the four outcomes on fixtures that share a PRO, so a regression that reintroduces the identity bug fails immediately.

from decimal import Decimal


class FakeLedger:
    def __init__(self, prior):
        self._prior = prior
    def prior_settlements(self, identity):
        return self._prior


def _line(**kw):
    base = dict(carrier_scac="EXLA", pro_number="EXLA55231", charge_code="LH",
                amount="1200.00", service_date="20260705", amendment_ref=None)
    base.update(kw)
    return base


def test_redelivery_is_duplicate():
    fp = charge_fingerprint("LH", Decimal("1200.00"), "20260705")
    led = FakeLedger([{"fingerprint": fp}])
    assert classify(_line(), led, Decimal("1200.00")) == ReBillType.DUPLICATE


def test_corrected_rebill_is_amended():
    fp = charge_fingerprint("LH", Decimal("1200.00"), "20260705")
    led = FakeLedger([{"fingerprint": fp}])
    line = _line(amount="1050.00", amendment_ref="COR-88")
    assert classify(line, led, Decimal("1050.00")) == ReBillType.AMENDED_REBILL


def test_unexplained_second_charge_is_overcharge():
    fp = charge_fingerprint("LH", Decimal("1200.00"), "20260705")
    led = FakeLedger([{"fingerprint": fp}])
    line = _line(amount="1400.00", amendment_ref=None)
    assert classify(line, led, Decimal("1200.00")) == ReBillType.OVERCHARGE


def test_first_sighting_prices_against_contract():
    assert classify(_line(), FakeLedger([]), Decimal("1200.00")) == ReBillType.CLEAN

In production the proof is in the ledger: every PRO settles at most once for a given fingerprint, and AMENDED_REBILL events always carry a superseded-original link. A rising DUPLICATE rate on one carrier means a redelivery loop upstream, not a tolerance to loosen.

Preventive configuration

Encode the identity discipline so it cannot regress into invoice-number keying:

rebill_classifier:
  identity_key: [carrier_scac, pro_number]   # never invoice_number
  fingerprint_fields: [charge_code, amount, service_date]
  prior_lookback_days: 180
  amended_requires_marker: true              # no marker => not an amendment
  on_lower_rebill_without_marker: OVERCHARGE # anomalies still surface
  duplicate_disposition: SHORT_PAY

• PRO-keyed dedup in CI. Assert that two lines sharing a PRO and fingerprint collapse to one settlement on a sample batch.
• Marker completeness check. Alert when amendment_ref is present but points to a PRO with no prior settlement — that is a broken correction reference, not a valid amendment.
• Lookback window. Keep the prior-settlement index at least as wide as the carrier’s longest re-bill lag so a late correction still finds its original.

FAQ

Why not just use the invoice number to detect duplicates?

Because carriers change it. A corrected re-bill or a redelivery frequently carries a new invoice number for the same PRO, so an invoice-number key treats one movement as two shipments — missing the duplicate and mis-pricing the re-bill. The PRO is the movement’s stable identity; key on it and the duplicate reappears.

How is an amended re-bill different from an overcharge if both raise the amount?

The amendment marker. A legitimate correction carries an amendment_ref (or corrected-total reference) that the carrier issued on purpose; it supersedes the original and re-enters validation. A second charge with a higher amount and no marker is unexplained — that is an overcharge to dispute, not a correction to accept.

What makes the charge fingerprint stable across redelivery?

It hashes only the fields that define the charge’s economic identity — charge code, amount to the cent, and service date — and normalizes them first. It deliberately excludes volatile fields like invoice number, transmission timestamp, or batch id, so a byte-identical redelivery produces the same fingerprint and is caught as a duplicate.

Should a re-bill at a lower amount be auto-accepted?

No. A lower re-bill with a valid amendment marker is fine, but without one it is still an anomaly — the same PRO billed twice for different amounts with no explanation. Route it to review as an overcharge so a human confirms why, rather than silently banking the difference and hiding a carrier billing defect.

Related

• Charge Variance Classification — the parent engine this identity logic feeds its typed result into.
• Reconciliation Ledger Schema — the settled-charge ledger the classifier reads prior settlements from and supersedes amendments in.
• Implementing Async Batch Invoice Processing with Celery — the ingestion-side idempotency that keeps a redelivered payload from re-entering as a new invoice.
• Discrepancy Resolution & Dispute Routing — the stage that routes the overcharge and duplicate dispositions this page produces.

Up one level: Charge Variance Classification · Section: Discrepancy Resolution & Dispute Routing