CREATE OR REPLACE function evaluateVAT(zdcoid number, ondate Date, grossamount number) return varchar2 is
  TYPE refCursor IS REF CURSOR;
  net number;
  totaltax number;
  perc number;
  formula varchar2(2000);
  rcursor refCursor;
  
  cursor deptcodetaxes is
    select ztcd_udf taxformula
    from zdco, zdtl, ztxc, ztcd
    where zdco_id=zdtl_zdco_id and zdtl_ztxc_id=ztxc_id and ztxc_id=ztcd_ztxc_id
          and zdco_id=zdcoid
          and zdtl_fromtime<=ondate and ondate<=zdtl_totime
    order by ztcd_order;      
  rowdeptcodetaxes deptcodetaxes%rowtype;          
begin
  formula := '';
  open deptcodetaxes;
  loop
    fetch deptcodetaxes into rowdeptcodetaxes;
    exit when deptcodetaxes%notfound;
    
    if formula <> ' ' then
      formula := formula||' + ';
    end if;
      
    formula := formula||rowdeptcodetaxes.taxformula; 
    
  end loop;  
  close deptcodetaxes;
  
  totaltax :=0;
  
  if formula <> ' ' then
    formula:=replace(formula, 'x', to_char(1));
    formula:='(1+'||formula||')';
    open rcursor for 'select '||trim(formula)||' val from dual';
    fetch rcursor into perc;
    close rcursor;

    --get the vat        
    formula:=to_char(grossamount, '999999999.9999')||'*(1 - 1/'||to_char(perc, '999.9999')||')';
    open rcursor for 'select '||trim(formula)||' val from dual';
    fetch rcursor into totaltax;
    close rcursor;
  end if;
  return trunc(totaltax, 4);
end;
/
