Posts Tagged ‘Payroll’

What happens if I update Payroll tax tables and…

Hello all, here is something I was asked recently:

“I have processed my 2012 payrolls for the year, my IT is always on the latest and greatest regarding service packs, hotfixes, updates etc, and they installed the 2013 payroll tax tables update, and I have not executed the Payroll Year End, can I close it anyway?”

Short answer: Yes you can close it, but it will be incorrect.

Please if this happens to you, make sure to restore the Dynamics DB prior to the install of the payroll tax table update because I am sure you always do a backup of your databases prior to any updates installed right?

Leslie Vail has a post from 2011 where she explains how FICA/S wages and withholding is calculated Leslie’s Post

NOTE FOR LESLIE (Step 5b says “he year-end” instead of “the year-end”Smile)

But I wanted to provide a graphical representation of it

01-09-2013 12-21-51 PM


Until my next post…


Francisco G. Hillyer

It’s here again: Year End Processes 2012

So just like every year we go thru similar steps, I am providing some help along the way, 598846_4784214609951_774409899_nmany of the processes described here belong to a published KB from Microsoft as well links to posts from other resources but I wanted you to have them all in one place.

Please note that some of the links require you to have access to Customersource/Partnersource.


Difference between Year End Update and Payroll Tax Table Updates

The Year End Update download contains software changes to allow you to comply with 2012 filing requirements as well as the most recent fixes for your Dynamics application.

The 2013 Payroll Tax Table Updates contains updated rates and tax changes as well it might contain FICA/Medicare changes to be applied when processing 2013 payrolls.

Here is the link for the 2012 US Payroll Year End Update: Click Here

Here is the link for the US Payroll Tax Updates: Click Here

It is important to mention that you can install the Year end update prior to your last payroll for 2012, however do not install the Tax table updates before your last 2012 payroll as you might end up with a wrong calculation.

Always backup your data, your .DIC files as well.


Setting up / Adjusting Fiscal Periods in GL

Microsoft provides a KB article number 871679 KB871679


Closing Dynamics GP GL


Complete the posting/closing procedures for the modules in the suggested order prior to closing GL, please note that if you are not using a particular module, just skip it:

Follow the instructions provided in each KB

Inventory: KB 872713

Receivables Management: KB 857444

Payables Management: KB 875169

Fixed Asset Management: KB 865653

Analytical Accounting:

For Microsoft Dynamics GP 10.0 Service Pack 2 and greater, functionality was added to consolidate balances for dimensions in Analytical Accounting. Please review KB 960356 to make sure you have properly marked the dimensions that you want to be consolidated  during the year-end process. Please note that there is no separate year-end process that needs to be run in the Analytical Accounting module. When the year-end close process is run for General Ledger, it will automatically consolidate the balances and move the transactions in Analytical Accounting for dimensions that were properly marked.

PAYROLL year end procedures are independent of the procedures in other modules and are usually performed at the end of the calendar year. Please see this KB 850663


Adjusting entries are considered most of the time as entries that allow you to correct errors that were made when transactions were recorded as well they might be utilized to assign revenues or expenses to period or periods in which revenues were earned or expenses incurred.

If you need to in setup or adjust your fiscal periods in GP, please review the section “Setting up / Adjusting Fiscal Periods in GL” at the beginning of this article.


The posting type helps Dynamics GP determine whether an account will be closed to the retained earnings account or if the account balance will be brought forward to the next fiscal year.

Follow these steps to print an account list:

  1. On the Reports menu, point to Financial, and then click Account.
  2. In the Reports list, select All Accounts, and then click New.
  3. In the Option box, type all accounts.
  4. Click to select the Inactive Accounts check box. (if you want to delete inactive accounts)
  5. Click Destination to specify a report destination, and then click OK.
  6. Click Print.

Use the fiscal periods setup window to close all fiscal periods open for the 2012 year. This will prevent transactions from posting to the wrong period or the wrong year.

