IF EXISTS(SELECT name FROM sysobjects
          WHERE name = 'EXCLUI_LANCCONTABIL' AND type = 'P')
  DROP PROCEDURE EXCLUI_LANCCONTABIL
  GO

CREATE PROCEDURE EXCLUI_LANCCONTABIL @P_TIPOMOVI VARCHAR(2), @P_SEQUENCIAL VARCHAR(10)
AS
DECLARE @CDEMPRESA VARCHAR(2);
DECLARE @CDLOTE VARCHAR(20);
DECLARE @NRLANCLOTE VARCHAR(6);
DECLARE @CDFILIAL VARCHAR(4);
DECLARE @NRLANCTONF VARCHAR(10);
DECLARE @NRLANCESTQ VARCHAR(10);
DECLARE @NRORDPROD VARCHAR(10);
DECLARE @NRSUBORDPROD VARCHAR(1);
DECLARE @CDETAPAPROD VARCHAR(10);
DECLARE @NRAPRCUSTOP VARCHAR(10);
DECLARE @CDREDUCONT VARCHAR(3);
DECLARE @NRMOVBANC VARCHAR(10);
DECLARE @NRBAIXAPAG VARCHAR(10);
DECLARE @NRBAIXAREC VARCHAR(10);
DECLARE @NRTRANBANC VARCHAR(10);

