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

# SAIF.Business.Policy.Agency database tables

This page catalogs the Oracle tables read by `SAIF.Business.Policy.Agency`.

## Table catalog

| Table | Database | Schema/Link | CRUD | Purpose | Primary business object |
| --- | --- | --- | --- | --- | --- |
| [AGENCY](#agency) | Oracle | `[unqualified]` | R | Stores agency identity, status, paperless, routing, and payment flags. | `clsAgency` |
| [AGENCY_CONTRACT](#agency_contract) | Oracle | `[unqualified]` | R | Stores agency appointment, payment-frequency, and commission schedule data. | `clsAgencyContract` |
| [AGENT](#agent) | Oracle | `[unqualified]` | R | Stores both agency-office and producer rows, distinguished by retrieval predicates. | `clsAgencyOffice`, `clsProducer` |
| [POLICY](#policy) | Oracle | `[unqualified]` | R | Stores the agent-of-record key used by the producer lookup helper. | `clsProducer` |

## AGENCY

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]` |
| **Purpose** | Stores the main agency record used by keyed retrieves, collection filters, and status predicates. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `AGENCY_SEQ_NUM`, `AGENCY_FEID`, `AGENCY_EFF_DATE`, `AGENCY_EXP_DATE`, `AGENCY_CANCEL_REASON_CODE`, `DIRECT_WRITER_FLAG`, `EFT_FLAG`, `OVERRIDE_CHECK_LIMIT_FLAG`, `EXCLUDE_NO_TOUCH_QUOTE_FLAG` |
| **Sequence** | N/A |
| **Related tables** | `AGENCY_CONTRACT`, `AGENT` |
| **Business objects** | `clsAgency`, `clsAgencyCollection` |

### Operations referencing this table
- [Retrieve an agency by sequence number](operations/retrieve-an-agency-by-sequence-number.md)
- [Retrieve a set of agencies](operations/retrieve-a-set-of-agencies.md)
- [Evaluate agency status predicates](operations/evaluate-agency-status-predicates.md)

### Business rules referencing this table
- [BR-002: Agency sequence number is required](business-rules.md#br-002-agency-sequence-number-is-required)
- [BR-003: Agency FEID filters agency collections](business-rules.md#br-003-agency-feid-filters-agency-collections)
- [BR-018: Y/N flags convert through framework boolean helpers](business-rules.md#br-018-yn-flags-convert-through-framework-boolean-helpers)
- [BR-019: Special agency sequence numbers identify SAIF and not-yet-appointed agencies](business-rules.md#br-019-special-agency-sequence-numbers-identify-saif-and-not-yet-appointed-agencies)

### SQL referencing this table
- [SQL-001: Retrieve agency by sequence number](sql/SQL-001-retrieve-agency-by-sequence-number.md)
- [SQL-002: Search agencies by FEID and contract types](sql/SQL-002-search-agencies-by-feid-and-contract-types.md)

---

## AGENCY_CONTRACT

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]` |
| **Purpose** | Stores appointment and commission settings used to classify agencies and producer-commission eligibility. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `AGENCY_CONTRACT_SEQ_NUM`, `AGENCY_SEQ_NUM`, `AGENCY_CONTRACT_TYPE_CODE`, `COMM_GL_TYPE_CODE`, `EFF_DATE`, `EXP_DATE` |
| **Sequence** | N/A |
| **Related tables** | `AGENCY` |
| **Business objects** | `clsAgencyContract`, `clsAgencyContractCollection`, `clsAgencyCollection` |

### Operations referencing this table
- [Retrieve a set of agencies](operations/retrieve-a-set-of-agencies.md)
- [Bulk-load contracts for a set of agencies](operations/bulk-load-contracts-for-a-set-of-agencies.md)
- [Determine an agency's contract type on a date](operations/determine-an-agencys-contract-type-on-a-date.md)
- [Retrieve agency contracts](operations/retrieve-agency-contracts.md)
- [Determine agency eligibility for an office](operations/determine-agency-eligibility-for-an-office.md)
- [Determine producer-commission eligibility](operations/determine-producer-commission-eligibility.md)

### Business rules referencing this table
- [BR-004: Contract-type filters use correlated contract existence](business-rules.md#br-004-contract-type-filters-use-correlated-contract-existence)
- [BR-009: Missing active contracts mean unassigned contract type](business-rules.md#br-009-missing-active-contracts-mean-unassigned-contract-type)
- [BR-011: Agency contract and commission codes map to fixed domain values](business-rules.md#br-011-agency-contract-and-commission-codes-map-to-fixed-domain-values)
- [BR-013: Producer-commission contracts require GL code AGT](business-rules.md#br-013-producer-commission-contracts-require-gl-code-agt)
- [BR-021: Regional-office eligibility rejects service-center-only appointments](business-rules.md#br-021-regional-office-eligibility-rejects-service-center-only-appointments)

### SQL referencing this table
- [SQL-002: Search agencies by FEID and contract types](sql/SQL-002-search-agencies-by-feid-and-contract-types.md)
- [SQL-003: Retrieve agency contract by sequence number](sql/SQL-003-retrieve-agency-contract-by-sequence-number.md)
- [SQL-004: Retrieve agency contracts](sql/SQL-004-retrieve-agency-contracts.md)

---

## AGENT

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]` |
| **Purpose** | Stores both office/location rows and producer rows used by the agency domain model. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `AGENT_NUM`, `AGENCY_SEQ_NUM`, `AGENCY_LOCATION_FLAG`, `EXCLUDE_LOV_FLAG`, `EXCLUDE_SVC_CTR_FLAG`, `AGENT_EFF_DATE`, `AGENT_EXP_DATE`, `MAIL_ADDR_LOC_SEQ_NUM` |
| **Sequence** | N/A |
| **Related tables** | `AGENCY`, `POLICY` |
| **Business objects** | `clsAgencyOffice`, `clsAgencyOfficeCollection`, `clsProducer`, `clsProducerCollection` |

### Operations referencing this table
- [Bulk-load producers for a set of agencies](operations/bulk-load-producers-for-a-set-of-agencies.md)
- [Retrieve agency offices](operations/retrieve-agency-offices.md)
- [Retrieve producers](operations/retrieve-producers.md)
- [Aggregate active offices across agencies](operations/aggregate-active-offices-across-agencies.md)

### Business rules referencing this table
- [BR-015: Agency-office retrieval requires non-LOV-excluded office rows](business-rules.md#br-015-agency-office-retrieval-requires-non-lov-excluded-office-rows)
- [BR-016: Agency office number is required](business-rules.md#br-016-agency-office-number-is-required)
- [BR-022: Active office aggregation clones offices without data-portal context](business-rules.md#br-022-active-office-aggregation-clones-offices-without-data-portal-context)
- [BR-023: Producer agencies are detected when agency sequence number equals agent number](business-rules.md#br-023-producer-agencies-are-detected-when-agency-sequence-number-equals-agent-number)

### SQL referencing this table
- [SQL-005: Retrieve agency office by agent number](sql/SQL-005-retrieve-agency-office-by-agent-number.md)
- [SQL-006: Retrieve agency offices for an agency](sql/SQL-006-retrieve-agency-offices-for-an-agency.md)
- [SQL-007: Retrieve producer by agent number](sql/SQL-007-retrieve-producer-by-agent-number.md)
- [SQL-009: Retrieve producers](sql/SQL-009-retrieve-producers.md)

---

## POLICY

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]` |
| **Purpose** | Supplies the agent-of-record key for the direct producer helper lookup. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `POLICY_NUM`, `AGENT_NUM` |
| **Sequence** | N/A |
| **Related tables** | `AGENT` |
| **Business objects** | `clsProducer` |

### Operations referencing this table
- [Look up the agent of record for a policy](operations/look-up-the-agent-of-record-for-a-policy.md)

### Business rules referencing this table
- [BR-017: Agent of record is read from POLICY.AGENT_NUM](business-rules.md#br-017-agent-of-record-is-read-from-policyagent_num)

### SQL referencing this table
- [SQL-008: Retrieve agent of record for a policy](sql/SQL-008-retrieve-agent-of-record-for-a-policy.md)

## Table relationship diagram

The following diagram shows the relationships implied by this library's SQL.

```mermaid
erDiagram
    AGENCY ||--o{ AGENCY_CONTRACT : "AGENCY_SEQ_NUM"
    AGENCY ||--o{ AGENT : "AGENCY_SEQ_NUM"
    AGENT ||--o{ POLICY : "AGENT_NUM"
    AGENT ||--|| AGENT : "agency row / office row"
```

## 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)
- [Configuration](configuration.md)
- [API reference](api/index.md)

## Sources
- `Business\SAIF.Business.Policy.Agency\clsAgency.vb`
- `Business\SAIF.Business.Policy.Agency\clsAgencyCollection.vb`
- `Business\SAIF.Business.Policy.Agency\clsAgencyContract.vb`
- `Business\SAIF.Business.Policy.Agency\clsAgencyContractCollection.vb`
- `Business\SAIF.Business.Policy.Agency\clsAgencyOffice.vb`
- `Business\SAIF.Business.Policy.Agency\clsAgencyOfficeCollection.vb`
- `Business\SAIF.Business.Policy.Agency\clsProducer.vb`
- `Business\SAIF.Business.Policy.Agency\clsProducerCollection.vb`