PAYSQL/i — Payroll / HR Gross-to-Net

PAYSQL/i is a payroll / HR gross-to-net application: it computes each employee's gross pay, applies pretax and posttax deductions, withholds federal tax through a graduated annual bracket table, arrives at net pay, posts the pay run to a general-ledger distribution, and accrues year-to-date totals. Unlike a classic RPG application, PAYSQL/i is pure SQL PL — the entire domain (schema, functions, procedures, triggers) lives in DB2 for i and is exercised from STRSQL, a batch CALL driver, or a thin SQLRPGLE caller that issues EXEC SQL CALL. There is no 5250 subfile screen; the “online” surface is the callable procedure interface itself. This manual is the reference for the operator who runs the payroll cycle and for the developer who maintains the routines. It is grounded entirely in the committed source (sqlpl-app-pay/src/schema.sql, routines.sql, and the test/pay_battle.mjs, pay_rpg.mjs and pay_volume_sim.mjs drivers).

A. Overview & Architecture ↑ top

A.1 What it does

PAYSQL/i runs a biweekly payroll from employee master through to a posted general-ledger distribution:

• Gross computation — salaried employees earn ANNSAL/26 per biweekly period (prorated for a mid-period termination); hourly employees earn HRATE×80 standard biweekly hours.
• Deductions — each employee's elected deduction codes are applied, split into pretax (reduces taxable wages) and posttax, each either a flat amount or a percent of gross.
• Federal withholding — taxable wages are annualized (×26), reduced by a flat per-exemption allowance, run through a graduated annual bracket table (tax = base + rate × excess), then de-annualized (÷26) back to a per-period withholding.
• Net pay — gross − pretax − fedtax − posttax; a would-be-negative net is vetoed, never posted.
• Posting & accrual — the run is posted to a balanced GL distribution (wage-expense debit, tax / benefits / cash credits), the run header flips to POSTED, and a YTD accumulator is updated automatically by a trigger.

A.2 Pure-SQL-PL architecture: the database is the application

PAYSQL/i has a deliberately different shape from the RPG-orchestrated applications in this estate. There is no RPG business logic and no DDS screen. Every rule — gross, proration, the bracket walk, the deduction cursor, net, posting, GL distribution, YTD accrual — is implemented as DB2 for i SQL PL: compound scalar functions, stored procedures (some nesting CALLs into others), and table triggers. The schema lives in library PAYSQL.

• SQL PL layer (everything). schema.sql creates the tables, the seeded bracket and deduction-code catalogs, and the payslip view; routines.sql creates 3 functions, 5 procedures and 3 triggers that carry all the logic. Integrity and accrual are enforced by triggers, not by the caller.
• Caller layer (reach only). The routines are reachable three ways, all equivalent: interactive STRSQL (CALL PAYSQL.PY_RUN_POST(...)), a batch/JOBQ CALL, and a compiled SQLRPGLE program (CALLPAY) that issues EXEC SQL CALL. The RPG program is a proof of reach — it exists to show the compound procedures are callable from compiled RPG and that OUT parameters and a trigger-raised SIGNAL surface correctly as SQLCODE; it holds no payroll math of its own.

The benefit for operations: the payroll rules are one auditable place (the SQL-PL routines), and the same procedures answer identically from an operator's STRSQL session, a scheduled batch job, and any embedded-SQL caller.

A.3 Component & flow

  CALLERS                     SQL-PL LOGIC (library PAYSQL)          DATA + TRIGGERS
  -------                     ----------------------------          ---------------
  STRSQL   ---CALL--->        PY_RUN_POST(runid)                    PYEMP  (master)
  batch    ---CALL--->          |  loops ACTIVE PYEMP               PYBRK  (brackets)
  SQLRPGLE ---EXEC SQL CALL->    +--> PY_RUN_EMPLOYEE(run,emp)      PYDEDC (ded codes)
  (CALLPAY)                            +--> PY_COMPUTE_GROSS        PYEMPDED (elections)
                                       |      FN_PRORATE/FN_ROUND2       |
                                       |    +--> PY_WITHHOLD             v
                                       |          FN_TAXBRK/FN_ROUND2  PYRUNDT (payslip)
                                       |    INSERT PYRUNDT ------------> TR_PYRUNDT_VETO (net>=0)
                                       |                                 TR_PYRUNDT_ACCR --> PYACCUM (YTD)
                                       +--> (GL pass) INSERT PYGL        (both write PYAUD)
                                       +--> UPDATE PYRUN RSTAT='P'

  ADJUSTMENTS   UPDATE PYSLIPV.NET --> TR_PYSLIPV_UPD (INSTEAD OF) --> PY_ADJUST_NET (net>=0) --> PYRUNDT + PYAUD

