create or replace function f_checkdeliverysignature
  (packingnoteid in packingnote.packingnote_id%type
  ) return  varchar2 is
   hasSignature number;
   starSign varchar2(5);
begin
  hasSignature := 0;
  starSign := null;
  begin
   select count(1)
   into hasSignature
          from packingnote pn
          join stop on (pn.stop_id = stop.stop_id)
          join deliverysignature dsig on (dsig.stop_id = pn.stop_id and dsig.stop_id = pn.deliverysignature_id)
          where pn.packingnote_id = packingnoteid;
    exception when no_data_found then
        hasSignature := 0;
  end;
  if hasSignature = 0 then
    starSign := '*';
  end if;
  return starSign;
end f_checkdeliverysignature;
/
