Page History

/*Cross check - total outstanding per vendor vs dcbalance.*/

WITH OS as (SELECT AccountLink, SUM(outstanding) as Outstanding
				,CASE WHEN V.bForCurAcc = 1 THEN ROUND(SUM(p.fForeignOutstanding),2) ELSE 0 END
					as ForeignOutstanding
				FROM PostAP p 
				LEFT JOIN Vendor V ON p.accountlink = V.DCLink
				GROUP BY 
					p.AccountLink , V.bForCurAcc
					)
SELECT V.Account, V.Name, OS.*, V.DCBalance
	,V.fForeignBalance
	, V.iCurrencyID
	FROM Vendor V
	LEFT JOIN OS ON V.DCLink = OS.AccountLink
	WHERE 
		ABS(COALESCE(V.DCBalance,0) - OS.Outstanding) >0.01
		OR 
		(V.bForCurAcc=1 AND COALESCE(V.fForeignBalance,0) <> OS.ForeignOutstanding)
	ORDER BY V.Account