CREATE BIGFILE TABLESPACE CM_XT039_P2017JAN DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE BIGFILE TABLESPACE CM_XT039_P2017FEB DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE BIGFILE TABLESPACE CM_XT039_P2017MAR DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE BIGFILE TABLESPACE CM_XT039_P2017APR DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE BIGFILE TABLESPACE CM_XT039_P2017MAY DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE BIGFILE TABLESPACE CM_XT039_P2017JUN DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE BIGFILE TABLESPACE CM_XT039_P2017JUL DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE BIGFILE TABLESPACE CM_XT039_P2017AUG DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE BIGFILE TABLESPACE CM_XT039_P2017SEP DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE BIGFILE TABLESPACE CM_XT039_P2017OCT DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE BIGFILE TABLESPACE CM_XT039_P2017NOV DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE BIGFILE TABLESPACE CM_XT039_P2017DEC DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE BIGFILE TABLESPACE CM_XT039_PMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

 

CREATE TABLE CI_TD_ENTRY (

TD_ENTRY_ID CHAR(14) NOT NULL ENABLE,

BATCH_CD CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

BATCH_NBR NUMBER(10,0) DEFAULT 0 NOT NULL ENABLE,

MESSAGE_CAT_NBR NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

MESSAGE_NBR NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

ASSIGNED_TO CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

TD_TYPE_CD CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

ROLE_ID CHAR(10) DEFAULT ' ' NOT NULL ENABLE,

ENTRY_STATUS_FLG CHAR(2) DEFAULT ' ' NOT NULL ENABLE,

VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

CRE_DTTM DATE,

ASSIGNED_DTTM DATE,

COMPLETE_DTTM DATE,

COMPLETE_USER_ID CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

COMMENTS VARCHAR2(254) DEFAULT ' ' NOT NULL ENABLE,

ASSIGNED_USER_ID CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

TD_PRIORITY_FLG CHAR(4) DEFAULT ' ' NOT NULL ENABLE,

ILM_DT DATE,

ILM_ARCH_SW CHAR(1)

)

ENABLE ROW MOVEMENT

PARTITION BY RANGE (ILM_DT)

SUBPARTITION BY RANGE (TD_ENTRY_ID) SUBPARTITION TEMPLATE

(

SUBPARTITION S01 VALUES LESS THAN ( '12499999999999' ),

SUBPARTITION S02 VALUES LESS THAN ( '24999999999999' ),

SUBPARTITION S03 VALUES LESS THAN ( '37499999999999' ),

SUBPARTITION S04 VALUES LESS THAN ( '49999999999999' ),

SUBPARTITION S05 VALUES LESS THAN ( '62499999999999' ),

SUBPARTITION S06 VALUES LESS THAN ( '74999999999999' ),

SUBPARTITION S07 VALUES LESS THAN ( '87499999999999' ),

SUBPARTITION SMAX VALUES LESS THAN ( MAXVALUE )

)

