Query for getting Applicant Details.

SELECT
papf.person_id,
papf.applicant_number,
papf.full_name,
papf.date_of_birth,
papf.nationality,
papf.marital_status,
paaf.organization_id,
paaf.location_id,
paaf.supervisor_id,
pe.type,
pe.location_id,
pe.date_start
FROM
per_all_people_f papf,
per_all_assignments_f paaf,
per_events pe
WHERE
papf.business_group_id = P_BUSINESS_GROUP_ID
AND papf.current_applicant_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND papf.person_id = paaf.person_id
AND paaf.primary_flag = 'Y'
AND pe.assignment_id = paaf.assignment_id;