create or replace procedure microsstransfersoil(customerid in varchar2,
                                                productid  in varchar2,
                                                lquantity  in number,
                                                timestamp  in number) is
  /*MicrossTransferSoil Version 2.1*/
  gv_userexception exception;
  gv_message             abslog.message%type;
  gv_customerid          number(28, 0);
  gv_productid           number(28, 0);
  gv_distributionpointid number(28, 0);
  gv_sizeid              number(28, 0);
  gv_finishingmethodid   number(28, 0);
  gv_processbuid         number(28, 0);
  gv_plantid             number(28, 0);
  gv_systemuserid        number(28, 0);
  gv_delayevenexchange   number(28, 0);
  gv_plpcid              number(28, 0);
  gv_currentdate         date;
  gv_deliverypointid     number(28, 0);
  gv_typeofmerch         int;
  gv_invoicebuid         number(28, 0);
  gv_plpcsdid            number(28, 0);

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

  procedure initialize is
  begin
    -- 'STEP1'    
    --initialize
    gv_message     := 'check parameter timestamp (YYYYMMDD) ' || timestamp;
    gv_currentdate := to_date(timestamp, 'YYYYMMDD');
    begin
      gv_message := 'get systemuser';
      select s.systemuser_id into gv_systemuserid from systemuser s where s.username = 'SORT';
    exception
      when no_data_found then
        select min(systemuser_id) into gv_systemuserid from systemuser;
    end;
  end initialize;

  procedure check_parameters is
  begin
    -- 'STEP2'      
    --check_parameters get customer id
    gv_message := 'Customer legacycustomernumber ' || customerid;
    select c.customer_id, c.defaultbusinessunit_id, bu.plant_id
    into   gv_customerid, gv_processbuid, gv_plantid
    from   customer c
    join   businessunit bu
    on     bu.businessunit_id = c.defaultbusinessunit_id
    where  c.legacycustomernumber = customerid;
    --get distributionpoint if
    gv_message := 'Distributionpoint id for customer legacycustomernumber ' || customerid;
    select di.distributionpoint_id
    into   gv_distributionpointid
    from   distributionpoint di
    where  di.department_id =
           (select min(d.department_id) from department d where d.customer_id = gv_customerid);
    --get product
    gv_message := 'get product legacyproductcode ' || productid;
    select p.product_id, p.typeofmerchandise
    into   gv_productid, gv_typeofmerch
    from   product p
    where  p.legacyproductcode = productid;
    --get size
    gv_message := 'get size';
    select min(ps.sizedefinition_id)
    into   gv_sizeid
    from   productsize ps
    where  ps.product_id = gv_productid
    and    ps.sizedefinition_id in
           (select s.sizedefinition_id from sizedefinition s where s.code <> '*');
    --get finisingmethod
    gv_message := 'get finishingmethod';
    select min(pf.finishingmethod_id)
    into   gv_finishingmethodid
    from   productfinishingmethod pf
    where  pf.product_id = gv_productid;
    --get deliverypoint
    gv_message := 'get deliverypoint';
    select min(dv.deliverypoint_id)
    into   gv_deliverypointid
    from   deliverypoint dv
    where  dv.customer_id = gv_customerid;
    --get gv_invoicebuid
    gv_message := 'get gv_invoicebuid';
    select coalesce(min(ca.invoicebusinessunit_id), gv_processbuid)
    into   gv_invoicebuid
    from   customeractivities ca, product p, productgroup pg, activity a
    where  ca.activity_id = a.activity_id
    and    a.activity_id = pg.activity_id
    and    pg.productgroup_id = p.productgroup_id
    and    ca.customer_id = gv_customerid
    and    p.product_id = gv_productid
    and    ca.processbusinessunit_id = gv_processbuid;
    --get plpc
    select min(plpc.productlistpercustomer_id)
    into   gv_plpcid
    from   productlistpercustomer plpc
    where  plpc.customer_id = gv_customerid
    and    plpc.product_id = gv_productid
    and    plpc.dateactive <= gv_currentdate
    and    plpc.dateinactive >= gv_currentdate;    
    if gv_plpcid is not null then 
     select plpc.finishingmethod_id
      into gv_finishingmethodid
      from productlistpercustomer plpc where plpc.productlistpercustomer_id=gv_plpcid;
      end if;               
    if gv_plpcid is not null then
      begin        
    select plpc.sizedefinition_id 
           into gv_plpcsdid 
           from productlistpercustomer plpc, sizedefinition sd
    where plpc.productlistpercustomer_id=gv_plpcid
    and plpc.sizedefinition_id=sd.sizedefinition_id
    and sd.code <> '*';
    if gv_plpcsdid is not null
      then 
    gv_sizeid:=gv_plpcsdid;
    end if;
    end;
    end if;       
    if gv_plpcid is null then gv_delayevenexchange:=0;
      else 
    select nvl(plpc.delayedevenexchange,0)
    into   gv_delayevenexchange
    from   productlistpercustomer plpc
    where  plpc.productlistpercustomer_id = gv_plpcid;
     end if;       
    gv_message := 'productlistpercustomer for customer/product ' || customerid || '/' || productid ||
                  ' with product/size/finishingmethod ID:' || gv_productid || '/' || gv_sizeid || '/' ||
                  gv_finishingmethodid;    
    if gv_plpcid is null then
      begin
        gv_message := 'No data found! ' || gv_message;
        raise gv_userexception;
      end;
    end if;
  end check_parameters;

  procedure create_soilcount is
    soilcountid number(28, 0);
  begin
    --create_soilcount
    --'STEP3'
    gv_message := 'create_soilcount';
    begin
      select s.soilcount_id
      into   soilcountid
      from   soilcount s
      where  s.distributionpoint_link_id = gv_distributionpointid
      and    s.processbusinessunit_id = gv_processbuid
      and    s.countdate = getdateonly(gv_currentdate);
      --update soilcount
      gv_message := 'update soilcount';
      update soilcount s
      set    s.systemuser_id = gv_systemuserid, s.timestamp = sysdate
      where  s.soilcount_id = soilcountid;
    exception
      when no_data_found then
        begin
          --insert soilcountline
          gv_message := 'insert soilcountline';
          insert into soilcount
            (soilcount_id, distributionpoint_link_id, processbusinessunit_id, countdate,
             systemuser_id, timestamp)
          values
            (seq_soilcount.nextval, gv_distributionpointid, gv_processbuid,
             getdateonly(gv_currentdate), gv_systemuserid, sysdate);
        end;
    end;
    begin
      if soilcountid is not null then
        begin
          --insert into soilcountline
          gv_message := 'insert soilcountline';
          insert into soilcountline
            (soilcountline_id, soilcount_id, product_link_id, sizedefinition_link_id,
             finishingmethod_id, countconverted, countkeypad, countlightframe, countabused,
             soilweight, delayevenexchange, unrelatedloan, systemuser_id, timestamp)
          values
            (seq_soilcountline.nextval, soilcountid, gv_productid, gv_sizeid, gv_finishingmethodid,
             null, null, lquantity, 0, 0, gv_delayevenexchange, 'N', gv_systemuserid, sysdate);
        exception
          when dup_val_on_index then
            begin
              --update soilcountline
              gv_message := 'update soilcountline';
              update soilcountline
              set    countlightframe = nvl(countlightframe, 0) + lquantity,
                     systemuser_id = gv_systemuserid, timestamp = gv_currentdate
              where  soilcount_id = soilcountid
              and    product_link_id = gv_productid
              and    sizedefinition_link_id = gv_sizeid
              and    finishingmethod_id = gv_finishingmethodid;
            end;
        end;
      else
        begin
          --insert into soilcountline
          gv_message := 'insert into soilcountline';
          insert into soilcountline
            (soilcountline_id, soilcount_id, product_link_id, sizedefinition_link_id,
             finishingmethod_id, countconverted, countkeypad, countlightframe, countabused,
             soilweight, delayevenexchange, unrelatedloan, systemuser_id, timestamp)
          values
            (seq_soilcountline.nextval, seq_soilcount.currval, gv_productid, gv_sizeid,
             gv_finishingmethodid, null, null, lquantity, 0, 0, gv_delayevenexchange, 'N',
             gv_systemuserid, sysdate);
        end;
      end if;
    end;
    begin
      --insert into soilhistory
      gv_message := 'insert into soilhistory';
      insert into soilhistory
        (soilhistory_id, customer_link_id, product_link_id, sizedefinition_link_id,
         processbusinessunit_id, quantityabused, quantitysoil, daynumber, week, year, receivingdate,
         systemuser_id, timestamp, distributionpoint_link_id, invoicebusinessunit_id)
      values
        (seq_soilhistory.nextval, gv_customerid, gv_productid, gv_sizeid, gv_processbuid, 0,
         lquantity, getabsdaynumber(gv_currentdate), getisoweek(gv_currentdate),
         getyearofweek(gv_currentdate), getdateonly(gv_currentdate), gv_systemuserid, sysdate,
         gv_distributionpointid, gv_invoicebuid);
    exception
      when dup_val_on_index then
        begin
          --update soilhistory
          gv_message := 'update soilhistory';
          update soilhistory
          set    quantitysoil = nvl(quantitysoil, 0) + lquantity, systemuser_id = gv_systemuserid,
                 timestamp = gv_currentdate
          where  customer_link_id = gv_customerid
          and    product_link_id = gv_productid
          and    sizedefinition_link_id = gv_sizeid
          and    processbusinessunit_id = gv_processbuid
          and    distributionpoint_link_id = gv_distributionpointid
          and    receivingdate = getdateonly(gv_currentdate);
        end;
    end;
  end create_soilcount;

  procedure create_delevenexchange is
    lastindate           date;
    deliveryvariationsid number(28, 0);
    amount               number(28, 6); --deliveryvariations  
    vdeliverydate        date;
    scanbyroute          varchar2(1);
    lastinroute          route.route_id%type;
    routestopin          routestop.routestop_id%type;
    deedeliveryschemeid  deliveryscheme.deliveryscheme_id%type;
    routestopid          routestop.routestop_id%type;
    plannedroute         route.route_id%type;
    soilcountsubtype     deliveryvariations.soilcountsubtype%type; --Todo9361
  begin
    --create_delevenexchange
    --'STEP4'
    gv_message := 'create_delevenexchange';
    if gv_delayevenexchange > 0 then
      begin
        --pack_sort.getlastinroute
        gv_message := 'pack_sort.getlastinroute';
        pack_sort.getlastinroute(gv_customerid, gv_currentdate, gv_deliverypointid, gv_typeofmerch,
                                 lastinroute, routestopin, lastindate);
        --pack_sort.fillplannedoutroute
        gv_message := 'pack_sort.fillplannedoutroute';
        pack_sort.fillplannedoutroute(gv_plantid, gv_currentdate, gv_typeofmerch,
                                      gv_delayevenexchange,
                                      --deliverymethod,
                                      deedeliveryschemeid, gv_plpcid, gv_customerid,
                                      gv_deliverypointid, lastinroute, lastindate, routestopin,
                                      routestopid, plannedroute, vdeliverydate,
                                      --planneddate,
                                      scanbyroute);
        if vdeliverydate is null then
          vdeliverydate := gv_currentdate + 7;
        end if;
        begin
          ---DISTRIBUTIONPOINT_ID, PRODUCT_ID, SIZEDEFINITION_ID, FINISHINGMETHOD_ID,
          -- PACKINGNOTE_ID, PROCESSED, DELIVERYDATE, ORIGIN, CAUSENOTE_ID, 
          --UPDATERENTQTY, SOILCOUNTSUBTYPE, WEBORDERLINE_ID, REFERENCE, 
          --ADJUSTMENTREASON_ID, REASONCODE_ID
          select deliveryvariations_id, amount
          into   deliveryvariationsid, amount
          from   deliveryvariations
          where  product_id = gv_productid
          and    sizedefinition_id = gv_sizeid
          and    finishingmethod_id = gv_finishingmethodid
          and    packingnote_id is null
          and    processed = 'N'
          and    causenote_id is null
          and    updaterentqty = 'N'
          and    origin = 1
          and    getdateonly(deliverydate) = getdateonly(vdeliverydate)
          and    reference = 'Micross'
          and    distributionpoint_id = gv_distributionpointid
          and    soilcountsubtype = 1
          and    weborderline_id is null;
          --update deliveryvariations
          gv_message := 'update deliveryvariations';
          update deliveryvariations
          set    amount = nvl(amount, 0) + lquantity, systemuser_id = gv_systemuserid,
                 timestamp = sysdate
          where  deliveryvariations.deliveryvariations_id = deliveryvariationsid;
        exception
          when no_data_found then
            begin
              --insert into deliveryvariations
              gv_message := 'insert into deliveryvariations';
              insert into deliveryvariations
                (deliveryvariations_id, product_id, packingnote_id, causenote_id, deliverydate,
                 origin, amount, finishingmethod_id, fixedamount, distributionpoint_id,
                 sizedefinition_id, reference, systemuser_id, processed, timestamp, updaterentqty,
                 adjustmentreason_id, soilcountsubtype)
              values
                (seq_deliveryvariations.nextval, gv_productid, null, null, vdeliverydate, 1,
                 lquantity, gv_finishingmethodid, 'N', gv_distributionpointid, gv_sizeid, 'Micross',
                 gv_systemuserid, 'N', sysdate, 'N', null, 1);
            end;
        end;
      end;
    end if; --for delayevenexchange>0
  end create_delevenexchange;

  procedure create_onsiteinvadjustment is
    recordonsiteinv              varchar2(10);
    productlistperdistribpointid number(28, 0);
  begin
    --create_onsiteinvadjustment
    --'STEP5'
    gv_message := 'create_onsiteinvadjustment for gv_plpcid ' || gv_plpcid;
    if gv_plpcid is not null then
      begin
        select nvl(max(value), 'N')
        into   recordonsiteinv
        from   systemsetting
        where  name = 'RECORDONSITEINVENTORY'
        and    plant_id = gv_plantid;
        if recordonsiteinv = 'Y' then
          begin
            --select productlistperdistribpoint
            gv_message := 'select productlistperdistribpoint for distributionpoint_id ' ||
                          gv_distributionpointid || ' product_id ' || gv_productid || ' size ' ||
                          gv_sizeid || ' finishingmethod_id ' || gv_finishingmethodid;
            select productlistperdistribpoint_id
            into   productlistperdistribpointid
            from   productlistperdistribpoint
            where  distributionpoint_id = gv_distributionpointid
            and    product_id = gv_productid
            and    sizedefinition_id = gv_sizeid
            and    finishingmethod_id = gv_finishingmethodid;
            begin
              --insert into onsiteinventoryadjustment
              gv_message := 'insert into onsiteinventoryadjustment';
              insert into onsiteinventoryadjustment
                (onsiteinventoryadjustment_id, productlistperdistribpoint_id, origin, inventorydate,
                 quantity, reference, systemuser_id, timestamp)
              values
                (seq_onsiteinventoryadjustment.nextval, productlistperdistribpointid, 4,
                 getdateonly(gv_currentdate), lquantity * (-1), 'micross', gv_systemuserid, sysdate);
            exception
              when dup_val_on_index then
                begin
                  --update onsiteinventoryadjustment
                  gv_message := 'update onsiteinventoryadjustment';
                  update onsiteinventoryadjustment
                  set    quantity = nvl(quantity, 0) + (-1) * lquantity,
                         systemuser_id = gv_systemuserid, timestamp = sysdate
                  where  productlistperdistribpoint_id = productlistperdistribpointid
                  and    origin = 4
                  and    inventorydate = getdateonly(gv_currentdate)
                  and    reference = 'micross'
                  and    systemuser_id = gv_systemuserid;
                end;
            end;
          end;
        end if;
      end;
    end if;
  end create_onsiteinvadjustment;

  procedure finalize is
  begin
    --finalize
    gv_message := 'finalize';
    commit;
  end finalize;
begin
  --initialize
  initialize;
  --check_parameters
  check_parameters;
  --create_soilcount
  create_soilcount;
  --create_delevenexchange
  create_delevenexchange;
  --create_onsiteinvadjustment
  create_onsiteinvadjustment;
  --finalize
  finalize;
exception
  --error handling 1 place
  when gv_userexception then
    begin
      rollback;
      write_log(gv_message || ' Parameters used: customer ' || customerid || ' product ' ||
                productid || ' lquantity ' || lquantity || ' timestamp ' || gv_currentdate, 3);
    end;
  when no_data_found then
    begin
      rollback;
      write_log('No data found! ' || gv_message || ' Parameters used: customer ' || customerid ||
                ' product ' || productid || ' lquantity ' || lquantity || ' timestamp ' ||
                gv_currentdate, 3);
    end;
  when others then
    begin
      rollback;
      write_log('Error! ' || gv_message || ' Parameters used: customer ' || customerid ||
                ' product ' || productid || ' lquantity ' || lquantity || ' timestamp ' ||
                gv_currentdate || ' ' || sqlerrm, 3);
    end;
end microsstransfersoil;
/
