1 / 50

STATE OF CONNECTICUT Core-CT Project

STATE OF CONNECTICUT Core-CT Project. EPM HR User Group Meeting October 27 2004. Agenda. Welcome Jeanne Skellett Public Queries Update / Reminders Reporting Table Refresh & Status Page EPM HR Reporting Scenarios Mary Yabrosky

zelenia-lewis
Download Presentation

STATE OF CONNECTICUT Core-CT Project

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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. Core Delivered Queries: Statewide Public Queries Agency PublicQueries CT_CORE_MODULE_FUNCTION_QNAME CT_MODULE_FUNCTION_QNAME CT_AGENCYACRONYM_MODULE_FUNCTION_QNAME Public Query Reminder Public Query Naming Standards

  5. 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? System Updates

  6. Reporting Table Status

  7. Reporting Table Status

  8. 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 System Updates

  9. Training/Education Affirmative Action Reporting Breakout Sessions

  10. 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 Training/Education

  11. Breakout Sessions: User’s 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 Training/Education

  12. 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. Training/Education

  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. Breakout Sessions Cont. – Tables 3 & 4 Training/Education 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 >.

  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: $A$1:$G$10 in this scenario. Click Next >.

  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. 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 System Updates Payroll Other Earnings Table (CT_PAY_EARNS_MV)

  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 employee’s records. 35

  36. New EPM Security Approach View SQL Example: 36

  37. New EPM Security Approach 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' SQL Example: 37

  38. New EPM Security Approach DOT view using the Current approach: 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 employee’s 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 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' SQL Example: 43

  44. New EPM Security Approach DOT view using the Proposed approach 44

  45. New EPM Security Approach MHA view using the Proposed approach 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 • Workman’s Comp reports require agencies to view 52 weeks of employee’s payroll history regardless of agency assignment. 46

  47. New EPM Security Approach • Issue resolved by the Proposed EPM Approach: • The Worker’s 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/epm) • Next Meeting – • 101 E. River Drive, Room 1002 A&B

More Related