SELECT
pqh_inst_type_pkg.get_inst_type(paf.organization_id) Org_Code1,
hl.lookup_code Job_Code1,
--pqh_inst_type_pkg.get_inst_type(paf.organization_id) Org_Code,
--hl.lookup_code Job_Code,
count(decode(hl.lookup_code,'1',DECODE(ppe.pei_information1,'01',1,null),'2',DECODE(ppe.pei_information1,'01',1,null),'3',DECODE(ppe.pei_information1,'01',1,null),null)) tenured1,
count(decode(hl.lookup_code,'1',DECODE(ppe.pei_information1,'02',1,'04',1,null),'2',DECODE(ppe.pei_information1,'02',1,'04',1,null),'3',DECODE(ppe.pei_information1,'02',1,'04',1,null),null)) ontenure1,
count(decode(hl.lookup_code,'1',null,'2',null,'3',null,peo.person_id)) nonFaculty1
--count(decode(hl.lookup_code,'1',DECODE(ppe.pei_information1,'01',1,null),'2',DECODE(ppe.pei_information1,'01',1,null),'3',DECODE(ppe.pei_information1,'01',1,null),null)) tenured,
--count(decode(hl.lookup_code,'1',DECODE(ppe.pei_information1,'02',1,'04',1,null),'2',DECODE(ppe.pei_information1,'02',1,'04',1,null),'3',DECODE(ppe.pei_information1,'02',1,'04',1,null),null)) ontenure,
--count(decode(hl.lookup_code,'1',null,'2',null,'3',null,peo.person_id)) nonFaculty
from hr_lookups hl,
per_jobs job ,
per_all_assignments_f paf,
per_people_extra_info ppe,
per_all_people_f peo,
per_pay_proposals ppp,
per_pay_bases ppb
WHERE paf.person_id = ppe.person_id
AND peo.person_id = paf.person_id
AND peo.current_employee_flag = 'Y'
AND paf.job_id = job.job_id
AND ppe.information_type in 'PQH_TENURE_STATUS'
AND :p_report_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND :p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND fnd_date.canonical_to_date(ppe.pei_information2) <= :p_report_date
AND hl.lookup_code = job.job_information8
AND job.job_information8 in ('1', '2', '3')
AND job.job_information8 IS NOT NULL
AND job.job_information_category = 'US'
AND hl.lookup_type like 'US_IPEDS_JOB_CATEGORIES'
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND pqh_employment_category.identify_empl_category(paf.employment_category,:cp_fr,:cp_ft,:cp_pr,
:cp_pt) = 'FR'
AND paf.pay_basis_id = ppb.pay_basis_id
AND paf.assignment_id = ppp.assignment_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 paf.organization_id in (
select organization_id
from hr_all_organization_units
where business_group_id = :p_business_group_id)
and hl.lookup_code=:JobCode
and pqh_inst_type_pkg.get_inst_type ( paf.organization_id )=:OrgCode
group by pqh_inst_type_pkg.get_inst_type(paf.organization_id), hl.lookup_code |
