Reconciling EP/Showbiz Fringe Detail Lines

Entertainment Partners and Showbiz Budgeting do not export an employer’s fringe burden as a single number attached to a paycheck; they explode it into a stack of separate detail lines — one per fund — each carrying its own rate, its own fund code, and a computed amount that is supposed to trace back to a specific gross earning. When those detail lines arrive in the nightly feed described in EP/Showbiz Sync Parsing, they are structurally valid rows that can still be financially wrong: a fringe line whose amount does not equal its rate times the gross it references, a line pointing at an earning that is not in the batch, or a line stamped with a fund code the loaded rate schedule does not recognize. This walkthrough builds the reconciliation step that catches all three — grouping each fringe detail line to its parent gross, recomputing the expected contribution in Decimal, confirming the totals tie out, and quarantining anything that does not — so a payroll export is proven consistent before a single fringe dollar posts to the general ledger.

Prerequisites and Context

This page assumes the raw export has already cleared the parsing gate: amounts are typed, cost codes are well-formed, and each row has survived the boundary schema built in EP/Showbiz Sync Parsing. Reconciliation runs one layer later, on the typed records, and cares only about internal consistency between earnings and their fringes. The implementation targets Python 3.11+ for standard-library zoneinfo, and uses the same deliberate stack as the rest of Cost Ingestion & Data Parsing Workflows: the decimal module for every monetary value and every percentage rate, hashlib for the payload fingerprint, zoneinfo for timezone-aware audit stamps, and Pydantic v2 (model_validate, field_validator, ConfigDict(frozen=True)) for the earning and fringe schemas.

Two upstream contracts matter here. The fund code on each fringe line must resolve against an approved schedule — the same taxonomy discipline enforced in Cost Code Standardization — because a fringe line that ties out arithmetically but books to the wrong fund is still a misstatement. And the fringe rates themselves originate in collective bargaining agreements: an International Alliance of Theatrical Stage Employees (IATSE) health-and-welfare contribution, a Screen Actors Guild – American Federation of Television and Radio Artists (SAG-AFTRA) pension-and-health accrual, and a Directors Guild of America (DGA) fund line each carry a distinct rate keyed to contract year and jurisdiction. The authoritative computation of those rates belongs to Pension & Health Fund Calculations; this step does not recompute the guild math from scratch, it verifies that the number EP or Showbiz already placed on the line agrees with the rate and gross it claims to be derived from. That distinction is what a completion guarantor’s auditor relies on: an underfunded trust remittance hidden inside a plausible-looking fringe line is exactly the variance that reopens a reserve calculation.

Step-by-Step Implementation

Reconciliation is a grouping problem followed by a per-line arithmetic check. Each gross earning is keyed by an earning_id; every fringe detail line references that same key. The routine indexes the earnings, groups the fringe lines by their parent, and for each line resolves one of exactly two outcomes: it ties out and flows to the ledger feed, or it fails — as an orphan, an unknown fund, or a tie-out mismatch — and flows to quarantine with a reason code and a fingerprint. Money and rates are Decimal throughout, and the expected contribution is quantized to cents with ROUND_HALF_UP at the point it becomes a comparable figure.

from __future__ import annotations

import hashlib
import json
from collections import defaultdict
from datetime import datetime
from decimal import Decimal, ROUND_HALF_UP
from zoneinfo import ZoneInfo

from pydantic import BaseModel, ConfigDict, Field, field_validator

CENTS = Decimal("0.01")
LEDGER_TZ = ZoneInfo("America/Los_Angeles")
# Absorbs a single half-cent rounding step on a percentage fringe, never a real gap.
TIE_OUT_TOLERANCE = Decimal("0.01")

# Fund codes the loaded rate schedule recognizes for this contract year.
KNOWN_FUND_CODES = {"IA-HW", "IA-PEN", "SAG-PH", "SAG-PEN", "DGA-PH"}


