Explanation of SQL
This query compares the the Summary Journal for each payment to the GL record of it. Note that not all records indicated an error, as manual GL entries may show up in this report (and are not incorrect).
Note: This report can take a long time to run (1-5 minutes).
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
Set the @StartDate variable to the begining time you want to run the comparison. For speed reasons, it is not recommended that you run this more than 2 years in one query.
DECLARE @StartDate DateTime; SET @StartDate='1/1/2008'; SELECT (SELECT PrintAsName AS Division FROM EmployeeGroup WHERE A.DivisionID = EmployeeGroup.ID) AS Division, TransHeader.OrderNumber AS OrderNumber, Account.CompanyName AS CompanyName, B.JournalDate, A.GLDate, A.GLAmount AS GLPayment, B.JournalAmount AS JournalPayment, A.GLAmount - B.JournalAmount AS Difference, A.GLGroupID, Account.ID AS AccountID FROM ( SELECT COALESCE(DivisionID, 10) AS DivisionID, AccountID GLAccountID, GroupID AS GLGroupID, TransactionID AS GLTHID, GL.EntryDateTime AS GLDate, SUM(Amount) GLAmount FROM GL WHERE GLClassificationType IN (1000, 1007, 2001) AND GL.EntryDateTime >= @StartDate GROUP BY COALESCE(DivisionID, 10), AccountID, GroupID, TransactionID, EntryDateTime HAVING SUM(Amount) <> 0 AND (AccountID IS NOT NULL) ) AS A FULL JOIN ( SELECT AccountID AS JAccountID, StartDateTime AS JournalDate, DetailAmount AS JournalAmount, StartGLGroupID, EndGLGroupID, TransactionID AS JTHID FROM Journal WHERE ClassTypeID = 20001 -- AND DetailAmount <> 0 AND Journal.StartDateTime >= @StartDate ) AS B ON A.GLGroupID BETWEEN B.StartGLGroupID AND B.EndGLGroupID LEFT JOIN Account ON Account.ID = COALESCE(JAccountID, GLAccountID) LEFT JOIN TransHeader ON TransHeader.ID = COALESCE(JTHID, GLTHID) WHERE COALESCE(B.JournalAmount, 0) <> COALESCE(A.GLAmount, 0) AND COALESCE(B.JournalAmount, 0) <> COALESCE(-A.GLAmount, 0) ORDER BY Division, JournalDate, CompanyName, GLAccountID
Version Information
- Entered : 6/25/2009
- Version : Control 4.3
You could leave a comment if you were logged in.