Fusion Blog

EBS Blog


Contact Us

Oracle Workflows - All Articles
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
Some six years ago, I joined a project for a Swiss client. On my first day in that project, I realised that there was a need to send approx 50 different types of notifications. The layout for those notifications was HTML based data content. Considering the scale of requirement, it wasn’t feasible to develop 50different workflows or 50 different Alerts for this purpose. Hence I then designed a PL/SQL utility, that can churn out notifications with HTML contents. After that Swiss project, I have used the same API at 3 other clients.
I am hereby donating this PL/SQL API to be downloaded and used for free by Oracle Apps Community.

To use this utility
Download this utility
Install this utility
Call PL/SQL Utility API
Ensure Workflow Background process is scheduled to run

Salient Features of this utility are :-
1.You can send a plain text or html text or even results of a SQL Statement as the notification contents.
2.To send result of SQL Statement in notification, you can pass a table name, column name and where clause as parameters to this API.
Based upon this information, API will construct SQL, and build HTML Table with data fetched from SQL Statement.
3.No size restriction of 32K on notification contents, as this API is designed to use PL/SQL Clob.
4.For recipient parameter, you can either pass FND_USER.USER_NAME or an Email Address as parameter.
If email address is passed, then an ad-hoc role will be created on the fly [if need be].
To see a working example of this API, click on this link
Link for Sample Demo and Syntax for Passing SQL Statement
In the above link, you can see the syntax for generating HTML Notification by passing name of the table, column & where clause as parameters.
Also notice that you can pass two or three or four or as many SQL Statements to this API.
Another example, of sending HTML Contents in Notification, see this link

An outdated powerpoint presentation of this API can be seen from this link [ object names are old/been changed ]
An outdated Technical Design Doc for this API can be seen from this link
This presentation contains overview for you to quickly understand this API. The naming convention used in this presentation are out of date though.

Isn’t it better to develop a Workflow for each such notification requirement? By using workflow, you get audit trail in WF_NOTIFICATIONS table?
This API leverages Oracle Workflow behind the scenes. When you install this open source tool, it will not only install PL/SQL API’s in your database, but it will also load the generic Workflow using WFLOAD. Hence behind the scenes this API uses a workflow itself.

After calling this API, I wish to see the flow of the Generic Workflow in Status Monitor screen. How do I identify the Workflow Item Key?
The PL/SQL API will return a notification reference number, which forms the WF Item Key.

What will be the item type of this generic workflow?
The item type of the generic workflow will be XXWFAPI. Having said so, you do not need to know these internal details.

How to install this utility?
1. Download the tar file xx_wf_api.tar
2. FTP this tar file in binary mode to any directory on Linux/Unix of the DB Tier
3. Lets assume that directory is /tmp
4. cd /tmp
5. tar –xvf xx_wf_api.tar
6. cd xx_wf_api
7. Open XX_WFAPI01.sql and in first few lines, for synonyms, replace bes.<tablename> to YourCustomSchemaName. <tablename>
8. load_wf_api.sh appspassword customSchemaUserName customSchemaPwd
Sample output when running the install script can be seen from this link
For example load_wf_api.sh apps xxcust xxcust
Custom Tables for this utility will be created into the schema that is passed in as 2nd parameter to load_wf_api.sh
In the above example, password of apps is apps, and login for custom schema is xxcust/xxcust

Is this utility free
Yes, however if you have successfully installed and run this utility, then share your hurdles of installation & usage with others, so that everyone benefits from your experiences.

Download this utility from link below

Anil Passi


0 #1 Abdelmalek 2008-01-30 16:48
Hi Anil,

It is always a pleasure to receive your e-mails and read your articles.
For this article, I have not yet taken the care to read the details, but I want to thank you for all the tips and ideas that you have put at our disposal.
Again, thank you and good continuation

A bdelmalek
0 #2 Anill Passi 2008-01-30 17:07
Hi Abdelmalek

To begin with you can see the powerpoint presentation, it will give you good overview of this pl/sql API

An il Passi
0 #3 Sameer Madhware 2008-01-31 10:47
Hi Anil,

I went through this document and found it very helpful to accomplish what our client has required.But somehow I couldnt find the strategy to display the result set extracted by a query having multiple joins and using multiple tables.

Could you please advise?

Thanks ,
0 #4 Balaji R 2008-02-01 04:25
Hats off to u. words not there to describe ur utility.
0 #5 Anil Passi 2008-02-29 21:35
Hey Sameer

The from clause can have multiple tables, and also the where clause parameter can be passed joins to multiple tables too.

A nil Passi

Balaji : Thanks for your feedback, much appreciated
0 #6 Srinivas Lokam 2008-08-09 01:58
Hi Anil,
Cool Tool !!!
In addition to this Dynamic HTML table generation, Is it possible to add another Input TEXT FIELD for each record(where users can enter some data and reply back) and I want to access these entered Input TEXTFIELD's in workflow notifcation reply process??

Any Help would be appreciated.

T hanks,
srinivas .L
0 #7 Anil Passi 2008-08-09 03:08
Hi Srini

This is a open source tool, so feel free to enhance this further.

In case you make any enhancements, email me the final enhanced toolset so that I can upload for others to use.

An il
0 #8 Balkrishna1 2009-03-31 03:29
Hi Anil

This is unbelievable.Th ank you ...... :).I have one question .Can we add multiple email address or ROLE .

Thanks and Regards
Balkrsi hna
0 #9 sudhakara mullagura 2009-04-14 02:33
Hi Anipessi,

than ks a lot, its a pleasure a see all your tips and ideas.

I need one help. can you please tell about 'XML Geteway' on this topic i have to give a prasention,
and also can you provide any PPTs regarding the same..

Thanks you,
Sudhakar Mullagura
0 #10 Khushi 2009-04-29 08:27
Hello guys,

If somebody has installed the above utility then could you please forward the script files and the workflow to my maill.

I am facing problem while trying to install it.

0 #11 Abdulrahman Mohammed 2010-12-26 07:53
Dear Anil and Team,

your utitlity is awesome, please carry on with your excellent work.

May Almighty increase your tribe.

warm regards

Abdulr ahman

Add comment

Security code

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Jun 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
   1  2  3  4  5  6
  7  8  910111213

Enquire For Training

Related Items

Fusion Training Packages

Get Email Updates

Powered by Google FeedBurner