EPM HR User Group Meeting Presentation, October 27

1
STATE OF CONNECTICUTCore-CT Project
EPM HR User Group MeetingOctober 27 2004
2
Agenda

• Welcome Jeanne Skellett
• Public Queries Update / Reminders
• Reporting Table Refresh Status Page
• EPM HR Reporting Scenarios Mary Yabrosky
• HR Reporting Scenarios Instructions
  Steve Soklow
• Break Out Session
• Group Feedback
• EPM Demonstration
• New Pay Earns Reporting Table Mary Yabrosky
• HR Security View Roger Sherman
• Questions Discussion EPM Team

3
Public Query Reminders
EPM Super User Responsibilities Naming
Standards Documentation Backup Recovery
Agency Query Specialist Review Remove Unused
Queries
4
Public Query Reminder
Public Query Naming Standards

• Core Delivered Queries
• Statewide Public Queries
• Agency Public Queries

• CT_CORE_MODULE_FUNCTION_QNAME
• CT_MODULE_FUNCTION_QNAME
• CT_AGENCYACRONYM_MODULE_FUNCTION_QNAME

5
System Updates

• EPM Web Status Update Tool
• What is it?
• Where is it located?
• http//www.core-ct.state.ct.us/epm/
• How do I use It?

6
Reporting Table Status
7
Reporting Table Status
8
System Updates

• Customer Requests
• Position Reporting Table (CT_POSITION_TBL)
• Position End Date
  CT_POSITION_END_DT
• Employee Information Rpt Tbl (CT_EMPLOYEE_TBL)
• Establishment ID ESTABID
• Full/Part Time Status FULL_PART_TIME
• Regular/Temporary Status REG_TEMP
• Employee Name Prefix NAME_PREFIX
• Highest Educational Level HIGHEST_EDUC_LVL

9

Training/Education Affirmative Action
Reporting Breakout Sessions
10
Training/Education

• Training Objective
• Pilot New EPM Training Approach
• Initiate Discussion Among Users
• Gain / Reinforce Knowledge of
• EPM Reporting Tables
• Query Tool
• Create and Demonstrate New Queries for
• Private Use
• Public Use
• Excel Pivot Tables

11
Training/Education

• Breakout Sessions
• Users will break out into 6 Groups (Groups of 10
  -15)
• Three (3) HR AA Reporting Scenarios are provided
• Groups will create queries based on scenarios
  using Query Template and Supporting Job Aids
  provided
• Groups will
• Report Results
• Build Query On-Line
• Download to Excel Pivot Table

12
Training/Education

• Breakout Sessions Cont. Tables 1 2
• AA Scenario 1
• CHRO is requesting a series of Affirmative Action
  Reports from your agency. The first report is
  for All Employees Hired between September 1,
  2003 and August 31, 2004. Create a query from
  the EPM Employee Table to capture appropriate
  Action and Action Reason codes for this effective
  date range by Department.

13
Training/Education

Breakout Sessions Cont. Tables 1 2 AA
Scenario 1 - Sample Results
14

Training/Education

Breakout Sessions Cont. Tables 3 4 AA
Scenario 2 CHRO is requesting a series of
Affirmative Action Reports from your agency.
The second report is a count of All Active
employees by Occupational Group and age bracket.
Create a query off of the Employee Table on
Name and EEO4 where employees are between the age
of 41 45.
15
Training/Education

• Breakout Sessions Cont. Tables 3 4

AA Scenario 2 Sample Results
16
Training/Education
Breakout Sessions Cont. Tables 5 6 AA
Scenario 3 CHRO is requesting a series of
Affirmative Action Reports from your agency.
The third report is a Count of all employees by
Occupational Group, LMA, Ethnic Group and Gender
. Create a query off of the Employee Table and
count the number of Employees by County, EEO4
Category, Job Code, Department, Ethnic Group and
Gender. Download to create Excel Pivot Table.
17
Training/Education
Breakout Sessions Cont. Tables 5 6 AA
Scenario 3 Sample Results
18
Training/Education
Breakout Sessions Cont. Report Results - All
Scenarios Build Query On-Line - All
Scenarios Create Excel Pivot Table Scenario 3
19
Training/Education
Breakout Sessions cont. Excel Pivot Table
Scenario 3 When should I use a PivotTable
report? Use a PivotTable report when you want to
analyze related totals, especially when you have
a long list of figures to sum and you want to
compare several facts about each figure. Because
a PivotTable report is interactive, you can
change the view of the data to see more details
or calculate different summaries, such as counts
or averages.

