Core_PlanSourceCensusView

Employee Inclusion

Employees are included when they have any taxable earnings (IsTaxable = 1) in the specified year AND meet the following criteria:

• Have at least one regular (non-student, non-temporary) position
• Are not terminated employees (EmploymentStatus != 'T')
• Are not student employees (EmploymentStatus != 'Student Employee')
• Do not have student email pattern (@myemail.indwes.edu) with student earnings
• Do not have temporary positions (Rank != 'TEM')

Employment Level Classification

• Full-Time (F): Only Full-Time Admin and Full-Time Faculty with EmploymentType = 'Full-time' are benefit eligible
• Part-Time (P): All others (Graduate Assistants, Part-time, Adjuncts) are not benefit eligible
• Business Rule: Graduate Assistants are always classified as Part-time regardless of FTE calculation

Organization Class (org_class) Priority

The org_class field uses a priority-based selection from primary positions:

1. ADM (Administrator) - Highest priority (Directors, Managers, Administrators)
2. FAC (Faculty) - Second priority (Professors, Instructors)
3. ADJ (Adjunct) - Third priority (Adjunct faculty, part-time instructors)
4. HLY (Hourly) - Fourth priority (Hourly employees)

Selection Logic:

• First priority: Ongoing primary positions (EndDate IS NULL)
• Second priority: Any active primary position
• Final fallback: Job title analysis for edge cases

Employee Identification

• organization_id: Always 'IWU' - IWU's organization ID in PlanSource
• first_name: Core.Employee.FirstName (max 30 characters)
• last_name: Core.Employee.LastName (max 50 characters)
• middle_name: Core.Employee.MiddleName (max 30 characters, nullable)
• name_suffix: NULL - Not tracked in IWU system
• SSN: Core.Employee.SSN formatted as 9 digits without dashes
• employee_number: Core.Employee.EmployeeNumber (Colleague ID)
• subscriber_code: Core.Employee.EmployeeNumber - API-SSO Lookup Code

Contact Information

• address_1, address_2: Core.Employee.Address1, Address2 (max 60 characters each)
• city: Core.Employee.City (max 60 characters)
• state: Core.Employee.State (state abbreviation)
• zip_code: Core.Employee.ZipCode (5 or 9 digits)
• country: Core.Employee.Country (defaults to 'US')
• home_phone: Core.Employee.HomePhone (max 30 characters)
• cell_phone: Core.Employee.MobilePhone (max 30 characters)
• work_phone: NULL - Not tracked
• email: Core.Employee.Email (IWU Email address)
• email2: NULL - Not tracked

Personal Information

• birthdate: Core.Employee.BirthDate formatted as YYYYMMDD
• gender: Core.Employee.Gender mapped to 'm', 'f', or 'u' (unknown)
• marital_status: NULL - Not tracked in IWU system
• is_smoker: NULL - Not tracked
• preferred_locale: NULL - Not tracked

Employment Dates

• hire_date: Core.Employee.HireDate formatted as YYYYMMDD
• original_hire_date: Core.Employee.OriginalHireDate formatted as YYYYMMDD
• seniority_date: NULL - Not used
• termination_date: Core.Employee.TerminationDate formatted as YYYYMMDD (if terminated)
• termination_reason_id: Core.Employee.TerminationReason (if terminated)

Employment Status and Classification

• employment_status:
  • 'A' (Active): Employee has no termination date
  • 'T' (Terminated): Employee terminated during or before the census year
• employment_level:
  • 'F' (Full-Time): Only Full-Time Admin and Full-Time Faculty with Full-time employment type
  • 'P' (Part-Time): All others (Graduate Assistants, Part-time, Adjuncts)
• pay_type:
  • 'S' (Salary): Exempt employees (ExemptStatus = 'E')
  • 'H' (Hourly): Non-exempt employees
• business_title: Position.Position.PositionTitle with fallback to Core.Employee.JobTitle or EmploymentStatus
• org_class: Priority-based selection from primary positions (ADM > FAC > ADJ > HLY)

Compensation

• current_salary: Annualized salary from Position.Position.AnnualSalary with fallback to Position.ServiceHistory.AnnualSalary
  • Source Priority:
    1. Position.Position.AnnualSalary where AnnualSalary > 0 and position active during census year
    2. Position.ServiceHistory.AnnualSalary (most recent, non-zero salary)
    3. Defaults to 0.00 if no salary found
  • Business Rule: Uses "Annualized Amount" from WAG screen as specified by PlanSource requirements
• current_salary_effective_on: NULL - Not currently populated
• pay_rate: NULL - Not used for salary employees
• hours_per_week: NULL - Not currently populated (commented out logic available)

Organizational Information

