Importing Bonuses


As a Payroll (or Admin) user, follow the steps below to import Bonus payments for a specific job.

  • 5/24/23 - Use the attached workbook to prep the data


Preparing the Document for Upload

  1. Confirm the provided job number is a valid bonus job. If it isn't, ask the requester for a valid one or to make one.
    • It must be a TSDE-Lead job
    • Have the SPECIAL - Bonus Payment project type
    • Must have an active service order
      • You may need to restore a V&E'd SO
    • The Customer Order cannot be FINAL
  2. Confirm all the provided pay dates are week-ending dates (Saturdays). Use this formula to check: =CHOOSE(WEEKDAY(CellWithDate),"Sun","Mon","Tue","Wed","Thu","Fri","Sat") 
    • If any are NOT Saturdays, request week-ending dates.
    • If all ARE on Saturdays, check the timing.
      • If in the PAST, you may proceed to 3.
      • If in the FUTURE, it's best not to be too far ahead. The furthest date should be within the upcoming payroll or the following one.
    • Cannot have leading 0s in the month/day of the date (ie. not 07/06/2023, must be 7/6/2023)
  3. Ensure the columns are in this order: QTrax Job, RepID, First Name, Last Name, Bonus Amount, Description, Date Worked.
  4. Enter a description.
    • The description can't be more than 50 characters and should include work and payroll dates.
    • Use this formula to generate the description: ="Description (w/e "&TEXT(CellWithWorkDate,"mm/dd/yyyy)") & " mm/dd pay date"
    • Use this formula to check the length: =LEN(cell)
  5. The data is now ready to upload.


Uploading the Data 


Note: A 360 account with Payroll permissions must be used.

  1. Pull up the bonus job in 360.

  2. On the Job Dashboard, click the Enter Payroll Records button in the Advanced Tools section (on the left). This will open the TSDE Time Sheets page.



  3. On the TSDE Time Sheets page, the SOID in the table grid will be the SOID the bonus payments will be added to for the job.

  4. Click the Add Bulk Payment button.

  5. Click the Pay Type drop-down and select Bonus.

  6. Paste in the bonus data, including the headers (again, the column order should be: QTrax Job, RepID, First Name, Last Name, Bonus Amount, Description, Date Worked).
     
  7. Click the Verify Data button. This will review your list and identify any issues with the data (duplicated rows, incorrect QTrax name, etc.).
    • If there were any issues, go to How to Handle Validation Issues? (below).

  8. Confirm the Total Amount matches what you expect.
    • If it does, click the Add Payroll Payments button.
    • If it doesn't, double-check that the original Excel file has Set precision as displayed selected (File>Options>Advanced>When calculating this workbook). If bonuses are longer than two decimal points, there can be rounding discrepancies.

  9. Let the requester know the bonuses were uploaded to the specified job, that they'll appear on the specified week-ending timesheets, and confirm the pay date everything will be processed on.

Importing Bell Canada Commissions and Car Allowance Payments

Currently, we can only bulk import Bonuses or Expenses. Bell Canada requires some payments to be coded as Commissions or Car Allowance for tax purposes. Until those categories are added to the bulk-import dropdown, the payments must be imported as bonuses and then changed to the appropriate type. 360 Support must make this change.



--Identify imported bonuses
SELECT  PayType, QCode, *
FROM  QTraxCanada.dbo.tblTimeSheetDetails
WHERE  JobID=      --bonus job
AND    ts_datetime>=  --date of import

--After confirming only the desired bonuses are selected, update the type
UPDATE  QTraxCanada.dbo.tblTimeSheetDetails
SET    PayType=,  --'Commissions' or 'Car Allowance'
    QCode=    --140 for commisions, 130 for car allowances
WHERE  JobID=      --bonus job
AND    ts_datetime>=  --date of import



Validation Issues

If an issue is found with the data validation (see the example screenshot below), follow these steps:

  1. Click 'Cancel'
  2. Correct the Issue reported in the original Excel file
  3. Re-import the data by copy/paste the corrected records into the grid
  4. Click 'Verify Data'
  5. Confirm the Total Amount matches what you expect.  If so, click the 'Add Payroll Payments' button.



If you upload records from multiple bonus files at the same time, you may get the below error. Uploading the files separately should fix this, but you might need to sign out or clear your cache, as doing everything in the same session still caused the error.