SQL-005: Retrieve agency office by agent number

This statement loads a single office view from AGENT for clsAgencyOffice. It applies the office-only predicate and then narrows the result to one agent number.

Classification

Field	Value
SQL ID	SQL-005
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]
Command type	Text
Business object	clsAgencyOffice
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
:AgentNum	Input	Decimal (NUMBER)	N/A	Agent number used as the agency-office identity key.

Tables

Table	Access	Columns used
AGENT	SELECT	AGENT_NUM, AGENCY_SEQ_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.AGENT_NUM = :AgentNum

Operations and processes

• Retrieve agency offices

Business rules

• BR-001: Missing-key retrieval returns a default object
• BR-015: Agency-office retrieval requires non-LOV-excluded office rows
• BR-016: Agency office number is required
• 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\clsAgencyOffice.vb