CREATE OR REPLACE
PROCEDURE SEMANAL_DATA_ENTR_MES
  (P_CDFILIAL     IN VARCHAR2,
   P_CDPERIENTR   IN VARCHAR2,
   P_DTINICIAL    IN DATE,
   P_IDDIANUTIL   IN VARCHAR2,
   P_DTPROCESSA   IN DATE) IS
   V_CONTAUXI   NUMBER;
   V_DIENTREGA  VARCHAR2(2);
   V_DTDIAENTR  DATE;
   V_IDDIASEM   NUMBER;
   V_DTENTRAUX  DATE;
   V_PRIMVEZ    NUMBER;
   V_JAEXISTE   NUMBER;
   V_NRORG      NUMBER;
   V_PERFILPROD_SITENTR VARCHAR2(1); -- Parametro que define se atrasa (A) ou adianta (D) a entrega do produto

/*-------- CURSOR QUE CONTEM AS DATAS DE ENTREGA NO MES COM BASE NOS DIAS DE ENTREGA SEMANAIS ------*/

   CURSOR C_DATAS_ENTR IS
(SELECT NEXT_DAY(LAST_DAY(P_DTINICIAL - 1),TO_NUMBER(DIENTREGA))
        FROM DIASENTR
        WHERE
         NRORG = V_NRORG AND
         CDPERIENTR = P_CDPERIENTR AND
         IDOCOR01 = '1' AND
         NEXT_DAY(LAST_DAY(P_DTINICIAL - 1),TO_NUMBER(DIENTREGA)) >= P_DTPROCESSA
UNION
        SELECT NEXT_DAY(
         NEXT_DAY(LAST_DAY(P_DTINICIAL - 1),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA))
          FROM DIASENTR
           WHERE
            NRORG = V_NRORG AND
            CDPERIENTR = P_CDPERIENTR AND
            IDOCOR02 = '1' AND
           TO_CHAR(NEXT_DAY(
            NEXT_DAY(LAST_DAY(P_DTINICIAL - 1),TO_NUMBER(DIENTREGA))
           ,TO_NUMBER(DIENTREGA)),'MM')
           = TO_CHAR(P_DTINICIAL,'MM') AND
          ( NEXT_DAY(
         NEXT_DAY(LAST_DAY(P_DTINICIAL - 1),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)) >= P_DTPROCESSA)
UNION
        SELECT NEXT_DAY(
         NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL - 1),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA))
          FROM DIASENTR
           WHERE
            NRORG = V_NRORG AND
            CDPERIENTR = P_CDPERIENTR AND
            IDOCOR03 = '1' AND
           TO_CHAR(NEXT_DAY(
            NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL - 1),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)),'MM')
           = TO_CHAR(P_DTINICIAL,'MM') AND
          (NEXT_DAY(NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL - 1),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)) >= P_DTPROCESSA)

UNION
        SELECT NEXT_DAY(NEXT_DAY(
         NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL - 1),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA))
          FROM DIASENTR
           WHERE
            NRORG = V_NRORG AND
            CDPERIENTR = P_CDPERIENTR AND
            IDOCOR04 = '1' AND
           TO_CHAR(NEXT_DAY(NEXT_DAY(
            NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL - 1),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)),'MM')
           = TO_CHAR(P_DTINICIAL,'MM') AND
          (NEXT_DAY(NEXT_DAY(
           NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL - 1),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)) >= P_DTPROCESSA)

UNION
        SELECT NEXT_DAY(LAST_DAY(P_DTINICIAL)-7,TO_NUMBER(DIENTREGA))
          FROM DIASENTR
           WHERE
            NRORG = V_NRORG AND
            CDPERIENTR = P_CDPERIENTR AND
            IDOCOR05 = '1' AND  
           TO_CHAR(NEXT_DAY(LAST_DAY(P_DTINICIAL)-7,TO_NUMBER(DIENTREGA)),'MM')
           = TO_CHAR(P_DTINICIAL,'MM') AND
            (NEXT_DAY(LAST_DAY(P_DTINICIAL)-7,TO_NUMBER(DIENTREGA)) >= P_DTPROCESSA)
             ) ORDER BY 1;

/*--- CURSOR QUE CONTEM AS DATAS DE ENTREGA DO PROXIMO MES    ------*/
/*-   COM BASE NOS DIAS DE ENTREGA SEMANAIS                   ------*/

   CURSOR C_DATAS_PROX IS
