CREATE TABLE WORKFLOWSERVERSTATS (

ID RAW(16),

TYPE NVARCHAR2(256) NOT NULL,

DATEFROM DATE NOT NULL,

DATETO DATE NOT NULL,

DURATION NUMBER NOT NULL,

ISSUCCESS CHAR(1 BYTE) NOT NULL,

PROCESSID RAW(16) NULL,

CONSTRAINT PK_WORKFLOWSERVERSTATS PRIMARY KEY (ID) USING INDEX STORAGE ( INITIAL 64K NEXT 1M MAXEXTENTS UNLIMITED ))

LOGGING;

CREATE INDEX IDX_WORKFLOWSERVERSTATS_PROCESSID ON WORKFLOWSERVERSTATS (PROCESSID)

LOGGING;

CREATE TABLE WORKFLOWSERVERPROCESSHISTORY (

ID RAW(16),

PROCESSID RAW(16) NOT NULL,

IDENTITYID NVARCHAR2(256) NULL,

ALLOWEDTOEMPLOYEENAMES NCLOB NULL,

TRANSITIONTIME DATE NULL,

"Order" NUMBER GENERATED ALWAYS AS IDENTITY,

INITIALSTATE NVARCHAR2(1024) NOT NULL,

DESTINATIONSTATE NVARCHAR2(1024) NOT NULL,

COMMAND NVARCHAR2(1024) NOT NULL,

CONSTRAINT PK_WORKFLOWSERVERPROCESSHISTORY PRIMARY KEY (ID) USING INDEX STORAGE ( INITIAL 64K NEXT 1M MAXEXTENTS UNLIMITED ))

LOGGING;

CREATE INDEX IDX_WORKFLOWSERVERPROCESSHISTORY_PROCESSID ON WORKFLOWSERVERPROCESSHISTORY (PROCESSID)

LOGGING;

ALTER TABLE WORKFLOWINBOX MODIFY IDENTITYID NVARCHAR2(1024);

CREATE OR REPLACE TYPE TREPORTBYSCHEMEENTRY AS OBJECT (

CODE VARCHAR2(1024),

PROCESSCOUNT NUMBER,

TRANSITIONCOUNT NUMBER

);

CREATE OR REPLACE TYPE TREPORTBYSCHEME AS TABLE OF TREPORTBYSCHEMEENTRY;

CREATE OR REPLACE FUNCTION WORKFLOWREPORTBYSCHEMES

RETURN TREPORTBYSCHEME

ResultReport TREPORTBYSCHEME;

SELECT

TREPORTBYSCHEMEENTRY(ws.CODE,

(SELECT COUNT(inst.ID) FROM WORKFLOWPROCESSINSTANCE inst

LEFT JOIN WORKFLOWPROCESSSCHEME ps on ps.ID = inst.SCHEMEID

WHERE coalesce(ps.ROOTSCHEMECODE, ps.SCHEMECODE) = ws.CODE),

(SELECT COUNT(history.ID) FROM WORKFLOWPROCESSTRANSITIONH history

LEFT JOIN WORKFLOWPROCESSINSTANCE inst on history.PROCESSID = inst.ID

LEFT JOIN WORKFLOWPROCESSSCHEME ps on ps.ID = inst.SCHEMEID

WHERE coalesce(ps.ROOTSCHEMECODE, ps.SCHEMECODE) = ws.CODE))

BULK COLLECT INTO ResultReport

FROM WORKFLOWSCHEME ws;

RETURN ResultReport;

end;

CREATE OR REPLACE TYPE TREPORTBYTRANSITIONSENTRY AS OBJECT (

"Date" DATE,

SCHEMECODE VARCHAR2(1024),

COUNT NUMBER

);

CREATE OR REPLACE TYPE TREPORTBYTRANSITIONS AS TABLE OF TREPORTBYTRANSITIONSENTRY;

CREATE OR REPLACE TYPE TREPORTENTRY AS OBJECT (df DATE, de DATE);

CREATE OR REPLACE TYPE TREPORT AS TABLE OF TREPORTENTRY;

CREATE OR REPLACE FUNCTION WORKFLOWREPORTBYTRANSITIONS(datefrom DATE, dateto DATE, period NUMBER)

