[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select root_activity,
root_activity_version,
begin_date
into rt_activity,
rt_activity_version,
bg_date
from wf_items
where item_type = x_item_type
and item_key = x_item_key;
select display_name
into item_type_tl
from wf_item_types_vl
where name = x_item_type;
select a.item_type, /* activity definition */
ait.display_name item_type_disp,
a.name,
a1.display_name,
a1.description,
a.type,
l1.meaning type_disp,
a.function,
a.result_type,
art.display_name result_type_disp,
a.cost,
a.rerun,
a.icon_name,
a.message,
to_char(s.due_date)||' '||to_char(s.due_date, 'HH24:MI:SS') due_date,
a.error_item_type||'/'||a.error_process error_process,
a.expand_role,
p.instance_id, /* activity usage */
p.instance_label, /* activity usage */
'' timeout,
p.start_end,
p.default_result,
wf_core.activity_result(a.result_type, p.default_result)
activity_usage_result,
p.icon_geometry,
p.perform_role,
p.user_comment,
s.activity_status, /* activity status */
l2.meaning activity_status_disp,
s.activity_result_code,
wf_core.activity_result(a.result_type, s.activity_result_code)
activity_status_result,
s.assigned_user,
wf_directory.getroledisplayname(s.assigned_user) rolename,
s.notification_id,
/* date conversion is ok */
/* BINDVAR_SCAN_IGNORE */
to_char(s.begin_date) || ' ' || to_char(s.begin_date, 'HH24:MI:SS')
begin_date,
/* date conversion is ok */
/* BINDVAR_SCAN_IGNORE */
to_char(s.end_date) || ' ' || to_char(s.end_date, 'HH24:MI:SS')
end_date,
s.execution_time,
s.error_name,
s.error_message,
s.error_stack,
n.recipient_role,
n.status not_status,
n.due_date not_due_date,
n.begin_date not_begin_date,
n.end_date not_end_date
from wf_activities pd,
wf_process_activities p,
wf_activities a,
wf_activities_tl a1,
wf_item_types_vl ait,
wf_item_activity_statuses s,
wf_lookups l1,
wf_lookups l2,
wf_lookup_types art,
wf_notifications n
where pd.item_type = x_item_type
and pd.name = x_proc_parent
and pd.begin_date <= to_date(x_begin_date, 'DD/MM/YYYY HH24:MI:SS')
and (pd.end_date is null or
pd.end_date > to_date(x_begin_date, 'DD/MM/YYYY HH24:MI:SS'))
and p.process_version = pd.version
and p.process_name = pd.name
and p.process_item_type = pd.item_type
and p.activity_name = x_proc_name
and a.item_type = p.activity_item_type
and a.name = p.activity_name
and a.begin_date <= to_date(x_begin_date, 'DD/MM/YYYY HH24:MI:SS')
and (a.end_date is null or
a.end_date > to_date(x_begin_date, 'DD/MM/YYYY HH24:MI:SS'))
and a1.item_type = a.item_type
and a1.name = a.name
and a1.version = a.version
and a1.language = userenv('LANG')
and ait.name = a.item_type
and s.item_type (+) = x_item_type
and s.item_key (+) = x_item_key
and s.process_activity (+) = p.instance_id
and art.lookup_type(+) = a.result_type
and l1.lookup_code (+) = a.type
and l1.lookup_type (+) = 'WFENG_ACTIVITY_TYPE'
and l2.lookup_code (+) = s.activity_status
and l2.lookup_type (+) = 'WFENG_STATUS'
and s.notification_id = n.notification_id(+);
select l.lookup_code,
l.meaning
from wf_lookups l
where l.lookup_type = act_result_type;
select ia.name,
ia.display_name,
ia.type,
ia.format,
decode(ia.type,
'NUMBER', to_char(iav.number_value),
'DATE', to_char(iav.date_value, nvl(ia.format,
'DD-MON-YYYY HH24:MI:SS')),
'LOOKUP', wf_core.activity_result(ia.format, iav.text_value),
iav.text_value) value
from wf_item_attributes_vl ia,
wf_item_attribute_values iav
where iav.item_type = x_item_type
and iav.item_key = x_item_key
and ia.item_type = iav.item_type
and ia.name = iav.name
and substr(ia.name, 1, 1) <> '.'
order by ia.sequence;
select a.item_type, /* activity definition */
ait.display_name item_type_disp,
a.name,
a1.display_name,
a.version,
a1.description,
a.type,
l1.meaning type_disp,
a.function,
a.result_type,
art.display_name result_type_disp,
a.cost,
a.rerun,
a.icon_name,
a.message,
to_char(s.due_date)||' '||to_char(s.due_date, 'HH24:MI:SS') due_date,
a.error_item_type||'/'||a.error_process error_process,
a.expand_role,
p.instance_id, /* activity usage */
p.instance_label, /* activity usage */
wf_engine_util.activity_timeout(p.instance_id) timeout,
p.start_end,
p.default_result,
wf_core.activity_result(pd.result_type, p.default_result)
activity_usage_result,
p.icon_geometry,
p.perform_role,
p.user_comment,
s.activity_status, /* activity status */
l2.meaning activity_status_disp,
s.activity_result_code,
wf_core.activity_result(a.result_type, s.activity_result_code)
activity_status_result,
s.assigned_user,
wf_directory.getroledisplayname(s.assigned_user) rolename,
s.notification_id,
to_char(s.begin_date) || ' ' || to_char(s.begin_date, 'HH24:MI:SS')
begin_date,
to_char(s.end_date) || ' ' || to_char(s.end_date, 'HH24:MI:SS')
end_date,
s.execution_time,
s.error_name,
s.error_message,
s.error_stack,
n.recipient_role,
n.status not_status,
to_char(n.due_date) || ' '|| to_char(n.due_date, 'HH24:MI:SS')
not_due_date,
to_char(n.begin_date) || ' '|| to_char(n.begin_date, 'HH24:MI:SS')
not_begin_date,
to_char(n.end_date) || ' '|| to_char(n.end_date, 'HH24:MI:SS')
not_end_date
from wf_activities pd,
wf_process_activities p,
wf_activities a,
wf_activities_tl a1,
wf_item_types_vl ait,
wf_item_activity_statuses s,
wf_notifications n,
wf_lookups l1,
wf_lookups l2,
wf_lookup_types art
where pd.item_type = x_proc_type
and pd.name = x_proc_name
and pd.begin_date <= to_date(x_begin_date, 'DD/MM/YYYY HH24:MI:SS')
and (pd.end_date is null or
pd.end_date > to_date(x_begin_date, 'DD/MM/YYYY HH24:MI:SS'))
and p.process_item_type = pd.item_type
and p.process_name = pd.name
and p.process_version = pd.version
and a.item_type = p.activity_item_type
and a.name = p.activity_name
and a.begin_date <= to_date(x_begin_date, 'DD/MM/YYYY HH24:MI:SS')
and (a.end_date is null or
a.end_date > to_date(x_begin_date, 'DD/MM/YYYY HH24:MI:SS'))
and a1.item_type = a.item_type
and a1.name = a.name
and a1.version = a.version
and a1.language = userenv('LANG')
and ait.name = a.item_type
and s.item_type (+) = x_item_type
and s.item_key (+) = x_item_key
and s.process_activity (+) = p.instance_id
and art.lookup_type(+) = a.result_type
and l1.lookup_code (+) = a.type
and l1.lookup_type (+) = 'WFENG_ACTIVITY_TYPE'
and l2.lookup_code (+) = s.activity_status
and l2.lookup_type (+) = 'WFENG_STATUS'
and s.notification_id = n.notification_id(+);
select l.lookup_code,
l.meaning
from wf_lookups l
where l.lookup_type = act_result_type;
select aa.name,
aa.display_name,
aa.type,
aa.format,
decode(aa.type,
'NUMBER', to_char(aav.number_value),
'DATE', to_char(aav.date_value, nvl(aa.format,
'DD-MON-YYYY HH24:MI:SS')),
'LOOKUP', wf_core.activity_result(aa.format, aav.text_value),
aav.text_value) value
from wf_activity_attributes_vl aa,
wf_activity_attr_values aav
where aa.activity_item_type = act_item_type
and aa.activity_name = act_name
and aa.activity_version = act_version
and aav.process_activity_id = act_instance_id
and aa.name = aav.name
and aa.name not like '#%';
select n.notification_id,
n.recipient_role,
n.status,
to_char(n.due_date) || ' ' || to_char(n.due_date, 'HH24:MI:SS')
due_date,
to_char(n.begin_date) || ' ' || to_char(n.begin_date, 'HH24:MI:SS')
begin_date,
to_char(n.end_date) || ' ' || to_char(n.end_date, 'HH24:MI:SS')
end_date,
ma.name,
ma.display_name,
ma.type,
ma.format,
decode(ma.type,
'NUMBER', to_char(na.number_value),
'DATE', to_char(na.date_value, nvl(ma.format,
'DD-MON-YYYY HH24:MI:SS')),
'LOOKUP', wf_core.activity_result(ma.format, na.text_value),
na.text_value) value
from wf_notifications n,
wf_notification_attributes na,
wf_message_attributes_vl ma
where n.group_id = nid
and n.message_type = ma.message_type
and n.message_name = ma.message_name
and ma.name = na.name
and na.notification_id = n.notification_id;
select p1.icon_geometry from_icon_geometry, /* from activity */
p1.instance_id from_instance_id,
t.result_code, /* transition info */
decode((wf_core.activity_result(a.result_type, t.result_code)),
'*', wf_core.translate('DEFAULT'),
'#ANY', wf_core.translate('WFMON_ANYRESULT'),
wf_core.activity_result(a.result_type, t.result_code))
activity_result,
t.arrow_geometry,
s.activity_status, /* transition status */
s.activity_result_code,
l.lookup_code,
p2.icon_geometry to_icon_geometry, /* to activity */
p2.instance_id to_instance_id
from wf_activities pd,
wf_process_activities p1,
wf_activities a,
wf_activity_transitions t,
wf_item_activity_statuses s,
wf_lookups l,
wf_process_activities p2
where pd.item_type = x_proc_type
and pd.name = x_proc_name
and pd.begin_date <= to_date(x_begin_date, 'DD/MM/YYYY HH24:MI:SS')
and (pd.end_date is null or
pd.end_date > to_date(x_begin_date, 'DD/MM/YYYY HH24:MI:SS'))
and p1.process_item_type = pd.item_type
and p1.process_name = pd.name
and p1.process_version = pd.version
and a.item_type = p1.activity_item_type
and a.name = p1.activity_name
and a.begin_date <= to_date(x_begin_date, 'DD/MM/YYYY HH24:MI:SS')
and (a.end_date is null or
a.end_date > to_date(x_begin_date, 'DD/MM/YYYY HH24:MI:SS'))
and t.from_process_activity = p1.instance_id
and s.item_type (+) = x_item_type
and s.item_key (+) = x_item_key
and s.process_activity (+) = p1.instance_id
and l.lookup_type = 'WFSTD_BOOLEAN'
and ((s.activity_status = 'COMPLETE' and
(t.result_code in (nvl(s.activity_result_code, '#NULL'),
wf_engine.eng_trans_any) or
t.result_code = wf_engine.eng_trans_default and not exists
(select null from wf_activity_transitions tr
where tr.from_process_activity = p1.instance_id
and tr.result_code = nvl(s.activity_result_code, '#NULL'))) and
l.lookup_code = 'T') or
(not (s.activity_status = 'COMPLETE' and
(t.result_code in (nvl(s.activity_result_code, '#NULL'),
wf_engine.eng_trans_any) or
t.result_code = wf_engine.eng_trans_default and not exists
(select null from wf_activity_transitions tr
where tr.from_process_activity = p1.instance_id
and tr.result_code = nvl(s.activity_result_code, '#NULL')))) or
s.activity_status is null) and
l.lookup_code = 'F')
and p2.instance_id = t.to_process_activity;
select NAME, TEXT, ID
into res_name, res_text, res_id
from WF_RESOURCES
where TYPE = x_restype
and NAME = x_resname
and NAME <> 'WF_ADMIN_ROLE'
and LANGUAGE = userenv('LANG');
select NAME, TEXT, ID
from WF_RESOURCES
where TYPE = x_restype
and NAME like x_respattern||'%'
and NAME <> 'WF_ADMIN_ROLE'
and LANGUAGE = userenv('LANG')
order by NAME;
select r.name,
r.display_name
from wf_roles r
where display_name not like '~WF_ADHOC-%'
and (display_name like tmpbuf1 or
display_name like tmpbuf2 or
display_name like tmpbuf3 or
display_name like tmpbuf4) and
upper(display_name) like upper(P_FIND_CRITERIA)||'%'
order by r.display_name;
select count(*) into cnt
from wf_roles
where display_name not like '~WF_ADHOC-%'
and (display_name like tmpbuf1 or
display_name like tmpbuf2 or
display_name like tmpbuf3 or
display_name like tmpbuf4) and
upper(display_name) like upper(P_FIND_CRITERIA) ||'%';
select r.name,
r.display_name
from wf_roles r
order by r.display_name;
select root_activity,
root_activity_version,
begin_date,
end_date,
user_key,
owner_role
into rt_activity,
rt_activity_version,
bg_date,
ed_date,
ukey,
owner
from wf_items
where item_type = x_item_type
and item_key = x_item_key;
select replace(nls_language,' ','%20')
into xnls_lang
from wf_languages
where code=userenv('LANG');
PROCEDURE updateToFWKMonitorUrl(oldUrl in varchar2,
newUrl out nocopy varchar2,
errorCode out nocopy pls_integer) is
region varchar2(4000);
end updateToFWKMonitorUrl;
select replace(nls_language,' ','%20')
into xnls_lang
from wf_languages
where code=userenv('LANG');
select replace(nls_language,' ','%20')
into xnls_lang
from wf_languages
where code=userenv('LANG');
select wit.display_name,
wi.item_key,
wi.user_key,
wi.begin_date,
wi.item_type,
wi.end_date,
wa.display_name process_name
from wf_items wi,
wf_item_types_vl wit,
wf_activities_vl wa
where wi.item_type = wit.name
and wa.item_type = wi.item_type
and wa.name = wi.root_activity
and wa.version = wi.root_activity_version
and wi.item_type like decode(x_itemtype,
'', '%',
'ALL', '%',
'*', '%',
x_itemtype)
and wi.item_key like x_ident||'%'
and (wi.owner_role like upper(c_process_owner)||'%' or
c_process_owner is null)
and (wi.user_key like x_user_ident||'%' or
x_user_ident is null)
and (wa.display_name like x_process||'%')
and (((wi.end_date is null) and (x_active in ('ACTIVE', 'ALL'))) or
((wi.end_date is not null) and (x_active in ('COMPLETE', 'ALL'))))
and ((x_numdays is null) or
(wi.end_date is null and not exists
(select null
from wf_item_activity_statuses ias
where ias.item_type = wi.item_type
and ias.item_key = wi.item_key
and ias.end_date > sysdate - x_numdays)))
and ((c_person is null) or exists
(select null
from wf_item_activity_statuses ias,
wf_notifications ntf
where wi.end_date is null
and ias.item_type = wi.item_type
and ias.item_key = wi.item_key
and ias.activity_status||'' = 'NOTIFIED'
and ntf.group_id = ias.notification_id
and ntf.recipient_role||'' = upper(c_person)))
and ((x_status = 'ALL') or exists
(select null
from wf_item_activity_statuses ias
where ias.item_type = wi.item_type
and ias.item_key = wi.item_key
and ias.activity_status||'' = x_status))
order by 1,2;
select count(1)
from wf_item_activity_statuses wias
where wias.item_type = itype
and wias.item_key = ikey
and wias.activity_status = status;
select MA.NAME
from WF_NOTIFICATION_ATTRIBUTES NA,
WF_MESSAGE_ATTRIBUTES_VL MA,
WF_NOTIFICATIONS N
where N.NOTIFICATION_ID = mnid
and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
and MA.MESSAGE_NAME = N.MESSAGE_NAME
and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
and MA.NAME = NA.NAME
and MA.SUBTYPE = 'RESPOND'
and MA.TYPE <> 'FORM'
and MA.NAME = 'RESULT';
select i.begin_date, i.end_date, a.display_name, it.display_name, i.root_activity
into x_begin_date, x_end_date, proc_dispname, item_type_dispname, x_root_activity
from wf_items i, wf_activities_vl a, wf_item_types_vl it
where i.item_type = x_item_type
and i.item_key = x_item_key
and i.item_type = it.name
and i.root_activity = a.name
and i.item_type = a.item_type
and a.begin_date <= i.begin_date
and (a.end_date is null or a.end_date > i.begin_date);
select user_key into u_key from wf_items
where item_type=x_item_type and item_key = x_item_key;
** youre coming from the notifications list and select the
** advanced search option for the first time then the
** x_advanced variable will be set to FIRST. All other
** times it will be set to true
*/
IF (x_advanced in ('FIRST', 'TRUE')) THEN
/*
** Create the controls frame
*/
draw_advanced_controls(
x_item_type,
x_item_key,
x_admin_mode,
x_access_key,
'TRUE',
x_active,
x_complete,
x_error,
x_suspend,
x_proc_func,
x_note_resp,
x_note_noresp,
x_func_std,
x_event,
x_sort_column,
x_sort_order,
x_nls_lang);
** the select statement twice. The first is the sort
** for the ascending list. The second is the list
** for the descending sort. The x_activity_cursor is
** defined in wfmons and can be shared across multiple
** selects as long as the select list matches the
** wf_activity_record definition
*/
IF (NVL(x_sort_order, 'ASC') = 'ASC') THEN
OPEN x_activity_cursor FOR
select item_type,
item_key,
begin_date,
execution_time,
end_date,
begin_date_time,
duration,
activity_item_type,
activity_type,
parent_activity_name,
activity_name,
activity_display_name,
parent_display_name,
activity_status,
notification_status,
notification_id,
recipient_role,
recipient_role_name,
activity_status_display,
result
from wf_item_activities_history_v wfhist
where wfhist.item_type = x_item_type
and wfhist.item_key = x_item_key
and wfhist.activity_def_begin_date <= x_begin_date
and (wfhist.activity_def_end_date is null or wfhist.activity_def_end_date > x_begin_date)
order by
DECODE(x_sort_column, 'STATUS', activity_status_display,
'WHO', recipient_role_name,
'PARENT', parent_display_name,
'ACTIVITY', activity_display_name,
'STARTDATE', to_char(begin_date, 'J.SSSSS'),
'DURATION', to_char(duration, '00000000'),
'RESULT', result,
to_char(begin_date, 'J.SSSSS')),
begin_date,
execution_time;
select item_type,
item_key,
begin_date,
execution_time,
end_date,
begin_date_time,
duration,
activity_item_type,
activity_type,
parent_activity_name,
activity_name,
activity_display_name,
parent_display_name,
activity_status,
notification_status,
notification_id,
recipient_role,
recipient_role_name,
activity_status_display,
result
from wf_item_activities_history_v wfhist
where wfhist.item_type = x_item_type
and wfhist.item_key = x_item_key
and wfhist.activity_def_begin_date <= x_begin_date
and (wfhist.activity_def_end_date is null or wfhist.activity_def_end_date > x_begin_date)
order by
DECODE(x_sort_column, 'STATUS', activity_status_display,
'WHO', recipient_role_name,
'PARENT', parent_display_name,
'ACTIVITY', activity_display_name,
'STARTDATE', to_char(begin_date, 'J.SSSSS'),
'DURATION', to_char(duration, '00000000'),
'RESULT', result,
to_char(begin_date, 'J.SSSSS')) desc,
begin_date desc,
execution_time desc;
** Go fetch all the rows that were selected in the above cursor
*/
LOOP
FETCH x_activity_cursor INTO x_activity_record;
SELECT decode(ma.type,
'NUMBER', to_char(na.number_value),
'DATE', to_char(na.date_value,
nvl(ma.format, 'DD/MON/YYYY HH24:MI:SS')),
'LOOKUP', wf_core.activity_result(ma.format, na.text_value),
na.text_value) result,
1
INTO x_notification_result,
x_notification_response
FROM wf_notification_attributes na,
wf_message_attributes_vl ma,
wf_notifications n
WHERE n.group_id = x_notification_id
AND n.message_type = ma.message_type
AND n.message_name = ma.message_name
AND ma.name = na.name
AND ma.name = 'RESULT'
AND na.notification_id = n.notification_id;
** row was fetched into a single row select which is the case
** for a notification that was
** sent to a role with multiple receipients. (Expand Roles is
** turned on. In these cases use the result that was selected
** from the activity that was fetched above.
*/
WHEN OTHERS THEN
x_notification_result := x_activity_record.result;
SELECT count(*)
INTO x_notification_response
FROM wf_notification_attributes na,
wf_message_attributes_vl ma,
wf_notifications n
WHERE n.group_id = x_notification_id
AND n.message_type = ma.message_type
AND n.message_name = ma.message_name
AND ma.name = na.name
AND ma.subtype = 'RESPOND'
AND na.notification_id = n.notification_id;
** Retrieve role information. This has to be selected separately
** instead of joining into main select so the orig_system_ids can
** be used on wf_roles to preserve indexes over the view.
*/
if (x_activity_record.recipient_role is not null) then
wf_directory.getroleinfo(x_activity_record.recipient_role,
role_name, email_address, buf, buf, buf);
select ac.display_name,
wf_core.activity_result(ac.result_type, ias.activity_result_code) result,
ias.error_name,
ias.error_message,
ias.error_stack
from wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
where ias.item_type = c_item_type
and ias.item_key = c_item_key
and ias.activity_status = 'ERROR'
and ias.process_activity = pa.instance_id
and pa.activity_name = ac.name
and pa.activity_item_type = ac.item_type
and pa.process_name = ap.name
and pa.process_item_type = ap.item_type
and pa.process_version = ap.version
and i.item_type = c_item_type
and i.item_key = ias.item_key
and i.begin_date >= ac.begin_date
and i.begin_date < nvl(ac.end_date, i.begin_date+1)
order by ias.begin_date, ias.execution_time;
SELECT item_type,
item_key,
root_activity,
root_activity_version,
user_key,
owner_role,
begin_date,
end_date
FROM wf_items
WHERE parent_item_type = p_parent_item_type
AND parent_item_key = p_parent_item_key;
SELECT display_name
INTO l_item_type_disp_name
FROM wf_item_types_vl
WHERE name = p_parent_item_type;