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	SELECT	AGENCY_SEQ_NUM, AGENT_NUM, AGENCY_LOCATION_FLAG, EXCLUDE_LOV_FLAG, EXCLUDE_SVC_CTR_FLAG, effective and expiration dates, audit columns

Sample 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
• Aggregate active offices across agencies

Business rules

• BR-015: Agency-office retrieval requires non-LOV-excluded office rows
• BR-022: Active office aggregation clones offices without data-portal context
• BR-024: The library is retrieve-only

Sources

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