REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \ REM dbdrv: checkfile:~PROD:~PATH:~FILE REM +=======================================================================+ REM | Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA | REM | All rights reserved. | REM +=======================================================================+ REM | FILENAME REM | wfmlrb.pls REM | REM | DESCRIPTION REM | Mailer REM | REM | NOTES: REM | 08 Jun 2000: use substrb when truncating a string into a buffer REM | use substr for string manipulation/concatination REM | MODIFICATION LOG: REM | 01 JAN 2002: JWSMITH BUG 2001012 - Increased role, to_name, REM | n_to_role to varchar2(320) REM | 09 FEB 2002: CTILLEY BUG 2202392 - Check status of Notification REM | and only process if it is currently open. Change in REM | PutMessage and PutDirectMessage. REM | 05 JUN 2002: CTILLEY BUG 2395898 - Only append the response if REM | truncated and the Response attribute exists in template. REM | 30 JAN 2003: SACSHARM BUG 2774891 - Priority values are opposite of REM | true value REM | 23 JUL 2003: SMAYZE Merged in the LOB Pool enhancement REM +=======================================================================+ SET VERIFY OFF WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; create or replace package body WF_MAIL as /* $Header: wfmlrb.pls 26.134 2005/06/09 13:04:17 vshanmug ship $ */ -- -- Constants -- chr_indent varchar2(8) := ' '; -- 8 spaces (virtual tab) chr_indentsize pls_integer := 8; -- Number of char spaces in a tab -- 3432204 The LOBLineBreak algorithm as been altered. The line wrapping -- has been increased to allow for the horizontal line in text/plain -- notifications -- wf_linelen pls_integer := 72; -- Max line length for word wrap wf_linelen pls_integer := 73; -- Max line length for word wrap FND_WFNTF_DETAILS NUMBER := 1014409; g_timezoneName varchar2(80) := ''; g_direct_response boolean := FALSE; g_send_accesskey boolean := TRUE; g_autoclose_fyi boolean := TRUE; g_template varchar2(30); -- internal name for a message g_fyi boolean := FALSE; -- CLOB Processing Variables --g_text_message CLOB; -- LOB locator for text body message --g_html_message CLOB; -- LOB locator for HTML body message --g_attachment CLOB; -- LOB locator for attachments -- Indexes for CLOB from the pool g_LOBTable wf_temp_lob.wf_temp_lob_table_type; g_text_messageIdx pls_integer; g_html_messageIdx pls_integer; g_attachmentIdx pls_integer; g_text_chunk pls_integer; g_html_chunk pls_integer; -- More Info feature g_moreinfo varchar2(3) := NULL; g_to_role varchar2(320); -- GLOBAL Package level varaibles that contain static data. g_ntfHistory varchar2(30); g_ntfActionHistory varchar2(50); g_newLine varchar2(1) := wf_core.newLine; g_tab varchar2(1) := wf_core.tab; g_moreInfoAPrompt varchar2(200); g_moreInfoAnswer varchar2(200); g_moreInfoQPrompt varchar2(200); g_moreInfoSubject varchar2(200); g_moreInfoSubmit varchar2(200); g_moreInfoQuestion varchar2(200); g_moreInfoFrom varchar2(200); g_moreInfoRequested varchar2(200); g_moreInfoRequestee varchar2(4000); g_webAgent varchar2(200) := wf_core.translate('WF_WEB_AGENT'); g_wfmonId varchar2(60); g_to varchar2(60); g_from varchar2(60); g_beginDate varchar2(100); g_dueDate2 varchar2(60); g_notificationId varchar2(60); g_priority varchar2(60); g_dueDate varchar2(60); g_invalidRemarks varchar2(400); g_forExample varchar2(200); g_soOn varchar2(60); g_none varchar2(200); g_truncate varchar2(200); g_noResult varchar2(200); g_install varchar2(60) := wf_core.translate('WF_INSTALL'); g_ntfDocText varchar2(30) := wf_notification.doc_text; g_ntfDocHtml varchar2(30) := wf_notification.doc_html; g_Id varchar2(30); g_isFwkNtf boolean; g_sig_required varchar2(1); g_fwk_flavor varchar2(255); g_email_flavor varchar2(255); g_render varchar2(255); -- -- HTML Table defaults -- table_width varchar2(8) := '100%'; table_border varchar2(2) := '0'; table_cellpadding varchar2(2) := '0'; table_cellspacing varchar2(2) := '0'; table_bgcolor varchar2(7) := 'white'; th_bgcolor varchar2(7) := '#ffffff'; th_fontcolor varchar2(7) := '#000000'; th_fontface varchar2(80) := 'Arial, Helvetica, Geneva, sans-serif'; th_fontsize varchar2(2) := '2'; td_bgcolor varchar2(7) := '#ffffff'; td_fontcolor varchar2(7) := 'black'; td_fontface varchar2(80) := 'Arial, Helvetica, Geneva, sans-serif'; td_fontsize varchar2(2) := '2'; -- UpdateStatus -- Update mail status and close any notification with no response. -- Handle error. -- IN -- notification id -- status -- error name (null if error is in WF_CORE) procedure UpdateStatus( nid in number, status in varchar2, error_name in varchar2) is l_autoclose VARCHAR(1); begin if wf_mail.autoclose_fyi then l_autoclose := 'Y'; else l_autoclose := 'N'; end if; wf_mail.UpdateStatus2(nid => UpdateStatus.nid, status => UpdateStatus.status, autoclose => l_autoclose, error_name => UpdateStatus.error_name, external_error => null); exception when others then wf_core.context('WF_MAIL', 'UpdateStatus', to_char(nid), UpdateStatus.status, UpdateStatus.error_name); raise; end UpdateStatus; -- UpdateStatus2 -- Update mail status and close any notification with no response. -- Handle error. -- IN -- nid notification id -- status Status to set the notification -- autoclose Flag to specify whether the notification should be closed -- automitically -- error name (null if error is in WF_CORE) -- external_error Any error message that can not be reflected or captured -- through the wf_core.context facilty ie Java. procedure UpdateStatus2( nid in number, status in varchar2, autoclose in varchar2, error_name in varchar2, external_error in varchar2) is l_mType VARCHAR2(8); l_currState VARCHAR2(8); updateState boolean; l_role varchar2(230); parameterList wf_parameter_list_t; begin select message_type, mail_status, recipient_role into l_mType, l_currState, l_role from wf_notifications where notification_id = nid; -- If the prevsious state was FAILED, then preserve this state. -- A new status of null means that a null message was sent. -- This does not cover the case for a pref deliberately set to -- query or to summary (even from summary). if l_currState = 'FAILED' and (status is null or status = '') then updateState := false; else updateState := true; end if; if updateState then -- This notification had already locked by wfmail() in the mailer update WF_NOTIFICATIONS set MAIL_STATUS = UpdateStatus2.status where NOTIFICATION_ID = nid; end if; if (UpdateStatus2.status = 'ERROR') then WF_MAIL.HandleSendError(nid => UpdateStatus2.nid, status => UpdateStatus2.status, error_name => UpdateStatus2.error_name, external_error => UpdateStatus2.external_error); elsif (UpdateStatus2.status = 'FAILED') then -- Here we only raise an event and leave the message as is. -- oracle.apps.wf.notification.send.failure parameterlist := wf_parameter_list_t(); wf_event.AddParameterToList('NOTIFICATION_ID',nid,parameterlist); wf_event.AddParameterToList('ROLE',l_role,parameterlist); wf_event.AddParameterToList('STATUS',status,parameterlist); wf_event.AddParameterToList('ERROR_NAME',error_name, parameterlist); wf_event.AddParameterToList('EXTERNAL_ERROR',external_error, parameterlist); wf_event.addParameterToList('Q_CORRELATION_ID', l_mType, parameterlist); --Raise the event wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send.failure', p_event_key => l_role, p_parameters => parameterlist); elsif UpdateStatus2.status = 'UNAVAIL' then -- 4031628 The UNAVAIL mail_status has never been used, even -- in the C mailer. It only happens when there is a match on -- the pattern/actions. Here we can start to raise an event -- that indicates that a recipient is not available to respond -- to the notification. parameterlist := wf_parameter_list_t(); wf_event.AddParameterToList('NOTIFICATION_ID', UpdateStatus2.nid, parameterlist); wf_event.AddParameterToList('STATUS', UpdateStatus2.status, parameterlist); wf_event.addParameterToList('Q_CORRELATION_ID', l_mType, parameterlist); --Raise the event wf_event.Raise(p_event_name=>'oracle.apps.wf.notification.receive.unavail', p_event_key => to_char(nid), p_parameters => parameterlist); elsif (UpdateStatus2.status = 'SENT') then -- The default behaviour is to leave the notification open -- unless there is a routing rule to tell otherwise. -- This is contrary to the behaviour of previous releases -- and will be readdressed a little later on. -- close this notification if there is no response update WF_NOTIFICATIONS N set N.STATUS = 'CLOSED', N.END_DATE = sysdate where N.NOTIFICATION_ID = nid and not exists (select NULL from WF_MESSAGE_ATTRIBUTES MA where MA.MESSAGE_TYPE = N.MESSAGE_TYPE and MA.MESSAGE_NAME = N.MESSAGE_NAME and MA.SUBTYPE = 'RESPOND') and (UpdateStatus2.autoclose = 'Y' and not exists (select null from wf_routing_rules r where (r.message_type = n.message_type or r.message_type = '*') and (r.message_name = n.message_name or r.message_name = '*') and r.action = 'FYIOPEN' and r.role = n.recipient_role and sysdate between nvl(begin_date, sysdate -1) and nvl(end_date, sysdate + 1))); end if; exception when others then wf_core.context('WF_MAIL', 'UpdateStatus2', to_char(nid), UpdateStatus2.status, UpdateStatus2.autoclose, UpdateStatus2.error_name); raise; end UpdateStatus2; -- ResetFailed -- Update mail status from FAILED to MAIL for open notifications. -- IN -- Queue number on which to process procedure ResetFailed(p_queue varchar2) is l_nid number; l_recipient WF_NOTIFICATIONS.RECIPIENT_ROLE%TYPE; l_status varchar2 (8); l_timeout boolean; l_error_result varchar2(2000); begin -- Dequeue from the exception queue -- and re-enqueue the message. l_timeout := FALSE; wf_xml.setFirstMessage('TRUE'); while not l_timeout loop wf_xml.GetExceptionMessage(p_queue, l_nid, l_recipient, l_status, l_timeout, l_error_result); if ( not l_timeout ) then update WF_NOTIFICATIONS N set N.MAIL_STATUS = 'MAIL' where N.NOTIFICATION_ID = l_nid; -- wf_xml.EnqueueNotification(l_nid); end if; end loop; commit; exception when others then wf_core.context('WF_MAIL', 'ResetFailed', p_queue); raise; end ResetFailed; -- HandleSendError (PRIVATE) -- Call any callback in error mode if error occurs in sending mail. -- IN -- notification id -- mailer send status -- error name (null if error is in WF_CORE) -- external_error Any error message that can not be reflected or captured -- through the wf_core.context facilty ie Java. procedure HandleSendError( nid in number, status in varchar2, error_name in varchar2, external_error in varchar2) is cb varchar2(240); ctx varchar2(2000); role varchar2(320); -- Dynamic sql stuff sqlbuf varchar2(120); tvalue varchar2(4000) := ''; nvalue number := ''; dvalue date := ''; l_dummy varchar2(1); begin -- Get the callback function. select CALLBACK, CONTEXT, RECIPIENT_ROLE into cb, ctx, role from WF_NOTIFICATIONS where NOTIFICATION_ID = nid; -- If there is no callback, just clear any error and return. if (cb is null) then wf_core.clear; return; end if; -- Put supplied error message on stack, if any if (error_name is not null) then begin wf_core.token('NID', to_char(nid)); wf_core.token('STATUS', status); wf_core.token('ROLE', role); if external_error is not null then wf_core.token('EXTERNAL_ERROR', external_error); end if; wf_core.raise(error_name); exception when others then null; end; end if; -- Put default error message on stack, if none exists if (wf_core.error_name is null) then begin wf_core.token('NID', to_char(nid)); if external_error is not null then wf_core.token('EXTERNAL_ERROR', external_error); end if; wf_core.raise('WFMAIL_GENERIC'); exception when others then null; end; end if; -- Call the CB in error mode -- ### cb is from table -- BINDVAR_SCAN_IGNORE sqlbuf := 'begin '||cb|| '(:p1, :p2, :p3, :p4, :p5, :p6, :p7); end;'; execute immediate sqlbuf using in 'ERROR', in ctx, in l_dummy, in l_dummy, in out tvalue, in out nvalue, in out dvalue; -- Clear the error from the stack. wf_core.clear; exception when others then null; end HandleSendError; -- Disable_Recipient_Ntf_pref -- Updates the recipient of a notification to DISABLED where -- there has been a failure to deliver to their email address. -- This function is triggered by the oracle.apps.wf.notification.send.failure -- event. function Disable_Recipient_Ntf_Pref(p_subscription_guid in raw, p_event in out NOCOPY WF_EVENT_T) return varchar2 is role varchar2(320); recipient varchar2(320); pref varchar2(8); params wf_parameter_list_t; errorName varchar2(320); externError varchar2(4000); errMessage varchar2(4000); errStack varchar2(32000); tokens wf_mail_util.parserStack_t; tk pls_integer; dummy varchar2(2000); dummyNumber number; orig_system varchar2(30); orig_system_id number; paramList wf_parameter_list_t; invalidRoleList varchar2(32000); reasonList varchar2(32000); invalidRoleCount pls_integer := 0; begin if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(WF_LOG_PKG.level_procedure, 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref', 'BEGIN'); end if; role := p_event.getEventKey(); params := p_event.getParameterList(); errorName := wf_event.getValueForParameter('ERROR_NAME', params); externError := wf_event.getValueForParameter('EXTERNAL_ERROR', params); if errorName = 'WFMLRSND_FAILED_UNDELIVERABLE' then -- Where the notification was at all undeliverable, then there -- will be a comma sperated list of recipients. Each one of these -- recipients will have to be disabled. -- The format of the list is {{addrspec1},{addrspec2},...,{addrspecN}}. tokens := wf_mail_util.strParser(externError, '{}'); paramList := wf_parameter_list_t(null); invalidRoleList := ''; reasonList := ''; invalidRoleCount := 0; for tk in 1..tokens.COUNT loop if tokens(tk) is not null and tokens(tk) <> ',' then if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(WF_LOG_PKG.level_statement, 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref', 'TOKEN ['||tokens(tk)||']'); end if; Wf_Directory.GetRoleInfoMail(role => tokens(tk), display_name => dummy, email_address => dummy, notification_preference => pref, language => dummy, territory => dummy, orig_system => orig_system, orig_system_id => orig_system_id, installed_flag => dummy); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(WF_LOG_PKG.level_statement, 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref', 'PREFERENCE ['||pref||']'); end if; if pref is not null and pref not in ('DISABLED', 'QUERY', 'SUMMARY', 'SUMHTML') then if orig_system in ('FND_USR', 'PER') then FND_PREFERENCE.put(p_user_name => tokens(tk), p_module_name => 'WF', p_pref_name => 'MAILTYPE', p_pref_value => 'DISABLED'); else paramList.DELETE; wf_event.AddParameterToList('USER_NAME', tokens(tk), paramList); wf_event.AddParameterToList('ORCLWORKFLOWNOTIFICATIONPREF', 'DISABLED', paramList); wf_event.AddParameterToList('RAISEERRORS', 'TRUE', paramList); begin wf_local_synch.propagate_user(p_orig_system => orig_system, p_orig_system_id => orig_system_id, p_attributes => paramList); exception when others then -- Should the propagate fail, then save the -- role to be used to update the ErrorStack with -- the failed role and the reason. wf_core.get_error(err_name => errorName, err_message => errMessage, err_stack => errStack); invalidRoleList := invalidRoleList||'{'||tokens(tk)|| ', '||errMessage||'}'; invalidRoleCount := invalidRoleCount + 1; end; end if; else if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(WF_LOG_PKG.level_statement, 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref', 'Unable to update notification preference for email ['|| tokens(tk)||']. Check for duplicates'); end if; end if; end if; end loop; end if; if invalidRoleCount > 0 then if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(WF_LOG_PKG.level_procedure, 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref', 'END WARNING'); end if; p_event.setErrorMessage(wf_core.translate('WFMLR_ROLE_UPDATE_FAILURE')); p_event.setErrorStack(substrb(invalidRoleList, 1, 4000)); return 'WARNING'; else if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(WF_LOG_PKG.level_procedure, 'wf.plsql.WF_MAIL.Disable_Recipient_Ntf_Pref', 'END SUCCESS'); end if; return 'SUCCESS'; end if; exception when others then wf_core.context('WF_MAIL','Disable_Recipient_Ntf_Pref', role, pref); raise; end Disable_Recipient_Ntf_Pref; -- GetLovList (PRIVATE) -- Get Text Lov List -- Inputs: -- lk_type - lookup type, which lookup from table. -- Output: -- A list of valid lookup meanings, in the form -- meaning -- Example: LOOKUP_TYPE: YES_NO -- Returns: -- No -- Yes function GetLovList( lk_type in varchar2) return varchar2 is cursor c is select MEANING from WF_LOOKUPS where LOOKUP_TYPE = lk_type order by MEANING; buffer varchar2(32000); begin buffer := ''; -- -- Loop through selecting all lookups -- for curs in c loop -- Add lookup to end of buffer string. buffer := buffer||chr_indent||curs.meaning||g_newLine; end loop; return(buffer); exception when others then wf_core.context('WF_MAIL', 'GetLovList', lk_type); raise; end GetLovList; -- Bug# 2301881 -- FormatErrorMessage (PRIVATE) -- Gets the error message for an Invalid -- response and returns it formatted -- IN -- error message for name WFMLR_INVALID_LOOKUP -- -- OUT -- lookup type -- remarks with expected values procedure FormatErrorMessage(lk_type in varchar2, remarks out NOCOPY varchar2) is exp_values varchar2(1000); l_vstart number; l_vend number; l_value1 varchar2(100); l_value2 varchar2(100); begin remarks := g_invalidRemarks; -- get the values for the lookup type exp_values := GetLovList(lk_type); -- get the first two values for the lookup type l_vstart := instr(exp_values, g_newLine, 1); l_vend := instr(exp_values, g_newLine, l_vstart+1); l_value1 := trim(substr(exp_values, 1, l_vstart-1)); l_value2 := trim(substr(exp_values, l_vstart+1, l_vend-l_vstart-1)); remarks := remarks || ' (' || g_forExample || ' "' || l_value1 || '", "' || l_value2 || '", ' || g_soOn || ')'; exception when others then remarks := g_invalidRemarks; end FormatErrorMessage; -- HandleResponseError (PRIVATE) handle exception in response -- -- Sets the MAIL_ERROR error message attribute, then sets the -- notification status to INVALID. -- -- IN -- notification id -- lookup type -- value found procedure HandleResponseError(nid in number, lk_type in varchar2, lk_meaning in varchar2, error_result in out NOCOPY varchar2) is errname varchar2(30); errmsg varchar2(2000); errstack varchar2(4000); no_program_unit exception; pragma exception_init(no_program_unit, -6508); -- Bug# 2301881 variables to handle new error message format value_found varchar2(1000); remarks varchar2(1000); parameterlist wf_parameter_list_t := wf_parameter_list_t(); role varchar2(30); group_id number; mType varchar2(8); begin -- First look for a wf_core error. wf_core.get_error(errname, errmsg, errstack); -- If no wf_core error look for a sql error. if (errname is null) then errmsg := sqlerrm; end if; -- Bug# 2301881 Format the error message if (lk_meaning is not null) then value_found := lk_meaning; else value_found := g_none; end if; if (lk_type is not null) then FormatErrorMessage(lk_type, remarks); else remarks := g_none; end if; error_result := errmsg; -- Set MAIL_ERROR_NAME attribute begin Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_NAME', errname); exception when no_program_unit then raise; when others then if (wf_core.error_name = 'WFNTF_ATTR') then Wf_Core.Clear; Wf_Notification.AddAttr(nid, 'MAIL_ERROR_NAME'); Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_NAME', errname); end if; end; -- Bug# 2301881 setting the values for message attributes -- Set MAIL_ERROR_MESSAGE attribute begin Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_MESSAGE', errmsg); exception when no_program_unit then raise; when others then if (wf_core.error_name = 'WFNTF_ATTR') then Wf_Core.Clear; Wf_Notification.AddAttr(nid, 'MAIL_ERROR_MESSAGE'); Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_MESSAGE', errmsg); end if; end; -- Set MAIL_VALUE_FOUND attribute begin Wf_Notification.SetAttrText(nid, 'MAIL_VALUE_FOUND', value_found); exception when no_program_unit then raise; when others then if (wf_core.error_name = 'WFNTF_ATTR') then Wf_Core.Clear; Wf_Notification.AddAttr(nid, 'MAIL_VALUE_FOUND'); Wf_Notification.SetAttrText(nid, 'MAIL_VALUE_FOUND', value_found); end if; end; -- Set MAIL_EXP_VALUES attribute begin Wf_Notification.SetAttrText(nid, 'MAIL_EXP_VALUES', remarks); exception when no_program_unit then raise; when others then if (wf_core.error_name = 'WFNTF_ATTR') then Wf_Core.Clear; Wf_Notification.AddAttr(nid, 'MAIL_EXP_VALUES'); Wf_Notification.SetAttrText(nid, 'MAIL_EXP_VALUES', remarks); end if; end; -- End Bug# 2301881 -- Set MAIL_ERROR_STACK attribute if (errstack is null) then errstack := ' '; end if; begin Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_STACK', errstack); exception when no_program_unit then raise; when others then if (wf_core.error_name = 'WFNTF_ATTR') then Wf_Core.Clear; Wf_Notification.AddAttr(nid, 'MAIL_ERROR_STACK'); Wf_Notification.SetAttrText(nid, 'MAIL_ERROR_STACK', errstack); end if; end; -- Set the mail_status to INVALID (mailer will pick this up) update WF_NOTIFICATIONS set MAIL_STATUS = 'INVALID' where NOTIFICATION_ID = nid; -- wf_xml.enqueueNotification(nid); select recipient_role, group_id, message_type into role, group_id, mType from wf_notifications where notification_id = nid; wf_event.AddParameterToList('NOTIFICATION_ID',nid,parameterlist); wf_event.AddParameterToList('ROLE',role,parameterlist); wf_event.AddParameterToList('GROUP_ID',nvl(group_id,nid),parameterlist); wf_event.addParameterToList('Q_CORRELATION_ID', mType, parameterlist); --Raise the event wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send', p_event_key => to_char(nid), p_parameters => parameterlist); exception when others then wf_core.context('WF_MAIL', 'HandleResponseError', nid); raise; end HandleResponseError; -- WordWrap (PRIVATE) -- Insert newlines to word wrap a line buffer. -- Inputs: -- text - text buffer -- indent - number of tabs to indent each line by -- Returns: -- buffer contents with newlines and tabs embedded function WordWrap( text in varchar2, indent in number) return varchar2 is buf varchar2(32000); -- Text buffer textlen pls_integer; -- Length of original 'text' argument newpos pls_integer; -- Current position in text space pls_integer; -- Position of space char to use for line break tabs varchar2(80); -- Tab string to prepend each line with maxlinelen pls_integer; -- Max line length allowed begin -- Ignore empty string if (text is null) then return(''); end if; buf := ''; newpos := 1; textlen := length(text); -- Build the indentation string. This is a string to pre-pend to every -- line, containing the requested number of tabs. -- (No, lpad/rpad won't work because initial string is null.) tabs := ''; for i in 1 .. indent loop tabs := substr(tabs||chr_indent, 1, 80); end loop; -- Adjust line length to account for indentation maxlinelen := wf_linelen - (chr_indentsize * indent); loop -- Exit when all remaining text fits on one line. exit when (textlen - newpos <= maxlinelen); -- If next newline is before maxlinelen, then the line is already -- short enough. Use the newline as the linebreak. space := instr(text, g_newLine, newpos, 1); if ((space = 0) or (space > (newpos + maxlinelen))) then -- Either no newlines, or next newline is beyond maxlinelen. -- Find the last space before maxlinelen. space := instr(text, ' ', -(textlen - newpos - maxlinelen), 1); if ((space = 0) or (space < newpos)) then -- No spaces on this line. -- Wrap at the next space or newline available. space := instr(replace(text, g_newLine, ' '), ' ', newpos + maxlinelen, 1); if (space = 0) then -- No spaces or newlines left at all, so no more wrapping -- can be done. Exit now and append any remaining text unaltered. exit; end if; end if; end if; -- Append the new line to the buffer followed by a newline, -- indented by requested number of tabs. buf := substrb(buf||tabs||rtrim(substr(text, newpos, space - newpos))|| g_newLine, 1, 32000); -- Start again after last space. newpos := space + 1; end loop; -- Append last partial line. buf := substrb(buf||tabs||rtrim(substr(text, newpos)), 1, 32000); return(buf); exception when others then wf_core.context('WF_MAIL', 'WordWrap', text, to_char(indent)); raise; end WordWrap; -- GetLovMeaning (PRIVATE) -- Return the displayed meaning of a lookup -- Inputs: -- lk_type - lookup type -- lk_code - lookup code -- Returns: -- lookup meaning function GetLovMeaning( lk_type in varchar2, lk_code in varchar2) return varchar2 is buf varchar2(80); begin -- Allow null values if (lk_code is null) then return(null); end if; begin select MEANING into buf from WF_LOOKUPS where LOOKUP_TYPE = lk_type and LOOKUP_CODE = lk_code; exception when no_data_found then wf_core.token('TYPE', lk_type); wf_core.token('CODE', lk_code); wf_core.raise('WFSQL_LOOKUP_CODE'); end; return(buf); exception when others then wf_core.context('WF_MAIL', 'GetLovMeaning', lk_type, lk_code); raise; end GetLovMeaning; -- GetLovCode (PRIVATE) Return the hidden code of a lookup -- -- IN -- lookup type -- lookup meaning -- RETURN -- lookup code function GetLovCode( lk_type in varchar2, lk_meaning in varchar2) return varchar2 is buf varchar2(30); begin -- Allow null values if (lk_meaning is null) then wf_core.raise('WFMLR_INVALID_LOOKUP'); end if; -- Exact match begin select LOOKUP_CODE into buf from WF_LOOKUPS where LOOKUP_TYPE = lk_type and MEANING = lk_meaning; return buf; exception when no_data_found then null; end; -- Case-insensitive match begin select LOOKUP_CODE into buf from WF_LOOKUPS where LOOKUP_TYPE = lk_type and upper(MEANING) = upper(lk_meaning); exception when no_data_found then wf_core.raise('WFMLR_INVALID_LOOKUP'); end; return buf; exception when others then wf_core.context('WF_MAIL', 'GetLovCode', lk_type, lk_meaning); raise; end GetLovCode; -- GetLovListInternal (PRIVATE) -- Get Text Lov List (Internal Name) -- Inputs: -- lk_type - lookup type, which lookup from table. -- Output: -- A list of valid lookup meanings, in the form -- meaning -- Example: LOOKUP_TYPE: YES_NO -- Returns: -- No -- Yes function GetLovListInternal( lk_type in varchar2) return varchar2 is cursor c is select LOOKUP_CODE from WF_LOOKUPS where LOOKUP_TYPE = lk_type order by LOOKUP_CODE; buffer varchar2(32000); begin buffer := ''; -- -- Loop through selecting all lookups -- for curs in c loop -- Add lookup to end of buffer string. buffer := buffer||curs.lookup_code||g_newLine; end loop; return(buffer); exception when others then wf_core.context('WF_MAIL', 'GetLovListInternal', lk_type); raise; end GetLovListInternal; -- GetDirectAnswer (PRIVATE) -- Get Answer for direct response -- Inputs: -- mail body. -- Output: -- Answer procedure GetDirectAnswer(body in out NOCOPY varchar2, one_answer out NOCOPY varchar2) is answer varchar2(4000); newline_pos pls_integer; close_doublequote_pos pls_integer; answer_syntax_error exception; begin -- Striping leading spaces and tabs while (substr(body, 1, 1) = ' ' or substr(body, 1, 1) = g_tab) loop body := substr(body, 2, length(body) - 1); end loop; if (substr(body, 1, 1) <> '"') then -- Answer not quoted in "" newline_pos := instr(body, g_newLine, 1); if (newline_pos = 0) then answer := substrb(body,1,4000); else if (newline_pos <> 1) then answer := substr(body, 1, newline_pos-1); else answer := null; end if; body := substr(body, newline_pos + 1, length(body) - newline_pos); end if; -- Striping trailing spaces, \r or \tab while ((substr(answer, length(answer), 1) = ' ') or (substr(answer, length(answer), 1) = g_newLine) or (substr(answer, length(answer), 1) = g_tab)) loop answer := substr(answer, 1, length(answer) - 1); end loop; else close_doublequote_pos := instr(body, '"', 2); if close_doublequote_pos > 4001 then answer := substr(body, 2, 3998); else answer := substr(body, 2, close_doublequote_pos -2); end if; if (substr(body, close_doublequote_pos + 1, 1) <> g_newLine) then raise answer_syntax_error; end if; body := substr(body, close_doublequote_pos + 2, length(body) - close_doublequote_pos - 1); end if; one_answer := answer; exception when answer_syntax_error then wf_core.context('WF_MAIL', 'GetDirectAnswer'); raise; when others then wf_core.context('WF_MAIL', 'GetDirectAnswer'); raise; end GetDirectAnswer; -- GetEmailResponse (PRIVATE) - Get Email Response Section -- IN -- notification id -- RETURN -- response template function GetEmailResponse(nid in number) return varchar2 is cursor c1 is select WMA.DISPLAY_NAME, WMA.DESCRIPTION, WMA.TYPE, WMA.FORMAT, decode(WMA.TYPE, 'VARCHAR2', decode(WMA.FORMAT, '', WNA.TEXT_VALUE, substr(WNA.TEXT_VALUE, 1, to_number(WMA.FORMAT))), 'NUMBER', decode(WMA.FORMAT, '', to_char(WNA.NUMBER_VALUE), to_char(WNA.NUMBER_VALUE, WMA.FORMAT)), 'DATE', decode(WMA.FORMAT, '', to_char(WNA.DATE_VALUE), to_char(WNA.DATE_VALUE, WMA.FORMAT)), 'LOOKUP', WNA.TEXT_VALUE, WNA.TEXT_VALUE) VALUE from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES_VL WMA where WNA.NOTIFICATION_ID = nid and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE and WN.MESSAGE_NAME = WMA.MESSAGE_NAME and WMA.NAME = WNA.NAME and WMA.SUBTYPE = 'RESPOND' and WMA.TYPE not in ('FORM', 'URL') order by WMA.SEQUENCE; buffer varchar2(32000); begin -- for each response variable for rec in c1 loop -- Print description buffer := buffer||WordWrap(rec.description, 0); if (rec.description is not null) then buffer := buffer||g_newLine; end if; -- Print prompt buffer := buffer||' '||rec.display_name||': '|| wf_mail.response_quote; -- Print field if (rec.type = 'LOOKUP') then -- LOOKUPs: show displayed meaning, list of choices buffer := buffer || GetLovMeaning(rec.format, rec.value) || wf_mail.response_quote || g_newLine || GetLovList(rec.format); else -- VARCHAR2, NUMBER, or DATE: use value directly. buffer := buffer || rec.value || wf_mail.response_quote || g_newLine; end if; buffer := buffer || g_newLine; end loop; return buffer; exception when others then wf_core.context('WF_MAIL', 'GetEmailResponse', to_char(nid)); raise; end GetEmailResponse; -- GetEmailDirectResponse (PRIVATE) - Get Email Response Section -- IN -- notification id -- RETURN -- response template function GetEmailDirectResponse(nid in number) return varchar2 is cursor c1 is select WMA.DISPLAY_NAME, WMA.DESCRIPTION, WMA.TYPE, WMA.FORMAT, decode(WMA.TYPE, 'VARCHAR2', decode(WMA.FORMAT, '', WNA.TEXT_VALUE, substr(WNA.TEXT_VALUE, 1, to_number(WMA.FORMAT))), 'NUMBER', decode(WMA.FORMAT, '', to_char(WNA.NUMBER_VALUE), to_char(WNA.NUMBER_VALUE, WMA.FORMAT)), 'DATE', decode(WMA.FORMAT, '', to_char(WNA.DATE_VALUE), to_char(WNA.DATE_VALUE, WMA.FORMAT)), 'LOOKUP', WNA.TEXT_VALUE, WNA.TEXT_VALUE) VALUE from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES_VL WMA where WNA.NOTIFICATION_ID = nid and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE and WN.MESSAGE_NAME = WMA.MESSAGE_NAME and WMA.NAME = WNA.NAME and WMA.SUBTYPE = 'RESPOND' and WMA.TYPE not in ('FORM', 'URL') order by WMA.SEQUENCE; buffer varchar2(32000) := ''; seq pls_integer := 1; begin -- for each response variable for rec in c1 loop -- Construct response instruction: wf_core.token('DISPLAY_NAME', rec.display_name); wf_core.token('SEQ', seq); wf_core.token('DESCRIPTION', rec.description); -- Enter the on line <#seq> buffer := buffer|| wf_core.substitute('WFMLR', 'WFMLR_DIRECT_ENTER'); if (rec.value is not null) then if (rec.type = 'LOOKUP') then wf_core.token('DEFAULT_VALUE',GetLovMeaning(rec.format,rec.value)); else wf_core.token('DEFAULT_VALUE', rec.value); end if; end if; if (rec.format is not null) then wf_core.token('FORMAT', rec.format); elsif (rec.type = 'VARCHAR2') then wf_core.token('FORMAT', '2000'); end if; buffer := buffer||g_newLine; -- Handle LOOKUP specially because of printing the LOV list if (rec.type = 'LOOKUP') then if (rec.value is not null) then buffer := buffer|| wf_core.substitute('WFMLR', 'WFMLR_DIRECT_LOOKUP_DEFAULT'); else buffer := buffer|| wf_core.substitute('WFMLR', 'WFMLR_DIRECT_LOOKUP'); end if; buffer := WordWrap(buffer, 0); buffer := buffer || g_newLine || GetLovList(rec.format); else -- Value must be ........ if (rec.type = 'DATE') then -- DATE: show date format if (rec.format is not null) then Buffer := buffer || wf_core.substitute('WFMLR', 'WFMLR_DIRECT_DATE_FORMAT'); else buffer := buffer || wf_core.substitute('WFMLR', 'WFMLR_DIRECT_DATE'); end if; elsif (rec.type = 'NUMBER') then -- NUMBER: show number format if (rec.format is not null) then buffer := buffer || wf_core.substitute('WFMLR','WFMLR_DIRECT_NUMBER_FORMAT'); else buffer := buffer|| wf_core.substitute('WFMLR', 'WFMLR_DIRECT_NUMBER'); end if; elsif (rec.type = 'VARCHAR2') then -- VARCHAR2: show varchar2 format buffer := buffer|| wf_core.substitute('WFMLR', 'WFMLR_DIRECT_VARCHAR2_FORMAT'); end if; if ((rec.value is not null) and (rec.type <> 'LOOKUP')) then wf_core.token('DEFAULT_VALUE', rec.value); buffer := buffer||g_newLine|| wf_core.substitute('WFMLR', 'WFMLR_DIRECT_DEFAULT'); end if; buffer := WordWrap(buffer, 0); end if; buffer := buffer || g_newLine || g_newLine; seq := seq + 1; end loop; return buffer; exception when others then wf_core.context('WF_MAIL', 'GetEmailDirectResponse', to_char(nid)); raise; end GetEmailDirectResponse; -- UrlEncode (PRIVATE) -- Inputs: -- input string -- Output: -- encoded string function UrlEncode(in_string varchar2) return varchar2 is encoded_string varchar2(32000); begin encoded_string := in_string; encoded_string := replace(encoded_string, '%', '%25' ); encoded_string := replace(encoded_string, ' ', '%20' ); encoded_string := replace(encoded_string, '!', '%21' ); encoded_string := replace(encoded_string, '"', '%22' ); encoded_string := replace(encoded_string, '#', '%23' ); encoded_string := replace(encoded_string, '$', '%24' ); encoded_string := replace(encoded_string, '&', '%26' ); encoded_string := replace(encoded_string, '''', '%27' ); encoded_string := replace(encoded_string, '(', '%28' ); encoded_string := replace(encoded_string, ')', '%29' ); encoded_string := replace(encoded_string, '*', '%2a' ); encoded_string := replace(encoded_string, '+', '%2b' ); encoded_string := replace(encoded_string, ',', '%2c' ); encoded_string := replace(encoded_string, '-', '%2d' ); encoded_string := replace(encoded_string, '.', '%2e' ); encoded_string := replace(encoded_string, '/', '%2f' ); encoded_string := replace(encoded_string, ';', '%3b' ); encoded_string := replace(encoded_string, '<', '%3c' ); encoded_string := replace(encoded_string, '=', '%3d' ); encoded_string := replace(encoded_string, '>', '%3e' ); encoded_string := replace(encoded_string, '?', '%3f' ); encoded_string := replace(encoded_string, '@', '%40' ); encoded_string := replace(encoded_string, '[', '%5b' ); encoded_string := replace(encoded_string, '\', '%5c' ); encoded_string := replace(encoded_string, ']', '%5d' ); encoded_string := replace(encoded_string, '^', '%5e' ); encoded_string := replace(encoded_string, '_', '%5f' ); encoded_string := replace(encoded_string, '`', '%60' ); encoded_string := replace(encoded_string, '{', '%7b' ); encoded_string := replace(encoded_string, '|', '%7c' ); encoded_string := replace(encoded_string, '}', '%7d' ); encoded_string := replace(encoded_string, '~', '%7e' ); encoded_string := replace(encoded_string, g_newLine, '%0D%0A'||g_newLine); return(encoded_string); end UrlEncode; -- GetMoreInfoLOV (PRIVATE) - bug 2282139 -- Return a list of WF participants (PRIVATE) -- IN -- notification id -- current_role - role/user to whom the ntf is addresses -- RETURN list of roles whom have participated in the workflow function GetMoreInfoLOV(nid NUMBER, current_role in VARCHAR2) return varchar2 is itemType VARCHAR2(8); itemKey VARCHAR2(200); context VARCHAR2(2000); orig_sys varchar2(30); orig_sysid number; pos number; buffer varchar2(32000); -- Cursor to find all users/roles associated with the notification -- other than the ones associated to recipient_role cursor c is select distinct role user_name from (select wi.begin_date begin_date, owner_role role from wf_items wi where wi.item_type = itemType and wi.item_key = itemKey and owner_role is not null and owner_role <> current_role and owner_role not in (select wur.role_name from wf_user_roles wur where wur.user_name = current_role and wur.user_orig_system = orig_sys and wur.user_orig_system_id = orig_sysid) union select ntf.begin_date begin_date, ntf.recipient_role role from wf_item_activity_statuses ias, wf_notifications ntf where ias.item_type = itemType and ias.item_key = itemKey and ias.notification_id = ntf.group_id and ntf.recipient_role <> current_role and ntf.recipient_role not in (select wur.role_name from wf_user_roles wur where wur.user_name = current_role and wur.user_orig_system = orig_sys and wur.user_orig_system_id = orig_sysid) union select ntf.begin_date begin_date, ntf.recipient_role role from wf_item_activity_statuses_h ias, wf_notifications ntf where ias.item_type = itemType and ias.item_key = itemKey and ias.notification_id = ntf.group_id and ntf.recipient_role <> current_role and ntf.recipient_role not in (select wur.role_name from wf_user_roles wur where wur.user_name = current_role and wur.user_orig_system = orig_sys and wur.user_orig_system_id = orig_sysid) -- sacsharm bug 2887904 latest participant first order by begin_date desc); begin select context into context from wf_notifications where notification_id = nid; -- get item type and item key from the context pos := instr(context, ':', 1, 1); itemType := substr(context, 1, pos-1); pos := pos+1; itemKey := substr(context, pos, instr(context, ':', pos, 1) - pos); buffer := ''; -- get role's orig sys and orig sys id from the role name pos := instr(current_role, ':', 1, 1); if (pos > 0) then orig_sys := substr(current_role, 1, pos-1); orig_sysid := substr(current_role, pos+1); else Wf_Directory.GetRoleOrigSysInfo(current_role, orig_sys, orig_sysid); end if; for curs in c loop -- sacsharm bug 2887904 if one particpant, no linefeed needed -- buffer := buffer || curs.user_name || g_newLine; if c%ROWCOUNT = 1 then buffer := curs.user_name; -- sacsharm bug 2887904 only latest particpant should be returned -- NOTE following exit should be removed later one issue of how to -- display participant roles in email is resolved. This is done -- so that currently this function only returns latest participant exit; elsif c%ROWCOUNT > 1 then buffer := buffer || g_newLine || curs.user_name; end if; end loop; return buffer; exception when others then WF_CORE.Context('WF_MAIL','GetMoreInfoLOV', to_char(nid)); raise; end GetMoreInfoLOV; -- -- GetMoreInfoMailTo (PRIVATE) - bug 2282139 -- IN -- nid - Notification id -- n_tag - NID string -- reply_to - Reply to email id -- subject - Email subject -- OUT -- mail to html tag for more info request or submission function GetMoreInfoMailTo(nid in number, n_tag in varchar2, reply_to in varchar2, subject in varchar2) return varchar2 is buffer varchar2(32000); body varchar2(32000); encoded_tag varchar2(240); question varchar2(4000); l_requestee varchar2(32000); cursor c_questions is select user_comment from wf_comments where notification_id = nid and action = 'QUESTION' order by comment_date desc ; begin -- this gives mailto tag for the OPEN_MOREINFO template to submit -- requested information if (g_moreinfo = 'SUB') then -- Encode any special characters encoded_tag := UrlEncode(n_tag||'[4]'); -- sacsharm, too much space -- body := body || g_newLine; body := body || g_moreInfoQPrompt; open c_questions; fetch c_questions into question; close c_questions; -- provide response delimiters based on the content of the question if (instr(question, '''', 1) > 0 and instr(question, '"', 1) > 0 and (instr(question, '[', 1) > 0 or instr(question, ']', 1) > 0)) then -- all delimiters are used in the question, escape one and enclose -- within the escaped one question := replace(question, '''', '\\'''); body := body || ': '''||question||''''; elsif (instr(question, '''', 1) > 0 and instr(question, '"', 1) > 0) then body := body || ': ['||question||']'; elsif (instr(question, '''', 1) > 0) then body := body || ': "'||question||'"'; else body := body || ' :'''||question||''''; end if; body := body || g_newLine; body := body || g_moreInfoAPrompt; -- ankung (removing <) -- body := body || ': ''<'; body := body || ': '''; body := body || g_moreInfoAnswer; -- ankung (removing >) -- body := body || '>'''; body := body || ''''; body := body || g_newLine; body := UrlEncode(body); buffer := buffer ||'&'||'nbsp;'||'&'||'nbsp;'|| ''|| ''||g_moreInfoSubmit|| ''||''; -- this gives the additional link along with the response links to -- request for more information from a user/role. elsif (g_moreinfo = 'REQ') then -- Encode any special characters encoded_tag := UrlEncode(n_tag||'[3]'); -- ankung (placing role between the '') -- body := body || g_moreInfoFrom ||': '''''; -- body := body || g_newLine; body := body || g_moreInfoFrom; body := body || ': '''; l_requestee := GetMoreInfoLOV(nid, g_to_role); if (l_requestee is null) then l_requestee := g_moreInfoRequestee; end if; body := body || wf_notification.SubstituteSpecialChars(l_requestee); -- body := body || GetMoreInfoLOV(nid, g_to_role); -- ankung (continuation of above) body := body || ''''; -- sacsharm commented out, too much space -- body := body || g_newLine; body := body || g_newLine; body := body || g_moreInfoQPrompt; -- ankung (removing <) -- body := body || ': ''<'; body := body || ': '''; body := body || g_moreInfoQuestion; -- ankung (removing >) -- body := body || '>'''; body := body || ''''; -- sacsharm commented out, too much space -- body := body || g_newLine; body := body || g_newLine; body := UrlEncode(body); buffer := buffer ||'&'||'nbsp;'||'&'||'nbsp;'|| ''|| ''||g_moreInfoSubject|| ''||''; end if; return buffer; exception when others then wf_core.context('Wf_Mail', 'GetMoreInfoMailTo', to_char(nid)); raise; end GetMoreInfoMailTo; -- GetMailToBody (PRIVATE) - Construct the mailto body part. -- IN -- notification id -- One of the Action(Result) attribute answer -- RETURN -- mailto html tag with the subject and body function GetMailToBody(nid in number, result_answer in varchar2) return varchar2 is cursor c1 is select WMA.NAME, WMA.DISPLAY_NAME, WMA.DESCRIPTION, WMA.TYPE, WMA.FORMAT, decode(WMA.TYPE, 'VARCHAR2', decode(WMA.FORMAT, '', WNA.TEXT_VALUE, substr(WNA.TEXT_VALUE, 1, to_number(WMA.FORMAT))), 'NUMBER', decode(WMA.FORMAT, '', to_char(WNA.NUMBER_VALUE), to_char(WNA.NUMBER_VALUE, WMA.FORMAT)), 'DATE', decode(WMA.FORMAT, '', to_char(WNA.DATE_VALUE), to_char(WNA.DATE_VALUE, WMA.FORMAT)), 'LOOKUP', WNA.TEXT_VALUE, WNA.TEXT_VALUE) VALUE from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES_VL WMA where WNA.NOTIFICATION_ID = nid and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE and WN.MESSAGE_NAME = WMA.MESSAGE_NAME and WMA.NAME = WNA.NAME and WMA.SUBTYPE = 'RESPOND' and WMA.TYPE not in ('FORM', 'URL') order by decode(WMA.NAME, 'RESULT', -100, WMA.SEQUENCE); buffer varchar2(32000); begin -- for each response variable for rec in c1 loop -- Print description if ((rec.name <> 'RESULT') or (result_answer = 'Respond')) then if (rec.description is not null) then buffer := buffer||WordWrap(rec.description, 0); buffer := buffer||g_newLine; end if; end if; -- Print prompt buffer := buffer||rec.display_name||': '''; -- Preseed the answer so that recipient does not have to type in manually. if ((rec.name = 'RESULT') and (result_answer <> 'Respond')) then rec.value := result_answer; end if; -- Print field if (rec.type = 'LOOKUP') then -- LOOKUPs: show displayed meaning, list of choices buffer := buffer || GetLovMeaning(rec.format, rec.value) || '''' || g_newLine; if (rec.name <> 'RESULT' ) then buffer := buffer || GetLovList(rec.format); end if; else -- VARCHAR2, NUMBER, or DATE: use value directly. buffer := buffer || rec.value || '''' || g_newLine; end if; buffer := buffer || g_newLine; end loop; buffer := UrlEncode(buffer); return buffer; exception when others then wf_core.context('WF_MAIL', 'GetMailToBody', to_char(nid)); raise; end GetMailToBody; -- GetMailToBody (PRIVATE) - Construct the mailto body part. -- IN -- notification id -- One of the Action(Result) attribute answer -- RETURN -- mailto html tag with the subject and body procedure GetMailToBody(nid in number, result_answer in varchar2, doc in out NOCOPY CLOB) is cursor c1 is select WMA.NAME, WMA.DISPLAY_NAME, WMA.DESCRIPTION, WMA.TYPE, WMA.FORMAT, decode(WMA.TYPE, 'VARCHAR2', decode(WMA.FORMAT, '', WNA.TEXT_VALUE, substr(WNA.TEXT_VALUE, 1, to_number(WMA.FORMAT))), 'NUMBER', decode(WMA.FORMAT, '', to_char(WNA.NUMBER_VALUE), to_char(WNA.NUMBER_VALUE, WMA.FORMAT)), 'DATE', decode(WMA.FORMAT, '', to_char(WNA.DATE_VALUE), to_char(WNA.DATE_VALUE, WMA.FORMAT)), 'LOOKUP', WNA.TEXT_VALUE, WNA.TEXT_VALUE) VALUE from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES_VL WMA where WNA.NOTIFICATION_ID = nid and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE and WN.MESSAGE_NAME = WMA.MESSAGE_NAME and WMA.NAME = WNA.NAME and WMA.SUBTYPE = 'RESPOND' and WMA.TYPE not in ('FORM', 'URL') order by decode(WMA.NAME, 'RESULT', -100, WMA.SEQUENCE); str_buffer varchar2(32000); --buffer CLOB; -- bufferIdx pls_integer; begin -- bufferIdx := wf_temp_lob.getLob(g_LOBTable); -- for each response variable for rec in c1 loop -- Print description if ((rec.name <> 'RESULT') or (result_answer = 'Respond')) then if (rec.description is not null) then str_buffer := str_buffer||WordWrap(rec.description, 0); str_buffer := str_buffer||g_newLine; end if; end if; -- Print prompt str_buffer := str_buffer||rec.display_name||': '''; -- Preseed the answer so that recipient does not have -- to type in manually. if ((rec.name = 'RESULT') and (result_answer <> 'Respond')) then rec.value := result_answer; end if; -- Print field if (rec.type = 'LOOKUP') then -- LOOKUPs: show displayed meaning, list of choices str_buffer := str_buffer || GetLovMeaning(rec.format, rec.value) || '''' || g_newLine; if (rec.name <> 'RESULT' ) then str_buffer := str_buffer || GetLovList(rec.format); end if; else -- VARCHAR2, NUMBER, or DATE: use value directly. str_buffer := str_buffer || rec.value || '''' || g_newLine; end if; str_buffer := str_buffer || g_newLine; if(length(str_buffer) > 24000) then str_buffer := UrlEncode(str_buffer); DBMS_LOB.writeAppend(lob_loc => doc, amount => length(str_buffer), buffer => str_buffer); str_buffer := ''; end if; -- DBMS_LOB.writeAppend(g_LOBTable(bufferIdx).temp_lob, -- length(str_buffer), -- str_buffer); -- str_buffer := ''; end loop; if(length(str_buffer) > 0) then str_buffer := UrlEncode(str_buffer); DBMS_LOB.writeAppend(lob_loc => doc, amount => length(str_buffer), buffer => str_buffer); str_buffer := ''; end if; -- DBMS_LOB.Append(doc, g_LOBTable(bufferIdx).temp_lob); -- wf_temp_lob.releaseLob(g_LOBTable, bufferIdx); exception when others then -- wf_temp_lob.releaseLob(g_LOBTable, bufferIdx); wf_core.context('WF_MAIL', 'GetMailToBody', to_char(nid)); raise; end GetMailToBody; -- GetMailTo - Construct MailTo Section (PRIVATE) -- IN -- notification id -- notification tag -- notification reply to -- notification subject -- RETURN -- mailto html tag with the subject and body function GetMailTo(nid in number, n_tag in varchar2, reply_to in varchar2, subject in varchar2) return varchar2 is -- SQL Statement for fetching URL RESPONSE attributes. cursor c1 is select WMA.NAME, WMA.DISPLAY_NAME, WNA.TEXT_VALUE, WMA.DESCRIPTION from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES_VL WMA where WNA.NOTIFICATION_ID = nid and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE and WN.MESSAGE_NAME = WMA.MESSAGE_NAME and WMA.NAME = WNA.NAME and WMA.SUBTYPE = 'RESPOND' and WMA.TYPE = 'URL' order by WMA.SEQUENCE; lov varchar2(64); lov_list varchar2(240); buffer varchar2(32000); auto_answer varchar2(64); newline_pos number; disp_name varchar2(80); attr_type varchar2(8); attr_format varchar2(240); attr_value varchar2(32000); attr_desc varchar2(240); encoded_tag varchar2(240); begin -- Clear buffer buffer := ''; -- URL RESPONSE attributes overrides the normal RESULT attributes. -- So, my goal here is to check for this case. -- URL RESPONSE attributes is going to appear as a anchor and don't have -- to construct the MAILTO html tag stuff that we do for the normal -- RESULT attribute. -- NOTE: Please do know that I don't want to destablize the existing code -- for the normal RESULT attribute MAILTO handleing so that I am coding -- these two cases seperately. -- for each response variable for rec in c1 loop buffer := buffer||'

