---
title: Wage subsidy worksheet and calculation
summary: Builds the pay-period and timecard worksheet behind a wage subsidy and converts reported hours into the subsidy amount paid to the employer.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-07-30
library: SAIF.Business.Claim.EAIP
reference_type: package
operation_id: OP-EAIP-03
trigger: Caller adds pay-period summaries and timecards under a wage subsidy, then calls UpdateFromPayPeriods() and Save()
---

# Wage subsidy worksheet and calculation

A wage subsidy is not typed in — it is computed. The specialist records the employer's pay periods, optionally down to daily timecards with in/out times, and the library rolls those up: timecards produce hours and gross wages, pay periods aggregate into summaries by date range, and the summary totals drive the subsidy amount through a configured reimbursement rate that varies by effective date. This operation covers that worksheet: `clsPayPeriodTimeCard`, `clsPayPeriod` and its `clsPayPeriodSummary` inner class, the date-range helpers in `mdlUtility`, and the wage-subsidy-specific arithmetic and rules in `clsWageSubsidy`. The generic expense lifecycle these objects sit inside — status transitions, approval, amendment — is documented in [Expense lifecycle and payment approval](expense-lifecycle.md).

| Field | Value |
| --- | --- |
| **Operation ID** | OP-EAIP-03 |
| **Trigger** | Caller invokes `clsWageSubsidy.NewWageSubsidy(dpi, period)`, adds `clsPayPeriod.clsPayPeriodSummary` and `clsPayPeriod` rows, optionally generates or loads timecards, calls `UpdatePeriodFromTimeCards()` and `UpdateFromPayPeriods()`, then calls `Save()` |
| **Inputs** | Owning `clsPeriod` and wage-subsidy expense; pay-period from/thru dates; worker wage amount, potential and actual unit quantities and unit code; regular/overtime/other pay rates; per-day in and out times or reported hours and minutes; the `claims.eaip.wagesubsidyrates` configuration setting |
| **Outputs** | Persisted `EAIP_PAY_PERIOD` and `EAIP_PERIOD_TIMECARD` rows; computed calculated hours, pay amounts, and gross wages; wage-subsidy effective and expiration dates, day count, gross wages, and paid amount; duplicate/overlap and 66-day warnings |
| **Primary components** | `clsPayPeriodTimeCard` (`CalculatedHours`, `Create`, inline insert/update/delete, `Rule_ValidDate`, `Rule_ValidTotalHours`, `Rule_RequiredTimesOne`/`Two`), `clsPayPeriodTimecardCollection` (`TotalHours`, `IncludedDaysCount`, `Rule_ValidMaxDays`, `GetTimeCards`, `GenerateTimeCards`), `clsPayPeriod` (`AveragePayRate`, `GrossWageAmount`, `UpdatePeriodFromTimeCards`, `GetPayPeriodSummaries`), `clsPayPeriod.clsPayPeriodSummary`, `clsWageSubsidy` (`UpdateFromPayPeriods`, `WageSubsidyCalculationFactor`), `mdlUtility` |
| **Entry point** | `Business\SAIF.Business.Claim.EAIP\Period\clsWageSubsidy.vb` |

## Steps

