If we need to end date a responsibility for a user then we can use the below syntax.
Syntax:
DECLARE
p_user_name VARCHAR2 (50);
p_resp_name VARCHAR2 (50) := 'Application Developer'; /*Responsibility Name*/
v_user_id NUMBER (10) := 0;
v_responsibility_id NUMBER (10) := 0;
v_application_id NUMBER (10) := 0;
BEGIN
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;
/*DBMS_OUTPUT.
put_line (
'Responsibility ID'
|| v_responsibility_id
|| 'and'
|| 'Application ID'
|| v_application_id);*/
FOR u
IN (SELECT fu.user_id, furg.start_date,furg.description
FROM fnd_user_resp_groups_direct furg,
fnd_user fu,
fnd_responsibility_tl fr
WHERE fr.responsibility_name = p_resp_name
AND furg.user_id = fu.user_id
AND fu.user_name <> 'SYSADMIN'
AND furg.responsibility_id = fr.responsibility_id
AND fr.language = USERENV ('LANG'))
LOOP
v_user_id := u.user_id;
/*DBMS_OUTPUT.put_line ('User_name' || v_user_id);*/
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 => u.start_date,
end_date => TRUNC (SYSDATE) - 1,
description => u.description);
DBMS_OUTPUT.
put_line ('The End Date has been set for responsibility');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error calling the API');
RAISE;
END;
END LOOP;
END;
Syntax:
DECLARE
p_user_name VARCHAR2 (50);
p_resp_name VARCHAR2 (50) := 'Application Developer'; /*Responsibility Name*/
v_user_id NUMBER (10) := 0;
v_responsibility_id NUMBER (10) := 0;
v_application_id NUMBER (10) := 0;
BEGIN
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;
/*DBMS_OUTPUT.
put_line (
'Responsibility ID'
|| v_responsibility_id
|| 'and'
|| 'Application ID'
|| v_application_id);*/
FOR u
IN (SELECT fu.user_id, furg.start_date,furg.description
FROM fnd_user_resp_groups_direct furg,
fnd_user fu,
fnd_responsibility_tl fr
WHERE fr.responsibility_name = p_resp_name
AND furg.user_id = fu.user_id
AND fu.user_name <> 'SYSADMIN'
AND furg.responsibility_id = fr.responsibility_id
AND fr.language = USERENV ('LANG'))
LOOP
v_user_id := u.user_id;
/*DBMS_OUTPUT.put_line ('User_name' || v_user_id);*/
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 => u.start_date,
end_date => TRUNC (SYSDATE) - 1,
description => u.description);
DBMS_OUTPUT.
put_line ('The End Date has been set for responsibility');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error calling the API');
RAISE;
END;
END LOOP;
END;
1 Comments
Your article gives me another approach on the subject.
ReplyDeletePost a Comment