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.

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        Mohammad R. Daoud-- Create date: 2012-01-19-- Description:    Procedure to reconcile GL Summary-- =============================================CREATE PROCEDURE DI_GLRECONCILE

ASBEGINDELETE 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 DEBITAMTFROM         dbo.GL20000 INNER JOIN                      dbo.GL00100 ON dbo.GL20000.ACTINDX = dbo.GL00100.ACTINDXGROUP 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

See more here:
GL Summary Reconcile