OracleAppsDNA CJ
PL/SQL document: Workflow Notification with
Dynamic HTML Body
by Shailender Thallam / July 29, 2014 / 2 Comments
sertte | Access |
temtpe FRERTWLNowieaan
ttl ane FRBOOP
igo Mane 0dr Connt
tscee8f <<
Tre [Peames =]
Frane Taget [Ful Wirdon =
Detauit
Typo [Baneant =
Value
Corea! Apel Help
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.
>
START YeCNOTIE END
Create an attribute ‘XX BODY’ of ‘Document type as shown in the
below screen shot:
“Digital x/) @la| vrs) 212) a
BD
(E-E), FSTD [Stance
(2-Eh XHTM_N XHTML Notficaion)
HE Atrbunes
{Ed *_ B00" (Body conten)
Fd Processes
“i 9OLHTM_P HTML Notification Process)
BE Notes
‘eo XLNOTIF (Motion er HTML Boa)
Furetione
Ee
5: Mereasee
7 90 MSIE (Meseage of HTML Bod)
>. BCDY (Bec Convert
BB Locka Tynes
FED Director Service rou [rie
aa
Deszrpiin
Type [Document =]
Frame Tarat [Fal Window 7
Default
ee Bes eee eee
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:
visrane [ESI] ales | Acces | Reut | c
‘subjeot [Messeae of HTML Body
Teat Body | HTML Bod |
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 Atty | Deas | Roles | Aoess|
eral are [SST
Dla Name [Neifoaton fer TWLBagy
Decigicn NeifeafonferWTWLBody
roomie] gy em
ee
funciontine [FUSES
eaxige [tere SSCS] fr
Edt
Message [Meccage of HTML Body
Expand Rates
ae ee
Heteton | Dstai | oles | Acoeed Node |Pide Atte |
Label [LNOTIF
StatiEnd [Namal ~
Comment
Timeout
Type [Ne Tneout =
<< Dacia ippsDNAcor
Type [Detouk SSS]
Peer -
Tune [Constant =]
ee aj
cet |_| He
Package Specification
1 CREATE OR REPLACE
2 PACKAGE xx_emp_wf_doc_pkg
BAS
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 );
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 VARCHAR? )
915
16 1_body VARCHAR2(32767) 5
11 BEGIN
2+
13 document_type := ‘text/html'
ae
15 body := *
16
17
18
19 | Employee Name |
20 Employee Number |
21
22
23
245
25 ‘FOR i IN
26 (SELECT ename,empno FROM emp
27)
28 LOOP
29 ‘BEGIN
30 body := 1_body || ‘
B1 | '||"i.ename |] ‘ |
32 ‘ || i.empno || ‘ |
33
'5
34 END;
35 END LOOP;
36 document := 1_body;
37 --
38 --Setting document type which is nothing but MIME type
39 --
49 document_type
41 EXCEPTION
42 WHEN OTHERS THEN
43 document := "Error: ‘|| sqlerrm || '"5
44 END xx_create_wf_doc;
45 END xx_emp_wf_doc_pkg;
46 /
47 SHOW errors;
48 EXIT;
“text/html' 5
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 Litentype varchar2 (8) °XXHTM
3 process varchar2 (80) XX HTH
4 Litenkey VARCHAR2 (2) "1234-7
5 Luser_key VARCHAR2 (20) "123";
6 — document_id cLoB;
7
a--
9 BEGIN
10
a --
12 --Creating Workflow Process
1B --
14 wf_engine.createprocess (itemtype Litemtype,
15 itemkey aTitemkey,,
16 process Iprocess,
7 user_key Iuser_key,
18 owner_role “SYSADMIN
19 ds
20 --
21 --Calling PLSQL document for generating HTML code
22 --
23 1_document_id := ‘PLSQL:XX_EMP_WF_DOC_PKG.XX_CREATE_WF_DOC/* |
24 ==
25 --Setting Value for document type attribute
26 --
27 wf_engine.setitemattrtext (itemtype 1itemtype,
28 ‘itemkey Litemkey,
29 aname "Xx_BODY",
30 avalue 1_document_id
31 8
32 --
33 --Start Process
34 --
35 wf_engine.startprocess (itemtype => 1_itemtype,
36 itenkey => 1_itemkey);
37
38
39° commit;
48 DBMS_OUTPUT.put_line (‘Done!');
41 --
42 EXCEPTION
43 WHEN OTHERS
440 THEN
45 DBMS_OUTPUT.put_line (‘Error: * || SQLERRM);
46 END;
47 /
—EEEEE=D >
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
Message of HTML Body
Personalize "Notification Details Function”
Personalize "Ulotificafion Details Attributes
Personolize Table Lavout: (leftTable) Personalz
To SHTHALLAM
Sent 10-Jul-2024 14:13:12
1D 99602816
Employee Name Employee Number
BPIR 8000
SMITH 7369
ALLEN 7499
WARD 7524
JONES 7566
MARTIN 7654
BLAKE 7698 mss ”
CLARK 7782
SCOTT 7788
KING 7839
TURNER 7844
ADAMS 7876
JAMES 7900
FORD 7902
MILLER 7934
Return to Worklise
You can download the workflow file from this URL:
http://mmw.oracleappsdna.com/uploads/XXHTM N.wft
Seeded Procedure Signature for Procedure which generated
PI/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 (document_id IN VARCHAR2,
display_type IN VARCHAR2,
document TIN 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 > 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/* || 1
——— >
Please leave comments if you need any additional information.
For Further Reading
1. How to Create a Small Workflow just to send EMAIL notifications
and Run it from backend
2. UTL SMTP AP! to send HTML Emails from PL/SQL Iseful Inform:
n about Workflow Background Process
m
Concurrent Prog 7 Comments @ Loginy
G
Join the discussion,
Los INwiTH ‘on sicn uP win orsaus @)
Name
Share Best Newest Oldest
Chanakya Kolapalli -F
8 years ago
This is really helpful Mr.Shailender, Thank you for all you efforts in
helping others
° © Reply * Share>
Manali =F
7 years ago
Please help me with clob documents because in case of my
requirement document size exceeds the 32kb.
Can you please provide the same steps for CLOB documents.
0 2 Reply * Share>
gavini gavaskar -F
5 years ago
Hi
how could we pass parameters to the document procedure to get
specified records.
Thanks,
Gavaskar,
o 0 Reply * Share»
A, Stet Aovsnisester —r
4 years ago
For parameter of cursor ,i resolved as below.
CURSOR CUR_QUALITF
Is
select ACTIONACTION_DATE, APPROVER_USER_NAME,
WF_NOTES from XXUMX_ACTION_HISTORY WHEKE 1 EM_KEY = documenta;
~your html tag down.
and in calling workflow
V_DOCUMENT_ID
'PLSQL:XX_YOUR_PKG.YR_PROC_NAME/' || ITEMKEY;
document_id is using itemkey as parameter. tested
working.
o 0 Reply * Share»
GG Gavaskar Gavini > atzal =f
years ago
Thanks Afzal for your reply.
my requirement is ,
eg:
cursor c1(p_emp number) is
select * from emp where emp_num=p_emp;
i want to get the cursor data dynamically by
sending the emp_no what ever i want.
In the above document creation procedure the
cursor is like this
‘SELECT ename,empno FROM emp.
Thanks,
Gavaskar.
o 0 Reply * Share>
Z Zulqarnain =f
2years ago
Hi dear , | m facing Error: ORA-20002: 3114: Activity
'XXHTM_N/XX_HTM_P’ is not a process. , error message when | run
the above code, kindly guide me what's the issue
° 0 Reply * Share>
R Rahul Bachchas =f
ayear ago
Search on this Site Search ...
Popular Questions Asked
What is the difference between XML publisher and BI Publisher? asked
by Questions Master
What is the difference between CASE and DECODE? asked by Questions
Master
What are the different types of Sales Orders in Order Management?
asked by Questions Master
How to display the names of the employees who earn highest salary in
their respective departments? asked by Questions Master
Can you have multiple layout templates for a singe data template?
asked by Questions Master
Categories
Select Category .
Recent Posts
Oracle ERP Cloud Instance Clone/Refresh Guidelines
ESS Request History with Time took to Run
Payload to Update AR Invoice DFF in Oracle ERP Cloud
Emails generated by Oracle ERP are delivered to customer/Supplier with
from email address as "no-reply@oracle.com” instead of no-
reply@client-DomainName.com
Oracle ERP Cloud SQL Query to find Bank Accounts Associated with a
Supplier OracleAppsDNA.com 2024 i llostson this bog a tsedon the views the Auth
Reproducing ates without permissions prohibited
