Reconciling EDI 834 Enrollments to Payroll Deductions

An 834 file can parse perfectly and still leave payroll wrong, because enrollment data and deduction data live in two systems that only agree if a reconciliation stage forces them to. This pattern sits downstream of EDI 834 Parsing, the parent topic within the Multi-Format Payroll Data Ingestion & Normalization framework, and closes the gap between “the carrier says this member is enrolled” and “payroll is actually withholding the right premium every pay period.”

Problem Framing

A validated Enrollment834 record is a statement about coverage, not about payroll. It says a member elected a plan effective on a date, with a premium amount the carrier will bill. It says nothing about whether the payroll system’s deduction table has a matching line, whether that line’s amount agrees to the cent, or whether it started and stopped on the right dates. Three specific failure patterns break naive implementations that treat “parsed” as “reconciled”:

• Silent enrollment without deduction. An INS add (INS03 = 021 or 001) loads cleanly into the eligibility system, but no corresponding deduction line is ever created in payroll — often because the deduction engine’s batch ran before the enrollment file landed, or because a plan-code mapping was missing and the record fell through to a manual queue that no one worked. The member believes they have coverage; the employer is not withholding the premium the carrier will bill for.
• Termination that does not stop the deduction. An INS cancel (INS03 = 024) with a DTP*349 termination date reaches the eligibility system, but the payroll deduction line has no matching end date, so the deduction keeps firing after coverage ends. This is the more expensive direction of the mismatch: the employee is paying for coverage they no longer have, which is both a wage-and-hour exposure and a refund liability once discovered.
• Mid-period effective dates averaged instead of prorated. An election effective on the 12th of a semi-monthly period gets billed as a full-period deduction because the mapping only checks “is this plan active,” not “for how many of this period’s days.” Prorating by calendar fraction — rather than defaulting to a full premium or skipping the period entirely — is the difference between an audit-clean deduction register and a discrepancy the carrier invoice surfaces first.

All three converge on the same requirement: reconciliation must compare the same two decimal-precise numbers — expected premium and actual deduction — for the same effective window, and it must do so as an explicit gate, not an assumption baked into the deduction batch. The Decimal discipline enforced in Enforcing Decimal Precision Across Payroll Fields applies here at the comparison boundary, not only inside the parser.

Prerequisites & Data Requirements

Reconciliation consumes the canonical Enrollment834 records already produced by EDI 834 Parsing — it does not re-parse raw segments. It also requires read access to the current payroll deduction ledger for the same member set and pay period.

Two preconditions sit outside the function signature:

1. Enrollment records are already structurally valid. Any 834 segment that failed envelope, loop, or field validation was quarantined before reaching this stage; reconciliation never receives a record with a missing member_id or an unparseable date.
2. The plan-code-to-deduction-code map is complete and versioned. An HD03 plan code with no mapping entry is itself a discrepancy — plan_code_unmapped — and must not silently skip the member.

Step-by-Step Implementation

Step 1 — Model the two sides of the comparison. Keep the enrollment-derived expectation and the payroll-ledger actual as separate typed records so a mismatch in either is explicit, never inferred.

from dataclasses import dataclass
from datetime import date
from decimal import Decimal, ROUND_HALF_UP
from typing import Dict, List, Optional
import logging

logger = logging.getLogger("payroll.edi834.reconcile")

CENTS = Decimal("0.01")


@dataclass(frozen=True)
class ExpectedDeduction:
    member_id: str
    plan_code: str
    action_code: str
    coverage_effective: date
    coverage_termination: Optional[date]
    period_premium: Decimal  # prorated for this pay period


@dataclass(frozen=True)
class ActualDeduction:
    member_id: str
    plan_code: str
    deduction_amount: Decimal
    deduction_start: date
    deduction_end: Optional[date]


@dataclass(frozen=True)
class Discrepancy:
    member_id: str
    plan_code: str
    code: str  # enrolled_not_deducted | termed_still_deducting | amount_mismatch
    expected_amount: Decimal
    actual_amount: Decimal
    detail: str

Expected output: three importable dataclasses with no behavior yet; mypy/dataclasses enforce that every field is typed and period_premium cannot be constructed from a float literal without an explicit Decimal(...) call.

