REM $Header: wfengc.sql 26.10 2003/11/12 13:40:44 vshanmug ship $
REM +======================================================================+
REM | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM
REM NAME
REM     wfengc.sql  -  WorkFlow ENGine table Create
REM
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     03.12.2003 VARRAJAR Modified all role/user related columns to be
REM                         VARCHAR2(320)
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;


/*
** WF_ACTIVITIES
*/
create table WF_ACTIVITIES
(
    ITEM_TYPE      varchar2(8)  not null,
    NAME           varchar2(30) not null,
    VERSION        number       not null,
    TYPE           varchar2(8)  not null,
    RERUN          varchar2(8)  not null,
    EXPAND_ROLE    varchar2(1)  default 'N' not null,
    PROTECT_LEVEL  number       default 1000 not null,
    CUSTOM_LEVEL   number       default 0 not null,
    BEGIN_DATE     date         not null,
    RUNNABLE_FLAG  varchar2(1)  default 'Y' not null,
    ERROR_ITEM_TYPE varchar2(8) default 'WFERROR' not null,
    END_DATE       date,
    FUNCTION_TYPE  varchar2(30),
    FUNCTION       varchar2(240),
    RESULT_TYPE    varchar2(30),
    COST           number,
    READ_ROLE      varchar2(320),
    WRITE_ROLE     varchar2(320),
    EXECUTE_ROLE   varchar2(320),
    ICON_NAME      varchar2(30),
    MESSAGE        varchar2(30),
    ERROR_PROCESS  varchar2(30),
    EVENT_NAME     varchar2(240),
    DIRECTION      varchar2(30),
    SECURITY_GROUP_ID varchar2(32)
);

create table WF_ACTIVITIES_TL
(
    ITEM_TYPE      varchar2(8)    not null,
    NAME           varchar2(30)   not null,
    VERSION        number         not null,
    DISPLAY_NAME   varchar2(80)   not null,
    LANGUAGE       varchar2(4)    not null,
    SOURCE_LANG    varchar2(4)    not null,
    PROTECT_LEVEL  number         default 1000 not null,
    CUSTOM_LEVEL   number         default 0 not null,
    DESCRIPTION    varchar2(240),
    SECURITY_GROUP_ID varchar2(32)
);

/*
** WF_ACTIVITY_ATTRIBUTES
*/
create table WF_ACTIVITY_ATTRIBUTES
(
    ACTIVITY_ITEM_TYPE varchar2(8)    not null,
    ACTIVITY_NAME      varchar2(30)   not null,
    ACTIVITY_VERSION   number         not null,
    NAME               varchar2(30)   not null,
    SEQUENCE           number         not null,
    TYPE               varchar2(8)    not null,
    VALUE_TYPE         varchar2(8)    default 'CONSTANT' not null,
    PROTECT_LEVEL      number         default 1000 not null,
    CUSTOM_LEVEL       number         default 0 not null,
    SUBTYPE            varchar2(8),
    FORMAT             varchar2(240),
    TEXT_DEFAULT       varchar2(4000),
    NUMBER_DEFAULT     number,
    DATE_DEFAULT       date,
    SECURITY_GROUP_ID  varchar2(32)
);

create table WF_ACTIVITY_ATTRIBUTES_TL
(
    ACTIVITY_ITEM_TYPE varchar2(8)    not null,
    ACTIVITY_NAME      varchar2(30)   not null,
    ACTIVITY_VERSION   number         not null,
    NAME               varchar2(30)   not null,
    LANGUAGE           varchar2(4)    not null,
    SOURCE_LANG        varchar2(4)    not null,
    DISPLAY_NAME       varchar2(80)   not null,
    PROTECT_LEVEL      number         default 1000 not null,
    CUSTOM_LEVEL       number         default 0 not null,
    DESCRIPTION        varchar2(240),
    SECURITY_GROUP_ID  varchar2(32)
);

/*
** WF_PROCESS_ACTIVITIES
*/
create table WF_PROCESS_ACTIVITIES
(
    PROCESS_ITEM_TYPE  varchar2(8)  not null,
    PROCESS_NAME       varchar2(30) not null,
    PROCESS_VERSION    number       not null,
    ACTIVITY_ITEM_TYPE varchar2(8)  not null,
    ACTIVITY_NAME      varchar2(30) not null,
    INSTANCE_ID        number       not null,
    INSTANCE_LABEL     varchar2(30) not null,
    PERFORM_ROLE_TYPE  varchar2(8)  default 'CONSTANT' not null,
    PROTECT_LEVEL      number       default 1000 not null,
    CUSTOM_LEVEL       number       default 0 not null,
    START_END          varchar2(8),
    DEFAULT_RESULT     varchar2(30),
    ICON_GEOMETRY      varchar2(2000),
    PERFORM_ROLE       varchar2(320),
    USER_COMMENT       varchar2(240),
    SECURITY_GROUP_ID  varchar2(32)
);

