Skip to content

Work status and work release tracking

Two closely related histories are kept for every claim: the work status, which records when the worker actually returned to work, and the work release status, which records when a physician released the worker to work and under what restriction. Both are sequence-numbered histories, both always expose the latest entry, and both are implemented as the same shape of business object, so they are documented together.

Field Value
Operation ID OP-BC-11
Trigger clsClaimWorkStatus.NewClaimWorkStatus / GetClaimWorkStatus; clsClaimWorkReleaseStatus.NewClaimWorkReleaseStatus / GetClaimWorkReleaseStatus; followed by save()
Inputs ClaimID; the return-to-work date or the work release date; the work status code or work release code; UpdateNum; the acting portal user
Outputs A sequence-numbered CL_CLAIM_WORK_STATUS or CL_CLAIM_WORK_RELEASE_STATUS row; the HasData indicator on the release object; error messages InsertError, UpdateError, DeleteError, and rule failures
Primary components clsClaimWorkStatus (BeforeCreate, AfterCreate, ValidWorkStatusDate), clsClaimWorkReleaseStatus (BeforeCreate, AfterCreate, Fill, ValidWorkReleaseDate, HasData)
Entry point Business\SAIF.Business.Claim\clsClaimWorkStatus.vb

Steps

  1. Reserve a sequence number. BeforeCreate issues a scalar request against the appropriate Oracle sequence — the work status sequence for work status rows (SQL-003, CLAIM_WORK_STATUS_SEQ) or the work release status sequence for release rows (SQL-002, CLAIM_WORK_REL_STATUS_SEQ_NUM).
  2. Seed the new row. AfterCreate assigns the returned sequence number, stores the claim identifier and the acting portal user, sets the update number to zero, and defaults the status code to the numeric-zero character. The release object additionally raises its HasData indicator.
  3. Validate the dates before saving. Each object registers a date rule that runs before persistence:
  4. The work status object rejects a return-to-work date that is populated and later than the current moment (BR-005).
  5. The work release object rejects an invalid work release date at the framework's second rule-check level (BR-003), and separately refuses to save when no work release status data is present at all (BR-004).
  6. Insert the row. save() applies the pending edit level and delegates to the framework save, which inserts the sequence number, claim identifier, date, SYSDATE, update number, and update user.
  7. The work status insert hard-codes a work status code of 1, so a new work status row always starts in that state regardless of what the caller set (SQL-007, CL_CLAIM_WORK_STATUS).
  8. The work release insert binds the work release code supplied by the caller (SQL-006, CL_CLAIM_WORK_RELEASE_STATUS).
  9. If exactly one row is not affected: add InsertError carrying the framework database contention message; otherwise the previous insert error is cleared (BR-008).
  10. Retrieve the current entry. GetClaimWorkStatus and GetClaimWorkReleaseStatus select the row for the claim whose sequence number is the maximum for that claim, so callers always see the latest history entry and never an earlier one (BR-019, SQL-020, SQL-019, CL_CLAIM_WORK_STATUS, CL_CLAIM_WORK_RELEASE_STATUS).
  11. The release object raises its HasData indicator as part of filling, so the presence rule in step 3 passes only for a row that actually came back.
  12. Update the row. Saving an existing row writes the date, SYSDATE, an incremented update number, and the update user, keyed on the claim identifier, sequence number, and prior update number (SQL-034, SQL-033).
  13. The work status update never writes the work status code column, so the code assigned at insert can never be changed through this object.
  14. If exactly one row is not affected: add UpdateError carrying the framework database contention message (BR-008).
  15. Delete the row. Deleting removes the row by claim identifier, sequence number, and prior update number (SQL-045, SQL-044).
  16. If exactly one row is not affected: add DeleteError carrying the framework database contention message (BR-008).

Process flow

The following diagram visualizes this operation's control flow.

flowchart TD
    Start([Caller opens a work status or<br>work release status for a claim]) --> Mode{New or existing?}
    Mode -->|New| Seq[Read the next value from<br>the matching Oracle sequence]
    Seq --> Seed[Seed claim, user, update number<br>and default status code]
    Mode -->|Existing| Get[Select the row with the highest<br>sequence number for the claim]
    Get --> Fill[Fill the object and raise<br>the has-data indicator]
    Seed --> Edit[Caller sets the date<br>and status code]
    Fill --> Edit
    Edit --> Rules{Date valid and<br>data present?}
    Rules -->|No| RuleErr([Rule messages surface;<br>save is refused])
    Rules -->|Yes| Write{New row?}
    Write -->|Yes| Ins[Insert the sequenced row]
    Write -->|No| Upd[Update keyed on claim, sequence<br>and prior update number]
    Ins --> IRows{Rows affected = 1?}
    Upd --> URows{Rows affected = 1?}
    IRows -->|No| IErr([Add InsertError])
    URows -->|No| UErr([Add UpdateError])
    IRows -->|Yes| Done([Row persisted])
    URows -->|Yes| Done

Failure and edge-case behavior

  • Because retrieval always returns the highest sequence number, earlier history entries are unreachable through these objects; reading the full history requires a different query.
  • The work status code column is written only at insert and never at update, so correcting a mis-set work status requires deleting and re-inserting the row.
  • A new work status row is always inserted with the hard-coded status code, so the value the caller placed on the object before saving is silently discarded.
  • The two objects express their concurrency check differently — one increments the update number from the bound parameter and the other increments the column value — so a caller reasoning about concurrency must read each statement rather than assume symmetry.*
  • The work status object registers only the date rule, so nothing prevents saving a row that carries no meaningful status data, unlike the release object which enforces a presence rule.
  • The sequence number is consumed at create time, so an abandoned edit permanently burns a sequence value.
  • A return-to-work date exactly equal to the current moment passes the rule; only strictly later dates are rejected.*

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\clsClaimWorkStatus.vb
  • Business\SAIF.Business.Claim\clsClaimWorkReleaseStatus.vb