Forum Stats

  • 3,837,534 Users
  • 2,262,268 Discussions
  • 7,900,315 Comments

Discussions

Absence Duration - Exclude Statutory holiday using UOM calendar day

Cecilia-Oracle
Cecilia-Oracle Member Posts: 24 Employee

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