Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Hierarchial query help !

User525984-OCMay 24 2013 — edited May 31 2013
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.
This post has been answered by Frank Kulash on May 30 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 27 2013
Added on May 24 2013
12 comments
1,901 views