Allocation Rounding issues on foreign currency AR/AP accounts

Problem

Sage Evolution sometimes processes transactions with more than 2 decimals behind the comma. This results in rounding issues on the allocation side, and as a result also on a difference on the age analysis. This article describes how to solve this.

Solution

Before continuing, always make a backup first.

Analysis of transactions

First identify the transactions that have more than 2 decimals

Accounts payable:

Analysis of transactions

select * from PostAP P
	LEFT JOIN Vendor V ON P.AccountLink = V.DCLink
	WHERE 
		--V.Account = '<<ACCOUNTNAME HERE>>' AND 
		(CAST(Debit as decimal(14,4))-CAST(Credit as decimal(14,4))) <> ROUND(CAST(Debit as decimal(14,4))-CAST(Credit as decimal(14,4)),2)


--Check PostGL:
SELECT *
 FROM PostGL PG
	WHERE PG.cAuditNumber IN (select cAuditNumber from PostAP P
	LEFT JOIN Vendor V ON P.AccountLink = V.DCLink
	WHERE 
		V.Account = '<<ACCOUNTNAME HERE>>' AND 
		(CAST(Debit as decimal(18,4))-CAST(Credit as decimal(14,4))) <> ROUND(CAST(Debit as decimal(14,4))-CAST(Credit as decimal(14,4)),2)
)

Accounts receivable:

Analysis of transactions

select * from PostAR P
	LEFT JOIN Client C ON P.AccountLink = C.DCLink
	WHERE 
		--C.Account = '<<ACCOUNT CODE HERE>>' AND 
		(CAST(P.Debit as decimal(14,4))-CAST(P.Credit as decimal(14,4))) <> ROUND(CAST(P.Debit as decimal(14,4))-CAST(P.Credit as decimal(14,4)),2)


--Check PostGL:
SELECT *
 FROM PostGL PG
	WHERE PG.cAuditNumber IN (select P.cAuditNumber from PostAR P
	LEFT JOIN Client C ON P.AccountLink = C.DCLink
	WHERE 
		C.Account = '<<ACCOUNT CODE HERE>>' AND 
		(CAST(P.Debit as decimal(18,4))-CAST(P.Credit as decimal(14,4))) <> ROUND(CAST(P.Debit as decimal(14,4))-CAST(P.Credit as decimal(14,4)),2)
)

Export the transactions to CSV/Excel for reference.

Fix the transactions

Accounts payable:

Analysis of transactions

