We can use below mentioned script to create responsibility from backend using API.
-->
Loop
CREATE OR REPLACE procedure APPS.xxran_responsibility
as
v_rowid varchar2(500);
v_web_host_name varchar2(500);
v_web_agent_name varchar2(500);
v_version varchar2(500) := 4;
v_responsibility_id number; -- some variables
v_resp_name varchar2(100) ;
v_application varchar2(100);
v_resp_key varchar2(100);
v_menu_name varchar2(100) ;
v_data_group varchar2(100);
v_req_group varchar2(100) ;
-- ids and other **** used by the API
v_application_id number;
v_data_group_id number;
v_menu_id number;
v_request_group_id number;
lc_status varchar2(1);
lc_error_msg varchar2(240);
/*--Create Cursor table
CREATE TABLE APPS.XXRAN_RESPONSIBILITY_TAB
(
RESP_NAME VARCHAR2(100 BYTE),
APPLICATION VARCHAR2(50 BYTE),
RESP_KEY VARCHAR2(40 BYTE),
MENU_NAME VARCHAR2(60 BYTE),
DATA_GROUP VARCHAR2(40 BYTE),
REQ_GROUP VARCHAR2(50 BYTE),
STATUS VARCHAR2(1 BYTE),
ERROR_MSG VARCHAR2(240 BYTE)
)
*/
cursor cur is
select
resp_name,application,resp_key,
menu_name,data_group,req_group
from XXRAN_RESPONSIBILITY_TAB ;
begin
for c in cur
lc_status := 'Y';
begin
v_resp_name := c.resp_name;
v_resp_key := c.resp_key;
v_application := c.application;
v_data_group := c.resp_key;
v_menu_name := c.menu_name;
v_req_group := c.req_group;
v_data_group := c.data_group;
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_vl
where user_menu_name = v_menu_name;
-- get the request_group_id
if v_req_group is not null then
select request_group_id
into v_request_group_id
from fnd_request_groups
where request_group_name = v_req_group
and application_id = v_application_id;
End If;
-- get current responsibility_id
select FND_RESPONSIBILITY_S.NEXTVAL
into v_responsibility_id
from DUAL;
-- run API
Exception
When others then
dbms_output.put_line('failed to fetch ids' || sqlerrm);
lc_status := 'E';
End;
if lc_status = 'Y'then
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_char(sysdate,'DD-MON-YYYY'),
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
);
End If;
dbms_output.put_line(' added resp');
dbms_output.put_line('v_rowid '||v_rowid);
Exception
when others then
dbms_output.put_line('failed to add resp' || sqlerrm);
End;
End Loop ;
exception
when others then
dbms_output.put_line('failed to main ' || sqlerrm);
end;
very useful
ReplyDeleteThanks, Great work.
ReplyDelete