Step 2 — Prorate the premium for a mid-period effective date. Convert a full-period carrier premium into the amount owed for the days actually covered within one pay period, using integer day counts under Decimal throughout.

def prorate_premium(
    full_period_premium: Decimal,
    period_start: date,
    period_end: date,
    coverage_effective: date,
    coverage_termination: Optional[date],
) -> Decimal:
    """Prorate a full-period premium by the fraction of period days covered."""
    period_days = Decimal((period_end - period_start).days + 1)
    covered_start = max(period_start, coverage_effective)
    covered_end = min(period_end, coverage_termination or period_end)

    if covered_start > covered_end:
        return Decimal("0.00")

    covered_days = Decimal((covered_end - covered_start).days + 1)
    prorated = (full_period_premium * covered_days / period_days).quantize(
        CENTS, rounding=ROUND_HALF_UP
    )
    logger.info(
        "edir_prorate covered_days=%s period_days=%s prorated=%s",
        covered_days, period_days, prorated,
    )
    return prorated

Expected output: a full-period premium of Decimal("300.00") over a 30-day period, with coverage_effective on day 12, returns Decimal("190.00") (19 of 30 days).

Step 3 — Build the expected deduction from parsed 834 fields. Apply the maintenance type so an add or change produces a positive expectation and a term produces a zero expectation once the termination date is behind the period.

def build_expected_deduction(
    member_id: str,
    plan_code: str,
    action_code: str,
    coverage_effective: date,
    coverage_termination: Optional[date],
    full_period_premium: Decimal,
    period_start: date,
    period_end: date,
) -> ExpectedDeduction:
    """Translate one 834 enrollment event into an expected per-period deduction."""
    if action_code == "024" and coverage_termination and coverage_termination < period_start:
        period_premium = Decimal("0.00")
    else:
        period_premium = prorate_premium(
            full_period_premium, period_start, period_end,
            coverage_effective, coverage_termination,
        )
    return ExpectedDeduction(
        member_id=member_id,
        plan_code=plan_code,
        action_code=action_code,
        coverage_effective=coverage_effective,
        coverage_termination=coverage_termination,
        period_premium=period_premium,
    )

Expected output: for action_code="024" with coverage_termination two periods in the past, period_premium == Decimal("0.00").

Step 4 — Reconcile expected against actual and emit named discrepancies. Compare to the cent; never round the actual side to match the expected side or vice versa.

def reconcile(
    expected: List[ExpectedDeduction],
    actual: Dict[str, ActualDeduction],  # keyed by f"{member_id}:{plan_code}"
) -> List[Discrepancy]:
    """Compare expected 834-derived deductions against the actual payroll ledger."""
    discrepancies: List[Discrepancy] = []

    for exp in expected:
        key = f"{exp.member_id}:{exp.plan_code}"
        act = actual.get(key)

        if exp.period_premium > Decimal("0.00") and act is None:
            discrepancies.append(Discrepancy(
                exp.member_id, exp.plan_code, "enrolled_not_deducted",
                exp.period_premium, Decimal("0.00"),
                "active enrollment has no matching deduction line",
            ))
            logger.warning(
                "edir_discrepancy code=enrolled_not_deducted member=%s plan=%s expected=%s",
                exp.member_id, exp.plan_code, exp.period_premium,
            )
            continue

        if exp.period_premium == Decimal("0.00") and act is not None and act.deduction_end is None:
            discrepancies.append(Discrepancy(
                exp.member_id, exp.plan_code, "termed_still_deducting",
                exp.period_premium, act.deduction_amount,
                "coverage termed but deduction line has no stop date",
            ))
            logger.warning(
                "edir_discrepancy code=termed_still_deducting member=%s plan=%s actual=%s",
                exp.member_id, exp.plan_code, act.deduction_amount,
            )
            continue

        if act is not None and act.deduction_amount != exp.period_premium:
            discrepancies.append(Discrepancy(
                exp.member_id, exp.plan_code, "amount_mismatch",
                exp.period_premium, act.deduction_amount,
                "deduction amount does not tie to prorated premium",
            ))
            logger.warning(
                "edir_discrepancy code=amount_mismatch member=%s plan=%s expected=%s actual=%s",
                exp.member_id, exp.plan_code, exp.period_premium, act.deduction_amount,
            )

    return discrepancies

