Numbers to words in any language

This tip describes how to convert numbers to words while logged into Oracle Applications as an English user, using a custom view and function. This should work for any language that is installed on the database.

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

This was first published in June 2006

Dig deeper on Oracle E-Business Suite

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

1 comment

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close