--order 502421

create or replace procedure fillMaxWashes_Product is

  tID       integer;
  dataSys   timestamp;
  sysUserID Systemuser.Systemuser_Id%type;
  --varMaxWahes Product.Maxwashes%type;
  flagId Automaticflag.Flag_Id%type;
  --pID product.product_id%type;
  cursor products is
    select p.product_id, p.maxwashes from product p where p.maxwashes > 0 and flagId is not null;
begin

  select sysdate into dataSys from dual;
  select min(s.systemuser_id) into sysUserID from Systemuser s;

  select f.flag_id
    into flagId
    from Automaticflag f
   where f.event = 'Maximum number of washes reached';

  FOR prodrec IN products LOOP
    begin
      select t.treatmentcheckscheme_id
        into tID
        from treatmentcheckscheme t
       where t.code = 'MAX' || prodrec.maxwashes;
    exception
      when no_data_found then
        select seq_treatmentcheckscheme.nextval into tID from dual;
      
        insert into treatmentcheckscheme
          (TREATMENTCHECKSCHEME_ID,
           CODE,
           DESCRIPTION,
           ACTIVE,
           SYSTEMUSER_ID,
           TIMESTAMP)
        values
          (tID,
           'MAX' || prodrec.maxwashes,
           'Max Washes' || prodrec.maxwashes,
           'Y',
           sysUserID,
           dataSys);
      
        insert into treatmentcheckschemedetail
        values
          (seq_treatmentcheckschemedetail.nextval,
           tID,
           0,
           prodrec.maxwashes,
           0,
           0,
           0,
           100,
           flagId,
           null,
           sysUserID,
           dataSys);
    end;
    update product p
       set p.treatmentcheckscheme_id = tID
     where p.product_id = prodrec.product_id;
  
  END LOOP;

end fillMaxWashes_Product;
/

execute fillMaxWashes_Product;

drop procedure fillMaxWashes_Product;