Adding USER in EBS Instance from Backend
++++++++++++++++++++++++++++++++++++++++++
set serveroutput on
DECLARE
v_password VARCHAR2(30):='Equifax@123';
v_username VARCHAR2(100);
v_session_id INTEGER := USERENV('sessionid');
CURSOR C1 IS
SELECT name from
(SELECT 'MURALI.M' NAME FROM DUAL UNION ALL
SELECT 'PAVI.S' FROM DUAL UNION ALL
SELECT 'ROHAN.VYAS' FROM DUAL UNION ALL
SELECT 'SAILIK.SEN' FROM DUAL UNION ALL
SELECT 'SUDEEPTO.DAS' FROM DUAL UNION ALL
SELECT 'TANMAYEE.HALDER' FROM DUAL UNION ALL
SELECT 'TANVI.SINGH' FROM DUAL UNION ALL
SELECT 'VIDHYA.KULANTHAIVELU' FROM DUAL) where name NOT IN (SELECT user_name from FND_USER);
BEGIN
FOR r1 IN C1 LOOP
fnd_user_pkg.createuser (
x_user_name => r1.name,
x_owner => NULL,
x_unencrypted_password => v_password,
x_session_number => v_session_id,
x_start_date => SYSDATE,
x_end_date => NULL
);
COMMIT;
DBMS_OUTPUT.put_line ('User: '||r1.name||' Created Successfully');
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
ROLLBACK;
END;
_________________________________________________________________________________
Adding Responsibility to USER in EBS Instance from Backend
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
set serveroutput on
DECLARE
v_count NUMBER;
CURSOR C1 IS
SELECT 'MURALI.M' NAME FROM DUAL UNION ALL
SELECT 'PAVI.S' FROM DUAL UNION ALL
SELECT 'ROHAN.VYAS' FROM DUAL UNION ALL
SELECT 'ROMIT.VERMA' FROM DUAL UNION ALL
SELECT 'SAILIK.SEN' FROM DUAL UNION ALL
SELECT 'SUDEEPTO.DAS' FROM DUAL UNION ALL
SELECT 'TANMAYEE.HALDER' FROM DUAL UNION ALL
SELECT 'TANVI.SINGH' FROM DUAL UNION ALL
SELECT 'VIDHYA.KULANTHAIVELU' FROM DUAL;
BEGIN
FOR r1 IN C1 LOOP
SELECT count(1)
INTO v_count
FROM fnd_user fu
, fnd_user_resp_groups fur
, fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND trunc(SYSDATE) BETWEEN trunc(fr.start_date) AND trunc(nvl( (fr.end_date - 1),SYSDATE) )
AND trunc(SYSDATE) BETWEEN trunc(fur.start_date) AND trunc(nvl( (fur.end_date - 1),SYSDATE) )
AND user_name = r1.name
AND fr.responsibility_name = 'System Administrator';
dbms_output.put_line('Username : '||r1.name);
IF v_count = 0 THEN
fnd_user_pkg.addresp (r1.name,'SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD','Add Responsibility to USER using pl/sql',SYSDATE,SYSDATE + 100);
commit;
dbms_output.put_line('Responsibility Added Successfully to '||r1.name);
END IF;
END LOOP;
EXCEPTION
WHEN others THEN
dbms_output.put_line(' Responsibility is not added due to ' || SQLCODE || substr(SQLERRM, 1, 100));
ROLLBACK;
END;