A single pay run flows: caller issues CALL PY_RUN_POST('RUN001', ...) → the procedure loops every ACTIVE employee, nesting PY_COMPUTE_GROSS then PY_WITHHOLD and inserting one PYRUNDT payslip row → that INSERT fires the BEFORE veto (net≥0) and the AFTER accrual (YTD into PYACCUM) → a second pass writes the balanced PYGL distribution and flips the run to POSTED.

A.4 Object inventory

The full catalogue is 3 functions + 5 procedures + 3 triggers, over 9 PFs and 1 view, reachable from STRSQL, batch, or the CALLPAY SQLRPGLE program. Sections D and F expand each.

B. Online / Access Surface ↑ top

B.1 How it is invoked (honest: there is no 5250 screen)

Honest statement: PAYSQL/i has no interactive 5250 subfile screen, no DDS display file, and no menu program. It is a pure data-layer application. The operator surface is the callable procedure interface, reached identically three ways — interactive SQL, a batch CALL, and an SQLRPGLE caller. Before invoking anything, the job's library list must include PAYSQL; the tested jobs run with LIBL = QSYS QGPL PAYSQL QTEMP and CURLIB = PAYSQL.

The batch idiom. PY_RUN_POST is the one-call payroll cycle: it takes the run id IN and four counters OUT (COMPUTED, SKIPPED, VETOED, POSTED). A scheduled submission is therefore a bare procedure call against a run id whose header row already exists in PYRUN:

-- interactive / STRSQL
CALL PAYSQL.PY_RUN_POST('RUN001', ?, ?, ?, ?);

-- batch: wrap the CALL in a program/driver and SBMJOB it
SBMJOB CMD(CALL PGM(PAYSQL/CALLPAY)) JOB(PAYRUN)

The SQLRPGLE caller (CALLPAY)

CALLPAY is the reach-proof driver (test/pay_rpg.mjs). It declares packed host variables, then in sequence: (1) EXEC SQL CALL PY_COMPUTE_GROSS and PY_WITHHOLD to round-trip IN/OUT values through the nested compound procedures; (2) EXEC SQL CALL PY_RUN_POST and reads back the four OUT counters; (3) EXEC SQL CALL PY_ADJUST_NET with a negative amount to confirm the trigger/procedure SIGNAL reaches RPG as a negative SQLCODE (−438, the unhandled-SIGNAL convention), then a valid adjustment to confirm normal success. Each step DSPLYs value/%char(sqlcode).

B.2 Controls & audit

PAYSQL/i does not model a four-eyes maker–checker workflow: a pay run posts in one call and a sanctioned net adjustment applies immediately. The control posture is data-layer guards + automatic audit + idempotency:

• Net-veto (defense in depth). TR_PYRUNDT_VETO (BEFORE INSERT on PYRUNDT) SIGNALs SQLSTATE 75014 on any negative net — even an ad-hoc INSERT that tries to bypass PY_WITHHOLD is rejected. A vetoed employee is caught by PY_RUN_EMPLOYEE's UNDO handler (counted VETOED), and the run continues.
• Adjustment gating via the view. TR_PYSLIPV_UPD (INSTEAD OF UPDATE on PYSLIPV) reroutes a net change to PY_ADJUST_NET, which re-validates net≥0 itself (75013) before touching the base table. A direct UPDATE of a payslip net cannot bypass the guard.
• Trigger-driven accrual. The YTD accumulator (PYACCUM) is written only by TR_PYRUNDT_ACCR (AFTER INSERT), never by the posting procedure — the books cannot drift from the payslips because the accrual is a side effect of the payslip landing.
• Audit log (PYAUD). Every accrual (OP='ACCRUE', one per posted employee) and every net adjustment (OP='ADJUST') is written automatically by the triggers/procedure, giving an after-the-fact trail.
• Conservative SIGNALs over silent defaults. Functions refuse to guess: an unknown filing status (FN_TAXBRK, 75010), a zero-length period (FN_PRORATE, 75011) and an unknown pay type (PY_COMPUTE_GROSS, 75012) all SIGNAL rather than tax at zero or divide by zero.

