--- Script to Check Recently modified Profile Values.
set linesize 132 pagesize 1000 feed off;
col Prof_Name format A50
col PROF_LEVEL format a15
col user_name format A15
col email_address format a15
col VALUE format A20 wrap
col who format 999999990
col when format a15
col user_name format A15
col "Granted By" format A15
select
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Organization',
10007, 'ServResp',
'UnDef') PROF_LEVEL,
usr.user_name,
usr.email_address,
v.profile_option_value VALUE,
usr1.user_name "Granted By",
to_char(v.last_update_date,'DD-MON-RRRR') "WHEN"
from apps.fnd_profile_options p,
apps.fnd_profile_option_values v,
apps.fnd_profile_options_tl n,
apps.fnd_user usr,
apps.fnd_user usr1,
apps.fnd_application app,
apps.fnd_responsibility rsp,
apps.fnd_nodes svr,
apps.hr_operating_units org
where
(sysdate - v.last_update_date) < 7
and p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and p.profile_option_name= 'APPS_SSO_LOCAL_LOGIN'
and v.profile_option_value !='SSO'
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
and v.last_updated_by = usr1.user_id
minus
select
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Organization',
10007, 'ServResp',
'UnDef') PROF_LEVEL,
usr.user_name,
usr.email_address,
v.profile_option_value VALUE,
usr1.user_name "Granted By",
to_char(v.last_update_date,'DD-MON-RRRR') "WHEN"
from apps.fnd_profile_options p,
apps.fnd_profile_option_values v,
apps.fnd_profile_options_tl n,
apps.fnd_user usr,
apps.fnd_user usr1,
apps.fnd_application app,
apps.fnd_responsibility rsp,
apps.fnd_nodes svr,
apps.hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and v.level_id = 10004
and usr.user_name in ('SYSADMIN','GUEST')
and p.profile_option_name = n.profile_option_name
and p.profile_option_name= 'APPS_SSO_LOCAL_LOGIN'
and v.profile_option_value !='SSO'
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
and v.last_updated_by = usr1.user_id
order by prof_level;
~~~Cheers