REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
REM dbdrv: checkfile:~PROD:~PATH:~FILE
/*=======================================================================+
 |  Copyright (c) 2000 Oracle Corporation Redwood Shores, California, USA|
 |                            All rights reserved.                       |
 +=======================================================================+
 | FILENAME
 |   wfmtnb.sql
 |
 | DESCRIPTION
 |   PL/SQL body for package: WF_MAINTENANCE 
 |
 | NOTES
 |   Routines to maintain design and runtime data.
 |
 | HISTORY
 |
 | 17-NOV-2000	rwunderl.us	created
 | 02-JAN-2002  jwsmith  increased l_oldname, l_newname to varchar2(320)
 *=======================================================================*/

SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;


create or replace package body WF_MAINTENANCE as
 /* $Header: wfmtnb.pls 26.10 2004/07/13 22:02:37 rwunderl ship $ */

g_CommitCounter NUMBER := 0;
g_docommit BOOLEAN := FALSE;
procedure PerformCommit;

-- procedure PropagateChangedName
--   Locates all occurrences of an old username and changes to 
--   the new username.
--
-- IN:
--   OldName - Old Username we are changing from.
--   NewName - New Username we are changing to.
--   CommitFrequency - Number of updates we perform before commit.
--
procedure PropagateChangedName(
  OldName in varchar2,
  NewName in varchar2,
  docommit in BOOLEAN )

is

l_oldname VARCHAR2(320); -- Local Variable of OldName
l_newname VARCHAR2(320); -- Local Variable of NewName

-- Setting up cursors for tables that would store a role name.
-- Some tables have columns named 'READ_ROLE' and 'WRITE_ROLE' that
-- are not currently used, so they are not included.

cursor Items (l_oldname varchar2) is
select ITEM_TYPE, ITEM_KEY
from   WF_ITEMS
where  OWNER_ROLE = l_oldname;

cursor ItemActivityStatuses (l_oldname varchar2) is
select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
from   WF_ITEM_ACTIVITY_STATUSES
where  ASSIGNED_USER =  l_oldname;

cursor ItemActivityStatuses_H (l_oldname varchar2) is
select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
from   WF_ITEM_ACTIVITY_STATUSES_H
where  ASSIGNED_USER =  l_oldname;

cursor Notifications (l_oldname varchar2) is
select NOTIFICATION_ID
from   WF_NOTIFICATIONS
where  RECIPIENT_ROLE     = l_oldname
or     ORIGINAL_RECIPIENT = l_oldname
or     more_info_role     = l_oldname 
or     from_role          = l_oldname
or     responder          = l_oldname;

cursor ProcessActivities (l_oldname varchar2) is
select PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION, 
       INSTANCE_LABEL, INSTANCE_ID
from   WF_PROCESS_ACTIVITIES
where  PERFORM_ROLE = l_oldname;

cursor RoutingRules (l_oldname varchar2) is
select RULE_ID
from   WF_ROUTING_RULES
where  ROLE = l_oldname
or     ACTION_ARGUMENT = l_oldname;

cursor wfComments (l_oldname varchar2) is
select rowid
from   wf_comments
where  from_role = l_oldname
or     from_user = l_oldname
or     to_role = l_oldname
or     to_user = l_oldname
or     proxy_role = l_oldname;

l_roleInfoTAB WF_DIRECTORY.wf_local_roles_tbl_type;

begin
l_newname := upper(substrb(NewName,1,320));
l_oldname := upper(substrb(OldName,1,320));
g_docommit := docommit;


/* We check to be sure that old name no longer exists (IE: the name was
   changed).  If it is we can go ahead and effect the change.  

   If the old name is still active and somewhere else in the directory services
   we can't change it, so we have to raise the error that the name still
   exists.

   We then check to be sure the new name is active and ready to receive    
   the records from the old name.
*/

 WF_DIRECTORY.GetRoleInfo2(l_oldName, l_roleInfoTAB);

 if (l_roleInfoTAB(1).display_name is not NULL) then
  if not (WF_DIRECTORY.ChangeLocalUsername(l_oldname, l_newname, FALSE)) then
  WF_CORE.Token('ROLE', l_oldname);
  WF_CORE.Token('PROCEDURE', 'PropagateChangedName');
  WF_CORE.Token('PARAMETER', 'OldName');
  WF_CORE.Raise('WFMTN_ACTIVEROLE');
  return;
  end if;
 end if;

 WF_DIRECTORY.GetRoleInfo2(l_newname, l_roleInfoTAB);
 
 if  (l_roleInfoTAB(1).display_name is null) then 
  WF_CORE.Token('ROLE', l_newname);
  WF_CORE.Raise('WFNTF_ROLE');
  return;
 end if;


