SQL-020 Retrieve the latest claim work-release-status row

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

Classification

Field	Value
SQL ID	SQL-020
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsClaimWorkReleaseStatus
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_RELEASE_STATUS	SELECT	CLAIM_ID, CLAIM_WORK_REL_STATUS_SEQ_NUM

Sample SQL

-- Retrieve latest work-release-status row
Select CL_CLAIM_WORK_RELEASE_STATUS.*
  From CL_CLAIM_WORK_RELEASE_STATUS
 Where CLAIM_ID = :ClaimID
   And CLAIM_WORK_REL_STATUS_SEQ_NUM =
       (Select max(CLAIM_WORK_REL_STATUS_SEQ_NUM)
          From CL_CLAIM_WORK_RELEASE_STATUS
         Where CLAIM_ID = :ClaimID)

Business rules

• BR-004: Work release date must be valid at level two
• BR-005: Work release status data must be present

Sources

• SAIF.Business.Claim/clsClaimWorkReleaseStatus.vb