Oracle Workflow Notifications John Peters JRPJR Inc john

Oracle Workflow Notifications John Peters JRPJR, Inc. john. peters@jrpjr. com 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 4 John Peters, JRPJR, Inc. 1

• How many of you have worked with Oracle Workflow? • How many of you have modified Workflow Notifications? • How many of you have a minimal understanding of PL/SQL 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 4 John Peters, JRPJR, Inc. 2

What you should learn from this presentation: • How does Oracle Workflow build a Notification • Some tips for setting up the Workflow Notification mailer • Some simple customizations you can perform on Workflow Notifications 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 4 John Peters, JRPJR, Inc. 3

Versions Being Shown • All of my examples are Workflow 2. 6. 3 • This is the version shipped with 11. 5. 9 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 4 John Peters, JRPJR, Inc. 4

Presentation Emphasis • Primary emphasis of this presentation will be the changes to workflow notifications that are necessary for them to be sent outside of a company. • The topics covered will be applicable even within a company if you want to personalize the notification messaging 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 5

Notification Components Notifications are comprised of: - A Notification Activity - A Message Template - Optional Message Item Attribute(s) 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 6

Notification Activity • Think of this as a container to hold and call the message from. • This is what you will place on your workflow diagram. • When inserting a Notification Activity on a workflow diagram remember to provide a value for the Performer. If you don’t you will get a workflow runtime error about a NULL Performer. 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 7

Notification Expand Roles • If you are sending the Notification to a Role (everyone with the System Administrator Responsibility) the Expand Roles is very important • Checked, this will create a separate Notification to each person in the Role. If an action is expected every person has an opportunity to act on the Notification • Unchecked, this will create a single Notification which is emailled to each person in the Role. The first person to act on the notification completes (or closes) it. 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 8

Message • This is what contains the text you are going to send. • You can format messages at Text or HTML • Message Bodies can include Attributes (variables) 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 9

Resulting Message • This is the result of the HTML message body • Notice the extra line ‘Oracle Workflow Notification (FYI)’. This came from the message template. 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 10

Message Templates • Oracle comes seeded with Message Templates in the System: Mailer Item Type. • In the PC Client Workflow Builder – Open the System: Mailer Item Type from the database – The Internal Name is WFMAIL 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 11

Open FYI Mail Template • Here is where that extra text comes from. 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 12

You Can Customize Templates • Do not modify the Oracle Supplied Message Templates • You can create custom Message Templates in the System: Mailer Item Type • You can also create custom Message Templates in any Item Type, including the one you are customizing. • Details are in: Oracle Workflow Administrator’s Guide, RELEASE 2. 6. 3, Part No. B 10283– 01 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 13

My Customized Template 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 14

How to Reference Customized Templates • Workflow Mailer Configuration using OAM 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 15

How to Reference Customized Templates • Workflow Message Attributes • Right mouse button on the Message Name • Select New Attribute … • This Attribute must have an internal name of #WFM_OPEN_MAIL_FYI 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 16

How to Reference Customized Templates • The value must be of the format: <ITEM_TYPE>: <MESSAGE NAME> 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 17

Here is the result • Notice other possibly objectionable message components if the message is going external 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 18

Message Attachments • Message Attachments are controlled by the User Preferences • To suppress the Attachments choose ‘HTML mail’ • This can be done at the User level 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 19

Message Attributes • Message Attributes allow you to modify features of a Workflow Notification, without having to create separate Workflow Mailers. • There are dozens of Special Message Attributes. I am only showing two here. • #WFM_FROM – Changes the From Email address • #WFM_REPLYTO – Changes the Reply To Email • See the following document for more information on Message Attributes: Oracle Workflow Developer’s Guide, Release 2. 6. 3 Part No. B 10284– 01 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 20

Using #WFM_FROM • This message attribute will change the From Email Address in the Notification. 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 21

Document Message Attributes • Document Attributes allow you to build a Message Body From a Query • Document Type Attributes reference PL/SQL procedures to build a text stream • Datatypes supported are: VARCHAR 2 – 32 KB Size Limitation CLOB – 4 GB (possible limitations due to workflow engine limitation) BLOB – 4 GB (possible limitations due to workflow engine limitation) 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 22

Create a Document Attribute • This will be a holder for a reference to the PL/SQL procedure and any required parameters • Right mouse on Attributes • Select New Attribute … 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 23

Create a Function • This Workflow function will set the Document Attribute values when the Workflow is run. • Right mouse on Function • Select New Function … 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 24

Create Workflow Process • I am going to build a new demo Workflow Process • Right mouse on Process • Select New Process … 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 25

Build the Workflow Process • Drag Functions in to Process • Set Document Attribute, then Send Notification 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 26

PL/SQL Code • I have removed exception handling and other constructs to make the code easy to read. • The first procedure sets the Document Attribute • The second procedure builds the Document text stream. 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 27

