Sunday, January 29, 2012

GL Summary Reconcile

 

One of my clients requested to reconcile their GL Account Summary to GL Account Details periodically using SQL which is not currently available in Dynamics GP, I suggested to schedule a macro run but they wanted this to be done at the SQL level, therefore I have written the SQL command below to empty GL10110 table and repopulate the records to the tables using figures in GL20000.

Find the script below or download it from this location.

Disclaimer: This script works when your fiscal periods are matched with calendar periods only as it uses month of the transaction date as the transaction period, on the other hand, I tested this on limited data, if you find an issue or have any idea for script enhancement, please let feel free to comment.


Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mohammad R. Daoud
-- Create date: 2012-01-19
-- Description: Procedure to reconcile GL Summary
-- =============================================
CREATE PROCEDURE DI_GLRECONCILE

AS
BEGIN
DELETE FROM
[GL10110]

INSERT INTO [GL10110]
([ACTINDX]
,[YEAR1]
,[PERIODID]
,[PERDBLNC]
,[ACTNUMBR_1]
,[ACTNUMBR_2]
,[ACTNUMBR_3]
,[ACTNUMBR_4]
,[ACTNUMBR_5]
,[ACTNUMBR_6]
,[ACTNUMBR_7]
,[ACTNUMBR_8]
,[ACTNUMBR_9]
,[ACTNUMBR_10]
,[ACCATNUM]
,[CRDTAMNT]
,[DEBITAMT])

SELECT dbo.GL20000.ACTINDX, YEAR(dbo.GL20000.TRXDATE) AS YEAR1, MONTH(dbo.GL20000.TRXDATE) AS Period,
SUM(dbo.GL20000.DEBITAMT - dbo.GL20000.CRDTAMNT) AS [Period Balance], dbo.GL00100.ACTNUMBR_1, dbo.GL00100.ACTNUMBR_2,
dbo.GL00100.ACTNUMBR_3, dbo.GL00100.ACTNUMBR_4, dbo.GL00100.ACTNUMBR_5, dbo.GL00100.ACTNUMBR_6, dbo.GL00100.ACTNUMBR_7,
dbo.GL00100.ACTNUMBR_8, dbo.GL00100.ACTNUMBR_9, dbo.GL00100.ACTNUMBR_10, dbo.GL00100.ACCATNUM, SUM(dbo.GL20000.CRDTAMNT)
AS CRDTAMNT, SUM(dbo.GL20000.DEBITAMT) AS DEBITAMT
FROM dbo.GL20000 INNER JOIN
dbo.GL00100 ON dbo.GL20000.ACTINDX = dbo.GL00100.ACTINDX
GROUP BY dbo.GL20000.ACTINDX, YEAR(dbo.GL20000.TRXDATE), dbo.GL00100.ACTNUMBR_1, dbo.GL00100.ACTNUMBR_2, dbo.GL00100.ACTNUMBR_3,
dbo.GL00100.ACTNUMBR_4, dbo.GL00100.ACTNUMBR_5, dbo.GL00100.ACTNUMBR_6, dbo.GL00100.ACTNUMBR_7, dbo.GL00100.ACTNUMBR_8,
dbo.GL00100.ACTNUMBR_9, dbo.GL00100.ACTNUMBR_10, dbo.GL00100.ACCATNUM, MONTH(dbo.GL20000.TRXDATE)
END

1 comment:

Christopher Williams said...

Thanks very much for this. We had an issue where GL10110 was out of whack with GL20000. I did make two modifications: 1) in order to keep the beginning balances for each account I added a where clause to the delete statement: where PERIODID <> 0
2) I'm not sure which version of GP this was developed against but my GP10 and GP2010 systems do not have the ACTNUMBR_6 through ACTNUMBR_10 fields so those were removed.

Related Posts:

Related Posts with Thumbnails