add

About Me

My photo
Oracle Apps - Techno Functional consultant

Thursday, October 27

Display Numbers in Figures (words)

1) Using Julian method
select to_char(to_date(11221873,'J'),'JSP') from dual -- For upper-case letters
select to_char(to_date(11221873,'J'),'Jsp') from dual -- For mixed-case letters
select to_char(to_date(11221873,'J'),'jsp') from dual -- For lower-case letters

The Julian Method is limited to display text only till million, so 9,999,999 is the maximum value that can be displayed in words.

2) Using Data and Time Suffixes. Listed is the option for Data and Time Suffix
TH - converts numbers to ordinal numbers
SP - converts numbers to words
SPTH - converts numbers to ordinal words

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'FFSP') AS amt_in_words
FROM DUAL --> For Upper Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'Ffsp') AS amt_in_words
FROM DUAL --> For Mixed Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'ffsp') AS amt_in_words
FROM DUAL --> For Lower Case Letters

The maximum value that can be displayed is 99,999,999. So we added another number as against the Julian function.

3) This is the best option but limited only to Oracle Application users.
Oracle Apps has provided an inbuilt function AP_AMOUNT_UTILITIES_PKG which can be used to achieve the result.

SELECT Upper(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
FROM DUAL --> For Upper Case Letters

SELECT InitCap(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
FROM DUAL --> For Mixed Case Letters

SELECT Lower(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
FROM DUAL --> For Lower Case Letters

Well as there is an end to everything, even this function has a limitation but it can display big enough as it goes upto hundred billions. 999,999,999,999 is the maximum allowed value.

No comments: