补充一点:
--create a new user with system admin access
DECLARE
v_session_id INTEGER := USERENV( 'sessionid' );
v_user_name VARCHAR2( 30 ) := UPPER( '&Enter_User_Name');
v_employee_id VARCHAR2( 30 );
CURSOR resp IS
SELECT A.APPLICATION_SHORT_NAME,
FR.responsibility_key,
FR.DESCRIPTION
FROM FND_APPLICATION_VL A,
FND_RESPONSIBILITY_VL FR,
--FND_MENU_ENTRIES_VL FM,
FND_USER_RESP_GROUPS FURG,
FND_USER FU
WHERE A.APPLICATION_ID = FR.APPLICATION_ID
--AND FM.MENU_ID = FR.MENU_ID
AND FR.RESPONSIBILITY_ID=FURG.RESPONSIBILITY_ID
AND FURG.USER_ID = FU.USER_ID
--AND FM.GRANT_FLAG ='Y'
AND (FU.END_DATE IS NULL OR FU.END_DATE >SYSDATE)
AND FR.END_DATE IS NULL
AND (FURG.END_DATE IS NULL OR FURG.END_DATE >SYSDATE)
--AND SUBSTR(FR.RESPONSIBILITY_NAME,1,3) IN ('AF1','ALF','SOF')
AND USER_NAME = 'MFG-LXR';
BEGIN
--Note, can be executed only when you have apps password.
-- Call the procedure to Creaet FND User
fnd_user_pkg.createuser
( x_user_name => v_user_name,
x_owner => '',
x_unencrypted_password => '123456',
x_session_number => v_session_id,
x_start_date => SYSDATE,
--x_end_date => SYSDATE + 100,
x_last_logon_date => SYSDATE,
x_description => :USER_DESC,
x_password_date => SYSDATE + 365 ,
x_password_accesses_left => 30,
x_password_lifespan_accesses => 0,
x_password_lifespan_days => 30,
--x_employee_id => v_employee_id,
/*Change this id by running below SQL*/
/*
SELECT person_id
,full_name
FROM per_all_people_f
WHERE upper(full_name) LIKE '%' || upper('full_name') || '%'
GROUP BY person_id
,full_name
*/
x_email_address => '',
x_fax => '',
x_customer_id => '',
x_supplier_id => '' );
FOR c INresp LOOP
fnd_user_pkg.addresp( username => v_user_name,
resp_app => c.APPLICATION_SHORT_NAME,
resp_key => c.responsibility_key,
security_group => 'STANDARD',
/*
SELECT fa.application_short_name resp_app,fa.basepath,fa.*
FROM apps.fnd_application fa
WHERE application_short_name = 'SYSADMIN';
SELECT r.responsibility_key resp_key, r.responsibility_id,
r.request_group_id
FROM apps.fnd_responsibility r
WHERE r.responsibility_key = 'SYSTEM_ADMINISTRATOR';
SELECT fsg.security_group_id secid, fsg.*
FROM fnd_security_groups fsg;
*/
description => c.description,
start_date => SYSDATE ,
end_date => SYSDATE + 365);
END LOOP;
COMMIT;
END;
|