class GrossEarning(BaseModel):
    """The taxable gross a set of fringe detail lines must reconcile against."""

    model_config = ConfigDict(frozen=True)

    earning_id: str
    employee_id: str
    cost_code: str
    gross_amount: Decimal = Field(gt=Decimal("0"))

    @field_validator("gross_amount", mode="before")
    @classmethod
    def _no_float(cls, v: object) -> Decimal:
        if isinstance(v, float):
            raise ValueError("gross_amount must be str/Decimal, never float")
        return Decimal(str(v))


class FringeDetailLine(BaseModel):
    """One employer-side fringe accrual EP/Showbiz emits as its own row."""

    model_config = ConfigDict(frozen=True)

    fringe_id: str
    earning_id: str            # foreign key back to the gross earning
    fund_code: str             # e.g. IATSE H&W, SAG-AFTRA P&H
    rate: Decimal              # decimal fraction of gross, e.g. 0.0925
    reported_amount: Decimal   # the amount EP/Showbiz placed on the line

    @field_validator("rate", "reported_amount", mode="before")
    @classmethod
    def _no_float(cls, v: object) -> Decimal:
        if isinstance(v, float):
            raise ValueError("rate/amount fields must be str/Decimal, never float")
        return Decimal(str(v))


def _fingerprint(payload: dict) -> str:
    canonical = json.dumps(payload, sort_keys=True, separators=(",", ":"))
    return hashlib.sha256(canonical.encode("utf-8")).hexdigest()


def reconcile_fringes(
    earnings: list[GrossEarning], fringes: list[FringeDetailLine]
) -> tuple[list[dict], list[dict]]:
    earning_by_id = {e.earning_id: e for e in earnings}
    grouped: dict[str, list[FringeDetailLine]] = defaultdict(list)
    for line in fringes:
        grouped[line.earning_id].append(line)

    tied_out: list[dict] = []
    quarantine: list[dict] = []

    for earning_id, lines in grouped.items():
        parent = earning_by_id.get(earning_id)
        for line in lines:
            expected = None
            if parent is not None:
                expected = (parent.gross_amount * line.rate).quantize(
                    CENTS, rounding=ROUND_HALF_UP
                )

            reason: str | None = None
            if parent is None:
                reason = "orphan_fringe_no_gross"
            elif line.fund_code not in KNOWN_FUND_CODES:
                reason = "unknown_fund_code"
            elif abs(line.reported_amount - expected) > TIE_OUT_TOLERANCE:
                reason = "fringe_tie_out_mismatch"

            record = {
                "fringe_id": line.fringe_id,
                "earning_id": earning_id,
                "fund_code": line.fund_code,
                "rate": str(line.rate),
                "reported_amount": str(line.reported_amount),
                "expected_amount": str(expected) if expected is not None else None,
                "gross_amount": str(parent.gross_amount) if parent else None,
                "reconciled_at": datetime.now(LEDGER_TZ).isoformat(),
            }
            record["payload_sha256"] = _fingerprint(record)

            if reason is None:
                record["status"] = "tied_out"
                tied_out.append(record)
            else:
                record["status"] = "quarantined"
                record["reason_code"] = reason
                quarantine.append(record)

    return tied_out, quarantine

Because reconcile_fringes reads nothing from the network and holds no state between runs, the same earnings and fringe lines always produce the same partition and the same fingerprints. That determinism is the property that lets a production accountant re-run a disputed week and get a byte-identical answer, and it is what a guarantor’s examiner reconstructs when asking why a specific fund line was held back. Note that the tie-out uses an absolute tolerance of one cent rather than exact equality — a deliberate choice examined in the gotchas below, because a percentage fringe legitimately rounds and a zero-tolerance comparison would quarantine correct lines by the thousand.

The decision path each detail line follows is small and total: every line resolves to exactly one of the tie-out feed or the quarantine queue, and no line is dropped.

