DARTS : UM_F_EMPLOYE_VW - Employee Flat Table

This is the HR Master Flat File Extract which six tables (see information at right) are derived from. It contains the most current last job record (or records) for each employee with an array of demographic details, and includes restricted data.

Key	Record.Fieldname	Format	XLAT	Heading Text	Source
	ACTION - Action	Char3	N	Action	UM_F__EMPLOYEE → JOB - EE Job History
	ACTION_DT - Action Date	Date		Action Date	UM_F__EMPLOYEE → JOB - EE Job History
	ACTION_REASON - Reason Code	Char3		Reason	UM_F__EMPLOYEE → JOB - EE Job History
	AGE_YEARS - Age Years	Num3.0		Years
	ANNL_BENEF_BASE_RT - Annual Benefits Base Rate	Num16.3		Ann Ben Rt	UM_F__EMPLOYEE → JOB - EE Job History
	ANNUAL_RT - Annual Rate	Num16.3		Annual Rt	UM_F__EMPLOYEE → JOB - EE Job History
	BAS_ACTION - Benefits Administration Action	Char3		BAS Action	UM_F__EMPLOYEE → JOB - EE Job History
	BAS_GROUP_ID - BAS Group ID	Char3		BAS Group	UM_F__EMPLOYEE → JOB - EE Job History
	BENEFIT_PROGRAM - Benefit Program	Char3		Ben Progrm
	BEN_STATUS - Benefits Employee Status	Char4	N	Ben Status	UM_F__EMPLOYEE → JOB - EE Job History
	BIRTHDATE - Date of Birth	Date		Birthdate
	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	UM_F__EMPLOYEE → JOB - EE Job History
	COMPRATE - Compensation Rate	Num13.6		Comp Rate	UM_F__EMPLOYEE → JOB - EE Job History
	COMP_FREQUENCY - Compensation Frequency	Char5		Comp Freq	UM_F__EMPLOYEE → JOB - EE Job History
	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	UM_F__EMPLOYEE → JOB - EE Job History
→	DEPTID - Department	Char10		Dept ID	UM_F__EMPLOYEE → JOB - EE Job History
	DEPT_DESCR - Department Description	Char30		Dept Desc
	DEPT_ENTRY_DT - Department Entry Date	Date		Dept Date	UM_F__EMPLOYEE → JOB - EE Job History
	DISABLED - Disabled	Char1		Disabled
	DISABLED_VET - Disabled Veteran	Char1		Disab Vet
	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	UM_F__EMPLOYEE → JOB - EE Job History
	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 - Eligibility Config Field 9	Char10		Elig Fld 9
→	EMPLID - Empl ID	Char11		ID	UM_F__EMPLOYEE → JOB - EE Job History
	EMPL_CLASS - Employee Classification	Char3	N	Empl Class	UM_F__EMPLOYEE → JOB - EE Job History
