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	SELECT	AGENCY_SEQ_NUM, AGENCY_FEID, name columns, status/date columns, payment and paperless flags
AGENCY_CONTRACT	JOIN / EXISTS	AGENCY_SEQ_NUM, AGENCY_CONTRACT_TYPE_CODE

Sample 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
• Bulk-load producers for a set of agencies
• Bulk-load contracts for a set of agencies

Business rules

• BR-003: Agency FEID filters agency collections
• BR-004: Contract-type filters use correlated contract existence
• BR-005: Empty collection criteria returns every agency
• BR-024: The library is retrieve-only

Sources

• Business\SAIF.Business.Policy.Agency\clsAgencyCollection.vb
• Business\SAIF.Business.Policy.Agency\clsAgencyContract.vb