List of Time and Labor Queries in HRS

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

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.
   
   
   

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

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

      

   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 ‘A48’ then you would enter A48% as a wild card. This would return all values beginning with A48. Such as A480000 or A4800200.

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:

To the top

---

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:

To the top

---

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:

To the top

---

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:

To the top

---

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:

To the top

---

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: 

To the top

---

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)

Results:

 

To the top

---

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:

To the top

---

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: 

To the top

---

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:

To the top 

---

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: 

To the top

---

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:

To the top

---

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: 

To the top

---

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:

To the top

---

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:

To the top

---

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:

To the top

---

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: 

To the top 

---

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:

To the top

---

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:

To the top

---

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:

To the top

---

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: Dept Id, Empl Type

Results:

To the top

---

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:

To the top

---

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:

To the top

---

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:

To the top

---

Query: Students Not Using Punches

Query Name: UW_TL_STUDENT_REP_REV

Who: Payroll Coordinators
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 Date

Results:

To the top

---

Query: Active Employee TL Security Configuration

Query Name: UW_TL_TLSEC_CUR

Who: Payroll Coordinators, Support Staff
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:

To the top

---

Query: Review Payable Time Approval History

Query Name: UW_TL_PAY_TIME_APPR_REV

Who: Payroll Coordinators, Support Staff
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:

To the top

---

Query: Differential Supplement Setup

Query Name: UW_TL_DIFF_SUP_SET

Who: Payroll Coordinators, Admins, Service Operations
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:

       

To the top

       

To the top