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