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 |
