FND Flex Values

Description

Categories: Enginatics, Toolkit - Setup & Support
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.

Copy

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,
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
from
fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffvv,
fnd_flex_hierarchies_vl ffhv
where
1=1 and
ffvs.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.structured_hierarchy_level=ffhv.hierarchy_id(+)
order by
ffvs.flex_value_set_name,
ffvv.parent_flex_value_low,
ffvv.flex_value

Parameter Name

SQL text

Validation

Created By

ffvv.created_by=xxen_util.user_id(:created_by)

LOV

Last Updated By

ffvv.last_updated_by=xxen_util.user_id(:last_updated_by)

LOV

Active only

ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate)

LOV

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

Missing in Hierarchy

not exists (select null from applsys.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

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

Creation Date From

ffvv.creation_date>=:creation_date_from

Date

Last Update Date From

ffvv.last_update_date>=:last_update_date_from

DateTime

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

Flex Value Set

select
'xxen_util.meaning(substr(ffvv.compiled_value_attributes,'||
(row_number() over (partition by ffvq.flex_value_set_id order by ffvq.assignment_date,ffvq.value_attribute_type)*2-1)
||',1),'''||fvat.lookup_type||''',0) '||
decode(fvat.value_attribute_type,
'FIN_ITEM_SET_FLAG','financial_ctegory_set',
'LOB_HIER_TOP_NODE','"Line of Business Hier Top Node"',
'DETAIL_BUDGETING_ALLOWED','budgeting_allowed',
'DETAIL_POSTING_ALLOWED','posting_allowed',
'GL_ACCOUNT_TYPE','account_type',
'GL_CONTROL_ACCOUNT','third_party_control',
'RECONCILIATION FLAG','reconcile',
'GL_LEDGER_SET','ledger_set'
)||',' text
from
fnd_flex_value_sets ffvs,
fnd_flex_validation_qualifiers ffvq,
fnd_value_attribute_types fvat
where
ffvs.flex_value_set_name=:flex_value_set_name and
ffvs.flex_value_set_id=ffvq.flex_value_set_id and
ffvq.id_flex_application_id=fvat.application_id and
ffvq.id_flex_code=fvat.id_flex_code and
ffvq.segment_attribute_type=fvat.segment_attribute_type and
ffvq.value_attribute_type=fvat.value_attribute_type
order by
ffvq.flex_value_set_id,
ffvq.assignment_date,
ffvq.value_attribute_type