20
Training/Education
MS Excel PivotTable and PivotChart Wizard
Instructions Place cursor in first cell of data
of your Excel Worksheet
21
Training/Education
PivotTable and PivotChart Wizard Click Data Menu
Item and select PivotTable and PivotChart Wizard
Report option.
22
Training/Education
PivotTable and PivotChart Wizard Step 1 Choose
Default MS Excel database and click Next gt.
23
Training/Education
PivotTable and PivotChart Wizard Step 2 Because
you placed you cursor in the first cell of you
data on your worksheet first, Pivot Table and
Pivot Chart Wizard Step 2 3 window will search
for all data, down and to the right, or Range
A1G10 in this scenario. Click Next gt.
24
Training/Education
PivotTable and PivotChart Wizard Step 3 Save new
Pivot Table to a New Worksheet. Click Finish.
25
Training/Education
PivotTable and PivotChart Wizard. Drag and Drop
Fields.
26
Training/Education
Breakout Sessions Cont. Tables 5 6 AA
Scenario 3 Pivot Table Sample Results
27
Training/Education
Breakout Session Cont. Identify Query for Public
Realm Demonstrate Proper Steps in Making Query
Public
28

• Break (15 minutes)
• Please turn in your index cards!!!

29
System Updates
Payroll Earnings Table (CT_PAY_EARNS_MV)
The Pay Earns table will allow users to select
and view all earnings and all time and labor
hours from one easy-to-use reporting table. In
addition to all earnings and hours, this
reporting table will hold Employee ID, Name,
Department ID, Account, Account Code, Position
Number, Pay End Date and Earnings Code. The
reporting table will have a separate row for
total hours and pay for each employee by earn
codes and pay end date. This table can be easily
joined to the Account Code Table to access
additional coding string fields such as Class
Field, Project, Program or Chartfield codes.
30
System Updates
Payroll Other Earnings Table (CT_PAY_EARNS_MV)

• OTH_HRS
• OTH_PAY
• OTH_EARNS
• EMPLID
• NAME
• ACCT_CD
• ACCOUNT
• DEPTID
• POSITION_NBR
• PAY_END_DT
• ERNCD

• Other Hours
• Other Pay
• Other Earnings
• Employee ID
• Name
• Account Code
• Account
• Department ID
• Position Number
• Pay Period End Date
• Earnings Code

31
System Updates
Payroll Earnings Query
32

• HR Security
• New EPM Security Approach

32
33
New EPM Security Approach

• Current HRMS/EPM Security Approach
• Issue with Current Approach
• Proposed EPM Security Approach
• Issue Resolved
• Issue created by the Proposed EPM Approach
• Issue Resolved
• Summary

34
New EPM Security Approach

• Current HR/EPM Security Approach
• Based on Employee ID
• Employee Row Security Class
• Allows the agency to view the complete
  history of an employee.

34
35
New EPM Security Approach

• Issue with Current EPM Security Approach
• Transfer of an Employee
• When a person is transferred from an agency,
  their records can no longer be seen by that
  agency within HRMS or EPM.
• Example
• If a person is transferred from the Department of
  Mental Health to DOT, Mental Health can no longer
  view the transferred employees records.

35
36
New EPM Security Approach
View SQL Example
36
37
New EPM Security Approach
SQL Example

• SELECT A.EMPLID, A.EMPL_RCD, A.DUR, A.DEPTID
• FROM PS_CT_PAYTIME_TBL A, PS_CT_FAST_EMP_MV A1
• WHERE A.EMPLID A1.EMPLID
• AND A.EMPL_RCD A1.EMPL_RCD
• AND A1.ROWSECCLASS 'DPDOT'

