The Details of this Formula available in Youtube Tutorial:
GSP Grade|Steps Eligibility Formula - Oracle Fusion HCM Fast Formula Tutorial-12
Child Fast Formula
/*Formula Name: GSP_PERSON_CEILING_STEP
Type : Compensation Person Selection
Purpose: Check of the person is in the ceiling step
*/
DEFAULT FOR PER_GRADE_STEP_NAME IS ' '
DEFAULT FOR PER_GRADE_STEP_SEQUENCE IS -1
DEFAULT_DATA_VALUE FOR PER_GRD_GRADE_HISTORY_CEILING_STEP_ID IS -1
DEFAULT_DATA_VALUE FOR PER_GSP_STEP_RATES_STEP_NAME IS ' '
DEFAULT_DATA_VALUE FOR PER_GSP_STEP_RATES_STEP_SEQUENCE is -1
DEFAULT_DATA_VALUE FOR PER_GSP_STEP_RATES_STEP_ID IS -1
l_data = set_log('Entering GSP_PERSON_CEILING_STEP' )
grade = GET_CONTEXT(GRADE_ID, -1)
eDate = GET_CONTEXT(EFFECTIVE_DATE, '1900/01/01 00:00:00' (date) )
l_data = set_log('Grade ' || TO_CHAR(grade) )
l_data = set_log('EFF DATE ' || TO_CHAR(eDate) )
l_data = set_log('CURR STEP NAME ' || PER_GRADE_STEP_NAME )
l_data = set_log('CURR STEP SEQ ' || TO_CHAR(PER_GRADE_STEP_SEQUENCE) )
STEPID = -1
STEPNAME = ' '
STEPSEQ = -1
CEILSTEPID = -1
INCEILSTEP = 'N'
indx = PER_GRD_GRADE_HISTORY_CEILING_STEP_ID.FIRST(-1)
if indx != -1 THEN (
CEILSTEPID = PER_GRD_GRADE_HISTORY_CEILING_STEP_ID[indx]
)
IF CEILSTEPID != -1 THEN (
indx = PER_GSP_STEP_RATES_STEP_ID.FIRST(-1)
WHILE PER_GSP_STEP_RATES_STEP_ID.EXISTS(indx)
LOOP(
STEPID = PER_GSP_STEP_RATES_STEP_ID[INDX]
STEPNAME = PER_GSP_STEP_RATES_STEP_NAME[INDX]
STEPSEQ = PER_GSP_STEP_RATES_STEP_SEQUENCE[INDX]
l_data = set_log('STEP ' || TO_CHAR(STEPID) )
l_data = set_log('SEQ ' || TO_CHAR(STEPSEQ) )
l_data = set_log('NAME ' || STEPNAME )
IF CEILSTEPID = STEPID THEN (
l_data = set_log('FOUND CEILING ' || STEPNAME )
EXIT
)
indx = PER_GSP_STEP_RATES_STEP_ID.NEXT(indx, -1)
)
)ELSE (
indx = PER_GSP_STEP_RATES_STEP_ID.LAST(-1)
if indx != -1 THEN (
STEPID = PER_GSP_STEP_RATES_STEP_ID[INDX]
STEPNAME = PER_GSP_STEP_RATES_STEP_NAME[INDX]
STEPSEQ = PER_GSP_STEP_RATES_STEP_SEQUENCE[INDX]
l_data = set_log('LAST STEP ' || TO_CHAR(STEPID) )
l_data = set_log('LAST SEQ ' || TO_CHAR(STEPSEQ) )
l_data = set_log('LAST NAME ' || STEPNAME )
)
)
IF STEPID != -1 THEN (
IF PER_GRADE_STEP_NAME = STEPNAME THEN (
INCEILSTEP = 'Y'
l_data = set_log('YES IN CEILING ' || STEPNAME )
)
)
l_data = set_log('Leaving GSP_PERSON_CEILING_STEP' )
RETURN INCEILSTEP
***********************************************************************************************
Parent Fast Formula
/*
Name: GSP_PERSON_ELIG
Formula Type : Participation and Rate Eligibility
Purpose: Person should have worked in the current step for a year to be
eligible for next step.
Person is eligible for only next step in the same Grade
If the person is already in the ceiling step of current Grade
then the person is eligible for next grade and the first step
*/
DEFAULT FOR PER_GRADE_STEP_NAME IS ' '
DEFAULT FOR PER_GRADE_STEP_SEQUENCE IS -1
DEFAULT FOR PER_ASG_GRADE_ID is -1
DEFAULT FOR PER_ASG_GRADE_NAME is ' '
DEFAULT FOR PER_GRADE_STEP_EFFECTIVE_START_DATE IS '1900/01/01 00:00:00' (date)
DEFAULT FOR CMP_IV_STEP_ID IS -1
DEFAULT FOR CMP_IV_STEP_NAME IS ' '
INPUTS ARE CMP_IV_GRADE_LADDER_ID(NUMBER),CMP_IV_GRADE_LADDER_NAME (TEXT),
CMP_IV_PERIOD_START_DATE (DATE), CMP_IV_PERIOD_END_DATE (DATE) ,
CMP_IV_GRADE_ID (NUMBER), CMP_IV_GRADE_NAME (TEXT), CMP_IV_GRADE_SEQ (NUMBER) ,
CMP_IV_STEP_ID (NUMBER), CMP_IV_STEP_NAME (TEXT)
ldata = SET_LOG('Entering TLK_GSP_PERSON_ELIG' )
ELIGIBLE = 'Y'
eDate = GET_CONTEXT(EFFECTIVE_DATE, '1900/01/01 00:00:00' (date) )
asgID = GET_CONTEXT(HR_ASSIGNMENT_ID, -1 )
ldata = SET_LOG('PER_ASG_GRADE_ID ' || TO_CHAR(PER_ASG_GRADE_ID) )
ldata = SET_LOG('PER_ASG_GRADE_NAME ' || PER_ASG_GRADE_NAME )
ldata = SET_LOG('PER_GRADE_STEP_SEQUENCE ' || TO_CHAR(PER_GRADE_STEP_SEQUENCE) )
ldata = SET_LOG('PER_GRADE_STEP_NAME ' || PER_GRADE_STEP_NAME )
ldata = SET_LOG('EFFECTIVE_DATE ' || TO_CHAR(eDate) )
ldata = SET_LOG('PER_GRADE_STEP_EFFECTIVE_START_DATE ' || TO_CHAR(PER_GRADE_STEP_EFFECTIVE_START_DATE) )
ldata = SET_LOG('Asg ID ' || TO_CHAR(asgID) )
ldata = SET_LOG('CMP_IV_GRADE_LADDER_ID ' || TO_CHAR(CMP_IV_GRADE_LADDER_ID ) )
ldata = SET_LOG('CMP_IV_GRADE_LADDER_NAME ' || CMP_IV_GRADE_LADDER_NAME )
ldata = SET_LOG('CMP_IV_GRADE_ID ' || TO_CHAR(CMP_IV_GRADE_ID ) )
ldata = SET_LOG('CMP_IV_GRADE_SEQ ' || TO_CHAR(CMP_IV_GRADE_SEQ ) )
ldata = SET_LOG('CMP_IV_GRADE_NAME ' || CMP_IV_GRADE_NAME )
ldata = SET_LOG('CMP_IV_STEP_ID ' || TO_CHAR(CMP_IV_STEP_ID ) )
ldata = SET_LOG('CMP_IV_STEP_NAME ' || CMP_IV_STEP_NAME )
/* more then a year validation */
IF DAYS_BETWEEN(eDate , PER_GRADE_STEP_EFFECTIVE_START_DATE) < 365 THEN (
ELIGIBLE = 'N'
ldata = SET_LOG(' inelig due to year validation ' )
)
IF ELIGIBLE = 'Y' THEN (
inceiling = 'N'
IF CMP_IV_STEP_ID WAS DEFAULTED THEN (
ldata = SET_LOG(' In Grade Validation' )
IF PER_ASG_GRADE_ID WAS NOT DEFAULTED THEN (
IF IS_EXECUTABLE('GSP_PERSON_CEILING_STEP') THEN(
SET_INPUT('GRADE_ID', PER_ASG_GRADE_ID )
EXECUTE('GSP_PERSON_CEILING_STEP')
inceiling = GET_OUTPUT('INCEILSTEP', 'N')
ldata = SET_LOG(' inceiling ' ||inceiling )
)
)
/* validate the current and elig grade */
IF PER_ASG_GRADE_ID != CMP_IV_GRADE_ID THEN (
ELIGIBLE = 'N'
IF inceiling = 'Y' THEN (
IF PER_ASG_GRADE_NAME = 'Analyst 1' and CMP_IV_GRADE_NAME = 'Analyst 2' THEN (
ELIGIBLE = 'Y'
ldata = SET_LOG('Next Grade allowed ' || CMP_IV_GRADE_NAME )
) ELSE (
IF PER_ASG_GRADE_NAME = 'Analyst 2' and CMP_IV_GRADE_NAME = 'Analyst 3' THEN (
ELIGIBLE = 'Y'
ldata = SET_LOG('Next Grade allowed ' || CMP_IV_GRADE_NAME )
)
)
)
)
)ELSE (
ldata = SET_LOG(' In Step Validation' )
IF CMP_IV_STEP_ID != -1 THEN (
ELIGIBLE = 'N'
IF PER_ASG_GRADE_NAME = CMP_IV_GRADE_NAME THEN (
IF PER_GRADE_STEP_SEQUENCE+1 = TO_NUMBER(CMP_IV_STEP_NAME) THEN (
ELIGIBLE = 'Y'
ldata = SET_LOG('Next step allowed ' || CMP_IV_STEP_NAME )
)
)ELSE (
/* moving to new g4ade */
IF TO_NUMBER(CMP_IV_STEP_NAME) = 1 THEN(
ELIGIBLE = 'Y'
ldata = SET_LOG('First step allowed ' || CMP_IV_STEP_NAME )
)
)
)
)
)
ldata = SET_LOG('Leaving TLK_GSP_PERSON_ELIG '|| ELIGIBLE )
RETURN ELIGIBLE
