Script to load data for Menu Exclusions while creation responsiblities

create or replace PROCEDURE CLC_RESP_MENU_EXCLU_PRG
(
errbuf                      OUT VARCHAR2
,retcode                   OUT NUMBER
)
as

l_application_id    fnd_responsibility_tl.application_id%type;
l_responsibility_id    fnd_responsibility_tl.responsibility_id%type;
l_action_id        FND_RESP_FUNCTIONS.ACTION_ID%type;
l_rule_type        FND_RESP_FUNCTIONS.rule_type%type;
l_rowid            varchar2(2000);
l_cnt            number;
l_conc_request_id    fnd_concurrent_requests.REQUEST_ID%type;
l_err_msg        varchar2(2000);   

CURSOR c_menu
IS
SELECT distinct RESPONSIBILITY_NAME,
EXCLU_TYPE,
EXCLU_NAME,
EXCLU_DESC
FROM CLC_RESP_MENU_EXCLU_LOAD
where PROCESS_FLAG='P';


Begin

FND_FILE.PUT_LINE(FND_FILE.LOG,'  Starting of CLC_RESP_MENU_EXCLU_PRG...');
--FND_FILE.PUT_LINE(FND_FILE.LOG,'  Initializing Apps');
--FND_GLOBAL.Apps_Initialize(FND_GLOBAL.USER_ID, FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID);

select fnd_global.conc_request_id into l_conc_request_id from dual;

for c_rec in c_menu loop
FND_FILE.PUT_LINE(FND_FILE.LOG,'Loop Start');

l_application_id    := null;   
l_responsibility_id    := null;   
l_action_id        := null;   
l_rule_type        := null;   
l_rowid            := null;   
l_cnt            := 0;
l_err_msg        := null;

select application_id,responsibility_id 
into l_application_id,l_responsibility_id
from fnd_RESPONSIBILITY_tl
where RESPONSIBILITY_NAME = c_rec.RESPONSIBILITY_NAME
and LANGUAGE =USERENV('LANG');

FND_FILE.PUT_LINE(FND_FILE.LOG,'application_id:' || l_application_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'responsibility_id:' || l_responsibility_id);

if c_rec.EXCLU_TYPE ='Function' then
l_rule_type :='F';
begin
SELECT FUNCTION_ID
into l_action_id
FROM FND_FORM_FUNCTIONS_VL  
WHERE USER_FUNCTION_NAME=c_rec.EXCLU_NAME;
exception
when too_many_rows then
l_err_msg :='too_many_rows in FND_FORM_FUNCTIONS_VL';
l_action_id :=null;
when others then
l_err_msg :='No_Data_Found in FND_FORM_FUNCTIONS_VL';
l_action_id :=null;
end;


elsif c_rec.EXCLU_TYPE ='Menu' then
l_rule_type :='M';
begin
select MENU_ID
into l_action_id
from FND_MENUS_VL
where USER_MENU_NAME=c_rec.EXCLU_NAME;
exception
when too_many_rows then
l_err_msg :='too_many_rows exist in FND_MENUS_VL';
l_action_id :=null;
when others then
l_err_msg :='No_Data_Found in FND_MENUS_VL';
l_action_id :=null;
end;

end if;

FND_FILE.PUT_LINE(FND_FILE.LOG,'action_id:' || l_action_id);
begin
select count(*)
into l_cnt
from FND_RESP_FUNCTIONS
where RESPONSIBILITY_ID=l_responsibility_id
and APPLICATION_ID =l_application_id
and RULE_TYPE =l_rule_type
and ACTION_ID =l_action_id;

exception when others then
l_cnt :=0;
end;

FND_FILE.PUT_LINE(FND_FILE.LOG,'l_cnt:' || l_cnt);


if l_application_id is not null and l_responsibility_id is not null and l_rule_type is not null and  l_action_id is not null and l_cnt < 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'calling FND_RESP_FUNCTIONS_PKG.INSERT_ROW' );
APPS.FND_RESP_FUNCTIONS_PKG.INSERT_ROW
(l_rowid            --X_ROWID in out nocopy VARCHAR2,
 ,l_application_id    --X_APPLICATION_ID in NUMBER,
 ,l_responsibility_id    --X_RESPONSIBILITY_ID in NUMBER,
 ,l_action_id        --X_ACTION_ID in NUMBER,
 ,l_rule_type        --X_RULE_TYPE in VARCHAR2,
 ,FND_GLOBAL.USER_ID    --X_CREATED_BY in NUMBER,
 ,sysdate        --X_CREATION_DATE in DATE,
 ,FND_GLOBAL.USER_ID    --X_LAST_UPDATED_BY in NUMBER,
 ,sysdate        --X_LAST_UPDATE_DATE in DATE,
 ,FND_GLOBAL.USER_ID    --X_LAST_UPDATE_LOGIN in NUMBER)
);
end if;


if l_rowid is null and l_cnt >= 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update1' );
update CLC_RESP_MENU_EXCLU_LOAD
set PROCESS_FLAG='E',
ERROR_MESSAGE ='Data_Already_Exists',
REQUEST_ID=l_conc_request_id
where RESPONSIBILITY_NAME = c_rec.RESPONSIBILITY_NAME
and EXCLU_NAME =c_rec.EXCLU_NAME;

elsif l_rowid is null and l_cnt < 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update2' );
update CLC_RESP_MENU_EXCLU_LOAD
set PROCESS_FLAG='E',
ERROR_MESSAGE =l_err_msg,
REQUEST_ID=l_conc_request_id
where RESPONSIBILITY_NAME = c_rec.RESPONSIBILITY_NAME
and EXCLU_NAME =c_rec.EXCLU_NAME;

elsif l_rowid is not null and l_cnt < 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update3' );
update CLC_RESP_MENU_EXCLU_LOAD
set PROCESS_FLAG='S',
REQUEST_ID=l_conc_request_id
where RESPONSIBILITY_NAME = c_rec.RESPONSIBILITY_NAME
and EXCLU_NAME =c_rec.EXCLU_NAME;
end if;
commit;
FND_FILE.PUT_LINE(FND_FILE.LOG,'End loop' );

end loop;
FND_FILE.PUT_LINE(FND_FILE.LOG,'End Program' );

EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,' OTHER exception ');
end CLC_RESP_MENU_EXCLU_PRG;
/