Expected output: an add with period_premium=Decimal("190.00") and no matching actual entry yields one Discrepancy with code="enrolled_not_deducted".

Step 5 — Run reconciliation for one pay period. Wire the steps together against a small in-memory ledger to confirm the full path end to end.

expected_records = [
    build_expected_deduction(
        member_id="M-7734", plan_code="MED-PPO", action_code="021",
        coverage_effective=date(2026, 7, 12), coverage_termination=None,
        full_period_premium=Decimal("300.00"),
        period_start=date(2026, 7, 1), period_end=date(2026, 7, 15),
    ),
]
actual_ledger = {}  # no deduction line posted yet

results = reconcile(expected_records, actual_ledger)
# results[0].code == "enrolled_not_deducted"
# results[0].expected_amount == Decimal("126.67")

Verification

• Add creates a deduction. Feed an action_code="021" record with a full-period effective date and an actual ledger entry whose deduction_amount equals the un-prorated period_premium; assert reconcile() returns an empty list.
• Term stops the deduction. Feed an action_code="024" record with coverage_termination before period_start, and an actual entry with deduction_end set to that same date; assert no termed_still_deducting discrepancy. Remove deduction_end from the same actual entry and assert the discrepancy appears with actual_amount equal to the still-active deduction.
• Mid-period proration to the cent. With full_period_premium=Decimal("300.00"), a 30-day period, and coverage_effective on day 12, assert prorate_premium(...) == Decimal("190.00") exactly — 19 of 30 days at Decimal("10.00")/day, never a float-derived approximation like 189.99999999.
• Enrolled-not-deducted detection. Feed an active add with no actual entry at all and assert exactly one Discrepancy with code="enrolled_not_deducted" and expected_amount matching the prorated value.
• Premium tie-out across a batch. Sum period_premium across every expected record with no discrepancy and assert it equals the sum of deduction_amount in the matching actual ledger entries, to the cent, using Decimal equality rather than a tolerance band.

Failure Modes

• Deduction created from the load date instead of the coverage-effective date. Root cause: the deduction engine’s batch job stamps a new deduction line with today’s date rather than reading DTP*348 from the enrollment record, so a retroactive election posts a full, non-prorated premium starting from processing day. Remediation: always derive coverage_effective from the parsed 834 record and run every deduction line through prorate_premium() against the pay period it lands in, never the batch run date.
• Plan-code drift between carrier and payroll. Root cause: the carrier renames or splits an HD03 plan code and the deduction mapping table is not updated in the same release, so every affected member’s expected deduction silently maps to nothing and reconciliation reports enrolled_not_deducted for the entire plan at once. Remediation: treat an unmapped plan_code as its own discrepancy class (plan_code_unmapped) surfaced separately from per-member mismatches, and route it to Fallback Routing for Unclassified Deductions rather than letting it look like a batch of individual member errors.
• Termination processed after the deduction batch already ran. Root cause: the 834 termination file arrives after payroll’s deduction cutoff for the current period, so the member is correctly flagged as termed_still_deducting but the fix cannot land until the next cycle, and without a carry-forward refund flag the overage is never returned. Remediation: when a termination discrepancy is detected for a period that already disbursed, emit a refund-eligible flag alongside the discrepancy so a subsequent pay period’s off-cycle adjustment run picks it up automatically instead of waiting for a manual complaint.

Frequently Asked Questions

Related

• Parsing EDI 834 Files with Python — the implementation walkthrough that produces the canonical enrollment records this stage consumes.
• EDI 834 Parsing — the parent topic covering envelope validation, loop-state assembly, and effective-dated coverage resolution.
• Ordering Pre-Tax 401(k) and Section 125 Deductions — the deduction-sequencing rules that apply once a premium deduction is confirmed reconciled.
• Deduction Mapping Rules — the topic covering how plan and garnishment codes map to payroll deduction lines and general-ledger accounts.