In DBI (Daily Business Intelligence), the cost center hierarchy is maintained in the following table “fii_cost_ctr_hierarchies”
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...)