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.

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

• Entered : //2009
• Version :