/*=======================================================================+ | 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=>''); 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 := 'WFTESTER'; select count(NAME) into l_cnt from wf_roles where name = l_name; l_displayName := 'WFTESTER'; 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=>''); 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 := 'TEST.USER.OVER.30.CHARS.ORACLE.COM'; select count(NAME) into l_cnt from wf_roles where name = l_name; l_displayName := 'TEST.USER.OVER.30.CHARS.ORACLE.COM'; 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=>''); 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=>l_name, 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=>l_name, 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=>l_name, 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=>l_name, 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=>l_name, 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 := '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