DARTS : UM_F_EMPL_VW - Employee Flat Table

This is the HR Master Flat File Extract version from the UM_F_EMPLOYE_VW - Employee Flat Table that contains the most current last job record (or records) for each employee with an array of demographic details. This view does not include restricted data.

Key	Record.Fieldname	Format	XLAT	Heading Text
	ACTION - Action	Char3	N	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_TITLE - Business Title	Char30		Title
	BUSINESS_UNIT - Business Unit	Char5		Unit
	CMPNY_SENIORITY_DT - Company Seniority Date	Date		Company Dt
	COMPANY - Company	Char3		Co
	COMPRATE - Compensation Rate	Num13.6		Comp Rate
	COMP_FREQUENCY - Compensation Frequency	Char5		Comp Freq
	CONTRACT_BEGIN_DT - Contract Begin Date	Date		Begin Date
	CONTRACT_LENGTH - Contract Length	Char1	N	Contract Length
	CONTRACT_STATUS - Contract Status	Char1	N	Status
	CONTRACT_TYPE - Contract Type	Char3		Type
	CONTRCT_EXP_END_DT - Contract Expected End Date	Date		Expect End Dt
	DAILY_RT - Daily Rate	Num16.3		Daily Rt
→	DEPTID - Department	Char10		Dept ID
	DEPT_DESCR - Department Description	Char30		Dept Desc
	DEPT_ENTRY_DT - Department Entry Date	Date		Dept Date
	EARNS_DIST_TYPE - Earnings Distribution Type	Char1	N	Dist type
	EEO1CODE - EEO-1 Job Category	Char1	N	EEO-1 Cat
	EEO_CLASS - EEO Classification	Char1	N	EEO Class
	EFFDT_FROM - Effective Date From	Date		From Date
	EFFSEQ_WRK - Effective Sequence	Num3.0		Eff Seq
	EG_ACADEMIC_RANK - Academic Rank	Char3		Academic Rank
	EG_GRANTED_DT - Tenure Granted Date	Date		Granted Date
	EG_MAND_REVW_DT - Mandatory Review Dt	Date		Mand. Review Dt
	EG_ORIG_TRACK_DT - Original Tenure Track Start Dt	Date		Orig. Track Dt
	EG_TENURE_HOME - Tenure Home Organization	Char10		Tenure Home
	ELIG_CONFIG1 - Part -Time Status	Char10		Part-Time Stat
	ELIG_CONFIG2 - Benefits Eligible - Temporary	Char10		Benifits Elig
	ELIG_CONFIG3 - Work Calendar	Char10		Work Calendar
	ELIG_CONFIG4 - Eligibility Config Field 4	Char10		Elig Fld 4
	ELIG_CONFIG5 - Eligibility Config Field 5	Char10		Elig Fld 5
	ELIG_CONFIG6 - Eligibility Config Field 6	Char10		Elig Fld 6
	ELIG_CONFIG7 - Eligibility Config Field 7	Char10		Elig Fld 7
	ELIG_CONFIG8 - Eligibility Config Field 8	Char10		Elig Fld 8
	ELIG_CONFIG9 - Cohort	Char10		Cohort
→	EMPLID - Empl ID	Char11		ID
	EMPL_CLASS - Employee Classification	Char3	N	Empl Class
