 DROP INDEX INDX_CATISSUE_SPECIMEN_QTY;
 DROP INDEX INDX_CATISSUE_SPECIMEN_AVQTY;
 
-DELETE FROM  CATISSUE_EXTERNAL_IDENTIFIER WHERE SPECIMEN_ID IN(SELECT identifier FROM CATISSUE_SPECIMEN WHERE IS_COLL_PROT_REQ = 1);
-DELETE FROM CATISSUE_SPECIMEN WHERE IS_COLL_PROT_REQ = 1;
-
+DELETE FROM CATISSUE_EXTERNAL_IDENTIFIER WHERE SPECIMEN_ID IN(SELECT identifier FROM CATISSUE_SPECIMEN WHERE IS_COLL_PROT_REQ = 1);
+
+UPDATE CATISSUE_SPECIMEN set ACTIVITY_STATUS = 'Disabled' where IDENTIFIER IN
+(SELECT SPECIMEN_ID FROM CATISSUE_EXISTING_SP_ORD_ITEM WHERE SPECIMEN_ID IN 
+(SELECT IDENTIFIER FROM CATISSUE_SPECIMEN WHERE IS_COLL_PROT_REQ = 1));
+
+UPDATE CATISSUE_SPECIMEN set ACTIVITY_STATUS = 'Disabled' where IDENTIFIER IN
+(Select SPECIMEN_ID from CATISSUE_DERIEVED_SP_ORD_ITEM  WHERE SPECIMEN_ID IN 
+(SELECT IDENTIFIER FROM CATISSUE_SPECIMEN WHERE IS_COLL_PROT_REQ = 1));
+
+DELETE FROM CATISSUE_SPECIMEN WHERE IS_COLL_PROT_REQ = 1 and ACTIVITY_STATUS = 'ACTIVE';
 
 ALTER TABLE CATISSUE_SPECIMEN ADD (REQ_SPECIMEN_ID number(19,0));
 

INSERT INTO CATISSUE_SEARCH_DISPLAY_DATA (RELATIONSHIP_ID, COL_ID, DISPLAY_NAME, DEFAULT_VIEW_ATTRIBUTE,ATTRIBUTE_ORDER)
values(143,333,'Parent Container Id',1,3);

drop sequence CATISSUE_RACE_SEQ;
create sequence CATISSUE_RACE_SEQ start with 1 increment by 1;
alter table CATISSUE_RACE add  IDENTIFIER number(19,0);
update  CATISSUE_RACE set IDENTIFIER=CATISSUE_RACE_SEQ.nextval;

ALTER TABLE CATISSUE_CONTAINER DROP COLUMN PARENT_CONTAINER_ID;
ALTER TABLE CATISSUE_SPECIMEN_ARRAY DROP COLUMN STORAGE_CONTAINER_ID;

alter table CATISSUE_SPECIMEN_COLL_GROUP drop constraint FK_CATISSUE_COLL_PROT_EVENT;

UPDATE CATISSUE_SPECIMEN_COLL_GROUP scg SET Collection_Protocol_Event_Id = 