(

PARTITION "P2017JAN" VALUES LESS THAN (TO_DATE('2017-02-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace CM_XT039_P2017JAN,

PARTITION "P2017FEB" VALUES LESS THAN (TO_DATE('2017-03-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace CM_XT039_P2017FEB,

PARTITION "P2017MAR" VALUES LESS THAN (TO_DATE('2017-04-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace CM_XT039_P2017MAR,

PARTITION "P2017APR" VALUES LESS THAN (TO_DATE('2017-05-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace CM_XT039_P2017APR,

PARTITION "P2017MAY" VALUES LESS THAN (TO_DATE('2017-06-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace CM_XT039_P2017MAY,

PARTITION "P2017JUN" VALUES LESS THAN (TO_DATE('2017-07-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace CM_XT039_P2017JUN,

PARTITION "P2017JUL" VALUES LESS THAN (TO_DATE('2017-08-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace CM_XT039_P2017JUL,

PARTITION "P2017AUG" VALUES LESS THAN (TO_DATE('2017-09-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace CM_XT039_P2017AUG,

PARTITION "P2017SEP" VALUES LESS THAN (TO_DATE('2017-10-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace CM_XT039_P2017SEP,

PARTITION "P2017OCT" VALUES LESS THAN (TO_DATE('2017-11-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace CM_XT039_P2017OCT,

PARTITION "P2017NOV" VALUES LESS THAN (TO_DATE('2017-12-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace CM_XT039_P2017NOV,

PARTITION "P2017DEC" VALUES LESS THAN (TO_DATE('2018-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace CM_XT039_P2017DEC,

PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)

tablespace CM_XT039_PMAX

);

CREATE BIGFILE TABLESPACE CM_XT039_IND DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

 

CREATE UNIQUE INDEX XT039P0 ON CI_TD_ENTRY ( TD_ENTRY_ID ) TABLESPACE CM_XT039_IND

GLOBAL PARTITION BY RANGE (TD_ENTRY_ID)

(

PARTITION P1 VALUES LESS THAN ( '12499999999999' ),

PARTITION P2 VALUES LESS THAN ( '24999999999999' ),

PARTITION P3 VALUES LESS THAN ( '37499999999999' ),

PARTITION P4 VALUES LESS THAN ( '49999999999999' ),

PARTITION P5 VALUES LESS THAN ( '62499999999999' ),

PARTITION P6 VALUES LESS THAN ( '74999999999999' ),

PARTITION P7 VALUES LESS THAN ( '87499999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

);

 

ALTER TABLE CI_TD_ENTRY ADD CONSTRAINT XT039P0 PRIMARY KEY(TD_ENTRY_ID) USING INDEX;

 

CREATE UNIQUE INDEX XT039S2 ON CI_TD_ENTRY ( ASSIGNED_TO, TD_ENTRY_ID ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW;

 

CREATE INDEX XT039S3 ON CI_TD_ENTRY ( ENTRY_STATUS_FLG, ASSIGNED_TO ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW;

 

CREATE INDEX XT039S4 ON CI_TD_ENTRY ( ROLE_ID, TD_TYPE_CD, ENTRY_STATUS_FLG, TD_PRIORITY_FLG, ASSIGNED_TO, CRE_DTTM ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW;

 

CREATE INDEX XT039S5 ON CI_TD_ENTRY ( BATCH_CD, BATCH_NBR, ENTRY_STATUS_FLG ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW;

 

CREATE UNIQUE INDEX XT039S6 ON CI_TD_ENTRY ( TD_ENTRY_ID, ASSIGNED_TO, ENTRY_STATUS_FLG )TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW;

 

CREATE UNIQUE INDEX XT039S7 ON CI_TD_ENTRY ( COMPLETE_USER_ID, COMPLETE_DTTM, TD_ENTRY_ID ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW;

 

CREATE UNIQUE INDEX ILM_XT039S8 ON CI_TD_ENTRY ( ILM_DT, ILM_ARCH_SW, TD_ENTRY_ID ) LOCAL COMPRESS ADVANCED LOW;

CREATE TABLE CI_TD_DRLKEY

(

TD_ENTRY_ID CHAR(14) NOT NULL ENABLE,

SEQ_NUM NUMBER(3,0) NOT NULL ENABLE,

KEY_VALUE VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

CONSTRAINT CI_TD_DRLKEY_FK FOREIGN KEY(TD_ENTRY_ID) REFERENCES CI_TD_ENTRY ON DELETE CASCADE

)

PARTITION BY REFERENCE (CI_TD_DRLKEY_FK)

ENABLE ROW MOVEMENT;

 

CREATE UNIQUE INDEX XT037P0 ON CI_TD_DRLKEY ( TD_ENTRY_ID, SEQ_NUM ) TABLESPACE CM_XT039_IND

GLOBAL PARTITION BY RANGE (TD_ENTRY_ID)

(

PARTITION P1 VALUES LESS THAN ( '12499999999999' ),

PARTITION P2 VALUES LESS THAN ( '24999999999999' ),

PARTITION P3 VALUES LESS THAN ( '37499999999999' ),

PARTITION P4 VALUES LESS THAN ( '49999999999999' ),

PARTITION P5 VALUES LESS THAN ( '62499999999999' ),

PARTITION P6 VALUES LESS THAN ( '74999999999999' ),

PARTITION P7 VALUES LESS THAN ( '87499999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

)

COMPRESS ADVANCED LOW;

 

ALTER TABLE CI_TD_DRLKEY ADD CONSTRAINT XT037P0 PRIMARY KEY(TD_ENTRY_ID, SEQ_NUM) USING INDEX;

 

CREATE INDEX XT037S1 ON CI_TD_DRLKEY ( KEY_VALUE, TD_ENTRY_ID ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW;

 

CREATE TABLE CI_TD_ENTRY_CHA

(

TD_ENTRY_ID CHAR(14) NOT NULL ENABLE,

CHAR_TYPE_CD CHAR(8) NOT NULL ENABLE,

SEQ_NUM NUMBER(3,0) DEFAULT 0 NOT NULL ENABLE,

CHAR_VAL CHAR(16) DEFAULT ' ' NOT NULL ENABLE,

VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

ADHOC_CHAR_VAL VARCHAR2(254) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK1 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK2 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK3 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK4 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK5 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

SRCH_CHAR_VAL VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

CONSTRAINT CI_TD_ENTRY_CHA_FK FOREIGN KEY(TD_ENTRY_ID) REFERENCES CI_TD_ENTRY ON DELETE CASCADE

)

PARTITION BY REFERENCE (CI_TD_ENTRY_CHA_FK)

ENABLE ROW MOVEMENT;

 

CREATE UNIQUE INDEX XT701P0 ON CI_TD_ENTRY_CHA ( TD_ENTRY_ID, CHAR_TYPE_CD, SEQ_NUM ) TABLESPACE CM_XT039_IND

GLOBAL PARTITION BY RANGE (TD_ENTRY_ID)

(

PARTITION P1 VALUES LESS THAN ( '12499999999999' ),

PARTITION P2 VALUES LESS THAN ( '24999999999999' ),

PARTITION P3 VALUES LESS THAN ( '37499999999999' ),

PARTITION P4 VALUES LESS THAN ( '49999999999999' ),

PARTITION P5 VALUES LESS THAN ( '62499999999999' ),

PARTITION P6 VALUES LESS THAN ( '74999999999999' ),

PARTITION P7 VALUES LESS THAN ( '87499999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

)

COMPRESS ADVANCED LOW;

 

ALTER TABLE CI_TD_ENTRY_CHA ADD CONSTRAINT XT701P0 PRIMARY KEY(TD_ENTRY_ID, CHAR_TYPE_CD, SEQ_NUM) USING INDEX;

 

CREATE INDEX XT701S1 ON CI_TD_ENTRY_CHA ( SRCH_CHAR_VAL, CHAR_TYPE_CD, TD_ENTRY_ID ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW;

 

CREATE TABLE CI_TD_LOG

(

TD_ENTRY_ID CHAR(14) NOT NULL ENABLE,

SEQ_NUM NUMBER(3,0) NOT NULL ENABLE,

LOG_DTTM DATE NOT NULL ENABLE,

LOG_TYPE_FLG CHAR(4) DEFAULT ' ' NOT NULL ENABLE,

USER_ID CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

ASSIGNED_TO CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

DESCRLONG VARCHAR2(4000) DEFAULT ' ' NOT NULL ENABLE,

CONSTRAINT CI_TD_LOG_FK FOREIGN KEY(TD_ENTRY_ID) REFERENCES CI_TD_ENTRY ON DELETE CASCADE

)

PARTITION BY REFERENCE (CI_TD_LOG_FK)

ENABLE ROW MOVEMENT;

CREATE UNIQUE INDEX XT721P0 ON CI_TD_LOG ( TD_ENTRY_ID, SEQ_NUM ) TABLESPACE CM_XT039_IND

GLOBAL PARTITION BY RANGE (TD_ENTRY_ID)

(

PARTITION P1 VALUES LESS THAN ( '12499999999999' ),

PARTITION P2 VALUES LESS THAN ( '24999999999999' ),

PARTITION P3 VALUES LESS THAN ( '37499999999999' ),

PARTITION P4 VALUES LESS THAN ( '49999999999999' ),

PARTITION P5 VALUES LESS THAN ( '62499999999999' ),

PARTITION P6 VALUES LESS THAN ( '74999999999999' ),

PARTITION P7 VALUES LESS THAN ( '87499999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

)

COMPRESS ADVANCED LOW;

 

ALTER TABLE CI_TD_LOG ADD CONSTRAINT XT721P0 PRIMARY KEY(TD_ENTRY_ID, SEQ_NUM) USING INDEX;

 

CREATE INDEX XT721S1 ON CI_TD_LOG ( LOG_DTTM, USER_ID, LOG_TYPE_FLG, TD_ENTRY_ID ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW;

CREATE TABLE CI_TD_MSG_PARM

(

TD_ENTRY_ID CHAR(14) NOT NULL ENABLE,

SEQ_NUM NUMBER(3,0) NOT NULL ENABLE,

MSG_PARM_VAL VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,

VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

CONSTRAINT CI_TD_MSG_PARM_FK FOREIGN KEY(TD_ENTRY_ID) REFERENCES CI_TD_ENTRY ON DELETE CASCADE

)

PARTITION BY REFERENCE (CI_TD_MSG_PARM_FK)

ENABLE ROW MOVEMENT;

CREATE UNIQUE INDEX XT040P0 ON CI_TD_MSG_PARM ( TD_ENTRY_ID, SEQ_NUM ) TABLESPACE CM_XT039_IND

GLOBAL PARTITION BY RANGE (TD_ENTRY_ID)

(

PARTITION P1 VALUES LESS THAN ( '12499999999999' ),

PARTITION P2 VALUES LESS THAN ( '24999999999999' ),

PARTITION P3 VALUES LESS THAN ( '37499999999999' ),

PARTITION P4 VALUES LESS THAN ( '49999999999999' ),

PARTITION P5 VALUES LESS THAN ( '62499999999999' ),

PARTITION P6 VALUES LESS THAN ( '74999999999999' ),

PARTITION P7 VALUES LESS THAN ( '87499999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

)

COMPRESS ADVANCED LOW;

 

ALTER TABLE CI_TD_MSG_PARM ADD CONSTRAINT XT040P0 PRIMARY KEY(TD_ENTRY_ID, SEQ_NUM) USING INDEX;

 

CREATE TABLE CI_TD_SRTKEY

(

TD_ENTRY_ID CHAR(14) NOT NULL ENABLE,

SEQ_NUM NUMBER(3,0) NOT NULL ENABLE,

KEY_VALUE VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

CONSTRAINT CI_TD_SRTKEY_FK FOREIGN KEY(TD_ENTRY_ID) REFERENCES CI_TD_ENTRY ON DELETE CASCADE

)

PARTITION BY REFERENCE (CI_TD_SRTKEY_FK)

ENABLE ROW MOVEMENT;

 

CREATE UNIQUE INDEX XT041P0 ON CI_TD_SRTKEY ( TD_ENTRY_ID, SEQ_NUM ) TABLESPACE CM_XT039_IND

GLOBAL PARTITION BY RANGE (TD_ENTRY_ID)

(

PARTITION P1 VALUES LESS THAN ( '12499999999999' ),

PARTITION P2 VALUES LESS THAN ( '24999999999999' ),

PARTITION P3 VALUES LESS THAN ( '37499999999999' ),

PARTITION P4 VALUES LESS THAN ( '49999999999999' ),

PARTITION P5 VALUES LESS THAN ( '62499999999999' ),

PARTITION P6 VALUES LESS THAN ( '74999999999999' ),

PARTITION P7 VALUES LESS THAN ( '87499999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

)

COMPRESS ADVANCED LOW;

 

ALTER TABLE CI_TD_SRTKEY ADD CONSTRAINT XT041P0 PRIMARY KEY(TD_ENTRY_ID, SEQ_NUM) USING INDEX;

 

CREATE INDEX XT041S1 ON CI_TD_SRTKEY ( KEY_VALUE, TD_ENTRY_ID ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW;