REM $Header: wfeventc.sql 26.10 2004/04/28 01:20:26 yohuang ship $ REM +======================================================================+ REM | Copyright (c) 2000 Oracle Corporation Redwood Shores, California, USA| REM | All rights reserved. | REM +======================================================================+ REM NAME REM wfeventc.sql - WorkFlow EVENT Manager system Create tables. REM DESCRIPTION REM Creates the tables associated with the Event Manager system REM MODIFICATION LOG: REM 05/2002 VSHANMUG Bug 2353079 - Added new column SECURITY_GROUP_ID REM to all the tables REM 09/2002 VARRAJAR Modified SECURITY_GROUP_ID to be varchar2 REM Bug 2558446 - Added new columns CUSTOMIZATION_LEVEL REM and LICENSED_FLAG REM 01/2003 VARRAJAR Modified CUSTOMIZASTION_LEVEL and LICENSED_FLAG REM to be NOT NULL REM 04/2003 VARRAJAR Added new table and indexes WF_BES_SUBSCRIBER_PINGS REM +======================================================================+ REM Connect to base account REM (autopatch will run all scripts in apps account) connect &1/&2; REM Continue in case of error where tables aready exist WHENEVER SQLERROR CONTINUE; -- In most cases, we should not limit the table to have initial extent 10k and -- next extent 10k. Use the default value from tablespace is fine. /* ** WF_EVENTS */ create table WF_EVENTS (GUID RAW(16) NOT NULL, -- PK, GUID NAME VARCHAR2(240) NOT NULL, -- UK TYPE VARCHAR2(8) NOT NULL, -- EVENT | GROUP STATUS VARCHAR2(8) NOT NULL, -- ENABLED | DISABLED GENERATE_FUNCTION VARCHAR2(240) , -- generate function JAVA_GENERATE_FUNC VARCHAR2(240) , -- Java Generate Function OWNER_NAME VARCHAR2(30) , -- owning program OWNER_TAG VARCHAR2(30) , -- owning program tag CUSTOMIZATION_LEVEL VARCHAR2(1) default 'L' NOT NULL, -- Customization Level LICENSED_FLAG VARCHAR2(1) default 'Y' NOT NULL, -- Licensed Flag SECURITY_GROUP_ID VARCHAR2(32) ); /* ** WF_EVENTS_TL */ create table WF_EVENTS_TL (GUID RAW(16) NOT NULL, -- PK, GUID LANGUAGE VARCHAR2(4) NOT NULL, -- language code DISPLAY_NAME VARCHAR2(80) NOT NULL, DESCRIPTION VARCHAR2(2000) , SOURCE_LANG VARCHAR2(4) NOT NULL, SECURITY_GROUP_ID VARCHAR2(32) ); /* ** WF_EVENT_GROUPS */ create table WF_EVENT_GROUPS (GROUP_GUID RAW(16) NOT NULL, -- PK, FK to WF_EVENTS, Group MEMBER_GUID RAW(16) NOT NULL, -- PK, FK to WF_EVENTS, Event SECURITY_GROUP_ID VARCHAR2(32) ) storage (initial 10k next 10k); /* ** WF_SYSTEMS */ create table WF_SYSTEMS (GUID RAW(16) NOT NULL, -- PK NAME VARCHAR2(30) NOT NULL, -- UK MASTER_GUID RAW(16), -- FK to WF_SYSTEMS DISPLAY_NAME VARCHAR2(80) NOT NULL, -- TL (on base table) DESCRIPTION VARCHAR2(240), -- TL (on base table) SECURITY_GROUP_ID VARCHAR2(32) ); /* ** WF_AGENTS */ create table WF_AGENTS (GUID RAW(16) NOT NULL, -- PK, GUID NAME VARCHAR2(30) NOT NULL, -- UK1 logical name SYSTEM_GUID RAW(16) NOT NULL, -- FK to WF_SYSTEMS.GUID PROTOCOL VARCHAR2(30), -- AQ, SMTP, custom... ADDRESS VARCHAR2(240), -- QUEUE_HANDLER VARCHAR2(240), -- queue handler package name JAVA_QUEUE_HANDLER VARCHAR2(240), -- Java Queue Handler QUEUE_NAME VARCHAR2(80), -- DIRECTION VARCHAR2(8) NOT NULL, -- IN | OUT | ANY STATUS VARCHAR2(8) NOT NULL, -- ENABLED | DISABLED TYPE VARCHAR2(8) DEFAULT 'AGENT' NOT NULL, -- AGENT | GROUP DISPLAY_NAME VARCHAR2(80) NOT NULL, -- TL (on base table) DESCRIPTION VARCHAR2(240), -- TL (on base table) SECURITY_GROUP_ID VARCHAR2(32) ); /* ** WF_EVENT_SUBSCRIPTIONS */ create table WF_EVENT_SUBSCRIPTIONS (GUID RAW(16) NOT NULL, -- PK, GUID SYSTEM_GUID RAW(16) NOT NULL, -- FK - WF_SYSTEMS.GUID SOURCE_TYPE VARCHAR2(8) NOT NULL, -- LOCAL | EXTERNAL | ANY SOURCE_AGENT_GUID RAW(16), -- FK to WF_AGENTS EVENT_FILTER_GUID RAW(16) NOT NULL, -- FK to WF_EVENTS PHASE NUMBER, -- order in which subs are executed STATUS VARCHAR2(8) NOT NULL, -- ENABLED | DISABLED RULE_DATA VARCHAR2(8) NOT NULL, -- KEY | MESSAGE OUT_AGENT_GUID RAW(16), -- outbound agent, if sending TO_AGENT_GUID RAW(16), -- destination agent, if sending PRIORITY NUMBER, -- 1 - 100 priority for message RULE_FUNCTION VARCHAR2(240), -- code to run STANDARD_TYPE VARCHAR2(30), STANDARD_CODE VARCHAR2(30) , JAVA_RULE_FUNC VARCHAR2(240) , -- Java Rule Function ON_ERROR_CODE VARCHAR2(30) , ACTION_CODE VARCHAR2(30), WF_PROCESS_TYPE VARCHAR2(30), -- workflow process type WF_PROCESS_NAME VARCHAR2(30), -- workflow process name PARAMETERS VARCHAR2(4000), -- other parameters OWNER_NAME VARCHAR2(30), -- owning program OWNER_TAG VARCHAR2(30), -- owning program tag CUSTOMIZATION_LEVEL VARCHAR2(1) default 'L' NOT NULL, -- Customization Level LICENSED_FLAG VARCHAR2(1) default 'Y' NOT NULL, -- Licensed Flag EXPRESSION VARCHAR2(4000), -- sql rule to be evaluated DESCRIPTION VARCHAR2(240), -- TL (on base table) SECURITY_GROUP_ID VARCHAR2(32), INVOCATION_ID NUMBER, MAP_CODE VARCHAR2(30) ); /* ** WF_AGENT_GROUPS */ create table WF_AGENT_GROUPS ( GROUP_GUID RAW(16) NOT NULL, MEMBER_GUID RAW(16) NOT NULL, SECURITY_GROUP_ID VARCHAR2(32) ) storage (initial 10k next 10k); /* ** WF_BES_SUBSCRIBER_PINGS */ create table WF_BES_SUBSCRIBER_PINGS ( PING_NUMBER NUMBER NOT NULL, PING_TIME DATE NOT NULL, QUEUE_NAME VARCHAR2(30) NOT NULL, SUBSCRIBER_NAME VARCHAR2(30) NOT NULL, STATUS VARCHAR2(30) NOT NULL, ACTION_TIME DATE NOT NULL, SECURITY_GROUP_ID VARCHAR2(32) ) storage(initial 4K next 32K); /* * Sequences */ CREATE SEQUENCE WF_CONTROL_JMS_SUBSCRIBER_ID_S NOMAXVALUE NOMINVALUE NOCYCLE NOCACHE / CREATE SEQUENCE WF_BES_PING_NUMBER_S NOMAXVALUE NOMINVALUE NOCYCLE NOCACHE / commit; exit;