Sample Fast Formula - Global Absence Entry Validation

 /**********************************************************************
FORMULA NAME: XYZ_Client_Absence_Entry_Validation_FF
FORMULA TYPE: Global Absence Entry Validation 
DESCRIPTION : Validate Adoption Leave, Miscellaneous Maternity Leave, Maternity Leave, Sabbatical Leave, Optional Holiday, Leave Without Pay
**********************************************************************
Change History:
Name Date Version Comments
-------------------------------------------------------------------------------
Aditya 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 FOR IV_ABSENCE_REASON IS ' '
DEFAULT FOR IV_TOTALDURATION  IS 0
DEFAULT FOR PER_ASG_MANAGER_USER_PERSON_ID  IS -1
DEFAULT_DATA_VALUE for ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR is 0
DEFAULT_DATA_VALUE for PER_EXT_PER_ROLE_NAME is 'XYZ'
DEFAULT FOR PER_PERSON_EIT_EFF_USER_IN_Client_SABBATICAL_SABBATICAL_TAKEN is 'N'
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_STATUS_CD IS ' '
DEFAULT FOR ANC_ABS_TYP_NAME IS ' '
DEFAULT FOR ANC_ABS_ENTRS_END_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_REL_ATTRIBUTE_DATE4 IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_PERSON_ENTERPRISE_HIRE_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_PER_LATEST_REHIRE_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_PER_WORK_RELATIONSHIP_MAX_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE is '47121231'
DEFAULT FOR STRT_DT IS '4712/12/31 00:00:00' (date)
DEFAULT for LC_ABS_CHECK IS '0'
DEFAULT FOR l_annual_leave_balance is 0
DEFAULT FOR l_annual_leave_carryover_balance is 0
INPUTS ARE IV_END_DATE (date), IV_START_DATE (date), IV_ABSENCE_REASON, IV_TOTALDURATION
lc_absence_reason1 = 'Adopting Child with age less than 3 months'
lc_absence_reason2 = 'Adopting Child with age more than 3 months'
lc_absence_reason3 = 'Miscarriage Leave'
lc_absence_reason4 = 'Tubectomy Operation'
lc_absence_reason5 = 'Leave for illness arising out of maternity complications'
lc_absence_reason6 = 'Medical termination of pregnancy'
lc_absence_reason7 = 'Premature birth of child'
VALID           = 'Y'
lc_absence_type = ANC_ABS_TYP_NAME
lc_absence_reason = IV_ABSENCE_REASON
ln_absence_duration = IV_TOTALDURATION
lc_err = 'Error-'
LD_START_DATE = to_char(IV_START_DATE,'dd-mm-rrrr')

if lc_absence_type = 'Adoption Leave' then
(
if lc_absence_reason = lc_absence_reason1 THEN
(
if ln_absence_duration <> 84 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration has to be 84 Calendar Days'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
if lc_absence_reason = lc_absence_reason2 THEN
(
if ln_absence_duration <> 28 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration has to be 28 Calendar Days'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
)
else if lc_absence_type = 'Miscellaneous Maternity Leave' then
(
if lc_absence_reason = lc_absence_reason3 THEN
(
if ln_absence_duration <> 42 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration has to be 42 Calendar Days '
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
if lc_absence_reason = lc_absence_reason4 THEN
(
if ln_absence_duration <> 14 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration has to be 14 Calendar Days'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
if lc_absence_reason = lc_absence_reason5 or lc_absence_reason = lc_absence_reason6 or lc_absence_reason = lc_absence_reason7 THEN
(
if ln_absence_duration <> 28 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration has to be 28 Calendar Days'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
)
else if lc_absence_type = 'Maternity Leave' then
(
if PER_REL_ATTRIBUTE_DATE4 was defaulted THEN
(
/*STRT_DT = PER_PERSON_ENTERPRISE_HIRE_DATE*/
/*STRT_DT = PER_PER_LATEST_REHIRE_DATE*/
/*STRT_DT = PER_PER_WORK_RELATIONSHIP_MAX_DATE*/
STRT_DT = PER_PER_LATEST_REHIRE_DATE
)
ELSE
(
STRT_DT = PER_REL_ATTRIBUTE_DATE4
)
ln_days = DAYS_BETWEEN(IV_START_DATE,STRT_DT)
if ln_days < 80 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Employment is less than 80 calendar days, so cannot avail Maternity Leave'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
l_person_id = TO_CHAR(GET_CONTEXT(PERSON_ID,0))
L_SESSION_USER_PERSONID = GET_VALUE_SET('XYZ_SESSION_USER_PERSONID_VS')
LN_CHILD_COUNT = to_number(GET_VALUE_SET('XYZ_IN_CHILD_COUNT_VS','|=EMP_PERSON_ID='''||l_person_id||''''))
/*if ln_absence_duration <> 182 and L_SESSION_USER_PERSONID = l_person_id and LN_CHILD_COUNT <=1 and L_SESSION_USER_PERSONID<>to_char(PER_ASG_MANAGER_USER_PERSON_ID) then*/
if ln_absence_duration <> 182 and (L_SESSION_USER_PERSONID = l_person_id or L_SESSION_USER_PERSONID = to_char(PER_ASG_MANAGER_USER_PERSON_ID)) and LN_CHILD_COUNT <=1 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration should be equal to 26 weeks or 182 calendar days. Based on the start date provided, end date should be '|| to_char(add_days(IV_START_DATE,181),'dd-mon-rrrr')
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
else if ln_absence_duration <> 84 and (L_SESSION_USER_PERSONID = l_person_id or L_SESSION_USER_PERSONID = to_char(PER_ASG_MANAGER_USER_PERSON_ID)) and LN_CHILD_COUNT >= 2 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence Duration should be equal to 12 weeks or 84 calendar days. Based on the start date provided, end date should be '|| to_char(add_days(IV_START_DATE,83),'dd-mon-rrrr')
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
lc_hr_assignment_id = TO_CHAR(GET_CONTEXT(HR_ASSIGNMENT_ID,0))
lc_ABSENCE_ENTRY_ID = TO_CHAR(GET_CONTEXT(ABSENCE_ENTRY_ID,-1))
LN_PREV_ABS_GAP = to_number(GET_VALUE_SET('XYZ_IN_MONTHS_BETWEEN_ABS_VS','|=ASG_ID='''||lc_hr_assignment_id||'''|ABS_NAME='''||lc_absence_type||'''|ABS_START_DATE='''||LD_START_DATE||'''|ABS_ENTRY_ID='''||lc_ABSENCE_ENTRY_ID||''''))
if LN_PREV_ABS_GAP < 7 THEN (
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
/*TOKEN_VALUE[1]= 'Absence cannot be applied as previous absence ended less than 7 months back'||LD_START_DATE||lc_absence_type||'-'||lc_hr_assignment_id||'-'||lc_ABSENCE_ENTRY_ID||'-'||to_char(LN_PREV_ABS_GAP)*/
TOKEN_VALUE[1]= 'Absence cannot be applied as previous absence ended less than 7 months back'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
else if lc_absence_type = 'Sabbatical Leave' then
(
if PER_PERSON_EIT_EFF_USER_IN_Client_SABBATICAL_SABBATICAL_TAKEN = 'Y' then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Sabbatical Leave has already been availed'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
l_curr_date = TO_DATE(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE)
if days_between(IV_START_DATE,l_curr_date) < 30 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Sabbatical Leave can be availed with atleast 30 days notice'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
if PER_REL_ATTRIBUTE_DATE4 was defaulted THEN
(
/*STRT_DT = PER_PERSON_ENTERPRISE_HIRE_DATE*/
/*STRT_DT = PER_PER_LATEST_REHIRE_DATE*/
/*STRT_DT = PER_PER_WORK_RELATIONSHIP_MAX_DATE*/
STRT_DT = PER_PER_LATEST_REHIRE_DATE
)
ELSE
(
STRT_DT = PER_REL_ATTRIBUTE_DATE4
)
if months_between(IV_START_DATE,STRT_DT) < 36 then
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Employee has not spent minimum tenure in the organization'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
else if lc_absence_type = 'Optional Holiday' then
(
LC_ABS_CHECK = GET_VALUE_SET('XYZ_IN_CAL_EVENT_VALUE_SET','|=ABS_START_DATE='''||LD_START_DATE||'''')
if LC_ABS_CHECK = '0' THEN
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'No Optional Holiday available on the selected date'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE  
)
)
else if lc_absence_type = 'Leave Without Pay' then
(
ln_hr_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)
ld_effective_date= to_date(to_char(IV_START_DATE,'RRRR')||'/12/31','rrrr/mm/dd')
ln_leg_group_id = GET_CONTEXT(LEGISLATIVE_DATA_GROUP_ID,0)
LN_PERSON_ID = GET_CONTEXT(PERSON_ID,-1)
CHANGE_CONTEXTS(HR_ASSIGNMENT_ID = ln_hr_assignment_id,PERSON_ID = LN_PERSON_ID,EFFECTIVE_DATE = ld_effective_date,LEGISLATIVE_DATA_GROUP_ID = ln_leg_group_id)
(
lc_plan_name = 'Annual Leave'
l_annual_leave_balance = GET_PLAN_BALANCE(lc_plan_name)
lc_plan_name = 'Annual Leave - Carry Over'
l_annual_leave_carryover_balance  = GET_PLAN_BALANCE(lc_plan_name)
)
if l_annual_leave_balance + l_annual_leave_carryover_balance > 0 THEN
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence cannot be applied as Annual Leave is not exhausted'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE  
)
)
else if lc_absence_type = 'Annual Leave' then
(
ln_hr_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)
ld_effective_date= to_date(to_char(IV_START_DATE,'RRRR')||'/12/31','rrrr/mm/dd')
ln_leg_group_id = GET_CONTEXT(LEGISLATIVE_DATA_GROUP_ID,0)
LN_PERSON_ID = GET_CONTEXT(PERSON_ID,-1)
CHANGE_CONTEXTS(HR_ASSIGNMENT_ID = ln_hr_assignment_id,PERSON_ID = LN_PERSON_ID,EFFECTIVE_DATE = ld_effective_date,LEGISLATIVE_DATA_GROUP_ID = ln_leg_group_id)
(
lc_plan_name = 'Annual Leave'
l_annual_leave_balance = GET_PLAN_BALANCE(lc_plan_name)
lc_plan_name = 'Annual Leave - Carry Over'
l_annual_leave_carryover_balance  = GET_PLAN_BALANCE(lc_plan_name)
)
if l_annual_leave_balance + l_annual_leave_carryover_balance < IV_TOTALDURATION THEN
(
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
TOKEN_VALUE[1]= 'Absence duration is more than the Annual Leave Balance available'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE  
)
)
else if lc_absence_type = 'Paternity Leave' then
(
lc_hr_assignment_id = TO_CHAR(GET_CONTEXT(HR_ASSIGNMENT_ID,0))
lc_ABSENCE_ENTRY_ID = TO_CHAR(GET_CONTEXT(ABSENCE_ENTRY_ID,-1))
LN_PREV_ABS_GAP = to_number(GET_VALUE_SET('XYZ_IN_MONTHS_BETWEEN_ABS_VS','|=ASG_ID='''||lc_hr_assignment_id||'''|ABS_NAME='''||lc_absence_type||'''|ABS_START_DATE='''||LD_START_DATE||'''|ABS_ENTRY_ID='''||lc_ABSENCE_ENTRY_ID||''''))
if LN_PREV_ABS_GAP < 7 THEN (
ERROR_MESSAGE ='XYZ_CUSTOM_ABS_VALIDATION'
TOKEN_NAME[1] = 'MESSAGE'
/*TOKEN_VALUE[1]= 'Absence cannot be applied as previous absence ended less than 7 months back'||LD_START_DATE||lc_absence_type||'-'||lc_hr_assignment_id||'-'||lc_ABSENCE_ENTRY_ID||'-'||to_char(LN_PREV_ABS_GAP)*/
TOKEN_VALUE[1]= 'Absence cannot be applied as previous absence ended less than 7 months back'
ERROR_CODE = 'E' 
VALID = 'N'
return VALID,ERROR_MESSAGE,ERROR_CODE,TOKEN_NAME,TOKEN_VALUE 
)
)
return VALID