PER Person Synchronization

Description

Imported from BI Publisher
Application: Human Resources
Source: Person Synchronization Report (XML)
Short Name: PERBGSYN_XML
DB package: PER_PERBGSYN_XMLP_PKG

PER Person Synchronization and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

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)

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)

Parameter Name

SQL text

Validation

PER_DATES_STANDARD

Date

Person Name

LOV Oracle

Download