Employee Years of Service

Body

This Query return the years of service of employees, as of the last day of each calendar year in five year increments.
Data returned can be filtered by Department Code to find specific entries. This query is frequently used to determine longevity awards. Requires annual updating. Contact darts@maine.edu.

Detailed Information

Databases

PeopleSoft Human Resources HRPRD  - Public Query

Report Location

MyCampus > MaineStreet > Enterprise Applications > Human Resources - Production > NavBar (compass icon, top right) > Navigator > Reporting Tools > Query > Query Manager or Query Viewer

Report Name

  • UMS_SERVICE_DATE_FOR_AWARDS - basic report for use by all institutions

Source Tables

  • A.UM_F_EMPLOYE_VW - Employee Flat Table
  • B.UM_PER_POI left outer joined with A
  • C.UM_POI_CAT_TBL - POI category table left outer joined with B

Data Fields Generated

Record.Fieldname Format XLAT Heading Text
A.BUSINESS_UNIT - Business Unit Char5   UNIT
A.EMPL_CLASS - Employee Classification Char3 L EMPL_CLASS
A.EMPLID - Empl ID Char11   EMPLID
A.UM_FORMATTED_NAME - Formatted_Name Char80   NAME_SORT
A.NAME - Name Char50   NAME
A.UM_EMAIL_BUS_ADDR - Business Email Address Char70   EMAIL
A.UM_POSITION_TITLE - Position Title Char30   TITLE
A.UM_POSITION_DESC - Position Description Char254   POSITION_DESC
A.DEPTID - Department Char10   DEPTID
A.DEPT_DESCR - Department Description Char30   DEPARTMENT
A.UM_COLLEGE_DESCR50 - College Descxription Char50   UMS_COLLEGE
A.REG_TEMP - Regular/Temporary Char1 N REG_TEMP
A.HIRE_DT - First Start Date Date   HIRE_DATE
A.SERVICE_DT - Service Date Date   SERVICE_DATE
A.PAYGROUP - Pay Group Char3   PAYGROUP
/*+Time Elapsed YEARS*/
 trunc(( to_date(20231231,'YYYY-MM-DD')
 - to_date(A.SERVICE_DT,'YYYY-MM-DD')		 SNm5.0   SERVICE_AS_OF_12/31/2023
A.UM_REPORTS_TO_LNME - Reports to Last Name Char30   SUPERVISOR_LAST_NAME
A.UM_REPORTS_TO_FNME - Reports To First Name Char30   SUPERVISOR_LAST_NAME
A.UM_REPORTS_TO_NAME - Reports to Name Char50   SUPERVISOR_NAME
A.UM_REPORTS_TO_DESC - Reports To Description Char254   SUPERVISOR_TITLE
A.UM_REP_TO_CMP_ADDR - Reports to Campus Address Char55   SUPERVISOR_ADDRESS
A.UM_REP_TO_LOC_DESC - Reports to Location Descr Char30   SUPERVISOR_CAMPUS
A.UM_REPORTS_TOEMAIL - Reports To Email Address Char70   SUPERVISOR_EMAIL
B.EMPLID - Empl ID Char11   EMPLID
B.UM_POI_CAT - POI Category Char5   POI Cat.
C.DESCR - Description Char30   POI_TYPE
B.BUSINESS_UNIT - Business Unit Char5   POI_UNIT
B.BEGIN_DT - Begin Date Date   BEGIN_DT
B.END_DT - End Date Date   END_DT
TO_CHAR(B.COMMENTS) Char200   COMMENTS

 

Details

Details

Article ID: 158688
Created
Fri 5/10/24 4:24 AM
Modified
Wed 9/18/24 11:31 AM
Applies To
Faculty
Staff
Community

Related Services / Offerings

Related Services / Offerings (1)

DARTS (UMS Data) Inquiry
Submit an inquiry about UMS Data (except Data Governance)