Core_PentegraCensusView
Core.PentegraCensusView Documentation
Purpose
The Core.PentegraCensusView provides a consolidated view for Pentegra census reporting, combining employee demographic, employment, payroll, and benefits data. It is designed to support annual census submissions and compliance reporting for retirement and benefits plans, including all employees with W-2 income in the previous year and their complete employment history.
View Structure
| Field Name | Data Type | Description | Source(s) | Transformation Notes |
|---|---|---|---|---|
| First Name | VARCHAR(100) | Employee first name | Core.Employee | Direct mapping |
| Last Name | VARCHAR(100) | Employee last name | Core.Employee | Direct mapping |
| SSN | VARCHAR(11) | Social Security Number | Core.Employee | Direct mapping |
| Gender | VARCHAR(10) | Gender (Male/Female) | Core.Employee | Mapped from code |
| Date of Birth | DATE | Employee birth date | Core.Employee | Formatted as MM/dd/yyyy |
| Original Date of Hire | DATE | Original hire date | Core.Employee | Formatted as MM/dd/yyyy |
| Date of Separation | DATE | Last separation date | Position.ServiceHistory | Derived from EventType 'Termination' |
| Date of Rehire | DATE | Last rehire date | Position.ServiceHistory | Derived from EventType 'Rehire' |
| Employment Status | VARCHAR(10) | Employment status code (A, T, R, P, D, LOA) | Core.Employee, Position.ServiceHistory | Derived from status fields |
| Hours | INT | Annualized hours (capped at 1000) | Position.ServiceHistory | Calculated from WeeklyHours * 52 |
| Gross Compensation | DECIMAL(16,2) | Annual salary | Position.ServiceHistory | Direct mapping |
| Pre-Entry Compensation | DECIMAL(16,2) | Severance pay | Payroll.PayrollDetail | Summed for previous year |
| Pre-Tax Health/125 | DECIMAL(16,2) | Total pre-tax health contributions | Payroll.PayrollBenefits | Summed for previous year |
| Excluded Compensation | DECIMAL(16,2) | Bonus pay or adjunct salary | Payroll.PayrollDetail, Position.ServiceHistory | Conditional logic |
| Severance Compensation | DECIMAL(16,2) | Severance pay | Payroll.PayrollDetail | Summed for previous year |
| Pre-Tax Salary Deferral | DECIMAL(16,2) | Employee retirement contributions | Payroll.PayrollBenefits | Summed for previous year |
| Roth Salary Deferral | DECIMAL(16,2) | Roth contributions (always 0 for IWU) | N/A | Set to 0 |
| Mandatory EE Contribution | DECIMAL(16,2) | Mandatory employee contribution (fixed at 4%) | N/A | Set to 0.04 |
| Employer Match | DECIMAL(16,2) | Employer match contributions | Payroll.PayrollBenefits | Summed for previous year |
| Employer Contribution | DECIMAL(16,2) | Employer retirement contributions | Payroll.PayrollBenefits | Summed for previous year |
| Other Contribution | DECIMAL(16,2) | Other contributions (always 0) | N/A | Set to 0 |
| Loan Repayments | DECIMAL(16,2) | Loan repayments (always 0) | N/A | Set to 0 |
| Company Division | VARCHAR(100) | Division/Company | Position.ServiceHistory | Direct mapping |
| Active Military | BIT | Active military status (always 0) | N/A | Set to 0 |
| Union Employee | BIT | Union employee status (always 0) | N/A | Set to 0 |
| Job Classification | VARCHAR(100) | Job title | Position.ServiceHistory | Direct mapping |
| Note | VARCHAR(50) | Years of service band | Position.ServiceHistory | Derived from CurrentServiceYears |
Source System Mapping
- Core.Employee: Demographic and employment data
- Position.ServiceHistory: Employment events, service years, job details
- Payroll.PayrollDetail: Payroll line items (regular, overtime, bonus, severance)
- Payroll.PayrollBenefits: Benefit and retirement contributions
Data Transformation Rules
- Employment Status: Derived from Core.Employee and ServiceHistory, mapped to codes (A, T, R, P, D, LOA)
- Hours: Calculated as WeeklyHours * 52, capped at 1000
- Compensation Fields: Summed for the previous year, with conditional logic for adjuncts and bonuses
- Contribution Fields: Summed by type for the previous year
- Note: Service years banded into categories (e.g., '5+ Years of Service')
- Static Fields: Some fields are set to 0 or fixed values as required by Pentegra
Data Quality Rules
- Required Fields: First Name, Last Name, SSN, Date of Birth, Employment Status, Hours, Gross Compensation
- Validation: Ensure all included employees have W-2 income in the previous year
- Date Ranges: All payroll and benefit data limited to the previous calendar year
- Referential Integrity: All joins must match valid EmployeeIDs
Usage Notes
- Reporting: Used for annual Pentegra census submission and compliance
- Filtering: Includes only employees active or with payroll/service events in the previous year
- Performance: Indexed on EmployeeID for efficient lookups
- Integration: Can be exported for external reporting or compliance
Maintenance
- Refresh Schedule: Updated annually after year-end payroll is finalized
- Change Management: Update logic if Pentegra requirements change
- Monitoring: Validate record counts and totals after each refresh
- Documentation: View definition is documented with an extended property in the database