I am new to oracle and oracle ebs and I need some help.
I am doing an Oracle report and I need to list flex values from fnd_flex_values_vl view in a hierarchical way using a SQL hierarchical query.
There are two objects, that store information about flex values hierarchy. It is FND_FLEX_VALUE_NORM_HIERARCHY (table) and fnd_flex_value_children_v (view). I assume one of these is enough, since fnd_flex_value_children_v is made using FND_FLEX_VALUE_NORM_HIERARCHY and some other objects.
However, the problem I faced is, that several parents may be listed for one flex value and that I need to find all top parents or leaves in order to do an up-bottom or bottom-up hierarchy. As far as I understand fnd_flex_value_children_v doesn`t necessary store top parents (stores only children). Also it seems that there is probably not one, but there may be multiple hierarchies (I need to list them all then in one query).
Thank you very much for you attention. New user. =)

Check Solution