Sage Evolution
Space shortcuts
Page tree
Skip to end of metadata
Go to start of metadata

This article will describe how to find and solve allocation issues. (AR)

Step-by-step guide to find differences

Follow the follow steps to find differences

  1. To find the allocation differences for AR run the following Script:

    TRUNCATE TABLE _etblAllocsDCLinkRangeTemp
	insert into _etblAllocsDCLinkRangeTemp (DCLink)
			select DCLink from Client

exec _espAllocsToTableARAP 'AR', '2099-12-31', 1, 0, null, null


SELECT *,
	newOutstanding = outstandingAR + DifferenceAlloc
	FROM (
SELECT 
	C.Account
	,X2.*
	,DifferenceAlloc = AllocatedAmount - OutstandingAR
	 from (
	SELECT 
		 *
		, CASE WHEN FROMDMC < 0 THEN FROMDMC +Amount ELSE FROmDMC - Amount END as AllocatedAmount
		, outstandingAR = (SELECT outstanding from postar where AUTOIDx = X1.AUTOIDx)
	 from(
		SELECT 
		iAccountID
		,AutoIdx
		,FROMDMC
		,SUM(fAmount) Amount

		--B--alanceRemaining = ROUNd(Case when FromDMC > 0 Then  FromDMC-fAmount else FromDMC +famount END,2)
		from (
				 Select
					A1.iAccountID
					,  A1.iFromRecID  as AutoIdx
					, A1.iToRecID as [Alocated To AutoIdx]
					, FromDMC = (Select debit-credit from  postar where autoidx = A1.iFromRecID)
					, ToDMC = (Select debit-credit from  postar where autoidx = A1.iToRecID)
					, A1.fAmount
					 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  c.dclink  in (SELECT DCLink FROM Client)
				)
				X
			group by
			 AutoIdx
			,iAccountID
			,FROMDMC) x1 

	WHERE 
		ROUND(CASE 
				WHEN FROMDMC < 0 THEN FROMDMC +Amount ELSE FROmDMC - Amount END,2) <> 0
	) X2
LEFT JOIN CLIENT C on C.DCLINK = IaccountID
		WHERE  ROUND(AllocatedAmount,2) <> RounD(OutstandingAR,2)
--ORder by  C.Account ASC
) X

  2. To find overallocations:

    Declare @Decimals int Set @Decimals = (select top 1 Decimals from StDfTbl) 

Select 
P.AutoIdx,
round(abs(Debit - Credit),@Decimals) as TxAmount,
round((select sum(fAmount) from _etblAllocsTemp where iFromRecID = P.AutoIdx),@Decimals) as [Allocation Amount],
round(round((select sum(fAmount) from _etblAllocsTemp where iFromRecID = P.AutoIdx),@Decimals) -  round(abs(Debit - Credit),@Decimals),@Decimals) as [Over Allocation],
C.Account,
C.Name
from PostAR P
left outer join Client C on P.AccountLink = C.DCLink
where 
	round(abs(Debit - Credit),@Decimals) < round((select sum(fAmount) from _etblAllocsTemp where iFromRecID = P.AutoIdx), @Decimals) 
	--AND Account = @sup		
order by Account


    Step-by-step guide to Solve overallocations:

  3. Replace the customercode in the top part.

    --SET THE CLIENT:
DECLARE @Sup varchar(50)
SELECT  @Sup = 'UFL001'


TRUNCATE TABLE _etblAllocsDCLinkRangeTemp
	insert into _etblAllocsDCLinkRangeTemp (DCLink)
			select DCLink from Client where account = @Sup

exec _espAllocsToTableARAP 'AR', '2099-12-31', 1, 0, null, null


UPDATE A
	SET A.Outstanding = b.CalcOutstanding

FROM  POSTAR A 
LEFT JOIN (
SELECT * from (
SELECT 

	AutoIdx,
	DMC =  Debit-credit,
	CalcOutstanding = Debit-credit,
	ActualOutstanding = outstanding
 from postAR where cAllocs is null) X where ROUND(CalcOutstanding,2) <> ROUND(ActualOutstanding,2)) B on  A.AutoIDX = B.AutoIdx
 where B.AutoIdx is not null
--


UPDATE A
	SET A.Outstanding =  Calculated.newOutstanding


FROM PostAR A 
LEFT JOIN (
SELECT *,
	newOutstanding = outstandingAR + DifferenceAlloc
	FROM (
SELECT 
	C.Account
	,X2.*
	,DifferenceAlloc = AllocatedAmount - OutstandingAR
	 from (
	SELECT 
		 *
		, CASE WHEN FROMDMC < 0 THEN FROMDMC +Amount ELSE FROmDMC - Amount END as AllocatedAmount
		, outstandingAR = (SELECT outstanding from postar where AUTOIDx = X1.AUTOIDx)
	 from(
		SELECT 
		iAccountID
		,AutoIdx
		,FROMDMC
		,SUM(fAmount) Amount

		--B--alanceRemaining = ROUNd(Case when FromDMC > 0 Then  FromDMC-fAmount else FromDMC +famount END,2)
		from (
				 Select
					A1.iAccountID
					,  A1.iFromRecID  as AutoIdx
					, A1.iToRecID as [Alocated To AutoIdx]
					, FromDMC = (Select debit-credit from  postar where autoidx = A1.iFromRecID)
					, ToDMC = (Select debit-credit from  postar where autoidx = A1.iToRecID)
					, A1.fAmount
					 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  c.dclink  in (SELECT DCLink FROM Client WHERE Account = @Sup)
				)
				X
			group by
			 AutoIdx
			,iAccountID
			,FROMDMC) x1 

	WHERE 
		ROUND(CASE 
				WHEN FROMDMC < 0 THEN FROMDMC +Amount ELSE FROmDMC - Amount END,2) <> 0
	) X2
LEFT JOIN CLIENT C on C.DCLINK = IaccountID
		WHERE  ROUND(AllocatedAmount,2) <> RounD(OutstandingAR,2)
--ORder by  C.Account ASC
) X ) Calculated on Calculated.AutoIdx = A.AutoIdx
where Calculated .AutoIdx is not null



----

Declare @Decimals int Set @Decimals = (select top 1 Decimals from StDfTbl) 

Select 
P.AutoIdx,
round(abs(Debit - Credit),@Decimals) as TxAmount,
round((select sum(fAmount) from _etblAllocsTemp where iFromRecID = P.AutoIdx),@Decimals) as [Allocation Amount],
round(round((select sum(fAmount) from _etblAllocsTemp where iFromRecID = P.AutoIdx),@Decimals) -  round(abs(Debit - Credit),@Decimals),@Decimals) as [Over Allocation],
C.Account,
C.Name
from PostAR P
left outer join Client C on P.AccountLink = C.DCLink
where 
	round(abs(Debit - Credit),@Decimals) < round((select sum(fAmount) from _etblAllocsTemp where iFromRecID = P.AutoIdx), @Decimals) 
	AND Account = @sup		
order by Account
  4. Run the sql for the customer.
  5. You will see the transaction that is overallocated.
  6. go into sage → allocations → Find the line → Unallocate 1 transactions from the overallocation.
  7. save
  8. close
  9. Run the script again. This will solve the overallocation. (you should have no results now)
  10. Go back into the allocations.
  11. Allocate the line you just unallocated again to the same original transaction.
  12. Save
  13. run age analysis (far in future)
  14. Check if the result equals the sql result : Select sum(debit-credit) postar where accountlink = XX