These SQL's are used to verify the GL data is internally consistent.

• In some historic data, we have seen cases where the GLClassificationType is not set properly. This field is used in several financial reports, most notably the Balance Sheet and Income Statement. If these reports are producing incorrect values, this SQL will help find/repair this problem.
• Another issue that will cause the B/S and I/S reports to not balance is if invalid GLClassificationType entries are made. This query will identify these, though they must be researched and fixed manually.
• Another check is made to ensure that all GL Entries are for currently valid accounts. These can not be automatically fixed, but are identified in the query below

Low. As written, this query resets the GL data to a state which it should already be in. It can be run repeatedly as written with no negative affect.

This SQL will show GL entries with the problems identified:

SELECT
AccountName 
, GLA.ID AS GLAccountID
, GLAccountID AS LedgerGLAccountID
, GLA.GLClassificationType
, GL.GLClassificationType AS LedgerGLClassificationType
, GLA.GLClassTypeName
, GL.GLClassTypeName AS LedgerGLClassTypeName
, GL.EntryDateTime
FROM Ledger GL
 LEFT JOIN GLAccount GLA ON GLA.ID = GL.GLAccountID
WHERE GL.ID > 0 
  AND ( GL.GLClassificationType <> GLA.GLClassificationType
  OR GLA.AccountName IS NULL
  OR GL.GLClassificationType >= 7000
  OR GL.GLClassificationType IS NULL
  OR GL.GLAccountID IS NULL
  )

This SQL will fix GL entries with the GLClassificationType not being set properly:

CREATE VIEW GLAcctTypeFix AS 
SELECT
AccountName 
, GLA.ID AS GLAccountID
, GLAccountID AS LedgerGLAccountID
, GLA.GLClassificationType
, GL.GLClassificationType AS LedgerGLClassificationType
, GLA.GLClassTypeName
, GL.GLClassTypeName AS LedgerGLClassTypeName
, GL.EntryDateTime
FROM Ledger GL
 LEFT JOIN GLAccount GLA ON GLA.ID = GL.GLAccountID
WHERE GL.ID > 0 
  AND ( GL.GLClassificationType <> GLA.GLClassificationType
  OR GLA.AccountName IS NULL
  OR GL.GLClassificationType >= 7000
  OR GL.GLClassificationType IS NULL
  OR GL.GLAccountID IS NULL
  )
GO
UPDATE GLAcctTypeFix
SET LedgerGLClassificationType = GLClassificationType
, LedgerGLClassTypeName = GLClassTypeName
GO
DROP VIEW GLAcctTypeFix

• Entered : 06/2010
• Updated : 11/2011
• Version : 4.5+