This Candidate Submissions query displays submission details, including candidate name, status, and questionnaire score, for each candidate with confirmed submissions as of today, helping users monitor recruitment activity and submission statuses.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Retrieve candidate submission details with status and approver info.
IC.CANDIDATE_NUMBER ,
PPNFV.FULL_NAME CANDIDATE_NAME,
PPFNVAN.FULL_NAME CONFIRMED_BY_PERSON_NAME,
ISUB.SUBMISSION_CONFIRMED_DATE,
ISUB.INTERNAL_FLAG,
ISUB.DISQUALIFIED_FLAG,
ISUB.MERGED_FLAG, --To track if the submission has been delinked/merged.
ISUB.QSTNR_SCORE QUESTIONNAIRE_SCORE, --The total questionnaire score for this submission.
ISUB.OBJECT_STATUS,
ISUB.SYSTEM_PERSON_TYPE,
ISUB.IS_COMPLETED_FLAG,
ISUB.IS_RESTRICTED_FLAG,
ISUB.ACTIVE_FLAG,
ISUB.SUBMISSION_DATE,
ISUB.SUBM_LAST_MODIFIED_DATE SUBMISSION_LAST_MODIFIED,
ISUB.SUBMISSION_LANGUAGE_CODE SUBMISSION_LANGUAGE,
ISUB.CATEGORY_CODE SUBMISSION_CATEGORY
FROM
PER_PERSON_NAMES_F_V PPNFV,
IRC_CANDIDATES IC,
IRC_SUBMISSIONS ISUB,
PER_PERSON_NAMES_F_V PPFNVAN --This table is used to fetch the approver name.
WHERE
IC.PERSON_ID = PPNFV.PERSON_ID
AND TRUNC (SYSDATE) BETWEEN PPNFV.EFFECTIVE_START_DATE
AND PPNFV.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PPFNVAN.EFFECTIVE_START_DATE
AND PPFNVAN.EFFECTIVE_END_DATE
AND IC.PERSON_ID = ISUB.PERSON_ID
AND ISUB.CONFIRMED_BY_PERSON_ID = PPFNVAN.PERSON_ID