Create or replace package CalcPla is

   FUNCTION SalarioActual (Pcia IN char, emp  IN varchar2,fec in date default sysdate) RETURN number;
   PRAGMA RESTRICT_REFERENCES (SalarioActual, WNDS, WNPS);

   FUNCTION TarifaActual (Pcia IN char, lab  IN varchar2,fec in date default sysdate) RETURN number;
   PRAGMA RESTRICT_REFERENCES (TarifaActual, WNDS, WNPS);

   FUNCTION PeriodoDeFecha (Pcia      IN char, 
                            Pplanilla IN char,
                            fecha     IN date) RETURN number;
   PRAGMA RESTRICT_REFERENCES (PeriodoDeFecha, WNDS, WNPS);

   FUNCTION EmpleadoADistribuir(Pcia   IN char,
                                emp    IN varchar2,
                                ccosto IN varchar2) RETURN varchar2;
   PRAGMA RESTRICT_REFERENCES (EmpleadoADistribuir, WNDS, WNPS);

   FUNCTION ResultEval(Pcia in char, Pemp in varchar2, Pto in varchar2, Pfec in date) return number;
   PRAGMA RESTRICT_REFERENCES (ResultEval, WNDS, WNPS);
 

END CalcPla;
/

Create or replace package Body CalcPla is

   FUNCTION SalarioActual (Pcia IN char, emp  IN varchar2, fec in date default sysdate) RETURN number is
      CURSOR salarios IS
         select salarioNuevo
         from   plaSalario
         where  cia      = Pcia
         and    empleado = emp
         and    fecha    <=trunc(fec)
         order by fecha desc;
      vSalario plaSalario.salarioNuevo%type;
   BEGIN
      OPEN salarios;
      FETCH salarios INTO vSalario;
      CLOSE salarios;
      RETURN nvl(vSalario,0);	 	 
   END SalarioActual;

   FUNCTION TarifaActual (Pcia IN char, lab  IN varchar2, fec in date default sysdate) RETURN number IS
     CURSOR tarifas IS
        select Tarifa
	from   platarifalabor
	where  cia   = Pcia
	and    labor = lab
	and    fecha <=trunc(fec)
	order by fecha desc;

      vTarifa platarifalabor.tarifa%type;
   BEGIN
      OPEN tarifas;
      FETCH tarifas INTO vTarifa;
      CLOSE tarifas;
      RETURN nvl(vTarifa,0);	 	 
   END TarifaActual; 

   FUNCTION PeriodoDeFecha (Pcia      IN char, 
                            Pplanilla IN char,
                            fecha     IN date) RETURN number IS

      per plaperiodo.periodo%type;
   BEGIN
      select periodo  
         into   per
      from   plaPeriodo
      where  cia      = Pcia
      and    planilla = Pplanilla
      and    trunc(fecha) between fechaInicial and fechaFinal;
      RETURN per;
   EXCEPTION
      when no_data_found then
         RETURN 0;
   END PeriodoDeFecha;

   FUNCTION EmpleadoADistribuir(Pcia   IN char,
                                emp    IN varchar2,
                                ccosto IN varchar2) RETURN varchar2 IS
       CURSOR distribucion IS
         SELECT empleado
         FROM   PLACCOSTOEMP
         WHERE  cia      = Pcia
         AND    empleado = emp;
         
       Vemp PLACCOSTOEMP.Empleado%type;
   BEGIN
      if Ccosto='0' then
         OPEN distribucion;
         FETCH distribucion INTO Vemp;
         CLOSE distribucion;
      end if;
      return nvl(Vemp, '0');
   END EmpleadoADistribuir;

   FUNCTION ResultEval(Pcia in char, Pemp in varchar2, Pto in varchar2, Pfec in date) return number is
      cursor evalspuesto is
         select evaluacion, peso
         from rhevalpuesto e
         where e.cia=Pcia
           and e.puesto=Pto;
      cursor evals(Peval in char) is
         select resultado
         from rhevaluacionprof e
         where e.cia=Pcia
           and e.cedula=pemp
           and e.evaluacion=Peval
           and e.fecha>=Pfec
        order by e.fecha;
      Vtot number(5,2):=0;
      Vres number(5,2);

   BEGIN
      for ep in evalspuesto
      loop
         Vres:=0;
         open evals(ep.evaluacion);
         fetch evals into Vres;
         close evals;
         Vtot:=nvl(Vtot,0)+nvl(Vres,0)*ep.peso/100;
      end loop;
      return Vtot;
   END resultEval;

END CalcPla;
/
