Thursday, September 19, 2019

Benefit Data in Compensation Formula



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.

8 comments:

  1. good information thank you for sharing your knowledge
    hyderabad

    ReplyDelete
  2. Hi Tilak and Lakshmi,

    Need your help. First time I am working on fast formula. I have written an enrollment opportunity fast formula and attached it to change beneficiaries life event in supplemental life option. Formula is very simple. It is returing 'N' in L_ENRT_OPP. Now when employee is going to self service, employee is still able to elect this plan option. As per formula this option should not be electable for employee.

    Any help is highly appreciated.

    Thanks,
    prachya

    ReplyDelete
    Replies
    1. provide the exact name of the formula type

      Delete
  3. Hello

    I need a sample formula for automatic enrollment method.

    Thanks

    ReplyDelete
    Replies
    1. This is what I could find

      AUTO_DFLT_VAL = 'Y'
      CARRY_FORWARD_ELIG_DPNT = 'AUTO_PL_AUTO_DES_DEP'
      return AUTO_DFLT_VAL, CARRY_FORWARD_ELIG_DPNT

      This formula types support following contexts.

      DATE_EARNED
      EFFECTIVE_DATE
      BUSINESS_GROUP_ID
      ELIG_PER_ID
      HR_ASSIGNMENT_ID
      HR_RELATIONSHIP_ID
      HR_TERM_ID
      JOB_ID
      LEGAL_EMPLOYER_ID
      LEGISLATIVE_DATA_GROUP_ID
      LER_ID
      OPT_ID
      ORGANIZATION_ID
      PAYROLL_ASSIGNMENT_ID
      PAYROLL_RELATIONSHIP_ID
      PAYROLL_TERM_ID
      PERSON_ID
      PL_ID
      PL_TYP_ID
      PGM_ID

      Delete
  4. Hello,
    I have to write an ff in compensation that according to a specific value of a column in the budget worksheet(different value for every employee)makes some calculation. How can I take this value in the ff?? Is it necessary any sql logic according to id return the wanted value??

    Thankss

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete