[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select WMA.TYPE, WMA.DISPLAY_NAME, WNA.TEXT_VALUE, WNA.NAME
from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = p_nid
and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WMA.TYPE = 'URL'
and WMA.ATTACH = 'N'
and WMA.NAME = WNA.NAME;
select name
from wf_users
where (name, orig_system, orig_system_id) in
(select user_name, user_orig_system, user_orig_system_id
from wf_user_roles
where role_name = urc1.role
and role_orig_system = substr(urc1.role, 1, urc1.colon-1)
and role_orig_system_id = substr(urc1.role, urc1.colon+1)
and user_name <> role_name
and user_orig_system <> role_orig_system
and user_orig_system_id <> role_orig_system_id)
and notification_preference not in ('SUMMARY','QUERY')
order by notification_preference, language;
select name
from wf_users
where (name, orig_system, orig_system_id) in
(select user_name, user_orig_system, user_orig_system_id
from wf_user_roles
where role_name = urc2.role
and user_name <> role_name)
and notification_preference not in ('SUMMARY','QUERY')
order by notification_preference, language;
select WMA.TYPE, WMA.DISPLAY_NAME,
decode(WMA.TYPE, 'URL', WF_NOTIFICATION.GetUrlText(WNA.TEXT_VALUE,
p_nid), WNA.TEXT_VALUE) URL, WNA.NAME
from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = p_nid
and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and (WMA.TYPE = 'URL' or WMA.TYPE = 'DOCUMENT')
and WMA.ATTACH = 'Y'
and WMA.NAME = WNA.NAME;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
select WMA.NAME, WMA.DISPLAY_NAME, WMA.DESCRIPTION, WMA.TYPE, WMA.FORMAT,
decode(WMA.TYPE,
'VARCHAR2', decode(WMA.FORMAT,
'', WNA.TEXT_VALUE,
substr(WNA.TEXT_VALUE, 1, to_number(WMA.FORMAT))),
'NUMBER', decode(WMA.FORMAT,
'', to_char(WNA.NUMBER_VALUE),
to_char(WNA.NUMBER_VALUE, WMA.FORMAT)),
'DATE', decode(WMA.FORMAT,
'', to_char(WNA.DATE_VALUE),
to_char(WNA.DATE_VALUE, WMA.FORMAT)),
'LOOKUP', WNA.TEXT_VALUE,
WNA.TEXT_VALUE) VALUE
from WF_NOTIFICATION_ATTRIBUTES WNA,
WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = p_nid
and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WMA.NAME = WNA.NAME
and WMA.SUBTYPE = 'RESPOND'
and WMA.TYPE not in ('FORM', 'URL')
order by WMA.SEQUENCE;
l_attrlist.DELETE;
l_attrlist.DELETE;
select installed_flag
into l_installed_lang
from wf_languages
where nls_language = p_nlang
and nls_territory = p_nterr
and installed_flag = 'Y';
l_attrlist.DELETE;
select priority, access_key, status
into l_priority, l_access_key, l_status
from wf_notifications_view
where notification_id = p_nid;
l_attrlist.DELETE;
select 1 into l_response
from dual
where exists (select NULL
from WF_MESSAGE_ATTRIBUTES MA,
WF_NOTIFICATIONS N
where N.NOTIFICATION_ID = p_nid
and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
and MA.MESSAGE_NAME = N.MESSAGE_NAME
and MA.SUBTYPE = 'RESPOND');
l_attrlist.DELETE;
select recipient_role, priority, status, more_info_role
into l_recipient, l_priority, l_status, l_more_info_role
from wf_notifications
where notification_id = p_nid;
l_attrlist.DELETE;
select recipient_role, priority, status, more_info_role
into l_recipient, l_priority, l_status, l_more_info_role
from wf_notifications
where notification_id = p_nid;
select recipient_role
into l_recipient_role
from wf_notifications
where notification_id = p_nid
and ((MAIL_STATUS = 'MAIL' and STATUS <> 'CLOSED')
or (MAIL_STATUS = 'INVALID'));
l_recipient_list.DELETE;
l_recipient_lang.DELETE;
l_recipient_lang.DELETE;
select recipient_role
into l_recipient_role
from wf_notifications
where notification_id = p_nid
and MAIL_STATUS in ('MAIL', 'INVALID');
select recipient_role, more_info_role
into l_recipient_role, l_more_info_role
from wf_notifications
where notification_id = p_nid
and MAIL_STATUS in ('MAIL', 'INVALID')
and STATUS <> 'CLOSED';
select status into l_currstatus
from wf_notifications
where notification_id = l_nid
and status in ('OPEN','CANCELED', 'CLOSED')
and mail_status in ('MAIL','INVALID','FAILED');
select protocol, inbound_outbound, queue_count
into l_protocol, l_iobound, l_queue_count
from wf_queues
where protocol = 'SMTP'
and INBOUND_OUTBOUND = 'OUTBOUND'
and DISABLE_FLAG = 'N';
select userenv('LANGUAGE')
into nls_base
from sys.dual;
select installed_flag
into l_installed_flag
from wf_languages
where nls_language = language
and installed_flag = 'Y';
resourceList.DELETE;
attrlist.DELETE;
attrlist.DELETE;
attrlist.DELETE;
select UR.USER_NAME, UR.USER_ORIG_SYSTEM, UR.USER_ORIG_SYSTEM_ID
from WF_USER_ROLES UR
where UR.ROLE_NAME = rname
and UR.ROLE_ORIG_SYSTEM = rorig
and UR.ROLE_ORIG_SYSTEM_ID = rorigid
and ((UR.USER_NAME <> UR.ROLE_NAME) or
(UR.USER_ORIG_SYSTEM <> UR.ROLE_ORIG_SYSTEM and
UR.USER_ORIG_SYSTEM_ID <> UR.ROLE_ORIG_SYSTEM_ID));
copy_recipient_list.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
select ACCESS_KEY, PRIORITY, STATUS, MESSAGE_TYPE,
MESSAGE_NAME, MORE_INFO_ROLE
into access_key, priority, status, messageType,
messageName, moreInfoRole
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid;
-- WF_MAIL.GetLOBMessage4() API as these values may update in the meantime
WF_MAIL.GetLobMessage4(nid, nodeName, agent, replyto,
recipient_role, l_nlsLanguage, l_nlsTerritory,
notification_pref, email,
display_name,
'N',
subject, body_atth,
error_result, bodyToken,
g_status, g_mstatus);
-- WF_MAIL.GetLOBMessage4() API as these values may update in the meantime
WF_MAIL.GetLobMessage4(nid, nodeName, agent, replyto,
recipient_role, l_nlsLanguage, l_nlsTerritory,
notification_pref, email,
display_name,
'Y',
subject, body_atth,
error_result, bodyToken,
g_status, g_mstatus);
attrlist.DELETE;
attrlist.DELETE;
attrlist.DELETE;
attrlist.DELETE;
resourceList.DELETE;
attrlist.DELETE;
attrlist.DELETE;
attrlist.DELETE;
attrlist.DELETE;
attrlist.DELETE;
attrlist.DELETE;
select UR.USER_NAME, UR.USER_ORIG_SYSTEM, UR.USER_ORIG_SYSTEM_ID
from WF_USER_ROLES UR
where UR.ROLE_NAME = rname
and UR.ROLE_ORIG_SYSTEM = rorig
and UR.ROLE_ORIG_SYSTEM_ID = rorigid
and ((UR.USER_NAME <> UR.ROLE_NAME) or
(UR.USER_ORIG_SYSTEM <> UR.ROLE_ORIG_SYSTEM and
UR.USER_ORIG_SYSTEM_ID <> UR.ROLE_ORIG_SYSTEM_ID));
attrlist.DELETE;
select NVL(MORE_INFO_ROLE, RECIPIENT_ROLE), STATUS, MAIL_STATUS,
MESSAGE_TYPE, MESSAGE_NAME
into recipient_role, status, mail_status, messageType, messageName
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid;
attrlist.DELETE;
select nls_codeset, override_email_charset
into l_nlsCodeset, l_OverrideCodeset
from wf_languages
where nls_language = l_nlsLanguage;
attrlist.DELETE;
attrlist.DELETE;
attrlist.DELETE;
attrList.DELETE;
attrlist.DELETE;
select UR.USER_NAME, UR.USER_ORIG_SYSTEM, UR.USER_ORIG_SYSTEM_ID
from WF_USER_ROLES UR
where UR.ROLE_NAME = rname
and UR.ROLE_ORIG_SYSTEM = rorig
and UR.ROLE_ORIG_SYSTEM_ID = rorigid
and ((UR.USER_NAME <> UR.ROLE_NAME) or
(UR.USER_ORIG_SYSTEM <> UR.ROLE_ORIG_SYSTEM and
UR.USER_ORIG_SYSTEM_ID <> UR.ROLE_ORIG_SYSTEM_ID));
attrlist.DELETE;
attrlist.DELETE;
select 1
into l_open
from wf_notifications
where notification_id = nid
and status = 'OPEN';
responses.delete;
wf_notification.UpdateInfo2(l_nid, l_user, l_fromAddr,
l_comment);
wf_notification.UpdateInfo2(l_nid, l_user, l_fromAddr,
l_comment);
SELECT recipient from
(SELECT distinct nvl(more_info_role,recipient_role) recipient
FROM wf_notifications
WHERE mail_status is null
AND status = 'OPEN'
AND rownum > 0)
WHERE Wf_Directory.GetRoleNtfPref(recipient) in ('SUMMARY', 'SUMHTML');
SELECT recipient from
(SELECT distinct nvl(more_info_role,recipient_role) recipient
FROM wf_notifications
WHERE mail_status is null
AND status = 'OPEN'
AND rownum > 0) , wf_roles wr
WHERE recipient = wr.NAME
AND wr.notification_preference = 'QUERY';
SELECT user_name
FROM wf_user_roles wu, wf_roles wr
WHERE wu.role_name = p_roleName
AND wr.name = wu.user_name
AND wu.user_orig_system = wr.orig_system
AND wu.user_orig_system_id = wr.orig_system_id
AND wr.notification_preference in ('SUMMARY', 'SUMHTML');
select MESSAGE_TYPE, CALLBACK, CONTEXT, STATUS
into itemType, cb, ctx, status
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid;
update wf_notifications
set mail_status = 'ERROR'
where notification_id = nid;
select ACTIVITY_STATUS
into status
from wf_item_activity_statuses ias
, wf_process_activities pa
where ias.item_type = itemType
and ias.item_key = itemKey
and ias.process_activity = pa.instance_id
and pa.process_name = 'ROOT';