Query to find the profile option value from the backend

Please use the below query to find the profile option value from the backend. You can also use the same query to find the Profile options based on it value.

In case of clones we might need to find the Source host entries in the profile option value, in those cases please the below query and pass hostname when prompted for enter_the_profile_value and leave the enter_the_profile_name as null.

 

If you want find the value of the particular profile option you can enter the profile name which it is prompted and level the enter_the_profile_value as null. Likewise if you want to find the profile options having the certain values, Please leave the enter_the_profile_name as null.

set lines 999 pagesize 999
col name for a25
col USER_PROFILE_OPTION_NAME for a30
col PROFILE_OPTION_VALUE for a60
SELECT po.user_profile_option_name, 
       po.profile_option_name            name, 
       profile_option_value, 
       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,
'10005', svr.node_name,
'10006', org.NAME,
'10004', usr.user_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))),
'Undefined'
) "LEVEL_VALUE"
FROM   apps.fnd_profile_option_values pov, 
       fnd_profile_options_vl po ,apps.fnd_user usr,
apps.fnd_application app,
apps.fnd_responsibility rsp,
apps.fnd_nodes svr,
apps.hr_operating_units org
WHERE  Upper(po.user_profile_option_name) LIKE upper('%&Enter_the_Profile_Name%')
       AND upper(profile_option_value) like upper('%&Enter_the_profile_value%')
	   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;