create or replace
PROCEDURE EXCLUI_LANCCONTABIL(
   P_TIPOMOVI IN VARCHAR2,
   P_SEQUENCIAL IN VARCHAR2)
IS
BEGIN
   IF P_TIPOMOVI IN ('00','01','06','07','12') THEN
      FOR C_CHAVE IN(SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE,
                            R.CDFILIAL, R.NRLANCTONF
                       FROM RELACNFLANCLOTE R
                      WHERE R.CDFILIAL   = SUBSTR(P_SEQUENCIAL,1,4)
                        AND R.NRLANCTONF = SUBSTR(P_SEQUENCIAL,5,10)) LOOP

         UPDATE NOTAFISCAL
            SET IDSTATUSCTBNF = 'P'
          WHERE (C_CHAVE.CDFILIAL   = CDFILIAL)
            AND (C_CHAVE.NRLANCTONF = NRLANCTONF);

         DELETE FROM RELACNFLANCLOTE
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);

         FOR C_LANCCONT IN(
           SELECT NRLANCDEFI, NRSEQUDEFI
             FROM LANCLOTE
            WHERE CDEMPRESA   = C_CHAVE.CDEMPRESA
              AND CDLOTE      = C_CHAVE.CDLOTE
              AND NRLANCLOTE  = C_CHAVE.NRLANCLOTE) LOOP

           DELETE FROM CONTCTBLMAPA C
            WHERE CDEMPRESA   = C_CHAVE.CDEMPRESA
              AND NRLANCCONT  = C_LANCCONT.NRLANCDEFI
              AND NRSEQUMAPAPU NOT IN (SELECT NRSEQUMAPAPU FROM MAPAAPURAC M
                                        WHERE C.NRSEQUMAPAPU = M.NRSEQUMAPAPU
                                          AND M.IDBLOQMAPAPU = 'S');

           DELETE FROM LANCCONT
            WHERE (CDEMPRESA  = C_CHAVE.CDEMPRESA)
              AND (NRLANCCONT = C_LANCCONT.NRLANCDEFI)
              AND (NRSEQLANC  = C_LANCCONT.NRSEQUDEFI);
         END LOOP;

         DELETE FROM ERROLANCLOTE
          WHERE CDEMPRESA  = C_CHAVE.CDEMPRESA
            AND CDLOTE      = C_CHAVE.CDLOTE
            AND NRLANCLOTE  = C_CHAVE.NRLANCLOTE;

         DELETE FROM LANCLOTE
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
      END LOOP;
   ELSIF P_TIPOMOVI IN ('02','04','05') THEN
      FOR C_CHAVE IN(SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE,
                            R.CDFILIAL, R.NRLANCESTQ
                       FROM RELALESTLANCLOT R, LISTA L
                      WHERE L.NRLISTA    = P_SEQUENCIAL
                        AND R.CDFILIAL   = SUBSTR(L.CDLISTA,1,4)
                        AND R.NRLANCESTQ = SUBSTR(L.CDLISTA,5,10)) LOOP          
          
         UPDATE LANCTOESTOQ 
            SET IDSTATUSCTBEST = 'P'
          WHERE (C_CHAVE.CDFILIAL   = CDFILIAL)
            AND (C_CHAVE.NRLANCESTQ = NRLANCESTQ);
          
         DELETE FROM RELALESTLANCLOT
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
      END LOOP;                                                                  
   ELSIF P_TIPOMOVI = ('03') THEN                                               
      FOR C_CHAVE IN(SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE,
                            R.CDFILIAL, R.NRORDPROD, R.NRSUBORDPROD, R.CDETAPAPROD, R.NRAPRCUSTOP
                       FROM RELACAPRLANCLOT R, LISTA L
                      WHERE L.NRLISTA      = P_SEQUENCIAL
                         AND R.CDFILIAL = SUBSTR(L.CDLISTA,1,4)
                        AND R.NRORDPROD = SUBSTR(L.CDLISTA,5,10)
                        AND R.NRSUBORDPROD = SUBSTR(L.CDLISTA,15,1)
                        AND R.CDETAPAPROD = SUBSTR(L.CDLISTA,16,10)
                        AND R.NRAPRCUSTOP = SUBSTR(L.CDLISTA,26,10)) LOOP
          
         UPDATE APRCUSTOP  
            SET IDSTATUSCTBOP = 'P'
          WHERE (C_CHAVE.CDFILIAL     = CDFILIAL)
            AND (C_CHAVE.NRORDPROD    = NRORDPROD)
            AND (C_CHAVE.NRSUBORDPROD = NRSUBORDPROD
            AND (C_CHAVE.CDETAPAPROD  = CDETAPAPROD)
            AND (C_CHAVE.NRAPRCUSTOP  = NRAPRCUSTOP));
          
         DELETE FROM RELACAPRLANCLOT
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
      END LOOP;                   
   ELSIF P_TIPOMOVI = '08' THEN
      FOR C_CHAVE IN(SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE,
                            R.CDREDUCONT, R.NRMOVBANC
                       FROM RELAMBANCLANLOT R, LISTA L
                      WHERE L.NRLISTA    = P_SEQUENCIAL
                        AND R.CDREDUCONT = SUBSTR(L.CDLISTA,1,3)
                        AND R.NRMOVBANC  = SUBSTR(L.CDLISTA,4,10)) LOOP          
          
         UPDATE MOVBANCARIO  
            SET IDSTATUSCTBBANC = 'P'
          WHERE (C_CHAVE.CDREDUCONT = CDREDUCONT)
            AND (C_CHAVE.NRMOVBANC  = NRMOVBANC);
          
         DELETE FROM RELAMBANCLANLOT
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
      END LOOP;                   
   ELSIF P_TIPOMOVI = '09' THEN
      FOR C_CHAVE IN(SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE, R.NRTRANBANC
                       FROM RELACTRANSFLANC R, LISTA L
                      WHERE L.NRLISTA    = P_SEQUENCIAL
                        AND R.CDEMPRESA = SUBSTR(L.CDLISTA,1,2)
                        AND R.NRTRANBANC  = SUBSTR(L.CDLISTA,3,10)) LOOP          
          
         UPDATE TRANSFBANC  
            SET IDSTATUSCTBL = 'P'
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.NRTRANBANC = NRTRANBANC);
          
         DELETE FROM RELACTRANSFLANC
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
      END LOOP;                    
   ELSIF P_TIPOMOVI = ('10') THEN
      FOR C_CHAVE IN(SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE,
                            R.CDFILIAL, R.NRBAIXAPAG
                       FROM RELACBPAGLANLOT R, LISTA L
                      WHERE L.NRLISTA    = P_SEQUENCIAL
                        AND R.CDFILIAL   = SUBSTR(L.CDLISTA,1,4)
                        AND R.NRBAIXAPAG = SUBSTR(L.CDLISTA,5,10)) LOOP          
          
         UPDATE BAIXAPAGA  
            SET IDSTATUSCTBPAG = 'P'
          WHERE (C_CHAVE.CDFILIAL   = CDFILIAL)
            AND (C_CHAVE.NRBAIXAPAG = NRBAIXAPAG);
          
         DELETE FROM RELACBPAGLANLOT
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
      END LOOP;                    
   ELSIF P_TIPOMOVI = ('11') THEN
      FOR C_CHAVE IN(SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE,
                            R.CDFILIAL, R.NRBAIXAREC
                       FROM RELACBRECLANLOT R, LISTA L
                      WHERE L.NRLISTA    = P_SEQUENCIAL
                        AND R.CDFILIAL   = SUBSTR(L.CDLISTA,1,4)
                        AND R.NRBAIXAREC = SUBSTR(L.CDLISTA,5,10)) LOOP          
          
         UPDATE BAIXARECEBIDA
            SET IDSTATUSCTBREC = 'P'
          WHERE (C_CHAVE.CDFILIAL   = CDFILIAL)
            AND (C_CHAVE.NRBAIXAREC = NRBAIXAREC);

         DELETE FROM RELACBRECLANLOT
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (C_CHAVE.CDEMPRESA  = CDEMPRESA)
            AND (C_CHAVE.CDLOTE     = CDLOTE)
            AND (C_CHAVE.NRLANCLOTE = NRLANCLOTE);
      END LOOP;
   END IF;
END;
/