CREATE OR REPLACE FUNCTION fn_acum_imp_rango_meses
(
    pch_compania char,
    pch_trabajador char,
    pch_agr_cnc char,
    pnu_anio_ini number,
    pnu_mes_ini number,
    pnu_anio_fin number,
    pnu_mes_fin number
)
RETURN number
/********************************************************************/
/*** 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-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

IS
  vnu_imp_01 number(19,6);
  vnu_imp_02 number(19,6);
  vnu_imp_03 number(19,6);
  vnu_imp_04 number(19,6);
  vnu_imp_05 number(19,6);
  vnu_imp_06 number(19,6);
  vnu_imp_07 number(19,6);
  vnu_imp_08 number(19,6);
  vnu_imp_09 number(19,6);
  vnu_imp_10 number(19,6);
  vnu_imp_11 number(19,6);
  vnu_imp_12 number(19,6);                      
  vnu_acumula number(19,6);
  vnu_acumula2 number(19,6);
  vdc_importe number(19,6);
  
BEGIN

     --hallando el aņo final
     vnu_acumula := 0;
     vnu_acumula2:= 0;
     vdc_importe := 0;
     
     vnu_imp_01 := 0;
     vnu_imp_02 := 0;
     vnu_imp_03 := 0;
     vnu_imp_04 := 0;
     vnu_imp_05 := 0;
     vnu_imp_06 := 0;
     vnu_imp_07 := 0;
     vnu_imp_08 := 0;
     vnu_imp_09 := 0;
     vnu_imp_10 := 0;
     vnu_imp_11 := 0;
     vnu_imp_12 := 0;
     BEGIN
        SELECT am.acum_imp_mes_01, am.acum_imp_mes_02, am.acum_imp_mes_03, am.acum_imp_mes_04,
        am.acum_imp_mes_05, am.acum_imp_mes_06, am.acum_imp_mes_07, am.acum_imp_mes_08,
        am.acum_imp_mes_09, am.acum_imp_mes_10, am.acum_imp_mes_11, am.acum_imp_mes_12
        INTO vnu_imp_01, vnu_imp_02, vnu_imp_03, vnu_imp_04,
        vnu_imp_05, vnu_imp_06, vnu_imp_07, vnu_imp_08,
        vnu_imp_09, vnu_imp_10, vnu_imp_11, vnu_imp_12
        FROM acumulados_mensuales am
        WHERE compania = pch_compania
        AND trabajador = pch_trabajador
        AND agr_conceptos = pch_agr_cnc
        AND anio = pnu_anio_fin;        
     EXCEPTION
        WHEN OTHERS THEN vnu_imp_01 := 0;
                          vnu_imp_02 := 0;
                          vnu_imp_03 := 0;
                          vnu_imp_04 := 0;
                          vnu_imp_05 := 0;
                          vnu_imp_06 := 0;
                          vnu_imp_07 := 0;
                          vnu_imp_08 := 0;
                          vnu_imp_09 := 0;
                          vnu_imp_10 := 0;
                          vnu_imp_11 := 0;
                          vnu_imp_12 := 0;        
     END;

     IF pnu_mes_fin >= 1 THEN
        vnu_acumula := vnu_imp_01;
     END IF;

     IF pnu_mes_fin >= 2 THEN
        vnu_acumula := vnu_acumula + vnu_imp_02;
     END IF;
     
     IF pnu_mes_fin >= 3 THEN
        vnu_acumula := vnu_acumula + vnu_imp_03;
     END IF;

     IF pnu_mes_fin >= 4 THEN
        vnu_acumula := vnu_acumula + vnu_imp_04;
     END IF;

     IF pnu_mes_fin >= 5 THEN
        vnu_acumula := vnu_acumula + vnu_imp_05;
     END IF;

     IF pnu_mes_fin >= 6 THEN
        vnu_acumula := vnu_acumula + vnu_imp_06;
     END IF;

     IF pnu_mes_fin >= 7 THEN
        vnu_acumula := vnu_acumula + vnu_imp_07;
     END IF;

     IF pnu_mes_fin >= 8 THEN
        vnu_acumula := vnu_acumula + vnu_imp_08;
     END IF;

     IF pnu_mes_fin >= 9 THEN
        vnu_acumula := vnu_acumula + vnu_imp_09;
     END IF;

     IF pnu_mes_fin >= 10 THEN
        vnu_acumula := vnu_acumula + vnu_imp_10;
     END IF;
     
     IF pnu_mes_fin >= 11 THEN
        vnu_acumula := vnu_acumula + vnu_imp_11;
     END IF;
     
     IF pnu_mes_fin >= 12 THEN
        vnu_acumula := vnu_acumula + vnu_imp_12;
     END IF;

		 --hallamos de enero hasta el mes inicial, sin contar el inicial, para luego restarlo
     IF (pnu_anio_fin = pnu_anio_ini) THEN --aņo igual
		     IF pnu_mes_ini > 1 THEN
		        vnu_acumula := vnu_acumula - vnu_imp_01;
         END IF;
         
		     IF pnu_mes_ini > 2 THEN
		        vnu_acumula := vnu_acumula - vnu_imp_02;
         END IF;
         		     
		     IF pnu_mes_ini > 3 THEN
		         vnu_acumula := vnu_acumula - vnu_imp_03;
         END IF;
         		
		     IF pnu_mes_ini > 4 THEN
		         vnu_acumula := vnu_acumula - vnu_imp_04;
         END IF;
         		
		     IF pnu_mes_ini > 5 THEN
		         vnu_acumula := vnu_acumula - vnu_imp_05;
         END IF;
         		
		     IF pnu_mes_ini > 6 THEN
		         vnu_acumula := vnu_acumula - vnu_imp_06;
         END IF;
         		
		     IF pnu_mes_ini > 7 THEN
		         vnu_acumula := vnu_acumula - vnu_imp_07;
         END IF;
         		
		     IF pnu_mes_ini > 8 THEN
		         vnu_acumula := vnu_acumula - vnu_imp_08;
         END IF;
         		
		     IF pnu_mes_ini > 9 THEN
		         vnu_acumula := vnu_acumula - vnu_imp_09;
         END IF;
         		
		     IF pnu_mes_ini > 10 THEN
		         vnu_acumula := vnu_acumula - vnu_imp_10;
         END IF;
         		     
		     IF pnu_mes_ini > 11 THEN
		         vnu_acumula := vnu_acumula - vnu_imp_11;
         END IF;             
		 END IF;--aņo igual
  
     IF (pnu_anio_fin > pnu_anio_ini)  THEN
       vnu_imp_01 := 0;
       vnu_imp_02 := 0;
       vnu_imp_03 := 0;
       vnu_imp_04 := 0;
       vnu_imp_05 := 0;
       vnu_imp_06 := 0;
       vnu_imp_07 := 0;
       vnu_imp_08 := 0;
       vnu_imp_09 := 0;
       vnu_imp_10 := 0;
       vnu_imp_11 := 0;
       vnu_imp_12 := 0;
       BEGIN
          SELECT am.acum_imp_mes_01, am.acum_imp_mes_02, am.acum_imp_mes_03, am.acum_imp_mes_04,
          am.acum_imp_mes_05, am.acum_imp_mes_06, am.acum_imp_mes_07, am.acum_imp_mes_08,
          am.acum_imp_mes_09, am.acum_imp_mes_10, am.acum_imp_mes_11, am.acum_imp_mes_12
          INTO vnu_imp_01, vnu_imp_02, vnu_imp_03, vnu_imp_04,
          vnu_imp_05, vnu_imp_06, vnu_imp_07, vnu_imp_08,
          vnu_imp_09, vnu_imp_10, vnu_imp_11, vnu_imp_12
          FROM acumulados_mensuales am
          WHERE compania = pch_compania
          AND trabajador = pch_trabajador
          AND agr_conceptos = pch_agr_cnc
          AND anio = pnu_anio_ini;        
       EXCEPTION
          WHEN OTHERS THEN vnu_imp_01 := 0;
                            vnu_imp_02 := 0;
                            vnu_imp_03 := 0;
                            vnu_imp_04 := 0;
                            vnu_imp_05 := 0;
                            vnu_imp_06 := 0;
                            vnu_imp_07 := 0;
                            vnu_imp_08 := 0;
                            vnu_imp_09 := 0;
                            vnu_imp_10 := 0;
                            vnu_imp_11 := 0;
                            vnu_imp_12 := 0;        
       END;
            
       IF pnu_mes_ini <= 1  THEN
          vnu_acumula2 := vnu_imp_01;
       END IF;
  
       IF pnu_mes_ini <= 2  THEN
          vnu_acumula2 := vnu_acumula2 + vnu_imp_02;
       END IF;
       
       IF pnu_mes_ini <= 3 THEN
          vnu_acumula2 := vnu_acumula2 + vnu_imp_03;
       END IF;
  
       IF pnu_mes_ini <= 4 THEN
          vnu_acumula2 := vnu_acumula2 + vnu_imp_04;
       END IF;
  
       IF pnu_mes_ini <= 5 THEN
          vnu_acumula2 := vnu_acumula2 + vnu_imp_05;
       END IF;
  
       IF pnu_mes_ini <= 6 THEN
          vnu_acumula2 := vnu_acumula2 + vnu_imp_06;
       END IF;
  
       IF pnu_mes_ini <= 7 THEN
          vnu_acumula2 := vnu_acumula2 + vnu_imp_07;
       END IF;
  
       IF pnu_mes_ini <= 8 THEN
          vnu_acumula2 := vnu_acumula2 + vnu_imp_08;
       END IF;
  
       IF pnu_mes_ini <= 9 THEN
          vnu_acumula2 := vnu_acumula2 + vnu_imp_09;
       END IF;
  
       IF pnu_mes_ini <= 10 THEN
          vnu_acumula2 := vnu_acumula2 + vnu_imp_10;
       END IF;
       
       IF pnu_mes_ini <= 11 THEN
          vnu_acumula2 := vnu_acumula2 + vnu_imp_11;
       END IF;
       
       IF pnu_mes_ini <= 12 THEN
          vnu_acumula2 := vnu_acumula2 + vnu_imp_12;
       END IF;                       
     END IF;

     vdc_importe := NVL(vnu_acumula,0) + NVL(vnu_acumula2,0);
     RETURN vdc_importe;
END;
/
CREATE PUBLIC SYNONYM fn_acum_imp_rango_meses FOR ADAM.fn_acum_imp_rango_meses;
GRANT ALL ON fn_acum_imp_rango_meses TO ADAMA;