Running Benefit Retro Query in HRS

Overview

The Retro All Query is designed to give Administrators the ability to identify employees with calculated retro benefits that will be loaded to the next payroll. The query will allow a Benefit Administrator to check whether or not a calculated retro deduction is correct, and whether or not the amount can be deducted from the paycheck or paid through Benefits Billing. This query can and should be checked on a daily basis, as results are updated with each daily retro calc. The deductions, however, are not loaded to paychecks until typically two days before the payroll confirmation.

Process Considerations:

  • The UW_BN_RETRO_ALL query should be run during the payroll calculation week.
  • Report may change daily as a result of the retro calculation process.
  • The last day to review the report is before benefit retro loads to the paysheets which is typically two days before the payroll confirmation.
You may click on the links below to navigate directly to a section:

Instructions 

1.  Click Reporting Tools → Query → Query Viewer

2.  Enter  UW_BN_RETRO_ALL into the search criteria and click Search.

Query Viewer

3.  You are shown the Search Results below.  If you had done a partial search, multiple queries may appear in the list.  If you searched for the specific query you are looking for, you should only see that value below.  To view the query, click the link for Excel.

Query Results

4.  A prompt window will open.  On that page, enter the Business Unit you want to run the report for.  You can also search for the Business Unit by clicking on the magnifying glass next to the field.  After you choose your business unit, click View Results.

Query Prompt

5.  Open the query using Microsoft Excel and click OK or Open depending on your internet browser.

    Below is an example of Mozilla Firefox.

Query Popup

Below is an example of Microsoft Edge.

IE Popup

6.  Review the results and determine a course of action if needed.  Note: An employee may appear on the report with multiple rows if retro benefits has calculated for multiple pay periods and Benefit Plans.  Refer to Reviewing Retro Benefits in HRS  if you need assistance on reviewing or updating the retro benefit page.  The data appearing in the Excel spreadsheet is:

A.  DeptID = Department ID Number
B.  Pay Group = The pay group of the Empl Rcd that is marked the Benefits Primary job
C.  Name = Employee's Name
D.  Empl ID = Employee's ID Number
E.  Empl Rcd# = The Benefits Primary job record the retro is attached to.
F.  Plan Type = Category or grouping of Benefit Plans.  (ex:  Plan Type 10 include all health plans) A list of Plan Types are available in  Plan Types, Benefit Plans, and Payroll Deductions Job Aid in HRS.
G.  Benefit Plan = Specific insurance plan an employee is enrolled in.  A list of Benefit Plans are available in  Plan Types, Benefit Plans, and Payroll Deductions Job Aid in HRS.
H.  Benefit Program = The suite of Benefit Plans an employee is able to enroll in is referred to as their Benefit Program.
I.  Coverage Code = The level of coverage an employee is enrolled for (ex: family, single, etc.) A list of Coverage Codes are available in Using Health Insurance Coverage Codes and Relationships in HRS.
J.  Retro Ded Effective Date = The Deduction Begin Date that corresponds to the reprocessed Event or Job Row action.
K.  Old Ded Amount = The amount previously calculated as the employee's deduction for that Benefit Plan and level of coverage.
L.  New Ded Amount = The new amount calculated as the employee's deduction for that Benefits Plan and level of coverage.
M.  Override Ded Amount = Will default as zero. Benefit Administrators have the ability to override the amounts calculated by the system and enter the dollar amount to be refunded or deducted for each Benefit Plan and level of coverage.  If an override exists, a dollar amount will appear in this column.
N.  Retro Ded Amount = The net result of the old deduction, new deduction, and any override deduction amounts. 

If this value is negative, the employee will be refunded that dollar amount for that Benefit Plan and coverage level.  If the value is positive, the employee will be charged that dollar amount on the next available payroll check for that Benefit Plan and coverage level.
O.  Deduction Class = Indicates the tax status of the deduction.  Deduction Class values are B for Before Tax, A for After Tax, N for Nontaxable, and T for Taxable.
P.  Deduction Code = Indicates the benefit deduction code an employee is enrolled in.  A list of Deduction Codes are available in  Plan Types, Benefit Plans, and Payroll Deductions Job Aid in HRS.
Q.  Retro Ded Sequence Number = Indicates the retro deduction sequence number.
R.  Pay Status = Indicates the payroll status of the employee's benefit primary job.  Pay Status values are A for Active, D for Deceased, L for Leave of Absence, P for Leave with Pay, R for Retired, T for Terminated and S for Short Work Break.
S.  Term Date = Indicates the date employee terminated employment.
Report


Additional Resources

Related KBs:

Get Help



KeywordsExcel retro deductions payroll insurance paycheck OK to Process checkbox retroactive PSHUP PSHUP Load Query Viewer Query Manager payrolls classified unclassified grad LTE check box benefits public query university staff FA/AS/LI FASLI Monthly bi-weekly biweekly BN, Benefits BN, Benefits   Doc ID20583
OwnerChristina S.GroupUW–Shared Services
Created2011-10-11 09:03:50Updated2023-08-15 10:23:43
SitesUW–Shared Services
Feedback  0   0