Oracle Workflows

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

Add comment

Name (required)

E-mail (required, but will not display)

Website

Notify me of follow-up comments

Refresh

Send

Cancel