---
title: "SQL-006: Retrieve agency offices for an agency"
summary: Retrieve agency-office rows for one agency.
status: draft
owner: Technical Writer
last_reviewed: 2026-07-31
library: SAIF.Business.Policy.Agency
reference_type: package
---

# SQL-006: Retrieve agency offices for an agency

This statement drives `clsAgencyOfficeCollection` retrieval. It reuses the office-only `AGENT` predicate and scopes the result set to one agency sequence number.

## Classification

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

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:AgencySeqNum` | Input | Decimal (`NUMBER`) | N/A | Agency sequence number whose office rows should be returned. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [AGENT](../database-tables.md#agent) | SELECT | `AGENCY_SEQ_NUM`, `AGENT_NUM`, `AGENCY_LOCATION_FLAG`, `EXCLUDE_LOV_FLAG`, `EXCLUDE_SVC_CTR_FLAG`, effective and expiration dates, audit columns |

## Sample SQL

```sql
Select
  AGENT.AGENCY_LOCATION_FLAG as AgencyLocationFlag,
  AGENT.AGENCY_SEQ_NUM as AgencySeqNum,
  AGENT.AGENT_EFF_DATE as AgentEffDate,
  AGENT.AGENT_EXP_DATE as AgentExpDate,
  AGENT.AGENT_NAME as AgentName,
  AGENT.AGENT_NUM as AgentNum,
  AGENT.CREATE_DATE as CreateDate,
  AGENT.CREATE_USER as CreateUser,
  AGENT.EXCLUDE_LOV_FLAG as ExcludeLovFlag,
  AGENT.EXCLUDE_SVC_CTR_FLAG as ExcludeSvcCtrFlag,
  AGENT.INTERFACE_DATE as InterfaceDate,
  AGENT.MAIL_ADDR_LOC_SEQ_NUM as MailAddrLocSeqNum,
  AGENT.UPDATE_DATE as UpdateDate,
  AGENT.UPDATE_USER as UpdateUser
From AGENT
Where
  (AGENT.EXCLUDE_LOV_FLAG = 'N'
   AND (AGENT.AGENCY_SEQ_NUM = AGENT.AGENT_NUM OR AGENCY_LOCATION_FLAG = 'Y'))
  AND AGENT.AGENCY_SEQ_NUM = :AgencySeqNum
```

## Operations and processes
- [Retrieve agency offices](../operations/retrieve-agency-offices.md)
- [Aggregate active offices across agencies](../operations/aggregate-active-offices-across-agencies.md)

## Business rules
- [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-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-024: The library is retrieve-only](../business-rules.md#br-024-the-library-is-retrieve-only)

## Sources
- `Business\SAIF.Business.Policy.Agency\clsAgencyOfficeCollection.vb`