This candidate details query retrieves detailed candidate information including contact, job, assignment, and organizational data by joining multiple HR tables based on person identifiers and associated dates.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves candidate details with job, organization, contact, and assignment-related information.
HAOU.NAME ORGANIZATION,
IC.CANDIDATE_NUMBER,
CASE
WHEN PPNFV.TITLE IS NOT NULL THEN PPNFV.TITLE || PPNFV.FULL_NAME
ELSE PPNFV.FULL_NAME
END CANDIDATE_NAME,
PP.DATE_OF_BIRTH,
PP.BLOOD_TYPE,
PAF.COUNTRY,
PAF.REGION_1 || ',' || PAF.REGION_2 || ',' || PAF.REGION_3 REGION,
PAF.ADDRESS_LINE_1 CANDIDATE_ADDRESS,
PAF.POSTAL_CODE,
PPH.COUNTRY_CODE_NUMBER,
PPH.AREA_CODE,
PPH.PHONE_NUMBER,
PEA.EMAIL_ADDRESS,
PJ.EFFECTIVE_START_DATE JOB_START_DATE,
PJ.EFFECTIVE_END_DATE JOB_END_DATE,
PJ.NAME JOB_NAME,
PJ.MANAGER_LEVEL,
PJ.JOB_FUNCTION_CODE,
PJ.FULL_PART_TIME EMPLOYMENT_TYPE,
PAAF.ASSIGNMENT_NUMBER,
PAAF.ASSIGNMENT_NAME,
PAAF.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE,
PAAF.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE,
PAAF.ASSIGNMENT_SEQUENCE,
PAAF.ASSIGNMENT_TYPE
FROM
IRC_CANDIDATES IC,
PER_PERSONS PP,
PER_PHONES PPH,
PER_EMAIL_ADDRESSES PEA,
PER_ADDRESSES_F PAF,
PER_ALL_ASSIGNMENTS_M PAAF,
PER_PERSON_NAMES_F_V PPNFV,
PER_JOBS PJ,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE 1=1
AND IC.PERSON_ID = PP.PERSON_ID
AND IC.CAND_PHONE_ID = PPH.PHONE_ID
AND IC.CAND_EMAIL_ID = PEA.EMAIL_ADDRESS_ID
AND IC.CAND_ADDRESS_ID = PAF.ADDRESS_ID
AND IC.PERSON_ID = PPNFV.PERSON_ID
AND IC.PERSON_ID = PAAF.PERSON_ID
AND PAAF.JOB_ID = PJ.JOB_ID
AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND SYSDATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PJ.EFFECTIVE_START_DATE AND PJ.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
ORDER BY IC.CANDIDATE_NUMBER