create or replace procedure generateinternetorders(customerid           customer.customer_id%type,
                                                   stockcountscheduleid stockcountschedule.stockcountschedule_id%type,
                                                   language1_id         languages.language_id%type,
                                                   language2_id         languages.language_id%type,
                                                   webuserid            webuser.webuser_id%type,
                                                   fromdeaprtmentno     number,
                                                   todepartmentno       number,
                                                   order_date           date,
                                                   systemuserid         systemuser.systemuser_id%type,
                                                   reportrunid          number) is
  --Version 1.2
  --ABS-11726 Allocation Redesign for Web and Linen Assistant PDA
  weeknr number;
  yearnr number;
  type iohcurtyp is ref cursor;
  ioh_cursor           iohcurtyp;
  sql_stmt             varchar2(1500);
  dip_id               distributionpoint.distributionpoint_id%type;
  dep_id               department.department_id%type;
  dep_occupancy        department.occupancy%type;
  accesssales          webuser.accesssalesitems%type;
  accessgarments       webuser.accessgarments%type;
  accesslinen          webuser.accessnongarments%type;
  linen                boolean;
  garments             boolean;
  sales                boolean;
  ioh_id               internetorder.internetorder_id%type;
  daynr                integer;
  tmlinen              integer;
  tmgarm               integer;
  tmsales              integer;
  ordermeth            productlistperdistribpoint.orderingmethod%type;
  prodid               product.product_id%type;
  sizeid               sizedefinition.sizedefinition_id%type;
  sizestar_id          sizedefinition.sizedefinition_id%type;
  finmeth              finishingmethod.finishingmethod_id%type;
  prodcode             productlistpercustomer.customcode%type;
  pcode                productlistpercustomer.customcode%type;
  custcode             productlistpercustomer.customcode%type;
  custdescr            productlistpercustomer.customdescription%type;
  proddesc             product_desc.longdescription%type;
  proddesc1            product_desc.description%type;
  proddesc2            product_desc.description%type;
  long_proddesc1       product_desc.longdescription%type;
  long_proddesc2       product_desc.longdescription%type;
  prodtom              product.typeofmerchandise%type;
  normstockamnt        normstockamount.amount%type;
  stdamnt              standarddeliveryamount.amount%type;
  deldate              date;
  delday               integer;
  packoncntday         stockcountschedule.packoncountday%type;
  plpdpid              productlistperdistribpoint.productlistperdistribpoint_id%type;
  departmentid         department.department_id%type;
  deliverdayon         stockcountschedule.deliverydaydetermination%type;
  stockdeliveryday     stockcountscheduleday.deliveryday_id%type;
  foundstockscheduleid stockcountschedule.stockcountschedule_id%type;
  databasecode         databasesettings.code%type;

  cursor vcursor(langid1    languages.language_id%type,
                 langid2    languages.language_id%type,
                 custid     customer.customer_id%type,
                 dipid      distributionpoint.distributionpoint_id%type,
                 mydate     productlistpercustomer.dateactive%type,
                 sizestarid sizedefinition.sizedefinition_id%type,
                 dayno      day.daynumber%type,
                 linen      product.typeofmerchandise%type,
                 garm       product.typeofmerchandise%type,
                 sales      product.typeofmerchandise%type) is
    select x.orderingmethod, x.product_id, x.sizedefinition_id, x.finishingmethod_id, x.code,
           x.typeofmerchandise, l.customcode, l.customdescription,
           (select pd.longdescription
             from   product_desc pd
             where  pd.product_id = x.product_id
             and    pd.language_id = langid1 ),
           (select pd.longdescription
             from   product_desc pd
             where  pd.product_id = x.product_id
             and    pd.language_id = langid2 ),
           (select pd1.description
             from   product_desc pd1
             where  pd1.product_id = x.product_id
             and    pd1.language_id = langid1),
           (select pd2.description
             from   product_desc pd2
             where  pd2.product_id = x.product_id
             and    pd2.language_id = langid2), x.dayamount, x.productlistperdistribpoint_id
    from   productlistpercustomer l,
           (select plpdp.orderingmethod, plpdp.product_id, plpdp.sizedefinition_id,
                    plpdp.finishingmethod_id,
                    (coalesce((select a.productlistpercustomer_id
                               from   productlistpercustomer a
                               where  a.customer_id = custid
                               and    a.product_id = plpdp.product_id
                               and    a.sizedefinition_id = plpdp.sizedefinition_id
                               and    a.finishingmethod_id = plpdp.finishingmethod_id
                               and    a.dateactive <= mydate
                               and    a.dateinactive >= mydate),
                               (select b.productlistpercustomer_id
                                 from   productlistpercustomer b
                                 where  b.customer_id = custid
                                 and    b.product_id = plpdp.product_id
                                 and    b.sizedefinition_id = sizestarid
                                 and    b.finishingmethod_id = plpdp.finishingmethod_id
                                 and    b.dateactive <= mydate
                                 and    b.dateinactive >= mydate))) productlistpercustomer_id,
                    product.code, product.typeofmerchandise,
                    (select sum(nsa.amount)
                      from   normstockamount nsa, day
                      where  nsa.productlistperdistribpoint_id = plpdp.productlistperdistribpoint_id
                      and    nsa.day_id = day.day_id
                      and    day.daynumber = dayno) dayamount, plpdp.productlistperdistribpoint_id
             from   productlistperdistribpoint plpdp, product
             where  plpdp.distributionpoint_id = dipid
             and    plpdp.product_id = product.product_id
             and    product.typeofmerchandise in (linen, garm, sales)
             and
              (select /*+index(sdl XIF799STANDARDDELIVERYLINE)*/
                     count(sdl.standarddeliveryline_id)
                     from   standarddeliveryline sdl
                     where  sdl.productlistperdistribpoint_id = plpdp.productlistperdistribpoint_id
                     and    sdl.dateactive <= mydate
                     and    sdl.dateinactive >= mydate) > 0
             and    
              (select count(nsa.productlistperdistribpoint_id)
                     from   normstockamount nsa, day
                     where  nsa.productlistperdistribpoint_id = plpdp.productlistperdistribpoint_id
                     and    nsa.day_id = day.day_id
                     and    day.daynumber = dayno) > 0
             and    plpdp.orderingmethod in (1, 2)
             and    plpdp.sizedefinition_id != sizestarid) x
    where  l.productlistpercustomer_id = x.productlistpercustomer_id
    and    l.customer_id = custid
    and    l.dateactive <= mydate
    and    l.dateinactive >= mydate;

  function isdepartmentdeliveryday(depid department.department_id%type, delday day.day_id%type)
    return boolean is
    depdelday deliveryday.day_id%type;
  begin
    begin
      select dd.day_id
      into   depdelday
      from   deliveryday dd
      where  dd.department_id = depid
      and    dd.day_id = delday;
    exception
      when no_data_found then
        return true;
    end;
    if depdelday = delday then
      return false;
    end if;
  end isdepartmentdeliveryday;

