DARTS : Active Employees by Campus

Report Purpose

This query is designed to return all active employees on each campus as of the HR census data of choice.

The query includes UMS (UMS08) Staff by using a case statement expression that defines Campus by location code.

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_DTS_ACTIVE_BY_CAMPUS

Report Type

Query

Prompts Required

A.BUSINESS_UNIT  - labeled as CAMPUS

A.CENSUS_DT - is Greater Than date Selected - this allows the user to return the latest census date without needing to know exactly what it is. 

 The HR Census is run - End of March, End of Fiscal Year(June), End of October, End of Calendar Year(December)  but can vary from the 29th through the 31st depending on the days of the week and when payroll closes.

Data Fields Generated

Tables Used

UM_F_EMP_CENSVW - Employee Census Table VW

Expression Information

The query includes an expression to extract the USERID or LDAP ID from the @maine email address as many back-end processes use the USERID as a unique identifier.

/*- EXTRACT USERID using EMAIL*/
substr(A.UM_EMAIL_BUS_ADDR,1,
decode(instr(A.UM_EMAIL_BUS_ADDR,'@'),
0,length(A.UM_EMAIL_BUS_ADDR),instr(A.UM_EMAIL_BUS_ADDR,'@')-1))

hardcoded criteria -
A.EMPL_STATUS - Employee Status  - Active, Leave, Leave with Pay, Suspended, Short Work Break
A.PRIMARY_JOB_IND - Flag  = 'Y'   - to return one row for each person