Tuesday 13 March 2012

Profile Value at any Level

Below is another handy query to get profile Values at all levels.
The query prompts for 2 parameters, the profile name and the level name that could be application/responsibility/username. Its a wild search so will retrieve data if any of parameter is passed a blank.



SELECT   distinct pot.user_profile_option_name Profile     , DECODE (a.profile_option_value             , '1', '1 (may be "Yes")'             , '2', '2 (may be "No")'             , a.profile_option_value              ) Value     , DECODE (a.level_id             , 10001, 'Site'             , 10002, 'Application'             , 10003, 'Responsibility'             , 10004, 'User'             , '????'              ) Level_identifier     , DECODE (a.level_id             , 10002, e.application_name             , 10003, c.responsibility_name             , 10004, d.user_name             , '-'              ) Level_NameFROM     applsys.fnd_application_tl e     , applsys.fnd_user d     , applsys.fnd_responsibility_tl c     , applsys.fnd_profile_option_values a     , applsys.fnd_profile_options b     , applsys.fnd_profile_options_tl potWHERE    1=1AND      UPPER (pot.user_profile_option_name) LIKE UPPER ('%&v_profile%')AND      pot.profile_option_name = b.profile_option_nameAND      b.application_id = a.application_id(+)AND      b.profile_option_id = a.profile_option_id(+)AND      a.level_value = c.responsibility_id(+)AND      a.level_value = d.user_id(+)AND      a.level_value = e.application_id(+)AND      (   UPPER (e.application_name) LIKE UPPER ('%&appname_respname_username%')        OR UPPER (c.responsibility_name) LIKE UPPER ('%&&appname_respname_username%')        OR UPPER (d.user_name) LIKE UPPER ('%&&appname_respname_username%')       )ORDER BY Profile     , Level_identifier     , Level_name     , Value

   

Labels: FND, Query

No comments:

Post a Comment