Thursday, August 9, 2018

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 */
/* 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 */
/* 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. 

/* 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.


Fast Formula Log.



***Entering Tilak_Lakshmi_Eligbility_Elig_FF***
 Person ID : 300100167151081
 Asg ID : 300100167151144
 Effective date : 2018-08-07T00:00:00.000Z
 Current Step  : 1
 Validating STEP  : 2
Elig ASG ID  : -1
 First index of Seniority date   : 1
 Seniority Object ID    : 300100167151144
 Seniority Date Code    : GSP_TL_STEP_CODE
 Seniority Date   : 2014-07-23T00:00:00.000Z
 First Anniversary : 2015-07-23T00:00:00.000Z
 Third Anniversary : 2017-07-22T00:00:00.000Z
 Assignment has worked more then 3 years
 Leaving  Tilak_Laskshmi_Eligbility_Elig_FF : N
 ***Entering Tilak_Lakshmi_Eligbility_Elig_FF***
 Person ID : 300100167151081
 Asg ID : 300100167151144
 Effective date : 2018-08-07T00:00:00.000Z
 Current Step  : 1
 Validating STEP  : 3
 Elig ASG ID  : -1
 First index of Seniority date   : 1
 Seniority Object ID    : 300100167151144
 Seniority Date Code    : GSP_TL_STEP_CODE
 Seniority Date   : 2014-07-23T00:00:00.000Z
 First Anniversary : 2015-07-23T00:00:00.000Z
 Third Anniversary : 2017-07-22T00:00:00.000Z
 Assignment has worked more then 3 years
 Leaving  Tilak_Laskshmi_Eligbility_Elig_FF : Y
 ***Entering Tilak_Lakshmi_Eligbility_Elig_FF***
 Person ID : 300100167151081
 Asg ID : 300100167151144
 Effective date : 2018-08-07T00:00:00.000Z
 Current Step  : 1
 Validating STEP  : 4
 Elig ASG ID  : 300100167151144
 First index of Seniority date   : 1
 Seniority Object ID    : 300100167151144
 Seniority Date Code    : GSP_TL_STEP_CODE
 Seniority Date   : 2014-07-23T00:00:00.000Z
 First Anniversary : 2015-07-23T00:00:00.000Z
 Third Anniversary : 2017-07-22T00:00:00.000Z
 Assignment is already eligible for a Step
 Leaving  Tilak_Laskshmi_Eligbility_Elig_FF : N
 *** Entering Tilak_Lakshmi_GSP_DATE
 Ladder name Tilak-Lakshmi Test Ladder
 effective Date  2018/08/07
 Salary Frequency  MONTHLY
 Exiting Tilak_Lakshmi_GSP_DATE: 2018/09/01


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.



Posted by at 8 comments:

Monday, August 6, 2018

Grade Steps Progressions (GSP) Fast Formula Samples - 2




In our previous blog, we explained the Eligibility Formula for GSP. In this blog, we will continue our discussion with the Salary progression rate calculation Rule (GSP Rate), Salary progression, Date determination and Rule (GSP Date).
 
Formula Type: Salary progression rate calculation Rule (GSP Rate).

These Formulas allow you to customize the rate for all the Grades and Steps in a Grade Ladder. The Formula is defined at a Grade Ladder level and therefore the FF is executed for every Grade/Step in the Ladder and the value from the Formula takes precedence to the defined rate at Grade/Step level.

Note that the Formulas are not returning the salary but the Grade Step Rate. The GSP process is using the defined Annual Factors and FTE (Full Time Equivalent) to calculate the Salary from the value returned by the Fast Formula.  Do not assume that the value returned from the Formula is used as it is.

The Formulas uses the same context as any other Compensation Formula. Please see our previous blog on Compensation Formula Types and Contexts.

Input Values:

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

Grade Ladder Setup with Rate and Date Formula.



 

Formula use case For Calculating Rate:
As per our hypothetical use case, the Rate is calculated from a person’s current salary.

·       Get the Current Salary and Salary Frequencies from the Salary setup using DBI.
·       Get the Salary frequency from the Grade Ladder DBI, if the Ladder’s frequency is not the same as Salary setup (you do not need to get it from DBI as it is defined at ladder level, you can hard code the values), convert the salary to Grade Ladder frequency.
·       For each step, we add 2% of the original salary and the percentage increased with every setup. For example if the current step is 1 and Rate is calculated for Step 2, then we add 2% of salary, if Rate is calculated for Step 3 then we add 4% salary and so on.
·       As per our eligibility Rule, the person is either eligible for next step or the following step after that, therefore we use 2% or 4% hike.
·       If there is any FTE defined, we need to convert the rate with FTE because it is again applied in the process after the formula returns the value.


Formula Type: Salary progression rate calculation Rule

/***********************************************************
Formula Type : Salary Progression Rate Calculation Rule

***********************************************************/
/* DBI FOR  Ladder Factor and Freq */
DEFAULT_DATA_VALUE FOR  PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR IS 1
DEFAULT_DATA_VALUE FOR  PER_ASG_GRADE_RATES_RATE_FREQUENCY is ' '
DEFAULT_DATA_VALUE FOR  PER_ASG_GRADE_RATES_NAME is ' '
DEFAULT_DATA_VALUE FOR  PER_GRADE_RATES_ANNUALIZATION_FACTOR IS 1
DEFAULT_DATA_VALUE FOR  PER_GRADE_RATES_RATE_FREQUENCY IS ' '

