--
--    Licensed Materials - Property of IBM
--    
--    BI and PM: JSM
--    
--    (c) Copyright IBM Corp. 2003, 2010.
--    
--    US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
--    


-------------------------------------------------------------------------------
--	NC4_UPDATE_ORA.SQL			*	DO NOT EDIT	 *			Data Store Version: 4 		 
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
--$100-- backup create begin
-------------------------------------------------------------------------------

CREATE TABLE X_MESSAGESTRUCT(
   PK_MS_ID                      INTEGER NOT NULL,
   FK_DYNREC_ID                  INTEGER,
   NAME                          VARCHAR2(255),
   DESCRIPTION                   VARCHAR2(1000),
   SOURCE                        VARCHAR2(100),
   SUBJECT                       VARCHAR2(210),
   MODCOUNT                      INTEGER DEFAULT (-1),
   LASTMOD_BY                    VARCHAR(50),
   CHANNEL_TYPE                  INTEGER,
   CONTACT                       VARCHAR2(50),
   AVOID_DUPLICATION             INTEGER DEFAULT (0) NOT NULL
)
;

-------------------------------------------------------------------------------
--$110-- backup inserts begin
-------------------------------------------------------------------------------

INSERT INTO X_MESSAGESTRUCT (PK_MS_ID, FK_DYNREC_ID, NAME, DESCRIPTION,
   SOURCE, SUBJECT, MODCOUNT, LASTMOD_BY, CHANNEL_TYPE, CONTACT, AVOID_DUPLICATION) 
SELECT PK_MS_ID, FK_DYNREC_ID, NAME, DESCRIPTION,
   SOURCE, SUBJECT, MODCOUNT, LASTMOD_BY, CHANNEL_TYPE, CONTACT, AVOID_DUPLICATION FROM  NC_MESSAGESTRUCT 
;   

-------------------------------------------------------------------------------
--$120-- foreign keys drop begin
-------------------------------------------------------------------------------

ALTER TABLE NC_MESSAGESTRUCT
   DROP CONSTRAINT F2N_MESSAGESTRUCT
;

ALTER TABLE NC_MESSAGELINE
   DROP CONSTRAINT F1N_MESSAGELINE 
;

ALTER TABLE NC_MESSAGE_ATTACHMENT
   DROP CONSTRAINT F2N_MESSAGE_ATTACH
;

ALTER TABLE R_MESSAGESTRUCT_TASK
   DROP CONSTRAINT F1R_MESSAGESTRUCT_
;


ALTER TABLE R_MSGSTRUCT_AVOIDTOPICS
   DROP CONSTRAINT F1R_MSGSTRUCT_AVOI
;

ALTER TABLE R_MSGSTRUCT_RECIPIENT_DELIVINF
   DROP CONSTRAINT F1R_MSGSTRUCT_RECI
;

-------------------------------------------------------------------------------
--$130-- views drop begin
-------------------------------------------------------------------------------


-------------------------------------------------------------------------------
--$140-- tables drop begin
-------------------------------------------------------------------------------

DROP TABLE NC_MESSAGESTRUCT
;

-------------------------------------------------------------------------------
--$200-- recreate tables begin
-------------------------------------------------------------------------------

CREATE TABLE NC_MESSAGESTRUCT(
   PK_MS_ID                      INTEGER NOT NULL,
   FK_DYNREC_ID                  INTEGER,
   NAME                          VARCHAR2(255),
   DESCRIPTION                   VARCHAR2(1000),
   SOURCE                        VARCHAR2(100),
   SUBJECT                       VARCHAR2(210),
   MODCOUNT                      INTEGER DEFAULT (-1),
   LASTMOD_BY                    VARCHAR(50),
   CHANNEL_TYPE                  INTEGER,
   CONTACT                       VARCHAR2(50),
   CREDENTIAL                    VARCHAR2(1024),
   AVOID_DUPLICATION             INTEGER DEFAULT (0) NOT NULL
)
;


-------------------------------------------------------------------------------
--$203-- alter tables begin
-------------------------------------------------------------------------------

CREATE INDEX I1N_MESSAGESTRUCT ON NC_MESSAGESTRUCT (
   FK_DYNREC_ID)
;

ALTER TABLE NC_MESSAGESTRUCT
   ADD CONSTRAINT PN_MESSAGESTRUCT PRIMARY KEY (PK_MS_ID)
;


-------------------------------------------------------------------------------
--$210-- views create begin
-------------------------------------------------------------------------------



-------------------------------------------------------------------------------
--$220-- server data inserts begin
-------------------------------------------------------------------------------

INSERT INTO NC_DB_VERSION (DB_VERSION_ID,BUILD) VALUES (4,'build no.')
;


-------------------------------------------------------------------------------
--$230-- restore inserts begin
-------------------------------------------------------------------------------

INSERT INTO NC_MESSAGESTRUCT (PK_MS_ID, FK_DYNREC_ID, NAME, DESCRIPTION,
   SOURCE, SUBJECT, MODCOUNT, LASTMOD_BY, CHANNEL_TYPE, CONTACT, AVOID_DUPLICATION) 
SELECT PK_MS_ID, FK_DYNREC_ID, NAME, DESCRIPTION,
   SOURCE, SUBJECT, MODCOUNT, LASTMOD_BY, CHANNEL_TYPE, CONTACT, AVOID_DUPLICATION FROM  X_MESSAGESTRUCT 
;   

-------------------------------------------------------------------------------
--$240-- foreign keys add begin
-------------------------------------------------------------------------------

ALTER TABLE NC_MESSAGESTRUCT
   ADD CONSTRAINT F2N_MESSAGESTRUCT FOREIGN KEY (FK_DYNREC_ID)
      REFERENCES NC_DYNAMIC_RECIPIENT_STRUCT
;

ALTER TABLE R_MESSAGESTRUCT_TASK
   ADD CONSTRAINT F1R_MESSAGESTRUCT_ FOREIGN KEY (FK_MESSAGESTRUCT_ID)
      REFERENCES NC_MESSAGESTRUCT
;


ALTER TABLE R_MSGSTRUCT_AVOIDTOPICS
   ADD CONSTRAINT F1R_MSGSTRUCT_AVOI FOREIGN KEY (FK_MS_ID)
      REFERENCES NC_MESSAGESTRUCT
;


ALTER TABLE NC_MESSAGELINE
   ADD CONSTRAINT F1N_MESSAGELINE FOREIGN KEY (FK_MESSAGESTRUCT_ID)
      REFERENCES NC_MESSAGESTRUCT
;

ALTER TABLE NC_MESSAGE_ATTACHMENT
   ADD CONSTRAINT F2N_MESSAGE_ATTACH FOREIGN KEY (FK_MESSAGE_ID)
      REFERENCES NC_MESSAGESTRUCT
;

ALTER TABLE R_MSGSTRUCT_RECIPIENT_DELIVINF
   ADD CONSTRAINT F1R_MSGSTRUCT_RECI FOREIGN KEY (FK_MESSAGESTRUCT_ID)
      REFERENCES NC_MESSAGESTRUCT
;

-------------------------------------------------------------------------------
--$250-- backup drop begin
-------------------------------------------------------------------------------

DROP TABLE X_MESSAGESTRUCT
;

-------------------------------------------------------------------------------
--$260-- end sql by function
-------------------------------------------------------------------------------
--	END NC4_UPDATE_ORA.SQL			*	DO NOT EDIT	 * 		 
-------------------------------------------------------------------------------