sp82286_statutoryweeklywage¶

Overview¶

Field	Value
Schema	WCIS_DBA
Type	Standalone procedure (not package-qualified); contains one nested private procedure, `SelectStatutoryWeeklyWage`
Database	WCIS_DBA
Calling libraries	`SAIF.Business.Claim`
Calling classes	`clsStatutoryWeeklyWageCalculation` (live code — not dead)

Purpose¶

Determines the statutory weekly wage for a claim from the injury date, wage amount, pay-period code, and (where applicable) hours-worked-per-day and days-worked-per-week. Validates each input in turn; resolves an injury-date-dependent weeks-per-month conversion factor and a pre-1995 minimum-days-per-week floor; then converts the supplied wage into a weekly amount according to the pay-period code. Every execution path — a validation failure or a successful calculation — returns exactly one row containing the calculated wage and an error message via a SYS_REFCURSOR output parameter; failures are reported as data, not as raised exceptions.

Procedures / Methods¶

sp82286_statutoryweeklywage¶

Field	Value
Returns	`recordset` — `SYS_REFCURSOR` (`IN OUT`), single row: `StatutoryWeeklyWage`, `ErrorMessage`
Called by	`clsStatutoryWeeklyWageCalculation.GetStatutoryWeeklyWageCalculation` (`SAIF.Business.Claim/clsStatutoryWeeklyWageCalculation.vb`, entry point line 112; `BeforeRetrieve` lines 68–86 issues the stored-procedure request `CalculateStatutoryWeeklyWage`, `CommandType.StoredProcedure` line 74; `AfterRetrieve` lines 88–100 reads the result; `RetrieveSQL` returns the procedure name at line 208)

Description: Validates the injury date, wage amount, pay-period code, and the period-specific hours/days inputs; resolves the injury-date-dependent weeks-per-month factor and the pre-1995 minimum-days-per-week floor; converts the supplied wage to a weekly amount according to the pay-period code; and returns the result via the nested SelectStatutoryWeeklyWage procedure.

Parameters¶

Parameter	Direction	Type	Default	Description
`inInjuryDate`	IN	VARCHAR2	—	Date of injury as a string, bound by the caller as `AddParm("inInjuryDate", m_InjuryDateString, 10)` (`clsStatutoryWeeklyWageCalculation.vb:79`, value `CStr(MyCriteria.InjuryDate)` at line 78). Parsed inside the procedure with `to_date(inInjuryDate, 'MM/DD/YYYY')`; drives the validity check and the two effective-date rules (weeks-per-month factor, minimum-days-per-week floor).
`inWages`	IN	NUMBER	—	Wage amount at the time of injury, expressed in the unit implied by `inPayPeriod` (per hour, day, week, month, or year). Bound as `AddParm("inWages", MyCriteria.Wages)` (line 80).
`inPayPeriod`	IN	VARCHAR2	—	Single-character pay-period code: `H` (hourly), `D` (daily), `W` (weekly), `M` (monthly), `A` (annual). Any other value is rejected. Bound as `AddParm("inPayPeriod", MyCriteria.PayPeriod, 1)` (line 81).
`inHoursWorkedPerDay`	IN	NUMBER	—	Hours worked per day. Required (non-zero) only when `inPayPeriod = 'H'`; used as a multiplier on the hourly branch; ignored on all other branches. Bound as `AddParm("inHoursWorkedPerDay", MyCriteria.HoursPerDay)` (line 82).
`inDaysWorkedPerWeek`	IN	NUMBER	—	Days worked per week. Required (non-zero) when `inPayPeriod` is `H` or `D`. Copied into local `v_DaysWorkedPerWeek`, which may be raised to a floor of 3 by the pre-1995 rule before use as a multiplier. Bound as `AddParm("inDaysWorkedPerWeek", MyCriteria.DaysPerWeek)` (line 83).
`recordset`	IN OUT	SYS_REFCURSOR	—	Output cursor, opened exactly once by the nested `SelectStatutoryWeeklyWage` over `dual`, returning a single row with columns `StatutoryWeeklyWage` and `ErrorMessage`. Registered by the caller as `AddCursorParm("recordset", ParameterDirection.Output)` (line 84) and consumed in `AfterRetrieve` via `Reader.GetDecimal("StatutoryWeeklyWage")` (line 94) and `Reader.GetSmartString("ErrorMessage")` (line 95).