NOTE: If you still use Microsoft FRx, keep one period in the most recent historical year open to prevent the error: “FRX Print Engine Failed to Load the Company Calendar” see this KB 874932


Run the check links procedure on the financial series group of modules.

Make sure that you always have a backup and that you can confirm you can restore from it.


If you are like me and always want to keep historical records, you must enable the checkbox next to Accounts and Transactions in the Maintain History area of the General Ledger Setup Window.

NOTE: If for some reason you have the checkbox enabled for “Close to Divisional Segments” and you are no longer closing to Divisional Segments or by mistake someone else enabled it and not using it pay attention:

I have discovered in the past a BUG in dynamics GP that is still there as I reported in this blog in August 14, 2012, if you would like to read more about this important step please click here: YEAR END CLOSE BUG 64711


Make sure all users are out of the system

Remove stranded user sessions, you can follow this post Removing Stranded Sessions

Backup DYNAMICS database

Backup all company databases if you are unsure which databases to backup you can query the company master table (SY01500) and retrieve the names, the following script can help you determine that information.

select INTERID, CMPNYNAM from dynamics..sy01500

Make sure to backup as well the Dynamics GP code folder


Use the Trial Balance report window to print a year end detailed trial balance report. Even when this step is optional it is highly recommended to be followed.


Print any year end financial statements that are required. The most common are:

  • Balance Sheet
  • Profit and Loss Statement
  • Statement of Cash Flows
  • Statement of Retained Earnings

Before you can perform the year end closing routine, you must setup a new fiscal year, please follow the instructions on the section at the top of this article to Setup or Adjust Fiscal Periods.

  • Click on the Microsoft Dynamics GP Menu > Tools > Routines > Financial > Year End Closing.
  • If its not specified, you can key in the Retained Earnings Account.
  • Optional: Specify the Starting Journal Entry
  • Click on Close Year

Make another backup of the Dynamics DB and all the company Databases.


Thank you for reading I would like to mention that this post enhances Microsoft KB 888003 


Until my next post

Francisco G. Hillyer

Dynamics GP Payroll Processing – A Deceptively Simple Example

The Dynamics GP Payroll module is a complete payroll processing module that allows you to maintain employee payroll information and process periodic payrolls for salary and hourly employees, with benefits and deductions, and tax calculations. We have many clients that run their payroll in Dynamics GP; ranging from 20 employees, to over 2,000.

I am not a payroll expert, so what follows is a quick overview of processing a salary payroll in Dynamics GP, where I deftly avoid the complicated parts and make it look like anybody can run payroll. The fact is that it takes a fair amount of Dynamics GP knowledge and a lot of non-system payroll knowledge to accurately run a successful payroll using Dynamics GP. We process our own payroll on Dynamics GP, but they don’t let me touch it.

These are the three basic steps:

  1. Build Payroll Checks
  2. Calculate Payroll Checks
  3. Print Payroll Checks

These are the reports that print out. The system steps you through the process:

Here is a quick video that goes through the Dynamics GP Payroll Processing process:

Payroll Garnishments – Tax Levies

Over the years, you learn where to obtain good, useful information.  You also learn who provides consistent, accurate and helpful commentaries on difficult subjects.  Terry Heley, Escalation Engineer, at Microsoft is one of those people.  Terry has been managing Microsoft Dynamics GP payroll code and tax updates for a long time.  She is also the most knowledgeable person I know of when it comes to Microsoft Dynamics GP payroll issues.  I recently had a difficult garnishment setup situation for one of our customers.  After a considerable amount of frustration and trial and error, I reached out to Terry for any insight she may have on our garnishment problem.   She provided me with a set of examples outlining garnishment setups for tax levies, child support, creditor garnishments, garnishments taken before child support and multiple garnishments with previous marked.  As they say, a picture is worth a thousand words.  Well, when it comes to garnishments, an example is even better than a picture.  Below is the first of a series I will be posting about setting up garnishments in Microsoft Dynamics GP.  Thank you Terry.

