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;