create or replace PROCEDURE EXCLUIR_LIQUIDACAO_PEDIDOITEM
(P_CDFILIAL       IN PEDIDO.CDFILIAL%TYPE,
 P_NRPEDIDO       IN PEDIDO.NRPEDIDO%TYPE,
 P_NRITEMPED      IN ITEMPEDIDO.NRITEMPED%TYPE  DEFAULT NULL,
 P_TIPOEXP        IN EXPRELACITEMPPED.IDTIPOEXP%TYPE,
 P_CDOPERADOR     IN OPERADOR.CDOPERADOR%TYPE,
 P_CDJUSTEXCHIS   IN ITEMPEDIDO.CDJUSTEXCHIS%TYPE,
 P_IDRETORNOLIQ OUT PEDIDO.IDSITPEDIDO%TYPE)
IS
   V_ERRO              INTEGER;
   V_DTPROCESSA        PARAMFILIAL.DTPROCESSA%TYPE;
   V_IDUTILDTPROC      PARAMGERAL.IDUTILDTPROC%TYPE;
   V_DTINICIAL         DATE;
   V_DTFINAL           DATE;
   V_IDEVOLESTLIQMAN   PARAMPEDIDO.IDEVOLESTLIQMAN%TYPE;
   V_IDUTRESELOTE      PARAMPEDIDO.IDUTRESELOTE%TYPE;
   V_TITEM             PARAMPEDIDO.IDEVOLESTLIQMAN%TYPE;
   V_NRITEMPED         ITEMPEDIDO.NRITEMPED%TYPE;
   BOACHOUITEM         INTEGER;
   V_SITUITPEDANT      ITEMPEDIDO.IDSITITEMPED%TYPE;
   V_NRSEQERROITEM     ERROLIQUIDACAO.NRSEQERROITEM%TYPE;
   V_CDFILIMOVI        FILIAL.CDFILIAL%TYPE;
   V_NRLANCESTQ        LANCTOESTOQ.NRLANCESTQ%TYPE;
   V_NRSEQUITEM        ITLANCTOEST.NRSEQUITEM%TYPE;
   V_COUNTITEMEST      INTEGER;
   V_DTANTERIOR        DATE;
   V_COUNT_ITEM        INTEGER;
   V_COUNT_PEND        INTEGER;
   V_COUNT_CONF        INTEGER;
   V_COUNT_APRO        INTEGER;
   V_COUNT_EXP         INTEGER;
   V_COUNT_CANC        INTEGER;
   V_COUNT_LIQU        INTEGER;
   V_COUNT_PAAT        INTEGER;
   V_NOVASITPEDIDO     PEDIDO.IDSITPEDIDO%TYPE;
