Maintenance Object: TO DO ENTRY
Parent Table: CI_TD_ENTRY
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
);
INDEX
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;
Child Table: CI_TD_DRLKEY
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;
INDEX
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;
Child Table: CI_TD_ENTRY_CHA
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;
INDEX
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;
Child Table: CI_TD_LOG
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;
INDEX
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;
Child Table: CI_TD_MSG_PARM
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;
INDEX
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;
Child Table: CI_TD_SRTKEY
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;
INDEX
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;