Garnishments-Tax Levies:

A federal tax levy is accomplished by ‘garnishing’ an employee’s wages to the extent that they are not exempt from the levy.  For example, there may be a tax levy that is for $25,000.  The tax levy deduction will be taken from the employees ‘take-home pay’ until it reaches the exempt amount.

The exempt amount is an amount that comes from the table (after looking at form 668-W that the employee fills out – (according to the number of exemptions, the pay period, and the filing status)).

The ‘take-home pay’ will be calculated as Wages, minus all Taxes and Deductions (both voluntary and involuntary) that were in effect at the time of receiving the tax levy.  Once an employee’s take-home pay has been determined, all but the exempt amount is subject to the levy.

Any new payroll deductions that are initiated by the employee after the levy has been received by the employer must be deducted from the exempt amount when determining the employee’s net pay, unless they are required as a condition of employment.  (This also includes increases in elective deductions such as a 401k.)

It looks like Tax Levies are always an amount (by looking at the form 668-W).

Ex:  Employee Arthur receives $1,211.54 every two weeks.  On Aug 1, 2010, the employer receives form 668-W stating that a federal tax levy was being issued against Arthur’s wages for $25,000.  Arthur claimed married filing jointly with 3 personal exemptions on Part 3 of the form.  (The exempt amount taken from the table is $657.69.)  As of Aug 1, Arthur had the following deductions:

Federal income tax $44.44
Social security tax 50.88
Medicare tax 17.57
State income taxes 30.00
401(k) plan (3% of salary) 36.35
Health insurance (after tax) 45.00
Total: $224.24

Prior to the Tax Levy, Arthur’s take-home pay is $987.30 ($1,211.54 – $224.24).  The exempt amount of Arthur’s take-home pay (taken from the table) is $657.69.  Therefore, the amount subject to the tax levy is $329.61 ($987.30 – $657.69).  And the take home pay after the Tax Levy is $657.69.

How would we set up this deduction?

In Employee Deduction Maintenance
Deduction Type: Garnishment
Original Amount $25,000
Method: Fixed Amount
Garnishment Category: Tax Levy
Amount $25,000
Percent N/A
Earnings N/A
Maximum Deduction Codes
Federal FEDLEVY (this is just an example)
State N/A

In Garnishment Maximum Setup
State/Fed FED
Method Percent of Earnings
Max Withholding Percent 100%
Max Exempt Amount $657.69 (This is the amount taken from the table)
Min Wage Rule Amount $0
Earnings Code FEDLEVY

In Earnings Setup
Include in Earnings
Pay Codes All
Deductions 401(k) & Health Insurance (According to the info we have about Federal Tax Levies, it should be all deductions that are being taken at the time the tax levy was issued.  New deductions after the tax levy is in place would not be included.)
Taxes All Checkboxes Marked

Pay code

Other Deductions

Calculate Checks report


Prior to the Tax Levy, Arthur’s take-home pay is $987.30 ($1,211.54 – $224.24).  The exempt amount of Arthur’s take-home pay (taken from the table) is $657.69.  Therefore, the amount subject to the tax levy is $329.61 ($987.30 – $657.69).  And the take home pay after the Tax Levy is $657.69.

U.S. Payroll – March Round 4 Tax Update for GP 2010 including 941 2011 format….released!

Its finally here, the long waited update from Dynamics GP team for payroll has been released.


Here is the link to the direct download:

U.S. Payroll Tax Update for Microsoft Dynamics GP 2010 (Round 4)
This page contains the latest U.S. Payroll Tax Update for Microsoft Dynamics GP 2010.


Here are the updated version numbers

Microsoft Dynamics GP


Project Accounting




Human Resources


Field Service


Grant Management


Dynamics Online Services


The automatic tax update site is available.


