Oracle user responsiblity
/* Formatted on 2018/07/04 17:09 (Formatter Plus v4.8.8) */
DECLARE
v_user VARCHAR2 (10) := 'USERNAME'; --欲建立的帳號
v_pwd VARCHAR2 (10) := 'DEFAULT PASSWORD'; --欲建立的帳號密碼
v_resp_name VARCHAR2 (100) := 'RESP_NAME'; --欲建立的Responsibility Name
--
v_user_id NUMBER;
v_text VARCHAR2 (100);
v_resp_id NUMBER;
v_app_id NUMBER;
BEGIN
v_text :=
fnd_web_sec.create_user (p_user => v_user,
p_pwd => v_pwd,
p_user_id => v_user_id
);
DBMS_OUTPUT.put_line (v_text || ' ' || TO_CHAR (v_user_id));
IF v_text = 'Y'
THEN
BEGIN
SELECT r.responsibility_id, r.application_id
INTO v_resp_id, v_app_id
FROM fnd_responsibility r, fnd_responsibility_tl t
WHERE r.responsibility_id = t.responsibility_id
AND r.application_id = t.application_id
AND t.LANGUAGE = 'US'
AND t.responsibility_name = v_resp_name;
fnd_user_resp_groups_api.insert_assignment
(user_id => v_user_id,
responsibility_id => v_resp_id,
responsibility_application_id => v_app_id,
security_group_id => 0,
start_date => TRUNC
(SYSDATE
),
end_date => NULL,
description => v_resp_name
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put ('Create Responsibility has errors!');
END;
END IF;
END;