Sample Fast Fomula - Global Absence Partial Period Accrual Rate Formula

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

FORMULA NAME: XYZ_ANNUAL_LEAVE_PRORATION_FF

FORMULA TYPE: Global Absence Partial Period Accrual Rate Formula

DESCRIPTION: This formula returns the accrual for mid-period enrollments and un-enrollments for Annual Leave absence plan

Change History:

Name Date Version  Comments

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

Aditya DRAFT 1A Initial Version

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

DEFAULT FOR IV_ACCRUAL IS 0

DEFAULT FOR ACP_HIRE_DATE IS '1901/01/01 00:00:00' (date)

DEFAULT FOR ACP_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_ACCRUALPERIODSTARTDATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_ACCRUALPERIODENDDATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_PLANENROLLMENTSTARTDATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_PLANENROLLMENTENDDATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_CALEDARSTARTDATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_CALEDARENDDATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE is '47121231'

INPUTS are IV_ACCRUAL,IV_ACCRUALPERIODSTARTDATE,IV_ACCRUALPERIODENDDATE,IV_PLANENROLLMENTSTARTDATE,IV_PLANENROLLMENTENDDATE,IV_CALEDARSTARTDATE,IV_CALEDARENDDATE

ln_hr_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)

ld_effective_date= GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00'(date))

ln_leg_group_id = GET_CONTEXT(LEGISLATIVE_DATA_GROUP_ID,0)

LN_PERSON_ID = GET_CONTEXT(PERSON_ID,-1)

ld_hire_date=ACP_HIRE_DATE

ld_term_date=ACP_TERMINATION_DATE

/*to check if hire date is on or before 15th of the month*/

ld_st_date= GREATEST(GREATEST(ld_hire_date,IV_ACCRUALPERIODSTARTDATE),IV_PLANENROLLMENTSTARTDATE)

ln_st_day = to_number(to_char(ld_st_date,'DD'))

if ln_st_day > 15 THEN

(

ld_accrual_period_st_date = ADD_DAYS(LAST_DAY(ld_st_date),1)/*accrual start date is from next month*/

)

else

(

ld_accrual_period_st_date = ADD_MONTHS(ADD_DAYS(LAST_DAY(ld_st_date),1),-1)/*accrual start date should be from 1st of current month*/

)

/*to check if end date is on or after 15th of the month*/

ld_end_date= LEAST(LEAST(ld_term_date,IV_ACCRUALPERIODENDDATE),IV_PLANENROLLMENTENDDATE)

ln_end_day= to_number(to_char(ld_end_date,'DD'))

if ln_end_day <= 15 THEN

(

ld_accrual_period_end_date = ADD_MONTHS(LAST_DAY(ld_end_date),-1)

)

else

(

ld_accrual_period_end_date = LAST_DAY(ld_end_date)

)

ln_term_duration = 1 + (TO_NUMBER(TO_CHAR(IV_CALEDARENDDATE, 'yy')) - TO_NUMBER(TO_CHAR(IV_CALEDARSTARTDATE, 'yy'))) * 12 + (TO_NUMBER(TO_CHAR(IV_CALEDARENDDATE, 'mm')) - TO_NUMBER(TO_CHAR(IV_CALEDARSTARTDATE, 'mm')))

/*prorationFactor = months_between(ADD_DAYS(ld_accrual_period_end_date,1),ld_accrual_period_st_date)/12*/

ln_participation_duration = months_between(ADD_DAYS(ld_accrual_period_end_date,1),ld_accrual_period_st_date)

ln_prorated_accrual = IV_ACCRUAL * (ln_participation_duration / ln_term_duration)

accrual = round(ln_prorated_accrual)

RETURN accrual