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