Last month, Kelly Youells posted on the Dynamics GP Support and Services Blog about an issue which can cause Microsoft Dynamics GP 2010 Utilities to hang when updating to Service Pack 3 or later. The details of the issue are posted on the blog and also linked to the Dynamics GP 2010 Hot Topic:
The quick summary is that Dynamics GP does not allow fiscal/financial periods to overlap any more. If there are overlapping periods, the upgrade process will go into an infinite loop and appear to hang.
Kelly provides the following query to look for overlapping periods:
SELECT distinct a.YEAR1, a.PERIODID, a.PERIODDT, a.PERDENDTFROM SY40100 a JOIN SY40100 b ON a.PERIODID<>b.PERIODID AND a.PERIODDT <= b.PERIODDT AND a.PERDENDT >= b.PERIODDT AND (a.PERIODID <>0 AND b.PERIODID <>0) order by a.YEAR1, a.PERIODID
For example: The query highlighted the 13th period of the 2013 year was overlapping for the 30th June 2013.
2013 12 2013-06-01 00:00:00.000 2013-06-30 00:00:00.0002013 13 2013-06-30 00:00:00.000 2013-06-30 00:00:00.000
She also provides example update statements to adjust the periods, for our example:
update SY40100 set PERDENDT = '2013-06-29 00:00:00' where YEAR1 = 2013 and PERIODID = 12
update SY40100 set PERIODDT = '2013-06-30 00:00:00', PERDENDT = '2013-06-30 00:00:00' where YEAR1 = 2013 and PERIODID = 13
This additional end of year period is quite common and the queries provided in the article will help resolve overlapping periods caused by this situation.
However, I had a recent case where we still had a number of overlapping periods reported by the original query and not enough information to understand why they were being reported. Updating the original query to add the columns from the other half of the query helped us identify what was wrong.
SELECT distinct a.YEAR1, a.PERIODID, a.PERIODDT, a.PERDENDT , b.YEAR1, b.PERIODID, b.PERIODDT, b.PERDENDTFROM SY40100 a JOIN SY40100 b ON a.PERIODID<>b.PERIODID AND a.PERIODDT <= b.PERIODDT AND a.PERDENDT >= b.PERIODDT AND (a.PERIODID <>0 AND b.PERIODID <>0) order by a.YEAR1, a.PERIODID
We identified two situations which were causing overlapping periods to be reported:
The following query detects this incorrect data:
select a.YEAR1, a.PERIODID, a.PERIODDT, a.PERDENDT , b.PERIODDT, b.PERDENDT, b.SERIES, b.ODESCTNFROM SY40100 a JOIN SY40100 b ON a.YEAR1 = b.YEAR1 AND a.PERIODID = b.PERIODID AND a.SERIES = 0where a.PERIODDT <> b.PERIODDT or a.PERDENDT <> b.PERDENDT or b.PERIODDT = '1900-01-01 00:00:00' or b.PERDENDT = '1900-01-01 00:00:00'order by a.YEAR1, a.PERIODID, b.SERIES, b.ODESCTN
If the above query returns results, you can fix the data using the following update statement:
update b set b.PERIODDT = a.PERIODDT, b.PERDENDT = a.PERDENDTFROM SY40100 a JOIN SY40100 b ON a.YEAR1 = b.YEAR1 AND a.PERIODID = b.PERIODID AND a.SERIES = 0where a.PERIODDT <> b.PERIODDT or a.PERDENDT <> b.PERDENDT or b.PERIODDT = '1900-01-01 00:00:00' or b.PERDENDT = '1900-01-01 00:00:00'
After running the above update statement we still had overlapping periods reported. Further research identified data in the period table when there was no matching year set up. The following query will detect the orphaned data.
select a.YEAR1, a.PERIODID, a.PERIODDT, a.PERDENDT, a.SERIES, a.ODESCTN , b.YEAR1FROM SY40100 a LEFT OUTER JOIN SY40101 bON a.YEAR1 = b.YEAR1 where b.YEAR1 IS NULL
If the above query returns results, you can remove the orphaned records using the following delete statement:
delete aFROM SY40100 a LEFT OUTER JOIN SY40101 bON a.YEAR1 = b.YEAR1 where b.YEAR1 IS NULL
After we fixed the incorrect or missing dates and removed the orphaned records, the original query provided by Kelly no longer reported any overlapping periods. We repeated this process for each of the companies.
We were then able to proceed with the upgrades of all of the companies without further hanging issues.
Please don't forget the final step: After the upgrade has completed, you must reconcile the General Ledger Years to ensure that transactions show correctly in the now modified fiscal/financial periods.
Hope this additional information was helpful.
This is a highly contentious topic amongst my customers, and whilst I have seen plenty of technical guidance (such as this) that tells you how to work around the problem, I have yet to see anything that addresses the accounting issues - i.e.:
1. that if you run reconcile after changing the financial periods in this way, genuine (non-adjusting) transactions that are currently reported as period 12 will be moved into period 13 - so prior year reports will not be reproducable in FRx. This can be avoided by a data-cleansing exercise of course, but this will be somewhat labour-intensive as all prior years are affected - a big deal for long term customers.
2. that transactions from feeder systems brought in through eConnect will have to be changed to back date transactions for 31st March by one day to avoid them posting to P13 in future - possibly involving a software change, possibly involving a workaround in the relevant Pre/Post eConnect SPs.
The most confusing aspect of all of this is that the GP 2010 manual clearly says that overlapping periods are okay - by the implication that the maximum number of periods allowed in a financial year is 367.
Nice article. I would like to point out this was a labor intensive case, finding and fixing these overlapping periods. Granted some of that time was spent discovering the other overlapping periods that you list in this article. I also felt that a lot of time was spent fixing the upgrade tables that were put in an inconsistent state when the database "hung" during the upgrade. Now with the power of hindsight on our side, do you think it would been easier and possibly quicker to roll back the upgrades and fix these issues on the original databases?
In summary fixing this issue was heavy lifting and I recommend you get Microsoft involved unless you are extremely comfortable working with the GP tables.
Sorry if this is a duplicate post. I was having issues posting.
Posting from Mark Polino at DynamicAccounting.net
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.