RETURN TREPORTBYTRANSITIONS

ResultReport TREPORTBYTRANSITIONS;

ReportTmp TREPORT := TREPORT();

curdate DATE;

dateend DATE;

IF datefrom > dateto THEN

RETURN NULL;

END IF;

curdate := TRUNC(datefrom, 'MM');

if period >= 1 then

curdate := curdate + (EXTRACT(DAY FROM datefrom) - 1);

end if;

if period >= 2 then

curdate := curdate + TO_NUMBER(TO_CHAR(datefrom, 'HH24')) / 24;

end if;

if period >= 3 then

curdate := curdate + TO_NUMBER(TO_CHAR(datefrom, 'MI')) / (24 * 60);

end if;

if period >= 4 then

curdate := curdate + TO_NUMBER(TO_CHAR(datefrom, 'SS')) / (24 * 60 * 60);

end if;

WHILE curdate <= dateto LOOP

dateend := CASE

WHEN period = 0 then ADD_MONTHS(curdate, 1)

WHEN period = 1 THEN curdate + 1

WHEN period = 2 THEN curdate + 1 / 24

WHEN period = 3 THEN curdate + 1 / (24 * 60)

WHEN period = 4 THEN curdate + 1 / (24 * 60 * 60)

end;

ReportTmp.EXTEND(1);

ReportTmp(ReportTmp.LAST) := TREPORTENTRY(curdate, dateend);

curdate := dateend;

END LOOP;

SELECT TREPORTBYTRANSITIONSENTRY(

p.df,

scheme.CODE,

coalesce(COUNT(history.ID), 0))

BULK COLLECT INTO ResultReport

FROM TABLE(ReportTmp) p

LEFT JOIN WORKFLOWSCHEME scheme on 1=1

LEFT JOIN WORKFLOWPROCESSSCHEME ps on scheme.CODE = coalesce(ps.ROOTSCHEMECODE, ps.SCHEMECODE)

LEFT JOIN WORKFLOWPROCESSINSTANCE inst on ps.ID = inst.SCHEMEID

LEFT JOIN WORKFLOWPROCESSTRANSITIONH history on history.PROCESSID = inst.ID AND history.TRANSITIONTIME >= p.df AND history.TRANSITIONTIME < p.de

GROUP BY p.df, scheme.CODE

ORDER BY p.df, scheme.CODE;

RETURN ResultReport;

end;

CREATE OR REPLACE TYPE TREPORTBYSTATSENTRY AS OBJECT ( "Date" timestamp,

SCHEMECODE NVARCHAR2(1024),

TYPE NVARCHAR2(1024),

ISSUCCESS CHAR(1 BYTE),

COUNT NUMBER,

DURATIONAVG NUMBER,

DURATIONMIN NUMBER,

DURATIONMAX NUMBER

);

CREATE OR REPLACE TYPE TREPORTBYSTATS AS TABLE OF TREPORTBYSTATSENTRY;

CREATE OR REPLACE TYPE TCODES AS TABLE OF NVARCHAR2(1024);

CREATE OR REPLACE TYPE TSTATSUCCESS AS TABLE OF CHAR(1 BYTE);

CREATE OR REPLACE FUNCTION WORKFLOWREPORTBYSTATS(datefrom DATE, dateto DATE, period NUMBER)

RETURN TREPORTBYSTATS

ResultReport TREPORTBYSTATS;

curdate DATE;

dateend DATE;

ReportTmp TREPORT := TREPORT();

SchemesTmp TCODES;

TypesTmp TCODES;

SuccessesTmp TSTATSUCCESS := TSTATSUCCESS(0, 1);

IF datefrom > dateto THEN

RETURN NULL;

END IF;

curdate := TRUNC(datefrom, 'MM');

if period >= 1 then

curdate := curdate + (EXTRACT(DAY FROM datefrom) - 1);

end if;

if period >= 2 then

curdate := curdate + TO_NUMBER(TO_CHAR(datefrom, 'HH24')) / 24;

end if;

if period >= 3 then

curdate := curdate + TO_NUMBER(TO_CHAR(datefrom, 'MI')) / (24 * 60);

end if;

if period >= 4 then

