Page History

...

Checks and fixes for issues on allocations in AR and AP (Incorrect balances, and missing transactions)

Note that it will undo the allocation in Sage (removes the broken allocation string from the transactions, meaning you/the client will have to allocate again)

To check which lines will be unallocated:

Code Block

---------------
---------------
-----AP--------
---------------
---------------


--add all the Vendors
 TRUNCATE TABLE _etblAllocsDCLinkRangeTemp
  insert into _etblAllocsDCLinkRangeTemp (DCLink)
   (Select DClink from Vendor where bforcurAcc =0)			-

--Fill the Temp table sage uses
exec _espAllocsAllToTableARAP 'AP'							




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

 -------------------------------------
-------------------------------------
-------------------------------------
Select 
A1.iFromRecID  as AutoIdx,
 A1.iToRecID as [Alocated To AutoIdx]
  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



---------------
---------------
-----AR--------
---------------
---------------

--add all the clients
 TRUNCATE TABLE _etblAllocsDCLinkRangeTemp
  insert into _etblAllocsDCLinkRangeTemp (DCLink)
   (Select DClink from Client where bforcurAcc =0)

--Fill the Temp table sage uses
exec _espAllocsAllToTableARAP 'AR'


EXEC _AS_SP_AllocRecalculation @ARAP = 'AR',@CorrectErrors =  1


--Run it again as the fix above changed allocations:
exec _espAllocsAllToTableARAP 'AR'

-------------------------------------
-------------------------------------
-------------------------------------
--No mirror 1
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 PostAR PAR on NoMirror.iFromRecID = PAR.AutoIdx 
 left outer join Client C on NoMirror.iAccountID = C.DCLink 
 order by NoMirror.iFromRecID

-------------------------------------
-------------------------------------
 --Alloc diff
 Select	
	  A1.iFromRecID  as AutoIdx
	, A1.iToRecID as [Alocated To AutoIdx]

	 from _etblAllocsTemp A1 
	 left outer join _etblAllocsTemp A2 on A2.iToRecID=A1.iFromRecID and A1.iToRecID=A2.iFromRecID 
	 left outer join PostAR PAR on A1.iFromRecID = PAR.AutoIdx
	  left outer join Client C on A1.iAccountID = C.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

Checks and fixes incorrect calculated outstanding balances (home currency only)

To see what lines will be updated (when running step 2 and 3, it will update all the lines in the sql below)

Code Block

--AR
EXEC _AS_SP_AllocRecalculation  @ARAP = 'AR', @CorrectErrors = 0			--@CorrectErrors is set to 1 in step 2 and 3


--AP
EXEC _AS_SP_AllocRecalculation  @ARAP = 'AP', @CorrectErrors = 0			--@CorrectErrors is set to 1 in step 2 and 3

...

Space shortcuts

Page tree

Versions Compared

Old Version 2

New Version Current

Key