Exclude Second & Fourth Saturday , All Sunday & Holiday Count from the Duration of Absence

 

If Customer wants to exclude the  Second & Fourth Saturday , All Sunday & Holiday Count from the Duration of Absence while applying the Absence request, then follow the below steps to achieve it Oracle HRMS R12.2.8

Scenario: While applying Causal Leave , System should not consider the In between 2nd & 4th Saturday , Sunday &  Paid Holidays from the Duration count. But for as Other Absence Types, it should include the in between days.

 

Step 1: Create the Function.

 Compile the Below function in the Sql Developer.

create or replace FUNCTION XX_GET_HOLI_WEEK_COUNT(
    p_start_date DATE,
    p_end_date   DATE )
  RETURN NUMBER
IS
  T_DAYS NUMBER (15) := NULL;
BEGIN
  T_DAYS := 0;
  SELECT XX.week_count + XX."Holi_Count" + XX.Saturday
  INTO T_DAYS
  FROM
    (SELECT week_count,
            (
            SELECT COUNT(holi.name)
            FROM hxt_holiday_days holi
            WHERE holiday_date BETWEEN ( p_start_date ) AND ( p_end_date )
            ) AS "Holi_Count",
            (
            SELECT count(*)
            FROM
              (WITH ALL_SATURDAYS AS
              (SELECT TO_CHAR(to_date('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR'),'WW')                        * (level)                                                                                             AS WEEK_NO,
                NEXT_DAY(TO_DATE('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR')                                   + (TO_CHAR(TO_DATE('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR'),'WW' ) * (level-1) * 7),'SAT')                        AS SATURDAY_DATE,
                row_number() OVER (PARTITION BY TO_CHAR(NEXT_DAY(TO_DATE('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR') + (TO_CHAR(TO_DATE('1-JAN-'|| to_char(p_start_date, 'RRRR'), 'DD-MON-RRRR'),'WW' ) * (level-1) * 7),'SAT'),'MON') ORDER BY level) AS Pos
              FROM DUAL
                CONNECT BY level<= 52
              ORDER BY 1
              )
            SELECT SATURDAY_DATE ,POS FROM ALL_SATURDAYS WHERE POS IN (2,4) ORDER BY 1,2
              )
            WHERE SATURDAY_DATE BETWEEN p_start_date AND p_end_date
            ) AS Saturday
    FROM
      ( WITH dates AS
      (SELECT TO_DATE(p_start_date) + level - 1 datum
      FROM dual
        CONNECT BY level <= TO_DATE(p_end_date) - TO_DATE(p_start_date) + 1
      )
    SELECT COUNT(datum) AS week_count
    FROM dates
    WHERE TO_CHAR(datum,'dy','nls_date_language = english') = 'sun'
      )
    )XX;
  RETURN T_DAYS;
EXCEPTION
WHEN OTHERS THEN
  raise_application_error(-20001,'An error was encountered - ' || SQLCODE || ' -ERROR- ' || sqlerrm);
END;

 

Step 2: Create the Formula Function

Navigation : India HRMS Manager  --> Other Definitions --> Formula Function

Create the Formula Function as per the Screen shot mentioned below,

 

Step 3: Add the Validation in BG Absence Duration Fast Formula

Navigation : India HRMS Manager  --> Total Compensation  --> Write Formula.

Query the BG_ABSENCE_DURATION Formula

 Add the Below conditions in the Fast formula,

If absence_attendance_type_id = 61 then
         (
           L_DAYS = XX_GET_HOLI_WEEK_COUNT (date_start,date_end)
           /*Duration = TO_CHAR(TO_NUMBER(Duration) - L_DAYS)*/
           l_duration = (DAYS_BETWEEN(date_end,date_start) + 1) - L_DAYS
          )
        ELSE
        l_duration = (DAYS_BETWEEN(date_end,date_start) + 1)
       
        duration = to_char( l_duration - l_half_day_leave)
  )

Note : Here 61 is the Absence type ID for which the Exclude logics to be enabled.