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
Oct 16 2023
HDL – DELETE future dated position record

There are scenarios when we want to delete a future dated row from an object. Please note that for objects like Positions/Locations etc which are date tracked, one can make use of HDL with SET command to delete the future dated row.

Let us take an example, where we have below data on Position:

Position Name – Test Position

Effective Start Date – 01- Jan-2023 – Record creation

Effective Start Date – 01-Oct-2023 – Record updated (let us say Standard working hours).

Now the requirement is to delete the row with effective start date – 01-Oct-2023.

In such cases, below HDL can be used:

SET PURGE_FUTURE_CHANGES Y

METADATA|Position|BusinessUnitName|PositionCode|EffectiveStartDate|EffectiveEndDate

MERGE|Position|BU Name|Pos Code|2023/01/01|4712/12/31

Mandeep Gupta 0 Comments
Oct 16 2023
Configuration – Employee Location on Location Details page

Starting 22A, Oracle introduced a new field called “Employee Location”.

https://www.oracle.com/webfolder/technetwork/tutorials/tutorial/cloud/r13/wn/ghr/releases/22A/22A-hr-wn.htm#F21144

Any locations for which the value of the field will be selected as “Yes”, will be only available to be used in Manage Employment screen. This is a great feature to segregate locations which can be used on employee assignments vs locations which are used as training centers/ supplier locations etc.

By default, this field is hidden on Location Details quick action.

One needs to enable a sandbox and then from transaction design studio, make it visible.

From Quick actions under My Client Groups, choose Location Details:

image.png

If you still don’t see the attribute, create a new sandbox, add Experience Design Tool in it:

Choose Location Details as Action:

image.png

Click on Add,

under Page attributes, you see Employee Location is hidden OOTB:

image.png

P.S. – Please note this field can’t be used in classic locations page. Also, the default value is set to No.

Mandeep Gupta 0 Comments
Oct 16 2023
Approvals – Using functions in BPM approvals

There are cases where we need to use functions like days between to get difference between two dates. These functions can not be used from Transaction console. One has to login to BPM Worklist to access the functions.

Follow the below steps:

Open the worklist app:

https://xxxx-saasfaprod1.fa.ocs.oraclecloud.com/integration/worklistapp/faces/home.jspx

Click on your user name -> Administration and then Task Configurations:

Search for Task:

image.png

Click on Edit:

Then under Assignees -> Click on Diamond Icon:

image.png

Click on – Go To Rule:

Click on create rule -> on Condition, choose fx:

image.png

Then from functions tab, choose the required function:

image.png
image.png

List of all available functions:

https://docs.oracle.com/en/middleware/fusion-middleware/bpm/12.2.1.3/rules-user/oracle-business-rules-built-classes-and-functions.html#GUID-E4EB667A-45C2-46BD-81C1-0FAD12C79851

Mandeep Gupta 0 Comments
Sep 14 2023
BIP – Approval Task Name in Transaction Console vs Task name in BPM

In HCM, you can define approval rules for different HCM tasks from Tools -> Transaction Console -> Approval Rules

For example, you want to define an approval rule for Promote action. You can search Promo% in Find box and it will give you all matching approval rules:

You can then click on ‘Configure Rules’ to define the rules.

However, Transaction console has many limitations. You can’t define complex approval rules. You can’t make use of functions etc (Day between) in transaction console. In order to define more complex rules, you need to do it from BPM.

First step is that you need to search for a task from “Task Configuration”:

The issue here is sometimes the Approval Rule names in Transaction Console doesn’t match with Task names in BPM.

In such case, you can make sure of below Query:

SELECT HAPT.NAME "Approval Rule Name"
      ,HAPT.DESCRIPTION
      ,HRPB.TASK_FILE_NAME TaskName
      ,HRPB.TXN_MODULE_IDENTIFIER
      ,HAPT.CATEGORY_NAME
      ,HRPB.CATEGORY_CODE
      ,HAPT.SUBCATEGORY_NAME
      ,HRPB.SUBCATEGORY_CODE
      ,HRPB.FAMILY
      ,DECODE(HAAO.APPROVAL_DISABLED,'true','Bypassed','false','Enabled') "Enabled Status"
 FROM FUSION.HRC_ARM_PROCESS_B HRPB
     ,FUSION.HRC_ARM_PROCESS_TL HAPT
     ,FUSION.HRC_ARM_APPROVAL_OPTIONS HAAO
WHERE HRPB.PROCESS_ID=HAAO.PROCESS_ID
  AND HRPB.PROCESS_ID=HAPT.PROCESS_ID
  AND HAPT.LANGUAGE = 'US'
  AND HRPB.FAMILY = 'HCM'
 ORDER BY 1

