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) := '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;
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) := '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