--order 502432
      
      /*
      ACTION is CREATE Table REPORTSERVERCLUSTER
      */

CREATE SEQUENCE SEQ_REPORTSERVERCLUSTER
START WITH 1
INCREMENT BY 1;

CREATE TABLE REPORTSERVERCLUSTER (
       REPORTSERVERCLUSTER_ID NUMBER NOT NULL,
       DESCRIPTION          VARCHAR2(80) NOT NULL,
       REPORTTYPE           NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT ReportType_valid2
                                          CHECK (REPORTTYPE IN (0, 1)),
       ACTIVE               VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew177
                                          CHECK (ACTIVE IN ('Y', 'N')),
       DEFAULTSERVERCLUSTER VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew178
                                          CHECK (DEFAULTSERVERCLUSTER IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

COMMENT ON COLUMN REPORTSERVERCLUSTER.REPORTTYPE IS '0 = Production (Default)
1 = Reporting';

CREATE UNIQUE INDEX XAK1REPORTSERVERCLUSTER ON REPORTSERVERCLUSTER
(
       DESCRIPTION                    ASC
);


ALTER TABLE REPORTSERVERCLUSTER
       ADD CONSTRAINT XPKREPORTSERVERCLUSTER PRIMARY KEY (
              REPORTSERVERCLUSTER_ID);

insert into reportservercluster rsc
select seq_reportservercluster.nextval,
'Default',
0,
'N',
'Y',
getminsystemuser(),
sysdate
from dual
where (select count(1) from reportservercluster rsc where rsc.reporttype = 0) = 0 ; 
     
      /*
      ACTION is CREATE Table REPORTSERVER
      */

CREATE SEQUENCE SEQ_REPORTSERVER
START WITH 1
INCREMENT BY 1;

CREATE TABLE REPORTSERVER (
       REPORTSERVER_ID      NUMBER NOT NULL,
       HOSTNAME             VARCHAR2(30) NOT NULL,
       DESCRIPTION          VARCHAR2(80) NOT NULL,
       PORTNUMBER           NUMBER NOT NULL,
       RMIPORTNUMBER        NUMBER NULL,
       REPORTSERVERCLUSTER_ID NUMBER NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew179
                                          CHECK (ACTIVE IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1REPORTSERVER ON REPORTSERVER
(
       HOSTNAME                       ASC,
       PORTNUMBER		      ASC
);

CREATE INDEX XIF1REPORTSERVER ON REPORTSERVER
(
       REPORTSERVERCLUSTER_ID         ASC
);


ALTER TABLE REPORTSERVER
       ADD CONSTRAINT XPKREPORTSERVER PRIMARY KEY (REPORTSERVER_ID);

      
      /*
      CHANGE REPORT for Table DIALOGS
          - Adding column DIALOGTYPE
          - Adding column definition to DIALOGTYPE
          - Adding column REPORTTYPE
          - Adding column definition to REPORTTYPE
          - Adding column REPORTSERVERCLUSTER_ID
          - Adding index XIF1DIALOGS
          - Adding a new relationship DIALOGS_REPORTSERVERCLUSTER_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK DIALOGS_REPORTSERVERCLUSTER_ID of table DIALOGS, which references table REPORTSERVERCLUSTER).
      ACTION is ALTER or modify Table DIALOGS
      */

ALTER TABLE DIALOGS ADD (DIALOGTYPE           NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT DialogType_valid
                                          CHECK (DIALOGTYPE IN (0, 1, 2)));


COMMENT ON COLUMN DIALOGS.DIALOGTYPE IS '0 = Simple dialog (Default)
1 = Dialog with reports
2 = Report with Selection dialog';


ALTER TABLE DIALOGS ADD (REPORTTYPE           NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT ReportType_valid
                                          CHECK (REPORTTYPE IN (0, 1)));


COMMENT ON COLUMN DIALOGS.REPORTTYPE IS '0 = Production (Default)
1 = Reporting';


ALTER TABLE DIALOGS ADD (REPORTSERVERCLUSTER_ID NUMBER NULL);

CREATE INDEX XIF1DIALOGS ON DIALOGS
(
       REPORTSERVERCLUSTER_ID         ASC
);

      
      /*
      CHANGE REPORT for Table USERDEFINEDRPT
          - Adding column REPORTSERVERCLUSTER_ID
          - Adding index XIF1USERDEFINEDRPT
          - Adding a new relationship USERDEFINEDRPT_REPORTSERVERCLU
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK USERDEFINEDRPT_REPORTSERVERCLU of table USERDEFINEDRPT, which references table REPORTSERVERCLUSTER).
      ACTION is ALTER or modify Table USERDEFINEDRPT
      */

ALTER TABLE USERDEFINEDRPT ADD (REPORTSERVERCLUSTER_ID NUMBER NULL);

CREATE INDEX XIF1USERDEFINEDRPT ON USERDEFINEDRPT
(
       REPORTSERVERCLUSTER_ID         ASC
);


ALTER TABLE REPORTSERVER
       ADD (CONSTRAINT REPORTSERVER_REPORTSERVERCLUST
              FOREIGN KEY (REPORTSERVERCLUSTER_ID)
                             REFERENCES REPORTSERVERCLUSTER  (REPORTSERVERCLUSTER_ID));


ALTER TABLE DIALOGS
       ADD (CONSTRAINT DIALOGS_REPORTSERVERCLUSTER_ID
              FOREIGN KEY (REPORTSERVERCLUSTER_ID)
                             REFERENCES REPORTSERVERCLUSTER  (REPORTSERVERCLUSTER_ID));


ALTER TABLE USERDEFINEDRPT
       ADD (CONSTRAINT USERDEFINEDRPT_REPORTSERVERCLU
              FOREIGN KEY (REPORTSERVERCLUSTER_ID)
                             REFERENCES REPORTSERVERCLUSTER  (REPORTSERVERCLUSTER_ID));

update userdefinedrpt udr
set udr.reportservercluster_id =
(select rsc.reportservercluster_id from reportservercluster rsc where rsc.defaultservercluster = 'Y')
where udr.reportservercluster_id is null;



