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

CREATE PROCEDURE [dbo].[CPJ_Moratoria_Anular] @pJsonRecibido VARCHAR(8000)
AS 
SET NOCOUNT ON
 

	DECLARE @pId_Contrato     INT
    DECLARE @pFlagUsarTrans	  TINYINT

    SET @pId_Contrato	    = ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Contrato')), 0)
	SET @pFlagUsarTrans	    = ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'FlagUsarTrans')), 0)
 
	DECLARE @Id_Cliente     INT
	DECLARE @Fecha_Contrato DATETIME
	DECLARE @Id_Motivo_Est  SMALLINT
	DECLARE @Fecha_Baja     DATETIME  
	DECLARE @Id_Trans_Rec   INT

	Select @Fecha_Contrato = Fecha_Alta, @Id_Cliente = Id_Cliente From Contratos 
		   Where Id_Contrato = @pId_Contrato 

	select Top 1 @Id_Motivo_Est = Id_Motivo_Est, @Fecha_Baja = Fecha_Baja FROM CLIENTES_HIST 
		   WHERE ID_CLIENTE = @Id_Cliente and Fecha < @Fecha_Contrato Order by Fecha Desc

	SELECT @Id_Trans_Rec = ISNULL(C.ID_TRANS,0) 
		   FROM  CBTES C, CBTES_TIPOS T 
		   WHERE C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
		   AND T.Compromete_Recibos IN (1,-1)
 		   AND C.Id_Contrato = @pId_Contrato 

IF @pFlagUsarTrans <> 0 AND @@TRANCOUNT > 0 
	SET @pFlagUsarTrans = 0

BEGIN TRY
  
	IF @pFlagUsarTrans <> 0 
	BEGIN
		BEGIN TRAN
	END

	IF @Fecha_Baja is not null
	   UPDATE CLIENTES SET Id_Motivo_Est = @Id_Motivo_Est, Fecha_Baja = @Fecha_Baja, Activo = 0
			  WHERE ID_CLIENTE = @Id_Cliente

    IF @Id_Trans_Rec > 0
	   BEGIN

			UPDATE Clientes_CtaCte  SET ID_ESTADO_CBTE = 1, SALDO = A.Importe  
				   FROM  Clientes_CtaCte E, CBTES C, CBTES_APLICA A
				   WHERE E.Id_Trans = C.Id_Trans
				   AND A.Id_Trans_D = @Id_Trans_Rec
				   AND A.Id_Trans = C.Id_Trans
				   AND A.Id_Trans <> 0

			UPDATE CBTES SET ID_ESTADO_CBTE = 1, SALDO = A.Importe  
				   FROM  CBTES C, CBTES_APLICA A
				   WHERE A.Id_Trans_D = @Id_Trans_Rec
				   AND A.Id_Trans = C.Id_Trans
				   AND A.Id_Trans <> 0

			UPDATE Clientes_CtaCte  SET ID_ESTADO_CBTE = 3, SALDO = 0  
							   FROM  Clientes_CtaCte
							   WHERE Id_Trans = @Id_Trans_Rec

			UPDATE CBTES SET ID_ESTADO_CBTE = 3, SALDO = 0
				   FROM  CBTES 
				   WHERE Id_Trans  = @Id_Trans_Rec
       END

    UPDATE Clientes_CtaCte  SET Id_Estado_Cbte = 3 
		WHERE Id_Trans IN (SELECT Id_Trans FROM Cbtes WHERE Id_Contrato = @pId_Contrato)
		

    UPDATE CBTES SET Id_Estado_Cbte = 3, Imp_Total = 0,Imp_Gravado = 0, Saldo = 0 , Imp_Exento = 0 , Imp_Gravado2 = 0 , Imp_Gravado3 = 0
		,Imp_IVA = 0, Imp_IVA2 = 0 ,Imp_IVA3 = 0, Imp_Percep = 0, Imp_Percepcion_IB = 0, Imp_Percepcion_Iva = 0
		,Imp_Bonificacion = 0 , Imp_DescEspecial = 0 ,Imp_DescFin = 0 , Imp_Neto = 0, Imp_NoGravado = 0
		WHERE ID_CONTRATO = @pId_Contrato

    UPDATE CONTRATOS SET FECHA_BAJA = GETDATE(), ACTIVO = 0 WHERE ID_CONTRATO = @pId_Contrato	

  
	IF @pFlagUsarTrans <> 0 AND @@TRANCOUNT > 0
	BEGIN
		COMMIT
	END

	SELECT	 0				AS ErrorNumber 
			,0				AS ErrorSeverity  
			,0				AS ErrorState 
			,0				AS ErrorProcedure  
			,0				AS ErrorLine		
			,''				AS ErrorMessage


END TRY
BEGIN CATCH

	EXECUTE usp_GetErrorInfo

	IF @pFlagUsarTrans <> 0 AND @@TRANCOUNT > 0
	BEGIN
		ROLLBACK
	END

	RETURN 9999

END CATCH

/* 

 EXEC [CPJ_Moratoria_Anular]  '{"FlagUsarTrans":"0","Id_Contrato":"341730"}'

*/