Search for:
Nov 25 2023
HDL – Sample HDL to Load Agents information in ORC

HCM Data Loader supports bulk uploading for Agents for Oracle recruiting cloud. Currently, there is no HDL support for loading Agencies. And idea is already submitted for same:

https://community.oracle.com/customerconnect/discussion/666039/hcm-data-loader-support-for-loading-agencies-in-recruiting-cloud#latest

Coming to Agents bulk upload, you can used below HDL:

METADATA|Agent|EmailAddress|AgencyName|AgentNotes|FirstName|LastName|StatusCode|SourceSystemId|SourceSystemOwner
MERGE|Agent|[email protected]|ExecFinder|67 High Street, London|Agency|Name|Active|ExecFinder.Agent1|HRC_SQLLOADER

METADATA|AgentContext|EmailAddress|Username|SourceSystemId|SourceSystemOwner
MERGE|AgentContext|[email protected]|Agency.Name|ExecFinder.Agent1|HRC_SQLLOADER


METADATA|AgentContextLocation|EmailAddress|Username|LocationName|SourceSystemId|SourceSystemOwner
MERGE|AgentContextLocation|[email protected]|Agency.Name|Germany|ExecFinder.Agent1-Germany|HRC_SQLLOADER
Mandeep Gupta 0 Comments
Jul 31 2022
HDL – Source values for Candidate HDL

When loading Candidate data using HDL, the values of Source attributes (Source and Source Medium) are required. These values are configured in UI using task ‘Manage Candidate Dimension Source Names’.

Use below SQL to get the values:

SELECT DISTINCT
 source_medium_url_value,
source_url_value
  FROM
 fusion.IRC_DIMENSION_DEF_VL
Mandeep Gupta 0 Comments
Nov 29 2021
BIP – Query to extract questions related to Job Requisition
SELECT DISTINCT iirq.REQUISITION_ID
	  ,hqb.QUESTIONNAIRE_ID 
      ,irb.REQUISITION_NUMBER
      ,hqb.QUESTIONNAIRE_CODE
      ,hqb.STATUS
	  ,hct.name		"Category"
	  ,hst.name		"Subscriber"
	  ,hqb.MAX_POSSIBLE_SCORE
	  ,hqnb.question_id
	  ,hqt.question_text
	  ,hqnb.QSTN_VERSION_NUM
	  ,hqnb.QUESTION_CODE
	  ,hqnb.QUESTION_TYPE
  FROM IRC_IM_REQ_QSTNRS iirq
      ,IRC_REQUISITIONS_B irb
      ,HRQ_QUESTIONNAIRES_B hqb
	  ,HRQ_CATEGORIES_TL hct
	  ,HRQ_SUBSCRIBERS_TL hst
	  ,HRQ_QSTNR_SECTIONS_B hqsb
      ,HRQ_QSTNR_QUESTIONS hqq
      ,HRQ_QUESTIONS_B hqnb
      ,HRQ_QUESTIONS_TL hqt
WHERE iirq.REQUISITION_ID = irb.REQUISITION_ID
  AND irb.REQUISITION_ID = 300000237266130
  AND iirq.QUESTIONNAIRE_ID = hqb.QUESTIONNAIRE_ID
  AND hqb.category_id = hct.category_id
  AND hct.language = 'US'
  AND hqb.subscriber_id = hst.subscriber_id
  AND hst.language = 'US'
  AND hqsb.QUESTIONNAIRE_ID     = hqb.QUESTIONNAIRE_ID
  AND hqsb.QSTNR_VERSION_NUM    = hqb.QSTNR_VERSION_NUM
  AND hqsb.BUSINESS_GROUP_ID    = hqb.BUSINESS_GROUP_ID
  AND hqq.QSTNR_SECTION_ID      = hqsb.QSTNR_SECTION_ID
  AND hqq.BUSINESS_GROUP_ID     = hqsb.BUSINESS_GROUP_ID
  AND hqq.QUESTION_ID           = hqnb.QUESTION_ID
  AND hqq.QSTN_VERSION_NUM      = hqnb.QSTN_VERSION_NUM
  AND hqq.BUSINESS_GROUP_ID     = hqnb.BUSINESS_GROUP_ID
  AND hqnb.QUESTION_ID          = hqt.QUESTION_ID 
  AND hqt.language = 'US'
Mandeep Gupta 0 Comments
Feb 19 2021
ORC – Query to get candidate application status
select distinct ic.candidate_number,
ppnfv.full_name,
irv.requisition_number,
ipv.name as phase,
isv.name as state
from irc_requisitions_vl irv,
irc_submissions isub,
irc_phases_vl ipv,
irc_states_vl isv,
per_person_names_f_v ppnfv,
irc_candidates ic
where 1=1
AND irv.REQUISITION_ID (+) = isub.REQUISITION_ID
AND ipv.PHASE_ID (+) = isub.CURRENT_PHASE_ID
AND isv.STATE_ID (+) = isub.CURRENT_STATE_ID
AND isub.PERSON_ID (+) = ic.PERSON_ID
AND ic.PERSON_ID = ppnfv.PERSON_ID
AND ppnfv.NAME_TYPE = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN ppnfv.EFFECTIVE_START_DATE AND ppnfv.EFFECTIVE_END_DATE
ORDER BY ic.candidate_number
Mandeep Gupta 0 Comments