DEFAULT FOR     PER_ASG_GRADE_LADDER_NAME IS ' '
/* DBI FOR  Salary Information */
DEFAULT FOR CMP_ASSIGNMENT_SALARY_FTE_VALUE IS 1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_AMOUNT IS -1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR IS -1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT IS -1 
DEFAULT FOR CMP_ASSIGNMENT_SALARY_BASIS_CODE IS ' '

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)

DEFAULT FOR CMP_IV_STEP_NAME IS '-1'

L_temp = ESS_LOG_WRITE('*** Entering Tilak_Lakshmi_GSP_RATE' )
L_temp = ESS_LOG_WRITE('Ladder name ' + PER_ASG_GRADE_LADDER_NAME )

L_Grade_annual_factor = 12
L_Grade_Freq  = 'MONTHLY'

/* find the current asg frequency
  You do not need this, since the FF written for a Ladder
*/
index = PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR.FIRST(-1)
WHILE (PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR.EXISTS(index))
LOOP (

   L_temp = ESS_LOG_WRITE('Grade Rate Name  ' + PER_ASG_GRADE_RATES_NAME[index] )
   IF (PER_ASG_GRADE_RATES_NAME[index] =PER_ASG_GRADE_LADDER_NAME) THEN (
      L_Grade_annual_factor = PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR[index]
      L_Grade_Freq          = PER_ASG_GRADE_RATES_RATE_FREQUENCY[index]

       L_temp= ESS_LOG_WRITE( ' Grade Annualization Factor  ' +  to_char(L_Grade_annual_factor) )
       L_temp= ESS_LOG_WRITE( ' Grade Annualization Frequency  ' +  L_Grade_Freq )
      EXIT
   )
   index  = PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR.NEXT(index ,-1)

)

L_temp= ESS_LOG_WRITE( ' CMP_ASSIGNMENT_SALARY_FTE_VALUE  ' +  to_char(CMP_ASSIGNMENT_SALARY_FTE_VALUE) )
L_temp= ESS_LOG_WRITE( ' CMP_ASSIGNMENT_SALARY_AMOUNT  ' +  to_char(CMP_ASSIGNMENT_SALARY_AMOUNT) )
L_temp= ESS_LOG_WRITE( ' CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR  ' +  to_char(CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR) )
L_temp= ESS_LOG_WRITE( ' CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT  ' +  to_char(CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT) )
L_temp= ESS_LOG_WRITE( ' CMP_ASSIGNMENT_SALARY_BASIS_CODE  ' +  CMP_ASSIGNMENT_SALARY_BASIS_CODE )
L_temp= ESS_LOG_WRITE( ' PER_GRADE_STEP_NAME  ' +  PER_GRADE_STEP_NAME )
L_temp= ESS_LOG_WRITE( ' Eligible STEP_NAME  ' +  CMP_IV_STEP_NAME )


L_salary = CMP_ASSIGNMENT_SALARY_AMOUNT
/* if the freq of the grade and salary is not the same */
IF (L_Grade_Freq != CMP_ASSIGNMENT_SALARY_BASIS_CODE) THEN (
   L_salary = CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT
   /* Convert the annual Salary to Grade rate */
   IF (L_Grade_annual_factor != 1) THEN (
      L_salary = L_salary / L_Grade_annual_factor
   )
)

L_temp= ESS_LOG_WRITE( ' Grade Rate salary  ' +  to_char(L_salary) )

/* Fte*/
IF (CMP_ASSIGNMENT_SALARY_FTE_VALUE WAS NOT DEFAULTED) THEN (
   L_salary = L_salary / CMP_ASSIGNMENT_SALARY_FTE_VALUE
   L_temp= ESS_LOG_WRITE( ' Grade Rate salary  ' +  to_char(L_salary) )
)
L_temp= ESS_LOG_WRITE( ' FTE Salary ' +  to_char(L_salary) )

/* add % to salary */
IF ( (TO_NUMBER(CMP_IV_STEP_NAME) - to_number(PER_GRADE_STEP_NAME)) > 1 ) THEN (
   L_salary =L_salary * 1.4
)ELSE (
    L_salary =L_salary * 1.2
)
L_temp= ESS_LOG_WRITE( ' Adjusted Salary ' +  to_char(L_salary) )


L_temp = ESS_LOG_WRITE('Exiting Tilak_Lakshmi_GSP_RATE: '+ TO_CHAR(L_salary) )
Return L_salary


Explanation of Formula.
/* DBI FOR  Ladder Factor and Freq */
DEFAULT_DATA_VALUE FOR  PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR IS 1
DEFAULT_DATA_VALUE FOR  PER_ASG_GRADE_RATES_RATE_FREQUENCY is ' '
DEFAULT_DATA_VALUE FOR  PER_ASG_GRADE_RATES_NAME is ' '
DEFAULT_DATA_VALUE FOR  PER_GRADE_RATES_ANNUALIZATION_FACTOR IS 1
DEFAULT_DATA_VALUE FOR  PER_GRADE_RATES_RATE_FREQUENCY IS ' '
DEFAULT FOR             PER_ASG_GRADE_LADDER_NAME IS ' '
/* DBI FOR  Salary Information */
DEFAULT FOR CMP_ASSIGNMENT_SALARY_FTE_VALUE IS 1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_AMOUNT IS -1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR IS -1
DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT IS -1 
DEFAULT FOR CMP_ASSIGNMENT_SALARY_BASIS_CODE IS ' '
DEFAULT FOR PER_GRADE_STEP_NAME IS '-1'

Declaration and initialization of Formula. We have declared more DBI than what we actually used in the Formula.


/* 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)

DEFAULT FOR CMP_IV_STEP_NAME IS '-1'

Declaration of Input values. The Step name alone initialized with default value because if the Ladder does not uses the Step, we do not get any errors from using Step Name Input values.

L_temp = ESS_LOG_WRITE('*** Entering Tilak_Lakshmi_GSP_RATE' )
L_temp = ESS_LOG_WRITE('Ladder name ' + PER_ASG_GRADE_LADDER_NAME )

L_Grade_annual_factor = 12
L_Grade_Freq  = 'MONTHLY'

/* find the current asg frequency
  You do not need this, since the FF written for a Ladder
*/
index = PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR.FIRST(-1)
WHILE (PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR.EXISTS(index))
LOOP (

   L_temp = ESS_LOG_WRITE('Grade Rate Name  ' + PER_ASG_GRADE_RATES_NAME[index] )
   IF (PER_ASG_GRADE_RATES_NAME[index] =PER_ASG_GRADE_LADDER_NAME) THEN (
      L_Grade_annual_factor = PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR[index]
      L_Grade_Freq          = PER_ASG_GRADE_RATES_RATE_FREQUENCY[index]

       L_temp= ESS_LOG_WRITE( ' Grade Annualization Factor  ' +  to_char(L_Grade_annual_factor) )
       L_temp= ESS_LOG_WRITE( ' Grade Annualization Frequency  ' +  L_Grade_Freq )
      EXIT
   )
   index  = PER_ASG_GRADE_RATES_ANNUALIZATION_FACTOR.NEXT(index ,-1)

)
Annualization Factor is defaulted to 12 and Frequency is defaulted to Monthly. As this Formula is created for a Ladder, we know in advance what is the Frequency and Annualization Factor. Therefore, we do not need to find the information from the DBI, we can use the hard coded values.
The code that finds the Frequency and Annualization factor do not work for this formula. This is just an example. The reason why it is not working is explained in the ‘Limitation of Formula’ section.


L_temp= ESS_LOG_WRITE( ' CMP_ASSIGNMENT_SALARY_FTE_VALUE  ' +  to_char(CMP_ASSIGNMENT_SALARY_FTE_VALUE) )
L_temp= ESS_LOG_WRITE( ' CMP_ASSIGNMENT_SALARY_AMOUNT  ' +  to_char(CMP_ASSIGNMENT_SALARY_AMOUNT) )
L_temp= ESS_LOG_WRITE( ' CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR  ' +  to_char(CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR) )
L_temp= ESS_LOG_WRITE( ' CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT  ' +  to_char(CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT) )
L_temp= ESS_LOG_WRITE( ' CMP_ASSIGNMENT_SALARY_BASIS_CODE  ' +  CMP_ASSIGNMENT_SALARY_BASIS_CODE )
L_temp= ESS_LOG_WRITE( ' PER_GRADE_STEP_NAME  ' +  PER_GRADE_STEP_NAME )
L_temp= ESS_LOG_WRITE( ' Eligible STEP_NAME  ' +  CMP_IV_STEP_NAME )