begin
  --get day, week , year of order_date
  daynr  := getabsdaynumber(order_date);
  weeknr := getisoweek(order_date);
  yearnr := getyearofweek(order_date);
  --get size *
  select s.sizedefinition_id into sizestar_id from sizedefinition s where s.code = '*';
  --get databasesettings code for setting long product description -> todo 23837 for BCM
  select dbs.code into databasecode from databasesettings dbs;
  --create dynamic sql statement for creation of InternetOrder record
  if (stockcountscheduleid = -1) then
    --get all active distribution points per customer
    sql_stmt := 'SELECT DIP.DISTRIBUTIONPOINT_ID, DEP.DEPARTMENT_ID, DEP.OCCUPANCY,SC.STOCKCOUNTSCHEDULE_ID
FROM DEPARTMENT DEP
JOIN DISTRIBUTIONPOINT DIP ON DIP.DEPARTMENT_ID = DEP.DEPARTMENT_ID
JOIN STOCKCOUNTSCHEDULEDIST SCSDIST ON SCSDIST.DISTRIBUTIONPOINT_ID = DIP.DISTRIBUTIONPOINT_ID
JOIN STOCKCOUNTSCHEDULE SC ON SC.STOCKCOUNTSCHEDULE_ID=SCSDIST.STOCKCOUNTSCHEDULE_ID
JOIN STOCKCOUNTSCHEDULEDAY SCDAY ON (SCDAY.STOCKCOUNTSCHEDULE_ID = SC.STOCKCOUNTSCHEDULE_ID AND SC.CUSTOMER_ID = DEP.CUSTOMER_ID)
WHERE DIP.ACTIVE = ''Y''
AND DEP.CUSTOMER_ID = :c
AND DEP.ACTIVE = ''Y''
AND SCDAY.DAY_ID = :day
AND SC.REPLENISHMENTTYPE = 0';
  else
    --get all active distribution points in stock count schedule
    sql_stmt := 'SELECT DIP.DISTRIBUTIONPOINT_ID, DIP.DEPARTMENT_ID, DEP.OCCUPANCY, SC.STOCKCOUNTSCHEDULE_ID
