Skip to content

Stored-procedure read models

Provides read-only, stored-procedure-backed lookups for aggravation-rights dates, timeliness of first payment, medically-stationary date, and statutory weekly wage calculations, each returning a single REF CURSOR result set.

Field Value
Operation ID stored-proc-lookups
Trigger Caller retrieves one of the four read-only business objects
Inputs ClaimID and calculation-specific inputs (e.g., injury date, wages, pay period)
Outputs Calculated result values populated in AfterRetrieve
Primary components clsAggRightDates, clsTimelinessOfFirstPayment, clsMedicallyStationaryDate, clsStatutoryWeeklyWageCalculation
Entry point SAIF.Business.Claim/clsAggRightDates.vb

Steps

  1. clsAggRightDates calls sp82046_CalcAggRightDates with inClaimID and a REF CURSOR output (SQL-013).
  2. AfterRetrieve raises "Claim Not Found" when the cursor returns no rows (BR-008).
  3. clsTimelinessOfFirstPayment calls sp82067_TimelinessOf1stPayment with inClaimID and an optional requestType = 'Detail' parameter added when Criteria.FromClaimDetail is true (SQL-034).
  4. Reuses the same "Claim Not Found" error identifier as clsAggRightDates — an ID collision flagged as a defect.**
  5. clsMedicallyStationaryDate calls sp82289_medicallystationarydt with inSaifClaimNum (length 7) and a REF CURSOR output (SQL-025).
  6. clsStatutoryWeeklyWageCalculation calls sp82286_statutoryweeklywage with injury date, wages, pay period, hours worked per day, and days worked per week, plus a REF CURSOR output (SQL-033).
  7. Each class populates its properties from the returned cursor in AfterRetrieve.

Process flow

The following diagram visualizes this operation's control flow.

sequenceDiagram
    participant Caller
    participant ReadOnlyObject as Read-only business object
    participant Procedure as Oracle stored procedure
    Caller->>ReadOnlyObject: Retrieve(claimId, ...)
    ReadOnlyObject->>Procedure: CommandType.StoredProcedure call + REF CURSOR
    Procedure-->>ReadOnlyObject: cursor rows
    ReadOnlyObject->>ReadOnlyObject: AfterRetrieve populates properties
    ReadOnlyObject-->>Caller: populated object or "Claim Not Found"

Failure and edge-case behavior

  • All four procedures are invoked without a package qualifier, so package membership is unknown; procedure bodies reside in the Oracle database and could not be retrieved in this environment.**
  • clsAggRightDates and clsTimelinessOfFirstPayment both raise "Claim Not Found" using the same reused identifier when no row is returned.**

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

  • SAIF.Business.Claim/clsAggRightDates.vb
  • SAIF.Business.Claim/clsITimelinessOfFirstPayment.vb
  • SAIF.Business.Claim/clsMedicallyStationaryDate.vb
  • SAIF.Business.Claim/clsStatutoryWeeklyWageCalculation.vb