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.

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

Contact us to schedule a demo or if you need help with the installation

select
ffvs.flex_value_set_name,
ffvv.parent_flex_value_low independent_value,
ffvv.flex_value,
ffvv.flex_value_meaning translated_value,
ffvv.description,
xxen_util.meaning(decode(ffvv.enabled_flag,'Y','Y'),'YES_NO',0) enabled,
ffvv.start_date_active,
ffvv.end_date_active,
xxen_util.meaning(decode(ffvv.summary_flag,'Y','Y'),'YES_NO',0) 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,
ffvv.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 ffvv.summary_flag=decode(ffvnh.range_attribute,'P','Y','N') and ffvv.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
ffvv.compiled_value_attributes,
xxen_util.user_name(ffvv.created_by) created_by,
xxen_util.client_time(ffvv.creation_date) creation_date,
xxen_util.user_name(ffvv.last_updated_by) last_updated_by,
xxen_util.client_time(ffvv.last_update_date) last_update_date,
ffvv.flex_value_set_id
from
fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffvv,
fnd_flex_hierarchies_vl ffhv,
fnd_flex_value_norm_hierarchy ffvnh
where
1=1 and
ffvs.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.structured_hierarchy_level=ffhv.hierarchy_id(+) and
ffvv.flex_value_set_id=ffvnh.flex_value_set_id(+) and
ffvv.flex_value=ffvnh.parent_flex_value(+)
order by
ffvs.flex_value_set_name,
ffvv.parent_flex_value_low,
ffvv.flex_value,
ffvnh.child_flex_value_low
Parameter Name SQL text Validation
Used by Key Flexfield 
ffvv.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 
ffvv.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
Flex Value like 
lower(ffvv.flex_value) like lower(:flex_value_like)
 Char
Created By 
ffvv.created_by=xxen_util.user_id(:created_by)
 LOV
Creation Date From 
ffvv.creation_date>=:creation_date_from
 Date
Last Updated By 
ffvv.last_updated_by=xxen_util.user_id(:last_updated_by)
 LOV
Last Update Date From 
ffvv.last_update_date>=:last_update_date_from
 DateTime
Active only 
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate)
 LOV
Missing in Hierarchy 
not exists (select null from fnd_flex_value_hierarchies ffvh where ffvs.flex_value_set_id=ffvv.flex_value_set_id and ffvh.parent_flex_value=:parent_flex_value and ffvv.flex_value between ffvh.child_flex_value_low and ffvh.child_flex_value_high)
 LOV
Parent or Child 
ffvv.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
Download
   
Related Blitz Reports
FND Flex Value Hierarchy FND Flex Hierarchies (Rollup Groups) GL Rollup Groups GL Balance by Account Hierarchy GL Account Analysis COPY OF: GL Balance GL Balance XXFT GL Balance Mrugesh: GL Balance
Blitz Report™

Blitz Report™ provides multiple benefits: