--20015498 

ALTER TABLE PLANTPROCESS ADD (APPSERVERCLUSTER_ID NUMBER NULL);

create or replace procedure createorupdateappservercluster is
  existingappservercluster appservercluster.appservercluster_id%type;
  newAppServerCluster      appservercluster.appservercluster_id%type;
  maxCluster               number;
  cursor info is
    select * from plantprocess pp where pp.appservercluster_id is null;
begin
  for dataInfo in info loop
    begin
      -- search appservercluster that contains plantprocess.applicationserver_id
      select appsc.appservercluster_id
        into existingAppServerCluster
        from appservercluster appsc, appserverclustercontent ascc
       where appsc.appservercluster_id = ascc.appservercluster_id
         and ascc.applicationserver_id = dataInfo.applicationserver_id;
    exception
      when no_data_found then
        null;
    end;
    if (existingAppServerCluster is not null) then
      -- update pp.appservercluster_id with the existing one
      begin
        update plantprocess pp
           set pp.appservercluster_id = existingAppServerCluster
         where pp.plantprocess_id = dataInfo.plantprocess_id;
      exception
        when others then
          null;
      end;
    else
      newAppServerCluster := SEQ_APPSERVERCLUSTER.Nextval;
      maxCluster          := 0;
      begin
        select max(to_number(substr(appsc.code, 9))) + 1
          into maxCluster
          from appservercluster appsc
         where appsc.code like 'CLUSTER_%';
      exception
        when others then
          null;
      end;
      if (maxCluster is null) then
        maxCluster := 1;
      end if;
      --create appservercluster
      begin
        insert into appservercluster
          (appservercluster_id,
           code,
           description,
           MASTERAPPSERVER_ID,
           SYSTEMUSER_ID,
           timestamp)
        values
          (newAppServerCluster,
           'CLUSTER_' || maxCluster,
           'CLUSTER_' || maxCluster,
           NULL,
           0,
           SYSDATE);
      end;
      --create appserverclustercontent
      begin
        insert into appserverclusterCONTENT
          (appservercluster_id,
           APPLICATIONSERVER_ID,
           ACTUALSTATUS,
           SYSTEMUSER_ID,
           timestamp)
        values
          (newAppServerCluster,
           dataInfo.applicationserver_id,
           0,
           0,
           SYSDATE);
      end;
      --update pp.appservercluster_id with the new one just created
      begin
        update plantprocess pp
           set pp.appservercluster_id = newAppServerCluster
         where pp.plantprocess_id = dataInfo.plantprocess_id;
      exception
        when others then
          null;
      end;
    end if;
  end loop;
end createOrUpdateAppServerCluster;


/

execute createOrUpdateAppServerCluster;

drop procedure createOrUpdateAppServerCluster;


ALTER TABLE PLANTPROCESS MODIFY (APPSERVERCLUSTER_ID NOT NULL);

ALTER TABLE PLANTPROCESS DROP COLUMN APPLICATIONSERVER_ID;


ALTER TABLE PLANTPROCESS ADD (PROCESSEDBYAPPSRV_ID NUMBER NULL);


CREATE INDEX XIF7PLANTPROCESS ON PLANTPROCESS
(PROCESSEDBYAPPSRV_ID   ASC);

CREATE INDEX XIF8PLANTPROCESS ON PLANTPROCESS
(APPSERVERCLUSTER_ID   ASC);

ALTER TABLE PLANTPROCESS
ADD CONSTRAINT PLANTPROCESS_APPSRV FOREIGN KEY (PROCESSEDBYAPPSRV_ID) REFERENCES APPLICATIONSERVER (APPLICATIONSERVER_ID) ON DELETE SET NULL;

ALTER TABLE PLANTPROCESS
ADD CONSTRAINT PLANTPROCESS_APPCLUSTER FOREIGN KEY (APPSERVERCLUSTER_ID) REFERENCES APPSERVERCLUSTER (APPSERVERCLUSTER_ID);




