Skip to main content
Indiana Wesleyan University Support Knowledge Base

Core_PentegraCensusView

  1. Last updated
  2. Save as PDF

Overview

The Pentegra census output is generated by the table-valued function Core.fn_PentegraCensus(@Year) and exposed via the view Core.PentegraCensusView, which calls the function for the previous calendar year (YEAR(GETDATE()) - 1). The function aggregates payroll, benefits, and employment history to produce a single row per employee for the specified year.

Inclusion Criteria

Employees are included when both of the following are true:

  • Had any taxable earnings ([Payroll].[Earnings].IsTaxable = 1) in the specified @Year
  • @Year is within the last 7 years up to the current year (i.e., @Year BETWEEN YEAR(GETDATE())-7 AND YEAR(GETDATE()))

Implementation Notes

  • Employment history comes from Position.ServiceHistory with helper CTEs for current employment and separation/rehire dates.
  • Payroll/benefit amounts are rolled up from Payroll.Earnings, Payroll.PayrollBenefits, and Payroll.PayrollTransaction.
  • The latest YTD taxable earnings per employee are taken from the most recent Payroll.PayrollTransaction record in the year.

Field Mapping and Transformations

Basic Information

  • First Name: Core.Employee.FirstName
  • Last Name: Core.Employee.LastName
  • SSN: Core.Employee.SSN
  • Gender: Mapped from Core.Employee.Gender → Male/Female/NULL
  • Date of Birth: Core.Employee.BirthDate formatted MM/dd/yyyy

Key Dates

  • Original Date of Hire: Core.Employee.OriginalHireDate formatted MM/dd/yyyy
  • Date of Separation: Core.Employee.TerminationDate formatted MM/dd/yyyy
  • Date of Rehire: Latest rehire after any separation from ServiceHistory (shown only if a separation date exists), formatted MM/dd/yyyy

Employment Status (for the specified @Year)

  • If terminated during @Year → T
  • If terminated in a different year → empty string
  • If no termination date → status derived from current employment description:
    • contains Retire → R
    • contains Disab → P
    • contains Deceas → D
    • contains Leave → LOA
    • otherwise empty string (active during the year)

Hours

Hours are determined by plan rules using payroll hours where available:

  1. Job title contains "Phased Retirement" → 1000
  2. If payroll hours exist in @Year (sum of Payroll.Earnings.Hours > 0):
    • Sum Hours ≥ 1000 → 1000
    • Sum Hours ≥ 500 → 750
    • Else → 250
  3. If no payroll hours:
    • EmploymentType = 'Full-time' OR WeeklyHours ≥ 30 → 1000
    • EmploymentType = 'Adjunct' OR JobTitle LIKE '%Adjunct%' → 250
    • EmploymentType = 'Part-time' AND WeeklyHours ≥ 10 → 750
    • Else by WeeklyHours * 52 thresholds: ≥1000 → 1000, ≥500 → 750, <500 → 250

Compensation

  • Gross Compensation: Latest Payroll.PayrollTransaction.YTDFederalTaxableEarnings for the year
  • Pre-Entry Compensation: 0.00
  • Pre-Tax Health/125: Sum of employee contributions from Payroll.PayrollBenefits for Type IN ('Medical','Dental','Vision','Flexible Spending')
  • Excluded Compensation: Sum of the following categories from Payroll.Earnings for the year, with negative category totals treated as 0:
    • Overtime (IsOvertime = 1)
    • Bonus-like (IsBonus = 1 OR IsAdditionalPay = 1)
    • Holiday (IsHolidayPay = 1)
    • Paid leave (IsVacationPay = 1 OR IsSickPay = 1) Notes: This reflects Pentegra expectations that exclusions are non-negative for reporting; source negatives (corrections) are not carried as negative exclusions.
  • Severance Compensation: SeverancePay + ReimbursementPay where:
    • SeverancePay: IsTerminationPay = 1 AND IsAccrualPayout = 0
    • ReimbursementPay: IsReimbursement = 1

Retirement Contributions

Sourced from Payroll.PayrollBenefits for the year:

  • Pre-Tax Salary Deferral: Sum of employee contributions with retirement codes ('TCAA','TCAH')
  • Roth Salary Deferral: 0.00
  • Mandatory EE Contribution: 0.00
  • Employer Match: Sum of employer contributions with retirement code ('TCAA')
  • Employer Contribution (Non-Elective): Sum of employer contributions with retirement code ('TCER')
  • Other Contribution: 0.00
  • Loan Repayments: 0.00

