Fast Formula – Compensation Example

Fast Formula – Compensation- A Real Life Scenario Example

In previous sections, we went over the basics of the Fast Formula, input values and return values of Fast Formula of Compensation product.

In this session, we will create our first real life Fast Formula for Compensation Formula Type, Compensation Default and Override (for CWB) and Total Compensation Item (for TCS).

To write such a Fast Formula, we need to have a real life use case. Therefore, we have taken a hypothetical case which very closes resembles a real life scenario and we will write a Fast Formula for Compensation Default and Override (CWB) and Total Compensation Item (TCS) with a little difference.

Use Case:

• We need to calculate the salary of an employee.
• The salary frequency is Monthly.
• We need to calculate the salary as of the first date of every month.
• The salary needs to be calculated for every month within the Plan/Statement Periods.
• We do not need to calculate the salary for the month if the first date of the month is not part of the period. For example, we do not calculate for January 2015 if the period start date is 10-January-2015.
• We do need to calculate the salary for the month if the start date of the month is before the period end date. For example, we will calculate for December 2015 if the period end date is 20-December-2015.
• If there is FTE (Full Time Equivalent Factor), we need to consider the FTE. For example, FTE .5 when a person works for 4 hours a day and the standard working hours are 8 per day.
• The salary could be defined in different frequencies. For example, Monthly, Yearly or Daily.   But the salary period is always defined as monthly. Therefore in this example, we will the Annual Salary for calculation.
• Compensation Default and Override (for CWB):  For CWB, in this example, we need to sum the amounts for the Plan Period and return one value. In case the salary amount has different currencies for the Plan Period , we do not calculate the sum but return -1.
• Total Compensation Item (for TCS): For TCS, In this example, we need to return the Salary value for every month. Since the value is returning on a monthly basis, we do not worry about the currency in Fast Formula.  

The following is our analysis before we start writing the formula:

1. We need the start and end date of the Period (Input Values)
2. We need to ascertain the start date of the period.  If the start date is not the first of the month, we need to calculate the salary from the first date of the next month.
3. We need to find the DBI for Annual Salary, Currency and FTE.
4. From the Annual salary, we need to derive the monthly salary.

Once we have the above information, we do the following:

• We loop through the dates from the start date to the end date of the period.
• We calculate the First date by using Add_Months Function.
• Within the loop, we set the effective date context to the first date of the month.
• We get the salary from Annual Salary DBI and divide the amount by 12 to get the monthly salary.
• If the FTE DBI value is not 1, then we multiply the salary amount with the FTE to derive the monthly average salary.
• For CWB, we calculate the sum of the monthly values. For TCS , we append the monthly values with a delimiter ‘;’.

CWB FORMULA:

/*

 Author  : Tilak

 Type    : Compensation Default and Override

 NAME    :   MY_FIRST_CWB_DEFAULT_FF

 Remarks: This is a first simple formula to explain the functionality of a Fast Formula. This does not have an advanced feature.

 Requirement: Calculate the sum of the salary as of the first date of the month within the period.

 DBI used:    CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT

              CMP_ASSIGNMENT_SALARY_CURRENCY_CODE

              PER_ASG_FTE_VALUE

*/

/*

Declare the DBI

*/

DEFAULT FOR  CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT IS 0

DEFAULT FOR  PER_ASG_FTE_VALUE IS 1

DEFAULT FOR  CMP_ASSIGNMENT_SALARY_CURRENCY_CODE IS 'XYZ'

/*

Declare the Input Values

*/

INPUTS ARE CMP_IV_PLAN_START_DATE (text), CMP_IV_PLAN_END_DATE (text)

l_hr_assign_id = get_context(HR_ASSIGNMENT_ID, -1)

L_DATA = ESS_LOG_WRITE( 'BEGIN MY_FIRST_CWB_DEFAULT_FF' )

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_DATA = ESS_LOG_WRITE( 'Asg ID     :' + TO_CHAR(l_hr_assign_id))

