Friday, April 16, 2010

Removing Enddate from Responsibilities given to Users

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.

Sample Procedure for removing end date from Responsibilities given to Users:
--------------------------------------------------------------------------------------------


DECLARE

p_user_name VARCHAR2 (50) := 'A42485';
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

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;

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

No comments:

Post a Comment