Every thing You must know about Profile options in Oracle Apps

Introduction to Profile options in Oracle Apps

A set of changeable options that affects the way your application runs.The profile option acts like a Global Variable in Oracle, to provide the flexibility in Oracle Applications

Two type of Profile options

System Profile Options :It is set by the System administrator.User cannot access it and Any change in the system profile becomes effective only when the user logs on again or change responsibility

Personal Profile Options: It is set by the System Administrator.User can change the option values.Any changes become effective immediately

Profile Hierarchy
A profile option can be set at one or more levels, depending on its hierarchy type. Most profile options use the Security hierarchy type, meaning that they can potentially be set at the four levels: Site (lowest level) , Application, Responsibility, and User (highest level).

Profile options in Oracle Apps

Some Important points about Profile Hierarchy
1) A value set at the higher level override the one set at the lower level
2)After implementation System Administrator sets the default profile values at the site level
3)Option values are dynamically set at the run time

Hierarchy Types
Hierarchy types enable system administrators to group and set profile options according to their business needs or the needs of the installation.
There are several hierarchy types: Security, Organization, Server, and Server+Responsibility.

 

Security Security is the default hierarchy type. Profiles that use this hierarchy type follow the hierarchy: Site – Application – Responsibility – User.

Note: Most profile options that existed before hierarchy type was introduced use Security.

 

Organization Organization refers to operating unit. For example, clerks in different organizations may need to have different values for a given profile option, depending on their organization, but clerks in the same organization would use the same value. The Organization hierarchy type allows system administrators to set a profile option at the organization level, so that all users within that organization will use the profile option value set once at the organization level. Profiles using this hierarchy type follow the hierarchy Site – Organization – User.
Server

 

The Server hierarchy type is used when the system needs to determine the middle-tier server on which the user’s session is running. For example, the profile “Applications Web Agent” can be defined using the Server type. The setting of this profile option can differ for an internal server versus an external one. Cookie validation, can then be done against the value of this profile option. Profiles using this hierarchy type follow the hierarchy Site – Server – User.

 

Server+Responsibility

 

The Server+Responsibility hierarchy type allows you to set distinct profile values for specific combinations of server and responsibility. When evaluating profile values to use, the value found with the most specific match across all levels is chosen. At any level, a special default value can be chosen in case no other specific match at that level is found.

Either or both of the responsibility or server may have specific values, or may be the default value. For purposes of evaluating default matches, the server is considered to be at a lower level and less specific than the responsibility.

When evaluating profile values at this Server+Responsibility level, the system first looks for a specific match for both the responsibility and server level values. If no such match is found, it looks for a row matching responsibility and default for the server level. If no such match is found, it will next look for a row matching the server with default for the responsibility level. If no such match is found, it will continue up the hierarchy to the Site level.

Tables involved in Profile Option

FND_PROFILE_OPTIONS

FND_PROFILE_OPTIONS_TL

FND_PROFILE_OPTIONS_VALUES

How to check Profile Option values

We can either check it through front end   or backend sql query

  1. Navigation for Front end

Go to system administrator responsibility  -> Profile -> System Profile Option

2)  We can check Profile option through backend also using two method

a) FND_PROFILE API

b) Sql query to the profile option tables

Using FND_PROFILE API to check Site level Profile value

Site level values of Profile can be found as

select FND_PROFILE.value(‘<profile_option_name’) from dual;

select FND_PROFILE.value(‘APPS_AUTH_AGENT’) from dual;

select FND_PROFILE.value(‘APPS_SSO_LDAP_SYNC’) from dual;

select  fnd_profile.value(‘SIGNON_PASSWORD_LENGTH’) from dual;

Profile_option_name is the name of Profile stored in FND_PROFILE_OPTION table

Using Query to check site level value

set linesize 140

set pagesize 132

col “PROFILE NAME” format A30

col “Update Time” format A22

col VALUE format a35;

col USER format a30;

col LEVEL format a6;

select

n.user_profile_option_name “PROFILE NAME”,

decode(v.level_id, 10001,’Site’,10002,’Application’,10003,’Responsibility’,10004,’User’,10005,’Server’,10006,’Organization’,’UnDef’) “LEVEL”,

v.profile_option_value VALUE,

to_char(v.LAST_UPDATE_DATE,’DD-MON-YYYY:hh24:mi:ss’) “Update Time”,

fu.user_name “USER”

from apps.fnd_profile_options p,

apps.fnd_profile_option_values v,

apps.fnd_profile_options_tl n,

apps.fnd_user fu

where p.profile_option_id = v.profile_option_id (+)

and p.profile_option_name = n.profile_option_name

AND v.last_updated_by=fu.user_id

and upper(n.user_profile_option_name) like upper(‘&profile_name%’)

and v.level_id= 10004 ;

Here profile_name is the User profile name

Some other Important queries to check Profile

  1. Checking the Profile at a particular level

Using FND_PROFILE API

select fnd_profile.value_specific(‘<profile_option_name>’,’USER_ID’,’RESP_ID’,’APPL_ID’,’ORG_ID’,’SERVER_ID’) from dual;

To check profile_option_value at the user level

