SAIF.Business.View.Provider database tables

This page catalogs database tables accessed by this library. All access is read-only (BR-DATA-001); no table listed below is ever created, updated, or deleted through this library.

Table catalog

Table	Database	Schema/Link	CRUD	Purpose	Primary business object
PROVIDER	Oracle	[unqualified]**	R	Provider aggregate root keyed by PROVIDER_SEQ_NUM	clsProvider
PROVIDER_TAX_ID	Oracle	[unqualified]**	R	Tax-ID registrations for a provider, incl. W-9 dates and address	clsProviderTaxID
PROVIDER_LOCATION	Oracle	[unqualified]**	R	Provider location (firm or person site)	clsProviderLocation
PROVIDER_ADDRESS	Oracle	[unqualified]**	R	Provider-location address rows with effective/expiry dates	clsProviderAddress
PROVIDER_INELIG_PERIOD	Oracle	[unqualified]**	R	Ineligibility windows for a provider location	clsProviderIneligibilityPeriod
PROVIDER_RELATIONSHIP	Oracle	[unqualified]**	R	Parent/child provider-location relationships	clsProviderRelationship
PROVIDER_TYPE_CODE	Oracle	[unqualified]**	R	Provider type code lookup	clsProviderType
PROVIDER_DCBS_COST_CHARGE	Oracle	[unqualified]**	R	DCBS cost-to-charge factors with date window	clsDCBSCostCharge
MCO_PROVIDER	Oracle	[unqualified]**	R	MCO plan enrollments for a location	clsMCOProvider
PROVIDER_SERVICE	Oracle	[unqualified]**	R	Provider service-code assignments	clsProviderServiceInfo
PROVIDER_SERVICE_CODE	Oracle	[unqualified]**	R	Service code lookup joined to PROVIDER_SERVICE	clsProviderServiceInfo
PROVIDER_SERVICE_CONTRACT	Oracle	[unqualified]**	R	Provider service contracts	clsProviderServiceContractInfo
PROVIDER_IDENTITY	Oracle	[unqualified]**	R	Alternate provider identity values used by search	clsProviderSearch (search-only, no dedicated business object)

PROVIDER

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Provider aggregate root, retrieved by ID, primary tax ID, or as a set
Access pattern	Portal/criteria (clsProvider.Criteria, clsProviderCollection.Criteria)
CRUD operations	Read
Key columns used	PROVIDER_SEQ_NUM, PRIMARY_TAX_ID
Sequence	N/A
Related tables	PROVIDER_TAX_ID
Business objects	clsProvider, clsProviderCollection

Operations referencing this table

• Retrieve a provider by ID or primary tax ID
• Retrieve a set of providers

Business rules referencing this table

• BR-PRV-001: Primary tax ID must be a 9-digit number
• BR-PRV-003: Provider-set retrieval uses unparameterized SQL
• BR-PRV-004: Providers retrieved via factory methods are read-only

SQL referencing this table

• SQL-PRV-01: Retrieve provider by ID or tax ID
• SQL-PRV-01b: Retrieve provider by primary tax ID
• SQL-PRV-02: Retrieve provider collection by tax-ID list
• SQL-PRV-02b: Retrieve provider collection by caller WHERE clause

---

PROVIDER_TAX_ID

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Tax-ID registrations for a provider, used for payee-address resolution and search matching
Access pattern	Portal/criteria (clsProviderTaxIDCollection.Criteria); left-joined from clsProvider.Criteria; joined/unioned in search
CRUD operations	Read
Key columns used	TAX_ID, PROVIDER_SEQ_NUM
Sequence	N/A
Related tables	PROVIDER, PROVIDER_LOCATION
Business objects	clsProviderTaxID, clsProviderTaxIDCollection

Operations referencing this table

• Retrieve a provider by ID or primary tax ID
• Load provider tax IDs
• Search for provider locations
• Retrieve a set of providers

Business rules referencing this table

• BR-PRV-002: Payee address resolution order
• BR-SRCH-005: Provider name search also matches registered names

SQL referencing this table

• SQL-PRV-01: Retrieve provider by ID or tax ID
• SQL-PRV-01b: Retrieve provider by primary tax ID
• SQL-PTAX-01: Retrieve provider tax IDs
• SQL-SRCH-01: Provider search

---

PROVIDER_LOCATION

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Provider location aggregate root; second retrieval root that back-navigates to its Provider
Access pattern	Portal/criteria (clsProviderLocation.Criteria, clsProviderLocationCollection.Criteria); joined from address, relationship, and search queries
CRUD operations	Read
Key columns used	PROVIDER_LOCATION_SEQ_NUM, PROVIDER_SEQ_NUM, PROVIDER_TYPE_CODE
Sequence	N/A
Related tables	PROVIDER, PROVIDER_ADDRESS, PROVIDER_RELATIONSHIP, PROVIDER_TYPE_CODE, PROVIDER_IDENTITY, PROVIDER_SERVICE, MCO_PROVIDER, PROVIDER_INELIG_PERIOD, PROVIDER_DCBS_COST_CHARGE, PROVIDER_SERVICE_CONTRACT
Business objects	clsProviderLocation, clsProviderLocationCollection

