Oracle Cloud HCM - Sample Absence Validation Fast Formula

Today we are going to see Sample Absence Validation Fast Formula

/* FORMULA TYPE: Global Absence Entry Validation */

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_TOTALDURATION IS 0
DEFAULT FOR ANC_ABS_TYP_NAME IS ' '

INPUTS ARE IV_END_DATE (date), IV_START_DATE (date), IV_TOTALDURATION

l_eff_dt = get_context(EFFECTIVE_DATE,'0001/01/01 00:00:00'(date))
l_system_year = to_number(to_char(l_eff_dt,'yyyy'))
l_absence_start_year = to_number(to_char(IV_START_DATE,'yyyy'))
l_absence_end_year = to_number(to_char(IV_END_DATE,'yyyy'))

/* START - Next Calendar Year Leaves needs to be applied separately */
if (ANC_ABS_TYP_NAME = 'Casual Leave') then
(
if (l_absence_start_year = l_system_year and l_absence_end_year = l_system_year)
then (valid = 'Y')
else
(
valid = 'N'
error_message = 'Next Calendar Year Leaves needs to be applied separately'
return valid,error_message
)
)
/* END - Next Calendar Year Leaves needs to be applied separately */

/* START - Leave Duration can be either Half Day or multiples of full days */

if (ANC_ABS_TYP_NAME = 'Casual Leave') then
(
 if (MOD(IV_TOTALDURATION,0.5) <> 0) then
  (
   valid = 'N'
   error_message = 'Leave Duration can be either Half Day or multiples of full days'
   return valid,error_message
  )
)
/* END - Leave Duration can be either Half Day or multiples of full days */

/* More than 1 day of Casual Leave cannot be applied on Continuous Days */

 if (ANC_ABS_TYP_NAME = 'Casual Leave') then
 (
  if (IV_TOTALDURATION > 1) then
  (
     valid = 'N'
     error_message = 'XX_ERROR_MSG'
     return valid,error_message 
   )
  if (IV_TOTALDURATION <= 1) then
  (
/* Employee applying for 1 day leave, check whether employee applied Casual Leave for previous and post 1 day */
   l_work_days = 0
   l_holiday_cnt = 0
   i = -1
/* Loop until previous 1 Work Day is found. Saturday, Sunday and Holidays are not counted as Work Day */
   while (l_work_days < 1)
   loop
   (
   l_prev_st_dt = to_date(to_char(add_days(IV_START_DATE,i),'yyyy-mm-dd'),'yyyy-mm-dd')
   l_prev_ed_dt = to_date(to_char(IV_START_DATE,'yyyy-mm-dd'),'yyyy-mm-dd')

/* Fetch Holiday details from UDT table. */
   l_holiday_cnt = instr(GET_TABLE_VALUE('XX_HOLIDAY_CALENDAR_UDT','HolidayName',to_char(l_post_ed_dt1),'NONE'),'PUB',1,1)

/* if you use calendar events, then use below function to fetch holidays. Below function will return 0 for Weekends and Holidays. 1 for Work days */
/*l_holiday_cnt = GET_PAY_AVAILABILITY('ASSIGN',l_st_dt,l_ed_dt,'Y','Y','Y','Y','D')*/   
 
   if ((l_holiday_cnt = 1) or (to_number(to_char(l_prev_st_dt,'Day')) = 6) or (to_number(to_char(l_prev_st_dt,'Day')) = 7)) then
   (
   l_work_days = l_work_days
   )
   else
   (
   l_work_days = l_work_days + 1
   )

   i = i - 1  
   )   
    
   l_work_days = 0
   l_holiday_cnt = 0
   i = 1
/* Loop until post 1 Work Day is found. Saturday, Sunday and Holidays are not counted as Work Day */
   while (l_work_days < 1)
   loop
   (
   l_post_st_dt = to_date(to_char(add_days(IV_END_DATE,1),'yyyy-mm-dd'),'yyyy-mm-dd')
   l_post_ed_dt1 = to_date(to_char(add_days(IV_END_DATE,i),'yyyy-mm-dd'),'yyyy-mm-dd')
   l_post_ed_dt = to_date(to_char(add_days(IV_END_DATE,i + 1),'yyyy-mm-dd'),'yyyy-mm-dd')
  
   l_holiday_cnt = instr(GET_TABLE_VALUE('XX_HOLIDAY_CALENDAR_UDT','HolidayName',to_char(l_post_ed_dt1),'NONE'),'PUB',1,1)
  
   if ((l_holiday_cnt = 1) or (to_number(to_char(l_post_ed_dt1,'Day')) = 6) or (to_number(to_char(l_post_ed_dt1,'Day')) = 7)) then
   (
   l_work_days = l_work_days
   )
   else
   (
   l_work_days = l_work_days + 1
   )

   i = i + 1  
   )   

   l_person_id = GET_CONTEXT(PERSON_ID,0)
   CHANGE_CONTEXTS(PERSON_ID = l_person_id)
   (
/* Below function returns the number of Casual leaves between l_prev_st_dt and l_prev_ed_dt)  */
     l_prev_casual_leave = GET_ABSENCE_DAYS_PER_TYPE('Casual Leave',l_prev_st_dt,l_prev_ed_dt)
/* Below function returns the number of Casual leaves between l_post_st_dt and l_post_ed_dt)  */
     l_post_casual_leave = GET_ABSENCE_DAYS_PER_TYPE('Casual Leave',l_post_st_dt,l_post_ed_dt)
   )
   if (l_prev_casual_leave >= 1 or l_post_casual_leave >= 1) then
   (
     valid = 'N'
     error_message = 'XX_ERROR_MSG'
     return valid,error_message
   )
  )
 )
return valid