Fusion Absence Management (Release 9.

2)

Sample Fast Formulas

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

FORMULA NAME: XX_ANC_ACCRUAL_XX_ExtendedChildCare

FORMULA TYPE: Global Absence Accrual
DESCRIPTION: This formula returns the accrual for employees enrolled in Extended Child Care
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 10-Apr-2015 Initial Version
*******************************************************************************/
DEFAULT FOR PER_ASG_FTE IS 1
DEFAULT FOR PER_REL_ORIGINAL_DATE_OF_HIRE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_REL_ACTUAL_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)
ln_accrual = 0
ld_effective_date=PER_REL_ORIGINAL_DATE_OF_HIRE
ld_emp_term_date =PER_REL_ACTUAL_TERMINATION_DATE
ld_current_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))
ld_term_start_date = TO_DATE('01-01-'||TO_CHAR(ld_effective_date, 'rrrr'),'dd-mm-rrrr')
ld_term_end_date = TO_DATE('31-12-'||TO_CHAR(ld_effective_date, 'rrrr'),'dd-mm-rrrr')
ld_term_duration = DAYS_BETWEEN (ld_term_end_date, ld_term_start_date)
ld_hire_year = TO_NUMBER(TO_CHAR(ld_effective_date,'rrrr'))
ld_curr_year = TO_NUMBER(TO_CHAR(ld_current_date,'rrrr'))
accrual = 0
IF (ld_curr_year = ld_hire_year)
THEN
(
IF (TO_NUMBER(TO_CHAR(ld_effective_date,'MM')) < 7)
THEN
(
accrual = 2

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

)
IF (TO_NUMBER(TO_CHAR(ld_effective_date,'MM')) >= 7 AND TO_NUMBER(TO_CHAR(ld_effective_date,'MM')) <= 12)
THEN
(
accrual = 1
)
)
IF (ld_curr_year > ld_hire_year)
THEN
(accrual = 2)
ceiling = 2
carryover=0
vestingUnits=3
vestingUOM='M'
RETURN accrual,ceiling,carryover,vestingUnits,vestingUOM
/******************************************************************************
FORMULA NAME: XX_ANC_ACCRUAL_XX_FamilyLeave
FORMULA TYPE: Global Absence Accrual
DESCRIPTION: This formula returns the accrual for employees enrolled Family Leave
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 10-Apr-2015 Initial Version
*******************************************************************************/
DEFAULT FOR PER_ASG_FTE IS 1
DEFAULT FOR PER_REL_ORIGINAL_DATE_OF_HIRE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_REL_ACTUAL_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

ln_accrual = 0
ld_effective_date=PER_REL_ORIGINAL_DATE_OF_HIRE
ld_emp_term_date =PER_REL_ACTUAL_TERMINATION_DATE
ld_current_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))
ld_term_start_date = TO_DATE('01-01-'||TO_CHAR(ld_effective_date, 'rrrr'),'dd-mm-rrrr')
ld_term_end_date = TO_DATE('31-12-'||TO_CHAR(ld_effective_date, 'rrrr'),'dd-mm-rrrr')
ld_term_duration = DAYS_BETWEEN (ld_term_end_date, ld_term_start_date)
ld_hire_year = TO_NUMBER(TO_CHAR(ld_effective_date,'rrrr'))
ld_curr_year = TO_NUMBER(TO_CHAR(ld_current_date,'rrrr'))
accrual = 0
IF (ld_curr_year = ld_hire_year)
THEN
(
IF (TO_NUMBER(TO_CHAR(ld_effective_date,'MM')) < 7)
THEN
(
accrual = 2
)
IF (TO_NUMBER(TO_CHAR(ld_effective_date,'MM')) >= 7 AND TO_NUMBER(TO_CHAR(ld_effective_date,'MM')) <= 12)
THEN
(
accrual = 1
)
)
IF (ld_curr_year > ld_hire_year)
THEN
(accrual = 2)
ceiling = 2
carryover=0

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

vestingUnits=3
vestingUOM='M'
RETURN accrual,ceiling,carryover,vestingUnits,vestingUOM

/******************************************************************************
FORMULA NAME: XX_ANC_ACCRUAL_XX_ChildCare
FORMULA TYPE: Global Absence Accrual
DESCRIPTION: This formula returns the accrual for employees enrolled in Child Care
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 10-Apr-2015 Initial Version
*******************************************************************************/
DEFAULT FOR PER_ASG_FTE IS 1
DEFAULT FOR PER_REL_ORIGINAL_DATE_OF_HIRE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_REL_ACTUAL_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)
ln_accrual = 0
ld_effective_date=PER_REL_ORIGINAL_DATE_OF_HIRE
ld_emp_term_date =PER_REL_ACTUAL_TERMINATION_DATE
ld_current_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))
ld_term_start_date = TO_DATE('01-01-'||TO_CHAR(ld_effective_date, 'rrrr'),'dd-mm-rrrr')
ld_term_end_date = TO_DATE('31-12-'||TO_CHAR(ld_effective_date, 'rrrr'),'dd-mm-rrrr')
ld_term_duration = DAYS_BETWEEN (ld_term_end_date, ld_term_start_date)
ld_hire_year = TO_NUMBER(TO_CHAR(ld_effective_date,'rrrr'))
ld_curr_year = TO_NUMBER(TO_CHAR(ld_current_date,'rrrr'))
accrual = 0
IF (ld_curr_year = ld_hire_year)

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

THEN
(
IF (TO_NUMBER(TO_CHAR(ld_effective_date,'MM')) < 7)
THEN
(
accrual = 6
)
IF (TO_NUMBER(TO_CHAR(ld_effective_date,'MM')) >= 7 AND TO_NUMBER(TO_CHAR(ld_effective_date,'MM')) <= 12)
THEN
(
accrual = 3
)
)
IF (ld_curr_year > ld_hire_year)
THEN
(accrual = 6)
ceiling = 6
carryover=0
vestingUnits=3
vestingUOM='M'

RETURN accrual,ceiling,carryover,vestingUnits,vestingUOM
/******************************************************************************
FORMULA NAME: XX_ANC_GET_OFFLIEU_INDIVIDUAL_LEAVE_BALANCE
FORMULA TYPE: Global Absence Accrual
DESCRIPTION: This formula returns the leave balance for Off-in-Lieu (Individually earned)
Change History:
Name
Date
Comments
-----------------------------------------------

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

Ashish Harbhajanka 20-Apr-2015 Initial Version

*******************************************************************************/
INPUTS ARE IV_PERSON_ID,IV_TERM_ID,IV_EFF_DT (DATE)
ld_current_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))
L_Leave_Balance = 0
ln_off_per_plan_id = 300000001752614
L_Term_Id = IV_TERM_ID
L_Person_Id = IV_PERSON_ID
L_Eff_Dt = IV_EFF_DT

/* Accrual Plan Id for Off-in-Lieu (Individually earned)*/

CHANGE_CONTEXTS(ACCRUAL_PLAN_ID = ln_off_per_plan_id,HR_ASSIGNMENT_ID = L_Term_Id,PERSON_ID = L_Person_Id,EFFECTIVE_DATE =L_Eff_Dt )

(
L_Leave_Balance = GET_ACCRUAL_BALANCE()
)
return L_Leave_Balance

/******************************************************************************
FORMULA NAME: XX_ANC_GET_OFFLIEU_PUBLIC_LEAVE_BALANCE
FORMULA TYPE: Global Absence Accrual
DESCRIPTION: This formula returns the leave balance for Off-in-Lieu (Public Holiday)
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 20-Apr-2015 Initial Version
*******************************************************************************/
INPUTS ARE IV_PERSON_ID,IV_TERM_ID,IV_EFF_DT (DATE)

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

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

L_Leave_Balance = 0
ln_off_public_plan_id = 300000001752613
L_Term_Id = IV_TERM_ID
L_Person_Id = IV_PERSON_ID
L_Eff_Dt = IV_EFF_DT

/* Accrual Plan Id for Off-in-Lieu (Public Holiday)*/

CHANGE_CONTEXTS(ACCRUAL_PLAN_ID = ln_off_public_plan_id,HR_ASSIGNMENT_ID = L_Term_Id,PERSON_ID = L_Person_Id,EFFECTIVE_DATE =L_Eff_Dt )

(
L_Leave_Balance = GET_ACCRUAL_BALANCE()
)
return L_Leave_Balance
/******************************************************************************
FORMULA NAME: XX_ANC_GET_ANNUAL_LEAVE_BALANCE
FORMULA TYPE: Global Absence Accrual
DESCRIPTION: This formula returns the leave balance for Annual Leave - Non Manager
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 20-Apr-2015 Initial Version
*******************************************************************************/
INPUTS ARE IV_PERSON_ID,IV_TERM_ID,IV_EFF_DT (DATE)
ln_accrual = 0
ld_current_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))
L_Leave_Balance = 0
ln_annual_accrual_plan_id = 300000001752602 /*Accrual Plan Id for Annual Leave Plan (Non- Manager) */

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

L_Term_Id = IV_TERM_ID
L_Person_Id = IV_PERSON_ID
L_Eff_Dt = IV_EFF_DT
CHANGE_CONTEXTS(ACCRUAL_PLAN_ID = ln_annual_accrual_plan_id,HR_ASSIGNMENT_ID = L_Term_Id,PERSON_ID = L_Person_Id,EFFECTIVE_DATE
=L_Eff_Dt )
(
L_Leave_Balance = GET_ACCRUAL_BALANCE()
)
return L_Leave_Balance
/*******************************************************************
FORMULA NAME: XX_ANC_XX_ACCMAT_Annual
FORMULA TYPE: Global Absence Accrual Matrix
DESCRIPTION: This sample formula will find the weighted average of the Accrual value based on the number of the days the employee was eligible in a
particular band. For example, I an employee has changed from Band 1 to Band 2 on 03-Mar-2014 and accrual value for Band 1 and Band 2 is 100 and 200
respectively. The formula will return:
accrual =
(100*(number of days employee in Band1) + 200*(number of days Employee in Band 2))/365
Change History:
Name
Date
Comments
-------------------------------------------------------------------------------------------------------------------Ashish Harbhajanka
12-May-2015
Initial Version
-------------------------------------------------------------------------------------------------------------------***********************************************************************/
/*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/
DEFAULT for PER_ASG_REL_ORIGINAL_DATE_OF_HIRE is '4712/12/31 00:00:00' (date)
Default for IV_BAND_CHG_DT1 is '1951/01/01 00:00' (date)
Default for IV_BAND_CHG_DT2 is '1951/01/01 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_BAND_CHG_BEFVAL1 is 15

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

Default for IV_BAND_CHG_AFTVAL1 is 18

Default for IV_BAND_CHG_AFTVAL2 is 21
DEFAULT FOR PER_ASG_PERSON_NUMBER IS 'X'
Default for IV_CEILING is 0
Default for IV_CARRYOVER is 0
Inputs are
IV_ACCRUAL,IV_BAND_CHG_DT1,IV_BAND_CHG_DT2,IV_BAND_CHG_BEFVAL1,IV_BAND_CHG_AFTVAL1,IV_BAND_CHG_BEFVAL2,IV_BAND_CHG_AFTVAL2,
IV_CARRYOVER,IV_CEILING,IV_ACCRUALPERIODSTARTDATE,IV_ACCRUALPERIODENDDATE
/*=========== DATABASE ITEM DEFAULTS ENDS======================*/
/*================ FORMULA SECTION BEGIN =======================*/
ld_effective_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))
ld_term_start_date = TO_DATE('01-01-'||TO_CHAR(ld_effective_date, 'rrrr'),'dd-mm-rrrr')
ld_term_end_date = TO_DATE('31-12-'||TO_CHAR(ld_effective_date, 'rrrr'),'dd-mm-rrrr')
ld_band1_end_date = ADD_YEARS(PER_ASG_REL_ORIGINAL_DATE_OF_HIRE,3)
ld_band2_end_date = ADD_YEARS(PER_ASG_REL_ORIGINAL_DATE_OF_HIRE,6)
accrual = IV_ACCRUAL
l_log = ess_log_write('***********************START********************************')
l_log = ess_log_write('PERSON_NUMBER : '|| PER_ASG_PERSON_NUMBER)
l_log = ess_log_write('ld_effective_date : '|| TO_CHAR(ld_effective_date,'DD-MM-YYYY'))
l_log = ess_log_write('ld_band1_end_date : '|| TO_CHAR(ld_band1_end_date,'DD-MM-YYYY'))
l_log = ess_log_write('ld_band2_end_date : '|| TO_CHAR(ld_band2_end_date,'DD-MM-YYYY'))
l_log = ess_log_write('IV_BAND_CHG_BEFVAL1 : '|| TO_CHAR(IV_BAND_CHG_BEFVAL1))
l_log = ess_log_write('IV_BAND_CHG_AFTVAL1 : '|| TO_CHAR(IV_BAND_CHG_AFTVAL1))
l_log = ess_log_write('IV_BAND_CHG_AFTVAL2 : '|| TO_CHAR(IV_BAND_CHG_AFTVAL2))
l_log = ess_log_write('Initial Accrual : '|| TO_CHAR(IV_ACCRUAL))
l_log = ess_log_write('Accrual Period Start Date : '|| TO_CHAR(IV_ACCRUALPERIODSTARTDATE,'DD-MON-RRRR'))
l_log = ess_log_write('Accrual Period End Date : '|| TO_CHAR(IV_ACCRUALPERIODENDDATE,'DD-MON-RRRR'))
l_log = ess_log_write('***********************END********************************')

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