--Update PostGL: (this will solve rounding issues on balance sheet.
UPDATE PostGL 
	SET Debit = CAST(Debit as decimal(18,2))
		,Credit = CAST(Credit as decimal(18,2))
 FROM PostGL PG
	WHERE PG.cAuditNumber IN (select cAuditNumber from PostAP P
				LEFT JOIN Vendor V ON P.AccountLink = V.DCLink
				WHERE 
					V.Account = '<<ACCOUNT CODE HERE>>' AND 
					(CAST(Debit as decimal(18,4))-CAST(Credit as decimal(14,4))) <> ROUND(CAST(Debit as decimal(14,4))-CAST(Credit as decimal(14,4)),2)
			)
	
	
--Update PostAP:
UPDATE PostAP
	SET Debit = CAST(Debit as decimal(18,2))
		,Credit = CAST(Credit as decimal(18,2))
	FROM 
		PostAP P
		LEFT JOIN Vendor V ON P.AccountLink = V.DCLink
		WHERE 
			V.Account = '<<ACCOUNT CODE HERE>>' AND 
			(CAST(Debit as decimal(14,4))-CAST(Credit as decimal(14,4))) <> ROUND(CAST(Debit as decimal(14,4))-CAST(Credit as decimal(14,4)),2)

Accounts receivable:

Analysis of transactions

--Update PostGL: (this will solve rounding issues on balance sheet.
UPDATE PostGL 
	SET Debit = CAST(Debit as decimal(18,2))
		,Credit = CAST(Credit as decimal(18,2))
 FROM PostGL PG
	WHERE PG.cAuditNumber IN (select P.cAuditNumber from PostAR P
				LEFT JOIN Client C ON P.AccountLink = C.DCLink
				WHERE 
					C.Account = '<<ACCOUNT CODE HERE>>' AND 
					(CAST(P.Debit as decimal(18,4))-CAST(P.Credit as decimal(14,4))) <> ROUND(CAST(P.Debit as decimal(14,4))-CAST(P.Credit as decimal(14,4)),2)
			)
	
	
--Update PostAR:
UPDATE PostAR
	SET Debit = CAST(Debit as decimal(18,2))
		,Credit = CAST(Credit as decimal(18,2))
	FROM 
		PostAR P
		LEFT JOIN Client C ON P.AccountLink = C.DCLink
		WHERE 
			C.Account = '<<ACCOUNT CODE HERE>>' AND 
			(CAST(Debit as decimal(14,4))-CAST(Credit as decimal(14,4))) <> ROUND(CAST(Debit as decimal(14,4))-CAST(Credit as decimal(14,4)),2)

Verify Allocations Integrity

Verify Allocations Integrity

Use the following query to verify the allocations.

This stored proc / query identifies the allocations which are not matching.

Check query

AP Allocs check query

DECLARE @CheckDate date = '2022-05-31'
DECLARE @VendorCode nvarchar(100) = 'CA - Oxfam Novib HQ'


select 
	alloc.*
	,(ap.Debit - ap.Credit ) as txamount
	,ROUND(abs(ap.Debit - ap.Credit ) - alloc.AllocAmount,2) as OutstandingCalc
	,round(ap.outstanding,2) as Outstanding
	,ROUND(abs(ap.Debit - ap.Credit ) - alloc.AllocAmount,2) - round(ap.outstanding,2) as DifCalc
	
	--, ap.* 
	
	
	from  PostAP ap 
		left join Vendor v on V.Dclink = ap.accountlink
		left join (select SUM(a.fAmount) AllocAmount, ap.autoidx from postap ap 
						left join Vendor v on V.Dclink = ap.accountlink
						outer apply [dbo].[_efnAllocsSplit] (ap.callocs) a
						--where v.account = @VendorCode
						--where ap.txdate <= @CheckDate
						where ap.callocs is not null 
						--and ap.AutoIdx <> 22990    ---- LANGE CALLOCS
						group by ap.autoidx) Alloc on alloc.autoidx = ap.autoidx

--where v.account = @VendorCode
--where ap.TxDate <= @CheckDate

where ABS(ROUND(abs(ap.Debit - ap.Credit ) - alloc.AllocAmount,2) - round(ap.outstanding,2)) > 0.1

Query:

Verify Allocations

--exec _AS_SP_AllocRecalculation 'AP', 0


-- =============================================
-- Author:  Jan van der Hilst
-- Company:  Asamco B.V - Camelsa CCG
-- Create date: 09/05/2017
-- =============================================
CREATE PROCEDURE [dbo]._AS_SP_AllocRecalculation
 @ARAP varchar(2),
 @CorrectErrors bit

AS
BEGIN
--USAGE:
--exec _AS_SP_AllocRecalculation 'AP', 0

If NOT Exists(Select [Name] from sysobjects where xType = 'U' and [Name] = '#AllocData') 
 BEGIN
  CREATE Table #AllocData (
    DCLink int
   ,iToRecID Int
   ,SumAllocs float
  )
 END

If NOT Exists(Select [Name] from sysobjects where xType = 'U' and [Name] = '#ActionDone') 
BEGIN
 CREATE Table #ActionDone (
   AutoIDx int
  ,Debit float
  ,Credit float
  ,DMC float
  ,Outstanding float
  ,SumAllocs float
  ,CorrectCalculatedOutstanding float
  ,Action varchar(30)
 )
END

 TRUNCATE TABLE #AllocData
 TRUNCATE TABLE #ActionDone
 TRUNCATE TABLE _etblAllocsDCLinkRangeTemp


