FND Flex Security Rules

Description
Categories: Application, Enginatics, Setup
Flexfield value security rules, rule elements (included or excluded flexfield value ranges), flexfields where the secured value set is used and responsibilities that the rule is assigned to.
select
ffvrv.flex_value_rule_name rule_name,
ffvrv.description,
ffvrv.error_message,
&responsibility_column
ffvs.flex_value_set_name value_set_name,
&flexfield_columns
xxen_util.user_name(ffvrv.created_by) rule_created_by,
xxen_util.client_time(ffvrv.creation_date) rule_creation_date,
xxen_util.user_name(ffvrv.last_updated_by) rule_last_updated_by,
xxen_util.client_time(ffvrv.last_update_date) rule_last_update_date
from
fnd_flex_value_sets ffvs,
fnd_flex_value_rules_vl ffvrv,
(select ffvrl.* from fnd_flex_value_rule_lines ffvrl where '&show_rule_elements'='Y') ffvrl,
(select ffvru.* from fnd_flex_value_rule_usages ffvru where '&show_responsibilities'='Y') ffvru,
fnd_responsibility_vl frv,
(select fifsgv.* from fnd_id_flex_segments_vl fifsgv where '&show_flexfields'='Y') fifsgv,
fnd_id_flex_structures_vl fifsv,
fnd_id_flexs fif,
fnd_application_vl fav
where
1=1 and
ffvs.flex_value_set_id=ffvrv.flex_value_set_id and
ffvrv.flex_value_rule_id=ffvrl.flex_value_rule_id(+) and
ffvrv.flex_value_rule_id=ffvru.flex_value_rule_id(+) and
ffvru.responsibility_id=frv.responsibility_id(+) and
ffvru.application_id=frv.application_id(+) and
ffvs.flex_value_set_id=fifsgv.flex_value_set_id(+) and
fifsgv.id_flex_code=fifsv.id_flex_code(+) and
fifsgv.id_flex_num=fifsv.id_flex_num(+) and
fifsgv.application_id=fifsv.application_id(+) and
fifsv.application_id=fif.application_id(+) and
fifsv.id_flex_code=fif.id_flex_code(+) and
fif.application_id=fav.application_id(+)
order by
ffvrv.flex_value_rule_name,
frv.responsibility_name,
fav.application_name,
fif.id_flex_name,
fifsv.id_flex_structure_name,
fifsgv.segment_num,
ffvrl.include_exclude_indicator desc,
ffvrl.flex_value_low
Parameter Name SQL text Validation
Responsibility Name
frv.responsibility_name=:responsibility
LOV
Show Responsibilities
Y
LOV
Show Flexfields
Y
LOV
Show Rule Elements
Y
LOV
Flexfield
fif.id_flex_name=:title
LOV
Flexfield Code
fif.id_flex_code=:id_flex_code
LOV
Flex Value Set
ffvs.flex_value_set_name=:value_set_name
LOV
Show Responsibilities
frv.responsibility_name responsibility,
Show Flexfields
fav.application_name application,
fif.id_flex_name flexfield,
fif.id_flex_code,
fifsv.id_flex_structure_name structure,
fifsv.id_flex_num,
fifsgv.segment_name,
fifsgv.form_left_prompt window_prompt,
fifsgv.application_column_name column_name,
xxen_util.meaning(decode(fifsgv.security_enabled_flag,'Y','Y'),'YES_NO',0) security_enabled,
Show Rule Elements
xxen_util.meaning(ffvrl.include_exclude_indicator,'INCLUDE_EXCLUDE',3) include_exclude,
ffvrl.flex_value_low,
ffvrl.flex_value_high,
xxen_util.user_name(ffvrl.created_by) element_created_by,
xxen_util.client_time(ffvrl.creation_date) element_creation_date,
xxen_util.user_name(ffvrl.last_updated_by) element_last_updated_by,
xxen_util.client_time(ffvrl.last_update_date) element_last_update_date,
Rule Name
ffvrv.flex_value_rule_name=:rule_name
LOV