IF (TO_CHAR(ld_effective_date,'YYYY') = TO_CHAR(ld_band1_end_date,'YYYY')) /* (TO_CHAR(ld_effective_date,'YYYY') =

TO_CHAR(IV_BAND_CHG_DT1,'YYYY')) */
THEN
(
l_log = ess_log_write('Within Band 1')
l_no_of_ds1 = DAYS_BETWEEN(ld_band1_end_date,ld_term_start_date) + 1
l_log = ess_log_write('l_no_of_ds1 : '|| TO_CHAR(l_no_of_ds1))
l_no_of_ds2 = DAYS_BETWEEN(ld_term_end_date,ld_band1_end_date) + 1
l_log = ess_log_write('l_no_of_ds2 : '|| TO_CHAR(l_no_of_ds2))
l_no_of_ds3 = 0
l_log = ess_log_write('IV_BAND_CHG_BEFVAL1 : '|| TO_CHAR(IV_BAND_CHG_BEFVAL1))
l_log = ess_log_write('IV_BAND_CHG_AFTVAL1 : '|| TO_CHAR(IV_BAND_CHG_AFTVAL1))
l_log = ess_log_write('IV_BAND_CHG_AFTVAL2 : '|| TO_CHAR(IV_BAND_CHG_AFTVAL2))
accrual=(l_no_of_ds1*IV_BAND_CHG_BEFVAL1 + l_no_of_ds2 * IV_BAND_CHG_AFTVAL1 + l_no_of_ds3*IV_BAND_CHG_AFTVAL2)/ 365
l_log = ess_log_write('Accrual : '|| TO_CHAR(accrual))
)
IF (TO_CHAR(ld_effective_date,'YYYY') = TO_CHAR(ld_band2_end_date,'YYYY')) /*(TO_CHAR(ld_effective_date,'YYYY') =
TO_CHAR(IV_BAND_CHG_DT2,'YYYY'))*/
THEN
(
l_log = ess_log_write('Within Band 2')
l_no_of_ds1 = 0
l_no_of_ds2 = DAYS_BETWEEN(ld_band2_end_date,ld_term_start_date) + 1
l_log = ess_log_write('l_no_of_ds2 : '|| TO_CHAR(l_no_of_ds2))
l_no_of_ds3 = DAYS_BETWEEN(ld_term_end_date,ld_band2_end_date) + 1
l_log = ess_log_write('l_no_of_ds3 : '|| TO_CHAR(l_no_of_ds3))
l_log = ess_log_write('IV_BAND_CHG_BEFVAL1 : '|| TO_CHAR(IV_BAND_CHG_BEFVAL1))
l_log = ess_log_write('IV_BAND_CHG_AFTVAL1 : '|| TO_CHAR(IV_BAND_CHG_AFTVAL1))
l_log = ess_log_write('IV_BAND_CHG_AFTVAL2 : '|| TO_CHAR(IV_BAND_CHG_AFTVAL2))
accrual=(l_no_of_ds1*IV_BAND_CHG_BEFVAL1 + l_no_of_ds2 * IV_BAND_CHG_AFTVAL1 + l_no_of_ds3*IV_BAND_CHG_AFTVAL2)/ 365
l_log = ess_log_write('Accrual : '|| TO_CHAR(accrual))
)

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

l_log = ess_log_write(' Prorated Accrual considered : '|| TO_CHAR(accrual))

floored_accrual = FLOOR(accrual)
decimal_accrual = accrual - floored_accrual
IF (decimal_accrual < 0.5 )
THEN
accrual = floored_accrual
IF (decimal_accrual >= 0.5)
THEN
accrual = floored_accrual + 1

l_log = ess_log_write(' Final Accrual considered : '|| TO_CHAR(accrual))

carryover = IV_CARRYOVER
ceiling = IV_CEILING
l_log = ess_log_write(' Carryover : '|| TO_CHAR(IV_CARRYOVER))
l_log = ess_log_write(' Ceiling : '|| TO_CHAR(IV_CEILING))
return accrual,ceiling,carryover
/******************************************************************************
FORMULA NAME: XX_ANC_XXPC_ACCMAT_LongServiceLeave
FORMULA TYPE: Global Absence Accrual Matrix
DESCRIPTION: This formula returns the accrual for employees enrolled into Long Service Leave
Change History:
Name
Date
Comments
---------------------------------------------------------------------------Ashish Harbhajanka 21-May-2015 Initial Version
*******************************************************************************/
DEFAULT FOR PER_TERM_REL_LENGTH_OF_SERVICE IS 0
DEFAULT FOR PER_PER_ADD_REGION2 IS ' '

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

DEFAULT FOR PER_TERM_REL_DATE_START is '4712/12/31 00:00:00' (date)

DEFAULT FOR PER_ASG_REL_DATE_START is '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_ASG_DISPLAY_NAME is ' '
DEFAULT FOR IV_ACCRUAL is 0
DEFAULT FOR IV_CEILING is 0
DEFAULT FOR IV_CARRYOVER is 0
INPUTS are IV_ACCRUAL,IV_CEILING,IV_CARRYOVER
ld_effective_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))
ln_los_years = TRUNC(MONTHS_BETWEEN(ld_effective_date,PER_ASG_REL_DATE_START)/12,2)
ln_cons_5years = FLOOR(PER_TERM_REL_LENGTH_OF_SERVICE/5)
ln_cons_years = FLOOR(PER_TERM_REL_LENGTH_OF_SERVICE) - 10
ln_length_of_service = PER_TERM_REL_LENGTH_OF_SERVICE
lc_emp_addr_state = PER_PER_ADD_REGION2
ln_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)
ln_person_id = GET_CONTEXT(PERSON_ID,0)
ln_years_between = TO_NUMBER(TO_CHAR(ld_effective_date,'YYYY')) - TO_NUMBER(TO_CHAR(PER_ASG_REL_DATE_START,'YYYY'))
ln_div_floor = FLOOR(ln_years_between/5)
ln_div = ln_years_between/5
ln_div_dec = ln_div - ln_div_floor
ln_rem = MOD(ln_years_between,5)
ln_above10years = ln_years_between - 10
ln_cons_5years_mod = MOD(ln_above10years,5)
ln_cons_5years_div = FLOOR(ln_above10years/5)
accrual = IV_ACCRUAL
ceiling = IV_CEILING
carryover = IV_CARRYOVER
l_log = ess_log_write('Before IF LOOP ')
l_log = ess_log_write('Start Processing for Person ID : ' || TO_CHAR(ln_person_id))
l_log = ess_log_write('Start Processing for Assignment ID : ' || TO_CHAR(ln_assignment_id))

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

l_log = ess_log_write('Start Processing for : ' || PER_ASG_DISPLAY_NAME)

l_log = ess_log_write('Employee Address State : ' || lc_emp_addr_state)
l_log = ess_log_write('Years of Service : ' || TO_CHAR(ln_years_between))
l_log = ess_log_write('ln_cons_years : ' || TO_CHAR(ln_cons_years))
l_log = ess_log_write('ln_cons_5years : ' || TO_CHAR(ln_cons_5years))
l_log = ess_log_write('ln_cons_5years_mod : ' || TO_CHAR(ln_cons_5years_mod))
l_log = ess_log_write('ln_cons_5years_div : ' || TO_CHAR(ln_cons_5years_div))
l_log = ess_log_write('Accrual : ' || TO_CHAR(accrual))

IF (lc_emp_addr_state = 'New South Wales' or lc_emp_addr_state = 'Queensland' or lc_emp_addr_state = 'Western Australia' or lc_emp_addr_state =
'Victoria')
THEN
(
IF (ln_years_between = 10)
THEN
(
accrual = 325
l_log = ess_log_write('Step 1 : ' || TO_CHAR(accrual))
l_log = ess_log_write('Accrual : ' || TO_CHAR(accrual))
)
IF (ln_years_between > 10 AND ln_cons_5years_mod = 0 AND ln_cons_5years_div > 0)
THEN
(
accrual = 162.5
l_log = ess_log_write('Step 2 : ' || TO_CHAR(accrual))
l_log = ess_log_write('Accrual : ' || TO_CHAR(accrual))
)
)
ELSE
(
IF (lc_emp_addr_state = 'South Australia')
THEN

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

IF (ln_years_between = 10 )
THEN
accrual = 487.5
IF (ln_years_between > 10)
THEN
accrual = 48.75
)
l_log = ess_log_write('After IF LOOP ')
l_log = ess_log_write('Employee Address State : ' || lc_emp_addr_state)
l_log = ess_log_write('Years of Service : ' || TO_CHAR(ln_years_between))
l_log = ess_log_write('ln_cons_years : ' || TO_CHAR(ln_cons_years))
l_log = ess_log_write('ln_cons_5years : ' || TO_CHAR(ln_cons_5years))
l_log = ess_log_write('ln_cons_5years_mod : ' || TO_CHAR(ln_cons_5years_mod))
l_log = ess_log_write('ln_cons_5years_div : ' || TO_CHAR(ln_cons_5years_div))
l_log = ess_log_write('Accrual : ' || TO_CHAR(accrual))
l_log = ess_log_write('End Processing for Person ID : ' || TO_CHAR(ln_person_id))
l_log = ess_log_write('End Processing for Assignment ID : ' || TO_CHAR(ln_assignment_id))
l_log = ess_log_write('End Processing for : ' || PER_ASG_DISPLAY_NAME)
RETURN accrual,ceiling,carryover
/*'New South Wales', 'Queensland', 'South Australia','Western Australia', 'Victoria'*/
/******************************************************************************
FORMULA NAME: XX_ANC_XX_VALID_Paternity
FORMULA TYPE: Global Absence Entry Validation
DESCRIPTION: This formula returns the Paternity Leave Criteria. Only workers who have less than 3 dependent children are eligible
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 07-Apr-2015 Initial Version
*******************************************************************************/
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_PERSON_ID IS 0

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_CONTACT_TYPE IS ' '

DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_DATE_OF_BIRTH IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE for ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR is 0
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0

/*=========== DATABASE ITEM DEFAULTS END =====================*/

/*================ FORMULA SECTION BEGIN =======================*/
/*==============INPUTS SECTION BEGIN================*/
INPUTS ARE iv_start_date (date),
iv_end_date (date),
IV_TOTALDURATION

/*==============INPUTS SECTION BEGIN================*/

ln_pl_type_id = GET_CONTEXT(ABSENCE_TYPE_ID,0) /* pl stands for Paternity Leave */
ld_child_birth_date = TO_DATE('1951-01-01','yyyy-MM-dd')
ld_start_date = TO_DATE('1951-01-01','yyyy-MM-dd')
j=1
l_new_duration = IV_TOTALDURATION
ln_child_count = 0
ln_rem = MOD(IV_TOTALDURATION,0.5)
IF (ln_rem <> 0)
THEN
(
VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_SG_ERR_DURCAL' /* 'Leave Duration should either be Half/Full Day.Please change absence start and end dates
accordingly' */
RETURN VALID,ERROR_MESSAGE

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

CHANGE_CONTEXTS(START_DATE=iv_start_date, END_DATE=iv_end_date)
(
i=PER_PER_CONTACT_PERSON_ID.FIRST(-1)
WHILE PER_PER_CONTACT_PERSON_ID.exists(i)
LOOP
(
if(PER_PER_CONTACT_CONTACT_TYPE[i] = 'C' or PER_PER_CONTACT_CONTACT_TYPE[i] = 'A')
then
(
ln_child_count = ln_child_count + 1
ld_child_birth_date = PER_PER_CONTACT_DATE_OF_BIRTH[i]
IF ( ld_child_birth_date < PER_PER_CONTACT_DATE_OF_BIRTH[i])
THEN
ld_child_birth_date = PER_PER_CONTACT_DATE_OF_BIRTH[i]
)
i= i+1
)
if(ln_child_count => 3 ) /*ln_child_count > 3*/
THEN
(valid = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_PATERNITY' /*'This leave is only allowed upto 3 childrens'*/
RETURN valid,ERROR_MESSAGE
)
if (ln_child_count = 0)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_PAT_0KIDS' /* 'You can only avail this leave if you have dependent/adopted children' */
RETURN VALID,ERROR_MESSAGE

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

)
else(
valid='Y'
)
)
CHANGE_CONTEXTS(START_DATE=ld_start_date, END_DATE=IV_START_DATE)
(
WHILE ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR.exists(j)
LOOP
(
CHANGE_CONTEXTS (ABSENCE_ENTRY_ID = ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR[j])
(
IF (ln_pl_type_id = ANC_ABS_ENTRS_ABSENCE_TYPE_ID)
THEN
(
IF (DAYS_BETWEEN(IV_START_DATE,ld_child_birth_date) > 365 OR DAYS_BETWEEN(IV_START_DATE,ld_child_birth_date) < 0)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_FLEX_FROM_CHILDBIRTH' /* This leave needs to be applied within 1 year of chilbirth */
RETURN VALID,ERROR_MESSAGE
)
)

)
j=j+1
)
)
/*================ FORMULA SECTION END =======================*/
return VALID,ERROR_MESSAGE

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

/******************************************************************************
FORMULA NAME: XX_ANC_XX_VALID_Marriage
FORMULA TYPE: Global Absence Entry Validation
DESCRIPTION: This formula returns the Marriage Leave Criteria. Only workers who are single are allowed to use this leave type.This Leave can be availed
just once
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 07-Apr-2015 Initial Version
*******************************************************************************/
/*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/
DEFAULT for ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0
DEFAULT for PER_PER_MARITAL_STATUS is ' '
DEFAULT for ANC_ABS_ENTRS_DURATION is 0
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_STATUS_CD is ' '
DEFAULT FOR IV_TOTALDURATION is 0
DEFAULT_DATA_VALUE for ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR is 0
DEFAULT for ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0

/*=========== DATABASE ITEM DEFAULTS END =====================*/

/*================ FORMULA SECTION BEGIN =======================*/
/*==============INPUTS SECTION BEGIN================*/
INPUTS ARE iv_start_date (date),
iv_end_date (date),
IV_TOTALDURATION

/*==============INPUTS SECTION BEGIN================*/

l_cuurent_year=to_char(Get_context(EFFECTIVE_DATE, '1999/01/01 12:00:00' (date)),'YYYY')

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

l_start_date=to_date(l_cuurent_year+'/01/01')
l_end_date=to_date(l_cuurent_year+'/12/31')
l_absence_type_id=GET_CONTEXT(ABSENCE_TYPE_ID,0)
l_current_duration=days_between(iv_end_date,iv_start_date)+1
l_days=0
l_days_duration =0
ln_occurence = 1
l_total_days = 0
l_new_duration = IV_TOTALDURATION
lc_marital_status = PER_PER_MARITAL_STATUS
ln_rem = MOD(IV_TOTALDURATION,0.5)
IF (ln_rem <> 0)
THEN
(
VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_DURCAL' /* 'Leave Duration should either be Half/Full Day.Please change absence start and end dates
accordingly' */
RETURN VALID,ERROR_MESSAGE
)

CHANGE_CONTEXTS(START_DATE=l_start_date, END_DATE=l_end_date)
(
i=ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR.FIRST(-1)
WHILE ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR.exists(i)
LOOP
(
CHANGE_CONTEXTS(ABSENCE_ENTRY_ID=ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR[i])
(
if((l_absence_type_id=ANC_ABS_ENTRS_ABSENCE_TYPE_ID) AND (ANC_ABS_ENTRS_ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'))

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

then
(
l_days_duration=ANC_ABS_ENTRS_DURATION
ln_occurence = ln_occurence + 1
)
l_days=l_days_duration+l_days
)
i= ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR.NEXT(I,-1)
)
if(ln_occurence > 1)
THEN
(valid = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_FIRST_MARRIAGE' /*'This Leave is allowed only for the First Legal Marriage' */
RETURN valid,ERROR_MESSAGE
)
l_total_days = l_days + l_new_duration
if(lc_marital_status <> 'S')
then
(valid='N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_MARRIAGE_ELIG' /*'Only Single and unmarried workers can avail this leave'*/
)
else(
valid='Y'
)
)

/*================ FORMULA SECTION END =======================*/

return VALID,ERROR_MESSAGE

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

/******************************************************************************
FORMULA NAME: XX_ANC_XX_VALID_FlexMaternity
FORMULA TYPE: Global Absence Entry Validation
DESCRIPTION: This formula returns validates the Flex Maternity Leave entry
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 05-May-2015 Initial Version
*******************************************************************************/
DEFAULT FOR IV_START_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_END_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE for ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR is 0
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_PERSON_ID IS 0
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_CONTACT_TYPE IS ' '
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_DATE_OF_BIRTH IS '4712/12/31 00:00:00' (date)
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0
DEFAULT FOR ANC_ABS_MTRNT_ACTUAL_CHILD_BIRTH_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR ANC_ABS_MTRNT_ACTUAL_RETURN_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR ANC_ABS_MTRNT_ACTUAL_DURATION IS 112
DEFAULT FOR ANC_ABS_ENTRS_DURATION IS 0
DEFAULT FOR ANC_ABS_TYP_NAME IS ' '
DEFAULT FOR ANC_ABS_ENTRS_START_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR ANC_ABS_ENTRS_END_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR ANC_ABS_ENTRS_APPROVAL_STATUS_CD is ' '
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_STATUS_CD is ' '
DEFAULT FOR ANC_ABS_ENTRS_PERSON_ID is 0

INPUTS ARE IV_END_DATE (date), IV_START_DATE (date)

lc_resource_type = 'ASSIGN'
ld_period_start_date = IV_START_DATE

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

ld_period_end_date = IV_END_DATE
lc_use_sch_asg = 'Y'
lc_use_sch_inh = 'Y'
lc_include_noshift = 'N'
lc_include_calevents = 'N'
lc_calc_units = 'D'
ln_curr_fmla_duration = 0
lc_mat_8weeks = 'Maternity Leave (8 Weeks)'
lc_mat_16weeks = 'Maternity Leave (16 weeks)'
ln_fml_type_id = GET_CONTEXT(ABSENCE_TYPE_ID,0) /*fml stands for Flexible Maternity Leave */
l_person_id = GET_CONTEXT(PERSON_ID,0) /* Person ID */
ln_fml_count = 0
ln_counter = 0
ln_child_count = 0
ld_start_date = TO_DATE('1951-01-01','yyyy-MM-dd')
ld_end_date = TO_DATE('4712-12-31','yyyy-MM-dd')
ld_maternity_end_date = TO_DATE('4712-12-31','yyyy-MM-dd')
ld_current_date = TO_DATE(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE)
ld_child_birth_date = TO_DATE('1951-01-01','yyyy-MM-dd')
j=1
k=1
CHANGE_CONTEXTS(START_DATE=iv_start_date, END_DATE=iv_end_date)
(
i=PER_PER_CONTACT_PERSON_ID.FIRST(-1)
WHILE PER_PER_CONTACT_PERSON_ID.exists(i)
LOOP
(
IF (PER_PER_CONTACT_CONTACT_TYPE[i] = 'C' )
THEN

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

(
ln_child_count = ln_child_count + 1
/* Logic to fetch Youngest Child Birth Date */
ld_child_birth_date = PER_PER_CONTACT_DATE_OF_BIRTH[i]
IF ( ld_child_birth_date < PER_PER_CONTACT_DATE_OF_BIRTH[i])
THEN
ld_child_birth_date = PER_PER_CONTACT_DATE_OF_BIRTH[i]
)
ELSE
(ln_child_count = ln_child_count)
i= i+1
)
if (ln_child_count = 0)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_MAT_0KIDS' /* 'You can only avail this leave if you have dependent children' */
RETURN VALID,ERROR_MESSAGE
)
)
CHANGE_CONTEXTS(START_DATE=ld_start_date, END_DATE=ld_end_date)
(
WHILE ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR.exists(j)
LOOP
(
CHANGE_CONTEXTS (ABSENCE_ENTRY_ID = ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR[j])
(
CHANGE_CONTEXTS (ABSENCE_TYPE_ID = ANC_ABS_ENTRS_ABSENCE_TYPE_ID)
(
IF ((ANC_ABS_TYP_NAME = lc_mat_8weeks OR ANC_ABS_TYP_NAME = lc_mat_16weeks) AND (ANC_ABS_ENTRS_ABSENCE_STATUS_CD <>
'ORA_WITHDRAWN'))

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

THEN (
ln_counter = ln_counter + 1
)
)
)
j=j+1
)
IF (ln_counter = 0)
THEN (VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_FLEX_FROM_MATERNITY' /* You can only avail this leave after availing Maternity Leave (8 weeks / 16 weeks) */
RETURN VALID, ERROR_MESSAGE)
/*
CHANGE_CONTEXTS (ABSENCE_ENTRY_ID = ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR[ln_counter])
(
IF (ANC_ABS_MTRNT_ACTUAL_CHILD_BIRTH_DATE WAS DEFAULTED)
THEN (VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_FLEX_DATEOFBIRTH'
RETURN VALID, ERROR_MESSAGE)
IF (DAYS_BETWEEN(ld_period_start_date, ANC_ABS_MTRNT_ACTUAL_CHILD_BIRTH_DATE) > 365 OR DAYS_BETWEEN(ld_period_start_date,
ANC_ABS_MTRNT_ACTUAL_CHILD_BIRTH_DATE) < 0)
THEN (VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_FLEX_FROM_MATERNITY'
RETURN VALID, ERROR_MESSAGE)
ELSE
(ld_maternity_end_date = ANC_ABS_MTRNT_ACTUAL_RETURN_DATE
ln_remaining_days = (16 - (ANC_ABS_MTRNT_ACTUAL_DURATION/7)) * 5
ln_fml_available = LEAST(ln_remaining_days, 40))
)
*/

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

)
CHANGE_CONTEXTS(START_DATE=ld_start_date, END_DATE=IV_START_DATE) /* Removed ld_maternity_end_date to ld_start_date */
(
WHILE ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR.exists(k)
LOOP
(
CHANGE_CONTEXTS (ABSENCE_ENTRY_ID = ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR[k])
(
IF (ln_fml_type_id = ANC_ABS_ENTRS_ABSENCE_TYPE_ID)
THEN
( ln_fml_count = ln_fml_count + ANC_ABS_ENTRS_DURATION
IF (DAYS_BETWEEN(IV_START_DATE,ld_child_birth_date) > 365 OR DAYS_BETWEEN(IV_START_DATE,ld_child_birth_date) < 0)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'FX_ANC_XX_FLEX_FROM_CHILDBIRTH' /* This leave needs to be applied within 1 year of chilbirth */
RETURN VALID,ERROR_MESSAGE
)
)

)
k=k+1
)
)
ln_curr_fmla_duration = GET_PAY_AVAILABILITY (lc_resource_type,
ld_period_start_date,
ld_period_end_date,
lc_use_sch_asg,
lc_use_sch_inh,
lc_include_noshift,
lc_include_calevents,

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

lc_calc_units)
ln_fmla_total= ln_curr_fmla_duration + ln_fml_count /* May need to add the current absence duration as required */
IF ln_fmla_total > 56
THEN (VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_FLEX_TOTALDAYS' /* You are only entitled to a maximum of 56 Days of Leaves for this leave type. */
RETURN VALID, ERROR_MESSAGE)
ELSE (VALID = 'Y')
RETURN VALID, ERROR_MESSAGE
/******************************************************************************
FORMULA NAME: XX_ANC_XX_VALID_Unpaid
FORMULA TYPE: Global Absence Entry Validation
DESCRIPTION: This formula validates the Unpaid leave entry for APAC region
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 13-Apr-2015 Initial Version
Ashish Harbhajanka 26-Jun-2015 Added Validation for Annual Leave Plan (Manager) and Annual Leave Plan (DTC Contract)
**********************************************************************************************************************/
DEFAULT FOR IV_START_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_END_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE for ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR is 0
DEFAULT for PER_ASG_REL_ORIGINAL_DATE_OF_HIRE is '1951/01/01 00:00:00' (date)
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0
DEFAULT FOR ANC_ABS_TYP_NAME IS ' '
DEFAULT FOR ANC_ABS_ENTRS_END_DATE IS '4712/12/31 00:00:00' (date)
INPUTS ARE IV_END_DATE (date), IV_START_DATE (date),IV_TOTALDURATION
lc_annual_name = 'Annual Leave Non Manager'

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

