create or replace package pack_routetransaction is

  /*********************************************************************************************\
  * -- Author   : ABS                                                                           *
  * -- Version  : 2.0a                                                                          *
  * -- Created  : 19-10-2009                                                                    *
  * -- Updated  : 12-05-2011 removed parameters transaction                                     *
  *               18-04-2012 always use stopnumber as passed, check delscheme                   *
  *                          with change stop same as with move stop                            *
  *               23-04-2012 use previous stop deliveryscheme in change route stop procedure    *
  *               04-09-2012 Added changes after testing package with ABSSolute                 *
  *               26-09-2012 Extra checks and changes for move route                            *
  *               13-10-2012 Less restrictions in the move route dates EMG                      *
  *               15-10-2012 Now do NOT delete productdeliveryday                               *
  *               16-10-2012 Added * to messages                                                *
  *               17-10-2012 Added = sign to change stopnumber check/update                     *
  *               30-10-2012 Updated move stop, extra start date and dyas check                 *
  *               01-11-2012 Updated selection for old stops removed = sign                     *
  *               05-11-2012 Stops,cancel only when no invoice for routecontrol                 *
  *               07-11-2012 Old stop from today possible.Update deliveryscans                  *
  *               31-01-2013 Extra check for move route todo 21976 from EMG                     *
  *               15-02-2013 (GSCTD21292)System_user passed as parameter in onlineroutechange   *
  *               27-02-2013 TD 22185 extra check routecontrol upd.analyse EMG                  *
  *               01-03-2013 TD 22196 remove check for NLI only upd.analyse EMG                 *
  *               08-03-2013 TD 22388 removed complete check pd.analyse EMG                     *
  *               19-04-2013 TD 22357 new system setting DIRECTSTOPCHANGE                       *
  *               22-05-2013 TS 21976 make active customerdeliveryscheme                        *
  *               16-01-2015 change stopnumber when routenumber length = 5   LST only           *
  *               28-01-2015 better logging with routetransactionid in logging                  *
  *               07-04-2015 merged with live version of packroutetransaction                   *
  *               27-07-2015 ABS-7284 lv_computedNextDrivingDate replaced by fp_changestartdate *
  *               08-02-2016 LST001-15 issue after merge, updated check update deliveryvariation*
  *               20-07-2016 ABS-24052 unique constraint error when moving route / ABS-24058    *
  *               08-12-2017 ABS-58723, ABS-58725 Add HOW ignoring restriction in PACK_Routetran*
  * -- Purpose  : Route transactions, the option to change routes                               *
  *               Basis is the ROUTECHANGETRANSACTION table                                     *
  * -- Status   : 0 New not processed                                                           *
  *               1 Succesfully processed                                                       *
  *               2 Error                                                                       *
  *               3 Not processed because of batch rejection                                    *
  *               4 Cancel                                                                      *
  \*********************************************************************************************/

  procedure addroutechange(fp_changestartdate          in routechangetransaction.changestartdate%type,
                           fp_oldroutelastdrivingdate  in date,
                           fp_customernumber           in customer.customernumber%type,
                           fp_deliverypointcode        in deliverypoint.code%type,
                           fp_plantcode                in plant.code%type,
                           fp_drivingday               in day.daynumber%type,
                           fp_routenumber              in route.routenumber%type,
                           fp_depotcode                in depot.code%type,
                           fp_stopnumber               in routestop.stopnumber%type,
                           fp_deliveryschemecode       in deliveryscheme.code%type,
                           fp_newdrivingday            in day.daynumber%type,
                           fp_newroutenumber           in route.routenumber%type,
                           fp_newstopnumber            in routestop.stopnumber%type,
                           fp_newdeliveryscheme        in deliveryscheme.code%type,
                           fp_productgroupcode         in productgroup.code%type,
                           fp_errmsg                   out routechangetransaction.errormsg%type,
                           fp_routechangetransactionid out routechangetransaction.routechangetransaction_id%type);

  procedure getroutechangestatus(fp_routechangetransactionid in routechangetransaction.routechangetransaction_id%type,
                                 fp_errmsg                   out routechangetransaction.errormsg%type,
                                 fp_status                   out routechangetransaction.status%type);

  procedure processroutechanges(fp_plantcode              in plant.code%type,
                                fp_batchtransactionnumber out routechangetransaction.batchtransactionnumber%type,
                                fp_status                 out routechangetransaction.status%type);

  procedure reprocessroutechanges(fp_batchtransactionnumber in routechangetransaction.batchtransactionnumber%type,
                                  fp_status                 out routechangetransaction.status%type);

  procedure onlineroutechange(fp_changestartdate         in routechangetransaction.changestartdate%type,
                              fp_oldroutelastdrivingdate in date,
                              fp_customernumber          in customer.customernumber%type,
                              fp_deliverypointcode       in deliverypoint.code%type,
                              fp_plantcode               in plant.code%type,
                              fp_drivingday              in day.daynumber%type,
                              fp_routenumber             in route.routenumber%type,
                              fp_depotcode               in depot.code%type,
                              fp_stopnumber              in routestop.stopnumber%type,
                              fp_deliveryschemecode      in deliveryscheme.code%type,
                              fp_newdrivingday           in day.daynumber%type,
                              fp_newroutenumber          in route.routenumber%type,
                              fp_newstopnumber           in routestop.stopnumber%type,
                              fp_newdeliveryscheme       in deliveryscheme.code%type,
                              fp_productgroupcode        in productgroup.code%type,
                               fp_sortSequence            in routestop.sortsequence%type,
                              fp_errmsg                  out routechangetransaction.errormsg%type,
                              fp_onlineroutechange       out number,
                              fp_systemuserid            in systemuser.systemuser_id%type default 0);

  procedure cancelroutechange(fp_routechangetransactionid in routechangetransaction.routechangetransaction_id%type,
                              fp_status                   out routechangetransaction.status%type);
  function getssmovedeliveredstop(fp_plantcode in plant.code%type) return number;
