Purpose of the function : Any given number should be converted in terms of Indian rupee and paisa words' For Example Rs'123456789'12 should be converted into words as Rupee Twelve Crore Thirty-Four Lakh, Fifty-Six Thousand seven Hundred Eighty Nine and Paisa Twelve only' Normally JSP Format conversion of date will be done in million and billion whereas Indian rupee needs conversions Hundred Lakhs as One crore and no million terms' In fact One million need to be written as Ten Lakhs'
create or replace function "NUM_2_WORD" (in_number in NUMBER)
return VARCHAR2 is
out_word varchar2(1000);
begin select
'Rupees 'II(case when nvl(crore,0)>0 then to_char(to_date(crore,'J'),'JSP')II' Crore ' else ' ' end
)II' 'II
(case when nvl(lakh,0)>0 then to_char(to_date(lakh,'J'),'JSP')II' Lakhs ' else ' ' end
)II' 'II
(case when nvl(units,0)>0 then to_char(to_date(units,'J'),'JSP')II' ' else ' ' end )II'
'II
(case when nvl(paise,0)>0 then ' and Paise
'IIto_char(to_date(paise,'J'),'JSP')II' ' else ' ' end
)II' 'II
' Only ' into out_word