1 PACKAGE BODY BIS_IMPL_OPT_PKG AS
2 /*$Header: BISIMPLB.pls 120.11 2005/12/20 10:59:30 tiwang noship $*/
3
4 PROCEDURE DEBUG( P_TEXT VARCHAR2, P_IDENT NUMBER DEFAULT 0)
5 IS
6 BEGIN
7 FND_LOG_REPOSITORY.INIT;
8 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
9 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, icx_sec.getsessioncookie, FND_LOG.LEVEL_UNEXPECTED );
10 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, P_TEXT, FND_LOG.LEVEL_UNEXPECTED );
11 END IF;
12 END;
13
14 PROCEDURE enableImplementation(
15 p_object_name varchar2)
16 IS
17 stmt varchar2(2000);
18 BEGIN
19
20 update bis_obj_properties prop
21 set implementation_flag = 'Y'
22 where OBJECT_type = 'PAGE'
23 and OBJECT_NAME = p_object_name;
24
25 update bis_obj_properties
26 set implementation_flag = 'Y'
27 where (object_type, OBJECT_NAME)
28 in( select distinct
29 depend_object_type,
30 depend_OBJECT_NAME
31 from bis_obj_dependency
32 where enabled_flag='Y'
33 start with object_type='PAGE'
34 and object_name = p_object_name
35 connect by prior DEPEND_OBJECT_NAME = object_name
36 AND PRIOR depend_object_type = object_type);
37 DEBUG('Done enableImplementation');
38 END;
39
40
41 PROCEDURE disableImplPruned (
42 p_object_type varchar2,
43 p_object_name varchar2)
44 IS
45 BEGIN
46 update BIS_OBJ_PROPERTIES
47 set implementation_flag = 'N'
48 where not exists (
49 -- back traverse and hit Pages that are implemented
50 (select distinct
51 dep.object_owner,
52 dep.object_type,
53 dep.OBJECT_NAME
54 from bis_obj_dependency dep
55 where dep.enabled_flag= 'Y'
56 and dep.object_type = 'PAGE'
57 and exists (
58 select 1
59 from BIS_OBJ_PROPERTIES tmp
60 where tmp.OBJECT_NAME = dep.OBJECT_NAME
61 and tmp.OBJECT_TYPE = dep.object_type
62 and tmp.implementation_flag = 'Y'
63 )
64 start with (
65 dep.depend_object_type = p_object_type and
66 dep.depend_object_name = p_object_name
67 )
68 connect by dep.DEPEND_OBJECT_NAME = prior dep.object_name
69 AND dep.depend_object_type = PRIOR dep.object_type
70 )
71 )
72 and BIS_OBJ_PROPERTIES.object_type = p_object_type
73 and BIS_OBJ_PROPERTIES.object_NAME = p_object_name;
74 END;
75
76
77 PROCEDURE smartPrunImpl(
78 p_object_name varchar2)
79 IS
80 l_depend_object_type bis_obj_dependency.depend_OBJECT_TYPE%type;
81 l_depend_object_name bis_obj_dependency.depend_OBJECT_NAME%type;
82
83 CURSOR C_DISIMPLGRP ( P_PGNAME bis_obj_dependency.object_name%type )
84 IS
85 select distinct
86 depend_object_type,
87 depend_OBJECT_NAME
88 from bis_obj_dependency
89 where enabled_flag='Y'
90 start with object_type='PAGE' and object_name = P_PGNAME
91 connect by prior DEPEND_OBJECT_NAME = object_name
92 AND PRIOR depend_object_type = object_type;
93 BEGIN
94 open C_DISIMPLGRP(p_object_name);
95 loop
96 fetch C_DISIMPLGRP into l_depend_object_type, l_depend_object_name;
97 exit when C_DISIMPLGRP%NOTFOUND;
98 DEBUG('Processing ' || l_depend_object_type|| ', ' || l_depend_object_name);
99 disableImplPruned(l_depend_object_type, l_depend_object_name);
100 end loop;
101
102 END;
103
104 PROCEDURE disableImplementation(
105 p_object_name varchar2)
106 IS
107 stmt varchar2(2000);
108 BEGIN
109 update bis_obj_properties prop
110 set implementation_flag = 'N'
111 where OBJECT_type = 'PAGE'
112 and OBJECT_NAME = p_object_name;
113
114 smartPrunImpl(p_object_name);
115 DEBUG('Done disableImplementation');
116 END;
117
118
119 -- this procedure should be used to change implementation flag for page object only
120 PROCEDURE changeImplementation(
121 p_object_name varchar2,
122 p_impl_flag varchar2)
123 IS
124 stmt varchar2(2000);
125 BEGIN
126 -- original implementation
127 /*
128 IF p_impl_flag = 'Y' THEN
129 enableImplementation(p_object_name);
130 ELSE
131 disableImplementation(p_object_name);
132 END IF;
133 */
134
135 execute immediate
136 'update bis_obj_properties set IMPLEMENTATION_FLAG = :1
137 where OBJECT_NAME = :2 AND object_type = :3'
138 using p_impl_flag, p_object_name, 'PAGE';
139 END;
140
141
142 PROCEDURE processChange
143 IS
144 BEGIN
145 null;
146 END;
147
148 PROCEDURE Init_impl IS
149 BEGIN
150 null;
151 END Init_impl;
152
153 PROCEDURE setImplementationOptions(
154 errbuf OUT NOCOPY VARCHAR2,
155 retcode OUT NOCOPY VARCHAR2
156 ) IS
157 BEGIN
158 errbuf := NULL;
159 retcode := '0';
160 IF (Not BIS_COLLECTION_UTILITIES.setup('BIS_IMPL_OPT_PKG.setImplementationOptions')) THEN
161 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
162 return;
163 END IF;
164 propagateimplementationoptions();
165 EXCEPTION
166 WHEN OTHERS THEN
167 errbuf := sqlerrm;
168 retcode := sqlcode;
169 BIS_COLLECTION_UTILITIES.put_line(' ');
170 BIS_COLLECTION_UTILITIES.put_line('Error occurred:');
171 BIS_COLLECTION_UTILITIES.put_line(errbuf);
172
173 BIS_COLLECTION_UTILITIES.WRAPUP(
174 FALSE,
175 0,
176 errbuf,
177 null,
178 null
179 );
180
181 END setImplementationOptions;
182
183
184 /** Comment out the following two APIs because in 4422645
185 UI is provided for the user to set impl flag for any reports
186 function check_top_node(p_object_type in varchar2,p_object_name in varchar2) return varchar2 is
187 l_dummy varchar2(1);
188 cursor c_top_node is
189 select 'Y'
190 from dual
191 where not exists
192 (select 'Y'
193 from bis_obj_dependency
194 where depend_object_name=p_object_name
195 and depend_object_type=p_object_type);
196 begin
197 l_dummy:='N';
198 ------As of now, we only consider page or report has
199 -----the chance being top node
200 if p_object_type in ('MV','TABLE','VIEW','REGION') then
201 return 'N';
202 else ----'REPORT' or 'PAGE'
203 open c_top_node;
204 fetch c_top_node into l_dummy;
205 close c_top_node;
206 return l_dummy;
207 end if;
208 exception
209 when others then
210 raise;
211 end;
212
213
214 ---This api is added for enhancement 3999465.
215 ----It is called by preparation program before calling setImplementationOptions.
216 ----Since we don't have UI for the user to set impl flag for reports,
217 ---we have to use this API to set impl flag to "Y" at runtime (it is better
218 ---than doing this at seeding time, because the user may create lots of reports
219 ---while end up only run request set for few of them) so that
220 ---reports (MVs under reports) can be refreshed properly.
221 ---Note that once the flag is set to "Y", there is no chance to set it back
222 ---to "N". This may cause potential issue for MV logs.
223 ---Once we have UI for the user to set impl flag for reports, we
224 ----can get rid of this API call.
225 procedure set_implflag_reports_in_set(p_set_name in varchar2,p_set_app_id in number) is
226
227 l_sql varchar2(1000):='
228 update bis_obj_properties
229 set implementation_flag=''Y''
230 where object_type=''REPORT''
231 and object_name in (
232 select distinct object_name
233 from bis_request_set_objects
234 where object_type=''REPORT''
235 and REQUEST_SET_NAME=:1
236 and SET_APP_ID=:2 )';
237 begin
238 execute immediate l_sql using p_set_name,p_set_app_id;
239 commit;
240 exception
241 when others then
242 bis_collection_utilities.put_line('error in set_implflag_reports_in_set '||sqlerrm);
243 end;
244 **/
245
246 ----this procedure is added for enhancement 3999465,4422645
247 ----set impl flag under reports because separate UI
248 ---is provided for the user to set impl flag for reports
249 --- Modified again for bug 4664831 on Oct 12, 2006
250
251 procedure propagate_impl_flag_reports is
252 cursor c_impl_reports is
253 select object_name
254 from bis_obj_properties
255 where object_type='REPORT'
256 and implementation_flag='Y';
257
258 /**
259 cursor c_unimpl_reports is
260 select object_name
261 from bis_obj_properties
262 where object_type='REPORT'
263 and implementation_flag='N';
264 **/
265 cursor c_obj_under_reports (p_report_name varchar2) is
266 select distinct dep.depend_object_name, dep.depend_object_type
267 from
268 ( select object_name,
269 object_type,
270 depend_object_name,
271 depend_object_type
272 from bis_obj_dependency
273 where enabled_flag='Y') dep
274 where dep.depend_object_type<>'REPORT'--bug 4609286
275 start with dep.object_type = 'REPORT'
276 and dep.object_name=p_report_name
277 connect by prior dep.depend_object_name = dep.object_name
278 and prior dep.depend_object_type = dep.object_type;
279
280
281 l_impl_report_rec c_impl_reports%rowtype;
282 ---l_unimpl_report_rec c_unimpl_reports%rowtype;
283 l_obj_under_report_rec c_obj_under_reports%rowtype;
284
285 begin
286
287 /** commented for bug 4664831
288 for l_unimpl_report_rec in c_unimpl_reports loop
289 for l_obj_under_report_rec in c_obj_under_reports(l_unimpl_report_rec.object_name) loop
290 update bis_obj_properties
291 set IMPLEMENTATION_FLAG ='N'
292 where object_type=l_obj_under_report_rec.depend_object_type
293 and object_name=l_obj_under_report_rec.depend_object_name;
294 end loop;
295 end loop;
296 **/
297
298 for l_impl_report_rec in c_impl_reports loop
299 for l_obj_under_report_rec in c_obj_under_reports(l_impl_report_rec.object_name) loop
300 update bis_obj_properties
301 set IMPLEMENTATION_FLAG ='Y'
302 where object_type=l_obj_under_report_rec.depend_object_type
303 and object_name=l_obj_under_report_rec.depend_object_name;
304 end loop;
305 end loop;
306 end ;
307
308
309 ---added for enhancement 3999465 and 4422645.
310 FUNCTION report_in_impl_pages(p_report_name varchar2) return varchar2
311 is
312 cursor report_in_impl_pages is
313 select 'Y' from dual
314 where exists
315 (select 'Y'
316 from
317 (SELECT distinct object_name
318 FROM
319 ( select object_name,
320 object_type,
321 object_owner,
322 depend_object_name,
323 depend_object_type,
324 depend_object_owner,
325 enabled_flag
326 from
327 bis_obj_dependency
328 where enabled_flag='Y' ) obj
329 where object_type='PAGE'
330 START WITH depend_object_name =p_report_name AND depend_object_type ='REPORT'
331 CONNECT BY PRIOR object_name = depend_object_name AND PRIOR object_type = depend_object_type) pages,
332 bis_obj_properties properties
333 where pages.object_name=properties.object_name
334 and properties.object_type='PAGE'
335 and properties.implementation_flag='Y');
336
337 l_report_in_impl_pages varchar2(1);
338 begin
339 l_report_in_impl_pages:='N';
340 open report_in_impl_pages;
341 fetch report_in_impl_pages into l_report_in_impl_pages;
342 close report_in_impl_pages;
343 if l_report_in_impl_pages is null then
344 l_report_in_impl_pages:='N';
345 end if;
346 return l_report_in_impl_pages;
347 end;
348
349
350 -----this procedure is called by RSG preparation program through setImplementationOptions,
351 -----as well as in page configuration module directly
352 PROCEDURE propagateimplementationoptions IS
353 l_report_in_impl_pages varchar2(1);
354
355 cursor c_reports_impl_null is
356 select object_name
357 from bis_obj_properties
358 where object_type='REPORT'
359 and implementation_flag is null;
360
361 l_reports_rec c_reports_impl_null%rowtype;
362
363 BEGIN
364
365
366 ---added for enhancement 3999465 and 4422645.
367 ---For backward compatibility
368 ---When a new report (implementation flag is null) is added to an existing implemented page
369 ---the report should have impl flag set to Y automatically
370 for l_reports_rec in c_reports_impl_null loop
371 l_report_in_impl_pages:=report_in_impl_pages(l_reports_rec.object_name);
372 if l_report_in_impl_pages='Y' then
373 execute immediate 'update bis_obj_properties set IMPLEMENTATION_FLAG = ''Y''
374 where object_type=''REPORT'' and object_name=:1' using l_reports_rec.object_name;
375 end if;
376 end loop;
377
378 -- Reset implementation flag. Note if implementation option flag is null, treat as N
379 ----Modified for enhancement 3999465 and 4422645
380 ----Exclude reports because we will have UI to set impl flag for reports
381 execute immediate
382 'update bis_obj_properties set IMPLEMENTATION_FLAG = ''N''
383 WHERE (object_type not in ( ''PAGE'',''REPORT''))
384 OR implementation_flag IS NULL';
385
386
387 ------set implementation flag to 'Y' for objects under implemented pages
388 ------Modified for enhancement 3999465 and 4422645. Exclude reports
389 execute immediate
390 'update bis_obj_properties set IMPLEMENTATION_FLAG = :1
391 where object_type<> ''REPORT''
392 and (object_name, object_type) in (
393 select distinct dep.depend_object_name, dep.depend_object_type
394 from bis_obj_dependency dep
395 where dep.enabled_flag = :2
396 start with dep.object_type = :3
397 and exists (select 1
398 from bis_obj_properties prop
399 where prop.object_type = dep.object_type
400 and prop.object_name = dep.object_name
401 and prop.implementation_flag = :4)
402 connect by prior dep.depend_object_name = dep.object_name
403 and prior dep.depend_object_type = dep.object_type
404 AND PRIOR dep.enabled_flag = :5
405 )'
406 using 'Y', 'Y', 'PAGE', 'Y', 'Y';
407
408 ----added this call for enhancement 3999465,4422645
409 ----set implementation flag to 'Y' for objects under implemented reports
410 propagate_impl_flag_reports ;
411
412 END propagateimplementationoptions;
413
414
415
416 -- begin: added for bug 3560408
417 -- private function used to find if a form function name is valid
418 -- not take portal page into consideration
419 FUNCTION is_valid_page_func (
420 p_func_name IN VARCHAR2) RETURN VARCHAR2
421 IS
422 CURSOR c_funcs (p_func_name VARCHAR2) IS
423 SELECT function_name
424 FROM fnd_form_functions
425 WHERE function_name = p_func_name
426 AND web_html_call LIKE 'OA.jsp?akRegionCode=BIS_COMPONENT_PAGE'||'&'||'akRegionApplicationId=191%';
427 v_func_name fnd_form_functions.function_name%type; --Enhancement 4106617
428 v_is_valid_func VARCHAR2(5);
429 BEGIN
430 IF (p_func_name IS NULL OR p_func_name = '') THEN
431 RETURN 'N';
432 END IF;
433
434 OPEN c_funcs(p_func_name);
435 FETCH c_funcs INTO v_func_name;
436 IF (c_funcs%notfound) THEN
437 -- not a valid page form function
438 v_is_valid_func := 'N';
439 ELSE
440 -- valid page form function
441 v_is_valid_func := 'Y';
442 END IF;
443 CLOSE c_funcs;
444 RETURN v_is_valid_func;
445 EXCEPTION
446 WHEN OTHERS THEN
447 RETURN 'N';
448 END is_valid_page_func;
449
450 -- private function used to find the page object name for a given fnd form function
451 FUNCTION get_page_name_by_func (
452 p_func_name IN VARCHAR2) RETURN VARCHAR2
453 IS
454 CURSOR c_page_object_name(p_func_name VARCHAR2) IS
455 SELECT object_name FROM bis_obj_dependency
456 WHERE object_type = 'PAGE' AND object_name = p_func_name || '_OA'
457 UNION ALL
458 SELECT object_name FROM bis_obj_properties
459 WHERE object_type = 'PAGE' AND object_name = p_func_name || '_OA';
460
461 v_object_name bis_obj_dependency.object_name%type; --Enhancement 4106617
462 BEGIN
463 IF (p_func_name IS NULL OR p_func_name = '') THEN
464 RETURN NULL;
465 END IF;
466 OPEN c_page_object_name(p_func_name);
467 FETCH c_page_object_name INTO v_object_name;
468 IF (c_page_object_name%notfound) THEN
469 -- no _OA attached
470 v_object_name := p_func_name;
471 END IF;
472 CLOSE c_page_object_name;
473 RETURN v_object_name;
474 EXCEPTION
475 WHEN OTHERS THEN
476 RETURN NULL;
477 END get_page_name_by_func;
478
479
480
481 ---Added for enhancement 4422645
482 PROCEDURE cascade_implflag_to_reports(p_page_function in varchar2,
483 p_impl_flag in varchar2) is
484
485 cursor reports_under_page(p_page_name varchar2) is
486 select depend_objects.obj_name
487 from
488 ( select distinct
489 obj.depend_OBJECT_NAME obj_name,
490 obj.depend_object_type obj_type,
491 obj.depend_object_owner obj_owner
492 from
493 ( select object_name,
494 object_type,
495 object_owner,
496 depend_object_name,
497 depend_object_type,
498 depend_object_owner,
499 enabled_flag
500 from
501 bis_obj_dependency
502 where enabled_flag='Y' ) obj
503 start with obj.object_type ='PAGE'
504 and obj.object_name = p_page_name
505 connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
506 and prior depend_object_type=object_type
507 ) depend_objects
508 where depend_objects.obj_type='REPORT';
509
510 l_report_rec reports_under_page%rowtype;
511
512 l_page_name bis_obj_properties.object_name%type;
513
514 l_report_in_impl_pages varchar2(1);
515
516 begin
517 l_page_name:=get_page_name_by_func(p_page_function);
518 for l_report_rec in reports_under_page(l_page_name) loop
519 l_report_in_impl_pages:='N';
520 if p_impl_flag='Y' then
521 execute immediate 'update bis_obj_properties set implementation_flag=:1 where object_type=:2 and object_name=:3'
522 using 'Y','REPORT',l_report_rec.obj_name;
523 else ---p_impl_flag='N'
524 l_report_in_impl_pages:=report_in_impl_pages(l_report_rec.obj_name);
525 if l_report_in_impl_pages='N' then
526 execute immediate 'update bis_obj_properties set implementation_flag=:1 where object_type=:2 and object_name=:3'
527 using 'N','REPORT',l_report_rec.obj_name;
528 end if;
529 end if;
530 end loop;
531 exception
532 when others then
533 raise;
534 end ;
535
536 -- public api: set implementation flag for a page identified by form function name
537 PROCEDURE setfndformfuncpageimplflag (
538 p_func_name IN VARCHAR2,
539 p_impl_flag IN VARCHAR2,
540 x_return_status OUT nocopy VARCHAR2,
541 x_msg_data OUT nocopy VARCHAR2
542 ) IS
543 v_impl_flag VARCHAR2(10);
544 v_page_name VARCHAR2(480); --Enhancement 4106617
545 BEGIN
546 IF (p_func_name IS NULL OR p_func_name = '') THEN
547 x_return_status := fnd_api.g_ret_sts_error;
548 x_msg_data := 'BIS_BIA_INV_PAGE_FORM_FUNC';
549 RETURN;
550 END IF;
551
552 IF (p_impl_flag IS NULL OR (p_impl_flag <> 'Y' AND p_impl_flag <> 'N')) THEN
553 x_return_status := fnd_api.g_ret_sts_error;
554 x_msg_data := 'BIS_BIA_INVALID_IMPL_FLAG';
555 RETURN;
556 END IF;
557
558 v_impl_flag := getfndformfuncpageimplflag(p_func_name);
559
560 IF (v_impl_flag IS NOT NULL AND v_impl_flag = 'INVALID') THEN
561 -- object doesn't have at least one enabled portlet
562 x_return_status := fnd_api.g_ret_sts_error;
563 x_msg_data := 'BIS_BIA_PAGE_NO_ENABLED_PORTLETS';
564 ELSIF (v_impl_flag IS NULL) THEN
565 -- invalid form function name
566 x_return_status := fnd_api.g_ret_sts_error;
567 x_msg_data := 'BIS_BIA_INVALID_FORM_FUNC_FOR_PAGE';
568 ELSIF (v_impl_flag = 'Y' OR v_impl_flag = 'N') THEN
569 v_page_name := get_page_name_by_func(p_func_name);
570 changeimplementation(v_page_name, p_impl_flag);
571 -- successfully return
572 x_return_status := FND_API.G_RET_STS_SUCCESS;
573 END IF;
574
575 ---06/21/2005 Modified for enhancement 4422645, added logic to enable/disable reports
576 ---under the page based on UI requirement
577 cascade_implflag_to_reports(p_func_name, p_impl_flag);
578
579 RETURN;
580 EXCEPTION
581 WHEN OTHERS THEN
582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
583 x_msg_data := 'BIS_BIA_UNEXPECTED_ERROR';
584 RETURN;
585 END;
586
587
588 ---added for enhancement 4422645
589 PROCEDURE setreportimplflag (
590 p_report_name IN VARCHAR2,
591 p_impl_flag IN VARCHAR2,
592 x_return_status OUT nocopy VARCHAR2,
593 x_msg_data OUT nocopy VARCHAR2
594 ) IS
595 BEGIN
596 IF (p_impl_flag IS NULL OR (p_impl_flag <> 'Y' AND p_impl_flag <> 'N')) THEN
597 x_return_status := fnd_api.g_ret_sts_error;
598 x_msg_data := 'BIS_BIA_INVALID_IMPL_FLAG';
599 RETURN;
600 END IF;
601
602 execute immediate 'update bis_obj_properties set implementation_flag=:1 where object_type=:1 and object_name=:2'
603 using p_impl_flag,'REPORT' ,p_report_name;
604 x_return_status := FND_API.G_RET_STS_SUCCESS;
605 RETURN;
606 EXCEPTION
607 WHEN OTHERS THEN
608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
609 x_msg_data := 'BIS_BIA_UNEXPECTED_ERROR';
610 RETURN;
611 END;
612
613
614
615
616 -- public api to get implementation flag for a page object identified by form function name
617 FUNCTION getfndformfuncpageimplflag (
618 p_func_name IN VARCHAR2
619 ) RETURN VARCHAR2 IS
620 v_is_valid_page_func VARCHAR2(5);
621 v_page_name bis_obj_dependency.object_name%type; --Enhancement 4106617
622 v_portlet_name bis_obj_dependency.depend_object_name%type; --Enhancement 4106617
623 v_ret_code VARCHAR2(10);
624
625 CURSOR c_enabled_dep_portlets (p_page_obj_name VARCHAR2) IS
626 SElECT depend_object_name
627 FROM bis_obj_dependency objdep
628 WHERE objdep.object_name = p_page_obj_name
629 AND objdep.object_type = 'PAGE'
630 AND objdep.depend_object_type = 'PORTLET'
631 AND objdep.ENABLED_FLAG = 'Y';
632
633 CURSOR c_getimpl_flag (p_page_obj_name VARCHAR2) IS
634 SELECT Nvl (implementation_flag, 'N') implflag
635 FROM bis_obj_properties
636 WHERE object_type = 'PAGE' AND object_name = p_page_obj_name;
637 BEGIN
638 v_is_valid_page_func := is_valid_page_func(p_func_name);
639 --dbms_output.put_line('v_is_valid_page_func: '||v_is_valid_page_func);
640 IF (v_is_valid_page_func = 'N') THEN
641 -- invalid page form function name
642 RETURN NULL;
643 END IF;
644
645 v_page_name := get_page_name_by_func(p_func_name);
646 --dbms_output.put_line('v_page_name: '||v_page_name);
647 OPEN c_enabled_dep_portlets(v_page_name);
648 FETCH c_enabled_dep_portlets INTO v_portlet_name;
649 IF (c_enabled_dep_portlets%notfound) THEN
650 -- doesn't have at least one enabled dependent portlet object
651 v_ret_code := 'INVALID';
652 --dbms_output.put_line('invalid');
653 ELSE
654 -- for implementation flag, default null as 'N'
655 IF (c_getimpl_flag%ISOPEN) THEN
656 CLOSE c_getimpl_flag;
657 END IF;
658 OPEN c_getimpl_flag(v_page_name);
659 FETCH c_getimpl_flag INTO v_ret_code;
660 CLOSE c_getimpl_flag;
661 END IF;
662 CLOSE c_enabled_dep_portlets;
663 RETURN v_ret_code;
664 EXCEPTION
665 WHEN OTHERS THEN
666 RETURN NULL;
667 END getfndformfuncpageimplflag;
668 -- end: added for bug 3560408
669
670
671 ---added for enhancement 4422645
672 -- public api to get implementation flag for a report
673 ---Returned values: 'Y', 'N' ---implementation flags
674 ---'INVALID' when the report is not in RSG or doesn't have dependent objects,the UI should grey out
675 ---the Enabled check box
676 ---null when exception happens
677 FUNCTION getreportimplflag (
678 p_report_name IN VARCHAR2
679 ) RETURN VARCHAR2 IS
680
681 v_ret_code VARCHAR2(10);
682
683 CURSOR c_enabled_dep_objects IS
684 select 'Y'
685 from dual
686 where exists
687 (SElECT depend_object_name
688 FROM bis_obj_dependency objdep
689 WHERE objdep.object_name = p_report_name
690 AND objdep.object_type = 'REPORT'
691 AND objdep.ENABLED_FLAG = 'Y');
692
693 --added for bug 4532066
694 CURSOR c_linked_programs IS
695 select 'Y'
696 from dual
697 where exists
698 (SElECT object_name
699 FROM bis_obj_prog_linkages
700 WHERE object_name = p_report_name
701 AND object_type = 'REPORT'
702 AND ENABLED_FLAG = 'Y');
703
704 CURSOR c_getimpl_flag IS
705 SELECT object_name,implementation_flag implflag
706 FROM bis_obj_properties
707 WHERE object_type = 'REPORT' AND object_name = p_report_name;
708
709 l_dummy varchar2(1);
710 l_prog_exist varchar2(1);
711 l_report_in_impl_pages varchar2(1);
712 l_object_name bis_obj_properties.object_name%type;
713
714 BEGIN
715 l_dummy:='N';
716 l_object_name:=null;
717
718 OPEN c_enabled_dep_objects;
719 FETCH c_enabled_dep_objects INTO l_dummy;
720 close c_enabled_dep_objects;
721
722 -- logic modified for the bug 4532066
723 -- if no dep objects then we have to check if there is any program associated with
724 -- the report
725 l_prog_exist:='N';
726 OPEN c_linked_programs;
727 FETCH c_linked_programs INTO l_prog_exist;
728 close c_linked_programs;
729
730 IF l_dummy='N' AND l_prog_exist='N' THEN
731 -- report doesn't have at least one enabled dependent object
732 -- and no program is linked with the report
733 v_ret_code := 'INVALID';
734 ELSE
735 OPEN c_getimpl_flag;
736 FETCH c_getimpl_flag INTO l_object_name, v_ret_code;
737 CLOSE c_getimpl_flag;
738 if v_ret_code is null then
739 if l_object_name is not null then
740 ----added for backward compatibility
741 --- For the case when a new report is added to an existing implemented page
742 ----at that moment the report in bis_obj_properties has implementation_flag as null
743 l_report_in_impl_pages:=report_in_impl_pages(p_report_name) ;
744 if l_report_in_impl_pages='Y' then
745 v_ret_code:='Y';
746 else
747 v_ret_code:='N';
748 end if;
749 else --l_object_name is null, which means the report doesn't exist in RSG
750 v_ret_code:='INVALID';
751 end if ;--if l_object_name is not null
752 end if; --v_ret_code is null
753 END IF;
754 RETURN v_ret_code;
755 EXCEPTION
756 WHEN OTHERS THEN
757 RETURN NULL;
758 END ;
759
760
761
762 -- code added for bug 3736131
763 -- this public API has been added at the request of Product teams
764 -- As they needed one API To check if their Module has been implemented or not
765 -- public api to know if the page is implemented or not
766 -- This will raise whatever exception occurs, so that the wrapper JAVA API
767 -- or whoever is calling this exception may get the exception and error is easy to track
768 -- Though in normal circumstances there will be no exception. ONLY IF Database goes down
769 -- Or the tables do not exist which is rarest possibility
770
771 FUNCTION isPageImplemented (
772 p_func_name IN VARCHAR2
773 ) RETURN VARCHAR2 IS
774 v_is_valid_page_func VARCHAR2(5);
775 v_page_name VARCHAR2(480); --Enhancement 4106617
776 v_ret_code VARCHAR2(10);
777 BEGIN
778 v_is_valid_page_func := is_valid_page_func(p_func_name);
779
780 IF (v_is_valid_page_func = 'N') THEN -- invalid page form function name
781 RETURN NULL;
782 END IF;
783
784 v_page_name := get_page_name_by_func(p_func_name);
785
786 -- for implementation flag, default null as 'N'
787 execute immediate 'select nvl(implementation_flag, :1) implflag
788 FROM bis_obj_properties
789 WHERE object_type = :2 AND object_name = :3'
790 INTO v_ret_code
791 using 'N','PAGE', v_page_name;
792
793 return v_ret_code;
794
795 EXCEPTION
796 WHEN OTHERS THEN
797 RAISE;
798 END;
799 -- end: added for Enhancement 3736131
800
801
802 ---This function is for RSG internal use only
803 function get_impl_flag(p_obj_name in varchar2,p_obj_type in varchar2) return varchar2 is
804 l_impl_flag varchar2(1);
805 begin
806 select implementation_flag
807 into l_impl_flag
808 from bis_obj_properties where object_name=p_obj_name and object_type=p_obj_type;
809 return l_impl_flag;
810 exception
811 when no_data_found then
812 return 'N';
813 when others then
814 raise;
815 end;
816
817 function check_implementation return varchar2 is
818 l_dummy varchar2(1);
819
820 cursor l_check_impl is
821 select 'Y'
822 from dual
823 where exists
824 (select 'Y' from bis_obj_properties
825 where implementation_flag='Y'
826 and object_type in ('PAGE','REPORT')
827 ) ;
828
829 begin
830 l_dummy:='N';
831 open l_check_impl;
832 fetch l_check_impl into l_dummy;
833 if l_check_impl%notfound then
834 l_dummy:='N';
835 end if;
836 close l_check_impl;
837 return l_dummy;
838 exception
839 when others then
840 raise;
841 end;
842
843 END BIS_IMPL_OPT_PKG;
844
845
[Home] [Help]