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)