Convert the currence to Words format in oracle.

Background:
Imagine you are developing a report for AP module [Infact in any module] and customer wanted to display Invoice amount in Words, then you can use this solution.

Solution:
SELECT ap_amount_utilities_pkg.ap_convert_number(12345) AS amt_in_words
  FROM dual;

Output:

AMT_IN_WORDS
---------------------
Twelve thousand three hundred forty-five

Note:
1. Maximum number allowed is (10 Power 12)
2. Non Oracle Apps users can use the following query.
    select TO_CHAR(TO_DATE(&enter_a_number, 'J'),'JSP') FROM dual
    Limitation: Entered number should not exceed 5373484

--------------------------------      ******************       ----------------------------------

CREATE OR REPLACE FUNCTION APPS.spell_money (p_number IN NUMBER)
   RETURN VARCHAR2
AS
   TYPE myarray IS TABLE OF VARCHAR2 (255);

   l_str      myarray
      := myarray (' Thousand ',
                  ' Lakh ',
                  ' Crore ',
                  ' Arab ',
                  ' Kharab ',
                  ' Shankh '
                 );
   l_num      VARCHAR2 (50)   DEFAULT TRUNC (p_number);
   l_return   VARCHAR2 (4000);
BEGIN
   IF (SUBSTR (l_num, LENGTH (l_num) - 2, 3) <> 0)
   THEN
      l_return :=
         TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 3), 'J'),
                  'Jsp');
   END IF;

   l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);

   FOR i IN 1 .. l_str.COUNT
   LOOP
      EXIT WHEN l_num IS NULL;

      IF (SUBSTR (l_num, LENGTH (l_num) - 1, 2) <> 0)
      THEN
         l_return :=
               TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 1, 2), 'J'),
                        'Jsp'
                       )
            || l_str (i)
            || l_return;
      END IF;

      l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 2);
   END LOOP;

   IF TO_CHAR (p_number) LIKE '%.%'
   THEN
      l_num := SUBSTR (ROUND (p_number, 2), INSTR (p_number, '.') + 1);

      IF (LENGTH (SUBSTR (ROUND (p_number, 2), INSTR (p_number, '.') + 1))) =
                             
                                              1
      THEN
         l_num := TO_NUMBER (TO_CHAR (l_num) || '0');
      END IF;

      IF l_num > 0
      THEN
         l_return :=
               l_return
            || ' And '
            || TO_CHAR (TO_DATE (l_num, 'J'), 'Jsp')
            || ' Paise';
      END IF;
   END IF;

   RETURN (l_return||' only');
END spell_money;


====================================================================


CREATE OR REPLACE FUNCTION APPS.amt_in_words ( l_amt IN NUMBER, l_ccode  VARCHAR2)
RETURN VARCHAR2
AS
words          VARCHAR2(1000);
whole          VARCHAR2(500)  :=  NULL;
frac           VARCHAR2(500)  :=  NULL;
suffix1        VARCHAR2(20);
suffix2        VARCHAR2(20);
currency       NUMBER (20,3);
code           VARCHAR2(200);
c2             NUMBER;
i              NUMBER;
p              NUMBER;
flen           NUMBER;
text           VARCHAR2(30);
tl             NUMBER;
tl1            NUMBER;
BEGIN
  IF l_ccode IN ('BHD','KWD','OMR') THEN
     text := TO_CHAR(l_amt,'99999999.999');
     tl1  := 1000;
  ELSE
     text := TO_CHAR(l_amt,'99999999.99');
     tl1  := 100;
  END IF;
  tl       := LENGTH(text);
  p        := INSTR(text,'.',1,1);
  tl       := LENGTH(SUBSTR(text,p+1,tl));
  currency := TRUNC(l_amt);
  c2       := l_amt - currency;
  c2       := c2 * tl1 ;
  code     :=UPPER(l_ccode);
  BEGIN
     SELECT DECODE (l_ccode,'USD', 'cents',
                            'GBP','pences',
                            'SAR', 'halalat',
                            'QAR','dirhams',
                            'AED', 'fils',
                            'DEM', 'pfennigs',
                            'EURO','euro cents',
                            'INR','paise',
                            'FRF', 'centimes',
                            'BHD','fils',
                            'KWD','fils',
                            'OMR','baizas')
       INTO suffix2
       FROM DUAL;
  EXCEPTION
       WHEN OTHERS THEN
          RAISE;
  END;
  BEGIN
     SELECT   DECODE (l_ccode,'USD', ' US Dollars',
                              'BHD',' Bahraini Dinars',
                              'GBP',' Pounds',
                              'KWD',' Kuwaiti Dinars',
                              'SAR', ' Saudi Riyals',
                              'QAR',' Qatari Riyals',
                              'AED', ' UAE Dirhams',
                              'DEM', ' German Marks',
                              'EURO',' Euros',
                              'INR',' Indian Rupees',
                              'FRF', ' French Franks',
                              'OMR',' Omani Rials',
                              'IRR', ' Iran Rials')
       INTO suffix1
       FROM DUAL;
  EXCEPTION
       WHEN OTHERS THEN
          RAISE;
  END;
  IF (currency + c2 = 0 ) THEN
     --words :='Zero '||' and Zero '||INITCAP(suffix2)||' '||'Only';
     words :='Zero ' || INITCAP(suffix1) ||' and Zero '||INITCAP(suffix2)||' '||'Only';
  ELSE
     words :=ap_amount_utilities_pkg.ap_convert_number(currency) ||','|| ap_amount_utilities_pkg.ap_convert_number(c2) ;
     p     :=  INSTR (words,',',1,1);
     whole :=whole || SUBSTR(words,1,p-1);
     frac  :=frac || SUBSTR(words,p+1, LENGTH(words));
  --   words := INITCAP(replace(whole,'-',' '))||' and '||
--INITCAP(frac)||' '||INITCAP(suffix2)||' '||'Only';

words := INITCAP(replace(whole,'-',' '))|| INITCAP(suffix1) ||' and '||
INITCAP(frac)||' '||INITCAP(suffix2)||' '||'Only';
  END IF;
  RETURN words;
EXCEPTION
    WHEN OTHERS THEN
       words := 'UNKNOWN CURRENCY';
       RETURN words;
END amt_in_words;
/