create or replace procedure getgarmentdatakannegiesser2(idcode              in uniqueitem.primaryid%type,
                                                        plantcode           in out plant.code%type,
                                                        fp_languagecode     in languages.code%type,
                                                        status              out nocopy integer,
                                                        finisherselection   out nocopy varchar2,
                                                        finishercode        out nocopy integer,
                                                        pressselection      out nocopy varchar2,
                                                        presscode           out nocopy integer,
                                                        checkpointselection out nocopy varchar2,
                                                        checkpointcode      out nocopy integer,
                                                        repairselection     out nocopy varchar2,
                                                        repaircode          out nocopy integer,
                                                        destination         out nocopy varchar2,
                                                        destinationcode     out nocopy integer,
                                                        foldcode            out nocopy integer,
                                                        stackchangeinfo     out nocopy varchar2,
                                                        sortstring          out nocopy varchar2,
                                                        alternatesortstring out nocopy integer,
                                                        flags               out nocopy varchar2,
                                                        infotext            out nocopy wearer.remark%type,
                                                        productcode         out nocopy product.code%type,
                                                        productdescription  out nocopy product_desc.description%type,
                                                        routenumber         out nocopy route.routenumber%type,
                                                        routeday            out nocopy day.daynumber%type,
                                                        stopnumber          out nocopy routestop.stopnumber%type,
                                                        customernumber      out nocopy customer.customernumber%type,
                                                        wearernumber        out nocopy wearer.wearernumber%type,
                                                        flagcode1           out nocopy sortaddressperflag.sortaddress%type,
                                                        flagcode2           out nocopy sortaddressperflag.sortaddress%type,
                                                        flagcode3           out nocopy sortaddressperflag.sortaddress%type,
                                                        flagcode4           out nocopy sortaddressperflag.sortaddress%type,
                                                        flagdescription1    out nocopy flag_desc.description%type,
                                                        flagdescription2    out nocopy flag_desc.description%type,
                                                        flagdescription3    out nocopy flag_desc.description%type,
                                                        flagdescription4    out nocopy flag_desc.description%type,
                                                        fp_washestotal      out nocopy uniqueitem.washestotal%type,
                                                        fp_productimagename out nocopy image.imagename%type,
                                                        fp_flag_freetext1   out nocopy uniqueitemflag.freetextitem%type,
                                                        fp_flag_freetext2   out nocopy uniqueitemflag.freetextitem%type,
                                                        fp_flag_freetext3   out nocopy uniqueitemflag.freetextitem%type,
                                                        fp_flag_freetext4   out nocopy uniqueitemflag.freetextitem%type) is
  uniqueitemid         uniqueitem.uniqueitem_id%type;
  plantid_param        plant.plant_id%type;
  plantid_garment      plant.plant_id%type;
  languageid           languages.language_id%type;
  finishingcodeid      finishingcode.finishingcode_id%type;
  serviceteamid        serviceteam.serviceteam_id%type;
  colorid              color.color_id%type;
  finishingmethodid    finishingmethod.finishingmethod_id%type;
  productid            product.product_id%type;
  cnt                  integer;
  wearerflagid         flag.flag_id%type;
  wearerflagstartdate  date;
  empflagid            flag.flag_id%type;
  empflagstartdate     date;
  debuglevel           integer;
  allowrepairscan      integer;
  vchargerepairs       customer.chargerepairs%type;
  vcustomerowned       uniqueitem.customerowned%type;
  customername         customer.name%type;
  customerid           customer.customer_id%type;
  banklocker           varchar2(20);
  garmentdispensercode garmentdispenser.code%type;
  deliverypointcode    deliverypoint.code%type;
  xsortaddress         varchar2(20);
  departmentnumber     department.departmentnumber%type;
  destinationstring    varchar2(3);
  flagsinfo            varchar2(500);
  customeremployee#    wearer.customeremployeenumber%type;
  wearername           wearer.searchname%type;
  gender               wearer.sex%type;
  sizecode             sizedefinition.code%type;
  packingsequence      product.packingsequence%type;
  pool                 uniqueitem.poolitem%type;
  businessunitid       businessunit.businessunit_id%type;
  stayid               stay.stay_id%type;
  routeid              route.route_id%type;
  imageid              product.image_id%type;
  routestopid          routestop.routestop_id%type;
  planneddate          date;
  bucode               businessunit.code%type;
  returnedplantcode    plant.code%type;
  valternatesortstring varchar2(28);
  repaircog            customer.chargerepaircog%type;
  repairlog            customer.chargerepairs%type;
  findpackingnote      varchar2(20);
  ordernumber          integer;
  --Order 502022
  routedate date;
  --Todo 6660
  idprimary uniqueitem.primaryid%type; --is never used in this procedure
  --Order 20012033
  routecodeonlabel    route.codeonlabel%type;
  secondaryid         secondaryuniqueitem.secondaryid%type; --not used
  gv_sortcustomerid   customer.sortcustomer_id%type;
  gv_vosplantcode     varchar2(10);
  gv_vosortcustomerid customer.sortcustomer_id%type;
  productsortgroupseq productsortgroupperplant.sequencenumber%type;
  lockersortgroup#    lockersortgroup.lockersortgroupnumber%type;
  distributionpoint#  distributionpoint.distributionpointnumber%type;
  lv_dummy            varchar2(255);
  lv_databasecode     databasesettings.code%type;

  procedure get_productimagename is
  begin
    select max(i.imagename)
    into   fp_productimagename
    from   product p, image i
    where  p.product_id = productid
    and    p.image_id = i.image_id;
  end get_productimagename;

  procedure get_languageid is
  begin
    if fp_languagecode is not null then
      select l.language_id into languageid from languages l where l.code = fp_languagecode;
    end if;
  exception
    when no_data_found then
      languageid := null;
  end get_languageid;
