--add all the Vendors
TRUNCATE TABLE _etblAllocsDCLinkRangeTemp
insert into _etblAllocsDCLinkRangeTemp (DCLink)
(Select DClink from Vendor ) -- where /*bforcurAcc =0 and */ dclink = 532)
--Fill the Temp table sage uses
exec _espAllocsAllToTableARAP 'AP'
/*This table outlines which allocations do not have a contra-position. (so the contra-allocation in the other tx as expeced)*/
Select
NoMirror.iFromRecID as AutoIdx,
NoMirror.iToRecID as [Alocated To AutoIdx]
from
(Select A1.* from _etblAllocsTemp A1 left join _etblAllocsTemp A2 on A2.iToRecID = A1.iFromRecID and A1.iToRecID = A2.iFromRecID where A2.idAllocsTemp Is Null) NoMirror
left outer join PostAP PAP on NoMirror.iFromRecID = PAP.AutoIdx left outer join Vendor V on NoMirror.iAccountID = V.DCLink order by NoMirror.iFromRecID
-------------------------------------
-------------------------------------
-------------------------------------
--these tx show differences in from-to allocation amounts
;with y as (Select
A1.iFromRecID as AutoIdx,
A1.iToRecID as [Alocated To AutoIdx]
,p1 = (select sum(A3.fAmount) from _etblAllocsTemp A3
where A3.iFromRecID=A1.iFromRecID and A3.iToRecID=A1.iToRecID group by A3.iFromRecID, A3.iToRecID)
,p2 =
(select sum(A4.fAmount) from _etblAllocsTemp A4 where A4.iFromRecID=A2.iFromRecID and A4.iToRecID=A2.iToRecID group by A4.iFromRecID, A4.iToRecID)
from _etblAllocsTemp A1 left outer join _etblAllocsTemp A2 on A2.iToRecID=A1.iFromRecID and A1.iToRecID=A2.iFromRecID
left outer join PostAP PAP on A1.iFromRecID = PAP.AutoIdx
left outer join Vendor V on A1.iAccountID = V.DCLink
where ((select sum(A3.fAmount) from _etblAllocsTemp A3
where A3.iFromRecID=A1.iFromRecID and A3.iToRecID=A1.iToRecID group by A3.iFromRecID, A3.iToRecID) <>
(select sum(A4.fAmount) from _etblAllocsTemp A4 where A4.iFromRecID=A2.iFromRecID and A4.iToRecID=A2.iToRecID group by A4.iFromRecID, A4.iToRecID))
-- order by A1.iFromRecID
)
select * , p1-p2 as diff from y where abs(p1-p2) > 1
Overview
Content Tools