1 2 Previous Next 24 Replies Latest reply: Jul 31, 2013 11:09 PM by jagadekara RSS

    Sending the same Notification to multiple users

    675082
      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
          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
            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
              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
                hi ,

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


                Shashank
                • 5. Re: Sending the same Notification to multiple users
                  NoLongerActive
                  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
                    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
                      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
                        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
                          <wrong user - sorry!>

                          Edited by: user13299470 on Sep 9, 2010 11:01 AM
                          • 10. Re: Sending the same Notification to multiple users
                            NoLongerActive
                            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
                              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
                                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
                                  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
                                    Alejandro Sosa
                                    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