If you have employees paid in the state of Oregon, you need to install the tax update and the code update…major changes in their tax structure…there is also a new filing status for Oregon, so read the instructions if you are in Oregon carefully, please.

Thanks THeley…

Employee FICA Social Security Dedution Change

You should be aware by now that employees will have FICA Social Security withheld at a 4.2% rate in 2011 rather than the 6.2% rate used in 2010.  However, the employer still has to pay FICA Social Security at the 6.2% rate.  Under current Great Plains tax code calculations, this will result in incorrect amounts indicated on the Check Register for Employer Owed FICA ( see screen shots below) until the Service Pack/Tax Code update is released by Microsoft (currently scheduled for mid January 2011).   Any payroll run in Great Plains with a 2011 payroll check/direct deposit date run prior to the mid January Service Pack/Tax Code update must make corrections for the federal taxes submitted to the IRS and also make corrections to the general ledger posting journals from payroll.  You should also assume that the quarterly 941 and the year end employer tax reconciliation may also require adjustments for this issue. 

The screen shots below are taken from Fabrikam for one employee.  The tax schedule used is dated 12/22/2010 (Round 1 2011)  which includes the 4.2% withholding for Employee FICA Social Security.  The amount withheld from Pilar Ackerman is $52.96 ($39.37 + $13.59).   The Employer Owed FICA on the Check Register is also listed as $52.96.  The correct Employer Owed FICA is actually $71.72 ($58.13 + $13.59).  Using the Employer Owed FICA from the Check Register will result in under payment of FICA taxes due by the employer of $18.76 ($71.72 – $52.96).    The general ledger posting journals for payroll will also be incorrect and musts be adjusted as well. 

Do not install the Round 1 2011 tax table update in your environment until all payrolls have been prepared with payroll check dates in 2010 and your Year End Wage File has been created.  I can install the Round 1 tax table update in my environment as I am not preparing any actual payrolls.  When the Service Pack/Tax Code update is released in mid January, Microsoft will undoubtedly have additional updates and/or information available at that time.

It is critically important that the individuals responsible for transmitting taxes due to the IRS understand and are familiar with this issue.  Please share this information with those individuals in your organization that are responsible for payroll.   Microsoft will be updating their website with pertinent information, and I would recommend that all interested persons check with Customer Source at the Microsoft website.

Should you backup your database before processing payroll?

A client called yesterday and were in a bit of a tizzy. They processed payroll last month and in the middle of things GP crashed. They said a few cuss words, logged back into GP then continued processing. When they went to do their period end reports they were a little taken back because gross wages was off by $13,000,000. I had to write that out just to see how many zero’s that is.
A few questions came out of the CFO’s mouth such as:

  • How could this be?
  • Did they really send a check out to an employee for that amount?
  • Is our bank reconciliation off?
  • Is our GL amounts off?

We discovered an employee with the below information:

My first question was – “Are you sure this employee is still in the country because if I received a check like that, I’m heading straight to Samoa for an indefinite period of time.”

They chuckled a bit but I don’t think they found my comments to helpful.

The payrun that caused this was of course two weeks ago so restoring from a backup was not really an option. This made me pull out my SQL skills and go to work. I used tech doc 948268 to resolve the issue but here are the basic steps:

  1. Backup Company databases
  2. Make sure you backed up the correct company database
  3. Delete the payrun in question from the following tables using these scripts. Replace XX with the correct Audit trail code. delete UPR30100 where AUCTRLCD=’XX’ delete UPR30200 where AUCTRLCD=’XX’ delete UPR30300 where AUCTRLCD=’XX’ delete UPR30400 where AUCTRLCD=’XX’ delete UPR30401 where AUCTRLCD=’XX’
  4. Delete the employee’s summary information by using this script, replacing YY with the employee ID and ZZZZ with the year in question. delete UPR30301 where EMPLOYID = ‘YY’ and YEAR1 = ‘ZZZZ’
  5. Run Reconcile in payroll – Point to Tools on the Microsoft Dynamics GP menu, point to Utilities, point to Payroll, and then click Reconcile.
  6. Deal with bank reconciliation module and GL as necessary

