SQL-009: Retrieve producers

This statement drives clsProducerCollection retrieval. It returns all producer rows when no agency sequence number is supplied and narrows the result set to one agency when the criteria includes :AgencySeqNum.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:AgencySeqNum	Input (conditional)	clsSmartDecimal to NUMBER	N/A	Agency sequence number, bound only when the collection is scoped to one agency.

Tables

Table	Access	Columns used
AGENT	SELECT	AGENT_NUM, AGENCY_SEQ_NUM, name columns, location and 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.AGENCY_SEQ_NUM = :AgencySeqNum

Operations and processes

• Bulk-load producers for a set of agencies
• Retrieve producers

Business rules

• BR-006: Bulk loaders discard orphan child rows
• BR-007: Bulk loaders replace N-plus-1 child retrieval
• BR-023: Producer agencies are detected when agency sequence number equals agent number
• BR-024: The library is retrieve-only

Sources

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