begin
  --getgarmentdatakannegiesser2 version 0.5
  case plantcode
    when 'VOSALT' then
      begin
        gv_vosplantcode := plantcode;
        plantcode       := '1';
        select max(c.customer_id)
        into   gv_vosortcustomerid
        from   customer c
        where  c.customernumber = 777777;
      end;
    when 'VOSNEU' then
      begin
        gv_vosplantcode := plantcode;
        plantcode       := '1';
        select max(c.customer_id)
        into   gv_vosortcustomerid
        from   customer c
        where  c.customernumber = 777770;
      end;
    else
      begin
        gv_vosplantcode     := null;
        gv_vosortcustomerid := null;
      end;
  end case;
  select d.code into lv_databasecode from databasesettings d;
  get_languageid;
  pack_sort.determinegarmentid('KANNEGIESSER', idcode, plantcode, uniqueitemid, pool, bucode,
                               businessunitid, plantid_garment, returnedplantcode, status, idprimary,
                               plantid_param, secondaryid);
  if gv_vosplantcode = 'VOSNEU' and pool <> 1 then
    begin
      gv_vosortcustomerid := null;
    end;
  end if;
  if lv_databasecode = 'PAV' then
    begin
      --ABS-96306
      select ur.lastindate
      into   routedate
      from   uniqueitemroute ur
      where  ur.uniqueitem_id = uniqueitemid
      and    ur.lastindate > sysdate - 16;
    exception
      when others then
        null;
    end;
  end if;
  routedate := nvl(routedate, sysdate);
  begin
    if plantcode is null then
      plantcode := returnedplantcode;
    end if;
    pack_sort.getsystemsettings(plantid_param, debuglevel, lv_dummy, allowrepairscan);
    ---------------------------------------------------------------
    --other fields
    if status = 0 then
      begin
        findpackingnote := '';
        ordernumber     := 0;
        pack_sort.getgarmentdatainfo('KANNEGIESSER', plantid_garment, uniqueitemid, languageid,
                                     finishingcodeid, serviceteamid, vchargerepairs, vcustomerowned,
                                     colorid, wearerflagid, wearerflagstartdate, empflagid,
                                     empflagstartdate, finishingmethodid, productid, productcode,
                                     productdescription, sizecode, customernumber, customerid,
                                     customername, wearernumber, wearername, banklocker,
                                     departmentnumber, customeremployee#, gender,
                                     garmentdispensercode, deliverypointcode, infotext, routenumber,
                                     routeday, stopnumber, packingsequence, stayid, routeid, imageid,
                                     routestopid, planneddate, status, valternatesortstring,
                                     debuglevel, findpackingnote, ordernumber, routedate,
                                     fp_washestotal, routecodeonlabel, null, gv_sortcustomerid);
        if status = 0 then
          --Alternate Sort String
          --order 501273
          if valternatesortstring = 'Y' then
            alternatesortstring := packingsequence;
          else
            alternatesortstring := '';
          end if;
          get_productimagename; --502901
          begin
            select nvl(psgp.sequencenumber, 0)
            into   productsortgroupseq
            from   productsortgroupperplant psgp, productsortgroup psg, product pr
            where  psgp.productsortgroup_id = psg.productsortgroup_id
            and    psg.productsortgroup_id = pr.productsortgroup_id
            and    psgp.plant_id = plantid_param
            and    pr.product_id = productid;
          exception
            when others then
              productsortgroupseq := 0;
          end;
          begin
            select nvl(lsg.lockersortgroupnumber, 0), di.distributionpointnumber
            into   lockersortgroup#, distributionpoint#
            from   uniqueitemnonpool n, weareremployment e
            left   outer join(locker l
            join   lockersortgroup lsg
            on     l.lockersortgroup_id = lsg.lockersortgroup_id) on e.locker_id = l.locker_id, distributionpoint di
            where  n.uniqueitem_id = uniqueitemid
            and    n.weareremployment_id = e.weareremployment_id
            and    e.distributionpoint_id = di.distributionpoint_id;
          exception
            when others then
              lockersortgroup#   := 0;
              distributionpoint# := 0;
          end;
          if gv_sortcustomerid is not null then
            begin
              --get sort customer stopnumber for route found
              select nvl(min(rs.stopnumber), 0)
              into   stopnumber
              from   routestop rs
              join   route r
              on     r.route_id = rs.route_id
              where  r.routenumber = routenumber
              and    r.drivingday_id = routeday
              and    rs.customer_id = gv_sortcustomerid
              and    trunc(sysdate) between rs.dateactive and rs.dateinactive;
            exception
              when others then
                stopnumber := 0;
            end;
          end if;
          sortstring      := pack_sort.buildsortstring(productcode, sizecode, customernumber,
                                                       customerid, wearernumber, wearername,
                                                       banklocker, departmentnumber,
                                                       customeremployee#, gender,
                                                       garmentdispensercode, deliverypointcode,
                                                       stopnumber, plantid_param, vcustomerowned,
                                                       ordernumber, productsortgroupseq,
                                                       lockersortgroup#, distributionpoint#,
                                                       nvl(gv_vosortcustomerid, gv_sortcustomerid));
          stackchangeinfo := pack_sort.buildsortstring(productcode, sizecode, customernumber,
                                                       customerid, wearernumber, wearername,
                                                       banklocker, departmentnumber,
                                                       customeremployee#, gender,
                                                       garmentdispensercode, deliverypointcode,
                                                       stopnumber, plantid_param, vcustomerowned,
                                                       ordernumber, productsortgroupseq,
                                                       lockersortgroup#, distributionpoint#,
                                                       nvl(gv_vosortcustomerid, gv_sortcustomerid),
                                                       'Y');
          --order1044
          begin
            --finishingcode
            select s.sortaddress
            into   xsortaddress
            from   finishingcode f, sortaddressperfinishingcode s
            where  f.finishingcode_id = finishingcodeid
            and    f.finishingcode_id = s.finishingcode_id
            and    s.plant_id = plantid_param;
            if xsortaddress is null then
              finisherselection := 'B';
              finishercode      := 0;
            else
              finishercode      := substr(xsortaddress, 2, 2);
              finisherselection := substr(xsortaddress, 1, 1);
            end if;
            cnt := to_number(finishercode);
          exception
            when others then
              finisherselection := 'B';
              finishercode      := 0;
          end;
          pressselection := 'B';
          presscode      := 0;
          begin
            --checkpoint selection
            select s.sortaddress
            into   xsortaddress --
            from   serviceteam s
            where  s.serviceteam_id = serviceteamid;
            if xsortaddress is null then
              checkpointselection := 'B';
              checkpointcode      := 0;
            else
              checkpointcode      := substr(xsortaddress, 2, 2);
              checkpointselection := substr(xsortaddress, 1, 1);
            end if;
            cnt := to_number(checkpointcode);
          exception
            when others then
              checkpointselection := 'B';
              checkpointcode      := 0;
          end;
          if customerid is not null then
            begin
              --repair selection
              /*
              only for LOG
              0 = No Repair allowed  and No charge repair
              1 = Repair allowed and No charge repair
              2 = Repair allowed and Charge repair
              only for COG
              0 = No Repair allowed  and No charge repair
              1 = Repair allowed and No charge repair
              2 = Repair allowed and Charge repair
              */
              repairselection := 'B';
              repaircode      := 0;
            
              select c.chargerepaircog, c.chargerepairs
              into   repaircog, repairlog
              from   customer c
              where  c.customer_id = customerid;
            
              select r.repairaddress
              into   xsortaddress --RepairSelection
              from   repairaddresspercolor r
              where  r.color_id = colorid
              and    r.plant_id = plantid_param;
            
              if vcustomerowned = 'Y' then
                if repaircog <> 0 then
                  --COG
                  repairselection := 'R';
                  repaircode      := substr(xsortaddress, 2, 2);
                end if;
              else
                if repairlog <> 0 then
                  --LOG
                  repairselection := 'R';
                  repaircode      := substr(xsortaddress, 2, 2);
                end if;
              end if;
            exception
              when no_data_found then
                begin
                  repairselection := 'B';
                  repaircode      := 0;
                end;
            end;
          else
            begin
              repairselection := 'B';
              repaircode      := 0;
            end;
          end if;
          begin
            --Destination
            pack_sort.getflags(uniqueitemid, plantid_param, wearerflagid, wearerflagstartdate,
                               empflagid, empflagstartdate, languageid, 0, flagcode1, flagcode2,
                               flagcode3, flagcode4, flagdescription1, flagdescription2,
                               flagdescription3, flagdescription4, flagsinfo, destinationstring, 'Y',
                               fp_flag_freetext1, fp_flag_freetext2, fp_flag_freetext3,
                               fp_flag_freetext4);
            if destinationstring is not null then
              destinationcode := substr(destinationstring, 2, 2);
              destination     := substr(destinationstring, 1, 1);
            else
              begin
                select s.sortaddress
                into   xsortaddress --Destination
                from   sortaddressperfinishingmeth s
                where  s.finishingmethod_id = finishingmethodid
                and    s.plant_id = plantid_param;
                if xsortaddress is null then
                  destination     := 'M';
                  destinationcode := 0;
                else
                  destinationcode := substr(xsortaddress, 2, 2);
                  destination     := substr(xsortaddress, 1, 1);
                end if;
                cnt := to_number(destinationcode);
                if destination not in ('M', 'F', 'A', 'H') then
                  destination     := 'M';
                  destinationcode := 0;
                end if;
              exception
                when others then
                  destination     := 'M';
                  destinationcode := 0;
              end;
            end if;
          end;
          begin
            --fold program
            select f.foldingprogram
            into   xsortaddress --Fold
            from   foldingprogramdefinition f
            where  f.product_id = productid
            and    f.finishingmethod_id = finishingmethodid
            and    f.plant_id = plantid_param;
            if xsortaddress is null then
              foldcode := 0;
            else
              foldcode := substr(xsortaddress, 1, 2);
            end if;
            cnt := to_number(foldcode);
          exception
            when others then
              begin
                foldcode := 0;
                pack_sort.write_logging('Error for IDCODE ' || idcode ||
                                        'Folding Program not found' || sqlcode || ' ' || sqlerrm,
                                        debuglevel, 'GETGARMENTDATAKANNEGIESSER2', plantid_param, 3,
                                        1);
              end;
          end;
          --flags
          pack_sort.getflags(uniqueitemid, plantid_param, wearerflagid, wearerflagstartdate,
                             empflagid, empflagstartdate, languageid, 4, flagcode1, flagcode2,
                             flagcode3, flagcode4, flagdescription1, flagdescription2,
                             flagdescription3, flagdescription4, flagsinfo, destinationstring, 'Y',
                             fp_flag_freetext1, fp_flag_freetext2, fp_flag_freetext3,
                             fp_flag_freetext4);
          flags := flagsinfo;
        else
          pack_sort.write_logging('Error for IDCODE ' || idcode ||
                                  ' DELIVERYSCHEME OR ROUTE STOPS NOT DEFINED FOR CUSTOMER' ||
                                  customernumber, debuglevel, 'GETGARMENTDATAKANNEGIESSER2',
                                  plantid_param, 3, 1);
        end if; --for stATUS=0
        if pool = 1 and lv_databasecode = 'ANG' then
          begin
            select nvl(max(pt.code), plantcode)
            into   plantcode
            from   uniqueitem u
            join   distributionpoint dp
            on     dp.distributionpoint_id =
                   nvl(u.distributionpoint_id, u.lastdistributionpoint_link_id)
            join   department t
            on     t.department_id = dp.department_id
            join   customer c
            on     c.customer_id = t.customer_id
            join   businessunit bu
            on     bu.businessunit_id = c.defaultbusinessunit_id
            join   plant pt
            on     pt.plant_id = bu.plant_id
            where  u.uniqueitem_id = uniqueitemid;
          end;
        end if;
      end;
    end if;
    pack_sort.writelogfile('KANNEGIESSER', debuglevel, plantcode, idcode, bucode, status,
                           finisherselection, checkpointselection, repairselection, destination,
                           to_char(foldcode), stackchangeinfo, routenumber, stopnumber, routeday, '',
                           sortstring, alternatesortstring, customernumber, '', wearernumber,
                           wearername, departmentnumber, customeremployee#, gender, banklocker,
                           garmentdispensercode, deliverypointcode, productcode, productdescription,
                           sizecode, '', '', '', flagcode1, flagcode2, flagcode3, flagcode4,
                           flagdescription1, flagdescription2, flagdescription3, flagdescription4,
                           infotext, '', flags, '', 0, ' ');
  exception
    when others then
      pack_sort.write_logging(idcode || ' ' || sqlerrm, debuglevel, 'GETGARMENTDATAKANNEGIESSER2',
                              plantid_param, 3, 1);
  end;
end getgarmentdatakannegiesser2;
/
