SQL-002: Insert correspondence or endorsement row

This statement writes a new ENDORSEMENT row for both correspondence and endorsement objects after the framework assigns the next sequence value and stamps audit metadata.

Classification

Field	Value
SQL ID	SQL-002
Operation type	Create
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsCorrespondence
Criteria property	InsertSQL
Lifecycle method	BeforeInsert / AfterInsert

Parameters

Parameter	Direction	Type	Max length	Description
:PolicyNumber	Input	Decimal (NUMBER)	—	Policy number.
:PolicyPeriodEffectiveDate	Input	clsSmartDate (DATE)	—	Policy period effective date.
:CorrespondenceSequenceNumber	Input	Decimal (NUMBER)	—	Generated endorsement sequence number.
:EffectiveDate	Input	Date (DATE)	—	Correspondence effective date.
:ExpirationDate	Input	clsSmartDate (DATE)	—	Optional expiration date.
:CorrespondenceDate	Input	Date (DATE)	—	Correspondence or endorsement date.
:PrintedDate	Input	clsSmartDate (DATE)	—	Optional printed date.
:CorrespondenceCode	Input	String (VARCHAR2)	5	Endorsement or 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 audit value.
:CreateDate	Input	Date (DATE)	—	Create-date audit value.
:HoldPrintFlag	Input	clsSmartString (VARCHAR2)	1	Hold-print indicator.
:CorrespondenceJobID	Input	clsSmartDecimal (NUMBER)	—	Print job identifier.
:UpdateUser	Input	clsSmartString (VARCHAR2)	30	Update-user audit value.
:UpdateDate	Input	clsSmartDate (DATE)	—	Update-date audit 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	INSERT	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

INSERT INTO ENDORSEMENT (
  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)
VALUES (
  :PolicyNumber,
  :PolicyPeriodEffectiveDate,
  :CorrespondenceSequenceNumber,
  :EffectiveDate,
  :ExpirationDate,
  :CorrespondenceDate,
  :PrintedDate,
  :CorrespondenceCode,
  :NewValue,
  :OldValue,
  :CreateUser,
  :CreateDate,
  :HoldPrintFlag,
  :CorrespondenceJobID,
  :UpdateUser,
  :UpdateDate,
  :VersionCode,
  :NCCIEndorsementCode,
  :CancelEndorseSequenceNumber)

Operations and processes

• Persist a correspondence or endorsement

Business rules

• BR-011: Collection save applies edits before framework persistence
• BR-012: Insert and update must affect exactly one row
• BR-018: Correspondence requires non-zero primary identifiers
• BR-019: Correspondence codes must exist in ENDORSE_CODE

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