This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Jul 31, 2013 9:09 PM by jagadekara RSS

Sending the same Notification to multiple users

675082 Newbie
Currently Being Moderated
Hi ,

Could any help me how to send the same notification to different users .

my requirement is I have one 1)submitter 2) Approver 3) Export_Approver

When Approver approves the Shipment the submitter is getting the notification but I want when the Export _approver approves the shipment both  Submitter and Approver should get the Notification that he has appoved and the message is same for both  1 and 2 .

how to achieve this? please provide me the steps .


Shashank
  • 1. Re: Sending the same Notification to multiple users
    SarojaKandepuneni Journeyer
    Currently Being Moderated
    Perform the below steps to achieve your requirement
    1. Create a Adhoc role with notification preference as MAIL* (i.e MAILHTML, MAILATTH...etc) and email address value as null. Assign the users 1)submitter
    and 2) Approver to this role
    2. Create a notification activity(FYI notification) such that after Export_Approver approves the control comes to this notification activity
    3. Set the performer for this notification activity as the role which you created in step1
  • 2. Re: Sending the same Notification to multiple users
    675082 Newbie
    Currently Being Moderated
    Hi ,

    Thanks for that Reply . I have got an idea but unfortunately I am not able to figure out how to start . I mean for setting the Adhoc role do we need to write a backend procedure ? if so how how to assign them in the workflow builder under notifications.

    for example as mentioned I have the 1) Submiiter procedure and fuuction in WF builder .
    2) Approver procedure and fuuction in WF builder .
    3) Export Approver procedure and fuuction in WF builder .


    As mentioned I able to send the notification to the submitter when the approver approves it , because here there is only single user.

    but incase of export approver both approver and submitter should get the notification.

    So how to how go about it ? it would be helpful if you can provide me the steps ?



    Regards,
    Shashank
  • 3. Re: Sending the same Notification to multiple users
    SarojaKandepuneni Journeyer
    Currently Being Moderated
    The following are the steps you can do to achieve your requirement

    1. First you need to create the Ad-hoc role using the below API

    declare
    l_role_name varchar2(50):='TESTROLE';
    l_role_display_name varchar2(50):='TESTROLE';

    begin

    WF_DIRECTORY.CreateAdHocRole (

    role_name => l_role_name,
    role_display_name => l_role_display_name ,
    language => 'AMERICAN'     ,
    territory => 'AMERICA' ,
         email_address => null,
         notification_preference => 'MAILHTML'
         );
    commit;
    end;
    /

    l_role_name -> the role name which you want to create

    2. Assign the users 'Submitter' and 'Approver' to the Ad-hoc role created in above step
    using the below API

    declare
    l_users varchar2(100):='TESTUSER1 TESTUSER2' ;
    begin

    WF_DIRECTORY.AddUsersToAdHocRole (
    role_name => 'TESTROLE',
    role_users => l_users
         );

    commit;
    end;

    l_users -> the user names separated by space which you want to assign to a role


    3. Create a item attribute say 'RECPT' to specify this role in the workflow
    4. Assign this item attribute 'RECPT' to the performer of notification activity
    by which you want to send the approval email
    5. In the code you can set the item attribute 'RECPT' to the Ad-hoc role which you created

    wf_engine.SetItemAttrText(<itemType>, <itemKey>, 'RECPT', 'TESTROLE');
  • 4. Re: Sending the same Notification to multiple users
    675082 Newbie
    Currently Being Moderated
    hi ,

    Thank you . will try that method and post my reply .


    Shashank
  • 5. Re: Sending the same Notification to multiple users
    NoLongerActive Pro
    Currently Being Moderated
    As an additional note, if you are creating the ad-hoc role and adding users to it from within your Workflow process, you should NOT commit unless you are in an autonomous transaction.

    Also, you can add users to the ad-hoc role when the role is created to reduce the need for an additional call.

    HTH,

    Matt

    -----
    WorkflowFAQ.com - the ONLY independent resource for Oracle Workflow development

    Alpha review chapters from my book "Developing With Oracle Workflow" are available via my website http://www.workflowfaq.com
    Have you read the blog at http://www.workflowfaq.com/blog ?
    WorkflowFAQ support forum: http://forum.workflowfaq.com
  • 6. Re: Sending the same Notification to multiple users
    675082 Newbie
    Currently Being Moderated
    Hi Sarojak,

    Tried in this way with the script provided by you .

    1) Created a procedure inside a custom package and executed .
    2) I can see that the 'TestRole ' is created under wf_Roles table.
    3) after writing this proc and compiling it , created an attribute under workflow builder as Receipient with type as Role.
    4) Assigned this attribute as performer under the notification .

    5)when running the workflow from developer studio I am getting the following error.


    **********procedure**************

    Procedure XXAPL_Adhocusers ( p_itemtype IN VARCHAR2,
    p_itemkey IN VARCHAR2,
    p_actid IN NUMBER,
    p_funcmode IN VARCHAR2,
    x_result OUT VARCHAR2)

    Is
    l_role_name varchar2(100):='TESTROLE';
    l_role_display_name varchar2(100):='TESTROLE';
    p_Shipment_Id Number;
    Req_user_name Varchar2(100);
    p_Appr_user_name Varchar2(100);
    l_users varchar2(100):='REQUESTOR , APPROVER_NAME' ;
    p_Exp_Appr_usr_nm Varchar2(100);

    Begin

    p_shipment_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
    itemkey => p_itemkey,
    aname => 'SHIPMENT_ID');

    Req_user_name := wf_engine.getitemattrtext (itemtype => p_itemtype,
    itemkey => p_itemkey,
    aname =>'REQUESTOR');

    p_Appr_user_name:= wf_engine.getitemattrtext(itemtype => p_itemtype,
    itemkey => p_itemkey,
    aname =>'APPR_USER_NAME');


    p_Exp_Appr_usr_nm:=wf_engine.getitemattrtext (itemtype => p_itemtype,
    itemkey => p_itemkey,
    aname =>'EXP_CTRL_USR_NAME');


    WF_DIRECTORY.CreateAdHocRole(role_name => l_role_name,
    role_display_name => l_role_display_name ,
    language => 'AMERICAN' ,
    territory => 'AMERICA' ,
    email_address => null,
    notification_preference => 'MAILHTML'
    );

    WF_DIRECTORY.AddUsersToAdHocRole(role_name => 'TESTROLE',
    role_users => l_users
    );

    Wf_Engine.SetItemAttrText (itemtype => p_itemtype,
    itemkey => p_itemkey,
    aname =>'RECEPIENT',
    avalue =>'TESTROLE');

    Commit;
    END XXAPL_Adhocusers;


    ***** don't know where exactly I am going wrong ******************

    Activity Type Notice

    Error Name WFENG_NOTIFICATION_PERFORMER

    Error Message 3120: Activity 'XXAPLSHP/279194' has no performer.

    Error Stack Wf_Engine_Util.Notification_Send(XXAPLSHP, 10214, 279194, XXAPLSHP:MSG_SR_MATRL_SHIPD) Wf_Engine_Util.Notification(XXAPLSHP, 10214, 279194, RUN


    I am grateful for Patience.

    Shashank
  • 7. Re: Sending the same Notification to multiple users
    SarojaKandepuneni Journeyer
    Currently Being Moderated
    Please check the following things

    1. I guess REQUESTOR , APPROVER_NAME are the names of the users which has to receive the email notification

    2. Please check that users are assigned to role using the below query
    SELECT * FROM wf_user_roles WHERE role_name = 'TESTROLE'
    It should list the users that you assigned

    3. The item attribute which you created for assigning the recipient value should be of type 'Text' but not 'Role'
    Please modify it.

    4. what Is the item attribute name which you created to specify performer value?, is it 'RECEPIENT'
    Verify the value that was set at runtime
    select * from WF_ITEM_ATTRIBUTE_VALUES WHERE item_type=<item Type> and item_key = <item key> and name = 'RECEPIENT'
  • 8. Re: Sending the same Notification to multiple users
    675082 Newbie
    Currently Being Moderated
    1. I guess REQUESTOR , APPROVER_NAME are the names of the users which has to receive the email notification

    Ans : Yes the REQUESTOR and APPROVER_NAME are the user names of those who have to receive the notification.

    2. question
    ANS : This query has returned no rows for me .

    3 ANS : modified the attribute as text in the workflow builder.

    4. what Is the item attribute name which you created to specify performer value?, is it 'RECEPIENT'
    Verify the value that was set at runtime

    select * from WF_ITEM_ATTRIBUTE_VALUES WHERE item_type=<item Type> and item_key = <item key> and name = 'RECEPIENT'

    ans : yes it RECEPIENT only .
    from the query i can see that name = 'RECEPIENT' and around 12 rows have gone in to error .


    Shashnk
  • 9. Re: Sending the same Notification to multiple users
    780509 Newbie
    Currently Being Moderated
    <wrong user - sorry!>

    Edited by: user13299470 on Sep 9, 2010 11:01 AM
  • 10. Re: Sending the same Notification to multiple users
    NoLongerActive Pro
    Currently Being Moderated
    Hi,

    If you have APPROVER and REQUESTOR_NAME as users in the database, then this should have worked.

    What do you get if you try

    SELECT *
    FROM wf_users
    WHERE name IN ('APPROVER', 'REQUESTOR_NAME');

    You should only have one record for the last query - that checks what value the RECIPIENT item attribute has in the specific instance of the workflow process that you are running. You shouldn't get more than one record for the query.

    I would also modify the variable that contains the list of users to add to remove the spaces. IIRC, comma or space can be used as a delimiter, so the system might be getting confused about what to add.

    Finally, what does wfstat.sql show for the process that is in error?

    HTH,

    Matt
    -----
    WorkflowFAQ.com - the ONLY independent resource for Oracle Workflow development

    Alpha review chapters from my book "Developing With Oracle Workflow" are available via my website http://www.workflowfaq.com
    Have you read the blog at http://www.workflowfaq.com/blog ?
    WorkflowFAQ support forum: http://forum.workflowfaq.com
  • 11. Re: Sending the same Notification to multiple users
    SarojaKandepuneni Journeyer
    Currently Being Moderated
    The error message 'Activity 'XXAPLSHP/279194' has no performer' is clearly showing that performer
    is not set for the notification activity.

    Plase debug the issue step by step in the following order

    1. First check that users 'REQUESTOR' and 'APPROVER_NAME' are exists in DB by using the table
    wf_local_roles
    2. Then check whether the Ad-hoc role 'TESTROLE' is created using the same table
    select * from wf_local_roles where name='TESTROLE'
    3. Check that users are added to the role
    SELECT * FROM wf_user_roles WHERE role_name = 'TESTROLE'
    4. If any of the above specified steps are not working as expected then create the users, role manually
    and assign the users to role using the given scripts just to ensure that we are using the scripts correctly.
    Better use space as separator for the users when the assigning to the role.
    5. Launch the workflow to send the notification
    6. You can check the value assigned to 'RECEPIENT' at runtime with the below query
    select * from WF_ITEM_ATTRIBUTE_VALUES WHERE item_type=<item Type> and item_key = <item key> and name = 'RECEPIENT'
  • 12. Re: Sending the same Notification to multiple users
    675082 Newbie
    Currently Being Moderated
    1. First check that users 'REQUESTOR' and 'APPROVER_NAME' are exists in DB by using the table
    wf_local_roles
    2. Then check whether the Ad-hoc role 'TESTROLE' is created using the same table
    select * from wf_local_roles where name='TESTROLE'
    3. Check that users are added to the role
    SELECT * FROM wf_user_roles WHERE role_name = 'TESTROLE'
    4. If any of the above specified steps are not working as expected then create the users, role manually
    and assign the users to role using the given scripts just to ensure that we are using the scripts correctly.
    Better use space as separator for the users when the assigning to the role.
    5. Launch the workflow to send the notification
    6. You can check the value assigned to 'RECEPIENT' at runtime with the below query
    select * from WF_ITEM_ATTRIBUTE_VALUES WHERE item_type=<item Type> and item_key = <item key> and name = 'RECEPIENT'


    well the REQUESTOR and APPROVER are the attribute names of two different users. I using getitemattribute from another procedure in the same package .

    the addhoc TESTROLE has been created in wf_roles.
    but the users are not added to the that role .

    Briefly explaing what i have done -----------------------

    Procedure XX_Adhocusers ( p_itemtype IN VARCHAR2,
    p_itemkey IN VARCHAR2,
    p_actid IN NUMBER,
    p_funcmode IN VARCHAR2,
    x_result OUT VARCHAR2)
    Is
    l_role_name Varchar2(100):='TESTROLE';
    l_role_display_name Varchar2(100):='TESTROLE';
    p_Shipment_Id Number;
    Req_user_name Varchar2(100);
    p_Appr_user_name Varchar2(100);
    l_users Varchar2(100); -- ------'fu.USER_NAME'||','||fu1.USER_NAME ;

    --- p_Exp_Appr_usr_nm Varchar2(100);
    Begin

    l_users := Req_user_name||' '||p_Appr_user_name ;

    Req_user_name := Wf_Engine.GetItemAttrText (itemtype => p_itemtype,
    itemkey => p_itemkey,
    aname => 'REQUESTOR' );

    p_Appr_user_name:= Wf_Engine.GetItemAttrText(itemtype => p_itemtype,
    itemkey => p_itemkey,
    aname =>'APPR_USER_NAME' );


    p_shipment_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
    itemkey => p_itemkey,
    aname => 'SHIPMENT_ID');


    -- Wf_Engine.SetItemAttrText(itemtype => p_itemtype,
    -- itemkey => p_itemkey,
    -- aname => 'TASK_ID',
    -- avalue => l_Task_id );
    --
    -- Wf_Engine.SetItemAttrText(itemtype => p_itemtype,
    -- itemkey => p_itemkey ,
    -- aname =>'EXP_ORG_ID',
    -- avalue => l_exp_org_id);
    --
    -- Wf_Engine.SetItemAttrText(itemtype => p_itemtype,
    -- itemkey => p_itemkey ,
    -- aname =>'ESTI_SHIPG_CST',
    -- avalue => l_shipg_cst);

    WF_DIRECTORY.CreateAdHocRole(role_name => l_role_name,
    role_display_name => l_role_display_name ,
    language => 'AMERICAN' ,
    territory => 'AMERICA' ,
    email_address => null,
    notification_preference => 'MAILHTML',
    role_users => l_users,
    status => 'ACTIVE',
    expiration_date => NULL
    );

    -- WF_DIRECTORY.AddUsersToAdHocRole(role_name => 'TESTROLE',
    -- role_users => l_users
    -- );

    -- dbms_output.put_line('Created Role' ||' '||l_role_name);

    ---Insert into xxtest values (l_users);

    Wf_Engine.SetItemAttrText (itemtype => p_itemtype,
    itemkey => p_itemkey,
    aname =>'RECEPIENT',
    avalue =>'TESTROLE');

    Commit ;

    If l_users Is NOT Null
    THEN
    x_result := 'COMPLETE:Y';
    ELSE
    x_result := 'COMPLETE:N' ;
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    x_result := 'COMPLETE:N';

    END XX_Adhocusers;

    END XX_Shipg_WF;


    *******************

    In the Workflow builder Created a new function as adhoc user assigned this XX_Shipg_WF.XX_Adhocusers in function name .
    in wf when export approver is notifed for approval it will move to the fucntion adhoc user which i have defined and then the approval notification goes to both the Submitter and Approver . here assigned the performer as 'RECEPIENT' in notification.

    ************************

    Matt and Sarojak , please do the corrections if you happen to find any errors in the script.

    Regards,
    Shashnk
  • 13. Re: Sending the same Notification to multiple users
    675082 Newbie
    Currently Being Moderated
    Getting this workflow err when running from the develope studio in sysadmin

    Error Name -6500 [Error Process Error: -4

    Error Message  ORA-06500: PL/SQL: storage error ORA-04030: out of process memory when trying to allocate 16408 bytes (koh-kghu call ,pmucalm coll) [Error Process Error: ORA-04030: out of process memory when trying to allocate 32680 bytes (session heap,ktspsrchche: L2s)]

    Error Stack Wf_Directory.CreateAdHocRole() Wf_Engine_Util.Function_Call(XX_Shipg_WF.XX_Adhocusers, XXSHP, 9896, 279770, RUN) [Error Process Error: Wf_Item.Create_Item(WFERROR, WF973639, DEFAULT_ERROR) Wf_Engine.CreateProcess(WFERROR, WF973639, DEFAULT_ERROR)]
  • 14. Re: Sending the same Notification to multiple users
    AlejandroSosa Journeyer
    Currently Being Moderated
    The PLSQL code looks fine. But it still looks the notification does not have a way to know who the performer is going to be. Please check you have done the following in WF Builder:

    For the corresponding message of this notification create another attribute, type Role, any name you like. In the Default panel set Type to Item Attribute, and Value set to RECIPIENT (the one you already created at item level). This will set the message attribute to whatever the value you set for Item attribute RECIPIENT.

    Reload your workflow via WFLOAD or re-save and see how it goes.

    Alejandro
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points