Monday 15 January 2018

Query to get the Profile Option Configurations

/* Formatted on 1/15/2018 9:40:58 AM (QP5 v5.114.809.3010) */
  SELECT   ot.user_profile_option_name,
           TO_CHAR (v.level_id) level_id,
           DECODE (v.level_id,
                   10001, 'Site',
                   10002, 'Application',
                   10003, 'Responsibility',
                   10004, 'User ID',
                   v.level_id)
              level_meaning,
           DECODE (v.level_id,
                   10001, 'Site',
                   10002, apl.application_name,
                   10003, frt.responsibility_name,
                   10004, u.user_name,
                   v.level_id)
              level_name,
           v.profile_option_value,
           o.profile_option_name,
           v.creation_date value_creation_date,
           v.created_by value_created_by,
           v.last_update_date value_last_updated_date,
           v.last_updated_by value_last_updated_by
    FROM   applsys.fnd_profile_options_tl ot,
           applsys.fnd_profile_options o,
           applsys.fnd_profile_option_values v,
           applsys.fnd_responsibility_tl frt,
           apps.fnd_application_vl apl,
           fnd_user u
   WHERE       v.level_value = frt.responsibility_id(+)
           AND v.profile_option_id = o.profile_option_id
           AND o.profile_option_name = ot.profile_option_name
           AND ot.language = 'US'
           AND NVL (frt.language, 'US') = 'US'
           AND v.level_value = apl.application_id(+)
           AND u.user_id(+) = v.level_value
ORDER BY   ot.user_profile_option_name,
           v.level_id,
           DECODE (v.level_id,
                   10001, 'Site',
                   10002, 'Application',
                   10003, frt.responsibility_name,
                   10004, u.user_name,
                   v.level_id);