Adding Reponsibility To User

DECLARE

   v_user_name       VARCHAR2 (15) := 'MYRANJIT';

   v_req_resp_name   VARCHAR2 (50) := 'Inventory';

   v_user_id         NUMBER (10);

   v_resp_id         NUMBER (10);

   v_appl_id         NUMBER (10);

   v_count           NUMBER (10);

   v_resp_app        VARCHAR2 (50);

   v_resp_key        VARCHAR2 (50);

   v_description     VARCHAR2 (100);

   RESULT            BOOLEAN;

   ln_count          NUMBER;

BEGIN

   SELECT fu.user_id, frt.responsibility_id, frt.application_id

     INTO v_user_id, v_resp_id, v_appl_id

     FROM fnd_user fu,

          fnd_responsibility_tl frt,

          fnd_user_resp_groups_direct furgd

    WHERE     fu.user_id = furgd.user_id

          AND frt.responsibility_id = furgd.responsibility_id

          AND frt.LANGUAGE = 'US'

          AND fu.user_name = v_user_name

          AND frt.responsibility_name = 'System Administrator';

   fnd_global.apps_initialize (v_user_id, v_resp_id, v_appl_id);

  

   SELECT count(*)

   INTO   ln_count

   FROM   fnd_responsibility_vl

   WHERE  responsibility_name = v_req_resp_name;

   SELECT COUNT (*)

     INTO v_count

     FROM fnd_user fu,

          fnd_responsibility_tl frt,

          fnd_user_resp_groups_direct furgd

    WHERE     fu.user_id = furgd.user_id

          AND frt.responsibility_id = furgd.responsibility_id

          AND frt.LANGUAGE = 'US'

          AND fu.user_name = :v_user_name

          AND frt.responsibility_name = v_req_resp_name;

   IF v_count = 0 AND ln_count > 0

   THEN

      SELECT fa.application_short_name,

             frv.responsibility_key,

             frv.description

        INTO v_resp_app, v_resp_key, v_description

        FROM fnd_responsibility_vl frv, fnd_application fa

       WHERE     frv.application_id = fa.application_id

             AND frv.responsibility_name = v_req_resp_name;

      fnd_user_pkg.addresp (username         => v_user_name,

                            resp_app         => v_resp_app,

                            resp_key         => v_resp_key,

                            security_group   => 'STANDARD',

                            description      => v_description,

                            start_date       => SYSDATE - 1,

                            end_date         => NULL);

      DBMS_OUTPUT.put_line (

            'The responsibility added to the user/Not Available in System '

         || v_user_name

         || ' is '

         || v_req_resp_name);

      COMMIT;

   ELSE

      DBMS_OUTPUT.put_line (

         'The responsibility has already been added to the user');

   END IF;

END;