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

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

/*=======================================================================+
 |  Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
 |                            All rights reserved.                       |
 +=======================================================================+
 |
 |     $Header: wfevdemc.sql 26.6 2003/02/17 17:29:58 rwunderl ship $
 |
 | FILENAME
 |   wfevdemc.sql
 |
 | DESCRIPTION
 |	Create workflow Event Demonstration tables and data
 |
 |    USERS :          SUPPLIER
 |                     
 |    USER ROLES :
 |                     ROLE            USER
 |                     ----            -----
 |                     MANAGERS        SUPPLIER
 |
 | NOTES
 |
 | CREATED    21-SEP-00		   Created
 *=======================================================================*/

whenever sqlerror continue;
/*
** REMOVE ANY PREVIOUS SUBSCRIPTIONS
*/
delete from WF_EVENT_SUBSCRIPTIONS
where EVENT_FILTER_GUID in
 ( select GUID 
   from WF_EVENTS
   where NAME in ('demo.oracle.wf.b2b.po.ack'
		, 'demo.oracle.wf.b2b.po.asn'
		, 'demo.oracle.wf.b2b.po.create'
		, 'demo.oracle.wf.b2b.po.invoice'));
/*
** WF_EVENTDEMO_ITEMS
*/
drop   table WF_EVENTDEMO_ITEMS;

create table WF_EVENTDEMO_ITEMS(	item_number	varchar2(30) not null);
--
create unique index WF_EVENTDEMO_ITEM_PK on WF_EVENTDEMO_ITEMS (item_number);
--
insert into WF_EVENTDEMO_ITEMS values ( 'PP1000');
insert into WF_EVENTDEMO_ITEMS values ( 'DELLGX110');
insert into WF_EVENTDEMO_ITEMS values ( 'MTSTARTAC');
--
/*
** WF_EVENTDEMO_PO
*/
--
drop   table WF_EVENTDEMO_PO;

create table WF_EVENTDEMO_PO ( po_number 	varchar2(30) not null,
				requestor	varchar2(30) not null,
				item_number	varchar2(30) not null,
				item_description varchar2(30) not null,
				total_cost	number not null,
				delivery_date	date,
				invoice_number	number);
--
create unique index WF_EVENTDEMO_PO_PK on WF_EVENTDEMO_PO (po_number);
--
/*
** 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 := 'Barry Lewis';

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=>'WFINVALID',
                                  fax=>'',
                                  status=>'ACTIVE',
                                  expiration_date=>'');

end if;

exception
  when OTHERS then
    null; --Mask any errors.

end;
/

declare
  l_name        varchar2(360);
  l_displayName varchar2(240);
  l_cnt         number;

begin
  l_name := 'SYSADMIN';
  select count(NAME) into l_cnt
  from   wf_roles
  where  name = l_name;
  l_displayName := 'System Administrator';

end if;

exception
  when OTHERS then
    null; --Mask any errors.

end;
/

declare
  l_name        varchar2(360);
  l_displayName varchar2(240);
  l_cnt         number;

begin
  l_name := 'WFADMIN';
  select count(NAME) into l_cnt
  from   wf_roles
  where  name = l_name;
  l_displayName := 'Workflow Administrator';

end if;

exception
  when OTHERS then
    null; --Mask any errors.

end;
/

declare
  l_name        varchar2(360);
  l_displayName varchar2(240);
  l_cnt         number;

begin
  l_name := 'CDOUGLAS';
  select count(NAME) into l_cnt
  from   wf_roles
  where  name = l_name;
  l_displayName := 'Carl Douglas';

end if;

exception
  when OTHERS then
    null; --Mask any errors.

end;
/

declare
  l_name        varchar2(360);
  l_displayName varchar2(240);
  l_cnt         number;

begin
  l_name := 'KWALKER';
  select count(NAME) into l_cnt
  from   wf_roles
  where  name = l_name;
  l_displayName := 'Kenneth Walker';

end if;

exception
  when OTHERS then
    null; --Mask any errors.

end;
/

declare
  l_name        varchar2(360);
  l_displayName varchar2(240);
  l_cnt         number;

begin
  l_name := 'SPIERSON';
  select count(NAME) into l_cnt
  from   wf_roles
  where  name = l_name;
  l_displayName := 'Sandra Pierson';

end if;

exception
  when OTHERS then
    null; --Mask any errors.

end;
/

/*
** Create Roles
*/
declare
  l_name        varchar2(360);
  l_displayName varchar2(240);
  l_cnt         number;

