Wednesday, 4 June 2014

Query to check value of Profile Options in R12

/* Formatted on 6/4/2014 3:15:59 PM (QP5 v5.115.810.9015) */
SELECT p.profile_option_name short_name,
       n.user_profile_option_name name,
       TO_CHAR (v.last_update_date, 'DD-MON-RR') "Last Updated",
       DECODE (v.level_id,
          10001, 'Site',
          10002, 'Application',
          10003, 'Responsibility',
          10004, 'User',
          10005, 'Server',
          10007, 'SERVRESP',
          'UnDef')
          level_set,
       DECODE (TO_CHAR (v.level_id),
          '10001', '',
          '10002', app.application_short_name,
          '10003', rsp.responsibility_key,
          '10005', svr.node_name,
          '10006', org.name,
          '10004', usr.user_name,
          '10007', 'Serv/resp',
          'UnDef')
          "CONTEXT",
       v.profile_option_value VALUE
FROM fnd_profile_options p,
     fnd_profile_option_values v,
     fnd_profile_options_tl n,
     fnd_user usr,
     fnd_application app,
     fnd_responsibility rsp,
     fnd_nodes svr,
     hr_operating_units org
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) LIKE UPPER ('%&profile_name%')
      AND usr.user_id(+) = v.level_value
      AND rsp.application_id(+) = v.level_value_application_id
      AND rsp.responsibility_id(+) = v.level_value
      AND app.application_id(+) = v.level_value
      AND svr.node_id(+) = v.level_value
      AND org.organization_id(+) = v.level_value
ORDER BY short_name, level_set

No comments:

Post a Comment