SAIF.Business.Policy.Affiliate database tables

This page catalogs the Oracle tables and framework-managed code tables accessed by SAIF.Business.Policy.Affiliate.

Table catalog

Table	Database	Schema/Link	CRUD	Purpose	Primary business object
AFFILIATE	Oracle	[unqualified]**	R	Person table keyed by affiliate SSN and reused by both standalone affiliate retrieval and policy-affiliate joins.	clsAffiliate
BUSINESS_AFFILIATE	Oracle	[unqualified]**	R	Association table linking a policy to an affiliate person, with a type code that distinguishes interested parties from preferred workers.	clsInterestedParty
CORP_OFFICER_EXEMPTION	Oracle	[unqualified]**	R	Corporate officer exemption table keyed by exemption sequence number and scoped to a policy.	clsCorporateOfficerExemption
PREFERRED_WORKER	Oracle	[unqualified]**	R	Eligibility table used only by the lazy preferred-worker lookup to enrich an already loaded policy-affiliate association.	clsPolicyPreferredWorker
AFFILIATE_TITLE_CODE	Oracle	[unqualified]**	R	Framework-managed code table used to translate interested-party title codes into descriptions.	clsInterestedParty
INSURED_CODE	Oracle	[unqualified]**	R	Framework-managed code table used to translate interested-party insured-code values into descriptions.	clsInterestedParty
EXEMPT_REASON_CODE	Oracle	[unqualified]**	R	Framework-managed code table used to translate corporate officer exemption reason codes into descriptions.	clsCorporateOfficerExemption

AFFILIATE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Person table keyed by affiliate SSN and reused by both standalone affiliate retrieval and policy-affiliate joins.
Access pattern	direct SQL
CRUD operations	R
Key columns used	AFFILIATE_SSN, name columns, AFFILIATE_DOB, audit columns
Sequence	N/A
Related tables	BUSINESS_AFFILIATE, PREFERRED_WORKER, CORP_OFFICER_EXEMPTION
Business objects	clsAffiliate, clsInterestedParty, clsPolicyPreferredWorker, clsPolicyAffiliateBase

Operations referencing this table

• Retrieve an affiliate by SSN
• Retrieve an interested party
• Retrieve a policy preferred worker
• Classify a corporate officer exemption

Business rules referencing this table

• BR-AFF-001: Missing-key retrieval returns a default object
• BR-AFF-002: Affiliate display names omit empty parts
• BR-AFF-020: Stale cached affiliate objects are reloaded on access

SQL referencing this table

• SQL-001: Retrieve affiliate by SSN
• SQL-004: Retrieve interested party by key
• SQL-005: Retrieve interested parties by policy window
• SQL-006: Retrieve preferred worker by key
• SQL-008: Retrieve preferred workers by policy window

---

BUSINESS_AFFILIATE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Association table linking a policy to an affiliate person, with a type code that distinguishes interested parties from preferred workers.
Access pattern	direct SQL
CRUD operations	R
Key columns used	AFFILIATE_SSN, POLICY_NUM, BUS_SEQ_NUM, AFFILIATE_EFF_DATE, AFFILIATE_CANCEL_DATE, type and flag columns
Sequence	N/A
Related tables	AFFILIATE
Business objects	clsInterestedParty, clsInterestedPartyCollection, clsPolicyPreferredWorker, clsPolicyPreferredWorkerCollection

Operations referencing this table

• Retrieve an interested party
• Retrieve interested parties for a policy
• Retrieve a policy preferred worker
• Retrieve preferred workers for a policy
• Compare and locate affiliate associations

Business rules referencing this table

• BR-AFF-003: Interested-party queries require type code 1
• BR-AFF-004: Affiliate associations join to people by SSN
• BR-AFF-005: Association windows use overlap semantics
• BR-AFF-006: Association lists sort by person name
• BR-AFF-009: Preferred-worker queries require type code 2
• BR-AFF-017: Policy-affiliate identity is a three-part key

SQL referencing this table

• SQL-004: Retrieve interested party by key
• SQL-005: Retrieve interested parties by policy window
• SQL-006: Retrieve preferred worker by key
• SQL-008: Retrieve preferred workers by policy window

---

CORP_OFFICER_EXEMPTION

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Corporate officer exemption table keyed by exemption sequence number and scoped to a policy.
Access pattern	direct SQL
CRUD operations	R
Key columns used	CORP_OFF_SEQ_NUM, POLICY_NUM, EXEMPT_REASON_CODE, EFF_DATE, EXP_DATE, AFFILIATE_SSN, ENDORSE_SEQ_NUM
Sequence	N/A
Related tables	AFFILIATE
Business objects	clsCorporateOfficerExemption, clsCorporateOfficerExemptionCollection

