--order 502045

CREATE SEQUENCE SEQ_ABSLOGPROCESSJOBPART
START WITH 1
INCREMENT BY 1;

CREATE TABLE ABSLOGPROCESSJOBPART (
        ABSLOGPROCESSJOBPART_ID NUMBER NOT NULL,
        ABSLOGPROCESSJOB_ID NUMBER NOT NULL,
	PROCESSNAME VARCHAR2(30) NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

CREATE INDEX XIF1ABSLOGPROCESSJOBPART ON ABSLOGPROCESSJOBPART
(
       ABSLOGPROCESSJOB_ID          ASC
);

ALTER TABLE ABSLOGPROCESSJOBPART
       ADD  ( CONSTRAINT XPKABSLOGPROCESSJOBPART PRIMARY KEY (ABSLOGPROCESSJOBPART_ID) ) ;

ALTER TABLE ABSLOGPROCESSJOBPART
       ADD  ( CONSTRAINT ABSLOGPROCESSJOBPART_ABSLOGPRO
              FOREIGN KEY (ABSLOGPROCESSJOB_ID)
                             REFERENCES ABSLOGPROCESSJOB
                             ON DELETE CASCADE ) ;

insert into abslogprocessjobpart apart
  select seq_abslogprocessjobpart.nextval,
         ajob.abslogprocessjob_id,
         ajob.processname,
         ajob.systemuser_id,
         ajob.timestamp
    from abslogprocessjob ajob;

ALTER TABLE ABSLOGPROCESSJOBLINE ADD ( ABSLOGPROCESSJOBPART_ID NUMBER NULL );

update abslogprocessjobline aline
   set aline.abslogprocessjobpart_id =  (select apart.abslogprocessjobpart_id
                                          from abslogprocessjobpart apart
                                         where apart.abslogprocessjob_id =
                                               aline.abslogprocessjob_id);
                                               
ALTER TABLE ABSLOGPROCESSJOBLINE MODIFY(ABSLOGPROCESSJOBPART_ID NOT NULL);

ALTER TABLE ABSLOGPROCESSJOBLINE
       ADD  ( CONSTRAINT ABSLOGPROCESSJOBLINE_ABSLOGPAR
              FOREIGN KEY (ABSLOGPROCESSJOBPART_ID)
                             REFERENCES ABSLOGPROCESSJOBPART
				ON DELETE CASCADE) ;

ALTER TABLE ABSLOGPROCESSJOB DROP COLUMN PROCESSNAME;

--drop FK to ABSLOGPROCESSJOB table
create or replace procedure deleteFK is
  constraint_name user_constraints.constraint_name%type;
begin
  select constraint_name into constraint_name from user_constraints
    where table_name = upper('ABSLOGPROCESSJOBLINE')
    and r_constraint_name = 'XPKABSLOGPROCESSJOB';
  
    execute immediate
      ('alter table ABSLOGPROCESSJOBLINE drop constraint ' || constraint_name) ;
exception
      when NO_DATA_FOUND then 
           dbms_output.put_line('Foreign key to ABSLOGPROCESSJOB table not found');
end deleteFK;
/

execute deleteFK;


drop procedure deleteFK;


drop index XIF1ABSLOGPROCESSJOBLINE;

--drop column ABSLOGPROCESSJOB_ID
alter table ABSLOGPROCESSJOBLINE drop column ABSLOGPROCESSJOB_ID;

create index XIF2ABSLOGPROCESSJOBLINE on ABSLOGPROCESSJOBLINE
(
  ABSLOGPROCESSJOBPART_ID  ASC,
  PRIORITY    ASC
);
