--order 502421

create or replace procedure fillMaxWashes_PLPC is

  tID             integer;
  tID2            integer;
  tID3            integer;
  tID4            integer;
  dataSys         timestamp;
  sysUserID       Systemuser.Systemuser_Id%type;
  maxWashesFlagId Automaticflag.Flag_Id%type;
  treatmentFlagId Automaticflag.Flag_Id%type;

  cursor c_plpc is
    select plpc.productlistpercustomer_id, plpc.maxwashes
      from productlistpercustomer plpc
     where plpc.maxwashes > 0
       and plpc.numberwashesspecialtreatment = 0
       and maxWashesFlagId is not null;

  cursor c_plpc2 is
    select plpc.productlistpercustomer_id,
           plpc.numberwashesspecialtreatment
      from productlistpercustomer plpc, product p
     where plpc.maxwashes = 0
       and plpc.numberwashesspecialtreatment > 0
       and treatmentFlagId is not null
       and plpc.product_id = p.product_id
       and p.maxwashes = 0;

  cursor c_plpc3 is
    select plpc.productlistpercustomer_id,
           plpc.numberwashesspecialtreatment,
           p.maxwashes
      from productlistpercustomer plpc, product p
     where plpc.maxwashes = 0
       and plpc.numberwashesspecialtreatment > 0
       and treatmentFlagId is not null
       and maxWashesFlagId is not null
       and plpc.product_id = p.product_id
       and p.maxwashes > 0;

  cursor c_plpc4 is
    select plpc.productlistpercustomer_id,
           plpc.numberwashesspecialtreatment,
           plpc.maxwashes
      from productlistpercustomer plpc
     where plpc.maxwashes > 0
       and plpc.numberwashesspecialtreatment > 0
       and treatmentFlagId is not null
       and maxWashesFlagId is not null;

