MYOB EXO – Stored Procedure to recalc GL (Fast)

The standard utilities for Exo are slow to recalculate GL Balances and movement. This is because:

1. They delete records line by line, which gets really slow for the GLMovements table in most databases.
2. They take forever to update the resulting data, because most Exo functions work on a line-by-line basis, rather than calculating as a set.

Based on this, we had a need to recalculate GL Balances faster than the standard functions. Behind the cut is a function that does this fast.

This is much faster, because it uses a TRUNCATE on the GLMovements table, and then repopulates using set based transforms. It can still take awhile for larger databases though.

if not exists (select 1 from sys.procedures where name='X_RECALC_GL')
exec ('CREATE PROCEDURE [dbo].[X_RECALC_GL]
AS
--PURPOSE:	Much faster GL Recalc procedure, general toolkit procedure to speed up any recalc of GLMovements/GLAccs Balances.
BEGIN
SET NOCOUNT ON

TRUNCATE TABLE GLMOVEMENTS;

INSERT INTO GLMOVEMENTS (ACCNO, SUBACCNO, BRANCHNO, COMPANYNO, PERIOD_SEQNO, AMOUNT, AMOUNT_FC)
SELECT T.ACCNO, T.SUBACCNO, T.BRANCHNO, T.COMPANYNO, T.PERIOD_SEQNO, ISNULL(SUM(T.AMOUNT),0) AS AMOUNT, ISNULL(SUM(T.FCAMOUNT),0) AS FCAMOUNT
FROM GLTRANS T 
GROUP BY T.ACCNO, T.SUBACCNO, T.BRANCHNO, T.COMPANYNO,T.PERIOD_SEQNO; 

WITH BAL AS
(		 SELECT		 A.ACCNO
					,A.NAME
					,A.SECTION
					,BALANCE
					,ISNULL(ROUND(SUM(CASE WHEN A.SECTION<>0 OR PS.YEARAGE=0 THEN AMOUNT ELSE 0 END),2),0) AS CALC_BALANCE
					,ISNULL(ROUND(SUM(CASE WHEN A.SECTION<>0 OR PS.YEARAGE=0 THEN AMOUNT ELSE 0 END),2),0)-ROUND(A.BALANCE,2) AS BALANCE_DIFF
		 FROM		 GLACCS AS A WITH (NOLOCK) LEFT OUTER JOIN
					 GLTRANS AS T WITH (NOLOCK) ON A.ACCNO = T.ACCNO LEFT OUTER JOIN
					 PERIOD_STATUS AS PS WITH (NOLOCK) ON PS.SEQNO = T.PERIOD_SEQNO
		 GROUP BY	 A.ACCNO, A.NAME, A.SECTION, A.BALANCE, A.BALSHEETACCTOT
		 HAVING		 ISNULL(ROUND(SUM(CASE WHEN A.SECTION<>0 OR PS.YEARAGE=0 THEN AMOUNT ELSE 0 END),2),0)<>ROUND(A.BALANCE,2)
	)
UPDATE	 A
SET		 BALANCE=BAL.CALC_BALANCE
FROM	 GLACCS AS A INNER JOIN
		 BAL ON BAL.ACCNO=A.ACCNO

END;')