create or replace function f_assignedDN(routecontr_id in routecontrol.routecontrol_id%type)
  return number is
  result number;
begin
  result:=0;
  select /*+index(PN XIE15PACKINGNOTE)*/ NVL(max(PN.PACKINGNOTE_ID), 0)
  into result
  from PACKINGNOTE       PN,
       STOP,Status,
       deliverypoint dp,
        CUSTOMER,
       BUSINESSUNIT      BU, routecontrol rc
 where STOP.CUSTOMER_LINK_ID = CUSTOMER.CUSTOMER_ID
   and pn.deliverypoint_id=dp.deliverypoint_id
   and dp.customer_id=CUSTOMER.CUSTOMER_ID
   AND nvl(PN.DELIVERYPOINT_ID, 0) = nvl(STOP.DELIVERYPOINT_ID, 0)
   AND PN.PROCESSBUSINESSUNIT_ID = BU.BUSINESSUNIT_ID
    AND BU.PLANT_ID = rc.plant_id
   AND STOP.ROUTECONTROL_ID = rc.routecontrol_id
     and NVL(PN.ROUTECONTROL_ID,0) = 0 
     and NVL(PN.STOP_ID,0) = 0 
   AND PN.INVOICESTATUS = 0
   AND PN.STATUS_ID =status.status_id
   and status.statustype='PN'
   and status.code in ('10','20','30')
   and ((STOP.STATUS - 0) * (STOP.STATUS - 3) = 0)
   and rc.routecontrol_id=routecontr_id;
   return result;
end F_assignedDN;
/
