STEP1: TAKE THE BACKUP(.XLS) FROM SOURCE INSTANCE
---Take CAB responsibility Backup into excel sheet
SELECT fu.user_id,
fu.user_name c1_user_name,
fu.start_date user_start_date,
fu.end_date user_end_date,
fa.application_short_name c2_resp_app,
frv.responsibility_key c3_resp_key,
frv.responsibility_name c4_resp_desc ,
furgd.start_date c5_start_date,
furgd.end_date c6_end_date,
'STANDARD'c7_sec_group
FROM fnd_user fu,
fnd_user_resp_groups_direct furgd,
fnd_responsibility_vl frv,
fnd_application_vl fa
WHERE fu.user_id = furgd.user_id
AND furgd.responsibility_id = frv.responsibility_id
AND frv.application_id = fa.application_id
AND frv.responsibility_name IN
('EMR IT Sysadmin',
'EMR IT Profile Administrator',
'EMR DFF Segments Administrator',
'XML Publisher Administrator',
'EMR IT Development',
'EMR User Administrator'
)
AND ( furgd.end_date IS NULL
OR furgd.end_date > TO_DATE ('22-Sep-2015', 'DD-Mon-RRRR')
)
ORDER BY 1, 2
---Take User wise responsibility Backup into excel sheet
SELECT fu.user_id,
fu.user_name c1_user_name,
fu.start_date user_start_date,
fu.end_date user_end_date,
fa.application_short_name c2_resp_app,
frv.responsibility_key c3_resp_key,
frv.responsibility_name c4_resp_desc ,
furgd.start_date c5_start_date,
furgd.end_date c6_end_date,
'STANDARD'c7_sec_group
FROM fnd_user fu,
fnd_user_resp_groups_direct furgd,
fnd_responsibility_vl frv,
fnd_application_vl fa
WHERE fu.user_id = furgd.user_id
AND furgd.responsibility_id = frv.responsibility_id
AND frv.application_id = fa.application_id
AND fu.user_name IN
('AKSHATHA_NAYAK',---SLK USERS BACKUP
'ALOK_TIWARI',
'ANASURYA_K',
'ARATI_SWAIN',
'BHAVANI_VANTARI',
'CATHERINE_CHARLES',
'GAGANA_MS',
'GATTU_SAILAKSHMI',
'GOPI_DASARI',
'KEERTHI_YERRAMSETTI',
'KISHOR_PESHWE',
'LAVANYA_P3',
'LAVANYA_SETHOLA',
'MADHAVA_VEMURI',
'MAHADEVAIAH_KEMPASHETTY',
'MAHESWAR_ALAVALAPATI',
'NITHYA_DA',
'PREETI_WALI',
'RENUKA_PATIL',
'SAHITHI_BUDITHI',
'SANTOSHI_ANNARAO',
'SHRUTHI_KR',
'SHWETA_MADGUNI',
'SIRISHA_KM',
'SPOORTHI_G',
'VAISHNAVI_CJB',
'RAJA_THUPAKULA',
'ATHIRA_SAJAN',
'VIJAYAKUMARA_K',
'ASHWINI_BHAT',
'VANAMALA_BHARATHI',
'PRASANNA_GOTHAM',
'AKSHIT_CHAWLA', ---IAT TEAM BACKUP
'AMIT_SONI',
'ANU_BHATI',
'ARJUN_THAKUR',
'ASHUTOSH_KUMAR',
'DEWANSHU_GOEL',
'HARJOT_RANI',
'IAT_INTERNS1',
'IAT_INTERNS2',
'IAT_INTERNS3',
'IAT_INTERNS4',
'KAUR_JASPREET',
'KIRANDEEP_KAUR',
'MADHAVA_BANGARU',
'MAHADEVAN_PRANADHARTHI',
'MANOJ_JENA',
'NEERAJ_KUMAR2',
'NEERAJ_SINGLA',
'PANKAJ_SINGH',
'PRIVY_GERA',
'RAJKANNAN_PONNUCHAMY',
'RAJNEES_GUPTA',
'RATNAKAR_PANDURI',
'RISHABH_SACHDEVA',
'SREENU_ISUKAPALLI',
'ANCUTA_CIMPAN',---MMI USERS BACK
'CLARA_TYUKODI',
'ERWINVAN_DIJK',
'HENK_VAN-ESSEN',
'JASPER_KLUNDER',
'KNUD_BOUCHER',
'MARTA_FARAGO',
'MARTIN_COENRAAD',
'MICHIEL_RIDDERBOS',
'PETER_PELLENAARS',
'SACHIN_NADIGAR'
)
AND ( furgd.end_date IS NULL
OR furgd.end_date > TO_DATE ('22-Sep-2015', 'DD-Mon-RRRR')
)
ORDER BY 1,2
---Merge Both the sheets into once excel(CAB+Userwise)
STEP2: COVERT THE XLS FILE INTO CSV FORMAT
- Covert the XLS file into CSV file
- Open the CSV file as Notepad and Save it as Text format.
STEP3: CREATE THE TABLE IN TARGET INSTANCE
CREATE TABLE apps.XXFND_S0_CLONE_RESP_BKUP
(
c1_user_name VARCHAR2(100),
c2_resp_app VARCHAR2(100),
c3_resp_key VARCHAR2(100),
c4_resp_desc VARCHAR2(100),
c5_start_date DATE,
c6_end_date DATE,
c7_sec_group VARCHAR2(100)
);
STEP4: LOAD THE DATA INTO STAGING TABLE VIA TOAD
Navigation:
STEP5: RUN THE BELOW QUERY TO LOAD ALL THE USERWISE RESPONSIBILITIES
FROM THE STAGING TO STANDARD TABLES.
FROM THE STAGING TO STANDARD TABLES.
DECLARE
v_responsibility_name VARCHAR2 (100);
v_application_name VARCHAR2 (100);
v_responsibility_key VARCHAR2 (100);
v_security_group VARCHAR2 (100);
v_description VARCHAR2 (100);
CURSOR x_cur
IS
SELECT c1_user_name, c2_resp_app, c3_resp_key
FROM xxfnd_s0_clone_resp_bkup a, fnd_user b
WHERE a.c1_user_name = b.user_name;
BEGIN
FOR i IN x_cur
LOOP
fnd_user_pkg.addresp (username => i.c1_user_name,
resp_app => i.c2_resp_app,
resp_key => i.c3_resp_key,
security_group => 'STANDARD',
description => NULL,
start_date => SYSDATE,
end_date => NULL
);
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line ('Responsiblity is attached to the user successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error encountered while attaching responsibilty to the user and the error is '
|| SQLERRM
);
END;
-------------------------------OR------------------------------------------
DECLARE
v_responsibility_name VARCHAR2 (100);
v_application_name VARCHAR2 (100);
v_responsibility_key VARCHAR2 (100);
v_security_group VARCHAR2 (100);
v_description VARCHAR2 (100);
CURSOR x_cur
IS
SELECT c1_user_name, c2_resp_app, c3_resp_key
FROM xxfnd_s0_clone_resp_bkup a, fnd_user b, FND_RESPONSIBILITY c
WHERE a.c1_user_name = b.user_name
and a.c3_resp_key = c.responsibility_key
and a.c3_resp_key not like '%&%'
AND (C6_END_DATE is null or C6_END_DATE > sysdate);
BEGIN
FOR i IN x_cur
LOOP
fnd_user_pkg.addresp (username => i.c1_user_name,
resp_app => i.c2_resp_app,
resp_key => i.c3_resp_key,
security_group => 'STANDARD',
description => NULL,
start_date => SYSDATE,
end_date => NULL
);
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line ('Responsiblity is attached to the user successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error encountered while attaching responsibilty to the user and the error is '
|| SQLERRM
);
END;