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