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%');

3 Responses to “Query to find Profile Option Details at all levels”

zcat08 said...
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%');


Team search said...
December 3, 2013 at 9:00 AM

Thank you for sharing your query and Feedback


Rose W said...
September 1, 2021 at 1:22 AM

Lovvely blog you have


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.