Business Rules¶

Rules extracted from this procedure's logic (read directly from the retrieved PL/SQL body):

Rule ID	Rule	Condition	Action
PR-BC-001	An injury date on or before 01-Jan-1900 is not a usable injury date and stops the calculation.	`to_date(inInjuryDate, 'MM/DD/YYYY') <= to_date('01-jan-1900','dd-mon-yyyy')`	Set `v_ErrorMessage := 'Invalid injury date'`, open the result cursor with the initialised wage of `0.00`, and `RETURN` without calculating.
PR-BC-002	Wages at the time of injury must be a positive amount.	`inWages <= 0`	Set `v_ErrorMessage := 'Invalid wages at time of injury'`, return `0.00`, and `RETURN`.
PR-BC-003	The pay period must be one of the five recognised codes: `H`, `D`, `W`, `M`, `A`.	`inPayPeriod` is not `'H'`, `'D'`, `'W'`, `'M'` or `'A'`	Set `v_ErrorMessage := 'Invalid pay period'`, return `0.00`, and `RETURN`.
PR-BC-004	An hourly wage cannot be annualised without a non-zero hours-worked-per-day value.	`inPayPeriod = 'H'` and `inHoursWorkedPerDay = 0`	Set `v_ErrorMessage := 'Invalid hours worked per day for a hourly pay period'`, return `0.00`, and `RETURN`.
PR-BC-005	Hourly and daily wages cannot be converted to a weekly wage without a non-zero days-worked-per-week value.	(`inPayPeriod = 'H'` or `inPayPeriod = 'D'`) and `inDaysWorkedPerWeek = 0`	Set `v_ErrorMessage := 'Invalid days worked per week for a hourly or daily pay period'`, return `0.00`, and `RETURN`.
PR-BC-006	The weeks-per-month conversion factor used for monthly wages depends on the injury date: injuries before 01-Oct-1973 use the historical factor, later injuries use the current factor.	`to_date(inInjuryDate, 'MM/DD/YYYY') < to_date('01-oct-1973','dd-mon-yyyy')`	If true → `v_WeeksPerMonth := 4.3452`. Otherwise → `v_WeeksPerMonth := 4.35`.
PR-BC-007	For hourly or daily workers injured before the 1995 effective date, the days-worked-per-week used in the calculation is floored at three days, even when the worker actually worked fewer days.	(`inPayPeriod = 'H'` or `'D'`) and `to_date(inInjuryDate,'MM/DD/YYYY') < to_date('07-jun-1995','dd-mon-yyyy')` and `inDaysWorkedPerWeek < 3`	Set `v_DaysWorkedPerWeek := 3` (overriding the supplied value) before the wage calculation.
PR-BC-008	The statutory weekly wage is derived from the pay period: hourly = rate × hours/day × days/week; daily = rate × days/week; weekly = the wage as supplied; monthly = wage ÷ weeks-per-month; annual = wage ÷ 52.	`CASE inPayPeriod` over `'H'`, `'D'`, `'W'`, `'M'`, `'A'`	Assign the corresponding expression to `v_StatutoryWeeklyWage`; the `ELSE` arm yields `0.00` (unreachable in practice because PR-BC-003 has already rejected any other code).
PR-BC-009	Every execution path — validation failure or successful calculation — returns exactly one row containing a wage and an error message; failures are reported as data, not as raised exceptions.	Always	Call nested `SelectStatutoryWeeklyWage`, which opens `recordset` for `SELECT v_StatutoryWeeklyWage AS StatutoryWeeklyWage, v_ErrorMessage AS ErrorMessage FROM dual`. On success `v_ErrorMessage` remains the initialised empty string and the wage is populated; on validation failure the wage stays `0.00` and the message is populated. There is no `EXCEPTION` block in the procedure.

