[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into l_security_group_count
from fnd_security_groups
where security_group_id >= 0;
select prompt,
description,
sub_menu_id,
entry_sequence
from fnd_menu_entries_vl
where menu_id = p_object.parent_menu_id
and sub_menu_id is not null
and prompt is not null
order by entry_sequence;
select prompt, description, sub_menu_id, entry_sequence
from fnd_menu_entries_vl
where menu_id = p_object.parent_menu_id
and sub_menu_id is not null
order by entry_sequence;
select b.prompt prompt,
nvl(nvl(b.description,a.description),b.prompt) description,
b.function_id,
b.entry_sequence,
a.type,
a.web_html_call
from fnd_form_functions_vl a,
fnd_menu_entries_vl b
where b.menu_id = p_object.parent_menu_id
and a.function_id = b.function_id
--jprabhud - 03/04/03 - Refresh Portal Page - Remove the filter based on the type
--and a.type in (lWww ,lWwk)
--nbarik - 04/15/03 - Bug Fix 2893197 - Don't show Web Portlet and DB Portlet as links
AND a.type NOT IN ('WEBPORTLET', 'DBPORTLET')
-- jprabhud 05/18/05 - addded decode condition
and decode(a.web_html_call,null,'NULL',lower(a.web_html_call)) not like lHtmlCall --Fix for 2606104
AND b.prompt IS NOT NULL --Bug Fix 2664392 : Don't show the function , if prompt is null
order by entry_sequence;
select nvl(b.prompt,b.description) prompt,
nvl(nvl(b.description,a.description),b.prompt) description,
b.function_id,
b.entry_sequence,
a.type,
a.web_html_call
from fnd_form_functions_vl a,
fnd_menu_entries_vl b
where b.menu_id = p_object.parent_menu_id
and a.function_id = b.function_id
and a.type in ('WEBPORTLET')
and a.web_html_call like '%BIS_PM_RELATED_LINK_LAYOUT%'
order by entry_sequence;
select count(*)
into l_count
from fnd_menu_entries
where menu_id = p_object.parent_menu_id
and sub_menu_id is not null;
select count(*)
into l_count
from fnd_form_functions a,
fnd_menu_entries b
where b.menu_id = p_object.parent_menu_id
and a.function_id = b.function_id
--jprabhud - 03/04/03 - Refresh Portal Page - Remove the filter based on the type
--and a.type in ('WWW','WWK')
--nbarik - 04/15/03 - Bug Fix 2893197 - Don't show Web Portlet and DB Portlet as links
AND a.type NOT IN ('WEBPORTLET', 'DBPORTLET')
-- jprabhud 05/18/05 - added decode condition
and decode(a.web_html_call,null,'NULL',lower(a.web_html_call)) not like '%window.open%';
select count(*)
into l_rl_portlet_count
from fnd_form_functions_vl a,
fnd_menu_entries_vl b
where b.menu_id = p_object.parent_menu_id
and a.function_id = b.function_id
and a.type in ('WEBPORTLET')
and a.web_html_call like '%BIS_PM_RELATED_LINK_LAYOUT%';
select prompt,
description,
sub_menu_id
into l_prompt,
l_description,
l_sub_menu_id
from fnd_menu_entries_vl
where menu_id = p_object.parent_menu_id
and entry_sequence = p_object.entry_sequence
order by entry_sequence;
select responsibility_name,
description,
menu_id
into l_responsibility_name,
l_description,
l_menu_id
from fnd_responsibility_vl
where responsibility_id = p_object.responsibility_id
and application_id = p_object.resp_appl_id
-- remove version check to show all resp
--and version = 'W'
and start_date <= sysdate
and (end_date is null or end_date > sysdate);
var temp=parent.opener.parent.document.Favorites.C.selectedIndex;
var temp=parent.opener.parent.document.Favorites.C.selectedIndex;
FUNCTION insert_bis_custom_related_link (
pdisplay_sequence In NUMBER,
puser_link_name In VARCHAR2,
pfunction_id IN NUMBER,
presponsibility_id In NUMBER,
psecurity_group_id In NUMBER,
presponsibility_application_id In NUMBER,
plinked_function_id In VARCHAR2,
plink_type IN VARCHAR2,
puser_url IN VARCHAR2,
plevel_site_id in NUMBER,
plevel_responsibility_id in NUMBER,
plevel_application_id in NUMBER,
plevel_org_id in NUMBER,
plevel_function_id in NUMBER,
plevel_user_id In NUMBER,
pcreated_by IN NUMBER,
pcreation_date IN DATE DEFAULT SYSDATE,
plast_update_date In DATE DEFAULT SYSDATE,
plast_updated_by IN NUMBER,
plast_update_login In NUMBER
) RETURN NUMBER
IS
l_related_link_id number;
select bis_related_links_s.nextval into l_related_link_id from dual;
insert into bis_custom_related_links
(related_link_id,
display_sequence,
function_id,
responsibility_id,
security_group_id,
responsibility_application_id,
linked_function_id,
link_type,
user_url,
level_site_id,
level_responsibility_id,
level_application_id,
level_org_id,
level_function_id,
level_user_id,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
l_related_link_id,
pdisplay_sequence,
pfunction_id,
presponsibility_id,
psecurity_group_id,
presponsibility_application_id,
plinked_function_id,
plink_type,
puser_url,
plevel_site_id,
plevel_responsibility_id,
plevel_application_id,
plevel_org_id,
plevel_function_id,
plevel_user_id,
pcreated_by,
pcreation_date,
plast_update_date,
plast_updated_by,
plast_update_login
);
INSERT INTO bis_custom_related_links_tl
( related_link_id,
user_link_name,
language,
source_lang,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
select
l_related_link_id,
puser_link_name,
language_code,
userenv('LANG'),
pcreated_by,
pcreation_date,
plast_update_date,
plast_updated_by,
plast_update_login
from fnd_languages l
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select null
from bis_custom_related_links_tl
where related_link_id = l_related_link_id
and language = l.language_code);
END insert_bis_custom_related_link;
FUNCTION insert_bis_related_link (
pUser_id In NUMBER,
pdisplay_sequence In NUMBER,
puser_link_name In VARCHAR2,
pfunction_id IN NUMBER,
presponsibility_id In NUMBER,
psecurity_group_id In NUMBER,
presponsibility_application_id In NUMBER,
plinked_function_id In VARCHAR2,
plink_type IN VARCHAR2,
puser_url IN VARCHAR2,
pcreated_by IN NUMBER,
pcreation_date IN DATE DEFAULT SYSDATE,
plast_update_date In DATE DEFAULT SYSDATE,
plast_updated_by IN NUMBER,
plast_update_login In NUMBER
) RETURN NUMBER
IS
l_related_link_id number;
select bis_related_links_s.nextval into l_related_link_id from dual;
insert into bis_related_links
(related_link_id,
user_id,
display_sequence,
-- user_link_name,
function_id,
responsibility_id,
security_group_id,
responsibility_application_id,
linked_function_id,
link_type,
user_url,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
l_related_link_id,
puser_id,
pdisplay_sequence,
-- puser_link_name,
pfunction_id,
presponsibility_id,
psecurity_group_id,
presponsibility_application_id,
plinked_function_id,
plink_type,
puser_url,
pcreated_by,
pcreation_date,
plast_update_date,
plast_updated_by,
plast_update_login
);
INSERT INTO bis_related_links_tl
( related_link_id,
user_link_name,
language,
source_lang,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
select
l_related_link_id,
puser_link_name,
language_code,
userenv('LANG'),
pcreated_by,
pcreation_date,
plast_update_date,
plast_updated_by,
plast_update_login
from fnd_languages l
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select null
from bis_related_links_tl
where related_link_id = l_related_link_id
and language = l.language_code);
END insert_bis_related_link;
select function_id into l_function_id
from fnd_form_functions
where function_name = pFunction_name;
SELECT parameters INTO l_param
FROM fnd_form_functions
WHERE function_id = pFunctionId;
SELECT function_id INTO l_func_id
FROM fnd_form_functions
WHERE function_name = l_retr_func;
SELECT max(NVL(display_sequence, 0)) + 1
FROM bis_custom_related_links_v
WHERE level_site_id = p_site_id
AND function_id = p_function_id;
SELECT max(NVL(display_sequence, 0)) + 1
FROM bis_custom_related_links_v
WHERE level_responsibility_id = p_resp_id
AND function_id = p_function_id;
SELECT max(NVL(display_sequence, 0)) + 1
FROM bis_custom_related_links_v
WHERE level_application_id = p_app_id
AND function_id = p_function_id;
SELECT max(NVL(display_sequence, 0)) + 1
FROM bis_custom_related_links_v
WHERE level_org_id = p_org_id
AND function_id = p_function_id;
SELECT max(NVL(display_sequence, 0)) + 1
FROM bis_custom_related_links_v
WHERE level_function_id = p_level_function_id
AND function_id = p_function_id;
SELECT max(NVL(display_sequence, 0)) + 1
FROM bis_custom_related_links_v
WHERE level_user_id = p_user_id
AND function_id = p_function_id;
SELECT max(NVL(display_sequence, 0)) + 1
FROM bis_related_links_v
WHERE function_id IN (p_function_id, p_function_id2);
SELECT application_id
FROM fnd_responsibility
WHERE responsibility_id = p_resp_id;
SELECT b.responsibility_application_id,
b.security_group_id
INTO l_resp_appl_id,
l_security_group_id
FROM fnd_responsibility_vl a,
FND_USER_RESP_GROUPS b
WHERE a.responsibility_id = p_resp_id
AND b.user_id = p_user_id
AND b.RESPONSIBILITY_id = a.responsibility_id
AND b.RESPONSIBILITY_application_id = a.application_id;
l_related_link_id := INSERT_BIS_CUSTOM_RELATED_LINK (l_index, p_user_link_name, p_function_id,
l_resp_id, l_security_group_id, l_resp_appl_id,
p_linked_function_id, p_link_type, p_url,
l_level_site_id, l_level_resp_id, l_level_app_id, l_level_org_id, l_level_function_id, l_level_user_id,
-1,sysdate, sysdate, -1, -1);
l_related_link_id := INSERT_BIS_RELATED_LINK (p_user_id, l_index, p_user_link_name, p_function_id,
l_resp_id, l_security_group_id, l_resp_appl_id,
p_linked_function_id, p_link_type, p_url, -1,
sysdate, sysdate, -1, -1);
select LINK_PARAMETERS
from BIS_RELATED_LINKS
where RELATED_LINK_ID = p_related_link_id;
CURSOR updatePreseed is
select LINKED_FUNCTION_ID from bis_related_links
where RELATED_LINK_ID = p_related_link_id;
if updatePreseed%ISOPEN then
close updatePreseed;
open updatePreseed;
fetch updatePreseed into l_linked_function_id;
close updatePreseed;
select max(NVL(display_sequence,0)) + 1 into l_index
from bis_custom_related_links_v
where level_user_id = p_user_id
and function_id = p_function_id;
select b.responsibility_application_id,
b.security_group_id
into l_resp_appl_id,
l_security_group_id
from fnd_responsibility_vl a,
FND_USER_RESP_GROUPS b
where a.responsibility_id = p_resp_id
and b.user_id = p_user_id
and b.RESPONSIBILITY_id = a.responsibility_id
and b.RESPONSIBILITY_application_id = a.application_id;
--insert a new row into the bis_related_links_table.
l_related_link_id := insert_bis_related_link (p_user_id,
l_index, p_user_link_name, p_function_id,
p_resp_id, l_security_group_id,l_resp_appl_id,
l_linked_function_id, p_link_type, p_url,
-1, sysdate, sysdate, -1, -1);
update BIS_RELATED_LINKS set LINK_PARAMETERS=l_link_parameters where RELATED_LINK_ID=p_related_link_id;
select a.responsibility_id,
a.responsibility_name,
b.responsibility_application_id,
b.security_group_id,
fsg.SECURITY_GROUP_NAME
from FND_SECURITY_GROUPS_VL fsg,
fnd_responsibility_vl a,
FND_USER_RESP_GROUPS b
where b.user_id = p_user_id
and b.start_date <= sysdate
and (b.end_date is null or b.end_date > sysdate)
and b.RESPONSIBILITY_id = a.responsibility_id
and b.RESPONSIBILITY_application_id = a.application_id
and a.version = 'W'
and a.start_date <= sysdate
and (a.end_date is null or a.end_date > sysdate)
and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID
order by responsibility_name;
p_resp_sql := 'select responsibility_id, responsibility_name from fnd_responsibility_vl where responsibility_id in ('
|| l_resp_criteria || ') order by responsibility_name ';
p_resp_sql := 'select a.responsibility_id, a.responsibility_name from fnd_responsibility_vl a, fnd_user_resp_groups b
where a.responsibility_id in ('|| l_resp_criteria || ')
and b.user_id = '|| p_user_id ||' and b.RESPONSIBILITY_id = a.responsibility_id and b.responsibility_application_id = a.application_id order by responsibility_name ';
procedure deleteRelatedLink_Wrapper(p_related_link_id pls_integer, p_isPreseed IN VARCHAR2) is
begin
if (p_isPreseed = 'N') then
delete from bis_custom_related_links where related_link_id = p_related_link_id;
delete from bis_related_links where related_link_id = p_related_link_id;
end deleteRelatedLink_Wrapper;
PROCEDURE UPDATERELATEDLINK_WRAPPER(
p_related_link_id IN PLS_INTEGER,
p_related_link_name IN VARCHAR2,
p_user_url IN VARCHAR2 DEFAULT NULL,
p_isPreseed IN VARCHAR2)
IS
BEGIN
if (p_isPreseed = 'N') then
IF (p_user_url = null) THEN
UPDATE BIS_CUSTOM_RELATED_LINKS_TL SET USER_LINK_NAME=p_related_link_name
where RELATED_LINK_ID=p_related_link_id and language=userenv('LANG');
UPDATE BIS_CUSTOM_RELATED_LINKS SET USER_URL=p_user_url WHERE RELATED_LINK_ID=p_related_link_id;
UPDATE BIS_CUSTOM_RELATED_LINKS_TL SET USER_LINK_NAME=p_related_link_name
WHERE RELATED_LINK_ID=p_related_link_id and language=userenv('LANG');
UPDATE BIS_RELATED_LINKS_TL SET USER_LINK_NAME=p_related_link_name
where RELATED_LINK_ID=p_related_link_id and language=userenv('LANG');
UPDATE BIS_RELATED_LINKS SET USER_URL=p_user_url WHERE RELATED_LINK_ID=p_related_link_id;
UPDATE BIS_RELATED_LINKS_TL SET USER_LINK_NAME=p_related_link_name
WHERE RELATED_LINK_ID=p_related_link_id and language=userenv('LANG');
END UPDATERELATEDLINK_WRAPPER;
update bis_custom_related_links set DISPLAY_SEQUENCE=l_display_sequence where RELATED_LINK_ID=l_id;
update bis_related_links set DISPLAY_SEQUENCE=l_display_sequence where RELATED_LINK_ID=l_id;
SELECT l.display_sequence,l.user_link_name, l.function_id, l.responsibility_id, l.security_group_id,
l.responsibility_application_id,l.linked_function_id, l.link_type,l.user_url
FROM bis_related_links_v l
WHERE l.function_id = p_report_function_id;
SELECT l.display_sequence,l.user_link_name, l.function_id, l.responsibility_id, l.security_group_id,
l.responsibility_application_id,l.linked_function_id, l.link_type,l.user_url
FROM bis_custom_related_links_v l
WHERE l.function_id = p_report_function_id
AND l.level_site_id is not null;
SELECT l.display_sequence,l.user_link_name, l.function_id, l.responsibility_id, l.security_group_id,
l.responsibility_application_id,l.linked_function_id, l.link_type,l.user_url
FROM bis_custom_related_links_v l
WHERE l.function_id = p_report_function_id
AND l.level_responsibility_id is not null;
SELECT l.display_sequence,l.user_link_name, l.function_id, l.responsibility_id, l.security_group_id,
l.responsibility_application_id,l.linked_function_id, l.link_type,l.user_url
FROM bis_custom_related_links_v l
WHERE l.function_id = p_report_function_id
AND l.level_application_id is not null;
SELECT l.display_sequence,l.user_link_name, l.function_id, l.responsibility_id, l.security_group_id,
l.responsibility_application_id,l.linked_function_id, l.link_type,l.user_url
FROM bis_custom_related_links_v l
WHERE l.function_id = p_report_function_id
AND l.level_org_id is not null;
SELECT l.display_sequence,l.user_link_name, l.function_id, l.responsibility_id, l.security_group_id,
l.responsibility_application_id,l.linked_function_id, l.link_type,l.user_url
FROM bis_custom_related_links_v l
WHERE l.function_id = p_report_function_id
AND l.level_function_id is not null;
select bis_related_links_s.nextval into l_related_link_id from dual;
insertCustomLinks( l_related_link_id, l.display_sequence, l_function_id, l.responsibility_id, l.security_group_id,
l.responsibility_application_id, l.linked_function_id, l.link_type, l.user_url, l.user_link_name,
l_level_site_id, l_level_resp_id, l_level_app_id, l_level_org_id, l_level_function_id, l_level_user_id, p_user_id);
select bis_related_links_s.nextval into l_related_link_id from dual;
insertCustomLinks( l_related_link_id, l.display_sequence, l_function_id, l.responsibility_id, l.security_group_id,
l.responsibility_application_id, l.linked_function_id, l.link_type, l.user_url, l.user_link_name,
l_level_site_id, l_level_resp_id, l_level_app_id, l_level_org_id, l_level_function_id, l_level_user_id, p_user_id);
select bis_related_links_s.nextval into l_related_link_id from dual;
insertCustomLinks( l_related_link_id, l.display_sequence, l_function_id, l.responsibility_id, l.security_group_id,
l.responsibility_application_id, l.linked_function_id, l.link_type, l.user_url, l.user_link_name,
l_level_site_id, l_level_resp_id, l_level_app_id, l_level_org_id, l_level_function_id, l_level_user_id, p_user_id);
select bis_related_links_s.nextval into l_related_link_id from dual;
insertCustomLinks( l_related_link_id, l.display_sequence, l_function_id, l.responsibility_id, l.security_group_id,
l.responsibility_application_id, l.linked_function_id, l.link_type, l.user_url, l.user_link_name,
l_level_site_id, l_level_resp_id, l_level_app_id, l_level_org_id, l_level_function_id, l_level_user_id, p_user_id);
select bis_related_links_s.nextval into l_related_link_id from dual;
insertCustomLinks( l_related_link_id, l.display_sequence, l_function_id, l.responsibility_id, l.security_group_id,
l.responsibility_application_id, l.linked_function_id, l.link_type, l.user_url, l.user_link_name,
l_level_site_id, l_level_resp_id, l_level_app_id, l_level_org_id, l_level_function_id, l_level_user_id, p_user_id);
select bis_related_links_s.nextval into l_related_link_id from dual;
insertCustomLinks( l_related_link_id, l.display_sequence, l_function_id, l.responsibility_id, l.security_group_id,
l.responsibility_application_id, l.linked_function_id, l.link_type,l.user_url,l.user_link_name,l_level_site_id,
l_level_resp_id, l_level_app_id, l_level_org_id, l_level_function_id, l_level_user_id, p_user_id);
PROCEDURE insertCustomLinks(
p_related_link_id in number,
p_display_sequence in number,
p_function_id in number,
p_responsibility_id in number,
p_security_group_id in number,
p_responsibility_app_id in number,
p_linked_function_id in number,
p_link_type in varchar2,
p_user_url in varchar2,
p_user_link_name in varchar2,
p_level_site_id in number,
p_level_resp_id in number,
p_level_app_id in number,
p_level_org_id in number,
p_level_function_id in number,
p_level_user_id in number,
p_user_id in number) IS
BEGIN
INSERT INTO bis_custom_related_links
(related_link_id,
display_sequence,
function_id,
responsibility_id,
security_group_id,
responsibility_application_id,
linked_function_id,
link_type,
user_url,
level_site_id,
level_responsibility_id,
level_application_id,
level_org_id,
level_function_id,
level_user_id,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
VALUES(p_related_link_id,
p_display_sequence,
p_function_id,
p_responsibility_id,
p_security_group_id,
p_responsibility_app_id,
p_linked_function_id,
p_link_type,
p_user_url,
p_level_site_id,
p_level_resp_id,
p_level_app_id,
p_level_org_id,
p_level_function_id,
p_level_user_id,
p_user_id,
SYSDATE,
SYSDATE,
p_user_id,
p_user_id);
INSERT INTO bis_custom_related_links_tl
( related_link_id,
user_link_name,
language,
source_lang,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
select
p_related_link_id,
p_user_link_name,
language_code,
userenv('LANG'),
p_user_id,
SYSDATE,
SYSDATE,
p_user_id,
p_user_id
from fnd_languages l
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select null
from bis_custom_related_links_tl
where related_link_id = p_related_link_id
and language = l.language_code);
END insertCustomLinks;
select sum(level_user_id), sum(level_function_id), sum(level_application_id), sum(level_org_id), sum(level_responsibility_id),sum(level_site_id)
into l_user_level, l_function_level, l_app_level, l_org_level, l_resp_level, l_site_level
from bis_custom_related_links_vl
where function_id = p_function_id;
procedure delete_function_links (
p_function_id IN number
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
cursor cr_seedlinks is
SELECT related_link_id
FROM bis_related_links
WHERE function_id = p_function_id;
SELECT related_link_id
FROM bis_custom_related_links
WHERE function_id = p_function_id;
delete bis_related_links_tl
where related_link_id = l.related_link_id;
-- mdamle 06/29/2004 - Don't delete customized links
/*
for l in cr_customlinks loop
begin
delete bis_custom_related_links_tl
where related_link_id = l.related_link_id;
delete bis_related_links
where function_id = p_function_id;
-- mdamle 06/29/2004 - Don't delete customized links
/*
begin
delete bis_custom_related_links
where function_id = p_function_id;
END delete_function_links;
select bis_related_links_s.nextval into l_related_link_id from dual;
INSERT INTO BIS_RELATED_LINKS
(RELATED_LINK_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
FUNCTION_ID,
LINK_TYPE,
LINKED_FUNCTION_ID,
USER_ID,
USER_URL,
LAST_UPDATE_LOGIN,
RESPONSIBILITY_ID,
SECURITY_GROUP_ID,
RESPONSIBILITY_APPLICATION_ID,
DISPLAY_SEQUENCE)
SELECT
l_related_link_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_function_id,
p_link_type,
p_linked_function_id,
p_user_id,
p_user_url,
p_login_id,
p_resp_id,
p_sec_grp_id,
p_resp_app_id,
p_display_sequence
FROM DUAL;
INSERT INTO bis_related_links_tl
(related_link_id,
user_link_name,
language,
source_lang,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
select
l_related_link_id,
p_user_link_name,
language_code,
userenv('LANG'),
p_user_id,
SYSDATE,
SYSDATE,
p_user_id,
p_login_id
from fnd_languages l
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select null
from bis_related_links_tl
where related_link_id = l_related_link_id
and language = l.language_code);
select related_link_id into l_related_link_id
from bis_related_links
where function_id = p_function_id
and display_sequence = p_display_sequence;
update bis_related_links_tl
set user_link_name = p_user_link_name,
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
source_lang = userenv('LANG')
where related_link_id = l_related_link_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
SELECT function_id, link_type, linked_function_id, user_url, display_Sequence,
responsibility_id, security_group_id, responsibility_application_id,
user_link_name
FROM bis_related_links_vl
WHERE function_id = p_source_function_id;
delete_function_links(
p_function_id => p_dest_function_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
l_related_link_id := insert_bis_related_link (
pUser_id => p_user_id,
pdisplay_sequence => l.display_sequence,
puser_link_name => l.user_link_name,
pfunction_id => p_dest_function_id,
presponsibility_id => l.responsibility_id,
psecurity_group_id => l.security_group_id,
presponsibility_application_id => l.responsibility_application_id,
plinked_function_id => l.linked_function_id,
plink_type => l.link_type,
puser_url => l.user_url,
pcreated_by => p_user_id,
plast_updated_by => p_user_id,
plast_update_login => p_user_id);
select a.responsibility_id,
b.responsibility_application_id,
b.security_group_id,
a.menu_id
into l_object.responsibility_id,
l_object.resp_appl_id,
l_object.security_group_id,
l_menu_id
from FND_SECURITY_GROUPS_VL fsg,
fnd_responsibility_vl a,
FND_USER_RESP_GROUPS b
where a.responsibility_id = p_resp_id
and b.user_id = p_user_id
and b.start_date <= sysdate
and (b.end_date is null or b.end_date > sysdate)
and b.RESPONSIBILITY_id = a.responsibility_id
and b.RESPONSIBILITY_application_id = a.application_id
and a.version = 'W'
and a.start_date <= sysdate
and (a.end_date is null or a.end_date > sysdate)
and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID;
p_funcn_sql := ' SELECT mev.function_id AS FunctionId,
fff.user_function_name AS Prompt,
nvl(fff.description, nvl(mev.description, mev.prompt)) AS Description,
''N'' as DummySelect,
NULL as ReportUrl
FROM
FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
WHERE
mev.function_id=fff.function_id
AND mev.function_id in (';
AND mev.function_id in (select linked_function_id
from bis_custom_related_Links_v rl
where level_user_id = :' || l_bind_count;
SELECT mev.function_id as FunctionId,
fff.user_function_name AS Prompt,
nvl(fff.description, nvl(mev.description, mev.prompt)) as Description,
''N'' as DummySelect,
NULL as ReportUrl
FROM
FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
where
mev.function_id=fff.function_id
AND mev.function_id in (';
AND mev.function_id not in (select linked_function_id
from bis_custom_related_Links_v rl
where level_user_id = :' || l_bind_count;
select a.responsibility_id,
b.responsibility_application_id,
b.security_group_id,
a.menu_id
into l_object.responsibility_id,
l_object.resp_appl_id,
l_object.security_group_id,
l_menu_id
from FND_SECURITY_GROUPS_VL fsg,
fnd_responsibility_vl a,
FND_USER_RESP_GROUPS b
where a.responsibility_id = l_id
and b.user_id = p_user_id
and b.start_date <= sysdate
and (b.end_date is null or b.end_date > sysdate)
and b.RESPONSIBILITY_id = a.responsibility_id
and b.RESPONSIBILITY_application_id = a.application_id
and a.version = 'W'
and a.start_date <= sysdate
and (a.end_date is null or a.end_date > sysdate)
and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID;
p_funcn_sql := ' select mev.function_id as FunctionId,
fff.user_function_name AS Prompt,
nvl(fff.description, nvl(mev.description, mev.prompt)) as Description,
''N'' as DummySelect,
null as ReportUrl
from FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
where mev.function_id=fff.function_id
AND mev.function_id in (';
and mev.function_id in (select linked_function_id
from bis_custom_related_Links_v rl
where level_user_id = :' || l_bind_count;
select mev.function_id as FunctionId,
fff.user_function_name AS Prompt,
nvl(fff.description, nvl(mev.description, mev.prompt)) as Description,
''N'' as DummySelect,
null as ReportUrl
from FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
where mev.function_id=fff.function_id
and mev.function_id in (';
and mev.function_id not in (select linked_function_id
from bis_custom_related_Links_v rl
where level_user_id = :' || l_bind_count;
select a.responsibility_id,
b.responsibility_application_id,
b.security_group_id,
a.menu_id
into l_object.responsibility_id,
l_object.resp_appl_id,
l_object.security_group_id,
l_menu_id
from FND_SECURITY_GROUPS_VL fsg,
fnd_responsibility_vl a,
FND_USER_RESP_GROUPS b
where a.responsibility_id = p_resp_id
and b.user_id = p_user_id
and b.start_date <= sysdate
and (b.end_date is null or b.end_date > sysdate)
and b.RESPONSIBILITY_id = a.responsibility_id
and b.RESPONSIBILITY_application_id = a.application_id
and a.version = 'W'
and a.start_date <= sysdate
and (a.end_date is null or a.end_date > sysdate)
and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID;
p_funcn_sql := 'SELECT mev.function_id as FunctionId,
NVL(mev.prompt, fff.function_name) AS Prompt,
nvl(fff.description, nvl(mev.description, mev.prompt)) AS Description,
''N'' AS DummySelect,
NULL AS ReportUrl
FROM FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
WHERE mev.function_id IN (';
SELECT linked_function_id
FROM BIS_CUSTOM_RELATED_LINKS_V rl
WHERE level_user_id = :';
SELECT mev.function_id AS FunctionId,
NVL(mev.prompt, fff.function_name) AS Prompt,
nvl(fff.description, nvl(mev.description, mev.prompt)) AS Description,
''N'' AS DummySelect,
NULL AS ReportUrl
FROM FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
WHERE mev.function_id IN (';
SELECT linked_function_id
FROM BIS_CUSTOM_RELATED_LINKS_V rl
WHERE level_user_id = :';
select a.responsibility_id,
b.responsibility_application_id,
b.security_group_id,
a.menu_id
into l_object.responsibility_id,
l_object.resp_appl_id,
l_object.security_group_id,
l_menu_id
from FND_SECURITY_GROUPS_VL fsg,
fnd_responsibility_vl a,
FND_USER_RESP_GROUPS b
where a.responsibility_id = p_resp_id
and b.user_id = p_user_id
and b.start_date <= sysdate
and (b.end_date is null or b.end_date > sysdate)
and b.RESPONSIBILITY_id = a.responsibility_id
and b.RESPONSIBILITY_application_id = a.application_id
and a.version = 'W'
and a.start_date <= sysdate
and (a.end_date is null or a.end_date > sysdate)
and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID;
p_funcn_sql := ' select mev.function_id as FunctionId,
fff.user_function_name AS Prompt,
nvl(fff.description, nvl(mev.description, mev.prompt)) as Description,
''N'' as DummySelect,
null as ReportUrl
from FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
where mev.function_id=fff.function_id
and mev.function_id in (';
and mev.function_id in (select linked_function_id
from bis_custom_related_Links_v rl
where level_user_id = :';
select mev.function_id as FunctionId,
fff.user_function_name AS Prompt,
nvl(fff.description, nvl(mev.description, mev.prompt)) as Description,
''N'' as DummySelect,
null as ReportUrl
from FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
where mev.function_id=fff.function_id
and mev.function_id in (';
and mev.function_id not in (select linked_function_id
from bis_custom_related_Links_v rl
where level_user_id = :';
UPDATE BIS_RELATED_LINKS_TL T SET (
USER_LINK_NAME
) = (SELECT
B.USER_LINK_NAME
FROM BIS_RELATED_LINKS_TL B
WHERE B.RELATED_LINK_ID = T.RELATED_LINK_ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.RELATED_LINK_ID,
T.LANGUAGE
) IN (SELECT
SUBT.RELATED_LINK_ID,
SUBT.LANGUAGE
FROM BIS_RELATED_LINKS_TL SUBB, BIS_RELATED_LINKS_TL SUBT
WHERE SUBB.RELATED_LINK_ID = SUBT.RELATED_LINK_ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.USER_LINK_NAME <> SUBT.USER_LINK_NAME
));
INSERT INTO BIS_RELATED_LINKS_TL
(
RELATED_LINK_ID,
LANGUAGE,
USER_LINK_NAME,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
SELECT
B.RELATED_LINK_ID,
L.LANGUAGE_CODE,
B.USER_LINK_NAME,
B.SOURCE_LANG,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN
FROM BIS_RELATED_LINKS_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS
(
SELECT NULL
FROM BIS_RELATED_LINKS_TL T
WHERE T.RELATED_LINK_ID = B.RELATED_LINK_ID
AND T.LANGUAGE = L.LANGUAGE_CODE
);
UPDATE BIS_CUSTOM_RELATED_LINKS_TL T SET (
USER_LINK_NAME
) = (SELECT
B.USER_LINK_NAME
FROM BIS_CUSTOM_RELATED_LINKS_TL B
WHERE B.RELATED_LINK_ID = T.RELATED_LINK_ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.RELATED_LINK_ID,
T.LANGUAGE
) IN (SELECT
SUBT.RELATED_LINK_ID,
SUBT.LANGUAGE
FROM BIS_CUSTOM_RELATED_LINKS_TL SUBB, BIS_CUSTOM_RELATED_LINKS_TL SUBT
WHERE SUBB.RELATED_LINK_ID = SUBT.RELATED_LINK_ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.USER_LINK_NAME <> SUBT.USER_LINK_NAME
));
INSERT INTO BIS_CUSTOM_RELATED_LINKS_TL
(
RELATED_LINK_ID,
LANGUAGE,
USER_LINK_NAME,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
SELECT
B.RELATED_LINK_ID,
L.LANGUAGE_CODE,
B.USER_LINK_NAME,
B.SOURCE_LANG,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN
FROM BIS_CUSTOM_RELATED_LINKS_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS
(
SELECT NULL
FROM BIS_CUSTOM_RELATED_LINKS_TL T
WHERE T.RELATED_LINK_ID = B.RELATED_LINK_ID
AND T.LANGUAGE = L.LANGUAGE_CODE
);