(SELECT NEXT_DAY(LAST_DAY(P_DTINICIAL),TO_NUMBER(DIENTREGA))
        FROM DIASENTR
        WHERE
         NRORG = V_NRORG AND
         CDPERIENTR = P_CDPERIENTR AND
         IDOCOR01 = '1' AND
         (NEXT_DAY(LAST_DAY(P_DTINICIAL),TO_NUMBER(DIENTREGA)) >= P_DTPROCESSA)
UNION
        SELECT NEXT_DAY(
         NEXT_DAY(LAST_DAY(P_DTINICIAL),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA))
          FROM DIASENTR
           WHERE
            NRORG = V_NRORG AND
            CDPERIENTR = P_CDPERIENTR AND
            IDOCOR02 = '1' AND
           TO_CHAR(NEXT_DAY(
            NEXT_DAY(LAST_DAY(P_DTINICIAL),TO_NUMBER(DIENTREGA))
           ,TO_NUMBER(DIENTREGA)),'MM')
           = TO_CHAR(LAST_DAY(P_DTINICIAL)+1,'MM') AND
           (NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL),TO_NUMBER(DIENTREGA))
           ,TO_NUMBER(DIENTREGA)) >= P_DTPROCESSA)
UNION
        SELECT NEXT_DAY(
         NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA))
          FROM DIASENTR
           WHERE
            NRORG = V_NRORG AND
            CDPERIENTR = P_CDPERIENTR AND
            IDOCOR03 = '1' AND
           TO_CHAR(NEXT_DAY(
            NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)),'MM')
           = TO_CHAR(LAST_DAY(P_DTINICIAL)+1,'MM') AND
          (NEXT_DAY(NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)) >= P_DTPROCESSA)

UNION
        SELECT NEXT_DAY(NEXT_DAY(
         NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA))
          FROM DIASENTR
           WHERE
            NRORG = V_NRORG AND
            CDPERIENTR = P_CDPERIENTR AND
            IDOCOR04 = '1' AND
           TO_CHAR(NEXT_DAY(NEXT_DAY(
            NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)),'MM')
           = TO_CHAR(LAST_DAY(P_DTINICIAL)+1,'MM') AND
          (NEXT_DAY(NEXT_DAY(NEXT_DAY(NEXT_DAY(LAST_DAY(P_DTINICIAL),TO_NUMBER(DIENTREGA))
          ,TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)),TO_NUMBER(DIENTREGA)) >= P_DTPROCESSA)

UNION
        SELECT NEXT_DAY(LAST_DAY(LAST_DAY(P_DTINICIAL)+1)-7,TO_NUMBER(DIENTREGA))
          FROM DIASENTR
           WHERE
            NRORG = V_NRORG AND
            CDPERIENTR = P_CDPERIENTR AND
            IDOCOR05 = '1' AND 
           TO_CHAR(NEXT_DAY(LAST_DAY(LAST_DAY(P_DTINICIAL)+1),TO_NUMBER(DIENTREGA)),'MM')
           = TO_CHAR(LAST_DAY(P_DTINICIAL)+1,'MM') AND
           (NEXT_DAY(LAST_DAY(LAST_DAY(P_DTINICIAL)+1)-7,TO_NUMBER(DIENTREGA)) >= P_DTPROCESSA)
           ) ORDER BY 1;


BEGIN

   SELECT NRORG
     INTO V_NRORG
     FROM FILIAL
    WHERE CDFILIAL = P_CDFILIAL;

   SELECT NVL(MAX(IDSITUENTREGA), 'A')
     INTO V_PERFILPROD_SITENTR
     FROM PERFILPROD PFP
     WHERE PFP.CDFILIAL   = P_CDFILIAL
       AND PFP.CDPERIENTR = P_CDPERIENTR
       AND NVL(PFP.IDSITUENTREGA, 'A') = 'D';

    V_DTENTRAUX := P_DTINICIAL;
    OPEN C_DATAS_ENTR;
    LOOP
      FETCH C_DATAS_ENTR
      INTO V_DTDIAENTR;
      EXIT WHEN C_DATAS_ENTR%NOTFOUND;

 /*- CALCULA UMA DATA DE ENTREGA ANTERIOR A UMA DATA NAO UTIL -*/

     IF P_IDDIANUTIL = 'S' THEN
      V_CONTAUXI := 1;
       WHILE V_CONTAUXI > 0 LOOP
         SELECT COUNT(*) INTO V_CONTAUXI
         FROM FERICOMP
         WHERE
         CDFILIAL = P_CDFILIAL AND
         DTFERICOMP = V_DTDIAENTR;
         IF V_CONTAUXI > 0 THEN
            IF NVL(V_PERFILPROD_SITENTR, 'A') <> 'D' THEN
               V_DTDIAENTR := V_DTDIAENTR - 1;
            ELSE
               V_DTDIAENTR := V_DTDIAENTR + 1;
            END IF;
         END IF;
       END LOOP;
     END IF;
/* - Verifica se ja existe a data de entrega que foi calculada por um dia nao util -*/
     SELECT COUNT(*) INTO V_JAEXISTE
     FROM CALECOMP
     WHERE
       CDFILIAL = P_CDFILIAL AND
       CDPERIENTR = P_CDPERIENTR AND
       DTENTREGA = V_DTDIAENTR;
