How to send an email from oracle workflow process using an AdHocRole?

If you have any problems and suggestions you can send email to my email address:[email protected].i want you!! (+_+)

1.檢測Workflow Notification Mailer配置是否可以發郵件。

step1：Workflow Administrator Web-->Oracle Applications Manager-->Workflow Manager 頁面上Notification Mailers和Agent Listeners是否打開。如果沒有點Notification Mailers進行配置。

step2：Test Mailer 進行測試。點選上圖的Test Mailer 按鈕進行測試。

<Oracle+Workflow實力詳解.doc>

http://www.baidu.com/s?tn=baiduhome_pg&ie=utf-8&bs=Oracle%2BWorkflow+%E5%AE%9E%E5%8A%9B%E8%AF%A6%E8%A7%A3.doc&f=8&rsv_bp=1&rsv_spt=1&wd=Oracle%2BWorkflow+%E5%AE%9E%E4%BE%8B%E8%AF%A6%E8%A7%A3.doc&inputT=11217

Workflow Notification Mailer Setup

http://onlineappsdba.com/index.php/2007/12/27/workflow-notification-mailer-setup-in-oracle-apps-r1212i/

Oracle Workflow Administrator's Guide

http://docs.oracle.com/cd/E18727_01/doc.121/e12903/T319398T319402.htm#T319579

2.PL/SQL

KAGE:hp_test_sendmail_pkg

API:

WF_DIRECTORY.CreateAdHocRole(role_name => v_role_email,

                             role_display_name => v_role_email,

                             language => 'AMERICAN',

                             territory =>'AMERICA',

                             email_address => v_role_email,

                             notification_preference =>'MAILHTM2',

                             role_users => l_users,

                             status =>'ACTIVE',

                             expiration_date =>NULL);

Parameter：

        email_address：--發送email不能為空

        notification_preference--Indicate how this role receives notifications:  'MAILTEXT', 'MAILHTML', 'MAILATTH', 'MAILHTM2', 'QUERY', 'SUMMARY', or, for Oracle Applications only, 'SUMHTML'. If null, the procedure sets the notification preference to'MAILHTML'.       

      role_users：--need to pass a comma or space delimited list如：role_users => 'OFFSHORE, BDEVLIN'  或者為null

API--Oracle Workflow API Reference

http://web.njit.edu/info/limpid/DOC/workflow.102/b15855/toc.htm

notification_preference參數詳情--Oracle Workflow Administrator's Guide

http://docs.oracle.com/cd/E18727_01/doc.121/e12903/T319398T319402.htm#I_choosenp

測試使用的PL/SQL:

CREATEORREPLACEPACKAGEBODY hp_test_sendmail_pkg AS

procedure SEND_WFM_OPEN_MAIL(nn invarchar2defaultnull)is

l_itemtypeVARCHAR2(100) := 'HP_016';

l_itemkeyVARCHAR2(100);

v_role_emailVARCHAR2(100) := '[email protected]';

n_count_roleINTEGER := 0;

l_processVARCHAR2(100) := 'XX_TEST_MAIL_04';

l_RECI_ROLEVARCHAR2(100) := 'RECI_ROLE';

l_usersVARCHAR2(100);

l_fromVARCHAR2(100);

BEGIN

SELECTCOUNT(*)

INTO n_count_role

FROM wf_local_roles

WHERENAME = v_role_email;

IF n_count_role = 0THEN

WF_DIRECTORY.CreateAdHocRole(role_name => v_role_email,

role_display_name => v_role_email,

language => 'AMERICAN',

territory =>'AMERICA',

email_address => v_role_email,

notification_preference =>'MAILHTM2',

role_users => l_users,

status =>'ACTIVE',

expiration_date =>NULL);

ENDIF;

SELECT oe_order_headers_s.NEXTVAL INTO l_itemkey FROM dual;

wf_engine.createprocess(itemtype => l_itemtype,

itemkey => l_itemkey,

process => l_process,

user_key => l_itemkey);

BEGIN

selectname

into l_from

from wf_local_roles

wherenamelike'SYSADMIN';

EXCEPTION

WHENOTHERSTHEN

NULL;

END;

wf_engine.setitemattrtext(itemtype => l_itemtype,

itemkey => l_itemkey,

aname =>'#WFM_REPLYTO',

avalue => l_from);

wf_engine.setitemattrtext(itemtype => l_itemtype,

itemkey => l_itemkey,

aname =>'#WFM_FROM',

avalue => l_from);

wf_engine.setitemattrtext(itemtype => l_itemtype,

itemkey => l_itemkey,

aname => l_RECI_ROLE,

avalue => v_role_email);

--設定Document類型Attribute  木有用啊

