Create or Replace Package InvUtil as

   Function Consecutivo(pCia In Varchar2, pSuc In Varchar2, pTipMovto In Varchar2,
                        pMovto In Varchar2) Return Number;

   Procedure FijarArticulo(pArticulo in Varchar2);

   Function ObtArticulo Return Varchar2;

   Function SigArtTemp(pCia in Varchar2) Return Varchar2;

   FUNCTION RefArticulo (pcia      in varchar2,
                         particulo in varchar2) RETURN varchar2;

End Invutil;
/

Create or Replace Package Body InvUtil as
   vArticulo Inv_Articulo.Cod_Articulo%Type;

   Function Consecutivo(pCia In Varchar2, pSuc In Varchar2, pTipMovto In Varchar2,
                        pMovto In Varchar2) Return Number IS
      pragma autonomous_transaction;
      vConsec inv_consec_movtos.num_movto%Type;
   Begin
      update Inv_Consec_Movtos
         set num_movto=num_movto+1
      WHERE  Cod_Cia   = pCia
        AND  Sucursal  = pSuc
        AND  Tip_Movto = pTipMovto
        AND  Cod_Movto = pMovto
      RETURNING NUM_MOVTO-1 INTO vConsec;
      IF sql%notfound then
         insert into inv_consec_movtos
              (cod_cia,sucursal,tip_movto,cod_movto,num_movto)
              values(pCia,pSuc,pTipMovto,pMovto,2);
         vConsec:=1;
      End if;
      commit;
      RETURN(vConsec);
   End Consecutivo;

   Procedure FijarArticulo(pArticulo in Varchar2) Is
   Begin
      vArticulo := pArticulo;
   End FijarArticulo;

   Function ObtArticulo Return Varchar2 Is
   Begin
      Return vArticulo;
   End ObtArticulo;

   Function SigArtTemp(pCia in Varchar2) Return Varchar2 Is
     Pragma Autonomous_Transaction;
     vCons      Inv_Parametro.Con_ArTemp%Type;
     vPrefTemp  Inv_Parametro.Pre_ArTemp%Type;
  begin
     Select Pre_ArTemp, Con_ArTemp + 1 
       Into vPrefTemp, vCons
     From Inv_Parametro
     Where Cod_Cia = pCia;

     Update Inv_Parametro
        Set Con_Artemp = vCons
     Where Cod_Cia = pCia;

     Commit;

     Return vPrefTemp||Lpad(vCons, 6, '0');
   End SigArtTemp;

   FUNCTION RefArticulo (pcia      in varchar2,
                         particulo in varchar2) RETURN varchar2 IS
                      
      vref inv_articulo.referencia%type;
   BEGIN
      select referencia
      into   vref
      from   inv_articulo
      where  cod_cia      = Pcia
      and    cod_articulo = particulo;
	 
      RETURN vref;
   Exception
	    when no_data_found then
	       raise_application_error(-20001, 'ERROR <GENERATP.RefArticulo>: Articulo '||particulo||' no existe.');
   END RefArticulo;

End Invutil;
/
