---
title: "SQL-002: Search agencies by FEID and contract types"
summary: Retrieve agencies by optional FEID and contract-type criteria.
status: draft
owner: Technical Writer
last_reviewed: 2026-07-31
library: SAIF.Business.Policy.Agency
reference_type: package
---

# SQL-002: Search agencies by FEID and contract types

This statement drives `clsAgencyCollection` retrieval. It starts from the base agency query and conditionally appends a FEID predicate, a correlated contract-type `EXISTS` predicate, or both. The contract-type filter is notable because it renders string literals into the `IN` list instead of binding them as parameters.

## Classification

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

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:FederalEmployerID` | Input (conditional) | String | 12 | Agency federal employer ID, bound only when the criteria supplies a non-empty FEID. |
| `contract type codes` | Input (rendered as literals) | String literals | N/A | Codes produced by `clsAgencyContract.Convert(AgencyContractTypes)` and interpolated into the `IN` list rather than bound. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [AGENCY](../database-tables.md#agency) | SELECT | `AGENCY_SEQ_NUM`, `AGENCY_FEID`, name columns, status/date columns, payment and paperless flags |
| [AGENCY_CONTRACT](../database-tables.md#agency_contract) | JOIN / EXISTS | `AGENCY_SEQ_NUM`, `AGENCY_CONTRACT_TYPE_CODE` |

## 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_FEID = :FederalEmployerID
  AND exists (
    select 'x'
    from agency_contract
    where agency.agency_seq_num = agency_contract.agency_seq_num
      and agency_contract_type_code in ('SERV', 'FULL', 'PREM', 'GRP', 'NCCI'))
```

## Operations and processes
- [Retrieve a set of agencies](../operations/retrieve-a-set-of-agencies.md)
- [Bulk-load producers for a set of agencies](../operations/bulk-load-producers-for-a-set-of-agencies.md)
- [Bulk-load contracts for a set of agencies](../operations/bulk-load-contracts-for-a-set-of-agencies.md)

## Business rules
- [BR-003: Agency FEID filters agency collections](../business-rules.md#br-003-agency-feid-filters-agency-collections)
- [BR-004: Contract-type filters use correlated contract existence](../business-rules.md#br-004-contract-type-filters-use-correlated-contract-existence)
- [BR-005: Empty collection criteria returns every agency](../business-rules.md#br-005-empty-collection-criteria-returns-every-agency)
- [BR-024: The library is retrieve-only](../business-rules.md#br-024-the-library-is-retrieve-only)

## Sources
- `Business\SAIF.Business.Policy.Agency\clsAgencyCollection.vb`
- `Business\SAIF.Business.Policy.Agency\clsAgencyContract.vb`