PQH Upcoming Events

Description
Categories: BI Publisher, Human Resources
Application: Public Sector HR
Source: Tenure: Upcoming Events Report (XML)
Short Name: PQHUSUCE_XML
DB package: PQH_PQHUSUCE_XMLP_PKG
SELECT 	hr.meaning 	Event_Review_Type, 
	pe.date_start , pe.time_start
FROM 	per_events 		pe,
	per_bookings		pb,
	per_all_people_f 		pap,
	hr_lookups 		hr
WHERE 	pe.event_id		= pb.event_id 
AND 	pap.person_id		= pb.person_id 
AND 	pe.type 			= hr.lookup_code
AND 	hr.lookup_type 		= 'EMP_EVENT_TYPE'
AND	pe.event_or_interview 	= 'E'
AND	SYSDATE			BETWEEN pap.effective_start_date and pap.effective_end_date
AND 	pe.business_group_id+0	= :p_business_group_id
AND	pap.person_id		= :p_person_id
AND	pe.date_start		>= SYSDATE
AND	UPPER(hr.meaning)	LIKE  '%TENURE%'
UNION
SELECT 	hr.meaning, 	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
AND	pe.date_start		>= SYSDATE
Parameter Name SQL text Validation
Business Group Id
 
PER_CHAR
 
Faculty Member
 
LOV Oracle