Calculating 13 or 14 months salary in TCS

Calculating 13 or 14 months salary in TCS

 

 

This is a sample formula for calculating 13 or 14 months salary for the Total Compensation Statement.

This is just a sample code, this may not work as it is for your needs, and you may need to change the logic as per your requirements.

 

Please see the YouTube Tutorial for an explanation of this code.

 

1)     Simple Sample Formula

Assume your salary is monthly, the salary has not changed during the statement period.
The 13th salary is added to the salary of December.

 

 

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

FF Name: TILAK_ARRAY_13MONTH_SAL_1

Type   : Total Compensation Item

Developer: Tilak

Purpose: Calculate 13-month salary and adding that in DEC month

 

 

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

 

/* Declare DBI */

 

 

DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT is 0

DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR is 0

DEFAULT FOR PER_ASG_LEGAL_ENTITY_ID  is -1

DEFAULT FOR CMP_ASSIGNMENT_SALARY_DATE_FROM is   '1900/01/01' (date)

 

DEFAULT FOR COMPENSATION_DATES is EMPTY_TEXT_NUMBER

DEFAULT FOR VALUES         is EMPTY_TEXT_NUMBER

DEFAULT FOR ASSIGNMENTS    is EMPTY_TEXT_NUMBER

DEFAULT FOR LEGALEMPLOYERS is EMPTY_TEXT_NUMBER

 

/* Declare IV */

 

INPUTS ARE  CMP_IV_PERIOD_START_DATE(DATE),

         CMP_IV_PERIOD_END_DATE (DATE)

 

/***************** Main ************************/

temp = SET_LOG('Entering Fromula TILAK_ARRAY_13MONTH_SAL_1 ' )

myAnnSalary = CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT

myAnnFactor = CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR

 

/*

myAnnFactor = 13 

 

IF CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT WAS DEFAULTED THEN

(

   temp =SET_LOG('No Salary Found using 13K for testing ' )

   myAnnSalary = 13000

)

*/

 

asgId  = get_context(HR_ASSIGNMENT_ID, -1)

leId   = PER_ASG_LEGAL_ENTITY_ID

 

myMonthSalary  = myAnnSalary / myAnnFactor

temp =SET_LOG('Annual  Salary  ' || TO_CHAR(myAnnSalary) )

temp =SET_LOG('Monthly Salary  ' || TO_CHAR(myMonthSalary) )

temp =SET_LOG('Assinment  ' || TO_CHAR(asgId) )

temp =SET_LOG('LE   ' || TO_CHAR(leId) )

 

/* Asuumed Salary is first of Every Month First the first date within the period */

firstDate = TRUNC(CMP_IV_PERIOD_START_DATE, 'MM')

endDate   = CMP_IV_PERIOD_END_DATE

 

temp =SET_LOG('first Date  ' || TO_CHAR(firstDate) )

temp =SET_LOG('Last Date ' || TO_CHAR(endDate) )

 

index = 1

IF (firstDate < CMP_IV_PERIOD_START_DATE) then

(

  firstDate = ADD_MONTHS(firstDate, 1)

)

 

/* Create Row */

WHILE firstDate <= endDate

LOOP(

temp =SET_LOG('Processing Date  ' || TO_CHAR(firstDate) )

COMPENSATION_DATES[index] = to_char(firstDate, 'YYYY/MM/DD')

ASSIGNMENTS[index]    = to_char(asgId)

LEGALEMPLOYERS[index] = to_char(leId)

VALUES[index]                = to_char(myMonthSalary)

IF ( TO_CHAR(firstDate, 'MM') = '12' ) then

(

     VALUES[index]                = to_char(myMonthSalary  * 2)

  )

 

  

   index = index + 1

   firstDate = add_months(firstDate, 1)

)

 

 

temp =SET_LOG('Leaving Fromula TILAK_ARRAY_13MONTH_SAL_1 ' )

RETURN COMPENSATION_DATES, VALUES, ASSIGNMENTS, LEGALEMPLOYERS

 

         2) This Formula assumes that the salary might be changed within the period 
             Th 13th month salary is added as an entry on December 

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

FF Name: TILAK_ARRAY_13MONTH_SAL_2 

Type   : Total Compensation Item

Developer: Tilak

Purpose: Calculate 13-month salary and adding that in DEC month

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

