CREATE TABLE XXX.XXXX_USER_RESP_TBL
(USERNAME VARCHAR2(50),
RESPONSIBILITY_KEY VARCHAR2(240),
END_DATE_FLAG VARCHAR2(1),
ERROR_FLAG VARCHAR2(10),
ERROR_MESSAGE VARCHAR2(2000),
CREATED_BY VARCHAR2(20),
CREATION_DATE DATE,
LAST_UPDATED_BY VARCHAR2(20),
LAST_UPDATE_DATE DATE);
CREATE SYNONYM XXXX_USER_RESP_TBL FOR XXX.XXXX_USER_RESP_TBL ;
Create Control File to Load Data into Stage Table:
LOAD DATA
APPEND
PRESERVE BLANKS
INTO TABLE XXXX_USER_RESP_TBL
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
USERNAME "LTRIM(RTRIM(:USERNAME))"
,RESPONSIBILITY_KEY "LTRIM(RTRIM(:RESPONSIBILITY_KEY))"
,END_DATE_FLAG "LTRIM(RTRIM(:END_DATE_FLAG))"
,ERROR_FLAG "LTRIM(RTRIM(:ERROR_FLAG))"
,ERROR_MESSAGE "LTRIM(RTRIM(:ERROR_MESSAGE))"
,CREATED_BY CONSTANT "-1"
,CREATION_DATE SYSDATE
,LAST_UPDATED_BY CONSTANT "-1"
,LAST_UPDATE_DATE SYSDATE
)
Sample Data File:
a) First Column: Employee Number
b) Second Column: Responsibility Key
c) Third Column: End Date Flag to End Date the Responsibility Assignment
432543|SYSADMIN|Y
654757|SYSTEM_ADMINISTRATOR|
Script to Assign/Deassign Responsibilities to User:
DECLARE
CURSOR c_resp_cursor IS
SELECT gfui.username user_name,
fu.user_id,
(SELECT fa.application_short_name
FROM fnd_application fa
WHERE fr.application_id = fa.application_id) application_short_name,
gfui.responsibility_key,
fr.responsibility_id,
fr.application_id,
SYSDATE start_date,
decode(end_date_flag, 'Y', SYSDATE, '') end_date,
NULL error_flag,
NULL error_msg
FROM xxxx_user_resp_tbl gfui, fnd_responsibility fr, fnd_user fu
WHERE nvl(gfui.error_flag, 'ERROR') = 'ERROR'
AND gfui.responsibility_key = fr.responsibility_key(+)
AND gfui.username = fu.user_name(+);
--
TYPE v_resp_type IS TABLE OF c_resp_cursor%ROWTYPE INDEX BY PLS_INTEGER;
v_resp_tab v_resp_type;
--
v_row_proc NUMBER := 0;
v_count NUMBER := 0;
--
BEGIN
--
v_resp_tab.DELETE;
--
OPEN c_resp_cursor;
LOOP
FETCH c_resp_cursor BULK COLLECT
INTO v_resp_tab LIMIT 5000;
EXIT WHEN v_resp_tab.COUNT = 0;
--
FOR i IN v_resp_tab.FIRST .. v_resp_tab.COUNT
LOOP
v_count := 0;
v_row_proc := 0;
v_resp_tab(i).error_flag := NULL;
v_resp_tab(i).error_msg := NULL;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM fnd_user_resp_groups_direct
WHERE v_resp_tab(i).user_id = user_id
AND v_resp_tab(i).responsibility_id = responsibility_id;
EXCEPTION
WHEN OTHERS THEN
v_count := 0;
END;
--
IF v_resp_tab(i).user_id IS NULL
THEN
--
v_resp_tab(i).error_flag := 'ERROR';
v_resp_tab(i).error_msg := 'User does not exists ' || v_resp_tab(i).user_name;
--
END IF;
--
IF v_resp_tab(i).responsibility_id IS NULL
THEN
--
v_resp_tab(i).error_flag := 'ERROR';
v_resp_tab(i).error_msg := v_resp_tab(i).error_msg ||
' Responsibility does not exists for the Responsibility Key : ' ||
v_resp_tab(i).responsibility_key;
--
END IF;
--
IF v_count = 0
THEN
IF nvl(v_resp_tab(i).error_flag, 'SUCCESS') != 'ERROR'
THEN
--
BEGIN
--
v_resp_tab(i).error_flag := 'SUCCESS';
v_resp_tab(i).error_msg := NULL;
--
fnd_user_pkg.addresp(username => v_resp_tab(i).user_name,
resp_app => v_resp_tab(i).application_short_name,
resp_key => v_resp_tab(i).responsibility_key,
security_group => 'STANDARD',
description => NULL,
start_date => SYSDATE,
end_date => NULL);
EXCEPTION
WHEN OTHERS THEN
v_resp_tab(i).error_flag := 'ERROR';
v_resp_tab(i).error_msg := SQLCODE || '-' || SQLERRM ||
'Issue while adding responsibility ' ||
v_resp_tab(i).responsibility_key || ' for User ' ||
v_resp_tab(i).user_name;
END;
--
END IF;
ELSE
IF nvl(v_resp_tab(i).error_flag, 'SUCCESS') != 'ERROR'
THEN
--
BEGIN
fnd_user_resp_groups_api.update_assignment(user_id => v_resp_tab(i).user_id,
responsibility_id => v_resp_tab(i).responsibility_id,
responsibility_application_id => v_resp_tab(i).application_id,
security_group_id => 0,
start_date => v_resp_tab(i).start_date,
end_date => v_resp_tab(i).end_date,
description => v_resp_tab(i).responsibility_key);
EXCEPTION
WHEN OTHERS THEN
v_resp_tab(i).error_flag := 'ERROR';
v_resp_tab(i).error_msg := SQLCODE || '-' || SQLERRM ||
'Issue while updating assignment ' ||
v_resp_tab(i)
.responsibility_key || ' for User ' ||
v_resp_tab(i).user_name;
END;
--
END IF;
--
END IF;
v_row_proc := v_row_proc + 1;
IF (MOD(v_row_proc, 100) = 0)
THEN
COMMIT;
END IF;
--
UPDATE xxxx_user_resp_tbl
SET error_flag = nvl(v_resp_tab(i).error_flag, 'SUCCESS'),
error_message = v_resp_tab(i).error_msg
WHERE username = v_resp_tab(i).user_name;
--
COMMIT;
--
END LOOP;
--
END LOOP;
--
COMMIT;
--
CLOSE c_resp_cursor;
--
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-21001, SQLCODE || '-' || SQLERRM);
END;
/
Migration Steps:
1. 1.Run the Script to create table ‘XXXX_USER_RESP_TBL.sql’
2. 2. HR_USER_RESP_ASSIGN.dat (File to load data
to table XXXX_USER_RESP_TBL)
Data File Name:- HR_USER_RESP_ASSIGN.dat Control File:- XXXX_USER_RESP_LOAD.ctl
Command: sqlldr apps/appspwd
control= XXXX_USER_RESP_LOAD.ctl data= HR_USER_RESP_ASSIGN.dat log=
HR_USER_RESP_ASSIGN_LOG.log bad= HR_USER_RESP_ASSIGN.bad
3. 3.After loading data into the table XXXX_USER_RESP_TBL,
Run the insert script ‘XXXX_USER_RESP_LOAD.sql’ to assign/deassign
responsibilities to user.
No comments:
Post a Comment