lc_off_lieu_public_name = 'Off-in-Lieu (Public Holiday)'

lc_off_lieu_per_name = 'Off-in-Lieu (Individually earned)'
lc_annual_mgr_name = 'Annual Leave Plan (Manager)'
lc_annual_dtc_contract_name = 'Annual Leave Plan (DTC Contract)'
ld_effective_date = IV_START_DATE
ln_person_id = GET_CONTEXT(PERSON_ID,0)
ln_term_id = GET_CONTEXT(HR_TERM_ID,0)
ln_annual_accrual_plan_id = 300000001752602
/*Accrual Plan Id for Annual Leave Plan - Non Manager*/
ln_off_public_plan_id = 300000001752613
/* Accrual Plan Id for Off-in-Lieu (Public Holiday)*/
ln_off_per_plan_id = 300000001752614
/* Accrual Plan Id for Off-in-Lieu (Individually earned)*/
ln_annual_mgr_accrual_plan_id = 300000001752603 /*Accrual Plan Id for Annual Leave Plan (Manager) */
ln_annual_dtc_contract_accrual_plan_id = 300000001752605 /*Accrual Plan Id for Annual Leave Plan (DTC Contract) */
ln_annual_leave_balance = 0
ln_off_public_leave_balance = 0
ln_off_per_leave_balance = 0
ln_annual_mgr_leave_balance = 0
ln_annual_dtc_contract_leave_balance = 0
ln_net_balance = 0
ln_rem = MOD(IV_TOTALDURATION,0.5)
IF (ln_rem <> 0)
THEN
(
VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_DURCAL' /* 'Leave Duration should either be Half/Full Day.Please change absence start and end dates
accordingly' */
RETURN VALID,ERROR_MESSAGE
)

SET_INPUT('IV_PERSON_ID', ln_person_id)

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

SET_INPUT('IV_TERM_ID', ln_term_id)
SET_INPUT('IV_EFF_DT', ld_effective_date)
EXECUTE('FX_ANC_GET_ANNUAL_LEAVE_BALANCE')
ln_annual_leave_balance = GET_OUTPUT('L_Leave_Balance', 0)
SET_INPUT('IV_PERSON_ID', ln_person_id)
SET_INPUT('IV_TERM_ID', ln_term_id)
SET_INPUT('IV_EFF_DT', ld_effective_date)
EXECUTE('FX_ANC_GET_OFFLIEU_PUBLIC_LEAVE_BALANCE')
ln_off_public_leave_balance = GET_OUTPUT('L_Leave_Balance', 0)

SET_INPUT('IV_PERSON_ID', ln_person_id)
SET_INPUT('IV_TERM_ID', ln_term_id)
SET_INPUT('IV_EFF_DT', ld_effective_date)
EXECUTE('FX_ANC_GET_OFFLIEU_INDIVIDUAL_LEAVE_BALANCE')
ln_off_per_leave_balance = GET_OUTPUT('L_Leave_Balance', 0)
SET_INPUT('IV_PERSON_ID', ln_person_id)
SET_INPUT('IV_TERM_ID', ln_term_id)
SET_INPUT('IV_EFF_DT', ld_effective_date)
EXECUTE('FX_ANC_GET_ANNUAL_MGR_LEAVE_BALANCE')
ln_annual_mgr_leave_balance = GET_OUTPUT('L_Leave_Balance', 0)
SET_INPUT('IV_PERSON_ID', ln_person_id)
SET_INPUT('IV_TERM_ID', ln_term_id)
SET_INPUT('IV_EFF_DT', ld_effective_date)
EXECUTE('FX_ANC_GET_ANNUAL_DTC_CONTRACT_LEAVE_BALANCE')
ln_annual_dtc_contract_leave_balance = GET_OUTPUT('L_Leave_Balance', 0)
ln_net_balance = ln_annual_leave_balance + ln_off_public_leave_balance + ln_off_per_leave_balance + ln_annual_mgr_leave_balance +
ln_annual_dtc_contract_leave_balance

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

IF (IV_END_DATE <= ADD_MONTHS(PER_ASG_REL_ORIGINAL_DATE_OF_HIRE,1))

THEN
( VALID = 'Y'
ERROR_MESSAGE = ' '
)
else
(
IF (ln_net_balance = 0)
THEN
(VALID = 'Y')
ELSE
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_MSG_UNPAID' /* 'You can only apply this leave after you have exhausted Annual and Off-in-Lieu Leaves' */
)
)
RETURN VALID, ERROR_MESSAGE
/******************************************************************************
FORMULA NAME: XX_ANC_XX_VALID_SharedParental
FORMULA TYPE: Global Absence Entry Validation
DESCRIPTION: This formula returns the Shared Parental Leave Criteria. Only workers who have at least one children are eligible.
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 04-May-2015 Initial Version
*******************************************************************************/
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_PERSON_ID IS 0
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_CONTACT_TYPE IS ' '
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_DATE_OF_BIRTH IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_DISPLAY_NAME is ' '
DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE is '47121231'
DEFAULT_DATA_VALUE for ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR is 0
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

/*=========== DATABASE ITEM DEFAULTS END =====================*/

/*================ FORMULA SECTION BEGIN =======================*/
/*==============INPUTS SECTION BEGIN================*/
INPUTS ARE iv_start_date (date),
iv_end_date (date),
IV_TOTALDURATION

/*==============INPUTS SECTION BEGIN================*/

l_new_duration = IV_TOTALDURATION
ln_child_count = 0
ld_effective_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00'(date))
ln_spl_type_id = GET_CONTEXT(ABSENCE_TYPE_ID,0) /*spl stands for Shared Parental Leave */
ld_child_birth_date = TO_DATE('1951-01-01','yyyy-MM-dd')
ld_start_date = TO_DATE('1951-01-01','yyyy-MM-dd')
/*ld_current_date = TO_DATE(DATE_TO_TEXT(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE),'YYYY/MM/DD HH24:MM:SS')*/
ld_current_date = TO_DATE(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE)
ln_rem = MOD(l_new_duration,0.5)
j=1
IF (ln_rem <> 0)
THEN
(
VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_DURCAL' /* 'Leave Duration should either be Half/Full Day.Please change absence start and end dates
accordingly' */
RETURN VALID,ERROR_MESSAGE

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

)
VALID = 'Y'
CHANGE_CONTEXTS(START_DATE=iv_start_date, END_DATE=iv_end_date)
(
i=PER_PER_CONTACT_PERSON_ID.FIRST(-1)
WHILE PER_PER_CONTACT_PERSON_ID.exists(i)
LOOP
(
if(PER_PER_CONTACT_CONTACT_TYPE[i] = 'C' or PER_PER_CONTACT_CONTACT_TYPE[i] = 'A')
then
(
ln_child_count = ln_child_count + 1
ld_child_birth_date = PER_PER_CONTACT_DATE_OF_BIRTH[i]
IF ( ld_child_birth_date < PER_PER_CONTACT_DATE_OF_BIRTH[i])
THEN
ld_child_birth_date = PER_PER_CONTACT_DATE_OF_BIRTH[i]
)
i= i+1
)
if (ln_child_count = 0)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_SHRD_PARENTAL' /* 'Please ensure that you have apply via the Shared Parental Leave Allocation System and
upload the letter to HR.' */
RETURN VALID,ERROR_MESSAGE
)
else
VALID = 'Y'
)

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

CHANGE_CONTEXTS(START_DATE=ld_start_date, END_DATE=IV_START_DATE)
(
WHILE ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR.exists(j)
LOOP
(
CHANGE_CONTEXTS (ABSENCE_ENTRY_ID = ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR[j])
(
IF (ln_spl_type_id = ANC_ABS_ENTRS_ABSENCE_TYPE_ID)
THEN
(
IF (DAYS_BETWEEN(IV_START_DATE,ld_child_birth_date) > 365 OR DAYS_BETWEEN(IV_START_DATE,ld_child_birth_date) < 0)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_FLEX_FROM_CHILDBIRTH' /* This leave needs to be applied within 1 year of chilbirth */
RETURN VALID,ERROR_MESSAGE
)
else
VALID = 'Y'
)
)
j=j+1
)
)
return VALID,ERROR_MESSAGE
/*================ FORMULA SECTION END =======================*/
/******************************************************************************
FORMULA NAME: XX_ANC_XX_VALID_ChildCare
FORMULA TYPE: Global Absence Entry Validation
DESCRIPTION: This formula returns the Child Care Leave Criteria. Only workers who have at least one children with age less than 7 years are eligible.
Change History:
Name
Date
Comments

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

----------------------------------------------Ashish Harbhajanka 30-Apr-2015 Initial Version

*******************************************************************************/
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_PERSON_ID IS 0
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_CONTACT_TYPE IS ' '
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_DATE_OF_BIRTH IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_DISPLAY_NAME is ' '
DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE is '47121231'

