Monday, September 9, 2013
Query to find Profile Option Details at 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',
'UnDef'
) level_set,
v.level_value level_val,
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND
UPPER(n.user_profile_option_name)LIKEUPPER('%&ProfileName%');
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
3 Responses to “Query to find Profile Option Details at all levels”
October 28, 2013 at 8:15 AM
Thank you for sharing! The "LIKEUPPER" did not work for me so I changed it to be separate key words. Also I added another column to lookup the level_val if they are Application, Responsibility or User.
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',
'UnDef'
) level_set,
v.level_value level_val_id,
DECODE(v.level_id,
10001, 'Site',
10002, (SELECT application_short_name FROM fnd_application WHERE application_id = v.level_value ),
10003, (SELECT responsibility_key FROM fnd_responsibility WHERE responsibility_id = v.level_value AND application_id = v.application_id),
10004, (SELECT user_name FROM fnd_user WHERE user_id = v.level_value ),
'UnDef'
) level_val_name,
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND UPPER(n.user_profile_option_name) LIKE UPPER('%&ProfileName%');
December 3, 2013 at 9:00 AM
Thank you for sharing your query and Feedback
September 1, 2021 at 1:22 AM
Lovvely blog you have
Post a Comment