curdate := curdate + TO_NUMBER(TO_CHAR(datefrom, 'SS')) / (24 * 60 * 60);

end if;

WHILE curdate <= dateto LOOP

dateend := CASE

WHEN period = 0 then ADD_MONTHS(curdate, 1)

WHEN period = 1 THEN curdate + 1

WHEN period = 2 THEN curdate + 1 / 24

WHEN period = 3 THEN curdate + 1 / (24 * 60)

WHEN period = 4 THEN curdate + 1 / (24 * 60 * 60)

end;

ReportTmp.EXTEND(1);

ReportTmp(ReportTmp.LAST) := TREPORTENTRY(curdate, dateend);

curdate := dateend;

END LOOP;

SELECT DISTINCT coalesce(ps.ROOTSCHEMECODE, ps.SCHEMECODE)

BULK COLLECT into SchemesTmp

FROM WORKFLOWSERVERSTATS stats

LEFT JOIN WORKFLOWPROCESSINSTANCE inst on inst.ID = stats.PROCESSID

LEFT JOIN WORKFLOWPROCESSSCHEME ps on inst.SCHEMEID = ps.ID

WHERE DATEFROM >= datefrom AND DATEFROM < dateto;

SELECT DISTINCT stats.TYPE

BULK COLLECT into TypesTmp

FROM WORKFLOWSERVERSTATS stats

WHERE stats.DATEFROM >= datefrom AND stats.DATEFROM < dateto;

SELECT TREPORTBYSTATSENTRY(

p.df,

scheme.column_value,

types.column_value,

success.column_value,

coalesce(COUNT(stats.ID), 0),

coalesce(AVG(stats.DURATION), 0),

coalesce(MIN(stats.DURATION), 0),

coalesce(MAX(stats.DURATION), 0))

BULK COLLECT INTO ResultReport

FROM TABLE(ReportTmp) p

LEFT JOIN TABLE(SchemesTmp) scheme on 1=1

LEFT JOIN TABLE(TypesTmp) types on 1=1

LEFT JOIN TABLE(SuccessesTmp) success on 1=1

LEFT JOIN WORKFLOWSERVERSTATS stats on stats.TYPE = types.column_value AND stats.ISSUCCESS = success.column_value AND stats.DATEFROM >= p.df AND stats.DATEFROM < p.de

LEFT JOIN WORKFLOWPROCESSINSTANCE inst on stats.PROCESSID = inst.ID

LEFT JOIN WORKFLOWPROCESSSCHEME ps on ps.ID = inst.SCHEMEID AND scheme.column_value = coalesce(ps.ROOTSCHEMECODE, ps.SCHEMECODE)

GROUP BY p.df, scheme.column_value, types.column_value, success.column_value

ORDER BY p.df, scheme.column_value, types.column_value, success.column_value;

RETURN ResultReport;

end;

ALTER TABLE WORKFLOWSCHEME ADD DELETEFINALIZED CHAR(1 BYTE) DEFAULT 0 NOT NULL;

ALTER TABLE WORKFLOWSCHEME ADD DONTFILLINDOX CHAR(1 BYTE) DEFAULT 0 NOT NULL;

ALTER TABLE WORKFLOWSCHEME ADD DONTPREEXECUTE CHAR(1 BYTE) DEFAULT 0 NOT NULL;

ALTER TABLE WORKFLOWSCHEME ADD AUTOSTART CHAR(1 BYTE) DEFAULT 0 NOT NULL;

ALTER TABLE WORKFLOWSCHEME ADD DEFAULTFORM NVARCHAR2(1024) NULL;

CREATE TABLE WORKFLOWSERVERLOGS (

ID RAW(16),

MESSAGE NCLOB NOT NULL,

MESSAGETEMPLATE NCLOB NOT NULL,

TIMESTAMP DATE NOT NULL,

EXCEPTION NCLOB NULL,

PROPERTIESJSON NCLOB NULL,

LOGLEVEL NUMBER(3) NOT NULL,

RUNTIMEID NVARCHAR2(900) NOT NULL,

CONSTRAINT PK_WORKFLOWSERVERLOGS PRIMARY KEY (ID) USING INDEX STORAGE ( INITIAL 64K NEXT 1M MAXEXTENTS UNLIMITED ))

