Canadian Payroll 'Year end' processed before applying 'Tax Update' patch

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hi, Due to time constraints, we had no choice but to run the Canadian Payroll Year end process (on GP2013) before the tax update patch from Microsoft was released. This means that the first pay of 2014 was generated with the amounts of 2013. The patch has now been applied but we need to modify the tax credit amounts and basic amounts etc. Where do I adjust these amounts? Can I just go in the 'Tax Credit Indexation Factors' window(Tools/Setup/payroll Canada/control, then Tax Credit button) and modify them manually? Also, what tables do I need to update to adjust the basic amount for each employee.. Can you please list me the SQL tables I need to update?

Many thanks

*This post is locked for comments

All responses (22)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Canadian Payroll 'Year end' processed before applying 'Tax Update' patch

Hi,

Ohh, I remember that bad patch from Microsoft which messed up the T4 numbers. We ran the SQL scripts and got the right numbers but only after our T4's were generated and delivered(I guess we were too proactive) so we had to rerun the T4's thanks to Microsoft... So all of our employees received 2 T4's that year.. :-/

Now for the CPY50103 and CPY40101 business: the good thing is that I have a backup that I took before running the year end.. I will go check the status of those 2 tables in that database to see if they are different... We are unfortunately too far in the year (already 2 pays were generated) to restore so I think we will have to live with the small discrepancies we have.. The major T4 boxes (14, 16, 18,22) have no discrepancy, fortunately... I will investigate and I will let you know of my findings if you are interested.... I may ask our Microsoft Partner (for a fee of course -))

Have a good day.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Canadian Payroll 'Year end' processed before applying 'Tax Update' patch

I would think that the Year End reset probably calculates some values and then dumps CPY40101 and transplants the data from CPY50103 into CPY40101. Given that they say to make a backup BEFORE running this and that it's not un-doable it's not hard to stab in the dark and say that it must be doing that. The issue is knowing what else it might do, to other tables or parts of the database (or how it transplants the numbers, does it select and update raw numbers or run them through a formula to validate them?). I generally tend to stay far away from the DB, only using SELECT statements to find say a particular number so I can cross reference that back into an application window.

Last year at T4 time Microsoft put out a bad patch which messed up the T4 numbers for a lot of people, our systems included. There was a post patch in the form of EXE and in the form of a SQL script. The SQL script could be run on it's own (which we did and it fixed it without having to go through a full day of updating desktops, servers, and the damned company database updates! - We have 30 companies so it's a full 8 hour day each patch cycle for us!). If you had the wrong patch last year and never got the SQL script to fix it that might result in some calc somewhere not working right this year.

community.dynamics.com/.../canadian-payroll-t4-cpp-qpp-and-ei-maximums-stopping-at-2011-rates.aspx

If it's really wrong I wouldn't mess with it much more, I would say get a VAR who deals with GP or open a case with MS. They will know how to fix the tables and frankly it's not worth risking having a perpetual flaw in the system. And with the CRA looking over these numbers for all your employee's it's not worth the risk in having something funny showing up.

My advise, kick all the users out if you didn't run the 2012 tax update patch last year or your numbers were weird last year, back it up, look at the code of the patch, run it, check your T4 numbers for this year, if they are right you're probably fine. If not, I'd stop, restore your DB to before you ran the 2012 SQL patch and then let your users back in. There should be transactions made during your testing phase IF you went down this road.

Remember I'm just some guy off the internet, I don't work for Microsoft and you're risking your data if you try my suggestions. They're just ideas I have, I make no promises they will work or not. :-o
Community Member Microsoft Employee on at

Like (0)

Report

RE: Canadian Payroll 'Year end' processed before applying 'Tax Update' patch

We are running the 2nd pay today.... Hoping all will be fine..I'm crossing my fingers..... I was told by HR that we do not use the vacation pay in CPY that Tami was refering too.. I'm just hoping it was not too much of a nightmare for them to got all this fixed..

I have yet another question though about YearEnd and T4's

Does someone know what is the difference between the following 2 tables in CPY: CPY50103 and CPY40101. I know that CPY50103 only keeps last year T4 as opposed to CPY40101 but when I look at my T4 for last year (2013), I do not have the same values in a few fields (such as PEIInsEarnings24 and PCPPPensionEarn26). The right amounts are in CPY50103

If I look at the resource description (in tools) the tables are called the following

CPY50103 --> P_CPY_Employee_T4_MSTR_LY

CPY40101 --> P_CPY_T4_Summary_1998on