1. **Create the wage subsidy.** `NewWageSubsidy(inDPI, inPeriod)` creates the expense through the shared base (sequence-assigned identifier, `New` status, create stamp) and then forces the expense type code to the wage constant, so a wage subsidy can never be typed as a purchase category ([SQL-007](../sql/SQL-007.md), [EAIP_EXPENSE_SEQ](../database-tables.md#eaip_expense_seq)).
2. **Add a pay-period summary.** The worksheet is organized as summaries by date range: each `clsPayPeriodSummary` owns one or more `clsPayPeriod` rows sharing a from/thru date range. Setting the summary's from date or thru date propagates that value down to every child pay period, so the range is edited once and applied consistently.
3. **Create a pay period.** `clsPayPeriod.Create()` takes the next value of `eaip_pay_period_seq` and records the create stamp ([SQL-002](../sql/SQL-002.md), [EAIP_PAY_PERIOD_SEQ](../database-tables.md#eaip_pay_period_seq)). The caller sets the effective and expiration dates, the worker's wage amount, the potential and actual unit quantities, the unit code, the day count, and the three pay rates.
4. **Compute the averaged amounts.** When no timecards contribute hours (the timecard collection's total hours is zero), the pay period recomputes its own regular, overtime, and other pay amounts from rate and quantity. Two derived values drive the roll-up:
   - `AveragePayRate` = worker wage amount ÷ potential unit quantity, rounded to four decimals;
   - `GrossWageAmount` = average pay rate × actual unit quantity, capped at the worker wage amount so a data-entry error cannot inflate the subsidy above the wages actually paid;
   - `TotalGrossWageAmount` = total pay amount + gross wage amount.
5. **Validate the pay period.** `clsPayPeriod` registers `Rule_ValidEffDate` and `Rule_ValidExpDate` (the dates must fall inside the owning service period), `Rule_ValidDates` (end on or after start), `Rule_ValidUnitCode` (a unit type is required when averaging is used), `Rule_ValidDays` (the day count may not exceed the number of days in the date range), and `Rule_ValidActualUnits` (actual units may not exceed potential units). Errors are labeled "Worksheet" so the user sees which panel failed.
6. **Generate the timecard grid.** `clsPayPeriodTimecardCollection.GenerateTimeCards(dpi, payPeriodID)` reads the pay period, computes the day span between its effective and expiration dates, and creates one unsaved timecard per calendar day, seeding each with the pay period's regular, overtime, and other pay rates. Existing timecards are read instead by `GetTimeCards`, which appends a period filter and a date ordering to the shared select-from clause and joins `EAIP_PERIOD_TIMECARD` to `EAIP_PAY_PERIOD` so each timecard also carries its pay period's effective and expiration dates ([EAIP_PERIOD_TIMECARD](../database-tables.md#eaip_period_timecard)).
7. **Enter times and compute hours.** `clsPayPeriodTimeCard.CalculatedHours` prefers explicitly reported hours and minutes; only when both are zero does it derive time from the two in/out pairs. Each pair contributes the span between its start and stop, and a stop time earlier than its start time is rolled forward one day by the setter, so an overnight shift computes correctly rather than negative. The two spans are summed, converted to hours plus minutes ÷ 60 rounded to two decimals, and floored at zero.
8. **Validate the timecard.** `Rule_ValidDate` requires the timecard date to fall between the pay period's start and end dates. `Rule_ValidTotalHours` requires regular plus overtime hours to equal the calculated hours and rewrites its own message at evaluation time to show both figures, so the user sees the actual mismatch ([BR-PROC-015](../business-rules.md#br-proc-015-timecard-regular-and-overtime-hours-must-equal-calculated-hours)). `Rule_RequiredTimesOne` and `Rule_RequiredTimesTwo` require both halves of an in/out pair to be present. Two further rules that would require the out time to follow the in time are defined but their registrations are commented out, so that check is instead handled by the roll-forward in the setter.
9. **Roll the timecards up.** The timecard collection considers only days flagged as included, and exposes total hours, total regular/overtime/other hours and pay, total gross wages, and a distinct included-day count. `Rule_ValidMaxDays` fires an error when more than 66 distinct days are included ([BR-PROC-008](../business-rules.md#br-proc-008-wage-subsidy-days-may-not-exceed-66-in-a-service-period)). `clsPayPeriod.UpdatePeriodFromTimeCards()` pushes those totals — hours, pay amounts, and gross wages — back onto the pay period.
10. **Persist the timecards.** `clsPayPeriodTimeCard` is the one class in the library that does not use the Oracle package: its criteria carries fully inline statements. `Create()` reserves an identifier from `EAIP_PERIOD_TIMECARD_SEQ` ([SQL-003](../sql/SQL-003.md), [EAIP_PERIOD_TIMECARD_SEQ](../database-tables.md#eaip_period_timecard_seq)); `BeforeInsert` binds the twenty-six columns of the inline insert ([SQL-011](../sql/SQL-011.md), [EAIP_PERIOD_TIMECARD](../database-tables.md#eaip_period_timecard)); `BeforeUpdate` binds `LastUpdateNum` before incrementing the update info and runs the inline update whose `WHERE` clause compares both the identifier and the update number ([SQL-031](../sql/SQL-031.md)); `BeforeDelete` binds the identifier and the last update number to the inline delete ([SQL-041](../sql/SQL-041.md)). Each `After*` handler checks the driver row count and adds an insert, update, or delete error carrying the DB contention message when it is not one.
11. **Group pay periods into summaries.** `clsPayPeriod.GetPayPeriodSummaries` groups the loaded pay periods by their date range, orders the groups, wraps each into a `clsPayPeriodSummary`, and attaches two collection-level rules built on the date-range helpers in `mdlUtility`: `Rule_NoDuplicateDateRanges` (error — two summaries may not share an identical from/thru pair) and `Rule_NoOverlappingDateRanges` (warning — ranges may not overlap when sorted by begin date). The summary itself adds `Rule_ValidSummaryDays` (warning), two further day-count checks, and overlapping effective/expiration date checks. A summary's `Save`/`SaveChild` delegate down to its child pay periods, `MarkDeleted` removes all of them, and `NewCopy` deep-copies the whole group.
12. **Persist the pay periods.** `BeforeInsert` binds the pay-period parameters to `pk82500_eaip.eaip_pay_period_insert` ([SQL-010](../sql/SQL-010.md), [Details](../procedures/pk82500_eaip.md#eaip_pay_period_insert)); `BeforeUpdate` uses the compare-old/write-new update-number pattern against `pk82500_eaip.eaip_pay_period_update` ([SQL-030](../sql/SQL-030.md), [Details](../procedures/pk82500_eaip.md#eaip_pay_period_update)); `BeforeDelete` binds the identifier and last update number to `pk82500_eaip.eaip_pay_period_delete` ([SQL-040](../sql/SQL-040.md), [Details](../procedures/pk82500_eaip.md#eaip_pay_period_delete)). A single pay period is read with `pk82500_eaip.GetPayPeriodByPayPeriodId` ([SQL-020](../sql/SQL-020.md), [Details](../procedures/pk82500_eaip.md#getpayperiodbypayperiodid)) and the collection with `pk82500_eaip.EAIP_PAY_PERIOD_Select` ([Details](../procedures/pk82500_eaip.md#eaip_pay_period_select)).
13. **Roll the summaries into the subsidy.** `clsWageSubsidy.UpdateFromPayPeriods()` runs only when at least one summary exists. It sets the subsidy's effective date to the minimum summary from date, the expiration date to the maximum summary thru date, the day count to the summed total days, and the gross wages amount to the summed gross wages.
14. **Resolve the reimbursement rate.** The method then takes the earlier of the subsidy's new effective date and the minimum effective date across all wage subsidies on the period, and passes it to `WageSubsidyCalculationFactor`. That shared function deserializes the `claims.eaip.wagesubsidyrates` configuration setting into an array of effective-dated rate records and selects, with a single-match lookup, the record whose effective-to-expiration window contains the supplied date.
15. **Compute the paid amount.** `PaidAmount = Math.Round(GrossWagesAmount × factor, 2)` ([BR-PROC-007](../business-rules.md#br-proc-007-wage-subsidy-pay-equals-gross-wages-times-the-effective-rate-factor)). This is the amount that flows into the expense approval gate, the bill, and eventually the DCBS reimbursement match.
16. **Validate the wage subsidy.** On top of the shared expense rules, `clsWageSubsidy` requires the subsidy's effective date to equal the first pay-period start date and its expiration date to equal the last pay-period end date; requires both dates to fall inside the service period unless the amount to bill has been adjusted to zero; requires end on or after start; requires a non-zero day count before payment ("Wage subsidy requests cannot be paid unless days are included."); requires at least one pay period ("Worksheet is required."); and blocks amendments and corrections coexisting in the same collection. The collection adds `Rule_NoDuplicateDateRanges` and `Rule_NoOverlappingDateRanges` as warnings and `Rule_ValidDaySpanInTwoYears`, the warning that the 66 days must fall within the 24-month service period.
17. **Apply the 66-day payment gate.** `clsWageSubsidyCollection.CanPay()` sums the days of every non-amended subsidy without a denied code and warns "Supervisor approval is required when paying more than 66 days." above 66, in addition to everything the shared collection gate checks ([BR-PROC-008](../business-rules.md#br-proc-008-wage-subsidy-days-may-not-exceed-66-in-a-service-period)).
18. **Persist the wage subsidy and cascade.** `BeforeInsert` calls the base (which writes the `EAIP_EXPENSE` row) and then appends a second request against `pk82500_eaip.eaip_wage_subsidy_insert` binding the period identifier, effective and expiration dates, gross wages, and days ([Details](../procedures/pk82500_eaip.md#eaip_wage_subsidy_insert)). On a successful row count, `AfterInsert` and `AfterUpdate` save the pay-period summaries, which in turn save their pay periods and timecards; a row count other than one adds `Insert Failed - Wage Subsidy` or the DB contention message and skips the cascade. Amendments and corrections deep-copy the whole summary tree onto the clone, so an amended subsidy starts from a full copy of the original worksheet.
19. **Report amended days.** `ReportableDays` returns the subsidy's day count minus the original's day count for an amendment, so the billing document shows the incremental days rather than double-counting the original ones.

## Process flow

The following diagram visualizes this operation's control flow.

```mermaid
flowchart TD
	Start([Specialist opens a wage subsidy]) --> New[NewWageSubsidy: expense created,<br>type forced to wage]
	New --> Sum[Add pay-period summary<br>with from and thru dates]
	Sum --> PP[Add pay periods:<br>eaip_pay_period_seq nextval,<br>wages, units, rates]
	PP --> TC{Timecards used?}
	TC -->|Yes| Gen[GenerateTimeCards creates<br>one row per day seeded with rates]
	Gen --> Times[Enter in/out pairs or<br>reported hours and minutes]
	Times --> Calc[CalculatedHours: overnight rolled<br>forward, spans summed, floored at zero]
	Calc --> Roll[UpdatePeriodFromTimeCards pushes<br>hours and gross wages to pay period]
	TC -->|No| Avg[AveragePayRate = wages / potential units;<br>GrossWageAmount = rate x actual units,<br>capped at wages]
	Roll --> Group
	Avg --> Group[GetPayPeriodSummaries groups by range;<br>duplicate = error, overlap = warning]
	Group --> Update[UpdateFromPayPeriods:<br>min from, max thru, sum days,<br>sum gross wages]
	Update --> Rate[WageSubsidyCalculationFactor reads<br>configured effective-dated rates]
	Rate --> Amt[PaidAmount = gross wages x factor,<br>rounded to 2 decimals]
	Amt --> Valid{Worksheet rules pass?}
	Valid -->|No| Broken([Errors: dates must match worksheet,<br>worksheet required, days required])
	Valid -->|Yes| Days{More than 66 days?}
	Days -->|Yes| Warn[Warning: supervisor approval<br>required over 66 days]
	Days -->|No| Save
	Warn --> Save[Save: expense row then<br>eaip_wage_subsidy row]
	Save --> Rows{RowsAffected = 1?}
	Rows -->|No| Err([Insert/Update error with<br>DB contention message])
	Rows -->|Yes| Cascade[Summaries, pay periods,<br>and timecards saved]
	Cascade --> Done([Worksheet persisted])
```

## Failure and edge-case behavior

- The reimbursement rate lookup uses a single-match selection over the configured rate windows. A date that matches no window or matches two overlapping windows does not produce a validation message: the lookup yields the default rate of zero for no match, and throws for a duplicate match. A misconfigured `claims.eaip.wagesubsidyrates` setting therefore either silently zeroes every subsidy or fails the whole save.*
- The calculation runs only when `UpdateFromPayPeriods()` is called. Editing the worksheet without calling it leaves the subsidy's dates, days, gross wages, and paid amount stale relative to the pay periods behind them.*
- Timecards bypass the Oracle package entirely and rely on inline statements, so their concurrency check is the `update_num` predicate in the statement text rather than a package-returned row count; a stale timecard update simply affects zero rows and surfaces as the DB contention message.
- `GenerateTimeCards` produces one row per calendar day inclusive of both endpoints, including weekends and days the worker did not work; unwanted days must be excluded by clearing the included flag, since only included days contribute to totals.
- Days are validated in two independent places — the timecard collection errors above 66 included days, and the wage-subsidy collection warns above 66 summed days — so a worksheet can pass one check and trip the other.
- Overlapping and duplicate date ranges are warnings at the wage-subsidy collection level but an error at the pay-period summary level, so the same overlap can be blocking or non-blocking depending on which collection validates it.
- The gross-wage cap silently truncates: an actual unit quantity above the potential quantity is caught by rule, but a rate/quantity combination that exceeds the worker wage amount is reduced without a message.*
- A failed expense-row write short-circuits the summary cascade, leaving edited pay periods and timecards dirty and unsaved.

## Related documentation

- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts.

## Sources

- `Business\SAIF.Business.Claim.EAIP\Period\clsWageSubsidy.vb`
- `Business\SAIF.Business.Claim.EAIP\Period\clsPayPeriod.vb`
- `Business\SAIF.Business.Claim.EAIP\Period\clsPayPeriodTimeCard.vb`
- `Business\SAIF.Business.Claim.EAIP\Period\mdlUtility.vb`
- `Business\SAIF.Business.Claim.EAIP\Period\clsExpense.vb`
- `Business\SAIF.Business.Claim.EAIP\Period\clsPeriod.vb`