wf_engine.setitemattrtext(itemtype => l_itemtype,

itemkey => l_itemkey,

aname =>'#HDR_REGION',

avalue =>'PLSQLCLOB:CUX_WF_DEMO_PKG.GET_DOCUMENT_DETAILS/' ||

l_itemtype ||':' || l_itemkey);

wf_engine.startprocess(itemtype => l_itemtype, itemkey => l_itemkey);

COMMIT;

END SEND_WFM_OPEN_MAIL;

PROCEDURE REJECT_DEAL(ITEMTYPE INVARCHAR2,

ITEMKEYINVARCHAR2,

ACTIDINNUMBER,

FUNCMODEINVARCHAR2,

RESULTOUTOUTNOCOPYVARCHAR2)IS

BEGIN

BEGIN

INSERTINTO hp_test_table

(a1, a2, a3, a4, a5)

values

('mail testing', ITEMTYPE, ITEMKEY, ACTID, FUNCMODE);

commit;

RESULTOUT :='COMPLETE';

EXCEPTION

WHENOTHERSTHEN

NULL;

END;

END REJECT_DEAL;

--獲得Document Attribute内容

PROCEDURE get_document_details(document_id INVARCHAR2,

display_typeINVARCHAR2,

documentINOUTCLOB,

document_typeINOUTVARCHAR2)IS

l_item_type wf_items.item_type%TYPE;

l_item_key wf_items.item_key%TYPE;

l_document_idNUMBER;

l_org_idNUMBER;

l_documentVARCHAR2(32000) := '';

l_clob_documentCLOB;

BEGIN

l_item_type := substr(document_id,1, instr(document_id, ':') - 1);

l_item_key := substr(document_id,

instr(document_id,':') + 1,

length(document_id) -2);

l_document_id := wf_engine.getitemattrnumber(itemtype => l_item_type,

itemkey => l_item_key,

aname =>'DOCUMENT_ID');

l_org_id := wf_engine.getitemattrnumber(itemtype => l_item_type,

itemkey => l_item_key,

aname =>'ORG_ID');

fnd_client_info.set_org_context(to_char(l_org_id));

l_document :='<HTML> <HEAD> <TITLE> Oracle Workflow HP </TITLE> <STYLE><!--&TEMPLATE_STYLE--></STYLE></HEAD> <BODY BGCOLOR="#FFFFFF" > <P><SPAN class="OraTipLabel">&TIMEZONE</SPAN><P>&HEADER<P>&BODY';

dbms_lob.createtemporary(lob_loc => l_clob_document,

cache =>TRUE,

dur => dbms_lob.session);

dbms_lob.OPEN(lob_loc => l_clob_document,

open_mode => dbms_lob.lob_readwrite);

wf_notification.writetoclob(l_clob_document, l_document);--追加，可不斷寫

dbms_lob.append(document, l_clob_document);

IF (dbms_lob.ISOPEN(l_clob_document) = 1)THEN

dbms_lob.CLOSE(l_clob_document);

dbms_lob.freetemporary(l_clob_document);

ENDIF;

document := document || l_document;

END;

end hp_test_sendmail_pkg;

3.Workflow Process Image

4.Attributes

(1)Notification Mailer Message Template Attributes

• #WFM_OPEN_MAIL - Specify the template to use for e-mail notifications that require a response, if you are using the templated response method.
• #WFM_OPEN_MAIL_DIRECT - Specify the template to use for e-mail notifications that require a response, if you are using the direct response method.
• #WFM_OPEN_MAIL_FYI - Specify the template to use for e-mail notifications that do not require a response.
• #ATTACHED_URLS - Specify the template to use to create the Notification References attachment for HTML-formatted notification messages that include URL attributes with Attach Content checked.
• #WFM_CANCELED - Specify the template to use to inform the recipient that a previously sent notification is canceled.
• #WFM_OPEN_INVALID - Specify the template to send to a user when the user responds incorrectly to a notification.
• #WFM_CLOSED - Specify the template to use to inform the recipient that a previously sent notification is now closed.
• #WFM_OPEN_MORE_INFO - Specify the template to use to send a request for more information from one user to another user.

        System Template：

(2)Notification Mailer Attributes

You can use special message attributes to control how a notification mailer generates the e-mail message for a notification, if the recipient has a notification preference to receive e-mail notifications. For example, if you want to customize notifications from a particular department, you can define these attributes for those notifications.

• #WFM_FROM - Define this attribute for a message to specify the value that appears in the From field of the message header when the e-mail notification message is delivered to a user. If the #WFM_FROM message attribute is defined for a notification, the notification mailer that sends the message will use the #WFM_FROM attribute value in the From field for that message, overriding the mailer's From Address parameter value。

  

• #WFM_REPLYTO - Define this attribute for a message to specify the address of the e-mail account that receives incoming messages, to the which e-mail notification response should be sent. If the #WFM_REPLYTO message attribute is defined for a notification, the notification mailer that sends the message will use the #WFM_REPLYTO attribute value as the reply address for that message, overriding the mailer's Reply To parameter value.

  

