Function for displaying Rupees in Words

Function for displaying Rupees in Words

by santosh kumar adda oracleapps

Function for displaying Rupees in Words

–XXIII. Function for displaying Rupees in Words :
CREATE OR REPLACE FUNCTION to_word_i
(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 to_word_i;