Wednesday 19 June 2013

Profile Option - Customized

SELECT DECODE(RTRIM(fu1.user_name)
               ,'AUTOINSTALL', 'A'
               ,'B')                                order_by_user
  ,      app.application_short_name                       application_name
  ,      pop.profile_option_name               user_profile_option_name
  ,      SUBSTR(fu1.user_name, 1, 30)||' (last updated by: '
                                     ||SUBSTR(fu2.user_name, 1, 30)
                                     ||')'          user_name
  ,      DECODE(pop.site_enabled_flag, 'Y', 'Yes'
                        , 'N', 'No'
                        , pop.site_enabled_flag)    site_enabled_text
  ,      DECODE(pop.app_enabled_flag, 'Y', 'Yes'
                        , 'N', 'No'
                        , pop.app_enabled_flag)     app_enabled_text
  ,      DECODE(pop.resp_enabled_flag, 'Y', 'Yes'
                        , 'N', 'No'
                        , pop.resp_enabled_flag)    resp_enabled_text
  ,      DECODE(pop.user_enabled_flag, 'Y', 'Yes'
                        , 'N', 'No'
                        , pop.user_enabled_flag)    user_enabled_text
  ,      TO_CHAR(pop.end_date_active, 'DD-MON-YY')  end_date_active
  ,      to_char(app.application_id)                         application_id
  ,      to_char(pop.profile_option_id)                      profile_option_id
  FROM fnd_profile_options pop
  ,    fnd_application     app
  ,    fnd_user            fu1
  ,    fnd_user            fu2
  WHERE app.application_id = pop.application_id
  AND   pop.last_updated_by = fu2.user_id
  AND   (   (    pop.created_by  = fu1.user_id
             AND (fu1.user_id NOT IN (-1,0,1,2)))
         OR (    pop.created_by       = fu1.user_id
             AND fu1.user_id IN (-1,0,1,2)
             AND pop.last_updated_by != fu1.user_id
             AND pop.last_updated_by NOT IN (-1,0,1,2)))
  ORDER BY 1, 2, 3

No comments:

Post a Comment