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_number
If there is a need to get the status of the candidates for a single requisition, requisition number can be added to the where clause.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM