PQH Existing Profile

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(decode(eits.pei_information1,'02',
		nvl(eits.pei_information4,eits.pei_information3), eits.pei_information2))	Tenure_Status_date, 
	PQH_PQHUSEPR_XMLP_PKG.cf_tenure_statusformula(eits.pei_information1, eiar.pei_information1) CF_tenure_status, 
	PQH_PQHUSEPR_XMLP_PKG.cf_academic_rankformula(eiar.pei_information1) CF_Academic_Rank, 
	PQH_PQHUSEPR_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	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	eits.pei_information1	<> '03'  	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