Extract profile option values for all possible levels

In Oracle E-Business Suite profile options can be set on several levels:





    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
    
                 ========================================================

SELECT b.name,

       b.organization_id,

       level_value,

       PROFILE_OPTION_VALUE

  FROM fnd_profile_option_values a, hr_operating_units b

 WHERE 1 = 1

 AND a.level_value = b.ORGANIZATION_ID

 

Extract profile option values for all possible levels

Author: Cyriel van Schaik at 12:34 AM

 0 0 0 326

In Oracle E-Business Suite profile options can be set on several levels:

• 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

- See more at: http://oracleebsapps.blogspot.in/2011/08/extract-profile-option-values-for-all.html#sthash.plOQ44mQ.dpuf