Using Table based Valueset when DBI is unavailable

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:

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

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..

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:

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.