PER EEO-1 Exception

select
   nvl(ass.location_id,-1) location_id  
  ,substr(hla.location_code||' - '||hla.address_line_1||' '||hla.address_line_2||' '||hla.address_line_3,1,115) address1
  ,hla.town_or_city||decode(ass.location_id,null,null,', ')||hla.region_2||' '||hla.postal_code city1,
	PER_PERUSEOX_XMLP_PKG.CP_display_p CP_display
from
  per_all_people_f          peo
 ,per_all_assignments_f         ass
 ,per_jobs                     job
 ,hr_locations_all          hla
where peo.business_group_id = :P_BUSINESS_GROUP_ID
and  ass.business_group_id = :P_BUSINESS_GROUP_ID
and  ass.primary_flag     = 'Y'
and  ass.assignment_type = 'E'  
and  peo.current_employee_flag = 'Y'
and :report_date_end 
      between peo.effective_start_date and peo.effective_end_date
and :report_date_end 
between ass.effective_start_date and ass.effective_end_date
and  peo.person_id = ass.person_id
and ass.location_id = hla.location_id (+) 
      AND EXISTS ( 
          SELECT 'X' 
            FROM hr_location_extra_info    hlei1
           where hla.location_id = hlei1.location_id
             and hlei1.information_type = 'EEO-1 Specific Information'
             and hlei1.lei_information_category = 'EEO-1 Specific Information')  
UNION
select
   nvl(ass.location_id,-1) location_id  
  ,substr(hla.location_code||' - '||hla.address_line_1||' '||hla.address_line_2||' '||hla.address_line_3,1,115) address
  ,hla.town_or_city||decode(ass.location_id,null,null,', ')||hla.region_2||' '||hla.postal_code city
  ,PER_PERUSEOX_XMLP_PKG.CP_display_p CP_display
from
  per_all_people_f          peo
 ,per_all_assignments_f         ass
 ,per_jobs                     job
 ,hr_locations_all          hla
where peo.business_group_id = :P_BUSINESS_GROUP_ID
and  ass.business_group_id = :P_BUSINESS_GROUP_ID
and  ass.primary_flag     = 'Y'
and  ass.assignment_type = 'E'  
and  peo.current_employee_flag = 'Y'
and :report_date_end 
      between peo.effective_start_date and peo.effective_end_date
and :report_date_end 
between ass.effective_start_date and ass.effective_end_date
and  peo.person_id = ass.person_id
and ass.location_id = hla.location_id (+) 
AND  ass.location_id in
      (
select pghn.entity_id
--	PER_PERUSEOX_XMLP_PKG.CP_display_p CP_display
from   per_gen_hierarchy_nodes pghn
               where  pghn.node_type <> 'PAR'
       and     pghn.hierarchy_version_id = :p_hierarchy_version_id
       ) 
group by ass.location_id 
        ,hla.town_or_city||decode(ass.location_id,null,null,', ')||
        hla.region_2||' '||hla.postal_code
     ,hla.location_code||' - '||hla.address_line_1||' '||
      hla.address_line_2||' '||hla.address_line_3
order by city1, address1