create sequence WF_PROCESS_ACTIVITIES_S
    increment by 1
    start with 1;


/*
** WF_ACTIVITY_ATTR_VALUES
*/
create table WF_ACTIVITY_ATTR_VALUES
(
    PROCESS_ACTIVITY_ID number       not null,
    NAME                varchar2(30) not null,
    VALUE_TYPE          varchar2(8)  default 'CONSTANT' not null,
    PROTECT_LEVEL       number       default 1000 not null,
    CUSTOM_LEVEL        number       default 0 not null,
    TEXT_VALUE          varchar2(4000),
    NUMBER_VALUE        number,
    DATE_VALUE          date,
    SECURITY_GROUP_ID  varchar2(32)
);

/*
** WF_ACTIVITY_TRANSITIONS
*/
create table WF_ACTIVITY_TRANSITIONS
(
    FROM_PROCESS_ACTIVITY  number       not null,
    RESULT_CODE            varchar2(30) not null,
    TO_PROCESS_ACTIVITY    number       not null,
    PROTECT_LEVEL          number       default 1000 not null,
    CUSTOM_LEVEL           number       default 0 not null,
    ARROW_GEOMETRY         varchar2(2000),
    SECURITY_GROUP_ID      varchar2(32)
);

/*
** WF_ITEMS
*/
create table WF_ITEMS
(
    ITEM_TYPE             varchar2(8)   not null,
    ITEM_KEY              varchar2(240) not null,
    ROOT_ACTIVITY         varchar2(30)  not null,
    ROOT_ACTIVITY_VERSION number        not null,
    USER_KEY              varchar2(240),
    OWNER_ROLE            varchar2(320),
    PARENT_ITEM_TYPE      varchar2(8),
    PARENT_ITEM_KEY       varchar2(240),
    PARENT_CONTEXT        varchar2(2000),
    BEGIN_DATE            date          not null,
    END_DATE              date,
    HA_MIGRATION_FLAG     varchar2(1),
    SECURITY_GROUP_ID     varchar2(32)
);

create sequence WF_ERROR_PROCESSES_S;


/*
** WF_ITEM_ATTRIBUTE_VALUES
*/
create table WF_ITEM_ATTRIBUTE_VALUES
(
    ITEM_TYPE       varchar2(8)   not null,
    ITEM_KEY        varchar2(240) not null,
    NAME            varchar2(30)  not null,
    TEXT_VALUE      varchar2(4000),
    NUMBER_VALUE    number,
    DATE_VALUE      date,
    EVENT_VALUE     wf_event_t,
    SECURITY_GROUP_ID varchar2(32)
)
pctfree 25
storage (
  freelists 17
  initial    4K
  next     512K
  maxextents   50
);

/*
** WF_ITEM_ACTIVITIY_STATUSES
*/
create table WF_ITEM_ACTIVITY_STATUSES
(
    ITEM_TYPE            varchar2(8)   not null,
    ITEM_KEY             varchar2(240) not null,
    PROCESS_ACTIVITY     number        not null,
    ACTIVITY_STATUS      varchar2(8),
    ACTIVITY_RESULT_CODE varchar2(30),
    ASSIGNED_USER        varchar2(320),
    NOTIFICATION_ID      number,
    BEGIN_DATE           date,
    END_DATE             date,
    DUE_DATE             date,
    EXECUTION_TIME       number,
    OUTBOUND_QUEUE_ID    raw(16),
    ERROR_NAME           varchar2(30),
    ERROR_MESSAGE        varchar2(2000),
    ERROR_STACK          varchar2(4000),
    SECURITY_GROUP_ID    varchar2(32),
    ACTION               varchar2(30),
    PERFORMED_BY         varchar2(320)
);