Operations referencing this table

• Retrieve a provider location and its lazily-loaded graph
• Retrieve provider locations by provider or by provider type
• Load and select a provider-location address
• Load provider relationships (parents or children)
• Search for provider locations

Business rules referencing this table

• BR-PRV-006: Primary location selection
• BR-PLOC-001: Provider location ID must be positive
• BR-PLOC-002: Provider ID must be positive
• BR-PLOC-003: Provider type code must be non-empty
• BR-PLOC-004: Ineligible locations are excluded from filtered lists
• BR-SRCH-004: Business/person selector maps to business flag
• BR-SRCH-007: Search results capped at 300 rows

SQL referencing this table

• SQL-PLOC-01: Provider location by ID
• SQL-PLOC-02: Locations by provider
• SQL-PLOC-03: Locations by provider type
• SQL-PADR-01: Provider addresses
• SQL-PREL-01: Child relationships
• SQL-PREL-02: Parent relationships
• SQL-SRCH-01: Provider search

---

PROVIDER_ADDRESS

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Address rows for a provider location, with effective/termination dates and address type
Access pattern	Portal/criteria (clsProviderAddress.RETRIEVE_SQL, left join from PROVIDER_LOCATION); joined in search
CRUD operations	Read
Key columns used	PROVIDER_LOCATION_SEQ_NUM
Sequence	N/A
Related tables	PROVIDER_LOCATION
Business objects	clsProviderAddress, clsProviderAddressCollection

Operations referencing this table

• Load and select a provider-location address
• Search for provider locations

Business rules referencing this table

• BR-PADR-001: Addresses without expiration default to Date.MaxValue
• BR-PADR-002: Address active-on-date determination

SQL referencing this table

• SQL-PADR-01: Provider addresses
• SQL-SRCH-01: Provider search

---

PROVIDER_INELIG_PERIOD

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Ineligibility windows for a provider location
Access pattern	Portal/criteria (clsProviderIneligibilityPeriod.Criteria, clsProviderIneligibilityPeriodCollection.Criteria)
CRUD operations	Read
Key columns used	PROVIDER_INELIG_PERIOD_SEQ_NUM, PROVIDER_LOCATION_SEQ_NUM
Sequence	N/A
Related tables	PROVIDER_LOCATION
Business objects	clsProviderIneligibilityPeriod, clsProviderIneligibilityPeriodCollection

Operations referencing this table

• Load provider ineligibility periods

Business rules referencing this table

• BR-PINE-001: Ineligibility periods without termination default to Date.MaxValue
• BR-PLOC-004: Ineligible locations are excluded from filtered lists

SQL referencing this table

• SQL-PINE-01: Ineligibility period by ID
• SQL-PINE-02: Ineligibility periods by location

---

PROVIDER_RELATIONSHIP

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Parent/child relationships between provider locations (self-referencing hierarchy)
Access pattern	Portal/criteria (clsProviderRelationshipCollection.Criteria, both directions); no standalone single-relationship retrieval
CRUD operations	Read
Key columns used	PROVIDER_LOCATION_SEQ_NUM, PARENT_PROVIDER_LOC_SEQ_NUM
Sequence	N/A
Related tables	PROVIDER_LOCATION
Business objects	clsProviderRelationship, clsProviderRelationshipCollection

Operations referencing this table

• Load provider relationships (parents or children)

Business rules referencing this table

• BR-PREL-001: Works-for relationships are WRK code
• BR-PREL-002: Works-for defaults to active relationships only
• BR-PREL-003: Relationship active-date determination
• BR-PREL-004: Destination relationship type is the opposite of the source

SQL referencing this table

• SQL-PREL-01: Child relationships
• SQL-PREL-02: Parent relationships

---

PROVIDER_TYPE_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Provider type code lookup table
Access pattern	Portal/criteria (clsProviderType.Criteria, clsProviderTypeCollection.Criteria); joined in search
CRUD operations	Read
Key columns used	CODE
Sequence	N/A
Related tables	PROVIDER_LOCATION
Business objects	clsProviderType, clsProviderTypeCollection

Operations referencing this table

• Load a provider type / all provider types
• Search for provider locations

Business rules referencing this table

• BR-PLOC-003: Provider type code must be non-empty

SQL referencing this table

• SQL-PTYP-01: Provider type by code
• SQL-PTYP-02: All provider types
• SQL-SRCH-01: Provider search

---

PROVIDER_DCBS_COST_CHARGE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	DCBS cost-to-charge factors with a date window, for a provider location
Access pattern	Direct-query (clsDCBSCostChargeList constructor via SqlHelper.ResultSetQuery)
CRUD operations	Read
Key columns used	PROVIDER_LOCATION_SEQ_NUM
Sequence	N/A
Related tables	PROVIDER_LOCATION (inferred*)
Business objects	clsDCBSCostCharge, clsDCBSCostChargeList

Operations referencing this table

• Load DCBS cost-charge factors

Business rules referencing this table

• BR-DATE-001: Dated records are in effect within their date window

SQL referencing this table

• SQL-DCBS-01: DCBS cost charges

---

MCO_PROVIDER

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	MCO plan enrollments for a provider location
Access pattern	Direct-query (clsMCOProviderList constructor via SqlHelper.ResultSetQuery); optional join in search
CRUD operations	Read
Key columns used	PROVIDER_LOCATION_SEQ_NUM, MCO_PLAN_ID
Sequence	N/A
Related tables	PROVIDER_LOCATION
Business objects	clsMCOProvider, clsMCOProviderList

Operations referencing this table

• Load MCO enrollments
• Search for provider locations

Business rules referencing this table

• BR-MCO-001: MCO plan must match the assigned plan ID
• BR-DATE-001: Dated records are in effect within their date window

SQL referencing this table

• SQL-MCO-01: MCO provider plans
• SQL-SRCH-01: Provider search

---

PROVIDER_SERVICE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Provider service-code assignments for a provider location
Access pattern	Direct-query (clsProviderServiceList constructor, joined to PROVIDER_SERVICE_CODE); optional join in search
CRUD operations	Read
Key columns used	PROVIDER_LOCATION_SEQ_NUM, PROVIDER_SERVICE_CODE
Sequence	N/A
Related tables	PROVIDER_SERVICE_CODE, PROVIDER_LOCATION
Business objects	clsProviderServiceInfo, clsProviderServiceList

Operations referencing this table

• Load provider services and service contracts
• Search for provider locations

SQL referencing this table

• SQL-PSVC-01: Provider services
• SQL-SRCH-01: Provider search

---

PROVIDER_SERVICE_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Service code lookup joined to PROVIDER_SERVICE assignments
Access pattern	Direct-query (clsProviderServiceList constructor, comma join)
CRUD operations	Read
Key columns used	CODE
Sequence	N/A
Related tables	PROVIDER_SERVICE
Business objects	clsProviderServiceInfo, clsProviderServiceList

Operations referencing this table

• Load provider services and service contracts

SQL referencing this table

• SQL-PSVC-01: Provider services

---

PROVIDER_SERVICE_CONTRACT

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Provider service contracts for a provider location, restricted by an expiry offset window
Access pattern	Direct-query (clsProviderServiceContractList constructor via SqlHelper.ResultSetQuery)
CRUD operations	Read
Key columns used	PROVIDER_LOCATION_SEQ_NUM, eff_date, exp_date
Sequence	N/A
Related tables	PROVIDER_LOCATION (inferred*)
Business objects	clsProviderServiceContractInfo, clsProviderServiceContractList

Operations referencing this table

• Load provider services and service contracts

Business rules referencing this table

• BR-PSVC-001: Service contracts are limited to the offset window
• BR-MCO-001: MCO plan must match the assigned plan ID
• BR-DATE-001: Dated records are in effect within their date window

SQL referencing this table

• SQL-PSVC-02: Provider service contracts

---

PROVIDER_IDENTITY

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Alternate provider identity values, matched by the "other ID" search field
Access pattern	Direct-query, optional join (clsProviderSearch.searchForProvider)
CRUD operations	Read
Key columns used	PROVIDER_LOCATION_SEQ_NUM, IDENTITY_VALUE
Sequence	N/A
Related tables	PROVIDER_LOCATION
Business objects	clsProviderSearch (search-only; no dedicated business object)

Operations referencing this table

• Search for provider locations

SQL referencing this table

• SQL-SRCH-01: Provider search

---

Table relationship diagram

The following diagram shows relationships between tables used by this library, derived from join and filter predicates in the SQL text (no foreign-key constraint DDL is present in the repository).**

