[Home] [Help]
1: package body WF_MAIL as
2: /* $Header: wfmlrb.pls 120.31.12010000.4 2008/12/05 00:04:53 alepe ship $ */
3:
4:
5: --
124: error_name in varchar2)
125: is
126: l_autoclose VARCHAR(1);
127: begin
128: if wf_mail.autoclose_fyi then
129: l_autoclose := 'Y';
130: else
131: l_autoclose := 'N';
132: end if;
129: l_autoclose := 'Y';
130: else
131: l_autoclose := 'N';
132: end if;
133: wf_mail.UpdateStatus2(nid => UpdateStatus.nid,
134: status => UpdateStatus.status,
135: autoclose => l_autoclose,
136: error_name => UpdateStatus.error_name,
137: external_error => null);
136: error_name => UpdateStatus.error_name,
137: external_error => null);
138: exception
139: when others then
140: wf_core.context('WF_MAIL', 'UpdateStatus', to_char(nid),
141: UpdateStatus.status, UpdateStatus.error_name);
142: raise;
143:
144: end UpdateStatus;
192: end if;
193:
194:
195: if (UpdateStatus2.status = 'ERROR') then
196: WF_MAIL.HandleSendError(nid => UpdateStatus2.nid,
197: status => UpdateStatus2.status,
198: error_name => UpdateStatus2.error_name,
199: external_error => UpdateStatus2.external_error);
200: elsif (UpdateStatus2.status = 'FAILED') then
266: and nvl(end_date, sysdate + 1)));
267: end if;
268: exception
269: when others then
270: wf_core.context('WF_MAIL', 'UpdateStatus2', to_char(nid),
271: UpdateStatus2.status, UpdateStatus2.autoclose,
272: UpdateStatus2.error_name);
273: raise;
274:
305: commit;
306:
307: exception
308: when others then
309: wf_core.context('WF_MAIL', 'ResetFailed', p_queue);
310: raise;
311:
312: end ResetFailed;
313:
421:
422: errMessage varchar2(4000);
423: errStack varchar2(32000);
424:
425: tokens wf_mail_util.parserStack_t;
426: tk pls_integer;
427: dummy varchar2(2000);
428: dummyNumber number;
429:
451: begin
452:
453: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
454: wf_log_pkg.string(WF_LOG_PKG.level_procedure,
455: 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref',
456: 'BEGIN');
457: end if;
458: role := p_event.getEventKey();
459: params := p_event.getParameterList();
491: recipientAddress := substrb(externError, delim+1);
492:
493: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
494: wf_log_pkg.string(WF_LOG_PKG.level_statement,
495: 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref',
496: 'NID ['||to_char(nid)||
497: '] RESPONDER ['||recipientAddress||']');
498: end if;
499:
506: when no_data_found then
507: -- If there is no notification, then don't do anything
508: return 'SUCCESS';
509: when others then
510: wf_core.context('WF_MAIL','Disable_Recipient_Ntf_Pref',
511: errorName, externError, sqlerrm);
512: raise;
513: end;
514:
535: end loop;
536: end if;
537: end if;
538:
539: tokens := wf_mail_util.strParser(externError, '{}');
540: paramList := wf_parameter_list_t(null);
541:
542: invalidRoleList := '';
543: reasonList := '';
552: if tokens(tk) is not null and tokens(tk) <> ',' then
553:
554: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
555: wf_log_pkg.string(WF_LOG_PKG.level_statement,
556: 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref',
557: 'TOKEN ['||tokens(tk)||']');
558: end if;
559: Wf_Directory.GetRoleInfoMail(role => tokens(tk),
560: display_name => dummy,
567: installed_flag => dummy);
568:
569: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
570: wf_log_pkg.string(WF_LOG_PKG.level_statement,
571: 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref',
572: 'PREFERENCE ['||pref||']');
573: end if;
574:
575: if pref is not null and
624:
625: else
626: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
627: wf_log_pkg.string(WF_LOG_PKG.level_statement,
628: 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref',
629: 'Unable to update notification preference for email ['||
630: tokens(tk)||']. Check for duplicates');
631: end if;
632: end if;
645: -- following roles will be disabled DISABLE_REPORT
646: if (recipient_disabled) then
647: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
648: wf_log_pkg.string(WF_LOG_PKG.level_statement,
649: 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref',
650: 'One or more users have been disabled. Sending a notification to SYSADMIN');
651: end if;
652: if length(errorReport) > 2000 then
653: errorReport := substrb(errorReport, 1, 1900);
699:
700: if invalidRoleCount > 0 then
701: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
702: wf_log_pkg.string(WF_LOG_PKG.level_procedure,
703: 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref',
704: 'END WARNING');
705: end if;
706:
707: p_event.setErrorMessage(wf_core.translate('WFMLR_ROLE_UPDATE_FAILURE'));
709: return 'WARNING';
710: else
711: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
712: wf_log_pkg.string(WF_LOG_PKG.level_procedure,
713: 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref',
714: 'END SUCCESS');
715: end if;
716: return 'SUCCESS';
717: end if;
717: end if;
718:
719: exception
720: when others then
721: wf_core.context('WF_MAIL','Disable_Recipient_Ntf_Pref',
722: role, pref);
723: raise;
724: end Disable_Recipient_Ntf_Pref;
725:
757:
758: return(buffer);
759: exception
760: when others then
761: wf_core.context('WF_MAIL', 'GetLovList', lk_type);
762: raise;
763: end GetLovList;
764:
765:
838:
839: begin
840: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
841: wf_log_pkg.string(WF_LOG_PKG.level_procedure,
842: 'wf.plsql.WF_MAIL.HandleResponseError',
843: 'BEGIN');
844: end if;
845:
846: g_invalidRemarks := wf_core.translate('WFMLR_INVALID_REMARKS');
965: wf_event.addParameterToList('Q_CORRELATION_ID', mType || ':' || mName, parameterlist);
966:
967: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
968: wf_log_pkg.string(WF_LOG_PKG.level_statement,
969: 'wf.plsql.WF_MAIL.HandleResponseError',
970: 'Raising the Send event');
971: end if;
972:
973: --Raise the event
978: exception
979: when others then
980: if (wf_log_pkg.level_exception >= fnd_log.g_current_runtime_level) then
981: wf_log_pkg.string(WF_LOG_PKG.level_exception,
982: 'wf.plsql.WF_MAIL.HandleResponseError',
983: 'Error Msg '|| sqlerrm);
984: end if;
985:
986: wf_core.context('WF_MAIL', 'HandleResponseError', nid);
982: 'wf.plsql.WF_MAIL.HandleResponseError',
983: 'Error Msg '|| sqlerrm);
984: end if;
985:
986: wf_core.context('WF_MAIL', 'HandleResponseError', nid);
987: raise;
988: end HandleResponseError;
989:
990:
1066:
1067: return(buf);
1068: exception
1069: when others then
1070: wf_core.context('WF_MAIL', 'WordWrap', text, to_char(indent));
1071: raise;
1072: end WordWrap;
1073:
1074:
1104:
1105: return(buf);
1106: exception
1107: when others then
1108: wf_core.context('WF_MAIL', 'GetLovMeaning', lk_type, lk_code);
1109: raise;
1110: end GetLovMeaning;
1111:
1112:
1156:
1157: return buf;
1158: exception
1159: when others then
1160: wf_core.context('WF_MAIL', 'GetLovCode', lk_type, lk_meaning);
1161: raise;
1162: end GetLovCode;
1163:
1164:
1196:
1197: return(buffer);
1198: exception
1199: when others then
1200: wf_core.context('WF_MAIL', 'GetLovListInternal', lk_type);
1201: raise;
1202: end GetLovListInternal;
1203:
1204: -- GetDirectAnswer (PRIVATE)
1259: one_answer := answer;
1260:
1261: exception
1262: when answer_syntax_error then
1263: wf_core.context('WF_MAIL', 'GetDirectAnswer');
1264: raise;
1265: when others then
1266: wf_core.context('WF_MAIL', 'GetDirectAnswer');
1267: raise;
1262: when answer_syntax_error then
1263: wf_core.context('WF_MAIL', 'GetDirectAnswer');
1264: raise;
1265: when others then
1266: wf_core.context('WF_MAIL', 'GetDirectAnswer');
1267: raise;
1268: end GetDirectAnswer;
1269:
1270:
1312: end if;
1313:
1314: -- Print prompt
1315: buffer := buffer||' '||rec.display_name||': '||
1316: wf_mail.g_open_text_delimiter;
1317:
1318: -- Print field
1319: if (rec.type = 'LOOKUP') then
1320: -- LOOKUPs: show displayed meaning, list of choices
1318: -- Print field
1319: if (rec.type = 'LOOKUP') then
1320: -- LOOKUPs: show displayed meaning, list of choices
1321: buffer := buffer || GetLovMeaning(rec.format, rec.value) ||
1322: wf_mail.g_close_text_delimiter || g_newLine ||
1323: GetLovList(rec.format);
1324: else
1325: -- VARCHAR2, NUMBER, or DATE: use value directly.
1326: buffer := buffer || rec.value || wf_mail.g_close_text_delimiter ||
1322: wf_mail.g_close_text_delimiter || g_newLine ||
1323: GetLovList(rec.format);
1324: else
1325: -- VARCHAR2, NUMBER, or DATE: use value directly.
1326: buffer := buffer || rec.value || wf_mail.g_close_text_delimiter ||
1327: g_newLine;
1328: end if;
1329:
1330: buffer := buffer || g_newLine;
1333: return buffer;
1334:
1335: exception
1336: when others then
1337: wf_core.context('WF_MAIL', 'GetEmailResponse', to_char(nid));
1338: raise;
1339: end GetEmailResponse;
1340:
1341:
1459: return buffer;
1460:
1461: exception
1462: when others then
1463: wf_core.context('WF_MAIL', 'GetEmailDirectResponse', to_char(nid));
1464: raise;
1465: end GetEmailDirectResponse;
1466:
1467:
1605: end loop;
1606: return buffer;
1607: exception
1608: when others then
1609: WF_CORE.Context('WF_MAIL','GetMoreInfoLOV', to_char(nid));
1610: raise;
1611: end GetMoreInfoLOV;
1612:
1613: --
1669: body := body || g_moreInfoAPrompt;
1670:
1671: -- ankung (removing <)
1672: -- body := body || ': ''<';
1673: body := body || ': '||wf_mail.g_open_html_delimiter;
1674:
1675: body := body || g_moreInfoAnswer;
1676:
1677: -- ankung (removing >)
1675: body := body || g_moreInfoAnswer;
1676:
1677: -- ankung (removing >)
1678: -- body := body || '>''';
1679: body := body || wf_mail.g_close_html_delimiter;
1680:
1681: body := body || g_newLine;
1682: body := UrlEncode(body);
1683: buffer := buffer ||'&'||'nbsp;'||'&'||'nbsp;'||
1697: -- ankung (placing role between the '')
1698: -- body := body || g_moreInfoFrom ||': ''''';
1699: -- body := body || g_newLine;
1700: body := body || g_moreInfoFrom;
1701: body := body || ': '||wf_mail.g_open_html_delimiter;
1702:
1703: l_requestee := GetMoreInfoLOV(nid, g_to_role);
1704: if (l_requestee is null) then
1705: l_requestee := g_moreInfoRequestee;
1707: body := body || wf_notification.SubstituteSpecialChars(l_requestee);
1708: -- body := body || GetMoreInfoLOV(nid, g_to_role);
1709:
1710: -- ankung (continuation of above)
1711: body := body || wf_mail.g_close_html_delimiter;
1712:
1713: -- sacsharm commented out, too much space
1714: -- body := body || g_newLine;
1715: body := body || g_newLine;
1716: body := body || g_moreInfoQPrompt;
1717:
1718: -- ankung (removing <)
1719: -- body := body || ': ''<';
1720: body := body || ': '||wf_mail.g_open_html_delimiter;
1721:
1722: body := body || g_moreInfoQuestion;
1723:
1724: -- ankung (removing >)
1722: body := body || g_moreInfoQuestion;
1723:
1724: -- ankung (removing >)
1725: -- body := body || '>''';
1726: body := body || wf_mail.g_close_html_delimiter;
1727:
1728: -- sacsharm commented out, too much space
1729: -- body := body || g_newLine;
1730: body := body || g_newLine;
1739: end if;
1740: return buffer;
1741: exception
1742: when others then
1743: wf_core.context('Wf_Mail', 'GetMoreInfoMailTo', to_char(nid));
1744: raise;
1745: end GetMoreInfoMailTo;
1746:
1747:
1793: end if;
1794: end if;
1795:
1796: -- Print prompt
1797: buffer := buffer||rec.display_name||': '||wf_mail.g_open_html_delimiter;
1798:
1799: -- Preseed the answer so that recipient does not have to type in manually.
1800: if ((rec.name = 'RESULT') and (result_answer <> 'Respond')) then
1801: rec.value := result_answer;
1804: -- Print field
1805: if (rec.type = 'LOOKUP') then
1806: -- LOOKUPs: show displayed meaning, list of choices
1807: buffer := buffer || GetLovMeaning(rec.format, rec.value) ||
1808: wf_mail.g_close_html_delimiter|| g_newLine;
1809: if (rec.name <> 'RESULT' ) then
1810: buffer := buffer || GetLovList(rec.format);
1811: end if;
1812: else
1810: buffer := buffer || GetLovList(rec.format);
1811: end if;
1812: else
1813: -- VARCHAR2, NUMBER, or DATE: use value directly.
1814: buffer := buffer || rec.value ||wf_mail.g_close_html_delimiter||
1815: g_newLine;
1816: end if;
1817:
1818: buffer := buffer || g_newLine;
1823: return buffer;
1824:
1825: exception
1826: when others then
1827: wf_core.context('WF_MAIL', 'GetMailToBody', to_char(nid));
1828: raise;
1829: end GetMailToBody;
1830:
1831: -- GetMailToBody (PRIVATE) - Construct the mailto body part.
1882: end if;
1883:
1884: -- Print prompt
1885: str_buffer := str_buffer||rec.display_name||': '||
1886: wf_mail.g_open_html_delimiter;
1887:
1888: -- Preseed the answer so that recipient does not have
1889: -- to type in manually.
1890:
1895: -- Print field
1896: if (rec.type = 'LOOKUP') then
1897: -- LOOKUPs: show displayed meaning, list of choices
1898: str_buffer := str_buffer || GetLovMeaning(rec.format, rec.value) ||
1899: wf_mail.g_close_html_delimiter|| g_newLine;
1900: if (rec.name <> 'RESULT' ) then
1901: str_buffer := str_buffer || GetLovList(rec.format);
1902: end if;
1903: else
1902: end if;
1903: else
1904: -- VARCHAR2, NUMBER, or DATE: use value directly.
1905: str_buffer := str_buffer || rec.value ||
1906: wf_mail.g_close_html_delimiter||g_newLine;
1907: end if;
1908:
1909: str_buffer := str_buffer || g_newLine;
1910:
1934:
1935: exception
1936: when others then
1937: -- wf_temp_lob.releaseLob(g_LOBTable, bufferIdx);
1938: wf_core.context('WF_MAIL', 'GetMailToBody', to_char(nid));
1939: raise;
1940: end GetMailToBody;
1941:
1942: -- GetMailTo - Construct MailTo Section (PRIVATE)
2105: return(buffer);
2106:
2107: exception
2108: when others then
2109: wf_core.context('WF_MAIL', 'GetMailTo', nid);
2110: raise;
2111:
2112: end GetMailTo;
2113:
2270: html_body_text := t_html_body;
2271:
2272: exception
2273: when others then
2274: wf_core.context('WF_MAIL', 'GetWarning', ufrom, usubject, ubody);
2275: raise;
2276: end GetWarning;
2277:
2278:
2295: text_body_text out NOCOPY varchar2,
2296: html_body_text out NOCOPY varchar2)
2297: as
2298: begin
2299: wf_mail.GetWarning('WFMAIL:WARNING', ufrom, usubject, ubody, subject,
2300: text_body_text, html_body_text);
2301: end GetWarning;
2302:
2303: -- GetTemplateName - Get the template type and name based on the
2326: inAttr varchar2(1);
2327:
2328: begin
2329: t_type := 'WFMAIL'; -- Set the default type;
2330: wf_mail.Set_FYI_Flag(FALSE);
2331:
2332: -- Get template name
2333: if (n_status = 'OPEN') then
2334: t_name := 'OPEN_'||n_mstatus;
2357: and MA.MESSAGE_NAME = N.MESSAGE_NAME
2358: and MA.SUBTYPE = 'RESPOND');
2359: -- Set the template name to FYI
2360: t_name := t_name||'_FYI';
2361: wf_mail.Set_FYI_Flag(TRUE);
2362:
2363: exception
2364: when NO_DATA_FOUND then
2365: -- If a different mail template name is specified, it took
2368: t_name := g_template;
2369:
2370: -- This is a response required notification.
2371: -- Qualify if DIRECT_RESPONSE=Y
2372: elsif wf_mail.direct_response then
2373: t_name := t_name || '_DIRECT';
2374: end if;
2375: end;
2376: end if;
2381: where notification_id = nid;
2382:
2383: -- Now that the template name has been derrived, see
2384: -- if the default value has been overridden.
2385: altTempl := WF_MAILER_PARAMETER.getValueForCorr(nid, mType || ':'|| mName, t_name, inAttr);
2386: colPos := instrb(altTempl, ':', 1);
2387: if colPos > 0 then
2388: t_type := substrb(altTempl, 1, colPos -1);
2389: t_name := substrb(altTempl, colPos + 1, length(altTempl)-colPos);
2396: where NAME = t_name
2397: and TYPE = t_type;
2398: exception
2399: when NO_DATA_FOUND then
2400: wf_core.context('WF_MAIL','getTemplateName',
2401: 'nid => '||to_char(nid),
2402: 'n_status => '||n_status,
2403: 'n_mstatus => '||n_mStatus,
2404: 't_type => '||t_type,
2410: end if;
2411:
2412: exception
2413: when others then
2414: WF_CORE.Context('WF_MAIL','getTemplateName',to_char(nid), n_status,
2415: n_mstatus, t_type, t_name);
2416: raise;
2417: end getTemplateName;
2418:
2448: t_type := 'WFMAIL';
2449:
2450: -- If the content is secure, just dont send anything pertaining to the notification
2451: -- other than the nid.
2452: Wf_Mail.ProcessSecurityPolicy(nid, l_sec_policy, t_name);
2453: if (t_name is not null) then
2454: n_nid_str := 'NID '||to_char(nid);
2455: return;
2456: end if;
2468: end if;
2469:
2470:
2471: -- construct the NID string for the notification
2472: if (wf_mail.direct_response) then
2473: n_nid_str := 'NID['||to_char(nid)||'/'||n_key||'@'||node||']'||'[2]';
2474: else
2475: n_nid_str := 'NID['||to_char(nid)||'/'||n_key||'@'||node||']';
2476: end if;
2496: end if;
2497:
2498: exception
2499: when others then
2500: wf_core.context('WF_MAIL', 'ProcessSignaturePolicy', to_char(nid));
2501: raise;
2502: end ProcessSignaturePolicy;
2503:
2504: -- Returns TRUE if the language is Bi directional
2652: document_type := display_type;
2653:
2654: exception
2655: when others then
2656: wf_core.context('WF_MAIL','GetHeaderTable',document_id, display_type);
2657: raise;
2658: end GetHeaderTable;
2659:
2660:
2772:
2773: -- More information processing - bug 2282139
2774: g_moreinfo := NULL;
2775:
2776: if (wf_mail.test_flag = TRUE) then
2777: n_mstatus := 'MAIL';
2778: if (n_status not in ('OPEN','CANCELED','CLOSED')) then
2779: n_status := 'OPEN';
2780: end if;
2834: ''||g_newLine;
2835: end if;
2836:
2837: -- Bug 2375920 get signature policy for the notification
2838: Wf_Mail.GetSignaturePolicy(nid, n_sig_policy);
2839:
2840: n_subject := WF_NOTIFICATION.GetSubject(nid, 'text/plain');
2841:
2842: -- We will always fetch plain text version of the message because
3023: end if;
3024: end if;
3025:
3026: -- Substitute
3027: if wf_mail.direct_response then
3028: n_direct := '[2]';
3029: else
3030: n_direct := NULL;
3031: end if;
3073: n_response := g_moreInfoAPrompt || ': "<' ||
3074: g_moreInfoAnswer ||'>"';
3075: n_response := n_response || g_newLine;
3076: else
3077: if wf_mail.direct_response then
3078: n_response := GetEmailDirectResponse(nid);
3079: else
3080: n_response := GetEmailResponse(nid);
3081: end if;
3159:
3160: -- Add click_here_response section
3161: n_click_here := ''||n_disp_click||'';
4615:
4616: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
4617: wf_log_pkg.string(WF_LOG_PKG.level_statement,'wf.plsql.WF_MAIL.newLOBTag',
4618: 'URL: {'||n_click_here||'}');
4619: end if;
4620:
4621:
4674: end if;
4675:
4676: exception
4677: when others then
4678: WF_CORE.Context('WF_MAIL','SetContext',to_char(nid));
4679: raise;
4680: end setContext;
4681:
4682: -- Get_Last_Question (Private)
4756: AND n.message_name = wm.name
4757: AND n.message_type = wm.type;
4758:
4759: -- FYI notification
4760: if (wf_mail.Get_FYI_Flag) then
4761: open c_comm;
4762: fetch c_comm into l_comm_cnt;
4763: if (c_comm%notfound) then
4764: l_comm_cnt := 0;
4810: exception
4811: when others then
4812: if (wf_log_pkg.level_exception >= fnd_log.g_current_runtime_level) then
4813: wf_log_pkg.string(WF_LOG_PKG.level_exception,
4814: 'wf.plsql.wf_mail.Get_Action_History',
4815: 'Get_Action_History failed. Error: ' || sqlerrm);
4816: end if;
4817: p_text_history := '';
4818: p_html_history := '';
4930: begin
4931:
4932: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
4933: wf_log_pkg.string(WF_LOG_PKG.level_procedure,
4934: 'wf.plsql.WF_MAIL.GetLOBMessage3', 'BEGIN');
4935: end if;
4936:
4937: -- 3532615 Moved these from being initialized at the session level
4938: -- back to the notification level.
4992: r_ntf_pref := ntf_pref;
4993: r_email := email;
4994: r_dname := dname;
4995:
4996: if (wf_mail.test_flag = TRUE) then
4997: n_mstatus := 'MAIL';
4998: if (n_status not in ('OPEN','CANCELED','CLOSED')) then
4999: n_status := 'OPEN';
5000: end if;
5018: r_ntf_pref := nvl(r_ntf_pref, 'QUERY');
5019:
5020: step := 'Getting Signature policy';
5021: -- Bug 2375920 get signature policy for the notification
5022: Wf_Mail.GetSignaturePolicy(nid, n_sig_policy);
5023:
5024: -- Bug 2375920 Process the email message based on the signature policy
5025: ProcessSignaturePolicy(nid, n_sig_policy, n_status, n_mstatus,
5026: n_key, node, t_type, t_name, n_nid_str);
5069: g_isFwkNtf := true;
5070: end if;
5071:
5072: step := 'Getting timezone details';
5073: n_text_timezone := wf_mail_util.getTimezone(g_ntfDocText);
5074: n_html_timezone := wf_mail_util.getTimezone(g_ntfDocHtml);
5075:
5076: step := 'Getting subject';
5077: n_subject := WF_NOTIFICATION.GetSubject(nid, 'text/plain');
5070: end if;
5071:
5072: step := 'Getting timezone details';
5073: n_text_timezone := wf_mail_util.getTimezone(g_ntfDocText);
5074: n_html_timezone := wf_mail_util.getTimezone(g_ntfDocHtml);
5075:
5076: step := 'Getting subject';
5077: n_subject := WF_NOTIFICATION.GetSubject(nid, 'text/plain');
5078:
5090: step := 'Getting body';
5091: if (r_ntf_pref in ('MAILTEXT', 'MAILATTH')) then
5092: -- Below Allocated TEMP LOB here is released within WF_XML.getBodyPart
5093: -- ( which is called by caller of getLOBMessage3 API)
5094: -- based on the Ntf type (text/plain or text/html) by calling wf_mail.CloseLOB
5095: g_text_messageIdx := wf_temp_lob.getLob(g_LOBTable);
5096:
5097: if (renderbody = 'Y') then
5098: end_of_message := FALSE;
5108: end loop;
5109: exception
5110: when others then
5111:
5112: wf_core.context('WF_MAIL','GetLOBMessage3',
5113: 'nid => '||to_char(nid),
5114: 'r_ntf_pref => '||r_ntf_pref);
5115: wf_core.token('ERROR',sqlerrm);
5116: wf_core.raise('WFMLR_NTFERR');
5125: end if;
5126:
5127: if (r_ntf_pref in ('MAILHTML', 'MAILATTH', 'MAILHTM2')) then
5128: -- Below Allocated TEMP LOB IS released within WF_XML.getBodyPart
5129: -- based on the Ntf type (text/plain or text/html) by calling wf_mail.CloseLOB
5130: g_html_messageIdx := wf_temp_lob.getLob(g_LOBTable);
5131:
5132: if (renderbody = 'Y') then
5133: end_of_message := FALSE;
5142: end if;
5143: end loop;
5144: exception
5145: when others then
5146: wf_core.context('WF_MAIL','GetLOBMessage3',
5147: 'nid => '||to_char(nid),
5148: 'r_ntf_pref => '||r_ntf_pref);
5149: wf_core.token('ERROR',sqlerrm);
5150: wf_core.raise('WFMLR_NTFERR');
5220: end if;
5221:
5222: -- DBMS_LOB.Write(template_html, length(t_html_body), 1, t_html_body);
5223:
5224: if wf_mail.direct_response then
5225: n_direct := '[2]';
5226: else
5227: n_direct := NULL;
5228: end if;
5228: end if;
5229:
5230: -- More info feature
5231: if (g_moreinfo = 'SUB') then
5232: n_response := g_moreInfoAPrompt || ': '||wf_mail.g_open_text_delimiter||
5233: '<' ||
5234: g_moreInfoAnswer ||'>'||wf_mail.g_close_text_delimiter;
5235: n_response := n_response || g_newLine;
5236: else
5230: -- More info feature
5231: if (g_moreinfo = 'SUB') then
5232: n_response := g_moreInfoAPrompt || ': '||wf_mail.g_open_text_delimiter||
5233: '<' ||
5234: g_moreInfoAnswer ||'>'||wf_mail.g_close_text_delimiter;
5235: n_response := n_response || g_newLine;
5236: else
5237: if wf_mail.direct_response then
5238: n_response := GetEmailDirectResponse(nid);
5233: '<' ||
5234: g_moreInfoAnswer ||'>'||wf_mail.g_close_text_delimiter;
5235: n_response := n_response || g_newLine;
5236: else
5237: if wf_mail.direct_response then
5238: n_response := GetEmailDirectResponse(nid);
5239: else
5240: n_response := GetEmailResponse(nid);
5241: end if;
5337:
5338: -- If GUEST access is enabled and signature is required for response,
5339: -- no click here reponse link is provided to discourage signing under
5340: -- GUEST login
5341: if (g_sig_required = 'Y' and wf_mail.Send_AccessKey) then
5342: n_click_here := '';
5343: else
5344: n_click_here := getClickHereResponse(nid => nid, n_key => n_key,
5345: agent => agent,
5394: -- end if;
5395:
5396: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
5397: wf_log_pkg.string(WF_LOG_PKG.level_procedure,
5398: 'wf.plsql.WF_MAIL.GetLOBMessage3', 'END');
5399: end if;
5400:
5401: exception
5402: when no_program_unit then
5399: end if;
5400:
5401: exception
5402: when no_program_unit then
5403: wf_core.context('WF_MAIL', 'GetLOBMessage3', to_char(nid), node,
5404: 'step -> '||step);
5405:
5406: if(g_html_messageIdx IS NOT NULL AND g_html_messageIdx > 0 ) then
5407: wf_temp_lob.releaseLob(g_LOBTable, g_html_messageIdx);
5428: wf_temp_lob.releaseLob(g_LOBTable, g_text_messageIdx);
5429: end if;
5430:
5431: error_result := err_message||g_newLine||err_stack;
5432: wf_core.context('WF_MAIL', 'GetLOBMessage3', to_char(nid), node,
5433: error_result, 'Step -> '||step);
5434: end GetLOBMessage3;
5435:
5436:
5476: pragma exception_init(no_program_unit, -6508);
5477:
5478: begin
5479:
5480: wf_mail.getLobMessage3(nid, node, agent, replyto, recipient, language,
5481: territory, ntf_pref, email, dname, 'Y',
5482: subject, body_atth, error_result, bodyToken);
5483:
5484: exception
5482: subject, body_atth, error_result, bodyToken);
5483:
5484: exception
5485: when no_program_unit then
5486: wf_core.context('WF_MAIL', 'GetLOBMessage2', to_char(nid), node);
5487: raise;
5488: when others then
5489: -- First look for a wf_core error.
5490: wf_core.get_error(err_name, err_message, err_stack);
5494: err_message := sqlerrm;
5495: end if;
5496:
5497: error_result := err_message||g_newLine||err_stack;
5498: wf_core.context('WF_MAIL', 'GetLOBMessage2', to_char(nid), node,
5499: error_result);
5500:
5501: end getLOBMessage2;
5502:
5558: r_language := 'AMERICAN';
5559: r_territory := 'AMERICA';
5560: end if;
5561:
5562: wf_mail.getLobMessage2(nid, node, agent, replyto, n_to_role, r_language,
5563: r_territory, r_ntf_pref, r_email, r_dname,
5564: subject, body_atth, error_result);
5565:
5566: exception
5564: subject, body_atth, error_result);
5565:
5566: exception
5567: when no_program_unit then
5568: wf_core.context('WF_MAIL', 'GetLOBMessage', to_char(nid), node);
5569: raise;
5570: when others then
5571: -- First look for a wf_core error.
5572: wf_core.get_error(err_name, err_message, err_stack);
5576: err_message := sqlerrm;
5577: end if;
5578:
5579: error_result := err_message||g_newLine||err_stack;
5580: wf_core.context('WF_MAIL', 'GetLOBMessage', to_char(nid), node,
5581: error_result);
5582: end GetLOBMessage;
5583:
5584: -- GetSummary - get summary messages for one role
5601: lob varchar2(1);
5602: begin
5603: GetSummary(role, dname, node, subject, body_text,lob);
5604: if lob = 'Y' then
5605: wf_core.context('WF_MAIL', 'GetSummary', role, node);
5606: wf_core.raise('WFMLR_SUMMARY_TOOBIG');
5607: end if;
5608: exception
5609: when others then
5606: wf_core.raise('WFMLR_SUMMARY_TOOBIG');
5607: end if;
5608: exception
5609: when others then
5610: wf_core.context('WF_MAIL', 'GetSummary', role, node);
5611: raise;
5612: end GetSummary;
5613:
5614: -- GetSummary2 - get summary messages for one role
5686: begin
5687:
5688: if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
5689: wf_log_pkg.string(wf_log_pkg.level_event,
5690: 'wf.plsql.WF_MAIL.GetSummary2',
5691: 'BEGIN');
5692: end if;
5693:
5694: g_wfmonId := wf_core.translate('WFMON_ID');
5706: end if;
5707:
5708: -- We have the basic template name. Now check to see if has been
5709: -- redirected using a mailer configuration parameter of the same name.
5710: altTempl := WF_MAILER_PARAMETER.GetValueForCorr('WFMAIL', templateName);
5711: colon := instrb(altTempl, ':', 1);
5712: if colon > 0 then
5713: templateType := substrb(altTempl, 1, colon -1);
5714: templateName := substrb(altTempl, colon + 1, length(altTempl)-colon);
5773: end if;
5774:
5775:
5776: -- Substitute USER_NAME with role display name
5777: t_timezone := wf_mail_util.getTimezone(contType);
5778:
5779: if contType = g_ntfDocText then
5780: t_body := substrb(replace(t_body, '&'||'USER_NAME', dname), 1, 32000);
5781: t_body := substrb(replace(t_body, '&'||'TIMEZONE', t_timezone), 1, 32000);
5888: end if;
5889:
5890: if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
5891: wf_log_pkg.string(wf_log_pkg.level_event,
5892: 'wf.plsql.WF_MAIL.GetSummary2',
5893: 'END');
5894: end if;
5895:
5896:
5908:
5909: end if;
5910:
5911:
5912: wf_core.context('WF_MAIL', 'GetSummary2', role, node, contType);
5913: raise;
5914: end GetSummary2;
5915:
5916: -- GetSummary - get summary messages for one role
5936: begin
5937: GetSummary2(role, dname, node, 'Y', 'text/plain', subject, body_text, lob);
5938: exception
5939: when others then
5940: wf_core.context('WF_MAIL', 'GetSummary', role, node);
5941: raise;
5942: end GetSummary;
5943:
5944:
5972: -- OUT
5973: -- 32K chunk of the LOB
5974: --
5975: -- Use the API in the following manner
5976: -- WF_MAIL.InitFetchLob(g_ntfDocText)
5977: -- while not clob_end loop
5978: -- WF_MAIL.FetchLobContent(cBuf, g_ntfDocText, clob_end);
5979: -- ...
5980: -- end loop;
5974: --
5975: -- Use the API in the following manner
5976: -- WF_MAIL.InitFetchLob(g_ntfDocText)
5977: -- while not clob_end loop
5978: -- WF_MAIL.FetchLobContent(cBuf, g_ntfDocText, clob_end);
5979: -- ...
5980: -- end loop;
5981: --
5982: procedure FetchLOBContent(buffer OUT NOCOPY VARCHAR2,
6009: end if;
6010: end if;
6011: exception
6012: when others then
6013: WF_CORE.Context('WF_MAIL','FetchLOBContent',doc_type,
6014: to_char(pos)||':'||buffer);
6015: raise;
6016: end FetchLOBContent;
6017:
6030: wf_temp_lob.releaseLob(g_LOBTable, g_text_messageIdx);
6031: end if;
6032: exception
6033: when others then
6034: WF_CORE.Context('WF_MAIL','CloseLOB', doc_type);
6035: raise;
6036: end;
6037:
6038: -- CloseLOB - Close the message LOBs ready for use again later
6039: --
6040: procedure CloseLOB
6041: is
6042: begin
6043: WF_MAIL.CloseLOB(g_ntfDocText);
6044: WF_MAIL.CloseLOB(g_ntfDocHtml);
6045: exception
6046: when others then
6047: WF_CORE.Context('WF_MAIL','CloseLOB');
6040: procedure CloseLOB
6041: is
6042: begin
6043: WF_MAIL.CloseLOB(g_ntfDocText);
6044: WF_MAIL.CloseLOB(g_ntfDocHtml);
6045: exception
6046: when others then
6047: WF_CORE.Context('WF_MAIL','CloseLOB');
6048: raise;
6043: WF_MAIL.CloseLOB(g_ntfDocText);
6044: WF_MAIL.CloseLOB(g_ntfDocHtml);
6045: exception
6046: when others then
6047: WF_CORE.Context('WF_MAIL','CloseLOB');
6048: raise;
6049: end;
6050:
6051:
6058: -- piece_value - the data stored in the global content_array table.
6059: function FetchUrlContent(piece_count in number,
6060: error_result in out NOCOPY varchar2) return varchar2 as
6061: begin
6062: return(wf_mail.content_array(piece_count));
6063:
6064: exception
6065: when NO_DATA_FOUND then
6066: return('NO_DATA_FOUND');
6066: return('NO_DATA_FOUND');
6067:
6068: when others then
6069: error_result := sqlerrm;
6070: wf_core.context('WF_MAIL', 'FetchUrlContent', piece_count);
6071:
6072: end FetchUrlContent;
6073:
6074:
6098:
6099: url_pieces := utl_http.request_pieces(url);
6100:
6101: for l_rec_num in 1..url_pieces.count loop
6102: wf_mail.content_array(l_rec_num) := url_pieces(l_rec_num);
6103: piece_count := l_rec_num;
6104: end loop;
6105:
6106: exception
6107:
6108: when utl_http.init_failed then
6109: error_result := 'UTL_HTTP.INIT_FAILED';
6110: error_result := error_result || sqlerrm;
6111: wf_core.context('WF_MAIL', 'GetUrlContent', url);
6112:
6113: when utl_http.request_failed then
6114: error_result := 'UTL_HTTP.REQUEST_FAILED';
6115: error_result := error_result || sqlerrm;
6112:
6113: when utl_http.request_failed then
6114: error_result := 'UTL_HTTP.REQUEST_FAILED';
6115: error_result := error_result || sqlerrm;
6116: wf_core.context('WF_MAIL', 'GetUrlContent', url);
6117:
6118: when no_program_unit then
6119: wf_core.context('WF_MAIL', 'GetUrlContent', url);
6120: raise;
6115: error_result := error_result || sqlerrm;
6116: wf_core.context('WF_MAIL', 'GetUrlContent', url);
6117:
6118: when no_program_unit then
6119: wf_core.context('WF_MAIL', 'GetUrlContent', url);
6120: raise;
6121: when others then
6122: error_result := sqlerrm;
6123: wf_core.context('WF_MAIL', 'GetUrlContent', url);
6119: wf_core.context('WF_MAIL', 'GetUrlContent', url);
6120: raise;
6121: when others then
6122: error_result := sqlerrm;
6123: wf_core.context('WF_MAIL', 'GetUrlContent', url);
6124:
6125: end GetUrlContent;
6126:
6127: -- GetDocContent - get Document content
6152: doccontent := Wf_Notification.GetAttrDoc(nid, docattrname, disptype);
6153:
6154: exception
6155: when no_program_unit then
6156: wf_core.context('WF_MAIL', 'GetDocContent', docattrname);
6157: raise;
6158: when others then
6159: -- First look for a wf_core error.
6160: wf_core.get_error(err_name, err_message, err_stack);
6164: err_message := sqlerrm;
6165: end if;
6166:
6167: error_result := err_message;
6168: wf_core.context('WF_MAIL', 'GetDocContent', docattrname);
6169:
6170: end GetDocContent;
6171:
6172: -- GetLOBDocContent - get Document content
6192: err_message varchar2(2000);
6193: err_stack varchar2(4000);
6194: begin
6195:
6196: Wf_Mail.GetLOBDocContent(nid, docattrname, disptype, doctype, error_result);
6197:
6198: exception
6199: when no_program_unit then
6200: wf_core.context('WF_MAIL', 'oldGetLOBDocContent', docattrname);
6196: Wf_Mail.GetLOBDocContent(nid, docattrname, disptype, doctype, error_result);
6197:
6198: exception
6199: when no_program_unit then
6200: wf_core.context('WF_MAIL', 'oldGetLOBDocContent', docattrname);
6201: raise;
6202: when others then
6203: -- First look for a wf_core error.
6204: wf_core.get_error(err_name, err_message, err_stack);
6208: err_message := sqlerrm;
6209: end if;
6210:
6211: error_result := err_message;
6212: wf_core.context('WF_MAIL', 'oldGetDocLOBContent', docattrname);
6213: end GetLOBDocContent;
6214:
6215: -- GetLOBDocContent - get Document content
6216: --
6268:
6269:
6270: exception
6271: when no_program_unit then
6272: wf_core.context('WF_MAIL', 'GetLOBDocContent', docattrname);
6273: raise;
6274: when others then
6275: -- First look for a wf_core error.
6276: wf_core.get_error(err_name, err_message, err_stack);
6280: err_message := sqlerrm;
6281: end if;
6282:
6283: error_result := err_message;
6284: wf_core.context('WF_MAIL', 'GetDocLOBContent', docattrname);
6285:
6286: end GetLOBDocContent;
6287:
6288: -- RemoveSpace (PRIVATE)
6361: end loop;
6362: return tmpBody;
6363: exception
6364: when others then
6365: wf_core.context('WF_MAIL', 'RemoveSpace');
6366: raise;
6367: end RemoveSpace;
6368:
6369: -- PutMessage
6456: end loop;
6457:
6458: -- Bug 2375920 get the signature policy for the notification and
6459: -- raise error is the policy is invalid
6460: Wf_Mail.GetSignaturePolicy(nid, n_sig_policy);
6461: if (n_sig_policy is not NULL and upper(n_sig_policy) <> 'DEFAULT') then
6462: if(upper(n_sig_policy) = 'PSIG_ONLY') then
6463: wf_core.context('WF_MAIL', 'PutMessage', to_char(nid), node, from_addr);
6464: wf_core.token('NID', to_char(nid));
6459: -- raise error is the policy is invalid
6460: Wf_Mail.GetSignaturePolicy(nid, n_sig_policy);
6461: if (n_sig_policy is not NULL and upper(n_sig_policy) <> 'DEFAULT') then
6462: if(upper(n_sig_policy) = 'PSIG_ONLY') then
6463: wf_core.context('WF_MAIL', 'PutMessage', to_char(nid), node, from_addr);
6464: wf_core.token('NID', to_char(nid));
6465: wf_core.raise('WFRSPR_PWD_SIGNATURE');
6466: else
6467: wf_core.context('WF_MAIL', 'PutMessage', to_char(nid), node, from_addr);
6463: wf_core.context('WF_MAIL', 'PutMessage', to_char(nid), node, from_addr);
6464: wf_core.token('NID', to_char(nid));
6465: wf_core.raise('WFRSPR_PWD_SIGNATURE');
6466: else
6467: wf_core.context('WF_MAIL', 'PutMessage', to_char(nid), node, from_addr);
6468: wf_core.token('NID', to_char(nid));
6469: wf_core.token('POLICY', n_sig_policy);
6470: wf_core.raise('WFMLR_INVALID_SIG_POLICY');
6471: end if;
6594: -- Complete the response.
6595: if response then
6596: Wf_Notification.Respond(nid, NULL, 'email:'||from_addr);
6597: else
6598: wf_core.context('WF_MAIL', 'PutMessage', to_char(nid), node, from_addr);
6599: wf_core.raise('WFRSPR_NORESPONSE');
6600: end if;
6601:
6602: exception
6600: end if;
6601:
6602: exception
6603: when no_program_unit then
6604: wf_core.context('WF_MAIL','PutMessage', to_char(nid));
6605: raise;
6606: when OTHERS then
6607: wf_core.context('WF_MAIL','PutMessage', to_char(nid));
6608: -- Save error message and set status to INVALID so mailer will
6603: when no_program_unit then
6604: wf_core.context('WF_MAIL','PutMessage', to_char(nid));
6605: raise;
6606: when OTHERS then
6607: wf_core.context('WF_MAIL','PutMessage', to_char(nid));
6608: -- Save error message and set status to INVALID so mailer will
6609: -- bounce an "invalid reply" message to sender.
6610: HandleResponseError(nid, lk_type, lk_meaning, error_result);
6611: end PutMessage;
6690: end;
6691:
6692: -- Bug 2375920 get the signature policy for the notification and
6693: -- raise error is the policy is invalid
6694: Wf_Mail.GetSignaturePolicy(nid, n_sig_policy);
6695: if (n_sig_policy is not NULL and upper(n_sig_policy) <> 'DEFAULT') then
6696: if(upper(n_sig_policy) = 'PSIG_ONLY') then
6697: wf_core.context('WF_MAIL', 'PutDirectMessage', to_char(nid), node, from_addr);
6698: wf_core.token('NID', to_char(nid));
6693: -- raise error is the policy is invalid
6694: Wf_Mail.GetSignaturePolicy(nid, n_sig_policy);
6695: if (n_sig_policy is not NULL and upper(n_sig_policy) <> 'DEFAULT') then
6696: if(upper(n_sig_policy) = 'PSIG_ONLY') then
6697: wf_core.context('WF_MAIL', 'PutDirectMessage', to_char(nid), node, from_addr);
6698: wf_core.token('NID', to_char(nid));
6699: wf_core.raise('WFRSPR_PWD_SIGNATURE');
6700: else
6701: wf_core.context('WF_MAIL', 'PutDirectMessage', to_char(nid), node, from_addr);
6697: wf_core.context('WF_MAIL', 'PutDirectMessage', to_char(nid), node, from_addr);
6698: wf_core.token('NID', to_char(nid));
6699: wf_core.raise('WFRSPR_PWD_SIGNATURE');
6700: else
6701: wf_core.context('WF_MAIL', 'PutDirectMessage', to_char(nid), node, from_addr);
6702: wf_core.token('NID', to_char(nid));
6703: wf_core.token('POLICY', n_sig_policy);
6704: wf_core.raise('WFMLR_INVALID_SIG_POLICY');
6705: end if;
6765: -- Complete the response.
6766: if response then
6767: Wf_Notification.Respond(nid, NULL, 'email:'||from_addr);
6768: else
6769: wf_core.context('WF_MAIL', 'PutDirectMessage', to_char(nid),
6770: node, from_addr);
6771: wf_core.raise('WFRSPR_NORESPONSE');
6772: end if;
6773: exception
6771: wf_core.raise('WFRSPR_NORESPONSE');
6772: end if;
6773: exception
6774: when no_program_unit then
6775: wf_core.context('WF_MAIL','PutDirectMessage', to_char(nid));
6776: raise;
6777: when OTHERS then
6778: wf_core.context('WF_MAIL','PutDirectMessage', to_char(nid));
6779: -- Save error message and set status to INVALID so mailer will
6774: when no_program_unit then
6775: wf_core.context('WF_MAIL','PutDirectMessage', to_char(nid));
6776: raise;
6777: when OTHERS then
6778: wf_core.context('WF_MAIL','PutDirectMessage', to_char(nid));
6779: -- Save error message and set status to INVALID so mailer will
6780: -- bounce an "invalid reply" message to sender.
6781: HandleResponseError(nid, lk_type, lk_meaning, error_result);
6782: end PutDirectMessage;
6855: wf_notification.UpdateInfo2(nid, to_user, from_addr, comment);
6856: end if;
6857: exception
6858: when no_program_unit then
6859: wf_core.context('WF_MAIL','PutMoreInfoRequest', to_char(nid));
6860: raise;
6861: when OTHERS then
6862: wf_core.context('WF_MAIL','PutMoreInfoRequest', to_char(nid));
6863: -- Save error message and set status to INVALID so mailer will
6858: when no_program_unit then
6859: wf_core.context('WF_MAIL','PutMoreInfoRequest', to_char(nid));
6860: raise;
6861: when OTHERS then
6862: wf_core.context('WF_MAIL','PutMoreInfoRequest', to_char(nid));
6863: -- Save error message and set status to INVALID so mailer will
6864: -- bounce an "invalid reply" message to sender.
6865: HandleResponseError(nid, dummy, dummy, error_result);
6866: end PutMoreInfoRequest;
6938: to_user := NULL;
6939: wf_notification.UpdateInfo2(nid, to_user, from_addr, comment);
6940: exception
6941: when no_program_unit then
6942: wf_core.context('WF_MAIL','PutMoreInfoMessage', to_char(nid));
6943: raise;
6944: when OTHERS then
6945: wf_core.context('WF_MAIL','PutMoreInfoMessage', to_char(nid));
6946: -- Save error message and set status to INVALID so mailer will
6941: when no_program_unit then
6942: wf_core.context('WF_MAIL','PutMoreInfoMessage', to_char(nid));
6943: raise;
6944: when OTHERS then
6945: wf_core.context('WF_MAIL','PutMoreInfoMessage', to_char(nid));
6946: -- Save error message and set status to INVALID so mailer will
6947: -- bounce an "invalid reply" message to sender.
6948: HandleResponseError(nid, dummy, dummy, error_result);
6949: end PutMoreInfoMessage;
7024: buffer := '';
7025: end if;
7026: exception
7027: when no_program_unit then
7028: wf_core.context('WF_MAIL', 'GetURLAttachment', to_char(nid));
7029: raise;
7030: when others then
7031: -- First look for a wf_core error.
7032: wf_core.get_error(err_name, err_message, err_stack);
7036: err_message := sqlerrm;
7037: end if;
7038:
7039: error_result := err_message;
7040: wf_core.context('WF_MAIL', 'GetURLAttachment', to_char(nid));
7041:
7042: end;
7043:
7044:
7173: and rownum < 2;
7174: end;
7175: exception
7176: when OTHERS then
7177: wf_core.context('WF_MAIL', 'GetCharset', lang, terr);
7178: raise;
7179: end GetCharset;
7180:
7181: -- GetSessionLanguage
7207: codeset := substr(nls_str, dot+1);
7208:
7209: exception
7210: when others then
7211: wf_core.context('WF_MAIL', 'GetSessionLanguage');
7212: raise;
7213: end GetSessionLanguage;
7214:
7215: -- Bug 2375920
7278: l_sec_policy varchar2(100);
7279: l_email_allowed varchar2(1);
7280: begin
7281: -- Get security policy for the notification
7282: Wf_Mail.GetSecurityPolicy(p_nid, l_sec_policy);
7283:
7284: -- If the policy is not seeded, default it to Y to allow email
7285: begin
7286: SELECT email_allowed
7312: end if;
7313:
7314: exception
7315: when others then
7316: Wf_Core.Context('Wf_Mail', 'ProcessSecurityPolicy', to_char(p_nid));
7317: raise;
7318: end ProcessSecurityPolicy;
7319:
7320: -- Set_FYI_Flag (Private)
7374:
7375: begin
7376:
7377: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
7378: wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.wf_mail.Get_Ntf_Language.BEGIN',
7379: 'User preferences. LANG {'||p_language||'} TERR {'||p_territory||'}');
7380: end if;
7381:
7382: -- Get notification's language preference
7477:
7478: -- If at least one of the value is null, use the base NLS setting
7479: if (p_language is null or p_territory is null) then
7480: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
7481: wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.wf_mail.Get_Ntf_Language.NULL',
7482: 'Using base NLS setting because p_language is {'||p_language||
7483: '} and p_territory is {'||p_territory||'}');
7484: end if;
7485:
7483: '} and p_territory is {'||p_territory||'}');
7484: end if;
7485:
7486: -- <
7487: -- WF_MAIL.GetSessionLanguage(l_base_lang, l_base_terr, l_base_codeset);
7488: -- Note: we should AVOID to call below API here
7489: -- use global variables instead .
7490: WF_NOTIFICATION_UTIL.getNLSContext(
7491: l_base_lang ,
7507:
7508: end if;
7509:
7510: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
7511: wf_log_pkg.string(wf_log_pkg.level_procedure, 'wf.plsql.wf_mail.Get_Ntf_Language.END',
7512: 'Email Notification LANG {'||p_language||'} TERR {'||p_territory||'}'||
7513: ' CODESET {'||p_codeset||'}');
7514: end if;
7515:
7514: end if;
7515:
7516: exception
7517: when others then
7518: Wf_Core.Context('Wf_Mail', 'Get_Ntf_Language', to_char(p_nid), p_language, p_territory);
7519: raise;
7520: end Get_Ntf_Language;
7521:
7522: --
7586: begin
7587:
7588: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
7589: wf_log_pkg.string(WF_LOG_PKG.level_procedure,
7590: 'wf.plsql.WF_MAIL.send',
7591: 'BEGIN');
7592: end if;
7593:
7594: -- Test the FYI flag. If set, then treat this message as FYI
7604: -- Recipients required
7605: if (p_recipient_list is null) then
7606: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
7607: wf_log_pkg.string(WF_LOG_PKG.level_procedure,
7608: 'wf.plsql.WF_MAIL.send',
7609: 'Recipient List is empty. ');
7610: end if;
7611:
7612: wf_core.raise('WFMLR_NO_RECIPIENTS');
7615: -- At least subject or body is required
7616: if (p_subject is null and (p_message is null or dbms_lob.GetLength(p_message)=0)) then
7617: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
7618: wf_log_pkg.string(WF_LOG_PKG.level_procedure,
7619: 'wf.plsql.WF_MAIL.send',
7620: 'Subject and Message Contents are blank or null. ');
7621: end if;
7622:
7623: wf_core.raise('WFMLR_MSG_INCOMPLETE');
7626: -- For all e-mails, respone or FYI, module name is required
7627: if (p_module is null) then
7628: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
7629: wf_log_pkg.string(WF_LOG_PKG.level_procedure,
7630: 'wf.plsql.WF_MAIL.send',
7631: 'Message module is not specified. ');
7632: end if;
7633:
7634: wf_core.raise('WFMLR_NO_MODULE');
7643: wf_xml.AddElementAttribute('maxcount', '1', l_attrlist);
7644: l_pos := wf_xml.NewLOBTag(l_msg_doc, l_pos, 'NOTIFICATIONGROUP', '', l_attrlist);
7645: l_attrlist.DELETE;
7646:
7647: l_nodename := WF_MAILER_PARAMETER.GetValueForCorr(p_module, 'NODENAME');
7648:
7649: -- Id string required for response required alerts.
7650: -- We assume that a null id string constitutes a FYI message
7651: if (p_idstring is not null and l_fyi_flag = false) then
7872: wf_temp_lob.releaseLob(g_LOBTable, l_messageIdx);
7873:
7874: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
7875: wf_log_pkg.string(WF_LOG_PKG.level_procedure,
7876: 'wf.plsql.WF_MAIL.send',
7877: 'END');
7878: end if;
7879:
7880: exception
7884: wf_temp_lob.releaseLob(g_LOBTable, l_messageIdx);
7885: END if;
7886:
7887:
7888: wf_core.context('Wf_Mail', 'Send', p_idstring, p_subject);
7889: raise;
7890: end Send;
7891:
7892:
7968: BEGIN
7969:
7970: IF(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) THEN
7971: wf_log_pkg.string(wf_log_pkg.level_procedure,
7972: 'wf.plsql.WF_MAIL.SendMoreInfoResponseWarning', 'BEGIN');
7973: END IF;
7974:
7975: -- Get details of last question asked from WF_COMMENTS table and
7976: -- Get role language, territory and codeset details.
8071: when NO_DATA_FOUND then
8072: wf_core.raise('WFCORE_NO_MESSAGE');
8073: end;
8074:
8075: l_nodename := wf_mailer_parameter.getvalueforcorr(
8076: l_msg_type||':'||l_msg_name,'NODENAME');
8077:
8078: l_idstr := '';
8079:
8285:
8286:
8287: if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
8288: wf_log_pkg.string(wf_log_pkg.level_procedure,
8289: 'wf.plsql.WF_MAIL.SendMoreInfoResponseWarning',
8290: 'Initializing ' || wf_xml.wf_ntf_send_message || ' event');
8291: end if;
8292:
8293: wf_event_t.initialize(l_event);
8310: wf_temp_lob.releaselob(g_lobtable, l_messageidx);
8311:
8312: if(wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
8313: wf_log_pkg.string(wf_log_pkg.level_procedure,
8314: 'wf.plsql.WF_MAIL.SendMoreInfoResponseWarning', 'END');
8315: end if;
8316:
8317: exception
8318: when others then
8385: close_html in varchar2)
8386: is
8387: begin
8388: if open_text is null then
8389: wf_mail.g_open_text_delimiter := '"';
8390: else
8391: wf_mail.g_open_text_delimiter := open_text;
8392: end if;
8393:
8387: begin
8388: if open_text is null then
8389: wf_mail.g_open_text_delimiter := '"';
8390: else
8391: wf_mail.g_open_text_delimiter := open_text;
8392: end if;
8393:
8394: if close_text is null then
8395: wf_mail.g_close_text_delimiter := '"';
8391: wf_mail.g_open_text_delimiter := open_text;
8392: end if;
8393:
8394: if close_text is null then
8395: wf_mail.g_close_text_delimiter := '"';
8396: else
8397: wf_mail.g_close_text_delimiter := close_text;
8398: end if;
8399:
8393:
8394: if close_text is null then
8395: wf_mail.g_close_text_delimiter := '"';
8396: else
8397: wf_mail.g_close_text_delimiter := close_text;
8398: end if;
8399:
8400: if open_html is null then
8401: wf_mail.g_open_html_delimiter := '''';
8397: wf_mail.g_close_text_delimiter := close_text;
8398: end if;
8399:
8400: if open_html is null then
8401: wf_mail.g_open_html_delimiter := '''';
8402: else
8403: wf_mail.g_open_html_delimiter := open_html;
8404: end if;
8405:
8399:
8400: if open_html is null then
8401: wf_mail.g_open_html_delimiter := '''';
8402: else
8403: wf_mail.g_open_html_delimiter := open_html;
8404: end if;
8405:
8406: if open_html is null then
8407: wf_mail.g_close_html_delimiter := '''';
8403: wf_mail.g_open_html_delimiter := open_html;
8404: end if;
8405:
8406: if open_html is null then
8407: wf_mail.g_close_html_delimiter := '''';
8408: else
8409: wf_mail.g_close_html_delimiter := close_html;
8410: end if;
8411:
8405:
8406: if open_html is null then
8407: wf_mail.g_close_html_delimiter := '''';
8408: else
8409: wf_mail.g_close_html_delimiter := close_html;
8410: end if;
8411:
8412: end SetResponseDelimiters;
8413:
8411:
8412: end SetResponseDelimiters;
8413:
8414:
8415: end WF_MAIL;