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	SELECT	AGENCY_SEQ_NUM, name columns, status/date columns, paperless/payment flags, address-location keys, audit columns

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_SEQ_NUM = :AgencySeqNum

Operations and processes

• Retrieve an agency by sequence number
• Evaluate agency status predicates

Business rules

• BR-001: Missing-key retrieval returns a default object
• BR-002: Agency sequence number is required
• BR-018: Y/N flags convert through framework boolean helpers
• BR-024: The library is retrieve-only
• BR-025: Identity keys are agency seq, contract seq, and agent number

Sources

• Business\SAIF.Business.Policy.Agency\clsAgency.vb