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