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

  1. 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.

    Enter Search Criteria

    Tip: Use the "Favorites" link to keep a list of your most used queries.

  2. 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.

    1. 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 HTML

    2. 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. 

      Run to Excel

      This screen print shows the window opened to Excel with the results of the query.

      Excel File View

    3. **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.



TIME and LABOR QUERIES:


Query: TL Exception Query

Query Name: UW_TL_768_TLER
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, Group Id, Empl Id, Employee Classification

Results:

TL Exception Query



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.  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:
UW_TL_DUPLICATE_TIME_QUERY


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:

Time Rejected By Payroll



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:

Payable Status Report



Query: Time Entry Status Query

Query Name: UW_TL_774_TESR
Who: 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:

Time Entry Status Query



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:

Payable Time by TRC



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)

Results:

Payable Time by Task

 


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 UW Service Center 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)

Results:

Needs Approval Query


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:

Approvers Assigned Query




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:

Emails of Active Approvers


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:

Backup Approvers Emails



Query: Comp Time Balance Query

Query Name: UW_TL_COMP_TIME_RPT
Who: 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

Results:

Comp Time Query



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:

TL Group Membership

 
 

Query: Inactive TL Approvers

Query Name: UW_TL_INACTIVE_APPROVERS
Who: 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:

Inactive Approvers

 

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:

No Approvers Assigned


Query: Employees Assigned to a Payroll Coordinator

Query Name: UW_TL_PYCOORDS_ASSIGNED
Who: Payroll Coordinators, Supervisors
When: Ad-Hoc

Description: This query identifies all employees (Active & Inactive) assigned to a particular payroll coordinator.

Prompts/Inputs: Payroll Coordinator Empl Id

Results:

Employees Assigned to Payroll Coordinator

 


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:

Emails of Payroll Coordinators

 

 

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:

Scheduled Hours exceed LGHOL



Query: SH Work on a Legal Holiday

Query Name: UW_TL_SH_WRK_LGHOL
Who: 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:

SH Work on a Legal Holiday



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:

Time Entry Method



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:


UW_TL_NO_RPTD_TIME

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.

UW_TL_DIVISION_CHANGE


ADDITIONAL RESOURCES:

Related KBs:

  • N/A
Related Links:
    • N/A



    Keywords:Accrual and Approval Approved Approvers Assigned Auto Change Changes Comp Coordinators Dated Duplicate Effective Element Emplid Exceeds Exception Group Holiday Hours Hrs HRS Inactivated Inactive Labor Legal Lunch Membership Mismatches Needs No NoPay Payable Payroll Prior Profile Punch Punch/Elapsed queries Query query querying REG Rejected Report Reported Reporter reporting reports Role Rule Schedule Security SH Status T&L Task Taskgroup TCD Time Time TL TRC Validation Work   Doc ID:20530
    Owner:Wendy G.Group:Human Resource System (HRS)
    Created:2011-10-04 21:51 CSTUpdated:2017-04-27 09:04 CST
    Sites:Human Resource System (HRS)
    Feedback:  1   1