- DATA VIEW NAME
- EMPLOYEE_TOTAL_SERVICE
- OVERVIEW
- This table shows the total creditable service an employee has after 1999 and before 2000 under WRS from their beginning date under WRS to the January 1 of the Annual Statement. If an employee had creditable service in more than one employment category, the total service after 1999 and before 2000 in each employment category is displayed separately. There could be up to 8 employment categories for an employee.
This information can be used in conjunction with the following to show a more complete picture of the employee’s WRS record: UA_EMPLOYEE_CURRENT_SERVICE (Multiple records possible)
UA_EMPLOYEE_CONTRIBUTIONS
UA_EMPLOYEE_GENERAL_INFO
UA_EMPLOYEE_MONEY_PURCHASE
UA_EMPLOYEE_CATEGORY
RETIREMENT_BENEFIT_PROJECTIONS - 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 - No special considerations.
- QUESTIONS
- Contact: Division of Information Technology Help Desk
Phone: 264-HELP (4357) - LAST UPDATED
- 03/2002 – 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), added Sequence_Num, removed Cred_Service_Year, added Creditable_Serv_After_Act10, Total_Credit_Years becomes Employee_Total_Service and
Loaddate becomes Extract_Date_Time c4j
02/15 – Custodian Change - DATA FORMATS
- Primary Key Columns are identified with an asterisk(*)
- Alphanumeric Data
- 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’ - Numeric Data
- 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 - Date
- DATE – Date can be in various formats. Date functions available with your query tool can be used to format the date.
Examples: DATE (YYYYMMDD), DATE (YYDDD), DATE (DD-MM-YY)
| 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. |
| SEQUENCE_NUM | Varchar2(1) | This is the sequence number that indicates that more than up to 9 rows of data may exist on this view per emplid. |
| 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. |
| *CATEGORY_CODE | Varchar2(2) | This code identifies the employment category an employee is reported in. Example “30” General “40” Teacher “11” Teacher, Exec retirement |
| CREDITABLE_SERV_BEFORE_2000 | Number (6,2) | The portion of a year of creditable service that is earned in the current calendar or fiscal year by employment category before year 2000. If in more than one employment category in the current calendar or fiscal year there will be two occurrences of the this data. |
| CREDITABLE_SERV_AFTER_1999 | Number (6,2) | The portion of a year of creditable service that is earned in the current calendar or fiscal year by employment category after year 1999. If in more than one employment category in the current calendar or fiscal year there will be two occurrences of the this data. |
| CREDITABLE_SERV_AFTER_ACT10 | Number (6,2) | The portion of a year of creditable service that is earned in the current calendar or fiscal year by employment category after Act10 was enacted. If in more than one employment category in the current calendar or fiscal year there will be two occurrences of the this data. |
| EMPLOYEE_TOTAL_SERVICE | Number (6,2) | Total amount of creditable years of service before 2000 plus after 1999 plus after Act10 in the retirement system by employment category. |
| EXTRACT_DATE_TIME | Date | The date and time these data were extracted. Format : YYYYMMDD HH24:MI:SS |