Control SQL - AP Integrity Check
Explanation of SQL
This query compares the A/P balance of bills in the GL with the balance in the TransHeader record.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
-- AP Integrity Check SELECT (SELECT DivisionName FROM Division WHERE COALESCE(GLDivisionID, TH.DivisionID) = Division.ID) AS Division, COALESCE(A.EffectiveDate, TH.OrderCreatedDate) AS PostingDate, TH.BillNumber AS BillNumber, TH.OrderNumber AS OrderNumber, A.GLAmount, TH.BalanceDue AS THAmount, COALESCE (TH.BalanceDue, 0) - COALESCE (A.GLAmount, 0) AS Difference, Account.CompanyName AS CompanyName, TH.Description, TH.StatusText, TH.StatusID, COALESCE(TH.ID, GLTransactionID) AS TransHeaderID, Account.ID AS AccountID, TH.BillNumber AS BillNumber, (SELECT MAX(EntryDateTime) FROM GL GL2 WHERE GL2.TransactionID = TH.ID) AS LastGLEntryDate FROM ( SELECT TransactionID AS GLTransactionID, COALESCE(DivisionID, 10) AS GLDivisionID, AccountID AS GLAccountID, -SUM(Amount) AS GLAmount, CAST(NULL AS DateTime) AS EffectiveDate FROM GL WHERE (GLAccountID = 22) AND (TransactionID IS NOT NULL) GROUP BY TransactionID, AccountID, COALESCE(DivisionID, 10) HAVING SUM(Amount) <> 0 UNION SELECT NULL AS GLTransactionID, COALESCE(DivisionID, 10) AS GLDivisionID, AccountID AS GLAccountID, -SUM(Amount) AS GLAmount, EntryDateTime AS EffectiveDate FROM GL WHERE (GLAccountID = 22) AND (TransactionID IS NULL) GROUP BY AccountID, COALESCE(DivisionID, 10), EntryDateTime HAVING SUM(Amount) <> 0 ) AS A FULL OUTER JOIN ( SELECT ID AS BillTransactionID, BalanceDue, AccountID FROM TransHeader WHERE TransactionType = 8 AND BalanceDue <> 0 AND StatusID <> 9 ) AS B 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) 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
Version Information
- Entered : 6/25/2009
- Version : Control 4.3
You could leave a comment if you were logged in.