R12.2 Apps DBA. Powered by Blogger.

How to Add Responsibility to User from Back-end in Oracle Apps 12.2

No comments :
-----------------------------------------------------
PROCEDURE AddResp(
    username       VARCHAR2,    -- User Name --
    resp_app       VARCHAR2,    -- Responsibility Application Short Name --
    resp_key       VARCHAR2,    -- Responsibility Key --
    security_group VARCHAR2,    -- Security Group Id --
    description    VARCHAR2,    -- Any Description --
    start_date DATE,            -- Strat Date -- Default SYSDATE --
    end_date DATE               -- End Date --Default NULL --
  );
Here is the query to find the parameter values
--------------------------------------------------------
SELECT fa.application_short_name,
       fr.responsibility_key,
       fsg.security_group_key,
       frt.description
  FROM apps.fnd_responsibility fr,
       fnd_application fa,
       fnd_security_groups fsg,
       fnd_responsibility_tl frt
 WHERE frt.responsibility_name = 'System Administrator'
   AND frt.LANGUAGE = USERENV ('LANG')
   AND frt.responsibility_id = fr.responsibility_id
   AND fr.application_id = fa.application_id
   AND fr.data_group_id = fsg.security_group_id;
API to add responsibility to user
-------------------------------------------------------------
-- You can use following api to add responsibility to the user you want. It --- will prompt you for the username and responsibility name.
-------------------------------------------------------------
DECLARE
   v_user_name             VARCHAR2 (30)  := '&user_name';
   v_responsibility_name   VARCHAR2 (100) := '&responsibility_name';
   v_application_name      VARCHAR2 (100) := NULL;
   v_responsibility_key    VARCHAR2 (100) := NULL;
   v_security_group        VARCHAR2 (100) := NULL;
   v_description           VARCHAR2 (100) := NULL;
BEGIN
   SELECT fa.application_short_name, fr.responsibility_key,
          fsg.security_group_key, frt.description
     INTO v_application_name, v_responsibility_key,
          v_security_group, v_description
     FROM apps.fnd_responsibility fr,
          fnd_application fa,
          fnd_security_groups fsg,
          fnd_responsibility_tl frt
    WHERE frt.responsibility_name = v_responsibility_name
      AND frt.LANGUAGE = USERENV ('LANG')
      AND frt.responsibility_id = fr.responsibility_id
      AND fr.application_id = fa.application_id
      AND fr.data_group_id = fsg.security_group_id;
   fnd_user_pkg.addresp (username            => v_user_name,
                         resp_app            => v_application_name,
                         resp_key            => v_responsibility_key,
                         security_group      => v_security_group,
                         description         => v_description,
                         start_date          => SYSDATE,
                         end_date            => NULL
                        );
   COMMIT;
   DBMS_OUTPUT.put_line (   'Responsiblity '
                         || v_responsibility_name
                         || ' is attached to the user '
                         || v_user_name
                         || ' Successfully'
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
                         (   'Unable to attach responsibility to user due to'
                          || SQLCODE
                          || ' '
                          || SUBSTR (SQLERRM, 1, 100)
                         );
END;
Once this is complete, Here is the query to verify whether the responsibility is attached to the user.
----------------------------------------------------------------
SELECT frt.responsibility_name
  FROM fnd_user_resp_groups furg, fnd_user fu, fnd_responsibility_tl frt
 WHERE fu.user_name = '&USER_NAME'
   AND fu.user_id = furg.user_id
   AND furg.responsibility_id = frt.responsibility_id;

No comments :

Post a Comment

Note: only a member of this blog may post a comment.