I ended up deleting one table that had 450,000 rows duplicated for one transaction. Thus the 13 million dollar employee card.

Now the real question is should a backup be done before processing a payroll batch? The answer is a resounding YYYYYYYEEEEEEESSSSSSS!!!!! Does that mean everytime? Let me be clear. Everytime you process payroll you are at risk of having your payroll module blow up. The above was a fairly easy fix compared to what can happen. I wish I could be more upbeat about how great GP is and how solid payroll is as a module.

Another thought. We have a couple of clients that all they do is payroll. They process thousands of transactions a week and in any given day they have several people posting payroll batches. To back up their database takes 10 to 20 mins. To do a backup each time is not really feasible. So after explaining the situation clearly they have decided to risk a days worth of work for irregular event of having the payroll module messed up with a posting interruption.

Talking with Mike from our office about this event and he told me how he fired someone when he was a controller when they processed payroll without doing a backup. The one and only time they didn’t have a backup resulted in heartache, gnashing of teeth, Mike flying off the top ropes with a 10 key….you get the drift.

Here is a link to backing up your GP system.

Please consider making a backup before processing your payroll. One time in a thousand you’ll actually need it but you’ll thank your lucky stars when you do.

It Pays to Know a SQL Expert, Especially if you are not!

As consultants, we all have aspects of our work that are more frustrating than others. Migration of data is one of those tasks that I prefer to do alone…I tend to swear a lot and it isn’t pretty! Last week I had a payroll migration that was one of the most hairy to date and I have been doing this for years.

It started out very vanilla. After all, it doesn’t matter the quantity of data…it is all about the quality, right? I only needed current year employee payroll transactions. My methodology is very tried and true. I Download the data into Excel, clean it up, save it, map it to Integration Manager and after a few hit and miss tries…the data has been successfully integrated. I can move on to something more exciting.

My client runs about 2,500 payroll checks a week and there is a massive amount of employee turnover. In the legacy system (which is housed in a separate facility from the new implementation) employees were not inactivated and, after several years, there were literally hundreds of thousands of employee master records.

As in Dynamics GP, the data that I needed was in several different tables, and had to be exported. No problem I think, and dump out the historical payroll transactions via SQL table export. I will do the same thing with the entire employee master record table, Do a VLook-up to parse out the employees that haven’t been paid this year…and there you go… I have what I need to import into my new GP database.

Who knew there was a limit to how much data Excel could handle. Yes, I know, there is a max. amount of rows – and Excel 2007 can hold a lot! Apparently, not enough for my employee master and state tax file. After several attempts (and a lot of swearing) I have the data in separate workbooks. Now to get rid of the employees I do not need. Not so easy. The calculation speed was painfully slow and while I could import data to all 1M rows, Excel couldn’t run the formula due to memory constraints (so I am told.) 12 hours later I am completely frustrated and at my witts end.

This is where the story gets good. My fairy godmother (a.k.a. my boss) tells me to quit pulling my hair out and call Tom Celvi. He is a SQL wizard! I am a bean-counter and application consultant. I have always thought of SQL as something to be respected and a bit feared. After all, it is the “house” for my GP data.

Silly me! There is a whole other world out there that I owe myself to learn more about. Tom has shown me that SQL is a very powerful tool and can be used for other things besides housing my precious GP databases.

Tom created a database within the clients SQL 2008 environment, imported all my raw, ugly data and through a series events that I can only explain as pure alchemy…he managed to move that cleaned up data into my GP database and I had my data in all the correct payroll tables along with only the 12,000 current employees!

This took Tom 10 hours in total and I bet he didn’t swear once! I was then able to successfully integrate the current year payroll transactions via Integration Manager and my my client is live and processing payroll.

