'||wf_core.newline),1, 32000); end if; elsif (not_attr_row.type = 'NUMBER') then -- NUMBER is number_value, with format if provided. if (not_attr_row.format is null) then value := to_char(not_attr_row.number_value); else value := to_char(not_attr_row.number_value, not_attr_row.format); end if; elsif (not_attr_row.type = 'DATE') then -- DATE is date_value, with format if provided. if (not_attr_row.format is null) then value := to_char(not_attr_row.date_value); else value := to_char(not_attr_row.date_value, not_attr_row.format); end if; elsif (not_attr_row.type = 'FORM') then -- FORM is display_name (function), with parameters of function -- recursively token-substituted if needed. value := not_attr_row.text_value; params := instr(value, ':'); if (params <> 0) then value := not_attr_row.display_name||' ( '|| substr(value, 1, params)|| wf_notification.GetTextInternal(substr(value,params+1), nid, target, FALSE, FALSE, 'text/plain')||' )'; end if; elsif (not_attr_row.type = 'URL') then -- URL is display_name (url), with parameters of url -- recursively token-substituted if needed. value := not_attr_row.text_value; target := substr(nvl(not_attr_row.format, '_top'), 1, 16); value := wf_notification.SetFrameworkAgent(value); params := instr(value, '?'); if (params <> 0) then value := substr(value, 1, params)|| wf_notification.GetTextInternal(substr(value,params+1), nid, target, TRUE, FALSE, 'text/plain'); end if; if (disptype = wf_notification.doc_html) then extPos := instrb(value, '.', -1, 1) + 1; extStr := lower(substrb(value, extPos)); if (params = 0 and extStr in ('gif','jpg','png','tif','bmp','jpeg')) then value := '
'||document||''; end if; return; else /* ** Get the attribute display name */ -- Get type and format of attr. -- This is used for translating number/date strings. begin select WMATL.DISPLAY_NAME, NVL(WMA.FORMAT, '_blank') into display_name, target from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES_TL WMATL, WF_MESSAGE_ATTRIBUTES WMA where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID and WN.MESSAGE_NAME = WMATL.MESSAGE_NAME and WN.MESSAGE_TYPE = WMATL.MESSAGE_TYPE and WNA.NAME = WMATL.NAME and WN.MESSAGE_NAME = WMA.MESSAGE_NAME and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE and WNA.NAME = WMA.NAME and WMATL.LANGUAGE = userenv('LANG'); exception when no_data_found then display_name := null; when others then raise; end; /* ** If this is a plain text request then just return the display ** name for the attribute. If it is html then get the attachment ** url link and return it. */ if (disptype = wf_notification.doc_html) THEN -- Returns session user name if available username := Wfa_Sec.GetUser; fnd_document_management.get_launch_document_url ( username, key, FALSE, launch_url); document := ''|| display_name||''; ELSE document := display_name; END IF; return; end if; document := null; exception when others then wf_core.context('wf_notification', 'GetAttrDoc2', to_char(nid), aname, disptype); raise; end GetAttrDoc2; -- bug 2581129 -- GetSubject -- Get subject of notification message with token values substituted -- from notification attributes. Takes disptype as input. -- IN: -- nid - Notification Id -- disptype - Display Type -- RETURNS: -- Substituted message subject -- NOTE: -- If errors are detected this routine returns the subject unsubstituted, -- or null if all else fails, instead of raising exceptions. -- function GetSubject( nid in number, disptype in varchar2) return varchar2 is local_subject varchar2(240); target varchar2(16); l_error varchar2(32000); begin -- Get subject select WM.SUBJECT into local_subject from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM where N.NOTIFICATION_ID = nid and N.MESSAGE_NAME = WM.NAME and N.MESSAGE_TYPE = WM.TYPE; -- Return substituted subject, limited to 240 chars in case -- tokens exceed length. -- return(substrb(GetTextInternal(local_subject, nid, target, FALSE, -- TRUE, disptype), 1, 240)); -- Allow PLSQL Document attributes within Subject return(substrb(GetText(local_subject, nid, disptype), 1, 240)); exception when others then -- Return the error message with error stack l_error := wf_core.translate('ERROR') || wf_core.newline; if (wf_core.error_name is not null) then l_error := l_error || wf_core.error_message || wf_core.newline; l_error := l_error || wf_core.translate('WFENG_ERRNAME') || ': ' || wf_core.error_name || wf_core.newline; else l_error := l_error || sqlerrm || wf_core.newline; l_error := l_error || wf_core.translate('WFENG_ERRNAME') || ': ' || to_char(sqlcode) || wf_core.newline; end if; l_error := l_error || wf_core.translate('WFENG_ERRSTACK') || ': ' || wf_core.error_stack || wf_core.newline; return (substrb(l_error, 1, 240)); end GetSubject; -- GetSubject -- Get subject of notification message with token values substituted -- from notification attributes. -- IN: -- nid - Notification Id -- RETURNS: -- Substituted message subject -- NOTE: -- If errors are detected this routine returns the subject unsubstituted, -- or null if all else fails, instead of raising exceptions. -- function GetSubject(nid in number) return varchar2 is local_subject varchar2(240); begin return (Wf_Notification.GetSubject(nid, 'text/html')); end GetSubject; -- -- GetBody -- Get body of notification message with token values substituted -- from notification attributes. -- This function may return up to 32K chars. It can NOT be used in a view -- definition or in a Form. For views and forms, use GetShortBody, which -- truncates values at 1950 chars. -- IN: -- nid - Notification Id -- disptype - Requested display type. Valid values: -- wf_notification.doc_text - 'text/plain' -- wf_notification.doc_html - 'text/html' -- wf_notification.doc_attach - '' -- RETURNS: -- Substituted message body -- NOTE: -- If errors are detected this routine returns the body unsubstituted, -- or null if all else fails, instead of raising exceptions. -- function GetBody( nid in number, disptype in varchar2) return varchar2 is local_body varchar2(32000); local_html_body varchar2(32000); -- To check if Reassign or Request Info is performed on a FYI Notification CURSOR c_comm IS SELECT count(1) FROM wf_comments WHERE action_type in ('REASSIGN', 'QA') AND notification_id = nid AND rownum = 1; l_resp_cnt number; l_fyi boolean; l_comm_cnt pls_integer; l_html_hist boolean; l_cust_hist varchar2(4000); l_action_hist varchar2(240); begin -- Get body select WM.BODY, WM.HTML_BODY into local_body, local_html_body from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM where N.NOTIFICATION_ID = nid and N.MESSAGE_NAME = WM.NAME and N.MESSAGE_TYPE = WM.TYPE; -- If user has not used WF_NOTIFICATION(HISTORY) or #HISTORY, append Action History in the -- notification body by default if this is a... -- 1. Response required notification -- 2. FYI notification with at least one Reassign action -- Query to check if the ntf is FYI or not SELECT count(1) INTO l_resp_cnt FROM wf_message_attributes wma, wf_notifications wn WHERE wn.notification_id = nid AND wma.message_type = wn.message_type AND wma.message_name = wn.message_name AND wma.subtype = 'RESPOND' AND rownum = 1; if (l_resp_cnt = 0) then l_fyi := true; else l_fyi := false; end if; -- If this is FYI, get the count of Reassign and Request Info actions if (l_fyi) then l_comm_cnt := 0; open c_comm; fetch c_comm into l_comm_cnt; if (c_comm%notfound) then l_comm_cnt := 0; end if; close c_comm; end if; l_html_hist := false; if ((l_fyi and l_comm_cnt > 0) or not l_fyi) then -- According to bug 3612609, if the user just defines #HISTORY, but does not place it in the -- message body, even then it should be used instead of default WF Action History begin l_cust_hist := Wf_Notification.GetAttrText(nid, '#HISTORY'); exception when others then l_cust_hist := ''; Wf_Core.Clear; end; -- Validate if l_cust_hist has a valid PLSQL doc api attached to it. If it ever has JSP: we -- would not be here. if (l_cust_hist is not null and upper(trim(substr(l_cust_hist, 1, 5))) = 'PLSQL') then l_action_hist := '&#HISTORY'; else l_action_hist := 'WF_NOTIFICATION(HISTORY)'; end if; -- Either a FYI with at least one reassign/Request Info or a Response notification. -- So, append Action History if (local_body is not null and instrb(local_body, 'WF_NOTIFICATION(HISTORY)') = 0 and instrb(local_body, '&#HISTORY') = 0) then local_body := local_body || Wf_Core.newline || l_action_hist; end if; if (local_html_body is not null and instrb(local_html_body, 'WF_NOTIFICATION(HISTORY)') = 0 and instrb(local_html_body, '&#HISTORY') = 0) then -- Defer adding history macro until after stripping off BODY tags l_html_hist := true; end if; end if; -- Return substituted body. if (disptype = wf_notification.doc_text) then local_body := GetText(local_body, nid, disptype); -- replace the functions here local_body := runFuncOnBody(nid, local_body, disptype); return(local_body); else if (local_html_body is null) then --use the plain text body but fake it as html by adding
local_body := substrb(replace(local_body, wf_core.newline, '
'||wf_core.newline),1, 32000); -- get the attribute values local_body := GetText(local_body, nid, disptype); -- replace the functions here local_body := runFuncOnBody(nid, local_body, disptype); return(local_body); else if instr(upper(local_html_body),'
'||document||''); end if; */ exception when others then -- Close cursor just in case /* if (dbms_sql.is_open(curs)) then dbms_sql.close_cursor(curs); end if; */ wf_core.context('Wf_Notification', 'GetAttrClob', to_char(nid), aname, disptype); raise; end GetAttrClob; -- Name: NewClob -- Creates a new record in the temp table with a clob -- this is necessary because clobs cannot reside in plsql -- but must be part of a table. Procedure NewClob (clobloc in out nocopy clob, -- -- clobid in out nocopy number, msg_string in varchar2) is pos integer; amt number; begin -- make clob temporary. this may impact the speed of the UI -- such that user has to wait to see the notification. -- To improve performance make sure buffer cache is well tuned. dbms_lob.createtemporary(clobloc, TRUE, DBMS_LOB.session); if msg_string is not null then pos := 1; amt := length(msg_string); dbms_lob.write(clobloc,amt,pos,msg_string); end if; exception when others then wf_core.context('WF_NOTIFICATION','NewClob'); raise; end NewClob; --Name Read_Clob --reads a specific clob in 32K chunks. Call this repeatedly until --end_of_clob is true. procedure read_clob (line out nocopy varchar2 , end_of_clob in out nocopy boolean) is pos number; buff_length pls_integer:=16000; begin --linenbr is always one before the line to print. --it is incremented afterwards. pos:=(buff_length * nvl(wf_notification.clob_chunk,0) ) +1; dbms_lob.read(wf_notification.temp_clob,buff_length,pos,line); if pos+buff_length > dbms_lob.getlength(wf_notification.temp_clob) then end_of_clob := TRUE; wf_notification.clob_chunk := 0; else wf_notification.clob_chunk := wf_notification.clob_chunk +1; end if; exception when others then wf_core.context('Wf_Notification', 'Read_Clob','pos => '||to_char(pos), 'line => {'||line||'}'); raise; end Read_Clob; --Name ReadAttrClob (PUBLIC) --Desc : Gets full text of a PLSQLCLOB variable -- and returns the 32K chunks in the doctext out variable. -- Call this repeatedly until end_of_text is true. --USE : use this to get the value of idividual PLSQLCLOBs such as attachments. -- to susbtitute a PLSQLSQL clob into a message body, use GetFullBody procedure ReadAttrClob(nid in number, aname in varchar2, doctext in out nocopy varchar2, end_of_text in out nocopy boolean) is clob_id number; attr_name varchar2(30); begin if nid = wf_notification.last_nid and wf_notification.clob_exists is not null then wf_notification.read_Clob(doctext, end_of_text); else --create a clob wf_notification.newclob(wf_notification.temp_clob,null); wf_notification.clob_exists :=1; --set the clob text wf_notification.getattrclob(nid, aname, wf_notification.doc_html, wf_notification.temp_clob , attr_name); --retreive all the clob text in 32K chunks. if attr_name = aname then -- the attribute was substituted with something in the clob so print it. wf_notification.clob_chunk := 0; wf_notification.read_Clob(doctext, end_of_text); else --the aname was not substituted so just print it. doctext := aname; end if; --finally set the global vars wf_notification.last_nid:=nid; end if; exception when others then wf_core.context('Wf_Notification', 'ReadAttrClob'); raise; end ReadAttrClob; -- -- Denormalization of Notifications -- -- -- GetSessionLanguage (PRIVATE) -- Try to return the cached session language value. -- If it is not cached yet, call the real query function. -- function GetSessionLanguage return varchar2 is l_lang varchar2(64); l_terr varchar2(64); l_chrs varchar2(64); begin if (Wf_Notification.nls_language is not null) then return Wf_Notification.nls_language; end if; GetNLSLanguage(l_lang, l_terr, l_chrs); return l_lang; end GetSessionLanguage; -- -- GetNLSLanguage (PRIVATE) -- Get the NLS Lanugage setting of current session -- Try to cached the value for future use. -- NOTE: -- Because it tried to use cached values first. The subsequent calls -- will give you the cached values instead of the current value. -- procedure GetNLSLanguage(language out nocopy varchar2, territory out nocopy varchar2, charset out nocopy varchar2) is tmpbuf varchar2(240); pos1 number; -- position for '_' pos2 number; -- position for '.' begin if (Wf_Notification.nls_language is null) then tmpbuf := userenv('LANGUAGE'); pos1 := instr(tmpbuf, '_'); pos2 := instr(tmpbuf, '.'); Wf_Notification.nls_language := substr(tmpbuf, 1, pos1-1); Wf_Notification.nls_territory := substr(tmpbuf, pos1+1, pos2-pos1-1); Wf_Notification.nls_charset := substr(tmpbuf, pos2+1); end if; GetNLSLanguage.language := Wf_Notification.nls_language; GetNLSLanguage.territory := Wf_Notification.nls_territory; GetNLSLanguage.charset := Wf_Notification.nls_charset; end GetNLSLanguage; -- -- SetNLSLanguage (PRIVATE) -- Set the NLS Lanugage setting of current session -- procedure SetNLSLanguage(p_language in varchar2, p_territory in varchar2) is begin if (p_language = Wf_Notification.nls_language) then return; end if; DBMS_SESSION.SET_NLS('NLS_LANGUAGE',p_language); DBMS_SESSION.SET_NLS('NLS_TERRITORY',p_territory); -- update cache Wf_Notification.nls_language := p_language; Wf_Notification.nls_territory := p_territory; exception when others then Wf_Core.Context('Wf_Notification', 'SetNLSLanguage', p_language, p_territory); raise; end SetNLSLanguage; -- -- Denormalize_Notification -- Populate the donormalized value to WF_NOTIFICATIONS table according -- to the language setting of username provided. -- IN: -- nid - Notification id -- username - optional role name, if not provided, use the -- recipient_role of the notification. -- langcode - language code -- -- NOTE: username has precedence over langcode. Either username or -- langcode is needed. -- procedure Denormalize_Notification(nid in number, username in varchar2, langcode in varchar2) is l_orig_lang varchar2(64); l_user varchar2(320); l_from_role varchar2(320); l_from_user varchar2(360); l_to_user varchar2(360); l_subject varchar2(2000); l_language varchar2(64); role_info_tbl wf_directory.wf_local_roles_tbl_type; l_territory varchar2(64); l_defer_denormalize boolean; l_parameterlist wf_parameter_list_t; begin l_orig_lang := Wf_Notification.GetSessionLanguage; l_defer_denormalize := FALSE; l_parameterlist := wf_parameter_list_t(); -- ### implement as generic log in the future -- if (wf_notification.debug) then -- dbms_output.put_line('original language: '||l_orig_lang); -- dbms_output.put_line('original langcode: '||userenv('LANG')); -- end if; -- if username is supplied, use the language setting of such user -- default to Recipient's setting if no valid language is found. begin if (username is not null) then Wf_Directory.GetRoleInfo2(username, role_info_tbl); l_language := role_info_tbl(1).language; role_info_tbl.DELETE; -- ### implement as generic log in the future -- if (wf_notification.debug) then -- dbms_output.put_line('wants language: '||l_language); -- end if; -- check if user language match if (l_orig_lang <> l_language) then -- if (wf_notification.debug) then -- dbms_output.put_line('defers denormalization'); -- end if; return; end if; elsif (langcode is not null) then -- if (wf_notification.debug) then -- dbms_output.put_line('wants langcode: '||langcode); -- end if; -- check if langcode match if (langcode <> userenv('LANG')) then -- if (wf_notification.debug) then -- dbms_output.put_line('defers denormalization'); -- end if; return; end if; select NLS_LANGUAGE into l_language from WF_LANGUAGES where CODE = langcode; -- ### and INSTALLED_FLAG = 'Y'; -- ### Maybe we do not need to restrict installed flag to be Y end if; exception when OTHERS then l_language := null; end; begin select RECIPIENT_ROLE, FROM_ROLE into l_user, l_from_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; Wf_Directory.GetRoleInfo2(l_user, role_info_tbl); -- in most cases, l_language should be null and we use the language setting -- of the recipient role. if (l_language is null) then l_language := role_info_tbl(1).language; -- if (wf_notification.debug) then -- dbms_output.put_line('wants language: '||l_language); -- end if; if (l_orig_lang <> l_language) then -- do not do anything if the languages do not match -- if (wf_notification.debug) then -- dbms_output.put_line('languages does not match, defers denormalization'); -- end if; --return; l_defer_denormalize := TRUE; end if; end if; if (l_defer_denormalize) then l_territory := role_info_tbl(1).territory; -- To raise an EVENT when l_defer_denormalize is set true wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist); wf_event.AddParameterToList('ROLE',l_user,l_parameterlist); wf_event.AddParameterToList('LANGUAGE',l_language,l_parameterlist); wf_event.AddParameterToList('TERRITORY',l_territory,l_parameterlist); -- Raise the event wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.denormalize', p_event_key => to_char(nid), p_parameters => l_parameterlist); return; end if; -- To User -- N.B.: substrb is used in stead of substr because 320 is a hard byte limit. -- substr in some characterset may return > 320 bytes. l_to_user := role_info_tbl(1).display_name; l_to_user := substrb(l_to_user,1,320); -- From User -- If FROM_ROLE has not been defined yet, we tried to draw this from -- #FROM_ROLE. if (l_from_role is NULL) then begin l_from_role := Wf_Notification.GetAttrText(nid, '#FROM_ROLE'); exception when OTHERS then wf_core.clear; -- clear the error stack l_from_role := NULL; end; end if; -- We need to make l_from_user consistant with l_from_role. if (l_from_role is not NULL) then l_from_user := Wf_Directory.GetRoleDisplayName(l_from_role); l_from_user := substrb(l_from_user, 1,320); end if; -- Subject -- skilaru 08-MAY-03 bug fix 2883247 l_subject := Wf_Notification.GetSubject(nid, 'text/plain'); -- Populate the notification values -- begin update WF_NOTIFICATIONS set FROM_USER = l_from_user, FROM_ROLE = nvl(l_from_role,FROM_ROLE), TO_USER = l_to_user, SUBJECT = l_subject, LANGUAGE = userenv('LANG') where NOTIFICATION_ID = nid; exception when OTHERS then wf_core.token('NID', to_char(nid)); wf_core.raise('WFNTF_DENORM_FAILED'); end; exception when OTHERS then wf_core.context('Wf_Notification', 'Denormalize_Notification', to_char(nid), username); raise; end Denormalize_Notification; -- -- closeFYI -- Close FYI notifications that are not associated with an item. -- IN: -- itemtype - Item Type the notification belongs to. -- begindate - Close FYI notifications that were opened on -- or before this date. -- procedure closeFYI( itemtype in varchar2, messageName in varchar2, begindate in date) is xitemtype varchar2(8); xmessageName varchar2(30); cursor fyiNid is select WN.NOTIFICATION_ID from WF_NOTIFICATIONS WN where MESSAGE_TYPE like xitemtype and MESSAGE_NAME like xmessageName and BEGIN_DATE<=begindate and STATUS = 'OPEN' and not exists ( select NULL from WF_MESSAGE_ATTRIBUTES WMA where WMA.MESSAGE_TYPE = WN.MESSAGE_TYPE and WMA.MESSAGE_NAME = WN.MESSAGE_NAME and WMA.SUBTYPE = 'RESPOND'); begin xitemtype := nvl(itemtype, '%'); xmessageName := nvl(messageName, '%'); for c in fyiNid LOOP WF_NOTIFICATION.Close( c.NOTIFICATION_ID ); end loop; end closeFYI; -- -- NtfSignRequirementsMet (PUBLIC) (Bug 2698999) -- Checks if the notification's singature requirements are met -- IN -- nid - notification id -- OUT -- true - if the ntf is signed -- false - if the ntf is not signed -- function NtfSignRequirementsMet(nid in number) return boolean is sig_policy varchar2(100); sig_id number; sig_status number; creation_date date; signed_date date; verified_date date; lastAttVal_date date; validated_date date; ebuf varchar2(4000); estack varchar2(4000); l_attr_sigid number; sig_required Varchar2(1); fwk_sig_flavor Varchar2(255); email_sig_flavor Varchar2(255); render_hint Varchar2(255); begin -- get the signature policy for the notification -- wf_mail.GetSignaturePolicy(nid, sig_policy); begin 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; /* check if signature is required for this sig_policy*/ GetSignatureRequired(sig_policy, nid,sig_required, fwk_sig_flavor, email_sig_flavor, render_hint); If(sig_required = 'N') then return TRUE; Elsif(sig_required = 'Y') then -- bug 2779748: Cancelled Notification does not need to be signed begin if (WF_Notification.GetAttrText(nid, 'RESULT') = '#SIG_CANCEL') then return TRUE; end if; exception when others then if (wf_core.error_name = 'WFNTF_ATTR') then wf_core.clear; else raise; end if; end; sig_id := WF_DIG_SIG_EVIDENCE_STORE.GetMostRecentSigID('WF_NTF', nid); begin -- #WF_SIG_ID may be defined as text or number... Now both will work -- Eventually should use GetAttrNumber l_attr_sigid := to_number(Wf_Notification.GetAttrText(nid, '#WF_SIG_ID')); exception when others then if (wf_core.error_name = 'WFNTF_ATTR') then wf_core.clear; l_attr_sigid := -1; else raise; end if; end; if (sig_id = -1 or sig_id <> l_attr_sigid) then return FALSE; end if; WF_DIG_SIG_EVIDENCE_STORE.GetSigStatusInfo(sig_id, sig_status, creation_date, signed_date, verified_date, lastAttVal_date, validated_date, ebuf, estack); if (sig_status >= WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZED) then return TRUE; end if; return FALSE; end if; -- Currently only two policies supported. For others assume it is signed return TRUE; exception when others then wf_core.context('Wf_Notification', 'NtfSignRequirementsMet', to_char(nid)); raise; end NtfSignRequirementsMet; -- -- Request More Info -- -- -- UpdateInfo -- non-null username: Ask this user for more information -- null username: Reply to the inquery -- comment could be question or answer -- NOTE: -- This is a Framework specific api. Embedded version SHOULD NOT call -- this api. -- Because we cannot validate a session inside Framework, calling such -- api outside of Framework may produce erroneous result. -- IN -- nid - Notification Id -- username - User to whom the comment is intended -- comment - Comment text -- wl_user - Worklist user to whom the notfication belongs, in case a proxy is acting -- action_source - Source from where the call is made. Could be null or 'WA' -- procedure UpdateInfo(nid in number, username in varchar2, comment in varchar2, wl_user in varchar2, action_source in varchar2) is resource_busy exception; pragma exception_init(resource_busy, -00054); l_from_role varchar2(320); replyby varchar2(320); myusername varchar2(320); mydispname varchar2(360); l_messageType varchar2(8); l_groupId number; l_parameterlist wf_parameter_list_t := wf_parameter_list_t(); mailpref varchar2 (8); recipient_role VARCHAR2 (320); cb varchar2(240); context varchar2(2000); sqlbuf varchar2(2000); tvalue varchar2(4000); nvalue number ; dvalue date ; --Bug 3827935 l_charcheck boolean; --Bug 3065814 l_recip_role varchar2(320); l_orig_recip_role varchar2(320); l_more_info_role varchar2(320); l_question_role varchar2(320); l_dummy varchar2(1); begin -- Framework has control of a session. -- We are not allowed to re-validate a session any more. So we cannot -- use wfa_sec.GetSession() directly. myusername := wfa_sec.GetFWKUserName; -- Bug 3065814 -- Set the global context variables to appropriate values for this mode if (action_source = 'WA') then -- Action is performed by a proxy on behalf of wl_user. g_context_proxy := myusername; g_context_user := UpdateInfo.wl_user; -- In Answer mode, wl_user is the more_info_role and Fwk session user is the proxy_role myusername := UpdateInfo.wl_user; else -- Action is performed by the recipient of the notification g_context_proxy := null; g_context_user := myusername; end if; mydispname := Wf_Directory.GetRoleDisplayName(myusername); g_context_user_comment := updateinfo.comment; --Bug 3065814 --Get the callback function SELECT callback , context ,RECIPIENT_ROLE, ORIGINAL_RECIPIENT, MORE_INFO_ROLE ,from_role into cb, context,l_recip_role , l_orig_recip_role, l_more_info_role, l_from_role FROM wf_notifications WHERE notification_id = nid; g_context_recipient_role := l_recip_role; g_context_original_recipient:= l_orig_recip_role; g_context_from_role := l_from_role; --The new role will be different for 'ANSWER mode --we overwrite it there. g_context_new_role := username; g_context_more_info_role := l_more_info_role; -- If we are in a different Fwk session, need to clear Workflow PLSQL state if (not Wfa_Sec.CheckSession) then Wf_Global.Init; end if; -- question mode if (username is not null) then if (myusername = username) then --Bug 2474770 --If the current user is the same as the one from --whom more-info is requested then raise the error --that you cannot ask for more info from yourself. wf_core.token('USER',username); wf_core.raise('WFNTF_INVALID_MOREINFO_REQUEST'); else -- do not want it hung when some one is doing update. begin select MORE_INFO_ROLE into l_from_role from WF_NOTIFICATIONS where NOTIFICATION_ID = nid for update nowait; exception when NO_DATA_FOUND then null; when resource_busy then wf_core.raise('WFNTF_BEING_UPDATED'); -- ### This notification is being updated currently, please -- ### try again in a brief moment. end; if (cb is not null) then tvalue := myusername; nvalue := nid; -- ### Review Note 2 - cb is from table --Check for bug#3827935 l_charcheck := wf_notification_util.CheckIllegalChar(cb); --Throw the Illegal exception when the check fails -- BINDVAR_SCAN_IGNORE sqlbuf := 'begin '||cb|| '(:p1, :p2, :p3, :p4, :p5, :p6, :p7); end;'; execute immediate sqlbuf using in 'QUESTION', in context, in l_dummy, in l_dummy, in out tvalue, in out nvalue, in out dvalue; end if; -- always allow question -- bug 2474562 -- allows any role, as restriction is done in the pop list level -- if (IsValidInfoRole(nid,username)) then -- shanjgik 01-JUL-03 bug 2887130 -- get mail preference of the user who will respond with more information mailpref := wf_notification.GetMailPreference (username, null, null); -- if there is a valid session, then we can update the FROM_ROLE -- and FROM_USER accurately. if (myusername is not null) then update WF_NOTIFICATIONS set MORE_INFO_ROLE = username, FROM_USER = mydispname, FROM_ROLE = myusername, MAIL_STATUS = decode (mailpref, 'QUERY', '', 'SUMMARY', '', 'SUMHTML','', 'DISABLED', 'FAILED', null, '', 'MAIL') where NOTIFICATION_ID = nid; -- otherwise, we default to what it should be. Unfortunately, if it -- is a group role, we will not be able to identify which member I am. else update WF_NOTIFICATIONS set MORE_INFO_ROLE = username, FROM_USER = TO_USER, FROM_ROLE = RECIPIENT_ROLE, MAIL_STATUS = decode (mailpref, 'QUERY', '', 'SUMMARY', '', 'SUMHTML','', 'DISABLED', 'FAILED', null, '', 'MAIL') where NOTIFICATION_ID = nid; end if; Wf_Notification.SetComments(nid, myusername, username, 'QUESTION', action_source, substrb(comment,1,4000)); -- LANGUAGE here is for FROM_USER which came from WF_NOTIFICATIONS above -- insert into WF_COMMENTS ( -- NOTIFICATION_ID, -- FROM_ROLE, -- FROM_USER, -- COMMENT_DATE, -- ACTION, -- USER_COMMENT, -- LANGUAGE -- ) -- select NOTIFICATION_ID, -- FROM_ROLE, -- FROM_USER, -- sysdate, -- 'QUESTION', -- substrb(comment,1,4000), -- LANGUAGE -- from WF_NOTIFICATIONS -- where NOTIFICATION_ID = nid; -- bug 2474562 -- else -- wf_core.token('ROLE',username); -- wf_core.raise('WFNTF_NOT_PARTICIPANTS'); -- end if; end if; -- answer mode -- NOTE: the language here is the language of the MORE_INFO_ROLE, -- no denormalization is needed here. else -- Do not allow reply when a question has not been asked, or it has -- already been answered. In both cases, MORE_INFO_ROLE is set to null. -- Also acquire a row lock, so that we do not let multiple people to -- answer at the same time. begin select MORE_INFO_ROLE,Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE), RECIPIENT_ROLE,FROM_ROLE into l_from_role, replyby, recipient_role,l_question_role from WF_NOTIFICATIONS where NOTIFICATION_ID = nid and MORE_INFO_ROLE is not null for update nowait; exception when NO_DATA_FOUND then -- if it has no row, we cannot reply to this notification wf_core.raise('WFNTF_CANNOT_REPLY'); -- ### You cannot reply to a question that has not been asked -- ### or has already been answered. when resource_busy then wf_core.raise('WFNTF_BEING_UPDATED'); -- ### This notification is being updated currently, please -- ### try again in a brief moment. end; if (myusername is not null and l_from_role <> myusername) then if (not Wf_Directory.IsPerformer(myusername, l_from_role)) then wf_core.token('ROLE',myusername); wf_core.raise('WFNTF_NOT_PARTICIPANTS'); end if; l_from_role := myusername; replyby := mydispname; end if; if (cb is not null) then tvalue := myusername; nvalue := nid; g_context_new_role := l_question_role; -- ### Review Note 2 - cb is from table -- Check for bug#3827935 l_charcheck := wf_notification_util.CheckIllegalChar(cb); --Throw the Illegal exception when the check fails -- BINDVAR_SCAN_IGNORE sqlbuf := 'begin '||cb|| '(:p1, :p2, :p3, :p4, :p5, :p6, :p7); end;'; execute immediate sqlbuf using in 'ANSWER', in context, in l_dummy, in l_dummy, in out tvalue, in out nvalue, in out dvalue; end if; -- shanjgik 01-JUL-03 bug 2887130 -- get the recipient's(one who requested more information) mail preference mailpref := wf_notification.GetMailPreference (recipient_role, null, null); update WF_NOTIFICATIONS set FROM_USER = replyby, FROM_ROLE = l_from_role, MORE_INFO_ROLE = null, MAIL_STATUS = decode (mailpref, 'QUERY', '', 'SUMMARY', '', 'SUMHTML','', 'DISABLED', 'FAILED', null, '', 'MAIL') where NOTIFICATION_ID = nid; Wf_Notification.SetComments(nid, l_from_role, recipient_role, 'ANSWER', action_source, substrb(comment,1,4000)); -- LANGUAGE here is for FROM_USER which came from GetRoleDisplayName above, -- so the LANGUAGE should be current userenv('LANG'). -- insert into WF_COMMENTS ( -- NOTIFICATION_ID, -- FROM_ROLE, -- FROM_USER, -- COMMENT_DATE, -- ACTION, -- USER_COMMENT, -- LANGUAGE -- ) -- select NOTIFICATION_ID, -- FROM_ROLE, -- FROM_USER, -- sysdate, -- 'ANSWER', -- substrb(comment,1,4000), -- userenv('LANG') -- from WF_NOTIFICATIONS -- where NOTIFICATION_ID = nid; end if; -- Send the notification through email -- wf_xml.EnqueueNotification(nid); -- Bug 2283697 -- To raise an EVENT whenever DML operation is performed on -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table. wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist); wf_event.AddParameterToList('ROLE', username, l_parameterlist); wf_event.AddParameterToList('GROUP_ID', nvl(l_groupId, nid), l_parameterlist); wf_event.addParameterToList('Q_CORRELATION_ID', l_messageType, l_parameterlist); -- Raise the event wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send', p_event_key => to_char(nid), p_parameters => l_parameterlist); exception when OTHERS then Wf_Core.Context('Wf_Notification', 'UpdateInfo', to_char(nid), username, wl_user, action_source); raise; end UpdateInfo; -- bug 2474562 -- deprecate - this api is no longer needed, keeps this for reference only -- -- IsValidInfoRole -- Check to see if a role is a participant so far function IsValidInfoRole(nid in number, username in varchar2) return boolean is itype varchar2(30); ikey varchar2(240); ans number; begin begin -- 99% of the case, it should be found in WIAS select ITEM_TYPE, ITEM_KEY into itype, ikey from WF_ITEM_ACTIVITY_STATUSES where NOTIFICATION_ID = nid; exception when NO_DATA_FOUND then begin -- rarely the nid is from WIASH, but just in case select ITEM_TYPE, ITEM_KEY into itype, ikey from WF_ITEM_ACTIVITY_STATUSES_H where NOTIFICATION_ID = nid; exception when NO_DATA_FOUND then -- Notification only begin select NULL, '#SYNCH' into itype, ikey from WF_NOTIFICATIONS where NOTIFICATION_ID = nid; exception when OTHERS then return(FALSE); end; when OTHERS then return(FALSE); end; when OTHERS then return(FALSE); end; -- check if this is item owner begin select 1 into ans from WF_ITEMS where ITEM_TYPE = itype and ITEM_KEY = ikey and OWNER_ROLE = IsValidInfoRole.username; return(TRUE); exception when NO_DATA_FOUND then null; end; if (itype is null and ikey = '#SYNCH') then -- this is notification only begin select 1 into ans from WF_NOTIFICATIONS where IsValidInfoRole.username in (RECIPIENT_ROLE, ORIGINAL_RECIPIENT) and NOTIFICATION_ID = nid; exception when NO_DATA_FOUND then return(FALSE); end; else -- this is notification from a flow begin -- NOTE -- The following sql is suggested by Deb in the performance team -- it uses the index on group_id which is much more selective -- than those on recipient_role or original_recipient. -- Even though the explain plan seems to indicate a high cost, the -- run time performance on volumn database is much better. select 1 into ans from ( select /*+ leading(grp_id_view) */ RECIPIENT_ROLE , ORIGINAL_RECIPIENT from WF_NOTIFICATIONS a , ( select notification_id group_id from WF_ITEM_ACTIVITY_STATUSES where item_type = itype and item_key = ikey union all select notification_id group_id from WF_ITEM_ACTIVITY_STATUSES_H where item_type = itype and item_key = ikey ) grp_id_view where grp_id_view.group_id = a.group_id ) recipient_view where (recipient_view.RECIPIENT_ROLE = IsValidInfoRole.username or recipient_view.ORIGINAL_RECIPIENT = IsValidInfoRole.username) and rownum < 2; exception when NO_DATA_FOUND then return(FALSE); end; end if; return(TRUE); exception when OTHERS then Wf_Core.Context('Wf_Notification','IsValidInfoRole',to_char(nid),username); raise; end IsValidInfoRole; -- UpdateInfo2 - bug 2282139 -- non-null username - Ask this user for more information -- null username - Reply to the inquery -- from email - from email id of responder/requestor -- comment - could be question or answer -- NOTE: -- This is a WF Mailer specific API. Used when a user requests more info -- or provides info through email response. -- procedure UpdateInfo2(nid in number, username in varchar2, from_email in varchar2, comment in varchar2) is resource_busy exception; pragma exception_init(resource_busy, -00054); l_from_role varchar2(320); replyby varchar2(320); myusername varchar2(320); mydispname varchar2(360); l_messageType varchar2(8); l_groupId number; l_parameterlist wf_parameter_list_t := wf_parameter_list_t(); role_info_tbl wf_directory.wf_local_roles_tbl_type; l_username varchar2(320); l_stat varchar2(8); --Bug 3065814 l_recip_role varchar2(320); l_orig_recip_role varchar2(320); l_more_info_role varchar2(320); cb varchar2(240); context varchar2(2000); sqlbuf varchar2(2000); tvalue varchar2(4000); nvalue number; dvalue date; l_question_role varchar2(320); l_found boolean; l_dummy varchar2(1); -- Bug 3827935 l_charcheck boolean; 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_NOTIFICATION.UpdateInfo2.Begin', 'NID: '||to_char(nid) ||', Username: '||username|| ' From: '||from_email); end if; -- Get notification related information SELECT callback, context, recipient_role, original_recipient, more_info_role, from_role, status INTO cb, context, l_recip_role, l_orig_recip_role, l_more_info_role, l_from_role, l_stat FROM wf_notifications WHERE notification_id = nid; -- Donot process the request if the notification is not open. if (l_stat <> 'OPEN') 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_NOTIFICATION.UpdateInfo2.not_open', 'Notification '||to_char(nid)||' is not OPEN. Returning.'); end if; return; end if; -- mailer doesnot have a valid user session. need to get -- the user name based on the from_email GetUserfromEmail(from_email, myusername, mydispname, l_found); if (l_found) then g_context_user := myusername; else g_context_user := 'email:' || myusername; end if; 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_NOTIFICATION.UpdateInfo2.got_user', 'Email: '||from_email||' User: '||myusername||' DispName: '||mydispname); end if; --Bug 3065814 --Set the global context variables to appropriate values for this mode g_context_user_comment := updateinfo2.comment; g_context_recipient_role := l_recip_role; g_context_original_recipient:= l_orig_recip_role; g_context_from_role := l_from_role; -- The new role will be different for 'ANSWER mode we overwrite it there. g_context_new_role := username; g_context_more_info_role := l_more_info_role; -- question mode if (username is not null) then -- Check if the question is asked to a valid role -- i. The role should be valid within WF Directory Service. -- ii. We might also want to check if the user is a participant of the ntf?? wf_directory.GetRoleInfo2(username, role_info_tbl); l_username := role_info_tbl(1).name; -- Check if it is a Display Name if (l_username is NULL) then begin SELECT name INTO l_username FROM wf_role_lov_vl WHERE upper(display_name) = upper(username) AND rownum = 1; exception when NO_DATA_FOUND then wf_core.token('ROLE', username); wf_core.raise('WFNTF_ROLE'); end; end if; -- If the username was specified as display name, l_username would have the internal name if (l_username in (myusername, l_recip_role)) then -- If the current user is the same as the one from whom more-info is requested -- requested then raise the error that you cannot ask for more info from yourself. wf_core.token('USER',username); wf_core.raise('WFNTF_INVALID_MOREINFO_REQUEST'); else -- do not want it hung when some one is doing update. begin select MORE_INFO_ROLE, MESSAGE_TYPE, GROUP_ID into l_from_role, l_messageType, l_groupId from WF_NOTIFICATIONS where NOTIFICATION_ID = nid for update nowait; exception when NO_DATA_FOUND then null; when resource_busy then wf_core.raise('WFNTF_BEING_UPDATED'); end; 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_NOTIFICATION.UpdateInfo2.question', 'Updating QUESTION'); end if; if (cb is not null) then tvalue := myusername; nvalue := nid; -- ### Review Note 2 - cb is from table -- Check for bug#3827935 l_charcheck := wf_notification_util.CheckIllegalChar(cb); --Throw the Illegal exception when the check fails -- BINDVAR_SCAN_IGNORE sqlbuf := 'begin '||cb|| '(:p1, :p2, :p3, :p4, :p5, :p6, :p7); end;'; execute immediate sqlbuf using in 'QUESTION', in context, in l_dummy, in l_dummy, in out tvalue, in out nvalue, in out dvalue; end if; -- as we donot have a user session for the mailer, the only way to -- find FROM_ROLE and FROM_USER are through the from_addr. If the -- user name and display name are not available, email address is updated /* if (myusername is not null) then update WF_NOTIFICATIONS set MORE_INFO_ROLE = username, FROM_USER = mydispname, FROM_ROLE = myusername where NOTIFICATION_ID = nid; -- otherwise, we default to what it should be. Unfortunately, if it -- is a group role, we will not be able to identify which member I am. else */ update WF_NOTIFICATIONS set MAIL_STATUS = 'MAIL', MORE_INFO_ROLE = l_username, FROM_USER = TO_USER, FROM_ROLE = RECIPIENT_ROLE where NOTIFICATION_ID = nid; /*end if; */ Wf_Notification.SetComments(nid, myusername, l_username, 'QUESTION', null, substrb(comment,1,4000)); -- LANGUAGE here is for FROM_USER which came from WF_NOTIFICATIONS above -- insert into WF_COMMENTS ( -- NOTIFICATION_ID, -- FROM_ROLE, -- FROM_USER, -- COMMENT_DATE, -- ACTION, -- USER_COMMENT, -- LANGUAGE -- ) -- select NOTIFICATION_ID, -- FROM_ROLE, -- FROM_USER, -- sysdate, -- 'QUESTION', -- substrb(comment,1,4000), -- LANGUAGE -- from WF_NOTIFICATIONS -- where NOTIFICATION_ID = nid; end if; -- answer mode -- NOTE: the language here is the language of the MORE_INFO_ROLE, -- no denormalization is needed here. else -- Do not allow reply when a question has not been asked, or it has -- already been answered. In both cases, MORE_INFO_ROLE is set to null. -- Also acquire a row lock, so that we do not let multiple people to -- answer at the same time. begin select MORE_INFO_ROLE, Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE), MESSAGE_TYPE, GROUP_ID , from_role into l_from_role, replyby, l_messageType, l_groupId, l_question_role from WF_NOTIFICATIONS where NOTIFICATION_ID = nid and MORE_INFO_ROLE is not null for update nowait; exception when NO_DATA_FOUND then -- if it has no row, we cannot reply to this notification wf_core.raise('WFNTF_CANNOT_REPLY'); -- ### You cannot reply to a question that has not been asked -- ### or has already been answered. when resource_busy then wf_core.raise('WFNTF_BEING_UPDATED'); end; -- we donot validate the role, it may be email address. we donot want -- FROM_ROLE and FROM_USER to be NULL. l_from_role := myusername; replyby := mydispname; if (cb is not null) then tvalue := myusername; nvalue := nid; g_context_new_role := l_question_role; -- ### Review Note 2 - cb is from table -- BINDVAR_SCAN_IGNORE sqlbuf := 'begin '||cb|| '(:p1, :p2, :p3, :p4, :p5, :p6, :p7); end;'; execute immediate sqlbuf using in 'ANSWER', in context, in l_dummy, in l_dummy, in out tvalue, in out nvalue, in out dvalue; end if; 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_NOTIFICATION.UpdateInfo2.answer', 'Updating ANSWER'); end if; update WF_NOTIFICATIONS set MAIL_STATUS = 'MAIL', FROM_USER = replyby, FROM_ROLE = l_from_role, MORE_INFO_ROLE = null where NOTIFICATION_ID = nid; Wf_Notification.SetComments(nid, myusername, l_recip_role, 'ANSWER', null, substrb(comment,1,4000)); -- LANGUAGE here is for FROM_USER which came from GetRoleDisplayName above, -- so the LANGUAGE should be current userenv('LANG'). -- insert into WF_COMMENTS ( -- NOTIFICATION_ID, -- FROM_ROLE, -- FROM_USER, -- COMMENT_DATE, -- ACTION, -- USER_COMMENT, -- LANGUAGE -- ) -- select NOTIFICATION_ID, -- FROM_ROLE, -- FROM_USER, -- sysdate, -- 'ANSWER', -- substrb(comment,1,4000), -- userenv('LANG') -- from WF_NOTIFICATIONS -- where NOTIFICATION_ID = nid; end if; -- Send the notification through email -- Enqueuing has been moved to a subscription for forward -- compatability. The subscription need only be enabled to use -- the older mailer. The subscription will call the -- wf_xml.EnqueueNotification(nid); -- Bug 2283697 -- To raise an EVENT whenever DML operation is performed on -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table. wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist); wf_event.AddParameterToList('ROLE', username, l_parameterlist); wf_event.AddParameterToList('GROUP_ID', nvl(l_groupId, nid), l_parameterlist); wf_event.addParameterToList('Q_CORRELATION_ID', l_messageType, l_parameterlist); -- Raise the event wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send', p_event_key => to_char(nid), p_parameters => l_parameterlist); exception when OTHERS then Wf_Core.Context('Wf_Notification', 'UpdateInfo2', to_char(nid), username, from_email); raise; end UpdateInfo2; -- UpdateInfoGuest: -- Called for updating more info when access key is present, -- responder to the request more info role as the user -- is trying to respond to Request More Info as GUEST user -- via E-mail without logging in. -- responder - Responder to the request more info -- moreinfoanswer - answer to request more information -- procedure UpdateInfoGuest(nid in number, moreinforesponder in varchar2 default null, moreinfoanswer in varchar2 default null) is resource_busy exception; pragma exception_init(resource_busy, -00054); l_from_role varchar2(320); l_recipient_role varchar2(320); replyby varchar2(320); l_messageType varchar2(8); l_groupId number; l_parameterlist wf_parameter_list_t := wf_parameter_list_t(); l_more_info_role varchar2(320); cb varchar2(240); context varchar2(2000); sqlbuf varchar2(2000); tvalue varchar2(4000); nvalue number; dvalue date; l_question_role varchar2(320); l_dummy varchar2(1); l_orig_recip_role varchar2(320); begin wf_log_pkg.string(WF_LOG_PKG.LEVEL_UNEXPECTED, 'WF_NOTIFICATION.UpdateInfoGuest', 'NID: '||to_char(nid)); -- Do not allow reply when a question has not been asked, or it has -- already been answered. In both cases, MORE_INFO_ROLE is set to null. -- Also acquire a row lock, so that we do not let multiple people to -- answer at the same time. begin select ORIGINAL_RECIPIENT, RECIPIENT_ROLE, MORE_INFO_ROLE, Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE), MESSAGE_TYPE, GROUP_ID , from_role, callback, context into l_orig_recip_role, l_recipient_role, l_from_role, replyby, l_messageType, l_groupId, l_question_role, cb, context from WF_NOTIFICATIONS where NOTIFICATION_ID = nid and MORE_INFO_ROLE is not null for update nowait; --Bug 3924931 --Set the global context variables to appropriate values for this mode g_context_user := updateinfoguest.moreinforesponder; g_context_user_comment := updateinfoguest.moreinfoanswer; g_context_new_role := l_question_role; g_context_recipient_role := l_recipient_role; g_context_original_recipient:= l_orig_recip_role; g_context_from_role := l_question_role; g_context_more_info_role := l_from_role; exception when NO_DATA_FOUND then -- if it has no row, we cannot reply to this notification wf_core.raise('WFNTF_CANNOT_REPLY'); -- ### You cannot reply to a question that has not been asked -- ### or has already been answered. when resource_busy then wf_core.raise('WFNTF_BEING_UPDATED'); end; if (cb is not null) then tvalue := moreinforesponder; nvalue := nid; -- ### Note 2 - cb is from table -- BINDVAR_SCAN_IGNORE sqlbuf := 'begin '||cb|| '(:p1, :p2, :p3, :p4, :p5, :p6, :p7); end;'; execute immediate sqlbuf using in 'ANSWER', in context, in l_dummy, in l_dummy, in out tvalue, in out nvalue, in out dvalue; end if; wf_log_pkg.string(WF_LOG_PKG.LEVEL_UNEXPECTED, 'WF_NOTIFICATION.UpdateInfoGuest', 'Updating ANSWER'); update WF_NOTIFICATIONS set MAIL_STATUS = 'MAIL', FROM_USER = moreinforesponder, FROM_ROLE = moreinforesponder, MORE_INFO_ROLE = null where NOTIFICATION_ID = nid; Wf_Notification.SetComments(nid, moreinforesponder, l_recipient_role, 'ANSWER', null, substrb(moreinfoanswer,1,4000)); -- Send the notification through email -- Enqueuing has been moved to a subscription for forward -- compatability. The subscription need only be enabled to use -- the older mailer. The subscription will call the -- wf_xml.EnqueueNotification(nid); -- Bug 2283697 -- To raise an EVENT whenever DML operation is performed on -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table. wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist); -- skilaru 12-MAR-04 In UpdateInfo2 username would be null in Answer mode -- to keep the behaviour same just pass null as ROLE.. wf_event.AddParameterToList('ROLE', null, l_parameterlist); wf_event.AddParameterToList('GROUP_ID', nvl(l_groupId, nid), l_parameterlist); wf_event.addParameterToList('Q_CORRELATION_ID', l_messageType, l_parameterlist); -- Raise the event wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send', p_event_key => to_char(nid), p_parameters => l_parameterlist); exception when OTHERS then Wf_Core.Context('Wf_Notification', 'UpdateInfoGuest', to_char(nid), moreinforesponder); raise; end UpdateInfoGuest; -- -- HideMoreInfo (PUBLIC) -- Checks the notification attribute #HIDE_MOREINFO to see if the -- More Information request button is allowed or hidden. Just in case -- more_info_role becomes not null with direct table update... function HideMoreInfo(nid in number) return varchar2 is l_hide varchar2(1); begin -- Get value for #HIDE_MOREINFO attribute for the notification begin l_hide := substrb(WF_NOTIFICATION.GetAttrText(nid, '#HIDE_MOREINFO'), 1, 1); -- Bugfix 2880029 - changed sacsharm - 03/31/03 -- Only if attribute value is explicitly 'Y' hide Request More Info. else -- if it is null or 'N' or any other character donot hide Request More Info. l_hide := upper(nvl(l_hide, 'N')); if (l_hide <> 'Y') then l_hide := 'N'; end if; exception when others then -- Bugfix 2880029 - changed sacsharm - 03/31/03 -- If attribute not defined, do not hide Request More Info. if (wf_core.error_name = 'WFNTF_ATTR') then wf_core.clear; l_hide := 'N'; else raise; end if; end; return (l_hide); exception when others then wf_core.context('Wf_Notification', 'HideMoreInfo', to_char(nid)); raise; end HideMoreInfo; -- GetComments -- Consolidates the questions and answers asked for the notification -- Also returns the last question asked. -- This is for the mailer to send the history with the email. -- It assumes that the table has been already opened -- IN -- nid - Notification id -- dislay_type The display type for the history -- OUT -- history in either text or html format -- last asked question procedure GetComments(nid in number, display_type in varchar2, html_history out nocopy varchar2, last_ques out nocopy varchar2) is CURSOR c_ques IS SELECT user_comment FROM wf_comments WHERE notification_id = nid AND action = 'QUESTION' ORDER BY comment_date desc; begin open c_ques; fetch c_ques into last_ques; if (c_ques%notfound) then last_ques := ''; end if; close c_ques; -- Call the GetComments2 procedure to get the Action History for only -- More Info Requests. This procedure was doing that previously Wf_Notification.GetComments2(p_nid => nid, p_display_type => display_type, p_hide_reassign => 'Y', p_hide_requestinfo => 'N', p_action_history => html_history); exception when others then wf_core.context('Wf_Notification', 'GetComments', to_char(nid), display_type); raise; end GetComments; -- -- GetComments2 -- Creates the Action History table for a given a notification id based on -- different filter criteria. -- IN -- p_nid - Notification id -- p_display_type - Display Type -- p_action_type - Action Type to look for (REASSIGN, RESPOND, QA,...) -- p_comment_date - Comment Date -- p_from_role - Comment provider -- p_to_role - Comment receiver -- p_hide_reassign - If Reassign comments be shown or not -- p_hide_requestinfo - If More Info request be shown or not -- OUT -- p_action_history - Action History table -- procedure GetComments2(p_nid in number, p_display_type in varchar2, p_action_type in varchar2, p_comment_date in date, p_from_role in varchar2, p_to_role in varchar2, p_hide_reassign in varchar2, p_hide_requestinfo in varchar2, p_action_history out nocopy varchar2) is -- TYPE c_comments_t IS REF CURSOR; -- l_comments_c c_comments_t; -- TYPE comment_rec_t IS RECORD -- ( -- notification_id number, -- comment_date date, -- action varchar2(30), -- from_user varchar2(360), -- to_user varchar2(360), -- user_comment varchar2(4000) -- ); -- l_comm_rec comment_rec_t; -- l_sql_stmt varchar2(2000); l_user_comment varchar2(4000); i pls_integer; j pls_integer; l_pos pls_integer; l_table_dir varchar2(1); l_dir varchar2(10); cells tdType; l_result varchar2(32000); l_delim varchar2(1); l_note varchar2(4000); l_action varchar2(30); -- l_action_type1 varchar2(10); -- l_action_type2 varchar2(10); l_item_type varchar2(8); l_item_key varchar2(240); l_actid number; l_result_type varchar2(30); l_result_code varchar2(30); l_action_str varchar2(250); l_wf_system varchar2(360); l_count number; l_suppress_hist varchar2(1); l_title varchar2(250); CURSOR c_comments IS select rownum H_SEQUENCE, H_NOTIFICATION_ID, H_FROM_USER, H_TO_USER, H_ACTION_TYPE, H_ACTION, H_COMMENT, H_ACTION_DATE from (select H_SEQUENCE, H_NOTIFICATION_ID, H_FROM_USER, H_TO_USER, H_ACTION_TYPE, H_ACTION, H_COMMENT, H_ACTION_DATE from (select 99999999 H_SEQUENCE, IAS.NOTIFICATION_ID H_NOTIFICATION_ID, IAS.ASSIGNED_USER H_FROM_ROLE, wf_directory.getRoleDisplayName(IAS.ASSIGNED_USER) H_FROM_USER, 'WF_SYSTEM' H_TO_ROLE, l_wf_system H_TO_USER, A.RESULT_TYPE H_ACTION_TYPE, IAS.ACTIVITY_RESULT_CODE H_ACTION, '#WF_NOTE#' H_COMMENT, nvl(IAS.END_DATE, IAS.BEGIN_DATE) H_ACTION_DATE from WF_ITEM_ACTIVITY_STATUSES IAS, WF_ACTIVITIES A, WF_PROCESS_ACTIVITIES PA, WF_ITEMS I where IAS.ITEM_TYPE = l_item_type and IAS.ITEM_KEY = l_item_key and IAS.PROCESS_ACTIVITY = l_actid and IAS.ITEM_TYPE = I.ITEM_TYPE and IAS.ITEM_KEY = I.ITEM_KEY and IAS.ACTIVITY_RESULT_CODE IS NOT NULL and IAS.ACTIVITY_RESULT_CODE not in( '#EXCEPTION', '#FORCE', '#MAIL', '#NULL', '#STUCK', '#TIMEOUT') and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE) and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID and PA.ACTIVITY_NAME = A.NAME and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE union all select 99999999 H_SEQUENCE, IAS.NOTIFICATION_ID H_NOTIFICATION_ID, IAS.ASSIGNED_USER H_FROM_ROLE, wf_directory.getRoleDisplayName(IAS.ASSIGNED_USER) H_FROM_USER, 'WF_SYSTEM' H_TO_ROLE, l_wf_system H_TO_USER, A.RESULT_TYPE H_ACTION_TYPE, IAS.ACTIVITY_RESULT_CODE H_ACTION, '#WF_NOTE#' H_COMMENT, nvl(IAS.END_DATE, IAS.BEGIN_DATE) H_ACTION_DATE from WF_ITEM_ACTIVITY_STATUSES_H IAS, WF_ACTIVITIES A, WF_PROCESS_ACTIVITIES PA, WF_ITEMS I where IAS.ITEM_TYPE = l_item_type and IAS.ITEM_KEY = l_item_key and IAS.PROCESS_ACTIVITY = l_actid and IAS.ITEM_TYPE = I.ITEM_TYPE and IAS.ITEM_KEY = I.ITEM_KEY and IAS.ACTIVITY_RESULT_CODE IS NOT NULL and IAS.ACTIVITY_RESULT_CODE not in( '#EXCEPTION', '#FORCE', '#MAIL', '#NULL', '#STUCK', '#TIMEOUT') and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE) and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID and PA.ACTIVITY_NAME = A.NAME and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE union all select C.SEQUENCE H_SEQUENCE, C.NOTIFICATION_ID H_NOTIFICATION_ID, C.FROM_ROLE H_FROM_ROLE, C.FROM_USER H_FROM_USER, C.TO_ROLE H_TO_ROLE, C.TO_USER H_TO_USER, '#WF_COMMENTS#' H_ACTION_TYPE, C.ACTION H_ACTION, C.USER_COMMENT H_COMMENT, C.COMMENT_DATE H_ACTION_DATE from WF_ITEM_ACTIVITY_STATUSES IAS, WF_COMMENTS C where IAS.ITEM_TYPE = l_item_type and IAS.ITEM_KEY = l_item_key and IAS.PROCESS_ACTIVITY = l_actid and IAS.NOTIFICATION_ID = C.NOTIFICATION_ID and C.ACTION not in ('RESPOND', 'RESPOND_WA', 'RESPOND_RULE', 'SEND') union all select C.SEQUENCE H_SEQUENCE, C.NOTIFICATION_ID H_NOTIFICATION_ID, C.FROM_ROLE H_FROM_ROLE, C.FROM_USER H_FROM_USER, C.TO_ROLE H_TO_ROLE, C.TO_USER H_TO_USER, '#WF_COMMENTS#' H_ACTION_TYPE, C.ACTION H_ACTION, C.USER_COMMENT H_COMMENT, C.COMMENT_DATE H_ACTION_DATE from WF_ITEM_ACTIVITY_STATUSES_H IAS, WF_COMMENTS C where IAS.ITEM_TYPE = l_item_type and IAS.ITEM_KEY = l_item_key and IAS.PROCESS_ACTIVITY = l_actid and IAS.NOTIFICATION_ID = C.NOTIFICATION_ID and C.ACTION not in ('RESPOND', 'RESPOND_WA', 'RESPOND_RULE', 'SEND') ) order by H_NOTIFICATION_ID, H_ACTION_DATE, H_SEQUENCE ); cursor c_ntf_hist is select rownum H_SEQUENCE, H_NOTIFICATION_ID, H_FROM_USER, H_TO_USER, H_ACTION_TYPE, H_ACTION, H_COMMENT, H_ACTION_DATE from (select H_SEQUENCE, H_NOTIFICATION_ID, H_FROM_USER, H_TO_USER, H_ACTION_TYPE, H_ACTION, H_COMMENT, H_ACTION_DATE from (select C.SEQUENCE H_SEQUENCE, C.NOTIFICATION_ID H_NOTIFICATION_ID, C.FROM_ROLE H_FROM_ROLE, C.FROM_USER H_FROM_USER, C.TO_ROLE H_TO_ROLE, C.TO_USER H_TO_USER, C.ACTION_TYPE H_ACTION_TYPE, C.ACTION H_ACTION, C.USER_COMMENT H_COMMENT, C.COMMENT_DATE H_ACTION_DATE from WF_ITEM_ACTIVITY_STATUSES IAS, WF_COMMENTS C where IAS.ITEM_TYPE = l_item_type and IAS.ITEM_KEY = l_item_key and IAS.PROCESS_ACTIVITY = l_actid and IAS.NOTIFICATION_ID = C.NOTIFICATION_ID and C.ACTION_TYPE in ('REASSIGN', 'QA') union all select C.SEQUENCE H_SEQUENCE, C.NOTIFICATION_ID H_NOTIFICATION_ID, C.FROM_ROLE H_FROM_ROLE, C.FROM_USER H_FROM_USER, C.TO_ROLE H_TO_ROLE, C.TO_USER H_TO_USER, C.ACTION_TYPE H_ACTION_TYPE, C.ACTION H_ACTION, C.USER_COMMENT H_COMMENT, C.COMMENT_DATE H_ACTION_DATE from WF_ITEM_ACTIVITY_STATUSES_H IAS, WF_COMMENTS C where IAS.ITEM_TYPE = l_item_type and IAS.ITEM_KEY = l_item_key and IAS.PROCESS_ACTIVITY = l_actid and IAS.NOTIFICATION_ID = C.NOTIFICATION_ID and C.ACTION_TYPE in ('REASSIGN', 'QA') ) order by H_NOTIFICATION_ID, H_ACTION_DATE, H_SEQUENCE ); l_comm_rec c_comments%ROWTYPE; l_ntf_hist_rec c_ntf_hist%ROWTYPE; begin -- Bug 3767799 -- Getting comments based on different filter criteria is not implemented in Fwk UI. We will not -- implement the same in PLSQL Action History also. Given the Notification Id, we will return the -- complete Action History based on the Cursor above. -- if (p_hide_reassign = 'Y' and p_hide_requestinfo = 'Y') then -- l_action_type1 := 'REASSIGN'; -- l_action_type2 := 'QA'; -- elsif (p_hide_reassign = 'N' and p_hide_requestinfo = 'Y') then -- l_action_type1 := 'QA'; -- l_action_type2 := 'QA'; -- elsif (p_hide_reassign = 'Y' and p_hide_requestinfo = 'N') then -- l_action_type1 := 'REASSIGN'; -- l_action_type2 := 'REASSIGN'; -- else -- l_action_type1 := 'X'; -- l_action_type2 := 'X'; -- end if; -- l_sql_stmt := 'SELECT notification_id, comment_date, action, from_user, to_user, user_comment '|| -- ' FROM wf_comments '|| -- ' WHERE ((:p1 is not null and action_type like :p2) '|| -- ' or (:p3 is null and action_type not in (:p4, :p5))) '|| -- ' AND comment_date = nvl(:p6, comment_date) '|| -- ' AND from_role = nvl(:p7, from_role) '|| -- ' AND to_role = nvl(:p8, to_role) '|| -- ' AND notification_id = :p9 '|| -- ' ORDER BY comment_date DESC, action_type ASC'; begin SELECT item_type, item_key, process_activity INTO l_item_type, l_item_key, l_actid FROM wf_item_activity_statuses WHERE notification_id = p_nid; exception when NO_DATA_FOUND then begin SELECT item_type, item_key, process_activity INTO l_item_type, l_item_key, l_actid FROM wf_item_activity_statuses_h WHERE notification_id = p_nid; exception when NO_DATA_FOUND then -- It is possible that notification is sent outside of a flow, -- in that case, it will not appear in Workflow runtime tables. -- Just return here. return; end; end; if (l_item_type in ('POSCHORD', 'POSUPDNT', 'POSORDNT', 'POSASNNB', 'CREATEPO', 'POAPPRV', 'POPRICAT', 'PORCOTOL', 'PONGRQCH', 'POERROR', 'POWFDS', 'RCVDMEMO', 'APVRMDER', 'POREQCHA', 'PORCPT', 'REQAPPRV', 'PORPOCHA')) then l_suppress_hist := 'Y'; l_title := Wf_Core.Translate('WFNTF_NTF_HISTORY'); else l_suppress_hist := 'N'; l_title := Wf_Core.Translate('WFNTF_ACTION_HISTORY'); end if; l_wf_system := Wf_Core.Translate('WF_SYSTEM'); l_delim := ':'; l_table_dir := table_direction; if (l_table_dir = 'L') then l_dir := null; else l_dir := 'dir="RTL"'; end if; j := 1; -- Action History Title cells(j) := wf_core.translate('NUM'); if (p_display_type = wf_notification.doc_html) then cells(j) := 'S5%:'||cells(j); end if; j := j+1; cells(j) := wf_core.translate('ACTION_DATE'); if (p_display_type = wf_notification.doc_html) then cells(j) := 'S15%:'||cells(j); end if; j := j+1; cells(j) := wf_core.translate('ACTION'); if (p_display_type = wf_notification.doc_html) then cells(j) := 'S10%:'||cells(j); end if; j := j+1; cells(j) := wf_core.translate('FROM'); if (p_display_type = wf_notification.doc_html) then cells(j) := 'S15%:'||cells(j); end if; j := j+1; cells(j) := wf_core.translate('TO'); if (p_display_type = wf_notification.doc_html) then cells(j) := 'S15%:'||cells(j); end if; j := j+1; cells(j) := wf_core.translate('DETAILS'); if (p_display_type = wf_notification.doc_html) then cells(j) := 'S40%:'||cells(j); end if; j := j+1; -- OPEN l_comments_c FOR l_sql_stmt using p_action_type, p_action_type, p_action_type, -- l_action_type1, l_action_type2, p_comment_date, p_from_role, p_to_role, p_nid; if (l_suppress_hist = 'N') then OPEN c_comments; -- Construct the action history table with all the matching comments records loop fetch c_comments into l_comm_rec; exit when c_comments%NOTFOUND; cells(j) := to_char(l_comm_rec.h_sequence); j := j+1; if (p_display_type = wf_notification.doc_html) then cells(j) := 'S:'||to_char(l_comm_rec.h_action_date, Wf_Notification.g_nls_date_mask); else cells(j) := to_char(l_comm_rec.h_action_date, Wf_Notification.g_nls_date_mask); end if; j := j+1; -- If the record is not from WF_COMMENTS, need to resolve the action if (l_comm_rec.h_action_type <> '#WF_COMMENTS#') then l_action_str := Wf_Core.Activity_Result(l_comm_rec.h_action_type, l_comm_rec.h_action); else l_action := l_comm_rec.h_action; l_pos := instr(l_action, '_', 1); if (l_pos > 0) then l_action := substr(l_action, 1, l_pos-1); end if; l_action_str := Wf_Core.Translate(l_action); end if; if (p_display_type = wf_notification.doc_html) then cells(j) := 'S:'||l_action_str; else cells(j) := l_action_str; end if; j := j+1; if (p_display_type = wf_notification.doc_html) then cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_comm_rec.h_from_user); else cells(j) := l_comm_rec.h_from_user; end if; j := j+1; if (p_display_type = wf_notification.doc_html) then cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_comm_rec.h_to_user); else cells(j) := l_comm_rec.h_to_user; end if; j := j+1; l_note := l_comm_rec.h_comment; -- WF_NOTE indicates that this is a Respond attribute. if (l_note = '#WF_NOTE#') then begin SELECT text_value INTO l_note FROM wf_notification_attributes WHERE notification_id = l_comm_rec.h_notification_id AND name = 'WF_NOTE'; exception when no_data_found then l_note := ''; end; end if; if (p_display_type = wf_notification.doc_html) then l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000); end if; cells(j) := l_note; if (p_display_type = wf_notification.doc_html) then if (cells(j) is null) then cells(j) := 'S: '; else cells(j) := 'S:'||cells(j); end if; end if; j := j+1; end loop; l_count := c_comments%rowcount; CLOSE c_comments; elsif (l_suppress_hist = 'Y') then OPEN c_ntf_hist; -- Construct the action history table with all the matching comments records loop fetch c_ntf_hist into l_ntf_hist_rec; exit when c_ntf_hist%NOTFOUND; cells(j) := to_char(l_ntf_hist_rec.h_sequence); j := j+1; if (p_display_type = wf_notification.doc_html) then cells(j) := 'S:'||to_char(l_ntf_hist_rec.h_action_date, Wf_Notification.g_nls_date_mask); else cells(j) := to_char(l_ntf_hist_rec.h_action_date, Wf_Notification.g_nls_date_mask); end if; j := j+1; l_action := l_ntf_hist_rec.h_action; l_pos := instr(l_action, '_', 1); if (l_pos > 0) then l_action := substr(l_action, 1, l_pos-1); end if; l_action_str := Wf_Core.Translate(l_action); if (p_display_type = wf_notification.doc_html) then cells(j) := 'S:'||l_action_str; else cells(j) := l_action_str; end if; j := j+1; if (p_display_type = wf_notification.doc_html) then cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_ntf_hist_rec.h_from_user); else cells(j) := l_ntf_hist_rec.h_from_user; end if; j := j+1; if (p_display_type = wf_notification.doc_html) then cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_ntf_hist_rec.h_to_user); else cells(j) := l_ntf_hist_rec.h_to_user; end if; j := j+1; l_note := l_ntf_hist_rec.h_comment; if (p_display_type = wf_notification.doc_html) then l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000); end if; cells(j) := l_note; if (p_display_type = wf_notification.doc_html) then if (cells(j) is null) then cells(j) := 'S: '; else cells(j) := 'S:'||cells(j); end if; end if; j := j+1; end loop; l_count := c_ntf_hist%rowcount; CLOSE c_ntf_hist; end if; -- If there is nothing to display, return a null if (l_count = 0) then p_action_history := ''; return; end if; -- Sequence is now based on the rownum, not the reverse -- for k in 0..(i-1) loop -- if (p_display_type = wf_notification.doc_html) then -- cells((k+1)*6+1) := 'C:'||to_char(i-k-1); -- else -- cells((k+1)*6+1) := to_char(i-k-1); -- end if; -- end loop; -- Construct table from the cells if (p_display_type = wf_notification.doc_html) then table_width := '100%'; NTF_Table(cells=>cells, col=>6, type=>'H'||l_table_dir, rs=>l_result); -- Display title "Action History" l_result := '
| '|| l_title||' |
| '||l_result||' |