Result:

Mandeep Gupta 0 Comments
Sep 3 2023
BIP – Query to find size of documents attached in DOR

Oracle HCM provides a functionality to store different kinds of worker documents in Document Records. Over a period of time, a large number of documents get accumulated for different workers. In such cases, there are requirements to know size of attachments in DORs. For, this I have developed a simple query which can be modified as needed:

select fdv.file_name
      ,fdv.title
      ,dm_document_id
      ,TRUNC(SUM(wcc_documents.dFileSize)/1024,2) 			as "Size in KB"
      ,TRUNC(SUM(wcc_documents.dFileSize)/1024/1024,2) 		as "Size in MB"
      ,TRUNC(SUM(wcc_documents.dFileSize)/1024/1024/1024,2) as "Size in GB"
 from hr_documents_of_record hdr, 
      fnd_attached_documents fad, 
      fnd_documents_vl fdv,
      fusion_ocserver11g.revisions wcc_revisions,  
      fusion_ocserver11g.documents wcc_documents
where to_char(hdr.documents_of_record_id) = fad.pk1_value(+)
  and fad.entity_name(+) = 'HR_DOCUMENTS_OF_RECORD'
  and fad.document_id = fdv.document_id(+)
  and fdv.dm_version_number = wcc_revisions.did
  and wcc_revisions.did = wcc_documents.did
  and wcc_documents.disprimary = 1
group by fdv.file_name
        ,fdv.title
	,dm_document_id	
order by 1
Mandeep Gupta 0 Comments
Sep 3 2023
BIP – Query to find workers with FTE < 1

Below SQL query can be used to find part-timers in HCM. Any worker where FTE is less than 1 is normally considered as part-timer:

select paam.assignment_number, pawmf.value
  from per_legal_employers ple
      ,per_periods_of_service ppos
      ,per_all_assignments_m paam
      ,PER_ASSIGN_WORK_MEASURES_F pawmf
 where ppos.legal_entity_id = ple.organization_id
   and paam.period_of_Service_id = ppos.period_of_Service_id
   and TRUNC(SYSDATE) between paam.effective_start_date AND paam.effective_end_date
   and TRUNC(SYSDATE) between pawmf.effective_start_date AND pawmf.effective_end_date
   and paam.assignment_status_type like 'ACTIVE%'
   and ple.name like 'XX%United%Stat%'
   and paam.assignment_id = pawmf.assignment_id
   and pawmf.unit = 'FTE'
   and  pawmf.value <1
   and paam.assignment_type ='E'
   and ppos.actual_termination_date is null
 order by 1
Mandeep Gupta 0 Comments
Sep 3 2023
BIP – Extract Absence Entries in HDL Format

Recently, I faced a scenario for a customer where after go-live, where there was an issue found with absence plan configuration. The absence plan was incorrectly set up. The Balance Frequency Source wasn’t setup to “Repeating Period”, so the accrual was calculated incorrectly. In order to fix this, a new absence plan was created and the absence entries were made against the new plan.

So, the approach taken was to take a backup of all absence entries from PROD, enroll the employees into new plan and reupload the absence entries.

A BIP report was developed to take a backup of existing absence entries in HDL format. Below is the query for same:

