Created by Corina C Larsen, last modified by Miki Yanagi on Nov 29, 2021
This is the HR Master Flat File Export Extract version of the UM_F_EMPLOYE_VW - Employee Flat Table that contains the current (last) job record (or records) for each employee with an array of demographic details, no protected data, and not including student jobs. Contains one record (MIN(EMPL_RCD)) per employee per Business Unit. If you need to get all of the jobs, use UM_FGLALLJOB_VW (FIN).
The data refresh is scheduled at 4:30am daily based on UM_F_EMPLOYEE table in HR.
| Key | Record.Fieldname | Format | XLAT | Heading Text |
|---|---|---|---|---|
| ACTION - Action | Char3 | Action | ||
| ACTION_DT - Action Date | Date | Action Date | ||
| ACTION_REASON - Reason Code | Char3 | Reason | ||
| ANNL_BENEF_BASE_RT - Annual Benefits Base Rate | Num16.3 | Ann Ben Rt | ||
| ANNUAL_RT - Annual Rate | Num16.3 | Annual Rt | ||
| → | BUSINESS_UNIT - Business Unit | Char5 | Unit | |
| DEPTID - Department | Char10 | DeptID | ||
| EFFDT_FROM - Effective Date From | Date | From Date | ||
| EG_ACADEMIC_RANK - Academic Rank | Char3 | Academic Rank | ||
| ELIG_CONFIG3 - Eligibility Config Field 3 | Char10 | Elig Fld 3 | ||
| EMAIL_ADDR - Email Address | Char70 | |||
| → | EMPLID - Empl ID | Char11 | ID | |
| EMPL_CLASS - Employee Classification | Char3 | Empl Class | ||
| EMPL_RCD - Empl Rcd Nbr | Num3.0 | Empl Rcd# | ||
| EMPL_STATUS - Payroll Status | Char1 | N | Pay Status | |
| FIRST_NAME - First Name | Char30 | First Name | ||
| FTE - FTE | Num2.6 | FTE | ||
| FULL_PART_TIME - Full/Part Time | Char1 | N | Full/Part | |
| HIRE_DT - First Start Date | Date | Start Date | ||
| IPEDSSCODE - IPEDS-S Job Category | Char1 | N | IPEDS Cd | |
| JOBCODE - Job Code | Char6 | Job Code | ||
| JOB_ENTRY_DT - Job Entry Date | Date | Entry Date | ||
| JOB_FAMILY - Job Family | Char6 | Job Family | ||
| JOB_INDICATOR - Job Indicator | Char1 | N | Indicator | |
| LAST_NAME - Last Name | Char30 | Last | ||
| LOCATION - Location Code | Char10 | Location | ||
| MIDDLE_NAME - Middle Name | Char30 | Middle | ||
| NAME - Name | Char50 | Name | ||
| NAME_PREFIX - Name Prefix | Char4 | Prefix | ||
| NAME_SUFFIX - Name Suffix | Char15 | Suffix | ||
| POSITION_NBR - Position Number | Char8 | Position | ||
| PRIMARY_JOB_IND - Primary Job Indicator | Char1 | N | Pri Job | |
| REG_TEMP - Regular/Temporary | Char1 | N | Reg/Temp | |
| REHIRE_DT - Rehire Date | Date | Rehire Dt | ||
| REPORTS_TO - Reports To Position Number | Char8 | Reports To | ||
| SAL_ADMIN_PLAN - Salary Administration Plan | Char4 | Sal Plan | ||
| SERVICE_DT - Service Date | Date | Service Dt | ||
| TENURE_STATUS - Tenure Status | Char3 | N | Tenure St | |
| TERMINATION_DT - Termination Date | Date | Term Date | ||
| UM_CAMPUS_ADDRESS1 - CAMPUS_ADDRESS1 | Char55 | CAMPUS ADDR1 | ||
| UM_DEPT_DESCR - Department Description | Char30 | Dept Desc | ||
| UM_EG_RANK_DESCR - EG Academic Rank Description | Char30 | EG Rank Descr | ||
| UM_EMPL_CLASS_DESC - Empl Class Description | Char30 | Empl Class Desc | ||
| UM_EMP_STATUS_DESC - Empl Status Description | Char30 | Emp Status Desc | ||
| UM_EXPECTED_END_DT - Planned Exit | Date | Planned Exit | ||
| UM_FTE_TOTAL - FTE Total | Num2.2 | FTE Total | ||
| UM_IPEDSCODE_DESCR - IPEDSSCODE Description | Char30 | IPEDSSCODE Desc | ||
| UM_JOBCODE_DESCR - JobCode Description | Char30 | Jobcode Descr | ||
| UM_LAST_CHECK_DT - Last Check Date | Date | Last Chk Dte | ||
| UM_LAST_PAY_END_DT - Last Pay End Date | Date | Last Pay End Dt | ||
| UM_LOCATION_DESCR - Location Description | Char30 | Location Descr | ||
| UM_POSITION_DESC - Position Description | Char254 | Position Descr | ||
| UM_PRIM_FUNCTION - Primary Function | Char1 | Prim Function | ||
| UM_PRIM_FUNC_DESC - Primary Function Description | Char30 | Prim Func Descr | ||
| UM_REPORTS_TOEMAIL - Reports To Email Address | Char70 | Rpts to EMail | ||
| UM_REPORTS_TO_DESC - Reports To Description | Char254 | Rpts to Descr | ||
| UM_REPORTS_TO_EMPL - Reports to Empl ID | Char11 | Rpts To Empl | ||
| UM_REPORTS_TO_FNME - Reports To First Name | Char30 | Rpts To FName | ||
| UM_REPORTS_TO_LNME - Reports To Last Name | Char30 | Rpts to LName | ||
| UM_REPORTS_TO_NAME - Reports to Name | Char50 | Report to Name | ||
| UM_REP_TO_CMP_ADDR - Reports to Campus Address | Char55 | Rep to Cmp Addr | ||
| UM_REP_TO_LOC_CD - UM_REP_TO_LOC_CODE | Char10 | UM_REP_TO_LOC | ||
| UM_REP_TO_LOC_DESC - Reports to Location Descr | Char30 | Rep to Loc Desc | ||
| UM_TENURE_DESCR - UM_TENURE_DESCR | Char30 | UM_TENURE_DESCR | ||
| UM_UNION_DESCR - Union Code Description | Char30 | Union Cd Descr | ||
| UNION_CD - Union Code | Char3 | Union Code | ||
| WORK_PHONE - Work Phone # | Char24 | Work Phone |
There exist seven different versions of the table commonly referred to as "HR Flat File." Click each table for details.
Human Resources Database
UM_F_EMPLOYE_VW - Employee Flat Table - This is the parent table for all views below.
UM_F_EMPL_VW - Employee Flat Table
Campus Solutions Reporting Database
Finance Database
UM_FGLEMPLOY_VW (this page)
Code for UM_FGLEMPLOY_VW Expand source
SELECT A.EMPLID ,A.EMPL_RCD ,A.BUSINESS_UNIT ,A.EFFDT_FROM ,A.ACTION ,A.ACTION_DT ,A.ACTION_REASON ,A.DEPTID ,A.UM_DEPT_DESCR ,A.EG_ACADEMIC_RANK ,A.UM_EG_RANK_DESCR ,A.ELIG_CONFIG3 ,A.UM_EMAIL_BUS_ADDR ,A.EMPL_CLASS ,A.UM_EMPL_CLASS_DESC ,A.EMPL_STATUS ,A.UM_EMP_STATUS_DESC ,A.FIRST_NAME ,A.MIDDLE_NAME ,A.LAST_NAME ,A.NAME ,A.NAME_PREFIX ,A.NAME_SUFFIX ,A.UM_FTE_TOTAL ,A.FTE ,A.FULL_PART_TIME ,A.HIRE_DT ,A.IPEDSSCODE ,A.UM_IPEDSCODE_DESCR ,A.REPORTS_TO ,A.UM_REPORTS_TO_NAME ,A.UM_REPORTS_TO_EMPL ,A.UM_REPORTS_TO_DESC ,A.UM_REPORTS_TOEMAIL ,A.UM_REPORTS_TO_LNME ,A.UM_REPORTS_TO_FNME ,A.JOBCODE ,A.UM_JOBCODE_DESCR ,A.JOB_ENTRY_DT ,A.JOB_INDICATOR ,A.JOB_FAMILY ,A.UM_PRIMARY_JOB_IND ,A.POSITION_NBR ,A.UM_POSITION_DESC ,A.REG_TEMP ,A.REHIRE_DT ,A.SAL_ADMIN_PLAN ,A.SERVICE_DT ,A.TERMINATION_DT ,A.UM_EXPECTED_END_DT ,A.UM_PRIM_FUNCTION ,A.UM_PRIM_FUNC_DESC ,A.UNION_CD ,A.UM_UNION_DESCR ,A.UM_TENURE_STATUS ,A.UM_TENURE_DESCR ,A.UM_CAMPUS_ADDRESS1 ,A.LOCATION ,A.UM_LOCATION_DESCR ,A.UM_WORK_PHONE , A.UM_LAST_CHECK_DT , A.UM_REP_TO_CMP_ADDR , A.UM_REP_TO_LOC_DESC , A.UM_LAST_PAY_END_DT , A.ANNL_BENEF_BASE_RT , A.ANNUAL_RT FROM SYSADM.PS_UM_F_EMPLOYEE A WHERE A.EMPL_RCD = NVL(( SELECT MIN(C.EMPL_RCD) FROM SYSADM.PS_UM_F_EMPLOYEE C WHERE A.EMPLID = C.EMPLID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND C.UM_PRIMARY_JOB_IND = 'Y'), ( SELECT MIN(C.EMPL_RCD) FROM SYSADM.PS_UM_F_EMPLOYEE C WHERE A.EMPLID = C.EMPLID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT ))
Data Dictionary Links
Clicking the linked (blue) fields in any table will take you to the Data Dictionary and provide you with more information about that field as well as field values if they are available.
