You are currently viewing SQL Query to fetch ORC Candidate Job Application Status
SQL Query to fetch ORC Candidate Job Application Status

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 CodePhase DescriptionState CodeState Description
NEWNew phaseTO_BE_REVIEWEDTo be Reviewed State
NEWNew phaseREVIEWEDReviewed State
NEWNew phaseSELECTED_FOR_SCREENINGSelected For Screening
NEWNew phaseREJECTED_EMPLOYERRejected by Employer
NEWNew phaseWITHDRAWN_BY_CANDIDATEWithdrawn by Candidate
SCREENINGScreenPHONE_SCREEN_TO_BE_SCHEDULEDPhone Screen to be Scheduled
SCREENINGScreenPHONE_SCREEN_SCHEDULEDPhone Screen Scheduled
SCREENINGScreenPHONE_SCREEN_COMPLETEDPhone Screen Completed
SCREENINGScreenSELECTED_FOR_INTERVIEWSelected for Interview
SCREENINGScreenREJECTED_EMPLOYERRejected by Employer
SCREENINGScreenWITHDRAWN_BY_CANDIDATEWithdrawn by Candidate
INTERVIEW_AND_SELECTIONInterview and SelectionINTERVIEW_SCHEDULEDInterview Scheduled
INTERVIEW_AND_SELECTIONInterview and SelectionINTERVIEW_COMPLETEDInterview Completed
INTERVIEW_AND_SELECTIONInterview and SelectionFEEDBACK_REQUESTEDFeedback Requested
INTERVIEW_AND_SELECTIONInterview and SelectionFEEDBACK_COMPLETEDFeedback Completed
INTERVIEW_AND_SELECTIONInterview and SelectionSELECTED_FOR_OFFERSelected for Offer
INTERVIEW_AND_SELECTIONInterview and SelectionREJECTED_EMPLOYERRejected by Employer
INTERVIEW_AND_SELECTIONInterview and SelectionWITHDRAWN_BY_CANDIDATEWithdrawn by Candidate
OFFEROfferTO_BE_CREATEDTo be Created
OFFEROfferOFFER_DRAFTDraft
OFFEROfferOFFER_APPROVALPending Approval
OFFEROfferOFFER_APPROVAL_REJECTEDApproval Rejected
OFFEROfferOFFER_APPROVEDApproved
OFFEROfferOFFER_EXTENDEDExtended
OFFEROfferOFFER_ACCEPTEDAccepted
OFFEROfferREJECTED_EMPLOYERRejected by Employer
OFFEROfferWITHDRAWN_BY_CANDIDATEWithdrawn by Candidate
HRHRAUTOPROCESSING_PENDINGPending Automated Processing
HRHRTO_BE_PROCESSEDPending Manual Processing
HRHRPROCESSING_ERRORError During Processing
HRHRPROCESSINGProcessing in Progress
HRHRPROCESSEDProcessed
HRHRREJECTED_EMPLOYERRejected by Employer
HRHRWITHDRAWN_BY_CANDIDATEWithdrawn 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.

If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.