create or replace procedure autocompletecleanroomcharges(fp_plantprocessid         in plantprocess.plantprocess_id%type,
                                                          fp_abslogprocessjobpartid in abslogprocessjobpart.abslogprocessjobpart_id%type default null,
                                                          fp_systemuserid           in systemuser.systemuser_id%type default 0) is
  lv_plantid        plant.plant_id%type;
  lv_statusid       status.status_id%type;
  gv_user_exception exception;
  gv_defaultuserid systemuser.systemuser_id%type := 0;
  gv_message       abslog.message%type;

  ----- Internal procedures/functions
  procedure write_log(fp_message in varchar2, fp_priority in number default 1) is
  begin
    /* Logging: write message in ABSLOG with these priorities:
    1 = Message
    2 = Warning
    3 = Error */
    abslogwrite.abslog_write(5, 'AUTOCOMPLETECLEANROOMCHARGES', null, fp_priority, fp_message, gv_defaultuserid);
    if fp_abslogprocessjobpartid is null then
      begin
        begin
          abslogwrite.abslog_write(5, 'AUTOCOMPLETECLEANROOMCHARGES', null, fp_priority, fp_message, gv_defaultuserid);
        end;
      end;
    else
      abslogwrite.abslogprocessjobline_write(fp_abslogprocessjobpartid, fp_priority, fp_message, gv_defaultuserid);
    end if;
  end write_log;

begin
  begin
  select pp.plant_id
    into lv_plantid
    from plantprocess pp
   where pp.plantprocess_id = fp_plantprocessid;
  exception
    when no_data_found then
        gv_message       := 'Plant process not found.';
        write_log(gv_message, 3);
  end;

 begin
  select s.status_id
    into lv_statusid
    from status s
   where s.code = 10
     and s.statustype = 'CA';
  exception
    when no_data_found then
        gv_message       := 'Status of type CA not found.';
        write_log(gv_message, 3);
  end;

  for x in (select sr.sterilizationrun_id, sr.chargenumber,
                   (select st.code from sterilizer st where st.sterilizer_id = sr.sterilizer_id) sterilizercode
              from sterilizationrun sr, businessunit bu
             where sr.businessunit_id = bu.businessunit_id
             and bu.plant_id= lv_plantid
              and sr.status = 4
               and sr.chargetype = 1 --cleanroom charges
               and ((sr.cooldownendtime is null)
                or (sr.cooldownendtime < sysdate))) loop
  begin
      update cart c
         set c.status_id           = lv_statusid,
             c.sterilizationrun_id = null,
             c.systemuser_id       = fp_systemuserid,
             c.timestamp           = sysdate
       where c.sterilizationrun_id = x.sterilizationrun_id;

      update sterilizationrun sr
         set sr.status        = 6,
             sr.systemuser_id = fp_systemuserid,
             sr.timestamp     = sysdate
       where sr.sterilizationrun_id = x.sterilizationrun_id;

      gv_message       := 'Charge ' || x.chargenumber || ' Sterilizer ' || x.sterilizercode || ' completed.';
      write_log(gv_message, 1);
  exception  --exception handling
  when others then
    begin
      rollback;
      gv_message := 'Error completing charge ' || x.chargenumber || ' Sterilizer ' || x.sterilizercode || '. Error :' || sqlerrm;
      write_log(gv_message, 3);
    end;
    end;
  end loop;
  commit;
end autocompletecleanroomcharges;
/
