Table of Contents

Explanation of SQL

This SQL compares the WIP and Built totals in the order (TransHeader) table with the balance in the GL.

Note: If the user has the option to not track WIP and Built in the GL selected, all orders in WIP and Built will show up on this report.

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 COALESCE(GLDivisionID, OrderDivisionID) = Division.ID) AS Division,
        OrderNumber,
        GLStatusText AS GLBalanceIn,
        TransHeader.StatusText AS CurrentOrderStatus,
        TransHeader.SubTotalPrice,
        GLAmount,
        OrdersDueAmount,        
        Account.CompanyName,
        TransHeader.Description AS OrderDescription,
        (SELECT MAX(EntryDateTime) 
                FROM GL
                WHERE TransactionID = TransHeader.ID) AS LastGLDate,
        TransHeader.ID AS TransHeaderID
 
FROM
(
    SELECT * 
    FROM
        (
          SELECT ID AS OrderTHID,
                COALESCE(DivisionID, 10) AS OrderDivisionID,
                SubTotalPrice AS OrderAmount,
                'WIP' AS OrderStatusText,
                AccountID AS OrderAccountID
          FROM TransHeader
          WHERE StatusID = 1 AND SubTotalPrice <> 0
        ) A
        FULL JOIN
        (
          SELECT TransactionID AS GLTHID,
                COALESCE(DivisionID, 10) AS GLDivisionID,
                'WIP' AS GLStatusText,
                SUM(Amount) AS GLAmount,
                AccountID AS GLsAccountID
          FROM GL
          WHERE GLAccountID = 11
          GROUP BY COALESCE(DivisionID, 10), TransactionID, AccountID
          HAVING SUM(Amount) <> 0
        ) B ON A.OrderTHID = B.GLTHID
 
        LEFT JOIN
        (
          SELECT TransactionID AS OrdersDueTHID,
                -SUM(Amount) AS OrdersDueAmount
          FROM GL
          WHERE GLAccountID = 21
          GROUP BY TransactionID
          HAVING SUM(Amount) <> 0
        ) G ON G.OrdersDueTHID = COALESCE(A.OrderTHID, B.GLTHID)
 
        WHERE (COALESCE (A.OrderAmount, 0) - COALESCE (B.GLAmount, 0) NOT BETWEEN -0.01 AND 0.01)       
 
        UNION 
 
        SELECT *
        FROM
        (
          SELECT ID AS OrderTHID,
                COALESCE(DivisionID, 10) AS OrderDivisionID,
                SubTotalPrice AS OrderAmount,
                'Built' AS OrderStatusText,
                AccountID AS OrderAccountID
          FROM TransHeader
          WHERE StatusID = 2 AND SubTotalPrice <> 0
        ) C
        FULL JOIN
        (
          SELECT TransactionID AS GLTHID,
                COALESCE(DivisionID, 10) AS GLDivisionID,
                'Built' AS GLStatusText,
                SUM(Amount) AS GLAmount,
                AccountID AS GLsAccountID
          FROM GL
          WHERE GLAccountID = 12
          GROUP BY COALESCE(DivisionID, 10), TransactionID, AccountID
          HAVING SUM(Amount) <> 0
        ) D ON C.OrderTHID = D.GLTHID
 
        LEFT JOIN
        (
          SELECT TransactionID AS OrdersDueTHID,
                -SUM(Amount) AS OrdersDueAmount
          FROM GL
          WHERE GLAccountID = 21
          GROUP BY TransactionID
          HAVING SUM(Amount) <> 0
        ) K ON K.OrdersDueTHID = COALESCE(C.OrderTHID, D.GLTHID)
 
        WHERE (COALESCE (C.OrderAmount, 0) - COALESCE (D.GLAmount, 0) NOT BETWEEN -0.01 AND 0.01)       
) E
 
LEFT JOIN TransHeader ON TransHeader.ID = COALESCE(OrderTHID, GLTHID)
 
LEFT JOIN Account ON Account.ID = COALESCE(OrderAccountID, GLsAccountID)
 
ORDER BY Division, OrderNumber

Version Information