REM dbdrv: none
REM $Header: wf260upg.sql 26.56 2005/01/21 09:36:11 vshanmug ship $
REM +======================================================================+
REM | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM   NAME
REM       wf260upg.sql - WorkFlow 2.6.0 Upgrade Script
REM   DESCRIPTION
REM       Upgrade Workflow 2.5.0 -> Workflow 2.6.0 data model.
REM   USAGE
REM       sqlplus apps/apps @wf260upg applsys fnd
REM   NOTES
REM   - Add new columns for WF_LOCAL tables
REM   - Partitioning WF_LOCAL tables.
REM   - Add new columns for Business event system
REM             WF_ACTIVITIES
REM   - Modify view for Business event system
REM             WF_ACTIVITIES_VL
REM   - Denormalization of notification (add new columns)
REM             WF_NOTIFICATIONS
REM   - Included security_group_id in table definitions as VARCHAR2(32)
REM		made the column nullable
REM   - Added new columns to WF_EVENT_SUBSCRIPTIONS
REM   - Added new table and indexes WF_BES_SUBSCRIBER_PINGS
REM   - Modified all role/user related columns to be VARCHAR2(320)
REM
REM +======================================================================+

REM Connect to base account
REM (autopatch will run all scripts in apps account)
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

connect &1/&2;

REM Continue if errors so that script is re-runnable
WHENEVER SQLERROR CONTINUE

DEFINE hdr = "$Header: wf260upg.sql 26.56 2005/01/21 09:36:11 vshanmug ship $"

REM
REM Alter Tables
REM


REM
REM WF_LOCAL tables
REM

alter table WF_LOCAL_USERS add
(
    ORIG_SYSTEM         varchar2(30) DEFAULT 'WF_LOCAL_USERS' NOT NULL,
    ORIG_SYSTEM_ID      number DEFAULT 0 NOT NULL  
);
alter table WF_LOCAL_ROLES add
(
    ORIG_SYSTEM         varchar2(30) DEFAULT 'WF_LOCAL_ROLES' NOT NULL,
    ORIG_SYSTEM_ID      number DEFAULT 0 NOT NULL 
);

REM 
REM Create WF_LOCAL_ROLES_TL Table
REM
create table WF_LOCAL_ROLES_TL ( 
      NAME                    varchar2(320)   not null, 
      DISPLAY_NAME            varchar2(360)   not null, 
      DESCRIPTION             varchar2(1000), 
      ORIG_SYSTEM             varchar2(30)    DEFAULT 'WF_LOCAL_ROLES' NOT NULL,
      ORIG_SYSTEM_ID          number          DEFAULT 0 NOT NULL,
      PARTITION_ID            NUMBER DEFAULT 0, 
      LANGUAGE                varchar2(30) );



REM
REM Creating WF_DIRECTORY_PARTITIONS table.
REM                                        
create table WF_DIRECTORY_PARTITIONS
(
  PARTITION_ID  NUMBER  NOT NULL,
  ORIG_SYSTEM   VARCHAR2(30)
);

REM
REM Adding new columns to WF_DIRECTORY_PARTITIONS table.
REM 
alter table WF_DIRECTORY_PARTITIONS ADD
(
  ROLE_VIEW      VARCHAR2(30) DEFAULT NULL,
  USER_ROLE_VIEW VARCHAR2(30) DEFAULT NULL
);

REM 
REM Adding new columns for MLS support to WF_DIRECTORY_PARTITIONS table.
REM
alter table WF_DIRECTORY_PARTITIONS ADD
(
   ROLE_TL_VIEW    VARCHAR2(30) DEFAULT NULL
);



REM
REM High Availability Support
REM
alter table WF_ITEMS add
(
    HA_MIGRATION_FLAG varchar2(1)
);
 

REM Business Event System Support

alter table WF_ACTIVITIES add
(
    EVENT_NAME         varchar2(240),
    DIRECTION          varchar2(30)
);

alter table WF_ITEM_ATTRIBUTE_VALUES add
(
    EVENT_VALUE        wf_event_t
);


REM WF_QUEUES: doesnt exist in 2.5.1, but will already exist in 2.5.2

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


REM MORE_INFO Enhancement

