0 Replies Latest reply on Aug 17, 2013 9:01 PM by CSK

    Workflow notifications to Distribution List

    CSK

      Hi Everyone,

       

      We have an issue with adding a new role(user) to the email distribution list and sending workflow notifications.

       

       

      Please find the steps we have done,

       

       

      1. Created a distribution list in Oracle XXDISTLIST, Navigation Alert Manager--> Distribution lists, which has Mail recipients USER1@YYY.COM;USER2@YYY.COM;USER3@YYY.COM

       

      2. The above distribution list has been created a new email alias called XXDISTLIST@YYY.COM which consist of the users USER1,USER2 and USER3

       

      3. Now based on a condition if l_no is not null then we need to add one more person USER4 (based on l_no USER4 may vary eg. 1_no=1 then USER4=XXX, 1_no=2 then USER4=YYY etc) at run time to the Distribution XXDISTLIST and in email alias XXDISTLIST@YYY.COM

       

      if l_no is not null then XXDISTLIST should consist of USER1,USER2,USER3 and USER4 else it should only have USER1,USER2 and USER3

       

       

      The problem is, it is adding the USER4 in WF_ROLES,WF_USER_ROLES,WF_LOCAL_ROLES and WF_USER_ROLE_ASSIGNMENTS but when we checked mail box notification the group email XXDISTLIST@YYY.COM has only members USER1,USER2 and USER3 and not including USER4

       

      Please help what went wrong below.

       

       

      CREATE OR REPLACE PACKAGE BODY APPS.XXTEST_PKG

      AS

         PROCEDURE XXTEST_PRC

         (

         p_dist_list                IN         VARCHAR2

         p_no                       IN          NUMBER

         p_seq_no                   IN          NUMBER

         )

         IS

            l_itemtype                       VARCHAR2(20)            :='XXDISTLISTTEST';

            l_role_name                      VARCHAR2(50);

            l_role_cnt                       NUMBER;

            l_no                             NUMBER;

            l_user                           VARCHAR2(50);

            l_user_name                      VARCHAR2 (100)  := 'USER1 USER2 USER3';

            lc_dist_list_email               VARCHAR2 (100)  := 'XXDISTLIST@YYY.COM';--Group Email Name created with USER1,USER2 and USER3

         BEGIN

            --Create a Adhoc role--

            l_role_name        := p_dist_list;-- The value for p_dist_list is a distribution list 'XXDISTLIST' created in oracle alert Manager--> Distribution lists, which has Mail recipients USER1@YYY.COM;USER2@YYY.COM;USER3@YYY.COM

            l_no               := p_no;

           

            --Check if the role already Exists--

            SELECT count(1)

            INTO   l_role_cnt

            FROM   wf_roles

            WHERE  name = l_role_name;

            --

                  IF l_role_cnt > 0 THEN

                 

                  WF_DIRECTORY.REMOVEUSERSFROMADHOCROLE

                    (

                     l_role_name

                    );

                  COMMIT; 

                 

                  WF_DIRECTORY.ADDUSERSTOADHOCROLE

                      (

                      l_role_name

                      ,lc_user_name

                      );

                  COMMIT;

       

                  ELSE

                   --Create Adhoc Role--

                  WF_DIRECTORY.CREATEADHOCROLE

                              (l_role_name,

                               l_role_name,

                               NULL,

                               NULL,

                               'Dist List',

                               'MAILHTML',

                               NULL,

                               lc_dist_list_email,

                               lc_user_name,

                               'ACTIVE',

                               NULL

                              );

                             

                  END IF;

                 

                IF l_no IS NOT NULL

                THEN

               

                  WF_DIRECTORY.ADDUSERSTOADHOCROLE

                      (

                      l_role_name

                      ,l_user    -- USER4 (email address is USER4@YYY.COM)

                      );

                  COMMIT;              

                END IF;

            --

            WF_ENGINE.CREATEPROCESS

            (

             itemtype   => l_itemtype

            ,itemkey    => p_seq_no

            ,process    => 'XXTEST_PROCESS'

            ,user_key   => p_seq_no

            );

           

            WF_ENGINE.SETITEMATTRTEXT

            (

             itemtype        => l_itemtype

            ,itemkey         => p_seq_no

            ,aname           => 'TEST_USER'

            ,avalue          => l_role_name

            );

           

            --Start Workflow Process--

            WF_ENGINE.STARTPROCESS

            (

             itemtype        => l_itemtype

            ,itemkey         => p_seq_no

            );

            --

            COMMIT;

                --

         EXCEPTION

            WHEN OTHERS THEN

               lc_error_msg                   := 'SQL Error: ' || SQLERRM;

               fnd_file.put_line (fnd_file.LOG,  lc_error_msg);

               RAISE;  

         END XXTEST_PRC;

       

      END XXTEST_PKG;

      /

       

       

       

      Thanks,

      CSK