Wednesday, January 27, 2016

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
;


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect