Wednesday, 19 June 2013

Profile Option - User Level

SELECT DECODE(RTRIM(fu1.user_name)
               ,'AUTOINSTALL', 'A'
               ,'B')                        order_by_user
  ,      ap1.application_short_name               application_name
  ,      pos.profile_option_name       user_profile_option_name
  ,      fu3.user_name                      user_level_value
  ,      pov.profile_option_value           profile_option_value
  ,      SUBSTR(fu1.user_name, 1, 30)||' (last updated by: '
                                     ||SUBSTR(fu2.user_name, 1, 30)
                                     ||')'          user_name
  FROM fnd_application     ap1
  ,    fnd_user            fu1
  ,    fnd_user            fu2
  ,    fnd_user            fu3
  ,    fnd_profile_options       pos
  ,    fnd_profile_option_values pov
  WHERE ap1.application_id                 = pov.application_id
  AND   pov.level_value                    = fu3.user_id
  AND   pov.last_updated_by                = fu2.user_id
  AND   (   (    pov.created_by  = fu1.user_id
             AND (fu1.user_id NOT IN (-1,0,1,2)))
         OR (    pov.created_by       = fu1.user_id
             AND fu1.user_id IN (-1,0,1,2)
             AND pov.last_updated_by != fu1.user_id
             AND pov.last_updated_by NOT IN (-1,0,1,2)))
  AND   pov.application_id    = pos.application_id
  AND   pov.profile_option_id = pos.profile_option_id
  ORDER BY 1, 2, 3

No comments:

Post a Comment