IPERS Monthly Retirement Report - Data Mapping
The following table outlines the Iowa field names, application Column names, data type, and fthe location from which the data is retrieved.
Application Field Name | Description | Database Table/ Name | Type | Location in Application |
|---|---|---|---|---|
Reporting Year | Reporting year | iarettab.rpt_yr | Char (4) | Entered by the user when loading data |
Reporting Month | Reporting month | iarettab.rpt_month | Char (2) | Entered by the user when loading data |
Employee Number | Employee number | iarettab.empl_no | int | Employee.empl_no |
Social Security | Social Security Number (stored without dashes) | iarettab.ssn | Char(9) | employee.ssn[1,3], employee.ssn[5,6], employee.ssn[8,11] |
Last Name | Last Name, including suffix if any | iarettab.l_name | Char(20) | (Employee.l_name, “ “ employee.name_suffix) clipped to 20 characters |
First Name | First Name | iarettab.f_name | Char(19) | Employee.f_name[1,19] |
Middle Initial | Middle Initial | iarettab.l_name | Char(1) | Employee.m_name[1,1] |
Address | Two address lines | iarettab.addr1 | Char(30) | employee.addr1[1,30] |
(second address line) | iarettab.addr2 | Char(30) | employee.addr2[1,30] | |
City | City | iarettab.city | Char(20) | employee.city |
State | State | iarettab.state_id | Char(2) | employee.state_id |
Zip Code | Zip code (up to 9 digits) | iarettab.zip | Char(10) | employee.zip |
Occupational Class | The two-digit occupation code related to each member’s employment type and corresponding contribution rate. Members may not have multiple Detail Records with the same occupation code on the same report but may have multiple Detail Records with different occupation codes. Employers are limited to using occupation codes associated with their employer type. For example, all schools, community colleges, AEAs, and universities should report employees under Occupation Code 11. If you have questions about which occupation codes to report employees under, contact a member of the IPERS’ Employer Relations Bureau. Valid values:
| iarettab.occ_cls | Char(2) | Default is ‘11’ for clients whose hrm profile type is ‘E,’ otherwise ‘01’. If the 32000 page empuser.ftext7 “Occ cls” is populated, the first two characters of this field override the default. |
Monthly Wages | Total covered wages for this occupation code and member for this reporting period. | iarettab.wages | Decimal (10,2) | Deduct.sal_m where the deduction code is one of the deduction codes on the Human Resources Profile state screen (hrmstate.state11 through state16), and the deduction was taken during the month (taken_m > 0). The program uses the first matching deduction code it finds for the employee. |
Birthdate | Employee date of birth | iarettab.dob | Datetime | Employee.birthdate |
Gender | Employee gender | iarettab.gender | Char(1) | Person.sex |
Hire Date | First date of employment with the employer. | iarettab.hire_date | Datetime | If the 32000 page empuser.ftext8 “IPERS HireDate” is populated (employee State Required page), this date is used, otherwise employee.hire_date is used. |
Termination Date | If the member terminates and later returns to employment, a new employment date is required. | iarettab.term_date | Datetime | Person.term_date only when the year and month of the term_date match the reporting year and month. |
Last Check Date | Date of last IPERS-covered check for terminated employees. A valid date is required for employees who have terminated employment. | iarettab.last_chk_dt | Datetime | (most recent) Checkhis.iss_date where man_void is blank or ‘M’ only when the year and month of person.term_date matches the reporting year and month. |
(not displayed) | Termination indicator Report quarter | iarettab.term_ind iarettab.rpt_quarter | Char(1) Char(1) | DB field not in use. DB field not in use. |