Differences
This shows you the differences between two versions of the page.
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, | + | WHERE COALESCE(GLDivisionID, |
- | | + | |
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, | ||
- | | + | |
Account.ID AS AccountID, | Account.ID AS AccountID, | ||
TH.BillNumber AS BillNumber, | TH.BillNumber AS BillNumber, | ||
- | (SELECT | + | (SELECT |
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 | SELECT | ||
- | | + | |
AccountID AS GLAccountID, | AccountID AS GLAccountID, | ||
-SUM(Amount) AS GLAmount, | -SUM(Amount) AS GLAmount, | ||
- | | + | |
FROM GL | FROM GL | ||
WHERE (GLAccountID = 22) AND (TransactionID IS NOT NULL) | WHERE (GLAccountID = 22) AND (TransactionID IS NOT NULL) | ||
- | GROUP BY TransactionID, | + | GROUP BY TransactionID, |
- | HAVING | + | HAVING |
+ | |||
UNION | UNION | ||
+ | |||
SELECT | SELECT | ||
- | | + | |
AccountID AS GLAccountID, | AccountID AS GLAccountID, | ||
-SUM(Amount) AS GLAmount, | -SUM(Amount) AS GLAmount, | ||
- | EntryDateTime | + | EntryDateTime |
FROM GL | FROM GL | ||
WHERE (GLAccountID = 22) AND (TransactionID IS NULL) | WHERE (GLAccountID = 22) AND (TransactionID IS NULL) | ||
- | GROUP BY AccountID, | + | GROUP BY AccountID, |
- | HAVING | + | HAVING |
+ | |||
) AS A | ) AS A | ||
FULL OUTER JOIN | FULL OUTER JOIN | ||
( | ( | ||
- | SELECT ID as BillTransactionID, | + | |
FROM TransHeader | FROM TransHeader | ||
- | WHERE TransactionType = 8 and BalanceDue | + | WHERE TransactionType = 8 AND BalanceDue |
) AS B | ) AS B | ||
+ | |||
ON B.BillTransactionID = A.GLTransactionID | ON B.BillTransactionID = A.GLTransactionID | ||
- | LEFT JOIN TransHeader TH on TH.ID = coalesce(A.GLTransactionID, | ||
- | LEFT JOIN Account ON Account.ID = Coalesce(A.GLAccountID, | + | LEFT JOIN TransHeader TH ON TH.ID = COALESCE(A.GLTransactionID, |
+ | |||
+ | LEFT JOIN Account ON Account.ID = COALESCE(A.GLAccountID, | ||
- | WHERE (Coalesce(B.BalanceDue, | + | WHERE (COALESCE(B.BalanceDue, |
ORDER BY Division, TH.BillNumber, | ORDER BY Division, TH.BillNumber, |