37
38
New EPM Security Approach
DOT view using the Current approach
EMPLID EMPL_RCD DUR DEPTID
311424 0 2/27/2004 MHA53700
311424 0 3/1/2004 MHA53700
311424 0 3/2/2004 MHA53700
311424 0 3/3/2004 MHA53700
311424 0 3/3/2004 MHA53700
311424 0 3/4/2004 MHA53700
311424 0 3/5/2004 DOT57100
311424 0 3/5/2004 DOT57100
311424 0 3/8/2004 DOT57100
311424 0 3/8/2004 DOT57100
311424 0 3/8/2004 DOT57100
311424 0 3/9/2004 DOT57100
311424 0 3/9/2004 DOT57100
38
39
New EPM Security Approach
MHA view using the current approach
39
40
New EPM Security Approach

• Proposed EPM Security Approach
• Based on the Department ID
• Department Row
  Security Class
• Allows the agency to view the history of an
  employee for the period of time the person was
  employed by that agency.

40
41
New EPM Security Approach

• Issue Resolved by the EPM Security Approach
• Transfer of an Employee
• When a person is transferred from an agency,
  their records can be viewed, within EPM, for the
  period of time the person was employed by that
  agency.
• Example
• If a person is transferred from the Department of
  Mental Health to DOT, Mental Health can now view
  the transferred employees records for the period
  of time the person was employed by them.

41
42
New EPM Security Approach
View SQL Example
42
43
New EPM Security Approach
SQL Example

• SELECT A.EMPLID, A.EMPL_RCD, A.DUR, A.DEPTID
• FROM PS_CT_PAYTIME_TBL A, PS_FAST_SCRTY_2 A1
• WHERE A.DEPTID A1.DEPTID
• AND A1.ROWSECCLASS 'DPDOT'

43
44
New EPM Security Approach
DOT view using the Proposed approach
EMPLID EMPL_RCD DUR DEPTID
311424 0 3/5/2004 DOT57100
311424 0 3/5/2004 DOT57100
311424 0 3/8/2004 DOT57100
311424 0 3/8/2004 DOT57100
311424 0 3/8/2004 DOT57100
311424 0 3/9/2004 DOT57100
311424 0 3/9/2004 DOT57100
311424 0 3/9/2004 DOT57100
311424 0 3/10/2004 DOT57100
311424 0 3/10/2004 DOT57100
311424 0 3/10/2004 DOT57100
311424 0 3/11/2004 DOT57100
311424 0 3/11/2004 DOT57100
44
45
New EPM Security Approach
MHA view using the Proposed approach
EMPLID EMPL_RCD DUR DEPTID
311424 0 12/26/2003 MHA53700
311424 0 1/10/2004 MHA53700
311424 0 1/12/2004 MHA53700
311424 0 1/13/2004 MHA53700
311424 0 1/13/2004 MHA53700
311424 0 1/14/2004 MHA53700
311424 0 1/15/2004 MHA53700
311424 0 1/16/2004 MHA53700
311424 0 1/19/2004 MHA53700
311424 0 1/20/2004 MHA53700
311424 0 1/20/2004 MHA53700
311424 0 1/21/2004 MHA53700
311424 0 1/22/2004 MHA53700
45
46
New EPM Security Approach

• Issue created by the Proposed EPM Approach
• Transfer of an Employee
• When a person is transferred from an agency, the
  receiving agency can no longer report the
  complete history of the transferred employee.
• Example
• Workmans Comp reports require agencies to view
  52 weeks of employees payroll history regardless
  of agency assignment.

46
47
New EPM Security Approach

• Issue resolved by the Proposed EPM Approach
• The Workers Comp Specialist will be able
  to use the Public Query CT_HR_PAY_FROM_ PS to
  report the salary information for the last 52
  weeks for an employee.
• The query is based on employee ID as used
  in the current EPM Security approach.

47
48
New EPM Security Approach

• Proposed EPM Security Approach Advantages
• Allows the agency to view the complete
  history of an employee on-line.
• Allows reporting of history for the period
  of time an employee was employed by the agency.

48
49
QUESTIONS? COMMENTS? FEEDBACK?
49
50
Closing Remarks
Thank You for your participation today. We look
forward to continuing our work together to
improve and make EPM a Success!

• Feed Back Forms
• Suggestions for Training Areas
• Agency Presentations
• Look for this presentation and meeting on the
  Core-CT Website (http//www.core-ct.state.ct.us/ep
  m)
• Next Meeting
• 101 E. River Drive, Room 1002 AB