Oracle HCM Fast Formula : Grade Steps Progressions (GSP) Fast Formula and Seniority Dates.

In our previous blogs, we discussed about the Eligibility, Rate and Date Fast Formulas for GSP.
In this blog, we will continue discussing with GSP Eligibility and Seniority Dates.

Seniority Dates: Seniority date is the date on which the person status/attribute changes started. In other words, it is the latest effective start date of a person’s attributes like Position, Job, Grade or Steps etc. It could be more complicated with adjustment but for the purpose of GSP, we are not getting into the details.

This Seniority date helps to find the length of time the person remained or worked on a status without further changes. For example, if we need to find the number of days a person has worked in a Step or Grade, we need to know the start date of the Step or Grade changes. This is what we call as Seniority Date.

Many times, we see that the eligibility of a Step or Grade depends on the number of days, months or hours the person has worked on the current Step or Grade. For example, a person is eligible for the next step only after 6 months of previous step changes.

In this blog, we discuss the different possible approaches that are available to meet the requirements and the strengths and the limitations of each of these approaches.

Fast Formula: If the requirement is simple like if a person is eligible for a Step or if the person spends 6 or more months on current Step, we can write a formula where we can change the context of EFFECTIVE_DATE to prior to 6 months and get the Step Name. If the step name is same as the current Step, then we can make the Person eligible for the next step.

Formula Type: Participation and Rate Eligibility

/* DBI Used in the FF */

DEFAULT FOR PER_GRADE_STEP_NAME IS '-1'

L_temp = ESS_LOG_WRITE('***Entering Tilak_Lakshmi_Eligbility_Elig_FF***')

/* Get context Values */

L_PERSON_ID = GET_CONTEXT(PERSON_ID, -1)

L_ASG_ID = GET_CONTEXT(HR_ASSIGNMENT_ID, -1)

L_EFF_DATE = get_context(EFFECTIVE_DATE, '1900/01/01 00:00:00' (date))

/* Local Variables */

ELIGIBLE = 'N'

l_current_step = PER_GRADE_STEP_NAME

l_Prev_step = ' '

l_Prev_date = ADD_MONTHS(L_EFF_DATE, -6)

L_temp = ESS_LOG_WRITE('Person ID : ' + to_char(L_PERSON_ID) )

L_temp = ESS_LOG_WRITE('Asg ID : ' + to_char(L_ASG_ID) )

L_temp = ESS_LOG_WRITE('Effective date : ' + to_char(L_EFF_DATE) )

L_temp = ESS_LOG_WRITE('current Step : ' + l_current_step )

L_temp = ESS_LOG_WRITE('Effective date - 6 : ' + to_char(l_Prev_date) )

/* Change the context to 6 month past*/

CHANGE_CONTEXTS(EFFECTIVE_DATE=l_Prev_date)(

l_Prev_step = PER_GRADE_STEP_NAME

L_temp = ESS_LOG_WRITE('Prev Step : ' + l_Prev_step )

)

IF (l_current_step = l_Prev_step) THEN (

ELIGIBLE = 'Y'

)

L_temp = ESS_LOG_WRITE('Leaving Tilak_Laskshmi_Eligbility_Elig_FF : ' + ELIGIBLE)

RETURN ELIGIBLE

If the requirement is not that simple, we can make use of the Oracle HCM Seniority Module to calculate the seniority date.

HCM Seniority Date: HCM Seniority date calculation is supported for different attributes of a person to calculate the seniority date. As of now the following attributes are supported. Please check with your support to see the attributes that are supported.

Business Unit
Bargaining Unit  
Collective Agreement  
Country  
Department  
Enterprise
Grade  
Grade Step  
Job  
Legal Employer  
Location  
Position  
Union

Since the GSP is related to Steps and Grades, we will discuss the setup and usage of Steps seniority date for our example. The setup and usage of the seniority date are the same for all attributes so that you can apply the same steps to all the Seniority dates.

Definition of Seniority Date: Seniority date definition is based on the lookup code from the lookup type, ORA_PER_SENIORITY_ITEMS. We need to create our custom lookup code as a first step of Seniority Date definition. For our example, we create a lookup code, GSP_TL_STEP_CODE as follows.

Once the Lookup Code definition is done, we need to attach the seniority date attribute to the Lookup Code as follows.

Setup and Maintenance > Configure Seniority Dates

