SQL-006: Retrieve correspondence by policy-period key

This alternative retrieve shape loads correspondence by business key instead of primary key. GetInformationPage uses this statement with the fixed correspondence code P401.

Classification

Field	Value
SQL ID	SQL-006
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsCorrespondence
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve / AfterRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
:PolicyNumber	Input	Decimal (NUMBER)	—	Policy number.
:PolicyPeriodEffectiveDate	Input	Date (DATE)	—	Policy-period effective date.
:CorrespondenceCode	Input	String (VARCHAR2)	5	Correspondence code, including P401 for information pages.

Tables

Table	Access	Columns used
ENDORSEMENT	SELECT	full correspondence column projection
OUTPUT_LOG	SELECT	IMAGE_SEQ_NUM, POLICY_NUM, CORRESPOND_SEQ_NUM

Sample SQL

SELECT
  E.POLICY_NUM AS PolicyNumber,
  E.POLICY_PERIOD_EFF_DATE AS PolicyPeriodEffectiveDate,
  E.ENDORSE_SEQ_NUM AS CorrespondenceSequenceNumber,
  E.EFFECTIVE_DATE AS EffectiveDate,
  E.EXPIRATION_DATE AS ExpirationDate,
  E.ENDORSE_DATE AS CorrespondenceDate,
  E.PRINTED_DATE AS PrintedDate,
  E.ENDORSE_CODE AS CorrespondenceCode,
  E.NEW_VALUE AS NewValue,
  E.OLD_VALUE AS OldValue,
  E.CREATE_USER AS CreateUser,
  E.CREATE_DATE AS CreateDate,
  E.HOLD_PRINT_FLAG AS HoldPrintFlag,
  E.CORR_JOBID AS CorrespondenceJobID,
  E.UPDATE_USER AS UpdateUser,
  E.UPDATE_DATE AS UpdateDate,
  E.VERSION_CODE AS VersionCode,
  E.PRINTED_NCCI_CODE AS NCCIEndorsementCode,
  E.CANCEL_ENDORSE_SEQ_NUM AS CancelEndorseSequenceNumber,
  (SELECT MAX(image_seq_num)
     FROM output_log ol
    WHERE NVL(ol.image_seq_num, 0) > 0
      AND ol.policy_num = e.POLICY_NUM
      AND ol.correspond_seq_num = e.ENDORSE_SEQ_NUM) AS ImageSeqNum
FROM ENDORSEMENT E
WHERE E.POLICY_NUM = :PolicyNumber
  AND E.POLICY_PERIOD_EFF_DATE = :PolicyPeriodEffectiveDate
  AND E.ENDORSE_CODE = :CorrespondenceCode

Operations and processes

• Retrieve a correspondence item

Business rules

• BR-008: Correspondence retrieval switches between two key shapes
• BR-009: Information-page retrieval always uses code P401

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