[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_wf_selector in varchar2,
x_read_role in varchar2,
x_write_role in varchar2,
x_execute_role in varchar2,
x_persistence_type in varchar2,
x_persistence_days in varchar2,
x_level_error out NOCOPY number
) is
row_id varchar2(30);
select NAME, DISPLAY_NAME, NAME
into conflict_name, l_dname, l_name
from WF_ITEM_TYPES_VL
where DISPLAY_NAME = x_display_name
and NAME <> x_name;
select NAME, DISPLAY_NAME
into conflict_name, l_dname
from WF_ITEM_TYPES_VL
where DISPLAY_NAME = n_dname
and NAME <> l_name;
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_ITEM_TYPES_VL
where NAME = x_name;
Wf_Item_Types_Pkg.Update_Row(
x_name => x_name,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_wf_selector => x_wf_selector,
x_read_role => x_read_role,
x_write_role => x_write_role,
x_execute_role => x_execute_role,
x_display_name => n_dname,
x_description => x_description,
x_persistence_type => x_persistence_type,
x_persistence_days => l_persistence_days
);
Wf_Item_Types_Pkg.Insert_Row(
x_rowid => row_id,
x_name => x_name,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_wf_selector => x_wf_selector,
x_read_role => x_read_role,
x_write_role => x_write_role,
x_execute_role => x_execute_role,
x_display_name => n_dname,
x_description => x_description,
x_persistence_type => x_persistence_type,
x_persistence_days => l_persistence_days
);
update WF_ITEM_ATTRIBUTES set
SEQUENCE = -1
where ITEM_TYPE = itemtype
and SEQUENCE = oldseq;
update WF_ITEM_ATTRIBUTES set
SEQUENCE = SEQUENCE - 1
where ITEM_TYPE = itemtype
and SEQUENCE = i;
update WF_ITEM_ATTRIBUTES set
SEQUENCE = SEQUENCE + 1
where ITEM_TYPE = itemtype
and SEQUENCE = i;
update WF_ITEM_ATTRIBUTES set
SEQUENCE = newseq
where ITEM_TYPE = itemtype
and SEQUENCE = -1;
select ITEM_TYPE||':'||NAME, DISPLAY_NAME, NAME
into conflict_name, l_dname, l_name
from WF_ITEM_ATTRIBUTES_VL
where DISPLAY_NAME = x_display_name
and ITEM_TYPE = x_item_type
and NAME <> x_name;
select ITEM_TYPE||':'||NAME, DISPLAY_NAME
into conflict_name, l_dname
from WF_ITEM_ATTRIBUTES_VL
where DISPLAY_NAME = n_dname
and ITEM_TYPE = x_item_type
and NAME <> l_name;
select PROTECT_LEVEL, CUSTOM_LEVEL, SEQUENCE
into protection_level, customization_level, old_sequence
from WF_ITEM_ATTRIBUTES_VL
where ITEM_TYPE = x_item_type
and NAME = x_name;
Wf_Item_Attributes_Pkg.Update_Row(
x_item_type => x_item_type,
x_name => x_name,
x_sequence => x_sequence,
x_type => x_type,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_subtype => x_subtype,
x_format => x_format,
x_text_default => l_text_default,
x_number_default => l_number_default,
x_date_default => l_date_default,
x_display_name => n_dname,
x_description => x_description
);
select nvl(max(SEQUENCE), -1)+1
into old_sequence
from WF_ITEM_ATTRIBUTES
where ITEM_TYPE = x_item_type;
Wf_Item_Attributes_Pkg.Insert_Row(
x_rowid => row_id,
x_item_type => x_item_type,
x_name => x_name,
x_sequence => x_sequence,
x_type => x_type,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_subtype => x_subtype,
x_format => x_format,
x_text_default => l_text_default,
x_number_default => l_number_default,
x_date_default => l_date_default,
x_display_name => n_dname,
x_description => x_description
);
select LOOKUP_TYPE, DISPLAY_NAME, LOOKUP_TYPE
into conflict_name, l_dname, l_name
from WF_LOOKUP_TYPES
where DISPLAY_NAME = x_display_name
and LOOKUP_TYPE <> x_lookup_type;
select LOOKUP_TYPE, DISPLAY_NAME
into conflict_name, l_dname
from WF_LOOKUP_TYPES
where DISPLAY_NAME = n_dname
and LOOKUP_TYPE <> l_name;
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_LOOKUP_TYPES
where LOOKUP_TYPE = x_lookup_type;
Wf_Lookup_Types_Pkg.Update_Row(
x_lookup_type => x_lookup_type,
x_item_type => x_item_type,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_display_name => n_dname,
x_description => x_description
);
Wf_Lookup_Types_Pkg.Insert_Row(
x_rowid => row_id,
x_lookup_type => x_lookup_type,
x_item_type => x_item_type,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_display_name => n_dname,
x_description => x_description
);
select LOOKUP_TYPE||':'||LOOKUP_CODE, MEANING, LOOKUP_CODE
into conflict_name, l_dname, l_name
from WF_LOOKUPS
where MEANING = x_meaning
and LOOKUP_TYPE = x_lookup_type
and LOOKUP_CODE <> x_lookup_code;
select LOOKUP_TYPE||':'||LOOKUP_CODE, MEANING
into conflict_name, l_dname
from WF_LOOKUPS
where MEANING = n_dname
and LOOKUP_TYPE = x_lookup_type
and LOOKUP_CODE <> l_name;
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_LOOKUPS
where LOOKUP_TYPE = x_lookup_type
and LOOKUP_CODE = x_lookup_code;
Wf_Lookups_Pkg.Update_Row(
x_lookup_type => x_lookup_type,
x_lookup_code => x_lookup_code,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_meaning => n_dname,
x_description => x_description
);
Wf_Lookups_Pkg.Insert_Row(
x_rowid => row_id,
x_lookup_type => x_lookup_type,
x_lookup_code => x_lookup_code,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_meaning => n_dname,
x_description => x_description
);
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_MESSAGES_VL
where TYPE = x_type
and NAME = x_name;
Wf_Messages_Pkg.Update_Row(
x_type => x_type,
x_name => x_name,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_default_priority => x_default_priority,
x_read_role => x_read_role,
x_write_role => x_write_role,
x_display_name => x_display_name,
x_description => x_description,
x_subject => x_subject,
x_body => x_body,
x_html_body => x_html_body
);
Wf_Messages_Pkg.Insert_Row(
x_rowid => row_id,
x_type => x_type,
x_name => x_name,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_default_priority => x_default_priority,
x_read_role => x_read_role,
x_write_role => x_write_role,
x_display_name => x_display_name,
x_description => x_description,
x_subject => x_subject,
x_body => x_body,
x_html_body => x_html_body
);
update WF_MESSAGE_ATTRIBUTES set
SEQUENCE = -1
where MESSAGE_TYPE = msgtype
and MESSAGE_NAME = msgname
and SEQUENCE = oldseq;
update WF_MESSAGE_ATTRIBUTES set
SEQUENCE = SEQUENCE - 1
where MESSAGE_TYPE = msgtype
and MESSAGE_NAME = msgname
and SEQUENCE = i;
update WF_MESSAGE_ATTRIBUTES set
SEQUENCE = SEQUENCE + 1
where MESSAGE_TYPE = msgtype
and MESSAGE_NAME = msgname
and SEQUENCE = i;
update WF_MESSAGE_ATTRIBUTES set
SEQUENCE = newseq
where MESSAGE_TYPE = msgtype
and MESSAGE_NAME = msgname
and SEQUENCE = -1;
select MESSAGE_TYPE||':'||MESSAGE_NAME||':'||NAME, DISPLAY_NAME, NAME
into conflict_name, l_dname, l_name
from WF_MESSAGE_ATTRIBUTES_VL
where DISPLAY_NAME = n_dname
and MESSAGE_TYPE = x_message_type
and MESSAGE_NAME = x_message_name
and NAME <> x_name;
select MESSAGE_TYPE||':'||MESSAGE_NAME||':'||NAME, DISPLAY_NAME
into conflict_name, l_dname
from WF_MESSAGE_ATTRIBUTES_VL
where DISPLAY_NAME = n_dname
and MESSAGE_TYPE = x_message_type
and MESSAGE_NAME = x_message_name
and NAME <> l_name;
select PROTECT_LEVEL, CUSTOM_LEVEL, SEQUENCE
into protection_level, customization_level, old_sequence
from WF_MESSAGE_ATTRIBUTES_VL
where MESSAGE_TYPE = x_message_type
and MESSAGE_NAME = x_message_name
and NAME = x_name;
Wf_Message_Attributes_Pkg.Update_Row(
x_message_type => x_message_type,
x_message_name => x_message_name,
x_name => x_name,
x_sequence => x_sequence,
x_type => x_type,
x_subtype => x_subtype,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_format => x_format,
x_text_default => l_text_default,
x_number_default => l_number_default,
x_date_default => l_date_default,
x_value_type => x_value_type,
x_display_name => n_dname,
x_description => x_description,
x_attach => x_attach
);
select nvl(max(SEQUENCE), -1)+1
into old_sequence
from WF_MESSAGE_ATTRIBUTES
where MESSAGE_TYPE = x_message_type
and MESSAGE_NAME = x_message_name;
Wf_Message_Attributes_Pkg.Insert_Row(
x_rowid => row_id,
x_message_type => x_message_type,
x_message_name => x_message_name,
x_name => x_name,
x_sequence => x_sequence,
x_type => x_type,
x_subtype => x_subtype,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_format => x_format,
x_text_default => l_text_default,
x_number_default => l_number_default,
x_date_default => l_date_default,
x_value_type => x_value_type,
x_display_name => n_dname,
x_description => x_description,
x_attach => x_attach
);
noinsert pls_integer := -1; /* always insert by default */
select PROTECT_LEVEL, CUSTOM_LEVEL, VERSION, BEGIN_DATE, END_DATE
into protection_level, customization_level,
old_version, old_begin_date, old_end_date
from WF_ACTIVITIES_VL
where ITEM_TYPE = x_item_type
and NAME = x_name
and x_effective_date >= BEGIN_DATE
and x_effective_date < nvl(END_DATE, x_effective_date+1);
select min(BEGIN_DATE)
into old_end_date
from WF_ACTIVITIES_VL
where ITEM_TYPE = x_item_type
and NAME = x_name
and BEGIN_DATE >= x_effective_date;
select nvl(max(VERSION), 1), count(1)
into new_version, noinsert
from WF_ACTIVITIES
where ITEM_TYPE = x_item_type
and NAME = x_name;
select nvl(max(VERSION), 0) + 1
into new_version
from WF_ACTIVITIES
where ITEM_TYPE = x_item_type
and NAME = x_name;
update WF_ACTIVITIES set
END_DATE = x_effective_date
where ITEM_TYPE = x_item_type
and NAME = x_name
and VERSION = old_version;
select ITEM_TYPE||':'||NAME||':'||to_char(VERSION), DISPLAY_NAME, NAME
into conflict_name, l_dname, l_name
from WF_ACTIVITIES_VL
where DISPLAY_NAME = n_dname
and ITEM_TYPE = x_item_type
and x_effective_date >= BEGIN_DATE
and x_effective_date < nvl(END_DATE, x_effective_date+1)
and NAME <> x_name;
select ITEM_TYPE||':'||NAME||':'||to_char(VERSION), DISPLAY_NAME
into conflict_name, l_dname
from WF_ACTIVITIES_VL
where DISPLAY_NAME = n_dname
and ITEM_TYPE = x_item_type
and x_effective_date >= BEGIN_DATE
and x_effective_date < nvl(END_DATE, x_effective_date+1)
and NAME <> l_name;
if (noinsert <= 0) then
if (x_event_filter is null and x_event_type = null) then
Wf_Activities_Pkg.Insert_Row(
x_rowid => row_id,
x_item_type => x_item_type,
x_name => x_name,
x_version => new_version,
x_type => x_type,
x_rerun => x_rerun,
x_expand_role => x_expand_role,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_begin_date => x_effective_date,
x_end_date => old_end_date,
x_function => x_function,
x_function_type => x_function_type,
x_result_type => x_result_type,
x_cost => x_cost,
x_read_role => x_read_role,
x_write_role => x_write_role,
x_execute_role => x_execute_role,
x_icon_name => x_icon_name,
x_message => x_message,
x_error_process => x_error_process,
x_display_name => n_dname,
x_description => x_description,
x_error_item_type => x_error_item_type,
x_runnable_flag => x_runnable_flag
);
Wf_Activities_Pkg.Insert_Row(
x_rowid => row_id,
x_item_type => x_item_type,
x_name => x_name,
x_version => new_version,
x_type => x_type,
x_rerun => x_rerun,
x_expand_role => x_expand_role,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_begin_date => x_effective_date,
x_end_date => old_end_date,
x_function => x_function,
x_function_type => x_function_type,
x_result_type => x_result_type,
x_cost => x_cost,
x_read_role => x_read_role,
x_write_role => x_write_role,
x_execute_role => x_execute_role,
x_icon_name => x_icon_name,
x_message => x_message,
x_error_process => x_error_process,
x_display_name => n_dname,
x_description => x_description,
x_error_item_type => x_error_item_type,
x_runnable_flag => x_runnable_flag,
x_event_filter => x_event_filter,
x_event_type => x_event_type
);
update WF_ACTIVITIES
set BEGIN_DATE = x_effective_date,
END_DATE = to_date(NULL)
where ITEM_TYPE = x_item_type
and NAME = x_name
and VERSION = new_version;
update WF_ACTIVITIES
set END_DATE = to_date(NULL)
where ITEM_TYPE = x_item_type
and NAME = x_name
and VERSION = new_version;
update WF_ACTIVITY_ATTRIBUTES set
SEQUENCE = -1
where ACTIVITY_ITEM_TYPE = acttype
and ACTIVITY_NAME = actname
and ACTIVITY_VERSION = actver
and SEQUENCE = oldseq;
update WF_ACTIVITY_ATTRIBUTES set
SEQUENCE = SEQUENCE - 1
where ACTIVITY_ITEM_TYPE = acttype
and ACTIVITY_NAME = actname
and ACTIVITY_VERSION = actver
and SEQUENCE = i;
update WF_ACTIVITY_ATTRIBUTES set
SEQUENCE = SEQUENCE + 1
where ACTIVITY_ITEM_TYPE = acttype
and ACTIVITY_NAME = actname
and ACTIVITY_VERSION = actver
and SEQUENCE = i;
update WF_ACTIVITY_ATTRIBUTES set
SEQUENCE = newseq
where ACTIVITY_ITEM_TYPE = acttype
and ACTIVITY_NAME = actname
and ACTIVITY_VERSION = actver
and SEQUENCE = -1;
select PROTECT_LEVEL, CUSTOM_LEVEL, SEQUENCE
into protection_level, customization_level, old_sequence
from WF_ACTIVITY_ATTRIBUTES_VL
where ACTIVITY_ITEM_TYPE = x_activity_item_type
and ACTIVITY_NAME = x_activity_name
and ACTIVITY_VERSION = x_activity_version
and NAME = x_name;
Wf_Activity_Attributes_Pkg.Update_Row(
x_activity_item_type => x_activity_item_type,
x_activity_name => x_activity_name,
x_activity_version => x_activity_version,
x_name => x_name,
x_sequence => x_sequence,
x_type => x_type,
x_value_type => x_value_type,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_subtype => x_subtype,
x_format => x_format,
x_text_default => l_text_default,
x_number_default => l_number_default,
x_date_default => l_date_default,
x_display_name => x_display_name,
x_description => x_description
);
select nvl(max(SEQUENCE), -1)+1
into old_sequence
from WF_ACTIVITY_ATTRIBUTES
where ACTIVITY_ITEM_TYPE = x_activity_item_type
and ACTIVITY_NAME = x_activity_name
and ACTIVITY_VERSION = x_activity_version;
Wf_Activity_Attributes_Pkg.Insert_Row(
x_rowid => row_id,
x_activity_item_type => x_activity_item_type,
x_activity_name => x_activity_name,
x_activity_version => x_activity_version,
x_name => x_name,
x_sequence => x_sequence,
x_type => x_type,
x_value_type => x_value_type,
x_protect_level => x_protect_level,
x_custom_level => x_custom_level,
x_subtype => x_subtype,
x_format => x_format,
x_text_default => l_text_default,
x_number_default => l_number_default,
x_date_default => l_date_default,
x_display_name => x_display_name,
x_description => x_description
);
noinsert pls_integer := 1;
select WF_PROCESS_ACTIVITIES_S.NEXTVAL
into x_instance_id
from sys.dual;
raise NO_DATA_FOUND; --jump to insert
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_PROCESS_ACTIVITIES
where INSTANCE_ID = x_instance_id;
select NAME into l_perform_role
from WF_ROLES
where DISPLAY_NAME = x_perform_role
and rownum < 2;
update WF_PROCESS_ACTIVITIES set
PROCESS_ITEM_TYPE = x_process_item_type,
PROCESS_NAME = x_process_name,
PROCESS_VERSION = x_process_version,
ACTIVITY_ITEM_TYPE = x_activity_item_type,
ACTIVITY_NAME = x_activity_name,
INSTANCE_LABEL = x_instance_label,
PROTECT_LEVEL = x_protect_level,
CUSTOM_LEVEL = x_custom_level,
START_END = x_start_end,
DEFAULT_RESULT = x_default_result,
ICON_GEOMETRY = x_icon_geometry,
PERFORM_ROLE = l_perform_role,
PERFORM_ROLE_TYPE = l_perform_role_type,
USER_COMMENT = x_user_comment
where INSTANCE_ID = x_instance_id;
select NAME into l_perform_role
from WF_ROLES
where DISPLAY_NAME = x_perform_role
and rownum < 2;
select nvl(max(VERSION), 1), count(1)
into new_version, noinsert
from WF_ACTIVITIES
where ITEM_TYPE = x_process_item_type
and NAME = 'ROOT';
if (noinsert <= 0) then
WF_LOAD.UPLOAD_ACTIVITY(
x_item_type=>x_process_item_type,
x_name=>'ROOT',
x_type=>'FOLDER',
x_display_name=>'ROOT',
x_description=>'',
x_rerun=>'RESET',
x_protect_level=>x_protect_level,
x_custom_level=>x_custom_level,
x_effective_date=>sysdate,
x_function=>'',
x_function_type =>'',
x_result_type=>'*',
x_cost=>0,
x_read_role=>'',
x_write_role=>'',
x_execute_role=>'',
x_icon_name=>'ROOT',
x_message=>'',
x_error_process=>'',
x_expand_role=>'N',
x_error_item_type =>'WFERROR',
x_runnable_flag =>'N',
x_event_filter => '',
x_event_type => '',
x_log_message=>dummy_log_message,
x_version=>dummy_version,
x_level_error=>dummy_level_error);
select count(1)
into noinsert
from WF_PROCESS_ACTIVITIES
where PROCESS_NAME = 'ROOT'
and ACTIVITY_ITEM_TYPE = x_process_item_type
and ACTIVITY_NAME = x_activity_name
and rownum=1;
if (x_process_name <> 'ROOT' or noinsert <= 0) then
-- Insert new row
insert into WF_PROCESS_ACTIVITIES (
PROCESS_ITEM_TYPE,
PROCESS_NAME,
PROCESS_VERSION,
ACTIVITY_ITEM_TYPE,
ACTIVITY_NAME,
INSTANCE_ID,
INSTANCE_LABEL,
PROTECT_LEVEL,
CUSTOM_LEVEL,
START_END,
DEFAULT_RESULT,
ICON_GEOMETRY,
PERFORM_ROLE,
PERFORM_ROLE_TYPE,
USER_COMMENT
) values (
x_process_item_type,
x_process_name,
x_process_version,
x_activity_item_type,
x_activity_name,
x_instance_id,
x_instance_label,
x_protect_level,
x_custom_level,
x_start_end,
x_default_result,
x_icon_geometry,
l_perform_role,
l_perform_role_type,
x_user_comment
);
if (x_process_name <> 'ROOT' and noinsert <= 0) then
-- Insert a root process activity
WF_LOAD.UPLOAD_PROCESS_ACTIVITY(
x_process_item_type=>x_process_item_type,
x_process_name=>'ROOT',
x_process_version=>new_version,
x_activity_item_type=>x_process_item_type,
x_activity_name=>x_process_name,
x_instance_id=>root_instance_id,
x_instance_label=>x_process_name,
x_protect_level=>x_protect_level,
x_custom_level=>x_custom_level,
x_start_end=>'',
x_default_result=>'',
x_icon_geometry=>'',
x_perform_role=>'',
x_perform_role_type=>'CONSTANT',
x_user_comment=>'',
x_level_error=>dummy_level_error
);
select WAA.TYPE
into l_type
from WF_PROCESS_ACTIVITIES WPA, WF_ACTIVITIES WA,
WF_ACTIVITY_ATTRIBUTES WAA
where WPA.INSTANCE_ID = x_process_activity_id
and WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
and WPA.ACTIVITY_NAME = WA.NAME
and x_effective_date >= WA.BEGIN_DATE
and x_effective_date < nvl(WA.END_DATE, x_effective_date+1)
and WA.ITEM_TYPE = WAA.ACTIVITY_ITEM_TYPE
and WA.NAME = WAA.ACTIVITY_NAME
and WA.VERSION = WAA.ACTIVITY_VERSION
and WAA.NAME = x_name;
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_ACTIVITY_ATTR_VALUES
where PROCESS_ACTIVITY_ID = x_process_activity_id
and NAME = x_name;
update WF_ACTIVITY_ATTR_VALUES set
PROTECT_LEVEL = x_protect_level,
CUSTOM_LEVEL = x_custom_level,
TEXT_VALUE = l_text_value,
NUMBER_VALUE = l_number_value,
DATE_VALUE = l_date_value,
VALUE_TYPE = x_value_type
where PROCESS_ACTIVITY_ID = x_process_activity_id
and NAME = x_name;
insert into WF_ACTIVITY_ATTR_VALUES (
PROCESS_ACTIVITY_ID,
NAME,
PROTECT_LEVEL,
CUSTOM_LEVEL,
TEXT_VALUE,
NUMBER_VALUE,
DATE_VALUE,
VALUE_TYPE
) values (
x_process_activity_id,
x_name,
x_protect_level,
x_custom_level,
l_text_value,
l_number_value,
l_date_value,
x_value_type
);
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_ACTIVITY_TRANSITIONS
where FROM_PROCESS_ACTIVITY = x_from_process_activity
and RESULT_CODE = x_result_code
and TO_PROCESS_ACTIVITY = x_to_process_activity;
update WF_ACTIVITY_TRANSITIONS set
FROM_PROCESS_ACTIVITY = x_from_process_activity,
RESULT_CODE = x_result_code,
TO_PROCESS_ACTIVITY = x_to_process_activity,
PROTECT_LEVEL = x_protect_level,
CUSTOM_LEVEL = x_custom_level,
ARROW_GEOMETRY = x_arrow_geometry
where FROM_PROCESS_ACTIVITY = x_from_process_activity
and RESULT_CODE = x_result_code
and TO_PROCESS_ACTIVITY = x_to_process_activity;
insert into WF_ACTIVITY_TRANSITIONS (
FROM_PROCESS_ACTIVITY,
RESULT_CODE,
TO_PROCESS_ACTIVITY,
PROTECT_LEVEL,
CUSTOM_LEVEL,
ARROW_GEOMETRY
) values (
x_from_process_activity,
x_result_code,
x_to_process_activity,
x_protect_level,
x_custom_level,
x_arrow_geometry
);
procedure DELETE_LOOKUP_TYPE(
x_lookup_type in varchar2,
x_level_error out NOCOPY number
) is
protection_level number;
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_LOOKUP_TYPES
where LOOKUP_TYPE = x_lookup_type;
Delete_Lookups(x_lookup_type, x_level_error);
Wf_Lookup_Types_Pkg.Delete_Row(x_lookup_type => x_lookup_type);
Wf_Core.Context('Wf_Load', 'Delete_Lookup_Type', x_lookup_type);
end DELETE_LOOKUP_TYPE;
procedure DELETE_LOOKUP(
x_lookup_type in varchar2,
x_lookup_code in varchar2,
x_level_error out NOCOPY number
) is
protection_level number;
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_LOOKUPS
where LOOKUP_TYPE = x_lookup_type
and LOOKUP_CODE = x_lookup_code;
Wf_Lookups_Pkg.Delete_Row(
x_lookup_type => x_lookup_type,
x_lookup_code => x_lookup_code
);
Wf_Core.Context('Wf_Load', 'Delete_Lookup', x_lookup_type, x_lookup_code);
end DELETE_LOOKUP;
procedure DELETE_LOOKUPS(
x_lookup_type in varchar2,
x_level_error out NOCOPY number
) is
protection_level number;
select MIN(protect_level), MAX(custom_level)
into protection_level, customization_level
from WF_LOOKUP_TYPES_TL
where LOOKUP_TYPE = X_LOOKUP_TYPE;
delete from WF_LOOKUPS_TL
where LOOKUP_TYPE = X_LOOKUP_TYPE;
wf_core.context('WF_LOAD', 'DELETE_LOOKUPS');
end DELETE_LOOKUPS;
procedure DELETE_ITEM_TYPE(
x_name in varchar2,
x_level_error out NOCOPY number
) is
protection_level number;
select PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE
into protection_level, customization_level, l_persistence_type
from WF_ITEM_TYPES_VL
where NAME = x_name;
Delete_Item_Attributes(x_name, x_level_error);
select 1
into dummy
from sys.dual
where not exists
(select 1
from WF_LOOKUP_TYPES
where ITEM_TYPE = x_name)
and not exists
(select 1
from WF_ACTIVITIES
where ITEM_TYPE = x_name)
and not exists
(select 1
from WF_MESSAGES
where TYPE = x_name);
Wf_Item_Types_Pkg.Delete_Row(x_name=>x_name);
Wf_Core.Context('Wf_Load', 'Delete_Item_Type', x_name);
end DELETE_ITEM_TYPE;
procedure DELETE_ITEM_ATTRIBUTE(
x_item_type in varchar2,
x_name in varchar2,
x_level_error out NOCOPY number
) is
protection_level number;
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_ITEM_ATTRIBUTES_VL
where ITEM_TYPE = x_item_type
and NAME = x_name;
Wf_Item_Attributes_Pkg.Delete_Row(
x_item_type => x_item_type,
x_name => x_name);
Wf_Core.Context('Wf_Load', 'Delete_Item_Attribute', x_item_type, x_name);
end DELETE_ITEM_ATTRIBUTE;
procedure DELETE_ITEM_ATTRIBUTES(
x_item_type in varchar2,
x_level_error out NOCOPY number
) is
protection_level number;
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_ITEM_TYPES_VL
where NAME = x_item_type;
delete from WF_ITEM_ATTRIBUTES_TL
where ITEM_TYPE = X_ITEM_TYPE;
delete from WF_ITEM_ATTRIBUTES
where ITEM_TYPE = X_ITEM_TYPE;
Wf_Core.Context('Wf_Load', 'Delete_Item_Attributes', x_item_type);
end DELETE_ITEM_ATTRIBUTES;
procedure DELETE_MESSAGE(
x_type in varchar2,
x_name in varchar2,
x_level_error out NOCOPY number
) is
protection_level number;
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_MESSAGES
where TYPE = x_type
and NAME = x_name;
Delete_Message_Attributes(x_type, x_name, x_level_error);
select 1
into dummy
from sys.dual
where not exists
(select 1
from WF_ACTIVITIES
where ITEM_TYPE = x_type
and MESSAGE = x_name)
and not exists
(select 1
from WF_NOTIFICATIONS
where MESSAGE_TYPE = x_type
and MESSAGE_NAME = x_name);
Wf_Messages_Pkg.Delete_Row(x_type => x_type, x_name => x_name);
Wf_Core.Context('Wf_Load', 'Delete_Message', x_type, x_name);
end DELETE_MESSAGE;
procedure DELETE_MESSAGE_ATTRIBUTE(
x_message_type in varchar2,
x_message_name in varchar2,
x_name in varchar2,
x_level_error out NOCOPY number
) is
protection_level number;
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_MESSAGE_ATTRIBUTES_VL
where MESSAGE_TYPE = x_message_type
and MESSAGE_NAME = x_message_name
and NAME = x_name;
Wf_Message_Attributes_Pkg.Delete_Row(
x_message_type => x_message_type,
x_message_name => x_message_name,
x_name => x_name);
Wf_Core.Context('Wf_Load', 'Delete_Message_Attribute', x_message_type,
x_message_name, x_name);
end DELETE_MESSAGE_ATTRIBUTE;
procedure DELETE_MESSAGE_ATTRIBUTES(
x_message_type in varchar2,
x_message_name in varchar2,
x_level_error out NOCOPY number
) is
protection_level NUMBER;
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_MESSAGES
where TYPE = x_message_type
and NAME = x_message_name;
delete from WF_MESSAGE_ATTRIBUTES_TL
where MESSAGE_TYPE = x_message_type
and MESSAGE_NAME = x_message_name;
delete from WF_MESSAGE_ATTRIBUTES
where MESSAGE_TYPE = x_message_type
and MESSAGE_NAME = x_message_name;
Wf_Core.Context('Wf_Load', 'Delete_Message_Attributes', x_message_type,
x_message_name);
end DELETE_MESSAGE_ATTRIBUTES;
procedure DELETE_ACTIVITY(
x_item_type in varchar2,
x_name in varchar2,
x_level_error out NOCOPY number
) is
protection_level number;
select PROTECT_LEVEL, CUSTOM_LEVEL
into protection_level, customization_level
from WF_ACTIVITIES_VL
where ITEM_TYPE = x_item_type
and NAME = x_name
and END_DATE is null;
update WF_ACTIVITIES set
end_date = sysdate
where ITEM_TYPE = x_item_type
and NAME = x_name
and END_DATE is null;
select PERSISTENCE_TYPE
into l_persistence_type
from WF_ITEM_TYPES
where NAME = x_item_type;
Wf_Core.Context('Wf_Load', 'Delete_Activity', x_item_type, x_name);
end DELETE_ACTIVITY;
procedure Delete_Transition(
p_previous_step in number ,
p_next_step in number ,
p_result_code in varchar2 )
is
begin
-- Reset any caches that might be running.
WF_CACHE.Reset;
delete WF_ACTIVITY_TRANSITIONS
where FROM_PROCESS_ACTIVITY = p_previous_step
and RESULT_CODE = nvl(p_result_code, RESULT_CODE);
delete WF_ACTIVITY_TRANSITIONS
where TO_PROCESS_ACTIVITY = p_next_step
and RESULT_CODE = nvl(p_result_code, RESULT_CODE);
delete WF_ACTIVITY_TRANSITIONS
where FROM_PROCESS_ACTIVITY = p_previous_step
and RESULT_CODE = nvl(p_result_code, RESULT_CODE)
and TO_PROCESS_ACTIVITY = p_next_step;
Wf_Core.Context('Wf_Load', 'Delete_Transition', p_previous_step,
p_next_step, p_result_code);
select ICON_GEOMETRY, ACTIVITY_NAME, INSTANCE_LABEL
into l_icon_geometry, p_activity_name, p_instance_label
from WF_PROCESS_ACTIVITIES
where instance_id = p_activity_instance;
procedure UPDATE_MESSAGE (
p_type in varchar2,
p_name in varchar2,
p_subject in varchar2,
p_body in varchar2,
p_html_body in varchar2,
p_level_error out NOCOPY number
)
is
l_protect_level number;
select PROTECT_LEVEL,
CUSTOM_LEVEL,
DEFAULT_PRIORITY,
DISPLAY_NAME,
DESCRIPTION,
SUBJECT,
BODY,
HTML_BODY
into
p_protect_level,
p_custom_level,
p_default_priority,
p_display_name,
p_description,
p_subject,
p_body,
p_html_body
from WF_MESSAGES_VL
where TYPE = p_type
and NAME = p_name;
wf_selector varchar2(240),
read_role varchar2(320),
write_role varchar2(320),
execute_role varchar2(320),
persistence_type varchar2(8),
persistence_days varchar2(8));
select PROTECT_LEVEL, CUSTOM_LEVEL, NAME, DISPLAY_NAME, DESCRIPTION,
TYPE, SUBTYPE, FORMAT, TEXT_DEFAULT,
to_char(NUMBER_DEFAULT) NUMBER_DEFAULT,
to_char(DATE_DEFAULT, 'YYYY/MM/DD HH24:MI:SS') DATE_DEFAULT,
SEQUENCE
from WF_ITEM_ATTRIBUTES_VL
where ITEM_TYPE = itt
order by SEQUENCE;
select PROTECT_LEVEL, CUSTOM_LEVEL, LOOKUP_TYPE, DISPLAY_NAME,
DESCRIPTION
from WF_LOOKUP_TYPES
where ITEM_TYPE = itt
order by LOOKUP_TYPE;
select PROTECT_LEVEL, CUSTOM_LEVEL, LOOKUP_CODE, MEANING, DESCRIPTION
from WF_LOOKUPS
where LOOKUP_TYPE = lut
order by LOOKUP_CODE;
select PROTECT_LEVEL, CUSTOM_LEVEL, NAME, DISPLAY_NAME, DESCRIPTION,
SUBJECT, BODY, DEFAULT_PRIORITY, READ_ROLE, WRITE_ROLE,
HTML_BODY
from WF_MESSAGES_VL
where TYPE = itt
order by TYPE, NAME;
select PROTECT_LEVEL, CUSTOM_LEVEL, NAME, DISPLAY_NAME, DESCRIPTION,
TYPE, SUBTYPE, FORMAT, TEXT_DEFAULT,
to_char(NUMBER_DEFAULT) NUMBER_DEFAULT,
to_char(DATE_DEFAULT, 'YYYY/MM/DD HH24:MI:SS') DATE_DEFAULT,
VALUE_TYPE, ATTACH, SEQUENCE
from WF_MESSAGE_ATTRIBUTES_VL
where MESSAGE_TYPE = itt and MESSAGE_NAME = msg
order by SEQUENCE;
select PROTECT_LEVEL, CUSTOM_LEVEL, NAME, DISPLAY_NAME, DESCRIPTION,
ITEM_TYPE, VERSION, TYPE, RERUN, FUNCTION, RESULT_TYPE, COST,
ICON_NAME, MESSAGE, ERROR_PROCESS, EXPAND_ROLE,
READ_ROLE, WRITE_ROLE, EXECUTE_ROLE,
to_char(BEGIN_DATE, 'YYYY/MM/DD HH24:MI:SS') EFFECTIVE_DATE,
ERROR_ITEM_TYPE, RUNNABLE_FLAG, FUNCTION_TYPE,
EVENT_NAME, DIRECTION
from WF_ACTIVITIES_VL
where sysdate >= BEGIN_DATE
and (sysdate < END_DATE or END_DATE is null)
and ITEM_TYPE = itt
order by item_type, name;
select PROTECT_LEVEL, CUSTOM_LEVEL, NAME, DISPLAY_NAME, DESCRIPTION,
TYPE, SUBTYPE, FORMAT, TEXT_DEFAULT,
to_char(NUMBER_DEFAULT) NUMBER_DEFAULT,
to_char(DATE_DEFAULT, 'YYYY/MM/DD HH24:MI:SS') DATE_DEFAULT,
VALUE_TYPE, SEQUENCE
from WF_ACTIVITY_ATTRIBUTES_VL
where ACTIVITY_ITEM_TYPE = itt
and ACTIVITY_NAME = actname
and ACTIVITY_VERSION = ver
order by SEQUENCE;
select ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID, START_END,
DEFAULT_RESULT, ICON_GEOMETRY, PERFORM_ROLE,
USER_COMMENT, PERFORM_ROLE_TYPE, INSTANCE_LABEL,
PROTECT_LEVEL, CUSTOM_LEVEL
from WF_PROCESS_ACTIVITIES
where PROCESS_ITEM_TYPE = itt
and PROCESS_NAME = actname
and PROCESS_VERSION = ver
order by INSTANCE_ID;
select RESULT_CODE, TO_PROCESS_ACTIVITY, ARROW_GEOMETRY,
PROTECT_LEVEL, CUSTOM_LEVEL
from WF_ACTIVITY_TRANSITIONS
where FROM_PROCESS_ACTIVITY = id
order by RESULT_CODE;
select NAME, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL,
TEXT_VALUE,
to_char(NUMBER_VALUE) NUMBER_VALUE,
to_char(DATE_VALUE, 'YYYY/MM/DD HH24:MI:SS') DATE_VALUE
from WF_ACTIVITY_ATTR_VALUES
where PROCESS_ACTIVITY_ID = id;
select PROTECT_LEVEL, CUSTOM_LEVEL, NAME, DISPLAY_NAME, DESCRIPTION,
WF_SELECTOR, READ_ROLE, WRITE_ROLE, EXECUTE_ROLE,
PERSISTENCE_TYPE,
to_char(PERSISTENCE_DAYS) PERSISTENCE_DAYS
into v_itt
from WF_ITEM_TYPES_VL
where NAME = p_item_type;
Wf_Core.Context('Wf_Load', 'Selecting ITEM_TYPE', p_item_type);
select DISPLAY_NAME
into l_dname
from WF_ITEM_TYPES_VL
where NAME = p_destination_item_type;
x_wf_selector=> v_itt.wf_selector,
x_read_role=> v_itt.read_role,
x_write_role=> v_itt.write_role,
x_execute_role=> v_itt.execute_role,
x_persistence_type=> v_itt.persistence_type,
x_persistence_days=> v_itt.persistence_days,
x_level_error=> l_level_error
);
select INSTANCE_ID into v_npac(i)
from WF_PROCESS_ACTIVITIES
where INSTANCE_LABEL = pacr.instance_label
and PROCESS_ITEM_TYPE = p_destination_item_type
and PROCESS_NAME = l_pname
and PROCESS_VERSION = 1;
procedure Delete_Process_Activity(
p_step in number)
is
dummy number;
Wf_Load.Delete_Transition(p_previous_step=>p_step);
Wf_Load.Delete_Transition(p_next_step=>p_step);
delete from WF_ACTIVITY_ATTR_VALUES where PROCESS_ACTIVITY_ID = p_step;
delete from WF_PROCESS_ACTIVITIES where INSTANCE_ID = p_step;
Wf_Core.Context('Wf_Load', 'Delete_Process_Activity',
to_char(p_step));
select VALUE_TYPE,
nvl(nvl(TEXT_VALUE, to_char(NUMBER_VALUE)),
to_char(DATE_VALUE, 'YYYY/MM/DD HH24:MI:SS'))
into p_attribute_value_type,
p_attribute_value
from WF_ACTIVITY_ATTR_VALUES
where PROCESS_ACTIVITY_ID = p_process_instance_id
and NAME = p_attribute_name;
select TYPE,
TEXT_DEFAULT,
to_char(NUMBER_DEFAULT),
to_char(DATE_DEFAULT, 'YYYY/MM/DD HH24:MI:SS')
into p_attribute_type,
p_attribute_value,
l_number,
l_date
from WF_ITEM_ATTRIBUTES
where ITEM_TYPE = p_item_type
and NAME = p_attribute_name;
select DISPLAY_NAME,
DESCRIPTION,
TYPE,
RERUN,
PROTECT_LEVEL,
CUSTOM_LEVEL,
BEGIN_DATE,
FUNCTION,
FUNCTION_TYPE,
RESULT_TYPE,
COST,
READ_ROLE,
WRITE_ROLE,
EXECUTE_ROLE,
ICON_NAME,
MESSAGE,
ERROR_PROCESS,
EXPAND_ROLE,
ERROR_ITEM_TYPE,
RUNNABLE_FLAG,
VERSION
into p_display_name,
p_description,
p_type,
p_rerun,
p_protect_level,
p_custom_level,
p_begin_date,
p_function,
p_function_type,
p_result_type,
p_cost,
p_read_role,
p_write_role,
p_execute_role,
p_icon_name,
p_message,
p_error_process,
p_expand_role,
p_error_item_type,
p_runnable_flag,
p_version
from WF_ACTIVITIES_VL
where ITEM_TYPE = p_item_type
and NAME = p_name
and END_DATE is null;
procedure UPDATE_ACTIVITY (
p_item_type in varchar2,
p_name in varchar2,
p_display_name in varchar2,
p_description in varchar2 ,
p_expand_role in varchar2 ,
p_level_error out NOCOPY number)
is
conflict_name varchar2(240);
Wf_Core.Context('Wf_Load', 'Update_Activity Get_Activity');
select ITEM_TYPE||':'||NAME||':'||to_char(VERSION), DISPLAY_NAME, NAME
into conflict_name, l_dname, l_name
from WF_ACTIVITIES_VL
where DISPLAY_NAME = n_dname
and ITEM_TYPE = p_item_type
and l_begin_date >= BEGIN_DATE
and l_begin_date < nvl(END_DATE, l_begin_date+1)
and NAME <> p_name;
select ITEM_TYPE||':'||NAME||':'||to_char(VERSION), DISPLAY_NAME
into conflict_name, l_dname
from WF_ACTIVITIES_VL
where DISPLAY_NAME = n_dname
and ITEM_TYPE = p_item_type
and l_begin_date >= BEGIN_DATE
and l_begin_date < nvl(END_DATE, l_begin_date+1)
and NAME <> l_name;
update WF_ACTIVITIES
set expand_role = l_expand_role
where ITEM_TYPE = p_item_type
and NAME = p_name
and VERSION = l_version;
update WF_ACTIVITIES_TL
set DISPLAY_NAME = n_dname,
DESCRIPTION = l_description
where ITEM_TYPE = p_item_type
and NAME = p_name
and VERSION = l_version;
Wf_Core.Context('Wf_Load', 'Update_Activity', p_item_type, p_name,
p_display_name);
select INSTANCE_ID into id
from WF_PROCESS_ACTIVITIES
where INSTANCE_LABEL = p_instance_label
and PROCESS_NAME = p_process_name
and PROCESS_ITEM_TYPE = p_process_item_type
and PROCESS_VERSION = p_process_version;
select INSTANCE_ID into id
from WF_PROCESS_ACTIVITIES
where PROCESS_NAME = p_process_name
and PROCESS_ITEM_TYPE = p_process_item_type
and PROCESS_VERSION = p_process_version
and ACTIVITY_ITEM_TYPE = p_activity_item_type
and ACTIVITY_NAME = p_activity_name
and rownum = 1;
select ACTIVITY_NAME
into l_actname
from WF_PROCESS_ACTIVITIES
where INSTANCE_ID = p_instance_id;
select RESULT_CODE
from WF_ACTIVITY_TRANSITIONS
where FROM_PROCESS_ACTIVITY = from_act
and TO_PROCESS_ACTIVITY = to_act;
select TO_PROCESS_ACTIVITY
from WF_ACTIVITY_TRANSITIONS
where FROM_PROCESS_ACTIVITY = from_act
and RESULT_CODE = res_code;
select TO_PROCESS_ACTIVITY, RESULT_CODE
from WF_ACTIVITY_TRANSITIONS
where FROM_PROCESS_ACTIVITY = from_act;
select FROM_PROCESS_ACTIVITY
from WF_ACTIVITY_TRANSITIONS
where TO_PROCESS_ACTIVITY = to_act
and RESULT_CODE = res_code;
select FROM_PROCESS_ACTIVITY, RESULT_CODE
from WF_ACTIVITY_TRANSITIONS
where TO_PROCESS_ACTIVITY = to_act;
select NAME
from WF_ITEM_ATTRIBUTES
where ITEM_TYPE = p_item_type
and NAME like '%'||p_suffix;
select NAME
from WF_ACTIVITIES
where NAME like '%'||p_suffix
and ITEM_TYPE = p_item_type
and TYPE = 'NOTICE'
and END_DATE is null;
select NAME
from WF_MESSAGES
where NAME like '%'||p_suffix
and TYPE = p_item_type;
select INSTANCE_ID
from WF_PROCESS_ACTIVITIES
where PROCESS_NAME = p_process_name
and PROCESS_ITEM_TYPE = p_process_item_type
and PROCESS_VERSION = p_process_version;
select MEANING, DESCRIPTION, PROTECT_LEVEL, CUSTOM_LEVEL
into x_meaning, x_description, x_protect_level, x_custom_level
from WF_LOOKUPS
where LOOKUP_TYPE = x_lookup_type
and LOOKUP_CODE = x_lookup_code;
procedure UPDATE_LOOKUP(
x_lookup_type in varchar2,
x_lookup_code in varchar2,
x_meaning in varchar2 ,
x_description in varchar2 ,
x_protect_level in number ,
x_custom_level in number ,
x_level_error out NOCOPY number
)
is
l_meaning varchar2(80);
Wf_Core.Context('Wf_Load', 'Update_Lookup', x_lookup_type, x_lookup_code);
select LOOKUP_CODE
from WF_LOOKUPS
where LOOKUP_TYPE = p_lookup_type;
select WPA.ACTIVITY_ITEM_TYPE, WPA.ACTIVITY_NAME
from WF_PROCESS_ACTIVITIES WPA,
WF_ACTIVITIES WA
where WPA.PROCESS_ITEM_TYPE = p_process_item_type
and WPA.PROCESS_NAME = p_process_name
and WPA.PROCESS_VERSION = ver
and WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
and WPA.ACTIVITY_NAME = WA.NAME
and WA.TYPE = 'PROCESS'
and active_date >= WA.BEGIN_DATE
and active_date < nvl(WA.END_DATE, active_date+1);
select VERSION into m_version
from WF_ACTIVITIES
where ITEM_TYPE = p_process_item_type
and NAME = p_process_name
and active_date >= BEGIN_DATE
and active_date < nvl(END_DATE, active_date + 1);
select count(1) into n
from WF_PROCESS_ACTIVITIES
where PROCESS_ITEM_TYPE = p_process_item_type
and PROCESS_NAME = p_process_name
and PROCESS_VERSION = m_version
and ACTIVITY_ITEM_TYPE = nvl(p_activity_item_type, p_process_item_type)
and ACTIVITY_NAME = p_activity_name;