Canadian Payroll Year-End got wrong numbers for Quebec…

Every year-end it’s the same race for Dynamics GP customers that are using either the US  or the Canadian Payroll module in their system, they have to apply tax updates to account for the new deduction rates and personal credits. The US customers are lucky, because their tax updates are not tied to a full service pack, as Microsoft managed to put them in separate tax tables, thus a simple XML file import is doing the job.

Not so much luck for the Canadian Payroll users, as the tax rates & calculations are somehow not completely stored in tables, but also embedded into the core code of the module.. which makes the update only possible thru a full service pack to update the core code of GP (usually there are only 3-4 modules that got a new build number).

Fellow MVP Jen Kuntz has posted a brief note about this on her blog, with the links for the downloads.

The less funny part is that when the US tax updates get released somewhere around mid to end of November, the Canadian Government manages to release those new numbers only very late, sometimes in early December, but due to the singularity of the Province of Quebec, which has it’s own tax rules, those new rates get often delayed until mid-December.

Which brings me to the point that the poor GP development team in Fargo has to rush the code changes & testing often in as little as 1 or 2 weeks to get it out in time before the Christmas closing (or Year-end). Every year we make some bets about when it’s going to be released, not so happy to see preliminary tentative dates on Customer Source giving Friday December 22 as release date. This often leaves us with no spare time to TEST out the service pack and then apply it to our Production environment before closing shop for the holidays.

However, yesterday (Dec. 20st), the Canadian Payroll service pack 2017 for GP2013 was delivered as an early X-mas gift and we could install it & run some payroll tests.. Only to realize this morning when the Paymaster looked up the personal tax deduction for the employees to check if the rates were aligned with the official documents from CA & QC, and he told me that the values didn’t look good :-(.

According to the Year-End Tax update document itself (and confirmed with official documents), the tax credit for personal deduction should be 15’012$CAD in the Province of Quebec, whereas here we clearly see that the value is identical with the Canadian Basic Personal Amount. Somehow this value is not consistent in the Year-End documentation itself and should be corrected by Microsoft in the coming days. The code wasn’t updating the proper values..

If you have already run the payroll reset function, then all your employees would be currently set with the wrong value..

No panic ! There is a way to fix it with a quick SQL script that you can run against your GP company database where the Canadian payroll is used. Use the following code bellow to query your data and check the current amounts (Federal & Quebec)

-- Federal Amount
SELECT CPY10105.PBasicPersonalAmount
 ,CPY10105.PSpousalAmount
 ,CPY10105.P_Child_Amount
 ,CPY10105.PEmployeeID
 ,CPY10100.PLastName
 ,CPY10100.PFirstName
 ,CPY10100.PInactive
FROM CPY10105
INNER JOIN CPY10100 ON CPY10105.PEmployeeID = CPY10100.PEmployeeID
WHERE (CPY10100.PInactive = 0)

-- Provincial Amount
SELECT PQuebecMR19Base
 ,PQuebecMR19Line12
 ,PEmployeeID
 ,PFirstName
 ,PLastName
FROM CPY10100
WHERE Pinactive = 0

I’m not including inactive employees, as you may want to keep the rates of former employees, though you may want to review this on a case by case basis, as there might be employee on long-term leave (those get usually disabled to not get into the payroll run).

If the values that are returned are not correct, then you can fix them with the next script below:

UPDATE CPY10100 /*P_CPY_Master*/
SET PQuebecMR19Base = 15012.00000
 ,PQuebecMR19Line12 = 15012.00000
WHERE Pinactive = 0
 AND PQuebecMR19Base = 11730 --> last year's value
 AND PProvince = 'QC' --> do not update other provinces if you have

Also check out in the Employee card the spouse & child values at the Federal level, as some employees may want to claim the base amounts too according to the Federal form TD1(2018).

The Provincial amounts are defined in the TP-1015.3 which you can download the PDF from the link.

Happy Holidays and Happy New Year all.
Until next post..

Advertisement