L_salary = CMP_ASSIGNMENT_SALARY_AMOUNT
/* if the freq of the grade and salary is not the same */
IF (L_Grade_Freq != CMP_ASSIGNMENT_SALARY_BASIS_CODE) THEN (
   L_salary = CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT
   /* Convert the annual Salary to Grade rate */
   IF (L_Grade_annual_factor != 1) THEN (
      L_salary = L_salary / L_Grade_annual_factor
   )
)
Converting the salary to the Grade Rate value.

L_temp= ESS_LOG_WRITE( ' Grade Rate salary  ' +  to_char(L_salary) )

/* Fte*/
IF (CMP_ASSIGNMENT_SALARY_FTE_VALUE WAS NOT DEFAULTED) THEN (
   L_salary = L_salary / CMP_ASSIGNMENT_SALARY_FTE_VALUE
   L_temp= ESS_LOG_WRITE( ' Grade Rate salary  ' +  to_char(L_salary) )
)
L_temp= ESS_LOG_WRITE( ' FTE Salary ' +  to_char(L_salary) )
Applying FTE to the Grade Rate Value.

/* add % to salary */
IF ( (TO_NUMBER(CMP_IV_STEP_NAME) - to_number(PER_GRADE_STEP_NAME)) > 1 ) THEN (
   L_salary =L_salary * 1.4
)ELSE (
    L_salary =L_salary * 1.2
)
L_temp= ESS_LOG_WRITE( ' Adjusted Salary ' +  to_char(L_salary) )
Giving raise a per the experience.


L_temp = ESS_LOG_WRITE('Exiting Tilak_Lakshmi_GSP_RATE: '+ TO_CHAR(L_salary) )
Return L_salary

Returning the Final Value.

Limitation of the Formula:
1)     No DBI at Steps level to find the rate is available.
2)     The sample code is using the Grade level DBI to find the Rate and there is no rate defined at the Grade level.

Log

*** Entering Tilak_Lakshmi_GSP_RATE
Ladder name Tilak-Lakshmi Test Ladder
Grade Rate Name  GSP Midwest Customer Support Grades
 CMP_ASSIGNMENT_SALARY_FTE_VALUE  .5
 CMP_ASSIGNMENT_SALARY_AMOUNT  1001
 CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR  12
 CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT  12012
 CMP_ASSIGNMENT_SALARY_BASIS_CODE  MONTHLY
 PER_GRADE_STEP_NAME  1
 Eligible STEP_NAME  2
 Grade Rate salary  1001
 Grade Rate salary  2002
 FTE Salary 2002
 Adjusted Salary 2402.4
Exiting Tilak_Lakshmi_GSP_RATE: 2402.4


Formula use case For Calculating Date:
Honestly, we were not able to find a good use case for calculating the date. We are not sure if the following use case has any practical purpose but it may help you to understand the basics of the Data Calculation Fast Formula. We decided to determine the date from the Frequency of the salary. If the Salary Frequency is Annual, the Date will be the first of next year. If it is Monthly then the Date will be the first of next month.
Otherwise, the Effective date will be returned.

Formula Type: Salary progression rate calculation Rule


/*********************************************
Formula Type:     Salary Progression Date Determination Rule

****************************************************/


DEFAULT FOR     PER_ASG_GRADE_LADDER_NAME IS ' '
/* DBI FOR  Salary Information */
DEFAULT FOR CMP_ASSIGNMENT_SALARY_BASIS_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_GSP_DATE' )
L_temp = ESS_LOG_WRITE('Ladder name ' + PER_ASG_GRADE_LADDER_NAME )

l_date  = GET_CONTEXT(EFFECTIVE_DATE, '2000/01/10' (date))
l_date_c = to_char(l_date, 'YYYY/MM/DD')

L_temp = ESS_LOG_WRITE('effective Date  ' + l_date_c )
L_temp = ESS_LOG_WRITE('Salary Frequency  ' + CMP_ASSIGNMENT_SALARY_BASIS_CODE )

