Function Script:
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;
TEST SCRIPT:
SET serveroutput on;
BEGIN
DBMS_OUTPUT.put_line (ruppee_to_word (1455555));
END;
--
How Order Management integrated with iStore?
Oracle Financial
Other Products