One pay code, many places in the books

Overtime is one earning in payroll. In the books it can be three different lines, depending on who earned it and where. The rule that decides is real, and in most companies nobody has ever seen it written down.

RuleLand the mapping as a table you can query, with a date on every row, so the rule that routes money is visible and versioned.

Neverhard-code account routing into report logic. The rule changes, the reports keep the old one, and the drift begins there.

◆ What goes wrongthe plain version.

Payroll thinks in pay codes: base salary, overtime, a bonus. The books think in accounts: where each cost is recorded. Between them sits a routing rule, and it is not one to one. The same overtime can be recorded as factory labor for a plant worker and as operating salary for an office worker.

One code in, three destinations out. The rule in the middle decides, and it lives only inside the system.

The rule itself is configuration, spread across posting setups by company, worker type, and location. It works. But when a controller asks why overtime shows up in two accounts, the answer requires a specialist to go read the configuration, and the same question comes back next quarter. This is the third drift cause on the ledger tie page, seen from the inside.

◆ Why it costs real moneythree failures, all quiet.

Failure	What actually happens
New pay code, no route	A new earning is created mid year, a retention bonus, say. Until someone maps it, its cost posts to a default account. The books still balance, and the bonus is invisible in every departmental report.
The rule changes silently	Posting configuration is edited when the business changes. Reports built on the old routing keep working and quietly disagree with the books from that day forward. Nobody announced the change, because nobody knew reports depended on it.
Two people, two answers	The routing depends on worker group, company, and location at once. Two analysts, each holding part of the rule in their head, produce different labor cost for the same department. Both are defensible. Neither is checkable.

◆ What we buildthe rule as a table, dated, queryable.

The mapping lands in the warehouse as its own table: pay code, worker group, company, the account it routes to, and the dates the route was true. That last part matters. When the rule changes, the old row keeps its end date instead of disappearing, so last year's report can still explain itself.

Sample rows:

pay_component	worker_group	ledger_account	valid_from	valid_to
OT_150	Plant hourly	5100 · Factory labor	2024-01-01	open
OT_150	Office salaried	6000 · Operating salary	2024-01-01	open
OT_150	Field billable	5400 · Project cost	2025-07-01	open
BONUS_RET	All	none	2026-03-15	open

The fourth row is the point. The retention bonus exists, has been paid since March, and routes nowhere. In the source system that fact is an absence, which no report can show. As a row, it is a finding.

The check runs each period and asks one question: is there money on any pay code with no current mapping row?

-- pay with no route: amounts on components lacking a current mapping
SELECT f.pay_component,
       SUM(f.amount)            AS unrouted_amount,
       COUNT(DISTINCT f.worker_key) AS workers_affected
FROM fct_payroll_result_line f
JOIN dim_date d ON f.accounting_date_key = d.date_key
LEFT JOIN map_pay_component_account m
       ON  f.pay_component = m.pay_component
       AND f.worker_group  = m.worker_group
       AND d.full_date BETWEEN m.valid_from AND COALESCE(m.valid_to, DATE '9999-12-31')
WHERE d.fiscal_period = '2026-05'
  AND m.pay_component IS NULL
GROUP BY 1
ORDER BY 2 DESC

A left join keeps every pay line whether or not a mapping matches, and the filter keeps only the ones where nothing matched. Empty result, all money routed. Any rows at all, and each is a named amount, a named code, and a count of people affected, which is a work item instead of a surprise in the audit.

Use case

Problem

The rule that routes pay into the books lives only in system configuration. New pay codes post to defaults unnoticed, rule changes break reports silently, and no two people state the rule the same way.

What we build

The mapping as a dated table in the warehouse, one row per route, kept when it changes rather than overwritten. A period check that names any money moving on a code with no route.

What you get

A routing rule anyone can read, history that explains old reports, and unmapped pay surfaced as a finding with an amount and a headcount, before the auditor finds it.

Want the routing rule on paper instead of in someone's head?

We extract the mapping, land it dated, and wire the check that finds unrouted pay each period.

Talk to us

Terms on this page

pay code: One kind of pay in payroll: base salary, overtime, a bonus. Also called a pay component.
ledger account: The bucket a cost is recorded in within the books.
routing rule: The configuration deciding which account each pay code posts to, by worker group, company, or location.
worker group: A grouping of employees that shares treatment: plant hourly, office salaried, field billable.
default account: Where money posts when no specific route exists. Balances the books and hides the cost.
dated row: A mapping row carrying the dates it was true, so old reports can still explain themselves.
left join: A join keeping every row from the first table whether or not the second matches.
finding: A problem surfaced as a named row with an amount, rather than an absence nobody can see.