CREATE OR REPLACE PROCEDURE CANCELA_EXPEDICAO_ITEM(
    P_FILIAL IN NOTAFISCAL.CDFILIAL%TYPE,
    P_LANCTONF IN NOTAFISCAL.NRLANCTONF%TYPE,
    P_NRSEQITPROD IN ITEMPRODUTO.NRSEQITPROD%TYPE
    )

IS
   V_IDSTATUS          NOTAFISCAL.IDSTATUS%TYPE;
   V_IDSTATUSNFE       NOTAFISCAL.IDSTATUSNFE%TYPE;
   V_NREXPEDICAO       NOTAFISCAL.NREXPEDICAO%TYPE;
   V_NRLANCESTQ        LANCTOESTOQ.NRLANCESTQ%TYPE;
   V_DTENTRSAID        NOTAFISCAL.DTENTRSAID%TYPE;
   V_CDFILIEMIT        NOTAFISCAL.CDFILIEMIT%TYPE;
   V_CDFILIALREPLIC    NOTAFISCAL.CDFILIAL%TYPE;
   V_NRLANCTONFREPLIC  NOTAFISCAL.NRLANCTONF%TYPE;
   V_NRSEQITPRODREPLIC ITEMPRODUTO.NRSEQITPROD%TYPE;
   V_CDPRODUTOREPLIC   PRODUTO.CDPRODUTO%TYPE;
   V_DTENTRSAIDREPLIC  NOTAFISCAL.DTENTRSAID%TYPE;
   V_CDFILIRECEREPLIC  NOTAFISCAL.CDFILIAL%TYPE;
   V_NRLANCESTQREPLIC  LANCTOESTOQ.NRLANCESTQ%TYPE;
   V_DTPROCESSA        PARAMFILIAL.DTPROCESSA%TYPE;
   V_IDUTILDTPROC      PARAMGERAL.IDUTILDTPROC%TYPE;
   V_DTINICIAL         PARAMFILIAL.DTPROCESSA%TYPE;
   V_DTFINAL           PARAMFILIAL.DTPROCESSA%TYPE;
   V_NRSEQ             TRAVACANCITEMEXP.NRSEQITERRO%TYPE;
   V_CDFILIALPED       PEDIDO.CDFILIAL%TYPE;
   V_NRPEDIDO          PEDIDO.NRPEDIDO%TYPE;
   V_NRITEMPED         ITEMPEDIDO.NRITEMPED%TYPE;
   V_IDUTRESELOTE      PARAMPEDIDO.IDUTRESELOTE%TYPE;
   V_COUNT_ITEM        INTEGER;