alter table WF_NOTIFICATIONS add
(
    MORE_INFO_ROLE     VARCHAR2(30)
);


REM Denormalization of notification

alter table WF_NOTIFICATIONS add
(
   FROM_USER          varchar2(80),
   TO_USER            varchar2(80),
   SUBJECT            varchar2(2000),
   LANGUAGE           varchar2(4)
);


REM Event Additions

create table WF_AGENT_GROUPS
(
   GROUP_GUID          raw(16) not null,
   MEMBER_GUID         raw(16) not null
) storage (initial 10k next 10k);

alter table WF_AGENTS add
(
 TYPE               VARCHAR2(8)    DEFAULT 'AGENT' NOT NULL
);

alter table WF_EVENT_SUBSCRIPTIONS add
(
 EXPRESSION         VARCHAR2(4000)
);


REM MORE_INFO additional enhancements

alter table WF_NOTIFICATIONS modify
(
 TO_USER           VARCHAR2(320),
 FROM_USER         VARCHAR2(320),
 MORE_INFO_ROLE    VARCHAR2(320)
);

alter table WF_NOTIFICATIONS add
(
 FROM_ROLE         VARCHAR2(320)
);

create table WF_COMMENTS
(
 NOTIFICATION_ID   NUMBER NOT NULL,
 FROM_ROLE         VARCHAR2(320) NOT NULL,
 FROM_USER         VARCHAR2(320) NOT NULL,
 COMMENT_DATE      DATE NOT NULL,
 ACTION            VARCHAR2(30) NOT NULL,
 USER_COMMENT      VARCHAR2(4000)
);

alter table WF_COMMENTS add
(
 LANGUAGE          VARCHAR2(4)
);

REM Bug 2353079 - Added column SECURITY_GROUP_ID to all the 
REM               workflow tables

alter table WF_ACTIVITIES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ACTIVITIES_TL add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ACTIVITY_ATTRIBUTES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ACTIVITY_ATTRIBUTES_TL add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ACTIVITY_ATTR_VALUES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ACTIVITY_TRANSITIONS add
(
  SECURITY_GROUP_ID  VARCHAR2(32) 
);

alter table WF_AGENTS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_AGENT_GROUPS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_COMMENTS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_EVENTS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_EVENTS_TL add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_EVENT_GROUPS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_EVENT_SUBSCRIPTIONS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ITEMS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ITEM_ACTIVITY_STATUSES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ITEM_ACTIVITY_STATUSES_H add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ITEM_ATTRIBUTES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ITEM_ATTRIBUTES_TL add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ITEM_ATTRIBUTE_VALUES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ITEM_TYPES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ITEM_TYPES_TL add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_LOCAL_LANGUAGES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_LOCAL_ROLES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_LOCAL_USERS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);


alter table WF_LOCAL_USER_ROLES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

REM For changing the old data model
alter table WF_LOCAL_LANGUAGES modify (SECURITY_GROUP_ID VARCHAR2(32));

