Number format in Oracle E-Business Suite 11i
                                                 Last update (2014-03-03 04:59:22)
                                                                                                                   Date added (2013-11-05 11:09:12)

Summary
Usually the numbers (for the amounts) must be set to a specific format in the E-Business Suite for all modules (AR, AP, GL, etc).

For example a format like 10,000.00 is very common. In the 10,000.00 example, the thousands are seperated by a comma character(,) and the decimal part with a dot character(.)

In order to set the number format in the desired format of 10,000.00 you must do the following:

1. In the database init.ora or spfile set the parameter nls_numeric_characters to .,
First is the decimal seperator (.) and then the group seperator (,)

2. In the user's home screen of the EBS there is in the upper right corner the "Preferences" option. Press there and set the field "Number format" to the desired format.

3. Set the two profiles either at the Site level or the User level:

Currency: Thousands Separator = Yes
ICX: Numeric characters = 10,000.00

You can find the profile option values with the query

SELECT 'At Site Level' "Profile Type",
        'Site' NAME,    
        profOptTl.USER_PROFILE_OPTION_NAME, 
        profOpt.USER_CHANGEABLE_FLAG, 
        profOpt.USER_VISIBLE_FLAG, 
          profOpt.SITE_ENABLED_FLAG, 
        ProfOptVal.LEVEL_ID, 
        ProfOptVal.LEVEL_VALUE, 
          ProfOptVal.PROFILE_OPTION_VALUE
FROM    APPLSYS.FND_PROFILE_OPTIONS_TL profOptTl, 
        APPLSYS.FND_PROFILE_OPTIONS profOpt, 
        APPLSYS.FND_PROFILE_OPTION_VALUES ProfOptVal
WHERE   ( profOptTl.PROFILE_OPTION_NAME=profOpt.PROFILE_OPTION_NAME ) 
  AND    ( profOpt.PROFILE_OPTION_ID=ProfOptVal.PROFILE_OPTION_ID )
  AND    ProfOptVal.LEVEL_ID = 10001
  AND   (UPPER(profOpt.PROFILE_OPTION_NAME) IN ('ICX_NUMERIC_CHARACTERS', 'CURRENCY:THOUSANDS_SEPARATOR',
'ICX_LANGUAGE', 'ICX_TERRITORY' )) 
'UNION ALL' 
SELECT 'Application', 
App.APPLICATION_SHORT_NAME, 
profOptTl.USER_PROFILE_OPTION_NAME, 
profOpt.USER_CHANGEABLE_FLAG, 
profOpt.USER_VISIBLE_FLAG, 
profOpt.SITE_ENABLED_FLAG, 
ProfOptVal.LEVEL_ID, 
ProfOptVal.LEVEL_VALUE, 
ProfOptVal.PROFILE_OPTION_VALUE 
'FROM   APPLSYS.FND_PROFILE_OPTIONS_TL profOptTl,' 
       APPLSYS.FND_PROFILE_OPTIONS profOpt, 
       APPLSYS.FND_PROFILE_OPTION_VALUES ProfOptVal, 
       APPLSYS.FND_APPLICATION App
WHERE 
( profOptTl.PROFILE_OPTION_NAME=profOpt.PROFILE_OPTION_NAME ) AND 
  ( profOpt.PROFILE_OPTION_ID=ProfOptVal.PROFILE_OPTION_ID ) AND 
  ( ProfOptVal.LEVEL_VALUE=App.APPLICATION_ID )
AND    ProfOptVal.LEVEL_ID = 10002
  AND   (UPPER(profOpt.PROFILE_OPTION_NAME) IN ('ICX_NUMERIC_CHARACTERS',  'CURRENCY:THOUSANDS_SEPARATOR',
'ICX_LANGUAGE', 'ICX_TERRITORY' )) 
'UNION ALL' 
SELECT 'Responsibility',
       RespT.RESPONSIBILITY_NAME,
         profOptTl.USER_PROFILE_OPTION_NAME, 
       profOpt.USER_CHANGEABLE_FLAG, 
       profOpt.USER_VISIBLE_FLAG, 
         profOpt.SITE_ENABLED_FLAG, 
       ProfOptVal.LEVEL_ID, 
       ProfOptVal.LEVEL_VALUE, 
         ProfOptVal.PROFILE_OPTION_VALUE 
'FROM   APPLSYS.FND_PROFILE_OPTIONS_TL profOptTl, ' 
APPLSYS.FND_PROFILE_OPTIONS profOpt, 
       APPLSYS.FND_PROFILE_OPTION_VALUES ProfOptVal, 
       APPLSYS.FND_RESPONSIBILITY_TL RespT
WHERE 
( profOptTl.PROFILE_OPTION_NAME=profOpt.PROFILE_OPTION_NAME ) AND 
  ( profOpt.PROFILE_OPTION_ID=ProfOptVal.PROFILE_OPTION_ID ) AND 
  ( ProfOptVal.LEVEL_VALUE=RespT.RESPONSIBILITY_ID )
AND    ProfOptVal.LEVEL_ID = 10003
  AND (UPPER(profOpt.PROFILE_OPTION_NAME) IN ('ICX_NUMERIC_CHARACTERS',  'CURRENCY:THOUSANDS_SEPARATOR',
'ICX_LANGUAGE', 'ICX_TERRITORY' )) 
'UNION ALL'
SELECT 'User',
       UserT.USER_NAME, 
         profOptTl.USER_PROFILE_OPTION_NAME, 
       profOpt.USER_CHANGEABLE_FLAG, 
       profOpt.USER_VISIBLE_FLAG, 
         profOpt.SITE_ENABLED_FLAG, 
       ProfOptVal.LEVEL_ID, 
       ProfOptVal.LEVEL_VALUE, 
         ProfOptVal.PROFILE_OPTION_VALUE 
'FROM APPLSYS.FND_PROFILE_OPTIONS_TL profOptTl,' 
    APPLSYS.FND_PROFILE_OPTIONS profOpt, 
    APPLSYS.FND_PROFILE_OPTION_VALUES ProfOptVal, 
    APPLSYS.FND_USER UserT
WHERE 
( profOptTl.PROFILE_OPTION_NAME=profOpt.PROFILE_OPTION_NAME ) 
AND   ( profOpt.PROFILE_OPTION_ID=ProfOptVal.PROFILE_OPTION_ID ) 
 AND  ( ProfOptVal.LEVEL_VALUE=UserT.USER_ID )
AND    ProfOptVal.LEVEL_ID = 10004
  AND (UPPER(profOpt.PROFILE_OPTION_NAME) IN ('ICX_NUMERIC_CHARACTERS',  'CURRENCY:THOUSANDS_SEPARATOR',
'ICX_LANGUAGE', 'ICX_TERRITORY' ));