Skip to end of metadata
Go to start of metadata

NOTE: This query shows ALLOCATION AMOUNTS vs PostAP.OUTSTANDING! (and foreign) and shows differences!


CAREFUL: performance impact!


RESULT: These will need to be recalculated. See:



declare @vendorId bigint = 868
;with AllocValues as (
SELECT 
		p.AccountLink,p.AutoIdx, CmD = p.Credit - p.Debit,
		p.Outstanding, p.fForeignOutstanding	
		,SUM(a.fAmount) as AllocAmount
		,SUM(a.fForeignAmount) as AllocAmountForeign
		,AllocOutstanding = ROUND((p.Credit - p.Debit)-
			COALESCE(CASE WHEN p.Credit > 0 THEN  1 ELSE -1 END * SUM(a.fAmount),0),2)
		,AllocOutstandingForeign = CASE WHEN V.bForCurAcc = 1 THEN ROUND((p.fForeignCredit - p.fForeignDebit)-
			COALESCE(CASE WHEN p.fForeignCredit > 0 THEN  1 ELSE -1 END * SUM(a.fForeignAmount),0),2)
				ELSE 0 END
	from PostAP p 
	LEFT JOIN Vendor V ON p.AccountLink = V.DCLink
	OUTER APPLY _as_GL_AllocsSplit_NTEXT(p.cAllocs) a
	--where p.AccountLink = 868
	GROUP BY 
		p.AccountLink,p.AutoIdx, p.Outstanding, p.fForeignOutstanding,p.Credit, p.Debit,p.fForeignCredit, p.fForeignDebit
		,V.bForCurAcc
		)
	,Diff as (select *
		,DiffHC = Outstanding - AllocOutstanding
		,DiffFC = fForeignOutstanding - AllocOutstandingForeign
		from AllocValues A
		WHERE 
			ABS(Outstanding - AllocOutstanding) > 0.01
			OR 
			ABS(fForeignOutstanding - AllocOutstandingForeign) > 0.01)
	select * from Diff
  • No labels