L_PL_START_DATE      = TO_DATE(CMP_IV_PLAN_START_DATE,'YYYY/MM/DD')

L_PL_END_DATE        = TO_DATE(CMP_IV_PLAN_END_DATE,'YYYY/MM/DD')

L_VALUE              =  0

l_CURR               =  ' '

/*

Determine the First date

*/

IF (L_PL_START_DATE <> ROUND(L_PL_START_DATE, 'MM')) THEN

(

  L_PL_START_DATE = TRUNC(ADD_MONTHS(L_PL_START_DATE, 1), 'MM')

  L_DATA = ESS_LOG_WRITE( 'New Start Date      :' + TO_CHAR( L_PL_START_DATE,'YYYY/MM/DD'))

  L_DATA = ESS_LOG_WRITE( 'New End Date      :' + TO_CHAR( L_PL_END_DATE,'YYYY/MM/DD'))  

)

WHILE (L_PL_START_DATE <= L_PL_END_DATE)

LOOP(

  L_DATA = ESS_LOG_WRITE( 'New Start Date in Loop     :' + TO_CHAR( L_PL_START_DATE,'YYYY/MM/DD'))

 CHANGE_CONTEXTS(EFFECTIVE_DATE = L_PL_START_DATE)

 (

   L_DATA = ESS_LOG_WRITE( 'Salary      :' + TO_CHAR( CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT ))

   IF (CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT WAS NOT DEFAULTED ) THEN

   (

     L_AMOUNT  =  CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT

      L_DATA = ESS_LOG_WRITE( 'Annual Salary      :' + TO_CHAR(L_AMOUNT))

      L_DATA = ESS_LOG_WRITE( 'FTE      :' + TO_CHAR(PER_ASG_FTE_VALUE))

      L_DATA = ESS_LOG_WRITE( 'CURRENCY      :' + CMP_ASSIGNMENT_SALARY_CURRENCY_CODE)

     IF (PER_ASG_FTE_VALUE WAS NOT DEFAULTED) THEN

     (

        L_AMOUNT  =  CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT * PER_ASG_FTE_VALUE

      )

      IF (l_CURR =  ' ') THEN

      (

         L_CURR = CMP_ASSIGNMENT_SALARY_CURRENCY_CODE

      )       

      IF ( L_CURR = CMP_ASSIGNMENT_SALARY_CURRENCY_CODE) THEN

      (

        L_VALUE = L_VALUE +  (L_AMOUNT/12)

        L_DATA = ESS_LOG_WRITE( 'CALC VALUE      :' + TO_CHAR(L_VALUE))

      )ELSE  

      (

        L_DATA = ESS_LOG_WRITE( 'CURRENCY MIS MATCH      :' )

       L_VALUE       = -1

      Exit

       )

      

   )

   

 )

 L_PL_START_DATE = ADD_MONTHS(L_PL_START_DATE, 1)

)

L_DATA = ESS_LOG_WRITE( 'END MY_FIRST_CWB_DEFAULT_FF' )  

RETURN  L_VALUE       

Explanation of the Fast Formula:

Once we determine the DBI, we need to declare and assign a default value to the DBI.  Since the Fast Formula does not support null values, we need to define the default value for the DBI in case it returns a null value to avoid any unknown issues.
         /*
            Declare the inputs and DBI

*/

DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT IS 0

We can also check the DBI value is null or not null using the syntax ‘WAS DEFAULTED’ and ‘WAS NOT DEFAULTED’.

Once the DBI is declared, we need to declare the Input Values.
We need to understand the type of input values passed. Some developers also default the Input Values. The product documentation provides the information about the input values and its types. Since this Fast Formula is attached to the formula type, Compensation Default and Override, we can see the input values passed to this one from section 3 under Input Values. We declare the CWB dates as text but in TCS we declare them as date.

INPUTS ARE CMP_IV_PLAN_START_DATE (text), CMP_IV_PLAN_END_DATE (text)

