Thursday, April 24, 2008

Getting Profile values 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;

2 comments:

Anonymous said...

Thank you!

Unknown said...
This comment has been removed by the author.