How to Send Notification in Worklist of User through PL-SQL

Posted on by

Q: How to send HTML Based Notification by a mere PL/SQL Call?
Q: Oracle EBS/APPS how to send Email and Workflow notification?

You can use any existing workflow and send notification by using wf_notification.send

Just you need to set the attributes of workflow message that you want to send.

Below is the example:

1) Start Oracle Workflow Builder and open “Service Messages” Workflow.

log in to workflow builder , load the “Service Messages” , right click on item type and get the internal name, which is ‘CS_MSGS’

2) Now there are 4 messages in this item type and you want to use Expanded FYI Message. Right click and select properties to get internal name. EXPANDED_FYI_MSG.

3) Now you just need to set the attributes of the message. But you don’t know the message attributes, so right click on message and select body tab.

4) Execute Following PLSQL code to test notification.
DECLARE

v_notification_id        NUMBER                 := NULL;
v_from_user_name    VARCHAR2 (500)    := ‘XX_FROM_USER’;
l_to_user_name         VARCHAR2 (500)    := ‘XX_TO_USER’;
v_subject_line           VARCHAR2 (500)    := ‘Subject Line’;
v_message_line         VARCHAR2 (500)    := ‘Message Line’;

BEGIN

v_notification_id :=
wf_notification.send (UPPER (l_to_user_name),
‘CS_MSGS’,
‘EXPANDED_FYI_MSG’
);

wf_notification.setattrtext (v_notification_id,
‘#FROM_ROLE’,
v_from_user_name
);

wf_notification.setattrtext (v_notification_id,
‘OBJECT_TYPE’,
v_subject_line
);

wf_notification.setattrtext (v_notification_id, ‘SENDER’, v_from_user_name);

wf_notification.setattrtext (v_notification_id,
‘MESSAGE_TEXT’,
v_message_line
);

wf_notification.denormalize_notification (v_notification_id);

COMMIT;

END;