We found that there is not much information available on GSP Formula Types. So, we have put together a good set of sample codes with a hypothetical use case that could help the GSP users.
The information provided in this blog may not be the latest or may be obsoleted. Please consult with your Product Support to get the latest information. The code provided here is to give an example.
We explained the contexts of GSP Formula Types in our previous blog and therefore we are not going to repeat the same again. All the CMP/TCS and GSP formulas types support the same Contexts.
Formula Type: Compensation Person Selection.
The type of formula used in ESS parameters is person Selection Formula.
This formula is executed while selecting the person for the process. The count of processed people is calculated after executing this formula and individual persons are processed after the person selection Formula is executed for all the people who were selected by other ESS parameters.
åå
Input value for Person Selection Formula for GSP process.
Input Value
|
Type
|
Format
|
CMP_IV_GRADE_LADDER_ID
|
Number
|
Since the Person Selection is common for the entire compensation product, you can use any example provided for CWB, TCS or GSP.
Formula Type: Participation and Rate Eligibility.
The Formula type belongs to Oracle Benefit and used in Oracle Benefit’s Eligibility. GSP uses the Benefit Eligibility Engine to determine the eligibility of a Grade Ladder, grades and steps for a person. Therefore, you need to create eligibility profile to limit /Filter the Grade Ladder, Grades or Steps.
Again, it is very common for CWB, TCS and GSP. Though it is common type, it uses different input values and business logic to validate a Ladder, Grade or Steps.
The most important aspects the eligibility formula is the Return value and Return variable. This type of formula supports the value ‘Y’ or ‘N’ and the return variable must be ELIGIBLE. If you return any other variable or in any other case format the Formula set the person/grade/step as ineligible.
The contexts Supported by the GSP eligibility Formulas
Context
|
Type
|
DATE_EARNED
|
Date
|
EFFECTIVE_DATE
|
Date
|
HR_ASSIGNMENT_ID
|
Number
|
JOB_ID
|
Number
|
LEGAL_EMPLOYER_ID
|
Number
|
LEGISLATIVE_DATA_GROUP_ID
|
Number
|
PAYROLL_ASSIGNMENT_ID
|
Number
|
PAYROLL_RELATIONSHIP_ID
|
Number
|
PAYROLL_TERM_ID
|
Number
|
PERSON_ID
|
Number
|
HR_TERM_ID
|
Number
|
Eligibility of Grade Ladder.
You can create eligibility profile at Ladder level. This formula is executed once for an assignment. If the Assignment is not eligible for the Ladder, the process does not evaluate the grades and steps. This is the First formula which gets executed at the person Level processing.
Input Values
|
Types
|
CMP_IV_GRADE_LADDER_ID
|
Number
|
CMP_IV_GRADE_LADDER_NAME
|
Text
|
Eligibility of Grade.
The formula is attached with the Grade eligibility profile and is executed for every person. The grade is not eligible then the Steps are not processed. The process is intelligent enough to process the grades that are above the current grade of the person and it continues the process till it finds the first ineligible grade.
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
|
Eligibility Step.
Steps are processed very similar to the grades.
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
|
The Eligibility is attached at Step level. No eligibility is defined at Grade Level or Ladder Level.
The person will move to next step for every process only when the person worked more than a year in the Organization. In case, if the person has worked more than three years in the Organization, then he is eligible for third step (Skip one step). The person is eligible for only one step in a process.
Create an eligibility profile with formula. (Formula is created before the eligibility profile)
Create Steps rule with eligibility profile
Formula text of Eligibility.
/*
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'
/* 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))
/* Local Variables */
L_1_ANIV = ADD_DAYS(PER_PER_LATEST_REHIRE_DATE, 365)
L_3_ANIV = ADD_DAYS(PER_PER_LATEST_REHIRE_DATE, 1095 )
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('First Anniversary : ' + to_char(L_1_ANIV) )
L_temp = ESS_LOG_WRITE('Third Anniversary : ' + to_char(L_3_ANIV) )
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 ) )
/* 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
(
/* 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 = '4') 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 Formula.
/* 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'Initialized the DBI that is going to be used. The hiring date is used for calculating the Anniversary and Step name is used for comparing the steps to determine the eligible step.
/* 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)
Initialized the Input values. Though we do not use all the input values, we initialized all the Input values for our example.
/* 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))
The variables are initialized from the context. We use effective date to calculate and compare the years of experience. The Assignment Id is used to find the previous eligibility.
/* Local Variables */
L_1_ANIV = ADD_DAYS(PER_PER_LATEST_REHIRE_DATE, 365)
L_3_ANIV = ADD_DAYS(PER_PER_LATEST_REHIRE_DATE, 1095 )
L_ELIG = 'N'
ELIGIBLE = 'N'
Required local variables are initialized or calculated. First year and Third year anniversary date are calculated from effective Date. L_elig is defaulted to ‘N’ and used to determine if the assignment is already eligible for a step and ELIGIBLE for returning the result (please note that this type of formula supports only the return variable ‘ELIGIBLE’).
/* 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 ) )
/* 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
To validate if a person is already eligible, we check the variable/value from WSA (to understand more about WSA please read out blog, Communicating between Fast Formulas), if the WSA variable matches with the assignment id, we set the l_elig variable to ‘Y’. Since the person is already eligible for a step, we do not validate further, we return the default value of ELIGIBLE that is ‘N’.
/* 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'
)
)
If the person has worked for more than 3 years, we check the person’s current step (DBI PER_GRADE_STEP_NAME) is ‘1’ and the Formula validates step (Input value, CMP_IV_STEP_NAME) is ‘3’ (third step from the current step ) or current step is 2. And the validated step is 4, then we set the ELIGIBLE variable to ‘Y’ (otherwise the default value, ‘N’).
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 = '4') THEN
(
ELIGIBLE = 'Y'
)
)
)
If the person worked more than 1 year and less than 3 years, we check the current step (DBI PER_GRADE_STEP_NAME) and the validated step (Input value, CMP_IV_STEP_NAME) is the next step (Example: Current step is 1 and next step is 2 ) then we set the ELIGIBLE variable to ‘Y’ (otherwise the default value, ‘N’).
/* if eligible store the value */
IF (ELIGIBLE = 'Y' ) THEN
(
WSA_SET('WSA_GSP_ELIG_PERSON', L_ASG_ID)
)
If the person is eligible then we set the WSA variable to the assignment id so that we do not need to validate other steps.
RETURN ELIGIBLE
In the End, We return the ELIGIBLE value (Y/N.
I don’t think logs need any explanation, please comment if you need any detail on the log .
Log of 1 year experienced Person, eligible for Step 2 .
***Entering Tilak_Lakshmi_Eligbility_Elig_FF***
Person ID : 300100167151081
Asg ID : 300100167151144
Effective date : 2015-07-23T00:00:00.000Z
First Anniversary : 2015-07-23T00:00:00.000Z
Third Anniversary : 2017-07-22T00:00:00.000Z
Current Step : 1
Validating STEP : 2
Elig ASG ID : -1
Assignment has worked more then 1 year
Leaving Tilak_Laskshmi_Eligbility_Elig_FF : Y
***Entering Tilak_Lakshmi_Eligbility_Elig_FF***
Person ID : 300100167151081
Asg ID : 300100167151144
Effective date : 2015-07-23T00:00:00.000Z
First Anniversary : 2015-07-23T00:00:00.000Z
Third Anniversary : 2017-07-22T00:00:00.000Z
Current Step : 1
Validating STEP : 3
Elig ASG ID : 300100167151144
Assignment is already eligible for a Step
Leaving Tilak_Laskshmi_Eligbility_Elig_FF : N
Ineligible assignment_id 300100167151144: Reason ERL
***Entering Tilak_Lakshmi_Eligbility_Elig_FF***
Person ID : 300100167151081
Asg ID : 300100167151144
Effective date : 2015-07-23T00:00:00.000Z
First Anniversary : 2015-07-23T00:00:00.000Z
Third Anniversary : 2017-07-22T00:00:00.000Z
Current Step : 1
Validating STEP : 4
Elig ASG ID : 300100167151144
Assignment is already eligible for a Step
Leaving Tilak_Laskshmi_Eligbility_Elig_FF : N
Log of 3 year experienced Person, not eligible for step 2 but 3.
Processing Person : FFTEST, Tilak-Lakshmi
***Entering Tilak_Lakshmi_Eligbility_Elig_FF***
Person ID : 300100167099668
Asg ID : 300100167099683
Effective date : 2015-07-23T00:00:00.000Z
First Anniversary : 2002-07-23T00:00:00.000Z
Third Anniversary : 2004-07-22T00:00:00.000Z
Current Step : 1
Validating STEP : 2
Elig ASG ID : 300100167151144
Assignment has worked more then 3 years
Leaving Tilak_Laskshmi_Eligbility_Elig_FF : N
Ineligible assignment_id 300100167099683: Reason ERL
***Entering Tilak_Lakshmi_Eligbility_Elig_FF***
Person ID : 300100167099668
Asg ID : 300100167099683
Effective date : 2015-07-23T00:00:00.000Z
First Anniversary : 2002-07-23T00:00:00.000Z
Third Anniversary : 2004-07-22T00:00:00.000Z
Current Step : 1
Validating STEP : 3
Elig ASG ID : 300100167151144
Assignment has worked more then 3 years
Leaving Tilak_Laskshmi_Eligbility_Elig_FF : Y
***Entering Tilak_Lakshmi_Eligbility_Elig_FF***
Person ID : 300100167099668
Asg ID : 300100167099683
Effective date : 2015-07-23T00:00:00.000Z
First Anniversary : 2002-07-23T00:00:00.000Z
Third Anniversary : 2004-07-22T00:00:00.000Z
Current Step : 1
Validating STEP : 4
Elig ASG ID : 300100167099683
Assignment is already eligible for a Step
Leaving Tilak_Laskshmi_Eligbility_Elig_FF : N
This is end of the Eligibility formula. We will cover the Rate and Date formulas
in our next blog.
As usual, if you have any questions or suggestions, please feel free to comment.
As usual, if you have any questions or suggestions, please feel free to comment.
You Tube Tutorial:
GSP Person Selection Formula 1 - Oracle Fusion HCM Fast Formula Tutorial-8
/*
Name : GSP_PERSON_SELECTION
Type. : Compensation Person Selection
Purpose : to filter the person sho are in probation period or already in step 5 or more
************************************************/
/* DBI */
DEFAULT FOR PER_GRADE_STEP_SEQUENCE IS -1
DEFAULT FOR PER_ASG_DATE_PROBATION_END IS '1900/01/01 00:00:00' (date)
/* input */
INPUTS ARE CMP_IV_GRADE_LADDER_ID (NUMBER)
l_data = SET_LOG(' Entering GSP_PERSON_SELECTION')
RETVAL = 'Y'
grade = GET_CONTEXT(GRADE_ID , -1)
eDate = GET_CONTEXT(EFFECTIVE_DATE, '1900/01/01 00:00:00' (date) )
l_data = SET_LOG(' Grade ID ' || TO_CHAR(grade) )
l_data = SET_LOG(' EFF DATE ' || TO_CHAR(eDate) )
l_data = SET_LOG(' STEP SEQ ' || TO_CHAR(PER_GRADE_STEP_SEQUENCE) )
l_data = SET_LOG(' PROB DATE ' || TO_CHAR(PER_ASG_DATE_PROBATION_END) )
/* step is < 5 * */
IF (PER_GRADE_STEP_SEQUENCE >= 5 ) THEN (
RETVAL = 'N'
l_data = SET_LOG(' Failed in step ' )
)
/* probation validation */
IF (RETVAL = 'Y' ) THEN (
IF ( PER_ASG_DATE_PROBATION_END >= eDate ) THEN (
RETVAL = 'N'
l_data = SET_LOG(' Failed in prob ' )
)
)
l_data = SET_LOG(' Leaving GSP_PERSON_SELECTION ' || RETVAL )
RETURN RETVAL
