WITH QMAIN AS(
SELECT ppf.full_name FullName
,ppf.first_name FirstName
,ppf.last_name LastName
,ppf.national_identifier NationalIdentifier
,pqp_hross_reports.Get_Date(ppf.date_of_birth) EmployeeDOB
,pqp_hross_reports.Get_Date(ppf.last_update_date) HrPersonLastUpdateDate
,per.address_line1 AddressLine1
,per.address_line2 AddressLine2
,per.address_line3 AddressLine3
,per.town_or_city EmployeeCity
,per.region_2 PrimaryAddressState
,per.region_1 PrimaryAddressCounty
,per.postal_code PostalCode
,per.country PrimaryAddressCountry
,pqp_hross_reports.Get_Date(per.last_update_date) HrAddressLastUpdateDate
,per.telephone_number_1 Telephone1
,per.telephone_number_2 Telephone2
,ppf.email_address EmailAddress
,pqp_hross_reports.Get_Date(per.last_update_date) HrContactLastUpdateDate
,ipe.given_names StudentFirstName
,ipe.surname StudentLastName
,ipe.api_person_id StudentNationalIdentifier
,pqp_hross_reports.Get_Date(ipe.birth_dt) StudentDOB
,ipe.person_number StudentNumber
,pqp_hross_reports.Get_Date(ipe.last_update_date) OssPersonLastUpdateDate
,hzl.address1 StudentAddressLine1
,hzl.address2 StudentAddressLine2
,hzl.address3 StudentAddressLine3
,hzl.city StudentEmployeeCity
,hzl.state StudentPrimaryAddressState
,hzl.county StudentPrimaryAddressCounty
,hzl.country StudentPrimaryAddressCountry
,hzl.postal_code StudentPostalCode
,pqp_hross_reports.Get_Date(hzl.last_update_date) OssAddressLastUpdateDate
,hcp.phone_country_code || ' ' || hcp.phone_area_code || ' ' || hcp.phone_number || ' ' || hcp.phone_extension StudentTelephone1
,pqp_hross_reports.Get_Secondary_Telephone_Number(hcp.owner_table_id) StudentTelephone2
,ipe.email_addr StudentEmailAddress
,pqp_hross_reports.Get_Date(hcp.last_update_date) OssContactLastUpdateDate
,pqp_hross_reports.Compare_Values(TRIM(ppf.first_name), TRIM(ipe.given_names)) CompareFirstName
,pqp_hross_reports.Compare_Values(TRIM(ppf.last_name), TRIM(ipe.surname)) CompareLastName
,pqp_hross_reports.Compare_Values(TRIM(ppf.national_identifier), TRIM(ipe.api_person_id)) CompareNationalIdentifier
,pqp_hross_reports.Compare_Values(ppf.date_of_birth, ipe.birth_dt) CompareDOB
,pqp_hross_reports.Compare_Values(TRIM(per.address_line1), TRIM(hzl.address1)) CompareAddressLine1
,pqp_hross_reports.Compare_Values(TRIM(per.address_line2), TRIM(hzl.address2)) CompareAddressLine2
,pqp_hross_reports.Compare_Values(TRIM(per.address_line3), TRIM(hzl.address3)) CompareAddressLine3
,pqp_hross_reports.Compare_Values(TRIM(per.town_or_city), TRIM(hzl.city)) CompareEmployeeCity
,pqp_hross_reports.Compare_Values(TRIM(per.region_2), TRIM(hzl.state)) ComparePrimaryAddressState
,pqp_hross_reports.Compare_Values(TRIM(per.region_1), TRIM(hzl.county)) ComparePrimaryAddressCounty
,pqp_hross_reports.Compare_Values(TRIM(per.postal_code), TRIM(hzl.postal_code)) ComparePostalCode
,pqp_hross_reports.Compare_Values(TRIM(per.country), TRIM(hzl.country)) ComparePrimaryAddressCountry
,pqp_hross_reports.Compare_Values(TRIM(TRANSLATE(per.telephone_number_1, '()+-*x', ' ' )), TRIM(TRANSLATE(hcp.phone_country_code || hcp.phone_area_code || hcp.phone_number || hcp.phone_extension, '()+-*x', ' ' ))) CompareTelephone1
,pqp_hross_reports.Compare_Values(TRIM(TRANSLATE(per.telephone_number_2, '()+-*x', ' ' )), TRIM(TRANSLATE(pqp_hross_reports.Get_Secondary_Telephone_Number(hcp.owner_table_id), '()+-*x', ' ' ))) CompareTelephone2
,pqp_hross_reports.Compare_Values(TRIM(ppf.email_address), TRIM(ipe.email_addr)) CompareEmailAddress
,pqp_hross_reports.Get_Mismatch_Indicator_Flag MismatchIndicatorFlag
,ppt.user_person_type PersonType
,rownum SerialNumber
FROM per_people_f ppf
,per_assignments_f paf
,per_assignment_status_types pas
,per_business_groups pbg
,pay_payrolls_f prl
,hr_organization_units hao
,hr_locations hrl
,per_people_extra_info pei
,per_person_types ppt
,igs_pe_person_v ipe
,per_addresses per
,hz_party_sites hps
,hz_locations hzl
,hz_contact_points hcp
WHERE ppf.party_id = ipe.person_id
&PERSON_ID_LIST_WHERE
&PERSON_END_DATE_WHERE
AND ppf.person_id = paf.person_id
AND paf.assignment_type = 'E'
&ASSIGNMENT_END_DATE_WHERE
AND pas.assignment_status_type_id = paf.assignment_status_type_id
AND pas.per_system_status = 'ACTIVE_ASSIGN'
AND pas.active_flag = 'Y'
AND ( pas.business_group_id = paf.business_group_id
OR pas.legislation_code = pbg.legislation_code
OR ( pas.business_group_id Is Null
AND pas.legislation_code Is Null ))
AND pbg.business_group_id = ppf.business_group_id
AND ppf.person_id = pei.person_id
AND pei.information_type = 'PQP_OSS_PERSON_DETAILS'
AND pei.pei_information_category = 'PQP_OSS_PERSON_DETAILS'
AND ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP','EMP_APL')
AND ppt.active_flag = 'Y'
AND ppf.person_id = per.person_id(+)
&ADDRESS_END_DATE_WHERE
AND per.primary_flag(+) = 'Y'
AND hao.organization_id = paf.organization_id
AND hrl.location_id = paf.location_id
AND prl.payroll_id(+) = paf.payroll_id
AND prl.business_group_id(+) = paf.business_group_id
AND hps.party_id(+) = ipe.person_id
AND hzl.location_id(+) = hps.location_id
AND hps.identifying_address_flag(+) = 'Y'
AND hcp.owner_table_id(+) = ipe.person_id
AND hcp.contact_point_type(+) = 'PHONE'
AND hcp.primary_flag(+) = 'Y'
&ORDER_BY_CLAUSE
)
SELECT QMAIN.*
,ROWNUM SortSequence
FROM QMAIN
ORDER BY SortSequence |