SQL-015: Retrieve endorsements for a policy period

This policy-period query applies different inclusion logic for Guidewire and non-Guidewire policy periods, while still restricting the result set to endorsement-coded rows.

Classification

Field	Value
SQL ID	SQL-015
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsEndorsementCollection
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, bound once and reused in the predicate.
:PolicyPeriodExpirationDate	Input	Date (DATE)	—	Policy-period expiration date.

Tables

Table	Access	Columns used
ENDORSEMENT	SELECT	full correspondence column projection, EFFECTIVE_DATE, EXPIRATION_DATE
POLICY_PERIOD	LEFT JOIN	POLICY_NUM, POLICY_PERIOD_EFF_DATE, SOURCE_SYSTEM_CODE
ENDORSE_CODE	EXISTS filter	CODE, CORRESPOND_TYPE_CODE
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
LEFT OUTER JOIN POLICY_PERIOD pp
  ON pp.policy_num = e.policy_num
  AND pp.policy_period_eff_date = :PolicyPeriodEffectiveDate
WHERE EXISTS (
  SELECT 1
    FROM ENDORSE_CODE EC
   WHERE EC.CODE = E.ENDORSE_CODE
     AND EC.CORRESPOND_TYPE_CODE = 'E')
  AND E.POLICY_NUM = :PolicyNumber
  AND ((NVL(pp.source_system_code, 'x') <> 'GW'
        AND ((e.effective_date < :PolicyPeriodExpirationDate
              AND NVL(e.expiration_date, :PolicyPeriodEffectiveDate + 1) > :PolicyPeriodEffectiveDate)
          OR (e.effective_date = :PolicyPeriodEffectiveDate
              AND e.expiration_date = :PolicyPeriodEffectiveDate)
          OR e.policy_period_eff_date = :PolicyPeriodEffectiveDate))
    OR (pp.source_system_code = 'GW'
        AND e.policy_period_eff_date = :PolicyPeriodEffectiveDate))
ORDER BY e.endorse_seq_num DESC

Operations and processes

• Retrieve endorsement collections for a policy or policy period

Business rules

• BR-001: Only endorsement-coded correspondence is returned
• BR-002: Policy-period filters bind only for period-scoped retrieval
• BR-003: Policy-period membership depends on source-system rules
• BR-010: Transaction-pending endorsement loads reuse a pre-fetched code collection

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\clsEndorsementCollection.vb