How to Create a Small Workflow just to send EMAIL notifications and Run it from backend

We can use UTL_SMTP API to send Emails using PL/SQL block of code but emails sent through UTL_SMTP has no track like whether the email is sent or not, or for whom the email is sent, whether the email is viewed by the recipient or not. We can have solution to all these problems if we send an email notification through workflow, we can track an email notification through WF_NOTIFICATIONS table and more over the notification copy goes to the recipient work list (home page) as well.

Here is the process to create a simple workflow notification:

Create 4 Item Attributes and a process as shown below

Create a Message with display name as “XX Notif Message” and Internal Name as “XX_MSG”. Add content to the message body as shown in the below screen shot:

WF_NOTIFICATION is a seeded oracle function which formats the attributes values mentioned in the notification body, internally it uses HTML to bring formatted table like structure. To know more about WF_NOTIFICATION, read this article.

Drag all the attributes which you would like to show on the message under the message tree as shown in the screen shot one.

Create a Notification with display name as “XX Email Notification” and Internal Name as “XX_NOTIF”. Add message to the notification as shown in the below screen shot:

Create a small workflow process with 3 nodes Start node, Notification node and End node like shown in the below screen shot.

Double click on notification node and navigate to ‘Node’ tab and add role to the notification as shown in the below screen shot

Save the notification to the database from workflow builder or through WF_LOAD command.

Note: Uploading the workflow from workflow builder would take lot of time.

Execute below block of code to run the workflow from backend

DECLARE
   l_itemtype            varchar2 (8)                := 'XX_NOTIF';
   l_process             varchar2 (80)               := 'XX_NOTIF_PROCESS';
   l_itemkey             VARCHAR2 (20)               := '1234-1'; --this should be unique
   l_user_key            VARCHAR2 (20)               := '123';
--
--
BEGIN

--
--Creating Workflow Process
--
   wf_engine.createprocess (itemtype        => l_itemtype,
                            itemkey         => l_itemkey,
                            process         => l_process,
                            user_key        => l_user_key,
                            owner_role      => 'SYSADMIN'
                           );
--
--Setting Item attribute for 'Adhoc role'
--
   wf_engine.setitemattrtext (itemtype      => l_itemtype,
                              itemkey       => l_itemkey,
                              aname         => 'XX_ROLE',
                              avalue        => 'SHTHALLAM' --Recipient user name
                             );                           
--
--Setting Attribute Value
--
   wf_engine.setitemattrtext (itemtype      => l_itemtype,
                              itemkey       => l_itemkey,
                              aname         => 'XX_ITEM',
                              avalue        => 'IT-090001'
                             );
--
--Setting Attribute Value
--
   wf_engine.setitemattrtext (itemtype      => l_itemtype,
                              itemkey       => l_itemkey,
                              aname         => 'XX_LOT',
                              avalue        => 'IT-090001-LOT'
                             );                             
--
--Setting Attribute Value
--
   wf_engine.setitemattrtext (itemtype      => l_itemtype,
                              itemkey       => l_itemkey,
                              aname         => 'XX_ORG',
                              avalue        => 'M1'
                             );        
--
--Start Process
--
   wf_engine.startprocess (itemtype   => l_itemtype,
                           itemkey    => l_itemkey);
--
--
   commit;
   DBMS_OUTPUT.put_line ('Done!');
--
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
end;
/

Below is the screen shot of the email notification generated.

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