Page History

--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