rem HEADER
rem   $Header: wfprot.sql 26.1 2001/11/05 20:11:16 ctilley ship $
rem NAME
rem   wfprot.sql - WorkFlow PROTection level reset
rem USAGE
rem   @wfprot <item_type> <protection_level>
rem DESCRIPTION
rem   Resets the protection level for all objects in a specified
rem   item type to the supplied value.
rem NOTE
rem   After resetting the protection level NOTHING in the item type
rem   will be customizable by a higher access level.
rem
rem dbdrv: none

WHENEVER SQLERROR EXIT FAILURE ROLLBACK

define type=&1
define level=&2

prompt ---- WF_ITEM_TYPES
update wf_item_types
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  name like '&type';
commit;

prompt ---- WF_ITEM_TYPES_TL
update wf_item_types_tl
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  name like '&type';
commit;

prompt ---- WF_ITEM_ATTRIBUTES
update wf_item_attributes
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  item_type like '&type';
commit;

prompt ---- WF_ITEM_ATTRIBUTES_TL
update wf_item_attributes_tl
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  item_type like '&type';
commit;

prompt ---- WF_LOOKUP_TYPES_TL
update wf_lookup_types_tl
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  item_type like '&type';
commit;

prompt ---- WF_LOOKUPS_TL
update wf_lookups_tl
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  lookup_type in
  (select lookup_type
  from wf_lookup_types
  where item_type like '&type');
commit;

prompt ---- WF_RESOURCES
update wf_resources
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  type like '&type';
commit;

prompt ---- WF_MESSAGES
update wf_messages
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  type like '&type';
commit;

prompt ---- WF_MESSAGES_TL
update wf_messages_tl
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  type like '&type';
commit;

prompt ---- WF_MESSAGE_ATTRIBUTES
update wf_message_attributes
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  message_type like '&type';
commit;

prompt ---- WF_MESSAGE_ATTRIBUTES_TL
update wf_message_attributes_tl
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  message_type like '&type';
commit;

prompt ---- WF_ACTIVITIES
update wf_activities
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  item_type like '&type';
commit;

prompt ---- WF_ACTIVITIES_TL
update wf_activities_tl
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  item_type like '&type';
commit;

prompt ---- WF_ACTIVITY_ATTRIBUTES
update wf_activity_attributes
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  activity_item_type like '&type';
commit;

prompt ---- WF_ACTIVITY_ATTRIBUTES_TL
update wf_activity_attributes_tl
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  activity_item_type like '&type';
commit;

prompt ---- WF_PROCESS_ACTIVITIES
update wf_process_activities
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  process_item_type like '&type';
commit;

prompt ---- WF_ACTIVITY_ATTR_VALUES
update wf_activity_attr_values
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  process_activity_id in
  (select instance_id
  from wf_process_activities
  where process_item_type like '&type');
commit;

prompt ---- WF_ACTIVITY_TRANSITIONS
update wf_activity_transitions
set    protect_level = greatest(protect_level, &level),
       custom_level = least(custom_level, &level)
where  from_process_activity in
  (select instance_id
  from wf_process_activities
  where process_item_type like '&type');
commit;

exit;