DBI: Find all child values in Cost Center Hierarchy

In DBI (Daily Business Intelligence), the cost center hierarchy is maintained in the following table “fii_cost_ctr_hierarchies”

dbi_cost_center_hierarchy.png

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...)