Query to Get Org Name, Location, Business Group Name, Legal Entiry Name and Operationg Unit Name

SELECT

o.organization_id

, otl.name

, o.date_from

, o.date_to

, o.internal_address_line

, loc.location_code as location

, o.comments

, les.business_group_id

, otl2.name as business_group

, les.organization_id as le_id

, les.name as legal_entity

, opu.organization_id as ou_id

, opu.name as operating_unit

FROM

hr_all_organization_units o

, hr_all_organization_units_tl otl

, hr_organization_information o2

, hr_organization_information o3

, hr_organization_information o4

, hr_legal_entities les

, hr_all_organization_units otl2

, hr_locations loc

, hr_operating_units opu

where 1=1

and o.organization_id = otl.organization_id

AND o.organization_id = o2.organization_id (+)

and o.organization_id = o3.organization_id

and o.organization_id = o4.organization_id

AND o3.org_information_context = 'Accounting Information'

and o2.org_information_context (+) = 'Work Day Information'

and o4.org_information_context = 'CLASS'

and o4.org_information1 = 'INV'

and o4.org_information2 = 'Y'

and o3.org_information2 = les.organization_id

and les.business_group_id = otl2.organization_id

and o.location_id = loc.location_id(+)

and o3.org_information3 = opu.organization_id

order by o.organization_id

;