blog-1-script-to-create-responsibility-using-api
Query:
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
Loop
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;
