CREATE BIGFILE TABLESPACE CM_XT012_P2017JAN DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

CREATE BIGFILE TABLESPACE CM_XT012_P2017FEB DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

CREATE BIGFILE TABLESPACE CM_XT012_P2017MAR DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

CREATE BIGFILE TABLESPACE CM_XT012_P2017APR DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

CREATE BIGFILE TABLESPACE CM_XT012_P2017MAY DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

CREATE BIGFILE TABLESPACE CM_XT012_P2017JUN DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

CREATE BIGFILE TABLESPACE CM_XT012_P2017JUL DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

CREATE BIGFILE TABLESPACE CM_XT012_P2017AUG DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

CREATE BIGFILE TABLESPACE CM_XT012_P2017SEP DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

CREATE BIGFILE TABLESPACE CM_XT012_P2017OCT DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

CREATE BIGFILE TABLESPACE CM_XT012_P2017NOV DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

CREATE BIGFILE TABLESPACE CM_XT012_P2017DEC DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

CREATE BIGFILE TABLESPACE CM_XT012_PMAX DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

 

CREATE TABLE CI_ADJ

( CHAR(12) NOT NULL ENABLE,

ADJ_ID

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

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

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

CRE_DT DATE, CHAR(4) DEFAULT '' NOT NULL ENABLE,

CAN_RSN_CD

ADJ_AMT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,

XFER_ADJ_ID CHAR(12) DEFAULT ' ' NOT NULL ENABLE,

CURRENCY_CD CHAR(3) DEFAULT '' NOT NULL ENABLE,

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

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

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

BASE_AMT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,

GEN_REF_DT DATE,

APPR_REQ_ID CHAR(12) DEFAULT ' ' NOT NULL ENABLE,

ADJ_DATA_AREA CLOB,

ILM_DT DATE,

ILM_ARCH_SW CHAR(1),

)

ENABLE ROW MOVEMENT

PARTITION BY RANGE (ILM_DT)

SUBPARTITION BY RANGE (ADJ_ID) SUBPARTITION TEMPLATE (

SUBPARTITION S01 VALUES LESS THAN ( '124999999999' ),

SUBPARTITION S02 VALUES LESS THAN ( '249999999999' ),

SUBPARTITION S03 VALUES LESS THAN ( '374999999999' ),

SUBPARTITION S04 VALUES LESS THAN ( '499999999999' ),

SUBPARTITION S05 VALUES LESS THAN ( '624999999999' ),

SUBPARTITION S06 VALUES LESS THAN ( '749999999999' ),

SUBPARTITION S07 VALUES LESS THAN ( '874999999999' ),

SUBPARTITION S08 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_XT012_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_XT012_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_XT012_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_XT012_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_XT012_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_XT012_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_XT012_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_XT012_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_XT012_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_XT012_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_XT012_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_XT012_P2017DEC,

PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)

tablespace CM_XT012_PMAX

)

 

/

 

CREATE BIGFILE TABLESPACE CM_XT012_IND DATAFILE '+DATA' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED

/

 

CREATE UNIQUE INDEX XT012P0 ON CI_ADJ ( ADJ_ID ) TABLESPACE CM_XT012_IND

GLOBAL PARTITION BY RANGE (ADJ_ID)

