create or replace view v_deliverysignature as
select dsig.personidentification, dsig.signature, pn.consignmentnote_id,
       (select rc.drivingdate
           from routecontrol rc
          where rc.routecontrol_id = stop.routecontrol_id) signaturedate,
       to_char(stop.departuretime, 'fmhh24fm:MI') signaturetime24,
       dp.signaturebarcode,
       (case when length(dsig.signature) > 0 then 1
       else 0 end) hassignature,
        dsig.stop_id
  from packingnote pn
  join stop on (pn.stop_id = stop.stop_id)
  join deliverypoint dp on pn.deliverypoint_id = dp.deliverypoint_id
  left outer join deliverysignature dsig on (dsig.stop_id = pn.stop_id and
                                 dsig.stop_id = pn.deliverysignature_id)
 where pn.packingnote_id =
       (select min(pn2.packingnote_id)
          from packingnote pn2
         where pn2.consignmentnote_id = pn.consignmentnote_id)
/
