Oracle EBS 11i Add Responsibility to User by the Responsibility reference of Other User.
Warning: R12 version must be fixed columns sort to insert successfully.
DECLARE
--input parameters
l_reference_user_name VARCHAR2 (50) := 'HKIT_KPWONG';
l_add_resp_user_name VARCHAR2 (50) := 'SZIT_QWR';
l_resp_name VARCHAR2 (300) := 'Application Developer';
-----------------------------------------------------------
l_user_orig_system_id NUMBER := -999999;
l_role_name VARCHAR2 (300) := NULL;
l_add_resp_user_exists NUMBER := 0;
l_insert_role_record NUMBER := 0;
l_insert_role_assign_record NUMBER := 0;
BEGIN
--get responsibility role name
SELECT COUNT (1)
INTO l_add_resp_user_exists
FROM (SELECT u.user_id user_id
, (SELECT responsibility_name
FROM fnd_responsibility_tl
WHERE responsibility_id = wur.role_orig_system_id
AND application_id = fa.application_id
AND language = 'US')
app_responsibility_name
, wur.role_name
, wur.user_orig_system_id
, wur.role_orig_system_id responsibility_id
, fa.application_id responsibility_application_id
, fsg.security_group_id security_group_id
, fnd_date.canonical_to_date ('1000/01/01') start_date
, TO_DATE (NULL) end_date
, TO_CHAR (NULL) description
, TO_NUMBER (NULL) created_by
, TO_DATE (NULL) creation_date
, TO_NUMBER (NULL) last_updated_by
, TO_DATE (NULL) last_update_date
, TO_NUMBER (NULL) last_update_login
FROM fnd_user u
, wf_user_role_assignments_v wura
, wf_user_roles wur
, fnd_application fa
, fnd_security_groups fsg
WHERE wura.user_name = u.user_name
AND wur.role_orig_system = 'FND_RESP'
AND wur.partition_id = 2
AND wura.role_name = wur.role_name
AND wura.user_name = wur.user_name
AND fa.application_short_name = /* Val between 1 st and 2 nd separator */
REPLACE (SUBSTR (wura.role_name
, INSTR (wura.role_name
, '|'
, 1
, 1)
+ 1
, ( INSTR (wura.role_name
, '|'
, 1
, 2)
- INSTR (wura.role_name
, '|'
, 1
, 1)
- 1))
, '%col'
, ':')
AND fsg.security_group_key = /* Val after 3 rd separator */
REPLACE (SUBSTR (wura.role_name
, INSTR (wura.role_name
, '|'
, 1
, 3)
+ 1)
, '%col'
, ':')
AND u.user_name = l_add_resp_user_name) resp
WHERE 1 = 1 AND resp.app_responsibility_name = l_resp_name;
IF (l_add_resp_user_exists > 0)
THEN
DBMS_OUTPUT.put_line (
'Failed !!! Add Responsebility Name: '
|| l_resp_name
|| ' to User: '
|| l_add_resp_user_name
|| ' by reference User: '
|| l_reference_user_name);
DBMS_OUTPUT.put_line (
'The Responsibility exists at Added User: ' || l_add_resp_user_name);
RETURN;
END IF;
--get responsibility role name
SELECT resp.role_name
INTO l_role_name
FROM (SELECT u.user_id user_id
, (SELECT responsibility_name
FROM fnd_responsibility_tl
WHERE responsibility_id = wur.role_orig_system_id
AND application_id = fa.application_id
AND language = 'US')
app_responsibility_name
, wur.role_name
, wur.user_orig_system_id
, wur.role_orig_system_id responsibility_id
, fa.application_id responsibility_application_id
, fsg.security_group_id security_group_id
, fnd_date.canonical_to_date ('1000/01/01') start_date
, TO_DATE (NULL) end_date
, TO_CHAR (NULL) description
, TO_NUMBER (NULL) created_by
, TO_DATE (NULL) creation_date
, TO_NUMBER (NULL) last_updated_by
, TO_DATE (NULL) last_update_date
, TO_NUMBER (NULL) last_update_login
FROM fnd_user u
, wf_user_role_assignments_v wura
, wf_user_roles wur
, fnd_application fa
, fnd_security_groups fsg
WHERE wura.user_name = u.user_name
AND wur.role_orig_system = 'FND_RESP'
AND wur.partition_id = 2
AND wura.role_name = wur.role_name
AND wura.user_name = wur.user_name
AND fa.application_short_name = /* Val between 1 st and 2 nd separator */
REPLACE (SUBSTR (wura.role_name
, INSTR (wura.role_name
, '|'
, 1
, 1)
+ 1
, ( INSTR (wura.role_name
, '|'
, 1
, 2)
- INSTR (wura.role_name
, '|'
, 1
, 1)
- 1))
, '%col'
, ':')
AND fsg.security_group_key = /* Val after 3 rd separator */
REPLACE (SUBSTR (wura.role_name
, INSTR (wura.role_name
, '|'
, 1
, 3)
+ 1)
, '%col'
, ':')
AND u.user_name = l_reference_user_name) resp
WHERE 1 = 1 AND resp.app_responsibility_name = l_resp_name;
IF (l_role_name IS NULL)
THEN
DBMS_OUTPUT.put_line (
'Failed !!! Add Responsebility Name: '
|| l_resp_name
|| ' to User: '
|| l_add_resp_user_name
|| ' by reference User: '
|| l_reference_user_name);
DBMS_OUTPUT.put_line (
'The reference User have not the Responsibility: '
|| l_resp_name
|| '. Please input correct data!');
RETURN;
END IF;
--get user orig system id
SELECT DISTINCT wur.user_orig_system_id
INTO l_user_orig_system_id
FROM wf_user_roles wur
WHERE 1 = 1 AND wur.user_name = l_add_resp_user_name;
IF (l_user_orig_system_id = -999999)
THEN
DBMS_OUTPUT.put_line (
'Failed !!! Add Responsebility Name: '
|| l_resp_name
|| ' to User: '
|| l_add_resp_user_name
|| ' by reference User: '
|| l_reference_user_name);
DBMS_OUTPUT.put_line ('No found User Orig System ID.');
RETURN;
END IF;
INSERT INTO wf_local_user_roles
SELECT l_add_resp_user_name
, wur.role_name
, wur.user_orig_system
, l_user_orig_system_id
, wur.role_orig_system
, wur.role_orig_system_id
, wur.start_date
, wur.expiration_date
, wur.security_group_id
, wur.partition_id
, wur.owner_tag
, wur.created_by
, wur.creation_date
, wur.last_updated_by
, wur.last_update_date
, wur.last_update_login
, wur.assignment_type
, wur.parent_orig_system
, wur.parent_orig_system_id
, wur.user_start_date
, wur.role_start_date
, wur.user_end_date
, wur.role_end_date
, wur.effective_start_date
, wur.effective_end_date
, wur.assignment_reason
FROM wf_local_user_roles wur
WHERE 1 = 1
AND wur.user_name = l_reference_user_name
AND wur.role_name = l_role_name;
l_insert_role_record := SQL%ROWCOUNT;
IF (l_insert_role_record = 0)
THEN
DBMS_OUTPUT.put_line (
'Failed !!! Add Responsebility Name: '
|| l_resp_name
|| ' to User: '
|| l_add_resp_user_name
|| ' by reference User: '
|| l_reference_user_name);
DBMS_OUTPUT.put_line ('No data into Role table.');
RETURN;
END IF;
INSERT INTO wf_user_role_assignments
SELECT l_add_resp_user_name
, wura.role_name
, wura.relationship_id
, wura.assigning_role
, wura.start_date
, wura.end_date
, wura.created_by
, wura.creation_date
, wura.last_updated_by
, wura.last_update_date
, wura.last_update_login
, wura.user_start_date
, wura.role_start_date
, wura.assigning_role_start_date
, wura.user_end_date
, wura.role_end_date
, wura.assigning_role_end_date
, wura.partition_id
, wura.effective_start_date
, wura.effective_end_date
, wura.user_orig_system
, l_user_orig_system_id
, wura.role_orig_system
, wura.role_orig_system_id
, wura.parent_orig_system
, wura.parent_orig_system_id
, wura.owner_tag
, wura.assignment_reason
FROM wf_user_role_assignments wura
WHERE 1 = 1
AND wura.user_name = l_reference_user_name
AND wura.role_name = l_role_name;
l_insert_role_assign_record := SQL%ROWCOUNT;
IF (l_insert_role_record <> l_insert_role_assign_record)
THEN
DBMS_OUTPUT.put_line (
'Failed !!! Add Responsebility Name: '
|| l_resp_name
|| ' to User: '
|| l_add_resp_user_name
|| ' by reference User: '
|| l_reference_user_name);
ROLLBACK;
COMMIT;
END IF;
DBMS_OUTPUT.put_line (
'Successfully !!! Add Responsebility Name: '
|| l_resp_name
|| ' to User: '
|| l_add_resp_user_name
|| ' by reference User: '
|| l_reference_user_name);
COMMIT;
END;
http://www.cnblogs.com/quanweiru/archive/2013/05/21/3090818.html