You are currently viewing SQL Query to fetch ORC Candidate Job Application Status
SQL Query to fetch ORC Candidate Job Application Status
- SQL Query to fetch ORC Candidate Job Application Status
Total Visits: 11

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.