The Grade/Step Progression functionality supports updating the worker’s salary with the rate associated with the Grade/Step the worker is progressed to. GSP does not support incrementing the worker’s current salary with the grade/step rate amount defined.
Requirement:Consider the following scenario:
- Worker on Step 1 and current salary Rate is $5000.
- After a Length of Service of 1 year on Step 1, the worker must be progressed to Step 2. The rate defined at Step 2 is 300. The worker’s current salary should be incremented by $300.00 i.e. 5000+300 = 5300.
- After a Length of Service of 2 year on Step 2, the worker must be progressed to Step 3. The salary needs to be incremented by $500.00 i.e 5300+500 = 5800.
This allows the initial salary given to each worker can be different (in this case 5000). The rate defined at the step level is the increment amount and not the total salary rate. This requirement cannot be achieved directly by defining grade or step rates but can be achieved using a fast formula to calculate the increment amounts. The following are 2 possible solutions to accommodate the above requirement using fast formula.
Possible Solution 1:
In the fast Formula, find the current Grade / Step of the worker and the salary rate. For each possible grade or step, define the increment amount within the fast formula. Apply the increment amount to the current rate and calculate the new salary rate.
Find the current rate (say rate)
If CMP_IV_GRADE_NAME = ‘GRADE1’ and CMP_IV_STEP_NAME = ‘2’ then (
value = 300
)Else(
If CMP_IV_GRADE_NAME = ‘GRADE1’ and CMP_IV_STEP_NAME = ‘3’ then (
value = 500
) )
Then you can add the calculated value to the current grade rate
value = rate (Current Salary) + value
Possible Solution 2:
The incremental rate value in defined for each step in the ladder. Find the step rate in the formula and return the sum of worker’s current salary rate and step rate.
In this blog, we are going to discuss the possible solution 2.
Fast Formula:
/******************************************************************
FF Name: TILAK_GSP_SALARY_INCREMENT
Type: Salary Progression Rate Calculation Rule
Purpose: Get the current Salary from the Assignment and add the Rate from the GSP Steps To determine the incremented salary
Author: Tilak
******************************************************************//* DBI for the Fast Formula */
DEFAULT FOR PER_GRADE_STEP_NAME IS '-1'
DEFAULT_DATA_VALUE FOR PER_GSP_STEP_RATES_STEP_NAME is '-1'
DEFAULT_DATA_VALUE FOR PER_GSP_STEP_RATES_VALUE_VALUE IS '-1'
DEFAULT_DATA_VALUE FOR PER_GSP_STEP_RATES_STEP_ID IS -1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_AMOUNT is 0
/* Input Values */
INPUTS ARE CMP_IV_GRADE_LADDER_ID(NUMBER), CMP_IV_GRADE_ID (NUMBER), CMP_IV_GRADE_STEP_ID(NUMBER), GRADE_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'
DEFAULT FOR CMP_IV_GRADE_NAME IS '-1'
DEFAULT FOR CMP_IV_STEP_NAME IS '-1'
DEFAULT FOR CMP_IV_GRADE_STEP_ID IS -1
DEFAULT FOR GRADE_STEP_ID IS -1
L_temp = SET_LOG ('*** Entering TILAK_GSP_SALARY_INCREMENT ' )
l_salary = 0
l_eff_date = GET_CONTEXT(EFFECTIVE_DATE, '2000/01/10' (date))
l_grade_Id = GET_CONTEXT(GRADE_ID , -1 )
l_asg_Id = GET_CONTEXT(HR_ASSIGNMENT_ID , -1 )
/* print the contexts */
L_temp = SET_LOG ('Context Effective Date ' + to_char(l_eff_date) )
L_temp = SET_LOG ('Context Assignment ' + to_char(l_asg_Id) )
L_temp = SET_LOG ('Context Grade ' + to_char(l_grade_Id) )
/* print input values */
L_temp = SET_LOG ('IV Grade ' + to_char(CMP_IV_GRADE_ID) )
L_temp = SET_LOG ('IV Grade ' + CMP_IV_GRADE_NAME )
L_temp = SET_LOG ('IV Step ' + to_char(CMP_IV_GRADE_STEP_ID ) )
L_temp = SET_LOG ('IV Step ' + CMP_IV_STEP_NAME )
L_temp = SET_LOG ('GRADE_STEP_ID ' + TO_CHAR(GRADE_STEP_ID) )
L_Current_rate = 0
/* Find the current salary from DBI */
L_Current_rate = CMP_ASSIGNMENT_SALARY_AMOUNT
L_temp = SET_LOG ('current Salary Value in loop ' + TO_CHAR(L_Current_rate) )
l_rate_value = '0'
/* get the step rate value */
l_index = PER_GSP_STEP_RATES_VALUE_VALUE.FIRST(-1)
WHILE (PER_GSP_STEP_RATES_VALUE_VALUE.EXISTS(l_index))
LOOP(
/* You can have additional validation on dates */
L_temp = SET_LOG ('Rate Value in loop ' + PER_GSP_STEP_RATES_VALUE_VALUE[l_index] )
L_temp = SET_LOG ('Rate Value step id loop ' + to_char(PER_GSP_STEP_RATES_STEP_ID[l_index]) )
L_temp = SET_LOG ('Rate Value step name loop ' + PER_GSP_STEP_RATES_STEP_NAME[l_index] )
IF (PER_GSP_STEP_RATES_STEP_ID[l_index] = CMP_IV_GRADE_STEP_ID ) THEN ( L_temp = SET_LOG ('selected Rate Value in loop ' + PER_GSP_STEP_RATES_VALUE_VALUE[l_index] )
l_rate_value = PER_GSP_STEP_RATES_VALUE_VALUE[l_index
EXIT
)
l_index = PER_GSP_STEP_RATES_VALUE_VALUE.NEXT(l_index ,-1)
)
l_salary = L_Current_rate + to_number(l_rate_value)
L_temp = SET_LOG ('Exiting TILAK_GSP_SALARY_INCREMENT ***** ' + to_char(l_salary) )
RETURN l_salary
Partial Log from the Process:
Start Date : 2020-10-01
Start of the range process rangeid: 100000000844440 ,in the thread 24752
Effective date: 01-10-2020 , Assignment Id: 300000007387311
Processing Person : TilakAnalyst1119, Lakshmi
Grades Step Defaulted Eligible
------------------------------------------------------
Effective date: 01-11-2020 , Assignment Id: 300000007387311
*** Entering TILAK_GSP_SALARY_INCREMENT
Context Effective Date 2020-11-01T00:00:00.000Z
Context Assignment 300000007387311
Context Grade 300000006552581
IV Grade 300000006552581
IV Grade ANALYST Grade Step IC2\
IV Step 300000006552585
IV Step IC2.2
GRADE_STEP_ID 300000006552584
current Salary Value in loop 2101.11
Rate Value in loop 2101.11
Rate Value step id loop 300000006552584
Rate Value step name loop IC2.1
Rate Value in loop 2202.12
Rate Value step id loop 300000006552585
Rate Value step name loop IC2.2
selected Rate Value in loop 2202.12
Exiting TILAK_GSP_SALARY_INCREMENT ***** 4303.23
Rate from Formula:4303.23
Value / Factor / Annualized rate:4303.23 / 12 / 51638.76
salary/fte /ann fte / proposed salary 25213.32 / 2101.11 / 25213.32 / 51638.76
Proposed salary Fte 4303.23
Formatted proposed Salary 2 / / 4303.23
*** Entering TILAK_GSP_SALARY_INCREMENT
Context Effective Date 2020-11-01T00:00:00.000Z
Context Assignment 300000007387311
Context Grade 300000006552581
IV Grade 300000006552581
IV Grade ANALYST Grade Step IC2
IV Step 300000006552588
IV Step IC2.3
GRADE_STEP_ID 300000006552584
current Salary Value in loop 2101.11
Rate Value in loop 2101.11
Rate Value step id loop 300000006552584
Rate Value step name loop IC2.1
Rate Value in loop 2202.12
Rate Value step id loop 300000006552585
Rate Value step name loop IC2.2
Rate Value in loop 2303.13
Rate Value step id loop 300000006552588
Rate Value step name loop IC2.3
selected Rate Value in loop 2303.13
Exiting TILAK_GSP_SALARY_INCREMENT ***** 4404.24
Rate from Formula:4404.24
Explanation of the Fast Formula:
The following three DBI’s are array DBI’s based on context GRADE_ID and EFFECTIVE_DATE.
In other words, the information for the DBI is extracted for a Grade Id and a Date. By default, the Formula uses the person current grade and the effective date of the process.
DEFAULT_DATA_VALUE FOR PER_GSP_STEP_RATES_STEP_NAME is '-1'
DEFAULT_DATA_VALUE FOR PER_GSP_STEP_RATES_VALUE_VALUE IS '-1'
DEFAULT_DATA_VALUE FOR PER_GSP_STEP_RATES_STEP_ID IS -1
If you are using grades and grade rates, you can use the following DBI’s instead of Step based DBI’s.
PER_GRADE_RATES_NAME
PER_GRADE_RATE_VALUE_VALUE
The following DBI is used to get the employees current Salary. It is based on context HR_ASSIGNMENT_ID and EFFECTIVE_DATE.
DEFAULT FOR CMP_ASSIGNMENT_SALARY_AMOUNT is 0
These following lines are used to declare the Input values. You can find the information about the Grade, steps for which the Formula is executed.
/* Input Values */
INPUTS ARE CMP_IV_GRADE_LADDER_ID(NUMBER), CMP_IV_GRADE_ID (NUMBER), CMP_IV_GRADE_STEP_ID(NUMBER), GRADE_STEP_ID(NUMBER) ,
CMP_IV_GRADE_LADDER_NAME (TEXT), CMP_IV_GRADE_NAME (TEXT),CMP_IV_STEP_NAME (TEXT)
The following lines provide a default value to the Input values so that the formula does not error in case the values are not passed from the Fast formula.
DEFAULT FOR CMP_IV_STEP_NAME IS '-1'
DEFAULT FOR CMP_IV_GRADE_NAME IS '-1'
DEFAULT FOR CMP_IV_STEP_NAME IS '-1'
DEFAULT FOR CMP_IV_GRADE_STEP_ID IS -1
DEFAULT FOR GRADE_STEP_ID IS -1
The following lines are used to declaring variables for the current rate and eligible step rate.
l_salary = 0
L_Current_rate = 0
The following line is to find the person current salary value.
L_Current_rate = CMP_ASSIGNMENT_SALARY_AMOUNT
The following loop is to find the rate of all of steps in current grade. Since the DBI gets all the step rates, we need to validate the step that we are looking for.
If you are hard coding the value for Steps or Grade, you can implement your logic here instead of querying the value from the eligible Grades and Steps.
l_rate_value = '0'
/* get the step rate value */
l_index = PER_GSP_STEP_RATES_VALUE_VALUE.FIRST(-1)
WHILE (PER_GSP_STEP_RATES_VALUE_VALUE.EXISTS(l_index))
LOOP(
/* You can have additional validation on dates */
L_temp = SET_LOG ('Rate Value in loop ' + PER_GSP_STEP_RATES_VALUE_VALUE[l_index]
L_temp = SET_LOG ('Rate Value step id loop ' + to_char(PER_GSP_STEP_RATES_STEP_ID[l_index]) )
L_temp = SET_LOG ('Rate Value step name loop ' +PER_GSP_STEP_RATES_STEP_NAME[l_index] )
When the DBI step id matches the step id for which formula is executed, we get the value from the DBI.
IF (PER_GSP_STEP_RATES_STEP_ID[l_index] = CMP_IV_GRADE_STEP_ID ) THEN (
L_temp = SET_LOG ('selected Rate Value in loop ' + PER_GSP_STEP_RATES_VALUE_VALUE[l_index] )
l_rate_value = PER_GSP_STEP_RATES_VALUE_VALUE[l_index]
EXIT
)
l_index = PER_GSP_STEP_RATES_VALUE_VALUE.NEXT(l_index ,-1)
)
Once we get the current salary and the Step rate, we add them and return the final value.
l_salary = L_Current_rate + to_number(l_rate_value)
L_temp = SET_LOG ('Exiting TILAK_GSP_SALARY_INCREMENT ***** ' + to_char(l_salary) )
RETURN l_salary
This formula works on the person current grade.
If your requirement is to write a formula that evaluates for a different grade, you need to change the context of the Grade for which formula is executed.
/* get the step rate value */
CHANGE_CONTEXTS(GRADE_ID= CMP_IV_GRADE_ID)
(
l_index = PER_GSP_STEP_RATES_VALUE_VALUE.FIRST(-1)
WHILE (PER_GSP_STEP_RATES_VALUE_VALUE.EXISTS(l_index))
LOOP(
/* You can have additional validation on dates */
L_temp = SET_LOG ('Rate Value in loop ' + PER_GSP_STEP_RATES_VALUE_VALUE[l_index] )
L_temp = SET_LOG ('Rate Value step id loop ' + to_char(PER_GSP_STEP_RATES_STEP_ID[l_index]) )
L_temp = SET_LOG ('Rate Value step name loop ' + PER_GSP_STEP_RATES_STEP_NAME[l_index] )
IF (PER_GSP_STEP_RATES_STEP_ID[l_index] = CMP_IV_GRADE_STEP_ID ) THEN (
L_temp = SET_LOG ('selected Rate Value in loop ' + PER_GSP_STEP_RATES_VALUE_VALUE[l_index] )
l_rate_value = PER_GSP_STEP_RATES_VALUE_VALUE[l_index]
EXIT
)
l_index = PER_GSP_STEP_RATES_VALUE_VALUE.NEXT(l_index ,-1)
)
)
Hope this section helped you understand how to use Grade/Step Rates values for incrementing Salary.
This is only a sample formula and is meant to be a guideline. If you are using any of the above code, you need to test it to check if it matches your specific requirements.
