CREATE OR REPLACE PACKAGE CalcGen IS

   Function Contable(Salini in number, Debitos in number, creditos in number,
                     tipo in char, factor in number default 1) return number;
   PRAGMA RESTRICT_REFERENCES (Contable, WNDS, WNPS);

   Function To_Hour(x in number) return number;
   PRAGMA RESTRICT_REFERENCES (to_hour, WNDS, WNPS);

   Function MontoInteres(Saldo In Number,
                         Tasa  In Number,
                         Fecini In Date,
                         Fecfin in Date default sysdate) Return Number;
   PRAGMA RESTRICT_REFERENCES (MontoInteres, WNDS, WNPS);

   Function FechaNueva(Pfec in date, Dias  In Number) return  Date; 
   PRAGMA RESTRICT_REFERENCES (FechaNUeva, WNDS, WNPS);

   Function ImpHist(Pcia in char, Pimp in char, pfecha in date, pporcdef in number default 0) return number;
   PRAGMA RESTRICT_REFERENCES (ImpHist, WNDS, WNPS);

   Function DiasInteres(Saldo In Number, Monto  In Number, Tasa  In Number) Return Number;

   Function Porc_Mora_Real(Dias In Number, Tasa In Number) Return Number;

   Function Calc_Fecha (Fecha       In Date, 
                     Mon_Interes In Number, 
                     Tasa        In Number,
                     Saldo_Ant   In Number) Return Date;

   Function To_time(x in number) return number;
   PRAGMA RESTRICT_REFERENCES (to_time, WNDS, WNPS);

   Function Agente(Vcia in char, Vsuc in char, Vcliente in char,
                   Vtipo in char default 'VN') return varchar2;
   PRAGMA RESTRICT_REFERENCES (Agente, WNDS, WNPS);

   Function SegDerecho(Vmod in number, Vitm in number, Vcia in char, Vgru in varchar2) return varchar2;
   PRAGMA RESTRICT_REFERENCES (SegDerecho, WNDS, WNPS);

   Function ObtenerPeriodo(vcia in varchar2, fecha in date) return number;
   PRAGMA RESTRICT_REFERENCES (ObtenerPeriodo, WNDS, WNPS);

   Function ObtClave(Vcia in varchar2, Vmod in varchar2, Vobj in varchar2, Vcla in varchar2,
                     Vins in varchar2 default null, Vdef in varchar2 default null) return varchar2;

   Function IsNumber(x in varchar2, decs varchar2 default '.') RETURN boolean;

   Function InicioPeriodo(vcia in varchar2, per in number) return date;

   Function FinPeriodo(vcia in varchar2, per in number) return date;

END CalcGen;
/

