CREATE OR REPLACE PACKAGE CalcVen IS

   Function Desccxc(Cia In Varchar2, Lin In Varchar2, Fecpago In Date,
               Feccompra In Date, Fecvence In Date, Monto In Number) Return Number;
   PRAGMA RESTRICT_REFERENCES (Desccxc, WNDS, WNPS);

   Function Desccxp(Cia In Varchar2, Proveedor In Varchar2, Fecpago In Date,
               Feccompra In Date, Fecvence In Date, Monto In Number) Return Number;
   PRAGMA RESTRICT_REFERENCES (Desccxp, WNDS, WNPS);

   Function CalcDepto(Pcia in char, Part in varchar2, Pbod in char, Ptras in char default 'B') return varchar2;
   PRAGMA RESTRICT_REFERENCES (CalcDepto, WNDS, WNPS);

   Function ObtUbicacion(Pcia in char, PBod in char, Part in varchar2) return varchar2;
   PRAGMA RESTRICT_REFERENCES (ObtUbicacion, WNDS, WNPS);

   Function TotalFac(Pcia in char, Psuc in char, Pfac in number) return number;
   PRAGMA RESTRICT_REFERENCES (TotalFac, WNDS, WNPS);

   Function TotalDev(Pcia in char, Psuc in char, PDev in number) return number;
   PRAGMA RESTRICT_REFERENCES (TotalDev, WNDS, WNPS);

   Function TotalPed(Pcia in char, Psuc in char, Pped in number, Ptot in char default 'T') return number;
   PRAGMA RESTRICT_REFERENCES (TotalPed, WNDS, WNPS);

   Function TotalNota(Pcia in char, Psuc in char, Pdoc in number) return number;
   PRAGMA RESTRICT_REFERENCES (TotalNota, WNDS, WNPS);

   Function ExisteArt(Pcia in char, Pbod in char, Part in varchar2, Plot in varchar2) return number;
   PRAGMA RESTRICT_REFERENCES (ExisteArt, WNDS, WNPS);

END CalcVen;
/

CREATE OR REPLACE PACKAGE BODY CalcVen IS

   Function Desccxc(Cia       In Varchar2,
                    Lin       In Varchar2,
                    Fecpago   In Date,
                    Feccompra In Date,
                    Fecvence  In Date,
                    Monto     In Number) Return Number Is

      Cursor Dias(Lapso In Number) Is 
         Select Por_Desc
         From   Cxc_Por_Desc
         Where  Cod_Cia = Cia
         And    Linea = Lin
         And    Dias >= Lapso
         Order By Dias;

      P_Desc  Number:=0;

   Begin
      If Fecpago <= Fecvence Then
         Open Dias(Trunc(Fecpago)-Trunc(Feccompra));
         Fetch Dias Into P_Desc;
         If Dias%Notfound Then
            P_Desc:=0;
         End If;
         Close Dias;
      End If;    
      Return Nvl(Monto*P_Desc/100,0);
   End Desccxc;

   Function Desccxp(Cia       In Varchar2,
                    Proveedor In Varchar2,
                    Fecpago   In Date,
                    Feccompra In Date,
                    Fecvence  In Date,
                    Monto     In Number) Return Number Is

      Cursor Dias(Lapso In Number) Is 
         Select Por_Desc
         From   Cxp_Det_Descu
         Where  Cod_Cia = Cia
         And    Cod_Proveedor = Proveedor
         And    Dias >= Lapso
         Order By Dias;

      P_Desc  Number:=0;

   Begin
      If Fecpago <= Fecvence Then
         Open Dias(Trunc(Fecpago)-Trunc(Feccompra));
         Fetch Dias Into P_Desc;
         If Dias%Notfound Then
            P_Desc:=0;
         End If;
         Close Dias;
      End If;    
      Return Nvl(Monto*P_Desc/100,0);
   End Desccxp;

   Function CalcDepto(Pcia in char, Part in varchar2, Pbod in char, Ptras in char default 'B') return varchar2 is
      Vdep inv_articulo.cod_depto%type;
   Begin
      if Ptras='B' then
         return Pbod;
      else
         select cod_depto into Vdep
         from inv_articulo
         where cod_cia=Pcia
           and cod_articulo=Part;
         return Vdep;
      end if;
   Exception
      when no_data_found then
         return Pbod;
   End CalcDepto;

   Function ObtUbicacion(Pcia in char, PBod in char, Part in varchar2) return varchar2 is
      Cursor Ubi is
         select localiza
         from inv_artic_locbod
         where cod_cia=Pcia
           and cod_bodega=Pbod
           and cod_articulo=Part
         order by localiza;
      VLoc  inv_artic_locbod.localiza%type:=null;
   Begin
      open Ubi;
      fetch Ubi into Vloc;
      close Ubi;
      return Vloc;
   End ObtUbicacion;

   Function TotalFac(Pcia in char, Psuc in char, Pfac in number) return number is
      Vtotal vendetfactura.totallinea%type;
   Begin
      select sum(totallinea) into Vtotal
      from vendetfactura
      where cia=Pcia
        and sucursal=Psuc
        and documento=Pfac;
      return nvl(Vtotal, 0);
   End TotalFac;

   Function TotalDev(Pcia in char, Psuc in char, PDev in number) return number is
      Vtotal vendetdevolucion.totallinea%type;
   Begin
      select sum(totallinea) into Vtotal
      from vendetdevolucion
      where cia=Pcia
        and sucursal=Psuc
        and documento=PDev;
      return nvl(Vtotal, 0);
   End TotalDev;

   Function TotalPed(Pcia in char, Psuc in char, Pped in number, Ptot in char default 'T') return number is
      Vtotal number;
   Begin
      select sum(decode(Ptot,'C',totallinea,
                             'R',reservado*totallinea,
                             'F',facturado*totallinea/cantidad,
                             'B',(cantidad-facturado)*totallinea/cantidad,
                                totallinea)) into Vtotal
      from vendetpedido
      where cia=Pcia
        and sucursal=Psuc
        and documento=Pped;
      return nvl(Vtotal, 0);
   End TotalPed;

   Function TotalNota(Pcia in char, Psuc in char, Pdoc in number) return number is
      Vtotal vendetnota.totallinea%type;
   Begin
      select sum(totallinea) into Vtotal
      from vendetnota
      where cia=Pcia
        and sucursal=Psuc
        and documento=Pdoc;
      return nvl(Vtotal, 0);
   End TotalNota;

   Function ExisteArt(Pcia in char, Pbod in char, Part in varchar2, Plot in varchar2) return number is
      vtotal inv_lote_artic_bodega.can_existe%type;
   Begin
      select sum(can_existe) into vtotal
      from inv_lote_artic_bodega
      where cod_cia=Pcia
      	and cod_bodega=Pbod
	and cod_articulo=Part
	and cod_lote=Plot;
      return nvl(vtotal,0);
   End ExisteArt;
END CalcVen;
/
