Transcript Customizing Oracle Workflow A Technical Perspective

Oracle Workflow Notifications
John Peters
JRPJR, Inc.
[email protected]
07/19/04 NorCal 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 NorCal 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 NorCal 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 NorCal 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 NorCal OAUG Training
Day, Paper 2.3
John Peters, JRPJR, Inc.
5
Notification Components
Notifications are comprised of:
- A Notification Activity
- A Message
- A Message Template
- Optional Message Item Attribute(s)
07/19/04 NorCal 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 NorCal 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 NorCal 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 NorCal 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 NorCal 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 NorCal 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 NorCal 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. B10283–01
07/19/04 NorCal OAUG Training
Day, Paper 2.3
John Peters, JRPJR, Inc.
13
My Customized Template
07/19/04 NorCal OAUG Training
Day, Paper 2.3
John Peters, JRPJR, Inc.
14
How to Reference Customized Templates
• Workflow Mailer Configuration using OAM
07/19/04 NorCal 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 NorCal 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 NorCal 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 NorCal 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 NorCal 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. B10284–01
07/19/04 NorCal 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 NorCal 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:
VARCHAR2 – 32KB Size Limitation
CLOB – 4GB (possible limitations due to workflow engine limitation)
BLOB – 4GB (possible limitations due to workflow engine limitation)
07/19/04 NorCal 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 NorCal 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 NorCal 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 NorCal 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 NorCal 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 NorCal 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 NorCal 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 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',
PLSQL:ZZZZ_DEMO_WF_NOTIFICATIONS.EMP_TABLE/‘
|| p_itemtype || '|‘ ||
p_itemkey);
END SET_NOTIF_ATTRIBUTES;
07/19/04 NorCal 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
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);
07/19/04 NorCal 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 NorCal 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 NorCal 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 NorCal 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 NorCal 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
Workflow Instance 1
Workflow Instance 2
Workflow Loaded Ver 1
Workflow Loaded Ver 2
Time
07/19/04 NorCal 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 NorCal 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 NorCal OAUG Training
Day, Paper 2.3
John Peters, JRPJR, Inc.
37
• My contact information:
John Peters
[email protected]
http://www.jrpjr.com
• Additional reference papers can be found at:
http://www.norcaloaug.org
07/19/04 NorCal OAUG Training
Day, Paper 2.3
John Peters, JRPJR, Inc.
38