select pbg.name,
pbg2.name name2,
ppf.full_name,
ppf2.full_name full_name2,
decode(ppf.full_name, ppf2.full_name,null,'*') full_name3,
ppf.effective_start_date,
ppf2.effective_start_date effective_start_date2,
decode(ppf.effective_start_date, ppf2.effective_start_date,null,'*') effective_start_date3,
ppf.effective_end_date,
ppf2.effective_end_date effective_end_date2,
decode(ppf.effective_end_date, ppf2.effective_end_date,null,'*') effective_end_date3,
ppf.last_name,
ppf2.last_name last_name2,
decode(ppf.last_name, ppf2.last_name,null,'*') last_name3,
ppf.date_of_birth,
ppf2.date_of_birth date_of_birth2,
decode(ppf.date_of_birth, ppf2.date_of_birth,null,'*') date_of_birth3,
ppf.first_name,
ppf2.first_name first_name2,
decode(ppf.first_name, ppf2.first_name,null,'*') first_name3,
ppf.known_as,
ppf2.known_as known_as2,
decode(ppf.known_as, ppf2.known_as,null,'*') known_as3,
hr_general.decode_lookup('MAR_STATUS',ppf.marital_status) marital_status,
hr_general.decode_lookup('MAR_STATUS',ppf2.marital_status) marital_status2,
decode(ppf.marital_status, ppf2.marital_status,null,'*') marital_status3,
ppf.middle_names,
ppf2.middle_names middle_names2,
decode(ppf.middle_names, ppf2.middle_names,null,'*') middle_names3,
hr_general.decode_lookup('NATIONALITY',ppf.nationality) nationality,
hr_general.decode_lookup('NATIONALITY',ppf2.nationality) nationality2,
decode(ppf.nationality, ppf2.nationality,null,'*') nationality3,
hr_general.decode_lookup('SEX',ppf.sex) sex,
hr_general.decode_lookup('SEX',ppf2.sex) sex2,
decode(ppf.sex, ppf2.sex,null,'*') sex3,
hr_general.decode_lookup('TITLE',ppf.title) title,
hr_general.decode_lookup('TITLE',ppf2.title) title2,
decode(ppf.title, ppf2.title,null,'*') title3,
hr_general.decode_lookup('BLOOD_TYPE',ppf.blood_type) blood_type,
hr_general.decode_lookup('BLOOD_TYPE',ppf2.blood_type) blood_type2,
decode(ppf.blood_type, ppf2.blood_type,null,'*') blood_type3,
fl.description correspondence_language,
fl2.description correspondence_language2,
decode(fl.description, fl2.description,null,'*') correspondence_language3,
ppf.honors,
ppf2.honors honors2,
decode(ppf.honors, ppf2.honors,null,'*') honors3,
ppf.pre_name_adjunct,
ppf2.pre_name_adjunct pre_name_adjunct2,
decode(ppf.pre_name_adjunct, ppf2.pre_name_adjunct,null,'*')
pre_name_adjunct3,
ppf.rehire_authorizor,
ppf2.rehire_authorizor rehire_authorizor2,
decode(ppf.rehire_authorizor, ppf2.rehire_authorizor,null,'*') rehire_authorizor3,
hr_general.decode_lookup('YES_NO',ppf.rehire_recommendation) rehire_recommendation,
hr_general.decode_lookup('YES_NO',ppf2.rehire_recommendation) rehire_recommendation2,
decode(ppf.rehire_recommendation, ppf2.rehire_recommendation,null,'*') rehire_recommendation3,
hr_general.decode_lookup('YES_NO',ppf.resume_exists) resume_exists,
hr_general.decode_lookup('YES_NO',ppf2.resume_exists) resume_exists2,
decode(ppf.resume_exists, ppf2.resume_exists,null,'*') resume_exists3,
ppf.resume_last_updated,
ppf2.resume_last_updated resume_last_updated2,
decode(ppf.resume_last_updated, ppf2.resume_last_updated,null,'*') resume_last_updated3,
hr_general.decode_lookup('YES_NO',ppf.second_passport_exists) second_passport_exists,
hr_general.decode_lookup('YES_NO',ppf2.second_passport_exists) second_passport_exists2,
decode(ppf.second_passport_exists, ppf2.second_passport_exists,null,'*') second_passport_exists3,
hr_general.decode_lookup('STUDENT_STATUS',ppf.student_status) student_status,
hr_general.decode_lookup('STUDENT_STATUS',ppf2.student_status) student_status2,
decode(ppf.student_status, ppf2.student_status,null,'*') student_status3,
ppf.suffix,
ppf2.suffix suffix2,
decode(ppf.suffix, ppf2.suffix,null,'*') suffix3,
ppf.date_of_death,
ppf2.date_of_death date_of_death2,
decode(ppf.date_of_death, ppf2.date_of_death,null,'*')
date_of_death3,
hr_general.decode_lookup('TOBACCO_USER',ppf.uses_tobacco_flag) uses_tobacco_flag,
hr_general.decode_lookup('TOBACCO_USER',ppf2.uses_tobacco_flag) uses_tobacco_flag2,
decode(ppf.uses_tobacco_flag, ppf2.uses_tobacco_flag,null,'*') uses_tobacco_flag3,
ppf.original_date_of_hire,
ppf2.original_date_of_hire original_date_of_hire2,
decode(ppf.original_date_of_hire, ppf2.original_date_of_hire,null,'*')
original_date_of_hire3,
ppf.town_of_birth,
ppf2.town_of_birth town_of_birth2,
decode(ppf.town_of_birth, ppf2.town_of_birth,null,'*') town_of_birth3,
ppf.region_of_birth,
ppf2.region_of_birth region_of_birth2,
decode(ppf.region_of_birth, ppf2.region_of_birth,null,'*') region_of_birth3,
ftv.territory_short_name country_of_birth,
ftv2.territory_short_name country_of_birth2,
decode(ftv.territory_short_name, ftv2.territory_short_name,null,'*') country_of_birth3,
hr_general.decode_lookup('YES_NO',ppf.fast_path_employee) fast_path_employee,
hr_general.decode_lookup('YES_NO',ppf2.fast_path_employee) fast_path_employee2,
decode(ppf.fast_path_employee, ppf2.fast_path_employee,null,'*') fast_path_employee3,
ppf.email_address,
ppf2.email_address email_address2,
decode(ppf.email_address, ppf2.email_address,null,'*') email_address3,
ppf.fte_capacity,
ppf2.fte_capacity fte_capacity2,
decode(ppf.fte_capacity, ppf2.fte_capacity,null,'*') fte_capacity3
from per_people_f ppf,
fnd_territories_vl ftv,
fnd_languages_vl fl,
per_people_f ppf2,
fnd_territories_vl ftv2,
fnd_languages_vl fl2,
per_business_groups pbg,
per_business_groups pbg2
where :c_effective_date
between ppf.effective_start_date
and ppf.effective_end_date
and :c_effective_date
between ppf2.effective_start_date
and ppf2.effective_end_date
and ppf.business_group_id <> :p_business_group_id
and ppf2.business_group_id = :p_business_group_id
and pbg.business_group_id = ppf.business_group_id
and pbg2.business_group_id = ppf2.business_group_id
and ppf2.party_id = ppf.party_id
and ppf2.person_id = nvl(null, ppf2.person_id)
and ftv.territory_code(+) = ppf.country_of_birth
and ftv2.territory_code(+) = ppf2.country_of_birth
and ftv2.territory_code(+) = ppf2.country_of_birth
and fl.language_code(+) = ppf.correspondence_language
and fl2.language_code(+) = ppf2.correspondence_language
and ppf2.party_id = nvl(:c_party_id,ppf2.party_id)
order by nvl(ppf2.order_name,ppf2.full_name) |