Under lessons learned:

  1. SQL doesn’t have to be feared, it can be your friend (but should be respected)
  2. Quality over Quantity is over rated – strive for both because Excel does have its limits.
  3. hang out with a SQL guru, it will save on hair dye

2008 Payroll year end close update Dynamics 10.0

Bear with me a moment while I grumble and I’ll get to YEC for 10.0.

Great Plains isn’t what it used to be. That’s both good and bad I suppose.

It is good that all workstations should be on the same SP version to be able to log into a company. This saves many significant issues popping up as most are well aware. Workstations used to always be out of sync and a lot of the time the resolution would be “Apply this patch like all the rest of the workstations. etc.”

However it is frustrating when you only need to apply the year end update for payroll. In times past you could put the cnk file only on the payroll persons workstation and they’d be good to go. It’s a major effort now to apply any service pack. It almost feels like people are being pushed into a Citrix/Hosted or Terminal Server model. There are major advantages of only having to apply SP’s once at a central location. I know you can have the SP automatically apply the next time a user logs into the system but that is mostly just a huge pain none the less.

Ok, 10.0 year end update. Here are a few things I’ve come across while going through the year end update. Most are pretty user specific but here they are none the less.

  1. All people must be out of the system. Sounds like a no brainer but we spent several mins troubleshooting why the system tables wouldn’t update then found some people had snuck into the system. (Hopefully Mike’s head doesn’t hurt too bad from hitting it repeatedly on the desk in front of him.)
  2. To install the update make sure the installation of GP is at least the first recommended release of 10.0. Had one client on 10.0.465 which is a beta release of 10.0. Who knows how they got that one installed. Kept getting this erorr when trying to apply the update. Applied 10.0 Feature pack 1 which includes SP3 (1.5 gb download so don’t do this with a 56k modem), applied the update and they were good to go.
  3. You can run the update from a client workstation. Don’t need to be at the server. Kind of counter intuitive but it works. After corrupting GP on the server during resolution of #1 we got the update to go on a client workstation. (Thanks to Jamie Nelson on this one).
  4. Plan for several minutes per database. I’ve timed several updates and it seems like it runs around the 20 min range per database. This is true when the databases are pre SP3. Not sure if it’s quicker if SP3 is already applied. (Anyone know this?)
  5. You must run the year end wage file before applying 2009 tax tables. Duh.
  6. You can’t run a 2009 payroll before running the year end wage file. Duh again. ha.
  7. User gets kicked out of GP when running payroll reports after applying update. Recreated reports.dic as it was corrupted in the process. So….make sure you backup reports.dic, forms.dic, dynamics and company databases etc. The process looks like it actually exports then imports the reports as part of the update so that could easily cause issues.

Here’s a few other Year end update comments from Christina Phillips on her blog.

Victoria Yudin still has a great post on Year end close resources.

2009 payroll tax update for Dynamics GP 8.0

For those slackers still on Dynamics Great Plains 8.0 and are using payroll here is a procedure to apply the 2009 tax tables before you run your first payroll of the year. You probably don’t need to worry about the tax update for 2008 as no form changes have been made. You should do the payroll year end procedure (tech doc # 850663) as usual but when it comes to applying the 2009 tax update you will have to do something like this.

Here is the 2009 payroll tax update script. It is for 10.0 but I tested on my 9.0 system and it completed successfully. I would recommend this procedure for applying the tax tables for 8.0:

1. Verify current tax numbers that are currently setup in payroll tax tables. Tools>>setup>>system>>payroll tax. Also note what date is listed for Last Tax Update (use in step 4)
2. Back up the Dynamics and company database
3. Run the script in SQL
4. Look at same tables as step 1 and verify changes have been made. Also verify last tax update is now listed as 12/19/2008
5. Run a payroll in a test company to verify the numbers are correct
6. Run payroll in production company and verify numbers are correct

Hope that helps you remaining 8.0 folks. Need to consider upgrading soon.

Subscription Options:
Subscribe via RSS
Articles Categories