Back on the 1st July 2000, Australia changed its sales tax system from a wholesale sales tax with various rates (depending on the item) to a Goods and Services Tax (GST) with a fixed 10% rate.
To go with the change in the tax system, the Australian Taxation Office (ATO) also brought in a new tax return reporting system known as the Business Activity Statement.
This was back in the days of Great Plains eEnterprise version 5.50 when I was running my old Winthrop Dexterity Consultants business. As there were not enough development resources available in Fargo to complete all the changes we needed in a time frame to allow testing and deployment before July 2000, I ended up working as a contract developer to create the Business Activity Statement (BAS) and Pay As You Go (PAYG) Withholding reporting tool as well as "Line Level" taxes in the Purchase Order Processing module.
Why the history lesson?
Well, as part of the development of the BAS & PAYG reporting tool, I also created a couple of utility scripts to help move un-posted Sales Order Processing (SOP) transactions to the new tax model and recalculate the line level taxes and document totals. These scripts were to be used on the evening before the new tax system came into effect to update the sales transactions that have not been posted.
Recently, I was asked to assist with the tax changes coming into effect in New Zealand on the 1st October 2010. Their GST rate is changing from 12.5% to 15%. To help with this taxation change I used the Runtime Execute window in the Support Debugging Tool to provide similar scripts in a format that can be easily loaded and executed on any Dynamics GP system.
There are two scripts provided in the Support Debugging Tool settings file attached to this article:
After the close of business on the day before the tax changes come into effect, make sure that all SOP transactions that need to stay on the old tax rates are posted. Then you can use one of the following three approaches to change your tax setup:
Once you have updated the tax setup, new transactions will use the new rates.
Now you are ready to run the scripts to update the un-posted SOP transactions. You will need to download the Support Debugging Tool for your version of Microsoft Dynamics GP. The links to download the Support Debugging Tool for the various versions are located on the portal page:
These scripts assume that ALL un-posted SOP transactions need the tax recalculated and that the tax rate changes to Tax Schedules and Tax Details have already been made. Also, make sure you have a backup of the data before running the scripts.
All Done. The un-posted SOP Transactions will now have tax reflecting the new rates and all totals & distributions will have been updated to match. You will need to repeat this for other companies in the system as required.
This information is doubly important now as Andrew from Touchstone also mentions that the United Kingdom (UK) will be changing its VAT rates from 17.5% to 20% on the 1st January 2011. So these scripts should be able to assist our colleagues in the UK as well. See Andrew's post: All Change on 20% Tax Again January 2011.
Note: The Support Debugging Tool settings file with the two scripts is available as an attachment at the bottom of this post.
For more information on the Support Debugging Tool, please visit the Support Debugging Tool Portal page.
17-Jan-2012: Fixed minor bug in the SOP_UPDATE script which will cause slow performance. Thanks to Stephen for pointing it out.
One of the problem we encountered when implementing taxes on SOP is the rounding differences. SOP calculates taxes by line items but when you multiply the Sub-Total x Tax rate, the figures can differ with the taxes calculated on line level by 1-2 cents.
What we did was, we added a VB code to check the calculation and if the figures doesn't tally, it will prompt a message for user to change the tax amount.
Any better solution?
Tax calculations in Dynamics GP are line level and not document level. The tax for each line needs to be a whole number of cents. This is very likely to give a rounding difference at the document total level.
Please note that there is no guarantee that the same tax details or rates have been used for all the lines on a transaction. If different tax details or rates have been used, looking at tax at the document level is not really valid.
Most national tax authorities will accept tax calculated at the line level and then summed as almost all computer based accounting systems work this way.
If you really must have the tax adjusted, then using VBA, VSTools or Dexterity to handle some adjusting of the figures before saving can be a solution.
In your case, you are asking the user to make the change. If you want to improve on this, why not use VBA to automate the change that needs to be made, so that user interaction is not required.
This is going to be very handy in the UK we have a tax change comming on the 4th of January 2011 to 20%.
Any one out there who wants more info on the UK changes you can get a fact sheet from HMRC at: www.hmrc.gov.uk/.../rate-rise-guidance.pdf
Thanks for this functionality David
Posting by Mark Polino from DynamicAccounting.net
The method is great and I use it when the Irish Vat rates change. One small bug is in the SOP_UPDATE where it sets the SOP_Number and SOP_Type it is setting them from the SOP_LINE_WORK it should be from the SOP_HDR_WORK. The only consiquence is that it takes longer than it should because it is reading the full SOP_LINE_WORK for every SOP_HDR. In my case it took 5 hours in stead of 20 mins.
Well spotted. Yes, the SOP_UPDATE script was using the wrong table for the 'SOP Type' and 'SOP Number' fields when setting the range. The dangers of cut and paste.....
I have updated the script and re-attached it to this post.
I attended one of your Convergence sessions in 2012. Clark Patterson also say hi! We're trying to use the SOP_RECALC to correct Sales Tax on the Header to match sales tax that's passed down from our website. It doesn't appear to be working. Any suggestions?
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.