Post a Comment On: Oracle Help

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;