PQP OSS HRMS Comparison

Description
Categories: BI Publisher, Human Resources
Application: Public Sector Payroll
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
Sort By Desc
 
Sort By
 
Data Match Filter Description
 
Report Run Date
 
Data Match Filter
 
Report Name
 
Student Number
 
National Identifier
 
First Name
 
Last Name
 
Person ID Group
 
Location ID
 
Location Value
 
Payroll ID
 
Payroll Name
 
Organization ID
 
Organization Name
 
Person Type Description
 
Person Type
 
Effective End Date
 
Effective Start Date
 
Business Group ID
 
Business Group Name
 
Person ID Group Query