In sum, the control model is guarded data + trigger accrual + audit, all enforced in the SQL-PL layer, rather than a segregation-of-duties approval flow.

C. Batch / Cycle Procedures ↑ top

PAYSQL/i's “cycle” is a single per-period event: for each pay period, seed a PYRUN header, then call PY_RUN_POST once. That one call computes every active employee's payslip, posts the GL, accrues YTD, and closes the run. There is no separate daily / weekly / monthly split — the natural cadence is one run per pay period (the tests use consecutive biweekly periods). The section below documents the full routine set the cycle drives.

C.1 Full procedure / function / trigger set

C.2 The pay-run cycle in detail

Gross — PY_COMPUTE_GROSS

Reads the employee and the run's period. Salaried (PTYPE='S'): gross = ROUND(ANNSAL/26.0); if the employee terminated inside the period (TERMDT within [PSTART,PEND]) it prorates by a simple 14-day model — WORKEDDAYS = day-of-month(TERMDT) − day-of-month(PSTART) + 1 over 14, through FN_PRORATE. Hourly (PTYPE='H'): gross = ROUND(HRATE×80), no proration. An unknown pay type SIGNALs 75012.

Withholding & net — PY_WITHHOLD

Cursor-walks the employee's elected deductions (PYEMPDED joined to PYDEDC), summing pretax (DKIND='B') and posttax ('A') totals, each a flat amount (DBASIS='F') or a percent of gross ('P'). Then: taxable = gross − pretax (floored at 0); annualize annual = taxable×26 − EXEMPT×2000 (floored at 0); look up annualtax = FN_TAXBRK(FSTAT, annual); de-annualize fedtax = ROUND(annualtax/26.0); finally net = ROUND(gross − pretax − fedtax − posttax).

Worked example — E00001 (ann 78000, single, 1 exemption; 401K 5% + MED 75 pretax + UNIO 20 posttax):
  gross    = 78000.00 / 26              = 3000.00
  preded   = round(3000*0.05) + 75.00   = 150.00 + 75.00 = 225.00
  taxable  = 3000.00 - 225.00           = 2775.00
  annual   = 2775*26 - 1*2000           = 72150.00 - 2000 = 70150.00
  bracket S: floor 44725 rate .22 base 5147.00
    annualtax = 5147.00 + .22*(70150-44725) = 10740.50
  fedtax   = round(10740.50 / 26)       = 413.10
  net      = 3000 - 225 - 413.10 - 20   = 2341.90

Per-employee & posting — PY_RUN_EMPLOYEE / PY_RUN_POST

PY_RUN_EMPLOYEE is BEGIN ATOMIC: if a payslip already exists for (RUNID,EMPID) it returns SKIPPED; otherwise it nests the two computes and INSERTs the PYRUNDT row (returning COMPUTED). An UNDO handler on SQLEXCEPTION catches a trigger veto, unwinds that one INSERT, and returns VETOED — the run is not aborted. PY_RUN_POST loops every ESTAT='A' employee through it, tallying the four counters; then, in a second pass over the just-computed (DSTAT='C') rows, writes the four-line GL distribution per employee, flips each row to DSTAT='P', and finally sets PYRUN.RSTAT='P'. If the run is already posted, it returns immediately with all counters 0.

Expected OUT (battle RUN001: 5 salaried + 20 hourly, 1 net-negative vetoed):
  COMPUTED = 24   SKIPPED = 0   VETOED = 1   POSTED = 24

C.3 Ordering & idempotency