LOGGING;

CREATE INDEX IDX_WORKFLOWSERVERLOGS_TIMESTAMP ON WORKFLOWSERVERLOGS (TIMESTAMP)

LOGGING;

CREATE INDEX IDX_WORKFLOWSERVERLOGS_LEVEL ON WORKFLOWSERVERLOGS (LOGLEVEL)

LOGGING;

CREATE INDEX IDX_RUNTIMEID_LEVEL ON WORKFLOWSERVERLOGS (RUNTIMEID)

LOGGING;

CREATE TABLE WORKFLOWSERVERUSER(

ID RAW(16),

NAME NVARCHAR2(256) NOT NULL,

EMAIL NVARCHAR2(256) NULL,

PHONE NVARCHAR2(256) NULL,

ISLOCKED CHAR(1 BYTE) DEFAULT 0 NOT NULL,

EXTERNALID NVARCHAR2(1024) NULL,

LOCKFLAG RAW(16) NOT NULL,

TENANTID NVARCHAR2(1024) NULL,

ROLES NCLOB NULL,

EXTENSIONS NCLOB NULL,

CONSTRAINT PK_WORKFLOWSERVERUSER PRIMARY KEY (ID) USING INDEX STORAGE ( INITIAL 64 K NEXT 1 M MAXEXTENTS UNLIMITED )

)

LOGGING;

CREATE TABLE WORKFLOWSERVERUSERCREDENTIAL(

ID RAW(16),

PASSWORDHASH NVARCHAR2(128) NULL,

PASSWORDSALT NVARCHAR2(128) NULL,

USERID RAW(16) NOT NULL,

LOGIN NVARCHAR2(256) NOT NULL,

AUTHTYPE NUMBER(3) NOT NULL,

TENANTID NVARCHAR2(1024) NULL,

EXTERNALPROVIDERNAME NVARCHAR2(128) NULL,

CONSTRAINT FK_WORKFLOWSERVERUSER_WORKFLOWSERVERUSERCREDENTIAL FOREIGN KEY (USERID)

REFERENCES WORKFLOWSERVERUSER (ID) ON DELETE CASCADE,

CONSTRAINT PK_WORKFLOWSERVERUSERCREDENTIAL PRIMARY KEY (ID) USING INDEX STORAGE ( INITIAL 64 K NEXT 1 M MAXEXTENTS UNLIMITED )

)

LOGGING;

CREATE INDEX IX_WORKFLOWSERVERUSERCREDENTIAL_USERID ON WORKFLOWSERVERUSERCREDENTIAL (USERID);

CREATE INDEX IX_WORKFLOWSERVERUSERCREDENTIAL_LOGIN ON WORKFLOWSERVERUSERCREDENTIAL (LOGIN);

CREATE TABLE WORKFLOWSERVERPROCESSLOGS(

ID RAW(16),

PROCESSID RAW(16) NOT NULL,

CREATEDON TIMESTAMP default current_timestamp NOT NULL,

TIMESTAMP TIMESTAMP default current_timestamp NOT NULL,

SCHEMECODE NVARCHAR2(256) NULL,

MESSAGE NCLOB DEFAULT ' ' NOT NULL,

PROPERTIES NCLOB DEFAULT ' ' NOT NULL,

EXCEPTION NCLOB DEFAULT ' ' NOT NULL,

TENANTID NVARCHAR2(1024) NULL,

CONSTRAINT PK_WORKFLOWSERVERPROCESSLOGS PRIMARY KEY (ID) USING INDEX STORAGE ( INITIAL 64 K NEXT 1 M MAXEXTENTS UNLIMITED )

)

LOGGING;

CREATE INDEX IDX_WORKFLOWSERVERPROCESSLOGS_TIMESTAMP ON WORKFLOWSERVERPROCESSLOGS (TIMESTAMP);

CREATE INDEX IDX_WORKFLOWSERVERPROCESSLOGS_CREATEDON ON WORKFLOWSERVERPROCESSLOGS (CREATEDON);

CREATE INDEX IDX_WORKFLOWSERVERPROCESSLOGS_PROCESSID ON WORKFLOWSERVERPROCESSLOGS (PROCESSID);

COMMIT;