1 package body BIS_CREATE_REQUESTSET AS
2 /*$Header: BISCRSTB.pls 120.16 2006/09/07 14:30:19 aguwalan ship $*/
3
4 g_set_application varchar2(30);
5 g_fnd_stats varchar2(30);
6 g_fnd_stats_app varchar2(30);
7 g_parameter_default_type varchar2(30);
8 g_create_snpl varchar2(30);
9 g_create_snpl_app varchar2(30);
10 g_reset_flag varchar2(30);
11 g_reset_flag_app varchar2(30);
12 g_start_stage number ;
13 -- g_set_all_name varchar2(30);
14 -- g_set_all_longname varchar2(240);
15 g_current_user_id NUMBER := FND_GLOBAL.User_id;
16 g_current_login_id NUMBER := FND_GLOBAL.Login_id;
17 g_req_monitoring_err EXCEPTION;
18
19 g_bsc_loader_ind_program varchar2(30):='BSC_REFRESH_SUMMARY_IND';
20 g_bsc_loader_dim_program varchar2(30):='BSC_REFRESH_DIM_IND';
21 g_bsc_loader_del_program varchar2(30):='BSC_DELETE_DATA_IND';
22
23 g_bsc_auto_gen_exist varchar2(1);
24
25 -- FOR PING
26 TYPE T_PING_REC IS RECORD (
27 object_owner bis_obj_dependency.object_owner%TYPE,
28 object_type bis_obj_dependency.object_type%TYPE,
29 OBJECT_NAME bis_obj_dependency.OBJECT_NAME%TYPE,
30 HAS_DATA VARCHAR2(10));
31 TYPE T_PING_TABLE IS TABLE OF T_PING_REC;
32
33 g_ping_table T_PING_TABLE;
34
35 TYPE object_rec is record(
36 object_type bis_obj_dependency.object_type%TYPE,
37 OBJECT_NAME bis_obj_dependency.OBJECT_NAME%TYPE
38 );
39 TYPE object_table is table of object_rec;
40
41 g_apps_schema_name varchar2(30);
42 g_stage_prompt varchar2(30);
43
44
45 procedure log(MODULE IN VARCHAR2,
46 MESSAGE IN VARCHAR2) IS
47 begin
48 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
49 FND_LOG.string(FND_LOG.LEVEL_ERROR, module, message);
50 END IF;
51 end;
52
53 procedure logmsg(p_text in varchar2) is
54 begin
55 -- dbms_output.put_Line(p_text);
56 null;
57 end;
58
59 ---added for bug 4610116
60 function get_impl_flag_temp (p_set_name varchar2,
61 p_set_app varchar2,
62 p_top_object_type varchar2,
63 p_top_object_name varchar2,
64 p_object_type varchar2,
65 p_object_name varchar2) return varchar2 is
66 l_temp_impl_flag varchar2(1);
67 cursor c_impl_flag is
68 select 'Y'
69 from dual
70 where exists
71 (select 'Y'
72 from BIS_BIA_RSG_IMPL_FLAG_TEMP
73 where set_name=p_set_name
74 and set_app=p_set_app
75 and top_object_type=p_top_object_type
76 and top_object_name=p_top_object_name
77 and object_type=p_object_type
78 and object_name=p_object_name
79 and object_impl_flag='Y');
80
81 begin
82 l_temp_impl_flag:='N';
83 open c_impl_flag;
84 fetch c_impl_flag into l_temp_impl_flag;
85 if c_impl_flag%notfound then
86 l_temp_impl_flag:='N';
87 end if;
88 close c_impl_flag;
89 return l_temp_impl_flag;
90 exception
91 when others then
92 raise;
93 end;
94
95 function is_mvlog_mgt_enabled return varchar2 is
96 begin
97 return fnd_profile.value('BIS_BIA_MVLOG_ENABLE');
98 end;
99
100 FUNCTION duration(
101 p_duration number) return VARCHAR2 IS
102 BEGIN
103 return(to_char(floor(p_duration)) ||' Days '||
104 to_char(mod(floor(p_duration*24), 24))||':'||
105 to_char(mod(floor(p_duration*24*60), 60))||':'||
106 to_char(mod(floor(p_duration*24*60*60), 60)));
107 END duration;
108
109 procedure delete_set_all(p_setname in varchar2,p_setlongname in varchar2,p_setapp in varchar2) is
110 l_group_name varchar2(30);
111 l_group_app varchar2(30);
112
113 cursor c_setname is
114 select
115 REQUEST_SET_NAME
116 from
117 fnd_request_sets_vl a,
118 fnd_application b
119 where a.USER_REQUEST_SET_NAME=p_setlongname
120 and a.application_id=b.application_id
121 and b.application_short_name=p_setapp;
122
123 l_setname varchar2(30);
124
125 begin
126 l_group_name:='DBI Requests and Reports';
127 l_group_app:='BIS';
128 --g_set_application:=fnd_global.application_short_name;
129 --- g_set_application:='BIS';
130 ----Based on the demo meeting on August 28, 2002
131 ----Need to add the set to DBI requests group attached to Business Intelligence Administrator resp
132 if set_in_group(p_setname,p_setapp,l_group_name,l_group_app)='Y' then
133 fnd_set.remove_set_from_group(
134 request_set=>upper(p_setname),
135 set_application=>p_setapp,
136 request_group=>l_group_name,
137 group_application=>l_group_app
138 );
139 commit;
140 end if;
141 -----get the set name from set longname
142 ----this is to avoid the bug in UI.
143 ----If the user types in set name and longname instead of using LOV
144 ----The set name and longname may not in synyc with the existing request set
145 open c_setname;
146 fetch c_setname into l_setname;
147 close c_setname;
148
149 if l_setname is not null then
150 fnd_set.delete_set(upper(l_setname),p_setapp);
151 /* changes for 'view request set history': delete from
152 bis_request_set_options and bis_request_set_objects if the request
153 set already exists in these tables. */
154 delete_rs_objects(upper(l_setname), p_setapp);
155 delete_rs_option(upper(l_setname), p_setapp);
156 else
157 fnd_set.delete_set(upper(p_setname),p_setapp);
158 /* changes for 'view request set history': delete from
159 bis_request_set_options and bis_request_set_objects if the request
160 set already exists in these tables. */
161 delete_rs_objects(upper(p_setname), p_setapp);
162 delete_rs_option(upper(p_setname), p_setapp);
163 end if;
164 commit;
165 end delete_set_all;
166
167
168 function check_bsc_auto_gen return varchar2 is
169 cursor c_exist is
170 select 'Y'
171 from dual
172 where exists
173 (select 'Y'
174 from user_objects
175 where object_name='BSC_DBGEN_UTILS' and object_type='PACKAGE' );
176
177 l_dummy varchar2(1);
178
179 begin
180 l_dummy:='N';
181 open c_exist;
182 fetch c_exist into l_dummy;
183 close c_exist;
184 return l_dummy;
185 exception
186 when others then
187 raise;
188 end;
189
190
191 procedure create_set_all(p_setname in varchar2,p_setlongname in varchar2,p_setapp in varchar2) is
192 l_group_name varchar2(30);
193 l_group_app varchar2(30);
194
195 cursor c_setname is
196 select
197 REQUEST_SET_NAME
198 from
199 fnd_request_sets_vl a,
200 fnd_application b
201 where a.USER_REQUEST_SET_NAME=p_setlongname
202 and a.application_id=b.application_id
203 and b.application_short_name=p_setapp;
204
205
206
207
208 l_setname varchar2(30);
209 l_temp_table_owner varchar2(30);
210
211 begin
212 ---this global variable is added for fixing bug 3503046
213 g_stage_prompt:=fnd_message.get_string('BIS','BIS_BIA_RSG_STAGE_PROMPT');
214
215 g_bsc_auto_gen_exist:=check_bsc_auto_gen;
216
217 l_group_name:='DBI Requests and Reports';
218 l_group_app:='BIS';
219
220
221 --g_set_application:=fnd_global.application_short_name;
222 --- g_set_application:='BIS';
223 ----Based on the demo meeting on August 28, 2002
224 ----Need to add the set to DBI requests group attached to Business Intelligence Administrator resp
225 if set_in_group(p_setname,p_setapp,l_group_name,l_group_app)='Y' then
226 fnd_set.remove_set_from_group(
227 request_set=>upper(p_setname),
228 set_application=>p_setapp,
229 request_group=>l_group_name,
230 group_application=>l_group_app
231 );
232 commit;
233 end if;
234
235
236 -----get the set name from set longname
237 ----this is to avoid the bug in UI.
238 ----If the user types in set name and longname instead of using LOV
239 ----The set name and longname may not in synyc with the existing request set
240 open c_setname;
241 fetch c_setname into l_setname;
242 close c_setname;
243
244 if l_setname is not null then
245 fnd_set.delete_set(upper(l_setname),p_setapp);
246 /* changes for 'view request set history': delete from
247 bis_request_set_options and bis_request_set_objects if the request
248 set already exists in these tables. */
249 delete_rs_objects(upper(l_setname), p_setapp);
250 delete_rs_option(upper(l_setname), p_setapp);
251
252 else
253 fnd_set.delete_set(upper(p_setname),p_setapp);
254 /* changes for 'view request set history': delete from
255 bis_request_set_options and bis_request_set_objects if the request
256 set already exists in these tables. */
257 delete_rs_objects(upper(p_setname), p_setapp);
258 delete_rs_option(upper(p_setname), p_setapp);
259 end if;
260
261 commit;
262
263
264
265 fnd_set.create_set
266 (name=>p_setlongname,
267 short_name=>upper(p_setname),
268 application=>p_setapp,
269 description=>p_setlongname||'(created by request set generator)',
270 owner=>null,
271 start_date=>sysdate,
272 end_date=>null,
273 print_together=>'N',
274 incompatibilities_allowed=>'N',
275 LANGUAGE_CODE=>'US');
276 commit;
277
278 fnd_set.add_set_to_group(
279 request_set=>upper(p_setname),
280 set_application=>p_setapp,
281 request_group=>'DBI Requests and Reports',
282 group_application=> 'BIS'
283 );
284 commit;
285
286 ---clean up global temp table whenever a request set is to be created
287 ---bug 4724296
288 l_temp_table_owner:=bis_create_requestset.get_object_owner('BIS_BIA_RSG_IMPL_FLAG_TEMP','TABLE');
289 execute immediate 'truncate table '||l_temp_table_owner||'.BIS_BIA_RSG_IMPL_FLAG_TEMP';
290 l_temp_table_owner:=bis_create_requestset.get_object_owner('BIS_BIA_RSG_STAGE_OBJECTS','TABLE');
291 execute immediate 'truncate table '||l_temp_table_owner||'.BIS_BIA_RSG_STAGE_OBJECTS';
292
293 exception
294 when others then
295
296 raise;
297 end;
298
299
300 function object_not_linked_to_reports(p_top_object_name varchar2,
301 p_top_object_type varchar2,
302 p_object_name varchar2,
303 p_object_type varchar2) return varchar2 is
304 l_dummy varchar2(1);
305 cursor c_obj_not_linked_to_reports is
306 select 'Y'
307 from
308 ( select distinct
309 obj.depend_OBJECT_NAME obj_name,
310 obj.depend_object_type obj_type
311 from
312 ( select object_name,
313 object_type,
314 depend_object_name,
315 depend_object_type,
316 enabled_flag
317 from
318 bis_obj_dependency
319 where enabled_flag='Y'
320 and depend_object_type<>'REPORT'
321 and object_type<>'REPORT'
322 ) obj
323 start with obj.object_type =p_top_object_type
324 and obj.object_name=p_top_object_name
325 connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
326 and prior obj.DEPEND_OBJECT_TYPE=obj.object_type ) depend_objects
327 where depend_objects.obj_type=p_object_type
328 and depend_objects.obj_name=p_object_name;
329
330 begin
331 l_dummy:='N';
332 open c_obj_not_linked_to_reports;
333 fetch c_obj_not_linked_to_reports into l_dummy;
334 if c_obj_not_linked_to_reports%notfound then
335 l_dummy:='N';
336 end if;
337 close c_obj_not_linked_to_reports;
338 return l_dummy;
339 exception
340 when others then
341 raise;
342 end;
343
344
345 procedure process_impl_temp_table(p_set_name varchar2,
346 p_set_app varchar2,
347 p_top_object_type varchar2,
348 p_top_object_name varchar2) is
349 cursor c_unimpl_reports is
350 select object_name
351 from BIS_BIA_RSG_IMPL_FLAG_TEMP
352 where set_name=p_set_name
353 and set_app=p_set_app
354 and top_object_type=p_top_object_type
355 and top_object_name=p_top_object_name
356 and object_type='REPORT'
357 and object_impl_flag='N';
358
359 cursor c_impl_reports is
360 select object_name
361 from BIS_BIA_RSG_IMPL_FLAG_TEMP
362 where set_name=p_set_name
363 and set_app=p_set_app
364 and top_object_type=p_top_object_type
365 and top_object_name=p_top_object_name
366 and object_type='REPORT'
367 and object_impl_flag='Y';
368
369 cursor c_obj_under_reports (p_report_name varchar2) is
370 select distinct dep.depend_object_name, dep.depend_object_type
371 from
372 ( select object_name,
373 object_type,
374 depend_object_name,
375 depend_object_type
376 from bis_obj_dependency
377 where enabled_flag='Y') dep
378 where dep.depend_object_type<>'REPORT'
379 start with dep.object_type = 'REPORT'
380 and dep.object_name=p_report_name
381 connect by prior dep.depend_object_name = dep.object_name
382 and prior dep.depend_object_type = dep.object_type;
383
384 l_unimpl_report_rec c_unimpl_reports%rowtype;
385 l_impl_report_rec c_impl_reports%rowtype;
386 l_obj_rec c_obj_under_reports%rowtype;
387
388 begin
389 for l_unimpl_report_rec in c_unimpl_reports loop
390 for l_obj_rec in c_obj_under_reports(l_unimpl_report_rec.object_name) loop
391 ---added for bug 4664831
392 ---we should not set the impl flag to 'N' in case
393 ---the object is also linked to region (KPI list for example)
394 ---without report in between
395 if object_not_linked_to_reports(p_top_object_name,
396 p_top_object_type,
397 l_obj_rec.depend_object_name,
398 l_obj_rec.depend_object_type)='N' then
399 update BIS_BIA_RSG_IMPL_FLAG_TEMP
400 set object_impl_flag='N'
401 where set_name=p_set_name
402 and set_app=p_set_app
403 and top_object_type=p_top_object_type
404 and top_object_name=p_top_object_name
405 and object_type=l_obj_rec.depend_object_type
406 and object_name=l_obj_rec.depend_object_name;
407 end if;---end if object_not_linked_to_reports
408 end loop;
409 end loop;
410
411
412 for l_impl_report_rec in c_impl_reports loop
413 for l_obj_rec in c_obj_under_reports(l_impl_report_rec.object_name) loop
414 update BIS_BIA_RSG_IMPL_FLAG_TEMP
415 set object_impl_flag='Y'
416 where set_name=p_set_name
417 and set_app=p_set_app
418 and top_object_type=p_top_object_type
419 and top_object_name=p_top_object_name
420 and object_type=l_obj_rec.depend_object_type
421 and object_name=l_obj_rec.depend_object_name;
422 end loop;
423 end loop;
424 commit;
425 exception
426 when others then
427 raise;
428 end;
429
430 procedure insert_stage_objects(p_set_name in varchar2,
431 p_set_app in varchar2,
432 p_object_name in varchar2,
433 p_object_type in varchar2) is
434
435 l_sql_stmt varchar2(2000);
436
437 cursor c_objects_per_page is
438 select depend_objects.obj_type object_type,depend_objects.obj_name object_name
439 from
440 ( select distinct
441 obj.depend_OBJECT_NAME obj_name,
442 obj.depend_object_type obj_type,
443 obj.depend_object_owner obj_owner
444 from
445 ( select object_name,
446 object_type,
447 object_owner,
448 depend_object_name,
449 depend_object_type,
450 depend_object_owner,
454 where enabled_flag='Y' ) obj
451 enabled_flag
452 from
453 bis_obj_dependency
455 start with obj.object_type =p_object_type
456 and obj.object_name = p_object_name
457 connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
458 and prior obj.DEPEND_OBJECT_TYPE=obj.object_type ) depend_objects
459 union---the object itself could have program so we need union here
460 select object_type,object_name
461 from bis_obj_properties
462 where object_type= p_object_type
463 and object_name=p_object_name;
464
465
466 l_obj_rec c_objects_per_page%rowtype;
467 l_stage varchar2(30);
468 l_impl_flag varchar2(1);
469
470
471 begin
472 ---added for bug 4610116
473 for l_obj_rec in c_objects_per_page loop
474 l_impl_flag:= BIS_IMPL_OPT_PKG.get_impl_flag(l_obj_rec.object_name,l_obj_rec.object_type);
475 l_sql_stmt:='insert into BIS_BIA_RSG_IMPL_FLAG_TEMP(set_name,set_app,top_object_type,top_object_name,object_type,object_name,object_impl_flag)
476 values (:1,:2,:3,:4,:5,:6,:7)';
477 EXECUTE IMMEDIATE l_sql_stmt USING p_set_name,p_set_app,p_object_type,p_object_name,l_obj_rec.object_type ,l_obj_rec.object_name,l_impl_flag;
478 end loop;
479
480 ---Process BIS_BIA_RSG_IMPL_FLAG_TEMP.
481 ---Reset impl flag for objects within a page
482 ---The purpose is to exclude objects under an unimplemented report from a request set
483 if p_object_type='PAGE' then
484 process_impl_temp_table(p_set_name,p_set_app,p_object_type,p_object_name);
485 end if;
486
487 l_stage:=null;
488 for l_obj_rec in c_objects_per_page loop
489 ---added for bug 4532066
490 if get_impl_flag_temp(p_set_name,p_set_app,p_object_type,p_object_name,l_obj_rec.object_type,l_obj_rec.object_name)='Y' then
491 l_sql_stmt := 'insert into BIS_BIA_RSG_STAGE_OBJECTS(set_name,set_app,stage_name,object_type,object_name)
492 values (:1,:2,:3,:4,:5)';
493 EXECUTE IMMEDIATE l_sql_stmt USING p_set_name,p_set_app,l_stage,l_obj_rec.object_type ,l_obj_rec.object_name;
494 end if;
495 end loop;
496 commit;
497 exception
498 when others then
499 raise;
500 end;
501
502
503 function get_lookup_meaning(p_type in varchar2,
504 p_code in varchar2) return varchar2 is
505
506 l_meaning varchar2(80);
507 begin
508 select meaning into l_meaning
509 from fnd_common_lookups
510 where lookup_type=p_type
511 and lookup_code=p_code;
512 return l_meaning;
513 exception
514 when no_data_found then
515 return null;
516 when others then
517 raise;
518 end;
519
520
521 procedure get_stage_sequence(p_set_name in varchar2,
522 p_set_app in varchar2,
523 p_process_name in varchar2,
524 p_process_app in varchar2,
525 x_stage out NOCOPY varchar2,
526 x_sequence out NOCOPY number) is
527
528 cursor c_stage is
529 select
530 c.stage_name,
531 b.sequence
532 from
533 fnd_request_sets a,
534 fnd_request_set_programs b,
535 fnd_request_set_stages c,
536 fnd_concurrent_programs d,
537 fnd_application e,
538 fnd_application f
539 where a.request_set_id=b.request_set_id
540 and b.request_set_stage_id=c.request_set_stage_id
541 and a.request_set_id=c.request_set_id
542 and b.concurrent_program_id=d.concurrent_program_id
543 and b.program_application_id=d.application_id
544 and a.application_id=e.application_id
545 and d.application_id=f.application_id
546 and a.request_set_name=p_set_name
547 and d.concurrent_program_name=p_process_name
548 and e.application_short_name=p_set_app
549 and f.application_short_name=p_process_app
550 -- added to pickup indexes on FND_REQUEST_SET_STAGES and FND_REQUEST_SET_PROGRAMS
551 -- bug3143536
552 and c.set_application_id = a.application_id
553 and b.request_set_id = c.request_set_id
554 and a.application_id = b.set_application_id;
555
556 l_stage varchar2(30);
557 l_sequence number;
558 l_stage_rec c_stage%rowtype;
559 begin
560 l_stage:=null;
561 l_sequence:=null;
562 for l_stage_rec in c_stage loop
563 l_stage:=l_stage_rec.stage_name;
564 l_sequence:=l_stage_rec.sequence;
565 end loop;
566 x_stage:=l_stage;
567 x_sequence:=l_sequence;
568 exception
569 when others then
570 x_stage:=null;
571 x_sequence:=null;
572 raise;
573 end;
574
575
576 function get_max_prog_sequence(p_set_name in varchar2,p_set_app in varchar2, p_stage_name varchar2) return number is
577
578 cursor c_max_seq is
579 select
580 max(b.sequence) max_prog_seq
581 from
582 fnd_request_sets a,
583 fnd_request_set_programs b,
584 fnd_request_set_stages c,
585 fnd_application d
586 where a.request_set_id=b.request_set_id
587 and b.request_set_stage_id=c.request_set_stage_id
588 and a.request_set_id=c.request_set_id
589 and a.application_id=d.application_id
590 and d.application_short_name=p_set_app
591 and a.request_set_name=p_set_name
592 and c.stage_name=p_stage_name
593 -- added to pickup indexes on FND_REQUEST_SET_STAGES and FND_REQUEST_SET_PROGRAMS
594 -- bug3143536
595 and c.set_application_id = a.application_id
596 and b.request_set_id = c.request_set_id
600 begin
597 and a.application_id = b.set_application_id
598 group by c.stage_name;
599 l_max_seq number;
601 l_max_seq:=null;
602 open c_max_seq;
603 fetch c_max_seq into l_max_seq;
604 return l_max_seq;
605 exception
606 when others then
607 return null;
608 raise;
609 end;
610
611
612 procedure add_mv_to_set(
613 p_setname in varchar2,
614 p_set_application in varchar2,
615 p_level in number,
616 p_start_stage in number,
617 p_refresh_mode in varchar2,
618 p_object_name in varchar2,
619 p_max_level in number,
620 p_process_counter out nocopy integer)
621 IS
622 l_exist_stage varchar2(30);
623 l_exist_stage_number number;
624 l_exist_sequence number;
625 l_process_name varchar2(30);
626 l_process_app varchar2(30);
627 l_max_program_seq number;
628 l_level_stage varchar2(30);
629 begin
630
631 l_process_name := 'BIS_MV_REFRESH';
632 l_process_app := 'BIS';
633
634 --------dbms_output.put_Line('p_max_level:'||p_max_level);
635
636 -- ----dbms_output.put_Line('p_level:'||p_level);
637
638 l_level_stage:= 'Stage_'||to_char((p_max_level-p_level+1)*100+p_start_stage);
639
640 ----dbms_output.put_Line('MV stage by level: '||l_level_stage);
641
642 p_process_counter:=p_process_counter+1;
643
644
645
646 get_stats_stage_sequence(upper(p_setname),
647 p_set_application,
648 l_process_name,
649 p_object_name,
650 g_parameter_default_type,
651 l_exist_stage,
652 l_exist_sequence);
653
654 if l_exist_stage is null then
655 l_max_program_seq:=get_max_prog_sequence(upper(p_setname),
656 p_set_application,
657 l_level_stage);
658 if l_max_program_seq is null then
659 l_max_program_seq:=0;
660 end if;
661 begin
662
663 fnd_set.add_program(
664 program =>l_process_name ,
665 program_application=>l_process_app ,
666 request_set=>upper(p_setname) ,
667 set_application=>p_set_application ,
668 stage=>l_level_stage,
669 program_sequence=>l_max_program_seq+10,
670 critical=>'Y' ,
671 number_of_copies =>0,
672 save_output =>'Y',
673 style=>null,
674 printer=>null);
675
676 fnd_set.PROGRAM_PARAMETER(
677 PROGRAM=>l_process_name,
678 PROGRAM_APPLICATION=>l_process_app,
679 REQUEST_SET=>upper(p_setname),
680 SET_APPLICATION=>p_set_application,
681 STAGE=>l_level_stage,
682 PROGRAM_SEQUENCE=>l_max_program_seq+10,
683 PARAMETER=>'Refresh Mode',
684 DISPLAY=>'Y',
685 MODIFY=> 'Y' ,
686 SHARED_PARAMETER=>null ,
687 DEFAULT_TYPE=>'Constant',
688 DEFAULT_VALUE=>p_refresh_mode
689 );
690
691 fnd_set.PROGRAM_PARAMETER(
692 PROGRAM=>l_process_name,
693 PROGRAM_APPLICATION=>l_process_app,
694 REQUEST_SET=>upper(p_setname),
695 SET_APPLICATION=>p_set_application,
696 STAGE=>l_level_stage,
697 PROGRAM_SEQUENCE=>l_max_program_seq+10,
698 PARAMETER=>'Materialized View',
699 DISPLAY=>'Y',
700 MODIFY=> 'Y' ,
701 SHARED_PARAMETER=>null ,
702 DEFAULT_TYPE=>'Constant',
703 DEFAULT_VALUE=>p_object_name
704 );
705
706 commit;
707
708 exception
709 when others then
710 raise;
711 end;
712 else
713 l_exist_stage_number:=to_number(substr(l_exist_stage,7));
714 -----if a same process alreay defined in a set and the stage is later than the current process needed
715 ----then we need to remove this process and re-add it to the set on an earlier stage
716 -- if l_exist_stage_number>p_level*100+p_start_stage then
717 if l_exist_stage_number>(p_max_level-p_level+1)*100+p_start_stage then
718 begin
719 fnd_set.remove_program
720 (program=>l_process_name,
721 program_application=>l_process_app,
722 request_set=>upper(p_setname),
723 set_application=>p_set_application,
724 stage=>l_exist_stage,
725 program_sequence=>l_exist_sequence);
726 commit;
727 exception
728 when others then
729 raise;
730 end;
731 l_max_program_seq:=get_max_prog_sequence(upper(p_setname),
732 p_set_application,
733 l_level_stage);
734 if l_max_program_seq is null then
735 l_max_program_seq:=0;
736 end if;
737
738 begin
739
740 fnd_set.add_program(
741 program =>l_process_name ,
742 program_application=>l_process_app ,
743 request_set=>upper(p_setname) ,
747 critical=>'Y' ,
744 set_application=>p_set_application ,
745 stage=>l_level_stage,
746 program_sequence=>l_max_program_seq+10,
748 number_of_copies =>0,
749 save_output =>'Y',
750 style=>null,
751 printer=>null);
752
753 fnd_set.PROGRAM_PARAMETER(
754 PROGRAM=>l_process_name,
755 PROGRAM_APPLICATION=>l_process_app,
756 REQUEST_SET=>upper(p_setname),
757 SET_APPLICATION=>p_set_application,
758 STAGE=>l_level_stage,
759 PROGRAM_SEQUENCE=>l_max_program_seq+10,
760 PARAMETER=>'Refresh Mode',
761 DISPLAY=>'Y',
762 MODIFY=> 'Y' ,
763 SHARED_PARAMETER=>null ,
764 DEFAULT_TYPE=>'Constant',
765 DEFAULT_VALUE=>p_refresh_mode
766 );
767
768 fnd_set.PROGRAM_PARAMETER(
769 PROGRAM=>l_process_name,
770 PROGRAM_APPLICATION=>l_process_app,
771 REQUEST_SET=>upper(p_setname),
772 SET_APPLICATION=>p_set_application,
773 STAGE=>l_level_stage,
774 PROGRAM_SEQUENCE=>l_max_program_seq+10,
775 PARAMETER=>'Materialized View',
776 DISPLAY=>'Y',
777 MODIFY=> 'Y' ,
778 SHARED_PARAMETER=>null ,
779 DEFAULT_TYPE=>'Constant',
780 DEFAULT_VALUE=>p_object_name
781 );
782 commit;
783 exception
784 when others then
785 raise;
786 end;
787 end if; ---end if l_exist_stage_number>(l_max_leve..
788 end if; ---end if exist stage is null
789
790 end;
791
792
793
794
795
796
797 function object_has_program(p_object_type in varchar2,p_object_name in varchar2) return varchar2 is
798 l_dummy varchar2(1);
799
800 cursor c_obj_has_program is
801 select 'Y'
802 from dual
803 where exists
804 (select 1
805 from bis_obj_prog_linkages
806 where object_type=p_object_type
807 and object_name=p_object_name
808 and enabled_flag='Y');
809 begin
810 open c_obj_has_program;
811 fetch c_obj_has_program into l_dummy;
812 if c_obj_has_program%notfound then
813 l_dummy:='N';
814 end if;
815 close c_obj_has_program;
816 return l_dummy;
817 end;
818
819
820 function get_report_type(p_object_name in varchar2) return varchar2 is
821 l_sql varchar2(1000):='begin :1 :=BSC_DBGEN_UTILS.get_Objective_Type(:2); end;';
822 l_report_type varchar2(30);
823 begin
824 execute immediate l_sql using OUT l_report_type,IN p_object_name;
825 return l_report_type;
826 exception
827 when others then
828 raise;
829 end;
830
831
832 function get_indicator_auto_gen(p_object_name in varchar2) return number is
833 l_indicator number;
834 begin
835 execute immediate 'select distinct indicator from bsc_kpis_b where short_name=:1' into l_indicator using p_object_name;
836 return l_indicator;
837 exception
838 when no_data_found then
839 return null;
840 when others then
841 raise;
842 end;
843
844 procedure add_auto_gen_reports(p_setname in varchar2,
845 p_set_application in varchar2,
846 p_level in number,
847 p_max_level in number,
848 p_object_name in varchar2) is
849
850 l_exist_stage varchar2(30);
851 l_exist_stage_number number;
852 l_exist_sequence number;
853 l_process_name varchar2(30);
854 l_process_app varchar2(30);
855 l_max_program_seq number;
856 l_level_stage varchar2(30);
857 l_indicator number;
858 begin
859
860 --dbms_output.put_Line('beginning of add_auto_gemn');
861 l_process_name := g_bsc_loader_ind_program;
862 l_process_app := 'BSC';
863 l_indicator :=to_char(get_indicator_auto_gen(p_object_name));
864 if l_indicator is null then
865 return;
866 end if;
867
868 l_level_stage:= 'Stage_'||to_char((p_max_level-p_level+1)*100);
869
870
871 get_stats_stage_sequence(upper(p_setname),
872 p_set_application,
873 l_process_name,
874 l_indicator,
875 g_parameter_default_type,
876 l_exist_stage,
877 l_exist_sequence);
878
879
880 if l_exist_stage is null then
881 l_max_program_seq:=get_max_prog_sequence(upper(p_setname),
882 p_set_application,
883 l_level_stage);
884 if l_max_program_seq is null then
885 l_max_program_seq:=0;
886 end if;
887 begin
888 --dbms_output.put_Line('point1');
889 fnd_set.add_program(
890 program =>l_process_name ,
891 program_application=>l_process_app ,
892 request_set=>upper(p_setname) ,
893 set_application=>p_set_application ,
894 stage=>l_level_stage,
895 program_sequence=>l_max_program_seq+10,
896 critical=>'Y' ,
900 printer=>null);
897 number_of_copies =>0,
898 save_output =>'Y',
899 style=>null,
901 --dbms_output.put_Line('point2');
902 fnd_set.PROGRAM_PARAMETER(
903 PROGRAM=>l_process_name,
904 PROGRAM_APPLICATION=>l_process_app,
905 REQUEST_SET=>upper(p_setname),
906 SET_APPLICATION=>p_set_application,
907 STAGE=>l_level_stage,
908 PROGRAM_SEQUENCE=>l_max_program_seq+10,
909 PARAMETER=>'x_indicators',
910 DISPLAY=>'Y',
911 MODIFY=> 'Y' ,
912 SHARED_PARAMETER=>null ,
913 DEFAULT_TYPE=>'Constant',
914 DEFAULT_VALUE=>l_indicator
915 );
916 --dbms_output.put_Line('point3');
917
918 commit;
919
920 exception
921 when others then
922 raise;
923 end;
924 else
925 l_exist_stage_number:=to_number(substr(l_exist_stage,7));
926 -----if a same process alreay defined in a set and the stage is later than the current process needed
927 ----then we need to remove this process and re-add it to the set on an earlier stage
928 -- if l_exist_stage_number>p_level*100+p_start_stage then
929 if l_exist_stage_number>(p_max_level-p_level+1)*100 then
930 begin
931 fnd_set.remove_program
932 (program=>l_process_name,
933 program_application=>l_process_app,
934 request_set=>upper(p_setname),
935 set_application=>p_set_application,
936 stage=>l_exist_stage,
937 program_sequence=>l_exist_sequence);
938 commit;
939 exception
940 when others then
941 raise;
942 end;
943 l_max_program_seq:=get_max_prog_sequence(upper(p_setname),
944 p_set_application,
945 l_level_stage);
946 if l_max_program_seq is null then
947 l_max_program_seq:=0;
948 end if;
949
950 begin
951
952 fnd_set.add_program(
953 program =>l_process_name ,
954 program_application=>l_process_app ,
955 request_set=>upper(p_setname) ,
956 set_application=>p_set_application ,
957 stage=>l_level_stage,
958 program_sequence=>l_max_program_seq+10,
959 critical=>'Y' ,
960 number_of_copies =>0,
961 save_output =>'Y',
962 style=>null,
963 printer=>null);
964
965 --dbms_output.put_Line('point4');
966 fnd_set.PROGRAM_PARAMETER(
967 PROGRAM=>l_process_name,
968 PROGRAM_APPLICATION=>l_process_app,
969 REQUEST_SET=>upper(p_setname),
970 SET_APPLICATION=>p_set_application,
971 STAGE=>l_level_stage,
972 PROGRAM_SEQUENCE=>l_max_program_seq+10,
973 PARAMETER=>'x_indicators',
974 DISPLAY=>'Y',
975 MODIFY=> 'Y' ,
976 SHARED_PARAMETER=>null ,
977 DEFAULT_TYPE=>'Constant',
978 DEFAULT_VALUE=>l_indicator
979 );
980
981 --dbms_output.put_Line('point5');
982 commit;
983 exception
984 when others then
985 raise;
986 end;
987 end if; ---end if l_exist_stage_number>(l_max_leve..
988 end if; ---end if exist stage is null
989 --dbms_output.put_Line('end of add_auto_gen_report');
990 end;
991
992
993 ---------******this procedure replaces old procedure add_page_to_set
994 procedure add_any_object_to_set(p_object_name in varchar2,
995 p_object_type in varchar2,
996 p_setname in varchar2,
997 p_set_application in varchar2,
998 p_option in varchar2,
999 p_analyze_table in varchar2,
1000 p_refresh_mode in varchar2,
1001 p_force_full_refresh in varchar2) is
1002
1003
1004
1005 ---This cursor is modified in enhancement 3999465
1006 ----The report itself can be linked to a program
1007 ----so we have to add the union part
1008 cursor c_objects is
1009 select distinct
1010 depend_object_owner object_owner,
1011 depend_object_type object_type,
1012 depend_OBJECT_NAME object_name,
1013 level+1 mylevel
1014 from
1015 (select distinct
1016 a.depend_object_owner ,
1017 a.depend_object_type ,
1018 a.depend_object_name ,
1019 a.object_type ,
1020 a.object_owner ,
1021 a.object_name
1022 from
1023 bis_obj_dependency a
1024 where enabled_flag='Y') temp
1025 start with object_type =p_object_type
1026 and object_name = p_object_name
1027 connect by prior DEPEND_OBJECT_NAME=object_name
1028 and prior DEPEND_OBJECT_TYPE=object_type
1029 union
1030 select distinct
1031 object_owner,
1032 object_type,
1033 object_name,
1034 1 mylevel
1035 from
1036 bis_obj_dependency
1037 where enabled_flag='Y'
1041 select distinct
1038 and object_type =p_object_type
1039 and object_name = p_object_name
1040 union
1042 depend_object_owner object_owner,
1043 depend_object_type object_type,
1044 depend_object_name object_name,
1045 1 mylevel
1046 from
1047 bis_obj_dependency
1048 where enabled_flag='Y'
1049 and depend_object_type =p_object_type
1050 and depend_object_name = p_object_name
1051 union --added for bug 4648079 auto gen report with custom calendar
1052 select distinct
1053 object_owner,
1054 object_type,
1055 object_name,
1056 1 mylevel
1057 from bis_obj_prog_linkages
1058 where object_type=p_object_type
1059 and object_name=p_object_name
1060 order by mylevel desc;
1061
1062
1063 cursor c_max_level is
1064 select nvl(max(level),0)+1 from
1065 (select distinct
1066 a.depend_object_owner ,
1067 a.depend_object_type ,
1068 a.depend_object_name ,
1069 a.object_type ,
1070 a.object_owner ,
1071 a.object_name
1072 from
1073 bis_obj_dependency a
1074 where enabled_flag='Y') temp
1075 start with object_type=p_object_type
1076 and object_name = p_object_name
1077 connect by prior depend_object_name=object_name
1078 and prior depend_object_type=object_type;
1079
1080
1081 ----a refresh program with INIT_INCR type can be used in both initial loading request set and
1082 -----incremental request set
1083 ------For enhancement 4251030, exclude bsc loader program because it needs
1084 ------special logic for parameter being passed in
1085 ------We will handle it separately in procedure add_auto_gen_reports
1086 cursor c_process (p_objectname varchar2,p_objecttype varchar2,p_mode varchar2) is
1087 select distinct
1088 a.CONC_PROGRAM_NAME CONCURRENT_PROGRAM_NAME,
1089 a.CONC_APP_SHORT_NAME APPLICATION_SHORT_NAME
1090 from
1091 bis_obj_prog_linkages a,
1092 fnd_concurrent_programs b
1093 where a.object_name=p_objectname
1094 and a.object_type=p_objecttype
1095 and a.enabled_flag='Y'
1096 and a.CONC_PROGRAM_NAME<>g_bsc_loader_ind_program
1097 and (decode(nvl(a.refresh_mode,'INCR'),'INIT_INCR','INCR',nvl(a.refresh_mode,'INCR'))=p_mode
1098 or
1099 decode(nvl(a.refresh_mode,'INCR'),'INIT_INCR','INIT',nvl(a.refresh_mode,'INCR'))=p_mode)
1100 and a.CONC_PROGRAM_NAME=b.concurrent_program_name
1101 and a.CONC_APP_ID=b.application_id
1102 and b.ENABLED_FLAG='Y' ;
1103
1104
1105 cursor c_max_stage is
1106 select
1107 max(b.display_sequence)
1108 from
1109 fnd_request_sets a,
1110 fnd_request_set_stages b,
1111 fnd_application c
1112 where a.request_set_id=b.request_set_id
1113 and a.application_id=b.set_application_id
1114 and a.application_id=c.application_id
1115 and c.application_short_name=p_set_application
1116 and a.request_set_name=upper(p_setname);
1117
1118
1119 l_max_level number;
1120 l_max_stage number;
1121 l_objects_rec c_objects%rowtype;
1122 l_process_rec c_process%rowtype;
1123 l_process_name varchar2(30);
1124 l_process_app varchar2(30);
1125 l_exist_stage varchar2(30);
1126 l_exist_stage_number number;
1127 l_exist_sequence number;
1128 l_max_program_seq number;
1129 l_objectname bis_obj_dependency.object_name%type;
1130 l_level number;
1131 l_set_application varchar2(30);
1132 l_setname varchar2(30);
1133 l_process_counter integer;
1134 l_level_stage varchar2(30);
1135 l_mode varchar2(30);
1136
1137 -- FOR PING
1138 l_ping_result VARCHAR2(10);
1139 l_module varchar2(300) := 'bis.BIS_CREATE_REQUESTSET.add_any_object_to_set';
1140
1141
1142 begin
1143
1144
1145
1146 g_parameter_default_type:='C';
1147
1148 If p_refresh_mode is null and p_analyze_table='Y' then
1149 -----dbms_output.put_Line('in add_any_object_to_set');
1150 return;
1151 end if;
1152
1153
1154 logmsg('page/report name: '|| p_object_name);
1155 log( l_module, 'object name ' ||p_object_name);
1156 l_set_application:=p_set_application;
1157 if p_set_application is null then
1158 l_set_application:=g_set_application;
1159 end if;
1160
1161 if p_setname is not null then
1162 l_setname :=upper(p_setname);
1163 end if;
1164
1165 insert_stage_objects(upper(l_setname),
1166 l_set_application,
1167 p_object_name,
1168 p_object_type );
1169
1170
1171 open c_max_stage;
1172 fetch c_max_stage into l_max_stage;
1173 close c_max_stage;
1174
1175 open c_max_level;
1176 fetch c_max_level into l_max_level;
1177 close c_max_level;
1178
1179 if l_max_stage is null then
1180 l_max_stage:=0;
1181 end if;
1182
1183 logmsg('l_max_stage: '||l_max_stage);
1184 logmsg('l_max_level:'||l_max_level);
1185 logmsg('l_set_name: '||l_setname);
1186
1187
1188 if l_max_level>0 and l_max_level*100>l_max_stage then
1189 ----adding stages if this page has more levels of dependencies
1190 for i in trunc(l_max_stage/100+1)..l_max_level loop
1191 --- --dbms_output.put_Line('stages added: '||'Stage_'||to_char(i*100));
1192 if i=1 then
1196 set_application=>l_set_application,
1193 fnd_set.add_stage
1194 (name=>g_stage_prompt||' '||to_char(i*100),
1195 request_set=>upper(l_setname),
1197 short_name=>'Stage_'||to_char(i*100),
1198 description=>null,
1199 display_sequence=>i*100,
1200 function_short_name=>'FNDRSSTE',
1201 function_application=>'FND',
1202 critical=>'N',
1203 incompatibilities_allowed=>'N',
1204 start_stage=>'Y',
1205 language_code=>'US');
1206 else
1207 fnd_set.add_stage
1208 (name=>g_stage_prompt||' '||to_char(i*100),
1209 request_set=>upper(l_setname),
1210 set_application=>l_set_application,
1211 short_name=>'Stage_'||to_char(i*100),
1212 description=>null,
1213 display_sequence=>i*100,
1214 function_short_name=>'FNDRSSTE',
1215 function_application=>'FND',
1216 critical=>'N',
1217 incompatibilities_allowed=>'N',
1218 start_stage=>'N',
1219 language_code=>'US');
1220 end if;
1221 end loop;
1222 commit;
1223 end if; -----end if l_max_level>0 and l_m.....
1224
1225
1226 ----for each process, check if it exists in the set
1227 ----no, then add the process to corresponding stage
1228 --- yes , check the stage to decide if the process needs to be relocated
1229 for l_objects_rec in c_objects loop
1230 ---added for bug 4532066
1231 if get_impl_flag_temp(l_setname,l_set_application,p_object_type,p_object_name,l_objects_rec.object_type,l_objects_rec.object_name) ='Y' then
1232 l_objectname:=l_objects_rec.object_name;
1233 l_level:=l_objects_rec.mylevel;
1234 l_level_stage:= 'Stage_'||to_char((l_max_level-l_level+1)*100);
1235 logmsg('object name: '||l_objects_rec.object_name);
1236 log( l_module, 'object name: '||l_objects_rec.object_name||' object type: '||l_objects_rec.object_type||' level: '||l_level);
1237 l_process_counter:=0;
1238
1239 ---check if the object has been initial loaded or not. If yes, pull in incremental refresh program
1240 l_mode:=p_refresh_mode;
1241 -- FOR PING. Since one object can be shared in multiple pages/reports and check if it has data is time consuming
1242 ----Here we cache the result to improve performance
1243 if p_refresh_mode='INIT'
1244 and p_force_full_refresh='N'
1245 and ( l_objects_rec.object_type ='MV'
1246 OR (l_objects_rec.object_type in ('VIEW','TABLE') and object_has_program(l_objects_rec.object_type,l_objects_rec.object_name)='Y')) then
1247 l_ping_result := NULL;
1248 log( l_module, 'g_ping_table size ' || g_ping_table.count());
1249 FOR i in 1..g_ping_table.count()
1250 LOOP
1251 IF (g_ping_table(i).object_type = l_objects_rec.object_type AND
1252 g_ping_table(i).object_name = l_objects_rec.object_name) THEN
1253 l_ping_result := g_ping_table(i).HAS_DATA;
1254 log( l_module, 'reuse existing result for ' || l_objects_rec.object_name || ' : ' || l_ping_result);
1255 exit when (l_ping_result iS NOT NULL);
1256 END IF;
1257 END LOOP;
1258 if (l_ping_result = 'Y') THEN
1259 l_mode:='INCR';
1260 elsif (l_ping_result = 'N') THEN
1261 NULL;
1262 else
1263 g_ping_table.extend;
1264
1265 log( l_module, 'No existing result for ' || l_objects_rec.object_name);
1266 l_ping_result := object_has_data(l_objects_rec.object_name,l_objects_rec.object_type,l_objects_rec.object_owner);
1267 log( l_module, 'Enqueue ' || l_ping_result || ' for '|| l_objects_rec.object_name);
1268 g_ping_table(g_ping_table.last).object_owner := l_objects_rec.object_owner;
1269 g_ping_table(g_ping_table.last).object_type := l_objects_rec.object_type;
1270 g_ping_table(g_ping_table.last).object_name := l_objects_rec.object_name;
1271 g_ping_table(g_ping_table.last).has_data := l_ping_result;
1272 if l_ping_result ='Y' then
1273 l_mode:='INCR';
1274 end if;
1275 end if;
1276 end if;
1277 ------dbms_output.put_Line('l_mode: '||l_mode);
1278 log( l_module, 'l_mode: ' || l_mode);
1279
1280 for l_process_rec in c_process(l_objects_rec.object_name,l_objects_rec.object_type,l_mode) loop
1281 l_process_name:=l_process_rec.concurrent_program_name;
1282 l_process_app:=l_process_rec.application_short_name;
1283 -----dbms_output.put_Line('process name:'||l_process_name);
1284 log( l_module, ' refresh program: ' ||l_process_name );
1285 l_process_counter:=l_process_counter+1;
1286
1287
1288 get_stage_sequence(upper(l_setname),
1289 l_set_application,
1290 l_process_name,
1291 l_process_app,
1292 l_exist_stage,
1293 l_exist_sequence);
1294 if l_exist_stage is null then ---the program doesn't exist in the set
1295 l_max_program_seq:=get_max_prog_sequence(upper(l_setname),
1296 l_set_application,
1297 l_level_stage);
1298 if l_max_program_seq is null then
1299 l_max_program_seq:=0;
1300 end if;
1301 begin
1302 fnd_set.add_program
1303 (program =>l_process_name ,
1304 program_application=>l_process_app ,
1305 request_set=>upper(l_setname) ,
1309 critical=>'Y' ,
1306 set_application=>l_set_application ,
1307 stage=>l_level_stage,
1308 program_sequence=>l_max_program_seq+10,
1310 number_of_copies =>0,
1311 save_output =>'Y',
1312 style=>null,
1313 printer=>null);
1314 commit;
1315 exception
1316 when others then
1317 raise;
1318 end;
1319
1320 else ----the program already exist in the set
1321 l_exist_stage_number:=to_number(substr(l_exist_stage,7));
1322 -----if a same process alreay defined in a set and the stage is later than current process needed
1323 ----then we need to remove this process and re-add it to the set on an earlier stage
1324 if l_exist_stage_number>(l_max_level-l_level+1)*100 then
1325 begin
1326 fnd_set.remove_program
1327 (program=>l_process_name,
1328 program_application=>l_process_app,
1329 request_set=>upper(l_setname),
1330 set_application=>l_set_application,
1331 stage=>l_exist_stage,
1332 program_sequence=>l_exist_sequence);
1333 commit;
1334
1335 exception
1336 when others then
1337 raise;
1338 end;
1339 l_max_program_seq:=get_max_prog_sequence(upper(l_setname),
1340 l_set_application,
1341 l_level_stage);
1342 if l_max_program_seq is null then
1343 l_max_program_seq:=0;
1344 end if;
1345 begin
1346 fnd_set.add_program
1347 (program =>l_process_name ,
1348 program_application=>l_process_app ,
1349 request_set=>upper(l_setname) ,
1350 set_application=>l_set_application ,
1351 stage=>l_level_stage,
1352 program_sequence=>l_max_program_seq+10,
1353 critical=>'Y' ,
1354 number_of_copies =>0,
1355 save_output =>'Y',
1356 style=>null,
1357 printer=>null);
1358 commit;
1359 exception
1360 when others then
1361 raise;
1362 end;
1363 end if;---end if l_exist_stage_number>(l_max_level-l...
1364 end if; ---end if exist stage is null
1365 end loop;----end loop of processes
1366
1367
1368 if ( l_process_counter = 0 and -- no product team Refresh Program Defined in RSG
1369 l_objects_rec.object_type = 'MV' -- 'MV' type
1370 -- and l_objects_rec.object_owner<>'BSC'----exclude BSC MVs because they are loaded by BSC loader
1371 and p_refresh_mode not in ('DATA_VAL','SETUP_VAL') ) then
1372
1373 -- For 'MV' type object, call add_mv_to_set.
1374 add_mv_to_set(l_setname, l_set_application, l_level, 0,
1375 l_mode, l_objectname,l_max_level, l_process_counter);
1376
1377 end if; -- end if for 'MV' object type
1378
1379 -----for enhancement 4251030. Generate request sets for auto gen reports
1380 if g_bsc_auto_gen_exist='Y' and l_objects_rec.object_type='REPORT' and get_report_type(l_objectname)='BSCREPORT' then
1381
1382 add_auto_gen_reports(l_setname ,
1383 l_set_application,
1384 l_level ,
1385 l_max_level ,
1386 l_objectname) ;
1387
1388 end if;
1389
1390 end if;---end if implementation_flag='Y'
1391 end loop;----end loop of objects
1392 -----dbms_output.put_Line('end of all objects');
1393 log(l_module, 'end of all objects in the page');
1394
1395 end;
1396 --------******
1397
1398
1399
1400 function set_in_group(p_set_name varchar2,p_setapp varchar2,p_group_name varchar2,p_group_app varchar2) return varchar2 is
1401 cursor c_set_exist is
1402 select 'Y'
1403 from dual
1404 where exists
1405 (select a.request_set_name
1406 from fnd_request_sets a,
1407 fnd_request_group_units b,
1408 fnd_request_groups c,
1409 fnd_application d1,
1410 fnd_application d2
1411 where a.application_id=b.unit_application_id
1412 and a.request_set_id=b.request_unit_id
1413 and b.request_unit_type='S'
1414 and a.application_id=d1.application_id
1415 and a.request_set_name=p_set_name
1416 and d1.application_short_name=p_setapp
1417 and c.application_id=b.application_id
1418 and c.request_group_id=b.request_group_id
1419 and c.application_id=d2.application_id
1420 and d2.application_short_name=p_group_app
1421 and c.request_group_name=p_group_name);
1422 l_dummy varchar2(1);
1423
1424 begin
1425 open c_set_exist;
1426 fetch c_set_exist into l_dummy;
1427 if c_set_exist%notfound then
1428 return 'N';
1429 else
1430 return 'Y';
1431 end if;
1432 close c_set_exist;
1433 exception
1434 when others then
1435 raise;
1436 end;
1437
1438
1439 -----This procedure will check if any stage in the set is empty.
1440 ----If yes, remove the empty stages, relink stages and reset start stage
1441 procedure remove_empty_stages(p_set_name varchar2,
1442 p_setapp varchar2) is
1443 cursor c_stages is
1444 select
1445 a.application_id set_app_id ,
1446 a.request_set_id set_id ,
1447 c.REQUEST_SET_STAGE_ID stage_id,
1448 c.STAGE_NAME stage_name,
1449 c.display_sequence
1453 fnd_request_set_stages c
1450 from
1451 fnd_request_sets a,
1452 fnd_application b,
1454 where
1455 a.application_id=b.application_id
1456 and b.application_short_name=p_setapp
1457 and a.application_id=c.SET_APPLICATION_ID
1458 and a.request_set_id=c.REQUEST_SET_ID
1459 and a.request_set_name=upper(p_set_name)
1460 order by c.display_sequence;
1461
1462 cursor c_start_stage is
1463 select start_stage
1464 from
1465 fnd_request_sets a,
1466 fnd_application b
1467 where
1468 a.application_id=b.application_id
1469 and a.request_set_name=p_set_name
1470 and b.application_short_name=p_setapp;
1471
1472 l_stage_rec c_stages%rowtype;
1473 l_stage_array varcharTableType;
1474 l_counter integer;
1475 l_first_stage_id number;
1476 l_start_stage_id number;
1477 l_set_id number;
1478 l_set_app_id number;
1479
1480 begin
1481 ---------remove empty stages
1482 for l_stage_rec in c_stages loop
1483 if is_stage_empty(l_stage_rec.set_app_id,
1484 l_stage_rec.set_id,
1485 l_stage_rec.stage_id)='Y' then
1486 fnd_set.remove_stage(
1487 request_set=>upper(p_set_name),
1488 set_application=>p_setapp,
1489 stage=>l_stage_rec.stage_name);
1490 commit;
1491 end if;
1492 end loop;
1493
1494 -----relink stages after the empty stages have been removed
1495 l_counter:=0;
1496 for l_stage_rec in c_stages loop
1497 l_counter:=l_counter+1;
1498 if l_counter=1 then
1499 l_first_stage_id:=l_stage_rec.stage_id;
1500 l_set_id:=l_stage_rec.set_id;
1501 l_set_app_id:=l_stage_rec.set_app_id;
1502 end if;
1503 l_stage_array(l_counter):=l_stage_rec.stage_name;
1504 end loop;
1505
1506 if l_counter>1 then
1507 for i in 1..l_counter-1 loop
1508 fnd_set.link_stages
1509 (request_set=>upper(p_set_name),
1510 set_application=>p_setapp,
1511 from_stage=>l_stage_array(i),
1512 to_stage=>l_stage_array(i+1),
1513 success=>'Y',
1514 warning=>'Y',
1515 error=>'N');
1516 end loop;
1517 end if;
1518 commit;
1519
1520 -----check if start stage is valid. If not, reset the start stage
1521 open c_start_stage;
1522 fetch c_start_stage into l_start_stage_id;
1523 if c_start_stage%notfound then
1524 l_start_stage_id:=null;
1525 end if;
1526 close c_start_stage;
1527 if nvl(l_start_stage_id, -1)<>nvl(l_first_stage_id,-1) then
1528 ---update the start stage
1529 update fnd_request_sets
1530 set start_stage=l_first_stage_id
1531 where request_set_id=l_set_id
1532 and application_id=l_set_app_id;
1533 commit;
1534 end if;
1535
1536 end;
1537
1538
1539 function is_stage_empty(p_setapp_id number,
1540 p_set_id number,
1541 p_set_stage_id number) return varchar2 is
1542 cursor c_stage_empty is
1543 select 'N'
1544 from dual
1545 where exists
1546 (select request_set_program_id
1547 from
1548 fnd_request_set_programs
1549 where set_application_id=p_setapp_id
1550 and request_set_id=p_set_id
1551 and request_set_stage_id=p_set_stage_id);
1552 l_dummy varchar2(1);
1553 begin
1554 open c_stage_empty;
1555 fetch c_stage_empty into l_dummy;
1556 if c_stage_empty%notfound then
1557 l_dummy:='Y';
1558 end if;
1559 close c_stage_empty;
1560 return l_dummy;
1561 end;
1562
1563
1564
1565
1566 procedure get_stats_stage_sequence(p_set_name in varchar2,
1567 p_set_app in varchar2,
1568 p_process_name in varchar2,
1569 p_parameter_value in varchar2,
1570 p_parameter_type in varchar2,
1571 x_stage out NOCOPY varchar2,
1572 x_sequence out NOCOPY number) is
1573
1574 cursor c_stage is
1575 select
1576 distinct
1577 b.stage_name,
1578 c.sequence
1579 from
1580 fnd_request_sets a,
1581 fnd_request_set_stages b,
1582 fnd_request_set_programs c,
1583 fnd_request_set_program_args d,
1584 fnd_application e
1585 where a.request_set_id=b.request_set_id
1586 and a.application_id=b.set_application_id
1587 and a.application_id=e.application_id
1588 and e.application_short_name=p_set_app
1589 and a.request_set_name=p_set_name
1590 and b.set_application_id=c.set_application_id
1591 and b.request_set_id=c.request_set_id
1592 and b.request_set_stage_id=c.request_set_stage_id
1593 and c.request_set_id=d.request_set_id
1594 and c.set_application_id=d.application_id
1595 and c.request_set_program_id=d.request_set_program_id
1596 and d.descriptive_flexfield_name='$SRS$.'||p_process_name
1597 and d.default_type=p_parameter_type
1598 and d.default_value=p_parameter_value;
1599
1600
1601 l_stage varchar2(30);
1602 l_sequence number;
1603 l_stage_rec c_stage%rowtype;
1604
1605 begin
1606 l_stage:=null;
1607 l_sequence:=null;
1608 for l_stage_rec in c_stage loop
1609 l_stage:=l_stage_rec.stage_name;
1610 l_sequence:=l_stage_rec.sequence;
1611 end loop;
1612 x_stage:=l_stage;
1613 x_sequence:=l_sequence;
1617 x_sequence:=null;
1614 exception
1615 when others then
1616 x_stage:=null;
1618 raise;
1619 end;
1620
1621 FUNCTION get_apps_schema_name RETURN VARCHAR2 IS
1622
1623 l_apps_schema_name VARCHAR2(30);
1624
1625 CURSOR c_apps_schema_name IS
1626 SELECT oracle_username
1627 FROM fnd_oracle_userid WHERE oracle_id
1628 BETWEEN 900 AND 999 AND read_only_flag = 'U';
1629 BEGIN
1630
1631 OPEN c_apps_schema_name;
1632 FETCH c_apps_schema_name INTO l_apps_schema_name;
1633 CLOSE c_apps_schema_name;
1634 RETURN l_apps_schema_name;
1635
1636 EXCEPTION
1637 WHEN OTHERS THEN
1638 RETURN NULL;
1639 END get_apps_schema_name;
1640
1641
1642 function get_object_owner(p_obj_name in varchar2,p_obj_type in varchar2) return varchar2 is
1643
1644 l_owner varchar2(30);
1645
1646 /**
1647 cursor c_mv_owner(p_apps_schema_name varchar2) is
1648 (SELECT owner
1649 FROM all_mviews
1650 WHERE owner = p_apps_schema_name
1651 AND Upper(mview_name) = Upper(p_obj_name))
1652 UNION ALL
1653 (SELECT s.table_owner owner
1654 FROM all_mviews mv, user_synonyms s
1655 WHERE mv.owner = s.table_owner
1656 AND mv.mview_name = s.table_name
1657 AND Upper(mv.mview_name) = Upper(p_obj_name)
1658 );**/
1659
1660 cursor c_mv_owner1(p_apps_schema_name varchar2) is
1661 select owner
1662 from all_mviews
1663 where owner=p_apps_schema_name
1664 and mview_name = Upper(p_obj_name);
1665
1666 cursor c_mv_owner2 is
1667 SELECT s.table_owner owner
1668 FROM all_mviews mv, user_synonyms s
1669 WHERE mv.owner = s.table_owner
1670 AND mv.mview_name = s.table_name
1671 AND s.synonym_name = Upper(p_obj_name);
1672
1673
1674 /**
1675 CURSOR c_tab_owner(p_apps_schema_name varchar2) IS
1676 (SELECT owner
1677 FROM all_tables
1678 WHERE owner = p_apps_schema_name
1679 AND Upper(table_name) = Upper(p_obj_name))
1680 UNION ALL
1681 (SELECT s.table_owner owner
1682 FROM user_synonyms s, all_tables t
1683 WHERE t.owner = s.table_owner
1684 AND t.table_name = s.table_name
1685 AND t.table_name = Upper(p_obj_name));**/
1686
1687 cursor c_tab_owner1 is
1688 SELECT s.table_owner owner
1689 FROM user_synonyms s, all_tables t
1690 WHERE t.owner = s.table_owner
1691 AND t.table_name = s.table_name
1692 AND s.synonym_name = Upper(p_obj_name);
1693
1694 cursor c_tab_owner2(p_apps_schema_name varchar2) IS
1695 select owner
1696 from all_tables
1697 where owner= p_apps_schema_name
1698 and table_name = Upper(p_obj_name);
1699
1700 CURSOR c_view_owner(p_apps_schema_name varchar2) IS
1701 SELECT p_apps_schema_name owner
1702 FROM user_views
1703 WHERE view_name = Upper(p_obj_name);
1704
1705 l_view_owner_rec c_view_owner%rowtype;
1706
1707 begin
1708 if g_apps_schema_name is null then
1709 g_apps_schema_name:=get_apps_schema_name;
1710 end if;
1711
1712 if p_obj_type='MV' then
1713 open c_mv_owner1(g_apps_schema_name);
1714 fetch c_mv_owner1 into l_owner;
1715 if c_mv_owner1%notfound then
1716 open c_mv_owner2;
1717 fetch c_mv_owner2 into l_owner;
1718 if c_mv_owner2%notfound then
1719 l_owner:='NOTFOUND';
1720 end if;
1721 close c_mv_owner2;
1722 end if;
1723 close c_mv_owner1;
1724 end if;
1725
1726 if p_obj_type='TABLE' then
1727 open c_tab_owner1;
1728 fetch c_tab_owner1 into l_owner;
1729 if c_tab_owner1%notfound then
1730 open c_tab_owner2(g_apps_schema_name);
1731 fetch c_tab_owner2 into l_owner;
1732 if c_tab_owner2%notfound then
1733 l_owner:='NOTFOUND';
1734 end if;
1735 close c_tab_owner2;
1736 end if;
1737 close c_tab_owner1;
1738 end if;
1739 if p_obj_type='VIEW' then
1740 l_owner:='NOTFOUND';
1741 for l_view_owner_rec in c_view_owner(g_apps_schema_name) loop
1742 l_owner:=l_view_owner_rec.owner;
1743 end loop;
1744 end if;
1745 return l_owner;
1746 exception
1747 when others then
1748 raise;
1749 end;
1750
1751
1752
1753
1754
1755
1756 ---This procedure will move the dimension's dependent
1757 ---dimensions to the earlier stage than the stage
1758 ---for the dimension itself
1759
1760 -- aguwalan - The following procedure is no longer in use. Also it has issues
1761 -- reported in the Performance Repository
1762 /*
1763 procedure move_depend_dimensions(p_dim_name in varchar2,
1764 p_dim_type in varchar2,
1765 p_setname in varchar2,
1766 p_setapp in varchar2,
1767 p_option in varchar2,
1768 p_analyze_table in varchar2,
1769 p_refresh_mode in varchar2,
1770 p_start_stage in number) is
1771
1772 -----this cursor fetches depend dimensions only.
1773 -----if a dimension depdends on non-dimension objects,
1774 ----those will be handled in add_table_to_set
1775 cursor c_depend_dimensions is
1776 select distinct
1777 depend_object_type object_type,
1781 where a.enabled_flag='Y'
1778 depend_OBJECT_NAME object_name,
1779 level
1780 from bis_obj_dependency a
1782 and a.object_type<>'VIEW'
1783 and EXISTS( Select 'Y' from bis_obj_properties b
1784 where a.depend_object_name=b.object_name
1785 and a.depend_object_type=b.object_type
1786 and b.DIMENSION_FLAG='Y' )
1787 start with a.object_type= p_dim_type and a.object_name=p_dim_name
1788 connect by prior a.DEPEND_OBJECT_NAME=a.object_name
1789 and prior a.depend_object_type= a.object_type
1790 order by level desc;
1791
1792 cursor c_max_level is
1793 select max(level)
1794 from bis_obj_dependency a
1795 where a.enabled_flag='Y'
1796 and EXISTS ( Select 'Y' from bis_obj_properties b
1797 where a.depend_object_name=b.object_name
1798 and a.depend_object_type=b.object_type
1799 and b.DIMENSION_FLAG='Y')
1800 start with a.object_type= p_dim_type and a.object_name=p_dim_name
1801 connect by prior a.DEPEND_OBJECT_NAME=a.object_name
1802 and prior a.depend_object_type=a.object_type
1803 order by level desc;
1804
1805 cursor c_process (p_objectname varchar2,p_objecttype varchar2) is
1806 select distinct
1807 a.CONC_PROGRAM_NAME CONCURRENT_PROGRAM_NAME,
1808 a.CONC_APP_SHORT_NAME APPLICATION_SHORT_NAME
1809 from
1810 bis_obj_prog_linkages a,
1811 fnd_concurrent_programs b
1812 where a.object_name=p_objectname
1813 and a.object_type=p_objecttype
1814 and a.enabled_flag='Y'
1815 ---and (nvl(a.refresh_mode,'INCR')=p_refresh_mode or nvl(a.refresh_mode,'INCR')='INIT_INCR')
1816 and decode(nvl(a.refresh_mode,'INCR'),'INIT_INCR','INCR',nvl(a.refresh_mode,'INCR'))=p_refresh_mode
1817 and a.CONC_PROGRAM_NAME=b.concurrent_program_name
1818 and a.CONC_APP_ID=b.application_id
1819 and b.ENABLED_FLAG='Y';
1820
1821 cursor c_min_stage is
1822 select
1823 min(b.display_sequence)
1824 from
1825 fnd_request_sets a,
1826 fnd_request_set_stages b,
1827 fnd_application c
1828 where a.request_set_id=b.request_set_id
1829 and a.application_id=b.set_application_id
1830 and a.application_id=c.application_id
1831 and c.application_short_name=p_setapp
1832 and a.request_set_name=upper(p_setname);
1833
1834 l_max_dim_depend_level number;
1835 l_min_stage number;
1836 l_depend_dimension_rec c_depend_dimensions%rowtype;
1837 l_process_counter integer;
1838 l_process_rec c_process%rowtype;
1839 l_process_name varchar2(30);
1840 l_process_app varchar2(30);
1841 l_exist_stage varchar2(30);
1842 l_exist_stage_number number;
1843 l_exist_sequence number;
1844 l_max_program_seq number;
1845 l_level integer;
1846
1847
1848 begin
1849 ---add stages if needed
1850 open c_min_stage;
1851 fetch c_min_stage into l_min_stage;
1852 close c_min_stage;
1853 open c_max_level;
1854 fetch c_max_level into l_max_dim_depend_level;
1855 close c_max_level;
1856 -- ----dbms_output.put_Line('dim type: '||p_dim_type);
1857 -- ----dbms_output.put_Line('dim name: '||p_dim_name);
1858
1859 ------dbms_output.put_Line('min stage: '||l_min_stage);
1860 ------dbms_output.put_Line('max level: '||l_max_dim_depend_level);
1861 -- ----dbms_output.put_Line('p_start_stage: '||p_start_stage);
1862
1863 ----adding stages if this dimension has more levels of dependencies
1864 if (l_max_dim_depend_level>0 and p_start_stage-l_max_dim_depend_level*100 <l_min_stage) then
1865 for i in trunc(l_min_stage/100-1)..p_start_stage/100-l_max_dim_depend_level loop
1866 -- ----dbms_output.put_Line('i : '||i);
1867 fnd_set.add_stage
1868 (name=>g_stage_prompt||' '||to_char(i*100),
1869 request_set=>upper(p_setname),
1870 set_application=>p_setapp,
1871 short_name=>'Stage_'||to_char(i*100),
1872 description=>null,
1873 display_sequence=>i*100,
1874 function_short_name=>'FNDRSSTE',
1875 function_application=>'FND',
1876 critical=>'N',
1877 incompatibilities_allowed=>'N',
1878 start_stage=>'N',
1879 language_code=>'US');
1880 end loop;
1881 commit;
1882 end if; --end if l_max_dim_depend_level>0 and......
1883
1884
1885 ----for each process, check if it exists in the set
1886 ----no, then add the process to corresponding stage
1887 --- yes , check the stage to decide
1888 for l_depend_dimension_rec in c_depend_dimensions loop
1889 --l_objectname:=l_depend_dimension_rec.object_name;
1890 -- ----dbms_output.put_Line('depend dim: '||l_depend_dimension_rec.object_name);
1891 l_level:=l_depend_dimension_rec.level;
1892 l_process_counter:=0;
1893 for l_process_rec in c_process(l_depend_dimension_rec.object_name,l_depend_dimension_rec.object_type) loop
1894 l_process_name:=l_process_rec.concurrent_program_name;
1895 -- ----dbms_output.put_Line('depend dim program name: '||l_process_name);
1896 l_process_app:=l_process_rec.application_short_name;
1897 l_process_counter:=l_process_counter+1;
1898 get_stage_sequence(upper(p_setname),
1899 p_setapp,
1900 l_process_name,
1901 l_process_app,
1902 l_exist_stage,
1903 l_exist_sequence);
1904
1905 l_max_program_seq:=get_max_prog_sequence(upper(p_setname),
1906 p_setapp,
1910 end if;
1907 'Stage_'||to_char(p_start_stage-l_level*100));
1908 if l_max_program_seq is null then
1909 l_max_program_seq:=0;
1911
1912 if l_exist_stage is null then
1913 begin
1914 fnd_set.add_program
1915 (program =>l_process_name ,
1916 program_application=>l_process_app ,
1917 request_set=>upper(p_setname) ,
1918 set_application=>p_setapp ,
1919 stage=>'Stage_'||to_char(p_start_stage-l_level*100),
1920 program_sequence=>l_max_program_seq+10,
1921 critical=>'Y' ,
1922 number_of_copies =>0,
1923 save_output =>'Y',
1924 style=>null,
1925 printer=>null);
1926 commit;
1927 exception
1928 when others then
1929 raise;
1930 end;
1931 else
1932 l_exist_stage_number:=to_number(substr(l_exist_stage,7));
1933 -----if a same process alreay exist in a set and the stage is later than the current process needed
1934 ----then we need to remove the existing process and re-add it to the set on an ealier stage
1935 if l_exist_stage_number>p_start_stage-l_level*100 then
1936 begin
1937 fnd_set.remove_program
1938 (program=>l_process_name,
1939 program_application=>l_process_app,
1940 request_set=>upper(p_setname),
1941 set_application=>p_setapp,
1942 stage=>l_exist_stage,
1943 program_sequence=>l_exist_sequence);
1944 commit;
1945 exception
1946 when others then
1947 raise;
1948 end;
1949
1950 begin
1951 fnd_set.add_program
1952 (program =>l_process_name ,
1953 program_application=>l_process_app ,
1954 request_set=>upper(p_setname) ,
1955 set_application=>p_setapp,
1956 stage=>'Stage_'||to_char(p_start_stage-l_level*100),
1957 program_sequence=>l_max_program_seq+10,
1958 critical=>'Y' ,
1959 number_of_copies =>0,
1960 save_output =>'Y',
1961 style=>null,
1962 printer=>null);
1963 commit;
1964 exception
1965 when others then
1966 raise;
1967 end;
1968 end if; ---end if l_exist_stage_number>p_s....
1969 end if; ---end if exist stage is null
1970 end loop; ---end loop of processes
1971
1972 ---- if the depend dimension is a MV and
1973 -----product teams didn't define MV refresh program in RSG
1974 ----then call RSG generic MV refresh program
1975 if (l_process_counter = 0 and -- no product team Refresh Program Defined in RSG
1976 l_depend_dimension_rec.object_type = 'MV' -- 'MV' type
1977 ) then
1978
1979 l_process_name := 'BIS_MV_REFRESH';
1980 l_process_app := 'BIS';
1981
1982 get_stats_stage_sequence(upper(p_setname),
1983 p_setapp,
1984 l_process_name,
1985 l_depend_dimension_rec.object_name,
1986 g_parameter_default_type,
1987 l_exist_stage,
1988 l_exist_sequence);
1989
1990 l_max_program_seq:=get_max_prog_sequence(upper(p_setname),
1991 p_setapp,
1992 'Stage_'||to_char(p_start_stage-l_level*100));
1993 if l_max_program_seq is null then
1994 l_max_program_seq:=0;
1995 end if;
1996
1997
1998
1999 if l_exist_stage is null then
2000 begin
2001 fnd_set.add_program(
2002 program =>l_process_name ,
2003 program_application=>l_process_app ,
2004 request_set=>upper(p_setname) ,
2005 set_application=>p_setapp,
2006 stage=>'Stage_'||to_char(p_start_stage-l_level*100),
2007 program_sequence=>l_max_program_seq+10,
2008 critical=>'Y' ,
2009 number_of_copies =>0,
2010 save_output =>'Y',
2011 style=>null,
2012 printer=>null);
2013
2014 fnd_set.PROGRAM_PARAMETER(
2015 PROGRAM=>l_process_name,
2016 PROGRAM_APPLICATION=>l_process_app,
2017 REQUEST_SET=>upper(p_setname),
2018 SET_APPLICATION=>p_setapp,
2019 STAGE=>'Stage_'||to_char(p_start_stage-l_level*100),
2020 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2021 PARAMETER=>'Refresh Mode',
2022 DISPLAY=>'Y',
2023 MODIFY=> 'Y' ,
2024 SHARED_PARAMETER=>null ,
2025 DEFAULT_TYPE=>'Constant',
2026 DEFAULT_VALUE=>p_refresh_mode
2027 );
2028
2029 fnd_set.PROGRAM_PARAMETER(
2030 PROGRAM=>l_process_name,
2031 PROGRAM_APPLICATION=>l_process_app,
2032 REQUEST_SET=>upper(p_setname),
2033 SET_APPLICATION=>p_setapp,
2034 STAGE=>'Stage_'||to_char(p_start_stage-l_level*100),
2035 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2036 PARAMETER=>'Materialized View',
2037 DISPLAY=>'Y',
2038 MODIFY=> 'Y' ,
2042
2039 SHARED_PARAMETER=>null ,
2040 DEFAULT_TYPE=>'Constant',
2041 DEFAULT_VALUE=>l_depend_dimension_rec.object_name );
2043 commit;
2044 exception
2045 when others then
2046 raise;
2047 end;
2048 else
2049 l_exist_stage_number:=to_number(substr(l_exist_stage,7));
2050 ----For dimensions, if a same process alreay exist in a set and the stage is later than the current process needed
2051 ----then we need to remove the existing process and re-add it to the set on an ealier stage
2052 if l_exist_stage_number>p_start_stage-l_level*100 then
2053 begin
2054 fnd_set.remove_program
2055 (program=>l_process_name,
2056 program_application=>l_process_app,
2057 request_set=>upper(p_setname),
2058 set_application=>p_setapp,
2059 stage=>l_exist_stage,
2060 program_sequence=>l_exist_sequence);
2061 commit;
2062 exception
2063 when others then
2064 raise;
2065 end;
2066
2067 begin
2068 fnd_set.add_program(
2069 program =>l_process_name ,
2070 program_application=>l_process_app ,
2071 request_set=>upper(p_setname) ,
2072 set_application=>p_setapp,
2073 stage=>'Stage_'||to_char(p_start_stage-l_level*100),
2074 program_sequence=>l_max_program_seq+10,
2075 critical=>'Y' ,
2076 number_of_copies =>0,
2077 save_output =>'Y',
2078 style=>null,
2079 printer=>null);
2080
2081 fnd_set.PROGRAM_PARAMETER(
2082 PROGRAM=>l_process_name,
2083 PROGRAM_APPLICATION=>l_process_app,
2084 REQUEST_SET=>upper(p_setname),
2085 SET_APPLICATION=>p_setapp,
2086 STAGE=>'Stage_'||to_char(p_start_stage-l_level*100),
2087 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2088 PARAMETER=>'Refresh Mode',
2089 DISPLAY=>'Y',
2090 MODIFY=> 'Y' ,
2091 SHARED_PARAMETER=>null ,
2092 DEFAULT_TYPE=>'Constant',
2093 DEFAULT_VALUE=>p_refresh_mode
2094 );
2095
2096 fnd_set.PROGRAM_PARAMETER(
2097 PROGRAM=>l_process_name,
2098 PROGRAM_APPLICATION=>l_process_app,
2099 REQUEST_SET=>upper(p_setname),
2100 SET_APPLICATION=>p_setapp,
2101 STAGE=>'Stage_'||to_char(p_start_stage-l_level*100),
2102 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2103 PARAMETER=>'Materialized View',
2104 DISPLAY=>'Y',
2105 MODIFY=> 'Y' ,
2106 SHARED_PARAMETER=>null ,
2107 DEFAULT_TYPE=>'Constant',
2108 DEFAULT_VALUE=>l_depend_dimension_rec.object_name
2109 );
2110 commit;
2111 exception
2112 when others then
2113 raise;
2114 end;
2115 end if; ---end if l_exist_stage_number>(l_max_leve..
2116 end if; ---end if exist stage is null
2117 end if; -- end if for 'MV' object type
2118
2119 end loop; ---end loop of depend dimensions l_depend_dimension_rec
2120
2121
2122 end;*/
2123
2124
2125
2126 procedure add_object_to_set(p_object_type in varchar2,
2127 p_object_name in varchar2,
2128 p_object_owner in varchar2,
2129 p_setname in varchar2,
2130 p_setapp in varchar2,
2131 p_option in varchar2,
2132 p_analyze_table in varchar2,
2133 p_refresh_mode in varchar2,
2134 p_portal_exist in varchar2,
2135 p_force_full_refresh in varchar2) is
2136
2137 begin
2138 -- FOR PING
2139 g_ping_table := T_PING_TABLE();
2140 add_any_object_to_set(p_object_name,
2141 p_object_type,
2142 p_setname ,
2143 p_setapp ,
2144 p_option ,
2145 p_analyze_table ,
2146 p_refresh_mode ,
2147 p_force_full_refresh );
2148
2149 /* changes for 'view request set history': insert record into
2150 bis_request_set_objects. */
2151 create_rs_objects(upper(p_setname), p_setapp, p_object_type,
2152 p_object_name , p_object_owner);
2153
2154
2155
2156 end;
2157
2158
2159
2160
2161
2162
2163 ---?????Need to validate this part of logic
2164 ----this function will return 'N' if the object has no direct dependency except dimensions
2165 function dependency_exist(p_object_name in varchar2, p_object_type in varchar2) return varchar2 is
2166 cursor c_dependency_exist is
2167 select 'Y'
2168 from dual
2169 where exists
2170 (select a.depend_object_name
2171 from bis_obj_dependency a,
2172 bis_obj_properties b
2173 where a.depend_object_name=b.object_name(+)
2174 and a.depend_object_type=b.object_type(+)
2175 and a.object_name=p_object_name
2176 and a.object_type=p_object_type
2177 and a.enabled_flag='Y'
2178 and nvl(b.dimension_flag,'N')='N');
2179 l_dummy varchar2(1);
2180 begin
2184 l_dummy:='N';
2181 open c_dependency_exist;
2182 fetch c_dependency_exist into l_dummy;
2183 if c_dependency_exist%notfound then
2185 end if;
2186 close c_dependency_exist;
2187 return l_dummy;
2188 exception
2189 when others then
2190 raise;
2191 end;
2192
2193
2194
2195
2196 procedure add_mv_log_mgt_programs(p_setname in varchar2,
2197 p_setapp in varchar2,
2198 p_stage_number in number,
2199 p_obj_type in varchar2,
2200 p_obj_name in varchar2,
2201 p_custom_api in varchar2,
2202 p_mode in varchar2,
2203 p_program_name in varchar2,
2204 p_program_app in varchar2) is
2205
2206 L_MAX_PROGRAM_SEQ number;
2207
2208 begin
2209 l_max_program_seq:=get_max_prog_sequence(upper(p_setname),
2210 p_setapp,
2211 'Stage_'||to_char(p_stage_number));
2212 if l_max_program_seq is null then
2213 l_max_program_seq:=0;
2214 end if;
2215 begin
2216 fnd_set.add_program
2217 (program =>p_program_name ,
2218 program_application=>p_program_app,
2219 request_set=>upper(p_setname),
2220 set_application=>p_setapp ,
2221 stage=>'Stage_'||to_char(p_stage_number),
2222 program_sequence=>l_max_program_seq+10,
2223 critical=>'Y' ,
2224 number_of_copies =>0,
2225 save_output =>'Y',
2226 style=>null,
2227 printer=>null);
2228 commit;
2229
2230 ------register parameters
2231 fnd_set.PROGRAM_PARAMETER(
2232 PROGRAM=>p_program_name,
2233 PROGRAM_APPLICATION=>p_program_app,
2234 REQUEST_SET=>upper(p_setname),
2235 SET_APPLICATION=>p_setapp,
2236 STAGE=>'Stage_'||to_char(p_stage_number),
2237 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2238 PARAMETER=>'API Name',
2239 DISPLAY=>'Y',
2240 MODIFY=> 'Y' ,
2241 SHARED_PARAMETER=>null ,
2242 DEFAULT_TYPE=>'Constant',
2243 DEFAULT_VALUE=>p_custom_api
2244 );
2245
2246 fnd_set.PROGRAM_PARAMETER(
2247 PROGRAM=>p_program_name,
2248 PROGRAM_APPLICATION=>p_program_app,
2249 REQUEST_SET=>upper(p_setname),
2250 SET_APPLICATION=>p_setapp,
2251 STAGE=>'Stage_'||to_char(p_stage_number),
2252 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2253 PARAMETER=>'Object Name',
2254 DISPLAY=>'Y',
2255 MODIFY=> 'Y' ,
2256 SHARED_PARAMETER=>null ,
2257 DEFAULT_TYPE=>'Constant',
2258 DEFAULT_VALUE=>p_obj_name
2259 );
2260
2261 fnd_set.PROGRAM_PARAMETER(
2262 PROGRAM=>p_program_name,
2263 PROGRAM_APPLICATION=>p_program_app,
2264 REQUEST_SET=>upper(p_setname),
2265 SET_APPLICATION=>p_setapp,
2266 STAGE=>'Stage_'||to_char(p_stage_number),
2267 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2268 PARAMETER=>'Object Type',
2269 DISPLAY=>'Y',
2270 MODIFY=> 'Y' ,
2271 SHARED_PARAMETER=>null ,
2272 DEFAULT_TYPE=>'Constant',
2273 DEFAULT_VALUE=>p_obj_type
2274 );
2275
2276 fnd_set.PROGRAM_PARAMETER(
2277 PROGRAM=>p_program_name,
2278 PROGRAM_APPLICATION=>p_program_app,
2279 REQUEST_SET=>upper(p_setname),
2280 SET_APPLICATION=>p_setapp,
2281 STAGE=>'Stage_'||to_char(p_stage_number),
2282 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2283 PARAMETER=>'Mode',
2284 DISPLAY=>'Y',
2285 MODIFY=> 'Y' ,
2286 SHARED_PARAMETER=>null ,
2287 DEFAULT_TYPE=>'Constant',
2288 DEFAULT_VALUE=>p_mode
2289 );
2290 commit;
2291 end;
2292
2293 end;
2294
2295
2296 function get_mv_log (p_object_name in varchar2,p_schema_name in varchar2 ) return varchar2 is
2297 cursor c_logs_for_analyze is
2298 SELECT DISTINCT LOG_TABLE
2299 FROM all_snapshot_logs
2300 WHERE master = p_object_name
2301 AND log_owner = p_schema_name;
2302
2303 l_log_name varchar2(30);
2304 begin
2305 l_log_name:=null;
2306 open c_logs_for_analyze;
2307 fetch c_logs_for_analyze into l_log_name;
2308 if c_logs_for_analyze%notfound then
2309 l_log_name:=null;
2310 end if;
2311 close c_logs_for_analyze;
2312 return l_log_name;
2313 exception
2314 when no_data_found then
2315 return null;
2316 when others then
2317 raise;
2318 end;
2319
2320
2321
2322
2323 ----Added for enhancement 3549337
2324 procedure analyze_objects_in_set(
2325 p_request_set_code IN VARCHAR2,
2326 p_set_app IN varchar2) is
2327
2328 cursor c_pages is
2329 select distinct a.object_name,a.object_type
2330 from
2331 bis_request_set_objects a,
2332 fnd_request_sets b,
2333 fnd_application c
2334 where a.request_set_name=b.request_set_name
2335 and a.set_app_id=b.application_id
2336 and b.request_set_name=upper(p_request_set_code)
2337 and b.application_id=c.application_id
2338 and c.application_short_name=p_set_app;
2339
2343 ( select distinct
2340 cursor c_objects(p_object_name varchar2,p_object_type varchar2) is
2341 select depend_objects.obj_type,depend_objects.obj_name
2342 from
2344 obj.depend_OBJECT_NAME obj_name,
2345 obj.depend_object_type obj_type,
2346 obj.depend_object_owner obj_owner
2347 from
2348 ( select object_name,
2349 object_type,
2350 object_owner,
2351 depend_object_name,
2352 depend_object_type,
2353 depend_object_owner,
2354 enabled_flag
2355 from
2356 bis_obj_dependency
2357 where enabled_flag='Y' ) obj
2358 start with obj.object_type =p_object_type
2359 and obj.object_name = p_object_name
2360 connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
2361 and prior depend_object_type=object_type
2362 ) depend_objects
2363 where depend_objects.obj_type='MV'
2364 or (depend_objects.obj_type in ('MV','TABLE') and
2365 depend_objects.obj_type||depend_objects.obj_name in
2366 (select object_type||object_name from bis_obj_prog_linkages where enabled_flag='Y'));
2367
2368 l_all_objects object_table;
2369 l_pages_rec c_pages%rowtype;
2370 l_obj_rec c_objects%rowtype;
2371 l_max_program_seq number;
2372
2373
2374 l_exist_flag varchar2(1);
2375 l_obj_owner varchar2(30);
2376 l_log_name varchar2(30);
2377
2378 begin
2379
2380 g_set_application:='BIS';
2381 g_fnd_stats:='BIS_BIA_STATS_TABLE';
2382 g_fnd_stats_app:='BIS';
2383 l_all_objects:=object_table();
2384
2385
2386 for l_pages_rec in c_pages loop
2387 for l_obj_rec in c_objects(l_pages_rec.object_name,l_pages_rec.object_type) loop
2388 -----dbms_output.put_Line(l_obj_rec.obj_name);
2389 l_exist_flag:='N';
2390 if l_all_objects.count()>0 then
2391 for i in 1..l_all_objects.count() loop
2392 if l_obj_rec.obj_type=l_all_objects(i).object_type and
2393 l_obj_rec.obj_name=l_all_objects(i).object_name then
2394 l_exist_flag:='Y';
2395 exit;
2396 end if;
2397 end loop;
2398 if l_exist_flag='N' then
2399 l_all_objects.extend;
2400 l_all_objects(l_all_objects.last).object_type:=l_obj_rec.obj_type;
2401 l_all_objects(l_all_objects.last).object_name:=l_obj_rec.obj_name;
2402 end if;
2403 else
2404 l_all_objects.extend;
2405 l_all_objects(l_all_objects.last).object_type:=l_obj_rec.obj_type;
2406 l_all_objects(l_all_objects.last).object_name:=l_obj_rec.obj_name;
2407 end if;
2408 end loop; --end loop of objects in a page
2409 end loop; ---end loop of pages
2410
2411
2412 fnd_set.add_stage
2413 (name=>g_stage_prompt||' '||to_char(100),
2414 request_set=>upper(p_request_set_code),
2415 set_application=> p_set_app,
2416 short_name=>'Stage_'||to_char(100),
2417 description=>null,
2418 display_sequence=>100,
2419 function_short_name=>'FNDRSSTE',
2420 function_application=>'FND',
2421 critical=>'N',
2422 incompatibilities_allowed=>'N',
2423 start_stage=>'Y',
2424 language_code=>'US');
2425 commit;
2426
2427
2428 for j in 1..l_all_objects.count() loop
2429
2430 l_max_program_seq:=get_max_prog_sequence(upper(p_request_set_code),
2431 p_set_app,
2432 'Stage_'||to_char(100));
2433 if l_max_program_seq is null then
2434 l_max_program_seq:=0;
2435 end if;
2436 --- --dbms_output.put_Line('prog sequence '||l_max_program_seq);
2437
2438 -------add analyze program for the object
2439 begin
2440 fnd_set.add_program
2441 (program =>g_fnd_stats ,
2442 program_application=>g_fnd_stats_app,
2443 request_set=>upper(p_request_set_code) ,
2444 set_application=>p_set_app ,
2445 stage=>'Stage_'||to_char(100),
2446 program_sequence=>l_max_program_seq+10,
2447 critical=>'Y' ,
2448 number_of_copies =>0,
2449 save_output =>'Y',
2450 style=>null,
2451 printer=>null);
2452 commit;
2453
2454 fnd_set.PROGRAM_PARAMETER(
2455 PROGRAM=>g_fnd_stats,
2456 PROGRAM_APPLICATION=>g_fnd_stats_app,
2457 REQUEST_SET=>upper(p_request_set_code),
2458 SET_APPLICATION=>p_set_app,
2459 STAGE=>'Stage_'||to_char(100),
2460 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2461 PARAMETER=>'Object Type',
2462 DISPLAY=>'Y',
2463 MODIFY=> 'Y' ,
2464 SHARED_PARAMETER=>null ,
2465 DEFAULT_TYPE=>'Constant',
2466 DEFAULT_VALUE=>l_all_objects(j).object_type
2467 );
2468
2469 fnd_set.PROGRAM_PARAMETER(
2470 PROGRAM=>g_fnd_stats,
2471 PROGRAM_APPLICATION=>g_fnd_stats_app,
2472 REQUEST_SET=>upper(p_request_set_code),
2473 SET_APPLICATION=>p_set_app,
2474 STAGE=>'Stage_'||to_char(100),
2475 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2476 PARAMETER=>'Object Name',
2477 DISPLAY=>'Y',
2478 MODIFY=> 'Y' ,
2479 SHARED_PARAMETER=>null ,
2480 DEFAULT_TYPE=>'Constant',
2481 DEFAULT_VALUE=>l_all_objects(j).object_name
2482 );
2483 commit;
2484 end;
2485
2486 end loop;
2487 exception
2491
2488 when others then
2489 raise;
2490 end;
2492
2493 ----this procedure is added for enhancement 3999465 and 4251030
2494 ----for RSG to support auto-gen reports
2495 -----it adds load dimension program and delete indicator data program
2496 ----before the load summary program
2497 procedure add_other_loader_programs(p_setname in varchar2,
2498 p_setapp in varchar2,
2499 p_refresh_mode in varchar2,
2500 p_force_full_refresh in varchar2,
2501 p_set_id number,
2502 p_set_app_id number,
2503 p_stage_id number,
2504 p_stage_name varchar2) is
2505
2506 cursor c_stage_objects is
2507 select distinct
2508 'REPORT' object_type,
2509 c.default_value object_name
2510 from
2511 fnd_request_set_programs a,
2512 fnd_request_set_program_args c
2513 where a.set_application_id=p_set_app_id
2514 and a.request_set_id=p_set_id
2515 and a.request_set_stage_id=p_stage_id
2516 and a.request_set_id=c.request_set_id
2517 and a.set_application_id=c.application_id
2518 and a.request_set_program_id=c.request_set_program_id
2519 and c.descriptive_flexfield_name='$SRS$.'||g_bsc_loader_ind_program
2520 and c.default_type=g_parameter_default_type
2521 and c.application_column_name='ATTRIBUTE1';
2522
2523 l_object_rec c_stage_objects%rowtype;
2524 l_counter number;
2525 l_current_stage_number number;
2526 l_dim_stage_number number;
2527 l_del_stage_number number;
2528 l_max_program_seq number;
2529
2530
2531 begin
2532
2533 l_current_stage_number:=to_number(substr(p_stage_name,7));
2534 --- dbms_output.put_line('l_current_stage_number: '||l_current_stage_number);
2535 l_counter:=0;
2536 for l_object_rec in c_stage_objects loop
2537 l_counter:=l_counter+1;
2538
2539 if l_counter=1 then
2540 if l_current_stage_number>=300 then
2541 l_dim_stage_number:=l_current_stage_number-100;
2542 if p_refresh_mode='INIT' then
2543 l_del_stage_number:=l_current_stage_number-200;
2544 end if ;----p_refresh_mode
2545 elsif l_current_stage_number>=200 then
2546 l_dim_stage_number:=l_current_stage_number-100;
2547 --dbms_output.put_line('l_dim_stage_number '||l_dim_stage_number);
2548 if p_refresh_mode='INIT' then
2549 l_del_stage_number:=l_current_stage_number-100-35;
2550 -- dbms_output.put_line('l_del_stage_number '||l_del_stage_number);
2551 fnd_set.add_stage
2552 (name=>g_stage_prompt||' '||to_char(l_del_stage_number),
2553 request_set=>upper(p_setname),
2554 set_application=>p_setapp,
2555 short_name=>'Stage_'||to_char(l_del_stage_number),
2556 description=>null,
2557 display_sequence=>l_del_stage_number,
2558 function_short_name=>'FNDRSSTE',
2559 function_application=>'FND',
2560 critical=>'N',
2561 incompatibilities_allowed=>'N',
2562 start_stage=>'N',
2563 language_code=>'US');
2564 commit;
2565 end if ;----p_refresh_mode
2566 else ---l_current_stage_number>=100
2567 l_dim_stage_number:=l_current_stage_number-30;
2568 fnd_set.add_stage
2569 (name=>g_stage_prompt||' '||to_char(l_dim_stage_number),
2570 request_set=>upper(p_setname),
2571 set_application=>p_setapp,
2572 short_name=>'Stage_'||to_char(l_dim_stage_number),
2573 description=>null,
2574 display_sequence=>l_dim_stage_number,
2575 function_short_name=>'FNDRSSTE',
2576 function_application=>'FND',
2577 critical=>'N',
2578 incompatibilities_allowed=>'N',
2579 start_stage=>'N',
2580 language_code=>'US');
2581 commit;
2582 if p_refresh_mode='INIT' then
2583 l_del_stage_number:=l_current_stage_number-35;
2584 fnd_set.add_stage
2585 (name=>g_stage_prompt||' '||to_char(l_del_stage_number),
2586 request_set=>upper(p_setname),
2587 set_application=>p_setapp,
2588 short_name=>'Stage_'||to_char(l_del_stage_number),
2589 description=>null,
2590 display_sequence=>l_del_stage_number,
2591 function_short_name=>'FNDRSSTE',
2592 function_application=>'FND',
2593 critical=>'N',
2594 incompatibilities_allowed=>'N',
2595 start_stage=>'N',
2596 language_code=>'US');
2597 commit;
2598 end if ;----p_refresh_mode
2599 end if;---end if l_current_stage_number>300
2600 end if;---end if l_counter=1
2601
2602
2603
2604 if p_refresh_mode='INIT' and p_force_full_refresh='Y' then
2605 l_max_program_seq:=get_max_prog_sequence(upper(p_setname),
2606 p_setapp,
2607 'Stage_'||to_char(l_del_stage_number));
2608
2609 if l_max_program_seq is null then
2610 l_max_program_seq:=0;
2611 end if;
2612
2613 fnd_set.add_program
2614 (program =>g_bsc_loader_del_program ,
2615 program_application=>'BSC',
2619 program_sequence=>l_max_program_seq+10,
2616 request_set=>upper(p_setname) ,
2617 set_application=>p_setapp ,
2618 stage=>'Stage_'||to_char(l_del_stage_number),
2620 critical=>'Y' ,
2621 number_of_copies =>0,
2622 save_output =>'Y',
2623 style=>null,
2624 printer=>null);
2625
2626
2627
2628 fnd_set.PROGRAM_PARAMETER(
2629 PROGRAM=>g_bsc_loader_del_program,
2630 PROGRAM_APPLICATION=>'BSC',
2631 REQUEST_SET=>upper(p_setname),
2632 SET_APPLICATION=>p_setapp,
2633 STAGE=>'Stage_'||to_char(l_del_stage_number),
2634 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2635 PARAMETER=>'x_indicators',
2636 DISPLAY=>'Y',
2637 MODIFY=> 'Y' ,
2638 SHARED_PARAMETER=>null ,
2639 DEFAULT_TYPE=>'Constant',
2640 DEFAULT_VALUE=>l_object_rec.object_name
2641 );
2642
2643 fnd_set.PROGRAM_PARAMETER(
2644 PROGRAM=>g_bsc_loader_del_program,
2645 PROGRAM_APPLICATION=>'BSC',
2646 REQUEST_SET=>upper(p_setname),
2647 SET_APPLICATION=>p_setapp,
2648 STAGE=>'Stage_'||to_char(l_del_stage_number),
2649 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2650 PARAMETER=>'x_keep_input_table_data',
2651 DISPLAY=>'Y',
2652 MODIFY=> 'Y' ,
2653 SHARED_PARAMETER=>null ,
2654 DEFAULT_TYPE=>'Constant',
2655 DEFAULT_VALUE=>'Y'
2656 );
2657 commit;
2658 end if; ----p_refresh_mode ='INIT' and force full refresh='Y'
2659
2660 l_max_program_seq:=get_max_prog_sequence(upper(p_setname),
2661 p_setapp,
2662 'Stage_'||to_char(l_dim_stage_number));
2663
2664 if l_max_program_seq is null then
2665 l_max_program_seq:=0;
2666 end if;
2667
2668 fnd_set.add_program
2669 (program =>g_bsc_loader_dim_program ,
2670 program_application=>'BSC',
2671 request_set=>upper(p_setname) ,
2672 set_application=>p_setapp ,
2673 stage=>'Stage_'||to_char(l_dim_stage_number),
2674 program_sequence=>l_max_program_seq+10,
2675 critical=>'Y' ,
2676 number_of_copies =>0,
2677 save_output =>'Y',
2678 style=>null,
2679 printer=>null);
2680
2681
2682 fnd_set.PROGRAM_PARAMETER(
2683 PROGRAM=>g_bsc_loader_dim_program,
2684 PROGRAM_APPLICATION=>'BSC',
2685 REQUEST_SET=>upper(p_setname),
2686 SET_APPLICATION=>p_setapp,
2687 STAGE=>'Stage_'||to_char(l_dim_stage_number),
2688 PROGRAM_SEQUENCE=>l_max_program_seq+10,
2689 PARAMETER=>'x_indicators',
2690 DISPLAY=>'Y',
2691 MODIFY=> 'Y' ,
2692 SHARED_PARAMETER=>null ,
2693 DEFAULT_TYPE=>'Constant',
2694 DEFAULT_VALUE=>l_object_rec.object_name
2695 );
2696
2697 commit;
2698
2699 end loop;
2700
2701 end;
2702
2703 /*
2704 * Overloading wrapup api to support Enh#4418520-aguwalan
2705 */
2706 PROCEDURE wrapup( p_setname IN VARCHAR2,
2707 p_setapp IN VARCHAR2,
2708 p_option IN VARCHAR2,
2709 p_analyze_table IN VARCHAR2,
2710 p_refresh_mode IN VARCHAR2,
2711 p_force_full_refresh IN VARCHAR2,
2712 p_alert_flag IN VARCHAR2) IS
2713 BEGIN
2714 wrapup(p_setname, p_setapp, p_option, p_analyze_table, p_refresh_mode, p_force_full_refresh, p_alert_flag, 'Y');
2715 END;
2716
2717 /**
2718 The wrapup performs the following activities
2719 (1) add stages for analyzing MV and other objects
2720 (2) add stages for MV log management
2721 (3) Add stages: first stage---Update object implementation flag
2722 last stage---MV dummy refresh programs
2723 Alerting
2724 (4) remove empty stages
2725 It is called from UI
2726 **/
2727 procedure wrapup( p_setname in varchar2,
2728 p_setapp in varchar2,
2729 p_option in varchar2,
2730 p_analyze_table in varchar2,
2731 p_refresh_mode in varchar2,
2732 p_force_full_refresh in varchar2,
2733 p_alert_flag in varchar2,
2734 p_rsg_history_flag in varchar2) is
2735
2736
2737 cursor c_stages is
2738 select
2739 a.application_id set_app_id ,
2740 a.request_set_id set_id ,
2741 a.request_set_name set_name,
2742 b.application_short_name set_app,
2743 c.REQUEST_SET_STAGE_ID stage_id,
2744 c.STAGE_NAME stage_name,
2745 c.display_sequence
2746 from
2747 fnd_request_sets a,
2748 fnd_application b,
2749 fnd_request_set_stages c
2750 where
2751 a.application_id=b.application_id
2752 and b.application_short_name=p_setapp
2753 and a.application_id=c.SET_APPLICATION_ID
2754 and a.request_set_id=c.REQUEST_SET_ID
2755 and a.request_set_name=upper(p_setname)
2756 order by c.display_sequence;
2757
2758 cursor c_max_stage is
2759 select
2760 max(b.display_sequence)
2761 from
2762 fnd_request_sets a,
2763 fnd_request_set_stages b,
2764 fnd_application c
2765 where a.request_set_id=b.request_set_id
2766 and a.application_id=b.set_application_id
2770
2767 and a.application_id=c.application_id
2768 and c.application_short_name=p_setapp
2769 and a.request_set_name=upper(p_setname);
2771 cursor c_min_stage is
2772 select
2773 min(b.display_sequence)
2774 from
2775 fnd_request_sets a,
2776 fnd_request_set_stages b,
2777 fnd_application c
2778 where a.request_set_id=b.request_set_id
2779 and a.application_id=b.set_application_id
2780 and a.application_id=c.application_id
2781 and c.application_short_name=p_setapp
2782 and a.request_set_name=upper(p_setname);
2783
2784
2785 l_stage_rec c_stages%rowtype;
2786
2787 l_max_stage number;
2788 l_min_stage number;
2789
2790
2791 ----For fixing bug 3647514. Store objects in set into global temp table
2792 ---BIS_BIA_RSG_STAGE_OBJECTS
2793 ----join to BIS_BIA_RSG_STAGE_OBJECTS when retrieve objects to analyze
2794 ---This cursor should also fetch those MVs that are refreshed by
2795 ----RSG generic MV refresh program
2796 cursor c_stage_objects(p_set_id number,p_set_app_id number,p_stage_id number,p_set_name varchar2,p_set_app varchar2) is
2797 select distinct
2798 c.object_type,
2799 c.object_name
2800 from
2801 fnd_request_set_programs a,
2802 fnd_concurrent_programs b,
2803 bis_obj_prog_linkages c,
2804 BIS_BIA_RSG_STAGE_OBJECTS d
2805 where a.set_application_id=p_set_app_id
2806 and a.request_set_id=p_set_id
2807 and a.request_set_stage_id=p_stage_id
2808 and a.program_application_id=b.application_id
2809 and a.concurrent_program_id=b.concurrent_program_id
2810 and b.application_id=c.CONC_APP_ID
2811 and b.concurrent_program_name=c.CONC_PROGRAM_NAME
2812 and c.enabled_flag='Y'
2813 and c.object_type=d.object_type
2814 and c.object_name=d.object_name
2815 and d.set_name=p_set_name
2816 and d.set_app=p_set_app
2817 union
2818 select distinct
2819 'MV' object_type,
2820 c.default_value object_name
2821 from
2822 fnd_request_set_programs a,
2823 fnd_request_set_program_args c
2824 where a.set_application_id=p_set_app_id
2825 and a.request_set_id=p_set_id
2826 and a.request_set_stage_id=p_stage_id
2827 and a.request_set_id=c.request_set_id
2828 and a.set_application_id=c.application_id
2829 and a.request_set_program_id=c.request_set_program_id
2830 and c.descriptive_flexfield_name='$SRS$.BIS_MV_REFRESH'
2831 and c.default_type=g_parameter_default_type
2832 and c.application_column_name='ATTRIBUTE2';
2833
2834
2835 cursor c_custom_api (p_set_id number,p_set_app_id number,p_stage_id number,p_obj_name varchar2,p_obj_type varchar2)
2836 is
2837 select
2838 distinct d.CUSTOM_API custom_api
2839 from
2840 fnd_request_set_programs a,
2841 fnd_concurrent_programs b,
2842 bis_obj_prog_linkages c,
2843 bis_obj_properties d
2844 where a.set_application_id=p_set_app_id
2845 and a.request_set_id=p_set_id
2846 and a.request_set_stage_id=p_stage_id
2847 and a.program_application_id=b.application_id
2848 and a.concurrent_program_id=b.concurrent_program_id
2849 and b.application_id=c.CONC_APP_ID
2850 and b.concurrent_program_name=c.CONC_PROGRAM_NAME
2851 and c.enabled_flag='Y'
2852 and c.refresh_mode in ('INIT','INIT_INCR')-----?? can we use INIT_INCR here
2853 and c.object_type=d.object_type
2854 and c.object_name=d.object_name
2855 and d.object_type=p_obj_type
2856 and d.object_name=p_obj_name;
2857
2858
2859 l_dummy varchar2(1);
2860 l_stage_object_rec c_stage_objects%rowtype;
2861 l_counter integer;
2862 l_mv_stage_number number;
2863 l_snp_drop_stage_number number;
2864 l_snp_create_stage_number number;
2865 l_max_program_seq number;
2866 l_reset_stage_number number;
2867 l_log_name varchar2(30);
2868 l_object_owner varchar2(30);
2869 l_custom_api varchar2(80);
2870
2871 begin
2872 g_set_application:='BIS';
2873 g_fnd_stats:='BIS_BIA_STATS_TABLE';
2874 g_fnd_stats_app:='BIS';
2875 g_parameter_default_type:='C';
2876
2877 g_create_snpl:='BIS_BIA_RSG_LOG_MGMNT';
2878 g_create_snpl_app:='BIS';
2879
2880 ----Added for enhancement 3549337. Request set for analyzing programs only
2881 if p_refresh_mode is null and p_analyze_table='Y' then
2882
2883 analyze_objects_in_set(
2884 p_setname,
2885 p_setapp );
2886
2887
2888 open c_max_stage;
2889 fetch c_max_stage into l_max_stage;
2890 close c_max_stage;
2891
2892
2893 -- Add the last stage as the RSG Report History collection program Enh#3473874 aguwalan
2894 -- this case handles Request set for analyzing programs only
2895
2896 -- Enh#4418520-aguwalan
2897 add_link_history_stage(p_setname, p_setapp, l_max_stage, p_rsg_history_flag);
2898
2899 remove_empty_stages(p_setname,
2900 p_setapp);
2901
2902 create_rs_option(upper(p_setname), p_setapp,
2903 p_refresh_mode, p_analyze_table,p_force_full_refresh, p_alert_flag, p_rsg_history_flag);
2904
2905 return;
2906 end if;
2907
2908
2909 for l_stage_rec in c_stages loop
2910 l_counter:=0;
2911 l_mv_stage_number:=to_number(substr(l_stage_rec.stage_name,7))+50;
2912 l_snp_drop_stage_number:=to_number(substr(l_stage_rec.stage_name,7))-10;
2913 l_snp_create_stage_number:=to_number(substr(l_stage_rec.stage_name,7))+10;
2914
2918 ----adding one stage for analyzing objects after current stage
2915 for l_stage_object_rec in c_stage_objects(l_stage_rec.set_id,l_stage_rec.set_app_id,l_stage_rec.stage_id,l_stage_rec.set_name,l_stage_rec.set_app) loop
2916 l_counter:=l_counter+1;
2917 if l_counter=1 then
2919 if p_analyze_table='Y' then
2920 fnd_set.add_stage
2921 (name=>g_stage_prompt||' '||to_char(l_mv_stage_number),
2922 request_set=>upper(p_setname),
2923 set_application=>p_setapp,
2924 short_name=>'Stage_'||to_char(l_mv_stage_number),
2925 description=>null,
2926 display_sequence=>l_mv_stage_number,
2927 function_short_name=>'FNDRSSTE',
2928 function_application=>'FND',
2929 critical=>'N',
2930 incompatibilities_allowed=>'N',
2931 start_stage=>'N',
2932 language_code=>'US');
2933 commit;
2934 end if;---end if analyze table='Y'
2935 -------adding stages for dropping/creating snapshot logs for tables
2936 if p_refresh_mode='INIT' then
2937 fnd_set.add_stage
2938 (name=>g_stage_prompt||' '||to_char(l_snp_drop_stage_number),
2939 request_set=>upper(p_setname),
2940 set_application=>p_setapp,
2941 short_name=>'Stage_'||to_char(l_snp_drop_stage_number),
2942 description=>null,
2943 display_sequence=>l_snp_drop_stage_number,
2944 function_short_name=>'FNDRSSTE',
2945 function_application=>'FND',
2946 critical=>'N',
2947 incompatibilities_allowed=>'N',
2948 start_stage=>'N',
2949 language_code=>'US');
2950 commit;
2951
2952 fnd_set.add_stage
2953 (name=>g_stage_prompt||' '||to_char(l_snp_create_stage_number),
2954 request_set=>upper(p_setname),
2955 set_application=>p_setapp,
2956 short_name=>'Stage_'||to_char(l_snp_create_stage_number),
2957 description=>null,
2958 display_sequence=>l_snp_create_stage_number,
2959 function_short_name=>'FNDRSSTE',
2960 function_application=>'FND',
2961 critical=>'N',
2962 incompatibilities_allowed=>'N',
2963 start_stage=>'N',
2964 language_code=>'US');
2965 commit;
2966 end if;-----end if refresh mode ='INIT'
2967 end if; ---end if counter=1
2968
2969
2970 --------call analyze object for each MV , table and log
2971 if p_analyze_table='Y' and (l_stage_object_rec.object_type='MV' or l_stage_object_rec.object_type='TABLE') then
2972 -- ----dbms_output.put_Line('debug point 1');
2973 -- ----dbms_output.put_Line('object name +type'||l_stage_object_rec.object_name||'+'||l_stage_object_rec.object_type);
2974
2975 l_max_program_seq:=get_max_prog_sequence(upper(p_setname),
2976 p_setapp,
2977 'Stage_'||to_char(l_mv_stage_number));
2978 if l_max_program_seq is null then
2979 l_max_program_seq:=0;
2980 end if;
2981 begin
2982 fnd_set.add_program
2983 (program =>g_fnd_stats ,
2984 program_application=>g_fnd_stats_app,
2985 request_set=>upper(p_setname) ,
2986 set_application=>p_setapp ,
2987 stage=>'Stage_'||to_char(l_mv_stage_number),
2988 program_sequence=>l_max_program_seq+10,
2989 critical=>'Y' ,
2990 number_of_copies =>0,
2991 save_output =>'Y',
2992 style=>null,
2993 printer=>null);
2994 commit;
2995 -- ----dbms_output.put_Line('debug point 2');
2996 -------register parameters for STATS program for the current object
2997 fnd_set.PROGRAM_PARAMETER(
2998 PROGRAM=>g_fnd_stats,
2999 PROGRAM_APPLICATION=>g_fnd_stats_app,
3000 REQUEST_SET=>upper(p_setname),
3001 SET_APPLICATION=>p_setapp,
3002 STAGE=>'Stage_'||to_char(l_mv_stage_number),
3003 PROGRAM_SEQUENCE=>l_max_program_seq+10,
3004 PARAMETER=>'Object Type',
3005 DISPLAY=>'Y',
3006 MODIFY=> 'Y' ,
3007 SHARED_PARAMETER=>null ,
3008 DEFAULT_TYPE=>'Constant',
3009 DEFAULT_VALUE=>l_stage_object_rec.object_type
3010 );
3011 -------dbms_output.put_Line('debug point 3');
3012 fnd_set.PROGRAM_PARAMETER(
3013 PROGRAM=>g_fnd_stats,
3014 PROGRAM_APPLICATION=>g_fnd_stats_app,
3015 REQUEST_SET=>upper(p_setname),
3016 SET_APPLICATION=>p_setapp,
3017 STAGE=>'Stage_'||to_char(l_mv_stage_number),
3018 PROGRAM_SEQUENCE=>l_max_program_seq+10,
3019 PARAMETER=>'Object Name',
3020 DISPLAY=>'Y',
3021 MODIFY=> 'Y' ,
3022 SHARED_PARAMETER=>null ,
3023 DEFAULT_TYPE=>'Constant',
3024 DEFAULT_VALUE=>l_stage_object_rec.object_name
3025 );
3026 commit;
3027 --------dbms_output.put_Line('debug point 4');
3028 end;
3029
3030 end if;---end if object type ='MV' or 'TABLE'
3031
3032
3036 ----so that table MV log management is in sync with MV log management for MVs
3033 -------Add MV log management programs (drop/create) for tables that have custom api defined
3034 ------and its initial loading program is pulled into the request set
3035 ----in BIA 4.0.8, we add one more condition p_force_full_refresh='Y'
3037 ---i.e only manage MV logs in clear and initial load
3038 l_custom_api:=null;
3039 -- --dbms_output.put_Line('object name: '||l_stage_object_rec.object_name);
3040 -- --dbms_output.put_Line('object type: '||l_stage_object_rec.object_type);
3041 if l_stage_object_rec.object_type='TABLE' and p_refresh_mode='INIT' and p_force_full_refresh='Y' then
3042 open c_custom_api (l_stage_rec.set_id,l_stage_rec.set_app_id,l_stage_rec.stage_id,l_stage_object_rec.object_name,l_stage_object_rec.object_type);
3043 fetch c_custom_api into l_custom_api;
3044 close c_custom_api;
3045 ------dbms_output.put_Line('custom api: '||l_custom_api);
3046 if l_custom_api is not null then
3047 add_mv_log_mgt_programs(p_setname ,
3048 p_setapp ,
3049 l_snp_drop_stage_number,
3050 l_stage_object_rec.object_type,
3051 l_stage_object_rec.object_name,
3052 l_custom_api ,
3053 'BEFORE',
3054 g_create_snpl,
3055 g_create_snpl_app);
3056 -----dbms_output.put_Line('added drop log programs');
3057
3058 add_mv_log_mgt_programs(p_setname ,
3059 p_setapp ,
3060 l_snp_create_stage_number,
3061 l_stage_object_rec.object_type,
3062 l_stage_object_rec.object_name,
3063 l_custom_api ,
3064 'AFTER',
3065 g_create_snpl,
3066 g_create_snpl_app);
3067
3068 ---- --dbms_output.put_Line('added create log programs');
3069
3070 end if ;----l_custom_api is not null
3071 end if ;-----object_type='TABLE' and p_refresh_mode='INIT' and p_force_full_refresh='Y'
3072
3073 end loop;---end loop for objects in the stage
3074
3075 ----add this API call for enhancement 3999465 and 4251030
3076 add_other_loader_programs(p_setname ,
3077 p_setapp,
3078 p_refresh_mode ,
3079 p_force_full_refresh ,
3080 l_stage_rec.set_id ,
3081 l_stage_rec.set_app_id,
3082 l_stage_rec.stage_id ,
3083 l_stage_rec.stage_name );
3084
3085 end loop;----end loop for stages in the set
3086
3087 -----add First and last stages for MV dummy refresh
3088 open c_max_stage;
3089 fetch c_max_stage into l_max_stage;
3090 close c_max_stage;
3091
3092 open c_min_stage;
3093 fetch c_min_stage into l_min_stage;
3094 close c_min_stage;
3095
3096 --------dbms_output.put_Line('before calling add_first_last');
3097 if p_refresh_mode in ('INIT','INCR') then
3098 -- Adds the last Report History Collection Stage also Enh#3473874
3099 -- Enh#4418520-aguwalan
3100 add_first_last_stages(p_setname,p_setapp,l_max_stage,l_min_stage, p_rsg_history_flag);
3101 end if;
3102 -------dbms_output.put_Line('after calling add_first_last');
3103
3104 ------The last step is to cleanup empty stages
3105 remove_empty_stages(p_setname,
3106 p_setapp);
3107
3108
3109
3110 /* changes for 'view request set history': insert record into
3111 bis_request_set_options. */
3112 create_rs_option(upper(p_setname), p_setapp,
3113 p_refresh_mode, p_analyze_table,p_force_full_refresh, p_alert_flag, p_rsg_history_flag);
3114
3115
3116 end; ---end wrapup;
3117
3118 -- Enh#4418520-aguwalan; Adding another option for RSG History Collection
3119 procedure create_rs_option(p_set_name in varchar2, p_set_app in varchar2,
3120 p_refresh_mode in varchar2, p_analyze_table in varchar2,p_force_full in varchar2,
3121 p_alert_flag in varchar2, p_rsg_history_flag in VARCHAR2) is
3122 l_stmt VARCHAR2 (2000);
3123 l_set_app_id number;
3124
3125 begin
3126 -- perform
3127 g_current_user_id := FND_GLOBAL.User_id;
3128 g_current_login_id := FND_GLOBAL.Login_id;
3129
3130 select APPLICATION_ID into l_set_app_id from fnd_application where
3131 application_short_name=p_set_app;
3132 l_stmt := 'insert into bis_request_set_options(request_set_name, set_app_id,
3133 option_name,option_value, CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
3134 LAST_UPDATE_LOGIN,LAST_UPDATE_DATE) values
3135 (:1,:2,:3,:4,:5,:6,:7,:8,:9)';
3136 EXECUTE IMMEDIATE l_stmt USING p_set_name, l_set_app_id,'REFRESH_MODE',p_refresh_mode,
3137 g_current_user_id, sysdate,g_current_user_id,
3138 g_current_login_id, sysdate ;
3139 commit;
3140
3141 EXECUTE IMMEDIATE l_stmt USING p_set_name, l_set_app_id,'ANALYZE_OBJECT',p_analyze_table,
3142 g_current_user_id, sysdate,g_current_user_id,
3143 g_current_login_id, sysdate ;
3144 commit;
3145
3146 EXECUTE IMMEDIATE l_stmt USING p_set_name, l_set_app_id,'FORCE_FULL',p_force_full,
3147 g_current_user_id, sysdate,g_current_user_id,
3151 EXECUTE IMMEDIATE l_stmt USING p_set_name, l_set_app_id,'ALERT_FLAG',p_alert_flag,
3148 g_current_login_id, sysdate ;
3149 commit;
3150
3152 g_current_user_id, sysdate,g_current_user_id,
3153 g_current_login_id, sysdate ;
3154 commit;
3155
3156 -- Enh#4418520-aguwalan
3157 EXECUTE IMMEDIATE l_stmt USING p_set_name, l_set_app_id,'HISTORY_COLLECT',p_rsg_history_flag,
3158 g_current_user_id, sysdate, g_current_user_id, g_current_login_id, sysdate ;
3159 commit;
3160
3161 end create_rs_option ;
3162
3163 procedure create_rs_objects(p_set_name in varchar2, p_set_app in varchar2,
3164 p_object_type in varchar2, p_object_name in varchar2, p_object_owner in
3165 varchar2) is
3166 l_stmt VARCHAR2 (2000);
3167 l_object_owner varchar2(50):=null;
3168 l_set_app_id number;
3169 cursor c_owner is select distinct OBJECT_OWNER from bis_obj_dependency
3170 where object_type=p_object_type and object_name=p_object_name;
3171 l_module varchar2(300) := 'bis.BIS_CREATE_REQUESTSET.create_rs_objects';
3172 l_func1 varchar2(300);
3173 l_func2 varchar2(300);
3174 l_cursor_id integer;
3175 l_rows integer;
3176
3177 begin
3178 log(l_module, 'Inside ' || l_module );
3179 g_current_user_id := FND_GLOBAL.User_id;
3180 g_current_login_id := FND_GLOBAL.Login_id;
3181
3182 -- bug#3426783.
3183 -- added to strip _OA on the fly, if the page name has been migrated
3184 IF(p_object_type = 'PAGE') THEN
3185 l_stmt := 'BEGIN :function_name := bis_impl_dev_pkg.get_function_by_page(:page_name); END;';
3186 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
3187 DBMS_SQL.parse(l_cursor_id,l_stmt,DBMS_SQL.V7);
3188 DBMS_SQL.bind_variable(l_cursor_id,'page_name',p_object_name, 300);
3189 DBMS_SQL.bind_variable(l_cursor_id,'function_name',l_func1, 300);
3190 l_rows:=DBMS_SQL.execute(l_cursor_id);
3191 DBMS_SQL.variable_value(l_cursor_id,'function_name',l_func1);
3192 DBMS_SQL.close_cursor(l_cursor_id);
3193 log(l_module, 'Function name for ' || p_object_name || ' : ' || l_func1);
3194
3195 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
3196 DBMS_SQL.parse(l_cursor_id,l_stmt,DBMS_SQL.V7);
3197 DBMS_SQL.bind_variable(l_cursor_id,'page_name',p_object_name, 300);
3198 DBMS_SQL.bind_variable(l_cursor_id,'function_name', l_func2 || '_OA', 300);
3199 l_rows:=DBMS_SQL.execute(l_cursor_id);
3200 DBMS_SQL.variable_value(l_cursor_id,'function_name',l_func2);
3201 DBMS_SQL.close_cursor(l_cursor_id);
3202 log(l_module, 'Function name for ' || p_object_name || '_OA' || ' : ' || l_func2);
3203
3204 IF( p_object_name = l_func1 AND
3205 l_func1 = l_func2 ) THEN
3206 log(l_module, 'Migrating ' || p_object_name || '_OA' || ' to '|| p_object_name);
3207 l_stmt :='
3208 UPDATE bis_request_set_objects
3209 set object_name = :1
3210 where object_name = :2
3211 and object_type = ''PAGE''
3212 ';
3213 EXECUTE IMMEDIATE l_stmt USING p_object_name, p_object_name||'_OA';
3214
3215 log(l_module, 'Migrated ' || SQL%ROWCOUNT || ' rows');
3216
3217 END IF;
3218 END IF;
3219
3220 --------dbms_output.put_Line('within create rs objects');
3221 select APPLICATION_ID into l_set_app_id from fnd_application where
3222 application_short_name=p_set_app;
3223 if (p_object_owner is null) then
3224 open c_owner;
3225 fetch c_owner into l_object_owner;
3226 close c_owner;
3227 else l_object_owner:=p_object_owner;
3228 end if;
3229 l_stmt := 'insert into bis_request_set_objects(request_set_name, set_app_id,
3230 object_type,object_name, object_owner, CREATED_BY,
3231 CREATION_DATE,LAST_UPDATED_BY,
3232 LAST_UPDATE_LOGIN,LAST_UPDATE_DATE) values
3233 (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)';
3234 --------dbms_output.put_Line('before insert');
3235 EXECUTE IMMEDIATE l_stmt USING p_set_name, l_set_app_id,p_object_type,
3236 p_object_name, l_object_owner,
3237 g_current_user_id, sysdate,g_current_user_id,
3238 g_current_login_id, sysdate ;
3239 --- ----dbms_output.put_Line('after insert');
3240 commit;
3241 EXCEPTION WHEN OTHERS THEN
3242 log(l_module, sqlerrm);
3243 --- ----dbms_output.put_Line('end of create rs objects');
3244 end create_rs_objects;
3245
3246 procedure delete_rs_objects(p_set_name in varchar2, p_set_app in varchar2) is
3247 l_stmt VARCHAR2 (2000);
3248 l_set_app_id number;
3249 begin
3250 ---- ----dbms_output.put_Line('within delete rs objects');
3251 select APPLICATION_ID into l_set_app_id from fnd_application where
3252 application_short_name=p_set_app;
3253 l_stmt := 'delete bis_request_set_objects where request_set_name=:a and
3254 set_app_id=:b';
3255 EXECUTE IMMEDIATE l_stmt USING p_set_name, l_set_app_id;
3256 commit;
3257 --------dbms_output.put_Line('end of delete rs objects');
3258 end delete_rs_objects;
3259
3260 procedure delete_rs_option(p_set_name in varchar2, p_set_app in varchar2) is
3261 l_stmt VARCHAR2 (2000);
3262 l_set_app_id number;
3263 begin
3264 -- ----dbms_output.put_Line('within delete rs options');
3265 select APPLICATION_ID into l_set_app_id from fnd_application where
3266 application_short_name=p_set_app;
3267 l_stmt := 'delete bis_request_set_options where request_set_name=:a and
3268 set_app_id=:b';
3269 EXECUTE IMMEDIATE l_stmt USING p_set_name, l_set_app_id;
3270 COMMIT;
3271 --------dbms_output.put_Line('end of delete rs options');
3272 end delete_rs_option;
3276 is
3273
3274
3275 function object_has_data(p_object_name in varchar2, p_object_type in varchar2,p_object_owner in varchar2) return varchar2
3277 l_sql varchar2(2000);
3278 l_count number;
3279 l_owner varchar2(30);
3280 l_timestamp date;
3281 l_module varchar2(300) := 'bis.BIS_CREATE_REQUESTSET.object_has_data';
3282
3283 begin
3284 ---First check if the object physically exists
3285 l_owner:=get_object_owner(p_object_name,p_object_type);
3286 log(l_module, 'Owner ' || l_owner);
3287
3288 l_timestamp := sysdate;
3289 if nvl(l_owner,'NOTFOUND')<>'NOTFOUND' then
3290 l_sql:='select /*+ FIRST_ROWS */ 1 from '||l_owner||'.'||p_object_name||' where rownum=1';
3291 log(l_module, 'Executing ' || l_sql);
3292 --- ----dbms_output.put_Line('before execute :'||l_sql);
3293 execute immediate l_sql into l_count;
3294 ---- ----dbms_output.put_Line('after execute :'||l_sql);
3295 log(l_module, duration(sysdate - l_timestamp));
3296
3297 if l_count=1 then
3298 return 'Y';
3299 else
3300 return 'N';
3301 end if;
3302 else
3303 return 'N';
3304 end if ; ---l_owner<>'NOTFOUND'
3305 exception
3306 when no_data_found then
3307 return 'N';
3308 when others then
3309 raise;
3310 end;
3311
3312
3313 procedure add_first_last_stages(p_set_name in varchar2,p_set_app in varchar2,p_max_stage in number,p_min_stage in number,
3314 p_rsg_history_flag in varchar2) is
3315
3316 begin
3317 --- ----dbms_output.put_Line('min stage :'||p_min_stage);
3318 --------dbms_output.put_Line('max stage :'||p_max_stage);
3319 if p_min_stage>0 then
3320 --Add Update object implementation flag program at the begining of the request set
3321 fnd_set.add_stage
3322 (name=>g_stage_prompt||' '||to_char(p_min_stage-50),
3323 request_set=>upper(p_set_name),
3324 set_application=>p_set_app,
3325 short_name=>'Stage_'||to_char(p_min_stage-50),
3326 description=>null,
3327 display_sequence=>p_min_stage-50,
3328 function_short_name=>'FNDRSSTE',
3329 function_application=>'FND',
3330 critical=>'N',
3331 incompatibilities_allowed=>'N',
3332 start_stage=>'N',
3333 language_code=>'US');
3334
3335 commit;
3336 -------dbms_output.put_Line('added stage: '||'Stage_'||to_char(p_min_stage-50));
3337
3338 fnd_set.add_program
3339 (program =>'BIS_RSG_PREP' ,
3340 program_application=>'BIS',
3341 request_set=>upper(p_set_name) ,
3342 set_application=>p_set_app ,
3343 stage=>'Stage_'||to_char(p_min_stage-50),
3344 program_sequence=>10,
3345 critical=>'Y' ,
3346 number_of_copies =>0,
3347 save_output =>'Y',
3348 style=>null,
3349 printer=>null);
3350 commit;
3351
3352 fnd_set.PROGRAM_PARAMETER(
3353 PROGRAM=>'BIS_RSG_PREP',
3354 PROGRAM_APPLICATION=>'BIS',
3355 REQUEST_SET=>upper(p_set_name),
3356 SET_APPLICATION=>p_set_app,
3357 STAGE=>'Stage_'||to_char(p_min_stage-50),
3358 PROGRAM_SEQUENCE=>10,
3359 PARAMETER=>'Request Set Code',
3360 DISPLAY=>'Y',
3361 MODIFY=> 'Y' ,
3362 SHARED_PARAMETER=>null ,
3363 DEFAULT_TYPE=>'Constant',
3364 DEFAULT_VALUE=>upper(p_set_name)
3365 );
3366
3367 end if;
3368
3369 if p_max_stage >0 then
3370 ---Add MV dummy refresh program at the end of the request set
3371 fnd_set.add_stage
3372 (name=>g_stage_prompt||' '||to_char(p_max_stage+50),
3373 request_set=>upper(p_set_name),
3374 set_application=>p_set_app,
3375 short_name=>'Stage_'||to_char(p_max_stage+50),
3376 description=>null,
3377 display_sequence=>p_max_stage+50,
3378 function_short_name=>'FNDRSSTE',
3379 function_application=>'FND',
3380 critical=>'N',
3381 incompatibilities_allowed=>'N',
3382 start_stage=>'N',
3383 language_code=>'US');
3384
3385 commit;
3386 -------dbms_output.put_Line('added stage: '||'Stage_'||to_char(p_max_stage+50));
3387
3388 fnd_set.add_program
3389 (program =>'BIS_RSG_FINAL' ,
3390 program_application=>'BIS',
3391 request_set=>upper(p_set_name) ,
3392 set_application=>p_set_app ,
3393 stage=>'Stage_'||to_char(p_max_stage+50),
3394 program_sequence=>10,
3395 critical=>'Y' ,
3396 number_of_copies =>0,
3397 save_output =>'Y',
3398 style=>null,
3399 printer=>null);
3400
3401 fnd_set.PROGRAM_PARAMETER(
3402 PROGRAM=>'BIS_RSG_FINAL',
3403 PROGRAM_APPLICATION=>'BIS',
3404 REQUEST_SET=>upper(p_set_name),
3405 SET_APPLICATION=>p_set_app,
3406 STAGE=>'Stage_'||to_char(p_max_stage+50),
3407 PROGRAM_SEQUENCE=>10,
3408 PARAMETER=>'Request Set Code',
3409 DISPLAY=>'Y',
3410 MODIFY=> 'Y' ,
3414 );
3411 SHARED_PARAMETER=>null ,
3412 DEFAULT_TYPE=>'Constant',
3413 DEFAULT_VALUE=>upper(p_set_name)
3415 commit;
3416 -------dbms_output.put_Line('added program BIS_MV_DUMMY_REFRESH');
3417 -- Add the last stage as the RSG Reporting program Enh#3473874 aguwalan
3418 -- Enh#4418520-aguwalan
3419 add_link_history_stage(p_set_name, p_set_app, p_max_stage+100, p_rsg_history_flag);
3420 commit;
3421 end if;
3422
3423 end add_first_last_stages;
3424
3425 function is_req_alive(p_req_id IN NUMBER)
3426 return boolean IS
3427 l_req_id number;
3428 l_call_Status boolean;
3429 l_phase varchar2(200);
3430 l_status varchar2(200);
3431 l_req_phase varchar2(200);
3432 l_dev_status varchar2(200);
3433 l_message varchar2(200);
3434
3435 begin
3436 -- := FND_GLOBAL.conc_request_id
3437 l_req_id := p_req_id;
3438 -- make it runnable under sql-plus, as the current req-id will be
3439 -- set to -1.
3440 if l_req_id = -1 THEN
3441 BIS_COLLECTION_UTILITIES.debug(' Req#' || l_req_id || ', can not moniter the status!' );
3442 return TRUE;
3443 end if;
3444
3445 l_call_status := FND_CONCURRENT.get_request_status(l_req_id , null, null,
3446 l_phase, l_status, l_req_phase, l_dev_status, l_message);
3447 if (NOT l_call_status ) then
3448 RAISE_APPLICATION_ERROR (-20000,'Error happened in request: ' || l_req_id);
3449 end if;
3450
3451 if (l_req_phase is null ) THEN
3452 BIS_COLLECTION_UTILITIES.debug(' Req#' || l_req_id || ' not exists' );
3453 return FALSE;
3454 elsif (l_req_phase='COMPLETE' ) THEN
3455 BIS_COLLECTION_UTILITIES.debug(' Req#' || l_req_id || ' is completed!' );
3456 IF (l_status = 'Error' OR l_dev_status = 'ERROR') THEN
3457 RAISE_APPLICATION_ERROR (-20000,'Error happened in request: ' || l_req_id);
3458 ELSIF (l_status = 'Terminated' OR l_dev_status = 'TERMINATED') THEN
3459 l_call_status := fnd_concurrent.set_completion_status('TERMINATED' ,NULL);
3460 RAISE_APPLICATION_ERROR (-20000,'request: ' || l_req_id || ' was terminated!');
3461 ELSIF (l_status = 'Warning' OR l_dev_status = 'WARNING') THEN
3462 l_call_status := fnd_concurrent.set_completion_status('WARNING' ,NULL);
3463 return FALSE;
3464 ELSE
3465 return FALSE;
3466 END IF;
3467 else
3468 BIS_COLLECTION_UTILITIES.debug(' Req#' || l_req_id || ' is running!' );
3469 return TRUE;
3470 end if;
3471 end;
3472
3473 FUNCTION isSubmitAlert(pReqCode VARCHAR2) RETURN BOOLEAN
3474 IS
3475 CURSOR C_ALERT_FLAG ( pReqCode bis_request_set_options.OPTION_NAME%type )
3476 IS
3477 select NVL(OPTION_VALUE, 'N')
3478 from bis_request_set_options
3479 where request_set_name = pReqCode
3480 and OPTION_NAME = 'ALERT_FLAG';
3481 l_flag VARCHAR2(10) := NULL;
3482 BEGIN
3483
3484 OPEN C_ALERT_FLAG(pReqCode);
3485 fetch C_ALERT_FLAG into l_flag;
3486 CLOSE C_ALERT_FLAG;
3487 IF( NVL(l_flag, 'N') = 'Y' ) THEN
3488 BIS_COLLECTION_UTILITIES.put_line( pReqCode || ' is flagged to submit alert!');
3489 RETURN TRUE;
3490 ELSE
3491 BIS_COLLECTION_UTILITIES.put_line( pReqCode || ' is not flagged to submit alert!');
3492 RETURN FALSE;
3493 END IF;
3494 END;
3495
3496 /**
3497 procedure waitForCQComplete(
3498 p_cp_Short_name IN VARCHAR,
3499 p_request_id IN NUMBER
3500 ) is
3501 l_message VARCHAR2(500) := NULL;
3502 l_status INTEGER;
3503 l_alertname VARCHAR2(30) := NULL;
3504 begin
3505 l_alertname := SUBSTR(p_request_id || p_cp_Short_name, 1, 30);
3506 dbms_alert.register(l_alertname);
3507 LOOP
3508 --dbms_alert.waitone(l_alertname,l_message,l_status, 60*5);
3509 dbms_alert.waitone(l_alertname,l_message,l_status, 60*1);
3510 EXIT WHEN NOT ( l_status = 1 and is_req_alive(FND_GLOBAL.conc_request_id) and is_req_alive(p_request_id) );
3511 END LOOP;
3512 dbms_alert.remove(l_alertname);
3513 end;
3514 **/
3515
3516 procedure waitForRequest(
3517 p_request_id IN NUMBER
3518 )
3519 is
3520 begin
3521 commit;
3522 LOOP
3523 dbms_lock.sleep(10);
3524 BIS_COLLECTION_UTILITIES.put_line('inside the loop of waitforrequest');
3525 BIS_COLLECTION_UTILITIES.put_line('FND_GLOBAL.conc_request_id '||FND_GLOBAL.conc_request_id);
3526 BIS_COLLECTION_UTILITIES.put_line('p_request_id'||p_request_id);
3527 EXIT WHEN NOT ( is_req_alive(FND_GLOBAL.conc_request_id) and is_req_alive(p_request_id) );
3528 --EXIT WHEN NOT ( is_req_alive(p_request_id) );
3529 END LOOP;
3530
3531 end;
3532
3533 ---added for bug 4532066
3534 ---this procedure will print out the request sets which
3535 ---contain unimplemented dashboards/reports
3536 ---and set the Preparation program to warning status
3537 ---the user should re-generate these request sets
3538 procedure check_unimpl_objects_is_sets(p_request_set_code in varchar2) is
3539
3540 cursor c_unimpl_obj_in_set is
3541 select distinct a.object_type, bis_impl_dev_pkg.get_user_object_name(a.object_type,a.object_name) user_object_name
3542 from bis_request_set_objects a,
3546 and a.object_type=b.object_type
3543 bis_obj_properties b
3544 where a.request_set_name=p_request_set_code
3545 and a.set_app_id=191
3547 and a.object_name=b.object_name
3548 and b.implementation_flag='N';
3549
3550 l_obj_rec c_unimpl_obj_in_set%rowtype;
3551
3552
3553 cursor c_set_with_unimpl_obj is
3554 select distinct c.user_request_set_name from
3555 bis_request_set_objects a,
3556 bis_obj_properties b,
3557 fnd_request_sets_vl c
3558 where a.object_name=b.object_name
3559 and a.object_type=b.object_type
3560 and b.implementation_flag='N'
3561 and a.request_set_name=c.request_set_name
3562 and a.set_app_id=c.application_id;
3563
3564 l_counter number;
3565 l_set_rec c_set_with_unimpl_obj%rowtype;
3566 l_program_status boolean :=true;
3567
3568 begin
3569 l_counter:=0;
3570 BIS_COLLECTION_UTILITIES.put_line('The following dashboards/reports in this request set are not implemented.');
3571 BIS_COLLECTION_UTILITIES.put_line('Data can not be loaded properly for unimplemented dashboards/reports.');
3572 BIS_COLLECTION_UTILITIES.put_line('Please go to RSG UI to remove them by updating this request set.');
3573 BIS_COLLECTION_UTILITIES.put_line('-----------Start of the dashboards/reports list------');
3574 for l_obj_rec in c_unimpl_obj_in_set loop
3575 l_counter:=l_counter+1;
3576 BIS_COLLECTION_UTILITIES.put_line(l_obj_rec.object_type||' '||l_obj_rec.user_object_name);
3577 end loop;
3578 BIS_COLLECTION_UTILITIES.put_line('-----------End of the dashboards/reoprts list------');
3579
3580 if l_counter>0 then
3581 BIS_COLLECTION_UTILITIES.put_line('For your information only. Following is a list of request sets that contain unimplemented dashboards/reports. ');
3582 BIS_COLLECTION_UTILITIES.put_line('-----------Start of the request set list------');
3583 for l_set_rec in c_set_with_unimpl_obj loop
3584 BIS_COLLECTION_UTILITIES.put_line(l_set_rec.user_request_set_name);
3585 end loop;
3586 BIS_COLLECTION_UTILITIES.put_line('-----------End of the request set list------');
3587 l_program_status := fnd_concurrent.set_completion_status('WARNING' ,NULL);
3588 end if;
3589 end;
3590
3591
3592 procedure preparation_conc(
3593 errbuf OUT NOCOPY VARCHAR2,
3594 retcode OUT NOCOPY VARCHAR,
3595 p_request_set_code IN VARCHAR
3596 ) is
3597 l_request_id INTEGER;
3598 l_phase VARCHAR2(500) := NULL;
3599 --l_status VARCHAR2(500) := NULL;
3600 l_status INTEGER;
3601 l_devphase VARCHAR2(500) := NULL;
3602 l_devstatus VARCHAR2(500) := NULL;
3603 l_message VARCHAR2(500) := NULL;
3604 l_result BOOLEAN;
3605 l_stmt varchar2(20000);
3606 l_cursor_id integer;
3607 l_rows integer:=0;
3608 l_program_status boolean :=true;
3609
3610 cursor refresh_mode is
3611 select option_value
3612 from bis_request_set_options
3613 where request_set_name=p_request_set_code
3614 and SET_APP_ID=191
3615 and option_name='REFRESH_MODE';
3616
3617 cursor force_full_refresh is
3618 select option_value
3619 from bis_request_set_options
3620 where request_set_name=p_request_set_code
3621 and set_app_id=191
3622 and option_name='FORCE_FULL';
3623
3624 l_refresh_mode varchar2(30);
3625 l_force_full_refresh varchar2(30);
3626
3627 cursor get_req_set_details(p_req_id number) is
3628 select
3629 req.argument1,
3630 req.argument2
3631 from
3632 fnd_concurrent_requests req
3633 where
3634 req.request_id = p_req_id ;
3635
3636 CURSOR mv_log_truncate_running IS
3637 SELECT req.request_id REQUEST, req.phase_code Phase, requested_start_date s_date
3638 FROM fnd_concurrent_programs prog, fnd_concurrent_requests req
3639 WHERE prog.CONCURRENT_PROGRAM_NAME = 'BIS_BIA_TRUNCATE_EMPTY_MV_LOGS'
3640 AND req.concurrent_program_id = prog.concurrent_program_id
3641 AND req.program_application_id = prog.application_id
3642 AND req.phase_code = 'R';
3643
3644 l_req_set_appl_id number;
3645 l_req_set_id number;
3646
3647 begin
3648 BIS_COLLECTION_UTILITIES.put_line('Checking if Empty MV Log Truncation is running ...');
3649 FOR mv_log_truncate_running_rec IN mv_log_truncate_running LOOP
3650 EXIT WHEN mv_log_truncate_running%NOTFOUND;
3651 BIS_COLLECTION_UTILITIES.put_line(' - Req. Id:'||mv_log_truncate_running_rec.REQUEST||' Phase:'|| mv_log_truncate_running_rec.Phase || ' Started:' ||mv_log_truncate_running_rec.s_Date);
3652 RAISE_APPLICATION_ERROR (-20000,'Empty MV Log Truncation Program Running');
3653 RETURN;
3654 END LOOP;
3655 IF (Not BIS_COLLECTION_UTILITIES.setup('preparation_conc')) THEN
3656 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || sqlerrm);
3657 return;
3658 END IF;
3659 fnd_profile.put ('CONC_SINGLE_THREAD','N');
3660
3661 -- Enh#4418520-aguwalan
3662 IF(is_history_collect_on(p_request_set_code, 191)) THEN
3663 --Add entry of this request set in table BIS_RS_RUN_HISTORY Enh#3473874 aguwalan
3664 open get_req_set_details(FND_GLOBAL.CONC_PRIORITY_REQUEST);
3665 fetch get_req_set_details into l_req_set_appl_id, l_req_set_id;
3666 close get_req_set_details;
3667
3668 BEGIN
3669 BIS_COLL_RS_HISTORY.update_terminated_rs;
3670
3671 BIS_COLL_RS_HISTORY.add_rsg_rs_run_record(p_request_set_id =>l_req_set_id,
3672 p_request_set_appl_id => l_req_set_appl_id,
3676 BIS_COLLECTION_UTILITIES.put_line('Exception while adding record for RS Run in BIS_RS_RUN_HISTORY_TABLE or updating terminated request set. ignorable exception ' || sqlerrm);
3673 p_request_name => p_request_set_code,
3674 p_root_req_id =>FND_GLOBAL.CONC_PRIORITY_REQUEST);
3675 EXCEPTION WHEN OTHERS THEN
3677 errbuf := sqlerrm;
3678 retcode := sqlcode;
3679 l_program_status := fnd_concurrent.set_completion_status('WARNING' ,NULL);
3680 END;
3681 ELSE
3682 BIS_COLLECTION_UTILITIES.put_line('------------------------------------------------------------------');
3683 BIS_COLLECTION_UTILITIES.put_line('Request Set History Collection Option is off for this Request Set.');
3684 BIS_COLLECTION_UTILITIES.put_line('No History Collection will happen for this request set.');
3685 BIS_COLLECTION_UTILITIES.put_line('------------------------------------------------------------------');
3686 END IF;
3687
3688 BEGIN
3689 BIS_COLLECTION_UTILITIES.put_line('********************************************************');
3690 BIS_COLLECTION_UTILITIES.put_line('kicking off RSG seed data validation program');
3691 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
3692 APPLICATION=> 'BIS',
3693 PROGRAM=>'BIS_BIA_RSG_VALIDATION',
3694 DESCRIPTION=>NULL,
3695 START_TIME=>NULL ,
3696 SUB_REQUEST=>FALSE,
3697 ARGUMENT1=>p_request_set_code,
3698 ARGUMENT2=>'BIS');
3699
3700 BIS_COLLECTION_UTILITIES.put_line('request id for RSG seed data validation program: '||l_request_id);
3701 BIS_COLLECTION_UTILITIES.put_line('********************************************************');
3702
3703 /**
3704 ------added for enhancement 3999465. But commented for 4422645
3705 ------ THis call can be removed if in the future we have UI
3706 ------for the user to set impl flag for reports
3707 BIS_COLLECTION_UTILITIES.put_line('Set implementation flag for reports directly included in this request set');
3708 BIS_IMPL_OPT_PKG.set_implflag_reports_in_set(p_request_set_code,l_req_set_appl_id);
3709 **/
3710
3711 BIS_COLLECTION_UTILITIES.put_line('Invoking ' || 'BIS_IMPL_OPT_PKG.setImplementationOptions');
3712 l_stmt := 'BEGIN BIS_IMPL_OPT_PKG.setImplementationOptions(:errbuf, :retcode); END;';
3713 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
3714 DBMS_SQL.parse(l_cursor_id,l_stmt,DBMS_SQL.V7);
3715 DBMS_SQL.bind_variable(l_cursor_id,'errbuf',errbuf, 32767);
3716 DBMS_SQL.bind_variable(l_cursor_id,'retcode',retcode, 200);
3717 l_rows:=DBMS_SQL.execute(l_cursor_id);
3718 DBMS_SQL.close_cursor(l_cursor_id);
3719
3720 BIS_COLLECTION_UTILITIES.put_line('Done ' || 'BIS_IMPL_OPT_PKG.setImplementationOptions');
3721 BIS_COLLECTION_UTILITIES.put_line('********************************************************');
3722
3723 ---Added for bug 4532066
3724 check_unimpl_objects_is_sets(p_request_set_code);
3725
3726
3727 if is_mvlog_mgt_enabled='Y' then
3728 open refresh_mode;
3729 fetch refresh_mode into l_refresh_mode;
3730 if refresh_mode%notfound then
3731 l_refresh_mode:='INCR';
3732 end if;
3733 close refresh_mode;
3734 open force_full_refresh ;
3735 fetch force_full_refresh into l_force_full_refresh;
3736 if force_full_refresh%notfound then
3737 l_force_full_refresh:='N';
3738 end if;
3739 close force_full_refresh ;
3740
3741 if l_refresh_mode='INIT' and l_force_full_refresh='Y' then
3742 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
3743 APPLICATION=> 'BIS',
3744 PROGRAM=>'BIS_BIA_RSG_MLOG_CAD',
3745 DESCRIPTION=>NULL,
3746 START_TIME=>NULL ,
3747 SUB_REQUEST=>FALSE,
3748 ARGUMENT1=>p_request_set_code);
3749 BIS_COLLECTION_UTILITIES.put_line('Submitted request for MV log management program' || l_request_id);
3750 waitForRequest(l_request_id);
3751 end if;
3752 end if;
3753
3754 EXCEPTION WHEN OTHERS THEN
3755 BIS_COLLECTION_UTILITIES.put_line('Exception happens in preparation program, ' || sqlerrm);
3756 errbuf := sqlerrm;
3757 retcode := sqlcode;
3758 l_program_status := fnd_concurrent.set_completion_status('WARNING' ,NULL);
3759 END;
3760
3761 end preparation_conc;
3762
3763 procedure finalization_conc(
3764 errbuf OUT NOCOPY VARCHAR2,
3765 retcode OUT NOCOPY VARCHAR,
3766 p_request_set_code IN VARCHAR
3767 )is
3768 l_request_id INTEGER;
3769 l_root_request_id INTEGER;
3770
3771 l_stmt varchar2(20000);
3772 l_cursor_id integer;
3773 l_rows integer:=0;
3774 l_program_status boolean :=true;
3775 l_program_message varchar2(200);
3776 begin
3777 IF (Not BIS_COLLECTION_UTILITIES.setup('finalization_conc')) THEN
3778 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || sqlerrm);
3779 return;
3780 END IF;
3781
3782 l_root_request_id := FND_GLOBAL.CONC_PRIORITY_REQUEST;
3783 BIS_COLLECTION_UTILITIES.put_line('FND_GLOBAL.CONC_PRIORITY_REQUEST: ' || l_root_request_id);
3784 fnd_profile.put ('CONC_SINGLE_THREAD','N');
3785
3786 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
3787 'BIS',
3788 'BIS_MV_DUMMY_REFRESH',
3789 NULL,
3790 NULL,
3791 FALSE);
3792 BIS_COLLECTION_UTILITIES.put_line('Submitted request for BIS_MV_DUMMY_REFRESH ' || l_request_id);
3793
3797
3794 --changed for enh 3473874
3795 BIS_COLLECTION_UTILITIES.put_line('Wait for MV consider refresh program to complete ');
3796 waitForRequest(l_request_id);
3798 ---Add the following API call for refreshing data for custom KPIs
3799 bsc_loader_wrapper(p_request_set_code);
3800
3801
3802 ---launch update last refresh date program at last so that
3803 ---it can update last refresh time for custom KPI objects
3804 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
3805 'BIS',
3806 'BIS_LAST_REFRESH_DATE_CONC',
3807 NULL,
3808 NULL,
3809 FALSE,
3810 l_root_request_id);
3811 BIS_COLLECTION_UTILITIES.put_line('Submitted request for BIS_LAST_REFRESH_DATE_CONC ' || l_request_id);
3812 waitForRequest(l_request_id);
3813
3814 EXCEPTION WHEN OTHERS THEN
3815 BIS_COLLECTION_UTILITIES.put_line('Exception happens in finalization program, ' || sqlerrm);
3816 errbuf := sqlerrm;
3817 retcode := sqlcode;
3818
3819 end finalization_conc;
3820
3821
3822
3823 ---we use dynamic sql and query BSC table at runtime
3824 ----but this would introduce runtime dependency on BSC product
3825 ----An alternative would be using the naming convention of the kpi report
3826 function get_indicator(p_object_name varchar2) return varchar2 is
3827 l_kpi varchar2(30);
3828 l_sql varchar2(2000);
3829 l_table_owner varchar2(30);
3830 begin
3831 ---- select rtrim(ltrim(ltrim(P_OBJECT_NAME,substr(P_OBJECT_NAME,0,instr(P_OBJECT_NAME,'[')-1)),'['),']') into l_kpi from dual;
3832 --- select substr(ltrim(p_object_name,'BSC_S'),0,instr(ltrim(p_object_name,'BSC_S'),'_')-1) into l_kpi from dual;
3833 ----select substr(ltrim(p_object_name,'BSC_'),0,instr(ltrim(p_object_name,'BSC_'),'_')-1) into l_kpi from dual ;
3834
3835 ---first check if the bsc table exists or not
3836 l_table_owner:=get_object_owner(upper('bsc_kpi_analysis_measures_b'),'TABLE');
3837 if l_table_owner='NOTFOUND' then
3838 return null;
3839 end if;
3840
3841 l_sql:='select distinct to_char(b.indicator) indicator_id from '||
3842 ' bis_indicators a, '||
3843 ' bsc_kpi_analysis_measures_b b '||
3844 ' where a.dataset_id=b.dataset_id '||
3845 ' and a.function_name=:1';
3846 execute immediate l_sql into l_kpi using p_object_name;
3847
3848 return l_kpi;
3849 exception
3850 when no_data_found then
3851 return null;
3852 when others then
3853 raise;
3854 end;
3855
3856 ----This function checks if the new loader program for indicators exists
3857 function loader_exist return varchar2 is
3858 l_exist_flag varchar2(1);
3859 begin
3860 l_exist_flag:='N';
3861 select 'Y'
3862 into l_exist_flag
3863 from fnd_concurrent_programs
3864 where concurrent_program_name='BSC_LOAD_INDICATORS_DATA'
3865 and application_id=271;
3866 return l_exist_flag;
3867 exception
3868 when no_data_found then
3869 l_exist_flag:='N';
3870 return l_exist_flag;
3871 when others then
3872 raise;
3873 end;
3874
3875 ----this function returns the loading mode of the request set
3876 function loading_mode(p_request_set_name in varchar2) return varchar2 is
3877 l_loading_mode varchar2(30);
3878 begin
3879 l_loading_mode:=null;
3880 select distinct option_value into l_loading_mode
3881 from bis_request_set_options
3882 where request_set_name=p_request_set_name
3883 and option_name='REFRESH_MODE';
3884 return l_loading_mode;
3885 exception
3886 when no_data_found then
3887 l_loading_mode:='INCR' ;
3888 return l_loading_mode;
3889 when others then
3890 raise;
3891 end;
3892
3893
3894 function force_full_refresh(p_request_set_name in varchar2) return varchar2 is
3895 l_force_full_refresh varchar2(30);
3896 begin
3897 l_force_full_refresh:=null;
3898 select distinct option_value into l_force_full_refresh
3899 from bis_request_set_options
3900 where request_set_name=p_request_set_name
3901 and option_name='FORCE_FULL';
3902 return l_force_full_refresh;
3903 exception
3904 when no_data_found then
3905 l_force_full_refresh:='N' ;
3906 return l_force_full_refresh;
3907 when others then
3908 raise;
3909 end;
3910
3911 function kpi_in_list(p_kpi_list in varchar2, p_kpi in varchar2) return varchar2 is
3912 begin
3913 if p_kpi_list is null then
3914 return 'N';
3915 else
3916 if instr(p_kpi_list,','||p_kpi||',')=0
3917 and instr(p_kpi_list,p_kpi||',')=0
3918 and instr(p_kpi_list,','||p_kpi)=0 then
3919 return 'N';
3920 else
3921 return 'Y';
3922 end if;
3923 end if;
3924 end;
3925
3926
3927 procedure bsc_loader_wrapper(
3928 p_request_set_code IN VARCHAR
3929 )is
3930
3931 cursor c_page_objects is
3932 select distinct a.object_name,a.object_type
3933 from
3934 bis_request_set_objects a,
3935 fnd_request_sets b
3939 and b.application_id=191;
3936 where a.request_set_name=b.request_set_name
3937 and a.set_app_id=b.application_id
3938 and b.request_set_name=p_request_set_code
3940
3941 l_page_object_rec c_page_objects%rowtype;
3942
3943 cursor c_custom_kpi_in_page(p_object_name varchar2,p_object_type varchar2)
3944 is
3945 select distinct get_indicator(obj_name) kpi
3946 from
3947 ( select distinct
3948 obj.depend_OBJECT_NAME obj_name,
3949 obj.depend_object_type obj_type,
3950 obj.depend_object_owner obj_owner
3951 from
3952 ( select object_name,
3953 object_type,
3954 object_owner,
3955 depend_object_name,
3956 depend_object_type,
3957 depend_object_owner,
3958 enabled_flag
3959 from
3960 bis_obj_dependency
3961 where enabled_flag='Y' ) obj
3962 start with obj.object_type =p_object_type
3963 and obj.object_name = p_object_name
3964 connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
3965 and prior depend_object_type=object_type
3966 ) depend_objects
3967 where obj_type ='REPORT'
3968 and obj_owner=get_bsc_schema_name
3969 and obj_name like 'BSC%';
3970
3971 l_custom_kpi_rec c_custom_kpi_in_page%rowtype;
3972 l_kpi_list varchar2(2000);
3973 l_kpi_name_list varchar2(2000);
3974
3975 l_request_id number;
3976
3977 l_result BOOLEAN;
3978 l_phase VARCHAR2(500) := NULL;
3979 l_status VARCHAR2(500) := NULL;
3980 l_devphase VARCHAR2(500) := NULL;
3981 l_devstatus VARCHAR2(500) := NULL;
3982 l_message VARCHAR2(500) := NULL;
3983
3984 l_hist_coll_on BOOLEAN;
3985 begin
3986 -- Enh#4418520-aguwalan
3987 l_hist_coll_on := is_history_collect_on(p_request_set_code, 191);
3988
3989 l_kpi_list:=null;
3990 l_kpi_name_list :=null;
3991 BIS_COLLECTION_UTILITIES.put_line('***********************************');
3992 For l_page_object_rec in c_page_objects loop
3993 for l_custom_kpi_rec in c_custom_kpi_in_page(l_page_object_rec.object_name,l_page_object_rec.object_type) loop
3994 if l_custom_kpi_rec.kpi is not null and kpi_in_list(l_kpi_list,l_custom_kpi_rec.kpi)='N' then
3995 l_kpi_list:=l_kpi_list||l_custom_kpi_rec.kpi||',';
3996 --l_kpi_name_list := l_kpi_name_list || l_custom_kpi_rec.kpi_name || ',';
3997 end if;
3998 end loop;
3999 end loop;
4000 if l_kpi_list is not null then
4001 l_kpi_list:=rtrim(l_kpi_list,',');
4002 end if;
4003
4004 /* if l_kpi_name_list is not null then
4005 l_kpi_name_list:=rtrim(l_kpi_name_list,',');
4006 end if;*/
4007
4008 BIS_COLLECTION_UTILITIES.put_line('l_kpi_list:'||l_kpi_list);
4009 BIS_COLLECTION_UTILITIES.put_line('loader_exist:'||loader_exist);
4010
4011 -----turning off sequential execution before spawning sub-program
4012 -- fnd_profile.put ('CONC_SINGLE_THREAD','N');
4013
4014 if l_kpi_list is not null and loader_exist='Y' then
4015 if loading_mode(p_request_set_code)='INIT' and force_full_refresh(p_request_set_code)='Y' then
4016 BIS_COLLECTION_UTILITIES.put_line('Initial loading mode. calling loader program to delete data from indicators');
4017
4018 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
4019 'BSC',
4020 'BSC_DELETE_DATA_IND',
4021 NULL,
4022 NULL,
4023 FALSE,
4024 l_kpi_list,
4025 'Y');---'Y' means keep input tables data
4026
4027 BIS_COLLECTION_UTILITIES.put_line('Submitted request for BSC_DELETE_INDICATORS_DATA ' || l_request_id);
4028 waitForRequest(l_request_id);
4029 -- Enh#4418520-aguwalan
4030 IF (l_hist_coll_on) THEN
4031 BIS_COLL_RS_HISTORY.insert_program_object_data(x_request_id => l_request_id,
4032 x_stage_req_id => null,
4033 x_object_name => l_kpi_list,
4034 x_object_type => 'BSC_CUSTOM_KPI',
4035 x_refresh_type => loading_mode(p_request_set_code),
4036 x_set_request_id => FND_GLOBAL.CONC_PRIORITY_REQUEST);
4037 END IF;
4038 end if;
4039 BIS_COLLECTION_UTILITIES.put_line('Call loader program to load custom dimensions');
4040 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
4041 'BSC',
4042 'BSC_REFRESH_DIM_IND',
4043 NULL,
4044 NULL,
4045 FALSE,
4046 l_kpi_list,
4047 'N');
4048 BIS_COLLECTION_UTILITIES.put_line('Submitted request for BSC_LOAD_INDICATORS_DIMS ' || l_request_id);
4049 waitForRequest(l_request_id);
4050 -- Enh#4418520-aguwalan
4051 IF (l_hist_coll_on) THEN
4052 BIS_COLL_RS_HISTORY.insert_program_object_data( x_request_id => l_request_id,
4053 x_stage_req_id => null,
4054 x_object_name => l_kpi_list,
4055 x_object_type => 'BSC_CUSTOM_KPI',
4056 x_refresh_type => loading_mode(p_request_set_code),
4057 x_set_request_id => FND_GLOBAL.CONC_PRIORITY_REQUEST);
4058 END IF;
4059 BIS_COLLECTION_UTILITIES.put_line('Call loader program to load data for indicators');
4060 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
4061 'BSC',
4065 FALSE,
4062 'BSC_REFRESH_SUMMARY_IND',
4063 NULL,
4064 NULL,
4066 l_kpi_list,
4067 'N');
4068
4069 BIS_COLLECTION_UTILITIES.put_line('Submitted request for BSC_LOAD_INDICATORS_DATA ' || l_request_id);
4070 waitForRequest(l_request_id);
4071 -- Enh#4418520-aguwalan
4072 IF (l_hist_coll_on) THEN
4073 BIS_COLL_RS_HISTORY.insert_program_object_data( x_request_id => l_request_id,
4074 x_stage_req_id => null,
4075 x_object_name => l_kpi_list,
4076 x_object_type => 'BSC_CUSTOM_KPI',
4077 x_refresh_type => loading_mode(p_request_set_code),
4078 x_set_request_id => FND_GLOBAL.CONC_PRIORITY_REQUEST);
4079 END IF;
4080 end if;
4081 BIS_COLLECTION_UTILITIES.put_line('********************************************');
4082
4083 exception
4084 when others then
4085 raise;
4086 end;
4087
4088 function form_function_exist(p_object_type in varchar2, p_object_name in varchar2) return varchar2 is
4089 cursor c_report_function is
4090 select 'Y'
4091 from fnd_form_functions
4092 where function_name=p_object_name
4093 and type in ('WWW','JSP');
4094
4095 ---add WEBPORTLETX for bug 4067976
4096 cursor c_portlet_function is
4097 select 'Y'
4098 from fnd_form_functions
4099 where function_name=p_object_name
4100 and type in ('WEBPORTLET','WEBPORTLETX');
4101
4102 cursor c_page_function is
4103 select 'Y'
4104 from fnd_form_functions
4105 where function_name= bis_impl_dev_pkg.get_function_by_page(p_object_name)
4106 and upper(web_html_call) like '%BIS_COMPONENT_PAGE'||'&'||'AKREGIONAPPLICATIONID=191%';
4107 l_dummy varchar2(1);
4108 begin
4109 l_dummy:='N';
4110 if p_object_type='REPORT' then
4111 open c_report_function;
4112 fetch c_report_function into l_dummy;
4113 if c_report_function%notfound then
4114 l_dummy:='N';
4115 end if;
4116 end if;
4117
4118 if p_object_type='PORTLET' then
4119 open c_portlet_function;
4120 fetch c_portlet_function into l_dummy;
4121 if c_portlet_function%notfound then
4122 l_dummy:='N';
4123 end if;
4124 end if;
4125
4126 if p_object_type='PAGE' then
4127 open c_page_function;
4128 fetch c_page_function into l_dummy;
4129 if c_page_function%notfound then
4130 l_dummy:='N';
4131 end if;
4132 end if;
4133 return l_dummy;
4134 exception
4135 when others then
4136 raise;
4137 end;
4138
4139 ----this program will print out the invalid RSG seed data
4140 procedure seed_data_validation(
4141 errbuf OUT NOCOPY VARCHAR2,
4142 retcode OUT NOCOPY VARCHAR,
4143 p_request_set_code IN VARCHAR2,
4144 p_set_app IN varchar2
4145 ) is
4146
4147 cursor c_pages is
4148 select distinct a.object_name,a.object_type
4149 from
4150 bis_request_set_objects a,
4151 fnd_request_sets b,
4152 fnd_application c
4153 where a.request_set_name=b.request_set_name
4154 and a.set_app_id=b.application_id
4155 and b.request_set_name=upper(p_request_set_code)
4156 and b.application_id=c.application_id
4157 and c.application_short_name=p_set_app;
4158
4159 l_page_rec c_pages%rowtype;
4160
4161
4162 cursor c_objects_per_page(p_object_name varchar2,p_object_type varchar2) is
4163 select depend_objects.obj_type object_type,depend_objects.obj_name object_name,depend_objects.obj_owner object_owner
4164 from
4165 ( select distinct
4166 obj.depend_OBJECT_NAME obj_name,
4167 obj.depend_object_type obj_type,
4168 obj.depend_object_owner obj_owner
4169 from
4170 ( select object_name,
4171 object_type,
4172 object_owner,
4176 enabled_flag
4173 depend_object_name,
4174 depend_object_type,
4175 depend_object_owner,
4177 from
4178 bis_obj_dependency
4179 where enabled_flag='Y' ) obj
4180 start with obj.object_type =p_object_type
4181 and obj.object_name = p_object_name
4182 connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
4183 and prior depend_object_type=object_type
4184 ) depend_objects;
4185
4186 l_obj_rec c_objects_per_page%rowtype;
4187 l_obj_phy_owner varchar2(30);
4188 l_sql_stmt varchar2(2000);
4189 l_function_exist varchar2(1);
4190 TYPE CurTyp IS REF CURSOR;
4191 cv CurTyp;
4192 l_invalid_obj_type varchar2(30);
4193 l_invalid_obj_owner varchar2(50);
4194 l_invalid_obj_name bis_obj_properties.object_name%type;
4195 l_report_title varchar2(500);
4196 l_report_comment varchar2(2000);
4197 l_report_end varchar2(500);
4198 l_obj_type_prompt varchar2(100);
4199 l_obj_owner_prompt varchar2(100);
4200 l_obj_name_prompt varchar2(100);
4201
4202
4203 l_program_status boolean :=true;
4204
4205 begin
4206
4207 IF (Not BIS_COLLECTION_UTILITIES.setup('seed_data_validation')) THEN
4208 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || sqlerrm);
4209 return;
4210 END IF;
4211
4212 -----write invalid objects into global temp table
4213 for l_page_rec in c_pages loop
4214 for l_obj_rec in c_objects_per_page(l_page_rec.object_name,l_page_rec.object_type) loop
4215 if l_obj_rec.object_type in ('TABLE','MV','VIEW') then
4216 l_obj_phy_owner:=get_object_owner(l_obj_rec.object_name,l_obj_rec.object_type);
4217 if l_obj_phy_owner='NOTFOUND' then
4218 l_sql_stmt := 'insert into BIS_BIA_RSG_SEED_VALIDATION(object_type,object_name,object_owner)
4219 values (:1,:2,:3)';
4220 EXECUTE IMMEDIATE l_sql_stmt USING l_obj_rec.object_type,l_obj_rec.object_name,l_obj_rec.object_owner ;
4221 commit;
4222 end if;---end if DB object not found
4223 end if;----end if DB objects
4224 if l_obj_rec.object_type in ('REPORT','PORTLET','PAGE') then
4225 l_function_exist:=form_function_exist(l_obj_rec.object_type,l_obj_rec.object_name);
4226 if l_function_exist='N' then
4227 l_sql_stmt := 'insert into BIS_BIA_RSG_SEED_VALIDATION(object_type,object_name,object_owner)
4228 values (:1,:2,:3)';
4229 EXECUTE IMMEDIATE l_sql_stmt USING l_obj_rec.object_type,l_obj_rec.object_name,l_obj_rec.object_owner ;
4230 commit;
4231 end if;---end if form function not exist
4232 end if;----end if form function objects
4233 end loop; ---end loop of objects
4234 end loop;---end loop of pages
4235
4236
4237 ----print out invalid objects from the global temp table
4238
4239 l_report_title:=fnd_message.get_string('BIS','BIS_BIA_RSG_VALIDATION_TITLE');
4240 l_report_comment:=fnd_message.get_string('BIS','BIS_BIA_RSG_VALIDATION_COMMENT');
4241 l_report_end:=fnd_message.get_string('BIS','BIS_BIA_RSG_VALIDATION_END');
4242 l_obj_type_prompt:=fnd_message.get_string('BIS','BIS_BIA_RSG_VALIDATION_TYPE');
4243 l_obj_owner_prompt:=fnd_message.get_string('BIS','BIS_BIA_RSG_VALIDATION_OWNER');
4244 l_obj_name_prompt :=fnd_message.get_string('BIS','BIS_BIA_RSG_VALIDATION_NAME');
4245
4246
4247 l_sql_stmt:='select distinct object_type,object_owner,object_name from BIS_BIA_RSG_SEED_VALIDATION
4248 order by object_type,object_owner,object_name';
4249 open cv for l_sql_stmt;
4250
4251 BIS_COLLECTION_UTILITIES.put_line_out(l_report_comment);
4252 BIS_COLLECTION_UTILITIES.put_line_out(' ');
4253 BIS_COLLECTION_UTILITIES.put_line_out(' ');
4254 BIS_COLLECTION_UTILITIES.put_line_out(l_report_title);
4255 BIS_COLLECTION_UTILITIES.put_line_out(l_obj_type_prompt||' '||l_obj_owner_prompt||' '||l_obj_name_prompt);
4256
4257
4258
4259 loop
4260 fetch cv into l_invalid_obj_type,l_invalid_obj_owner,l_invalid_obj_name;
4261 exit when cv%notfound;
4262 if l_invalid_obj_type='MV' then
4263 l_invalid_obj_type:=l_invalid_obj_type||' ';
4264 end if;
4265 if l_invalid_obj_type='VIEW' then
4266 l_invalid_obj_type:=l_invalid_obj_type||' ';
4267 end if;
4268
4269 if l_invalid_obj_type='TABLE' then
4270 l_invalid_obj_type:=l_invalid_obj_type||' ';
4271 end if;
4272
4273 if l_invalid_obj_type='REPORT' then
4274 l_invalid_obj_type:=l_invalid_obj_type||' ';
4275 end if;
4276
4277 if l_invalid_obj_type='PAGE' then
4278 l_invalid_obj_type:=l_invalid_obj_type||' ';
4279 end if;
4280
4281
4282
4283 BIS_COLLECTION_UTILITIES.put_line_out(l_invalid_obj_type||' '||l_invalid_obj_owner||' '||l_invalid_obj_name);
4284 end loop;
4285 close cv;
4286 BIS_COLLECTION_UTILITIES.put_line(l_report_end);
4287
4288 exception
4289 when others then
4290 BIS_COLLECTION_UTILITIES.put_line('Exception happens in ' || 'bis_create_requestset.seed_data_validation, ' || sqlerrm);
4291 errbuf := sqlerrm;
4292 retcode := '2';
4293 l_program_status := fnd_concurrent.set_completion_status('WARNING' ,NULL);
4294 end;
4295
4296 /*
4297 Enh#3473874
4298 This programs is called while creation of a request set.
4299 This will add one extra stage at the end of the request set and
4300 link history collection program to it.
4301 Then it links this stage to the stages in case of error.
4302 */
4303
4307 report_stage_name varchar2(200);
4304 PROCEDURE add_link_history_stage(p_set_name in varchar2,p_set_app in varchar2,p_max_stage in number,
4305 p_rsg_history_flag in varchar2)
4306 IS
4308 cursor c_stages
4309 is
4310 select s.STAGE_NAME
4311 from fnd_request_sets r ,fnd_request_set_stages s,
4312 fnd_application app
4313 where r.REQUEST_SET_ID= s.REQUEST_SET_ID
4314 and r.application_id = s.set_Application_id
4315 and r.REQUEST_SET_NAME = p_set_name
4316 and r.application_id = app.application_id
4317 and app.application_short_name=p_set_app;
4318
4319 c_stages_rec c_stages%rowtype;
4320
4321 l_root_request_id integer;
4322
4323 BEGIN
4324 ---Add RSG History Report program at the end of the request set
4325
4326 l_root_request_id := FND_GLOBAL.CONC_PRIORITY_REQUEST;
4327 if (p_rsg_history_flag = 'Y') then
4328 fnd_set.add_stage
4329 (name=>g_stage_prompt||' '||to_char(p_max_stage+50),
4330 request_set=>upper(p_set_name),
4331 set_application=>p_set_app,
4332 short_name=>'Stage_'||to_char(p_max_stage+50),
4333 description=>null,
4334 display_sequence=>p_max_stage+50,
4335 function_short_name=>'FNDRSSTE',
4336 function_application=>'FND',
4337 critical=>'N',
4338 incompatibilities_allowed=>'N',
4339 start_stage=>'N',
4340 language_code=>'US');
4341
4342 fnd_set.add_program
4343 (program =>'BIS_BIA_RSG_HISTORY_PROG' ,
4344 program_application=>'BIS',
4345 request_set=>upper(p_set_name) ,
4346 set_application=>p_set_app ,
4347 stage=>'Stage_'||to_char(p_max_stage+50),
4348 program_sequence=>10,
4349 critical=>'Y',
4350 number_of_copies =>0,
4351 save_output =>'Y',
4352 style=>null,
4353 printer=>null);
4354
4355 fnd_set.PROGRAM_PARAMETER(
4356 PROGRAM=>'BIS_BIA_RSG_HISTORY_PROG',
4357 PROGRAM_APPLICATION=>'BIS',
4358 REQUEST_SET=>upper(p_set_name),
4359 SET_APPLICATION=>p_set_app,
4360 STAGE=>'Stage_'||to_char(p_max_stage+50),
4361 PROGRAM_SEQUENCE=>10,
4362 PARAMETER=>'Root Request ID',
4363 DISPLAY=>'Y',
4364 MODIFY=> 'Y' ,
4365 SHARED_PARAMETER=>null ,
4366 DEFAULT_TYPE=>'Constant',
4367 DEFAULT_VALUE=>null
4368 );
4369 commit;
4370 report_stage_name := 'Stage_'||to_char(p_max_stage+50);
4371
4372 --Now loop through all the stages and link this last stage with all the stages in case of error
4373 for c_stages_rec in c_stages loop
4374 if (c_stages_rec.STAGE_NAME <> report_stage_name) then
4375 fnd_set.link_stages (request_set =>upper(p_set_name),
4376 set_application =>'BIS',
4377 from_stage =>c_stages_rec.STAGE_NAME,
4378 to_stage=>report_stage_name,
4379 success => 'N',
4380 warning => 'N',
4381 error => 'Y');
4382 end if;
4383 end loop;
4384 commit;
4385 end if;
4386 -- Bug#4881518 :: Adding a new stage and a new program to fix the issue with
4387 -- incorrect Request Set Status due to RSG History Collection program
4388 fnd_set.add_stage(name=>g_stage_prompt||' '||to_char(p_max_stage+150),
4389 request_set=>upper(p_set_name),
4390 set_application=>p_set_app,
4391 short_name=>'Stage_'||to_char(p_max_stage+150),
4392 description=>null,
4393 display_sequence=>p_max_stage+150,
4394 function_short_name=>'FNDRSSTE',
4395 function_application=>'FND',
4396 critical=>'N',
4397 incompatibilities_allowed=>'N',
4401 fnd_set.add_program(program =>'BIS_BIA_RS_STATUS_CHK' ,
4398 start_stage=>'N',
4399 language_code=>'US');
4400
4402 program_application=>'BIS',
4403 request_set=>upper(p_set_name) ,
4404 set_application=>p_set_app ,
4405 stage=>'Stage_'||to_char(p_max_stage+150),
4406 program_sequence=>20,
4407 critical=>'Y',
4408 number_of_copies =>0,
4409 save_output =>'Y',
4410 style=>null,
4411 printer=>null);
4412 commit;
4413 EXCEPTION WHEN OTHERS THEN
4414 BIS_COLLECTION_UTILITIES.put_line('Exception happens in add_link_history_stage ' || sqlerrm);
4415 raise;
4416
4417 END add_link_history_stage;
4418
4419 function get_bsc_schema_name return varchar is
4420 cursor get_appl_short_name is
4421 select application_short_name from fnd_application
4422 where application_id =271;
4423
4424 begin
4425 for get_appl_rec in get_appl_short_name loop
4426 return get_appl_rec.application_short_name;
4427 end loop;
4428
4429 end;
4430
4431 /*
4432 * Added for Bug#4881518 :: API to check the status of all the requests inside the request set
4433 */
4434 PROCEDURE set_rs_status(errbuf OUT NOCOPY VARCHAR2,
4435 retcode OUT NOCOPY VARCHAR) IS
4436
4437 l_root_request_id NUMBER;
4438
4439 CURSOR c_get_all_prog_status IS
4440 SELECT request_id, user_concurrent_program_name NAME, 'PROG' TYPE
4441 FROM BIS_RS_PROG_RUN_HISTORY bis, FND_CONCURRENT_PROGRAMS_VL fnd
4442 WHERE bis.set_request_id = l_root_request_id AND bis.status_code ='E'
4443 AND bis.prog_app_id = fnd.application_id AND bis.program_id = fnd.concurrent_program_id
4444 UNION
4445 SELECT request_id, user_stage_name NAME, 'STAGE' TYPE
4446 FROM BIS_RS_STAGE_RUN_HISTORY bis, FND_REQUEST_SET_STAGES_VL fnd
4447 WHERE set_request_id = l_root_request_id AND status_code ='E'
4448 AND bis.set_app_id = fnd.set_application_id AND bis.request_set_id = fnd.request_set_id
4449 AND bis.stage_id = fnd.request_set_stage_id ;
4450
4451 l_request_id VARCHAR2(100);
4452 l_name VARCHAR2(240);
4453 l_type VARCHAR2(10);
4454 l_program_status BOOLEAN;
4455 BEGIN
4456 l_root_request_id := FND_GLOBAL.CONC_PRIORITY_REQUEST;
4457 BIS_COLLECTION_UTILITIES.put_line('Checking the status of all the program of the Request Set :: RequestId#'||l_root_request_id);
4458 OPEN c_get_all_prog_status;
4459 FETCH c_get_all_prog_status INTO l_request_id, l_name, l_type;
4460 CLOSE c_get_all_prog_status;
4461 IF (l_type = 'PROG') THEN
4462 BIS_COLLECTION_UTILITIES.put_line('Program - '||l_name || ', Request Id # '||l_request_id ||' completed with status=Error');
4463 BIS_COLLECTION_UTILITIES.put_line('Hence setting status of the current program (BIS Request Set Status Check Program) to Error.');
4464 l_program_status := fnd_concurrent.set_completion_status('ERROR' ,NULL);
4465 ELSIF (l_type = 'STAGE') THEN
4466 BIS_COLLECTION_UTILITIES.put_line(l_name || ', Request Id # '||l_request_id ||' completed with status=Error');
4467 BIS_COLLECTION_UTILITIES.put_line('Hence setting status of the current program to Error.');
4468 l_program_status := fnd_concurrent.set_completion_status('ERROR' ,NULL);
4469 ELSE
4470 BIS_COLLECTION_UTILITIES.put_line('All the programs in the request set completed Successfully.');
4471 END IF;
4472 EXCEPTION
4473 WHEN OTHERS THEN
4474 BIS_COLLECTION_UTILITIES.put_line('Exception in set_rs_status' || sqlerrm);
4475 l_program_status := fnd_concurrent.set_completion_status('ERROR' ,NULL);
4476 BIS_COLLECTION_UTILITIES.put_line('BIS Request Set Status Check Program completed with internal exception,');
4477 BIS_COLLECTION_UTILITIES.put_line('due to this the correct status of the programs in the request set can not be found.');
4478 BIS_COLLECTION_UTILITIES.put_line('Note that the status of Request set can be Normal or Warning even though one of the');
4479 BIS_COLLECTION_UTILITIES.put_line('program might have completed with error.');
4480 END;
4481
4482 /*
4483 * API to return the value of the request set option='HISTORY_COLLECT' :: Enh#4418520-aguwalan
4484 */
4485 FUNCTION is_history_collect_on(p_request_set_name IN VARCHAR2, p_request_app_id IN NUMBER) RETURN BOOLEAN
4486 IS
4487 CURSOR c_history_coll_option(rs_name VARCHAR2, rs_app_id NUMBER)
4488 IS
4489 select NVL(OPTION_VALUE, 'Y')
4490 from bis_request_set_options
4491 where request_set_name = rs_name
4492 and set_app_id = rs_app_id
4493 and OPTION_NAME = 'HISTORY_COLLECT';
4494 l_flag VARCHAR2(10);
4495 BEGIN
4496 l_flag := NULL;
4497 OPEN c_history_coll_option(p_request_set_name, p_request_app_id);
4498 FETCH c_history_coll_option into l_flag;
4499 CLOSE c_history_coll_option;
4500 IF( NVL(l_flag, 'Y') = 'Y' ) THEN
4501 RETURN true;
4502 ELSE
4503 RETURN false;
4504 END IF;
4505 END;
4506
4507 /*
4508 * Overloading is_history_collect_on API to take the root_Request_id and return the request set
4509 * option='HISTORY_COLLECT' :: Enh#4418520-aguwalan
4510 */
4511 FUNCTION is_history_collect_on(p_root_request_id IN NUMBER) RETURN BOOLEAN
4512 IS
4513 CURSOR c_request_set_details IS
4514 SELECT rs.request_set_name
4515 FROM fnd_concurrent_requests cr, fnd_request_sets rs
4516 WHERE cr.request_id = p_root_request_id
4517 AND rs.application_id = cr.argument1
4518 AND rs.request_set_id = cr.argument2;
4519 l_request_set_name VARCHAR2(1000);
4520 BEGIN
4521 OPEN c_request_set_details;
4522 FETCH c_request_set_details into l_request_set_name;
4523 CLOSE c_request_set_details;
4524 IF(l_request_set_name is not null) THEN
4525 RETURN is_history_collect_on(l_request_set_name,191);
4526 ELSE
4527 RETURN true;
4528 END IF;
4529 END;
4530
4531 END BIS_CREATE_REQUESTSET;
[Home] [Help]