SELECT DATA_ROW
FROM (
SELECT 'METADATA'
|| CHR (124) || 'PersonAbsenceEntry'
|| CHR (124) || 'PerAbsenceEntryId'
|| CHR (124) || 'AbsenceType'
|| CHR (124) || 'AbsenceTypeId'
|| CHR (124) || 'AbsenceStatus'
|| CHR (124) || 'ApprovalStatus'
|| CHR (124) || 'AssignmentNumber'
|| CHR (124) || 'AssignmentId'
|| CHR (124) || 'EmployerId'
|| CHR (124) || 'PersonNumber'
|| CHR (124) || 'PersonId'
|| CHR (124) || 'StartDate'
|| CHR (124) || 'StartTime'
|| CHR (124) || 'StartDateDuration'
|| CHR (124) || 'EndDate'
|| CHR (124) || 'EndTime'
|| CHR (124) || 'EndDateDuration'
|| CHR (124) || 'AbsenceReasonId'
|| CHR (124) || 'SubmittedDate'
|| CHR (124) || 'PlannedEndDate'
|| CHR (124) || 'NotificationDate'
|| CHR (124) || 'ConfirmedDate'
|| CHR (124) || 'SourceSystemId' 
|| CHR (124) || 'SourceSystemOwner'  AS DATA_ROW
FROM DUAL
UNION all
select  'MERGE'
|| CHR (124) || 'PersonAbsenceEntry'
|| CHR (124) || apae.per_absence_entry_id
|| CHR (124) || aatft.name
|| CHR (124) || apae.absence_type_id
|| CHR (124) || apae.absence_status_cd
|| CHR (124) || apae.approval_status_cd
|| CHR (124) || paam.assignment_number
|| CHR (124) || paam.assignment_id
|| CHR (124) || apae.legal_entity_id
|| CHR (124) || papf.person_number
|| CHR (124) || apae.person_id
|| CHR (124) || to_char(apae.start_date,'RRRR/MM/DD')
|| CHR (124) || apae.start_time
|| CHR (124) || apae.start_date_duration
|| CHR (124) || to_char(apae.end_date,'RRRR/MM/DD')
|| CHR (124) || apae.end_time
|| CHR (124) || apae.end_date_duration
|| CHR (124) || apae.absence_type_reason_id
|| CHR (124) || to_char(apae.submitted_date,'RRRR/MM/DD')
|| CHR (124) || to_char(apae.planned_end_date,'RRRR/MM/DD')
|| CHR (124) || to_char(apae.notification_date,'RRRR/MM/DD')
|| CHR (124) || to_char(apae.confirmed_date,'RRRR/MM/DD')
|| CHR (124) || hikm.source_system_id
|| CHR (124) || hikm.source_system_owner
FROM ANC_PER_ABS_ENTRIES apae,
     ANC_ABSENCE_TYPES_F_TL aatft,
     PER_ALL_ASSIGNMENTS_M paam,
     PER_ALL_PEOPLE_F papf,
     HRC_INTEGRATION_KEY_MAP hikm
WHERE hikm.surrogate_id = apae.per_absence_entry_id
  AND apae.absence_type_id = aatft.absence_type_id
  AND apae.assignment_id = paam.assignment_id
  AND apae.person_id = papf.person_id
  AND paam.person_id = papf.person_id
  AND aatft.language = 'US'
  AND aatft.name LIKE 'Annual%'
  AND TRUNC(SYSDATE) BETWEEN aatft.effective_start_date AND aatft.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
  AND apae.start_date BETWEEN paam.effective_start_date AND paam.effective_end_date
 )

Once the data is extracted, you need to make sure that Source System Owner is updated from FUSION to HRC_SQLLOADER. Source System Owner is set to FUSION when an entry is created from UI.

Once the output of the BIP is ready, Change “MERGE” to “DELETE” to delete all the absence entries. Then enroll the workers in new plan, do the required changes in BIP extract and upload the data back in Fusion HCM.

Mandeep Gupta 0 Comments
Aug 16 2023
HDL – Sample file to close benefit certifications

Starting 23C, a new HDL object has been added to close any pending actions for benefits. The certification type is validated against a lookup called – BEN_ENRT_CTFN_TYP

Below is a sample file to close any pending certification actions against “POC”:

METADATA|CloseEnrollmentCertification|PersonNumber|ParticipantFirstName|ParticipantLastName|CertificationType|ProgramName|PlanName|OptionName|ReceivedDate|DeniedDate|SourceSystemId|SourceSystemOwner
MERGE|CloseEnrollmentCertification|12|First Name 1|Last Name 1|POC|Test Benefits Program|Test Optical|Employee + 1|2023/02/01||12_First Name 1_Last Name 1_POC|HRC_SQLLOADER
MERGE|CloseEnrollmentCertification|13|First Name 2|Last Name 2|POC|Test Benefits Program|Test Medical|Employee + 1||2023/02/01|12_First Name 2_Last Name 2_POC|HRC_SQLLOADER
Mandeep Gupta 0 Comments
Aug 16 2023
HDL – Sample file to load Person Habits

Attributes like student status, tobacco consumption which impact worker benefits can be loaded using PersonHabits.dat.

Sample File:

METADATA|PersonHabits|SourceSystemOwner|SourceSystemId|LegalEmployer|PersonNumber|EffectiveStartDate|EffectiveEndDate|StudentStatus|CvrdInAnthrPl
MERGE|PersonHabits|HRC_SQLLOADER|PERSON_HABIT_12|Test LE|12|2021/12/03|4712/12/31|PART_TIME|
MERGE|PersonHabits|HRC_SQLLOADER|PERSON_HABIT_13|Test LE|13|2021/09/24|4712/12/31|FULL_TIME|

Mandeep Gupta 0 Comments
Aug 8 2023
Configuration – Conditional Name Formatting

There are requirements where customers want to display Honors in “Display Name” if First Name is blank. This where the conditional name formatting functionality of Oracle HCM helps to achieve desired results.

Let us assume, we want display name in below format:

NVL(First Name, Honors), Name Information 1