/* We will now start looping through the cursors and updating OldName 
   to NewName 
*/
for I in Items (l_oldname) loop

 update WF_ITEMS
 set    OWNER_ROLE = l_newname
 where  ITEM_TYPE = i.item_type
 and    item_key = i.item_key;

PerformCommit();
end loop;

for IAS in ItemActivityStatuses (l_oldname) loop

 update WF_ITEM_ACTIVITY_STATUSES
 set    ASSIGNED_USER = l_newname
 where  ITEM_TYPE = ias.item_type
 and    ITEM_KEY = ias.item_key
 and    PROCESS_ACTIVITY = ias.process_activity;

PerformCommit();
end loop;

for IASH in ItemActivityStatuses_H (l_oldname) loop

 update WF_ITEM_ACTIVITY_STATUSES_H
 set    ASSIGNED_USER = l_newname
 where  ITEM_TYPE = iash.item_type
 and    ITEM_KEY = iash.item_key
 and    PROCESS_ACTIVITY = iash.process_activity;

PerformCommit();
end loop;

for NTF in Notifications (l_oldname) loop

 update WF_NOTIFICATIONS
 set    RECIPIENT_ROLE     = l_newname
 where  NOTIFICATION_ID    = ntf.notification_id 
 and    RECIPIENT_ROLE     = l_oldname;

 update WF_NOTIFICATIONS
 set    ORIGINAL_RECIPIENT = l_newname
 where  NOTIFICATION_ID    = ntf.notification_id
 and    ORIGINAL_RECIPIENT = l_oldname;

update WF_NOTIFICATIONS
 set    MORE_INFO_ROLE     = l_newname
 where  NOTIFICATION_ID    = ntf.notification_id
 and    MORE_INFO_ROLE     = l_oldname;

update WF_NOTIFICATIONS
 set    FROM_ROLE          = l_newname
 where  NOTIFICATION_ID    = ntf.notification_id
 and    FROM_ROLE          = l_oldname;

 update WF_NOTIFICATIONS
 set    RESPONDER          = l_newname
 where  NOTIFICATION_ID    = ntf.notification_id
 and    RESPONDER          = l_oldname;

PerformCommit();
end loop;

for PAct in ProcessActivities (l_oldname) loop

 update WF_PROCESS_ACTIVITIES
 set    PERFORM_ROLE = l_newname
 where  PROCESS_ITEM_TYPE = pact.process_item_type
 and    PROCESS_NAME = pact.process_name
 and    PROCESS_VERSION = pact.process_version
 and    INSTANCE_LABEL = pact.instance_label
 and    INSTANCE_ID = pact.instance_id;

PerformCommit();
end loop;

for RR in RoutingRules (l_oldname) loop

 update WF_ROUTING_RULES
 set    ROLE = l_newname
 where  RULE_ID = rr.rule_id
 and    ROLE = l_oldname;

 update WF_ROUTING_RULES
 set    ACTION_ARGUMENT = l_newname
 where  RULE_ID = rr.rule_id
 and    ACTION_ARGUMENT = l_oldname;

PerformCommit();
end loop;

for wcom in wfComments (l_oldname) loop
 update WF_COMMENTS
 set    FROM_ROLE = l_newname,
        FROM_USER = l_roleInfoTAB(1).display_name
 where  rowid = wcom.rowid
 and    FROM_ROLE = l_oldName;
 
 update WF_COMMENTS
 set    TO_ROLE = l_newname,
        TO_USER = l_roleInfoTAB(1).display_name
 where  rowid = wcom.rowid
 and    TO_ROLE = l_oldName;
 
 update WF_COMMENTS
 set    PROXY_ROLE = l_newname
 where  rowid = wcom.rowid
 and    PROXY_ROLE = l_oldName;

 PerformCommit();
end loop;

commit;

exception
 when others then
   WF_CORE.Context('WF_MAINTENANCE', 'PropagateChangedName', OldName, NewName);
   raise;
end PropagateChangedName;

-- procedure PerformCommit (private)
--   Decides if commit should occur and commits.
--
-- IN:
--   No Parameters.
--
procedure PerformCommit

IS
BEGIN
if (g_docommit) then
 g_commitCounter := g_commitCounter +1;
 if (g_commitCounter >= WF_MAINTENANCE.g_CommitFrequency) then
    commit;
    g_commitCounter := 0;
 end if;
end if;

END PerformCommit;

end WF_MAINTENANCE;
/
commit;
exit;