Transaction Master Summary Exceptions Report

Overview

The Transaction Master Summary Exceptions Report uses the Transaction Master Summary report as the base report, and applies various exceptions parameters to the aggregate hours for the week. For example, if the exception is "OT > 10", the report looks for more than 10 hours of Overtime in the entire week.

This is one of three similarly-named reports, which provide visibility to the same data through a different lens. The other Transaction Master Reports are:

• Transaction Master Detail: Full line-by-line report of each pay and bill transaction
• Transaction Master Summary: Rollup of the Transaction Master Detail report; summarized by Candidate Placement ID, Week ending date, and earn code.

The Transaction Master Summary Exceptions report provides visibility only to the standard Bullhorn One earn codes, Reg (Regular), OT (Overtime), and DT (Doubletime). For agencies leveraging multiple Reg, OT, or DT earn codes, the report will need to be customized to support multiple earn codes for these categories.

Best Practices

If you are running a weekly payroll, run the report for one week of data. If the pay cycle is bi-weekly, then running the report for two weeks is the best practice.

Because of the urgency to review exceptions after pay and bill processes have completed, it is recommended to review the first page of the report, which provides a total number of exceptions identified per Exceptions Rule. If a significant number of exceptions are identified, it is a best practice to:

1. Export the report to Excel.
2. Share the Excel report with co-workers.
3. Assign specific Exception Rules to each team member to research.
4. Regroup as a team to discuss the Exception Rules that need to be addressed and resolved.

Report Uses

The Transaction Master Summary Exceptions Report provides a color-coded visual representation of Candidate Placements that fall outside of the parameters you set. If there is an exception against one of the Exception Rules, the cell for the applicable rule will be red and include the word “Exception”. The color green indicates that the Exception Rule falls within the parameters you set.

This report is a summary by Placement ID, by week ending, by earn code. If a Candidate has four earn codes for the week, you will see four rows associated with the Candidate on the report: one row for each earn code. All earn codes associated with the Candidate will be displayed, even if only one of the earn codes has an exception against it.

You will only see transaction summary data on the report when there is at least one Exception Rule that falls outside of your parameters. If no exceptions are found, no transaction summary data will appear on the report.

Payroll and Billing Specialists should run this report after billing processes are completed as a quick data validation or “smoke test”. This can often surface any issues that may have occurred, so that they can be quickly addressed.

You can then use the Transaction Master Detail Report if additional detail is needed to research the exceptions.

Running the Report

1. From the Menu, select Canvas.
   
2. When Canvas opens, select Run beside the Transaction Master Summary Exception Report.
   
3. Enter the criteria for the report.
   • The Period End Date Range is the only required criteria, but you should also add at least one exception before running this report.
   • If you need additional report criteria/parameters, enter the date range, then click Next to indicate additional criteria on the next page.
     
   • If you select Next, the second screen of report criteria allows you to enter optional filters. Select Finish to run the report.
     
4. The initial screen will display the exception parameters for which the report was run, and a total number of exceptions that were identified for each Exception Rule. This gives you a snapshot of the volume and types of Exceptions that were identified.
   
5. The report details will display on the next screen. Depending on the amount of data, it may be easier to view the report via Excel.
   1. To export to Excel, select the drop down menu at the top of the report, click View in Excel Options, then select View in Excel 2007 Format.
      
      
      • The View in Excel 2007 Format option maintains the same report format as the Canvas report. If you do not need to keep the Canvas report formatting, then select the Data option.
   2. Once the applicable Excel option is selected, Bullhorn One will process the request, and the following screen will appear. Click on the xlsx file to open the file in Excel.
      
6. Once the report is exported to Excel, you can utilize standard Excel functions to filter, sort and pivot the data. Two tabs will display on the Excel report: Summary Totals, and Exception report.
   • The screenshots below represent a vertical view of the data, which include columns from A to AQ. Due to space constraints, multiple screenshots are provided to show all columns displayed on the report.
     
     
     

To view a full sample Transaction Master Summary Exceptions Report, click this link:

Sample Transaction Master Summary Exceptions Report.xlsx

Report Criteria

These options are available as criteria:

• Period End Date Range (Required)
• Payable Export ID (Optional)
• Placement ID (Optional)
• Bill-to Client (Optional)
• Placement Type (Optional)
• Client Corporation (Optional)
• Employee/Candidate (Optional)

Exception Rule Parameters

You can adjust the Exception Rule parameters to be agency-specific, client-specific, or for one-off scenarios. The chart below provides an overview of the available exception rules to run as part of the report.

• Each Exception Rule is optional; you can run the report with all or any combination of rules.
• Some Exception rules include an operator, which provides additional flexibility for you to customize the report. Operator examples include:
  • < : less than
  • > : greater than
  • = : equals to
• In the chart below, the "XX" represents your ability to enter a number parameter prior to running the report.

Exception Title	Exception Criteria
Reg Threshold	"Operator " XX Regular Hours
OT Threshold	"Operator" XX OT Hours
DT Threshold	"Operator" XX DT Hours)
Total Pay Hours Threshold	Total Pay Hours > XX
OT > Reg Pay Hours	OT Hours > Reg Pay Hours
Unequal Pay/Bill Hours (Pay & Bill Hours do not Match)	Pay Hours != Bill Hours
Pay Amount Exceeds Threshold	Total Pay Amount > $X
Exceeds Pay Rate Threshold	Total Pay Rate > $X
Multiple Timecards	Yes or No
Multiple Weekending Dates	Yes or No
Pay Amount > Bill Amount	Yes or No
Pay Amount with no Bill Amount	Yes or No

Fields

These fields are available on the report:

