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

CREATE procedure dbo.CP_Asientos_AjuInflacion @pDefinitivo CHAR(1), @pTipo CHAR(1),@pId_Moneda CHAR(3), @pFDesde datetime,@pFHasta datetime,@pId_Plan_Cta INT,@pAlias INT
/*

@Tipo = 'C'   --Coeficientes
@Tipo = 'F'   --Coeficientes A Aplicar
@Tipo = 'H'   --Valores Historicos
@Tipo = 'A'   --Valores Actuales
@Tipo = 'D'   --Diferencia

*/
AS

IF @pFDesde > @pFHasta 
	RETURN 1

IF DATEDIFF(MM,@pFDesde,@pFHasta) > 12  
	SET @pFHasta = DBO.CF_EOMonth(DATEADD(MM,12,@pFDesde))

IF UPPER(@pDefinitivo) = 'D' SET @pTipo = 'D'

DECLARE @Id_Cta_Contable INT
		,@Nro_Cta_Contable VARCHAR(20)
		,@Descripcion VARCHAR(80)
		,@Alias INT
		,@Saldo DECIMAL(16,2)
		,@Mes01	DECIMAL(18,5)
		,@Mes02	DECIMAL(18,5)
		,@Mes03	DECIMAL(18,5)
		,@Mes04	DECIMAL(18,5)
		,@Mes05	DECIMAL(18,5)
		,@Mes06	DECIMAL(18,5)
		,@Mes07	DECIMAL(18,5)
		,@Mes08	DECIMAL(18,5)
		,@Mes09	DECIMAL(18,5)
		,@Mes10	DECIMAL(18,5)
		,@Mes11	DECIMAL(18,5)
		,@Mes12	DECIMAL(18,5)
		

DECLARE @CoefMes00	DECIMAL(18,5) 
		,@CoefMes01	DECIMAL(18,5) 
		,@CoefMes02	DECIMAL(18,5) 
		,@CoefMes03	DECIMAL(18,5) 
		,@CoefMes04	DECIMAL(18,5) 
		,@CoefMes05	DECIMAL(18,5) 
		,@CoefMes06	DECIMAL(18,5) 
		,@CoefMes07	DECIMAL(18,5) 
		,@CoefMes08	DECIMAL(18,5) 
		,@CoefMes09	DECIMAL(18,5) 
		,@CoefMes10	DECIMAL(18,5) 
		,@CoefMes11	DECIMAL(18,5) 
		,@CoefMes12	DECIMAL(18,5) 
	

DECLARE	@CoefAplicMes01	DECIMAL(18,5) 
		,@CoefAplicMes02	DECIMAL(18,5) 
		,@CoefAplicMes03	DECIMAL(18,5) 
		,@CoefAplicMes04	DECIMAL(18,5) 
		,@CoefAplicMes05	DECIMAL(18,5) 
		,@CoefAplicMes06	DECIMAL(18,5) 
		,@CoefAplicMes07	DECIMAL(18,5) 
		,@CoefAplicMes08	DECIMAL(18,5) 
		,@CoefAplicMes09	DECIMAL(18,5) 
		,@CoefAplicMes10	DECIMAL(18,5) 
		,@CoefAplicMes11	DECIMAL(18,5) 
		,@CoefAplicMes12	DECIMAL(18,5)

CREATE TABLE #ValorH (
	Id_Cta_Contable INT,
	Nro_Cta_Contable VARCHAR(20),
	Descripcion VARCHAR(80),
	Alias SMALLINT,
	Mes01	DECIMAL(18,5) DEFAULT(0),
	Mes02	DECIMAL(18,5) DEFAULT(0),
	Mes03	DECIMAL(18,5) DEFAULT(0),
	Mes04	DECIMAL(18,5) DEFAULT(0),
	Mes05	DECIMAL(18,5) DEFAULT(0),
	Mes06	DECIMAL(18,5) DEFAULT(0),
	Mes07	DECIMAL(18,5) DEFAULT(0),
	Mes08	DECIMAL(18,5) DEFAULT(0),
	Mes09	DECIMAL(18,5) DEFAULT(0),
	Mes10	DECIMAL(18,5) DEFAULT(0),
	Mes11	DECIMAL(18,5) DEFAULT(0),
	Mes12	DECIMAL(18,5) DEFAULT(0),
	Total	DECIMAL(18,5) DEFAULT(0)
	)


