1 package body BIS_SUBMIT_REQUESTSET AS
2 /*$Header: BISSRSUB.pls 120.18.12010000.2 2008/08/12 07:46:54 bijain ship $*/
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(120.18.12000000.2=120.19):~PROD:~PATH:~FILE
5
6 procedure log(MODULE IN VARCHAR2,
7 MESSAGE IN VARCHAR2) IS
8 begin
9 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
10 FND_LOG.string(FND_LOG.LEVEL_ERROR, module, message);
11 END IF;
12 end;
13
14 function get_parameter_flag(p_program_name varchar2, p_app_id number) return varchar2 is
15 cursor c_parameter is
16 select 'Y'
17 from dual
18 where exists
19 (select descriptive_flexfield_name
20 from fnd_descr_flex_column_usages
21 where application_id=p_app_id
22 and descriptive_flex_context_code = 'Global Data Elements'
23 and descriptive_flexfield_name='$SRS$.'||p_program_name
24 and enabled_flag='Y'
25 and display_flag='Y');
26
27 l_dummy varchar2(1);
28
29 begin
30 open c_parameter;
31 fetch c_parameter into l_dummy;
32 if c_parameter%notfound then
33 return 'N';
34 else
35 return l_dummy;
36 end if;
37 exception
38 when others then
39 raise;
40 end;
41
42 procedure update_default_value(p_program_name varchar2, p_app_id number) is
43 sqlstmt varchar2(2000);
44 begin
45 /** comment out the code because this procedure logic will not
46 be used in current RSG
47 sqlstmt:='update fnd_descr_flex_column_usages '||
48 'set default_value=null '||
49 'where application_id='||p_app_id||
50 ' and descriptive_flexfield_name='||'''$SRS$.'||p_program_name||''''||
51 ' and default_value is not null '||
52 ' and enabled_flag =''Y'''||
53 ' and display_flag=''Y'''||
54 ' and upper(END_USER_COLUMN_NAME) like ''%DATE%'''||
55 ' and default_type=''S''';
56 --dbms_output.put_line(substr(sqlstmt,1,200));
57 --dbms_output.put_line(substr(sqlstmt,201,200));
58 execute immediate sqlstmt;
59 commit;
60 exception
61 when others then
62 raise;
63 **/
64 null;
65 end;
66
67
68
69 procedure sort_table(p_sorting_tbl in out NOCOPY table_sorting_tbl_type) is
70
71 l_length NUMBER;
72 l_incr NUMBER;
73 l_first number;
74 l_temp_amt date;
75 l_temp_index NUMBER;
76 l_temp_num NUMBER;
77 l_sorted_tbl table_sorting_tbl_type;
78 begin
79
80
81 l_length := p_sorting_tbl.COUNT;
82 l_incr := trunc(l_length/2);
83 l_first := p_sorting_tbl.FIRST;
84
85
86 -- sorting p_sorting_tbl using SHELL sort method
87 --
88
89 WHILE l_incr >= 1 LOOP
90
91 FOR i IN l_incr + l_first .. l_first + l_length - 1 LOOP
92
93 -- hold the values at the current index intemporary variables
94 --
95 l_temp_index := p_sorting_tbl(i).tbl_index;
96 l_temp_amt := p_sorting_tbl(i).refresh_date;
97 l_temp_num := i;
98
99 WHILE ( l_temp_num >= l_incr + l_first AND l_temp_amt >
100 p_sorting_tbl(l_temp_num - l_incr).refresh_date ) LOOP
101
102
103 p_sorting_tbl(l_temp_num).tbl_index :=
104 p_sorting_tbl(l_temp_num - l_incr).tbl_index;
105
106 p_sorting_tbl(l_temp_num).refresh_date :=
107 p_sorting_tbl(l_temp_num - l_incr).refresh_date;
108 l_temp_num := l_temp_num - l_incr;
109
110 END LOOP;
111
112
113 p_sorting_tbl(l_temp_num).tbl_index := l_temp_index;
114 p_sorting_tbl(l_temp_num).refresh_date := l_temp_amt;
115
116 END LOOP;
117
118 l_incr := trunc(l_incr/2);
119
120 END LOOP;
121
122
123 end;
124
125 ----added for bug 4532066
126 function portlet_has_impl_report(p_portlet_name in varchar2) return varchar2 is
127 cursor c_reports is
128 select distinct
129 obj.depend_OBJECT_NAME ,
130 obj.depend_object_type
131 from
132 (select object_name,
133 object_type,
134 object_owner,
135 depend_object_name,
136 depend_object_type,
137 depend_object_owner,
138 enabled_flag
139 from
140 bis_obj_dependency
141 where enabled_flag='Y') obj
142 where depend_object_type='REPORT'
143 start with
144 obj.object_type ='PORTLET'
145 and obj.object_name=p_portlet_name
146 connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
147 and prior obj.DEPEND_OBJECT_TYPE=obj.object_TYPE ;
148
149 l_report_rec c_reports%rowtype;
150 l_impl_report varchar2(1);
151 l_dummy number;
152 begin
153 l_impl_report:='N';
154 l_dummy:=0;
155 for l_report_rec in c_reports loop
156 l_dummy:=l_dummy+1;
157 if BIS_IMPL_OPT_PKG.get_impl_flag(l_report_rec.depend_object_name,'REPORT')='Y' then
158 l_impl_report:='Y';
159 exit;
160 end if;
161 end loop;
162
163 ---added for bug 4675702
164 ---handle the corner case where objects are linked to portlets directly
165 ---without reports in between.
166 if l_dummy=0 then
167 l_impl_report:='Y';
168 end if;
169 return l_impl_report;
170 exception
171 when others then
172 raise;
173 end;
174
175
176 function get_last_refreshtime(p_obj_type varchar2,p_obj_owner varchar2,p_obj_name varchar2) return varchar2
177 is
178 begin
179 if p_obj_type<>'PAGE' then
180 if get_last_refreshdate(p_obj_type,p_obj_owner,p_obj_name) <>to_date('01-01-1900','DD-MM-YYYY') then
181 return fnd_date.date_to_charDT(get_last_refreshdate(p_obj_type,p_obj_owner,p_obj_name)) ;
182 else
183 return null;
184 end if;
185 else ----this api is used by RSG only. Now we don't show time for pages in RSG
186 return null;
187 end if;
188 exception
189 when others then
190 --dbms_output.put_line(sqlerrm);
191 raise;
192 end;
193
194
195 function get_obj_refresh_date_old(p_obj_type varchar2,p_obj_owner varchar2,p_obj_name varchar2) return date
196 is
197
198 cursor c_obj_latest_date is
199 select max(temp.latest_date) latest_date
200 from
201 (select
202 max(aa.actual_completion_date) latest_date,
203 aa.program_application_id,
204 aa.concurrent_program_id
205 from
206 fnd_concurrent_requests aa,
207 ( select distinct
208 b.application_id application_id,
209 b.CONCURRENT_PROGRAM_ID concurrent_program_id
210 from
211 bis_obj_prog_linkages a,
212 fnd_concurrent_programs b
213 where a.object_name=p_obj_name
214 and a.object_type=p_obj_type
215 and a.CONC_PROGRAM_NAME=b.concurrent_program_name
216 and a.conc_program_name <>'BSC_REFRESH_SUMMARY_IND'
217 and a.CONC_APP_ID=b.application_id
218 and a.enabled_flag='Y'
219 and b.enabled_flag='Y'
220 and a.refresh_mode in ('INIT','INCR','INIT_INCR')) bb
221 where
222 aa.program_application_id= bb.application_id
223 and aa.concurrent_program_id=bb.concurrent_program_id
224 and aa.status_code in ('I','R','G','C')
225 and aa.phase_code='C'
226 group by aa.program_application_id,aa.concurrent_program_id) temp;
227
228 cursor c_mv_latest_date is
229 select
230 max(aa.actual_completion_date) latest_time
231 -- aa.program_application_id program_application_id,
232 -- aa.concurrent_program_id concurrent_program_id
233 from
234 fnd_concurrent_requests aa,
235 fnd_concurrent_programs bb
236 where bb.concurrent_program_name='BIS_MV_REFRESH'
237 and bb.application_id=191
238 and aa.program_application_id=bb.application_id
239 and aa.concurrent_program_id=bb.concurrent_program_id
240 and aa.status_code in ('I','R','G','C')
241 and aa.phase_code='C'
242 and aa.argument2=p_obj_name
243 and aa.argument1 in ('INIT','INCR');
244 ---group by aa.program_application_id,aa.concurrent_program_id;
245
246 cursor c_mv_has_program is
247 select 'Y'
248 from dual
249 where exists
250 (select 'Y'
251 from bis_obj_prog_linkages a,
252 fnd_concurrent_programs b
253 where a.object_name=p_obj_name
254 and a.object_type='MV'
255 and a.enabled_flag='Y'
256 and a.CONC_APP_ID=b.application_id
257 and a.CONC_PROGRAM_NAME=b.concurrent_program_name
258 and b.enabled_flag='Y');
259
260 cursor c_auto_gen_report_date is
261 select
262 max(aa.actual_completion_date) latest_time
263 -- aa.program_application_id program_application_id,
264 -- aa.concurrent_program_id concurrent_program_id
265 from
266 fnd_concurrent_requests aa,
267 fnd_concurrent_programs bb
268 where bb.concurrent_program_name='BSC_REFRESH_SUMMARY_IND'
269 and bb.application_id=271
270 and aa.program_application_id=bb.application_id
271 and aa.concurrent_program_id=bb.concurrent_program_id
272 and aa.status_code in ('I','R','G','C')
273 and aa.phase_code='C'
274 and aa.argument1=to_char(bis_create_requestset.get_indicator_auto_gen(p_obj_name));
275
276
277 l_obj_latest_date date;
278 l_dummy varchar2(1);
279 l_module varchar2(300) := 'bis.GET_LAST_REFRESH_DATE.'||p_obj_type||'.'||p_obj_name;
280
281 begin
282 l_obj_latest_date:=null;
283 l_dummy:=null;
284
285 if p_obj_type='MV' then
286 open c_mv_has_program;
287 fetch c_mv_has_program into l_dummy;
288 if c_mv_has_program%notfound then
289 l_dummy:='N';
290 end if;
291 close c_mv_has_program;
292 if l_dummy='Y' then
293 open c_obj_latest_date;
294 fetch c_obj_latest_date into l_obj_latest_date;
295 close c_obj_latest_date;
296 else
297 open c_mv_latest_date;
298 fetch c_mv_latest_date into l_obj_latest_date;
299 close c_mv_latest_date;
300 end if;
301 else
302 open c_obj_latest_date;
303 fetch c_obj_latest_date into l_obj_latest_date;
304 close c_obj_latest_date;
305 end if;
306
307 if p_obj_type='REPORT' and bis_create_requestset.get_report_type(p_obj_name)='BSCREPORT' then
308 open c_auto_gen_report_date;
309 fetch c_auto_gen_report_date into l_obj_latest_date;
310 close c_auto_gen_report_date;
311 end if;
312
313 log(l_module, 'Got ' || l_obj_latest_date || ' from FND for (' || p_obj_name ||','|| p_obj_type ||')' );
314 -- bis_collection_utilities.put_line('Got ' || l_obj_latest_date || ' from FND for (' || p_obj_name ||','|| p_obj_type ||')' );
315 return l_obj_latest_date;
316 exception
317 when others then
318 raise;
319 end;
320
321 -- for backward compatibility, call get_obj_refresh_date_old if new logic get nothing.
322 function get_obj_refresh_date(p_obj_type varchar2,p_obj_owner varchar2,p_obj_name varchar2) return date
323 is
324 CURSOR C_OBJ_LST_REFDAT(p_obj_name VARCHAR2, p_obj_type VARCHAR2)
325 IS
326 select last_refresh_date
327 from bis_obj_properties
328 where object_name= p_obj_name
329 and object_type= p_obj_type;
330 l_date DATE;
331 l_module varchar2(300) := 'bis.GET_LAST_REFRESH_DATE.'||p_obj_type||'.'||p_obj_name;
332 begin
333 open C_OBJ_LST_REFDAT(p_obj_name, p_obj_type);
334 fetch C_OBJ_LST_REFDAT into l_date;
335 close C_OBJ_LST_REFDAT;
336 log(l_module, 'Got ' || l_date || ' from bis_obj_properties.last_refresh_date for (' || p_obj_name ||','|| p_obj_type ||')' );
337 bis_collection_utilities.put_line(' '||p_obj_type||'.'||p_obj_name||': '||to_char(l_date,'DD-MON-YYYY'));
338 if (l_date is null ) then
339 return get_obj_refresh_date_old(p_obj_type,p_obj_owner,p_obj_name);
340 else
341 return l_date;
342 end if;
343 end;
344
345
346
347 ---Modify the logic in this function for bug 4257955 so that it has the following behavior
348 ---- (0)If the user never run the request set to refresh the report/portlet, the
349 ------ last refresh date will be null. No matter if the reports are real time reports or
350 ------the reports that need run refresh programs.
351 --- (1)After the request set has been run,if the report or portlet doesn't have any programs or MVs attached to it
352 ---RSG will not maintain its last refresh date. It is treated the same
353 ----as those reports/portlets not registered in RSG, Hence we return null
354 ----instead of "Date not available"
355 ----(2)After the request set has been run, If the report or portlet has programs or MVs attached to it, RSG will
356 ---- update its last refresh date accordingly every time when the request set is run
357 ---- If all objects under the report or portlet are refreshed successfully, the report
358 -----or portlet will have last refresh date updated; if for some reason, one or more objects under
359 ----the report or portlet are never being refreshed, then the report or portlet will
360 -----have special date '01-01-1900'. Then the get_last_refreshdate api will recognize this
361 -----and return 'Date not available' for this case
362 ----Please note that we can also move this check logic (check if programs or MVs exist) into get last refresh date API
363 -----then we don't need to set a special date (01-01-1900) here for report/portlet. However the concern is that it may
364 ---cause runtime performance issue, because the api is called by PMV during rendering time
365 function derive_portlet_report_date(p_object_name in varchar2,p_object_type in varchar2,p_refresh_mode in varchar2) return date is
366
367 cursor c_objects is
368 select distinct
369 --- obj.depend_object_owner object_owner,
370 obj.depend_object_type object_type,
371 obj.depend_OBJECT_NAME object_name
372 from
373 ( select object_name,
374 object_type,
375 object_owner,
376 depend_object_name,
377 depend_object_type,
378 depend_object_owner,
379 enabled_flag
380 from
381 bis_obj_dependency
382 where enabled_flag='Y')obj
383 start with obj.object_type =p_object_type and obj.object_name=p_object_name
384 connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
385 and prior obj.DEPEND_OBJECT_TYPE=obj.object_type
386 union----the following part is for picking up the report itself
387 select distinct
388 --- objdep.object_owner object_owner,
389 objp.object_type object_type,
390 objp.object_name object_name
391 from
392 bis_obj_properties objp
393 where objp.object_name=p_object_name
394 and objp.object_type = p_object_type;
395
396
397 l_obj_rec c_objects%rowtype;
398
399
400 -----Fix for bug 3278518
401 -----If an object has only initial loading program,skip it while deriving the last refresh date
402 ----for report or portlet in incremental loading mode
403 -----If an object has only incremental loading program, skip it while deriving the
404 ----last refresh date for report or portlet in initial loading mode
405 --- After bug fix 4257955, if the report or portlet has only one refresh program
406 ----in the dependency tree,if the program has mode 'INIT', then the report will have
407 ----last refresh date =null in incremental loading mode; if the program has mode 'INCR', then
408 ---the report will have last refresh date=null in initial loading mode.
409 ----This is better than return 'Not available' we had before
410 cursor c_obj_has_program(p_obj_type varchar2, p_obj_name varchar2,p_refresh_mode varchar2)
411 is
412 select 'Y'
413 from dual
414 where exists (select 'Y'
415 from bis_obj_prog_linkages a,
416 fnd_concurrent_programs b
417 where a.object_name=p_obj_name
418 and a.object_type=p_obj_type
419 and (a.refresh_mode=p_refresh_mode or a.refresh_mode='INIT_INCR')
420 and a.enabled_flag='Y'
421 and a.CONC_APP_ID=b.application_id
422 and a.CONC_PROGRAM_NAME=b.concurrent_program_name
423 and b.enabled_flag='Y'
424 );
425
426
427 l_prog_flag varchar2(1);
428
429 l_date date;
430 l_temp_date date;
431 l_obj_refresh_dates table_sorting_tbl_type;
432 l_dummy varchar2(1);
433 l_counter number;
434 l_module varchar2(300) := 'bis.DERIVE_PORTLET_REPORT_DATE.'||p_object_type||'.'||p_object_name;
435
436 begin
437 l_dummy:=null;
438 l_counter:=0;
439 for l_obj_rec in c_objects loop
440 ----added for bug 4532066
441 if BIS_IMPL_OPT_PKG.get_impl_flag(l_obj_rec.object_name,l_obj_rec.object_type)='Y' then
442 l_temp_date:=null;
443 open c_obj_has_program(l_obj_rec.object_type, l_obj_rec.object_name,p_refresh_mode);
444 fetch c_obj_has_program into l_prog_flag;
445 if c_obj_has_program%notfound then
446 if l_obj_rec.object_type='MV' then
447 l_prog_flag:='Y';
448 else
449 l_prog_flag:='N';
450 log(l_module, 'found no program defined for obj: ' || l_obj_rec.object_name );
451 end if;
452 end if;
453 close c_obj_has_program;
454
455 if l_prog_flag='Y' then
456 l_temp_date:=get_obj_refresh_date(l_obj_rec.object_type,null,l_obj_rec.object_name);
457 log(l_module, 'found (program, last_refresh_date)= (' || l_obj_rec.object_name || ', ' || to_char(l_temp_date, 'dd-Mon-yyyy hh:mi:ss') ||')');
458 end if;
459
460 ---Bug 4257955.If the object has never been refreshed successfully, it has '01-01-1900'
461 ----After sorting with other objects, it will make the repor/portlet
462 ----have the last refresh date as '01-01-1900' automatically
463 ----So the get last refresh date api will return 'Date not available'
464
465
466 if l_temp_date is not null then
467 l_counter:=l_counter+1;
468 l_obj_refresh_dates(l_counter).tbl_index:=l_counter;
469 l_obj_refresh_dates(l_counter).refresh_date:=l_temp_date;
470 end if;
471 end if;
472
473 end loop;
474
475 if l_counter>0 then
476 log(l_module, 'sorting...' );
477 sort_table(l_obj_refresh_dates);
478 --- for i in 1..l_counter loop
479 ---dbms_output.put_line('index: '||l_obj_refresh_dates(i).tbl_index||' date: '||l_obj_refresh_dates(i).refresh_date);
480 ---end loop;
481 l_date:=l_obj_refresh_dates(l_counter).refresh_date; ---the minimum date among all objects
482 else
483 -----Bug 4257955. if the report has no programs or MVs in the dependency tree
484 -----the last refresh date will be null and the
485 -----get last refresh date api will return null instead of 'Not available'
486 l_date:=null;
487 log(l_module, 'returning date ' || l_date );
488 end if;
489 return l_date;
490 exception
491 when others then
492 raise;
493 end;
494
495
496 function get_portlet_report_date(p_object_name in varchar2,p_object_type in varchar2) return date is
497 cursor c_direct_date is
498 select LAST_REFRESH_DATE,object_name
499 from bis_obj_properties
500 where object_name= p_object_name
501 and object_type in ('REPORT','PORTLET');
502 --and object_type=p_object_type;
503
504 l_date date;
505 l_object_name bis_obj_properties.object_name%type; --enhancement 4106617
506
507 begin
508 open c_direct_date;
509 fetch c_direct_date into l_date,l_object_name;
510 close c_direct_date;
511
512
513 /** Bug 4257955--this piece of logic is moved to update last refresh date program.
514 Objects with programs or MVs but not refreshed successfully
515 will be updated to '01-01-1900' to differenciate from those without
516 programs or MVs defined.
517 ---The following change is for enhancement 3426538
518 if l_object_name is not null then ---object registered in RSG
519 if l_date is null then
520 l_date:=to_date('01-01-1900','DD-MM-YYYY');
521 end if;
522 end if;
523 **/
524
525 if l_object_name is null then ---object not registered in RSG
526 l_date:=null;
527 end if;
528
529 ---Bug 4257955. if the object resides in RSG, whatever date in bis_Obj_porperties should be returned
530 ---a real date, null or '01-01-1900'
531 return l_date;
532 /** Per management requirement, remove the backward compatible logic
533 We no longer derive last refresh date on the fly. The last refresh date will be updated to
534 bis_obj_properties table whenever the request set is re-generated in 4.0.7 and gets run
535 successfully
536
537 if l_date is not null then
538 return l_date;
539 else
540 l_date:=derive_portlet_report_date(p_object_name,p_object_type);
541 return l_date;
542 end if;
543 **/
544 exception
545 when others then
546 raise;
547
548 end;
549
550
551 ----Bug 4257955. The logic of the following function
552 ----is changed to have such behaviors
553 ----(1) If request set has never been run for the page, the last refresh date will be null
554 ----(2) After request set has been run, if the page has no refresh program or MV in the
555 --------whole dependency tree, the last refresh date will be null
556 ----(3) After the request set has been run, if all the objects in the dependency tree
557 -------have been refreshed successfully, the page will have the earliest last refresh date among
558 -------all of them
559 -----(4) After the request set has been run, if some of the objects in the dependency tree
560 ------have never been refreshed successfully ('01-01-1900'), the page last refresh date will
561 ------be '01-01-1900' so the get last refresh date API will return 'Date not available"
562
563 ----This function is added for bug 3310316----
564 function derive_page_date(p_page_name varchar2) return date
565 is
566 ---modified this cursor for bug 4532066
567 ---derive dashboard date based on reports under the dashboard
568 ---bug 4675702: portlets should also be considered because
569 ---in RSG product teams can link objects to portlets directly without reports in between.
570 cursor c_depend_objects is
571 select distinct
572 obj.depend_OBJECT_NAME ,
573 obj.depend_object_type
574 from
575 (select object_name,
576 object_type,
577 object_owner,
578 depend_object_name,
579 depend_object_type,
580 depend_object_owner,
581 enabled_flag
582 from
583 bis_obj_dependency
584 where enabled_flag='Y') obj
585 where depend_object_type in ('REPORT','PORTLET')
586 start with
587 obj.object_type ='PAGE'
588 and obj.object_name=p_page_name
589 connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
590 and prior obj.DEPEND_OBJECT_TYPE=obj.object_TYPE ;
591
592
593 l_obj_rec c_depend_objects%rowtype;
594 l_date date;
595 l_temp_date date;
596 l_obj_refresh_dates table_sorting_tbl_type;
597 l_dummy varchar2(1);
598 l_counter number;
599 l_module varchar2(300) := 'bis.GET_LAST_REFRESH_DATE.'||'PAGE'||'.'||p_page_name;
600 begin
601 l_counter:=0;
602 -----loop through objects under the page to derive the last refresh date for page
603 -----it is possible that a table or MV is attached to page directly
604 for l_obj_rec in c_depend_objects loop
605 ---added for bug 4532066
606 if (l_obj_rec.depend_object_type='REPORT'
607 and BIS_IMPL_OPT_PKG.get_impl_flag(l_obj_rec.depend_object_name,l_obj_rec.depend_object_type)='Y' )
608 ---added for bug 4675702
609 ---The check is necessary. If none of the reports under a portlet is implement
610 --then the portlet should not affect page's date
611 OR (l_obj_rec.depend_object_type='PORTLET'
612 and BIS_IMPL_OPT_PKG.get_impl_flag(l_obj_rec.depend_object_name,l_obj_rec.depend_object_type)='Y'
613 and portlet_has_impl_report(l_obj_rec.depend_object_name)='Y') then
614
615 l_temp_date:=get_last_refreshdate(l_obj_rec.depend_object_type,null,l_obj_rec.depend_object_name);
616
617 /** comment out the following logic for bug 4257955
618 If a report or portlet has date '01-01-1900', it should
619 make the page last refresh date '01-01-1900' automatically
620 if l_temp_date=to_date('01-01-1900','DD-MM-YYYY') then
621 l_temp_date:=null;
622 end if;
623 **/
624 log(l_module, 'found last_refresh_date= ('||l_obj_rec.depend_object_type||'.'|| l_obj_rec.depend_object_name || ', ' || to_char(l_temp_date, 'dd-Mon-yyyy hh:mi:ss') ||')');
625 bis_collection_utilities.put_line(' '||l_obj_rec.depend_object_type||'.'|| l_obj_rec.depend_object_name || ': ' || to_char(l_temp_date, 'DD-MON-YYYY'));
626 if l_temp_date is not null then
627 l_counter:=l_counter+1;
628 l_obj_refresh_dates(l_counter).tbl_index:=l_counter;
629 l_obj_refresh_dates(l_counter).refresh_date:=l_temp_date;
630 end if;
631 end if;
632 end loop;
633 log(l_module, 'sorting...' );
634 sort_table(l_obj_refresh_dates);
635 if l_counter >0 then
636 l_date:=l_obj_refresh_dates(l_counter).refresh_date; ---the minimum date among all reports
637 else
638 l_date:=null;
639 end if;
640 log(l_module, 'returning date ' || l_date );
641 return l_date;
642
643 exception
644 when others then
645 raise;
646 end;
647
648
649 ----This function is added for bug 3310316----
650 function get_page_date(p_page_name varchar2) return date
651 is
652
653 /**The following cursor has the correct logic
654 but it caused number of sqls being executed exceed 10
655 cursor c_direct_date is
656 select LAST_REFRESH_DATE
657 from bis_obj_properties
658 where bis_impl_dev_pkg.get_function_by_page(object_name)=bis_impl_dev_pkg.get_function_by_page(p_page_name)
659 and object_type='PAGE';
660 **/
661
662 /**The following cursor has logic hole
663 It can't handle the case where form function A and
664 A_OA exist for two OA pages, then two rows will be returned
665 cursor c_direct_date is
666 select LAST_REFRESH_DATE
667 from bis_obj_properties
668 where object_name=p_page_name||'_OA'
669 and object_type='PAGE'
670 union
671 select LAST_REFRESH_DATE
672 from bis_obj_properties
673 where object_name=p_page_name
674 and object_type='PAGE';
675 **/
676 cursor c_direct_date1 is
677 select LAST_REFRESH_DATE,object_name
678 from bis_obj_properties
679 where object_name=p_page_name
680 and object_type='PAGE';
681
682 cursor c_direct_date2 is
683 select LAST_REFRESH_DATE,object_name
684 from bis_obj_properties
685 where object_name=p_page_name||'_OA'
686 and object_type='PAGE';
687
688
689 l_date date;
690 l_object_name bis_obj_properties.object_name%type; --Enhancement 4106617
691
692 begin
693 open c_direct_date1;
694 fetch c_direct_date1 into l_date,l_object_name;
695 close c_direct_date1;
696
697 if l_date is null then
698 open c_direct_date2;
699 fetch c_direct_date2 into l_date,l_object_name;
700 close c_direct_date2;
701 end if;
702
703
704 -----The following code is changed for enhancement 3426538
705 if l_object_name is not null then ----the page exists in RSG
706 /** Bug 4257955. Return whatever date in bis_Obj_properties
707 The logic of '01-01-1900' is moved to update last
708 refresh date program for individual object
709 if l_date is null then
710 l_date:=to_date('01-01-1900','DD-MM-YYYY');
711 end if;
712 **/
713 null;
714 else---the page does't exist in RSG
715 l_date:=null;
716 end if;
717
718
719 /**
720 if l_date is not null then
721 return l_date;
722 else
723 l_date:=derive_page_date(p_page_name);
724 return l_date;
725 end if;
726 **/
727 -----commented the above logic per Mandar's requirement
728 ----We no longer derive page last refresh date
729 ----To see the date on page, the user has to re-generate and run the request set to
730 ----uptake the 4.0.7 enhancement 3040249
731 ---The purpose of doing this is to reduce number of sqls executed when PMV render the page
732 return l_date;
733 exception
734 when others then
735 raise;
736
737 end;
738
739
740 -----Bug 4257955
741 -----The following API can only be used internally by RSG
742 -----To get last refresh date for any object, the public API is
743 -----get_last_refreshdate_url
744 function get_last_refreshdate(p_obj_type varchar2,p_obj_owner varchar2,p_obj_name varchar2) return date
745 is
746
747 l_date date;
748 ----cursor c_olap_function had been commented out since enh 3426538
749 ----the piece of code is removed on Dec 15, 2004 to avoid future confusion
750
751 l_dummy varchar2(1);
752 l_module varchar2(300) := 'bis.GET_LAST_REFRESH_DATE.'||p_obj_type||'.'||p_obj_name;
753 begin
754 l_dummy:=null;
755 if p_obj_type='PORTLET' or p_obj_type='REPORT' then
756
757 /** commented out the following code for enhancement 3426538
758 open c_oltp_function;
759 fetch c_oltp_function into l_dummy;
760 if c_oltp_function%notfound then
761 l_dummy:='N';
762 end if;
763 close c_oltp_function;
764 if l_dummy='Y' then ----per PMV team requirement, form functions not existing in RSG means it is for OLTP
765 log(l_module, 'returning sysdate for OLTP funtion ' || sysdate );
766 l_date:=sysdate;---in this case, we need to return sysdate
767 else
768 log(l_module, 'non OLTP funtion ' );
769 l_date:=get_portlet_report_date(p_obj_name,p_obj_type) ;
770 end if;----end if dummy='Y' **/
771
772 l_date:=get_portlet_report_date(p_obj_name,p_obj_type) ;
773 log(l_module, 'returning '||l_date||' for REPORT and PORTLET type object'||p_obj_name);
774 elsif (p_obj_type='PAGE') then-----change for bug 3310316
775 l_date := get_page_date(p_obj_name);
776 log(l_module, 'returning '||l_date||' for PAGE type object'||p_obj_name);
777
778 else ----other types of objects
779 l_date:=get_obj_refresh_date(p_obj_type,p_obj_owner,p_obj_name);
780 log(l_module, 'returning date for other type of obj: ' || l_date );
781 end if;
782 return l_date;
783
784 exception
785 when others then
786 --dbms_output.put_line(sqlerrm);
787 raise;
788
789
790 end;
791
792 ----find out the latest time that program "Data Loader - Refresh Summary Levels by Indicators"
793 ----being run for the given indicator
794 function get_kpi_refresh_date(p_indicator in varchar2) return date is
795 cursor c_kpi_refresh_date(indicator_id number) is
796 Select
797 max(aa.actual_completion_date) last_refresh_date
798 from
799 fnd_concurrent_requests aa,
800 fnd_concurrent_programs bb
801 where bb.concurrent_program_name='BSC_REFRESH_SUMMARY_IND'
802 and bb.application_id=271
803 and aa.program_application_id=bb.application_id
804 and aa.concurrent_program_id=bb.concurrent_program_id
805 and aa.status_code in ('I','R','G','C')
806 and aa.phase_code='C'
807 and aa.argument1 like '%'||indicator_id||'%'
808 and aa.argument2='N';
809 l_date date;
810
811 begin
812 l_date:=null;
813 open c_kpi_refresh_date(p_indicator);
814 fetch c_kpi_refresh_date into l_date;
815 close c_kpi_refresh_date;
816 return l_date;
817 exception
818 when others then
819 raise;
820 end;
821
822
823
824
825 procedure update_page_portlet_date (p_request_id in number) is
826 ----fixing bug 4053299
827 ---remove the condition parent_request_id=-1 for
828 cursor request_set_id is
829 select to_number(argument2), to_number(argument1)
830 from fnd_concurrent_requests
831 ----where parent_request_id=-1
832 where request_id=p_request_id;
833
834
835 cursor request_set_option(p_request_set_id varchar2, p_req_set_app_id varchar2) is
836 select distinct a.option_value refresh_mode
837 from bis_request_set_options a,
838 fnd_request_sets b
839 where a.request_set_name=b.request_set_name
840 and a.set_app_id=b.application_id
841 and a.option_name='REFRESH_MODE'
842 and b.request_set_id=p_request_set_id
843 and b.application_id=p_req_set_app_id;
844
845 l_refresh_mode varchar2(30);
846
847
848 cursor c_portlet_report_in_set (p_request_id number) is
849 select distinct object_type,
850 object_name,
851 object_owner
852 from BIS_OBJ_SET_TEMP
853 where request_id=p_request_id
854 and object_type in ('REPORT','PORTLET');
855
856
857
858 cursor c_pages_in_set(p_request_id number) is
859 select distinct object_type,
860 object_name,
861 object_owner
862 from BIS_OBJ_SET_TEMP
863 where request_id=p_request_id
864 and object_type ='PAGE';
865
866
867 l_object_rec c_portlet_report_in_set%rowtype;
868 l_page_obj_rec c_pages_in_set%rowtype;
869 l_request_set_id number;
870 l_req_set_app_id number;
871 l_sql varchar2(2000);
872 l_date date;
873 l_indicator varchar2(10);
874 l_request_id_this number;
875
876 begin
877
878 l_request_id_this:=fnd_global.CONC_REQUEST_ID;
879
880 open request_set_id;
881 fetch request_set_id into l_request_set_id, l_req_set_app_id;
882 close request_set_id;
883
884 --- BIS_COLLECTION_UTILITIES.put_line('request set id '||l_request_set_id);
885
886 l_refresh_mode:=null;
887 open request_set_option(l_request_set_id, l_req_set_app_id);
888 fetch request_set_option into l_refresh_mode;
889 close request_set_option;
890
891 if l_refresh_mode is null then
892 l_refresh_mode:='INCR';
893 end if;
894
895 BIS_COLLECTION_UTILITIES.put_line('=====Start updating dates for reports/portlets in the request set===');
896 for l_object_rec in c_portlet_report_in_set(l_request_id_this) loop
897
898 ---added for bug 4532066
899 if l_object_rec.object_type='PORTLET' and BIS_IMPL_OPT_PKG.get_impl_flag(l_object_rec.object_name,l_object_rec.object_type)='Y' and portlet_has_impl_report(l_object_rec.object_name)='N' then
900 BIS_COLLECTION_UTILITIES.put_line('*****'||l_object_rec.object_type||' '||l_object_rec.object_name||' has no implemented reports. Not to update its date.');
901 else if BIS_IMPL_OPT_PKG.get_impl_flag(l_object_rec.object_name,l_object_rec.object_type)='N' then
902 BIS_COLLECTION_UTILITIES.put_line('*****'||l_object_rec.object_type||' '||l_object_rec.object_name||' is not implemented. Not to update its date.');
903 else
904 l_date:=null;
905 l_indicator:=null;
906 if l_object_rec.object_owner=bis_create_requestset.get_bsc_schema_name
907 and l_object_rec.object_type='REPORT'
908 and l_object_rec.object_name like 'BSC%' then
909 l_indicator:=bis_create_requestset.get_indicator(l_object_rec.object_name);
910 end if;
911 if l_indicator is not null then
912 l_date:=get_kpi_refresh_date(l_indicator);
913 else
914 l_date:=derive_portlet_report_date(l_object_rec.object_name,l_object_rec.object_type,l_refresh_mode);
915 end if;
916 BIS_COLLECTION_UTILITIES.put_line('*****'||l_object_rec.object_type||'.'||l_object_rec.object_name||': '||to_char(l_date,'DD-MON-YYYY'));
917 bis_impl_dev_pkg.update_obj_last_refresh_date(l_object_rec.object_type,l_object_rec.object_name, l_date);
918
919 commit;
920 end if;
921 end if;----implementation flag='N'
922 end loop;
923
924 BIS_COLLECTION_UTILITIES.put_line(' ');
925 BIS_COLLECTION_UTILITIES.put_line('=========Start updating dates for pages in the request set====');
926 BIS_COLLECTION_UTILITIES.put_line('Please be informed that if a page/report has date ''01-01-1900'',');
927 BIS_COLLECTION_UTILITIES.put_line('it means that at least one program for the page/report failed or has not been run.');
928 BIS_COLLECTION_UTILITIES.put_line('In this case, ''Data Last Update: Date is not available for Display'' will be displayed on UI');
929
930
931 for l_page_obj_rec in c_pages_in_set(l_request_id_this) loop
932 l_date:=null;
933 l_date:=derive_page_date(l_page_obj_rec.object_name);
934 BIS_COLLECTION_UTILITIES.put_line('*****'||l_page_obj_rec.object_type||'.'||l_page_obj_rec.object_name||': '||to_char(l_date,'DD-MON-YYYY'));
935 bis_impl_dev_pkg.update_obj_last_refresh_date('PAGE',l_page_obj_rec.object_name,l_date);
936 commit;
937 end loop;
938
939
940 BIS_COLLECTION_UTILITIES.put_line('End updating dates for pages and portlets/reports');
941
942 exception
943 when others then
944 raise;
945 end;
946
947
948 function has_loader_sum_prog(p_report_name in varchar2) return varchar2 is
949
950 cursor c_report_program(p_report_name varchar2) is
951 select 'Y'
952 from bis_obj_prog_linkages
953 where object_name=p_report_name
954 and object_type='REPORT'
955 and CONC_PROGRAM_NAME='BSC_REFRESH_SUMMARY_IND';
956
957 l_dummy varchar2(1);
958 begin
959 l_dummy:='N';
960 open c_report_program(p_report_name);
961 fetch c_report_program into l_dummy;
962 close c_report_program;
963 return l_dummy ;
964 end;
965
966
967 procedure update_last_refresh_date(
968 errbuf OUT NOCOPY VARCHAR2,
969 retcode OUT NOCOPY VARCHAR2,
970 p_request_id IN NUMBER
971 ) is
972
973
974 cursor c_obj_direct_in_set (P_REQSET_ID NUMBER) is
975 select distinct a.object_name, a.object_type,a.object_owner
976 from
977 bis_request_set_objects a,
978 fnd_request_sets b,
979 fnd_concurrent_requests c
980 where a.request_set_name=b.request_set_name
981 and a.SET_APP_ID=b.application_id
982 and b.request_set_id=to_number(c.argument2)
983 and b.application_id=to_number(c.argument1)
984 and c.request_id=P_REQSET_ID;
985
986 l_direct_obj_rec c_obj_direct_in_set%rowtype;
987
988
989
990 CURSOR C_OBJ_TO_BE_UPDATED(p_obj_type varchar2,p_obj_name varchar2)
991 IS
992 select distinct
993 obj.depend_OBJECT_NAME object_name,
994 obj.depend_object_type object_type,
995 obj.depend_object_owner object_owner
996 from
997 (select object_name,
998 object_type,
999 object_owner,
1000 depend_object_name,
1001 depend_object_type,
1002 depend_object_owner,
1003 enabled_flag
1004 from
1005 bis_obj_dependency
1006 where enabled_flag='Y') obj
1007 start with
1008 obj.object_type =p_obj_type
1009 and obj.object_name=p_obj_name
1010 connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
1011 and prior obj.DEPEND_OBJECT_TYPE=obj.object_TYPE ;
1012
1013 l_indirect_obj_rec C_OBJ_TO_BE_UPDATED%rowtype;
1014
1015 cursor c_page_name(p_name varchar2) is
1016 select object_name
1017 from bis_obj_properties
1018 where bis_impl_dev_pkg.get_function_by_page(object_name)=bis_impl_dev_pkg.get_function_by_page(p_name)
1019 and object_type='PAGE';
1020
1021 ----This cursor is added for bug 4257955
1022 ----Please note we don't check program refresh mode in this cursor
1023 cursor c_obj_has_program(p_obj_type varchar2, p_obj_name varchar2)
1024 is
1025 select 'Y'
1026 from dual
1027 where exists (select 'Y'
1028 from bis_obj_prog_linkages a,
1029 fnd_concurrent_programs b
1030 where a.object_name=p_obj_name
1031 and a.object_type=p_obj_type
1032 and a.enabled_flag='Y'
1033 and a.CONC_APP_ID=b.application_id
1034 and a.CONC_PROGRAM_NAME=b.concurrent_program_name
1035 and b.enabled_flag='Y'
1036 );
1037
1038 cursor c_obj_with_program(p_request_id number) is
1039 select distinct object_type,
1040 object_name
1041 from BIS_OBJ_SET_TEMP
1042 where request_id=p_request_id
1043 and has_program='Y'
1044 union
1045 select distinct object_type,
1046 object_name
1047 from BIS_OBJ_SET_TEMP
1048 where request_id=p_request_id
1049 and object_type='MV';
1050
1051
1052
1053 l_last_refresh_date BIS_OBJ_PROPERTIES.LAST_REFRESH_DATE%TYPE;
1054 l_obj_name BIS_OBJ_PROPERTIES.OBJECT_NAME%TYPE;
1055 l_obj_type BIS_OBJ_PROPERTIES.OBJECT_TYPE%TYPE;
1056 l_obj_owner BIS_OBJ_PROPERTIES.OBJECT_OWNER%TYPE;
1057 l_request_id NUMBER;
1058 l_obj_has_program varchar2(1);
1059 l_top_obj_name BIS_OBJ_PROPERTIES.OBJECT_NAME%TYPE;
1060 l_top_obj_type BIS_OBJ_PROPERTIES.OBJECT_TYPE%TYPE;
1061 l_top_obj_owner BIS_OBJ_PROPERTIES.OBJECT_OWNER%TYPE;
1062 l_sql varchar2(2000);
1063 l_request_id_this number;
1064
1065 begin
1066
1067 l_request_id_this:=fnd_global.CONC_REQUEST_ID;
1068 BIS_COLLECTION_UTILITIES.put_line('root request id ' || p_request_id);
1069 BIS_COLLECTION_UTILITIES.put_line('request id of this program ' || l_request_id_this);
1070 l_request_id := p_request_id;
1071 if (l_request_id is null) then
1072 l_request_id := FND_GLOBAL.CONC_PRIORITY_REQUEST;
1073 BIS_COLLECTION_UTILITIES.put_line('FND_GLOBAL.CONC_PRIORITY_REQUEST: ' || l_request_id);
1074 end if;
1075
1076
1077 ---preparing the temp table
1078
1079 for l_direct_obj_rec in c_obj_direct_in_set(l_request_id) loop
1080 l_top_obj_name:=l_direct_obj_rec.object_name;
1081 l_top_obj_type:=l_direct_obj_rec.object_type;
1082 l_top_obj_owner:=l_direct_obj_rec.object_owner;
1083 if l_direct_obj_rec.object_type='PAGE' then
1084 open c_page_name(l_direct_obj_rec.object_name) ;
1085 fetch c_page_name into l_top_obj_name;
1086 close c_page_name;
1087 end if;
1088
1089 ---added for bug 4532066
1090 if BIS_IMPL_OPT_PKG.get_impl_flag(l_top_obj_name,l_top_obj_type)='N' then
1091 BIS_COLLECTION_UTILITIES.put_line(l_top_obj_type||' '||l_top_obj_name||' is not implemented. Not to update its date.');
1092
1093 else
1094
1095 l_obj_has_program:='N';
1096 open c_obj_has_program(l_top_obj_type, l_top_obj_name);
1097 fetch c_obj_has_program into l_obj_has_program;
1098 close c_obj_has_program;
1099
1100 l_sql:='insert into BIS_OBJ_SET_TEMP(request_id,'||
1101 'object_name,'||
1102 'object_type,'||
1103 'object_owner,'||
1104 'has_program) '||
1105 'values (:1,:2,:3,:4,:5)';
1106 execute immediate l_sql using l_request_id_this, l_top_obj_name,l_top_obj_type,l_top_obj_owner,l_obj_has_program;
1107
1108 For l_indirect_obj_rec in C_OBJ_TO_BE_UPDATED(l_top_obj_type,l_top_obj_name) loop
1109 l_obj_has_program:='N';
1110 open c_obj_has_program(l_indirect_obj_rec.object_type, l_indirect_obj_rec.object_name);
1111 fetch c_obj_has_program into l_obj_has_program;
1112 close c_obj_has_program;
1113 execute immediate l_sql using l_request_id_this,l_indirect_obj_rec.object_name,l_indirect_obj_rec.object_type,l_indirect_obj_rec.object_owner,l_obj_has_program;
1114 end loop;
1115 end if;-----implementation_flag='N'
1116 end loop;
1117 commit;
1118
1119 BIS_COLLECTION_UTILITIES.put_line(' ');
1120 BIS_COLLECTION_UTILITIES.put_line('Please be informed that if an object has date ''01-01-1900'',');
1121 BIS_COLLECTION_UTILITIES.put_line('it means that the refresh program for this object failed or has not been run.');
1122 BIS_COLLECTION_UTILITIES.put_line(' ');
1123 BIS_COLLECTION_UTILITIES.put_line('=====Start updating dates for MVs or objects linked to programs===');
1124
1125
1126 open c_obj_with_program(l_request_id_this);
1127 loop
1128 fetch c_obj_with_program into l_obj_type, l_obj_name;
1129 exit when c_obj_with_program%NOTFOUND;
1130
1131 ---added for bug 4532066
1132 if BIS_IMPL_OPT_PKG.get_impl_flag(l_obj_name,l_obj_type)='N' then
1133 BIS_COLLECTION_UTILITIES.put_line(l_obj_type||' '||l_obj_name||' is not implemented. Not to update its date.');
1134 else
1135
1136
1137 ------Added for bug 4451368
1138 -----Handle the corner case: In case of data corruption, not able to find objective
1139 -----corresponding to the auto-gen report, then we should set the date to null for the report
1140 -----so that it doesn't affect the page date
1141 if l_obj_type='REPORT'
1142 and has_loader_sum_prog(l_obj_name)='Y'
1143 and (bis_create_requestset.get_indicator_auto_gen(l_obj_name) is null
1144 or bis_create_requestset.get_report_type(l_obj_name)<>'BSCREPORT') then
1145 l_last_refresh_date:=null;
1146
1147 else
1148 l_last_refresh_date:=get_obj_refresh_date_old(l_obj_type,null,l_obj_name);
1149
1150 --------the following logic is added for bug 4257955
1151 -----(0)If the request set has never been run, the object will have last refresh date as null
1152 -----(1)After the request set has been run, if the object is not mv and no program being defined
1153 --------then the last refresh date for this object is still null
1154 -----(2)After the request set has been run, if the object is MV or has program defined
1155 -------but not refreshed successfully (or the program has not been run), then it has date set to '01-01-1900'
1156 ------- The get last refresh date API will return 'Date not available' in this case
1157 ------(3)Normal case: After the request set has been run, and the object has been refreshed
1158 --------successfully, then we set the last refresh date to the date got from FND table
1159 if l_last_refresh_date is null then
1160 l_last_refresh_date:=to_date('01-01-1900','DD-MM-YYYY');
1161 end if;
1162 ------end bug 4257955
1163
1164 end if; ---end if for bug 4451368
1165
1166 bis_impl_dev_pkg.update_obj_last_refresh_date(l_obj_type,l_obj_name, l_last_refresh_date);
1167 BIS_COLLECTION_UTILITIES.put_line(' '||l_obj_type ||'.'||l_obj_name ||': ' || to_char(l_last_refresh_date,'DD-MON-YYYY'));
1168 end if;---implementation_flag ='N'
1169 end loop;
1170 close c_obj_with_program;
1171 commit;
1172 BIS_COLLECTION_UTILITIES.put_line(' ');
1173
1174
1175 update_page_portlet_date(l_request_id);
1176
1177 exception
1178 when others then
1179 if C_OBJ_TO_BE_UPDATED%isopen then
1180 close C_OBJ_TO_BE_UPDATED;
1181 end if;
1182 commit;
1183 raise;
1184 end;
1185
1186 --Enh#4289567; API to find out if a request set is running for a Dashboard/Report
1187 FUNCTION request_set_running(p_obj_type IN VARCHAR2, p_obj_name IN VARCHAR2, start_time OUT NOCOPY VARCHAR2)
1188 RETURN VARCHAR2 IS
1189 l_return_flag VARCHAR2(1);
1190 l_start_time DATE;
1191 CURSOR c_dashboards_rs IS
1192 SELECT 'Y', START_DATE
1193 FROM BIS_RS_RUN_HISTORY HISTORY, BIS_REQUEST_SET_OBJECTS OBJECTS, FND_CONCURRENT_REQUESTS FND
1194 WHERE OBJECTS.object_name = p_obj_name
1195 AND OBJECTS.object_type = p_obj_type
1196 AND BIS_BIA_RSG_PSTATE.get_refresh_mode(OBJECTS.REQUEST_SET_NAME) <> 'ANAL'
1197 AND OBJECTS.request_set_name = HISTORY.request_set_name
1198 AND HISTORY.PHASE_CODE = 'R'
1199 AND HISTORY.REQUEST_ID = FND.REQUEST_ID
1200 AND FND.STATUS_CODE <> 'X'
1201 ORDER BY START_DATE;
1202
1203 CURSOR c_reports_rs IS
1204 SELECT 'Y', START_DATE
1205 FROM (
1206 SELECT request_set_name --request set for dashboard that has this report
1207 FROM bis_request_set_objects
1208 WHERE object_name IN (
1209 SELECT DISTINCT obj.OBJECT_NAME
1210 FROM bis_obj_dependency obj
1211 WHERE object_type IN ('PAGE') AND enabled_flag='Y'
1212 START WITH obj.depend_object_type IN ('REPORT','PORTLET')
1213 AND obj.depend_object_name= p_obj_name
1214 CONNECT BY PRIOR obj.OBJECT_NAME=obj.depend_object_name
1215 AND PRIOR obj.OBJECT_TYPE=obj.depend_object_TYPE
1216 )
1217 AND object_type ='PAGE'
1218 UNION -- request set for report directly
1219 SELECT request_set_name
1220 FROM bis_request_set_objects
1221 WHERE object_name = p_obj_name
1222 AND object_type IN ('REPORT','PORTLET')
1223 ) RS, BIS_RS_RUN_HISTORY HISTORY, FND_CONCURRENT_REQUESTS FND
1224 WHERE BIS_BIA_RSG_PSTATE.get_refresh_mode(RS.REQUEST_SET_NAME) <> 'ANAL'
1225 AND RS.REQUEST_SET_NAME = HISTORY.REQUEST_SET_NAME AND HISTORY.PHASE_CODE = 'R'
1226 AND HISTORY.REQUEST_ID = FND.REQUEST_ID AND FND.STATUS_CODE <> 'X'
1227 ORDER BY START_DATE;
1228 BEGIN
1229 l_return_flag := 'N';
1230 IF (p_obj_type = 'PAGE') THEN
1231 OPEN c_dashboards_rs;
1232 FETCH c_dashboards_rs INTO l_return_flag, l_start_time;
1233 CLOSE c_dashboards_rs;
1234 ELSE
1235 IF (p_obj_type IN ('REPORT','PORTLET')) THEN
1236 OPEN c_reports_rs;
1237 FETCH c_reports_rs INTO l_return_flag, l_start_time;
1238 CLOSE c_reports_rs;
1239 END IF;
1240 END IF;
1241 IF (l_start_time IS NOT NULL) THEN
1242 start_time := BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ(l_start_time); --rkumar:bug#5154379
1243 END IF;
1244 RETURN l_return_flag;
1245 END;
1246
1247
1248 function get_last_refreshdate_url(p_obj_type in varchar2,
1249 p_obj_owner in varchar2,
1250 p_obj_name in varchar2,
1251 p_url_flag in varchar2 default 'Y') return varchar2 is
1252
1253 begin
1254 return get_last_refreshdate_url(p_obj_type,p_obj_owner,p_obj_name,p_url_flag,'');
1255 end;
1256
1257 ---Bug 4257955. Modify the function so that it has the following behavior
1258 ---(1) It is the only public API that should be called to get last refreshed date for any object
1259 ---(2) It will return string with hyper link and/or html tags
1260 ------if p_url_flag is set to 'Y'; Otherwise, it will just return the String itself
1261 ---(4) The API will return null if the object has last refresh date as null in bis_obj_properties
1262 -------OR if the object doesn't exist in RSG at all
1263 ----(5) The API will return 'Date not available' if the object has last refresh date as '01-01-1900' in
1264 --------bis_obj_properties
1265 function get_last_refreshdate_url(p_obj_type in varchar2,
1266 p_obj_owner in varchar2,
1267 p_obj_name in varchar2,
1268 p_url_flag in varchar2 default 'Y',
1269 p_RF_Url in varchar2) return varchar2 is
1270
1271 l_function_html varchar2(240);
1272 l_function_parameters varchar2(2000);
1273 l_web_agent varchar2(240);
1274 l_message_text1 varchar2(240);
1275 l_module varchar2(300) := 'bis.GET_LAST_REFRESH_DATE_PROC.'||p_obj_type||'.'||p_obj_name;
1276 l_return_string varchar2(2000);
1277 l_last_refresh_date date;
1278 l_formatted_date varchar2(200);
1279 l_function_id number;
1280
1281 -- Enh#4289567
1282 l_rs_run_time VARCHAR2(20);
1283 l_current_stat_message VARCHAR2(240);
1284 l_current_status_gif VARCHAR2(100);
1285
1286 cursor c_form_function is
1287 select
1288 function_id,
1289 web_html_call,
1290 parameters
1291 from
1292 fnd_form_functions
1293 where function_name='BIS_BIA_RSG_PSTATE_REPORT';
1294
1295 begin
1296
1297 --moved the code out of if condition for enhancement 4638578
1298 --CODE FIX FOR 4653272, CHANGED form apps_web_agent to apps_jsp_agent
1299 l_web_agent:=fnd_profile.value('APPS_JSP_AGENT');
1300 log(l_module, 'applications web agent:'||l_web_agent);
1301 log(l_module, 'getting the refresh details');
1302 open c_form_function;
1303 fetch c_form_function into l_function_id,l_function_html,l_function_parameters;
1304 close c_form_function;
1305 log(l_module, 'form function html call:'||l_function_id);
1306 log(l_module, 'form function html call:'||l_function_html);
1307 log(l_module, 'form function parameters:'||l_function_parameters);
1308
1309 l_last_refresh_date:=get_last_refreshdate(p_obj_type,p_obj_owner,p_obj_name);
1310
1311 if p_obj_type='PAGE' then
1312 if l_last_refresh_date is not null and l_last_refresh_date<>to_date('01-01-1900','DD-MM-YYYY') then
1313 --bug 4314736
1314 --l_formatted_date:=to_char(l_last_refresh_date,fnd_profile.value_specific('ICX_DATE_FORMAT_MASK'));
1315 l_formatted_date:= BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ(l_last_refresh_date);
1316
1317 l_message_text1:=fnd_message.get_string('BIS','BIS_BIA_PMV_RFH_DATE_API_MSG');
1318 log(l_module, 'message text:'||l_message_text1);
1319 if p_url_flag='Y' then
1320 --CODE FIX FOR 4653272, replaced ? with &
1321 -- CODE FIX FOR 4710006, added call to icx_sec.CreateRFURL, to convert to MAC compliant
1322 -- RF.jsp url
1323 l_return_string:='<span class="OraTipText">'||l_message_text1||' '||'<A HREF="'||
1324 /*icx_sec.CreateRFURL(p_function_id => l_function_id
1325 , p_session_id => fnd_global.session_id
1326 , p_parameters => l_function_parameters||
1327 '&fromLastUpdateDate=Y&'||'pParameters=pParamIds'||'@'||'Y'||'~'||'DBI_REQUEST_SET'||'^'||'DBI_REQUEST_SET'||
1328 '@'||p_obj_name||'~'||'DBI_CONTENT_TYPE'||'^'||'DBI_CONTENT_TYPE@PAGE'
1329 , p_application_id =>fnd_global.resp_appl_id
1330 , p_responsibility_id => fnd_global.resp_id
1331 , p_security_group_id => icx_sec.g_security_group_id) */
1332 p_RF_Url||'" class="OraLinkText">'||l_formatted_date||'</A>'||'</span>';
1333 else
1334 l_return_string:=l_message_text1||' '||l_formatted_date;
1335 end if;
1336 log(l_module, 'got details url: '||l_return_string);
1337 else if l_last_refresh_date=to_date('01-01-1900','DD-MM-YYYY') then
1338 if p_url_flag='Y' then
1339 l_return_string:='<span class="OraTipText">'||fnd_message.get_string('BIS','BIS_PMV_LAST_UPDATE_ERR')||'</span>';
1340 else
1341 l_return_string:=fnd_message.get_string('BIS','BIS_PMV_LAST_UPDATE_ERR');
1342 end if;
1343 else
1344 l_return_string:=null;
1345 end if;
1346 end if;
1347 end if;
1348 if p_obj_type in ('REPORT','PORTLET','TABLE','VIEW','MV') then
1349 if l_last_refresh_date is not null and l_last_refresh_date<>to_date('01-01-1900','DD-MM-YYYY') then
1350 l_message_text1:=fnd_message.get_string('BIS','BIS_BIA_PMV_RFH_DATE_API_MSG');
1351 --bug 4314736
1352 --l_formatted_date:=to_char(l_last_refresh_date,fnd_profile.value_specific('ICX_DATE_FORMAT_MASK'));
1353 l_formatted_date:= BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ(l_last_refresh_date);
1354
1355 if p_url_flag='Y' then
1356 l_return_string:='<span class="OraTipText">'||l_message_text1||' '||'<A HREF="'||
1357 /* icx_sec.CreateRFURL(p_function_id => l_function_id
1358 , p_session_id => fnd_global.session_id
1359 , p_parameters => l_function_parameters||
1360 '&fromLastUpdateDate=Y&'||'pParameters=pParamIds'||'@'||'Y'||'~'||'DBI_REQUEST_SET'||'^'||'DBI_REQUEST_SET'||
1361 '@'||p_obj_name||'~'||'DBI_CONTENT_TYPE'||'^'||'DBI_CONTENT_TYPE@REPORT'
1362 , p_application_id =>fnd_global.resp_appl_id
1363 , p_responsibility_id => fnd_global.resp_id
1364 , p_security_group_id => icx_sec.g_security_group_id) */
1365 p_RF_Url||'" class="OraLinkText">'||l_formatted_date||'</A>'||'</span>';
1366 --rkumar:bug#5161136
1367 if p_obj_name in ('BIS_BIA_RSG_TABLESPACE_PGE', 'BIS_BIA_RSG_SETS_DET_PGE', 'BIS_BIA_RSG_SETS_LVL_PGE',
1368 'BIS_BIA_RSG_SPACE_DET_PGE','BIS_BIA_RSG_SUB_REQS_PGE','BIS_BIA_RSG_REQ_DETAILS_PGE') then
1369 l_return_string:=l_message_text1||' '||l_formatted_date;
1370 end if;
1371 else
1372 l_return_string:=l_message_text1||' '||l_formatted_date;
1373 end if;
1374 log(l_module, 'got details url: '||l_return_string);
1375 else if l_last_refresh_date=to_date('01-01-1900','DD-MM-YYYY') then
1376 if p_url_flag='Y' then
1377 l_return_string:='<span class="OraTipText">'||fnd_message.get_string('BIS','BIS_PMV_LAST_UPDATE_ERR')||'</span>';
1378 else
1379 l_return_string:=fnd_message.get_string('BIS','BIS_PMV_LAST_UPDATE_ERR');
1380 end if;
1381 else
1382 l_return_string:=null;
1383 end if;
1384 end if;
1385 end if;
1386
1387 -- Enh#4289567 :: to display Current Status Icon with Data Last Update Date
1388 IF (p_url_flag='Y' AND p_obj_type IN ('REPORT','PAGE','PORTLET') AND request_set_running(p_obj_type, p_obj_name, l_rs_run_time) = 'Y') THEN
1389 l_current_stat_message := FND_MESSAGE.get_string('BIS','BIS_RSG_RS_CURRENT_STATUS');
1390 l_current_status_gif := '/OA_MEDIA/bispro16.gif';
1391 l_return_string := '<IMG alt="'||l_current_stat_message|| l_rs_run_time ||
1392 '" title="'||l_current_stat_message|| l_rs_run_time ||
1393 '"src="'||l_current_status_gif||'" >' || l_return_string;
1394 END IF;
1395
1396 return l_return_string;
1397 exception
1398 when others then
1399 raise;
1400
1401 end;
1402
1403 END BIS_SUBMIT_REQUESTSET;
[Home] [Help]