/* Declare DBI */ 

DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT is 0

DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR is 0

DEFAULT FOR PER_ASG_LEGAL_ENTITY_ID  is -1 

DEFAULT FOR CMP_ASSIGNMENT_SALARY_DATE_FROM is   '1900/01/01' (date)

DEFAULT FOR COMPENSATION_DATES is EMPTY_TEXT_NUMBER 

DEFAULT FOR VALUES             is EMPTY_TEXT_NUMBER 

DEFAULT FOR ASSIGNMENTS        is EMPTY_TEXT_NUMBER 

DEFAULT FOR LEGALEMPLOYERS     is EMPTY_TEXT_NUMBER 

/* Declare IV */ 

INPUTS ARE  CMP_IV_PERIOD_START_DATE(DATE), 

            CMP_IV_PERIOD_END_DATE (DATE)

/***************** Main ************************/

temp = SET_LOG('Entering Fromula TILAK_ARRAY_13MONTH_SAL_1 ' )

myAnnSalary = CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT 

myAnnFactor = CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR

asgId  = get_context(HR_ASSIGNMENT_ID, -1)

leId   = PER_ASG_LEGAL_ENTITY_ID 

temp =SET_LOG('Assinment  ' || TO_CHAR(asgId) )

temp =SET_LOG('LE   ' || TO_CHAR(leId) )

/* Asuumed Salary is first of Every Month First the first date within the period */ 

firstDate = TRUNC(CMP_IV_PERIOD_START_DATE, 'MM') 

endDate   = CMP_IV_PERIOD_END_DATE 

temp =SET_LOG('first Date  ' || TO_CHAR(firstDate) )

temp =SET_LOG('Last Date    ' || TO_CHAR(endDate) )

index     = 1

IF (firstDate < CMP_IV_PERIOD_START_DATE) then

(

  firstDate = ADD_MONTHS(firstDate, 1)

)

/* Create Row */ 

WHILE firstDate <= endDate

LOOP(

     myMonthSalary = 0 

    /* Get the salary on the date */

    CHANGE_CONTEXTS(EFFECTIVE_DATE=firstDate)

     (

      myAnnSalary = CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT 

      myAnnFactor = CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR

       myMonthSalary  = myAnnSalary / myAnnFactor

      

      temp =SET_LOG('Annual  Salary  ' || TO_CHAR(myAnnSalary) )

      temp =SET_LOG('Monthly Salary  ' || TO_CHAR(myMonthSalary) )

 

    )

    if  (myMonthSalary != 0 ) THEN 

    (  

       temp =SET_LOG('Processing Date  ' || TO_CHAR(firstDate) ) 

       temp =SET_LOG('Processing Date  ' || TO_CHAR(firstDate) ) 

       COMPENSATION_DATES[index] = to_char(firstDate, 'YYYY/MM/DD')

       ASSIGNMENTS[index]        = to_char(asgId)

       LEGALEMPLOYERS[index]     = to_char(leId)

       VALUES[index]                    = to_char(myMonthSalary) 

      /* add one more row for Dec */

      IF ( TO_CHAR(firstDate, 'MM') = '12' ) then

      (

         index = index + 1 

         COMPENSATION_DATES[index] = to_char(firstDate, 'YYYY/MM/DD')

         ASSIGNMENTS[index]        = to_char(asgId)

         LEGALEMPLOYERS[index]     = to_char(leId)

         VALUES[index]                    = to_char(myMonthSalary) 

      )

      index = index + 1

    ) 

   firstDate = add_months(firstDate, 1) 

)

temp =SET_LOG('Leaving Fromula TILAK_ARRAY_13MONTH_SAL_1 ' )

RETURN COMPENSATION_DATES, VALUES, ASSIGNMENTS, LEGALEMPLOYERS

This is only a sample formula and is meant to be a guideline. If you are using any of the above code, you need to test it to check  if it matches your specific requirements.

Common Issues faced by Compensation Fast Formula Developers

Common Issues 

We found that many Fast Formula users face the same issues again and again. We thought that we can put together some guidelines that could help them in solving their issues.

Issue	Description
Working with multi Assignments	When an employee transferred in a period or terminated and rehired in the same period. The Fast Formula needs to deal with two different assignment of the person.

Working with multi Assignments

