Differences

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

Link to this comparison view

control_sql_-_ap_integrity_check [2019/01/27 11:28]
127.0.0.1 external edit
control_sql_-_ap_integrity_check [2019/02/05 11:30] (current)
admin
Line 26: Line 26:
     (SELECT DivisionName ​     (SELECT DivisionName ​
         FROM Division ​         FROM Division ​
-        WHERE coalesce(GLDivisionID,​ TH.DivisionID) = Division.ID) AS Division, +        WHERE COALESCE(GLDivisionID,​ TH.DivisionID) = Division.ID) AS Division, 
-    ​Coalesce(A.EffectiveDate,​ TH.OrderCreatedDate) ​as PostingDate,​+    ​COALESCE(A.EffectiveDate,​ TH.OrderCreatedDate) ​AS PostingDate,​
     TH.BillNumber AS BillNumber,     TH.BillNumber AS BillNumber,
     TH.OrderNumber AS OrderNumber,​     TH.OrderNumber AS OrderNumber,​
Line 37: Line 37:
     TH.StatusText,​     TH.StatusText,​
     TH.StatusID,​     TH.StatusID,​
-    ​coalesce(TH.ID, GLTransactionID) AS TransHeaderID,​+    ​COALESCE(TH.ID, GLTransactionID) AS TransHeaderID,​
     Account.ID AS AccountID,     Account.ID AS AccountID,
     TH.BillNumber AS BillNumber,     TH.BillNumber AS BillNumber,
-    (SELECT ​max(EntryDateTime) ​+    (SELECT ​MAX(EntryDateTime) ​
         FROM GL GL2          FROM GL GL2 
         WHERE GL2.TransactionID = TH.ID) AS LastGLEntryDate         WHERE GL2.TransactionID = TH.ID) AS LastGLEntryDate
Line 46: Line 46:
 ( (
     SELECT ​   TransactionID AS GLTransactionID, ​     SELECT ​   TransactionID AS GLTransactionID, ​
-        ​Coalesce(DivisionID,​ 10) AS GLDivisionID,​+        ​COALESCE(DivisionID,​ 10) AS GLDivisionID,​
         AccountID AS GLAccountID, ​       ​         AccountID AS GLAccountID, ​       ​
         -SUM(Amount) AS GLAmount,         -SUM(Amount) AS GLAmount,
-        ​Cast(NULL as DateTime) ​as EffectiveDate+        ​CAST(NULL AS DateTime) ​AS EffectiveDate
     FROM GL     FROM GL
     WHERE (GLAccountID = 22) AND (TransactionID IS NOT NULL)     WHERE (GLAccountID = 22) 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 
 + 
     SELECT ​   NULL AS GLTransactionID, ​     SELECT ​   NULL AS GLTransactionID, ​
-        ​Coalesce(DivisionID,​ 10) AS GLDivisionID,​+        ​COALESCE(DivisionID,​ 10) AS GLDivisionID,​
         AccountID AS GLAccountID, ​       ​         AccountID AS GLAccountID, ​       ​
         -SUM(Amount) AS GLAmount,         -SUM(Amount) AS GLAmount,
-        EntryDateTime ​as EffectiveDate+        EntryDateTime ​AS EffectiveDate
     FROM GL     FROM GL
     WHERE (GLAccountID = 22) AND (TransactionID IS NULL)     WHERE (GLAccountID = 22) 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
    
 FULL OUTER JOIN  FULL OUTER JOIN 
  
- SELECT ID as BillTransactionID,​ BalanceDue, AccountID+    ​SELECT ID AS BillTransactionID,​ BalanceDue, AccountID
     FROM TransHeader ​     FROM TransHeader ​
-    WHERE TransactionType = 8 and BalanceDue ​ and StatusID ​ 9+    WHERE TransactionType = 8 AND BalanceDue ​<> ​AND StatusID ​<> ​9
 ) AS B ) AS B
 + 
 ON B.BillTransactionID = A.GLTransactionID ON B.BillTransactionID = A.GLTransactionID
-LEFT JOIN TransHeader TH on TH.ID = coalesce(A.GLTransactionID,​ B.BillTransactionID) 
    
-LEFT JOIN Account ON Account.ID = Coalesce(A.GLAccountID,​ B.AccountID)+LEFT JOIN TransHeader TH ON TH.ID = COALESCE(A.GLTransactionID,​ B.BillTransactionID) 
 +  
 +LEFT JOIN Account ON Account.ID = COALESCE(A.GLAccountID,​ B.AccountID)
    
-WHERE (Coalesce(B.BalanceDue,​1) ​ 0) AND (COALESCE (A.GLAmount,​ 0) - COALESCE (B.BalanceDue,​ 0) NOT BETWEEN -0.01 AND 0.01)+WHERE (COALESCE(B.BalanceDue,​1) ​<> ​0) AND (COALESCE (A.GLAmount,​ 0) - COALESCE (B.BalanceDue,​ 0) NOT BETWEEN -0.01 AND 0.01)
    
 ORDER BY Division, TH.BillNumber,​ PostingDate ORDER BY Division, TH.BillNumber,​ PostingDate