This is an old revision of the document!
Explanation of SQL
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
Risk of Data Corruption if Run Improperly
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.
SQL #1
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
Version Information
- Entered : 06/2010
- Updated : 11/2011
- Version : 4.5+
You could leave a comment if you were logged in.