select fnd_profile.value_specific(‘APPS_FRAMEWORK_AGENT’,'<User_id>’) from dual;

Using direct query to table

set linesize 140

set pagesize 132

col “PROFILE NAME” format A30

col “Update Time” format A22

col VALUE format a35;

col USER format a30;

col LEVEL format a6;

select

n.user_profile_option_name “PROFILE NAME”,

decode(v.level_id, 10001,’Site’,10002,’Application’,10003,’Responsibility’,10004,’User’,10005,’Server’,10006,’Organization’,’UnDef’) “LEVEL”,

v.profile_option_value VALUE,

to_char(v.LAST_UPDATE_DATE,’DD-MON-YYYY:hh24:mi:ss’) “Update Time”,

fu.user_name “USER”

from apps.fnd_profile_options p,

apps.fnd_profile_option_values v,

apps.fnd_profile_options_tl n,

apps.fnd_user fu

where p.profile_option_id = v.profile_option_id (+)

and p.profile_option_name = n.profile_option_name

AND v.last_updated_by=fu.user_id

and upper(n.user_profile_option_name) like upper(‘&profile_name%’)

and v.level_id= &level_id;

The above query will list the profile level values at the particular level.

We can add more filter to check the profile at user level

set linesize 140

set pagesize 132

col “PROFILE NAME” format A30

col “Update Time” format A22

col VALUE format a35;

col USER format a30;

col LEVEL format a6;

select

n.user_profile_option_name “PROFILE NAME”,

decode(v.level_id, 10001,’Site’,10002,’Application’,10003,’Responsibility’,10004,’User’,10005,’Server’,10006,’Organization’,’UnDef’) “LEVEL”,

v.profile_option_value VALUE,

to_char(v.LAST_UPDATE_DATE,’DD-MON-YYYY:hh24:mi:ss’) “Update Time”,

fu.user_name “USER”

from apps.fnd_profile_options p,

apps.fnd_profile_option_values v,

apps.fnd_profile_options_tl n,

apps.fnd_user fu

where p.profile_option_id = v.profile_option_id (+)

and p.profile_option_name = n.profile_option_name

AND v.last_updated_by=fu.user_id

and upper(n.user_profile_option_name) like upper(‘&profile_name%’)

and v.level_id= 10004 ;

and v.level_value=(select user_id from apps.fnd_user where user_name=’&user’);

2)  Checking profile changed in last 3 days

set linesize 140

set pagesize 132

col “PROFILE NAME” format A30

col “Update Time” format A22

col VALUE format a35;

col USER format a30;

col LEVEL format a6;

select

n.user_profile_option_name “PROFILE NAME”,  p.profile_option_name,

decode(v.level_id, 10001,’Site’,10002,’Application’,10003,’Responsibility’,10004,’User’,10005,’Server’,10006,’Organization’,’UnDef’) “LEVEL”,

v.profile_option_value VALUE,

to_char(v.LAST_UPDATE_DATE,’DD-MON-YYYY:hh24:mi:ss’) “Update Time”,

fu.user_name “USER”

,v.level_value

from apps.fnd_profile_options p,

apps.fnd_profile_option_values v,

apps.fnd_profile_options_tl n,

apps.fnd_user fu

where p.profile_option_id = v.profile_option_id (+)

and p.profile_option_name = n.profile_option_name

AND v.last_updated_by=fu.user_id

and v.last_update_date > sysdate -3

–and v.level_id= 10001;

3) Checking the Important login system profiles

select profile_option_value from apps.fnd_profile_option_values where level_id=10001 and PROFILE_OPTION_ID  in (select profile_option_id from apps.fnd_profile_options where profile_option_name in (‘APPS_FRAMEWORK_AGENT’,’HELP_WEB_AGENT’,’APPS_JSP_AGENT’,’APPS_PORTAL’,

‘CZ_UIMGR_URL’, ‘ASO_CONFIGURATOR_URL’,’APPS_SERVLET_AGENT’,’APPS_WEB_AGENT’

,’ICX_FORMS_LAUNCHER’)) ;

How to change the  Profile options in Oracle Apps

We can change either through Front or backend

Navigation will remain same for Front end. We will discuss more about changing from Backend

Using FND_PROFILE API

  1. How to change Profile option at the user level
declare

retval boolean;

cursor cur is select * from fnd_user

where user_name= ‘&1’

begin

for rec in cur

loop

dbms_output.put_line ( rec.user_name );

retval:=fnd_profile.save(‘<profile_option_name’’,'<value>’,’USER’,rec.user_id);

end loop;

end;

/

2) How to change the site level profile

Set serveroutput on

declare

dummy boolean;

begin

dummy := APPS.FND_PROFILE.SAVE(‘<profile_option_name>’,’<value>’ ,’SITE’);

if not dummy then

dbms_output.put_line( ‘Error setting the Profile option’ );

end if;

end;

/

Using direct query method

This method requires more knowledge as you need to know the Profile_option_id

Changing the Profile value at the user level

update FND_PROFILE_OPTION_VALUES

set PROFILE_OPTION_VALUE=<value>

where PROFILE_OPTION_ID = &1

and level_value=(select user_id from fnd_user where user_name=’&1′)

and level_id=10004;

Changing the Profile value at the site level

update FND_PROFILE_OPTION_VALUES

set PROFILE_OPTION_VALUE=<value>

where PROFILE_OPTION_ID = &1

and level_id=10001;

I hope you must have like the content on Profile options in Oracle Apps. Please do provide feedback