CREATE TABLE #Resultado (
	Tipo Char(1),
	Id_Cta_Contable INT,
	Nro_Cta_Contable VARCHAR(20),
	Descripcion VARCHAR(80),
	Alias SMALLINT,
	Mes00   DECIMAL(18,5) DEFAULT(0),
	Mes01	DECIMAL(18,5) DEFAULT(0),
	Mes02	DECIMAL(18,5) DEFAULT(0),
	Mes03	DECIMAL(18,5) DEFAULT(0),
	Mes04	DECIMAL(18,5) DEFAULT(0),
	Mes05	DECIMAL(18,5) DEFAULT(0),
	Mes06	DECIMAL(18,5) DEFAULT(0),
	Mes07	DECIMAL(18,5) DEFAULT(0),
	Mes08	DECIMAL(18,5) DEFAULT(0),
	Mes09	DECIMAL(18,5) DEFAULT(0),
	Mes10	DECIMAL(18,5) DEFAULT(0),
	Mes11	DECIMAL(18,5) DEFAULT(0),
	Mes12	DECIMAL(18,5) DEFAULT(0),
	Total	DECIMAL(18,5) DEFAULT(0)
	)


IF UPPER(@pTipo) IN ('C','A','D','F') 
BEGIN

	SET @CoefMes00 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(DATEADD(MM,-1,@pFDesde)) ) 
	SET @CoefMes01 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(@pFDesde) ) 
	SET @CoefMes02 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(DATEADD(MM,1,@pFDesde)) ) 
	SET @CoefMes03 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(DATEADD(MM,2,@pFDesde)) ) 
	SET @CoefMes04 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(DATEADD(MM,3,@pFDesde)) ) 
	SET @CoefMes05 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(DATEADD(MM,4,@pFDesde)) ) 
	SET @CoefMes06 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(DATEADD(MM,5,@pFDesde)) ) 
	SET @CoefMes07 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(DATEADD(MM,6,@pFDesde)) ) 
	SET @CoefMes08 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(DATEADD(MM,7,@pFDesde)) ) 
	SET @CoefMes09 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(DATEADD(MM,8,@pFDesde)) ) 
	SET @CoefMes10 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(DATEADD(MM,9,@pFDesde)) ) 
	SET @CoefMes11 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(DATEADD(MM,10,@pFDesde)) ) 
	SET @CoefMes12 = (SELECT TOP 1 Coeficiente FROM Valores_Monedas_Inflacion  WHERE Id_Moneda = @pId_Moneda AND DBO.CF_EOMonth(Fecha) = DBO.CF_EOMonth(DATEADD(MM,11,@pFDesde)) ) 

	SET @CoefAplicMes01 = @CoefMes12 / @CoefMes00
	SET @CoefAplicMes02 = @CoefMes12 / @CoefMes01
	SET @CoefAplicMes03 = @CoefMes12 / @CoefMes02
	SET @CoefAplicMes04 = @CoefMes12 / @CoefMes03
	SET @CoefAplicMes05 = @CoefMes12 / @CoefMes04
	SET @CoefAplicMes06 = @CoefMes12 / @CoefMes05
	SET @CoefAplicMes07 = @CoefMes12 / @CoefMes06
	SET @CoefAplicMes08 = @CoefMes12 / @CoefMes07
	SET @CoefAplicMes09 = @CoefMes12 / @CoefMes08
	SET @CoefAplicMes10 = @CoefMes12 / @CoefMes09
	SET @CoefAplicMes11 = @CoefMes12 / @CoefMes10
	SET @CoefAplicMes12 = @CoefMes12 / @CoefMes11

	IF UPPER(@pTipo) IN ('C')
	BEGIN
		INSERT INTO #Resultado 
		SELECT 'C',0,'','Coeficientes',0 
				,@CoefMes00 AS Mes00
				,@CoefMes01 AS Mes01
				,@CoefMes02 AS Mes02
				,@CoefMes03 AS Mes03
				,@CoefMes04 AS Mes04
				,@CoefMes05 AS Mes05
				,@CoefMes06 AS Mes06
				,@CoefMes07 AS Mes07
				,@CoefMes08 AS Mes08
				,@CoefMes09 AS Mes09
				,@CoefMes10 AS Mes10
				,@CoefMes11 AS Mes11
				,@CoefMes12 AS Mes12
				,0
    END

	IF UPPER(@pTipo) IN ('F')
	BEGIN
		INSERT INTO #Resultado 
		SELECT 'C',0,'','Coeficientes a Aplicar',0
				,0 
				,@CoefAplicMes01 AS Mes01
				,@CoefAplicMes02 AS Mes02
				,@CoefAplicMes03 AS Mes03
				,@CoefAplicMes04 AS Mes04
				,@CoefAplicMes05 AS Mes05
				,@CoefAplicMes06 AS Mes06
				,@CoefAplicMes07 AS Mes07
				,@CoefAplicMes08 AS Mes08
				,@CoefAplicMes09 AS Mes09
				,@CoefAplicMes10 AS Mes10
				,@CoefAplicMes11 AS Mes11
				,@CoefAplicMes12 AS Mes12
				,0
	END
