SQL Scripts for Microsoft Dynamics GP: Extract GL Period Balances

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was put together a while ago for a client who was migrating from Dynamics GP to another ERP system.

There are three sections to the script.

Firstly, a table is created in the DYNAMICS database into which data from all company databases will be populated:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
CREATE TABLE GLPeriodBalances
	(
		INTERID VARCHAR(100),
		ACTINDX VARCHAR(100),
		ACTNUMST VARCHAR(100),
		YEAR1 VARCHAR(100),
		PERIODID VARCHAR(100),
		LEDGERID VARCHAR(100),
		PERDBLNC VARCHAR(100),
		ACTNUMBR_1 VARCHAR(100),
		ACTNUMBR_2 VARCHAR(100),
		ACTNUMBR_3 VARCHAR(100),
		ACCATNUM VARCHAR(100),
		DEBITAMT VARCHAR(100),
		CRDTAMNT VARCHAR(100)
	)
GO

Secondly, a script which can be run in each company database to populate the extract table in the DYNAMICS database:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/INSERT INTO DYNAMICS..GLPeriodBalances
	(
		INTERID
		,ACTINDX
		,ACTNUMST
		,YEAR1
		,PERIODID
		,LEDGERID
		,PERDBLNC
		,ACTNUMBR_1
		,ACTNUMBR_2
		,ACTNUMBR_3
		,ACCATNUM
		,DEBITAMT
		,CRDTAMNT
	)
	
	SELECT
		DB_NAME(),
		['GL Period Balance'].ACTINDX
		,['Account Index Master'].ACTNUMST
		,['GL Period Balance'].YEAR1
		,['GL Period Balance'].PERIODID
		,['GL Period Balance'].LEDGER_ID
		,['GL Period Balance'].PERDBLNC
		,['Account Index Master'].ACTNUMBR_1
		,['Account Index Master'].ACTNUMBR_2
		,['Account Index Master'].ACTNUMBR_3
		,['GL Period Balance'].ACCATNUM
		,['GL Period Balance'].DEBITAMT
		,['GL Period Balance'].CRDTAMNT
	FROM
		GL00105 AS ['Account Index Master'] --Account Index Master (GL00105)
	INNER JOIN
		GL10110 AS ['GL Period Balance'] --Account Current Summary Master (GL10110)
			ON
				['Account Index Master'].ACTINDX = ['GL Period Balance'].ACTINDX
GO

Thirdly, the script which is used to select data from the extract table in the DYNAMICS database:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/SELECT 
      *
FROM
	DYNAMICS..GLPeriodBalances
GO