Search This Blog

Monday, May 13, 2013

Query to check profile options at all levels

Query to check profile options at all levels

select 
b.user_profile_option_name "Long Name"
, a.profile_option_name "Short Name"
, decode(to_char(c.level_id),'10001','Site'
,'10002','Application'
,'10003','Responsibility'
,'10004','User'
,'Unknown') "Level"
, decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown') "Level Value"
, c.PROFILE_OPTION_VALUE "Profile Value"
, c.profile_option_id "Profile ID"
, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
from 
apps.fnd_profile_options a
, apps.FND_PROFILE_OPTIONS_VL b
, apps.FND_PROFILE_OPTION_VALUES c
, apps.FND_USER d
, apps.FND_USER e
, apps.FND_RESPONSIBILITY_VL g
, apps.FND_APPLICATION h
where 
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
b.user_profile_option_name like '&ProfileName' -- 'AFLOG_ENABLED'
and a.profile_option_name = b.profile_option_name
and a.profile_option_id = c.profile_option_id
and a.application_id = c.application_id
and c.last_updated_by = d.user_id (+)
and c.level_value = e.user_id (+)
and c.level_value = g.responsibility_id (+)
and c.level_value = h.application_id (+)
order by 
b.user_profile_option_name, c.level_id, 
decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown');

No comments:

Post a Comment