Thursday, April 27, 2017

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.

2 comments:

  1. Hi Tilak/Lakshmi,

    Great work guys.This Blog is helpful.

    I have an issue in benefits person changes causes life event type fast formula.

    My requirement is that life event should be triggered based on Assignment table attribute1 value. Can this be achieved as the there is no input value for this column? Also do you know where can i see the logs for this type of fast formulas?

    Regards,
    KP

    ReplyDelete
    Replies
    1. I am not sure I understood the question, have you checked the input values, old_val, new_val and column ?

      Delete