/*Calculate the Date */
IF (CMP_ASSIGNMENT_SALARY_BASIS_CODE = 'ANNUALLY') THEN (
  
    L_DATE = TRUNC(ADD_MONTHS(l_DATE, 12), 'YYYY')
)ELSE (
   IF (CMP_ASSIGNMENT_SALARY_BASIS_CODE = 'MONTHLY') THEN (
  
       L_DATE = TRUNC(ADD_MONTHS(l_DATE, 12), 'MM')
   )
)
l_date_c = to_char(l_date, 'YYYY/MM/DD')

L_temp = ESS_LOG_WRITE('Exiting Tilak_Lakshmi_GSP_DATE: '+l_date_c  )
Return l_date_c

The Formula is pretty self-explanatory.  If the Salary Frequency is Annually, 12 months is added to effective date and First date of the year is calculated from the date.  If the Frequency is Monthly, 1 month is added to the effective date and First date of the month is calculated from the Date. The result is converted into ‘YYYY/MM/DD’ date format and the value is returned.


Log


 *** Entering Tilak_Lakshmi_GSP_DATE
Ladder name Tilak-Lakshmi Test Ladder
effective Date  2016/07/30
Salary Frequency  MONTHLY
Exiting Tilak_Lakshmi_GSP_DATE: 2016/08/01



This completes the Rate and Date formulas. As usual, if you have any questions or suggestions, please feel free to comment.






You Tube Tutorial: GSP Person Selection Formula 2 - Oracle Fusion HCM Fast Formula Tutorial-9

/*

Name:  GSP_PERSON_SELECTION2 

Type:  Compensation Person Selection

Purpose: Skip the person who is already in ceiling step 

          

***************************/


/* DBI */ 


DEFAULT_DATA_VALUE FOR  PER_GRD_GRADE_HISTORY_CEILING_STEP_ID IS -1

DEFAULT_DATA_VALUE FOR  PER_GRD_GRADE_HISTORY_EFFECTIVE_START_DATE IS  '1900/01/01 00:00:00' (date)

DEFAULT_DATA_VALUE FOR  PER_GRD_GRADE_HISTORY_EFFECTIVE_END_DATE IS  '1900/01/01 00:00:00' (date)

DEFAULT FOR PER_GRADE_STEP_NAME IS ' '


/* input Values */ 

INPUTS ARE CMP_IV_GRADE_LADDER_ID (NUMBER)


l_data = set_log('Entering GSP_PERSON_SELECTION2' )

grade = GET_CONTEXT(GRADE_ID, -1)

eDate = GET_CONTEXT(EFFECTIVE_DATE, '1900/01/01 00:00:00' (date) )


l_data = set_log('Ladder ' ||  TO_CHAR(CMP_IV_GRADE_LADDER_ID) )

l_data = set_log('Grade  ' ||  TO_CHAR(grade) )

l_data = set_log('EFF DATE   ' ||  TO_CHAR(eDate) )

l_data = set_log('Person Grade Step Name   ' ||  PER_GRADE_STEP_NAME )


/* Find the ceiling id of eff date */ 

RETVAL = 'Y' 

l_step_id = -1

index = PER_GRD_GRADE_HISTORY_CEILING_STEP_ID.FIRST( -1)

WHILE (PER_GRD_GRADE_HISTORY_CEILING_STEP_ID.EXISTS(index))

LOOP (

   lstDate = PER_GRD_GRADE_HISTORY_EFFECTIVE_START_DATE[index] 

   lenDate = PER_GRD_GRADE_HISTORY_EFFECTIVE_END_DATE[index] 

   l_data = set_log('CEILING Dates   ' ||  TO_CHAR(lstDate) || ' / '||  TO_CHAR(lenDate))

   if (edate >= lstDate AND edate <= lenDate ) THEN (

       l_step_id =  PER_GRD_GRADE_HISTORY_CEILING_STEP_ID[index] 

       l_data = set_log('Step ID    ' ||  TO_CHAR(l_step_id) )

       exit

   )

  index = PER_GRD_GRADE_HISTORY_CEILING_STEP_ID.NEXT(index, -1 )

)

/* eof loop */ 

IF ( l_step_id != -1  ) THEN (

  lparam = '|=STEP_ID='|| TO_CHAR(l_step_id) 

  l_data = set_log('Person Grade Step Name   ' ||   lparam )

  l_step_name = GET_VALUE_SET('GSP_GET_STEP_NAME' , lparam)

  l_data = set_log('Ceil Step Name   ' ||   l_step_name )  

  IF ( PER_GRADE_STEP_NAME = l_step_name )THEN (

     RETVAL = 'N' 

  )



)


l_data = set_log('Leaving  GSP_PERSON_SELECTION2 ' || RETVAL )

RETURN RETVAL 

Posted by at 6 comments:
Subscribe to: Posts (Atom)