• Header before run. A PYRUN row (status 'O') must exist for the run id before PY_RUN_POST is called — the procedure reads the period from it and, on completion, flips it to 'P'.
• Idempotent at both layers. Re-calling PY_RUN_POST on a posted run is a pure no-op (early return); within a run, an already-present payslip is SKIPPED and GL rows are written only for DSTAT='C' rows, which are flipped to 'P' in the same pass. A re-run posts nothing new and does not double the YTD accumulator.
• Compute order is fixed by the driver: gross before withhold (withhold needs the gross); all employees computed before the GL pass; run header flipped last.
• Cross-run accumulation. Distinct runs write distinct primary-keyed PYRUNDT(RUNID,EMPID) rows; the YTD accumulator sums across runs in the same year via the AFTER-insert trigger's UPSERT.

D. Data Files (data dictionary) ↑ top

All files are in library PAYSQL, grounded in schema.sql. Dates are stored as INT in YYYYMMDD form (or YYYY for the accumulator year); money is DECIMAL; bracket/deduction rates are DECIMAL proportions or percents.

PYEMP — Employee master (PK EMPID)

PYBRK — Graduated tax brackets (PK FSTAT, BFLOOR)

Tax on income I in [BFLOOR, next BFLOOR) is BBASE + BRATE×(I−BFLOOR). Seeded (illustrative, not a real-year IRS table): S floors 0/11000/44725/95375/182100 at 10/12/22/24/32%; M floors 0/22000/89450/190750/364200 at the same rates.

PYDEDC — Deduction-code catalog (PK DEDCODE)

Seeded: 401K (B/P 5%), MED (B/F $75), UNIO (A/F $20), GARN (A/F $500).

PYEMPDED — Deduction elections (PK EMPID, DEDCODE)

The withhold cursor JOINs this to PYDEDC; an election whose code no longer exists in PYDEDC simply drops out of the join (no run abort).

PYRUN — Pay-run header (PK RUNID)

PYRUNDT — Pay-run detail / payslip (PK RUNID, EMPID)

PYACCUM — YTD accumulator (PK EMPID, PYEAR)

Maintained only by TR_PYRUNDT_ACCR (AFTER INSERT on PYRUNDT), never written directly by the posting procedure — the accrual is trigger-driven, not application bookkeeping.

PYGL — GL distribution (IDENTITY GSEQ)

PYAUD — Trigger audit log (IDENTITY ASEQ)

PYSLIPV — Payslip view (over PYRUNDT)

Exposes RUNID, EMPID, GROSS, FEDTAX, NET, DSTAT. Updatable only through TR_PYSLIPV_UPD (INSTEAD OF UPDATE): a net change reroutes to PY_ADJUST_NET (which re-validates net≥0); other columns are inert (the “view is otherwise inert” contract).

Relationships

• PYRUNDT.EMPID → PYEMP.EMPID; PYRUNDT.RUNID → PYRUN.RUNID (payslip references employee + run).
• PYEMPDED (EMPID,DEDCODE) links employees to PYDEDC deduction codes.
• PYEMP.FSTAT selects the PYBRK bracket set used at withholding.
• PYGL and PYAUD are per-run/employee logs; PYACCUM is a per-employee/year rollup of PYRUNDT.
• PYSLIPV is a view over PYRUNDT (no separate storage).

E. Operations Runbook ↑ top

E.1 Running a pay run

1. Confirm the job's library list includes PAYSQL (LIBL = QSYS QGPL PAYSQL QTEMP, CURLIB = PAYSQL).
2. Confirm the employee master, deduction codes/elections and brackets are current (a new hire needs a PYEMP row with ESTAT='A' before it is picked up).
3. Create the pay-run header for the period (status open): INSERT INTO PAYSQL/PYRUN VALUES ('RUN001',<PSTART>,<PEND>,<PAYDT>,'O').
4. Run the payroll cycle in one call and capture the four OUT counters:

-- STRSQL (or wrap in a batch driver / CALLPAY and SBMJOB it)
CALL PAYSQL.PY_RUN_POST('RUN001', ?, ?, ?, ?);
--   OUT: COMPUTED  SKIPPED  VETOED  POSTED

Post-checks after the run:

• COMPUTED = the number of active employees whose payslip was created this run; SKIPPED = already-present payslips (re-run); VETOED = employees whose net would have been negative (not posted); POSTED = GL-distributed rows (= COMPUTED on a first run).
• The run header flipped: SELECT RSTAT FROM PAYSQL/PYRUN WHERE RUNID='RUN001' = P.
• Each posted employee has exactly 4 GL lines summing to 0.00 (see E.2).
• The YTD accumulator advanced for each posted employee (trigger-driven).
• One PYAUD ACCRUE row per posted employee.