Source-comment discrepancies preserved, not silently corrected: - The comment above the 1995 rule reads "If the injury date is before 10/07/1995", but the code compares against 07-jun-1995. The code (07-jun-1995) is authoritative for PR-BC-007; the comment date appears to be a stale or transposed value. - The annual branch computes inWages / 52. A commented-out alternative (inWages / 12) / v_WeeksPerMonth is retained in the source immediately below it. Only the active / 52 form is in effect. - The procedure header comment names the object sp82286_statutoryweeklywagecalculation, while the actual declared and END-terminated name is sp82286_statutoryweeklywage. The declared name is authoritative and matches the value returned by the caller's RetrieveSQL property.

These rules are also documented in business-rules.md, which cross-references back to this page.

Operations¶

Operations and processes implemented in this procedure:

Operation	Description	Trigger
Calculate statutory weekly wage	Validates the injury date, wage amount, pay-period code and the period-specific hours/days inputs; resolves the injury-date-dependent weeks-per-month factor and the pre-1995 minimum-days-per-week floor; then converts the supplied wage to a weekly amount according to the pay-period code. Returns the calculated wage together with an error message in a single-row REF CURSOR.	`clsStatutoryWeeklyWageCalculation.GetStatutoryWeeklyWageCalculation(inDataportal, inInjuryDate, inWages, inPayPeriod, inHoursPerDay, inDaysPerWeek)` → BOF retrieve pipeline → `BeforeRetrieve` issues the stored-procedure request `CalculateStatutoryWeeklyWage`.
Emit calculation result set	`SelectStatutoryWeeklyWage` (nested private procedure) opens the `recordset` REF CURSOR over `dual`, projecting the local variables `v_StatutoryWeeklyWage` and `v_ErrorMessage` as columns `StatutoryWeeklyWage` and `ErrorMessage`. Called from every exit path so the caller always receives exactly one row.	Called by the main body at each of the five validation exits and at the successful-calculation exit.
Consume calculation result (.NET side, for traceability)	`clsStatutoryWeeklyWageCalculation.AfterRetrieve` reads the single cursor row into `m_StatutoryWeeklyWage` and `m_ErrorMessage`, exposed as the read-only properties `StatutoryWeeklyWage` and `ErrorMessage`. Wrapped in an empty `Try … Catch`, so a read failure leaves the members at their defaults.	Immediately after the stored-procedure request completes (`clsStatutoryWeeklyWageCalculation.vb:88–100`).

These operations are also listed in operations-processes.md with a source reference back to this page.

Source Code¶

PL/SQL procedure body

procedure sp82286_statutoryweeklywage
(inInjuryDate         IN Varchar2
, inWages             IN Number
, inPayPeriod         IN Varchar2
, inHoursWorkedPerDay IN Number
, inDaysWorkedPerWeek IN Number
,  recordset IN OUT sys_refcursor)
/****************************************************************
*Procedure Title  : Calcuate Statutory Weekly Wage
*
*Name             : sp82286_statutoryweeklywagecalculation
*Called by        :.Net object clsStatutoryWeeklyWageCalculation
*****************************************************************
*PROGRAMMER     ANALYST                 DATE                     MOD #
*TONBUS                                 06/22/2007
*Description:  Process determines the statutory weekly wage depending on the
*              parameters that are passed in and makes the results available
*              to the .net object clsStatutoryWeeklyWageCalculation
******************************************************************/

Is

 v_StatutoryWeeklyWage Number(11, 2);
 v_ErrorMessage        Varchar2(100);
 v_WeeksPerMonth       Number(5, 4);
 v_DaysWorkedPerWeek   Number;

procedure SelectStatutoryWeeklyWage
is
  begin
  OPEN recordset FOR
  SELECT
       v_StatutoryWeeklyWage as StatutoryWeeklyWage,
       v_ErrorMessage as ErrorMessage
    FROM dual;
end SelectStatutoryWeeklyWage;