For this example, we have explained the main points of the business logic below.

• We determine if the start date of the plan is the start of the month and if not, we change the start date to the beginning of next month.

/*

Determine the Start Date

*/

IF (L_PL_START_DATE <> ROUND(L_PL_START_DATE, 'MM')) THEN

(

..

..

)

• We go through the loop for each month

WHILE (L_PL_START_DATE <= L_PL_END_DATE)

LOOP (

  L_DATA = ESS_LOG_WRITE( 'New Start Date in Loop     :' + TO_CHAR( L_PL_START_DATE,'YYYY/MM/DD'))

• We change the context to the beginning of the month using CHANGE_CONTEXTS. When we change the context, the DBI values are extracted as of the new context value.  The change context takes effect only within the brackets.

 CHANGE_CONTEXTS(EFFECTIVE_DATE = L_PL_START_DATE)

 (

   ……

   ……

   ……

)

And we get the salary amount using the DBI, CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT.

 CHANGE_CONTEXTS(EFFECTIVE_DATE = L_PL_START_DATE)

 (

   L_DATA = ESS_LOG_WRITE( 'Salary      :' + TO_CHAR( CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT ))

   IF (CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT WAS NOT DEFAULTED) THEN

   (

      L_AMOUNT  =  CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT

      L_DATA = ESS_LOG_WRITE( 'Annual Salary      :' + TO_CHAR(L_AMOUNT))

      L_DATA = ESS_LOG_WRITE( 'FTE      :' + TO_CHAR(PER_ASG_FTE_VALUE))

      L_DATA = ESS_LOG_WRITE( 'CURRENCY      :' + CMP_ASSIGNMENT_SALARY_CURRENCY_CODE)

• We check if the DBI, PER_ASG_FTE_VALUE has a null value (WAS NOT DEFAULTED) and if not null, we use this value to calculate the salary.

     IF (PER_ASG_FTE_VALUE WAS NOT DEFAULTED) THEN

     (

        L_AMOUNT  =  CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT * PER_ASG_FTE_VALUE

      )

• We check if the currency of the monthly salary amounts is same. If so, we calculate the sum of these values. Otherwise, we set the value to -1 and exit the loop.

      IF (l_CURR =  ' ') THEN

      (

         L_CURR = CMP_ASSIGNMENT_SALARY_CURRENCY_CODE

      )       

      IF ( L_CURR = CMP_ASSIGNMENT_SALARY_CURRENCY_CODE) THEN

      (

        L_VALUE = L_VALUE +  (L_AMOUNT/12)

        L_DATA = ESS_LOG_WRITE( 'CALC VALUE      :' + TO_CHAR(L_VALUE))

      )ELSE  

      (

        L_DATA = ESS_LOG_WRITE( 'CURRENCY MIS MATCH      :' )

       L_VALUE       = -1

      Exit

       )

   )

 )

• We set the start date to the next month and continue the loop till the period end date.

 L_PL_START_DATE = ADD_MONTHS(L_PL_START_DATE, 1)

)

The most important part is logging (debugging) the Fast Formula. You should try to add as much debug data as needed in the log. Once you feel the Fast Formula is working fine, you can comment them out. There are different logging mechanisms available: ESS_LOG_WRITE, HRTRACE etc. If the Formula is processed in ESS, we recommend using ESS_LOG_WRITE().

L_DATA = ESS_LOG_WRITE( 'BEGIN MY_FIRST_CWB_DEFAULT_FF' )

The final part is the return value. As we explained in the previous section, you must know what the Fast Formula is expected to return. From section 3, we know it can be any variable  for Compensation Default and Override Formula type.

RETURN  L_VALUE       

Once the CWB Fast Formula has been compiled successfully, you can attach the Fast Formula to the Attribute Column property.

You can process the CWB Plan in Compensation -> Run Batch Process -> Start Workforce Compensation Cycle.  Once the process is completed successfully, you can see the log messages in Compensation -> Run Batch Process -> Monitor process -> Log and output (Navigation may vary from release to release). By checking the log, you can easily debug the Fast Formula.  
The sample of log message from this process is given below:

