Oracle EBS Queries related to FND_PROFILE

Value of specific profile option at all level

select
b.user_profile_option_name "Long Name"
, a.profile_option_name "Short Name"
, decode(to_char(c.level_id),'10001','Site'
,'10002','Application'
,'10003','Responsibility'
,'10004','User'
,'Unknown') "Level"
, decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown') "Level Value"
, c.PROFILE_OPTION_VALUE "Profile Value"
, c.profile_option_id "Profile ID"
, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
from
apps.fnd_profile_options a
, apps.FND_PROFILE_OPTIONS_VL b
, apps.FND_PROFILE_OPTION_VALUES c
, apps.FND_USER d
, apps.FND_USER e
, apps.FND_RESPONSIBILITY_VL g
, apps.FND_APPLICATION h
where
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
b.user_profile_option_name like '&ProfileName' 
and a.profile_option_name = b.profile_option_name
and a.profile_option_id = c.profile_option_id
and a.application_id = c.application_id
and c.last_updated_by = d.user_id (+)
and c.level_value = e.user_id (+)
and c.level_value = g.responsibility_id (+)
and c.level_value = h.application_id (+)
order by
b.user_profile_option_name, c.level_id,
decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown');

Find profile option value from backend

SELECT
p.profile_option_name short_name,
n.user_profile_option_name name,
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005,
'Server', 10007, 'SERVRESP', 'UnDef') level_set,
decode(to_char(v.level_id),
'10001',
'',
'10002',
app.application_short_name,
'10003',
rsp.responsibility_key,
'10005',
svr.node_name,
'10006',
org.name,
'10004',
usr.user_name,
'10007',
'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value value
FROM
fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE
p.profile_option_id = v.profile_option_id (+)
AND p.profile_option_name = n.profile_option_name
AND ( upper(n.user_profile_option_name) LIKE upper('<PUT_USER_PROFILE_OPTION_NAME>')
OR upper(n.user_profile_option_name) LIKE upper('Concurrent%Active Request Limit') )
AND usr.user_id (+) = v.level_value
AND rsp.application_id (+) = v.level_value_application_id
AND rsp.responsibility_id (+) = v.level_value
AND app.application_id (+) = v.level_value
AND svr.node_id (+) = v.level_value
AND org.organization_id (+) = v.level_value
ORDER BY
4;

Modify Profile Option Value from backend

DECLARE stat boolean; 
BEGIN dbms_output.disable; 
dbms_output.enable(100000); 
stat := FND_PROFILE.SAVE('XX_PROFILE_NAME', 'XX_NEW_VALUE', 'SITE'); 
IF stat THEN dbms_output.put_line( 'Stat = TRUE - profile updated' ); 
ELSE dbms_output.put_line( 'Stat = FALSE - profile NOT updated' ); 
END IF; 
commit; 
END;
/

EXAMPLE SETTING UP “Concurrent:Active Request Limit” AT USER LEVEL

DECLARE
a BOOLEAN;
BEGIN
a := fnd_profile.SAVE ('Concurrent:Active Request Limit' , 10 , 'USER' , '<user_sso>' ); IF a THEN
DBMS_OUTPUT.put_line ('Success');
COMMIT;
ELSE
DBMS_OUTPUT.put_line ('Error');
END IF;
END;
/

REPALCE PROFILE OPTION VALUES AFTER THE CLONING PROCESS

UPDATE fnd_profile_option_values SET profile_option_value=REPLACE(profile_option_value,'https://prod_link','https://dev_link') WHERE profile_option_value LIKE 'https://prod_link%';
COMMIT;

Query to audit profile options.
Profile option with modification date and user

SELECT
t.user_profile_option_name
, profile_option_value
, v.creation_date
, v.last_update_date
, v.creation_date – v.last_update_date "Change Date"
, (SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By"
, (SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM
fnd_profile_options o
, fnd_profile_option_values v
, fnd_profile_options_tl t
WHERE
o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;

All Profile Option values in EBS instance at SITE LEVEL

SELECT
pro1.profile_option_name,
substr(pro1.user_profile_option_name,1,60) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value
FROM
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pro.profile_option_name = pro1.profile_option_name
and pro.profile_option_id = pov.profile_option_id
and pov.level_value = resp.responsibility_id (+)
and pov.level_id = 10001
and pov.level_value = appl.application_id (+)
AND resp.LANGUAGE (+) = USERENV('LANG')
and pov.level_value = u.user_id (+)
and pro1.LANGUAGE (+) = USERENV('LANG')
order by 1,2;

All Profile Option values in EBS instance at ALL LEVELS

select
b.user_profile_option_name "Long Name"
, a.profile_option_name "Short Name"
, decode(to_char(c.level_id),'10001','Site'
,'10002','Application'
,'10003','Responsibility'
,'10004','User'
,'Unknown') "Level"
, decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown') "Level Value"
, c.PROFILE_OPTION_VALUE "Profile Value"
, c.profile_option_id "Profile ID"
, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
from
apps.fnd_profile_options a
, apps.FND_PROFILE_OPTIONS_VL b
, apps.FND_PROFILE_OPTION_VALUES c
, apps.FND_USER d
, apps.FND_USER e
, apps.FND_RESPONSIBILITY_VL g
, apps.FND_APPLICATION h
where
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
--b.user_profile_option_name like '&ProfileName' -- 'AFLOG_ENABLED'
a.profile_option_name = b.profile_option_name
and a.profile_option_id = c.profile_option_id
and a.application_id = c.application_id
and c.last_updated_by = d.user_id (+)
and c.level_value = e.user_id (+)
and c.level_value = g.responsibility_id (+)
and c.level_value = h.application_id (+)
order by
b.user_profile_option_name, c.level_id,
decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown');

COMPARE PROFILE OPTIONS BETWEEN TWO INSTANCES BY CREATING DBLINK

SELECT
pro1.profile_option_name,
substr(pro1.user_profile_option_name,1,60) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value,
decode(pov_qa.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov_qa.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov_qa.profile_option_value,'Is Null') qa_Value
FROM
fnd_profile_option_values@teslink pov_qa,
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pov_qa.profile_option_id= pov.profile_option_id
and pro.profile_option_name = pro1.profile_option_name
and pro.profile_option_id = pov.profile_option_id
and pov.level_value = resp.responsibility_id (+)
and pov.level_id = 10001
and pov.level_value = appl.application_id (+)
AND resp.LANGUAGE (+) = USERENV('LANG')
and pov.level_value = u.user_id (+)
and pro1.LANGUAGE (+) = USERENV('LANG')
order by 1,2;

To display all profile options with values and levels where the word TRACE, LOG, or DEBUG is found in the PROFILE_OPTION_NAME:

select po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
from FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where (po.PROFILE_OPTION_NAME like '%TRACE%' or po.PROFILE_OPTION_NAME like '%DEBUG%' or po.PROFILE_OPTION_NAME like '%LOG%')
and pov.application_id = po.application_id
and pov.profile_option_id = po.profile_option_id
and usr.user_id (+) = pov.level_value
and rsp.application_id (+) = pov.level_value_application_id
and rsp.responsibility_id (+) = pov.level_value
and app.application_id (+) = pov.level_value
and svr.node_id (+) = pov.level_value
and org.organization_id (+) = pov.level_value
order by "NAME", pov.level_id, "VALUE";

Below will display values of profile options “FND: Debug Log Enabled” (AFLOG_ENABLED) and “Initialization SQL Statement – Custom'” (FND_INIT_SQL) at each level. If the values are respectively ‘Y’ and/or contains sql statement for these profile options then log and/or trace are enable for that level (which could be at site, user, … level):

select po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
from FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where (po.profile_option_name like '%AFLOG_ENABLED%' or po.profile_option_name like
'%FND_INIT_SQL%')
and pov.application_id = po.application_id
and pov.profile_option_id = po.profile_option_id
and usr.user_id (+) = pov.level_value
and rsp.application_id (+) = pov.level_value_application_id
and rsp.responsibility_id (+) = pov.level_value
and app.application_id (+) = pov.level_value
and svr.node_id (+) = pov.level_value
and org.organization_id (+) = pov.level_value
order by "NAME", pov.level_id, "VALUE";

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Oracle EBS Queries related to FND_PROFILE

Related posts: