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.
If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.
