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
- Entered : 6/2/2015
- Version : Control 4.3
You could leave a comment if you were logged in.