FND Profile Option Values

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Profile option values on all setup levels.
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
fpo.user_profile_option_name,
fav0.application_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,
fpov.profile_option_value system_profile_option_value,
xxen_util.user_name(fpov.created_by) created_by,
xxen_util.client_time(fpov.creation_date) creation_date,
xxen_util.user_name(fpov.last_updated_by) last_updated_by,
xxen_util.client_time(fpov.last_update_date) last_update_date,
fpo.profile_option_name system_profile_option_name,
fpo.profile_option_id,
fpov.level_value,
fpov.level_value2
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 NameSQL textValidation
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
Date
Last Update Date To
fpov.last_update_date<:updated_to+1
Date
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
Download
Related Blitz Reports
Profile Setup Track and Change FND User Login History AR Profile Option Migration FND Responsibilities FND Concurrent Requests FND Responsibility Access Blitz Report Assignments and Responsibilities FND Concurrent Requests 11i FND Responsibility Access 11i
Blitz Report™