As we described earlier in our previous blogs, the Fast Formula is either executed for a person (TCS, Primary assignment is used internally) or for an Assignments (CWB, GSP).  Some time in real business cases, the Fast Formula needs to deals with more than one assignment. For example, when a person is transferred or a person is terminated and hired in the same period where the Fast Formula is executed, the Fast Formula needs to works with both these assignment to collect the information.  Since the Fast Formula is executed with an Assignment Id on an effective date, the Fast Formula developer needs to create their own logic to switch between the assignments.

Considering these issues, Compensation started passing an array of Assignment Id, Term Id, Payroll Assignment Id, Payroll Term Id and Payroll Relationship Id as input values to Fast Formula from Future Release (Please check with Oracle about the availability).

The input values are:

• CMP_IVR_ASSIGNMENT_ID
• CMP_IVR_TERM_ID
• CMP_IVR_PAY_ASSIGNMENT_ID
• CMP_IVR_PAY_TERM_ID
• CMP_IVR_PAY_RELATION_ID

In case, the Compensation is not able to find related information for the Assignment, it will pass -1 in other arrays to make sure all these arrays are passed with equal numbers of rows.  

R11:  Since we do not have this array input values support in R11, we also provide an example that uses the existing array DBIs to get the same result.
For both the examples, we use the same hypothetical example to show the logic.
The formula is developed using TCS Item Fast Formula but the same Fast Formula can be used in any Compensation Fast Formula types with minor changes.

These Fast formulas go through all the primary employee assignments to get their salary.

/*

Name: TCS_RXX_MULTI_ASG

Type   :  Total Compensation Item

*/

/*Declare DBI*/

DEFAULT FOR CMP_ASSIGNMENT_SALARY_AMOUNT IS 0

DEFAULT FOR PER_ASG_ASSIGNMENT_TYPE IS 'N'

DEFAULT FOR PER_ASG_PRIMARY_FLAG IS 'N'

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

/*OUTPUT VALUES */

DEFAULT FOR COMPENSATION_DATES is EMPTY_TEXT_NUMBER

DEFAULT FOR VALUES is EMPTY_TEXT_NUMBER

DEFAULT FOR ASSIGNMENTS is EMPTY_TEXT_NUMBER

DEFAULT FOR LEGALEMPLOYERS is EMPTY_TEXT_NUMBER

/* Input Values */

DEFAULT for CMP_IVR_ASSIGNMENT_ID is EMPTY_NUMBER_NUMBER

INPUTS ARE CMP_IV_PERSON_ID , CMP_IV_PERIOD_START_DATE(DATE),

     CMP_IV_PERIOD_END_DATE (DATE), CMP_IVR_ASSIGNMENT_ID(NUMBER_NUMBER) ,

     CMP_IVR_TERM_ID(NUMBER_NUMBER), CMP_IVR_PAY_ASSIGNMENT_ID (NUMBER_NUMBER),

     CMP_IVR_PAY_TERM_ID(NUMBER_NUMBER), CMP_IVR_PAY_RELATION_ID(NUMBER_NUMBER)

l_data= ESS_LOG_WRITE( 'ENTERING TCS_R12_MULTI_ASG ' )

l_counter = 1
index = CMP_IVR_ASSIGNMENT_ID.FIRST(-1)

l_data= ESS_LOG_WRITE( ' first index ' + TO_CHAR(index ) )

L_PERSON_ID = GET_CONTEXT(PERSON_ID, -1)

L_ASG_ID = GET_CONTEXT(HR_ASSIGNMENT_ID, -1)

L_TOT_COUNT = CMP_IVR_ASSIGNMENT_ID.COUNT

l_data= ESS_LOG_WRITE( ' NO OF ASSIGNMENT  ' + TO_CHAR(L_TOT_COUNT ) )

l_data= ESS_LOG_WRITE( ' PERSON_ID  ' + TO_CHAR(L_PERSON_ID  ) )

l_data= ESS_LOG_WRITE( ' ASG_ID  ' + TO_CHAR(L_ASG_ID ) )

l_data= ESS_LOG_WRITE( ' Start Date  ' + TO_CHAR(CMP_IV_PERIOD_START_DATE ) )

l_data= ESS_LOG_WRITE( ' End Date   ' + TO_CHAR(CMP_IV_PERIOD_END_DATE ) )

/* Looping through the assignment array */

