Copy Fiscal Calendar To A New Company

Microsoft Dynamics GPI have a bit of catching up to do on writing posts, but to start the year off I am back to some SQL scripts. As I have mentioned before I have a number of clients with multiple, and in some cases well over 100, companies in Microsoft Dynamics GP.

I do try to make life easier for them when possible so have been sharing a number of scripts I have for implementing systems. One such script is one which will copy financial (fiscal) calendars from one database to another.

There are three parameters at the top which need to be set before running the script:

  1. Year
  2. Source Company
  3. Destination Company

These parameters are highlighted below:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @Year VARCHAR(4) = '2014'
DECLARE @SourceDatabase VARCHAR(5) = 'TWO'
DECLARE @DestinationDatabase VARCHAR(5) = 'THREE'
DECLARE @SQLStatement VARCHAR(1000)

SET @SQLStatement =
	'INSERT INTO ' + @DestinationDatabase + '..SY40101
		(YEAR1,FSTFSCDY,LSTFSCDY,NUMOFPER,HISTORYR)
	--VALUES
		(SELECT
			YEAR1,FSTFSCDY,LSTFSCDY,NUMOFPER,HISTORYR
		FROM
			' + @SourceDatabase + '..SY40101
		WHERE
			(SELECT
				COUNT(YEAR1)
			FROM
				' + @DestinationDatabase + '..SY40101
			WHERE
				YEAR1 = ' + @Year + ') = 0
		AND
			YEAR1 = ' + @Year + ')'
EXEC (@SQLStatement)

SET @SQLStatement =
	'INSERT INTO ' + @DestinationDatabase + '..SY40100
		(CLOSED,SERIES,ODESCTN,FORIGIN,PERIODID,PERIODDT,PERNAME
		,PSERIES_1,PSERIES_2,PSERIES_3,PSERIES_4,PSERIES_5,PSERIES_6,
		YEAR1,PERDENDT)
	--VALUES
		(SELECT
			CLOSED,SERIES,ODESCTN,FORIGIN,PERIODID,PERIODDT,PERNAME
			,PSERIES_1,PSERIES_2,PSERIES_3,PSERIES_4,PSERIES_5,PSERIES_6,
			YEAR1,PERDENDT
		FROM
			' + @SourceDatabase + '..SY40100
		WHERE
			(SELECT
				COUNT(YEAR1)
			FROM
				' + @DestinationDatabase + '..SY40100
			WHERE
				YEAR1 = ' + @Year + ') = 0
			AND
				YEAR1 = ' + @Year + ')'
EXEC (@SQLStatement)
GO

The calendar will only be copied if the destination database does not contain a calendar with the same name.

Review of Microsoft Dynamics GP 2013 Implementation by Victoria Yudin

Microsoft Dynamics GPVictoria Yudin, the longest serving Microsoft(R) Most Valuable Professional for Microsoft Dynamics GP, has just released Microsoft Dynamics GP 2013 Implementation via Packt Publishing.

This is an update of the Microsoft Dynamics GP 2010 book she wrote a few years ago, which has been updated for Microsoft Dynamics GP 2013:

Microsoft Dynamics GP 2013 Implementation by Victoria Yudin

In brief this book covers:

  • Plan, install, and implement Microsoft Dynamics GP 2013 with real-world advice from a Microsoft Dynamics GP MVP
  • Learn how to set up the core modules in Microsoft Dynamics GP effectively following detailed, step-by-step instructions
  • Discover additional tools and resources available for your Dynamics GP

Continue reading “Review of Microsoft Dynamics GP 2013 Implementation by Victoria Yudin”