/*=========== DATABASE ITEM DEFAULTS END =====================*/

/*================ FORMULA SECTION BEGIN =======================*/
/*==============INPUTS SECTION BEGIN================*/
INPUTS ARE iv_start_date (date),
iv_end_date (date),
IV_TOTALDURATION

/*==============INPUTS SECTION BEGIN================*/

l_new_duration = IV_TOTALDURATION
ln_child_count = 0
ld_effective_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00'(date))
ld_yngst_child_birth_date = TO_DATE('1951-01-01','yyyy-MM-dd')
/*ld_current_date = TO_DATE(DATE_TO_TEXT(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE),'YYYY/MM/DD HH24:MM:SS')*/
ld_current_date = TO_DATE(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE)
ln_rem = MOD(l_new_duration,0.5)
IF (ln_rem <> 0)
THEN
(

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_DURCAL' /* 'Leave Duration should either be Half/Full Day.Please change absence start and end dates
accordingly' */
RETURN VALID,ERROR_MESSAGE
)
lc_eligible = 'N'
CHANGE_CONTEXTS(START_DATE=iv_start_date, END_DATE=iv_end_date)
(
i=PER_PER_CONTACT_PERSON_ID.FIRST(-1)
WHILE PER_PER_CONTACT_PERSON_ID.exists(i)
LOOP
(
if(PER_PER_CONTACT_CONTACT_TYPE[i] = 'C' or PER_PER_CONTACT_CONTACT_TYPE[i] = 'A')
then
(
ln_child_count = ln_child_count + 1
/* Adding Condition for Checking Child Age */
ld_child_compare_date = ADD_YEARS(PER_PER_CONTACT_DATE_OF_BIRTH[i],7)
ld_child_birth_date = PER_PER_CONTACT_DATE_OF_BIRTH[i]
IF (ld_yngst_child_birth_date < ld_child_birth_date)
THEN
(
ld_yngst_child_birth_date = ld_child_birth_date
ln_no_of_days = DAYS_BETWEEN(ld_effective_date,ld_child_birth_date)
ln_age = ROUND(ln_no_of_days/365,2)
)
)

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

i= i+1
)
/*Added by Ashish for Debugging */
/*End of Debugging Section */
if (ln_child_count = 0)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_PAT_0KIDS' /* 'You can only avail this leave if you have dependent/adopted children' */
RETURN VALID,ERROR_MESSAGE
)
if (ln_age > 7)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_CHILD_AGE_LESS7' /* You can only avail this leave if your youngest child is less than 7 years of age.'*/
RETURN VALID,ERROR_MESSAGE
)
else(
valid='Y'
)
)

/*================ FORMULA SECTION END =======================*/

return VALID,ERROR_MESSAGE
/******************************************************************************
FORMULA NAME: XX_ANC_XX_VALID_ExtendedChildCare
FORMULA TYPE: Global Absence Entry Validation
DESCRIPTION: This formula returns the Extended Child Care Leave Criteria.Only workers who have at least one children within age of 7-12 years are eligible.
Change History:

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 30-Apr-2015 Initial Version
*******************************************************************************/
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_PERSON_ID IS 0
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_CONTACT_TYPE IS ' '
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_DATE_OF_BIRTH IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_DISPLAY_NAME is ' '
DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE is '47121231'

/*=========== DATABASE ITEM DEFAULTS END =====================*/

/*================ FORMULA SECTION BEGIN =======================*/
/*==============INPUTS SECTION BEGIN================*/
INPUTS ARE iv_start_date (date),
iv_end_date (date),
IV_TOTALDURATION

/*==============INPUTS SECTION BEGIN================*/

l_new_duration = IV_TOTALDURATION
ln_child_count = 0
ld_effective_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00'(date))
ld_yngst_child_birth_date = TO_DATE('1951-01-01','yyyy-MM-dd')
ln_no_of_days = 0
ln_age = 0
ld_start_date = TO_DATE('1951-01-01','yyyy-MM-dd')
ld_end_date = TO_DATE('4712-12-31','yyyy-MM-dd')
/*ld_current_date = TO_DATE(DATE_TO_TEXT(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE),'YYYY/MM/DD HH24:MM:SS')*/
ld_current_date = TO_DATE(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE)

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

ln_rem = MOD(l_new_duration,0.5)
IF (ln_rem <> 0)
THEN
(
VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_DURCAL' /* 'Leave Duration should either be Half/Full Day.Please change absence start and end dates
accordingly' */
RETURN VALID,ERROR_MESSAGE
)

CHANGE_CONTEXTS(START_DATE=iv_start_date, END_DATE=iv_end_date)
(
i=PER_PER_CONTACT_PERSON_ID.FIRST(-1)
WHILE PER_PER_CONTACT_PERSON_ID.exists(i)
LOOP
(
if(PER_PER_CONTACT_CONTACT_TYPE[i] = 'C' or PER_PER_CONTACT_CONTACT_TYPE[i] = 'A')
then
(
ln_child_count = ln_child_count + 1
/* Adding Condition for Checking Child Age */
ld_child_compare_date = ADD_YEARS(PER_PER_CONTACT_DATE_OF_BIRTH[i],7)
ld_child_birth_date = PER_PER_CONTACT_DATE_OF_BIRTH[i]

IF (ld_yngst_child_birth_date < ld_child_birth_date)

THEN
(
ld_yngst_child_birth_date = ld_child_birth_date

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

ln_no_of_days = DAYS_BETWEEN(ld_current_date,ld_yngst_child_birth_date)
ln_age = ROUND(ln_no_of_days/365,2)
)
)
i= i+1
)
if (ln_child_count = 0)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_PAT_0KIDS' /* 'You can only avail this leave if you have dependent/adopted children' */
RETURN VALID,ERROR_MESSAGE
)
/*
if (1=1)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'Age of youngest child is : '|| TO_CHAR(ln_age)
RETURN VALID,ERROR_MESSAGE
)
*/
if (ln_age > 7 and ln_age < 12)
THEN
(
VALID = 'Y'
)
else
(
VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_CHILD_AGE_7TO12' /* You can only avail this leave if your youngest child is between 7 to 12 years of age.'*/
RETURN VALID,ERROR_MESSAGE
)
)

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

/*================ FORMULA SECTION END =======================*/

return VALID,ERROR_MESSAGE
/******************************************************************************
FORMULA NAME: XX_ANC_XX_VALID_Adoption
FORMULA TYPE: Global Absence Entry Validation
DESCRIPTION: This formula returns the Adoption Leave Criteria. Only workers who have adopted childrens having age less than a year are eligible.
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 11-May-2015 Initial Version
*******************************************************************************/
/*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_PERSON_ID IS 0
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_CONTACT_TYPE IS ' '
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_DATE_OF_BIRTH IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_DISPLAY_NAME is ' '
DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE is '47121231'
DEFAULT for ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0
DEFAULT for PER_PER_MARITAL_STATUS is ' '
DEFAULT for ANC_ABS_ENTRS_DURATION is 0
DEFAULT FOR IV_TOTALDURATION is 0
DEFAULT_DATA_VALUE for ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR is 0
DEFAULT for ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0

/*=========== DATABASE ITEM DEFAULTS END =====================*/

/*================ FORMULA SECTION BEGIN =======================*/
/*==============INPUTS SECTION BEGIN================*/
INPUTS ARE iv_start_date (date),
iv_end_date (date),

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

IV_TOTALDURATION

/*==============INPUTS SECTION BEGIN================*/

l_cuurent_year=to_char(Get_context(EFFECTIVE_DATE, '1999/01/01 12:00:00' (date)),'YYYY')
l_start_date=to_date(l_cuurent_year+'/01/01')
l_end_date=to_date(l_cuurent_year+'/12/31')
l_absence_type_id=GET_CONTEXT(ABSENCE_TYPE_ID,0)
l_current_duration=days_between(iv_end_date,iv_start_date)+1
l_days=0
l_days_duration =0
ln_occurence = 1
l_total_days = 0
l_new_duration = IV_TOTALDURATION
lc_marital_status = PER_PER_MARITAL_STATUS
ln_child_count = 0
ld_yngst_child_birth_date = TO_DATE('1951-01-01','yyyy-MM-dd')
ln_no_of_days = 0
ld_current_date = TO_DATE(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE)
ln_rem = MOD(IV_TOTALDURATION,0.5)
IF (ln_rem <> 0)
THEN
(
VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_DURCAL' /* 'Leave Duration should either be Half/Full Day.Please change absence start and end dates
accordingly' */
RETURN VALID,ERROR_MESSAGE
)

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

CHANGE_CONTEXTS(START_DATE=iv_start_date, END_DATE=iv_end_date)
(
i=PER_PER_CONTACT_PERSON_ID.FIRST(-1)
WHILE PER_PER_CONTACT_PERSON_ID.exists(i)
LOOP
(
if(PER_PER_CONTACT_CONTACT_TYPE[i] = 'A')
then
(
ln_child_count = ln_child_count + 1
/* Adding Condition for Checking Child Age */
ld_child_birth_date = PER_PER_CONTACT_DATE_OF_BIRTH[i]
IF (ld_yngst_child_birth_date < ld_child_birth_date)
THEN
(
ld_yngst_child_birth_date = ld_child_birth_date
ln_no_of_days = DAYS_BETWEEN(ld_current_date,ld_child_birth_date)
ln_age = ROUND(ln_no_of_days/365,2)
)
)
i= i+1
)
if (ln_child_count = 0)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_ADOPTION_0KIDS' /* 'You can only avail this leave if you have adopted children' */
RETURN VALID,ERROR_MESSAGE
)

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

if (ln_age > 1)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_CHILD_AGE_LESS1' /* You can only avail this leave if your youngest child is less than a year old.'*/
RETURN VALID,ERROR_MESSAGE
)
else(
valid='Y'
)
)

/*================ FORMULA SECTION END =======================*/

return VALID,ERROR_MESSAGE

/******************************************************************************
FORMULA NAME: XX_ANC_VALID_Duration
FORMULA TYPE: Global Absence Entry Validation
DESCRIPTION: This formula checks that the absence duration should either be half/full day.
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 12-May-2015 Initial Version
*******************************************************************************/
/*=========== DATABASE ITEM DEFAULTS END =====================*/
/*================ FORMULA SECTION BEGIN =======================*/
/*==============INPUTS SECTION BEGIN================*/
INPUTS ARE iv_start_date (date),
iv_end_date (date),

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

IV_TOTALDURATION

/*==============INPUTS SECTION BEGIN================*/

l_new_duration = IV_TOTALDURATION
ln_rem = MOD(l_new_duration,0.5)

IF (ln_rem <> 0)
THEN
(
VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_DURCAL' /* 'Leave Duration should either be Half/Full Day.E.g.: Full Day (8.45am 5.45pm / 8am 5pm etc)E.g.:
Half Day (8.45am 12.45pm or 1.45pm 5.45pm / 8am 12pm or 1pm 5pm etc)' */
RETURN VALID,ERROR_MESSAGE
)
ELSE
(
VALID = 'Y'
)
RETURN VALID,ERROR_MESSAGE

/******************************************************************************
FORMULA NAME: XX_ANC_XXPC_VALID_Duration
FORMULA TYPE: Global Absence Entry Validation
DESCRIPTION: This formula checks that the absence duration should either be half/full day for FXPC
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 12-May-2015 Initial Version
*******************************************************************************/

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

/*=========== DATABASE ITEM DEFAULTS END =====================*/

/*================ FORMULA SECTION BEGIN =======================*/
/*==============INPUTS SECTION BEGIN================*/
INPUTS ARE iv_start_date (date),
iv_end_date (date),
IV_TOTALDURATION

/*==============INPUTS SECTION BEGIN================*/

