<-----Google Analytics Code Start----> <-----Google Analytics Code Close---->
Skip to content



Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
Home arrow Home arrow Open Source in Oracle Apps - Generic Workflow Notification API
This website has now moved to http://apps2fusion.com

Open Source in Oracle Apps - Generic Workflow Notification API | Print |  E-mail
Written by Anil Passi   
Wednesday, 30 January 2008
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

Comments (5)add
eBusiness analyste
written by Abdelmalek , January 30, 2008
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

written by Anill Passi , January 30, 2008
Hi Abdelmalek

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

Anil Passi
Complex Query
written by Sameer Madhware , January 31, 2008
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?

Hats off to Anil passi
written by Balaji R , February 01, 2008
Hats off to u. words not there to describe ur utility.
written by Anil Passi , February 29, 2008
Hey Sameer

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

Anil Passi

Balaji : Thanks for your feedback, much appreciated
You must be logged in to a comment. Please register if you do not have an account yet.