Removing End Date from Responsibilities Given to USER

Sometimes while working on a support projects, we used to have access to the read only responsibilities ,or though we have given the access to the super users initially, but those accesses might have revoked after system went live.

But in test environment, we may require those accesses back so as to fix the bugs or to test the functionality.

The removing of end date from a responsibility which is already assigned to a user, can be done using fnd_user_resp_groups_api API.

DECLARE
p_user_name   VARCHAR2 (50) := 'AIUEBY';
p_resp_name   VARCHAR2 (50) := 'Order Management Super User';
v_user_id NUMBER (10) := 0;
v_responsibility_id NUMBER (10) := 0;
v_application_id NUMBER (10) := 0;
BEGIN
--To Get User ID for USERNAME
BEGIN
SELECT user_id
  INTO v_user_id
  FROM fnd_user
WHERE UPPER (user_name) = UPPER (p_user_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line ('User not found');
RAISE;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error finding User.');
RAISE;
END;
    --To Get Applciation id and Responsibility ID.
BEGIN
SELECT application_id,
   responsibility_id
  INTO v_application_id,
       v_responsibility_id
  FROM fnd_responsibility_vl
WHERE UPPER (responsibility_name) = UPPER (p_resp_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line ('Responsibility not found.');
RAISE;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.put_line('More than one responsibility found with this name.');
RAISE;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error finding responsibility.');
RAISE;
END;

BEGIN
DBMS_OUTPUT.put_line ('Initializing The Application');

fnd_global.apps_initialize (user_id => v_user_id,
resp_id => v_responsibility_id,
resp_appl_id => v_application_id);

DBMS_OUTPUT.put_line('Calling FND_USER_RESP_GROUPS_API API To Insert/Update Resp');

fnd_user_resp_groups_api.update_assignment(user_id => v_user_id,
   responsibility_id => v_responsibility_id,
   responsibility_application_id => v_application_id,
   security_group_id => 0,
   start_date => SYSDATE,
   end_date => NULL,
   description => NULL);

DBMS_OUTPUT.put_line('The End Date has been removed from responsibility');

COMMIT;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error calling the API');
RAISE;
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error calling the API');
RAISE;
END;
END;