Profile Setup Track and Change

Description
BR100 Export / change tracking
Unlike Oracle's SQL script from note 201945.1, this report also shows the user visible profile option value in addition to the internal system profile option value.
select
fav0.application_name,
fpo.user_profile_option_name,
decode(fpov.level_id,10001,'Site',10002,'Application', 10003,'Responsibility', 10004,'User', 10005,'Server',10006,'Operating Unit',
10007,decode(to_char(fpov.level_value2),'-1','Responsibility',decode(to_char(fpov.level_value),'-1','Server','Server+Resp'))
) level_,
decode(fpov.level_id,10001,null,10002,fav.application_name,10003,frv.responsibility_name,10004,xxen_util.user_name(decode(fpov.level_id,10004,fpov.level_value)),10005,fn.node_name,10006,haouv.name,
10007,decode(to_char(fpov.level_value2),'-1',frv.responsibility_name,decode(to_char(fpov.level_value),'-1',fn.node_name,fn.node_name||' - '||frv.responsibility_name))
) level_name,
xxen_util.display_profile_option_value(fpo.application_id,fpo.profile_option_id,fpov.profile_option_value) profile_option_value,
xxen_util.user_name(fpov.last_updated_by) last_updated_by,
xxen_util.client_time(fpov.last_update_date) last_update_date,
fpov.profile_option_value system_profile_option_value,
fpo.profile_option_name system_profile_option_name,
fpo.profile_option_id,
fpov.level_value,
fpov.level_value2,
xxen_util.user_name(fpov.created_by) created_by,
xxen_util.client_time(fpov.creation_date) creation_date
from
fnd_application_vl fav0,
fnd_profile_options_vl fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user fu,
fnd_application_vl fav,
hr_all_organization_units_vl haouv,
fnd_nodes fn
where
1=1 and
fav0.application_id=fpo.application_id and
fpo.profile_option_id=fpov.profile_option_id and
fpo.application_id=fpov.application_id and
decode(fpov.level_id,10002,fpov.level_value)=fav.application_id(+) and
case when fpov.level_id in (10003,10007) then fpov.level_value end=frv.responsibility_id(+) and
case when fpov.level_id in (10003,10007) then fpov.level_value_application_id end=frv.application_id(+) and
decode(fpov.level_id,10004,fpov.level_value)=fu.user_id(+) and
decode(fpov.level_id,10005,fpov.level_value,10007,fpov.level_value2)=fn.node_id(+) and
decode(fpov.level_id,10006,fpov.level_value)=haouv.organization_id(+)
order by
fpo.user_profile_option_name,
fpov.level_id,
decode(fpov.level_id,10001,null,10002,fav.application_name, 10003,frv.responsibility_name, 10004,xxen_util.user_name(decode(fpov.level_id,10004,fpov.level_value)), 10005,fn.node_name,10006,haouv.name)
Parameter Name SQL text Validation
User Profile Name starts with
upper(fpo.user_profile_option_name) like upper(:user_profile_option_name)||'%'
LOV
User Profile Name any lang
fpo.profile_option_name in (select fpot.profile_option_name from fnd_profile_options_tl fpot where fpot.user_profile_option_name=:user_profile_name_any_lang)
LOV
Setup Level
fpov.level_id=decode(:setup_level,'Site',10001,'Application',10002,'Responsibility',10003,'User',10004,'Server',10005,'Operating Unit',10006)
LOV
Setup Level Value
upper(decode(fpov.level_id,10002,fav.application_name, 10003,frv.responsibility_name, 10004,fu.user_name, 10005,'serverx',10006,haouv.name)) like upper(:setup_level_value)
LOV
Application Name
fav0.application_name=:application_name
LOV
Application Short Name
fav0.application_short_name=:application_short_name
LOV
System Profile Name starts with
fpo.profile_option_name like upper(:profile_option_name)||'%'
LOV
Last Update Date From
fpov.last_update_date>=:updated_from
DateTime
Last Update Date To
fpov.last_update_date<=:updated_to
DateTime
Last Updated By
fpov.last_updated_by=xxen_util.user_id(:last_updated_by)
LOV
Profile System Value like
fpov.profile_option_value like :profile_option_value
Char
Redundant
fpov.level_id<>10001 and
exists (
select null from
fnd_profile_option_values fpov2
where
fpov.profile_option_value=fpov2.profile_option_value and
fpov.application_id=fpov2.application_id and
fpov.profile_option_id=fpov2.profile_option_id and
fpov2.level_id=10001 and
fpov2.level_value=0
) and
'Yes'=:redundant
LOV