Copy Fiscal Calendar To All Companies

In the last post I mentioned that I create scripts when implementing Microsoft Dynamics GP and then make them available to clients when they would be of benefit. The last script I posted, allowed calendars to ve copied from one database to another. Afetr writing that scirpt I then adapted it by wrapping a cursor around it which allowed a source database to be defined and the calendar copied to all other databases.

The original script is still useful as it allows a targeted copying of calendars from a source to a destination database, but the new allows calendars to be quickly replicated across all companies if they share the same financial year.

The script has two parameters (highlighted) at the top which need to be set:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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)
DECLARE @SQLStatement VARCHAR(1000)

DECLARE
	cursor_InterID CURSOR 
FOR 
	SELECT
		INTERID
	FROM
		DYNAMICS..SY01500
	INNER JOIN
		master..sysdatabases
	ON
		name = INTERID
	
	OPEN cursor_InterID

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@DestinationDatabase
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)

			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)
			FETCH NEXT FROM
				cursor_InterID
			INTO
				@DestinationDatabase
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

As always with a script, make sure you have a backup of the databases which will be effected before running the script and do some testing after testing the script.