END

IF UPPER(@pTipo) IN ('H','A','D','F')
BEGIN
	DECLARE AjusInfla CURSOR FOR 
		SELECT Id_Cta_Contable,Nro_Cta_Contable,Descripcion,Alias
			FROM Pla_Ctas_Cuentas_Contables 
			WHERE Id_Plan_Cta = @pId_Plan_Cta AND ISNULL(Monetaria,0) = 0 AND Imputable = 1
			ORDER BY Nro_Cta_Contable

    OPEN AjusInfla
	FETCH NEXT FROM AjusInfla INTO @Id_Cta_Contable, @Nro_Cta_Contable, @Descripcion ,@Alias
					

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Mes01 = dbo.CF_Cta_Cont_Saldo(                              @pFDesde  ,              DBO.CF_EOMonth(@pFDesde) , @Alias, @pId_Plan_Cta,0,0,'',0,0,'',1)

		IF dbo.CF_FOMONTH(DATEADD(MM, 1,@pFDesde)) <= @pFHasta 
			SET @Mes02 = dbo.CF_Cta_Cont_Saldo( dbo.CF_FOMONTH(DATEADD(MM, 1,@pFDesde)),DBO.CF_EOMonth(DATEADD(MM, 1,@pFDesde)), @Alias, @pId_Plan_Cta,0,0,'',0,0,'',1)
		IF dbo.CF_FOMONTH(DATEADD(MM, 2,@pFDesde)) <= @pFHasta 
			SET @Mes03 = dbo.CF_Cta_Cont_Saldo( dbo.CF_FOMONTH(DATEADD(MM, 2,@pFDesde)),DBO.CF_EOMonth(DATEADD(MM, 2,@pFDesde)), @Alias, @pId_Plan_Cta,0,0,'',0,0,'',1)
		IF dbo.CF_FOMONTH(DATEADD(MM, 3,@pFDesde)) <= @pFHasta 
			SET @Mes04 = dbo.CF_Cta_Cont_Saldo( dbo.CF_FOMONTH(DATEADD(MM, 3,@pFDesde)),DBO.CF_EOMonth(DATEADD(MM, 3,@pFDesde)), @Alias, @pId_Plan_Cta,0,0,'',0,0,'',1)
		IF dbo.CF_FOMONTH(DATEADD(MM, 4,@pFDesde)) <= @pFHasta 
			SET @Mes05 = dbo.CF_Cta_Cont_Saldo( dbo.CF_FOMONTH(DATEADD(MM, 4,@pFDesde)),DBO.CF_EOMonth(DATEADD(MM, 4,@pFDesde)), @Alias, @pId_Plan_Cta,0,0,'',0,0,'',1)
		IF dbo.CF_FOMONTH(DATEADD(MM, 5,@pFDesde)) <= @pFHasta 
			SET @Mes06 = dbo.CF_Cta_Cont_Saldo( dbo.CF_FOMONTH(DATEADD(MM, 5,@pFDesde)),DBO.CF_EOMonth(DATEADD(MM, 5,@pFDesde)), @Alias, @pId_Plan_Cta,0,0,'',0,0,'',1)
		IF dbo.CF_FOMONTH(DATEADD(MM, 6,@pFDesde)) <= @pFHasta 
			SET @Mes07 = dbo.CF_Cta_Cont_Saldo( dbo.CF_FOMONTH(DATEADD(MM, 6,@pFDesde)),DBO.CF_EOMonth(DATEADD(MM, 6,@pFDesde)), @Alias, @pId_Plan_Cta,0,0,'',0,0,'',1)
		IF dbo.CF_FOMONTH(DATEADD(MM, 7,@pFDesde)) <= @pFHasta 
			SET @Mes08 = dbo.CF_Cta_Cont_Saldo( dbo.CF_FOMONTH(DATEADD(MM, 7,@pFDesde)),DBO.CF_EOMonth(DATEADD(MM, 7,@pFDesde)), @Alias, @pId_Plan_Cta,0,0,'',0,0,'',1)
		IF dbo.CF_FOMONTH(DATEADD(MM, 8,@pFDesde)) <= @pFHasta 
			SET @Mes09 = dbo.CF_Cta_Cont_Saldo( dbo.CF_FOMONTH(DATEADD(MM, 8,@pFDesde)),DBO.CF_EOMonth(DATEADD(MM, 8,@pFDesde)), @Alias, @pId_Plan_Cta,0,0,'',0,0,'',1)
		IF dbo.CF_FOMONTH(DATEADD(MM, 9,@pFDesde)) <= @pFHasta 
			SET @Mes10 = dbo.CF_Cta_Cont_Saldo( dbo.CF_FOMONTH(DATEADD(MM, 9,@pFDesde)),DBO.CF_EOMonth(DATEADD(MM, 9,@pFDesde)), @Alias, @pId_Plan_Cta,0,0,'',0,0,'',1)
		IF dbo.CF_FOMONTH(DATEADD(MM, 10,@pFDesde)) <= @pFHasta 
			SET @Mes11 = dbo.CF_Cta_Cont_Saldo( dbo.CF_FOMONTH(DATEADD(MM,10,@pFDesde)),DBO.CF_EOMonth(DATEADD(MM,10,@pFDesde)), @Alias, @pId_Plan_Cta,0,0,'',0,0,'',1)
		IF dbo.CF_FOMONTH(DATEADD(MM, 11,@pFDesde)) <= @pFHasta 
			SET @Mes12 = dbo.CF_Cta_Cont_Saldo( dbo.CF_FOMONTH(DATEADD(MM,11,@pFDesde)),DBO.CF_EOMonth(DATEADD(MM,11,@pFDesde)), @Alias, @pId_Plan_Cta,0,0,'',0,0,'',1)		

	    INSERT INTO #ValorH (Id_Cta_Contable,Nro_Cta_Contable,Descripcion,Alias,
						Mes01,Mes02,Mes03,Mes04,Mes05,Mes06,Mes07,Mes08,Mes09,Mes10,Mes11,Mes12)
				VALUES (@Id_Cta_Contable, @Nro_Cta_Contable, @Descripcion ,@Alias
					,@Mes01,@Mes02,@Mes03,@Mes04,@Mes04,@Mes06,@Mes07,@Mes08,@Mes08,@Mes10,@Mes11,@Mes12)

		FETCH NEXT FROM AjusInfla INTO @Id_Cta_Contable, @Nro_Cta_Contable, @Descripcion ,@Alias
	END
	CLOSE AjusInfla
	DEALLOCATE AjusInfla

	IF UPPER(@pTipo) IN ('H')
	BEGIN
		INSERT INTO #Resultado 
		SELECT 'H',Id_Cta_Contable,Nro_Cta_Contable,Descripcion,Alias,
					0,
					Mes01,
					Mes02,
					Mes03,
					Mes04,
					Mes05,
					Mes06,
					Mes07,
					Mes08,
					Mes09,
					Mes10,
					Mes11,
					Mes12,
					Mes01 + Mes02 + Mes03 + Mes04 + Mes05 + Mes06 + Mes07 + Mes08 + Mes09 + Mes10 + Mes11 + Mes12
				FROM #ValorH
    END
