Friday, 2 December 2022

Script to get Profile Option Values at Responsibility Level


SELECT frv.responsibility_name,
       fpo.profile_option_name,
       fpot.user_profile_option_name,
       fpov.profile_option_value
FROM   fnd_profile_options_tl fpot,
       fnd_profile_options fpo,
       fnd_profile_option_values fpov,
       fnd_responsibility_vl frv
WHERE  fpot.profile_option_name = fpo.profile_option_name
       AND fpo.profile_option_id = fpov.profile_option_id
       AND fpov.level_id = '10003' --Level=> 10001:SITE,10002:APP,10003:RESP,10005:SERVER,10006:ORG,10004:USER
       AND fpov.level_value = frv.responsibility_id
       AND fpot.LANGUAGE = 'US'
       --and fpot.user_profile_option_name = '<USER_PROFILE_NAME>'
       AND frv.responsibility_name LIKE '<RESPONSIBILITY_NAME>'
ORDER  BY frv.responsibility_name;  


(OR)

/*Query Profile Option Values at All Levels*/
SELECT po.user_profile_option_name,
       po.profile_option_name                      name,
       pov.level_id,
       Decode(pov.level_id, 10001, 'Site',
                            10002, 'Application',
                            10003, 'Responsibility',
                            10004, 'User',
                            10005, 'Server',
                            10006, 'Organization',
                            10007, 'ServResp',
                            'Undefined')           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', (SELECT n.node_name
                                               FROM   fnd_nodes n
                                               WHERE  n.node_id = level_value2)
                                              ||'/'
                                              || (
       Decode(pov.level_value, -1, 'Default',
                               (SELECT responsibility_key
                                FROM   fnd_responsibility
                                WHERE  responsibility_id = level_value)) ),
                                     pov.level_id) CONTEXT,
       pov.profile_option_value                    VALUE,
       pov.last_update_date,
       usrlst.user_name                            last_updated_by
FROM   fnd_profile_options_vl po,
       fnd_profile_option_values pov,
       fnd_user usr,
       fnd_application app,
       fnd_responsibility_vl rsp,
       fnd_user usrlst,
       fnd_nodes svr,
       hr_operating_units org
WHERE  usrlst.user_id = pov.last_updated_by
       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 po.user_profile_option_name = '---------'
       --and usr.END_DATE is NULL
; 

No comments:

Post a Comment