SQL-016: Update correspondence or endorsement row
This update statement writes correspondence changes back to ENDORSEMENT only when the stored UPDATE_DATE still matches the caller-supplied original value, giving the library optimistic concurrency protection.
Classification
Field
Value
SQL ID SQL-016
Operation type Update
Execution method Inline SQL
Database Oracle
Schema [unqualified]**
Command type Text
Business object clsCorrespondence
Criteria property UpdateSQL
Lifecycle method BeforeUpdate / AfterUpdate
Parameters
Parameter
Direction
Type
Max length
Description
:LastUpdateDate
Input
clsSmartDate (DATE)—
Optimistic-concurrency token representing the pre-change update date.
:PolicyNumber
Input
Decimal (NUMBER)
—
Policy number.
:PolicyPeriodEffectiveDate
Input
clsSmartDate (DATE)—
Policy-period effective date.
:CorrespondenceSequenceNumber
Input
Decimal (NUMBER)
—
Correspondence sequence number.
:EffectiveDate
Input
Date (DATE)
—
Correspondence effective date.
:ExpirationDate
Input
clsSmartDate (DATE)—
Optional expiration date.
:CorrespondenceDate
Input
Date (DATE)
—
Correspondence date.
:PrintedDate
Input
clsSmartDate (DATE)—
Optional printed date.
:CorrespondenceCode
Input
String (VARCHAR2)
5
Correspondence code.
:NewValue
Input
clsSmartString (VARCHAR2)65
New value text.
:OldValue
Input
clsSmartString (VARCHAR2)65
Prior value text.
:CreateUser
Input
String (VARCHAR2)
30
Create-user value preserved during update.
:CreateDate
Input
Date (DATE)
—
Create-date value preserved during update.
:HoldPrintFlag
Input
clsSmartString (VARCHAR2)1
Hold-print indicator.
:CorrespondenceJobID
Input
clsSmartDecimal (NUMBER)—
Print job identifier.
:UpdateUser
Input
clsSmartString (VARCHAR2)30
New update-user value.
:UpdateDate
Input
clsSmartDate (DATE)—
New update-date value.
:VersionCode
Input
clsSmartString (VARCHAR2)8
Document version code.
:NCCIEndorsementCode
Input
clsSmartString (VARCHAR2)5
Printed NCCI endorsement code.
:CancelEndorseSequenceNumber
Input
clsSmartDecimal (NUMBER)—
Cancelled endorsement sequence number.
Tables
Table
Access
Columns used
ENDORSEMENT UPDATE
POLICY_NUM, POLICY_PERIOD_EFF_DATE, ENDORSE_SEQ_NUM, EFFECTIVE_DATE, EXPIRATION_DATE, ENDORSE_DATE, PRINTED_DATE, ENDORSE_CODE, NEW_VALUE, OLD_VALUE, CREATE_USER, CREATE_DATE, HOLD_PRINT_FLAG, CORR_JOBID, UPDATE_USER, UPDATE_DATE, VERSION_CODE, PRINTED_NCCI_CODE, CANCEL_ENDORSE_SEQ_NUM
Sample SQL
UPDATE ENDORSEMENT
SET
POLICY_NUM = : PolicyNumber ,
POLICY_PERIOD_EFF_DATE = : PolicyPeriodEffectiveDate ,
ENDORSE_SEQ_NUM = : CorrespondenceSequenceNumber ,
EFFECTIVE_DATE = : EffectiveDate ,
EXPIRATION_DATE = : ExpirationDate ,
ENDORSE_DATE = : CorrespondenceDate ,
PRINTED_DATE = : PrintedDate ,
ENDORSE_CODE = : CorrespondenceCode ,
NEW_VALUE = : NewValue ,
OLD_VALUE = : OldValue ,
CREATE_USER = : CreateUser ,
CREATE_DATE = : CreateDate ,
HOLD_PRINT_FLAG = : HoldPrintFlag ,
CORR_JOBID = : CorrespondenceJobID ,
UPDATE_USER = : UpdateUser ,
UPDATE_DATE = : UpdateDate ,
VERSION_CODE = : VersionCode ,
PRINTED_NCCI_CODE = : NCCIEndorsementCode ,
CANCEL_ENDORSE_SEQ_NUM = : CancelEndorseSequenceNumber
WHERE ENDORSE_SEQ_NUM = : CorrespondenceSequenceNumber
AND ( UPDATE_DATE = : LastUpdateDate
OR ( UPDATE_DATE IS NULL AND : LastUpdateDate IS NULL ))
Operations and processes
Business rules
Note: Statements marked with * were inferred from code structure, naming conventions, or
contextual evidence where direct confirmation was not available in source artifacts. Statements
marked with ** could not be confirmed; treat as unknown until verified.
Sources
Business\SAIF.Business.Policy.Correspondence\clsCorrespondence.vb