Follow below steps to achieve this requirement:

  1. Search for Task “Manage Person Name Formats” from setup and maintenance:

2. Search the name format you want to modify/ or create a new one if you want to have a new name format:

3. Click on edit and Choose Conditional Name Formatting component:

4. Scroll down and supply the values in “Preview Name Format” section:

5. As can be seen, first name is blank so Formatted Name is displayed as – Honors Name_Information_1

6. If you supply a value for first name:

Formatted Name is displayed as – Test Name_Information_1 (Honors is ignored as First Name is present).

This is a new feature introduced in 21C.

https://www.oracle.com/webfolder/technetwork/tutorials/tutorial/cloud/r13/wn/ghr/releases/21C/21C-global-hr-wn.htm#F19019

Please make sure to run “Apply Name Formats to Person Names, Keywords and LDAP” job set from scheduled processes under Tools.

Mandeep Gupta 0 Comments
Aug 6 2023
Configuration – Load Announcements file in content server

When creating a new announcement (Tools -> Announcements) , user has an option to upload images from local directory or a link can be given to a file uploaded in content server.

From local directory:

For content server link:

One needs to follow below steps in order to upload the image file to content server:

  1. Login into content server. Suffix your pod url with /cs:

https://pod.fa.oracle.com/cs

2. Do a new check-in:

3. Click on “Check In”

4. Once the file is successfully checked-in. Click on “New Folio” Under “Content Management”

5. Choose Simple Folio and Click on “Load Folio”:

6.

7. Under “Folio Elements”, click on Add:

Search for Content ID from step 3:

8. Click Next and Folio should be created:

9. Right Click on image and copy Image address.

Use that address in Annoucments.

Mandeep Gupta 0 Comments
Aug 6 2023
BIP – Queries to find count of workers in HCM

Below sample queries can be used to extract count of workers (Active/ Inactive) in HCM:

Query 1:

select count(distinct person_number) 
  from per_all_people_f papf
  where 1=1
    and exists (select 1 from per_periods_of_service ppos where ppos.person_id = papf.person_id)

Query 2: Find count workers not having basic assignment details:

select (distinct person_number) 
  from per_all_people_f papf
  where 1=1
    and exists (select 1 from per_periods_of_service ppos where ppos.person_id = papf.person_id)
    and person_number not in (SELECT papf.person_number 
                                FROM per_all_people_f papf,
                                     per_person_names_f ppnf,
                                     per_all_assignments_m paam,
                                     per_jobs_f_tl pjft,
                                     hr_organization_units_f_tl hauft,
  	                                 per_periods_of_service ppos
                               WHERE paam.person_id = ppnf.person_id
                                 AND papf.person_id = ppnf.person_id
                                 AND papf.person_id = paam.person_id
                                 AND paam.job_id = pjft.job_id (+)
                                AND pjft.language(+) = 'US'
                                 AND hauft.language (+) = 'US'
-                              -and papf.person_number = '414'
                                 AND hauft.organization_id (+) = paam.organization_id
                                 AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
                                 AND trunc(sysdate) BETWEEN paam.effective_start_date AND paam.effective_end_date
                                 AND trunc(sysdate) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
                                 AND trunc(sysdate) BETWEEN hauft.effective_start_date(+) AND hauft.effective_end_date(+)
                                 AND trunc(sysdate) BETWEEN pjft.effective_start_date(+) AND pjft.effective_end_date(+)
                                 AND paam.effective_latest_change = 'Y'
                                 AND paam.primary_flag = 'Y'
                                 AND ppnf.name_type = 'GLOBAL'
                                 AND paam.period_of_service_id = ppos.period_of_service_id
                                 AND ppos.date_start = (select max(ppos2.date_start) from per_periods_of_service ppos2
                                                         where ppos2.person_id = ppos.person_id
                                                             and ppos2.primary_flag = 'Y')
                             )
Mandeep Gupta 0 Comments
Aug 6 2023
BIP – Query to find list of same person having same position multiple times

On the responsive Position UI, the position screens shows the Pending Worker and Worker record as an incumbent and the FTE goes in negative.

Below SQL can be used to identify such persons:

SELECT paam.person_id, hapfv.name
  FROM per_all_assignments_m paam
      ,hr_all_positions_f_vl hapfv
 WHERE paam.position_id = hapfv.position_id
   AND paam.assignment_type NOT LIKE '%T'
   AND paam.assignment_status_type NOT LIKE 'INACTIVE'
   AND TRUNC(SYSDATE) BETWEEN hapfv.effective_start_date AND hapfv.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
  GROUP BY paam.person_id, hapfv.name
 HAVING COUNT(*) > 1
Mandeep Gupta 0 Comments