Control SQL - GL Balance Integrity Checks
Explanation of SQL
This query checks that the following items for consistency between the GL and other areas:
- A/Rs - ensures the GL A/Rs match those in the Order database
- A/Ps - ensures the GL A/Ps match those in the Bills database
- Customer Credit Balance - ensures the GL matches the balance of customer credits.
- Vendor Credit Balance - ensures the GL matches the balance of vendor credits.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
DECLARE @ShowAllResults INT; SET @ShowAllResults = 1; SELECT Division, Comparison, GLTotal, DetailTotal, (COALESCE(DetailTotal,0) - COALESCE(GLTotal,0)) AS Difference FROM ( SELECT Division.DivisionName AS Division, 'A/R Balance' AS Comparison, (SELECT SUM(amount) FROM GL WHERE GLAccountID = 14 AND COALESCE(GL.DivisionID, 10) = Division.ID) AS GLTotal, (SELECT SUM(BalanceDue* CASE WHEN StatusID >=3 THEN 1 ELSE TransHeader.PercentComplete/100 END) FROM TransHeader WHERE TransactionType IN (1,6) AND (StatusID BETWEEN 1 AND 3) AND COALESCE(TransHeader.DivisionID, 10) = Division.ID) AS DetailTotal FROM Division UNION SELECT Division.DivisionName AS Division, 'WIP Balance' AS Comparison, (SELECT SUM(amount) FROM GL WHERE GLAccountID = 11 AND COALESCE(GL.DivisionID, 10) = Division.ID) AS GLTotal, (SELECT SUM(SubTotalPrice) FROM TransHeader WHERE TransactionType IN (1,6) AND StatusID = 1 AND COALESCE(TransHeader.DivisionID, 10) = Division.ID) AS DetailTotal FROM Division UNION SELECT Division.DivisionName AS Division, 'Built Balance' AS Comparison, (SELECT SUM(amount) FROM GL WHERE GLAccountID = 12 AND COALESCE(GL.DivisionID, 10) = Division.ID) AS GLTotal, (SELECT SUM(SubTotalPrice) FROM TransHeader WHERE TransactionType IN (1,6) AND StatusID = 2 AND COALESCE(TransHeader.DivisionID, 10) = Division.ID) AS DetailTotal FROM Division UNION SELECT Division.DivisionName AS Division, 'Order Due Balance' AS Comparison, (SELECT -SUM(amount) FROM GL WHERE GLAccountID = 21 AND COALESCE(GL.DivisionID, 10) = Division.ID) AS GLTotal, (SELECT SUM(SubTotalPrice) FROM TransHeader WHERE TransactionType IN (1,6) AND StatusID IN (1,2) AND COALESCE(TransHeader.DivisionID, 10) = Division.ID) AS DetailTotal FROM Division UNION SELECT Division.DivisionName AS Division, 'A/P Balance' AS Comparison, (SELECT -SUM(amount) FROM GL WHERE GLAccountID = 22 AND COALESCE(GL.DivisionID, 10) = Division.ID) AS DetailTotal, (SELECT SUM(BalanceDue) FROM TransHeader WHERE TransactionType = 8 AND StatusID <> 9 AND COALESCE(TransHeader.DivisionID, 10) = Division.ID) AS DetailTotal FROM Division UNION SELECT Division.DivisionName AS Division, 'Customer Credit Balance' AS Comparison, (SELECT -SUM(Amount) FROM GL WHERE GLAccountID = 23 AND COALESCE(GL.DivisionID, 10) = Division.ID) AS GLTotal, (SELECT SUM(CreditBalance) FROM Account WHERE COALESCE(Account.DivisionID, 10) = Division.ID) AS DetailTotal FROM Division UNION SELECT Division.DivisionName AS Division, 'Vendor Credit Balance' AS Comparison, (SELECT SUM(Amount) FROM GL WHERE GLAccountID = 25 AND COALESCE(GL.DivisionID, 10) = Division.ID) AS GLTotal, (SELECT SUM(VendorCreditBalance) FROM Account WHERE COALESCE(Account.DivisionID, 10) = Division.ID) AS DetailTotal FROM Division UNION SELECT Division.DivisionName AS Division, 'Customer Deposit Balance' AS Comparison, (SELECT -SUM(Amount) FROM GL WHERE GLAccountID = 24 AND COALESCE(GL.DivisionID, 10) = Division.ID) AS GLTotal, (SELECT SUM( (PaymentTotal -(TotalPrice * PercentComplete/100)) ) FROM TransHeader WHERE TransactionType IN (1,6) AND StatusID IN (1,2) AND (PaymentTotal > (TotalPrice * PercentComplete/100)) AND COALESCE(TransHeader.DivisionID, 10) = Division.ID) AS DetailTotal FROM Division UNION SELECT Division.DivisionName AS Division, 'GL Total Balance' AS Comparison, (SELECT SUM(Amount) FROM GL WHERE (GLClassificationType < 9000) AND COALESCE(GL.DivisionID, 10) = Division.ID) AS GLTotal, 0 AS DetailTotal FROM Division ) TempTable WHERE (@ShowAllResults = 1) OR (COALESCE(DetailTotal,0) - COALESCE(GLTotal,0)) NOT BETWEEN -0.01 AND 0.01 ORDER BY Division, Comparison
Version Information
- Entered : 6/25/2009
- Version :
You could leave a comment if you were logged in.