---
title: SAIF.Business.Policy.Affiliate database tables
summary: Database tables used by SAIF.Business.Policy.Affiliate with relationships and usage context.
status: draft
owner: Technical Writer
last_reviewed: 2026-07-31
library: SAIF.Business.Policy.Affiliate
reference_type: package
---

# SAIF.Business.Policy.Affiliate database tables

This page catalogs the Oracle tables and framework-managed code tables accessed by `SAIF.Business.Policy.Affiliate`.

## Table catalog

| Table | Database | Schema/Link | CRUD | Purpose | Primary business object |
| --- | --- | --- | --- | --- | --- |
| [AFFILIATE](#affiliate) | Oracle | [unqualified]** | R | Person table keyed by affiliate SSN and reused by both standalone affiliate retrieval and policy-affiliate joins. | `clsAffiliate` |
| [BUSINESS_AFFILIATE](#business_affiliate) | Oracle | [unqualified]** | R | Association table linking a policy to an affiliate person, with a type code that distinguishes interested parties from preferred workers. | `clsInterestedParty` |
| [CORP_OFFICER_EXEMPTION](#corp_officer_exemption) | Oracle | [unqualified]** | R | Corporate officer exemption table keyed by exemption sequence number and scoped to a policy. | `clsCorporateOfficerExemption` |
| [PREFERRED_WORKER](#preferred_worker) | Oracle | [unqualified]** | R | Eligibility table used only by the lazy preferred-worker lookup to enrich an already loaded policy-affiliate association. | `clsPolicyPreferredWorker` |
| [AFFILIATE_TITLE_CODE](#affiliate_title_code) | Oracle | [unqualified]** | R | Framework-managed code table used to translate interested-party title codes into descriptions. | `clsInterestedParty` |
| [INSURED_CODE](#insured_code) | Oracle | [unqualified]** | R | Framework-managed code table used to translate interested-party insured-code values into descriptions. | `clsInterestedParty` |
| [EXEMPT_REASON_CODE](#exempt_reason_code) | Oracle | [unqualified]** | R | Framework-managed code table used to translate corporate officer exemption reason codes into descriptions. | `clsCorporateOfficerExemption` |

## AFFILIATE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Person table keyed by affiliate SSN and reused by both standalone affiliate retrieval and policy-affiliate joins. |
| **Access pattern** | direct SQL |
| **CRUD operations** | R |
| **Key columns used** | `AFFILIATE_SSN`, name columns, `AFFILIATE_DOB`, audit columns |
| **Sequence** | N/A |
| **Related tables** | `BUSINESS_AFFILIATE`, `PREFERRED_WORKER`, `CORP_OFFICER_EXEMPTION` |
| **Business objects** | `clsAffiliate`, `clsInterestedParty`, `clsPolicyPreferredWorker`, `clsPolicyAffiliateBase` |

### Operations referencing this table
- [Retrieve an affiliate by SSN](operations/retrieve-an-affiliate-by-ssn.md)
- [Retrieve an interested party](operations/retrieve-an-interested-party.md)
- [Retrieve a policy preferred worker](operations/retrieve-a-policy-preferred-worker.md)
- [Classify a corporate officer exemption](operations/classify-a-corporate-officer-exemption.md)

### Business rules referencing this table
- [BR-AFF-001: Missing-key retrieval returns a default object](business-rules.md#br-aff-001-missing-key-retrieval-returns-a-default-object)
- [BR-AFF-002: Affiliate display names omit empty parts](business-rules.md#br-aff-002-affiliate-display-names-omit-empty-parts)
- [BR-AFF-020: Stale cached affiliate objects are reloaded on access](business-rules.md#br-aff-020-stale-cached-affiliate-objects-are-reloaded-on-access)

### SQL referencing this table
- [SQL-001: Retrieve affiliate by SSN](sql/SQL-001-saif-business-policy-affiliate.md)
- [SQL-004: Retrieve interested party by key](sql/SQL-004-saif-business-policy-affiliate.md)
- [SQL-005: Retrieve interested parties by policy window](sql/SQL-005-saif-business-policy-affiliate.md)
- [SQL-006: Retrieve preferred worker by key](sql/SQL-006-saif-business-policy-affiliate.md)
- [SQL-008: Retrieve preferred workers by policy window](sql/SQL-008-saif-business-policy-affiliate.md)

---

## BUSINESS_AFFILIATE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Association table linking a policy to an affiliate person, with a type code that distinguishes interested parties from preferred workers. |
| **Access pattern** | direct SQL |
| **CRUD operations** | R |
| **Key columns used** | `AFFILIATE_SSN`, `POLICY_NUM`, `BUS_SEQ_NUM`, `AFFILIATE_EFF_DATE`, `AFFILIATE_CANCEL_DATE`, type and flag columns |
| **Sequence** | N/A |
| **Related tables** | `AFFILIATE` |
| **Business objects** | `clsInterestedParty`, `clsInterestedPartyCollection`, `clsPolicyPreferredWorker`, `clsPolicyPreferredWorkerCollection` |

### Operations referencing this table
- [Retrieve an interested party](operations/retrieve-an-interested-party.md)
- [Retrieve interested parties for a policy](operations/retrieve-interested-parties-for-a-policy.md)
- [Retrieve a policy preferred worker](operations/retrieve-a-policy-preferred-worker.md)
- [Retrieve preferred workers for a policy](operations/retrieve-preferred-workers-for-a-policy.md)
- [Compare and locate affiliate associations](operations/compare-and-locate-affiliate-associations.md)

### Business rules referencing this table
- [BR-AFF-003: Interested-party queries require type code 1](business-rules.md#br-aff-003-interested-party-queries-require-type-code-1)
- [BR-AFF-004: Affiliate associations join to people by SSN](business-rules.md#br-aff-004-affiliate-associations-join-to-people-by-ssn)
- [BR-AFF-005: Association windows use overlap semantics](business-rules.md#br-aff-005-association-windows-use-overlap-semantics)
- [BR-AFF-006: Association lists sort by person name](business-rules.md#br-aff-006-association-lists-sort-by-person-name)
- [BR-AFF-009: Preferred-worker queries require type code 2](business-rules.md#br-aff-009-preferred-worker-queries-require-type-code-2)
- [BR-AFF-017: Policy-affiliate identity is a three-part key](business-rules.md#br-aff-017-policy-affiliate-identity-is-a-three-part-key)

### SQL referencing this table
- [SQL-004: Retrieve interested party by key](sql/SQL-004-saif-business-policy-affiliate.md)
- [SQL-005: Retrieve interested parties by policy window](sql/SQL-005-saif-business-policy-affiliate.md)
- [SQL-006: Retrieve preferred worker by key](sql/SQL-006-saif-business-policy-affiliate.md)
- [SQL-008: Retrieve preferred workers by policy window](sql/SQL-008-saif-business-policy-affiliate.md)

---

## CORP_OFFICER_EXEMPTION

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Corporate officer exemption table keyed by exemption sequence number and scoped to a policy. |
| **Access pattern** | direct SQL |
| **CRUD operations** | R |
| **Key columns used** | `CORP_OFF_SEQ_NUM`, `POLICY_NUM`, `EXEMPT_REASON_CODE`, `EFF_DATE`, `EXP_DATE`, `AFFILIATE_SSN`, `ENDORSE_SEQ_NUM` |
| **Sequence** | N/A |
| **Related tables** | `AFFILIATE` |
| **Business objects** | `clsCorporateOfficerExemption`, `clsCorporateOfficerExemptionCollection` |

### Operations referencing this table
- [Retrieve a corporate officer exemption](operations/retrieve-a-corporate-officer-exemption.md)
- [Retrieve corporate officer exemptions for a policy](operations/retrieve-corporate-officer-exemptions-for-a-policy.md)
- [Classify a corporate officer exemption](operations/classify-a-corporate-officer-exemption.md)

### Business rules referencing this table
- [BR-AFF-013: Affiliate SSN identifies single-officer exemptions](business-rules.md#br-aff-013-affiliate-ssn-identifies-single-officer-exemptions)
- [BR-AFF-014: IsActiveCorporateOfficer requires an expiration date](business-rules.md#br-aff-014-isactivecorporateofficer-requires-an-expiration-date)
- [BR-AFF-015: Corporate officer details are loaded only for single-officer exemptions](business-rules.md#br-aff-015-corporate-officer-details-are-loaded-only-for-single-officer-exemptions)

### SQL referencing this table
- [SQL-002: Retrieve corporate officer exemption by sequence](sql/SQL-002-saif-business-policy-affiliate.md)
- [SQL-003: Retrieve exemptions by policy](sql/SQL-003-saif-business-policy-affiliate.md)

---

## PREFERRED_WORKER

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Eligibility table used only by the lazy preferred-worker lookup to enrich an already loaded policy-affiliate association. |
| **Access pattern** | direct SQL |
| **CRUD operations** | R |
| **Key columns used** | `AFFILIATE_SSN`, `PREFERRED_WORKER_ID`, `PW_CONTRACT_TYPE_CODE`, `PW_ELIGIBILITY_EFF_DATE`, `PW_ELIGIBILITY_EXP_DATE` |
| **Sequence** | N/A |
| **Related tables** | `AFFILIATE` |
| **Business objects** | `clsPolicyPreferredWorker` |

### Operations referencing this table
- [Load preferred-worker eligibility](operations/load-preferred-worker-eligibility.md)

### Business rules referencing this table
- [BR-AFF-010: Eligibility rows must cover both effective and cancel dates](business-rules.md#br-aff-010-eligibility-rows-must-cover-both-effective-and-cancel-dates)
- [BR-AFF-011: Earliest qualifying eligibility row wins](business-rules.md#br-aff-011-earliest-qualifying-eligibility-row-wins)
- [BR-AFF-012: Missing eligibility returns sentinel values](business-rules.md#br-aff-012-missing-eligibility-returns-sentinel-values)

### SQL referencing this table
- [SQL-007: Load preferred-worker eligibility](sql/SQL-007-saif-business-policy-affiliate.md)

---

## AFFILIATE_TITLE_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Framework-managed code table used to translate interested-party title codes into descriptions. |
| **Access pattern** | direct SQL via framework code collection |
| **CRUD operations** | R |
| **Key columns used** | `CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | `BUSINESS_AFFILIATE` |
| **Business objects** | `clsInterestedParty`, `clsInterestedPartyCollection` |

### Operations referencing this table
- [Resolve a code description](operations/resolve-a-code-description.md)

### Business rules referencing this table
- [BR-AFF-016: Code descriptions come from framework code collections](business-rules.md#br-aff-016-code-descriptions-come-from-framework-code-collections)

---

## INSURED_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Framework-managed code table used to translate interested-party insured-code values into descriptions. |
| **Access pattern** | direct SQL via framework code collection |
| **CRUD operations** | R |
| **Key columns used** | `CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | `BUSINESS_AFFILIATE` |
| **Business objects** | `clsInterestedParty`, `clsInterestedPartyCollection` |

### Operations referencing this table
- [Resolve a code description](operations/resolve-a-code-description.md)

### Business rules referencing this table
- [BR-AFF-016: Code descriptions come from framework code collections](business-rules.md#br-aff-016-code-descriptions-come-from-framework-code-collections)

---

## EXEMPT_REASON_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Framework-managed code table used to translate corporate officer exemption reason codes into descriptions. |
| **Access pattern** | direct SQL via framework code collection |
| **CRUD operations** | R |
| **Key columns used** | `CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | `CORP_OFFICER_EXEMPTION` |
| **Business objects** | `clsCorporateOfficerExemption` |

### Operations referencing this table
- [Resolve a code description](operations/resolve-a-code-description.md)

### Business rules referencing this table
- [BR-AFF-016: Code descriptions come from framework code collections](business-rules.md#br-aff-016-code-descriptions-come-from-framework-code-collections)

---

## Table relationship diagram

The following diagram shows the discoverable relationships between the tables used by this library.

```mermaid
erDiagram
  AFFILIATE ||--o{ BUSINESS_AFFILIATE : AFFILIATE_SSN
  AFFILIATE ||--o{ PREFERRED_WORKER : AFFILIATE_SSN
  AFFILIATE ||--o{ CORP_OFFICER_EXEMPTION : AFFILIATE_SSN
  BUSINESS_AFFILIATE }o--|| AFFILIATE_TITLE_CODE : AFFILIATE_TITLE_CODE
  BUSINESS_AFFILIATE }o--|| INSURED_CODE : INSURED_CODE
  CORP_OFFICER_EXEMPTION }o--|| EXEMPT_REASON_CODE : EXEMPT_REASON_CODE
```

## Related documentation
- [Index](index.md)
- [Overview](overview.md)
- [Architecture](architecture.md)
- [Dependencies](dependencies.md)
- [Operations and processes](operations-processes.md)
- [Business rules](business-rules.md)
- [SQL catalog](sql/index.md)
- [API reference](api/index.md)
- [Configuration](configuration.md)

> **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\clsAffiliate.vb`
- `Business\SAIF.Business.Policy.Affiliate\clsPolicyAffiliateBase.vb`
- `Business\SAIF.Business.Policy.Affiliate\clsInterestedParty.vb`
- `Business\SAIF.Business.Policy.Affiliate\clsInterestedPartyCollection.vb`
- `Business\SAIF.Business.Policy.Affiliate\clsPolicyPreferredWorker.vb`
- `Business\SAIF.Business.Policy.Affiliate\clsPolicyPreferredWorkerCollection.vb`
- `Business\SAIF.Business.Policy.Affiliate\clsCorporateOfficerExemption.vb`
- `Business\SAIF.Business.Policy.Affiliate\clsCorporateOfficerExemptionCollection.vb`