PQH IPEDS Report - Employees By Assigned Position (EAP) - Part A(obsoleted)

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