***never use any script found on this site without testing. All scripts are provided as-is, and without warranty of any kind.
I currently manage 54 production GP databases, and if I have to do something manually more than once or twice, I figure there has to be a way to automate it.
Our fiscal periods are plain vanilla - 12 monthly periods beginning on the first day of the month, however our GP users are all over the globe, so new periods must be opened automatically to 'reduce the noise'.
Attached are 3 scripts:
- usp_OpenCurrentPeriod - opens all 6 standard GP modules (modify as needed - notes are in the script) for the current calendar month. I created a SQL job that runs a few minutes after midnight UTC time on the first of the month to handle this for many of our companies;
- usp_OpenCurrentPeriodGLOnly - opens only the GP GL module (again - modify as needed - notes are in the script) for the current calendar month. This script is used for companies that only handle consolidation or other GL entries - this is also part of the SQL job noted above;
- usp_ClosePreviousPeriod - CLOSES all 6 standard GP modules (modify as needed - notes are in the script) for the previous calendar month. I do NOT use a SQL job for this as not all GP companies close at the same time. As written the script looks for the start date of the last calendar month and closes that period - so if I ran it today, it would close all modules for the period that started 1 Jan 2021.
A few other comments:
In my WHERE clause I am converting GETDATE() to a date format otherwise GP won't recognize it (you can also use a varchar(10)).
The table I'm updating is [companydb].dbo.SY40100 - this contains a combination of 'header' records (one for each period) WHERE SERIES = 0. I'm setting the PSERIES_1 through PSERIES_6 to 0 to open the period for each module, and to close the period I set it to a 1.
Because I'm manually setting the header record, I also set the underlying 'detail' records, which represent the items you see in the 'Mass Close' window from the Fiscal Periods window. In 25 years working with GP I've never seen anyone use this window, but we should still clean up after ourselves... These series have a different value that the PSERIES!
-- Header records (These are columns)
PSERIES_1 = Financial
PSERIES_2 = Sales
PSERIES_3 = Purchasing
PSERIES_4 = Inventory
PSERIES_5 = Payroll
PSERIES_6 = Project
-- SERIES values - for open/close by window
(There is no series 1)
Series - 2 = Financial
Series - 3 = Sales
Series - 4 = Purchasing
Series - 5 = Inventory
Series - 6 = Payroll
No Project windows available in this column
I am using this in two different environments (GP 2016 R2 and GP 2013) as we still have one company to migrate to GP 2016. In a couple of months we'll start testing the current version of GP for a late summer upgrade...
Lastly, there are plenty of ways to calculate dates in SQL - hopefully your period begin dates are either static like a constant day of the month.
Please please please test this this for your environment!