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:
We need the start and end date of the Period (Input Values)
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.
We need to find the DBI for Annual Salary, Currency and FTE.
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.
/*
Determine the Start Date
*/
IF (L_PL_START_DATE <> ROUND(L_PL_START_DATE, 'MM')) THEN
(
..
..
)
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)
(
……
……
……
)
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)
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)
)
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
