FND Flex Values

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report for all flex values and the hierarchies they are included in.
Column 'Hierarchy Position' can be used to validate your account hierarchy setup and check which account segment values are not included in any (or a specific) hierarchy yet.

In EBS R12.2., there is new flexfield value security in place, which would require the UMX|FND_FLEX_VSET_ALL_PRIVS_ROLE role to maintain flexfield ...  Report for all flex values and the hierarchies they are included in.
Column 'Hierarchy Position' can be used to validate your account hierarchy setup and check which account segment values are not included in any (or a specific) hierarchy yet.

In EBS R12.2., there is new flexfield value security in place, which would require the UMX|FND_FLEX_VSET_ALL_PRIVS_ROLE role to maintain flexfield values.
If you do not have this role but apps DB access, you can add it from the backend:

begin
wf_local_synch.propagateuserrole(
p_user_name=>'ANDY.HAACK',
p_role_name=>'UMX|FND_FLEX_VSET_ALL_PRIVS_ROLE'
);
commit;
end;   more 
select
ffvs.flex_value_set_name,
xxen_util.meaning(ffvs.validation_type,'SEG_VAL_TYPES',0) validation_type,
ffvs0.flex_value_set_name parent_flex_value_set,
ffv.parent_flex_value_low independent_value,
ffv.flex_value,
ffvt.flex_value_meaning translated_value,
ffvt.description,
xxen_util.yes(ffv.enabled_flag) enabled,
ffv.start_date_active,
ffv.end_date_active,
xxen_util.yes(ffv.summary_flag) parent,
ffvnh.child_flex_value_low||nvl2(ffvnh.child_flex_value_low,'-',null)||ffvnh.child_flex_value_high child_range,
xxen_util.meaning(ffvnh.range_attribute,'RANGE_ATTRIBUTE',0) range_attribute,
ffhv.hierarchy_name rollup_group,
ffv.hierarchy_level,
(select distinct listagg(ffvnh.parent_flex_value,', ') within group (order by ffvnh.parent_flex_value) over () from fnd_flex_value_norm_hierarchy ffvnh where 2=2 and ffv.summary_flag=decode(ffvnh.range_attribute,'P','Y','N') and ffv.flex_value between ffvnh.child_flex_value_low and ffvnh.child_flex_value_high and ffvs.flex_value_set_id=ffvnh.flex_value_set_id) parent_values,
&value_attributes
ffv.compiled_value_attributes,
&dff_columns
xxen_util.user_name(ffv.created_by) created_by,
xxen_util.client_time(ffv.creation_date) creation_date,
xxen_util.user_name(ffv.last_updated_by) last_updated_by,
xxen_util.client_time(ffv.last_update_date) last_update_date,
ffv.flex_value_set_id
from
fnd_flex_value_sets ffvs,
fnd_flex_value_sets ffvs0,
fnd_flex_values ffv,
fnd_flex_values_tl ffvt,
fnd_flex_hierarchies_vl ffhv,
fnd_flex_value_norm_hierarchy ffvnh
where
1=1 and
ffvs.parent_flex_value_set_id=ffvs0.flex_value_set_id(+) and
ffvs.flex_value_set_id=ffv.flex_value_set_id and
ffv.flex_value_id=ffvt.flex_value_id and
ffvt.language=userenv('lang') and
ffv.structured_hierarchy_level=ffhv.hierarchy_id(+) and
ffv.flex_value_set_id=ffvnh.flex_value_set_id(+) and
ffv.flex_value=ffvnh.parent_flex_value(+)
order by
ffvs.flex_value_set_name,
ffv.parent_flex_value_low,
ffv.flex_value,
ffvnh.child_flex_value_low
Parameter NameSQL textValidation
Used by Key Flexfield
ffv.flex_value_set_id in
(
select
fifsgv.flex_value_set_id
from
fnd_id_flexs fif,
fnd_id_flex_segments_vl fifsgv
where
fif.id_flex_name=:id_flex_name and
fif.application_id=fifsgv.application_id and
fif.id_flex_code=fifsgv.id_flex_code
)
LOV
Used by Key Flex Structure
ffv.flex_value_set_id in
(
select
fifsgv.flex_value_set_id
from
fnd_id_flex_structures_vl fifsv,
fnd_id_flex_segments_vl fifsgv
where
fifsv.id_flex_structure_name=:id_flex_structure_name and
fifsv.application_id=fifsgv.application_id and
fifsv.id_flex_code=fifsgv.id_flex_code and
fifsv.id_flex_num=fifsgv.id_flex_num
)
LOV
Flex Value Set
ffvs.flex_value_set_name=:flex_value_set_name
LOV
Independent Value
ffv.parent_flex_value_low=:independent_value
Char
Flex Value like
lower(ffv.flex_value) like lower(:flex_value_like)
Char
Created By
ffv.created_by=xxen_util.user_id(:created_by)
LOV
Creation Date From
ffv.creation_date>=:creation_date_from
Date
Creation Date To
ffv.creation_date<:creation_date_to+1
Date
Last Updated By
ffv.last_updated_by=xxen_util.user_id(:last_updated_by)
LOV
Last Update Date From
ffv.last_update_date>=:last_update_date_from
Date
Last Update Date To
ffv.last_update_date<:last_update_date_to+1
Date
Active only
ffv.enabled_flag='Y' and
sysdate between nvl(ffv.start_date_active,sysdate) and nvl(ffv.end_date_active,sysdate)
LOV
Missing in Hierarchy
not exists (select null from fnd_flex_value_hierarchies ffvh where ffvs.flex_value_set_id=ffv.flex_value_set_id and ffvh.parent_flex_value=:parent_flex_value and ffv.flex_value between ffvh.child_flex_value_low and ffvh.child_flex_value_high)
LOV
Parent or Child
ffv.summary_flag=decode(xxen_util.lookup_code(:parent_or_child,'DIRECTION_CODE',222),'P','Y','N')
LOV
Parent Value like
ffvnh.parent_flex_value like :hierarchy_name_like
Char
Used in Rollup Group
ffv.structured_hierarchy_level=:rollup_group_id
LOV
DFF Display
select xxen_util.dff_columns(p_table_name=>'fnd_flex_values',p_display_mode=>:dff_display) sql_text from dual
LOV
Download
Related Blitz Reports
FND Flex Value Upload FND Flex Values V1 FND Flex Value Hierarchy FND Rollup Groups GL Account Analysis GL Account Distribution Analysis 11g Test segment value GL Account Analysis 11g GL Account Distribution Analysis
Blitz Report™