PQH Recap

select  	paf.supervisor_id,
	ppf.last_name||', '||ppf.first_name 	person_name, 
	eiar.pei_information1			Rank_id, 
	paf.job_id , 
	eits.pei_information1			Tenure_Status_cd,
	fnd_date.canonical_to_date( eits.pei_information2) 	Tenure_Status_date, 
	PQH_PQHUSRCP_XMLP_PKG.cf_tenure_statusformula(eits.pei_information1, eiar.pei_information1) CF_tenure_status, 
	PQH_PQHUSRCP_XMLP_PKG.cf_set_total_for_periodformula(eits.pei_information1, count(ppf.last_name||', '||ppf.first_name) over (partition by paf.supervisor_id,
	eiar.pei_information1,paf.job_id,eits.pei_information1,fnd_date.canonical_to_date( eits.pei_information2))) CF_set_total_for_period, 
	PQH_PQHUSRCP_XMLP_PKG.cf_academic_rankformula(eiar.pei_information1) CF_Academic_Rank, 
	PQH_PQHUSRCP_XMLP_PKG.cf_job_nameformula(paf.job_id) CF_job_name
from 	per_all_people_f   		ppf,
     	per_assignments_f  		paf,
     	per_people_extra_info  	eiar,
     	per_people_extra_info  	eits
where  	ppf.person_id   	= paf.person_id
and    	sysdate  between ppf.effective_start_date and ppf.effective_end_date
and    	sysdate between  paf.effective_start_date and paf.effective_end_date
and	sysdate between fnd_date.canonical_to_date( eiar.pei_information2)  and
		nvl(fnd_date.canonical_to_date( eiar.pei_information3),sysdate)
and	fnd_date.canonical_to_date( eits.pei_information2)
		between :p_start_date and :p_end_date
and	eits.pei_information1 not in ('02','03')	and	ppf.business_group_id= :p_business_group_id
and	paf.business_group_id= :p_business_group_id
and    	paf.primary_flag      	= 'Y'
and    	eiar.information_type 	= 'PQH_ACADEMIC_RANK'
and    	eits.information_type 	= 'PQH_TENURE_STATUS'
and    	eiar.person_id        	= ppf.person_id
and    	eits.person_id        	= ppf.person_id 
 and paf.supervisor_id=:manager_id
--order by 	eits.pei_information2
order by Tenure_Status_cd,person_name,Tenure_Status_date,Rank_id,job_id