→	EMPL_RCD - Empl Record	Num3.0		Empl Record	UM_F__EMPLOYEE → JOB - EE Job History
	EMPL_STATUS - Payroll Status	Char1	N	Pay Status	UM_F__EMPLOYEE → JOB - EE Job History
	EMPL_TYPE - Employee Type	Char1	N	Type	UM_F__EMPLOYEE → JOB - EE Job History
	ESTABID - Establishment ID	Char12		Estab ID	UM_F__EMPLOYEE → JOB - EE Job History
	ETHNIC_CATEGORY - Ethnic Category	Char1	N	Ethnic Cat
	ETHNIC_GROUP - Ethnic Group	Char1	N	Ethnic Grp
	ETHNIC_GRP_CD - Ethnic Group	Char8		Ethnic Grp
	FICA_STATUS_EE - FICA Status-Employee	Char1	N	FICA EE	UM_F__EMPLOYEE → JOB - EE Job History
	FIRST_NAME - First Name	Char30		First Name
	FLSA_STATUS - FLSA Status	Char1	N	FLSA Stat	UM_F__EMPLOYEE → JOB - EE Job History
	FTE - FTE	Num2.6		FTE	UM_F__EMPLOYEE → JOB - EE Job History
	FULL_PART_TIME - Full/Part Time	Char1	N	Full/Part	UM_F__EMPLOYEE → JOB - EE Job History
	FUNCTION_CD - Function Code	Char2		Functn Cd
	GL_PAY_TYPE - General Ledger Pay Type	Char6		GL Pay Type
	GRADE - Salary Grade	Char3		Grade	UM_F__EMPLOYEE → JOB - EE Job History
	GRADE_ENTRY_DT - Grade Entry Date	Date		Grade Date	UM_F__EMPLOYEE → JOB - EE Job History
	HIGHEST_EDUC_LVL - Highest Education Level	Char2	N	Hi Educ Lv
	HIRE_DT - First Start Date	Date		Start Date	UM_F__EMPLOYEE → JOB - EE Job History
	HISP_LATINO - Person is Hispanic or Latino	Char1		Hisp or Latin
	HOME_PHONE - Home Phone #	Char24		Phone
	HOURLY_RT - Hourly Rate	Num13.6		Hrly Rate	UM_F__EMPLOYEE → JOB - EE Job History
	IPEDSSCODE - IPEDS-S Job Category	Char1	N	IPEDS Cd
	JOBCODE - Job Code	Char6		Job Code	UM_F__EMPLOYEE → JOB - EE Job History
	JOB_ENTRY_DT - Job Entry Date	Date		Entry Date	UM_F__EMPLOYEE → JOB - EE Job History
	JOB_FAMILY - Job Family	Char6		Job Family
	JOB_INDICATOR - Job Indicator	Char1	N	Indicator	UM_F__EMPLOYEE → JOB - EE Job History
	LAST_DATE_WORKED - Last Date Worked	Date		Last Date	UM_F__EMPLOYEE → JOB - EE Job History
	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	UM_F__EMPLOYEE → JOB - EE Job History
	MAIL_CITY - City	Char30		City
	MAIL_COUNTRY - Country	Char3		Cntry
	MAIL_POSTAL - Postal Code	Char12		Postal
	MAIL_STATE - State	Char6		St
	MAR_STATUS - Marital Status	Char1	N	Mar Status
	MIDDLE_NAME - Middle Name	Char30		Middle
	MILITARY_STATUS - Military Status	Char1	N	Mil Status
	MIL_DISCHRG_DT_USA - Military Discharge Date	Date		Mil Dischrg Dt
	MOBILE_PHONE - Mobile Phone Number	Char24		Mobile
	MONTHLY_RT - Monthly Rate	Num16.3		Monthly Rt	UM_F__EMPLOYEE → JOB - EE Job History
	NAME - Name	Char50		Name
	NAME_PREFIX - Name Prefix	Char4		Prefix
	NAME_SUFFIX - Name Suffix	Char15		Suffix
	NATIONAL_ID - National ID	Char20		NID
	OFFICER_CD - Officer Code	Char1	N	Officer Cd	UM_F__EMPLOYEE → JOB - EE Job History
	ORIG_HIRE_DT - Original Start Date	Date		Start Date
	PAID_FTE - Paid FTE	Num2.6		Paid FTE	UM_F__EMPLOYEE → JOB - EE Job History
	PAID_HRS - Hour Equivalent	Num4.0		Paid Hours	UM_F__EMPLOYEE → JOB - EE Job History
	PAYGROUP - Pay Group	Char3		Group	UM_F__EMPLOYEE → JOB - EE Job History
	PER_STATUS - Personnel Status	Char1	N	Per Status
	POSITION_ENTRY_DT - Position Entry Date	Date		Posn Dt	UM_F__EMPLOYEE → JOB - EE Job History
	POSITION_NBR - Position Number	Char8		Position	UM_F__EMPLOYEE → JOB - EE Job History
	POSITION_OVERRIDE - Override Position Data	Char1		Posn Ovrd	UM_F__EMPLOYEE → JOB - EE Job History
	PRIMARY_JOB_IND - Primary Job	Char1	N	Primary
	PROBATION_DT - Probation Date	Date		Probation Dates
	REG_TEMP - Regular/Temporary	Char1	N	Reg/Temp	UM_F__EMPLOYEE → JOB - EE Job History
	REHIRE_DT - Rehire Date	Date		Rehire Dt
	REPORTS_TO - Reports To Position Number	Char8		Reports To	UM_F__EMPLOYEE → JOB - EE Job History
	REVIEW_DT - Review Date	Date		Date
