Method 1:
A common method for converting a number to words is to use the TO_DATE function and 'j' format to convert the number to a julian date (the number of days since January 1, 4712 B.C.), then use the TO_CHAR function and 'jsp' format to spell the julian date.
A common method for converting a number to words is to use the TO_DATE function and 'j' format to convert the number to a julian date (the number of days since January 1, 4712 B.C.), then use the TO_CHAR function and 'jsp' format to spell the julian date.
I am using below query to convert for this number(1235487) to word.
SELECT TO_CHAR (TO_DATE (1235487, 'j'), 'Jsp') FROM DUAL;
OR
SELECT AP_AMOUNT_UTILITIES_PKG.ap_convert_number(1235487) FROM DUAL;
It's return for : One Million Two Hundred Thirty-Five Thousand Four Hundred Eighty-Seven
But i want result like :
Twelve Lakh Thirty Five Thousand Four Hundred Eighty Seven Only
And also it has range from 1 to 5373484 is we put value after 5373484, like 5373485
SELECT TO_CHAR (TO_DATE (5373485, 'j'), 'Jsp') FROM DUAL;
then it will throw you an error
"ORA-01854: julian date must be between 1 and 5373484"
How to achieve this?
Solution:
If you wish to spell numbers greater than the maximum julian date allowed (5373484) or if you wish to include decimal places, or if you wish to have the output in another format or language, then you will need a more complex function that works for larger numbers and include decimal places, other formats, and other languages.
If you wish to spell numbers greater than the maximum julian date allowed (5373484) or if you wish to include decimal places, or if you wish to have the output in another format or language, then you will need a more complex function that works for larger numbers and include decimal places, other formats, and other languages.
======================Function for Indian Format========================
create or replace function amt_to_word( p_number in number )
return varchar2
as
type myArray is table of varchar2(255);
l_str myArray := myArray(' Thousand ', ' Lakh ',' Crore ', ' Arav ',' Kharav ',' Neel ',' Padma ', ' Shankh ');
l_num varchar2(50) default trunc( p_number );
l_return varchar2(4000);
begin
if (substr( l_num, length(l_num)-2,3 )<>0)
then
l_return := REPLACE (to_char(to_date(substr(l_num, length(l_num)-2, 3),'J'),'Jsp'),'-',' ');
end if;
l_num:=substr( l_num, 1,length(l_num)-3);
for i in 1 .. l_str.count
loop
exit when l_num is null;
if (substr( l_num, length(l_num)-1,2 )<>0)
then
l_return := REPLACE (to_char(to_date(substr(l_num, length(l_num)-1, 2),'J' ),'Jsp'),'-',' ')|| l_str(i) || l_return;
end if;
l_num:=substr( l_num, 1,length(l_num)-2);
end loop;
if to_char( p_number ) like '%.%'
then
l_num := substr(round(p_number,2),instr(p_number,'.')+1);
if (length(substr(round(p_number,2),instr(p_number,'.')+1 )))=1
then
l_num := to_number(to_char(l_num) ||'0');
end if;
if l_num>0
then
l_return :=l_return ||' And' || REPLACE (to_char(to_date(l_num,'J'),' Jsp'),'-',' ')||' Paise';
end if;
end if;
--return(l_return);
return(l_return||' Only');
end amt_to_word;
======================Query=================================
SQL>select amt_to_word(5373485) from dual;
It's return for: Fifty Three Lakh Seventy Three Thousand Four Hundred Eighty Five Only
======================Function for International Format==================
CREATE OR REPLACE FUNCTION APPS.num_to_word (p_number IN NUMBER)
RETURN VARCHAR2
AS
TYPE myArray IS TABLE OF VARCHAR2 (255);
l_str myArray:= myArray ('',' thousand ',' million ',' billion ',' trillion ',' quadrillion ',' quintillion ',
' sextillion ',' septillion ',' octillion ',' nonillion ',' decillion ',' undecillion ',' duodecillion ');
l_num VARCHAR2 (50) DEFAULT TRUNC (abs(p_number));
l_return VARCHAR2 (4000);
V_POS NUMBER;
V_FRACTION VARCHAR2(1000);
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;
------------------FOR FRACTION VALUE------------------------
V_POS := INSTR(TO_CHAR(P_NUMBER),'.');
IF V_POS > 0 THEN
SELECT INITCAP(TO_CHAR(TO_DATE(SUBSTR(P_NUMBER,V_POS+1,LENGTH(P_NUMBER)), 'J'), 'JSP')) INTO V_FRACTION FROM DUAL;
l_return := l_return||'Point '||V_FRACTION;
END IF;
RETURN l_return;
END;
/
======================Query=================================
SQL>select num_to_word(5373485) from dual
It's return for: Five million Three Hundred Seventy-Three thousand Four Hundred Eighty-Five
Method 2:
Number to Word conversion in RTF
xdofx vs xdoxslt
<?xdofx:expression?> for extended SQL functions
<?xdoxslt:expression?> for extended XSL functions.
You cannot mix xdofx statements with XSL expressions in the same context
This function enables the conversion of numbers to words for RTF template output. This is a common requirement for check printing.
The new function is “to_check_number”. The syntax of this function is
<?xdofx:to_check_number(amount, precisionOrCurrency, caseType, decimalStyle)?>
RTF Template Entry
<?xdofx:to_check_number(12345.67, 2)?>
Returned Output
Twelve thousand three hundred forty-five and 67/100
Alternative
<?xdoxslt:toWordsAmt(TEST_AMOUNT)?>
Then it will display amount in words in Indian currency means Rupees not millions but it's showing some wrong spelling of particular number like Fourty.
Example:
<?xdoxslt: toWordsAmt(1244454.88)?>
Then it will display as
Twelve Lakh Fourty Four Thousand Four Hundred Fifty Four and paise Eighty Eight
Alternative
Use the following function in rdf level
IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(TEST_AMOUNT)
