SQL-021 Retrieve the latest claim work-status row¶

Retrieves the most recent return-to-work status row for a claim using a correlated max(seq) subquery.

Classification¶

Field	Value
SQL ID	SQL-021
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	`[unqualified]**`
Command type	Text
Business object	`clsClaimWorkStatus`
Criteria property	`RetrieveSQL`
Lifecycle method	`BeforeRetrieve`

Parameters¶

Parameter	Direction	Type	Max length	Description
`ClaimID`	Input	Decimal	—	Claim identifier (bound twice: outer query and max(seq) subquery)

Tables¶

Table	Access	Columns used
CL_CLAIM_WORK_STATUS	SELECT	CLAIM_ID, CLAIM_WORK_STATUS_SEQ_NUM

Sample SQL¶

-- Retrieve latest work-status row
Select CL_CLAIM_WORK_STATUS.*
  From CL_CLAIM_WORK_STATUS
 Where CLAIM_ID = :ClaimID
   And CLAIM_WORK_STATUS_SEQ_NUM =
       (Select max(CLAIM_WORK_STATUS_SEQ_NUM)
          From CL_CLAIM_WORK_STATUS
         Where CLAIM_ID = :ClaimID)

Business rules¶

BR-003: Work status date cannot be in the future

Sources¶

SAIF.Business.Claim/clsClaimWorkStatus.vb