In this article we will look into how to get the Candidate Job Application Status.
In ORC, the requisitions are posted, after that external/internal candidates apply to those requisitions and those candidates move through different phases/states like New/Reviewed, Selection and Interview/Selected, Offer/Accepted, HR/Processed based on the configuration of Candidate Selection Process (CSP).
The Phase/States could be like below based on the config of the CSP:
Phase Code Phase Description State Code State Description NEW New phase TO_BE_REVIEWED To be Reviewed State NEW New phase REVIEWED Reviewed State NEW New phase SELECTED_FOR_SCREENING Selected For Screening NEW New phase REJECTED_EMPLOYER Rejected by Employer NEW New phase WITHDRAWN_BY_CANDIDATE Withdrawn by Candidate SCREENING Screen PHONE_SCREEN_TO_BE_SCHEDULED Phone Screen to be Scheduled SCREENING Screen PHONE_SCREEN_SCHEDULED Phone Screen Scheduled SCREENING Screen PHONE_SCREEN_COMPLETED Phone Screen Completed SCREENING Screen SELECTED_FOR_INTERVIEW Selected for Interview SCREENING Screen REJECTED_EMPLOYER Rejected by Employer SCREENING Screen WITHDRAWN_BY_CANDIDATE Withdrawn by Candidate INTERVIEW_AND_SELECTION Interview and Selection INTERVIEW_SCHEDULED Interview Scheduled INTERVIEW_AND_SELECTION Interview and Selection INTERVIEW_COMPLETED Interview Completed INTERVIEW_AND_SELECTION Interview and Selection FEEDBACK_REQUESTED Feedback Requested INTERVIEW_AND_SELECTION Interview and Selection FEEDBACK_COMPLETED Feedback Completed INTERVIEW_AND_SELECTION Interview and Selection SELECTED_FOR_OFFER Selected for Offer INTERVIEW_AND_SELECTION Interview and Selection REJECTED_EMPLOYER Rejected by Employer INTERVIEW_AND_SELECTION Interview and Selection WITHDRAWN_BY_CANDIDATE Withdrawn by Candidate OFFER Offer TO_BE_CREATED To be Created OFFER Offer OFFER_DRAFT Draft OFFER Offer OFFER_APPROVAL Pending Approval OFFER Offer OFFER_APPROVAL_REJECTED Approval Rejected OFFER Offer OFFER_APPROVED Approved OFFER Offer OFFER_EXTENDED Extended OFFER Offer OFFER_ACCEPTED Accepted OFFER Offer REJECTED_EMPLOYER Rejected by Employer OFFER Offer WITHDRAWN_BY_CANDIDATE Withdrawn by Candidate HR HR AUTOPROCESSING_PENDING Pending Automated Processing HR HR TO_BE_PROCESSED Pending Manual Processing HR HR PROCESSING_ERROR Error During Processing HR HR PROCESSING Processing in Progress HR HR PROCESSED Processed HR HR REJECTED_EMPLOYER Rejected by Employer HR HR WITHDRAWN_BY_CANDIDATE Withdrawn by Candidate
Every candidate might be in a different phase/state at a give point of time and it is very important for the Recruiter/Hiring Manager to know the status of the Candidate.
The below query will help identify the Phase/State of the Candidate Job Applications.
select distinct ic.candidate_number,
ppnfv.full_name,
irv.requisition_number,
ipv.name as phase,
isv.name as state
from irc_requisitions_vl irv,
irc_submissions isub,
irc_phases_vl ipv,
irc_states_vl isv,
per_person_names_f_v ppnfv,
irc_candidates ic
where 1=1
AND irv.REQUISITION_ID (+) = isub.REQUISITION_ID
AND ipv.PHASE_ID (+) = isub.CURRENT_PHASE_ID
AND isv.STATE_ID (+) = isub.CURRENT_STATE_ID
AND isub.PERSON_ID (+) = ic.PERSON_ID
AND ic.PERSON_ID = ppnfv.PERSON_ID
AND ppnfv.NAME_TYPE = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN ppnfv.EFFECTIVE_START_DATE AND ppnfv.EFFECTIVE_END_DATE
ORDER BY ic.candidate_numberIf there is a need to get the status of the candidates for a single requisition, requisition number can be added to the where clause.
