It is highly recommended to check this note on support.oracle.com as there may be any update in this note.
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:
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.
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.
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.
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
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.
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
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;
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 SetNOTE:143175.1 - How To Identify Overlapping Child Ranges In A Value Set.
**************************************************************************
No comments:
Post a Comment