Amazon

How To Convert Amount In Number In To WORDS?

Create following Function to convert amount in number in to words.

CREATE OR REPLACE FUNCTION APPS.f_words (p_amount IN Number) RETURN Varchar2  IS
/*****************************************************************************
--Author          :  Shrikrishna Lobhe
--Creation Date   :  07-April-2014
--Purpose         :  This Function returns amount in words.
--Parameters      :
--1) p_amount     :  Only positive and negative values are allowed.
                     Precision can be entered upto 10 digits and only 2 scales
                     are allowed e.g 9999999999.99

******************************************************************************/
   -------------------------------------
   -- Index by Tables to store word list
   -------------------------------------
   TYPE typ_word_list IS TABLE OF Varchar2(200) INDEX BY BINARY_INTEGER;
   t_typ_word_list typ_word_list;
   TYPE typ_word_gap IS TABLE OF Varchar2(200) INDEX BY BINARY_INTEGER;
   t_typ_word_gap typ_word_gap;
   ------------------
   -- Local Variables
   ------------------
   v_amount        Number := p_amount;
   v_amount_length Number;
   v_words         Varchar2(10000);
   v_point_found   Varchar2(1) := 'N';
   v_point_value   Number;
BEGIN
   /*Getting value after point if found */
   v_point_value := SUBSTR(v_amount,(INSTR(v_amount,'.',1) + 1),2);

   /*Checking whether amount has any scale value also */
   v_point_found := CASE WHEN (INSTR(v_amount,'.',1)) = 0 THEN 'N'
                         WHEN (INSTR(v_amount,'.',1)) > 0 THEN 'Y'
                    END;
   /*Converting amount into pure numeric format */
   v_amount := FLOOR(ABS(v_amount)); 

   --
   v_amount_length := LENGTH(v_amount);
   --
   t_typ_word_gap(2)  := 'and Paise';
   t_typ_word_gap(3)  := 'Hundred';
   t_typ_word_gap(4)  := 'Thousand';
   t_typ_word_gap(6)  := 'Lakh';
   t_typ_word_gap(8)  := 'Crore';
   t_typ_word_gap(10) := 'Arab';
   --
   FOR i IN 1..99
   LOOP
       t_typ_word_list(i)   :=  To_Char(To_Date(i,'J'),'Jsp');
   END LOOP;
   --
   IF v_amount_length <= 2
   THEN
       /* Conversion 1 to 99 digits */
       v_words := t_typ_word_list(v_amount);
   ELSIF v_amount_length = 3
   THEN
       /* Conversion for 3 digits till 999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(3);
      -- v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2));
     
       IF SUBSTR(v_amount,2,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2));
       END IF;
   ELSIF v_amount_length = 4
   THEN
       /* Conversion for 4 digits till 9999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(4);
       IF SUBSTR(v_amount,2,1) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,1))||' '||t_typ_word_gap(3);
       END IF;
       IF SUBSTR(v_amount,3,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,3,2));
       END IF;
   ELSIF v_amount_length = 5
   THEN
       /* Conversion for 5 digits till 99999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,2))||' '||t_typ_word_gap(4);
       IF SUBSTR(v_amount,3,1) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,3,1))||' '||t_typ_word_gap(3);
       END IF;
       IF SUBSTR(v_amount,4,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,4,2));
       END IF; 

   ELSIF v_amount_length = 6
   THEN
       /* Conversion for 6 digits till 999999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(6);
       IF SUBSTR(v_amount,2,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2))||' '||t_typ_word_gap(4);
       END IF;
       IF SUBSTR(v_amount,4,1) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,4,1))||' '||t_typ_word_gap(3);
       END IF; 
       IF SUBSTR(v_amount,5,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,5,2));
       END IF;
   ELSIF v_amount_length = 7
   THEN
       /* Conversion for 7 digits till 9999999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,2))||' '||t_typ_word_gap(6);
       IF SUBSTR(v_amount,3,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,3,2))||' '||t_typ_word_gap(4);
       END IF;
       IF SUBSTR(v_amount,5,1) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,5,1))||' '||t_typ_word_gap(3);
       END IF; 
       IF SUBSTR(v_amount,6,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,6,2));
       END IF;
   ELSIF v_amount_length = 8
   THEN
       /* Conversion for 8 digits till 99999999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(8);
       IF SUBSTR(v_amount,2,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2))||' '||t_typ_word_gap(6);
       END IF;
       IF SUBSTR(v_amount,4,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,4,2))||' '||t_typ_word_gap(4);
       END IF; 
       IF SUBSTR(v_amount,6,1) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,6,1))||' '||t_typ_word_gap(3);
       END IF;
       IF SUBSTR(v_amount,7,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,7,2));
       END IF;
   ELSIF v_amount_length = 9
   THEN
       /* Conversion for 9 digits till 999999999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,2))||' '||t_typ_word_gap(8);
       IF SUBSTR(v_amount,3,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,3,2))||' '||t_typ_word_gap(6);
       END IF;
       IF SUBSTR(v_amount,5,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,5,2))||' '||t_typ_word_gap(4);
       END IF; 
       IF SUBSTR(v_amount,7,1) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,7,1))||' '||t_typ_word_gap(3);
       END IF;
       IF SUBSTR(v_amount,8,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,8,2));
       END IF;
   ELSIF v_amount_length = 10
   THEN
       /* Conversion for 10 digits till 9999999999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(10);
       IF SUBSTR(v_amount,2,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2))||' '||t_typ_word_gap(8);
       END IF;
       IF SUBSTR(v_amount,4,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,4,2))||' '||t_typ_word_gap(6);
       END IF; 
       IF SUBSTR(v_amount,6,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,6,2))||' '||t_typ_word_gap(4);
       END IF;
       IF SUBSTR(v_amount,8,1) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,8,1))||' '||t_typ_word_gap(3);
       END IF;
       IF SUBSTR(v_amount,9,2) != 0
       THEN
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,9,2));
       END IF;
   END IF;
   --
   IF v_point_found = 'Y'
   THEN
       IF v_point_value != 0
       THEN
           v_words := v_words||' '||t_typ_word_gap(2)||' '||t_typ_word_list(CASE WHEN LENGTH(SUBSTR(p_amount,(INSTR(p_amount,'.',1) + 1),2)) = 1 THEN SUBSTR(p_amount,(INSTR(p_amount,'.',1) + 1),2)||'0'
                                                                                 WHEN LENGTH(SUBSTR(p_amount,(INSTR(p_amount,'.',1) + 1),2)) = 2 THEN SUBSTR(p_amount,(INSTR(p_amount,'.',1) + 1),2)
                                                                            END);
       END IF;
   END IF;
   --
   IF p_amount < 0
   THEN
       v_words := 'Minus '||v_words;
   ELSIF p_amount = 0
   THEN
       v_words := 'Zero';
   END IF;
   IF LENGTH(v_amount) > 10
   THEN
       v_words := 'Value larger than specified precision allowed to convert into words. Maximum 10 digits allowed for precision.';
   END IF;
   RETURN (v_words);

END f_words;
/


sho err

select f_words(1548555) from dual
---------------------------------------------------------------------------------

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

   RETURN l_return;
END;
/



SELECT spell_number (5373455) FROM DUAL;






------------------------------------------------------------------------------

No comments:

Post a Comment

Subscribe to: Posts (Atom)