--Order 20014363.60

---GRID table---
CREATE SEQUENCE SEQ_GRID
START WITH 1
INCREMENT BY 1;

CREATE TABLE GRID
(
  GRID_ID              NUMBER NOT NULL ,
  NAME                 VARCHAR2(300) NOT NULL ,
  DEFAULTGRIDVIEW_ID   NUMBER NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE GRID
ADD CONSTRAINT  XPKGRID PRIMARY KEY (GRID_ID);

CREATE UNIQUE INDEX XAK1GRID ON GRID
(NAME   ASC);

CREATE INDEX XIF1GRID ON GRID
(DEFAULTGRIDVIEW_ID   ASC);


---GRIDCOLUMN table---
CREATE SEQUENCE SEQ_GRIDCOLUMN
START WITH 1
INCREMENT BY 1;

CREATE TABLE GRIDCOLUMN
(
  GRIDCOLUMN_ID        NUMBER NOT NULL ,
  GRID_ID              NUMBER NOT NULL ,
  NAME                 VARCHAR2(30) NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE GRIDCOLUMN
ADD CONSTRAINT  XPKGRIDCOLUMN PRIMARY KEY (GRIDCOLUMN_ID);

CREATE UNIQUE INDEX XAK1GRIDCOLUMN ON GRIDCOLUMN
(GRID_ID   ASC,NAME   ASC);

CREATE INDEX XIF1GRIDCOLUMN ON GRIDCOLUMN
(GRID_ID   ASC);

ALTER TABLE GRIDCOLUMN
ADD CONSTRAINT GRIDCOLUMN_GRID FOREIGN KEY (GRID_ID) REFERENCES GRID (GRID_ID) ON DELETE CASCADE;

---GRIDVIEW table---
CREATE SEQUENCE SEQ_GRIDVIEW
START WITH 1
INCREMENT BY 1;

CREATE TABLE GRIDVIEW
(
  GRIDVIEW_ID          NUMBER NOT NULL ,
  GRID_ID              NUMBER NOT NULL ,
  SHARED_YN            VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField219 CHECK (SHARED_YN IN ('Y', 'N')),
  OWNERUSER_ID         NUMBER NULL ,
  AUTOLOADALLROWS_YN   VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField220 CHECK (AUTOLOADALLROWS_YN IN ('Y', 'N')),
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE GRIDVIEW
ADD CONSTRAINT  XPKGRIDVIEW PRIMARY KEY (GRIDVIEW_ID);

CREATE INDEX XIF1GRIDVIEW ON GRIDVIEW
(GRID_ID   ASC);

CREATE INDEX XIF2GRIDVIEW ON GRIDVIEW
(OWNERUSER_ID   ASC);

ALTER TABLE GRIDVIEW
ADD CONSTRAINT GRIDVIEW_GRID FOREIGN KEY (GRID_ID) REFERENCES GRID (GRID_ID) ON DELETE CASCADE;

ALTER TABLE GRIDVIEW
ADD CONSTRAINT GRIDVIEW_SYSTEMUSER FOREIGN KEY (OWNERUSER_ID) REFERENCES SYSTEMUSER (SYSTEMUSER_ID) ON DELETE CASCADE;

ALTER TABLE GRID
ADD CONSTRAINT GRID_DEFAULTGRIDVIEW FOREIGN KEY (DEFAULTGRIDVIEW_ID) REFERENCES GRIDVIEW (GRIDVIEW_ID);

---GRIDVIEW_DESC table---
CREATE TABLE GRIDVIEW_DESC
(
  GRIDVIEW_ID          NUMBER NOT NULL ,
  LANGUAGE_ID          NUMBER NOT NULL ,
  DESCRIPTION          VARCHAR2(30) NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE GRIDVIEW_DESC
ADD CONSTRAINT  XPKGRIDVIEW_DESC PRIMARY KEY (GRIDVIEW_ID,LANGUAGE_ID);

CREATE INDEX XIF1GRIDVIEW_DESC ON GRIDVIEW_DESC
(GRIDVIEW_ID   ASC);

CREATE INDEX XIF2GRIDVIEW_DESC ON GRIDVIEW_DESC
(LANGUAGE_ID   ASC);

ALTER TABLE GRIDVIEW_DESC
ADD CONSTRAINT GRIDVIEWDESC_GRIDVIEW FOREIGN KEY (GRIDVIEW_ID) REFERENCES GRIDVIEW (GRIDVIEW_ID) ON DELETE CASCADE;

ALTER TABLE GRIDVIEW_DESC
ADD CONSTRAINT GRIDVIEWDESC_LANGUAGES FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;

---VIEWCOLUMN table---
CREATE TABLE VIEWCOLUMN
(
  GRIDVIEW_ID          NUMBER NOT NULL ,
  GRIDCOLUMN_ID        NUMBER NOT NULL ,
  SEQUENCENUMBER       NUMBER NOT NULL ,
  COLUMNWIDTH          NUMBER NOT NULL ,
  SORTINGPRIORITY      NUMBER NOT NULL ,
  SORTINGORDER         NUMBER NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN VIEWCOLUMN.SEQUENCENUMBER IS '1=first visible column, 2=second etc.';

COMMENT ON COLUMN VIEWCOLUMN.COLUMNWIDTH IS 'Column width in pixels';

COMMENT ON COLUMN VIEWCOLUMN.SORTINGPRIORITY IS '0 = no sorting
1..n = index of column in the sort model (where ''n'' is total number of grid columns used to sort the grid)
';

COMMENT ON COLUMN VIEWCOLUMN.SORTINGORDER IS '1=Ascending
2=Descending
';

CREATE UNIQUE INDEX XAK1VIEWCOLUMN ON VIEWCOLUMN
(GRIDVIEW_ID   ASC,SEQUENCENUMBER   ASC);

CREATE INDEX XIF1VIEWCOLUMN ON VIEWCOLUMN
(GRIDVIEW_ID   ASC);

CREATE INDEX XIF2VIEWCOLUMN ON VIEWCOLUMN
(GRIDCOLUMN_ID   ASC);

ALTER TABLE VIEWCOLUMN
ADD CONSTRAINT  XPKVIEWCOLUMN PRIMARY KEY (GRIDVIEW_ID,GRIDCOLUMN_ID);

ALTER TABLE VIEWCOLUMN
ADD CONSTRAINT VIEWCOLUMN_GRIDVIEW FOREIGN KEY (GRIDVIEW_ID) REFERENCES GRIDVIEW (GRIDVIEW_ID) ON DELETE CASCADE;

ALTER TABLE VIEWCOLUMN
ADD CONSTRAINT VIEWCOLUMN_GRIDCOLUMN FOREIGN KEY (GRIDCOLUMN_ID) REFERENCES GRIDCOLUMN (GRIDCOLUMN_ID) ON DELETE CASCADE;

---VIEWFILTER table---
CREATE SEQUENCE SEQ_VIEWFILTER
START WITH 1
INCREMENT BY 1;

CREATE TABLE VIEWFILTER
(
  VIEWFILTER_ID        NUMBER NOT NULL ,
  GRIDVIEW_ID          NUMBER NOT NULL ,
  GRIDCOLUMN_ID        NUMBER NOT NULL ,
  OPERATOR             NUMBER NOT NULL ,
  VALUE                VARCHAR2(40) NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN VIEWFILTER.OPERATOR IS '1 = equal to
2 = not equal to
3 = greater than
4 = greater or equal to
5 = lower or equal to
6 = lower than
7 = like
8 = begins with
11 = begins with (case insensitive)
12 = like (case insensitive)
13 = is empty
14 = is not empty
15 = ends with
16 = equals to (case insensitive)
';

CREATE INDEX XIF1VIEWFILTER ON VIEWFILTER
(GRIDVIEW_ID   ASC);

CREATE INDEX XIF2VIEWFILTER ON VIEWFILTER
(GRIDCOLUMN_ID   ASC);

ALTER TABLE VIEWFILTER
ADD CONSTRAINT  XPKVIEWFILTER PRIMARY KEY (VIEWFILTER_ID);

ALTER TABLE VIEWFILTER
ADD CONSTRAINT VIEWFILTER_GRIDVIEW FOREIGN KEY (GRIDVIEW_ID) REFERENCES GRIDVIEW (GRIDVIEW_ID) ON DELETE CASCADE;

ALTER TABLE VIEWFILTER
ADD CONSTRAINT VIEWFILTER_GRIDCOLUMN FOREIGN KEY (GRIDCOLUMN_ID) REFERENCES GRIDCOLUMN (GRIDCOLUMN_ID) ON DELETE CASCADE;

---VIEWMISCFILTER table---
CREATE TABLE VIEWMISCFILTER
(
  GRIDVIEW_ID          NUMBER NOT NULL ,
  COMPONENTNAME        VARCHAR2(30) NOT NULL ,
  VALUE                VARCHAR2(30) NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);

CREATE INDEX XIF1VIEWMISCFILTER ON VIEWMISCFILTER
(GRIDVIEW_ID   ASC);

ALTER TABLE VIEWMISCFILTER
ADD CONSTRAINT  XPKVIEWMISCFILTER PRIMARY KEY (GRIDVIEW_ID, COMPONENTNAME);

ALTER TABLE VIEWMISCFILTER
ADD CONSTRAINT VIEWMISCFILTER_GRIDVIEW FOREIGN KEY (GRIDVIEW_ID) REFERENCES GRIDVIEW (GRIDVIEW_ID) ON DELETE CASCADE;

---LASTUSEDGRIDVIEW table---
CREATE TABLE LASTUSEDGRIDVIEW
(
  GRID_ID              NUMBER NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  VIEW_ID              NUMBER NULL ,
  TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE LASTUSEDGRIDVIEW
ADD CONSTRAINT  XPKLASTUSEDGRIDVIEW PRIMARY KEY (GRID_ID,SYSTEMUSER_ID);

CREATE INDEX XIF1LASTUSEDGRIDVIEW ON LASTUSEDGRIDVIEW
(GRID_ID   ASC);

CREATE INDEX XIF2LASTUSEDGRIDVIEW ON LASTUSEDGRIDVIEW
(SYSTEMUSER_ID   ASC);

CREATE INDEX XIF3LASTUSEDGRIDVIEW ON LASTUSEDGRIDVIEW
(VIEW_ID   ASC);

ALTER TABLE LASTUSEDGRIDVIEW
ADD CONSTRAINT LASTUSEDGRIDVIEW_GRID FOREIGN KEY (GRID_ID) REFERENCES GRID (GRID_ID) ON DELETE CASCADE;

ALTER TABLE LASTUSEDGRIDVIEW
ADD CONSTRAINT LASTUSEDGRIDVIEW_SYSTEMUSER FOREIGN KEY (SYSTEMUSER_ID) REFERENCES SYSTEMUSER (SYSTEMUSER_ID) ON DELETE CASCADE;

ALTER TABLE LASTUSEDGRIDVIEW
ADD CONSTRAINT LASTUSEDGRIDVIEW_GRIDVIEW FOREIGN KEY (VIEW_ID) REFERENCES GRIDVIEW (GRIDVIEW_ID) ON DELETE SET NULL;

---SELECTIONCRITERIA table---
ALTER TABLE SELECTIONCRITERIA
  ADD (CONVERTED_YN VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField221 CHECK (CONVERTED_YN IN ('Y', 'N')));
