KB20230606/01: Sage 200 Evolution Tip & Trick – Company upgrade error “Error refreshing all functions! Could not find the object ‘_efntu TaxAmount’ or you do not have permission”.

What to do when you get the Sage 200 Evolution company upgrade error “Error refreshing all functions! Could not find the object ‘_efntuTaxAmount’ or you do not have permission“

This error may especially occur when upgrading from Sage 200 Evolution version 9.20 to version 10 using SQL2014.

Please note – It is recommended that this query should preferably be fixed by a professional Sage 200 Evolution support consultant who is familiar on how to compile SQL queries, to correct the problem.

Apply the following steps to resolve this query:

1. Restore the backup that was made before the upgrade was started.

2. Drop the company database functions (as listed below) by separately running the following SQL scripts:

drop function _efntuAmountExcl

drop function _efntuAmountIncl

drop function _efntuDiscAmount

drop function _efntuOldExclToNewExcl

drop function _efntuOldInclToNewIncl

drop function _efntuPaymentDiscAmount

drop function _efntuRoundToNearest

drop function _efntuTaxAmount

3. Upgrade the company as per the normal procedure which should now run fine.

4. Once the upgrade has been completed the above Functions need to be re-created by running the various SQL scripts below.

a). Recreating the  _efntuAmountExcl function

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[_efntuAmountExcl](@AmountIncl float, @TaxRate float, @Decs int=2)
 
returns float
 
with schemabinding
 
as
 
begin
 
  return (round(@AmountIncl / (1 + (@TaxRate / 100)), @Decs))
 
end
 
GO

b). Recreating the _efntuAmountIncl function

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[_efntuAmountIncl](@AmountExcl float, @TaxRate float, @Decs int=2)
 
returns float
 
with schemabinding
 
as
 
begin
 
  return (round(@AmountExcl * (1 + (@TaxRate / 100)), @Decs))
 
end
 
GO

c). Recreating the _efntuPaymentDiscAmount function

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[_efntuPaymentDiscAmount](@Amount float, @DiscPerc float)
 
returns float
 
with schemabinding
 
as
 
begin
 
return (case when @DiscPerc < 100 then ((@DiscPerc / 100) * (@Amount) / (1 - (@DiscPerc/100))) else @Amount end)
 
end
	 
GO

d). Recreating the _efntuDiscAmount function

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[_efntuDiscAmount](@Amount float, @DiscPerc float, @Decs int=2)
 
returns float
 
with schemabinding
 
as
 
begin
 
  return (round(((@DiscPerc / 100) * @Amount), @Decs))
 
end
 
GO

e) Recreating the _efntuOldExclToNewExcl function

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
create function [dbo].[_efntuOldExclToNewExcl](@ExclAmount float, @OldTaxRate float, @NewTaxRate float, @Decs int = 2)
 
returns float
 
as
 
begin
 
  return (round((@ExclAmount * (1 + (@OldTaxRate/100))) / (1 + (@NewTaxRate/100)), @Decs))
 
end
 
GO

f) Recreating the _efntuOldInclToNewIncl function

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
create function [dbo].[_efntuOldInclToNewIncl](@InclAmount float, @OldTaxRate float, @NewTaxRate float, @Decs int = 2)
 
returns float
 
as
 
begin
 
  return (round((@InclAmount / (1 + (@OldTaxRate/100))) * (1 + (@NewTaxRate/100)), @Decs))
 
end
 
GO

g) Recreating the _efntuRoundToNearest function

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[_efntuRoundToNearest](@Amount float, @RoundDirection int, @Denominator Float)
 
returns float
 
as
 
begin
 
  declare @TruncVal float
 
  declare @DecimalVal float
 
  declare @I int
 
  declare @J int
 
  if (@Denominator <> 0) and (@RoundDirection > 0) begin
 
    set @TruncVal = floor(@Amount);
 
    set @DecimalVal = (@Amount*100 - @TruncVal*100)/100
 
    set @I = (@DecimalVal*100)
 
    set @J = (@Denominator*100);
 
    if (select @I % @J) = 0
 
      set @Amount = @TruncVal + @DecimalVal
 
    else begin
 
      if @RoundDirection = 1
 
        set @Amount = @TruncVal + (floor(@DecimalVal/@Denominator) * @Denominator) + @Denominator
 
      else if @RoundDirection = 2
 
        set @Amount = @TruncVal + (floor(@DecimalVal/@Denominator) * @Denominator)
 
    end
 
  end
 
  return @Amount
 
end
 
GO

h) Recreating the _efntuTaxAmount function

CREATE FUNCTION [dbo].[_efntuTaxAmount](@Amount float, @TaxRate float, @Decs int=2)
 
returns float
 
with schemabinding
 
as
 
begin
 
  return (round(@Amount - (@Amount / (1 + (@TaxRate / 100))), 2))
 
end
GO