Adjusting a posted payslip. Change net only through the view, which reroutes to the validated procedure:

-- sanctioned: reroutes to PY_ADJUST_NET, re-validates net >= 0, audits
UPDATE PAYSQL/PYSLIPV SET NET = 700.00 WHERE RUNID='RUN001' AND EMPID='E00003';
-- a negative net here is REJECTED (SQLSTATE 75013); the row is left unchanged

E.2 Reconciling the figures

These are the same invariants the battle and volume simulations check against an independent hand-derived (Python) oracle — nothing is read back and asserted against itself.

• Net identity (per employee). NET = GROSS − PREDED − FEDTAX − POSTDED exactly, for every posted row — no penny drift:

SELECT EMPID FROM PAYSQL/PYRUNDT
  WHERE RUNID='RUN001' AND ROUND(GROSS-PREDED-FEDTAX-POSTDED,2) <> NET;
-- expected: zero rows

• GL balance (per employee). Each employee has exactly 4 GL lines that sum to 0.00:

SELECT EMPID FROM PAYSQL/PYGL WHERE RUNID='RUN001'
  GROUP BY EMPID HAVING COUNT(*) <> 4 OR ROUND(SUM(GAMT),2) <> 0;
-- expected: zero rows

• GL wage-expense tie-out. SUM(GAMT) on account 700000 = total gross posted.
• YTD tie-out. PYACCUM(EMPID,PYEAR) equals the sum of that employee's posted PYRUNDT gross/fedtax/net for the year (e.g. after two runs E00001 shows 6000 / 826.20 / 4683.80).
• Bracket sanity. FN_TAXBRK at a floor returns the base exactly (e.g. FN_TAXBRK('S',11000.00)=1100.00); an unknown filing status SIGNALs rather than returning 0.

E.3 Failure & re-run rules

F. Developer Reference ↑ top

The complete SQL-PL surface, from schema.sql and routines.sql. All objects are in library PAYSQL. Signatures are given as declared; IN parameters precede OUT.

F.1 Tables & the view

F.2 Functions (3)

FN_ROUND2 (V DECIMAL(15,6)) RETURNS DECIMAL(11,2)

Compound scalar. Rounds to the cent HALF-UP (away from zero; payroll amounts are ≥0 so half-up and half-away-from-zero agree): scales ×100, takes FLOOR, adds 1 if the fraction ≥0.5, divides by 100.0. The divisor literal carries an explicit decimal point deliberately — an integer-looking operand truncates like C division (SQLPL-PLAT-01 workaround).

FN_TAXBRK (FSTATIN CHAR(1), TAXABLE DECIMAL(11,2)) RETURNS DECIMAL(11,2)

Compound scalar. Cursor-walks PYBRK for the filing status ordered by floor, keeping the highest floor ≤ income (income floored at 0), then returns FN_ROUND2(BESTBS + BESTRT×(INCOME−BESTFLR)). If no bracket matches (unknown filing status) it SIGNALs SQLSTATE 75010 rather than taxing at zero.

FN_PRORATE (DAYSWORKED INT, DAYSINPERIOD INT) RETURNS DECIMAL(9,6)

Compound scalar. Returns DAYSWORKED/DAYSINPERIOD clamped to [0,1]. A non-positive period SIGNALs 75011. Multiplies by 1.0 to force decimal (not integer-truncated) division (SQLPL-PLAT-01 workaround).

F.3 Procedures (5)

PY_COMPUTE_GROSS (IN RUNIDIN CHAR(6), IN EMPIDIN CHAR(6); OUT GROSSOUT DECIMAL(11,2))

Reads PYEMP + the PYRUN period. Salaried: FN_ROUND2(ANNSAL/26.0), prorated by FN_PRORATE(WORKEDDAYS,14) when TERMDT falls in [PSTART,PEND] (WORKEDDAYS = MOD(TERMDT,100)−MOD(PSTART,100)+1). Hourly: FN_ROUND2(HRATE×80). Unknown PTYPE → 75012. Divisor 26.0 (not 26) is a SQLPL-PLAT-01 workaround.

