create or replace package guaranteedminimumdelivery is
  /*****************************************************************************************************************\
  * Package Guaranteed Minimum Delivery                                                                             *
  * Version 1.0                                                                                                     *
  *                                                                                                                 *
  * Release notes                                                                                                   *
  * 29-Nov-2018     ABS-71603 Create SQL Procedure to fill 'FILLDELIVERYLOADTABLES                                  '
  * 04-Feb-2019     ABS-71608 (New) Modules Guaranteed Delivery Minimum + Temporary Assignment of Pool Garments     *
  *                                                                                                                 *
  * Description:                                                                                                    *
  * Consists of 2 procedures                                                                                        *
  * - CreateDeliveryLoad creates deliveryload/detail                                                                *
  * - InitializeDeliveryLoad change status 1 to 2 and count personalizedavailableqty                                *
  \*****************************************************************************************************************/

  --CreateDeliveryLoad, creates deliveryload/detail
  procedure createdeliveryload(fp_plantid            in plant.plant_id%type,
                               fp_abslogprocessjobid in abslogprocessjob.abslogprocessjob_id%type default null,
                               fp_systemuserid       in systemuser.systemuser_id%type default 0);

  --InitializeDeliveryLoad, change deliveryload from status 1 to 2 and count personalizedavailableqty
  procedure initializedeliveryload(fp_deliveryloadid in deliveryload.deliveryload_id%type,
                                   fp_systemuserid   in systemuser.systemuser_id%type default 0);
