Create or Replace Package ComUtil as

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

   Function FechaDespachoOC(pCia in Varchar2, pSucursal in Varchar2, 
                            pOrden in Number) Return Date;


End Comutil;
/

Create or Replace Package Body ComUtil as

   cCia     Gen_Compania.Cod_Cia%Type := ' x ';
   cEstDesp GenPrefsVal.Valor%Type;

   Function Consecutivo(pCia In Varchar2, pSuc In Varchar2, pTipDoc In Varchar2) Return Number is
      pragma autonomous_transaction;
      VConsec com_consecutivo.consec_solicitud%type;
   BEGIN
      if pTipdoc='S' then
         update com_consecutivo
            set consec_solicitud=consec_solicitud+1
         WHERE  Cod_Cia   = pCia
           AND  Sucursal  = pSuc
         RETURNING consec_solicitud INTO vConsec;
      elsif pTipdoc='O' then
         update com_consecutivo
            set consec_orden=consec_orden+1
         WHERE  Cod_Cia   = pCia
           AND  Sucursal  = pSuc
         RETURNING consec_orden INTO vConsec;
      elsif pTipdoc='R' then
         update com_consecutivo
            set consec_reclamo=consec_reclamo+1
         WHERE  Cod_Cia   = pCia
           AND  Sucursal  = pSuc
         RETURNING consec_reclamo INTO vConsec;
      elsif pTipdoc='N' then
         update com_consecutivo
            set consec_nacio=consec_nacio+1
         WHERE  Cod_Cia   = pCia
           AND  Sucursal  = pSuc
         RETURNING consec_nacio INTO vConsec;
      elsif pTipdoc='RC' then
         update com_consecutivo
            set consec_recnac=consec_recnac+1
         WHERE  Cod_Cia   = pCia
           AND  Sucursal  = pSuc
         RETURNING consec_recnac INTO vConsec;
      end if;
      IF sql%notfound then
         Insert into  com_consecutivo
            (cod_cia, sucursal, consec_solicitud, consec_orden, consec_reclamo, consec_nacio, consec_recnac)
            Values(pCia, pSuc, decode(pTipdoc,'S',1,0), decode(pTipdoc,'O',1,0),
                   decode(pTipdoc,'R',1,0), decode(pTipdoc,'N',1,0), decode(pTipdoc,'RC',1,0));
         Vconsec:= 1;
      End if;
      Commit;
      return Vconsec;
   End Consecutivo;

   Function ObtEstadoDespacho(pCia in Varchar2) Return Varchar2 Is      
   Begin
      If pCia != cCia Then
         cCia     := pCia;
         cEstDesp := CalcGen.ObtClave(pCia, 'SGE', 'COMPRAS', 'ESTORDDESP', 'COMPRAS', Null);
      End If;

      Return cEstDesp;
   End ObtEstadoDespacho;

   Function FechaDespachoOC(pCia in Varchar2, pSucursal in Varchar2, 
                            pOrden in Number) Return Date Is
      vFecha   Com_Est_Orden.Fecha%Type;
      vEstDesp Com_Est_Orden.Cod_Estado%Type := ObtEstadoDespacho(pCia);
   Begin
      Select Fecha
        Into vFecha
      From Com_Est_Orden
      Where Cod_Cia    = pCia
        And Sucursal   = pSucursal
        And Num_Orden  = pOrden
        And Cod_Estado = vEstDesp;

      Return vFecha;
   Exception
      When No_Data_Found Then
         Return Null;
   End FechaDespachoOC;


End Comutil;
/
