PQP OSS HRMS Comparison
Description
Categories: BI Publisher
Application: Public Sector Payroll
Source: OSS HRMS Comparison Report
Short Name: PQPCMPRPT
DB package: pqp_hross_reports
Source: OSS HRMS Comparison Report
Short Name: PQPCMPRPT
DB package: pqp_hross_reports
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Person ID Group Query |
|
Char | |
Business Group Name |
|
Char | |
Business Group ID |
|
Char | |
Effective Start Date |
|
Char | |
Effective End Date |
|
Char | |
Person Type |
|
Char | |
Person Type Description |
|
Char | |
Organization Name |
|
Char | |
Organization ID |
|
Char | |
Payroll Name |
|
Char | |
Payroll ID |
|
Char | |
Location Value |
|
Char | |
Location ID |
|
Char | |
Person ID Group |
|
Char | |
Last Name |
|
Char | |
First Name |
|
Char | |
National Identifier |
|
Char | |
Student Number |
|
Char | |
Report Name |
|
Char | |
Data Match Filter |
|
Char | |
Report Run Date |
|
Char | |
Data Match Filter Description |
|
Char | |
Sort By |
|
Char | |
Sort By Desc |
|
Char |