end guaranteedminimumdelivery;
/
create or replace package body guaranteedminimumdelivery is

  procedure createdeliveryload(fp_plantid            in plant.plant_id%type,
                               fp_abslogprocessjobid in abslogprocessjob.abslogprocessjob_id%type default null,
                               fp_systemuserid       in systemuser.systemuser_id%type default 0) is
    gv_userexception exception;
    gv_abslogprocessjobpartid abslogprocessjobpart.abslogprocessjobpart_id%type;
    gv_message                abslog.message%type;
    gv_plantcode              plant.code%type;
    gv_process_desc           process.description%type;
    gv_guaranteedmindeldays   number;
    gv_maxbatchsize           number;
    gv_sizeall                sizedefinition.sizedefinition_id%type;
    gv_deliverydate           date;
    gv_deliveryday            number;
    gv_total_rowcount         number;
    gv_no_output_anymore      number := 0;

    ---internal procedure(s)
    procedure write_log(fp_message     in varchar2,
                        fp_priority    in number,
                        fp_partmessage in varchar2 default null) is
      procedure write_output is
      begin
        if gv_no_output_anymore = 0 then
          begin
            --write_output
            dbms_output.put_line(fp_message);
          end;
        end if;
      exception
        when others then
          begin
            gv_no_output_anymore := 1;
          end;
      end write_output;
    begin
      if fp_abslogprocessjobid is null then
        begin
          abslogwrite.abslog_write(5, 'createdeliveryload', fp_plantid, fp_priority, fp_message,
                                   fp_systemuserid);
        end;
      else
        begin
          --write_log abslogprocessjob
          if (gv_abslogprocessjobpartid is null or fp_partmessage is not null) then
            begin
              gv_abslogprocessjobpartid := abslogwrite.abslogprocessjobpart_write(fp_abslogprocessjobid,
                                                                                  coalesce(fp_partmessage,
                                                                                            'createdeliveryload'),
                                                                                  fp_systemuserid);
            end;
          end if;
          abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, fp_priority, fp_message,
                                                 fp_systemuserid);
        end;
      end if;
      write_output;
    exception
      when others then
        begin
          --write_log abslog
          abslogwrite.abslog_write(5, 'createdeliveryload', fp_plantid, fp_priority, fp_message,
                                   fp_systemuserid);
          write_output;
        end;
    end write_log;

    procedure intitialize is
    begin
      --intitialize
      gv_message                := 'intitialize';
      gv_abslogprocessjobpartid := null;
      gv_total_rowcount         := 0;
      --check parameter fp_plantid
      if fp_plantid is null then
        begin
          gv_message := 'Parameter fp_plantid cannot be left blank!';
          raise gv_userexception;
        end;
      else
        begin
          gv_message := 'Parameter fp_plantid ' || to_char(fp_plantid);
          select pt.code into gv_plantcode from plant pt where pt.plant_id = fp_plantid;
        end;
      end if;
      --check parameter fp_abslogprocessjobid
      if fp_abslogprocessjobid is not null then
        begin
          --select abslogprocessjob
          gv_message := 'fp_abslogprocessjobid ' || to_char(fp_abslogprocessjobid) ||
                        ' for plant code ' || gv_plantcode;
          select ps.description
          into   gv_process_desc
          from   abslogprocessjob ajl
          join   plantprocess pp
          on     pp.plantprocess_id = ajl.plantprocess_id
          join   process ps
          on     ps.process_id = pp.process_id
          where  ajl.abslogprocessjob_id = fp_abslogprocessjobid
          and    ajl.plant_id = fp_plantid;
        end;
      end if;
      --check parameter fp_systemuserid
      if fp_systemuserid is null then
        begin
          gv_message := 'Parameter fp_systemuserid cannot be left blank!';
          raise gv_userexception;
        end;
      end if;
      --get size star/all
      gv_message := 'get size star (*)';
      select s.sizedefinition_id into gv_sizeall from sizedefinition s where s.code = '*';
    end intitialize;

    procedure get_systemsettings is
    begin
      --GUARANTEEDMINDELDAYS
      gv_message := 'system setting GUARANTEEDMINDELDAYS for plant ' || gv_plantcode;
      select nvl(to_number(max(coalesce(st.value, st.factorydefault, '0'))), 0)
      into   gv_guaranteedmindeldays
      from   systemsetting st
      where  st.name = 'GUARANTEEDMINDELDAYS'
      and    st.plant_id = fp_plantid;
      --MAXMANUALSORTBATCHSIZE
      gv_message := 'system setting MAXMANUALSORTBATCHSIZE for plant ' || gv_plantcode;
      select nvl(to_number(max(coalesce(st.value, st.factorydefault, '0'))), 99)
      into   gv_maxbatchsize
      from   systemsetting st
      where  st.name = 'MAXMANUALSORTBATCHSIZE'
      and    st.plant_id = fp_plantid;
    end get_systemsettings;

    procedure create_load is
      lv_deliveryloadid    deliveryload.deliveryload_id%type;
      lv_sequencenumber    deliveryload.sequencenumber%type;
      lv_dld_count         number;
      lv_plantdepartmentid plantdepartment.plantdepartment_id%type;
      lv_cust_count        number;
    begin
      --create_load
      gv_message      := 'create_load, get deliveryday/date';
      gv_deliverydate := trunc(sysdate) + gv_guaranteedmindeldays;
      gv_deliveryday  := getabsdaynumber(gv_deliverydate);
      --Process Customer C, CustomerDeliveryDay CDD, CustomerExtraInfo CEI
      gv_message := 'create_load xc';
      for xc in (select c.customer_id, max(c.customernumber) customernumber, max(c.name) customername
                 from   customer c
                 join   customerdeliveryday cdd
                 on     cdd.customer_id = c.customer_id
                 join   customerextrainfo cei
                 on     cei.customer_id = c.customer_id
                 where  cdd.day_id = gv_deliveryday
                 and    c.dateactive <= gv_deliverydate
                 and    c.dateinactive > gv_deliverydate
                 and    cei.guaranteedminimumdelivery = 'Y'
                 and    exists (select 1
                         from   customeractivities ca
                         join   businessunit bu
                         on     bu.businessunit_id = ca.processbusinessunit_id
                         where  ca.customer_id = c.customer_id
                         and    bu.plant_id = fp_plantid)
                 group  by c.customer_id) loop
        begin
          --Process ProductListPerCustomer PLPC etc
          write_log('Customer: ' || xc.customernumber || '  Name ' || xc.customername, 1);
          lv_cust_count := 0;
          gv_message    := 'create_load xl';
          for xl in (select plpc.product_id, plpc.sizedefinition_id, plpc.finishingmethod_id,
                            max((select max(bu.businessunit_id)
                                 from   customeractivities ca
                                 join   businessunit bu
                                 on     bu.businessunit_id = ca.processbusinessunit_id
                                 where  ca.customer_id = plpc.customer_id
                                 and    ca.activity_id = pg.activity_id
                                 and    bu.plant_id = fp_plantid)) businessunit_id
                     from   productlistpercustomer plpc
                     join   product p
                     on     p.product_id = plpc.product_id
                     join   productgroup pg
                     on     pg.productgroup_id = p.productgroup_id
                     join   productdeliveryday pdd
                     on     pdd.productlistpercustomer_id = plpc.productlistpercustomer_id
                     where  plpc.customer_id = xc.customer_id
                     and    plpc.dateactive <= gv_deliverydate
                     and    plpc.dateinactive > gv_deliverydate
                     and    pdd.day_id = gv_deliveryday
                     and    exists (select 1
                             from   customeractivities ca
                             join   businessunit bu
                             on     bu.businessunit_id = ca.processbusinessunit_id
                             where  ca.customer_id = plpc.customer_id
                             and    ca.activity_id = pg.activity_id
                             and    bu.plant_id = fp_plantid)
                     group  by plpc.product_id, plpc.sizedefinition_id, plpc.finishingmethod_id) loop
            begin
              --Process Wearer W, WearerEmployment WE, etc
              gv_message := 'create_load xw';
              for xw in (select wi.wearerinventory_id, we.distributionpoint_id, lc.deliverypoint_id,
                                lc.bank, wi.changesperweek, delp.code deliverypointcode
                         from   wearer w
                         join   weareremployment we
                         on     we.wearer_id = w.wearer_id
                         join   wearerinventory wi
                         on     wi.weareremployment_id = we.weareremployment_id
                         join   locker lc
                         on     lc.locker_id = we.locker_id
                         join   deliverypoint delp
                         on     delp.deliverypoint_id = lc.deliverypoint_id
                         join   distributionpoint dp
                         on     dp.distributionpoint_id = we.distributionpoint_id
                         where  w.customer_id = xc.customer_id
                         and    w.dateactive <= gv_deliverydate
                         and    w.dateinactive > gv_deliverydate
                         and    not exists (select 1
                                 from   deliveryday dd
                                 where  dd.department_id = dp.department_id
                                 and    dd.day_id = gv_deliveryday)
                         and    wi.dateactive <= gv_deliverydate
                         and    wi.dateinactive > gv_deliverydate
                         and    wi.product_id = xl.product_id
                         and    ((wi.sizedefinition_id = xl.sizedefinition_id) or
                               (xl.sizedefinition_id = gv_sizeall))
                         and    wi.finishingmethod_id = xl.finishingmethod_id
                         and    wi.maxinventory > 0) loop
                begin
                  --reset values
                  lv_sequencenumber := null;
                  lv_dld_count      := 0;
                  --First check if there is a DeliveryLoad which contains already
                  --a detail line for the same bank.
                  --If there is then the current wearer inventory can be added to this one.
                  gv_message := 'check if deliveryload, exists for bank ' || nvl(xw.bank, ' ');
                  for zz in (select dl.deliveryload_id, dl.sequencenumber
                             from   deliveryload dl
                             join   deliveryloaddetail dld
                             on     dld.deliveryload_id = dl.deliveryload_id
                             where  dl.businessunit_id = xl.businessunit_id
                             and    dl.deliverypoint_id = xw.deliverypoint_id
                             and    dl.deliverydate = gv_deliverydate
                             and    dld.sortstring = nvl(xw.bank, ' ')
                             order  by dl.sequencenumber desc) loop
                    begin
                      lv_deliveryloadid := zz.deliveryload_id;
                      lv_sequencenumber := zz.sequencenumber;
                      lv_dld_count      := 0; --it is there, no new count needed
                      --first record found exit loop
                      exit;
                    end;
                  end loop;
                  if lv_sequencenumber is null then
                    begin
                      --check if deliveryload, exists
                      gv_message := 'check if deliveryload, exists';
                      select dl.deliveryload_id, dl.sequencenumber,
                             nvl((select count(1)
                                  from   deliveryloaddetail dld
                                  where  dld.deliveryload_id = dl.deliveryload_id), 0) dld_count
                      into   lv_deliveryloadid, lv_sequencenumber, lv_dld_count
                      from   deliveryload dl
                      where  dl.businessunit_id = xl.businessunit_id
                      and    dl.deliverypoint_id = xw.deliverypoint_id
                      and    dl.deliverydate = gv_deliverydate
                      and    dl.sequencenumber =
                             (select max(dlx.sequencenumber) sequencenumber
                               from   deliveryload dlx
                               where  dlx.businessunit_id = dl.businessunit_id
                               and    dlx.deliverypoint_id = dl.deliverypoint_id
                               and    dlx.deliverydate = dl.deliverydate);
                      --more then we can have in the batch, create new one
                      if lv_dld_count >= gv_maxbatchsize then
                        begin
                          lv_sequencenumber := lv_sequencenumber + 1;
                          raise no_data_found;
                        end;
                      end if;
                    exception
                      when no_data_found then
                        begin
                          --insert into deliveryload
                          gv_message := 'insert into deliveryload';
                          --get new count
                          lv_dld_count      := nvl(lv_dld_count, 0) + 1;
                          lv_sequencenumber := nvl(lv_sequencenumber, 1);
                          --get plantdepartment_id
                          select max(std.plantdepartment_id)
                          into   lv_plantdepartmentid
                          from   standarddelivery std
                          where  std.distributionpoint_id = xw.distributionpoint_id;
                          --insert into deliveryload
                          insert into deliveryload
                            (deliveryload_id, businessunit_id, deliverypoint_id, deliverydate,
                             sequencenumber, plantdepartment_id, code, status, creationtimestamp,
                             initializationtimestamp, initializationsystemuser_id,
                             outscanstarttimestamp, outscanstartsystemuser_id, systemuser_id,
                             timestamp)
                          values
                            (seq_deliveryload.nextval, xl.businessunit_id, xw.deliverypoint_id,
                             gv_deliverydate, lv_sequencenumber, lv_plantdepartmentid,
                             to_char(gv_deliverydate, 'YYYYIWDY') ||
                              lpad(to_char(lv_sequencenumber), 2, '0') ||
                              lpad(to_char(xc.customernumber), 12, '0') ||
                              lpad(xw.deliverypointcode, 6, ' '), 1, sysdate, null, null, null, null,
                             fp_systemuserid, sysdate)
                          returning deliveryload_id into lv_deliveryloadid;
                        end;
                    end;
                  end if;
                  begin
                    --insert into deliveryloaddetail
                    gv_message := 'insert into deliveryloaddetail';
                    insert into deliveryloaddetail
                      (deliveryloaddetail_id, deliveryload_id, wearerinventory_id, sortstring,
                       todeliverquantity, personalizedavailableqty, sortposition, systemuser_id,
                       timestamp)
                    values
                      (seq_deliveryloaddetail.nextval, lv_deliveryloadid, xw.wearerinventory_id,
                       nvl(xw.bank, ' '), xw.changesperweek, 0, 0, fp_systemuserid, sysdate);
                    --count rows
                    gv_total_rowcount := gv_total_rowcount + 1;
                    lv_cust_count     := lv_cust_count + 1;
                  exception
                    when dup_val_on_index then
                      null; --no problem was already there
                  end;
                end;
              end loop; --xw
            end;
          end loop; --xl
          write_log('Number of rows created  ' || lv_cust_count, 1);
        end;
      end loop; --xc
    end create_load;

    procedure finalize is
    begin
      --finalize
      commit;
      if gv_total_rowcount > 0 then
        begin
          write_log('Total of deliveryloaddetail ' || gv_total_rowcount || ' created', 1);
        end;
      else
        begin
          write_log('No rows created', 1);
        end;
      end if;
    end finalize;
  begin
    --intitialize
    intitialize;
    --get systemsettings
    get_systemsettings;
    --create_load
    create_load;
    --finalize
    finalize;
  exception
    --exception handling global
    when gv_userexception then
      begin
        rollback;
        write_log(gv_message, 3);
      end;
    when no_data_found then
      begin
        rollback;
        write_log('Error No Data Found ' || gv_message, 3);
      end;
    when others then
      begin
        rollback;
        write_log('Error Others ' || gv_message || ' ' || sqlerrm, 3);
      end;
  end createdeliveryload;

  procedure initializedeliveryload(fp_deliveryloadid in deliveryload.deliveryload_id%type,
                                   fp_systemuserid   in systemuser.systemuser_id%type default 0) is
    gv_userexception exception;
    gv_plantid          plant.plant_id%type;
    gv_message          abslog.message%type;
    gv_rec_deliveryload deliveryload%rowtype;
    gv_repairflagid     automaticflag.flag_id%type; --AutomaticFlag_ID = 3
    gv_rewashflagid     automaticflag.flag_id%type; --AutomaticFlag_ID = 19

    ---internal procedure(s)
    procedure write_log(fp_message in varchar2, fp_priority in number) is
      procedure write_output is
      begin
        --write_output
        dbms_output.put_line(fp_message);
      exception
        when others then
          null; --no problem if output cannot be written
      end write_output;
    begin
      --write_log abslog
      abslogwrite.abslog_write(5, 'initializedeliveryload', gv_plantid, fp_priority, fp_message,
                               fp_systemuserid);
      write_output;
    end write_log;

    procedure intitialize is
    begin
      --intitialize
      gv_message := 'intitialize';
      --check parameter fp_deliveryloadid
      if fp_deliveryloadid is null then
        begin
          gv_message := 'Parameter fp_deliveryloadid cannot be left blank!';
          raise gv_userexception;
        end;
      else
        begin
          gv_message := 'Parameter fp_deliveryloadid ' || to_char(fp_deliveryloadid);
          select dll.*
          into   gv_rec_deliveryload
          from   deliveryload dll
          where  dll.deliveryload_id = fp_deliveryloadid;
          if gv_rec_deliveryload.status >= 2 then
            begin
              gv_message := 'Parameter fp_deliveryloadid ' || to_char(fp_deliveryloadid) ||
                            ' already initialized';
              raise gv_userexception;
            end;
          end if;
        end;
      end if;
      --check parameter fp_systemuserid
      if fp_systemuserid is null then
        begin
          gv_message := 'Parameter fp_systemuserid cannot be left blank!';
          raise gv_userexception;
        end;
      end if;
      --get gv_repairflagid
      gv_message := 'get repair flag for event = Repair requested by repair pointer';
      select nvl(max(af.flag_id), 0)
      into   gv_repairflagid
      from   automaticflag af
      where  af.event = 'Repair requested by repair pointer';
      --get gv_rewashflagid
      gv_message := 'get Rewash flag for event = Rewash';
      select nvl(max(af.flag_id), 0)
      into   gv_rewashflagid
      from   automaticflag af
      where  af.event = 'Rewash';
    end intitialize;

    procedure initial_load is
      lv_sortstring   deliveryloaddetail.sortstring%type := ' ';
      lv_sortposition deliveryloaddetail.sortposition%type := 0;
      lv_available    number;

      procedure get_available_qty(fp_wearerinventoryid in wearerinventory.wearerinventory_id%type) is
      begin
        --get_available_qty
        gv_message := 'get_available_qty for wearerinventory_id ' || to_char(fp_wearerinventoryid);
        select count(distinct ui.uniqueitem_id)
        into   lv_available
        from   uniqueitem ui
        join   uniqueitemnonpool uinp
        on     uinp.uniqueitem_id = ui.uniqueitem_id
        join   stay
        on     stay.stay_id = ui.stay_id
        where  uinp.wearerinventory_id = fp_wearerinventoryid
        and    stay.stay_id = ui.stay_id
        and    stay.staynr in (30, 33, 40)
        and    not exists
         (select 1
                from   uniqueitemflag uif
                join   flag
                on     flag.flag_id = uif.flag_id
                where  uif.uniqueitem_id = ui.uniqueitem_id
                and    uif.startdate <= trunc(sysdate)
                and    ((flag.prohibitdelivery = 'Y') or (uif.flag_id = gv_repairflagid) or
                      (uif.flag_id = gv_rewashflagid)));
      end get_available_qty;
    begin
      --initial_load
      gv_message := 'initial_load';
      for xd in (select dld.sortstring, dld.wearerinventory_id
                 from   deliveryloaddetail dld
                 where  dld.deliveryload_id = fp_deliveryloadid
                 group  by dld.sortstring, dld.wearerinventory_id
                 order  by dld.sortstring, dld.wearerinventory_id) loop
        begin
          if (xd.sortstring <> lv_sortstring) then
            begin
              lv_sortposition := lv_sortposition + 1;
              lv_sortstring   := xd.sortstring;
            end;
          end if;
          --get_available_qty
          get_available_qty(xd.wearerinventory_id);
          --Update DeliveryLoadDetail
          gv_message := 'update deliveryloaddetail dld to Initialized';
          update deliveryloaddetail dld
          set    dld.sortposition = lv_sortposition, dld.personalizedavailableqty = lv_available,
                 dld.systemuser_id = fp_systemuserid, dld.timestamp = sysdate
          where  dld.deliveryload_id = fp_deliveryloadid
          and    dld.wearerinventory_id = xd.wearerinventory_id;
        end;
      end loop; --x
      --Update DeliveryLoad
      gv_message := '';
      update deliveryload dl
      set    dl.status = 2, dl.systemuser_id = fp_systemuserid, dl.timestamp = sysdate,
             dl.initializationsystemuser_id = fp_systemuserid, dl.initializationtimestamp = sysdate
      where  dl.deliveryload_id = fp_deliveryloadid;
    end initial_load;

    procedure finalize is
    begin
      --finalize
      commit;
      write_log('Ready', 1);
    end finalize;
  begin
    --intitialize
    intitialize;
    --initial_load
    initial_load;
    --finalize
    finalize;
  exception
    --exception handling global
    when gv_userexception then
      begin
        rollback;
        write_log(gv_message, 3);
      end;
    when no_data_found then
      begin
        rollback;
        write_log('Error No Data Found ' || gv_message, 3);
      end;
    when others then
      begin
        rollback;
        write_log('Error Others ' || gv_message || ' ' || sqlerrm, 3);
      end;
  end initializedeliveryload;

end guaranteedminimumdelivery;
/
