IL TRS Payroll Report - File Layout and Data Mapping
The following table outlines the Illinois field names, database fields, format, whether required, and the location from which the data is retrieved.
Field Name | Database Field | Format | Required | Data Extracted From |
---|---|---|---|---|
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:
| |
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:
| |
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:
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. |