HZ PARTIES LINK TO ORGANIZATION TABLE LINK

HZ PARTIES LINK TO ORGANIZATION TABLE LINK

SELECT 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

/* Formatted on 7/28/2015 7:48:07 PM (QP5 v5.240.12305.39446) */
SELECT hzp.*
  FROM hz_parties hzp,  --Table
       xle_entity_profiles lep, --Table
       xle_registrations reg,
       hr_locations_all hrl,
       fnd_territories_vl ter,
       hr_operating_units hro   --Table
 WHERE     1 = 1
       AND lep.party_id = hzp.party_id --Link  
       AND lep.legal_entity_id = reg.source_id
       AND hrl.location_id = reg.location_id
       AND ter.territory_code = hrl.country
       AND lep.legal_entity_id = hro.default_legal_context_id