Friday, January 05, 2007

APPs:Get the Profile Option Values

Following sql script can be used as the apps user to query the profile option values.

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',, '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 '%[INSERT YOUR PROFILE VALUE HERE]%'
ORDER BY short_name,

No comments: