SAIF.Business.Policy.Agency database tables

This page catalogs the Oracle tables read by SAIF.Business.Policy.Agency.

Table catalog

Table	Database	Schema/Link	CRUD	Purpose	Primary business object
AGENCY	Oracle	[unqualified]	R	Stores agency identity, status, paperless, routing, and payment flags.	clsAgency
AGENCY_CONTRACT	Oracle	[unqualified]	R	Stores agency appointment, payment-frequency, and commission schedule data.	clsAgencyContract
AGENT	Oracle	[unqualified]	R	Stores both agency-office and producer rows, distinguished by retrieval predicates.	clsAgencyOffice, clsProducer
POLICY	Oracle	[unqualified]	R	Stores the agent-of-record key used by the producer lookup helper.	clsProducer

AGENCY

Field	Value
Database	Oracle
Schema/Link	[unqualified]
Purpose	Stores the main agency record used by keyed retrieves, collection filters, and status predicates.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	AGENCY_SEQ_NUM, AGENCY_FEID, AGENCY_EFF_DATE, AGENCY_EXP_DATE, AGENCY_CANCEL_REASON_CODE, DIRECT_WRITER_FLAG, EFT_FLAG, OVERRIDE_CHECK_LIMIT_FLAG, EXCLUDE_NO_TOUCH_QUOTE_FLAG
Sequence	N/A
Related tables	AGENCY_CONTRACT, AGENT
Business objects	clsAgency, clsAgencyCollection

Operations referencing this table

• Retrieve an agency by sequence number
• Retrieve a set of agencies
• Evaluate agency status predicates

Business rules referencing this table

• BR-002: Agency sequence number is required
• BR-003: Agency FEID filters agency collections
• BR-018: Y/N flags convert through framework boolean helpers
• BR-019: Special agency sequence numbers identify SAIF and not-yet-appointed agencies

SQL referencing this table

• SQL-001: Retrieve agency by sequence number
• SQL-002: Search agencies by FEID and contract types

---

AGENCY_CONTRACT

Field	Value
Database	Oracle
Schema/Link	[unqualified]
Purpose	Stores appointment and commission settings used to classify agencies and producer-commission eligibility.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	AGENCY_CONTRACT_SEQ_NUM, AGENCY_SEQ_NUM, AGENCY_CONTRACT_TYPE_CODE, COMM_GL_TYPE_CODE, EFF_DATE, EXP_DATE
Sequence	N/A
Related tables	AGENCY
Business objects	clsAgencyContract, clsAgencyContractCollection, clsAgencyCollection

Operations referencing this table

• Retrieve a set of agencies
• Bulk-load contracts for a set of agencies
• Determine an agency's contract type on a date
• Retrieve agency contracts
• Determine agency eligibility for an office
• Determine producer-commission eligibility

Business rules referencing this table

• BR-004: Contract-type filters use correlated contract existence
• BR-009: Missing active contracts mean unassigned contract type
• BR-011: Agency contract and commission codes map to fixed domain values
• BR-013: Producer-commission contracts require GL code AGT
• BR-021: Regional-office eligibility rejects service-center-only appointments

SQL referencing this table

• SQL-002: Search agencies by FEID and contract types
• SQL-003: Retrieve agency contract by sequence number
• SQL-004: Retrieve agency contracts

---

AGENT

Field	Value
Database	Oracle
Schema/Link	[unqualified]
Purpose	Stores both office/location rows and producer rows used by the agency domain model.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	AGENT_NUM, AGENCY_SEQ_NUM, AGENCY_LOCATION_FLAG, EXCLUDE_LOV_FLAG, EXCLUDE_SVC_CTR_FLAG, AGENT_EFF_DATE, AGENT_EXP_DATE, MAIL_ADDR_LOC_SEQ_NUM
Sequence	N/A
Related tables	AGENCY, POLICY
Business objects	clsAgencyOffice, clsAgencyOfficeCollection, clsProducer, clsProducerCollection

Operations referencing this table

• Bulk-load producers for a set of agencies
• Retrieve agency offices
• Retrieve producers
• Aggregate active offices across agencies

Business rules referencing this table

• BR-015: Agency-office retrieval requires non-LOV-excluded office rows
• BR-016: Agency office number is required
• BR-022: Active office aggregation clones offices without data-portal context
• BR-023: Producer agencies are detected when agency sequence number equals agent number

SQL referencing this table

• SQL-005: Retrieve agency office by agent number
• SQL-006: Retrieve agency offices for an agency
• SQL-007: Retrieve producer by agent number
• SQL-009: Retrieve producers

---

POLICY

Field	Value
Database	Oracle
Schema/Link	[unqualified]
Purpose	Supplies the agent-of-record key for the direct producer helper lookup.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	POLICY_NUM, AGENT_NUM
Sequence	N/A
Related tables	AGENT
Business objects	clsProducer

Operations referencing this table

• Look up the agent of record for a policy

Business rules referencing this table

• BR-017: Agent of record is read from POLICY.AGENT_NUM

SQL referencing this table

• SQL-008: Retrieve agent of record for a policy

Table relationship diagram

The following diagram shows the relationships implied by this library's SQL.

erDiagram
    AGENCY ||--o{ AGENCY_CONTRACT : "AGENCY_SEQ_NUM"
    AGENCY ||--o{ AGENT : "AGENCY_SEQ_NUM"
    AGENT ||--o{ POLICY : "AGENT_NUM"
    AGENT ||--|| AGENT : "agency row / office row"

Related documentation

• Index
• Overview
• Architecture
• Dependencies
• Operations and processes
• Business rules
• SQL catalog
• Configuration
• API reference

Sources

• Business\SAIF.Business.Policy.Agency\clsAgency.vb
• Business\SAIF.Business.Policy.Agency\clsAgencyCollection.vb
• Business\SAIF.Business.Policy.Agency\clsAgencyContract.vb
• Business\SAIF.Business.Policy.Agency\clsAgencyContractCollection.vb
• Business\SAIF.Business.Policy.Agency\clsAgencyOffice.vb
• Business\SAIF.Business.Policy.Agency\clsAgencyOfficeCollection.vb
• Business\SAIF.Business.Policy.Agency\clsProducer.vb
• Business\SAIF.Business.Policy.Agency\clsProducerCollection.vb