Data Mapping - Wage Report
The following table outlines the Iowa field names, descriptions, database column names, data type, and the location from which the data is retrieved.
Field Name | Description | Database Table/ Name | Type | Location in Application |
|---|---|---|---|---|
Year | Reporting year. | iaqtrwage.yr | Char (4) | Entered by the user when loading data. |
Quarter | Reporting quarter. | iaqtrwage.quarter | Char (1) | Entered by the user when loading data. |
Employee Number | Employee number. | iaqtrwage.empl_no | Int | Employee.empl_no |
Social Security | Social Security Number (stored without dashes). | iaqtrwage.ssn | Char (11) | Employee.empl_no |
Last Name | Last Name, including suffix if any. | iaqtrwage.l_name | Char (30) | Employee.empl_no |
First Name | First Name, including middle initial or name if any. | iaqtrwage.f_name | Char (30) | Employee.empl_no |
State Code | IA-defined state code. | iaqtrwage.state_code | Char (2) | Not stored elsewhere; hard-coded to ‘19’. |
Quarterly Wages | Total quarterly wages. | iaqtrwage.tearn_q | Decimal (10, 2) | Payroll.tearn_q |
Quarterly Taxable Wages | Quarterly wages subject to UI tax. | iaqtrwage.tax_wages | Decimal (12, 2) | (tearn_q – (t_earn_y – iaqtrsetup.sui_max)). |
Location | State code for employee location. | iaqtrwage.emp_locn | Char (3) | If a user-defined location crosswalk table exists, user_ref.desc_x where user_ref.prefx is the table name listed in hrmstate.state9 and user_ref.code is r_person.location. Otherwise, hrm_locn.state_locn_code where hrm_locn.code = r_person.location. |
Month 1 Employment | Had earnings in the pay run which includes the 12th of the first month of the quarter. | iaqtrwage.month1 | Char (1) | Empuser.tcode1 where page_no = ‘32000’. |
Month 2 Employment | Had earnings in the pay run which includes the 12th of the second month of the quarter. | iaqtrwage.month2 | Char (1) | Empuser.tcode2 where page_no = ‘32000’. |
Month 3 Employment | Had earnings in the pay run which includes the 12th of the third month of the quarter. | iaqtrwage.month3 | Char (1) | Payroll.tearn_m |