This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
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:
- SOP_UPDATE: This script will update the Tax Schedule IDs on the transaction header and lines from the Tax Schedule information on the Customer/Debtor Master and Item Master and SOP Setup (for non-IV items). Use of this script is optional and does not update any numeric values.
- SOP_RECALC: This script will re-calculate the transaction tax based on the Tax Schedules stored on the header and lines. The Tax Details will be updated and the Tax Amounts will be changed and the document totals updated accordingly. Note: These scripts are provided AS IS and are not supported. Please make sure you have a full backup before using the scripts.
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:
- The simplest is just to update the Tax Rate for each of the relevant Tax Detail IDs using the Tax Detail Maintenance window. You will only need to execute the SOP_RECALC script for this approach. or;
- You can also create new Tax Details with the new Tax Rates and update the existing Tax Schedule IDs to use the new Tax Details rather than the old ones. You will only need to execute the SOP_RECALC script for this approach. or;
- You can create new Tax Details and new Tax Schedules for the new Tax Rates. You will then need to update the customer/debtor master and item master records to set the new Tax Schedules as defaults. You will need to execute the SOP_UPDATE script and then the SOP_RECALC script for this approach.
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.
- Once you have the appropriate archive downloaded, extract the contents to the GP application folder and launch GP (using Run as Administrator if User Access Control (UAC) is active on Windows Vista, Windows 7 or Windows Server 2008).
- You should be asked to include new code. Select Yes. Then login to GP as a user with SysAdmin or DBOwner permissions, usually ‘sa’.
- Once logged in, from the menus select Microsoft Dynamics GP >> Tools >> Support Debugging Tool
- To enable Advanced Mode: From the Support Debugging Tool main window, click Options >> Dex.in Settings. Check the “Enable Debugger Advanced Mode Features” checkbox. Click OK. (This step only needs to be performed once per system).
- From the Support Debugging Tool main window, click Options >> Configuration Export/Import. Use the path button to select the settings file (download from the bottom of this post and extract to the desktop) and click Import. Click OK on the Import Settings File. If warned about overwriting click Yes to continue. Click OK on the final Dialog. Click OK to close the Configuration Export/Import window. (This step only needs to be performed once per system).
- From the Support Debugging Tool main window, click Options >> Runtime Execute. Use the lookup or browse buttons to load the SOP_UPDATE script and/or SOP_RECALC script as desired. Click Execute to run the script. Select Yes to confirmation warning. The transactions will be updated. Click OK to final dialog. When finished running scripts, close Runtime Execute Window
- Close Support Debugging Tool window.
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.
Debugger Settings Tax Recalc.dbg.zip
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.
8 thoughts on “Changing SOP Tax Rates using the Support Debugging Tool”
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: http://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?