alter table WF_LOOKUPS_TL add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_LOOKUP_TYPES_TL add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_MESSAGES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_MESSAGES_TL add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_MESSAGE_ATTRIBUTES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_MESSAGE_ATTRIBUTES_TL add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_NOTIFICATIONS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_NOTIFICATION_ATTRIBUTES add
(
  EVENT_VALUE        WF_EVENT_T,
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_PROCESS_ACTIVITIES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_QUEUES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_RESOURCES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ROUTING_RULES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ROUTING_RULE_ATTRIBUTES add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_SYSTEMS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_MAILER_PARAMETERS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_MAILER_TAGS add
(
  ALLOW_RELOAD       VARCHAR2(1),
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_WORKLIST_COL_DEFINITIONS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_WORKLIST_DEFINITIONS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

alter table WF_ATTRIBUTE_CACHE add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

REM Events and Subscriptions Management: Bug 2558446


alter table WF_EVENTS add
(
	CUSTOMIZATION_LEVEL VARCHAR2(1) default 'L' NOT NULL
);

alter table WF_EVENT_SUBSCRIPTIONS add
(
	CUSTOMIZATION_LEVEL VARCHAR2(1) default 'L' NOT NULL
);

alter table WF_EVENTS add
(
	LICENSED_FLAG VARCHAR2(1) default 'Y' NOT NULL
);

alter table WF_EVENT_SUBSCRIPTIONS add
(
	LICENSED_FLAG VARCHAR2(1) default 'Y' NOT NULL
);

REM 3239088: JBES COLUMNS   
 ALTER TABLE WF_AGENTS ADD    
 (   
   JAVA_QUEUE_HANDLER   VARCHAR2(240)   
 );   
    
 ALTER TABLE WF_EVENTS ADD    
 (   
   JAVA_GENERATE_FUNC   VARCHAR2(240)   
 );   
    
 ALTER TABLE WF_EVENT_SUBSCRIPTIONS   
 ADD    
 (    
   STANDARD_TYPE    VARCHAR2(30),   
   STANDARD_CODE   VARCHAR2(30) ,   
   JAVA_RULE_FUNC   VARCHAR2(240) ,   
   ON_ERROR_CODE   VARCHAR2(30) ,   
   ACTION_CODE       VARCHAR2(30),   
   INVOCATION_ID      NUMBER,   
   MAP_CODE           VARCHAR2(30)   
 ); 

REM COLUMN SIZE UPDATION FOR 2.6.3
REM FOR TABLE WF_ITEM_TYPES
alter table WF_ITEM_TYPES modify (EXECUTE_ROLE  varchar2(320));
alter table WF_ITEM_TYPES modify (READ_ROLE     varchar2(320));
alter table WF_ITEM_TYPES modify ( WRITE_ROLE    varchar2(320));

REM FOR TABLE WF_ACTIVITIES
alter table WF_ACTIVITIES modify (EXECUTE_ROLE  varchar2(320));
alter table WF_ACTIVITIES modify (READ_ROLE     varchar2(320));
alter table WF_ACTIVITIES modify ( WRITE_ROLE    varchar2(320));

REM FOR TABLE WF_PROCESS_ACTIVITIES
alter table WF_PROCESS_ACTIVITIES modify (PERFORM_ROLE  varchar2(320));

REM FOR TABLE WF_ITEM_ACTIVITY_STATUSES
alter table WF_ITEM_ACTIVITY_STATUSES modify (ASSIGNED_USER  varchar2(320));

REM FOR TABLE WF_ITEM_ACTIVITY_STATUSES_H
alter table WF_ITEM_ACTIVITY_STATUSES_H modify (ASSIGNED_USER  varchar2(320));

REM FOR TABLE WF_ITEMS
alter table WF_ITEMS modify (OWNER_ROLE  varchar2(320));

REM FOR TABLE WF_MESSAGES
alter table WF_MESSAGES modify (READ_ROLE     varchar2(320));
alter table WF_MESSAGES modify ( WRITE_ROLE    varchar2(320));

REM FOR TABLE WF_NOTIFICATIONS
alter table WF_NOTIFICATIONS modify ( RECIPIENT_ROLE    varchar2(320));
alter table WF_NOTIFICATIONS modify ( ORIGINAL_RECIPIENT    varchar2(320));

REM FOR TABLE WF_ROUTING_RULES
alter table WF_ROUTING_RULES modify (ROLE     varchar2(320));

REM 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);

/* remove index creation here.
create unique index WF_BES_SUBSCRIBER_PINGS_U1
on WF_BES_SUBSCRIBER_PINGS (PING_NUMBER, QUEUE_NAME, SUBSCRIBER_NAME)
    storage (
        pctincrease 0
        initial     4K
        next        16k
        maxextents  50
    );

create index WF_BES_SUBSCRIBER_PINGS_N1
on WF_BES_SUBSCRIBER_PINGS (PING_TIME);

create index WF_BES_SUBSCRIBER_PINGS_N2
on WF_BES_SUBSCRIBER_PINGS (STATUS);
*/

REM Create the sequence 
CREATE SEQUENCE WF_BES_PING_NUMBER_S
 NOMAXVALUE
 NOMINVALUE
 NOCYCLE
 NOCACHE
/

CREATE SEQUENCE WF_CONTROL_JMS_SUBSCRIBER_ID_S
 NOMAXVALUE
 NOMINVALUE
 NOCYCLE
 NOCACHE
/


REM New tables for dig sigs.
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(30), 
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(1), 
REQUESTED_SIGNER_ID VARCHAR2(30), 
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), 
FLAVOR VARCHAR2(30) NOT NULL, 
SECURITY_GROUP_ID VARCHAR2(32)); 

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));

