CREATE OR REPLACE FUNCTION fn_fco_nro_trab_eps
(
    pch_compania char,
    psi_anio number,
    pti_mes number,
    pch_sucursal char
)
RETURN number
AS
    vdc_total number(5,0);
    vdt_fec_ini date;
    vdt_fec_fin date;
    vti_por_suc number(1);
    vnu_dia number(2);
    vch_mes char(2);

BEGIN
    
    IF NVL(TRIM(pch_sucursal), '') = '' THEN 
        vti_por_suc := 0;
    ELSE
        vti_por_suc := 1;
    END IF;
    
    vnu_dia := 1;
    BEGIN
         SELECT fn_fco_ult_dia_mes (psi_anio, pti_mes)
         INTO vnu_dia
         FROM dual;
    EXCEPTION
         WHEN OTHERS THEN vnu_dia := 1;         
    END;

    vch_mes := pti_mes;
    IF LENGTH(pti_mes) = 1 THEN
       vch_mes := '0' || pti_mes;
    END IF;
    
    vdt_fec_ini := TO_DATE((RTRIM(psi_anio) || RTRIM(vch_mes) || '01'), 'YYYYMMDD');
    vdt_fec_fin := TO_DATE((RTRIM(psi_anio) || RTRIM(vch_mes) || RTRIM(vnu_dia)), 'YYYYMMDD');
    
--    DBMS_OUTPUT.PUT_LINE('vch_mes: '||vch_mes);
    vdc_total := 0;
    BEGIN
      SELECT
          count(*)
      INTO vdc_total  
      FROM
          trabajadores_grales 
      WHERE
          compania = pch_compania And
          clase_nomina <> 'NP' And
          fecha_ingreso <= vdt_fec_fin And
          (fecha_baja is null or fecha_baja >= vdt_fec_ini);
    EXCEPTION
       WHEN OTHERS THEN vdc_total := 0;    
    END;
    
    RETURN vdc_total;
END;
/
CREATE PUBLIC SYNONYM fn_fco_nro_trab_eps FOR ADAM.fn_fco_nro_trab_eps;
GRANT ALL ON fn_fco_nro_trab_eps TO ADAMA;