Using Table based Valueset when DBI is unavailable
Using Table based Valueset when DBI is unavailable

 1,967 total views

When we develop integrations using HCM Extracts, we normally come across many scenarios where in we are unable to get all the attributes required using the Database Items (DBIs) for the integration using a single/multiple User Entities (UEs).

Want to learn Fusion Technical tools? Checkout this article

If you are new to HCM Extracts, I would suggest you to read through the below articles on HCM Extracts

In this article we will look into an alternative approach when DBI itself is not available for a field or we might need to add multiple UEs to get the attribute.

Consider we have a requirement to pull employee future assignment status. However we need to pull the latest assignment record from the UE PER_EXT_PAY_EMPLOYEES_UE for getting details for all other fields like Job Name, Position Name, Organization Name and Location Name.

In order to achieve this requirement, we can use the table based Valueset and Fast Formula approach.

SQL Query for Table based Valueset:

SELECT paa.assignment_status_type
FROM per_all_assignments_m paa
WHERE paa.person_id = :{PARAMETER.P_PERSON_ID}
AND paa.primary_flag = 'Y'
AND effective_start_date >= (
SELECT max(paam.effective_start_date)
FROM per_all_assignments_m paam
WHERE person_id = :{PARAMETER.P_PERSON_ID})

Next we need to incorporate this SQL Query in the table based Valueset

We can create the table based Valueset like below:
Navigator > Setup and Maintenance > Manage Value Sets

Create Table based Valueset like below:

Table based valueset for use in fast formula or payroll flows in fusion hcm using sql query
table based valueset with assignment sql query for use in valueset in fusion hcm

Here in the Valueset we are using the parameter P_PERSON_ID. We need to set this value in the Fast Formula to get the future dated assignment status type value.

Next we need to create the Fast Formula to get the value from this Valueset and pass it on to the HCM Extract.

Creating Fast Formula to use Valueset:

Navigator > Setup and Maintenance > Define Fast Formulas > Fast Formulas

Create the Fast Formula like below: Extract Rule Formula type

Creating Fast formula for Extract Rule in Fusion hcm to use valueset
complete fast formula code to use valuset and derive information in fusion hcm

Fast Formula Code:

DEFAULT FOR DATA_ELEMENTS IS EMPTY_TEXT_TEXT
INPUTS ARE DATA_ELEMENT_CODE (TEXT), DATA_ELEMENTS (TEXT_TEXT)
RULE_VALUE = ' '
if (DATA_ELEMENTS.EXISTS('PERSON_ID')) then
(
l_person_id = DATA_ELEMENTS['PERSON_ID']
RULE_VALUE = GET_VALUE_SET('SM_GET_EMPLOYEE_FUTURE_STATUS','|=P_PERSON_ID='''||l_person_id||'''')
)
RETURN RULE_VALUE

In this Fast Formula we are using the input as PERSON_ID to get the future Assignment Status. The PERSON_ID should be defined in the record with less sequence than the extract rule Fast Formula for this logic to work.

Ex: We have ASSIGNMENT_RECORD in the ASSIGNMENT_DATAGROUP and in this record we have 4 attributes now..

Seq
Attribute Name
1
PERSON_ID
2
JOB_ID
3
ORGANIZATION_ID
4
LOCATION_ID
5
Future Assignment Status (Extract Rule FF)

We need to add the attribute with FF at a sequence greater than the input value it is dependent on else the FF won’t return any value.

Adding attribute to HCM Extract:

Navigator > Data Exchange > Manage Extract Definitions > Search for Extract Name > Click on Advanced Edit
Go to the record where the attribute needs to be added and check the available sequence post the dependent input attribute sequence and add the FF like below:

Adding attribute to hcm extract to use fast formula which uses valueset in fusion hcm

Once you add this attribute, please compile the formulas and validate the extract. Once validated, you are good to run the extract and check the output.

Tip: We can use this table based Valueset and Fast Formula approach wherever we do not find a dbi or where we cannot achieve the logic using SBIs. Using this approach could hit performance if there are huge number of employees.

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.

Leave a Reply