Operations referencing this table

• Retrieve a corporate officer exemption
• Retrieve corporate officer exemptions for a policy
• Classify a corporate officer exemption

Business rules referencing this table

• BR-AFF-013: Affiliate SSN identifies single-officer exemptions
• BR-AFF-014: IsActiveCorporateOfficer requires an expiration date
• BR-AFF-015: Corporate officer details are loaded only for single-officer exemptions

SQL referencing this table

• SQL-002: Retrieve corporate officer exemption by sequence
• SQL-003: Retrieve exemptions by policy

---

PREFERRED_WORKER

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Eligibility table used only by the lazy preferred-worker lookup to enrich an already loaded policy-affiliate association.
Access pattern	direct SQL
CRUD operations	R
Key columns used	AFFILIATE_SSN, PREFERRED_WORKER_ID, PW_CONTRACT_TYPE_CODE, PW_ELIGIBILITY_EFF_DATE, PW_ELIGIBILITY_EXP_DATE
Sequence	N/A
Related tables	AFFILIATE
Business objects	clsPolicyPreferredWorker

Operations referencing this table

• Load preferred-worker eligibility

Business rules referencing this table

• BR-AFF-010: Eligibility rows must cover both effective and cancel dates
• BR-AFF-011: Earliest qualifying eligibility row wins
• BR-AFF-012: Missing eligibility returns sentinel values

SQL referencing this table

• SQL-007: Load preferred-worker eligibility

---

AFFILIATE_TITLE_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Framework-managed code table used to translate interested-party title codes into descriptions.
Access pattern	direct SQL via framework code collection
CRUD operations	R
Key columns used	CODE, CODE_DESCR
Sequence	N/A
Related tables	BUSINESS_AFFILIATE
Business objects	clsInterestedParty, clsInterestedPartyCollection

Operations referencing this table

• Resolve a code description

Business rules referencing this table

• BR-AFF-016: Code descriptions come from framework code collections

---

INSURED_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Framework-managed code table used to translate interested-party insured-code values into descriptions.
Access pattern	direct SQL via framework code collection
CRUD operations	R
Key columns used	CODE, CODE_DESCR
Sequence	N/A
Related tables	BUSINESS_AFFILIATE
Business objects	clsInterestedParty, clsInterestedPartyCollection

Operations referencing this table

• Resolve a code description

Business rules referencing this table

• BR-AFF-016: Code descriptions come from framework code collections

---

EXEMPT_REASON_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Framework-managed code table used to translate corporate officer exemption reason codes into descriptions.
Access pattern	direct SQL via framework code collection
CRUD operations	R
Key columns used	CODE, CODE_DESCR
Sequence	N/A
Related tables	CORP_OFFICER_EXEMPTION
Business objects	clsCorporateOfficerExemption

Operations referencing this table

• Resolve a code description

Business rules referencing this table

• BR-AFF-016: Code descriptions come from framework code collections

---

Table relationship diagram

The following diagram shows the discoverable relationships between the tables used by this library.

erDiagram
  AFFILIATE ||--o{ BUSINESS_AFFILIATE : AFFILIATE_SSN
  AFFILIATE ||--o{ PREFERRED_WORKER : AFFILIATE_SSN
  AFFILIATE ||--o{ CORP_OFFICER_EXEMPTION : AFFILIATE_SSN
  BUSINESS_AFFILIATE }o--|| AFFILIATE_TITLE_CODE : AFFILIATE_TITLE_CODE
  BUSINESS_AFFILIATE }o--|| INSURED_CODE : INSURED_CODE
  CORP_OFFICER_EXEMPTION }o--|| EXEMPT_REASON_CODE : EXEMPT_REASON_CODE

Related documentation

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

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

• Business\SAIF.Business.Policy.Affiliate\clsAffiliate.vb
• Business\SAIF.Business.Policy.Affiliate\clsPolicyAffiliateBase.vb
• Business\SAIF.Business.Policy.Affiliate\clsInterestedParty.vb
• Business\SAIF.Business.Policy.Affiliate\clsInterestedPartyCollection.vb
• Business\SAIF.Business.Policy.Affiliate\clsPolicyPreferredWorker.vb
• Business\SAIF.Business.Policy.Affiliate\clsPolicyPreferredWorkerCollection.vb
• Business\SAIF.Business.Policy.Affiliate\clsCorporateOfficerExemption.vb
• Business\SAIF.Business.Policy.Affiliate\clsCorporateOfficerExemptionCollection.vb