You probably know the User Preferences link at the right top of your E-Business Suite homepage. On this page the users can change some of the behaviour, number and date formats of their Oracle E-Business Suite sessions.
By default, every user has the default settings/preferences as being applied to the instance but they have the opportunity to change these settings for their own purpose. E-Business Suite maintains these changes as Profile Options on the specific user level.
To know what an overridden setting is for an user is for example handy when you want to apply these format to your custom reports. Of course, if there is nothing changed by the user the default system settings are used.
Find below a small list of User Preferences with the related profile option name for you to use in your own SQL or Profile Option API call.
- Default Application Language
Profile Option Name: ICX_LANGUAGE | User Profile Option Name: ICX: Language - Accessibility Features
Profile Option Name: ICX_ACCESSIBILITY_FEATURES | User Profile Option Name: Self Service Accessibility Features - Territory
Profile Option Name: ICX_TERRITORY | User Profile Option Name: ICX: Territory - Date Format
Profile Option Name: ICX_DATE_FORMAT_MASK | User Profile Option Name: ICX: Date format mask - Timezone
Profile Option Name: CLIENT_TIMEZONE_ID | User Profile Option Name: Client Timezone - Number Format
Profile Option Name: ICX_NUMERIC_CHARACTERS | User Profile Option Name: ICX: Numeric characters - Currency
Profile Option Name: ICX_PREFERRED_CURRENCY and JTF_PROFILE_DEFAULT_CURRENCY | User Profile Option Name: ICX: Preferred Currency and JTF_PROFILE_DEFAULT_CURRENCY - Client Character Encoding
Profile Option Name: FND_NATIVE_CLIENT_ENCODING | User Profile Option Name: FND: NATIVE CLIENT ENCODING - Start Page
Profile Option Name: APPLICATIONS_START_PAGE | User Profile Option Name: Applications Start page
So, you can query the FND_PROFILE_OPTION_VALUES table from the APPLSYS schema to find an overridden value for for example the Number Format preference for an user by using the below SQL:
SELECT
VAL.PROFILE_OPTION_VALUE
FROM
APPLSYS.FND_PROFILE_OPTION_VALUES VAL,
APPLSYS.FND_PROFILE_OPTIONS OPT,
APPLSYS.FND_USER FUSER
WHERE
VAL.PROFILE_OPTION_ID = OPT.PROFILE_OPTION_ID
AND VAL.LEVEL_VALUE = FUSER.USER_ID
AND OPT.PROFILE_OPTION_NAME = 'ICX_NUMERIC_CHARACTERS'
AND VAL.LEVEL_ID = 10004 -- USER LEVEL
AND FUSER.USER_NAME = 'SCHAIKC';Or call the profile option API to get the value of the user:
SELECT FND_PROFILE.VALUE_SPECIFIC('ICX_NUMERIC_CHARACTERS',<USER_ID>) FROM DUAL;I hope the above can help when you just wonder where those preferences are. Feel free to comment!
Hi.,
Thanks for the SQL to identify these preferences set at User level.,
My PL/SQL written returns different value depending upon the TERRITORY settings at User Level.,
For example TO_CHAR(’20/09/2012′,’D’) returns different value 6 and 7, for user territory settings United Arab Emirates and United States.,
Are you aware of this.,
By the way, irrespective, how do I globally set these values to “United States” and hide these through Personalization (Territory)
any help would be appreciated.,
Regards,
Ram
n_ram@hotmail.com
Hi Ram,
This is related to the timezone being active during the session by the user. Depending on the timezone a call to to_char to get the day of the week might return for example a 6 or 7 depending on the hours compared to GMT. Check out this article for more details on this: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm
Regards,
Cyriel
Hi Ram,
As an addition on my earlier reply I did some golddigging in my old project files and found a codepart where I probably had the same issue also with deriving the day of the week based on an incoming date.
I fixed that by using the mod function together with a dummy date. This works independent of the nls_territoty and timezone settings being applied on the session.
See below:
SELECT mod(date ‘2012-10-24’ – date ‘1000-01-01’,7)+1 FROM DUAL;
This will return 1 for a monday, 2 for a tuesday, 3 for a wednesday etc.
Hope it helps.
Regards,
Cyriel
Hi Cyriel,
thanks for your reply.,
I do not prefer the previous note of using “Alter Session”, as we need to reset back the time zone carefully and if not lead to other complications within the application., don’t wanna disturb these., as it is doing some job!!
Let me try SELECT mod(date ‘2012-10-24’ – date ‘1000-01-01’,7)+1 FROM DUAL, within my Function level (Safe game!)
I will update with the result.,
thanks again.,
Best,
n_ram@hotmail.com
Hi Ram,
Totally agree with you regarding the ALTER SESSION call…..:-)
All the best,
Cyriel
I am not able to set the Client Time Zone for the users. It’s not allowing me to assign any value. I am using system administrator responsibility. Can some one help with this.
Hi,
I am trying to use TO_CHAR(ael.accounted_dr,’FM999G999G999G999G999G990D00′) but it does not fetch the user preference when the query is executed from host program.
DO let m know if you have any idea about this issue
Regards
Ramesh
Hi,
Is there any way I can hide the “Preferences” link at site level ?
We do not want users to play around with these preferences.
Thanks
Thank you very much. it really help me.
thanks a lot. good information.
Cyriel van Schaik…..u da maan!!!
Oracle Home Page on the “Preferences” how to automatically clear the value in the Default Start Page, when the attached responsibility access for the user is inactivated. For Example, user has setup his preference start page responsibility as Self services time and page as Time entry, and this responsibility access to the user is enddated, so the same time the value from user preference also need to be remove.