Payroll Cost & GL Reconciliation

The month-end question: does what payroll paid match what the ledger booked? Mapping errors hide until close. This report finds them the day after commit, per cost center, in one query across both systems.

RuleReconcile every period, per cost center, on the mapped dimension. A zero-difference row is the deliverable.Neverwait for month-end close to find a mapping gap. Every new pay component gets a mapping before its first run.

◆ Why payroll and the ledger disagree— three causes, and where each one shows up.

Cause	What happens	Where it shows
Mapping gaps	Payroll accounting maps each pay component to account, cost center, and spend category. A new earning code without a mapping posts nowhere, or to a suspense account. The most common failure, and it hides until close.	One cost center's difference equals the unmapped component's total, exactly.
Cost center drift	Workday and the ERP maintain cost centers separately; codes drift. Costs post, but to a code the other system reads differently. See Cost Center & Company.	Two rows off by the same amount in opposite directions.
Timing	Retro pay lands cash in this period for labor in prior ones, and accrual entries book labor the ledger has not paid. The paid-in versus earned-for keys from Payroll Result separate the two views.	Differences that reverse next period.

◆ The owned model, and the SQL— Workday lines on one side, ERP journal lines on the other, joined on the mapped cost center.

This is the query the mapped dimension was built for: fct_payroll_result summed to fully loaded cost per cost center, against the ERP's payroll-sourced journal lines, joined through dim_cost_center's code map. The ERP side reads the same warehouse's SAP model or its Oracle equivalent:

-- one period, both systems, one mapped dimension; the diff column is the report
WITH wd AS (
  SELECT cc.erp_code,
         SUM(f.amount) AS wd_amount
  FROM fct_payroll_result f
  JOIN dim_pay_component pc ON f.pay_component_key = pc.pay_component_key
  JOIN dim_cost_center  cc ON f.cost_center_key   = cc.cost_center_key
  WHERE f.paid_in_period_key = 202605
    AND pc.in_fully_loaded
  GROUP BY 1
),
gl AS (
  SELECT j.cost_center AS erp_code,
         SUM(j.amount) AS gl_amount
  FROM fct_gl_journal_line j
  WHERE j.fiscal_period = 202605
    AND j.source = 'Payroll'
  GROUP BY 1
)
SELECT COALESCE(wd.erp_code, gl.erp_code) AS cost_center,
       wd.wd_amount,
       gl.gl_amount,
       COALESCE(wd.wd_amount, 0) - COALESCE(gl.gl_amount, 0) AS difference
FROM wd
FULL OUTER JOIN gl ON wd.erp_code = gl.erp_code
ORDER BY ABS(COALESCE(wd.wd_amount,0) - COALESCE(gl.gl_amount,0)) DESC

Sample output, May, the three cost centers from the mapped dimension's sample, including the drift case CC_MKTG that maps to SAP 3200:

cost_center	wd_amount	gl_amount	difference
3200	486,200	473,800	12,400
4100	1,214,500	1,214,500	0
4200	391,700	391,700	0

Two zeros and one finding: 3200 is short exactly 12,400 on the ledger side, the signature of one unmapped component, and drilling the Workday side by component names it in one query. Because the join runs on the mapped dimension, the drifted code pair reconciles instead of appearing as two mismatched rows. The FULL OUTER JOIN matters: a cost center present in only one system is itself a finding. Sample values are illustrative, never client data.

◆ Running it as a control— after every commit, not at close, with the two views kept separate.

Practice	Why
Run per commit	The day after each committed run, not month-end. A mapping gap found in week one is a config fix; found at close it is a restatement conversation.
Two views, labeled	Cash view on paid-in period matches the ledger's posted journals. Labor-cost view on earned-for period feeds cost-per-head and plan-versus-actual. Same fact, two period keys, never blended.
New-component gate	The rulebar's Never as a process: a pay component with no accounting mapping fails the pre-commit checklist, so the report's job stays boring.

Want payroll and the ledger to agree every period?

We build both sides in one warehouse, the mapped dimension between them, and the per-commit control, and you own every line.

Talk to us

Terms on this page

payroll accounting: The mapping from pay components to account, cost center, and spend category. Drives the journal.
payroll journal: The summarized posting of a committed run into the ledger.
suspense: Where unmapped amounts land, when they land anywhere.
mapped dimension: dim_cost_center carrying both systems' codes. The join that makes reconciliation one query.
full outer join: Keeps rows present in only one system. Those rows are findings.
cash view: Cost by paid-in period. Matches posted journals.
labor-cost view: Cost by earned-for period. Feeds cost-per-head and plan-versus-actual.
per-commit control: The reconciliation run after every committed payroll, so close holds no surprises.