/* remove indexes creation here.  All should be in wfidxc.sql
create unique index WF_AGENT_GROUPS_U1
    on WF_AGENT_GROUPS (MEMBER_GUID, GROUP_GUID);


REM Updated some indexes in WF_ITEMS to correspond to afwf.odf.  
REM Making sure that any upgrade takes the corrected indexes.

 drop index WF_ITEMS_N4;
 
 create index WF_ITEMS_N4
    on WF_ITEMS (ITEM_TYPE, ROOT_ACTIVITY, OWNER_ROLE)
    storage (pctincrease 100);

 create index WF_ITEMS_N5
    on WF_ITEMS (USER_KEY)
    storage (pctincrease 100);

create index WF_ITEMS_N6
    on WF_ITEMS (OWNER_ROLE)
    storage (pctincrease 100);

REM new index to support high availability
 create unique index WF_ITEMS_U1
   on WF_ITEMS (HA_MIGRATION_FLAG,ITEM_TYPE,ITEM_KEY)
   storage (pctincrease 100);
*/

REM changes to support workflow roles (for certs and sigs table)
alter table WF_DIG_SIGS modify
(
 REQUESTED_SIGNER_TYPE VARCHAR2(20),
 REQUESTED_SIGNER_ID VARCHAR2(320)
);

alter table WF_DIG_CERTS modify
(
 OWNER_ID VARCHAR2(320)
);

REM change to support reusable controller code (for flavors table).
alter table WF_DIG_SIG_SPI_FLAVORS add
(
 BSR_FWK_CO VARCHAR2(240)
);

REM Security Group Id 
alter table WF_DIRECTORY_PARTITIONS add
(
  SECURITY_GROUP_ID  VARCHAR2(32)
);

REM Partition_Id is nullable in table WF_DIRECTORY_PARTITIONS
alter table WF_DIRECTORY_PARTITIONS modify 
(
  ORIG_SYSTEM  VARCHAR2(30) NOT NULL,
  PARTITION_ID NUMBER       NULL
);

REM WF_DIRECTORY_PARTITIONS_TL - Directory Partitions Translated
REM
create table WF_DIRECTORY_PARTITIONS_TL
(
  ORIG_SYSTEM         VARCHAR2(30) NOT NULL,
  DISPLAY_NAME        VARCHAR2(80) NOT NULL,
  LANGUAGE            VARCHAR2(4)  NOT NULL,
  SOURCE_LANG         VARCHAR2(4)  NOT NULL,
  SECURITY_GROUP_ID   VARCHAR2(32)
);

REM WF_LOCAL_ROLES for new WFDS
REM
alter table WF_LOCAL_ROLES add
(
  START_DATE          DATE
);

alter table WF_LOCAL_ROLES add
(
  USER_FLAG           VARCHAR2(1),
  PARTITION_ID        NUMBER DEFAULT 0
);

alter table WF_LOCAL_USER_ROLES add
(
  START_DATE          DATE,
  EXPIRATION_DATE     DATE,
  PARTITION_ID        NUMBER DEFAULT 0
);

REM Increase name, display_name, description...
alter table WF_LOCAL_ROLES modify
(
  NAME          VARCHAR2(320),
  DISPLAY_NAME  VARCHAR2(360),
  DESCRIPTION   VARCHAR2(1000),
  EMAIL_ADDRESS VARCHAR2(320)
);

alter table WF_LOCAL_USER_ROLES modify
(
  USER_NAME  VARCHAR2(320),
  ROLE_NAME  VARCHAR2(320)
);

REM Now that WF_LOCAL_USERS will no longer exist
REM We merge the data into WF_LOCAL_ROLES
REM First remove any users already exist in roles
delete from WF_LOCAL_ROLES R
 where exists (select null
                 from WF_LOCAL_USERS U
                where U.NAME = R.NAME
                  and U.ORIG_SYSTEM = R.ORIG_SYSTEM
                  and U.ORIG_SYSTEM_ID = R.ORIG_SYSTEM_ID);

