Month: February 2016

Query List of Profile Options Oracle R12

andreasvictorLeave a comment 
/* Formatted on 2/26/2016 9:38:27 AM (QP5 v5.163.1008.3004) */
  SELECT b.user_profile_option_name "Long Name",
         a.profile_option_name "Short Name",
         DECODE (TO_CHAR (c.level_id),
                 '10001', 'Site',
                 '10002', 'Application',
                 '10003', 'Responsibility',
                 '10004', 'User',
                 'Unknown')
            "Level",
         DECODE (
            TO_CHAR (c.level_id),
            '10001', 'Site',
            '10002', NVL (h.application_short_name, TO_CHAR (c.level_value)),
            '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
            '10004', NVL (e.user_name, TO_CHAR (c.level_value)),
            'Unknown')
            "Level Value",
         c.PROFILE_OPTION_VALUE "Profile Value",
         c.profile_option_id "Profile ID",
         TO_CHAR (c.LAST_UPDATE_DATE, 'DD-MON-YYYY HH24:MI') "Updated Date",
         NVL (d.user_name, TO_CHAR (c.last_updated_by)) "Updated By"
    FROM apps.fnd_profile_options a,
         apps.FND_PROFILE_OPTIONS_VL b,
         apps.FND_PROFILE_OPTION_VALUES c,
         apps.FND_USER d,
         apps.FND_USER e,
         apps.FND_RESPONSIBILITY_VL g,
         apps.FND_APPLICATION h
   WHERE     1 = 1
         AND a.profile_option_name = b.profile_option_name
         AND a.profile_option_id = c.profile_option_id
         AND a.application_id = c.application_id
         AND c.last_updated_by = d.user_id(+)
         AND c.level_value = e.user_id(+)
         AND c.level_value = g.responsibility_id(+)
         AND c.level_value = h.application_id(+)
         AND UPPER(b.user_profile_option_name) LIKE '%'||:P_PROFILE_NAME||'%'
ORDER BY b.user_profile_option_name,
         c.level_id,
         DECODE (
            TO_CHAR (c.level_id),
            '10001', 'Site',
            '10002', NVL (h.application_short_name, TO_CHAR (c.level_value)),
            '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
            '10004', NVL (e.user_name, TO_CHAR (c.level_value)),
            'Unknown');
Advertisement