1 PACKAGE BODY bis_pmv_portal_util_pvt AS
2 /* $Header: BISPMVPB.pls 120.2 2006/03/31 14:57:00 serao noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.12=120.2):~PROD:~PATH:~FILE
5
6 /*
7 REM +=======================================================================+
8 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
9 REM | All rights reserved. |
10 REM +=======================================================================+
11 REM | FILENAME |
12 REM | BISPMVPB.pls |
13 REM | |
14 REM | DESCRIPTION |
15 REM | This is the utility package for Oracle Portal |
16 REM | |
17 REM | HISTORY |
18 REM | kiprabha 02/27/03 Initial Creation |
19 REM | ansingh 08/01/03 Delete Hanging Related Links |
20 REM | nkishore 03/02/03 Get page_id based on function_id |
21 REM +=======================================================================+
22 */
23
24 -- **************** GLOBAL VARIABLES *********************
25 g_user_id NUMBER := -1 ;
26
27
28 -- OA Pages
29 -- Added p_page_name
30 PROCEDURE clean_portlets (
31 p_user_name in VARCHAR2 DEFAULT NULL-- jprabhud - 04/23/04 - Bug 3573468
32 ,p_page_id in NUMBER DEFAULT NULL-- jprabhud - 04/23/04 - Bug 3573468
33 ,p_page_name in VARCHAR2 DEFAULT NULL-- jprabhud - 04/23/04 - Bug 3573468
34 ,x_return_status OUT NOCOPY VARCHAR2
35 ,x_msg_count OUT NOCOPY NUMBER
36 ,x_msg_data OUT NOCOPY VARCHAR2
37 ,p_function_name in VARCHAR2 DEFAULT NULL -- jprabhud - 04/23/04 - Bug 3573468
38 )
39
40 IS
41
42 l_schedule_id_arr BISVIEWER.t_num;
43 l_ref_path_tbl BIS_PMV_PORTAL_UTIL_PVT.BIS_PMV_REF_PATH_TBL_TYPE ;
44 l_return_status VARCHAR2(100) ;
45 l_msg_data VARCHAR2(1000) ;
46 l_msg_count NUMBER;
47 l_schedule_id NUMBER ;
48 l_sched_index NUMBER := 1 ;
49 l_user_id NUMBER := -1 ;
50 l_page_id NUMBER := p_page_id ;
51 l_page_name VARCHAR2(100);
52
53 --Hanging Related Links -ansingh
54 l_plug_id NUMBER;
55 l_plugId_Array BISVIEWER.t_num;
56
57 --Added plug_id for Hanging Related Links -ansingh, BugFix 3123327 Removed Into clause
58 CURSOR c_all_schedules(p_ref_path IN VARCHAR2) IS
59 SELECT bs.schedule_id, bs.plug_id
60 FROM
61 icx_portlet_customizations ipc,
62 bis_schedule_preferences bs
63 WHERE
64 bs.plug_id = ipc.plug_id and
65 ipc.reference_path = p_ref_path ;
66
67 --Added plug_id for Hanging Related Links -ansingh, BugFix 3123327 Removed Into clause
68 CURSOR c_user_schedules(p_ref_path IN VARCHAR2) IS
69 SELECT bs.schedule_id, bs.user_id, bs.plug_id
70 FROM
71 icx_portlet_customizations ipc,
72 bis_schedule_preferences bs
73 WHERE
74 bs.plug_id = ipc.plug_id AND
75 ipc.reference_path = p_ref_path AND
76 bs.user_id = g_user_id ;
77
78 --BugFix 3417849
79 -- jprabhud - 04/23/04 - Bug 3573468
80 CURSOR get_page_id(c_pageName IN VARCHAR2) IS
81 SELECT function_id FROM fnd_form_functions
82 WHERE type ='JSP'
83 AND web_html_call = 'OA.jsp?akRegionCode=BIS_COMPONENT_PAGE&akRegionApplicationId=191'
84 AND upper(parameters) like upper(c_pageName);
85
86
87 BEGIN
88 -- Get the FND user_id if the user_name is passed
89 IF p_user_name IS NOT NULL THEN
90 SELECT user_id INTO g_user_id FROM fnd_user
91 WHERE user_name = p_user_name;
92 END IF;
93
94 -- Find the reference paths corresponding to p_user_name, p_page_id
95 -- OA Pages enhancement
96 /*
97 IF ((p_page_id < 0) OR (p_page_name is not null)) THEN
98 IF (p_page_name IS NOT NULL) THEN
99 l_page_id := get_oa_page_id( p_page_name => p_page_name,
100 x_return_status => l_return_status,
101 x_msg_count => l_msg_count,
102 x_msg_data => l_msg_data
103 ) ;
104 ELSE
105 l_page_id := p_page_id ;
106 END IF;
107 */
108 -- jprabhud - 04/23/04 - Bug 3573468
109 IF ( (p_page_id IS NULL) AND (p_function_name IS NOT NULL)) THEN
110 l_page_id := get_oa_page_id( p_function_name => p_function_name,
111 x_return_status => l_return_status,
112 x_msg_count => l_msg_count,
113 x_msg_data => l_msg_data
114 ) ;
115 END IF;
116
117
118 --BugFix 3417849 Get page_id based on function_id
119 -- jprabhud - 04/23/04 - Bug 3573468
120 --IF ( (p_page_id <0) AND (p_page_name IS NULL) ) THEN
121 IF ( (l_page_id <0) AND (p_page_name IS NULL) ) THEN
122 --l_page_id := p_page_id ;
123 get_oa_reference_paths( p_page_id => l_page_id,
124 x_ref_path_tbl => l_ref_path_tbl,
125 x_return_status => l_return_status,
126 x_msg_count => l_msg_count,
127 x_msg_data => l_msg_data
128 ) ;
129 ELSIF ( p_page_name IS NOT NULL) THEN
130 IF (get_page_id%ISOPEN) THEN
131 CLOSE get_page_id;
132 END IF;
133 l_page_name := '%'||p_page_name||'%';
134 open get_page_id(l_page_name);
135 LOOP
136 fetch get_page_id into l_page_id;
137 if get_page_id%NOTFOUND then
138 CLOSE get_page_id;
139 EXIT;
140 end if;
141 l_page_id := (-1) * l_page_id;
142 get_oa_reference_paths( p_page_id => l_page_id,
143 x_ref_path_tbl => l_ref_path_tbl,
144 x_return_status => l_return_status,
145 x_msg_count => l_msg_count,
146 x_msg_data => l_msg_data
147 ) ;
148 END LOOP;
149
150 ELSE
151 get_reference_paths( p_user_name => p_user_name,
152 p_page_id => p_page_id,
153 x_ref_path_tbl => l_ref_path_tbl,
154 x_return_status => l_return_status,
155 x_msg_count => l_msg_count,
156 x_msg_data => l_msg_data
157 ) ;
158 END IF;
159
160 IF (l_return_status = FND_API.G_RET_STS_ERROR OR l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
161 x_return_status := l_return_status ;
162 x_msg_count := l_msg_count ;
163 x_msg_data := l_msg_data ;
164 RETURN;
165 ELSE
166 -- Do a bulk operation here
167
168 IF l_ref_path_tbl.COUNT > 0 THEN
169
170 FOR i in l_ref_path_tbl.FIRST..l_ref_path_tbl.LAST LOOP
171 IF (p_user_name is null) THEN
172 IF (c_all_schedules%ISOPEN) THEN
173 CLOSE c_all_schedules;
174 END IF;
175 FOR l_sched in c_all_schedules(l_ref_path_tbl(i).ref_path) LOOP
176
177 BEGIN
178
179 l_schedule_id := l_sched.schedule_id ;
180 IF l_schedule_id IS NOT NULL THEN
181 l_schedule_id_arr(l_sched_index) := l_schedule_id ;
182 l_plugId_Array(l_sched_index) := l_sched.plug_id; --hanging related links
183 l_sched_index := l_sched_index + 1 ;
184 END IF;
185 EXCEPTION
186 WHEN OTHERS THEN
187 NULL;
188 END ;
189
190 END LOOP ;
191 ELSE --user_id is not null
192 IF (c_user_schedules%ISOPEN) THEN
193 CLOSE c_user_schedules;
194 END IF;
195 FOR l_sched in c_user_schedules(l_ref_path_tbl(i).ref_path) LOOP
196
197 BEGIN
198 IF (l_sched.schedule_id is not null AND l_sched.user_id = g_user_id) then
199 l_schedule_id_arr(l_sched_index) := l_sched.schedule_id ;
200 l_plugId_Array(l_sched_index) := l_sched.plug_id; --hanging related links
201 l_sched_index := l_sched_index + 1 ;
202 END IF;
203 EXCEPTION
204 WHEN OTHERS THEN
205 NULL;
206 END ;
207
208 END LOOP ;
209 END IF;
210 END LOOP ; /* Reference Path loop */
211
212 -- Need to take care of the situation where
213 -- the result of this query is a large set
214 IF l_schedule_id_arr.COUNT > 0 THEN
215 bulk_delete_schedules (p_schedule_ids => l_schedule_id_arr) ;
216 bulk_delete_attributes (p_schedule_ids => l_schedule_id_arr, p_page_id => l_page_id);
217 END IF;
218 --hanging related links
219 IF l_plugId_Array.COUNT > 0 THEN
220 DELETE_HANGING_RELATED_LINKS(pUserId=>g_user_id, pPlugIdArray=>l_plugId_Array);
221 END IF ;
222
223
224 -- Fix for bug 3006533
225 -- Update the caching_key in icx_portlet_customizations
226
227 FOR k in l_ref_path_tbl.FIRST..l_ref_path_tbl.LAST LOOP
228
229 BIS_PMV_UTIL.stale_portlet_by_refPath(l_ref_path_tbl(k).ref_path) ;
230
231 END LOOP ;
232
233 COMMIT ;
234
235 END IF ; -- IF l_ref_path.COUNT > 0
236
237 END IF ; -- return_status is not error
238
239
240 EXCEPTION
241 WHEN FND_API.G_EXC_ERROR THEN
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data) ;
244
245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data) ;
248 WHEN OTHERS THEN
249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
250 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
251
252 END clean_portlets ;
253
254
255
256 FUNCTION get_oa_page_id (
257 p_page_name in VARCHAR2 DEFAULT NULL
258 , p_function_name in VARCHAR2 DEFAULT NULL-- jprabhud - 04/23/04 - Bug 3573468
259 ,x_return_status OUT NOCOPY VARCHAR2
260 ,x_msg_count OUT NOCOPY NUMBER
261 ,x_msg_data OUT NOCOPY VARCHAR2
262 ) RETURN NUMBER
263 is
264
265 l_menu_id NUMBER;
266 l_page_id NUMBER;
267 l_return_status VARCHAR2(100) ;
268 l_msg_data VARCHAR2(1000) ;
269 l_msg_count NUMBER;
270 -- jprabhud - 04/23/04 - Bug 3573468
271 l_function_id NUMBER;
272
273
274 BEGIN
275
276 -- jprabhud - 04/23/04 - Bug 3573468
277 IF (p_function_name is null) THEN
278 select menu_id
279 into l_menu_id
280 from fnd_menus
281 where menu_name = p_page_name ;
282
283 l_page_id := (-1) * l_menu_id ;
284
285 ELSE
286 select function_id into l_function_id
287 from fnd_form_functions
288 where function_name = p_function_name;
289
290 l_page_id := (-1) * l_function_id;
291 END IF;
292
293
294
295
296 return l_page_id ;
297
298
299 EXCEPTION
300 WHEN FND_API.G_EXC_ERROR THEN
301 x_return_status := FND_API.G_RET_STS_ERROR;
302 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data) ;
303
304 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
306 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data) ;
307 WHEN OTHERS THEN
308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
309 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
310
311 END get_oa_page_id ;
312
313 PROCEDURE bulk_delete_schedules
314 (
315 p_schedule_ids IN BISVIEWER.t_num
316 )
317
318 IS
319 BEGIN
320 IF p_schedule_ids.COUNT > 0 THEN
321
322 IF (g_user_id = -1) OR (g_user_id IS NULL) THEN
323
324 FORALL i in p_schedule_ids.FIRST..p_schedule_ids.LAST
325 delete from bis_schedule_preferences
326 where schedule_id = p_schedule_ids(i) ;
327
328 FORALL i in p_schedule_ids.FIRST..p_schedule_ids.LAST
329 delete from bis_scheduler
330 where schedule_id = p_schedule_ids(i) ;
331 ELSE
332
333 FORALL i in p_schedule_ids.FIRST..p_schedule_ids.LAST
334 delete from bis_schedule_preferences
335 where schedule_id = p_schedule_ids(i)
336 and user_id = g_user_id;
337
338 FORALL i in p_schedule_ids.FIRST..p_schedule_ids.LAST
339 delete from bis_scheduler
340 where schedule_id = p_schedule_ids(i)
341 and user_id = g_user_id;
342
343 END IF ;
344
345 END IF ;
346
347 -- I believe we should not have any EXCEPTION blocks
348 -- to ensure that either all the actions are completed or
349 -- none at all
350
351 END bulk_delete_schedules ;
352
353
354 -- OA Pages : added p_page_id
355 -- Delete schedule records as well as page-level records
356 PROCEDURE bulk_delete_attributes (p_schedule_ids IN BISVIEWER.t_num,
357 p_page_id IN NUMBER)
358
359 IS
360 BEGIN
361 IF p_schedule_ids.COUNT > 0 THEN
362 IF (g_user_id = -1) OR (g_user_id IS NULL) THEN
363 FORALL i in p_schedule_ids.FIRST..p_schedule_ids.LAST
364 -- split this for bug 5130341
365 delete from bis_user_attributes
366 where schedule_id = p_schedule_ids(i) ;
367
368 delete from bis_user_attributes
369 where page_id = p_page_id
370 and user_id > -2; --to use the index
371 ELSE
372 FORALL i in p_schedule_ids.FIRST..p_schedule_ids.LAST
373 -- split this for bug 5130341
374 delete from bis_user_attributes
375 where
376 (schedule_id = p_schedule_ids(i)
377 and user_id = g_user_id);
378
379 delete from bis_user_attributes
380 where
381 (page_id = p_page_id
382 and user_id = g_user_id) ;
383
384 END IF ;
385 END IF ;
386
387 END bulk_delete_attributes ;
388
389 -- Note : 1. USER_ID IN wwpob_portlet_instance$ IS ACTUALLY THE USER_NAME
390 -- IN FND_USER
391 -- 2. This procedure has a PORTAL dependency. On the long run,
392 -- this should be replaced by a centralized BIA API that will
393 -- access the Portal schema
394 -- Fix for bug 3006533
395 -- Check for p_user_name
396 PROCEDURE get_reference_paths(
397 p_user_name IN VARCHAR2
398 ,p_page_id IN NUMBER
399 ,x_ref_path_tbl OUT NOCOPY BIS_PMV_PORTAL_UTIL_PVT.BIS_PMV_REF_PATH_TBL_TYPE
400 ,x_return_status OUT NOCOPY VARCHAR2
401 ,x_msg_count OUT NOCOPY NUMBER
402 ,x_msg_data OUT NOCOPY VARCHAR2
403 )
404 IS
405
406 l_ref_path_rec BIS_PMV_PORTAL_UTIL_PVT.BIS_PMV_REF_PATH_REC_TYPE ;
407 l_index NUMBER := 1;
408
409 --jprabhud - 07/16/03- Make this a dynamic sql to remove portal dependency
410 TYPE All_Ref_Paths IS REF CURSOR;
411 c_all_ref_paths All_Ref_Paths;
412 l_all_ref_paths_stmt varchar2(2000);
413
414 --jprabhud - 07/16/03- Make this a dynamic sql to remove portal dependency
415 -- Removed cursor c_user_ref_paths as it is not being used, and has reference to portal table
416
417 BEGIN
418 --jprabhud - 07/16/03- Make this a dynamic sql to remove portal dependency
419 if c_all_ref_paths%ISOPEN then
420 close c_all_ref_paths;
421 end if;
422 l_all_ref_paths_stmt := 'select name from wwpob_portlet_instance$ where page_id = :1';
423 OPEN c_all_ref_paths FOR l_all_ref_paths_stmt USING p_page_id;
424
425 --jprabhud - 07/16/03- Make this a dynamic sql to remove portal dependency
426 LOOP
427 FETCH c_all_ref_paths INTO l_ref_path_rec.ref_path;
428 x_ref_path_tbl(l_index) := l_ref_path_rec ;
429 l_index := l_index + 1 ;
430 EXIT WHEN c_all_ref_paths%NOTFOUND;
431 END LOOP;
432 CLOSE c_all_ref_paths;
433
434
435
436 EXCEPTION
437 WHEN FND_API.G_EXC_ERROR THEN
438 --jprabhud - 07/16/03- Make this a dynamic sql to remove portal dependency
439 if c_all_ref_paths%ISOPEN then
440 close c_all_ref_paths;
441 end if;
442 x_return_status := FND_API.G_RET_STS_ERROR;
443 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data) ;
444 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
445 --jprabhud - 07/16/03- Make this a dynamic sql to remove portal dependency
446 if c_all_ref_paths%ISOPEN then
447 close c_all_ref_paths;
448 end if;
449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
450 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data) ;
451 WHEN OTHERS THEN
452 --jprabhud - 07/16/03- Make this a dynamic sql to remove portal dependency
453 if c_all_ref_paths%ISOPEN then
454 close c_all_ref_paths;
455 end if;
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
457 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
458 END get_reference_paths ;
459
460
461 -- Note : 1. PAGE_ID for OA pages is of the form
462 -- -{MENU_ID}
463 -- Notice the '-' sign
464 -- 2. REFERENCE_PATHS present in ICX_PORTLET_CUSTOMIZATIONS are
465 -- of the form
466 -- %PAGE_ID%
467 -- where '%' represents a string of alpha-numeric characters
468 --BugFix 3417849 make ref_path_tbl as IN OUT
469 PROCEDURE get_oa_reference_paths(
470 p_page_id IN NUMBER
471 ,x_ref_path_tbl IN OUT NOCOPY BIS_PMV_PORTAL_UTIL_PVT.BIS_PMV_REF_PATH_TBL_TYPE
472 ,x_return_status OUT NOCOPY VARCHAR2
473 ,x_msg_count OUT NOCOPY NUMBER
474 ,x_msg_data OUT NOCOPY VARCHAR2
475 )
476 IS
477
478 l_ref_path_rec BIS_PMV_PORTAL_UTIL_PVT.BIS_PMV_REF_PATH_REC_TYPE ;
479 l_index NUMBER := 1;
480
481 CURSOR c_all_ref_paths(p_page_id IN NUMBER) IS
482 select reference_path
483 from icx_portlet_customizations
484 where reference_path like '%' || p_page_id || '%' ;
485
486
487 BEGIN
488 if (x_ref_path_tbl is not null) then
489 l_index := x_ref_path_tbl.COUNT + 1;
490 end if;
491
492 if c_all_ref_paths%ISOPEN then
493 close c_all_ref_paths;
494 end if;
495
496
497
498 for l_ref_path in c_all_ref_paths(p_page_id) loop
499 l_ref_path_rec.ref_path := l_ref_path.reference_path ;
500 x_ref_path_tbl(l_index) := l_ref_path_rec ;
501 l_index := l_index + 1 ;
502 end loop ;
503
504
505
506 EXCEPTION
507 WHEN FND_API.G_EXC_ERROR THEN
508 x_return_status := FND_API.G_RET_STS_ERROR;
509 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data) ;
510 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
511 null ;
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
513 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data) ;
514 WHEN OTHERS THEN
515 null ;
516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
517 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
518 END get_oa_reference_paths ;
519
520
521 ----------------------Delete Hanging Related Links-------------------------------
522
523 --bulk delete the related links that have been left hanging
524 --as a result of refresh portal page. -ansingh
525
526
527 PROCEDURE DELETE_HANGING_RELATED_LINKS (pUserId IN NUMBER, pPlugIdArray IN BISVIEWER.t_num)
528 IS
529 BEGIN
530
531 IF (pUserId = -1) OR (pUserId IS NULL) THEN
532 FORALL i IN pPlugIdArray.FIRST..pPlugIdArray.LAST
533 DELETE FROM BIS_RELATED_LINKS
534 WHERE FUNCTION_ID = pPlugIdArray(i);
535 ELSE
536 FORALL i IN pPlugIdArray.FIRST..pPlugIdArray.LAST
537 DELETE FROM BIS_RELATED_LINKS
538 WHERE FUNCTION_ID = pPlugIdArray(i)
539 AND USER_ID = pUserId;
540 END IF;
541
542 END DELETE_HANGING_RELATED_LINKS;
543 ----------------------Delete Hanging Related Links-------------------------------
544
545 END bis_pmv_portal_util_pvt;
546
[Home] [Help]