create or replace procedure generateinternetorders(fp_customerid           in customer.customer_id%type,
                                                   fp_stockcountscheduleid in stockcountschedule.stockcountschedule_id%type,
                                                   fp_language1_id         in languages.language_id%type,
                                                   fp_language2_id         in languages.language_id%type,
                                                   fp_webuserid            in webuser.webuser_id%type,
                                                   fp_fromdepartmentno     in number,
                                                   fp_todepartmentno       in number,
                                                   fp_order_date           in date,
                                                   fp_systemuserid         in systemuser.systemuser_id%type,
                                                   fp_reportrunid          in number) is
  --Version 2.1
  --ABS-54324 Redesign of generateinternetorders procedure
  gv_systemuserid         systemuser.systemuser_id%type := null;
  gv_plantid              plant.plant_id%type := null;
  gv_message              abslog.message%type;
  gv_ioh_id               internetorder.internetorder_id%type;
  gv_tmlinen              integer;
  gv_tmgarm               integer;
  gv_tmsales              integer;
  gv_sizestar_id          sizedefinition.sizedefinition_id%type;
  gv_databasecode         databasesettings.code%type;
  gv_deldaynrlin          integer;
  gv_deldatelin           date;
  gv_delweeklin           number;
  gv_deldaynrgarm         number;
  gv_delweekgarm          number;
  gv_deldategarm          date;
  gv_deldaynr             number;
  gv_deldatelindeldeterm  date;
  gv_deldaylindeldeterm   number;
  gv_deldategarmdeldeterm date;
  gv_deldaygarmdeldeterm  number;

  --internal procedures
 procedure write_log(fp_message in varchar2, fp_priority in number) is
  begin
    /* Logging: write message in ABSLOG with these priorities for fp_priority:
    1 = Message
    2 = Warning
    3 = Error */
    abslogwrite.abslog_write(5, 'GENERATEINTERNETORDERS', gv_plantid, fp_priority, fp_message,
                             gv_systemuserid);
    begin
      dbms_output.put_line(fp_message);
    exception
      when others then
        null;
    end;
  end write_log;

  procedure initialize is
    procedure determine_toms is
      lv_accesssales    webuser.accesssalesitems%type;
      lv_accessgarments webuser.accessgarments%type;
      lv_accesslinen    webuser.accessnongarments%type;
      lv_linen          boolean;
      lv_garments       boolean;
      lv_sales          boolean;
    begin
      --determine_toms
      gv_message := 'determine_toms';
      --webuser used
      if (fp_webuserid > -1) then
        begin
          --select WebUser record to determine the type of merchandise
          gv_message := 'webuser id ' || fp_webuserid;
          select w.accessgarments, w.accessnongarments, w.accesssalesitems
          into   lv_accessgarments, lv_accesslinen, lv_accesssales
          from   webuser w
          where  w.webuser_id = fp_webuserid;
          --Determine which TOM can be used
          if (lv_accessgarments = 'Y') then
            lv_garments := true;
          else
            lv_garments := false;
          end if;
          if (lv_accesslinen = 'Y') then
            lv_linen := true;
          else
            lv_linen := false;
          end if;
          if (lv_accesssales = 'Y') then
            lv_sales := true;
          else
            lv_sales := false;
          end if;
        end;
        begin
          --select CustomerInternetOptions to determine type of merchandise
          gv_message := 'customerinternetoptions for customer id ' || fp_customerid;
          select cio.ordergarments, cio.orderlinen, cio.ordersalesitems
          into   lv_accessgarments, lv_accesslinen, lv_accesssales
          from   customerinternetoptions cio
          where  cio.customer_id = fp_customerid;
          --Determine which TOM can be used
          if (lv_garments and lv_accessgarments = 'Y') then
            lv_garments := true;
          else
            lv_garments := false;
          end if;
          if (lv_linen and lv_accesslinen = 'Y') then
            lv_linen := true;
          else
            lv_linen := false;
          end if;
          if (lv_sales and lv_accesssales = 'Y') then
            lv_sales := true;
          else
            lv_sales := false;
          end if;
        exception
          when no_data_found then
            begin
              if not (fp_webuserid > -1) then
                begin
                  lv_garments := true;
                  lv_linen    := true;
                  lv_sales    := true;
                end;
              end if;
            end;
        end;
      else
        begin
          lv_garments := true;
          lv_linen    := true;
          lv_sales    := true;
        end;
      end if;
      --set allowed types of merchandise
      if (lv_garments = true) then
        gv_tmgarm := 2;
      else
        gv_tmgarm := 0;
      end if;
      if (lv_linen = true) then
        gv_tmlinen := 1;
      else
        gv_tmlinen := 0;
      end if;
      if (lv_sales = true) then
        gv_tmsales := 3;
      else
        gv_tmsales := 0;
      end if;
    end determine_toms;
  begin
    --initialize
    --determine_toms
    determine_toms;
    --get size *
    gv_message := 'get size *';
    select s.sizedefinition_id into gv_sizestar_id from sizedefinition s where s.code = '*';
    --get databasesettings code for setting long product description -> todo 23837 for BCM
    gv_message := 'get database setting';
    select dbs.code into gv_databasecode from databasesettings dbs;
    --get day, week of delivery date for linen
    gv_message := 'get day/week number';
    begin
      gv_deldatelin := pack_sort.getnextdeliverydate(fp_order_date, fp_customerid, 1);
    exception
      when others then
        begin
          gv_deldatelin := fp_order_date;
        end;
    end;
    gv_delweeklin  := getisoweek(gv_deldatelin);
    gv_deldaynrlin := getabsdaynumber(gv_deldatelin);
    --get day, week of delivery date for garments
    begin
      gv_deldategarm := pack_sort.getnextdeliverydate(fp_order_date, fp_customerid, 2);
    exception
      when others then
        begin
          gv_deldategarm := fp_order_date;
        end;
    end;
    gv_delweekgarm  := getisoweek(gv_deldategarm);
    gv_deldaynrgarm := getabsdaynumber(gv_deldategarm);
    gv_deldaynr     := getabsdaynumber(fp_order_date);
  end initialize;

  procedure generate_orders is
  
    procedure getcreateinternetorder(fp_dip_id in number) is
      lv_internetorderstatus internetorder.status%type;
    begin
      --getcreateinternetorder
      gv_message := 'getcreateinternetorder for distributionpoint id ' || fp_dip_id;
      select io.internetorder_id, io.status
      into   gv_ioh_id, lv_internetorderstatus
      from   internetorder io
      where  io.distributionpoint_link_id = fp_dip_id
      and    io.orderdate = fp_order_date;
      if lv_internetorderstatus = 1 then
        begin
          --delete old lines, because we are going to do this again
          delete from internetorderline iol where iol.internetorder_id = gv_ioh_id;
        end;
      end if;
    exception
      when no_data_found then
        begin
          insert into internetorder
            (internetorder_id, orderdate, status, distributionpoint_link_id, systemuser_id,
             timestamp)
          values
            (seq_internetorder.nextval, fp_order_date, 1, fp_dip_id, fp_systemuserid, sysdate)
          returning internetorder_id into gv_ioh_id;
        end;
    end getcreateinternetorder;
  begin
    --generate_orders
    gv_message := 'generate_orders';
    --loop stockcountscheduledist / distributionpoint
    for x in (select scdist.distributionpoint_id, dp.department_id, max(t.occupancy) occupancy,
                     scs.deliverydaydetermination
              from   stockcountscheduledist scdist
              join   distributionpoint dp
              on     dp.distributionpoint_id = scdist.distributionpoint_id
              join   stockcountschedule scs
              on     scs.stockcountschedule_id = scdist.stockcountschedule_id
              join   department t
              on     t.department_id = dp.department_id
              where  dp.active = 'Y'
              and    t.active = 'Y'
              and    t.customer_id = fp_customerid
              and    (scdist.stockcountschedule_id = fp_stockcountscheduleid or
                    nvl(fp_stockcountscheduleid, -1) = -1)
              and    ((t.departmentnumber between fp_fromdepartmentno and fp_todepartmentno) or
                    nvl(fp_fromdepartmentno, -1) = -1)
              and    ((dp.department_id in
                    (select rp.number_value
                        from   reportparam rp
                        where  rp.reportparam_id = fp_reportrunid)) or
                    (nvl(fp_reportrunid, -1) = -1))
              and    exists
               (select 1
                      from   stockcountscheduleday scsd
                      where  scsd.stockcountschedule_id = scdist.stockcountschedule_id
                      and    scsd.day_id = gv_deldaynr)
              and    (exists (select 1
                              from   webuserdepartment wdep
                              join   webusercustomer wcust
                              on     wcust.webusercustomer_id = wdep.webusercustomer_id
                              where  wdep.department_id = t.department_id
                              and    (wcust.webuser_id = fp_webuserid)
                              and    wcust.customer_id = t.customer_id) or not exists
                     (select 1
                       from   webuserdepartment wdep
                       join   webusercustomer wcust
                       on     wcust.webusercustomer_id = wdep.webusercustomer_id
                       where  (wcust.webuser_id = fp_webuserid)
                       and    wcust.customer_id = t.customer_id))
              group  by scdist.distributionpoint_id, dp.department_id, scs.deliverydaydetermination) loop
      begin
        --getcreateinternetorder
        getcreateinternetorder(x.distributionpoint_id);
        if x.deliverydaydetermination = 1 then
          gv_deldatelindeldeterm  := determine_delivery_date(fp_customerid, gv_deldaynr,
                                                             fp_stockcountscheduleid, fp_webuserid,
                                                             fp_fromdepartmentno, fp_todepartmentno,
                                                             fp_reportrunid, fp_order_date,
                                                             x.distributionpoint_id, 1);
          gv_deldaylindeldeterm   := getabsdaynumber(gv_deldatelindeldeterm);
          gv_deldategarmdeldeterm := determine_delivery_date(fp_customerid, gv_deldaynr,
                                                             fp_stockcountscheduleid, fp_webuserid,
                                                             fp_fromdepartmentno, fp_todepartmentno,
                                                             fp_reportrunid, fp_order_date,
                                                             x.distributionpoint_id, 2);
          gv_deldaygarmdeldeterm  := getabsdaynumber(gv_deldategarmdeldeterm);
        end if;
        --loop productlistperdistribpoint / standarddeliveryline
        --insert into internetorderline
        gv_message := 'insert into internetorderline distributionpoint id ' ||
                      x.distributionpoint_id;
        insert into internetorderline
          (internetorderline_id, quantity, type, fixedamount, internetorder_id, product_link_id,
           sizedefinition_link_id, deliveryvariations_id, finishingmethod_id, productcode,
           systemuser_id, productdescription, timestamp, normstockamount, standarddeliveryamount,
           maxorderqty)
          select seq_internetorderline.nextval, null, y.orderingmethod, 'N', gv_ioh_id, y.product_id,
                 y.sizedefinition_id, null, y.finishingmethod_id, y.productcode, fp_systemuserid,
                 y.product_desc, sysdate, nvl(y.normstockamnt, 0), nvl(y.staamount, 0),
                 f_getmaxorderqty(y.plpd_id, fp_order_date)
          from   (select nvl(max((select l.customcode
                                   from   productlistpercustomer l
                                   where  l.productlistpercustomer_id = stl.productlistpercustomer_id)),
                               max(p.code)) productcode, plpd.product_id, plpd.sizedefinition_id,
                          plpd.finishingmethod_id, p.typeofmerchandise, plpd.orderingmethod,
                          max(plpd.productlistperdistribpoint_id) plpd_id,
                          sum((select sum(nsa.amount)
                               from   normstockamount nsa
                               where  nsa.productlistperdistribpoint_id =
                                      plpd.productlistperdistribpoint_id
                               and    plpd.orderingmethod <> 2
                               and    nsa.day_id = gv_deldaynr)) normstockamnt,
                          round(sum(((select sum(sta.amount)
                                       from   standarddeliveryamount sta
                                       where  sta.standarddeliveryline_id = stl.standarddeliveryline_id
                                       and    sta.day_id = case p.typeofmerchandise
                                                when 2 then
                                                 case x.deliverydaydetermination
                                                   when 1 then
                                                    gv_deldaygarmdeldeterm
                                                   else
                                                    gv_deldaynrgarm
                                                 end
                                                else
                                                 case x.deliverydaydetermination
                                                   when 1 then
                                                    gv_deldaylindeldeterm
                                                   else
                                                    gv_deldaynrlin
                                                 end
                                              end
                                       and    case p.typeofmerchandise
                                               when 2 then
                                                case x.deliverydaydetermination
                                                  when 1 then
                                                   gv_deldategarmdeldeterm
                                                  else
                                                   gv_deldategarm
                                                end
                                               else
                                                case x.deliverydaydetermination
                                                  when 1 then
                                                   gv_deldatelindeldeterm
                                                  else
                                                   gv_deldatelin
                                                end
                                             end between stl.dateactive and stl.dateinactive
                                       and    (stl.season_id is null or
                                             (stl.season_id is not null and exists
                                              (select s.season_id
                                                 from   seasonweek sw
                                                 join   season s
                                                 on     s.season_id = sw.season_id
                                                 where  sw.weeknumber = case
                                                          when p.typeofmerchandise = 2 then
                                                           gv_delweekgarm
                                                          else
                                                           gv_delweeklin
                                                        end
                                                 and    s.season_id = stl.season_id)))
                                       and    not exists
                                        (select dd.day_id
                                               from   deliveryday dd
                                               where  dd.department_id = x.department_id
                                               and    dd.day_id = case p.typeofmerchandise
                                                        when 2 then
                                                         case x.deliverydaydetermination
                                                           when 1 then
                                                            gv_deldaygarmdeldeterm
                                                           else
                                                            gv_deldaynrgarm
                                                         end
                                                        else
                                                         case x.deliverydaydetermination
                                                           when 1 then
                                                            gv_deldaylindeldeterm
                                                           else
                                                            gv_deldaynrlin
                                                         end
                                                      end)) * x.occupancy) / 100)) staamount,
                          case
                            when gv_databasecode = 'BCM' then
                             coalesce(max((select pd.description
                                          from   product_desc pd
                                          where  pd.product_id = plpd.product_id
                                          and    pd.language_id = fp_language1_id)),
                                      max((select pd.description
                                           from   product_desc pd
                                           where  pd.product_id = plpd.product_id
                                           and    pd.language_id = fp_language2_id)),
                                      (max((select l.customdescription
                                            from   productlistpercustomer l
                                            where  l.productlistpercustomer_id =
                                                   stl.productlistpercustomer_id))))
                            else
                             coalesce((max((select l.customdescription
                                           from   productlistpercustomer l
                                           where  l.productlistpercustomer_id =
                                                  stl.productlistpercustomer_id))),
                                      max((select pd.description
                                           from   product_desc pd
                                           where  pd.product_id = plpd.product_id
                                           and    pd.language_id = fp_language1_id)),
                                      max((select pd.description
                                           from   product_desc pd
                                           where  pd.product_id = plpd.product_id
                                           and    pd.language_id = fp_language2_id)))
                          end product_desc
                   from   standarddeliveryline stl
                   join   productlistperdistribpoint plpd
                   on     plpd.productlistperdistribpoint_id = stl.productlistperdistribpoint_id
                   join   product p
                   on     p.product_id = plpd.product_id
                   where  plpd.distributionpoint_id = x.distributionpoint_id --loop for x.distributionpoint_id
                   and    fp_order_date between stl.dateactive and stl.dateinactive
                   and    plpd.sizedefinition_id != gv_sizestar_id
                   and    p.typeofmerchandise in (gv_tmlinen, gv_tmgarm, gv_tmsales)
                   and    exists (select 1
                           from   normstockamount nsa
                           where  nsa.productlistperdistribpoint_id =
                                  plpd.productlistperdistribpoint_id
                           and    nsa.day_id = gv_deldaynr)
                   and    plpd.orderingmethod in (1, 2)
                   group  by plpd.product_id, plpd.sizedefinition_id, plpd.finishingmethod_id,
                             p.typeofmerchandise, plpd.orderingmethod) y
          where  not exists (select 1
                  from   internetorderline il
                  where  il.internetorder_id = gv_ioh_id
                  and    il.product_link_id = y.product_id
                  and    il.sizedefinition_link_id = y.sizedefinition_id
                  and    il.finishingmethod_id = y.finishingmethod_id);
        --count lines
        if sql%rowcount = 0 then
          begin
            --remove internet order, because there are no lines
            gv_message := 'remove internet order with no lines gv_ioh_id ' || gv_ioh_id;
            delete internetorder io
            where  io.internetorder_id = gv_ioh_id
            and    not exists (select 1
                    from   internetorderline iol
                    where  iol.internetorder_id = io.internetorder_id);
          end;
        end if;
      end;
    end loop; --x
  end generate_orders;

  procedure finalize is
  begin
    --finalize
    gv_message := 'finalize';
    commit;
  end finalize;
begin
  --initialize
  initialize;
  --generate_orders
  generate_orders;
  --finalize
  finalize;
exception
  when no_data_found then
    begin
      rollback;
      write_log('No data found!' || gv_message, 3);
    end;
  when others then
    begin
      rollback;
      write_log(gv_message || ' ' || sqlerrm, 3);
    end;
end generateinternetorders;
/