• #WFM_HTMLAGENT - Define this attribute for a message to specify the base URL that identifies the HTML Web agent that handles HTML notification responses. This URL is required to support e-mail notifications with HTML attachments. The default URL is derived from the Workflow Web Agent specified in the Global Preferences Web page in standalone Oracle Workflow, or from the Applications Web Agent (APPS_WEB_AGENT) profile option in Oracle Applications. However, you can override this default by defining a different value for this attribute. If the #WFM_HTMLAGENT message attribute is defined for a notification, the notification mailer that sends the message will use the #WFM_HTMLAGENT attribute value as the HTML Web agent for that message, overriding the mailer's HTML Agent parameter value.
• #WFM_LANGUAGE - Define this attribute for a message to specify the value of the NLS_LANGUAGE database initialization parameter that determines the language for the e-mail notification. Set the attribute value to a language name supported by the Oracle Database, such as ARABIC. If the #WFM_LANGUAGE message attribute is defined with a valid value for a notification, the notification mailer that sends the message will use the #WFM_LANGUAGE attribute value when generating the e-mail message, overriding the language preference of the recipient role.

• #WFM_TERRITORY - Define this attribute for a message to specify the value of the NLS_TERRITORY database initialization parameter that determines the territory-dependent formatting for the e-mail notification. Set the attribute value to a territory name supported by the Oracle Database, such as UNITED ARAB EMIRATES. If the #WFM_TERRITORY message attribute is defined with a valid value for a notification, the notification mailer that sends the message will use the #WFM_TERRITORY attribute value when generating the e-mail message, overriding the territory preference of the recipient role.

  Note: The #WFM_LANGUAGE and #WFM_TERRITORY attributes function independently of each other. If you define only one of these attributes with an overriding value for the corresponding NLS parameter, the notification mailer still uses the recipient role's preference for the other parameter.

Oracle Workflow Developer's Guide

http://www.stanford.edu/dept/itss/docs/oracle/10gR2/workflow.102/b15854/T309002T309007.htm

5.Question

(1)Message有傳回值，在發送的郵件中并不需要流程準許的連結，而讓使用者登陸EBS進行流程處理。

在發送的郵件中會有三個連結，點選其中的一個連結是對#WFM_REPLYTO 所填郵箱位址回複郵件，假如#WFM_REPLYTO 為空，預設回複位址為Inbound EMail 的Reply To Address所填郵箱位址，并不會對工作流進行Approve和Reject。（可能是EBS配置的問題，未找到原因）。

#HIDE_MOREINFO --Request Information Link 的隐藏

Oracle Workflow Developer's Guide

http://www.stanford.edu/dept/itss/docs/oracle/10gR2/workflow.102/b15854/T309002T309007.htm

Choose1：#WF_SECURITY_POLICY Attribute

You can use a special message attribute with the internal name #WF_SECURITY_POLICY to control whether notifications that include sensitive content can be sent in e-mail. If you specify that a notification's content must not be sent in e-mail, users receive an e-mail message that only informs them that they must access the notification through the Notification Details Web page instead to view its content and respond. See: Reviewing Notifications via Electronic Mail, Oracle Workflow User's Guide.

The #WF_SECURITY_POLICY attribute must be of type text. To prevent notification content from being sent in e-mail, set the value of the #WF_SECURITY_POLICY attribute to NO_EMAIL. If you set the value to EMAIL_OK or DEFAULT, leave the value blank, or if you do not define a #WF_SECURITY_POLICY attribute for the message, Oracle Workflow sends the full notification content in e-mail to users whose notification preference is set to receive e-mail.

You can either specify a constant value for the #WF_SECURITY_POLICY attribute, or specify an item type attribute as the value and include logic in your workflow process that dynamically determines at runtime whether the notification content can be sent in e-mail or not and sets that item type attribute accordingly.

Choose2：Notification Mailer Message Template Attributes的Default Value進行設定。

• If you do not want to send an embedded region in e-mail, you can exclude the message body from notification e-mail messages by assigning the notification a message template that directs recipients to access the notification through the Worklist Web pages instead.
  • For a notification that requires a response, define the special message attributes #WFM_OPEN_MAIL and #WFM_OPEN_MAIL_DIRECT, and set the values of these attributes toWFMAIL:VIEW_FROMUI to use the Workflow View From UI message template.
  • For a notification that does not require a response, define the special message attribute #WFM_OPEN_MAIL_FYI, and set the value of this attribute toWFMAIL:VIEW_FROMUI_FYI to use the Workflow View FYI From UI message template.

（2）#HDR_REGION Attribute 和#RELATED_APPL Attribute的使用。

        http://apps2fusion.com/at/74-pk/386-integrating-oaf-notifications-with-oracle-workflow