Candidate Action Details (SQL Script)

Human Capital Management (HCM) Human Resources (HRMS)
1

This Candidate Action Details query displays action-related details for job candidates, including names, action codes, statuses, and dates, helping users track candidate progress and workflow status in the recruitment process.

#sqlquery

SELECT 
       --SQL4Fusion (An Orbit Analytics Project)
       --Retrieve the actions performed by the candidate with current effective names.
       IC.CANDIDATE_NUMBER CANDIDATE_NUMBER,
       PPNFV.FULL_NAME CANDIDATE_NAME,
       ICA.ACTION_CODE,
       ICA.ACTION_STATUS_CODE,
       ICA.ACTION_START_DATE,
       ICA.ACTION_END_DATE,
       ICA.ACTION_PAYLOAD,
       ICA.ACTION_SECURE_PAYLOAD
FROM 
       PER_PERSON_NAMES_F_V PPNFV,
	   IRC_CANDIDATES IC,
	   IRC_CAND_ACTIONS ICA
WHERE     
       IC.PERSON_ID = PPNFV.PERSON_ID
	   AND IC.PERSON_ID = ICA.PERSON_ID
       AND TRUNC (SYSDATE) BETWEEN PPNFV.EFFECTIVE_START_DATE
                               AND PPNFV.EFFECTIVE_END_DATE