/*=======================================================================+
| 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';
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 := 'WFADMIN';
select count(NAME) into l_cnt
from wf_roles
where name = l_name;
l_displayName := 'Workflow Administrator';
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 := 'CDOUGLAS';
select count(NAME) into l_cnt
from wf_roles
where name = l_name;
l_displayName := 'Carl Douglas';
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 := 'KWALKER';
select count(NAME) into l_cnt
from wf_roles
where name = l_name;
l_displayName := 'Kenneth Walker';
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 := 'SPIERSON';
select count(NAME) into l_cnt
from wf_roles
where name = l_name;
l_displayName := 'Sandra Pierson';
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;
/
/*
** 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