CREATE OR REPLACE PROCEDURE CANCELA_EXPEDICAO(
    P_FILIAL IN NOTAFISCAL.CDFILIAL%TYPE,
    P_LANCTONF IN NOTAFISCAL.NRLANCTONF%TYPE)

IS
   V_COUNTREPLIC  INTEGER;
   V_COUNTOL      INTEGER;
   BOTRAVANFRELAC BOOLEAN;
   V_CDFILIMOVI   FILIAL.CDFILIAL%TYPE;
   V_DTPROCESSA   PARAMFILIAL.DTPROCESSA%TYPE;
   V_DTINICIAL    PARAMFILIAL.DTPROCESSA%TYPE;
   V_DTFINAL      PARAMFILIAL.DTPROCESSA%TYPE;
   V_IDSTATUS     NOTAFISCAL.IDSTATUS%TYPE;
   V_IDSTATUSNFE  NOTAFISCAL.IDSTATUSNFE%TYPE;
   V_NREXPEDICAO  NOTAFISCAL.NREXPEDICAO%TYPE;
   V_NUMEXP       INTEGER;
   V_NRLANCESTQ    LANCTOESTOQ.NRLANCESTQ%TYPE;
   V_DTENTRSAID   NOTAFISCAL.DTENTRSAID%TYPE;
