Oracle Apps Profile options

What is  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 in Oracle Apps

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).

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 the Hierarchy type of the Profile Option

col SITE format a4 
col APPL format a4 
col RESP format a4 
col USER format a4 
col SRVR format a4 
col ORG format a3 
col svrp format a4 
set pagesize 100 
select unique profile_option_name, 
  hierarchy_type "Type", 
  WRITE_ALLOWED_FLAG, 
  READ_ALLOWED_FLAG, 
  SITE_ENABLED_FLAG || SITE_UPDATE_ALLOWED_FLAG "Site", 
  SERVERRESP_ENABLED_FLAG || SERVERRESP_UPDATE_ALLOWED_FLAG "SvRp", 
  SERVER_ENABLED_FLAG || SERVER_UPDATE_ALLOWED_FLAG "Srvr", 
  USER_ENABLED_FLAG || USER_UPDATE_ALLOWED_FLAG "User", 
  RESP_ENABLED_FLAG || RESP_UPDATE_ALLOWED_FLAG "Resp", 
  ORG_ENABLED_FLAG || ORG_UPDATE_ALLOWED_FLAG "Org", 
  APP_ENABLED_FLAG ||APP_UPDATE_ALLOWED_FLAG "Appl" 
from 
  fnd_profile_options 
where 
  profile_option_name in 
  ('APPS_WEB_AGENT','APPS_SERVLET_AGENT','APPS_JSP_AGENT','APPS_FRAMEWORK_AGENT', 
   'ICX_FORMS_LAUNCHER','ICX_DISCOVERER_LAUNCHER','ICX_DISCOVERER_VIEWER_LAUNCHER', 
   'HELP_WEB_AGENT','APPS_PORTAL','CZ_UIMGR_URL','ASO_CONFIGURATOR_URL', 
   'QP_PRICING_ENGINE_URL','TCF:HOST') 
   or hierarchy_type='SERVER' 
   or hierarchy_type='SERVRESP' 
order by hierarchy_type,profile_option_name; 
 

Example Output of Enabled QueryPROFILE_OPTION_NAME              Type     W R Site SvRp Srvr User Resp Org Appl 
-------------------------------- -------- - - ---- ---- ---- ---- ---- --- ---- 
FND_FUNCTION_VALIDATION_LEVEL    SERVER   Y Y YY   NN   YY   NN   NN   NN  NN 
FND_VALIDATION_LEVEL             SERVER   Y Y YY   NN   YY   NN   NN   NN  NN 
NODE_TRUST_LEVEL                 SERVER   Y Y YY   NN   YY   NN   NN   NN  NN 
OKS_SMTP_DOMAIN                  SERVER   Y Y YY   NN   YY   YN   NN   NN  NN 
UMX_REGISTER_HERE_HTMLPARAMS     SERVER   Y Y NN   NN   YY   NN   NN   NN  NN 
UMX_REGISTER_HERE_REGPARAMS      SERVER   Y Y NN   NN   YY   NN   NN   NN  NN 
UMX_REGISTER_HERE_REG_SRV        SERVER   Y Y NN   NN   YY   NN   NN   NN  NN 
APPS_FRAMEWORK_AGENT             SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 
APPS_JSP_AGENT                   SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 
APPS_PORTAL                      SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 
APPS_SERVLET_AGENT               SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 
APPS_WEB_AGENT                   SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 
ASO_CONFIGURATOR_URL             SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 
CZ_UIMGR_URL                     SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 
HELP_WEB_AGENT                   SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 
ICX_DISCOVERER_LAUNCHER          SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 
ICX_DISCOVERER_VIEWER_LAUNCHER   SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 
ICX_FORMS_LAUNCHER               SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 
QP_PRICING_ENGINE_URL            SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 
TCF:HOST                         SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN 

20 rows selected.

Following query can be used to find the node information from FND_NODES

set pagesize 50 
col node_name format a15 
col server_id format a8 
col server_address format a15 
col platform_code format a4 
col webhost format a12 
col domain format a20 
col virtual_ip format a12 
set linesize 132 
select 
  node_id, 
  platform_code, 
  support_db D, 
  support_cp C, 
  support_admin A, 
  support_forms F, 
  support_web W, 
  node_name, 
  server_id, 
  server_address, 
  domain, 
  webhost, 
  virtual_ip 
from 
  fnd_nodes 
order by node_id;

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 Back-end

Using FND_PROFILE API

1. How to change Profile option at the user level through PLSQL

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 from backend or how to set profile options in oracle apps from backend

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;

How to put 10046 trace at user level in Oracle apps using Profile option

DECLARE
l_ret boolean;
l_user_id number;
BEGIN

select user_id
into l_user_id
from fnd_user
where user_name = '&&USER_NAME';

l_ret := fnd_profile.SAVE(X_NAME => 'FND_INIT_SQL',
X_VALUE => 'BEGIN FND_CTL.FND_SESS_CTL('''','''','''', ''TRUE'','''',''ALTER SESSION SET TRACEFILE_IDENTIFIER=''||''''''''||''&&USER_NAME'' ||''''''''||'' EVENTS =''||''''''''||'' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 ''||''''''''); END;',
X_LEVEL_NAME => 'USER',
X_LEVEL_VALUE => l_user_id);
commit;

dbms_output.put_line('Profile has updated successfully');

EXCEPTION
when others then
dbms_output.put_line('Failed to update the profile: '||sqlerrm);
END;
/

How to delete the trace profile after the tracing is completed

DECLARE
l_ret boolean;
l_user_id number;
BEGIN

select user_id
into l_user_id
from fnd_user
where user_name = '&USER_NAME';

l_ret := fnd_profile.DELETE(X_NAME => 'FND_INIT_SQL',
X_LEVEL_NAME => 'USER',
X_LEVEL_VALUE => l_user_id);
commit;

dbms_output.put_line('Profile has erased successfully');

EXCEPTION
when others then
dbms_output.put_line('Failed to erase the profile: '||sqlerrm);
END;
/

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

Related Articles

Top Ten secret about Oracle Concurrent Manager and Types

10 Tricks You Must Know When Using Autoconfig In Oracle R12

Top 25 Useful oracle apps queries for DBA

60 Awesome oracle apps dba interview questions