 |
|
| Oracle Tips: |
|
|
|
E-BUSINESS SUITE UPDATE
Numbers to words in any language
Brent Green
06.13.2006
Rating: -2.69- (out of 5)
|
This code snippet is primarily used for converting numbers to words in any language. We had an instance where we needed to print French checks while logged into Oracle Applications as an English user. Here's how we did it.
NOTE: This should work for any language that is installed on the database. We have French and English. For the main language the user could use the Oracle built-in function AP_AMOUNT_UTILITIES_PKG.ap_convert_number. I have manipulated and created a view and a function based on this function.
- Created a custom view based on ap_lookup_codes, and forced it to use French instead of userenv('LANG').
CREATE OR REPLACE VIEW AP_LOOKUP_CODES_FRENCH
(LOOKUP_TYPE, LOOKUP_CODE, DISPLAYED_FIELD, DESCRIPTION,
ENABLED_FLAG, START_DATE_ACTIVE, ND_DATE_ACTIVE)AS SELECT
LV.LOOKUP_TYPE, LV.LOOKUP_CODE, LV.MEANING "DISPLAYED_FIELD",
LV.DESCRIPTION, LV.ENABLED_FLAG, LV.START_DATE_ACTIVE,
LV.END_DATE_ACTIVE from FND_LOOKUP_VALUES LV WHERE LV.LANGUAGE = 'FRC'
and LV.VIEW_APPLICATION_ID = 200 and LV.SECURITY_GROUP_ID = fnd_global.lookup_security_group
(LV.LOOKUP_TYPE, LV.VIEW_APPLICATION_ID)/
- Created a custom function to convert numbers to words. Used for dollar amount.
FUNCTION TO_WORDS_FRENCH (in_numeral IN NUMBER) return varchar2 is
c_zero ap_lookup_codes_french.displayed_field%TYPE;
c_thousand ap_lookup_codes_french.displayed_field%TYPE;
c_million ap_lookup_codes_french.displayed_field%TYPE;
c_billion ap_lookup_codes_french.displayed_field%TYPE;
number_too_large exception;
numeral integer := TRUNC(in_numeral);
WholePart NUMBER := ABS(TRUNC(in_numeral));
--FractionalPart NUMBER := (ABS(in_numeral) - WholePart) * 100;
FractionalPart NUMBER := ABS(in_numeral) - WholePart;
Fraction VARCHAR2(2000);
max_digit integer := 12; -- for numbers less than a trillion
number_text varchar2(240) := '';
billion_seg varchar2(25);
million_seg varchar2(25);
thousand_seg varchar2(25);
units_seg varchar2(25);
billion_lookup varchar2(80);
million_lookup varchar2(80);
thousand_lookup varchar2(80);
units_lookup varchar2(80);
session_language fnd_languages.nls_language%TYPE;
thousand number := power(10,3);
million number := power(10,6);
billion number := power(10,9);
begin
if numeral >= power(10,max_digit) then
raise number_too_large;
end if;
-- Dollar amount should not have more than two fractional
-- digits. TO_CHAR results in an additional character for
-- the decimal point. Therefore, we compare LENGTH to 3.
IF LENGTH(TO_CHAR(FractionalPart)) > 3
THEN
RAISE number_too_large;
END IF;
-- If the dollar amount is zero (WholePart)
if numeral = 0 then
select ' '||displayed_field||' '
into c_zero
from ap_lookup_codes_french
where lookup_code = 'ZERO';
number_text := number_text||c_zero;
end if;
billion_seg := to_char(trunc(numeral/billion));
numeral := numeral - (trunc(numeral/billion) * billion);
million_seg := to_char(trunc(numeral/million));
numeral := numeral - (trunc(numeral/million) * million);
thousand_seg := to_char(trunc(numeral/thousand));
units_seg := to_char(mod(numeral,thousand));
select ' '||lc1.displayed_field||' ',
' '||lc2.displayed_field||' ',
' '||lc3.displayed_field||' ',
' '||lc4.displayed_field,
lc5.description,
lc6.description,
lc7.description,
lc8.description
into c_billion,
c_million,
c_thousand,
c_zero,
billion_lookup,
million_lookup,
thousand_lookup,
units_lookup
from ap_lookup_codes_french lc1,
ap_lookup_codes_french lc2,
ap_lookup_codes_french lc3,
ap_lookup_codes_french lc4,
ap_lookup_codes_french lc5,
ap_lookup_codes_french lc6,
ap_lookup_codes_french lc7,
ap_lookup_codes_french lc8
where lc1.lookup_code = 'BILLION'
and lc1.lookup_type = 'NLS TRANSLATION'
and lc2.lookup_code = 'MILLION'
and lc2.lookup_type = 'NLS TRANSLATION'
and lc3.lookup_code = 'THOUSAND'
and lc3.lookup_type = 'NLS TRANSLATION'
and lc4.lookup_code = 'ZERO'
and lc4.lookup_type = 'NLS TRANSLATION'
and lc5.lookup_code = billion_seg
and lc5.lookup_type = 'NUMBERS'
and lc6.lookup_code = million_seg
and lc6.lookup_type = 'NUMBERS'
and lc7.lookup_code = thousand_seg
and lc7.lookup_type = 'NUMBERS'
and lc8.lookup_code = units_seg
and lc8.lookup_type = 'NUMBERS';
if billion_seg <> '0' then
number_text := number_text||billion_lookup ||c_billion;
end if;
if million_seg <> '0' then
number_text := number_text||million_lookup||c_million;
end if;
if thousand_seg <> '0' then
number_text := number_text||thousand_lookup||c_thousand;
end if;
if units_seg <> '0' then
number_text := number_text||units_lookup;
end if;
number_text := ltrim(number_text);
number_text := upper(substr(number_text,1,1)) ||
rtrim(lower(substr(number_text,2,length(number_text))));
IF FractionalPart = 0
THEN
-- When fractional part is 0.
-- Check if 0 fractional part should be displayed.
Fraction := TO_CHAR(FractionalPart, '09')|| '/100';
ELSE
-- When fractional part is not 0.
-- Dollar amount should not have more than two fractional
-- digits. TO_CHAR results in an additional character for
-- the decimal point. Therefore, we compare LENGTH to 3.
FractionalPart := FractionalPart * 100;
Fraction := TO_CHAR(FractionalPart, '09')|| '/100';
END IF;
return(UPPER(number_text)|| ' ET ' || Fraction);
END TO_WORDS_FRENCH;
- Executed function.
select TO_WORDS_FRENCH(123.45) from dual;
CENT VINGT-TROIS ET 45/100
|
|
|
|
|
|
|
|
|
| TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of . |
|
| | |
All Rights Reserved, , TechTarget |
|
|
|
|
|
