In our previous blog, we
explained the Eligibility Formula for GSP. In this blog, we will continue our discussion
with the Salary progression rate calculation Rule (GSP Rate), Salary
progression, Date determination and Rule (GSP Date).
Formula Type: Salary progression rate calculation Rule
(GSP Rate).
These Formulas allow you to
customize the rate for all the Grades and Steps in a Grade Ladder. The Formula is
defined at a Grade Ladder level and therefore the FF is executed for every
Grade/Step in the Ladder and the value from the Formula takes precedence to the
defined rate at Grade/Step level.
Note that the Formulas are not
returning the salary but the Grade Step Rate. The GSP process is using the
defined Annual Factors and FTE (Full Time Equivalent) to calculate the Salary
from the value returned by the Fast Formula.
Do not assume that the value returned from the Formula is used as it is.
The Formulas uses the same
context as any other Compensation Formula. Please see our previous blog on Compensation Formula
Types and Contexts.
Input Values:
|
Input
Values
|
Types
|
|
CMP_IV_GRADE_LADDER_ID
|
Number
|
|
CMP_IV_GRADE_LADDER_NAME
|
Text
|
|
CMP_IV_GRADE_ID
|
Number
|
|
CMP_IV_GRADE_NAME
|
Text
|
|
CMP_IV_STEP_ID
|
Number
|
|
CMP_IV_STEP_NAME
|
Text
|
Grade Ladder Setup with Rate
and Date Formula.
Formula use case For Calculating Rate:
As per our hypothetical use case, the Rate is calculated from a person’s current salary.
As per our hypothetical use case, the Rate is calculated from a person’s current salary.
· Get the Current Salary and Salary Frequencies from the
Salary setup using DBI.
· Get the Salary frequency from the Grade Ladder DBI, if the
Ladder’s frequency is not the same as Salary setup (you do not need to get it from
DBI as it is defined at ladder level, you can hard code the values), convert
the salary to Grade Ladder frequency.
· For each step, we add 2% of the original salary and the
percentage increased with every setup. For example if the current step is 1 and
Rate is calculated for Step 2, then we add 2% of salary, if Rate is calculated
for Step 3 then we add 4% salary and so on.
· As per our eligibility Rule,
the person is either eligible for next step or the following step after that, therefore
we use 2% or 4% hike.
· If there is any FTE defined, we need to convert the
rate with FTE because it is again applied in the process after the formula
returns the value.
Formula Type: Salary progression rate calculation Rule
/***********************************************************
Formula Type : Salary Progression Rate Calculation Rule
***********************************************************/
/* DBI FOR
Ladder Factor and Freq */
DEFAULT_DATA_VALUE FOR
PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR IS 1
DEFAULT_DATA_VALUE FOR
PER_ASG_GRADE_RATES_RATE_FREQUENCY is ' '
DEFAULT_DATA_VALUE FOR
PER_ASG_GRADE_RATES_NAME is ' '
DEFAULT_DATA_VALUE FOR
PER_GRADE_RATES_ANNUALIZATION_FACTOR IS 1
DEFAULT_DATA_VALUE FOR
PER_GRADE_RATES_RATE_FREQUENCY IS ' '
DEFAULT FOR PER_ASG_GRADE_LADDER_NAME
IS ' '
/* DBI FOR
Salary Information */
DEFAULT FOR CMP_ASSIGNMENT_SALARY_FTE_VALUE IS 1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_AMOUNT IS -1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR
IS -1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT IS
-1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_BASIS_CODE IS ' '
DEFAULT FOR PER_GRADE_STEP_NAME IS '-1'
/* Input Values */
INPUTS ARE CMP_IV_GRADE_LADDER_ID(NUMBER),
CMP_IV_GRADE_ID (NUMBER), CMP_IV_STEP_ID(NUMBER),
CMP_IV_GRADE_LADDER_NAME (TEXT), CMP_IV_GRADE_NAME
(TEXT),CMP_IV_STEP_NAME (TEXT)
DEFAULT FOR CMP_IV_STEP_NAME IS '-1'
L_temp = ESS_LOG_WRITE('*** Entering
Tilak_Lakshmi_GSP_RATE' )
L_temp = ESS_LOG_WRITE('Ladder name ' +
PER_ASG_GRADE_LADDER_NAME )
L_Grade_annual_factor = 12
L_Grade_Freq =
'MONTHLY'
/* find the current asg frequency
You do not need
this, since the FF written for a Ladder
*/
index =
PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR.FIRST(-1)
WHILE
(PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR.EXISTS(index))
LOOP (
L_temp =
ESS_LOG_WRITE('Grade Rate Name ' +
PER_ASG_GRADE_RATES_NAME[index] )
IF
(PER_ASG_GRADE_RATES_NAME[index] =PER_ASG_GRADE_LADDER_NAME) THEN (
L_Grade_annual_factor = PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR[index]
L_Grade_Freq =
PER_ASG_GRADE_RATES_RATE_FREQUENCY[index]
L_temp= ESS_LOG_WRITE(
' Grade Annualization Factor ' + to_char(L_Grade_annual_factor) )
L_temp=
ESS_LOG_WRITE( ' Grade Annualization Frequency
' + L_Grade_Freq )
EXIT
)
index =
PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR.NEXT(index ,-1)
)
L_temp= ESS_LOG_WRITE( '
CMP_ASSIGNMENT_SALARY_FTE_VALUE ' + to_char(CMP_ASSIGNMENT_SALARY_FTE_VALUE) )
L_temp= ESS_LOG_WRITE( '
CMP_ASSIGNMENT_SALARY_AMOUNT ' + to_char(CMP_ASSIGNMENT_SALARY_AMOUNT) )
L_temp= ESS_LOG_WRITE( ' CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR ' +
to_char(CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR) )
L_temp= ESS_LOG_WRITE( '
CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT '
+
to_char(CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT) )
L_temp= ESS_LOG_WRITE( '
CMP_ASSIGNMENT_SALARY_BASIS_CODE '
+ CMP_ASSIGNMENT_SALARY_BASIS_CODE )
L_temp= ESS_LOG_WRITE( ' PER_GRADE_STEP_NAME ' +
PER_GRADE_STEP_NAME )
L_temp= ESS_LOG_WRITE( ' Eligible STEP_NAME ' +
CMP_IV_STEP_NAME )
L_salary = CMP_ASSIGNMENT_SALARY_AMOUNT
/* if the freq of the grade and salary is not the same
*/
IF (L_Grade_Freq != CMP_ASSIGNMENT_SALARY_BASIS_CODE)
THEN (
L_salary =
CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT
/* Convert the
annual Salary to Grade rate */
IF
(L_Grade_annual_factor != 1) THEN (
L_salary =
L_salary / L_Grade_annual_factor
)
)
L_temp= ESS_LOG_WRITE( ' Grade Rate salary ' +
to_char(L_salary) )
/* Fte*/
IF (CMP_ASSIGNMENT_SALARY_FTE_VALUE WAS NOT DEFAULTED)
THEN (
L_salary =
L_salary / CMP_ASSIGNMENT_SALARY_FTE_VALUE
L_temp=
ESS_LOG_WRITE( ' Grade Rate salary '
+ to_char(L_salary) )
)
L_temp= ESS_LOG_WRITE( ' FTE Salary ' + to_char(L_salary) )
/* add % to salary */
IF ( (TO_NUMBER(CMP_IV_STEP_NAME) -
to_number(PER_GRADE_STEP_NAME)) > 1 ) THEN (
L_salary
=L_salary * 1.4
)ELSE (
L_salary
=L_salary * 1.2
)
L_temp= ESS_LOG_WRITE( ' Adjusted Salary ' + to_char(L_salary) )
L_temp = ESS_LOG_WRITE('Exiting Tilak_Lakshmi_GSP_RATE:
'+ TO_CHAR(L_salary) )
Return L_salary
Explanation
of Formula.
/* DBI FOR
Ladder Factor and Freq */
DEFAULT_DATA_VALUE FOR
PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR IS 1
DEFAULT_DATA_VALUE FOR
PER_ASG_GRADE_RATES_RATE_FREQUENCY is ' '
DEFAULT_DATA_VALUE FOR
PER_ASG_GRADE_RATES_NAME is ' '
DEFAULT_DATA_VALUE FOR
PER_GRADE_RATES_ANNUALIZATION_FACTOR IS 1
DEFAULT_DATA_VALUE FOR
PER_GRADE_RATES_RATE_FREQUENCY IS ' '
DEFAULT FOR PER_ASG_GRADE_LADDER_NAME
IS ' '
/* DBI FOR
Salary Information */
DEFAULT FOR CMP_ASSIGNMENT_SALARY_FTE_VALUE IS 1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_AMOUNT IS -1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR
IS -1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT IS
-1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_BASIS_CODE IS ' '
DEFAULT FOR PER_GRADE_STEP_NAME IS '-1'
Declaration and initialization
of Formula. We have declared more DBI than what we actually used in the
Formula.
/* Input Values */
INPUTS ARE CMP_IV_GRADE_LADDER_ID(NUMBER),
CMP_IV_GRADE_ID (NUMBER), CMP_IV_STEP_ID(NUMBER),
CMP_IV_GRADE_LADDER_NAME (TEXT), CMP_IV_GRADE_NAME (TEXT),CMP_IV_STEP_NAME
(TEXT)
DEFAULT FOR CMP_IV_STEP_NAME IS '-1'
Declaration of Input values. The
Step name alone initialized with default value because if the Ladder does not
uses the Step, we do not get any errors from using Step Name Input values.
L_temp = ESS_LOG_WRITE('*** Entering
Tilak_Lakshmi_GSP_RATE' )
L_temp = ESS_LOG_WRITE('Ladder name ' +
PER_ASG_GRADE_LADDER_NAME )
L_Grade_annual_factor = 12
L_Grade_Freq =
'MONTHLY'
/* find the current asg frequency
You do not need
this, since the FF written for a Ladder
*/
index =
PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR.FIRST(-1)
WHILE
(PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR.EXISTS(index))
LOOP (
L_temp =
ESS_LOG_WRITE('Grade Rate Name ' +
PER_ASG_GRADE_RATES_NAME[index] )
IF
(PER_ASG_GRADE_RATES_NAME[index] =PER_ASG_GRADE_LADDER_NAME) THEN (
L_Grade_annual_factor = PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR[index]
L_Grade_Freq =
PER_ASG_GRADE_RATES_RATE_FREQUENCY[index]
L_temp=
ESS_LOG_WRITE( ' Grade Annualization Factor
' +
to_char(L_Grade_annual_factor) )
L_temp=
ESS_LOG_WRITE( ' Grade Annualization Frequency
' + L_Grade_Freq )
EXIT
)
index =
PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR.NEXT(index ,-1)
)
Annualization Factor is
defaulted to 12 and Frequency is defaulted to Monthly. As this Formula is
created for a Ladder, we know in advance what is the Frequency and
Annualization Factor. Therefore, we do not need to find the information from
the DBI, we can use the hard coded values.
The code that finds the Frequency and Annualization factor do not work for this formula. This is just an example. The reason why it is not working is explained in the ‘Limitation of Formula’ section.
The code that finds the Frequency and Annualization factor do not work for this formula. This is just an example. The reason why it is not working is explained in the ‘Limitation of Formula’ section.
L_temp= ESS_LOG_WRITE( '
CMP_ASSIGNMENT_SALARY_FTE_VALUE ' + to_char(CMP_ASSIGNMENT_SALARY_FTE_VALUE) )
L_temp= ESS_LOG_WRITE( '
CMP_ASSIGNMENT_SALARY_AMOUNT ' + to_char(CMP_ASSIGNMENT_SALARY_AMOUNT) )
L_temp= ESS_LOG_WRITE( '
CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR
' +
to_char(CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR) )
L_temp= ESS_LOG_WRITE( '
CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT '
+
to_char(CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT) )
L_temp= ESS_LOG_WRITE( '
CMP_ASSIGNMENT_SALARY_BASIS_CODE '
+ CMP_ASSIGNMENT_SALARY_BASIS_CODE )
L_temp= ESS_LOG_WRITE( ' PER_GRADE_STEP_NAME ' +
PER_GRADE_STEP_NAME )
L_temp= ESS_LOG_WRITE( ' Eligible STEP_NAME ' +
CMP_IV_STEP_NAME )
L_salary = CMP_ASSIGNMENT_SALARY_AMOUNT
/* if the freq of the grade and salary is not the same
*/
IF (L_Grade_Freq != CMP_ASSIGNMENT_SALARY_BASIS_CODE)
THEN (
L_salary =
CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT
/* Convert the
annual Salary to Grade rate */
IF
(L_Grade_annual_factor != 1) THEN (
L_salary =
L_salary / L_Grade_annual_factor
)
)
Converting the salary to the
Grade Rate value.
L_temp= ESS_LOG_WRITE( ' Grade Rate salary ' +
to_char(L_salary) )
/* Fte*/
IF (CMP_ASSIGNMENT_SALARY_FTE_VALUE WAS NOT DEFAULTED)
THEN (
L_salary =
L_salary / CMP_ASSIGNMENT_SALARY_FTE_VALUE
L_temp=
ESS_LOG_WRITE( ' Grade Rate salary '
+ to_char(L_salary) )
)
L_temp= ESS_LOG_WRITE( ' FTE Salary ' + to_char(L_salary) )
Applying FTE to the Grade Rate
Value.
/* add % to salary */
IF ( (TO_NUMBER(CMP_IV_STEP_NAME) -
to_number(PER_GRADE_STEP_NAME)) > 1 ) THEN (
L_salary
=L_salary * 1.4
)ELSE (
L_salary
=L_salary * 1.2
)
L_temp= ESS_LOG_WRITE( ' Adjusted Salary ' + to_char(L_salary) )
Giving raise a per the
experience.
L_temp = ESS_LOG_WRITE('Exiting Tilak_Lakshmi_GSP_RATE:
'+ TO_CHAR(L_salary) )
Return L_salary
Returning the Final Value.
Limitation of the Formula:
1)
No DBI at Steps
level to find the rate is available.
2)
The sample code is
using the Grade level DBI to find the Rate and there is no rate defined at the
Grade level.
Log
*** Entering Tilak_Lakshmi_GSP_RATE
Ladder name Tilak-Lakshmi Test Ladder
Grade Rate Name
GSP Midwest Customer Support Grades
CMP_ASSIGNMENT_SALARY_FTE_VALUE .5
CMP_ASSIGNMENT_SALARY_AMOUNT 1001
CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR 12
CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT 12012
CMP_ASSIGNMENT_SALARY_BASIS_CODE MONTHLY
PER_GRADE_STEP_NAME 1
Eligible
STEP_NAME 2
Grade Rate
salary 1001
Grade Rate
salary 2002
FTE Salary 2002
Adjusted Salary
2402.4
Exiting Tilak_Lakshmi_GSP_RATE: 2402.4
Formula use case For Calculating Date:
Honestly, we were not able to find a good use case for
calculating the date. We are not sure if the following use case has any
practical purpose but it may help you to understand the basics of the Data
Calculation Fast Formula. We decided to determine the date from the Frequency
of the salary. If the Salary Frequency is Annual, the Date will be the first of
next year. If it is Monthly then the Date will be the first of next month.
Otherwise, the Effective date
will be returned.
Formula Type: Salary progression rate calculation Rule
/*********************************************
Formula Type: Salary
Progression Date Determination Rule
****************************************************/
DEFAULT FOR PER_ASG_GRADE_LADDER_NAME
IS ' '
/* DBI FOR
Salary Information */
DEFAULT FOR CMP_ASSIGNMENT_SALARY_BASIS_CODE IS ' '
/* Input Values */
INPUTS ARE CMP_IV_GRADE_LADDER_ID(NUMBER),
CMP_IV_GRADE_ID (NUMBER), CMP_IV_STEP_ID(NUMBER),
CMP_IV_GRADE_LADDER_NAME (TEXT), CMP_IV_GRADE_NAME
(TEXT),CMP_IV_STEP_NAME (TEXT)
L_temp = ESS_LOG_WRITE('*** Entering
Tilak_Lakshmi_GSP_DATE' )
L_temp = ESS_LOG_WRITE('Ladder name ' +
PER_ASG_GRADE_LADDER_NAME )
l_date =
GET_CONTEXT(EFFECTIVE_DATE, '2000/01/10' (date))
l_date_c = to_char(l_date, 'YYYY/MM/DD')
L_temp = ESS_LOG_WRITE('effective Date ' + l_date_c )
L_temp = ESS_LOG_WRITE('Salary Frequency ' + CMP_ASSIGNMENT_SALARY_BASIS_CODE )
/*Calculate the Date */
IF (CMP_ASSIGNMENT_SALARY_BASIS_CODE = 'ANNUALLY') THEN
(
L_DATE =
TRUNC(ADD_MONTHS(l_DATE, 12), 'YYYY')
)ELSE (
IF
(CMP_ASSIGNMENT_SALARY_BASIS_CODE = 'MONTHLY') THEN (
L_DATE =
TRUNC(ADD_MONTHS(l_DATE, 12), 'MM')
)
)
l_date_c = to_char(l_date, 'YYYY/MM/DD')
L_temp = ESS_LOG_WRITE('Exiting Tilak_Lakshmi_GSP_DATE:
'+l_date_c )
Return l_date_c
The Formula is pretty self-explanatory. If the Salary Frequency is Annually, 12
months is added to effective date and First date of the year is calculated from
the date. If the Frequency is Monthly, 1
month is added to the effective date and First date of the month is calculated
from the Date. The result is converted into ‘YYYY/MM/DD’ date format and the
value is returned.
Log
*** Entering Tilak_Lakshmi_GSP_DATE
Ladder name Tilak-Lakshmi Test Ladder
effective Date
2016/07/30
Salary Frequency
MONTHLY
Exiting Tilak_Lakshmi_GSP_DATE: 2016/08/01
This completes
the Rate and Date formulas. As usual, if you
have any questions or suggestions, please feel free to comment.
You Tube Tutorial:
GSP Person Selection Formula 2 - Oracle Fusion HCM Fast Formula Tutorial-9
/*
Name: GSP_PERSON_SELECTION2
Type: Compensation Person Selection
Purpose: Skip the person who is already in ceiling step
***************************/
/* DBI */
DEFAULT_DATA_VALUE FOR PER_GRD_GRADE_HISTORY_CEILING_STEP_ID IS -1
DEFAULT_DATA_VALUE FOR PER_GRD_GRADE_HISTORY_EFFECTIVE_START_DATE IS '1900/01/01 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_GRD_GRADE_HISTORY_EFFECTIVE_END_DATE IS '1900/01/01 00:00:00' (date)
DEFAULT FOR PER_GRADE_STEP_NAME IS ' '
/* input Values */
INPUTS ARE CMP_IV_GRADE_LADDER_ID (NUMBER)
l_data = set_log('Entering GSP_PERSON_SELECTION2' )
grade = GET_CONTEXT(GRADE_ID, -1)
eDate = GET_CONTEXT(EFFECTIVE_DATE, '1900/01/01 00:00:00' (date) )
l_data = set_log('Ladder ' || TO_CHAR(CMP_IV_GRADE_LADDER_ID) )
l_data = set_log('Grade ' || TO_CHAR(grade) )
l_data = set_log('EFF DATE ' || TO_CHAR(eDate) )
l_data = set_log('Person Grade Step Name ' || PER_GRADE_STEP_NAME )
/* Find the ceiling id of eff date */
RETVAL = 'Y'
l_step_id = -1
index = PER_GRD_GRADE_HISTORY_CEILING_STEP_ID.FIRST( -1)
WHILE (PER_GRD_GRADE_HISTORY_CEILING_STEP_ID.EXISTS(index))
LOOP (
lstDate = PER_GRD_GRADE_HISTORY_EFFECTIVE_START_DATE[index]
lenDate = PER_GRD_GRADE_HISTORY_EFFECTIVE_END_DATE[index]
l_data = set_log('CEILING Dates ' || TO_CHAR(lstDate) || ' / '|| TO_CHAR(lenDate))
if (edate >= lstDate AND edate <= lenDate ) THEN (
l_step_id = PER_GRD_GRADE_HISTORY_CEILING_STEP_ID[index]
l_data = set_log('Step ID ' || TO_CHAR(l_step_id) )
exit
)
index = PER_GRD_GRADE_HISTORY_CEILING_STEP_ID.NEXT(index, -1 )
)
/* eof loop */
IF ( l_step_id != -1 ) THEN (
lparam = '|=STEP_ID='|| TO_CHAR(l_step_id)
l_data = set_log('Person Grade Step Name ' || lparam )
l_step_name = GET_VALUE_SET('GSP_GET_STEP_NAME' , lparam)
l_data = set_log('Ceil Step Name ' || l_step_name )
IF ( PER_GRADE_STEP_NAME = l_step_name )THEN (
RETVAL = 'N'
)
)
l_data = set_log('Leaving GSP_PERSON_SELECTION2 ' || RETVAL )
RETURN RETVAL
Hi Tilak,
ReplyDeleteVery great job.
Please hlep me with basic syntax in Fast formula in Benefits module with simple example. This will help me/others to refer immediate and to understand basic.
Thanks
Jaya
Typically I never remark on online journals yet your article is convincing to the point that I never stop myself to say something regarding it. You’re doing a great job Man,Keep it up. "Reservation Hotel Booking" "Online Air Ticket Booking" "Cheap Hotel Reservations Online" "Online Discount Hotel Reservations" "Discount Hotel Reservations" "Hotel Booking Services" "Discount Airline Flights" "Affordable Vacation Packages" "Affordable Honeymoon Packages"
ReplyDeleteVery interesting blog Awesome post. your article is really informative
ReplyDeleteOracle Fusion HCM Online Training
Good information, thanks for sharing this blog.
ReplyDeleteOracle Fusion HCM Online Training
Good information, thanks for sharing this blog.
ReplyDeleteOracle Fusion HCM Online Training
useful information thanks for sharing your knowledge
ReplyDeletehyderabad