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.
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.
- Basics of HCM Extracts in Fusion HCM
- Resolve A system error occurred during processing in Extracts
- Configure GMFZT logging for an HCM Extract
- New features in HCM Extracts in 19B release
- Importing Changes Only HCM Extract post 20A update
- Scheduling and Cancelling an existing schedule of HCM Extract
- Skip Output file of HCM Extract when no data is fetched
- Basics of Changes Only Functionality in HCM Extract
- Using Table based Valueset when DBI is unavailable
- Most frequently used User Entities in HCM Extracts
- How to view the output of HCM Extract run by other users?
- How to link/connect the User Entities in HCM Extracts
- Fix for assertion failure error in HCM Extracts
- Top 50 Interview questions and answers on HCM Extracts
- HCM Extracts Dynamic Output Filename guide
- SQL Query to find scheduled HCM Extracts
- Achieve complex requirements using Custom Global Reports Data Model
- Payroll Flow and HCM Extracts Mapping for Payroll Module
- Hiding XML nodes in HCM Extract Output
- Running the latest extract version using Payroll Flow
- How to convert HCM Extract Export XML to Readable format?
- List of User Entities, DBIs, Routes and Contexts from R13 19D
- Deleted Data Report using Audit Functionality
- Passing Logged in User details to HCM Extract
- How to handle FF not compiled error for HCM Extracts
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
Table of Contents
Step 1 – User Entity Details Tab:
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:
- 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:
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:
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.
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.
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.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM
Thank you very much for this article Sricharan!!! It will help a lot-:)
Thank you for the compliment. Keep following for latest updates.
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