FND Flex Value Hierarchy
Description
- Categories
- Enginatics
- Repository
- GitHub
Flexfield value hierarchy showing a hierarchical tree of parent and child relations and child ranges.
Parameter 'Parents without Child only' can be used to validate the hierarchy for nodes where the child range does not include a single child value.
The query is based on a treewalk through table fnd_flex_value_norm_hierarchy, which contains one record for every parent node, and a range_attribute column to indicate if the child value low/high range should match either parent nodes or child values.
While table fnd_flex_value_norm_hierarchy contains one record for each hierarchy node, table fnd_flex_value_hierarchies shows a flat representation of all hierarchy nodes and their lowest child ranges (range_attribute=C). For any lowest child range value, it contains one record for every higher hierarchy, that this child range is included in, up to the topmost hierarchy node. It can be used, for example, to validate directly if a child value is included in a specific higher level hierarchy node.
For GL flex value hierarchies, there are additional tables gl_seg_val_norm_hierarchy and gl_seg_val_hierarchies, which store one record for each matching child value for parent nodes, instead of just the range.
These tables are updated automatically after each flex value hierarchy change by concurrent 'General Ledger Accounting Setup Program' (GLSTFL).
gl_seg_val_norm_hierarchy stores one record for every child and their direct parent.
gl_seg_val_hierarchies stores one record for every node in the hierarchy (regardless if child or parent) and all their parent records, regardless on which level. It can be used, for example, to directly find all childs of one parent node.
Table gl_account_hierarchies stores the relation between summary template and detail code combination ids.
Parameter 'Parents without Child only' can be used to validate the hierarchy for nodes where the child range does not include a single child value.
The query is based on a treewalk through table fnd_flex_value_norm_hierarchy, which contains one record for every parent node, and a range_attribute column to indicate if the child value low/high range should match either parent nodes or child values.
While table fnd_flex_value_norm_hierarchy contains one record for each hierarchy node, table fnd_flex_value_hierarchies shows a flat representation of all hierarchy nodes and their lowest child ranges (range_attribute=C). For any lowest child range value, it contains one record for every higher hierarchy, that this child range is included in, up to the topmost hierarchy node. It can be used, for example, to validate directly if a child value is included in a specific higher level hierarchy node.
For GL flex value hierarchies, there are additional tables gl_seg_val_norm_hierarchy and gl_seg_val_hierarchies, which store one record for each matching child value for parent nodes, instead of just the range.
These tables are updated automatically after each flex value hierarchy change by concurrent 'General Ledger Accounting Setup Program' (GLSTFL).
gl_seg_val_norm_hierarchy stores one record for every child and their direct parent.
gl_seg_val_hierarchies stores one record for every node in the hierarchy (regardless if child or parent) and all their parent records, regardless on which level. It can be used, for example, to directly find all childs of one parent node.
Table gl_account_hierarchies stores the relation between summary template and detail code combination ids.
SQL 74 lines
select lpad(' ',2*(level-1))||level level_, lpad(' ',2*(level-1))||ffvnh.parent_flex_value value, ffvv.description||case when ffvv.enabled_flag='Y' and sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) then null else ' (inactive)' end description, 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, xxen_util.meaning(connect_by_isleaf,'SYS_YES_NO',700) is_leaf, connect_by_root ffvnh.parent_flex_value root_value, substr(sys_connect_by_path(ffvnh.parent_flex_value,'-> '),4) path, ffvnh.parent_flex_value value_flat, &value_attributes ffvv.compiled_value_attributes, xxen_util.user_name(ffvnh.created_by) created_by, xxen_util.client_time(ffvnh.creation_date) creation_date, xxen_util.user_name(ffvnh.last_updated_by) last_updated_by, xxen_util.client_time(ffvnh.last_update_date) last_update_date, ffvnh.flex_value_set_id from ( select ffvnh.parent_flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh where ffvnh.flex_value_set_id=(select ffvs.flex_value_set_id from fnd_flex_value_sets ffvs where ffvs.flex_value_set_name=:flex_value_set_name) union all select ffv2.flex_value parent_flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2 where (:show_child_values='Y' or :parents_without_child is not null) and 2=2 and ffv2.summary_flag='N' and ffv2.flex_value_set_id=(select ffvs.flex_value_set_id from fnd_flex_value_sets ffvs where ffvs.flex_value_set_name=:flex_value_set_name) ) ffvnh, fnd_flex_values_vl ffvv, fnd_flex_hierarchies_vl ffhv where 3=3 and ffvnh.parent_flex_value=ffvv.flex_value and ffvnh.flex_value_set_id=ffvv.flex_value_set_id and ffvv.structured_hierarchy_level=ffhv.hierarchy_id(+) connect by nocycle ffvnh.parent_flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with 1=1 and (:parent_flex_value is not null or not exists (select null from fnd_flex_value_norm_hierarchy ffvnh0 where ffvnh0.flex_value_set_id=(select ffvs.flex_value_set_id from fnd_flex_value_sets ffvs where ffvs.flex_value_set_name=:flex_value_set_name) and ffvnh.parent_flex_value between ffvnh0.child_flex_value_low and ffvnh0.child_flex_value_high and ffvv.summary_flag=decode(ffvnh0.range_attribute,'P','Y','N') ) ) |
Parameters 4
| Parameter | Type | List of values SQL | |
|---|---|---|---|
| Flex Value Set | LOV | ||
| Hierarchy Start Value | LOV |
| |
| Parents without Child only | LOV |
| |
| Show Child Values | LOV Oracle |