PLSQL Script to Show Amount in Words in Oracle Apps R12

PLSQL Script to Show Figure Amount  in Words in Oracle Apps R12


CREATE OR REPLACE FUNCTION APPS.xx_money_words(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 xx_money_words;
/