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.0
  --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;

  --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;
      else
        begin
          lv_garments := true;
          lv_linen    := true;
          lv_sales    := true;
        end;
      end if;
      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;
      --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
              from   stockcountscheduledist scdist
              join   distributionpoint dp
              on     dp.distributionpoint_id = scdist.distributionpoint_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 or nvl(fp_webuserid, -1) = -1)
                              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 or nvl(fp_webuserid, -1) = -1)
                       and    wcust.customer_id = t.customer_id))
              group  by scdist.distributionpoint_id, dp.department_id) loop
      begin
        --getcreateinternetorder
        getcreateinternetorder(x.distributionpoint_id);
        --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)
          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)
          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,
                          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
                                                 gv_deldaynrgarm
                                                else
                                                 gv_deldaynrlin
                                              end
                                       and    case p.typeofmerchandise
                                                when 2 then
                                                 gv_deldategarm
                                                else
                                                 gv_deldatelin
                                              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    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
                                                 gv_deldaynrgarm
                                                else
                                                 gv_deldaynrlin
                                                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;
/
