Last updated on June 17th, 2019 at 04:00 am
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
- 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
- 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
- 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
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