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