l_new_duration = IV_TOTALDURATION
ln_rem = MOD(l_new_duration,0.5)

IF (ln_rem <> 0)
THEN
(
VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_DURCAL_FXPC' /* 'Leave Duration should either be Half/Full Day*/
RETURN VALID,ERROR_MESSAGE
)
ELSE
(
VALID = 'Y'
)
RETURN VALID,ERROR_MESSAGE
/******************************************************************************
FORMULA NAME: XX_ANC_XX_VALID_Maternity
FORMULA TYPE: Global Absence Entry Validation
DESCRIPTION: This formula returns validates the Maternity Leave Criteria. Allowed only if a child is born within 1 year of leave application date
Change History:

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 14-May-2015 Initial Version
*******************************************************************************/
DEFAULT FOR IV_START_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_END_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE for ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR is 0
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_PERSON_ID IS 0
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_CONTACT_TYPE IS ' '
DEFAULT_DATA_VALUE FOR PER_PER_CONTACT_DATE_OF_BIRTH IS '4712/12/31 00:00:00' (date)
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0
DEFAULT FOR ANC_ABS_MTRNT_ACTUAL_CHILD_BIRTH_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR ANC_ABS_MTRNT_ACTUAL_RETURN_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR ANC_ABS_MTRNT_ACTUAL_DURATION IS 112
DEFAULT FOR ANC_ABS_ENTRS_DURATION IS 0
DEFAULT FOR ANC_ABS_TYP_NAME IS ' '

INPUTS ARE IV_END_DATE (date), IV_START_DATE (date)

ld_period_start_date = IV_START_DATE
ld_period_end_date = IV_END_DATE

ln_ml_type_id = GET_CONTEXT(ABSENCE_TYPE_ID,0) /*ml stands for Maternity Leave */

ln_counter = 0
ln_child_count = 0
ld_start_date = TO_DATE('1951-01-01','yyyy-MM-dd')
ld_end_date = TO_DATE('4712-12-31','yyyy-MM-dd')
ld_maternity_end_date = TO_DATE('4712-12-31','yyyy-MM-dd')
ld_current_date = TO_DATE(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE)

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

ld_child_birth_date = TO_DATE('1951-01-01','yyyy-MM-dd')
j=1
VALID = 'Y'

CHANGE_CONTEXTS(START_DATE=iv_start_date, END_DATE=iv_end_date)
(
i=PER_PER_CONTACT_PERSON_ID.FIRST(-1)
WHILE PER_PER_CONTACT_PERSON_ID.exists(i)
LOOP
(
IF (PER_PER_CONTACT_CONTACT_TYPE[i] = 'C')
THEN
(
ln_child_count = ln_child_count + 1
/* Logic to fetch Youngest Child Birth Date */
ld_child_birth_date = PER_PER_CONTACT_DATE_OF_BIRTH[i]
IF ( ld_child_birth_date < PER_PER_CONTACT_DATE_OF_BIRTH[i])
THEN
ld_child_birth_date = PER_PER_CONTACT_DATE_OF_BIRTH[i]
)
ELSE
(ln_child_count = ln_child_count)
i= i+1
)
if (ln_child_count = 0)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_ERR_MAT_0KIDS' /* 'You can only avail this leave if you have dependent children' */

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

RETURN VALID,ERROR_MESSAGE
)
)
CHANGE_CONTEXTS(START_DATE=ld_start_date, END_DATE=IV_START_DATE) /* Removed ld_maternity_end_date to ld_start_date */
(
WHILE ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR.exists(j)
LOOP
(
CHANGE_CONTEXTS (ABSENCE_ENTRY_ID = ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR[j])
(
IF (ln_ml_type_id = ANC_ABS_ENTRS_ABSENCE_TYPE_ID)
THEN
(
IF (DAYS_BETWEEN(IV_START_DATE,ld_child_birth_date) > 365 OR DAYS_BETWEEN(IV_START_DATE,ld_child_birth_date) < 0)
THEN
(VALID = 'N'
ERROR_MESSAGE = 'XX_ANC_XX_FLEX_FROM_CHILDBIRTH' /* This leave needs to be applied within 1 year of chilbirth */
RETURN VALID,ERROR_MESSAGE
)
)

)
j=j+1
)
)
RETURN VALID, ERROR_MESSAGE
/******************************************************************************
FORMULA NAME: XX_ANC_XX_PARACC_AnnualXXAssignee
FORMULA TYPE: Global Absence Partial Period Accrual Rate

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

DESCRIPTION: This formula returns the FTE pro-rated partial period accrual for employees enrolled into Annual Leave plan in Singapore
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 20-Apr-2015 Initial Version
Ashish Harbhajanka 16-Jun-2015 Changed Accrual Calculation Basis From Hire Date to Assignment Start Date
*******************************************************************************/
DEFAULT FOR PER_REL_ORIGINAL_DATE_OF_HIRE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_ASG_REL_DATE_START is '4712/12/31 00:00:00' (date)
ld_term_end_date = TO_DATE('31-12-'||TO_CHAR(PER_REL_ORIGINAL_DATE_OF_HIRE, 'rrrr'),'dd-mm-rrrr')
ld_term_end_date = TO_DATE('31-12-'||TO_CHAR(PER_ASG_REL_DATE_START, 'rrrr'),'dd-mm-rrrr') /* Added By Ashish on 16-Jun-2015 */
ln_accrual = 0
ln_months = MONTHS_BETWEEN(ld_term_end_date, PER_REL_ORIGINAL_DATE_OF_HIRE)
ln_months = MONTHS_BETWEEN(ld_term_end_date, PER_ASG_REL_DATE_START)
/* Added By Ashish on 16-Jun-2015 */
IF (ln_months < 3)
THEN
(ln_accrual = 4)
IF (ln_months >= 3 AND ln_months < 6)
THEN
(ln_accrual = 8)
IF (ln_months >= 6 AND ln_months < 9)
THEN
(ln_accrual = 12)
IF (ln_months >= 9 AND ln_months < 12)
THEN
(ln_accrual = 16)
accrual = ln_accrual
vestingUnits=1
vestingUOM='M'

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

RETURN accrual,vestingUnits,vestingUOM
/***************************************************************************
FORMULA NAME: XX_ANC_PARACC_Annual
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 plan
Change History:
Name
Date
Comments
------------------------------------------------------------------------------Ashish Harbhajanka 25-Feb-2014 Initial Version
Ashish Harbhajanka 16-Jun-2015 Changed Accrual Calculation Basis From Hire Date to Assignment Start Date
*******************************************************************************/
DEFAULT FOR IV_ACCRUAL IS 0
DEFAULT FOR PER_REL_ORIGINAL_DATE_OF_HIRE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_REL_ACTUAL_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_ASG_REL_DATE_START is '4712/12/31 00:00:00' (date)
INPUTS ARE IV_ACCRUAL,IV_CALEDARSTARTDATE (date),IV_CALEDARENDDATE(date)
ld_emp_hire_date=PER_REL_ORIGINAL_DATE_OF_HIRE
ld_emp_hire_date=PER_ASG_REL_DATE_START
ld_emp_term_date =PER_REL_ACTUAL_TERMINATION_DATE
ld_effective_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))
ld_term_start_date = TO_DATE('01-01-'||TO_CHAR(ld_effective_date, 'rrrr'),'dd-mm-rrrr')
ld_term_end_date = TO_DATE('31-12-'||TO_CHAR(ld_effective_date, 'rrrr'),'dd-mm-rrrr')
ld_term_duration = DAYS_BETWEEN (ld_term_end_date, ld_term_start_date)
/* If Employee is Active - Termination Date is Defaulted */
IF (TO_CHAR(ld_effective_date,'MM-YYYY') = TO_CHAR(ld_emp_hire_date,'MM-YYYY'))
THEN
(

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

ld_participation_start_date = ld_emp_hire_date
ld_participation_end_date = TO_DATE('31-12-'||TO_CHAR(ld_emp_hire_date,'rrrr'),'dd-mm-rrrr')
ld_participation_duration = DAYS_BETWEEN(ld_participation_end_date,ld_participation_start_date)
prorated_accrual = ROUND(IV_ACCRUAL * (ld_participation_duration / ld_term_duration),2)
valid = 'N'
floored_accrual = floor(prorated_accrual)
decimal_accrual = prorated_accrual - floored_accrual
IF (decimal_accrual = 0)
THEN
final_accrual = floored_accrual
IF (decimal_accrual > 0 and decimal_accrual <= 0.5)
THEN
final_accrual = floored_accrual + 0.5
IF (decimal_accrual > 0.5)
THEN
final_accrual = floored_accrual + 1
accrual = final_accrual
)
/* IF Employee has Termination date - Not Defaulted */
IF (TO_CHAR(ld_effective_date,'MM-YYYY') = TO_CHAR(ld_emp_term_date,'MM-YYYY'))
THEN
(
ld_participation_start_date = ld_emp_term_date
ld_participation_end_date = TO_DATE('31-12'||TO_CHAR(ld_emp_term_date,'rrrr'),'dd-mm-rrrr')
ld_participation_duration = DAYS_BETWEEN(ld_participation_end_date,ld_participation_start_date)
prorated_accrual = ROUND(IV_ACCRUAL * (ld_participation_duration / ld_term_duration),2)

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

floored_accrual = floor(prorated_accrual)
decimal_accrual = prorated_accrual - floored_accrual
IF (decimal_accrual = 0)
THEN
final_accrual = prorated_accrual
IF (decimal_accrual >0 and decimal_accrual <= 0.5)
THEN
decimal_accrual = 0
IF (decimal_accrual > 0.5)
THEN
decimal_accrual = 0.5
final_accrual = floored_accrual + decimal_accrual
accrual = (-1) * final_accrual
/*RETURN accrual*/
)
RETURN accrual

/************************************************************************************
FORMULA NAME: XX_ANC_MAT_DURCAL
FORMULA TYPE: Global Absence Type Duration
DESCRIPTION: This sample formula returns the calendar days for Maternity Leave.
Change History:
Name Date Comments
-------------------------------------------------------------------------------------------Ashish Harbhajanka 28-Apr-2015 Initial Version
************************************************************************************/
INPUTS ARE IV_START_DATE (date),
IV_END_DATE (date),
IV_START_TIME,
IV_END_TIME,

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

