SQL-038 Update an active benefit schedule

Updates the next issue date and remaining payment count on an active benefit-schedule row, guarded by optimistic concurrency.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
NextIssueDate	Input	Date	—	Next scheduled issue date (added twice in BeforeUpdate)
RemainingPaymentCount	Input	Decimal	—	Remaining payments on the schedule
UpdateUser	Input	String	30	Auditing user
ClaimID	Input	Decimal	—	Row key (added twice)
BenefitPaymentSeqNum	Input	Decimal	—	Row key
UpdateNum	Input	Decimal	—	Optimistic-concurrency check value

Tables

Table	Access	Columns used
CL_BENEFIT_SCHEDULE	UPDATE	NEXT_ISSUE_DATE, REMAINING_PAYMENT_COUNT, UPDATE_NUM, UPDATE_USER_ID, UPDATE_DATE, CLAIM_ID, BENEFIT_PAYMENT_SEQ_NUM

Sample SQL

-- Update active benefit schedule
Update CL_BENEFIT_SCHEDULE
   Set NEXT_ISSUE_DATE        = :NextIssueDate,
       REMAINING_PAYMENT_COUNT = :RemainingPaymentCount,
       UPDATE_NUM             = UPDATE_NUM + 1,
       UPDATE_USER_ID         = :UpdateUser,
       UPDATE_DATE            = SYSDATE
 Where CLAIM_ID                = :ClaimID
   And BENEFIT_PAYMENT_SEQ_NUM = :BenefitPaymentSeqNum
   And UPDATE_NUM              = :UpdateNum

Operations and processes

• Benefit-payment stop processing

Business rules

• BR-009: Benefit schedule update must affect a row
• BR-012: Optimistic concurrency contention check

Sources

• SAIF.Business.Claim/clsActiveBenefitSchedule.vb