Wednesday, July 31, 2013

How to find assigned Profile values from backend at different levels

This query will give the values of the profile option set at different level..
just use the profile option name or user profile option name which you see in the front end.
to query

SELECT user_profile_option_name,SUBSTR(e.profile_option_name,1,25) PROFILE
,DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Resp',10004,'User') "Level"
,DECODE(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue
,NVL(a.profile_option_value,'Is Null') Value
,SUBSTR(a.last_update_date,1,25) UPDATED_DATE
FROM fnd_profile_option_values a
, fnd_responsibility_tl b
, fnd_application c
,fnd_user d
, fnd_profile_options e
, fnd_profile_options_tl f
WHERE 1=1
--and e.profile_option_name IN ('FND_BRANDING_SIZE','ASO_COMP_LOGO')
and f.user_profile_option_name in ('FND: Branding Size')
AND e.profile_option_id = a.profile_option_id AND a.level_value = b.responsibility_id (+)
AND a.level_value = c.application_id (+) AND a.level_value = d.user_id (+)
and f.PROFILE_OPTION_NAME(+)=e.profile_option_name
ORDER BY e.profile_option_name;

No comments:

Post a Comment