control_sql_-_vendor_credit_balance_integrity_check [Support]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

====== Control SQL - Vendor Credit Balance ======



===== Explanation of SQL =====



This query compares the GL record of all Vendor Credit Balances with the record in the Vendor (account table).



===== Risk of Data Corruption if Run Improperly =====

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



===== SQL =====



<code sql>
SELECT  
    (SELECT DivisionName AS Division 
        FROM EmployeeGroup 
        WHERE COALESCE(A.DivisionID, Account.DivisionID) = EmployeeGroup.ID) AS Division,
    Account.CompanyName,
    Account.VendorCreditBalance,
    GLAmount AS GLCreditBalance,
    COALESCE(GLAmount,0) - COALESCE(Account.VendorCreditBalance,0) AS Difference,
    Account.ID AS AccountID,
    (SELECT MAX(EntryDateTime) 
        FROM GL GL2 
        WHERE GL2.AccountID = Account.ID
          AND GL2.GLAccountID = 25) AS LastGLCreditDate
FROM    
(
    SELECT  COALESCE(DivisionID, 10) AS DivisionID, 
            AccountID AS GLsAccountID, 
            SUM(Amount) GLAmount
    FROM GL
    WHERE GLAccountID = 25
    GROUP BY COALESCE(DivisionID, 10), AccountID
    HAVING SUM(Amount) <> 0
) AS A
FULL JOIN
(
    SELECT ID AS VendorAccountID
    FROM Account
    WHERE VendorCreditBalance <> 0
) AS B ON A.GLsAccountID = B.VendorAccountID
 
LEFT JOIN Account ON Account.ID = COALESCE(A.GLsAccountID, B.VendorAccountID)
WHERE COALESCE(A.GLAmount, 0) <> COALESCE(Account.VendorCreditBalance, 0)
ORDER BY Division, CompanyName, GLsAccountID
</code>



===== Version Information =====
  * Entered : 6/25/2009
  * Version : Control 4.3