end pack_routetransaction;
/
create or replace package body pack_routetransaction is
  gv_defaultuserid         systemuser.systemuser_id%type := 0;
  gv_message               abslog.message%type;
  gv_stopnrunique          systemsetting.value%type;
  gv_databasecode          databasesettings.code%type;
  gv_directstopchange      systemsetting.value%type;
  gv_deliverydetermination systemsetting.value%type;
  ge_wrongvalue exception;
  ge_general    exception;
  gv_ssmovedeliveredstop systemsetting.value%type;
  gv_SS_MOVESTOPMOVEUP 		 systemsetting.value%type;

  type RouteChangeTransactionIdTable is table of number index by binary_integer;
  type MovestopmoveupStopNumberTable is table of number index by binary_integer;

  procedure write_log(fp_message  in abslog.message%type,
                      fp_priority in abslog.priority%type default 1) is
  begin
    /*
    Write message in ABSLOG with these priorities
    1 = Message
    2 = Warning
    3 = Error
    4 = Stack trace
    5 = Debug info
    */
    abslogwrite.abslog_write(5, 'pack_routetransaction', null, fp_priority, fp_message,
                             gv_defaultuserid);
  end write_log;

  function get_max_deliveryschemeid return number is
    lv_max_deliveryscheme deliveryscheme.deliveryscheme_id%type;
  begin
    --get max deliveryscheme, frequency 1 is the highest
    gv_message := 'get_max_deliveryschemeID';
    select min(ds.deliveryscheme_id)
    into   lv_max_deliveryscheme
    from   deliveryscheme ds
    where  ds.frequency = 1
    and    ds.offsetweek = 0;
    return lv_max_deliveryscheme;
  end get_max_deliveryschemeid;

  function get_plant_id(fp_plantcode in plant.code%type) return number is
    lv_plantid plant.plant_id%type;
  begin
    --get id for plant code
    gv_message := 'inactive plant code / plant code does not exist ' || nvl(fp_plantcode, 'NULL');
    select pt.plant_id
    into   lv_plantid
    from   plant pt
    where  pt.code = fp_plantcode
    and    pt.active = 'Y';
    return lv_plantid;
  end get_plant_id;

  function getssmovedeliveredstop(fp_plantcode in plant.code%type) return number is
    lv_value systemsetting.value%type;
  begin
    --get id for route
    gv_message := 'inactive route id / route id does not exist !';
    select ss.value
    into   lv_value
    from   systemsetting ss
    where  ss.name like 'MOVEDELIVEREDSTOP'
    and    ss.plant_id = (select p.plant_id from plant p where p.code = fp_plantcode);
    return lv_value;
  exception
    when no_data_found then
      begin
        return 0;
      end;
  end getssmovedeliveredstop;

  procedure get_ss_movestopmoveup(fp_plantcode in plant.code%type) is
  begin
    select ss.value
    into   gv_SS_MOVESTOPMOVEUP
    from   SYSTEMSETTING ss
    where  ss.name = 'MOVESTOPMOVEUP'
    and    ss.plant_id = (select p.plant_id from plant p where p.code = fp_plantcode);
  exception
    when no_data_found then
      gv_SS_MOVESTOPMOVEUP := 0;
  end get_ss_movestopmoveup;

  function is_plant_active(fp_plantid in plant.plant_id%type) return boolean is
    lv_active plant.active%type;
  begin
    --check if plant is active
    select pt.active into lv_active from plant pt where pt.plant_id = fp_plantid;
    if lv_active = 'Y' then
      return true;
    else
      gv_message := ' Plant must be active';
      return false;
    end if;
  end is_plant_active;

  function get_route_id(fp_routenumber in route.routenumber%type,
                        fp_drivingday  in route.drivingday_id%type,
                        fp_plantid     in route.plant_id%type) return number is
    lv_routeid route.route_id%type;
    lv_active  route.active%type;
  begin
    --get rout id
    gv_message := 'Route does not exist, route ' || fp_routenumber || ' day ' || fp_drivingday ||
                  ' plantID ' || fp_plantid;
    select r.route_id, r.active
    into   lv_routeid, lv_active
    from   route r
    where  r.routenumber = fp_routenumber
    and    r.plant_id = fp_plantid
    and    r.drivingday_id = fp_drivingday;
    if lv_active <> 'Y' then
      gv_message := 'Route ' || fp_routenumber || ' day ' || fp_drivingday || ' plantID ' ||
                    fp_plantid || ' is not active';
      raise ge_general;
    end if;
    return lv_routeid;
  end get_route_id;

  function get_routestopid(fp_routeid         in route.route_id%type,
                           fp_customerid      in customer.customer_id%type,
                           fp_deliverypointid in deliverypoint.deliverypoint_id%type,
                           fp_stopnumber      in routestop.stopnumber%type,
                           fp_changedate      in routechangetransaction.changestartdate%type,
                           fp_transaction     in routechangetransaction.transactionnumber%type default 0)
    return routestop.routestop_id%type is
    lv_routestopid routestop.routestop_id%type;
    lv_counter     number;
  begin
    --check if stop exists for customer / deliverypoint
    gv_message := 'get active stop number ' || fp_stopnumber || ' for routeID ' || fp_routeid ||
                  ' for customerID ' || fp_customerid || ' deliverypointID ' ||
                  nvl(to_char(fp_deliverypointid), 'NULL') || ' for change date ' ||
                  to_char(fp_changedate, 'dd-mon-yyyy') || ' for transaction number ' ||
                  to_char(fp_transaction);
    begin
      select routestop_id
      into   lv_routestopid
      from   (select rs.routestop_id
               from   routestop rs
               where  rs.route_id = fp_routeid
               and    rs.customer_id = fp_customerid
               and    rs.stopnumber = fp_stopnumber
               and    ((fp_deliverypointid is null and rs.deliverypoint_id is null) or
                     fp_deliverypointid = rs.deliverypoint_id)
               and    ((gv_directstopchange = 0 and
                     ((fp_changedate between rs.dateactive and rs.dateinactive) or
                     (fp_transaction in (5, 6) and gv_databasecode not in ('LST')))) or
                     (gv_directstopchange = 1 and trunc(sysdate) between rs.dateactive and
                     rs.dateinactive))
               order  by rs.dateactive desc) ---now take the most recent active one, in case there is more then 1...
      where  rownum = 1;
    exception
      when no_data_found then
        begin
          if fp_transaction = 99 then
            --not found on the right date, lets ignore that..8-6-2015 wbo/simo/pvd
            gv_message := 'get active stop number ' || fp_stopnumber || ' for routeID ' ||
                          fp_routeid || ' for customerID ' || fp_customerid || ' deliverypointID ' ||
                          nvl(to_char(fp_deliverypointid), 'NULL') || ' for JIT stop number change';
            select routestop_id
            into   lv_routestopid
            from   (select rs.routestop_id
                     from   routestop rs
                     where  rs.route_id = fp_routeid
                     and    rs.customer_id = fp_customerid
                     and    rs.stopnumber = fp_stopnumber
                     and    ((fp_deliverypointid is null and rs.deliverypoint_id is null) or
                           fp_deliverypointid = rs.deliverypoint_id)
                     and    rs.movedroutestop_id is null
                     order  by rs.dateactive desc)
            where  rownum = 1;
          else
            raise no_data_found;
          end if;
        end;
    end;
    --26-09-2012 extra check for more stops
    if fp_changedate > trunc(sysdate) and gv_directstopchange = 0 then
      begin
        select count(1)
        into   lv_counter
        from   routestop rs
        where  rs.route_id = fp_routeid
        and    rs.customer_id = fp_customerid
        and    rs.stopnumber = fp_stopnumber
        and    fp_changedate between rs.dateactive and rs.dateinactive
        and    ((fp_deliverypointid is null and rs.deliverypoint_id is null) or
              fp_deliverypointid = rs.deliverypoint_id); --check if more then one future route is found
        if lv_counter > 1 then
          gv_message := 'More than one future route stop exists. Stop cannot be moved/changed/deleted';
          raise ge_general;
        end if;
      end;
    end if;
    --
    return lv_routestopid;
  end get_routestopid;

  function check_dup_stop_route(fp_routeid    in route.route_id%type,
                                fp_stopnumber in routestop.stopnumber%type,
                                fp_changedate in routechangetransaction.changestartdate%type)
    return boolean is
    lv_routestopid routestop.routestop_id%type;
  begin
    --check if stop exists
    gv_message := 'check if stop number ' || fp_stopnumber || ' for routeID ' || fp_routeid ||
                  ' already exists';
    select max(rs.routestop_id)
    into   lv_routestopid
    from   routestop rs
    where  rs.route_id = fp_routeid
    and    rs.stopnumber = fp_stopnumber
    and    fp_changedate between rs.dateactive and rs.dateinactive;
    if lv_routestopid is null then
      return false;
    else
      return true;
    end if;
  end check_dup_stop_route;

  function get_district_id(fp_plantid in plant.plant_id%type) return number is
    lv_districtid district.district_id%type;
  begin
    --get district id of plant
    gv_message := 'get_district_id for plantID ' || fp_plantid;
    select min(dp.district_id)
    into   lv_districtid
    from   districtplant dp
    where  dp.plant_id = fp_plantid;
    return lv_districtid;
  end get_district_id;

  function get_max_batchtransactionnumber return number is
    lv_batchtransactionnumber routechangetransaction.batchtransactionnumber%type;
  begin
    --get_max_batchtransactionnumber
    select max(rct.batchtransactionnumber) + 1
    into   lv_batchtransactionnumber
    from   routechangetransaction rct;
    return lv_batchtransactionnumber;
  end get_max_batchtransactionnumber;

  function get_depot_id(fp_depotcode in depot.code%type, fp_plantid in plant.plant_id%type)
    return number is
    lv_depotid        depot.depot_id%type;
    lv_plantaddressid plant.address_id%type;
  begin
    --get or create depot id
    gv_message := 'depot code ' || nvl(fp_depotcode, 'NULL') || ' for plantID ' ||
                  to_char(fp_plantid);
    if fp_depotcode is null then
      return null;
    else
      select dt.depot_id
      into   lv_depotid
      from   depot dt
      where  dt.code = fp_depotcode
      and    dt.plant_id = fp_plantid;
      return lv_depotid;
    end if;
  exception
    when no_data_found then
      begin
        --create depot if it does not yet exist
        select pt.address_id into lv_plantaddressid from plant pt where pt.plant_id = fp_plantid;
        --get next sequence
        select seq_depot.nextval into lv_depotid from dual;
        --insert into depot
        insert into depot
          (depot_id, plant_id, code, description, systemuser_id, address_id, timestamp, phoneno,
           faxno)
        values
          (lv_depotid, fp_plantid, fp_depotcode, fp_depotcode, gv_defaultuserid, lv_plantaddressid,
           sysdate, null, null);
        return lv_depotid;
      end;
  end get_depot_id;

     function updateDuplicateDVAmount(fp_changestartdate deliveryvariations.deliverydate%type,
                                  fp_oldstopid       packingnote.stop_id%type,
                                  fp_processed       deliveryvariations.processed%type,
                                  fp_oldroutestopid  routestop.routestop_id%type
                                   )

   return number is
   lv_duplicateDeliveryVarId deliveryvariations.deliveryvariations_id%type;
   lv_count number;
 begin

 if (fp_processed = 'Y') then

      for xdv in (select *
                 from deliveryvariations dv
                where dv.processed = fp_processed
                  and dv.deliverydate <> fp_changestartdate
                  and dv.packingnote_id in
                      (select pk.packingnote_id
                         from packingnote pk
                        where pk.stop_id = fp_oldstopid)) loop
     begin
       update deliveryvariations dv
          set dv.deliverydate  = fp_changestartdate,
              dv.systemuser_id = gv_defaultuserid,
              dv.timestamp     = sysdate
        where dv.deliveryvariations_id = xdv.deliveryvariations_id;
     exception
       when dup_val_on_index then
         select max(dv.deliveryvariations_id)
           into lv_duplicateDeliveryVarId
           from deliveryvariations dv
          where dv.distributionpoint_id = xdv.distributionpoint_id
            and dv.product_id = xdv.product_id
            and dv.sizedefinition_id = xdv.sizedefinition_id
            and dv.finishingmethod_id = xdv.finishingmethod_id
            and dv.packingnote_id is null
            and dv.processed = fp_processed
            and dv.deliverydate = fp_changestartdate
            and dv.origin = xdv.origin
            and nvl(dv.causenote_id, 0) = nvl(xdv.causenote_id, 0)
            and dv.updaterentqty = xdv.updaterentqty
            and dv.soilcountsubtype = xdv.soilcountsubtype
            and nvl(dv.weborderline_id, 0) = nvl(xdv.weborderline_id, 0)
            and nvl(dv.reference, 0) = nvl(xdv.reference, 0)
            and nvl(dv.adjustmentreason_id, 0) =
                nvl(xdv.adjustmentreason_id, 0)
            and nvl(dv.reasoncode_id, 0) = nvl(xdv.reasoncode_id, 0);

         update deliveryvariations dv
            set dv.amount        = dv.amount + xdv.amount,
                dv.systemuser_id = gv_defaultuserid,
                dv.timestamp     = sysdate
          where dv.deliveryvariations_id = lv_duplicateDeliveryVarId;

         delete from deliveryvariations dv
          where dv.deliveryvariations_id = xdv.deliveryvariations_id;

     end;
   end loop; -- xdv

   else

    select count(*)
           into lv_count
                 from deliveryvariations dv
                where dv.processed = fp_processed
				and dv.origin <> 3
                  and  getabsdaynumber(dv.deliverydate) = (select r.drivingday_id
                                                          from routestop rss join route r on r.route_id=rss.route_id
                                                          where rss.routestop_id=fp_oldroutestopid)
                   and    dv.distributionpoint_id =  (select dp.distributionpoint_id
                    from   distributionpoint dp, department d
                    where  d.customer_id = (select st.customer_link_id from stop st where st.stop_id = fp_oldstopid )
                    and    d.department_id = dp.department_id
					and    dp.distributionpoint_id = dv.distributionpoint_id);


     for xdv in (select *
                 from deliveryvariations dv
                where dv.processed = fp_processed
				and dv.origin <> 3
                  and  getabsdaynumber(dv.deliverydate) = (select r.drivingday_id
                                                          from routestop rss join route r on r.route_id=rss.route_id
                                                          where rss.routestop_id=fp_oldroutestopid)
                   and    dv.distributionpoint_id = (select dp.distributionpoint_id
                    from   distributionpoint dp, department d
                    where
                    d.customer_id = (select st.customer_link_id from stop st where st.stop_id = fp_oldstopid )
                    and    d.department_id = dp.department_id
                    and    dp.distributionpoint_id = dv.distributionpoint_id) ) loop
     begin
       update deliveryvariations dv
          set dv.deliverydate  = fp_changestartdate,
              dv.systemuser_id = gv_defaultuserid,
              dv.timestamp     = sysdate
        where dv.deliveryvariations_id = xdv.deliveryvariations_id;
     exception
       when dup_val_on_index then
         select max(dv.deliveryvariations_id)
           into lv_duplicateDeliveryVarId
           from deliveryvariations dv
          where dv.distributionpoint_id = xdv.distributionpoint_id
            and dv.product_id = xdv.product_id
            and dv.sizedefinition_id = xdv.sizedefinition_id
            and dv.finishingmethod_id = xdv.finishingmethod_id
            and dv.processed = xdv.processed
            and dv.deliverydate = fp_changestartdate
            and dv.origin = xdv.origin
            and nvl(dv.causenote_id, 0) = nvl(xdv.causenote_id, 0)
            and dv.updaterentqty = xdv.updaterentqty
            and dv.soilcountsubtype = xdv.soilcountsubtype
            and nvl(dv.weborderline_id, 0) = nvl(xdv.weborderline_id, 0)
            and nvl(dv.reference, 0) = nvl(xdv.reference, 0)
            and nvl(dv.adjustmentreason_id, 0) =
                nvl(xdv.adjustmentreason_id, 0)
            and nvl(dv.reasoncode_id, 0) = nvl(xdv.reasoncode_id, 0);

         update deliveryvariations dv
            set dv.amount        = dv.amount + xdv.amount,
                dv.systemuser_id = gv_defaultuserid,
                dv.timestamp     = sysdate
          where dv.deliveryvariations_id = lv_duplicateDeliveryVarId;

         delete from deliveryvariations dv
          where dv.deliveryvariations_id = xdv.deliveryvariations_id;

     end;
   end loop; -- xdv


   end if;

   return lv_duplicateDeliveryVarId;
 end updateDuplicateDVAmount;


  procedure init_route(fp_routerec in out route%rowtype) is
    lv_query varchar2(1000);
    type cur_typ is ref cursor;
    rcur_typ cur_typ;
  begin
    gv_message := 'init route';
    --fill the route row with default values from its table
    for rec in (select data_default, data_type
                from   user_tab_columns
                where  table_name = 'ROUTE'
                order  by column_id) loop
      if rec.data_default is null then
        lv_query := lv_query || ', null';
      else
        lv_query := lv_query || ', ' || rec.data_default;
      end if;
    end loop;
    lv_query := 'SELECT' || substr(lv_query, 2) || ' FROM DUAL';
    open rcur_typ for lv_query;
    fetch rcur_typ
      into fp_routerec;
    close rcur_typ;
    fp_routerec.systemuser_id := gv_defaultuserid;
    fp_routerec.timestamp     := sysdate;
    --get route sequence
    gv_message := 'get sequence for route';
    select seq_route.nextval into fp_routerec.route_id from dual;
    --other default values
    fp_routerec.putupreportpercustomer := 0;
    fp_routerec.putupreportperroute    := 'N';
    fp_routerec.putupreportperdepot    := 'N';
    fp_routerec.putupreportroutestop   := 0;
    fp_routerec.printsoilticket        := 'N';
    fp_routerec.printorderform         := 0;
    fp_routerec.bundletagsflatw        := 0;
    fp_routerec.bundletagsgarm         := 0;
  end init_route;

  procedure init_routestop(fp_routestoprec in out routestop%rowtype) is
    lv_query varchar2(1000);
    type cur_typ is ref cursor;
    rcur_typ cur_typ;
  begin
    gv_message := 'init routestop';
    --fill the route row with default values from its table
    for rec in (select data_default, data_type
                from   user_tab_columns
                where  table_name = 'ROUTESTOP'
                order  by column_id) loop
      if rec.data_default is null then
        lv_query := lv_query || ', null';
      else
        lv_query := lv_query || ', ' || rec.data_default;
      end if;
    end loop;
    lv_query := 'SELECT' || substr(lv_query, 2) || ' FROM DUAL';
    open rcur_typ for lv_query;
    fetch rcur_typ
      into fp_routestoprec;
    close rcur_typ;
    fp_routestoprec.systemuser_id := gv_defaultuserid;
    fp_routestoprec.timestamp     := sysdate;
    --get route sequence
    gv_message := 'get sequence for routestop';
    select seq_routestop.nextval into fp_routestoprec.routestop_id from dual;
    --other default values
    fp_routestoprec.stoptype       := 3;
    fp_routestoprec.returnroute_id := fp_routestoprec.route_id;
    fp_routestoprec.soiltickets    := 0;
  end init_routestop;

  procedure init_stop(fp_stoprec in out stop%rowtype) is
    lv_query varchar2(1000);
    type cur_typ is ref cursor;
    rcur_typ cur_typ;
  begin
    gv_message := 'init stop';
    --fill the route row with default values from its table
    for rec in (select data_default, data_type
                from   user_tab_columns
                where  table_name = 'STOP'
                order  by column_id) loop
      if rec.data_default is null then
        lv_query := lv_query || ', null';
      else
        lv_query := lv_query || ', ' || rec.data_default;
      end if;
    end loop;
    lv_query := 'SELECT' || substr(lv_query, 2) || ' FROM DUAL';
    open rcur_typ for lv_query;
    fetch rcur_typ
      into fp_stoprec;
    close rcur_typ;
    fp_stoprec.systemuser_id := gv_defaultuserid;
    fp_stoprec.timestamp     := sysdate;
    --get route sequence
    gv_message := 'get sequence for stop';
    select seq_stop.nextval into fp_stoprec.stop_id from dual;
    --other default values
    fp_stoprec.status            := 0;
    fp_stoprec.chargedeliveryfee := 0;
    fp_stoprec.visitminutes      := 0;
    fp_stoprec.travelminutes     := 0;
    fp_stoprec.stoptype          := 3;
  end init_stop;

  procedure create_cpltransition(fp_oldstandarddeliverylineid in standarddeliveryline.standarddeliveryline_id%type,
                                 fp_newstandarddeliverylineid in standarddeliveryline.standarddeliveryline_id%type) is
  begin
    --create_cpltransition
    gv_message := 'insert cpltransition';
    insert into cpltransition
      (oldstandarddeliveryline_id, newstandarddeliveryline_id, systemuser_id, timestamp)
    values
      (fp_oldstandarddeliverylineid, fp_newstandarddeliverylineid, gv_defaultuserid, sysdate);
  exception
    when dup_val_on_index then
      null; --no problem is already there
  end create_cpltransition;

  function get_new_stopnumber(fp_routeid     in route.route_id%type,
                              fp_stopnumber  in routestop.stopnumber%type,
                              fp_changedate  in routechangetransaction.changestartdate%type,
                              fp_routenumber in route.routenumber%type) return number is
  begin
    --get_new_stopnumber
    gv_message := 'get_new_stopnumber';
    if gv_stopnrunique = 1 then
      --double numbers allowed
      return fp_stopnumber;
    else
      begin
        if check_dup_stop_route(fp_routeid, fp_stopnumber, fp_changedate) then
          --stop already found try with stop 0
          write_log('Stopnumber ' || to_char(fp_stopnumber) || ' already used for route ' ||
                    to_char(fp_routenumber), 2);
          return fp_stopnumber;
        else
          return fp_stopnumber;
        end if;
      exception
        when no_data_found then
          return fp_stopnumber;
      end;
    end if;
  end get_new_stopnumber;

  function check_customer_active(fp_customerid in customer.customer_id%type) return date is
    lv_active         customer.active%type;
    lv_customernumber customer.customernumber%type;
    lv_dateactive     customer.dateactive%type;
    lv_dateinactive   customer.dateinactive%type;
  begin
    --check if customer is active
    select c.customernumber, c.active, c.dateactive, c.dateinactive
    into   lv_customernumber, lv_active, lv_dateactive, lv_dateinactive
    from   customer c
    where  c.customer_id = fp_customerid;
    if (lv_active <> 'Y') or not (sysdate between lv_dateactive and lv_dateinactive) then
      gv_message := 'Customer ' || lv_customernumber || ' is not active';
      raise ge_general;
    end if;
    return lv_dateinactive;
  end check_customer_active;

  procedure check_deliverypoint_active(fp_deliverypointid in deliverypoint.deliverypoint_id%type) is
    lv_active deliverypoint.active%type;
  begin
    --check_deliverypoint_active
    if fp_deliverypointid is not null then
      gv_message := 'check_deliverypoint_active ' || fp_deliverypointid;
      select dp.active
      into   lv_active
      from   deliverypoint dp
      where  dp.deliverypoint_id = fp_deliverypointid;
      if lv_active <> 'Y' then
        gv_message := 'Deliverypoint is not active ' || fp_deliverypointid;
        raise ge_general;
      end if;
    end if;
  end check_deliverypoint_active;

  procedure check_customer_bu(fp_customerid in customer.customer_id%type,
                              fp_plantid    in plant.plant_id%type) is
    lv_plantid plant.plant_id%type;
  begin
    --check BU's via plants
    select bu.plant_id
    into   lv_plantid
    from   customer c, businessunit bu
    where  c.customer_id = fp_customerid
    and    c.defaultbusinessunit_id = bu.businessunit_id;
    if lv_plantid <> fp_plantid then
      begin
        select bu.plant_id
        into   lv_plantid
        from   customeractivities ca, businessunit bu
        where  ca.customer_id = fp_customerid
        and    ca.processbusinessunit_id = bu.businessunit_id
        and    bu.plant_id = fp_plantid;
      exception
        when no_data_found then
          gv_message := 'Customer not processed in this plant';
          raise ge_general;
      end;
    end if;
  end check_customer_bu;

  procedure check_deliveryschemes(fp_newdeliveryschemeid in deliveryscheme.deliveryscheme_id%type,
                                  fp_routeid             in route.route_id%type,
                                  fp_routedeliveryscheme in out route.deliveryscheme_id%type) is
    lv_newfrequency   deliveryscheme.frequency%type;
    lv_routefrequency deliveryscheme.frequency%type;
    lv_newoffset      deliveryscheme.offsetweek%type;
    lv_routeoffset    deliveryscheme.offsetweek%type;
  begin
    --check_deliveryschemes
    gv_message := 'check_deliveryschemes for routeID ' || fp_routeid;
    --get route deliveryscheme
    if fp_routedeliveryscheme is null then
      select r.deliveryscheme_id
      into   fp_routedeliveryscheme
      from   route r
      where  r.route_id = fp_routeid;
    end if;
    if fp_newdeliveryschemeid is not null then
      --get data fp_newdeliveryschemeid
      select ds.frequency, ds.offsetweek
      into   lv_newfrequency, lv_newoffset
      from   deliveryscheme ds
      where  ds.deliveryscheme_id = fp_newdeliveryschemeid;
      --get data fp_routedeliveryscheme
      select ds.frequency, ds.offsetweek
      into   lv_routefrequency, lv_routeoffset
      from   deliveryscheme ds
      where  ds.deliveryscheme_id = fp_routedeliveryscheme;
      if gv_databasecode <> 'LST' then
        begin
          --08-04-2015 no checks for LST
          --checks
          if lv_newfrequency < lv_routefrequency then
            gv_message := 'Stop has a higher frequency then the route';
            raise ge_general;
          end if;
          if mod(lv_newfrequency, lv_routefrequency) > 0 then
            gv_message := 'Frequency of the stop does not match the frequency of the route';
            raise ge_general;
          end if;
          if mod((lv_newoffset - lv_routeoffset), lv_routefrequency) > 0 then
            gv_message := 'Offset of the stop does not match the offset of the route';
            raise ge_general;
          end if;
        end;
      end if;
    end if;
  end check_deliveryschemes;

  procedure check_sortbatch(fp_routestopid in routestop.routestop_id%type,
                            fp_changedate  in routechangetransaction.changestartdate%type) is
    lv_countsortbatch number;
  begin
    if gv_directstopchange = 0 then
      --check_sortbatch
      gv_message := 'check_sortbatch';
      select count(1)
      into   lv_countsortbatch
      from   sortbatchcontents sbc
      where  sbc.routestop_id = fp_routestopid
      and    exists (select 1
              from   sortbatch sb
              where  sb.sortbatch_id = sbc.sortbatch_id
              and    sb.status in (1, 2)
              and    sb.batchdate >= fp_changedate);
      if lv_countsortbatch > 0 then
        gv_message := 'There is a sortbatch from this stop that must be sorted first';
        raise ge_general;
      end if;
    end if;
  exception
    when no_data_found then
      null;
  end check_sortbatch;

  procedure add_customer_deldays_movestop(fp_customerid       in customer.customer_id%type,
                                          fp_deliveryschemeid in deliveryscheme.deliveryscheme_id%type,
                                          fp_dayid            in customerdeliveryscheme.day_id%type,
                                          fp_olddayid         in customerdeliveryscheme.day_id%type,
                                          fp_oldroutestopid   in routestop.routestop_id%type,
                                          fp_changedate       in date,
                                          fp_old_delschemeid  in deliveryscheme.deliveryscheme_id%type,
                                          fp_deliverypointid  in deliverypoint.deliverypoint_id%type) is
    old_deliverytype customerdeliveryday.deliverytype%type;
    to_deliverytype  customerdeliveryday.deliverytype%type;
  begin
    --copy the productdeliveryday PLPC, only existing records, new is not needed
    for x in (select l.productlistpercustomer_id, pdd.deliverytype
              from   productlistpercustomer l, productdeliveryday pdd
              where  l.customer_id = fp_customerid
              and    l.productlistpercustomer_id = pdd.productlistpercustomer_id
              and    l.dateinactive >= fp_changedate
              and    pdd.day_id = fp_olddayid
              and    not exists
               (select 1
                      from   productdeliveryday pddx
                      where  pddx.productlistpercustomer_id = pdd.productlistpercustomer_id
                      and    pddx.day_id = fp_dayid
                      and    pddx.deliverytype = pdd.deliverytype)) loop
      begin
        insert into productdeliveryday
          (productlistpercustomer_id, day_id, timestamp, systemuser_id, deliverytype)
        values
          (x.productlistpercustomer_id, fp_dayid, sysdate, gv_defaultuserid, x.deliverytype);
      exception
        when dup_val_on_index then
          begin
            --already did exist, wrong delivery type, so update it, will be type B only
            if x.deliverytype = 'B' then
              begin
                update productdeliveryday pdd
                set    pdd.deliverytype = x.deliverytype,
                       pdd.systemuser_id = gv_defaultuserid,
                       pdd.timestamp = sysdate
                where  pdd.productlistpercustomer_id = x.productlistpercustomer_id
                and    pdd.day_id = fp_dayid;
              end;
            end if;
          end;
      end;
    end loop; --x PLPC loop
    --add_customer_deliveryscheme_and_days
    gv_message := 'add_customer_deldays_movestop for customerID ' || fp_customerid ||
                  ' deliveryschemeID ' || fp_deliveryschemeid || ' day ' || fp_dayid;
    if fp_deliveryschemeid is not null then
      --try to add, if it already exists no problem
      begin
        insert into customerdeliveryscheme
          (customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
        values
          (fp_customerid, fp_deliveryschemeid, fp_dayid, gv_defaultuserid, sysdate);
      exception
        when dup_val_on_index then
          update customerdeliveryscheme ds
          set    ds.active = 'Y',
                 ds.systemuser_id = gv_defaultuserid,
                 ds.timestamp = sysdate
          where  ds.active <> 'Y'
          and    ds.customer_id = fp_customerid
          and    ds.deliveryscheme_id = fp_deliveryschemeid
          and    ds.day_id = fp_dayid;
      end;
    end if;
    --loop add customerdeliveryschemes
    for x in (select dsprs.deliveryscheme_id
              from   deliveryschemesperstop dsprs
              where  dsprs.routestop_id = fp_oldroutestopid
              and    (dsprs.deliveryscheme_id <> fp_old_delschemeid)-- or fp_old_delschemeid is null)
              group  by dsprs.deliveryscheme_id) loop
      begin
        insert into customerdeliveryscheme
          (customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
        values
          (fp_customerid, x.deliveryscheme_id, fp_dayid, gv_defaultuserid, sysdate);
      exception
        when dup_val_on_index then
          update customerdeliveryscheme ds
          set    ds.active = 'Y',
                 ds.systemuser_id = gv_defaultuserid,
                 ds.timestamp = sysdate
          where  ds.active <> 'Y'
          and    ds.customer_id = fp_customerid
          and    ds.deliveryscheme_id = x.deliveryscheme_id
          and    ds.day_id = fp_dayid;
      end;
    end loop;
    -- linen days
    begin
      select customerdeliveryday.deliverytype
      into   old_deliverytype
      from   customerdeliveryday
      where  customerdeliveryday.customer_id = fp_customerid
      and    customerdeliveryday.day_id = fp_olddayid
      and    customerdeliveryday.typeofmerchadise = 1;
    exception
      when no_data_found then
        old_deliverytype := null;
    end;
    gv_message := 'move_stop old_deliverytype=' || old_deliverytype || ' for day ' || fp_olddayid ||
                  ' and TOM = 1';
    begin
      to_deliverytype := null;
      select customerdeliveryday.deliverytype
      into   to_deliverytype
      from   customerdeliveryday
      where  customerdeliveryday.customer_id = fp_customerid
      and    customerdeliveryday.day_id = fp_dayid
      and    customerdeliveryday.typeofmerchadise = 1;
      --departments day should be activated /deactivated:
      begin
        for xd in (select t.department_id
                   from   department t
                   where  t.customer_id = fp_customerid
                   and    not exists (select 1
                           from   distributionpoint dp
                           where  dp.department_id = t.department_id
                           and    (dp.deliverypoint_id = fp_deliverypointid or
                                 fp_deliverypointid is null))
                         -- to not exists a route stop for that day already and the department should not be inactivate
                   and    not exists (select 1
                           from   routestop rs, distributionpoint dp, route r
                           where  dp.department_id = t.department_id
                           and    (dp.deliverypoint_id = rs.deliverypoint_id or
                                 rs.deliverypoint_id is null)
                           and    rs.route_id = r.route_id
                           and    rs.customer_id = t.customer_id
                           and    r.drivingday_id = fp_dayid)) loop
          begin
            insert into deliveryday
              (day_id, department_id, systemuser_id, timestamp)
            values
              (fp_dayid, xd.department_id, gv_defaultuserid, sysdate);
          exception
            when dup_val_on_index then
              null;
          end;
        end loop; --xd
      exception
        when no_data_found then
          null;
      end;
      begin
        for xd in (select t.department_id
                   from   department t
                   where  t.customer_id = fp_customerid
                   minus
                   select t.department_id
                   from   department t
                   where  t.customer_id = fp_customerid
                   and    not exists (select 1
                           from   distributionpoint dp
                           where  dp.department_id = t.department_id
                           and    (dp.deliverypoint_id = fp_deliverypointid or
                                 fp_deliverypointid is null))
                         -- to not exists a route stop for that day already and the department should not be inactivate
                   and    not exists (select 1
                           from   routestop rs, distributionpoint dp, route r
                           where  dp.department_id = t.department_id
                           and    (dp.deliverypoint_id = rs.deliverypoint_id or
                                 rs.deliverypoint_id is null)
                           and    rs.route_id = r.route_id
                           and    rs.customer_id = t.customer_id
                           and    r.drivingday_id = fp_dayid)) loop
          begin
            delete from deliveryday
            where  day_id = fp_dayid
            and    department_id = xd.department_id;
          exception
            when others then
              null;
          end;
        end loop; --xd
      end;
      begin
        for xd in (select t.department_id
                   from   department t, deliveryday dd
                   where  t.customer_id = fp_customerid
                   and    t.department_id = dd.department_id
                   and    dd.day_id = fp_olddayid) loop
          begin
            insert into deliveryday
              (day_id, department_id, systemuser_id, timestamp)
            values
              (fp_dayid, xd.department_id, gv_defaultuserid, sysdate);
          exception
            when dup_val_on_index then
              null;
          end;
        end loop; --xd
      exception
        when no_data_found then
          null;
      end;
    exception
      when no_data_found then
        --no customer delivery days found
        begin
          if old_deliverytype is not null then
            gv_message := 'move_stop insert line in customer dv days for ' || old_deliverytype ||
                          ' for day ' || fp_dayid || ' and TOM = 1';
            insert into customerdeliveryday
              (customer_id, day_id, timestamp, systemuser_id, deliverytype, typeofmerchadise)
            values
              (fp_customerid, fp_dayid, sysdate, gv_defaultuserid, old_deliverytype, 1);
            --copy deliverydays as well
            for xd in (select t.department_id
                       from   department t
                       where  t.customer_id = fp_customerid
                       and    not exists (select 1
                               from   distributionpoint dp
                               where  dp.department_id = t.department_id
                               and    (dp.deliverypoint_id = fp_deliverypointid or
                                     fp_deliverypointid is null))
                             -- to not exists a route stop for that day already and the department should not be inactivate
                       and    not exists (select 1
                               from   routestop rs, distributionpoint dp, route r
                               where  dp.department_id = t.department_id
                               and    (dp.deliverypoint_id = rs.deliverypoint_id or
                                     rs.deliverypoint_id is null)
                               and    rs.route_id = r.route_id
                               and    rs.customer_id = t.customer_id
                               and    r.drivingday_id = fp_dayid)) loop
              begin
                insert into deliveryday
                  (day_id, department_id, systemuser_id, timestamp)
                values
                  (fp_dayid, xd.department_id, gv_defaultuserid, sysdate);
              exception
                when dup_val_on_index then
                  null;
              end;
            end loop; --xd
            for xd in (select t.department_id
                       from   department t
                       where  t.customer_id = fp_customerid
                       minus
                       select t.department_id
                       from   department t
                       where  t.customer_id = fp_customerid
                       and    not exists (select 1
                               from   distributionpoint dp
                               where  dp.department_id = t.department_id
                               and    (dp.deliverypoint_id = fp_deliverypointid or
                                     fp_deliverypointid is null))
                             -- to not exists a route stop for that day already and the department should not be inactivate
                       and    not exists (select 1
                               from   routestop rs, distributionpoint dp, route r
                               where  dp.department_id = t.department_id
                               and    (dp.deliverypoint_id = rs.deliverypoint_id or
                                     rs.deliverypoint_id is null)
                               and    rs.route_id = r.route_id
                               and    rs.customer_id = t.customer_id
                               and    r.drivingday_id = fp_dayid)) loop
              begin
                delete from deliveryday
                where  day_id = fp_dayid
                and    department_id = xd.department_id;
              exception
                when others then
                  null;
              end;
            end loop; --xd
            begin
              --keep the same departments delivery by inserting in destination day a record in deliveriday
              for xd in (select t.department_id
                         from   department t, deliveryday dd
                         where  t.customer_id = fp_customerid
                         and    t.department_id = dd.department_id
                         and    dd.day_id = fp_olddayid) loop
                begin
                  insert into deliveryday
                    (day_id, department_id, systemuser_id, timestamp)
                  values
                    (fp_dayid, xd.department_id, gv_defaultuserid, sysdate);
                exception
                  when dup_val_on_index then
                    null;
                end;
              end loop; --xd
            exception
              when no_data_found then
                null;
            end;
          end if;
        end;
    end;
    if to_deliverytype is not null then
      gv_message := 'move_stop to_deliverytype = ' || to_deliverytype || ' for day ' || fp_dayid ||
                    ' and TOM = 1';
      if (old_deliverytype = 'B' and to_deliverytype <> 'B') or
         (old_deliverytype = 'D' and to_deliverytype = 'P') then
        update customerdeliveryday
        set    timestamp = sysdate, systemuser_id = gv_defaultuserid,
               deliverytype = old_deliverytype
        where  customerdeliveryday.customer_id = fp_customerid
        and    customerdeliveryday.day_id = fp_dayid
        and    customerdeliveryday.typeofmerchadise = 1;
      end if;
    end if;
    -- garments days
    begin
      old_deliverytype := null;
      select customerdeliveryday.deliverytype
      into   old_deliverytype
      from   customerdeliveryday
      where  customerdeliveryday.customer_id = fp_customerid
      and    customerdeliveryday.day_id = fp_olddayid
      and    customerdeliveryday.typeofmerchadise = 2;
    exception
      when no_data_found then
        old_deliverytype := null;
    end;
    gv_message := 'move_stop old_deliverytype=' || old_deliverytype || ' for day ' || fp_olddayid ||
                  ' and TOM = 2';
    begin
      to_deliverytype := null;
      select customerdeliveryday.deliverytype
      into   to_deliverytype
      from   customerdeliveryday
      where  customerdeliveryday.customer_id = fp_customerid
      and    customerdeliveryday.day_id = fp_dayid
      and    customerdeliveryday.typeofmerchadise = 2;
    exception
      when no_data_found then
        begin
          if old_deliverytype is not null then
            gv_message := 'move_stop insert line in customer dv days for ' || old_deliverytype ||
                          ' for day ' || fp_dayid || ' and TOM = 2';
            insert into customerdeliveryday
              (customer_id, day_id, timestamp, systemuser_id, deliverytype, typeofmerchadise)
            values
              (fp_customerid, fp_dayid, sysdate, gv_defaultuserid, old_deliverytype, 2);
          end if;
        end;
    end;
    if to_deliverytype is not null then
      gv_message := 'move_stop to_deliverytype = ' || to_deliverytype || ' for day ' || fp_dayid ||
                    ' and TOM = 2';
      if (old_deliverytype = 'B' and to_deliverytype <> 'B') or
         (old_deliverytype = 'D' and to_deliverytype = 'P') then
        update customerdeliveryday
        set    timestamp = sysdate, systemuser_id = gv_defaultuserid,
               deliverytype = old_deliverytype
        where  customerdeliveryday.customer_id = fp_customerid
        and    customerdeliveryday.day_id = fp_dayid
        and    customerdeliveryday.typeofmerchadise = 2;
      end if;
    end if;
    -- residential days
    begin
      old_deliverytype := null;
      select customerdeliveryday.deliverytype
      into   old_deliverytype
      from   customerdeliveryday
      where  customerdeliveryday.customer_id = fp_customerid
      and    customerdeliveryday.day_id = fp_olddayid
      and    customerdeliveryday.typeofmerchadise = 5;
    exception
      when no_data_found then
        old_deliverytype := null;
    end;
    gv_message := 'move_stop old_deliverytype=' || old_deliverytype || ' for day ' || fp_olddayid ||
                  ' and TOM = 5';
    begin
      to_deliverytype := null;
      select customerdeliveryday.deliverytype
      into   to_deliverytype
      from   customerdeliveryday
      where  customerdeliveryday.customer_id = fp_customerid
      and    customerdeliveryday.day_id = fp_dayid
      and    customerdeliveryday.typeofmerchadise = 5;
    exception
      when no_data_found then
        begin
          if old_deliverytype is not null then
            gv_message := 'move_stop insert line in customer dv days for ' || old_deliverytype ||
                          ' for day ' || fp_dayid || ' and TOM = 5';
            insert into customerdeliveryday
              (customer_id, day_id, timestamp, systemuser_id, deliverytype, typeofmerchadise)
            values
              (fp_customerid, fp_dayid, sysdate, gv_defaultuserid, old_deliverytype, 5);
          end if;
        end;
    end;
    if to_deliverytype is not null then
      gv_message := 'move_stop to_deliverytype = ' || to_deliverytype || ' for day ' || fp_dayid ||
                    ' and TOM = 5';
      if (old_deliverytype = 'B' and to_deliverytype <> 'B') or
         (old_deliverytype = 'D' and to_deliverytype = 'P') then
        update customerdeliveryday
        set    timestamp = sysdate, systemuser_id = gv_defaultuserid,
               deliverytype = old_deliverytype
        where  customerdeliveryday.customer_id = fp_customerid
        and    customerdeliveryday.day_id = fp_dayid
        and    customerdeliveryday.typeofmerchadise = 5;
      end if;
    end if;
  end add_customer_deldays_movestop;

  procedure add_customer_delscheme_days(fp_customerid       in customer.customer_id%type,
                                        fp_deliveryschemeid in deliveryscheme.deliveryscheme_id%type,
                                        fp_dayid            in customerdeliveryscheme.day_id%type) is
    lv_numberofwearers number;
  begin
    --add_customer_deliveryscheme_and_days
    gv_message := 'add_customer_deliveryscheme_and_days for customerID ' || fp_customerid ||
                  ' deliveryschemeID ' || fp_deliveryschemeid || ' day ' || fp_dayid;
    --try to add, if it already exists no problem
    begin
      insert into CUSTOMERDELIVERYSCHEME
        (customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
      values
        (fp_customerid, fp_deliveryschemeid, fp_dayid, gv_defaultuserid, sysdate);
    exception
      when dup_val_on_index then
        update customerdeliveryscheme ds
        set    ds.active = 'Y',
               systemuser_id = gv_defaultuserid,
               timestamp = sysdate
        where  ds.active <> 'Y'
        and    ds.customer_id = fp_customerid
        and    ds.deliveryscheme_id = fp_deliveryschemeid
        and    ds.day_id = fp_dayid;
    end;
    --add to customer deliveryday, for linnen
    begin
      insert into CUSTOMERDELIVERYDAY
        (customer_id, day_id, timestamp, systemuser_id, deliverytype, typeofmerchadise)
      values
        (fp_customerid, fp_dayid, sysdate, gv_defaultuserid, 'D', 1);
    exception
      when dup_val_on_index then
        null;
    end;
    --add if need be customer deliveryday, for garments
    select count(1) into lv_numberofwearers from wearer w where w.customer_id = fp_customerid;
    if lv_numberofwearers >= 1 then
      begin
        insert into CUSTOMERDELIVERYDAY
          (customer_id, day_id, timestamp, systemuser_id, deliverytype, typeofmerchadise)
        values
          (fp_customerid, fp_dayid, sysdate, gv_defaultuserid, 'D', 2);
      exception
        when dup_val_on_index then
          null;
      end;
    end if;
  end add_customer_delscheme_days;

  procedure inactivate_stop_routcontrol(fp_routeid         in route.route_id%type,
                                        fp_routestopid     in routestop.routestop_id%type,
                                        fp_changestartdate in routechangetransaction.changestartdate%type) is
    lv_routealreadypackedcount number;
  begin
    --inactivate_stop_routcontrol
    gv_message := 'inactivate_stop_routcontrol';
    --check if stop is packed already
    select count(1)
    into   lv_routealreadypackedcount
    from   routecontrol rc, stop st
    where  rc.route_id = fp_routeid
    and    rc.drivingdate >= fp_changestartdate
    and    rc.routecontrol_id = st.routecontrol_id
    and    st.routestop_id = fp_routestopid
    and    (rc.packingstatus >= 3 or gv_ssmovedeliveredstop = 1)
    and    st.status not in (1, 2, 3); --not cancelled/moved out/moved in
    if lv_routealreadypackedcount > 0 then
      gv_message := 'Routestop is being packed / delivered, stop will not be cancelled';
      raise ge_general;
    end if;
    --cancel stops from route status control
    for xs in (select st.stop_id
               from   routecontrol rc, stop st
               where  rc.route_id = fp_routeid
               and    (rc.packingstatus in (1, 2) or gv_ssmovedeliveredstop = 1)
               and    rc.drivingdate > fp_changestartdate
               and    rc.invoicesgenerated = 1 --cancel only when no invoice
               and    st.routecontrol_id = rc.routecontrol_id
               and    st.routestop_id = fp_routestopid) loop
      begin
        --update stop make cancelled
        update stop st
        set    st.status = 1, st.systemuser_id = gv_defaultuserid, st.timestamp = sysdate
        where  st.stop_id = xs.stop_id;
        --update all packing notes, remove all stop/route/routecontrol id's set to null
        update packingnote pk
        set    pk.routecontrol_id = null, pk.route_id = null, pk.stop_id = null, pk.stopnumber = 0,
               pk.systemuser_id = gv_defaultuserid, pk.timestamp = sysdate
        where  pk.stop_id = xs.stop_id;
        --update all client delivery notes, remove all stop/route/routecontrol id's set to null
        update clientdeliverynote cdn
        set    cdn.routecontrol_id = null, cdn.route_id = null, cdn.stop_id = null,
               cdn.stopnumber = 0, cdn.systemuser_id = gv_defaultuserid, cdn.timestamp = sysdate
        where  cdn.stop_id = xs.stop_id;
        --update straightevenreturn
        update straightevenreturn sr
        set    sr.stop_id = null, sr.systemuser_id = gv_defaultuserid, sr.timestamp = sysdate
        where  sr.stop_id = xs.stop_id;
      end;
    end loop; --xs
  end inactivate_stop_routcontrol;

  procedure add_stop_to_routecontrol(fp_newrouteid          in route.route_id%type,
                                     fp_newstopnumber       in routestop.stopnumber%type,
                                     fp_newroutestopid      in routestop.routestop_id%type,
                                     fp_customerid          in customer.customer_id%type,
                                     fp_changestartdate     in routechangetransaction.changestartdate%type,
                                     fp_cancel_oldstopid_yn in varchar2 default 'N', --parameters below only needed for cancel
                                     fp_oldroutestopid      in routestop.routestop_id%type,
                                     fp_oldstopnumber       in stop.stopnumber%type,
                                     fp_canceldate          in date,
                                     fp_deliverypointid     in routechangetransaction.deliverypoint_id%type) is
    lv_stoprec                stop%rowtype;
    lv_stopstatusxs           stop.status%type;
    lv_daycheck               number;
    lv_to_routecontrolid      routecontrol.routecontrol_id%type;
    lv_routecontrol_has_stops boolean;
    lv_duplicatedeliveryvarid number;
  begin
    --add_stop_to_routecontrol
    gv_message := 'add_stop_to_routecontrol';
    --get / check if route control needs to be updated
    for xr in (select rc.routecontrol_id, rc.drivingdate, rc.packingstatus, rc.route_id
               from   routecontrol rc
               where  rc.route_id = fp_newrouteid
               and    (rc.packingstatus < 3 or gv_databasecode = 'NLI' or gv_databasecode = 'SMG' or
                     gv_ssmovedeliveredstop = 1)
               and    rc.drivingdate >= trunc(fp_changestartdate)) loop
      begin
        lv_routecontrol_has_stops := false;
        --update the stops with newroutestopid if found a moved-in stop having also oldroutestopid
        for y in (select s.stop_id, s.stopnumber
                  from   stop s
                  where  s.routecontrol_id = xr.routecontrol_id
                  and    s.customer_link_id = fp_customerid
                        --and    s.stopnumber = case when (gv_databasecode <> 'SMG') then fp_newstopnumber else s.stopnumber end
                  and    s.status not in (1, 2)) loop
          begin
            --only SMG consider also the other stops than newstopnumber
            --(and this case should be logged in abslog table)
            if (y.stopnumber <> fp_newstopnumber) then
              if (gv_databasecode <> 'SMG') then
                continue;
              else
                write_log('SMG Exception: The StopNumber[' || to_char(fp_newstopnumber) ||
                          '] not added to {RC[' || to_char(xr.routecontrol_id) || '] /R[' ||
                          to_char(xr.route_id) || '] /C[' || to_char(fp_customerid) ||
                          ']} (because already existing stop {SN[' || to_char(y.stopnumber) ||
                          '] /S[' || to_char(y.stop_id) || ']} on same route)!', 2);
              end if;
            end if;
            lv_routecontrol_has_stops := true;
            update stop
            set    routestop_id = fp_newroutestopid,
                   systemuser_id = gv_defaultuserid,
                   timestamp = sysdate
            where  routestop_id = fp_oldroutestopid
            and    stop_id = y.stop_id
            and    status = 3;
          end;
        end loop; --y
        --if no stops found then insert one
        if (not lv_routecontrol_has_stops) then
          begin
            if (gv_databasecode = 'NLI' or gv_databasecode = 'SMG') and xr.packingstatus = 4 then
              update routecontrol rc
              set    rc.packingstatus = 3,
                     rc.systemuser_id = gv_defaultuserid,
                     rc.timestamp = sysdate
              where  rc.routecontrol_id = xr.routecontrol_id
              and    xr.packingstatus >= 4;
            end if;
            --check the route stop freq. related to the route frequency, read driving day
            for z in (select ds.frequency, ds.offsetweek
                      from   deliveryschemesperstop de, deliveryscheme ds
                      where  de.routestop_id = fp_newroutestopid
                      and    de.deliveryscheme_id = ds.deliveryscheme_id
                      group  by ds.frequency, ds.offsetweek
                      order  by ds.frequency, ds.offsetweek) loop
              begin
                select mod((getdateforweekyear(getabsdaynumber(xr.drivingdate),
                                                getisoweek(xr.drivingdate),
                                                getyearofweek(xr.drivingdate)) -
                            getdateforweekyear(getabsdaynumber(xr.drivingdate), ds.referenceweek,
                                                ds.referenceyear)), (z.frequency * 7)) -
                        (z.offsetweek * 7)
                into   lv_daycheck
                from   databasesettings ds;
                if lv_daycheck = 0 then
                  begin
                    --insert into stop
                    init_stop(lv_stoprec);
                    lv_stoprec.stopnumber       := fp_newstopnumber;
                    lv_stoprec.routecontrol_id  := xr.routecontrol_id;
                    lv_stoprec.routestop_id     := fp_newroutestopid;
                    lv_stoprec.customer_link_id := fp_customerid;
                    lv_stoprec.deliverypoint_id := fp_deliverypointid;
                    gv_message                  := 'insert into stop';
                    insert into stop values lv_stoprec;
                    exit;
                  end;
                end if;
              end;
            end loop; --z
          end;
        end if;
      end;
    end loop; --xr
    --check if old stop needs to be cancelled
    if fp_cancel_oldstopid_yn = 'Y' then
      begin
        --find the old stops to be cancelled
        for xs in (select st.stop_id oldstopid, st.deliverypoint_id, rc.routecontrol_id,
                          rc.drivingdate
                   from   stop st, routecontrol rc
                   where  st.routestop_id = fp_oldroutestopid
                   and    st.stopnumber = fp_oldstopnumber
                   and    st.customer_link_id = fp_customerid
                   and    st.routecontrol_id = rc.routecontrol_id
                   and    (gv_databasecode = 'NLI' or --todo 22196 EMG, still cancel for NLI
                         (rc.packingstatus < 3 and rc.invoicesgenerated = 1) or
                         gv_ssmovedeliveredstop = 1) --cancel only when no invoice
                   and    rc.drivingdate > nvl(fp_canceldate, fp_changestartdate)) loop
          begin
            --cancel stop
            select st.status into lv_stopstatusxs from stop st where st.stop_id = xs.oldstopid;
            if lv_stopstatusxs not in (1, 2, 3) then
              update stop st
              set    st.status = 1, st.systemuser_id = gv_defaultuserid, st.timestamp = sysdate
              where  st.stop_id = xs.oldstopid;
            end if;
            --find new stop/routecontrol
            select max(rc.routecontrol_id),
                   max((select max(st.stop_id)
                        from   stop st
                        where  st.routecontrol_id = rc.routecontrol_id
                        and    st.customer_link_id = fp_customerid
                        and    st.routestop_id = fp_newroutestopid))
            into   lv_to_routecontrolid, lv_stoprec.stop_id
            from   routecontrol rc
            where  rc.route_id = fp_newrouteid
            and    rc.drivingdate = fp_changestartdate;
            --update ALL delivery variations linked to the packing notes that had old stopid
            --in case of an unique index exception, add DVs to an existing one
            begin
              update deliveryvariations dv
              set    dv.deliverydate = fp_changestartdate, dv.systemuser_id = gv_defaultuserid,
                     dv.timestamp = sysdate
              where  dv.processed = 'Y'
              and    dv.deliverydate <> fp_changestartdate
              and    dv.packingnote_id in
                     (select pk.packingnote_id from packingnote pk where pk.stop_id = xs.oldstopid);
            exception
              when dup_val_on_index then
                --try to update DVs one by one and sum them up when needed
                lv_duplicateDeliveryVarId := updateDuplicateDVAmount (fp_changestartdate, xs.oldstopid, 'Y', fp_oldroutestopid);
            end;
            --update ALL packing notes with new stopid that had old stopid
            update packingnote pk
            set    pk.routecontrol_id = lv_to_routecontrolid, pk.stop_id = lv_stoprec.stop_id,
                   pk.stopnumber = fp_newstopnumber, pk.route_id = fp_newrouteid,
                   pk.deliverydate = fp_changestartdate,
                   pk.systemuser_id = gv_defaultuserid, pk.timestamp = sysdate
            where  pk.stop_id = xs.oldstopid;
            --update ALL client delivery notes with new stopid that had old stopid
            update clientdeliverynote cdn
            set    cdn.routecontrol_id = lv_to_routecontrolid, cdn.stop_id = lv_stoprec.stop_id,
                   cdn.stopnumber = fp_newstopnumber, cdn.route_id = fp_newrouteid,
                   cdn.deliverydate = fp_changestartdate,
                   cdn.systemuser_id = gv_defaultuserid, cdn.timestamp = sysdate
            where  cdn.stop_id = xs.oldstopid;
            -- update ALL delivery variations not processed linked to an old stopid
            begin

            update deliveryvariations dv
            set    dv.deliverydate = fp_changestartdate,
            			 dv.systemuser_id = gv_defaultuserid,
                   dv.timestamp = sysdate
            where  dv.processed = 'N'
            and    dv.origin <> 3 --Manual Entry
            and    dv.deliverydate = xs.drivingdate
            and    exists
             (select 1
                    from   stop s
                    join   department t
                    on     t.customer_id = s.customer_link_id
                    join   distributionpoint dp
                    on     dp.department_id = t.department_id
                    where  s.stop_id = xs.oldstopid
                    and    dp.distributionpoint_id = dv.distributionpoint_id
                    and    (s.deliverypoint_id = dp.deliverypoint_id or
                          (s.deliverypoint_id is null and s.customer_link_id = t.customer_id)));

            exception
              when dup_val_on_index then
                --try to update DVs suumarizing the amounts for existing records
                lv_duplicateDeliveryVarId := updateDuplicateDVAmount(fp_changestartdate, xs.oldstopid, 'N', fp_oldroutestopid);
            end;

            --update ALL straightevenreturn with new stopid that had old stopid
            update straightevenreturn str
            set    str.stop_id = lv_stoprec.stop_id,
            		   str.systemuser_id = gv_defaultuserid,
                   str.timestamp = sysdate
            where  str.stop_id = xs.oldstopid;
            --update deliveryscans
            update deliveryscan ds
            set    ds.routecontrol_id = lv_to_routecontrolid,
                   ds.planneddeliverydate = fp_changestartdate,
                   ds.systemuser_id = gv_defaultuserid,
                   ds.timestamp = sysdate
            where  ds.routecontrol_id = xs.routecontrol_id
            and    ds.packingnote_id is null
            and    ds.customer_link_id = fp_customerid
            and    (xs.deliverypoint_id is null or ds.deliverypoint_link_id = xs.deliverypoint_id);
            --update ALL bagtags with new stopid that had old stopid
            update bagtag bt
            set    bt.stop_id = lv_stoprec.stop_id, bt.route_id = fp_newrouteid,
                   bt.planneddeliverydate = fp_changestartdate,
                   bt.systemuser_id = gv_defaultuserid,
                   bt.timestamp = sysdate
            where  bt.stop_id = xs.oldstopid;
            --update uniqueitemroute for system setting DELIVERYDETERMINATION = 1
            if gv_deliverydetermination = 1 then
              begin
                update uniqueitem ui
                set    ui.planneddeliverydate = fp_changestartdate,
                       ui.systemuser_id = gv_defaultuserid, ui.timestamp = sysdate
                where  ui.uniqueitem_id =
                       (select uir.uniqueitem_id
                        from   uniqueitemroute uir
                        where  uir.plannedoutstop_id = xs.oldstopid);
                update uniqueitemroute uir
                set    uir.plannedoutroute_id = fp_newrouteid,
                       uir.plannedoutdate = fp_changestartdate,
                       uir.plannedoutstop_id = lv_stoprec.stop_id,
                       uir.systemuser_id = gv_defaultuserid, uir.timestamp = sysdate
                where  uir.plannedoutstop_id = xs.oldstopid;
                update uniqueitemroute uir
                set    uir.lastoutroute_id = fp_newrouteid, uir.lastoutdate = fp_changestartdate,
                       uir.lastoutstop_id = lv_stoprec.stop_id,
                       uir.systemuser_id = gv_defaultuserid,
                       uir.timestamp = sysdate
                where  uir.lastoutstop_id = xs.oldstopid
                and    exists (select 1
                        from   deliveryscan ds
                        left   outer join packingnote pn
                        on     ds.packingnote_id = pn.packingnote_id
                        where  ds.uniqueitem_link_id = uir.uniqueitem_id
                        and    pn.status_id = (select status_id
                                               from   status
                                               where  statustype = 'PN'
                                               and    code = '10'));
              end;
            end if;
          end;
        end loop; --xs
      end;
    end if; --fp_cancel_oldstopid_yn = 'Y'
  end add_stop_to_routecontrol;

  procedure copy_child_records(fp_oldstandarddeliveryid in standarddelivery.standarddelivery_id%type,
                               fp_newstandarddeliveryid in standarddelivery.standarddelivery_id%type) is
    lv_temporarydeliverystoprec temporarydeliverystop%rowtype;
    lv_temporaryrentstoprec     temporaryrentstop%rowtype;
  begin
    --copy_child_records
    gv_message := 'copy_child_records';
    for xf in (select *
               from   temporarydeliverystop td
               where  td.standarddeliveryline_id = fp_oldstandarddeliveryid) loop
      begin
        --temporarydeliverystop
        lv_temporarydeliverystoprec                         := xf;
        lv_temporarydeliverystoprec.standarddeliveryline_id := fp_newstandarddeliveryid;
        lv_temporarydeliverystoprec.systemuser_id           := gv_defaultuserid;
        lv_temporarydeliverystoprec.timestamp               := sysdate;
        select seq_temporarydeliverystop.nextval
        into   lv_temporarydeliverystoprec.temporarydeliverystop_id
        from   dual;
        gv_message := 'insert into temporarydeliverystop';
        insert into temporarydeliverystop values lv_temporarydeliverystoprec;
      end;
    end loop; --xf
    for xg in (select *
               from   temporaryrentstop tr
               where  tr.standarddeliveryline_id = fp_oldstandarddeliveryid) loop
      begin
        --temporaryrentstop
        lv_temporaryrentstoprec                         := xg;
        lv_temporaryrentstoprec.standarddeliveryline_id := fp_newstandarddeliveryid;
        lv_temporaryrentstoprec.systemuser_id           := gv_defaultuserid;
        lv_temporaryrentstoprec.timestamp               := sysdate;
        select seq_temporaryrentstop.nextval
        into   lv_temporaryrentstoprec.temporaryrentstop_id
        from   dual;
        gv_message := 'insert into temporaryrentstop';
        insert into temporaryrentstop values lv_temporaryrentstoprec;
      end;
    end loop; --xg
  end copy_child_records;

  procedure putenddate2standarddeliveries(fp_customerid      in customer.customer_id%type,
                                          fp_deliverypointid in deliverypoint.deliverypoint_id%type,
                                          fp_drivingday_id   in day.day_id%type,
                                          fp_changestartdate in date) is
    lv_someotherstopscount         number;
    lv_otherdeliverydayscount      number;
    lv_standarddeliverylinerec     standarddeliveryline%rowtype;
    lv_standarddeliveryamountrec   standarddeliveryamount%rowtype;
    lv_standarddeliveryamntdtlsrec standarddeliveryamntdetails%rowtype;
  begin
    --putenddate2standarddeliveries
    gv_message := 'put end date to standardeliveries';
    --if there is no other stop for this customer/deliverypoint on this driving day
    --then standard deliveries must be stopped as well
    select count(1)
    into   lv_someotherstopscount
    from   routestop rs, route r
    where  rs.customer_id = fp_customerid
    and    (rs.deliverypoint_id = fp_deliverypointid or
          (fp_deliverypointid is null and rs.deliverypoint_id is null))
    and    rs.route_id = r.route_id
    and    r.drivingday_id = fp_drivingday_id
    and    rs.dateinactive > fp_changestartdate;
    --if no other stops then stop standard deliveries
    if lv_someotherstopscount = 0 then
      for xd in (select stl.*
                 from   standarddelivery std, standarddeliveryline stl, distributionpoint dp,
                        department t
                 where  std.standarddelivery_id = stl.standarddelivery_id
                 and    std.distributionpoint_id = dp.distributionpoint_id
                 and    dp.department_id = t.department_id
                 and    t.customer_id = fp_customerid
                 and    (dp.deliverypoint_id = fp_deliverypointid or
                       (fp_deliverypointid is null and dp.deliverypoint_id is null))
                 and    stl.dateinactive > fp_changestartdate
                 and    stl.dateactive <= stl.dateinactive
                 and    exists
                  (select 1
                         from   standarddeliveryamount sta
                         where  sta.standarddeliveryline_id = stl.standarddeliveryline_id
                         and    sta.day_id = fp_drivingday_id)) loop
        begin
          --check for other deliverydays
          select count(1)
          into   lv_otherdeliverydayscount
          from   standarddeliveryamount a
          where  a.standarddeliveryline_id = xd.standarddeliveryline_id
          and    a.day_id <> fp_drivingday_id;
          --if they exist
          if lv_otherdeliverydayscount > 0 then
            --copy standarddeliveryline
            lv_standarddeliverylinerec := xd;
            select seq_standarddeliveryline.nextval
            into   lv_standarddeliverylinerec.standarddeliveryline_id
            from   dual;
            --use max date active
            if lv_standarddeliverylinerec.dateactive < fp_changestartdate then
              begin
                lv_standarddeliverylinerec.dateactive := fp_changestartdate;
                --check if we really need a new record
                if lv_standarddeliverylinerec.dateactive >= lv_standarddeliverylinerec.dateinactive then
                  raise ge_general; --no new record needed
                end if;
              end;
            end if;
            lv_standarddeliverylinerec.systemuser_id := gv_defaultuserid;
            lv_standarddeliverylinerec.timestamp     := sysdate;
            --write new standarddeliveryline
            gv_message := 'insert standarddeliveryline';
            insert into standarddeliveryline values lv_standarddeliverylinerec;
            --create cpl transition record
            create_cpltransition(xd.standarddeliveryline_id,
                                 lv_standarddeliverylinerec.standarddeliveryline_id);
            --copy child records
            for xe in (select *
                       from   standarddeliveryamount sa
                       where  sa.standarddeliveryline_id = xd.standarddeliveryline_id
                       and    sa.day_id <> fp_drivingday_id) loop
              begin
                --standarddeliveryamount
                lv_standarddeliveryamountrec                         := xe;
                lv_standarddeliveryamountrec.standarddeliveryline_id := lv_standarddeliverylinerec.standarddeliveryline_id;
                lv_standarddeliveryamountrec.systemuser_id           := gv_defaultuserid;
                lv_standarddeliveryamountrec.timestamp               := sysdate;
                gv_message                                           := 'insert standarddeliveryamount';
                insert into standarddeliveryamount values lv_standarddeliveryamountrec;
              end;
            end loop; --xe
            for xh in (select *
                       from   standarddeliveryamntdetails sda
                       where  sda.standarddeliveryline_id = xd.standarddeliveryline_id
                       and    sda.day_id <> fp_drivingday_id) loop
              begin
                lv_standarddeliveryamntdtlsrec                         := xh;
                lv_standarddeliveryamntdtlsrec.standarddeliveryline_id := lv_standarddeliverylinerec.standarddeliveryline_id;
                lv_standarddeliveryamntdtlsrec.systemuser_id           := gv_defaultuserid;
                lv_standarddeliveryamntdtlsrec.timestamp               := sysdate;
                select seq_stddeliveryamntdetails.nextval
                into   lv_standarddeliveryamntdtlsrec.standarddeliveryamntdetails_id
                from   dual;
                gv_message := 'insert into standarddeliveryamntdetails';
                insert into standarddeliveryamntdetails values lv_standarddeliveryamntdtlsrec;
              end;
            end loop; --xh
            copy_child_records(xd.standarddeliveryline_id,
                               lv_standarddeliverylinerec.standarddeliveryline_id);
          end if;
        exception
          when ge_general then
            null;
        end;
      end loop; --xd
    end if;
  end putenddate2standarddeliveries;

  procedure insert_standarddeliveryline(fp_standarddeliverylinerec   in standarddeliveryline%rowtype,
                                        fp_drivingday                in routechangetransaction.drivingday_id%type,
                                        fp_newdrivingday             in routechangetransaction.newdrivingday_id%type,
                                        fp_changedate                in routechangetransaction.changestartdate%type,
                                        fp_new_deliveryschemeid      in deliveryscheme.deliveryscheme_id%type,
                                        fp_routechangetransactionrec in routechangetransaction%rowtype) is
    lv_newstandarddeliverylinerec  standarddeliveryline%rowtype;
    lv_standarddeliveryamountrec   standarddeliveryamount%rowtype;
    lv_standarddeliveryamntdtlsrec standarddeliveryamntdetails%rowtype;
    lv_countstdamountlines         number;
    lv_alreadyexist_new_sdl_id			number;
  begin
    --copy standarddeliveryline
    lv_newstandarddeliverylinerec := fp_standarddeliverylinerec;
    --check if already exists the SDL for the new deliveryschemeid
    --and decide if duplicate the old deliveryschemeid SDL
    --or just update fro the existing new deliveryschemeid SDL
    begin
      lv_alreadyexist_new_sdl_id := 0;
      select sdl.standarddeliveryline_id
        into lv_alreadyexist_new_sdl_id
        from standarddeliveryline sdl
       where sdl.standarddelivery_id = lv_newstandarddeliverylinerec.standarddelivery_id
         and sdl.productlistperdistribpoint_id = lv_newstandarddeliverylinerec.productlistperdistribpoint_id
         and sdl.deliveryscheme_id = fp_new_deliveryschemeid
         and sdl.dateactive = fp_changedate;
    exception
      when NO_DATA_FOUND then
        lv_alreadyexist_new_sdl_id := 0;
    end;
		--if the SDL for the new deliveryschemeid doesn't exist
		if (lv_alreadyexist_new_sdl_id = 0) then
    select seq_standarddeliveryline.nextval
    into   lv_newstandarddeliverylinerec.standarddeliveryline_id
    from   dual;
    else
      lv_newstandarddeliverylinerec.standarddeliveryline_id := lv_alreadyexist_new_sdl_id;
    end if;

    lv_newstandarddeliverylinerec.deliveryscheme_id := fp_new_deliveryschemeid;
    --use max date active, when it was in the future keep that date
    if lv_newstandarddeliverylinerec.dateactive < fp_changedate and
       (lv_newstandarddeliverylinerec.renewal = 'N' or
       (lv_newstandarddeliverylinerec.renewal = 'Y' and
       fp_changedate between lv_newstandarddeliverylinerec.dateactive and
       lv_newstandarddeliverylinerec.dateinactive)) then
      lv_newstandarddeliverylinerec.dateactive := fp_changedate;
    end if;
    --check if we really need a new record
    if lv_newstandarddeliverylinerec.dateactive > lv_newstandarddeliverylinerec.dateinactive then
      raise ge_general; --no new record needed
    end if;
    lv_newstandarddeliverylinerec.systemuser_id := gv_defaultuserid;
    lv_newstandarddeliverylinerec.timestamp     := sysdate;
    --insert a new SDL record only if doesn't exist
    if (lv_alreadyexist_new_sdl_id = 0) then
    gv_message := 'insert into standarddeliveryline';
    insert into standarddeliveryline values lv_newstandarddeliverylinerec;
		end if;

    --create cpltransition
    create_cpltransition(fp_standarddeliverylinerec.standarddeliveryline_id,
                         lv_newstandarddeliverylinerec.standarddeliveryline_id);
    begin
      insert into customerdeliveryscheme
        (customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp, active)
      values
        (fp_routechangetransactionrec.customer_id, lv_newstandarddeliverylinerec.deliveryscheme_id,
         fp_newdrivingday, gv_defaultuserid, sysdate, 'Y');
    exception
      when dup_val_on_index then
        null;
    end;
    --insert or update for standarddeliveryamount table
    for xe in (select *
               from   standarddeliveryamount sa
               where  sa.standarddeliveryline_id =
                      fp_standarddeliverylinerec.standarddeliveryline_id
               and    sa.day_id = fp_drivingday) loop
      begin
        --standarddeliveryamount
        lv_standarddeliveryamountrec                         := xe;
        lv_standarddeliveryamountrec.standarddeliveryline_id := lv_newstandarddeliverylinerec.standarddeliveryline_id;
        lv_standarddeliveryamountrec.day_id                  := nvl(fp_newdrivingday, fp_drivingday);
        lv_standarddeliveryamountrec.systemuser_id           := gv_defaultuserid;
        lv_standarddeliveryamountrec.timestamp               := sysdate;
        gv_message                                           := 'insert into standarddeliveryamount';
        insert into standarddeliveryamount values lv_standarddeliveryamountrec;
      exception
        when dup_val_on_index then
          gv_message := 'update standarddeliveryamount';
          update standarddeliveryamount sa
          set    sa.amount = sa.amount + xe.amount
          where  sa.standarddeliveryline_id = lv_newstandarddeliverylinerec.standarddeliveryline_id
          and    sa.day_id = fp_newdrivingday;
      end;
    end loop; --xe
    --insert or not for standarddeliveryamntdetails table
    for xh in (select *
               from   standarddeliveryamntdetails sda
               where  sda.standarddeliveryline_id =
                      fp_standarddeliverylinerec.standarddeliveryline_id
               and    sda.day_id = fp_drivingday) loop
      begin
        lv_standarddeliveryamntdtlsrec                         := xh;
        lv_standarddeliveryamntdtlsrec.standarddeliveryline_id := lv_newstandarddeliverylinerec.standarddeliveryline_id;
        lv_standarddeliveryamntdtlsrec.day_id                  := nvl(fp_newdrivingday, fp_drivingday);
        lv_standarddeliveryamntdtlsrec.systemuser_id           := gv_defaultuserid;
        lv_standarddeliveryamntdtlsrec.timestamp               := sysdate;
        select seq_stddeliveryamntdetails.nextval
        into   lv_standarddeliveryamntdtlsrec.standarddeliveryamntdetails_id
        from   dual;
        gv_message := 'insert into standarddeliveryamntdetails';
        insert into standarddeliveryamntdetails values lv_standarddeliveryamntdtlsrec;
      exception
        when dup_val_on_index then
          null;
      end;
    end loop; --xh
    --duplicate for temporary stop tables
    copy_child_records(fp_standarddeliverylinerec.standarddeliveryline_id,
                       lv_newstandarddeliverylinerec.standarddeliveryline_id);
    --count standarddeliveryamount lines
    select count(1)
    into   lv_countstdamountlines
    from   standarddeliveryamount sta
    where  sta.standarddeliveryline_id = fp_standarddeliverylinerec.standarddeliveryline_id;
    if lv_countstdamountlines > 1 then
      begin
        if (fp_standarddeliverylinerec.deliveryscheme_id <> fp_new_deliveryschemeid) then
          begin
            --new std del line needed for old del scheme
            select seq_standarddeliveryline.nextval
            into   lv_newstandarddeliverylinerec.standarddeliveryline_id
            from   dual;
            --then create a new std del line, for the old del scheme's
            lv_newstandarddeliverylinerec.deliveryscheme_id := fp_standarddeliverylinerec.deliveryscheme_id; --old del scheme
            --write record, for old delivery scheme
            gv_message := 'write record, for old delivery scheme';
            insert into standarddeliveryline values lv_newstandarddeliverylinerec;
            create_cpltransition(fp_standarddeliverylinerec.standarddeliveryline_id,
                                 lv_newstandarddeliverylinerec.standarddeliveryline_id);
            begin
              insert into customerdeliveryscheme
                (customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp, active)
              values
                (fp_routechangetransactionrec.customer_id,
                 lv_newstandarddeliverylinerec.deliveryscheme_id, fp_newdrivingday, gv_defaultuserid,
                 sysdate, 'Y');
            exception
              when dup_val_on_index then
                null;
            end;
          end;
        end if;
        --Now only copy for the other days then the new driving day!
        for xf in (select *
                   from   standarddeliveryamount sa
                   where  sa.standarddeliveryline_id =
                          fp_standarddeliverylinerec.standarddeliveryline_id
                   and    sa.day_id <> fp_drivingday) loop
          begin
            --standarddeliveryamount
            lv_standarddeliveryamountrec                         := xf;
            lv_standarddeliveryamountrec.standarddeliveryline_id := lv_newstandarddeliverylinerec.standarddeliveryline_id;
            lv_standarddeliveryamountrec.systemuser_id           := gv_defaultuserid;
            lv_standarddeliveryamountrec.timestamp               := sysdate;
            gv_message                                           := 'insert into standarddeliveryamount, for other days';
            insert into standarddeliveryamount values lv_standarddeliveryamountrec;
          exception
            when dup_val_on_index then
              gv_message := 'update standarddeliveryamount';
              update standarddeliveryamount sa
              set    sa.amount = sa.amount + xf.amount,
                     sa.systemuser_id = gv_defaultuserid,
                     sa.timestamp = sysdate
              where  sa.standarddeliveryline_id = lv_newstandarddeliverylinerec.standarddeliveryline_id
              and    sa.day_id = xf.day_id;
          end;
        end loop; --xf
        for xh in (select *
                   from   standarddeliveryamntdetails sda
                   where  sda.standarddeliveryline_id = fp_standarddeliverylinerec.standarddeliveryline_id
                   and    sda.day_id <> fp_drivingday) loop
          begin
            lv_standarddeliveryamntdtlsrec                         := xh;
            lv_standarddeliveryamntdtlsrec.standarddeliveryline_id := lv_newstandarddeliverylinerec.standarddeliveryline_id;
            lv_standarddeliveryamntdtlsrec.systemuser_id           := gv_defaultuserid;
            lv_standarddeliveryamntdtlsrec.timestamp               := sysdate;
            select seq_stddeliveryamntdetails.nextval
            into   lv_standarddeliveryamntdtlsrec.standarddeliveryamntdetails_id
            from   dual;
            gv_message := 'insert into standarddeliveryamntdetails, for other days';
            insert into standarddeliveryamntdetails values lv_standarddeliveryamntdtlsrec;
          end;
        end loop; --xh
      end;
    end if;
    --stop original standarddeliveryline
    update standarddeliveryline sl
    set    sl.dateinactive = fp_changedate - 1,
           sl.renewal = 'N',
				 /*sl.renewalstartdate = null, sl.renewalenddate = null,*/
    			 sl.systemuser_id = gv_defaultuserid,
           sl.timestamp = sysdate
    where  sl.standarddeliveryline_id = fp_standarddeliverylinerec.standarddeliveryline_id;
    ---Always update renewal
    update standarddeliveryline sta
    set    sta.renewalstddeliveryline_id = nvl((select max(cplt.newstandarddeliveryline_id)
                                                from   cpltransition cplt
                                                where  cplt.oldstandarddeliveryline_id = sta.renewalstddeliveryline_id),
                                                       sta.renewalstddeliveryline_id),
           sta.systemuser_id = gv_defaultuserid,
           sta.timestamp = sysdate
    where  sta.renewalstddeliveryline_id = fp_standarddeliverylinerec.standarddeliveryline_id;
    --delete wrong records
    delete from standarddeliveryline stl
    where  stl.standarddeliveryline_id = fp_standarddeliverylinerec.standarddeliveryline_id
    and    stl.dateactive > stl.dateinactive;
  exception
    when ge_general then
      null;
  end insert_standarddeliveryline;

  procedure copy_standarddeliverylines(fp_routechangetransactionrec in routechangetransaction%rowtype,
                                       fp_olddeliveryschemeid       deliveryscheme.deliveryscheme_id%type) is
    lv_intervalcheckmoveroute number;
    lv_sdl_start_date         date; ---start date for the new standard delivery line
  begin
    --determine interval, the minimum interval for the routestop
    select (nvl(min(ds.frequency), 1) * 7)
    into   lv_intervalcheckmoveroute
    from   deliveryschemesperstop dsp
    join   deliveryscheme ds
    on     ds.deliveryscheme_id = dsp.deliveryscheme_id
    where  dsp.routestop_id = fp_routechangetransactionrec.routestop_id;
    --determine new start date
    if gv_directstopchange = 1 then
      lv_sdl_start_date := trunc(sysdate);
    else
      lv_sdl_start_date := fp_routechangetransactionrec.oldroutelastdrivingdate +
                           lv_intervalcheckmoveroute;
      if lv_sdl_start_date > fp_routechangetransactionrec.changestartdate then
        begin
          lv_sdl_start_date := fp_routechangetransactionrec.changestartdate;
        end;
      end if;
    end if;
    --copy_standarddeliverylines
    gv_message := 'copy_standarddeliverylines';
    if (fp_routechangetransactionrec.drivingday_id <> fp_routechangetransactionrec.newdrivingday_id) or
       (nvl(fp_routechangetransactionrec.deliveryscheme_id, fp_olddeliveryschemeid) <>
       nvl(fp_routechangetransactionrec.newdeliveryscheme_id, fp_olddeliveryschemeid)) then
      begin
        for xg in (select sl.*
                   from   standarddelivery std, standarddeliveryline sl, distributionpoint dp,
                          department t, productlistperdistribpoint plpd, product p, customer c
                   where  std.standarddelivery_id = sl.standarddelivery_id
                   and    std.distributionpoint_id = dp.distributionpoint_id
                   and    dp.department_id = t.department_id
                   and    t.customer_id = fp_routechangetransactionrec.customer_id
                   and    (dp.deliverypoint_id = fp_routechangetransactionrec.deliverypoint_id or
                         fp_routechangetransactionrec.deliverypoint_id is null)
                   and    (sl.dateinactive is null or fp_routechangetransactionrec.changestartdate < case
                           when (sl.renewalstddeliveryline_id is null and sl.renewal = 'Y') then
                            nvl(sl.renewalenddate, sl.dateinactive)
                           else
                            sl.dateinactive
                         end)
                   and    sl.deliveryscheme_id =
                          nvl(fp_routechangetransactionrec.deliveryscheme_id, fp_olddeliveryschemeid) ---select only with right delscheme
                   and    plpd.productlistperdistribpoint_id = sl.productlistperdistribpoint_id
                   and    plpd.product_id = p.product_id
                   and    sl.dateactive <= sl.dateinactive
                   and    (p.productgroup_id = fp_routechangetransactionrec.productgroup_id or
                         fp_routechangetransactionrec.productgroup_id is null)
                   and    t.customer_id = c.customer_id
                   and    not (c.allocationmethod > 0 and t.bulkdeliveriesdept = 'N' and
                          t.excludefrombulkdelivery = 'N')
                   and    exists
                    (select 1
                           from   standarddeliveryamount sa
                           where  sa.standarddeliveryline_id = sl.standarddeliveryline_id
                           and    sa.day_id = fp_routechangetransactionrec.drivingday_id)
                   order  by sl.dateactive desc, sl.standarddeliveryline_id asc /*process the new lines first*/
                   ) loop
          begin
            gv_message := 'copy_standarddeliverylines';
            --copy the standarddelivery line
            insert_standarddeliveryline(xg,
                              fp_routechangetransactionrec.drivingday_id,
                                        fp_routechangetransactionrec.newdrivingday_id,
                                        lv_sdl_start_date,
                              nvl(fp_routechangetransactionrec.newdeliveryscheme_id,fp_olddeliveryschemeid),
                              fp_routechangetransactionrec);
          end;
        end loop; --xg
      end;
    end if;
  end copy_standarddeliverylines;

  procedure update_return_routes(fp_customer_id     in customer.customer_id%type,
                                 fp_routstopenddate routestop.dateinactive%type,
                                 fp_routeid         in route.route_id%type,
                                 fp_routenumber     in route.routenumber%type) is
    lv_rec_newroutestop routestop%rowtype;
  begin
    --update_return_routes
    gv_message := 'update_return_routes for customer id ' || fp_customer_id ||
                  ' routestop end date ' || to_char(fp_routstopenddate, 'DD-MM-YYYY') ||
                  ' route id ' || fp_routeid;
    for x in (select rs.*
              from   routestop rs
              where  rs.customer_id = fp_customer_id
              and    rs.returnroute_id = fp_routeid
              and    rs.dateinactive > fp_routstopenddate
              and    rs.route_id <> fp_routeid) loop
      begin
        if x.dateactive < fp_routstopenddate then
          begin
            --create new routestop for future with self reference route as return route
            lv_rec_newroutestop := x;
            select seq_routestop.nextval into lv_rec_newroutestop.routestop_id from dual;
            lv_rec_newroutestop.dateactive     := fp_routstopenddate;
            lv_rec_newroutestop.returnroute_id := x.route_id;
            insert into routestop values lv_rec_newroutestop;
            --also replicate for the new routestop_id the old routestop_id referenced rows in deliveryschemesperstop table
            for y in (select deliveryscheme_id, day_id
                      from   deliveryschemesperstop
                      where  routestop_id = x.routestop_id
                      and    customer_id = x.customer_id) loop
              insert into deliveryschemesperstop
                (routestop_id, deliveryscheme_id, day_id, customer_id, systemuser_id, timestamp)
              values
                (lv_rec_newroutestop.routestop_id, y.deliveryscheme_id, y.day_id, x.customer_id,
                 gv_defaultuserid, sysdate);
            end loop; --y
            --link the future stops to the new routestop
            update stop s
            set    routestop_id = lv_rec_newroutestop.routestop_id,
                   s.systemuser_id = gv_defaultuserid,
                   s.timestamp = sysdate
            where  routestop_id = x.routestop_id
            and    exists (select 1
                    from   routecontrol
                    where  routecontrol_id = s.routecontrol_id
                    and    drivingdate >= fp_routstopenddate);
            --enddate the old routestop
            update routestop rs
            set    rs.dateinactive = fp_routstopenddate,
                   rs.systemuser_id = gv_defaultuserid,
                   rs.timestamp = sysdate
            where  rs.routestop_id = x.routestop_id;
          end;
        else
          begin
            update routestop rs
            set    rs.returnroute_id = rs.route_id, --self referring
                   rs.systemuser_id = gv_defaultuserid,
                   rs.timestamp = sysdate
            where  rs.routestop_id = x.routestop_id;
          end;
        end if;
        write_log('Changed the return route from route / stop ' || fp_routenumber || ' / ' ||
                  x.stopnumber, 1);
      end;
    end loop; --x
  end update_return_routes;

  procedure new_route(fp_routechangetransactionrec in routechangetransaction%rowtype) is
    lv_routerec route%rowtype;
  begin
    --new_route
    gv_message := 'new_route';
    if is_plant_active(fp_routechangetransactionrec.plant_id) = false then
      raise ge_wrongvalue;
    end if;
    --init route record
    init_route(lv_routerec);
    --fill other values
    lv_routerec.plant_id      := fp_routechangetransactionrec.plant_id;
    lv_routerec.routenumber   := fp_routechangetransactionrec.newroutenumber;
    lv_routerec.description   := fp_routechangetransactionrec.newroutenumber;
    lv_routerec.drivingday_id := fp_routechangetransactionrec.newdrivingday_id;
    lv_routerec.depot_id      := get_depot_id(fp_routechangetransactionrec.depotcode,
                                              fp_routechangetransactionrec.plant_id);
    if fp_routechangetransactionrec.deliveryscheme_id is null then
      lv_routerec.deliveryscheme_id := get_max_deliveryschemeid;
    else
      lv_routerec.deliveryscheme_id := fp_routechangetransactionrec.deliveryscheme_id;
    end if;
    lv_routerec.district_id := get_district_id(fp_routechangetransactionrec.plant_id);
    --insert route
    gv_message := 'new_route insert route';
    insert into route values lv_routerec;
  exception
    when dup_val_on_index then
      gv_message := 'Route already exists';
      raise;
  end new_route;

  procedure change_depot(fp_routechangetransactionrec in routechangetransaction%rowtype) is
    lv_depotid depot.depot_id%type;
  begin
    --change_depot
    gv_message := 'change_depot';
    if is_plant_active(fp_routechangetransactionrec.plant_id) = false then
      raise ge_wrongvalue;
    end if;
    --get depot
    lv_depotid := get_depot_id(fp_routechangetransactionrec.depotcode,
                               fp_routechangetransactionrec.plant_id);
    --update depot for route
    update route r
    set    r.depot_id = lv_depotid, r.timestamp = sysdate, r.systemuser_id = gv_defaultuserid
    where  r.route_id = fp_routechangetransactionrec.route_id;
  end change_depot;

  procedure new_stop(fp_routechangetransactionrec in routechangetransaction%rowtype) is
    lv_routeid               route.route_id%type;
    lv_routedeliveryschemeid route.deliveryscheme_id%type;
    lv_routestoprec          routestop%rowtype;
  begin
    --new_stop
    gv_message := 'new_stop';
    if is_plant_active(fp_routechangetransactionrec.plant_id) = false then
      raise ge_wrongvalue;
    end if;
    --check route id
    lv_routeid := get_route_id(fp_routechangetransactionrec.routenumber,
                               fp_routechangetransactionrec.drivingday_id,
                               fp_routechangetransactionrec.plant_id);
    --check deliverypoint active
    check_deliverypoint_active(fp_routechangetransactionrec.deliverypoint_id);
    --check customer bu
    check_customer_bu(fp_routechangetransactionrec.customer_id,
                      fp_routechangetransactionrec.plant_id);
    --check delivery scheme's
    check_deliveryschemes(fp_routechangetransactionrec.newdeliveryscheme_id, lv_routeid,
                          lv_routedeliveryschemeid);
    --init routestop
    init_routestop(lv_routestoprec);
    --check customer
    lv_routestoprec.dateinactive := check_customer_active(fp_routechangetransactionrec.customer_id);
    --fill other values
    lv_routestoprec.route_id         := lv_routeid;
    lv_routestoprec.returnroute_id   := lv_routeid;
    lv_routestoprec.stopnumber       := get_new_stopnumber(lv_routeid,
                                                           fp_routechangetransactionrec.newstopnumber,
                                                           fp_routechangetransactionrec.changestartdate,
                                                           fp_routechangetransactionrec.routenumber);
    lv_routestoprec.customer_id      := fp_routechangetransactionrec.customer_id;
    lv_routestoprec.deliverypoint_id := fp_routechangetransactionrec.deliverypoint_id;
    lv_routestoprec.dateactive       := fp_routechangetransactionrec.changestartdate;
    lv_routestoprec.sortsequence     := fp_routechangetransactionrec.sortsequence;
    --add customer deliveryscheme
    add_customer_delscheme_days(fp_routechangetransactionrec.customer_id,
                                nvl(fp_routechangetransactionrec.newdeliveryscheme_id,
                                     lv_routedeliveryschemeid),
                                fp_routechangetransactionrec.drivingday_id);
    --insert routestop
    gv_message := 'new_stop insert routestop';
    insert into routestop values lv_routestoprec;
    --insert into deliveryschemeperstop
    gv_message := 'new_stop insert deliveryschemesperstop';
    --try to add, if it already exists no problem
    begin
      insert into customerdeliveryscheme
        (customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
      values
        (lv_routestoprec.customer_id,
         nvl(fp_routechangetransactionrec.newdeliveryscheme_id, lv_routedeliveryschemeid),
         fp_routechangetransactionrec.drivingday_id, gv_defaultuserid, sysdate);
    exception
      when dup_val_on_index then
        update customerdeliveryscheme ds
        set    ds.active = 'Y'
        where  ds.active <> 'Y'
        and    ds.customer_id = lv_routestoprec.customer_id
        and    ds.deliveryscheme_id =
               nvl(fp_routechangetransactionrec.newdeliveryscheme_id, lv_routedeliveryschemeid)
        and    ds.day_id = fp_routechangetransactionrec.drivingday_id;
    end;
    insert into deliveryschemesperstop
      (routestop_id, customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
    values
      (lv_routestoprec.routestop_id, lv_routestoprec.customer_id,
       nvl(fp_routechangetransactionrec.newdeliveryscheme_id, lv_routedeliveryschemeid),
       fp_routechangetransactionrec.drivingday_id, gv_defaultuserid, sysdate);
    --add stop to routecontrol
    add_stop_to_routecontrol(lv_routestoprec.route_id, lv_routestoprec.stopnumber,
                             lv_routestoprec.routestop_id, lv_routestoprec.customer_id,
                             fp_routechangetransactionrec.changestartdate, 'N', null, null, null,
                             fp_routechangetransactionrec.deliverypoint_id); --no cancel so other parameters can be null
  exception
    when dup_val_on_index then
      gv_message := 'Stop already exists';
      raise;
  end new_stop;

  procedure remove_stopnumber(fp_routechangetransactionrec in routechangetransaction%rowtype) is
    lv_routeid         route.route_id%type;
    lv_routestopid     routestop.routestop_id%type;
    lv_routstopenddate routestop.dateinactive%type;
  begin
    --remove_stopnumber
    gv_message := 'remove_stopnumber';
    if is_plant_active(fp_routechangetransactionrec.plant_id) = false then
      raise ge_wrongvalue;
    end if;
    --get new lv_routstopenddate
    if fp_routechangetransactionrec.oldroutelastdrivingdate is null then
      begin
        lv_routstopenddate := fp_routechangetransactionrec.changestartdate;
      end;
    else
      begin
        lv_routstopenddate := fp_routechangetransactionrec.oldroutelastdrivingdate + 1;
      end;
    end if;
    --check route id
    lv_routeid := get_route_id(fp_routechangetransactionrec.routenumber,
                               fp_routechangetransactionrec.drivingday_id,
                               fp_routechangetransactionrec.plant_id);
    --get routestop id
    lv_routestopid := get_routestopid(lv_routeid, fp_routechangetransactionrec.customer_id,
                                      fp_routechangetransactionrec.deliverypoint_id,
                                      fp_routechangetransactionrec.stopnumber,
                                      fp_routechangetransactionrec.changestartdate);
    --Step 1: Inactivate routestop
    update routestop rs
    set    rs.dateinactive = lv_routstopenddate, rs.systemuser_id = gv_defaultuserid,
           rs.timestamp = sysdate
    where  rs.routestop_id = lv_routestopid;
    --Step 2: Cancel stops from route status control
    inactivate_stop_routcontrol(lv_routeid, lv_routestopid, lv_routstopenddate);
    --Step 3: Put end date to standard deliveries
    putenddate2standarddeliveries(fp_routechangetransactionrec.customer_id,
                                  fp_routechangetransactionrec.deliverypoint_id,
                                  fp_routechangetransactionrec.drivingday_id, lv_routstopenddate);
    --Step 4: Update stops that have the route of the ended stop as return route
    update_return_routes(fp_routechangetransactionrec.customer_id, lv_routstopenddate, lv_routeid,
                         fp_routechangetransactionrec.routenumber);
  end remove_stopnumber;

  procedure change_stopnumber(fp_routechangetransactionrec in routechangetransaction%rowtype) is
    lv_routeid               route.route_id%type;
    lv_routestopid           routestop.routestop_id%type;
    lv_newroutestoprec       routestop%rowtype;
    lv_routstopenddate       routestop.dateinactive%type;
    lv_routedeliveryschemeid deliveryscheme.deliveryscheme_id%type;
    lv_changestartdate       date;
    lv_is_movestopmoveup		 boolean := false;
  begin
    --change_stopnumber
    gv_message := 'change_stopnumber';
		write_log('Calling Pack_Routetransaction.CHANGE_STOPNUMBER...');

    --this change_stopnumber is used when movestopmoveup
    if (gv_SS_MOVESTOPMOVEUP = 1 and
      	fp_routechangetransactionrec.newdrivingday_id is null and
      	fp_routechangetransactionrec.newroute_id is null and
        fp_routechangetransactionrec.newdeliveryscheme_id is null)
    then
      lv_is_movestopmoveup := true;
    end if;

    if is_plant_active(fp_routechangetransactionrec.plant_id) = false then
      raise ge_wrongvalue;
    end if;
    if gv_directstopchange = 1 then
      lv_changestartdate := trunc(sysdate);
    else
      lv_changestartdate := fp_routechangetransactionrec.changestartdate;
    end if;
    --get new lv_routstopenddate
    if fp_routechangetransactionrec.oldroutelastdrivingdate is null then
      begin
        lv_routstopenddate := lv_changestartdate;
      end;
    else
      begin
        lv_routstopenddate := fp_routechangetransactionrec.oldroutelastdrivingdate + 1;
      end;
    end if;

    --check route id
    lv_routeid := get_route_id(fp_routechangetransactionrec.routenumber,
                               fp_routechangetransactionrec.drivingday_id,
                               fp_routechangetransactionrec.plant_id);
    --get routestop id
    lv_routestopid := get_routestopid(lv_routeid, fp_routechangetransactionrec.customer_id,
                                      fp_routechangetransactionrec.deliverypoint_id,
                                      fp_routechangetransactionrec.stopnumber,
                                      fp_routechangetransactionrec.changestartdate,
                                      case
                                         when length(fp_routechangetransactionrec.routenumber) = 5 and
                                              gv_databasecode = 'LST' then
                                          99
                                         else
                                          5
                                       end); --copy old into new routestop

    if ((length(fp_routechangetransactionrec.routenumber) = 5 and gv_databasecode = 'LST') or
       gv_directstopchange = 1) then
      --not change any dates, just update the stopnumber request 16-01-2015
      update routestop rs
      set    rs.stopnumber = fp_routechangetransactionrec.newstopnumber,
      			 rs.movedroutestop_id = null,
             rs.systemuser_id = gv_defaultuserid, rs.timestamp = sysdate
      where  rs.routestop_id = lv_routestopid;
      --copy new route data into record
      select * into lv_newroutestoprec from routestop rs where rs.routestop_id = lv_routestopid;
    else
      begin
        --copy old into new routestop
        select * into lv_newroutestoprec from routestop rs where rs.routestop_id = lv_routestopid;
        begin
          --check if new stop number already exists in route for date
          if check_dup_stop_route(lv_routeid, fp_routechangetransactionrec.newstopnumber,
                                  fp_routechangetransactionrec.changestartdate) and gv_stopnrunique = 0
          then
            write_log('Stopnumber ' || to_char(fp_routechangetransactionrec.newstopnumber) || ' already used for route ' ||
                      to_char(fp_routechangetransactionrec.routenumber), 2);
          end if;
        exception
          when no_data_found then
            null;
        end;
        ---Step 1: Create new routestop
        if (not lv_is_movestopmoveup) then
	        select seq_routestop.nextval into lv_newroutestoprec.routestop_id from dual;
        else
          --when have movestopmoveup then uses same routestopid
          lv_newroutestoprec.routestop_id := lv_routestopid;
        end if;
        lv_newroutestoprec.stopnumber    := fp_routechangetransactionrec.newstopnumber;
        lv_newroutestoprec.dateactive    := fp_routechangetransactionrec.changestartdate;
        if lv_newroutestoprec.dateactive > lv_newroutestoprec.dateinactive then
          lv_newroutestoprec.dateinactive := to_date('31-12-2099', 'dd-mm-yyyy');
        end if;
        lv_newroutestoprec.systemuser_id := gv_defaultuserid;
        lv_newroutestoprec.timestamp     := sysdate;
        lv_newroutestoprec.sortsequence  := fp_routechangetransactionrec.sortsequence; 
        if (not lv_is_movestopmoveup) then
          gv_message := 'change_stopnumber insert routestop';
          insert into routestop values lv_newroutestoprec;
				end if;

        --Step 2: Put end date to previous routestop
        if (not lv_is_movestopmoveup) then
        	gv_message := 'change_stopnumber update routestop';
        	update routestop rs
        	set    rs.dateinactive = case
                                     when lv_routstopenddate < rs.dateinactive then
                                      lv_routstopenddate
                                     else
                                      rs.dateinactive
                                   end,
                 rs.movedroutestop_id = lv_newroutestoprec.routestop_id,
                 rs.systemuser_id = gv_defaultuserid,
                 rs.timestamp = sysdate
          where  rs.routestop_id = lv_routestopid;
				end if;

        -- Extra Step replacing Step#1 and Step#2 when have movestopmoveup
        if (lv_is_movestopmoveup) then
          gv_message := 'change_stopnumber update routestop (when movestopmoveup)';
          update routestop rs
          set    rs.stopnumber = lv_newroutestoprec.stopnumber,
                 rs.systemuser_id = gv_defaultuserid,
                 rs.timestamp = sysdate
          where  rs.routestop_id = lv_routestopid;
        end if;

        --Step 3a: Update stopnumber in route status control
        gv_message := 'change_stopnumber update stop';
        update stop st
        set    st.stopnumber = lv_newroutestoprec.stopnumber,
               st.routestop_id = lv_newroutestoprec.routestop_id,
               st.systemuser_id = gv_defaultuserid,
               st.timestamp = sysdate
        where  st.routestop_id = lv_routestopid
        and    st.customer_link_id = fp_routechangetransactionrec.customer_id
        and    exists (select 1
                from   routecontrol rc
                where  rc.routecontrol_id = st.routecontrol_id
                and    rc.route_id = lv_routeid
                and    rc.pocketpcstatus = 0
                and    rc.drivingdate >= fp_routechangetransactionrec.changestartdate
                and    rc.routelistprinted = 'N');
        --Step 3b update all packingnotes with new stopnumber
        gv_message := 'change_stopnumber update packingnote';
        update packingnote pk
        set    pk.stopnumber = lv_newroutestoprec.stopnumber,
               pk.systemuser_id = gv_defaultuserid,
               pk.timestamp = sysdate
        where  pk.stop_id in
               (select st.stop_id
                from   stop st
                where  st.routestop_id = lv_newroutestoprec.routestop_id
                and    st.stopnumber = lv_newroutestoprec.stopnumber
                and    st.customer_link_id = fp_routechangetransactionrec.customer_id);
        --Step 3c update all client delivery notes with new stopnumber
        gv_message := 'change_stopnumber update clientdeliverynote';
        update clientdeliverynote cdn
        set    cdn.stopnumber = lv_newroutestoprec.stopnumber,
               cdn.systemuser_id = gv_defaultuserid,
               cdn.timestamp = sysdate
        where  cdn.stop_id in
               (select st.stop_id
                from   stop st
                where  st.routestop_id = lv_newroutestoprec.routestop_id
                and    st.stopnumber = lv_newroutestoprec.stopnumber
                and    st.customer_link_id = fp_routechangetransactionrec.customer_id);

        --Step 4: Update StopNumber in future RouteStops for the same route and Customer
        gv_message := 'change_stopnumber update stopnumber in future routestops for same route/customer';
        update routestop rs
        set    rs.stopnumber = fp_routechangetransactionrec.newstopnumber,
               rs.systemuser_id = gv_defaultuserid,
               rs.timestamp = sysdate
        where  rs.route_id = lv_routeid
        and    (rs.stopnumber = fp_routechangetransactionrec.stopnumber or rs.stopnumber = 0)
        and    rs.customer_id = fp_routechangetransactionrec.customer_id
        and    rs.deliverypoint_id = fp_routechangetransactionrec.deliverypoint_id
        and    rs.dateactive >= fp_routechangetransactionrec.changestartdate;

        --insert into deliveryschemeperstop
        if (not lv_is_movestopmoveup) then
          for wbo in (select dss.deliveryscheme_id, dss.day_id
                      from   deliveryschemesperstop dss
                      where  dss.routestop_id = lv_routestopid
                      and    dss.customer_id = lv_newroutestoprec.customer_id) loop
            begin
              --try to add, if it already exists no problem
              begin
                insert into customerdeliveryscheme
                  (customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
                values
                  (lv_newroutestoprec.customer_id,
                   nvl(wbo.deliveryscheme_id, lv_routedeliveryschemeid), wbo.day_id, gv_defaultuserid,
                   sysdate);
              exception
                when dup_val_on_index then
                  update customerdeliveryscheme ds
                  set    ds.active = 'Y',
                         ds.systemuser_id = gv_defaultuserid,
                         ds.timestamp = sysdate
                  where  ds.active <> 'Y'
                  and    ds.customer_id = lv_newroutestoprec.customer_id
                  and    ds.deliveryscheme_id = nvl(wbo.deliveryscheme_id, lv_routedeliveryschemeid)
                  and    ds.day_id = wbo.day_id;
              end;
              gv_message := 'new_stop insert deliveryschemesperstop';
              insert into deliveryschemesperstop
                (routestop_id, customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
              values
                (lv_newroutestoprec.routestop_id, lv_newroutestoprec.customer_id,
                 nvl(wbo.deliveryscheme_id, lv_routedeliveryschemeid), wbo.day_id, gv_defaultuserid,
                 sysdate);
            exception
              when dup_val_on_index then
                null;
            end;
          end loop;
        end if;
      end;
    end if;
    write_log('Return from Pack_Routetransaction.CHANGE_STOPNUMBER.');
  end change_stopnumber;

  procedure move_stop(fp_routechangetransactionrec in routechangetransaction%rowtype) is
    lv_routeid               route.route_id%type;
    lv_routestopid           routestop.routestop_id%type;
    lv_routedeliveryschemeid deliveryscheme.deliveryscheme_id%type;
    lv_newrouteid            route.route_id%type;
    lv_newroutestoprec       routestop%rowtype;
    lv_removestop            varchar2(1) := 'N';
    lv_routstopenddate       routestop.dateinactive%type;

    procedure end_previous_stop(fp_removestop                in out varchar2,
                                fp_routechangetransactionrec in routechangetransaction%rowtype,
                                fp_routeid                   in route.route_id%type,
                                fp_routestopid               in routestop.routestop_id%type,
                                fp_newrouteid                in route.route_id%type,
                                fp_newroutestopid            in routestop.routestop_id%type) is
    begin
      --step 4 end previous stop
      --end_previous_stop
      gv_message := 'end_previous_stop';
      if (fp_routechangetransactionrec.productgroup_id is null) then
        begin
          update routestop rs
          set    rs.dateinactive = case
                                     when rs.dateactive > lv_routstopenddate then
                                      rs.dateactive + 1
                                     else
                                      lv_routstopenddate
                                   end,
                 rs.systemuser_id = gv_defaultuserid,
                 rs.timestamp = sysdate,
                 rs.movedroutestop_id = fp_newroutestopid
          where  rs.routestop_id = fp_routestopid;
          --stop ended
          fp_removestop := 'Y';
          --update also all workorders
          update workorder wo
          set    wo.route_id = fp_newrouteid,
                 wo.systemuser_id = gv_defaultuserid,
                 wo.timestamp = sysdate
          where  wo.route_id = fp_routeid
          and    wo.neededdate >= lv_routstopenddate
          and    wo.status_id in (select st.status_id
                                  from   status st
                                  where  st.statustype = 'WO'
                                  and    st.code in ('05', '10', '15', '20', '25'))
          and    wo.customer_link_id = fp_routechangetransactionrec.customer_id;
        end;
      end if;
    end end_previous_stop;

    procedure update_routecontrol(fp_removestop     in varchar2,
                                  fp_customerid     in customer.customer_id%type,
                                  fp_routeid        in route.route_id%type,
                                  fp_routestopid    in routestop.routestop_id%type,
                                  fp_oldstopnumber  in stop.stopnumber%type,
                                  fp_newroutestopid in routestop.routestop_id%type,
                                  fp_newstopnumber  in stop.stopnumber%type) is
    begin
      --step 5 update routestatus control
      --update_routecontrol
      gv_message := 'update_routecontrol';
      if fp_removestop = 'Y' then
        begin
          --add new stop + cancel old stop
          add_stop_to_routecontrol(fp_routeid, fp_newstopnumber, fp_newroutestopid, fp_customerid,
                                   fp_routechangetransactionrec.changestartdate, 'Y', fp_routestopid,
                                   fp_oldstopnumber, lv_routstopenddate,
                                   fp_routechangetransactionrec.deliverypoint_id);
        end;
      else
        begin
          --only add new stop
          add_stop_to_routecontrol(fp_routeid, fp_newstopnumber, fp_newroutestopid, fp_customerid,
                                   lv_routstopenddate, 'N', null, null, null,
                                   fp_routechangetransactionrec.deliverypoint_id); --no cancel so other parameters can be null
        end;
      end if; --fp_removestop = 'Y'
    end update_routecontrol;

    procedure cleanupdays_movestop is
      lv_counter number := 0;
    begin
      --cleanupdays_movestop
      gv_message := 'cleanupdays_movestop';
      if gv_directstopchange = 1 then
        begin
          if fp_routechangetransactionrec.drivingday_id <>
             fp_routechangetransactionrec.newdrivingday_id then
            begin
              --customer works with route-stops, so ALL days should have route-stops!!
              --EMG: so we can delete days for which there is NO route-stop!
              select count(1)
              into   lv_counter
              from   routestop rs
              join   route r
              on     r.route_id = rs.route_id
              where  r.drivingday_id = fp_routechangetransactionrec.drivingday_id
              and    rs.customer_id = fp_routechangetransactionrec.customer_id;
              if lv_counter = 0 then
                begin
                  --delete customer days in related tables see condition above why
                  gv_message := 'cleanupdays_movestop delete from customerdeliveryday';
                  delete from customerdeliveryday cdd
                  where  cdd.day_id = fp_routechangetransactionrec.drivingday_id
                  and    cdd.customer_id = fp_routechangetransactionrec.customer_id;
                  gv_message := 'cleanupdays_movestop delete from productdeliveryday';
                  delete from productdeliveryday pdd
                  where  pdd.day_id = fp_routechangetransactionrec.drivingday_id
                  and    pdd.productlistpercustomer_id in
                         (select l.productlistpercustomer_id
                           from   productlistpercustomer l
                           where  l.customer_id = fp_routechangetransactionrec.customer_id);
                  gv_message := 'cleanupdays_movestop delete from customerdeliveryscheme';
                  delete from customerdeliveryscheme cds
                  where  cds.day_id = fp_routechangetransactionrec.drivingday_id
                  and    cds.customer_id = fp_routechangetransactionrec.customer_id;
                end;
              end if;
            end;
          end if;
        end;
      end if;
    end cleanupdays_movestop;

    procedure update_batchjobcontents(fp_drivingday_id    in route.drivingday_id%type,
                                      fp_newdrivingday_id in route.drivingday_id%type,
                                      fp_deliverypoint_id in deliverypoint.deliverypoint_id%type,
                                      fp_changestartdate  in batchjobcontent.planneddeliverydate%type,
                                      fp_plant_id         in plant.plant_id%type,
                                      fp_customer_id      in customer.customer_id%type) is
      m_diff number := 0;
    begin
      if (fp_drivingday_id <> fp_newdrivingday_id) then
        m_diff := fp_newdrivingday_id - fp_drivingday_id;
        update batchjobcontent bc
        set    bc.planneddeliverydate = bc.planneddeliverydate + m_diff,
        		   bc.timestamp = sysdate,
               bc.systemuser_id = gv_defaultuserid
        where  bc.status = 1
        and    exists (select 1
                from   batchjob b
                where  b.batchjob_id = bc.batchjob_id
                and    b.plant_id = fp_plant_id
                and    b.status = 1)
        and    exists (select 1
                from   department d, distributionpoint dp
                where  d.department_id = bc.department_link_id
                and    d.customer_id = fp_customer_id
                and    dp.department_id = d.department_id
                and    ((dp.deliverypoint_id = fp_deliverypoint_id) or
                      (fp_deliverypoint_id is null)))
        and    bc.planneddeliverydate >= fp_changestartdate;
      end if;
    end update_batchjobcontents;

  begin
    --move_stop step 1
    gv_message := 'move_stop';
    write_log('Calling Pack_Routetransaction.MOVE_STOP...');

    if is_plant_active(fp_routechangetransactionrec.plant_id) = false then
      raise ge_wrongvalue;
    end if;
    --get new lv_routstopenddate
    if fp_routechangetransactionrec.oldroutelastdrivingdate is null then
      begin
        lv_routstopenddate := fp_routechangetransactionrec.changestartdate;
      end;
    else
      begin
        lv_routstopenddate := fp_routechangetransactionrec.oldroutelastdrivingdate + 1;
      end;
    end if;
    --check route id
    lv_routeid := get_route_id(fp_routechangetransactionrec.routenumber,
                               fp_routechangetransactionrec.drivingday_id,
                               fp_routechangetransactionrec.plant_id);
    --get routestop id
    lv_routestopid := get_routestopid(lv_routeid, fp_routechangetransactionrec.customer_id,
                                      fp_routechangetransactionrec.deliverypoint_id,
                                      fp_routechangetransactionrec.stopnumber,
                                      fp_routechangetransactionrec.changestartdate, 6);
    --init routestop->copy old routestop into new routestop
    select * into lv_newroutestoprec from routestop rs where rs.routestop_id = lv_routestopid;
    if gv_directstopchange = 0 then
      --get new routestopID
      select seq_routestop.nextval into lv_newroutestoprec.routestop_id from dual;
    end if;
    --check delivery scheme's
    check_deliveryschemes(fp_routechangetransactionrec.newdeliveryscheme_id,
                          fp_routechangetransactionrec.route_id, lv_routedeliveryschemeid);
    --check sortbatch
    check_sortbatch(lv_routestopid, fp_routechangetransactionrec.changestartdate);
    begin
      --check new route id
      lv_newrouteid := get_route_id(fp_routechangetransactionrec.newroutenumber,
                                    fp_routechangetransactionrec.newdrivingday_id,
                                    fp_routechangetransactionrec.plant_id);
    exception
      when no_data_found then
        if gv_databasecode = 'LST' then
          select seq_route.nextval into lv_newrouteid from dual;
          insert into route
            (route_id, plant_id, drivingday_id, routenumber, description, depot_id, systemuser_id,
             codeonlabel, timestamp, driver_id, truck_id, starttime, endtime, packingday_id,
             packingdeadline, loadingdock, putupreportpercustomer, putupreportperroute,
             putupreportperdepot, putupreportroutestop, printsoilticket, printorderform,
             bundletagsflatw, bundletagsgarm, active, sortbatchdefinition_id,
             commissionpayabledriver, spareroute, splitperpackingdept, deliveryscheme_id,
             printpicklistrsc, printmountingorder, printdismountingorder, printsalesnotes,
             district_id, routemanager_id, printtransportlabels)
            select lv_newrouteid, r.plant_id, fp_routechangetransactionrec.newdrivingday_id,
                   fp_routechangetransactionrec.newroutenumber,
                   to_char(fp_routechangetransactionrec.newroutenumber) /*routedesc*/, r.depot_id,
                   r.systemuser_id, r.codeonlabel, sysdate, r.driver_id, r.truck_id, r.starttime,
                   r.endtime, r.packingday_id, r.packingdeadline, r.loadingdock,
                   r.putupreportpercustomer, r.putupreportperroute, r.putupreportperdepot,
                   r.putupreportroutestop, r.printsoilticket, r.printorderform, r.bundletagsflatw,
                   r.bundletagsgarm, r.active, r.sortbatchdefinition_id, r.commissionpayabledriver,
                   r.spareroute, r.splitperpackingdept,
                   nvl(fp_routechangetransactionrec.newdeliveryscheme_id, r.deliveryscheme_id),
                   r.printpicklistrsc, r.printmountingorder, r.printdismountingorder,
                   r.printsalesnotes, r.district_id, r.routemanager_id, r.printtransportlabels
            from   route r
            where  r.route_id = lv_routeid;
        else
          raise no_data_found;
        end if;
    end;
    if gv_databasecode <> 'LST' then
      lv_newroutestoprec.dateinactive := to_date('31-12-2099', 'DD-MM-YYYY');
    end if;
    --check if routestop is used by another customer...?
    if check_dup_stop_route(lv_newrouteid, fp_routechangetransactionrec.newstopnumber,
                            fp_routechangetransactionrec.changestartdate) and gv_stopnrunique = 0 then
      begin
        --stop already found try with st
        write_log('Move stop: Stopnumber already used. Already exists routstop_id ' ||
                  lv_newroutestoprec.routestop_id, 2);
      end;
    end if;
    --always use the new stopnumber
    lv_newroutestoprec.stopnumber := fp_routechangetransactionrec.newstopnumber;
    if gv_directstopchange = 1 then
      begin
        lv_newroutestoprec.route_id := lv_newrouteid;
        update routestop rs
        set    rs.route_id = lv_newrouteid,
               rs.stopnumber = fp_routechangetransactionrec.newstopnumber,
               rs.systemuser_id = gv_defaultuserid,
               rs.timestamp = sysdate,
               rs.sortsequence = fp_routechangetransactionrec.Sortsequence,
               rs.returnroute_id = case
                                     when rs.returnroute_id = rs.route_id then
                                      lv_newrouteid
                                     else
                                      rs.returnroute_id
                                   end
        where  rs.routestop_id = lv_routestopid;
      end;
    else
      begin
        --check if newroutestop stopnumber already exists for same customer
        lv_newroutestoprec.routestop_id := get_routestopid(lv_newrouteid,
                                                           fp_routechangetransactionrec.customer_id,
                                                           fp_routechangetransactionrec.deliverypoint_id,
                                                           fp_routechangetransactionrec.newstopnumber,
                                                           fp_routechangetransactionrec.changestartdate,
                                                           6);
        --new routestop already exists fill corresponding record
        gv_message := 'move_stop: new routestop already exists fill corresponding record';
        select *
        into   lv_newroutestoprec
        from   routestop rs
        where  rs.routestop_id = lv_newroutestoprec.routestop_id;
        if gv_databasecode <> 'LST' then
          --if existing routestop is not active anymore, make it active till 31-12-2099 (empty)
          if lv_newroutestoprec.dateinactive < fp_routechangetransactionrec.changestartdate then
            begin
              lv_newroutestoprec.dateinactive := to_date('31-12-2099', 'dd-mm-yyyy');
              update routestop rs
              set    rs.dateinactive = lv_newroutestoprec.dateinactive,
                     rs.systemuser_id = gv_defaultuserid,
                     rs.timestamp = sysdate
              where  rs.routestop_id = lv_newroutestoprec.routestop_id;
            end;
          end if;
          --if existing route stop is in future, make it active from the change date
          if lv_newroutestoprec.dateactive > fp_routechangetransactionrec.changestartdate then
            begin
              lv_newroutestoprec.dateactive := fp_routechangetransactionrec.changestartdate;
              update routestop rs
              set    rs.dateactive = fp_routechangetransactionrec.changestartdate,
                     rs.systemuser_id = gv_defaultuserid,
                     rs.timestamp = sysdate
              where  rs.routestop_id = lv_newroutestoprec.routestop_id;
            end;
          end if;
        end if;
      exception
        when no_data_found then
          begin
            --fill other values
            lv_newroutestoprec.route_id         := lv_newrouteid;
            lv_newroutestoprec.returnroute_id   := lv_newrouteid;
            lv_newroutestoprec.customer_id      := fp_routechangetransactionrec.customer_id;
            lv_newroutestoprec.deliverypoint_id := fp_routechangetransactionrec.deliverypoint_id;
            lv_newroutestoprec.dateactive       := fp_routechangetransactionrec.changestartdate;
            lv_newroutestoprec.timestamp        := sysdate;
            lv_newroutestoprec.systemuser_id    := gv_defaultuserid;
            lv_newroutestoprec.Sortsequence     := fp_routechangetransactionrec.Sortsequence;
            --insert newroutestop record
            gv_message := 'move_stop: insert routestop';
            insert into routestop values lv_newroutestoprec;
          end;
      end;
    end if;

    --step 2 add customer deliveryscheme
    add_customer_deldays_movestop(fp_routechangetransactionrec.customer_id,
                                  fp_routechangetransactionrec.newdeliveryscheme_id,
                                  fp_routechangetransactionrec.newdrivingday_id,
                                  fp_routechangetransactionrec.drivingday_id,
                                  lv_routestopid,
                                  case when gv_directstopchange = 0 then
                                    fp_routechangetransactionrec.changestartdate
                                  else
                                    trunc(sysdate)
                                  end,
                                  fp_routechangetransactionrec.deliveryscheme_id,
                                  fp_routechangetransactionrec.deliverypoint_id);

    --extra step check customer deliveryscheme per stop
    --insert into deliveryschemeperstop
    if (fp_routechangetransactionrec.newdeliveryscheme_id is null and
       fp_routechangetransactionrec.deliveryscheme_id is null and gv_directstopchange = 0) then
      --option 1 copy all to new
      --try to add, if it already exists no problem
      begin
        insert into customerdeliveryscheme
          (customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
          select dprs.customer_id, dprs.deliveryscheme_id,
                 fp_routechangetransactionrec.newdrivingday_id, gv_defaultuserid, sysdate
          from   deliveryschemesperstop dprs
          where  dprs.routestop_id = lv_routestopid
          and    not exists (select 1
                  from   customerdeliveryscheme cdd
                  where  cdd.customer_id = dprs.customer_id
                  and    cdd.day_id = fp_routechangetransactionrec.newdrivingday_id
                  and    cdd.deliveryscheme_id = dprs.deliveryscheme_id)
          and    not exists
           (select 1
                  from   deliveryschemesperstop dprsx
                  where  dprsx.routestop_id = lv_newroutestoprec.routestop_id
                  and    dprsx.customer_id = dprs.customer_id
                  and    dprsx.deliveryscheme_id = dprs.deliveryscheme_id
                  and    dprsx.day_id = fp_routechangetransactionrec.newdrivingday_id)
          group  by dprs.customer_id, dprs.deliveryscheme_id;
      exception
        when dup_val_on_index then
          null;
      end;
      --unique index ROUTESTOP_ID, CUSTOMER_ID, DELIVERYSCHEME_ID, DAY_ID
      gv_message := 'new_stop insert deliveryschemesperstop, option 1';
      insert into deliveryschemesperstop
        (routestop_id, customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
        select lv_newroutestoprec.routestop_id, dprs.customer_id, dprs.deliveryscheme_id,
               fp_routechangetransactionrec.newdrivingday_id, gv_defaultuserid, sysdate
        from   deliveryschemesperstop dprs
        where  dprs.routestop_id = lv_routestopid
        and    not exists
         (select 1
                from   deliveryschemesperstop dprsx
                where  dprsx.routestop_id = lv_newroutestoprec.routestop_id
                and    dprsx.customer_id = dprs.customer_id
                and    dprsx.deliveryscheme_id = dprs.deliveryscheme_id
                and    dprsx.day_id = fp_routechangetransactionrec.newdrivingday_id);
    else
      if (fp_routechangetransactionrec.newdeliveryscheme_id is not null and
         fp_routechangetransactionrec.deliveryscheme_id is null) then
        --option 2
        --insert into the new delscheme deliveryschemeperstop
        begin
          --try to add, if it already exists no problem
          begin
            insert into customerdeliveryscheme
              (customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
            values
              (lv_newroutestoprec.customer_id, fp_routechangetransactionrec.newdeliveryscheme_id,
               fp_routechangetransactionrec.newdrivingday_id, gv_defaultuserid, sysdate);
          exception
            when dup_val_on_index then
              update customerdeliveryscheme ds
              set    ds.active = 'Y',
                     ds.systemuser_id = gv_defaultuserid,
                     ds.timestamp = sysdate
              where  ds.active <> 'Y'
              and    ds.customer_id = lv_newroutestoprec.customer_id
              and    ds.deliveryscheme_id = fp_routechangetransactionrec.newdeliveryscheme_id
              and    ds.day_id = fp_routechangetransactionrec.newdrivingday_id;
          end;
          gv_message := 'new_stop insert deliveryschemesperstop, option 2';
          begin
            gv_message := 'new_stop insert deliveryschemesperstop';
            insert into deliveryschemesperstop
              (routestop_id, customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
            values
              (lv_newroutestoprec.routestop_id, lv_newroutestoprec.customer_id,
               fp_routechangetransactionrec.newdeliveryscheme_id,
               fp_routechangetransactionrec.newdrivingday_id, gv_defaultuserid, sysdate);
          exception
            when dup_val_on_index then
              null;
          end;
        end;
      else
        if (fp_routechangetransactionrec.newdeliveryscheme_id is null and
           fp_routechangetransactionrec.deliveryscheme_id is not null) then
          --option 3
          --insert into the old delscheme deliveryschemeperstop
          --try to add, if it already exists no problem
          begin
            insert into customerdeliveryscheme
              (customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
            values
              (lv_newroutestoprec.customer_id, fp_routechangetransactionrec.deliveryscheme_id,
               fp_routechangetransactionrec.newdrivingday_id, gv_defaultuserid, sysdate);
          exception
            when dup_val_on_index then
              update customerdeliveryscheme ds
              set    ds.active = 'Y',
                     ds.systemuser_id = gv_defaultuserid,
                     ds.timestamp = sysdate
              where  ds.active <> 'Y'
              and    ds.customer_id = lv_newroutestoprec.customer_id
              and    ds.deliveryscheme_id = fp_routechangetransactionrec.deliveryscheme_id
              and    ds.day_id = fp_routechangetransactionrec.newdrivingday_id;
          end;
          begin
            --insert into the old delscheme deliveryschemeperstop
            begin
              gv_message := 'new_stop insert deliveryschemesperstop';
              insert into deliveryschemesperstop
                (routestop_id, customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
              values
                (lv_newroutestoprec.routestop_id, lv_newroutestoprec.customer_id,
                 fp_routechangetransactionrec.deliveryscheme_id,
                 fp_routechangetransactionrec.newdrivingday_id, gv_defaultuserid, sysdate);
            exception
              when dup_val_on_index then
                null;
            end;
          end;
        else
          if (fp_routechangetransactionrec.newdeliveryscheme_id is not null and
             fp_routechangetransactionrec.deliveryscheme_id is not null) then
            --option 4
            begin
              insert into customerdeliveryscheme
                (customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
                select dprs.customer_id, dprs.deliveryscheme_id,
                       fp_routechangetransactionrec.newdrivingday_id, gv_defaultuserid, sysdate
                from   deliveryschemesperstop dprs
                where  dprs.routestop_id = lv_routestopid
                and    dprs.deliveryscheme_id <> fp_routechangetransactionrec.deliveryscheme_id
                and    not exists
                 (select 1
                        from   customerdeliveryscheme cdd
                        where  cdd.customer_id = dprs.customer_id
                        and    cdd.day_id = fp_routechangetransactionrec.newdrivingday_id
                        and    cdd.deliveryscheme_id = dprs.deliveryscheme_id)
                and    not exists
                 (select 1
                        from   deliveryschemesperstop dprsx
                        where  dprsx.routestop_id = lv_newroutestoprec.routestop_id
                        and    dprsx.customer_id = dprs.customer_id
                        and    dprsx.deliveryscheme_id = dprs.deliveryscheme_id
                        and    dprsx.day_id = fp_routechangetransactionrec.newdrivingday_id)
                group  by dprs.customer_id, dprs.deliveryscheme_id;
            exception
              when dup_val_on_index then
                null;
            end;
            gv_message := 'new_stop insert deliveryschemesperstop, option 4';
            insert into deliveryschemesperstop
              (routestop_id, customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
              select lv_newroutestoprec.routestop_id, dprs.customer_id, dprs.deliveryscheme_id,
                     fp_routechangetransactionrec.newdrivingday_id, gv_defaultuserid, sysdate
              from   deliveryschemesperstop dprs
              where  dprs.routestop_id = lv_routestopid
              and    dprs.deliveryscheme_id <> fp_routechangetransactionrec.deliveryscheme_id
              and    not exists
               (select 1
                      from   deliveryschemesperstop dprsx
                      where  dprsx.routestop_id = lv_newroutestoprec.routestop_id
                      and    dprsx.customer_id = dprs.customer_id
                      and    dprsx.deliveryscheme_id = dprs.deliveryscheme_id
                      and    dprsx.day_id = fp_routechangetransactionrec.newdrivingday_id);
            --insert into the new delscheme deliveryschemeperstop
            begin
              insert into customerdeliveryscheme
                (customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
              values
                (lv_newroutestoprec.customer_id, fp_routechangetransactionrec.newdeliveryscheme_id,
                 fp_routechangetransactionrec.newdrivingday_id, gv_defaultuserid, sysdate);
            exception
              when dup_val_on_index then
                update customerdeliveryscheme ds
                set    ds.active = 'Y',
                       ds.systemuser_id = gv_defaultuserid,
                       ds.timestamp = sysdate
                where  ds.active <> 'Y'
                and    ds.customer_id = lv_newroutestoprec.customer_id
                and    ds.deliveryscheme_id = fp_routechangetransactionrec.newdeliveryscheme_id
                and    ds.day_id = fp_routechangetransactionrec.newdrivingday_id;
            end;
            begin
              gv_message := 'new_stop insert deliveryschemesperstop';
              insert into deliveryschemesperstop
                (routestop_id, customer_id, deliveryscheme_id, day_id, systemuser_id, timestamp)
              values
                (lv_newroutestoprec.routestop_id, lv_newroutestoprec.customer_id,
                 fp_routechangetransactionrec.newdeliveryscheme_id,
                 fp_routechangetransactionrec.newdrivingday_id, gv_defaultuserid, sysdate);
            exception
              when dup_val_on_index then
                null;
            end;
          end if;
        end if;
      end if;
    end if;

    --step 3 copy standarddeliverylines to new driving day, all deliveryschemes from old routestop or only selected one
    for DS_FORREC in (select dsp.deliveryscheme_id
               from   deliveryschemesperstop dsp
               where  dsp.routestop_id = lv_routestopid
                      and    (dsp.deliveryscheme_id = fp_routechangetransactionrec.deliveryscheme_id
                     						or fp_routechangetransactionrec.deliveryscheme_id is null)
                      group  by dsp.deliveryscheme_id)
    loop
        copy_standarddeliverylines(fp_routechangetransactionrec, DS_FORREC.deliveryscheme_id);
    end loop;

    if gv_directstopchange = 1 then
      cleanupdays_movestop;
    else
      --step 4 end previous stop
      end_previous_stop(lv_removestop, fp_routechangetransactionrec, lv_routeid, lv_routestopid,
                        lv_newrouteid, lv_newroutestoprec.routestop_id);
      --step 5 update routestatus control
      update_routecontrol(lv_removestop, lv_newroutestoprec.customer_id, lv_newrouteid,
                          lv_routestopid, fp_routechangetransactionrec.stopnumber,
                          lv_newroutestoprec.routestop_id, lv_newroutestoprec.stopnumber);
      --Step 6: Update stops that have the route of the ended stop as return route
      update_return_routes(fp_routechangetransactionrec.customer_id, lv_routstopenddate, lv_routeid,
                           fp_routechangetransactionrec.routenumber);
    end if;
    -- Step 8: Update BatchJobContents
    update_batchjobcontents(fp_routechangetransactionrec.drivingday_id,
                            fp_routechangetransactionrec.newdrivingday_id,
                            fp_routechangetransactionrec.deliverypoint_id,
                            fp_routechangetransactionrec.changestartdate,
                            fp_routechangetransactionrec.plant_id,
                            fp_routechangetransactionrec.customer_id);

		write_log('Return from Pack_Routetransaction.MOVE_STOP.');
  end move_stop;

  procedure change_deliveryscheme(fp_routechangetransactionrec in routechangetransaction%rowtype) is
    lv_route_id               route.route_id%type;
  	lv_is_routeday_change			boolean;
    lv_routestop_is_valid			boolean;
    lv_stop_id								number;
    lv_stoprec                stop%rowtype;
    lv_date									  date;
    lv_number									number;

  begin
    --change deliveryscheme
    gv_message := 'change_deliveryscheme';
		write_log('Calling Pack_Routetransaction.CHANGE_DELIVERYSCHEME...');

    lv_is_routeday_change := (FP_ROUTECHANGETRANSACTIONREC.drivingday_id <> FP_ROUTECHANGETRANSACTIONREC.newdrivingday_id)
    														or (FP_ROUTECHANGETRANSACTIONREC.route_id <> FP_ROUTECHANGETRANSACTIONREC.newroute_id);

    lv_route_id := FP_ROUTECHANGETRANSACTIONREC.route_id;

  --STEP 1: ADD the NEW DELIVERY SCHEME to CUSTOMER
    --add customer deliveryscheme
    add_customer_delscheme_days(fp_routechangetransactionrec.customer_id,
                                fp_routechangetransactionrec.newdeliveryscheme_id,
                                fp_routechangetransactionrec.newdrivingday_id);

  --STEP 2: for eligible ROUTESTOP(s) => REPLACE the OLD DELIVERYSCHEME (delete) with the NEW DELIVERYSCHEME (add)
    if (lv_route_id <> 0) then
    	for RS_FORREC in (
				select 	RS.routestop_id
         from   ROUTESTOP RS
         where  RS.route_id = lv_route_id
         and    RS.customer_id = FP_ROUTECHANGETRANSACTIONREC.customer_id
         and    (RS.deliverypoint_id = FP_ROUTECHANGETRANSACTIONREC.deliverypoint_id or FP_ROUTECHANGETRANSACTIONREC.deliverypoint_id is null)
         and 		FP_ROUTECHANGETRANSACTIONREC.changestartdate < RS.dateinactive
         and		exists (select 1
         					from	DELIVERYSCHEMESPERSTOP DSPS
                  where	DSPS.deliveryscheme_id = nvl(FP_ROUTECHANGETRANSACTIONREC.deliveryscheme_id, DSPS.deliveryscheme_id)
                --and   DSPS.customer_id = FP_ROUTECHANGETRANSACTIONREC.customer_id
                --and   DSPS.day_id = FP_ROUTECHANGETRANSACTIONREC.drivingday_id
                  and   DSPS.routestop_id = RS.routestop_id))
			loop
        begin
        --Remove the old delivery scheme from stop
          delete from DELIVERYSCHEMESPERSTOP DSPS
          where	DSPS.deliveryscheme_id = nvl(FP_ROUTECHANGETRANSACTIONREC.deliveryscheme_id, DSPS.deliveryscheme_id)
        --and   DSPS.customer_id = FP_ROUTECHANGETRANSACTIONREC.customer_id
        --and   DSPS.day_id = FP_ROUTECHANGETRANSACTIONREC.drivingday_id
          and   DSPS.routestop_id = RS_FORREC.routestop_id;

        --Add the new deliveryscheme to stop
          gv_message := 'insert into deliveryschemesperstop';
          insert into DELIVERYSCHEMESPERSTOP(
            customer_id, deliveryscheme_id, day_id,
            routestop_id,
            systemuser_id, timestamp)
          values(
            FP_ROUTECHANGETRANSACTIONREC.customer_id, FP_ROUTECHANGETRANSACTIONREC.newdeliveryscheme_id, FP_ROUTECHANGETRANSACTIONREC.newdrivingday_id,
            RS_FORREC.routestop_id,
            gv_defaultuserid, sysdate);

        exception
          when DUP_VAL_ON_INDEX then null;
        end;
    	end loop;
    end if;

  --STEP 3: INACTIVATE the OLD DELIVERYSCHEME
    --if there is no stop anymore then the CustomerDeliveryScheme is inactivated
    begin
      select	1
      into		lv_number
      from		DELIVERYSCHEMESPERSTOP DSPS
      join		ROUTESTOP RS on RS.routestop_id = DSPS.routestop_id
      where		DSPS.customer_id = FP_ROUTECHANGETRANSACTIONREC.customer_id
      and   	DSPS.day_id = FP_ROUTECHANGETRANSACTIONREC.drivingday_id
      and 		DSPS.deliveryscheme_id = nvl(FP_ROUTECHANGETRANSACTIONREC.deliveryscheme_id, DSPS.deliveryscheme_id)
      and   	DSPS.deliveryscheme_id <> FP_ROUTECHANGETRANSACTIONREC.newdeliveryscheme_id
      and 		FP_ROUTECHANGETRANSACTIONREC.changestartdate < RS.dateinactive;
		exception
      when NO_DATA_FOUND then
        update  CUSTOMERDELIVERYSCHEME CDS
        set     CDS.active = 'N',
                CDS.systemuser_id = gv_defaultuserid,
                CDS.timestamp = sysdate
        where   CDS.customer_id = FP_ROUTECHANGETRANSACTIONREC.customer_id
        and     CDS.day_id = FP_ROUTECHANGETRANSACTIONREC.drivingday_id
        and     CDS.deliveryscheme_id = nvl(FP_ROUTECHANGETRANSACTIONREC.deliveryscheme_id, CDS.deliveryscheme_id)
        and     CDS.deliveryscheme_id <> FP_ROUTECHANGETRANSACTIONREC.newdeliveryscheme_id;
      when TOO_MANY_ROWS then
        null;
		end;

  --STEP 4: CHANGE for STANDARD DELIVERIES
  --when there is a Route/Day change then Copy_Standarddeliverylines is run within TransNumber = 6
  	if (not lv_is_routeday_change) then
    --looping for old deliveryschemes and copy standarddeliverylines to new deliveryscheme
      --copy_standarddeliverylines(fp_routechangetransactionrec, fp_routechangetransactionrec.deliveryscheme_id);
      for DS_FORREC in (select DSPS.deliveryscheme_id
                        from   DELIVERYSCHEMESPERSTOP DSPS
                        join	 ROUTESTOP RS on RS.routestop_id = DSPS.routestop_id
												where	 RS.route_id = lv_route_id
                        and    RS.customer_id = FP_ROUTECHANGETRANSACTIONREC.customer_id
                        and    (RS.deliverypoint_id = FP_ROUTECHANGETRANSACTIONREC.deliverypoint_id or FP_ROUTECHANGETRANSACTIONREC.deliverypoint_id is null)
                        and    DSPS.deliveryscheme_id = nvl(FP_ROUTECHANGETRANSACTIONREC.deliveryscheme_id, DSPS.deliveryscheme_id)
                        and    DSPS.deliveryscheme_id <> FP_ROUTECHANGETRANSACTIONREC.newdeliveryscheme_id)
      loop
          copy_standarddeliverylines(fp_routechangetransactionrec, DS_FORREC.deliveryscheme_id);
      end loop;
    end if;

	--STEP 5: CHECK for FUTURE STOPS AND ROUTECONTROL
    if (lv_route_id <> 0) then
    	for RC_FORREC in (
        select  RC.*
        from    ROUTECONTROL RC
        where   RC.route_id = lv_route_id
        and   	FP_ROUTECHANGETRANSACTIONREC.changestartdate <= RC.drivingdate
        and     RC.packingstatus <= 2
        and     RC.invoicesgenerated = 1
        and     RC.pocketpcstatus = 0)
			loop
        for RS_FORREC in (
          select	*
          from    ROUTESTOP RS
          where   RS.route_id = lv_route_id
          and     RS.customer_id = FP_ROUTECHANGETRANSACTIONREC.customer_id
          and    	(RS.deliverypoint_id = FP_ROUTECHANGETRANSACTIONREC.deliverypoint_id or FP_ROUTECHANGETRANSACTIONREC.deliverypoint_id is null)
          and    	FP_ROUTECHANGETRANSACTIONREC.changestartdate < RS.dateinactive)
        loop
          begin
            begin
              select  nvl(stop_id, 0)
              into    lv_stop_id
              from    STOP
              where   routecontrol_id = RC_FORREC.routecontrol_id
              and     routestop_id = RS_FORREC.routestop_id
              and     (status = 0 or status = 3);
            exception
      				when NO_DATA_FOUND then
                lv_stop_id := 0;
            end;

            lv_routestop_is_valid := PACK_DELIVERYBASICS.ROUTESTOPVALID(
                                                RS_FORREC.RouteStop_ID, RS_FORREC.DeliveryPoint_ID, null, 1,
                                                SYSDATE, RC_FORREC.DrivingDate,
                                                null, null, 0, 0, 'N', true,
                                                lv_date, lv_number, lv_number, lv_number, lv_date);

            if (lv_stop_id <> 0 and not lv_routestop_is_valid) then
              update  STOP
              set     status = 1
              where   stop_id = lv_stop_id;
            end if;

            if (lv_stop_id = 0 and lv_routestop_is_valid) then
            	begin
                init_stop(LV_STOPREC);
                LV_STOPREC.routecontrol_id := RC_FORREC.routecontrol_id;
                LV_STOPREC.routestop_id := RS_FORREC.routestop_id;
                LV_STOPREC.customer_link_id := FP_ROUTECHANGETRANSACTIONREC.customer_id;
                LV_STOPREC.deliverypoint_id := RS_FORREC.deliverypoint_id;
                LV_STOPREC.stopnumber := RS_FORREC.stopnumber;
                LV_STOPREC.status := 0;
                LV_STOPREC.stoptype := 3;
                LV_STOPREC.contactperson_id := RS_FORREC.contactperson_id;
                LV_STOPREC.chargedeliveryfee := RS_FORREC.chargedeliveryfee;
                insert into STOP values LV_STOPREC;
              end;
            end if;
          end;
        end loop;
    	end loop;
    end if;

    write_log('Return from Pack_Routetransaction.CHANGE_DELIVERYSCHEME.');
  end change_deliveryscheme;

  procedure process_1_transaction(fp_routechangetransactionrec in routechangetransaction%rowtype) is
  begin
    --process_1_transaction
    gv_message := 'start process_1_transaction routechangetransaction_id ' ||
                  to_char(fp_routechangetransactionrec.routechangetransaction_id);
    case
      when fp_routechangetransactionrec.transactionnumber = 1 then
        new_route(fp_routechangetransactionrec);
      when fp_routechangetransactionrec.transactionnumber = 2 then
        change_depot(fp_routechangetransactionrec);
      when fp_routechangetransactionrec.transactionnumber = 3 then
        new_stop(fp_routechangetransactionrec);
      when fp_routechangetransactionrec.transactionnumber = 4 then
        remove_stopnumber(fp_routechangetransactionrec);
      when fp_routechangetransactionrec.transactionnumber = 5 then
        change_stopnumber(fp_routechangetransactionrec);
      when fp_routechangetransactionrec.transactionnumber = 6 then
        move_stop(fp_routechangetransactionrec);
      when fp_routechangetransactionrec.transactionnumber = 7 then
        change_deliveryscheme(fp_routechangetransactionrec);
      else
        begin
          gv_message := ' routechangetransaction transactionnumber must be between 1 and 7 it is ' ||
                        fp_routechangetransactionrec.transactionnumber;
          raise ge_wrongvalue;
        end;
    end case;
  end process_1_transaction;

    function create_routechangetransaction(fp_transaction             in routechangetransaction.transactionnumber%type,
                                           fp_changestartdate         in routechangetransaction.changestartdate%type,
                                           fp_customerid              in customer.customer_id%type,
                                           fp_deliverypointid         in deliverypoint.deliverypoint_id%type,
                                           fp_plantid                 in plant.plant_id%type,
                                           fp_routenumber             in route.routenumber%type,
                                           fp_drivingdayid            in day.daynumber%type,
                                           fp_depotcode               in depot.code%type,
                                           fp_routeid                 in route.route_id%type,
                                           fp_stopnumber              in routestop.stopnumber%type,
                                           fp_routestopid             in routestop.routestop_id%type,
                                           fp_oldroutelastdrivingdate in date,
                                           fp_deliveryschemeid        in deliveryscheme.deliveryscheme_id%type,
                                           fp_newdrivingdayid         in day.daynumber%type,
                                           fp_newroutenumber          in route.routenumber%type,
                                           fp_newstopnumber           in routestop.stopnumber%type,
                                           fp_newrouteid              in route.route_id%type,
                                           fp_newdeliveryschemeid     in deliveryscheme.deliveryscheme_id%type,
                                           fp_productgroupid          in productgroup.productgroup_id%type, 
                                           fp_sortSequence            in routestop.sortsequence%type default 0)
      return number is
      lv_routechangetransactionid routechangetransaction.routechangetransaction_id%type;
    begin
      gv_message := 'create routechangetransaction record';
      --get new routechangetransaction_id
      select seq_routechangetransaction.nextval into lv_routechangetransactionid from dual;
      --insert into routechangetransaction
      gv_message := 'insert routechangetransaction';
      insert into routechangetransaction
        (routechangetransaction_id, transactionnumber, status, errormsg, batchtransactionnumber,
         changestartdate, customer_id, deliverypoint_id, plant_id, routenumber, drivingday_id,
         depotcode, route_id, stopnumber, routestop_id, oldroutelastdrivingdate, deliveryscheme_id,
         newroute_id, newroutenumber, newstopnumber, newdrivingday_id, newdeliveryscheme_id,
         productgroup_id, creationtimestamp, timestamp, systemuser_id, sortSequence)
      values
        (lv_routechangetransactionid, fp_transaction, 0, null, 0, nvl(fp_changestartdate, sysdate),
         fp_customerid, fp_deliverypointid, fp_plantid, fp_routenumber, fp_drivingdayid,
         fp_depotcode, fp_routeid, fp_stopnumber, fp_routestopid, fp_oldroutelastdrivingdate,
         fp_deliveryschemeid, fp_newrouteid, fp_newroutenumber, fp_newstopnumber, fp_newdrivingdayid,
         fp_newdeliveryschemeid, fp_productgroupid, sysdate, sysdate, gv_defaultuserid, fp_sortSequence);

    write_log('Return from Pack_Routetransaction.CREATE_ROUTECHANGETRANSACTION with RoutechangetransactionId = '||lv_routechangetransactionid);

      return lv_routechangetransactionid;
    end create_routechangetransaction;

  procedure check_parameters(fp_transaction             in routechangetransaction.transactionnumber%type,
                             fp_changestartdate         in out routechangetransaction.changestartdate%type,
                             fp_customernumber          in customer.customernumber%type,
                             fp_deliverypointcode       in deliverypoint.code%type,
                             fp_plantcode               in plant.code%type,
                             fp_drivingday              in day.daynumber%type,
                             fp_routenumber             in route.routenumber%type,
                             fp_depotcode               in depot.code%type,
                             fp_stopnumber              in routestop.stopnumber%type,
                             fp_oldroutelastdrivingdate in routechangetransaction.oldroutelastdrivingdate%type,
                             fp_deliveryschemecode      in deliveryscheme.code%type,
                             fp_newdrivingday           in day.daynumber%type,
                             fp_newroutenumber          in route.routenumber%type,
                             fp_newstopnumber           in routestop.stopnumber%type,
                             fp_newdeliveryscheme       in deliveryscheme.code%type,
                             fp_productgroupcode        in productgroup.code%type,
                             fp_customerid              out customer.customer_id%type,
                             fp_deliverypointid         out deliverypoint.deliverypoint_id%type,
                             fp_plantid                 out plant.plant_id%type,
                             fp_routeid                 out route.route_id%type,
                             fp_depotid                 out depot.depot_id%type,
                             fp_routestopid             out routestop.routestop_id%type,
                             fp_deliveryschemeid        out deliveryscheme.deliveryscheme_id%type,
                             fp_newrouteid              out route.route_id%type,
                             fp_newdeliveryschemeid     out deliveryscheme.deliveryscheme_id%type,
                             fp_productgroupid          out productgroup.productgroup_id%type) is
    lv_intervalcheckmoveroute number;
    lv_route_deliveryschemeid number;
  begin
    write_log('Calling procedure Pack_Routetransaction.CHECK_PARAMETERS...');
    --check transaction number
    if (fp_transaction not between 1 and 7) or fp_transaction is null then
      gv_message := 'transaction must be between 1 and 7 and it is ' ||
                    nvl(to_char(fp_transaction), 'NULL');
      raise ge_wrongvalue;
    end if;
    --check change start date
    if fp_transaction > 2 then
      if (fp_changestartdate is null or trunc(fp_changestartdate) < trunc(sysdate)) then
        gv_message := 'change start date is wrong, must be >= sysdate it is ' ||
                      nvl(to_char(trunc(fp_changestartdate), 'dd-mon-yyyy'), 'NULL');
        raise ge_wrongvalue;
      end if;
    else
      --changestartdate = sysdate for transaction 1 and 2
      fp_changestartdate := trunc(sysdate);
    end if;
    --check customer number
    if fp_transaction > 2 then
      gv_message := 'customer number ' || nvl(to_char(fp_customernumber), 'NULL');
      select c.customer_id
      into   fp_customerid
      from   customer c
      where  c.customernumber = fp_customernumber;
    end if;
    --check deliverypoint code
    if fp_deliverypointcode is not null and fp_transaction > 2 then
      gv_message := 'deliverypoint code ' || fp_deliverypointcode;
      select dp.deliverypoint_id
      into   fp_deliverypointid
      from   deliverypoint dp
      where  dp.customer_id = fp_customerid
      and    dp.code = fp_deliverypointcode;
    else
      fp_deliverypointid := null;
    end if;
    --check plant code
    if fp_plantcode is not null then
      fp_plantid := get_plant_id(fp_plantcode);
    end if;
    --check driving day
    if fp_transaction in (2, 3, 4, 5, 6, 7) and
       ((fp_drivingday is null) or (fp_drivingday not between 1 and 7)) then
      gv_message := 'driving day is ' || nvl(to_char(fp_drivingday), 'NULL');
      raise ge_wrongvalue;
    end if;
    --check deliveryscheme
    if fp_deliveryschemecode is not null then
      gv_message := 'delivery scheme code ' || nvl(fp_deliveryschemecode, 'NULL');
      begin
        select ds.deliveryscheme_id
        into   fp_deliveryschemeid
        from   deliveryscheme ds
        where  ds.code = fp_deliveryschemecode;
      exception
        when no_data_found then
          gv_message := 'Delivery scheme code: ' || nvl(fp_deliveryschemecode, 'NULL') ||
                        ' does not exist! ';
          raise ge_wrongvalue;
      end;
    else
      fp_deliveryschemeid := null;
    end if;
    --check route number
    if fp_transaction in (2, 3, 4, 5, 6, 7) then
      begin
        gv_message := 'route number ' || nvl(to_char(fp_routenumber), 'NULL');
        if fp_routenumber is null then
          raise ge_wrongvalue;
        end if;
        fp_routeid := get_route_id(fp_routenumber, fp_drivingday, fp_plantid);
        if fp_deliveryschemeid is not null and fp_transaction = 2 then
          begin
            select r.deliveryscheme_id
            into   lv_route_deliveryschemeid
            from   route r
            where  r.route_id = fp_routeid;
            if fp_deliveryschemeid <> lv_route_deliveryschemeid then
              begin
                gv_message := 'route number ' || nvl(to_char(fp_routenumber), 'NULL') ||
                              ' has a different delivery scheme then ' || fp_deliveryschemecode;
                raise ge_wrongvalue;
              end;
            end if;
          end;
        end if;
      exception
        when no_data_found then
          raise ge_wrongvalue;
      end;
    end if;
    --check depot code
    if fp_transaction in (1, 2) and fp_depotcode is not null then
      fp_depotid := get_depot_id(fp_depotcode, fp_plantid);
    else
      fp_depotid := null;
    end if;
    --check routestopnumber
    if fp_transaction in (4, 5, 6, 7) then
      fp_routestopid := get_routestopid(fp_routeid, fp_customerid, fp_deliverypointid, fp_stopnumber,
                                        fp_changestartdate,
                                        case
                                           when length(fp_routenumber) = 5 and fp_transaction = 5 and
                                                gv_databasecode = 'LST' then
                                            99
                                           else
                                            fp_transaction
                                         end);
      if fp_deliveryschemeid is not null and fp_routestopid is not null then
        begin
          select dsps.deliveryscheme_id
          into   lv_route_deliveryschemeid
          from   deliveryschemesperstop dsps
          where  dsps.routestop_id = fp_routestopid
          and    dsps.deliveryscheme_id = fp_deliveryschemeid
          and    dsps.customer_id = fp_customerid
          and    dsps.day_id = fp_drivingday;
        exception
          when no_data_found then
            begin
              gv_message := 'route number ' || nvl(to_char(fp_routenumber), 'NULL') ||
                            ' stopnumber ' || nvl(to_char(fp_stopnumber), 'NULL') ||
                            ' has a different delivery scheme then ' || fp_deliveryschemecode;
              raise ge_wrongvalue;
            end;
        end;
      end if;
    else
      fp_routestopid := null;
    end if;
    --check oldroutelastdrivingdate
    if (fp_transaction in (4) and fp_oldroutelastdrivingdate is not null and
       fp_oldroutelastdrivingdate not between trunc(sysdate) and fp_changestartdate) then
      gv_message := 'old last route driving date ' ||
                    nvl(to_char(fp_oldroutelastdrivingdate, 'DD-MON-YYYY'), 'NULL') ||
                    ' is not between today and the changedate!';
      raise ge_wrongvalue;
    end if;
    --check for move routestop the interval chosen 26-09-2012
    if fp_transaction = 6 and gv_directstopchange = 0 and fp_newdeliveryscheme is null and
       gv_databasecode not in ('LST', 'AUJ', 'CBD', 'BNL', 'HOW') then
      begin
        select (nvl(min(ds.frequency), 1) * 7) - 1
        into   lv_intervalcheckmoveroute
        from   deliveryschemesperstop dsp
        join   deliveryscheme ds
        on     ds.deliveryscheme_id = dsp.deliveryscheme_id
        where  dsp.routestop_id = fp_routestopid;
        if fp_drivingday = fp_newdrivingday then
          --check oldroutelastdrivingdate
          if fp_oldroutelastdrivingdate < ((trunc(sysdate) - lv_intervalcheckmoveroute)) then
            begin
              gv_message := '*****   Route stop not moved, selected date ' ||
                            nvl(to_char(fp_oldroutelastdrivingdate, 'DD-MON-YYYY'), 'NULL') ||
                            ' must be bigger than ' ||
                            to_char((trunc(sysdate) - lv_intervalcheckmoveroute), 'DD-MON-YYYY') ||
                            '   *****';
              raise ge_wrongvalue;
            end;
          end if;
          if fp_changestartdate not between (fp_oldroutelastdrivingdate + 1) and
             (trunc(fp_oldroutelastdrivingdate + 1) + lv_intervalcheckmoveroute) then
            begin
              gv_message := '*****   Route stop not moved, selected date ' ||
                            nvl(to_char(fp_changestartdate, 'DD-MON-YYYY'), 'NULL') ||
                            ' is not between ' ||
                            to_char((fp_oldroutelastdrivingdate + 1), 'DD-MON-YYYY') || ' and ' ||
                            to_char((trunc(fp_oldroutelastdrivingdate + 1) +
                                    lv_intervalcheckmoveroute), 'DD-MON-YYYY') || '   *****';
              raise ge_wrongvalue;
            end;
          end if;
        else
          --check oldroutelastdrivingdate
          if fp_oldroutelastdrivingdate not between (trunc(sysdate) - lv_intervalcheckmoveroute) and
             trunc(sysdate) then
            begin
              gv_message := '*****   Route stop not moved, selected date ' ||
                            nvl(to_char(fp_oldroutelastdrivingdate, 'DD-MON-YYYY'), 'NULL') ||
                            ' is not between ' ||
                            to_char((trunc(sysdate) - lv_intervalcheckmoveroute), 'DD-MON-YYYY') ||
                            ' and ' || to_char(trunc(sysdate), 'DD-MON-YYYY') || '   *****';
              raise ge_wrongvalue;
            end;
          end if;
          if fp_changestartdate not between trunc(sysdate) and
             (trunc(sysdate) + lv_intervalcheckmoveroute) then
            begin
              gv_message := '*****   Route stop not moved, selected date ' ||
                            nvl(to_char(fp_changestartdate, 'DD-MON-YYYY'), 'NULL') ||
                            ' is not between ' || to_char(trunc(sysdate), 'DD-MON-YYYY') || ' and ' ||
                            to_char((trunc(sysdate) + lv_intervalcheckmoveroute), 'DD-MON-YYYY') ||
                            '   *****';
              raise ge_wrongvalue;
            end;
          end if;
        end if;
      end;
    end if;
    --en 26-09-2012
    --check deliveryscheme
    if (fp_transaction in (1, 6, 7) and fp_deliveryschemecode is not null) then
      gv_message := 'delivery scheme code ' || nvl(fp_deliveryschemecode, 'NULL');
      begin
        select ds.deliveryscheme_id
        into   fp_deliveryschemeid
        from   deliveryscheme ds
        where  ds.code = fp_deliveryschemecode;
      exception
        when no_data_found then
          gv_message := 'Delivery scheme code: ' || nvl(fp_deliveryschemecode, 'NULL') ||
                        ' does not exist! ';
          raise ge_wrongvalue;
      end;
    else
      fp_deliveryschemeid := null;
    end if;
    --check new driving day
    if (fp_transaction in (1, 5, 6, 7)) and
       ((fp_newdrivingday is null) or (fp_newdrivingday not between 1 and 7)) then
      gv_message := 'new driving day is ' || nvl(to_char(fp_newdrivingday), 'NULL');
      raise ge_wrongvalue;
    end if;
    --new route number
    if fp_transaction in (1, 5, 6, 7) and fp_newroutenumber is null then
      gv_message := 'new route number is NULL and must be filled for transaction ' ||
                    to_char(fp_transaction);
      raise ge_wrongvalue;
    end if;
    --get new route id if need be and it is there
    if (fp_transaction in (3, 5, 6, 7)) and fp_routenumber is not null then
      begin
        fp_newrouteid := get_route_id(fp_newroutenumber, fp_newdrivingday, fp_plantid);
      exception
        when no_data_found then
          fp_newrouteid := null;
      end;
    else
      fp_newrouteid := null;
    end if;
    --check new stop number
    if fp_transaction in (3, 5, 6, 7) and fp_newstopnumber is null then
      gv_message := 'new stop number is NULL and must be filled for transaction ' ||
                    to_char(fp_transaction);
      raise ge_wrongvalue;
    end if;
    --check new delivery scheme
    if fp_transaction in (1, 3, 6, 7) then
      gv_message := 'new delivery scheme ' || nvl(fp_newdeliveryscheme, 'NULL');
      if fp_transaction in (1, 3, 6) and fp_newdeliveryscheme is null then
        fp_newdeliveryschemeid := null;
      else
        begin
          select ds.deliveryscheme_id
          into   fp_newdeliveryschemeid
          from   deliveryscheme ds
          where  ds.code = fp_newdeliveryscheme;
        exception
          when no_data_found then
            gv_message := 'new delivery scheme code: ' || nvl(fp_newdeliveryscheme, 'NULL') ||
                          ' does not exist! ';
            raise ge_wrongvalue;
        end;
      end if;
    else
      fp_newdeliveryschemeid := null;
    end if;
    --check productgroup
    if fp_transaction > 5 and fp_productgroupcode is not null then
      begin
        select pg.productgroup_id
        into   fp_productgroupid
        from   productgroup pg
        where  pg.code = fp_productgroupcode;
      exception
        when no_data_found then
          gv_message := 'Product group code ' || fp_productgroupcode || ' does not exist!';
          raise ge_wrongvalue;
      end;
    else
      fp_productgroupid := null;
    end if;

    write_log('Return from Pack_Routetransaction.CHECK_PARAMETERS for TransactionNumber = '||fp_transaction);
  end check_parameters;

  procedure addroutechange(fp_changestartdate          in routechangetransaction.changestartdate%type,
                           fp_oldroutelastdrivingdate  in date,
                           fp_customernumber           in customer.customernumber%type,
                           fp_deliverypointcode        in deliverypoint.code%type,
                           fp_plantcode                in plant.code%type,
                           fp_drivingday               in day.daynumber%type,
                           fp_routenumber              in route.routenumber%type,
                           fp_depotcode                in depot.code%type,
                           fp_stopnumber               in routestop.stopnumber%type,
                           fp_deliveryschemecode       in deliveryscheme.code%type,
                           fp_newdrivingday            in day.daynumber%type,
                           fp_newroutenumber           in route.routenumber%type,
                           fp_newstopnumber            in routestop.stopnumber%type,
                           fp_newdeliveryscheme        in deliveryscheme.code%type,
                           fp_productgroupcode         in productgroup.code%type,
                           fp_errmsg                   out routechangetransaction.errormsg%type,
                           fp_routechangetransactionid out routechangetransaction.routechangetransaction_id%type) is
    lv_transaction         routechangetransaction.transactionnumber%type;
    lv_customerid          customer.customer_id%type;
    lv_deliverypointid     deliverypoint.deliverypoint_id%type;
    lv_plantid             plant.plant_id%type;
    lv_routeid             route.route_id%type;
    lv_depotid             depot.depot_id%type;
    lv_routestopid         routestop.routestop_id%type;
    lv_deliveryschemeid    deliveryscheme.deliveryscheme_id%type;
    lv_newrouteid          route.route_id%type;
    lv_newdeliveryschemeid deliveryscheme.deliveryscheme_id%type;
    lv_productgroupid      productgroup.productgroup_id%type;
    lv_changestartdate     routechangetransaction.changestartdate%type;

    procedure determine_transaction is
    begin
      --determine_transaction
      gv_message := 'determine_transaction';
      case
        when fp_drivingday is null and fp_routenumber is null and fp_stopnumber is null and
             fp_newroutenumber is not null then
          --add route
          lv_transaction := 1;
        when fp_drivingday is not null and fp_routenumber is not null and fp_stopnumber is null and
             fp_newstopnumber is null then
          --change depot
          lv_transaction := 2;
        when fp_drivingday is not null and fp_routenumber is not null and fp_stopnumber is null and
             fp_newstopnumber is not null then
          --add route stop
          lv_transaction := 3;
        when fp_drivingday is not null and fp_routenumber is not null and fp_stopnumber is not null and
             fp_newstopnumber is null and fp_newdeliveryscheme is null then
          --remove route stop
          lv_transaction := 4;
        when fp_drivingday is not null and fp_routenumber is not null and fp_stopnumber is not null and
             fp_newstopnumber is not null and fp_newdeliveryscheme is null and
             ((fp_newroutenumber is null and fp_newdrivingday is null) or
             (fp_newroutenumber = fp_routenumber and fp_newdrivingday = fp_drivingday)) then
          --change stop number within route
          lv_transaction := 5;
        when fp_drivingday is not null and fp_routenumber is not null and fp_stopnumber is not null and
             fp_newstopnumber is not null and fp_newdeliveryscheme is null then
          --move stop
          lv_transaction := 6;
        when fp_drivingday is not null and fp_newdeliveryscheme is not null then
          --change deliveryscheme
          lv_transaction := 7;
        else
          --error transaction not found!?
          lv_transaction := 0;
      end case;
    end determine_transaction;

  begin
    --write log
    write_log('Start procedure pack_routetransaction.addroutechange');
    --check parameters
    lv_changestartdate := fp_changestartdate;
    determine_transaction;
    check_parameters(lv_transaction, lv_changestartdate, fp_customernumber, fp_deliverypointcode,
                     fp_plantcode, fp_drivingday, fp_routenumber, fp_depotcode, fp_stopnumber,
                     fp_oldroutelastdrivingdate, fp_deliveryschemecode, fp_newdrivingday,
                     fp_newroutenumber, fp_newstopnumber, fp_newdeliveryscheme, fp_productgroupcode,
                     lv_customerid, lv_deliverypointid, lv_plantid, lv_routeid, lv_depotid,
                     lv_routestopid, lv_deliveryschemeid, lv_newrouteid, lv_newdeliveryschemeid,
                     lv_productgroupid);
    --create routetransaction
    fp_routechangetransactionid := create_routechangetransaction(lv_transaction, lv_changestartdate,
                                                                 lv_customerid, lv_deliverypointid,
                                                                 lv_plantid, fp_routenumber,
                                                                 fp_drivingday, fp_depotcode,
                                                                 lv_routeid, fp_stopnumber,
                                                                 lv_routestopid,
                                                                 fp_oldroutelastdrivingdate,
                                                                 lv_deliveryschemeid,
                                                                 fp_newdrivingday, fp_newroutenumber,
                                                                 fp_newstopnumber, lv_newrouteid,
                                                                 lv_newdeliveryschemeid,
                                                                 lv_productgroupid);
    --when all is ok
    commit;
    write_log('Procedure pack_routetransaction.addroutechange ran succesfully. For transaction ' ||
              lv_transaction);
  exception
    when no_data_found then
      begin
        fp_errmsg := 'Addroutechange Error No Data Found ' || gv_message;
        write_log(fp_errmsg, 3);
        rollback;
      end;
    when ge_wrongvalue then
      begin
        fp_errmsg := 'Addroutechange Error Wrong value ' || gv_message;
        write_log(fp_errmsg, 3);
        rollback;
      end;
    when dup_val_on_index then
      begin
        fp_errmsg := 'Addroutechange Error Duplicate Value On Index ' || gv_message;
        write_log(fp_errmsg, 3);
        rollback;
      end;
    when others then
      begin
        write_log(sqlerrm, 5);
        fp_errmsg := 'Addroutechange Error Others ' || gv_message;
        write_log(fp_errmsg, 3);
        fp_errmsg := fp_errmsg || ' ' || sqlerrm;
        rollback;
      end;
  end addroutechange;

  procedure getroutechangestatus(fp_routechangetransactionid in routechangetransaction.routechangetransaction_id%type,
                                 fp_errmsg                   out routechangetransaction.errormsg%type,
                                 fp_status                   out routechangetransaction.status%type) is
  begin
    --write log
    write_log('Start procedure PACK_ROUTETRANSACTION.addroutechange');
    --get record information
    fp_status := -1;
    fp_errmsg := 'routechangetransaction_id ' || fp_routechangetransactionid;
    --get information from routechangetransaction
    select rc.errormsg, rc.status
    into   fp_errmsg, fp_status
    from   routechangetransaction rc
    where  rc.routechangetransaction_id = fp_routechangetransactionid;
    --when all is ok
    write_log('Procedure PACK_ROUTETRANSACTION.getroutechangestatus ran succesfully');
  exception
    when no_data_found then
      begin
        fp_errmsg := 'Getroutechangestatus Invalid ID No record found ' || gv_message;
        write_log(fp_errmsg, 3);
      end;
    when ge_wrongvalue then
      begin
        fp_errmsg := 'Getroutechangestatus Wrong value ' || gv_message;
        write_log(fp_errmsg, 3);
      end;
    when dup_val_on_index then
      begin
        fp_errmsg := 'Getroutechangestatus Duplicate Value On Index ' || gv_message;
        write_log(fp_errmsg, 3);
      end;
    when others then
      begin
        write_log(sqlerrm, 5);
        fp_errmsg := 'Getroutechangestatus Error Others ' || gv_message;
        write_log(fp_errmsg, 3);
        fp_errmsg := fp_errmsg || ' ' || sqlerrm;
      end;
  end getroutechangestatus;

  procedure update_routechangetransaction(fp_batchtransactionnumber   in routechangetransaction.batchtransactionnumber%type,
                                          fp_status                   in routechangetransaction.status%type,
                                          fp_routechangetransactionid in routechangetransaction.routechangetransaction_id%type) is
  begin
    --update_routechangetransaction
    case
      when fp_status = 1 then
        --no errors
        update routechangetransaction z
        set    z.batchtransactionnumber = fp_batchtransactionnumber, z.timestamp = sysdate,
               z.status = fp_status, z.errormsg = null
        where  z.routechangetransaction_id = fp_routechangetransactionid;
      else
        --errors update with errormsg as well
        update routechangetransaction z
        set    z.batchtransactionnumber = fp_batchtransactionnumber, z.timestamp = sysdate,
               z.status = fp_status, z.errormsg = gv_message
        where  z.routechangetransaction_id = fp_routechangetransactionid;
    end case;
    commit;
  end update_routechangetransaction;

  procedure processroutechanges(fp_plantcode              in plant.code%type,
                                fp_batchtransactionnumber out routechangetransaction.batchtransactionnumber%type,
                                fp_status                 out routechangetransaction.status%type) is
    lv_plantid plant.plant_id%type;
    lv_counter number := 0;
  begin
    --write log
    write_log('Start procedure PACK_ROUTETRANSACTION.processroutechanges');
    fp_status := 1;
    --get plant id/check plant code
    if fp_plantcode is not null then
      lv_plantid := get_plant_id(fp_plantcode);
    end if;
    --get new batchtransactionnumber
    fp_batchtransactionnumber := get_max_batchtransactionnumber;
    --start processing records
    for x in (select *
              from   routechangetransaction rct
              where  rct.status = 0
              and    ((rct.plant_id = lv_plantid) or (fp_plantcode is null))
              order  by rct.routechangetransaction_id) loop
      begin
        fp_status := 1;
        process_1_transaction(x);
        update_routechangetransaction(fp_batchtransactionnumber, fp_status,
                                      x.routechangetransaction_id);
        lv_counter := lv_counter + 1;
      exception
        when no_data_found then
          begin
            gv_message := 'Processroutechanges No data found ' || gv_message || ' ' || sqlerrm || ' ' ||
                          ' routechangetransaction id ' || x.routechangetransaction_id;
            write_log(gv_message, 3);
            fp_status := 2;
            rollback;
            update_routechangetransaction(fp_batchtransactionnumber, fp_status,
                                          x.routechangetransaction_id);
          end;
        when ge_general then
          begin
            write_log('Processroutechanges ' || gv_message || ' routechangetransaction id ' ||
                      x.routechangetransaction_id || ' ' || sqlerrm, 3);
            fp_status := 2;
            rollback;
            update_routechangetransaction(fp_batchtransactionnumber, fp_status,
                                          x.routechangetransaction_id);
          end;
        when ge_wrongvalue then
          begin
            gv_message := 'Processroutechanges Error Wrong value ' || gv_message ||
                          ' routechangetransaction id ' || x.routechangetransaction_id || ' ' ||
                          sqlerrm;
            write_log(gv_message, 3);
            fp_status := 2;
            rollback;
            update_routechangetransaction(fp_batchtransactionnumber, fp_status,
                                          x.routechangetransaction_id);
          end;
        when dup_val_on_index then
          begin
            gv_message := 'Processroutechanges Error Duplicate Value On Index ' || gv_message || ' ' ||
                          sqlerrm || ' routechangetransaction id ' || x.routechangetransaction_id;
            write_log(gv_message, 3);
            fp_status := 2;
            rollback;
            update_routechangetransaction(fp_batchtransactionnumber, fp_status,
                                          x.routechangetransaction_id);
          end;
        when others then
          begin
            gv_message := 'Processroutechanges Error Others ' || sqlerrm || ' ' || gv_message ||
                          ' routechangetransaction id ' || x.routechangetransaction_id;
            write_log(gv_message, 3);
            fp_status := 2;
            rollback;
            update_routechangetransaction(fp_batchtransactionnumber, fp_status,
                                          x.routechangetransaction_id);
          end;
      end;
    end loop;
    write_log('Procedure PACK_ROUTETRANSACTION.processroutechanges ran succesfully, ' ||
              lv_counter || ' rows processed');
  exception
    when no_data_found then
      begin
        gv_message := 'Processroutechanges Error No data found ' || gv_message;
        write_log(gv_message, 3);
        fp_status := 2;
        rollback;
      end;
    when ge_general then
      begin
        write_log('Processroutechanges ' || gv_message, 3);
        fp_status := 2;
        rollback;
      end;
    when ge_wrongvalue then
      begin
        gv_message := 'Processroutechanges Error Wrong value ' || gv_message;
        write_log(gv_message, 3);
        fp_status := 2;
        rollback;
      end;
    when dup_val_on_index then
      begin
        gv_message := 'Processroutechanges Error Duplicate Value On Index ' || gv_message || ' ' ||
                      sqlerrm;
        write_log(gv_message, 3);
        fp_status := 2;
        rollback;
      end;
    when others then
      begin
        gv_message := 'Processroutechanges Error Others ' || sqlerrm || ' ' || gv_message;
        write_log(gv_message, 3);
        fp_status := 2;
        rollback;
      end;
  end processroutechanges;

  procedure update_routechangetrans_batch(fp_batchtransactionnumber in routechangetransaction.batchtransactionnumber%type,
                                          fp_status                 in routechangetransaction.status%type) is
  begin
    gv_message := gv_message || ' : update_routechangetrans_batch';
    --update_routechangetransaction
    for x in (select rct.routechangetransaction_id
              from   routechangetransaction rct
              where  rct.batchtransactionnumber = fp_batchtransactionnumber
              order  by rct.routechangetransaction_id) loop
      begin
        case
          when fp_status = 1 then
            --no errors
            update routechangetransaction z
            set    z.timestamp = sysdate, z.status = fp_status, z.errormsg = null
            where  z.routechangetransaction_id = x.routechangetransaction_id;
          else
            --errors update with errormsg as well
            update routechangetransaction z
            set    z.timestamp = sysdate, z.status = fp_status, z.errormsg = gv_message
            where  z.routechangetransaction_id = x.routechangetransaction_id;
        end case;
      end;
    end loop;
    commit;
  end update_routechangetrans_batch;

  procedure reprocessroutechanges(fp_batchtransactionnumber in routechangetransaction.batchtransactionnumber%type,
                                  fp_status                 out routechangetransaction.status%type) is
    lv_counter number := 0;
  begin
    --write log
    write_log('Start procedure PACK_ROUTETRANSACTION.reprocessroutechanges');
    if fp_batchtransactionnumber not between 0 and 8 or fp_batchtransactionnumber is null then
      gv_message := 'Invalid transaction number';
      raise ge_wrongvalue;
    end if;
    --start processing records
    for x in (select *
              from   routechangetransaction rct
              where  rct.batchtransactionnumber = fp_batchtransactionnumber
              and    rct.status in (2, 3)
              order  by rct.routechangetransaction_id) loop
      begin
        process_1_transaction(x);
        lv_counter := lv_counter + 1;
      end;
    end loop;
    --when all is ok
    fp_status := 1;
    update_routechangetrans_batch(fp_batchtransactionnumber, fp_status);
    write_log('Procedure PACK_ROUTETRANSACTION.reprocessroutechanges ran succesfully, ' ||
              lv_counter || ' rows processed');
  exception
    when no_data_found then
      begin
        gv_message := 'Reprocessroutechanges Error No Data Found ' || gv_message;
        write_log(gv_message, 3);
        fp_status := 2;
        rollback;
        update_routechangetrans_batch(fp_batchtransactionnumber, fp_status);
      end;
    when ge_general then
      begin
        write_log('Reprocessroutechanges ' || gv_message, 3);
        fp_status := 2;
        rollback;
        update_routechangetrans_batch(fp_batchtransactionnumber, fp_status);
      end;
    when ge_wrongvalue then
      begin
        gv_message := 'Reprocessroutechanges Error Wrong value ' || gv_message;
        write_log(gv_message, 3);
        fp_status := 2;
        rollback;
        update_routechangetrans_batch(fp_batchtransactionnumber, fp_status);
      end;
    when dup_val_on_index then
      begin
        gv_message := 'Reprocessroutechanges Error Duplicate Value On Index ' || gv_message || ' ' ||
                      sqlerrm;
        write_log(gv_message, 3);
        fp_status := 2;
        rollback;
        update_routechangetrans_batch(fp_batchtransactionnumber, fp_status);
      end;
    when others then
      begin
        gv_message := 'Reprocessroutechanges Error Others ' || sqlerrm || ' ' || gv_message;
        write_log(gv_message, 3);
        fp_status := 2;
        rollback;
        update_routechangetrans_batch(fp_batchtransactionnumber, fp_status);
      end;
  end reprocessroutechanges;

  procedure update_onlineroutechanges(fp_routechangetransactionid in routechangetransaction.routechangetransaction_id%type,
                                      fp_onlineroutechange        in number,
                                      fp_errmsg                   in routechangetransaction.errormsg%type) is
  begin
    gv_message := gv_message || ' update_onlineroutechanges routechangetransaction_id ' || fp_routechangetransactionid;
    update routechangetransaction z
    set    z.timestamp = sysdate, z.status = fp_onlineroutechange, z.errormsg = fp_errmsg,
           z.systemuser_id = gv_defaultuserid
    where  z.routechangetransaction_id = fp_routechangetransactionid;
    --commit;
  end update_onlineroutechanges;

  procedure add_routechanges(fp_changestartdate          in routechangetransaction.changestartdate%type,
                             fp_oldroutelastdrivingdate  in date,
                             fp_customernumber           in customer.customernumber%type,
                             fp_deliverypointcode        in deliverypoint.code%type,
                             fp_plantcode                in plant.code%type,
                             fp_drivingday               in day.daynumber%type,
                             fp_routenumber              in route.routenumber%type,
                             fp_depotcode                in depot.code%type,
                             fp_stopnumber               in routestop.stopnumber%type,
                             fp_deliveryschemecode       in deliveryscheme.code%type,
                             fp_newdrivingday            in day.daynumber%type,
                             fp_newroutenumber           in route.routenumber%type,
                             fp_newstopnumber            in routestop.stopnumber%type,
                             fp_newdeliveryscheme        in deliveryscheme.code%type,
                             fp_productgroupcode         in productgroup.code%type,
                             fp_sortSequence             in routestop.sortsequence%type,
                             fp_errmsg                   out routechangetransaction.errormsg%type,
                             fp_routechangetransid_table out RouteChangeTransactionIdTable)
  is
    lv_routechangetransid  routechangetransaction.routechangetransaction_id%type;
    lv_transactionnumber   routechangetransaction.transactionnumber%type;
    lv_customerid          customer.customer_id%type;
    lv_deliverypointid     deliverypoint.deliverypoint_id%type;
    lv_plantid             plant.plant_id%type;
    lv_routeid             route.route_id%type;
    lv_depotid             depot.depot_id%type;
    lv_routestopid         routestop.routestop_id%type;
    lv_deliveryschemeid    deliveryscheme.deliveryscheme_id%type;
    lv_newrouteid          route.route_id%type;
    lv_newdeliveryschemeid deliveryscheme.deliveryscheme_id%type;
    lv_productgroupid      productgroup.productgroup_id%type;
    lv_changestartdate     routechangetransaction.changestartdate%type;
    lv_logmessage  				 abslog.message%type;
    lv_movestopmoveup_idx			number := 0;
    lv_movestopmoveup_table 	MovestopmoveupStopNumberTable;

    procedure add_rct_number(fp_transactionnumber routechangetransaction.transactionnumber%type) is
      idx_rct integer;
    begin
      idx_rct := FP_ROUTECHANGETRANSID_TABLE.count + 1;
      FP_ROUTECHANGETRANSID_TABLE(idx_rct) := fp_transactionnumber;
    end add_rct_number;

    procedure push_movestopmoveup_stopnumber(fp_stopnumber routestop.stopnumber%type) is
    begin
      lv_movestopmoveup_idx := lv_movestopmoveup_idx + 1;
      LV_MOVESTOPMOVEUP_TABLE(lv_movestopmoveup_idx) := fp_stopnumber;
    end push_movestopmoveup_stopnumber;

    procedure pop_movestopmoveup_stopnumber(fp_stopnumber out routestop.stopnumber%type,
      																		  fp_newstopnumber out routestop.stopnumber%type) is
    begin
      fp_stopnumber := LV_MOVESTOPMOVEUP_TABLE(lv_movestopmoveup_idx);
      if (lv_movestopmoveup_idx < LV_MOVESTOPMOVEUP_TABLE.count) then
      	fp_newstopnumber := LV_MOVESTOPMOVEUP_TABLE(lv_movestopmoveup_idx + 1);
      else
				fp_newstopnumber := fp_stopnumber + 10;
      end if;
      lv_movestopmoveup_idx := lv_movestopmoveup_idx - 1;
    end pop_movestopmoveup_stopnumber;

    procedure determine_movestopmoveup(fp_routenumber				in route.routenumber%type,
                                       fp_stopnumber    		in routestop.stopnumber%type)
    is
    lv_plantid         	 plant.plant_id%type;
    lv_routeid         	 route.route_id%type;
    begin
      if (gv_SS_MOVESTOPMOVEUP = 0 or fp_stopnumber = 0) then
        return;
      end if;
      lv_plantid := get_plant_id(fp_plantcode);
      lv_routeid := get_route_id(fp_newroutenumber, fp_newdrivingday, lv_plantid);
      lv_movestopmoveup_idx := 0;
			for RS_FORREC in (
          select RS.routestop_id, RS.stopnumber
          from   ROUTESTOP RS,
          			 ROUTE R
          where  RS.route_id = R.route_id
          and	 R.routenumber = fp_routenumber
          and	 R.route_id = lv_routeid 
          and 	 RS.stopnumber >= fp_stopnumber
          order by RS.stopnumber)
      loop
              add_rct_number(5);
          push_movestopmoveup_stopnumber(RS_FORREC.stopnumber);
      end loop;
    end determine_movestopmoveup;

    function move_stop_moveup(fp_plantid          in plant.plant_id%type,
                               fp_drivingdayid		in day.daynumber%type,
                               fp_routeid         in route.route_id%type,
                               fp_routenumber     in route.routenumber%type)
    return number
    is
      lv_customerid     		customer.customer_id%type;
      lv_deliverypointid		deliverypoint.deliverypoint_id%type;
      lv_stopnumber       	routestop.stopnumber%type;
      lv_newstopnumber      routestop.stopnumber%type;
      lv_routestopid				routestop.routestop_id%type;
      lv_routechangetransid	routechangetransaction.routechangetransaction_id%type;

    begin
      pop_movestopmoveup_stopnumber(lv_stopnumber, lv_newstopnumber);
      select 	RS.customer_id,
              RS.deliverypoint_id,
              RS.routestop_id
       into 	lv_customerid,
              lv_deliverypointid,
              lv_routestopid
       from   ROUTESTOP RS
       where  RS.route_id = fp_routeid
       and 		RS.stopnumber = lv_stopnumber;
       lv_routechangetransid := create_routechangetransaction(5, sysdate,
                                                              lv_customerid, lv_deliverypointid,
                                                              fp_plantid, fp_routenumber,
                                                              fp_drivingdayid, null,
                                                              fp_routeid, lv_stopnumber,
                                                              lv_routestopid,
                                                              null,
                                                              null,
                                                              null, null, lv_newstopnumber, null, null,
                                                              '', fp_sortSequence);
			return lv_routechangetransid;
    end move_stop_moveup;

    procedure determine_transaction is
    begin
    --determine_transaction
      gv_message := 'determine_transaction';
      write_log('Calling procedure Pack_Routetransaction.DETERMINE_TRANSACTION...');
      case
        when fp_drivingday is null and fp_routenumber is null and fp_stopnumber is null and
             fp_newroutenumber is not null then
        --add route
          add_rct_number(1);

        when fp_drivingday is not null and fp_routenumber is not null and fp_stopnumber is null and
             fp_newstopnumber is null then
        --change depot
          add_rct_number(2);

        when fp_drivingday is not null and fp_routenumber is not null and fp_stopnumber is null and
             fp_newstopnumber is not null then
        --add route stop
          add_rct_number(3);

        when fp_drivingday is not null and fp_routenumber is not null and fp_stopnumber is not null and
             fp_newstopnumber is null and fp_newdeliveryscheme is null then
        --remove route stop
          add_rct_number(4);

        when (fp_drivingday is not null) and (fp_routenumber is not null) and (fp_stopnumber is not null) and
          (fp_newdrivingday is not null and fp_newroutenumber is not null) and
          (fp_newstopnumber is not null)
        then
          if ((fp_newdrivingday = fp_drivingday) and (fp_newroutenumber = fp_routenumber))
          then
            if (fp_newstopnumber <> fp_stopnumber) then
            --change stop number within route
              add_rct_number(5);
            end if;
            if (fp_newdeliveryscheme is not null) then
            --change deliveryscheme
              add_rct_number(7);
            end if;
          else
          --change stop number(s) when movestopmoveup
            determine_movestopmoveup(fp_newroutenumber, fp_newstopnumber);
          --move stop
            add_rct_number(6);
            if (fp_newdeliveryscheme is not null) then
            --change deliveryscheme
              add_rct_number(7);
            end if;
          end if;
      end case;
    end determine_transaction;

  begin
    --write log
    write_log('Calling procedure Pack_Routetransaction.ADD_ROUTECHANGES...');

    lv_changestartdate := fp_changestartdate;

    --add the RCT Number(s)
    determine_transaction;

    for idx_rct in FP_ROUTECHANGETRANSID_TABLE.first .. FP_ROUTECHANGETRANSID_TABLE.last loop
      lv_transactionnumber := FP_ROUTECHANGETRANSID_TABLE(idx_rct);

    --validate parameters and fill out the next using local parameters
      check_parameters(lv_transactionnumber, lv_changestartdate, fp_customernumber, fp_deliverypointcode,
                       fp_plantcode, fp_drivingday, fp_routenumber, fp_depotcode, fp_stopnumber,
                       fp_oldroutelastdrivingdate, fp_deliveryschemecode, fp_newdrivingday,
                       fp_newroutenumber, fp_newstopnumber, fp_newdeliveryscheme, fp_productgroupcode,
                       lv_customerid, lv_deliverypointid, lv_plantid, lv_routeid, lv_depotid,
                       lv_routestopid, lv_deliveryschemeid, lv_newrouteid, lv_newdeliveryschemeid,
                       lv_productgroupid);

    --create routetransaction
    	if (FP_ROUTECHANGETRANSID_TABLE(idx_rct) = 5 and lv_movestopmoveup_idx > 0) then
	    	lv_routechangetransid := move_stop_moveup(lv_plantid, fp_newdrivingday, lv_newrouteid, fp_newroutenumber);
      else
        lv_routechangetransid := create_routechangetransaction(lv_transactionnumber, lv_changestartdate,
                                                               lv_customerid, lv_deliverypointid,
                                                               lv_plantid, fp_routenumber,
                                                               fp_drivingday, fp_depotcode,
                                                               lv_routeid, fp_stopnumber,
                                                               lv_routestopid,
                                                               fp_oldroutelastdrivingdate,
                                                               lv_deliveryschemeid,
                                                               fp_newdrivingday, fp_newroutenumber,
                                                               fp_newstopnumber, lv_newrouteid,
                                                               lv_newdeliveryschemeid,
                                                               lv_productgroupid, fp_sortSequence);
			end if;
    --replace in table the RCT Number with respective generated ID
      FP_ROUTECHANGETRANSID_TABLE(idx_rct) := lv_routechangetransid;
    end loop;

    --when all is ok
    commit;

    --write log
    lv_logmessage := 'Procedure Pack_Routetransaction.ADD_ROUTECHANGES ran succesfully for transactions: ';
    for idx_rct in FP_ROUTECHANGETRANSID_TABLE.first .. FP_ROUTECHANGETRANSID_TABLE.last loop
      lv_logmessage := lv_logmessage || FP_ROUTECHANGETRANSID_TABLE(idx_rct) || ',';
    end loop;
    write_log(lv_logmessage);

  exception
    when no_data_found then
      begin
        fp_errmsg := 'Addroutechange Error No Data Found ' || gv_message;
        write_log(fp_errmsg, 3);
        rollback;
      end;
    when ge_wrongvalue then
      begin
        fp_errmsg := 'Addroutechange Error Wrong value ' || gv_message;
        write_log(fp_errmsg, 3);
        rollback;
      end;
    when dup_val_on_index then
      begin
        fp_errmsg := 'Addroutechange Error Duplicate Value On Index ' || gv_message;
        write_log(fp_errmsg, 3);
        rollback;
      end;
    when others then
      begin
        write_log(sqlerrm, 5);
        fp_errmsg := 'Addroutechange Error Others ' || gv_message;
        write_log(fp_errmsg, 3);
        fp_errmsg := fp_errmsg || ' ' || sqlerrm;
        rollback;
      end;
  end add_routechanges;

  procedure onlineroutechange(fp_changestartdate         in routechangetransaction.changestartdate%type,
                              fp_oldroutelastdrivingdate in date,
                              fp_customernumber          in customer.customernumber%type,
                              fp_deliverypointcode       in deliverypoint.code%type,
                              fp_plantcode               in plant.code%type,
                              fp_drivingday              in day.daynumber%type,
                              fp_routenumber             in route.routenumber%type,
                              fp_depotcode               in depot.code%type,
                              fp_stopnumber              in routestop.stopnumber%type,
                              fp_deliveryschemecode      in deliveryscheme.code%type,
                              fp_newdrivingday           in day.daynumber%type,
                              fp_newroutenumber          in route.routenumber%type,
                              fp_newstopnumber           in routestop.stopnumber%type,
                              fp_newdeliveryscheme       in deliveryscheme.code%type,
                              fp_productgroupcode        in productgroup.code%type,
                              fp_sortSequence            in routestop.sortsequence%type,
                              fp_errmsg                  out routechangetransaction.errormsg%type,
                              fp_onlineroutechange       out number,
                              fp_systemuserid            in systemuser.systemuser_id%type default 0)
  is
    LV_ROUTECHANGETRANSID_TABLE  RouteChangeTransactionIdTable;
    lv_errmsg                    routechangetransaction.errormsg%type;
    lv_routechangetransactionrec routechangetransaction%rowtype;

  begin
    --write log
    write_log('Start procedure Pack_Routetransaction.ONLINEROUTECHANGE');
    --initialize systemuser TD21292
    gv_defaultuserid := fp_systemuserid;
    --get System Setting MOVEDELIVEREDSTOP and MOVESTOPMOVEUP
    gv_ssmovedeliveredstop := getssmovedeliveredstop(fp_plantcode);
    get_ss_movestopmoveup(fp_plantcode);

    --add route change
    add_routechanges(fp_changestartdate, fp_oldroutelastdrivingdate, fp_customernumber,
                   fp_deliverypointcode, fp_plantcode, fp_drivingday, fp_routenumber, fp_depotcode,
                   fp_stopnumber, fp_deliveryschemecode, fp_newdrivingday, fp_newroutenumber,
                   fp_newstopnumber, fp_newdeliveryscheme, fp_productgroupcode, fp_sortSequence, lv_errmsg,
                     LV_ROUTECHANGETRANSID_TABLE);
    --check if it went ok
    if (lv_errmsg is not null) or (LV_ROUTECHANGETRANSID_TABLE.count = 0) then
      if (LV_ROUTECHANGETRANSID_TABLE.count = 0) then
        gv_message := 'No any Transaction Number identified to process ...';
        raise no_data_found;
      end if;
    --rollback;
      fp_errmsg            := lv_errmsg;
      fp_onlineroutechange := 0;
      update_onlineroutechanges(lv_routechangetransactionrec.routechangetransaction_id, fp_onlineroutechange, fp_errmsg);
      commit;

		else
      --process transactions
      for idx_rct in LV_ROUTECHANGETRANSID_TABLE.first .. LV_ROUTECHANGETRANSID_TABLE.last loop
        select *
        into   lv_routechangetransactionrec
        from   routechangetransaction rct
        where  rct.routechangetransaction_id = LV_ROUTECHANGETRANSID_TABLE(idx_rct);

        process_1_transaction(lv_routechangetransactionrec);

        --when all is ok
        fp_onlineroutechange := 1;
        update_onlineroutechanges(lv_routechangetransactionrec.routechangetransaction_id, fp_onlineroutechange, null);

        write_log('Procedure Pack_Routetransaction.ONLINEROUTECHANGE processed 1 row for ID = [ '
              ||lv_routechangetransactionrec.routechangetransaction_id||' ].');
      end loop;

      commit;
      write_log('Procedure Pack_Routetransaction.ONLINEROUTECHANGE finished succesfully');
		end if;

  exception
    when no_data_found then
      begin
        gv_message := 'Onlineroutechange Error Others ' || gv_message;
        write_log(gv_message, 3);
        fp_errmsg            := gv_message;
        fp_onlineroutechange := 2;
        rollback;
        update_onlineroutechanges(lv_routechangetransactionrec.routechangetransaction_id,
                                  fp_onlineroutechange, gv_message);
        commit;
      end;
    when ge_general then
      begin
        write_log('Onlineroutechange ' || gv_message, 3);
        fp_errmsg            := gv_message;
        fp_onlineroutechange := 2;
        rollback;
        update_onlineroutechanges(lv_routechangetransactionrec.routechangetransaction_id,
                                  fp_onlineroutechange, gv_message);
        commit;
      end;
    when ge_wrongvalue then
      begin
        gv_message := 'Onlineroutechange Error Wrong value ' || gv_message;
        write_log(gv_message, 3);
        fp_errmsg            := gv_message;
        fp_onlineroutechange := 2;
        rollback;
        update_onlineroutechanges(lv_routechangetransactionrec.routechangetransaction_id,
                                  fp_onlineroutechange, gv_message);
        commit;
      end;
    when dup_val_on_index then
      begin
        gv_message := 'Onlineroutechange Error Duplicate Value On Index ' || gv_message || ' ' ||
                      sqlerrm;
        write_log(gv_message, 3);
        fp_errmsg            := gv_message;
        fp_onlineroutechange := 2;
        rollback;
        update_onlineroutechanges(lv_routechangetransactionrec.routechangetransaction_id,
                                  fp_onlineroutechange, gv_message);
        commit;
      end;
    when others then
      begin
        gv_message := 'Onlineroutechange Error Others ' || sqlerrm || ' ' || gv_message;
        write_log(gv_message, 3);
        fp_errmsg            := gv_message;
        fp_onlineroutechange := 2;
        rollback;
        update_onlineroutechanges(lv_routechangetransactionrec.routechangetransaction_id,
                                  fp_onlineroutechange, gv_message);
        commit;
      end;
  end onlineroutechange;

  procedure cancelroutechange(fp_routechangetransactionid in routechangetransaction.routechangetransaction_id%type,
                              fp_status                   out routechangetransaction.status%type) is
    lv_routechangetransactionid routechangetransaction.routechangetransaction_id%type;
  begin
    --check if exists
    select rct.routechangetransaction_id
    into   lv_routechangetransactionid
    from   routechangetransaction rct
    where  rct.routechangetransaction_id = fp_routechangetransactionid
    and    rct.status <> 1;
    --if exists update
    fp_status := 4;
    update_onlineroutechanges(lv_routechangetransactionid, fp_status,
                              'Canceled routechangetransaction');
    commit;
    write_log('Procedure PACK_ROUTETRANSACTION.cancelroutechange ran succesfully, 1 row cancelled');
  exception
    when no_data_found then
      begin
        write_log('Procedure PACK_ROUTETRANSACTION.cancelroutechange Error RouteChangeTransactionID does not exist!',
                  3);
        fp_status := -1;
        rollback;
      end;
    when others then
      begin
        write_log('Procedure PACK_ROUTETRANSACTION.cancelroutechange Error others ' || sqlerrm, 3);
        fp_status := -1;
        rollback;
      end;
  end cancelroutechange;

begin
  --initialize
  --get system setting parameter for STOPNRUNIQUE
  gv_message := 'get STOPNRUNIQUE';
  select nvl(max(st.value), 0)
  into   gv_stopnrunique
  from   systemsetting st
  where  st.name = 'STOPNRUNIQUE';
  --get DIRECTSTOPCHANGE value
  gv_message := 'get DIRECTSTOPCHANGE';
  select nvl(max(st.value), 0)
  into   gv_directstopchange
  from   systemsetting st
  where  st.name = 'DIRECTSTOPCHANGE';
  --get database code
  select code into gv_databasecode from databasesettings;
end pack_routetransaction;
/