TCS Formula:

/*

 Author  : Tilak

 Type    : Total Compensation Item

 NAME    :   MY_FIRST_TCS_ITEM_FF

 Remarks: This is a first simple formula to explain the functionality of Formula. This does not have an advanced feature.

 Requirement: Calculate the Salary as of first date of the months within the period and sum the salary.

 DBI used:    CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT

              CMP_ASSIGNMENT_SALARY_CURRENCY_CODE

              PER_ASG_FTE_VALUE

*/

/*Declare the inputs and DBI */

DEFAULT FOR  CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT IS 0

DEFAULT FOR  PER_ASG_FTE_VALUE IS 1

DEFAULT FOR  CMP_ASSIGNMENT_SALARY_CURRENCY_CODE IS 'XYZ'

INPUTS ARE CMP_IV_PERIOD_START_DATE (DATE) ,CMP_IV_PERIOD_END_DATE (DATE)

L_DATA = ESS_LOG_WRITE( 'BEGIN MY_FIRST_TCS_ITEM_FF' )

L_PL_START_DATE      = CMP_IV_PERIOD_START_DATE

L_PL_END_DATE        = CMP_IV_PERIOD_END_DATE

l_hr_assign_id = get_context(HR_ASSIGNMENT_ID, -1)

L_DATA = ESS_LOG_WRITE( 'Start Date   :' + TO_CHAR( L_PL_START_DATE,'YYYY/MM/DD'))

L_DATA = ESS_LOG_WRITE( 'END Date   :' + TO_CHAR( L_PL_END_DATE,'YYYY/MM/DD'))

L_DATA = ESS_LOG_WRITE( 'Asg ID     :' + TO_CHAR(l_hr_assign_id))

L_DATA = ESS_LOG_WRITE( ' start of Date Conversion')

COMPENSATION_DATES   =  ' '

VALUES               =  ' '

l_CURR               =  ' '

L_DATA = ESS_LOG_WRITE( ' Determine the First date')

/*Determine the First date*/

IF (L_PL_START_DATE <> ROUND(L_PL_START_DATE, 'MM')) THEN

(

  L_PL_START_DATE = TRUNC(ADD_MONTHS(L_PL_START_DATE, 1), 'MM')

  L_DATA = ESS_LOG_WRITE( 'New Start Date      :' + TO_CHAR( L_PL_START_DATE,'YYYY/MM/DD'))

)

L_DATA = ESS_LOG_WRITE( ' start of Date Loop')

WHILE (L_PL_START_DATE <= L_PL_END_DATE)

LOOP(

 L_DATA = ESS_LOG_WRITE( 'New Start Date   in loop    :' + TO_CHAR( L_PL_START_DATE,'YYYY/MM/DD'))

 CHANGE_CONTEXTS(EFFECTIVE_DATE = L_PL_START_DATE)

 (

  L_DATA = ESS_LOG_WRITE( 'ANN SALARY   :' + TO_CHAR( CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT))

   IF (CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT WAS NOT DEFAULTED ) THEN

   (

     L_AMOUNT  =  CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT

      L_DATA = ESS_LOG_WRITE( 'Annual Salary      :' + TO_CHAR(L_AMOUNT))

      L_DATA = ESS_LOG_WRITE( 'FTE      :' + TO_CHAR(PER_ASG_FTE_VALUE))

      L_DATA = ESS_LOG_WRITE( 'CURRENCY      :' + CMP_ASSIGNMENT_SALARY_CURRENCY_CODE)

     IF (PER_ASG_FTE_VALUE WAS NOT DEFAULTED) THEN

     (

        L_AMOUNT  =  CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT * PER_ASG_FTE_VALUE

      )

      IF (l_CURR =  ' ') THEN

      (

         L_CURR = CMP_ASSIGNMENT_SALARY_CURRENCY_CODE

      )       

      IF ( L_CURR = CMP_ASSIGNMENT_SALARY_CURRENCY_CODE) THEN

      (

        COMPENSATION_DATES   =   COMPENSATION_DATES + to_char(L_PL_START_DATE, 'YYYY/MM/DD') + ';'

         VALUES              =   VALUES + TO_CHAR(L_AMOUNT/12) + ';'

        L_DATA = ESS_LOG_WRITE( 'CALC VALUE      :' + TO_CHAR(L_AMOUNT))

      )ELSE  

      (

        L_DATA = ESS_LOG_WRITE( 'CURRENCY MIS MATCH      :' )

       )

      

   )

   

 )

 L_PL_START_DATE = ADD_MONTHS(L_PL_START_DATE, 1)

)

