CREATE OR REPLACE PACKAGE util_numeric AS
  /*
  ** (c) Bond and Pollard Ltd 2022
  ** This software is free to use and modify at your own risk.
  ** 
  ** Module Name   : util_numeric
  ** Description   : Number handling utilities
  ** 
  **------------------------------------------------------------------------
  ** Modification History
  **  
  ** Date             Name                Description
  **------------------------------------------------------------------------
  ** 16/06/2022       Ian Bond            Program created
  ** 12/02/2024       Ian Bond            Add AI generated function num_to_alphanumeric
  **                                      to convert integer to alphanumeric code.
  ** 16/03/2024       Ian Bond            Add function to calculate pi   
  ** 25/09/2025       Ian Bond            Ensure hex numbers are uppercase. 
  **                                      Validate input to alphatodec, input string must
  **                                      only contain characters A to Z.
  **                                      Amend base conversion to handle -ve numbers.
  ** 27/09/2025       Ian Bond            Improve exception handling WHEN OTHERS.  
  **                                      Fix handling of NULL and 0 in base/hex conversion.
  */
 
  /*  
  ** Global constants
  */
  gc_error     CONSTANT plsql_constants.severity_error%TYPE := plsql_constants.severity_error;
  gc_info      CONSTANT plsql_constants.severity_info%TYPE  := plsql_constants.severity_info;
  gc_warn      CONSTANT plsql_constants.severity_warn%TYPE  := plsql_constants.severity_warn;


  
  /*
  ** Global variables
  */

  
  /*
  ** Global exceptions
  */
  
  e_invalid_data EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_invalid_data,-20001);
  
  e_invalid_base EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_invalid_base,-20002);
  
  e_invalid_alpha_range EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_invalid_alpha_range,-20003);


  /*
  ** Public functions and procedures
  */
  
   
  /*
  ** dectobase - Convert a decimal integer to the specified base value
  **
  ** IN
  **   p_number        - Decimal integer to be converted
  **   p_base          - Integer representing number base, e.g. 2 is Binary, 8 is octal, 
  **                     16 is hexadecimal
  ** RETURN
  **   VARCHAR2   A string representing the base value of the decimal
  ** EXCEPTIONS
  **   e_invalid_data      - Log error if number base passed not in range 1 to 16.
  */
  FUNCTION dectobase (
    p_number IN INTEGER, 
    p_base   IN INTEGER
  ) RETURN VARCHAR2;

  /*
  ** basetodec - Convert a number of the specified base to a decimal value
  **
  ** IN
  **   p_number        - A string containing the base value to be converted to decimal.
  **                     e.g. '10' is the binary string representing 2 in base 10.
  **   p_base          - Integer representing number base, e.g. 2 is Binary, 8 is octal, 
  **                     16 is hexadecimal
  ** RETURN
  **   NUMBER  Is the decimal value of the specified base number
  ** EXCEPTIONS
  **   e_invalid_data      - Log error if number passed contains characters other than 0 - 9, A - Z.
  **   e_invalid_base      - Log error if number base passed not in range 1 to 16.
  */
  FUNCTION basetodec (
    p_number IN VARCHAR2, 
    p_base   IN INTEGER
  ) RETURN NUMBER;

  /*
  ** dectohex - Convert a decimal integer to a hexadecimal string value
  **
  ** IN
  **   p_number        - A decimal integer value to be converted to Hexadecimal
  ** RETURN
  **   VARCHAR2   A string containing the Hexadecimal value of the decimal integer
  ** EXCEPTIONS
  **   <exception_name1>      - <brief description>
  */
  FUNCTION dectohex (
    p_number IN INTEGER
  ) RETURN VARCHAR2;

  /*
  ** hextodec - Convert a hexadecimal string value to a decimal integer
  **
  ** IN
  **   p_number        - Hexadecimal string to be converted to decimal
  ** RETURN
  **   NUMBER  The decimal value of the Hexadecimal number
  ** EXCEPTIONS
  **   e_invalid_data      - Log error if number passed contains characters other than 0 - 9, A - Z.
  */
  FUNCTION hextodec (
    p_number IN VARCHAR2
  ) RETURN NUMBER;

  /*
  ** factorial - Calculate the factorial for a positive integer
  **
  ** IN
  **   p_number        - Positive integer
  ** RETURN
  **   NUMBER  Factorial of p_number
  ** EXCEPTIONS
  **   e_invalid_data      - Log error if number passed contains characters other than 0 - 9, A - Z.
  */
  FUNCTION factorial(
    p_number IN INTEGER
  ) RETURN NUMBER;

  /*
  ** factorialr - Calculate the factorial for a positive integer
  **
  ** Calculate the factorial using a recursive function.
  ** IN
  **   p_number        - Positive integer
  ** RETURN
  **   NUMBER  Factorial of p_number
  ** EXCEPTIONS
  **   <exception_name1>      - <brief description>
  */
  FUNCTION factorialr (
    p_number IN INTEGER
  ) RETURN NUMBER;

  /*
  ** sort_numbers - Sort a list of numbers
  **
  ** 
  ** IN
  **   p_string       - String of numbers to sort, separated by commas
  **   p_order        - Sort sequence 'A' for Ascending, all other values Descending
  ** RETURN
  **   VARCHAR2  String containing the sorted list of numbers
  ** EXCEPTIONS
  **   <exception_name1>      - <brief description>
  */
  FUNCTION sort_numbers (
    p_string IN VARCHAR2, 
    p_order  IN VARCHAR2
  ) RETURN VARCHAR2;
  
  /*
  ** num_to_alphanumeric - Convert integer to alphanumeric code
  **
  ** A copilot AI generated pl/sql function to convert numbers to an alphanumeric code where:
  ** 1=A, 2=B, 26=Z, 27=AA, 28=AB, 52=AZ, 53=BA etc.
  **
  ** Here’s how the function works:
  ** We start with the input number.
  ** In each iteration, we calculate the remainder after dividing by 26 (the number of letters in the alphabet).
  ** We convert the remainder to the corresponding letter (‘A’ for 1, ‘B’ for 2, and so on).
  ** We prepend the letter to the result string.
  ** We update the input number by subtracting the remainder and dividing by 26.
  ** Repeat until the input number becomes zero.
  ** Now you can use this function to convert numbers to the desired alphanumeric code. For example:
  **
  ** NUM_TO_ALPHANUMERIC(1) returns 'A'.
  ** NUM_TO_ALPHANUMERIC(27) returns 'AA'.
  ** NUM_TO_ALPHANUMERIC(52) returns 'AZ'.
  ** NUM_TO_ALPHANUMERIC(53) returns 'BA'.
  ** 
  ** IN
  **   p_number       - Positive integer to convert
  ** RETURN
  **   VARCHAR2  String containing the alphanumeric code
  ** EXCEPTIONS
  **   <exception_name1>      - <brief description>
  */
  FUNCTION num_to_alphanumeric (
    p_number IN NUMBER
  ) 
  RETURN VARCHAR2;

  /*
  ** dectoalpha - Convert a decimal value to an alphabetic code
  **
  ** Convert a positive integer into an alphabetic code, using the specified range of letters. 
  ** Use an efficient calculation instead of a simple but highly inefficient loop. 
  ** 
  ** e.g.
  **
  ** 1=A
  ** 2=B
  ** 3=C
  ** 26=Z
  ** 27=AA
  ** 28=AB 
  ** 52=AZ
  ** 53=BA
  ** 700=ZX
  ** 702=ZZ
  ** 703=AAA
  ** 704=AAB
  ** 18278=ZZZ
  ** 18279=AAAA
  ** 72385=DCBA
  ** 475254=ZZZZ
  ** 1143606698788=ELIZABETH
  ** 
  ** IN
  **   p_number      - Positive decimal integer to be converted
  **   p_range       - Number between 1 and 26, representing range of alphabetic characters to use in code.
  **                   e.g. 5 would use letters A to E.
  ** RETURN
  **   VARCHAR2  String containing the alphabetic code
  ** EXCEPTIONS
  **   e_invalid_data      - Log error if number passed < 1
  */
  FUNCTION dectoalpha (
    p_number IN INTEGER, 
    p_range  IN INTEGER
  ) RETURN VARCHAR2;

  /*
  ** alphatodec - Convert an alphabetic code to a decimal integer
  **
  ** Decode an alphabetic code, with the specified range of characters, converting it back to an integer. 
  **
  ** Example alphacodes using all 26 letters of the alphabet:
  ** A=1
  ** B=2
  ** Z=26
  ** AA=27
  ** ZZ=702
  ** AAA=703
  ** 
  ** IN
  **   p_code        - String containing the alphabetic code
  **   p_range       - Number between 1 and 26, representing range of alphabetic characters to use in code.
  **                   e.g. 5 would use letters A to E.
  ** RETURN
  **   NUMBER  Decimal integer value of the alphabetic code
  ** EXCEPTIONS
  **   e_invalid_data      - Log error if alphabetic code passed contains letters outside range, e.g. if range is 5, only A-E allowed.
  */
  FUNCTION alphatodec(
    p_code  IN VARCHAR2, 
    p_range IN INTEGER
  ) RETURN NUMBER;

  /*
  ** pi - Calculate pi to a reasonable accuracy
  **
  ** RETURN
  **   NUMBER  Value of pi
  **
  */
  FUNCTION pi
    RETURN NUMBER;
    
