Tip

Numbers to words in any language

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.

  1. 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)/
    
  2. 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 >=

    Requires Free Membership to View

  1. 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;
  2. Executed function.
    select TO_WORDS_FRENCH(123.45) from dual;
    
    CENT VINGT-TROIS ET 45/100
    

    This was first published in June 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.