CREATE OR REPLACE FUNCTION add_working_days
         (inDays INTEGER
         ,inStartingDate DATE DEFAULT SYSDATE)
         RETURN DATE IS
   vWorkingDaysCount INTEGER;
   vStartingDate     DATE;
   vReturnDate       DATE;
BEGIN
   -- Initialize variables
   vWorkingDaysCount := 0;
   vStartingDate     := NVL (inStartingDate, SYSDATE); -- In case someone passes a NULL
   vReturnDate       := vStartingDate;

   IF NVL (inDays, 0) > 0 THEN
      FOR i IN 1 .. inDays LOOP
         IF RTRIM (TO_CHAR (vStartingDate + i, 'DAY')) IN ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY') THEN
            vReturnDate := vStartingDate + i;
         END IF;
      END LOOP;
   ELSIF NVL (inDays, 0) < 0 THEN
      FOR i IN 1 .. ABS (inDays) LOOP
         IF RTRIM (TO_CHAR (vStartingDate - i, 'DAY')) IN ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY') THEN
            vReturnDate := vStartingDate - i;
         END IF;
      END LOOP;
   END IF;

   RETURN vReturnDate;
END add_working_days;
/

create or replace
PACKAGE X_Utility AS
    FUNCTION isFestivo(data DATE) RETURN BOOLEAN;

    FUNCTION isLavorativo(data DATE) RETURN BOOLEAN;

    FUNCTION sommaGiorni(data DATE, num NUMBER, flag VARCHAR2) RETURN DATE; 

    FUNCTION sottraiGiorni(data DATE, num NUMBER, flag VARCHAR2) RETURN DATE;

END X_Utility;

create or replace
PACKAGE BODY X_Utility AS

    FUNCTION sommaGiorni(data DATE, num NUMBER, flag VARCHAR2) RETURN DATE IS
        u    NUMBER;
        RESULT DATE;
    BEGIN
    
        RESULT := data;

        IF (num = 0) THEN
            RETURN RESULT;
        END IF;
        
        IF (flag = 'S') THEN
            SELECT (data+num) INTO RESULT FROM DUAL;
            RETURN RESULT;
        ELSE
            u := 0;
            WHILE (u < num)
            LOOP
                SELECT (RESULT+1) INTO RESULT FROM DUAL;
                IF (isLavorativo(RESULT)) THEN
                    u := u +1;
                END IF;
            END LOOP;
            RETURN RESULT;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('problemi nella sommaGiorni: ' || SQLERRM);
    END sommaGiorni;

    FUNCTION sottraiGiorni(data DATE, num NUMBER, flag VARCHAR2) RETURN DATE IS
        u    NUMBER;
        RESULT DATE;
    BEGIN
    
        RESULT := data;

        IF (num = 0) THEN
            RETURN RESULT;
        END IF;
        
        IF (flag = 'S') THEN
            SELECT (data-num) INTO RESULT FROM DUAL;
            RETURN RESULT;
        ELSE
            u := 0;
            WHILE (u < num)
            LOOP
                SELECT (RESULT-1) INTO RESULT FROM DUAL;
                IF (isLavorativo(RESULT)) THEN
                    u := u +1;
                END IF;
            END LOOP;
            RETURN RESULT;
        END IF;
        
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('problemi nella sottraiGiorni: ' || SQLERRM);
    END sottraiGiorni;

    FUNCTION isFestivo(data DATE) RETURN BOOLEAN IS
        anno        NUMBER(4);
        mese        NUMBER(2);
        giorno      NUMBER(2);
        TYPE DurateType IS TABLE OF NUMBER(2) INDEX BY BINARY_INTEGER;
        durata DurateType;    
        ctr1 NUMBER(10);
        ctr2 NUMBER(10);
        ctr3 NUMBER(10);
        ctr4 NUMBER(10);
        ctr5 NUMBER(10);
        ctr6 NUMBER(10);
        psqDay NUMBER(2);
        psqMon NUMBER(2);
        psqYear NUMBER(4);
        dayOfWeek   NUMBER(1);
    BEGIN
        giorno := TO_NUMBER(SUBSTR(TO_CHAR(data, 'YYYYMMDD'), 7, 2));
        mese := TO_NUMBER(SUBSTR(TO_CHAR(data, 'YYYYMMDD'), 5, 2));
        anno := TO_NUMBER(SUBSTR(TO_CHAR(data, 'YYYYMMDD'), 1, 4));
        
        durata.DELETE;
        
        durata(0) := 31;
        IF (MOD(anno, 4) = 0) THEN
            durata(1) := 29;
        ELSE
            durata(1) := 28;
        END IF;
        durata(2) := 31;
        durata(3) := 30;
        durata(4) := 31;
        durata(5) := 30;
        durata(6) := 31;
        durata(7) := 31;
        durata(8) := 30;
        durata(9) := 31;
        durata(10) := 30;
        durata(11) := 31;
        
        --  FESTIVITA' MAGGIORI.
        IF (
        
           ((mese = 1) AND ((giorno = 1) OR (giorno = 6))) OR 
           
           ((mese = 4) AND (giorno = 25)) OR 
           
           ((mese = 5) AND (giorno = 1)) OR 
           
           ((mese = 8) AND (giorno = 15)) OR 
           
           ((mese = 11) AND (giorno = 1)) OR 
           
           ((mese = 12) AND ((giorno = 8) OR (giorno = 25) OR (giorno=26)))
           
           ) THEN
           RETURN TRUE;
        END IF;

        --  NUOVE FESTIVITA'.
        IF (mese = 12 AND giorno = 31 AND anno = 2001) THEN
           RETURN TRUE;
        END IF;
        
        IF ( mese = 6 AND giorno = 2 ) THEN
           RETURN TRUE;
        END IF;

        -- CALCOLO DELLA PASQUETTA.
        ctr1 := MOD(anno, 4);
        ctr2 := MOD(anno, 7);
        ctr3 := MOD(anno, 19);
        ctr6 := ctr3 * 19 + 24;
        ctr4 := MOD(ctr6, 30);
        ctr6 := ctr1 * 2 + ctr2 * 4 + ctr4 * 6 + 5;
        ctr5 := MOD(ctr6, 7);
        ctr6 := ctr4 + ctr5 + 22;

        IF (ctr6 <= 31) THEN
          psqDay := ctr6;
          psqMon := 3;
        ELSE
            psqMon := 4;
            ctr6 := ctr4 + ctr5 - 9;
            IF (((ctr3>10) AND (ctr4=28) AND (ctr5=6)) OR (ctr6>25)) THEN
              psqDay := ctr6 - 7;
            ELSE
              psqDay := ctr6;
            END IF;
        END IF;
        psqDay := psqDay +1;

        IF (psqDay > durata(psqMon -1)) THEN
          psqMon := psqMon +1;
          psqDay := 1;
        END IF;
        
        IF ((giorno = psqDay) AND (mese = psqMon)) THEN
           RETURN TRUE;
        END IF;
        
        -- SABATO E DOMENICA.
        dayOfWeek := TO_CHAR(data, 'D');
        
        IF ((dayOfWeek = 6) OR (dayOfWeek = 7)) THEN
           RETURN TRUE;
        END IF;

        RETURN FALSE;
    EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('problemi nella isFestivo: ' || SQLERRM);
    END isFestivo;

    FUNCTION isLavorativo(data DATE) RETURN BOOLEAN IS
    BEGIN
       RETURN NOT (isfestivo(data));
    EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('problemi nella isLavorativo: ' || SQLERRM);
    END isLavorativo;
END Tes_Utility;