[Home] [Help]
PACKAGE BODY: APPS.BIS_RL_PKG
Source
1 package body BIS_RL_PKG AS
2 /* $Header: BISVEXLB.pls 120.6.12000000.2 2007/04/19 11:09:23 akoduri ship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.107=120.6.12000000.2):~PROD:~PATH:~FILE
5 -- +==========================================================================+
6 -- | Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA |
7 -- | All rights reserved. |
8 -- +==========================================================================+
9 -- | FILENAME |
10 -- | BISVEXLB.pls |
11 -- | |
12 -- | DESCRIPTION |
13 -- | Body of Extensible Hyperlinks |
14 -- | NOTES |
15 -- | |
16 -- | HISTORY |
17 -- | 15-APR-99 amkulkar creation |
18 -- | 14-DEC-99 JK Radhakrisnan Removed the Hard coded label 'Customize' |
19 -- | 13-JUN-00 wnasrall Increased size of URL field to 2000 from 50 |
20 -- | 02-AUG-00 Debbie Jancis Fixed Bug 1346632: Message was hard coded |
21 -- | changed to BIS_RELOAD containing the text |
22 -- | Please reload the page to enable the new |
23 -- | links. |
24 -- | 08-Aug-00 Debbie Jancis Reincorporated wnasrall's fixes from version|
25 -- | 115.14 (bug 1307419). Problem was mispelled|
26 -- | javascript commands. Rolled back fix for |
27 -- | bug 1346632 as it is no longer necessary |
28 -- | Also fixed list initialization problem as |
29 -- | mentioned in version 115.15. |
30 -- | 07-Sep-00 aleung Remove sub headings(reports and URLs) under |
31 -- | when no links exist; valigned both report |
32 -- | table and URL table to "top" |
33 -- | 11-Sep-00 aleung fixed bug 1395913 |
34 -- | 11-Sep-00 aleung fixed bug 1395928 |
35 -- | 13-Sep-00 aleung add hide detail link |
36 -- | 27-Sep-00 aleung move the customized button out of this |
37 -- | package and put it in BISVIEWER |
38 -- | 28-Sep-00 aleung modified customize_Related_Links to call |
39 -- | bisviewer.showReport instead of redirecting |
40 -- | to previous URL |
41 -- | 19-Oct-00 aleung check if the entered location contains |
42 -- | "http:// ..." (bug 1469285) |
43 -- | 28-Nov-00 aleung increase the size of l_url in procedure |
44 -- | UpdateFavorites from 80 to 2000 and increase|
45 -- | the size of l_favorite in procedure |
46 -- | FavoriteCreate from 80 to 3000 to fix bug |
47 -- | 1517139 (links cannot be longer than 50 char|
48 -- | 18-Dec-00 aleung use new api for bis_report_util_pvt |
49 -- | 06-Mar-01 aleung call BISVIEWER.showReport directly for RG |
50 -- | related reports |
51 -- | 12-Mar-01 mdamle Related Links wrappers for Java |
52 -- | 23-Mar-01 aleung introduce vCurrentRespName |
53 -- | 31-Mar-01 aleung get related info based on user_id |
54 -- | 31-May-01 mdamle New ICX Profile for OA_HTML, OA_MEDIA |
55 -- | 06-JUN-01 aleung fix bug 1795101, modified deleteto() |
56 -- | 17-OCT-01 gsanap changed header & footer for ipd & others |
57 -- | 18-OCT-01 dmarkman get related info based on user_id =-1 |
58 -- | 28-SEP-02 nbarik Bug Fix 2599765 Use Insert as Select |
59 -- | 24-OCT-02 nkishore Bug Fix 2606104 removed SSWA pages from links |
60 -- | 20-NOV-02 nbarik Bug Fix 2664392 remove duplicate function |
61 -- | 10-DEC-02 nkishore Added updateRelatedLink_Wrapper, |
62 -- | reorderRelatedLinks, getAllRespFuncnSQLWrapper |
63 -- | 24-DEC-02 ansingh Added Wrappers for Preseeding RL Enhancement |
64 -- | 27-DEC-02 nkishore Bug Fix 2727839 |
65 -- | 24-DEC-02 ansingh Bug Fix 2736337 |
66 -- | 08-JAN-02 ansingh Bug Fix 2734371, 2732877, Added new Function |
67 -- | isUserIdInLinkParams |
68 -- | 15-APR-03 nbarik Bug Fix 2893197 - Don't show Web Portlet and |
69 -- | DB Portlet as links |
70 -- | 10-JUL-03 ansingh Related Links Enhancement |
71 -- | 08-AUG-03 ansingh BugFix 3029275 |
72 -- | 23-MAR-04 nbarik Bug Fix 3511444 |
73 -- | 12-MAR-04 mdamle Enh 3503753 - Site level custom. for links |
74 -- | 28-MAY-04 mdamle Added delete API to be called from LCT file |
75 -- | 29-JUN-04 mdamle Bug# 3733945 - Don't delete customized links |
76 -- | when links are are uploaded from lct file |
77 -- | 09-AUG-04 mdamle Bug# 3816889 - Insert custom link for inst. lang|
78 -- | 09-AUG-04 mdamle Bug# 3813449 - New API to copy links to dup. rpt|
79 -- | 30-MAR-05 ankagarw Bug# 4251673 - Changed addRelatedlink_Wrapper |
80 -- | If customLevel is null then getting security group id |
81 -- | from fnd_responsibility |
82 -- | 06-APR-05 ankagarw Bug# 4284816 - Changed addRelatedlink_Wrapper |
83 -- | added NVL condition for the resp_id, |
84 -- | security group id, resp application id |
85 -- | 25-MAR-2005 ankagarw bug#4392370 - Removed SECURITY_GROUP_ID |
86 -- | 04-AUG-2006 visuri bug#5372826- EDIT LINK PORTLET SHOW NO DATA |
87 -- | 19-APR-2007 akoduri Bug#5673635- Exception while adding link to |
88 -- | links portlet in dashboard designer |
89 -- +==========================================================================+
90
91 TYPE object IS RECORD (
92 location varchar2(30),
93 display_sequence pls_integer,
94 type varchar2(30),
95 resp_appl_id pls_integer,
96 responsibility_id pls_integer,
97 security_group_id pls_integer,
98 parent_menu_id pls_integer,
99 entry_sequence pls_integer,
100 menu_id pls_integer,
101 function_id pls_integer,
102 function_type varchar2(30),
103 menu_explode varchar2(30),
104 function_explode varchar2(30),
105 level pls_integer,
106 prompt varchar2(240),
107 description varchar2(240),
108 web_html_call varchar2(240),
109 show_all_entries varchar2(1)); -- nbarik - 03/23/04- Bug Fix 3511444 - Added show_all_entries;
110
111 TYPE objectTable IS TABLE OF object index by binary_integer;
112
113 -- mdamle 05/31/2001 - New ICX Profile for OA_HTML, OA_MEDIA
114 -- images varchar2(1000) := FND_PROFILE.value('ICX_REPORT_IMAGES');
115 -- g_images varchar2(1000) := FND_WEB_CONFIG.TRAIL_SLASH(images);
116 g_images varchar2(1000) := BIS_REPORT_UTIL_PVT.get_Images_Server;
117
118 g_nulllist objectTable;
119 g_list objectTable;
120 g_executablelist objectTable;
121 function security_group_count return pls_integer is
122 l_security_group_count pls_integer;
123 begin
124 select count(*)
125 into l_security_group_count
126 from fnd_security_groups
127 where security_group_id >= 0;
128
129 return l_security_group_count;
130 end security_group_count;
131
132
133 procedure listMenuEntries(p_object in object,
134 p_entries in boolean default TRUE,
135 p_executable in boolean default FALSE) is
136
137 l_index pls_integer;
138 l_object object;
139 l_count pls_integer;
140
141 cursor menuentries is
142 select prompt,
143 description,
144 sub_menu_id,
145 entry_sequence
146 from fnd_menu_entries_vl
147 where menu_id = p_object.parent_menu_id
148 and sub_menu_id is not null
149 and prompt is not null
150 order by entry_sequence;
151
152 -- nbarik - 03/23/04- Bug Fix 3511444
153 cursor c_all_menuentries is
154 select prompt, description, sub_menu_id, entry_sequence
155 from fnd_menu_entries_vl
156 where menu_id = p_object.parent_menu_id
157 and sub_menu_id is not null
158 order by entry_sequence;
159
160 --jprabhud - 03/04/03 - Refresh Portal Page - Remove the filter based on the type
161 --cursor functionentries ( lHtmlCall In VARCHAR2, lWww In VARCHAR2, lWwk In VARCHAR2) is
162 cursor functionentries ( lHtmlCall In VARCHAR2) is
163 --select nvl(b.prompt,b.description) prompt,
164 select b.prompt prompt,
165 nvl(nvl(b.description,a.description),b.prompt) description,
166 b.function_id,
167 b.entry_sequence,
168 a.type,
169 a.web_html_call
170 from fnd_form_functions_vl a,
171 fnd_menu_entries_vl b
172 where b.menu_id = p_object.parent_menu_id
173 and a.function_id = b.function_id
174 --jprabhud - 03/04/03 - Refresh Portal Page - Remove the filter based on the type
175 --and a.type in (lWww ,lWwk)
176 --nbarik - 04/15/03 - Bug Fix 2893197 - Don't show Web Portlet and DB Portlet as links
177 AND a.type NOT IN ('WEBPORTLET', 'DBPORTLET')
178 -- jprabhud 05/18/05 - addded decode condition
179 and decode(a.web_html_call,null,'NULL',lower(a.web_html_call)) not like lHtmlCall --Fix for 2606104
180 AND b.prompt IS NOT NULL --Bug Fix 2664392 : Don't show the function , if prompt is null
181 order by entry_sequence;
182
183 --ansingh -Preseed Related Links Enhancement -Start
184 cursor relatedlinksentries is
185 select nvl(b.prompt,b.description) prompt,
186 nvl(nvl(b.description,a.description),b.prompt) description,
187 b.function_id,
188 b.entry_sequence,
189 a.type,
190 a.web_html_call
191 from fnd_form_functions_vl a,
192 fnd_menu_entries_vl b
193 where b.menu_id = p_object.parent_menu_id
194 and a.function_id = b.function_id
195 and a.type in ('WEBPORTLET')
196 and a.web_html_call like '%BIS_PM_RELATED_LINK_LAYOUT%'
197 order by entry_sequence;
198
199 l_rl_portlet_count pls_integer;
200
201 --ansingh -Preseed Related Links Enhancement -End
202
203 begin
204
205 select count(*)
206 into l_count
207 from fnd_menu_entries
208 where menu_id = p_object.parent_menu_id
209 and sub_menu_id is not null;
210
211 if l_count > 0
212 then
213 -- nbarik - 03/23/04- Bug Fix 3511444
214 IF (p_object.show_all_entries = 'Y') THEN
215 for m in c_all_menuentries loop
216 l_index := g_list.COUNT;
217 g_list(l_index).type := 'MENU';
218 g_list(l_index).resp_appl_id := p_object.resp_appl_id;
219 g_list(l_index).responsibility_id := p_object.responsibility_id;
220 g_list(l_index).security_group_id := p_object.security_group_id;
221 g_list(l_index).parent_menu_id := p_object.parent_menu_id;
222 g_list(l_index).entry_sequence := m.entry_sequence;
223 g_list(l_index).level := p_object.level;
224 g_list(l_index).prompt := m.prompt;
225 g_list(l_index).description := m.description;
226 if p_object.menu_explode = 'Y' then
227 l_object.resp_appl_id := p_object.resp_appl_id;
228 l_object.responsibility_id := p_object.responsibility_id;
229 l_object.security_group_id := p_object.security_group_id;
230 l_object.parent_menu_id := m.sub_menu_id;
231 l_object.menu_explode := p_object.menu_explode;
232 l_object.function_explode := p_object.function_explode;
233 l_object.level := p_object.level+1;
234 l_object.prompt := p_object.prompt;
235 l_object.description := p_object.description;
236 l_object.show_all_entries := p_object.show_all_entries;
237 listMenuEntries(p_object =>l_object,
238 p_entries => p_entries,
239 p_executable => p_executable);
240 end if;
241 end loop; -- c_all_menuentries
242 ELSE
243 for m in menuentries loop
244 l_index := g_list.COUNT;
245 g_list(l_index).type := 'MENU';
246 g_list(l_index).resp_appl_id := p_object.resp_appl_id;
247 g_list(l_index).responsibility_id := p_object.responsibility_id;
248 g_list(l_index).security_group_id := p_object.security_group_id;
249 g_list(l_index).parent_menu_id := p_object.parent_menu_id;
250 g_list(l_index).entry_sequence := m.entry_sequence;
251 g_list(l_index).level := p_object.level;
252 g_list(l_index).prompt := m.prompt;
253 g_list(l_index).description := m.description;
254 if p_object.menu_explode = 'Y'
255 then
256 l_object.resp_appl_id := p_object.resp_appl_id;
257 l_object.responsibility_id := p_object.responsibility_id;
258 l_object.security_group_id := p_object.security_group_id;
259 l_object.parent_menu_id := m.sub_menu_id;
260 l_object.menu_explode := p_object.menu_explode;
261 l_object.function_explode := p_object.function_explode;
262 l_object.level := p_object.level+1;
263 l_object.prompt := p_object.prompt;
264 l_object.description := p_object.description;
265 listMenuEntries(p_object =>l_object,
266 p_entries => p_entries,
267 p_executable => p_executable);
268 end if;
269 end loop; -- menuentries
270 END IF; -- p_object.show_all_entries = 'Y'
271 end if;
272
273 if not p_executable
274 and (p_object.function_explode = 'Y' or (p_entries and p_object.level = 1))
275 then
276
277 select count(*)
278 into l_count
279 from fnd_form_functions a,
280 fnd_menu_entries b
281 where b.menu_id = p_object.parent_menu_id
282 and a.function_id = b.function_id
283 --jprabhud - 03/04/03 - Refresh Portal Page - Remove the filter based on the type
284 --and a.type in ('WWW','WWK')
285 --nbarik - 04/15/03 - Bug Fix 2893197 - Don't show Web Portlet and DB Portlet as links
286 AND a.type NOT IN ('WEBPORTLET', 'DBPORTLET')
287 -- jprabhud 05/18/05 - added decode condition
288 and decode(a.web_html_call,null,'NULL',lower(a.web_html_call)) not like '%window.open%';
289
290
291 --ansingh -Preseed Related Links Enhancement -Start
292 select count(*)
293 into l_rl_portlet_count
294 from fnd_form_functions_vl a,
295 fnd_menu_entries_vl b
296 where b.menu_id = p_object.parent_menu_id
297 and a.function_id = b.function_id
298 and a.type in ('WEBPORTLET')
299 and a.web_html_call like '%BIS_PM_RELATED_LINK_LAYOUT%';
300 --ansingh -Preseed Related Links Enhancement -End
301
302
303 if l_count > 0
304 then
305 if p_object.level = 1
306 then
307 l_index := g_list.COUNT;
308 g_list(l_index).type := 'MENU';
309 g_list(l_index).resp_appl_id := p_object.resp_appl_id;
310 g_list(l_index).responsibility_id := p_object.responsibility_id;
311 g_list(l_index).security_group_id := p_object.security_group_id;
312 g_list(l_index).parent_menu_id := p_object.parent_menu_id;
313 g_list(l_index).entry_sequence := 0;
314 g_list(l_index).level := p_object.level;
315 g_list(l_index).prompt := p_object.prompt;
316 g_list(l_index).description := p_object.description;
317 end if;
318
319 --jprabhud - 03/04/03 - Refresh Portal Page - Remove the filter based on the type
320 --for f in functionentries('%window.open%','WWW','WWK') loop
321 for f in functionentries('%window.open%') loop
322 l_index := g_list.COUNT;
323 g_list(l_index).type := 'FUNCTION';
324 g_list(l_index).resp_appl_id := p_object.resp_appl_id;
325 g_list(l_index).responsibility_id := p_object.responsibility_id;
326 g_list(l_index).security_group_id := p_object.security_group_id;
327 g_list(l_index).parent_menu_id := p_object.parent_menu_id;
328 g_list(l_index).entry_sequence := f.entry_sequence;
329 g_list(l_index).function_id := f.function_id;
330 g_list(l_index).function_type := f.type;
331 g_list(l_index).web_html_call := f.web_html_call;
332 g_list(l_index).level := p_object.level;
333 if f.prompt is not null
334 then
335 g_list(l_index).prompt := f.prompt;
336 if f.description is not null
337 then
338 g_list(l_index).description := f.description;
339 else
340 g_list(l_index).description := f.prompt;
341 end if;
342 else
343 g_list(l_index).prompt := f.description;
344 g_list(l_index).description := f.description;
345 end if;
346 end loop; -- menuentries
347 end if;
348
349 --ansingh -Preseed Related Links Enhancement -Start
350 if l_rl_portlet_count > 0
351 then
352 if p_object.level = 1
353 then
354 l_index := g_list.COUNT;
355 g_list(l_index).type := 'MENU';
356 g_list(l_index).resp_appl_id := p_object.resp_appl_id;
357 g_list(l_index).responsibility_id := p_object.responsibility_id;
358 g_list(l_index).security_group_id := p_object.security_group_id;
359 g_list(l_index).parent_menu_id := p_object.parent_menu_id;
360 g_list(l_index).entry_sequence := 0;
361 g_list(l_index).level := p_object.level;
362 g_list(l_index).prompt := p_object.prompt;
363 g_list(l_index).description := p_object.description;
364 end if;
365
366 for f in relatedlinksentries loop
367 l_index := g_list.COUNT;
368 g_list(l_index).type := 'WEBPORTLET';
369 g_list(l_index).resp_appl_id := p_object.resp_appl_id;
370 g_list(l_index).responsibility_id := p_object.responsibility_id;
371 g_list(l_index).security_group_id := p_object.security_group_id;
372 g_list(l_index).parent_menu_id := p_object.parent_menu_id;
373 g_list(l_index).entry_sequence := f.entry_sequence;
374 g_list(l_index).function_id := f.function_id;
375 g_list(l_index).function_type := f.type;
376 g_list(l_index).web_html_call := f.web_html_call;
377 g_list(l_index).level := p_object.level;
378 if f.prompt is not null
379 then
380 g_list(l_index).prompt := f.prompt;
381 if f.description is not null
382 then
383 g_list(l_index).description := f.description;
384 else
385 g_list(l_index).description := f.prompt;
386 end if;
387 else
388 g_list(l_index).prompt := f.description;
389 g_list(l_index).description := f.description;
390 end if;
391 end loop; -- relatedlinksportlet
392 end if;
393
394 --ansingh -Preseed Related Links Enhancement -End
395 end if;
396
397 exception
398 when others then
399 htp.p(SQLERRM);
400
401 end;
402
403
404 procedure listMenu(p_object in object,
405 p_entries in boolean default TRUE)
406 is
407 l_prompt varchar2(240);
408 l_description varchar2(240);
409 l_sub_menu_id pls_integer;
410 l_index pls_integer;
411 l_object object;
412
413 begin
414
415 select prompt,
416 description,
417 sub_menu_id
418 into l_prompt,
419 l_description,
420 l_sub_menu_id
421 from fnd_menu_entries_vl
422 where menu_id = p_object.parent_menu_id
423 and entry_sequence = p_object.entry_sequence
424 order by entry_sequence;
425
426 l_index := g_list.COUNT;
427 g_list(l_index).location := p_object.location;
428 g_list(l_index).display_sequence := p_object.display_sequence;
429 g_list(l_index).type := 'MENU';
430 g_list(l_index).responsibility_id := p_object.responsibility_id;
431 g_list(l_index).parent_menu_id := p_object.parent_menu_id;
432 g_list(l_index).entry_sequence := p_object.entry_sequence;
433 g_list(l_index).menu_explode := p_object.menu_explode;
434 g_list(l_index).function_explode := p_object.function_explode;
435 g_list(l_index).level := p_object.level;
436
437 if l_prompt is not null
438 then
439 g_list(l_index).prompt := l_prompt;
440 if l_description is not null
441 then
442 g_list(l_index).description := l_description;
443 else
444 g_list(l_index).description := l_prompt;
445 end if;
446 else
447 g_list(l_index).prompt := l_description;
448 g_list(l_index).description := l_description;
449 end if;
450
451 l_object.responsibility_id := p_object.responsibility_id;
452 l_object.parent_menu_id := l_sub_menu_id;
453 l_object.menu_explode := p_object.menu_explode;
454 l_object.function_explode := p_object.function_explode;
455 l_object.level := p_object.level+1;
456 listMenuEntries(p_object => l_object,
457 p_entries => p_entries);
458
459 exception
460 when others then
461 htp.p(SQLERRM);
462
463 end;
464
465
466 -- ***********************************************
467 -- Procedure listResponsibility
468 -- ***********************************************
469 procedure listResponsibility(p_object in object,
470 p_entries in boolean default TRUE,
471 p_executable in boolean default FALSE)
472 is
473 l_responsibility_name varchar2(100);
474 l_description varchar2(240);
475 l_menu_id pls_integer;
476 l_index pls_integer;
477 l_object object;
478
479 begin
480
481 select responsibility_name,
482 description,
483 menu_id
484 into l_responsibility_name,
485 l_description,
486 l_menu_id
487 from fnd_responsibility_vl
488 where responsibility_id = p_object.responsibility_id
489 and application_id = p_object.resp_appl_id
490 -- remove version check to show all resp
491 --and version = 'W'
492 and start_date <= sysdate
493 and (end_date is null or end_date > sysdate);
494
495 l_index := g_list.COUNT;
496 g_list(l_index).location := p_object.location;
497 g_list(l_index).display_sequence := p_object.display_sequence;
498 g_list(l_index).type := 'RESPONSIBILITY';
499 g_list(l_index).resp_appl_id := p_object.resp_appl_id;
500 g_list(l_index).responsibility_id := p_object.responsibility_id;
501 g_list(l_index).security_group_id := p_object.security_group_id;
502 g_list(l_index).prompt := l_responsibility_name;
503 g_list(l_index).description := l_description;
504 g_list(l_index).menu_explode := p_object.menu_explode;
505 g_list(l_index).function_explode := p_object.function_explode;
506 g_list(l_index).level := p_object.level;
507
508 if p_object.menu_explode = 'Y'
509 or (p_entries and p_object.level = 0)
510 then
511 l_object.resp_appl_id := p_object.resp_appl_id;
512 l_object.responsibility_id := p_object.responsibility_id;
513 l_object.security_group_id := p_object.security_group_id;
514 l_object.parent_menu_id := l_menu_id;
515 l_object.menu_explode := p_object.menu_explode;
516 l_object.function_explode := p_object.function_explode;
517 l_object.level := p_object.level+1;
518 l_object.prompt := p_object.prompt;
519 l_object.description := p_object.description;
520 -- nbarik - 03/23/04- Bug Fix 3511444
521 l_object.show_all_entries := p_object.show_all_entries;
522 listMenuEntries(p_object =>l_object,
523 p_entries => p_entries,
524 p_executable => p_executable);
525 end if;
526
527 exception
528 when others then
529 htp.p(SQLERRM);
530
531 end;
532
533 procedure FavoriteCreate is
534
535 l_title varchar2(80);
536 l_prompts icx_util.g_prompts_table;
537
538 begin
539
540 if(icx_sec.validateSession)
541 then
542 icx_util.getprompts(601, 'ICX_OBIS_FAVORITE_CREATE', l_title, l_prompts);
543 --l_title := FND_MESSAGE.get_string('BIS', 'BIS_ADD_URL');
544 htp.p('<html>');
545 htp.p('<head>');
546 htp.p('<title>'||l_title||'</title>');
547 htp.p('</head>');
548
549 --- This standardizes the fonts and colors
550 --- correct spelling of Javascript (1307419)
551 htp.p('<body onload="Javascript:window.focus()">');
552 BIS_UTILITIES_PVT.putStyle;
553
554 htp.p('<SCRIPT LANGUAGE="JavaScript">');
555
556 -- bug 1395928 fixed
557 -- aleung, 10/19/2000, check if the entered location contains "http:// ...", bug 1469285 fixed
558 htp.p('function saveCreate() {
559
560 if (document.createFavorite.LOCATION.value == "")
561 alert("'||l_prompts(1)||'");
562 else if (document.createFavorite.NAME.value == "")
563 alert("'||l_prompts(2)||'");
564 else {
565 var end=parent.opener.parent.document.Favorites.C.length;
566 var totext=document.createFavorite.NAME.value;
567 var tovalue;
568
569 if ((document.createFavorite.LOCATION.value.substr(0,7).toLowerCase()=="http://")
570 || (document.createFavorite.LOCATION.value.substr(0,8).toLowerCase()=="https://")
571 || (document.createFavorite.LOCATION.value.substr(0,6).toLowerCase()=="ftp://")
572 || (document.createFavorite.LOCATION.value.substr(0,7).toLowerCase()=="file://"))
573 tovalue = "0*0*0*X" + document.createFavorite.LOCATION.value + "*USER_URL";
574 else
575 tovalue = "0*0*0*X" + "http://" + document.createFavorite.LOCATION.value + "*USER_URL";
576
577 if (parent.opener.parent.document.Favorites.C.options[end-1].value == "")
578 end = end - 1;
579 parent.opener.parent.document.Favorites.C.options[end].text = totext;
580 parent.opener.parent.document.Favorites.C.options[end].value = tovalue;');
581 /* if instr(owa_util.get_cgi_env('HTTP_USER_AGENT'),'MSIE') = 0 then
582 htp.p('parent.opener.parent.history.go(0);');
583 end if; */
584 htp.p('window.close();
585 };
586 }');
587
588 htp.p('</SCRIPT>');
589
590 htp.formOpen('javascript:saveCreate()','POST','','','NAME="createFavorite"');
591 htp.tableOpen;
592 htp.tableRowOpen;
593 htp.tableData(htf.bold(l_prompts(1)), 'RIGHT');
594 htp.tableData(htf.formText(cname => 'LOCATION',
595 csize => '50'), 'LEFT');
596 htp.tableRowClose;
597 htp.tableRowOpen;
598 htp.tableData(htf.bold(l_prompts(2)), 'RIGHT');
599 htp.tableData(htf.formText(cname => 'NAME',
600 csize => '50'), 'LEFT');
601 htp.tableRowClose;
602 htp.tableRowOpen;
603 htp.p('<td align=center colspan=2>');
604 htp.p('<table width="100%"><tr>');
605 htp.p('<td align="right" width="50%">');
606 --icx_plug_utilities.buttonLeft(l_prompts(3),'javascript:saveCreate()','FNDJLFOK.gif');
607 icx_plug_utilities.buttonLeft(BIS_UTILITIES_PVT.getPrompt('BIS_OK'),'javascript:saveCreate()');
608 htp.p('</td><td align="right" width="50%">');
609 --icx_plug_utilities.buttonRight(l_prompts(4),'javascript:window.close()','FNDJLFCN.gif');
610 icx_plug_utilities.buttonRight(BIS_UTILITIES_PVT.getPrompt('BIS_CANCEL'),'javascript:window.close()');
611 htp.p('</td></tr></table>');
612 htp.p('</td>');
613 htp.tableRowClose;
614 htp.tableClose;
615 htp.formClose;
616
617 htp.bodyClose;
618 htp.htmlClose;
619
620 end if;
621
622 exception
623 when others then
624 htp.p(SQLERRM);
625 end;
626
627 procedure FavoriteRename is
628
629 l_title varchar2(80);
630 l_prompts icx_util.g_prompts_table;
631
632 begin
633
634 if(icx_sec.validateSession)
635 then
636 icx_util.getprompts(601, 'ICX_OBIS_FAVORITE_RENAME', l_title, l_prompts);
637 --l_title := FND_MESSAGE.get_string('BIS','BIS_RENAME_LINK');
638 htp.p('<html>');
639 htp.p('<head>');
640 htp.p('<title>'||l_title||'</title>');
641 htp.p('</head>');
642
643 --- This standardizes the fonts and colors
644 --- correct spelling of Javascript (1307419)
645 htp.p('<body onload="Javascript:window.focus()">');
646 BIS_UTILITIES_PVT.putStyle;
647
648 htp.p('<SCRIPT LANGUAGE="JavaScript">');
649
650 htp.p('function loadName() {
651 var temp=parent.opener.parent.document.Favorites.C.selectedIndex;
652 document.renameFavorite.NAME.value = parent.opener.parent.document.Favorites.C.options[temp].text;
653 }');
654
655 htp.p('function saveRename() {
656 var temp=parent.opener.parent.document.Favorites.C.selectedIndex;
657
658 if (document.renameFavorite.NAME.value == "")
659 alert("'||l_prompts(1)||'");
660 else {
661 parent.opener.parent.document.Favorites.C.options[temp].text = document.renameFavorite.NAME.value;');
662 /* if instr(owa_util.get_cgi_env('HTTP_USER_AGENT'),'MSIE') = 0 then
663 htp.p('parent.opener.parent.history.go(0);');
664 end if; */
665 htp.p('window.close();
666 };
667 }');
668
669 htp.p('</SCRIPT>');
670
671 htp.formOpen('javascript:saveRename()','POST','','','NAME="renameFavorite"');
672 htp.tableOpen;
673 htp.tableRowOpen;
674 htp.tableData(htf.bold(l_prompts(1)), 'RIGHT');
675 htp.tableData(htf.formText(cname => 'NAME',
676 csize => '35'), 'LEFT');
677 htp.tableRowClose;
678 htp.tableRowOpen;
679 htp.p('<td align=center colspan=2>');
680 htp.p('<table width="100%"><tr>');
681 htp.p('<td align="right" width="50%">');
682 --icx_plug_utilities.buttonLeft(l_prompts(2),'javascript:saveRename()','FNDJLFOK.gif');
683 icx_plug_utilities.buttonLeft(BIS_UTILITIES_PVT.getPrompt('BIS_OK'),'javascript:saveRename()');
684 htp.p('</td><td align="right" width="50%">');
685 --icx_plug_utilities.buttonRight(l_prompts(3),'javascript:window.close()','FNDJLFCN.gif');
686 icx_plug_utilities.buttonRight(BIS_UTILITIES_PVT.getPrompt('BIS_CANCEL'),'javascript:window.close()');
687 htp.p('</td></tr></table>');
688 htp.p('</td>');
689 htp.tableRowClose;
690 htp.tableClose;
691 htp.formClose;
692
693 htp.p('<SCRIPT LANGUAGE="JavaScript">loadName();</SCRIPT>');
694
695 htp.bodyClose;
696 htp.htmlClose;
697
698 end if;
699
700 exception
701 when others then
702 htp.p(SQLERRM);
703
704 end;
705
706 --serao - 02/25/02 - pvt function to do the actual insert into the table
707 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links
708 -- Modified routine to introduce customization
709 FUNCTION insert_bis_custom_related_link (
710 pdisplay_sequence In NUMBER,
711 puser_link_name In VARCHAR2,
712 pfunction_id IN NUMBER,
713 presponsibility_id In NUMBER,
714 psecurity_group_id In NUMBER,
715 presponsibility_application_id In NUMBER,
716 plinked_function_id In VARCHAR2,
717 plink_type IN VARCHAR2,
718 puser_url IN VARCHAR2,
719 plevel_site_id in NUMBER,
720 plevel_responsibility_id in NUMBER,
721 plevel_application_id in NUMBER,
722 plevel_org_id in NUMBER,
723 plevel_function_id in NUMBER,
724 plevel_user_id In NUMBER,
725 pcreated_by IN NUMBER,
726 pcreation_date IN DATE DEFAULT SYSDATE,
727 plast_update_date In DATE DEFAULT SYSDATE,
728 plast_updated_by IN NUMBER,
729 plast_update_login In NUMBER
730 ) RETURN NUMBER
731 IS
732 l_related_link_id number;
733 BEGIN
734
735 select bis_related_links_s.nextval into l_related_link_id from dual;
736
737
738 insert into bis_custom_related_links
739 (related_link_id,
740 display_sequence,
741 function_id,
742 responsibility_id,
743 security_group_id,
744 responsibility_application_id,
745 linked_function_id,
746 link_type,
747 user_url,
748 level_site_id,
749 level_responsibility_id,
750 level_application_id,
751 level_org_id,
752 level_function_id,
753 level_user_id,
754 created_by,
755 creation_date,
756 last_update_date,
757 last_updated_by,
758 last_update_login
759 ) VALUES (
760 l_related_link_id,
761 pdisplay_sequence,
762 pfunction_id,
763 presponsibility_id,
764 psecurity_group_id,
765 presponsibility_application_id,
766 plinked_function_id,
767 plink_type,
768 puser_url,
769 plevel_site_id,
770 plevel_responsibility_id,
771 plevel_application_id,
772 plevel_org_id,
773 plevel_function_id,
774 plevel_user_id,
775 pcreated_by,
776 pcreation_date,
777 plast_update_date,
778 plast_updated_by,
779 plast_update_login
780 );
781
782 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links - insert into TL table
783 INSERT INTO bis_custom_related_links_tl
784 ( related_link_id,
785 user_link_name,
786 language,
787 source_lang,
788 created_by,
789 creation_date,
790 last_update_date,
791 last_updated_by,
792 last_update_login
793 )
794 select
795 l_related_link_id,
796 puser_link_name,
797 language_code,
798 userenv('LANG'),
799 pcreated_by,
800 pcreation_date,
801 plast_update_date,
802 plast_updated_by,
803 plast_update_login
804 from fnd_languages l
805 where L.INSTALLED_FLAG in ('I', 'B')
806 and not exists
807 (select null
808 from bis_custom_related_links_tl
809 where related_link_id = l_related_link_id
810 and language = l.language_code);
811
812
813 -- commit needs to be done by the caller function
814 RETURN l_related_link_id;
815 END insert_bis_custom_related_link;
816
817
818 --serao - 02/25/02 - pvt function to do the actual insert into the table
819 FUNCTION insert_bis_related_link (
820 pUser_id In NUMBER,
821 pdisplay_sequence In NUMBER,
822 puser_link_name In VARCHAR2,
823 pfunction_id IN NUMBER,
824 presponsibility_id In NUMBER,
825 psecurity_group_id In NUMBER,
826 presponsibility_application_id In NUMBER,
827 plinked_function_id In VARCHAR2,
828 plink_type IN VARCHAR2,
829 puser_url IN VARCHAR2,
830 pcreated_by IN NUMBER,
831 pcreation_date IN DATE DEFAULT SYSDATE,
832 plast_update_date In DATE DEFAULT SYSDATE,
833 plast_updated_by IN NUMBER,
834 plast_update_login In NUMBER
835 ) RETURN NUMBER
836 IS
837 l_related_link_id number;
838 BEGIN
839
840 select bis_related_links_s.nextval into l_related_link_id from dual;
841
842 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links - Changed to bis_custom_related_Links
843 insert into bis_related_links
844 (related_link_id,
845 user_id,
846 display_sequence,
847 -- user_link_name,
848 function_id,
849 responsibility_id,
850 security_group_id,
851 responsibility_application_id,
852 linked_function_id,
853 link_type,
854 user_url,
855 created_by,
856 creation_date,
857 last_update_date,
858 last_updated_by,
859 last_update_login
860 ) VALUES (
861 l_related_link_id,
862 puser_id,
863 pdisplay_sequence,
864 -- puser_link_name,
865 pfunction_id,
866 presponsibility_id,
867 psecurity_group_id,
868 presponsibility_application_id,
869 plinked_function_id,
870 plink_type,
871 puser_url,
872 pcreated_by,
873 pcreation_date,
874 plast_update_date,
875 plast_updated_by,
876 plast_update_login
877 );
878
879 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links - insert into TL table
880 INSERT INTO bis_related_links_tl
881 ( related_link_id,
882 user_link_name,
883 language,
884 source_lang,
885 created_by,
886 creation_date,
887 last_update_date,
888 last_updated_by,
889 last_update_login
890 )
891 select
892 l_related_link_id,
893 puser_link_name,
894 language_code,
895 userenv('LANG'),
896 pcreated_by,
897 pcreation_date,
898 plast_update_date,
899 plast_updated_by,
900 plast_update_login
901 from fnd_languages l
902 where L.INSTALLED_FLAG in ('I', 'B')
903 and not exists
904 (select null
905 from bis_related_links_tl
906 where related_link_id = l_related_link_id
907 and language = l.language_code);
908
909
910 -- commit needs to be done by the caller function
911 RETURN l_related_link_id;
912 END insert_bis_related_link;
913
914 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links
915 PROCEDURE add_rl_from_function (
916 pFunction_name IN VARCHAR2,
917 pUserId In NUMBER,
918 pPlugId IN NUMBER
919 ) IS
920 l_function_id number;
921
922 BEGIN
923
924 select function_id into l_function_id
925 from fnd_form_functions
926 where function_name = pFunction_name;
927
928 copyLinksFromPrevLevel (
929 l_function_id,
930 pUserId,
931 CUSTOM_USER_LEVEL,
932 pUserId,
933 pPlugId);
934 EXCEPTION
935 WHEN others THEN
936 null;
937
938 END add_rl_from_function;
939
940 PROCEDURE Build_Related_Information_HTML
941 ( p_image_directory IN VARCHAR2
942 ,p_ICX_Report_Link IN VARCHAR2
943 ,p_function_id IN NUMBER
944 ,p_Responsibility_id IN NUMBER
945 ,p_user_id IN NUMBER
946 ,p_session_id IN VARCHAR2
947 ,x_HTML IN OUT NOCOPY VARCHAR2
948 ,p_function_name in varchar2 default null
949 )
950 IS
951 BEGIN
952 NULL;
953 END Build_Related_Information_HTML;
954
955 FUNCTION getFuncIdFromParams (
956 pFunctionId IN VARCHAR2
957
958 ) RETURN VARCHAR2 IS
959 l_param fnd_form_functions.parameters%TYPE;
960 l_func_id fnd_form_functions.function_id%TYPE;
961 l_retr_func VARCHAR2(32000);
962
963 BEGIN
964
965 l_func_id := NULL;
966
967 SELECT parameters INTO l_param
968 FROM fnd_form_functions
969 WHERE function_id = pFunctionId;
970
971 IF(l_param IS NOT NULL) THEN
972 l_retr_func := BIS_PMV_UTIL.getParameterValue(l_param, 'pFunctionName');
973 END IF;
974
975 IF (l_retr_func IS NOT NULL) THEN
976
977 SELECT function_id INTO l_func_id
978 FROM fnd_form_functions
979 WHERE function_name = l_retr_func;
980
981 END IF;
982
983 RETURN l_func_id;
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 RETURN NULL;
988
989
990 END;
991
992 -- mdamle 03/12/2001 - Related Links
993 -- ansingh - Modified the procedure for Preseeding Enhancement
994 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links
995 -- Modified routine to introduce customization
996 PROCEDURE addRelatedlink_Wrapper(
997 p_user_id IN VARCHAR2,
998 p_resp_id IN VARCHAR2,
999 p_function_id IN VARCHAR2,
1000 p_linked_function_id IN VARCHAR2,
1001 p_user_link_name IN VARCHAR2,
1002 p_link_type IN VARCHAR2,
1003 p_url IN VARCHAR2,
1004 p_custom_Level IN VARCHAR2,
1005 p_custom_level_value IN VARCHAR2)
1006 IS
1007 l_retr_func_id VARCHAR2(32000);
1008 l_resp_appl_id pls_integer;
1009 l_security_group_id pls_integer;
1010 l_related_link_id pls_integer;
1011 l_index pls_integer;
1012 l_exists pls_integer;
1013 --ansingh
1014 l_resp_id pls_integer;
1015
1016 l_isPreseed varchar2(1) := 'Y';
1017 l_level_site_id number;
1018 l_level_resp_id number;
1019 l_level_app_id number;
1020 l_level_org_id number;
1021 l_level_function_id number;
1022 l_level_user_id number;
1023
1024 CURSOR displaySequence_site (p_site_id IN VARCHAR2, p_function_id IN VARCHAR2)
1025 IS
1026 SELECT max(NVL(display_sequence, 0)) + 1
1027 FROM bis_custom_related_links_v
1028 WHERE level_site_id = p_site_id
1029 AND function_id = p_function_id;
1030
1031 CURSOR displaySequence_resp (p_resp_id IN VARCHAR2, p_function_id IN VARCHAR2)
1032 IS
1033 SELECT max(NVL(display_sequence, 0)) + 1
1034 FROM bis_custom_related_links_v
1035 WHERE level_responsibility_id = p_resp_id
1036 AND function_id = p_function_id;
1037
1038 CURSOR displaySequence_app (p_app_id IN VARCHAR2, p_function_id IN VARCHAR2)
1039 IS
1040 SELECT max(NVL(display_sequence, 0)) + 1
1041 FROM bis_custom_related_links_v
1042 WHERE level_application_id = p_app_id
1043 AND function_id = p_function_id;
1044
1045 CURSOR displaySequence_org (p_org_id IN VARCHAR2, p_function_id IN VARCHAR2)
1046 IS
1047 SELECT max(NVL(display_sequence, 0)) + 1
1048 FROM bis_custom_related_links_v
1049 WHERE level_org_id = p_org_id
1050 AND function_id = p_function_id;
1051
1052 CURSOR displaySequence_function (p_level_function_id IN VARCHAR2, p_function_id IN VARCHAR2)
1053 IS
1054 SELECT max(NVL(display_sequence, 0)) + 1
1055 FROM bis_custom_related_links_v
1056 WHERE level_function_id = p_level_function_id
1057 AND function_id = p_function_id;
1058
1059 CURSOR displaySequence_user (p_user_id IN VARCHAR2, p_function_id IN VARCHAR2)
1060 IS
1061 SELECT max(NVL(display_sequence, 0)) + 1
1062 FROM bis_custom_related_links_v
1063 WHERE level_user_id = p_user_id
1064 AND function_id = p_function_id;
1065
1066 CURSOR displaySequence_seed (p_user_id IN VARCHAR2, p_function_id IN VARCHAR2, p_function_id2 IN VARCHAR2)
1067 IS
1068 SELECT max(NVL(display_sequence, 0)) + 1
1069 FROM bis_related_links_v
1070 WHERE function_id IN (p_function_id, p_function_id2);
1071
1072 CURSOR getDefaultVal (p_resp_id IN VARCHAR2)
1073 IS
1074 SELECT application_id
1075 FROM fnd_responsibility
1076 WHERE responsibility_id = p_resp_id;
1077
1078
1079 BEGIN
1080 l_index := 0;
1081
1082 IF displaySequence_seed%ISOPEN THEN
1083 CLOSE displaySequence_seed;
1084 END IF;
1085 IF displaySequence_site%ISOPEN THEN
1086 CLOSE displaySequence_site;
1087 END IF;
1088 IF displaySequence_resp%ISOPEN THEN
1089 CLOSE displaySequence_resp;
1090 END IF;
1091 IF displaySequence_app%ISOPEN THEN
1092 CLOSE displaySequence_app;
1093 END IF;
1094 IF displaySequence_org%ISOPEN THEN
1095 CLOSE displaySequence_org;
1096 END IF;
1097 IF displaySequence_function%ISOPEN THEN
1098 CLOSE displaySequence_function;
1099 END IF;
1100 IF displaySequence_user%ISOPEN THEN
1101 CLOSE displaySequence_user;
1102 END IF;
1103 IF getDefaultVal%ISOPEN THEN
1104 CLOSE getDefaultVal;
1105 END IF;
1106
1107 --get the display sequence -ansingh
1108 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links - custom table
1109 if (p_custom_level is not null) then
1110 l_isPreseed := 'N';
1111 end if;
1112 IF (l_isPreseed = 'N') THEN
1113 if p_custom_level = CUSTOM_SITE_LEVEL then
1114 OPEN displaySequence_site(p_custom_level_value, p_function_id);
1115 FETCH displaySequence_site INTO l_index;
1116 IF (displaySequence_site%NOTFOUND) OR (l_index IS NULL) THEN
1117 l_index := 1;
1118 END IF;
1119 CLOSE displaySequence_site;
1120 else
1121 if p_custom_level = CUSTOM_RESP_LEVEL then
1122 OPEN displaySequence_resp(p_custom_level_value, p_function_id);
1123 FETCH displaySequence_resp INTO l_index;
1124 IF (displaySequence_resp%NOTFOUND) OR (l_index IS NULL) THEN
1125 l_index := 1;
1126 END IF;
1127 CLOSE displaySequence_resp;
1128 else
1129 if p_custom_level = CUSTOM_APP_LEVEL then
1130 OPEN displaySequence_app(p_custom_level_value, p_function_id);
1131 FETCH displaySequence_app INTO l_index;
1132 IF (displaySequence_app%NOTFOUND) OR (l_index IS NULL) THEN
1133 l_index := 1;
1134 END IF;
1135 CLOSE displaySequence_app;
1136 else
1137 if p_custom_level = CUSTOM_ORG_LEVEL then
1138 OPEN displaySequence_org(p_custom_level_value, p_function_id);
1139 FETCH displaySequence_org INTO l_index;
1140 IF (displaySequence_org%NOTFOUND) OR (l_index IS NULL) THEN
1141 l_index := 1;
1142 END IF;
1143 CLOSE displaySequence_org;
1144 else
1145 if p_custom_level = CUSTOM_FUNCTION_LEVEL then
1146 OPEN displaySequence_function(p_custom_level_value, p_function_id);
1147 FETCH displaySequence_function INTO l_index;
1148 IF (displaySequence_function%NOTFOUND) OR (l_index IS NULL) THEN
1149 l_index := 1;
1150 END IF;
1151 CLOSE displaySequence_function;
1152 else
1153 if p_custom_level = CUSTOM_USER_LEVEL then
1154 OPEN displaySequence_user(p_user_id, p_function_id);
1155 FETCH displaySequence_user INTO l_index;
1156 IF (displaySequence_user%NOTFOUND) OR (l_index IS NULL) THEN
1157 l_index := 1;
1158 END IF;
1159 CLOSE displaySequence_user;
1160 end if;
1161 end if;
1162 end if;
1163 end if;
1164 end if;
1165 end if;
1166 else
1167
1168 l_retr_func_id := getFuncIdFromParams(p_function_id);
1169
1170 OPEN displaySequence_seed(p_user_id, p_function_id, l_retr_func_id);
1171 FETCH displaySequence_seed INTO l_index;
1172
1173 IF (displaySequence_seed%NOTFOUND) OR (l_index IS NULL) THEN
1174 l_index := 1;
1175 END IF;
1176 CLOSE displaySequence_seed;
1177 END IF;
1178
1179
1180 IF (l_isPreseed = 'N') THEN
1181 --get the security grp id, resp_appl_id etc...-ansingh
1182 l_resp_id := p_resp_id;
1183 SELECT b.responsibility_application_id,
1184 b.security_group_id
1185 INTO l_resp_appl_id,
1186 l_security_group_id
1187 FROM fnd_responsibility_vl a,
1188 FND_USER_RESP_GROUPS b
1189 WHERE a.responsibility_id = p_resp_id
1190 AND b.user_id = p_user_id
1191 AND b.RESPONSIBILITY_id = a.responsibility_id
1192 AND b.RESPONSIBILITY_application_id = a.application_id;
1193 ELSE
1194 --l_resp_id := 0;
1195 --l_security_group_id := 0;
1196 --l_resp_appl_id := 0;
1197 l_resp_id := NVL(p_resp_id,0);
1198 OPEN getDefaultVal(l_resp_id);
1199 FETCH getDefaultVal INTO l_resp_appl_id;
1200 IF (getDefaultVal%NOTFOUND) THEN
1201 l_resp_appl_id := 0;
1202 l_security_group_id := 0;
1203 END IF;
1204 l_resp_appl_id := NVL(l_resp_appl_id, 0);
1205 l_security_group_id := 0;
1206 CLOSE getDefaultVal;
1207
1208
1209 END IF;
1210
1211 IF (l_isPreseed = 'N') THEN
1212 if p_custom_level = CUSTOM_SITE_LEVEL then
1213 l_level_site_id := TO_NUMBER(p_custom_level_value);
1214 else
1215 if p_custom_level = CUSTOM_RESP_LEVEL then
1216 l_level_resp_id := TO_NUMBER(p_custom_level_value);
1217 else
1218 if p_custom_level = CUSTOM_APP_LEVEL then
1219 l_level_app_id := TO_NUMBER(p_custom_level_value);
1220 else
1221 if p_custom_level = CUSTOM_ORG_LEVEL then
1222 l_level_org_id := TO_NUMBER(p_custom_level_value);
1223 else
1224 if p_custom_level = CUSTOM_FUNCTION_LEVEL then
1225 l_level_function_id := TO_NUMBER(p_custom_level_value);
1226 else
1227 if p_custom_level = CUSTOM_USER_LEVEL then
1228 l_level_user_id := TO_NUMBER(p_custom_level_value);
1229 end if;
1230 end if;
1231 end if;
1232 end if;
1233 end if;
1234 end if;
1235
1236 l_related_link_id := INSERT_BIS_CUSTOM_RELATED_LINK (l_index, p_user_link_name, p_function_id,
1237 l_resp_id, l_security_group_id, l_resp_appl_id,
1238 p_linked_function_id, p_link_type, p_url,
1239 l_level_site_id, l_level_resp_id, l_level_app_id, l_level_org_id, l_level_function_id, l_level_user_id,
1240 -1,sysdate, sysdate, -1, -1);
1241 else
1242 l_related_link_id := INSERT_BIS_RELATED_LINK (p_user_id, l_index, p_user_link_name, p_function_id,
1243 l_resp_id, l_security_group_id, l_resp_appl_id,
1244 p_linked_function_id, p_link_type, p_url, -1,
1245 sysdate, sysdate, -1, -1);
1246 end if;
1247
1248 END addRelatedlink_Wrapper;
1249
1250
1251 -- ansingh - Preseeding Enhancement
1252 procedure PRESEED_TO_NORMAL_WRAPPER(p_user_id in varchar2,
1253 p_resp_id in varchar2,
1254 p_function_id in varchar2,
1255 p_related_link_id in varchar2,
1256 p_user_link_name in varchar2,
1257 p_link_type in varchar2,
1258 p_url in varchar2) is
1259
1260 CURSOR preseedRL is
1261 select LINK_PARAMETERS
1262 from BIS_RELATED_LINKS
1263 where RELATED_LINK_ID = p_related_link_id;
1264
1265 CURSOR updatePreseed is
1266 select LINKED_FUNCTION_ID from bis_related_links
1267 where RELATED_LINK_ID = p_related_link_id;
1268
1269 l_resp_appl_id pls_integer;
1270 l_security_group_id pls_integer;
1271 l_related_link_id pls_integer;
1272 l_index pls_integer;
1273 l_exists pls_integer;
1274 --ansingh
1275 l_linked_function_id bis_related_links.linked_function_id%TYPE;
1276 l_link_parameters VARCHAR2(4000);
1277
1278 begin
1279 if (p_link_type = 'USER_URL') then
1280 if updatePreseed%ISOPEN then
1281 close updatePreseed;
1282 end if;
1283 open updatePreseed;
1284 fetch updatePreseed into l_linked_function_id;
1285 close updatePreseed;
1286 else
1287 l_linked_function_id := p_function_id;
1288 end if;
1289
1290 if preseedRL%ISOPEN then
1291 close preseedRL;
1292 end if;
1293 open preseedRL;
1294 fetch preseedRL into l_link_parameters;
1295 close preseedRL;
1296
1297
1298 l_index := 0;
1299 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links - custom table
1300 select max(NVL(display_sequence,0)) + 1 into l_index
1301 from bis_custom_related_links_v
1302 where level_user_id = p_user_id
1303 and function_id = p_function_id;
1304
1305 select b.responsibility_application_id,
1306 b.security_group_id
1307 into l_resp_appl_id,
1308 l_security_group_id
1309 from fnd_responsibility_vl a,
1310 FND_USER_RESP_GROUPS b
1311 where a.responsibility_id = p_resp_id
1312 and b.user_id = p_user_id
1313 and b.RESPONSIBILITY_id = a.responsibility_id
1314 and b.RESPONSIBILITY_application_id = a.application_id;
1315
1316 --insert a new row into the bis_related_links_table.
1317 l_related_link_id := insert_bis_related_link (p_user_id,
1318 l_index, p_user_link_name, p_function_id,
1319 p_resp_id, l_security_group_id,l_resp_appl_id,
1320 l_linked_function_id, p_link_type, p_url,
1321 -1, sysdate, sysdate, -1, -1);
1322
1323
1324 --add the userid to link_parameters
1325 if trim(l_link_parameters) is not null then
1326 l_link_parameters := l_link_parameters || ',' || p_user_id;
1327 else
1328 l_link_parameters := p_user_id;
1329 end if;
1330 update BIS_RELATED_LINKS set LINK_PARAMETERS=l_link_parameters where RELATED_LINK_ID=p_related_link_id;
1331
1332 end PRESEED_TO_NORMAL_WRAPPER;
1333
1334
1335 -- mdamle 03/12/2001 - Related Links Wrapper for Java
1336 procedure getRespSQLWrapper
1337 ( p_user_id IN PLS_INTEGER
1338 , p_resp_sql OUT NOCOPY varchar2
1339 ) is
1340
1341 cursor responsibilities is
1342 select a.responsibility_id,
1343 a.responsibility_name,
1344 b.responsibility_application_id,
1345 b.security_group_id,
1346 fsg.SECURITY_GROUP_NAME
1347 from FND_SECURITY_GROUPS_VL fsg,
1348 fnd_responsibility_vl a,
1349 FND_USER_RESP_GROUPS b
1350 where b.user_id = p_user_id
1351 and b.start_date <= sysdate
1352 and (b.end_date is null or b.end_date > sysdate)
1353 and b.RESPONSIBILITY_id = a.responsibility_id
1354 and b.RESPONSIBILITY_application_id = a.application_id
1355 and a.version = 'W'
1356 and a.start_date <= sysdate
1357 and (a.end_date is null or a.end_date > sysdate)
1358 and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID
1359 order by responsibility_name;
1360
1361 l_resp_criteria varchar2(30000);
1362 l_total_count PLS_INTEGER;
1363 l_object object;
1364
1365 begin
1366
1367 l_object.type := 'RESPONSIBILITY';
1368 l_object.parent_menu_id := '';
1369 l_object.entry_sequence := '';
1370 l_object.menu_explode := 'Y';
1371 l_object.function_explode := 'Y';
1372 l_object.level := 0;
1373
1374 l_total_count := 0;
1375 for r in responsibilities loop
1376 g_list := g_nulllist;
1377 l_object.responsibility_id := r.responsibility_id;
1378 l_object.resp_appl_id := r.responsibility_application_id;
1379 l_object.security_group_id := r.security_group_id;
1380 listResponsibility(p_object => l_object);
1381
1382 if g_list.COUNT > 1 then
1383 if (l_total_count = 0) then
1384 l_resp_criteria := r.responsibility_id;
1385 else
1386 l_resp_criteria := l_resp_criteria || ',' || r.responsibility_id;
1387 end if;
1388 l_total_count := l_total_count + 1;
1389 end if;
1390 end loop;
1391
1392 /*
1393 p_resp_sql := 'select responsibility_id, responsibility_name from fnd_responsibility_vl where responsibility_id in ('
1394 || l_resp_criteria || ') order by responsibility_name ';
1395 */
1396 --bugfix 3857066
1397 p_resp_sql := 'select a.responsibility_id, a.responsibility_name from fnd_responsibility_vl a, fnd_user_resp_groups b
1398 where a.responsibility_id in ('|| l_resp_criteria || ')
1399 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 ';
1400
1401 end getRespSQLWrapper;
1402
1403 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links
1404 procedure deleteRelatedLink_Wrapper(p_related_link_id pls_integer, p_isPreseed IN VARCHAR2) is
1405 begin
1406 if (p_isPreseed = 'N') then
1407 delete from bis_custom_related_links where related_link_id = p_related_link_id;
1408 else
1409 delete from bis_related_links where related_link_id = p_related_link_id;
1410 end if;
1411
1412 end deleteRelatedLink_Wrapper;
1413
1414
1415 --ansingh
1416 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links
1417 PROCEDURE UPDATERELATEDLINK_WRAPPER(
1418 p_related_link_id IN PLS_INTEGER,
1419 p_related_link_name IN VARCHAR2,
1420 p_user_url IN VARCHAR2 DEFAULT NULL,
1421 p_isPreseed IN VARCHAR2)
1422 IS
1423
1424 BEGIN
1425
1426 if (p_isPreseed = 'N') then
1427 IF (p_user_url = null) THEN
1428 UPDATE BIS_CUSTOM_RELATED_LINKS_TL SET USER_LINK_NAME=p_related_link_name
1429 where RELATED_LINK_ID=p_related_link_id and language=userenv('LANG');
1430 ELSE
1431 UPDATE BIS_CUSTOM_RELATED_LINKS SET USER_URL=p_user_url WHERE RELATED_LINK_ID=p_related_link_id;
1432 UPDATE BIS_CUSTOM_RELATED_LINKS_TL SET USER_LINK_NAME=p_related_link_name
1433 WHERE RELATED_LINK_ID=p_related_link_id and language=userenv('LANG');
1434 END IF;
1435 else
1436 IF (p_user_url = null) THEN
1437 UPDATE BIS_RELATED_LINKS_TL SET USER_LINK_NAME=p_related_link_name
1438 where RELATED_LINK_ID=p_related_link_id and language=userenv('LANG');
1439 ELSE
1440 UPDATE BIS_RELATED_LINKS SET USER_URL=p_user_url WHERE RELATED_LINK_ID=p_related_link_id;
1441 UPDATE BIS_RELATED_LINKS_TL SET USER_LINK_NAME=p_related_link_name
1442 WHERE RELATED_LINK_ID=p_related_link_id and language=userenv('LANG');
1443 END IF;
1444 end if;
1445
1446 END UPDATERELATEDLINK_WRAPPER;
1447
1448 --nbarik
1449 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links - Added preseed flag
1450 procedure reorderRelatedLinks(
1451 p_content_string in varchar2,
1452 p_isPreseed IN VARCHAR2) is
1453 l_start_index NUMBER;
1454 l_end_index NUMBER;
1455 l_id varchar2(15);
1456 l_display_sequence NUMBER;
1457 begin
1458 l_start_index := 1;
1459 l_end_index := instr(p_content_string, ';', l_start_index);
1460 l_display_sequence := 1;
1461 while l_end_index > 0 loop
1462 l_id := substr(p_content_string, l_start_index, (l_end_index-l_start_index));
1463
1464 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links
1465 IF (p_isPreseed = 'N') THEN
1466 update bis_custom_related_links set DISPLAY_SEQUENCE=l_display_sequence where RELATED_LINK_ID=l_id;
1467 else
1468 update bis_related_links set DISPLAY_SEQUENCE=l_display_sequence where RELATED_LINK_ID=l_id;
1469 end if;
1470
1471 l_display_sequence := l_display_sequence+1;
1472
1473 l_start_index := l_end_index+1;
1474 l_end_index := instr(p_content_string, ';', l_start_index);
1475 end loop;
1476 end reorderRelatedLinks;
1477
1478 -- Preseed Related Links Enhancement -ansingh
1479 FUNCTION isUserIdInLinkParams (
1480 p_userId IN VARCHAR2,
1481 p_linkParams IN VARCHAR2) RETURN VARCHAR2
1482 IS
1483 l_exists VARCHAR2(5):='N';
1484
1485 BEGIN
1486 IF (p_linkParams IS NOT NULL) THEN
1487 IF ((p_linkParams = p_userId) OR (instr(p_linkParams,p_userId||',')=1)
1488 OR (instr(p_linkParams,','||p_userId||',') > 1) OR ((instr(p_linkParams,','||p_userId) > 0)
1489 AND (instr(p_linkParams,','||p_userId)=length(p_linkParams)-length(p_userId)))) THEN
1490 RETURN 'Y';
1491 END IF;
1492 END IF;
1493 RETURN l_exists;
1494 EXCEPTION
1495 WHEN OTHERS THEN
1496 RETURN l_exists;
1497
1498 END isUserIdInLinkParams;
1499
1500 /* serao -06/03, for related links */
1501 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links
1502 -- Modified routine to introduce customization
1503 PROCEDURE copyLinksFromPrevLevel (
1504 p_report_function_id IN VARCHAR2,
1505 p_user_id IN VARCHAR2,
1506 p_custom_level IN VARCHAR2,
1507 p_custom_level_value IN VARCHAR2,
1508 p_plug_id IN VARCHAR2 := NULL
1509 ) IS
1510
1511 cursor cr_seedlinks is
1512 SELECT l.display_sequence,l.user_link_name, l.function_id, l.responsibility_id, l.security_group_id,
1513 l.responsibility_application_id,l.linked_function_id, l.link_type,l.user_url
1514 FROM bis_related_links_v l
1515 WHERE l.function_id = p_report_function_id;
1516
1517 cursor cr_sitelinks is
1518 SELECT l.display_sequence,l.user_link_name, l.function_id, l.responsibility_id, l.security_group_id,
1519 l.responsibility_application_id,l.linked_function_id, l.link_type,l.user_url
1520 FROM bis_custom_related_links_v l
1521 WHERE l.function_id = p_report_function_id
1522 AND l.level_site_id is not null;
1523
1524 cursor cr_resplinks is
1525 SELECT l.display_sequence,l.user_link_name, l.function_id, l.responsibility_id, l.security_group_id,
1526 l.responsibility_application_id,l.linked_function_id, l.link_type,l.user_url
1527 FROM bis_custom_related_links_v l
1528 WHERE l.function_id = p_report_function_id
1529 AND l.level_responsibility_id is not null;
1530
1531 cursor cr_applinks is
1532 SELECT l.display_sequence,l.user_link_name, l.function_id, l.responsibility_id, l.security_group_id,
1533 l.responsibility_application_id,l.linked_function_id, l.link_type,l.user_url
1534 FROM bis_custom_related_links_v l
1535 WHERE l.function_id = p_report_function_id
1536 AND l.level_application_id is not null;
1537
1538 cursor cr_orglinks is
1539 SELECT l.display_sequence,l.user_link_name, l.function_id, l.responsibility_id, l.security_group_id,
1540 l.responsibility_application_id,l.linked_function_id, l.link_type,l.user_url
1541 FROM bis_custom_related_links_v l
1542 WHERE l.function_id = p_report_function_id
1543 AND l.level_org_id is not null;
1544
1545 cursor cr_functionlinks is
1546 SELECT l.display_sequence,l.user_link_name, l.function_id, l.responsibility_id, l.security_group_id,
1547 l.responsibility_application_id,l.linked_function_id, l.link_type,l.user_url
1548 FROM bis_custom_related_links_v l
1549 WHERE l.function_id = p_report_function_id
1550 AND l.level_function_id is not null;
1551
1552
1553 l_related_link_id NUMBER;
1554 l_level_site_id number;
1555 l_level_resp_id number;
1556 l_level_app_id number;
1557 l_level_org_id number;
1558 l_level_function_id number;
1559 l_level_user_id number;
1560 l_prev_level varchar2(10);
1561 l_function_id number;
1562
1563 BEGIN
1564
1565 l_related_link_id := 0;
1566
1567 IF cr_seedlinks%ISOPEN THEN
1568 CLOSE cr_seedlinks;
1569 END IF;
1570 IF cr_sitelinks%ISOPEN THEN
1571 CLOSE cr_sitelinks;
1572 END IF;
1573 IF cr_resplinks%ISOPEN THEN
1574 CLOSE cr_resplinks;
1575 END IF;
1576 IF cr_applinks%ISOPEN THEN
1577 CLOSE cr_applinks;
1578 END IF;
1579 IF cr_orglinks%ISOPEN THEN
1580 CLOSE cr_orglinks;
1581 END IF;
1582 IF cr_functionlinks%ISOPEN THEN
1583 CLOSE cr_functionlinks;
1584 END IF;
1585
1586 if (p_custom_level = CUSTOM_SITE_LEVEL) then
1587 l_level_site_id := p_custom_level_value;
1588 else
1589 if (p_custom_level = CUSTOM_RESP_LEVEL) then
1590 l_level_resp_id := p_custom_level_value;
1591 else
1592 if (p_custom_level = CUSTOM_APP_LEVEL) then
1593 l_level_app_id := p_custom_level_value;
1594 else
1595 if (p_custom_level = CUSTOM_ORG_LEVEL) then
1596 l_level_org_id := p_custom_level_value;
1597 else
1598 if (p_custom_level = CUSTOM_FUNCTION_LEVEL) then
1599 l_level_function_id := p_custom_level_value;
1600 else
1601 if (p_custom_level = CUSTOM_USER_LEVEL) then
1602 l_level_user_id := p_custom_level_value;
1603 end if;
1604 end if;
1605 end if;
1606 end if;
1607 end if;
1608 end if;
1609
1610 l_prev_level := getPreviousCustomizationLevel(p_report_function_id, p_custom_level);
1611
1612 if p_plug_id is not null then
1613 l_function_id := p_plug_id;
1614 else
1615 l_function_id := p_report_function_id;
1616 end if;
1617
1618 if l_prev_level is null then
1619 for l in cr_seedlinks loop
1620 select bis_related_links_s.nextval into l_related_link_id from dual;
1621 insertCustomLinks( l_related_link_id, l.display_sequence, l_function_id, l.responsibility_id, l.security_group_id,
1622 l.responsibility_application_id, l.linked_function_id, l.link_type, l.user_url, l.user_link_name,
1623 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);
1624 end loop;
1625 else
1626 if l_prev_level = CUSTOM_SITE_LEVEL then
1627 for l in cr_sitelinks loop
1628 select bis_related_links_s.nextval into l_related_link_id from dual;
1629 insertCustomLinks( l_related_link_id, l.display_sequence, l_function_id, l.responsibility_id, l.security_group_id,
1630 l.responsibility_application_id, l.linked_function_id, l.link_type, l.user_url, l.user_link_name,
1631 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);
1632 end loop;
1633 else
1634 if l_prev_level = CUSTOM_RESP_LEVEL then
1635 for l in cr_resplinks loop
1636 select bis_related_links_s.nextval into l_related_link_id from dual;
1637 insertCustomLinks( l_related_link_id, l.display_sequence, l_function_id, l.responsibility_id, l.security_group_id,
1638 l.responsibility_application_id, l.linked_function_id, l.link_type, l.user_url, l.user_link_name,
1639 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);
1640 end loop;
1641 else
1642 if l_prev_level = CUSTOM_ORG_LEVEL then
1643 for l in cr_orglinks loop
1644 select bis_related_links_s.nextval into l_related_link_id from dual;
1645 insertCustomLinks( l_related_link_id, l.display_sequence, l_function_id, l.responsibility_id, l.security_group_id,
1646 l.responsibility_application_id, l.linked_function_id, l.link_type, l.user_url, l.user_link_name,
1647 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);
1648 end loop;
1649 else
1650 if l_prev_level = CUSTOM_APP_LEVEL then
1651 for l in cr_applinks loop
1652 select bis_related_links_s.nextval into l_related_link_id from dual;
1653 insertCustomLinks( l_related_link_id, l.display_sequence, l_function_id, l.responsibility_id, l.security_group_id,
1654 l.responsibility_application_id, l.linked_function_id, l.link_type, l.user_url, l.user_link_name,
1655 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);
1656 end loop;
1657 else
1658 if l_prev_level = CUSTOM_FUNCTION_LEVEL then
1659 for l in cr_functionlinks loop
1660 select bis_related_links_s.nextval into l_related_link_id from dual;
1661 insertCustomLinks( l_related_link_id, l.display_sequence, l_function_id, l.responsibility_id, l.security_group_id,
1662 l.responsibility_application_id, l.linked_function_id, l.link_type,l.user_url,l.user_link_name,l_level_site_id,
1663 l_level_resp_id, l_level_app_id, l_level_org_id, l_level_function_id, l_level_user_id, p_user_id);
1664 end loop;
1665 end if;
1666 end if;
1667 end if;
1668 end if;
1669 end if;
1670 end if;
1671
1672 IF cr_seedlinks%ISOPEN THEN
1673 CLOSE cr_seedlinks;
1674 END IF;
1675 IF cr_sitelinks%ISOPEN THEN
1676 CLOSE cr_sitelinks;
1677 END IF;
1678 IF cr_resplinks%ISOPEN THEN
1679 CLOSE cr_resplinks;
1680 END IF;
1681 IF cr_orglinks%ISOPEN THEN
1682 CLOSE cr_orglinks;
1683 END IF;
1684 IF cr_applinks%ISOPEN THEN
1685 CLOSE cr_applinks;
1686 END IF;
1687 IF cr_functionlinks%ISOPEN THEN
1688 CLOSE cr_functionlinks;
1689 END IF;
1690
1691 END copyLinksFromPrevLevel;
1692
1693
1694 PROCEDURE insertCustomLinks(
1695 p_related_link_id in number,
1696 p_display_sequence in number,
1697 p_function_id in number,
1698 p_responsibility_id in number,
1699 p_security_group_id in number,
1700 p_responsibility_app_id in number,
1701 p_linked_function_id in number,
1702 p_link_type in varchar2,
1703 p_user_url in varchar2,
1704 p_user_link_name in varchar2,
1705 p_level_site_id in number,
1706 p_level_resp_id in number,
1707 p_level_app_id in number,
1708 p_level_org_id in number,
1709 p_level_function_id in number,
1710 p_level_user_id in number,
1711 p_user_id in number) IS
1712
1713 BEGIN
1714 INSERT INTO bis_custom_related_links
1715 (related_link_id,
1716 display_sequence,
1717 function_id,
1718 responsibility_id,
1719 security_group_id,
1720 responsibility_application_id,
1721 linked_function_id,
1722 link_type,
1723 user_url,
1724 level_site_id,
1725 level_responsibility_id,
1726 level_application_id,
1727 level_org_id,
1728 level_function_id,
1729 level_user_id,
1730 created_by,
1731 creation_date,
1732 last_update_date,
1733 last_updated_by,
1734 last_update_login
1735 )
1736 VALUES(p_related_link_id,
1737 p_display_sequence,
1738 p_function_id,
1739 p_responsibility_id,
1740 p_security_group_id,
1741 p_responsibility_app_id,
1742 p_linked_function_id,
1743 p_link_type,
1744 p_user_url,
1745 p_level_site_id,
1746 p_level_resp_id,
1747 p_level_app_id,
1748 p_level_org_id,
1749 p_level_function_id,
1750 p_level_user_id,
1751 p_user_id,
1752 SYSDATE,
1753 SYSDATE,
1754 p_user_id,
1755 p_user_id);
1756
1757 INSERT INTO bis_custom_related_links_tl
1758 ( related_link_id,
1759 user_link_name,
1760 language,
1761 source_lang,
1762 created_by,
1763 creation_date,
1764 last_update_date,
1765 last_updated_by,
1766 last_update_login
1767 )
1768 select
1769 p_related_link_id,
1770 p_user_link_name,
1771 language_code,
1772 userenv('LANG'),
1773 p_user_id,
1774 SYSDATE,
1775 SYSDATE,
1776 p_user_id,
1777 p_user_id
1778 from fnd_languages l
1779 where L.INSTALLED_FLAG in ('I', 'B')
1780 and not exists
1781 (select null
1782 from bis_custom_related_links_tl
1783 where related_link_id = p_related_link_id
1784 and language = l.language_code);
1785
1786 END insertCustomLinks;
1787
1788 function getPreviousCustomizationLevel(
1789 p_function_id in number
1790 ,p_custom_level in varchar2) RETURN VARCHAR2 IS
1791 l_user_level number;
1792 l_function_level number;
1793 l_app_level number;
1794 l_org_level number;
1795 l_resp_level number;
1796 l_site_level number;
1797 l_prev_level varchar2(10) := null;
1798
1799 BEGIN
1800
1801 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)
1802 into l_user_level, l_function_level, l_app_level, l_org_level, l_resp_level, l_site_level
1803 from bis_custom_related_links_vl
1804 where function_id = p_function_id;
1805
1806 if (p_custom_level = CUSTOM_USER_LEVEL) then
1807 if (l_function_level is not null) then
1808 l_prev_level := CUSTOM_FUNCTION_LEVEL;
1809 else
1810 if (l_app_level is not null) then
1811 l_prev_level := CUSTOM_APP_LEVEL;
1812 else
1813 if (l_org_level is not null) then
1814 l_prev_level := CUSTOM_ORG_LEVEL;
1815 else
1816 if (l_resp_level is not null) then
1817 l_prev_level := CUSTOM_RESP_LEVEL;
1818 else
1819 if (l_site_level is not null) then
1820 l_prev_level := CUSTOM_SITE_LEVEL;
1821 else
1822 l_prev_level := null;
1823 end if;
1824 end if;
1825 end if;
1826 end if;
1827 end if;
1828 else
1829 if (p_custom_level = CUSTOM_FUNCTION_LEVEL) then
1830 if (l_app_level is not null) then
1831 l_prev_level := CUSTOM_APP_LEVEL;
1832 else
1833 if (l_org_level is not null) then
1834 l_prev_level := CUSTOM_ORG_LEVEL;
1835 else
1836 if (l_resp_level is not null) then
1837 l_prev_level := CUSTOM_RESP_LEVEL;
1838 else
1839 if (l_site_level is not null) then
1840 l_prev_level := CUSTOM_SITE_LEVEL;
1841 else
1842 l_prev_level := null;
1843 end if;
1844 end if;
1845 end if;
1846 end if;
1847 else
1848 if (p_custom_level = CUSTOM_APP_LEVEL) then
1849 if (l_org_level is not null) then
1850 l_prev_level := CUSTOM_ORG_LEVEL;
1851 else
1852 if (l_resp_level is not null) then
1853 l_prev_level := CUSTOM_RESP_LEVEL;
1854 else
1855 if (l_site_level is not null) then
1856 l_prev_level := CUSTOM_SITE_LEVEL;
1857 else
1858 l_prev_level := null;
1859 end if;
1860 end if;
1861 end if;
1862 else
1863 if (p_custom_level = CUSTOM_ORG_LEVEL) then
1864 if (l_resp_level is not null) then
1865 l_prev_level := CUSTOM_RESP_LEVEL;
1866 else
1867 if (l_site_level is not null) then
1868 l_prev_level := CUSTOM_SITE_LEVEL;
1869 else
1870 l_prev_level := null;
1871 end if;
1872 end if;
1873 else
1874 if (p_custom_level = CUSTOM_RESP_LEVEL) then
1875 if (l_site_level is not null) then
1876 l_prev_level := CUSTOM_SITE_LEVEL;
1877 else
1878 l_prev_level := null;
1879 end if;
1880 else
1881 l_prev_level := null;
1882 end if;
1883 end if;
1884 end if;
1885 end if;
1886 end if;
1887
1888 return l_prev_level;
1889 END getPreviousCustomizationLevel;
1890
1891 -- mdamle 05/28/2004 - Delete API to be called from LCT file
1892 procedure delete_function_links (
1893 p_function_id IN number
1894 ,x_return_status OUT NOCOPY VARCHAR2
1895 ,x_msg_count OUT NOCOPY NUMBER
1896 ,x_msg_data OUT NOCOPY VARCHAR2
1897 ) is
1898 cursor cr_seedlinks is
1899 SELECT related_link_id
1900 FROM bis_related_links
1901 WHERE function_id = p_function_id;
1902 cursor cr_customlinks is
1903 SELECT related_link_id
1904 FROM bis_custom_related_links
1905 WHERE function_id = p_function_id;
1906 BEGIN
1907
1908 IF cr_seedlinks%ISOPEN THEN
1909 CLOSE cr_seedlinks;
1910 END IF;
1911 IF cr_customlinks%ISOPEN THEN
1912 CLOSE cr_customlinks;
1913 END IF;
1914
1915 for l in cr_seedlinks loop
1916 begin
1917 delete bis_related_links_tl
1918 where related_link_id = l.related_link_id;
1919 exception
1920 when others then null;
1921 end;
1922 end loop;
1923
1924 -- mdamle 06/29/2004 - Don't delete customized links
1925 /*
1926 for l in cr_customlinks loop
1927 begin
1928 delete bis_custom_related_links_tl
1929 where related_link_id = l.related_link_id;
1930 exception
1931 when others then null;
1932 end;
1933 end loop;
1934 */
1935
1936 begin
1937 delete bis_related_links
1938 where function_id = p_function_id;
1939 exception
1940 when others then null;
1941 end;
1942
1943 -- mdamle 06/29/2004 - Don't delete customized links
1944 /*
1945 begin
1946 delete bis_custom_related_links
1947 where function_id = p_function_id;
1948 exception
1949 when others then null;
1950 end;
1951 */
1952
1953 IF cr_seedlinks%ISOPEN THEN
1954 CLOSE cr_seedlinks;
1955 END IF;
1956 IF cr_customlinks%ISOPEN THEN
1957 CLOSE cr_customlinks;
1958 END IF;
1959
1960 EXCEPTION
1961 WHEN FND_API.G_EXC_ERROR THEN
1962 x_return_status := FND_API.G_RET_STS_ERROR;
1963 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1964 ,p_data => x_msg_data);
1965 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1967 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1968 ,p_data => x_msg_data);
1969 WHEN NO_DATA_FOUND THEN
1970 x_return_status := FND_API.G_RET_STS_ERROR;
1971 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1972 ,p_data => x_msg_data);
1973 WHEN OTHERS THEN
1974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1975 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1976 ,p_data => x_msg_data);
1977 if (x_msg_data is null) then
1978 x_msg_data := SQLERRM;
1979 end if;
1980 END delete_function_links;
1981
1982 -- mdamle 05/28/2004 - Load API to be called from LCT file
1983 procedure load_row (
1984 p_function_id IN NUMBER
1985 ,p_user_id IN NUMBER
1986 ,p_link_type IN VARCHAR2
1987 ,p_linked_function_id IN NUMBER
1988 ,p_user_url IN VARCHAR2
1989 ,p_resp_id IN NUMBER
1990 ,p_sec_grp_id IN NUMBer
1991 ,p_resp_app_id IN NUMBER
1992 ,p_display_sequence IN NUMBER
1993 ,p_user_link_name IN VARCHAR2
1994 ,p_login_id IN NUMBER
1995 ,x_return_status OUT NOCOPY VARCHAR2
1996 ,x_msg_count OUT NOCOPY NUMBER
1997 ,x_msg_data OUT NOCOPY VARCHAR2
1998 ) is
1999
2000 l_related_link_id number;
2001
2002 BEGIN
2003
2004 select bis_related_links_s.nextval into l_related_link_id from dual;
2005 INSERT INTO BIS_RELATED_LINKS
2006 (RELATED_LINK_ID,
2007 CREATION_DATE,
2008 CREATED_BY,
2009 LAST_UPDATE_DATE,
2010 LAST_UPDATED_BY,
2011 FUNCTION_ID,
2012 LINK_TYPE,
2013 LINKED_FUNCTION_ID,
2014 USER_ID,
2015 USER_URL,
2016 LAST_UPDATE_LOGIN,
2017 RESPONSIBILITY_ID,
2018 SECURITY_GROUP_ID,
2019 RESPONSIBILITY_APPLICATION_ID,
2020 DISPLAY_SEQUENCE)
2021 SELECT
2022 l_related_link_id,
2023 sysdate,
2024 p_user_id,
2025 sysdate,
2026 p_user_id,
2027 p_function_id,
2028 p_link_type,
2029 p_linked_function_id,
2030 p_user_id,
2031 p_user_url,
2032 p_login_id,
2033 p_resp_id,
2034 p_sec_grp_id,
2035 p_resp_app_id,
2036 p_display_sequence
2037 FROM DUAL;
2038
2039 INSERT INTO bis_related_links_tl
2040 (related_link_id,
2041 user_link_name,
2042 language,
2043 source_lang,
2044 created_by,
2045 creation_date,
2046 last_update_date,
2047 last_updated_by,
2048 last_update_login
2049 )
2050 select
2051 l_related_link_id,
2052 p_user_link_name,
2053 language_code,
2054 userenv('LANG'),
2055 p_user_id,
2056 SYSDATE,
2057 SYSDATE,
2058 p_user_id,
2059 p_login_id
2060 from fnd_languages l
2061 where L.INSTALLED_FLAG in ('I', 'B')
2062 and not exists
2063 (select null
2064 from bis_related_links_tl
2065 where related_link_id = l_related_link_id
2066 and language = l.language_code);
2067 EXCEPTION
2068 WHEN FND_API.G_EXC_ERROR THEN
2069 x_return_status := FND_API.G_RET_STS_ERROR;
2070 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2071 ,p_data => x_msg_data);
2072 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2074 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2075 ,p_data => x_msg_data);
2076 WHEN NO_DATA_FOUND THEN
2077 x_return_status := FND_API.G_RET_STS_ERROR;
2078 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2079 ,p_data => x_msg_data);
2080 WHEN OTHERS THEN
2081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2082 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2083 ,p_data => x_msg_data);
2084 if (x_msg_data is null) then
2085 x_msg_data := SQLERRM;
2086 end if;
2087 END load_row;
2088
2089
2090 -- mdamle 05/28/2004 - Translate API to be called from LCT file
2091 procedure translate_row (
2092 p_function_id IN NUMBER
2093 ,p_display_sequence IN VARCHAR2
2094 ,p_user_link_name IN VARCHAR2
2095 ,p_user_id IN NUMBER
2096 ,p_login_id IN NUMBER
2097 ,x_return_status OUT NOCOPY VARCHAR2
2098 ,x_msg_count OUT NOCOPY NUMBER
2099 ,x_msg_data OUT NOCOPY VARCHAR2
2100 ) is
2101
2102 l_related_link_id number;
2103
2104 BEGIN
2105
2106 begin
2107 select related_link_id into l_related_link_id
2108 from bis_related_links
2109 where function_id = p_function_id
2110 and display_sequence = p_display_sequence;
2111 exception
2112 when others then null;
2113 end;
2114
2115 if l_related_link_id is not null then
2116 begin
2117 update bis_related_links_tl
2118 set user_link_name = p_user_link_name,
2119 last_update_date = sysdate,
2120 last_updated_by = p_user_id,
2121 last_update_login = p_login_id,
2122 source_lang = userenv('LANG')
2123 where related_link_id = l_related_link_id
2124 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
2125 exception
2126 when others then null;
2127 end;
2128 end if;
2129
2130
2131 EXCEPTION
2132 WHEN FND_API.G_EXC_ERROR THEN
2133 x_return_status := FND_API.G_RET_STS_ERROR;
2134 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2135 ,p_data => x_msg_data);
2136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2138 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2139 ,p_data => x_msg_data);
2140 WHEN NO_DATA_FOUND THEN
2141 x_return_status := FND_API.G_RET_STS_ERROR;
2142 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2143 ,p_data => x_msg_data);
2144 WHEN OTHERS THEN
2145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2146 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2147 ,p_data => x_msg_data);
2148 if (x_msg_data is null) then
2149 x_msg_data := SQLERRM;
2150 end if;
2151 END translate_row;
2152
2153 procedure copy_report_links (
2154 p_source_function_id IN number
2155 ,p_dest_function_id IN number
2156 ,p_user_id IN number
2157 ,x_return_status OUT NOCOPY VARCHAR2
2158 ,x_msg_count OUT NOCOPY NUMBER
2159 ,x_msg_data OUT NOCOPY VARCHAR2
2160 ) is
2161 cursor cr_seedlinks is
2162 SELECT function_id, link_type, linked_function_id, user_url, display_Sequence,
2163 responsibility_id, security_group_id, responsibility_application_id,
2164 user_link_name
2165 FROM bis_related_links_vl
2166 WHERE function_id = p_source_function_id;
2167
2168 l_related_link_id number;
2169 BEGIN
2170
2171 IF cr_seedlinks%ISOPEN THEN
2172 CLOSE cr_seedlinks;
2173 END IF;
2174
2175 delete_function_links(
2176 p_function_id => p_dest_function_id,
2177 x_return_status => x_return_status,
2178 x_msg_count => x_msg_count,
2179 x_msg_data => x_msg_data);
2180
2181 for l in cr_seedlinks loop
2182 l_related_link_id := insert_bis_related_link (
2183 pUser_id => p_user_id,
2184 pdisplay_sequence => l.display_sequence,
2185 puser_link_name => l.user_link_name,
2186 pfunction_id => p_dest_function_id,
2187 presponsibility_id => l.responsibility_id,
2188 psecurity_group_id => l.security_group_id,
2189 presponsibility_application_id => l.responsibility_application_id,
2190 plinked_function_id => l.linked_function_id,
2191 plink_type => l.link_type,
2192 puser_url => l.user_url,
2193 pcreated_by => p_user_id,
2194 plast_updated_by => p_user_id,
2195 plast_update_login => p_user_id);
2196 end loop;
2197
2198 IF cr_seedlinks%ISOPEN THEN
2199 CLOSE cr_seedlinks;
2200 END IF;
2201
2202 EXCEPTION
2203 WHEN FND_API.G_EXC_ERROR THEN
2204 x_return_status := FND_API.G_RET_STS_ERROR;
2205 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2206 ,p_data => x_msg_data);
2207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2209 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2210 ,p_data => x_msg_data);
2211 WHEN NO_DATA_FOUND THEN
2212 x_return_status := FND_API.G_RET_STS_ERROR;
2213 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2214 ,p_data => x_msg_data);
2215 WHEN OTHERS THEN
2216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2217 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2218 ,p_data => x_msg_data);
2219 if (x_msg_data is null) then
2220 x_msg_data := SQLERRM;
2221 end if;
2222 END copy_report_links;
2223
2224 --msaran: SQL Literals projects
2225 procedure getRespFuncnSQLWrap
2226 ( p_user_id IN PLS_INTEGER
2227 , p_resp_id IN PLS_INTEGER
2228 , p_report_function_id IN PLS_INTEGER
2229 , p_search_criteria IN VARCHAR2
2230 , p_funcn_sql OUT NOCOPY VARCHAR2
2231 , p_bind_count OUT NOCOPY NUMBER
2232 , p_bind_string OUT NOCOPY VARCHAR2
2233 ) is
2234
2235 l_function_bindstring varchar2(25000);
2236 l_menu_bindstring varchar2(25000);
2237
2238 l_function_criteria varchar2(25000);
2239 l_function_count NUMBER;
2240 l_menu_criteria varchar2(25000);
2241 l_menu_count NUMBER;
2242
2243 l_total_count PLS_INTEGER;
2244 l_menu_id PLS_INTEGER;
2245 l_object object;
2246 l_bind_count NUMBER := 1;
2247 begin
2248 l_function_criteria := '-1';
2249 l_function_count := 1;
2250 l_menu_criteria := '-1';
2251 l_menu_count := 1;
2252 select a.responsibility_id,
2253 b.responsibility_application_id,
2254 b.security_group_id,
2255 a.menu_id
2256 into l_object.responsibility_id,
2257 l_object.resp_appl_id,
2258 l_object.security_group_id,
2259 l_menu_id
2260 from FND_SECURITY_GROUPS_VL fsg,
2261 fnd_responsibility_vl a,
2262 FND_USER_RESP_GROUPS b
2263 where a.responsibility_id = p_resp_id
2264 and b.user_id = p_user_id
2265 and b.start_date <= sysdate
2266 and (b.end_date is null or b.end_date > sysdate)
2267 and b.RESPONSIBILITY_id = a.responsibility_id
2268 and b.RESPONSIBILITY_application_id = a.application_id
2269 and a.version = 'W'
2270 and a.start_date <= sysdate
2271 and (a.end_date is null or a.end_date > sysdate)
2272 and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID;
2273
2274
2275 l_total_count := 0;
2276
2277 l_object.type := 'RESPONSIBILITY';
2278 l_object.parent_menu_id := '';
2279 l_object.entry_sequence := '';
2280 l_object.menu_explode := 'Y';
2281 l_object.function_explode := 'Y';
2282 l_object.level := 0;
2283
2284 g_list := g_nulllist;
2285 listResponsibility(p_object => l_object);
2286
2287 -- Build IN clause for functions
2288 for i in 1..g_list.LAST loop
2289 if g_list(i).type = 'FUNCTION' then
2290 if (l_total_count = 0) then
2291 l_function_criteria := g_list(i).function_id;
2292 l_menu_criteria := g_list(i).parent_menu_id;
2293 else
2294 l_function_criteria := l_function_criteria || ',' || g_list(i).function_id;
2295 l_function_count := l_function_count + 1;
2296 l_menu_criteria := l_menu_criteria || ',' || g_list(i).parent_menu_id;
2297 l_menu_count := l_menu_count + 1;
2298 end if;
2299 l_total_count := l_total_count + 1;
2300 end if;
2301 end loop;
2302
2303 --user_function_name should be used for the Prompt -ansingh
2304 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links - Changed to bis_custom_related_Links_v
2305 p_funcn_sql := ' SELECT mev.function_id AS FunctionId,
2306 fff.user_function_name AS Prompt,
2307 nvl(fff.description, nvl(mev.description, mev.prompt)) AS Description,
2308 ''N'' as DummySelect,
2309 NULL as ReportUrl
2310 FROM
2311 FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
2312 WHERE
2313 mev.function_id=fff.function_id
2314 AND mev.function_id in (';
2315 --function criteria
2316 l_function_bindstring := '';
2317 for i in 1..l_function_count loop
2318 if (i <> 1) then
2319 l_function_bindstring := l_function_bindstring || ',';
2320 end if;
2321 l_function_bindstring := l_function_bindstring || ':' || l_bind_count;
2322 l_bind_count := l_bind_count + 1;
2323 end loop;
2324 p_funcn_sql := p_funcn_sql || l_function_bindstring || ')
2325 AND mev.menu_id in (';
2326 p_bind_string := l_function_criteria;
2327
2328 l_menu_bindstring := '';
2329 for i in 1..l_menu_count loop
2330 if (i <> 1) then
2331 l_menu_bindstring := l_menu_bindstring || ',';
2332 end if;
2333 l_menu_bindstring := l_menu_bindstring || ':' || l_bind_count;
2334 l_bind_count := l_bind_count + 1;
2335 end loop;
2336 p_funcn_sql := p_funcn_sql || l_menu_bindstring || ')
2337 AND lower(fff.user_function_name) like lower(''' || p_search_criteria || '''';
2338 p_bind_string := p_bind_string || ',' || l_menu_criteria;
2339 p_funcn_sql := p_funcn_sql || ')
2340 AND mev.function_id in (select linked_function_id
2341 from bis_custom_related_Links_v rl
2342 where level_user_id = :' || l_bind_count;
2343 p_bind_string := p_bind_string || ',' || p_user_id;
2344 l_bind_count := l_bind_count + 1;
2345 p_funcn_sql := p_funcn_sql || ' and responsibility_id = :' || l_bind_count;
2346 p_bind_string := p_bind_string || ',' || p_resp_id;
2347 l_bind_count := l_bind_count + 1;
2348 p_funcn_sql := p_funcn_sql || ' and function_id = :' || l_bind_count;
2349 p_bind_string := p_bind_string || ',' || p_report_function_id;
2350 l_bind_count := l_bind_count + 1;
2351 p_funcn_sql := p_funcn_sql || ' and link_type = ''WWW'')
2352 UNION
2353 SELECT mev.function_id as FunctionId,
2354 fff.user_function_name AS Prompt,
2355 nvl(fff.description, nvl(mev.description, mev.prompt)) as Description,
2356 ''N'' as DummySelect,
2357 NULL as ReportUrl
2358 FROM
2359 FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
2360 where
2361 mev.function_id=fff.function_id
2362 AND mev.function_id in (';
2363
2364 l_function_bindstring := '';
2365 for i in 1..l_function_count loop
2366 if (i <> 1) then
2367 l_function_bindstring := l_function_bindstring || ',';
2368 end if;
2369 l_function_bindstring := l_function_bindstring || ':' || l_bind_count;
2370 l_bind_count := l_bind_count + 1;
2371 end loop;
2372
2373 p_funcn_sql := p_funcn_sql || l_function_bindstring || ')
2374 AND mev.menu_id in (';
2375 p_bind_string := p_bind_string || ',' || l_function_criteria;
2376 l_menu_bindstring := '';
2377 for i in 1..l_menu_count loop
2378 if (i <> 1) then
2379 l_menu_bindstring := l_menu_bindstring || ',';
2380 end if;
2381 l_menu_bindstring := l_menu_bindstring || ':' || l_bind_count;
2382 l_bind_count := l_bind_count + 1;
2383 end loop;
2384 p_funcn_sql := p_funcn_sql || l_menu_bindstring || ')
2385 AND lower(fff.user_function_name) like lower(''' || p_search_criteria || '''';
2386 p_bind_string := p_bind_string || ',' || l_menu_criteria;
2387 p_funcn_sql := p_funcn_sql || ')
2388 AND mev.function_id not in (select linked_function_id
2389 from bis_custom_related_Links_v rl
2390 where level_user_id = :' || l_bind_count;
2391 p_bind_string := p_bind_string || ',' || p_user_id;
2392 l_bind_count := l_bind_count + 1;
2393 p_funcn_sql := p_funcn_sql || ' and responsibility_id = :' || l_bind_count;
2394 p_bind_string := p_bind_string || ',' || p_resp_id;
2395 l_bind_count := l_bind_count + 1;
2396 p_funcn_sql := p_funcn_sql || ' and function_id = :' || l_bind_count;
2397 p_bind_string := p_bind_string || ',' || p_report_function_id;
2398 p_funcn_sql := p_funcn_sql || ' and link_type = ''WWW'')
2399 ORDER by 2';
2400
2401 p_bind_count := l_bind_count;
2402
2403 end getRespFuncnSQLWrap;
2404
2405 --msaran: SQL Literals projects
2406 procedure getAllRespFuncnSQLWrap
2407 ( p_user_id IN PLS_INTEGER
2408 , p_resp_id IN VARCHAR2
2409 , p_report_function_id IN PLS_INTEGER
2410 , p_search_criteria IN VARCHAR2
2411 , p_funcn_sql OUT NOCOPY VARCHAR2
2412 , p_bind_count OUT NOCOPY NUMBER
2413 , p_bind_string OUT NOCOPY VARCHAR2
2414 ) is
2415
2416 l_function_bindstring varchar2(2000);
2417 l_menu_bindstring varchar2(2000);
2418
2419 l_function_criteria varchar2(3000);
2420 l_function_count NUMBER;
2421 l_menu_criteria varchar2(3000);
2422 l_menu_count NUMBER;
2423
2424 --nkishore BugFix 2727839, adding -1
2425 l_total_count PLS_INTEGER;
2426 l_menu_id PLS_INTEGER;
2427 l_object object;
2428
2429 l_start_index NUMBER;
2430 l_end_index NUMBER;
2431 l_id varchar2(15);
2432
2433 l_resp_count NUMBER := 0;
2434 l_bind_count NUMBER := 1;
2435
2436 begin
2437
2438 l_function_criteria := '-1';
2439 l_function_count := 1;
2440 l_menu_criteria := '-1';
2441 l_menu_count := 1;
2442
2443 l_start_index := 1;
2444 l_end_index := instr(p_resp_id, ',', l_start_index);
2445 l_total_count := 0;
2446
2447 while l_end_index > 0 loop
2448 l_resp_count := l_resp_count + 1;
2449 l_id := substr(p_resp_id, l_start_index, (l_end_index-l_start_index));
2450
2451 select a.responsibility_id,
2452 b.responsibility_application_id,
2453 b.security_group_id,
2454 a.menu_id
2455 into l_object.responsibility_id,
2456 l_object.resp_appl_id,
2457 l_object.security_group_id,
2458 l_menu_id
2459 from FND_SECURITY_GROUPS_VL fsg,
2460 fnd_responsibility_vl a,
2461 FND_USER_RESP_GROUPS b
2462 where a.responsibility_id = l_id
2463 and b.user_id = p_user_id
2464 and b.start_date <= sysdate
2465 and (b.end_date is null or b.end_date > sysdate)
2466 and b.RESPONSIBILITY_id = a.responsibility_id
2467 and b.RESPONSIBILITY_application_id = a.application_id
2468 and a.version = 'W'
2469 and a.start_date <= sysdate
2470 and (a.end_date is null or a.end_date > sysdate)
2471 and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID;
2472
2473
2474 l_object.type := 'RESPONSIBILITY';
2475 l_object.parent_menu_id := '';
2476 l_object.entry_sequence := '';
2477 l_object.menu_explode := 'Y';
2478 l_object.function_explode := 'Y';
2479 l_object.level := 0;
2480
2481 g_list := g_nulllist;
2482
2483
2484 listResponsibility(p_object => l_object);
2485
2486 -- Build IN clause for functions
2487 for i in 1..g_list.LAST loop
2488 if g_list(i).type = 'FUNCTION' then
2489 if (l_total_count = 0) then
2490 l_function_criteria := g_list(i).function_id;
2491 l_menu_criteria := g_list(i).parent_menu_id;
2492 else
2493 if (instr(l_function_criteria,',' || g_list(i).function_id)<1) then
2494 l_function_criteria := l_function_criteria || ',' || g_list(i).function_id;
2495 l_function_count := l_function_count + 1;
2496 end if;
2497 if (instr(l_menu_criteria,',' || g_list(i).parent_menu_id)<1) then
2498 l_menu_criteria := l_menu_criteria || ',' || g_list(i).parent_menu_id;
2499 l_menu_count := l_menu_count + 1;
2500 end if;
2501 end if;
2502 l_total_count := l_total_count + 1;
2503 end if;
2504 end loop;
2505
2506 l_start_index := l_end_index+1;
2507 l_end_index := instr(p_resp_id, ',', l_start_index);
2508
2509 end loop;
2510
2511 --user_function_name should be used for the Prompt -ansingh
2512 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links - Changed to bis_custom_related_Links_v
2513 p_funcn_sql := ' select mev.function_id as FunctionId,
2514 fff.user_function_name AS Prompt,
2515 nvl(fff.description, nvl(mev.description, mev.prompt)) as Description,
2516 ''N'' as DummySelect,
2517 null as ReportUrl
2518 from FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
2519 where mev.function_id=fff.function_id
2520 AND mev.function_id in (';
2521 l_function_bindstring := '';
2522 for i in 1..l_function_count loop
2523 if (i <> 1) then
2524 l_function_bindstring := l_function_bindstring || ',';
2525 end if;
2526 l_function_bindstring := l_function_bindstring || ':' || l_bind_count;
2527 l_bind_count := l_bind_count + 1;
2528 end loop;
2529 p_funcn_sql := p_funcn_sql || l_function_bindstring || ')
2530 AND mev.menu_id in (';
2531 p_bind_string := l_function_criteria;
2532
2533 l_menu_bindstring := '';
2534 for i in 1..l_menu_count loop
2535 if (i <> 1) then
2536 l_menu_bindstring := l_menu_bindstring || ',';
2537 end if;
2538 l_menu_bindstring := l_menu_bindstring || ':' || l_bind_count;
2539 l_bind_count := l_bind_count + 1;
2540 end loop;
2541 p_funcn_sql := p_funcn_sql || l_menu_bindstring || ')
2542 and lower(fff.user_function_name) like lower(:'|| l_bind_count;
2543 p_bind_string := p_bind_string || ',' || l_menu_criteria;
2544 p_bind_string := p_bind_string || ',' || p_search_criteria;
2545 l_bind_count := l_bind_count + 1;
2546 p_funcn_sql := p_funcn_sql || ')
2547 and mev.function_id in (select linked_function_id
2548 from bis_custom_related_Links_v rl
2549 where level_user_id = :' || l_bind_count;
2550 p_bind_string := p_bind_string || ',' || p_user_id;
2551 l_bind_count := l_bind_count + 1;
2552 p_funcn_sql := p_funcn_sql || ' and responsibility_id in ( ';
2553 for i in 1..l_resp_count loop
2554 if (i <> 1) then
2555 p_funcn_sql := p_funcn_sql || ',';
2556 end if;
2557 p_funcn_sql := p_funcn_sql || ':' || l_bind_count;
2558 l_bind_count := l_bind_count + 1;
2559 end loop;
2560 p_funcn_sql := p_funcn_sql || ',:' || l_bind_count; --for '-1'
2561 l_bind_count := l_bind_count + 1;
2562 p_bind_string := p_bind_string || ',' || p_resp_id || '-1';
2563 p_funcn_sql := p_funcn_sql || ')
2564 and function_id = :' || l_bind_count;
2565 p_bind_string := p_bind_string || ',' || p_report_function_id;
2566 l_bind_count := l_bind_count + 1;
2567 p_funcn_sql := p_funcn_sql || ' and link_type = ''WWW'')
2568 union
2569 select mev.function_id as FunctionId,
2570 fff.user_function_name AS Prompt,
2571 nvl(fff.description, nvl(mev.description, mev.prompt)) as Description,
2572 ''N'' as DummySelect,
2573 null as ReportUrl
2574 from FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
2575 where mev.function_id=fff.function_id
2576 and mev.function_id in (';
2577 l_function_bindstring := '';
2578 for i in 1..l_function_count loop
2579 if (i <> 1) then
2580 l_function_bindstring := l_function_bindstring || ',';
2581 end if;
2582 l_function_bindstring := l_function_bindstring || ':' || l_bind_count;
2583 l_bind_count := l_bind_count + 1;
2584 end loop;
2585 p_funcn_sql := p_funcn_sql || l_function_bindstring || ')
2586 AND mev.menu_id in (';
2587 p_bind_string := p_bind_string || ',' || l_function_criteria;
2588
2589 l_menu_bindstring := '';
2590 for i in 1..l_menu_count loop
2591 if (i <> 1) then
2592 l_menu_bindstring := l_menu_bindstring || ',';
2593 end if;
2594 l_menu_bindstring := l_menu_bindstring || ':' || l_bind_count;
2595 l_bind_count := l_bind_count + 1;
2596 end loop;
2597 p_funcn_sql := p_funcn_sql || l_menu_bindstring || ')
2598 and lower(fff.user_function_name) like lower(:'|| l_bind_count;
2599 p_bind_string := p_bind_string || ',' || l_menu_criteria;
2600 p_bind_string := p_bind_string || ',' || p_search_criteria;
2601 l_bind_count := l_bind_count + 1;
2602 p_funcn_sql := p_funcn_sql || ')
2603 and mev.function_id not in (select linked_function_id
2604 from bis_custom_related_Links_v rl
2605 where level_user_id = :' || l_bind_count;
2606 p_bind_string := p_bind_string || ',' || p_user_id;
2607 l_bind_count := l_bind_count + 1;
2608 p_funcn_sql := p_funcn_sql || ' and responsibility_id in ( ';
2609 for i in 1..l_resp_count loop
2610 if (i <> 1) then
2611 p_funcn_sql := p_funcn_sql || ',';
2612 end if;
2613 p_funcn_sql := p_funcn_sql || ':' || l_bind_count;
2614 l_bind_count := l_bind_count + 1;
2615 end loop;
2616 p_funcn_sql := p_funcn_sql || ',:' || l_bind_count; --for '-1'
2617 l_bind_count := l_bind_count + 1;
2618 p_bind_string := p_bind_string || ',' || p_resp_id || '-1';
2619 p_funcn_sql := p_funcn_sql || ')
2620 and function_id = :' || l_bind_count;
2621 p_bind_string := p_bind_string || ',' || p_report_function_id;
2622 p_funcn_sql := p_funcn_sql || ' and link_type = ''WWW'')
2623 order by 2';
2624
2625 p_bind_count := l_bind_count;
2626
2627 exception
2628 when others then
2629 htp.p(SQLERRM);
2630
2631 end getAllRespFuncnSQLWrap;
2632
2633 --msaran: SQL Literals projects
2634 procedure getRespRLPortletsSQLWrap
2635 ( p_user_id IN PLS_INTEGER
2636 , p_resp_id IN VARCHAR2
2637 , p_report_function_id IN PLS_INTEGER
2638 , p_search_criteria IN VARCHAR2
2639 , p_funcn_sql OUT NOCOPY VARCHAR2
2640 , p_bind_count OUT NOCOPY NUMBER
2641 , p_bind_string OUT NOCOPY VARCHAR2
2642 )
2643 IS
2644
2645 l_function_bindstring varchar2(2000);
2646 l_menu_bindstring varchar2(2000);
2647
2648 l_function_criteria varchar2(3000);
2649 l_function_count NUMBER;
2650 l_menu_criteria varchar2(3000);
2651 l_menu_count NUMBER;
2652
2653 l_total_count PLS_INTEGER;
2654 l_menu_id PLS_INTEGER;
2655 l_object object;
2656
2657 l_start_index NUMBER;
2658 l_end_index NUMBER;
2659 l_id varchar2(15);
2660 l_bind_count NUMBER := 1;
2661
2662 begin
2663
2664 l_function_criteria := '-1';
2665 l_function_count := 1;
2666 l_menu_criteria := '-1';
2667 l_menu_count := 1;
2668
2669 select a.responsibility_id,
2670 b.responsibility_application_id,
2671 b.security_group_id,
2672 a.menu_id
2673 into l_object.responsibility_id,
2674 l_object.resp_appl_id,
2675 l_object.security_group_id,
2676 l_menu_id
2677 from FND_SECURITY_GROUPS_VL fsg,
2678 fnd_responsibility_vl a,
2679 FND_USER_RESP_GROUPS b
2680 where a.responsibility_id = p_resp_id
2681 and b.user_id = p_user_id
2682 and b.start_date <= sysdate
2683 and (b.end_date is null or b.end_date > sysdate)
2684 and b.RESPONSIBILITY_id = a.responsibility_id
2685 and b.RESPONSIBILITY_application_id = a.application_id
2686 and a.version = 'W'
2687 and a.start_date <= sysdate
2688 and (a.end_date is null or a.end_date > sysdate)
2689 and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID;
2690
2691
2692 l_total_count := 0;
2693
2694 l_object.type := 'RESPONSIBILITY';
2695 l_object.parent_menu_id := '';
2696 l_object.entry_sequence := '';
2697 l_object.menu_explode := 'Y';
2698 l_object.function_explode := 'Y';
2699 l_object.level := 0;
2700 -- nbarik - 03/23/04- Bug Fix 3511444
2701 l_object.show_all_entries := 'Y';
2702 g_list := g_nulllist;
2703 listResponsibility(p_object => l_object);
2704
2705 -- Build IN clause for functions
2706 FOR i IN 1..g_list.LAST LOOP
2707 IF g_list(i).type = 'WEBPORTLET' THEN
2708 IF (l_total_count = 0) THEN
2709 l_function_criteria := g_list(i).function_id;
2710 l_menu_criteria := g_list(i).parent_menu_id;
2711 ELSE
2712 l_function_criteria := l_function_criteria || ',' || g_list(i).function_id;
2713 l_function_count := l_function_count + 1;
2714 l_menu_criteria := l_menu_criteria || ',' || g_list(i).parent_menu_id;
2715 l_menu_count := l_menu_count + 1;
2716 END IF;
2717 l_total_count := l_total_count + 1;
2718 END IF;
2719 END LOOP;
2720
2721 p_funcn_sql := 'SELECT mev.function_id as FunctionId,
2722 NVL(mev.prompt, fff.function_name) AS Prompt,
2723 nvl(fff.description, nvl(mev.description, mev.prompt)) AS Description,
2724 ''N'' AS DummySelect,
2725 NULL AS ReportUrl
2726 FROM FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
2727 WHERE mev.function_id IN (';
2728 l_function_bindstring := '';
2729 for i in 1..l_function_count loop
2730 if (i <> 1) then
2731 l_function_bindstring := l_function_bindstring || ',';
2732 end if;
2733 l_function_bindstring := l_function_bindstring || ':' || l_bind_count;
2734 l_bind_count := l_bind_count + 1;
2735 end loop;
2736 p_funcn_sql := p_funcn_sql || l_function_bindstring;
2737 p_bind_string := l_function_criteria;
2738 p_funcn_sql := p_funcn_sql || ')
2739 AND mev.function_id=fff.function_id
2740 AND mev.menu_id IN (';
2741 l_menu_bindstring := '';
2742 for i in 1..l_menu_count loop
2743 if (i <> 1) then
2744 l_menu_bindstring := l_menu_bindstring || ',';
2745 end if;
2746 l_menu_bindstring := l_menu_bindstring || ':' || l_bind_count;
2747 l_bind_count := l_bind_count + 1;
2748 end loop;
2749 p_funcn_sql := p_funcn_sql || l_menu_bindstring;
2750 p_bind_string := p_bind_string || ',' || l_menu_criteria;
2751 p_funcn_sql := p_funcn_sql || ')
2752 AND LOWER(NVL(mev.prompt, fff.function_name)) LIKE LOWER(''' || p_search_criteria || '''';
2753 p_funcn_sql := p_funcn_sql || ')
2754 AND mev.function_id IN (
2755 SELECT linked_function_id
2756 FROM BIS_CUSTOM_RELATED_LINKS_V rl
2757 WHERE level_user_id = :';
2758 p_funcn_sql := p_funcn_sql || l_bind_count;
2759 l_bind_count := l_bind_count + 1;
2760 p_bind_string := p_bind_string || ',' || p_user_id;
2761 p_funcn_sql := p_funcn_sql || '
2762 AND responsibility_id = :';
2763 p_funcn_sql := p_funcn_sql || l_bind_count;
2764 l_bind_count := l_bind_count + 1;
2765 p_bind_string := p_bind_string || ',' || p_resp_id;
2766 p_funcn_sql := p_funcn_sql || '
2767 AND function_id = :';
2768 p_funcn_sql := p_funcn_sql || l_bind_count;
2769 l_bind_count := l_bind_count + 1;
2770 p_bind_string := p_bind_string || ',' || p_report_function_id;
2771 p_funcn_sql := p_funcn_sql || '
2772 AND link_type = ''WWW'')
2773 UNION
2774 SELECT mev.function_id AS FunctionId,
2775 NVL(mev.prompt, fff.function_name) AS Prompt,
2776 nvl(fff.description, nvl(mev.description, mev.prompt)) AS Description,
2777 ''N'' AS DummySelect,
2778 NULL AS ReportUrl
2779 FROM FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
2780 WHERE mev.function_id IN (';
2781 l_function_bindstring := '';
2782 for i in 1..l_function_count loop
2783 if (i <> 1) then
2784 l_function_bindstring := l_function_bindstring || ',';
2785 end if;
2786 l_function_bindstring := l_function_bindstring || ':' || l_bind_count;
2787 l_bind_count := l_bind_count + 1;
2788 end loop;
2789 p_funcn_sql := p_funcn_sql || l_function_bindstring;
2790 p_bind_string := p_bind_string || ',' || l_function_criteria;
2791 p_funcn_sql := p_funcn_sql || ')
2792 AND mev.function_id=fff.function_id
2793 AND mev.menu_id IN (';
2794 l_menu_bindstring := '';
2795 for i in 1..l_menu_count loop
2796 if (i <> 1) then
2797 l_menu_bindstring := l_menu_bindstring || ',';
2798 end if;
2799 l_menu_bindstring := l_menu_bindstring || ':' || l_bind_count;
2800 l_bind_count := l_bind_count + 1;
2801 end loop;
2802 p_funcn_sql := p_funcn_sql || l_menu_bindstring;
2803 p_bind_string := p_bind_string || ',' || l_menu_criteria;
2804 p_funcn_sql := p_funcn_sql || ')
2805 AND LOWER(NVL(mev.prompt, fff.function_name)) LIKE LOWER(''' || p_search_criteria || '''';
2806 p_funcn_sql := p_funcn_sql || ')
2807 AND mev.function_id NOT IN (
2808 SELECT linked_function_id
2809 FROM BIS_CUSTOM_RELATED_LINKS_V rl
2810 WHERE level_user_id = :';
2811 p_funcn_sql := p_funcn_sql || l_bind_count;
2812 l_bind_count := l_bind_count + 1;
2813 p_bind_string := p_bind_string || ',' || p_user_id;
2814 p_funcn_sql := p_funcn_sql || '
2815 AND responsibility_id = :';
2816 p_funcn_sql := p_funcn_sql || l_bind_count;
2817 l_bind_count := l_bind_count + 1;
2818 p_bind_string := p_bind_string || ',' || p_resp_id;
2819 p_funcn_sql := p_funcn_sql || '
2820 AND function_id = :';
2821 p_funcn_sql := p_funcn_sql || l_bind_count;
2822 p_bind_string := p_bind_string || ',' || p_report_function_id;
2823 p_funcn_sql := p_funcn_sql || '
2824 AND link_type = ''WWW'')
2825 ORDER BY 2';
2826
2827 p_bind_count := l_bind_count;
2828
2829 END GETRESPRLPORTLETSSQLWRAP;
2830
2831 --msaran: SQL Literals projects
2832 PROCEDURE getAllRespRLPortletsSQLWrap
2833 ( p_user_id IN PLS_INTEGER
2834 , p_resp_id IN VARCHAR2
2835 , p_report_function_id IN PLS_INTEGER
2836 , p_search_criteria IN VARCHAR2
2837 , p_funcn_sql OUT NOCOPY VARCHAR2
2838 , p_bind_count OUT NOCOPY NUMBER
2839 , p_bind_string OUT NOCOPY VARCHAR2
2840 )
2841 is
2842
2843 l_function_bindstring varchar2(2000);
2844 l_menu_bindstring varchar2(2000);
2845
2846 l_function_criteria varchar2(3000);
2847 l_function_count NUMBER;
2848 l_menu_criteria varchar2(3000);
2849 l_menu_count NUMBER;
2850
2851 l_total_count PLS_INTEGER;
2852 l_menu_id PLS_INTEGER;
2853 l_object object;
2854
2855 l_start_index NUMBER;
2856 l_end_index NUMBER;
2857 l_id varchar2(15);
2858 l_bind_count NUMBER := 1;
2859
2860 begin
2861
2862 l_function_criteria := '-1';
2863 l_function_count := 1;
2864 l_menu_criteria := '-1';
2865 l_menu_count := 1;
2866
2867 select a.responsibility_id,
2868 b.responsibility_application_id,
2869 b.security_group_id,
2870 a.menu_id
2871 into l_object.responsibility_id,
2872 l_object.resp_appl_id,
2873 l_object.security_group_id,
2874 l_menu_id
2875 from FND_SECURITY_GROUPS_VL fsg,
2876 fnd_responsibility_vl a,
2877 FND_USER_RESP_GROUPS b
2878 where a.responsibility_id = p_resp_id
2879 and b.user_id = p_user_id
2880 and b.start_date <= sysdate
2881 and (b.end_date is null or b.end_date > sysdate)
2882 and b.RESPONSIBILITY_id = a.responsibility_id
2883 and b.RESPONSIBILITY_application_id = a.application_id
2884 and a.version = 'W'
2885 and a.start_date <= sysdate
2886 and (a.end_date is null or a.end_date > sysdate)
2887 and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID;
2888
2889
2890 l_total_count := 0;
2891
2892 l_object.type := 'RESPONSIBILITY';
2893 l_object.parent_menu_id := '';
2894 l_object.entry_sequence := '';
2895 l_object.menu_explode := 'Y';
2896 l_object.function_explode := 'Y';
2897 l_object.level := 0;
2898
2899 g_list := g_nulllist;
2900 listResponsibility(p_object => l_object);
2901
2902 -- Build IN clause for functions
2903 for i in 1..g_list.LAST loop
2904 if g_list(i).type = 'FUNCTION' OR g_list(i).type = 'WEBPORTLET' then
2905 if (l_total_count = 0) then
2906 l_function_criteria := g_list(i).function_id;
2907 l_menu_criteria := g_list(i).parent_menu_id;
2908 else
2909 l_function_criteria := l_function_criteria || ',' || g_list(i).function_id;
2910 l_function_count := l_function_count + 1;
2911 l_menu_criteria := l_menu_criteria || ',' || g_list(i).parent_menu_id;
2912 l_menu_count := l_menu_count + 1;
2913 end if;
2914 l_total_count := l_total_count + 1;
2915 end if;
2916 end loop;
2917
2918 --user_function_name should be used for the Prompt -ansingh
2919 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links - custom table
2920 p_funcn_sql := ' select mev.function_id as FunctionId,
2921 fff.user_function_name AS Prompt,
2922 nvl(fff.description, nvl(mev.description, mev.prompt)) as Description,
2923 ''N'' as DummySelect,
2924 null as ReportUrl
2925 from FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
2926 where mev.function_id=fff.function_id
2927 and mev.function_id in (';
2928 l_function_bindstring := '';
2929 for i in 1..l_function_count loop
2930 if (i <> 1) then
2931 l_function_bindstring := l_function_bindstring || ',';
2932 end if;
2933 l_function_bindstring := l_function_bindstring || ':' || l_bind_count;
2934 l_bind_count := l_bind_count + 1;
2935 end loop;
2936 p_funcn_sql := p_funcn_sql || l_function_bindstring;
2937 p_bind_string := l_function_criteria;
2938 p_funcn_sql := p_funcn_sql || ')
2939 and mev.menu_id in (';
2940 l_menu_bindstring := '';
2941 for i in 1..l_menu_count loop
2942 if (i <> 1) then
2943 l_menu_bindstring := l_menu_bindstring || ',';
2944 end if;
2945 l_menu_bindstring := l_menu_bindstring || ':' || l_bind_count;
2946 l_bind_count := l_bind_count + 1;
2947 end loop;
2948 p_funcn_sql := p_funcn_sql || l_menu_bindstring;
2949 p_bind_string := p_bind_string || ',' || l_menu_criteria;
2950 p_funcn_sql := p_funcn_sql || ')
2951 and lower(fff.user_function_name) like lower(:';
2952 p_funcn_sql := p_funcn_sql || l_bind_count;
2953 l_bind_count := l_bind_count + 1;
2954 p_bind_string := p_bind_string || ',' || p_search_criteria;
2955 p_funcn_sql := p_funcn_sql || ')
2956 and mev.function_id in (select linked_function_id
2957 from bis_custom_related_Links_v rl
2958 where level_user_id = :';
2959 p_funcn_sql := p_funcn_sql || l_bind_count;
2960 l_bind_count := l_bind_count + 1;
2961 p_bind_string := p_bind_string || ',' || p_user_id;
2962 p_funcn_sql := p_funcn_sql || '
2963 and responsibility_id = :';
2964 p_funcn_sql := p_funcn_sql || l_bind_count;
2965 l_bind_count := l_bind_count + 1;
2966 p_bind_string := p_bind_string || ',' || p_resp_id;
2967 p_funcn_sql := p_funcn_sql || '
2968 and function_id = :';
2969 p_funcn_sql := p_funcn_sql || l_bind_count;
2970 l_bind_count := l_bind_count + 1;
2971 p_bind_string := p_bind_string || ',' || p_report_function_id;
2972 p_funcn_sql := p_funcn_sql || '
2973 and link_type = ''WWW'')
2974 union
2975 select mev.function_id as FunctionId,
2976 fff.user_function_name AS Prompt,
2977 nvl(fff.description, nvl(mev.description, mev.prompt)) as Description,
2978 ''N'' as DummySelect,
2979 null as ReportUrl
2980 from FND_MENU_ENTRIES_VL mev, FND_FORM_FUNCTIONS_VL fff
2981 where mev.function_id=fff.function_id
2982 and mev.function_id in (';
2983 l_function_bindstring := '';
2984 for i in 1..l_function_count loop
2985 if (i <> 1) then
2986 l_function_bindstring := l_function_bindstring || ',';
2987 end if;
2988 l_function_bindstring := l_function_bindstring || ':' || l_bind_count;
2989 l_bind_count := l_bind_count + 1;
2990 end loop;
2991 p_funcn_sql := p_funcn_sql || l_function_bindstring;
2992 p_bind_string := p_bind_string || ',' || l_function_criteria;
2993 p_funcn_sql := p_funcn_sql || ')
2994 and mev.menu_id in (';
2995 l_menu_bindstring := '';
2996 for i in 1..l_menu_count loop
2997 if (i <> 1) then
2998 l_menu_bindstring := l_menu_bindstring || ',';
2999 end if;
3000 l_menu_bindstring := l_menu_bindstring || ':' || l_bind_count;
3001 l_bind_count := l_bind_count + 1;
3002 end loop;
3003 p_funcn_sql := p_funcn_sql || l_menu_bindstring;
3004 p_bind_string := p_bind_string || ',' || l_menu_criteria;
3005 p_funcn_sql := p_funcn_sql || ')
3006 and lower(fff.user_function_name) like lower(:';
3007 p_funcn_sql := p_funcn_sql || l_bind_count;
3008 l_bind_count := l_bind_count + 1;
3009 p_bind_string := p_bind_string || ',' || p_search_criteria;
3010 p_funcn_sql := p_funcn_sql || ')
3011 and mev.function_id not in (select linked_function_id
3012 from bis_custom_related_Links_v rl
3013 where level_user_id = :';
3014 p_funcn_sql := p_funcn_sql || l_bind_count;
3015 l_bind_count := l_bind_count + 1;
3016 p_bind_string := p_bind_string || ',' || p_user_id;
3017 p_funcn_sql := p_funcn_sql || '
3018 and responsibility_id = :';
3019 p_funcn_sql := p_funcn_sql || l_bind_count;
3020 l_bind_count := l_bind_count + 1;
3021 p_bind_string := p_bind_string || ',' || p_resp_id;
3022 p_funcn_sql := p_funcn_sql || '
3023 and function_id = :';
3024 p_funcn_sql := p_funcn_sql || l_bind_count;
3025 p_bind_string := p_bind_string || ',' || p_report_function_id;
3026 p_funcn_sql := p_funcn_sql || '
3027 and link_type = ''WWW'')
3028 order by 2';
3029
3030 p_bind_count := l_bind_count;
3031
3032 end getAllRespRLPortletsSQLWrap;
3033
3034 -- procedure to add a language. Bug.Fix.5410058
3035
3036 PROCEDURE Add_Language IS
3037
3038 BEGIN
3039
3040 UPDATE BIS_RELATED_LINKS_TL T SET (
3041 USER_LINK_NAME
3042 ) = (SELECT
3043 B.USER_LINK_NAME
3044 FROM BIS_RELATED_LINKS_TL B
3045 WHERE B.RELATED_LINK_ID = T.RELATED_LINK_ID
3046 AND B.LANGUAGE = T.SOURCE_LANG)
3047 WHERE (
3048 T.RELATED_LINK_ID,
3049 T.LANGUAGE
3050 ) IN (SELECT
3051 SUBT.RELATED_LINK_ID,
3052 SUBT.LANGUAGE
3053 FROM BIS_RELATED_LINKS_TL SUBB, BIS_RELATED_LINKS_TL SUBT
3054 WHERE SUBB.RELATED_LINK_ID = SUBT.RELATED_LINK_ID
3055 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
3056 AND (SUBB.USER_LINK_NAME <> SUBT.USER_LINK_NAME
3057 ));
3058
3059 INSERT INTO BIS_RELATED_LINKS_TL
3060 (
3061 RELATED_LINK_ID,
3062 LANGUAGE,
3063 USER_LINK_NAME,
3064 SOURCE_LANG,
3065 CREATION_DATE,
3066 CREATED_BY,
3067 LAST_UPDATE_DATE,
3068 LAST_UPDATED_BY,
3069 LAST_UPDATE_LOGIN
3070 )
3071 SELECT
3072 B.RELATED_LINK_ID,
3073 L.LANGUAGE_CODE,
3074 B.USER_LINK_NAME,
3075 B.SOURCE_LANG,
3076 B.CREATION_DATE,
3077 B.CREATED_BY,
3078 B.LAST_UPDATE_DATE,
3079 B.LAST_UPDATED_BY,
3080 B.LAST_UPDATE_LOGIN
3081 FROM BIS_RELATED_LINKS_TL B, FND_LANGUAGES L
3082 WHERE L.INSTALLED_FLAG IN ('I', 'B')
3083 AND B.LANGUAGE = USERENV('LANG')
3084 AND NOT EXISTS
3085 (
3086 SELECT NULL
3087 FROM BIS_RELATED_LINKS_TL T
3088 WHERE T.RELATED_LINK_ID = B.RELATED_LINK_ID
3089 AND T.LANGUAGE = L.LANGUAGE_CODE
3090 );
3091
3092 UPDATE BIS_CUSTOM_RELATED_LINKS_TL T SET (
3093 USER_LINK_NAME
3094 ) = (SELECT
3095 B.USER_LINK_NAME
3096 FROM BIS_CUSTOM_RELATED_LINKS_TL B
3097 WHERE B.RELATED_LINK_ID = T.RELATED_LINK_ID
3098 AND B.LANGUAGE = T.SOURCE_LANG)
3099 WHERE (
3100 T.RELATED_LINK_ID,
3101 T.LANGUAGE
3102 ) IN (SELECT
3103 SUBT.RELATED_LINK_ID,
3104 SUBT.LANGUAGE
3105 FROM BIS_CUSTOM_RELATED_LINKS_TL SUBB, BIS_CUSTOM_RELATED_LINKS_TL SUBT
3106 WHERE SUBB.RELATED_LINK_ID = SUBT.RELATED_LINK_ID
3107 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
3108 AND (SUBB.USER_LINK_NAME <> SUBT.USER_LINK_NAME
3109 ));
3110
3111 INSERT INTO BIS_CUSTOM_RELATED_LINKS_TL
3112 (
3113 RELATED_LINK_ID,
3114 LANGUAGE,
3115 USER_LINK_NAME,
3116 SOURCE_LANG,
3117 CREATION_DATE,
3118 CREATED_BY,
3119 LAST_UPDATE_DATE,
3120 LAST_UPDATED_BY,
3121 LAST_UPDATE_LOGIN
3122 )
3123 SELECT
3124 B.RELATED_LINK_ID,
3125 L.LANGUAGE_CODE,
3126 B.USER_LINK_NAME,
3127 B.SOURCE_LANG,
3128 B.CREATION_DATE,
3129 B.CREATED_BY,
3130 B.LAST_UPDATE_DATE,
3131 B.LAST_UPDATED_BY,
3132 B.LAST_UPDATE_LOGIN
3133 FROM BIS_CUSTOM_RELATED_LINKS_TL B, FND_LANGUAGES L
3134 WHERE L.INSTALLED_FLAG IN ('I', 'B')
3135 AND B.LANGUAGE = USERENV('LANG')
3136 AND NOT EXISTS
3137 (
3138 SELECT NULL
3139 FROM BIS_CUSTOM_RELATED_LINKS_TL T
3140 WHERE T.RELATED_LINK_ID = B.RELATED_LINK_ID
3141 AND T.LANGUAGE = L.LANGUAGE_CODE
3142 );
3143
3144
3145
3146 END Add_Language;
3147
3148 end BIS_RL_PKG;