IF OBJECT_ID('dbo.fn_acum_imp_rango_meses') IS NOT NULL
    DROP FUNCTION dbo.fn_acum_imp_rango_meses
GO
CREATE FUNCTION dbo.fn_acum_imp_rango_meses
(
    @pch_compania char(4),
    @pch_trabajador char(10),
    @pch_agr_cnc char(3),
    @pnu_anio_ini smallint,
    @pnu_mes_ini tinyint,
    @pnu_anio_fin smallint,
    @pnu_mes_fin tinyint
)
RETURNS decimal(19,6)
/********************************************************************/
/*** Reportes e Interfases de Data Tecnologia                     ***/
/*** Descripcion: Retorna el valor de una agrupacion de acumulados***/
/*** mensuales                                                    ***/
/*** Autor:	Diana Cecilia Rios Peinado                            ***/
/*** Fecha:	Junio 23 de 2005                                      ***/
/********************************************************************/
--OCT-02-05: DCR, Pasada a SQL de Oracle
--OCT-10-05: DCR, Funcionaba entre dos aņos, o para el mismo aņo comenzando en enero, mas no para el mismo
--aņo, con mes de inicio diferente a enero, Ej. de marzo a agosto de 2003, estaba tomando de enero a agosto de 2003
AS
BEGIN
DECLARE
  @vnu_imp_01 decimal(19,6),
  @vnu_imp_02 decimal(19,6),
  @vnu_imp_03 decimal(19,6),
  @vnu_imp_04 decimal(19,6),
  @vnu_imp_05 decimal(19,6),
  @vnu_imp_06 decimal(19,6),
  @vnu_imp_07 decimal(19,6),
  @vnu_imp_08 decimal(19,6),
  @vnu_imp_09 decimal(19,6),
  @vnu_imp_10 decimal(19,6),
  @vnu_imp_11 decimal(19,6),
  @vnu_imp_12 decimal(19,6),                      
  @vnu_acumula decimal(19,6),
  @vnu_acumula2 decimal(19,6),
  @vdc_importe decimal(19,6)
  
     --hallando el aņo final
     SET @vnu_acumula = 0
     SET @vnu_acumula2 = 0
     SET @vdc_importe = 0
     
     SET @vnu_imp_01 = 0
     SET @vnu_imp_02 = 0
     SET @vnu_imp_03 = 0
     SET @vnu_imp_04 = 0
     SET @vnu_imp_05 = 0
     SET @vnu_imp_06 = 0
     SET @vnu_imp_07 = 0
     SET @vnu_imp_08 = 0
     SET @vnu_imp_09 = 0
     SET @vnu_imp_10 = 0
     SET @vnu_imp_11 = 0
     SET @vnu_imp_12 = 0

			SELECT @vnu_imp_01 = am.acum_imp_mes_01, 
			@vnu_imp_02 = am.acum_imp_mes_02, 
			@vnu_imp_03 = am.acum_imp_mes_03, 
			@vnu_imp_04 = am.acum_imp_mes_04,
			@vnu_imp_05 = am.acum_imp_mes_05, 
			@vnu_imp_06 = am.acum_imp_mes_06, 
			@vnu_imp_07 = am.acum_imp_mes_07, 
			@vnu_imp_08 = am.acum_imp_mes_08,
			@vnu_imp_09 = am.acum_imp_mes_09, 
			@vnu_imp_10 = am.acum_imp_mes_10, 
			@vnu_imp_11 = am.acum_imp_mes_11, 
			@vnu_imp_12 = am.acum_imp_mes_12
			FROM acumulados_mensuales am
			WHERE compania = @pch_compania
			AND trabajador = @pch_trabajador
			AND agr_conceptos = @pch_agr_cnc
			AND anio = @pnu_anio_fin

     IF @pnu_mes_fin >= 1
        SET @vnu_acumula = @vnu_imp_01

     IF @pnu_mes_fin >= 2
        SET @vnu_acumula = @vnu_acumula + @vnu_imp_02
     
     IF @pnu_mes_fin >= 3
        SET @vnu_acumula = @vnu_acumula + @vnu_imp_03

     IF @pnu_mes_fin >= 4
        SET @vnu_acumula = @vnu_acumula + @vnu_imp_04

     IF @pnu_mes_fin >= 5
        SET @vnu_acumula = @vnu_acumula + @vnu_imp_05

     IF @pnu_mes_fin >= 6
        SET @vnu_acumula = @vnu_acumula + @vnu_imp_06

     IF @pnu_mes_fin >= 7
        SET @vnu_acumula = @vnu_acumula + @vnu_imp_07

     IF @pnu_mes_fin >= 8
        SET @vnu_acumula = @vnu_acumula + @vnu_imp_08

     IF @pnu_mes_fin >= 9
        SET @vnu_acumula = @vnu_acumula + @vnu_imp_09

     IF @pnu_mes_fin >= 10
        SET @vnu_acumula = @vnu_acumula + @vnu_imp_10
     
     IF @pnu_mes_fin >= 11
        SET @vnu_acumula = @vnu_acumula + @vnu_imp_11
     
     IF @pnu_mes_fin >= 12
        SET @vnu_acumula = @vnu_acumula + @vnu_imp_12
  
		 --hallamos de enero hasta el mes inicial, sin contar el inicial, para luego restarlo
     IF (@pnu_anio_fin = @pnu_anio_ini)
		 BEGIN  --aņo igual
		     IF @pnu_mes_ini > 1
		        SET @vnu_acumula = @vnu_acumula - @vnu_imp_01
		
		     IF @pnu_mes_ini > 2
		        SET @vnu_acumula = @vnu_acumula - @vnu_imp_02
		     
		     IF @pnu_mes_ini > 3
		        SET @vnu_acumula = @vnu_acumula - @vnu_imp_03
		
		     IF @pnu_mes_ini > 4
		        SET @vnu_acumula = @vnu_acumula - @vnu_imp_04
		
		     IF @pnu_mes_ini > 5
		        SET @vnu_acumula = @vnu_acumula - @vnu_imp_05
		
		     IF @pnu_mes_ini > 6
		        SET @vnu_acumula = @vnu_acumula - @vnu_imp_06
		
		     IF @pnu_mes_ini > 7
		        SET @vnu_acumula = @vnu_acumula - @vnu_imp_07
		
		     IF @pnu_mes_ini > 8
		        SET @vnu_acumula = @vnu_acumula - @vnu_imp_08
		
		     IF @pnu_mes_ini > 9
		        SET @vnu_acumula = @vnu_acumula - @vnu_imp_09
		
		     IF @pnu_mes_ini > 10
		        SET @vnu_acumula = @vnu_acumula - @vnu_imp_10
		     
		     IF @pnu_mes_ini > 11
		        SET @vnu_acumula = @vnu_acumula - @vnu_imp_11		     
		 END --aņo igual

     IF (@pnu_anio_fin > @pnu_anio_ini)
		 BEGIN--otro aņo
       SET @vnu_imp_01 = 0
       SET @vnu_imp_02 = 0
       SET @vnu_imp_03 = 0
       SET @vnu_imp_04 = 0
       SET @vnu_imp_05 = 0
       SET @vnu_imp_06 = 0
       SET @vnu_imp_07 = 0
       SET @vnu_imp_08 = 0
       SET @vnu_imp_09 = 0
       SET @vnu_imp_10 = 0
       SET @vnu_imp_11 = 0
       SET @vnu_imp_12 = 0

			SELECT @vnu_imp_01 = am.acum_imp_mes_01, 
			@vnu_imp_02 = am.acum_imp_mes_02, 
			@vnu_imp_03 = am.acum_imp_mes_03, 
			@vnu_imp_04 = am.acum_imp_mes_04,
			@vnu_imp_05 = am.acum_imp_mes_05, 
			@vnu_imp_06 = am.acum_imp_mes_06, 
			@vnu_imp_07 = am.acum_imp_mes_07, 
			@vnu_imp_08 = am.acum_imp_mes_08,
			@vnu_imp_09 = am.acum_imp_mes_09, 
			@vnu_imp_10 = am.acum_imp_mes_10, 
			@vnu_imp_11 = am.acum_imp_mes_11, 
			@vnu_imp_12 = am.acum_imp_mes_12
			FROM acumulados_mensuales am
			WHERE compania = @pch_compania
			AND trabajador = @pch_trabajador
			AND agr_conceptos = @pch_agr_cnc
			AND anio = @pnu_anio_ini        
            
       IF @pnu_mes_ini <= 1
          SET @vnu_acumula2 = @vnu_imp_01
  
       IF @pnu_mes_ini <= 2
          SET @vnu_acumula2 = @vnu_acumula2 + @vnu_imp_02
       
       IF @pnu_mes_ini <= 3
          SET @vnu_acumula2 = @vnu_acumula2 + @vnu_imp_03
  
       IF @pnu_mes_ini <= 4
          SET @vnu_acumula2 = @vnu_acumula2 + @vnu_imp_04
  
       IF @pnu_mes_ini <= 5
          SET @vnu_acumula2 = @vnu_acumula2 + @vnu_imp_05
  
       IF @pnu_mes_ini <= 6
          SET @vnu_acumula2 = @vnu_acumula2 + @vnu_imp_06
  
       IF @pnu_mes_ini <= 7
          SET @vnu_acumula2 = @vnu_acumula2 + @vnu_imp_07
  
       IF @pnu_mes_ini <= 8
          SET @vnu_acumula2 = @vnu_acumula2 + @vnu_imp_08
  
       IF @pnu_mes_ini <= 9
          SET @vnu_acumula2 = @vnu_acumula2 + @vnu_imp_09
  
       IF @pnu_mes_ini <= 10
          SET @vnu_acumula2 = @vnu_acumula2 + @vnu_imp_10
       
       IF @pnu_mes_ini <= 11
          SET @vnu_acumula2 = @vnu_acumula2 + @vnu_imp_11
       
       IF @pnu_mes_ini <= 12
          SET @vnu_acumula2 = @vnu_acumula2 + @vnu_imp_12
     END--otro aņo

     SET @vdc_importe = ISNULL(@vnu_acumula,0) + ISNULL(@vnu_acumula2,0)
     RETURN @vdc_importe
END
GO
GRANT EXECUTE ON dbo.fn_acum_imp_rango_meses TO PUBLIC
GO
