Monday, September 16, 2013

User Profile Values updated by Whom and When?

SELECT   t.user_profile_option_name, profile_option_value, v.creation_date,
         v.last_update_date, v.creation_date, v.last_update_date "ChangeDate",
         (SELECT UNIQUE user_name
                   FROM fnd_user
                  WHERE user_id = v.created_by) "Created By",
         (SELECT user_name
            FROM fnd_user
           WHERE user_id = v.last_updated_by) "LastUpdatedBy"
    FROM fnd_profile_options o,
         fnd_profile_option_values v,
         fnd_profile_options_tl t
   WHERE o.profile_option_id = v.profile_option_id
     AND o.application_id = v.application_id
     AND start_date_active <= SYSDATE
     AND NVL (end_date_active, SYSDATE) >= SYSDATE
     AND o.profile_option_name = t.profile_option_name
     AND level_id = 10001
     AND t.LANGUAGE IN (SELECT language_code
                          FROM fnd_languages
                         WHERE installed_flag = 'B'
                        UNION
                        SELECT nls_language
                          FROM fnd_languages
                         WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;

How to view Profile Option values from Backend for all Levels

SELECT p.profile_option_name short_name, n.user_profile_option_name NAME,
       DECODE (v.level_id,
               10001, 'Site',
               10002, 'Application',
               10003, 'Responsibility',
               10004, 'User',
               'UnDef'
              ) level_set,
       v.level_value level_val, v.profile_option_value VALUE
  FROM fnd_profile_options p,
       fnd_profile_option_values v,
       fnd_profile_options_tl n
 WHERE p.profile_option_id = v.profile_option_id(+)
   AND p.profile_option_name = n.profile_option_name
   AND UPPER(n.user_profile_option_name)LIKEUPPER('%&ProfileName%');