Created by Corina C Larsen, last modified by Miki Yanagi on Nov 02, 2021
| 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 | ||
| AGE_YEARS - Age Years | Num3.0 | Years | ||
| ANNL_BENEF_BASE_RT - Annual Benefits Base Rate | Num16.3 | Ann Ben Rt | ||
| ANNUAL_RT - Annual Rate | Num16.3 | Annual Rt | ||
| BAS_ACTION - Benefits Administration Action | Char3 | BAS Action | ||
| BAS_GROUP_ID - BAS Group ID | Char3 | BAS Group | ||
| BENEFIT_PROGRAM - Benefit Program | Char3 | Ben Progrm | ||
| BEN_STATUS - Benefits Employee Status | Char4 | N | Ben Status | |
| BIRTHDATE - Date of Birth | Date | Birthdate | ||
| BUSINESS_TITLE - Business Title | Char30 | Title | ||
| BUSINESS_UNIT - Business Unit | Char5 | Unit | ||
| → | CENSUS_DT - Census Date | Date | Census Dt | |
| 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 | ||
| 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 | |
| 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 | |
| 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 | ||
| 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 | |
| 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 | ||
| HIGHEST_EDUC_LVL - Highest Education Level | Char2 | N | Hi Educ Lv | |
| HIRE_DT - First Start Date | Date | Start Date | ||
| 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 | ||
| 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 | ||
| 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 | ||
| 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 | |
| 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 | ||
| → | ROLENAME - Role Name | Char30 | Role Name | |
| 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 | ||
| SEX - Gender | Char1 | N | Sex | |
| 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_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 | |||
| 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_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 | ||
| 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 |
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_EMP_CENSVW Expand source
SELECT A.EMPLID,
A.EMPL_RCD,
A.CENSUS_DT,
A.BUSINESS_UNIT,
A.LAST_NAME,
A.FIRST_NAME,
A.MIDDLE_NAME,
A.NAME,
A.NAME_PREFIX,
A.NAME_SUFFIX,
A.SECOND_LAST_NAME,
A.UM_FORMATTED_NAME,
a.UM_PREFERRED_NAME,
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.EMPL_STATUS,
A.UM_EMP_STATUS_DESC,
A.EMPL_TYPE,
A.UM_EMPL_TYPE_DESCR,
A.EMPL_CLASS,
A.UM_EMPL_CLASS_DESC,
A.REG_TEMP,
A.FULL_PART_TIME,
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.UM_MAIL_ADDRESS1,
A.UM_MAIL_ADDRESS2,
A.MAIL_CITY,
A.MAIL_STATE,
A.MAIL_POSTAL,
A.MAIL_COUNTRY,
A.UM_CAMPUS_ADDRESS1,
A.ESTABID,
A.HOME_PHONE,
A.WORK_PHONE,
A.UM_EMAIL_BUS_ADDR,
A.LOCATION,
A.UM_LOCATION_DESCR,
CASE
WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
a.BIRTHDATE
ELSE
TO_DATE('01/01/1900', 'MM/DD/YYYY')
END,
CASE
WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
A.AGE_YEARS
ELSE
0
END,
CASE
WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
A.SEX
ELSE
'*'
END,
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.TAX_LOCATION_CD,
A.FICA_STATUS_EE,
A.FLSA_STATUS,
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,
CASE
WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
A.US_WORK_ELIGIBILTY
ELSE
'*'
END,
CASE
WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
A.DISABLED
ELSE
'*'
END,
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,
CASE
WHEN beta.rolename = 'R_F_EMPLOYE_VW' THEN
A.DISABLED_VET
ELSE
'*'
END,
A.UM_DISABILITY_BAL,
A.EEO_CLASS,
A.EEO1CODE,
A.UM_EEO1CODE_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,
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.HIRE_DT,
A.ORIG_HIRE_DT,
A.PROBATION_DT,
A.SERVICE_DT,
A.CMPNY_SENIORITY_DT,
A.TERMINATION_DT,
A.REHIRE_DT,
A.UM_LAST_CHECK_DT,
A.LAST_DATE_WORKED,
A.UM_LAST_PAY_END_DT,
A.UM_EXPECTED_END_DT,
A.PER_STATUS,
A.YRS_SERVICE,
A.LEAVE_HRS_BALANCE,
A.UM_COMP_TIME_BAL,
A.CONTRACT_STATUS,
A.CONTRACT_BEGIN_DT,
A.CONTRCT_EXP_END_DT,
A.CONTRACT_LENGTH,
A.CONTRACT_TYPE,
A.UM_CONTRACT_DESCR,
A.ACTION,
A.ACTION_DT,
A.ACTION_REASON,
A.REVIEW_DT,
A.LAST_INCREASE_DT,
A.SAL_ADMIN_PLAN,
A.PAYGROUP,
A.UM_SALBASE,
A.ANNL_BENEF_BASE_RT,
A.UM_OVRRIDE_ABBR,
A.ANNUAL_RT,
A.MONTHLY_RT,
A.DAILY_RT,
A.HOURLY_RT,
A.COMPRATE,
A.COMP_FREQUENCY,
A.UM_COMP_FREQUENCY,
A.UM_RED_CIRCLE_IND,
A.SHIFT,
A.SHIFT_FACTOR,
A.SHIFT_RT,
A.STD_HOURS,
A.STD_HRS_FREQUENCY,
A.EARNS_DIST_TYPE,
A.UM_FTE_TOTAL,
A.FTE,
A.PAID_FTE,
A.PAID_HRS,
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.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,
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,
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,
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,
A.JOB_ENTRY_DT,
A.JOB_INDICATOR,
A.JOBCODE,
A.UM_JOBCODE_DESCR,
A.JOB_FAMILY,
A.EFFDT_FROM,
A.EFFSEQ_WRK,
A.UNION_CD,
A.UM_UNION_DESCR,
A.UNION_SENIORITY_DT,
A.IPEDSSCODE,
A.UM_IPEDSCODE_DESCR,
A.GRADE,
A.GRADE_ENTRY_DT,
A.OFFICER_CD,
A.UM_OFFICER_DESCR,
A.GL_PAY_TYPE,
A.PRIMARY_JOB_IND,
A.FUNCTION_CD,
A.UM_PRIM_FUNCTION,
A.UM_PRIM_FUNC_DESC,
A.UM_SUPV_LVL_ID,
A.UM_SUP_LVL_ID_DESC,
A.UM_DISCIPLINE_CODE,
A.UM_DISCIPL_DESCR50,
A.BUSINESS_TITLE,
A.POSITION_NBR,
A.UM_POSITION_DESC,
A.UM_POSITION_TITLE,
A.UM_POS_PTS,
A.UM_COLLEGE,
A.UM_COLLEGE_DESCR50,
A.UM_POS_CNTRCT_TYPE,
A.POSITION_OVERRIDE,
A.POSITION_ENTRY_DT,
A.STEP,
A.STEP_ENTRY_DT,
A.COMPANY,
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.REPORTS_TO,
A.UM_REPORTS_TO_NAME,
A.UM_REPORTS_TO_LNME,
A.UM_REPORTS_TO_FNME,
A.UM_REPORTS_TO_EMPL,
A.UM_REPORTS_TO_DESC,
A.UM_REP_TO_CMP_ADDR,
A.UM_REP_TO_LOC_DESC,
A.UM_REPORTS_TOEMAIL,
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.UM_SERVICE_CREDITS,
A.UM_POS_BEGIN_DTE,
A.UM_POS_EXP_ENDDTE,
A.UM_POS_CONTRTSTATS,
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,
BETA.ROLENAME,
A.UM_AFFILIATE_BU,
A.MOBILE_PHONE,
UM_COHORT
FROM SYSADM.PS_UM_F_EMP_CENSUS 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.CENSUS_DT,
A.BUSINESS_UNIT,
A.LAST_NAME,
A.FIRST_NAME,
A.MIDDLE_NAME,
A.NAME,
A.NAME_PREFIX,
A.NAME_SUFFIX,
A.SECOND_LAST_NAME,
A.UM_FORMATTED_NAME,
a.UM_PREFERRED_NAME,
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.EMPL_STATUS,
A.UM_EMP_STATUS_DESC,
A.EMPL_TYPE,
A.UM_EMPL_TYPE_DESCR,
A.EMPL_CLASS,
A.UM_EMPL_CLASS_DESC,
A.REG_TEMP,
A.FULL_PART_TIME,
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.UM_MAIL_ADDRESS1,
A.UM_MAIL_ADDRESS2,
A.MAIL_CITY,
A.MAIL_STATE,
A.MAIL_POSTAL,
A.MAIL_COUNTRY,
A.UM_CAMPUS_ADDRESS1,
A.ESTABID,
A.HOME_PHONE,
A.WORK_PHONE,
A.UM_EMAIL_BUS_ADDR,
A.LOCATION,
A.UM_LOCATION_DESCR,
CASE
WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
a.BIRTHDATE
ELSE
TO_DATE('01/01/1900', 'MM/DD/YYYY')
END,
CASE
WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
A.AGE_YEARS
ELSE
0
END,
CASE
WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
A.SEX
ELSE
'*'
END,
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.TAX_LOCATION_CD,
A.FICA_STATUS_EE,
A.FLSA_STATUS,
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,
CASE
WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
A.US_WORK_ELIGIBILTY
ELSE
'*'
END,
CASE
WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
A.DISABLED
ELSE
'*'
END,
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,
CASE
WHEN rol.rolename = 'R_F_EMPLOYE_VW' THEN
A.DISABLED_VET
ELSE
'*'
END,
A.UM_DISABILITY_BAL,
A.EEO_CLASS,
A.EEO1CODE,
A.UM_EEO1CODE_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,
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.HIRE_DT,
A.ORIG_HIRE_DT,
A.PROBATION_DT,
A.SERVICE_DT,
A.CMPNY_SENIORITY_DT,
A.TERMINATION_DT,
A.REHIRE_DT,
A.UM_LAST_CHECK_DT,
A.LAST_DATE_WORKED,
A.UM_LAST_PAY_END_DT,
A.UM_EXPECTED_END_DT,
A.PER_STATUS,
A.YRS_SERVICE,
A.LEAVE_HRS_BALANCE,
A.UM_COMP_TIME_BAL,
A.CONTRACT_STATUS,
A.CONTRACT_BEGIN_DT,
A.CONTRCT_EXP_END_DT,
A.CONTRACT_LENGTH,
A.CONTRACT_TYPE,
A.UM_CONTRACT_DESCR,
A.ACTION,
A.ACTION_DT,
A.ACTION_REASON,
A.REVIEW_DT,
A.LAST_INCREASE_DT,
A.SAL_ADMIN_PLAN,
A.PAYGROUP,
A.UM_SALBASE,
A.ANNL_BENEF_BASE_RT,
A.UM_OVRRIDE_ABBR,
A.ANNUAL_RT,
A.MONTHLY_RT,
A.DAILY_RT,
A.HOURLY_RT,
A.COMPRATE,
A.COMP_FREQUENCY,
A.UM_COMP_FREQUENCY,
A.UM_RED_CIRCLE_IND,
A.SHIFT,
A.SHIFT_FACTOR,
A.SHIFT_RT,
A.STD_HOURS,
A.STD_HRS_FREQUENCY,
A.EARNS_DIST_TYPE,
A.UM_FTE_TOTAL,
A.FTE,
A.PAID_FTE,
A.PAID_HRS,
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.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,
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,
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,
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,
A.JOB_ENTRY_DT,
A.JOB_INDICATOR,
A.JOBCODE,
A.UM_JOBCODE_DESCR,
A.JOB_FAMILY,
A.EFFDT_FROM,
A.EFFSEQ_WRK,
A.UNION_CD,
A.UM_UNION_DESCR,
A.UNION_SENIORITY_DT,
A.IPEDSSCODE,
A.UM_IPEDSCODE_DESCR,
A.GRADE,
A.GRADE_ENTRY_DT,
A.OFFICER_CD,
A.UM_OFFICER_DESCR,
A.GL_PAY_TYPE,
A.PRIMARY_JOB_IND,
A.FUNCTION_CD,
A.UM_PRIM_FUNCTION,
A.UM_PRIM_FUNC_DESC,
A.UM_SUPV_LVL_ID,
A.UM_SUP_LVL_ID_DESC,
A.UM_DISCIPLINE_CODE,
A.UM_DISCIPL_DESCR50,
A.BUSINESS_TITLE,
A.POSITION_NBR,
A.UM_POSITION_DESC,
A.UM_POSITION_TITLE,
A.UM_POS_PTS,
A.UM_COLLEGE,
A.UM_COLLEGE_DESCR50,
A.UM_POS_CNTRCT_TYPE,
A.POSITION_OVERRIDE,
A.POSITION_ENTRY_DT,
A.STEP,
A.STEP_ENTRY_DT,
A.COMPANY,
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.REPORTS_TO,
A.UM_REPORTS_TO_NAME,
A.UM_REPORTS_TO_LNME,
A.UM_REPORTS_TO_FNME,
A.UM_REPORTS_TO_EMPL,
A.UM_REPORTS_TO_DESC,
A.UM_REP_TO_CMP_ADDR,
A.UM_REP_TO_LOC_DESC,
A.UM_REPORTS_TOEMAIL,
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.UM_SERVICE_CREDITS,
A.UM_POS_BEGIN_DTE,
A.UM_POS_EXP_ENDDTE,
A.UM_POS_CONTRTSTATS,
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,
rol.rolename,
A.UM_AFFILIATE_BU,
A.MOBILE_PHONE,
UM_COHORT
FROM SYSADM.PS_UM_F_EMP_CENSUS 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);