IF EXISTS (SELECT *
           FROM SYSOBJECTS O, SYSUSERS U
           WHERE O.NAME = 'CANCELA_ATIVO_EXP' AND
	              TYPE = 'P' 			          AND
      	        U.UID = O.UID                AND
      	        U.NAME = USER)
DROP PROCEDURE CANCELA_ATIVO_EXP
GO
CREATE PROCEDURE CANCELA_ATIVO_EXP @P_CDFILIAL      VARCHAR(4),
                                   @P_NRLANCTONF    VARCHAR(10)
AS
   DECLARE @V_CTITEM            DOUBLE PRECISION;
   DECLARE @V_CDEMPRESA         VARCHAR(2);
   DECLARE @V_IDSIMULOPERABEM   VARCHAR(1);
   DECLARE @V_NRSEQCAPAOPEBEM   VARCHAR(20);
   DECLARE @V_NRSEQITCAPAOPE    VARCHAR(20);
   DECLARE @V_NRSEQOPERABEM     VARCHAR(20);
   DECLARE @V_CDBEMATIVO        VARCHAR(10);
BEGIN
   DECLARE C_ITEM CURSOR FOR
      SELECT CDEMPRESA, IDSIMULOPERABEM, NRSEQCAPAOPEBEM,
             NRSEQITCAPAOPE, NRSEQOPERABEM, CDBEMATIVO
        FROM ITCAPAOPERABEM
       WHERE CDFILIALNF = @P_CDFILIAL
         AND NRLANCTONF = @P_NRLANCTONF
       ORDER BY CDEMPRESA, IDSIMULOPERABEM, NRSEQCAPAOPEBEM
   OPEN C_ITEM;

   FETCH NEXT FROM C_ITEM INTO @V_CDEMPRESA, @V_IDSIMULOPERABEM, @V_NRSEQCAPAOPEBEM,
                               @V_NRSEQITCAPAOPE, @V_NRSEQOPERABEM, @V_CDBEMATIVO;
   WHILE (@@FETCH_STATUS = 0) BEGIN

      DELETE FROM ITCAPAOPERABEM
       WHERE CDEMPRESA = @V_CDEMPRESA
         AND IDSIMULOPERABEM = @V_IDSIMULOPERABEM
         AND NRSEQCAPAOPEBEM = @V_NRSEQCAPAOPEBEM
         AND NRSEQITCAPAOPE = @V_NRSEQITCAPAOPE;

      DELETE FROM OPERACAOBEM
       WHERE CDEMPRESA = @V_CDEMPRESA
         AND IDSIMULOPERACAO = @V_IDSIMULOPERABEM
         AND CDBEMATIVO = @V_CDBEMATIVO
         AND NRSEQOPERABEM = @V_NRSEQOPERABEM;

      SELECT @V_CTITEM = COUNT(DISTINCT NRSEQCAPAOPEBEM)
        FROM ITCAPAOPERABEM
       WHERE CDEMPRESA = @V_CDEMPRESA
         AND IDSIMULOPERABEM = @V_IDSIMULOPERABEM
         AND NRSEQCAPAOPEBEM = @V_NRSEQCAPAOPEBEM;

      IF @V_CTITEM = 0 BEGIN
         DELETE FROM CAPAOPERABEM
          WHERE CDEMPRESA = @V_CDEMPRESA
            AND IDSIMULOPERABEM = @V_IDSIMULOPERABEM
            AND NRSEQCAPAOPEBEM = @V_NRSEQCAPAOPEBEM;
      END;

      FETCH NEXT FROM C_ITEM INTO @V_CDEMPRESA, @V_IDSIMULOPERABEM, @V_NRSEQCAPAOPEBEM,
                                  @V_NRSEQITCAPAOPE, @V_NRSEQOPERABEM, @V_CDBEMATIVO;
   END;
   CLOSE C_ITEM;
   DEALLOCATE C_ITEM;
END;
GO

-- VERSAO --
BEGIN ATUVEROBJ('CANCELA_ATIVO_EXP','5.30.000'); END;
/