Compensation and Benefit Data
In this blog, we try to explain how the Benefit data can be used in Compensation by using Fast Formula.
In our example, we use Total Compensation Item Formula, but you can use any Compensation or Grade Steps Formula as these CMP and GSP Fast Formula Types use the same Context.
In our example, we get Benefit Amount and Benefit Coverage Start Date of a plan using Benefit function, BEN_FN_GET_CHAR_VALUE and these values are used as Total Compensation Transaction Date and Total Compensation Transaction Values.
Can we use the Benefit Function, BEN_FN_GET_CHAR_VALUE in Compensation Fast Formula?
No, we cannot use the Benefit Function, BEN_FN_GET_CHAR_VALUE in any Compensation Fast Formula. The Function, BEN_FN_GET_CHAR_VALUE uses the context, HR_ASSIGNMENT_ID, EFFECTIVE_DATE and BUSINESS_GROUP_ID. Unfortunately, Compensation Fast Formula Types do not support the context, BUSINESS_GROUP_ID therefore we cannot use any Benefit DBI or Function in Compensation. Most of the BEN DBI and Functions uses the context, BUSINESS_GROUP_ID.
How do we use Benefit Function or DBI in Compensation Fast Formula?
The only possible way is to use the parent and child Fast Formula concept where you can use the Benefit Formula as a child Formula. The Benefit Formula may use different Context so that understanding of the Benefit Formula type is crucial before start using it. Once you know the DBI or Function and their context, the Benefit Formula can be selected as per your requirement (need of the Contexts). The value for the context can be passed from the parent (Compensation) Formula and you need to make sure the contexts value that are passed to the child Formulas are available in Compensation before calling the Child (Benefit) Formula (for example, Business Group Id).
How we implemented the COMP and BEN Formula:
We too implemented our Formula using parent and child Formula concept. As we said we are planning to use Benefit Function, BEN_FN_GET_CHAR_VALUE. This Benefit Function uses the contexts, HR_ASSIGNMENT_ID, EFFECTIVE_DATE and BUSINESS_GROUP_ID, and different parameters as per the expected values.
Context: Compensation Fast Formula types supports HR_ASSIGNMENT_ID, EFFECTIVE_DATE and BUSINESS_GROUP_ID is not a dynamic value and it is the same to the entire Organization so that we decided to hardcode the value to pass as context to the child Formula.
Parameters: For our requirement we need the parameters, Table name, Column name, Plan name and Option name. Since we know the above value, we are hard coding the parameters values too.
Table name => BEN_PRTT_ENRT_RSLT
Column name => BNFT_AMT/ENRT_CVG_STRT_DT
Plan Name => BEN_AUTO_DEP_DESG_PLAN
Option Name => AUTODESGEMP
Child Formula (Benefit):
/***************************
Formula Type : Age Calculation
Formula Name : CMP_BEN_TEST_1
Returns: Coverage Start Date and Benefit Amount
*******************************/
l_val = SET_LOG('Entering into FF CMP_BEN_TEST_1')
RET_DATE = 'N'
RET_VAL = 'N'
RET_DATE_CHAR = 'N'
L_ASG_ID = GET_CONTEXT(HR_ASSIGNMENT_ID, -1)
L_EFF_DATE = get_context(EFFECTIVE_DATE, '1900/01/01 00:00:00' (date))
L_BG_ID = GET_CONTEXT(BUSINESS_GROUP_ID, -1)
l_val = SET_LOG('Assignment ID ' || to_char(L_ASG_ID) )
l_val = SET_LOG('Eff Date ' || to_char(L_EFF_DATE) )
l_val = SET_LOG('BG Date ' || to_char(L_BG_ID) )
RET_DATE_CHAR = BEN_FN_GET_CHAR_VALUE ('BEN_PRTT_ENRT_RSLT', 'ENRT_CVG_STRT_DT' , 'BEN_AUTO_DEP_DESG_PLAN', 'AUTODESGEMP' )
RET_VAL = BEN_FN_GET_CHAR_VALUE ('BEN_PRTT_ENRT_RSLT', 'BNFT_AMT' , 'BEN_AUTO_DEP_DESG_PLAN', 'AUTODESGEMP' )
l_val = SET_LOG('cvg date val ' || RET_DATE_CHAR || ' /' || RET_VAL)
/* Date expected to be in yyyy-mm-dd format string
And we change that to yyyy/mm/dd format
*/
IF (LENGTH(RET_DATE_CHAR) = 10) THEN (
RET_DATE = TO_CHAR( TO_DATE( RET_DATE_CHAR), 'YYYY/MM/DD')
)
l_val = SET_LOG('Exiting into FF CMP_BEN_TEST_1 ' || RET_DATE || ' /' || RET_VAL)
RETURN RET_DATE , RET_VAL
Parent Formula (Total Compensation)
/***************************
Formula Type : Total Compensation Item
Formula Name : CMP_BEN_TEST
Returns: Coverage Start Date and Benefit Amount
*******************************/
l_val = SET_LOG('Entering into FF CMP_BEN_TEST')
COMPENSATION_DATES = '2000/01/01'
VALUES = '-1'
L_ASG_ID = GET_CONTEXT(HR_ASSIGNMENT_ID, -1)
L_EFF_DATE = get_context(EFFECTIVE_DATE, '1900/01/01 00:00:00' (date))
l_val = SET_LOG('Assignment ID ' || to_char(L_ASG_ID) )
l_val = SET_LOG('Eff Date ' || to_char(L_EFF_DATE) )
IF (IS_EXECUTABLE('CMP_BEN_TEST_1')) THEN
(
SET_INPUT('BUSINESS_GROUP_ID', 100010025071996)
EXECUTE('CMP_BEN_TEST_1')
COMPENSATION_DATES = GET_OUTPUT('RET_DATE','2000/01/02')
VALUES = GET_OUTPUT('RET_VAL','-2')
L_DATA = SET_LOG( ' RETURN VALUE OF CHILD '|| COMPENSATION_DATES || ' / '|| VALUES )
)
l_val = SET_LOG('Exiting into FF CMP_BEN_TEST ' || COMPENSATION_DATES || ' /' || VALUES)
RETURN COMPENSATION_DATES, VALUES
Since the above formulas are simple and at this point, we don’t think that the Formulas needed any explanation.
We use SET_LOG instead of ESS_LOG_WRITE. This set_log is compensation function this is an alternate to ESS_LOG_WRITE. Advantage of SET_LOG is, if you use the Formula validator/tester in Compensation, this will be useful.
ESS Monitor from Compensation:
The Ess Log provides the output values (Date: 2018/10/12 Value : 10000)
called init
Processing person range 238912
Person Type validation is passed
RT processing item : 300100181283432
Entering into FF CMP_BEN_TEST
Assignment ID 300100171603642
Eff Date 2019-12-31T00:00:00.000Z
Entering into FF CMP_BEN_TEST_1
Assignment ID 300100171603642
Eff Date 2019-12-31T00:00:00.000Z
BG Date 100010025071996
cvg date val 12-10-2018 /10000
Exiting into FF CMP_BEN_TEST_1 2018/10/12 /10000
RETURN VALUE OF CHILD 2018/10/12 / 10000
Exiting into FF CMP_BEN_TEST 2018/10/12 /10000
Creating employer statement : 100000011593283
Person Status: C
Hope this helps you, if you have any questions or suggestions please feel free to comment.
In our example, we use Total Compensation Item Formula, but you can use any Compensation or Grade Steps Formula as these CMP and GSP Fast Formula Types use the same Context.
In our example, we get Benefit Amount and Benefit Coverage Start Date of a plan using Benefit function, BEN_FN_GET_CHAR_VALUE and these values are used as Total Compensation Transaction Date and Total Compensation Transaction Values.
Can we use the Benefit Function, BEN_FN_GET_CHAR_VALUE in Compensation Fast Formula?
No, we cannot use the Benefit Function, BEN_FN_GET_CHAR_VALUE in any Compensation Fast Formula. The Function, BEN_FN_GET_CHAR_VALUE uses the context, HR_ASSIGNMENT_ID, EFFECTIVE_DATE and BUSINESS_GROUP_ID. Unfortunately, Compensation Fast Formula Types do not support the context, BUSINESS_GROUP_ID therefore we cannot use any Benefit DBI or Function in Compensation. Most of the BEN DBI and Functions uses the context, BUSINESS_GROUP_ID.
How do we use Benefit Function or DBI in Compensation Fast Formula?
The only possible way is to use the parent and child Fast Formula concept where you can use the Benefit Formula as a child Formula. The Benefit Formula may use different Context so that understanding of the Benefit Formula type is crucial before start using it. Once you know the DBI or Function and their context, the Benefit Formula can be selected as per your requirement (need of the Contexts). The value for the context can be passed from the parent (Compensation) Formula and you need to make sure the contexts value that are passed to the child Formulas are available in Compensation before calling the Child (Benefit) Formula (for example, Business Group Id).
How we implemented the COMP and BEN Formula:
We too implemented our Formula using parent and child Formula concept. As we said we are planning to use Benefit Function, BEN_FN_GET_CHAR_VALUE. This Benefit Function uses the contexts, HR_ASSIGNMENT_ID, EFFECTIVE_DATE and BUSINESS_GROUP_ID, and different parameters as per the expected values.
Context: Compensation Fast Formula types supports HR_ASSIGNMENT_ID, EFFECTIVE_DATE and BUSINESS_GROUP_ID is not a dynamic value and it is the same to the entire Organization so that we decided to hardcode the value to pass as context to the child Formula.
Parameters: For our requirement we need the parameters, Table name, Column name, Plan name and Option name. Since we know the above value, we are hard coding the parameters values too.
Table name => BEN_PRTT_ENRT_RSLT
Column name => BNFT_AMT/ENRT_CVG_STRT_DT
Plan Name => BEN_AUTO_DEP_DESG_PLAN
Option Name => AUTODESGEMP
Child Formula (Benefit):
/***************************
Formula Type : Age Calculation
Formula Name : CMP_BEN_TEST_1
Returns: Coverage Start Date and Benefit Amount
*******************************/
l_val = SET_LOG('Entering into FF CMP_BEN_TEST_1')
RET_DATE = 'N'
RET_VAL = 'N'
RET_DATE_CHAR = 'N'
L_ASG_ID = GET_CONTEXT(HR_ASSIGNMENT_ID, -1)
L_EFF_DATE = get_context(EFFECTIVE_DATE, '1900/01/01 00:00:00' (date))
L_BG_ID = GET_CONTEXT(BUSINESS_GROUP_ID, -1)
l_val = SET_LOG('Assignment ID ' || to_char(L_ASG_ID) )
l_val = SET_LOG('Eff Date ' || to_char(L_EFF_DATE) )
l_val = SET_LOG('BG Date ' || to_char(L_BG_ID) )
RET_DATE_CHAR = BEN_FN_GET_CHAR_VALUE ('BEN_PRTT_ENRT_RSLT', 'ENRT_CVG_STRT_DT' , 'BEN_AUTO_DEP_DESG_PLAN', 'AUTODESGEMP' )
RET_VAL = BEN_FN_GET_CHAR_VALUE ('BEN_PRTT_ENRT_RSLT', 'BNFT_AMT' , 'BEN_AUTO_DEP_DESG_PLAN', 'AUTODESGEMP' )
l_val = SET_LOG('cvg date val ' || RET_DATE_CHAR || ' /' || RET_VAL)
/* Date expected to be in yyyy-mm-dd format string
And we change that to yyyy/mm/dd format
*/
IF (LENGTH(RET_DATE_CHAR) = 10) THEN (
RET_DATE = TO_CHAR( TO_DATE( RET_DATE_CHAR), 'YYYY/MM/DD')
)
l_val = SET_LOG('Exiting into FF CMP_BEN_TEST_1 ' || RET_DATE || ' /' || RET_VAL)
RETURN RET_DATE , RET_VAL
Parent Formula (Total Compensation)
/***************************
Formula Type : Total Compensation Item
Formula Name : CMP_BEN_TEST
Returns: Coverage Start Date and Benefit Amount
*******************************/
l_val = SET_LOG('Entering into FF CMP_BEN_TEST')
COMPENSATION_DATES = '2000/01/01'
VALUES = '-1'
L_ASG_ID = GET_CONTEXT(HR_ASSIGNMENT_ID, -1)
L_EFF_DATE = get_context(EFFECTIVE_DATE, '1900/01/01 00:00:00' (date))
l_val = SET_LOG('Assignment ID ' || to_char(L_ASG_ID) )
l_val = SET_LOG('Eff Date ' || to_char(L_EFF_DATE) )
IF (IS_EXECUTABLE('CMP_BEN_TEST_1')) THEN
(
SET_INPUT('BUSINESS_GROUP_ID', 100010025071996)
EXECUTE('CMP_BEN_TEST_1')
COMPENSATION_DATES = GET_OUTPUT('RET_DATE','2000/01/02')
VALUES = GET_OUTPUT('RET_VAL','-2')
L_DATA = SET_LOG( ' RETURN VALUE OF CHILD '|| COMPENSATION_DATES || ' / '|| VALUES )
)
l_val = SET_LOG('Exiting into FF CMP_BEN_TEST ' || COMPENSATION_DATES || ' /' || VALUES)
RETURN COMPENSATION_DATES, VALUES
Since the above formulas are simple and at this point, we don’t think that the Formulas needed any explanation.
We use SET_LOG instead of ESS_LOG_WRITE. This set_log is compensation function this is an alternate to ESS_LOG_WRITE. Advantage of SET_LOG is, if you use the Formula validator/tester in Compensation, this will be useful.
ESS Monitor from Compensation:
The Ess Log provides the output values (Date: 2018/10/12 Value : 10000)
called init
Processing person range 238912
Person Type validation is passed
RT processing item : 300100181283432
Entering into FF CMP_BEN_TEST
Assignment ID 300100171603642
Eff Date 2019-12-31T00:00:00.000Z
Entering into FF CMP_BEN_TEST_1
Assignment ID 300100171603642
Eff Date 2019-12-31T00:00:00.000Z
BG Date 100010025071996
cvg date val 12-10-2018 /10000
Exiting into FF CMP_BEN_TEST_1 2018/10/12 /10000
RETURN VALUE OF CHILD 2018/10/12 / 10000
Exiting into FF CMP_BEN_TEST 2018/10/12 /10000
Creating employer statement : 100000011593283
Person Status: C
Hope this helps you, if you have any questions or suggestions please feel free to comment.