• division: NULL - Not currently populated (commented out: CE.Division)
• location: NULL - Not tracked
• union_code: NULL - IWU employees are not unionized

Benefits and Status Fields

• benefit_company: NULL - Not used
• benefit_deduction_group: NULL - Not used
• highly_compensated: NULL - Not currently populated (threshold logic available)
• shareholder: NULL - IWU is not-for-profit
• owner: NULL - IWU is not-for-profit
• vip: NULL - Not tracked
• fmla: NULL - Not tracked
• benefits_start_date: NULL - Not currently populated
• test_employee: NULL - All real employees
• hide_from_payroll: NULL - Not used
• subscriber_status_id: NULL - Not currently populated

User-Defined Fields (udef)

• udef_1 through udef_4: NULL - Available for client configuration
• udef_5: Mirrors current_salary field (DECIMAL(10,2))
• udef_6 through udef_30: NULL - Available for client configuration

Additional Status Fields

• employment_status_starts_on: NULL - Not currently populated
• employment_status_ends_on: NULL - Not currently populated
• status_starts_on: NULL - Not currently populated
• status_ends_on_expected: NULL - Not used
• is_voluntary_termination: NULL - Not currently populated

Position Selection Logic

The view uses a RankedEmployees CTE with ROW_NUMBER() to select the best record per employee:

1. Active employees first: TerminationDate IS NULL
2. Primary positions first: IsPrimary DESC
3. Highest salary first: CurrentSalary DESC

Salary Calculation

• Primary Source: Position.Position table where position is active during census year
• Fallback Source: Position.ServiceHistory table (most recent non-zero salary)
• Filtering: Only positive salaries (AnnualSalary > 0) are considered
• Year Context: Positions must be active between @Year-01-01 and @Year-12-31

Organization Class Selection

Uses COALESCE with three priority levels:

1. Ongoing primary positions (EndDate IS NULL) - Highest priority
2. Any active primary position - Second priority
3. Job title analysis - Final fallback for edge cases

Rank Priority Order: ADM (1) > FAC (2) > ADJ (3) > HLY (4)

Student Worker Exclusion

Employees are excluded if they meet ALL of the following:

• Have student email pattern: Email LIKE '%@myemail.indwes.edu'
• Have student employment earnings: IsStudentEmployment = 1 AND IsTaxable = 1 AND TaxYear = @Year

Position Data

• Multiple primary positions per employee are handled via ranking logic
• Positions with EndDate are considered "ending" and have lower priority than ongoing positions
• Student worker positions (Rank IN ('CWS', 'IWU', 'SUM')) are excluded from regular employment checks

Salary Data

• Zero salaries are allowed (may indicate stipend positions)
• Salary fallback ensures employees without current position salary can still have historical salary
• Service history provides backup salary data when position table lacks salary information

Employment Type Classification

• Graduate Assistants are always classified as "Part-time" regardless of FTE calculation
• This ensures proper benefit eligibility classification per HR business rules

Duplicate Handling

• ROW_NUMBER() ensures one record per employee
• Ranking prioritizes active employees with primary positions and highest salaries

Missing Employees

• Check: Payroll.Earnings for taxable earnings in the census year
• Verify: Employee has at least one regular (non-student, non-temporary) position
• Confirm: Employee is not excluded by student worker rules
• Validate: EmploymentStatus != 'T' and EmploymentStatus != 'Student Employee'

Incorrect Organization Class

• Check: Primary positions in Position.Position table
• Verify: Rank values (ADM, FAC, ADJ, HLY) are correct
• Confirm: Priority logic (ADM > FAC > ADJ > HLY) is working
• Review: EndDate values - ongoing positions take precedence

Incorrect Employment Level

• Verify: EmploymentStatus and EmploymentType in Core.Employee table
• Check: Only Full-Time Admin and Full-Time Faculty with Full-time type get 'F'
• Confirm: Graduate Assistants are correctly classified as 'P' (Part-time)

Missing or Zero Salaries

• Check: Position.Position.AnnualSalary for active positions during census year
• Verify: Position.ServiceHistory.AnnualSalary for fallback values
• Review: Position StartDate and EndDate to ensure they overlap with census year
• Note: Zero salaries are allowed and may indicate stipend positions

Student Worker Inclusion Issues

• Verify: Email pattern matching (@myemail.indwes.edu)
• Check: Payroll.Earnings.IsStudentEmployment flag for the census year
• Confirm: Both conditions must be true for exclusion (email pattern AND student earnings)

Duplicate Records

• Check: ROW_NUMBER() ranking logic in RankedEmployees CTE
• Verify: Only one record per EmployeeID should be returned (rn = 1)
• Review: Ranking criteria (active status, primary position, salary)