[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure UpdateStatus(
nid in number,
status in varchar2,
error_name in varchar2)
is
l_autoclose VARCHAR(1);
wf_mail.UpdateStatus2(nid => UpdateStatus.nid,
status => UpdateStatus.status,
autoclose => l_autoclose,
error_name => UpdateStatus.error_name,
external_error => null);
wf_core.context('WF_MAIL', 'UpdateStatus', to_char(nid),
UpdateStatus.status, UpdateStatus.error_name);
end UpdateStatus;
procedure UpdateStatus2(
nid in number,
status in varchar2,
autoclose in varchar2,
error_name in varchar2,
external_error in varchar2)
is
l_mType VARCHAR2(8);
updateState boolean;
select message_type, message_name, mail_status, recipient_role
into l_mType, l_mName, l_currState, l_role
from wf_notifications
where notification_id = nid;
updateState := false;
updateState := true;
if updateState then
-- This notification had already locked by wfmail() in the mailer
update WF_NOTIFICATIONS
set MAIL_STATUS = UpdateStatus2.status
where NOTIFICATION_ID = nid;
if (UpdateStatus2.status = 'ERROR') then
WF_MAIL.HandleSendError(nid => UpdateStatus2.nid,
status => UpdateStatus2.status,
error_name => UpdateStatus2.error_name,
external_error => UpdateStatus2.external_error);
elsif (UpdateStatus2.status = 'FAILED') then
-- Here we only raise an event and leave the message as is.
-- oracle.apps.wf.notification.send.failure
parameterlist := wf_parameter_list_t();
elsif UpdateStatus2.status = 'UNAVAIL' then
-- 4031628 The UNAVAIL mail_status has never been used, even
-- in the C mailer. It only happens when there is a match on
-- the pattern/actions. Here we can start to raise an event
-- that indicates that a recipient is not available to respond
-- to the notification.
parameterlist := wf_parameter_list_t();
wf_event.AddParameterToList('NOTIFICATION_ID', UpdateStatus2.nid, parameterlist);
wf_event.AddParameterToList('STATUS', UpdateStatus2.status, parameterlist);
elsif (UpdateStatus2.status = 'SENT') then
-- The default behaviour is to leave the notification open
-- unless there is a routing rule to tell otherwise.
-- This is contrary to the behaviour of previous releases
-- and will be re-Addressed a little later on.
-- close this notification if there is no response
update WF_NOTIFICATIONS N
set N.STATUS = 'CLOSED',
N.END_DATE = sysdate
where N.NOTIFICATION_ID = nid
and not exists (select NULL
from WF_MESSAGE_ATTRIBUTES MA
where MA.MESSAGE_TYPE = N.MESSAGE_TYPE
and MA.MESSAGE_NAME = N.MESSAGE_NAME
and MA.SUBTYPE = 'RESPOND')
and (UpdateStatus2.autoclose = 'Y'
and not exists (select null
from wf_routing_rules r
where (r.message_type = n.message_type
or r.message_type = '*')
and (r.message_name = n.message_name
or r.message_name = '*')
and r.action = 'FYIOPEN'
and r.role = n.recipient_role
and sysdate between nvl(begin_date, sysdate -1)
and nvl(end_date, sysdate + 1)));
wf_core.context('WF_MAIL', 'UpdateStatus2', to_char(nid),
UpdateStatus2.status, UpdateStatus2.autoclose,
UpdateStatus2.error_name);
end UpdateStatus2;
update WF_NOTIFICATIONS N
set N.MAIL_STATUS = 'MAIL'
where N.NOTIFICATION_ID = l_nid;
select CALLBACK, CONTEXT, RECIPIENT_ROLE
into cb, ctx, role
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid;
select name, email_address
from wf_roles r, wf_user_roles ur
where ur.role_name = parent
and r.name = ur.user_name;
select recipient_role
into recipient
from wf_notifications
where notification_id = nid;
paramList.DELETE;
'Unable to update notification preference for email ['||
tokens(tk)||']. Check for duplicates');
msg_name => 'USER_PREF_UPDATE_REPORT');
aname => 'UPDATED_USER_REPORT',
avalue => errorReport);
p_event.setErrorMessage(wf_core.translate('WFMLR_ROLE_UPDATE_FAILURE'));
select MEANING
from WF_LOOKUPS
where LOOKUP_TYPE = lk_type
order by MEANING;
update WF_NOTIFICATIONS
set MAIL_STATUS = 'INVALID'
where NOTIFICATION_ID = nid;
select recipient_role, group_id, message_type, message_name
into role, group_id, mType, mName
from wf_notifications
where notification_id = nid;
select MEANING
into buf
from WF_LOOKUPS
where LOOKUP_TYPE = lk_type and LOOKUP_CODE = lk_code;
select LOOKUP_CODE
into buf
from WF_LOOKUPS
where LOOKUP_TYPE = lk_type
and MEANING = lk_meaning;
select LOOKUP_CODE
into buf
from WF_LOOKUPS
where LOOKUP_TYPE = lk_type
and upper(MEANING) = upper(lk_meaning);
select LOOKUP_CODE
from WF_LOOKUPS
where LOOKUP_TYPE = lk_type
order by LOOKUP_CODE;
select 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)),
--
-- <> : Due to boolean flag, wf_notification_util.GetCalendarDate can not be used.
--'DATE', wf_notification_util.GetCalendarDate(p_nid=>nid, p_date=>WNA.DATE_VALUE, p_date_format=>WMA.FORMAT),
'LOOKUP', WNA.TEXT_VALUE,
WNA.TEXT_VALUE) VALUE,
WNA.DATE_VALUE -- value is Date type <
from WF_NOTIFICATION_ATTRIBUTES WNA,
WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = 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;
select 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)),
-- 'DATE', wf_notification_util.GetCalendarDate(nid, WNA.DATE_VALUE, WMA.FORMAT, true),
'LOOKUP', WNA.TEXT_VALUE,
WNA.TEXT_VALUE) VALUE,
WNA.DATE_VALUE
from WF_NOTIFICATION_ATTRIBUTES WNA,
WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = 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;
SELECT DISTINCT role user_name
FROM
(
SELECT role_priority, role
FROM
(
-- 1). Process ONWER
SELECT 2 role_priority,
wi.owner_role role
FROM wf_items wi
where wi.item_type = itemType
and wi.item_key = itemKey
and owner_role IS NOT NULL
UNION ALL
-- 2). Notification current owner
select 1 role_priority,
ntf.recipient_role role
from (select notification_id
from wf_item_activity_statuses ias
where ias.item_type = itemType
and ias.item_key = itemKey
union all
select notification_id
from wf_item_activity_statuses_h ias
where ias.item_type = itemType
and ias.item_key = itemKey)
iantf,
wf_notifications ntf
where iantf.notification_id = ntf.group_id
AND ntf.group_id = nid
UNION ALL
-- 3). Notification original recipient
-- < For email : I don't think we should consider this sql
-- because SQL#2 will select current recipient and SQL#4 will
-- select wf_ntf.FROM_ROLE .
select 1 role_priority,
ntf.original_recipient role
from (select notification_id
from wf_item_activity_statuses ias
where ias.item_type = itemType
and ias.item_key = itemKey
union all
select notification_id
from wf_item_activity_statuses_h ias
where ias.item_type = itemType
and ias.item_key = itemKey)
iantf,
wf_notifications ntf
where iantf.notification_id = ntf.group_id
and ntf.group_id = nid
UNION ALL
-- 4). #FROM_ROLE or if ntf has been transfered / delegated / Questioned / Answered
--
SELECT 3 role_priority,
ntf.FROM_ROLE role
FROM ( select notification_id
from wf_item_activity_statuses ias
where ias.item_type = itemType
and ias.item_key = itemKey
union all
select notification_id
from wf_item_activity_statuses_h ias
where ias.item_type = itemType
and ias.item_key = itemKey
)
iantf,
wf_notifications ntf
where iantf.notification_id = ntf.group_id
and ntf.group_id = nid
and ntf.from_role is not null
)
WHERE role <> current_role
-- this role should not be a role to whome current user belongs
AND role not in (select wur.role_name
from wf_user_roles wur
where wur.user_name = current_role
and wur.user_orig_system = orig_sys
and wur.user_orig_system_id = orig_sysid
)
-- bug 2887904 latest participant first
-- sstomar: added role_priority instead of begin_date. bug 7565684
order by role_priority desc
)
-- Without below clause, cursor may return random user because it does not return
-- sequentially baed on an order by clause.
WHERE rownum=1;
select context
into context
from wf_notifications
where notification_id = nid;
select user_comment
from wf_comments
where notification_id = nid
and action in ('QUESTION', 'QUESTION_WA', 'QUESTION_RULE')
order by comment_date desc ;
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 = 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 decode(WMA.NAME, 'RESULT', -100, WMA.SEQUENCE);
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)),
-- <> bug 8430385
-- 'DATE', wf_notification_util.GetCalendarDate(nid, WNA.DATE_VALUE, WMA.FORMAT, true),
--
'LOOKUP', WNA.TEXT_VALUE,
WNA.TEXT_VALUE) VALUE,
WNA.DATE_VALUE -- << sstomar: bug8430385 >>
from WF_NOTIFICATION_ATTRIBUTES WNA,
WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = 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 decode(WMA.NAME, 'RESULT', -100, WMA.SEQUENCE);
select WMA.NAME, WMA.DISPLAY_NAME, WNA.TEXT_VALUE, WMA.DESCRIPTION
from WF_NOTIFICATION_ATTRIBUTES WNA,
WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = 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 = 'URL'
order by WMA.SEQUENCE;
select WMA.DISPLAY_NAME, 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), WMA.DESCRIPTION
into disp_name, attr_type, attr_format, attr_value, attr_desc
from WF_NOTIFICATION_ATTRIBUTES WNA,
WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = 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.NAME = 'RESULT'
and WMA.TYPE not in ('FORM', 'URL');
select SUBJECT, BODY, HTML_BODY
into t_subject, t_text_body, t_html_body
from WF_MESSAGES_VL
where NAME = messageName
and TYPE = itemType;
select 1 into fyi
from dual
where not exists (select NULL
from WF_MESSAGE_ATTRIBUTES MA,
WF_NOTIFICATIONS N
where N.NOTIFICATION_ID = nid
and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
and MA.MESSAGE_NAME = N.MESSAGE_NAME
and MA.SUBTYPE = 'RESPOND');
select message_type, message_name
into mType, mName
from wf_notifications
where notification_id = nid;
select 1
into validTemplate
from WF_MESSAGES_VL
where NAME = t_name
and TYPE = t_type;
select WMA.NAME
from WF_MESSAGE_ATTRIBUTES_VL WMA,
WF_NOTIFICATION_ATTRIBUTES WNA,
WF_NOTIFICATIONS WN
where WNA.NOTIFICATION_ID = 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.NAME like '#HDR%'
and (WNA.TEXT_VALUE is not null OR
WNA.NUMBER_VALUE is not null OR
WNA.DATE_VALUE is not null)
order by WMA.SEQUENCE;
SELECT due_date, from_user
INTO l_due_date, l_from_user
FROM wf_notifications
WHERE notification_id = nid;
select STATUS, MAIL_STATUS, ACCESS_KEY,
RECIPIENT_ROLE, PRIORITY, USER_COMMENT,
BEGIN_DATE, END_DATE, DUE_DATE, FROM_USER,
MORE_INFO_ROLE
into n_status, n_mstatus, n_key,
n_to_role, n_priority, n_comment,
n_start_date, n_end_date, n_due_date, n_from_user,
n_more_info_role
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid;
select SUBJECT, BODY, HTML_BODY
into t_subject, t_text_body, t_html_body
from WF_MESSAGES_VL
where NAME = t_name and TYPE = t_type;
select DESCRIPTION
into n_disp_click
from WF_MESSAGE_ATTRIBUTES_TL
where MESSAGE_TYPE = t_type
and MESSAGE_NAME = t_name
and NAME = 'CLICK_HERE_RESPONSE'
and LANGUAGE = userenv('LANG');
select 'Y'
into n_response_exists
from WF_MESSAGES_VL
where NAME = t_name and TYPE = 'WFMAIL'
and instr(body,'&'||'RESPONSE')<>0;
insertNewLine boolean;
insertNewLine := false;
insertNewLine := false;
insertNewLine := false;
insertNewLine := true;
insertNewLine := false;
insertNewLine := true;
insertNewLine := true;
elsif insertNewLine then
-- Copy partial and make note of the current position
-- This is to minimise the number of calls to dbms_lob.copy.
tempOffset := dbms_lob.getLength(g_LOBTable(tempIdx).temp_lob) +1;
select WMA.NAME, WMA.DISPLAY_NAME, WNA.TEXT_VALUE, WMA.DESCRIPTION
from WF_NOTIFICATION_ATTRIBUTES WNA,
WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = 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 = 'URL'
order by WMA.SEQUENCE;
select WMA.DISPLAY_NAME, WMA.TYPE, WMA.FORMAT,
-- <> : TEXT_VALUE (Attr value is not being used within this API)
--
--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),
WMA.DESCRIPTION
into disp_name, attr_type, attr_format, attr_desc
from WF_NOTIFICATION_ATTRIBUTES WNA,
WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = 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.NAME = 'RESULT'
and WMA.TYPE not in ('FORM', 'URL');
SELECT context, callback
into context, callback
FROM wf_notifications
where notification_id = nid;
SELECT user_comment
FROM wf_comments
WHERE notification_id = p_nid
AND action in ('QUESTION', 'QUESTION_WA', 'QUESTION_RULE')
ORDER BY comment_date desc;
SELECT count(1)
FROM wf_comments
WHERE action_type in ('REASSIGN', 'QA')
AND notification_id = p_nid;
SELECT wm.body, wm.html_body
INTO l_text_body, l_html_body
FROM wf_notifications n, wf_messages_vl wm
WHERE n.notification_id = p_nid
AND n.message_name = wm.name
AND n.message_type = wm.type;
select ACCESS_KEY,
PRIORITY, USER_COMMENT,
BEGIN_DATE, END_DATE, DUE_DATE, FROM_USER,
MORE_INFO_ROLE
into n_key,
n_priority, n_comment,
n_start_date, n_end_date, n_due_date, n_from_user,
n_more_info_role
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid;
select SUBJECT, BODY, HTML_BODY
into t_subject, t_text_body, t_html_body
from WF_MESSAGES_VL
where NAME = t_name and TYPE = t_type;
select DESCRIPTION
into n_disp_click
from WF_MESSAGE_ATTRIBUTES_TL
where MESSAGE_TYPE = t_type
and MESSAGE_NAME = t_name
and NAME = 'CLICK_HERE_RESPONSE'
and LANGUAGE = userenv('LANG');
select status,
mail_status
into n_status,
n_mstatus
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid;
select RECIPIENT_ROLE
into n_to_role
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid;
select NOTIFICATION_ID, RECIPIENT_ROLE, ACCESS_KEY, PRIORITY, DUE_DATE
from WF_NOTIFICATIONS
where STATUS = 'OPEN'
and RECIPIENT_ROLE IN
(select role from dual
union
select UR.ROLE_NAME
from WF_USER_ROLES UR
where UR.USER_ORIG_SYSTEM = rorig_system
and UR.USER_ORIG_SYSTEM_ID = rorig_system_id
and UR.USER_NAME = role)
order by PRIORITY desc, DUE_DATE asc, NOTIFICATION_ID asc ;
select SUBJECT, BODY, HTML_BODY
into t_subject, t_body, t_html_body
from WF_MESSAGES_VL
where NAME = templateName
and TYPE = templateType;
select NAME, DISPLAY_NAME, TYPE, FORMAT
from WF_MESSAGE_ATTRIBUTES_VL
where MESSAGE_NAME = msg_name
and MESSAGE_TYPE = msg_type
and SUBTYPE = 'RESPOND'
and TYPE not in ('FORM', 'URL')
order by length(DISPLAY_NAME) desc;
select MESSAGE_NAME, MESSAGE_TYPE, STATUS
into msg_name, msg_type, stat
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid;
select WMA.NAME, 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 = 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;
select MESSAGE_NAME, MESSAGE_TYPE, STATUS
into msg_name, msg_type, stat
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid;
wf_notification.UpdateInfo2(nid, to_user, from_addr, comment);
wf_notification.UpdateInfo2(nid, to_user, from_addr, comment);
select WMA.TYPE, WMA.DISPLAY_NAME,
decode(WMA.TYPE, 'URL', WF_NOTIFICATION.GetUrlText(WNA.TEXT_VALUE,
GetURLAttachment.nid), WNA.TEXT_VALUE) URL,
WNA.NAME
from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = GetURLAttachment.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 = 'Y'
and WMA.NAME = WNA.NAME;
select SUBJECT, HTML_BODY
into l_subject, l_html_body
from WF_MESSAGES_VL
where NAME = 'ATTACHED_URLS' and TYPE = 'WFMAIL';
select NLS_CODESET
into charset
from WF_LANGUAGES
where NLS_LANGUAGE = lang
and NLS_TERRITORY = terr;
select NLS_CODESET
into charset
from WF_LANGUAGES
where NLS_LANGUAGE = lang
and rownum < 2;
select userenv('LANGUAGE')
into nls_str
from sys.dual;
select override_email_charset
into db_override_cs
from wf_languages
where code ='WFCS'
and rownum < 2 ; -- This language code is being used to store
SELECT email_allowed
INTO l_email_allowed
FROM wf_ntf_security_policies
WHERE policy_name = l_sec_policy;
select nvl(OVERRIDE_EMAIL_CHARSET,nls_codeset),
installed_flag,
decode(OVERRIDE_EMAIL_CHARSET, null, 'N', 'Y')
into l_codeset, l_install , l_is_iana_cs
from wf_languages
where nls_language = l_lang
AND installed_flag = 'Y';
select nvl(OVERRIDE_EMAIL_CHARSET,nls_codeset),
installed_flag,
decode(OVERRIDE_EMAIL_CHARSET, null, 'N', 'Y')
into l_codeset, l_install , l_is_iana_cs
from wf_languages
where nls_language = p_language
and installed_flag = 'Y';
select 'Y'
into l_install
from fnd_territories
where obsolete_flag = 'N'
and nls_territory = p_territory;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
SELECT from_role, to_role, user_comment
FROM wf_comments
WHERE notification_id = p_nid
AND action in ('QUESTION', 'QUESTION_WA', 'QUESTION_RULE')
ORDER BY comment_date desc;
SELECT NLS_CODESET, INSTALLED_FLAG
INTO l_codeset,l_installed
FROM WF_LANGUAGES
WHERE NLS_LANGUAGE = l_language
AND INSTALLED_FLAG = 'Y';
SELECT message_type,message_name
INTO l_msg_type,l_msg_name
FROM wf_notifications
WHERE notification_id = p_nid;
SELECT subject, BODY, HTML_BODY
INTO l_subject,l_text_body,l_html_body
FROM wf_messages_vl
WHERE name = 'MORE_INFO_ANSWERED'
AND type = 'WFMAIL';
SELECT TEXT INTO l_mail_error_message
FROM WF_RESOURCES
WHERE NAME = 'WFNTF_CANNOT_REPLY'
AND LANGUAGE = userenv('LANG');
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
l_attrlist.DELETE;
UPDATE wf_event_subscriptions
SET status = p_status
WHERE rule_data = 'MESSAGE'
AND owner_name = 'Oracle Workflow'
AND owner_tag = 'FND'
AND event_filter_guid
IN (SELECT guid
FROM wf_events
WHERE name = 'oracle.apps.wf.notification.send.group'
AND type = 'GROUP')
AND out_agent_guid
IN (SELECT guid
FROM wf_agents
WHERE name = 'WF_NOTIFICATION_OUT'
AND system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')))
AND customization_level = 'L';