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
Nov 5 2023
BIP – Extract action type and action information from Work relationship
SELECT papf.person_number
      ,ppnf.full_name
      ,pao.action_type_code
      ,pav.action_name
      ,part.action_reason termination_reason 
  FROM per_all_people_f papf
      ,per_person_names_f ppnf
      ,per_periods_of_service ppos
      ,per_actions_vl pav
      ,per_action_reasons_tl part
      ,per_action_occurrences pao
 WHERE papf.person_id =ppnf.person_id 
   AND papf.person_id =ppos.person_id  
   AND UPPER(ppnf.name_type)='GLOBAL' 
  --AND TO_CHAR(ppos.actual_termination_date,'dd-mm-yyyy')<TO_CHAR(SYSDATE,'dd-mm-yyyy') 
   AND ppos.action_occurrence_id = pao.action_occurrence_id 
   AND pao.action_reason_id = part.action_reason_id (+)
   AND part.language (+) = USERENV('LANG')
   AND TRUNC(SYSDATE) BETWEEN TRUNC(papf.effective_start_date) AND TRUNC(papf.effective_end_date)
   AND TRUNC(SYSDATE) BETWEEN TRUNC(ppnf.effective_start_date) AND TRUNC(ppnf.effective_end_date)
   AND papf.person_number = '123351'
   AND pao.action_id = pav.action_id
order by papf.person_number
Mandeep Gupta 0 Comments
Nov 5 2023
Configuration – Using UNION in a value set

For requirements to add List of values to a DFF segments, one can create independent value set or a table based value set. For example, if you want LOV as your existing departments, then you can use a table based value set.

But sometimes, you need to display one hard coded value “All” along with list of departments. This is in order to specify that all departments are applicable for a DFF segement value.

In, this case you need to use UNION.

Below is how the query will look like:

SELECT substr(pd.name,1,125) dep_name
  FROM per_departments pd
UNION
SELECT 'All Departments' dep_name
  FROM DUAL

Value Set setup:

From Clause:

(SELECT substr(pd.name,1,125) dep_name  FROM per_departments pd UNION SELECT ‘All Departments’ dep_name  FROM DUAL) a

Value Column Name:

a.dep_name

ID Column Name:

a.dep_name

Mandeep Gupta 0 Comments
Nov 5 2023
BIP – Query to find attachment details for related materials

Learning admin can attachments on offering level for different roles – Admin/ Employee etc.

In case, you need to extract the attachment details for an offering, below BIP can be used:

SELECT wlf_learning_items_f.learning_item_id
      ,wlf_learning_items_f.learning_item_number    
	  ,wlf_li_relations_f.relation_id
	  ,fad.pk1_value 
	  ,fad.attached_document_id
	  ,fdt.document_id 
	  ,fdt.title 
	  ,fdt.file_name 
	  ,fad.creation_date
  FROM fnd_attached_documents fad 
      ,fnd_documents_tl fdt 
	  ,wlf_learning_items_f wlf_learning_items_f
	  ,wlf_li_relations_f wlf_li_relations_f
 WHERE fad.document_id = fdt.document_id 
   AND fdt.language = 'US'
   AND fad.entity_name = 'WLF_LI_RELATIONS_F'
   AND fad.pk1_value = wlf_li_relations_f.relation_id
   AND wlf_learning_items_f.learning_item_id = wlf_li_relations_f.source_id
   AND TRUNC(SYSDATE) BETWEEN wlf_learning_items_f.effective_start_date AND wlf_learning_items_f.effective_end_date 
   AND TRUNC(SYSDATE) BETWEEN wlf_li_relations_f.effective_start_date AND wlf_li_relations_f.effective_end_date 
 ORDER BY fad.creation_date DESC

Output:

Mandeep Gupta 0 Comments