SQL-007: Retrieve producer by agent number

This statement loads a single producer view from AGENT for clsProducer, keyed by agent number.

Classification

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

Parameters

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

Tables

Table	Access	Columns used
AGENT	SELECT	AGENT_NUM, AGENCY_SEQ_NUM, name columns, AGENCY_LOCATION_FLAG, exclusion flags, 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.AGENT_NUM = :AgentNum

Operations and processes

• Retrieve producers

Business rules

• BR-001: Missing-key retrieval returns a default object
• BR-023: Producer agencies are detected when agency sequence number equals agent number
• 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\clsProducer.vb