Correct Child Overlapping in the Hierarchy of a Valueset

It is highly recommended to check this note on support.oracle.com as there may be any update in this note.

How to correct Child Overlapping in the Hierarchy of a Valueset [ID 371580.1]

Applies to:

Oracle General Ledger - Version: 11.5.2 to 11.5.10
Information in this document applies to any platform.

Goal

How to correct Child Overlapping in the Hierarchy of a Valueset.

Solution

For Reporting and Budgetary purpose, hierarchy is maintained in the values of a Value Set i.e. Child values are assigned to a Parent value, which are in turn assigned to Grand Parents thus forming a Tree structure as follows:

How to correct Child Overlapping in the Hierarchy of a Valueset [ID 371580.1]

For a correct hierarchy, it is needed that a child value is assigned to only one Parent value. In case a Child value is
assigned to more than one Parent value, it results in Child Overlapping. 

There are two possible scenarios of Overlapping Child Ranges:

Scenario One

A Child value is assigned to a Parent value more than once. 

How to correct Child Overlapping in the Hierarchy of a Valueset [ID 371580.1]

For example, in the above hierarchy structure, the child ranges for 1000 are:
1001 to 1010 -----> Child Values Only
1010 to 1020 -----> Child Values Only

Due to above two assignments, 1010 is getting assigned twice to 1000. 

Scenario Two

A Child value is assigned to more than one Parent. 

How to correct Child Overlapping in the Hierarchy of a Valueset [ID 371580.1]

For Example: In the above hierarchy
The child range for 2100 is defined as "2050 to 2150 -----> Child Values Only"
The child range for 2200 is defined as "2150 to 2250 -----> Child Values Only"

Thus 2150 is getting assigned to both 2100 and 2200. 

Now we have a Parent value 2000, for which child range is defined as "2001 to 2999 -----> Parent Values Only"

If we evaluate final child values, 2150 will be rolling up twice to 2000.

NOTE : To get the list of child values which are getting rolled up more than once to a Parent value, refer Note 118130.1 on "How to Read Output of Script to Detect Overlapping Child Ranges in a Value Set".

The two scenarios explained above are possible only when a value (Child or Parent) is assigned more than once in the overall hierarchy structure.

How to identify Duplicate Assignments

Use the following SQL to identify the duplicate assignments in all the Value Sets: 

SELECT          fset.flex_value_set_name "Value Set",
                         fvalue.flex_value_set_id "Value Set Id",
                         fvalue.flex_value "Child Value",
                         fvalue.parent_flex_value "Parent Value"
FROM             FND_FLEX_VALUE_SETS                  fset,
                         FND_FLEX_VALUE_CHILDREN_V  fvalue
WHERE          (fvalue.flex_value, fvalue.flex_value_set_id) in (
                         select flex_value, flex_value_set_id
                         from FND_FLEX_VALUE_CHILDREN_V
                         group by flex_value, flex_value_set_id
                         having count(1) > 1)
AND               fset.flex_value_set_id = fvalue.flex_value_set_id
ORDER BY    fvalue.flex_value_set_id, fvalue.flex_value, fvalue.parent_flex_value;

For all the values in a value set, which have more than one Parent value assigned to it, the output of this query will give
the name of the Valueset, Child Value and the Parent value assigned to it.

To correct the Hierarchy, follow the steps given below:
1. Go to General Ledger Responsibility
2. Setup -> Financials -> Flexfields -> Key -> Values
3. Select the Value Set option in "Find Values By" and Enter the Value Set name as returned by the SQL above.
4. Query for the Child value returned by the SQL.
5. Click on "View Hierarchies" for this value.
6. Click on "Up" button.
You will observe that this is returning more than one value as it is assigned to multiple Parent values.
7. Query for the Parent Values and Click on "Define Child Range"
8. Correct the hierarchy i.e remove the assignment of the Child value from all but one Parent Value.
9. Saving the changes and exiting the form will initiate the Compile Value Set Hierarchy program.

This will resolve the issue. Rerun the script once again to check that it is not returning any rows.

NOTE: In case you just want to correct hierarchy of a particular Valueset, please use the following query:

SELECT      flex_value_set_id "Value Set Id",
                    flex_value "Child Value",
                    parent_flex_value "Parent Value"
FROM        FND_FLEX_VALUE_CHILDREN_V
WHERE      (flex_value, flex_value_set_id) in (
                   select flex_value, flex_value_set_id
                   from FND_FLEX_VALUE_CHILDREN_V
                   where flex_value_set_id =
                   (select flex_value_set_id
                   from fnd_flex_value_sets fset
                   where flex_value_set_name = '&Flexfield_Value_Set_Name')
                   group by flex_value, flex_value_set_id
                   having count(1) > 1)
ORDER BY    flex_value_set_id, flex_value, parent_flex_value;

References

NOTE:118130.1 - How to Read Output of Script to Detect Overlapping Child Ranges in a Value Set
NOTE:143175.1 - How To Identify Overlapping Child Ranges In A Value Set



Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions