Thursday, 8 November 2012

Query : Profile Options Value at all Levels

SELECT pot.user_profile_option_name "Profile",pot.profile_option_name,
       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, 'Appl',
                           10003, 'Resp',
                           10004, 'User',
                           10006, 'Org', '????') "Level",
       DECODE (a.level_id, 10002, e.application_name,
                           10003, c.responsibility_name,
                           10004, d.user_name,
                           10006, a.level_value, '-') "Location",a.*
FROM 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 pot
WHERE    b.profile_option_name like 'XX%D%B%LINK%'
      AND pot.profile_option_name = b.profile_option_name
      AND 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 c.language (+)= 'US'
      AND e.language (+)= 'US'
      AND pot.language (+)= 'US'           
ORDER BY "Profile", "Level", "Location", "Value",5

No comments:

Post a Comment