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;