Oracle Workflow Notifications

1
Oracle Workflow Notifications

• John PetersJRPJR, Inc.
• john.peters_at_jrpjr.com

2

• 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

3
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

4
Versions Being Shown

• All of my examples are Workflow 2.6.3
• This is the version shipped with 11.5.9

5
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

6
Notification Components

• Notifications are comprised of
• A Notification Activity
• A Message
• A Message Template
• Optional Message Item Attribute(s)

7
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 dont you will get a
  workflow runtime error about a NULL Performer.

8
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.

9
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)

10
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.

11
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

12
Open FYI Mail Template

• Here is where that extra text comes from.

13
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 inOracle Workflow Administrators
  Guide, RELEASE 2.6.3, Part No. B1028301

14
My Customized Template
15
How to Reference Customized Templates

• Workflow Mailer Configuration using OAM

16
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

17
How to Reference Customized Templates

• The value must be of the format
  ltITEM_TYPEgtltMESSAGE NAMEgt

18
Here is the result

• Notice other possibly objectionable message
  components if the message is going external

19
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

20
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 AttributesOracle Workflow
  Developers Guide, Release 2.6.3Part No.
  B1028401

21
Using WFM_FROM

• This message attribute will change the From Email
  Address in the Notification.

22
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 areVARCHAR2 32KB Size
  LimitationCLOB 4GB (possible limitations due
  to workflow engine limitation)BLOB 4GB
  (possible limitations due to workflow engine
  limitation)

23
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

24
Create a Function

• This Workflow function will set the Document
  Attribute values when the Workflow is run.
• Right mouse on Function
• Select New Function

25
Create Workflow Process

• I am going to build a new demo Workflow Process
• Right mouse on Process
• Select New Process

26
Build the Workflow Process

• Drag Functions in to Process
• Set Document Attribute, then Send Notification

27
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.

28
Procedure to Set the Document Attribute

• Document Attributes Values plsqlltproceduregt/ltdocu
  ment_identifiergt
• procedure PL/SQL Procedure
• document_identifier usually a concatenation
  ofITEM_TYPEITEM_KEY
• This is the only way to pass parameters specific
  to the Workflow Instance.

29
PL/SQL Code Set Attribute

• -- Portion of the Package ZZZZ_DEMO_WF_NOTIFICATIO
  NS
• PROCEDURE SET_NOTIF_ATTRIBUTES (p_itemtype
  in varchar2,
• 
  p_itemkey in
  varchar2,
• 
  p_actid in number,
• 
  p_funcmode in
  varchar2,
• 
  p_resultout out
  varchar2)
• IS
• BEGIN
• wf_engine.SetItemAttrText(p_itemtype,
• p_itemkey,
• 'EMP_TABLE',
• PLSQLZZZZ_DEMO_WF_NOTIFICATIONS.EMP_TABLE
  /
• p_itemtype '
• p_itemkey)
• END SET_NOTIF_ATTRIBUTES

30
PL/SQL Code Build Document (1)

• -- Portion of the Package ZZZZ_DEMO_WF_NOTIFICATIO
  NS
• -- Procedure Call and Misc. Setup
• PROCEDURE EMP_TABLE (p_document_id
  in varchar2,
• 
  p_display_type in varchar2,
  
• 
  p_document in out varchar2,
• 
  p_document_type in out varchar2)
• IS
• v_itemtype varchar2(200)
• v_itemkey varchar2(200)
• NL VARCHAR2(1) fnd_global.newline
• 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)

31
PL/SQL Code Build Document (2)

• -- Portion of the Package ZZZZ_DEMO_WF_NOTIFICATIO
  NS
• -- Procedure Create HTML Table Header
• if (p_display_type 'text/html')
• then
• p_document NL 'ltPgtltBgt'
• p_document p_document 'Employee Table'
• p_document p_document 'lt/Bgt'
• p_document p_document 'ltTABLE border1
  cellpadding2 cellspacing1gt'
• p_document p_document 'ltTRgt' NL
• p_document p_document 'ltTHgt' 'EMPNO'
  'lt/THgt'
• p_document p_document 'ltTHgt' 'ENAME'
  'lt/THgt'
• p_document p_document 'ltTHgt' 'JOB'
  'lt/THgt'
• p_document p_document 'lt/TRgt' NL

32
PL/SQL Code Build Document (3)

• -- Portion of the Package ZZZZ_DEMO_WF_NOTIFICATIO
  NS
• -- 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 'ltTRgt' 'ltTD
  nowrapgt' nvl(to_char(arec.EMPNO),
  'nbsp') 'lt/TDgt'
• p_document p_document 'ltTD nowrapgt'
  nvl(arec.ENAME, 'nbsp') 'lt/TDgt'
• p_document p_document 'ltTD nowrapgt'
  nvl(arec.JOB, 'nbsp') 'lt/TDgt
• 'lt/TRgt' NL
• end loop
• p_document p_document 'lt/TABLEgt'
• else
• p_document 'Text Document Not
  Implemented'
• end if

33
Resulting Email Message

• This is the same email with the message body
  showing the contents of the table scott.emp.

34
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.

35
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

36
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.
• Dont 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.

37
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

38

• My contact information
• John Petersjohn.peters_at_jrpjr.com
  http//www.jrpjr.com
• Additional reference papers can be found
  athttp//www.norcaloaug.org