Oracle Apps: Query to fetch Profile option values

Query to fetch profile option for a Specific Responsibility

SELECT

substr(pro1.user_profile_option_name,1,35) 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

pro1.user_profile_option_name like <PROFILE NAME>

and  pro.profile_option_name = pro1.profile_option_name

and  pro.profile_option_id = pov.profile_option_id

and  resp.responsibility_name like <RESP NAME>

and  pov.level_value = resp.responsibility_id (+)

and  pov.level_value = appl.application_id (+)

and  pov.level_value = u.user_id (+)

order by 1,2;

 

Query to fetch profile option values:

  SELECT fpo.profile_option_name SHORT_NAME,

         fpot.user_profile_option_name NAME,

         DECODE (fpov.level_id,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Responsibility',

                 10004, 'User',

                 10005, 'Server',

                 'UnDef')

            LEVEL_SET,

         DECODE (TO_CHAR (fpov.level_id),

                 '10001', '',

                 '10002', fap.application_short_name,

                 '10003', frsp.responsibility_key,

                 '10005', fnod.node_name,

                 '10006', hou.name,

                 '10004', fu.user_name,

                 'UnDef')

            "CONTEXT",

         fpov.profile_option_value VALUE

    FROM fnd_profile_options fpo,

         fnd_profile_option_values fpov,

         fnd_profile_options_tl fpot,

         fnd_user fu,

         fnd_application fap,

         fnd_responsibility frsp,

         fnd_nodes fnod,

         hr_operating_units hou

   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)

         AND fpo.profile_option_name = fpot.profile_option_name

         AND fu.user_id(+) = fpov.level_value

         AND frsp.application_id(+) = fpov.level_value_application_id

         AND frsp.responsibility_id(+) = fpov.level_value

         AND fap.application_id(+) = fpov.level_value

         AND fnod.node_id(+) = fpov.level_value

         AND hou.organization_id(+) = fpov.level_value

         AND fpot.user_profile_option_name IN ('&User_Profile_Option_Name')

ORDER BY short_name;