create or replace PACKAGE DSC IS
   
   Procedure Inicializar(Pcia in char);
   Function  Activos return boolean;
   Function MaxPorc(Indice in number) return number;
   Function Evaluar(pDsc1 in number, pDsc2 in number, pDsc3 in number) return number;
   FUNCTION DscVolumen (Vcia  in char, Vart in varchar2,
                        Vlista in varchar2, unidades in number) return number;
   Function DscContado(Pdefault in number) return number;
   Function DscPieza(Pcia in char, Part in varchar2, Pmed in varchar2,
                     Pprom in number) return number;
   Function Redondeo(vDescUnd in number, vCant in number,
                     multiplo in number default null,
                     complemento in number default  null) RETURN number;
   Function Promocion(cia in Char, Articulo in Varchar2,  Lista in varchar2, Fecha in Date) return number;
   Procedure CombinarDescuentos(pValor in boolean);

END;
/

create or replace PACKAGE BODY DSC IS
   Pindpieza         number(1);
   Pindvol           number(1);
   Pindcont          number(1);
   Pdesccontado      vendescuentos.desccontado%type;
   Pmincompra        vendescuentos.mincompra%type;
   Pdescpieza        vendescuentos.descpieza%type;
   Pmedida           vendescuentos.medida%type;
   Pmaxdescvol       vendescuentos.maxdescvol%type;
   Poperadores       vendescuentos.operadores%type;
   Ptipored          vendescuentos.tipored%type;
   Pmultiplored      vendescuentos.multiplored%type;
   Pcomplementored   vendescuentos.complementored%type;
   Pdscxlista        boolean:=false;
   PCombinarDesc     boolean:=false;

   FUNCTION DescuentosxLista(Vcia  in char) return boolean IS
      CURSOR descuentos IS
         select '1'
         from   vendescuentovol
         where  cia      = Vcia
           and  listaprecio is not null;
      Vexi      char(1);

   Begin
      Open DESCUENTOS;
      Fetch DESCUENTOS into Vexi;
      Close DESCUENTOS;
      return (Vexi is not null);
   End DescuentosxLista;

   Procedure Inicializar(Pcia in char) is
   begin
      select desccontado, mincompra, descpieza, medida, maxdescvol, operadores,
             tipored, multiplored, complementored 
         into pdesccontado, pmincompra, pdescpieza, pmedida, pmaxdescvol, poperadores,
              Ptipored, Pmultiplored, Pcomplementored 
      from vendescuentos
      where cia=Pcia;
      Pindpieza:=1;
      Pindvol:=2;
      Pindcont:=3;
      pdscxlista:=DescuentosxLista(Pcia);
   exception
      when no_data_found then
         Poperadores:=null;
   end Inicializar;

   Function  Activos return boolean is
   begin
      return pCombinarDesc and poperadores is not null;
   end;

   Function MaxPorc(Indice in number) return number is
   Begin
      if Indice=Pindpieza then
         return pdescpieza;
      elsif indice=pindvol then
         return pmaxdescvol;
      elsif indice=pindcont then
         return pdesccontado;
      else
         return 0;
      end if;
   End;

   Function Evaluar(pDsc1 in number, pDsc2 in number, pDsc3 in number) return number is
      TDesc    number(8,4):=0;
      
   begin
      if dsc.poperadores is not null then
         if substr(dsc.pOperadores,1,1)='+' then
            Tdesc:=pDsc1+pDsc2;
         else
            Tdesc:=pDsc1+pDsc2-pDsc1*pDsc2/100;
         end if;
         if substr(dsc.pOperadores,2,1)='+' then
            Tdesc:=Tdesc+pDsc3;
         else
            Tdesc:=Tdesc+pDsc3-Tdesc*pDsc3/100;
         end if;
      end if;
      return nvl(Tdesc,0);
   end Evaluar;

   FUNCTION ListaConDsc(Vcia  in char, Vart in varchar2, 
                      Vlista in varchar2) return boolean IS
      CURSOR descuentos IS
         select '1'
         from   vendescuentovol
         where  cia      = Vcia
         and    articulo = Vart
         and    listaprecio = Vlista;
      Vexi      char(1);

   Begin
      Open DESCUENTOS;
      Fetch DESCUENTOS into Vexi;
      Close DESCUENTOS;
      return (Vexi is not null);
   End ListaConDsc;
   
   FUNCTION DscVolumenLista(Vcia  in char, Vart in varchar2,
                            Vlista in varchar2, unidades in number) return number IS
      CURSOR descuentos IS
         select porcdescuento
         from   vendescuentovol
         where  cia      = Vcia
         and    articulo = Vart
         and    listaprecio = Vlista
         and    cantidad   <= unidades
         order by cantidad desc;
      Vporc     vendescuentovol.porcdescuento%type:=null;

   Begin
      Open DESCUENTOS;
      Fetch DESCUENTOS into Vporc;
      Close DESCUENTOS;
      return nvl(Vporc,0);
   End DscVolumenLista;

   FUNCTION DscVolumen (Vcia  in char, Vart in varchar2,
                        Vlista in varchar2, unidades in number) return number IS
      CURSOR descuentos IS
         select porcdescuento
         from   vendescuentovol
         where  cia      = Vcia
         and    articulo = Vart
         and    listaprecio is null
         and    cantidad   <= unidades
         order by cantidad desc;
      Vporc     vendescuentovol.porcdescuento%type:=null;

   Begin
      if PdscxLista and ListaConDsc(Vcia, Vart, Vlista) then
         return DscVolumenLista(Vcia, Vart, Vlista, unidades);
      else
         Open DESCUENTOS;
         Fetch DESCUENTOS into Vporc;
         Close DESCUENTOS;
         return nvl(Vporc,0);
      end if;
   End DscVolumen;

   Function DscContado(Pdefault in number) return number is
      Vporc number(5,2);
   Begin
      return least(nvl(Dsc.pdesccontado,0),nvl(Pdefault,0));
   End DscContado;
   
   Function DscPieza(Pcia in char, Part in varchar2, Pmed in varchar2,
                     Pprom in number) return number is
      Vporc    number(5,2):=0;
      VFacMed  inv_medida.factor%type;
   Begin
      If Pmed=Dsc.pmedida then
         begin
            select m.factor
               into VFacMed 
            from inv_artic_medida a, inv_medida m
            where a.cod_cia=Pcia
              and a.cod_articulo=Part
              and a.medida!=Pmed
              and a.medida=m.medida
              and m.medida_base=Pmed
              and m.exponente=1;
            if Pprom>=VFacMed*0.9 then
               Vporc:=nvl(Dsc.pDescPieza,0);
            end if;
         exception
            when no_data_found then
               null;
            when too_many_rows then
               null;
         end;
      end if;
      return Vporc;
   End DscPieza;

   Function Redondeo(vDescUnd in number, vCant in number,
                     multiplo in number default null,
                     complemento in number default  null) RETURN number IS
      Vmultred   vendescuentos.multiplored%type:=nvl(multiplo, pmultiplored);
      Vcompred   vendescuentos.complementored%type:=nvl(complemento, pcomplementored);
   BEGIN
      if nvl(Vmultred,0)=0 or pTipored='N' then
         return VDescUnd*Vcant;
      elsif pTipored='U' then
         return trunc((vDescUnd+nvl(Vcompred,0))/Vmultred)*Vmultred*vCant;
      else
         return trunc((vDescUnd*vCant+nvl(Vcompred,0))/Vmultred)*vmultred;
      end if;
   END;

   Function Promocion(cia in Char, Articulo in Varchar2, Lista in varchar2, Fecha in Date) return number is
      cursor promociones is
         Select f.porc_descuento
         from PrePrecioPro f
         where cod_cia=cia
           and cod_articulo=articulo
           and (cod_lista_precio is null or cod_lista_precio=lista)
           and exists (Select cod_promocion
                       from PrePromocion
                       where cod_cia=cia
                         and cod_promocion=f.cod_promocion
                         and fecha_ini <= trunc(fecha)
                         and fecha_fin >= trunc(fecha))
         order by decode(cod_lista_precio, Lista, 0, 1);

      vporcentaje PrePrecioPro.porc_descuento%type;
   Begin
      Open promociones;
      fetch promociones into vPorcentaje;
      close promociones;
      return nvl(vporcentaje,0);
   End Promocion;

   Procedure CombinarDescuentos(pValor in boolean) is
   begin
      pCombinarDesc:=pValor;
   end;

END;
/