'; if (rec.description is not null) then buffer := buffer||rec.description||'

'; end if; buffer := buffer|| ''; buffer := buffer||' '||rec.display_name; buffer := buffer||''||''; buffer := buffer||g_newLine; end loop; if (buffer is not null) then return(buffer); end if; -- -- Normal RESULT attribute handling -- begin select WMA.DISPLAY_NAME, WMA.TYPE, WMA.FORMAT, decode(WMA.TYPE, 'VARCHAR2', decode(WMA.FORMAT, '', WNA.TEXT_VALUE, substr(WNA.TEXT_VALUE, 1, to_number(WMA.FORMAT))), 'NUMBER', decode(WMA.FORMAT, '', to_char(WNA.NUMBER_VALUE), to_char(WNA.NUMBER_VALUE, WMA.FORMAT)), 'DATE', decode(WMA.FORMAT, '', to_char(WNA.DATE_VALUE), to_char(WNA.DATE_VALUE, WMA.FORMAT)), 'LOOKUP', WNA.TEXT_VALUE, WNA.TEXT_VALUE), WMA.DESCRIPTION into disp_name, attr_type, attr_format, attr_value, attr_desc from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES_VL WMA where WNA.NOTIFICATION_ID = nid and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE and WN.MESSAGE_NAME = WMA.MESSAGE_NAME and WMA.NAME = WNA.NAME and WMA.SUBTYPE = 'RESPOND' and WMA.NAME = 'RESULT' and WMA.TYPE not in ('FORM', 'URL'); -- We can only construct answer button or mailto link if is lookup if (attr_type <> 'LOOKUP') then auto_answer := 'Respond'; else -- If is LOOKUP RESULT attribute, we need to show the description here. if (attr_desc is not null) then buffer := buffer||'

