How to send Notification using Oracle Workflow


Create a sample workflow as follows.
<f1>



TEST_NF is a notification Item. Now create a message item TEST_MSG as follows. Write something in the text body.
<f2>


Now open the notification item TEST_NF from the process window. Set the message property to TEST_MSG.
<f3>


Open the Node tab. Set the performer value to the role name where you want to send the message. Please read the last section of this article to know more about the role and username. For the time lets assume if you want to send the message to MARY.G.
Now run this query to know which role names are associated with user MARY.G.

select role_name from wf_user_roles where user_name = 'MARY.G'


Give any role_name to performer>value property.
<f4>




Save the workflow. To initiate the workflow write the following plsql package.


create or replace package body xx_test_nf is

--This procedure is used to call the workflow
PROCEDURE start_wf(x_errbuf OUT VARCHAR2
,x_retcode OUT VARCHAR2) is
l_itemtype VARCHAR2(30) := 'TEST_WF1';
l_itemkey VARCHAR2(300);
begin
l_itemkey := 'TEST_WF1' || to_char(SYSDATE, 'dd/mm/yyhh:mm:ss');
apps.wf_engine.createprocess(l_itemtype, l_itemkey, 'TEST_PR1');
apps.wf_engine.startprocess(l_itemtype, l_itemkey);
commit;
end;

end xx_test_nf;



That's it. Check the user MARY.G frontend inbox for the notification.
<f5>





Additional Details

To create new role use the following procedure. Please modify it as per your requirement.

PROCEDURE XX_RCT_CREATE_ROLE(X_ERRBUF OUT VARCHAR2,
X_RETCODE OUT VARCHAR2,
P_LEDGER_NAME VARCHAR2,
P_ACCOUNT_NUMBER VARCHAR2,
P_GROUP_NUMBER VARCHAR2) IS
l_role VARCHAR2(300);
l_ledger_id NUMBER;
l_reconciler_role VARCHAR2(500);
l_approver_role VARCHAR2(500);
l_qc_role VARCHAR2(500);
l_ctr_first INTEGER := 0;
l_ctr_sec INTEGER := 0;
l_ctr_thrd INTEGER := 0;
BEGIN
x_errbuf := 'Success';
x_retcode := '0';
SELECT COUNT(*)
INTO l_ctr_first
FROM apps.wf_local_roles
WHERE NAME = l_reconciler_role;
l_role := P_GROUP_NUMBER || '_R';
IF l_ctr_first = 0 THEN
apps.wf_directory.CreateAdHocRole(role_name => l_role,
role_display_name => l_role,
role_description => l_role,
notification_preference => 'MAILHTML',
email_address => NULL,
status => 'ACTIVE',
expiration_date => NULL);
ELSE
END IF;
SELECT COUNT(*)
INTO l_ctr_sec
FROM apps.wf_local_roles
WHERE NAME = l_approver_role;
l_role := P_GROUP_NUMBER || '_L1';
IF l_ctr_sec = 0 THEN
apps.wf_directory.CreateAdHocRole(role_name => l_role,
role_display_name => l_role,
role_description => l_role,
notification_preference => 'MAILHTML',
email_address => NULL,
status => 'ACTIVE',
expiration_date => NULL);
ELSE

END IF;
SELECT COUNT(*)
INTO l_ctr_thrd
FROM apps.wf_local_roles
WHERE NAME = l_qc_role;
l_role := P_GROUP_NUMBER || '_L2';
IF l_ctr_thrd = 0 THEN
apps.wf_directory.CreateAdHocRole(role_name => l_role,
role_display_name => l_role,
role_description => l_role,
notification_preference => 'MAILHTML',
email_address => NULL,
status => 'ACTIVE',
expiration_date => NULL);
ELSE

END IF;
COMMIT;

END XX_RCT_CREATE_ROLE;


To add user to roles use the following procedure. Modify it as per your requirement.

PROCEDURE XX_RCT_ASSIGN_USERTOROLE(X_ERRBUF OUT VARCHAR2,
X_RETCODE OUT VARCHAR2,
P_USER_NAME VARCHAR2,
P_ROLE VARCHAR2) IS
l_user_name VARCHAR2(500) := P_USER_NAME;
l_role_name VARCHAR2(500) := P_ROLE;
l_ctr INTEGER := 0;
l_ctr_1 INTEGER := 0;
BEGIN
x_errbuf := 'Success';
x_retcode := '0';
SELECT COUNT(*)
INTO l_ctr_1
FROM apps.wf_local_roles
WHERE NAME = l_role_name;
IF l_ctr_1 <> 0 THEN
SELECT COUNT(*)
INTO l_ctr
FROM apps.fnd_user
WHERE user_name = l_user_name;
IF l_ctr <> 0 THEN
apps.wf_directory.AddUsersToAdHocRole(role_name => l_role_name,
role_users => l_user_name);
ELSE

dbms_output.put_line('FAIL INSERTION 1');
END IF;
ELSE
IF l_ctr_1 = 0 THEN
g_errmsg := 'Invalid Role Name';

dbms_output.put_line('FAIL INSERTION 2');
END IF;
END IF;

END XX_RCT_ASSIGN_USERTOROLE;




You can download the whole workflow, sql codes and this article from the following location.


Oracle Workflow Notification tutorial.zip



No comments :