BEGIN
   V_ERRO := 0;

   DELETE FROM ERROLIQUIDACAO
   WHERE CDFILIAL = P_CDFILIAL
     AND NRPEDIDO = P_NRPEDIDO;

   BEGIN
      SELECT NVL(TRUNC(NVL(DTPROCESSA,SYSDATE)), TRUNC(SYSDATE))
        INTO V_DTPROCESSA
        FROM PARAMFILIAL
       WHERE CDFILIAL = P_CDFILIAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_DTPROCESSA   := SYSDATE;
      END;
   END;

   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;

   IF SYSDATE < V_DTPROCESSA THEN
      V_DTINICIAL := SYSDATE;
   ELSE
      V_DTINICIAL := V_DTPROCESSA;
   END IF;
   IF SYSDATE > V_DTPROCESSA THEN
      V_DTFINAL := SYSDATE;
   ELSE
      V_DTFINAL := V_DTPROCESSA;
   END IF;

   BEGIN
      SELECT IDEVOLESTLIQMAN, IDUTRESELOTE
        INTO V_IDEVOLESTLIQMAN, V_IDUTRESELOTE
        FROM PARAMPEDIDO
       WHERE CDFILIAL = P_CDFILIAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_IDEVOLESTLIQMAN := 'N';
         V_IDUTRESELOTE := 'N';
      END;
   END;

   IF P_TIPOEXP = 'PEDIDO' THEN
      V_TITEM := 'T';
      V_NRITEMPED := NULL;
   ELSE
      V_NRITEMPED := P_NRITEMPED;
      V_TITEM := '';
   END IF;

   BOACHOUITEM := 0;
   FOR C_ITEM IN (SELECT I.CDFILIAL, I.NRPEDIDO, I.NRITEMPED, I.CDPRODUTO, P.CDPRODESTO, P.VRFATOCONV,
                         I.QTITEMPED, I.QTEXPITEMPED,  ((I.QTITEMPED - I.QTEXPITEMPED)*VRFATOCONV) AS QTITEMPEDEXP,
                         I.IDSITITEMPED
                    FROM ITEMPEDIDO I JOIN PRODUTO P
                                        ON I.CDPRODUTO = P.CDPRODUTO
                   WHERE I.CDFILIAL = P_CDFILIAL
                     AND I.NRPEDIDO = P_NRPEDIDO
                     AND ((I.NRITEMPED = V_NRITEMPED) OR (V_TITEM = 'T'))
                     AND (I.IDSITITEMPED = '6'))
   LOOP
      BOACHOUITEM := 1;
      IF (C_ITEM.IDSITITEMPED = '6') THEN
         BEGIN
            SELECT MAX(IDSITANTHIST)
              INTO V_SITUITPEDANT
              FROM HISITEMPED
             WHERE CDFILIAL = C_ITEM.CDFILIAL
               AND NRPEDIDO = C_ITEM.NRPEDIDO
               AND NRITEMPED = C_ITEM.NRITEMPED
               AND IDSITATUHIST = '6'
               AND DTHRHIST = (SELECT DTHRHIST
                                 FROM (SELECT DTHRHIST
                                         FROM HISITEMPED
                                        WHERE CDFILIAL = C_ITEM.CDFILIAL
                                          AND NRPEDIDO = C_ITEM.NRPEDIDO
                                          AND NRITEMPED = C_ITEM.NRITEMPED
                                          AND IDSITATUHIST = '6'
                                        ORDER BY 1 DESC)
                               WHERE ROWNUM = 1);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_SITUITPEDANT := NULL;
         END;
         IF (V_SITUITPEDANT IS NULL) THEN
            V_ERRO := 1;
            BEGIN
               SELECT NVL(MAX(NRSEQERROITEM)+1, '1')
                 INTO V_NRSEQERROITEM
                 FROM ERROLIQUIDACAO
                WHERE CDFILIAL   = C_ITEM.CDFILIAL
                  AND NRPEDIDO = C_ITEM.NRPEDIDO;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_NRSEQERROITEM := '0000000001';
            END;

            INSERT INTO ERROLIQUIDACAO
            (CDFILIAL, NRPEDIDO, NRITEMPED, NRSEQERROITEM, CDPRODUTO, DSERRO)
            VALUES
            (C_ITEM.CDFILIAL, C_ITEM.NRPEDIDO, C_ITEM.NRITEMPED, V_NRSEQERROITEM, C_ITEM.CDPRODUTO, 'SIT_ANTITEM');
         ELSE

            UPDATE ITEMPEDIDO
               SET IDSITITEMPED = V_SITUITPEDANT,
                   CDOPERADOR   = P_CDOPERADOR,
                   CDJUSTEXCHIS = P_CDJUSTEXCHIS
              WHERE CDFILIAL = C_ITEM.CDFILIAL
                AND NRPEDIDO = C_ITEM.NRPEDIDO
                AND NRITEMPED = C_ITEM.NRITEMPED;

            FOR C_ESTOQ IN (SELECT CDFILIAL, NRLANCESTQ, NRSEQUITEM
                              FROM ITLANCTOEST
                             WHERE DSLANCESTIT = 'Liquida' || chr(231) || chr(227) || 'o Manual'
                               AND CDFILIAL = C_ITEM.CDFILIAL
                               AND NRPEDIDO = C_ITEM.NRPEDIDO
                              AND NRITEMPED = C_ITEM.NRITEMPED)
            LOOP
               DELETE FROM LIQMANUALCUSTOM
               WHERE CDFILIAL = C_ITEM.CDFILIAL
                 AND NRPEDIDO = C_ITEM.NRPEDIDO
                 AND NRITEMPED = C_ITEM.NRITEMPED;

               DELETE FROM ITLANCTOEST
               WHERE CDFILIAL = C_ESTOQ.CDFILIAL
                 AND NRLANCESTQ = C_ESTOQ.NRLANCESTQ
                 AND NRSEQUITEM = C_ESTOQ.NRSEQUITEM;

               V_COUNTITEMEST := 0;
               BEGIN
                  SELECT COUNT(1)
                    INTO V_COUNTITEMEST
                    FROM ITLANCTOEST
                   WHERE CDFILIAL = C_ESTOQ.CDFILIAL
                     AND NRLANCESTQ = C_ESTOQ.NRLANCESTQ;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN BEGIN
                     V_COUNTITEMEST := 0;
                  END;
               END;
               IF (V_COUNTITEMEST = 0) THEN
                  DELETE FROM ITLANCTOEST
                  WHERE CDFILIAL = C_ESTOQ.CDFILIAL
                     AND NRLANCESTQ = C_ESTOQ.NRLANCESTQ;
               END IF;
               BEGIN
                  SELECT DTLANCESTQ
                    INTO V_DTANTERIOR
                    FROM LANCTOESTOQ
                   WHERE CDFILIAL = C_ESTOQ.CDFILIAL
                     AND NRLANCESTQ = C_ESTOQ.NRLANCESTQ;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN BEGIN
                     V_DTANTERIOR  := NULL;
                  END;
               END;
               IF (V_DTANTERIOR IS NOT NULL) THEN
                  IF(V_DTANTERIOR < V_DTINICIAL) THEN
                     V_DTINICIAL := V_DTANTERIOR;
                  END IF;
                  IF(V_DTANTERIOR > V_DTFINAL) THEN
                     V_DTFINAL := V_DTANTERIOR;
                  END IF;
               END IF;
               ATUALIZA_ESTOQUE(P_CDFILIAL, V_DTINICIAL, V_DTFINAL, C_ESTOQ.CDFILIAL, C_ESTOQ.NRLANCESTQ);
               FOR C_MOVILOTE IN (SELECT CDPRODUTO
                                    FROM MOVILOTE
                                   WHERE NRMOVILOTE  = C_ESTOQ.CDFILIAL||C_ESTOQ.NRLANCESTQ
                                     AND CDFILIAL   = P_CDFILIAL)
               LOOP
                  V_ERRO := 1;
                  FOR C_ITEMSESTOQ IN (SELECT DISTINCT I.CDFILIAL, I.NRPEDIDO, I.NRITEMPED, I.CDPRODUTO, P.CDARVPROD, P.NMPRODUTO
                                         FROM ITEMPEDIDO I JOIN PRODUTO P
                                                             ON I.CDPRODUTO = P.CDPRODUTO
                                        WHERE I.CDFILIAL = P_CDFILIAL
                                          AND I.NRPEDIDO = P_NRPEDIDO
                                          AND P.CDPRODESTO = C_MOVILOTE.CDPRODUTO)
                  LOOP
                     BEGIN
                        SELECT NVL(MAX(NRSEQERROITEM)+1, '1')
                          INTO V_NRSEQERROITEM
                          FROM ERROLIQUIDACAO
                         WHERE CDFILIAL   = P_CDFILIAL
                           AND NRPEDIDO = C_ITEMSESTOQ.NRPEDIDO;
                     EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                           V_NRSEQERROITEM := '0000000001';
                     END;
                     INSERT INTO ERROLIQUIDACAO
                     (CDFILIAL, NRPEDIDO, NRITEMPED, NRSEQERROITEM, CDPRODUTO, DSERRO)
                     VALUES
                     (C_ITEMSESTOQ.CDFILIAL, C_ITEMSESTOQ.NRPEDIDO, C_ITEMSESTOQ.NRITEMPED, V_NRSEQERROITEM, C_ITEMSESTOQ.CDPRODUTO, 'IRREGESTOQ');
                  END LOOP;
               END LOOP;
               DELETE MOVILOTE
               WHERE NRMOVILOTE  = C_ESTOQ.CDFILIAL||C_ESTOQ.NRLANCESTQ;
               IF (V_ERRO = 0) THEN
                  IF (V_IDUTRESELOTE = 'S') THEN
                     INSERE_RESERPRODPED(C_ITEM.CDFILIAL, C_ITEM.NRPEDIDO, C_ITEM.NRITEMPED,'N');
                  END IF;
               END IF;
            END LOOP;
          END IF;
      ELSE
         V_ERRO := 1;
         BEGIN
            SELECT NVL(MAX(NRSEQERROITEM)+1, '1')
              INTO V_NRSEQERROITEM
              FROM ERROLIQUIDACAO
             WHERE CDFILIAL   = C_ITEM.CDFILIAL
               AND NRPEDIDO = C_ITEM.NRPEDIDO;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NRSEQERROITEM := '0000000001';
         END;

         INSERT INTO ERROLIQUIDACAO
         (CDFILIAL, NRPEDIDO, NRITEMPED, NRSEQERROITEM, CDPRODUTO, DSERRO)
         VALUES
         (C_ITEM.CDFILIAL, C_ITEM.NRPEDIDO, C_ITEM.NRITEMPED, V_NRSEQERROITEM, C_ITEM.CDPRODUTO, 'SIT_ITEM');
      END IF;
   END LOOP;
   IF BOACHOUITEM > 0 THEN
      IF (V_ERRO = 0) THEN
         BEGIN
            SELECT COUNT(1)
              INTO V_COUNT_ITEM
              FROM ITEMPEDIDO
             WHERE CDFILIAL = P_CDFILIAL
               AND NRPEDIDO = P_NRPEDIDO;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_COUNT_ITEM := 0;
            END;
         END;
         BEGIN
            SELECT COUNT(1)
              INTO V_COUNT_PEND
              FROM ITEMPEDIDO
             WHERE CDFILIAL = P_CDFILIAL
               AND NRPEDIDO = P_NRPEDIDO
               AND IDSITITEMPED = '1';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_COUNT_PEND := 0;
            END;
         END;
         BEGIN
            SELECT COUNT(1)
              INTO V_COUNT_CONF
              FROM ITEMPEDIDO
             WHERE CDFILIAL = P_CDFILIAL
               AND NRPEDIDO = P_NRPEDIDO
               AND IDSITITEMPED = '2';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_COUNT_CONF := 0;
            END;
         END;
         BEGIN
            SELECT COUNT(1)
              INTO V_COUNT_APRO
              FROM ITEMPEDIDO
             WHERE CDFILIAL = P_CDFILIAL
               AND NRPEDIDO = P_NRPEDIDO
               AND IDSITITEMPED = '3';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_COUNT_APRO := 0;
            END;
         END;
         BEGIN
            SELECT COUNT(1)
              INTO V_COUNT_EXP
              FROM ITEMPEDIDO
             WHERE CDFILIAL = P_CDFILIAL
               AND NRPEDIDO = P_NRPEDIDO
               AND IDSITITEMPED = '4';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_COUNT_EXP := 0;
            END;
         END;
         BEGIN
            SELECT COUNT(1)
              INTO V_COUNT_CANC
              FROM ITEMPEDIDO
             WHERE CDFILIAL = P_CDFILIAL
               AND NRPEDIDO = P_NRPEDIDO
               AND IDSITITEMPED = '5';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_COUNT_CANC := 0;
            END;
         END;
         BEGIN
            SELECT COUNT(1)
              INTO V_COUNT_LIQU
              FROM ITEMPEDIDO
             WHERE CDFILIAL = P_CDFILIAL
               AND NRPEDIDO = P_NRPEDIDO
               AND IDSITITEMPED = '6';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_COUNT_LIQU := 0;
            END;
         END;
         BEGIN
            SELECT COUNT(1)
              INTO V_COUNT_PAAT
              FROM ITEMPEDIDO
             WHERE CDFILIAL = P_CDFILIAL
               AND NRPEDIDO = P_NRPEDIDO
               AND IDSITITEMPED = '7';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_COUNT_PAAT := 0;
            END;
         END;
         IF (V_COUNT_ITEM = (V_COUNT_PEND+V_COUNT_CANC)) THEN
            V_NOVASITPEDIDO := '1';
         ELSE
            IF (V_COUNT_ITEM = (V_COUNT_EXP+V_COUNT_CANC)) THEN
               V_NOVASITPEDIDO := '3';
            ELSE
               IF (V_COUNT_ITEM = V_COUNT_CANC) THEN
                  V_NOVASITPEDIDO := '4';
               ELSE
                  IF (V_COUNT_ITEM = (V_COUNT_LIQU+V_COUNT_CANC)) THEN
                     V_NOVASITPEDIDO := '5';
                  ELSE
                     IF ((V_COUNT_EXP > 0) OR (V_COUNT_PAAT > 0)) THEN
                        V_NOVASITPEDIDO := '6';
                     ELSE
                        IF (V_COUNT_LIQU > 0 ) THEN
                           V_NOVASITPEDIDO := '6';
                        ELSE
                           V_NOVASITPEDIDO := '2';
                        END IF;
                     END IF;
                  END IF;
               END IF;
            END IF;
         END IF;
         UPDATE PEDIDO
            SET IDSITPEDIDO = V_NOVASITPEDIDO
          WHERE CDFILIAL = P_CDFILIAL
            AND NRPEDIDO = P_NRPEDIDO;
         P_IDRETORNOLIQ := '0';
      ELSE
         P_IDRETORNOLIQ := '1';
      END IF;
   ELSE
      V_ERRO := 1;
      BEGIN
         SELECT NVL(MAX(NRSEQERROITEM)+1, '1')
           INTO V_NRSEQERROITEM
           FROM ERROLIQUIDACAO
          WHERE CDFILIAL   = P_CDFILIAL
            AND NRPEDIDO = P_NRPEDIDO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NRSEQERROITEM := '0000000001';
      END;

      INSERT INTO ERROLIQUIDACAO
      (CDFILIAL, NRPEDIDO, NRITEMPED, NRSEQERROITEM, CDPRODUTO, DSERRO)
      VALUES
      (P_CDFILIAL, P_NRPEDIDO, NULL, V_NRSEQERROITEM, NULL, 'SIT_ITEM');

      P_IDRETORNOLIQ := '1';
   END IF;
END;
/