begin

  select sysdate into dataSys from dual;

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

  begin
  execute immediate ('insert into automaticflag (automaticflag_id, event, systemuser_id, timestamp)
	              values (6, ''Every xx washes reached'', Getminsystemuser(), sysdate)');
  exception when others then null;
  end;

  begin
  execute immediate ('insert into automaticflag (automaticflag_id, event, systemuser_id, timestamp)
	              values (1, ''Maximum number of washes reached'', Getminsystemuser(), sysdate)');
  exception when others then null;
  end;

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

  select f.flag_id
    into treatmentFlagId
    from Automaticflag f
   where f.event = 'Every xx washes reached';

  FOR prodrec IN c_plpc 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
          (TREATMENTCHECKSCHEMEDETAIL_ID,
           TREATMENTCHECKSCHEME_ID,
           SCHEMETYPE,
           WASHES,
           FROMWASHES,
           TOWASHES,
           REPEATWASHES,
           CHECKPERCENTAGE,
           FLAG_ID,
           TREATMENTTYPE_ID,
           SYSTEMUSER_ID,
           TIMESTAMP)
        values
          (seq_treatmentcheckschemedetail.nextval,
           tID,
           0,
           prodrec.maxwashes,
           0,
           0,
           0,
           100,
           maxWashesFlagId,
           null,
           sysUserID,
           dataSys);
    end;
    update productlistpercustomer plpc
       set plpc.treatmentcheckscheme_id = tID
     where plpc.productlistpercustomer_id =
           prodrec.productlistpercustomer_id;
  
  END LOOP;

  FOR prodrec2 IN c_plpc2 LOOP
    begin
      select t.treatmentcheckscheme_id
        into tID2
        from treatmentcheckscheme t
       where t.code = 'ST' || prodrec2.numberwashesspecialtreatment;
    exception
      when no_data_found then
        select seq_treatmentcheckscheme.nextval into tID2 from dual;
      
        insert into treatmentcheckscheme
          (TREATMENTCHECKSCHEME_ID,
           CODE,
           DESCRIPTION,
           ACTIVE,
           SYSTEMUSER_ID,
           TIMESTAMP)
        values
          (tID2,
           'ST' || prodrec2.numberwashesspecialtreatment,
           'Special Treatment ' ||
           prodrec2.numberwashesspecialtreatment || ' washes',
           'Y',
           sysUserID,
           dataSys);
      
        insert into treatmentcheckschemedetail
          (TREATMENTCHECKSCHEMEDETAIL_ID,
           TREATMENTCHECKSCHEME_ID,
           SCHEMETYPE,
           WASHES,
           FROMWASHES,
           TOWASHES,
           REPEATWASHES,
           CHECKPERCENTAGE,
           FLAG_ID,
           TREATMENTTYPE_ID,
           SYSTEMUSER_ID,
           TIMESTAMP)
        values
          (seq_treatmentcheckschemedetail.nextval,
           tID2,
           1,
           0,
           1,
           9999,
           prodrec2.numberwashesspecialtreatment,
           100,
           treatmentFlagId,
           null,
           sysUserID,
           dataSys);
    end;
    update productlistpercustomer plpc
       set plpc.treatmentcheckscheme_id = tID2
     where plpc.productlistpercustomer_id =
           prodrec2.productlistpercustomer_id;
  
  END LOOP;

  FOR prodrec3 IN c_plpc3 LOOP
    begin
      select t.treatmentcheckscheme_id
        into tID3
        from treatmentcheckscheme t
       where t.code = 'STM' || prodrec3.numberwashesspecialtreatment;
    exception
      when no_data_found then
        select seq_treatmentcheckscheme.nextval into tID3 from dual;
      
        insert into treatmentcheckscheme
          (TREATMENTCHECKSCHEME_ID,
           CODE,
           DESCRIPTION,
           ACTIVE,
           SYSTEMUSER_ID,
           TIMESTAMP)
        values
          (tID3,
           'STM' || prodrec3.numberwashesspecialtreatment,
           'Special Treatment ' ||
           prodrec3.numberwashesspecialtreatment || ' washes',
           'Y',
           sysUserID,
           dataSys);
      
        insert into treatmentcheckschemedetail
          (TREATMENTCHECKSCHEMEDETAIL_ID,
           TREATMENTCHECKSCHEME_ID,
           SCHEMETYPE,
           WASHES,
           FROMWASHES,
           TOWASHES,
           REPEATWASHES,
           CHECKPERCENTAGE,
           FLAG_ID,
           TREATMENTTYPE_ID,
           SYSTEMUSER_ID,
           TIMESTAMP)
        values
          (seq_treatmentcheckschemedetail.nextval,
           tID3,
           1,
           0,
           1,
           prodrec3.maxwashes,
           prodrec3.numberwashesspecialtreatment,
           100,
           treatmentFlagId,
           null,
           sysUserID,
           dataSys);
      
        insert into treatmentcheckschemedetail
          (TREATMENTCHECKSCHEMEDETAIL_ID,
           TREATMENTCHECKSCHEME_ID,
           SCHEMETYPE,
           WASHES,
           FROMWASHES,
           TOWASHES,
           REPEATWASHES,
           CHECKPERCENTAGE,
           FLAG_ID,
           TREATMENTTYPE_ID,
           SYSTEMUSER_ID,
           TIMESTAMP)
        values
          (seq_treatmentcheckschemedetail.nextval,
           tID3,
           0,
           prodrec3.maxwashes,
           0,
           0,
           0,
           100,
           maxWashesFlagId,
           null,
           sysUserID,
           dataSys);
    end;
    update productlistpercustomer plpc
       set plpc.treatmentcheckscheme_id = tID3
     where plpc.productlistpercustomer_id =
           prodrec3.productlistpercustomer_id;
  
  END LOOP;

  FOR prodrec4 IN c_plpc4 LOOP
    begin
      select t.treatmentcheckscheme_id
        into tID4
        from treatmentcheckscheme t
       where t.code = 'STM' || prodrec4.numberwashesspecialtreatment;
    exception
      when no_data_found then
        select seq_treatmentcheckscheme.nextval into tID4 from dual;
      
        insert into treatmentcheckscheme
          (TREATMENTCHECKSCHEME_ID,
           CODE,
           DESCRIPTION,
           ACTIVE,
           SYSTEMUSER_ID,
           TIMESTAMP)
        values
          (tID4,
           'STM' || prodrec4.numberwashesspecialtreatment,
           'Special Treatment ' ||
           prodrec4.numberwashesspecialtreatment || ' washes',
           'Y',
           sysUserID,
           dataSys);
      
        insert into treatmentcheckschemedetail
          (TREATMENTCHECKSCHEMEDETAIL_ID,
           TREATMENTCHECKSCHEME_ID,
           SCHEMETYPE,
           WASHES,
           FROMWASHES,
           TOWASHES,
           REPEATWASHES,
           CHECKPERCENTAGE,
           FLAG_ID,
           TREATMENTTYPE_ID,
           SYSTEMUSER_ID,
           TIMESTAMP)
        values
          (seq_treatmentcheckschemedetail.nextval,
           tID4,
           1,
           0,
           1,
           prodrec4.maxwashes,
           prodrec4.numberwashesspecialtreatment,
           100,
           treatmentFlagId,
           null,
           sysUserID,
           dataSys);
      
        insert into treatmentcheckschemedetail
          (TREATMENTCHECKSCHEMEDETAIL_ID,
           TREATMENTCHECKSCHEME_ID,
           SCHEMETYPE,
           WASHES,
           FROMWASHES,
           TOWASHES,
           REPEATWASHES,
           CHECKPERCENTAGE,
           FLAG_ID,
           TREATMENTTYPE_ID,
           SYSTEMUSER_ID,
           TIMESTAMP)
        values
          (seq_treatmentcheckschemedetail.nextval,
           tID4,
           0,
           prodrec4.maxwashes,
           0,
           0,
           0,
           100,
           maxWashesFlagId,
           null,
           sysUserID,
           dataSys);
    end;
    update productlistpercustomer plpc
       set plpc.treatmentcheckscheme_id = tID4
     where plpc.productlistpercustomer_id =
           prodrec4.productlistpercustomer_id;
  
  END LOOP;

end fillMaxWashes_PLPC;
/

execute fillMaxWashes_PLPC;

drop procedure fillMaxWashes_PLPC;