This is an old revision of the document!


Control SQL - Customer Credit Balance Integrity Check

This SQL compares Customer Credits in the GL with Customer Credits in the Customer Database.

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

SELECT  
    (SELECT DivisionName 
        FROM Division 
        WHERE COALESCE(GLDivisionID, OrderDivisionID) = Division.ID) AS Division,
    (SELECT CompanyName 
        FROM Account
        WHERE ID = COALESCE(B.AccountID, A.GLsAccountID)) AS CompanyName,
    GLAmount AS GLCreditBalance,
    B.CreditBalance AS CustomerCreditBalance,
    COALESCE(GLAmount, 0) - COALESCE(B.CreditBalance,0) AS Difference,
    COALESCE(B.AccountID, A.GLsAccountID) AS AccountID,
    (SELECT MAX(EntryDateTime) 
        FROM GL GL2 
        WHERE GL2.AccountID = A.GLsAccountID
          AND GL2.GLAccountID = 23) AS LastGLCreditDate
FROM
(
    SELECT  COALESCE(DivisionID, 10) AS GLDivisionID, 
        AccountID AS GLsAccountID, 
        -SUM(Amount) AS GLAmount
    FROM GL
    WHERE GLAccountID = 23
    GROUP BY COALESCE(DivisionID, 10), AccountID
    HAVING SUM(Amount)  0
) AS A
FULL JOIN
(
    SELECT ID AS AccountID, CompanyName, CreditBalance, 
         COALESCE(DivisionID, 10) AS OrderDivisionID
    FROM Account
    WHERE CreditBalance > 0
) AS B ON A.GLsAccountID = B.AccountID
WHERE COALESCE(A.GLAmount, 0)  COALESCE(B.CreditBalance, 0)
ORDER BY Division, CompanyName, GLsAccountID
  • Entered : 6/25/2009
  • Version : Control 4.3
You could leave a comment if you were logged in.