Procedure to Set the Document Attribute • Document Attributes Values plsql: <procedure>/<document_identifier> • procedure = PL/SQL Procedure • document_identifier = usually a concatenation of ITEM_TYPE: ITEM_KEY • This is the only way to pass parameters specific to the Workflow Instance. 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 28

PL/SQL Code – Set Attribute -- Portion of the Package ZZZZ_DEMO_WF_NOTIFICATIONS PROCEDURE SET_NOTIF_ATTRIBUTES (p_itemtype in varchar 2, p_itemkey in varchar 2, p_actid in number, p_funcmode in varchar 2, p_resultout varchar 2) IS BEGIN wf_engine. Set. Item. Attr. Text(p_itemtype, p_itemkey, 'EMP_TABLE', PLSQL: ZZZZ_DEMO_WF_NOTIFICATIONS. EMP_TABLE/‘ || p_itemtype || '|‘ || p_itemkey); END SET_NOTIF_ATTRIBUTES; 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 29

PL/SQL Code – Build Document (1) -- Portion of the Package ZZZZ_DEMO_WF_NOTIFICATIONS -- Procedure Call and Misc. Setup PROCEDURE EMP_TABLE (p_document_id in p_display_type in p_document in out p_document_type in out IS v_itemtype varchar 2(200); v_itemkey varchar 2(200); NL VARCHAR 2(1) : = fnd_global. newline; varchar 2, varchar 2) BEGIN v_itemtype : = substr(p_document_id, 1, instr(p_document_id, '|') - 1); v_itemkey : = substr(p_document_id, instr(p_document_id, '|') + 1, length(p_document_id) - 2); 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 30

PL/SQL Code – Build Document (2) -- Portion of the Package ZZZZ_DEMO_WF_NOTIFICATIONS -- Procedure Create HTML Table Header if (p_display_type = 'text/html') then p_document : = NL || '<P><B>'; p_document : = p_document || 'Employee Table'; p_document : = p_document || '</B>'; p_document : = p_document || '<TABLE border=1 cellpadding=2 cellspacing=1>'; p_document : = p_document || '<TR>' || NL; p_document : = p_document || '<TH>' || 'EMPNO' || '</TH>'; p_document : = p_document || '<TH>' || 'ENAME' || '</TH>'; p_document : = p_document || '<TH>' || 'JOB' || '</TH>'; p_document : = p_document || '</TR>' || NL; 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 31

PL/SQL Code – Build Document (3) -- Portion of the Package ZZZZ_DEMO_WF_NOTIFICATIONS -- Procedure Create HTML Table Rows, Handle Text Document for arec in (select EMPNO, ENAME, JOB from scott. emp order by 2) loop p_document : = p_document || '<TR>' || '<TD nowrap>' || nvl(to_char(arec. EMPNO), '&nbsp') || '</TD>'; p_document : = p_document || '<TD nowrap>' || nvl(arec. ENAME, '&nbsp') || '</TD>'; p_document : = p_document || '<TD nowrap>' || nvl(arec. JOB, '&nbsp') || '</TD> || '</TR>' || NL; end loop; p_document : = p_document || '</TABLE>'; else p_document : = 'Text Document Not Implemented'; end if; END EMP_TABLE; 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 32

Resulting Email Message • This is the same email with the message body showing the contents of the table scott. emp. 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 33

Warnings about Message Modification • Workflow Messages are not versioned • Notifications are versioned • Adding new Attributes to a Message will affect all Workflows currently running immediately. • If those Attributes are not derived you will get an error. • You should make a copy of a Workflow Message if you will be adding Attributes to it. 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 34

Workflow Versioning • Workflow 1 starts with Version 1 • Workflow 2 starts with Version 1 • If a message change is made in Version 2 it will affect both Workflows 1 & 2 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 35

Workflow Notification Testing • Modify the Workflow Mailer Configuration using OAM • Enter an email address for the Test Address • This should be a common mail account or box that required users can view. User can then view this “test” email box to find notifications from the test instance. • Don’t create an email distribution list and assign the required users to it. This causes confusion since emails show up in your email box and you have to read them carefully to know if they are from PROD or not. 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 36

Additional Reference Material Here are other papers I have written on Oracle Workflow. They are available for download from my web site: http: //www. jrpjr. com – – Customizing Workflow: A technical perspective Oracle Alert and Workflow Comparison Oracle Workflow Tips and Tricks Requisition Approval Workflow Customizations 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 37

• My contact information: John Peters john. peters@jrpjr. com http: //www. jrpjr. com • Additional reference papers can be found at: http: //www. norcaloaug. org 07/19/04 Nor. Cal OAUG Training Day, Paper 2. 3 John Peters, JRPJR, Inc. 38