CREATE OR REPLACE FUNCTION CARREGA_PERIFORN_FUN
(
   P_CDFILIAL        IN FILIAL.CDFILIAL%TYPE,
   P_CDFORNECED      IN FORNECEDOR.CDFORNECED%TYPE,
   P_DTPROGENTAF     IN DATE,
   P_IDDESCONSILIMCALCMP  IN PARASUPRI.IDDESCONSILIMCALCMP%TYPE DEFAULT 'N',
   P_CDARVPROD       IN PRODUTO.CDARVPROD%TYPE DEFAULT NULL,
   P_CDPERIENTR      IN PERIENTR.CDPERIENTR%TYPE DEFAULT NULL,
   P_IDTIPODIA       IN PERIENTR.IDTIPODIA%TYPE DEFAULT NULL,
   P_IDDIANUTIL      IN PERIENTR.IDDIANUTIL%TYPE DEFAULT NULL,
   P_IDUTCALENDCMPGERACAOAF      IN REFORFIL.IDUTCALENDCMPGERACAOAF%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_IDUTCALENDCMPGERACAOAF VARCHAR(1);
   V_CALEVAZIA       VARCHAR2(1) := 'N';
   V_DTPROGENTAF     DATE;
   V_IDDNULIMSEG     VARCHAR2(1);
   V_NRLIMISEGU      NUMBER(5);
BEGIN
   IF P_CDPERIENTR IS NULL THEN
      SELECT
         MAX(REFORFIL.CDPERIENTR),
         MAX(PERIENTR.IDTIPODIA),
         MAX(PERIENTR.IDDIANUTIL),
         NVL(MAX(REFORFIL.IDUTCALENDCMPGERACAOAF),'N')
      INTO
         V_CDPERIENTR, V_IDTIPODIA, V_IDDIANUTIL, V_IDUTCALENDCMPGERACAOAF
      FROM
         REFORFIL, PERIENTR
      WHERE
         REFORFIL.CDPERIENTR = PERIENTR.CDPERIENTR AND
         REFORFIL.CDFORNECED = P_CDFORNECED AND
         REFORFIL.CDFILIAL = P_CDFILIAL;
   ELSE
      V_CDPERIENTR              := P_CDPERIENTR;
      V_IDTIPODIA               := P_IDTIPODIA;
      V_IDDIANUTIL              := P_IDDIANUTIL;
      V_IDUTCALENDCMPGERACAOAF  := P_IDUTCALENDCMPGERACAOAF;
   END IF;

   /*INICIO IMPLEMENTACAO SOLICITACAO 185368*/
   IF P_IDDESCONSILIMCALCMP = 'N' THEN
      V_DTPROGENTAF := P_DTPROGENTAF;
   ELSE
      SELECT NVL(MAX(TO_NUMBER(NRLIMISEGU)),0)
        INTO V_NRLIMISEGU
        FROM LIMISEGPROD, PRODUTO
       WHERE LIMISEGPROD.CDPRODUTO = PRODUTO.CDPRODUTO
         AND LIMISEGPROD.CDFILIAL  = P_CDFILIAL
         AND NRLIMISEGU IS NOT NULL
         AND PRODUTO.CDARVPROD     = (SELECT /*+ INDEX (A PK_LIMISEGPROD) INDEX (B PK_PRODUTO) */
                                             MAX(CDARVPROD)
                                        FROM LIMISEGPROD A, PRODUTO B
                                       WHERE A.CDPRODUTO = B.CDPRODUTO
                                         AND A.CDFILIAL  = P_CDFILIAL
                                         AND INSTR(P_CDARVPROD,B.CDARVPROD) = 1);

      IF V_IDDNULIMSEG = 'W' THEN
         /* Se nao tiver paramerizacao de periodiciadde busca parametro geral*/
         SELECT
            NVL(MAX(PARASUPRI.IDDNULIMSEG), 'N') INTO V_IDDNULIMSEG
         FROM
            PARASUPRI,
            FILIAL
         WHERE
            (FILIAL.CDFILIAL    = P_CDFILIAL) AND
            (PARASUPRI.CDFILIAL = NVL(FILIAL.CDFILCENTCMP, FILIAL.CDFILIAL));
      END IF;
      IF V_IDDNULIMSEG = 'N' THEN
         SELECT NVL(MAX(D.DTDATA), P_DTPROGENTAF)
           INTO V_DTPROGENTAF
           FROM DATA D
          WHERE D.DTDATA <= P_DTPROGENTAF - V_NRLIMISEGU
            AND NOT EXISTS (SELECT 1
                              FROM FERICOMP F
                             WHERE F.CDFILIAL   = P_CDFILIAL
                               AND F.DTFERICOMP = D.DTDATA);
      ELSE
         SELECT NVL(MAX(D.DTDATA), P_DTPROGENTAF)
            INTO V_DTPROGENTAF
            FROM DATA D
           WHERE D.DTDATA <= P_DTPROGENTAF
             AND NOT EXISTS (SELECT 1
                               FROM FERICOMP F
                              WHERE F.CDFILIAL   = P_CDFILIAL
                                AND F.DTFERICOMP = D.DTDATA);
         IF V_NRLIMISEGU > 0 THEN
            SELECT NVL(MAX(D.DTDATA), V_DTPROGENTAF)
              INTO V_DTPROGENTAF
              FROM (SELECT DT.DTDATA, ROWNUM NRLIMISEGU
                      FROM DATA DT
                     WHERE DT.DTDATA < V_DTPROGENTAF
                       AND NOT EXISTS (SELECT 1
                                         FROM FERICOMP F
                                        WHERE F.CDFILIAL   = P_CDFILIAL
                                          AND F.DTFERICOMP = DT.DTDATA)
                                        ORDER BY DT.DTDATA DESC) D
             WHERE D.NRLIMISEGU = V_NRLIMISEGU;
          END IF;
      END IF;
   END IF;
   /*FIM IMPLEMENTACAO SOLICITACAO 185368*/

   V_NEW_DTPROGENTAF := V_DTPROGENTAF;
   IF V_IDUTCALENDCMPGERACAOAF = 'S' 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  <= V_DTPROGENTAF;
      IF V_NEW_DTPROGENTAF IS NULL THEN
         V_CALEVAZIA := 'S';
      END IF;
   END IF;
   IF (V_NEW_DTPROGENTAF IS NULL) OR (V_IDUTCALENDCMPGERACAOAF = 'N') THEN
      IF V_IDTIPODIA = 'S' THEN
         V_DATA := TRUNC(V_DTPROGENTAF);
         WHILE V_EXISTE IS NULL LOOP
            V_EXISTE := NULL;
            BEGIN
               SELECT '1' INTO V_EXISTE
               FROM DIASENTR
               WHERE
                  TO_NUMBER(TO_CHAR(V_DATA, 'D')) = TO_NUMBER(DIASENTR.DIENTREGA)   AND
                  DIASENTR.CDPERIENTR = V_CDPERIENTR AND
                  (TRUNC((TO_NUMBER(TO_CHAR(V_DATA, 'DD')) + 6) / 7) + IDOCOR01 = (2*TO_NUMBER(IDOCOR01)) OR
                   TRUNC((TO_NUMBER(TO_CHAR(V_DATA, 'DD')) + 6) / 7) + IDOCOR02 = (3*TO_NUMBER(IDOCOR02)) OR
                   TRUNC((TO_NUMBER(TO_CHAR(V_DATA, 'DD')) + 6) / 7) + IDOCOR03 = (4*TO_NUMBER(IDOCOR03)) OR
                   TRUNC((TO_NUMBER(TO_CHAR(V_DATA, 'DD')) + 6) / 7) + IDOCOR04 = (5*TO_NUMBER(IDOCOR04)) OR
                   /*CORRECAO GRSA 186787*/
                   ((TRUNC((TO_NUMBER(TO_CHAR((V_DATA), 'DD')) + 6) / 7) + IDOCOR05 = (6*TO_NUMBER(IDOCOR05)))
                    OR ((TRUNC((TO_NUMBER(TO_CHAR((V_DATA), 'DD')) + 6) / 7) + IDOCOR05 = (5*TO_NUMBER(IDOCOR05))) AND (TRUNC((TO_NUMBER(TO_CHAR((V_DATA+7), '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 := V_DATA;
            ELSE
               V_DATA := V_DATA - 1;
            END IF;
            /*CORRECAO GRSA 186787*/
            IF (V_DTPROGENTAF - V_DATA) > 60 THEN
               V_EXISTE := '1';
            END IF;
         END LOOP;
      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  <= V_DTPROGENTAF;

         IF V_NEW_DTPROGENTAF IS NULL THEN
            V_NEW_DTPROGENTAF := V_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(V_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(V_DTPROGENTAF)+1,-2)), 'DD')) THEN
               V_DIENTREGA := '-'||TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(V_DTPROGENTAF)+1,-2)), 'DD');
            END IF;
            V_NEW_DTPROGENTAF := TO_DATE(SUBSTR(V_DIENTREGA, 2, 2)||TO_CHAR(ADD_MONTHS(V_DTPROGENTAF, -1), 'MMYYYY'), 'DDMMYYYY');
         ELSE
            V_DTDIAENTR := V_DIENTREGA;
            IF TO_NUMBER(V_DTDIAENTR) > TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(V_DTPROGENTAF)+1,-1)), 'DD')) THEN
               V_DIENTREGA := TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(V_DTPROGENTAF)+1,-1)), 'DD');
            END IF;
            V_NEW_DTPROGENTAF := TO_DATE(V_DIENTREGA||TO_CHAR(V_DTPROGENTAF, 'MMYYYY'), 'DDMMYYYY');
         END IF;
      END IF;
   END IF;

   IF V_IDDIANUTIL = 'S' THEN
      IF V_CALEVAZIA = 'N' THEN
         SELECT COUNT(*) INTO V_COUNT
         FROM FERICOMP
         WHERE CDFILIAL = P_CDFILIAL AND
               DTFERICOMP = V_NEW_DTPROGENTAF;
         IF V_COUNT > 0 THEN
            CARREGA_PERIFORN(P_CDFILIAL, P_CDFORNECED, V_NEW_DTPROGENTAF - 1, V_NEW_DTPROGENTAF);
         END IF;
      END IF;
   END IF;
   RETURN(V_NEW_DTPROGENTAF);
END;
/