END


IF UPPER(@pTipo) IN ('A')
BEGIN
	INSERT INTO #Resultado 
	SELECT 	'A',Id_Cta_Contable 
			,Nro_Cta_Contable
			,Descripcion
			,Alias
			,0 
			,Mes01 * @CoefAplicMes01 AS Mes01
			,Mes02 * @CoefAplicMes02 AS Mes02
			,Mes03 * @CoefAplicMes03 AS Mes03
			,Mes04 * @CoefAplicMes04 AS Mes04
			,Mes05 * @CoefAplicMes05 AS Mes05
			,Mes06 * @CoefAplicMes06 AS Mes06
			,Mes07 * @CoefAplicMes07 AS Mes07
			,Mes08 * @CoefAplicMes08 AS Mes08
			,Mes09 * @CoefAplicMes09 AS Mes09
			,Mes10 * @CoefAplicMes10 AS Mes10
			,Mes11 * @CoefAplicMes11 AS Mes11
			,Mes12 * @CoefAplicMes12 AS Mes12
			,(Mes01 * @CoefAplicMes01) + (Mes02 * @CoefAplicMes02) + (Mes03 * @CoefAplicMes03) + (Mes04 * @CoefAplicMes04) + (Mes05 * @CoefAplicMes05) + (Mes06 * @CoefAplicMes06) +
			 (Mes07 * @CoefAplicMes07) + (Mes08 * @CoefAplicMes08) + (Mes09 * @CoefAplicMes09) + (Mes10 * @CoefAplicMes10) + (Mes11 * @CoefAplicMes11) + (Mes12 * @CoefAplicMes12)
			FROM  #ValorH
