---
title: "SQL-014: Retrieve endorsements for a policy"
summary: Inline read that loads endorsement rows for a policy without period scoping.
status: draft
owner: Technical Writer
last_reviewed: 2026-07-31
library: SAIF.Business.Policy.Correspondence
reference_type: package
---

# SQL-014: Retrieve endorsements for a policy

This collection query returns endorsement-coded correspondence rows for a policy when no policy-period filter is requested.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-014 |
| **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. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [ENDORSEMENT](../database-tables.md#endorsement) | SELECT | full correspondence column projection |
| [ENDORSE_CODE](../database-tables.md#endorse_code) | EXISTS filter | CODE, CORRESPOND_TYPE_CODE |
| [OUTPUT_LOG](../database-tables.md#output_log) | SELECT | IMAGE_SEQ_NUM, POLICY_NUM, CORRESPOND_SEQ_NUM |

## Sample SQL

```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 EXISTS (
  SELECT 1
    FROM ENDORSE_CODE EC
   WHERE EC.CODE = E.ENDORSE_CODE
     AND EC.CORRESPOND_TYPE_CODE = 'E')
  AND E.POLICY_NUM = :PolicyNumber
```

## Operations and processes
- [Retrieve endorsement collections for a policy or policy period](../operations/retrieve-endorsement-collections-for-a-policy-or-policy-period.md)

## Business rules
- [BR-001: Only endorsement-coded correspondence is returned](../business-rules.md#br-001-only-endorsement-coded-correspondence-is-returned)
- [BR-002: Policy-period filters bind only for period-scoped retrieval](../business-rules.md#br-002-policy-period-filters-bind-only-for-period-scoped-retrieval)
- [BR-010: Transaction-pending endorsement loads reuse a pre-fetched code collection](../business-rules.md#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`