ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D471EB51A9E05362FB09050512 select hla.lookup_code RankCode, peo.sex GenCode, nvl(count(peo.person_id),0) tot_number, round(sum(NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)) Salary_range, round(nvl(avg(NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor),0)) avg_sal from hr_lookups hla, per_all_people_f peo, per_all_assignments_f ass, per_contracts_f pco, per_pay_proposals ppp, per_pay_bases ppb, per_people_extra_info ppea, per_jobs job, hr_lookups hl, per_shared_types pst, per_shared_types pst1 WHERE peo.person_id = ass.person_id AND peo.current_employee_flag = 'Y' AND ass.assignment_type = 'E' AND peo.person_id = pco.person_id AND peo.person_id = ppea.person_id AND :p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date AND :p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND :p_report_date BETWEEN pco.effective_start_date AND pco.effective_end_date AND :p_report_date BETWEEN fnd_date.canonical_to_date (ppea.pei_information2) AND fnd_date.canonical_to_date(NVL(ppea.pei_information3, '4712/12/31 00:00:00')) AND ass.pay_basis_id = ppb.pay_basis_id AND ass.assignment_id = ppp.assignment_id AND hla.lookup_code = ppea.pei_information1 AND ppea.pei_information1 IS NOT NULL AND ppea.pei_information_category = 'PQH_ACADEMIC_RANK' AND hla.lookup_type = 'PQH_ACADEMIC_RANK' AND hla.lookup_code != '01' AND ass.primary_flag = 'Y' and pco.type = 'FULL_TIME' and pco.status = pst.system_type_cd and pst.lookup_type = 'CONTRACT_STATUS' and pst1.system_type_cd(+) = pst.system_type_cd and pst1.lookup_type(+) = pst.lookup_type and pst.business_group_id is null and pst1.business_group_id(+) = :p_business_group_id and nvl(pst1.information1,pst.information1) = 'Y' AND pqh_employment_category.get_duration_in_months(pco.duration, pco.duration_units, pco.business_group_id, :p_report_date) < 9 AND job.job_information8 is not null AND job.job_information8 in ('1', '2', '3') AND hl.lookup_code = job.job_information8 AND pqh_employment_category.identify_empl_category(ass.employment_category,:cp_fr,:cp_ft,:cp_pr,:cp_pt) IN ('FR') AND hl.lookup_type = 'US_IPEDS_JOB_CATEGORIES' AND job.job_information_category = 'US' AND ass.job_id = job.job_id AND ppp.change_date = (SELECT MAX(change_date) FROM per_pay_proposals pro WHERE ppp.assignment_id = pro.assignment_id AND pro.change_date <= :p_report_date AND pro.approved = 'Y') AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0 AND ass.organization_id IN ( SELECT org.organization_id FROM hr_all_organization_units org WHERE business_group_id = :p_business_group_id AND pqh_inst_type_pkg.get_inst_type(org.organization_id) = 'NON-MED' ) and hla.lookup_code=:Rank and peo.sex=:Gender group by hla.lookup_code,peo.sex union select '01' RankCode, peo.sex GenCode, count(peo.person_id) tot_number, round(sum(NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)) Salary_range, round(avg(NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)) avg_sal from per_all_people_f peo, per_all_assignments_f ass, per_contracts_f pco, per_pay_proposals ppp, per_pay_bases ppb, per_jobs job, hr_lookups hl, per_shared_types pst, per_shared_types pst1 WHERE peo.person_id = ass.person_id AND peo.current_employee_flag = 'Y' AND ass.assignment_type ='E' AND peo.person_id = pco.person_id AND :p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date AND :p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND :p_report_date BETWEEN pco.effective_start_date AND pco.effective_end_date AND ass.pay_basis_id = ppb.pay_basis_id AND ass.assignment_id = ppp.assignment_id AND ass.primary_flag = 'Y' AND not exists (select pea.person_id from per_people_extra_info pea where pea.person_id = peo.person_id and pea.information_type in 'PQH_ACADEMIC_RANK' and pea.pei_information1 in ('02','03','04','05','06') and :p_report_date between fnd_date.canonical_to_date(pea.pei_information2) and fnd_date.canonical_to_date(NVL(pea.pei_information3, '4712/12/31 00:00:00'))) and pco.type = 'FULL_TIME' and pco.status = pst.system_type_cd and pst.lookup_type = 'CONTRACT_STATUS' and pst1.system_type_cd(+) = pst.system_type_cd and pst1.lookup_type(+) = pst.lookup_type and pst.business_group_id is null and pst1.business_group_id(+) = :p_business_group_id and nvl(pst1.information1,pst.information1) = 'Y' AND pqh_employment_category.get_duration_in_months(pco.duration, pco.duration_units, pco.business_group_id, :p_report_date) < 9 AND job.job_information8 is not null AND job.job_information8 in ('1', '2', '3') AND hl.lookup_code = job.job_information8 AND pqh_employment_category.identify_empl_category(ass.employment_category,:cp_fr,:cp_ft,:cp_pr,:cp_pt) IN ('FR') AND hl.lookup_type = 'US_IPEDS_JOB_CATEGORIES' AND job.job_information_category = 'US' AND ass.job_id = job.job_id AND ppp.change_date = (SELECT MAX(change_date) FROM per_pay_proposals pro WHERE ppp.assignment_id = pro.assignment_id AND pro.change_date <= :p_report_date AND pro.approved = 'Y') AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0 AND ass.organization_id IN ( SELECT org.organization_id FROM hr_all_organization_units org WHERE business_group_id = :p_business_group_id AND pqh_inst_type_pkg.get_inst_type(org.organization_id) = 'NON-MED') and '01'=:Rank and peo.sex=:Gender group by peo.sex PQH PQIPED3_XML US PQH IPEDS Report - Salary and Fringe Benefits Survey (SA) - Part A(obsoleted) Imported from BI Publisher Application: Public Sector HR Source: IPEDS Report - Salary and Fringe Benefits Survey (SA) - Part A (XML) (obsoleted) Short Name: PQIPED3_XML DB package: PQH_PQIPED3_XMLP_PKG BI Publisher :cp_fr :cp_ft :cp_pr :cp_pt :cp_report_date :cp_tottitleperreport :gender :line_num :p_business_group_id :p_conc_request_id :p_report_date :p_report_day_month :rank :reporttotlineno 1 -20 :p_business_group_id Char fnd_profile.value('PER_BUSINESS_GROUP_ID') US Business Group ID Business Group ID 2 10 :p_report_date Date Y US Reporting Date Reporting Date