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
- Entered : //2009
- Version :
You could leave a comment if you were logged in.