SQL-004: Retrieve agency contracts¶

This statement drives clsAgencyContractCollection retrieval. It returns all contract 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-004
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	`[unqualified]`
Command type	Text
Business object	`clsAgencyContractCollection`
Criteria property	`RetrieveSQL`
Lifecycle method	`BeforeRetrieve`

Parameters¶

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

Tables¶

Table	Access	Columns used
AGENCY_CONTRACT	SELECT	`AGENCY_CONTRACT_SEQ_NUM`, `AGENCY_CONTRACT_TYPE_CODE`, `AGENCY_SEQ_NUM`, `COMM_GL_TYPE_CODE`, 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_SEQ_NUM = :AgencySeqNum

Operations and processes¶

Business rules¶

Sources¶

Business\SAIF.Business.Policy.Agency\clsAgencyContractCollection.vb