IF OBJECT_ID('dbo.fn_fco_cir_salarios') IS NOT NULL
    DROP FUNCTION dbo.fn_fco_cir_salarios
GO
CREATE FUNCTION dbo.fn_fco_cir_salarios
(
    @pch_compania char(4),
    @psi_anio smallint,
    @pch_trabajador char(10)
)
RETURNS decimal(19,6)
AS
BEGIN
    DECLARE 
        @vch_agr_sal char(3),
        @vch_agr_che char(3),
        @vdc_sal decimal(19,6),
        @vdc_che decimal(19,6),
        @vsi_dif_dias smallint,
        @vdt_hasta smalldatetime,
        @vdt_desde smalldatetime,
        @vdc_tope_che decimal(19,6),
        @vdc_sal_minimo  decimal(19,6),            
        @vch_var_cheque  varchar(15)
        
    /* PARÁMETRO DEL VALOR DE SALARIOS */
    SELECT @vch_agr_sal = dato FROM vw_fdt_parametros
    WHERE compania = @pch_compania AND parametro = 'retencion' AND secuencia = 1

    /* PARÁMETRO DEL VALOR DE CHEQUES CANASTA */
    SELECT @vch_agr_che = dato FROM vw_fdt_parametros
    WHERE compania = @pch_compania AND parametro = 'retencion' AND secuencia = 21

    /* HALLAR TOPE DE CHEQUES */            
    SELECT @vdc_sal_minimo  = salario_minimo FROM hist_sal_min_zona
    WHERE zona = 'A' AND Year(fecha) = @psi_anio

    SELECT @vch_var_cheque = 'var_nom_' + LTrim(RTrim(IsNull(dato, ''))) FROM vw_fdt_parametros            
    WHERE compania = @pch_compania AND parametro = 'retencion' AND secuencia = 20            
    
    SELECT @vdc_tope_che = real * @vdc_sal_minimo FROM parametros_rh
    WHERE parametro_rh = @vch_var_cheque AND compania = @pch_compania

    /* VALOR DE LOS SALARIOS */
    SELECT @vdc_sal =
      acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +
      acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +
      acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12
    FROM acumulados_mensuales
    WHERE compania = @pch_compania 
      AND trabajador = @pch_trabajador
      AND agr_conceptos = @vch_agr_sal
      AND concepto = 0
      AND anio = @psi_anio

    /* VALOR DE LOS CHEQUES CANASTA */
    SELECT @vdc_che =
      acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +
      acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +
      acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12
    FROM acumulados_mensuales
    WHERE compania = @pch_compania               
      AND trabajador = @pch_trabajador               
      AND agr_conceptos = @vch_agr_che               
      AND concepto = 0               
      AND anio = @psi_anio

    SELECT 
        @vdt_desde = Case When Year(fecha_ingreso) = @psi_anio Then fecha_ingreso Else Convert(smalldatetime, RTrim(@psi_anio) + '0101') End,
        @vdt_hasta = Case When IsNull(Year(fecha_baja), 0) = @psi_anio Then fecha_baja Else Convert(smalldatetime, RTrim(@psi_anio) + '1231') End
    FROM trabajadores_grales
    WHERE compania = @pch_compania
      AND trabajador = @pch_trabajador

    /* Hallando la diferencia en dias entre desde y hasta */            
    IF IsNull(@vdc_che, 0) > 0            
    BEGIN            
        SET @vsi_dif_dias = 
        ((Year(@vdt_hasta) * 360) + 
         (Month(@vdt_hasta) * 30) +                 
         (Case Day(@vdt_hasta) 
            WHEN 31 THEN 30 
            WHEN 28 THEN Case Month(@vdt_hasta) WHEN 2 THEN 30 ELSE Day(@vdt_hasta) END 
            ELSE Day(@vdt_hasta) 
          END)) -                 
        ((Year(@vdt_desde) * 360) + 
         (Month(@vdt_desde) * 30) + 
         (Case Day(@vdt_desde)                 
            WHEN 31 THEN 30 
            WHEN 28 THEN Case Month(@vdt_desde) WHEN 2 THEN 30 ELSE Day(@vdt_desde) END 
            ELSE Day(@vdt_desde) 
          END)) + 1  
                   
        IF (@vdc_sal / @vsi_dif_dias) < @vdc_tope_che / 30            
            SET @vdc_sal = @vdc_sal - @vdc_che            
    END            

    RETURN IsNull(@vdc_sal, 0)
END
GO
GRANT EXECUTE ON dbo.fn_fco_cir_salarios TO PUBLIC
GO