/*- Se ja existir a data pula -*/
     IF V_JAEXISTE = 0 THEN
   /*- VERIFICA SE JA EXISTE ALGUMA DATA DE ENTREGA CALCULADA PARA  -*/
   /*- INCLUIR A DATA ANTERIOR A PROXIMA ENTREGA  -*/

         SELECT COUNT(*)
           INTO V_PRIMVEZ
           FROM CALECOMP
          WHERE CDFILIAL = P_CDFILIAL
            AND CDPERIENTR = P_CDPERIENTR
            AND TO_CHAR(DTENTREGA,'MMYYYY') = TO_CHAR(V_DTENTRAUX,'MMYYYY')
            AND TO_CHAR(DTANTPROXENT,'MMYYYY') <> (SELECT TO_CHAR(MIN(DTENTREGA)-1,'MMYYYY')
                                                      FROM CALECOMP CALE
                                                     WHERE CALE.CDFILIAL = CALECOMP.CDFILIAL
                                                       AND CALE.CDPERIENTR = CALECOMP.CDPERIENTR
                                                       AND TO_CHAR(CALE.DTENTREGA,'MMYYYY') <> TO_CHAR(V_DTENTRAUX,'MMYYYY')
                                                       AND TO_CHAR(CALE.DTENTREGA,'MMYYYY') > TO_CHAR(V_DTENTRAUX,'MMYYYY'));

   /*- INCLUI A DATA ANTERIOR A PROXIMA ENTREGA  -*/
         IF V_PRIMVEZ > 0 THEN
            UPDATE CALECOMP SET DTANTPROXENT = V_DTDIAENTR - 1
            WHERE CDFILIAL = P_CDFILIAL AND
            CDPERIENTR = P_CDPERIENTR AND
            DTENTREGA = V_DTENTRAUX;

            COMMIT;
         END IF;

   /*- INCLUI A DATA DE ENTREGA CALCULADA NO CALENDARIO DE COMPRAS -*/
         INSERT INTO CALECOMP
           (CDFILIAL,
            CDPERIENTR,
            DTENTREGA,
            DTANTPROXENT)
           VALUES
           (P_CDFILIAL,
            P_CDPERIENTR,
            V_DTDIAENTR,
            V_DTENTRAUX);

         COMMIT;
     END IF;
       V_DTENTRAUX := V_DTDIAENTR;
    END LOOP;
    CLOSE C_DATAS_ENTR;

/*- PEGA A PRIMEIRA DATA ANTERIOR A PROXIMA ENTREGA  -*/

    OPEN C_DATAS_PROX;
    LOOP
      FETCH C_DATAS_PROX
      INTO V_DTENTRAUX;
      EXIT WHEN C_DATAS_PROX%NOTFOUND;

      IF P_IDDIANUTIL = 'S' THEN
          SELECT COUNT(*) INTO V_CONTAUXI
          FROM FERICOMP
          WHERE
          CDFILIAL = P_CDFILIAL AND
          DTFERICOMP = V_DTENTRAUX;
          IF V_CONTAUXI = 0 THEN
            EXIT;
          END IF;
      ELSE
         EXIT;
      END IF;
    END LOOP;
    CLOSE C_DATAS_PROX;

    V_DTENTRAUX := V_DTENTRAUX - 1;

/*- VERIFICA SE JA EXISTE A ULTIMA DATA DE ENTREGA QUE RECEBERA A   -*/
/*- A DATA ANTERIOR A PROXIMA ENTREGA  -*/

    SELECT COUNT(*)
      INTO V_PRIMVEZ
      FROM CALECOMP
     WHERE CDFILIAL   = P_CDFILIAL
       AND CDPERIENTR = P_CDPERIENTR
       AND TO_CHAR(DTENTREGA,'MMYYYY')  = TO_CHAR(V_DTDIAENTR,'MMYYYY')
       AND TO_CHAR(DTANTPROXENT,'MMYYYY') <> (SELECT TO_CHAR(MIN(DTENTREGA)+1,'MMYYYY')
                                                FROM CALECOMP CALE
                                               WHERE CALE.CDFILIAL = CALECOMP.CDFILIAL
                                                 AND CALE.CDPERIENTR = CALECOMP.CDPERIENTR
                                                 AND TO_CHAR(CALE.DTENTREGA,'MMYYYY') <> TO_CHAR(V_DTENTRAUX,'MMYYYY')
                                                 AND TO_CHAR(CALE.DTENTREGA,'MMYYYY') > TO_CHAR(V_DTENTRAUX,'MMYYYY'));

    IF V_PRIMVEZ > 0 THEN
/*- INCLUI A DATA ANTERIOR A PROXIMA ENTREGA PARA A A ULTIMA DATA DE ENTREGA DO MES -*/
      UPDATE CALECOMP SET DTANTPROXENT = V_DTENTRAUX
      WHERE CDFILIAL = P_CDFILIAL AND
      CDPERIENTR = P_CDPERIENTR AND
      DTENTREGA = V_DTDIAENTR;

      COMMIT;
    END IF;
END;
/