Changing SOP Tax Rates using the Support Debugging Tool
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.