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 |