IV_TOTALDURATION
/*================ FORMULA SECTION BEGIN =======================*/
DEFAULT for IV_TOTALDURATION is 0
duration = DAYS_BETWEEN(IV_END_DATE,IV_START_DATE) + 1
/*================ FORMULA SECTION END =======================*/
return duration
/********************************************************************
FORMULA NAME: XX_ANC_AssigneeOTPResident_VESTING_1MONTH
FORMULA TYPE: Global Absence Vesting Period
DESCRIPTION: This sample formula removes the vesting period for Assignee/OTP/Resident
Name
Date
Comments
----------------------------------------------------------------------------------------------------------------Ashish Harbhajanka
15-Jun-2015 Initial Version
Ashish Harbhajanka
06-Jul-2015 Incorporate Changes Required for 'Global Temporary Assignment'
---------------------------------------------------------------------------------------------------------------********************************************************************/
/*==============DATABASE ITEM DEFAULTS BEGIN =====================*/
DEFAULT for PER_ASG_REL_DATE_START is '1951/01/01 00:00:00' (date)
DEFAULT for PER_ASG_REL_ORIGINAL_DATE_OF_HIRE is '1951/01/01 00:00:00' (date)
DEFAULT for PER_PERSON_ENTERPRISE_HIRE_DATE is '1951/01/01 00:00:00' (date)
DEFAULT FOR PER_ASG_ATTRIBUTE5 IS ' '
DEFAULT FOR PER_ASG_ACTION_CODE is ' '
/*==============DATABASE ITEM DEFAULTS ENDS======================*/
/*================== FORMULA SECTION BEGIN =======================*/
IF ((PER_ASG_REL_DATE_START = PER_ASG_REL_ORIGINAL_DATE_OF_HIRE) OR (PER_ASG_ACTION_CODE = 'GLB_TEMP_ASG'))
THEN
(
If (PER_ASG_ATTRIBUTE5 = 'Assignee')
OR (PER_ASG_ATTRIBUTE5 = 'OTP')
OR (PER_ASG_ATTRIBUTE5 = 'Resident')

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

then(
vestingUOM = 'M'
vestingUnits= 0
)
else(
vestingUOM = 'M'
vestingUnits=1
)
)
else
(vestingUOM = 'M'
vestingUnits=1
)
/*=================== FORMULA SECTION END =======================*/
return vestingUOM,vestingUnits
/********************************************************************
FORMULA NAME: XX_ANC_AssigneeOTPResident_VESTING_3MONTHS
FORMULA TYPE: Global Absence Vesting Period
DESCRIPTION: This sample formula removes the vesting period for Assignee/OTP/Resident. Conditionally applies vesting period of 3 month for first
assignment record.
Name
Date
Comments
----------------------------------------------------------------------------------------------------------------Ashish Harbhajanka
18-Jun-2015 Initial Version
Ashish Harbhajanka
06-Jul-2015 Incorporate Changes Required for 'Global Temporary Assignment'
---------------------------------------------------------------------------------------------------------------********************************************************************/
/*==============DATABASE ITEM DEFAULTS BEGIN =====================*/
DEFAULT for PER_ASG_REL_DATE_START is '1951/01/01 00:00:00' (date)
DEFAULT for PER_ASG_REL_ORIGINAL_DATE_OF_HIRE is '1951/01/01 00:00:00' (date)
DEFAULT for PER_PERSON_ENTERPRISE_HIRE_DATE is '1951/01/01 00:00:00' (date)
DEFAULT FOR PER_ASG_ATTRIBUTE5 IS ' '
DEFAULT FOR PER_ASG_ACTION_CODE is ' '

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

/*==============DATABASE ITEM DEFAULTS ENDS======================*/

/*================== FORMULA SECTION BEGIN =======================*/
IF ((PER_ASG_REL_DATE_START = PER_ASG_REL_ORIGINAL_DATE_OF_HIRE) OR (PER_ASG_ACTION_CODE = 'GLB_TEMP_ASG'))
THEN
(
If (PER_ASG_ATTRIBUTE5 = 'Assignee')
OR (PER_ASG_ATTRIBUTE5 = 'OTP')
OR (PER_ASG_ATTRIBUTE5 = 'Resident')
then(
vestingUOM = 'M'
vestingUnits=0
)
else(
vestingUOM = 'M'
vestingUnits=3
)
)
else
(vestingUOM = 'M'
vestingUnits=3
)
/*=================== FORMULA SECTION END =======================*/
return vestingUOM,vestingUnits
/***************************************************************************
FORMULA NAME: XX_ANC_ELIG_ASGTYPE_MARSTATUS_Married_NOAssignee_OTP_Resident
FORMULA TYPE: Participation and Rate Eligibility Formula
DESCRIPTION: This formula returns a 'Y' if the employee is married and not a Assignee, OTP, Resident
Change History:
Name
Date
Comments
--------------------------------------------------------------------------------Ashish Harbhajanka
8-May-2015 Initial Version
*******************************************************************************/

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

DEFAULT FOR PER_PER_MARITAL_STATUS_MEANING IS ' '

DEFAULT FOR PER_ASG_ATTRIBUTE5 IS ' '
ELIGIBLE = 'N'
IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = 'Assignee'))
THEN(ELIGIBLE = 'N')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = 'OTP'))
THEN(ELIGIBLE = 'N')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = 'Resident'))
THEN(ELIGIBLE = 'N')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = 'XX Assignee'))
THEN(ELIGIBLE = 'N')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = 'XX OTP'))
THEN(ELIGIBLE = 'N')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = 'XX Resident'))
THEN(ELIGIBLE = 'N')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = ' '))
THEN (ELIGIBLE = 'Y')
RETURN ELIGIBLE

/***************************************************************************
FORMULA NAME: XX_ANC_ELIG_ASGType_MARSTATUS_Married_NoXXAssignee_OTP_Resident
FORMULA TYPE: Participation and Rate Eligibility Formula
DESCRIPTION: This formula returns a 'Y' if the employee is single and not a Assignee, OTP, Resident
Change History:
Name
Date
Comments
--------------------------------------------------------------------------------Ashish Harbhajanka
3-June-2015 Initial Version
*******************************************************************************/

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

DEFAULT FOR PER_PER_MARITAL_STATUS_MEANING IS ' '

DEFAULT FOR PER_ASG_ATTRIBUTE5 IS ' '
ELIGIBLE = 'N'
IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = 'Assignee'))
THEN(ELIGIBLE = 'N')
IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = 'OTP'))
THEN(ELIGIBLE = 'N')
IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = 'Resident'))
THEN(ELIGIBLE = 'N')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = 'XX Assignee'))
THEN(ELIGIBLE = 'Y')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = 'XX OTP'))
THEN(ELIGIBLE = 'Y')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = 'XX Resident'))
THEN(ELIGIBLE = 'Y')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Married') AND (PER_ASG_ATTRIBUTE5 = ' '))
THEN (ELIGIBLE = 'Y')
RETURN ELIGIBLE

/***************************************************************************
FORMULA NAME: XX_ANC_ELIG_ASGType_NoAssignee_OTP_Resident
FORMULA TYPE: Participation and Rate Eligibility Formula
DESCRIPTION: Returns a 'Yes' if the employees are not a Assignee, OTP & Resident
Change History:
Name
Date
Comments
---------------------------------------------------------------------------------

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka
8-May-2015 Initial Version
*******************************************************************************/
DEFAULT FOR PER_ASG_ATTRIBUTE5 IS ' '
ELIGIBLE = 'Y'
IF ((PER_ASG_ATTRIBUTE5 = 'XX Resident')
OR (PER_ASG_ATTRIBUTE5 = 'XX Assignee')
OR (PER_ASG_ATTRIBUTE5 = 'XX OTP')
OR (PER_ASG_ATTRIBUTE5 = 'Assignee')
OR (PER_ASG_ATTRIBUTE5 = 'OTP')
OR (PER_ASG_ATTRIBUTE5 = 'Resident'))
THEN(ELIGIBLE = 'N')
RETURN ELIGIBLE
/***************************************************************************
FORMULA NAME: XX_ANC_ELIG_ASGType_NoFXAssignee_OTP_Resident
FORMULA TYPE: Participation and Rate Eligibility Formula
DESCRIPTION: Returns a Yes for employees who have assignment type as 'XX Assignee'
Change History:
Name
Date
Comments
--------------------------------------------------------------------------------Spencer Lin
8-May-2015 Initial Version
*******************************************************************************/
DEFAULT FOR PER_ASG_ATTRIBUTE5 IS ' '
ELIGIBLE = 'Y'
IF ((PER_ASG_ATTRIBUTE5 = 'XX Resident')
OR (PER_ASG_ATTRIBUTE5 = 'XX Assignee')
OR (PER_ASG_ATTRIBUTE5 = 'XX OTP'))
THEN(ELIGIBLE = 'N')

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

RETURN ELIGIBLE
/***************************************************************************
FORMULA NAME: XX_ANC_ELIG_OTP_ASSIGN_RESIDENT
FORMULA TYPE: Participation and Rate Eligibility Formula
DESCRIPTION: Returns a Yes for employees who have assignment type as 'Assignee', 'OTP', 'Resident'
Change History:
Name
Date
Comments
--------------------------------------------------------------------------------Ashish Harbhajanka
8-May-2015 Initial Version
*******************************************************************************/
DEFAULT FOR PER_ASG_ATTRIBUTE5 IS ' '
ELIGIBLE = 'N'
IF ((PER_ASG_ATTRIBUTE5 = 'Resident')
OR (PER_ASG_ATTRIBUTE5 = 'Assignee')
OR (PER_ASG_ATTRIBUTE5 = 'OTP')
)
THEN(ELIGIBLE = 'Y')
RETURN ELIGIBLE
/***************************************************************************
FORMULA NAME: XX_ANC_ELIG_ASGTYPE_XXAssign_Resident_OTP
FORMULA TYPE: Participation and Rate Eligibility Formula
DESCRIPTION: Returns a Yes for employees who have assignment type as 'XX Assignee'
Change History:
Name
Date
Comments
--------------------------------------------------------------------------------Ashish Harbhajanka
8-May-2015 Initial Version
*******************************************************************************/

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

DEFAULT FOR PER_ASG_ATTRIBUTE5 IS ' '

ELIGIBLE = 'N'
IF ((PER_ASG_ATTRIBUTE5 = 'Resident')
OR (PER_ASG_ATTRIBUTE5 = 'Assignee'))
THEN(ELIGIBLE = 'Y')
RETURN ELIGIBLE
/***************************************************************************
FORMULA NAME: XX_ANC_ELIG_ASGTYPE_XXOTP
FORMULA TYPE: Participation and Rate Eligibility Formula
DESCRIPTION: Returns a Yes for employees who have assignment Type as 'XX OTP'
Change History:
Name
Date
Comments
--------------------------------------------------------------------------------Ashish Harbhajanka 15-April-2015 Initial Version
*******************************************************************************/
DEFAULT FOR PER_ASG_ATTRIBUTE5 IS ' '
ELIGIBLE = 'N'
IF (PER_ASG_ATTRIBUTE5 = 'XX OTP ')
THEN(ELIGIBLE = 'Y')
RETURN ELIGIBLE
/***************************************************************************
FORMULA NAME: XX_ANC_ELIG_ASGTYPE_NOAssignee_OTP_Resident
FORMULA TYPE: Participation and Rate Eligibility Formula
DESCRIPTION: Returns a 'Yes' if the employees are not a Assignee, OTP & Resident
Change History:
Name
Date
Comments

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

--------------------------------------------------------------------------------Ashish Harbhajanka
8-May-2015 Initial Version
*******************************************************************************/
DEFAULT FOR PER_ASG_ATTRIBUTE5 IS ' '
ELIGIBLE = 'Y'
IF ((PER_ASG_ATTRIBUTE5 = 'Resident')
OR (PER_ASG_ATTRIBUTE5 = 'Assignee')
OR (PER_ASG_ATTRIBUTE5 = 'OTP')
OR (PER_ASG_ATTRIBUTE5 = 'XX Assignee')
OR (PER_ASG_ATTRIBUTE5 = 'XX OTP')
OR (PER_ASG_ATTRIBUTE5 = 'XX Resident'))
THEN(ELIGIBLE = 'N')
RETURN ELIGIBLE
/***************************************************************************
FORMULA NAME: XX_ANC_ELIG_ASGTYPE_MARSTATUS_Single_NOAssignee_OTP_Resident
FORMULA TYPE: Participation and Rate Eligibility Formula
DESCRIPTION: This formula returns a 'Y' if the employee is single and not a Assignee, OTP, Resident
Change History:
Name
Date
Comments
--------------------------------------------------------------------------------Ashish Harbhajanka
3-June-2015 Initial Version
*******************************************************************************/
DEFAULT FOR PER_PER_MARITAL_STATUS_MEANING IS ' '
DEFAULT FOR PER_ASG_ATTRIBUTE5 IS ' '
ELIGIBLE = 'N'
IF ((PER_PER_MARITAL_STATUS_MEANING = 'Single') AND (PER_ASG_ATTRIBUTE5 = 'XX Assignee'))

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

