PQP OSS HRMS Comparison

Description

Application: Public Sector Payroll
Source: OSS HRMS Comparison Report
Short Name: PQPCMPRPT
DB package: pqp_hross_reports

PQP OSS HRMS Comparison and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

				     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

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

Download

Related Blitz Reports

PER Full Assignment Details PER Worker Summary PER Employee Assignments PER Assignment Status PQH IPEDS Report - Salary and Fringe Benefits Survey (SA) - Part A(obsoleted) PER New Hire State PAY Business Payrolls Survey BEN Back-out Life Events Audit Log BEN Participation Audit Log

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Find out more

Features

Download

FAQ