-- ABS-6986
--------------   WORKFLOW   --------------
CREATE SEQUENCE SEQ_WORKFLOW
START WITH 1
INCREMENT BY 1;

CREATE TABLE WORKFLOW
(
WORKFLOW_ID NUMBER NOT NULL,
CODE VARCHAR2(12) NOT NULL,
DISPLAYSEQUENCE NUMBER NOT NULL,
ACTIVE VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD321 CHECK (ACTIVE IN ('Y','N')),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE WORKFLOW ADD CONSTRAINT XPKWORKFLOW PRIMARY KEY (WORKFLOW_ID);

CREATE UNIQUE INDEX XAK1WORKFLOW ON WORKFLOW (CODE ASC);

--------------   WORKFLOW_DESC   --------------
CREATE TABLE WORKFLOW_DESC
(
WORKFLOW_ID NUMBER NOT NULL,
LANGUAGE_ID NUMBER NOT NULL,
DESCRIPTION VARCHAR2(60) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE WORKFLOW_DESC ADD CONSTRAINT XPKWORKFLOW_DESC PRIMARY KEY (WORKFLOW_ID, LANGUAGE_ID);

CREATE INDEX XIF1WORKFLOW_DESC ON WORKFLOW_DESC (WORKFLOW_ID ASC);

CREATE INDEX XIF2WORKFLOW_DESC ON WORKFLOW_DESC (LANGUAGE_ID ASC);

ALTER TABLE WORKFLOW_DESC ADD CONSTRAINT WORKFLOW_DESC_WORKFLOW FOREIGN KEY (WORKFLOW_ID)
REFERENCES WORKFLOW (WORKFLOW_ID) ON DELETE CASCADE;

ALTER TABLE WORKFLOW_DESC ADD CONSTRAINT WORKFLOW_DESC_LANGUAGE FOREIGN KEY (LANGUAGE_ID)
REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;

--------------   WORKFLOWSTEP   --------------
CREATE SEQUENCE SEQ_WORKFLOWSTEP
START WITH 1
INCREMENT BY 1;

CREATE TABLE WORKFLOWSTEP
(
WORKFLOWSTEP_ID NUMBER NOT NULL,
WORKFLOW_ID NUMBER NOT NULL,
STEPNUMBER NUMBER NOT NULL,
STEPTYPE NUMBER NOT NULL CONSTRAINT STEPTYPE_VALID1_1 CHECK (STEPTYPE IN (1,2,3,4)),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL,
ACTIVE VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD322 CHECK (ACTIVE IN ('Y','N'))
);

COMMENT ON COLUMN WORKFLOWSTEP.STEPTYPE IS '1 = dialog, 2 = text, 3 = url, 4 = report';

ALTER TABLE WORKFLOWSTEP ADD CONSTRAINT XPKWORKFLOWSTEP PRIMARY KEY (WORKFLOWSTEP_ID);

CREATE UNIQUE INDEX XAK1WORKFLOWSTEP ON WORKFLOWSTEP (WORKFLOW_ID ASC, STEPNUMBER ASC);

CREATE INDEX XIF1WORKFLOWSTEP ON WORKFLOWSTEP (WORKFLOW_ID ASC);

ALTER TABLE WORKFLOWSTEP ADD CONSTRAINT WORKFLOWSTEP_WORKFLOWID FOREIGN KEY (WORKFLOW_ID)
REFERENCES WORKFLOW (WORKFLOW_ID) ON DELETE CASCADE;

--------------   WORKFLOWSTEP_DESC   --------------
CREATE TABLE WORKFLOWSTEP_DESC
(
WORKFLOWSTEP_ID NUMBER NOT NULL,
LANGUAGE_ID NUMBER NOT NULL,
DESCRIPTION VARCHAR2(24) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE WORKFLOWSTEP_DESC ADD CONSTRAINT XPKWORKFLOWSTEP_DESC PRIMARY KEY (WORKFLOWSTEP_ID, LANGUAGE_ID);

CREATE INDEX XIF1WORKFLOWSTEP_DESC ON WORKFLOWSTEP_DESC (WORKFLOWSTEP_ID ASC);

CREATE INDEX XIF2WORKFLOWSTEP_DESC ON WORKFLOWSTEP_DESC (LANGUAGE_ID ASC);

ALTER TABLE WORKFLOWSTEP_DESC ADD CONSTRAINT WORKFLOWSTEP_DESC_WFSID FOREIGN KEY (WORKFLOWSTEP_ID)
REFERENCES WORKFLOWSTEP (WORKFLOWSTEP_ID) ON DELETE CASCADE;

ALTER TABLE WORKFLOWSTEP_DESC ADD CONSTRAINT WORKFLOWSTEP_DESC_LANGID FOREIGN KEY (LANGUAGE_ID)
REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;

--------------   WORKFLOWSTEPINST   --------------
CREATE TABLE WORKFLOWSTEPINST
(
WORKFLOWSTEP_ID NUMBER NOT NULL,
LANGUAGE_ID NUMBER NOT NULL,
INSTRUCTION VARCHAR2(512) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE WORKFLOWSTEPINST ADD CONSTRAINT XPKWORKFLOWSTEPINST PRIMARY KEY (WORKFLOWSTEP_ID, LANGUAGE_ID);

CREATE INDEX XIF1WORKFLOWSTEPINST ON WORKFLOWSTEPINST (WORKFLOWSTEP_ID ASC);

CREATE INDEX XIF2WORKFLOWSTEPINST ON WORKFLOWSTEPINST (LANGUAGE_ID ASC);

ALTER TABLE WORKFLOWSTEPINST ADD CONSTRAINT WORKFLOWSTEPINST_WFSID FOREIGN KEY (WORKFLOWSTEP_ID)
REFERENCES WORKFLOWSTEP (WORKFLOWSTEP_ID) ON DELETE CASCADE;

ALTER TABLE WORKFLOWSTEPINST ADD CONSTRAINT WORKFLOWSTEPINST_LANGID FOREIGN KEY (LANGUAGE_ID)
REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;

--------------   WORKFLOWSTEPTEXT   --------------
CREATE TABLE WORKFLOWSTEPTEXT
(
WORKFLOWSTEP_ID NUMBER NOT NULL,
LANGUAGE_ID NUMBER NOT NULL,
TEXT CLOB NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE WORKFLOWSTEPTEXT ADD CONSTRAINT XPKWORKFLOWSTEPTEXT PRIMARY KEY (WORKFLOWSTEP_ID, LANGUAGE_ID);

CREATE INDEX XIF1WORKFLOWSTEPTEXT ON WORKFLOWSTEPTEXT (WORKFLOWSTEP_ID ASC);

CREATE INDEX XIF2WORKFLOWSTEPTEXT ON WORKFLOWSTEPTEXT (LANGUAGE_ID ASC);

ALTER TABLE WORKFLOWSTEPTEXT ADD CONSTRAINT WORKFLOWSTEPTEXT_WFSID FOREIGN KEY (WORKFLOWSTEP_ID)
REFERENCES WORKFLOWSTEP (WORKFLOWSTEP_ID) ON DELETE CASCADE;

ALTER TABLE WORKFLOWSTEPTEXT ADD CONSTRAINT WORKFLOWSTEPTEXT_LANGID FOREIGN KEY (LANGUAGE_ID)
REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;

--------------   WORKFLOWSTEPURL   --------------
CREATE TABLE WORKFLOWSTEPURL
(
WORKFLOWSTEP_ID NUMBER NOT NULL,
URL VARCHAR2(2083) NOT NULL,
AUTOOPEN VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD323 CHECK (AUTOOPEN IN ('Y','N')),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE WORKFLOWSTEPURL ADD CONSTRAINT XPKWORKFLOWSTEPURL PRIMARY KEY (WORKFLOWSTEP_ID);

ALTER TABLE WORKFLOWSTEPURL ADD CONSTRAINT WORKFLOWSTEPURL_WFSID FOREIGN KEY (WORKFLOWSTEP_ID)
REFERENCES WORKFLOWSTEP (WORKFLOWSTEP_ID) ON DELETE CASCADE;

--------------   WORKFLOWSTEPDLG  --------------
CREATE TABLE WORKFLOWSTEPDLG
(
WORKFLOWSTEP_ID NUMBER NOT NULL,
DIALOG_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE WORKFLOWSTEPDLG ADD CONSTRAINT XPKWORKFLOWSTEPDLG PRIMARY KEY (WORKFLOWSTEP_ID);

CREATE INDEX XIF1WORKFLOWSTEPDLG ON WORKFLOWSTEPDLG (DIALOG_ID ASC);

ALTER TABLE WORKFLOWSTEPDLG ADD CONSTRAINT WORKFLOWSTEPDLG_WFSID FOREIGN KEY (WORKFLOWSTEP_ID)
REFERENCES WORKFLOWSTEP (WORKFLOWSTEP_ID) ON DELETE CASCADE;

ALTER TABLE WORKFLOWSTEPDLG ADD CONSTRAINT WORKFLOWSTEPDLG_DIALOG FOREIGN KEY (DIALOG_ID)
REFERENCES DIALOGS (DIALOG_ID) ON DELETE CASCADE;

--------------   WORKFLOWSTEPRPT   --------------
CREATE TABLE WORKFLOWSTEPRPT
(
WORKFLOWSTEP_ID NUMBER NOT NULL,
DIALOG_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE WORKFLOWSTEPRPT ADD CONSTRAINT XPKWORKFLOWSTEPRPT PRIMARY KEY (WORKFLOWSTEP_ID);

CREATE INDEX XIF1WORKFLOWSTEPRPT ON WORKFLOWSTEPRPT (DIALOG_ID ASC);

ALTER TABLE WORKFLOWSTEPRPT ADD CONSTRAINT WORKFLOWSTEPRPT_WFSID FOREIGN KEY (WORKFLOWSTEP_ID)
REFERENCES WORKFLOWSTEP (WORKFLOWSTEP_ID) ON DELETE CASCADE;

ALTER TABLE WORKFLOWSTEPRPT ADD CONSTRAINT WORKFLOWSTEPRPT_DIALOG FOREIGN KEY (DIALOG_ID)
REFERENCES DIALOGS (DIALOG_ID) ON DELETE CASCADE;

--------------   USERGROUPPERWORKFLOW   --------------
CREATE TABLE USERGROUPPERWORKFLOW
(
WORKFLOW_ID NUMBER NOT NULL,
USERGROUP_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE USERGROUPPERWORKFLOW ADD CONSTRAINT XPKUSERGROUPPERWORKFLOW PRIMARY KEY (WORKFLOW_ID, USERGROUP_ID);

CREATE INDEX XIF1USERGROUPPERWORKFLOW ON USERGROUPPERWORKFLOW (WORKFLOW_ID ASC);

CREATE INDEX XIF2USERGROUPPERWORKFLOW ON USERGROUPPERWORKFLOW (USERGROUP_ID ASC);

ALTER TABLE USERGROUPPERWORKFLOW ADD CONSTRAINT USERGROUPPERWORKFLOW_WFID FOREIGN KEY (WORKFLOW_ID)
REFERENCES WORKFLOW (WORKFLOW_ID) ON DELETE CASCADE;

ALTER TABLE USERGROUPPERWORKFLOW ADD CONSTRAINT USERGROUPPERWORKFLOW_UGID FOREIGN KEY (USERGROUP_ID)
REFERENCES USERGROUP (USERGROUP_ID) ON DELETE CASCADE;

--------------   BUSINESSUNITPERWORKFLOW   --------------
CREATE TABLE BUSINESSUNITPERWORKFLOW
(
WORKFLOW_ID NUMBER NOT NULL,
BUSINESSUNIT_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE BUSINESSUNITPERWORKFLOW ADD CONSTRAINT XPKBUSINESSUNITPERWORKFLOW PRIMARY KEY (WORKFLOW_ID, BUSINESSUNIT_ID);

CREATE INDEX XIF1BUSINESSUNITPERWORKFLOW ON BUSINESSUNITPERWORKFLOW (WORKFLOW_ID ASC);

CREATE INDEX XIF2BUSINESSUNITPERWORKFLOW ON BUSINESSUNITPERWORKFLOW (BUSINESSUNIT_ID ASC);

ALTER TABLE BUSINESSUNITPERWORKFLOW ADD CONSTRAINT BUSINESSUNITPERWORKFLOW_WFID FOREIGN KEY (WORKFLOW_ID)
REFERENCES WORKFLOW (WORKFLOW_ID) ON DELETE CASCADE;

ALTER TABLE BUSINESSUNITPERWORKFLOW ADD CONSTRAINT BUSINESSUNITPERWORKFLOW_UGID FOREIGN KEY (BUSINESSUNIT_ID)
REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;

