How to link/connect the User Entities in HCM Extracts
How to link/connect the User Entities in HCM Extracts

 1,009 total views

In this article we will look into how to link the parent and child data groups in HCM Extracts. This article is triggered upon request by user Aurag in earlier post.

Want to learn Fusion Technical tools? Checkout this article

Before we get into more details, if you are new to HCM Extracts, read through the below posts if you have missed to look at them earlier.

User Entities are the basic building blocks of HCM Extracts. It is important to pick up the right User Entity for the Extract in order to get the output correctly.

Now we will look at some of the options at User Entity level which will help us determine if the UE can be used as Root Data Group and what are the contexts set it and what are the contexts required by that UE.

Navigate to Data Exchage > View User Entity Details

Let’s check our favorite UE – PER_EXT_PAY_EMPLOYEES_UE

Step 1 – User Entity Details Tab:

User Entity details for hcm extracts tips and tricks fusion hcm oracle hcm cloud
User Entity details

Provide the UE name and click on search. In the search results, select the right UE name and the details will be visible on the right hand side.

We will see the importance of the right hand side fields

  • Multiple Rows: Yes – This means that this UE returns multiple rows.
  • Historic: No – This UE doesn’t fetch the history data.
  • Context Required: No – This UE doesn’t require any input parameters to be passed to fetch data and hence can be used as Root Data Group.
  • Valid for Root Data Group: Yes – This UE is eligible to be Root Data Group.

Below there are two tabs “Database Items” and “Database Item Groups“.
Database Items – These are the attributes fetched by this UE. Ex: Job ID, Organization ID, Location ID, etc.
Database Item Groups – These are the attributes fetched by the UEs which are child to this UE and are connected by the Contexts Set by this UE. Ex: Job Name, Organization Name, Location attributes, etc.

Step 2 – Contexts Tab:

User Entity Contexts required and set for hcm extracts in fusion hcm learning extracts
User Entity Contexts
  • Here we can see the first section “Contexts Set” – this section lists all the contexts that are set by this User Entity. In a simple way we can say that whatever the fields selected by this UE these can be used as inputs for other child UEs.
  • Next section is “Contexts Required” – this section lists all the contexts that are required by this UE. Here we can see that only Effective Date is required and it will get passed through the user input when we run the HCM Extract.
  • Next section is “Context Setting User Entities” – this section lists all the UEs which set the contexts for this UE. In this case the list of UEs are setting the context of Effective Date and hence are listed here.

Step 3 – Query Tab:

how to get the sql query for user entity fusion hcm extracts oracle hcm cloud
SQL Query for UE

Here we can see the complete SQL Query behind the User Entity. And the fields that are being selected are called Data Base Items. There is a button “Calculate Rows” which when clicked will show us the number of rows that this UE returns. This will work only for the UEs which don’t have any contexts required.

Note: Query tab is very important when you are debugging the extract to see why the employees are not picking up or why some additional employees are getting picked up. You will have to copy the additional filter criteria defined at data group level and add it to the UE query and run it in data model to diagnose the issue with employees.

Step 4 – Extracts using User Entity Tab:

user entity details on data exchange for fusion hcm extracts oracle hcm cloud

Currently we can see that none of the extracts are using this UE in this POD.

Now let’s check another UE which cannot be set as Root Data Group to understand it better.

We can do a wildcard search too for DBI Names or UE Name on the search screen. let’s search for the Email Address UE.

Email Address user entity in fusion hcm for hcm extract child user entity data group
Email Address UE

We can see that this UE has “Contexts Required” as YES, and hence it cannot be used as Root Data Group.

Let’s move to contexts tab to see what are the contexts required for this UE.

Contexts required for user entities in fusion hcm for hcm extracts oracle hcm cloud
Contexts required for this UE

We can see that PERSON_ID is the context that is required for this UE. Which means that if we provide the input Person_ID, this UE will provide the Email Addresses of that specific person.

In the Context Setting User Entities we can see that PER_EXT_PAY_EMPLOYEES_UE is listed (There are many, you can go through the list) , so we can use the Root as PER_EXT_PAY_EMPLOYEES_UE and child as PER_EXT_EMAIL_ADDRESSES_UE and link the Person ID DBI from both the User Entities. Refer to the Database Items on the “User Entity Details” tab to get the correct DBI name for the Person ID and link them.

In this way we can search for DBI/UE using the “View User Entity Details” page and check which UE can be used as Root and which one can be used as child entity.

Hope this clears your questions on why the assignment/person related UEs are used as Root Data Groups every time when you have a requirement for a demographic interface.

Summary – In order to link the child data group to parent data group, the context required by the child UE should have been set by the Parent UE, else the parent child relationship won’t work.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 3 Comments

  1. Anurag

    Thank you very much for this article Sricharan!!! It will help a lot-:)

  2. Sricharan

    Thank you for the compliment. Keep following for latest updates.

  3. Akul

    hi Sricharan. your article is so useful. how do you know which DBIs to use in a fast formula. example if I need to get a termination date , there are so many DBIs .how do I know which one to use

Leave a Reply