Sending Multiple Notification Using ADHOCROLE

1.Create a Attribute(XX_APPROVER) of type 'Role'.
2.Create a Notification and set the Performer as XX_APPROVER.
3.Create a ADHOC user Group
V_USER_CONCAT :='user1 user2 user3';
XX_FYI_ROLE : ='XX_FYI_ROLE';
      WF_DIRECTORY.CREATEADHOCROLE
          (
         ROLE_NAME                             => XX_FYI_ROLE
          ,role_display_name                     => XX_FYI_ROLE
          ,LANGUAGE                              => NULL
          ,territory                                      => NULL
          ,role_description                         => 'MAIN_ROLE'
          ,notification_preference             => 'MAILHTML'
          ,role_users                                  => NULL
          ,email_address                            => NULL
          ,fax                                               => NULL
          ,STATUS                                     => 'ACTIVE'
          ,PARENT_ORIG_SYSTEM      => 'WF_LOCAL_ROLES'
          ,OWNER_TAG                           => NULL
);


4.Add User to the role
      WF_DIRECTORY.ADDUSERSTOADHOCROLE(
                                       ROLE_NAME      =>XX_FYI_ROLE,
                                       ROLE_USERS     =>V_USER_CONCAT
                                       );


5.Attach the Role to the attribute(XX_APPROVER)

         WF_ENGINE.SETITEMATTRTEXT (
                                    ITEMTYPE     => ITEMTYPE,
                                    itemkey            => itemkey,
                                    ANAME         => 'XX_APPROVER',
                                    AVALUE        =>  XX_FYI_ROLE 
                                   ); 
To Delete a User From User Group :

          WF_DIRECTORY.RemoveUsersFromAdHocRole(XX_FYI_ROLE,
  V_USER_CONCAT
  );
Query to Get all the USers attached to a Role :
select user_name from wf_local_user_roles where role_name like 'XX_FYI_ROLE';

Multiple Attachments in Notifications(In Table Format)

Multiple Attachments in Notifications(In Table Format)

Step1 :
Create a Document Type Item Attribute.

Step 2 :
Drag and Drop the item attribute in the Message you want to display the attachments.

Step 3 :
In your Message body Select HTML Body and paste give the attribute internal name

Step 4 :
Create a Procedure get_docs_in_notification.
create or replace
Procedure get_docs_in_notification( document_id IN VARCHAR2
,display_type IN VARCHAR2
,document IN OUT VARCHAR2
,document_type IN OUT VARCHAR2
)
IS
V_SAMPLE1 VARCHAR2(1000);
v_document_id varchar2(1000);
BEGIN
v_document_id :=document_id;
SELECT FND_GFM.CONSTRUCT_DOWNLOAD_URL
(FND_WEB_CONFIG.GFM_AGENT,document_id)
INTO V_SAMPLE1
FROM DUAL;
IF DISPLAY_TYPE = 'text/html' THEN
DOCUMENT :='<STYLE TYPE="text/css">';
DOCUMENT := DOCUMENT || 'TD{font-family: Arial; font-size: 10pt;}';
DOCUMENT := DOCUMENT || 'TH{font-family: Arial; font-size: 12pt;}';
document := DOCUMENT || '</STYLE>';
document := DOCUMENT || '<table border=1>';
DOCUMENT := DOCUMENT || '<tr>';
DOCUMENT := DOCUMENT || '<th colspan="2">Attachments</th>';
DOCUMENT := DOCUMENT || '</tr>';
FOR ILOOP IN (SELECT ROWNUM,
FDV.TITLE,
FND_GFM.CONSTRUCT_DOWNLOAD_URL
(FND_WEB_CONFIG.GFM_AGENT,fdv.media_id) as f_media_id
FROM FND_ATTACHED_DOCUMENTS FAD,
FND_DOCUMENT_ENTITIES FDE,
fnd_documents_vl fdv
WHERE FAD.ENTITY_NAME = FDE.DATA_OBJECT_CODE
AND FDE.DATA_OBJECT_CODE = 'XX_PNK_FORUM_ATTACHMENT'
AND FDV.DOCUMENT_ID = FAD.DOCUMENT_ID
AND fad.pk1_value = v_document_id
) LOOP
document := document || '<tr>';
DOCUMENT := DOCUMENT || '<td>';
DOCUMENT := DOCUMENT || ILOOP.ROWNUM;
document := document || '</td>';
document := document || '<td>';
DOCUMENT := DOCUMENT || '<a href = "';
DOCUMENT := DOCUMENT || ILOOP.F_MEDIA_ID;
DOCUMENT := DOCUMENT || '">';
DOCUMENT := DOCUMENT || ILOOP.TITLE;
document := document || '</a>';
document := document || '</td>';
document := document || '</tr>';
End Loop;
document := document || '</table>';
End If;
document_type := display_type;
END get_docs_in_notification;

Step 5 :
Now Call this procedure when setting value for your document through Pl/SQL.
WF_ENGINE.SETITEMATTRDOCUMENT(
L_ITEMTYPE,
L_ITEMKEY,
'DOC_AS_TABLE',
'PLSQL:get_docs_in_notification/' || 1014
);

The Output Will Be like :

Adding Multiple Attachments Dynamically

Adding Multiple Attachments Dynamically
Step 1 :
Create a Item attribute of type Document.

Step 2 :
Create a message attribute in your message where you want the attachment to appear. Give the internal
name of the attribute as “#ATTACHMENTS”.

Step 3 :
Now you can either give the value to the item attribute created in step 1 dynamically in query or can
hardcore it by inputting the value :
FND:entity=XX_PNK_FORUM_ATTACHMENT&pk1name=FORUM_ID&pk1value=1014
Where :
• XX_PNK_FORUM_ATTACHMENT is the entity name
• pk1name is the Primary Key 1 column Name obtained from table
FND_DOCUMENT_ENTITIES=> pk1_column
Use the Query :
SELECT FDE.DATA_OBJECT_CODE,
fde.pk1_column,
fad.pk1_value
FROM FND_ATTACHED_DOCUMENTS FAD,
FND_DOCUMENT_ENTITIES FDE
WHERE fad.entity_name = FDE.DATA_OBJECT_CODE
Code in Query:
Wf_Engine.setitemattrdocument
( l_itemtype
, l_itemkey
, 'ATTACHMENT_LINK'
, 'FND:entity=XX_PNK_FORUM_ATTACHMENT&pk1name=FORUM_ID&pk1value='||
l_primary_key
);
With this you can get multiple attachments in your notification.