[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_ROW (
X_ROWID in out nocopy varchar2,
X_GUID in raw,
X_SYSTEM_GUID in raw,
X_SOURCE_TYPE in varchar2,
X_SOURCE_AGENT_GUID in raw,
X_EVENT_FILTER_GUID in raw,
X_PHASE in number,
X_STATUS in varchar2,
X_RULE_DATA in varchar2,
X_OUT_AGENT_GUID in raw,
X_TO_AGENT_GUID in raw,
X_PRIORITY in number,
X_RULE_FUNCTION in varchar2,
X_WF_PROCESS_TYPE in varchar2,
X_WF_PROCESS_NAME in varchar2,
X_PARAMETERS in varchar2,
X_OWNER_NAME in varchar2,
X_OWNER_TAG in varchar2,
X_CUSTOMIZATION_LEVEL in varchar2,
X_LICENSED_FLAG in varchar2,
X_DESCRIPTION in varchar2,
X_EXPRESSION in varchar2,
X_ACTION_CODE in varchar2,
X_ON_ERROR_CODE in varchar2,
X_JAVA_RULE_FUNC in varchar2,
X_MAP_CODE in varchar2,
X_STANDARD_CODE in varchar2,
X_STANDARD_TYPE in varchar2
) is
l_guid raw(16);
cursor C is select ROWID from wf_event_subscriptions where guid = x_guid;
l_guid := Find_Subscription(x_subscription_guid => insert_row.x_guid,
x_system_guid => insert_row.x_system_guid,
x_source_type => insert_row.x_source_type,
x_source_agent_guid => insert_row.x_source_agent_guid,
x_event_filter_guid => insert_row.x_event_filter_guid,
x_phase => insert_row.x_phase,
x_rule_data => insert_row.x_rule_data,
x_priority => insert_row.x_priority,
x_rule_function => insert_row.x_rule_function,
x_wf_process_type => insert_row.x_wf_process_type,
x_wf_process_name => insert_row.x_wf_process_name,
x_parameters => insert_row.x_parameters,
x_owner_name => insert_row.x_owner_name,
x_owner_tag => insert_row.x_owner_tag);
SELECT name
INTO l_event_name
FROM wf_events
WHERE guid = x_event_filter_guid;
insert into wf_event_subscriptions (
guid,
system_guid,
source_type,
source_agent_guid,
event_filter_guid,
phase,
status,
rule_data,
out_agent_guid,
to_agent_guid,
priority,
rule_function,
wf_process_type,
wf_process_name,
parameters,
owner_name,
owner_tag,
customization_level,
licensed_flag,
description,
expression,
action_code,
on_error_code,
java_rule_func,
map_code,
standard_code,
standard_type
) select X_GUID,
X_SYSTEM_GUID,
X_SOURCE_TYPE,
X_SOURCE_AGENT_GUID,
X_EVENT_FILTER_GUID,
X_PHASE,
X_STATUS,
X_RULE_DATA,
X_OUT_AGENT_GUID,
X_TO_AGENT_GUID,
X_PRIORITY,
l_rule_func,
X_WF_PROCESS_TYPE,
X_WF_PROCESS_NAME,
X_PARAMETERS,
X_OWNER_NAME,
X_OWNER_TAG,
X_CUSTOMIZATION_LEVEL,
l_licensed_flag,
X_DESCRIPTION,
X_EXPRESSION,
X_ACTION_CODE,
X_ON_ERROR_CODE,
X_JAVA_RULE_FUNC,
X_MAP_CODE,
X_STANDARD_CODE,
X_STANDARD_TYPE
from dual where not exists (
select 'duplicate'
from wf_event_subscriptions
where guid = X_GUID);
wf_core.context('Wf_Events_Subscriptions_Pkg', 'Insert_Row', x_guid,
x_system_guid, X_SOURCE_TYPE, X_SOURCE_AGENT_GUID);
end INSERT_ROW;
procedure UPDATE_ROW (
X_GUID in raw,
X_SYSTEM_GUID in raw,
X_SOURCE_TYPE in varchar2,
X_SOURCE_AGENT_GUID in raw,
X_EVENT_FILTER_GUID in raw,
X_PHASE in number,
X_STATUS in varchar2,
X_RULE_DATA in varchar2,
X_OUT_AGENT_GUID in raw,
X_TO_AGENT_GUID in raw,
X_PRIORITY in number,
X_RULE_FUNCTION in varchar2,
X_WF_PROCESS_TYPE in varchar2,
X_WF_PROCESS_NAME in varchar2,
X_PARAMETERS in varchar2,
X_OWNER_NAME in varchar2,
X_OWNER_TAG in varchar2,
X_CUSTOMIZATION_LEVEL in varchar2,
X_LICENSED_FLAG in varchar2,
X_DESCRIPTION in varchar2,
X_EXPRESSION in varchar2,
X_ACTION_CODE in varchar2,
X_ON_ERROR_CODE in varchar2,
X_JAVA_RULE_FUNC in varchar2,
X_MAP_CODE in varchar2,
X_STANDARD_CODE in varchar2,
X_STANDARD_TYPE in varchar2
) is
l_custom_level varchar2(1);
l_update_allowed varchar2(1) := 'Y';
l_guid := Find_Subscription(x_subscription_guid => update_row.x_guid,
x_system_guid => update_row.x_system_guid,
x_source_type => update_row.x_source_type,
x_source_agent_guid => update_row.x_source_agent_guid,
x_event_filter_guid => update_row.x_event_filter_guid,
x_phase => update_row.x_phase,
x_rule_data => update_row.x_rule_data,
x_priority => update_row.x_priority,
x_rule_function => update_row.x_rule_function,
x_wf_process_type => update_row.x_wf_process_type,
x_wf_process_name => update_row.x_wf_process_name,
x_parameters => update_row.x_parameters,
x_owner_name => update_row.x_owner_name,
x_owner_tag => update_row.x_owner_tag);
SELECT name
INTO l_event_name
FROM wf_events
WHERE guid = x_event_filter_guid;
Wf_Core.Context('Wf_Event_Subscriptions_Pkg', 'Update_Row');
update wf_event_subscriptions set
system_guid = X_SYSTEM_GUID,
source_type = X_SOURCE_TYPE,
source_agent_guid = X_SOURCE_AGENT_GUID,
event_filter_guid = X_EVENT_FILTER_GUID,
phase = X_PHASE,
status = X_STATUS,
rule_data = X_RULE_DATA,
out_agent_guid = X_OUT_AGENT_GUID,
to_agent_guid = X_TO_AGENT_GUID,
priority = X_PRIORITY,
rule_function = l_rule_func,
wf_process_type = X_WF_PROCESS_TYPE,
wf_process_name = X_WF_PROCESS_NAME,
parameters = X_PARAMETERS,
owner_name = X_OWNER_NAME,
owner_tag = X_OWNER_TAG,
description = X_DESCRIPTION,
customization_level = X_CUSTOMIZATION_LEVEL,
licensed_flag = l_licensed_flag,
expression = X_EXPRESSION,
action_code = X_ACTION_CODE,
on_error_code = X_ON_ERROR_CODE,
java_rule_func = X_JAVA_RULE_FUNC,
map_code = X_MAP_CODE,
standard_code = X_STANDARD_CODE,
standard_type = X_STANDARD_TYPE
where guid = X_GUID;
wf_event.raise('oracle.apps.wf.event.subscription.update', X_GUID);
l_update_allowed := WF_EVENTS_PKG.is_update_allowed(X_CUSTOMIZATION_LEVEL, l_custom_level);
if l_update_allowed = 'N' then
-- Set up the Error Stack
wf_core.context('WF_EVENT_SUBSCRIPTIONS_PKG','UPDATE_ROW',
X_EVENT_FILTER_GUID,
l_custom_level,
X_CUSTOMIZATION_LEVEL);
-- Here are the updates allowed when the caller is the Loader
update wf_event_subscriptions set
system_guid = X_SYSTEM_GUID,
source_type = X_SOURCE_TYPE,
source_agent_guid = X_SOURCE_AGENT_GUID,
event_filter_guid = X_EVENT_FILTER_GUID,
phase = X_PHASE,
status = X_STATUS,
rule_data = X_RULE_DATA,
out_agent_guid = X_OUT_AGENT_GUID,
to_agent_guid = X_TO_AGENT_GUID,
priority = X_PRIORITY,
rule_function = l_rule_func,
wf_process_type = X_WF_PROCESS_TYPE,
wf_process_name = X_WF_PROCESS_NAME,
parameters = X_PARAMETERS,
owner_name = X_OWNER_NAME,
owner_tag = X_OWNER_TAG,
description = X_DESCRIPTION,
customization_level = X_CUSTOMIZATION_LEVEL,
licensed_flag = l_licensed_flag,
expression = X_EXPRESSION,
action_code = X_ACTION_CODE,
on_error_code = X_ON_ERROR_CODE,
java_rule_func = X_JAVA_RULE_FUNC,
map_code = X_MAP_CODE,
standard_code = X_STANDARD_CODE,
standard_type = X_STANDARD_TYPE
where guid = X_GUID;
-- UI users cannot update Core events
null;
-- users changes must be preserved. Update all
-- fields EXCEPT the status field.
update wf_event_subscriptions set
system_guid = X_SYSTEM_GUID,
source_type = X_SOURCE_TYPE,
source_agent_guid = X_SOURCE_AGENT_GUID,
event_filter_guid = X_EVENT_FILTER_GUID,
phase = X_PHASE,
rule_data = X_RULE_DATA,
out_agent_guid = X_OUT_AGENT_GUID,
to_agent_guid = X_TO_AGENT_GUID,
priority = X_PRIORITY,
rule_function = l_rule_func,
wf_process_type = X_WF_PROCESS_TYPE,
wf_process_name = X_WF_PROCESS_NAME,
parameters = X_PARAMETERS,
owner_name = X_OWNER_NAME,
owner_tag = X_OWNER_TAG,
description = X_DESCRIPTION,
customization_level = X_CUSTOMIZATION_LEVEL,
licensed_flag = l_licensed_flag,
expression = X_EXPRESSION,
action_code = X_ACTION_CODE,
on_error_code = X_ON_ERROR_CODE,
java_rule_func = X_JAVA_RULE_FUNC,
map_code = X_MAP_CODE,
standard_code = X_STANDARD_CODE,
standard_type = X_STANDARD_TYPE
where guid = X_GUID;
else -- Caller of the Update is UI
-- Limit events can have only a status change..
-- When the user is updating the event using the UI
-- Updates are allowed ONLY to the status field.
update wf_event_subscriptions set
status = X_STATUS,
licensed_flag = l_licensed_flag
where guid = X_GUID;
-- Here are the updates allowed for extensible and User defined events
-- only when the caller is the UI
if WF_EVENTS_PKG.g_Mode = 'CUSTOM' then
update wf_event_subscriptions set
system_guid = X_SYSTEM_GUID,
source_type = X_SOURCE_TYPE,
source_agent_guid = X_SOURCE_AGENT_GUID,
event_filter_guid = X_EVENT_FILTER_GUID,
phase = X_PHASE,
status = X_STATUS,
rule_data = X_RULE_DATA,
out_agent_guid = X_OUT_AGENT_GUID,
to_agent_guid = X_TO_AGENT_GUID,
priority = X_PRIORITY,
rule_function = l_rule_func,
wf_process_type = X_WF_PROCESS_TYPE,
wf_process_name = X_WF_PROCESS_NAME,
parameters = X_PARAMETERS,
owner_name = X_OWNER_NAME,
owner_tag = X_OWNER_TAG,
description = X_DESCRIPTION,
customization_level = X_CUSTOMIZATION_LEVEL,
licensed_flag = l_licensed_flag,
expression = X_EXPRESSION,
action_code = X_ACTION_CODE,
on_error_code = X_ON_ERROR_CODE,
java_rule_func = X_JAVA_RULE_FUNC,
map_code = X_MAP_CODE,
standard_code = X_STANDARD_CODE,
standard_type = X_STANDARD_TYPE
where guid = X_GUID;
wf_event.raise('oracle.apps.wf.event.subscription.update', X_GUID);
end UPDATE_ROW;
WF_EVENT_SUBSCRIPTIONS_PKG.UPDATE_ROW (
X_GUID => X_GUID,
X_SYSTEM_GUID => X_SYSTEM_GUID,
X_SOURCE_TYPE => X_SOURCE_TYPE,
X_SOURCE_AGENT_GUID => X_SOURCE_AGENT_GUID,
X_EVENT_FILTER_GUID => X_EVENT_FILTER_GUID,
X_PHASE => X_PHASE,
X_STATUS => X_STATUS,
X_RULE_DATA => X_RULE_DATA,
X_OUT_AGENT_GUID => X_OUT_AGENT_GUID,
X_TO_AGENT_GUID => X_TO_AGENT_GUID,
X_PRIORITY => X_PRIORITY,
X_RULE_FUNCTION => X_RULE_FUNCTION,
X_WF_PROCESS_TYPE => X_WF_PROCESS_TYPE,
X_WF_PROCESS_NAME => X_WF_PROCESS_NAME,
X_PARAMETERS => X_PARAMETERS,
X_OWNER_NAME => X_OWNER_NAME,
X_OWNER_TAG => X_OWNER_TAG,
X_CUSTOMIZATION_LEVEL => X_CUSTOMIZATION_LEVEL,
X_LICENSED_FLAG => X_LICENSED_FLAG,
X_DESCRIPTION => X_DESCRIPTION,
X_EXPRESSION => X_EXPRESSION,
X_ACTION_CODE => X_ACTION_CODE,
X_ON_ERROR_CODE => X_ON_ERROR_CODE,
X_JAVA_RULE_FUNC => X_JAVA_RULE_FUNC,
X_MAP_CODE => X_MAP_CODE,
X_STANDARD_CODE => X_STANDARD_CODE,
X_STANDARD_TYPE => X_STANDARD_TYPE
);
WF_EVENT_SUBSCRIPTIONS_PKG.INSERT_ROW(
X_ROWID => row_id,
X_GUID => X_GUID,
X_SYSTEM_GUID => X_SYSTEM_GUID,
X_SOURCE_TYPE => X_SOURCE_TYPE,
X_SOURCE_AGENT_GUID => X_SOURCE_AGENT_GUID,
X_EVENT_FILTER_GUID => X_EVENT_FILTER_GUID,
X_PHASE => X_PHASE,
X_STATUS => X_STATUS,
X_RULE_DATA => X_RULE_DATA,
X_OUT_AGENT_GUID => X_OUT_AGENT_GUID,
X_TO_AGENT_GUID => X_TO_AGENT_GUID,
X_PRIORITY => X_PRIORITY,
X_RULE_FUNCTION => X_RULE_FUNCTION,
X_WF_PROCESS_TYPE => X_WF_PROCESS_TYPE,
X_WF_PROCESS_NAME => X_WF_PROCESS_NAME,
X_PARAMETERS => X_PARAMETERS,
X_OWNER_NAME => X_OWNER_NAME,
X_OWNER_TAG => X_OWNER_TAG,
X_CUSTOMIZATION_LEVEL => X_CUSTOMIZATION_LEVEL,
X_LICENSED_FLAG => X_LICENSED_FLAG,
X_DESCRIPTION => X_DESCRIPTION,
X_EXPRESSION => X_EXPRESSION,
X_ACTION_CODE => X_ACTION_CODE,
X_ON_ERROR_CODE => X_ON_ERROR_CODE,
X_JAVA_RULE_FUNC => X_JAVA_RULE_FUNC,
X_MAP_CODE => X_MAP_CODE,
X_STANDARD_CODE => X_STANDARD_CODE,
X_STANDARD_TYPE => X_STANDARD_TYPE
);
procedure DELETE_ROW (X_GUID in raw) is
begin
wf_event.raise('oracle.apps.wf.event.subscription.delete',x_guid);
delete from wf_event_subscriptions
where guid = X_GUID;
wf_core.context('Wf_Events_Subscriptions_Pkg', 'Delete_Row', x_guid);
end DELETE_ROW;
procedure DELETE_SET (
X_SYSTEM_GUID in raw,
X_SOURCE_TYPE in varchar2,
X_SOURCE_AGENT_GUID in raw,
X_EVENT_FILTER_GUID in raw,
X_PHASE in number,
X_STATUS in varchar2,
X_RULE_DATA in varchar2,
X_OUT_AGENT_GUID in raw,
X_TO_AGENT_GUID in raw,
X_PRIORITY in number,
X_RULE_FUNCTION in varchar2,
X_WF_PROCESS_TYPE in varchar2,
X_WF_PROCESS_NAME in varchar2,
X_PARAMETERS in varchar2,
X_OWNER_NAME in varchar2,
X_OWNER_TAG in varchar2,
X_DESCRIPTION in varchar2,
X_EXPRESSION in varchar2,
X_ACTION_CODE in varchar2,
X_ON_ERROR_CODE in varchar2,
X_JAVA_RULE_FUNC in varchar2,
X_MAP_CODE in varchar2,
X_STANDARD_CODE in varchar2,
X_STANDARD_TYPE in varchar2
) is
begin
delete from wf_event_subscriptions
where (X_SYSTEM_GUID is null or (X_SYSTEM_GUID is not null
and system_guid like X_SYSTEM_GUID))
and (X_SOURCE_TYPE is null or (X_SOURCE_TYPE is not null
and source_type like X_SOURCE_TYPE))
and (X_SOURCE_AGENT_GUID is null or (X_SOURCE_AGENT_GUID is not null
and source_agent_guid like X_SOURCE_AGENT_GUID))
and (X_EVENT_FILTER_GUID is null or (X_EVENT_FILTER_GUID is not null
and event_filter_guid like X_EVENT_FILTER_GUID))
and (X_PHASE is null or (X_PHASE is not null
and phase like X_PHASE))
and (X_STATUS is null or (X_STATUS is not null
and status like X_STATUS))
and (X_RULE_DATA is null or (X_RULE_DATA is not null
and rule_data like X_RULE_DATA))
and (X_OUT_AGENT_GUID is null or (X_OUT_AGENT_GUID is not null
and out_agent_guid like X_OUT_AGENT_GUID))
and (X_TO_AGENT_GUID is null or (X_TO_AGENT_GUID is not null
and to_agent_guid like X_TO_AGENT_GUID))
and (X_PRIORITY is null or (X_PRIORITY is not null
and priority like X_PRIORITY))
and (X_RULE_FUNCTION is null or (X_RULE_FUNCTION is not null
and rule_function like X_RULE_FUNCTION))
and (X_WF_PROCESS_TYPE is null or (X_WF_PROCESS_TYPE is not null
and wf_process_type like X_WF_PROCESS_TYPE))
and (X_WF_PROCESS_NAME is null or (X_WF_PROCESS_NAME is not null
and wf_process_name like X_WF_PROCESS_NAME))
and (X_PARAMETERS is null or (X_PARAMETERS is not null
and parameters like X_PARAMETERS))
and (X_OWNER_NAME is null or (X_OWNER_NAME is not null
and owner_name like X_OWNER_NAME))
and (X_OWNER_TAG is null or (X_OWNER_TAG is not null
and owner_tag like X_OWNER_TAG))
and (X_DESCRIPTION is null or (X_DESCRIPTION is not null
and description like X_DESCRIPTION))
and (X_EXPRESSION is null or (X_EXPRESSION is not null
and expression like X_EXPRESSION))
and (X_ACTION_CODE is null or (X_ACTION_CODE is not null
and action_code like X_ACTION_CODE))
and (X_ON_ERROR_CODE is null or (X_ON_ERROR_CODE is not null
and on_error_code like X_ON_ERROR_CODE))
and (X_JAVA_RULE_FUNC is null or (X_JAVA_RULE_FUNC is not null
and java_rule_func like X_JAVA_RULE_FUNC))
and (X_MAP_CODE is null or (X_MAP_CODE is not null
and map_code like X_MAP_CODE))
and (X_STANDARD_CODE is null or (X_STANDARD_CODE is not null
and standard_code like X_STANDARD_CODE))
and (X_STANDARD_TYPE is null or (X_STANDARD_TYPE is not null
and standard_type like X_STANDARD_TYPE));
wf_core.context('Wf_Events_Subscriptions_Pkg', 'Delete_Set',
x_system_guid, X_source_type, X_Event_Filter_GUID);
end DELETE_SET;
select s.system_guid, s.source_type, s.source_agent_guid,
e.name, s.phase, s.status, s.rule_data,
s.out_agent_guid, s.to_agent_guid, s.priority,
s.rule_function, s.wf_process_type, s.wf_process_name,
s.parameters, s.owner_name, s.owner_tag, s.description, s.expression,
nvl(s.customization_level, 'L'), nvl(s.licensed_flag, 'Y'),
s.standard_type , s.standard_code , s.java_rule_func , s.on_error_code,
s.action_code
into l_system_guid, l_source_type, l_source_agent_guid,
l_event_filter_name, l_phase, l_status, l_rule_data,
l_out_agent_guid, l_to_agent_guid, l_priority,
l_rule_function, l_wf_process_type, l_wf_process_name,
l_parameters, l_owner_name, l_owner_tag, l_description, l_expression,
l_customization_level, l_licensed_flag,l_standardtype,l_standardcode,
l_javarulefunc,l_onerror , l_actioncode
from wf_event_subscriptions s, wf_events e
where s.guid = x_guid
and e.guid = s.event_filter_guid;
l_message := WF_EVENT_SYNCHRONIZE_PKG.SetSYSTEMGUID(l_message); -- update #LOCAL
l_message := WF_EVENT_SYNCHRONIZE_PKG.SetSID(l_message); -- update #SID
l_message := WF_EVENT_SYNCHRONIZE_PKG.SetAgent2('',' ',l_message); -- update #WF_IN, #WF_OUT, #WF_ERROR
SELECT guid
INTO l_event_filter_guid
FROM wf_events
WHERE name = l_value;
select customization_level
from wf_events
where guid = X_EVENT_FILTER_GUID;
select CUSTOMIZATION_LEVEL from
WF_EVENT_SUBSCRIPTIONS
where guid = X_GUID;
SELECT guid
FROM wf_event_subscriptions
WHERE system_guid = x_system_guid
AND source_type = x_source_type
AND event_filter_guid = x_event_filter_guid
AND (((source_agent_guid is null) AND (x_source_agent_guid is null))
OR source_agent_guid = x_source_agent_guid)
AND (((phase is null) AND (x_phase is null))
OR phase = x_phase)
AND (((owner_name is null) AND (x_owner_name is null))
OR owner_name = x_owner_name)
AND (((owner_tag is null) AND (x_owner_tag is null))
OR owner_tag = x_owner_tag);
SELECT guid
FROM wf_event_subscriptions
WHERE system_guid = x_system_guid
AND source_type = x_source_type
AND event_filter_guid = x_event_filter_guid
AND (phase is null OR phase = x_phase)
AND owner_name is null
AND owner_tag is null
AND rule_data = x_rule_data
AND priority = x_priority
AND (((rule_function is null) AND (x_rule_function is null))
OR rule_function = x_rule_function)
AND (((wf_process_type is null) AND (x_wf_process_type is null))
OR wf_process_type = x_wf_process_type)
AND (((wf_process_name is null) AND (x_wf_process_name is null))
OR wf_process_name = x_wf_process_name)
AND (((parameters is null) AND (x_parameters is null))
OR parameters = x_parameters);