Copy Fiscal Calendar To All Companies

● Ian Grieve ●  ● 4 Comments  ● 

Microsoft Dynamics GPIn 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:

  • Year
  • SourceDatabase
  • /*
    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)
    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
    		@DestinationCompany
    	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
    				@DestinationCompany
    		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.

    What should we write about next?

    Please leave this field empty.

    Your Name (required) -
    Your Email (required) -
    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

    4 Responses

    1. […] Ian Grieve offers a SQL script to Copy Fiscal Calendar To All Companies  […]

    2. […] Continue reading here: Copy Fiscal Calendar To All Companies | azurecurve […]

    Leave a Reply

    Your email address will not be published. Required fields are marked *