END

IF UPPER(@pTipo) IN ('D') AND UPPER(@pDefinitivo) <> 'D'
	SELECT 	'D',Id_Cta_Contable 
			,Nro_Cta_Contable
			,Descripcion
			,Alias 
			,(Mes01 * @CoefAplicMes01) - Mes01  AS Mes01
			,(Mes02 * @CoefAplicMes02) - Mes02  AS Mes02
			,(Mes03 * @CoefAplicMes03) - Mes03  AS Mes03
			,(Mes04 * @CoefAplicMes04) - Mes04  AS Mes04
			,(Mes05 * @CoefAplicMes05) - Mes05  AS Mes05
			,(Mes06 * @CoefAplicMes06) - Mes06  AS Mes06
			,(Mes07 * @CoefAplicMes07) - Mes07  AS Mes07
			,(Mes08 * @CoefAplicMes08) - Mes08  AS Mes08
			,(Mes09 * @CoefAplicMes09) - Mes09  AS Mes09
			,(Mes10 * @CoefAplicMes10) - Mes10  AS Mes10
			,(Mes11 * @CoefAplicMes11) - Mes11  AS Mes11
			,(Mes12 * @CoefAplicMes12) - Mes12  AS Mes12
			,((Mes01 * @CoefAplicMes01) - Mes01) + ((Mes02 * @CoefAplicMes02) - Mes02) + ((Mes03 * @CoefAplicMes03) - Mes03) + ((Mes04 * @CoefAplicMes04) - Mes04) +
			 ((Mes05 * @CoefAplicMes05) - Mes05) + ((Mes06 * @CoefAplicMes06) - Mes06) + ((Mes07 * @CoefAplicMes07) - Mes07) + ((Mes08 * @CoefAplicMes08) - Mes08) +
			 ((Mes09 * @CoefAplicMes09) - Mes09) + ((Mes10 * @CoefAplicMes10) - Mes10) + ((Mes11 * @CoefAplicMes11) - Mes11) + ((Mes12 * @CoefAplicMes12) - Mes12)
			FROM  #ValorH

IF UPPER(@pDefinitivo) = 'D'
	PRINT 'Falta generar asiento'

DROP TABLE #ValorH

SELECT * FROM #Resultado

DROP TABLE #Resultado

RETURN


-- EXEC CP_Asientos_AjuInflacion 'P','C','PES','20180101','20181231',nro_id_plan   --coefcientes
-- EXEC CP_Asientos_AjuInflacion 'P','F','PES','20180101','20181231',nro_id_plan   --coefcientes a aplicar
-- EXEC CP_Asientos_AjuInflacion 'P','H','PES','20180101','20181231',nro_id_plan   --Valores Historicos
-- EXEC CP_Asientos_AjuInflacion 'P','A','PES','20180101','20181231',nro_id_plan   --Valores Actuales
-- EXEC CP_Asientos_AjuInflacion 'P','D','PES','20180101','20181231',nro_id_plan   --difenrencia
-- EXEC CP_Asientos_AjuInflacion 'D','D','PES','20180101','20181231',nro_id_plan   --Generar Asiento


