Control SQL - GL Balance Integrity Checks

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.

None. This is a selection query and no data is modified in the running of it.

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
  • Entered : 6/25/2009
  • Version :
You could leave a comment if you were logged in.