Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_payments_gl_integrity_check [2019/01/27 11:29]
127.0.0.1 external edit 		control_sql_-_payments_gl_integrity_check [2019/02/05 11:33] (current)
admin
Line 59: Line 59:
       AND GL.EntryDateTime >= @StartDate       AND GL.EntryDateTime >= @StartDate
     GROUP BY COALESCE(DivisionID, 10), AccountID, GroupID, TransactionID, EntryDateTime     GROUP BY COALESCE(DivisionID, 10), AccountID, GroupID, TransactionID, EntryDateTime
-    HAVING SUM(Amount)  0 AND (AccountID IS NOT NULL)+    HAVING SUM(Amount) <> 0 AND (AccountID IS NOT NULL)
 ) AS A ) AS A
 FULL JOIN FULL JOIN
Line 70: Line 70:
         TransactionID AS JTHID         TransactionID AS JTHID
     FROM Journal     FROM Journal
-    WHERE ClassTypeID = 20001 -- AND DetailAmount  0+    WHERE ClassTypeID = 20001 -- AND DetailAmount <> 0
       AND Journal.StartDateTime >= @StartDate       AND Journal.StartDateTime >= @StartDate
 ) AS B ON A.GLGroupID BETWEEN B.StartGLGroupID AND B.EndGLGroupID ) AS B ON A.GLGroupID BETWEEN B.StartGLGroupID AND B.EndGLGroupID
Line 78: Line 78:
 LEFT JOIN TransHeader ON TransHeader.ID = COALESCE(JTHID, GLTHID) LEFT JOIN TransHeader ON TransHeader.ID = COALESCE(JTHID, GLTHID)
    
-WHERE COALESCE(B.JournalAmount, 0)  COALESCE(A.GLAmount, 0) +WHERE COALESCE(B.JournalAmount, 0) <> COALESCE(A.GLAmount, 0) 
-    AND COALESCE(B.JournalAmount, 0)  COALESCE(-A.GLAmount, 0)+    AND COALESCE(B.JournalAmount, 0) <> COALESCE(-A.GLAmount, 0)
    
 ORDER BY Division, JournalDate, CompanyName, GLAccountID ORDER BY Division, JournalDate, CompanyName, GLAccountID