begin
  l_name := 'ADMIN';
  select count(NAME) into l_cnt
  from   wf_roles
  where  name = l_name;
  l_displayName := 'Administrators';

if (l_cnt < 1) then
    WF_DIRECTORY.CreateAdHocRole( role_name=>l_name,
                                  role_display_name=>l_displayName,
                                  language=>'AMERICAN',
                                  territory=>'AMERICA',
                                  role_description=>l_displayName,
                                  notification_preference=>'QUERY',
                                  role_users=>'SYSADMIN WFADMIN',
                                  email_address=>l_name,
                                  fax=>'',
                                  status=>'ACTIVE',
                                  expiration_date=>'');

end if;

exception
  when OTHERS then
    -- make sure email address is null incase insert failed.
    update wf_local_roles set email_address = null where name=l_name;

end;
/

declare
  l_name        varchar2(360);
  l_displayName varchar2(240);
  l_cnt         number;

begin
  l_name := 'MANAGERS';
  select count(NAME) into l_cnt
  from   wf_roles
  where  name = l_name;
  l_displayName := 'Managers';

if (l_cnt < 1) then
    WF_DIRECTORY.CreateAdHocRole( role_name=>l_name,
                                  role_display_name=>l_displayName,
                                  language=>'AMERICAN',
                                  territory=>'AMERICA',
                                  role_description=>l_displayName,
                                  notification_preference=>'QUERY',
                                  role_users=>'SPIERSON',
                                  email_address=>l_name,
                                  fax=>'',
                                  status=>'ACTIVE',
                                  expiration_date=>'');

end if;

exception
  when OTHERS then
    null;

end;
/

declare
  l_name        varchar2(360);
  l_displayName varchar2(240);
  l_cnt         number;

begin
  l_name := 'WORKERS';
  select count(NAME) into l_cnt
  from   wf_roles
  where  name = l_name;
  l_displayName := 'Workers';

if (l_cnt < 1) then
    WF_DIRECTORY.CreateAdHocRole( role_name=>l_name,
                                  role_display_name=>l_displayName,
                                  language=>'AMERICAN',
                                  territory=>'AMERICA',
                                  role_description=>l_displayName,
                                  notification_preference=>'QUERY',
                                  role_users=>'BLEWIS KWALKER CDOUGLAS',
                                  email_address=>l_name,
                                  fax=>'',
                                  status=>'ACTIVE',
                                  expiration_date=>'');

end if;

exception
  when OTHERS then
    null;

end;
/

declare
  l_name        varchar2(360);
  l_displayName varchar2(240);
  l_cnt         number;

begin
  l_name := 'OTHERS';
  select count(NAME) into l_cnt
  from   wf_roles
  where  name = l_name;
  l_displayName := 'Others';

if (l_cnt < 1) then
    WF_DIRECTORY.CreateAdHocRole( role_name=>l_name,
                                  role_display_name=>l_displayName,
                                  language=>'AMERICAN',
                                  territory=>'AMERICA',
                                  role_description=>l_displayName,
                                  notification_preference=>'QUERY',
                                  role_users=>'SPIERSON CDOUGLAS',
                                  email_address=>l_name,
                                  fax=>'',
                                  status=>'ACTIVE',
                                  expiration_date=>'');

end if;

exception
  when OTHERS then
    null;

end;
/

commit;

exit

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de