-- ----------------------------------------------------------
--    M a i n   P r o c e d u r e
-- ----------------------------------------------------------
BEGIN
v_ErrorMessage := '';
v_StatutoryWeeklyWage := 0.00;
v_DaysWorkedPerWeek := inDaysWorkedPerWeek;

        --check for injury date
        If to_date(inInjuryDate, 'MM/DD/YYYY') <= to_date('01-jan-1900','dd-mon-yyyy') then
             v_ErrorMessage := 'Invalid injury date';
             SelectStatutoryWeeklyWage;
             Return;
        End If;

        --check for wages
        If inWages <= 0 Then
            v_errormessage := 'Invalid wages at time of injury';
            SelectStatutoryWeeklyWage;
            Return;
        End If;

        --check for valid pay period
        If inPayPeriod != 'H' and
           inPayPeriod != 'D' and
           inPayPeriod != 'W' and
           inPayPeriod != 'M' and
           inPayPeriod != 'A' Then
            v_errormessage := 'Invalid pay period';
            SelectStatutoryWeeklyWage;
            Return;
        End If;

        --check for valid hours for an hourly pay period
        If inPayPeriod = 'H' and
           inHoursWorkedPerDay = 0 Then
            v_errormessage := 'Invalid hours worked per day for a hourly pay period';
            SelectStatutoryWeeklyWage;
            Return;
        End If;

        --check for valid days for an hourly or daily pay period
        If (inPayPeriod = 'H' or
            inPayPeriod = 'D') and
           inDaysWorkedPerWeek = 0 Then
            v_errormessage := 'Invalid days worked per week for a hourly or daily pay period';
           SelectStatutoryWeeklyWage;
           Return;
        End If;

        --If the injury date is before 10/01/1973 then the weeks per month is 4.3452 else it is 4.35
        If to_date(inInjuryDate, 'MM/DD/YYYY') < to_date('01-oct-1973','dd-mon-yyyy') then
             v_WeeksPerMonth := 4.3452;
        Else
            v_WeeksPerMonth := 4.35;
        End If;

        --If the injury date is before 10/07/1995 and hourly or daily then the minimul days worked per week is three
        If (inPayPeriod = 'H' or
            inPayPeriod = 'D') and
           to_date(inInjuryDate, 'MM/DD/YYYY') < to_date('07-jun-1995','dd-mon-yyyy') and
           inDaysWorkedPerWeek < 3 Then
            v_DaysWorkedPerWeek := 3;
        End If;

        --Calculate the statutory weekly wage
        v_StatutoryWeeklyWage :=
        Case inPayPeriod
             When 'H' Then inWages * inHoursWorkedPerDay * v_DaysWorkedPerWeek
             When 'D' Then inWages * v_DaysWorkedPerWeek
             When 'W' Then inWages
             When 'M' Then inWages / v_WeeksPerMonth
             When 'A' Then inWages / 52
             --When 'A' Then (inWages / 12) / v_WeeksPerMonth
             Else 0.00
        End;

        SelectStatutoryWeeklyWage;
     Return ;

END sp82286_statutoryweeklywage;

Call signature as bound by the BOF caller

sp82286_statutoryweeklywage(
    :inInjuryDate,          -- VARCHAR2(10), 'MM/DD/YYYY'
    :inWages,               -- NUMBER
    :inPayPeriod,           -- VARCHAR2(1), one of H | D | W | M | A
    :inHoursWorkedPerDay,   -- NUMBER
    :inDaysWorkedPerWeek,   -- NUMBER
    :recordset              -- IN OUT SYS_REFCURSOR (registered as OUT by the caller)
);
-- Result set (single row, from DUAL): StatutoryWeeklyWage, ErrorMessage

Sources¶

Oracle DBA_SOURCE: WCIS_DBA.sp82286_statutoryweeklywage
Retrieved via legends oracle query --name sp82286_statutoryweeklywage on 2026-08-31 (found)
Calling code: SAIF.Business.Claim/clsStatutoryWeeklyWageCalculation.vb (lines 34, 68–100, 112, 208)

sp82286_statutoryweeklywage¶

Overview¶

Purpose¶

Procedures / Methods¶

sp82286_statutoryweeklywage¶

Parameters¶

Business Rules¶

Operations¶

Source Code¶

Related Documentation¶

Sources¶