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(STOP.DELIVERYPOINT_ID,PN.DELIVERYPOINT_ID) = PN.DELIVERYPOINT_ID
     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
     and (case when (RC.DRIVINGDATE < trunc(sysdate)) then (RC.DRIVINGDATE - PN.DELIVERYDATE) else 1 end) >= 0;
  return result;
end F_ASSIGNEDDN;
/