Profile Option values for all levels using SQL*Plus.


How to list E-Business Suite Profile Option values for all levels using SQL*Plus.

Connect to the eBusiness Suite database using APPS schema name

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;

 

Thanks – Shivmohan Purohit

One thought on “Profile Option values for all levels using SQL*Plus.

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s