BEGIN
   IF @P_TIPOMOVI IN ('00','01','06','07')
   BEGIN
      DECLARE C_CHAVE CURSOR FOR SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE,
                                        R.CDFILIAL, R.NRLANCTONF
                                   FROM RELACNFLANCLOTE R, LISTA L
                                  WHERE L.NRLISTA    = @P_SEQUENCIAL
                                    AND R.CDFILIAL   = SUBSTRING(L.CDLISTA,1,4)
                                    AND R.NRLANCTONF = SUBSTRING(L.CDLISTA,5,10);
      OPEN C_CHAVE;
      FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDFILIAL, @NRLANCTONF;
      
      WHILE (@@FETCH_STATUS = 0)
      BEGIN   
         UPDATE NOTAFISCAL
            SET IDSTATUSCTBNF = 'P'
          WHERE (@CDFILIAL   = CDFILIAL)
            AND (@NRLANCTONF = NRLANCTONF);  
         
         DELETE FROM RELACNFLANCLOTE
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);
         FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDFILIAL, @NRLANCTONF;
      END;
      CLOSE C_CHAVE;
      DEALLOCATE C_CHAVE;
   END
   ELSE
   IF @P_TIPOMOVI IN ('02','04','05')
   BEGIN
      DECLARE C_CHAVE CURSOR FOR SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE,
                                        R.CDFILIAL, R.NRLANCESTQ
                                   FROM RELALESTLANCLOT R, LISTA L
                                  WHERE L.NRLISTA    = @P_SEQUENCIAL
                                    AND R.CDFILIAL   = SUBSTRING(L.CDLISTA,1,4)
                                    AND R.NRLANCESTQ = SUBSTRING(L.CDLISTA,5,10);
      OPEN C_CHAVE;
      
      FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDFILIAL, @NRLANCESTQ;           
      
      WHILE (@@FETCH_STATUS = 0)
      BEGIN
            
         UPDATE LANCTOESTOQ 
            SET IDSTATUSCTBEST = 'P'
          WHERE (@CDFILIAL   = CDFILIAL)
            AND (@NRLANCESTQ = NRLANCESTQ);
          
         DELETE FROM RELALESTLANCLOT
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);

         FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDFILIAL, @NRLANCESTQ;
      END;
      CLOSE C_CHAVE;
      DEALLOCATE C_CHAVE;
   END                                                                  
   ELSE
   IF @P_TIPOMOVI = '03'
   BEGIN
      DECLARE C_CHAVE CURSOR FOR 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     = SUBSTRING(L.CDLISTA,1,4)
                                      AND R.NRORDPROD    = SUBSTRING(L.CDLISTA,5,10)
                                      AND R.NRSUBORDPROD = SUBSTRING(L.CDLISTA,15,1)
                                      AND R.CDETAPAPROD  = SUBSTRING(L.CDLISTA,16,10)
                                      AND R.NRAPRCUSTOP  = SUBSTRING(L.CDLISTA,26,10);
      OPEN C_CHAVE;
      
      FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDFILIAL, @NRORDPROD, @NRSUBORDPROD,
                                   @CDETAPAPROD, @NRAPRCUSTOP;
              
      WHILE (@@FETCH_STATUS = 0)
      BEGIN 
               
         UPDATE APRCUSTOP  
            SET IDSTATUSCTBOP = 'P'
          WHERE (@CDFILIAL     = CDFILIAL)
            AND (@NRORDPROD    = NRORDPROD)
            AND (@NRSUBORDPROD = NRSUBORDPROD
            AND (@CDETAPAPROD  = CDETAPAPROD)
            AND (@NRAPRCUSTOP  = NRAPRCUSTOP));
          
         DELETE FROM RELACAPRLANCLOT
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);
               
         FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDFILIAL, @NRORDPROD, @NRSUBORDPROD,
                                      @CDETAPAPROD, @NRAPRCUSTOP;      
 
      END;
      CLOSE C_CHAVE;
      DEALLOCATE C_CHAVE;
   END                   
   ELSE
   IF @P_TIPOMOVI = '08'
   BEGIN

      DECLARE C_CHAVE CURSOR FOR SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE,
                                        R.CDREDUCONT, R.NRMOVBANC
                                   FROM RELAMBANCLANLOT R, LISTA L
                                  WHERE L.NRLISTA    = @P_SEQUENCIAL
                                    AND R.CDREDUCONT = SUBSTRING(L.CDLISTA,1,3)
                                    AND R.NRMOVBANC  = SUBSTRING(L.CDLISTA,4,10);
      OPEN C_CHAVE;
     
      FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDREDUCONT, @NRMOVBANC;          
      
      WHILE (@@FETCH_STATUS = 0)
      BEGIN 
           
         UPDATE MOVBANCARIO  
            SET IDSTATUSCTBBANC = 'P'
          WHERE (@CDREDUCONT = CDREDUCONT)
            AND (@NRMOVBANC  = NRMOVBANC);
          
         DELETE FROM RELAMBANCLANLOT
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);
         FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDREDUCONT, @NRMOVBANC;
      END;
      CLOSE C_CHAVE;
      DEALLOCATE C_CHAVE;
   END                   
   ELSE
   IF @P_TIPOMOVI = '09'
   BEGIN

      DECLARE C_CHAVE CURSOR FOR SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE, 
                                        R.NRTRANBANC
                                   FROM RELACTRANSFLANC R, LISTA L
                                  WHERE L.NRLISTA    = @P_SEQUENCIAL
                                    AND R.CDEMPRESA  = SUBSTRING(L.CDLISTA,1,2)
                                    AND R.NRTRANBANC = SUBSTRING(L.CDLISTA,3,10);
      OPEN C_CHAVE;
     
      FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @NRTRANBANC;          
      
      WHILE (@@FETCH_STATUS = 0)
      BEGIN 
           
         UPDATE TRANSFBANC  
            SET IDSTATUSCTBL = 'P'
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@NRTRANBANC = NRTRANBANC);
          
         DELETE FROM RELACTRANSFLANC
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);
         FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDEMPRESA;
      END;
      CLOSE C_CHAVE;
      DEALLOCATE C_CHAVE;
   END                     
   ELSE
   IF @P_TIPOMOVI = ('10')
   BEGIN
      DECLARE C_CHAVE CURSOR FOR SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE,
                                        R.CDFILIAL, R.NRBAIXAPAG
                                   FROM RELACBPAGLANLOT R, LISTA L
                                  WHERE L.NRLISTA    = @P_SEQUENCIAL
                                    AND R.CDFILIAL   = SUBSTRING(L.CDLISTA,1,4)
                                    AND R.NRBAIXAPAG = SUBSTRING(L.CDLISTA,5,10);
      OPEN C_CHAVE;
      
      FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDFILIAL, @NRBAIXAPAG;

      WHILE (@@FETCH_STATUS = 0)
      BEGIN          
         UPDATE BAIXAPAGA  
            SET IDSTATUSCTBPAG = 'P'
          WHERE (@CDFILIAL   = CDFILIAL)
            AND (@NRBAIXAPAG = NRBAIXAPAG);
          
         DELETE FROM RELACBPAGLANLOT
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);

         FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDFILIAL, @NRBAIXAPAG;
      END;
      CLOSE C_CHAVE;
      DEALLOCATE C_CHAVE;
   END                    
   ELSE
   IF @P_TIPOMOVI = '11'
   BEGIN
      DECLARE C_CHAVE CURSOR FOR SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE,
                                        R.CDFILIAL, R.NRBAIXAREC
                                   FROM RELACBRECLANLOT R, LISTA L
                                  WHERE L.NRLISTA    = @P_SEQUENCIAL
                                    AND R.CDFILIAL   = SUBSTRING(L.CDLISTA,1,4)
                                    AND R.NRBAIXAREC = SUBSTRING(L.CDLISTA,5,10);
      OPEN C_CHAVE;
   
      FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDFILIAL, @NRBAIXAREC;
      
      WHILE (@@FETCH_STATUS = 0)
      BEGIN
         
         UPDATE BAIXARECEBIDA  
            SET IDSTATUSCTBREC = 'P'
          WHERE (@CDFILIAL   = CDFILIAL)
            AND (@NRBAIXAREC = NRBAIXAREC);
          
         DELETE FROM RELACBRECLANLOT
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);
            
         DELETE FROM LANCLOTE
          WHERE (@CDEMPRESA  = CDEMPRESA)
            AND (@CDLOTE     = CDLOTE)
            AND (@NRLANCLOTE = NRLANCLOTE);

         FETCH NEXT FROM C_CHAVE INTO @CDEMPRESA, @CDLOTE, @NRLANCLOTE, @CDFILIAL, @NRBAIXAREC;
      END;
      CLOSE C_CHAVE;
      DEALLOCATE C_CHAVE;    
   END;
END;
GO