Add a Attribute item of type Document.

Then You have go to Message and Add a Attribute Type of Document and link it to main attribute as
shown in picture.
After that you have to go to Message Properties -> Body

And in HTML Body you have to add your attribute like shown, so that workflow engine should interpret
that html.
PL/SQL document: Workflow Notification with
Dynamic HTML Body
By Shailender Thallam | July 29, 2014 | 7,921 views | Category: Workflows
2 Comments

To generate a HTML notification we can just write HTML code on the message notification but
unfortunately message body accepts only content 32kb of content. If message body exceeds 32kb
size then we can go for “PL/SQL” documents, “PL/SQL CLOB” documents, and “PL/SQL BLOB”
documents. We can also use this PL/SQL documents when we need to generate html code
dynamically.

More about
“PL/SQL” Documents – represents data as a character string of 32KB
“PL/SQL CLOB” Documents – represents data as a character large object (CLOB) of 4GB
“PL/SQL BLOB” Documents – represents data as a binary large object (BLOB) of 4GB
In this article lets see how to create a workflow notification using PL/SQL document to print
Employee name and number from EMP table.
Create a small workflow process with 3 nodes Start node, Notification node and End node like
shown in the below screen shot.

Create an attribute ‘XX_BODY’ of ‘Document‘ type as shown in the below screen shot:
Create a Message with display name as “Message of HTML Body” and Internal Name as
“XX_MSG”. Add content to the message body as shown in the below screen shot:

Create a Notification with display name as “Notification for HTML Body” and Internal Name as
“XX_NOTIF”. Add message, role to the newly created notification as shown in the below screen shot
Package Specification

1 CREATE OR REPLACE

2 PACKAGE xx_emp_wf_doc_pkg
 3 AS

4 PROCEDURE xx_create_wf_doc(

5 document_id IN VARCHAR2,
6 display_type IN VARCHAR2,
7 document IN OUT nocopy VARCHAR2,
8
document_type IN OUT nocopy VARCHAR2 );
9
END xx_emp_wf_doc_pkg;
10
/
11
SHOW errors;
12
EXIT;

package Body

1
CREATE OR REPLACE
2
PACKAGE body xx_emp_wf_doc_pkg
3
AS
4
PROCEDURE xx_create_wf_doc(
5
document_id IN VARCHAR2,
6
display_type IN VARCHAR2,
7
document IN OUT nocopy VARCHAR2,
8
document_type IN OUT nocopy VARCHAR2 )
9
IS
10
l_body VARCHAR2(32767);
11

12 BEGIN

13 --

14 document_type := 'text/html';

15 --
16 l_body := '

17 <table>

18 <thead>

19 <tr>

20 <th style="background-color:#CFE0F1;">Employee Name</th>

21
<th style="background-color:#CFE0F1;">Employee Number</th>
22
</thead>
23
</tr>
24
<tbody>
25
';
26
FOR i IN
27
(SELECT ename,empno FROM emp
28
)
29
LOOP
30
BEGIN
31
l_body := l_body || '<tr>
32

33
<td>'|| i.ename || '</td>

34 <td>' || i.empno || '</td>

35 </tr>';

36 END;

37 END LOOP;

38 document := l_body;

39 --

40 --Setting document type which is nothing but MIME type

41 --

42
document_type := 'text/html';
43
EXCEPTION
 44 WHEN OTHERS THEN

45 document := '<H4>Error: '|| sqlerrm || '</H4>';

46 END xx_create_wf_doc;

47 END xx_emp_wf_doc_pkg;

48 /

SHOW errors;

EXIT;

What does the above package do?

The above package generates a PL/SQL document which has a dynamic HTML code to display
employee details.

Script to run the workflow from backend

1
DECLARE
2
l_itemtype varchar2 (8) := 'XXHTM_N';
3
l_process varchar2 (80) := 'XX_HTM_P';
4
l_itemkey VARCHAR2 (20) := '1234-1'; --this should be unique
5
l_user_key VARCHAR2 (20) := '123';
6
l_document_id CLOB;
7
--
8
--
9
BEGIN
10

11

12 --

13 --Creating Workflow Process

14 --

15 wf_engine.createprocess (itemtype => l_itemtype,

16 itemkey => l_itemkey,

17 process => l_process,

18 user_key => l_user_key,
19 owner_role => 'SYSADMIN'
20 );
21
--
22
--Calling PLSQL document for generating HTML code
23
--
24
l_document_id := 'PLSQL:XX_EMP_WF_DOC_PKG.XX_CREATE_WF_DOC/' || l_itemkey;
25
--
26
--Setting Value for document type attribute
27
--
28
wf_engine.setitemattrtext (itemtype => l_itemtype,
29
itemkey => l_itemkey,
30
aname => 'XX_BODY',
31

32
avalue => l_document_id

33 );

34 --

35 --Start Process

36 --

37 wf_engine.startprocess (itemtype => l_itemtype,

38 itemkey => l_itemkey);

39 --

40 --

41 commit;
42
DBMS_OUTPUT.put_line ('Done!');
43
--
44 EXCEPTION

45 WHEN OTHERS

46 THEN

47 DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);

END;

What does the above block of code do?

The above code triggers a workflow and sets the document type of attribute of workflow with PL/SQL
document which holds the HTML code assigned to workflow message.

Output
You can download the workflow file from this
URL: http://www.oracleappsdna.com/uploads/XXHTM_N.wft

Seeded Procedure Signature for Procedure which generated Pl/SQL document

The PL/SQL procedure that generates a PL/SQL document has some seeded parameters which
should NOT be altered, below is the syntax:

PROCEDURE <PROCEDURE name> (document_id IN VARCHAR2,

display_type IN VARCHAR2,

document IN OUT VARCHAR2,

document_type IN OUT VARCHAR2)

1. document_id is nothing but a string that uniquely identifies a document. Usually we can pass
Item Key to this parameter.
2. display_type represents the content type used for the notification presentation
Eg:- ‘text/html’ OR ‘text/plain’
3. document the outbound text buffer where up to 32K of document text is returned
4. document_typethe outbound text buffer where the document content type is returned. Also
referred to as the returned type. If no type is supplied, then ‘text/plain’ is assumed

Calling PL/SQL document procedure

Syntax
PL/SQL:<Procedure>/<document_identifier>

< procedure > should be replaced with the PL/SQL package and procedure name in the form of
‘package.procedure’. The phrase < document_identifier > should be replaced with the PL/SQL
argument string that you want to pass directly to the procedure. The argument string should identify
the document.

For example:

l_document_id := 'PLSQL:XX_EMP_WF_DOC_PKG.XX_CREATE_WF_DOC/' || l_itemkey;

Please leave comments if you need any additional information.

For Further Reading