[Home] [Help]
73: g_none varchar2(200);
74: g_truncate varchar2(200);
75: g_noResult varchar2(200);
76: g_install varchar2(60) := wf_core.translate('WF_INSTALL');
77: g_ntfDocText varchar2(30) := wf_notification.doc_text;
78: g_ntfDocHtml varchar2(30) := wf_notification.doc_html;
79: g_Id varchar2(30);
80: g_isFwkNtf boolean;
81: g_sig_required varchar2(1);
74: g_truncate varchar2(200);
75: g_noResult varchar2(200);
76: g_install varchar2(60) := wf_core.translate('WF_INSTALL');
77: g_ntfDocText varchar2(30) := wf_notification.doc_text;
78: g_ntfDocHtml varchar2(30) := wf_notification.doc_html;
79: g_Id varchar2(30);
80: g_isFwkNtf boolean;
81: g_sig_required varchar2(1);
82: g_fwk_flavor varchar2(255);
170: parameterList wf_parameter_list_t;
171: begin
172: select message_type, message_name, mail_status, recipient_role
173: into l_mType, l_mName, l_currState, l_role
174: from wf_notifications
175: where notification_id = nid;
176:
177: -- If the prevsious state was FAILED, then preserve this state.
178: -- A new status of null means that a null message was sent.
185: end if;
186:
187: if updateState then
188: -- This notification had already locked by wfmail() in the mailer
189: update WF_NOTIFICATIONS
190: set MAIL_STATUS = UpdateStatus2.status
191: where NOTIFICATION_ID = nid;
192: end if;
193:
198: error_name => UpdateStatus2.error_name,
199: external_error => UpdateStatus2.external_error);
200: elsif (UpdateStatus2.status = 'FAILED') then
201: -- Here we only raise an event and leave the message as is.
202: -- oracle.apps.wf.notification.send.failure
203: parameterlist := wf_parameter_list_t();
204:
205: wf_event.AddParameterToList('NOTIFICATION_ID',nid,parameterlist);
206: wf_event.AddParameterToList('ROLE',l_role,parameterlist);
211: parameterlist);
212: wf_event.addParameterToList('Q_CORRELATION_ID', l_mType || ':' || l_mName, parameterlist);
213:
214: --Raise the event
215: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send.failure',
216: p_event_key => l_role,
217: p_parameters => parameterlist);
218:
219: elsif UpdateStatus2.status = 'UNAVAIL' then
232:
233: -- Raise the event
234: -- As of now there is NO subscription of this event with SOURCE_TYPE LOCAL.
235: -- So this is just for future point of view ....
236: wf_event.Raise(p_event_name=>'oracle.apps.wf.notification.receive.unavail',
237: p_event_key => to_char(nid),
238: p_parameters => parameterlist);
239:
240: elsif (UpdateStatus2.status = 'SENT') then
243: -- This is contrary to the behaviour of previous releases
244: -- and will be re-Addressed a little later on.
245:
246: -- close this notification if there is no response
247: update WF_NOTIFICATIONS N
248: set N.STATUS = 'CLOSED',
249: N.END_DATE = sysdate
250: where N.NOTIFICATION_ID = nid
251: and not exists (select NULL
280: -- Queue number on which to process
281: procedure ResetFailed(p_queue varchar2)
282: is
283: l_nid number;
284: l_recipient WF_NOTIFICATIONS.RECIPIENT_ROLE%TYPE;
285: l_status varchar2 (8);
286: l_timeout boolean;
287: l_error_result varchar2(2000);
288:
295: while not l_timeout loop
296: wf_xml.GetExceptionMessage(p_queue, l_nid, l_recipient, l_status,
297: l_timeout, l_error_result);
298: if ( not l_timeout ) then
299: update WF_NOTIFICATIONS N
300: set N.MAIL_STATUS = 'MAIL'
301: where N.NOTIFICATION_ID = l_nid;
302: -- wf_xml.EnqueueNotification(l_nid);
303: end if;
338: begin
339: -- Get the callback function.
340: select CALLBACK, CONTEXT, RECIPIENT_ROLE
341: into cb, ctx, role
342: from WF_NOTIFICATIONS
343: where NOTIFICATION_ID = nid;
344:
345: -- If there is no callback, just clear any error and return.
346: if (cb is null) then
400:
401: -- Disable_Recipient_Ntf_pref
402: -- Updates the recipient of a notification to DISABLED where
403: -- there has been a failure to deliver to their email address.
404: -- This function is triggered by the oracle.apps.wf.notification.send.failure
405: -- event.
406: function Disable_Recipient_Ntf_Pref(p_subscription_guid in raw,
407: p_event in out NOCOPY WF_EVENT_T) return varchar2
408: is
498: end if;
499:
500: select recipient_role
501: into recipient
502: from wf_notifications
503: where notification_id = nid;
504:
505: exception
506: when no_data_found then
681:
682: current_maxthreshold := wf_event.phase_maxthreshold;
683: wf_event.SetDispatchMode ('ASYNC');
684:
685: alertNid := WF_NOTIFICATION.send(role => 'SYSADMIN',
686: msg_type => 'WFMAIL',
687: msg_name => 'USER_PREF_UPDATE_REPORT');
688:
689: -- Set the attributes for the report. The message won't be dispatched
687: msg_name => 'USER_PREF_UPDATE_REPORT');
688:
689: -- Set the attributes for the report. The message won't be dispatched
690: -- until the commit is performed by the calling process.
691: WF_NOTIFICATION.setAttrText(nid => alertNid,
692: aname => 'NOTIFICATION_ID',
693: avalue => to_char(nid));
694: WF_NOTIFICATION.setAttrText(nid => alertNid,
695: aname => 'ROLE',
690: -- until the commit is performed by the calling process.
691: WF_NOTIFICATION.setAttrText(nid => alertNid,
692: aname => 'NOTIFICATION_ID',
693: avalue => to_char(nid));
694: WF_NOTIFICATION.setAttrText(nid => alertNid,
695: aname => 'ROLE',
696: avalue => role);
697: WF_NOTIFICATION.setAttrText(nid => alertNid,
698: aname => 'UPDATED_USER_REPORT',
693: avalue => to_char(nid));
694: WF_NOTIFICATION.setAttrText(nid => alertNid,
695: aname => 'ROLE',
696: avalue => role);
697: WF_NOTIFICATION.setAttrText(nid => alertNid,
698: aname => 'UPDATED_USER_REPORT',
699: avalue => errorReport);
700:
701: -- Ensure that the subject is correctly populated.
698: aname => 'UPDATED_USER_REPORT',
699: avalue => errorReport);
700:
701: -- Ensure that the subject is correctly populated.
702: WF_NOTIFICATION.Denormalize_Notification(alertNid);
703:
704: -- Resetting the wf_event.phase_maxthreshold value
705: wf_event.phase_maxthreshold := current_maxthreshold;
706:
883: error_result := errmsg;
884:
885: -- Set MAIL_ERROR_NAME attribute
886: begin
887: Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_NAME', errname);
888: exception
889: when no_program_unit then
890: raise;
891: when others then
890: raise;
891: when others then
892: if (wf_core.error_name = 'WFNTF_ATTR') then
893: Wf_Core.Clear;
894: Wf_Notification.AddAttr(nid, 'MAIL_ERROR_NAME');
895: Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_NAME', errname);
896: end if;
897: end;
898:
891: when others then
892: if (wf_core.error_name = 'WFNTF_ATTR') then
893: Wf_Core.Clear;
894: Wf_Notification.AddAttr(nid, 'MAIL_ERROR_NAME');
895: Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_NAME', errname);
896: end if;
897: end;
898:
899: -- Bug# 2301881 setting the values for message attributes
898:
899: -- Bug# 2301881 setting the values for message attributes
900: -- Set MAIL_ERROR_MESSAGE attribute
901: begin
902: Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_MESSAGE', errmsg);
903: exception
904: when no_program_unit then
905: raise;
906: when others then
905: raise;
906: when others then
907: if (wf_core.error_name = 'WFNTF_ATTR') then
908: Wf_Core.Clear;
909: Wf_Notification.AddAttr(nid, 'MAIL_ERROR_MESSAGE');
910: Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_MESSAGE', errmsg);
911: end if;
912: end;
913:
906: when others then
907: if (wf_core.error_name = 'WFNTF_ATTR') then
908: Wf_Core.Clear;
909: Wf_Notification.AddAttr(nid, 'MAIL_ERROR_MESSAGE');
910: Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_MESSAGE', errmsg);
911: end if;
912: end;
913:
914: -- Set MAIL_VALUE_FOUND attribute
912: end;
913:
914: -- Set MAIL_VALUE_FOUND attribute
915: begin
916: Wf_Notification.SetAttrText(nid, 'MAIL_VALUE_FOUND', value_found);
917: exception
918: when no_program_unit then
919: raise;
920: when others then
919: raise;
920: when others then
921: if (wf_core.error_name = 'WFNTF_ATTR') then
922: Wf_Core.Clear;
923: Wf_Notification.AddAttr(nid, 'MAIL_VALUE_FOUND');
924: Wf_Notification.SetAttrText(nid, 'MAIL_VALUE_FOUND', value_found);
925: end if;
926: end;
927:
920: when others then
921: if (wf_core.error_name = 'WFNTF_ATTR') then
922: Wf_Core.Clear;
923: Wf_Notification.AddAttr(nid, 'MAIL_VALUE_FOUND');
924: Wf_Notification.SetAttrText(nid, 'MAIL_VALUE_FOUND', value_found);
925: end if;
926: end;
927:
928: -- Set MAIL_EXP_VALUES attribute
926: end;
927:
928: -- Set MAIL_EXP_VALUES attribute
929: begin
930: Wf_Notification.SetAttrText(nid, 'MAIL_EXP_VALUES', remarks);
931: exception
932: when no_program_unit then
933: raise;
934: when others then
933: raise;
934: when others then
935: if (wf_core.error_name = 'WFNTF_ATTR') then
936: Wf_Core.Clear;
937: Wf_Notification.AddAttr(nid, 'MAIL_EXP_VALUES');
938: Wf_Notification.SetAttrText(nid, 'MAIL_EXP_VALUES', remarks);
939: end if;
940: end;
941:
934: when others then
935: if (wf_core.error_name = 'WFNTF_ATTR') then
936: Wf_Core.Clear;
937: Wf_Notification.AddAttr(nid, 'MAIL_EXP_VALUES');
938: Wf_Notification.SetAttrText(nid, 'MAIL_EXP_VALUES', remarks);
939: end if;
940: end;
941:
942: -- End Bug# 2301881
946: errstack := ' ';
947: end if;
948:
949: begin
950: Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_STACK', errstack);
951: exception
952: when no_program_unit then
953: raise;
954: when others then
953: raise;
954: when others then
955: if (wf_core.error_name = 'WFNTF_ATTR') then
956: Wf_Core.Clear;
957: Wf_Notification.AddAttr(nid, 'MAIL_ERROR_STACK');
958: Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_STACK', errstack);
959: end if;
960: end;
961:
954: when others then
955: if (wf_core.error_name = 'WFNTF_ATTR') then
956: Wf_Core.Clear;
957: Wf_Notification.AddAttr(nid, 'MAIL_ERROR_STACK');
958: Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_STACK', errstack);
959: end if;
960: end;
961:
962: -- Set the mail_status to INVALID (mailer will pick this up)
959: end if;
960: end;
961:
962: -- Set the mail_status to INVALID (mailer will pick this up)
963: update WF_NOTIFICATIONS
964: set MAIL_STATUS = 'INVALID'
965: where NOTIFICATION_ID = nid;
966: -- wf_xml.enqueueNotification(nid);
967:
966: -- wf_xml.enqueueNotification(nid);
967:
968: select recipient_role, group_id, message_type, message_name
969: into role, group_id, mType, mName
970: from wf_notifications
971: where notification_id = nid;
972:
973: wf_event.AddParameterToList('NOTIFICATION_ID', nid, parameterlist);
974: wf_event.AddParameterToList('ROLE', role, parameterlist);
981: 'Raising the Send event');
982: end if;
983:
984: --Raise the event
985: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
986: p_event_key => to_char(nid),
987: p_parameters => parameterlist);
988:
989: exception
1300: -- 'DATE', decode(WMA.FORMAT,
1301: -- '', to_char(WNA.DATE_VALUE),
1302: -- to_char(WNA.DATE_VALUE, WMA.FORMAT)),
1303: --
1304: -- <
1306: 'LOOKUP', WNA.TEXT_VALUE,
1307: WNA.TEXT_VALUE) VALUE,
1308: WNA.DATE_VALUE -- value is Date type <
1301: -- '', to_char(WNA.DATE_VALUE),
1302: -- to_char(WNA.DATE_VALUE, WMA.FORMAT)),
1303: --
1304: -- <
1305: --'DATE', wf_notification_util.GetCalendarDate(p_nid=>nid, p_date=>WNA.DATE_VALUE, p_date_format=>WMA.FORMAT),
1306: 'LOOKUP', WNA.TEXT_VALUE,
1307: WNA.TEXT_VALUE) VALUE,
1308: WNA.DATE_VALUE -- value is Date type <
1309: from WF_NOTIFICATION_ATTRIBUTES WNA,
1305: --'DATE', wf_notification_util.GetCalendarDate(p_nid=>nid, p_date=>WNA.DATE_VALUE, p_date_format=>WMA.FORMAT),
1306: 'LOOKUP', WNA.TEXT_VALUE,
1307: WNA.TEXT_VALUE) VALUE,
1308: WNA.DATE_VALUE -- value is Date type <
1309: from WF_NOTIFICATION_ATTRIBUTES WNA,
1310: WF_NOTIFICATIONS WN,
1311: WF_MESSAGE_ATTRIBUTES_VL WMA
1312: where WNA.NOTIFICATION_ID = nid
1313: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1306: 'LOOKUP', WNA.TEXT_VALUE,
1307: WNA.TEXT_VALUE) VALUE,
1308: WNA.DATE_VALUE -- value is Date type <
1309: from WF_NOTIFICATION_ATTRIBUTES WNA,
1310: WF_NOTIFICATIONS WN,
1311: WF_MESSAGE_ATTRIBUTES_VL WMA
1312: where WNA.NOTIFICATION_ID = nid
1313: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1314: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
1339: wf_mail.g_close_text_delimiter || g_newLine ||
1340: GetLovList(rec.format);
1341: ELSIF (rec.type = 'DATE' AND rec.DATE_VALUE is not null) then
1342: -- <
1343: buffer := buffer || wf_notification_util.GetCalendarDate(nid, rec.DATE_VALUE, rec.format, false)
1344: || wf_mail.g_close_text_delimiter
1345: || g_newLine;
1346: else
1347: -- VARCHAR2, NUMBER, : use value directly.
1378: to_char(WNA.NUMBER_VALUE, WMA.FORMAT)),
1379: --'DATE', decode(WMA.FORMAT,
1380: -- '', to_char(WNA.DATE_VALUE),
1381: -- to_char(WNA.DATE_VALUE, WMA.FORMAT)),
1382: -- 'DATE', wf_notification_util.GetCalendarDate(nid, WNA.DATE_VALUE, WMA.FORMAT, true),
1383: 'LOOKUP', WNA.TEXT_VALUE,
1384: WNA.TEXT_VALUE) VALUE,
1385: WNA.DATE_VALUE
1386: from WF_NOTIFICATION_ATTRIBUTES WNA,
1382: -- 'DATE', wf_notification_util.GetCalendarDate(nid, WNA.DATE_VALUE, WMA.FORMAT, true),
1383: 'LOOKUP', WNA.TEXT_VALUE,
1384: WNA.TEXT_VALUE) VALUE,
1385: WNA.DATE_VALUE
1386: from WF_NOTIFICATION_ATTRIBUTES WNA,
1387: WF_NOTIFICATIONS WN,
1388: WF_MESSAGE_ATTRIBUTES_VL WMA
1389: where WNA.NOTIFICATION_ID = nid
1390: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1383: 'LOOKUP', WNA.TEXT_VALUE,
1384: WNA.TEXT_VALUE) VALUE,
1385: WNA.DATE_VALUE
1386: from WF_NOTIFICATION_ATTRIBUTES WNA,
1387: WF_NOTIFICATIONS WN,
1388: WF_MESSAGE_ATTRIBUTES_VL WMA
1389: where WNA.NOTIFICATION_ID = nid
1390: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1391: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
1614: from wf_item_activity_statuses_h ias
1615: where ias.item_type = itemType
1616: and ias.item_key = itemKey)
1617: iantf,
1618: wf_notifications ntf
1619: where iantf.notification_id = ntf.group_id
1620: AND ntf.group_id = nid
1621:
1622: UNION ALL
1636: from wf_item_activity_statuses_h ias
1637: where ias.item_type = itemType
1638: and ias.item_key = itemKey)
1639: iantf,
1640: wf_notifications ntf
1641: where iantf.notification_id = ntf.group_id
1642: and ntf.group_id = nid
1643:
1644: UNION ALL
1657: where ias.item_type = itemType
1658: and ias.item_key = itemKey
1659: )
1660: iantf,
1661: wf_notifications ntf
1662: where iantf.notification_id = ntf.group_id
1663: and ntf.group_id = nid
1664: and ntf.from_role is not null
1665:
1684: begin
1685:
1686: select context
1687: into context
1688: from wf_notifications
1689: where notification_id = nid;
1690:
1691: -- get item type and item key from the context
1692: if context is not null then
1831:
1832: if (l_requestee is null) then
1833: l_requestee := g_moreInfoRequestee;
1834: end if;
1835: body := body || wf_notification.SubstituteSpecialChars(l_requestee);
1836: -- body := body || GetMoreInfoLOV(nid, g_to_role);
1837:
1838: -- ankung (continuation of above)
1839: body := body || wf_mail.g_close_html_delimiter;
1898: '', to_char(WNA.DATE_VALUE),
1899: to_char(WNA.DATE_VALUE, WMA.FORMAT)),
1900: 'LOOKUP', WNA.TEXT_VALUE,
1901: WNA.TEXT_VALUE) VALUE
1902: from WF_NOTIFICATION_ATTRIBUTES WNA,
1903: WF_NOTIFICATIONS WN,
1904: WF_MESSAGE_ATTRIBUTES_VL WMA
1905: where WNA.NOTIFICATION_ID = nid
1906: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1899: to_char(WNA.DATE_VALUE, WMA.FORMAT)),
1900: 'LOOKUP', WNA.TEXT_VALUE,
1901: WNA.TEXT_VALUE) VALUE
1902: from WF_NOTIFICATION_ATTRIBUTES WNA,
1903: WF_NOTIFICATIONS WN,
1904: WF_MESSAGE_ATTRIBUTES_VL WMA
1905: where WNA.NOTIFICATION_ID = nid
1906: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1907: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
1981: --'DATE', decode(WMA.FORMAT,
1982: -- '', to_char(WNA.DATE_VALUE),
1983: -- to_char(WNA.DATE_VALUE, WMA.FORMAT)),
1984: -- <
1985: -- 'DATE', wf_notification_util.GetCalendarDate(nid, WNA.DATE_VALUE, WMA.FORMAT, true),
1986: --
1987: 'LOOKUP', WNA.TEXT_VALUE,
1988: WNA.TEXT_VALUE) VALUE,
1989: WNA.DATE_VALUE -- << sstomar: bug8430385 >>
1986: --
1987: 'LOOKUP', WNA.TEXT_VALUE,
1988: WNA.TEXT_VALUE) VALUE,
1989: WNA.DATE_VALUE -- << sstomar: bug8430385 >>
1990: from WF_NOTIFICATION_ATTRIBUTES WNA,
1991: WF_NOTIFICATIONS WN,
1992: WF_MESSAGE_ATTRIBUTES_VL WMA
1993: where WNA.NOTIFICATION_ID = nid
1994: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1987: 'LOOKUP', WNA.TEXT_VALUE,
1988: WNA.TEXT_VALUE) VALUE,
1989: WNA.DATE_VALUE -- << sstomar: bug8430385 >>
1990: from WF_NOTIFICATION_ATTRIBUTES WNA,
1991: WF_NOTIFICATIONS WN,
1992: WF_MESSAGE_ATTRIBUTES_VL WMA
1993: where WNA.NOTIFICATION_ID = nid
1994: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1995: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
2042:
2043: -- <
2044: elsif (rec.type = 'DATE' AND rec.DATE_VALUE is not null) THEN
2045: str_buffer := str_buffer
2046: || wf_notification_util.GetCalendarDate(nid, rec.DATE_VALUE, rec.FORMAT, false)
2047: || wf_mail.g_close_html_delimiter
2048: || g_newLine;
2049: else
2050: -- NOTE: <
2104: is
2105: -- SQL Statement for fetching URL RESPONSE attributes.
2106: cursor c1 is
2107: select WMA.NAME, WMA.DISPLAY_NAME, WNA.TEXT_VALUE, WMA.DESCRIPTION
2108: from WF_NOTIFICATION_ATTRIBUTES WNA,
2109: WF_NOTIFICATIONS WN,
2110: WF_MESSAGE_ATTRIBUTES_VL WMA
2111: where WNA.NOTIFICATION_ID = nid
2112: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
2105: -- SQL Statement for fetching URL RESPONSE attributes.
2106: cursor c1 is
2107: select WMA.NAME, WMA.DISPLAY_NAME, WNA.TEXT_VALUE, WMA.DESCRIPTION
2108: from WF_NOTIFICATION_ATTRIBUTES WNA,
2109: WF_NOTIFICATIONS WN,
2110: WF_MESSAGE_ATTRIBUTES_VL WMA
2111: where WNA.NOTIFICATION_ID = nid
2112: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
2113: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
2148: buffer := buffer||'
';
2149: if (rec.description is not null) then
2150: buffer := buffer||rec.description||'
';
2151: end if;
2152: buffer := buffer|| '';
2153: buffer := buffer||' '||rec.display_name;
2154: buffer := buffer||''||'';
2155: buffer := buffer||g_newLine;
2156: end loop;
2176: to_char(WNA.DATE_VALUE, WMA.FORMAT)),
2177: 'LOOKUP', WNA.TEXT_VALUE,
2178: WNA.TEXT_VALUE), WMA.DESCRIPTION
2179: into disp_name, attr_type, attr_format, attr_value, attr_desc
2180: from WF_NOTIFICATION_ATTRIBUTES WNA,
2181: WF_NOTIFICATIONS WN,
2182: WF_MESSAGE_ATTRIBUTES_VL WMA
2183: where WNA.NOTIFICATION_ID = nid
2184: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
2177: 'LOOKUP', WNA.TEXT_VALUE,
2178: WNA.TEXT_VALUE), WMA.DESCRIPTION
2179: into disp_name, attr_type, attr_format, attr_value, attr_desc
2180: from WF_NOTIFICATION_ATTRIBUTES WNA,
2181: WF_NOTIFICATIONS WN,
2182: WF_MESSAGE_ATTRIBUTES_VL WMA
2183: where WNA.NOTIFICATION_ID = nid
2184: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
2185: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
2298:
2299: begin
2300: -- BLAF recommends displaying date with the TIME element
2301:
2302: -- n_due_date_text := to_char(n_due_date, Wf_Notification.g_nls_date_mask);
2303: -- n_start_date_text := to_char(n_start_date, Wf_Notification.g_nls_date_mask);
2304: --
2305: n_start_date_text := wf_notification_util.GetCalendarDate(n_nid, n_start_date, null, true);
2306: n_due_date_text := wf_notification_util.GetCalendarDate(n_nid, n_due_date, null, true);
2299: begin
2300: -- BLAF recommends displaying date with the TIME element
2301:
2302: -- n_due_date_text := to_char(n_due_date, Wf_Notification.g_nls_date_mask);
2303: -- n_start_date_text := to_char(n_start_date, Wf_Notification.g_nls_date_mask);
2304: --
2305: n_start_date_text := wf_notification_util.GetCalendarDate(n_nid, n_start_date, null, true);
2306: n_due_date_text := wf_notification_util.GetCalendarDate(n_nid, n_due_date, null, true);
2307:
2301:
2302: -- n_due_date_text := to_char(n_due_date, Wf_Notification.g_nls_date_mask);
2303: -- n_start_date_text := to_char(n_start_date, Wf_Notification.g_nls_date_mask);
2304: --
2305: n_start_date_text := wf_notification_util.GetCalendarDate(n_nid, n_start_date, null, true);
2306: n_due_date_text := wf_notification_util.GetCalendarDate(n_nid, n_due_date, null, true);
2307:
2308: n_end_date_text := wf_notification_util.GetCalendarDate(n_nid, n_end_date, null, true);
2309:
2302: -- n_due_date_text := to_char(n_due_date, Wf_Notification.g_nls_date_mask);
2303: -- n_start_date_text := to_char(n_start_date, Wf_Notification.g_nls_date_mask);
2304: --
2305: n_start_date_text := wf_notification_util.GetCalendarDate(n_nid, n_start_date, null, true);
2306: n_due_date_text := wf_notification_util.GetCalendarDate(n_nid, n_due_date, null, true);
2307:
2308: n_end_date_text := wf_notification_util.GetCalendarDate(n_nid, n_end_date, null, true);
2309:
2310: if (n_priority > 66) then
2304: --
2305: n_start_date_text := wf_notification_util.GetCalendarDate(n_nid, n_start_date, null, true);
2306: n_due_date_text := wf_notification_util.GetCalendarDate(n_nid, n_due_date, null, true);
2307:
2308: n_end_date_text := wf_notification_util.GetCalendarDate(n_nid, n_end_date, null, true);
2309:
2310: if (n_priority > 66) then
2311: --Bug 2774891 fix - sacsharm
2312: --n_priority_text := wf_core.substitute('WFTKN', 'HIGH');
2504: select 1 into fyi
2505: from dual
2506: where not exists (select NULL
2507: from WF_MESSAGE_ATTRIBUTES MA,
2508: WF_NOTIFICATIONS N
2509: where N.NOTIFICATION_ID = nid
2510: and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
2511: and MA.MESSAGE_NAME = N.MESSAGE_NAME
2512: and MA.SUBTYPE = 'RESPOND');
2530: end if;
2531:
2532: select message_type, message_name
2533: into mType, mName
2534: from wf_notifications
2535: where notification_id = nid;
2536:
2537: -- Now that the template name has been derrived, see
2538: -- if the default value has been overridden.
2608: n_nid_str := 'NID '||to_char(nid);
2609: return;
2610: end if;
2611:
2612: Wf_Notification.GetSignatureRequired(n_sig_policy, nid, g_sig_required,
2613: g_fwk_flavor, g_email_flavor, g_render);
2614:
2615: -- No signature required for this notification
2616: if (g_sig_required = 'N') then
2681: is
2682: cursor headers(nid in Number) is
2683: select WMA.NAME
2684: from WF_MESSAGE_ATTRIBUTES_VL WMA,
2685: WF_NOTIFICATION_ATTRIBUTES WNA,
2686: WF_NOTIFICATIONS WN
2687: where WNA.NOTIFICATION_ID = nid
2688: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
2689: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
2682: cursor headers(nid in Number) is
2683: select WMA.NAME
2684: from WF_MESSAGE_ATTRIBUTES_VL WMA,
2685: WF_NOTIFICATION_ATTRIBUTES WNA,
2686: WF_NOTIFICATIONS WN
2687: where WNA.NOTIFICATION_ID = nid
2688: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
2689: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
2690: and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
2696: order by WMA.SEQUENCE;
2697:
2698: nid number;
2699: attrList varchar2(4000);
2700: cells wf_notification.tdType;
2701: j pls_integer;
2702: pos pls_integer;
2703: language varchar2(30);
2704: headerTable varchar2(32000);
2722:
2723: begin
2724: SELECT due_date, from_user
2725: INTO l_due_date, l_from_user
2726: FROM wf_notifications
2727: WHERE notification_id = nid;
2728: exception
2729: when no_data_found then
2730: wf_core.token('NID', to_char(nid));
2742:
2743: document := '';
2744: if display_type = g_ntfDocText then
2745: if length(attrList) > 0 then
2746: document := wf_notification.wf_msg_attr(nid, attrList, display_type);
2747: end if;
2748: elsif display_type = g_ntfDocHtml then
2749: document := '
';Line 2791: wf_notification.NTF_Table(cells => cells, col => 3, type => 'N'||l_dir, | ' || g_newLine;Line 2797: wf_notification.set_ntf_table_type('N'); | ' || g_newLine; 2796: if length(attrList) > 0 then 2797: wf_notification.set_ntf_table_type('N'); 2798: wf_notification.set_ntf_table_direction(l_dir); 2799: document := document||wf_notification.wf_msg_attr(nid, attrList, display_type); 2800: wf_notification.set_ntf_table_type('V'); 2801: end if; Line 2798: wf_notification.set_ntf_table_direction(l_dir); | ' || g_newLine; 2796: if length(attrList) > 0 then 2797: wf_notification.set_ntf_table_type('N'); 2798: wf_notification.set_ntf_table_direction(l_dir); 2799: document := document||wf_notification.wf_msg_attr(nid, attrList, display_type); 2800: wf_notification.set_ntf_table_type('V'); 2801: end if; 2802: document := document || ' |
2795: document := document || '
2796: if length(attrList) > 0 then
2797: wf_notification.set_ntf_table_type('N');
2798: wf_notification.set_ntf_table_direction(l_dir);
2799: document := document||wf_notification.wf_msg_attr(nid, attrList, display_type);
2800: wf_notification.set_ntf_table_type('V');
2801: end if;
2802: document := document || ''||g_newLine;
2803: else
2804: document := '';
2905: n_dir varchar2(16);
2906:
2907: begin
2908: -- Get notification information
2909: -- Bug 2094159 get from_user from wf_notifications
2910: begin
2911: select STATUS, MAIL_STATUS, ACCESS_KEY,
2912: RECIPIENT_ROLE, PRIORITY, USER_COMMENT,
2913: BEGIN_DATE, END_DATE, DUE_DATE, FROM_USER,
2915: into n_status, n_mstatus, n_key,
2916: n_to_role, n_priority, n_comment,
2917: n_start_date, n_end_date, n_due_date, n_from_user,
2918: n_more_info_role
2919: from WF_NOTIFICATIONS
2920: where NOTIFICATION_ID = nid;
2921: exception
2922: when no_data_found then
2923: wf_core.token('NID', to_char(nid));
2940: -- Timezone will not be supported in this version of the GetMessage
2941: -- API.
2942: n_timezone := '';
2943:
2944: if (wf_notification.HideMoreInfo(nid) = 'N') then
2945: if(n_more_info_role is not null) then
2946: n_to_role := n_more_info_role;
2947: g_to_role := n_more_info_role;
2948: g_moreinfo := 'SUB';
2956: Wf_Directory.GetRoleInfo(n_to_role, r_dname, r_email, r_ntf_pref,
2957: r_language, r_territory);
2958: r_ntf_pref := nvl(r_ntf_pref, 'QUERY');
2959:
2960: wf_notification.GetComments(nid, g_ntfDocText,
2961: n_text_history, n_last_ques);
2962: wf_notification.GetComments(nid, g_ntfDocHtml,
2963: n_html_history, n_last_ques);
2964: if n_text_history is not null or n_text_history <> '' then
2958: r_ntf_pref := nvl(r_ntf_pref, 'QUERY');
2959:
2960: wf_notification.GetComments(nid, g_ntfDocText,
2961: n_text_history, n_last_ques);
2962: wf_notification.GetComments(nid, g_ntfDocHtml,
2963: n_html_history, n_last_ques);
2964: if n_text_history is not null or n_text_history <> '' then
2965: if isBiDi(r_language) then
2966: n_text_history := n_text_history||' '|| g_ntfHistory;
2990:
2991: -- Bug 2375920 get signature policy for the notification
2992: Wf_Mail.GetSignaturePolicy(nid, n_sig_policy);
2993:
2994: n_subject := WF_NOTIFICATION.GetSubject(nid, 'text/plain');
2995:
2996: -- We will always fetch plain text version of the message because
2997: -- Because for sendmail MAILATTH case, we need to send out html message
2998: -- body as attachment and then the plain text message as the body.
2997: -- Because for sendmail MAILATTH case, we need to send out html message
2998: -- body as attachment and then the plain text message as the body.
2999: -- For MAPI MAILATTH and MAILHTML cases, same thing.
3000: if isBiDi(r_language) then
3001: WF_NOTIFICATION.Set_NTF_Table_Direction('R');
3002: else
3003: WF_NOTIFICATION.Set_NTF_Table_Direction('L');
3004: end if;
3005:
2999: -- For MAPI MAILATTH and MAILHTML cases, same thing.
3000: if isBiDi(r_language) then
3001: WF_NOTIFICATION.Set_NTF_Table_Direction('R');
3002: else
3003: WF_NOTIFICATION.Set_NTF_Table_Direction('L');
3004: end if;
3005:
3006: n_text_body := WF_NOTIFICATION.GetBody(nid, g_ntfDocText);
3007:
3002: else
3003: WF_NOTIFICATION.Set_NTF_Table_Direction('L');
3004: end if;
3005:
3006: n_text_body := WF_NOTIFICATION.GetBody(nid, g_ntfDocText);
3007:
3008: GetHeaderTable(r_language||':'||to_char(nid), g_ntfDocText,
3009: t_headerText, dummy);
3010:
3008: GetHeaderTable(r_language||':'||to_char(nid), g_ntfDocText,
3009: t_headerText, dummy);
3010:
3011: if r_ntf_pref in ('MAILHTML', 'MAILATTH', 'MAILHTML2') then
3012: n_html_body := WF_NOTIFICATION.GetBody(nid, g_ntfDocHtml);
3013: GetHeaderTable(r_language||':'||to_char(nid),
3014: g_ntfDocHtml, t_headerHTML, dummy);
3015:
3016: -- Extracts content between
3142:
3143: -- Retrieve errror attributes for INVALID message
3144: if (t_name = 'OPEN_INVALID') then
3145: begin
3146: err_name := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_NAME');
3147: err_message := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_MESSAGE');
3148: err_invalid := Wf_Notification.GetAttrText(nid, 'MAIL_VALUE_FOUND');
3149: err_expected := Wf_Notification.GetAttrText(nid, 'MAIL_EXP_VALUES');
3150: exception
3143: -- Retrieve errror attributes for INVALID message
3144: if (t_name = 'OPEN_INVALID') then
3145: begin
3146: err_name := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_NAME');
3147: err_message := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_MESSAGE');
3148: err_invalid := Wf_Notification.GetAttrText(nid, 'MAIL_VALUE_FOUND');
3149: err_expected := Wf_Notification.GetAttrText(nid, 'MAIL_EXP_VALUES');
3150: exception
3151: when others then null;
3144: if (t_name = 'OPEN_INVALID') then
3145: begin
3146: err_name := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_NAME');
3147: err_message := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_MESSAGE');
3148: err_invalid := Wf_Notification.GetAttrText(nid, 'MAIL_VALUE_FOUND');
3149: err_expected := Wf_Notification.GetAttrText(nid, 'MAIL_EXP_VALUES');
3150: exception
3151: when others then null;
3152: end;
3145: begin
3146: err_name := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_NAME');
3147: err_message := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_MESSAGE');
3148: err_invalid := Wf_Notification.GetAttrText(nid, 'MAIL_VALUE_FOUND');
3149: err_expected := Wf_Notification.GetAttrText(nid, 'MAIL_EXP_VALUES');
3150: exception
3151: when others then null;
3152: end;
3153: end if;
3695:
3696: begin
3697:
3698: -- BLAF requriement to display the date with TIME elelment
3699: -- n_due_date_text := to_char(n_due_date, Wf_Notification.g_nls_date_mask);
3700: -- n_start_date_text := to_char(n_start_date, Wf_Notification.g_nls_date_mask);
3701:
3702: --<< sstomar>: NLS changes, bug 7578922
3703: n_due_date_text := wf_notification_util.GetCalendarDate(n_nid, n_due_date, null, true);
3696: begin
3697:
3698: -- BLAF requriement to display the date with TIME elelment
3699: -- n_due_date_text := to_char(n_due_date, Wf_Notification.g_nls_date_mask);
3700: -- n_start_date_text := to_char(n_start_date, Wf_Notification.g_nls_date_mask);
3701:
3702: --<< sstomar>: NLS changes, bug 7578922
3703: n_due_date_text := wf_notification_util.GetCalendarDate(n_nid, n_due_date, null, true);
3704: n_start_date_text := wf_notification_util.GetCalendarDate(n_nid, n_start_date, null, true);
3699: -- n_due_date_text := to_char(n_due_date, Wf_Notification.g_nls_date_mask);
3700: -- n_start_date_text := to_char(n_start_date, Wf_Notification.g_nls_date_mask);
3701:
3702: --<< sstomar>: NLS changes, bug 7578922
3703: n_due_date_text := wf_notification_util.GetCalendarDate(n_nid, n_due_date, null, true);
3704: n_start_date_text := wf_notification_util.GetCalendarDate(n_nid, n_start_date, null, true);
3705: n_end_date_text := wf_notification_util.GetCalendarDate(n_nid, n_end_date, null, true);
3706:
3707: if (n_priority > 66) then
3700: -- n_start_date_text := to_char(n_start_date, Wf_Notification.g_nls_date_mask);
3701:
3702: --<< sstomar>: NLS changes, bug 7578922
3703: n_due_date_text := wf_notification_util.GetCalendarDate(n_nid, n_due_date, null, true);
3704: n_start_date_text := wf_notification_util.GetCalendarDate(n_nid, n_start_date, null, true);
3705: n_end_date_text := wf_notification_util.GetCalendarDate(n_nid, n_end_date, null, true);
3706:
3707: if (n_priority > 66) then
3708: --Bug 2774891 fix - sacsharm
3701:
3702: --<< sstomar>: NLS changes, bug 7578922
3703: n_due_date_text := wf_notification_util.GetCalendarDate(n_nid, n_due_date, null, true);
3704: n_start_date_text := wf_notification_util.GetCalendarDate(n_nid, n_start_date, null, true);
3705: n_end_date_text := wf_notification_util.GetCalendarDate(n_nid, n_end_date, null, true);
3706:
3707: if (n_priority > 66) then
3708: --Bug 2774891 fix - sacsharm
3709: --n_priority_text := wf_core.substitute('WFTKN', 'HIGH');
4417: is
4418: -- SQL Statement for fetching URL RESPONSE attributes.
4419: cursor c1 is
4420: select WMA.NAME, WMA.DISPLAY_NAME, WNA.TEXT_VALUE, WMA.DESCRIPTION
4421: from WF_NOTIFICATION_ATTRIBUTES WNA,
4422: WF_NOTIFICATIONS WN,
4423: WF_MESSAGE_ATTRIBUTES_VL WMA
4424: where WNA.NOTIFICATION_ID = nid
4425: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
4418: -- SQL Statement for fetching URL RESPONSE attributes.
4419: cursor c1 is
4420: select WMA.NAME, WMA.DISPLAY_NAME, WNA.TEXT_VALUE, WMA.DESCRIPTION
4421: from WF_NOTIFICATION_ATTRIBUTES WNA,
4422: WF_NOTIFICATIONS WN,
4423: WF_MESSAGE_ATTRIBUTES_VL WMA
4424: where WNA.NOTIFICATION_ID = nid
4425: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
4426: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
4468: if (rec.description is not null) then
4469: str_buffer := str_buffer||rec.description||'
';
4470: end if;
4471: str_buffer := str_buffer|| '';
4474: str_buffer := str_buffer||' '||rec.display_name;
4475: str_buffer := str_buffer||''||'';
4476: str_buffer := str_buffer||g_newLine;
4508: -- 'LOOKUP', WNA.TEXT_VALUE,
4509: -- WNA.TEXT_VALUE),
4510: WMA.DESCRIPTION
4511: into disp_name, attr_type, attr_format, attr_desc
4512: from WF_NOTIFICATION_ATTRIBUTES WNA,
4513: WF_NOTIFICATIONS WN,
4514: WF_MESSAGE_ATTRIBUTES_VL WMA
4515: where WNA.NOTIFICATION_ID = nid
4516: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
4509: -- WNA.TEXT_VALUE),
4510: WMA.DESCRIPTION
4511: into disp_name, attr_type, attr_format, attr_desc
4512: from WF_NOTIFICATION_ATTRIBUTES WNA,
4513: WF_NOTIFICATIONS WN,
4514: WF_MESSAGE_ATTRIBUTES_VL WMA
4515: where WNA.NOTIFICATION_ID = nid
4516: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
4517: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
4689: begin
4690:
4691: validateAccess := FND_PROFILE.value('WF_VALIDATE_NTF_ACCESS');
4692:
4693: Wf_Notification.GetSignatureRequired(p_sig_policy => n_sig_policy,
4694: p_nid => nid,
4695: p_sig_required => sig_required,
4696: p_fwk_sig_flavor => fwk_flavor,
4697: p_email_sig_flavor => email_flavor,
4819: begin
4820:
4821: SELECT context, callback
4822: into context, callback
4823: FROM wf_notifications
4824: where notification_id = nid;
4825:
4826: wf_engine.preserved_context := FALSE;
4827: if (callback is not null) then
4927: l_get_text := false;
4928:
4929: SELECT wm.body, wm.html_body
4930: INTO l_text_body, l_html_body
4931: FROM wf_notifications n, wf_messages_vl wm
4932: WHERE n.notification_id = p_nid
4933: AND n.message_name = wm.name
4934: AND n.message_type = wm.type;
4935:
4943:
4944: -- If HISTORY macro is defined, Action History would appear anyways. If the FYI notification
4945: -- was reassigned at least once, include the Action History if even macro is not defined
4946: if (l_comm_cnt > 0) then
4947: if (instrb(l_text_body, 'WF_NOTIFICATION(HISTORY)') = 0) then
4948: l_get_text := true;
4949: end if;
4950: if (instrb(l_html_body, 'WF_NOTIFICATION(HISTORY)') = 0) then
4951: l_get_html := true;
4946: if (l_comm_cnt > 0) then
4947: if (instrb(l_text_body, 'WF_NOTIFICATION(HISTORY)') = 0) then
4948: l_get_text := true;
4949: end if;
4950: if (instrb(l_html_body, 'WF_NOTIFICATION(HISTORY)') = 0) then
4951: l_get_html := true;
4952: end if;
4953: end if;
4954: close c_comm;
4956: -- Response Required notification
4957: else
4958: -- If HISTORY macro is defined, Action History would appear anyways. Otherwise
4959: -- display it in the e-mail
4960: if (instrb(l_text_body, 'WF_NOTIFICATION(HISTORY)') = 0) then
4961: l_get_text := true;
4962: end if;
4963: if (instrb(l_html_body, 'WF_NOTIFICATION(HISTORY)') = 0) then
4964: l_get_html := true;
4959: -- display it in the e-mail
4960: if (instrb(l_text_body, 'WF_NOTIFICATION(HISTORY)') = 0) then
4961: l_get_text := true;
4962: end if;
4963: if (instrb(l_html_body, 'WF_NOTIFICATION(HISTORY)') = 0) then
4964: l_get_html := true;
4965: end if;
4966: end if;
4967:
4966: end if;
4967:
4968: -- Call the GetComments2 procedure to get the Action History
4969: if (l_get_text) then
4970: Wf_Notification.GetComments2(p_nid => Get_Action_History.p_nid,
4971: p_display_type => wf_notification.doc_text,
4972: p_hide_reassign => 'N',
4973: p_hide_requestinfo => 'N',
4974: p_action_history => l_text_history);
4967:
4968: -- Call the GetComments2 procedure to get the Action History
4969: if (l_get_text) then
4970: Wf_Notification.GetComments2(p_nid => Get_Action_History.p_nid,
4971: p_display_type => wf_notification.doc_text,
4972: p_hide_reassign => 'N',
4973: p_hide_requestinfo => 'N',
4974: p_action_history => l_text_history);
4975: end if;
4973: p_hide_requestinfo => 'N',
4974: p_action_history => l_text_history);
4975: end if;
4976: if (l_get_html)then
4977: Wf_Notification.GetComments2(p_nid => Get_Action_History.p_nid,
4978: p_display_type => wf_notification.doc_html,
4979: p_hide_reassign => 'N',
4980: p_hide_requestinfo => 'N',
4981: p_action_history => l_html_history);
4974: p_action_history => l_text_history);
4975: end if;
4976: if (l_get_html)then
4977: Wf_Notification.GetComments2(p_nid => Get_Action_History.p_nid,
4978: p_display_type => wf_notification.doc_html,
4979: p_hide_reassign => 'N',
4980: p_hide_requestinfo => 'N',
4981: p_action_history => l_html_history);
4982: end if;
4998:
4999:
5000: -- GetLOBMessage4 - get email message data as a LOB
5001: -- Bug 10202313: Added n_status, n_mstatus parameters to store the status, mail_status
5002: -- columns of wf_notifications table which are propagated from WF_XML.GenerateMessage() API
5003: --
5004: -- IN
5005: -- notification id
5006: -- mailer node name
5159: into n_key,
5160: n_priority, n_comment,
5161: n_start_date, n_end_date, n_due_date, n_from_user,
5162: n_more_info_role
5163: from WF_NOTIFICATIONS
5164: where NOTIFICATION_ID = nid;
5165: exception
5166: when no_data_found then
5167: wf_core.token('NID', to_char(nid));
5178:
5179:
5180: -- More information processing
5181: g_moreinfo := NULL;
5182: if (wf_notification.HideMoreInfo(nid) = 'N') then
5183: if(n_more_info_role is not null) then
5184: n_to_role := n_more_info_role;
5185: g_to_role := n_more_info_role;
5186: -- Flags that template for More Info submission needs to be used
5202: ProcessSignaturePolicy(nid, n_sig_policy, n_status, n_mstatus,
5203: n_key, node, t_type, t_name, n_nid_str);
5204:
5205: if isBiDi(r_language) then
5206: WF_NOTIFICATION.Set_NTF_Table_Direction('R');
5207: else
5208: WF_NOTIFICATION.Set_NTF_Table_Direction('L');
5209: end if;
5210:
5204:
5205: if isBiDi(r_language) then
5206: WF_NOTIFICATION.Set_NTF_Table_Direction('R');
5207: else
5208: WF_NOTIFICATION.Set_NTF_Table_Direction('L');
5209: end if;
5210:
5211: -- PLSQL Action History for the notification will not be explicitly processed by the
5212: -- mailer now. The notification sub-system would handle this as part of GetFullBody.
5250: n_text_timezone := wf_mail_util.getTimezone(g_ntfDocText);
5251: n_html_timezone := wf_mail_util.getTimezone(g_ntfDocHtml);
5252:
5253: step := 'Getting subject';
5254: n_subject := WF_NOTIFICATION.GetSubject(nid, 'text/plain');
5255:
5256: -- We will always fetch plain text version of the message because
5257: -- Because for sendmail MAILATTH case, we need to send out html message
5258: -- body as attachment and then the plain text message as the body.
5275: end_of_message := FALSE;
5276: step := 'Getting text/plain body';
5277: begin
5278: while not (end_of_message) loop
5279: WF_NOTIFICATION.GetFullBody(nid, buffer, end_of_message,
5280: g_ntfDocText);
5281: if buffer is not null and length(buffer) > 0 then
5282: DBMS_LOB.WriteAppend(g_LOBTable(g_text_messageIdx).temp_lob,
5283: length(buffer), buffer);
5310: end_of_message := FALSE;
5311: step := 'Getting text/html body';
5312: begin
5313: while not (end_of_message) loop
5314: WF_NOTIFICATION.GetFullBody(nid, buffer, end_of_message,
5315: g_ntfDocHtml);
5316: if buffer is not null and length(buffer) > 0 then
5317: DBMS_LOB.WriteAppend(g_LOBTable(g_html_messageIdx).temp_lob,
5318: length(buffer), buffer);
5359: -- to make the WARNING message to the responder more
5360: -- user-friendly
5361: if (t_name in ('OPEN_INVALID', 'OPEN_INVALID_MORE_INFO')) then
5362: begin
5363: err_name := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_NAME');
5364: err_message := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_MESSAGE');
5365: err_invalid := Wf_Notification.GetAttrText(nid, 'MAIL_VALUE_FOUND');
5366: err_expected := Wf_Notification.GetAttrText(nid, 'MAIL_EXP_VALUES');
5367: exception
5360: -- user-friendly
5361: if (t_name in ('OPEN_INVALID', 'OPEN_INVALID_MORE_INFO')) then
5362: begin
5363: err_name := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_NAME');
5364: err_message := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_MESSAGE');
5365: err_invalid := Wf_Notification.GetAttrText(nid, 'MAIL_VALUE_FOUND');
5366: err_expected := Wf_Notification.GetAttrText(nid, 'MAIL_EXP_VALUES');
5367: exception
5368: when others then null;
5361: if (t_name in ('OPEN_INVALID', 'OPEN_INVALID_MORE_INFO')) then
5362: begin
5363: err_name := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_NAME');
5364: err_message := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_MESSAGE');
5365: err_invalid := Wf_Notification.GetAttrText(nid, 'MAIL_VALUE_FOUND');
5366: err_expected := Wf_Notification.GetAttrText(nid, 'MAIL_EXP_VALUES');
5367: exception
5368: when others then null;
5369: end;
5362: begin
5363: err_name := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_NAME');
5364: err_message := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_MESSAGE');
5365: err_invalid := Wf_Notification.GetAttrText(nid, 'MAIL_VALUE_FOUND');
5366: err_expected := Wf_Notification.GetAttrText(nid, 'MAIL_EXP_VALUES');
5367: exception
5368: when others then null;
5369: end;
5370: end if;
5664: select status,
5665: mail_status
5666: into n_status,
5667: n_mstatus
5668: from WF_NOTIFICATIONS
5669: where NOTIFICATION_ID = nid;
5670: exception
5671: when no_data_found then
5672: wf_core.token('NID', to_char(nid));
5804: -- Get notification information
5805: begin
5806: select RECIPIENT_ROLE
5807: into n_to_role
5808: from WF_NOTIFICATIONS
5809: where NOTIFICATION_ID = nid;
5810: exception
5811: when no_data_found then
5812: wf_core.token('NID', to_char(nid));
5934: -- Bug 1753464 included sort order for the query
5935: -- Bug 2439529 Altered query to use UNION instead of OR.
5936: cursor c1 is
5937: select NOTIFICATION_ID, RECIPIENT_ROLE, ACCESS_KEY, PRIORITY, DUE_DATE
5938: from WF_NOTIFICATIONS
5939: where STATUS = 'OPEN'
5940: and RECIPIENT_ROLE IN
5941: (select role from dual
5942: union
6074: n_key := rec.access_key;
6075: to_name := rec.recipient_role;
6076:
6077: -- Get and token subsitute subject
6078: n_subject := WF_NOTIFICATION.GetSubject(nid, 'text/plain');
6079:
6080: if contType = g_ntfDocText then
6081: n_buf := lpad(to_char(nid), 7, ' ')||' '||
6082: rpad(substr(dname, 1, 42), 42, ' ')||' ';
6412: err_message varchar2(2000);
6413: err_stack varchar2(4000);
6414: begin
6415:
6416: doccontent := Wf_Notification.GetAttrDoc(nid, docattrname, disptype);
6417:
6418: exception
6419: when no_program_unit then
6420: wf_core.context('WF_MAIL', 'GetDocContent', docattrname);
6506: begin
6507: -- There is a difference betwen PLSQL: and PLSQLCLOB: documents
6508: -- First go for the PLSQL: if that returns the name of the
6509: -- attribute, then try the PLSQLCLOB:
6510: doc := WF_NOTIFICATION.GetAttrDoc(nid, docattrname, disptype);
6511: if disptype = g_ntfDocHtml then
6512: -- DBMS_LOB.CreateTemporary(g_html_message, true, dbms_lob.SESSION);
6513: -- DBMS_LOB.Open(g_html_message, DBMS_LOB.LOB_READWRITE);
6514: g_html_messageIdx := wf_temp_lob.getLob(g_LOBTable);
6512: -- DBMS_LOB.CreateTemporary(g_html_message, true, dbms_lob.SESSION);
6513: -- DBMS_LOB.Open(g_html_message, DBMS_LOB.LOB_READWRITE);
6514: g_html_messageIdx := wf_temp_lob.getLob(g_LOBTable);
6515: if doc = '&'||docattrname then
6516: Wf_Notification.GetAttrCLOB(nid, docattrname, disptype,
6517: g_LOBTable(g_html_messageIdx).temp_lob, doctype, aname);
6518: else
6519: DBMS_LOB.Write(g_LOBTable(g_html_messageIdx).temp_lob, length(doc), 1, doc);
6520: end if;
6522: -- DBMS_LOB.CreateTemporary(g_text_message, true, dbms_lob.SESSION);
6523: -- DBMS_LOB.Open(g_text_message, DBMS_LOB.LOB_READWRITE);
6524: g_text_messageIdx := wf_temp_lob.getLob(g_LOBTable);
6525: if doc = '&'||docattrname then
6526: Wf_Notification.GetAttrCLOB(nid, docattrname, disptype,
6527: g_LOBTable(g_text_messageIdx).temp_lob, doctype, aname);
6528: else
6529: DBMS_LOB.Write(g_LOBTable(g_text_messageIdx).temp_lob, length(doc), 1, doc);
6530: end if;
6690: -- Get notification message and status
6691: begin
6692: select MESSAGE_NAME, MESSAGE_TYPE, STATUS
6693: into msg_name, msg_type, stat
6694: from WF_NOTIFICATIONS
6695: where NOTIFICATION_ID = nid;
6696: exception
6697: when no_data_found then
6698: wf_core.token('NID', to_char(nid));
6833: -- Process this notification only if it has a status of 'OPEN'
6834: -- otherwise do nothing. Fix for bug 2202392.
6835: if (stat = 'OPEN') then
6836: -- Save the new attribute value for nid.
6837: Wf_Notification.SetAttrText(nid, resp_attrs(j).attr_name, value);
6838: end if;
6839: response := TRUE;
6840: end if;
6841: end if;
6846: wf_engine.preserved_context := FALSE;
6847:
6848: -- Complete the response.
6849: if response then
6850: Wf_Notification.Respond(nid, NULL, 'email:'||from_addr);
6851: else
6852: wf_core.context('WF_MAIL', 'PutMessage', to_char(nid), node, from_addr);
6853: wf_core.raise('WFRSPR_NORESPONSE');
6854: end if;
6904: '', to_char(WNA.DATE_VALUE),
6905: to_char(WNA.DATE_VALUE, WMA.FORMAT)),
6906: 'LOOKUP', WNA.TEXT_VALUE,
6907: WNA.TEXT_VALUE) VALUE
6908: from WF_NOTIFICATION_ATTRIBUTES WNA,
6909: WF_NOTIFICATIONS WN,
6910: WF_MESSAGE_ATTRIBUTES_VL WMA
6911: where WNA.NOTIFICATION_ID = nid
6912: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
6905: to_char(WNA.DATE_VALUE, WMA.FORMAT)),
6906: 'LOOKUP', WNA.TEXT_VALUE,
6907: WNA.TEXT_VALUE) VALUE
6908: from WF_NOTIFICATION_ATTRIBUTES WNA,
6909: WF_NOTIFICATIONS WN,
6910: WF_MESSAGE_ATTRIBUTES_VL WMA
6911: where WNA.NOTIFICATION_ID = nid
6912: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
6913: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
6934: -- Get notification message and status
6935: begin
6936: select MESSAGE_NAME, MESSAGE_TYPE, STATUS
6937: into msg_name, msg_type, stat
6938: from WF_NOTIFICATIONS
6939: where NOTIFICATION_ID = nid;
6940: exception
6941: when no_data_found then
6942: wf_core.token('NID', to_char(nid));
7007: -- otherwise do nothing. Fix for bug 2202392.
7008:
7009: if (stat = 'OPEN') then
7010: -- Save the new attribute value for nid.
7011: Wf_Notification.SetAttrText(nid, rec.name, new_value);
7012: end if;
7013: response := TRUE;
7014: end loop;
7015:
7017: wf_engine.preserved_context := FALSE;
7018:
7019: -- Complete the response.
7020: if response then
7021: Wf_Notification.Respond(nid, NULL, 'email:'||from_addr);
7022: else
7023: wf_core.context('WF_MAIL', 'PutDirectMessage', to_char(nid),
7024: node, from_addr);
7025: wf_core.raise('WFRSPR_NORESPONSE');
7103: comment := 'NULL';
7104: end if;
7105: -- validate the role before calling updateinfo
7106:
7107: -- update wf_notifications and wf_comments in QUESTION mode
7108: if (length(to_user) > 0 and length(comment) > 0) then
7109: wf_notification.UpdateInfo2(nid, to_user, from_addr, comment);
7110: end if;
7111: exception
7105: -- validate the role before calling updateinfo
7106:
7107: -- update wf_notifications and wf_comments in QUESTION mode
7108: if (length(to_user) > 0 and length(comment) > 0) then
7109: wf_notification.UpdateInfo2(nid, to_user, from_addr, comment);
7110: end if;
7111: exception
7112: when no_program_unit then
7113: wf_core.context('WF_MAIL','PutMoreInfoRequest', to_char(nid));
7121:
7122: -- PutMoreInfoMessage
7123: -- Reply processor. Read body of a reply for more information
7124: -- request, parse the body for the comments from the user and
7125: -- update wf_notification and wf_comments apropriately
7126: -- Used by the notification mail response processor.
7127: -- IN
7128: -- notification id
7129: -- mailer node name
7187: end if;
7188: end if;
7189: end if;
7190: end if;
7191: -- update wf_notifications and wf_comments in ANSWER mode
7192: to_user := NULL;
7193: wf_notification.UpdateInfo2(nid, to_user, from_addr, comment);
7194: exception
7195: when no_program_unit then
7189: end if;
7190: end if;
7191: -- update wf_notifications and wf_comments in ANSWER mode
7192: to_user := NULL;
7193: wf_notification.UpdateInfo2(nid, to_user, from_addr, comment);
7194: exception
7195: when no_program_unit then
7196: wf_core.context('WF_MAIL','PutMoreInfoMessage', to_char(nid));
7197: raise;
7223: err_stack varchar2(4000);
7224:
7225: cursor ntf is
7226: select WMA.TYPE, WMA.DISPLAY_NAME,
7227: decode(WMA.TYPE, 'URL', WF_NOTIFICATION.GetUrlText(WNA.TEXT_VALUE,
7228: GetURLAttachment.nid), WNA.TEXT_VALUE) URL,
7229: WNA.NAME
7230: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
7231: WF_MESSAGE_ATTRIBUTES_VL WMA
7226: select WMA.TYPE, WMA.DISPLAY_NAME,
7227: decode(WMA.TYPE, 'URL', WF_NOTIFICATION.GetUrlText(WNA.TEXT_VALUE,
7228: GetURLAttachment.nid), WNA.TEXT_VALUE) URL,
7229: WNA.NAME
7230: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
7231: WF_MESSAGE_ATTRIBUTES_VL WMA
7232: where WNA.NOTIFICATION_ID = GetURLAttachment.nid
7233: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
7234: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
7263: -- Build the list of URLs
7264: l_urlcount := 0;
7265: l_urllist := '
';
7266: for urlattr in ntf loop
7267: l_url := wf_notification.SetFrameworkAgent(urlattr.url);
7268: l_urllist := l_urllist || '' ||
7269: urlattr.display_name || '
'||g_newLine;
7270: l_urlcount := l_urlcount + 1;
7271: end loop;
7517: is
7518: begin
7519: -- Get value for signature policy for the notification from
7520: -- notification attribute
7521: sig_policy := Wf_Notification.GetAttrText(nid, '#WF_SIG_POLICY');
7522:
7523: exception
7524: when others then
7525: if (wf_core.error_name = 'WFNTF_ATTR') then
7544: p_sec_policy out NOCOPY varchar2)
7545: is
7546: begin
7547: -- Get security policy for the notification
7548: p_sec_policy := Wf_Notification.GetAttrText(p_nid, '#WF_SECURITY_POLICY');
7549: if (p_sec_policy is null) then
7550: p_sec_policy := 'DEFAULT';
7551: end if;
7552: exception
7677: end if;
7678:
7679: -- Get notification's language preference
7680: begin
7681: l_lang := upper(Wf_Notification.GetAttrText(p_nid, '#WFM_LANGUAGE'));
7682: exception
7683: when others then
7684: if (wf_core.error_name = 'WFNTF_ATTR') then
7685: wf_core.clear();
7734:
7735:
7736: -- Get notification's territory preference
7737: begin
7738: l_terr := upper(Wf_Notification.GetAttrText(p_nid, '#WFM_TERRITORY'));
7739: exception
7740: when others then
7741: if (wf_core.error_name = 'WFNTF_ATTR') then
7742: wf_core.clear();
7775: -- <
7776: -- WF_MAIL.GetSessionLanguage(l_base_lang, l_base_terr, l_base_codeset);
7777: -- Note: we should AVOID to call below API here
7778: -- use global variables instead .
7779: WF_NOTIFICATION_UTIL.getNLSContext(
7780: l_base_lang ,
7781: l_base_terr ,
7782: l_base_codeset ,
7783: l_base_nlsDateFormat ,
7840: --
7841:
7842: -- Send
7843: -- Sends a e-mail notification to the specified list of recipients.
7844: -- This API unlike wf_notification.send does not require workflow
7845: -- message or workflow roles to send a notification.
7846:
7847: procedure send(p_subject in varchar2,
7848: p_message in out nocopy clob,
8172: end if;
8173:
8174: l_event.SetEventData(l_msg_doc);
8175:
8176: l_agent := wf_agent_t('WF_NOTIFICATION_OUT', wf_event.local_system_name);
8177: l_event.SetFromAgent(l_agent);
8178:
8179: -- Generally it would be 'ALR'
8180: if(instr( send.p_module, ':') = 0) then
8353: -- << sstomar>: The content of this warning ntf emil
8354: -- are generated by Mailer, so other NLS parameters ( NLS_DATE_FORMAT etc.
8355: -- because we don;t have any such attributes in message body)
8356: -- are not required to SET/ RESET but if we want to use centralized API
8357: -- wf_notification_util.setNLSContext / getNLSContext then we need to modify.
8358: --
8359:
8360: -- getting the language for the session
8361: wf_notification.getnlslanguage(l_orig_lang, l_orig_terr, l_orig_chrs);
8357: -- wf_notification_util.setNLSContext / getNLSContext then we need to modify.
8358: --
8359:
8360: -- getting the language for the session
8361: wf_notification.getnlslanguage(l_orig_lang, l_orig_terr, l_orig_chrs);
8362:
8363: --
8364: If l_installed = 'Y' then
8365: -- Set user's language
8362:
8363: --
8364: If l_installed = 'Y' then
8365: -- Set user's language
8366: wf_notification.setnlslanguage(l_language, l_territory);
8367:
8368: end if;
8369:
8370: -- Get message details
8369:
8370: -- Get message details
8371: SELECT message_type,message_name
8372: INTO l_msg_type,l_msg_name
8373: FROM wf_notifications
8374: WHERE notification_id = p_nid;
8375:
8376: -- Get template 'WARNING'
8377: SELECT subject, BODY, HTML_BODY
8418:
8419: l_html_body := SUBSTR(REPLACE(l_html_body,'&QUESTION',l_question),1,32000);
8420:
8421: -- reset the session language
8422: WF_Notification.SetNLSLanguage(l_orig_lang, l_orig_terr);
8423:
8424: -- LOB to store message payload.
8425: dbms_lob.createtemporary(l_msg_doc, TRUE,dbms_lob.CALL);
8426:
8434: l_attrlist.DELETE;
8435:
8436:
8437: --
8438: -- WF_NOTIFICATION.MAIL_STAUTS should not affect to deliver
8439: -- this warning message.
8440: wf_xml.AddElementAttribute('nid','0',l_attrlist);
8441: wf_xml.AddElementAttribute('nidstr',l_idstr,l_attrlist);
8442: wf_xml.AddElementAttribute('language',l_language,l_attrlist);
8612: wf_event_t.initialize(l_event);
8613: l_event.event_name := wf_xml.wf_ntf_send_message;
8614:
8615: -- Create an event key : make it as 0 so that this warning message
8616: -- waon't rely on wf_notification TABLE.
8617: l_event.event_key := 0;
8618: l_event.seteventdata(l_msg_doc);
8619:
8620: l_agent := wf_agent_t('WF_NOTIFICATION_OUT',wf_event.local_system_name);
8616: -- waon't rely on wf_notification TABLE.
8617: l_event.event_key := 0;
8618: l_event.seteventdata(l_msg_doc);
8619:
8620: l_agent := wf_agent_t('WF_NOTIFICATION_OUT',wf_event.local_system_name);
8621: l_event.setfromagent(l_agent);
8622:
8623: l_event.addparametertolist('Q_CORRELATION_ID',l_msg_type || ':' ||l_msg_name);
8624: l_event.addparametertolist('NOTIFICATION_ID','0');
8636: exception
8637: when others then
8638: --Release allocated temp LOBs back to pool
8639: if (l_orig_lang is not null and l_orig_terr is not null) then
8640: WF_Notification.SetNLSLanguage(l_orig_lang,l_orig_terr);
8641: end IF;
8642:
8643: if(l_messageidx > 0) then
8644: wf_temp_lob.releaselob(g_lobtable, l_messageidx);
8648: end SendMoreInfoResponseWarning;
8649:
8650: -- SetNtfEventsSubStatus
8651: -- This procedure sets the status of seeded subscription to the event group
8652: -- oracle.apps.wf.notification.send.group. This subscription is responsible
8653: -- for notification XML message generation and presenting it to the mailer for
8654: -- e-mail dispatch. Disabling this subscription causes e-mails not to be sent.
8655: --
8656: -- ENABLED - E-mails are sent
8671: AND owner_tag = 'FND'
8672: AND event_filter_guid
8673: IN (SELECT guid
8674: FROM wf_events
8675: WHERE name = 'oracle.apps.wf.notification.send.group'
8676: AND type = 'GROUP')
8677: AND out_agent_guid
8678: IN (SELECT guid
8679: FROM wf_agents
8676: AND type = 'GROUP')
8677: AND out_agent_guid
8678: IN (SELECT guid
8679: FROM wf_agents
8680: WHERE name = 'WF_NOTIFICATION_OUT'
8681: AND system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')))
8682: AND customization_level = 'L';
8683:
8684: -- Inform BES cache manager that BES meta-data state has changed