• Post category:Fast Formula
  • Post comments:0 Comments
  • Post last modified:April 22, 2021
  • Reading time:7 mins read
You are currently viewing Sample Extract Rule Fast Formulas for ready reference
Sample Extract Rule Fast Formulas for ready reference

In this article I will be sharing some ready-to-use Extract Rule Fast formulas that we use in HCM Extracts to get work email, username with conditions, company code using values set, element entry input value, current date-time, and Home Mobile number.

Table of Contents

Extract Rule Fast Formula

These types of formulas as used to assign value to Attributes on the Extract. The return value should always be RULE_VALUE and it should be character type. If we must return the date or number, then we need to use the TO_CHAR function and convert them to character type and return the RULE_VALUE.

These formulas are mainly used for performing calculations and deriving attribute values based on other attributes in the extract.

Sample 1: Get Work Email of Employee

Get the Work Email of the Employee which is getting processed. The PERSON_ID context will automatically get passed to the formula when the employee’s record is getting processed. If we have to get the Work Email without a FF, we will have to add another data group as a child to the main data group, add a filter to get the work email, and then add an attribute. With FF we can avoid all those steps.

/* Default Values */
DEFAULT_DATA_VALUE FOR PER_PER_EMAIL_MAIL_ADDRESS is 'XX'
DEFAULT_DATA_VALUE FOR PER_PER_EMAIL_EMAIL_TYPE is 'XX'
/*================ FORMULA SECTION BEGIN =======================*/
l_loop         = 1
l_email_type   = 'x'
RULE_VALUE     = ' '
WHILE (PER_PER_EMAIL_MAIL_ADDRESS.exists(l_loop))
  LOOP
  (
      l_email_type = PER_PER_EMAIL_EMAIL_TYPE[l_loop]
      If (l_email_type = 'W1') THEN
      (
      RULE_VALUE = PER_PER_EMAIL_MAIL_ADDRESS[l_loop]
      RETURN RULE_VALUE
      )
l_loop = l_loop +1
  )
RETURN RULE_VALUE

Sample 2: Derive value based on Username

If USERNAME is not like ‘%CLIENT.COM%’ then pass the USERNAME else pass BLANK value

/* Default Values */
DEFAULT FOR PER_PER_USER_NAME is 'XX'
RULE_VALUE = ' '
   RULE_VALUE = PER_PER_USER_NAME
   If upper(PER_PER_USER_NAME) like '%CLIENT.COM%' then
      (RULE_VALUE = 'EMPLOYEE')
      
   if RULE_VALUE = 'XX' then 
      (RULE_VALUE = ' ')
   
RETURN RULE_VALUE

Sample 3: Derive value using Valueset

Get the Company Code from Value Set by passing the Legal Employer. Here the Legal Employer is another attribute on the Record with a sequence less than the sequence of this Company Code attribute. This formula calls the Table-based Value Set GET_ADP_COMPANY_CODE_VS to derive the Company Code Value. This Value Set is covered in the next section which lists sample Value Sets.