/*
** WF_ITEM_ACTIVITY_STATUSES_H
*/
create table WF_ITEM_ACTIVITY_STATUSES_H
(
    ITEM_TYPE            varchar2(8)   not null,
    ITEM_KEY             varchar2(240) not null,
    PROCESS_ACTIVITY     number        not null,
    ACTIVITY_STATUS      varchar2(8),
    ACTIVITY_RESULT_CODE varchar2(30),
    ASSIGNED_USER        varchar2(320),
    NOTIFICATION_ID      number,
    BEGIN_DATE           date,
    END_DATE             date,
    DUE_DATE             date,
    EXECUTION_TIME       number,
    OUTBOUND_QUEUE_ID    raw(16),
    ERROR_NAME           varchar2(30),
    ERROR_MESSAGE        varchar2(2000),
    ERROR_STACK          varchar2(4000),
    SECURITY_GROUP_ID    varchar2(32),
    ACTION               varchar2(30),
    PERFORMED_BY         varchar2(320)
)
pctfree 25
storage (
  freelists 17
  initial   4K
  next      4M
  maxextents   50
);


create table WF_QUEUES 
(
   PROTOCOL             VARCHAR2(10),
   INBOUND_OUTBOUND	VARCHAR2(10),
   DESCRIPTION		VARCHAR2(240),
   QUEUE_COUNT		NUMBER,
   DISABLE_FLAG		VARCHAR2(1),
   SECURITY_GROUP_ID    varchar2(32)
);

CREATE TABLE WF_DIG_CAS (
CA_NAME VARCHAR2(256) NOT NULL, 
CA_URL VARCHAR2(2000), 
SECURITY_GROUP_ID VARCHAR2(32));

CREATE TABLE WF_DIG_CERTS (
CERT CLOB NOT NULL, 
CERT_ID NUMBER NOT NULL, 
CERT_TYPE VARCHAR2(30) NOT NULL, 
PARENT_CERT_ID NUMBER, 
OWNER_ID VARCHAR2(320), 
OWNER_DOMAIN VARCHAR2(20), 
VALID VARCHAR2(1) NOT NULL,
SOT_FLAG VARCHAR2(1) NOT NULL, 
INTERMEDIATE_FLAG VARCHAR2(1) NOT NULL,
FINGERPRINT VARCHAR2(64), 
EXPIRE DATE, 
SECURITY_GROUP_ID VARCHAR2(32));

CREATE TABLE WF_DIG_CRLS (
CRL_ID NUMBER NOT NULL, 
ISSUE_DATE DATE NOT NULL, 
CRL_DATA CLOB, 
SECURITY_GROUP_ID VARCHAR2(32));

CREATE TABLE WF_DIG_SIGS (
SIG_ID NUMBER NOT NULL, 
CERT_ID NUMBER,
 SIG_POLICY VARCHAR2(30), 
SIG_FLAVOR VARCHAR2(30), 
SIG_OBJ_TYPE VARCHAR2(20) NOT NULL, 
SIG_OBJ_ID VARCHAR2(30) NOT NULL, 
PLAINTEXT CLOB,
 SIGNATURE CLOB, 
REQUESTED_SIGNER_TYPE VARCHAR2(20), 
REQUESTED_SIGNER_ID VARCHAR2(320), 
STATUS NUMBER, 
CREATION_DATE DATE, 
SIGNED_DATE DATE,
 VERIFIED_DATE DATE, 
LAST_VALIDATION_ATTEMPT DATE, 
VALIDATED_COMPLETE_DATE DATE, 
ERRBUF VARCHAR2(2000), 
ERRSTACK VARCHAR2(2000), 
SECURITY_GROUP_ID VARCHAR2(32));

CREATE TABLE WF_DIG_SIG_SPI_FLAVORS (
BSR_SPI VARCHAR2(240),
 VERIFY_SPI VARCHAR2(240), 
VALIDATE_SPI VARCHAR2(240), 
CERT_MAPPER VARCHAR2(240), 
BSR_FWK_CO VARCHAR2(240), 
FLAVOR VARCHAR2(30) NOT NULL, 
SECURITY_GROUP_ID VARCHAR2(32),
 VALIDATOR_STORE                          VARCHAR2(240),
 VALIDATION_MODE                          VARCHAR2(240),
 SIGNATURE_FORMAT                         VARCHAR2(20),
 SIGNATURE_MODE                           VARCHAR2(20));

Create table  wf_signature_policies (
SIG_POLICY   VARCHAR2(255) Not Null,
SIG_REQUIRED VARCHAR2(1) Not Null,
FWK_SIG_FLAVOR VARCHAR2(255),
EMAIL_SIG_FLAVOR VARCHAR2(255),
RENDER_HINT      VARCHAR2(20),
DEFAULT_POLICY   VARCHAR2(1));


commit;
exit;