HIRE/TERMINATION Absence Accrual Proration

We usually face issue with accrual proration that proration formula is working for HIRE action but the same formula is not working for TERMINATION.

This is the case when Matix Accrual has been setup. 

If accrual setup has been done by Fast Formula then we can handle proration in accrual formula itself.

Kindly go through below steps and explanation to setup proration for absence accrual for HIRE/TERMINATION. 

Explanation:-

Accrual Definition : Matrix

Case : 

Employee accrual - 2.5 per month

Employee Termination Date - 26-APR-2017

Accrual should be for 26 days only.

Oracle Calculation : 

Total Accrual = Accrual Proration * Accrual From Partial Accrual Period(Optional) * Accrual value defined in Accrual Matrix.

Here, In our case

Accrual value defined in Accrual Matrix = 2.5 

Accrual Proration = handled by Proration Formula

Accrual From Partial Accrual Period = handled by Partial Period Formula(Optional)

As termination case is not being handled by Accrual Proration Formula, we have handled HIRE/TERMINATION both the cases in Absence Partial Accrual Period Accrual Rate Formula.

Calculation :

Total Accrual = 1* (26/30) * 2.5

Please go through below setup and attached formula to know configuration to achieve proration for this case.

 

Formula 1: GIC Leave Proration FF

/*****************************************************************
FORMULA NAME: GIC Leave Proration FF
FORMULA TYPE: Global Absence Proration

DESCRIPTION:
Change History:
Name                 Date        Comments
Jigar Makwana        26/04/2017     
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
*****************************************************************/
/*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/
DEFAULT for PER_ASG_REL_ORIGINAL_DATE_OF_HIRE is '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_ASG_REL_ACTUAL_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT for ANC_ABS_PLN_NAME is 'A'
DEFAULT for ANC_ABS_PLN_PLAN_UOM is 'D'



prorationFactor = 1


return prorationFactor

 Formula 1: GIC Absence Partial Period Accrual Rate

/*****************************************************************
FORMULA NAME: GIC Absence Partial Period Accrual Rate
FORMULA TYPE: Global Absence Partial Period Accrual Rate Formula

DESCRIPTION:
Change History:
Name                 Date        Comments
Jigar Makwana        26/04/2017
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
*****************************************************************/
/*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/
DEFAULT for PER_ASG_REL_ORIGINAL_DATE_OF_HIRE is '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_ASG_REL_ACTUAL_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT for ANC_ABS_PLN_NAME is 'A'
DEFAULT for ANC_ABS_PLN_PLAN_UOM is 'D'

INPUTS ARE IV_ACCRUAL,IV_CALEDARSTARTDATE (date),IV_CALEDARENDDATE(date)


l_no_of_days=DAYS_BETWEEN(GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date)),PER_ASG_REL_ORIGINAL_DATE_OF_HIRE)


l_accrual = 1

l_length_of_service=(l_no_of_days)
hire_year_number = to_num(to_char(PER_ASG_REL_ORIGINAL_DATE_OF_HIRE, 'yyyy'))
current_year_number =  to_num(to_char(GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date)), 'yyyy'))
hire_month_no= to_num(to_char(PER_ASG_REL_ORIGINAL_DATE_OF_HIRE, 'mm'))
current_month_no = to_num(to_char(GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date)), 'mm'))
no_of_days_month = to_num(to_char(PER_ASG_REL_ORIGINAL_DATE_OF_HIRE, 'dd'))
Last_month_day = to_num(to_char(GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date)), 'dd'))
l_last_date_of_term_month = to_num(to_char(LAST_DAY(GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))),'dd'))


term_year_number = to_num(to_char(PER_ASG_REL_ACTUAL_TERMINATION_DATE, 'yyyy'))
term_month_no= to_num(to_char(PER_ASG_REL_ACTUAL_TERMINATION_DATE, 'mm'))
no_of_days_term_month = to_num(to_char(PER_ASG_REL_ACTUAL_TERMINATION_DATE, 'dd'))


  if( term_year_number=current_year_number AND term_month_no = current_month_no) then
         (
           l_accrual = (no_of_days_term_month/l_last_date_of_term_month)
                   
            )
   
   
 if(  hire_year_number = current_year_number AND hire_month_no = current_month_no) then

        (
          l_accrual = l_no_of_days/Last_month_day
            )
           
accrual = l_accrual*IV_ACCRUAL

return accrual