Fast Formula – Parent and Child Formula (Formula Calling Formula)
In the previous section, we went over on how to create a sample Fast Formula that can be used in a real life scenario.
In this session, we will create a Fast Formula (Child) that can be called from another Fast Formula (Parent). In other words, we will explain the Parent-Child concept of a Fast Formula.
Why do we need to call another Fast Formula?
The Fast Formula documentation reads the functionality of the Formula calling Formula as
“Instead of writing large formulas, common calculations can be put into their own smaller formulas. These calculation formulas can then be called from other formulas that need the calculation.”
Yes, it can be used for separating the common routine in different Fast Formulas but this is more of a convenience than necessity. I have different reasons on why we need this feature more than just separating the common routines. Below are two examples.
- If an organization has employees from different legislation, we need to have different Fast Formulas to support data from different Legislation.
- If we need to get data from different applications, we need to work with different DBI and Context. Since our Formula Type does not support the entire Context, we need to use different Formula Types.
1) Legislative Data Group (LDG): There are many DBIs that can be used only in a Legislative Data Group specific formula. For example, in Payroll Element Entry values DBI, I believe that the entire Payroll related DBIs can be used only in LDG specific Fast Formula (needs to be verified though).
In case your organization has employees from more than one LDG (assume US, UK) and you want to use the element entry values in your Fast Formula (assume Salary Element Entry value), you cannot achieve your requirement in one Fast Formula. If you create a Fast Formula in US LDG, then this Fast Formula will error for UK employees.
In this case, you can follow these steps:
- You can create a Fast Formula (Parent) that is non LDG specific. This can be used by both UK and US employees.
- Create a Fast Formula (Child) in US LDG. Let us assume this child Fast Formula is named as TILAKSHMI_FF_CHILD_US. We will use the Salary Element Entry from US LDG.
- Create a Fast Formula in UK LDG with the same name, TILAKSHMI_FF_CHILD_US. We use the Salary Element Entry for UK in this Fast Formula. If you remember, since we provided a LDG, the uniqueness is validated within the LDG while saving the Fast Formula. Please check the second section where we explained the uniqueness of Fast Formula name.
- Now, you can call the child Fast Formula, TILAKSHMI_FF_CHILD_US from your parent Fast Formula either by “CALL_FORMULA()” or “EXECUTE()”.
- You will attach the Parent Formula in your UI or process.
- In case you do not have same name for Child Fast Formula in UK and US LDG, you need to use IF condition in parent Fast Formula to call the right Fast Formula for the LDG.
For the above scenario, when the parent Fast Formula is executed:
- It will call the child Fast Formula as per the employee’s LDG.
- If the employee belongs to US, then the parent will call the child, TILAKSHMI_FF_CHILD_US that is created in US LDG.
- If the employee belongs to UK, then the parent will call the TILAKSHMI_FF_CHILD_US that is created in UK LDG.
Create a Child Formula in US LDG.
Create a Child Formula of same type in UK LDG using the same Formula Name.
Formula Text:
/*
Author : Tilak
Type : Compensation Default and Override
NAME : TILAKSHMI_FF_CHILD_US
Remarks: This is a Child formula in US LDG
*/
/*
DEFAULT FOR MONTHLY_SALARYBASIS_ELEMENT_AMOUNT_ENTRY_VALUE IS -1
l_hr_assign_id = get_context(HR_ASSIGNMENT_ID, -1)
l_eff_date = get_context(DATE_EARNED, '1900/01/01 00:00:00' (date))
l_pay_assign_id = get_context(PAYROLL_ASSIGNMENT_ID, -1)
L_DATA = ESS_LOG_WRITE( 'BEGIN TILAKSHMI_FF_CHILD_US' )
L_DATA = ESS_LOG_WRITE( 'ASG ID :' + TO_CHAR(l_hr_assign_id ))
L_DATA = ESS_LOG_WRITE( 'PAY ASG ID :' + TO_CHAR(l_pay_assign_id ))
L_DATA = ESS_LOG_WRITE( 'Date :' + TO_CHAR(l_eff_date , 'YYYY/MM/DD') )
L_VALUE = MONTHLY_SALARYBASIS_ELEMENT_AMOUNT_ENTRY_VALUE
L_DATA = ESS_LOG_WRITE( 'EXIT TILAKSHMI_FF_CHILD_US ' + TO_CHAR(L_VALUE) )
RETURN L_VALUE
Explanation of Fast Formula:
- This is a simple Fast Formula which just returns a value from a DBI, MONTHLY_SALARYBASIS_ELEMENT_AMOUNT_ENTRY_VALUE.
Parent Formula is created without LDG.
Formula Text:
/*
Author : Tilak
Type : Compensation Default and Override
NAME : TILAKSHMI_FF_PARENT_US
Remarks: This is a Parent formula without any LDG
*/
l_hr_assign_id = get_context(HR_ASSIGNMENT_ID, -1)
l_eff_date = get_context(DATE_EARNED, '1900/01/01 00:00:00' (date))
l_pay_assign_id = get_context(PAYROLL_ASSIGNMENT_ID, -1)
L_DATA = ESS_LOG_WRITE( 'BEGIN TILAKSHMI_FF_PARENT_US' )
L_DATA = ESS_LOG_WRITE( 'ASG ID :' + TO_CHAR(l_hr_assign_id ))
L_DATA = ESS_LOG_WRITE( 'PAY ASG ID :' + TO_CHAR(l_pay_assign_id ))
L_DATA = ESS_LOG_WRITE( 'Date :' + TO_CHAR(l_eff_date , 'YYYY/MM/DD') )
L_VALUE = 0
/* check if the child formula is available*/
IF (IS_EXECUTABLE('TILAKSHMI_FF_CHILD_US')) THEN
(
/* call the child formula */
EXECUTE('TILAKSHMI_FF_CHILD_US')
L_VALUE = GET_OUTPUT('L_VALUE', 0)
)
L_DATA = ESS_LOG_WRITE( 'EXIT TILAKSHMI_FF_PARENT_US ' + TO_CHAR(L_VALUE) )
RETURN L_VALUE
Explanation of Fast Formula
This is again a simple Fast Formula. There are three points you need to understand here.
- In one of our previous sessions, we explained that the Formula engine only passes the Contexts that are used in the Formula. Therefore, it is the developer’s responsibility to make sure the value for Context that is used in the Child formula is also passed to the Parent Formula. For this, you need to get all the Contexts in Parent formula though it is not used in the Parent Fast Formula. You can use GET_CONTEXT, NEED_CONTEXT etc to make sure that the value of the context are passed to the Parent Formula.
l_hr_assign_id = get_context(HR_ASSIGNMENT_ID, -1)
l_eff_date = get_context(DATE_EARNED, '1900/01/01 00:00:00' (date))
l_pay_assign_id = get_context(PAYROLL_ASSIGNMENT_ID, -1)
- Before we call the child formula we make sure it is available.
IF (IS_EXECUTABLE('TILAKSHMI_FF_CHILD_US')) THEN
(
- Call the child formula and get the output from the Child Formula.
/* call the child formula */
EXECUTE('TILAKSHMI_FF_CHILD_US')
L_VALUE = GET_OUTPUT('L_VALUE', 0)
2) Context: As we discussed in our previous sessions, different Formula Types support different context sets. Therefore, different Formula Types support different sets of DBI. If you need to understand the relation between Formula Types, Context and DBIs, please read our first session.
Since some DBIs can be used only in some Formula Types, we need to use different Formula Types to achieve our requirements. But our UI Field supports only one Formula Type, therefore, you need to use the child Fast Formula of different Formula Type to get the information from the DBI.
Let us go through an example to understand this part.
- For example, Compensation Default and Override (for CWB) needs to find the DBA Certification of a person to determine the Bonus.
- Let us assume the bonus is calculated as: 20% of Salary + $1000, if the DBA Certification is obtained in the plan period.
- The DBA Certification information is available in the Talent Management. And we will use the DBI; ‘HRT_PERSON_GENERIC_CONTENT_TYPE_CONTENT_ITEM_NAME’ to find the certification.
- For using the DBI, ‘HRT_PERSON_GENERIC_CONTENT_TYPE_CONTENT_ITEM_NAME’, we need to use the context, 'TALENT_PROFILE_CONTENT_TYPE' and set the context value as 'CERTIFICATION'.
- This Context is supported only by Talent Formula Types. For example, “Generic Formula type for Profile Content” supports the Context, 'TALENT_PROFILE_CONTENT_TYPE'.
To achieve the above requirement:
- We will create a Parent Formula of Formula Type, “Compensation Default and Override”
- The Parent Formula will call a Child Formula Type, “Generic Formula type for Profile Content” after setting the context, 'TALENT_PROFILE_CONTENT_TYPE'.
Create a Child Formula in Talent Type and use the Talent DBI.
Formula Text:
/*
TYPE : Generic Formula type for Profile Content
Name : TILAKSHMI_TALENT_DBI_CERT
Author : Tilak
*/
/*DEFAULT_DATA_VALUE FOR HRT_PERSON_GENERIC_CONTENT_TYPE_CONTEXT_NAME IS 'XXX'*/
DEFAULT_DATA_VALUE FOR HRT_PERSON_GENERIC_CONTENT_TYPE_CONTENT_ITEM_NAME IS 'XXX'
DEFAULT_DATA_VALUE FOR HRT_PERSON_GENERIC_CONTENT_TYPE_DATE_FROM IS '1900/01/01 00:00:00' (date)
INPUTS ARE IV_PLAN_START_DATE (text) ,IV_PLAN_END_DATE (text)
/*Default for Input values */
DEFAULT FOR IV_PLAN_START_DATE IS '2001/01/01'
DEFAULT FOR IV_PLAN_END_DATE IS '2001/01/01'
L_RETURN_VALUE = 'N'
L_DATA = ESS_LOG_WRITE( 'BEGIN TILAKSHMI_TALENT_DBI_CERT' )
l_person_id = get_context(PERSON_ID,-1)
l_eff_date = get_context(EFFECTIVE_DATE,'1900/01/01 00:00:00' (date))
l_type = get_context(TALENT_PROFILE_CONTENT_TYPE , 'NONE')
L_DATA = ESS_LOG_WRITE( 'Person ID Context ' || TO_CHAR(l_person_id ) )
L_DATA = ESS_LOG_WRITE( 'EFFECTIVE_DATE Context ' || TO_CHAR(l_eff_date ) )
L_DATA = ESS_LOG_WRITE( 'TALENT_PROFILE_CONTENT_TYPE Context ' || l_type)
L_DATA = ESS_LOG_WRITE( 'CMP_IV_PLAN_START_DATE ' || IV_PLAN_START_DATE )
L_DATA = ESS_LOG_WRITE( 'CMP_IV_PLAN_END_DATE ' || IV_PLAN_END_DATE )
/* convert plan dates into date value */
l_start_date = TO_DATE(IV_PLAN_START_DATE, 'YYYY/MM/DD')
l_end_date = TO_DATE(IV_PLAN_END_DATE, 'YYYY/MM/DD')
L_DATA = ESS_LOG_WRITE( 'Start of the Loop in DBI ' )
l_index = HRT_PERSON_GENERIC_CONTENT_TYPE_CONTENT_ITEM_NAME.FIRST(-1)
l_counter = 1
/* loop the all the item value */
WHILE HRT_PERSON_GENERIC_CONTENT_TYPE_CONTENT_ITEM_NAME.EXISTS(l_index)
LOOP(
L_DATA = ESS_LOG_WRITE( 'IN Index ' || TO_CHAR(l_index ) )
L_VALUE= HRT_PERSON_GENERIC_CONTENT_TYPE_CONTENT_ITEM_NAME[l_index]
L_DATA = ESS_LOG_WRITE( 'ITEM VALUE ' || L_VALUE )
/* Check the item value us DBA Certification */
IF (L_VALUE = 'ORACLE DBA CERTIFIED' ) THEN (
L_DATA = ESS_LOG_WRITE( 'DBA ' )
l_DATE = HRT_PERSON_GENERIC_CONTENT_TYPE_DATE_FROM[l_index]
/* if the certification received within the plan period */
if (l_DATE >= l_start_date AND l_DATE <= l_end_date) THEN (
L_DATA = ESS_LOG_WRITE( 'DBS IN CURRENT PLAN YEAR ' )
L_RETURN_VALUE = 'Y'
EXIT
)
)
l_index = HRT_PERSON_GENERIC_CONTENT_TYPE_CONTENT_ITEM_NAME.NEXT(l_index,-1)
)
L_DATA = ESS_LOG_WRITE( 'End of the Loop' )
L_DATA = ESS_LOG_WRITE( 'END TILAKSHMI_TALENT_DBI_CERT: ' || L_RETURN_VALUE )
RETURN L_RETURN_VALUE
Explanation of Fast Formula:
This Child Formula uses array DBI. Since the objective of this session is not to go over Arrays, we will cover this part in the next session.
INPUTS ARE IV_PLAN_START_DATE (text) , IV_PLAN_END_DATE (text)
/*Default for Input values */
DEFAULT FOR IV_PLAN_START_DATE IS '2001/01/01'
DEFAULT FOR IV_PLAN_END_DATE IS '2001/01/01'
In the Business logic, the Fast Formula loops the through the DBI values and validates the value of the DBI. If the value is ‘DBA Certification’, and if the ‘DBA Certification’ has been received within the plan period, then we return ‘Y’.
/* loop the all the item values */
WHILE HRT_PERSON_GENERIC_CONTENT_TYPE_CONTENT_ITEM_NAME.EXISTS(l_index)
LOOP(
L_DATA = ESS_LOG_WRITE( 'IN Index ' || TO_CHAR(l_index ) )
L_VALUE= HRT_PERSON_GENERIC_CONTENT_TYPE_CONTENT_ITEM_NAME[l_index]
L_DATA = ESS_LOG_WRITE( 'ITEM VALUE ' || L_VALUE )
/* Check the item value us DBA Certification */
IF (L_VALUE = 'ORACLE DBA CERTIFIED' ) THEN (
L_DATA = ESS_LOG_WRITE( 'DBA ' )
l_DATE = HRT_PERSON_GENERIC_CONTENT_TYPE_DATE_FROM[l_index]
/* if the certification received within the plan period */
if (l_DATE >= l_start_date AND l_DATE <= l_end_date) THEN (
L_DATA = ESS_LOG_WRITE( 'DBS IN CURRENT PLAN YEAR ' )
L_RETURN_VALUE = 'Y'
EXIT
)
)
l_index = HRT_PERSON_GENERIC_CONTENT_TYPE_CONTENT_ITEM_NAME.NEXT(l_index,-1)
)
Create Parent Formula in Compensation Type to use it in Compensation.
Formula Text:
/*
TYPE : Compensation Default and Override
Name : TILAKSHMI_CWB_PARENT_DBA_CERT
Author : Tilak
*/
DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT IS 0
INPUTS ARE CMP_IV_PLAN_START_DATE (text) ,CMP_IV_PLAN_END_DATE (text)
DEFAULT FOR CMP_IV_PLAN_START_DATE IS '2001/01/01'
DEFAULT FOR CMP_IV_PLAN_END_DATE IS '4012/01/01'
L_DATA = ESS_LOG_WRITE( 'BEGIN TILAKSHMI_CWB_PARENT_DBA_CERT' )
l_person_id = get_context(PERSON_ID,-1)
l_eff_date = get_context(EFFECTIVE_DATE,'1900/01/01 00:00:00' (date))
L_RETURN_VALUE = 'N'
l_end_date = TO_DATE(CMP_IV_PLAN_END_DATE, 'YYYY/MM/DD')
L_DATA = ESS_LOG_WRITE( 'Person ID COntext : ' || TO_CHAR(l_person_id) )
L_DATA = ESS_LOG_WRITE( 'EFFECTIVE_DATE context : ' || TO_CHAR(l_eff_date) )
L_DATA = ESS_LOG_WRITE( 'START DATE : ' || CMP_IV_PLAN_START_DATE )
L_DATA = ESS_LOG_WRITE( 'END DATE : ' || CMP_IV_PLAN_END_DATE )
L_VALUE = -1
/* When the salary is defined
*/
IF CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT WAS NOT DEFAULTED THEN
(
L_VALUE = CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT
L_VALUE = (L_VALUE/100)*20
L_DATA = ESS_LOG_WRITE( '20 % of Sal ' + TO_CHAR(L_VALUE ) )
IF (IS_EXECUTABLE('TILAKSHMI_TALENT_DBI_CERT')) THEN
(
SET_INPUT('TALENT_PROFILE_CONTENT_TYPE','CERTIFICATION')
SET_INPUT('EFFECTIVE_DATE',l_end_date)
SET_INPUT('IV_PLAN_START_DATE', CMP_IV_PLAN_START_DATE )
SET_INPUT('IV_PLAN_END_DATE',CMP_IV_PLAN_END_DATE )
EXECUTE('TILAKSHMI_TALENT_DBI_CERT')
L_CERT_VALUE = GET_OUTPUT('L_RETURN_VALUE','N')
L_DATA = ESS_LOG_WRITE( ' RETURN VALUE OF CERT '|| L_CERT_VALUE )
/* add 1000 if cert is Y
*/
IF (L_CERT_VALUE = 'Y') THEN
(
L_VALUE = L_VALUE + 1000
L_DATA = ESS_LOG_WRITE( ' Bonus after certification : '|| TO_CHAR(L_VALUE))
)
)
)
L_DATA = ESS_LOG_WRITE( 'END TEST_CMP_CPR_ENROLLED_FF : '|| TO_CHAR(L_VALUE))
RETURN L_VALUE
Explanation of Fast Formula
Define the DBI and Input values that are used in parent and child Fast Formula.
DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT IS 0
INPUTS ARE CMP_IV_PLAN_START_DATE (text) , CMP_IV_PLAN_END_DATE (text)
DEFAULT FOR CMP_IV_PLAN_START_DATE IS '2001/01/01'
DEFAULT FOR CMP_IV_PLAN_END_DATE IS '4012/01/01'
L_DATA = ESS_LOG_WRITE( 'BEGIN TILAKSHMI_CWB_PARENT_DBA_CERT' )
l_person_id = get_context(PERSON_ID,-1)
l_eff_date = get_context(EFFECTIVE_DATE,'1900/01/01 00:00:00' (date))
L_RETURN_VALUE = 'N'
l_end_date = TO_DATE(CMP_IV_PLAN_END_DATE, 'YYYY/MM/DD')
As per the business logic, validate the salary. If the salary is not defined, do not proceed. If the salary is defined, calculate 20% for salary for Bonus.
L_VALUE = -1
/* When the salary is defined
*/
IF CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT WAS NOT DEFAULTED THEN
(
L_VALUE = CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT
L_VALUE = (L_VALUE/100)*20
L_DATA = ESS_LOG_WRITE( '20 % of Sal ' + TO_CHAR(L_VALUE ) )
Check if the Child Fast Formula is available. If so, set the required context and input values.
IF (IS_EXECUTABLE('TILAKSHMI_TALENT_DBI_CERT')) THEN
(
SET_INPUT('TALENT_PROFILE_CONTENT_TYPE','CERTIFICATION')
SET_INPUT('EFFECTIVE_DATE',l_end_date)
SET_INPUT('IV_PLAN_START_DATE', CMP_IV_PLAN_START_DATE )
SET_INPUT('IV_PLAN_END_DATE',CMP_IV_PLAN_END_DATE )
Just for this example, we use different input values. You do not need to do so in a real Fast Formula.
SET_INPUT('IV_PLAN_START_DATE', CMP_IV_PLAN_START_DATE )
SET_INPUT('IV_PLAN_END_DATE',CMP_IV_PLAN_END_DATE )
If you notice, the Context is also set like the input values.
SET_INPUT('EFFECTIVE_DATE',l_end_date)
Execute the Child Fast Formula and get the return value. You need to use the return variables from Child Fast Formula ('L_RETURN_VALUE’) to get the value. The second parameter (‘N’) is the default value. If the child formula returns ‘Y’ then we add $1000 to the Bonus.
SET_INPUT('IV_PLAN_END_DATE',CMP_IV_PLAN_END_DATE )
EXECUTE('TILAKSHMI_TALENT_DBI_CERT')
L_CERT_VALUE = GET_OUTPUT('L_RETURN_VALUE','N')
L_DATA = ESS_LOG_WRITE( ' RETURN VALUE OF CERT '|| L_CERT_VALUE )
/*
add 1000 if cert is Y
*/
IF (L_CERT_VALUE = 'Y') THEN
(
L_VALUE = L_VALUE + 1000
L_DATA = ESS_LOG_WRITE( ' Bonus after certification : '|| TO_CHAR(L_VALUE))
)
Please look at our previous section to see how the Fast Formula is added in the UI. In the same manner, please add the parent Fast Formula to the UI.
YouTube Tutorial 14
/*************************
Name: TCS_CHILD_FF
Type: Compensation Person Selection
Simple Child Formula
**************************/
l_temp = ESS_LOG_WRITE('Entering TCS_CHILD_FF' )
VALUE = 'Y'
l_person = get_context(PERSON_ID, -1)
l_asg = get_context(HR_ASSIGNMENT_ID, -1)
l_temp = ESS_LOG_WRITE('Person ' + to_char(l_person) )
l_temp = ESS_LOG_WRITE('Asg ' + to_char(l_asg) )
if l_person = -1 OR l_asg = -1 THEN
(
VALUE = 'Y'
)
l_temp = ESS_LOG_WRITE('Leaving TCS_CHILD_FF ' + value )
Return VALUE
/*******************
Name: TCS_PARENT_FF
Type: Compensation Person Selection
Simple Parent Formula that calls Child Formula
******************/
l_temp = ESS_LOG_WRITE('Entering TCS_PARENT_FF' )
RETVALUE = 'N'
person = get_context(PERSON_ID, -1)
asg = get_context(HR_ASSIGNMENT_ID, -1)
l_temp = ESS_LOG_WRITE('Person ID ' + TO_CHAR(person) )
IF IS_EXECUTABLE('TCS_CHILD_FF') THEN
(
set_input('MYTEST' , 'XXXXXX')
set_input('PERSON_ID' 20000 )
l_temp = ESS_LOG_WRITE('Entering TCS_PARENT_FF' )
EXECUTE('TCS_CHILD_FF')
l_var = GET_OUTPUT('VALUE', 'x')
l_temp = ESS_LOG_WRITE('Child Return ' + l_var)
if l_var = 'Y' then
(
RETVALUE = 'Y'
)
)
YouTube Tutorial 16
/*************************
Name: TCS_CHILD_FF
Type: Age Calculation
Simple Child Formula
**************************/
DEFAULT FOR BEN_PGM_NAME is ' '
l_temp = ESS_LOG_WRITE('Entering TCS_CHILD_FF' )
VALUE = 'N'
ldate = get_Context(EFFECTIVE_DATE, '1900/01/01' (date) )
lbg = get_context(BUSINESS_GROUP_ID, -1)
lpgm = get_context(PGM_ID , -1)
lpgmname = BEN_PGM_NAME
l_temp = ESS_LOG_WRITE('Date ' + to_char(ldate) )
l_temp = ESS_LOG_WRITE('Bg ' + to_char(lbg) )
l_temp = ESS_LOG_WRITE('pgm ' + to_char(lpgm) )
l_temp = ESS_LOG_WRITE('pgm NAME ' + lpgmname)
l_temp = ESS_LOG_WRITE('Entering TCS_CHILD_FF ' + VALUE)
Return VALUE
/*******************
Name: TCS_PARENT_FF
Type: Compensation Person Selection
Simple Parent Formula that calls Child Formula
******************/
l_temp = ESS_LOG_WRITE('Entering TCS_PARENT_FF' )
RETVALUE = 'N'
person = get_context(PERSON_ID, -1)
asg = get_context(HR_ASSIGNMENT_ID, -1)
l_temp = ESS_LOG_WRITE('Person ID ' + TO_CHAR(person) )
IF IS_EXECUTABLE('TCS_CHILD_FF') THEN
(
l_var = 'N'
CALL_FORMULA('TCS_CHILD_FF' ,
100010025071996 > 'BUSINESS_GROUP_ID' ,
100100081493359 > 'PGM_ID' ,
l_var < 'VALUE' default 'X'
)
l_temp = ESS_LOG_WRITE('Child Return ' + l_var)
if l_var = 'Y' then
(
RETVALUE = 'Y'
)
)
l_temp = ESS_LOG_WRITE('Leaving TCS_PARENT_FF ' + RETVALUE )
RETURN RETVALUE
