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

SELECT  
pqh_inst_type_pkg.get_inst_type(paf.organization_id) Org_Code1,
hl.lookup_code Job_Code1,
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
--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)) 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)  	= 'PR'
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 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