FND Profile Option Values

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.

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

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)

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 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