create or replace function F_SHOW_ROUTESTOP_STD
(stopid in                  stop.stop_id%type,
 routestopid in             stop.routestop_id%type,
 stoptype in                routestop.stoptype%type,
 plantid in                 plant.plant_id%type)
return number is
  existspn                  number;
  stopsonroutelist          systemsetting.value%type;
  printalldelnotes          systemsetting.value%type;
begin

  -- get the systemsettings
  begin
    stopsonroutelist:='0';
    select ss.value
    into stopsonroutelist
    from systemsetting ss
    where ss.plant_id = plantid
      and ss.name = 'STOPSONROUTELIST';
    exception when no_data_found then stopsonroutelist:='0';
  end;

/*  if stopsonroutelist = '1' then
    if (routestopid is not null) and stoptype <> 4 then return 1;
    else if stoptype <> 4 then return 0; end if;
    end if;
  end if;*/

  if stopsonroutelist = '1' then
    if (routestopid is not null) and stoptype <> 4 then return 1; end if;
    if (routestopid is null) then return 1; end if;
    if (not(routestopid is not null) and stoptype = 4) then return 0; end if;
  end if;

--  check if the stop was manually added
  if routestopid is null then return 1; end if;
--  check if exists packing note linked to the stop
  -- get the systemsettings
  begin
    printalldelnotes:='Y';
    select ss.value
    into printalldelnotes
    from systemsetting ss
    where ss.plant_id = plantid
      and ss.name = 'ROUTELISTPRINTALLDELNOTES';
    exception when no_data_found then printalldelnotes:='Y';
  end;
  -- check when print all del notes
  if printalldelnotes = 'Y' then
    begin
      select count(*)
      into existspn
      from packingnote pn, stop st, customer c
      where pn.stop_id = st.stop_id
        and st.customer_link_id = c.customer_id
        and st.stop_id = stopid;
      if existspn > 0 then return 1; end if;
    end;
  else
    begin
      select count(*)
      into existspn
      from packingnote pn0
      where pn0.stop_id = stopid
        and not exists
        (select *
         from packingnote pn, stop st, customer c, routecontrol rc, route r
         where pn.stop_id = st.stop_id
           and pn.stop_id = pn0.stop_id
           and pn0.packingnote_id = pn.packingnote_id
           and st.customer_link_id = c.customer_id
           and st.routecontrol_id = rc.routecontrol_id
           and rc.route_id = r.route_id
           and c.delnoteforbulkputupitems = 0
           and r.putupreportpercustomer > 0
           and exists (select * from packingnoteline pnl
               where pnl.putuptype = 1 and pnl.packingnote_id = pn.packingnote_id));
      if existspn > 0 then return 1; end if;
    end;
  end if;
--  check if exists straight even return linked to the stop
  select count(*)
  into existspn
  from straightevenreturn sir
  where sir.stop_id = stopid;
  if existspn > 0 then return 1; end if;
-- check if stop is defined as Get
  select count(*)
  into existspn
    from stop st
   where st.stoptype = 2
     and st.stop_id = stopid;
  if existspn > 0 then return 1; end if;
  return 0;
end F_SHOW_ROUTESTOP_STD;
/

