Volume Rebate Reconciliation
A five-stage annual workflow. Food processors and food distributors submit velocity reports in three different formats; DJ Co-Ops ingests them, normalizes to a canonical shape, matches against RA numbers, resolves exceptions, and generates per-school-district payouts. The full reconciliation loop is what made volume-rebate season the credibility test of the production system.
- Workspace
- Admin / back-office (reconciliation owner)
- Primary task
- Take incoming velocity reports in incompatible formats, reconcile them against RA numbers and school district orders, resolve exceptions, and produce per-school-district rebate payouts.
- Complexity hidden
- Three different reporter formats (K12 canonical CSV, ProcessorLink Excel-style with manufacturer-abbreviation mapping, Lunchline pipe-delimited text), RA-number matching, exception triage, and per-school-district payout calculation.
- In production
- Volume rebate reconciliation processed 614,000+ transaction lines across 7 school years (2005–2012 preserved evidence), coordinating payouts across the 130+ food processor catalog and 16+ food distributor relationships.
- Demo simplification
- Three reporter formats, 20 synthetic school districts, one complete payout round for school year 2013–14.
Inbound velocity reports
Three food processors submitted velocity reports for the 2013-14 school year, each in their preferred reporter format. Click Import all reports to ingest them into the workspace and unlock the next stage.
Normalize reporter formats to canonical shape
Three reporter formats (K12foodservice · ProcessorLink · Lunchline) become one canonical line shape: { processorId, raNumber, districtId, qty, dollars, period }. The normalizer also resolves the ProcessorLink Mfr_Abbr lookup against dbo.ProcessorLinkMfrLookup and coerces format-anomaly values (commas in dollar fields, suffixes in qty fields).
RA number cross-reference
Each normalized line must match to a known school district by canonical districtId and have a valid Rebate Authorization (RA) number. Lines with both pass through to the payout calculation; lines with either missing fall to the Exception list.
Matched lines —
Unmatched lines —
Resolve exception cases
Lines that didn't match cleanly. Each exception has a resolution action: Assign RA (for missing RA), Reformat (for format mismatches), Map district (for unmatched school districts), or Skip (omit from payout). Exception resolutions persist across sessions.
Per-school-district payout summary
Final per-school-district rebate dollars after matching, exception resolution, and rebate-rate computation. School districts receive these credits applied against next year's USDA Foods entitlement allocation.