REM Second make all the roles with USER_FLAG 'N' when it is not set.
update WF_LOCAL_ROLES
   set USER_FLAG = 'N'
 where USER_FLAG is null;

REM Third insert user into role
insert into WF_LOCAL_ROLES (
  NAME, DISPLAY_NAME,DESCRIPTION, NOTIFICATION_PREFERENCE,
  LANGUAGE, TERRITORY, EMAIL_ADDRESS, FAX, ORIG_SYSTEM,
  ORIG_SYSTEM_ID, START_DATE, STATUS, EXPIRATION_DATE,
  SECURITY_GROUP_ID, USER_FLAG, PARTITION_ID)
 select
  NAME, DISPLAY_NAME, DESCRIPTION, NOTIFICATION_PREFERENCE,
  LANGUAGE, TERRITORY, EMAIL_ADDRESS, FAX, ORIG_SYSTEM,
  ORIG_SYSTEM_ID, to_date(NULL), STATUS, EXPIRATION_DATE,
  NULL, 'Y', 0
  from WF_LOCAL_USERS;

REM insert user roles into WF_LOCAL_USER_ROLES
insert into WF_LOCAL_USER_ROLES (
  USER_NAME, ROLE_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID,
  ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE,
  EXPIRATION_DATE, SECURITY_GROUP_ID, PARTITION_ID )
 select
  NAME, NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID,
  ORIG_SYSTEM,ORIG_SYSTEM_ID, to_date(NULL),
  EXPIRATION_DATE, NULL, 0
  from WF_LOCAL_USERS;

REM Rename WF_LOCAL_USERS to WF_LOCAL_USERS_OLD
alter table WF_LOCAL_USERS rename to WF_LOCAL_USERS_OLD;

REM change to support X509.
alter table WF_DIG_SIG_SPI_FLAVORS add
(
 VALIDATOR_STORE                          VARCHAR2(240),
 VALIDATION_MODE                          VARCHAR2(240),
 SIGNATURE_FORMAT                         VARCHAR2(20),
 SIGNATURE_MODE                           VARCHAR2(20)
);

REM Changes to support Audit when Workflow Process is expedited
ALTER TABLE wf_item_activity_statuses ADD
(
  ACTION               varchar2(30),
  PERFORMED_BY         varchar2(320)
);

ALTER TABLE wf_item_activity_statuses_h ADD
(
  ACTION               varchar2(30),
  PERFORMED_BY         varchar2(320)
);

ALTER TABLE wf_comments ADD
(
   TO_ROLE     varchar2(320),
   TO_USER     varchar2(360),
   ACTION_TYPE varchar2(30),
   PROXY_ROLE  varchar2(320)
);

create index WF_COMMENTS_N1
    on WF_COMMENTS (NOTIFICATION_ID)
    storage (
        pctincrease 100
        initial     4K
        next        1M
        maxextents  50
    );

ALTER TABLE wf_comments ADD
(
   SEQUENCE NUMBER DEFAULT 0 NOT NULL
);

REM ---------------------------------
REM 2.6.3 additions to WFDS Datamodel
REM ---------------------------------

REM New columns to WF_LOCAL_ROLES
alter table WF_LOCAL_ROLES add
(
    CREATED_BY                NUMBER(15),
    CREATION_DATE             DATE,
    LAST_UPDATED_BY           NUMBER(15),
    LAST_UPDATE_DATE          DATE,
    LAST_UPDATE_LOGIN         NUMBER(15),
    OWNER_TAG                 VARCHAR2(50),
    PARENT_ORIG_SYSTEM        VARCHAR2(30),
    PARENT_ORIG_SYSTEM_ID     NUMBER
);

REM New columns to WF_LOCAL_USER_ROLES
alter table WF_LOCAL_USER_ROLES add
(
    ASSIGNMENT_TYPE          VARCHAR2(1),
    CREATED_BY               NUMBER(15),
    CREATION_DATE            DATE,
    LAST_UPDATED_BY          NUMBER(15),
    LAST_UPDATE_DATE         DATE,
    LAST_UPDATE_LOGIN        NUMBER(15),
    OWNER_TAG                VARCHAR2(50),
    PARENT_ORIG_SYSTEM       VARCHAR2(30),
    PARENT_ORIG_SYSTEM_ID    NUMBER
);