Once the Seniority date setup is done on the basis of Step, we need to generate Seniority date for the person. (Again for the purpose of GSP, we are using simplest possible setups. If you need to understand the Seniority setup details please refer the setup documents.)

Person Management -> Manage Employment -> (Menu)->Manage Seniority Dates.

If this is the first time, there may not be any data for the seniority. Run the ‘Recalculate Seniority’ to generate the date for your seniority setup. This will trigger the ESS process on the background to generate the data. Once the process is completed, you can see the seniority date for your setup.

If you want to process the Seniority date for more than one person, you can use the ESS process, Calculate Seniority Dates provided by the Seniority module.

Seniority dates and Fast Formula.

The Seniority Date module has provided enough DBIs to access the Seniority data. Our sample Formula helps you to understand the usage of Seniority Date DBIs with GSP Formulas. For our use case, we used the similar requirement from our eligibility blog. The Eligibility Example was based on the experience/Seniority date of the Organization/Length of Service. For this blog, we change the logic to validate the seniority date in a Step. If a person has worked three or more years in the current Step, the person will be eligible for the third Step. If a person has worked one to three years on the current Step, the person will be eligible for the next step; otherwise, the person is not eligible.

Formula Type: Participation and Rate Eligibility

/* DBI Used in the FF */

DEFAULT FOR PER_PER_LATEST_REHIRE_DATE IS '1900/01/01 00:00:00'(date)

DEFAULT FOR PER_GRADE_STEP_NAME IS '-1'

/* Seniority Date */

DEFAULT_DATA_VALUE FOR PER_SENDT_F_V3_LEVEL_OBJECT_ID is -1

DEFAULT_DATA_VALUE FOR PER_SENDT_F_V3_SENIORITY_DATE is '1901/01/01' (date)

DEFAULT_DATA_VALUE FOR PER_SENDT_F_V3_SENIORITY_DATE_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_Eligbility_Elig_FF***')

/* Get context Values */

L_PERSON_ID = GET_CONTEXT(PERSON_ID, -1)

L_ASG_ID = GET_CONTEXT(HR_ASSIGNMENT_ID, -1)

L_EFF_DATE = get_context(EFFECTIVE_DATE, '1900/01/01 00:00:00' (date))

L_1_ANIV = L_EFF_DATE

L_3_ANIV = L_EFF_DATE

/* Local Variables */

L_ELIG = 'N'

ELIGIBLE = 'N'

L_temp = ESS_LOG_WRITE('Person ID : ' + to_char(L_PERSON_ID) )

L_temp = ESS_LOG_WRITE('Asg ID : ' + to_char(L_ASG_ID) )

L_temp = ESS_LOG_WRITE('Effective date : ' + to_char(L_EFF_DATE) )

L_temp = ESS_LOG_WRITE('Current Step : ' + PER_GRADE_STEP_NAME )

L_temp = ESS_LOG_WRITE('Validating STEP : ' + CMP_IV_STEP_NAME )

/* Get the value from WSA */

L_ELIG_ASG = WSA_GET('WSA_GSP_ELIG_PERSON', -1)

L_temp = ESS_LOG_WRITE('Elig ASG ID : ' + TO_CHAR( L_ELIG_ASG ) )

/* Calculate the Seniority Date */

l_index = PER_SENDT_F_V3_SENIORITY_DATE.FIRST(-1)

L_temp = ESS_LOG_WRITE('First index of Seniority date : ' + TO_CHAR( l_index ) )

