DROP TABLE ERX.INBOUND_NCPDP_MSG CASCADE CONSTRAINTS ;

CREATE TABLE ERX.INBOUND_NCPDP_MSG
  (
    INBOUND_NCPDP_MSG_ID NUMBER NOT NULL ,
    MESSAGE_ID           VARCHAR2 (35) ,
    REL_TO_MESSAGE_ID    VARCHAR2 (35) ,
    MESSAGE_TYPE         VARCHAR2 (35) ,
    MESSAGE_FROM         VARCHAR2 (35) ,
    MESSAGE XMLTYPE ,
    PHARMACY_ID            NUMBER DEFAULT 0 NOT NULL ,
    PATIENT_CHK_STATUS     VARCHAR2 (4) DEFAULT '0000' NOT NULL ,
    PATIENT_MATCH_DETAILS  VARCHAR2 (2000) ,
    PROVIDER_CHK_STATUS    VARCHAR2 (4) DEFAULT '0000' NOT NULL ,
    PROVIDER_MATCH_DETAILS VARCHAR2 (2000) ,
    DRUG_CHK_STATUS        VARCHAR2 (4) DEFAULT '0000' NOT NULL ,
    DRUG_MATCH_DETAILS     VARCHAR2 (2000) ,
    MESSAGE_STATUS         VARCHAR2 (4) DEFAULT '0000' NOT NULL ,
    MESSAGE_STATUS_DETAILS     VARCHAR2 (2000) ,
    RECEIVED_DATE          DATE ,
    UPDATED_DATE           DATE ,
    CREATED_DATE           DATE
  )
  PARTITION BY RANGE (RECEIVED_DATE) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
(  
   PARTITION rcvd_date_p1 VALUES LESS THAN (TO_DATE('1-6-2017', 'DD-MM-YYYY')),
   PARTITION rcvd_date_p2 VALUES LESS THAN (TO_DATE('1-7-2017', 'DD-MM-YYYY')), 
   PARTITION rcvd_date_p3 VALUES LESS THAN (TO_DATE('1-8-2017', 'DD-MM-YYYY')), 
   PARTITION rcvd_date_p4 VALUES LESS THAN (TO_DATE('1-9-2017', 'DD-MM-YYYY')), 
   PARTITION rcvd_date_p5 VALUES LESS THAN (TO_DATE('1-10-2017', 'DD-MM-YYYY')), 
   PARTITION rcvd_date_p6 VALUES LESS THAN (TO_DATE('1-11-2017', 'DD-MM-YYYY')), 
   PARTITION rcvd_date_p7 VALUES LESS THAN (TO_DATE('1-12-2017', 'DD-MM-YYYY')), 
   PARTITION rcvd_date_p8 VALUES LESS THAN (TO_DATE('1-1-2018', 'DD-MM-YYYY')), 
   PARTITION rcvd_date_p9 VALUES LESS THAN (TO_DATE('1-2-2018', 'DD-MM-YYYY')), 
   PARTITION rcvd_date_p10 VALUES LESS THAN (TO_DATE('1-3-2018', 'DD-MM-YYYY')), 
   PARTITION rcvd_date_p11 VALUES LESS THAN (TO_DATE('1-4-2018', 'DD-MM-YYYY')), 
   PARTITION rcvd_date_p12 VALUES LESS THAN (TO_DATE('1-5-2018', 'DD-MM-YYYY')))
  LOGGING XMLTYPE COLUMN MESSAGE STORE AS BINARY XML
  (
    STORAGE ( PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS UNLIMITED FREELISTS 1 BUFFER_POOL DEFAULT ) RETENTION ENABLE STORAGE IN ROW NOCACHE
  ) ;
CREATE INDEX ERX.IB_NCPDP_MSG_IDX ON ERX.INBOUND_NCPDP_MSG
  (
    PHARMACY_ID ASC
  )
  LOGGING ;
CREATE UNIQUE INDEX ERX.IB_NCPDP_MSG_PK ON ERX.INBOUND_NCPDP_MSG
  (
    INBOUND_NCPDP_MSG_ID ASC
  )
  LOGGING ;