Wednesday, July 22, 2015

Function to Return Amount in Words

CREATE OR REPLACE FUNCTION ruppee_to_word (amount IN NUMBER)
   RETURN VARCHAR2
AS
   v_length   INTEGER         := 0;
   v_num2     VARCHAR2 (50)   := NULL;
   v_amount   VARCHAR2 (50)   := TO_CHAR (TRUNC (amount));
   v_word     VARCHAR2 (4000) := NULL;
   v_word1    VARCHAR2 (4000) := NULL;
   TYPE myarray IS TABLE OF VARCHAR2 (255);
   v_str      myarray         := myarray (' Thousand ', ' Lakh ', ' Crore ');
BEGIN
   IF ((amount = 0) OR (amount IS NULL))
   THEN
      v_word := 'zero';
   ELSIF (TO_CHAR (amount) LIKE '%.%')
   THEN
      IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0)
      THEN
         v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);
         IF (LENGTH (v_num2) < 2)
         THEN
            v_num2 := v_num2 * 10;
         END IF;
         v_word1 :=
               ' AND '
            || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1, 2),
                                  'J'),
                         'JSP'
                        )
               )
            || ' paise ';
         v_amount := SUBSTR (amount, 1, INSTR (amount, '.') - 1);
         v_word :=
               TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2, 3),
                                 'J'
                                ),
                        'Jsp'
                       )
            || v_word;
         v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);
         FOR i IN 1 .. v_str.COUNT
         LOOP
            EXIT WHEN (v_amount IS NULL);
            v_word :=
                  TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,
                                            2),
                                    'J'
                                   ),
                           'Jsp'
                          )
               || v_str (i)
               || v_word;
            v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);
         END LOOP;
      END IF;
   ELSE
      v_word := TO_CHAR (TO_DATE (TO_CHAR (amount, '999999999'), 'J'), 'JSP');
   END IF;
   v_word := v_word || ' ' || v_word1 || ' only ';
   v_word := REPLACE (RTRIM (v_word), ' ', ' ');
   v_word := REPLACE (RTRIM (v_word), '-', ' ');
   RETURN INITCAP (v_word);
END ruppee_to_word;



BEGIN
   DBMS_OUTPUT.put_line (ruppee_to_word (1455555));
END;

No comments:

Post a Comment

Clear BNE Cache for WebADI Changes

It Sometime happens that WebAdi Changes doesn't reflect once migrated in controlled instances. Here are the quick steps(Generally perfor...