ApplicationXtender - Annotations Naming Convention

/****** Object:  UserDefinedFunction [dbo].[fn_objidToAnnoid]    Script Date: 8/10/2021 11:18:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_objidToAnnoid]
(
@val as BigInt,
@appname as VarChar(255)
)
returns varchar(max)
as
Begin
 
 DECLARE @base as int
 DECLARE @subpagenum as VarChar(3)
 DECLARE @pathid as TinyInt
 DECLARE @annopathid as TinyInt

 SET @subpagenum = ''
 SET @base = 32
 
  /* Check if value is valid and if we get a valid base (2 through 36) */
  If (@val<0) Return Null;
 
  /* variable to hold final answer */
  Declare @answer as varchar(63);
 
  /* Following variable contains all 
     possible alpha numeric letters for any valid base 
  */
  Declare @alldigits as varchar(36);
  Set @alldigits='0123456789abcdefghijklmnopqrstuvwxyz'
 
  /* Set the initial value of 
     final answer as empty string 
  */
  Set @answer='';
 
  DECLARE @oval AS BigInt
  
  SET @oval = @val
 
  /* Loop while the source value remains greater than 0 */
  While @val>0
  Begin
    Set @answer=Substring(@alldigits,@val % @base + 1,1) + @answer;
    Set @val = @val / @base;
  End
 
  DECLARE @path VARCHAR(3)
  
  SET @path = ''
  SET @pathid = (SELECT pathid from ae_apps where appname = @appname)
  SET @annopathid = (SELECT annpath from ae_apps where appname = @appname)
    
  While @pathid > 0
  Begin
    Set @path = Substring(@alldigits, @pathid % @base + 1,1) + @path;
    Set @pathid = @pathid / @base;
    
  End
  /* Return the final answer */
  DECLARE @annopath AS VARCHAR(MAX)
  
  SELECT @annopath = dbo.ae_paths.path FROM dbo.ae_paths WHERE pathid = @annopathid
  
  DECLARE @p1 AS VARCHAR(4)
  DECLARE @p2 AS VARCHAR(4)
  
  SET @p1 = CONVERT(VARCHAR, (@oval / 1024) / 1024)
  SET @p2 = CONVERT(VARCHAR, (@oval / 1024) % 1024)
  
  DECLARE @extension CHAR(4)
  DECLARE @ans VARCHAR(4)

  --SELECT @extension = CASE WHEN CONVERT(TINYINT, @subpagenum) < 1 THEN '.ano' ELSE '.' + REPLICATE('0', 3 - LEN(@subpagenum)) + @subpagenum END
  SET @oval = @subpagenum
  SET @ans = ''

  /* Loop while the source value remains greater than 0 */
  While @oval>0
  Begin
    Set @ans = Substring(@alldigits, @oval % @base + 1,1) + @ans;
    Set @oval = @oval / @base;
  End
  
  SELECT @extension = CASE WHEN CONVERT(TINYINT, @subpagenum) < 1 THEN '.ano' ELSE '.' + REPLICATE('0', 3 - LEN(@ans)) + @ans END

  RETURN @annopath + '\' + @appname + '\' + @p1 + '\' + @p2 + '\' + REPLICATE('0', 2 - LEN(@path)) + @path + REPLICATE('0', 6 - LEN(@answer)) + @answer + @extension;
End

SELECT dbo.fn_objidToAnnoid (objectid, 'Appname')
-- enter the objectid and the application name from AX to get the path to the annotation.

--@base can be anything from 2 to 36. This work is licensed under a Creative Commons Attribution 2.5 License.
CREATE FUNCTION dbo.f_convert_to_base10
  (@string VARCHAR(255), @base TINYINT)
RETURNS INT AS 
BEGIN
  DECLARE @return INT
  DECLARE @len INT
  DECLARE @finished BIT
  DECLARE @pos INT
  DECLARE @thischar CHAR(1)
  DECLARE @thisasc INT
  DECLARE @val INT
  
  SELECT @base     = 32 --CASE WHEN @base < 2 OR @base IS NULL THEN 2 WHEN @base > 36 THEN 36 ELSE @base END
  SELECT @return   = 0
  SELECT @finished = 0
  SELECT @string   = UPPER(@string)
  SELECT @len      = DATALENGTH(@string)
  -- Failsafe
  IF @len = 0
     SELECT @finished = 1
  -- Loop over all characters: capitalize first character and those after spaces, replace underscores with spaces
  SELECT @pos = 0
  WHILE @finished = 0
  BEGIN
    SELECT @pos = @pos + 1
    IF @pos > @len
       -- If we've run out of characters, we're done
       SELECT @finished = 1
    ELSE
    BEGIN
       -- Get the character (from right to left)
       SELECT @thischar = SUBSTRING(@string, (@len - (@pos - 1)), 1)
       -- Get the character's ASCII value
       SELECT @thisasc  = ASCII(@thischar)
       -- Convert to a numerical value
       SELECT @val = CASE
                       WHEN @thisasc BETWEEN 48 AND 57 -- '0' AND '9'
                         THEN @thisasc - 48
                       WHEN @thisasc BETWEEN 65 AND 90 -- 'A' (= decimal 10) AND 'Z'
                         THEN @thisasc - 65 + 10
                       ELSE 0 END
       -- Add this portion on
       SELECT @return = @return + (POWER(@base, (@pos - 1)) * @val)
    END
  END
  -- Done
  RETURN @return
END
GO

--Example of usage:
SELECT dbo.f_convert_to_base10(annofilename, '32') AS Objectid

SELECT dbo.f_convert_to_base10('0000uo', ‘32’) as Objectid