How to Add Responsibility to User from Backend

How to Add Responsibility to User from Backend

A responsibility can be added to a user using the “AddResp” procedure of “fnd_user_pkg”.

AddResp Procedure parameters

PROCEDURE AddResp(
    username       VARCHAR2,
    resp_app       VARCHAR2,
    resp_key       VARCHAR2,
    security_group VARCHAR2,
    description    VARCHAR2,
    start_date DATE,
    end_date DATE
  );
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

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;

Query to check responsibilities attached to a 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;