/****** Object: StoredProcedure [dbo].[usp_ClosePreviousPeriod] Script Date: 2/23/2021 6:45:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
---------------------------------------------------------------------------------
COPYRIGHT (C) 2019 Jeffrey Pfershy
This script is provided as-is without warranty, express or implied.
As with any code, you are responsible for testing this before using it
in a production environment.
This procedure has not been tested with any third party products such as
Binary Stream Multi-Entity Management (MEM) or others.
If you have questions I can be reached at jpfershy@hotmail.com
---------------------------------------------------------------------------------
usp_ClosePreviousPeriod STORED PROCEDURE CREATION SCRIPT
---------------------------------------------------------------------------------
Revision History
Date Who Description
----------- ---- --------------------------------------------------------
12 Jun 2019 JAP Initial creator
---------------------------------------------------------------------------------
Stored Procedure Name: usp_ClosePreviousPeriod
Purpose: To be used to close (mark closed) the previous fiscal period
for all modules in a GP database (company/entity).
Use: This stored proc was written to CLOSE ALL GP modules for the prior fiscal period,
which in our environment is the FIRST DAY OF THE MONTH.
**YOU WILL NEED TO MODIFY THIS CODE IF YOUR FISCAL PERIODS DO NOT BEGIN ON THE FIRST DAY OF THE MONTH**
**The field PERIODDT is the Fiscal Period START Date**
I run this manually as needed once finance notifies me which companies can be closed.
Create this stored procedure in every GP database you want to use it in,
and add other logic as needed to match your fiscal periods.
PSERIES_1 = Financial
PSERIES_2 = Sales
PSERIES_3 = Purchasing
PSERIES_4 = Inventory
PSERIES_5 = Payroll
PSERIES_6 = Project
-- Series - for open/close by window
Series - 2 = Financial
Series - 3 = Sales
Series - 4 = Purchasing
Series - 5 = Inventory
Series - 6 = Payroll
No Project windows available in this column
---------------------------------------------------------------------------------
EXEC dbo.usp_ClosePreviousPeriod
*/
ALTER PROCEDURE [dbo].[usp_ClosePreviousPeriod]
AS
SET NOCOUNT ON
BEGIN TRAN
UPDATE SY40100
SET PSERIES_1 = 1, PSERIES_2 = 1, PSERIES_3 = 1, PSERIES_4 = 1, PSERIES_5 = 1, PSERIES_6 = 1
WHERE PERIODDT = CONVERT(date,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())-1,0),120)
AND SERIES = 0
UPDATE SY40100
SET CLOSED = 1
WHERE PERIODDT = CONVERT(date,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())-1,0),120)
AND SERIES IN (2,3,4,5,6)
COMMIT TRAN;
/****** Object: StoredProcedure [dbo].[usp_OpenCurrentPeriodGLOnly] Script Date: 9/26/2020 3:18:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
---------------------------------------------------------------------------------
COPYRIGHT (C) 2020 Jeffrey Pfershy
This script is provided as-is without warranty, express or implied.
As with any code, you are responsible for testing this before using it
in a production environment.
This procedure has not been tested with any third party products such as
Binary Stream Multi-Entity Management (MEM) or others.
If you have questions I can be reached at jpfershy@hotmail.com
---------------------------------------------------------------------------------
usp_OpenCurrentPeriodGLOnly STORED PROCEDURE CREATION SCRIPT
---------------------------------------------------------------------------------
Revision History
Date Who Description
----------- ---- --------------------------------------------------------
29 Jan 2020 JAP Initial creator
---------------------------------------------------------------------------------
Stored Procedure Name: usp_OpenCurrentPeriodGLOnly
Purpose: To be used to open (unmark closed) the current fiscal period
for GL only in a GP database (company).
Use: This stored proc was written to open the GL module for the new fiscal period,
which in our environment is the first of the month.
I have a SQL job that runs on the first of every month and executes either
this sp or usp_OpenCurrentPeriod (for all modules),
depending on if my GP company is a 'GL only' company or not.
If your fiscal periods fall on other days use the lines that are commented
out below instead
Create this stored procedure in every GP database you want to use it in,
then set up a SQL job to run it either on the day of the month that your new
fiscal period starts, or add other logic to run it daily and if that date
is the start of the new fiscal period, run the update statement.
**YOU WILL NEED TO MODIFY THIS CODE IF YOUR FISCAL PERIODS DO NOT BEGIN ON THE FIRST DAY OF THE MONTH**
**The field PERIODDT is the Fiscal Period START Date**
PSERIES_1 = Financial
PSERIES_2 = Sales
PSERIES_3 = Purchasing
PSERIES_4 = Inventory
PSERIES_5 = Payroll
PSERIES_6 = Project
-- Series - for open/close by window
Series - 2 = Financial
Series - 3 = Sales
Series - 4 = Purchasing
Series - 5 = Inventory
Series - 6 = Payroll
No Project windows available in this column
---------------------------------------------------------------------------------
Sample: EXEC dbo.usp_OpenCurrentPeriodGLOnly
*/
CREATE PROCEDURE [dbo].[usp_OpenCurrentPeriodGLOnly]
AS
SET NOCOUNT ON
BEGIN TRAN
UPDATE dbo.SY40100
SET PSERIES_1 = 0
WHERE PERIODDT = CONVERT(date,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()),0),120) -- First day of the month
--WHERE PERIODDT = CONVERT(date,DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()),0),120) -- Today
UPDATE dbo.SY40100
SET CLOSED = 0
WHERE PERIODDT = CONVERT(date,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()),0),120) -- First day of the month
--WHERE PERIODDT = CONVERT(date,DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()),0),120) -- Today
AND SERIES = 2
COMMIT TRAN;
/****** Object: StoredProcedure [dbo].[usp_OpenCurrentPeriod] Script Date: 9/26/2020 3:59:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
---------------------------------------------------------------------------------
COPYRIGHT (C) 2019 Jeffrey Pfershy
This script is provided as-is without warranty, express or implied.
As with any code, you are responsible for testing this before using it
in a production environment.
This procedure has not been tested with any third party products such as
Binary Stream Multi-Entity Management (MEM) or others.
If you have questions I can be reached at jpfershy@hotmail.com
---------------------------------------------------------------------------------
usp_OpenCurrentPeriod STORED PROCEDURE CREATION SCRIPT
---------------------------------------------------------------------------------
Revision History
Date Who Description
----------- ---- --------------------------------------------------------
12 Jun 2019 JAP Initial creator
---------------------------------------------------------------------------------
Stored Procedure Name: usp_OpenCurrentPeriod
Purpose: To be used to open (unmark closed) the current fiscal period
for all modules (GL, AP, AR only) in a GP database (company).
Use: This stored proc was written to open all standard GP modules for the new
fiscal period, which in our environment is the first of the month.
I have a SQL job that runs on the first of every month and executes either
this sp or usp_OpenCurrentPeriodGLOnly (for GL only companies),
depending on if my GP company is a 'GL only' company or not.
If your fiscal periods fall on other days use the lines that are commented
out below instead
Create this stored procedure in every GP database you want to use it in,
then set up a SQL job to run it either on the day of the month that your new
fiscal period starts, or add other logic to run it daily and if that date
is the start of the new fiscal period, run the update statement.
**YOU WILL NEED TO MODIFY THIS CODE IF YOUR FISCAL PERIODS DO NOT BEGIN ON THE FIRST DAY OF THE MONTH**
**The field PERIODDT is the Fiscal Period START Date**
PSERIES_1 = Financial
PSERIES_2 = Sales
PSERIES_3 = Purchasing
PSERIES_4 = Inventory
PSERIES_5 = Payroll
PSERIES_6 = Project
-- Series - for open/close by window
Series - 2 = Financial
Series - 3 = Sales
Series - 4 = Purchasing
Series - 5 = Inventory
Series - 6 = Payroll
No Project windows available in this column
---------------------------------------------------------------------------------
Sample: EXEC dbo.usp_OpenCurrentPeriod
*/
ALTER PROCEDURE [dbo].[usp_OpenCurrentPeriod]
AS
SET NOCOUNT ON
BEGIN TRAN
UPDATE dbo.SY40100
SET PSERIES_1 = 0, PSERIES_2 = 0, PSERIES_3 = 0, PSERIES_4 = 0, PSERIES_5 = 0, PSERIES_6 = 0
WHERE PERIODDT = CONVERT(date,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()),0),120) -- First day of the month
--WHERE PERIODDT = CONVERT(date,DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()),0),120) -- Today
UPDATE dbo.SY40100
SET CLOSED = 0
WHERE PERIODDT = CONVERT(date,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()),0),120) -- First day of the month
--WHERE PERIODDT = CONVERT(date,DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()),0),120) -- Today
AND SERIES IN (2,3,4,5,6)
COMMIT TRAN;
GO