REM New columns to WF_LOCAL_ROLES_TL
alter table wf_local_roles_tl add
(
 OWNER_TAG                                VARCHAR2(50),
 CREATED_BY                               NUMBER(15),
 CREATION_DATE                            DATE,
 LAST_UPDATED_BY                          NUMBER(15),
 LAST_UPDATE_DATE                         DATE,
 LAST_UPDATE_LOGIN                        NUMBER(15));


REM Creating WF_ROLE_HIERARCHIES
create TABLE WF_ROLE_HIERARCHIES ( RELATIONSHIP_ID      NUMBER NOT NULL,
                                   SUB_NAME             VARCHAR2(320) NOT NULL,
                                   SUPER_NAME           VARCHAR2(320) NOT NULL,
                                   CREATED_BY            NUMBER(15),
                                   CREATION_DATE         DATE,
                                   LAST_UPDATED_BY       NUMBER(15),
                                   LAST_UPDATE_DATE      DATE,
                                   LAST_UPDATE_LOGIN     NUMBER(15),
                                   ENABLED_FLAG          VARCHAR2(1) NOT NULL,
                                   SECURITY_GROUP_ID     VARCHAR2(32),
                                   PROPAGATE_DATE        DATE,
                                   PARTITION_ID          NUMBER NOT NULL,
                                   SUPERIOR_PARTITION_ID NUMBER NOT NULL );


/* Creating sequence WF_ROLE_HIERARCHIES_S */
create sequence WF_ROLE_HIERARCHIES_S increment by 1;

/* Creating table WF_USER_ROLE_ASSIGNMENTS */
create table WF_USER_ROLE_ASSIGNMENTS (
                           USER_NAME                 VARCHAR2(320) NOT NULL,
                           ROLE_NAME                 VARCHAR2(320) NOT NULL,
                           RELATIONSHIP_ID           NUMBER        NOT NULL,
                           ASSIGNING_ROLE            VARCHAR2(320) NOT NULL,
                           START_DATE                DATE,
                           END_DATE                  DATE,
                           CREATED_BY                NUMBER(15),
                           CREATION_DATE             DATE,
                           LAST_UPDATED_BY           NUMBER(15),
                           LAST_UPDATE_DATE          DATE,
                           LAST_UPDATE_LOGIN         NUMBER(15),
                           USER_START_DATE           DATE,
                           ROLE_START_DATE           DATE,
                           ASSIGNING_ROLE_START_DATE DATE,
                           USER_END_DATE             DATE,
                           ROLE_END_DATE             DATE,
                           ASSIGNING_ROLE_END_DATE   DATE,
                           PARTITION_ID              NUMBER NOT NULL
                           );


/*
** Digital Signature related Data Model
*/
create table WF_NTF_SECURITY_POLICIES
(
 POLICY_NAME    VARCHAR2(20) NOT NULL,
 EMAIL_ALLOWED  VARCHAR2(1) NOT NULL
);

alter table WF_DIG_SIG_SPI_FLAVORS add
(
 VALIDATOR_STORE    VARCHAR2(240),
 VALIDATION_MODE    VARCHAR2(240),
 SIGNATURE_FORMAT   VARCHAR2(20),
 SIGNATURE_MODE     VARCHAR2(20)
);

REM New columns to WF_LOCAL_USER_ROLES
alter table WF_LOCAL_USER_ROLES add
(
    ROLE_END_DATE            DATE,
    ROLE_START_DATE          DATE,
    USER_END_DATE            DATE,
    USER_START_DATE          DATE
);

REM New columns to WF_LOCAL_USER_ROLES
alter table WF_LOCAL_USER_ROLES add
(
   EFFECTIVE_START_DATE     DATE,
   EFFECTIVE_END_DATE       DATE
);

REM New columns to WF_USER_ROLE_ASSIGNMENTS
alter table WF_USER_ROLE_ASSIGNMENTS add
(
   EFFECTIVE_START_DATE     DATE,
   EFFECTIVE_END_DATE       DATE
);

commit;
exit;