Substitute Payroll and Extra-Duty Pay Reconciliation: Mapping SIS Assignments to Payroll Codes and Exception Scripts

The pay period closes on the 15th. Your substitute coordinator uploads the coverage log, payroll runs the export, and three weeks later a middle school teacher emails asking why she got paid for one after-school detention shift instead of the four she covered. Someone pulls the SIS record, someone else pulls the timekeeping export, and the two don't match. Nobody can explain it without an hour of manual digging.

That gap — between what the SIS says happened and what payroll actually paid — is where most substitute payroll reconciliation problems in K-12 live. It's not a fraud problem. It's a mapping problem. The assignment data and the pay data speak two different languages, and someone has to translate between them every single pay period, usually by hand, usually under a deadline.

This post is about that translation layer: how you map SIS assignments to payroll codes, how you build reconciliation exports that actually catch mismatches, and how exception scripts should be written around your district's contract language instead of some generic default.

The core problem: two systems, two vocabularies

Your SIS knows a substitute filled a position. It records something like SUB-FULLDAY, a date, a school, and the absent teacher's ID. Your payroll system doesn't care about any of that. It wants a pay code — say SUB-DAILY-CERT at one rate and SUB-DAILY-NONCERT at another — plus a cost center, a fund code, and sometimes a grant string.

Between those two lives a set of assumptions nobody wrote down. The daily sub rate depends on whether the sub is certified. The half-day threshold might be 3.5 hours in one contract and 4 hours in another. Long-term sub status kicks in after a set number of consecutive days, at which point the rate jumps — and that day has to be paid at the new rate, not the next pay period.

Extra-duty pay is worse because it's rarely hourly. A ticket-taker at a Friday game gets a flat stipend. A coach gets a seasonal amount split across pay periods. A teacher covering their prep gets a per-period rate defined in the association contract. The SIS captures the event; payroll needs the dollar rule. Nothing in either system connects them automatically.

Where the mismatches actually come from

Before you can map anything cleanly, it helps to know exactly which failure points generate the exceptions. In practice, errors cluster into a handful of predictable buckets.

1. 1
   Rate tier misfires. A sub crosses the long-term threshold mid-week but the assignment still carries the daily code. The extra pay owed accumulates quietly until someone complains.
2. 2
   Half-day rounding disagreements. SIS logs 3.75 hours. One contract rounds that to a full day; another pays it as a half. Whichever rule the clerk applies from memory is the one that ships.
3. 3
   Duplicate coverage entries. A sub gets logged in two places — the SIS and a paper sign-in sheet the front office also enters — and both feed payroll.
4. 4
   Missing cost center. The assignment exists but has no fund string, so it either falls into a default bucket or gets dropped from the export entirely.
5. 5
   Stipend timing. A seasonal coaching stipend is entered as a lump sum but should be split across the right number of pay periods per the contract.

Worth noticing: almost none of these start as data-entry mistakes. The SIS record is usually correct. The failure happens in translation — when a correct assignment meets an ambiguous or undocumented pay rule.

A concrete mapping table

The fix starts with writing the translation down as an actual ruleset instead of tribal knowledge. A workable mapping table looks something like this:

The exact codes and thresholds are yours — the point is that every SIS assignment type needs a deterministic rule that resolves to one pay code and one cost center. If a row requires a human to "just know" the answer, that's an exception waiting to happen.

Notice the ≥ 20 consec. days condition. That single rule is where a disproportionate share of grievances originate, because consecutive-day counting spans pay periods and someone has to track the running total across uploads. It's the condition most likely to be applied late.

Building the reconciliation export

Once you have the mapping, reconciliation stops being a hunt and becomes a diff. You're comparing two things:

1. 1
   What the SIS says should be paid (assignments run through the mapping table)
2. 2
   What payroll actually queued (the pay register for that period)

The export should surface only the rows where those two disagree. Everything that matches is noise.

A practical export process runs like this:

1. 1
   Pull the SIS assignment extract for the pay period — every sub day, coverage, and stipend event with dates, staff IDs, and hours.
2. 2
   Apply the mapping ruleset to produce an expected pay line for each assignment, including code, rate, and cost center.
3. 3
   Pull the payroll register for the same window and staff set.
4. 4
   Join on staff ID + date + assignment type and flag any row where expected and actual differ on code, amount, or cost center.
5. 5
   Categorize each mismatch into an exception type (rate tier, duplicate, missing cost center, timing).
6. 6
   Route each category to the person who can resolve it, with the underlying records attached.

The diagram below shows the export workflow.

“

Include the running consecutive-day count as a field in the SIS extract so the export can flag LT switches automatically.

Step 5 is what separates a useful reconciliation from a giant list of red cells. A payroll clerk shouldn't have to figure out why a row is off — the export should tell her it's a long-term-threshold miss versus a duplicate entry, because those go to different people and get fixed differently.

This is also the discipline that makes district audits go smoothly. When reconciliation produces categorized, attributable exceptions, you already have most of what an auditor asks for. The approach mirrors the owner-mapped export logic covered in School Audit Preparation: Owner-Mapped Exports and Automated Evidence Assembly — pay records traceable back to a source assignment and a named rule are records you never have to reconstruct later.

