[Home] [Help]
35:
36:
37: cursor g_urls(p_nid varchar2) is
38: select WMA.TYPE, WMA.DISPLAY_NAME, WNA.TEXT_VALUE, WNA.NAME
39: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
40: WF_MESSAGE_ATTRIBUTES_VL WMA
41: where WNA.NOTIFICATION_ID = p_nid
42: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
43: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
57: g_webAgent varchar2(200) := wf_core.translate('WF_WEB_AGENT');
58: g_wfSchema varchar2(200) := wf_core.translate('WF_SCHEMA');
59: g_fndapi_misschr varchar2(1) := FND_API.G_MISS_CHAR;
60:
61: g_ntfDocText varchar2(30) := wf_notification.doc_text;
62: g_ntfDocHtml varchar2(30) := wf_notification.doc_html;
63:
64: -- <
65: g_base_language v$nls_parameters.value%TYPE ;
58: g_wfSchema varchar2(200) := wf_core.translate('WF_SCHEMA');
59: g_fndapi_misschr varchar2(1) := FND_API.G_MISS_CHAR;
60:
61: g_ntfDocText varchar2(30) := wf_notification.doc_text;
62: g_ntfDocHtml varchar2(30) := wf_notification.doc_html;
63:
64: -- <
65: g_base_language v$nls_parameters.value%TYPE ;
66: g_base_territory v$nls_parameters.value%TYPE ;
68:
69: -- << sstomar>> : Initialization can be done by using
70: -- variables from wf_core.nls_date_format,
71: -- wf_core.nls_date_language etc. but since
72: -- when calling WF_NOTIFICATION_UTIL.set/getNLSContext to retrieve
73: -- defaul NLS Language etc. we can define vars. here also.
74: g_base_nlsDateFormat v$nls_parameters.value%TYPE := wf_core.nls_date_format;
75: g_base_nlsDateLanguage v$nls_parameters.value%TYPE := wf_core.nls_date_language;
76: g_base_nlsCalendar v$nls_parameters.value%TYPE := wf_core.nls_calendar;
78: g_base_nlsNumericCharacters v$nls_parameters.value%TYPE := wf_core.nls_numeric_characters;
79:
80: g_WebMail_PostScript_Msg varchar2(1024);
81:
82: -- Bug 10202313: global variables to store status, mail_status of WF_NOTIFICATIONS table
83: -- which will be passed as parameters to WF_MAIL.GetLOBMessage4() API
84: g_status wf_notifications.status%TYPE;
85: g_mstatus wf_notifications.mail_status%TYPE;
86:
80: g_WebMail_PostScript_Msg varchar2(1024);
81:
82: -- Bug 10202313: global variables to store status, mail_status of WF_NOTIFICATIONS table
83: -- which will be passed as parameters to WF_MAIL.GetLOBMessage4() API
84: g_status wf_notifications.status%TYPE;
85: g_mstatus wf_notifications.mail_status%TYPE;
86:
87:
88: -- Return TRUE if the URL points to a image file.
81:
82: -- Bug 10202313: global variables to store status, mail_status of WF_NOTIFICATIONS table
83: -- which will be passed as parameters to WF_MAIL.GetLOBMessage4() API
84: g_status wf_notifications.status%TYPE;
85: g_mstatus wf_notifications.mail_status%TYPE;
86:
87:
88: -- Return TRUE if the URL points to a image file.
89: -- The URL is pretested to ensure that it does NOT contain
289: buf := replace(buf, l_amp||'amp;', l_amp);
290: return buf;
291: exception
292: when others then
293: wf_core.context('Wf_Notification', 'DecodeEntityReference');
294: raise;
295: end DecodeEntityReference;
296:
297: -- EnqueueLOBMessage
911: l_err_stack varchar2(4000);
912:
913: cursor c_attr(p_nid varchar2) is
914: select WMA.TYPE, WMA.DISPLAY_NAME,
915: decode(WMA.TYPE, 'URL', WF_NOTIFICATION.GetUrlText(WNA.TEXT_VALUE,
916: p_nid), WNA.TEXT_VALUE) URL, WNA.NAME
917: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
918: WF_MESSAGE_ATTRIBUTES_VL WMA
919: where WNA.NOTIFICATION_ID = p_nid
913: cursor c_attr(p_nid varchar2) is
914: select WMA.TYPE, WMA.DISPLAY_NAME,
915: decode(WMA.TYPE, 'URL', WF_NOTIFICATION.GetUrlText(WNA.TEXT_VALUE,
916: p_nid), WNA.TEXT_VALUE) URL, WNA.NAME
917: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
918: WF_MESSAGE_ATTRIBUTES_VL WMA
919: where WNA.NOTIFICATION_ID = p_nid
920: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
921: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
959: if upper(substr(l_crec.url,1, 6)) = 'PLSQL:' then
960:
961: -- wf_mail.getDocContent(p_nid, l_crec.name, l_display_type,
962: -- l_tmpcontent, l_error_result);
963: -- bug 2879507. call wf_notification.GetAttrDoc2 to get the
964: -- doc content as well as the document type
965:
966: begin
967: wf_notification.GetAttrDoc2(p_nid, l_crec.name, l_display_type,
963: -- bug 2879507. call wf_notification.GetAttrDoc2 to get the
964: -- doc content as well as the document type
965:
966: begin
967: wf_notification.GetAttrDoc2(p_nid, l_crec.name, l_display_type,
968: l_tmpcontent, l_content_type);
969: exception
970: when others then
971: if (wf_log_pkg.level_error >=
994:
995: -- Bug 8801597
996: -- <
997: -- (Mailer) java layer will fail if it finds '&' only, so replace it with '&'
998: l_filename := WF_NOTIFICATION.SubstituteSpecialChars(l_filename);
999:
1000: -- l_filename := Wf_Core.SubstituteSpecialChars(l_filename);
1001:
1002: AddElementAttribute('content-type',l_mimeType, l_attrlist);
1066: -- Bug 8801597
1067: -- <
1068: -- (Mailer) java layer will fail if it finds '&' only, so replace it with '&'
1069: -- l_filename := Wf_Core.SubstituteSpecialChars(l_filename);
1070: l_filename := WF_NOTIFICATION.SubstituteSpecialChars(l_filename);
1071:
1072: if lower(l_mimeType) not like 'text/%' and
1073: (l_encoding is null or lower(l_encoding) <> 'base64') then
1074: -- Assume that there has been no encoding on a RAW
1082: -- First the BODYPART structure to take the MESSAGE
1083: AddElementAttribute('content-type',l_mimeType, l_attrlist);
1084: l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
1085: l_attrlist.DELETE;
1086: l_attr_url := Wf_Notification.GetText(l_crec.URL, p_nid, l_display_type);
1087: AddElementAttribute('src', l_attr_url, l_attrlist);
1088: AddElementAttribute('content-type',l_mimeType, l_attrlist);
1089: AddElementAttribute('content-disposition',p_disposition,
1090: l_attrlist);
1152: -- content and then we fetch the content into
1153: -- the new structure.
1154: --
1155: begin
1156: Wf_Notification.GetAttrBLOB(p_nid, l_crec.name,
1157: l_display_type,
1158: l_blob, l_content_type,
1159: l_aname);
1160: exception
1185:
1186: -- Bug 8801597 ( File name may have '&' character etc. )
1187: -- SAX parser at java layer will fail if it finds '&' only, so replace it with '&'
1188: -- SubstituteSpecialChars API takes care if BLOB API already repalced '&' with '&'
1189: l_filename := WF_NOTIFICATION.SubstituteSpecialChars(l_filename);
1190:
1191: -- First the BODYPART structure to take the MESSAGE
1192: AddElementAttribute('content-type',l_mimeType, l_attrlist);
1193: l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
1192: AddElementAttribute('content-type',l_mimeType, l_attrlist);
1193: l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
1194: l_attrlist.DELETE;
1195:
1196: l_attr_url := Wf_Notification.GetText(l_crec.URL, p_nid, l_display_type);
1197:
1198: AddElementAttribute('src', l_attr_url, l_attrlist);
1199: AddElementAttribute('content-type',l_mimeType, l_attrlist);
1200: AddElementAttribute('content-disposition',p_disposition,
1213:
1214: -- BUG 3285943 - If this is for a framework notification
1215: -- then we don't need the attached URLs.
1216: -- Bug 5456241 : Pick html / text msg body based on content-type.
1217: if (WF_NOTIFICATION.isFwkRegion(p_nid, l_display_type)='Y' and g_install='EMBEDDED') then
1218: l_isURLAttrs := FALSE;
1219: end if;
1220:
1221: if l_isURLAttrs then
1316: '', to_char(WNA.DATE_VALUE),
1317: to_char(WNA.DATE_VALUE, WMA.FORMAT)),
1318: 'LOOKUP', WNA.TEXT_VALUE,
1319: WNA.TEXT_VALUE) VALUE
1320: from WF_NOTIFICATION_ATTRIBUTES WNA,
1321: WF_NOTIFICATIONS WN,
1322: WF_MESSAGE_ATTRIBUTES_VL WMA
1323: where WNA.NOTIFICATION_ID = p_nid
1324: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1317: to_char(WNA.DATE_VALUE, WMA.FORMAT)),
1318: 'LOOKUP', WNA.TEXT_VALUE,
1319: WNA.TEXT_VALUE) VALUE
1320: from WF_NOTIFICATION_ATTRIBUTES WNA,
1321: WF_NOTIFICATIONS WN,
1322: WF_MESSAGE_ATTRIBUTES_VL WMA
1323: where WNA.NOTIFICATION_ID = p_nid
1324: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1325: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
1389: l_pos integer;
1390: l_occurance integer := 1;
1391: l_item_type wf_items.item_type%TYPE;
1392: l_item_key wf_items.item_key%TYPE;
1393: l_priority wf_notifications.priority%TYPE;
1394: l_access_key wf_notifications.access_key%TYPE;
1395:
1396: l_response integer;
1397:
1390: l_occurance integer := 1;
1391: l_item_type wf_items.item_type%TYPE;
1392: l_item_key wf_items.item_key%TYPE;
1393: l_priority wf_notifications.priority%TYPE;
1394: l_access_key wf_notifications.access_key%TYPE;
1395:
1396: l_response integer;
1397:
1398: l_attrlist wf_xml_attr_table_type;
1476:
1477: begin
1478: select priority, access_key, status
1479: into l_priority, l_access_key, l_status
1480: from wf_notifications_view
1481: where notification_id = p_nid;
1482: exception
1483: when NO_DATA_FOUND then
1484: wf_core.raise('WFNTFGM_FAILED');
1553: select 1 into l_response
1554: from dual
1555: where exists (select NULL
1556: from WF_MESSAGE_ATTRIBUTES MA,
1557: WF_NOTIFICATIONS N
1558: where N.NOTIFICATION_ID = p_nid
1559: and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
1560: and MA.MESSAGE_NAME = N.MESSAGE_NAME
1561: and MA.SUBTYPE = 'RESPOND');
1608: -- l_doc CLOB;
1609: l_docIdx pls_integer;
1610: l_pos integer;
1611: l_occurance integer := 1;
1612: l_priority wf_notifications.priority%TYPE;
1613: l_status wf_notifications.status%TYPE;
1614: l_recipient wf_notifications.recipient_role%TYPE;
1615:
1616: l_attrlist wf_xml_attr_table_type;
1609: l_docIdx pls_integer;
1610: l_pos integer;
1611: l_occurance integer := 1;
1612: l_priority wf_notifications.priority%TYPE;
1613: l_status wf_notifications.status%TYPE;
1614: l_recipient wf_notifications.recipient_role%TYPE;
1615:
1616: l_attrlist wf_xml_attr_table_type;
1617: l_str varchar2 (250);
1610: l_pos integer;
1611: l_occurance integer := 1;
1612: l_priority wf_notifications.priority%TYPE;
1613: l_status wf_notifications.status%TYPE;
1614: l_recipient wf_notifications.recipient_role%TYPE;
1615:
1616: l_attrlist wf_xml_attr_table_type;
1617: l_str varchar2 (250);
1618:
1655:
1656: begin
1657: select recipient_role, priority, status, more_info_role
1658: into l_recipient, l_priority, l_status, l_more_info_role
1659: from wf_notifications
1660: where notification_id = p_nid;
1661: exception
1662: when NO_DATA_FOUND then
1663: wf_core.raise('WFNTFGM_FAILED');
1716: p_message in out NOCOPY VARCHAR2)
1717: is
1718: l_pos integer;
1719: l_occurance integer := 1;
1720: l_priority wf_notifications.priority%TYPE;
1721: l_status wf_notifications.status%TYPE;
1722: l_recipient wf_notifications.recipient_role%TYPE;
1723:
1724: l_attrlist wf_xml_attr_table_type;
1717: is
1718: l_pos integer;
1719: l_occurance integer := 1;
1720: l_priority wf_notifications.priority%TYPE;
1721: l_status wf_notifications.status%TYPE;
1722: l_recipient wf_notifications.recipient_role%TYPE;
1723:
1724: l_attrlist wf_xml_attr_table_type;
1725: l_str varchar2 (250);
1718: l_pos integer;
1719: l_occurance integer := 1;
1720: l_priority wf_notifications.priority%TYPE;
1721: l_status wf_notifications.status%TYPE;
1722: l_recipient wf_notifications.recipient_role%TYPE;
1723:
1724: l_attrlist wf_xml_attr_table_type;
1725: l_str varchar2 (250);
1726:
1758:
1759: begin
1760: select recipient_role, priority, status, more_info_role
1761: into l_recipient, l_priority, l_status, l_more_info_role
1762: from wf_notifications
1763: where notification_id = p_nid;
1764: exception
1765: when NO_DATA_FOUND then
1766: wf_core.raise('WFNTFGM_FAILED');
1827: -- indicates that a notification should be sent.
1828: begin
1829: select recipient_role
1830: into l_recipient_role
1831: from wf_notifications
1832: where notification_id = p_nid
1833: and ((MAIL_STATUS = 'MAIL' and STATUS <> 'CLOSED')
1834: or (MAIL_STATUS = 'INVALID'));
1835: exception
1945: -- indicates that a notification should be sent.
1946: begin
1947: select recipient_role
1948: into l_recipient_role
1949: from wf_notifications
1950: where notification_id = p_nid
1951: and MAIL_STATUS in ('MAIL', 'INVALID');
1952: exception
1953: when NO_DATA_FOUND then
2009: -- indicates that a notification should be sent.
2010: begin
2011: select recipient_role, more_info_role
2012: into l_recipient_role, l_more_info_role
2013: from wf_notifications
2014: where notification_id = p_nid
2015: and MAIL_STATUS in ('MAIL', 'INVALID')
2016: and STATUS <> 'CLOSED';
2017: exception
2317: l_nid number;
2318: l_queue_name varchar2(255);
2319: l_status varchar2(8) := NULL;
2320: l_currstatus varchar2(8);
2321: l_recipient WF_NOTIFICATIONS.RECIPIENT_ROLE%TYPE := NULL;
2322: l_attrlist wf_xml_attr_table_type;
2323: l_timeout BOOLEAN;
2324: l_pos integer;
2325: l_statusOK boolean;
2362: -- Verify that the status of the notification is
2363: -- still OK.
2364: begin
2365: select status into l_currstatus
2366: from wf_notifications
2367: where notification_id = l_nid
2368: and status in ('OPEN','CANCELED', 'CLOSED')
2369: and mail_status in ('MAIL','INVALID','FAILED');
2370: l_statusOK := TRUE;
2677:
2678: tmpIdx := wf_temp_lob.getLob(g_LOBTable);
2679: for url in g_urls(nid) loop
2680: value := url.text_value;
2681: value := wf_notification.SetFrameworkAgent(value);
2682:
2683: -- Check if there is a render type specification on the URL
2684: -- Remove it and set the renderType appropriately.
2685: renderType := substr(value, 1, 4);
3206: select ACCESS_KEY, PRIORITY, STATUS, MESSAGE_TYPE,
3207: MESSAGE_NAME, MORE_INFO_ROLE
3208: into access_key, priority, status, messageType,
3209: messageName, moreInfoRole
3210: from WF_NOTIFICATIONS
3211: where NOTIFICATION_ID = nid;
3212: exception
3213: when no_data_found then
3214: GenerateEmptyDoc(nid, p_pos, p_doc, 'no_data_found', FALSE);
3298: -- interface.
3299: -- The precedence will be only for normal notifications. Requests
3300: -- for more information will not be subject to the cc and bcc
3301: -- handling.
3302: -- moreInfoRole is null : either FIRST_SEND or wf.notification.answer event.
3303: if (moreInfoRole is null) then
3304: begin
3305: cc_list := upper(Wf_Notification.GetAttrText(nid, '#WFM_CC'));
3306: exception
3301: -- handling.
3302: -- moreInfoRole is null : either FIRST_SEND or wf.notification.answer event.
3303: if (moreInfoRole is null) then
3304: begin
3305: cc_list := upper(Wf_Notification.GetAttrText(nid, '#WFM_CC'));
3306: exception
3307: when others then
3308: if (wf_core.error_name = 'WFNTF_ATTR') then
3309: wf_core.clear();
3313: end if;
3314: end;
3315:
3316: begin
3317: bcc_list := upper(Wf_Notification.GetAttrText(nid, '#WFM_BCC'));
3318: exception
3319: when others then
3320: if (wf_core.error_name = 'WFNTF_ATTR') then
3321: wf_core.clear();
3376:
3377: ) then
3378:
3379: -- override pref. EXCEPT FYI notification,
3380: if ( WF_Notification.isFYI(nid) = false ) then
3381: -- Overrided ntf pref.
3382: notification_pref := 'MAILATTH';
3383: -- Use this variable to set PostScript message after NLS Context is set.
3384: l_ntfPref_Overridden := true;
3396: doctype := g_ntfDocHtml;
3397: end if;
3398:
3399: -- bug 5456241 : Passing doctype parameter
3400: if (WF_NOTIFICATION.isFwkRegion(nid, doctype)='Y' and
3401: g_install ='EMBEDDED') then
3402:
3403: frameworkContent := TRUE;
3404: -- 3803327 Text email with framework is currently disabled
3434:
3435: -- set user's language and territory : charset we can not chage for a session at DB layer.
3436: -- setNLS(l_nlsLanguage, l_nlsTerritory);
3437:
3438: -- Bug 11684796: Comparing user NLS parameters with the NLS parameters in WF_NOTIFICATION_UTIL
3439: -- package as WF_NOTIFICATION_UTIL.SetNLSContext() API sets these parameter values only
3440: if( ( WF_NOTIFICATION_UTIL.g_nls_language is null or
3441: nvl(l_nlsLanguage, 'AMERICAN') <> WF_NOTIFICATION_UTIL.g_nls_language) or
3442: ( WF_NOTIFICATION_UTIL.g_nls_territory is null or
3435: -- set user's language and territory : charset we can not chage for a session at DB layer.
3436: -- setNLS(l_nlsLanguage, l_nlsTerritory);
3437:
3438: -- Bug 11684796: Comparing user NLS parameters with the NLS parameters in WF_NOTIFICATION_UTIL
3439: -- package as WF_NOTIFICATION_UTIL.SetNLSContext() API sets these parameter values only
3440: if( ( WF_NOTIFICATION_UTIL.g_nls_language is null or
3441: nvl(l_nlsLanguage, 'AMERICAN') <> WF_NOTIFICATION_UTIL.g_nls_language) or
3442: ( WF_NOTIFICATION_UTIL.g_nls_territory is null or
3443: nvl( l_nlsTerritory, 'AMERICA') <> WF_NOTIFICATION_UTIL.g_nls_territory) or
3436: -- setNLS(l_nlsLanguage, l_nlsTerritory);
3437:
3438: -- Bug 11684796: Comparing user NLS parameters with the NLS parameters in WF_NOTIFICATION_UTIL
3439: -- package as WF_NOTIFICATION_UTIL.SetNLSContext() API sets these parameter values only
3440: if( ( WF_NOTIFICATION_UTIL.g_nls_language is null or
3441: nvl(l_nlsLanguage, 'AMERICAN') <> WF_NOTIFICATION_UTIL.g_nls_language) or
3442: ( WF_NOTIFICATION_UTIL.g_nls_territory is null or
3443: nvl( l_nlsTerritory, 'AMERICA') <> WF_NOTIFICATION_UTIL.g_nls_territory) or
3444: ( WF_NOTIFICATION_UTIL.g_nls_date_format is null or
3437:
3438: -- Bug 11684796: Comparing user NLS parameters with the NLS parameters in WF_NOTIFICATION_UTIL
3439: -- package as WF_NOTIFICATION_UTIL.SetNLSContext() API sets these parameter values only
3440: if( ( WF_NOTIFICATION_UTIL.g_nls_language is null or
3441: nvl(l_nlsLanguage, 'AMERICAN') <> WF_NOTIFICATION_UTIL.g_nls_language) or
3442: ( WF_NOTIFICATION_UTIL.g_nls_territory is null or
3443: nvl( l_nlsTerritory, 'AMERICA') <> WF_NOTIFICATION_UTIL.g_nls_territory) or
3444: ( WF_NOTIFICATION_UTIL.g_nls_date_format is null or
3445: nvl(l_nlsDateFormat , wf_core.nls_date_format) <> WF_NOTIFICATION_UTIL.g_nls_date_format) or
3438: -- Bug 11684796: Comparing user NLS parameters with the NLS parameters in WF_NOTIFICATION_UTIL
3439: -- package as WF_NOTIFICATION_UTIL.SetNLSContext() API sets these parameter values only
3440: if( ( WF_NOTIFICATION_UTIL.g_nls_language is null or
3441: nvl(l_nlsLanguage, 'AMERICAN') <> WF_NOTIFICATION_UTIL.g_nls_language) or
3442: ( WF_NOTIFICATION_UTIL.g_nls_territory is null or
3443: nvl( l_nlsTerritory, 'AMERICA') <> WF_NOTIFICATION_UTIL.g_nls_territory) or
3444: ( WF_NOTIFICATION_UTIL.g_nls_date_format is null or
3445: nvl(l_nlsDateFormat , wf_core.nls_date_format) <> WF_NOTIFICATION_UTIL.g_nls_date_format) or
3446: ( WF_NOTIFICATION_UTIL.g_nls_Date_Language is null or
3439: -- package as WF_NOTIFICATION_UTIL.SetNLSContext() API sets these parameter values only
3440: if( ( WF_NOTIFICATION_UTIL.g_nls_language is null or
3441: nvl(l_nlsLanguage, 'AMERICAN') <> WF_NOTIFICATION_UTIL.g_nls_language) or
3442: ( WF_NOTIFICATION_UTIL.g_nls_territory is null or
3443: nvl( l_nlsTerritory, 'AMERICA') <> WF_NOTIFICATION_UTIL.g_nls_territory) or
3444: ( WF_NOTIFICATION_UTIL.g_nls_date_format is null or
3445: nvl(l_nlsDateFormat , wf_core.nls_date_format) <> WF_NOTIFICATION_UTIL.g_nls_date_format) or
3446: ( WF_NOTIFICATION_UTIL.g_nls_Date_Language is null or
3447: nvl(l_nlsDateLanguage, wf_core.nls_date_language) <> WF_NOTIFICATION_UTIL.g_nls_Date_Language) or
3440: if( ( WF_NOTIFICATION_UTIL.g_nls_language is null or
3441: nvl(l_nlsLanguage, 'AMERICAN') <> WF_NOTIFICATION_UTIL.g_nls_language) or
3442: ( WF_NOTIFICATION_UTIL.g_nls_territory is null or
3443: nvl( l_nlsTerritory, 'AMERICA') <> WF_NOTIFICATION_UTIL.g_nls_territory) or
3444: ( WF_NOTIFICATION_UTIL.g_nls_date_format is null or
3445: nvl(l_nlsDateFormat , wf_core.nls_date_format) <> WF_NOTIFICATION_UTIL.g_nls_date_format) or
3446: ( WF_NOTIFICATION_UTIL.g_nls_Date_Language is null or
3447: nvl(l_nlsDateLanguage, wf_core.nls_date_language) <> WF_NOTIFICATION_UTIL.g_nls_Date_Language) or
3448: ( WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters is null or
3441: nvl(l_nlsLanguage, 'AMERICAN') <> WF_NOTIFICATION_UTIL.g_nls_language) or
3442: ( WF_NOTIFICATION_UTIL.g_nls_territory is null or
3443: nvl( l_nlsTerritory, 'AMERICA') <> WF_NOTIFICATION_UTIL.g_nls_territory) or
3444: ( WF_NOTIFICATION_UTIL.g_nls_date_format is null or
3445: nvl(l_nlsDateFormat , wf_core.nls_date_format) <> WF_NOTIFICATION_UTIL.g_nls_date_format) or
3446: ( WF_NOTIFICATION_UTIL.g_nls_Date_Language is null or
3447: nvl(l_nlsDateLanguage, wf_core.nls_date_language) <> WF_NOTIFICATION_UTIL.g_nls_Date_Language) or
3448: ( WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters is null or
3449: nvl(l_nlsNumericCharacters, wf_core.nls_numeric_characters) <> WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters) or
3442: ( WF_NOTIFICATION_UTIL.g_nls_territory is null or
3443: nvl( l_nlsTerritory, 'AMERICA') <> WF_NOTIFICATION_UTIL.g_nls_territory) or
3444: ( WF_NOTIFICATION_UTIL.g_nls_date_format is null or
3445: nvl(l_nlsDateFormat , wf_core.nls_date_format) <> WF_NOTIFICATION_UTIL.g_nls_date_format) or
3446: ( WF_NOTIFICATION_UTIL.g_nls_Date_Language is null or
3447: nvl(l_nlsDateLanguage, wf_core.nls_date_language) <> WF_NOTIFICATION_UTIL.g_nls_Date_Language) or
3448: ( WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters is null or
3449: nvl(l_nlsNumericCharacters, wf_core.nls_numeric_characters) <> WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters) or
3450: ( WF_NOTIFICATION_UTIL.g_nls_Sort is null or
3443: nvl( l_nlsTerritory, 'AMERICA') <> WF_NOTIFICATION_UTIL.g_nls_territory) or
3444: ( WF_NOTIFICATION_UTIL.g_nls_date_format is null or
3445: nvl(l_nlsDateFormat , wf_core.nls_date_format) <> WF_NOTIFICATION_UTIL.g_nls_date_format) or
3446: ( WF_NOTIFICATION_UTIL.g_nls_Date_Language is null or
3447: nvl(l_nlsDateLanguage, wf_core.nls_date_language) <> WF_NOTIFICATION_UTIL.g_nls_Date_Language) or
3448: ( WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters is null or
3449: nvl(l_nlsNumericCharacters, wf_core.nls_numeric_characters) <> WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters) or
3450: ( WF_NOTIFICATION_UTIL.g_nls_Sort is null or
3451: nvl(l_nlsSort, wf_core.nls_sort) <> WF_NOTIFICATION_UTIL.g_nls_Sort) ) then
3444: ( WF_NOTIFICATION_UTIL.g_nls_date_format is null or
3445: nvl(l_nlsDateFormat , wf_core.nls_date_format) <> WF_NOTIFICATION_UTIL.g_nls_date_format) or
3446: ( WF_NOTIFICATION_UTIL.g_nls_Date_Language is null or
3447: nvl(l_nlsDateLanguage, wf_core.nls_date_language) <> WF_NOTIFICATION_UTIL.g_nls_Date_Language) or
3448: ( WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters is null or
3449: nvl(l_nlsNumericCharacters, wf_core.nls_numeric_characters) <> WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters) or
3450: ( WF_NOTIFICATION_UTIL.g_nls_Sort is null or
3451: nvl(l_nlsSort, wf_core.nls_sort) <> WF_NOTIFICATION_UTIL.g_nls_Sort) ) then
3452:
3445: nvl(l_nlsDateFormat , wf_core.nls_date_format) <> WF_NOTIFICATION_UTIL.g_nls_date_format) or
3446: ( WF_NOTIFICATION_UTIL.g_nls_Date_Language is null or
3447: nvl(l_nlsDateLanguage, wf_core.nls_date_language) <> WF_NOTIFICATION_UTIL.g_nls_Date_Language) or
3448: ( WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters is null or
3449: nvl(l_nlsNumericCharacters, wf_core.nls_numeric_characters) <> WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters) or
3450: ( WF_NOTIFICATION_UTIL.g_nls_Sort is null or
3451: nvl(l_nlsSort, wf_core.nls_sort) <> WF_NOTIFICATION_UTIL.g_nls_Sort) ) then
3452:
3453: -- nid, l_nlsCalendar, just set here only.
3446: ( WF_NOTIFICATION_UTIL.g_nls_Date_Language is null or
3447: nvl(l_nlsDateLanguage, wf_core.nls_date_language) <> WF_NOTIFICATION_UTIL.g_nls_Date_Language) or
3448: ( WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters is null or
3449: nvl(l_nlsNumericCharacters, wf_core.nls_numeric_characters) <> WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters) or
3450: ( WF_NOTIFICATION_UTIL.g_nls_Sort is null or
3451: nvl(l_nlsSort, wf_core.nls_sort) <> WF_NOTIFICATION_UTIL.g_nls_Sort) ) then
3452:
3453: -- nid, l_nlsCalendar, just set here only.
3454: -- at other places pass value as null.
3447: nvl(l_nlsDateLanguage, wf_core.nls_date_language) <> WF_NOTIFICATION_UTIL.g_nls_Date_Language) or
3448: ( WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters is null or
3449: nvl(l_nlsNumericCharacters, wf_core.nls_numeric_characters) <> WF_NOTIFICATION_UTIL.g_nls_Numeric_Characters) or
3450: ( WF_NOTIFICATION_UTIL.g_nls_Sort is null or
3451: nvl(l_nlsSort, wf_core.nls_sort) <> WF_NOTIFICATION_UTIL.g_nls_Sort) ) then
3452:
3453: -- nid, l_nlsCalendar, just set here only.
3454: -- at other places pass value as null.
3455: WF_NOTIFICATION_UTIL.SetNLSContext(nid,
3451: nvl(l_nlsSort, wf_core.nls_sort) <> WF_NOTIFICATION_UTIL.g_nls_Sort) ) then
3452:
3453: -- nid, l_nlsCalendar, just set here only.
3454: -- at other places pass value as null.
3455: WF_NOTIFICATION_UTIL.SetNLSContext(nid,
3456: l_nlsLanguage ,
3457: l_nlsTerritory ,
3458: l_nlsDateFormat ,
3459: l_nlsDateLanguage ,
3464: l_context_changed := true;
3465: else
3466:
3467: -- No need to SET context but set NID, Calendar as these two parameters
3468: -- are being used in WF_NOTIFICATION_UTIL.GetCalendarDate
3469: WF_NOTIFICATION_UTIL.setCurrentNID(nid);
3470: WF_NOTIFICATION_UTIL.setCurrentCalendar(l_nlsCalendar);
3471:
3472: END if;
3465: else
3466:
3467: -- No need to SET context but set NID, Calendar as these two parameters
3468: -- are being used in WF_NOTIFICATION_UTIL.GetCalendarDate
3469: WF_NOTIFICATION_UTIL.setCurrentNID(nid);
3470: WF_NOTIFICATION_UTIL.setCurrentCalendar(l_nlsCalendar);
3471:
3472: END if;
3473:
3466:
3467: -- No need to SET context but set NID, Calendar as these two parameters
3468: -- are being used in WF_NOTIFICATION_UTIL.GetCalendarDate
3469: WF_NOTIFICATION_UTIL.setCurrentNID(nid);
3470: WF_NOTIFICATION_UTIL.setCurrentCalendar(l_nlsCalendar);
3471:
3472: END if;
3473:
3474: -- bug 8515763
3544: -- TODO : need to check above case.
3545: if(l_context_changed ) then
3546:
3547: -- set nid, calendar once only.
3548: -- <
3550: WF_NOTIFICATION_UTIL.SetNLSContext(
3551: nid,
3552: g_base_language ,
3546:
3547: -- set nid, calendar once only.
3548: -- <
3549: -- during life-life cycle of this API.
3550: WF_NOTIFICATION_UTIL.SetNLSContext(
3551: nid,
3552: g_base_language ,
3553: g_base_territory ,
3554: g_base_nlsDateFormat ,
3581: -- <
3582: AddElementAttribute('nlsDateformat', l_nlsDateFormat, attrlist);
3583:
3584: -- << REMOVE validation later> we can validate if date_language is installed or not.
3585: if(WF_NOTIFICATION_UTIL.isLanguageInstalled(l_nlsDateLanguage)) then
3586: AddElementAttribute('nlsDateLanguage', l_nlsDateLanguage, attrlist);
3587: else
3588: AddElementAttribute('nlsDateLanguage', l_nlsLanguage, attrlist);
3589: end if;
3721: -- Build the resource section of the XML so that the
3722: -- java layer can locate the notification body to
3723: -- merge into the template.
3724:
3725: ntfURL := wf_mail.urlEncode(wf_notification.getFwkBodyURL2(nid,
3726: g_ntfDocText,
3727: l_nlsLanguage,
3728: l_nlsCalendar));
3729:
3899: -- Build the resource section of the XML so that the
3900: -- java layer can locate the notification body to
3901: -- merge into the template.
3902:
3903: ntfURL := wf_mail.urlEncode(wf_notification.getFwkBodyURL2(nid,
3904: g_ntfDocHtml,
3905: l_nlsLanguage,
3906: l_nlsCalendar));
3907:
3969: -- SetNLS(l_nlsLanguage, l_nlsTerritory);
3970: if(l_context_changed ) then
3971:
3972: -- l_nlsCalendar only will be stored in global variable
3973: WF_NOTIFICATION_UTIL.SetNLSContext(
3974: nid,
3975: l_nlsLanguage ,
3976: l_nlsTerritory ,
3977: l_nlsDateFormat ,
4020: -- SetNLS(g_base_language, g_base_territory);
4021: -- Reset base language
4022: if( l_context_changed ) then
4023:
4024: WF_NOTIFICATION_UTIL.SetNLSContext
4025: (null, -- Resetting null for Nid is fine.
4026: g_base_language ,
4027: g_base_territory ,
4028: g_base_nlsDateFormat ,
4063:
4064: -- Reset base language, in case of exception.
4065: -- SetNLS(g_base_language, g_base_territory);
4066: -- Reset base language
4067: WF_NOTIFICATION_UTIL.SetNLSContext(
4068: null,
4069: g_base_language ,
4070: g_base_territory ,
4071: g_base_nlsDateFormat ,
4226: begin -- 3741599 If there is no notification, exit gracefully
4227: select NVL(MORE_INFO_ROLE, RECIPIENT_ROLE), STATUS, MAIL_STATUS,
4228: MESSAGE_TYPE, MESSAGE_NAME
4229: into recipient_role, status, mail_status, messageType, messageName
4230: from WF_NOTIFICATIONS
4231: where NOTIFICATION_ID = nid;
4232: exception
4233: when no_data_found then
4234: GenerateEmptyDoc(nid, 0, p_doc, 'no_data_found', TRUE);
4505: nvl(l_nlsNumericCharacters, wf_core.nls_numeric_characters) <> g_base_nlsNumericCharacters or
4506: nvl(l_nlsSort, wf_core.nls_sort) <> g_base_nlsSort ) then
4507:
4508:
4509: WF_NOTIFICATION_UTIL.SetNLSContext(0,
4510: l_nlsLanguage ,
4511: l_nlsTerritory ,
4512: l_nlsDateFormat ,
4513: l_nlsDateLanguage ,
4545:
4546: -- Reset base NLS Context
4547: if( l_context_changed ) then
4548:
4549: WF_NOTIFICATION_UTIL.SetNLSContext(
4550: null,
4551: g_base_language ,
4552: g_base_territory ,
4553: g_base_nlsDateFormat ,
4689: if l_renderBody = 'N' then
4690: -- If this is a HTML summary notification then
4691: -- create a RESOURCE tag to obtain the content from
4692: -- the Applications Framework
4693: resourceSrc := wf_mail.urlEncode(wf_notification.getSummaryUrl2(
4694: recipient_role, docType, l_nlsCalendar));
4695: AddElementAttribute('page-type','fwk', attrlist);
4696: AddElementAttribute('src', resourceSrc, attrlist);
4697:
4724: wf_temp_lob.releaseLob(g_LOBTable, messageIdx);
4725: end if;
4726:
4727: -- Reset base NLS Context in case of any EXCEPTION
4728: WF_NOTIFICATION_UTIL.SetNLSContext(
4729: null,
4730: g_base_language ,
4731: g_base_territory ,
4732: g_base_nlsDateFormat ,
4951: g_base_nlsNumericCharacters is NULL OR
4952: g_base_nlsSort is NULL
4953: ) then
4954:
4955: WF_NOTIFICATION_UTIL.getNLSContext(
4956: g_base_language ,
4957: g_base_territory ,
4958: g_base_codeset ,
4959: g_base_nlsDateFormat ,
4987: when others then
4988: wf_core.context('WF_XML','Generate',p_event_name, p_event_key);
4989:
4990: -- RESET BASSE LANGUAGE
4991: WF_NOTIFICATION_UTIL.SetNLSContext(null,
4992: g_base_language ,
4993: g_base_territory ,
4994: g_base_nlsDateFormat ,
4995: g_base_nlsDateLanguage ,
5018: begin
5019: begin
5020: select 1
5021: into l_open
5022: from wf_notifications
5023: where notification_id = nid
5024: and status = 'OPEN';
5025: exception
5026: when others then l_open := 0;
5331: -- sendNotification
5332: -- This API is a wrapper to the wf_xml.enqueueNotification. It is provided
5333: -- as forward compatabilty for the original mailer since the call to
5334: -- wf_xml.enqueueNotification has been removed from
5335: -- wf_notification.sendSingle.
5336: -- To use the original mailer, one must enable the subscription that will
5337: -- call this rule function.
5338: -- IN
5339: -- p_subscription
5420: wf_log_pkg.string(WF_LOG_PKG.LEVEL_EXCEPTION,
5421: 'WF_XML.handleReceiveEvent',
5422: 'Notification is not OPEN. Submitting response '||
5423: 'to provide user feedback');
5424: Wf_Notification.Respond(l_nid, NULL, 'email:'||l_fromAddr);
5425: exception
5426: when others then
5427: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5428: wf_log_pkg.string(WF_LOG_PKG.level_statement,
5426: when others then
5427: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5428: wf_log_pkg.string(WF_LOG_PKG.level_statement,
5429: 'wf.plsql.WF_XML.handleReceiveEvent',
5430: 'Exception on call to Wf_Notification.Respond NID['||l_nid||'] '||
5431: 'Error Msg '||sqlerrm);
5432: end if;
5433:
5434: wf_core.Context('WF_XML','handleReceiveEvent',
5514: -- Just a note :
5515: -- fnd_date.canonical_to_date stores value in
5516: -- varchar2(30); so it may fail for values larger than (in bytes) that.
5517: --
5518: wf_notification.SetAttrDate(l_nid, l_responses(i).name,
5519: fnd_date.canonical_to_date(lk_code));
5520: elsif (l_responses(i).type = 'NUMBER' ) then
5521: wf_notification.SetAttrNumber(l_nid, l_responses(i).name,
5522: fnd_number.canonical_to_number(lk_code));
5517: --
5518: wf_notification.SetAttrDate(l_nid, l_responses(i).name,
5519: fnd_date.canonical_to_date(lk_code));
5520: elsif (l_responses(i).type = 'NUMBER' ) then
5521: wf_notification.SetAttrNumber(l_nid, l_responses(i).name,
5522: fnd_number.canonical_to_number(lk_code));
5523: else
5524: wf_notification.setAttrText(l_nid, l_responses(i).name,
5525: lk_code);
5520: elsif (l_responses(i).type = 'NUMBER' ) then
5521: wf_notification.SetAttrNumber(l_nid, l_responses(i).name,
5522: fnd_number.canonical_to_number(lk_code));
5523: else
5524: wf_notification.setAttrText(l_nid, l_responses(i).name,
5525: lk_code);
5526: end if;
5527: end if;
5528:
5574: wf_core.raise('WFNTF_NO_ROLE');
5575: elsif(l_comment is null or length(trim(l_comment)) = 0) then
5576: wf_core.raise('WFNTF_NO_QUESTION');
5577: else
5578: wf_notification.UpdateInfo2(l_nid, l_user, l_fromAddr,
5579: l_comment);
5580: end if;
5581: elsif l_version = 4 then
5582: -- Answer mode
5583: l_user := null;
5584: if(l_comment is null or length(trim(l_comment)) = 0) then
5585: wf_core.raise('WFNTF_NO_ANSWER');
5586: else
5587: wf_notification.UpdateInfo2(l_nid, l_user, l_fromAddr,
5588: l_comment);
5589: end if;
5590: else
5591: -- Do not need to preserve context
5593:
5594: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5595: wf_log_pkg.string(WF_LOG_PKG.level_statement,
5596: 'wf.plsql.WF_XML.handleReceiveEvent',
5597: 'Calling Wf_Notification.Respond');
5598: end if;
5599:
5600: Wf_Notification.Respond(nid => l_nid,
5601: responder => 'email:'||l_fromAddr);
5596: 'wf.plsql.WF_XML.handleReceiveEvent',
5597: 'Calling Wf_Notification.Respond');
5598: end if;
5599:
5600: Wf_Notification.Respond(nid => l_nid,
5601: responder => 'email:'||l_fromAddr);
5602: end if;
5603: exception
5604: when others then
5724: -- to recepient_role if notification more_info_role is not null.
5725: CURSOR c_get_summary_roles is
5726: SELECT recipient from
5727: (SELECT distinct nvl(more_info_role,recipient_role) recipient
5728: FROM wf_notifications
5729: WHERE mail_status is null
5730: AND status = 'OPEN'
5731: AND rownum > 0)
5732: WHERE Wf_Directory.GetRoleNtfPref(recipient) in ('SUMMARY', 'SUMHTML');
5735: -- and having some open notifications
5736: CURSOR c_get_query_roles is
5737: SELECT recipient from
5738: (SELECT distinct nvl(more_info_role,recipient_role) recipient
5739: FROM wf_notifications
5740: WHERE mail_status is null
5741: AND status = 'OPEN'
5742: AND rownum > 0) , wf_roles wr
5743: WHERE recipient = wr.NAME
6025: error_stack := p_event.getErrorStack;
6026: begin
6027: select MESSAGE_TYPE, CALLBACK, CONTEXT, STATUS
6028: into itemType, cb, ctx, status
6029: from WF_NOTIFICATIONS
6030: where NOTIFICATION_ID = nid;
6031:
6032: -- If the notification is closed, then do not bother
6033: -- to process any errors.
6034: if status = 'CLOSED' then
6035: return 'SUCCESS';
6036: end if;
6037:
6038: update wf_notifications
6039: set mail_status = 'ERROR'
6040: where notification_id = nid;
6041:
6042: if ctx is not null then
6164:
6165: if (procarg is null) then
6166: procarg := '-dummy-';
6167: elsif pNid > 0 then
6168: procarg := Wf_Notification.GetText(procarg, pNid, disptype);
6169: end if;
6170:
6171: sqlbuf := 'begin '||procname||'(:p1, :p2, :p3, :p4); end;';
6172: execute immediate sqlbuf using
6212:
6213: if (procarg is null) then
6214: procarg := '-dummy-';
6215: elsif pNid > 0 then
6216: procarg := Wf_Notification.GetText(procarg, pNid, disptype);
6217: end if;
6218:
6219: sqlbuf := 'begin '||procname||'(:p1, :p2, :p3, :p4); end;';
6220: execute immediate sqlbuf using
6233: end;
6234:
6235:
6236: -- Send_Rule - This is the subscription rule function for the event group
6237: -- 'oracle.apps.wf.notification.send.group'. If the message
6238: -- payload is not complete return 'SUCCESS' from here, hence
6239: -- incomplete message payload/event will not be enqueued to
6240: -- WF_NOTIFICATION_OUT AQ.
6241: -- IN
6236: -- Send_Rule - This is the subscription rule function for the event group
6237: -- 'oracle.apps.wf.notification.send.group'. If the message
6238: -- payload is not complete return 'SUCCESS' from here, hence
6239: -- incomplete message payload/event will not be enqueued to
6240: -- WF_NOTIFICATION_OUT AQ.
6241: -- IN
6242: -- p_subscription_guid Subscription GUID as a CLOB
6243: -- p_event Event Message
6244: -- OUT
6253: begin
6254:
6255: -- If the notification is to be sent to a role whose message payload
6256: -- has multiple values for 'full-document' parameter, in that case
6257: -- we should enqueue the notification to WF_NOTIFICATION_OUT queue
6258: -- so that email notification will be sent to the users having the
6259: -- full-document="Y" value
6260:
6261: l_fulldocloc := dbms_lob.instr(l_eventdata, 'full-document="Y"', 1, 1);
6278: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
6279: wf_log_pkg.string(wf_log_pkg.level_statement,
6280: 'wf.plsql.WF_XML.Send_Rule',
6281: 'No Full Message for the event key '||l_eventKey||'.'
6282: ||'NOT enquing the message into WF_NOTIFICATION_OUT queue.');
6283: end if;
6284: return 'SUCCESS';
6285: end if;
6286: