- Site
- Application
- Responsibility
- Server
- Server with Responsibility
- Organization
- User
When needed one and the same profile option can be assigned to different levels. For example, when you implement Global Security Profiles to create access control on Operation Units – every responsibility for an Operating Unit may need a setting for profile options MO: Security Profile and HR: Security Profile. In the form which is used to set profile options all those different responsibilities can’t be seen at once.
In that case I use the SQL statement below to quickly provide me a list of the values of a profile option for all levels.
The profile option name (column profile_option_name from table applsys.fnd_profile_options) can be found within the definition of the profile itself through responsibility Application Developer – menu Profile.
Here’s the SQL to provide you the values on all levels of a specific profile.
SELECT
SUBSTR(e.profile_option_name,1,25) INTERNAL_NAME,
SUBSTR(pot.user_profile_option_name,1,60) NAME_IN_FORMS,
DECODE(a.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,
10004,’User’,10005,’Server’,10007,’Server+Resp’,a.level_id) LEVELl,
DECODE(a.level_id,10001,’Site’,10002,c.application_short_name,
10003,b.responsibility_name,10004,d.user_name,10005,n.node_name,
10007,m.node_name||’ + ‘||b.responsibility_name,a.level_id) LEVEL_VALUE,
NVL(a.profile_option_value,’Is Null’) VALUE,
to_char(a.last_update_date, ‘DD-MON-YYYY HH24:MI’) LAST_UPDATE_DATE,
dd.USER_NAME LAST_UPDATE_USER
FROM
applsys.fnd_profile_option_values a,
applsys.fnd_responsibility_tl b,
applsys.fnd_application c,
applsys.fnd_user d,
applsys.fnd_profile_options e,
applsys.fnd_nodes n,
applsys.fnd_nodes m,
applsys.fnd_responsibility_tl x,
applsys.fnd_user dd,
applsys.fnd_profile_options_tl pot
WHERE
e.profile_option_name = ‘XLA_MO_SECURITY_PROFILE_LEVEL’ AND e.PROFILE_OPTION_NAME = pot.profile_option_name (+)
AND e.profile_option_id = a.profile_option_id (+)
AND a.level_value = b.responsibility_id (+)
AND a.level_value = c.application_id (+)
AND a.level_value = d.user_id (+)
AND a.level_value = n.node_id (+)
AND a.LEVEL_VALUE_APPLICATION_ID = x.responsibility_id (+)
AND a.level_value2 = m.node_id (+)
AND a.LAST_UPDATED_BY = dd.USER_ID (+)
AND pot.LANGUAGE = ‘US’
ORDER BY
e.profile_option_name
Recent Comments