/* GET_COMPANY_CODE_FF */
/* FF to get the company code from legal employer mapping */
default for rule_value is ' '
INPUTS ARE DATA_ELEMENTS (TEXT_TEXT)
L_COMPANY_CODE = ' '
if(DATA_ELEMENTS.EXISTS('LE_NAME')) then
(
L_LEGAL_EMPLOYER = DATA_ELEMENTS['LE_NAME']
L_COMPANY_CODE = GET_VALUE_SET('GET_ADP_COMPANY_CODE_VS' ,'|=L_LEGAL_EMPLOYER='''|| L_LEGAL_EMPLOYER||'''')
L_LOG_DATA = ESS_LOG_WRITE ('L_LEGAL_EMPLOYER - ' || L_LEGAL_EMPLOYER || ' L_COMPANY_CODE - ' || L_COMPANY_CODE)
)
rule_value = L_COMPANY_CODE
RETURN rule_value

Sample 4: Get values from Element Entry by passing contexts

Get the Provident Fund Amount value from Element Entry. Here the input EFFECTIVE_DATE is being retrieved using the GET_CONTEXT and is being compared with the Element Entry Start Date and End Dates and whenever a match is found, the corresponding Amount value is returned.

DEFAULT_DATA_VALUE for ELEMENT_ENTRY_VALUE IS ' '
DEFAULT_DATA_VALUE for ELEMENT_ENTRY_BASE_ELEMENT_NAME IS ' '
DEFAULT_DATA_VALUE for ELEMENT_ENTRY_BASE_VALUE_NAME IS ' '
DEFAULT_DATA_VALUE for ELEMENT_ENTRY_EFFECTIVE_START_DATE is '1951/01/01 00:00:00' (date)
DEFAULT_DATA_VALUE for ELEMENT_ENTRY_EFFECTIVE_END_DATE is '1951/01/01 00:00:00' (date)
eff_date = GET_CONTEXT(EFFECTIVE_DATE, '1951/01/01 00:00:00' (date) )
I = 1
WHILE ELEMENT_ENTRY_VALUE.EXISTS(I) LOOP
(
    IF eff_date >= ELEMENT_ENTRY_EFFECTIVE_START_DATE[I] AND 
       eff_date <= ELEMENT_ENTRY_EFFECTIVE_END_DATE[I]   AND     
        ELEMENT_ENTRY_BASE_ELEMENT_NAME[I]='Provident Fund' AND
        ELEMENT_ENTRY_BASE_VALUE_NAME[i]= 'Amount'  THEN
    (
        RULE_VALUE =ELEMENT_ENTRY_VALUE[I]
        RETURN  RULE_VALUE
        EXIT
    )
    i=i+1
 )

Sample 5: Get Current Date and Time

Get the Input Date and the current Time in the extract. It can be used to name the file dynamically on the Delivery Options from the attribute Value. PER_SYS_DATE_TIME is used to get the system date and time.

DEFAULT FOR PER_SYS_DATE_TIME IS ' '
INPUTS ARE DATA_ELEMENTS (TEXT_TEXT)
l_text(DATE)=GET_PARAMETER_VALUE_DATE('effective_date')
L_DATE_TEMP1(Date)=  TO_DATE(SUBSTR(TRANSLATE(PER_SYS_DATE_TIME, '01234567890:.-TZ', '01234567890:.- '), 1, 19), 'YYYY-MM-DD HH24:MI:SS')
PARAMETER_DATE = TO_CHAR(l_text, 'YYYYMMDD')||' '||TO_CHAR(L_DATE_TEMP1, 'HH:MI:SS AM')
RULE_VALUE= PARAMETER_DATE
RETURN RULE_VALUE

Sample 6: Get the Home Phone/Home Mobile based on values

Get the Home Phone number if it exists else get the Home Mobile Phone.

/*@Section Default for DBI*/
default for PER_PER_HOME_PHONE_NUMBER      is 'X'
/*@Section Input Parameters*/
INPUTS ARE DATA_ELEMENTS (TEXT_TEXT)
lc_person_id = to_char(get_context( PERSON_ID,0))
lc_eff_date =to_char( get_context( EFFECTIVE_DATE,to_date('01/01/1951', 'MM/DD/YYYY')), 'MM/DD/YYYY')
IF PER_PER_HOME_PHONE_NUMBER was not defaulted then 
( 
	Rule_value = PER_PER_HOME_PHONE_NUMBER
	RETURN RULE_VALUE
)
ELSE
(
	l_phone_type ='HM'
	rule_value = get_value_set( 'HOME_MOBILE_NUMBER', '|=P_person_id ='''||lc_person_id||''''||'|P_DATE='''||lc_eff_date|| ''''||'|P_TYPEE='''||l_phone_type|| '''')
	
RETURN rule_value
)

The above samples can be used as a starting point when you have a requirement to derive/calculate values for an attribute in HCM Extract.

If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.