CREATE OR REPLACE PACKAGE BODY CalcGen IS
   Function Contable(Salini in number, Debitos in number, creditos in number,
                     tipo in char, factor in number default 1) return number is
   Begin
      if tipo='N' then
         return factor*(debitos-creditos);
      elsif tipo='I' then
         return factor*Salini;
      elsif tipo='F' then
         return factor*(salini+debitos-creditos);
      else
         return null;
      end if;
   End Contable;


   Function To_Hour(x in number) return number is
   Begin
      return nvl(trunc(x/100)+mod(x,100)/60,0);
   End To_hour;

   Function MontoInteres(Saldo In Number,
                         Tasa  In Number,
                         Fecini In Date,
                         Fecfin in Date default sysdate) Return Number is
   Begin
      if fecini<fecfin then
         Return trunc(Saldo*Tasa*(trunc(fecfin)-trunc(fecini))/36000,2);
      else
         return 0;
      end if;
   End MOntoInteres;

   Function FechaNueva(Pfec in date, Dias  In Number) return  Date is
   Begin
      return Pfec+Dias;
   End FechaNueva;

   Function ImpHist(Pcia in char, Pimp in char, pfecha in date, pporcdef in number default 0) return number is
      cursor imptos is
         select porcentaje
         from genhistimp
         where cod_cia=pcia
           and impuesto=pimp
           and fecha<=pfecha
         order by fecha desc;
      vporc genhistimp.porcentaje%TYPE;
   BEGIN
      open imptos;
      fetch imptos into vporc;
      close imptos;
      return nvl(Vporc, pporcdef);
   END ImpHist;

   Function DiasInteres(Saldo In Number, Monto  In Number, 
                        Tasa  In Number) Return Number Is
   Begin
      If Tasa>0 and Saldo!=0 Then
         Return (Monto*36000)/(Saldo*Tasa);
      Else
         Return 0;
      End If;
   End DiasInteres;

   Function Porc_Mora_Real(Dias In Number, Tasa In Number) Return Number Is
   Begin
      If Dias>0 Then
         Return Nvl(Tasa*Dias/3000,0);
      Else
         Return 0;
      End If;
   End Porc_mora_real;

   Function Calc_Fecha (Fecha       In Date, 
                     Mon_Interes In Number, 
                     Tasa        In Number,
                     Saldo_Ant   In Number) Return Date Is 
   Begin
      Return Fecha - DiasInteres(Saldo_ant, Mon_Interes, Tasa);
   End Calc_fecha;

   Function To_Time(x in number) return number is
   Begin
      return nvl(trunc(x)*100+round((x-trunc(x))*60),0);
   End To_time;

   Function Agente(Vcia in char, Vsuc in char, Vcliente in char,
                   Vtipo in char default 'VN') return varchar2 IS
      Agente gen_clie_agente.cod_agente%type;
      cursor agentes is
         Select a.cod_agente
         from   Gen_clie_agente a
         where  a.cod_cia     = Vcia
         and    a.sucursal    = Vsuc
         and    a.cod_cliente = Vcliente
         and    a.tipo_agente = Vtipo
         order by nvl(a.cod_depto,'0');
   BEGIN
      Open Agentes;
      fetch Agentes into Agente;
      Close agentes;
      return Agente;
   END Agente;

   Function SegDerecho(Vmod in number, Vitm in number, Vcia in char, Vgru in varchar2) return varchar2 is
      VindD varchar2(8);
   Begin
      Select decode(ind_derecho,'S','CHECK','UNCHECK')
         into VindD
      from seg_grant
      where modid=Vmod
        and itemid=Vitm
        and cod_grupo=Vgru
        and cod_cia=Vcia;
      return VindD;
   Exception
     when no_data_found then
        return 'UNCHECK';
   End SegDerecho;

   Function ObtenerPeriodo(vcia in varchar2, fecha in date) return number is
      Vper number(6);
   begin
      select  ano_fiscal*100+per_proceso
         into vper
      from gen_periodo_rango
      where cod_cia=vcia
        and fec_inicio<=trunc(fecha)
        and fec_final>=trunc(fecha);
      return vper;
   exception
      when no_data_found then
         return null;
   end ObtenerPeriodo;

   Function ObtClave(Vcia in varchar2, Vmod in varchar2, Vobj in varchar2, Vcla in varchar2,
                     Vins in varchar2 default null, Vdef in varchar2 default null) return varchar2 is
      Vvalor genprefsval.valor%type;
   Begin
      Begin
         Select valor into Vvalor
         from genprefsval
         where cia=Vcia
           and modulo=Vmod
           and instancia=nvl(Vins, Vobj)
           and objeto=Vobj
           and clave=Vcla;
      Exception
         When no_data_found then
           if nvl(Vins,Vobj)!=Vobj then
              Select valor into Vvalor
              from genprefsval
              where cia=vcia
              and modulo=Vmod
              and instancia=Vobj
              and objeto=Vobj
              and clave=Vcla;
          end if;
      End;
      return nvl(vvalor, Vdef);
   Exception
      when no_data_found then
         return Vdef;
   End ObtClave;

   Function IsNumber(x in varchar2, decs varchar2 default '.') RETURN boolean IS
   Begin
      return translate(x,'z0123456789'||decs,'z') is null;
   End IsNUmber;

   Function InicioPeriodo(vcia in varchar2, per in number) return date is
      Vfec  date;
      Vanof gen_periodo_rango.ano_fiscal%type:=trunc(per/100);
      Vperf gen_periodo_rango.per_proceso%type:=mod(per, 100);
   begin
      select  fec_inicio
         into vfec
      from gen_periodo_rango
      where cod_cia=vcia
        and ano_fiscal = vanof
        and per_proceso = vperf;
      return vfec;
   end InicioPeriodo;

   Function FinPeriodo(vcia in varchar2, per in number) return date is
      Vfec  date;
      Vanof gen_periodo_rango.ano_fiscal%type:=trunc(per/100);
      Vperf gen_periodo_rango.per_proceso%type:=mod(per, 100);
   begin
      select  fec_final
         into vfec
      from gen_periodo_rango
      where cod_cia=vcia
        and ano_fiscal = vanof
        and per_proceso = vperf;
      return vfec;
   end FinPeriodo;

END CalcGen;
/

create or replace package utlgen is

   function Asociado(pcia in varchar2, pcliente in varchar2, ptipo in varchar2 default null) return varchar2;
   function AsociadoPadre(pcia in varchar2, pasociado in varchar2, ptipo in varchar2 default null) return varchar2;

end;
/

create or replace package body utlgen is

   TipoAso varchar2(3):='ASO';

   function Asociado(pcia in varchar2, pcliente in varchar2, ptipo in varchar2 default null) return varchar2 is
      cursor asociados is
         select identificacion
         from gen_contclie
         where cod_cia=pcia
           and cod_cliente=pcliente
           and tipo=nvl(pTipo, TipoAso);
      Vaso gen_contclie.identificacion%type;
   begin
      open asociados;
      fetch asociados into Vaso;
      close asociados;
      return nvl(vaso, pcliente);
   end Asociado;

   function AsociadoPadre(pcia in varchar2, pasociado in varchar2, ptipo in varchar2 default null) return varchar2 is
      cursor clientes is
         select cod_cliente
         from gen_contclie
         where cod_cia=pcia
           and identificacion=pasociado
           and tipo=nvl(pTipo, tipoaso);
      Vcli gen_contclie.cod_cliente%type;
   begin
      open clientes;
      fetch clientes into Vcli;
      close clientes;
      return nvl(Vcli, pasociado);
   end AsociadoPadre;

end;
/

set serveroutput on
begin
   dbms_output.put_line('Aso de 0001 '||utlgen.asociado('001','0001'));
   dbms_output.put_line('Padre de 0002 '||utlgen.asociadopadre('001','0002'));
end;
/

  