Monthly Archives: December 2017

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
INNER JOIN CPY10100 ON CPY10105.PEmployeeID = CPY10100.PEmployeeID
WHERE (CPY10100.PInactive = 0)

-- Provincial Amount
SELECT PQuebecMR19Base
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..

Categories: Breaking News, Canadian Payroll, Dynamics GP, News, Year-End | Leave a comment

Why are my Project Timesheet Periods behind?

The initial situation

Are you using Project Accounting Timesheet (TS) entries in GP ? maybe you’re still on version 2013 & lower, which means you also use possibly Business Portal & PDK to manage and process the TS reports submitted by your employees.

If you’re wondering about the fact that after several years of TS report entry, your weekly period # has started to shift back.. and like in our company where GP has been implemented back in 2005, the shift currently is 2 full weeks .. Why you may ask ? just because every year isn’t equal in Calendar and doesn’t start exactly on Jan. 1st for your 1st day of reporting, which might be Saturday, Sunday or even Monday for some companies (not even counting for leap years).

In PDK this translates into showing the period #52 ending on Dec.  15th, this year, as shown in this screen capture:


After 12 years of weekly reporting

How do I fix it ?

Now someone would say that is an easy fix, as I’ve seen suggestions on a few forum posts or discussion, but it is not.. 1st place where they tell you to go is under “Project > Setup > Timesheet” and change the number of reporting periods per Year.. Alas, it doesn’t work like this, since PDK & GP is coded to ignore that value, when the option for field ‘Reporting Periods’ is anything else than Miscellaneous.


Original setup in GP

I’ve been poking around this setting long enough to confirm that this is possibly a bug, which wasn’t intentional I guess by Microsoft, but the truth is that the only purpose of that field is to provide a divider value for the Miscellaneous option.


