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  0 and StatusID  9+    WHERE TransactionType = 8 AND BalanceDue <> 0 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