WHILE ( CMP_IVR_ASSIGNMENT_ID.EXISTS(index))

LOOP (

  l_data= ESS_LOG_WRITE( ' Assignment in Array ' + TO_CHAR( CMP_IVR_ASSIGNMENT_ID[index]) )

  /* Thoug this formul ahs nothing to do with payroll data, we are just printing the data

     This will help the user who wantsto user payroll data

  */

  l_data= ESS_LOG_WRITE( 'TERM ID       ' + TO_CHAR(CMP_IVR_TERM_ID[index]) )

  l_data= ESS_LOG_WRITE( 'Pay Asg ID    ' + TO_CHAR(CMP_IVR_PAY_ASSIGNMENT_ID[index]) )

  l_data= ESS_LOG_WRITE( 'Pay Asg Rel ID' + TO_CHAR(CMP_IVR_PAY_RELATION_ID[index]) )

  

  /*Changing the context of assinment so that we can get asg related data from DBI

    Please note the change context works only within the bracket. Outside of the bracket

    the original context is in effect

  */

  CHANGE_CONTEXTS(HR_ASSIGNMENT_ID= CMP_IVR_ASSIGNMENT_ID[index] )

  (

     /* Validate the primary flag and assignment type */

      IF ( PER_ASG_ASSIGNMENT_TYPE = 'E' AND PER_ASG_PRIMARY_FLAG = 'Y' ) THEN

      (

          l_data= ESS_LOG_WRITE( 'Assignment is Primary Employee' )

          l_date_hire =  PER_ASG_REL_DATE_START

          /*if salary is not 0*/

          l_data= ESS_LOG_WRITE( 'Salary Amount ' || TO_CHAR(CMP_ASSIGNMENT_SALARY_AMOUNT) )

          IF  CMP_ASSIGNMENT_SALARY_AMOUNT WAS NOT DEFAULTED THEN

          (

              COMPENSATION_DATES[l_counter]= TO_CHAR( CMP_IV_PERIOD_END_DATE, 'YYYY/MM/DD')

              ASSIGNMENTS[l_counter] = TO_CHAR(CMP_IVR_ASSIGNMENT_ID[index])

              VALUES[l_counter] = TO_CHAR(CMP_ASSIGNMENT_SALARY_AMOUNT)

              l_counter = l_counter + 1

          )

       )  

   )

   /* Moving to next assingment*/

   index = CMP_IVR_ASSIGNMENT_ID.NEXT(index,-1)

)

l_data= ESS_LOG_WRITE( 'EXITINGTCS_R12_MULTI_ASG ' )

RETURN COMPENSATION_DATES , VALUES,ASSIGNMENTS

------------------------------------------------------------------------------------------------------------------------------------------

/*

Name: TCS_R11_MULTI_ASG

Type   :  Total Compensation Item

*/

/*Declare DBI*/

DEFAULT FOR CMP_ASSIGNMENT_SALARY_AMOUNT IS 0

DEFAULT FOR PER_ASG_ASSIGNMENT_TYPE IS 'N'

DEFAULT FOR PER_ASG_PRIMARY_FLAG IS 'N'

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

DEFAULT FOR PER_ASG_PAYROLL_INFO_PAYROLL_ASSIGNMENT_ID IS -1

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

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

DEFAULT_DATA_VALUE FOR  PER_HIST_ASG_ASSIGNMENT_ID IS -1

/* Input Values */

INPUTS ARE CMP_IV_PERSON_ID , CMP_IV_PERIOD_START_DATE(DATE),CMP_IV_PERIOD_END_DATE (DATE)

COMPENSATION_DATES = ' '

VALUES  = ' '

ASSIGNMENTS =  ' '

l_data= ESS_LOG_WRITE( 'ENTERING TCS_R11_MULTI_ASG ' )

l_counter = 1

index =  PER_HIST_ASG_ASSIGNMENT_ID.FIRST(-1)

l_data= ESS_LOG_WRITE( ' first index ' + TO_CHAR(index ) )

L_PERSON_ID = GET_CONTEXT(PERSON_ID, -1)

L_ASG_ID = GET_CONTEXT(HR_ASSIGNMENT_ID, -1)

L_TOT_COUNT = PER_HIST_ASG_ASSIGNMENT_ID.COUNT

