ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F4153869E053B46B63588994Y 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 PQP PQPCMPRPT AR PQP تقرير المقارنة بين OSS وHRMS Application: جدول أجور القطاع العام Source: تقرير المقارنة بين OSS وHRMS Short Name: PQPCMPRPT DB package: pqp_hross_reports D PQP OSS-/HRMS-Vergleichsauswertung Application: Public Sector Payroll Source: OSS-/HRMS-Vergleichsauswertung Short Name: PQPCMPRPT DB package: pqp_hross_reports F PQP Etat comparatif OSS et HRMS Application: Public Sector Payroll Source: Etat comparatif OSS et HRMS Short Name: PQPCMPRPT DB package: pqp_hross_reports US PQP OSS HRMS Comparison Application: Public Sector Payroll Source: OSS HRMS Comparison Report Short Name: PQPCMPRPT DB package: pqp_hross_reports ZHS PQP OSS HRMS 比较报表 Application: Public Sector Payroll Source: OSS HRMS 比较报表 Short Name: PQPCMPRPT DB package: pqp_hross_reports BI Publisher &address_end_date_where &assignment_end_date_where &order_by_clause &person_end_date_where &person_id_list_where :address_end_date_where :assignment_end_date_where :first_name_where :generic_where :last_name_where :location_name_where :matching_records_counter :mismatch_records_counter :national_identifier_where :order_by_clause :organnization_name_where :p_business_group_id :p_business_group_name :p_data_match_filter :p_data_match_filter_desc :p_effective_end_date :p_effective_start_date :p_first_name :p_last_name :p_location_id :p_location_value :p_national_identifier :p_organization_id :p_organization_name :p_payroll_id :p_payroll_name :p_person_id_group :p_person_id_group_query :p_person_type :p_person_type_desc :p_report_name :p_report_run_date :p_sort_by :p_sort_by_desc :p_student_number :payroll_name_where :person_end_date_where :person_id_group_query_where :person_id_list_where :person_type_where :student_number_where 1 10 :p_person_id_group_query Char US Person ID Group Query 2 20 :p_business_group_name Char US Business Group Name 3 30 :p_business_group_id Char US Business Group ID 4 40 :p_effective_start_date Char US Effective Start Date 5 50 :p_effective_end_date Char US Effective End Date 6 60 :p_person_type Char US Person Type 7 70 :p_person_type_desc Char US Person Type Description 8 80 :p_organization_name Char US Organization Name 9 90 :p_organization_id Char US Organization ID 10 100 :p_payroll_name Char US Payroll Name 11 110 :p_payroll_id Char US Payroll ID 12 120 :p_location_value Char US Location Value 13 130 :p_location_id Char US Location ID 14 140 :p_person_id_group Char US Person ID Group 15 150 :p_last_name Char US Last Name 16 160 :p_first_name Char US First Name 17 170 :p_national_identifier Char US National Identifier 18 180 :p_student_number Char US Student Number 19 190 :p_report_name Char US Report Name 20 200 :p_data_match_filter Char US Data Match Filter 21 210 :p_report_run_date Char US Report Run Date 22 220 :p_data_match_filter_desc Char US Data Match Filter Description 23 230 :p_sort_by Char US Sort By 24 240 :p_sort_by_desc Char US Sort By Desc