Table of Contents

Control SQL - Order Deposit Integrity Check

Explanation of SQL

This SQL compares the deposits on orders found in the GL to the deposits found in the TransHeader databse.

Risk of Data Corruption if Run Improperly

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

SQL

SELECT 
    (SELECT DivisionName 
        FROM Division 
        WHERE A.DivisionID = Division.ID) AS Division,
    (SELECT OrderNumber 
        FROM TransHeader 
        WHERE ID = COALESCE(THTransHeaderID, GLTransHeaderID)) AS OrderNumber,
    GLCredit, 
    (SELECT PaymentTotal
        FROM TransHeader 
        WHERE ID = COALESCE(THTransHeaderID, GLTransHeaderID)) AS PaymentTotal,
    COALESCE(GLCredit, 0) - COALESCE(B.PaymentTotal, 0) Difference, 
    (SELECT StatusText
        FROM TransHeader 
        WHERE ID = COALESCE(THTransHeaderID, GLTransHeaderID)) AS StatusText,
    COALESCE(THTransHeaderID, GLTransHeaderID) AS TransHeaderID,
    (SELECT MAX(EntryDateTime) 
        FROM GL GL2 
        WHERE GL2.TransactionID = COALESCE(THTransHeaderID, GLTransHeaderID)) AS LastGLEntryDate
FROM
(
    SELECT COALESCE(DivisionID, 10) AS DivisionID,
        TransactionID AS GLTransHeaderID, 
        -SUM(Amount) GLCredit
    FROM GL
    WHERE GLAccountID = 24 
    GROUP BY COALESCE(DivisionID, 10), TransactionID
    HAVING SUM(Amount) <> 0
) AS A
 
FULL JOIN
(
    SELECT     ID AS THTransHeaderID, 
        OrderNumber, 
        PaymentTotal, 
        StatusText
    FROM TransHeader
    WHERE StatusID IN (1,2) AND (PaymentTotal <> 0)
) AS B ON A.GLTransHeaderID = B.THTransHeaderID
 
WHERE COALESCE(A.GLCredit, 0) <> COALESCE(B.PaymentTotal, 0)
 
ORDER BY Division, OrderNumber

This version should work even for companies using progress billing - but check first. 

SELECT 
    (SELECT DivisionName AS Division 
        FROM EmployeeGroup 
        WHERE A.DivisionID = EmployeeGroup.ID) AS Division,
    (SELECT OrderNumber 
        FROM TransHeader 
        WHERE ID = COALESCE(THTransHeaderID, GLTransHeaderID)) AS OrderNumber,
    GLCredit, 
    (SELECT (PaymentTotal - (TotalPrice * PercentComplete/100)) 
        FROM TransHeader 
        WHERE ID = COALESCE(THTransHeaderID, GLTransHeaderID)) AS DepositAmount,
    COALESCE(GLCredit, 0) - COALESCE(B.DepositAmount, 0) Difference, 
    (SELECT StatusText
        FROM TransHeader 
        WHERE ID = COALESCE(THTransHeaderID, GLTransHeaderID)) AS StatusText,
    COALESCE(THTransHeaderID, GLTransHeaderID) AS TransHeaderID,
    PercentComplete,
    (SELECT MAX(EntryDateTime) 
        FROM GL GL2 
        WHERE GL2.TransactionID = COALESCE(THTransHeaderID, GLTransHeaderID)) AS LastGLEntryDate
FROM
(
    SELECT COALESCE(DivisionID, 10) AS DivisionID,
        TransactionID AS GLTransHeaderID, 
        -SUM(Amount) GLCredit
    FROM GL
    WHERE GLAccountID = 24 
    GROUP BY COALESCE(DivisionID, 10), TransactionID
    HAVING SUM(Amount) <> 0
) AS A
 
FULL JOIN
(
    SELECT     ID AS THTransHeaderID, 
        OrderNumber, 
        (PaymentTotal- (TotalPrice * PercentComplete/100)) AS DepositAmount, 
        StatusText,
        PercentComplete
    FROM TransHeader
    WHERE StatusID IN (1,2) AND ((PaymentTotal- (TotalPrice * PercentComplete/100)) > 0)
) AS B ON A.GLTransHeaderID = B.THTransHeaderID
 
WHERE COALESCE(A.GLCredit, 0) <> COALESCE(B.DepositAmount, 0)
 
ORDER BY Division, OrderNumber

Version Information