END util_numeric;
/


CREATE OR REPLACE PACKAGE BODY util_numeric AS
  /*
  ** (c) Bond and Pollard Ltd 2022
  ** This software is free to use and modify at your own risk.
  ** 
  ** Module Name   : util_numeric
  ** Description   : Number handling utilities
  ** 
  **------------------------------------------------------------------------
  ** Modification History
  **  
  ** Date            Name                 Description
  **------------------------------------------------------------------------
  ** 16/06/2022       Ian Bond            Program created
  ** 12/02/2024       Ian Bond            Add AI generated function num_to_alphanumeric
  **                                      to convert integer to alphanumeric code.
  ** 25/09/2025       Ian Bond            Ensure hex numbers are uppercase.
  **                                      Validate input to alphatodec, input string must
  **                                      only contain characters A to Z.
  **                                      Amend base conversion to handle -ve numbers.
  ** 27/09/2025       Ian Bond            Improve exception handling for WHEN OTHERS.
  **                                      Fix handling of NULL and 0 in base/hex conversion.
  */


  /*
  ** Private functions and procedures
  */
  
  /*
  ** hex_valid - Check if a string contains a valid hexadecimal value.
  **
  ** IN
  **   p_hex            - String containing hexadecimal value to be checked.
  ** RETURN
  **   BOOLEAN          - TRUE if valid HEX otherwise FALSE
  ** EXCEPTIONS
  **   <exception_name1>      - <brief description>
  */
  FUNCTION hex_valid (
    p_hex    IN VARCHAR2
    ) RETURN BOOLEAN
  IS
    v_valid BOOLEAN := true;
    v_length INTEGER := 0;
    i INTEGER := 0;
    c_valid_chars CONSTANT VARCHAR2(17) :='-0123456789ABCDEF';
  BEGIN
    v_length := length(p_hex);
    FOR i IN 1 .. v_length LOOP
      IF instr(c_valid_chars,substr(p_hex,i,1)) <= 0 THEN
        v_valid := false;
        EXIT;
      END IF;
    END LOOP;
    RETURN v_valid;
  EXCEPTION
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error.', sqlerrm, 'UTIL_NUMERIC.HEX_VALID', 'S', gc_error);
      RETURN false;
  END hex_valid;
  
  /*
  ** base_string_valid - Check if a string contains a valid base value.
  **
  ** IN
  **   p_num_str        - String containing base value to be checked.
  ** RETURN
  **   BOOLEAN          - TRUE if base string valid otherwise FALSE
  ** EXCEPTIONS
  **   <exception_name1>      - <brief description>
  */
  FUNCTION base_string_valid (
    p_num_str  IN VARCHAR2,
    p_base IN INTEGER
    ) RETURN BOOLEAN
  IS
    v_valid BOOLEAN := true;
    v_match BOOLEAN := false;
    c_valid_chars CONSTANT VARCHAR2(16) :='0123456789ABCDEF';
  BEGIN
    /*
      Parse each char in the input string (base number string) from left to right in turn.
      Check to see if each character is found within the list of valid characters for the 
      specified base range.
      If no match is found for any character in the input string, exit and return false.
    */
    FOR i IN 1 .. length(p_num_str) LOOP
      v_match := false;
      FOR j IN 1 .. p_base LOOP
        IF substr(p_num_str,i,1) = substr(c_valid_chars,j,1) THEN   /* compare each char in base number with each valid char in base range */
          v_match := true;
          EXIT;
        END IF;
      END LOOP;
      IF NOT v_match THEN
        v_valid := false;
        EXIT;
      END IF;
    END LOOP;
    RETURN v_valid;
  EXCEPTION
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error.', sqlerrm, 'UTIL_NUMERIC.BASE_STRING_VALID', 'S', gc_error);
      RETURN false;
  END base_string_valid;
  
  /*
  ** alpha_valid - Check if a string contains a valid alphabetic value.
  **
  ** IN
  **   p_hex            - String containing value to be checked.
  ** RETURN
  **   BOOLEAN          - TRUE if string contains only characters A to Z (or char at upper end of range) otherwise FALSE
  ** EXCEPTIONS
  **   e_invalid_alpha_range      - Log error if invalid range of alphabetic characters passed, must be 1 to 26.
  */
  FUNCTION alpha_valid (
    p_alpha_str   IN VARCHAR2,
    p_alpha_range  IN INTEGER
    ) RETURN BOOLEAN
  IS
    v_valid BOOLEAN := true;
    v_testchar VARCHAR2(1);
    v_fromchar VARCHAR2(1);
    v_tochar VARCHAR2(1);
    i INTEGER := 0;
  BEGIN
    /* Check that the range of characters specified is between 1 and 26, per the alphabet */
    IF p_alpha_range < 0 OR p_alpha_range > 26 THEN
      RAISE e_invalid_alpha_range;
    END IF;
    
    /* Set the lower and upper characters for the range of letters used in the alphabetic code.
        e.g. where range is 5 only leters A through E may be used.
    */
    v_fromchar := chr(65); /* 'A' */
    v_tochar := chr(65 + p_alpha_range -1); /* e.g. 'E' if alpha contains a range of 5 characters A to E */
    
    FOR i IN 1 .. length(p_alpha_str) LOOP
      v_testchar := substr(p_alpha_str,i,1);
      IF v_testchar < v_fromchar OR v_testchar > v_tochar THEN
        v_valid := false;
        EXIT;
      END IF;
    END LOOP;
    RETURN v_valid;
  EXCEPTION
    WHEN e_invalid_alpha_range THEN
      util_admin.log_message('Invalid range, must be a number between 1 and 26: ' || to_char(p_alpha_range), sqlerrm, 'UTIL_NUMERIC.ALPHA_VALID', 'S', gc_error);
      RETURN false;
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error.', sqlerrm, 'UTIL_NUMERIC.ALPHA_VALID', 'S', gc_error);
      RETURN false;
  END alpha_valid;
  

  /* 
  ** Public functions and procedures
  */
  

  FUNCTION dectobase(
    p_number IN INTEGER, 
    p_base   IN INTEGER
  ) 
  RETURN VARCHAR2
  IS
    v_result plsql_constants.maxvarchar2_t;
    v_quotient INTEGER;
    v_remainder INTEGER;
    c_digits CONSTANT VARCHAR2(16) :='0123456789ABCDEF';
    v_negative BOOLEAN := false;
    v_sign NUMBER :=1;
    e_zero_value EXCEPTION;
    e_null_value EXCEPTION;
  BEGIN
  
    IF NVL(p_base,0) < 2 OR NVL(p_base,0) > 16 THEN
      RAISE e_invalid_data;
    END IF;
    
    IF p_number IS NULL THEN
      RAISE e_null_value;
    END IF;
    
    IF p_number = 0 THEN
      RAISE e_zero_value;
    END IF;
    
    /* Handle -ve numbers */
    IF nvl(p_number,0) < 0 THEN
      v_sign := -1;
      v_negative := true;
    END IF;
    
    v_quotient := p_number * v_sign; /* strip sign from -ve numbers */
   
    WHILE v_quotient > 0 LOOP
      v_remainder := mod(v_quotient,p_base);
      v_quotient := trunc(v_quotient / p_base);
      v_result := substr(c_digits, v_remainder +1, 1) || v_result;
    END LOOP;
    
    IF v_negative THEN
      v_result := concat('-',v_result); /* Reinstate sign to -ve numbers */
    END IF;
    
    RETURN nvl(v_result,'0');
  EXCEPTION
    WHEN e_null_value THEN
      RETURN NULL;
    WHEN e_zero_value THEN
      RETURN '0';
    WHEN e_invalid_data THEN
      util_admin.log_message('Invalid base number: ' || to_char(p_base) || '. Base must be between 2 and 16.', sqlerrm, 'UTIL_NUMERIC.DECTOBASE', 'S', gc_error);
      RETURN NULL;
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error.', sqlerrm, 'UTIL_NUMERIC.DECTOBASE', 'S', gc_error);
      RETURN NULL;
  END dectobase;

  FUNCTION basetodec(
    p_number IN VARCHAR2, 
    p_base   IN INTEGER
  ) 
  RETURN NUMBER 
  IS
    c_digits CONSTANT VARCHAR2(16) := '0123456789ABCDEF';
    v_power INTEGER;
    v_result INTEGER :=0;
    v_decimal INTEGER;
    v_value_in plsql_constants.maxvarchar2_t;
    v_negative BOOLEAN := false;
    e_null_value EXCEPTION;
  BEGIN
  
    IF p_number IS NULL THEN
      RAISE e_null_value;
    END IF;
    
    /* Handle negative numbers */
    IF substr(p_number,1,1) = '-' THEN
      v_negative := true;
      v_value_in := upper(substr(p_number,2)); /* remove leading sign */
    ELSE
      v_value_in := upper(p_number);
    END IF;
    
    /* Check base specified is in correct range 1 to 16 */
    IF NVL(p_base,0) < 1 OR NVL(p_base,0) > 16 THEN
      RAISE e_invalid_base;
    END IF;
    
    /* Check input string contains a valid base number */
    IF NOT base_string_valid(v_value_in, p_base) THEN
      RAISE e_invalid_data;
    END IF;
    
    FOR i IN REVERSE 1 .. length(v_value_in) LOOP
      v_power := p_base**(length(v_value_in)-i);
      v_decimal := instr(c_digits,substr(v_value_in,i,1))-1;
      v_result := v_result + (v_decimal * v_power);
    END LOOP;
    IF v_negative THEN
      v_result := v_result * -1;  /* Add sign to negative numbers */
    END IF;
    RETURN v_result;
  EXCEPTION
    WHEN e_null_value THEN
      RETURN NULL;
    WHEN e_invalid_data THEN
      util_admin.log_message('Invalid number: ' || p_number || ' is not a base ' || to_char(p_base) || ' number.', sqlerrm, 'UTIL_NUMERIC.BASETODEC', 'S', gc_error);
      RETURN NULL;
    WHEN e_invalid_base THEN
      util_admin.log_message('Invalid base, must be a number between 1 and 16: ' || to_char(p_base), sqlerrm, 'UTIL_NUMERIC.BASETODEC', 'S', gc_error);
      RETURN NULL;
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error:', sqlerrm, 'UTIL_NUMERIC.BASETODEC', 'S', gc_error);
      RETURN NULL;
  END basetodec;

  FUNCTION dectohex(
    p_number IN INTEGER
  ) 
  RETURN VARCHAR2 
  IS
    v_result plsql_constants.maxvarchar2_t;
    v_quotient INTEGER;
    v_remainder INTEGER;
    c_base CONSTANT INTEGER :=16;
    c_digits CONSTANT VARCHAR2(c_base) :='0123456789ABCDEF';
    v_negative BOOLEAN := false;
    v_sign NUMBER :=1;
    e_null_value EXCEPTION;
  BEGIN
    IF p_number IS NULL THEN
      RAISE e_null_value;
    END IF;
    
    IF p_number < 0 THEN
      v_negative := true;
      v_sign := -1;
    END IF;
    v_quotient := p_number * v_sign; /* strip sign from -ve numbers */
    WHILE v_quotient > 0 LOOP
      v_remainder := mod(v_quotient,c_base);
      v_quotient := trunc(v_quotient / c_base);
      v_result := substr(c_digits, v_remainder +1, 1) || v_result;
    END LOOP;
    IF v_negative THEN
      v_result := concat('-',v_result); /* Reinstate sign to -ve numbers */
    END IF;
    RETURN nvl(v_result,'0');
  EXCEPTION
    WHEN e_null_value THEN
      RETURN NULL;
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error:', sqlerrm, 'UTIL_NUMERIC.DECTOHEX', 'S', gc_error);
      RETURN NULL;
  END dectohex;

  FUNCTION hextodec(
    p_number IN VARCHAR2
  ) 
  RETURN NUMBER 
  IS
    v_value_in plsql_constants.maxvarchar2_t;
    c_digits CONSTANT VARCHAR2(16) := '0123456789ABCDEF';
    c_base CONSTANT INTEGER := 16;
    v_power INTEGER;
    v_result INTEGER :=0;
    v_decimal INTEGER;
    v_negative BOOLEAN := false;
    e_null_value EXCEPTION;
  BEGIN
    IF p_number IS NULL THEN
      RAISE e_null_value;
    END IF;
     /* Handle negative numbers */
    IF substr(p_number,1,1) = '-' THEN
      v_negative := true;
      v_value_in := upper(substr(p_number,2)); /* remove leading sign */
    ELSE
      v_value_in := upper(p_number);
    END IF;
       
    IF NOT hex_valid(v_value_in) THEN
      RAISE e_invalid_data;
    END IF;
    
    FOR i IN REVERSE 1 .. length(v_value_in) LOOP
      v_power := c_base**(length(v_value_in)-i);
      v_decimal := instr(c_digits,substr(v_value_in,i,1))-1;
      v_result := v_result + (v_decimal * v_power);
    END LOOP;
    
    IF v_negative THEN
      v_result := v_result * -1;  /* Add sign to negative numbers */
    END IF;
    RETURN v_result;
    
  EXCEPTION
    WHEN e_null_value THEN
      RETURN NULL;
    WHEN e_invalid_data THEN
      util_admin.log_message('Invalid hexadecimal number: ' || p_number, sqlerrm, 'UTIL_NUMERIC.HEXTODEC', 'S', gc_error);
      RETURN NULL;
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error.', sqlerrm, 'UTIL_NUMERIC.HEXTODEC', 'S', gc_error);
      RETURN NULL;
  END hextodec;

  FUNCTION factorial(
    p_number IN INTEGER
  ) 
  RETURN NUMBER 
  IS
    v_fact NUMBER := 1;
    i NUMBER;
  BEGIN
    i := p_number;
    WHILE i > 1 LOOP
      v_fact := v_fact * i;
      i := i-1;
    END LOOP;
    RETURN (v_fact);
  EXCEPTION
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error.', sqlerrm, 'UTIL_NUMERIC.FACTORIAL', 'S', gc_error);
      RETURN NULL; 
  END factorial;

  -- Factorial using recursion
  FUNCTION factorialr(
    p_number IN INTEGER
  ) 
  RETURN NUMBER 
  IS
  BEGIN
    IF p_number <=1 THEN
      RETURN p_number;
    ELSE 
      RETURN p_number * factorialr(p_number -1);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
    util_admin.log_message('Unexpected error.', sqlerrm, 'UTIL_NUMERIC.FACTORIALR', 'S', gc_error);
    RETURN NULL;
  END factorialr;

  -- Sort a list of comma separated numbers into ascending or descending order
  FUNCTION sort_numbers (
    p_string IN VARCHAR2, 
    p_order  IN VARCHAR2
  ) 
  RETURN VARCHAR2 
  IS
    v_result plsql_constants.maxvarchar2_t;
    c_max_entries CONSTANT INTEGER := 10;
    TYPE t_list_array IS VARRAY(c_max_entries) OF NUMBER;
    v_list t_list_array := t_list_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    v_temp NUMBER;
  BEGIN
    -- Populate arrary with fields passed in p_string, a comma separated list of values
    FOR m IN 1 .. c_max_entries LOOP
      v_temp := nvl(to_number(util_string.get_field(p_string,m,',')),0);
      v_list(m) := to_number(util_string.get_field(p_string,m,','));
    END LOOP;

    -- Sort list
    FOR p1 IN 1 .. v_list.COUNT LOOP
      FOR p2 IN p1+1 .. v_list.COUNT LOOP
        IF (UPPER(p_order) = 'A' AND v_list(p2) < v_list(p1)) OR (UPPER(p_order) <> 'A' AND v_list(p2) > v_list(p1)) THEN
          v_temp := v_list(p1);
          v_list(p1) := v_list(p2);
          v_list(p2) := v_temp;
        END IF;
      END LOOP;
    END LOOP;

    -- Put sorted list into result string
    FOR m IN 1 .. v_list.LAST LOOP
      IF m = 1 THEN
        v_result := to_char(v_list(m));
      ELSIF v_list(m) IS NOT NULL THEN
        v_result := v_result || ',' || to_char(v_list(m));
      END IF;
    END LOOP;
    RETURN v_result;
  EXCEPTION
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error.', sqlerrm, 'UTIL_NUMERIC.SORT_NUMBERS', 'S', gc_error);
      RETURN NULL;
  END sort_numbers;

  FUNCTION num_to_alphanumeric(
    p_number IN NUMBER
  ) 
  RETURN VARCHAR2
  IS
    v_result VARCHAR2(100);
    v_base NUMBER := 26; -- Number of letters in the alphabet
    v_calc NUMBER;
    v_remainder NUMBER;
  BEGIN
    IF p_number <= 0 THEN
        RAISE e_invalid_data;
    END IF;
    v_calc := p_number;
    WHILE v_calc > 0 LOOP
      v_remainder := MOD(v_calc  - 1, v_base) + 1; -- Adjust for 1-based indexing
      v_result := CHR(ASCII('A') + v_remainder - 1) || v_result;
      v_calc  := (v_calc - v_remainder) / v_base;
    END LOOP;
    RETURN v_result;
  EXCEPTION
    WHEN e_invalid_data THEN
      util_admin.log_message('You must enter a positive whole number. Invalid value: ' || to_char(p_number), sqlerrm, 'UTIL_NUMERIC.NUM_TO_ALPHANUMERIC', 'S', gc_error);
      RETURN NULL;
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error.', sqlerrm, 'UTIL_NUMERIC.NUM_TO_ALPHANUMERIC', 'S', gc_error);  
      RETURN NULL;
  END num_to_alphanumeric;

  FUNCTION dectoalpha (
    p_number IN INTEGER, 
    p_range  IN INTEGER
  ) 
  RETURN VARCHAR2 
  IS
    c_max CONSTANT INTEGER := 30;
    c_alpha_max CONSTANT INTEGER := 26;
    v_alpha_range INTEGER;
    v_result VARCHAR2(c_max);
    v_power INTEGER;
    v_total INTEGER;
    v_n1 INTEGER;
  BEGIN
    /* Check p_number is a positive integer */
    IF p_number < 1 THEN
      RAISE e_invalid_data;
    END IF;
    
    /* Check range is within bounds of alphabet, between 1 and 26
        and reset values to within correct range if exceeded.
    */
    IF p_range < 1 THEN
      v_alpha_range :=1;
    ELSIF p_range > c_alpha_max THEN
      v_alpha_range := c_alpha_max;
    ELSE
      v_alpha_range := p_range;
    END IF;
    
    v_total := p_number;
    
    FOR n IN 1 .. c_max LOOP
      IF v_total <= 0 THEN
        EXIT;
      END IF;
      v_power := power(v_alpha_range,n-1);
      IF n = 1 THEN
        v_n1 := mod(v_total, v_alpha_range);
      ELSE
        v_n1 := floor(v_total / v_power);
      END IF;
      IF v_n1 < 1 THEN
        v_n1 := v_alpha_range;
      ELSIF v_n1 > v_alpha_range THEN
        v_n1 := mod(v_n1,v_alpha_range); 
        IF v_n1 < 1 THEN
          v_n1 := v_alpha_range;
        END IF;
      END IF;
      v_result := chr(v_n1+64) || v_result;
      v_total := v_total - (v_n1 * v_power);
    END LOOP;
    RETURN ltrim(v_result);
  EXCEPTION
    WHEN e_invalid_data THEN
      util_admin.log_message('You must enter a positive whole number. Invalid value: ' || to_char(p_number), sqlerrm, 'UTIL_NUMERIC.DECTOALPHA', 'S', gc_error);
      RETURN NULL;
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error.', sqlerrm, 'UTIL_NUMERIC.DECTOALPHA', 'S', gc_error);
      RETURN NULL;
  END dectoalpha;

  FUNCTION alphatodec(
    p_code  IN VARCHAR2, 
    p_range IN INTEGER
  ) 
  RETURN NUMBER 
  IS
    c_max CONSTANT INTEGER := 26;
    v_range INTEGER;
    v_power INTEGER;
    p_total INTEGER :=0;
    v_upper_char VARCHAR2(1);
  BEGIN  

    /* Alphabetic code range must be between 1 and 26, reset out of range values */
    IF p_range < 1 THEN
      v_range := 1;
    ELSIF p_range > c_max THEN
      v_range := c_max;
    ELSE
      v_range := p_range;
    END IF;
    
    /* Check alphabetic code contains only letters within specified range, 
        e.g. if range is 5 letters expected are A to E.
    */
    v_upper_char := chr(65 + p_range -1);
    IF NOT alpha_valid(p_code,v_range) THEN
      RAISE e_invalid_data;
    END IF;
    
    FOR i IN REVERSE 1 .. length(p_code) LOOP
      IF i = 1 THEN
        v_power := 1;
      ELSE
        v_power := power(v_range,i-1);
      END IF;
      p_total := p_total + ((ascii(substr(p_code,length(p_code)+1-i,1))-64)*v_power);   
    END LOOP;
    RETURN p_total;
  EXCEPTION
    WHEN e_invalid_data THEN
      util_admin.log_message('Invalid alphabetic string ' || p_code || '. You may use letters A to ' || v_upper_char || ' only. Range specified: ' || to_char(p_range), sqlerrm, 'UTIL_NUMERIC.ALPHATODEC', 'S', gc_error);
      RETURN NULL;
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error for alphabetic code:' || p_code || ' range:' || to_char(p_range), sqlerrm, 'UTIL_NUMERIC.ALPHATODEC', 'S', gc_error);
      RETURN NULL;
  END alphatodec;
  
  FUNCTION pi 
  RETURN NUMBER 
  IS
    last_pi NUMBER := 0;
    delta   NUMBER := 0.000001;
    pi      NUMBER := 1;
    denom   NUMBER := 3;
    oper    NUMBER := -1;
    negone  NUMBER := -1;
    two     NUMBER := 2;
  BEGIN
    LOOP
      last_pi := pi;
      pi := pi + oper * 1/denom;
      EXIT WHEN (abs(last_pi-pi) <= delta );
      denom := denom + two;
      oper := oper * negone;
    END LOOP;
    RETURN pi*4;
  EXCEPTION
    WHEN OTHERS THEN
      util_admin.log_message('Unexpected error.', sqlerrm, 'UTIL_NUMERIC.PI', 'S', gc_error);
      RETURN NULL;
  END pi;

END util_numeric;
/
