Core_PentegraCensusView
Purpose
This report generates the Pentegra census data required for retirement plan compliance and reporting. It provides a comprehensive view of employee compensation, benefits, and plan eligibility for the specified year.
Data Sources
- Employee Records:
Core.Employeetable - Employment History:
Position.ServiceHistorytable - Payroll Earnings:
Payroll.Earningstable - Benefits Data:
Payroll.PayrollBenefitstable - Payroll Transactions:
Payroll.PayrollTransactiontable
Key Business Rules
Employee Inclusion
Employees are included when they have any taxable earnings (IsTaxable = 1) in the specified year. This includes:
- Active employees with earnings
- Terminated employees who had earnings during the year
- Part-time, full-time, and adjunct employees
- Student workers with earnings
Hours Classification
Hours are determined by plan rules:
- Phased Retirement: Always 1,000 hours
- Full-time/30+ weekly hours: 1,000 hours
- Part-time (10-29 weekly hours): 750 hours
- Adjunct faculty: 250 hours (minimum)
- Actual payroll hours: Used when available, with plan rule thresholds applied
Field Definitions and Sources
Employee Identification
- First Name, Last Name, SSN: Direct from
Core.Employee - Date of Birth:
Core.Employee.BirthDateformatted MM/dd/yyyy - Original Date of Hire:
Core.Employee.OriginalHireDate - Date of Separation:
Core.Employee.TerminationDate(not from service history) - Date of Rehire: Latest rehire date from
Position.ServiceHistoryafter any separation
Employment Status
Derived from termination date and current employment description:
- T: Terminated during census year
- R: Retired
- P: Disabled
- D: Deceased
- LOA: On leave
- Blank: Active during year
Hours and Compensation Fields
Hours
- Source: Calculated from
Payroll.Earnings.Hoursand employment rules - Logic: Multi-branch CASE statement applying IWU plan classification rules
- Thresholds: 1000 (full participation), 750 (voluntary), 250 (minimum)
Gross Compensation
- Source:
Payroll.PayrollTransaction.YTDGrossPay - Logic: Latest record in year (ROW_NUMBER() rn=1)
- Note: Uses gross pay as required by Pentegra, not W-2 Box 1 amounts
Pre-Entry Compensation
- Value: Always $0.00
- Purpose: Compensation prior to being eligible for employer contributions
Pre-Tax Health/125
- Source:
Payroll.PayrollBenefitstable - Types: Medical, Dental, Vision, Flexible Spending
- Amount: Sum of employee contributions for the year
Excluded Compensation
- Formula:
YTDGrossPay - RegularPay - RegularPay: Sum of earnings where
EarningsCode = 'REG'only - Business Rule: Gross Wages minus Base Salary equals Excluded Compensation
- Data Source: Direct from payroll transaction data using the simplified formula
Severance Compensation
- Source:
Payroll.Earningstable - Components:
SeverancePay:IsTerminationPay = 1 AND IsAccrualPayout = 0ReimbursementPay:IsReimbursement = 1
- Note: APO (accrual payouts) are NOT considered severance
Retirement Contributions
Pre-Tax Salary Deferral
- Source:
Payroll.PayrollBenefitstable - Codes:
('TCAA','TCAH','TCPA','TCPH','TCFA','TCFH','TADJ','WPAA','WPAH','WPFA','WPFH','WPPA','WPPH') - Amount: Sum of employee contributions for the year
Roth Salary Deferral
- Value: Always $0.00
- Reason: Not available in IWU's retirement plan
Mandatory EE Contribution
- Value: Always $0.00
- Reason: IWU does not have mandatory employee contributions
Employer Match
- Source:
Payroll.PayrollBenefitstable - Codes:
('TCAA','TCAH','WPAA','WPAH') - Amount: Sum of employer matching contributions for the year
Employer Contribution (Non-Elective)
- Source:
Payroll.PayrollBenefitstable - Codes:
('TCER','WPER') - Amount: Sum of employer non-elective contributions for the year
Other Contribution
- Value: Always $0.00
- Reason: Not used by IWU
Loan Repayments
- Value: Always $0.00
- Reason: Not tracked in current system
Additional Information
Company Division
- Source:
CurrentEmployment.Divisionfrom service history - Purpose: Organizational division for reporting
Active Military
- Value: Always 0
- Note: Would need manual override if needed
Union Employee
- Value: Always 0
- Reason: IWU employees are not unionized
Job Classification
- Logic:
'U'only ifIsStudentWorker = 1AND zero retirement contributions - Data Source:
Payroll.Earningsfor student employment flag,Payroll.PayrollBenefitsfor contribution amounts - Purpose: Identifies ineligible employees for plan purposes
Note
- Source:
SeparationRehireDates.CurrentServiceYears - Logic: Bucketed service years as text (Less than 5, 5+, 10+, 15+, 20+, 25+, 30+)
- Purpose: Service milestone recognition
Census Year
- Value: The
@Yearparameter passed to the function - Default: Previous calendar year in the public view
Data Processing Notes
Earnings Classification
The Payroll.Earnings table uses these key flags:
IsRegularPay: Only REG earnings (base salary)IsTaxable: All earnings are taxable for income tax purposesIsRetirement: REG + VAC + SICK + HOL + AVAC + REGA (retirement eligible)IsStudentEmployment: CWS, IWU earnings codes
Payroll Transaction Processing
- YTDGrossPay: Cumulative gross pay through the pay date
- YTDFederalTaxableEarnings: W-2 Box 1 amounts (not used for Pentegra)
- Latest Record: Uses ROW_NUMBER() to get most recent transaction per employee per year
Data Quality Considerations
Earnings Data
- Negative amounts may exist due to payroll adjustments/corrections
- All earnings types are included in gross pay calculations
- REG earnings represent base salary only
Benefits Data
- Employee and employer contributions are summed for the year
- Missing employer contributions may trigger 4% imputation rules
- Benefit codes determine contribution types and eligibility
Employment History
- Service history provides employment timeline
- Current employment snapshot reflects status during census year
- Termination dates come from Employee table, not service history
Troubleshooting Common Issues
Missing Employees
- Check
Payroll.Earningsfor taxable earnings in the year - Verify employee is active in
Core.Employeetable - Confirm earnings have
IsTaxable = 1flag
Incorrect Compensation Amounts
- Verify
Payroll.PayrollTransaction.YTDGrossPayvalues - Check
Payroll.Earnings.RegularPaycalculations - Ensure all earnings types are being loaded correctly
Benefits Discrepancies
- Verify benefit codes in
Payroll.PayrollBenefits - Check employee and employer contribution calculations
- Confirm 4% imputation rules are applied correctly