THEN(ELIGIBLE = 'N')
IF ((PER_PER_MARITAL_STATUS_MEANING = 'Single') AND (PER_ASG_ATTRIBUTE5 = 'XX OTP'))
THEN(ELIGIBLE = 'N')
IF ((PER_PER_MARITAL_STATUS_MEANING = 'Single') AND (PER_ASG_ATTRIBUTE5 = 'XX Resident'))
THEN(ELIGIBLE = 'N')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Single') AND (PER_ASG_ATTRIBUTE5 = 'Assignee'))
THEN(ELIGIBLE = 'Y')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Single') AND (PER_ASG_ATTRIBUTE5 = 'OTP'))
THEN(ELIGIBLE = 'Y')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Single') AND (PER_ASG_ATTRIBUTE5 = 'Resident'))
THEN(ELIGIBLE = 'Y')
ELSE IF ((PER_PER_MARITAL_STATUS_MEANING = 'Single') AND (PER_ASG_ATTRIBUTE5 = ' '))
THEN (ELIGIBLE = 'Y')
RETURN ELIGIBLE

/***************************************************************************
FORMULA NAME: XX_ANC_ELIG_HomeState_XXPC
FORMULA TYPE: Participation and Rate Eligibility Formula
DESCRIPTION: Returns a Yes for employees who have Address Region as either of 'New South Wales','Queensland','Western Australia','Vistoria','South
Australia'
Change History:
Name
Date
Comments
--------------------------------------------------------------------------------Ashish Harbhajanka 20-April-2015 Initial Version
*******************************************************************************/
DEFAULT FOR PER_PER_ADD_REGION2 IS ' '
lc_emp_addr_state = PER_PER_ADD_REGION2
ELIGIBLE = 'N'

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

IF (lc_emp_addr_state = 'New South Wales' OR lc_emp_addr_state = 'Queensland' OR lc_emp_addr_state = 'Western Australia' OR lc_emp_addr_state =
'Victoria' OR lc_emp_addr_state = 'South Australia')
THEN(ELIGIBLE = 'Y')
RETURN ELIGIBLE
/******************************************************************************
FORMULA NAME: XX_ANC_GET_ANNUAL_MGR_LEAVE_BALANCE
FORMULA TYPE: Global Absence Accrual
DESCRIPTION: This formula returns the leave balance for Annual Leave Plan (Manager)
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 24-Jun-2015 Initial Version
*******************************************************************************/
INPUTS ARE IV_PERSON_ID,IV_TERM_ID,IV_EFF_DT (DATE)
ln_accrual = 0
ld_current_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))
L_Leave_Balance = 0
ln_annual_mgr_accrual_plan_id = 300000001752603 /*Accrual Plan Id for Annual Leave Plan (Manager) */
L_Term_Id = IV_TERM_ID
L_Person_Id = IV_PERSON_ID
L_Eff_Dt = IV_EFF_DT
CHANGE_CONTEXTS(ACCRUAL_PLAN_ID = ln_annual_mgr_accrual_plan_id,HR_ASSIGNMENT_ID = L_Term_Id,PERSON_ID = L_Person_Id,EFFECTIVE_DATE
=L_Eff_Dt )
(
L_Leave_Balance = GET_ACCRUAL_BALANCE()

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

)
return L_Leave_Balance
/******************************************************************************
FORMULA NAME: XX_ANC_GET_ANNUAL_DTC_CONTRACT_LEAVE_BALANCE
FORMULA TYPE: Global Absence Accrual
DESCRIPTION: This formula returns the leave balance for Annual Leave Plan (DTC Contract)
Change History:
Name
Date
Comments
----------------------------------------------Ashish Harbhajanka 24-Jun-2015 Initial Version
*******************************************************************************/
INPUTS ARE IV_PERSON_ID,IV_TERM_ID,IV_EFF_DT (DATE)
ln_accrual = 0
ld_current_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))
L_Leave_Balance = 0
ln_annual_dtc_contract_accrual_plan_id = 300000001752605 /*Accrual Plan Id for Annual Leave Plan (DTC Contract) */
L_Term_Id = IV_TERM_ID
L_Person_Id = IV_PERSON_ID
L_Eff_Dt = IV_EFF_DT
CHANGE_CONTEXTS(ACCRUAL_PLAN_ID = ln_annual_dtc_contract_accrual_plan_id,HR_ASSIGNMENT_ID = L_Term_Id,PERSON_ID =
L_Person_Id,EFFECTIVE_DATE =L_Eff_Dt )
(
L_Leave_Balance = GET_ACCRUAL_BALANCE()
)
return L_Leave_Balance

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

/*******************************************************************
FORMULA NAME : XX_COMP_HIERARCHY_XXAP_Compensation_Plan_2015
FORMULA TYPE : Compensation Hierarchy Determination
DESCRIPTION : Hierarchy determination fast formula which is based on assignment_id
Created By Ashish Harbhajanka

/*=========== INPUT VALUES DEFAULTS BEGIN =====================*/

INPUTS ARE CMP_IV_ASSIGNMENT_ID (number), CMP_IV_PLAN_ID (number), CMP_IV_PERIOD_ID (number)
DEFAULT FOR CMP_IV_ASSIGNMENT_ID IS 0
DEFAULT FOR XX_COMPENSATION_PLAN_DETAILS_BONUS_POOL_MGR_EMP_NO_ASG_ENTRY_VALUE IS '0'
DEFAULT FOR XX_COMPENSATION_PLAN_DETAILS_BONUS_POOL_MGR_ASG_ENTRY_VALUE IS 'X'
DEFAULT FOR XX_COMPENSATION_PLAN_DETAILS_PLAN_NAME_ASG_ENTRY_VALUE IS 'PLAN_NAME_DEFAULTED'
DEFAULT FOR PER_PER_FULL_NAME IS ' '
DEFAULT FOR PER_ASG_PERSON_NUMBER IS '0'
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_ASG_EFFECTIVE_END_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_ASG_EFFECTIVE_START_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_ASSIGNMENT_ID IS 0
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_ENTERPRISE_ID IS 0
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_LINE_MANAGER_ASG_ID IS 0
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_LINE_MANAGER_ID IS 0
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_PERSON_ID IS 0
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_ASSIGNMENT_ID IS 0
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_DISPLAY_NAME IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_FIRST_NAME IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_FULL_NAME IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_HONORS IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_KNOWN_AS IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_LAST_NAME IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_LIST_NAME IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_MIDDLE_NAME IS ' '

Ashish Harbhajanka

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_MILITARY_RANK IS ' '

DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_NAME_EFFE_END_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_NAME_EFFE_START_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_NAME_SUFFIX IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_NAME_TYPE IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_ORDER_NAME IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_PERSON_ID IS 0
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_PREV_LAST_NAME IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_PRE_NAME_ADJUNCT IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_TITLE IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_TYPE IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_TYPE_CODE IS ' '
DEFAULT_DATA_VALUE FOR PER_EXT_ASG_CONT_RESP_TYPE_MEANING IS ' '
ld_effective_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00' (date))
ln_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)
ln_person_id = GET_CONTEXT(PERSON_ID,0)
L_PERSON_ID = '0'
L_ASSIGNMENT_ID = '0'
/*
L_PLAN_POOL_MGR = XX_COMPENSATION_PLAN_DETAILS_BONUS_POOL_MGR_EMP_NO_ASG_ENTRY_VALUE
L_PLAN_POOL_MGR_NAME = XX_COMPENSATION_PLAN_DETAILS_BONUS_POOL_MGR_ASG_ENTRY_VALUE
L_PERSON_PLAN = XX_COMPENSATION_PLAN_DETAILS_PLAN_NAME_ASG_ENTRY_VALUE
L_PLAN_NAME = GET_TABLE_VALUE('XX_COMP_PLAN_CONTROL_DATA','PLAN_NAME',TO_CHAR(CMP_IV_PLAN_ID),'NO_PLAN_NAME_FOUND')
L_PLAN_CODE = GET_TABLE_VALUE('XX_COMP_PLAN_CONTROL_DATA','PLAN_CODE',TO_CHAR(CMP_IV_PLAN_ID),'NO_PLAN_CODE_FOUND')
l_log = ESS_LOG_WRITE('=== plan name and code from UDT using plan ID -> ' || L_PLAN_NAME || ' (' || L_PLAN_CODE || ')')
*/
l_log = ess_log_write('Start Processing for Person ID : ' || TO_CHAR(ln_person_id))
l_log = ess_log_write('Start Processing for Assignment ID : ' || TO_CHAR(ln_assignment_id))
l_log = ess_log_write('Start Processing for Person Number : ' || PER_ASG_PERSON_NUMBER)

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

l_log = ess_log_write('Start Processing for Person Name : ' || PER_PER_FULL_NAME)

CHANGE_CONTEXTS(HR_ASSIGNMENT_ID = ln_assignment_id ,EFFECTIVE_DATE = ld_effective_date)
(
i=PER_EXT_ASG_CONT_RESP_ASSIGNMENT_ID.FIRST(-1)
WHILE PER_EXT_ASG_CONT_RESP_ASSIGNMENT_ID.exists(i)
LOOP
(
IF (PER_EXT_ASG_CONT_RESP_TYPE[i] = 'HR_REP')
THEN
(
l_log = ESS_LOG_WRITE('=== start custom logging for employee : ' || PER_PER_FULL_NAME || ' (' ||
PER_ASG_PERSON_NUMBER|| ')')
l_log = ESS_LOG_WRITE (PER_EXT_ASG_CONT_RESP_DISPLAY_NAME[i] || ' is the HR Representative of Person Number : ' ||
PER_ASG_PERSON_NUMBER )
l_log = ESS_LOG_WRITE('Person ID of HR Representative is ' || to_char(PER_EXT_ASG_CONT_RESP_PERSON_ID[i]) )
l_log = ESS_LOG_WRITE('Assignment ID of HR Representative is ' || to_char(PER_EXT_ASG_CONT_RESP_ASSIGNMENT_ID[i]) )
L_APPROVER_PERSON_ID = PER_EXT_ASG_CONT_RESP_PERSON_ID[i]
L_APPROVER_ASG_ID = PER_EXT_ASG_CONT_ASSIGNMENT_ID[i]
l_log = ESS_LOG_WRITE('=== L_APPROVER_PERSON_ID ' || TO_CHAR(L_APPROVER_PERSON_ID ))
l_log = ESS_LOG_WRITE('=== L_APPROVER_ASG_ID ' || TO_CHAR(L_APPROVER_ASG_ID ))
IF L_APPROVER_PERSON_ID != 0 THEN
(
l_log = ESS_LOG_WRITE(' ')
L_PERSON_ID = TO_CHAR(L_APPROVER_PERSON_ID)
L_ASSIGNMENT_ID = TO_CHAR(L_APPROVER_ASG_ID)
RETURN L_PERSON_ID,L_ASSIGNMENT_ID
)

Fusion Absence Management (Release 9.2)

Sample Fast Formulas

Ashish Harbhajanka

ELSE
(
l_log = ESS_LOG_WRITE('=== no approver found, returning ')
l_log = ESS_LOG_WRITE(' ')
RETURN
)
)
l_log = ESS_LOG_WRITE('=== end custom logging for employee : ' || PER_PER_FULL_NAME || ' (' || PER_ASG_PERSON_NUMBER|| ')')
l_log = ess_log_write('Start Processing for Person ID : ' || TO_CHAR(ln_person_id))
l_log = ess_log_write('Start Processing for Assignment ID : ' || TO_CHAR(ln_assignment_id))
l_log = ess_log_write('Start Processing for Person Number : ' || PER_ASG_PERSON_NUMBER)
l_log = ess_log_write('Start Processing for Person Name : ' || PER_PER_FULL_NAME)
i=1+1
)
)
/* end of Formula */