---
title: "SQL-001: Retrieve agency by sequence number"
summary: Retrieve one agency row by agency sequence number.
status: draft
owner: Technical Writer
last_reviewed: 2026-07-31
library: SAIF.Business.Policy.Agency
reference_type: package
---

# SQL-001: Retrieve agency by sequence number

This statement loads a single `AGENCY` row for `clsAgency` so the BOF retrieve lifecycle can hydrate one agency business object by its primary key.

## Classification

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

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:AgencySeqNum` | Input | Decimal (`NUMBER`) | N/A | Agency sequence number used as the agency identity key. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [AGENCY](../database-tables.md#agency) | SELECT | `AGENCY_SEQ_NUM`, name columns, status/date columns, paperless/payment flags, address-location keys, audit columns |

## Sample SQL

```sql
Select
  AGENCY.AGENCY_PAPERLESS_FLAG as AgencyPaperlessFlag,
  AGENCY.POLICY_HOLDER_PAPERLESS_FLAG as PolicyHolderPaperlessFlag,
  AGENCY.AGENCY_CANCEL_REASON_CODE as AgencyCancelReasonCode,
  AGENCY.AGENCY_EFF_DATE as AgencyEffDate,
  AGENCY.AGENCY_EXP_DATE as AgencyExpDate,
  AGENCY.AGENCY_FEID as AgencyFeid,
  AGENCY.AGENCY_LEGAL_NAME as AgencyLegalName,
  AGENCY.AGENCY_LICENSE_NUMBER as AgencyLicenseNumber,
  AGENCY.AGENCY_NAME as AgencyName,
  AGENCY.AGENCY_SEQ_NUM as AgencySeqNum,
  AGENCY.AGENCY_SSN as AgencySsn,
  AGENCY.APPOINTED_DATE as AppointedDate,
  AGENCY.CHECK_ADDR_LOC_SEQ_NUM as CheckAddrLocSeqNum,
  AGENCY.COMM_HOLD_EFF_DATE as CommHoldEffDate,
  AGENCY.COMM_STMT_ROUTE_TEXT as CommStmtRouteText,
  AGENCY.CREATE_DATE as CreateDate,
  AGENCY.CREATE_USER as CreateUser,
  AGENCY.DIRECT_WRITER_FLAG as DirectWriterFlag,
  AGENCY.EFT_FLAG as EftFlag,
  AGENCY.INTERFACE_DATE as InterfaceDate,
  AGENCY.ONLINE_STMTS_ONLY_FLAG as OnlineStmtsOnlyFlag,
  AGENCY.OVERRIDE_CHECK_LIMIT_FLAG as OverrideCheckLimitFlag,
  AGENCY.PMS_AGENCY_NUM as PmsAgencyNum,
  AGENCY.PRINT_1099_FLAG as Print1099Flag,
  AGENCY.PRODUCER_DETAIL_FLAG as ProducerDetailFlag,
  AGENCY.STATEMENT_ADDR_LOC_SEQ_NUM as StatementAddrLocSeqNum,
  AGENCY.UPDATE_DATE as UpdateDate,
  AGENCY.UPDATE_USER as UpdateUser,
  AGENCY.VENDOR_NUM as VendorNum,
  AGENCY.EXCLUDE_NO_TOUCH_QUOTE_FLAG as ExcludeNoTouchQuoteFlag
From AGENCY
Where
  AGENCY.AGENCY_SEQ_NUM = :AgencySeqNum
```

## Operations and processes
- [Retrieve an agency by sequence number](../operations/retrieve-an-agency-by-sequence-number.md)
- [Evaluate agency status predicates](../operations/evaluate-agency-status-predicates.md)

## Business rules
- [BR-001: Missing-key retrieval returns a default object](../business-rules.md#br-001-missing-key-retrieval-returns-a-default-object)
- [BR-002: Agency sequence number is required](../business-rules.md#br-002-agency-sequence-number-is-required)
- [BR-018: Y/N flags convert through framework boolean helpers](../business-rules.md#br-018-yn-flags-convert-through-framework-boolean-helpers)
- [BR-024: The library is retrieve-only](../business-rules.md#br-024-the-library-is-retrieve-only)
- [BR-025: Identity keys are agency seq, contract seq, and agent number](../business-rules.md#br-025-identity-keys-are-agency-seq-contract-seq-and-agent-number)

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