create or replace PACKAGE Catalogos IS

   type catrec is record (Tipo varchar2(1), fecha date, fecharige date, fechavence date);
   type cattab is table of catrec index by varchar2(25);

   PBod gen_agente.bodega%type;

   procedure Inicializar(Pcia in char);
   Function HayCatalogo return boolean;
   Function EsCatalogo(pArt in varchar2) return boolean;
   Function Fecha(pArt in varchar2, pSel in varchar2 default 'D') return date;
   Function Bodega return varchar2;
   Function Raiz   return varchar2;
   Procedure FijarBodega(pCia in char, pSuc in char, pAge in varchar2);
   Function PrimerFactura(pCia in char, pSuc in char, pCli in varchar2, Pdia in date) return boolean;
   Function PrimerPedido(pCia in char, pSuc in char, pCli in varchar2, Pdia in date) return boolean;
   Function EsClienteNuevo(pCia in varchar2, pSuc in varchar2, pCli in varchar2) return varchar2;

END;
/

create or replace PACKAGE BODY Catalogos IS

   Tabc cattab;

   vRaiz varchar2(5):='CAT%';

   procedure Inicializar(Pcia in char) is
      cursor catalogos is
         select c.tipo, c.catalogo, trunc(c.fecha) fecha, trunc(c.fecharige) fecharige
         from vencatalogo c
         where c.cia=Pcia
           and c.fecha<=trunc(sysdate)
           and exists (select 1 from venclasecli cc
                       where cc.cia=pCia
                         and cc.catalogo=c.catalogo);
      Function FecVence(pTipo in varchar2, pFecrig in date) return date is
         vFec date;
      begin
         select min(trunc(fecharige))
            into vFec
         from vencatalogo
         where cia=pCia
           and tipo=pTipo
           and fecharige>pFecrig;
         return vFec;
      end;
   begin
      tabc.delete;
      for c in catalogos
      loop
         tabc(c.catalogo).tipo:=c.tipo;
         tabc(c.catalogo).fecha:=c.fecha;
         tabc(c.catalogo).fecharige:=c.fecharige;
         tabc(c.catalogo).fechavence:=Fecvence(c.tipo, c.fecharige);
      end loop;
   end Inicializar;

   Function HayCatalogo return boolean is
   begin
      return tabc.count>0;
   end;

   Function EsCatalogo(pArt in varchar2) return boolean is
   begin
      return tabc(pArt).tipo is not null;
   exception
      when no_data_found then
         return false;      
   end;

   Function Fecha(pArt in varchar2, pSel in varchar2 default 'D') return date is
   begin
      if pSel = 'R' then
         return tabc(pArt).fecharige;
      elsif pSel = 'V' then
         return tabc(pArt).fechavence;
      else
         return tabc(pArt).fecha;
      end if;
   exception
      when no_data_found then
         return null;      
   end;

   Function Bodega return varchar2 is
   begin
      return PBod;
   end;

   Function Raiz return varchar2 is
   begin
      return vRaiz;
   end;

   procedure FijarBodega(pCia in char, pSuc in char, pAge in varchar2) is
   begin
      select bodega
         into pBod
      from gen_agente
      where cod_cia=Pcia
        and sucursal=Psuc
        and cod_agente=Page;
   exception
      when no_data_found then
         pBod:=null;
   end FijarBodega;
   
   Function PrimerFactura(pCia in char, pSuc in char, pCli in varchar2, Pdia in date) return boolean is
      cursor facturas is
         select '1' from venfactura
         where cia=pCia
           and sucursal=pSuc
           and cliente=pCli
           and fecha>=Trunc(pDia)
           and estado='A';
      pFacts char(1);
   Begin
      open facturas;
      fetch facturas into pFacts;
      close facturas;
      return pFacts is null;
   End PrimerFactura;

   Function PrimerPedido(pCia in char, pSuc in char, pCli in varchar2, Pdia in date) return boolean is
      cursor pedidos is
         select '1' from venPedido
         where cia=pCia
           and sucursal=pSuc
           and cliente=pCli
           and fecha>=Trunc(pDia)
           and estado='A';
      pPeds char(1);
   Begin
      open pedidos;
      fetch pedidos into pPeds;
      close pedidos;
      return pPeds is null;
   End PrimerPedido;

   Function EsClienteNuevo(pCia in varchar2, pSuc in varchar2, pCli in varchar2) return varchar2 is
      pFacts varchar2(1);
   Begin
      select distinct 'N'
         into pFacts
      from venfactura
      where cia=pCia
         and sucursal=pSuc
         and cliente=pCli
         and estado='A';
      return nvl(pFacts, 'S');
   exception
    when no_data_found then
      return 'S';
   End EsClienteNuevo;