(

PARTITION P1 VALUES LESS THAN ( '124999999999' ),

PARTITION P2 VALUES LESS THAN ( '249999999999' ),

PARTITION P3 VALUES LESS THAN ( '374999999999' ),

PARTITION P4 VALUES LESS THAN ( '499999999999' ),

PARTITION P5 VALUES LESS THAN ( '624999999999' ),

PARTITION P6 VALUES LESS THAN ( '749999999999' ),

PARTITION P7 VALUES LESS THAN ( '874999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

)

/

 

ALTER TABLE CI_ADJ ADD CONSTRAINT XT012P0 PRIMARY KEY(ADJ_ID) USING INDEX

/

CREATE INDEX XT012S1 ON CI_ADJ ( SA_ID, ADJ_TYPE_CD ) TABLESPACE CM_XT012_IND COMPRESS ADVANCED LOW

/

CREATE UNIQUE INDEX XT012S2 ON CI_ADJ ( XFER_ADJ_ID, ADJ_ID ) TABLESPACE CM_XT012_IND COMPRESS ADVANCED LOW

/

CREATE UNIQUE INDEX XT012S3 ON CI_ADJ ( ILM_DT, ILM_ARCH_SW, ADJ_ID ) TABLESPACE CM_XT012_IND COMPRESS ADVANCED LOW

/

 

CREATE TABLE CI_ADJ_APREQ

(

AP_REQ_ID CHAR(12) NOT NULL ENABLE,

COUNTRY CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

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

ADJ_ID CHAR(12) DEFAULT ' ' NOT NULL ENABLE,

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

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

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

CITY VARCHAR2(90) DEFAULT ' ' NOT NULL ENABLE,

NUM1 CHAR(6) DEFAULT ' ' NOT NULL ENABLE,

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

COUNTY VARCHAR2(90) DEFAULT ' ' NOT NULL ENABLE,

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

STATE CHAR(6) DEFAULT ' ' NOT NULL ENABLE,

POSTAL CHAR(12) DEFAULT ' ' NOT NULL ENABLE,

CURRENCY_PYMNT CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

GEO_CODE CHAR(11) DEFAULT ' ' NOT NULL ENABLE,

IN_CITY_LIMIT CHAR(1) DEFAULT ' ' NOT NULL ENABLE,

PAID_AMT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,

SCHEDULED_PAY_DT DATE,

PYMNT_DT DATE,

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

PAY_DOC_ID VARCHAR2(20) DEFAULT ' ' NOT NULL ENABLE,

PAY_DOC_DT DATE,

PYMNT_ID CHAR(36) DEFAULT ' ' NOT NULL ENABLE,

PYMNT_METHOD_FLG CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

PYMNT_SEL_STAT_FLG CHAR(1) DEFAULT ' ' NOT NULL ENABLE,

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

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

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

CONSTRAINT CI_ADJ_APREQ_FK FOREIGN KEY(ADJ_ID) REFERENCES CI_ADJ ON DELETE CASCADE

)

PARTITION BY REFERENCE (CI_ADJ_APREQ_FK)

ENABLE ROW MOVEMENT

/

CREATE UNIQUE INDEX XT160P0 ON CI_ADJ_APREQ ( AP_REQ_ID ) TABLESPACE CM_XT012_IND

GLOBAL PARTITION BY RANGE (AP_REQ_ID)

(

PARTITION P1 VALUES LESS THAN ( '124999999999' ),

PARTITION P2 VALUES LESS THAN ( '249999999999' ),

PARTITION P3 VALUES LESS THAN ( '374999999999' ),

PARTITION P4 VALUES LESS THAN ( '499999999999' ),

PARTITION P5 VALUES LESS THAN ( '624999999999' ),

PARTITION P6 VALUES LESS THAN ( '749999999999' ),

PARTITION P7 VALUES LESS THAN ( '874999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

)

COMPRESS ADVANCED LOW

/

 

ALTER TABLE CI_ADJ_APREQ ADD CONSTRAINT XT160P0 PRIMARY KEY(AP_REQ_ID) USING INDEX

/

CREATE INDEX XT160S1 ON CI_ADJ_APREQ ( ADJ_ID ) TABLESPACE CM_XT012_IND

/

CREATE INDEX XT160S2 ON CI_ADJ_APREQ ( BATCH_CD, BATCH_NBR ) TABLESPACE CM_XT012_IND COMPRESS ADVANCED LOW

/

 

CREATE TABLE CI_ADJ_CALC_LN

(

ADJ_ID CHAR(12) NOT NULL ENABLE,

SEQNO NUMBER(5,0) NOT NULL ENABLE,

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

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

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

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

EFFDT DATE,

RC_SEQ NUMBER(4,0) DEFAULT 0 NOT NULL ENABLE,

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

CURRENCY_CD CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

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

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

PRT_SW CHAR(1) DEFAULT ' ' NOT NULL ENABLE,

APP_IN_SUMM_SW CHAR(1) DEFAULT ' ' NOT NULL ENABLE,

CALC_AMT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,

EXEMPT_AMT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,

BASE_AMT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,

MSR_PEAK_QTY_SW CHAR(1) DEFAULT ' ' NOT NULL ENABLE,

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

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

BILL_SQ NUMBER(18,6) DEFAULT 0 NOT NULL ENABLE,

AUDIT_CALC_AMT NUMBER(18,5) DEFAULT 0 NOT NULL ENABLE,

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

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

CONSTRAINT CI_ADJ_CALC_LN_FK FOREIGN KEY(ADJ_ID) REFERENCES CI_ADJ ON DELETE CASCADE

)

PARTITION BY REFERENCE (CI_ADJ_CALC_LN_FK)

ENABLE ROW MOVEMENT

/

 

CREATE UNIQUE INDEX XT310P0 ON CI_ADJ_CALC_LN ( ADJ_ID, SEQNO ) TABLESPACE CM_XT012_IND

GLOBAL PARTITION BY RANGE (ADJ_ID)

(

PARTITION P1 VALUES LESS THAN ( '124999999999' ),

PARTITION P2 VALUES LESS THAN ( '249999999999' ),

PARTITION P3 VALUES LESS THAN ( '374999999999' ),

PARTITION P4 VALUES LESS THAN ( '499999999999' ),

PARTITION P5 VALUES LESS THAN ( '624999999999' ),

PARTITION P6 VALUES LESS THAN ( '749999999999' ),

PARTITION P7 VALUES LESS THAN ( '874999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

)

COMPRESS ADVANCED LOW

/

 

ALTER TABLE CI_ADJ_CALC_LN ADD CONSTRAINT XT310P0 PRIMARY KEY(ADJ_ID, SEQNO) USING INDEX

/

 

CREATE TABLE CI_ADJ_CL_CHAR

(

ADJ_ID CHAR(12) NOT NULL ENABLE,

SEQNO NUMBER(5,0) NOT NULL ENABLE,

CHAR_TYPE_CD CHAR(8) NOT NULL ENABLE,

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

CHAR_VAL CHAR(16) DEFAULT ' ' 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,

CONSTRAINT CI_ADJ_CL_CHAR_FK FOREIGN KEY(ADJ_ID) REFERENCES CI_ADJ ON DELETE CASCADE

)

PARTITION BY REFERENCE (CI_ADJ_CL_CHAR_FK)

ENABLE ROW MOVEMENT

/

 

CREATE UNIQUE INDEX XT309P0 ON CI_ADJ_CL_CHAR ( ADJ_ID, SEQNO, CHAR_TYPE_CD ) TABLESPACE CM_XT012_IND

GLOBAL PARTITION BY RANGE (ADJ_ID)

(

PARTITION P1 VALUES LESS THAN ( '124999999999' ),

PARTITION P2 VALUES LESS THAN ( '249999999999' ),

PARTITION P3 VALUES LESS THAN ( '374999999999' ),

PARTITION P4 VALUES LESS THAN ( '499999999999' ),

PARTITION P5 VALUES LESS THAN ( '624999999999' ),

PARTITION P6 VALUES LESS THAN ( '749999999999' ),

PARTITION P7 VALUES LESS THAN ( '874999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

)

COMPRESS ADVANCED LOW

/

 

ALTER TABLE CI_ADJ_CL_CHAR ADD CONSTRAINT XT309P0 PRIMARY KEY(ADJ_ID, SEQNO, CHAR_TYPE_CD) USING INDEX

/

CREATE TABLE CI_ADJ_CHAR

(

ADJ_ID CHAR(12) NOT NULL ENABLE,

CHAR_TYPE_CD CHAR(8) NOT NULL ENABLE,

SEQ_NUM NUMBER(3,0) NOT NULL ENABLE,

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

CHAR_VAL CHAR(16) DEFAULT ' ' 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_ADJ_CHAR_FK FOREIGN KEY(ADJ_ID) REFERENCES CI_ADJ ON DELETE CASCADE

)

PARTITION BY REFERENCE (CI_ADJ_CHAR_FK)

ENABLE ROW MOVEMENT

/

 

CREATE UNIQUE INDEX XC781P0 ON CI_ADJ_CHAR (ADJ_ID, CHAR_TYPE_CD, SEQ_NUM) TABLESPACE CM_XT012_IND

GLOBAL PARTITION BY RANGE(ADJ_ID)

(

PARTITION PART1 VALUES LESS THAN ('124999999999'),

PARTITION PART2 VALUES LESS THAN ('249999999999'),

PARTITION PART3 VALUES LESS THAN ('374999999999'),

PARTITION PART4 VALUES LESS THAN ('499999999999'),

PARTITION PART5 VALUES LESS THAN ('624999999999'),

PARTITION PART6 VALUES LESS THAN ('749999999999'),

PARTITION PART7 VALUES LESS THAN ('874999999999'),

PARTITION PART8 VALUES LESS THAN (MAXVALUE)

)

COMPRESS ADVANCED LOW

/

 

ALTER TABLE CI_ADJ_CHAR ADD CONSTRAINT XC781P0 PRIMARY KEY (ADJ_ID, CHAR_TYPE_CD, SEQ_NUM) USING INDEX;

 

CREATE INDEX XC781S1 ON CI_ADJ_CHAR(SRCH_CHAR_VAL)

GLOBAL PARTITION BY HASH(SRCH_CHAR_VAL)

(

PARTITION PART1 TABLESPACE CM_XT012_IND,

PARTITION PART2 TABLESPACE CM_XT012_IND,

PARTITION PART3 TABLESPACE CM_XT012_IND,

PARTITION PART4 TABLESPACE CM_XT012_IND,

PARTITION PART5 TABLESPACE CM_XT012_IND,

PARTITION PART6 TABLESPACE CM_XT012_IND,

PARTITION PART7 TABLESPACE CM_XT012_IND,

PARTITION PART8 TABLESPACE CM_XT012_IND

)

/