I know that if I do an inquiry on a T4 for last year (inquiry/Employee LY info screen) I get the data from table CPY50103. But it seems that YearEnd functions take data from CPY40101 (tables in the 4000 range)... as I ran some sql queries and figures out that when you run the T4 Summary function (in the YearEnd routines) that it takes data from CPY40111 and generates a summary from the CPY40101 table..

I'm trying to understand all this because our T4 Summary report - (routine/payroll canada/T4 summary) does not give accurate numbers (we tracked numbers per pay in excel and the total does not exactly match)

So why are my T4's in cpy50103 and cpy40101 not the same?..

Many thanks....
Community Member Microsoft Employee on at

Like (0)

Report

RE: Canadian Payroll 'Year end' processed before applying 'Tax Update' patch

I found out our situation is a custom configuration for this one employee. So I've left the SQL table alone, still hunting down how this custom configuration was done in years past. Nobody in accounting seems to recall how they got it into the system in the first place :-(

Glad it worked out for you!
Community Member Microsoft Employee on at

Like (0)

Report

RE: Canadian Payroll 'Year end' processed before applying 'Tax Update' patch

This is exactly what I did.. Thank you... Hoping that there will be no hickups.. ;-)
Community Member Microsoft Employee on at

Like (0)

Report

RE: Canadian Payroll 'Year end' processed before applying 'Tax Update' patch

I don't know that you're actually supposed to do this but it seems like it's possible to just update the numbers in the master tax table. For Canadian Payroll it's under [COMPANY DATABASE] dbo.CPY10105. The table schema is per employee, and each valid employee has two entries (one is marked CA (Federal), and the other ZZ (Prov: AB, SK, BC etc...). Here you can update the tax fields... below is the schema:

[PEmployeeID]

[PJurisdiction]

[PTax]

[PBasicPersonalAmount]

[PSpousalAmount]

[PEquToMarriedAmt]

[PDisabledDependantAmt]

[PPensionAmount]

[PAgeAmount]

[PSeniorSupplmntAmt]

[PTuitionAmountFT]

[PTuitionAmountPT]

[PDisabilityAmount]

[PCaregiverAmount]

[PSpouseAgeAmount]

[PSpouseSeniorSuppl]

[PSpousePensionAmount]

[PSpouseDisabilityAmount]

[PSpouseTuitionAmtFT]

[PSpouseTuitionAmtPT]

[PDepndDisabilityAmt]

[PDepndTuitionFT]

[PDepndTuitionPT]

[PDependentsUnder18]

[PDisabledDependants]

[PLSF]

[PEstimatedAnnualIncome]

[PEstimatedAnnualExpense]

[PDesignatedAreaDeduction]

[PAdditionalTaxDeduction]

[PAuthAnnDed]

[PAuthAnnTaxCred]

[POtherAnnualIndexableAmt]

[POtherAnNonIndexAmt]

[PT4AAddtnlTaxDed]

[PMarriedOrEqu]

[PEmployeesDisability]

[P_Child_Amount]

[DEX_ROW_ID]

* Of note, I've never played with these tables, but I've got one employee in this table with incorrect numbers, everyone else has the correct numbers post 2013 tax table update. I'm toying with the idea of manually updating these rows to fix the issue.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Canadian Payroll 'Year end' processed before applying 'Tax Update' patch

Many thanks... I will tackle all this tomorrow... Time to go home.. Thank you everyone..
Community Member Microsoft Employee on at

Like (0)

Report

RE: Canadian Payroll 'Year end' processed before applying 'Tax Update' patch

Can you give me the Display names of these UPR tables? I could probably find their match in CPY..

I hope I will not have to do what Tami did.. (ie restore).. I'm not sure I would be able to anyways... Too much data has been entered and modified in our system. If it gets too complicated.. we may just leave everything as is and let the Canadian Revenue Agency deal with all the errors on our employees 2014 T4's.. and adjust only for extreme problematic cases...
Accounting Department 1,540 on at

Like (0)

Report

RE: Canadian Payroll 'Year end' processed before applying 'Tax Update' patch

It will be under Resource Descriptions > Canadian Payroll and Series = Project.

Employee Tax Credits is CPY10105 (history CPY50105)

Each employee should have a "CA" record for federal tax as well as a Provincial record.

If you have an up-to-date copy of your live company in a Test database it would be best to do your testing there first.
Richard Wheeler 75,730 on at

Like (0)

Report

RE: Canadian Payroll 'Year end' processed before applying 'Tax Update' patch

I do not have the Canadian Payroll module loaded. YOu can try going to Tools>Resource Descriptions->Tables and choosse Third Party,