CREATE OR REPLACE FUNCTION CARREGA_PERIFILI_FUN
(
   P_CDFILIAL        IN FILIAL.CDFILIAL%TYPE,
   P_CDARVPROD       IN PRODUTO.CDARVPROD%TYPE,
   P_DTPROGENTAF     IN DATE,
   P_MAXCDARVPROD    IN PRODUTO.CDARVPROD%TYPE     DEFAULT NULL
)  RETURN DATE
IS
   V_NEW_DTPROGENTAF DATE;
   V_CDPERIENTR      VARCHAR2(2);
   V_IDTIPODIA       VARCHAR2(1);
   V_IDDIANUTIL      VARCHAR2(1);
   V_DIENTREGA       VARCHAR2(3);
   V_DTDIAENTR       VARCHAR2(3);
   V_COUNT           NUMBER;
   V_EXISTE          CHAR(1);
   V_DATA            DATE;
   V_AUX_DATA        DATE;
   V_CDARVPROD       PRODUTO.CDARVPROD%TYPE;
   V_IDOCOR          DIASENTR.IDOCOR01%TYPE;
BEGIN
   IF P_MAXCDARVPROD IS NULL THEN
      SELECT MAX(B.CDARVPROD)
        INTO V_CDARVPROD
        FROM PERFILPROD A, PRODUTO B
       WHERE A.CDPRODUTO = B.CDPRODUTO
         AND A.CDFILIAL  = P_CDFILIAL
         AND INSTR(P_CDARVPROD, B.CDARVPROD) = 1;
   ELSE
      V_CDARVPROD := P_MAXCDARVPROD;
   END IF;
   SELECT MAX(PERFILPROD.CDPERIENTR),
          MAX(PERIENTR.IDTIPODIA),
          MAX(PERIENTR.IDDIANUTIL)
     INTO V_CDPERIENTR, V_IDTIPODIA, V_IDDIANUTIL
     FROM PERFILPROD, PERIENTR
    WHERE PERFILPROD.CDFILIAL   = P_CDFILIAL
      AND PERFILPROD.CDPERIENTR = PERIENTR.CDPERIENTR
      AND PERFILPROD.CDPRODUTO  = V_CDARVPROD;
   V_NEW_DTPROGENTAF := P_DTPROGENTAF;
   
   IF V_IDTIPODIA = 'S' THEN
      V_DATA := TRUNC(P_DTPROGENTAF);
      SELECT NVL(MAX('1'), '?')
        INTO V_IDOCOR  
        FROM DIASENTR
       WHERE TO_NUMBER(TO_CHAR(V_DATA, 'D')) = TO_NUMBER(DIASENTR.DIENTREGA)
         AND DIASENTR.CDPERIENTR = V_CDPERIENTR
         AND IDOCOR01 = '1' 
         AND IDOCOR02 = '1'
         AND IDOCOR03 = '1'
         AND IDOCOR04 = '1'
         AND IDOCOR05 = '1';
         
      IF V_IDOCOR <> '?' THEN
         V_NEW_DTPROGENTAF := V_DATA;
      ELSE   
      --RAISE_APPLICATION_ERROR(-20501, 'W');   
      FOR C_DATA IN(SELECT TRUNC(DTDATA) DATA
                      FROM DATA
                     WHERE DTDATA BETWEEN V_DATA-35 AND V_DATA
                     ORDER BY DTDATA DESC)LOOP
         V_EXISTE := NULL;
         BEGIN
            SELECT '1' INTO V_EXISTE
            FROM DIASENTR
            WHERE
               TO_NUMBER(TO_CHAR(C_DATA.DATA, 'D')) = TO_NUMBER(DIASENTR.DIENTREGA)   AND
               DIASENTR.CDPERIENTR = V_CDPERIENTR AND
               (TRUNC((TO_NUMBER(TO_CHAR(C_DATA.DATA, 'DD')) + 6) / 7) + IDOCOR01 = (2*TO_NUMBER(IDOCOR01)) OR
                TRUNC((TO_NUMBER(TO_CHAR(C_DATA.DATA, 'DD')) + 6) / 7) + IDOCOR02 = (3*TO_NUMBER(IDOCOR02)) OR
                TRUNC((TO_NUMBER(TO_CHAR(C_DATA.DATA, 'DD')) + 6) / 7) + IDOCOR03 = (4*TO_NUMBER(IDOCOR03)) OR
                TRUNC((TO_NUMBER(TO_CHAR(C_DATA.DATA, 'DD')) + 6) / 7) + IDOCOR04 = (5*TO_NUMBER(IDOCOR04)) OR
                TRUNC((TO_NUMBER(TO_CHAR(C_DATA.DATA, 'DD')) + 6) / 7) + IDOCOR05 = (6*TO_NUMBER(IDOCOR05)));
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_EXISTE := NULL;
            END;
         END;
         IF V_EXISTE IS NOT NULL THEN
            V_NEW_DTPROGENTAF := C_DATA.DATA;
            EXIT;
         END IF;
         IF (P_DTPROGENTAF - C_DATA.DATA) > 31 THEN
            V_NEW_DTPROGENTAF := C_DATA.DATA;
            EXIT;
         END IF;
      END LOOP;
      END IF;
   END IF;
   IF V_IDTIPODIA = 'R' THEN
      SELECT
         MAX(C.DTENTREGA) INTO V_NEW_DTPROGENTAF
      FROM
         CALECOMP C
      WHERE C.CDFILIAL    = P_CDFILIAL
        AND C.CDPERIENTR  = V_CDPERIENTR
        AND C.DTENTREGA  <= P_DTPROGENTAF;

      IF V_NEW_DTPROGENTAF IS NULL THEN
         V_NEW_DTPROGENTAF := P_DTPROGENTAF;
      END IF;
   END IF;
   IF V_IDTIPODIA = 'M' THEN
      SELECT MAX(DIENTREGA) INTO V_DIENTREGA
      FROM
        (SELECT '-'||DIENTREGA DIENTREGA
         FROM DIASENTR
         WHERE CDPERIENTR = V_CDPERIENTR
         UNION ALL
         SELECT DIENTREGA
         FROM DIASENTR
         WHERE CDPERIENTR = V_CDPERIENTR)
      WHERE
         DIENTREGA <= TO_CHAR(P_DTPROGENTAF, 'DD');

      IF SUBSTR(V_DIENTREGA, 1, 1) = '-' THEN
         V_DTDIAENTR := SUBSTR(V_DIENTREGA, 2, 2);
         IF TO_NUMBER(V_DTDIAENTR) > TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(P_DTPROGENTAF)+1,-2)), 'DD')) THEN
            V_DIENTREGA := '-'||TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(P_DTPROGENTAF)+1,-2)), 'DD');
         END IF;
         V_NEW_DTPROGENTAF := TO_DATE(SUBSTR(V_DIENTREGA, 2, 2)||TO_CHAR(ADD_MONTHS(P_DTPROGENTAF, -1), 'MMYYYY'), 'DDMMYYYY');
      ELSE
         V_DTDIAENTR := V_DIENTREGA;
         IF TO_NUMBER(V_DTDIAENTR) > TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(P_DTPROGENTAF)+1,-1)), 'DD')) THEN
            V_DIENTREGA := TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(P_DTPROGENTAF)+1,-1)), 'DD');
         END IF;
         V_NEW_DTPROGENTAF := TO_DATE(V_DIENTREGA||TO_CHAR(P_DTPROGENTAF, 'MMYYYY'), 'DDMMYYYY');
      END IF;
   END IF;
   V_AUX_DATA := V_NEW_DTPROGENTAF;
   IF V_IDDIANUTIL = 'S' THEN   
   SELECT COUNT(*) INTO V_COUNT
      FROM FERICOMP
      WHERE CDFILIAL = P_CDFILIAL AND
            TO_CHAR(DTFERICOMP, 'DD/MM/YYYY') = TO_CHAR(V_NEW_DTPROGENTAF, 'DD/MM/YYYY');
      IF V_COUNT > 0 THEN
         BEGIN
            SELECT CARREGA_PERIFILI_FUN(P_CDFILIAL, V_CDARVPROD, V_NEW_DTPROGENTAF - 1, V_CDARVPROD)
           INTO V_AUX_DATA
           FROM DUAL;
         EXCEPTION
            WHEN OTHERS THEN BEGIN
               RETURN(V_NEW_DTPROGENTAF);
            END;
         END;
      END IF;
   END IF;
   RETURN(V_AUX_DATA);
END;
/