PY_WITHHOLD (IN EMPIDIN CHAR(6), IN GROSSIN DECIMAL(11,2); OUT PREDEDOUT, FEDTAXOUT, POSTDEDOUT, NETOUT DECIMAL(11,2))

Cursor over PYEMPDED⨯PYDEDC accumulating pretax (DKIND B) and posttax (A) totals, flat (DBASIS F) or percent-of-gross (P, via GROSSIN×DVAL/100.0). Then TAXABLE=GROSS−PREDED (≥0); ANNUAL=TAXABLE×26−EXEMPT×2000 (≥0); ANNUALTAX=FN_TAXBRK(FSTAT,ANNUAL); FEDTAX=FN_ROUND2(ANNUALTAX/26.0); NET=FN_ROUND2(GROSS−PREDED−FEDTAX−POSTDED). A NOT-FOUND CONTINUE handler ends the cursor; an orphaned election (code absent from PYDEDC) simply drops from the join.

PY_RUN_EMPLOYEE (IN RUNIDIN CHAR(6), IN EMPIDIN CHAR(6); OUT RCOUT VARCHAR(20))

BEGIN ATOMIC. If a PYRUNDT row exists for (run,emp) → RCOUT='SKIPPED'; else nests PY_COMPUTE_GROSS then PY_WITHHOLD, computes TAXWAGE=GROSS−PREDED, INSERTs the payslip (DSTAT 'C') → RCOUT='COMPUTED'. An UNDO HANDLER FOR SQLEXCEPTION sets RCOUT='VETOED' and unwinds the INSERT (so a trigger veto doesn't abort the caller's loop).

PY_RUN_POST (IN RUNIDIN CHAR(6); OUT COMPUTED, SKIPPED, VETOED, POSTED INT)

Early-returns (all counters 0) if the run is already RSTAT='P'. Cursor C1 over ESTAT='A' employees calls PY_RUN_EMPLOYEE and tallies by return code. Cursor C2 over the just-computed (DSTAT='C') rows writes the 4-line GL distribution per employee (+GROSS/700000, −FEDTAX/210000, −(POSTDED+PREDED)/220000, −NET/100000), flips each row to 'P', increments POSTED; finally UPDATE PYRUN SET RSTAT='P'.

PY_ADJUST_NET (IN RUNIDIN CHAR(6), IN EMPIDIN CHAR(6), IN NEWNET DECIMAL(11,2))

The only sanctioned path to change a posted net (target of TR_PYSLIPV_UPD). Re-validates NEWNET≥0 (else 75013 — defense in depth, never trusting the caller), UPDATEs PYRUNDT.NET, and writes an ADJUST row to PYAUD.

F.4 Triggers (3)

TR_PYRUNDT_VETO — BEFORE INSERT ON PYRUNDT, FOR EACH ROW, BEGIN ATOMIC

If N.NET < 0 → SIGNAL SQLSTATE '75014'. Rejects any negative-net payslip, including an ad-hoc INSERT that tries to bypass PY_WITHHOLD.

TR_PYRUNDT_ACCR — AFTER INSERT ON PYRUNDT, FOR EACH ROW, BEGIN ATOMIC

Derives the year from the run's PEND (YR = PEND/10000), then UPSERTs PYACCUM(EMPID,YR) (SELECT COUNT INTO → INSERT or additive UPDATE, since MERGE is not assumed available), and writes an ACCRUE row to PYAUD. This is the sole writer of PYACCUM.

TR_PYSLIPV_UPD — INSTEAD OF UPDATE ON PYSLIPV, FOR EACH ROW, BEGIN ATOMIC

Reroutes the update to CALL PY_ADJUST_NET(O.RUNID, O.EMPID, N.NET), so no direct UPDATE of a payslip net can bypass the net≥0 re-validation. Only NET is rerouted; other view columns are inert.

F.5 SQL-PL patterns used

PAYSQL/i is a compact tour of the compound-SQL-PL idioms; a maintainer sees these repeatedly:

