Control SQL - Order Deposit Integrity Check

This SQL compares the deposits on orders found in the GL to the deposits found in the TransHeader databse.

None. This is a selection query and no data is modified in the running of it.

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

• Entered : 6/2/2015
• Version : Control 4.3