Adding Responsibilities to User and setting the profile options from PL/SQL Script.

In this article we shall see how to:-

1.      add multiple responsibilities to a user in EBS

2.      Set profile option value at user level.

DECLARE

      l_needed_resp varchar2(4000):=   'Alert Manager#

                                        Application Administrator#

                                        Functional Administrator#

                                        System Administration#

                                        System Administrator#

                                        Workflow Administrator Web (New)#

                                        XML Publisher Administrator#';

      CURSOR cur IS

          WITH qry AS (

          SELECT    l_needed_resp resp_string

          FROM    dual

          )

          SELECT    trim(replace(regexp_substr (resp_string, '[^#]+', 1, ROWNUM),chr(10),'')) resp

          FROM    qry

          CONNECT BY LEVEL <= LENGTH(regexp_replace (resp_string, '[^#]+')) + 1

          ;

    lc_user_name              VARCHAR2(100)    := 'XXTEST';

    lc_resp_appl_short_name   VARCHAR2(100)    ;

    lc_responsibility_key     VARCHAR2(100)    ;

    lc_security_group_key     VARCHAR2(100)    ;

    ld_resp_start_date        DATE             := TO_DATE('11-SEP-2016');

    ld_resp_end_date          DATE             := NULL;           

    l_success     BOOLEAN;

    l_user_id     NUMBER;

  BEGIN

      --

      -- This will set value of profile option 'ONT_DEBUG_LEVEL' at User level to 1

      --

      select user_id

      into  l_user_id

      from fnd_user

      where user_name = lc_user_name;

     

      --FND: Diagnostics

      l_success := FND_PROFILE.save

                   ( x_name                 => 'FND_DIAGNOSTICS'

                   , x_value                => 'Y'

                   , x_level_name           => 'USER'

                   , x_level_value          => l_user_id  -- Given User Id

                   , x_level_value_app_id   => NULL

                   ) ;

      COMMIT;            

      IF l_success

      THEN

         DBMS_OUTPUT.put_line('FND: Diagnostics Profile Updated successfully at User Level');

      ELSE

         DBMS_OUTPUT.put_line('FND: Diagnostics Profile Update Failed at User Level. Error:'||sqlerrm);

      END IF;

                        

      --  Utilities:Diagnostics          

      l_success := FND_PROFILE.save

                   ( x_name                 => 'DIAGNOSTICS'

                   , x_value                => 'Y'

                   , x_level_name           => 'USER'

                   , x_level_value          => l_user_id  -- Given User Id

                   , x_level_value_app_id   => NULL

                   ) ;   

                  

      COMMIT;            

      IF l_success

      THEN

         DBMS_OUTPUT.put_line('Utilities:Diagnostics Profile Updated successfully at User Level');

      ELSE

         DBMS_OUTPUT.put_line('Utilities:Diagnostics Profile Update Failed at User Level. Error:'||sqlerrm);

      END IF;

                        

      -- Personalize Self-Service Defn                  

      l_success := FND_PROFILE.save

                   ( x_name                 => 'FND_CUSTOM_OA_DEFINTION'

                   , x_value                => 'Y'

                   , x_level_name           => 'USER'

                   , x_level_value          => l_user_id  -- Given User Id

                   , x_level_value_app_id   => NULL

                   ) ;  

      COMMIT;            

      IF l_success

      THEN

         DBMS_OUTPUT.put_line('Personalize Self-Service Defn Profile Updated successfully at User Level');

      ELSE

         DBMS_OUTPUT.put_line('Personalize Self-Service Defn Profile Update Failed at User Level. Error:'||sqlerrm);

      END IF;

                          

      -- FND: Personalization Region Link Enabled                  

      l_success := FND_PROFILE.save

                   ( x_name                 => 'FND_PERSONALIZATION_REGION_LINK_ENABLED'

                   , x_value                => 'Y'

                   , x_level_name           => 'USER'

                   , x_level_value          => l_user_id  -- Given User Id

                   , x_level_value_app_id   => NULL

                   ) ;

      COMMIT;                                                                                          

      IF l_success

      THEN

         DBMS_OUTPUT.put_line('FND: Personalization Region Link Enabled Profile Updated successfully at User Level');

      ELSE

         DBMS_OUTPUT.put_line('FND: Personalization Region Link Enabled Profile Update Failed at User Level. Error:'||sqlerrm);

      END IF;     

      FOR rec IN cur LOOP

      BEGIN

        dbms_output.put_line('Resp:' || rec.resp);

        

        SELECT frv.responsibility_key, fa.application_short_name,

                                       (SELECT security_group_key

                                          FROM fnd_security_groups

                                         WHERE security_group_id IN (

                                                  SELECT data_group_id

                                                    FROM fnd_responsibility_vl frv,

                                                         fnd_application fa

                                                   WHERE responsibility_key = frv.responsibility_key

                                                     AND frv.application_id = fa.application_id

                                                     AND fa.application_short_name = fa.application_short_name))

                                                                                               security_group

        INTO lc_responsibility_key,lc_resp_appl_short_name,lc_security_group_key

        FROM fnd_responsibility_vl frv, fnd_application fa

        WHERE responsibility_name = rec.resp

              AND frv.application_id = fa.application_id;

          DBMS_OUTPUT.PUT_LINE ( 'lc_responsibility_key = ' || lc_responsibility_key );

          DBMS_OUTPUT.PUT_LINE ( 'lc_resp_appl_short_name = ' || lc_resp_appl_short_name );

          DBMS_OUTPUT.PUT_LINE ( 'lc_security_group_key = ' || lc_security_group_key );                        

                              

            BEGIN

                 fnd_user_pkg.addresp

                 (   username            => lc_user_name,

                    resp_app             => lc_resp_appl_short_name,

                    resp_key             => lc_responsibility_key,

                    security_group       => lc_security_group_key,

                    description         => NULL,

                    start_date           => ld_resp_start_date,

                    end_date            => ld_resp_end_date

                );

            DBMS_OUTPUT.PUT_LINE('************ADDED RESP');

             COMMIT;

            EXCEPTION

                        WHEN OTHERS THEN

                                    DBMS_OUTPUT.PUT_LINE(SQLERRM);

            END;

        EXCEPTION

                    WHEN OTHERS THEN

                                DBMS_OUTPUT.PUT_LINE(SQLERRM);

        END;                  

      END LOOP;

  END;

  /

As you can see that you can specify multiple responsibilities to be attached using “#” and new line delimiter as defined by “l_needed_resp” PL/SQL variable.

This script will speedup the process of setting up the user responsibility and needed profile option values.