SELECT DISTINCT hrl.country,

                hroutl_bg.NAME            bg,

                hroutl_bg.organization_id,

                lep.legal_entity_id,

                lep.NAME                  legal_entity,

                hroutl_ou.NAME            ou_name,

                hroutl_ou.organization_id org_id,

                hrl.location_id,

                hrl.location_code,

                glev.flex_segment_value

  FROM xle_entity_profiles          lep,

       xle_registrations            reg,

       hr_locations_all             hrl,

       hz_parties                   hzp,

       fnd_territories_vl           ter,

       hr_operating_units           hro,

       hr_all_organization_units_tl hroutl_bg,

       hr_all_organization_units_tl hroutl_ou,

       hr_organization_units        gloperatingunitseo,

       gl_legal_entities_bsvs       glev

WHERE lep.transacting_entity_flag = 'Y'

   AND lep.party_id = hzp.party_id

   AND lep.legal_entity_id = reg.source_id

   AND reg.source_table = 'XLE_ENTITY_PROFILES'

   AND hrl.location_id = reg.location_id

   AND reg.identifying_flag = 'Y'

   AND ter.territory_code = hrl.country

   AND lep.legal_entity_id = hro.default_legal_context_id

   AND gloperatingunitseo.organization_id = hro.organization_id

   AND hroutl_bg.organization_id = hro.business_group_id

   AND hroutl_ou.organization_id = hro.organization_id

   AND glev.legal_entity_id = lep.legal_entity_id;