Hi expert,
I have searched a fast formula that could exclude statutory holiday when using calendar day as the unit of measure, and I have updated the holidays in the formula,
Besides, I have create absence type with UOM calendar day and mapped the conversion formula as below, and an absence plan with UOM calendar day,
When I try to apply absence with the above setup, the duration remain 0 with start and end date selected,
would you please advise if there are anythings wrong in the fast formula?
Thank you very much!
/************************************************************************************
FORMULA NAME: FX_ANC_DURATION_EXCLUDESTATUTORYHOLIDAYS
FORMULA TYPE: Global Absence Type Duration
DESCRIPTION: This formula returns the duration based on date falls on Statutory Holidays.
Change History:
Name Date Version Comments
-------------------------------------------------------------------------------
Oracle 22-Apr-2020 DRAFT 1A Initial Version
--------------------------------------------------------------------------------------------
************************************************************************************/
/*================ FORMULA SECTION BEGIN =======================*/
Default for IV_START_DATE is '0001/01/01 00:00:00' (DATE)
Default for IV_END_DATE is '0001/01/01 00:00:00' (DATE)
Default for demo_date is '2015/03/21 00:00:00' (DATE)
Default for date_start is '1951/01/01 00:00:00' (DATE)
DEFAULT FOR holidays is EMPTY_DATE_NUMBER
Default for l_holiday_days is 0
Default for num1 is 0
Default for num2 is 0
INPUTS are IV_START_DATE (date), IV_END_DATE (date)
holidays[1]='2020/01/01 08:00:00' (DATE)
holidays[2]='2020/01/25 08:00:00' (DATE)
holidays[3]='2020/01/27 08:00:00' (DATE)
holidays[4]='2020/01/28 08:00:00' (DATE)
holidays[5]='2020/04/04 08:00:00' (DATE)
holidays[6]='2020/05/01 08:00:00' (DATE)
holidays[7]='2020/06/25 08:00:00' (DATE)
holidays[8]='2020/07/01 08:00:00'(DATE)
holidays[9]='2020/10/01 08:00:00' (DATE)
holidays[10]='2020/10/02 08:00:00' (DATE)
holidays[11]='2020/10/25 08:00:00' (DATE)
holidays[12]='2020/12/25 08:00:00'(DATE)
duration = days_between(IV_END_DATE,IV_START_DATE)+ 1
duration = round(duration)
I= 0
J=1
l_holiday_days = 0
while (I < duration) LOOP
(
J=1
while (J<=HOLIDAYS.COUNT) LOOP
(
if ( to_char(HOLIDAYS[J],'dd-MM-yyyy')=to_char(add_days(IV_START_DATE,I),'dd-MM-yyyy')) then
(
l_holiday_days=l_holiday_days+1
)
J = J + 1
)
I= I + 1
)
duration = duration - l_holiday_days