BEGIN

   DELETE FROM TRAVACANCELAEXPEDICAO
   WHERE CDFILIAL = P_FILIAL
     AND NRLANCTONF = P_LANCTONF;
     
   /*VERIFICA SE EXISTE REPLICACAO E DELETA A REPLICACAO*/
   /*A NF REPLICADA NAO PODE ESTAR TRANSMITIDA, ACEITA, DENEGADA, APROVADA, EMITIDA*/
   /* O ESTOQUE NAO PODE FICAR NEGATIVO */
   V_COUNTREPLIC := 0;
   V_COUNTOL     := 0;
   BOTRAVANFRELAC := FALSE;
   SELECT COUNT(1)
     INTO V_COUNTREPLIC
     FROM NOTAFISCAL
    WHERE IDDOCREPLIC = P_FILIAL ||P_LANCTONF;

   SELECT COUNT(1)
     INTO V_COUNTOL
     FROM NOTAFISCAL
    WHERE DSNOTAFISORIGOL = P_FILIAL ||P_LANCTONF;

   IF ((V_COUNTREPLIC > 0) OR (V_COUNTOL > 0)) THEN
      FOR C_NOTAORIG IN (SELECT N.CDFILIAL, N.NRLANCTONF, N.IDSTATUSNFE, N.IDSTATUS, N.IDENTRSAIDOP, N.CDFILIEMIT, N.CDFILIRECE,
                                N.DTENTRSAID, L.NRLANCESTQ, N.IDDOCREPLIC, N.DSNOTAFISORIGOL
                           FROM NOTAFISCAL N, LANCTOESTOQ L
                          WHERE IDDOCREPLIC = P_FILIAL ||P_LANCTONF
                            AND N.CDFILIAL = L.CDFILIAL
                            AND N.NRLANCTONF = L.NRLANCTONF
                         UNION ALL
                         SELECT N.CDFILIAL, N.NRLANCTONF, N.IDSTATUSNFE, N.IDSTATUS, N.IDENTRSAIDOP, N.CDFILIEMIT, N.CDFILIRECE,
                                N.DTENTRSAID, L.NRLANCESTQ, N.IDDOCREPLIC, N.DSNOTAFISORIGOL
                           FROM NOTAFISCAL N, LANCTOESTOQ L
                          WHERE DSNOTAFISORIGOL = P_FILIAL ||P_LANCTONF
                            AND N.CDFILIAL = L.CDFILIAL
                            AND N.NRLANCTONF = L.NRLANCTONF   ) LOOP

         IF ((C_NOTAORIG.IDSTATUSNFE NOT IN ('T', 'A', 'D')) OR
             (C_NOTAORIG.IDSTATUS NOT IN ('E', 'A'))) THEN
            DELETA_NF(C_NOTAORIG.CDFILIAL, C_NOTAORIG.NRLANCTONF);
            IF C_NOTAORIG.IDENTRSAIDOP = 'E' THEN
               V_CDFILIMOVI := C_NOTAORIG.CDFILIRECE;
            ELSE
               V_CDFILIMOVI := C_NOTAORIG.CDFILIEMIT;
            END IF;

            SELECT MAX(DTPROCESSA)
              INTO V_DTPROCESSA
              FROM PARAMFILIAL
             WHERE CDFILIAL = V_CDFILIMOVI;

            IF C_NOTAORIG.DTENTRSAID >= V_DTPROCESSA THEN
               V_DTINICIAL := V_DTPROCESSA;
            ELSE
               V_DTINICIAL := C_NOTAORIG.DTENTRSAID;
            END IF;

            IF C_NOTAORIG.DTENTRSAID <= V_DTPROCESSA THEN
               V_DTFINAL := V_DTPROCESSA;
            ELSE
               V_DTFINAL := C_NOTAORIG.DTENTRSAID;
            END IF;
            ATUALIZA_ESTOQUE(V_CDFILIMOVI, V_DTINICIAL, V_DTFINAL, C_NOTAORIG.CDFILIAL, C_NOTAORIG.NRLANCESTQ, NULL);

            /* Atualiza a Itlanctoest para nao evoluir estoque caso encontre erro ao atualizar o estoque */
            FOR C_PRODIRREG IN (SELECT NRMOVILOTE, CDFILIAL, CDALMOXARIFE, CDLOCALESTOQ,
                                       CDPRODUTO, NRLOTEESTQ, NRSUBLOTE
                                  FROM MOVILOTE
                                 WHERE NRMOVILOTE = C_NOTAORIG.CDFILIAL||C_NOTAORIG.NRLANCESTQ
                                   AND CDFILIAL   = V_CDFILIMOVI)
            LOOP
               BOTRAVANFRELAC := TRUE;
               INSERT INTO TRAVACANCELAEXPEDICAO
               (CDFILIAL, NRLANCTONF, IDERRO, CDFILIALIT, NRLANCTONFIT, CDFILIMOVI)
               VALUES
               (P_FILIAL, P_LANCTONF, 'TRAVAESTNFRELAC', C_NOTAORIG.CDFILIAL, C_NOTAORIG.NRLANCTONF, V_CDFILIMOVI);
            END LOOP;

            DELETE MOVILOTE
             WHERE NRMOVILOTE = C_NOTAORIG.CDFILIAL||C_NOTAORIG.NRLANCESTQ;
         ELSE
            BOTRAVANFRELAC := TRUE;

            INSERT INTO TRAVACANCELAEXPEDICAO
            (CDFILIAL, NRLANCTONF, IDERRO, CDFILIALIT, NRLANCTONFIT, CDFILIMOVI)
            VALUES
            (P_FILIAL, P_LANCTONF, 'TRAVASTATUSNFRELAC', C_NOTAORIG.CDFILIAL, C_NOTAORIG.NRLANCTONF, V_CDFILIMOVI);

            EXIT;
         END IF;
      END LOOP;
   END IF;
   IF NOT(BOTRAVANFRELAC) THEN
      ATUALIZA_SOLCITACAO_CMP(P_FILIAL, P_LANCTONF);

      /*VALIDAR SE A NOTA PERTENCE A UM MAPA DE COMISSAO BLOQUEADO*/

      SELECT MAX(N.IDSTATUS), MAX(N.IDSTATUSNFE), MAX(N.NREXPEDICAO), MAX(L.NRLANCESTQ), MAX(N.DTENTRSAID)
        INTO V_IDSTATUS, V_IDSTATUSNFE, V_NREXPEDICAO, V_NRLANCESTQ, V_DTENTRSAID
        FROM NOTAFISCAL N, LANCTOESTOQ L
       WHERE N.CDFILIAL = P_FILIAL
         AND N.NRLANCTONF = P_LANCTONF
         AND N.CDFILIAL = L.CDFILIAL(+)
         AND N.NRLANCTONF = L.NRLANCTONF(+);

      IF ((V_IDSTATUS NOT IN ('E', 'A', 'C', 'L')) AND (V_IDSTATUSNFE NOT IN ('A', 'T', 'D'))) THEN
         ATUALIZA_COLETA_PED(P_FILIAL, P_LANCTONF);

         ATUALIZA_ITENS_PED(P_FILIAL, P_LANCTONF);

         ATUALIZA_RETIRADA_EXP(P_FILIAL, P_LANCTONF);

         CANCELA_ATIVO_EXP(P_FILIAL, P_LANCTONF);

         DELETA_NF(P_FILIAL, P_LANCTONF);

         SELECT COUNT(NREXPEDICAO)
           INTO V_NUMEXP
           FROM NOTAFISCAL
          WHERE NREXPEDICAO = V_NREXPEDICAO
            AND CDFILIAL    = P_FILIAL;

         IF V_NUMEXP = 0 THEN
            DELETE FROM EXPEDICAO
            WHERE NREXPEDICAO = V_NREXPEDICAO
              AND CDFILIAL    = P_FILIAL;
         END IF;

         SELECT MAX(DTPROCESSA)
           INTO V_DTPROCESSA
           FROM PARAMFILIAL
          WHERE CDFILIAL = P_FILIAL;

         IF V_DTENTRSAID >= V_DTPROCESSA THEN
            V_DTINICIAL := V_DTPROCESSA;
         ELSE
            V_DTINICIAL := V_DTENTRSAID;
         END IF;

         IF V_DTENTRSAID <= V_DTPROCESSA THEN
            V_DTFINAL := V_DTPROCESSA;
         ELSE
            V_DTFINAL := V_DTENTRSAID;
         END IF;

         ATUALIZA_ESTOQUE(P_FILIAL, V_DTINICIAL, V_DTFINAL, P_FILIAL, V_NRLANCESTQ, NULL);

         /* VERIFICA ERRO NA ATUALIZACAO DE ESTOQUE */
         FOR C_PRODIRREG IN (SELECT NRMOVILOTE, CDFILIAL, CDALMOXARIFE, CDLOCALESTOQ,
                                    CDPRODUTO, NRLOTEESTQ, NRSUBLOTE
                               FROM MOVILOTE
                              WHERE NRMOVILOTE = P_FILIAL||V_NRLANCESTQ
                                AND CDFILIAL   = P_FILIAL)
            LOOP

               INSERT INTO TRAVACANCELAEXPEDICAO
               (CDFILIAL, NRLANCTONF, IDERRO, CDFILIALIT, NRLANCTONFIT, CDFILIMOVI)
               VALUES
               (P_FILIAL, P_LANCTONF, 'TRAVAESTNF', '', '', '');
            END LOOP;

            DELETE MOVILOTE
             WHERE NRMOVILOTE = P_FILIAL||V_NRLANCESTQ;

      ELSE
         INSERT INTO TRAVACANCELAEXPEDICAO
         (CDFILIAL, NRLANCTONF, IDERRO, CDFILIALIT, NRLANCTONFIT, CDFILIMOVI)
         VALUES
         (P_FILIAL, P_LANCTONF, 'TRAVASTATUSNF', '', '', '');
      END IF;
   END IF;
END;
/