IL TRS Payroll Report - File Layout and Data Mapping

Fiscal Year

fisc_yr

char(4)

Yes

Specified during the Load and Add processes.

The value is based on the Pay Schedule of the record being added.

Report Type

rpt_type

char(2)

Yes

Specified during the Load process.

Check the box for Defined Benefit, SSP Defined Contribution, or both.

Check Issue Date

rpt_date

smalldatetime

Yes

Selected during the Load process (Check Issue Date).

Pay Period Begin Date

begin_date

smalldatetime

Yes

Pay Period Begin Date from the pay schedule.

Pay Period End Date

end_date

smalldatetime

Yes

Pay Period End Date from the pay schedule.

Employee Number

empl_no

int

Yes

employee.empl_no

Social Security Number

ssn

char(11)

employee.ssn

Last Name

l_name

char(50)

employee.l_name

First Name

f_name

char(50)

employee.f_name

Middle Name

m_name

char(50)

employee.m_name

Suffix

name_suffix

char(3)

employee.name_suffix

The name suffix is automatically crosswalked to TRS name suffix codes.

Gender

gender

char(2)

person.sex

Birth Date

birthdate

smalldatetime

employee.birthdate

Address

addr1

char(50)

employee.addr1

Address 2

addr2

char(50)

employee.addr2

City

city

char(25)

employee.city

State

state

char(2)

employee.state_id

Zip Code

zip

char(5)

employee.zip[1,5]

Zip Code Extension

zip_ext

char(4)

employee.zip[7,10]

Phone

phone

char(10)

Set to the following, in order of preference:

1. person.home_phone

2. person.cell_phone

3. person.other_phone

4. person.work_phone

Email Address

email_addr

char(75)

employee.email_addr

Uses the employee’s email address if it is populated (employee.personal_email) or the employee’s email address (employee.email_addr) if the personal email address is blank.

Pay Frequency

pay_freq

char(2)

empuser.ftext1 where page_no = 32001

or

payroll.pay_freq if the user-defined field is not available

Balanced Calendar

bal_cal

char(1)

empuser.ftext7 where page_no = 32001

Employment Type

empl_type

char(1)

empuser.ftext1 where page_no = 32000

Hire Date

hire_date

smalldatetime

employee.hire_date or empuser.ftext2 where page_no = 32001 if exists

Termination Date

term_date

smalldatetime

empuser.ftext3 where page_no = 32001 if exists as an override for person.term_date

Termination Reason

term_code

char(2)

person.term_code

The value is crosswalked through the state term code in the Termination Codes table. There are only two codes for IL TRS reporting:

• Terminated

• Death

Job Category

job_cat

char(2)

empuser.ftext5 where page_no = 32001

Contract Days

contract_days

smallint

Primary payrate contract days

Salary FTE

fte

decimal(3, 0)

FTE from the primary payrate multiplied by 100

Annual Rate

ann_sal_rate

decimal(10, 2)

Sum of contract limit from payrate for all rates not exempt from retirement, where the contract flag is Y, and the status is neither I (Inactive) nor T (Terminated).

Contribution Category

contrib_type

char(2)

il_trs_ded_codes.cont_cat based on the employee's deduction code in check history matching il_trs_ded_codes.ded_cd or empuser.ftext6 where page_no = 32001 if exists

Payment Reason

pay_type

char(2)

state_paytable.cb001 based on pay code in check history record

Deferred

deferred

char(1)

Indicates that the employee received pay, but:

• Did not work any days in July and August,

• The employee was paid before they started working, or

• Earnings were paid over the summer when the school year was completed.

Check the calendar for days worked in the period.

Earnings

earnings

decimal(10, 2)

The sum from check history for the payment reason on the pay code reference table and add any board paid contributions if FTD != maximum deduction amount.

Excess Earnings

xcess_earnings

decimal(10, 2)

IRS Limit should be entered as the deduction maximum. Calculates the fiscal-to-date TRS earnings and compares them to the deduction maximum.

If FTD-current contributions > maximum, the total amount of contributions is reported as excess.

If FTD >= maximum and FTD-current contribution < maximum, only the amount of the current contributions that make the FTD = max is reported. The remaining is reported under earnings.

Contributions

contrib

decimal(10, 2)

The sum from the check history of the employee and board paid contributions for DB deduction types/categories

THIS Contributions

this_contrib

decimal(10, 2)

The sum from the check history of the employee-owed THIS contribution, currently 1.24%

Employer Defined Contributions

employer_contrib

decimal(10, 2)

The sum from the check history of the employer contributions for DC deduction types/categories

Docked Days

dock_days

decimal(5, 2)

Retrieved from check history, hours/day conversion factor on the 32000 page, similar to the Annual TRS Report.

Dock days uses the value in the Hours Per Day field on the Illinois State Retirement - Employee Information page only if the Leave Pay Method on the Human Resources Profile – State page is H - Hourly. Otherwise, it calculates dock days as days.

Sick/Personal Days

sick_days

decimal(5, 1)

The district needs to identify its leave codes for Sick and Personal Days. The combined balance is reported.

Days Paid

days_paid

smallint

Retrieved from the calendar for full-time employees, similar to the Annual TRS Report.

Post-Retirement Hours

post_ret_hrs

smallint

Hours of pay when the employee’s deduction is the ‘99’ Contribution Category.