SQL & PL/SQL

Hello forum members,

I have a need to write a stored proc for populating a table with expended hierarchical values.

My hierarchy in the FND_FLEX_VALUES_CHILDREN_V is defined as below.

Now I have written this query to find the leaf nodes which have the actual flex field values which now I want to store against their parent nodes so that in my reports I will filter on parent node to get all child flex values.

SELECT LEVEL , FLEX_VALUE,
LPAD ('-', 6 * (LEVEL - 5)) || PARENT_FLEX_VALUE,
CONNECT_BY_ISLEAF
FROM FND_FLEX_VALUE_CHILDREN_V
WHERE CONNECT_BY_ISLEAF = 1
START WITH PARENT_FLEX_VALUE = 'ALLOCS' /* this is only to see for allocations hierarchy*/
CONNECT BY PRIOR FLEX_VALUE = PARENT_FLEX_VALUE
ORDER BY 1;

I have many hierarchies defined like this in the base table fnd_flex_values_children_v , how can I write a pl sql program to loop through all the level 1 parent nodes, expand to the lowest levels with flex values and associate them their respective parent flex value and insert them into a table.

Could anyone give me some pointers on this ?

Any help is much appreciated.

Cheers to all members of amazing forums, I learn a lot from there forums.