CREATE OR REPLACE PACKAGE user_menu_resp
AS
PROCEDURE createuser (v_user_name VARCHAR2, v_password VARCHAR2);
PROCEDURE createresp;
PROCEDURE addresp (v_user_name VARCHAR2,
v_responsibility_name VARCHAR2,
v_application_name VARCHAR2,
v_responsibility_key VARCHAR2,
v_security_group VARCHAR2,
v_description VARCHAR2);
PROCEDURE updateuser (v_user_name VARCHAR2, v_start_date DATE);
PROCEDURE createmenu;
END;
CREATE OR REPLACE PACKAGE BODY user_menu_resp
IS
--Create Newuser
PROCEDURE createuser (v_user_name VARCHAR2, v_password VARCHAR2)
IS
vl_user_name VARCHAR2 (30) := v_user_name;
vl_password VARCHAR2 (30) := v_password;
BEGIN
fnd_user_pkg.
createuser (
x_user_name => vl_user_name,
x_unencrypted_password => vl_password,
x_owner => NULL,
x_start_date => TO_DATE ('8/2/2014', 'mm/dd/yyyy'));
COMMIT;
DBMS_OUTPUT.put_line ('user:' || v_user_name || 'created successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
put_line (
'unable to create user due to'
|| SQLCODE
|| ' '
|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
--Create Responsibility
PROCEDURE createresp
IS
v_rowid VARCHAR2 (500);
v_web_host_name VARCHAR2 (500);
v_web_agent_name VARCHAR2 (500);
v_version VARCHAR2 (500);
v_responsibility_id NUMBER;
-- some variables
v_resp_name VARCHAR2 (100) := 'Apps Developer Info';
v_application VARCHAR2 (100) := 'XML Publisher';
v_resp_key VARCHAR2 (100) := 'Apps Developer';
v_menu_name VARCHAR2 (100) := 'GL_SUPERUSER';
v_data_group VARCHAR2 (100) := 'Standard';
v_req_group VARCHAR2 (100) := 'XML Publisher Request Set';
-- ids and other crap used by the API
v_application_id NUMBER;
v_data_group_id NUMBER;
v_menu_id NUMBER;
v_request_group_id NUMBER;
BEGIN
-- get application_id
SELECT application_id
INTO v_application_id
FROM fnd_application_vl
WHERE application_name = v_application;
-- get data group id
SELECT data_group_id
INTO v_data_group_id
FROM fnd_data_groups
WHERE data_group_name = v_data_group;
-- get the menu_id
SELECT menu_id
INTO v_menu_id
FROM fnd_menus
WHERE menu_name = v_menu_name;
-- get the request_group_id
SELECT request_group_id
INTO v_request_group_id
FROM fnd_request_groups
WHERE request_group_name = v_req_group;
-- get current responsibility_id
SELECT FND_RESPONSIBILITY_S.NEXTVAL INTO v_responsibility_id FROM DUAL;
-- run API
fnd_responsibility_pkg.
insert_row ( -- out params
x_rowid => v_rowid,
-- in params
x_responsibility_id => v_responsibility_id,
x_application_id => v_application_id,
x_web_host_name => v_web_host_name,
x_web_agent_name => v_web_agent_name,
x_data_group_application_id => v_application_id,
x_data_group_id => v_data_group_id,
x_menu_id => v_menu_id,
x_start_date => TO_DATE ('01-JAN-1951'),
x_end_date => NULL,
x_group_application_id => v_application_id,
x_request_group_id => v_request_group_id,
x_version => v_version,
x_responsibility_key => v_resp_key,
x_responsibility_name => v_resp_name,
x_description => ”,
x_creation_date => SYSDATE,
x_created_by => -1,
x_last_update_date => SYSDATE,
x_last_updated_by => -1,
x_last_update_login => 0);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('failed to add resp' || SQLERRM);
END;
--Add Responsibility To user
PROCEDURE addresp (v_user_name VARCHAR2,
v_responsibility_name VARCHAR2,
v_application_name VARCHAR2,
v_responsibility_key VARCHAR2,
v_security_group VARCHAR2,
v_description VARCHAR2)
IS
vl_user_name VARCHAR2 (30) := v_user_name;
vl_responsibility_name VARCHAR2 (30) := v_responsibility_name;
vl_application_name VARCHAR2 (30) := v_application_name;
vl_responsibility_key VARCHAR2 (30) := v_responsibility_key;
vl_security_group VARCHAR2 (30) := v_security_group;
vl_description VARCHAR2 (30) := v_description;
BEGIN
SELECT fa.application_short_name,
fr.responsibility_key,
fsg.security_group_key,
frt.description
INTO vl_application_name,
vl_responsibility_key,
vl_security_group,
vl_description
FROM apps.fnd_responsibility fr,
fnd_application fa,
fnd_Security_groups fsg,
fnd_responsibility_tl frt
WHERE frt.responsibility_name = vl_responsibility_name
AND frt.language = USERENV ('lang')
AND frt.responsibility_id = fr.responsibility_id
AND fr.application_id = fa.application_id
AND fr.data_group_id = fsg.security_group_id;
fnd_user_pkg.
addresp (username => vl_user_name,
resp_app => vl_application_name,
resp_key => vl_responsibility_key,
security_group => vl_security_group,
description => vl_description,
start_date => TO_DATE ('8/2/2012', 'mm/dd/yyyy'),
end_date => NULL);
COMMIT;
DBMS_OUTPUT.
put_line (
'responsibility'
|| vl_responsibility_name
|| 'is attached to the user'
|| vl_user_name
|| ' successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
put_line (
'unable to create user due to'
|| SQLCODE
|| ' '
|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
--Update User Infomation
PROCEDURE updateuser (v_user_name VARCHAR2, v_start_date DATE)
IS
vl_user_name VARCHAR2 (30) := v_user_name;
vl_start_date DATE := v_start_date;
BEGIN
fnd_user_pkg.updateuser (x_user_name => vl_user_name,
x_owner => NULL,
x_end_date => NULL,
x_start_date => vl_start_date);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
put_line (
'unable to create password due to'
|| SQLCODE
|| ' '
|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
--Create Menu
PROCEDURE createmenu
IS
v_rowid VARCHAR2 (20);
v_menu_id NUMBER (20);
v_menu_name VARCHAR2 (200) := 'first tax account Menu';
v_function_name VARCHAR2 (200) := 'ZX_TAX_ACCOUNT_VIEW';
vl_menu_id VARCHAR2 (20);
BEGIN
SELECT FND_MENUS_S.NEXTVAL INTO vl_menu_id FROM DUAL;
fnd_menus_pkg.INSERT_ROW (x_rowid => v_rowid,
x_menu_id => vl_menu_id,
x_menu_name => v_menu_name,
x_user_menu_name => v_menu_name,
x_menu_type => NULL,
x_description => NULL,
x_creation_Date => SYSDATE,
x_created_by => fnd_global.user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('failed to create menu' || SQLERRM);
END;
END user_menu_resp;
No comments:
Post a Comment