R12 - FND - Script to add responsibility using fnd_user_pkg with validation

Some times we don't have the access to add the responsibility to the user using the the Create User form. So for this Oracle is having one API fnd_user_pkg.addresp which can do the job without using the Create User Form.

R12 - FND - Script to add responsibility using fnd_user_pkg with validation


DECLARE

v_user_name VARCHAR2 (10) := '&Enter_User_Name';
v_resp_name VARCHAR2 (50) := '&Enter_Existing_Responsibility_Name';
v_req_resp_name VARCHAR2 (50) := '&Enter_required_Responsibility_Name';
v_user_id NUMBER (10);
v_resp_id NUMBER (10);
v_appl_id NUMBER (10);
v_count NUMBER (10);
v_resp_app VARCHAR2 (50);
v_resp_key VARCHAR2 (50);
v_description VARCHAR2 (100);
RESULT BOOLEAN;

BEGIN

SELECT fu.user_id, frt.responsibility_id, frt.application_id
INTO v_user_id, v_resp_id, v_appl_id
FROM fnd_user fu,
fnd_responsibility_tl frt,
fnd_user_resp_groups_direct furgd
WHERE fu.user_id = furgd.user_id
AND frt.responsibility_id = furgd.responsibility_id
AND frt.LANGUAGE = 'US'
AND fu.user_name = v_user_name
AND frt.responsibility_name = v_resp_name;
fnd_global.apps_initialize (v_user_id, v_resp_id, v_appl_id);

SELECT COUNT (*)
INTO v_count
FROM fnd_user fu,
fnd_responsibility_tl frt,
fnd_user_resp_groups_direct furgd
WHERE fu.user_id = furgd.user_id
AND frt.responsibility_id = furgd.responsibility_id
AND frt.LANGUAGE = 'US'
AND fu.user_name = v_user_name
AND frt.responsibility_name = v_req_resp_name;

IF v_count = 0 THEN

SELECT fa.application_short_name, frv.responsibility_key,
frv.description
INTO v_resp_app, v_resp_key,
v_description
FROM fnd_responsibility_vl frv, fnd_application fa
WHERE frv.application_id = fa.application_id
AND frv.responsibility_name = v_req_resp_name;

fnd_user_pkg.addresp (
username => v_user_name,
resp_app => v_resp_app,
resp_key => v_resp_key,
security_group => 'STANDARD',
description => v_description,
start_date => SYSDATE - 1,
end_date => NULL);

RESULT :=
fnd_profile.SAVE (x_name => 'APPS_SSO_LOCAL_LOGIN',
x_value => 'BOTH',
x_level_name => 'USER',
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => 'FND_CUSTOM_OA_DEFINTION',
x_value => 'Y',
x_level_name => 'USER',
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => 'FND_DIAGNOSTICS',
x_value => 'Y',
x_level_name => 'USER',
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => 'DIAGNOSTICS',
x_value => 'Y',
x_level_name => 'USER',
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => 'FND_HIDE_DIAGNOSTICS',
x_value => 'N',
x_level_name => 'USER',
x_level_value => v_user_id
);

DBMS_OUTPUT.put_line ( 'The responsibility added to the user '
v_user_name
' is '
v_req_resp_name);

COMMIT;

ELSE

DBMS_OUTPUT.put_line
('The responsibility has already been added to the user');

END IF;

END;