PER Duplicate Person

select
  'HR' source,
        'HR' source2,
ppf.national_identifier,
ppf2.national_identifier national_identifier2,
ppf.party_id,
ppf2.party_id party_id2,
hr_reports.get_party_number(ppf.party_id) party_number,
hr_reports.get_party_number(ppf2.party_id) party_number2,
ppf.full_name,
ppf2.full_name full_name2,
ppf.order_name,
ppf2.order_name order_name2,
pbg.name bg_name,
pbg2.name bg_name2,
loc.location_code,
loc2.location_code location_code2,
org.name org_name,
org2.name org_name2,
add1.postal_code,
add2.postal_code postal_code2,
ppf.employee_number,
ppf2.employee_number employee_number2,
ppf.applicant_number,
ppf2.applicant_number applicant_number2,
ppf.npw_number npw_number,
ppf2.npw_number npw_number2
from    per_all_people_f ppf,
        per_business_groups pbg,
        per_addresses add1,
        per_all_assignments_f ass,
        hr_all_organization_units org,
        hr_locations_all loc,
        per_all_people_f ppf2,
        per_business_groups pbg2,
        per_addresses add2,
        per_all_assignments_f ass2,
        hr_all_organization_units org2,
        hr_locations_all loc2
where   ppf.business_group_id = :p_business_group_id
and     ppf.person_id = nvl(:p_person_id, ppf.person_id)
and     :c_effective_date
between ppf2.effective_start_date
and     ppf2.effective_end_date
and     ppf2.business_group_id <> :p_business_group_id
and     add1.person_id(+) = ppf.person_id
and     nvl(add1.primary_flag,'Y') = 'Y'
and     :c_effective_date
between nvl(add1.date_from,:c_effective_date)
and     nvl(add1.date_to,:c_effective_date)
and     add2.person_id(+) = ppf2.person_id
and     nvl(add2.primary_flag,'Y') = 'Y'
and     :c_effective_date
between nvl(add2.date_from,:c_effective_date)
and     nvl(add2.date_to,:c_effective_date)
and     pbg.business_group_id = ppf.business_group_id
and     pbg2.business_group_id = ppf2.business_group_id
and     ass.person_id(+) = ppf.person_id
and     :c_effective_date
        between nvl(ass.effective_start_date,:c_effective_date)
        and     nvl(ass.effective_end_date,:c_effective_date)
and      ( (ass.assignment_type in ('E','C') -- nvl(ass.assignment_type,'E') = 'E'
          and nvl(ass.primary_flag,'Y') = 'Y')
       or ((ass.assignment_type='A' )
         and ass.assignment_id=(select min(assignment_id)
                                             from per_all_assignments_f ass3
                                              where ass3.person_id=ass.person_id
                                               and ass3.assignment_type='A')) )
and     ass2.person_id(+) = ppf2.person_id
and     :c_effective_date
        between nvl(ass2.effective_start_date,:c_effective_date)
        and     nvl(ass2.effective_end_date,:c_effective_date)
and   ( (ass2.assignment_type in ('E','C')
          and nvl(ass2.primary_flag,'Y') = 'Y')
       or( (ass2.assignment_type='A')
          and ass2.assignment_id=(select min(assignment_id)
                                          from per_all_assignments_f ass4
                                           where ass4.person_id=ass2.person_id
                                           and ass4.assignment_type='A' )) )
and     org.organization_id(+) = ass.organization_id
and     org2.organization_id(+) = ass2.organization_id
and     loc.location_id(+) = ass.location_id
and     loc2.location_id(+) = ass2.location_id
and     ppf.party_id <> ppf2.party_id
and (ppf.national_identifier = ppf2.national_identifier
    or upper(ppf.last_name) = upper(ppf2.last_name)
    and (upper(ppf.first_name) = upper(ppf2.first_name)
        or ppf.first_name is null
        or ppf2.first_name is null)
    and (ppf.date_of_birth = ppf2.date_of_birth
    or ppf.date_of_birth is null
    or ppf2.date_of_birth is null)
    )
union
select
'HR' source,
        'TCA' source2,
        ppf.national_identifier,
        null national_identifier2,
ppf.party_id,
hzp.party_id party_id2,
hr_reports.get_party_number(ppf.party_id) party_number,
hr_reports.get_party_number(hzp.party_id) party_number2,
ppf.full_name,
hzp.person_name full_name2,
ppf.order_name,
hzp.person_name order_name2,
pbg.name bg_name,
null bg_name2,
loc.location_code,
null location_code2,
org.name org_name,
null org_name2,
add1.postal_code,
null postal_code2,
ppf.employee_number,
null employee_number2,
ppf.applicant_number,
null applicant_number2,
ppf.npw_number,
null npw_number2
from    per_all_people_f ppf,
        per_business_groups pbg,
        per_addresses add1,
        per_all_assignments_f ass,
        hr_all_organization_units org,
        hr_locations_all loc,
        hz_person_profiles hzp
where   ppf.business_group_id = :p_business_group_id
and     ppf.person_id = nvl(:p_person_id, ppf.person_id)
and     add1.person_id(+) = ppf.person_id
and     nvl(add1.primary_flag,'Y') = 'Y'
and     :c_effective_date
between nvl(add1.date_from,:c_effective_date)
and     nvl(add1.date_to,:c_effective_date)
and     pbg.business_group_id = ppf.business_group_id
and     ass.person_id(+) = ppf.person_id
--and     nvl(ass.primary_flag,'Y') = 'Y'
and     :c_effective_date
        between nvl(ass.effective_start_date,:c_effective_date)
        and     nvl(ass.effective_end_date,:c_effective_date)
and    ( (ass.assignment_type in ('E','C')
          and nvl(ass.primary_flag,'Y') = 'Y')
         or (( ass.assignment_type = 'A')
          and ass.assignment_id = (
select min(assignment_id)
                                               from per_all_assignments_f ass5
                                               where ass5.person_id = ass.person_id
                                               and ass5.assignment_type = 'A')))
and     org.organization_id(+) = ass.organization_id
and     loc.location_id(+) = ass.location_id
and     not exists (select null
                                from   per_all_people_f ppf2
                                where ppf2.party_id = hzp.party_id)
and     hzp.last_update_date =  (select         max(last_update_date)
                                from    hz_person_profiles
                                where   party_id = hzp.party_id)
and     (upper(ppf.last_name) = upper(hzp.person_last_name)
and (upper(ppf.first_name) = upper(hzp.person_first_name)
or hzp.person_first_name is null
or ppf.first_name is null)
and (ppf.date_of_birth = hzp.date_of_birth
or ppf.date_of_birth is null
or hzp.date_of_birth is null))
order by 7,5,6