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.