erDiagram
    PROVIDER ||--o{ PROVIDER_TAX_ID : "PRIMARY_TAX_ID = TAX_ID"
    PROVIDER ||--o{ PROVIDER_LOCATION : "PROVIDER_SEQ_NUM (inferred)"
    PROVIDER_LOCATION ||--o{ PROVIDER_TAX_ID : "PROVIDER_SEQ_NUM"
    PROVIDER_LOCATION ||--o{ PROVIDER_ADDRESS : "PROVIDER_LOCATION_SEQ_NUM"
    PROVIDER_LOCATION ||--o{ PROVIDER_RELATIONSHIP : "PROVIDER_LOCATION_SEQ_NUM"
    PROVIDER_LOCATION ||--o{ PROVIDER_RELATIONSHIP : "PARENT_PROVIDER_LOC_SEQ_NUM (self-referencing)"
    PROVIDER_TYPE_CODE ||--o{ PROVIDER_LOCATION : "CODE = PROVIDER_TYPE_CODE"
    PROVIDER_LOCATION ||--o{ PROVIDER_IDENTITY : "PROVIDER_LOCATION_SEQ_NUM"
    PROVIDER_LOCATION ||--o{ PROVIDER_SERVICE : "PROVIDER_LOCATION_SEQ_NUM"
    PROVIDER_LOCATION ||--o{ MCO_PROVIDER : "PROVIDER_LOCATION_SEQ_NUM"
    PROVIDER_SERVICE_CODE ||--o{ PROVIDER_SERVICE : "CODE = PROVIDER_SERVICE_CODE"
    PROVIDER_LOCATION ||--o{ PROVIDER_INELIG_PERIOD : "PROVIDER_LOCATION_SEQ_NUM (inferred)"
    PROVIDER_LOCATION ||--o{ PROVIDER_DCBS_COST_CHARGE : "PROVIDER_LOCATION_SEQ_NUM (inferred)"
    PROVIDER_LOCATION ||--o{ PROVIDER_SERVICE_CONTRACT : "PROVIDER_LOCATION_SEQ_NUM (inferred)"
    PROVIDER {
        NUMBER PROVIDER_SEQ_NUM PK
        VARCHAR2 PRIMARY_TAX_ID
    }
    PROVIDER_TAX_ID {
        NUMBER PROVIDER_SEQ_NUM FK
        VARCHAR2 TAX_ID
    }
    PROVIDER_LOCATION {
        NUMBER PROVIDER_LOCATION_SEQ_NUM PK
        NUMBER PROVIDER_SEQ_NUM FK
        VARCHAR2 PROVIDER_TYPE_CODE FK
    }
    PROVIDER_ADDRESS {
        NUMBER PROVIDER_LOCATION_SEQ_NUM FK
    }
    PROVIDER_RELATIONSHIP {
        NUMBER PROVIDER_LOCATION_SEQ_NUM FK
        NUMBER PARENT_PROVIDER_LOC_SEQ_NUM FK
    }
    PROVIDER_TYPE_CODE {
        VARCHAR2 CODE PK
    }
    PROVIDER_IDENTITY {
        NUMBER PROVIDER_LOCATION_SEQ_NUM FK
    }
    PROVIDER_SERVICE {
        NUMBER PROVIDER_LOCATION_SEQ_NUM FK
        VARCHAR2 PROVIDER_SERVICE_CODE FK
    }
    MCO_PROVIDER {
        NUMBER PROVIDER_LOCATION_SEQ_NUM FK
        VARCHAR2 MCO_PLAN_ID
    }
    PROVIDER_SERVICE_CODE {
        VARCHAR2 CODE PK
    }
    PROVIDER_INELIG_PERIOD {
        NUMBER PROVIDER_LOCATION_SEQ_NUM FK
    }
    PROVIDER_DCBS_COST_CHARGE {
        NUMBER PROVIDER_LOCATION_SEQ_NUM FK
    }
    PROVIDER_SERVICE_CONTRACT {
        NUMBER PROVIDER_LOCATION_SEQ_NUM FK
    }

Related documentation

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

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.Provider/clsProvider.vb
• SAIF.Business.View.Provider/clsProviderCollection.vb
• SAIF.Business.View.Provider/clsProviderLocation.vb
• SAIF.Business.View.Provider/clsProviderLocationCollection.vb
• SAIF.Business.View.Provider/clsProviderAddress.vb
• SAIF.Business.View.Provider/clsProviderAddressCollection.vb
• SAIF.Business.View.Provider/clsProviderRelationship.vb
• SAIF.Business.View.Provider/clsProviderRelationshipCollection.vb
• SAIF.Business.View.Provider/clsProviderIneligibilityPeriod.vb
• SAIF.Business.View.Provider/clsProviderIneligibilityPeriodCollection.vb
• SAIF.Business.View.Provider/clsProviderTaxID.vb
• SAIF.Business.View.Provider/clsProviderTaxIDCollection.vb
• SAIF.Business.View.Provider/clsProviderType.vb
• SAIF.Business.View.Provider/clsProviderTypeCollection.vb
• SAIF.Business.View.Provider/clsProviderSearch.vb
• SAIF.Business.View.Provider/clsDCBSCostCharge.vb
• SAIF.Business.View.Provider/clsMCOProvider.vb
• SAIF.Business.View.Provider/clsProviderServices.vb
• SAIF.Business.View.Provider/clsProviderServiceContract.vb