SET PAGESIZE 100;
ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY';

DROP PACKAGE EMP_PROG;
DROP TYPE RECORD_TYPE;
DROP TYPE RECORDS_TYPE;

CREATE TYPE RECORD_TYPE AS OBJECT (EVAL_YEAR NUMBER, EVAL NUMBER);
/

CREATE TYPE RECORDS_TYPE AS VARRAY(3) OF RECORD_TYPE;
/

CREATE OR REPLACE PACKAGE EMP_PROG AS
  FUNCTION EVAL_NEXT_DATE(RN INTEGER, EVAL_YEAR NUMBER, EVAL NUMBER, DAT_START_JOB DATE) RETURN DATE;
END EMP_PROG;
/

CREATE OR REPLACE PACKAGE BODY EMP_PROG 
AS
  WINDOW RECORDS_TYPE;
  LAST_PROG_DATE DATE DEFAULT NULL;
  
  FUNCTION EVAL_NEXT_DATE(RN INTEGER, EVAL_YEAR NUMBER, EVAL NUMBER, DAT_START_JOB DATE) RETURN DATE
  IS
    AVERAGE       NUMBER;
    MONTHS_TO_ADD NUMBER;
    CHANGED       BOOLEAN DEFAULT FALSE;
  BEGIN
    IF RN = 1 THEN 
      WINDOW := RECORDS_TYPE();
      LAST_PROG_DATE := DAT_START_JOB;
    END IF;
    
    WINDOW.EXTEND(1);
    WINDOW(WINDOW.COUNT) := RECORD_TYPE(EVAL_YEAR, EVAL);
    IF WINDOW.COUNT = 3 THEN
      IF WINDOW(1).EVAL_YEAR >= EXTRACT(YEAR FROM LAST_PROG_DATE) THEN
        SELECT AVG(EVAL) INTO AVERAGE FROM TABLE(WINDOW);
        CASE 
          WHEN AVERAGE >= 0 AND AVERAGE <= 4 THEN MONTHS_TO_ADD := 42;
          WHEN AVERAGE > 4 AND AVERAGE <= 6 THEN MONTHS_TO_ADD := 36;
          WHEN AVERAGE > 6 AND AVERAGE <= 10 THEN MONTHS_TO_ADD := 30;
        END CASE;
        
        LAST_PROG_DATE := LAST_PROG_DATE + NUMTOYMINTERVAL(MONTHS_TO_ADD, 'MONTH');
        CHANGED := TRUE;
      END IF;
      WINDOW(1) := WINDOW(2);
      WINDOW(2) := WINDOW(3);
      WINDOW.TRIM;
    END IF;
    
    IF CHANGED THEN
      CHANGED := FALSE;
      RETURN LAST_PROG_DATE;
    END IF;
    
    RETURN NULL;
  END EVAL_NEXT_DATE;
  
END EMP_PROG;
/

WITH V00 
AS 
  (SELECT ROW_NUMBER() OVER (PARTITION BY EMP_EVAL.ID_EMP ORDER BY EMP_EVAL.E_YEAR) RN,
          EMP_EVAL.ID_EMP,
          EMP_EVAL.E_YEAR,
          EVAL,
          DAT_START_JOB
   FROM EMP_EVAL,
        EMP
   WHERE 1 = 1
         AND EMP_EVAL.ID_EMP = EMP.ID_EMP
   ORDER BY EMP_EVAL.ID_EMP, EMP_EVAL.E_YEAR)
SELECT ID_EMP, E_YEAR, EVAL, DAT_START_JOB, EMP_PROG.EVAL_NEXT_DATE(RN, E_YEAR, EVAL, DAT_START_JOB) DAT_PROG
FROM V00;