Oracle Advaced PLSQL

Amount in words / Value in words

I know you have had faced so many difficulties when converting amount/value to words.Here I am sharing with you the solution to your problem ........."

SELECT Func_amount_words(153567.67,'Rupees','Cents') FROM DUAL;
Output;
One Hundred Fifty-Three Thousand Five Hundred Sixty-Seven Rupees And Sixty-Seven Cents

 _____________________________________________________________________

CREATE OR REPLACE FUNCTION Func_amount_words (p_number   IN VARCHAR2,
                                              P_Currency IN VARCHAR2,
                                              P_Decimal  IN VARCHAR2 )
                                              RETURN VARCHAR2
AS
   /***Author : Madhura Weragoda ****/ 
   TYPE myarray IS TABLE OF VARCHAR2 (255);

   l_str myarray := myarray ('',' thousand ',
                              ' lakhs ',
                              ' million',
                              ' billion ',
                              ' trillion ',
                              ' quadrillion ',
                              ' quintillion ',
                              ' sextillion ',
                              ' septillion ',
                              ' octillion ',
                              ' nonillion ',
                              ' decillion ',
                              ' undecillion ',
                              ' duodecillion '
                 );
   l_num      VARCHAR2 (50)   DEFAULT TRUNC (p_number);
   l_return   VARCHAR2 (4000);
BEGIN
   FOR i IN 1 .. l_str.COUNT
   LOOP
      EXIT WHEN l_num IS NULL;

      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'
                       )
            || l_str (i)
            || l_return;
      END IF;

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

   l_return := l_return || ' '||P_Currency;

   -- beginning of section added to include decimal places:
   IF p_number LIKE '%.%'
   THEN
      l_return := l_return || ' and';
      l_num := SUBSTR (p_number, INSTR (p_number, '.') + 1);
      l_return := l_return || ' ' || TO_CHAR (TO_DATE (l_num, 'j'), 'jsp');
      l_return := l_return ||' ' ||P_Decimal;
   END IF;

   -- end of section added to include decimal places
   RETURN initcap(l_return);
END Func_amount_words;

No comments:

Post a Comment