FROM STOCKCOUNTSCHEDULEDIST SCSDIST
JOIN STOCKCOUNTSCHEDULE SC ON SC.STOCKCOUNTSCHEDULE_ID = SCSDIST.STOCKCOUNTSCHEDULE_ID
JOIN DISTRIBUTIONPOINT DIP ON DIP.DISTRIBUTIONPOINT_ID = SCSDIST.DISTRIBUTIONPOINT_ID
JOIN DEPARTMENT DEP ON DEP.DEPARTMENT_ID = DIP.DEPARTMENT_ID
WHERE SC.REPLENISHMENTTYPE = 0
AND DIP.ACTIVE = ''Y''
AND SCSDIST.STOCKCOUNTSCHEDULE_ID = :s 
AND DEP.ACTIVE = ''Y''';
  end if;
  if (webuserid > -1) then
    sql_stmt := sql_stmt || ' AND (EXISTS (SELECT 1
FROM WEBUSERDEPARTMENT WDEP 
JOIN WEBUSERCUSTOMER WCUST ON WCUST.WEBUSERCUSTOMER_ID = WDEP.WEBUSERCUSTOMER_ID
WHERE WDEP.DEPARTMENT_ID = DEP.DEPARTMENT_ID
AND WCUST.WEBUSER_ID = :w
AND WCUST.CUSTOMER_ID = DEP.CUSTOMER_ID)
OR NOT EXISTS (SELECT 1
FROM WEBUSERDEPARTMENT WDEP 
JOIN WEBUSERCUSTOMER WCUST ON WCUST.WEBUSERCUSTOMER_ID = WDEP.WEBUSERCUSTOMER_ID
WHERE WCUST.WEBUSER_ID = :w
AND WCUST.CUSTOMER_ID = DEP.CUSTOMER_ID)) ';
  end if;
  if (fromdeaprtmentno > -1) then
    sql_stmt := sql_stmt || ' and DEP.DEPARTMENTNUMBER >= :d1 and DEP.DEPARTMENTNUMBER <= :d2 ';
  else
    if (reportrunid > -1) then
      sql_stmt := sql_stmt ||
                  ' and DEP.DEPARTMENT_ID in
                (SELECT T.NUMBER_VALUE FROM REPORTPARAM T WHERE T.REPORTPARAM_ID = ' ||
                  reportrunid || ')';
    end if;
  end if;
  if (webuserid > -1) then
    begin
      --select WebUser record to determine the type of merchandise
      select w.accessgarments, w.accessnongarments, w.accesssalesitems
      into   accessgarments, accesslinen, accesssales
      from   webuser w
      where  w.webuser_id = webuserid;

      if (accessgarments = 'Y') then
        garments := true;
      else
        garments := false;
      end if;

      if (accesslinen = 'Y') then
        linen := true;
      else
        linen := false;
      end if;

      if (accesssales = 'Y') then
        sales := true;
      else
        sales := false;
      end if;

      --select CustomerInternetOptions to determine type of merchandise
      select cio.ordergarments, cio.orderlinen, cio.ordersalesitems
      into   accessgarments, accesslinen, accesssales
      from   customerinternetoptions cio
      where  cio.customer_id = customerid;

      if (garments and accessgarments = 'Y') then
        garments := true;
      else
        garments := false;
      end if;

      if (linen and accesslinen = 'Y') then
        linen := true;
      else
        linen := false;
      end if;

      if (sales and accesssales = 'Y') then
        sales := true;
      else
        sales := false;
      end if;
    end;
  else
    begin
      garments := true;
      linen    := true;
      sales    := true;
    end;
  end if;

  --set allowed types of merchandise
  if (garments = true) then
    tmgarm := 2;
  else
    tmgarm := 0;
  end if;

  if (linen = true) then
    tmlinen := 1;
  else
    tmlinen := 0;
  end if;

  if (sales = true) then
    tmsales := 3;
  else
    tmsales := 0;
  end if;

  --select distribution points for InternetOrder
  if (webuserid = -1 and fromdeaprtmentno = -1) then
    if (stockcountscheduleid = -1) then
      open ioh_cursor for sql_stmt
        using customerid, daynr;
    else
      open ioh_cursor for sql_stmt
        using stockcountscheduleid;
    end if;
  else
    if (webuserid > -1 and fromdeaprtmentno = -1) then
      if (stockcountscheduleid = -1) then
        open ioh_cursor for sql_stmt
          using customerid, daynr, webuserid, webuserid;
      else
        open ioh_cursor for sql_stmt
          using stockcountscheduleid, webuserid, webuserid;
      end if;
    else
      if (webuserid = -1 and fromdeaprtmentno > -1) then
        if (stockcountscheduleid = -1) then
          open ioh_cursor for sql_stmt
            using customerid, daynr, fromdeaprtmentno, todepartmentno;
        else
          open ioh_cursor for sql_stmt
            using stockcountscheduleid, fromdeaprtmentno, todepartmentno;
        end if;
      else
        if (stockcountscheduleid = -1) then
          open ioh_cursor for sql_stmt
            using customerid, daynr, webuserid, webuserid, fromdeaprtmentno, todepartmentno;
        else
          open ioh_cursor for sql_stmt
            using stockcountscheduleid, webuserid, webuserid, fromdeaprtmentno, todepartmentno;
        end if;
      end if;
    end if;
  end if;

  fetch ioh_cursor
    into dip_id, dep_id, dep_occupancy, foundstockscheduleid;

  while ioh_cursor%found loop
    begin
      --502541 delete already existing new internet orders
      --21989 special for ANG problem
      delete from internetorder o
      where  o.distributionpoint_link_id = dip_id
      and    o.orderdate = order_date
      and    (o.status = 1 or o.systemuser_id = -1);
      --create InternetOrder
      begin
        insert into internetorder
          (internetorder_id, orderdate, status, distributionpoint_link_id, systemuser_id, timestamp)
        values
          (seq_internetorder.nextval, order_date, 1, dip_id, systemuserid, sysdate)
        returning internetorder_id into ioh_id;
      exception
        when dup_val_on_index then
          --internetorder already exists, select it
          begin
            select io.internetorder_id
            into   ioh_id
            from   internetorder io
            where  io.distributionpoint_link_id = dip_id
            and    io.orderdate = order_date;
          end;
      end;

      --select productlistperdistribpoint records to create internetorderline records
      open vcursor(language1_id, language2_id, customerid, dip_id, order_date, sizestar_id, daynr,
                   tmlinen, tmgarm, tmsales);
      fetch vcursor
        into ordermeth, prodid, sizeid, finmeth, prodcode, prodtom, custcode, custdescr, long_proddesc1, long_proddesc2, proddesc1,
             proddesc2, normstockamnt, plpdpid;
      while vcursor%found loop
        begin
          --det product code
          if (custcode is null) then
            pcode := prodcode;
          else
            pcode := custcode;
          end if;
          --det product description
          if (custdescr is null) then
            if (proddesc1 is null) then
              proddesc := proddesc2;
            else
              proddesc := proddesc1;
            end if;
          else
            proddesc := custdescr;
          end if;
          if (databasecode = 'BCM') then -- set long product description for BCM
            if (long_proddesc1 is not null) then
              proddesc := long_proddesc1;
            else
              if (long_proddesc2 is not null) then
                proddesc := long_proddesc2;
              end if;
            end if;
          end if;
          --for ordering method = order the norm stock amount is always 0
          if (ordermeth = 2) then
            normstockamnt := 0;
          end if;
          select scs.packoncountday
          into   packoncntday
          from   stockcountschedule scs
          where  scs.stockcountschedule_id = foundstockscheduleid;
          select scs.deliverydaydetermination
          into   deliverdayon
          from   stockcountschedule scs
          where  scs.stockcountschedule_id = foundstockscheduleid;
          select scsd.deliveryday_id
          into   stockdeliveryday
          from   stockcountscheduleday scsd
          where  scsd.stockcountschedule_id = foundstockscheduleid
          and    scsd.day_id = daynr
          and    exists (select 1
                  from   stockcountscheduledist scsdist
                  where  scsdist.stockcountschedule_id = foundstockscheduleid
                  and    scsdist.distributionpoint_id = dip_id);
          if (deliverdayon = 1) then
            delday := stockdeliveryday;
            if (delday >= daynr) then
              deldate := getdateforweekyear(delday, weeknr, yearnr);
            else
              deldate := getdateforweekyear(delday, weeknr + 1, yearnr);
            end if;
          else
            begin
              if (prodtom = 3) then
                 deldate := pack_sort.getnextdeliverydate(order_date, customerid, 1);
              else
                 deldate := pack_sort.getnextdeliverydate(order_date, customerid, prodtom);
              end if;
              delday  := getabsdaynumber(deldate);
            exception
              when others then
                begin
                  delday := stockdeliveryday;
                  if (delday >= daynr) then
                    deldate := getdateforweekyear(delday, weeknr, yearnr);
                  else
                    deldate := getdateforweekyear(delday, weeknr + 1, yearnr);
                  end if;
                end;
            end;
          end if;
          select /*+index(SDL XIF799STANDARDDELIVERYLINE)*/
           sum(round((sda.amount * dep_occupancy) / 100))
          into   stdamnt
          from   standarddeliveryline sdl
          left   outer join deliveryscheme ds
          on     sdl.deliveryscheme_id = ds.deliveryscheme_id, standarddeliveryamount sda,
           customerdeliveryscheme cds, productlistperdistribpoint plpdp
          where  plpdp.productlistperdistribpoint_id = plpdpid
          and    sdl.productlistperdistribpoint_id = plpdpid
          and    sdl.standarddeliveryline_id = sda.standarddeliveryline_id
          and    sda.day_id = delday
          and    sdl.dateactive <= deldate
          and    sdl.dateinactive >= deldate
          and    sdl.productlistpercustomer_id is not null
          and    cds.customer_id = customerid
          and    cds.deliveryscheme_id = sdl.deliveryscheme_id
          and    cds.day_id = sda.day_id
          and    (isdeliveryweek(getyearofweek(deldate), getisoweek(deldate), ds.frequency,
                                 ds.offsetweek) = 'Y')
          and    (sdl.season_id is null or
                (sdl.season_id is not null and exists
                 (select season.season_id
                    from   seasonweek sw, season
                    where  season.season_id = sdl.season_id
                    and    sw.season_id = season.season_id
                    and    sw.weeknumber = getisoweek(deldate))))
          and    istemporarydeliverystop3(getyearofweek(deldate), getisoweek(deldate),
                                          sdl.standarddeliveryline_id, deldate) = 0;
          begin
            --when customer is not visited on the date then std amount = 0
            if (not pack_sort.iscustomervisitdate(customerid, deldate)) then
              stdamnt := 0;
            end if;
          exception
            when others then
              begin
                abslogwrite.abslog_write(5, 'GENERATEINTERNETORDERS', null, 1, sqlerrm , 0);
                --stdamnt := 0;
              end;
          end;
          --when no delivery days for department then std del amnt is 0
          select dp.department_id
          into   departmentid
          from   distributionpoint dp, productlistperdistribpoint plpd
          where  plpd.productlistperdistribpoint_id = plpdpid
          and    dp.distributionpoint_id = plpd.distributionpoint_id;
          if (not isdepartmentdeliveryday(departmentid, delday) or stdamnt is null) then
            stdamnt := 0;
          end if;
          --create InternetOrderLine
          begin
            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)
            values
              (seq_internetorderline.nextval, null, ordermeth, 'N', ioh_id, prodid, sizeid, null,
               finmeth, pcode, systemuserid, proddesc, sysdate, normstockamnt, stdamnt);
          exception
            when dup_val_on_index then
              null;
          end;
          fetch vcursor
            into ordermeth, prodid, sizeid, finmeth, prodcode, prodtom, custcode, custdescr, long_proddesc1, long_proddesc2,
                 proddesc1, proddesc2, normstockamnt, plpdpid;
        end;
      end loop;
      close vcursor;
      fetch ioh_cursor
        into dip_id, dep_id, dep_occupancy, foundstockscheduleid;
    end;
  end loop;
  close ioh_cursor;
  delete from internetorder io
  where  io.status = 1
  and    io.orderdate = order_date
  and not exists
   (select 1 from internetorderline iol where iol.internetorder_id = io.internetorder_id)
  and    io.distributionpoint_link_id in
         (select dp.distributionpoint_id
           from   distributionpoint dp
           where  dp.department_id in
                  (select d.department_id from department d where d.customer_id = customerid));
  commit;
exception
  when others then
    dbms_output.put_line(sqlcode || ' ' || sqlerrm);
    rollback;
end generateinternetorders;
/
