So, using the above table, it is possible to get all children for any given node. (This is possible from the FND tables in Oracle apps too)

Here is the query

SELECT child_value

FROM

(

SELECT LEVEL,

parent_cc_id,

child_cc_id,

child_value,

lpad(' ',    3 *(LEVEL -1)) || child_desc cc_hrchy,

next_level_is_leaf_flag

FROM

(

select

h.parent_cc_id

,h.child_cc_id

,node_child.flex_value child_value

,node_child.description child_desc

,h.is_leaf_flag

,h.next_level_is_leaf_flag

from fii_cost_ctr_hierarchies h

, fnd_flex_values_vl node_child

where  h.child_cc_id = node_child.flex_value_id

and  node_child.flex_value_set_id = h.child_flex_value_set_id

and parent_level = child_level -1

)

START WITH parent_cc_id in ( select flex_value_id from fnd_flex_values_vl

where flex_value=''

and flex_value_set_id = 1005351 )

CONNECT BY PRIOR child_cc_id = parent_cc_id

)

where  next_level_is_leaf_flag='Y'

If you need to see only sub-parent nodes then replace the last line in the query to next_level_is_leaf_flag='N'

This query can be used in other reporting requirements such as using in any Discoverer reports.

For e.g. In your GL chart of accounts, lets say your Cost Center is segment2 and there is a need to find total balance for some particular Cost Center node "XYZ" then you could write a query similar to

select sum(balance column)



from gl_balances gb, gl_code_combinations gcc



where gcc.code_combination_id= gb.code_combination_id



and ...



and ...



and gcc.segment2 in ( result set from the above query...)

-Nilesh
http://www.infocaptor.com