/* *****************************************************************************
********************************************************************************

SCRIPT NAME:  DDL_For_CTOM_Sequences_For_Oracle.sql 
PURPOSE:      Create or recreate sequences for CTOM tables. 
AUTHOR:		  ScenPro, Inc.
DATE CREATED:	  March, 2006
VERSION: 	  CTOM v0.53Plus
NOTE:		  This script was created in Toad and is best viewed through that tool.

********************************************************************************
***************************************************************************** */

SET ECHO ON

SPOOL DDL_For_CTOM_Sequences.lst


DROP SEQUENCE ACTIVITY_RELATIONSHIP_SEQ;
DROP SEQUENCE ACTIVITY_SEQ;
DROP SEQUENCE ADVERSE_EVENT_THERAPY_SEQ;
DROP SEQUENCE AGENT_OCCURRENCE_SEQ;
DROP SEQUENCE AGENT_SEQ;
DROP SEQUENCE ASSESSMENT_RELATIONSHIP_SEQ;
DROP SEQUENCE ASSESSMENT_SEQ;
DROP SEQUENCE CANCER_STAGE_SEQ;
DROP SEQUENCE CONCEPT_DESCRIPTOR_SEQ;
DROP SEQUENCE ELIGIBILITY_CHECKLIST_SEQ;
DROP SEQUENCE ELIGIBILITY_CHECKLST_CRITR_SEQ;
DROP SEQUENCE ELIGIBILITY_CRITERIA_SEQ;
DROP SEQUENCE FEMALE_REPRDCTVE_CHRCTRSTC_SEQ;
DROP SEQUENCE HEALTHCARE_SITE_INVESTIGTR_SEQ;
DROP SEQUENCE HEALTHCARE_SITE_PRTCPNT_SEQ;
DROP SEQUENCE HEALTHCARE_SIT_PRTCPNT_ROL_SEQ;
DROP SEQUENCE HISTOPATHOLOGY_GRADE_SEQ;
DROP SEQUENCE IDENTIFIER_SEQ;
DROP SEQUENCE LAB_VIEWER_STATUS_SEQ;
DROP SEQUENCE METASTASIS_SITE_SEQ;
DROP SEQUENCE NEOPLASM_SEQ;
DROP SEQUENCE OBSERVATION_ASSESSMENT_SEQ;
DROP SEQUENCE OBSERVATION_RELATIONSHIP_SEQ;
DROP SEQUENCE OBSERVATION_SEQ;
DROP SEQUENCE ORGANIZATION_SEQ;
DROP SEQUENCE PARTICIPANT_ELIGBLTY_ANSWR_SEQ;
DROP SEQUENCE PERSON_OCCUPATION_SEQ;
DROP SEQUENCE PERSON_SEQ;
DROP SEQUENCE PROTOCOL_SEQ;
DROP SEQUENCE PROTOCOL_PARTICPNT_ELGBLTY_SEQ;
DROP SEQUENCE PROTOCOL_STATUS_SEQ;
DROP SEQUENCE SPECIMEN_SEQ;
DROP SEQUENCE STUDY_AGENT_SEQ;
DROP SEQUENCE STUDY_INVESTIGATOR_SEQ;
DROP SEQUENCE STUDY_PARTICIPANT_ASSGNMNT_SEQ;
DROP SEQUENCE STUDY_SITE_SEQ;
DROP SEQUENCE STUDY_TIME_POINT_SEQ;


CREATE SEQUENCE ACTIVITY_RELATIONSHIP_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE ACTIVITY_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE ADVERSE_EVENT_THERAPY_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE AGENT_OCCURRENCE_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE AGENT_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE ASSESSMENT_RELATIONSHIP_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE ASSESSMENT_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE CANCER_STAGE_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE CONCEPT_DESCRIPTOR_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE ELIGIBILITY_CHECKLIST_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE ELIGIBILITY_CHECKLST_CRITR_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE ELIGIBILITY_CRITERIA_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE FEMALE_REPRDCTVE_CHRCTRSTC_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE HEALTHCARE_SITE_INVESTIGTR_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE HEALTHCARE_SITE_PRTCPNT_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE HEALTHCARE_SIT_PRTCPNT_ROL_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE HISTOPATHOLOGY_GRADE_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE IDENTIFIER_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE LAB_VIEWER_STATUS_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE METASTASIS_SITE_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE NEOPLASM_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE OBSERVATION_ASSESSMENT_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE OBSERVATION_RELATIONSHIP_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE OBSERVATION_SEQ START WITH 1 INCREMENT BY 1;
-- organization_seq is used for healthcare sites, performing and central laboratories so that the organization_view can eventually have a single unique series of numbers for the id column
CREATE SEQUENCE ORGANIZATION_SEQ START WITH 1 INCREMENT BY 1;
CREATE SYNONYM  HEALTHCARE_SITE_SEQ FOR ORGANIZATION_SEQ;
CREATE SYNONYM  CENTRAL_LABORATORY_SEQ FOR ORGANIZATION_SEQ;
CREATE SYNONYM  PERFORMING_LABORATORY_SEQ FOR ORGANIZATION_SEQ;
-- 
CREATE SEQUENCE PARTICIPANT_ELIGBLTY_ANSWR_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE PERSON_OCCUPATION_SEQ START WITH 1 INCREMENT BY 1;
-- person_seq is used for both investigators and participants so that the person_view can have a single unique series of numbers for the id column 
CREATE SEQUENCE PERSON_SEQ START WITH 1 INCREMENT BY 1;
CREATE SYNONYM  INVESTIGATOR_SEQ FOR PERSON_SEQ;
CREATE SYNONYM  PARTICIPANT_SEQ FOR PERSON_SEQ;
-- 
CREATE SEQUENCE PROTOCOL_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE PROTOCOL_PARTICPNT_ELGBLTY_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE PROTOCOL_STATUS_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SPECIMEN_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE STUDY_AGENT_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE STUDY_INVESTIGATOR_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE STUDY_PARTICIPANT_ASSGNMNT_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE STUDY_SITE_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE STUDY_TIME_POINT_SEQ START WITH 1 INCREMENT BY 1;


SPOOL OFF

SET ECHO OFF
