Skip to content

SAIF.Business.View.Policy database tables

This page catalogs the dual-database structure used by this library. Primary policy and association reads target Oracle across 17 unqualified tables, while secondary SQL Server BDS access is security-only and occurs exclusively through opaque cp_* stored procedures with no base tables addressable in source. All detected database operations are read-only, consistent with BR-VP-027: Create/Insert/Update/Delete SQL are invalid.

Table catalog

Table Database Schema/Link CRUD Purpose Primary business object
AFFILIATE Oracle [unqualified]** R Resolve officer-of-notice affiliate names for a policy address clsPolicyAddress
ASSOCIATION_TYPE_CODE Oracle [unqualified]** R Catalog association type metadata, source procedures, and security group names clsAssociationTypeCode, clsAssociationTypeCodeCollection
BUSINESS_AFFILIATE Oracle [unqualified]** R Link policies to active affiliates for officer-of-notice lookup clsPolicyAddress
CANCELLATION Oracle [unqualified]** R Retrieve latest unreinstated future cancellation date clsPolicyData
COMBO_POLICY_PERIOD Oracle [unqualified]** R Resolve discount-combination policy memberships by combo period PolicyAssociationList
ENDORSEMENT Oracle [unqualified]** R Identify issued periods with printed endorsements clsAssociationTypeCode
ER_COMBINATION_POLICY Oracle [unqualified]** R Resolve experience-rating combination memberships PolicyAssociationList
NON_EMPR_ACCT Oracle [unqualified]** R Retrieve non-employer account identity and address details clsNonEmployerAccount
PA_AUDIT Oracle [unqualified]** R Evaluate audit access windows and retrieve latest final audit date clsPolicyData, clsPolicySecurity
PA_AUDITOR Oracle [unqualified]** R Match auditor vendor numbers to audits clsPolicySecurity
PA_POLICY_PERIOD Oracle [unqualified]** R Map policies to audit sequences for auditor authorization clsPolicySecurity
POLICY Oracle [unqualified]** R Resolve policy relationship rows, names, and rate-account parentage PolicyAssociationList, clsPolicySecurity
POLICY_PERIOD Oracle [unqualified]** R Resolve active combo periods and maximum effective dates PolicyAssociationList
POLICY_PERIOD_ALL Oracle [unqualified]** R Resolve latest issued policy periods across all policy-period rows clsAssociationTypeCode
POLICY_PERIOD_NON_WCIS Oracle [unqualified]** R Supply issued non-WCIS periods for period-date derivation clsAssociationTypeCode
POLICY_RELATIONSHIP Oracle [unqualified]** R Resolve manual parent/member policy associations PolicyAssociationList
TRADING_AS Oracle [unqualified]** R Retrieve trading-as names by policy or trading sequence clsTradingAs, clsTradingAsCollection

affiliate

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Resolve the affiliate person's name for the officer-of-notice string returned with policy address data.
Access pattern direct SQL
CRUD operations Read
Key columns used AFFILIATE_SSN, AFFILIATE_FIRST_NAME, AFFILIATE_INITIAL, AFFILIATE_LAST_NAME
Sequence N/A
Related tables BUSINESS_AFFILIATE
Business objects clsPolicyAddress

Operations referencing this table

Business rules referencing this table

SQL referencing this table

association_type_code

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Supply association-type descriptions, category codes, source procedure names, sort order, and optional security group names for policy association features.
Access pattern direct SQL and stored procedure indirection
CRUD operations Read
Key columns used CODE, CODE_DESCR, ASSOCIATION_TYPE_CATEGORY_CODE, SOURCE_PROCEDURE_NAME, SECURITY_GROUP_NAME, SORT_ORDER, POLICY_HEADER_DEFAULT_FLAG
Sequence N/A
Related tables POLICY_PERIOD_ALL, ENDORSEMENT, POLICY_PERIOD_NON_WCIS
Business objects clsAssociationTypeCode, clsAssociationTypeCodeCollection

Business rules referencing this table

SQL referencing this table

business_affiliate

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Link the policy to an active officer-of-notice affiliate row.
Access pattern direct SQL
CRUD operations Read
Key columns used POLICY_NUM, OFFICER_OF_NOTICE_FLAG, AFFILIATE_CANCEL_DATE, AFFILIATE_SSN
Sequence N/A
Related tables AFFILIATE
Business objects clsPolicyAddress

Operations referencing this table

