Benefits_BenefitPlan

Purpose

The Benefits.BenefitPlan table stores comprehensive information about available benefit plans. It serves as the master reference for all benefit plans offered by the organization, including plan details, coverage options, provider information, and effective dates. This table is essential for benefits administration, plan management, and enrollment validation.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
BenefitPlanID	INT	NO	Primary key, unique identifier	BENEFIT_PLANS	PLAN_ID	Direct mapping
BenefitCode	VARCHAR(20)	NO	Unique plan code	BENEFIT_PLANS	PLAN_CODE	Direct mapping
BenefitType	VARCHAR(50)	NO	Type of benefit	BENEFIT_PLANS	BENEFIT_TYPE	Mapped from codes
PlanName	VARCHAR(100)	NO	Official plan name	BENEFIT_PLANS	PLAN_NAME	Direct mapping
PlanDescription	VARCHAR(500)	YES	Detailed plan description	BENEFIT_PLANS	PLAN_DESC	Direct mapping
ProviderName	VARCHAR(100)	YES	Insurance provider name	BENEFIT_PLANS	PROVIDER_NAME	Direct mapping
ProviderCode	VARCHAR(20)	YES	Provider identifier	BENEFIT_PLANS	PROVIDER_CODE	Direct mapping
EffectiveDate	DATE	NO	Plan effective date	BENEFIT_PLANS	EFFECTIVE_DATE	Direct mapping
TerminationDate	DATE	YES	Plan termination date	BENEFIT_PLANS	TERMINATION_DATE	Direct mapping
IsActive	BIT	NO	Active plan flag	BENEFIT_PLANS	IS_ACTIVE	Derived from dates
CoverageLevels	VARCHAR(200)	NO	Available coverage levels	BENEFIT_PLANS	COVERAGE_LEVELS	Mapped from codes
EmployeeCostBase	DECIMAL(12,2)	NO	Base employee cost	BENEFIT_PLANS	EMPL_COST_BASE	Direct mapping
EmployerCostBase	DECIMAL(12,2)	NO	Base employer cost	BENEFIT_PLANS	EMPR_COST_BASE	Direct mapping
CostCalculationType	VARCHAR(50)	NO	Cost calculation method	BENEFIT_PLANS	COST_CALC_TYPE	Mapped from codes
WaitingPeriod	INT	YES	Days before coverage starts	BENEFIT_PLANS	WAITING_PERIOD	Direct mapping
EligibilityRules	VARCHAR(500)	YES	Plan eligibility criteria	BENEFIT_PLANS	ELIGIBILITY_RULES	Direct mapping
CreatedDate	DATETIME	NO	Record creation date	N/A	N/A	System generated
ModifiedDate	DATETIME	NO	Last modification date	N/A	N/A	System generated

Source System Mapping

Primary Source: BENEFIT_PLANS Table

Contains benefit plan definitions
Updated when plan changes occur
Key fields mapped directly to Benefits.BenefitPlan

Secondary Sources:

BENEFIT_PROVIDERS Table
- Provider details and contact information
- Used for provider validation
- Updated when provider information changes
BENEFIT_RATES Table
- Plan-specific rates and costs
- Used for cost calculations
- Updated when rates change

Data Transformation Rules

Active Status Derivation

IsActive = CASE
  WHEN TerminationDate IS NULL OR TerminationDate > GETDATE() THEN 1
  ELSE 0
END

Benefit Type Mapping

Source Code -> Target Type
'MED' -> 'Medical'
'DEN' -> 'Dental'
'VIS' -> 'Vision'
'LIF' -> 'Life Insurance'
'DIS' -> 'Disability'
'401' -> '401k'

Coverage Level Mapping

Source Code -> Target Level
'EE' -> 'Employee Only'
'ES' -> 'Employee + Spouse'
'EC' -> 'Employee + Children'
'FAM' -> 'Family'

Cost Calculation Type Mapping

Source Code -> Target Type
'FIXED' -> 'Fixed Amount'
'PERCENT' -> 'Percentage of Salary'
'TIERED' -> 'Tiered Rate'
'COMPOSITE' -> 'Composite Rate'

Data Quality Rules

Required Fields
- BenefitPlanID
- BenefitCode
- BenefitType
- PlanName
- EffectiveDate
- IsActive
- CoverageLevels
- EmployeeCostBase
- EmployerCostBase
- CostCalculationType
Validation Rules
- BenefitCode must be unique
- EffectiveDate must be in the past
- TerminationDate must be after EffectiveDate if present
- CoverageLevels must be valid for the benefit type
- Base costs must be non-negative
Business Rules
- Active plans must have valid provider information
- Cost calculation type must be valid for the benefit type
- Coverage levels must match plan type requirements
- Waiting period must be reasonable for plan type

Usage Notes

Reporting Considerations
- Use IsActive for current plan reporting
- Consider CostCalculationType for cost analysis
- Use CoverageLevels for enrollment validation
- Track plan changes over time
Performance Considerations
- Indexed on BenefitPlanID, BenefitCode, and BenefitType
- Consider filtering on IsActive for current plans
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Links to Benefits.Enrollment via BenefitCode
- Used in benefits administration views
- Source for benefits plan dimension in data warehouse

Maintenance

Refresh Schedule
- Monthly updates for plan changes
- Full refresh quarterly
- Emergency updates for plan modifications
Archive Strategy
- Historical records maintained in table
- Use IsActive for current vs. historical filtering
- Plan history tracked for compliance
Monitoring
- Track plan counts by benefit type
- Monitor cost changes
- Alert on validation rule violations
- Track plan modifications

Benefits.BenefitPlan Table Documentation