Control SQL -
Explanation of SQL
This SQL compares the A/R balance in the Order (TransHeader) database with the GL database.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
-- AR Integrity Check SELECT (SELECT DivisionName FROM Division WHERE COALESCE(A.GLDivisionID, TH.DivisionID) = Division.ID) AS Division, COALESCE(A.EffectiveDate, TH.SaleDate) AS PostingDate, TH.OrderNumber AS OrderNumber, A.GLAmount, (BalanceDue* CASE WHEN StatusID >=3 THEN 1 ELSE TH.PercentComplete/100 END) AS THAmount, COALESCE ( (BalanceDue* CASE WHEN StatusID >=3 THEN 1 ELSE TH.PercentComplete/100 END) , 0) - COALESCE (A.GLAmount, 0) AS Difference, Account.CompanyName AS CompanyName, TH.Description, TH.StatusText, TH.ID 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 = 14) 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 = 14) AND (TransactionID IS NULL) GROUP BY AccountID, COALESCE(DivisionID, 10), EntryDateTime HAVING SUM(Amount) <> 0 ) AS A FULL OUTER JOIN ( SELECT * FROM TransHeader WHERE TransactionType IN (1,6) AND BalanceDue <> 0 AND StatusID BETWEEN 1 AND 4 ) AS TH ON TH.ID = A.GLTransactionID 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) 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) ORDER BY Division, OrderNumber
Version Information
- Entered : 6/25/2009
- Version : Control 4.3
You could leave a comment if you were logged in.