List of Time and Labor Queries in HRS
Overview
Queries have been developed in Peoplesoft to provide information otherwise not available in the Time and Labor application. This procedure explains how to execute queries and provides an overview of each Time and Labor query.Process Considerations:
- Queries can be executed using Query Viewer, this application can be found on the PeopleSoft menu under Reporting Tools > Query > Query Viewer.
Instructions on How to Run a Query
- Go to Query Viewer. To retrieve a query you can search by name or by description (click drop down box). Enter the name of the query or enter a partial string to perform a search. In the screen print below the string UW_TL was entered to retrieve the list of Time and Labor queries.
Tip: Use the "Favorites" link to keep a list of your most used queries.
- Queries can be run to a variety of formats. The most common are HTML or Excel.
Each of the most common run options are covered below.
- Run to HTML:Click the HTML link. This option executes the query and displays the results at the bottom of the page. Some queries may prompt the user for information. This information is passed to the query during runtime. The screen print below shows a prompt page for the UW_TL_INACTIVE_APPROVERS query. To run this query enter the appropriate criteria and then press the ‘View Results’ button. Once the query runs the results will be displayed at the bottom of the page.
- Run to Excel: This is the more desirable format when you have large data returns and need to sort and/or filter the results.
Click the Excel link. This option executes the query and writes results to an excel spreadsheet. Some queries may prompt the user for information. This information is passed to the query during runtime. The screen print below shows a prompt page for the UW_TL_INACTIVE_APPROVERS query. To run this query enter the appropriate criteria and then press the ‘View Results’ button. Once the query is ready to display the results a download link to the file will display in the bottom left corner of the page. Click the file to view the results.
This screen print shows the window opened to Excel with the results of the query.
- **Wild Cards
A wild card allows you to specify a partial string for a field. Wild cards can be helpful in finding all values that match specific patterns in a string. To specify a wild card, type a partial string in the field. The wild card symbol (%) can be placed anywhere in a string. For example, if you are looking for Dept Ids that begin with ‘A48’ then you would enter A48% as a wild card. This would return all values beginning with A48. Such as A480000 or A4800200.
- Run to HTML:Click the HTML link. This option executes the query and displays the results at the bottom of the page. Some queries may prompt the user for information. This information is passed to the query during runtime. The screen print below shows a prompt page for the UW_TL_INACTIVE_APPROVERS query. To run this query enter the appropriate criteria and then press the ‘View Results’ button. Once the query runs the results will be displayed at the bottom of the page.
List of Time and Labor Queries
- TL Exception: UW_TL_EXCEPTION_QUERY
- Duplicate Time: UW_TL_DUPLICATE_TIME_QUERY
- Time Rejected By Payroll: UW_TL_772_TRPY
- Payable Status: UW_TL_773_PSR
- Time Entry Status: UW_TL_774_TESR
- Payable Time By TRC: UW_TL_775_RTTRC
- Payable Time By Task: UW_TL_776_RTT
- Needs Approval: UW_TL_777_NAR
- Approvers Assigned: UW_TL_APPROVERS_ASSIGNED
- Active TL Approvers Emails: UW_TL_APPROVER_DIRECTORY
- TL Back Up Approvers Emails: UW_TL_BK_APPROVER_DIRECTORY
- Comp Time Balance: UW_TL_COMP_TIME_RPT
- Group Membership: UW_TL_GROUP_MEMBERSHIP
- Inactive TL Approvers: UW_TL_INACTIVE_APPROVERS
- No Assigned Approvers: UW_TL_NO_APPROVERS_ASSGN
- Employees Assigned to a Payroll Coordinator: UW_TL_EMPLS_ASSIGNED_TO_PYC
- Emails of Payroll Coordinators: UW_TL_PYCOORD_DIRECTORY
- Scheduled Hours exceed Legal Holiday Accrual: UW_TL_SCH_HRS_EXCEED_LGHOL
- SH Work on a Legal Holiday: UW_TL_SH_WRK_LGHOL
- Time Entry Method for Employee: UW_TL_TIME_ENTRY_METHOD
- No Time Reported: UW_TL_NO_RPTD_TIME
- No TL Security: UW_TL_NO_TL_SECURITY
- Divisional Change Since TL Security: UW_TL_DIVISION_CHANGE
- Reported Time Audit: UW_TL_RPTD_TIME_AUDIT
- Reported Time Not Yet Processed by Time Admin: UW_TL_RPTD_TIME_NOT_PROC
- Students Not Using Punches: UW_TL_STUDENT_REP_REV
- Active Employee TL Security Configuration: UW_TL_TLSEC_CUR
- Review Payable Time Approval History: UW_TL_PAY_TIME_APPR_REV
- Differential Supplement Setup: UW_TL_DIFF_SUP_SET
- Differential Supplement Usage: UW_TL_DIFF_SUP_USAGE
Query: TL Exception Query
Query Name: UW_TL_EXCEPTION_QUERY
Who: Payroll Coordinators, Supervisors, Service Center
When: During Processing Week (Regularly) and Payroll Week
Description: The output from this query will list exceptions for all employees (problems with an employee’s timecard; for example, not complete) by campus/division.
Prompts/Inputs: Begin Date (Required), End Date (Required), Dept Id (Needs '%' at minimum), Group Id (Needs '%' at minimum), Empl Id (Needs '%' at minimum), Employee Classification (Needs '%' at minimum)
Results:
Query: Duplicate Time Query
Query Name: UW_TL_DUPLICATE_TIME_QUERY
Who: Payroll Coordinators, Supervisors
When: Pay Processing Week
Description: The query identifies employees with multiple jobs where reported time overlaps for a specific time period for those jobs. PeopleSoft does not include functionality to track time on one timecard for a person with multiple jobs. Therefore, this query will assist in identifying duplicate entries for time reporters who have multiple jobs.
Note: This is a very resource intensive query, running the query with a date range greater than two weeks could cause the query to not run to success or return unreliable data. Best Practice: Limit the date range in query to be one or two weeks at the most.
Prompts/Inputs: From Date, To Date, Dept Id, Employee Classification
Results:
Query: Time Rejected by Payroll
Query Name: UW_TL_772_TRPY
Who: Payroll Coordinators, Service Center
When: Payroll Processing Week (Tue-Thu)
Description: This query lists payable time detail that is rejected by Payroll. UW-Service Center, campuses and divisional Time and Labor Administrators will review the output details of this query to assist them in determining the employees whose payable time was rejected by payroll.
Prompts/Inputs: From Date, To Date (Required), Dept Id, Time Group (Group ID), Employee Classification (Optional-for all enter % for each field)
Results:
Query: Payable Status Query
Query Name: UW_TL_773_PSR
Who: Service Center
When: Payroll Processing Week
Description: This query lists all payable time by payable statuses. It will provide managers with a picture of processed and unprocessed time for time reporters with payable time.
Prompts/Inputs: Payable Status, From Date (Required), To Date (Required), Dept Id, Empl Id (Optional-for all enter % for each field)
Results:
Query: Time Entry Status Query
Query Name: UW_TL_774_TESRWho: Payroll Coordinators
When: Payroll Processing Week
Description: This query will provide a list of positive time reporters who failed to report time for a specific pay period (Note: date range must be for a payroll period).
Prompts/Inputs: Dept Id, Start Date (required), End Date (required), Group Id, Employee Classification
Results:
Query: Payable Time by TRC
Query Name: UW_TL_775_RTTRC
Who: Payroll Coordinators, Supervisor
When: Payroll Processing Week
Description: This query provides a list of employee TRC's based on search criteria provided by the user.
Prompts/Inputs: Start Date (Required), End Date, (Required), Dept Id, TRC Code, TRC Category, Group Id, Employee Classification (Optional-for all enter % for each field)
Results:
Query: Payable Time by Task
Query Name: UW_TL_776_RTT
Who: STOUT, Athletic, Housing
When: Payroll Processing Week, Ad-Hoc
Description: This query lists payable time by Task ID and TRC for a given date range. The output from this query is for time reporters and employees/units who are utilizing task entities. In Time and Labor, a task represents work assigned to a time reporter, and is represented by a combination of task entities. Task entities, also called task elements, are the specific types of task data you can capture when time is reported: customer, task, product, project, activity, company, combo code, department, business unit, job code, position number, location code, and up to five user-defined categories. Additional task entities are available if you’re using Project Costing with Time and Labor. This query will be used by time reporters and employees/units who are utilizing task entities for verification purposes.
Prompts/Inputs: Dept ID, From Date (Required), To Date (Required), Task Id, Time Group (Group ID), Employee Classification (Optional-for all enter % for each field)
Query: Needs Approval Query
Query Name: UW_TL_777_NAR
Who: Payroll Coordinators, Service Center
When: Payroll Processing Week
Description: This query lists time reporters who have reported time that has not been approved for the given Business Unit/Dept ID, Time Reporter Group ID and Time Period. It looks for reported time with a payable time status of NA (“Needs Approval”). This information is needed because in order for a time reporter (employee) to be paid their time must be approved. The resulting query will assist the UWSS Service Operations and campus/division/department payroll coordinators in managing this task prior to a payroll calculation or confirm.
Prompts/Inputs: Dept ID, From Date (Required), To Date (Required), Group Id, Employee Classification (Optional-for all enter % for each field)
Query: Approvers Assigned Query
Query Name: UW_TL_APPROVERS_ASSIGNED
Who:Payroll Coordinators, Supervisors
When: Ad-Hoc
Description:This query identifies all employees assigned to a particular approver or backup approver.
Prompts/Inputs: Approver/Backup Empl Id, HR Status, Dept Id
Results:
Query: Active TL Approvers Emails
Query Name: UW_TL_APPROVER_DIRECTORY
Who: Payroll Coordinators
When: Ad-Hoc
Description: This query will output names and email addresses of active approvers by department.
Prompts/Inputs: Dept Id
Results:
Query: TL Backup Approvers Emails
Query Name: UW_TL_BK_APPROVER_DIRECTORYWho: Payroll Coordinators
When: Ad-Hoc
Description: This query will output names and email addresses of active backup approvers by department.
Prompts/Inputs: Dept Id
Results:
Query: Comp Time Balance Query
Query Name: UW_TL_COMP_TIME_RPTWho: Payroll Coordinators, Supervisors, Service Center
When: Ad-Hoc
Description: This query lists comp time balances by department. This will allow departments to monitor and limit compensatory time.
Prompts/Inputs: As of Date, DeptID, Empl Class, Emplid
Query: Group Membership Query
Name: UW_TL_GROUP_MEMBERSHIPWho: Payroll Coordinators, Service Center
When: Ad-Hoc
Description: This query lists employees and the secruity groups they are assigned to.
Prompts/Inputs: Empl Id, Dept Id, Employee Classification & Time Group (Group Id)
Results:
Query: Inactive TL Approvers
Query Name: UW_TL_INACTIVE_APPROVERSWho: HR Administrators, Payroll Coordinators
When: Ad-Hoc
Description: This query lists employees assigned to inactive approvers on the Maintain TL Security page in Time and Labor.
Prompts/Inputs: Business Units, Dept Id, Employee Classification, Time Group (Group Id), Empl Id
Query: No Assigned Approvers
Query Name: UW_TL_NO_APPROVERS_ASSGN
Who: Payroll Coordinators, Supervisors
When: Ad-Hoc
Description: This query identifies employees that do not have any approvers assigned to them.
Prompts/Inputs: Dept ID, Empl ID, Employee Classification, As of Date
Results:
Query: Employees Assigned to a Payroll Coordinator
Query Name: UW_TL_EMPLS_ASSIGNED_TO_PYC
Who: Payroll Coordinators, Supervisors
When: Ad-Hoc
Description: This query identifies all employees (Active and/or Inactive) assigned to a particular payroll coordinator.
Prompts/Inputs: Payroll Coordinator Empl Id, Employee HR Status
Results:
Query: Emails of Payroll Coordinators
Query Name: UW_TL_PYCOORD_DIRECTORY
Who: Payroll Coordinators, Supervisors
When: Ad-Hoc
Description: This query will output names and email addresses of active payroll coordinators by department
Prompts/Inputs: Dept Id
Results:
Query: Schedule Hours exceed Legal Holiday Accrual
Query Name: UW_TL_SCH_HRS_EXCEED_LGHOL
Who: Payroll Coordinators, Supervisors, Service Center
When: Pay processing week in which a legal holiday occurs
Description: This query identifies employees scheduled for more than 8 hours on a legal holiday. Up to 8 hours of Legal Holiday will be loaded for eligible employees and therefore anyone with a schedule that works more than 8 hours may need to make up the time, use other leave time, or have it unpaid.
Prompts/Inputs: From Date, To Date, Dept Id, Empl Id, Time Group (Group Id), Employee Classification
Results:
Query: SH Work on a Legal Holiday
Query Name: UW_TL_SH_WRK_LGHOLWho: Supervisors, Payroll Coordinators who pay extra to student help for working on a legal holiday
When: Payroll processing week when a Legal Holiday occurs.
Description: This query identifies employees in employee class ‘SH’ with time reported on a legal holiday.
Prompts/Inputs: From Date, To Date, Dept Id, Empl Id, Employee Classification, Time Group (Group Id)
Query: Time Entry Method For Employee
Query Name: UW_TL_TIME_ENTRY_METHODWho: Payroll Coordinators
When: Ad Hoc
Description: The query provides a list of employees and shows their Time Entry method as selected on the TL Security page.
Prompts/Inputs: Dept Id, Employee Classification, Begin Date, End Date, Business Unit
Results:
Query: No Time Reported
Query Name: UW_TL_NO_RPTD_TIMEWho: Supervisors, Payroll Coordinators
When: Ad Hoc but recommended at least during payroll processing week
Description: This query identifies employees that do not have any reported time for the specified biweekly pay period. It can be used independently or in conjunction with the Total 80 report.
Prompts/Inputs: Dept Id, Start Date, End Date, Time Group (Group ID), Empl Class
Results:
Query: No TL Security
Query Name: UW_TL_NO_TL_SECURITYWho:Payroll Coordinators
When: Ad-Hoc
Description: The query identifies employees with no TL security pages, both Hourly and Salary.
Prompts/Inputs: Dept Id, Empl Type
Query: Divisional Change since TL Security
Query Name: UW_TL_DIVISION_CHANGE
Who: Payroll Coordinators
When: Ad Hoc
Description: This query identifies employees who have had a change to their division in job data but have not had a new effective dated row with updated payroll coordinators or approvers added to their TL Security page.
Prompts/Inputs: None. This query is designed to run off of row level security settings.
Results:
Query: Reported Time Audit
Query Name: UW_TL_RPTD_TIME_AUDIT
Who: Payroll Coordinators
When: Ad Hoc
Description: This query identifies the audit stamp of who made time entries or changes to an employee's timesheet. Results are returned based on users row level security. If the operator that made the time entry is outside of the query runners row level security audit results will not be visible. Contact your Affinity Group for audit stamp information in these instances.
Prompts/Inputs: Empl ID, Empl Rcd, Start Date, End Date
Audit Action Key: A: Add, K: Change, N: New, D: Delete
Results:
Query: Reported Time Not Yet Processed by Time Admin
Query Name: UW_TL_TIME_NOT_PROC
Who: Payroll Coordinators
When: Ad Hoc
Description: This query shows Reported Time for employees that hasn't yet been processed to Payable Time by Time Admin. The output from this query does not necessarily mean there is a problem or that Time Admin won't process the reported time. The output is only a snapshot of the Reported Time status at the moment the query was run. There is a column entitled 'Action', if the word 'Review' appears in this column, this means that this Reported Time should be monitored more closely and if the time doesn't get processed to Payable Time after the next Time Admin stream has run, contact Service Operations for assistance.
Prompts/Inputs: Campus, Dept ID, Empl Class, Empl ID, Start Date, End Date
Results:
Query: Students Not Using Punches
Query Name: UW_TL_STUDENT_REP_REV
When: Ad Hoc
Description: This query identifies students that have entered reported time using the quantity field instead of entering IN & OUT punches. It displays the reported quantities of time per day in a given period.
Prompts/Inputs: Dept ID (optional), Empl ID, Start Date, End DateResults:
Query: Active Employee TL Security Configuration
Query Name: UW_TL_TLSEC_CUR
When: Ad Hoc
Description: This query shows the existing TL Security setup for active employees.
The output from this query can be used to assist with verifying a group of employees are correctly setup on their TL Security pages and identifying employees requiring adjustments to their TL Security page. In addition, the output should be included with requests for Shared Services to process mass updates to Approvers on the TL Security page for a group of employees. If Shared Services knows the existing TL Security setup for the employees that a mass update is being requested, it makes the mass update process easier and helps minimize the chance of errors.
Prompts/Inputs: Campus, Department ID (%) & Employee Classification (%)
Results:
Query: Review Payable Time Approval History
Query Name: UW_TL_PAY_TIME_APPR_REV
When: Ad Hoc
Description: This query shows Payable Time approval history for Employees and/or Approvers.
Prompts/Inputs: Campus, Department ID (%) , Approver, Employee ID, Start and End Date
Results:
Query: Differential Supplement Setup
Query Name: UW_TL_DIFF_SUP_SET
When: Ad Hoc
Description: This query lists the Differential Supplement configuration for employees by Campus, by Department ID and Differential Supplement ID. Use this query to identify employees within a specific area that are setup to receive a Differential Supplement.
Prompts/Inputs: Campus, Department ID(%) and Dif Sup ID(%)
Results:
Query: Differential Supplement Usage
Query Name: UW_TL_DIFF_SUP_USAGE
When: Ad Hoc
Description: This query lists usage of the Differential Supplement for Employees by Campus and Department for a given date range. Use this query to identify which employees are receiving Differential Supplements for a given period of time.
Prompts/Inputs: Campus, Department ID(%), Employee ID(%) and Date Range
Results: