Last week, I was asked if I knew of a quick way to update the Basic Personal Exemption ("BPE") amount in Dynamics GP Canadian Payroll. It's not the first time I've been asked as it's relatively common for an "oops" on the CPY Year End Close routine to either forget to update the personal exemption amounts or that somehow the wrong amounts are in there. So, today's #TipTuesday is a short one: what's the quickest (& safest) way to update this if it was wrong on multiple people?

What is it?

The Basic Personal Exemption amounts for either Federal or Provincial tax purposes are most commonly seen on a TD-1 Form that an employee fills out when they are hired or need to make changes. The BPE is fixed each year so the Canadian Payroll module's year-end close routine has a window where the % change for the new year is verified and then the process will mass update all the employee records to the right value during the year-end process.

The window itself, on an employee card, is under TD1 Values > Tax Credits and from there, it defaults to "CA" (Federal). To view a specific jurisdiction, click on the Clear button on the toolbar, which will make the Jurisdiction selectable.

Payroll Employee Tax Credits window in Canadian Payroll in Dynamics GP

If I only had to update one employee, I would select the correct jurisdiction and click on the Update box to enable editing of the values in the window. However, if I forgot to run the indexing or if the indexing % was incorrect, here's a quick SQL script to correct the values.

SQL Update

The table name that contains the current year's TD1 tax credit info is the CPY10105 table. It will typically contain 2 records per employee if your employee works in one jurisdiction (one will be "CA" for federal and one will be for your province or territory).

It's super important to know which tax credit BPE needs to be updated as it is easy to inadvertently update all jurisdictions on each employee with an incorrect amount if one is not careful.

As with any SQL direct update approach, TEST TEST TEST. This isn't a complex script but testing will save everyone from a lot of hassle if whoever runs this forgot the where clause or gets the amount incorrect.

My approach is this:

  1. Run a SELECT script on the table first, with the proper WHERE clause to get the number of records I need to update. These are the expected results I should see later. SELECT * FROM CPY10105 for example
  2. Test the script on a test company database first, and then check Dynamics GP to spot-check some of the employees that should have been changed (in their tax credits window). Better yet, get a screenshot of some employees before and after to ensure the updates were done correctly.
  3. When running it for real, I recommend using BEGIN TRANSACTION and COMMIT TRANSACTION or ROLLBACK TRANSACTION in a worst-case scenario.

Below are some scripts to update Federal BPE amounts. I'm showing 3 common examples of how you might do this.

  1. Setting the amount to a hard-coded value for a jurisdiction.
  2. Setting the amount to a % based on the previous amount for a jurisdiction.
  3. Setting the amount to a static value for a given jurisdiction AND where the previous amount is specific.

I've had to use different approaches at different times depending on what went wrong. For example, many years ago a client noticed that the exemption % was only updating active employees who had income "last year" during the year-end close process. If they re-activated old employees (after a layoff for instance), they all had out-of-date TD1 values so we wanted to mass update but not all employees at a %, as that would increase everyone's including those that were already correct. In that case, we used a version like #3 below, only updating those with a specific previous value.

-- Example 1 - Update script with a hard-coded amount
UPDATE CPY10105
SET PBasicPersonalAmount = 12069.00
WHERE PJurisdiction = 'CA';

-- Example 2 - Update script with % change
UPDATE CPY10105
SET PBasicPersonalAmount = PBasicPersonalAmount * 1.02
WHERE PJurisdiction = 'CA';

-- Example 3 - Update script only where previous matches a specific amount
UPDATE CPY10105
SET PBasicPersonalAmount = 12069.00
WHERE PJurisdiction = 'CA'
AND PBasicPersonalAmount = 7748.00;

That's it for this post, it's relatively straightforward if payroll needs to do some bulk updates at some point in their environment.