(Note: the only time this Period # is actually validated by GP is during the posting of the TS batches)


In fact, when you setup and chose the option Miscellaneous the first time (before ever posting any TS report), GP will pop-up a message and claim that the value has to be greater than 0, but not bigger than 365 or 366 in case of leap years (actually saying it can’t be less than 0, but even zero is rejected).


How do I fix it ?

So, how to you gonna fix the shifting week issue ? The only field you have a control over, is the “First Date of Reporting Period 1”.. Every 5-6 years, when the calendar has shifted thru a whole week, your reporting period #1 might get out of sync and start in the previous year.. All you have to do is adjust the starting date to the new year, et voilà !


With the First Date of reporting changed to 2016-12-31, Period #52 is correct in 2017.


It’s not until 2021 that you’ll have to worry again to change the start date 🙂



Don’t do that while you have unposted TS reports in the system, since the period field in the table is set when a TS report is edited, but once submitted for approval, it doesn’t change anymore, and the last thing you want is to have a duplicate entry for the same period # in the current year (those are key fields for the PDK tables), ending with a xxxx-2 sequence report number.

Plan ahead of time your date change, and let all your reporting employees know that nobody should submit a new Timesheet into the new year, before you updated the First date of reporting for the next calendar year.

As always, it is warmly recommended to try out those settings first in a TEST company, and validate it with a couple of new TS reports to see if everything lines up correctly.  This shouldn’t affect the payroll periods setup, as those are managed in a different place, but if you post your TS reports directly to the US Payroll (which is an option in the PA TS Setup), you may want to check this out too.. As I don’t have a US Payroll setup in our companies, I wasn’t able to validate that aspect.

What if I have Timesheets started in the wrong period?

Two choices: either the TS report wasn’t processed in PDK and posted to GP, in which case you can simply return it to the user and make it edit to re-submit.. this is enough to update the Period field in the table after you updated the start date in the setup… or the TS was already processed and posted to GP, in which case you can run a SQL update to ‘switch’ those transactions from the new Period to an ‘extension’ of your 52 periods, this could be 53 or 54, depending how far you’re behind in the calendar.

TS Processed in PDK, but not posted in GP :
– update PDK10000 & PA10000 from the company DB

and PAYR = 2017

and PAYR = 2017
and [USERID] = '*PDK*'

Note: GP will not post a TS batch if the period is greater then the current # in the PA TS Setup window and eventually throw an error. To bypass this, just go back to the setup window and change the number of periods for the year from 52 to 54 (or whatever your max value needs to be). Don’t forget to put it back after posting.

ScreenCap 2017-11-30_181108

TS Processed in PDK & already Posted in GP:
– update historical table PA30100 (there is not history for PDK as everything is kept in PDK10000)

and PAYR = 2017

and PAYR = 2017
and [USERID] = '*PDK*'

But what if I want to change my Reporting Periods option ?

You can’t … at least not from the front-end GUI of GP.. Once it has been set and Timesheets have been posted into the system, this option is greyed out.

ScreenCap 2017-11-30_171117

During my research on the Internet, I came across a forum posting from Andy Sather at Microsoft, that provided some insight on the 2 values.. However, there was a typo in his answer, as he gave the value 9 for Miscellaneous, which is incorrect.. it should be 8 (I tested this in the Fabrikam company and looked up the value).

The only way to change it, is to grab to SQL Studio Management (or any tool to update a table value), and update the two fields driving the show. You’ll have to update two fields in table PA41801 from the company DB, which means repeat the process for each other company too.

-- Query content of PA Setup table

-- Update PA Reporting Periods option
-- Refer to this community post for the details 

SET PAreportingperiods = 3 -- bi-Weekly
 , PAnumofreportingperiods = 26 -- used when PAreportingPeriods = 8
Where PAreportingperiods = 2 -- Weekly
 and PAnumofreportingperiods = 52

I do not recommend the use of Miscellaneous in the Reporting Periods option, as PDK & GP don’t’ handle quite well fractions of weeks..For example, I tried to set the calendar year to use 53 or 55 weeks, but rather than dividing the 365 days in equal values, which is quite difficult when ending up with a decimal number ( 365 / 53 = 6.88 ), PDK & GP don’t round-up as you would expect, but rather just cut the numbers behind the decimal point..

Effectively, your period #52 is starting on Nov. 8th, finishing on Nov 14th, and your period #53 starts on Nov. 15h, but extends all the way until Dec. 31st.. not sure you want such  a long reporting period. Unless you have a very good reason to use a custom period setup, I suggest to stick with the regular settings from the list.

Hope this clarifies some of the mysteries around PA Timesheet periods setup and help you to fix some of the issues. Don’t forget to put a reminder every year-end to check your periods setup.

Until next post,

PS: I just noticed from a forum post on GPUG, that Equipment Log & Miscellaneous Log Setup suffer from the same issue, though in our company it was initially set to ‘Daily’ and uses therefore 365 periods/yr… which ended up to shift too after 13 years and we’re now already in period #21 on 2017-11-30, as it was never corrected in the past.

Update: There has been some interesting discussion around this topic on GPUG. Here’s is the link to the thread


Categories: Dynamics GP, PDK | Tags: , , , | Leave a comment

Blog at

Dynamics GP Tips and Traps For Users and Developers

Talking about Great Plains, Dexterity, SQL, and some C#


A fine site

Tom Roush's Blog

Musings on Life, Lessons, and Laughter...

Belinda Allen, Microsoft MVP Business Solutions

Things I've learned from years of working with Microsoft Dynamics GP.

David Musgrave's Winthrop Development Consultants Blog

This blog will keep you up to date with Winthrop Development Consultants and David’s experiences, tips and tricks as a developer and consultant in the Microsoft Dynamics GP community.

Gilbert Quevauvilliers - BI blog

My learnings and findings in the world of Business Intelligence

Build Numbers

Build numbers and version history for various products

Rambles of a non-IT person

Business development work to make lives easier

Dynamics GP - Learn & Discuss

Victory is directly proportional to Hardwork

Darryl Bajaro

- Codes, Processes and Tutorials


Keep Smile Always

The Dynamics GP Geek blog

My contribution to the never ending Dynamics GP journey

Q Factor's Blog

Microsoft Dynamics GP in a nutshell

Blog on IT

for when IT Fails...

Product-centric social site for tech pros. Get unbiased product ratings based on your peers firsthand experience. Connect with new peers to get the real story about products and services.

Gillian Horgan: An extraordinary eye on the world

Victoria Yudin

Ramblings and musings of a Dynamics GP MVP


All Dynamics GP Information