END;
/
      -- Filtro del cursor
      -- and (Catalogos.EsClienteNuevo(pCia, pSuc, pCli)='N' or
      --        Catalogos.Fecha(c.catalogo, 'V')>=trunc(sysdate))

      -- Filtro del loop
      -- if Catalogos.EsCatalogo(dp.Catalogo) and Catalogos.PrimerPedido(pCia, pSuc, pCli, Catalogos.fecha(dp.catalogo)) then

create or replace package FactVarios is
   Function ImpuestoConsumo(pMonto in number, pPorc in number) return number;
   FUNCTION MaxDesv(TCValor in number, TCFactura in number) RETURN number;
   Function MontoAbono(Pcia in char, Psuc in char, Pdoc in number, Ppag in number, Plinea in char, Pest in char) return number;
   FUNCTION VerifMaximoDesv(pMonto in number, pTotFinal in number, pTotVals in number, pDmax in number) RETURN boolean;
   Function SigRegistro return number;
   Procedure NuevaFactura;
   Procedure ActualizaMonto(Pcia in char, Pcaja in char, Pcajero in varchar2, pMonto in number);
   Procedure RemueveMonto(pMonto in number);
   Procedure CierraFactura(pFactura in number, pMonto in number);

end FactVarios;
/

create or replace package body FactVarios is

   VnumId  number(10);
   Vmaximo number(14,2);
 
   Function ImpuestoConsumo(pMonto in number, pPorc in number) return number IS
   BEGIN
      return nvl(pMonto,0)*nvl(pPorc,0)/100;
   END;

   FUNCTION MaxDesv(TCValor in number, TCFactura in number) RETURN number IS
   BEGIN
      if nvl(TCValor,0)=nvl(TCFactura,0) then
         return 0;
      else
         return convmoneda.convertir(0.00999, TCValor, TCFactura);
      end if;
   END;

   Function MontoAbono(pCia in char, pSuc in char, pDoc in number, pPag in number, pLinea in char, pEst in char) return number IS
      Vmon venpagodocumentos.monto%type:=0;
   BEGIN
      if pest='A' and plinea is not null and ppag is not null then
         select monto
            into vmon
         from venpagodocumentos
         where cia=pcia
         and   sucursal=psuc
         and   documento=ppag
         and   referencia=pdoc;
      end if;
      return Vmon;
   Exception
      when no_data_found then
	 return 0;
   END;

   FUNCTION VerifMaximoDesv(pMonto in number, pTotFinal in number, pTotVals in number, pDmax in number) RETURN boolean IS
   BEGIN
      if (pMonto>pTotFinal+pDmax) or (pTotVals>pTotFinal+pDmax) then
         return true;
      else
         return false;
      end if;
   END;

   Function SigRegistro return number is
      VReg number(10);
   begin
      select venbitfacq.nextval
         into vReg
      from dual;
      return vReg;
   end;

   Procedure NuevaFactura is
   begin
      vNumId:=null;
      vMaximo:=0;
   end;

   Procedure ActualizaMonto(Pcia in char, Pcaja in char, Pcajero in varchar2, pMonto in number) is
      pragma autonomous_transaction;
   begin
      if VNumId is null then
         vNumId:=SigRegistro;
         vMaximo:=nvl(pMonto,0);
         insert into venbitfac
            (registro, cia, caja, cajero, maximo, final, borrado, usuario, fecha, estado)
            values (vNumId, pcia, pcaja, pcajero, vMaximo, 0 ,0, user, sysdate, 'P');
      else
         vMaximo:=greatest(vMaximo, pMonto);
         update venbitfac
            set maximo=vMaximo
         where registro=vNumId;
      end if;
      commit;
   end;

   Procedure RemueveMonto(pMonto in number) is
      pragma autonomous_transaction;
   begin
      if VNumId is not null then
         update venbitfac
            set borrado=borrado+nvl(pMonto,0)
         where registro=vNumId;
         commit;
      end if;
   end;

   Procedure CierraFactura(pFactura in number, pMonto in number) is
      pragma autonomous_transaction;
   begin
      if VNumId is not null then
         update venbitfac
            set factura=pFactura, final=nvl(pMonto,0), estado='A'
         where registro=vNumId;
         commit;
      end if;
      vNumId:=null;
   end;


End FactVarios;
/

set serveroutput on
begin
   catalogos.inicializar('001');
   if catalogos.esCatalogo('CAT.11-08') then
      dbms_output.put_line('Fecha CAT.11-08 '||catalogos.fecha('CAT.11-08','V'));
   else
      dbms_output.put_line('No es X');
   end if;
   dbms_output.put_line('Nuevo 2498712 '||catalogos.esclientenuevo('001','001','2498712'));
   if catalogos.PrimerPedido('001', '001', '2498712', catalogos.fecha('CAT.09-08')) then
      dbms_output.put_line('Primer pedido ');
   end if;
end;
/ 

select catalogo, fecha, fecharige from vencatalogo
where catalogo in ('CAT.09-08','CAT.10-08','CAT.11-08')

