Gross Pay Register (GPR) queries are detailed at the bottom of this document.
Overview
A query is a method of directly retrieving data from M-Pathways for purposes of review. The queried data is viewed on a grid in M-Pathways, and can be downloaded into an Excel spreadsheet. Queries are the most efficient method of reviewing Time and Labor data for multiple employees at once (usually for a static group). This document sorts the available queries according to the Eight Steps to a Successful Payroll. It also:
- lists the public queries applicable to timekeepers
- summarizes possible uses for each query
Searching for Timekeeper Queries
In M-Pathways, public queries can be searched for based on the beginning of their names, which provides a more applicable Search Results list. Queries that begin with:
- MPY can be run to review paycheck amounts for a payroll,
- MTL can be run to review a variety of Time and Labor data for payrolls less than 6 months old, and
- ODS_MTL can be run to review Reported and Payable Time for payrolls older than 6 months.
Input Parameters for a Range of Dates
Different payable time queries require different types of input parameters for specifying dates:
- Pay Period Begin/End Dates – retrieves payable time processed in a specific payroll, including Prior Period Adjustments (PPAs). These queries include "Report Date" columns that identify the date for which each PPA was entered.
- DUR (Date Under Report) Begin/End Dates – retrieves the payable time for each date in a specified range. When these queries are run for a current pay period, they do not include PPAs. These queries only display PPAs when the input date range includes the report dates of the PPAs.
All other queries that retrieve data for a range of dates are considered DUR (Date Under Report) queries. This is because they only retrieve data for dates in the specified range. These queries may display input parameters of "Begin/End Dates" or "From/Thru Dates," without the DUR prefix.
Supporting Documentation
The Run an Existing (Public) Query Using Query Viewer - Step-by-Step Procedure, available on My LINC, can be used as a guide for running any public query.
Public Queries Applicable to Timekeepers
| Group Queries | Description |
|---|---|
| MTL_EMPLOYEES_IN_GROUP | View a list of employees included in your static group. |
| MTL_SUP_DEL_BY_EMP | Identifies the primary time approver and delegate approvers of an employee. |
| Schedule Queries | Description |
| MTL_SCHEDHRS_BY_EMPL | Review schedule assignment for one employee. |
| MTL_SCHEDHRS_BY_GROUP | Review current schedule assignments for a static group. |
| MTL_SCHEDHRS_FUTURE_DATED_ GRP | Review future-dated schedule assignments (if no future-dated schedule is assigned, displays current) for a static group. |
| Leave Data Queries | Description |
| MTL_LEAVEBY_PLANTYPE_GROUP | Review leave activity for one plan type (e.g., extended sick time only) for a static or dynamic group. |
| MTL_LEAVE_BAL_BY_PLAN | Review leave activity for one plan type for an employee. |
| MTL_LVE_BAL_GROUP | Review leave balances for each Plan Type in a static or dynamic group. |
| MTL_LVE_BAL_BY_EMPL | Review leave balances for each Plan Type for an employee. |
| MTL_PTO_BALANCE_BY_DEPTID | Review PTO balances of each employee in a department. |
| Reported Time Queries | Description |
| MTL_TRC_BY_WORKGROUP | Obtain a list of all active TRCs sorted by Workgroup. |
| MTL_NOT_ON_RPTD_TIME_GROUP | Determine if time was not reported for any members of a static group. |
| MTL_RPTD_TIME_DEPT | Review Reported Time details for a department. |
| MTL_RPTD_TIME_EMPL | Review Reported Time details for an employee. |
| MTL_RPTD_TIME_GROUP | Review Reported Time details for a static group. |
| MTL_RPTD_TIME_QTY_EMPL | Review the sum of hours/amounts for each TRC in Reported Time for an employee. |
| MTL_RPTD_TIME_QTY_GROUP | Review the sum of hours/amounts for each TRC in Reported Time for a static group. |
| MTL_TIME_BY_STATUS_SUPID | Lists all employees that have Reported Time for a supervisor and identifies the Approval Status. |
| MTL_RPTD_TIME_SUP | Review the Reported Time details for a supervisor ID. |
MTL_RPTD_TIME_VS_STDHRS_DEPT (by dept) |
Compare the sum of hours reported to the standard hours on job for a static group. Note: The sum of reported hours does not include any hours that are over-appointment (e.g., overtime, additional vacation or PTO). |
| MTL_FLSA_EFFECT_ON_TRCDS | Determine if earnings are used to calculate FLSA rates, or if earnings are eligible to be paid with a FLSA rate. |
| ODS_MTL_RPTD_TIME_EMPL_ARC | Review Reported Time details by employee from July 1, 2001 through the present. |
| ODS_MTL_RPTD_TIME_GROUP_ARC | Review Reported Time details by static group from July 1, 2001 through the present. |
| Payable Time Queries | Description |
| MTL_NOT_ON_PAYABLE_EMPL | Determine if Payable Time was not created/updated for someone with Reported Time. |
| MTL_NOT_ON_PAYABLE_GROUP | Determine if Payable Time was not created/updated for any member of a static group with Reported Time. |
| MTL_PAYABLE_TIME_DEPT | Review Payable Time for each employee in a department. |
| MTL_PAYABLE_TIME_DUR_EMPL | Review Payable Time for a range of dates based on the report date for an employee. Note: The report date is the date the time was reported for. |
| MTL_PAYABLE_TIME_DUR_GROUP | Review Payable Time for a range of dates based on the report date for a static group. Note: The report date is the date the time was reported for. |
| MTL_PAYABLE_TIME_EMPL | Review Payable Time details, including PPAs processed in the specified payroll, for an employee. |
| MTL_PAYABLE_TIME_GROUP | Review Payable Time details, including PPAs processed in the specified payroll, for a static group. |
| MTL_PAYABLE_TIME_SUP | Review Payable Time details for a supervisor ID. |
| MTL_PAYABLE_TIME_QTY_EMPL | Review the sum of hours/amounts for each TRC in Payable Time for an employee. |
| MTL_PAYABLE_TIME_QTY_GROUP | Review the sum of hours/amounts for each TRC in Payable Time for a static group. |
| MTL_TRC_BY_DEPT | Review Payable Time for the use of one TRC for each employee in a department. |
| MTL_TRC_BY_EMPLID | Review Payable Time for the use of one Time Reporting Code for an employee. |
| MTL_TRC_BY_GROUP | Review Payable Time for the use of one Time Reporting Code for a static group. |
| ODS_ MTL_PAYABLE_TIME_GROUP_ARC | Review Payable Time for payrolls from July 1, 2001 through the present for a static group. |
| ODS_MTL_ PAYABLE_TIME_EMPL_ARC | Review Payable Time for payrolls from July 1, 2001 through the present for an employee. |
| ODS_ MTL_PYBL_TIME_GRP_ARC_DUR | Review Payable Time for a range of dates for a static group. |
| ODS_MTL_PYBL_TIME_EMPL_ARC_DUR | Review Payable Time for a range of dates for an employee. |
| Exception Time Queries | Description |
| MTL_EXCEPTION_EMPLID | Determine if Exception Time was created instead of Payable Time for an employee. |
| MTL_EXCEPTION_GROUP | Determine if Exception Time was created instead of Payable Time for a static group. |
| Paycheck Data Queries | Description |
| MPY_GROSS_NET_BY_DEPTID_ENDDT | Review gross and net pay amounts for each employee in a department. |
| MPY_GROSS_NET_BY_EMPLID_ENDDT | Review gross and net pay amounts for one employee. |
| GPR Queries | Description |
| MTL_GROSS_PAY_BY_DEPT | Review GPR data for each employee in a department. |
| MTL_GROSS_PAY_BY_EMPL | Review GPR data for an employee. |
| MTL_GROSS_PAY_BY_GROUP | Review GPR data for each employee in a static group. |