====== 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 =====



<code 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
</code>



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



<code sql>
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
</code>



===== Version Information =====
  * Entered : 6/2/2015
  * Version : Control 4.3