Fast Formulas and Table Value Sets in Oracle HCM Extracts

In this article, we will see how Fast Formulas can be used in HCM Extracts. What are Fast formula types that Oracle provided in HCM Extracts.

Let’s see here in detail, step by step.

In Oracle Fusion, HCM Extract Fast Formulas are used to handle complex logic and calculations during data extraction. Here are the main types:

1. Extract Criteria Formula: Used to define complex criteria for determining whether a record should be included in the extract. This formula type is used to apply filters in the extract.
2. Extract Rule Formula: Helps calculate or derive attribute values based on specific business rules or logic. This formula type is used to fetch any custom requirement information.
3. Extract Advanced Condition Formula: Specifies conditions to trigger actions like logs or warnings during the extraction process.
4. Extract Record Formula: Automatically generated when using the "Generate Formula" option in Extract Records.

We will create a simple extract to understand step by step how a Extract Rule can be applied to an attribute based on existing attributes.

Navigate to Data Exchange à Extract Definitions and create a HCM Extract. Create a Data Group and add required attributes.

I am using PER_EXT_SEC_PERSON_UE user entity to add basic attributes like Person ID, Person Number, Start Date, Effective Start Date and Effective End Date. In this user entity, we do not have Person Full Name.

When we do not have Database Items in the User Entities, we can create a table value set and get the required information through Formula by passing parameters to the table value set.

Navigate to Manage Value Sets à Create a Table Value Set

Value Set: 

From Clause: PER_PERSON_NAMES_F PPNF

Where Clause:

PERSON_ID = :{PARAMETER.EMPLOYEE_ID}

AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE AND PPNF.NAME_TYPE = 'GLOBAL'

SQL Query to verify Table Value Set:

Table value set can be verified by creating a BI Data Model using below query.

SELECT

pay_ff_functions.gvs('MAB_PERSON_FULLNAME_SET','|=EMPLOYEE_ID='''||'300000047888610') RESULT

FROM DUAL

Extract Rule Fast Formula:

Navigate to task – Fast Formulas and Create a formula

/**************************************************
FORMULA NAME: To fetch Person Name in HCM Extract
DESCRIPTION: Sample Fast Formula to Fetch Person Name by Person ID.
**************************************************/
 
/* Constants */
DEFAULT FOR DATA_ELEMENTS IS EMPTY_TEXT_TEXT
 
/* Inputs */
INPUTS ARE DATA_ELEMENT_CODE (TEXT), DATA_ELEMENTS (TEXT_TEXT)
RULE_VALUE = ' '
 
/* Person Name based on Person ID */
if (DATA_ELEMENTS.EXISTS('EMPLOYEE_ID')) then
(
l_person_id = DATA_ELEMENTS['EMPLOYEE_ID']
RULE_VALUE = GET_VALUE_SET('MAB_PERSON_FULLNAME_SET','|=EMPLOYEE_ID='''||l_person_id||'''')
)
RETURN RULE_VALUE

Once Table Value Set is verified and Formula is compiled, we can now move to add an attribute with Employee Name

Navigate to Extract Definitions à Open the extract and go to Attributes. Click on Add and select Rule. Enter the details as shown below screen.

After adding details, click OK. Validate the Extract and it will be compiled. Define a delivery option with NONE (as this is for testing).

Submit the extract and verify the results. As we have set Delivery as NONE, it will only generate XML Data.

Click here to access Fast Formulas in HCM Extracts.pdf