
IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CP_CBTE_ANULAR_REVERSION') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE dbo.CP_CBTE_ANULAR_REVERSION 
END
GO

CREATE PROCEDURE [dbo].CP_CBTE_ANULAR_REVERSION @ID_TRANS INT
AS 
DECLARE @Mensaje			VARCHAR(100)
BEGIN TRY
BEGIN TRAN


IF ((SELECT Id_Estado_cbte FROM CBTES WHERE ID_TRANS = @ID_TRANS) <> 1) 
	BEGIN
	    SET @Mensaje = 'El cbte debe estar en Estado Pendiente'
		ROLLBACK
		SELECT	 1099		    	AS ErrorNumber 
				,0					AS ErrorSeverity  
				,0					AS ErrorState 
				,0					AS ErrorProcedure  
				,0					AS ErrorLine		
				,@Mensaje			AS ErrorMessage
		RETURN 1
	END


		DECLARE @trans_nueva INTEGER
		DECLARE @NRO_CBTE INTEGER
	    declare @desc varchar(50)
		declare @fecha_mov datetime
		declare @user smallint
		declare @id_cond_vta  smallint
        declare @id_tipo_cbte varchar(5)
		Declare @Id_Estado_Cbte_Rever smallint

		SET @id_cond_vta = (SELECT id_cond_vta from cbtes where id_trans = @ID_TRANS) 
		SET @trans_nueva = (SELECT max(id_trans) from transacciones ) + 1
		SET @desc = (SELECT descripcion from transacciones where id_trans = @ID_TRANS) 
		SET @fecha_mov = (SELECT fecha_mov from transacciones where id_trans = @ID_TRANS) 
		SET @user = (SELECT id_usuario from transacciones where id_trans = @ID_TRANS) 
		SET @id_tipo_cbte = (SELECT Isnull(Id_tipo_cbte_Rever,'')  From cbtes_Tipos T, Cbtes C where C.Id_Tipo_Cbte = T.Id_Tipo_Cbte And C.id_trans = @ID_TRANS) 

		IF @id_tipo_cbte = ''
			BEGIN
				SET @Mensaje = 'El Cbte de Reversión debe estar definido'
				ROLLBACK
				SELECT	 880		    	AS ErrorNumber 
						,0					AS ErrorSeverity  
						,0					AS ErrorState 
						,0					AS ErrorProcedure  
						,0					AS ErrorLine		
						,@Mensaje			AS ErrorMessage
				RETURN 1
			END		

		SET @Id_Estado_Cbte_Rever = (SELECT Isnull(Id_Estado_cbte,1)  From cbtes_Tipos where Id_Tipo_Cbte = @id_tipo_cbte) 
			    
						
        ----------------------------- Primero los insert ----------------------------------
        INSERT INTO TRANSACCIONES
			(id_trans,descripcion,fecha_mov,id_usuario)
			values (@trans_nueva,@desc,@fecha_mov,@user)

               SELECT * INTO #CBTES_aux_sp FROM CBTES WHERE ID_TRANS = @ID_TRANS

               SELECT * INTO #CBTES_ITEMS_aux_sp FROM CBTES_ITEMS WHERE ID_TRANS = @ID_TRANS

               SELECT * INTO #Clientes_CtaCte_aux_sp FROM Clientes_CtaCte WHERE ID_TRANS = @ID_TRANS

	           SELECT * INTO #Cbtes_Reten_aux_sp FROM Cbtes_Reten WHERE ID_TRANS = @ID_TRANS

	           SELECT * INTO #Cbtes_Ctas_Contables_aux_sp FROM Cbtes_Ctas_Contables WHERE ID_TRANS = @ID_TRANS

               ALTER TABLE #Cbtes_Ctas_Contables_aux_sp ADD IMPORTE DECIMAL(14,2)

               -----------------------------Actualizo id_trans -----------------------------------------------

		update #CBTES_aux_sp set id_trans = @trans_nueva , id_tipo_cbte = @id_tipo_cbte, Id_Estado_Cbte = @Id_Estado_Cbte_Rever
		               ,Fecha = Getdate(), Periodo_Iva = Getdate(), Periodo = Getdate(), Fecha_Venc = Getdate()
					   ,Nro_Auto_Trib = '',Cai_Vence = NULL
                       where id_trans = @ID_TRANS

		update #CBTES_ITEMS_aux_sp set id_trans = @trans_nueva  where id_trans = @ID_TRANS

		update #Clientes_CtaCte_aux_sp set id_trans = @trans_nueva, Importe = Importe * -1
	                 	,Saldo = Saldo*-1, Fecha = Getdate(),Fecha_Vence = Getdate()  
					    where id_trans = @ID_TRANS

		update #Cbtes_Reten_aux_sp set id_trans = @trans_nueva, Importe = Importe * -1
	                 	,Fecha = Getdate()  
					    where id_trans = @ID_TRANS

		update #Cbtes_Ctas_Contables_aux_sp set IMPORTE = HABER
					    where id_trans = @ID_TRANS 

		update #Cbtes_Ctas_Contables_aux_sp set HABER = DEBE 
					    where id_trans = @ID_TRANS
						
		update #Cbtes_Ctas_Contables_aux_sp set Id_trans = @trans_nueva, Fecha =  Getdate(), DEBE = IMPORTE,
		                Id_Cta_Contable = (SELECT dbo.CF_Cta_Cont(Alias,  Getdate()))
					    where id_trans = @ID_TRANS					
						
		ALTER TABLE #Cbtes_Ctas_Contables_aux_sp DROP COLUMN IMPORTE									  
   -----------------------------agrego -----------------------------------------------

               INSERT INTO CBTES
                      SELECT * FROM  #CBTES_aux_sp WHERE ID_TRANS = @trans_nueva    

               INSERT INTO CBTES_ITEMS
                      SELECT * FROM #CBTES_ITEMS_aux_sp WHERE ID_TRANS = @trans_nueva 

               INSERT INTO Clientes_CtaCte
                      SELECT * FROM #Clientes_CtaCte_aux_sp WHERE ID_TRANS = @trans_nueva

               INSERT INTO Cbtes_Reten
                      SELECT * FROM #Cbtes_Reten_aux_sp WHERE ID_TRANS = @trans_nueva

               INSERT INTO Cbtes_Ctas_Contables
                      SELECT * FROM #Cbtes_Ctas_Contables_aux_sp WHERE ID_TRANS = @trans_nueva

   ----------------------------- borro -----------------------------------------------

               DROP TABLE #CBTES_aux_sp 
               DROP TABLE #CBTES_ITEMS_aux_sp 
               DROP TABLE #Clientes_CtaCte_aux_sp 
		       DROP TABLE #Cbtes_Reten_aux_sp 
		       DROP TABLE #Cbtes_Ctas_Contables_aux_sp 


COMMIT
SELECT	 0					AS ErrorNumber 
		,0					AS ErrorSeverity  
		,0					AS ErrorState 
		,0					AS ErrorProcedure  
		,@trans_nueva		AS ErrorLine		
		,@Mensaje			AS ErrorMessage

END TRY  


BEGIN CATCH  
    EXECUTE usp_GetErrorInfo;  
	ROLLBACK
END CATCH;   




-- exec CP_CBTE_ANULAR_REVERSION 37362