In this article, we will look into how to get the candidate resume information on BI Report.
As part of data loads for Oracle Recruiting Cloud, we load the Candidate Resumes as part of the candidate information Candidate.dat
We can load the Candidate’s Resume, Cover Letter, etc. This information will be stored in the FND_ATTACHED_DOCUMENTS table.
Table of Contents
Candidate Attachments Information
The FND_ATTACHED_DOCUMENTS table can be linked to the IRC_CANDIDATES table for candidate attachments, or the IRC_SUBMISSIONS table for job submission attachments by using the PK_VALUE column:
- PK_VALUE = PERSON_ID for Candidates
- PK_VALUE = SUBMISSION_ID for Job Submissions
IRC_CANDIDATES is the core table for Candidates like what PER_ALL_PEOPLE_F is for employees.
Candidate Attachments:
SELECT *
FROM
FND_ATTACHED_DOCUMENTS
WHERE
ENTITY_NAME LIKE 'IRC_CANDIDATES'
AND PK1_VALUE IN (SELECT TO_CHAR(PERSON_ID)FROM FUSION.IRC_CANDIDATES)
Candidate Job Application (Job Submission) Attachments:
SELECT *
FROM
FND_ATTACHED_DOCUMENTS
WHERE
ENTITY_NAME LIKE 'IRC_SUBMISSIONS'
AND PK1_VALUE IN(SELECT TO_CHAR(SUBMISSION_ID)FROM FUSION.IRC_SUBMISSIONS)
Resumes not visible on Candidate Job Application:
In order for resume attachments to be visible on the individual job applications for the candidate:
- the attachments need to be loaded directly to the job application (CandidateJobApplication.dat)
OR
- the job applications need to be created after the attachment is loaded to the candidate profile
If the resumes are not loaded in the initial load during the creation of candidates and their job applications, the resumes won’t be visible on the candidate job applications even if the resumes are loaded to candidates. We have to explicitly load the resumes to the candidate job applications as well in this case.
Supported filetypes for Candidate Attachments:
The following file types (listed by file extension) are supported for candidate attachments:
- .txt
- .rtf
- .doc
- .docx
- .odt
- .html
- .htm
Loading Attachments for Candidates
We can load the candidate Resume and Cover letter using the attachments object on Candidate.dat file. We need to place the actual resume/cover letter in BlobFiles folder and list those file names in the Candidate.dat file in the below format
METADATA|Attachment|CandidateNumber|Title|File|DataTypeCode|URLorFileName|Category
MERGE|Attachment|C123|C123_Resume|123_Testing_Resume.docx|FILE|123_Testing_Resume.docx|IRC_CANDIDATE_RESUME
MERGE|Attachment|C123|C123_Cover|123_Testing_Cover.docx|FILE|123_Testing_Cover.docx|IRC_CANDIDATE_COVERLETTER
Now zip the Candidate.dat and the BlobFiles folder and load it using HCM Data Loader.
Query to fetch Candidate Resumes along with filename:
SELECT distinct
FndDocumentsEO.FILE_NAME AS FILE_NAME,
FndAttachedDocumentsEO.PK1_VALUE as person_id
FROM
FND_ATTACHED_DOCUMENTS FndAttachedDocumentsEO,
FND_DOCUMENTS_VL FndDocumentsEO,
FND_DOCUMENT_ENTITIES_VL FndDocumentEntitiesEO,
FND_DOCUMENT_CATEGORIES_VL FndDocumentCategoriesEO
where
FndAttachedDocumentsEO.document_id = FndDocumentsEO.document_id
and FndDocumentEntitiesEO.entity_name = FndAttachedDocumentsEO.entity_name
and FndAttachedDocumentsEO.category_name = FndDocumentCategoriesEO.category_name
and (
FndAttachedDocumentsEO.expiration_date IS NULL
or FndAttachedDocumentsEO.expiration_date > SYSDATE
)
and FndAttachedDocumentsEO.entity_name = 'IRC_CANDIDATES'
and FndDocumentCategoriesEO.CATEGORY_NAME = 'IRC_CANDIDATE_RESUME'
If the cover letters are also loaded, then we need to use the CATEGORY_NAME of IRC_CANDIDATE_COVERLETTER to fetch that information.
The above query can be joined with IRC_CANDIDATES to get the resume information for candidates.
If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.