Friday, October 7, 2011

List profile options and their values

This select will give you all the values for a specific profile option.
SELECT potl.language,
       potl.user_profile_option_name,
       potl.description,
       po.profile_option_id,
       DECODE(pov.level_id, 10001, 'Site',
                            10002, 'Application',
                            10003, 'Responsibility',
                            10004, 'User',
                            10005, 'Server',
                            10006, 'Org',
                            10007, DECODE(to_char(pov.level_value2), '-1', 'Responsibility',
                                   DECODE(to_char(pov.level_value), '-1', 'Server',
                                   'Server+Resp')),
                            'UnDef') level_set,
       DECODE(to_char(pov.level_id), '10001', '',
                                     '10002', app.application_short_name,
                                     '10003', rsp.responsibility_key,
                                     '10004', usr.user_name,
                                     '10005', svr.node_name,
                                     '10006', org.name,
                                     '10007', DECODE(to_char(pov.level_value2), '-1', 
                                                     rsp.responsibility_key,
                                              DECODE(to_char(pov.level_value), '-1',
                                             (SELECT node_name
                                              FROM fnd_nodes
                                              WHERE node_id = pov.level_value2),
                                             (SELECT node_name
                                              FROM fnd_nodes
                                              WHERE node_id = pov.level_value2)||'-'||
                                                              rsp.responsibility_key)),
                                     'UnDef') context,
       pov.profile_option_value value
FROM fnd_profile_options po,
     fnd_profile_options_tl potl,
     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_id = pov.profile_option_id
AND   po.profile_option_name = potl.profile_option_name
AND   usr.user_id (+) = pov.level_value
AND   app.application_id (+) = pov.level_value
AND   rsp.application_id (+) = pov.level_value_application_id
AND   rsp.responsibility_id (+) = pov.level_value
AND   svr.node_id (+) = pov.level_value
AND   org.organization_id (+) = pov.level_value
AND   po.profile_option_name = profile_option_name
ORDER BY 1, 5, 6
Tested with 11i.

1 comment:

  1. Nice command, Thanks for sharing.

    ReplyDelete

javascript:void(0)