Unable to create a new Fiscal Year ??

This week I was working on a client’s site that was proceeding with some GP Data Archiving by using the ProfAd Company Data Archiver (CDA). I had created several new companies with GP Utilities to accommodate for all the historical data that was going to be archived (about 6-7 years) for their huge Production Database (over 250GB for just the main company, in total about 300GB). For some this might seem a lot of data, for others this is a drop of water in the Data Ocean (my friend Steven Endow told me about a client with Terra-bytes of GP data!).. Nonetheless, this being on an old GP 2010 release, no longer supported, we had to make some room before upgrading to the latest build.

Needless to say, ProfAd still has a version of CDA for GP 2010, but let me tell you that it isn’t as nice as the newer versions. For one, you can’t define a common folder to save all the reports that are generated with each archive process run (up to 21 reports!), which doesn’t make it easy to run. For two, some of the reports run into the thousands of pages, as they list every single transactions for every module that is archived. But that’s another story that I’m going to blog about another day.

The purpose of this article is to fix a problem I ran into after the archiving process.. GP Utilities creates new companies and only puts a current Fiscal Year (FY) in GP with standard 12 periods named Period 1 to 12, going from January 1st to December 31st. What if your company’s FY isn’t following those rules ? Then you’ll have to delete it and start from scratch with the PSTL (Professional Service Tool Library). With the help of PSTL you can reset all your FY’s in one shot (beware that this is wiping out your SY40100 & SY40101 tables in the company DB, so be warned to take a backup before you start).

Once the FY has been wiped out, PSTL opens the standard Fiscal Year Creation window, where you enter the year, the date range and check a box if this is for a historical year. Then you’re supposed to click on the ‘Calculate’ button on the menu bar to generate the periods. And here is where I ran into a problem..

I had wiped out the default FY 2019 that was created by GP Utilities and wanted to create a FY 2014 for this company (hence the name H2014). Unfortunately GP wouldn’t let me do it. No matter how hard I tried, it wouldn’t create the periods.. and worse you’re locked into that window as there is no clear button and no cancel out… basically you have to kill your GP session. I googled the internet of course, but none of related issues I found where the exact same problem, so this was a unique case.

Checking from the SQL back-end, the table SY40101 which holds the header for the FY entries was showing the year 2014, but the table SY40100 with the periods would remain empty.. Thinking it was a leftover entry in the header table, I cleared the entry and tried again.. No luck, still the same issue, and even trying with FY 2019, it would fail miserably.

Then I checked SQL security as the message seemed to indicate there was an issue with ‘inserting’ the records in the period table.. No luck either. That’s when I started looking thru the code of the stored procedure smPeriodSetupCalculate which was the one failing above. The code isn’t very long but have several steps, each having a different return code if something goes wrong. Looking for the code 20303 that was returned in the error message, I found the section of the code that was causing the issue.

The insert statement into the table SY40100 would fail because it couldn’t find any records in the SELECT statement from the table SY01000 !! No idea how this table is related to the FY periods setup, but apparently SY_Transaction_Source_MSTR is needed to feed some of its data to the SY40100 table. Checking the other historical companies I had created, that tables was populated with some data, so it was an easy fix, by just running an INSERT with SELECT statement from H2013 to H2014. Once this was done, I could finally generate my new FY 2014 & 2019 for that company.

In another blog post I’m going to show you how you can quickly rename all your default FY periods in case you have to do a global reset like I had to once to shift 12 years worth of GP data due to a company merger.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.

Up ↑

JenKuntz.ca

Dynamics GP & other thoughts!

SergesPoint

Tricks and trips about SharePoint

Salim Adamon - Dynamics CRM Blog

Everything Dynamics CRM from a technical, architectural and business perspective

The Official Blog for Training Dynamo LLC

Microsoft Dynamics GP - Tips, Tricks, News and Thoughts

Dynamics GP Tips and Traps For Users and Developers

Talking about Great Plains, Dexterity, SQL, and some C#

smay772

A fine WordPress.com site

Tom Roush's Blog

Musings on Life, Lessons, and Laughter...

Belinda Allen, Microsoft MVP Business Solutions

Things I've learned from years of working with Microsoft Dynamics GP.

David Musgrave's Winthrop Development Consultants Blog

This blog will keep you up to date with Winthrop Development Consultants and David’s experiences, tips and tricks as a developer and consultant in the Microsoft Dynamics GP community.

%d bloggers like this: