Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Tuesday, 29 March 2016

How to Add Responsibility to User from Backend in Oracle applications R12

Query to find 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:

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)
                         );

Share this article :

0 comments:

Post a Comment