Organization Hierarchy using the Query

-- ---------------------------------------------------------------------------------
-- Purpose : Organization Hierarchy using the Query
-- Date      : 12.May.2009
-- Created by : Chaitanya
-- .....
-- ----------------------------------------------------------------------------------

select hbg.BUSINESS_GROUP_NAME,
(select NAME
from GL_SETS_OF_BOOKS_V
where set_of_books_id = hle.SET_OF_BOOKS_ID) sob_name,
hou.NAME ou_name,
hle.name le_name,
ood.ORGANIZATION_NAME,
ood.ORGANIZATION_CODE,
msi.SECONDARY_INVENTORY_NAME
from hrfv_business_groups hbg,
hr_operating_units hou,
hr_legal_entities hle,
org_organization_definitions ood,
mtl_secondary_inventories msi
where
hou.BUSINESS_GROUP_ID(+) = hbg.BUSINESS_GROUP_ID
and hle.organization_id(+) = hou.legal_entity_id
and ood.OPERATING_UNIT(+)= hle.ORGANIZATION_ID
and msi.ORGANIZATION_ID(+) = ood.ORGANIZATION_ID;

-- ---------------------------------------------------------------------------------