Posted By

theonlyalterego on 08/10/12

Tagged

sql
Oracle

Versions (?)

Last Edited at 08/10/12 04:24am

Statistics

Viewed 804 times

Favorited by 0 user(s)

Related snippets

Oracle Find Profile Values for User

/ Published in: SQL

find what profile values are set for a particular user

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
        decode(to_char(pov.level_id),
               '10001', 'SITE',
               '10002', 'APP',
               '10003', 'RESP',
               '10005', 'SERVER',
               '10006', 'ORG',
               '10004', 'USER', '???') "LEV",
        decode(to_char(pov.level_id),
               '10001', '',
               '10002', app.application_short_name,
               '10003', rsp.responsibility_key,
               '10005', svr.node_name,
               '10006', org.name,
               '10004', usr.user_name,
               '???') "CONTEXT",
        pov.profile_option_value "VALUE"
 FROM   FND_PROFILE_OPTIONS_VL po,
        FND_PROFILE_OPTION_VALUES pov,
        fnd_user usr,
        fnd_application app,
        fnd_responsibility rsp,
        fnd_nodes svr,
        hr_operating_units org
 WHERE  po.profile_option_name LIKE :profile_name
 AND    pov.application_id = po.application_id
 AND    pov.profile_option_id = po.profile_option_id
 AND    usr.user_id (+) = pov.level_value
 AND    rsp.application_id (+) = pov.level_value_application_id
 AND    rsp.responsibility_id (+) = pov.level_value
 AND    app.application_id (+) = pov.level_value
 AND    svr.node_id (+) = pov.level_value
 AND    org.organization_id (+) = pov.level_value
 AND decode(to_char(pov.level_id),
               '10001', '',
               '10002', app.application_short_name,
               '10003', rsp.responsibility_key,
               '10005', svr.node_name,
               '10006', org.name,
               '10004', usr.user_name,
               '???') LIKE :user_name
 ORDER BY "NAME", pov.level_id, "VALUE";