---
title: "SQL-003: Retrieve exemptions by policy"
summary: Retrieve corporate officer exemptions for one policy.
status: draft
owner: Technical Writer
last_reviewed: 2026-07-31
library: SAIF.Business.Policy.Affiliate
reference_type: package
---

# SQL-003: Retrieve exemptions by policy

Retrieve corporate officer exemptions for one policy.

## Classification

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

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:PolicyNumber` | Input | Decimal (`NUMBER`) | N/A | Policy whose corporate officer exemptions are listed. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CORP_OFFICER_EXEMPTION](../database-tables.md#corp_officer_exemption) | SELECT | `POLICY_NUM`, exemption columns reused from SQL-002 |

## Sample SQL

```sql
SELECT
  CORP_OFFICER_EXEMPTION.CORP_OFF_SEQ_NUM as CorporateOfficerExempSeqNum,
  CORP_OFFICER_EXEMPTION.POLICY_NUM as PolicyNumber,
  CORP_OFFICER_EXEMPTION.EXEMPT_REASON_CODE as ExemptionReasonCode,
  CORP_OFFICER_EXEMPTION.EFF_DATE as EffectiveDate,
  CORP_OFFICER_EXEMPTION.EXP_DATE as ExpirationDate,
  CORP_OFFICER_EXEMPTION.AFFILIATE_SSN as AffiliateSSN,
  CORP_OFFICER_EXEMPTION.CREATE_DATE as CreateDate,
  CORP_OFFICER_EXEMPTION.CREATE_USER as CreateUser,
  CORP_OFFICER_EXEMPTION.UPDATE_DATE as UpdateDate,
  CORP_OFFICER_EXEMPTION.UPDATE_USER as UpdateUser,
  CORP_OFFICER_EXEMPTION.ENDORSE_SEQ_NUM as EndorsementSeqNum
FROM CORP_OFFICER_EXEMPTION
WHERE
  CORP_OFFICER_EXEMPTION.POLICY_NUM = :PolicyNumber
```

## Operations and processes
- [Retrieve corporate officer exemptions for a policy](../operations/retrieve-corporate-officer-exemptions-for-a-policy.md)

## Business rules
- [BR-AFF-013: Affiliate SSN identifies single-officer exemptions](../business-rules.md#br-aff-013-affiliate-ssn-identifies-single-officer-exemptions)
- [BR-AFF-018: The library exposes retrieve-only data access](../business-rules.md#br-aff-018-the-library-exposes-retrieve-only-data-access)

> **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.Affiliate\clsCorporateOfficerExemptionCollection.vb`