Exclude The Holidays From Absense Calculations

Step 1: Create HR_LOOKUPS.

Step 2: Create Function

create or replace

function VION_GET_BANK_HOLIDAYS(P_START_DATE IN DATE,

P_END_DATE IN DATE) Return number is

l_Ret_Val number;

Begin

Begin

Select Count(*) into l_Ret_Val

from hr_lookups where

lookup_type = 'VION_BANK_HOLIDAYS'

and to_Date(lookup_code,'YYYYMMDD') between P_START_DATE

AND P_END_DATE;

Exception

When OThers Then

L_Ret_Val := '0';

End;

Return L_Ret_Val;

End;

Step 3: Create Formula Function.

Step 4: Create Fast Formula BG_ABSENCE_DURATION

and add

/* absence in days */

  ELSE

  (

     duration = to_char(days_between(date_end,date_start)+1) /*Added By Mandeep*/

V_Bank_Holidays = VION_GET_BANK_HOLIDAYS(date_start,date_end)

duration = to_char(to_num(duration) - V_Bank_Holidays)

/* duration = to_char(DAYS_BETWEEN(date_end,date_start) + 1) */

/*Commented By Mandeep*/

  )