-- order 502646
 
      /*
      ACTION is CREATE Table DATABASESCRIPT
      */

CREATE TABLE DATABASESCRIPT (
       DATABASESCRIPT_ID    NUMBER NOT NULL,
       CODE                 VARCHAR2(6) NOT NULL,
       USAGE                NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT DatabaseScriptUsage_valid
                                          CHECK (USAGE IN (0, 1, 2, 3)),
       DESCRIPTION          VARCHAR2(100) DEFAULT ' ' NOT NULL,
       SCRIPT               CLOB NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationField7
                                          CHECK (ACTIVE IN ('Y', 'N')),
       EXECUTIONHISTORY     VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationField8
                                          CHECK (EXECUTIONHISTORY IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE SEQUENCE SEQ_DATABASESCRIPT
START WITH 1
INCREMENT BY 1;

COMMENT ON COLUMN DATABASESCRIPT.USAGE IS '0=Incidental
1=Scheduled
2=Pre/post process
3=User defined query';

CREATE UNIQUE INDEX XAK1DATABASESCRIPT ON DATABASESCRIPT
(
       CODE                           ASC,
       USAGE                          ASC
);

CREATE UNIQUE INDEX XAK2DATABASESCRIPT ON DATABASESCRIPT
(
       CODE                           ASC,
       DESCRIPTION                    ASC
);


ALTER TABLE DATABASESCRIPT
       ADD  ( CONSTRAINT XPKDATABASESCRIPT PRIMARY KEY (
              DATABASESCRIPT_ID) ) ;

      
      /*
      ACTION is CREATE Table DATABASESCRIPTEXECUTION
      */

CREATE TABLE DATABASESCRIPTEXECUTION (
       DATABASESCRIPT_ID    NUMBER NOT NULL,
       RESULT               NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT DBScriptExecutionResult_valid
                                          CHECK (RESULT IN (0, 1)),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE SEQUENCE SEQ_DATABASESCRIPTEXECUTION
START WITH 1
INCREMENT BY 1;

COMMENT ON COLUMN DATABASESCRIPTEXECUTION.RESULT IS '0 = Successful
1 = Error';

CREATE INDEX XIF1DATABASESCRIPTEXECUTION ON DATABASESCRIPTEXECUTION
(
       DATABASESCRIPT_ID              ASC
);


ALTER TABLE DATABASESCRIPTEXECUTION
       ADD  ( CONSTRAINT XPKDATABASESCRIPTEXECUTION PRIMARY KEY (
              DATABASESCRIPT_ID, SYSTEMUSER_ID, TIMESTAMP) ) ;

      
      /*
      CHANGE REPORT for Table PLANTPROCESS
          - Adding a new relationship PLANTPROCESS_POSTSCRIPT_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK PLANTPROCESS_POSTSCRIPT_ID of table PLANTPROCESS, which references table DATABASESCRIPT).
          - Adding a new relationship PLANTPROCESS_PRESCRIPT_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK PLANTPROCESS_PRESCRIPT_ID of table PLANTPROCESS, which references table DATABASESCRIPT).
          - Adding a new relationship PLANTPROCESS_SCHEDULEDSCRIPT_I
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK PLANTPROCESS_SCHEDULEDSCRIPT_I of table PLANTPROCESS, which references table DATABASESCRIPT).
          - Changing column definition of STATUS
          - Adding column SCHEDULEDSCRIPT_ID
          - Adding column PRESCRIPT_ID
          - Adding column POSTSCRIPT_ID
          - Adding index XIF4PLANTPROCESS
          - Adding index XIF5PLANTPROCESS
          - Adding index XIF6PLANTPROCESS
      ACTION is ALTER or modify Table PLANTPROCESS
      */

COMMENT ON COLUMN PLANTPROCESS.STATUS IS '0 = Waiting for next run
1 = In progress
2 = Run successfully
3 = Main script Failed
4 = Pre-Script failed
5 = Post-Script failed';


ALTER TABLE PLANTPROCESS ADD (SCHEDULEDSCRIPT_ID   NUMBER NULL);


ALTER TABLE PLANTPROCESS ADD (PRESCRIPT_ID         NUMBER NULL);


ALTER TABLE PLANTPROCESS ADD (POSTSCRIPT_ID        NUMBER NULL);


CREATE INDEX XIF4PLANTPROCESS ON PLANTPROCESS
(
       SCHEDULEDSCRIPT_ID             ASC
);

CREATE INDEX XIF5PLANTPROCESS ON PLANTPROCESS
(
       PRESCRIPT_ID                   ASC
);

CREATE INDEX XIF6PLANTPROCESS ON PLANTPROCESS
(
       POSTSCRIPT_ID                  ASC
);


ALTER TABLE DATABASESCRIPTEXECUTION
       ADD  ( CONSTRAINT DATABASESCRIPTEXECUTION_DATABA
              FOREIGN KEY (DATABASESCRIPT_ID)
                             REFERENCES DATABASESCRIPT ) ;


ALTER TABLE PLANTPROCESS
       ADD  ( CONSTRAINT PLANTPROCESS_POSTSCRIPT_ID
              FOREIGN KEY (POSTSCRIPT_ID)
                             REFERENCES DATABASESCRIPT ) ;


ALTER TABLE PLANTPROCESS
       ADD  ( CONSTRAINT PLANTPROCESS_PRESCRIPT_ID
              FOREIGN KEY (PRESCRIPT_ID)
                             REFERENCES DATABASESCRIPT ) ;


ALTER TABLE PLANTPROCESS
       ADD  ( CONSTRAINT PLANTPROCESS_SCHEDULEDSCRIPT_I
              FOREIGN KEY (SCHEDULEDSCRIPT_ID)
                             REFERENCES DATABASESCRIPT ) ;


  insert into process
        (process_id, description, active)
         values
        ('90', ' User defined database script', 'Y');



