create or replace package pack_scrutiny is
PRAGMA SERIALLY_REUSABLE;
  -- Author  : MONICA.B
  -- Created : 7/15/2013 2:24:29 PM
  -- Purpose : Scrutiny process

  -- Public type declarations
  --type stop_ids_array is TABLE OF NUMBER INDEX BY BINARY_INTEGER;

  -- Public constant declarations
  --<ConstantName> constant <Datatype> := <Value>;

  -- Public variable declarations
  --<VariableName> <Datatype>;

  -- Public function and procedure declarations
PROCEDURE scrutinyProcess(plantId plant.plant_id%type,
                            stopIds numbers_array,
                            plantprocessId plantprocess.plantprocess_id%type,
                            dialogId dialogs.dialog_id%type,
                            abslogprocessjobId in out abslogprocessjob.abslogprocessjob_id%type,
                            languageId languages.language_id%type,
                            systemuserId systemuser.systemuser_id%type,
                            workstationId workstation.workstation_id%type);
end pack_scrutiny;
/
create or replace package body pack_scrutiny is
PRAGMA SERIALLY_REUSABLE;
  -- Private type declarations
  gv_userexception           exception;
  --type stop_ids_array is TABLE OF NUMBER INDEX BY BINARY_INTEGER;

  -- Private constant declarations
  RETURN_OK CONSTANT INTEGER := 0;
  RETURN_FAILED_UNKNOWN CONSTANT INTEGER := -1;

  PRIORITY_MESSAGE CONSTANT abslogprocessjobline.priority%TYPE := 1;
  --PRIORITY_WARNING CONSTANT abslogprocessjobline.priority%TYPE := 2;
  PRIORITY_ERROR CONSTANT abslogprocessjobline.priority%TYPE := 3;
  PRIORITY_DEBUG CONSTANT abslogprocessjobline.priority%TYPE := 5;

  -- Private variable declarations
  gv_processname              abslogprocessjobpart.processname%type;
  gv_systemuserid             systemuser.systemuser_id%type := 0;
  gv_workstationid            workstation.workstation_id%type := 0;
  gv_abslogprocessjobid       abslogprocessjob.abslogprocessjob_id%type;
  gv_abslogprocessjobpartid   abslogprocessjobpart.abslogprocessjobpart_id%type;
  gv_message                  abslog.message%type;
  gv_returnvalue              number := RETURN_OK;
  gv_plantid                  plant.plant_id%type;
  gv_approvedstops            number;
  gv_notapplicablestops       number;
  gv_notapprovedstops         number;
  gv_approvedroutecontrols    number;
  gv_notapprovedroutecontrols number;
  gv_totalroutecontrols       number;
  gv_checkResult              number;
  gv_StopNotApproved          boolean;

  -- Function and procedure implementations
  procedure write_log_line(fp_message        in abslogprocessjobline.message%type,
                            fp_routenumber    in route.routenumber%type default null,
                            fp_drivingdate    in routecontrol.drivingdate%type default null,
                            fp_stopnumber     in stop.stopnumber%type default null,
                            fp_customernumber in customer.customernumber%type default null) is
  lv_message varchar2(1000) := fp_message;
  begin
    if (fp_routenumber is not null) then
      lv_message := rpad(fp_routenumber, 10) || rpad(nvl(to_char(fp_drivingdate, 'dd-mm-yyyy'), ' '), 12)
                    || rpad(nvl(to_char(fp_stopnumber), ' '), 12) || rpad(nvl(to_char(fp_customernumber), ' '), 15) || fp_message;
    end if;

    IF (gv_abslogprocessjobpartid IS NOT NULL) THEN
       abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_MESSAGE, lv_message, gv_systemuserid);
    END IF;
  exception
    when others then
      begin
        dbms_output.put_line(fp_message);
        dbms_output.put_line(sqlerrm);
        gv_returnvalue := RETURN_FAILED_UNKNOWN;
        raise gv_userexception;
      end;
  end write_log_line;

  procedure write_log_header is
  lv_message varchar2(1000);
  lv_workstation varchar2(45);
  lv_systemuser varchar2(20);
  begin
    if (nvl(gv_workstationid, 0) <> 0) then
       select (w.code || ' ' || w.description) into lv_workstation from workstation w where w.workstation_id = gv_workstationid;
    end if;
    if (nvl(gv_systemuserid, 0) <> 0) then
       select s.username into lv_systemuser from systemuser s where s.systemuser_id = gv_systemuserid;
    end if;

    IF (gv_abslogprocessjobpartid IS NOT NULL) THEN
       lv_message := rpad('Process:', 13) || 'Scrutiny';
       abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_MESSAGE, lv_message, gv_systemuserid);

       lv_message := rpad('Started:', 13) || to_char(sysdate, 'dd-mm-yyyy hh:mi:ss');
       abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_MESSAGE, lv_message, gv_systemuserid);

       lv_message := rpad('Workstation:', 13) || lv_workstation;
       abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_MESSAGE, lv_message, gv_systemuserid);

       lv_message := rpad('Systemuser:', 13) || lv_systemuser;
       abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_MESSAGE, lv_message, gv_systemuserid);

       lv_message := rpad('Route', 10) || rpad('Driving Date', 12) || rpad('Stop', 12) || rpad('Customer', 15) || 'Message';
       abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_MESSAGE, lv_message, gv_systemuserid);

       lv_message := rpad('-', 150, '-');
       abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_MESSAGE, lv_message, gv_systemuserid);
    END IF;
  exception
    when others then
      begin
        dbms_output.put_line('Scrutinity process - log header');
        dbms_output.put_line(sqlerrm);
        gv_returnvalue := RETURN_FAILED_UNKNOWN;
        raise gv_userexception;
      end;
  end write_log_header;

  procedure write_log_footer is
  lv_message varchar2(1000);
  begin
    IF (gv_abslogprocessjobpartid IS NOT NULL) THEN
       lv_message := rpad('-', 150, '-');
       abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_MESSAGE, lv_message, gv_systemuserid);

       lv_message := rpad('Total Stops:', 14) || rpad(gv_approvedstops + gv_notapplicablestops + gv_notapprovedstops, 6) ||
       ' Approved: ' || rpad(gv_approvedstops, 6) || ' Not applicable: ' || rpad(gv_notapplicablestops, 6) ||
       ' Not approved: ' || rpad(gv_notapprovedstops, 6);
       abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_MESSAGE, lv_message, gv_systemuserid);

       lv_message := rpad('Total Routes:', 14) || rpad(gv_totalroutecontrols, 6) || ' Approved: ' || rpad(gv_approvedroutecontrols, 6) ||
       rpad(' ', 23) || ' Not approved: ' || rpad(gv_notapprovedroutecontrols, 6);
       abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_MESSAGE, lv_message, gv_systemuserid);

       lv_message := rpad('Finished:', 14) || to_char(sysdate, 'dd-mm-yyyy hh:mi:ss');
       abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_MESSAGE, lv_message, gv_systemuserid);
    END IF;
  exception
    when others then
      begin
        dbms_output.put_line('Scrutinity process - log footer');
        dbms_output.put_line(sqlerrm);
        gv_returnvalue := RETURN_FAILED_UNKNOWN;
        raise gv_userexception;
      end;
  end write_log_footer;

  procedure end_log is
  begin
    --end_log
    if gv_abslogprocessjobid is not null then
      abslogwrite.abslogprocessjob_update(gv_abslogprocessjobid, sysdate, gv_systemuserid);
      gv_abslogprocessjobid := NULL;
    end if;
  exception
    when others then
      begin
        dbms_output.put_line(gv_message);
        dbms_output.put_line(sqlerrm);
      end;
  end end_log;

  procedure get_abslogprocessjobid(fp_plantprocessid in plantprocess.plantprocess_id%type,
                                   fp_plantid IN plant.plant_id%TYPE) is
  begin
    --write_log
    gv_abslogprocessjobid := abslogwrite.abslogprocessjob_write(fp_plantid, sysdate, null, null,
                                                                fp_plantprocessid, gv_systemuserid);
  end get_abslogprocessjobid;

  function get_systemsetting(fp_systemsettingname systemsetting.name%type,
                             fp_plant_id systemsetting.plant_id%TYPE) return NUMBER is
  lv_value NUMBER;
  begin
    --get_systemsetting
    gv_message := 'Error getting System Setting ' || fp_systemsettingname;
    select to_number( nvl(ss.value, ss.factorydefault) )
    into   lv_value
    from   systemsetting ss
    where  ss.plant_id = fp_plant_id
    and    ss.name = fp_systemsettingname;
    return lv_value;
  exception
    when others then
       raise_application_error( -20001, gv_message);
  end get_systemsetting;

  -- Check 1: Check if Customer is excluded from Scrutiny
  function check1(stopId stop.stop_id%type) return number is
  lv_result number := 0;
  begin
    for x in (select s.status,
                     s.scrutinystatus,
                     c.scrutinyexcludecheckbags,
                     c.scrutinyexcludecheckdv,
                     r.routenumber,
                     rc.drivingdate,
                     s.stopnumber,
                     c.customernumber
                from stop s, customer c, routecontrol rc, route r
               where c.customer_id = s.customer_link_id
                 and s.routecontrol_id = rc.routecontrol_id
                 and rc.route_id = r.route_id
                 and s.stop_id = stopId) loop
      if (x.status = 1 or x.status = 2 or (x.scrutinyexcludecheckdv = 1)) then
        update stop s set s.scrutinystatus = 10 where s.stop_id = stopId;
        lv_result := 1;
        gv_notapplicablestops := gv_notapplicablestops + 1;
        gv_message := 'Stop is set to Not Applicable. Customer is excluded from scrutiny check DV.';
        write_log_line(gv_message, x.routenumber, x.drivingdate, x.stopnumber, x.customernumber);

        for y in (select dv.deliveryvariations_id
                from deliveryvariations dv, distributionpoint dp, department d, stop s, routecontrol rc
               where dv.distributionpoint_id = dp.distributionpoint_id
                 and dp.department_id = d.department_id
                 and d.customer_id = s.customer_link_id
                 and (nvl(s.routestop_id, 0) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                 and dv.deliverydate <= rc.drivingdate
                 and dv.processed = 'N'
                 and s.routecontrol_id = rc.routecontrol_id
                 and s.stop_id = stopId) loop
          update deliveryvariations dv set dv.scrutinystatus = 10 where dv.deliveryvariations_id = y.deliveryvariations_id;
        end loop;
      end if;
    end loop;
    return lv_result;
  exception
    when others then
      gv_message := 'Error in check1: ' ||sqlerrm;
  end check1;

  -- Check 2: Check if all unloaded Bags are sorted
  function check2(stopId stop.stop_id%type) return number is
  lv_result number := 0;
  begin
    for x in (select distinct r.routenumber,
                              rc.drivingdate,
                              s.stopnumber,
                              c.customernumber
                from bagtag bt, stop s, customer c, routecontrol rc, route r
                             where bt.stop_id = s.stop_id
                 and bt.stay_id <> (select st.stay_id
                                      from stay st
                                     where st.staytype = 6
                                       and st.staynr = 70)
                 and s.routecontrol_id = rc.routecontrol_id
                 and rc.route_id = r.route_id
                 and s.customer_link_id = c.customer_id
                 and s.stop_id = stopId) loop
      update stop s set s.scrutinystatus = 20 where s.stop_id = stopId;
      update routecontrol rc set rc.scrutinystatus = 20 where rc.routecontrol_id = (select s.routecontrol_id from stop s where s.stop_id = stopId);
      lv_result := 1;
      gv_notapprovedstops := gv_notapprovedstops + 1;
      gv_StopNotApproved := true;
      gv_message := 'Stop is set to Not Approved (soil receiving), not all bags are soil counted.';
      write_log_line(gv_message, x.routenumber, x.drivingdate, x.stopnumber, x.customernumber);
    end loop;
    return lv_result;
  exception
    when others then
      gv_message := 'Error in check2: ' || sqlerrm;
  end check2;

  -- Check 3: Nominal Stock is below a predefined quantity
  function check3(stopId stop.stop_id%type) return number is
  lv_result number := 0;
  lv_minimumnominal number := 0;
  begin
    for x in (select plpd.product_id,
                     sum(rqa.quantity) rqa_qty,
                     nvl((select ca.processbusinessunit_id
                           from customeractivities ca, productgroup pg, product p
                          where ca.customer_id = c.customer_id
                            and ca.activity_id = pg.activity_id
                            and pg.productgroup_id = p.productgroup_id
                            and p.product_id = plpd.product_id
                            and rownum = 1),
                         c.defaultbusinessunit_id) bu,
                     r.routenumber,
                     rc.drivingdate,
                     s.stopnumber,
                     c.customernumber,
                     c.customer_id
                from rentalqtyadjustment        rqa,
                     productlistperdistribpoint plpd,
                     distributionpoint          dp,
                     department                 d,
                     stop                       s,
                     routecontrol               rc,
                     route                      r,
                     customer                   c
               where rqa.productlistperdistribpoint_id =
                     plpd.productlistperdistribpoint_id
                 and plpd.distributionpoint_id = dp.distributionpoint_id
                 and dp.department_id = d.department_id
                 and d.customer_id = s.customer_link_id
                 and s.routecontrol_id = rc.routecontrol_id
                 and rc.route_id = r.route_id
                 and c.customer_id = s.customer_link_id
                 and (nvl(s.routestop_id, 0) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                 and rqa.adjustmentdate <= rc.drivingdate
                 and rqa.origin <> 2
                 and s.stop_id = stopId
               group by plpd.product_id,
                        c.customer_id,
                        c.defaultbusinessunit_id,
                        r.routenumber,
                        rc.drivingdate,
                        s.stopnumber,
                        c.customernumber) loop
      begin
        select nvl(smn.minimumnominal, 0) into lv_minimumnominal from scrutinyminnominal smn where smn.businessunit_id = x.bu
             and smn.deliveriesperweek = (select count(1) from customerdeliveryday cdd where cdd.customer_id = x.customer_id);
      exception
        when no_data_found then null;
      end;

      if (lv_minimumnominal = 0) then
        select ('Minimum Nominal not defined for business unit ' || bu.code) into gv_message from businessunit bu where bu.businessunit_id = x.bu;
        write_log_line(gv_message, x.routenumber, x.drivingdate, x.stopnumber, x.customernumber);
        exit;
      elsif (x.rqa_qty < lv_minimumnominal) then
        update deliveryvariations dv1 set dv1.scrutinystatus = 10
               where dv1.deliveryvariations_id in
               (select dv.deliveryvariations_id
                  from deliveryvariations dv, distributionpoint dp, department d, stop s
                 where dv.distributionpoint_id = dp.distributionpoint_id
                   and dp.department_id = d.department_id
                   and d.customer_id = s.customer_link_id
                   and (nvl(s.routestop_id, 0) = 0 or
                       (select nvl(rs.deliverypoint_id, 0)
                           from routestop rs
                          where rs.routestop_id = s.routestop_id) = 0 or
                       (select nvl(rs.deliverypoint_id, 0)
                           from routestop rs
                          where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                   and dv.deliverydate <= x.drivingdate
                   and dv.processed = 'N'
                   and dv.product_id = x.product_id
                   and dv.scrutinystatus = 11
                   and (select fm.washtype from finishingmethod fm where fm.finishingmethod_id = dv.finishingmethod_id) != 1 -- not dry clean
                   and d.customer_id = x.customer_id);

      end if;
    end loop;

    for x in (select sum(case
                         when dv.scrutinystatus <> 10 then 1
                         else 0
                       end) dvno,
                   r.routenumber,
                   rc.drivingdate,
                   s.stopnumber,
                   c.customernumber
              from deliveryvariations dv,
                   distributionpoint  dp,
                   department         d,
                   stop               s,
                   routecontrol       rc,
                   route              r,
                   customer           c
             where dv.distributionpoint_id = dp.distributionpoint_id
               and dp.department_id = d.department_id
               and d.customer_id = s.customer_link_id
               and (nvl(s.routestop_id, 0) = 0 or
                   (select nvl(rs.deliverypoint_id, 0)
                       from routestop rs
                      where rs.routestop_id = s.routestop_id) = 0 or
                   (select nvl(rs.deliverypoint_id, 0)
                       from routestop rs
                      where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
               and dv.deliverydate <= rc.drivingdate
               and dv.processed = 'N'
               and s.routecontrol_id = rc.routecontrol_id
               and rc.route_id = r.route_id
               and c.customer_id = s.customer_link_id
               and s.stop_id = stopId
             group by r.routenumber, rc.drivingdate, s.stopnumber, c.customernumber
            ) loop
      if (x.dvno = 0) then -- if all products are not applicable - stop is approved
          update stop s set s.scrutinystatus = 30 where s.stop_id = stopId;
          lv_result := 1;
          gv_approvedstops := gv_approvedstops + 1;
          gv_message := 'Stop is set to Approved because of minimum order.';
          write_log_line(gv_message, x.routenumber, x.drivingdate, x.stopnumber, x.customernumber);
      end if;
    end loop;
    return lv_result;
  exception
    when others then
      gv_message := 'Error in check3: ' || sqlerrm;
  end check3;

  -- Check 4: Negative Delivery Variation Lines
  function check4(stopId stop.stop_id%type) return number is
  lv_result number := 0;
  begin
    gv_message := '';
    for x in (select dv.deliveryvariations_id, dv.deliverydate, rc.drivingdate, s.stopnumber
                from deliveryvariations dv, distributionpoint dp, department d, stop s, routecontrol rc, reasoncode res
               where dv.distributionpoint_id = dp.distributionpoint_id
                 and dp.department_id = d.department_id
                 and d.customer_id = s.customer_link_id
                 and (nvl(s.routestop_id, 0) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                 and dv.deliverydate <= rc.drivingdate
                 and dv.processed = 'N'
                 -- and dv.origin = 3
                 and dv.amount < 0
                 and dv.scrutinystatus = 11
                 and (select fm.washtype from finishingmethod fm where fm.finishingmethod_id = dv.finishingmethod_id) != 1 -- not dry clean
                 and s.routecontrol_id = rc.routecontrol_id
                 and s.stop_id = stopId
                 and (nvl(dv.reasoncode_id, 0) = 0 or
                     (select res.excludescrutinycheck from reasoncode res where res.reasoncode_id = dv.reasoncode_id) <> 'Y')) loop
      update deliveryvariations dv set dv.scrutinystatus = 21 where dv.deliveryvariations_id = x.deliveryvariations_id;
         lv_result := 1;
    end loop;

    if (lv_result = 1) then
      for x in (select r.routenumber, rc.drivingdate, s.stopnumber, c.customernumber
                  from stop s, routecontrol rc, route r, customer c
                 where s.routecontrol_id = rc.routecontrol_id
                   and rc.route_id = r.route_id
                   and c.customer_id = s.customer_link_id
                   and s.stop_id = stopId) loop
            update stop s set s.scrutinystatus = 21 where s.stop_id = stopId;
            if not(gv_StopNotApproved) then
               gv_notapprovedstops := gv_notapprovedstops + 1;
               gv_StopNotApproved := true;
            end if;
            gv_message := 'Stop is set to Not Approved because of negative delivery variations.';
            write_log_line(gv_message, x.routenumber, x.drivingdate, x.stopnumber, x.customernumber);
      end loop;
    end if;
    return lv_result;
  exception
    when others then
      gv_message := 'Error in check4: ' || sqlerrm;
  end check4;

  -- Check 5: Delivery Variation with Reason Code Exclude from Scrutiny
  function check5(stopId stop.stop_id%type) return number is
  lv_result number := 0;
  begin
    for x in (select dv.deliveryvariations_id
                from deliveryvariations dv, distributionpoint dp, department d, stop s, routecontrol rc, reasoncode res
               where dv.distributionpoint_id = dp.distributionpoint_id
                 and dp.department_id = d.department_id
                 and d.customer_id = s.customer_link_id
                 and (nvl(s.routestop_id, 0) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                 and dv.deliverydate <= rc.drivingdate
                 and dv.processed = 'N'
                 and dv.scrutinystatus = 11
                 and dv.reasoncode_id = res.reasoncode_id
                 and res.excludescrutinycheck = 'Y'
                 and s.routecontrol_id = rc.routecontrol_id
                 and s.stop_id = stopId) loop
      update deliveryvariations dv set dv.scrutinystatus = 10 where dv.deliveryvariations_id = x.deliveryvariations_id;
    end loop;

    for x in (select sum(case
                         when dv.scrutinystatus <> 10 then 1
                         else 0
                       end) dvno,
                   r.routenumber,
                   rc.drivingdate,
                   s.stopnumber,
                   c.customernumber
              from deliveryvariations dv,
                   distributionpoint  dp,
                   department         d,
                   stop               s,
                   routecontrol       rc,
                   route              r,
                   customer           c
             where dv.distributionpoint_id = dp.distributionpoint_id
               and dp.department_id = d.department_id
               and d.customer_id = s.customer_link_id
               and (nvl(s.routestop_id, 0) = 0 or
                   (select nvl(rs.deliverypoint_id, 0)
                       from routestop rs
                      where rs.routestop_id = s.routestop_id) = 0 or
                   (select nvl(rs.deliverypoint_id, 0)
                       from routestop rs
                      where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
               and dv.deliverydate <= rc.drivingdate
               and dv.processed = 'N'
               and s.routecontrol_id = rc.routecontrol_id
               and rc.route_id = r.route_id
               and c.customer_id = s.customer_link_id
               and s.stop_id = stopId
             group by r.routenumber, rc.drivingdate, s.stopnumber, c.customernumber
            ) loop
      if (x.dvno = 0) then -- if all products are not applicable - stop is approved
          update stop s set s.scrutinystatus = 30 where s.stop_id = stopId;
          lv_result := 1;
          gv_approvedstops := gv_approvedstops + 1;
          gv_message := 'Stop is set to Approved because of Reason Code Exclude from Scrutiny.';
          write_log_line(gv_message, x.routenumber, x.drivingdate, x.stopnumber, x.customernumber);
      end if;
    end loop;
    return lv_result;
  exception
    when others then
      gv_message := 'Error in check5: ' || sqlerrm;
  end check5;

  -- Check 6: Missing Products
  function check6(stopId stop.stop_id%type) return number is
  lv_result number := 0;
  dvId number;
  dvAmount number;
  m_nominal number;
  existsDV number;

  begin
    for x in (select sd.distributionpoint_id,
                   plpd.product_id,
                   plpd.sizedefinition_id,
                   plpd.finishingmethod_id,
                   rc.drivingdate,
                   s.stopnumber,
                   (select p.code from product p where p.product_id = plpd.product_id) prod,
                   (select sd.code from sizedefinition sd where sd.sizedefinition_id = plpd.sizedefinition_id) sz
              from standarddelivery           sd,
                   standarddeliveryline       sdl,
                   standarddeliveryamount     sda,
                   distributionpoint          dp,
                   department                 d,
                   stop                       s,
                   routecontrol               rc,
                   productlistperdistribpoint plpd,
                   productlistpercustomer     plpc
             where sd.standarddelivery_id = sdl.standarddelivery_id
               and sdl.standarddeliveryline_id = sda.standarddeliveryline_id
               and sd.distributionpoint_id = dp.distributionpoint_id
               and dp.department_id = d.department_id
               and d.customer_id = s.customer_link_id
               and (nvl(s.routestop_id, 0) = 0 or
                   (select nvl(rs.deliverypoint_id, 0)
                       from routestop rs
                      where rs.routestop_id = s.routestop_id) = 0 or
                   (select nvl(rs.deliverypoint_id, 0)
                       from routestop rs
                      where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
               and sdl.dateactive <= rc.drivingdate
               and sdl.dateinactive > rc.drivingdate
               and sda.day_id = GETABSDAYNUMBER(rc.drivingdate)
               and sdl.productlistperdistribpoint_id =
                   plpd.productlistperdistribpoint_id
               and s.routecontrol_id = rc.routecontrol_id
               and s.stop_id = stopId
               and plpc.customer_id = s.customer_link_id
               and plpc.product_id = plpd.product_id
               and plpc.finishingmethod_id = plpd.finishingmethod_id
               and ((plpc.sizedefinition_id = plpd.sizedefinition_id) or
                   (plpc.sizedefinition_id = (select sz.sizedefinition_id from sizedefinition sz where sz.code = '*')))
               and plpc.soilcountmethod <> 0) loop
      begin
        gv_message := '';
        m_nominal := 0;
          begin
            select sum(rqa.quantity) into m_nominal
            from rentalqtyadjustment rqa, productlistperdistribpoint plpd
            where rqa.productlistperdistribpoint_id = plpd.productlistperdistribpoint_id
             and plpd.distributionpoint_id = x.distributionpoint_id
             and plpd.product_id = x.product_id
             and plpd.sizedefinition_id = x.sizedefinition_id
             and plpd.finishingmethod_id = x.finishingmethod_id
             and rqa.adjustmentdate <= x.drivingdate
             and rqa.origin <> 2;
          exception
           when no_data_found then null;
          end;

       if (m_nominal <> 0) then
           existsDV := 0;
           for y in
              (select dv.deliveryvariations_id dvId, dv.amount dvAmount, dv.scrutinystatus, dv.deliverydate, (select fm.washtype from finishingmethod fm where fm.finishingmethod_id = dv.finishingmethod_id) isDryClean
                from deliveryvariations dv
               where dv.distributionpoint_id = x.distributionpoint_id
                 and dv.product_id = x.product_id
                 and dv.sizedefinition_id = x.sizedefinition_id
                 and dv.finishingmethod_id = x.finishingmethod_id
                 and dv.deliverydate = x.drivingdate
                 and dv.origin = 1
                 and dv.amount >= 0) loop
             existsDV := 1;
             if (y.dvAmount = 0 and y.scrutinystatus <> 10 and y.isdryclean <> 1) then -- if DV is not applicable or is a dry clean - do nothing
               update deliveryvariations dv set dv.scrutinystatus = 21 where dv.deliveryvariations_id = y.dvId;
               gv_message := 'Delivery variation set to Not Approved because of missing product (' || x.prod || '/' || x.sz || ') for stop: ' || x.stopnumber || ', on date: ' || to_char(y.deliverydate, 'dd Mon yyyy');
               write_log_line(gv_message);
                 lv_result := 1;
             end if;
           end loop;

          if (existsDV != 1) then
            insert into deliveryvariations(deliveryvariations_id, distributionpoint_id, product_id, sizedefinition_id, finishingmethod_id,
                                            deliverydate, origin, soilcountsubtype, amount, reference, updaterentqty, fixedamount,
                                            processed, scrutinystatus, systemuser_id, timestamp)
                        values(seq_deliveryvariations.nextval, x.distributionpoint_id, x.product_id, x.sizedefinition_id,
                               x.finishingmethod_id, x.drivingdate, 1, 1, 0, 'Scrutiny', 'N', 'N', 'N', 21, gv_systemuserid, sysdate);
            gv_message := 'Delivery variation created because of missing product (' || x.prod || '/' || x.sz || ') for stop: ' || x.stopnumber || ', on date: ' || to_char(x.drivingdate, 'dd Mon yyyy');
            write_log_line(gv_message);
            lv_result := 1;
          end if;
      end if;
     end;
    end loop;

    if (lv_result = 1) then
      for x in (select r.routenumber, rc.drivingdate, s.stopnumber, c.customernumber
                  from stop s, routecontrol rc, route r, customer c
                 where s.routecontrol_id = rc.routecontrol_id
                   and rc.route_id = r.route_id
                   and c.customer_id = s.customer_link_id
                   and s.stop_id = stopId) loop
          update stop s set s.scrutinystatus = 21 where s.stop_id = stopId;
          if not(gv_StopNotApproved) then
             gv_notapprovedstops := gv_notapprovedstops + 1;
             gv_StopNotApproved := true;
          end if;
          gv_message := 'Stop is set to Not Approved because of missing products.';
          write_log_line(gv_message, x.routenumber, x.drivingdate, x.stopnumber, x.customernumber);
      end loop;
    end if;

    return lv_result;
  exception
    when others then
      gv_message := 'Error in check6: ' || sqlerrm;
  end check6;

  -- Check 7-8: Average and Deviation and Nominal
  function check7_8(stopId stop.stop_id%type) return number is
  lv_result number := 0;
  ss_weekavgcalcper number;
  ss_soildeviationsoilpercpos number;
  ss_soildeviationsoilpercneg number;
  m_delivery numbers_array;
  lv_delNo number := 0;
  m_average number := 0;
  m_quantity number := 0;
  lv_totalDel number := 0;
  m_nominal number := 0;
  begin
    ss_weekavgcalcper := get_systemsetting('WEEKAVGCALCPER', gv_plantid);
    ss_soildeviationsoilpercpos := get_systemsetting('SOILDEVIATIONSOILPERCPOS', gv_plantid);
    ss_soildeviationsoilpercneg := get_systemsetting('SOILDEVIATIONSOILPERCNEG', gv_plantid);
    for x in (select dv.distributionpoint_id,
                     dv.product_id,
                     dv.sizedefinition_id,
                     dv.finishingmethod_id,
                     rc.drivingdate,
                     s.stopnumber,
                     nvl(sum(dv.amount), 0) amount
                from deliveryvariations dv,
                     distributionpoint  dp,
                     department         d,
                     stop               s,
                     routecontrol       rc
               where dv.distributionpoint_id = dp.distributionpoint_id
                 and dp.department_id = d.department_id
                 and d.customer_id = s.customer_link_id
                 and (nvl(s.routestop_id, 0) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                 and dv.deliverydate <= rc.drivingdate
                 and dv.processed = 'N'
                 and dv.scrutinystatus = 11
                 and (select fm.washtype from finishingmethod fm where fm.finishingmethod_id = dv.finishingmethod_id) != 1 -- not dry clean
                 and dv.origin = 1
                 and s.routecontrol_id = rc.routecontrol_id
                 and s.stop_id = stopId
               group by dv.distributionpoint_id,
                        dv.product_id,
                        dv.sizedefinition_id,
                        dv.finishingmethod_id,
                        rc.drivingdate,
                        s.stopnumber) loop
      m_quantity := x.amount;

      -- calculate m_delivery / m_average
      select /*+index(PNL XAK2PACKINGNOTELINE) */ sum(pnl.deliveredamount) bulk collect into m_delivery
        from packingnote pn, packingnoteline pnl, routecontrol rc, route r
       where pn.packingnote_id = pnl.packingnote_id
         and pn.distributionpoint_id = x.distributionpoint_id
         and pnl.product_id = x.product_id
         and pnl.sizedefinition_id = x.sizedefinition_id
         and pnl.finishingmethod_id = x.finishingmethod_id
         and pn.routecontrol_id = rc.routecontrol_id
         and rc.drivingdate < x.drivingdate
         and pn.route_id = r.route_id
         and r.drivingday_id = GETABSDAYNUMBER(x.drivingdate)
         and PN.DELIVERYDATE between (getdateforweekyear(GETABSDAYNUMBER(x.drivingdate), getisoweek(sysdate), year(sysdate)) - 7 * ss_weekavgcalcper)
                                      and (getdateforweekyear(GETABSDAYNUMBER(x.drivingdate), getisoweek(sysdate), year(sysdate)))
       group by pn.deliverydate
       order by pn.deliverydate desc;

       lv_delNo := 0;
       lv_totalDel := 0;
       for y in 1..m_delivery.count loop
         lv_delNo := lv_delNo + 1;
         lv_totalDel := lv_totalDel + m_delivery(y);
       end loop;

       if (lv_delNo != 0) then
          m_average := round(lv_totalDel/lv_delNo);
       else
          m_average := 0;
       end if;

       -- calculate m_nominal
       select nvl(sum(rqa.quantity), 0) into m_nominal
          from rentalqtyadjustment rqa, productlistperdistribpoint plpd
        where rqa.productlistperdistribpoint_id = plpd.productlistperdistribpoint_id
           and plpd.distributionpoint_id = x.distributionpoint_id
           and plpd.product_id = x.product_id
           and plpd.sizedefinition_id = x.sizedefinition_id
           and plpd.finishingmethod_id = x.finishingmethod_id
           and rqa.adjustmentdate <= x.drivingdate
           and rqa.origin <> 2;

       gv_message := '';

       if (m_average = 0
        or ((m_average > m_quantity) and (round(((m_average - m_quantity) / m_average)*100) > ss_soildeviationsoilpercpos))
        or ((m_average <= m_quantity) and (round(((m_quantity - m_average) / m_average)*100) > ss_soildeviationsoilpercneg))
        or (m_quantity > m_nominal)) then -- update DV
         for y in (select dv.deliveryvariations_id, dv.deliverydate
                    from deliveryvariations dv,
                         distributionpoint  dp,
                         department         d,
                         stop               s,
                         routecontrol       rc
                   where dv.distributionpoint_id = dp.distributionpoint_id
                     and dp.department_id = d.department_id
                     and d.customer_id = s.customer_link_id
                     and (nvl(s.routestop_id, 0) = 0 or
                         (select nvl(rs.deliverypoint_id, 0)
                             from routestop rs
                            where rs.routestop_id = s.routestop_id) = 0 or
                         (select nvl(rs.deliverypoint_id, 0)
                             from routestop rs
                            where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                     and dv.deliverydate <= rc.drivingdate
                     and dv.processed = 'N'
                     and dv.scrutinystatus = 11
                     and (select fm.washtype from finishingmethod fm where fm.finishingmethod_id = dv.finishingmethod_id) != 1 -- not dry clean
                     and dv.origin = 1
                     and s.routecontrol_id = rc.routecontrol_id
                     and s.stop_id = stopId) loop
           update deliveryvariations dv set dv.scrutinystatus = 21 where dv.deliveryvariations_id = y.deliveryvariations_id;
              lv_result := 1;
         end loop;
       end if;
    end loop;

    if (lv_result = 1) then
      for x in (select r.routenumber, rc.drivingdate, s.stopnumber, c.customernumber
                  from stop s, routecontrol rc, route r, customer c
                 where s.routecontrol_id = rc.routecontrol_id
                   and rc.route_id = r.route_id
                   and c.customer_id = s.customer_link_id
                   and s.stop_id = stopId) loop
          update stop s set s.scrutinystatus = 21 where s.stop_id = stopId;
          if not(gv_StopNotApproved) then
             gv_notapprovedstops := gv_notapprovedstops + 1;
             gv_StopNotApproved := true;
          end if;
          gv_message := 'Stop is set to Not Approved because of deviation from average.';
          write_log_line(gv_message, x.routenumber, x.drivingdate, x.stopnumber, x.customernumber);
      end loop;
    end if;
    return lv_result;

  exception
    when others then
      gv_message := 'Error in check7_8: ' || sqlerrm;
  end check7_8;

  -- Check 9: Dry Cleaning
  function check9(stopId stop.stop_id%type) return number is
  lv_result number := 0;
  begin
    gv_message := '';
    for x in (select dv.deliveryvariations_id, dv.deliverydate, rc.drivingdate, s.stopnumber
                from deliveryvariations dv, distributionpoint dp, department d, stop s, routecontrol rc, finishingmethod fm
               where dv.distributionpoint_id = dp.distributionpoint_id
                 and dp.department_id = d.department_id
                 and d.customer_id = s.customer_link_id
                 and (nvl(s.routestop_id, 0) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                 and dv.deliverydate <= rc.drivingdate
                 and dv.processed = 'N'
                 and dv.scrutinystatus = 11
                 and dv.finishingmethod_id = fm.finishingmethod_id
                 and fm.washtype = 1
                 and dv.reference is null
                 and s.routecontrol_id = rc.routecontrol_id
                 and s.stop_id = stopId) loop
      update deliveryvariations dv set dv.scrutinystatus = 21 where dv.deliveryvariations_id = x.deliveryvariations_id;
         lv_result := 1;
    end loop;

    if (lv_result = 1) then
      for x in (select r.routenumber, rc.drivingdate, s.stopnumber, c.customernumber
                  from stop s, routecontrol rc, route r, customer c
                 where s.routecontrol_id = rc.routecontrol_id
                   and rc.route_id = r.route_id
                   and c.customer_id = s.customer_link_id
                   and s.stop_id = stopId) loop
          update stop s set s.scrutinystatus = 21 where s.stop_id = stopId;
          if not(gv_StopNotApproved) then
             gv_notapprovedstops := gv_notapprovedstops + 1;
             gv_StopNotApproved := true;
          end if;
          gv_message := 'Stop is set to Not Approved because of dry clean without reference.';
          write_log_line(gv_message, x.routenumber, x.drivingdate, x.stopnumber, x.customernumber);
      end loop;
    end if;
    return lv_result;
  exception
    when others then
      gv_message := 'Error in check9: ' || sqlerrm;
  end check9;

  -- Check 10: Heavy Soil
  function check10(stopId stop.stop_id%type) return number is
  lv_result number := 0;
  m_total number := 0;
  m_heavysoil number := 0;
  ss_scrutinyheavysoil number := 0;
  begin
    ss_scrutinyheavysoil := get_systemsetting('SCRUTINYHEAVYSOIL', gv_plantid);
    select total, heavysoil into m_total, m_heavysoil from
    (select nvl(sum(dv.amount), 0) total,
           nvl(sum(case
                     when (dv.origin = 1 and dv.soilcountsubtype = 4) then dv.amount
                     else 0
                   end),
               0) heavysoil
      from deliveryvariations dv,
           distributionpoint  dp,
           department         d,
           stop               s,
           routecontrol       rc
     where dv.distributionpoint_id = dp.distributionpoint_id
       and dp.department_id = d.department_id
       and d.customer_id = s.customer_link_id
       and (nvl(s.routestop_id, 0) = 0 or
           (select nvl(rs.deliverypoint_id, 0)
               from routestop rs
              where rs.routestop_id = s.routestop_id) = 0 or
           (select nvl(rs.deliverypoint_id, 0)
               from routestop rs
              where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
       and dv.deliverydate <= rc.drivingdate
       and dv.processed = 'N'
       and (select fm.washtype from finishingmethod fm where fm.finishingmethod_id = dv.finishingmethod_id) != 1 -- not dry clean
       and s.routecontrol_id = rc.routecontrol_id
       and s.stop_id = stopId);

    if (m_total <> 0) then
      gv_message := '';
      if ((m_heavysoil / m_total * 100) > ss_scrutinyheavysoil) then
        for x in (select dv.deliveryvariations_id, dv.deliverydate, rc.drivingdate, s.stopnumber
                    from deliveryvariations dv, distributionpoint dp, department d, stop s, routecontrol rc
                   where dv.distributionpoint_id = dp.distributionpoint_id
                     and dp.department_id = d.department_id
                     and d.customer_id = s.customer_link_id
                     and (nvl(s.routestop_id, 0) = 0 or
                         (select nvl(rs.deliverypoint_id, 0)
                             from routestop rs
                            where rs.routestop_id = s.routestop_id) = 0 or
                         (select nvl(rs.deliverypoint_id, 0)
                             from routestop rs
                            where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                     and dv.deliverydate <= rc.drivingdate
                     and dv.processed = 'N'
                     and dv.origin = 1
                     and dv.scrutinystatus <> 10
                     and (select fm.washtype from finishingmethod fm where fm.finishingmethod_id = dv.finishingmethod_id) != 1 -- not dry clean
                     and dv.soilcountsubtype = 4
                     and s.routecontrol_id = rc.routecontrol_id
                     and s.stop_id = stopId) loop
          update deliveryvariations dv set dv.scrutinystatus = 21 where dv.deliveryvariations_id = x.deliveryvariations_id;
             lv_result := 1;
        end loop;
      end if;
    end if;

    if (lv_result = 1) then
      for x in (select r.routenumber, rc.drivingdate, s.stopnumber, c.customernumber
                  from stop s, routecontrol rc, route r, customer c
                 where s.routecontrol_id = rc.routecontrol_id
                   and rc.route_id = r.route_id
                   and c.customer_id = s.customer_link_id
                   and s.stop_id = stopId) loop
          update stop s set s.scrutinystatus = 21 where s.stop_id = stopId;
          if not(gv_StopNotApproved) then
             gv_notapprovedstops := gv_notapprovedstops + 1;
             gv_StopNotApproved := true;
          end if;
          gv_message := 'Stop is set to Not Approved because heavy soil exceeds maximum percentage.';
          write_log_line(gv_message, x.routenumber, x.drivingdate, x.stopnumber, x.customernumber);
      end loop;
    end if;
    return lv_result;
  exception
    when others then
      gv_message := 'Error in check10: ' || sqlerrm;
  end check10;

  -- Check 11: Clean Return
  function check11(stopId stop.stop_id%type) return number is
  lv_result number := 0;
  begin
    gv_message := '';
    for x in (select dv.deliveryvariations_id, dv.deliverydate, rc.drivingdate, s.stopnumber
                from deliveryvariations dv, distributionpoint dp, department d, stop s, routecontrol rc
               where dv.distributionpoint_id = dp.distributionpoint_id
                 and dp.department_id = d.department_id
                 and d.customer_id = s.customer_link_id
                 and (nvl(s.routestop_id, 0) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                 and dv.deliverydate <= rc.drivingdate
                 and dv.processed = 'N'
                 and dv.origin = 1
                 and dv.scrutinystatus <> 10
                 and (select fm.washtype from finishingmethod fm where fm.finishingmethod_id = dv.finishingmethod_id) != 1 -- not dry clean
                 and dv.soilcountsubtype = 5
                 and s.routecontrol_id = rc.routecontrol_id
                 and s.stop_id = stopId) loop
      update deliveryvariations dv set dv.scrutinystatus = 21 where dv.deliveryvariations_id = x.deliveryvariations_id;
         lv_result := 1;
    end loop;

    if (lv_result = 1) then
      for x in (select r.routenumber, rc.drivingdate, s.stopnumber, c.customernumber
                  from stop s, routecontrol rc, route r, customer c
                 where s.routecontrol_id = rc.routecontrol_id
                   and rc.route_id = r.route_id
                   and c.customer_id = s.customer_link_id
                   and s.stop_id = stopId) loop
            update stop s set s.scrutinystatus = 21 where s.stop_id = stopId;
            if not(gv_StopNotApproved) then
               gv_notapprovedstops := gv_notapprovedstops + 1;
               gv_StopNotApproved := true;
            end if;
            gv_message := 'Stop is set to Not Approved because stop has clean return.';
            write_log_line(gv_message, x.routenumber, x.drivingdate, x.stopnumber, x.customernumber);
      end loop;
    end if;
    return lv_result;
  exception
    when others then
      gv_message := 'Error in check11: ' || sqlerrm;
  end check11;

  -- Check 12: 'Both COG and LOG, same wash price' products
  function check12(stopId stop.stop_id%type) return number is
  lv_result number := 0;
  begin
    for x in (select dv.deliveryvariations_id
                from deliveryvariations dv, distributionpoint dp, department d, stop s, routecontrol rc, productlistpercustomer plpc
               where dv.distributionpoint_id = dp.distributionpoint_id
                 and dp.department_id = d.department_id
                 and d.customer_id = s.customer_link_id
                 and (nvl(s.routestop_id, 0) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = 0 or
                     (select nvl(rs.deliverypoint_id, 0)
                         from routestop rs
                        where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                 and dv.deliverydate <= rc.drivingdate
                 and dv.processed = 'N'
                 and dv.scrutinystatus = 11
                 and s.routecontrol_id = rc.routecontrol_id
                 and s.stop_id = stopId
                 and dv.product_id = plpc.product_id
                 and dv.sizedefinition_id = plpc.sizedefinition_id
                 and dv.finishingmethod_id = plpc.finishingmethod_id
                 and d.customer_id = plpc.customer_id
                 and dv.deliverydate between plpc.dateactive and plpc.dateinactive
                 and plpc.billingmethod = 4
                 and plpc.chargewashesonlyforcog = 0) loop
      update deliveryvariations dv set dv.scrutinystatus = 10 where dv.deliveryvariations_id = x.deliveryvariations_id;
    end loop;

    for x in (select sum(case
                         when dv.scrutinystatus <> 10 then 1
                         else 0
                       end) dvno,
                   r.routenumber,
                   rc.drivingdate,
                   s.stopnumber,
                   c.customernumber
              from deliveryvariations dv,
                   distributionpoint  dp,
                   department         d,
                   stop               s,
                   routecontrol       rc,
                   route              r,
                   customer           c
             where dv.distributionpoint_id = dp.distributionpoint_id
               and dp.department_id = d.department_id
               and d.customer_id = s.customer_link_id
               and (nvl(s.routestop_id, 0) = 0 or
                   (select nvl(rs.deliverypoint_id, 0)
                       from routestop rs
                      where rs.routestop_id = s.routestop_id) = 0 or
                   (select nvl(rs.deliverypoint_id, 0)
                       from routestop rs
                      where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
               and dv.deliverydate <= rc.drivingdate
               and dv.processed = 'N'
               and s.routecontrol_id = rc.routecontrol_id
               and rc.route_id = r.route_id
               and c.customer_id = s.customer_link_id
               and s.stop_id = stopId
             group by r.routenumber, rc.drivingdate, s.stopnumber, c.customernumber
            ) loop
      if (x.dvno = 0) then -- if all products are not applicable - stop is approved
          update stop s set s.scrutinystatus = 30 where s.stop_id = stopId;
          lv_result := 1;
          gv_approvedstops := gv_approvedstops + 1;
          gv_message := 'Stop is set to Approved because all products are ''Both COG and LOG, same wash price''.';
          write_log_line(gv_message, x.routenumber, x.drivingdate, x.stopnumber, x.customernumber);
      end if;
    end loop;
    return lv_result;
  exception
    when others then
      gv_message := 'Error in check12: ' || sqlerrm;
  end check12;

  PROCEDURE scrutinyProcess(plantId            plant.plant_id%type,
                            stopIds            numbers_array,
                            plantprocessId     plantprocess.plantprocess_id%type,
                            dialogId           dialogs.dialog_id%type,
                            abslogprocessjobId in out abslogprocessjob.abslogprocessjob_id%type,
                            languageId         languages.language_id%type,
                            systemuserId       systemuser.systemuser_id%type,
                            workstationId      workstation.workstation_id%type) is

  type rcIdsList is table of routecontrol.routecontrol_id%type;
  lv_rcIds rcIdsList;

--  gv_checkResult number := 0;
  lv_stops numbers_array := stopIds;
  lv_scrutinyExcludeCheckDv number := 0;
  lv_scrutinyExcludeCheckBags number := 0;
  lv_checksList numbers_array := numbers_array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
  lv_pnGenerated number;
  locator varchar2(300) := '';
  begin
    --START
    gv_abslogprocessjobpartid := null;
    gv_abslogprocessjobid := abslogprocessjobId;
    gv_processname := 'pack_scrutiny.scrutinyProcess';
    gv_message := gv_processname;
    gv_plantid := plantId;
    if (systemuserId is not null) then
       gv_systemuserid := systemuserId;
    end if;
    gv_workstationid := workstationId;
    gv_approvedstops := 0;
    gv_notapplicablestops := 0;
    gv_notapprovedstops := 0;
    --check parameter not null
/*    if fp_plantprocessid is null then
      begin
        gv_message     := 'parameter fp_plantprocessid cannot be left empty!';
        gv_returnvalue := RETURN_FAILED_UNKNOWN;
        raise gv_userexception;
      end;
    end if;*/

    --get abslog process job id
    if (gv_abslogprocessjobid is null) then
       gv_abslogprocessjobid := abslogwrite.abslogprocessjob_write(plantId,
                                                                   sysdate,
                                                                   workstationId,
                                                                   dialogId,
                                                                   plantprocessId,
                                                                   nvl(systemuserId, gv_systemuserid));
    end if;
    abslogprocessjobId := gv_abslogprocessjobid;

    --write_log part
    if gv_abslogprocessjobpartid is null and gv_abslogprocessjobid is not null then
      gv_abslogprocessjobpartid := abslogwrite.abslogprocessjobpart_write(gv_abslogprocessjobid,
                                                                          gv_processname,
                                                                          nvl(systemuserId, gv_systemuserid));
    end if;

    -- initialize check list
    begin
      select sc.check1, sc.check2, sc.check3, sc.check4, sc.check5, sc.check6, sc.check7, sc.check8, sc.check9, sc.check10
               into lv_checksList(1),
                   lv_checksList(2),
                   lv_checksList(3),
                   lv_checksList(4),
                   lv_checksList(5),
                   lv_checksList(6),
                   lv_checksList(7),
                   lv_checksList(8),
                   lv_checksList(9),
                   lv_checksList(10) from scrutinycheck sc where sc.plant_id = gv_plantid;
    exception
      when others then
           lv_checksList := numbers_array(0, 1, 1, 1, 1, 1, 1, 1, 1, 1);  -- all checks will be performed
    end;

    -- process stops if plant was set to be excluded from scrutiny check
    if (lv_checksList(1) = 1) then
        gv_message := 'All plant customers are excluded from scrutiny check.';
        write_log_line(gv_message);
        return;
    end if;

    -- initialize stops array if null
    if (lv_stops is null) then
      select s.stop_id bulk collect into lv_stops
        from routecontrol rc, stop s
       where rc.routecontrol_id = s.routecontrol_id
         and rc.plant_id = plantId
         and trunc(rc.drivingdate) >= trunc(sysdate)
         and trunc(rc.drivingdate) <= trunc(sysdate) + 1
         and rc.scrutinystatus <> 30
         for update;
    end if;

    -- report header:
    write_log_header;
    
    if (lv_stops.count > 0) then
    for x in lv_stops.First..lv_stops.Last loop
      -- string of stops id's
    --  lv_stopsIds := lv_stopsIds || ', ' || lv_stops(x);
      locator := 'stop_id for a certain check do not exists' || ' -Stop :' || to_char(lv_stops(x))   ;
      
      begin
        -- skip approved stops for which PN was generated
        select count(1) into lv_pnGenerated from packingnote pn, stop s where pn.stop_id = s.stop_id and s.stop_id = lv_stops(x) and s.scrutinystatus = 30;
        if (lv_pnGenerated > 0) then
          continue;
        end if;

        gv_StopNotApproved := false;

        -- read customer's exclusion flags
        select c.scrutinyexcludecheckbags, c.scrutinyexcludecheckdv
          into lv_scrutinyExcludeCheckBags, lv_scrutinyExcludeCheckDv
          from stop s, customer c, routecontrol rc, route r
         where c.customer_id = s.customer_link_id
           and s.routecontrol_id = rc.routecontrol_id
           and rc.route_id = r.route_id
           and s.stop_id = lv_stops(x);

   /*     -- Check 1: Check if Customer is excluded from Scrutiny
        gv_checkResult := check1(lv_stops(x));
        if (gv_checkResult = 1 and lv_scrutinyExcludeCheckBags = 1) then
          continue;
        end if;*/

        -- Check 2: Check if all unloaded Bags are sorted
        if (lv_checksList(2) = 1) then
          if (lv_scrutinyExcludeCheckBags = 0) then
            gv_checkResult := check2(lv_stops(x));
            if (gv_checkResult = 1) then
              continue;
            end if;
          end if;
        end if;

        -- Check 1: Check if Customer is excluded from Scrutiny
        gv_checkResult := check1(lv_stops(x));
        if (gv_checkResult = 1) then
          continue;
        end if;

        -- set all stop's DV (except approved ones) to 'not checked'
        if (lv_scrutinyExcludeCheckDv = 0) then
          for y in (select dv.deliveryvariations_id
                      from deliveryvariations dv, distributionpoint dp, department d, stop s, routecontrol rc
                     where dv.distributionpoint_id = dp.distributionpoint_id
                       and dp.department_id = d.department_id
                       and d.customer_id = s.customer_link_id
                       and (nvl(s.routestop_id, 0) = 0 or
                           (select nvl(rs.deliverypoint_id, 0)
                               from routestop rs
                              where rs.routestop_id = s.routestop_id) = 0 or
                           (select nvl(rs.deliverypoint_id, 0)
                               from routestop rs
                              where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                       and dv.deliverydate <= rc.drivingdate
                       and dv.processed = 'N'
                       and dv.scrutinystatus <> 30
                       and s.routecontrol_id = rc.routecontrol_id
                       and s.stop_id = lv_stops(x)) loop
            update deliveryvariations dv set dv.scrutinystatus = 11 where dv.deliveryvariations_id = y.deliveryvariations_id;
          end loop;
        end if;

        -- Check 12: 'Both COG and LOG, same wash price' products
        if (lv_scrutinyExcludeCheckDv = 0) then
          gv_checkResult := check12(lv_stops(x));
          if (gv_checkResult = 1) then
            continue;
          end if;
        end if;

        -- Check 3: Nominal Stock is below a predefined quantity
        if (lv_checksList(3) = 1) then
          if (lv_scrutinyExcludeCheckDv = 0) then
            gv_checkResult := check3(lv_stops(x));
            if (gv_checkResult = 1) then
              continue;
            end if;
          end if;
        end if;

        -- Check 5: Delivery Variation with Reason Code Exclude from Scrutiny
        if (lv_checksList(5) = 1) then
          if (lv_scrutinyExcludeCheckDv = 0) then
            gv_checkResult := check5(lv_stops(x));
            if (gv_checkResult = 1) then
              continue;
            end if;
          end if;
        end if;

        -- Check 4: Negative Delivery Variation Lines
        if (lv_checksList(4) = 1) then
          if (lv_scrutinyExcludeCheckDv = 0) then
            gv_checkResult := greatest(gv_checkResult, check4(lv_stops(x)));
          end if;
        end if;

        -- Check 6: Missing Products
        if (lv_checksList(6) = 1) then
          if (lv_scrutinyExcludeCheckDv = 0) then
            gv_checkResult := greatest(gv_checkResult, check6(lv_stops(x)));
          end if;
        end if;

        -- Check 7-8: Average and Deviation and Nominal
        if (lv_checksList(7) = 1) then
          if (lv_scrutinyExcludeCheckDv = 0) then
            gv_checkResult := greatest(gv_checkResult, check7_8(lv_stops(x)));
          end if;
        end if;

        -- Check 9: Dry Cleaning
        if (lv_checksList(8) = 1) then
          if (lv_scrutinyExcludeCheckDv = 0) then
            gv_checkResult := greatest(gv_checkResult, check9(lv_stops(x)));
          end if;
        end if;

        -- Check 10: Heavy Soil
        if (lv_checksList(9) = 1) then
          if (lv_scrutinyExcludeCheckDv = 0) then
            gv_checkResult := greatest(gv_checkResult, check10(lv_stops(x)));
          end if;
        end if;

        -- Check 11: Clean Return
        if (lv_checksList(10) = 1) then
          if (lv_scrutinyExcludeCheckDv = 0) then
            gv_checkResult := greatest(gv_checkResult, check11(lv_stops(x)));
          end if;
        end if;

        -- Stop Completion
        if (lv_scrutinyExcludeCheckDv = 0) then
          for y in (select dv.deliveryvariations_id
                      from deliveryvariations dv, distributionpoint dp, department d, stop s, routecontrol rc
                     where dv.distributionpoint_id = dp.distributionpoint_id
                       and dp.department_id = d.department_id
                       and d.customer_id = s.customer_link_id
                       and (nvl(s.routestop_id, 0) = 0 or
                           (select nvl(rs.deliverypoint_id, 0)
                               from routestop rs
                              where rs.routestop_id = s.routestop_id) = 0 or
                           (select nvl(rs.deliverypoint_id, 0)
                               from routestop rs
                              where rs.routestop_id = s.routestop_id) = dp.deliverypoint_id)
                       and dv.deliverydate <= rc.drivingdate
                       and dv.processed = 'N'
                       and dv.scrutinystatus = 11
                       and s.routecontrol_id = rc.routecontrol_id
                       and s.stop_id = lv_stops(x)) loop
            update deliveryvariations dv set dv.scrutinystatus = 30 where dv.deliveryvariations_id = y.deliveryvariations_id;
          end loop;
        end if;

        -- if one of the checks failed, skip the stop update (already updated)
        if (gv_checkResult = 1) then
          continue;
        end if;

        for y in (select r.routenumber, rc.drivingdate, s.stopnumber, c.customernumber
                    from stop s, routecontrol rc, route r, customer c
                   where s.routecontrol_id = rc.routecontrol_id
                     and rc.route_id = r.route_id
                     and c.customer_id = s.customer_link_id
                     --and gv_checkResult = 0 -- if stop passed all checks this time
                     and s.stop_id = lv_stops(x)) loop
            update stop s set s.scrutinystatus = 30 where s.stop_id = lv_stops(x);
            gv_message := 'Stop is set to Approved.';
            gv_approvedstops := gv_approvedstops + 1;
          write_log_line(gv_message, y.routenumber, y.drivingdate, y.stopnumber, y.customernumber);
        end loop;
      exception when NO_DATA_FOUND then 
         IF (gv_abslogprocessjobpartid IS NOT NULL) THEN
         abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_ERROR, locator || ' ' || to_char(nvl(systemuserId, gv_systemuserid)));
         END IF;
        continue;
      end;
    end loop;
    end if;

    -- setting Route Control after processing
    if (lv_stops is not null and lv_stops.count > 0) then

      -- all checked routecontrols
      locator := ' all checked routecontrols fails'  ; 
      select count(distinct rc.routecontrol_id) into gv_totalroutecontrols from routecontrol rc, stop s where rc.routecontrol_id = s.routecontrol_id and s.stop_id in (select * from table(lv_stops));

      -- all approved route controls
      locator := '  all approved route controls fails'  ; 
      select distinct rc.routecontrol_id bulk collect into lv_rcIds
                from routecontrol rc, stop s1
               where not exists (select 1
                        from stop s
                       where s.routecontrol_id = rc.routecontrol_id
                         and s.scrutinystatus <> 10
                         and s.scrutinystatus <> 30
                         and s.status not in (1, 4))
                 and rc.routecontrol_id = s1.routecontrol_id
                 and s1.stop_id in (select * from table(lv_stops));

      gv_approvedroutecontrols := 0;

      if (lv_rcIds.FIRST is not null) then
        for x in lv_rcIds.FIRST..lv_rcIds.LAST loop
          locator := 'lv_rcIds.FIRST..lv_rcIds.LAST' ||  lv_rcIds(x) ; 
          update routecontrol rc set rc.scrutinystatus = 30, rc.timestamp = sysdate, rc.systemuser_id = nvl(systemuserId, gv_systemuserid)
                 where rc.routecontrol_id = lv_rcIds(x);
          gv_approvedroutecontrols := gv_approvedroutecontrols + 1;
        end loop;
      end if;

      -- all not approved route controls
      locator := ' all not approved route controls fails '; 
      select distinct rc.routecontrol_id bulk collect into lv_rcIds
                from routecontrol rc, stop s1
               where exists (select 1
                        from stop s
                       where s.routecontrol_id = rc.routecontrol_id
                         and (s.scrutinystatus = 20
                         or s.scrutinystatus = 21)
                         and s.status not in (1, 4))
                 and rc.routecontrol_id = s1.routecontrol_id
                 and s1.stop_id in (select * from table(lv_stops));

      gv_notapprovedroutecontrols := 0;

      if (lv_rcIds.FIRST is not null) then
        for x in lv_rcIds.FIRST..lv_rcIds.LAST loop
          locator := 'lv_rcIds.FIRST..lv_rcIds.LAST second ' ||  lv_rcIds(x) ; 
          update routecontrol rc set rc.scrutinystatus = 20, rc.timestamp = sysdate, rc.systemuser_id = nvl(systemuserId, gv_systemuserid)
                 where rc.routecontrol_id = lv_rcIds(x);
          gv_notapprovedroutecontrols := gv_notapprovedroutecontrols + 1;
        end loop;
      end if;
    end if;

    commit;
    -- report footer:
    write_log_footer;

  exception
    when others then
      IF (gv_abslogprocessjobpartid IS NOT NULL) THEN
         abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid, PRIORITY_ERROR, locator || ' - ' || sqlerrm, nvl(systemuserId, gv_systemuserid));
      END IF;
      raise;
  end scrutinyProcess;

  begin
  -- Initialization
  begin
    select sy.systemuser_id into gv_systemuserid from systemuser sy where sy.username = 'SYSADM';
  exception
    when no_data_found then
    begin
    select sy.systemuser_id into gv_systemuserid from systemuser sy where sy.username = 'ABSADM';
    exception
      when no_data_found then
        begin
          select min(sy.systemuser_id) into gv_systemuserid from systemuser sy;
        end;
    end;
  end;
exception
  when others then
    begin
      dbms_output.put_line('Error during abslogwrite initialization: ' || sqlerrm);
      raise;
    end;
end pack_scrutiny;
/
