PQH Notification for Tenure-track Status

SELECT 	hr.meaning		Review_type,
 	pe.date_start , pe.time_start
FROM 	per_all_assignments_f	paf,
	per_events 		pe,
	hr_lookups 		hr,
	per_all_people_f		pap
WHERE 	pe.assignment_id		= paf.assignment_id
AND	pap.person_id		= paf.person_id
AND 	pe.type 			= hr.lookup_code
AND	SYSDATE			BETWEEN paf.effective_start_date and paf.effective_end_date
AND	SYSDATE			BETWEEN pap.effective_start_date and pap.effective_end_date
AND 	pe.emp_or_apl		= 'E'
AND 	hr.lookup_type 		= 'EMP_INTERVIEW_TYPE'
AND	pe.event_or_interview 	= 'I'
AND	pap.person_id		= :p_person_id
AND	pe.business_group_id +0 	= :p_business_group_id