Business rules referencing this table

SQL referencing this table

cancellation

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Return the latest unreinstated, unrescinded cancellation date for a policy.
Access pattern direct SQL
CRUD operations Read
Key columns used POLICY_NUM, CANCELLATION_SEQ_NUM, CANCEL_DATE, REINSTATE_DATE, RESCIND_DATE
Sequence N/A
Related tables None identified
Business objects clsPolicyData

Operations referencing this table

Business rules referencing this table

SQL referencing this table

combo_policy_period

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Join a policy's active combination-period membership to sibling members in discount-combination association retrieval.
Access pattern direct SQL
CRUD operations Read
Key columns used POLICY_NUM, ACTIVE_MEMBER_FLAG, POLICY_PERIOD_EFF_DATE, COMBO_PERIOD_SEQ_NUM, MIN_PREMIUM_MEMBER_FLAG
Sequence N/A
Related tables POLICY, POLICY_PERIOD
Business objects PolicyAssociationList

Operations referencing this table

Business rules referencing this table

SQL referencing this table

endorsement

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Identify issued policy periods by printed P401 endorsement when deriving association header dates.
Access pattern direct SQL
CRUD operations Read
Key columns used POLICY_PERIOD_EFF_DATE, POLICY_NUM, ENDORSE_CODE, PRINTED_DATE
Sequence N/A
Related tables POLICY_PERIOD_ALL, POLICY_PERIOD_NON_WCIS
Business objects clsAssociationTypeCode

SQL referencing this table

er_combination_policy

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Resolve experience-rating combination members effective on the requested association date.
Access pattern direct SQL
CRUD operations Read
Key columns used POLICY_NUM, ER_COMBINATION_SEQ_NUM, EFF_DATE, EXP_DATE
Sequence N/A
Related tables POLICY
Business objects PolicyAssociationList

Operations referencing this table

Business rules referencing this table

SQL referencing this table

non_empr_acct

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Retrieve non-employer account master and mailing fields by account number.
Access pattern direct SQL
CRUD operations Read
Key columns used ACCT_NUM, ACCT_NAM, ADDR_LINE1, ADDR_LINE2, CITY_NAM, STATE_CD, ZIP_CD, ZIP_CD_EXT, PHONE_NUM
Sequence N/A
Related tables None identified
Business objects clsNonEmployerAccount

SQL referencing this table

pa_audit

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Supply audit completion/cancellation dates and audit identity for final-audit display and auditor authorization checks.
Access pattern direct SQL
CRUD operations Read
Key columns used POLICY_NUM, PA_TYPE_CODE, PA_STATUS_CODE, END_DATE, PA_SLCTN_CATEGORY_GROUP_CODE, PA_AUDIT_SEQ_NUM, PA_REVISES_SEQ_NUM, PA_AUDITOR_ID, CANCEL_DATE, COMPLETED_DATE
Sequence N/A
Related tables PA_AUDITOR, PA_POLICY_PERIOD, POLICY
Business objects clsPolicyData, clsPolicySecurity

Operations referencing this table

Business rules referencing this table

SQL referencing this table

pa_auditOR

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Match an auditor vendor number to authorized audit assignments.
Access pattern direct SQL
CRUD operations Read
Key columns used PA_AUDITOR_ID, VENDOR_NUM
Sequence N/A
Related tables PA_AUDIT
Business objects clsPolicySecurity

Operations referencing this table

Business rules referencing this table

SQL referencing this table

pa_policy_period

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Resolve the audit sequence numbers associated with the requested policy during auditor access checks.
Access pattern direct SQL
CRUD operations Read
Key columns used PA_AUDIT_SEQ_NUM, POLICY_NUM
Sequence N/A
Related tables PA_AUDIT
Business objects clsPolicySecurity

Operations referencing this table

Business rules referencing this table

SQL referencing this table

policy

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Supply policy relationship parentage, combination account linkage, and policy names for association and auditor-list queries.
Access pattern direct SQL
CRUD operations Read
Key columns used POLICY_NUM, POLICY_RELATIONSHIP_CODE, POLICY_RELATIONSHIP_POLICY_NUM, RATE_ACCOUNT, POLICY_NAME
Sequence N/A
Related tables POLICY_RELATIONSHIP, COMBO_POLICY_PERIOD, ER_COMBINATION_POLICY, PA_AUDIT
Business objects PolicyAssociationList, clsPolicySecurity

