Friday, January 4, 2013

How to delete a fiscal year that was incorrectly created

Well is that time of the year again...

Most of you are in the midst of closing 2012 and getting Microsoft Dynamics GP configured for fiscal year 2013. If you are in this process of closing 2012, make sure to check out the Year-End closing procedures series over at the Microsoft Dynamics GP Support and Services team blog:

Microsoft Dynamics GP Year-End Closing Blog Series 2012 Schedule!!

The past few days however, I have had to address a number of calls asking for a little help deleting fiscal years that have been setup incorrectly. The following screenshot illustrates one of such issues:

Fiscal Periods Setup window
For the most part, users enter the correct start and end dates for the period, but mistype the fiscal year, which can cause a lot of headaches since there's no Delete button to remediate the situation. In addition, if the year is changed to 2013, the system will not allow you to create a new fiscal year with the start and end dates of a previously setup fiscal year.

So with no Delete button, where do you go?

Well, you can always resort to a little T-SQL to fix the problem by running the following script against the company database:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
DELETE FROM SY40100 WHERE YEAR1 = 2103;
DELETE FROM SY40101 WHERE YEAR1 = 2103;
DELETE FROM SY40102 WHERE YEAR1 = 2103;
DELETE FROM SY40103 WHERE YEAR1 = 2103;

NOTE: in the above example the fiscal periods were properly setup to reflect 2013 start and end dates, but the year was incorrectly entered as 2103 - fat fingered if you will.

As is customary, please run this and any script against your test environment first to determine if you achieved the expected results. Backup your production databases environment prior to executing.

Once you have executed the script, please proceed to recreate the fiscal year and fiscal periods.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

2 comments:

Mike Lupro said...

A further post would be interesting after someone has created an incorrect fiscal year (with the correct start/end dates as shown) but has also closed the current fiscal year into the incorrect fiscal year.

Mike Lupro said...

In a later post it would be interesting to show how to handle an incorrect fiscal year after the user has closed the current fiscal year into the new/incorrect fiscal period.