→	ROLENAME - Role Name	Char30		Role Name
	SAL_ADMIN_PLAN - Salary Administration Plan	Char4		Sal Plan	UM_F__EMPLOYEE → JOB - EE Job History
	SECOND_LAST_NAME - Second Last Name	Char30		Second Last
	SERVICE_DT - Service Date	Date		Service Dt
	SEX - Gender	Char1	N	Sex
	SHIFT - Regular Shift	Char1	N	Shift	UM_F__EMPLOYEE → JOB - EE Job History
	SHIFT_FACTOR - Shift Differential Factor	Num2.3		Shift Fctr	UM_F__EMPLOYEE → JOB - EE Job History
	SHIFT_RT - Shift Differential Rate	Num13.6		Shift Rate	UM_F__EMPLOYEE → JOB - EE Job History
	STD_HOURS - Standard Hours	Num5.2		Stnd Hrs/Wk	UM_F__EMPLOYEE → JOB - EE Job History
	STD_HRS_FREQUENCY - Standard Work Period	Char5		Work Period	UM_F__EMPLOYEE → JOB - EE Job History
	STEP - Step	Num2.0		Step	UM_F__EMPLOYEE → JOB - EE Job History
	STEP_ENTRY_DT - Step Entry Date	Date		Step Date	UM_F__EMPLOYEE → JOB - EE Job History
	TAX_LOCATION_CD - Tax Location Code	Char10		Tax Loc	UM_F__EMPLOYEE → JOB - EE Job History
	TENURE_STATUS - Tenure Status	Char3	N	Tenure St
	TERMINATION_DT - Termination Date	Date		Term Date	UM_F__EMPLOYEE → JOB - EE Job History
	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_DENTAL_CVRCD - Dental Coverage Code	Char2		Dental Covr Cd
	UM_DENTAL_CVRDESC - Dental Coverage Description	Char30		Dental Covr Des
	UM_DENTAL_DESCR - Dental Plan Description	Char30		Dental Pln Desc
	UM_DENTAL_PLAN - Dental Plan	Char6		Plan
	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_ETHNIC_GRP_DESC - Ethnic Group Cd Description	Char30		Eth Grp Cd Desc
	UM_ETH_GROUP_DESC - Ethnic Group Description	Char30		Eth Group Descr
	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_HEALTH_CVRCD - Health Coverage Code	Char2		Health Covr Cd
	UM_HEALTH_CVRDESC - Health Coverge Description	Char30		Health Cov Desc
	UM_HEALTH_DESCR - Health Plan Description	Char30		Hlth Plan Desc
	UM_HEALTH_PLAN - Health Plan	Char6		Plan
	UM_HIGH_ED_LV_DESC - Highest Edcucation Level	Char30		Highest Ed Lvl
	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_MAR_STATU_DESCR - Marital Status Description	Char30		Marital Status
	UM_MILITARY_DESCR - Military Status Description	Char30		Military Descr
	UM_MULTIPLE_ETHNIC - Multiple Ethnic Flag	Char1		Multiple Ethnic
	UM_NID_COUNTRY - NID Country	Char3		Cntry
	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	UM_F__EMPLOYEE → JOB - EE Job History
	UNION_SENIORITY_DT - Union Seniority Date	Date		Union Date	UM_F__EMPLOYEE → JOB - EE Job History
	US_WORK_ELIGIBILTY - Eligible to Work in U.S.	Char1		US Wk Elig
	WORK_PHONE - Work Phone #	Char24		Work Phone
	YRS_SERVICE - Yrs of Service	Num3.2		Yrs Service

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 page) - This is the parent table for all views below.

UM_F_EMPL_VW - Employee Flat Table

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.