IF @ARAP = 'AR' BEGIN
 

 --Get The max Date
 DECLARE @MaxTxDateAR Date
 SELECT @MaxTxDateAR =  MAX(TxDate) from postar 

 --SELECT * from _efnAgedPostAR(@MaxTxDateAR,NULL) 

 --Select 
 -- PostAROutstanding = AR.outstanding,
 -- CalculatedOutstanding = AgedAR.CalculatedOutStanding
 
 --from _efnAgedPostAR(@MaxTxDateAR,NULL) AgedAR
 --LEFT JOIN PostAR AR on AR.AutoIdx = AgedAR.AutoIdx
 -- WHERE AR.outstanding <> AgedAR.CalculatedOutStanding
  
  --if correct errors, ONLY home currency accounts!
 IF @CorrectErrors = 1
 BEGIN
insert into _etblAllocsDCLinkRangeTemp (DCLink)
   (Select DClink from Client where bforcurAcc =0) 
 END
 ELSE
 BEGIN
 insert into _etblAllocsDCLinkRangeTemp (DCLink)
   (Select DClink from Client ) --where bforcurAcc =0)
 END
 
 INSERT INTO #AllocData (DCLink,iToRecID,SumAllocs)
 (Select 
  Allocations.iAccountID
 ,Allocations.iToRecID
 ,SUM(Allocations.fAmount) SumAllocs 
  from  [dbo].[_efnAllocsARAP] ('AR',@MaxTxDateAR,1,0,NULL,NULL) Allocations
  GROUP BY Allocations.iAccountID,Allocations.iToRecID)


   --Information table:
  INSERT INTO #ActionDone 
  (
   AutoIDx 
  ,Debit 
  ,Credit 
  ,DMC
  ,Outstanding
  ,SumAllocs
  ,CorrectCalculatedOutstanding
  ,Action
 )
 SELECT
   ARJ.AutoIDx
  ,ARJ.Debit
  ,ARJ.Credit
  ,ARJ.DMC
  ,ARJ.Outstanding
  ,ARJ.SumAllocs
  ,CASE WHEN ARJ.DMC > 0 
   THEN
    (ROUND(ARJ.DMC,2) - ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
   ELSE    
    (ROUND(ARJ.DMC,2) + ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
   END
  as CorrectCalculatedOutstanding
  ,CASE WHEN SumAllocs > DMC THEN
   'Please fix manually'
   ELSE
   'Updated by this query'
  End as ActionDone
 FROM
  (SELECT
    alloc.DCLink
    ,AR.AutoIdx
    ,AR.Debit
    ,AR.Credit
    ,AR.Debit-AR.Credit AS DMC
    ,AR.Outstanding
    ,AD.SumAllocs 
    --,Case when AR.Debit > 0 THEN AD.SumAllocs ELSE -AD.SumAllocs END AS SumAllocs2  
   from _etblAllocsDCLinkRangeTemp alloc
   LEFT JOIN PostAR AR on AR.Accountlink =  alloc.DcLink 
   LEFT JOIN #AllocData AD on AD.iToRecID = AR.AutoIdx
    WHERE AR.cAllocs IS NOT NULL) ARJ
   where 
    ROUND(
       (ROUND(ARJ.Outstanding,2)
        -
       (CASE WHEN ARJ.DMC > 0 
        THEN
         (ROUND(ARJ.DMC,2) - ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
        ELSE    
         (ROUND(ARJ.DMC,2) + ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
        END
       )),2)
     <> 0

 
 IF @CorrectErrors =1 
  BEGIN
   UPDATE AR
    set Outstanding = #ActionDone.CorrectCalculatedOutstanding
   FROM postAR AR
   INNER JOIN #ActionDone on AR.AutoIdx = #ActionDone.AutoIDx
   --WHERE Action = 'Updated by this query'

  END 

 Select   
   #ActionDone.AutoIDx 
  ,Client.Account
  ,#ActionDone.Debit 
  ,#ActionDone.Credit 
  ,#ActionDone.DMC
  ,#ActionDone.Outstanding
  ,#ActionDone.SumAllocs
  ,#ActionDone.CorrectCalculatedOutstanding
  ,#ActionDone.Action
  FROM #ActionDone
  LEFT JOIN postAR on #ActionDone.AutoIDx = postAR.AutoIdx
  LEFT JOIN Client on Client.DCLink = postAR.AccountLink

END --IF @ARAP = 'AR' BEGIN
IF @ARAP = 'AP' BEGIN
  
 --Get The max Date
 DECLARE @MaxTxDateAP Date
 SELECT @MaxTxDateAP =  MAX(TxDate) from postap 


 --if correct errors, ONLY home currency accounts!
 IF @CorrectErrors = 1
 BEGIN
insert into _etblAllocsDCLinkRangeTemp (DCLink)
   (Select DClink from Vendor where bforcurAcc =0) 
 END
 ELSE
 BEGIN
 insert into _etblAllocsDCLinkRangeTemp (DCLink)
   (Select DClink from Vendor ) --where bforcurAcc =0)
 END

 
 INSERT INTO #AllocData (DCLink,iToRecID,SumAllocs)
 (Select 
  Allocations.iAccountID
 ,Allocations.iToRecID
 ,SUM(Allocations.fAmount) SumAllocs 
  from  [dbo].[_efnAllocsARAP] ('AP',@MaxTxDateAP,1,0,NULL,NULL) Allocations
  GROUP BY Allocations.iAccountID,Allocations.iToRecID)

  --Information table:
  INSERT INTO #ActionDone 
  (
   AutoIDx 
  ,Debit 
  ,Credit 
  ,DMC
  ,Outstanding
  ,SumAllocs
  ,CorrectCalculatedOutstanding
  ,Action
 )
 SELECT
   ARJ.AutoIDx
  ,ARJ.Debit
  ,ARJ.Credit
  ,ARJ.DMC
  ,ARJ.Outstanding
  ,ARJ.SumAllocs
  ,CASE WHEN ARJ.DMC > 0 
   THEN
    -(ROUND(ARJ.DMC,2) - ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
   ELSE
    (-ROUND(ARJ.DMC,2) - ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
   END
  as CorrectCalculatedOutstanding
  ,CASE WHEN SumAllocs > DMC THEN
   'Please fix manually'
   ELSE
   'Updated by this query'
  End as ActionDone
 FROM
  (SELECT
    alloc.DCLink
    ,AP.AutoIdx
    ,AP.Debit
    ,AP.Credit
    ,AP.Debit-AP.Credit AS DMC
    ,AP.Outstanding
    ,AD.SumAllocs 
    --,Case when AR.Debit > 0 THEN AD.SumAllocs ELSE -AD.SumAllocs END AS SumAllocs2  
   from _etblAllocsDCLinkRangeTemp alloc
   LEFT JOIN PostAP AP on AP.Accountlink =  alloc.DcLink 
   LEFT JOIN #AllocData AD on AD.iToRecID = AP.AutoIdx
    WHERE AP.cAllocs IS NOT NULL) ARJ
   where 
      ROUND(
       (ROUND(ARJ.Outstanding,2)
        -
       (CASE WHEN ARJ.DMC > 0 
        THEN
         -(ROUND(ARJ.DMC,2) - ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
        ELSE
         (-ROUND(ARJ.DMC,2) - ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
        END 
       )),2)
     
     <> 0

 IF @CorrectErrors =1 
  BEGIN
   UPDATE AP
    set Outstanding = #ActionDone.CorrectCalculatedOutstanding
   FROM postAP AP
   INNER JOIN #ActionDone on AP.AutoIdx = #ActionDone.AutoIDx
   --WHERE Action = 'Updated by this query'
  END

 Select   
   #ActionDone.AutoIDx 
  ,Vendor.Account
  ,#ActionDone.Debit 
  ,#ActionDone.Credit 
  ,#ActionDone.DMC
  ,#ActionDone.Outstanding
  ,#ActionDone.SumAllocs
  ,#ActionDone.CorrectCalculatedOutstanding
  ,#ActionDone.Action
  FROM #ActionDone
  LEFT JOIN postAP on #ActionDone.AutoIDx = postAP.AutoIdx
  LEFT JOIN Vendor on Vendor.DCLink = postAP.AccountLink
END --IF @ARAP = 'AP' BEGIN

--DROPPING TABLES:
DROP TABLE #ActionDone
DROP TABLE #AllocData
END

Space shortcuts

Page tree

Problem

Solution

Verify Allocations Integrity

Check query

Query:

Related articles

Related articles