→	EMPL_RCD - Empl Record	Num3.0		Empl Record
	EMPL_STATUS - Payroll Status	Char1	N	Pay Status
	EMPL_TYPE - Employee Type	Char1	N	Type
	ESTABID - Establishment ID	Char12		Estab ID
	FICA_STATUS_EE - FICA Status-Employee	Char1	N	FICA EE
	FIRST_NAME - First Name	Char30		First Name
	FLSA_STATUS - FLSA Status	Char1	N	FLSA Stat
	FTE - FTE	Num2.6		FTE
	FULL_PART_TIME - Full/Part Time	Char1	N	Full/Part
	FUNCTION_CD - Function Code	Char2		Functn Cd
	GL_PAY_TYPE - General Ledger Pay Type	Char6		GL Pay Type
	GRADE - Salary Grade	Char3		Grade
	GRADE_ENTRY_DT - Grade Entry Date	Date		Grade Date
	HIRE_DT - First Start Date	Date		Start Date
	HOME_PHONE - Home Phone #	Char24		Phone
	HOURLY_RT - Hourly Rate	Num13.6		Hrly Rate
	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_DATE_WORKED - Last Date Worked	Date		Last Date
	LAST_INCREASE_DT - Date Last Increase	Date		Incr Dt
	LAST_NAME - Last Name	Char30		Last
	LEAVE_HRS_BALANCE - Leave Hours Balance	SNm7.6		Lv Hrs Bal
	LOCATION - Location Code	Char10		Location
	MAIL_CITY - City	Char30		City
	MAIL_COUNTRY - Country	Char3		Cntry
	MAIL_POSTAL - Postal Code	Char12		Postal
	MAIL_STATE - State	Char6		St
	MIDDLE_NAME - Middle Name	Char30		Middle
	MOBILE_PHONE - Mobile Phone Number	Char24		Mobile
	MONTHLY_RT - Monthly Rate	Num16.3		Monthly Rt
	NAME - Name	Char50		Name
	NAME_PREFIX - Name Prefix	Char4		Prefix
	NAME_SUFFIX - Name Suffix	Char15		Suffix
	OFFICER_CD - Officer Code	Char1	N	Officer Cd
	ORIG_HIRE_DT - Original Start Date	Date		Start Date
	PAID_FTE - Paid FTE	Num2.6		Paid FTE
	PAID_HRS - Hour Equivalent	Num4.0		Paid Hours
	PAYGROUP - Pay Group	Char3		Group
	PER_STATUS - Personnel Status	Char1	N	Per Status
	POSITION_ENTRY_DT - Position Entry Date	Date		Posn Dt
	POSITION_NBR - Position Number	Char8		Position
	POSITION_OVERRIDE - Override Position Data	Char1		Posn Ovrd
	PRIMARY_JOB_IND - Primary Job	Char1	N	Primary
	PROBATION_DT - Probation Date	Date		Probation Dates
	REG_TEMP - Regular/Temporary	Char1	N	Reg/Temp
	REHIRE_DT - Rehire Date	Date		Rehire Dt
	REPORTS_TO - Reports To Position Number	Char8		Reports To
	REVIEW_DT - Review Date	Date		Date
	SAL_ADMIN_PLAN - Salary Administration Plan	Char4		Sal Plan
	SECOND_LAST_NAME - Second Last Name	Char30		Second Last
	SERVICE_DT - Service Date	Date		Service Dt
	SHIFT - Regular Shift	Char1	N	Shift
	SHIFT_FACTOR - Shift Differential Factor	Num2.3		Shift Fctr
	SHIFT_RT - Shift Differential Rate	Num13.6		Shift Rate
	STD_HOURS - Standard Hours	Num5.2		Stnd Hrs/Wk
	STD_HRS_FREQUENCY - Standard Work Period	Char5		Work Period
	STEP - Step	Num2.0		Step
	STEP_ENTRY_DT - Step Entry Date	Date		Step Date
	TAX_LOCATION_CD - Tax Location Code	Char10		Tax Loc
	TENURE_STATUS - Tenure Status	Char3	N	Tenure St
	TERMINATION_DT - Termination Date	Date		Term Date
	UM_AFFILIATE_BU - Affiliate Business Units	Char47		Affiliate BU
	UM_CAMPUS_ADDRESS1 - Address	Char55		Address
	UM_COHORT - Cohort	Char1	N	Cohort
	UM_COLLEGE - College	Char10		College
	UM_COLLEGE_DESCR50 - College Descxription	Char50		College Descr50
	UM_COMP_FREQUENCY - Frequency	Char5		Freq
	UM_COMP_TIME_BAL - Ending Balance	SNm6.3		Ending Bal
	UM_CONTRACT_DESCR - Contract Description	Char30		Contract Descr
	UM_COURSE_CREDIT_1 - Course Credits 1	Num3.2		Course Credit1
	UM_COURSE_CREDIT_2 - Course Credits 2	Num3.2		Course Credit2
	UM_COURSE_CREDIT_3 - Course Credits 3	Num3.2		Course Credit3
	UM_COURSE_CREDIT_4 - Course Credits 4	Num3.2		Course Credit4
	UM_COURSE_CREDIT_5 - Course Credits 5	Num3.2		Course Credit5
	UM_COURSE_CREDIT_6 - Course Credits 6	Num3.2		Course Credit6
	UM_DEPT_MGR_EMPLID - Department Manager ID	Char11		Manager ID
	UM_DEPT_MGR_NAME - Department Manager Name	Char30		Manager
	UM_DEPT_MGR_POSN - Department Manager Position	Char8		Dept Mgr Posn
	UM_DEPT_MGR_TITLE - Department Manager Title	Char30		Dept Mgr Title
	UM_DISABILITY_BAL - Disability Balance	SNm7.6		Lv Hrs Bal
	UM_DISCIPLINE_CODE - Faculty Discipline code	Char6		Fac. Discipline
	UM_DISCIPL_DESCR50 - Description	Char50		Descr
	UM_EEO1CODE_DESCR - EEO1CODE Descripton	Char30		EEO1CODE Descr
	UM_EG_RANK_DESCR - EG Academic Rank Description	Char30		EG Rank Descr
	UM_EMAIL_BUS_ADDR - Business Email Address	Char70		Email
	UM_EMPL_CLASS_DESC - Empl Class Description	Char30		Empl Class Desc
	UM_EMPL_TYPE_DESCR - Empl Type Description	Char30		Empl Type Descr
	UM_EMP_STATUS_DESC - Empl Status Description	Char30		Emp Status Desc
	UM_EXPECTED_END_DT - Expected End Date	Date		Expected End Dt
	UM_FORMATTED_NAME - Formatted_Name	Char80		Formatted_Name
	UM_FTE_TOTAL - FTE Total	Num2.2		FTE
	UM_HOME_ADDRESS1 - Address	Char55		Address
	UM_HOME_ADDRESS2 - Address	Char55		Address
	UM_HOME_CITY - City	Char30		City
	UM_HOME_COUNTRY - Home Country	Char3		Cntry
	UM_HOME_POSTAL - Postal Code	Char12		Postal
	UM_HOME_STATE - State	Char6		St
	UM_IPEDSCODE_DESCR - IPEDSSCODE Description	Char30		IPEDSCODE Descr
	UM_JOBCODE_DESCR - Jobcode Description	Char30		Jobcode Descr
	UM_LAST_CHECK_DT - Last Check Date	Date		Last Date
	UM_LAST_PAY_END_DT - Last Pay End Date	Date		Last Pay End Da
	UM_LOCATION_DESCR - Location Description	Char30		Location Descr
	UM_MAIL_ADDRESS1 - Address	Char55		Address
	UM_MAIL_ADDRESS2 - Address	Char55		Address
	UM_OFFICER_DESCR - Officer Code Description	Char30		Officer CD Desc
	UM_OVRRIDE_ABBR - Manual Override ABBR	Char1		Override ABBR
	UM_PATFA_CAL_YR_1 - Patfa Calendar Year 1	Num4.0		Patfa Year 1
	UM_PATFA_CAL_YR_2 - Patfa Calendar Year 2	Num4.0		Patfa Year 2
	UM_PATFA_CAL_YR_3 - Patfa Calendar Year 3	Num4.0		Patfa Year 3
	UM_PATFA_CAL_YR_4 - Patfa Calendar Year 4	Num4.0		Patfa Year 4
	UM_PATFA_CAL_YR_5 - Patfa Calendar Year 5	Num4.0		Patfa Year 5
	UM_PATFA_CAL_YR_6 - Patfa Calendar Year 6	Num4.0		Patfa Year 6
	UM_PATFA_TERM_1 - Semester 1	Char1	N	Semester 1
	UM_PATFA_TERM_2 - Semester 2	Char1	N	Semester 2
	UM_PATFA_TERM_3 - Semester 3	Char1	N	Semester 3
	UM_PATFA_TERM_4 - Semester 4	Char1	N	Semester 4
	UM_PATFA_TERM_5 - Semester 5	Char1	N	Semester 5
	UM_PATFA_TERM_6 - Semester 6	Char1	N	Semester 6
	UM_POSITION_DESC - Position Description	Char254		Position Descr
	UM_POSITION_TITLE - Position Title	Char30		Title
	UM_POS_BEGIN_DTE - Position Contract Begin Date	Date		Pos Begin Date
	UM_POS_CNTRCT_TYPE - Position Contract_type	Char3		Pos Cntrct Type
	UM_POS_CONTRTSTATS - Contract Status	Char1		Status
	UM_POS_EXP_ENDDTE - Position contract exp end date	Date		pos exp enddate
	UM_POS_PTS - SECCP Points	Num4.0		SECCP Pts
	UM_PREFERRED_NAME - Preferred Name	Char80		Preferred Name
	UM_PRIM_FUNCTION - Primary Function	Char1		Prim Function
	UM_PRIM_FUNC_DESC - Primary Function Description	Char30		Prim Func Descr
	UM_RED_CIRCLE_IND - Red Circle Indicator	Char1		Indicator
	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 - Reportst To ID	Char11		Rpts To ID
	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_LOC - UM_REPORTS_TO_LOC	Char10		UM_RPTS_TO_LOC
	UM_REPORTS_TO_NAME - Reports to Name	Char50		Reports To
	UM_REP_TO_CMP_ADDR - Reports to Campus Address	Char55		Rep to Cmp Addr
	UM_REP_TO_LOC_DESC - Reports to Location Descr	Char30		Rep to Loc Desc
	UM_SALBASE - Salbase	Num16.2		Salbase
	UM_SERVICE_CREDITS - Service Credits	Num2.0		Service Credits
	UM_SUPV_LVL_ID - Supervisor Level	Char1		Supervisor Lvl
	UM_SUP_LVL_ID_DESC - Supervisor Level ID Level	Char30		Sup Lvl ID Desc
	UM_TENURE_DESCR - Tenure Description	Char30		Tenure Descr
	UM_UNION_DESCR - Union Code Description	Char30		Union CD Descr
	UNION_CD - Union Code	Char3		Union Code
	UNION_SENIORITY_DT - Union Seniority Date	Date		Union Date
	WORK_PHONE - Work Phone #	Char24		Work Phone
	YRS_SERVICE - Yrs of Service	Num3.2		Yrs Service

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 (this page)