l_data= ESS_LOG_WRITE( ' NO OF ASSIGNMENT  ' + TO_CHAR(L_TOT_COUNT ) )

l_data= ESS_LOG_WRITE( ' PERSON_ID  ' + TO_CHAR(L_PERSON_ID  ) )

l_data= ESS_LOG_WRITE( ' ASG_ID  ' + TO_CHAR(L_ASG_ID ) )

l_data= ESS_LOG_WRITE( ' Start Date  ' + TO_CHAR(CMP_IV_PERIOD_START_DATE ) )

l_data= ESS_LOG_WRITE( ' End Date   ' + TO_CHAR(CMP_IV_PERIOD_END_DATE ) )

/* Looping through the assignment array */

WHILE (  PER_HIST_ASG_ASSIGNMENT_ID.EXISTS(index))

LOOP (

    /* the history has all the date tracked rows, we take the row that falls within

       our date

    */

    l_data= ESS_LOG_WRITE( ' Assignment of asg array ' + TO_CHAR(PER_HIST_ASG_ASSIGNMENT_ID[index] ) )

    l_data= ESS_LOG_WRITE( ' Start Date of asg array ' + TO_CHAR(PER_HIST_ASG_EFFECTIVE_START_DATE[index] ) )

    l_data= ESS_LOG_WRITE( ' End Date of asg array  ' + TO_CHAR(PER_HIST_ASG_EFFECTIVE_END_DATE[index] ) )

    IF ( PER_HIST_ASG_EFFECTIVE_START_DATE[index] <= CMP_IV_PERIOD_END_DATE AND

         PER_HIST_ASG_EFFECTIVE_END_DATE[index]  >= CMP_IV_PERIOD_START_DATE) THEN

    (

         l_date = GREATEST(CMP_IV_PERIOD_START_DATE, PER_HIST_ASG_EFFECTIVE_START_DATE[index])

         CHANGE_CONTEXTS(HR_ASSIGNMENT_ID= PER_HIST_ASG_ASSIGNMENT_ID[index],  EFFECTIVE_DATE = l_date  )

         (

            /* Validate the primary flag and assignment type */

            IF ( PER_ASG_ASSIGNMENT_TYPE = 'E' AND PER_ASG_PRIMARY_FLAG = 'Y' ) THEN

            (

                l_data= ESS_LOG_WRITE( 'Assignment is Primary Employee' )

                l_date_hire =  PER_ASG_REL_DATE_START

                /*if salary is not 0*/

               l_data= ESS_LOG_WRITE( 'Salary Amount ' || TO_CHAR(CMP_ASSIGNMENT_SALARY_AMOUNT) )

               l_data = ESS_LOG_WRITE(' PAY ASG ' || TO_CHAR(PER_ASG_PAYROLL_INFO_PAYROLL_ASSIGNMENT_ID ) )

               IF  CMP_ASSIGNMENT_SALARY_AMOUNT WAS NOT DEFAULTED THEN

               (

                  COMPENSATION_DATES= COMPENSATION_DATES||TO_CHAR( l_date, 'YYYY/MM/DD')|| ';'

                  ASSIGNMENTS = ASSIGNMENTS||TO_CHAR(PER_HIST_ASG_ASSIGNMENT_ID[index]) ||';'

                  VALUES = VALUES ||TO_CHAR(CMP_ASSIGNMENT_SALARY_AMOUNT) ||';'

                  l_counter = l_counter + 1

                  l_data= ESS_LOG_WRITE( 'date  ' || COMPENSATION_DATES)

                  l_data= ESS_LOG_WRITE( 'asg' || ASSIGNMENTS )

                  l_data= ESS_LOG_WRITE( 'value' || VALUES )

               )

            )

          )  

    )     

    index =  PER_HIST_ASG_ASSIGNMENT_ID.NEXT(index, -1)

)

COMPENSATION_DATES  = LTRIM(COMPENSATION_DATES)

VALUES              = LTRIM(VALUES)

ASSIGNMENTS         = lTRIM(ASSIGNMENTS)

l_data= ESS_LOG_WRITE( 'EXITING TCS_R11_MULTI_ASG ' )  

RETURN COMPENSATION_DATES , VALUES,ASSIGNMENTS

Please note that these formulas are not fully tested. Use these codes as a sample to understand the logic.
Please also check the availability of the features with Oracle.