• Compound scalar function — CREATE FUNCTION ... LANGUAGE SQL BEGIN ... RETURN ... with local DECLAREs, IF/WHILE, and a cursor (FN_TAXBRK, FN_ROUND2, FN_PRORATE).
• Cursor walk with a NOT FOUND CONTINUE handler — DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE=1; then OPEN / FETCH / WHILE DONE=0 DO ... FETCH ... END WHILE / CLOSE (the bracket walk and the deduction accumulation; PY_RUN_POST's two cursors, resetting DONE=0 between them).
• Nested CALL between procedures — PY_RUN_EMPLOYEE nests PY_COMPUTE_GROSS and PY_WITHHOLD; PY_RUN_POST nests PY_RUN_EMPLOYEE. OUT parameters carry values back up the chain.
• ATOMIC block + UNDO handler — BEGIN ATOMIC ... DECLARE UNDO HANDLER FOR SQLEXCEPTION so a trigger SIGNAL rolls back one employee's INSERT and the loop continues (PY_RUN_EMPLOYEE).
• SIGNAL / conditional SQLSTATE — SIGNAL SQLSTATE '750xx' SET MESSAGE_TEXT=... for conservative refusals (unknown status/type, zero period, negative net).
• UPSERT without MERGE — SELECT COUNT(*) INTO then IF INSERT-or-additive-UPDATE (TR_PYRUNDT_ACCR's YTD accrual).
• INSTEAD OF UPDATE view trigger — rerouting a view update to a validated procedure (TR_PYSLIPV_UPD → PY_ADJUST_NET).
• Embedded-SQL reach (SQLRPGLE) — EXEC SQL CALL proc(:in, :out) with packed host variables; check SQLCODE (0 success, -438 unhandled SIGNAL) after each call (CALLPAY).
• Decimal-affinity discipline — division divisors carry an explicit decimal point (26.0, 100.0, ×1.0) so integer-looking DECIMAL operands don't truncate (SQLPL-PLAT-01 workaround, documented in source).

F.6 Application SQLSTATE table

When one of these SIGNALs is unhandled by an SQLRPGLE caller, it surfaces as SQLCODE -438 (the platform convention proven in CALLPAY's PY_ADJUST_NET(-5.00) step).

G. Glossary ↑ top

Annualize / de-annualize

Scaling a biweekly figure to a yearly one (×26) to look up graduated tax, then back (÷26) to a per-period withholding — PY_WITHHOLD's core move.

Bracket (graduated tax bracket)

A band of annual income (PYBRK) with a marginal rate and a cumulative base tax at its floor; tax = base + rate×(income−floor).

DSTAT / RSTAT

Payslip status (C computed, P posted) / run status (O open, P posted). The GL pass flips DSTAT and then RSTAT.

Deduction (pretax / posttax)

An amount withheld from pay. Pretax (DKIND B) reduces taxable wages before tax; posttax (A) reduces net after tax. Each is flat (DBASIS F) or a percent of gross (P).

Gross-to-net

The full payroll computation from gross pay through deductions and withholding to net pay.

Idempotent

Safe to run again with the same result. PY_RUN_POST on a posted run is a no-op; already-present payslips are skipped; YTD and GL are not doubled.

INSTEAD OF trigger

A view trigger that replaces the default DML with custom logic. TR_PYSLIPV_UPD reroutes a net update to the validated PY_ADJUST_NET.

Proration

Scaling a salaried gross for a partial period (e.g. a mid-period termination) by FN_PRORATE, clamped to [0,1].

SIGNAL / SQLSTATE

The SQL-PL way to raise an application error with a state code (here 750xx) and message. An unhandled SIGNAL reaches an RPG caller as SQLCODE -438.

SQL PL

DB2's procedural SQL language for functions, procedures and triggers. In PAYSQL/i it holds the entire application — there is no RPG business logic.

SQLRPGLE / EXEC SQL CALL

RPG with embedded SQL. CALLPAY uses EXEC SQL CALL only to prove the SQL-PL procedures are reachable from compiled RPG; it holds no payroll math.

UPSERT

Insert-or-update in one logical step. TR_PYRUNDT_ACCR does it via SELECT COUNT INTO / IF, without MERGE, to maintain the YTD accumulator.

YTD accumulator (PYACCUM)

Per-employee, per-year running totals of gross/fedtax/net, maintained solely by the AFTER-insert trigger so the books cannot drift from the payslips.