BEGIN

   DELETE FROM TRAVACANCITEMEXP
   WHERE CDFILIAL = P_FILIAL
     AND NRLANCTONF = P_LANCTONF
     AND NRSEQITPROD = P_NRSEQITPROD;

   BEGIN
      SELECT MAX(N.IDSTATUS), MAX(N.IDSTATUSNFE), MAX(N.NREXPEDICAO), MAX(L.NRLANCESTQ), MAX(N.DTENTRSAID),
             MAX(CDFILIEMIT)
        INTO V_IDSTATUS, V_IDSTATUSNFE, V_NREXPEDICAO, V_NRLANCESTQ, V_DTENTRSAID, V_CDFILIEMIT
        FROM NOTAFISCAL N LEFT JOIN LANCTOESTOQ L
                                 ON N.CDFILIAL = L.CDFILIAL
                                AND N.NRLANCTONF = L.NRLANCTONF
       WHERE N.CDFILIAL = P_FILIAL
         AND N.NRLANCTONF = P_LANCTONF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_IDSTATUS := NULL;
         V_IDSTATUSNFE := NULL;
         V_NREXPEDICAO := NULL;
         V_NRLANCESTQ := NULL;
         V_DTENTRSAID := NULL;
         V_CDFILIEMIT := NULL;
      END;
   END;

   IF ((V_IDSTATUS NOT IN ('E', 'A', 'C', 'L')) AND (V_IDSTATUSNFE NOT IN ('A', 'T', 'D'))) THEN
      BEGIN
         SELECT  NF.CDFILIAL, NF.NRLANCTONF, IP.NRSEQITPROD, IP.CDPRODUTO, NF.DTENTRSAID, NF.CDFILIRECE, LA.NRLANCESTQ
           INTO V_CDFILIALREPLIC, V_NRLANCTONFREPLIC, V_NRSEQITPRODREPLIC, V_CDPRODUTOREPLIC, V_DTENTRSAIDREPLIC, V_CDFILIRECEREPLIC, V_NRLANCESTQREPLIC
           FROM NOTAFISCAL NF JOIN ITEMPRODUTO IP
                                ON NF.CDFILIAL    = IP.CDFILIAL
                               AND NF.NRLANCTONF  = IP.NRLANCTONF
                              LEFT JOIN LANCTOESTOQ LA
                                     ON NF.CDFILIAL = LA.CDFILIAL
                                    AND NF.NRLANCTONF = LA.NRLANCTONF
         WHERE NF.IDDOCREPLIC =  P_FILIAL || P_LANCTONF
           AND IP.NRSEQITPROD = P_NRSEQITPROD;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_CDFILIALREPLIC := '';
            V_NRLANCTONFREPLIC := '';
            V_NRSEQITPRODREPLIC := '';
            V_CDPRODUTOREPLIC := '';
            V_DTENTRSAIDREPLIC := '';
            V_CDFILIRECEREPLIC := '';
            V_NRLANCESTQREPLIC := '';
         END;
      END;
      IF (V_NRLANCTONFREPLIC IS NOT NULL) THEN
         DELETE FROM ITEMPRODUTO
         WHERE CDFILIAL   = V_CDFILIALREPLIC
           AND NRLANCTONF = V_NRLANCTONFREPLIC
           AND NRSEQITPROD = V_NRSEQITPRODREPLIC;

         SELECT NVL(TRUNC(NVL(DTPROCESSA,SYSDATE)), TRUNC(SYSDATE))
           INTO V_DTPROCESSA
           FROM PARAMFILIAL
          WHERE CDFILIAL = V_CDFILIALREPLIC;

         BEGIN
            SELECT NVL(IDUTILDTPROC, 'S')
              INTO V_IDUTILDTPROC
              FROM PARAMGERAL
             WHERE CDPARGERAL = '0001';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_IDUTILDTPROC := 'N';
            END;
         END;
         IF V_IDUTILDTPROC = 'N' THEN
            V_DTPROCESSA := SYSDATE;
         END IF;
         GERA_FECHAMENTO_NFENTRADA(V_CDFILIALREPLIC, V_NRLANCTONFREPLIC, V_DTPROCESSA, V_NRLANCESTQREPLIC);

         DELETE FROM ITLANCTOEST
         WHERE CDFILIAL = V_CDFILIALREPLIC
           AND NRLANCESTQ = V_NRLANCTONFREPLIC
           AND NRSEQUITEM = V_NRSEQITPRODREPLIC;

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

         IF V_DTENTRSAIDREPLIC <= V_DTPROCESSA THEN
            V_DTFINAL := V_DTPROCESSA;
         ELSE
            V_DTFINAL := V_DTENTRSAIDREPLIC;
         END IF;
         ATUALIZA_ESTOQUE(V_CDFILIALREPLIC, V_DTINICIAL, V_DTFINAL, V_CDFILIALREPLIC, V_NRLANCESTQREPLIC);

         /* Atualiza a Itlanctoest para n?o 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 = V_CDFILIALREPLIC||V_NRLANCESTQREPLIC
                                AND CDFILIAL   = V_CDFILIALREPLIC)
         LOOP
            SELECT NVL(MAX(NRSEQITERRO),0)
              INTO V_NRSEQ
              FROM TRAVACANCITEMEXP
             WHERE CDFILIAL = P_FILIAL
               AND NRLANCTONF = P_LANCTONF
               AND NRSEQITPROD = P_NRSEQITPROD;

            V_NRSEQ := LPAD(RTRIM(TO_CHAR(TO_NUMBER(V_NRSEQ) + 1)),10,0);
            INSERT INTO TRAVACANCITEMEXP
            (CDFILIAL, NRLANCTONF, NRSEQITPROD, NRSEQITERRO, IDERRO, CDFILIALIT, NRLANCTONFIT, CDFILIMOVI)
            VALUES
            (P_FILIAL, P_LANCTONF, P_NRSEQITPROD,V_NRSEQ,  'TRAVAESTNFRELAC', V_CDFILIALREPLIC, V_NRLANCTONFREPLIC, V_CDFILIRECEREPLIC);
         END LOOP;

         DELETE MOVILOTE
          WHERE NRMOVILOTE = V_CDFILIALREPLIC||V_NRLANCESTQREPLIC;

      END IF;

      BEGIN
         SELECT CDFILIALPED, NRPEDIDO, NRITEMPED
           INTO V_CDFILIALPED, V_NRPEDIDO, V_NRITEMPED
           FROM ITEMPRODUTO
          WHERE CDFILIAL = P_FILIAL
            AND NRLANCTONF = P_LANCTONF
            AND NRSEQITPROD = P_NRSEQITPROD;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_CDFILIALPED := NULL;
            V_NRPEDIDO := NULL;
            V_NRITEMPED := NULL;
         END;
      END;

      DELETE FROM ITEMPEDIDONF
      WHERE CDFILIAL    = V_CDFILIALPED
        AND NRPEDIDO    = V_NRPEDIDO
        AND NRITEMPED   = V_NRITEMPED
        AND NREXPEDICAO = V_NREXPEDICAO;

      DELETE FROM ITEMPRODUTO
      WHERE CDFILIAL = P_FILIAL
        AND NRLANCTONF = P_LANCTONF
        AND NRSEQITPROD = P_NRSEQITPROD;

      BEGIN
         SELECT NVL(IDUTRESELOTE, 'N')
           INTO V_IDUTRESELOTE
           FROM PARAMPEDIDO
          WHERE CDFILIAL = V_CDFILIALPED;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_IDUTRESELOTE := 'N';
         END;
      END;
      IF (V_IDUTRESELOTE = 'S') THEN
         INSERE_RESERPRODPED(V_CDFILIALPED, V_NRPEDIDO, V_NRITEMPED);
      END IF;

      DELETE FROM CONTARECEBER
      WHERE CDFILIAL = P_FILIAL
        AND NRLANCTONF = P_LANCTONF;

      SELECT NVL(TRUNC(NVL(DTPROCESSA,SYSDATE)), TRUNC(SYSDATE))
        INTO V_DTPROCESSA
        FROM PARAMFILIAL
       WHERE CDFILIAL = V_CDFILIEMIT;

      BEGIN
         SELECT NVL(IDUTILDTPROC, 'S')
           INTO V_IDUTILDTPROC
           FROM PARAMGERAL
          WHERE CDPARGERAL = '0001';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_IDUTILDTPROC := 'N';
         END;
      END;
      IF V_IDUTILDTPROC = 'N' THEN
         V_DTPROCESSA := SYSDATE;
      END IF;


      GERA_NOTAFISCAL_FECHAMENTO(P_FILIAL, P_LANCTONF, V_DTPROCESSA, V_NRLANCESTQ);

      DELETE FROM ITLANCTOEST
      WHERE CDFILIAL = P_FILIAL
        AND NRLANCESTQ = V_NRLANCESTQ
        AND NRSEQUITEM = P_NRSEQITPROD;

      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);

      /* Atualiza a Itlanctoest para n?o 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 = P_FILIAL||V_NRLANCESTQ
                             AND CDFILIAL   = P_FILIAL)
      LOOP
         SELECT NVL(MAX(NRSEQITERRO),0)
           INTO V_NRSEQ
           FROM TRAVACANCITEMEXP
          WHERE CDFILIAL = P_FILIAL
            AND NRLANCTONF = P_LANCTONF
            AND NRSEQITPROD = P_NRSEQITPROD;

         V_NRSEQ := LPAD(RTRIM(TO_CHAR(TO_NUMBER(V_NRSEQ) + 1)),10,0);
         INSERT INTO TRAVACANCITEMEXP
         (CDFILIAL, NRLANCTONF, NRSEQITPROD, NRSEQITERRO, IDERRO, CDFILIALIT, NRLANCTONFIT, CDFILIMOVI)
          VALUES
         (P_FILIAL, P_LANCTONF, P_NRSEQITPROD, V_NRSEQ, 'TRAVAESTNF', P_FILIAL, P_LANCTONF, V_CDFILIEMIT);
      END LOOP;

      DELETE MOVILOTE
      WHERE NRMOVILOTE = P_FILIAL||V_NRLANCESTQ;

      V_COUNT_ITEM := 0;
      SELECT COUNT(1)
        INTO V_COUNT_ITEM
        FROM ITEMPRODUTO
       WHERE CDFILIAL = P_FILIAL
         AND NRLANCTONF = P_LANCTONF;
      IF (V_COUNT_ITEM = 0) THEN
         CANCELA_EXPEDICAO(P_FILIAL, P_LANCTONF);
      END IF;

   ELSE
      SELECT NVL(MAX(NRSEQITERRO),0)
        INTO V_NRSEQ
        FROM TRAVACANCITEMEXP
       WHERE CDFILIAL = P_FILIAL
         AND NRLANCTONF = P_LANCTONF
         AND NRSEQITPROD = P_NRSEQITPROD;

      V_NRSEQ := LPAD(RTRIM(TO_CHAR(TO_NUMBER(V_NRSEQ) + 1)),10,0);
      INSERT INTO TRAVACANCITEMEXP
      (CDFILIAL, NRLANCTONF, NRSEQITPROD, NRSEQITERRO, IDERRO, CDFILIALIT, NRLANCTONFIT, CDFILIMOVI)
      VALUES
      (P_FILIAL, P_LANCTONF, P_NRSEQITPROD,V_NRSEQ,  'TRAVASTATUSNF', '', '', '');
   END IF;

END;