Oracle Convert Number to Word Function In RDF

CREATE OR REPLACE FUNCTION XX_CONVERT_NUM_TO_WORDS (p_numeral NUMBER)
   RETURN VARCHAR2
IS
   -- Function to convert the number to words
   c_zero             ap_lookup_codes.displayed_field%TYPE;
   c_thousand         ap_lookup_codes.displayed_field%TYPE;
   c_million          ap_lookup_codes.displayed_field%TYPE;
   c_billion          ap_lookup_codes.displayed_field%TYPE;
   number_too_large   EXCEPTION;
   numeral            NUMBER                                 := ROUND(ABS (p_numeral),2);
   max_digit          INTEGER                                := 12;                 -- for numbers less than a trillion
   number_text        VARCHAR2 (1000)                        := '';
   billion_seg        VARCHAR2 (25);
   million_seg        VARCHAR2 (25);
   thousand_seg       VARCHAR2 (25);
   units_seg          VARCHAR2 (25);
   billion_lookup     VARCHAR2 (80);
   million_lookup     VARCHAR2 (80);
   thousand_lookup    VARCHAR2 (80);
   units_lookup       VARCHAR2 (80);
   session_language   fnd_languages.nls_language%TYPE;
   thousand           NUMBER                                 := POWER (10, 3);
   million            NUMBER                                 := POWER (10, 6);
   billion            NUMBER                                 := POWER (10, 9);
   dec_seg            VARCHAR2 (25);                                                                    -- For decimals
   dec_lookup         VARCHAR2 (80);
BEGIN
   IF numeral >= POWER (10, max_digit)
   THEN
      RAISE number_too_large;
   END IF;


   IF numeral = 0
   THEN
      SELECT ' ' || displayed_field || ' '
        INTO c_zero
        FROM ap_lookup_codes
       WHERE lookup_code = 'ZERO';

      RETURN (c_zero);
   END IF;


   billion_seg := TO_CHAR (TRUNC (numeral / billion));
   numeral := numeral - (TRUNC (numeral / billion) * billion);
   million_seg := TO_CHAR (TRUNC (numeral / million));
   numeral := numeral - (TRUNC (numeral / million) * million);
   thousand_seg := TO_CHAR (TRUNC (numeral / thousand));
   units_seg := TO_CHAR (TRUNC (MOD (numeral, thousand)));
   numeral := numeral - ((TO_NUMBER (thousand_seg) * thousand) + TRUNC (MOD (numeral, thousand)));


   SELECT ' ' || lc1.displayed_field || ' '
        , ' ' || lc2.displayed_field || ' '
        , ' ' || lc3.displayed_field || ' '
        , ' ' || lc4.displayed_field
        , lc5.description
        , lc6.description
        , lc7.description
        , lc8.description
     INTO c_billion
        , c_million
        , c_thousand
        , c_zero
        , billion_lookup
        , million_lookup
        , thousand_lookup
        , units_lookup
     FROM ap_lookup_codes lc1,
          ap_lookup_codes lc2,
          ap_lookup_codes lc3,
          ap_lookup_codes lc4,
          ap_lookup_codes lc5,
          ap_lookup_codes lc6,
          ap_lookup_codes lc7,
          ap_lookup_codes lc8
    WHERE lc1.lookup_code = 'BILLION'
      AND lc1.lookup_type = 'NLS TRANSLATION'
      AND lc2.lookup_code = 'MILLION'
      AND lc2.lookup_type = 'NLS TRANSLATION'
      AND lc3.lookup_code = 'THOUSAND'
      AND lc3.lookup_type = 'NLS TRANSLATION'
      AND lc4.lookup_code = 'ZERO'
      AND lc4.lookup_type = 'NLS TRANSLATION'
      AND lc5.lookup_code = billion_seg
      AND lc5.lookup_type = 'NUMBERS'
      AND lc6.lookup_code = million_seg
      AND lc6.lookup_type = 'NUMBERS'
      AND lc7.lookup_code = thousand_seg
      AND lc7.lookup_type = 'NUMBERS'
      AND lc8.lookup_code = units_seg
      AND lc8.lookup_type = 'NUMBERS';

   SELECT SUBSTR (USERENV ('LANGUAGE'), 1, INSTR (USERENV ('LANGUAGE'), '_') - 1)
     INTO session_language
     FROM DUAL;

   IF (session_language = 'FRENCH' OR session_language = 'CANADIAN FRENCH') AND thousand_seg = '1'
   THEN
      thousand_lookup := NULL;
   END IF;

   IF billion_seg <> '0'
   THEN
      number_text := number_text || billion_lookup || c_billion;
      IF (session_language <> 'AMERICAN') and (million_seg <> '0')
      THEN
         number_text := number_text || ' و ';
      END IF;
   END IF;


   IF million_seg <> '0'
   THEN
      -- Added on 26-mar-09
      IF (session_language <> 'AMERICAN')
      THEN
         IF (million_seg = '1')
         THEN
            number_text := number_text || ' ' || 'مليون';
         ELSIF (million_seg = '2')
         THEN
            number_text := number_text || ' ' || 'مليونان';
         ELSIF (TO_NUMBER (million_seg) BETWEEN 3 AND 10)
         THEN
            number_text := number_text || million_lookup || ' ' ||'مليون';
         ELSE                                                                                                -- above 10
            number_text := number_text || million_lookup || ' ' || 'مليون';
         END IF;

         IF (session_language <> 'AMERICAN') and (thousand_seg <> '0') then
               number_text := number_text || ' و ';
         END IF;

      ELSE
         number_text := number_text || million_lookup || c_million;
      END IF;
   END IF;


   IF thousand_seg <> '0'
   THEN
      -- Added on 26-mar-09
      IF (session_language <> 'AMERICAN')
      THEN

         IF (thousand_seg = '1')
         THEN
            number_text := number_text || ' ' || 'الف';
         ELSIF (thousand_seg = '2')
         THEN
            number_text := number_text || ' ' || 'الفي';
         ELSIF (TO_NUMBER (thousand_seg) BETWEEN 3 AND 10)
         THEN
            number_text := number_text || thousand_lookup || ' ' || 'الاف';
         ELSE                                                                                                -- above 10
            number_text := number_text || thousand_lookup || ' ' || 'الف';
         END IF;

         IF (units_seg <> '0') and (session_language <> 'AMERICAN') then
               number_text := number_text || ' و ';
         END IF;

      ELSE
         number_text := number_text || thousand_lookup || c_thousand;
      END IF;
   END IF;

   IF units_seg <> '0'
   THEN
      number_text := number_text||units_lookup;
   END IF;

   IF (session_language <> 'AMERICAN')
   THEN
      IF (billion_seg <> '0') OR (million_seg <> '0') OR (thousand_seg <> '0') OR (units_seg <> '0')
      THEN
         number_text := number_text || ' ' || 'درهم';
      END IF;
   ELSE
     number_text := number_text || ' ' || 'Dirhams';
   END IF;

   -- For decimal points
   IF numeral > 0
   THEN
      dec_seg := TO_CHAR (numeral * 100);

      SELECT lc9.description
        INTO dec_lookup
        FROM ap_lookup_codes lc9
       WHERE lc9.lookup_code = dec_seg AND lc9.lookup_type = 'NUMBERS';
   /*elsif numeral=0 then
      select lc9.description
      into   dec_lookup
      from   ap_lookup_codes lc9
      where    lc9.lookup_code = '0'
      and    lc9.lookup_type = 'NUMBERS';*/ -- commented on 26-mar-09
   ELSE
      dec_lookup := '';
   END IF;

   IF dec_lookup IS NOT NULL
   THEN                                                                                            -- added if 26-mar-09
      IF (session_language <> 'AMERICAN')
      THEN
         number_text := number_text || ' و ' || dec_lookup || ' فلس';
      ELSE
         number_text := number_text || ' And ' || dec_lookup || ' Fils';
      END IF;
    ELSE
      number_text := number_text;
   END IF;

   number_text := LTRIM (number_text);
   number_text := UPPER (SUBSTR (number_text, 1, 1)) || RTRIM (SUBSTR (number_text, 2, LENGTH (number_text)));
   RETURN (number_text);
EXCEPTION
   WHEN number_too_large
   THEN
      RETURN (SQLERRM);
   WHEN OTHERS
   THEN
      RETURN (SQLERRM);
END;

/