EPM public queries: Human Capital Management/ Human Resources

This article lists the public EPM queries and descriptions for this topic. Please review available public queries below before making a new query.

EPM public query names and descriptions for Human Capital Management/ Human Resources

• Replication of CT_DOIT_LMS_VW (DOCX): The query’s design replicates CT_DOIT_LMS_VW that is currently accessible only by DOIT for integration with their Learning Management System (LMS), which assists agencies impacted by HIPAA requirements for training. This query is to be used only by those agencies participating in the LMS program (ITD, DCF, DVA, DOT, OPM, DEP, DOC, SDE, DOL, DSS, DMV, FPC).
• 3Pct Deduction Returns (DOCX): "This query will retrieve the OPEB and ADJOPE general deductions on a specified employee starting with Pay Period End date greater than 6/4/2009.  Human Resources will be able to determine the entire amount contributed to determine the amount to be refunded. Facilitates implementation of Comptroller's Memo 2010-11.
• Sick Use Audit Query (DOCX): This query returns when an employee in a department has utilized four (4) or more hours of either paid or unpaid sick time for five (5) or more consecutive work days.  Consecutive work days are based upon entries on an employee’s timesheet and not based on a calendar or schedule.
• Active Employees by Eff Dt (DOCX): This query returns a list of active employees, prompted by DEPTID (%) and EFFDT.
• Employee, Positn, Chrtfld Data (DOCX): This query produces a report showing Active Employees, position data and chartfield data.  This report does not include vacant positions. 
• List of ee's AI (DOCX): Query returns all active employees in a specified agency, whether or not the employee received an AI and the next AI date if any. Action =PAY and Action/Reason MAI, IMA or IAN indicates the employee received an annual increment. User must enter the effective date of the AI row in question. Department allows the use of the wildcard %. (e.g., DAS%)
• Drivers License Information (DOCX): Provides driver's license, visa, and employee information for active employees.  Prompts on Department.  Requires the HR Sensitive data role to execute.
• All EE Addresses by Dept (DOCX): Use this query to find 'all' address type information for a current employee in your agency.  You can add additional jobs fields to this query or add criteria for address type.
• All Current EE Email/Phone (DOCX): Use this query to see all email and phone information for your current employees.   You can add job fields to this report, or set critieria on email/phone types. 
• Expected Return Date for Employees (DOCX): Query returns all employees with the status of leave of absence with or without pay and the expected return date for these employees by department.
• EE’s by Agency w/ RGSERS Deduct (DOCX): Query returns all employees set up with the deduction codes RGSERS, RGJUD or RGSHY per comptroller's memorandum 2013-04.
• Emergency Contacts for Active EEs (DOCX): Emergency contact phone, address information for active agency employees.
• EE Daily & Biweekly Pay Rates (DOCX): "Query returns daily and biweekly compensation rates for full and part time employees. Prompt on Department, using the wildcard %. Excluded are employees where standard hours =1.
• EE Daily & Biweekly Pay Rates (PDF)
• Employee Notepad (DOCX): Query returns all notepad entries for a particular employee.  Query promopts on employee ID.
• EE Supervisor (DOCX): Query returns active employees by agency and name of supervisor as identified on the job information tab in job data.
• Hire/Rehire 7-01-04 to 7-01-09 (DOCX): Query retrieves the hire or rehire job rows for all active employees with hire dates between 7-01-2004 and 7-01-2009.
• Hire/Rehire Date ≥ 7-1-2009 (DOCX): Query retrieves active employees with hire/rehire job data rows effective dated equal to or greater than 7-01-2009.
• Job Data Rows by Emplid (DOCX): This query will retrieve all job data rows for a specified employee.  This information will assist Human Resources in determining if an employee has had any unpaid lengths of employment in job data and will assist to determine if an adjustment needs to be made to the end date for collection of the 3% contributions.
• Job Data Audit Tool w/ Notes (DOCX): Query returns job data actions along with any associated notes for a Department during a specified Effective Date range.  This query utilizes the checkbox prompt feature allowing users to simple check on which job actions they wish to report. Note: Job Data rows may have multiple notes associated with a single job action.  The job action will repeat itself for each associated note.
• RIP Leave Accrual Liability (DOCX): Query returns sick and vacation hours and $ liability for retired employees. Query prompts on retirement date and last accrual process date.
• Military Service Information (DOCX):Provides military service and job information for active employees.  Prompts on department.
• Position Activity Query (DOCX): This query displays information on actions made on positions.  Specifically, this query displays the actions (i.e. Change, add) made on a position, the details of who and when the updates were made, and provides details of the position (position #, effeictive date, etc). 
• Position Activity Query (PDF)
• Active Positions Filled/Vacant (DOCX): Union query that returns all active employees and active, but unfilled positions. Prompts for Department using the wildcard %. Mary.Yabrosky 11-12-2004. Query reconstructed to use CTW_EMPLOYEE_VW instead of CTW_EMPLOYEE.
• Recycled/Denied Positions (DOCX): This query is used to identify positions which have been recycled or denied by workflow approvers and returned to the agency position specialist for further action. Positions currently in Position Status/Approval Action combination of Proposed/Recycle or Rejected/Deny will be returned.
• No Health Retiree Fund Deduction Codes (DOCX): In response to Comptroller's Memo 2017-03, effective July 1, 2017, this query returns deduction codes not setup for Retiree Health Fund employee and matching employer share contributions established per SEBAC agreements 2011.
• Addl Pay Setup Audit RIP 2009 (DOCX): Query provides agency HRMS staff with a tool to audit Additional Pay entries, pay outs 2012 and 2014, for employees participating in the Retirement Incentive Program 2009. Before executing this query, agency staff should familiarize themselves with the HRMS job aid, http://www.core-ct.state.ct.us/user/hrjobaids/hr/2009_rip.doc
• RIP Deductions Audit (DOCX): Query provides a list of employees participating in the Retirement Incentive Program without future deductions set up for the 2012 and 2014 pay outs. Before executing this query, agency staff should familiarize themselves with the HRMS job aid, http://www.core-ct.state.ct.us/user/hrjobaids/hr/2009_rip.doc
• RIP Job Data Audit (DOCX): Query provides an audit tool for agency HRMS staff; returns job data profile for employees participating in the Retirement Incentive Program 2009. Before executing this query, agency staff should familiarize themselves with the HRMS job aid, http://www.core-ct.state.ct.us/user/hrjobaids/hr/2009_rip.doc
• RIP Addl Pay Missing Entries (DOCX): Query provides an audit tool for agency HRMS staff; a list of all employees participating in the Retirement Incentive Program 2009 with no additional pay entries set up for the 2012 and 2014 pay outs.  Before executing this query, agency staff should familiarize themselves with the HRMS job aid, http://www.core-ct.state.ct.us/user/hrjobaids/hr/2009_rip.doc
• RIP Deduction Missing Entries (DOCX): Query provides a list of employees participating in the Retirement Incentive Program without future deductions set up for the 2012 and 2014 pay outs. Before executing this query, agency staff should familiarize themselves with the HRMS job aid, http://www.core-ct.state.ct.us/user/hrjobaids/hr/2009_rip.doc.
• All Terminated EEs (DOCX): All terminated employees by department and effective date range.
• IPEDS Reporting Audit (DOCX): Query returns active employees and associated SOC code for IPEDS reporting. The CT higher education institutions are required to annually submit human resources data to the Integrated Postsecondary Education Data System (IPEDS) for the U.S. Department’s National Center for Education Statistics (NCES). A new requirement with the April 2013 data submission now requires that the data submitted for each position contain Standard Occupation Classification (SOC) codes.  The SOC Codes are developed and maintained by the U.S. Department of Labor. More information on the SOC Codes can be found at http://www.bls.gov/soc/.
• VLRP Days & Amounts (DOCX): Query returns employee ID, name, date and amount of Voluntary Leave Reduction time, specified by Special Act No. 09-6, An Act Concerning State Personnel Cost Savings, signed by Governor Rell May 18, 2009.
• Active Employees Waive Data Setup (DOCX): Query returns a list of all active employees in a Department, indicating whether an employee's address and contact information has been protected from disclosure.  If CT_PROTECT_FLAG = 'Y' then this indicates that employees in that Jobcode are protected from disclosure.  If WAIVE_DATA_PROTECT = 'N' then this indicates that the employee has been exempted from disclosure. An employee must have one of these fields marked as indicated to be exempted from disclosure. This query can be used to determine compliance with C.G.S § 1-217.
• Workforce Analysis (DOCX): The query returns a count of employees by full time, then part time, permanent status, not terminated, gender, ethnicity, EE04 code and labor market.
• Workforce Analysis Detail (DOCX): "This query displays the detailed version of the information returned by CT_CORE_HR_WORKFORCE_ANALYSIS. The query is prompted by Department ID.
• Leave Balances for EEs <5 Yrs (DOCX): Query will identify employees by fund, providing totals of accrued hours by type and the State's liability in dollars for the accrued hours for employees with 5 years or less of state service, identified by benefit service date. The user is prompted for As of Date(accrual process date for leave balances) and Deptid.
• Leave Balances for EEs >5 Yrs (DOCX): Query will identify employees by fund, providing totals of accrued hours by type and the State's liability in dollars for the accrued hours for employees with 5 years or more of state service, identified by benefit service date. The user is prompted for As of Date(accrual process date for leave balances) and Deptid.
• Manager Service Time (DOCX): This query is used to select the service time for all managers to determine the correct lump sum adjustment amounts of employees' leave accruals on a non-calendar year basis.  The user is prompted for As of Date.

More resources

• Visit our EPM Reporting page for more help.
• View all public EPM queries.