- DATA VIEW NAME
- EMPLOYEE_CONTRIBUTIONS
- OVERVIEW
- This view contains a summary of all contributions made to the Wisconsin Retirement System by an employee and his employer. The view contains the current balance of an employee’s retirement account as of 11/2012. Each employee will have only one record or each type of contribution e.g required contributions, voluntary additional contributions, tax deferred contributions, etc.
This information can be used in conjunction with the following to show you a more complete picture of the employees WRS record:
UA_EMPLOYEE_TOTAL_SERVICE (Multiple records
possible)
UA_EMPLOYEE_CURRENT_SERVICE (Multiple records
possible)
-UA_EMPLOYEE_CATEGORY
UA_EMPLOYEE_GENERAL_INFO
EMPLOYEE_MONEY_PURCHASE - LOAD FREQUENCY
- Data no longer updated as of 11/2012
- DATA CUSTODIAN
- HRS Service Center
Contact: Catharine Derubeis - AUTHORIZATION
- You must receive approval to access data from the data custodian. For information about how to get authorized for InfoAccess data views, connect to InfoAccess Home Page
- SPECIAL
CONSIDERATIONS - A person, who at sometime in their Wisconsin Retirement System participation was an elected State Official, will have their final average earnings determined at the time of your retirement based on the greater of your actual earnings or your statutory salary. So in the case of elected state officials, all fields showing highest year earning information will contain either spaces or zeros.
Note: You will notice that some data fields have a Section header at the beginning of a group of fields. This is to identify groups of data element similar to how they appear on the Statement of Benefits printed form.
- QUESTIONS
- Contact: Division of Information Technology Help Desk
Phone: 264-HELP (4357) - LAST UPDATED
- 6/97 – New View Created
03/09 – Custodian Change
04/10 – Added Member_ID c4j
06/12 – Custodian Change c4j
11/12 – Removed SSN (replaced by Emplid), removed retirement_msg, changed all references from ‘fixed’ to ‘core’, added or removed other fields to more closely resemble on-line statement of benefits, and Loaddate becomes Extract_Date_Time c4j
02/15 – Custodian Change - DATA FORMATS
- The codes following the field names in this document define the field. The different types are:
VARCHAR2( N) (N) = maximum length. Field contains letters and/or numbers. Trailing blanks are eliminated.
Example: VARCHAR2 (5) could contain ‘ABC12’ or ‘ABC’ or ‘A’NUMBER (N) (N) = length. Field contains only numbers.
NUMBER (X,Y) = format for numbers with decimal positions
X = total number of digits Y = the number of digits after the decimal
Examples: NUMBER (6) represents 250012 NUMBER (6,2) represents 2500.12
| Column Name | Format | Description |
|---|---|---|
| EMPLID | Varchar2(8) | The emplid of the employee, assigned by university. This 8-character field should be used when writing query selection criteria. |
| MEMBER_ID | Varchar2(13) | In order to better protect your personal information, the Department of Employee Trust Funds (ETF) has assigned member identification (ID) numbers to all WRS members. |
| CORE_INT_PERCENT | Number(5,2) | The core interest rate as a percent earned for the reporting year format nn.nn (6.5% or 11.3% etc). |
| VARI_RATE_PERCENT | Number(5,2) | The variable gain or loss for the reporting year format nn.nn (6.5% or 11.3% etc). |
| RETIREMENT_AGE | Number(2) | The normal retirement age associated with the employment category the person is in. Example: general employment category = 65 normal retirement age |
| ACCOUNT BALANCES — REQUIRED SECTION | ||
| EMP_BEG_REQ_BALANCE_C | Number(10,2) | The beginning balance of the Employee Required Core Contributions at the start of the reporting year. |
| EMP_BEG_REQ_BALANCE_V | Number(10,2) | The beginning balance of the Employee Required Variable Contributions at the start of the reporting year. |
| EMP_BEG_REQ_BALANCE_T | Number(10,2) | This is the Total Contributions in the employee’s WRS account at the beginning of the reporting year and is created by adding beg_req_balance_c to the beg_req_balance_v. |
| REQ_INTEREST_EARNED_C | Number(10,2) | Interest earned during the reporting year on the employee required contribution core rate account. |
| REQ_INTEREST_EARNED_V | Number(10,2) | Variable Gain/Loss during the reporting year on the employee required variable contributions. |
| REQ_INTEREST_EARNED_T | Number(10,2) | The interest and variable gains/losses for the reporting period and is created by adding interest_core to the interest_vari. |
| EMP_PAID_CONTRIBUTION_C | Number(10,2) | The amount of the employee required core contributions to the WRS account for the reporting year. |
| EMP_PAID_CONTRIBUTION_V | Number(10,2) | The amount of the employee required variable contributions to the WRS account for the reporting year. |
| EMP_PAID_CONTRIBUTION_T | Number(10,2) | Total employee required core and variable contributions made to the WRS account in the reporting year. |
| EMPLR_PAID_EMP_CONTRIBUTION_C | Number(10,2) | The amount of the employer required core contributions to the WRS account for the reporting year. |
| EMPLR_PAID_EMP_CONTRIBUTION_V | Number(10,2) | The amount of the employer required variable contributions to the WRS account for the reporting year. |
| EMPLR_PAID_RMP_CONTRIBUTION_T | Number(10,2) | Total employer required core and variable contributions made to the WRS account in the reporting year. |
| EMP_REQ_ADJUSTMENT_C | Number(10,2) | The amount of any employee required core account adjustments made to the WRS account during the reporting year, e.g. a payroll adjustment paid in the reporting period for a prior year. |
| EMP_REQ_ADJUSTMENT_V | Number(10,2) | The amount of any employee required variable account adjustments made to the WRS account during the reporting year, e.g. a payroll adjustment paid in the reporting period for a prior year. |
| EMP_REQ_ADJUSTMENT_T | Number(10,2) | The total employee required core and variable adjustments made to the WRS account in the reporting year and is created by adding the req_adjustment_c to req_adjustment_v. |
| EMP_END_REQ_BALANCE_C | Number(10,2) | The 1/1/XX calendar year end balance for the employee required core contributions in the employee’s account. |
| EMP_END_REQ_BALANCE_V | Number(10,2) | The 1/1/XX calendar year end balance for the employee required variable contributions in the employee’s account. |
| EMP_END_REQ_BALANCE_T | Number(10,2) | The 1/1/XX calendar year ending balance for the employee required core and variable contributions in the employee’s account and is created by adding the end_req_balance_c to end_req_balance_v. |
| INVESTMENT_IN_CONTRACT | Number(10,2) | The employee’s contributions to the WRS used for determining the taxability of a benefit. |
| EMPLOYEE VOLUNTARY ADDITIONAL BALANCES SECTION | ||
| EMP_ADDL_BEG_BALANCE_C | Number(10,2) | The employee core voluntary additional contributions balance at the beginning of the reporting year. |
| EMP_ADDL_BEG_BALANCE_V | Number(10,2) | The employee variable voluntary additional contributions balance at the beginning of the reporting year. |
| ADDL_INTEREST_C | Number(10,2) | The core interest on the beginning balance of the employee core voluntary additional contributions. |
| ADDL_INTEREST_V | Number(10,2) | This variable gains/losses on the beginning balance of the employee variable voluntary additional contributions. |
| EMP_ADDL_CONTRIBUTION_C | Number(10,2) | The employee core voluntary contributions made in the reporting year. |
| EMP_ADDL_CONTRIBUTION_V | Number(10,2) | The employee variable voluntary contributions made in the reporting year. |
| EMP_ADDL_ADJUSTMENT_C | Number(10,2) | The employee additional core contribution adjustments made in the reporting year. |
| EMP_ADDL_ADJUSTMENT_V | Number(10,2) | The employee additional variable contribution adjustments made in the reporting year. |
| EMP_ADDL_END_BALANCE_C | Number(10,2) | The 1/1/XX ending balance of the employee voluntary core additional contributions. |
| EMP_ADDL_END_BALANCE_V | Number(10,2) | The 1/1/XX ending balance of the employee voluntary variable additional contributions. |
| EMP_TAX_DEF_BEG_BALANCE_C | Number(10,2) | The employee tax deferred additional core contributions balance at the beginning of the reporting year. |
| EMP_TAX_DEF_BEG_BALANCE_V | Number(10,2) | The employee tax deferred additional variable contributions balance at the beginning of the reporting year. |
| TAX_DEF_INTEREST_C | Number(10,2) | Core interest applied to the beginning balance in the tax deferred employee additional core contributions. |
| TAX_DEF_INTEREST_V | Number(10,2) | Variable Gains/Losses applied to the beginning balance in the tax deferred employee additional variable contributions. |
| EMP_TAX_DEF_CONTRIBUTION_C | Number(10,2) | Tax deferred employee additional core contributions made during the reporting year. |
| EMP_TAX_DEF_CONTRIBUTION_V | Number(10,2) | Tax deferred employee additional variable contributions made during the reporting year. |
| EMP_TAX_DEF_ADJUSTMENT_C | Number(10,2) | Adjustments to tax deferred employee additional core contributions during the reporting year. |
| EMP_TAX_DEF_ADJUSTMENT_V | Number(10,2) | Adjustments to tax deferred employee additional variable contributions during the reporting year. |
| EMP_TAX_DEF_END_BALANCE_C | Number(10,2) | This is the 1/1/XX ending balance for tax deferred employee additional core contributions. |
| EMP_TAX_DEF_END_BALANCE_V | Number(10,2) | This is the 1/1/XX ending balance for tax deferred employee additional variable contributions. |
| EMPLR_ADDL_LTDI_BEG_BALANCE_C | Number(10,2) | The employer core additional/LTDI contributions balance at the beginning of the reporting year. |
| EMPLR_ADDL_LTDI_BEG_BALANCE_V | Number(10,2) | The employer variable additional/LTDI contributions balance at the beginning of the reporting year. |
| EMPLR_ADDL_LTDI_INTEREST_C | Number(10,2) | The core interest on the beginning balance of the employer core additional/LTDI contributions. |
| EMPLR_ADDL_LTDI_INTEREST_V | Number(10,2) | This variable gains/losses on the beginning balance of the employer variable additional/LTDI contributions. |
| EMPLR_ADDL_LTDI_CONTRIBUTION_C | Number(10,2) | The employer core additional/LTDI contributions made in the reporting year. |
| EMPLR_ADDL_LTDI_CONTRIBUTION_V | Number(10,2) | The employer variable additional/LTDI contributions made in the reporting year. |
| EMPLR_ADDL_LTDI_ADJUSTMENT_C | Number(10,2) | The employer core additional/LTDI contribution adjustments made in the reporting year. |
| EMPLR_ADDL_LTDI_ADJUSTMENT_V | Number(10,2) | The employer additional/LTDI variable contribution adjustments made in the reporting year. |
| EMPLR_ADDL_LTDI_END_BALANCE_C | Number(10,2) | The 1/1/XX ending balance of the employer voluntary core additional/LTDI contributions. |
| EMPLR_ADDL_LTDI_END_BALANCE_C | Number(10,2) | The 1/1/XX ending balance of the employer voluntary variable additional/LTDI contributions. |
| INVESTMENT_IN_CONTRACT_ADDL | Number(10,2) | The employee’s additional contributions to the WRS used for determining the taxability of a benefit. |
| BENEFIT AMOUNTS SECTION | ||
| SEP_BENEFIT_MSG | Varchar2(105) | This contains a separation message that states if an employee is eligible for a separation benefit or not based on the employee’s age. |
| SEP_BENEFIT_AMOUNT | Number(10,2) | If an employee is eligible for a separation benefit this is the amount that would be payable as of the 1/1/XX. |
| DEATH_BENEFIT_REQ | Number(10,2) | Amount of required contributions payable as a death benefit upon the death of the employee. |
| DEATH_BENEFIT_ADDL | Number(10,2) | Amount of voluntary additional contributions (this includes voluntary additional and tax deferred which voluntary additional) payable as a death benefit upon the death of an employee. |
| DEATH_BENEFIT_TOT | Number(10,2) | Total amount payable as a death benefit upon an employees’ death. |
| DEATH_BENEFIT_REQ_INACT | Number(10,2) | Amount of required contributions payable as a death benefit upon the death of the former employee. |
| DEATH_BENEFIT_ADDL_INACT | Number(10,2) | Amount of voluntary additional contributions (this includes voluntary additional and tax deferred which voluntary additional) payable as a death benefit upon the death of the former employee. |
| DEATH_BENEFIT_TOT_INACT | Number(10,2) | Total amount payable as a death benefit upon a former employees’ death. |
| FORMULA RETIREMENT BENEFIT INFORMATION SECTION | ||
| HIGHEST_EARN_YEAR1 | Varchar2(7) | Contains the calendar of fiscal year of the highest earning that would be used in calculating a Formula Retirement Annuity Benefit. There are generally 3 occurrences of this information. Format for general employees = CCYY (1995) teachers = CCYY-YY (1995-96) |
| HIGHEST_EARN_AMOUNT1 | Number(10,2) | Contains the amount earned for highest earning year of the employee. There are generally 3 occurrences of this information. |
| HIGHEST_EARN_SERVICE1 | Number(4,2) | Indicates the amount of creditable service granted during the highest earnings year. Value for full year is 1.00. If service is less than 1.00, this figure is a decimal portion of a year. There are generally 3 occurrences of this information. |
| HIGHEST_EARN_YEAR2 | Varchar2(7) | Second occurrence of highest earning year. |
| HIGHEST_EARN_AMOUNT2 | Number(10,2) | Second occurrence of highest earning amount. |
| HIGHEST_EARN_SERVICE2 | Number(4,2) | Second occurrence of highest earned service. |
| HIGHEST_EARN_YEAR3 | Varchar2(7) | Third occurrence of highest earning year. |
| HIGHEST_EARN_AMOUNT3 | Number(10,2) | Third occurrence of highest earning amount. |
| HIGHEST_EARN_SERVICE3 | Number(4,2) | Third occurrence of highest earned service. |
| VARIABLE EXCESS/DEFICIENCY USED IN FORMULA BENEFIT CALCULATION SECTION |
||
| VAR_EXCESS_DEF_EMP | Number(10,2) | The earnings on the employee variable required contributions in the variable division that exceeded the earnings that would have been earned if in the core division. |
| VAR_EXCESS_DEF_ER | Number(10,2) | The earnings on the employer variable required contributions in the variable division that exceeded the earnings that would have been earned if in the core division. |
| VAR_EXCESS_DEF_TOT | Number(10,2) | The total earnings on the employee and employer variable required contributions in the variable division that exceeded the earnings that would have been earned if in the core division. This amount is used for calculating a Money Purchase Annuity that can be added to the Formula Annuity amount. |
| FINAL_AVG_MONTHLY_EARNINGS | Number(10,2) | The average monthly earnings of the three highest years of earnings. |
| EXTRACT_DATE_TIME | Date | The date and time these data were extracted. Format : YYYYMMDD HH24:MI:SS |