Grade Steps Progression: Incremental salary Formula Sample

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.