FND Flex Value Security Rules

Description

Categories: Enginatics
Repository: Github

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.

FND Flex Value Security Rules and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

select
ffvs.flex_value_set_name value_set_name,
ffvrv.flex_value_rule_name rule_name,
ffvrv.description,
ffvrv.error_message,
&responsibility_column
&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

Flexfield

fif.id_flex_name=:title

LOV

Flexfield Code

fif.id_flex_code=:id_flex_code

LOV

Rule Name

ffvrv.flex_value_rule_name=:rule_name

LOV

Flex Value Set

ffvs.flex_value_set_name=:value_set_name

LOV

Responsibility Name

frv.responsibility_name=:responsibility

LOV

Ledger

ffvs.flex_value_set_id in (
select
fifs.flex_value_set_id
from
gl_ledgers gl,
fnd_id_flex_segments fifs
where
gl.name=:ledger and
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
gl.chart_of_accounts_id=fifs.id_flex_num
)