'||attr_desc; end if; auto_answer := attr_format; end if; exception when no_data_found then auto_answer := 'Respond'; end; -- Encode any special characters encoded_tag := UrlEncode(n_tag); -- Construct mailto syntax buffer := buffer||'

'||disp_name||': '||''|| g_noResult||''||''; else lov_list := GetLovListInternal(auto_answer); lov_list := substr(lov_list, 1, length(lov_list)-1); while (lov_list is not null) loop newline_pos := instr(lov_list, g_newLine); if (newline_pos = 0) then lov := lov_list; else lov := substr(lov_list, 1, newline_pos - 1); end if; buffer := buffer||GetMailToBody(nid, lov); buffer :=buffer||'%0D%0A%0D%0A'||encoded_tag||'">'|| ''||GetLovMeaning(attr_format, lov)|| ''||''; if (newline_pos = 0) then lov_list := null; if (g_moreinfo = 'REQ') then buffer := buffer || GetMoreInfoMailTo(nid, n_tag, reply_to, subject); end if; else lov_list := substr(lov_list, newline_pos+1, length(lov_list) - newline_pos); buffer := buffer||g_newLine; buffer := buffer||'&'||'nbsp;'||'&'||'nbsp;'|| ' 0 then attrList := substrb(attrList,1, length(attrList)-1); end if; document := ''; if display_type = g_ntfDocText then if length(attrList) > 0 then document := wf_notification.wf_msg_attr(nid, attrList, display_type); end if; elsif display_type = g_ntfDocHtml then document := ''||g_newLine; document := document || '
'; j := 1; if (l_from_user is not null) then cells(j) := 'E:'||g_from; j := j + 1; cells(j) := 'S12:'; j := j + 1; cells(j) := 'S:'||'&'||'SENDER'; j := j + 1; end if; cells(j) := 'E:'||g_to; j := j + 1; cells(j) := 'S12:'; j := j + 1; -- sacsharm bug 2897428 fix -- cells(j) := 'S:'||'&'||'TO'; cells(j) := 'S:'||'&'||'TO_DNAME'; j := j + 1; cells(j) := 'E:'||g_beginDate; j := j + 1; cells(j) := 'S12:'; j := j + 1; cells(j) := 'S:'||'&'||'START_DATE'; j := j + 1; if (l_due_date is not null) then cells(j) := 'E:'||g_dueDate2; j := j + 1; cells(j) := 'S12:'; j := j + 1; cells(j) := 'S:'||'&'||'DUE_DATE'; j := j + 1; end if; cells(j) := 'E:'||g_Id; j := j + 1; cells(j) := 'S12:'; j := j + 1; cells(j) := 'S:'||'&'||'NOTIFICATION_ID'; wf_notification.NTF_Table(cells => cells, col => 3, type => 'N'||l_dir, rs => headerTable); document := document || headerTable || g_newLine; document := document || '' || g_newLine; if length(attrList) > 0 then wf_notification.set_ntf_table_type('N'); wf_notification.set_ntf_table_direction(l_dir); document := document||wf_notification.wf_msg_attr(nid, attrList, display_type); wf_notification.set_ntf_table_type('V'); end if; document := document || '
'||g_newLine; else document := ''; end if; document_type := display_type; exception when others then wf_core.context('WF_MAIL','GetHeaderTable',document_id, display_type); raise; end GetHeaderTable; -- GetMessage - get email message data -- -- IN -- notification id -- mailer node name -- web agent path -- OUT -- message subject -- message body (text) -- message body (html) -- NOTE -- This API was used by the C mailer which is now obsolete. This -- procedure is now considered deprecated. procedure GetMessage( nid in number, node in varchar2, agent in varchar2, replyto in varchar2, subject out NOCOPY varchar2, text_body out NOCOPY varchar2, html_body out NOCOPY varchar2, body_atth out NOCOPY varchar2, error_result in out NOCOPY varchar2) as n_status varchar2(8); n_mstatus varchar2(8); n_key varchar2(80); n_to_role varchar2(320); n_from_user varchar2(320); -- Bug 2094159 n_due_date date; n_start_date date; n_end_date date; n_priority number; n_comment varchar2(4000); n_subject varchar2(2000); n_response varchar2(32000); n_text_body varchar2(32000); n_html_body varchar2(32000); n_direct varchar2(3); n_click_here varchar2(4000); n_disp_click varchar2(240); r_dname varchar2(360); r_email varchar2(2000); r_language varchar2(4000); r_territory varchar2(4000); r_ntf_pref varchar2(240); t_type varchar2(100); t_name varchar2(100); t_subject varchar2(240); t_text_body varchar2(4000); t_html_body varchar2(4000); t_headerText varchar2(32000); n_headerText varchar2(32000); n_timezone varchar2(230); t_headerHTML varchar2(32000); n_headerHTML varchar2(32000); m_html varchar2(32000); err_name varchar2(30); err_message varchar2(2000); err_stack varchar2(4000); fyi pls_integer; body_start pls_integer; body_end pls_integer; tag_pos pls_integer; dir_pos pls_integer; start_cnt pls_integer; end_cnt pls_integer; crpos pls_integer; str_length pls_integer; lnsize pls_integer; temp varchar2(32000); line varchar2(32000); buffer varchar2(32000); no_program_unit exception; pragma exception_init(no_program_unit, -6508); dummy varchar2(4000); -- Bug# 2301881 variables to handle invalid response error message err_invalid varchar2(1000); err_expected varchar2(1000); -- Bug 2395898 variable to check if response attr exists n_response_exists varchar2(1); -- Bug 2375920 variables to process message based on signature n_sig_policy varchar2(100); n_nid_str varchar2(200); -- bug 2282139 more info feature n_more_info_role varchar2(320); n_mailto varchar2(10000); n_html_history varchar2(32000); n_text_history varchar2(32000); n_last_ques varchar2(4000); n_dir varchar2(16); begin -- Get notification information -- Bug 2094159 get from_user from wf_notifications begin select STATUS, MAIL_STATUS, ACCESS_KEY, RECIPIENT_ROLE, PRIORITY, USER_COMMENT, BEGIN_DATE, END_DATE, DUE_DATE, FROM_USER, MORE_INFO_ROLE into n_status, n_mstatus, n_key, n_to_role, n_priority, n_comment, n_start_date, n_end_date, n_due_date, n_from_user, n_more_info_role from WF_NOTIFICATIONS where NOTIFICATION_ID = nid; exception when no_data_found then wf_core.token('NID', to_char(nid)); wf_core.raise('WFNTF_NID'); end; -- More information processing - bug 2282139 g_moreinfo := NULL; if (wf_mail.test_flag = TRUE) then n_mstatus := 'MAIL'; if (n_status not in ('OPEN','CANCELED','CLOSED')) then n_status := 'OPEN'; end if; end if; -- g_to_role global variable is to identify to whom the email is addressed -- when contructing the More Info MAILTO, so that the role name is not displayed -- among the participants -- Timezone will not be supported in this version of the GetMessage -- API. n_timezone := ''; if (wf_notification.HideMoreInfo(nid) = 'N') then if(n_more_info_role is not null) then n_to_role := n_more_info_role; g_to_role := n_more_info_role; g_moreinfo := 'SUB'; else g_to_role := n_to_role; g_moreinfo := 'REQ'; end if; end if; -- Get Recipient information Wf_Directory.GetRoleInfo(n_to_role, r_dname, r_email, r_ntf_pref, r_language, r_territory); r_ntf_pref := nvl(r_ntf_pref, 'QUERY'); wf_notification.GetComments(nid, g_ntfDocText, n_text_history, n_last_ques); wf_notification.GetComments(nid, g_ntfDocHtml, n_html_history, n_last_ques); if n_text_history is not null or n_text_history <> '' then if isBiDi(r_language) then n_text_history := n_text_history||' '|| g_ntfHistory; else n_text_history := g_ntfHistory||' '|| n_text_history; end if; end if; if n_html_history is not null or n_html_history <> '' then if isBiDi(r_language) then n_dir := 'dir="rtl" '; else n_dir := ''; end if; n_html_history := ''|| '
'|| ''|| g_ntfHistory|| ''|| n_html_history || '
'||g_newLine; end if; -- Bug 2375920 get signature policy for the notification Wf_Mail.GetSignaturePolicy(nid, n_sig_policy); n_subject := WF_NOTIFICATION.GetSubject(nid, 'text/plain'); -- We will always fetch plain text version of the message because -- Because for sendmail MAILATTH case, we need to send out html message -- body as attachment and then the plain text message as the body. -- For MAPI MAILATTH and MAILHTML cases, same thing. if isBiDi(r_language) then WF_NOTIFICATION.Set_NTF_Table_Direction('R'); else WF_NOTIFICATION.Set_NTF_Table_Direction('L'); end if; n_text_body := WF_NOTIFICATION.GetBody(nid, g_ntfDocText); GetHeaderTable(r_language||':'||to_char(nid), g_ntfDocText, t_headerText, dummy); if r_ntf_pref in ('MAILHTML', 'MAILATTH', 'MAILHTML2') then n_html_body := WF_NOTIFICATION.GetBody(nid, g_ntfDocHtml); GetHeaderTable(r_language||':'||to_char(nid), g_ntfDocHtml, t_headerHTML, dummy); -- Extracts content between and if there is body tag -- This is to deal with people import the whole html file to the -- html message body through the builder. -- The logic here is that if we don't see and , then -- this is already just a html portion. Otherwise, extract -- the content in between and . body_start := 0; body_start := instr(upper(n_html_body), ''); if (body_start <> 0) then body_start := body_start + length(''); body_end := instr(upper(n_html_body), ''); if (body_end = 0) then body_end := length(n_html_body); else body_end := body_end - 1; end if; n_html_body := substr(n_html_body, body_start, body_end); end if; -- -- For every 900 character, we insert a newline just in case -- Because this whole message body may go out to the Unix SMTP gateway -- which does not like a line longer than 1000 characters. -- We do it 900 here just for safty. -- 2001/03/23 Changed algorithm to start at 900 point and then -- move to the nearest whitespace. -- lnsize := 900; start_cnt := 1; end_cnt := lnsize; temp := ''; str_length := length(n_html_body); while start_cnt < str_length loop -- use the existing newlines as a natural break crpos := instr(n_html_body, g_newLine, start_cnt+1, 1) - start_cnt; if crpos > 0 and crpos < end_cnt then end_cnt := crpos; else -- Move forward to the next white space. while (start_cnt + end_cnt < str_length) and substr(n_html_body, start_cnt + end_cnt, 1) not in (' ', g_newLine, g_tab) and end_cnt < 999 loop end_cnt := end_cnt + 1; end loop; -- We need to understand the full conditions underwhich -- the previous loop exited. All characters must be preserved -- and the line, no matter what can not exceed 900 characters. if end_cnt >= (999) then end_cnt := lnsize; while (start_cnt + end_cnt > start_cnt) and substr(n_html_body, start_cnt + end_cnt, 1) not in (' ', g_newLine, g_tab) and end_cnt > 0 loop end_cnt := end_cnt - 1; end loop; -- If we can not locate a white space, then use the default if end_cnt <= 0 then end_cnt := lnsize; end if; end if; end if; -- Ensure the last characters are not lost. if start_cnt + end_cnt >= str_length then line := substr(n_html_body, start_cnt); else line := substr(n_html_body, start_cnt, end_cnt); end if; temp := temp || line; -- If there is a newline at this point, -- then do not bother with another. if substr(n_html_body, start_cnt + end_cnt, 1) <> g_newLIne then temp := temp||g_newLine; end if; -- We do not want to start the new line with the space. if substr(n_html_body, start_cnt + end_cnt, 1) = ' ' then start_cnt := start_cnt + end_cnt + 1; else start_cnt := start_cnt + end_cnt; end if; end_cnt := lnsize; end loop; n_html_body := temp; end if; -- Bug 2375920 Process the email message based on the signature policy ProcessSignaturePolicy(nid, n_sig_policy, n_status, n_mstatus, n_key, node, t_type, t_name, n_nid_str); -- Get template begin select SUBJECT, BODY, HTML_BODY into t_subject, t_text_body, t_html_body from WF_MESSAGES_VL where NAME = t_name and TYPE = t_type; exception when no_data_found then wf_core.token('NAME', t_name); wf_core.token('TYPE', t_type); wf_core.raise('WFNTF_MESSAGE'); end; -- Get Click here Response display value begin select DESCRIPTION into n_disp_click from WF_MESSAGE_ATTRIBUTES_TL where MESSAGE_TYPE = t_type and MESSAGE_NAME = t_name and NAME = 'CLICK_HERE_RESPONSE' and LANGUAGE = userenv('LANG'); exception when NO_DATA_FOUND then -- ignore if this attribute does not exist null; end; -- Retrieve errror attributes for INVALID message if (t_name = 'OPEN_INVALID') then begin err_name := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_NAME'); err_message := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_MESSAGE'); err_invalid := Wf_Notification.GetAttrText(nid, 'MAIL_VALUE_FOUND'); err_expected := Wf_Notification.GetAttrText(nid, 'MAIL_EXP_VALUES'); exception when others then null; end; end if; -- If there is no html template available, use the plain text one. if (t_html_body is null) then t_html_body := replace(t_text_body, g_newLine, '
'||g_newLine); -- Ensure the direction of the text is correct for the language if isBiDi(r_language) then t_html_body := ''||t_html_body; else t_html_body := ''||t_html_body; end if; else -- Ensure that the direction of the text is correctly specified. if isBiDi(r_language) then tag_pos := instrb(upper(t_html_body), ' 0 then dir_pos := instrb(upper(t_html_body), ' DIR="', 1); if dir_pos = 0 then buffer := substrb(t_html_body, 1, 5); buffer := buffer||' DIR="RTL" '||substrb(t_html_body, tag_pos+5); t_html_body := buffer; end if; end if; end if; end if; -- Substitute if wf_mail.direct_response then n_direct := '[2]'; else n_direct := NULL; end if; -- Bug# 2301881 replacing err_stack with err_invalid and err_expected -- to make the WARNING message to the responder more user-friendly n_subject := Substitute(t_subject, nid, n_nid_str, n_status, n_to_role, r_dname, r_email, n_start_date, n_due_date, n_end_date, n_from_user, n_priority, n_comment, n_subject, dummy, dummy, err_name, err_message, err_invalid, err_expected, n_timezone); n_headerText := Substitute(t_headerText, nid, n_nid_str, n_status, n_to_role, r_dname, r_email, n_start_date, n_due_date, n_end_date, n_from_user, n_priority, n_comment, n_subject, dummy, dummy, err_name, err_message, err_invalid, err_expected, n_timezone); n_headerHTML := Substitute(t_headerText, nid, n_nid_str, n_status, n_to_role, r_dname, r_email, n_start_date, n_due_date, n_end_date, n_from_user, n_priority, n_comment, n_subject, dummy, dummy, err_name, err_message, err_invalid, err_expected, n_timezone); n_text_body := Substitute(t_text_body, nid, n_nid_str, n_status, n_to_role, r_dname, r_email, n_start_date, n_due_date, n_end_date, n_from_user, n_priority, n_comment, n_subject, n_headerText, n_text_body, err_name, err_message, err_invalid, err_expected, n_timezone); n_html_body := Substitute(t_html_body, nid, n_nid_str, n_status, n_to_role, r_dname, r_email, n_start_date, n_due_date, n_end_date, n_from_user, n_priority, n_comment, UrlEncode(n_subject), n_headerHTML, n_html_body, err_name, err_message, err_invalid, err_expected, n_timezone); -- Wrap the body into nice pretty lines. if (r_ntf_pref in ('MAILTEXT', 'MAILATTH')) then n_text_body := WordWrap(n_text_body, 0); end if; if (g_moreinfo = 'SUB') then n_response := g_moreInfoAPrompt || ': "<' || g_moreInfoAnswer ||'>"'; n_response := n_response || g_newLine; else if wf_mail.direct_response then n_response := GetEmailDirectResponse(nid); else n_response := GetEmailResponse(nid); end if; end if; -- make sure total length will not exceed 32K -- if it does truncate the body, leaving room for truncation string if (length(n_text_body) + length(n_response)) > 32000 then n_text_body := substr(n_text_body, 1, 31900 - length(n_response)) ||g_newLine|| g_truncate; end if; -- Add email response section if instr(n_text_body,'&'||'RESPONSE')> 0 then n_text_body := substrb(replace(n_text_body, '&'||'RESPONSE', n_response), 1, 32000); else -- Fix for bug 2395898 - do not append the response when no token -- Check to see if the response is included in the template begin select 'Y' into n_response_exists from WF_MESSAGES_VL where NAME = t_name and TYPE = 'WFMAIL' and instr(body,'&'||'RESPONSE')<>0; exception when no_data_found then n_response_exists := 'N'; end; -- we must have truncated the token, so just append the response if (n_response_exists = 'Y') then n_text_body := n_text_body||n_response; end if; end if; -- repeat for html body if (length(n_html_body) + length(n_response)) > 32000 then n_html_body := substr(n_html_body, 1, 31900 - length(n_response)) ||g_newLine|| g_truncate; end if; -- Add email response section if instr(n_html_body,'&'||'RESPONSE')> 0 then n_html_body := substrb(replace(n_html_body, '&'||'RESPONSE', n_response), 1, 32000); end if; -- More Information processing - adding history of questions and answers -- to outbound notifications if instr(n_html_body,'&'||'QUESTION')> 0 then n_html_body := substrb(replace(n_html_body, '&'||'QUESTION', n_last_ques), 1, 32000); end if; if instr(n_text_body,'&'||'QUESTION')> 0 then n_text_body := substrb(replace(n_text_body, '&'||'QUESTION', n_last_ques), 1, 32000); end if; if instr(n_html_body,'&'||'HISTORY')> 0 then n_html_body := substrb(replace(n_html_body, '&'||'HISTORY', n_html_history), 1, 32000); end if; if instr(n_text_body,'&'||'HISTORY')> 0 then n_text_body := substrb(replace(n_text_body, '&'||'HISTORY', n_text_history), 1, 32000); end if; -- Add mailto section -- when template used is OPEN_MOREINFO, providing a template to submit -- more information if (g_moreinfo = 'SUB') then n_mailto := GetMoreInfoMailTo(nid, 'NID['||to_char(nid)||'/'||n_key ||'@'||node||']',replyto, n_subject); else n_mailto := GetMailTo(nid, 'NID['||to_char(nid)||'/'||n_key||'@'||node||']', replyto, n_subject); end if; n_html_body := substrb(replace(n_html_body, '&'||'MAILTO', n_mailto), 1, 32000); -- Add click_here_response section n_click_here := ''||n_disp_click||''; n_html_body := substrb(replace(n_html_body, '&'||'CLICK_HERE_RESPONSE', n_click_here), 1, 32000); -- Get HTML attachment if (agent is null) then m_html := substrb(WFA_HTML.Detail2(nid, n_key, g_webAgent), 1, 32000); else m_html := substrb(WFA_HTML.Detail2(nid, n_key, agent), 1, 32000); end if; if isBiDi(r_language) then tag_pos := instrb(upper(m_html), ' 0 then dir_pos := instrb(upper(m_html), ' DIR="', 1); if dir_pos = 0 then buffer := substrb(m_html, 1, 5); buffer := buffer||' DIR="RTL" '||substrb(m_html, tag_pos+5); m_html := buffer; end if; end if; end if; -- Close of the HTML Body only where this is none if instr(n_html_body, '') = 0 then n_html_body := n_html_body || ''; end if; subject := n_subject; text_body := n_text_body; html_body := n_html_body; -- this is for the little attachment to the detail frame body_atth := m_html; exception when no_program_unit then wf_core.context('WF_MAIL', 'GetMessage', to_char(nid), node); raise; when others then -- First look for a wf_core error. wf_core.get_error(err_name, err_message, err_stack); -- If no wf_core error look for a sql error. if (err_name is null) then err_message := sqlerrm; end if; error_result := err_message||g_newLine||err_stack; wf_core.context('WF_MAIL', 'GetMessage', to_char(nid), node, error_result); end GetMessage; -- LOBReplace -- To replace the given token in message with the token value -- IN -- message The CLOB message containing the tokens -- token The varchar token, complete with '&' prefix -- tokenValue The varchar value to substitute for the token -- append The boolean flag to say if the token was NOT found, then -- append the tokenValue regardless -- procedure LOBReplace(message IN OUT NOCOPY CLOB, token IN VARCHAR2, tokenValue IN VARCHAR2, append IN BOOLEAN) is -- temp CLOB; tempIdx pls_integer; pos NUMBER; msgLen NUMBER; amount NUMBER; continue boolean; offset number; sourcePos number; targetPos number; tokenLen pls_integer; nextChar varchar2(10); validToken boolean; xSet varchar2(100) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'|| '0123456789_'; tokenFound boolean; begin -- DBMS_LOB.CreateTemporary(temp, TRUE, DBMS_LOB.CALL); tempIdx := -1; offset := 1; sourcePos := 1; targetPos := 1; continue := TRUE; msgLen := DBMS_LOB.GetLength(message); tokenLen := length(token); tokenFound := FALSE; while continue loop pos := DBMS_LOB.Instr(message, token, offset, 1); if (pos <> 0) then if (pos + tokenLen <= msgLen) then nextChar := upper(dbms_lob.substr(message, 1, pos + tokenLen)); if (instr(xSet, nextChar,1,1) > 0) then validToken := false; else validToken := true; end if; else validToken := true; end if; else validToken := false; continue := FALSE; end if; if continue then if validToken then -- Only request a LOB if it is necessary and only if one -- has not already been requested. if tempIdx = -1 then tempIdx := wf_temp_lob.getLob(g_LOBTable); end if; amount := pos - sourcePos; DBMS_LOB.Copy(dest_lob => g_LOBTable(tempIdx).temp_lob, src_lob => message, amount => amount, dest_offset => targetPos, src_offset => sourcePos); if (tokenValue <> '' or tokenValue is not null) then DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(tokenValue), tokenValue); end if; sourcePos := pos + tokenLen; targetPos := DBMS_LOB.GetLength(g_LOBTable(tempIdx).temp_lob) + 1; tokenFound := TRUE; end if; offset := pos + tokenLen; else amount := msgLen - sourcePos +1; if (amount > 0)then if tempIdx = -1 then tempIdx := wf_temp_lob.getLob(g_LOBTable); end if; DBMS_LOB.Copy(g_LOBTable(tempIdx).temp_lob, message, amount, targetPos, sourcePos); end if; if (append and tokenFound = FALSE and msgLen > 0) then if (tokenValue <> '' or tokenValue is not null) then if tempIdx = -1 then tempIdx := wf_temp_lob.getLob(g_LOBTable); end if; DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(tokenValue), tokenValue); end if; end if; end if; -- CONTINUE end loop; if tokenFound = TRUE then msgLen := DBMS_LOB.GetLength(g_LOBTable(tempIdx).temp_lob); DBMS_LOB.Trim(message, 0); DBMS_LOB.Copy(message, g_LOBTable(tempIdx).temp_lob, msgLen); end if; if tempIdx = -1 then wf_temp_lob.releaseLob(g_LOBTable, tempIdx); end if; exception when others then wf_temp_lob.releaseLob(g_LOBTable, tempIdx); WF_CORE.Context('WF_MAIL','LOBReplace',token, tokenValue); raise; end LOBReplace; -- LOBReplace -- To replace the given token in message with the token value -- IN -- message The CLOB message containing the tokens -- token The varchar token, complete with '&' prefix -- tokenValue The CLOB value to substitute for the token -- append The boolean flag to say if the token was NOT found, then -- append the tokenValue regardless -- procedure LOBReplace(message IN OUT NOCOPY CLOB, token IN VARCHAR2, tokenValue IN OUT NOCOPY CLOB, append IN BOOLEAN) is -- temp CLOB; tempIdx pls_integer; pos NUMBER; msgLen NUMBER; amount NUMBER; tokenValueLen number; continue boolean; offset number; sourcePos number; targetPos number; tokenLen pls_integer; nextChar varchar2(10); validToken boolean; xSet varchar2(100) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'|| '0123456789_'; tokenFound boolean; begin -- DBMS_LOB.CreateTemporary(temp, FALSE, DBMS_LOB.CALL); tempIdx := wf_temp_lob.getLob(g_LOBTable); offset := 1; sourcePos := 1; targetPos := 1; continue := TRUE; msgLen := DBMS_LOB.GetLength(message); tokenLen := length(token); tokenFound := FALSE; while continue loop pos := DBMS_LOB.Instr(message, token, offset, 1); if pos <> 0 then if (pos + tokenLen <= msgLen) then nextChar := upper(dbms_lob.substr(message, 1, pos + tokenLen)); if (instr(xSet, nextChar,1,1) > 0) then validToken := false; else validToken := true; end if; else validToken := true; end if; else validToken := false; continue := FALSE; end if; if continue then if validToken then amount := pos - sourcePos; DBMS_LOB.Copy(g_LOBTable(tempIdx).temp_lob, message, amount, targetPos, sourcePos); tokenValueLen := DBMS_LOB.GetLength(tokenValue); if (tokenValueLen > 0) then DBMS_LOB.Append(g_LOBTable(tempIdx).temp_lob, tokenValue); end if; sourcePos := pos + tokenLen; targetPos := DBMS_LOB.GetLength(g_LOBTable(tempIdx).temp_lob) + 1; tokenFound := TRUE; end if; offset := pos + tokenLen; else amount := msgLen - sourcePos + 1; if (amount > 0) then DBMS_LOB.Copy(g_LOBTable(tempIdx).temp_lob, message, amount, targetPos, sourcePos); end if; if (append and tokenFound = FALSE and msgLen > 0) then tokenValueLen := DBMS_LOB.GetLength(tokenValue); if tokenValueLen <> 0 then DBMS_LOB.Append(g_LOBTable(tempIdx).temp_lob, tokenValue); end if; end if; end if; end loop; if tokenFound then msgLen := DBMS_LOB.GetLength(g_LOBTable(tempIdx).temp_lob); DBMS_LOB.Trim(message, 0); DBMS_LOB.Copy(message, g_LOBTable(tempIdx).temp_lob, msgLen); end if; wf_temp_lob.releaseLob(g_LOBTable, tempIdx); exception when others then wf_temp_lob.releaseLob(g_LOBTable, tempIdx); WF_CORE.Context('WF_MAIL','LOBReplace',token, 'LOB'); raise; end LOBReplace; -- LOBSubstitute -- Template contains a max length of 4000 only. We require only a varchar2. -- IN -- template IN OUT NOCOPY VARCHAR2, -- n_nid IN NUMBER, -- n_code IN VARCHAR2, -- n_status IN VARCHAR2, -- n_to_role IN VARCHAR2, -- r_dName IN VARCHAR2, -- r_email IN VARCHAR2, -- n_start_Date IN DATE, -- n_due_Date IN DATE, -- n_end_Date IN DATE, -- n_from_user IN VARCHAR2, -- n_priority IN VARCHAR2, -- n_comment IN VARCHAR2, -- m_subject IN VARCHAR2, -- m_body IN OUT NOCOPY CLOB, -- err_Name IN OUT NOCOPY VARCHAR2, -- err_Message IN OUT NOCOPY VARCHAR2, -- err_Stack IN OUT NOCOPY VARCHAR2, -- n_timezone IN VARCHAR2) procedure LOBSubstitute(template IN OUT NOCOPY VARCHAR2, n_nid IN NUMBER, n_code IN VARCHAR2, n_status IN VARCHAR2, n_to_role IN VARCHAR2, r_dName IN VARCHAR2, r_email IN VARCHAR2, n_start_Date IN DATE, n_due_Date IN DATE, n_end_Date IN DATE, n_from_user IN VARCHAR2, n_priority IN VARCHAR2, n_comment IN VARCHAR2, m_subject IN VARCHAR2, m_header IN VARCHAR2, m_body IN OUT NOCOPY CLOB, err_Name IN OUT NOCOPY VARCHAR2, err_Message IN OUT NOCOPY VARCHAR2, err_Invalid IN OUT NOCOPY VARCHAR2, err_Expected IN OUT NOCOPY VARCHAR2, n_timezone IN VARCHAR2) is -- temp CLOB; tempIdx pls_integer; tempPos NUMBER := 0; pos NUMBER := 0; amper NUMBER := 0; msgLen NUMBER := 0; tknSize NUMBER := 0; tokenName VARCHAR2(60); tokenMatch BOOLEAN; n_due_date_text varchar2(50); n_start_date_text varchar2(50); n_priority_text varchar2(240); n_nidStr varchar2(50); eot NUMBER; begin -- BLAF requriement to display the date with TIME elelment n_due_date_text := to_char(n_due_date, Wf_Notification.g_nls_date_mask); n_start_date_text := to_char(n_start_date, Wf_Notification.g_nls_date_mask); if (n_priority > 66) then --Bug 2774891 fix - sacsharm --n_priority_text := wf_core.substitute('WFTKN', 'HIGH'); n_priority_text := wf_core.substitute('WFTKN', 'LOW'); elsif (n_priority > 33) then n_priority_text := wf_core.substitute('WFTKN', 'NORMAL'); else --Bug 2774891 fix - sacsharm --n_priority_text := wf_core.substitute('WFTKN', 'LOW'); n_priority_text := wf_core.substitute('WFTKN', 'HIGH'); end if; -- DBMS_LOB.CreateTemporary(temp, TRUE, DBMS_LOB.SESSION); -- DBMS_LOB.Open(temp, DBMS_LOB.LOB_READWRITE); tempIdx := wf_temp_lob.getLob(g_LOBTable); pos := 1; tempPos := 1; msgLen := length(template); while pos < msgLen loop -- Locate each instance of an ampersand and assume it is -- a token reference. amper := instr(template, '&', pos, 1); if amper = 0 then -- No ampers left. so write the rest of the CLOB if pos < msgLen then -- DBMS_LOB.Copy(temp, template, (msgLen - pos)+1, tempPos, pos); DBMS_LOB.Write(g_LOBTable(tempIdx).temp_lob, (msgLen - pos)+1, tempPos, substr(template, pos, (msgLen - pos)+1)); end if; EXIT; end if; -- Now we have the position of the amper, workout what -- token it is, if any. -- write from the last pos to the new token. if amper > pos then -- DBMS_LOB.Copy(temp, template, (amper - pos), tempPos, pos); DBMS_LOB.Write(g_LOBTable(tempIdx).temp_lob, (amper - pos), tempPos, substr(template, pos, (amper - pos))); tempPos := tempPos + ((amper - pos)); end if; pos := amper + 1; eot := amper; while ((eot <= msgLen) and (substr(template, eot, 1) not in (' ', g_newLine, g_tab))) loop eot := eot +1; end loop; tknSize := (eot - amper) - 1; tokenName := substr(template, amper+1, tknSize); tokenMatch := FALSE; if instr(tokenName,'NOTIFICATION_ID',1,1)=1 then n_nidStr := to_char(n_nid); if n_nidStr is not null then -- DBMS_LOB.WriteAppend(temp, length(n_nidStr), n_nidStr); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(n_nidStr), n_nidStr); tempPos := tempPos + length(n_nidStr); end if; pos := amper + length('NOTIFICATION_ID') + 1; tokenMatch := TRUE; end if; if instr(tokenName,'NOTIFICATION',1,1)=1 then if n_code is not null then -- DBMS_LOB.WriteAppend(temp, length(n_code), n_code); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(n_code), n_code); tempPos := tempPos + length(n_code); end if; pos := amper + length('NOTIFICATION') + 1; tokenMatch := TRUE; end if; if instr(tokenName,'STATUS',1,1)=1 then if n_status is not null then -- DBMS_LOB.WriteAppend(temp, length(n_status), n_status); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(n_status), n_status); tempPos := tempPos + length(n_status); end if; pos := amper + length('STATUS') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'TO_DNAME', 1, 1)=1 then if r_dname is not null then -- DBMS_LOB.WriteAppend(temp, length(r_dname), r_dname); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(r_dname), r_dname); tempPos := tempPos + length(r_dname); end if; pos := amper + length('TO_DNAME') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'TO_EMAIL', 1, 1)=1 then if r_email is not null then -- DBMS_LOB.WriteAppend(temp, length(r_email), r_email); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(r_email), r_email); tempPos := tempPos + length(r_email); end if; pos := amper + length('TO_EMAIL') + 1; tokenMatch := TRUE; end if; if instr(tokenName,'TO',1,1)=1 then if n_to_role is not null then -- DBMS_LOB.WriteAppend(temp, length(n_to_role), n_to_role); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(n_to_role), n_to_role); tempPos := tempPos + length(n_to_role); end if; pos := amper + length('TO') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'PRIORITY', 1, 1)=1 then if n_priority_text is not null then -- DBMS_LOB.WriteAppend(temp, length(n_priority_text), n_priority_text); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(n_priority_text), n_priority_text); tempPos := tempPos + length(n_priority_text); end if; pos := amper + length('PRIORITY') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'START_DATE', 1, 1)=1 then if n_start_date is not null then -- DBMS_LOB.WriteAppend(temp, length(n_start_date), n_start_date); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(n_start_date_text), n_start_date_text); tempPos := tempPos + length(n_start_date_text); end if; pos := amper + length('START_DATE') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'DUE_DATE', 1, 1)=1 then if n_due_date is not null then -- DBMS_LOB.WriteAppend(temp, length(n_due_date_text), n_due_date_text); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(n_due_date_text), n_due_date_text); tempPos := tempPos + length(n_due_date_text); end if; pos := amper + length('DUE_DATE') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'END_DATE', 1, 1)=1 then if n_end_date is not null then -- DBMS_LOB.WriteAppend(temp, length(n_end_date), n_end_date); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(n_end_date), n_end_date); tempPos := tempPos + length(n_end_date); end if; pos := amper + length('END_DATE') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'SENDER', 1, 1)=1 then if n_from_user is not null then -- DBMS_LOB.WriteAppend(temp, length(n_from_user), n_from_user); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(n_from_user), n_from_user); tempPos := tempPos + length(n_from_user); end if; pos := amper + length('SENDER') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'COMMENT', 1, 1)=1 then if n_comment is not null then -- DBMS_LOB.WriteAppend(temp, length(n_comment), n_comment); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(n_comment), n_comment); tempPos := tempPos + length(n_comment); end if; pos := amper + length('COMMENT') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'TIMEZONE', 1, 1)=1 then if n_timezone is not null then DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(n_timezone), n_timezone); tempPos := tempPos + length(n_timezone); end if; pos := amper + length('TIMEZONE') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'SUBJECT', 1, 1)=1 then if m_subject is not null then -- DBMS_LOB.WriteAppend(temp, length(m_subject), m_subject); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(m_subject), m_subject); tempPos := tempPos + length(m_subject); end if; pos := amper + length('SUBJECT') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'HEADER', 1, 1)=1 then if m_header is not null then --DBMS_LOB.WriteAppend(temp, length(m_header), m_header); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(m_header), m_header); tempPos := tempPos + length(m_header); end if; pos := amper + length('HEADER') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'BODY', 1, 1)=1 then if (m_body is not null and dbms_lob.getLength(m_body) > 0) or not g_isFwkNtf then DBMS_LOB.Append(g_LOBTable(tempIdx).temp_lob, m_body); tempPos := tempPos + DBMS_LOB.GetLength(m_body); pos := amper + length('BODY') + 1; tokenMatch := TRUE; else tokenMatch := FALSE; end if; end if; if instr(tokenName, 'MAIL_ERROR_NAME', 1, 1)=1 then if err_name is not null then -- DBMS_LOB.WriteAppend(temp, length(err_name), err_name); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(err_name), err_name); tempPos := tempPos + length(err_name); end if; pos := amper + length('MAIL_ERROR_NAME') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'MAIL_ERROR_MESSAGE', 1, 1)=1 then if err_message is not null then -- DBMS_LOB.WriteAppend(temp, length(err_message), err_message); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(err_message), err_message); tempPos := tempPos + length(err_message); end if; pos := amper + length('MAIL_ERROR_MESSAGE') + 1; tokenMatch := TRUE; end if; -- Bug# 2301881 Replacing error stack with invalid value -- found and the original response values -- to go with the WARNING message if instr(tokenName, 'MAIL_VALUE_FOUND', 1, 1)=1 then if err_invalid is not null then -- DBMS_LOB.WriteAppend(temp, length(err_invalid), err_invalid); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(err_invalid), err_invalid); tempPos := tempPos + length(err_invalid); end if; pos := amper + length('MAIL_VALUE_FOUND') + 1; tokenMatch := TRUE; end if; if instr(tokenName, 'MAIL_EXP_VALUES', 1, 1)=1 then if err_expected is not null then -- DBMS_LOB.WriteAppend(temp, length(err_expected), err_expected); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, length(err_expected), err_expected); tempPos := tempPos + length(err_expected); end if; pos := amper + length('MAIL_EXP_VALUES') + 1; tokenMatch := TRUE; end if; if not tokenMatch and amper > 0 then -- DBMS_LOB.WriteAppend(temp, 1, '&'); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, 1, '&'); tempPos := tempPos + 1; end if; end loop; -- msgLen := DBMS_LOB.GetLength(temp); msgLen := DBMS_LOB.GetLength(g_LOBTable(tempIdx).temp_lob); if msgLen > 0 then DBMS_LOB.Trim(m_body, 0); -- DBMS_LOB.Copy(m_body, temp, msgLen); DBMS_LOB.Copy(m_body, g_LOBTable(tempIdx).temp_lob, msgLen); end if; -- DBMS_LOB.Close(temp); -- DBMS_LOB.FreeTemporary(temp); wf_temp_lob.releaseLob(g_LOBTable, tempIdx); exception when others then wf_temp_lob.releaseLob(g_LOBTable, tempIdx); WF_CORE.Context('WF_MAIL','LOBSubstitute',tokenName, to_char(msgLen)||':'||to_char(pos)||':'||to_char(tempPos)); raise; end LOBSubstitute; -- OBSOLETELOBLineBreak - To add a new line every 900 characters -- or the nearst white space -- IN -- message -- line size default 900 procedure OBSOLETELOBLineBreak(message IN OUT NOCOPY CLOB, lineSize in INTEGER, maxLineSize INTEGER) is lnsize NUMBER; start_cnt NUMBER; end_cnt NUMBER; crpos NUMBER; tempOffset NUMBER; str_length NUMBER; -- temp CLOB; -- line CLOB; tempIdx pls_integer; lineIdx pls_integer; begin -- -- For every 900 character, we insert a newline just in case -- Because this whole message body may go out to the Unix SMTP gateway -- which does not like a line longer than 1000 characters. -- We do it 900 here just for safty. -- 2001/03/23 Changed algorithm to start at 900 point and then -- move to the nearest whitespace. -- lnsize := lineSize; start_cnt := 1; end_cnt := lnsize; str_length := DBMS_LOB.GetLength(message); if str_length = 0 then return; end if; -- DBMS_LOB.CreateTemporary(temp, TRUE, DBMS_LOB.CALL); -- DBMS_LOB.CreateTemporary(line, TRUE, DBMS_LOB.CALL); -- DBMS_LOB.Open(temp, DBMS_LOB.LOB_READWRITE); -- DBMS_LOB.Open(line, DBMS_LOB.LOB_READWRITE); tempIdx := wf_temp_lob.getLob(g_LOBTable); lineIdx := wf_temp_lob.getLob(g_LOBTable); while start_cnt < str_length loop -- use the existing newlines as a natural break crpos := DBMS_LOB.instr(message, g_newLine, start_cnt+1, 1) - start_cnt; if crpos > 0 and crpos < end_cnt then -- If there was a line bread and it is positioned less than the -- maximum allowed, then use that instead. end_cnt := crpos; else if crpos < 0 and (str_length - start_cnt) < end_cnt then end_cnt := str_length - start_cnt; end if; -- Move forward to the next white space. while (start_cnt + end_cnt < str_length) and DBMS_LOB.substr(message, 1, start_cnt + end_cnt) not in (' ', g_newLine, g_tab) and end_cnt < maxLineSize loop end_cnt := end_cnt + 1; end loop; -- We need to understand the full conditions underwhich -- the previous loop exited. All characters must be preserved -- and the line, no matter what can not exceed 900 characters. if end_cnt >= (maxLineSize) then end_cnt := lnsize; while (start_cnt + end_cnt > start_cnt) and DBMS_LOB.substr(message, 1, start_cnt + end_cnt) not in (' ', g_newLine, g_tab) and end_cnt > 0 loop end_cnt := end_cnt - 1; end loop; -- If we can not locate a white space, then use the default if end_cnt <= 0 then end_cnt := lnsize; end if; end if; end if; -- Ensure the last characters are not lost. -- DBMS_LOB.Trim(line, 0); DBMS_LOB.Trim(g_LOBTable(lineIdx).temp_lob, 0); tempOffset := dbms_lob.getLength(g_LOBTable(tempIdx).temp_lob) +1; if start_cnt + end_cnt >= str_length then -- line := DBMS_LOB.substr(message, start_cnt); -- DBMS_LOB.Copy(line, message, -- DBMS_LOB.GetLength(message) - start_cnt +1 , 1, -- start_cnt); -- DBMS_LOB.Copy(g_LOBTable(lineIdx).temp_lob, message, -- DBMS_LOB.GetLength(message) - start_cnt +1 , 1, -- start_cnt); DBMS_LOB.Copy(dest_lob => g_LOBTable(tempIdx).temp_lob, src_lob => message, amount => DBMS_LOB.GetLength(message) - start_cnt +1 , dest_offset => tempOffset, src_offset => start_cnt); else -- line := DBMS_LOB.substr(message, start_cnt, end_cnt); -- DBMS_LOB.Copy(line, message, end_cnt, 1, start_cnt); -- DBMS_LOB.Copy(g_LOBTable(lineIdx).temp_lob, message, end_cnt, 1, -- start_cnt); DBMS_LOB.Copy(dest_lob => g_LOBTable(tempIdx).temp_lob, src_lob => message, amount => end_cnt, dest_offset => tempOffset, src_offset => start_cnt); end if; -- temp := temp || line; -- DBMS_LOB.Append(temp, line); -- DBMS_LOB.Append(g_LOBTable(tempIdx).temp_lob, -- g_LOBTable(lineIdx).temp_lob); -- If there is a newline at this point, -- then do not bother with another. if DBMS_LOB.substr(message, 1, start_cnt + end_cnt) <> g_newLine then -- DBMS_LOB.WriteAppend(temp, 1, g_newLine); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, 1, g_newLine); end if; -- We do not want to start the new line with the space. if DBMS_LOB.substr(message, 1, start_cnt + end_cnt) = ' ' then start_cnt := start_cnt + end_cnt + 1; else start_cnt := start_cnt + end_cnt; end if; end_cnt := lnsize; end loop; DBMS_LOB.Trim(message, 0); -- DBMS_LOB.Copy(message, temp, DBMS_LOB.GetLength(temp), 1, 1); DBMS_LOB.Copy(message, g_LOBTable(tempIdx).temp_lob, DBMS_LOB.GetLength(g_LOBTable(tempIdx).temp_lob), 1, 1); -- if DBMS_LOB.IsOpen(temp)=1 then -- DBMS_LOB.Close(temp); -- DBMS_LOB.FreeTemporary(temp); -- end if; -- if DBMS_LOB.IsOpen(line)=1 then -- DBMS_LOB.Close(line); -- DBMS_LOB.FreeTemporary(line); -- end if; wf_temp_lob.releaseLob(g_LOBTable, lineIdx); wf_temp_lob.releaseLob(g_LOBTable, tempIdx); end OBSOLETELOBLineBreak; -- LOBLineBreak -- To wrap the text at textWidth but no line can exceed maLineSize -- NOTE: -- In order to be performant, the textWidth is given -- as a guide. If not natural break appears, then the nearest -- whitespace after textWidth will be used. procedure LOBLineBreak(message IN OUT NOCOPY CLOB, textWidth in INTEGER, maxLineSize INTEGER) is lnsize NUMBER; bfr_start NUMBER; chk_start NUMBER; line_size NUMBER; crpos NUMBER; wsLoc NUMBER; doc_length NUMBER; tempOffset NUMBER; insertNewLine boolean; whitespace boolean; tempIdx pls_integer; lineBuffer varchar2(32000); begin lnsize := textWidth; bfr_start := 1; chk_start := 1; line_size := lnsize; doc_length := DBMS_LOB.GetLength(message); if doc_length = 0 or doc_length < line_size then return; end if; tempIdx := wf_temp_lob.getLob(g_LOBTable); -- While the block start counter is less than the length of the -- document, then start scanning to make sure that the document -- conforms to the requirements of the parameters. while chk_start < doc_length loop wsLoc := -1; insertNewLine := false; -- use the existing newlines as a natural break. If found -- crpos will be the relative position from chk_start. crpos := DBMS_LOB.instr(lob_loc => message, pattern => g_newLine, offset => chk_start, nth => 1) - chk_start; -- If the position of crpos makes the respective line less than -- the required linesize, then use that. Otherwise, go looking for -- some whitespace to insert a new line. if crpos > 0 and crpos < line_size then line_size := crpos +1; insertNewLine := false; else -- Move forward to the next white space. wsLoc := DBMS_LOB.instr(lob_loc => message, pattern => ' ', offset => (chk_start + line_size), nth => 1) - chk_start; if (wsLoc > 0 and wsLoc < maxLineSize) then line_size := wsLoc; insertNewLine := true; whitespace := true; else wsLoc := DBMS_LOB.instr(lob_loc => message, pattern => g_newLine, offset => chk_start + line_size, nth => 1) - chk_start; if (wsLoc > 0 and wsLoc < maxLineSize) then line_size := wsLoc; wsLoc := 0; insertNewLine := false; whitespace := true; else wsLoc := DBMS_LOB.instr(lob_loc => message, pattern => g_tab, offset => chk_start + line_size, nth => 1) - chk_start; if (wsLoc > 0 and wsLoc < maxLineSize) then line_size := wsLoc; insertNewLine := true; whitespace := true; else line_size := maxLineSize; insertNewLine := true; whitespace := true; end if; end if; end if; end if; -- Ensure the last characters are not lost. if chk_start + line_size >= doc_length then -- copy from the chk_start to the end of the document. tempOffset := dbms_lob.getLength(g_LOBTable(tempIdx).temp_lob) +1; DBMS_LOB.Copy(dest_lob => g_LOBTable(tempIdx).temp_lob, src_lob => message, amount => doc_length - bfr_start +1 , dest_offset => tempOffset, src_offset => bfr_start); elsif insertNewLine then -- Copy partial and make note of the current position -- This is to minimise the number of calls to dbms_lob.copy. tempOffset := dbms_lob.getLength(g_LOBTable(tempIdx).temp_lob) +1; DBMS_LOB.Copy(dest_lob => g_LOBTable(tempIdx).temp_lob, src_lob => message, amount => (chk_start - bfr_start) + line_size, dest_offset => tempOffset, src_offset => bfr_start); DBMS_LOB.WriteAppend(g_LOBTable(tempIdx).temp_lob, 1, g_newLine); bfr_start := chk_start + line_size +1; end if; if wsLoc > 0 then chk_start := chk_start + line_size + 1; else chk_start := chk_start + line_size; end if; line_size := lnsize; end loop; DBMS_LOB.Trim(message, 0); DBMS_LOB.Copy(message, g_LOBTable(tempIdx).temp_lob, DBMS_LOB.GetLength(g_LOBTable(tempIdx).temp_lob), 1, 1); DBMS_LOB.Trim(g_LOBTable(tempIdx).temp_lob, 0); wf_temp_lob.releaseLob(g_LOBTable, tempIdx); end LOBLineBreak; -- GetMailTo - Construct MailTo Section (PRIVATE) -- IN -- notification id -- notification tag -- notification reply to -- notification subject -- RETURN -- mailto html tag with the subject and body procedure GetMailTo(nid in number, n_tag in varchar2, reply_to in varchar2, subject in varchar2, doc in out nocopy CLOB) is -- SQL Statement for fetching URL RESPONSE attributes. cursor c1 is select WMA.NAME, WMA.DISPLAY_NAME, WNA.TEXT_VALUE, WMA.DESCRIPTION from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES_VL WMA where WNA.NOTIFICATION_ID = nid and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE and WN.MESSAGE_NAME = WMA.MESSAGE_NAME and WMA.NAME = WNA.NAME and WMA.SUBTYPE = 'RESPOND' and WMA.TYPE = 'URL' order by WMA.SEQUENCE; lov varchar2(64); lov_list varchar2(240); str_buffer varchar2(32000); -- buffer CLOB; bufferIdx pls_integer; auto_answer varchar2(64); newline_pos number; disp_name varchar2(80); attr_type varchar2(8); attr_format varchar2(240); attr_value varchar2(32000); attr_desc varchar2(240); encoded_tag varchar2(240); begin -- Clear buffer str_buffer := ''; -- DBMS_LOB.createTemporary(buffer, FALSE, dbms_lob.call); bufferIdx := wf_temp_lob.getLob(g_LOBTable); -- URL RESPONSE attributes overrides the normal RESULT attributes. -- So, my goal here is to check for this case. -- URL RESPONSE attributes is going to appear as a anchor and don't have -- to construct the MAILTO html tag stuff that we do for the normal -- RESULT attribute. -- NOTE: Please do know that I don't want to destablize the existing code -- for the normal RESULT attribute MAILTO handling so that I am coding -- these two cases seperately. -- for each response variable for rec in c1 loop str_buffer := str_buffer||'

'; if (rec.description is not null) then str_buffer := str_buffer||rec.description||'

'; end if; str_buffer := str_buffer|| ''; str_buffer := str_buffer||' '||rec.display_name; str_buffer := str_buffer||''||''; str_buffer := str_buffer||g_newLine; -- DBMS_LOB.writeAppend(buffer, length(str_buffer), str_buffer); DBMS_LOB.writeAppend(g_LOBTable(bufferIdx).temp_lob, length(str_buffer), str_buffer); str_buffer := ''; end loop; -- if (dbms_lob.GetLength(buffer) > 0) then if (dbms_lob.GetLength(g_LOBTable(bufferIdx).temp_lob) > 0) then -- LOBReplace(doc, '&'||'MAILTO', buffer, FALSE); LOBReplace(doc, '&'||'MAILTO', g_LOBTable(bufferIdx).temp_lob, FALSE); RETURN; end if; -- -- Normal RESULT attribute handling -- begin select WMA.DISPLAY_NAME, WMA.TYPE, WMA.FORMAT, decode(WMA.TYPE, 'VARCHAR2', decode(WMA.FORMAT, '', WNA.TEXT_VALUE, substr(WNA.TEXT_VALUE, 1, to_number(WMA.FORMAT))), 'NUMBER', decode(WMA.FORMAT, '', to_char(WNA.NUMBER_VALUE), to_char(WNA.NUMBER_VALUE, WMA.FORMAT)), 'DATE', decode(WMA.FORMAT, '', to_char(WNA.DATE_VALUE), to_char(WNA.DATE_VALUE, WMA.FORMAT)), 'LOOKUP', WNA.TEXT_VALUE, WNA.TEXT_VALUE), WMA.DESCRIPTION into disp_name, attr_type, attr_format, attr_value, attr_desc from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES_VL WMA where WNA.NOTIFICATION_ID = nid and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE and WN.MESSAGE_NAME = WMA.MESSAGE_NAME and WMA.NAME = WNA.NAME and WMA.SUBTYPE = 'RESPOND' and WMA.NAME = 'RESULT' and WMA.TYPE not in ('FORM', 'URL'); -- We can only construct answer button or mailto link if is lookup if (attr_type <> 'LOOKUP') then auto_answer := 'Respond'; else -- If is LOOKUP RESULT attribute, we need to show the description here. if (attr_desc is not null) then str_buffer := str_buffer||'

'||attr_desc; end if; auto_answer := attr_format; end if; exception when no_data_found then auto_answer := 'Respond'; end; -- Encode any special characters encoded_tag := UrlEncode(n_tag); -- Construct mailto syntax str_buffer := str_buffer||'

'||disp_name||': '||''|| g_noResult||''||''; -- DBMS_LOB.WriteAppend(buffer, length(str_buffer), str_buffer); DBMS_LOB.WriteAppend(g_LOBTable(bufferIdx).temp_lob, length(str_buffer), str_buffer); str_buffer := ''; else lov_list := GetLovListInternal(auto_answer); lov_list := substr(lov_list, 1, length(lov_list)-1); while (lov_list is not null) loop newline_pos := instr(lov_list, g_newLine); if (newline_pos = 0) then lov := lov_list; else lov := substr(lov_list, 1, newline_pos - 1); end if; -- GetMailToBody(nid, lov, buffer); GetMailToBody(nid, lov, g_LOBTable(bufferIdx).temp_lob); str_buffer :=str_buffer||'%0D%0A%0D%0A'||encoded_tag||'">'|| ''||GetLovMeaning(attr_format, lov)|| ''||''; if (newline_pos = 0) then lov_list := null; if (g_moreinfo = 'REQ') then str_buffer := str_buffer || GetMoreInfoMailTo(nid, n_tag, reply_to, subject); end if; else lov_list := substr(lov_list, newline_pos+1, length(lov_list) - newline_pos); str_buffer := str_buffer||g_newLine; str_buffer := str_buffer||'&'||'nbsp;'||'&'||'nbsp;'|| ' nid, n_key => n_key, n_sig_policy => n_sig_policy, n_override_agent => agent); n_click_here := n_click_here || url; else if (agent is null) then if wf_mail.send_accesskey then n_click_here := n_click_here||g_webAgent ||'/WFA_HTML.DetailLink?nid='||to_char(nid) ||'&'||'nkey='||n_key ||'&'||'agent='||g_webAgent; else n_click_here := n_click_here||g_webAgent ||'/'|| wfa_sec.DirectLogin(nid); end if; else if wf_mail.send_accesskey then n_click_here := n_click_here||agent ||'/WFA_HTML.DetailLink?nid='||to_char(nid) ||'&'||'nkey='||n_key ||'&'||'agent='||agent; else n_click_here := n_click_here||agent ||'/'|| wfa_sec.DirectLogin(nid); end if; end if; end if; n_click_here := n_click_here||'">'||n_disp_click||''; if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(WF_LOG_PKG.level_statement,'wf.plsql.WF_MAIL.newLOBTag', 'URL: {'||n_click_here||'}'); end if; return n_click_here; end getClickHereResponse; -- -- setContext (PRIVATE) -- Set the context by executing the selector function -- IN -- nid - Notification id -- procedure setContext(nid NUMBER) is context VARCHAR2(2000); /* Bug 2312742 */ callback VARCHAR2(100); tvalue varchar2(4000); nvalue number; dvalue date; sqlbuf varchar2(4000); l_dummy varchar2(1); begin SELECT context, callback into context, callback FROM wf_notifications where notification_id = nid; wf_engine.preserved_context := FALSE; if (callback is not null) then -- ### callback is from table -- BINDVAR_SCAN_IGNORE sqlbuf := 'begin '||callback|| '(:p1, :p2, :p3, :p4, :p5, :p6, :p7); end;'; execute immediate sqlbuf using in 'TESTCTX', in context, in l_dummy, in l_dummy, in out tvalue, in out nvalue, in out dvalue; if (tvalue in ('FALSE', 'NOTSET')) then execute immediate sqlbuf using in 'SETCTX', in context, in l_dummy, in l_dummy, in out tvalue, in out nvalue, in out dvalue; end if; end if; exception when others then WF_CORE.Context('WF_MAIL','SetContext',to_char(nid)); raise; end setContext; -- PRIVATE -- Gets the server timezone message function getTimezone(contentType in varchar2) return varchar2 is timezone varchar2(240); begin if g_install='EMBEDDED' AND FND_TIMEZONES.timezones_enabled = 'Y' then if g_timezoneName is null then g_timezoneName := FND_TIMEZONES.GET_NAME(FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE); end if; if length(g_timezoneName) > 0 then if contentType = g_ntfDocText then wf_core.token('TIMEZONE', g_timezoneName); timezone := wf_core.Substitute('WFTKN','WFNTF_TIMEZONE'); else wf_core.token('TIMEZONE', ''|| g_timezoneName||''); timezone := wf_core.Substitute('WFTKN','WFNTF_TIMEZONE'); end if; else timezone := ''; end if; else timezone := ''; end if; return timezone; end getTimezone; -- Get_Last_Question (Private) -- Retrieves the last More Information question -- If no question exists, then it is blank. -- IN -- p_nid - Notification ID -- OUT -- p_last_ques - Returned last question procedure Get_Last_Question(p_nid in number, p_last_ques out nocopy varchar2) is CURSOR c_ques IS SELECT user_comment FROM wf_comments WHERE notification_id = p_nid AND action = 'QUESTION' ORDER BY comment_date desc; begin -- Fetch the last question asked open c_ques; fetch c_ques into p_last_ques; if (c_ques%notfound) then p_last_ques := ''; end if; close c_ques; end Get_Last_Question; -- Get_Action_History (Private) -- Scans the notifications message definition and determines if Action History -- should be displayed -- IN -- p_nid - Notification Id -- p_lang - User Language -- OUT -- p_html_history - Action History in HTML -- p_text_history - Action History in Text -- p_last_ques - Last Question Asked if More Infor Requested procedure Get_Action_History(p_nid in number, p_lang in varchar2, p_html_history out nocopy varchar2, p_text_history out nocopy varchar2, p_last_ques out nocopy varchar2) is l_text_body varchar2(4000); l_html_body varchar2(4000); l_comm_cnt pls_integer; l_text_history varchar2(32000); l_html_history varchar2(32000); l_get_html boolean; l_get_text boolean; CURSOR c_comm IS SELECT count(1) FROM wf_comments WHERE action_type in ('REASSIGN', 'QA') AND notification_id = p_nid; begin -- Fetch the last question asked Get_Last_Question(Get_Action_History.p_nid, Get_Action_History.p_last_ques); l_get_html := false; l_get_text := false; SELECT wm.body, wm.html_body INTO l_text_body, l_html_body FROM wf_notifications n, wf_messages_vl wm WHERE n.notification_id = p_nid AND n.message_name = wm.name AND n.message_type = wm.type; -- FYI notification if (wf_mail.Get_FYI_Flag) then open c_comm; fetch c_comm into l_comm_cnt; if (c_comm%notfound) then l_comm_cnt := 0; end if; -- If HISTORY macro is defined, Action History would appear anyways. If the FYI notification -- was reassigned at least once, include the Action History if even macro is not defined if (l_comm_cnt > 0) then if (instrb(l_text_body, 'WF_NOTIFICATION(HISTORY)') = 0) then l_get_text := true; end if; if (instrb(l_html_body, 'WF_NOTIFICATION(HISTORY)') = 0) then l_get_html := true; end if; end if; close c_comm; -- Response Required notification else -- If HISTORY macro is defined, Action History would appear anyways. Otherwise -- display it in the e-mail if (instrb(l_text_body, 'WF_NOTIFICATION(HISTORY)') = 0) then l_get_text := true; end if; if (instrb(l_html_body, 'WF_NOTIFICATION(HISTORY)') = 0) then l_get_html := true; end if; end if; -- Call the GetComments2 procedure to get the Action History if (l_get_text) then Wf_Notification.GetComments2(p_nid => Get_Action_History.p_nid, p_display_type => wf_notification.doc_text, p_hide_reassign => 'N', p_hide_requestinfo => 'N', p_action_history => l_text_history); end if; if (l_get_html)then Wf_Notification.GetComments2(p_nid => Get_Action_History.p_nid, p_display_type => wf_notification.doc_html, p_hide_reassign => 'N', p_hide_requestinfo => 'N', p_action_history => l_html_history); end if; p_text_history := l_text_history; p_html_history := l_html_history; exception when others then if (wf_log_pkg.level_exception >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(WF_LOG_PKG.level_exception, 'wf.plsql.wf_mail.Get_Action_History', 'Get_Action_History failed. Error: ' || sqlerrm); end if; p_text_history := ''; p_html_history := ''; end Get_Action_History; -- GetLOBMessage3 - get email message data as a LOB -- -- IN -- notification id -- mailer node name -- web agent path -- reply to -- recipient role -- lanague -- territory -- notification preference -- dsiplay name -- Render BODY token flag -- OUT -- message subject -- message body (text) -- message body (html) procedure GetLOBMessage3( nid in number, node in varchar2, agent in varchar2, replyto in varchar2, recipient in varchar2, language in varchar2, territory in varchar2, ntf_pref in varchar2, email in varchar2, dname in varchar2, renderBody in varchar2, subject out NOCOPY varchar2, body_atth out NOCOPY varchar2, error_result in out NOCOPY varchar2, bodyToken in out NOCOPY varchar2 ) as n_status varchar2(8); n_mstatus varchar2(8); n_key varchar2(80); n_to_role varchar2(320); n_from_user varchar2(320); -- Bug# 2094159 n_due_date date; n_start_date date; n_end_date date; n_priority number; n_comment varchar2(4000); n_subject varchar2(2000); n_response varchar2(32000); n_direct varchar2(3); n_click_here varchar2(4000); n_disp_click varchar2(240); n_text_timezone varchar2(240); n_html_timezone varchar2(240); r_dname varchar2(360); r_email varchar2(2000); r_ntf_pref varchar2(240); r_language varchar2(4000); r_territory varchar2(4000); t_type varchar2(100); t_name varchar2(100); t_subject varchar2(240); t_text_body varchar2(4000); t_html_body varchar2(4000); t_hdrRequired boolean; m_html varchar2(32000); mailTo VARCHAR2(32000); t_headerText varchar2(32000); n_headerText varchar2(32000); t_headerHTML varchar2(32000); n_headerHTML varchar2(32000); err_name varchar2(30); err_message varchar2(2000); err_stack varchar2(4000); str_dummy varchar2(4000) := NULL; fyi pls_integer; body_start pls_integer; body_end pls_integer; tmp_start pls_integer; tmp_end pls_integer; tag_pos pls_integer; dir_pos pls_integer; start_cnt pls_integer; end_cnt pls_integer; str_length pls_integer; end_of_message boolean; buffer VARCHAR2(32000); -- temp CLOB; -- template_text CLOB; -- template_html CLOB; no_program_unit exception; pragma exception_init(no_program_unit, -6508); dummy varchar2(4000); -- Bug# 2301881 variables to handle invalid response error message err_invalid varchar2(2000); err_expected varchar2(2000); n_sig_policy varchar2(100); n_nid_str varchar2(200); -- More Info feature bug 2282139 n_more_info_role varchar2(320); n_mailto varchar2(10000); n_text_history varchar2(32000); n_html_history varchar2(32000); n_last_ques varchar2(4000); n_dir varchar2(16); step varchar2(200); textForHtml boolean; htmlBodyPos pls_integer; textBodyPos pls_integer; begin -- 3532615 Moved these from being initialized at the session level -- back to the notification level. g_ntfHistory := wf_core.translate('WFNTF_HISTORY'); g_ntfActionHistory := wf_core.translate('WFNTF_ACTION_HISTORY'); g_moreInfoAPrompt := wf_core.translate('WFNTF_MOREINFO_APROMPT'); g_moreInfoAnswer := wf_core.translate('WFNTF_MOREINFO_ANSWER'); g_moreInfoQPrompt := wf_core.translate('WFNTF_MOREINFO_QPROMPT'); g_moreInfoSubject := wf_core.translate('WFNTF_MOREINFO'); g_moreInfoSubmit := wf_core.translate('WFNTF_MOREINFO_SUBMIT'); g_moreInfoQuestion := wf_core.translate('WFNTF_MOREINFO_QUESTION'); g_moreInfoFrom := wf_core.translate('WFNTF_MOREINFO_FROM'); g_moreInfoRequested := wf_core.translate('WFNTF_MOREINFO_REQUESTED'); g_moreInfoRequestee := wf_core.translate('WFNTF_MOREINFO_REQUESTEE'); g_wfmonId := wf_core.translate('WFMON_ID'); g_to := wf_core.translate('TO'); g_from := wf_core.translate('FROM'); g_beginDate := wf_core.translate('BEGIN_DATE'); g_dueDate2 := wf_core.translate('DUE_DATE'); g_notificationId := wf_core.translate('NOTIFICATION_ID'); g_priority := wf_core.translate('PRIORITY'); g_dueDate := wf_core.translate('WFMON_DUE_DATE'); g_invalidRemarks := wf_core.translate('WFMLR_INVALID_REMARKS'); g_forExample := wf_core.translate('WFMLR_FOR_EXAMPLE'); g_soOn := wf_core.translate('WFMLR_SO_ON'); g_none := wf_core.translate('WFMLR_NONE'); g_truncate := wf_core.translate('WFNTF_TRUNCATE'); g_noResult := wf_core.translate('WFNTF_NO_RESULT'); g_Id := wf_core.translate('ID'); step := 'Getting notification information'; -- Get notification information begin select STATUS, MAIL_STATUS, ACCESS_KEY, PRIORITY, USER_COMMENT, BEGIN_DATE, END_DATE, DUE_DATE, FROM_USER, MORE_INFO_ROLE into n_status, n_mstatus, n_key, n_priority, n_comment, n_start_date, n_end_date, n_due_date, n_from_user, n_more_info_role from WF_NOTIFICATIONS where NOTIFICATION_ID = nid; exception when no_data_found then wf_core.token('NID', to_char(nid)); wf_core.raise('WFNTF_NID'); end; n_to_role := recipient; r_language := language; r_territory := territory; r_ntf_pref := ntf_pref; r_email := email; r_dname := dname; if (wf_mail.test_flag = TRUE) then n_mstatus := 'MAIL'; if (n_status not in ('OPEN','CANCELED','CLOSED')) then n_status := 'OPEN'; end if; end if; -- More information processing g_moreinfo := NULL; if (wf_notification.HideMoreInfo(nid) = 'N') then if(n_more_info_role is not null) then n_to_role := n_more_info_role; g_to_role := n_more_info_role; -- Flags that template for More Info submission needs to be used g_moreinfo := 'SUB'; else -- Flags that template for More Info request needs to be used g_to_role := n_to_role; g_moreinfo := 'REQ'; end if; end if; r_ntf_pref := nvl(r_ntf_pref, 'QUERY'); step := 'Getting Signature policy'; -- Bug 2375920 get signature policy for the notification Wf_Mail.GetSignaturePolicy(nid, n_sig_policy); -- Bug 2375920 Process the email message based on the signature policy ProcessSignaturePolicy(nid, n_sig_policy, n_status, n_mstatus, n_key, node, t_type, t_name, n_nid_str); if isBiDi(r_language) then WF_NOTIFICATION.Set_NTF_Table_Direction('R'); else WF_NOTIFICATION.Set_NTF_Table_Direction('L'); end if; -- PLSQL Action History for the notification will not be explicitly processed by the -- mailer now. The notification sub-system would handle this as part of GetFullBody. -- if (renderBody = 'Y') then -- step := 'Getting action history'; -- Get_Action_History(nid, r_language, n_html_history, n_text_history, -- n_last_ques); -- else Get_Last_Question(nid, n_last_ques); n_html_history := ''; n_text_history := ''; -- end if; step := 'Getting template'; -- Get template begin select SUBJECT, BODY, HTML_BODY into t_subject, t_text_body, t_html_body from WF_MESSAGES_VL where NAME = t_name and TYPE = t_type; exception when no_data_found then wf_core.token('NAME', t_name); wf_core.token('TYPE', t_type); wf_core.raise('WFNTF_MESSAGE'); end; g_isFwkNtf := true; if (t_name in ('VIEW_FROMUI', 'VIEW_FROMUI_FYI')) then t_hdrRequired := TRUE; elsif renderBody = 'Y' then t_hdrRequired := TRUE; g_isFwkNtf := false; else t_hdrRequired := FALSE; g_isFwkNtf := true; end if; step := 'Getting timezone details'; n_text_timezone := getTimezone(g_ntfDocText); n_html_timezone := getTimezone(g_ntfDocHtml); step := 'Getting subject'; n_subject := WF_NOTIFICATION.GetSubject(nid, 'text/plain'); -- We will always fetch plain text version of the message because -- Because for sendmail MAILATTH case, we need to send out html message -- body as attachment and then the plain text message as the body. -- For MAPI MAILATTH and MAILHTML cases, same thing. -- DBMS_LOB.CreateTemporary(g_text_message, true, dbms_lob.SESSION); -- DBMS_LOB.Open(g_text_message, DBMS_LOB.LOB_READWRITE); -- DBMS_LOB.CreateTemporary(g_html_message, true, dbms_lob.SESSION); -- DBMS_LOB.Open(g_html_message, DBMS_LOB.LOB_READWRITE); -- we will request a temp LOB from the pool only if necessary -- we donot need the text message if the ntf pref is MAILHTML -- this will reduce the time spent in unecessarily processing -- the text LOB Message t_headerText := ''; t_headerHTML := ''; step := 'Getting body'; if (r_ntf_pref in ('MAILTEXT', 'MAILATTH')) then g_text_messageIdx := wf_temp_lob.getLob(g_LOBTable); if (renderbody = 'Y') then end_of_message := FALSE; step := 'Getting text/plain body'; begin while not (end_of_message) loop WF_NOTIFICATION.GetFullBody(nid, buffer, end_of_message, g_ntfDocText); if buffer is not null and length(buffer) > 0 then DBMS_LOB.WriteAppend(g_LOBTable(g_text_messageIdx).temp_lob, length(buffer), buffer); end if; end loop; exception when others then wf_core.context('WF_MAIL','GetLOBMessage3', 'nid => '||to_char(nid), 'r_ntf_pref => '||r_ntf_pref); wf_core.token('ERROR',sqlerrm); wf_core.raise('WFMLR_NTFERR'); end; -- Get the table of header attributes. Use a PL/SQL document -- API format. end if; -- RENDERBODY if (t_hdrRequired = TRUE) then GetHeaderTable(r_language||':'||to_char(nid), g_ntfDocText, t_headerText, str_dummy); end if; end if; if (r_ntf_pref in ('MAILHTML', 'MAILATTH', 'MAILHTM2')) then g_html_messageIdx := wf_temp_lob.getLob(g_LOBTable); if (renderbody = 'Y') then end_of_message := FALSE; step := 'Getting text/html body'; begin while not (end_of_message) loop WF_NOTIFICATION.GetFullBody(nid, buffer, end_of_message, g_ntfDocHtml); if buffer is not null and length(buffer) > 0 then DBMS_LOB.WriteAppend(g_LOBTable(g_html_messageIdx).temp_lob, length(buffer), buffer); end if; end loop; exception when others then wf_core.context('WF_MAIL','GetLOBMessage3', 'nid => '||to_char(nid), 'r_ntf_pref => '||r_ntf_pref); wf_core.token('ERROR',sqlerrm); wf_core.raise('WFMLR_NTFERR'); end; -- Extracts content between and if there is body tag -- This is to deal with people import the whole html file to the -- html message body through the builder. -- The logic here is that if we don't see and , then -- this is already just a html portion. Otherwise, extract -- the content in between and . -- Bug 3886396 - nested HTML does not really affect the e-mail -- formatting. Removing the BODY tag processing. -- step := 'Extracting the content between the body tags'; -- body_start := 1; -- body_start := DBMS_LOB.instr(g_LOBTable(g_html_messageIdx).temp_lob, -- ' 0) then -- body_start := DBMS_LOB.instr(g_LOBTable(g_html_messageIdx).temp_lob, '>', body_start, 1) + 1; -- It is possible to have nested HTML, so we need to skip to -- the last tag. The negative offset does not work -- for LOBs so need to loop through to the last. -- tmp_start := body_start; -- tmp_end := 0; -- loop -- tmp_end := DBMS_LOB.instr(g_LOBTable(g_html_messageIdx).temp_lob, '', tmp_start, 1); -- if (tmp_end = 0) then -- tmp_end := DBMS_LOB.instr(g_LOBTable(g_html_messageIdx).temp_lob, '', tmp_start, 1); -- end if; -- exit when tmp_end = 0; -- body_end := tmp_end; -- tmp_start := tmp_end +7; -- end loop; -- if (body_end = 0) then -- body_end := DBMS_LOB.GetLength(g_LOBTable(g_html_messageIdx).temp_lob); -- else -- body_end := body_end - 1; -- end if; -- DBMS_LOB.Copy(dest_lob => g_LOBTable(g_html_messageIdx).temp_lob, -- src_lob => g_LOBTable(g_html_messageIdx).temp_lob, -- amount => (body_end - body_start)+1, -- dest_offset => 1, -- src_offset => body_start); -- DBMS_LOB.Trim(lob_loc => g_LOBTable(g_html_messageIdx).temp_lob, -- newlen => (body_end - body_start)+1); -- end if; -- 3432204 - Obsoleted call as this function is now performed -- by the Java Mail API. -- LOBLineBreak(g_LOBTable(g_html_messageIdx).temp_lob, 900, 999); end if; -- RENDERBODY if (t_hdrRequired = TRUE) then GetHeaderTable(r_language||':'||to_char(nid), g_ntfDocHtml, t_headerHTML, str_dummy); end if; end if; -- DBMS_LOB.CreateTemporary(template_text, TRUE, DBMS_LOB.CALL); -- DBMS_LOB.CreateTemporary(template_html, TRUE, DBMS_LOB.CALL); -- DBMS_LOB.Open(template_text, DBMS_LOB.LOB_READWRITE); -- DBMS_LOB.Open(template_html, DBMS_LOB.LOB_READWRITE); -- DBMS_LOB.Write(template_text, length(t_text_body), 1, t_text_body); step := 'Getting click-here section'; -- Get Click here Response display value begin select DESCRIPTION into n_disp_click from WF_MESSAGE_ATTRIBUTES_TL where MESSAGE_TYPE = t_type and MESSAGE_NAME = t_name and NAME = 'CLICK_HERE_RESPONSE' and LANGUAGE = userenv('LANG'); exception when NO_DATA_FOUND then -- ignore if this attribute does not exist null; end; step := 'Getting error information for invalid response'; -- Retrieve error attributes for INVALID message -- Bug# 2301881 Replacing err_stack with err_invalid and err_expected -- to make the WARNING message to the responder more -- user-friendly if (t_name in ('OPEN_INVALID', 'OPEN_INVALID_MORE_INFO')) then begin err_name := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_NAME'); err_message := Wf_Notification.GetAttrText(nid, 'MAIL_ERROR_MESSAGE'); err_invalid := Wf_Notification.GetAttrText(nid, 'MAIL_VALUE_FOUND'); err_expected := Wf_Notification.GetAttrText(nid, 'MAIL_EXP_VALUES'); exception when others then null; end; end if; -- If there is no html template available, use the plain text one. step := 'Setting content to the template'; if (t_html_body is null) then t_html_body := replace(t_text_body, g_newLine, '
'||g_newLine); -- Ensure the direction of the text is correct for the language textForHtml := true; if isBiDi(r_language) then t_html_body := ''||t_html_body; else t_html_body := ''||t_html_body; end if; else -- Ensure that the direction of the text is correctly specified. if isBiDi(r_language) then tag_pos := instrb(upper(t_html_body), ' 0 then dir_pos := instrb(upper(t_html_body), ' DIR="', 1); if dir_pos = 0 then buffer := substrb(t_html_body, 1, tag_pos+4); buffer := buffer||' DIR="RTL" '||substrb(t_html_body, tag_pos+5); t_html_body := buffer; end if; end if; end if; end if; -- DBMS_LOB.Write(template_html, length(t_html_body), 1, t_html_body); if wf_mail.direct_response then n_direct := '[2]'; else n_direct := NULL; end if; -- More info feature if (g_moreinfo = 'SUB') then n_response := g_moreInfoAPrompt || ': "<' || g_moreInfoAnswer ||'>"'; n_response := n_response || g_newLine; else if wf_mail.direct_response then n_response := GetEmailDirectResponse(nid); else n_response := GetEmailResponse(nid); end if; end if; -- Substitute htmlBodyPos := instrb(t_html_body, '&'||'BODY',1, 1); textBodyPos := instrb(t_text_body, '&'||'BODY',1, 1); if (htmlBodyPos > 0 or textBodyPos > 0) then bodyToken := 'Y'; else bodyToken := 'N'; end if; step := 'Performing token substitution'; n_subject := Substitute(t_subject, nid, n_nid_str, n_status, n_to_role, r_dname, r_email, n_start_date, n_due_date, n_end_date, n_from_user, n_priority, n_comment, n_subject, str_dummy, str_dummy, err_name, err_message, err_invalid, err_expected, n_text_timezone); if (r_ntf_pref in ('MAILTEXT', 'MAILATTH')) then n_headerText := Substitute(t_headerText, nid, n_nid_str, n_status, n_to_role, r_dname, r_email, n_start_date, n_due_date, n_end_date, n_from_user, n_priority, n_comment, n_subject, str_dummy, str_dummy, err_name, err_message, err_invalid, err_expected, n_text_timezone); LOBSubstitute(t_text_body, nid, n_nid_str, n_status, n_to_role, r_dname, r_email, n_start_date, n_due_date, n_end_date, n_from_user, n_priority, n_comment, n_subject, n_headerText, g_LOBTable(g_text_messageIdx).temp_lob, err_name, err_message, err_invalid, err_expected, n_text_timezone); -- Wrap the body into nice pretty lines. LOBLineBreak(g_LOBTable(g_text_messageIdx).temp_lob, wf_linelen, 999); -- Add email response section LOBReplace(g_LOBTable(g_text_messageIdx).temp_lob, '&'||'RESPONSE', n_response, FALSE); -- More Information Processing - Bug 2282139 LOBReplace(g_LOBTable(g_text_messageIdx).temp_lob, '&'||'HISTORY', n_text_history, FALSE); LOBReplace(g_LOBTable(g_text_messageIdx).temp_lob, '&'||'QUESTION', n_last_ques, FALSE); end if; if (r_ntf_pref in ('MAILHTML', 'MAILATTH', 'MAILHTM2')) then n_headerHTML := Substitute(t_headerHTML, nid, n_nid_str, n_status, n_to_role, r_dname, r_email, n_start_date, n_due_date, n_end_date, n_from_user, n_priority, n_comment, n_subject, str_dummy, str_dummy, err_name, err_message, err_invalid, err_expected, n_html_timezone); LOBSubstitute(t_html_body, nid, n_nid_str, n_status, n_to_role, r_dname, r_email, n_start_date, n_due_date, n_end_date, n_from_user, n_priority, n_comment, UrlEncode(n_subject), n_headerHTML, g_LOBTable(g_html_messageIdx).temp_lob, err_name, err_message, err_invalid, err_expected, n_html_timezone); -- Add email response section LOBReplace(g_LOBTable(g_html_messageIdx).temp_lob, '&'||'RESPONSE', n_response, FALSE); -- More Information processing LOBReplace(g_LOBTable(g_html_messageIdx).temp_lob, '&'||'HISTORY', n_html_history, FALSE); LOBReplace(g_LOBTable(g_html_messageIdx).temp_lob, '&'||'QUESTION', n_last_ques, FALSE); -- Add mailto section if (g_moreinfo = 'SUB') then n_mailto := GetMoreInfoMailTo(nid, 'NID['||to_char(nid)||'/' ||n_key||'@'||node||']', replyTo, n_subject); LOBReplace(g_LOBTable(g_html_messageIdx).temp_lob, '&'||'MAILTO', n_mailto, FALSE); else GetMailTo(nid, 'NID['||to_char(nid)||'/'||n_key||'@'|| node||']', replyTo, n_subject, g_LOBTable(g_html_messageIdx).temp_lob); end if; -- If GUEST access is enabled and signature is required for response, -- no click here reponse link is provided to discourage signing under -- GUEST login if (g_sig_required = 'Y' and wf_mail.Send_AccessKey) then n_click_here := ''; else n_click_here := getClickHereResponse(nid => nid, n_key => n_key, agent => agent, n_disp_click => n_disp_click, n_sig_policy => n_sig_policy); end if; LOBReplace(g_LOBTable(g_html_messageIdx).temp_lob, '&'||'CLICK_HERE_RESPONSE', n_click_here, FALSE); -- Close of the HTML Body only where this is none -- This is only done for those tempaltes that the html/body tag -- was added. Otherwise there is a clash where there is nested -- HTML. Since this is rendered in a LOB, there should be no -- cause for the exising end /body tag should drop off the end -- like what would happen in the varchar2 version. if textForHtml then DBMS_LOB.WriteAppend(g_LOBTable(g_html_messageIdx).temp_lob, length(''), ''); end if; end if; step := 'Getting HTML attachment'; -- Get HTML attachment m_html := substrb(WFA_HTML.Detail2(nid, n_key, agent), 1, 32000); if isBiDi(r_language) then tag_pos := instrb(upper(m_html), ' 0 then dir_pos := instrb(upper(m_html), ' DIR="', 1); if dir_pos = 0 then buffer := substrb(m_html, 1, 5); buffer := buffer||' DIR="RTL" '||substrb(m_html, tag_pos+5); m_html := buffer; end if; end if; end if; subject := n_subject; -- text_body := n_text_body; -- html_body := n_html_body; -- this is for the little attachment to the detail frame body_atth := m_html; -- if (DBMS_LOB.IsOpen(template_text)=1) then -- DBMS_LOB.Close(template_text); -- DBMS_LOB.FreeTemporary(template_text); -- end if; -- if (DBMS_LOB.IsOpen(template_html)=1) then -- DBMS_LOB.Close(template_html); -- DBMS_LOB.FreeTemporary(template_html); -- end if; exception when no_program_unit then wf_core.context('WF_MAIL', 'GetLOBMessage3', to_char(nid), node, 'step -> '||step); raise; when others then -- First look for a wf_core error. wf_core.get_error(err_name, err_message, err_stack); -- If no wf_core error look for a sql error. if (err_name is null) then err_message := sqlerrm; end if; error_result := err_message||g_newLine||err_stack; wf_core.context('WF_MAIL', 'GetLOBMessage3', to_char(nid), node, error_result, 'Step -> '||step); end GetLOBMessage3; -- GetLOBMessage2 - get email message data as a LOB -- -- IN -- notification id -- mailer node name -- web agent path -- reply to -- recipient role -- lanague -- territory -- notification preference -- dsiplay name -- OUT -- message subject -- message body (text) -- message body (html) procedure GetLOBMessage2( nid in number, node in varchar2, agent in varchar2, replyto in varchar2, recipient in varchar2, language in varchar2, territory in varchar2, ntf_pref in varchar2, email in varchar2, dname in varchar2, subject out NOCOPY varchar2, body_atth out NOCOPY varchar2, error_result in out NOCOPY varchar2) as err_name varchar2(30); err_message varchar2(2000); err_stack varchar2(4000); bodyToken varchar2(1); no_program_unit exception; pragma exception_init(no_program_unit, -6508); begin wf_mail.getLobMessage3(nid, node, agent, replyto, recipient, language, territory, ntf_pref, email, dname, 'Y', subject, body_atth, error_result, bodyToken); exception when no_program_unit then wf_core.context('WF_MAIL', 'GetLOBMessage2', to_char(nid), node); raise; when others then -- First look for a wf_core error. wf_core.get_error(err_name, err_message, err_stack); -- If no wf_core error look for a sql error. if (err_name is null) then err_message := sqlerrm; end if; error_result := err_message||g_newLine||err_stack; wf_core.context('WF_MAIL', 'GetLOBMessage2', to_char(nid), node, error_result); end getLOBMessage2; -- GetLOBMessage - get email message data as a LOB -- -- IN -- notification id -- mailer node name -- web agent path -- OUT -- message subject -- message body (text) -- message body (html) procedure GetLOBMessage( nid in number, node in varchar2, agent in varchar2, replyto in varchar2, subject out NOCOPY varchar2, body_atth out NOCOPY varchar2, error_result in out NOCOPY varchar2) as n_to_role varchar2(320); r_dname varchar2(360); r_email varchar2(2000); r_ntf_pref varchar2(240); r_language varchar2(30); r_territory varchar2(30); r_orig_system varchar2(30); r_orig_system_id number; r_installed varchar2(1); err_name varchar2(30); err_message varchar2(2000); err_stack varchar2(4000); no_program_unit exception; pragma exception_init(no_program_unit, -6508); begin -- Get notification information begin select RECIPIENT_ROLE into n_to_role from WF_NOTIFICATIONS where NOTIFICATION_ID = nid; exception when no_data_found then wf_core.token('NID', to_char(nid)); wf_core.raise('WFNTF_NID'); end; -- Get Recipient information Wf_Directory.GetRoleInfoMail(n_to_role, r_dname, r_email, r_ntf_pref, r_language, r_territory, r_orig_system, r_orig_system_id, r_installed); if r_installed = 'N' then r_language := 'AMERICAN'; r_territory := 'AMERICA'; end if; wf_mail.getLobMessage2(nid, node, agent, replyto, n_to_role, r_language, r_territory, r_ntf_pref, r_email, r_dname, subject, body_atth, error_result); exception when no_program_unit then wf_core.context('WF_MAIL', 'GetLOBMessage', to_char(nid), node); raise; when others then -- First look for a wf_core error. wf_core.get_error(err_name, err_message, err_stack); -- If no wf_core error look for a sql error. if (err_name is null) then err_message := sqlerrm; end if; error_result := err_message||g_newLine||err_stack; wf_core.context('WF_MAIL', 'GetLOBMessage', to_char(nid), node, error_result); end GetLOBMessage; -- GetSummary - get summary messages for one role -- ( with LOB support ) -- IN -- role name -- display role name -- mailer node name -- OUT -- message subject -- message body (text) -- message body (html) procedure GetSummary( role in varchar2, dname in varchar2, node in varchar2, subject out NOCOPY varchar2, body_text out NOCOPY varchar2) as lob varchar2(1); begin GetSummary(role, dname, node, subject, body_text,lob); if lob = 'Y' then wf_core.context('WF_MAIL', 'GetSummary', role, node); wf_core.raise('WFMLR_SUMMARY_TOOBIG'); end if; exception when others then wf_core.context('WF_MAIL', 'GetSummary', role, node); raise; end GetSummary; -- GetSummary2 - get summary messages for one role -- Support the render flag for Applications Framework. -- If set, no body will be rendered as it will be -- deferred to the middle tier. -- IN -- role name -- display role name -- mailer node name -- content type -- OUT -- message subject -- message body (text) -- message body (html) -- lob (Y or N) procedure GetSummary2( role in varchar2, dname in varchar2, node in varchar2, renderBody in varchar2, contType in varchar2, subject out NOCOPY varchar2, body_text out NOCOPY varchar2, lob out NOCOPY varchar2) as n_key varchar2(80); n_subject varchar2(240); n_summ varchar2(32000); n_buf varchar2(32000); templateName varchar2(30); altTempl varchar2(40); templateType varchar2(8) := 'WFMAIL'; t_subject varchar2(240); t_body varchar2(32000); t_timezone varchar2(240); t_html_body varchar2(32000); n_timezone varchar2(240); nid pls_integer; to_name varchar2(320); colon pls_integer; rorig_system varchar2(30); rorig_system_id number; priority_text varchar2(240); ntf_pref varchar2(240); dummyStr varchar2(2000); r_language varchar2(30); r_displayName varchar2(360); tag_pos pls_integer; dir_pos pls_integer; buffer varchar2(32000); lob_init boolean := FALSE; -- temp_text CLOB; temp_textIdx pls_integer; -- Bug 1753464 included sort order for the query -- Bug 2439529 Altered query to use UNION instead of OR. cursor c1 is select NOTIFICATION_ID, RECIPIENT_ROLE, ACCESS_KEY, PRIORITY, DUE_DATE from WF_NOTIFICATIONS where STATUS = 'OPEN' and RECIPIENT_ROLE IN (select role from dual union select UR.ROLE_NAME from WF_USER_ROLES UR where UR.USER_ORIG_SYSTEM = rorig_system and UR.USER_ORIG_SYSTEM_ID = rorig_system_id and UR.USER_NAME = role) order by PRIORITY desc, DUE_DATE asc, NOTIFICATION_ID asc ; begin g_wfmonId := wf_core.translate('WFMON_ID'); g_to := wf_core.translate('TO'); g_priority := wf_core.translate('PRIORITY'); g_dueDate := wf_core.translate('WFMON_DUE_DATE'); -- Bug# 2358498 - flag to indicate if the sumamry is LOB lob := 'N'; if (renderBody = 'Y') then templateName := 'SUMMARY'; else templateName := 'SUMHTML'; end if; -- We have the basic template name. Now check to see if has been -- redirected using a mailer configuration parameter of the same name. altTempl := WF_MAILER_PARAMETER.GetValueForCorr('WFMAIL', templateName); colon := instrb(altTempl, ':', 1); if colon > 0 then templateType := substrb(altTempl, 1, colon -1); templateName := substrb(altTempl, colon + 1, length(altTempl)-colon); end if; -- Get notification information -- Get template 'SUMMARY' begin select SUBJECT, BODY, HTML_BODY into t_subject, t_body, t_html_body from WF_MESSAGES_VL where NAME = templateName and TYPE = templateType; exception when no_data_found then wf_core.token('NAME', templateName); wf_core.token('TYPE', templateType); wf_core.raise('WFNTF_MESSAGE'); end; -- Retrieve role orig_system ids for index access Wf_Directory.GetRoleInfoMail(role, r_displayName, dummyStr, ntf_pref, r_language, dummyStr, rorig_system, rorig_system_id, dummyStr); t_subject := substrb(replace(t_subject, '&'||'USER_NAME', r_displayName), 1, 240); if (rorig_system is null or rorig_system = '') then wf_core.token('ROLE', role); wf_core.raise('WFNTF_ROLE'); end if; if contType = g_ntfDocHtml then if (t_html_body is null) then t_html_body := replace(t_body, g_newLine, '
'||g_newLine); -- Ensure the direction of the text is correct for the language if isBiDi(r_language) then t_html_body := ''||t_html_body; else t_html_body := ''||t_html_body; end if; else -- Ensure that the direction of the text is correctly specified. if isBiDi(r_language) then tag_pos := instrb(upper(t_html_body), ' 0 then dir_pos := instrb(upper(t_html_body), ' DIR="', 1); if dir_pos = 0 then buffer := substrb(t_html_body, tag_pos, 5); buffer := buffer||' DIR="RTL" '|| substrb(t_html_body, tag_pos+5); t_html_body := buffer; end if; end if; end if; end if; end if; -- Substitute USER_NAME with role display name t_timezone := getTimezone(contType); if contType = g_ntfDocText then t_body := substrb(replace(t_body, '&'||'USER_NAME', dname), 1, 32000); t_body := substrb(replace(t_body, '&'||'TIMEZONE', t_timezone), 1, 32000); else t_html_body := substrb(replace(t_html_body, '&'||'USER_NAME', dname), 1, 32000); t_html_body := substrb(replace(t_html_body, '&'||'TIMEZONE', t_timezone), 1, 32000); end if; if (renderBody = 'Y') then -- Prepare summary header if contType = g_ntfDocText then n_summ := g_newLine; n_summ := n_summ||rpad(g_wfmonId, 7)||' '; n_summ := n_summ||rpad(g_to, 42)||' '; n_summ := n_summ||rpad(g_priority, 12)||' '; n_summ := n_summ||rpad(g_dueDate, 12)|| g_newLine; n_summ := n_summ||'------- ------------------------------------------ '; n_summ := n_summ||'------------ ------------'||g_newLine; n_buf := ''; end if; for rec in c1 loop nid := rec.notification_id; n_key := rec.access_key; to_name := rec.recipient_role; -- Get and token subsitute subject n_subject := WF_NOTIFICATION.GetSubject(nid, 'text/plain'); if contType = g_ntfDocText then n_buf := lpad(to_char(nid), 7, ' ')||' '|| rpad(substr(dname, 1, 42), 42, ' ')||' '; if (rec.priority > 66) then --Bug 2774891 fix - sacsharm --priority_text := wf_core.substitute('WFTKN', 'HIGH'); priority_text := wf_core.substitute('WFTKN', 'LOW'); elsif (rec.priority > 33) then priority_text := wf_core.substitute('WFTKN', 'NORMAL'); else --Bug 2774891 fix - sacsharm --priority_text := wf_core.substitute('WFTKN', 'LOW'); priority_text := wf_core.substitute('WFTKN', 'HIGH'); end if; n_buf := n_buf||lpad(priority_text, 12, ' '); n_buf := n_buf||' '||to_char(rec.due_date)||g_newLine; n_buf := n_buf||WordWrap(n_subject, 1); n_summ := n_summ||n_buf||g_newLine||g_newLine; end if; -- Bug 2358498 write content to LOB if there is a possibility -- that the size might go beyond 32K if length(n_summ) > 30000 then lob := 'Y'; if NOT lob_init then lob_init := TRUE; -- DBMS_LOB.CreateTemporary(g_text_message, TRUE, DBMS_LOB.SESSION); -- DBMS_LOB.Open(g_text_message, DBMS_LOB.LOB_READWRITE); g_text_messageIdx := wf_temp_lob.getLob(g_LOBTable); if contType = g_ntfDocText then DBMS_LOB.WriteAppend(g_LOBTable(g_text_messageIdx).temp_lob, length(t_body), t_body); else DBMS_LOB.WriteAppend(g_LOBTable(g_text_messageIdx).temp_lob, length(t_html_body), t_html_body); end if; -- DBMS_LOB.CreateTemporary(temp_text, FALSE, DBMS_LOB.CALL); temp_textIdx := wf_temp_lob.getLob(g_LOBTable); end if; DBMS_LOB.WriteAppend(g_LOBTable(temp_textIdx).temp_lob, length(n_summ), n_summ); n_summ := ''; end if; end loop; if lob = 'Y' then t_body := ''; t_html_body := ''; DBMS_LOB.WriteAppend(g_LOBTable(temp_textIdx).temp_lob, length(n_summ), n_summ); LOBReplace(g_LOBTable(g_text_messageIdx).temp_lob, '&'||'SUMMARY', g_LOBTable(temp_textIdx).temp_lob, FALSE); else t_body := replace(t_body, '&'||'SUMMARY', n_summ); end if; end if; -- RENDERBODY subject := t_subject; if contType = g_ntfDocText then body_text := t_body; else body_text := t_html_body; end if; exception when others then wf_core.context('WF_MAIL', 'GetSummary2', role, node, contType); raise; end GetSummary2; -- GetSummary - get summary messages for one role -- ( with LOB support ) -- IN -- role name -- display role name -- mailer node name -- OUT -- message subject -- message body (text) -- message body (html) -- lob (Y or N) procedure GetSummary( role in varchar2, dname in varchar2, node in varchar2, subject out NOCOPY varchar2, body_text out NOCOPY varchar2, lob out NOCOPY varchar2) as begin GetSummary2(role, dname, node, 'Y', 'text/plain', subject, body_text, lob); exception when others then wf_core.context('WF_MAIL', 'GetSummary', role, node); raise; end GetSummary; -- initFetchLOB -- -- IN -- Document type (TEXT or HTML) -- procedure InitFetchLOB(doc_type VARCHAR2, doc_length OUT NOCOPY NUMBER) is begin if doc_type = g_ntfDocHtml then g_html_chunk := 0; -- doc_length := DBMS_LOB.GetLength(g_html_message); doc_length := DBMS_LOB.GetLength(g_LOBTable(g_html_messageIdx).temp_lob); else -- Always assume that the caller wants the TEXT g_text_chunk := 0; -- doc_length := DBMS_LOB.GetLength(g_text_message); doc_length := DBMS_LOB.GetLength(g_LOBTable(g_text_messageIdx).temp_lob); end if; end InitFetchLOB; -- FetchLOBContent -- -- IN -- type of document to fetch TEXT/HTML -- End of LOB marker -- OUT -- 32K chunk of the LOB -- -- Use the API in the following manner -- WF_MAIL.InitFetchLob(g_ntfDocText) -- while not clob_end loop -- WF_MAIL.FetchLobContent(cBuf, g_ntfDocText, clob_end); -- ... -- end loop; -- procedure FetchLOBContent(buffer OUT NOCOPY VARCHAR2, doc_type IN VARCHAR2, end_of_clob IN OUT NOCOPY NUMBER) is pos NUMBER; buffer_length pls_integer := 16000; begin if doc_type = g_ntfDocHtml then pos := (buffer_length * nvl(g_html_chunk,0))+1; -- DBMS_LOB.Read(g_html_message, buffer_length, pos, buffer); DBMS_LOB.Read(g_LOBTable(g_html_messageIdx).temp_lob, buffer_length, pos, buffer); if pos+buffer_length > DBMS_LOB.GetLength(g_LOBTable(g_html_messageIdx).temp_lob) then end_of_clob := 1; g_html_chunk := 0; else g_html_chunk := g_html_chunk + 1; end if; else -- Always assume that the caller wants the TEXT pos := (buffer_length * nvl(g_text_chunk,0))+1; -- DBMS_LOB.Read(g_text_message, buffer_length, pos, buffer); DBMS_LOB.Read(g_LOBTable(g_text_messageIdx).temp_lob, buffer_length, pos, buffer); if pos+buffer_length > DBMS_LOB.GetLength(g_LOBTable(g_text_messageIdx).temp_lob) then end_of_clob := 1; g_text_chunk := 0; else g_text_chunk := g_text_chunk + 1; end if; end if; exception when others then WF_CORE.Context('WF_MAIL','FetchLOBContent',doc_type, to_char(pos)||':'||buffer); raise; end FetchLOBContent; -- CloseLOB - Close the message LOBs ready for use again later -- procedure CloseLOB(doc_type in VARCHAR2) is begin if doc_type = g_ntfDocHtml then -- DBMS_LOB.close(g_html_message); -- DBMS_LOB.FreeTemporary(g_html_message); wf_temp_lob.releaseLob(g_LOBTable, g_html_messageIdx); else -- DBMS_LOB.close(g_text_message); -- DBMS_LOB.FreeTemporary(g_text_message); wf_temp_lob.releaseLob(g_LOBTable, g_text_messageIdx); end if; exception when others then WF_CORE.Context('WF_MAIL','CloseLOB', doc_type); raise; end; -- CloseLOB - Close the message LOBs ready for use again later -- procedure CloseLOB is begin WF_MAIL.CloseLOB(g_ntfDocText); WF_MAIL.CloseLOB(g_ntfDocHtml); exception when others then WF_CORE.Context('WF_MAIL','CloseLOB'); raise; end; -- FetchUrlContent - Fetched the content from the global buffer which -- populated by GetUrlContent(). -- -- IN -- piece_count - the index to the url_content_array. -- OUT -- piece_value - the data stored in the global content_array table. function FetchUrlContent(piece_count in number, error_result in out NOCOPY varchar2) return varchar2 as begin return(wf_mail.content_array(piece_count)); exception when NO_DATA_FOUND then return('NO_DATA_FOUND'); when others then error_result := sqlerrm; wf_core.context('WF_MAIL', 'FetchUrlContent', piece_count); end FetchUrlContent; -- GetUrlContent - get URL content -- -- IN -- url address id -- OUT -- piece_count -- error result procedure GetUrlContent( url in varchar2, piece_count out NOCOPY number, error_result in out NOCOPY varchar2) as no_program_unit exception; pragma exception_init(no_program_unit, -6508); url_pieces utl_http.html_pieces; err_name varchar2(30); err_message varchar2(2000); err_stack varchar2(4000); content_array url_content_array; begin url_pieces := utl_http.request_pieces(url); for l_rec_num in 1..url_pieces.count loop wf_mail.content_array(l_rec_num) := url_pieces(l_rec_num); piece_count := l_rec_num; end loop; exception when utl_http.init_failed then error_result := 'UTL_HTTP.INIT_FAILED'; error_result := error_result || sqlerrm; wf_core.context('WF_MAIL', 'GetUrlContent', url); when utl_http.request_failed then error_result := 'UTL_HTTP.REQUEST_FAILED'; error_result := error_result || sqlerrm; wf_core.context('WF_MAIL', 'GetUrlContent', url); when no_program_unit then wf_core.context('WF_MAIL', 'GetUrlContent', url); raise; when others then error_result := sqlerrm; wf_core.context('WF_MAIL', 'GetUrlContent', url); end GetUrlContent; -- GetDocContent - get Document content -- -- IN -- notification id -- document attribute name -- display type -- OUT -- document content -- error result procedure GetDocContent( nid in number, docattrname in varchar2, disptype in varchar2, doccontent out NOCOPY varchar2, error_result in out NOCOPY varchar2) as no_program_unit exception; pragma exception_init(no_program_unit, -6508); err_name varchar2(30); err_message varchar2(2000); err_stack varchar2(4000); begin doccontent := Wf_Notification.GetAttrDoc(nid, docattrname, disptype); exception when no_program_unit then wf_core.context('WF_MAIL', 'GetDocContent', docattrname); raise; when others then -- First look for a wf_core error. wf_core.get_error(err_name, err_message, err_stack); -- If no wf_core error look for a sql error. if (err_name is null) then err_message := sqlerrm; end if; error_result := err_message; wf_core.context('WF_MAIL', 'GetDocContent', docattrname); end GetDocContent; -- GetLOBDocContent - get Document content -- Returns the document type of the PLSQLCLOB document -- -- IN -- notification id -- document attribute name -- display type -- OUT -- document content -- error result procedure GetLOBDocContent( nid in number, docattrname in varchar2, disptype in varchar2, error_result in out NOCOPY varchar2) as doctype varchar2(500); no_program_unit exception; pragma exception_init(no_program_unit, -6508); err_name varchar2(30); err_message varchar2(2000); err_stack varchar2(4000); begin Wf_Mail.GetLOBDocContent(nid, docattrname, disptype, doctype, error_result); exception when no_program_unit then wf_core.context('WF_MAIL', 'oldGetLOBDocContent', docattrname); raise; when others then -- First look for a wf_core error. wf_core.get_error(err_name, err_message, err_stack); -- If no wf_core error look for a sql error. if (err_name is null) then err_message := sqlerrm; end if; error_result := err_message; wf_core.context('WF_MAIL', 'oldGetDocLOBContent', docattrname); end GetLOBDocContent; -- GetLOBDocContent - get Document content -- -- IN -- notification id -- document attribute name -- display type -- OUT -- document type -- document content -- error result procedure GetLOBDocContent( nid in number, docattrname in varchar2, disptype in varchar2, doctype out NOCOPY varchar2, error_result in out NOCOPY varchar2) as no_program_unit exception; pragma exception_init(no_program_unit, -6508); doc varchar(32000) := ''; aname varchar2(30); err_name varchar2(30); err_message varchar2(2000); err_stack varchar2(4000); begin -- There is a difference betwen PLSQL: and PLSQLCLOB: documents -- First go for the PLSQL: if that returns the name of the -- attribute, then try the PLSQLCLOB: doc := WF_NOTIFICATION.GetAttrDoc(nid, docattrname, disptype); if disptype = g_ntfDocHtml then -- DBMS_LOB.CreateTemporary(g_html_message, true, dbms_lob.SESSION); -- DBMS_LOB.Open(g_html_message, DBMS_LOB.LOB_READWRITE); g_html_messageIdx := wf_temp_lob.getLob(g_LOBTable); if doc = '&'||docattrname then Wf_Notification.GetAttrCLOB(nid, docattrname, disptype, g_LOBTable(g_html_messageIdx).temp_lob, doctype, aname); else DBMS_LOB.Write(g_LOBTable(g_html_messageIdx).temp_lob, length(doc), 1, doc); end if; else -- DBMS_LOB.CreateTemporary(g_text_message, true, dbms_lob.SESSION); -- DBMS_LOB.Open(g_text_message, DBMS_LOB.LOB_READWRITE); g_text_messageIdx := wf_temp_lob.getLob(g_LOBTable); if doc = '&'||docattrname then Wf_Notification.GetAttrCLOB(nid, docattrname, disptype, g_LOBTable(g_text_messageIdx).temp_lob, doctype, aname); else DBMS_LOB.Write(g_LOBTable(g_text_messageIdx).temp_lob, length(doc), 1, doc); end if; end if; exception when no_program_unit then wf_core.context('WF_MAIL', 'GetLOBDocContent', docattrname); raise; when others then -- First look for a wf_core error. wf_core.get_error(err_name, err_message, err_stack); -- If no wf_core error look for a sql error. if (err_name is null) then err_message := sqlerrm; end if; error_result := err_message; wf_core.context('WF_MAIL', 'GetDocLOBContent', docattrname); end GetLOBDocContent; -- RemoveSpace (PRIVATE) -- Removes white spaces between response prompt and colon, from colon -- to the quote. -- IN -- body - Email response body -- resp_attrs - Response attribtue info for the current notification -- OUT -- body - Email body with the white spaces removed wherever required function RemoveSpace(body in varchar2, resp_attrs in resp_attrs_t) return varchar2 is colonPos pls_integer; quotePos pls_integer; prompPos pls_integer; prompt varchar2(80); tmpStr varchar2(32000); tmpBody varchar2(32000); begin tmpBody := body; -- remove spaces from the response prompt till the following colon for i in 1..resp_attrs.COUNT loop prompt := resp_attrs(i).attr_prompt; prompPos := instrb(tmpBody, prompt, 1); while (prompPos > 0) loop prompPos := prompPos + length(prompt) - 1; colonPos := instrb(tmpBody, ':', prompPos); tmpStr := substrb(tmpBody, prompPos + 1, (colonPos - prompPos) - 1); tmpStr := replace(tmpStr, g_tab); tmpStr := replace(tmpStr, g_newLine); if (ltrim(rtrim(tmpStr)) is NULL and colonPos > 0) then tmpBody := substrb(tmpBody, 1, prompPos) || substrb(tmpBody, colonPos); end if; prompPos := instrb(tmpBody, prompt, prompPos); end loop; end loop; -- first occurence of a colon colonPos := instrb(tmpBody, ':', 1); -- loop until there is a colon while colonPos > 0 loop -- first occurence of a double quote after the colon quotePos := instrb(tmpBody, '"', colonPos); if (quotePos > 0) then -- examine the string between the colon the following double quote -- and replace tab and newline tmpStr := substrb(tmpBody, colonPos+1, (quotePos-colonPos)-1); tmpStr := replace(tmpStr, g_tab); tmpStr := replace(tmpStr, g_newLine); -- if trim results in NULL, the string need not exist within the email if (ltrim(rtrim(tmpStr)) IS NULL) then tmpBody := substrb(tmpBody, 1, colonPos) || substrb(tmpBody, quotePos); end if; end if; -- first occurence of a single quote after the colon quotePos := instrb(tmpBody, '''', colonPos); if (quotePos > 0) then -- examine the string between the colon the following single quote -- and replace tab and newline tmpStr := substrb(tmpBody, colonPos+1, (quotePos-colonPos)-1); tmpStr := replace(tmpStr, g_tab); tmpStr := replace(tmpStr, g_newLine); -- if trim results in NULL, the string need not exist within the email if (ltrim(rtrim(tmpStr)) IS NULL) then tmpBody := substrb(tmpBody, 1, colonPos) || substrb(tmpBody, quotePos); end if; end if; colonPos := instrb(tmpBody, ':', colonPos+1); end loop; return tmpBody; exception when others then wf_core.context('WF_MAIL', 'RemoveSpace'); raise; end RemoveSpace; -- PutMessage -- Reply processor. Read body of a notification reply, set any -- response attributes, and complete response. -- Used by the notification mail response processor. -- IN -- notification id -- mailer node name -- response body text -- email 'from' address procedure PutMessage( nid in number, node in varchar2, resp_body in varchar2, from_addr in varchar2, error_result in out NOCOPY varchar2) as TYPE stack_t IS TABLE OF varchar2(32000) INDEX BY BINARY_INTEGER; stack stack_t; contentStack stack_t; resp_attrs resp_attrs_t; value varchar2(4000); prompt varchar2(2000); buffer varchar2(32000); tmpbuf varchar2(32000); token varchar2(32000); loc pls_integer; stk pls_integer; i pls_integer; j pls_integer; k pls_integer; l pls_integer; prompPos pls_integer; nextPos pls_integer := 0; tmpPos pls_integer; dleft number; dright number; sleft number; sright number; left number; right number; msg_name varchar2(30); msg_type varchar2(8); stat varchar2(8); response boolean := FALSE; lk_type varchar2(100); lk_meaning varchar2(100); n_sig_policy varchar2(100); -- Select msg response attrs. -- Order-by is to insure longest prompts are processed first to prevent -- problems where one prompt is a substring of another prompt. cursor c1 is select NAME, DISPLAY_NAME, TYPE, FORMAT from WF_MESSAGE_ATTRIBUTES_VL where MESSAGE_NAME = msg_name and MESSAGE_TYPE = msg_type and SUBTYPE = 'RESPOND' and TYPE not in ('FORM', 'URL') order by length(DISPLAY_NAME) desc; no_program_unit exception; pragma exception_init(no_program_unit, -6508); begin -- Get notification message and status begin select MESSAGE_NAME, MESSAGE_TYPE, STATUS into msg_name, msg_type, stat from WF_NOTIFICATIONS where NOTIFICATION_ID = nid; exception when no_data_found then wf_core.token('NID', to_char(nid)); wf_core.raise('WFNTF_NID'); end; i := 1; -- collect all the response attributes and their details -- for the notification for rec in c1 loop resp_attrs(i).attr_prompt := rec.display_name; resp_attrs(i).attr_type := rec.type; resp_attrs(i).attr_name := rec.name; resp_attrs(i).attr_format := rec.format; i := i + 1; end loop; -- Bug 2375920 get the signature policy for the notification and -- raise error is the policy is invalid Wf_Mail.GetSignaturePolicy(nid, n_sig_policy); if (n_sig_policy is not NULL and upper(n_sig_policy) <> 'DEFAULT') then if(upper(n_sig_policy) = 'PSIG_ONLY') then wf_core.context('WF_MAIL', 'PutMessage', to_char(nid), node, from_addr); wf_core.token('NID', to_char(nid)); wf_core.raise('WFRSPR_PWD_SIGNATURE'); else wf_core.context('WF_MAIL', 'PutMessage', to_char(nid), node, from_addr); wf_core.token('NID', to_char(nid)); wf_core.token('POLICY', n_sig_policy); wf_core.raise('WFMLR_INVALID_SIG_POLICY'); end if; end if; -- all database-friendly RemoveSpace buffer := RemoveSpace(resp_body, resp_attrs); if (buffer is not null or buffer <> '') then -- separate the mail content into tokens based on Content-Type -- to eliminate v-card interference. stk := 1; i := 1; loc := 1; token := ''; while (i <= length(buffer)) loop -- check if we are at the beginning of a Content-Type prompt := substrb(buffer, i, length('Content-Type')); if (upper(prompt) = 'CONTENT-TYPE') then if (token is not null or token <> '') then -- push the buffer to the stack and start again contentStack(stk) := token; stk := stk + 1; token := ''; loc := i; end if; end if; token := substrb(buffer, loc, (i - loc) + 1); i := i + 1; end loop; if (token is not null or token <> '') then contentStack(stk) := token; end if; -- now look for response attributes within tokens based on -- Content-Type. Only the body will contain the response though. stk := 1; for k in 1..contentStack.count loop for i in 1..resp_attrs.count loop prompPos := instrb(contentStack(k), resp_attrs(i).attr_prompt||':', 1); nextPos := 0; -- get the position of the next nearest prompt for j in 1..resp_attrs.count loop tmpPos := instrb(contentStack(k), resp_attrs(j).attr_prompt||':', prompPos + 1); if (tmpPos > prompPos + 1) then if (nextPos = 0) then nextPos := tmpPos; elsif (tmpPos <> 0 and tmpPos < nextPos) then nextPos := tmpPos; end if; end if; end loop; -- if nextPos is 0, then we are at the last or only response prompt -- push to stack only if there was a prompt found within the body -- hoping to avoid v-card here if (prompPos > 0) then if (nextPos = 0) then if (resp_attrs(i).attr_type in ('LOOKUP', 'NUMBER')) then stack(stk) := substrb(contentStack(k), prompPos, length(resp_attrs(i).attr_prompt) + 40); else stack(stk) := substrb(contentStack(k), prompPos, length(resp_attrs(i).attr_prompt) + 2000); end if; else stack(stk) := substrb(contentStack(k), prompPos, (nextPos - prompPos) - 1); end if; stk := stk + 1; end if; end loop; end loop; end if; -- process the response values from the stack for i in 1..stack.count loop for j in 1..resp_attrs.count loop -- check if we are at the beginning of a response prompt prompt := substrb(stack(i), 1, length(resp_attrs(j).attr_prompt)); if (upper(prompt) = upper(resp_attrs(j).attr_prompt)) then -- remove all the following occurences of the prompt -- within the stack for k in i..stack.count loop stack(k) := replace(stack(k), resp_attrs(j).attr_prompt||':'); end loop; -- check for double quotes from both ends dleft := instrb(stack(i), '"', 1, 1); dright := instrb(stack(i), '"', -1, 1); -- check for single quotes from both ends sleft := instrb(stack(i), '''', 1, 1); sright := instrb(stack(i), '''', -1, 1); if (dleft <> 0 and (sleft = 0 or dleft < sleft)) then left := dleft; else left := sleft; end if; if (dright > sright) then right := dright; else right := sright; end if; if ((right - left) > 1) then value := substrb(stack(i), left+1, (right - left)-1); if (resp_attrs(j).attr_type = 'LOOKUP') then lk_type := resp_attrs(j).attr_format; lk_meaning := value; value := GetLovCode(resp_attrs(j).attr_format, value); end if; -- Process this notification only if it has a status of 'OPEN' -- otherwise do nothing. Fix for bug 2202392. if (stat = 'OPEN') then -- Save the new attribute value for nid. Wf_Notification.SetAttrText(nid, resp_attrs(j).attr_name, value); end if; response := TRUE; end if; end if; end loop; end loop; -- Do not need to preserve context wf_engine.preserved_context := FALSE; -- Complete the response. if response then Wf_Notification.Respond(nid, NULL, 'email:'||from_addr); else wf_core.context('WF_MAIL', 'PutMessage', to_char(nid), node, from_addr); wf_core.raise('WFRSPR_NORESPONSE'); end if; exception when no_program_unit then wf_core.context('WF_MAIL','PutMessage', to_char(nid)); raise; when OTHERS then wf_core.context('WF_MAIL','PutMessage', to_char(nid)); -- Save error message and set status to INVALID so mailer will -- bounce an "invalid reply" message to sender. HandleResponseError(nid, lk_type, lk_meaning, error_result); end PutMessage; -- PutDirectMessage -- Direct reply processor. Read body of a notification reply, set any -- response attributes, and complete response. -- Used by the notification mail response processor. -- IN -- notification id -- mailer node name -- response body text -- email 'from' address procedure PutDirectMessage( nid in number, node in varchar2, resp_body in varchar2, from_addr in varchar2, error_result in out NOCOPY varchar2) as buffer varchar2(32000); msg_name varchar2(30); msg_type varchar2(8); stat varchar2(8); use_default boolean; first_blank_line boolean := true; response boolean; -- Select msg response attrs. -- Order-by is to insure longest prompts are processed first to prevent -- problems where one prompt is a substring of another prompt. cursor c1 is select WMA.NAME, WMA.TYPE, WMA.FORMAT, decode(WMA.TYPE, 'VARCHAR2', decode(WMA.FORMAT, '', WNA.TEXT_VALUE, substr(WNA.TEXT_VALUE, 1, to_number(WMA.FORMAT))), 'NUMBER', decode(WMA.FORMAT, '', to_char(WNA.NUMBER_VALUE), to_char(WNA.NUMBER_VALUE, WMA.FORMAT)), 'DATE', decode(WMA.FORMAT, '', to_char(WNA.DATE_VALUE), to_char(WNA.DATE_VALUE, WMA.FORMAT)), 'LOOKUP', WNA.TEXT_VALUE, WNA.TEXT_VALUE) VALUE from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES_VL WMA where WNA.NOTIFICATION_ID = nid and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE and WN.MESSAGE_NAME = WMA.MESSAGE_NAME and WMA.NAME = WNA.NAME and WMA.SUBTYPE = 'RESPOND' and WMA.TYPE not in ('FORM', 'URL') order by WMA.SEQUENCE; new_value varchar2(4000); new_start pls_integer; new_end pls_integer; no_program_unit exception; pragma exception_init(no_program_unit, -6508); -- Bug# 2301881 lk_type varchar2(30); lk_meaning varchar2(80); -- Bug 2375920 n_sig_policy varchar2(100); begin buffer := resp_body; response := false; -- Get notification message and status begin select MESSAGE_NAME, MESSAGE_TYPE, STATUS into msg_name, msg_type, stat from WF_NOTIFICATIONS where NOTIFICATION_ID = nid; exception when no_data_found then wf_core.token('NID', to_char(nid)); wf_core.raise('WFNTF_NID'); end; -- Bug 2375920 get the signature policy for the notification and -- raise error is the policy is invalid Wf_Mail.GetSignaturePolicy(nid, n_sig_policy); if (n_sig_policy is not NULL and upper(n_sig_policy) <> 'DEFAULT') then if(upper(n_sig_policy) = 'PSIG_ONLY') then wf_core.context('WF_MAIL', 'PutDirectMessage', to_char(nid), node, from_addr); wf_core.token('NID', to_char(nid)); wf_core.raise('WFRSPR_PWD_SIGNATURE'); else wf_core.context('WF_MAIL', 'PutDirectMessage', to_char(nid), node, from_addr); wf_core.token('NID', to_char(nid)); wf_core.token('POLICY', n_sig_policy); wf_core.raise('WFMLR_INVALID_SIG_POLICY'); end if; end if; -- Process all Response attributes. -- The first line in the mail body should be the answer for the first -- response attribute. And the second line should be for the second -- response attribute.... -- Blank line means take the default value. -- The mailer is assuming that blank line will be inserted by user -- when they want to take the default value. -- When an answer is too long, double quote should be enclosed at both -- beginning and the end of the answer. for rec in c1 loop -- Bug# 2301881 These values are needed in HandleResponseError -- and FormatErrorMessage to bounce invalid response mail lk_type := rec.format; lk_meaning := g_none; -- GetDirectAnswer() will take the next line from the mail body. -- GetDirectAnswer() takes care multiple lines answer.(answer enclosed -- by double quote use_default := false; GetDirectAnswer(buffer, new_value); if (new_value is null) then -- check if is leading blank line if (not(first_blank_line)) then new_value := rec.value; use_default := true; else while (new_value is null) loop GetDirectAnswer(buffer, new_value); end loop; first_blank_line := false; end if; else first_blank_line := false; end if; -- Bug# 2301881 lk_meaning := new_value; -- If this is a lookup, replace displayed meaning with code. if (rec.type = 'LOOKUP' AND use_default = false) then new_value := GetLovCode(rec.format, new_value); end if; -- Process this notification only if it has a status of 'OPEN' -- otherwise do nothing. Fix for bug 2202392. if (stat = 'OPEN') then -- Save the new attribute value for nid. Wf_Notification.SetAttrText(nid, rec.name, new_value); end if; response := TRUE; end loop; -- Do not need to preserve context wf_engine.preserved_context := FALSE; -- Complete the response. if response then Wf_Notification.Respond(nid, NULL, 'email:'||from_addr); else wf_core.context('WF_MAIL', 'PutDirectMessage', to_char(nid), node, from_addr); wf_core.raise('WFRSPR_NORESPONSE'); end if; exception when no_program_unit then wf_core.context('WF_MAIL','PutDirectMessage', to_char(nid)); raise; when OTHERS then wf_core.context('WF_MAIL','PutDirectMessage', to_char(nid)); -- Save error message and set status to INVALID so mailer will -- bounce an "invalid reply" message to sender. HandleResponseError(nid, lk_type, lk_meaning, error_result); end PutDirectMessage; -- PutMoreInfoRequest -- Reply processor. Read body of a request for more information -- parse the body for the role to send the request to. -- Used by the notification mail response processor. -- IN -- notification id -- mailer node name -- response body text -- email 'from' address procedure PutMoreInfoRequest( nid in number, node in varchar2, resp_body in varchar2, from_addr in varchar2, error_result in out nocopy varchar2) as buffer varchar2(32000); start_pos number; end_pos number; prompt varchar2(200); comment varchar2(200); to_user varchar2(200); dummy varchar2(200); no_program_unit exception; pragma exception_init(no_program_unit, -6508); begin buffer := resp_body; -- Remove line feeds incase there -- has been a line wrap on the response buffer := replace(buffer, g_newLine, ''); -- Locate who the response is to. prompt := g_moreInfoFrom||': '''; start_pos := instr(buffer, prompt, 1, 1); if start_pos <> 0 then start_pos := start_pos + length(prompt); end_pos := instr(buffer, '''', start_pos, 1); to_user := substr(buffer, start_pos, end_pos - start_pos); else to_user := ''; end if; -- More info request can come only from a HTML mail in which -- a template is provided with single quotes prompt := ''; start_pos := 0; end_pos := 0; prompt := g_moreInfoQPrompt||': '''; -- checking only for single quote, as the template is generated by the -- mailer with a single quote on clicking on the link start_pos := instr(buffer, prompt, 1, 1); if start_pos <> 0 then start_pos := start_pos + length(prompt); end_pos := instr(buffer, '''', start_pos, 1); if end_pos <> 0 then comment := substr(buffer, start_pos, end_pos - start_pos); if comment = g_moreInfoQuestion and length(comment) = length(g_moreInfoQuestion) then comment := ''; end if; end if; else comment := 'NULL'; end if; -- validate the role before calling updateinfo -- update wf_notifications and wf_comments in QUESTION mode if (length(to_user) > 0 and length(comment) > 0) then wf_notification.UpdateInfo2(nid, to_user, from_addr, comment); end if; exception when no_program_unit then wf_core.context('WF_MAIL','PutMoreInfoRequest', to_char(nid)); raise; when OTHERS then wf_core.context('WF_MAIL','PutMoreInfoRequest', to_char(nid)); -- Save error message and set status to INVALID so mailer will -- bounce an "invalid reply" message to sender. HandleResponseError(nid, dummy, dummy, error_result); end PutMoreInfoRequest; -- PutMoreInfoMessage -- Reply processor. Read body of a reply for more information -- request, parse the body for the comments from the user and -- update wf_notification and wf_comments apropriately -- Used by the notification mail response processor. -- IN -- notification id -- mailer node name -- response body text -- email 'from' address procedure PutMoreInfoMessage( nid in number, node in varchar2, resp_body in varchar2, from_addr in varchar2, error_result in out nocopy varchar2) as buffer varchar2(32000); start_pos number; end_pos number; prompt varchar2(200); comment varchar2(200); to_user varchar2(200); dummy varchar2(200); no_program_unit exception; pragma exception_init(no_program_unit, -6508); begin buffer := resp_body; -- Remove line feeds incase there -- has been a line wrap on the response buffer := replace(buffer, g_newLine, ''); prompt := ''; start_pos := 0; end_pos := 0; prompt := g_moreInfoAPrompt||': '''; start_pos := instr(buffer, prompt, 1, 1); if start_pos <> 0 then start_pos := start_pos + length(prompt); end_pos := instr(buffer, '''', start_pos, 1); if end_pos <> 0 then comment := substr(buffer, start_pos, end_pos - start_pos); if comment = g_moreInfoAnswer and length(comment) = length(g_moreInfoAnswer) then comment := ''; end if; end if; else prompt := ''; start_pos := 0; end_pos := 0; prompt := g_moreInfoAPrompt||': "'; start_pos := instr(buffer, prompt, 1, 1); if (start_pos <> 0) then start_pos := start_pos + length(prompt); end_pos := instr(buffer, '''', start_pos, 1); if (end_pos <> 0) then comment := substr(buffer, start_pos, end_pos - start_pos); if ((comment = g_moreInfoAnswer) and (length(comment) = length(g_moreInfoAnswer))) then comment := ''; end if; end if; end if; end if; -- update wf_notifications and wf_comments in ANSWER mode to_user := NULL; wf_notification.UpdateInfo2(nid, to_user, from_addr, comment); exception when no_program_unit then wf_core.context('WF_MAIL','PutMoreInfoMessage', to_char(nid)); raise; when OTHERS then wf_core.context('WF_MAIL','PutMoreInfoMessage', to_char(nid)); -- Save error message and set status to INVALID so mailer will -- bounce an "invalid reply" message to sender. HandleResponseError(nid, dummy, dummy, error_result); end PutMoreInfoMessage; -- GetURLAttachment - Return the attached URLS as a list on an attachment -- IN -- NID Notificaiton ID -- OUT -- BUFFER containing the attachment body -- ERROR_RESULT - Errorstack if requried procedure GetUrlAttachment (nid in number, buffer out NOCOPY varchar2, error_result out NOCOPY varchar2) is l_subject varchar2(2000); l_html_body varchar2(32000); l_url varchar2(2000); l_urllist varchar2(32000); l_urlcount integer; err_name varchar2(30); err_message varchar2(2000); err_stack varchar2(4000); cursor ntf is select WMA.TYPE, WMA.DISPLAY_NAME, decode(WMA.TYPE, 'URL', WF_NOTIFICATION.GetUrlText(WNA.TEXT_VALUE, GetURLAttachment.nid), WNA.TEXT_VALUE) URL, WNA.NAME from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES_VL WMA where WNA.NOTIFICATION_ID = GetURLAttachment.nid and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE and WN.MESSAGE_NAME = WMA.MESSAGE_NAME and (WMA.TYPE = 'URL') and WMA.ATTACH = 'Y' and WMA.NAME = WNA.NAME; no_program_unit exception; pragma exception_init(no_program_unit, -6508); begin -- Get the template. begin select SUBJECT, HTML_BODY into l_subject, l_html_body from WF_MESSAGES_VL where NAME = 'ATTACHED_URLS' and TYPE = 'WFMAIL'; exception when no_data_found then -- If the template has not been installed, then construct a -- default, minimem template. l_html_body := ''|| 'Notificaiton References'|| '
Default Template
'|| '&'||'URLLIST'; end; -- Build the list of URLs l_urlcount := 0; l_urllist := '
'; for urlattr in ntf loop l_url := wf_notification.SetFrameworkAgent(urlattr.url); l_urllist := l_urllist || '' || urlattr.display_name || '
'||g_newLine; l_urlcount := l_urlcount + 1; end loop; -- Substitute the list if l_urlcount > 0 then buffer := substrb(replace(l_html_body, '&'||'URLLIST', l_urllist), 1, 32000); else buffer := ''; end if; exception when no_program_unit then wf_core.context('WF_MAIL', 'GetURLAttachment', to_char(nid)); raise; when others then -- First look for a wf_core error. wf_core.get_error(err_name, err_message, err_stack); -- If no wf_core error look for a sql error. if (err_name is null) then err_message := sqlerrm; end if; error_result := err_message; wf_core.context('WF_MAIL', 'GetURLAttachment', to_char(nid)); end; -- Direct_Response - Return the value of the direct response flag -- -- OUT -- Direct Response as [TRUE|FALSE] function Direct_Response return boolean is begin return g_direct_response; end; -- Send_Accesskey - Return the value of the send access key flag -- -- OUT -- Direct Response as [TRUE|FALSE] function Send_Accesskey return boolean is enabled varchar2(1); flag boolean; begin if g_install = 'EMBEDDED' then enabled := FND_PROFILE.Value('WF_VALIDATE_NTF_ACCESS'); if enabled = 'Y' then flag := TRUE; else flag := FALSE; end if; else flag := g_send_accesskey; end if; return flag; end; -- autoclose_fyi - Return the value of the autoclose fyi flag -- -- OUT -- AUTOCLOSE_FYI as [TRUE|FALSE] function Autoclose_FYI return boolean is begin return g_autoclose_fyi; end; -- Direct_Response_On - Set the value of the direct response flag to TRUE -- procedure Direct_Response_On is begin g_direct_response := TRUE; end; -- Direct_Response_Off - Set the value of the direct response flag to FALSE -- procedure Direct_Response_off is begin g_direct_response := FALSE; end; -- send_accesskey_on - Set the value of the send acces key flag to TRUE -- procedure Send_Access_Key_On is begin g_send_accesskey := TRUE; end; -- Send_Accesskey_oOf - Set the value of the send acces key flag to FALSE -- procedure Send_Access_Key_Off is begin g_send_accesskey := FALSE; end; -- Autoclose_FYI_On - Set the value of the autoclose FYI flag to TRUE -- procedure Autoclose_FYI_On is begin g_autoclose_fyi := TRUE; end; -- Autoclose_FYI_Off - Set the value of the autoclose FYI flag to FALSE -- procedure Autoclose_FYI_Off is begin g_autoclose_fyi := FALSE; end; -- set_template - Set the mail template -- if nothing is specify, it will clear the mail template value. procedure set_template(name in varchar2) is begin g_template := substr(name, 1, 30); end set_template; -- -- GetCharset (PRIVATE) -- Get the character set base of the language and territory info. -- NOTE -- We may do more in the future to find the character set. -- procedure GetCharset(lang in varchar2, terr in varchar2, charset out NOCOPY varchar2) is begin begin if (terr is null) then raise NO_DATA_FOUND; end if; select NLS_CODESET into charset from WF_LANGUAGES where NLS_LANGUAGE = lang and NLS_TERRITORY = terr; exception when NO_DATA_FOUND then -- try to find the character set base on language alone select NLS_CODESET into charset from WF_LANGUAGES where NLS_LANGUAGE = lang and rownum < 2; end; exception when OTHERS then wf_core.context('WF_MAIL', 'GetCharset', lang, terr); raise; end GetCharset; -- GetSessionLanguage -- Get the session language and territory for the -- current session -- -- OUT -- Language -- Territory -- codeset procedure GetSessionLanguage(lang out NOCOPY varchar2, terr out NOCOPY varchar2, codeset out NOCOPY varchar2) is nls_str varchar2(1000); underscore integer; dot integer; begin select userenv('LANGUAGE') into nls_str from sys.dual; underscore := instr(nls_str,'_',1,1); dot := instr(nls_str, '.',1,1); lang := substr(nls_str, 1, underscore-1); terr := substr(nls_str, underscore+1, dot - underscore -1); codeset := substr(nls_str, dot+1); exception when others then wf_core.context('WF_MAIL', 'GetSessionLanguage'); raise; end GetSessionLanguage; -- Bug 2375920 -- GetSignaturePolicy (PUBLIC) -- Get the signature policy for the notification from -- the notification attribute -- IN -- nid - Notification id -- OUT -- sig_policy - Signature policy procedure GetSignaturePolicy(nid in number, sig_policy out NOCOPY varchar2) is begin -- Get value for signature policy for the notification from -- notification attribute sig_policy := Wf_Notification.GetAttrText(nid, '#WF_SIG_POLICY'); exception when others then if (wf_core.error_name = 'WFNTF_ATTR') then wf_core.clear; sig_policy := 'DEFAULT'; else raise; end if; end GetSignaturePolicy; -- gets the size of the current LOB table function getLobTableSize return number is begin return g_LOBTable.COUNT; end; -- -- GetSecurityPolicy -- procedure GetSecurityPolicy(p_nid in number, p_sec_policy out NOCOPY varchar2) is begin -- Get security policy for the notification p_sec_policy := Wf_Notification.GetAttrText(p_nid, '#WF_SECURITY_POLICY'); if (p_sec_policy is null) then p_sec_policy := 'DEFAULT'; end if; exception when others then if (wf_core.error_name = 'WFNTF_ATTR') then wf_core.clear; p_sec_policy := 'DEFAULT'; else raise; end if; end GetSecurityPolicy; -- -- ProcessSecurityPolicy -- procedure ProcessSecurityPolicy(p_nid in number, p_email out NOCOPY varchar2, p_message_name out NOCOPY varchar2) is l_sec_policy varchar2(100); l_email_allowed varchar2(1); begin -- Get security policy for the notification Wf_Mail.GetSecurityPolicy(p_nid, l_sec_policy); -- If the policy is not seeded, default it to Y to allow email begin SELECT email_allowed INTO l_email_allowed FROM wf_ntf_security_policies WHERE policy_name = l_sec_policy; exception when others then Wf_Core.Token('NID', to_char(p_nid)); Wf_Core.Token('POLICY', l_sec_policy); Wf_Core.Raise('WFMLR_INVALID_SEC_POLICY'); end; -- There should be a way to avoid hard-coding the values !!! p_email := l_email_allowed; -- Policy is either EMAIL_OK or DEFAULT if (l_email_allowed = 'Y') then p_message_name := null; -- Policy NO_EMAIL, the notification is not to be sent by e-mail elsif (l_email_allowed = 'N') then p_message_name := 'OPEN_MAIL_SECURE'; -- Policy is ENC_EMAIL_ONLY. Requires the notification to be encrypted. Currently not -- supported, so inform the user to access the online notification elsif (l_email_allowed = 'E') then p_message_name := 'OPEN_MAIL_SECURE'; end if; exception when others then Wf_Core.Context('Wf_Mail', 'ProcessSecurityPolicy', to_char(p_nid)); raise; end ProcessSecurityPolicy; -- Set_FYI_Flag (Private) -- Sets a global flag to identify if the current e-mail being processed is a -- FYI notification -- IN -- p_fyi boolean procedure Set_FYI_Flag(p_fyi in boolean) is begin g_fyi := p_fyi; end Set_FYI_Flag; -- Get_FYI_Flag (Private) -- Returns a global flag to identify if the current e-mail being processed is -- a FYI notification -- OUT -- Boolean value function Get_FYI_Flag return boolean is begin return g_fyi; end Get_FYI_Flag; -- Get_Ntf_Language (PRIVATE) -- Overrides the language and territory setting for the notification based -- on the #WFM_LANGUAGE and #WFM_TERRITORY attributes. If neither user's -- preference nor the notification level setting are valid, the base NLS -- setting is used -- IN -- p_nid - Notification Id -- IN OUT -- p_language - NLS Language -- p_territory - NLS Territory -- p_codeset - NLS Codeset procedure Get_Ntf_Language(p_nid in number, p_language in out nocopy varchar2, p_territory in out nocopy varchar2, p_codeset in out nocopy varchar2) is l_lang varchar2(30); l_terr varchar2(30); l_install varchar2(1); l_codeset varchar2(30); l_base_lang varchar2(30); l_base_terr varchar2(30); l_base_codeset varchar2(30); begin if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.wf_mail.Get_Ntf_Language.BEGIN', 'User preferences. LANG {'||p_language||'} TERR {'||p_territory||'}'); end if; -- Get notification's language preference begin l_lang := upper(Wf_Notification.GetAttrText(p_nid, '#WFM_LANGUAGE')); exception when others then if (wf_core.error_name = 'WFNTF_ATTR') then wf_core.clear(); l_lang := null; else raise; end if; end; l_install := 'N'; if (l_lang is not null) then -- Check for validity of notification's language begin select nls_codeset, installed_flag into l_codeset, l_install from wf_languages where nls_language = l_lang; exception when others then l_install := 'N'; l_lang := null; l_codeset := null; end; end if; -- Notification level language is installed and valid if (l_install = 'Y') then p_language := l_lang; p_codeset := l_codeset; else -- Notification level language is not installed, use user's preference begin select nls_codeset, installed_flag into p_codeset, l_install from wf_languages where nls_language = p_language; exception when others then l_install := 'N'; end; -- If neither notification level nor user's preferences are installed, -- use default if (l_install = 'N') then p_language := null; p_codeset := null; end if; end if; -- Get notification's territory preference begin l_terr := upper(Wf_Notification.GetAttrText(p_nid, '#WFM_TERRITORY')); exception when others then if (wf_core.error_name = 'WFNTF_ATTR') then wf_core.clear(); l_terr := null; else raise; end if; end; l_install := 'N'; if (l_terr is not null) then begin select 'Y' into l_install from wf_languages where nls_territory = l_terr; -- Notification level territory is valid p_territory := l_terr; exception when others then l_install := 'N'; end; end if; -- Use user's territory preference if it is valid if (l_install = 'N') then begin select 'Y' into l_install from wf_languages where nls_territory = p_territory; exception when others then -- Neither notification level nor user's territory preference is -- valid, use default p_territory := null; end; end if; -- If at least one of the value is null, use the base NLS setting if (p_language is null or p_territory is null) then if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.wf_mail.Get_Ntf_Language.NULL', 'Using base NLS setting because p_language is {'||p_language|| '} and p_territory is {'||p_territory||'}'); end if; WF_MAIL.GetSessionLanguage(l_base_lang, l_base_terr, l_base_codeset); if (p_language is null) then p_language := l_base_lang; p_codeset := l_base_codeset; end if; p_territory := nvl(p_territory, l_base_terr); end if; if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_procedure, 'wf.plsql.wf_mail.Get_Ntf_Language.END', 'Email Notification LANG {'||p_language||'} TERR {'||p_territory||'}'|| ' CODESET {'||p_codeset||'}'); end if; exception when others then Wf_Core.Context('Wf_Mail', 'Get_Ntf_Language', to_char(p_nid), p_language, p_territory); raise; end Get_Ntf_Language; end WF_MAIL; / -- show errors package body wf_mail; commit; exit;