• Post category:Recruiting
  • Post comments:0 Comments
  • Post last modified:July 19, 2021
  • Reading time:6 mins read
You are currently viewing How to get Candidate Resume Information in ORC?
How to get Candidate Resume Information in ORC?

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
  • .pdf
  • .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 LinkedInFacebook, and Twitter to get updated with the latest content.