Create Or Replace Function fn_fco_cir_sodexho
(
    pch_compania in char, 
    psi_anio in number, 
    pti_mes in number, 
    pch_trabajador in char
)
/***************************************/
/*** JCL 12-MAR-08                   ***/
/*** Retorna el valor gravado        ***/
/*** de bonos sodexho por trabajador ***/
/*** Cliente: TELMEX                 ***/
/***************************************/
Return Number Is
    Result number;
    vch_var varchar2(15);
    vch_agr_che char(3);
    vdc_ingresos number(19,6);
    vdc_tope number(19,6);
    vdc_aux number(2,1);
    vdc_che number(19,6);
Begin    
    -- Agrupación bonos sodexho
    Begin
        Select Left(dato, 3) Into vch_agr_che From vw_fdt_parametros
        Where pais = 'COL' And compania = pch_compania And parametro = 'retencion' And secuencia = 21;
    Exception
        When Others Then vch_agr_che := 'x';
    End;
    -- Tope de ingresos para que los bonos sean gravados
    Begin
        Select 'var_nom_' || Trim(Nvl(dato, '0')) Into vch_var
        From vw_fdt_parametros
        Where pais = 'COL' And compania = pch_compania And parametro = 'retencion' And secuencia = 20;
    Exception 
      When Others Then vch_var := 'var_nom_00';
    End;    
    vdc_tope := 0;
    Begin
        Select real Into vdc_tope
        From parametros_rh               
        Where compania = pch_compania
          And parametro_rh = vch_var;
    Exception
      When Others Then vdc_tope := 6502000;
    End;
    Begin
      Select Decode(pti_mes, 1, acum_imp_mes_01, 2, acum_imp_mes_02, 3, acum_imp_mes_03, 4, acum_imp_mes_04, 
                             5, acum_imp_mes_05, 6, acum_imp_mes_06, 7, acum_imp_mes_07, 8, acum_imp_mes_08,
                             9, acum_imp_mes_09,10, acum_imp_mes_10,11, acum_imp_mes_11,12, acum_imp_mes_12)
      Into vdc_ingresos
      From acumulados_mensuales 
      Where compania = pch_compania
        And trabajador = pch_trabajador
        And agr_conceptos = 'CGR'
        And concepto = 0
        And anio = psi_anio;
    Exception
      When Others Then vdc_ingresos := 0;
    End;
    vdc_aux := 1.3; -- para Telmex todos son Salario Integral, complementar esto luego
    Result := 0;
    If (vdc_ingresos / vdc_aux) >= vdc_tope Then
        /* Bonos Sodexho */
        Begin
            Select Decode(pti_mes, 1, acum_imp_mes_01, 2, acum_imp_mes_02, 3, acum_imp_mes_03, 4, acum_imp_mes_04, 
                                   5, acum_imp_mes_05, 6, acum_imp_mes_06, 7, acum_imp_mes_07, 8, acum_imp_mes_08,
                                   9, acum_imp_mes_09,10, acum_imp_mes_10,11, acum_imp_mes_11,12, acum_imp_mes_12)
            Into vdc_che
            From acumulados_mensuales
            Where compania = pch_compania
              And trabajador = pch_trabajador
              And agr_conceptos = vch_agr_che
              And concepto = 0
              And anio = psi_anio;
        Exception
            When Others Then vdc_che := 0;
        End;
        Result := vdc_che;
    End If;
    Return(Result);
End fn_fco_cir_sodexho;
/
Create Public Synonym fn_fco_cir_sodexho For ADAM.fn_fco_cir_sodexho;
Grant Execute On fn_fco_cir_sodexho To ADAMA;
