Differences

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

Link to this comparison view

control_sql_-_ar_integrity_check [2019/01/27 11:28]
127.0.0.1 external edit 		control_sql_-_ar_integrity_check [2019/02/05 11:30] (current)
admin
Line 51: Line 51:
     WHERE (GLAccountID = 14) AND (TransactionID IS NOT NULL)     WHERE (GLAccountID = 14) AND (TransactionID IS NOT NULL)
     GROUP BY TransactionID, AccountID, COALESCE(DivisionID, 10)     GROUP BY TransactionID, AccountID, COALESCE(DivisionID, 10)
-    HAVING SUM(Amount)  0+    HAVING SUM(Amount) <> 0
    
     UNION      UNION 
Line 63: Line 63:
     WHERE (GLAccountID = 14) AND (TransactionID IS NULL)     WHERE (GLAccountID = 14) AND (TransactionID IS NULL)
     GROUP BY AccountID, COALESCE(DivisionID, 10), EntryDateTime     GROUP BY AccountID, COALESCE(DivisionID, 10), EntryDateTime
-    HAVING SUM(Amount)  0+    HAVING SUM(Amount) <> 0
    
 ) AS A ) AS A
Line 71: Line 71:
     SELECT *      SELECT * 
     FROM TransHeader      FROM TransHeader 
-    WHERE TransactionType in (1,6) AND BalanceDue  0 AND StatusID BETWEEN 1 AND 4+    WHERE TransactionType IN (1,6) AND BalanceDue <> 0 AND StatusID BETWEEN 1 AND 4
 ) AS TH ) AS TH
    
Line 78: Line 78:
 LEFT JOIN Account ON Account.ID = COALESCE(A.GLAccountID, TH.AccountID) LEFT JOIN Account ON Account.ID = COALESCE(A.GLAccountID, TH.AccountID)
    
-WHERE (COALESCE( (BalanceDue* CASE WHEN StatusID >=3 THEN 1 ELSE TH.PercentComplete/100 END),1)  0) +WHERE (COALESCE( (BalanceDue* CASE WHEN StatusID >=3 THEN 1 ELSE TH.PercentComplete/100 END),1) <> 0) 
   AND (COALESCE (A.GLAmount, 0) - COALESCE ( (BalanceDue* CASE WHEN StatusID >=3 THEN 1 ELSE TH.PercentComplete/100 END), 0) NOT BETWEEN -0.01 AND 0.01)   AND (COALESCE (A.GLAmount, 0) - COALESCE ( (BalanceDue* CASE WHEN StatusID >=3 THEN 1 ELSE TH.PercentComplete/100 END), 0) NOT BETWEEN -0.01 AND 0.01)