IF (l_index <> -1) THEN (

WHILE PER_SENDT_F_V3_SENIORITY_DATE.EXISTS(l_index)

LOOP (

/* When the assignment is the same and lookup code is GSP_TL_STEP_CODE get the date */

L_temp = ESS_LOG_WRITE('Seniority Object ID : ' + TO_CHAR( PER_SENDT_F_V3_LEVEL_OBJECT_ID[l_index] ) )

L_temp = ESS_LOG_WRITE('Seniority Date Code : ' + PER_SENDT_F_V3_SENIORITY_DATE_CODE[l_index] )

IF (PER_SENDT_F_V3_LEVEL_OBJECT_ID[l_index] = L_ASG_ID AND PER_SENDT_F_V3_SENIORITY_DATE_CODE[l_index]='GSP_TL_STEP_CODE' ) THEN (

L_1_ANIV = ADD_DAYS(PER_SENDT_F_V3_SENIORITY_DATE[l_index], 365)

L_3_ANIV = ADD_DAYS(PER_SENDT_F_V3_SENIORITY_DATE[l_index], 1095 )

L_temp = ESS_LOG_WRITE('Seniority Date : ' + TO_CHAR( PER_SENDT_F_V3_SENIORITY_DATE[l_index] ) )

L_temp = ESS_LOG_WRITE('First Anniversary : ' + to_char(L_1_ANIV) )

L_temp = ESS_LOG_WRITE('Third Anniversary : ' + to_char(L_3_ANIV) )

EXIT

)

l_index = PER_SENDT_F_V3_SENIORITY_DATE.NEXT(l_index, -1)

)

/* IF the asg already eligible */

IF (L_ELIG_ASG = L_ASG_ID ) THEN

(

L_temp = ESS_LOG_WRITE('Assignment is already eligible for a Step' )

L_ELIG = 'Y'

)

/* not eligible yet */

IF (L_ELIG = 'N') THEN

(

/* Three year anniversary passed */

IF (L_3_ANIV <= L_EFF_DATE ) THEN

(

L_temp = ESS_LOG_WRITE('Assignment has worked more then 3 years' )

IF (PER_GRADE_STEP_NAME = '1' AND CMP_IV_STEP_NAME = '3') THEN

(

ELIGIBLE = 'Y'

)ELSE (

IF (PER_GRADE_STEP_NAME = '2' AND CMP_IV_STEP_NAME = '4') THEN

(

ELIGIBLE = 'Y'

)ELSE (

IF (PER_GRADE_STEP_NAME = '3' AND CMP_IV_STEP_NAME = '5') THEN

(

ELIGIBLE = 'Y'

)

) ELSE

(

/* One year anniversary */

L_temp = ESS_LOG_WRITE('Assignment has worked more then 1 year' )

IF (L_1_ANIV <= L_EFF_DATE ) THEN

(

IF (PER_GRADE_STEP_NAME = '1' AND CMP_IV_STEP_NAME = '2') THEN

(

ELIGIBLE = 'Y'

)ELSE (

IF (PER_GRADE_STEP_NAME = '2' AND CMP_IV_STEP_NAME = '3') THEN

(

ELIGIBLE = 'Y'

) ELSE (

IF (PER_GRADE_STEP_NAME = '3' AND CMP_IV_STEP_NAME = '5') THEN

(

ELIGIBLE = 'Y'

)

/* if eligible store the value */

IF (ELIGIBLE = 'Y' ) THEN

(

WSA_SET('WSA_GSP_ELIG_PERSON', L_ASG_ID)

)

L_temp = ESS_LOG_WRITE('Leaving Tilak_Laskshmi_Eligbility_Elig_FF : ' + ELIGIBLE)

RETURN ELIGIBLE

Explanation of Fast Formula.
Since the common logic is explained in the eligibility blog, we are not going to repeat the same here. We will go through the changes that are related to Seniority DBIs.

Formula Type: Participation and Rate Eligibility

/* DBI Used in the FF */

DEFAULT FOR PER_PER_LATEST_REHIRE_DATE IS '1900/01/01 00:00:00'(date)

DEFAULT FOR PER_GRADE_STEP_NAME IS '-1'

/* Seniority Date */

DEFAULT_DATA_VALUE FOR PER_SENDT_F_V3_LEVEL_OBJECT_ID is -1

DEFAULT_DATA_VALUE FOR PER_SENDT_F_V3_SENIORITY_DATE is '1901/01/01' (date)

DEFAULT_DATA_VALUE FOR PER_SENDT_F_V3_SENIORITY_DATE_CODE is ' '

For our Example we use only three DBIs from Seniority Date. These Seniority DBIs use Person_ID and Effective_Date as Contexts.
Level Object Id indicates the level where the seniority is defined. In our case it is Assignment.

If there is only one assignment Id for a person, you may not need this DBI. If you have more than one then you need to validate this DBI to identify the Assignment because the data are for a person.

If there is only one assignment Id for a person, you may not need this DBI. If you have more than one, then you need to validate this DBI to identify the Assignment because the data are for a person.

The DBI, PER_SENDT_F_V3_SENIORITY_DATE_CODE represents the lookup code. There might be more than one seniority date setup so this DBI helps validating the intended date code to make sure you are getting the correct seniority date.
Another DBI you can consider is PER_SENDT_F_V3_SENIORITY_FIELD_KEY. This stores the Step_id, in case if you want to validate the Step id with the Current Step Id of the person.