Exception-handling scripts tied to contract language

Generic reconciliation tools break on districts because they don't know your contracts. The exception scripts have to encode your language, not a vendor's assumptions.

An exception script is a written rule that says: when a mismatch of this type appears, here is how it gets resolved and who resolves it. A few examples of how they should read:

1. 1
   Long-term threshold crossed mid-period

   Recalculate all days at or after the threshold day at the LT rate. Owner: payroll clerk. Contract reference: Article 9.3. Requires supervisor sign-off if retroactive adjustment exceeds one pay period.

2. 2
   Half-day rounding dispute

   Apply the contract's stated rounding rule (round to nearest half). If hours fall in the 3.4–3.6 band, hold for coordinator confirmation. Owner: sub coordinator.

3. 3
   Duplicate coverage

   Keep the SIS entry, suppress the manual-entry line, log the suppression. Owner: front office lead.

4. 4
   Missing cost center

   Route back to the assigning administrator; do not default to a fallback fund. Owner: building admin.

The reason to write these down with contract citations is that grievances get resolved on paper. When an association rep questions a pay adjustment, "the script applied Article 9.3" is a defensible answer. "The system did it" is not.

One thing worth flagging: districts often try to build one exception script set for the whole organization. But if you run multiple bargaining units — certified, classified, and an athletics/activities agreement — you almost always need separate rule sets. The same coverage event can pay differently depending on which contract the staff member falls under. Collapsing those into one ruleset is how you end up paying a classified sub at a certified rate.

A real scenario

A mid-sized district — around 4,200 students across nine buildings — was reconciling substitute and extra-duty pay manually every two weeks. Two staff spent the better part of a day each cycle cross-checking the SIS coverage log against the payroll register in spreadsheets.

Their recurring pain was long-term sub rate misses. Because consecutive-day counts spanned pay periods, the daily-to-LT rate switch was routinely applied a period late. Over a year they'd issued somewhere in the range of $9k–$12k in retroactive corrections, plus two grievances that ate a meaningful chunk of HR time.

They rebuilt the process around a written mapping table and typed exception scripts, then generated a categorized reconciliation export each period instead of eyeballing spreadsheets. Manual reconciliation time dropped from roughly a full day to under two hours. More importantly, the long-term threshold catches moved to the correct pay period because the running consecutive-day count was calculated in the export rather than tracked in someone's memory. Retroactive corrections didn't disappear, but they became rare enough that grievances stopped.

The interesting part wasn't the time savings. It was that the payroll clerk stopped being a single point of failure. When she took a two-week leave, reconciliation still ran, because the rules lived in the ruleset instead of in her head.

When this level of structure makes sense — and when it doesn't

Building a full mapping-and-exception layer is real work. It's worth it when:

1. 1
   You run multiple bargaining units with different rate rules.
2. 2
   Long-term or tiered rates create recurring retroactive corrections.
3. 3
   Extra-duty and stipend pay is a meaningful share of your payroll volume.
4. 4
   Reconciliation currently depends on one or two people who can't be out during close.

It's probably overkill when:

1. 1
   You're a small district with a single flat sub rate and no tiered thresholds.
2. 2
   Extra-duty pay is a handful of stipends a year that fit on one page.
3. 3
   Your SIS and payroll already share a tight, tested integration that hasn't produced errors.

And a caution on who should not rush into this: if your SIS assignment data is unreliable at the source — subs logged inconsistently, coverage entered days late, missing staff IDs — fix that first. A mapping layer built on dirty source data just automates the wrong answers faster. Reconciliation exposes upstream data problems; it doesn't fix them.

Where automation helps without replacing judgment

The parts of this process that genuinely benefit from workflow platforms enhanced with automation are the repetitive, deterministic ones: pulling the two extracts, applying the mapping rules, running the diff, categorizing exceptions, and routing each one to the right owner. That's mechanical work a person shouldn't be doing by hand every two weeks.

What automation should not do is silently resolve exceptions. A rate-tier mismatch or a rounding dispute needs a person to confirm, because the resolution often depends on contract nuance and occasionally on a conversation with the association. A good operational platform surfaces the categorized exception with the source records attached and lets the right owner decide — the same coordination logic that keeps complex scheduling clean, as in Multi-Campus Timetable Optimization to Reduce Room Conflicts and Improve Coverage, where the system organizes the decision but a human still makes the call.

The goal isn't to remove people from payroll. It's to remove the manual translation between two systems that were never designed to talk to each other, so your staff spend their time on the handful of real exceptions instead of re-checking hundreds of rows that already match.

Closing thought

Substitute and extra-duty pay reconciliation feels like a data-entry problem, but it's really a rules problem. The assignments are usually right. What's missing is a written, contract-specific translation between what the SIS records and what payroll pays — plus exception scripts that say exactly how each mismatch gets resolved and by whom.

Write the mapping table. Tie the exception scripts to your actual contract articles. Build a reconciliation export that shows only the disagreements, categorized and routed. Do that, and the twice-a-month scramble turns into a short review of a handful of real exceptions — and a teacher asking why she got paid for one shift instead of four becomes a rare event instead of a recurring one.