Additional Information

  • Company Division: Current division from employment snapshot
  • Active Military: 0
  • Union Employee: 0
  • Job Classification: 'U' if the employee has student employment earnings in the year; otherwise NULL
  • Note: Based on CurrentServiceYears bucketed as: <5, 5+, 10+, 15+, 20+, 25+, 30+
  • Census Year: @Year

Full Column Reference

Field Source Transformation/Expression Notes
First Name Core.Employee.FirstName Direct  
Last Name Core.Employee.LastName Direct  
SSN Core.Employee.SSN Direct  
Gender Core.Employee.Gender CASE WHEN 'M'→'Male', 'F'→'Female' ELSE NULL END  
Date of Birth Core.Employee.BirthDate FORMAT(..., 'MM/dd/yyyy')  
Original Date of Hire Core.Employee.OriginalHireDate FORMAT(..., 'MM/dd/yyyy')  
Date of Separation Core.Employee.TerminationDate FORMAT(..., 'MM/dd/yyyy') Uses Employee table, not service history
Date of Rehire ServiceHistory → LastRehireDate CASE WHEN TerminationDate IS NOT NULL THEN FORMAT(LastRehireDate, 'MM/dd/yyyy') END Last rehire after any separation
Employment Status Core.Employee.TerminationDate, CurrentEmployment.EmploymentStatus Multi-branch CASE per function T/R/P/D/LOA/''
Hours Payroll.Earnings.Hours, CurrentEmployment Multi-branch CASE per plan rules 1000/750/250 thresholds; phased retirement rule
Gross Compensation Payroll.PayrollTransaction.YTDFederalTaxableEarnings Latest record in year (ROW_NUMBER() rn=1), CAST(... AS DECIMAL(16,2))  
Pre-Entry Compensation Constant 0.00  
Pre-Tax Health/125 Payroll.PayrollBenefits Sum of Medical + Dental + Vision + Flexible Spending employee contributions; CAST(... AS DECIMAL(16,2)) Year filter TaxYear=@Year
Excluded Compensation Payroll.Earnings Sum of Overtime + BonusLike + Holiday + PaidLeave category totals with negatives treated as 0; CAST(... AS DECIMAL(16,2)) Categories built in PayrollData CTE
Severance Compensation Payroll.Earnings ISNULL(SeverancePay,0) + ISNULL(ReimbursementPay,0); CAST(... AS DECIMAL(16,2)) Severance excludes APO accrual payouts
Pre-Tax Salary Deferral Payroll.PayrollBenefits Sum of employee retirement contributions for BenefitCode IN ('TCAA','TCAH'); CAST(... AS DECIMAL(16,2)) Year filter TaxYear=@Year
Roth Salary Deferral Constant 0.00 Not in IWU plan
Mandatory EE Contribution Constant 0.00 Not used by IWU
Employer Match Payroll.PayrollBenefits Sum of employer contributions BenefitCode IN ('TCAA'); CAST(... AS DECIMAL(16,2)) Year filter TaxYear=@Year
Employer Contribution Payroll.PayrollBenefits Sum of employer contributions BenefitCode IN ('TCER'); CAST(... AS DECIMAL(16,2)) Non-elective
Other Contribution Constant 0.00  
Loan Repayments Constant 0.00  
Company Division CurrentEmployment.Division Direct  
Active Military Constant 0 Manual overrides if needed
Union Employee Constant 0  
Job Classification EarningsData.IsStudentWorker CASE WHEN 1 THEN 'U' ELSE NULL END Only ineligible code currently used
Note SeparationRehireDates.CurrentServiceYears Bucketed CASE to text  
Census Year Parameter @Year  

Data Sources

  • Core.Employee
  • Position.ServiceHistory (employment timeline)
  • Payroll.Earnings (detailed earnings with categorization flags)
  • Payroll.PayrollBenefits (employee/employer benefit contributions)
  • Payroll.PayrollTransaction (YTD federal taxable earnings, by pay date)

Important Business Rules

  • Only taxable earnings are considered when rolling up payroll categories.
  • Accrual payouts (APO) are not treated as severance.
  • Negative payroll adjustments that affect excluded categories are capped at 0 for the "Excluded Compensation" field to avoid negative exclusions in Pentegra reporting.
  • The view output always reflects the specified @Year; the public view binds this to the previous calendar year.

Dependencies

  • Function: Core.fn_PentegraCensus(@Year)
  • View: Core.PentegraCensusView (calls function with YEAR(GETDATE()) - 1)
  1. Back to top
  • Was this article helpful?

Recommended articles

  1. Article type
    Reference
  2. Tags
    This page has no tags.