Overview
The queries listed below (and described in detail in the Query Descriptions section) may be helpful to you when reviewing time:
| Query Name | Purpose |
|---|---|
| MTL_NOT_ON_RPTD_TIME_GROUP | Identify who did not report time |
| MTL_TIME_BY_STATUS_SUPID | Review all employees for a Supervisor that have Reported time and the Approval Status |
| MTL_RPTD_TIME_SUP | Review Reported Time details for a supervisor ID |
| MTL_RPTD_TIME_VS_STDHRS_BW | Verify the correct biweekly hours were reported |
| MTL_PAYABLE_TIME_QTY_GROUP | Review total Payable Time hours for each TRC |
| MTL_PAYABLE_TIME_SUP | Review Payable Time details for a Supervisor ID |
| MTL_TRC_BY_GROUP | Review hours and comments reported for a TRC |
| MTL_EXCEPTION_GROUP | Review Exception Time (errors) that were created instead of Payable Time by Time Administration |
Query Descriptions
MTL_NOT_ON_RPTD_TIME_GROUP
View MTL_NOT_ON_RPTD_TIME_GROUP screenshot
Purpose:
For employees in a static group, lists only the employees that have not reported time, or have not checked the "no variations from their normal work schedule checkbox.
Input:
- Group ID (static group number)
- DUR Begin Date (first date of the pay period)
- DUR End Date (last date of the pay period)
Output:
- Group ID (static group number)
- ID, Empl Rcd#, Name (of employee)
MTL_TIME_BY_STATUS_SUPID
View MTL_TIME_BY_STATUS_SUPID screenshot
Purpose:
For a Supervisor ID, lists all the Supervisor's employees that have reported time, and identifies the statuses of the time.
Input:
- DUR Begin Date (first date of the pay period)
- DUR End Date (last date of the pay period)
- Supervisor ID
Output:
- ID, Empl Rcd#, Name, Dept ID (of employee)
- Report Date, TRC, Quantity, Combo Code, Status, Approved By (of the reported time)
MTL_RPTD_TIME_SUP
View MTL_RPTD_TIME_SUP screenshot
Purpose:
Review Reported Time details for a Supervisor ID.
Input:
- DUR Begin Date (first date of the pay period)
- DUR End Date (last date of the pay period)
- Supv ID
Output:
- EmplID/Empl Rcd Nbr
- Name
- Report Date
- TRC (Time Reporting Code)
- Quantity (number of hours or dollars)
- Combo Code (short code charged to)
- Override Rsn Cd
- User
- Status
- Approved By
- Datetime
- Comments
- Var from Reg
- No Variance Source
- Group
MTL_RPTD_TIME_VS_STDHRS_BW
View MTL_RPTD_TIME_VS_STDHRS_BW screenshot
This query does not work for Punch Time departments.
Note: For correct results, the DUR Begin Date must be the first day of the biweekly pay period and the DUR End Date must be the last day of the biweekly pay period.
Purpose:
For biweekly employees only, compares the number of hours in each biweekly employee's appointment to the number of hours reported (excluding those that add to pay or leave balances – e.g. overtime).
Input:
- Group ID (static group number)
- DUR Begin Date (first date of the pay period)
- DUR End Date (last date of the pay period)
Output:
- Group ID (static group number)
- ID, Empl Rcd#, Name (of employee)
- Sum of Hours Reported
- TRC (Time Reporting Code)
- Biweekly Std Hrs (the number of hours in an employee's weekly appointment, multiplied by two)
- Variance From BW Std Hrs (sum of hours reported minus biweekly standard hours)
MTL_PAYABLE_TIME_QTY_GROUP
View MTL_PAYABLE_TIME_QTY_GROUP screenshot
Note: For monthly employees, enter the pay period begin and end dates in which the variations will be paid, not the month for which the variations are being reported (e.g., enter May pay period begin and end dates to review payable time that includes April variations.)
Purpose:
- Lists the total number of hours reported by TRC for each employee.
- This is a quick way to check if the overall time is correct before reviewing and approving an employee's detailed time.
Input:
- Group ID (static group number)
- Pay Period Begin (date)
- Pay Period End (date)
Output:
- Group ID (static group number)
- ID, Empl Rcd#, Name (of employee)
- DeptID (department ID)
- TRC (time reporting code)
- Sum of Quantity (number of hours or dollars)
- Combo Code (short code charged to)
- Sent to Payroll Status
- Estimated, Taken by Payroll, Closed (normal statuses of payable time that will be processed by payroll)
- Sent (was processed by payroll calculation process)
MTL_PAYABLE_TIME_SUP
View MTL_PAYABLE_TIME_SUP screenshot
Purpose:
Review Payable Time details for a Supervisor ID.
Input:
- Pay Period Begin Date
- Pay Period End Date
- Supv ID
Output:
- EmplID/Empl Rcd Nbr
- Name
- Dept ID
- Rpt Dt
- TRC
- Quantity
- Combo Code
- Rec Only Adj
- Sent to Payroll Status
- Group
MTL_TRC_BY_GROUP
View MTL_TRC_BY_GROUP screenshot
Note: For correct results, the DUR Begin Date must be the first day of the biweekly pay period and the DUR End Date must be the last day of the biweekly pay period.
Purpose:
- Displays hours reported for a specific TRC (if any), for each employee in a static group.
- You can enter a date range that includes multiple pay periods if desired.
Input:
- Time Reporting Code (TRC)
- Group ID (static group number)
- Pay Period Begin (date)
- Pay Period End (date)
Output:
- Group ID (static group number)
- ID, Empl Rcd#, Name (of employee)
- Rpt Dt (the date time was reported for)
- TRC (Time Reporting Code)
- Quantity (number of hours or dollars)
- Combo Code (short code charged to)