REM $Header: wfdirc.sql 26.13 2005/02/12 05:35:23 anachatt ship $
REM +======================================================================+
REM | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM
REM NAME
REM     wfdirc.sql  -  WorkFlow DIRectory service Create schema
REM NOTES
REM
REM MODIFICATION LOG: 
REM     01/2002 JWSMITH BUG 2001012 - Increase name, email_address,
REM             user_name, role_name to varchar2(320), role display_name
REM             to varchar2(360)
REM     05/2002 VSHANMUG Bug 2353079 - Added new column SECURITY_GROUP_ID 
REM             to all the tables
REM =======================================================================

REM Connect to base account
REM (autopatch will run all scripts in apps account)
connect &1/&2;

set verify off
define hdr = "$Header: wfdirc.sql 26.13 2005/02/12 05:35:23 anachatt ship $"

WHENEVER SQLERROR CONTINUE;

/*
** WF_LOCAL_LANGUAGES - local languages
*/
create table WF_LOCAL_LANGUAGES
(
    CODE              varchar2(4) not null,
    DISPLAY_NAME      varchar2(80) not null,
    NLS_LANGUAGE      varchar2(30) not null,
    NLS_TERRITORY     varchar2(30) not null,
    NLS_CODESET       varchar2(30) not null,
    INSTALLED_FLAG    varchar2(1) not null,
    SECURITY_GROUP_ID varchar2(32)
);

create table wf_local_roles_tl
( 
 NAME                           VARCHAR2(320) NOT NULL,
 DISPLAY_NAME                   VARCHAR2(360)NOT NULL,
 DESCRIPTION                    VARCHAR2(1000),
 ORIG_SYSTEM                    VARCHAR2(30) NOT NULL,
 ORIG_SYSTEM_ID                 NUMBER NOT NULL,
 PARTITION_ID                   NUMBER,
 LANGUAGE                       VARCHAR2(30),
 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)
);

/*
** WF_LOCAL_USERS - local users (OBSOLETE, see WF_LOCAL_ROLES)
*/


/*
** WF_LOCAL_ROLES - local roles
*/
create table WF_LOCAL_ROLES
(
    NAME                      varchar2(320)  not null,
    DISPLAY_NAME              varchar2(360)  not null,
    DESCRIPTION               varchar2(1000),
    NOTIFICATION_PREFERENCE   varchar2(8)   not null,
    LANGUAGE                  varchar2(30)  not null,
    TERRITORY                 varchar2(30)  not null,
    EMAIL_ADDRESS             varchar2(320),
    FAX                       varchar2(240),
    ORIG_SYSTEM               varchar2(30) DEFAULT 'WF_LOCAL_ROLES' NOT NULL,
    ORIG_SYSTEM_ID            number DEFAULT 0 NOT NULL,
    STATUS                    varchar2(8)   not null,
    START_DATE                date,
    EXPIRATION_DATE           date,
    SECURITY_GROUP_ID         varchar2(32),
    USER_FLAG                 VARCHAR2(1),
    PARTITION_ID              NUMBER DEFAULT 0,
    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
);


/*
** WF_LOCAL_USER_ROLES
*/
create table WF_LOCAL_USER_ROLES
(
    USER_NAME                varchar2(320) not null,
    ROLE_NAME                varchar2(320) not null,
    USER_ORIG_SYSTEM         varchar2(30)  default 'WF_LOCAL_ROLES',
    USER_ORIG_SYSTEM_ID      number        default 0,
    ROLE_ORIG_SYSTEM         varchar2(30)  default 'WF_LOCAL_ROLES',
    ROLE_ORIG_SYSTEM_ID      number        default 0,
    START_DATE               date          default null,
    EXPIRATION_DATE          date          default null,
    SECURITY_GROUP_ID        varchar(32)   default null,
    PARTITION_ID             number        default 0,
    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,
    USER_START_DATE	     DATE,
    ROLE_START_DATE          DATE,
    USER_END_DATE            DATE,
    ROLE_END_DATE            DATE,
    EFFECTIVE_START_DATE     DATE,
    EFFECTIVE_END_DATE       DATE
);

/*                                     
** WF_DIRECTORY_PARTITIONS - Directory Partitions
*/                                     
create table WF_DIRECTORY_PARTITIONS
(
  ORIG_SYSTEM        VARCHAR2(30) NOT NULL,
  PARTITION_ID       NUMBER,
  SECURITY_GROUP_ID  VARCHAR2(32),
  ROLE_VIEW          VARCHAR2(30),
  USER_ROLE_VIEW     VARCHAR2(30),
  ROLE_TL_VIEW       VARCHAR2(30)
);

/*
** bug 2868207
** WF_DIRECTORY_PARTITIONS_TL - Directory Partitions Translated
*/
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)
);

/*
** Sequence WF_ADHOC_ROLE_SELECT
*/
create sequence WF_ADHOC_ROLE_S;

 /* 
 ** Creating WF_ROLE_HIERARCHIES 
 */
 create TABLE WF_ROLE_HIERARCHIES ( RELATIONSHIP_ID     NUMBER,
                                    SUB_NAME            VARCHAR2(320),
                                    SUPER_NAME          VARCHAR2(320),
                                    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),
                                    SECURITY_GROUP_ID   VARCHAR2(32),
                                    PROPAGATE_DATE      DATE,
                                    PARTITION_ID        NUMBER NOT NULL,
                                    SUPERIOR_PARTITION_ID NUMBER NOT NULL
                                    );

 /* 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,
                           EFFECTIVE_START_DATE DATE,
                           EFFECTIVE_END_DATE   DATE
                           );



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



commit;
exit;