1.List E-Business Suite Profile Option Values For All Levels
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', 10006, 'Org', 10007, decode(to_char(v.level_value2), '-1', 'Responsibility', decode(to_char(v.level_value), '-1', 'Server', 'Server+Resp')), 'UnDef') LEVEL_SET, decode(to_char(v.level_id), '10001', '', '10002', app.application_short_name, '10003', rsp.responsibility_key, '10004', usr.user_name, '10005', svr.node_name, '10006', org.name, '10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key, decode(to_char(v.level_value), '-1', (SELECT node_name FROM fnd_nodes WHERE node_id = v.level_value2), (SELECT node_name FROM fnd_nodes WHERE node_id = v.level_value2) || '-' || rsp.responsibility_key)), '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(p.profile_option_name) IN (SELECT profile_option_name FROM fnd_profile_options_tl WHERE upper(user_profile_option_name) LIKE upper('%&user_profile_name%')) 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 short_name, user_profile_option_name, level_id, level_set;2.How to Search all of the Profile Options for a Specific Value
SELECT p.profile_option_name profile_option_name,
n.user_profile_option_name user_profile_option_name, DECODE(v.level_id, 10001, 'Site', 10002, 'Application', 10003, 'Responsibility', 10004, 'User', 10005, 'Server', '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, '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 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 AND v.PROFILE_OPTION_VALUE LIKE '%' ORDER BY level_set;3.How To Find All Users With A Particular Profile Option Set?
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', '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, '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 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 AND Upper(n.user_profile_option_name) LIKE upper('INV:Debug Level') ORDER BY short_name; where you will prompt for the User_Profile_Option_Name you want to check and you will put the Profile name that you want to check, for example: Apps Servlet Agent If you want to check on the users level then you can append a condition : and v.level_id = 10004, same goes for Responsibility level then append the condition v.level_id = 10003.If you want for a certain user, then you can append a condition: and usr.user_name = '&User_Name' where you will prompt for the User_Name and then you will put the user you want to check, for example: SYSADMIN
原文地址：http://www.cnblogs.com/benio/archive/2013/03/12/2955963.html