SQL code for UM_F_EMPLOYE_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,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.AGE_YEARS
         ELSE
          0
       END,
       A.ANNL_BENEF_BASE_RT,
       A.UM_OVRRIDE_ABBR,
       A.ANNUAL_RT,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.BAS_ACTION
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.BAS_GROUP_ID
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.BEN_STATUS
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.BENEFIT_PROGRAM
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          a.BIRTHDATE
         ELSE
          TO_DATE('01/01/1900', 'MM/DD/YYYY')
       END,
       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,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_DENTAL_PLAN
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_DENTAL_DESCR
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_DENTAL_CVRCD
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_DENTAL_CVRDESC
         ELSE
          '*'
       END,
       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,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.DISABLED
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.DISABLED_VET
         ELSE
          '*'
       END,
       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,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.ETHNIC_GROUP
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_ETHNIC_GRP_DESC
         ELSE
          '*'
       END,
       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,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_HEALTH_PLAN
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_HEALTH_DESCR
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_HEALTH_CVRCD
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_HEALTH_CVRDESC
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.HIGHEST_EDUC_LVL
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_HIGH_ED_LV_DESC
         ELSE
          '*'
       END,
       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,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.MAR_STATUS
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_MAR_STATU_DESCR
         ELSE
          '*'
       END,
       A.MIDDLE_NAME,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.MILITARY_STATUS
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_MILITARY_DESCR
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          a.MIL_DISCHRG_DT_USA
         ELSE
          TO_DATE('01/01/1900', 'MM/DD/YYYY')
       END,
       A.MONTHLY_RT,
       A.NAME,
       A.NAME_PREFIX,
       A.NAME_SUFFIX,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.NATIONAL_ID
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_NID_COUNTRY
         ELSE
          '*'
       END,
       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,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.SEX
         ELSE
          '*'
       END,
       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,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.US_WORK_ELIGIBILTY
         ELSE
          '*'
       END,
       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,
       BETA.ROLENAME,
       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,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.HISP_LATINO
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_MULTIPLE_ETHNIC
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.ETHNIC_GRP_CD
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.ETHNIC_CATEGORY
         ELSE
          '*'
       END,
       CASE
         WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_ETH_GROUP_DESC
         ELSE
          '*'
       END,
       A.UM_AFFILIATE_BU,
       A.MOBILE_PHONE,
       a.um_cohort
  FROM ps_um_f_employee A,
       (SELECT 'R_F_EMPLOYE_VW' AS rolename
          FROM dual
        UNION
        SELECT 'X'
          FROM dual) BETA
 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,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.AGE_YEARS
         ELSE
          0
       END,
       A.ANNL_BENEF_BASE_RT,
       A.UM_OVRRIDE_ABBR,
       A.ANNUAL_RT,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.BAS_ACTION
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.BAS_GROUP_ID
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.BEN_STATUS
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.BENEFIT_PROGRAM
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          a.BIRTHDATE
         ELSE
          TO_DATE('01/01/1900', 'MM/DD/YYYY')
       END,
       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,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_DENTAL_PLAN
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_DENTAL_DESCR
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_DENTAL_CVRCD
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_DENTAL_CVRDESC
         ELSE
          '*'
       END,
       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,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.DISABLED
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.DISABLED_VET
         ELSE
          '*'
       END,
       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,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.ETHNIC_GROUP
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_ETHNIC_GRP_DESC
         ELSE
          '*'
       END,
       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,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_HEALTH_PLAN
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_HEALTH_DESCR
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_HEALTH_CVRCD
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_HEALTH_CVRDESC
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.HIGHEST_EDUC_LVL
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_HIGH_ED_LV_DESC
         ELSE
          '*'
       END,
       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,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.MAR_STATUS
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_MAR_STATU_DESCR
         ELSE
          '*'
       END,
       A.MIDDLE_NAME,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.MILITARY_STATUS
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_MILITARY_DESCR
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          a.MIL_DISCHRG_DT_USA
         ELSE
          TO_DATE('01/01/1900', 'MM/DD/YYYY')
       END,
       A.MONTHLY_RT,
       A.NAME,
       A.NAME_PREFIX,
       A.NAME_SUFFIX,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.NATIONAL_ID
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_NID_COUNTRY
         ELSE
          '*'
       END,
       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,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.SEX
         ELSE
          '*'
       END,
       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,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.US_WORK_ELIGIBILTY
         ELSE
          '*'
       END,
       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,
       rol.rolename,
       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,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.HISP_LATINO
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_MULTIPLE_ETHNIC
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.ETHNIC_GRP_CD
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.ETHNIC_CATEGORY
         ELSE
          '*'
       END,
       CASE
         WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
          A.UM_ETH_GROUP_DESC
         ELSE
          '*'
       END,
       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,
       (SELECT rlin.roleuser AS roleuser, rlin.rolename AS rolename
          FROM psroleuser rlin
         WHERE rlin.rolename = 'R_F_EMPLOYE_VW'
           AND rlin.roleuser =
               (SELECT KEY_FIELD_NAME_02
                  FROM ps_um_orcle_ps_xrf
                 WHERE key_field_name_01 = USER)
        UNION
        SELECT '*', '*'
          FROM dual
         WHERE NOT EXISTS (SELECT 'X'
                  FROM psroleuser rx
                 WHERE rx.rolename = 'R_F_EMPLOYE_VW'
                   AND rx.roleuser =
                       (SELECT KEY_FIELD_NAME_02
                          FROM ps_um_orcle_ps_xrf
                         WHERE key_field_name_01 = USER))) rol
 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.