Data Item in Report	Definition
Reg Pay Hours Exception	The Placement falls outside of the parameters set when running the report for Reg Hours earn code. This report currently identifies only the standard Reg Earn Code. If your agency incorporates multiple Reg earn codes, this exception will only include the standard Reg earn code. Future report enhancements are expected to allow for a broader range of Reg earn codes.
OT Pay Hours Exception	The Placement falls outside of the parameters set when running the report for OT Hours earn code. Unlike the Reg Hours earn code, all OT earn codes will be captured and included in this exception.
DT Pay Hours Exception	The Placement falls outside of the parameters set when running the report for DT Hours earn code. Unlike Reg Hours earn code, all DT earn codes will be captured and included in this Exception.
Total Pay Hours Exception	The Total Pay Hours paid falls outside of the parameter set when running the report. This is a good “smoke test" to ensure a candidate hasn’t submitted a timesheet for an excessive number of hours.
OT > Reg Hours Exception	The number of OT hours is greater than the number of Reg hours for the candidate Placement ID. This report currently identifies only the standard Reg Earn Code. If your agency incorporates multiple Reg earn codes, this exception will only include the standard Reg earn code. Future report enhancements are expected to allow for a broader range of Reg earn codes.
Unequal Pay Bill Hours Exception	The number of Pay Hours does not match the number of Bill Hours for the earn code. This is a good “smoke test" to ensure that the agency is properly billing for hours that they are paying, or to determine that there is a legitimate reason why these are not billable, or vice versa.
Pay Amount Exception	The Pay Amount exceeds the threshold set as a report parameter. This is a good “smoke test" to ensure a candidate isn’t paid a huge amount due to an accident or a data entry error.
Multiple Timecards Exception	If you set “yes” as the parameter, this exception will identify the Candidate ID that has multiple time cards submitted during the designated time frame. Payroll may need to review to ensure time and bonuses are properly paid based on locality pay laws.
Multiple Weekending Dates Exception	If you set “yes” as the parameter, this exception will identify the Candidate ID that has multiple time cards which represent different week ending dates during the designated time frame. Payroll may need to review to ensure time and bonuses are properly paid based on locality pay laws.
Pay > Bill Exception	If you set “yes” as the parameter, this exception will identify the Candidate ID where the total pay amount exceeds the total bill amount. This is a good “smoke test" to ensure that the agency is properly billing for hours that they are paying, or to determine that there is a legitimate reason why these are not billable.
Pay no Bill Exception	If you set “yes” as the parameter, this exception will identify the Candidate ID where the candidate has a pay amount, but there is no bill amount associated with the placement. This is a good “smoke test" to ensure that the agency is properly billing for hours that they are paying, or to determine that there is a legitimate reason why these are not billable.
Client	Company Name from the Company Profile associated with the Placement
Bill To Client Name	Company Name to which the billing is sent, which can be different from the Client Name. Example: The candidate may be working at IBM; however, the invoice is sent to the MSP provider (e.g. Beeline).
Candidate Name	Candidate name listed on the Bullhorn Candidate profile.
Candidate ID	ID number automatically assigned by Bullhorn on the Candidate profile.
Job ID	ID number automatically assigned by Bullhorn on the Job Order profile.
Job Title	Job Title listed on the Bullhorn Job Order profile.
Placement ID	ID number automatically assigned by Bullhorn on the Placement.
Employee Type	Employee Type selected on the Placement. Options include, but are not limited to: W2, T4, 1099.
Employment Type	Type of employment selected prior to entering the Placement data. Options include, but are not limited to: Contract, Contract to Hire, Permanent.
Bill Item	Represents the revenue category for the charge that is linked to a GL account; determined from the related earn code’s Bill Item field. This may be empty for agencies not leveraging Bill Items.
Class	General category to describe the Placement’s employment type (i.e. if the Employment Type is "Contract", then it is consultant hourly). Depending on the transaction, it is determined from the charge, Placement or related earn code. This field is typically utilized with QuickBooks.
Period End Date	The end date of the timecard associated with the transaction.
Payable Charge ID	ID number automatically assigned by Bullhorn on the Payable Charge.
Pay Status	Bullhorn Status of the Payable Charge. Options include: Exported, Processing, Export Error, Ready to Pay or Not Ready to Pay.
Pay Transaction Status	Status of the Timecard/Payable Charge. Options include: Submitted or Approved.
Pay Transaction Origin	Origin of the Pay Transaction Data. Options include: PeopleNet or Manual.
Pay Description	Combined description of: Candidate Name, Job Title, and Period End Date. Example: Elizabeth Green - Inspector - 2022-01-09
Pay Earn Code	Earn Code associated with pay being processed. Options include, but are not limited to: Reg, OT, DT, Per Diem, PTO, etc.
Pay Qty	Number of hours/units paid associated with the pay earn code.
Pay Amount	Pay Qty multiplied by Pay Rate.
Billable Charge ID	ID number automatically assigned by Bullhorn on the Billable Charge.
Bill Status	Bullhorn Status of the Billable Charge. Options include: Ready to Bill, Needs Review, Invoicing, Invoiced, Unbillable, Processing Failed, Processing, or Not Ready to Bill.
Bill Transaction Status	Status of the Timecard/Billable Charge. Options include: Approved or Submitted.
Bill Transaction Origin	Origin of the Bill Transaction Data. Options include: PeopleNet or Manual.
Bill Description	Combined description of: Candidate Name and Period End Date. Example: Elizabeth Green - 2022-01-09
Bill Earn Code	Earn Code associated with billing being processed. Options include, but are not limited to: Reg, OT, DT, On Call, Call Back, etc.
Bill Qty	Unit of Measure associated with the bill earn code. Examples include: hours, units, days, or miles.
Bill Amount	Pay Qty multiplied by Bill Rate.