Friday, March 28, 2014

11i profile options recently changed

SELECT 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, 'Appl'
          , 10003, 'Resp'
          , 10004, 'User'
          , '????') "Level"
 , DECODE( a.level_id
          , 10002, e.application_name
          , 10003, c.responsibility_name
          , 10004, d.user_name
          , '-') "Location", a.LAST_UPDATE_DATE
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 pot.profile_option_name = b.profile_option_name
AND UPPER(pot.user_profile_option_name) LIKE UPPER('BNE%')
 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 e.LAST_UPDATE_DATE is not null
 --AND( UPPER( e.application_name) LIKE UPPER( 'username%')
 --OR UPPER( c.responsibility_name) LIKE UPPER( 'username%')
 --OR UPPER( d.user_name) LIKE UPPER( '%username%'))
 ORDER BY a.LAST_UPDATE_DATE desc, "Profile", "Level", "Location", "Value";

No comments:

Post a Comment