Script to create Responsibility using API

We can use below mentioned script to create responsibility from backend using API.


-->

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

    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;