SQL-045 Update a claim work-status row

Updates the effective date on a work-status row; uses a differing :UpdateNum + 1 increment form rather than UPDATE_NUM + 1.*

Classification

Field	Value
SQL ID	SQL-045
Operation type	Update
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsClaimWorkStatus
Criteria property	UpdateSQL
Lifecycle method	BeforeUpdate

Parameters

Parameter	Direction	Type	Max length	Description
EffectiveDate	Input	Date	—	Effective date
UpdateUser	Input	String	30	Auditing user
ClaimID	Input	Decimal	—	Row key
SeqNum	Input	Decimal	—	Row key
UpdateNum	Input	Decimal	—	Used both in the WHERE clause and in UPDATE_NUM = :UpdateNum + 1

Tables

Table	Access	Columns used
CL_CLAIM_WORK_STATUS	UPDATE	EFFECTIVE_DATE, UPDATE_NUM, UPDATE_USER_ID, UPDATE_DATE, CLAIM_ID, CLAIM_WORK_STATUS_SEQ_NUM

Sample SQL

-- Update claim work-status
Update CL_CLAIM_WORK_STATUS
   Set EFFECTIVE_DATE = :EffectiveDate,
       UPDATE_NUM     = :UpdateNum + 1,
       UPDATE_USER_ID = :UpdateUser,
       UPDATE_DATE    = SYSDATE
 Where CLAIM_ID                  = :ClaimID
   And CLAIM_WORK_STATUS_SEQ_NUM = :SeqNum
   And UPDATE_NUM                = :UpdateNum

Business rules

• BR-003: Work status date cannot be in the future
• BR-012: Optimistic concurrency contention check

Sources

• SAIF.Business.Claim/clsClaimWorkStatus.vb