SQL-003: Retrieve agency contract by sequence number

This statement loads a single AGENCY_CONTRACT row for clsAgencyContract, allowing the BOF lifecycle to hydrate one agency contract business object by its primary key.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:AgencyContractSeqNum	Input	Decimal (NUMBER)	N/A	Agency contract sequence number used as the contract identity key.

Tables

Table	Access	Columns used
AGENCY_CONTRACT	SELECT	AGENCY_CONTRACT_SEQ_NUM, AGENCY_CONTRACT_TYPE_CODE, COMM_GL_TYPE_CODE, AGENCY_SEQ_NUM, schedule IDs, effective and expiration dates

Sample SQL

Select
  AGENCY_CONTRACT.AGENCY_CONTRACT_SEQ_NUM as AgencyContractSeqNum,
  AGENCY_CONTRACT.AGENCY_CONTRACT_TYPE_CODE as AgencyContractTypeCode,
  AGENCY_CONTRACT.AGENCY_PAYMENT_FREQ_CODE as AgencyPaymentFreqCode,
  AGENCY_CONTRACT.AGENCY_SEQ_NUM as AgencySeqNum,
  AGENCY_CONTRACT.COMM_GL_TYPE_CODE as CommGLTypeCode,
  AGENCY_CONTRACT.CREATE_DATE as CreateDate,
  AGENCY_CONTRACT.CREATE_USER as CreateUser,
  AGENCY_CONTRACT.DEFAULT_COMM_SCHED_ID as DefaultCommSchedID,
  AGENCY_CONTRACT.EFF_DATE as EffDate,
  AGENCY_CONTRACT.EXP_DATE as ExpDate,
  AGENCY_CONTRACT.LEGACY_COMM_SCHED_ID as LegacyCommSchedID,
  AGENCY_CONTRACT.LEGACY_RNW_COMM_SCHED_ID as LegacyRnwCommSchedID,
  AGENCY_CONTRACT.PMS_AGENCY_NUM as PmsAgencyNum,
  AGENCY_CONTRACT.RENEWAL_COMM_SCHED_ID as RenewalCommSchedID
From AGENCY_CONTRACT
Where
  AGENCY_CONTRACT.AGENCY_CONTRACT_SEQ_NUM = :AgencyContractSeqNum

Operations and processes

• Retrieve agency contracts
• Classify a contract
• Determine producer-commission eligibility

Business rules

• BR-001: Missing-key retrieval returns a default object
• BR-011: Agency contract and commission codes map to fixed domain values
• BR-012: Unknown contract or commission codes become unassigned
• BR-013: Producer-commission contracts require GL code AGT
• 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\clsAgencyContract.vb