Campus Solutions Reporting Database

UM_FGLEMPLOY_VW

UM_FGLEMPL_ACVW

UM_FGLALLJOB_VW

Finance Database

UM_FGLEMPLOY_VW

UM_FGLALLJOB_VW

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.

Reporting Tip

Use the following criteria with this table to define active Regular and PATFA (PT Faculty) employees

Headcount And FTE Without Distributions
(For FTE Use FTE_TOTAL)

Empl Class IN ('1','2','6','7','8','9')
AND Empl Status IN ('A','P')
AND Reg Temp = 'R' OR (Reg Temp = 'T' AND Union Cd = "009')
(AND Job Indicator = 'P'
OR Primary Job Flag =’Y’)
AND Action Reason <> 'PWB'
AND Annual Benefit Base Rate > 0

SQL code for UM_F_EMPL_VW

SELECT A.EMPLID,
       A.EMPL_RCD,
       A.BUSINESS_UNIT,
       A.EFFDT_FROM,
       A.EFFSEQ_WRK,
       A.ACTION,
       A.ACTION_DT,
       A.ACTION_REASON,
       A.LEAVE_HRS_BALANCE,
       A.UM_HOME_ADDRESS1,
       A.UM_HOME_ADDRESS2,
       A.UM_HOME_CITY,
       A.UM_HOME_STATE,
       A.UM_HOME_POSTAL,
       A.UM_HOME_COUNTRY,
       A.HOME_PHONE,
       A.UM_MAIL_ADDRESS1,
       A.UM_MAIL_ADDRESS2,
       A.MAIL_CITY,
       A.MAIL_STATE,
       A.MAIL_POSTAL,
       A.MAIL_COUNTRY,
       A.UM_CAMPUS_ADDRESS1,
       A.ANNL_BENEF_BASE_RT,
       A.UM_OVRRIDE_ABBR,
       A.ANNUAL_RT,
       A.BUSINESS_TITLE,
       A.CMPNY_SENIORITY_DT,
       A.COMP_FREQUENCY,
       A.UM_COMP_FREQUENCY,
       A.UM_COMP_TIME_BAL,
       A.COMPANY,
       A.COMPRATE,
       A.CONTRACT_BEGIN_DT,
       A.CONTRCT_EXP_END_DT,
       A.CONTRACT_LENGTH,
       A.CONTRACT_TYPE,
       A.UM_CONTRACT_DESCR,
       A.CONTRACT_STATUS,
       A.DAILY_RT,
       A.DEPT_ENTRY_DT,
       A.DEPTID,
       A.DEPT_DESCR,
       A.UM_DEPT_MGR_EMPLID,
       A.UM_DEPT_MGR_NAME,
       A.UM_DEPT_MGR_POSN,
       A.UM_DEPT_MGR_TITLE,
       A.UM_DISABILITY_BAL,
       A.UM_DISCIPLINE_CODE,
       A.UM_DISCIPL_DESCR50,
       A.EARNS_DIST_TYPE,
       A.EEO_CLASS,
       A.EEO1CODE,
       A.UM_EEO1CODE_DESCR,
       A.EG_ACADEMIC_RANK,
       A.UM_EG_RANK_DESCR,
       A.EG_GRANTED_DT,
       A.TENURE_STATUS,
       A.UM_TENURE_DESCR,
       A.EG_TENURE_HOME,
       A.EG_ORIG_TRACK_DT,
       A.EG_MAND_REVW_DT,
       A.ELIG_CONFIG1,
       A.ELIG_CONFIG2,
       A.ELIG_CONFIG3,
       A.ELIG_CONFIG4,
       A.ELIG_CONFIG5,
       A.ELIG_CONFIG6,
       A.ELIG_CONFIG7,
       A.ELIG_CONFIG8,
       A.ELIG_CONFIG9,
       A.UM_EMAIL_BUS_ADDR,
       A.EMPL_CLASS,
       A.UM_EMPL_CLASS_DESC,
       A.EMPL_STATUS,
       A.UM_EMP_STATUS_DESC,
       A.EMPL_TYPE,
       A.UM_EMPL_TYPE_DESCR,
       A.FICA_STATUS_EE,
       A.FIRST_NAME,
       A.FLSA_STATUS,
       A.UM_FORMATTED_NAME,
       a.UM_PREFERRED_NAME,
       A.UM_FTE_TOTAL,
       A.FTE,
       A.FULL_PART_TIME,
       A.FUNCTION_CD,
       A.GL_PAY_TYPE,
       A.GRADE,
       A.GRADE_ENTRY_DT,
       A.HIRE_DT,
       A.HOURLY_RT,
       A.IPEDSSCODE,
       A.UM_IPEDSCODE_DESCR,
       A.JOB_ENTRY_DT,
       A.JOB_INDICATOR,
       A.JOBCODE,
       A.UM_JOBCODE_DESCR,
       A.JOB_FAMILY,
       A.PRIMARY_JOB_IND,
       A.UM_LAST_CHECK_DT,
       A.LAST_DATE_WORKED,
       A.LAST_INCREASE_DT,
       A.LAST_NAME,
       A.LOCATION,
       A.UM_LOCATION_DESCR,
       A.MIDDLE_NAME,
       A.MONTHLY_RT,
       A.NAME,
       A.NAME_PREFIX,
       A.NAME_SUFFIX,
       A.OFFICER_CD,
       A.UM_OFFICER_DESCR,
       A.ORIG_HIRE_DT,
       A.PAID_FTE,
       A.PAID_HRS,
       A.UM_SERVICE_CREDITS,
       A.UM_PATFA_CAL_YR_1,
       A.UM_PATFA_TERM_1,
       A.UM_COURSE_CREDIT_1,
       A.UM_PATFA_CAL_YR_2,
       A.UM_PATFA_TERM_2,
       A.UM_COURSE_CREDIT_2,
       A.UM_PATFA_CAL_YR_3,
       A.UM_PATFA_TERM_3,
       A.UM_COURSE_CREDIT_3,
       A.UM_PATFA_CAL_YR_4,
       A.UM_PATFA_TERM_4,
       A.UM_COURSE_CREDIT_4,
       A.UM_PATFA_CAL_YR_5,
       A.UM_PATFA_TERM_5,
       A.UM_COURSE_CREDIT_5,
       A.UM_PATFA_CAL_YR_6,
       A.UM_PATFA_TERM_6,
       A.UM_COURSE_CREDIT_6,
       A.PAYGROUP,
       A.PER_STATUS,
       A.POSITION_NBR,
       A.UM_POSITION_DESC,
       A.PROBATION_DT,
       A.UM_RED_CIRCLE_IND,
       A.REG_TEMP,
       A.REHIRE_DT,
       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.REVIEW_DT,
       A.SAL_ADMIN_PLAN,
       A.UM_SALBASE,
       A.SECOND_LAST_NAME,
       A.SERVICE_DT,
       A.SHIFT,
       A.SHIFT_FACTOR,
       A.SHIFT_RT,
       A.STD_HOURS,
       A.STEP,
       A.STEP_ENTRY_DT,
       A.TERMINATION_DT,
       A.UM_EXPECTED_END_DT,
       A.UM_PRIM_FUNCTION,
       A.UM_PRIM_FUNC_DESC,
       A.UM_SUPV_LVL_ID,
       A.UM_SUP_LVL_ID_DESC,
       A.UNION_CD,
       A.UM_UNION_DESCR,
       A.UNION_SENIORITY_DT,
       A.WORK_PHONE,
       A.YRS_SERVICE,
       A.POSITION_OVERRIDE,
       A.POSITION_ENTRY_DT,
       A.ESTABID,
       A.STD_HRS_FREQUENCY,
       A.TAX_LOCATION_CD,
       A.UM_POSITION_TITLE,
       A.UM_POS_PTS,
       A.UM_COLLEGE,
       A.UM_COLLEGE_DESCR50,
       A.UM_POS_CNTRCT_TYPE,
       A.UM_POS_BEGIN_DTE,
       A.UM_POS_EXP_ENDDTE,
       A.UM_POS_CONTRTSTATS,
       A.UM_REP_TO_CMP_ADDR,
       A.UM_REPORTS_TO_LOC,
       A.UM_REP_TO_LOC_DESC,
       A.UM_LAST_PAY_END_DT,
       A.UM_AFFILIATE_BU,
       A.MOBILE_PHONE,
       a.um_cohort
  FROM ps_um_f_employee A
 WHERE USER = 'SYSADM'
UNION
SELECT A.EMPLID,
       A.EMPL_RCD,
       A.BUSINESS_UNIT,
       A.EFFDT_FROM,
       A.EFFSEQ_WRK,
       A.ACTION,
       A.ACTION_DT,
       A.ACTION_REASON,
       A.LEAVE_HRS_BALANCE,
       A.UM_HOME_ADDRESS1,
       A.UM_HOME_ADDRESS2,
       A.UM_HOME_CITY,
       A.UM_HOME_STATE,
       A.UM_HOME_POSTAL,
       A.UM_HOME_COUNTRY,
       A.HOME_PHONE,
       A.UM_MAIL_ADDRESS1,
       A.UM_MAIL_ADDRESS2,
       A.MAIL_CITY,
       A.MAIL_STATE,
       A.MAIL_POSTAL,
       A.MAIL_COUNTRY,
       A.UM_CAMPUS_ADDRESS1,
       A.ANNL_BENEF_BASE_RT,
       A.UM_OVRRIDE_ABBR,
       A.ANNUAL_RT,
       A.BUSINESS_TITLE,
       A.CMPNY_SENIORITY_DT,
       A.COMP_FREQUENCY,
       A.UM_COMP_FREQUENCY,
       A.UM_COMP_TIME_BAL,
       A.COMPANY,
       A.COMPRATE,
       A.CONTRACT_BEGIN_DT,
       A.CONTRCT_EXP_END_DT,
       A.CONTRACT_LENGTH,
       A.CONTRACT_TYPE,
       A.UM_CONTRACT_DESCR,
       A.CONTRACT_STATUS,
       A.DAILY_RT,
       A.DEPT_ENTRY_DT,
       A.DEPTID,
       A.DEPT_DESCR,
       A.UM_DEPT_MGR_EMPLID,
       A.UM_DEPT_MGR_NAME,
       A.UM_DEPT_MGR_POSN,
       A.UM_DEPT_MGR_TITLE,
       A.UM_DISABILITY_BAL,
       A.UM_DISCIPLINE_CODE,
       A.UM_DISCIPL_DESCR50,
       A.EARNS_DIST_TYPE,
       A.EEO_CLASS,
       A.EEO1CODE,
       A.UM_EEO1CODE_DESCR,
       A.EG_ACADEMIC_RANK,
       A.UM_EG_RANK_DESCR,
       A.EG_GRANTED_DT,
       A.TENURE_STATUS,
       A.UM_TENURE_DESCR,
       A.EG_TENURE_HOME,
       A.EG_ORIG_TRACK_DT,
       A.EG_MAND_REVW_DT,
       A.ELIG_CONFIG1,
       A.ELIG_CONFIG2,
       A.ELIG_CONFIG3,
       A.ELIG_CONFIG4,
       A.ELIG_CONFIG5,
       A.ELIG_CONFIG6,
       A.ELIG_CONFIG7,
       A.ELIG_CONFIG8,
       A.ELIG_CONFIG9,
       A.UM_EMAIL_BUS_ADDR,
       A.EMPL_CLASS,
       A.UM_EMPL_CLASS_DESC,
       A.EMPL_STATUS,
       A.UM_EMP_STATUS_DESC,
       A.EMPL_TYPE,
       A.UM_EMPL_TYPE_DESCR,
       A.FICA_STATUS_EE,
       A.FIRST_NAME,
       A.FLSA_STATUS,
       A.UM_FORMATTED_NAME,
       a.UM_PREFERRED_NAME,
       A.UM_FTE_TOTAL,
       A.FTE,
       A.FULL_PART_TIME,
       A.FUNCTION_CD,
       A.GL_PAY_TYPE,
       A.GRADE,
       A.GRADE_ENTRY_DT,
       A.HIRE_DT,
       A.HOURLY_RT,
       A.IPEDSSCODE,
       A.UM_IPEDSCODE_DESCR,
       A.JOB_ENTRY_DT,
       A.JOB_INDICATOR,
       A.JOBCODE,
       A.UM_JOBCODE_DESCR,
       A.JOB_FAMILY,
       A.PRIMARY_JOB_IND,
       A.UM_LAST_CHECK_DT,
       A.LAST_DATE_WORKED,
       A.LAST_INCREASE_DT,
       A.LAST_NAME,
       A.LOCATION,
       A.UM_LOCATION_DESCR,
       A.MIDDLE_NAME,
       A.MONTHLY_RT,
       A.NAME,
       A.NAME_PREFIX,
       A.NAME_SUFFIX,
       A.OFFICER_CD,
       A.UM_OFFICER_DESCR,
       A.ORIG_HIRE_DT,
       A.PAID_FTE,
       A.PAID_HRS,
       A.UM_SERVICE_CREDITS,
       A.UM_PATFA_CAL_YR_1,
       A.UM_PATFA_TERM_1,
       A.UM_COURSE_CREDIT_1,
       A.UM_PATFA_CAL_YR_2,
       A.UM_PATFA_TERM_2,
       A.UM_COURSE_CREDIT_2,
       A.UM_PATFA_CAL_YR_3,
       A.UM_PATFA_TERM_3,
       A.UM_COURSE_CREDIT_3,
       A.UM_PATFA_CAL_YR_4,
       A.UM_PATFA_TERM_4,
       A.UM_COURSE_CREDIT_4,
       A.UM_PATFA_CAL_YR_5,
       A.UM_PATFA_TERM_5,
       A.UM_COURSE_CREDIT_5,
       A.UM_PATFA_CAL_YR_6,
       A.UM_PATFA_TERM_6,
       A.UM_COURSE_CREDIT_6,
       A.PAYGROUP,
       A.PER_STATUS,
       A.POSITION_NBR,
       A.UM_POSITION_DESC,
       A.PROBATION_DT,
       A.UM_RED_CIRCLE_IND,
       A.REG_TEMP,
       A.REHIRE_DT,
       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.REVIEW_DT,
       A.SAL_ADMIN_PLAN,
       A.UM_SALBASE,
       A.SECOND_LAST_NAME,
       A.SERVICE_DT,
       A.SHIFT,
       A.SHIFT_FACTOR,
       A.SHIFT_RT,
       A.STD_HOURS,
       A.STEP,
       A.STEP_ENTRY_DT,
       A.TERMINATION_DT,
       A.UM_EXPECTED_END_DT,
       A.UM_PRIM_FUNCTION,
       A.UM_PRIM_FUNC_DESC,
       A.UM_SUPV_LVL_ID,
       A.UM_SUP_LVL_ID_DESC,
       A.UNION_CD,
       A.UM_UNION_DESCR,
       A.UNION_SENIORITY_DT,
       A.WORK_PHONE,
       A.YRS_SERVICE,
       A.POSITION_OVERRIDE,
       A.POSITION_ENTRY_DT,
       A.ESTABID,
       A.STD_HRS_FREQUENCY,
       A.TAX_LOCATION_CD,
       A.UM_POSITION_TITLE,
       A.UM_POS_PTS,
       A.UM_COLLEGE,
       A.UM_COLLEGE_DESCR50,
       A.UM_POS_CNTRCT_TYPE,
       A.UM_POS_BEGIN_DTE,
       A.UM_POS_EXP_ENDDTE,
       A.UM_POS_CONTRTSTATS,
       A.UM_REP_TO_CMP_ADDR,
       A.UM_REPORTS_TO_LOC,
       A.UM_REP_TO_LOC_DESC,
       A.UM_LAST_PAY_END_DT,
       A.UM_AFFILIATE_BU,
       A.MOBILE_PHONE,
       A.UM_COHORT
  FROM ps_um_f_employee   A,
       ps_scrty_tbl_dept  SEC,
       psoprdefn          b,
       ps_um_orcle_ps_xrf c
 WHERE B.OPRID = c.key_field_name_02
   AND USER <> 'SYSADM'
   AND USER = c.key_field_name_01
   AND SEC.ACCESS_CD = 'Y'
   AND SEC.ROWSECCLASS = B.ROWSECCLASS
   AND EXISTS (SELECT 'X'
          FROM PSTREENODE TN
         WHERE TN.SETID = SEC.SETID
           AND TN.TREE_NAME = 'DEPT_SECURITY'
           AND TN.EFFDT = SEC.TREE_EFFDT
           AND TN.TREE_NODE = A.DEPTID
           AND TN.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND
               SEC.TREE_NODE_NUM_END);

SQR for PS_UM_F_EMPLOYEE is on s:\hrms\sqr\prod\umflater.sqr.