Operations referencing this table

Business rules referencing this table

SQL referencing this table

policy_PERIOD

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Supply active policy-period windows for discount combinations and the maximum policy effective date for default association-date logic.
Access pattern direct SQL
CRUD operations Read
Key columns used POLICY_NUM, POLICY_PERIOD_EFF_DATE, POLICY_PERIOD_EXP_DATE
Sequence N/A
Related tables COMBO_POLICY_PERIOD
Business objects PolicyAssociationList

Operations referencing this table

Business rules referencing this table

SQL referencing this table

policy_PERIOD_ALL

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Provide all policy-period rows used to derive the latest issued period per policy for association headers.
Access pattern direct SQL
CRUD operations Read
Key columns used POLICY_NUM, POLICY_PERIOD_EFF_DATE, CANCEL_DATE, POLICY_PERIOD_EXP_DATE
Sequence N/A
Related tables ENDORSEMENT, POLICY_PERIOD_NON_WCIS
Business objects clsAssociationTypeCode

SQL referencing this table

policy_PERIOD_NON_WCIS

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Contribute issued non-WCIS policy periods when deriving association header date ranges.
Access pattern direct SQL
CRUD operations Read
Key columns used POLICY_PERIOD_EFF_DATE, POLICY_NUM, POLICY_PERIOD_STATUS_CODE
Sequence N/A
Related tables POLICY_PERIOD_ALL, ENDORSEMENT
Business objects clsAssociationTypeCode

SQL referencing this table

policy_RELATIONSHIP

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Resolve manual parent/member policy associations and affiliate-type codes.
Access pattern direct SQL
CRUD operations Read
Key columns used MBR_POLICY_NUM, PARENT_POLICY_NUM, AFFIL_TYP_CD
Sequence N/A
Related tables POLICY
Business objects PolicyAssociationList

Operations referencing this table

Business rules referencing this table

SQL referencing this table

trading_as

Field Value
Database Oracle
Schema/Link [unqualified]**
Purpose Retrieve trading-as rows for either a single trading sequence or all rows for a policy.
Access pattern direct SQL
CRUD operations Read
Key columns used TRADING_SEQ_NUM, POLICY_NUM, BUS_SEQ_NUM, TRADING_AS_NAME, TRADING_AS_TYPE_CODE
Sequence N/A
Related tables None identified
Business objects clsTradingAs, clsTradingAsCollection

SQL referencing this table

SQL Server BDS

BDS access in this library is entirely opaque stored-procedure based. The source code constructs a SQL Server clsSQLDataLayer at runtime from BDS_USER, bdsDatabase, and BDS_DATABASENAME, then executes cp_policy_in_group, cp_claimprofile_get_group_policy_list, cp_claimprofile_get_sc_book, cp_get_agent_online_policy_list, and cp_external_client_lookup. No SQL Server base-table names are referenced anywhere in compiled source, so no BDS table detail sections can be documented from repository evidence alone.

Table relationship diagram

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

erDiagram
    BUSINESS_AFFILIATE }o--|| AFFILIATE : "affiliate_ssn"
    POLICY_RELATIONSHIP }o--|| POLICY : "parent_policy_num"
    COMBO_POLICY_PERIOD }o--|| POLICY_PERIOD : "policy_num, policy_period_eff_date"
    COMBO_POLICY_PERIOD }o--|| POLICY : "policy_num"
    ER_COMBINATION_POLICY }o--|| POLICY : "policy_num"
    PA_AUDIT }o--|| PA_AUDITOR : "pa_auditor_id"
    PA_POLICY_PERIOD }o--|| PA_AUDIT : "pa_audit_seq_num"
    PA_AUDIT }o--|| POLICY : "policy_num"

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

  • SAIF.Business.View.Policy/Associations/clsAssociationTypeCode.vb
  • SAIF.Business.View.Policy/Associations/clsAssociationTypeCodeCollection.vb
  • SAIF.Business.View.Policy/clsNonEmployerAccount.vb
  • SAIF.Business.View.Policy/clsPolicyAddress.vb
  • SAIF.Business.View.Policy/clsPolicyData.vb
  • SAIF.Business.View.Policy/clsPolicySecurity.vb
  • SAIF.Business.View.Policy/clsTradingAs.vb
  • SAIF.Business.View.Policy/clsTradingAsCollection.vb
  • SAIF.Business.View.Policy/PolicyAssociations.vb