L_DATA = ESS_LOG_WRITE( 'END MY_FIRST_TCS_ITEM_FF' )  

RETURN  COMPENSATION_DATES ,VALUES

Total Compensation Item Fast Formula is added to the Total Compensation Item, when the Item type is Formula.

We can see the input values passed to this Fast Formula from section 3, under heading, Input Values. We declare the TCS period dates as Date because the value to the Input Value is passed in canonical format.

INPUTS ARE CMP_IV_PERIOD_START_DATE (DATE) ,CMP_IV_PERIOD_END_DATE (DATE)

Also, you can notice from the TCS Fast Formula that the return variables are defined as  “COMPENSATION_DATES” and  “VALUES”   and the values are added with delimiter, ‘;’ and the date values are added in YYYY/MM/DD format.

IF ( L_CURR = CMP_ASSIGNMENT_SALARY_CURRENCY_CODE) THEN

      (

        COMPENSATION_DATES   =   COMPENSATION_DATES + to_char(L_PL_START_DATE, 'YYYY/MM/DD') + ';'

         VALUES              =   VALUES + TO_CHAR(L_AMOUNT/12) + ';'

        L_DATA = ESS_LOG_WRITE( 'CALC VALUE      :' + TO_CHAR(L_AMOUNT))

      )ELSE  

…

…
RETURN  COMPENSATION_DATES ,VALUES

You may not be able to see the logs in the TCS Monitor page. Instead, you can see the logs of the TCS process from the CWB Monitor Page.
The sample of TCS log message from this process is given below:

Hope this has helped you with an understanding of a simple Fast Formula. We will go over more complex issues in the next section.

TCS Eligibility Fast Formula

Compensation You Tube Tutorial

/*

 Name: TCS_ELIG_FROMULA

Type:  Participation and Rate Eligibility

*/

DEFAULT FOR PER_ASG_REL_ORIGINAL_DATE_OF_HIRE is '2000/01/01' (date)

DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE is '2000/01/01' 

l_temp = ESS_LOG_WRITE('Entering TCS_ELIG_FROMULA')

ELIGIBLE = 'N' 

personid      = get_Context(PERSON_ID, -1) 

assignmentid  = get_context(HR_ASSIGNMENT_ID , -1) 

l_temp = ESS_LOG_WRITE('Person ' + to_char(personid) )

l_temp = ESS_LOG_WRITE('assignmentid ' + to_char(assignmentid) ) 

today = TO_DATE(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE, 'YYYY/MM/DD') 

doj    = PER_ASG_REL_ORIGINAL_DATE_OF_HIRE

l_temp = ESS_LOG_WRITE('Today  ' + to_char(Today) )

l_temp = ESS_LOG_WRITE('hire date ' + to_char(doj ) ) 

daydiff = DAYS_BETWEEN (today , doj) 

l_temp = ESS_LOG_WRITE('daydiff ' + to_char(daydiff ) ) 

if daydiff > ( 365 * 5) then (

   ELIGIBLE = 'Y'

) 

l_temp = ESS_LOG_WRITE('Leaving TCS_ELIG_FROMULA : ' + ELIGIBLE)

RETURN ELIGIBLE