Fringe detail line reconciliation and quarantine flow A gross earning and its fringe detail lines enter reconciliation. The lines are grouped by earning_id, then for each line the expected contribution is recomputed as gross times rate in Decimal, quantized to cents. A single decision asks whether the reported amount matches the expected amount within tolerance and the fund code is recognized. If it matches, the line ties out and flows to the ledger feed. If it does not match, is an orphan with no parent gross, or carries an unknown fund code, the line is quarantined with a reason code. Both branches converge on one terminal step that serializes the payload and stamps it with a SHA-256 audit hash. Gross + fringe lines one earning_id, N lines Group by earning_id collect fringe lines Expected = gross × rate Decimal · quantized reported = expected fund valid? match Tie-out passes post to ledger feed mismatch Mismatch or orphan Quarantine line reason_code + fingerprint Serialize payload + SHA-256 audit hash

Audit Trail Requirements

Every reconciled line — tied-out and quarantined alike — must be serialized to write-once storage, so an examiner reviewing the run sees the whole population rather than only the exceptions. At minimum the persisted record must carry: the fringe_id and its parent earning_id; the fund_code; the rate and the reported_amount as strings; the recomputed expected_amount and the gross_amount it was derived from; the timezone-aware reconciled_at stamp; the resolved status; the reason_code on any quarantined line; and the payload_sha256 fingerprint of the canonical record. Persist to append-only storage — object storage under an immutability lock, or an event-sourced table with no in-place update — before the tied-out lines commit to the ledger, so a crash mid-run leaves a replayable record of intent rather than a silent gap between what was checked and what was posted.

A later correction is a new reconciliation record that supersedes the quarantined one, never an edit of it, so the figure the original run was built on stays recoverable. This is the same write-once discipline formalized in Audit Logging & Immutability, and pairing the per-line fingerprint with the whole-file SHA-256 stamped at intake by EP/Showbiz Sync Parsing is what lets any fringe dollar — accepted or held — be traced back to the exact export row and the exact gross it reconciled against.

Gotchas and Production Edge Cases

Rounding on percentage fringes. A fringe is a rate applied to a gross, and a rate applied to a gross almost never lands on a whole cent. EP and Showbiz round their line to cents; if the reconciler recomputes to full precision and demands exact equality, it quarantines thousands of correct lines over a half-cent that both sides rounded differently. Quantize the expected amount to cents with ROUND_HALF_UP and compare within a one-cent tolerance, as the code does — but keep the tolerance at a single cent, because widening it to absorb “small” differences is how a genuine rate error slips through disguised as rounding.

Orphan fringe detail lines. A fringe line whose earning_id matches no gross in the batch is the most dangerous case, because it looks complete in isolation. It arises when a payroll edit voids an earning but leaves its fringes behind, or when a fringe line and its parent land in different batches after a mid-run split. Never treat an orphan as zero-gross and never post it against a guessed parent; route it to quarantine with orphan_fringe_no_gross so the missing earning is reconciled deliberately rather than absorbed silently into a fund total.

Float contamination. Never build a Decimal from a float literal — Decimal(0.0925) imports the binary rounding error into the very rate the tie-out depends on. Read every rate and amount through its string form, as Decimal(str(...)) above, so a fractional-cent artifact never becomes the discrepancy a trust-fund administrator asks you to explain across a season of timecards.

Multi-unit shoots. When two units report on the same night, their exports can reuse earning identifiers scoped only within a unit. Namespace earning_id by unit before grouping, or an earning from one unit will falsely satisfy a fringe line from another and produce a tie-out that reconciles cleanly to the wrong gross.

Idempotency on replay. Because the record fingerprint is deterministic and the store is append-only, re-running reconciliation after a partial failure is safe only if the ledger consumer deduplicates on (fringe_id, earning_id, payload_sha256); without that guard a retried batch double-posts a fringe accrual. When a fund’s rate table is missing rather than wrong, do not inline a guess — quarantine the line and resolve the rate through the controlled path in Pension & Health Fund Calculations before replaying.

Up one level: EP/Showbiz Sync Parsing, part of Cost Ingestion & Data Parsing Workflows.