How to send notification through pl/sql without creating a new workflow

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:


Here we will use seeded CS_MSGS (Service Messages)  item type nothing but the message type.

You can any of the predefined seeded item type or any custom workflow item type.

you just need the internal name of item type that you can get as shown below.

log in to workflow builder , load the item type , right click on item type and get the internal name.

or you can use the below query to get item type and it's internal name.


select * from WF_ITEM_TYPES_vl
where name ='CS_MSGS';

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.

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.

Now you have everything so it's time to write the code .




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;

===================================================================

wf_notification.denormalize_notification and COMMIT is mandatory at last.


==================================================================

This way you can send normal text as well as HTML notifications.

Just you need to identify proper item type and message name.