-- ABS-56606 (Removal of Java based SQ pieces in circ)

delete from abslogprocessjob a
 where a.plantprocess_id in
       (select pp.plantprocess_id
          from plantprocess pp
         where pp.process_id = 89
           and exists
         (select 1
                  from plantprocess pp2
                 where pp2.plant_id = pp.plant_id
                   and pp2.process_id = 1
                   and pp2.active = pp.active
                   and pp2.appservercluster_id = pp.appservercluster_id));
commit;

delete from failoverprocess b
 where b.plantprocess_id in
       (select pp.plantprocess_id
          from plantprocess pp
         where pp.process_id = 89
           and exists
         (select 1
                  from plantprocess pp2
                 where pp2.plant_id = pp.plant_id
                   and pp2.process_id = 1
                   and pp2.active = pp.active
                   and pp2.appservercluster_id = pp.appservercluster_id));
commit;

delete from plantprocesshistory c
 where c.plantprocess_id in
       (select pp.plantprocess_id
          from plantprocess pp
         where pp.process_id = 89
           and exists
         (select 1
                  from plantprocess pp2
                 where pp2.plant_id = pp.plant_id
                   and pp2.process_id = 1
                   and pp2.active = pp.active
                   and pp2.appservercluster_id = pp.appservercluster_id));
commit;

delete from plantprocess pp
 where pp.process_id = 89
   and exists
 (select 1
          from plantprocess pp2
         where pp2.plant_id = pp.plant_id
           and pp2.process_id = 1
           and pp2.active = pp.active
           and pp2.appservercluster_id = pp.appservercluster_id);
commit;

update plantprocess pp set pp.process_id = 1 where pp.process_id = 89;
commit;

delete from process p where p.process_id = 89;
commit;
