DROP PROCEDURE TMP_OP_DEPOSITSITEDETDelProc
GO
CREATE PROCEDURE TMP_OP_DEPOSITSITEDETDelProc
(
    @CORRNBR		   INT,	
    @SITEID                UD_IDSITE,
    @DATEPROCE		   UD_FECHA,
    @SEQUENCE		   INT
)
AS
BEGIN
    BEGIN TRAN

    DELETE 	
    FROM	TMP_OP_DEPOSITSITEDET
    WHERE CORRNBR=@CORRNBR AND SITEID=@SITEID AND DATEPROCE=@DATEPROCE AND SEQUENCE=@SEQUENCE

    IF (@@error!=0 AND @@ROWCOUNT=0)
    BEGIN
        RAISERROR  20000 'Error Eliminando en TMP_OP_DEPOSITSITEDET '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    DELETE 	
    FROM	TMP_OP_NBRDOCSUMMARY
    WHERE CORRNBR=@CORRNBR AND SITEID=@SITEID AND DATEPROCE=@DATEPROCE AND SEQUENCE=@SEQUENCE

    IF (@@error!=0 AND @@ROWCOUNT=0)
    BEGIN
        RAISERROR  20000 'Error Eliminando en TMP_OP_NBRDOCSUMMARY '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    UPDATE TMP_OP_NBRDOCSUMMARY 
    SET SEQUENCE=SEQUENCE-1 
    WHERE CORRNBR=@CORRNBR AND SITEID=@SITEID AND DATEPROCE=@DATEPROCE AND SEQUENCE>@SEQUENCE

    IF (@@error!=0 AND @@ROWCOUNT=0)
    BEGIN
        RAISERROR  20000 'Error Actualizando en TMP_OP_NBRDOCSUMMARY '
        ROLLBACK TRAN
        RETURN(1)
    
    END
	

	DECLARE @CONTADOR INT
	DECLARE @SITEID1	UD_IDSITE
	DECLARE @DATEPROCE1	UD_FECHA
	DECLARE @SEQUENCE1	INT

    	DECLARE TMP_CURSOR CURSOR
    	FOR SELECT SITEID,DATEPROCE,SEQUENCE
        FROM TMP_OP_DEPOSITSITEDET WHERE CORRNBR=@CORRNBR AND SITEID=@SITEID

	SET @CONTADOR=0
  	OPEN TMP_CURSOR
    	FETCH NEXT FROM TMP_CURSOR INTO @SITEID1,@DATEPROCE1,@SEQUENCE1
  

	WHILE @@FETCH_STATUS=0
        BEGIN    
		SET @CONTADOR=@CONTADOR+1
 
		UPDATE 	TMP_OP_DEPOSITSITEDET
		SET	SEQUENCE=@CONTADOR
		WHERE	CORRNBR=@CORRNBR AND SITEID=@SITEID1 AND DATEPROCE=@DATEPROCE1 AND SEQUENCE=@SEQUENCE1


     
   	       IF (@@ERROR!=0 OR @@ROWCOUNT=0)
        	  BEGIN       
            		CLOSE TMP_CURSOR
            		DEALLOCATE TMP_CURSOR
            		RETURN(1)  
               END         
             

		FETCH NEXT FROM TMP_CURSOR INTO @SITEID1,@DATEPROCE1,@SEQUENCE1
        
      	END /* Termina while */


    COMMIT TRAN
    CLOSE TMP_CURSOR
    DEALLOCATE TMP_CURSOR
	
    RETURN(0)
END

