Mass Update CPY Basic Personal Exemption

Last week, I was asked if I knew of a quick way to update the Basic Personal Exemption 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?

I believe if you’re reading this, you are already aware of what this is but to be a little more clear, it’s the Basic Personal Exemption amounts for either Federal or Provincial tax purposes, 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 you can verify the % change for the new year and mass update everyone that way 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) but once you click on the Clear button on the toolbar, the Jurisdiction is selectable.

If you only had to update one employee, you would select the correct jurisdiction and click on the Update box to enable editing of the values in the window. However, if you 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 you want to update as you could inadvertantly update all jurisductions on each employee with an incorrect amount if you’re not careful.

As with any SQL direct update approach, TEST TEST TEST. This isn’t a complex script but testing will save you from a lot of hassle if you forget the where clause or get 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. This is the expected results I should see later.
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 screen shot of some employees before and after to ensure you updated the right thing.
3. When running it for real, use BEGIN TRANSACTION and COMMIT TRANSACTION as a safety net if you’re uncomfortable still, after careful testing. (Google it if you’re unsure what I mean by this…)

Below are some example 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 a specific amount.

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.

-- Check the # of expected results in my specific where clause
SELECT * FROM CPY10105 WHERE PJurisdiction = ‘CA’

-- Update script with hard-coded amount (example 1)
UPDATE CPY10105
SET PBasicPersonalAmount = 12069.00
WHERE PJurisdiction = ‘CA’;

-- Update script with % change (example 2)
UPDATE CPY10105
SET PBasicPersonalAmount = PBasicPersonalAmount * 1.02
WHERE PJurisdiction = ‘CA’;

-- Update script only where previous matches a specific amount (example 3)
UPDATE CPY10105
SET PBasicPersonalAmount = 12069.00
WHERE PJurisdiction = ‘CA’
AND PBasicPersonalAmount = 7748.00;

That’s it for this post, it’s a pretty straight-forward one if you need to do some updates at some point in your environment.