Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\wf\demo\wfdemoc.sql

/*=======================================================================+
 |  Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
 |                            All rights reserved.                       |
 +=======================================================================+
 | FILENAME
 |   wfdemoc.sql
 |
 | DESCRIPTION
 |	Create workflow Demonstration tables and data
 |
 |    USERS :          SYSADMIN
 |                     WFADMIN
 |                     BLEWIS
 |                     TEST.USER.OVER.30.CHARS.ORACLE.COM
 |                     WFTESTER
 |                     CDOUGLAS
 |                     KWALKER
 |                     SPIERSON
 |                     
 |    ROLES :          ADMIN
 |                     MANAGERS
 |                     WORKERS
 |                     OTHERS
 |
 |    USER ROLES :
 |                     ROLE            USER
 |                     ----            -----
 |                     ADMIN           SYSADMIN
 |                                     WFADMIN
 |     
 |                     MANAGERS        SPIERSON
 |
 |                     WORKERS         BLEWIS
 |                                     KWALKER 
 |                                     CDOUGLAS
 |                     
 |                     OTHERS          SPIERSON
 |                                     CDOUGLAS
 |
 | NOTES
 |
 | MODIFIED    30-OCT-97  rseiden  Made script rerunnable by dropping
 |                                 tables prior to creating the table,
 |                                 inserting rows and creating the index.
 |                                 Added create of users and roles in 
 |                                 wf_local_users, wf_local_roles, and 
 |                                 wf_local_user_roles for the 
 |                                 Workflow Requisition Approval Demo.
 | MODIFIED    04-FEB-98  rseiden  Changed wf_local_users
 |                                 email_addresses from WFTEST
 |                                 to WFINVALID to avoid infinite loops
 |                                 caused when running the mailer under
 |                                 WFTEST.  re: Susan Stratton
 | MODIFIED    18-NOV-98  rseiden  replaced user names with Vision database
 |                                 compliant names according to:
 |                                 JANDERSON => BLEWIS
 |                                 MBEECH    => CDOUGLAS
 |                                 DMCKEE    => KWALKER
 |                                 JRUSH     => SPIERSON
 |                                 Removed user name JSMITH from demo.
 |                                 Added expiration_date column to inserts
 |                                 to wf_local_users, wf_local_roles.
 |                                 Default to null so the demo users/roles
 |                                 won't be deleted during a purge run.
 | MODIFIED    02-DEC-98  rseiden  set wf_local_user_roles 
 |                                 user/role_orig_system,
 |                                 user/role_orig_system_id columns.
 | MODIFIED    06-JAN-02  ahunt    Remove the direct insert to tables
 |                                 for users and roles and replace with
 |                                 the WF_DIRECTORY CreateAdhocUser and
 |                                 CreateAdhocRole
 |
 | Modified     09/23/03  fgraham  Added new user 
 |                                'TEST.USER.OVER.30.CHARS.ORACLE.COM' for
 |                                 purpose of creating new WinRunner RTs for 
 |                                 testing Bugs 3053601 & 3063982
*=======================================================================*/

/* $Header: wfdemoc.sql 26.9 2004/08/30 12:25:38 vshanmug ship $ */
whenever sqlerror continue;

drop   table WF_REQDEMO_EMP_HIERARCHY;

create table WF_REQDEMO_EMP_HIERARCHY	(person_username	varchar2(60) not null,
						                 manager_username   varchar2(60) );
--
create unique index WF_REQDEMO_EMP_HIERARCH_PK on WF_REQDEMO_EMP_HIERARCHY (person_username,manager_username);
--
/*
** WF_REQDEMO_EMP_HIERARCHY
*/
insert into WF_REQDEMO_EMP_HIERARCHY values ( 'BLEWIS','KWALKER');
insert into WF_REQDEMO_EMP_HIERARCHY values ( 'WFTESTER','TEST.USER.OVER.30.CHARS.ORACLE.COM');
insert into WF_REQDEMO_EMP_HIERARCHY values ( 'TEST.USER.OVER.30.CHARS.ORACLE.COM','CDOUGLAS');
insert into WF_REQDEMO_EMP_HIERARCHY values ( 'KWALKER','CDOUGLAS');
insert into WF_REQDEMO_EMP_HIERARCHY values ( 'CDOUGLAS','SPIERSON');
insert into WF_REQDEMO_EMP_HIERARCHY values ( 'SPIERSON','');

--
drop   table WF_REQDEMO_EMP_AUTHORITY;

create table WF_REQDEMO_EMP_AUTHORITY (username     	varchar2(60) not null,
					                   spending_limit	number(15)   not null );
/*
** WF_REQDEMO_EMP_AUTHORITY
*/
insert into WF_REQDEMO_EMP_AUTHORITY values ('BLEWIS',500);
insert into WF_REQDEMO_EMP_AUTHORITY values ('WFTESTER',500);
insert into WF_REQDEMO_EMP_AUTHORITY values ('TEST.USER.OVER.30.CHARS.ORACLE.COM',1000);
insert into WF_REQDEMO_EMP_AUTHORITY values ('KWALKER',1000);
insert into WF_REQDEMO_EMP_AUTHORITY values ('CDOUGLAS',2000);
insert into WF_REQDEMO_EMP_AUTHORITY values ('SPIERSON', 3000);

create unique index WF_REQDEMO_EMP_AUTHORITY_PK 
on WF_REQDEMO_EMP_AUTHORITY(username);

/*
** POPULATE LOCAL USERS
*/
prompt *** POPULATE ROLE DATA
prompt Create Users

declare
  l_name        varchar2(360);
  l_displayName varchar2(240);
  l_cnt         number;
  
begin
  l_name := 'BLEWIS';
  select count(NAME) into l_cnt
  from   wf_roles
  where  name = l_name;
  l_displayName := 'BLEWIS';
  
  if (l_cnt < 1) then
    WF_DIRECTORY.CreateAdHocUser( name=>l_name,
                                  display_name=>l_displayName,
                                  language=>'AMERICAN',
                                  territory=>'AMERICA',
                                  description=>l_displayName,
                                  notification_preference=>'QUERY',
                                  email_address=>l_name,
                                  fax=>'',
                                  status=>'ACTIVE',
                                  expiration_date=>'');