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.
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'
)
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 */
/* 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.
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 */
/* 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.
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.
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.
/* 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)
)
)
|
Since the DBI are array based,
we make sure the Array has values; once the array length is determined, we loop
through the array. Since all these DBI
are coming from same Route, since they share the same Index, we do not need to
validate the index again.
The value of the array validated against the Assignment and Seniority Lookup codes, if they match we get the seniority Date from the DBI. These DBI are extracted for an effective date, we do not expect more than one value for a Seniority date setup. Once we get the seniority date, we calculate the 3 year and 1 year anniversary date from the seniority date and these anniversaries are validate later to determine the eligibility of the step.
The value of the array validated against the Assignment and Seniority Lookup codes, if they match we get the seniority Date from the DBI. These DBI are extracted for an effective date, we do not expect more than one value for a Seniority date setup. Once we get the seniority date, we calculate the 3 year and 1 year anniversary date from the seniority date and these anniversaries are validate later to determine the eligibility of the step.
Fast Formula Log.
***Entering Tilak_Lakshmi_Eligbility_Elig_FF***
Elig ASG ID : -1
The Logs are self explanatory,
we are are not getting into the details of the logs .
Derived Factors and Seniority Dates.
In case you do not want to use
the fast formula to validate the seniority dates/length of the status, the
Eligibility module has provide an option to use the seniority module in their
Derived Factor. Since we are limiting the
blogs scope to Fast Formula, we do not want to get into the details of the
Derived Factors setup but the image below is self-explanatory to understand the
setup.
Derived Factor Setup:
Eligibility with Derived Factors.
Hope this blog will help you
in understanding the Fast Formula with Seniority date. As always, we look
forward to hearing from you.
