TL - Time and Labor Queries
GENERAL DESCRIPTION:
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.
PROCEDURE STEPS:
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 ‘10’ then you would enter 10% as a wild card. This would return all values beginning with 10. Such as 10999 or 10233.
- 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.
TIME and LABOR QUERIES:
Query: TL Exception Query
Query Name: UW_TL_EXCEPTION_QUERYWho: 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, Group Id, Empl Id, Employee Classification
Results:
Query: Duplicate Time Query
Query Name: UW_TL_DUPLICATE_TIME_QUERYWho: 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
Query: Time Rejected by Payroll
Query Name: UW_TL_772_TRPYWho: 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)
Query: Payable Status Query
Query Name: UW_TL_773_PSRWho: 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)
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_RTTRCWho: 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)
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_DIRECTORY
Who: 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_MEMBERSHIP
Who: 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
Results:
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_LGHOLWho: 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
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)
Results:
Query: Time Entry Method For Employee
Query Name: UW_TL_TIME_ENTRY_METHOD
Who: 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_TIME
Who: 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_SECURITY
Who: Payroll Coordinators
When: Ad-Hoc
Description: The query identifies employees with no TL security pages, both Hourly and Salary.
Prompts/Inputs:> Results:
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.
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.
Audit Action Key: A: Add, K: Change, N: New, D: Delete
ADDITIONAL RESOURCES
Related KBs:
- N/A