1 package body BIS_COLL_RS_HISTORY AS
2 /*$Header: BISRSHTB.pls 120.4 2006/05/18 12:29:15 aguwalan noship $*/
3
4 g_current_user_id NUMBER := FND_GLOBAL.User_id;
5 g_error_status VARCHAR2(80) := get_lookup_meaning('BIS_REQUEST_SET_STATUS','ERROR_COMPL');
6 g_completion_status VARCHAR2(80) := get_lookup_meaning('BIS_REQUEST_SET_STATUS','NORMAL_COMPL');
7 g_warning_status VARCHAR2(80) := get_lookup_meaning('BIS_REQUEST_SET_STATUS','WARNING_COMPL');
8
9 p_dummy_flag VARCHAR2(1) := 'Y';
10
11 function get_lookup_meaning(p_lookup_type varchar2, p_lookup_code varchar2) return varchar2 is
12 l_meaning varchar2(200):= null;
13 begin
14 select MEANING into l_meaning from FND_LOOKUP_VALUES_VL where LOOKUP_TYPE=p_lookup_type and LOOKUP_CODE=p_lookup_code;
15 return l_meaning;
16 end;
17
18 /**********
19 Enh#3473874 This procedure is to put RSG run data in report run tables.
20 First it finds out all the stages for the given request set id.
21 Then it recursively finds out the program within each stage
22 Then it finds objects refreshed by every program with taking its join with bis_prog_linkage.
23 It updates completion status and date for stages and request set.
24 *********/
25 procedure rsg_history_report (
26 errbuf OUT NOCOPY VARCHAR2,
27 retcode OUT NOCOPY VARCHAR2,
28 Root_request_id IN NUMBER
29 ) is
30
31 l_root_request_id number;
32 l_current_stage number;
33 l_conc_prog_id number;
34 consider_mv_req number;
35 l_rs_history_stage number;
36
37 l_request_id varchar2(2000) := '';
38 l_parent_request_ids varchar2(2000) :='';
39 l_obj_type varchar2(100);
40
41 l_child_request boolean := false;
42 l_program_status boolean :=true;
43 is_prog_part_of_rs boolean := false;
44
45 l_req_set_id number;
46 l_req_app_id number;
47 l_req_set_name FND_REQUEST_SETS.REQUEST_SET_NAME%TYPE;
48
49
50 cursor c_stages is
51 select
52 rs.Request_set_id Request_set_id,
53 rs.Set_app_id Set_app_id ,
54 stg.request_set_stage_id Stage_id,
55 req.Request_id Request_id ,
56 rs.request_id Set_request_id,
57 req.actual_start_date Start_date,
58 req.actual_completion_date Completion_date,
59 req.Status_code Status_code,
60 req.phase_code phase_code,
61 req.completion_text Completion_text
62 from bis_rs_run_history rs,
63 fnd_request_set_stages stg,
64 fnd_concurrent_requests req
65 where rs.request_id = l_root_request_id
66 and rs.request_set_id = stg.request_set_id
67 and req.argument3 = stg.request_set_stage_id
68 and req.parent_request_id = rs.request_id ;
69
70 c_stages_rec c_stages%rowtype;
71
72
73 cursor c_programs is
74 select
75 stg.request_id Stage_request_id,
76 req.request_id Request_id ,
77 req.CONCURRENT_PROGRAM_ID program_id,
78 req.argument1 obj_owner,
79 req.argument2 obj_name
80 from fnd_concurrent_requests req,
81 bis_rs_stage_run_history stg
82 where stg.set_request_id = l_root_request_id
83 and stg.request_id = req.parent_request_id ;
84
85 c_program_rec c_programs%rowtype;
86
87 cursor c_sub_programs is
88 select
89 req.request_id Request_id ,
90 req.parent_request_id parent_request_id,
91 req.CONCURRENT_PROGRAM_ID program_id,
92 prog.STAGE_REQUEST_ID stage_req_id
93 from fnd_concurrent_requests req,
94 BIS_RS_PROG_RUN_HISTORY prog
95 where --(req.parent_request_id is not null) and
96 req.parent_request_id = prog.request_id
97 and (instr(l_parent_request_ids,to_char(req.parent_request_id)||',') <> 0);
98
99 c_sub_programs_Rec c_sub_programs%rowtype;
100
101 cursor c_get_program_id is
102 select CONCURRENT_PROGRAM_ID
103 from fnd_concurrent_programs
104 where concurrent_program_name ='FNDGTST'
105 and APPLICATION_ID =0;
106
107 cursor c_obj_type(l_object_name varchar2 ) is
108 select object_type
109 from bis_obj_properties
110 where object_name= l_object_name
111 and (Object_type ='MV' or Object_type ='TABLE');
112
113 /** Cursor no longer used in the code
114 --for MVs of type consider refresh
115 cursor c_refresh_mv is
116 select obj.prog_request_id request_id
117 from BIS_RS_PROG_RUN_HISTORY prog,
118 fnd_concurrent_programs fnd,
119 BIS_OBJ_REFRESH_HISTORY obj
120 where prog.set_request_id = l_root_request_id
121 and prog.program_id = fnd.CONCURRENT_PROGRAM_ID
122 and fnd.CONCURRENT_PROGRAM_NAME ='BIS_MV_REFRESH'
123 and obj.prog_request_id = prog.request_id
124 and obj.Refresh_type = 'CONSIDER_REFRESH';
125
126 c_refresh_mv_rec c_refresh_mv%rowtype;
127 **/
128 cursor c_consider_mv is
129 select request_id
130 from BIS_RS_PROG_RUN_HISTORY
131 where set_request_id = l_root_request_id
132 and PROG_APP_ID =191
133 and program_id = ( select CONCURRENT_PROGRAM_ID
134 from fnd_concurrent_programs
135 where CONCURRENT_PROGRAM_NAME ='BIS_MV_DUMMY_REFRESH' and APPLICATION_ID =191);
136
137 -- get_stage
138 cursor c_get_stage(p_prog_req_id in number) is
139 select stage_request_id
140 from bis_rs_prog_run_history
141 where request_id = p_prog_req_id;
142
143 begin
144 errbuf := NULL;
145 retcode := '0';
146
147 --purge the old data.
148 purgeHistory;
149
150 -- handling here the logic this program running as standalone or part of request set.
151 l_root_request_id := Root_request_id;
152
153 if l_root_request_id is null then
154 --Program is running as the part of the request set
155
156 l_root_request_id := FND_GLOBAL.CONC_PRIORITY_REQUEST;
157 is_prog_part_of_rs := true;
158 --get request set details
159 if(get_req_set_details(p_request_set_id => l_req_set_id,
160 p_request_set_appl_id => l_req_app_id,
161 p_request_set_name => l_req_set_name,
162 p_root_request_id => l_root_request_id )) then
163 --BIS_COLLECTION_UTILITIES.put_line('Request set id '||l_req_set_id||' Application ID '||l_req_app_id );
164 p_dummy_flag := 'Y';
165 else
166 l_program_status := fnd_concurrent.set_completion_status('WARNING' ,NULL);
167 BIS_COLLECTION_UTILITIES.put_line('Given request id is not valid');
168 return;
169 end if;
170 BIS_COLLECTION_UTILITIES.put_line('********Program is running as the part of the request set***********');
171 else
172 BIS_COLLECTION_UTILITIES.put_line('************Program is running as a standalone program********');
173 --get request set details
174 if (get_req_set_details(p_request_set_id => l_req_set_id,
175 p_request_set_appl_id => l_req_app_id,
176 p_request_set_name => l_req_set_name,
177 p_root_request_id => l_root_request_id ) ) then
178
179 -- BIS_COLLECTION_UTILITIES.put_line('Request set id '||l_req_set_id||' Application ID '||l_req_app_id );
180 p_dummy_flag := 'Y';
181
182 -- see if given parameters are valid
183 if( l_req_app_id is null OR l_req_set_id is null) then
184 BIS_COLLECTION_UTILITIES.put_line('Given request id '||Root_request_id|| ' is not valid');
185 l_program_status := fnd_concurrent.set_completion_status('WARNING' ,NULL);
186 return;
187 end if;
188
189 -- see if program has already ran. if yes then return
190 if (if_program_already_ran(l_root_request_id)) then
191 BIS_COLLECTION_UTILITIES.put_line('History data has been already collected for ' ||l_root_request_id);
192 l_program_status := fnd_concurrent.set_completion_status('WARNING' ,NULL);
193 return;
194 end if;
195 else
196 l_program_status := fnd_concurrent.set_completion_status('WARNING' ,NULL);
197 BIS_COLLECTION_UTILITIES.put_line('History data will not be collected because either given request id '||Root_request_id|| ' is not valid or Request set has been terminated.');
198 return;
199 end if;
200
201 end if;
202
203
204 -- If request set is of type Gather Statistics only then add row in RS RUN table
205 -- as preparation program has not run
206 if (get_refresh_mode(l_req_set_id,l_req_app_id)= 'ANAL') then
207 add_rsg_rs_run_record(p_request_set_id => l_req_set_id,
208 p_request_set_appl_id => l_req_app_id,
209 p_request_name => l_req_set_name,
210 p_root_req_id => l_root_request_id);
211 end if;
212
213
214 --put stages data into history table
215 for c_stages_rec in c_stages loop
216
217 BIS_RS_STAGE_RUN_HISTORY_PKG.Insert_Row
218 ( p_Request_set_id => c_stages_rec.Request_set_id
219 ,p_Set_app_id => c_stages_rec.Set_app_id
220 ,p_Stage_id => c_stages_rec.Stage_id
221 , p_Request_id => c_stages_rec.Request_id
222 , p_Set_request_id => c_stages_rec.Set_request_id
223 , p_Start_date => c_stages_rec.Start_date
224 , p_Completion_date => c_stages_rec.Completion_date
225 , p_Status_code => c_stages_rec.Status_code
226 , p_phase_code => c_stages_rec.phase_code
227 , p_Creation_date => sysdate
228 , p_Created_by => g_current_user_id
229 , p_Last_update_date => sysdate
230 , p_Last_updated_by => g_current_user_id
231 , p_completion_text => c_stages_rec.completion_text
232 );
233
234
235 end loop;
236
237
238 --put program data into tables.
239 -- Here to find out recursive programs we can use connect by on fnd table as it takes very long time.
240 --Hence we have implemented connect by clause manually
241
242
243 ---put data for gather stats first.
244 open c_get_program_id ;
245 fetch c_get_program_id into l_conc_prog_id;
246 close c_get_program_id;
247
248
249
250
251 for c_programs_rec in c_programs loop
252 l_child_request := true;
253 l_request_id := c_programs_rec.Request_id || ',' || l_request_id;
254 if ( c_programs_rec.program_id = l_conc_prog_id) then
255
256 --get fnd_stats parametes and pass
257 open c_obj_type(c_programs_rec.obj_name);
258 fetch c_obj_type into l_obj_type;
259 close c_obj_type;
260
261 if l_obj_type is null then
262 if (substr(c_programs_rec.obj_name, 1,5) = 'MLOG$') then
263 l_obj_type := 'MV_LOG';
264 else
265 if (substr(c_programs_rec.obj_name , length(c_programs_rec.obj_name)-1,2) = 'MV') then
266 l_obj_type := 'MV';
267 else
268 l_obj_type := 'TABLE';
269 end if;
270
271 end if;
272 end if;
273
274 -- BIS_COLLECTION_UTILITIES.put_line('Adding data for gather stats program '||c_programs_rec.Request_id || ' with stage req id '|| c_programs_rec.Stage_request_id);
275 insert_program_object_data( x_request_id => c_programs_rec.Request_id
276 , x_stage_req_id => c_programs_rec.Stage_request_id
277 , x_object_name => c_programs_rec.obj_name
278 , x_object_type => l_obj_type
279 , x_refresh_type => 'ANALYZED'
280 ,x_set_request_id => l_root_request_id);
281
282 else
283 --BIS_COLLECTION_UTILITIES.put_line('Adding data for program '|| c_programs_rec.Request_id);
284 insert_program_object_data( x_request_id => c_programs_rec.Request_id
285 ,x_stage_req_id => c_programs_rec.Stage_request_id
286 ,x_object_name => null
287 ,x_object_type => null
288 ,x_refresh_type => null
289 ,x_set_request_id => l_root_request_id);
290 end if;
291 l_obj_type := null;
292 end loop;
293
294 --insert data for all the program recursively
295 while ( l_child_request ) loop
296 l_child_request := false;
297 l_parent_request_ids := l_request_id;
298 l_request_id := '';
299 for c_sub_programs_rec in c_sub_programs loop
300 l_child_request := true;
301 l_request_id := to_char(c_sub_programs_rec.Request_id) || ',' || l_request_id;
302 --BIS_COLLECTION_UTILITIES.put_line('Adding data for recursive program ' ||c_sub_programs_rec.Request_id);
303 insert_program_object_data( x_request_id => c_sub_programs_rec.Request_id
304 ,x_stage_req_id => c_sub_programs_rec.stage_req_id
305 ,x_object_name => null
306 ,x_object_type => null
307 ,x_refresh_type => null
308 ,x_set_request_id => l_root_request_id);
309
310 end loop;
311 end loop;
312
313 /** comment out the following code along with enhancement 4247289
314 All Unimplemented MVs being actually processed are recorded into history table
315 by procedure bis_mv_refresh.consider_refresh
316 ---- Update the request ids for the MVs which were marked as consider refresh
317 open c_consider_mv ;
318 fetch c_consider_mv into consider_mv_req;
319 close c_consider_mv ;
320
321 for c_refresh_mv_rec in c_refresh_mv loop
322 if( BIS_OBJ_REFRESH_HISTORY_PKG.Update_Row
323 ( p_Prog_request_id => c_refresh_mv_rec.request_id
324 ,p_new_Prog_request_id => consider_mv_req
325 ,p_Last_update_date => sysdate
326 ,p_Last_updated_by => g_current_user_id )) then
327 BIS_COLLECTION_UTILITIES.put_line('****Updated request for Mvs which were dummy refreshed*********');
328 else
329 BIS_COLLECTION_UTILITIES.put_line('******Update for consider refresh failed********');
330 end if;
331 end loop;
332 **/
333
334 --update request_set table for completion status and date data.
335 update_rs_stage_dates(l_root_request_id);
336
337 -- Get data for the History program (current program)
338 -- and update its stage and program completion date and status.
339 -- run following only if the program is running as the part of the request set
340 if(is_prog_part_of_rs) then
341 OPEN c_get_stage(FND_GLOBAL.conc_request_id);
342 FETCH c_get_stage into l_rs_history_stage;
343 CLOSE c_get_stage;
344
345 if((BIS_RS_PROG_RUN_HISTORY_PKG.Update_Row( p_Set_request_id => l_root_request_id
346 , p_Stage_request_id => l_rs_history_stage
347 , p_Request_id => FND_GLOBAL.conc_request_id
348 , p_Status_code => 'C'
349 , p_Phase_code => 'C'
350 , p_Completion_date => sysdate
351 , p_Last_update_date => sysdate
352 , p_Last_updated_by => g_current_user_id
353 , p_completion_text => g_completion_status))
354 and (BIS_RS_STAGE_RUN_HISTORY_PKG.Update_Row (p_Request_id => l_rs_history_stage
355 , p_Set_request_id =>l_root_request_id
356 , p_Completion_date => sysdate
357 , p_Status_code => 'C'
358 , p_phase_code => 'C'
359 , p_Last_update_date => sysdate
360 , p_Last_updated_by =>g_current_user_id
361 , p_completion_text => g_completion_status))
362 ) then
363
364 --BIS_COLLECTION_UTILITIES.put_line('****Program and stage status for current request updated sucessfully********');
365 p_dummy_flag := 'Y';
366 else
367 BIS_COLLECTION_UTILITIES.put_line('****Failed during setting Program and stage status for current request*********');
368 end if;
369 end if;
370 -- Collect Information such as row count, tablespace name for Objects
371 capture_object_info(l_root_request_id);
372 -- as fnd does not put completion text for requests completing with warning.
376 update_report_date;
373 -- but we will update in our code
374 update_warn_compl_txt(l_root_request_id);
375 -- call API to update report's last_update_date
377 EXCEPTION
378 when others then
379 BIS_COLLECTION_UTILITIES.put_line('Exception happens in RSG History Collection program, ' || sqlerrm);
380 --update request_set table for completion status and date data.
381 update_rs_stage_dates(l_root_request_id);
382 errbuf := sqlerrm;
383 retcode := sqlcode;
384 l_program_status := fnd_concurrent.set_completion_status('WARNING' ,NULL);
385 end rsg_history_report;
386
387 /*
388 Enh#3473874
389 This program purges historical data depending on profile option value.
390 */
391 procedure purgeHistory is
392 history_days number;
393
394 cursor rs_run is
395 select request_id from BIS_RS_RUN_HISTORY
396 where last_update_date <= (sysdate-history_days);
397
398 cursor rs_prog_run(p_set_rq_id number) is
399 select request_id from BIS_RS_PROG_RUN_HISTORY
400 where set_request_id = p_set_rq_id;
401
402 begin
403 history_days := fnd_profile.value('BIS_BIA_REQUESTSET_HISTORY');
404 if (history_days is null ) then
405 history_days := 90;
406 end if;
407
408 for rs_run_rec in rs_run loop
409 --delete object data first
410 for rs_prog_run_rec in rs_prog_run(rs_run_rec.request_id) loop
411 BIS_OBJ_REFRESH_HISTORY_PKG.Delete_Row(rs_prog_run_rec.request_id);
412 end loop;
413
414 -- then delete program data and stage data
415 BIS_RS_PROG_RUN_HISTORY_PKG.Delete_Row(rs_run_rec.request_id);
416 BIS_RS_STAGE_RUN_HISTORY_PKG.Delete_Row(rs_run_rec.request_id);
417 end loop;
418
419 BIS_RS_RUN_HISTORY_PKG.Delete_Row(sysdate-history_days);
420 commit;
421
422 EXCEPTION
423 when others then
424 BIS_COLLECTION_UTILITIES.put_line('Exception happens in purgeHistory ' || sqlerrm);
425 raise;
426 end purgeHistory;
427
428
429 /*
430 Enh#3473874
431 This API Gets called from three places. 1. From MV refresh Program 2. BSC Wrapper 3. History collection
432 This handles following conditions
433 1. If program called with program id alone then it founds objects and inserts
434 2. If program called with both program id and object details then it just inserts
435 3. It always first check if the records is there. If there updates else inserts
436 4. This programs also calls api to get space usage details of every object.
437 */
438 procedure insert_program_object_data (x_request_id IN NUMBER
439 ,x_stage_req_id IN NUMBER
440 ,x_object_name IN VARCHAR2
441 ,x_object_type IN VARCHAR2
442 ,x_refresh_type IN VARCHAR2
443 ,x_set_request_id IN NUMBER)
444 IS
445 request_id number;
446 l_root_request_id number;
447
448 cursor request_id_exists is
449 select 1 from BIS_RS_PROG_RUN_HISTORY
450 where Request_id = x_request_id;
451
452 cursor request_details is
453 select program_application_id,
454 req.concurrent_program_id,
455 Status_code,
456 Phase_code,
457 actual_start_date,
458 actual_completion_date,
459 completion_text,
460 concurrent_program_name
461 from fnd_concurrent_requests req, fnd_concurrent_programs prog
462 where request_id = x_request_id and req.concurrent_program_id = prog.concurrent_program_id
463 and req.program_application_id = prog.application_id;
464
465 req_details_rec request_details%rowtype;
466
467 --for objects data
468 l_Object_row_count number;
469 l_Object_space_usage number;
470 l_Tablespace_name dba_segments.TABLESPACE_NAME%type;
471 l_Free_tablespace_size number;
472
473 cursor c_objects is
474 select linkage.OBJECT_TYPE obj_type,
475 linkage.object_name obj_name,
476 linkage.refresh_mode obj_refresh_mode
477 from BIS_RS_PROG_RUN_HISTORY prog,
478 fnd_concurrent_programs fnd,
479 bis_obj_prog_linkages linkage
480 where prog.request_id = x_request_id
481 and prog.program_id = fnd.CONCURRENT_PROGRAM_ID
482 and prog.prog_app_id = fnd.application_id
483 and linkage.CONC_PROGRAM_NAME = fnd.CONCURRENT_PROGRAM_NAME
484 and linkage.CONC_APP_ID = fnd.application_id
485 and linkage.ENABLED_FLAG = 'Y'
486 and fnd.CONCURRENT_PROGRAM_NAME not in ('BIS_MV_REFRESH','BIS_RSG_PREP','BIS_RSG_FINAL',
487 'BIS_BIA_RSG_VALIDATION','BIS_BIA_RSG_MLOG_CAD',
488 'BIS_MV_DUMMY_REFRESH','BIS_LAST_REFRESH_DATE_CONC',
489 'BIS_BIA_RSG_LOG_MGMNT','FNDGTST','BSC_DELETE_DATA_IND',
490 'BSC_REFRESH_DIM_IND','BSC_REFRESH_SUMMARY_IND','BIS_BIA_STATS_TABLE');
491
492 c_objects_rec c_objects%rowtype;
493
494 BEGIN
495 open request_details;
496 fetch request_details into req_details_rec;
497 close request_details;
498
499 if (req_details_rec.concurrent_program_name = 'BIS_BIA_RS_STATUS_CHK') then
500 return;
501 end if;
502
503 open request_id_exists;
504 fetch request_id_exists into request_id;
505 if (x_request_id is not null) then
506 l_root_request_id := x_set_request_id;
507 if (request_id_exists%NOTFOUND) then
508 BIS_RS_PROG_RUN_HISTORY_PKG.Insert_Row
509 ( p_Set_request_id => l_root_request_id
510 , p_Stage_request_id => x_stage_req_id
514 , p_Status_code => req_details_rec.Status_code
511 , p_Request_id => x_request_id
512 , p_Program_id => req_details_rec.concurrent_program_id
513 , p_Prog_app_id => req_details_rec.program_application_id
515 , p_Phase_code => req_details_rec.Phase_code
516 , p_Start_date => req_details_rec.actual_start_date
517 , p_Completion_date => req_details_rec.actual_completion_date
518 , p_Creation_date => sysdate
519 , p_Created_by => g_current_user_id
520 , p_Last_update_date => sysdate
521 , p_Last_updated_by => g_current_user_id
522 , p_completion_text => req_details_rec.completion_text
523 );
524 else
525 --if request already exists then update status code , completion date and stage id.
526 if (BIS_RS_PROG_RUN_HISTORY_PKG.update_row
527 ( p_Set_request_id => l_root_request_id
528 , p_Stage_request_id => x_stage_req_id
529 , p_Request_id => x_request_id
530 , p_Status_code => req_details_rec.Status_code
531 , p_Phase_code => req_details_rec.Phase_code
532 , p_Completion_date => req_details_rec.actual_completion_date
533 , p_Last_update_date => sysdate
534 , p_Last_updated_by => g_current_user_id
535 , p_completion_text => req_details_rec.completion_text) ) then
536 p_dummy_flag := 'Y';
537 else
538 BIS_COLLECTION_UTILITIES.put_line('***Update failed for request id '||x_request_id);
539 end if;
540 end if;
541 close request_id_exists;
542
543 if ( x_object_name is not null) then
544 -- get space usage details
545 /* Commenting the code here; as this api is called from Multiple programs &
546 * Finding Tablespace, Row Count causes performance issue with programs like MV Refresh
547 * Finally in RSG History Collection, we will find these details for all the objects.
548 * The data collected might not be accurate, but then Product teams are ok with that.
549 */
550 /* get_space_usage_details(p_object_name => x_object_name
551 ,p_Object_type => x_object_type
552 ,p_Object_row_count => l_Object_row_count
553 ,p_Object_space_usage => l_Object_space_usage
554 ,p_Tablespace_name => l_Tablespace_name
555 ,p_Free_tablespace_size => l_Free_tablespace_size);
556 */
557 BIS_OBJ_REFRESH_HISTORY_PKG.Insert_Row(p_Prog_request_id => x_request_id
558 ,p_Object_type => x_object_type
559 ,p_Object_name => x_object_name
560 ,p_Refresh_type => x_refresh_type
561 ,p_Object_row_count => l_Object_row_count
562 ,p_Object_space_usage => l_Object_space_usage
563 ,p_Tablespace_name => l_Tablespace_name
564 ,p_Free_tablespace_size => l_Free_tablespace_size
565 ,p_Creation_date => sysdate
566 ,p_Created_by => g_current_user_id
567 ,p_Last_update_date => sysdate
568 ,p_Last_updated_by => g_current_user_id
569 );
570 else
571 for c_objects_rec in c_objects loop
572 -- get space usage details
573 get_space_usage_details(p_object_name => c_objects_rec.obj_name
574 ,p_Object_type => c_objects_rec.obj_type
575 ,p_Object_row_count => l_Object_row_count
576 ,p_Object_space_usage => l_Object_space_usage
577 ,p_Tablespace_name => l_Tablespace_name
578 ,p_Free_tablespace_size => l_Free_tablespace_size
579 );
580 BIS_OBJ_REFRESH_HISTORY_PKG.Insert_Row(p_Prog_request_id => x_request_id
581 ,p_Object_type => c_objects_rec.obj_type
582 ,p_Object_name => c_objects_rec.obj_name
583 ,p_Refresh_type => c_objects_rec.obj_refresh_mode
584 ,p_Object_row_count => l_Object_row_count
585 ,p_Object_space_usage => l_Object_space_usage
586 ,p_Tablespace_name => l_Tablespace_name
587 ,p_Free_tablespace_size => l_Free_tablespace_size
588 ,p_Creation_date => sysdate
589 ,p_Created_by => g_current_user_id
590 ,p_Last_update_date => sysdate
591 ,p_Last_updated_by => g_current_user_id
595 end if; --request id null
592 );
593 end loop;
594 end if;
596
597 EXCEPTION WHEN OTHERS THEN
598 BIS_COLLECTION_UTILITIES.put_line('Exception happens in insert_program_object_data ' || sqlerrm);
599 raise;
600 END insert_program_object_data;
601
602 /*
603 Enh#3473874
604 This program gets space usage details for given object name
605 */
606 procedure get_space_usage_details( p_object_name IN varchar2,
607 p_Object_type IN varchar2,
608 p_Object_row_count OUT NOCOPY number,
609 p_Object_space_usage OUT NOCOPY number,
610 p_Tablespace_name OUT NOCOPY varchar2,
611 p_Free_tablespace_size OUT NOCOPY number
612 )
613 is
614
615 l_object_type varchar2(30);
616
617 CURSOR cObjInfo_table IS
618 SELECT TABLESPACE_NAME Tblsp
619 FROM all_tables
620 WHERE TABLE_NAME = p_object_name
621 and OWNER = BIS_CREATE_REQUESTSET.get_object_owner(p_object_name,l_object_type);
622
623 /* Moving the code to find out the free tablespace out of this api
624 CURSOR cFreeTableSpace IS
625 SELECT SUM(bytes) FreeTablespace
626 FROM dba_free_Space fs
627 WHERE fs.tablespace_name = p_Tablespace_name ;
628 */
629 CURSOR cObjInfo_part IS
630 select PARTITION_NAME , TABLESPACE_NAME
631 from all_tab_partitions
632 where table_name = p_object_name
633 and table_owner = BIS_CREATE_REQUESTSET.get_object_owner(p_object_name,l_object_type);
634
635 TYPE curType IS REF CURSOR;
636 cRowCount curType;
637
638 l_stmt VARCHAR2(1000);
639 l_row_count INTEGER;
640
641
642 p_total_blocks number;
643 p_total_bytes number;
644 p_unused_blocks number;
645 p_unused_bytes number;
646 p_last_used_extent_file_id number;
647 p_last_used_extent_block_id number;
648 p_last_used_block number;
649
650 begin
651
652 p_Object_space_usage := 0;
653
654 IF p_Object_type = 'MV_LOG' then
655 l_object_type := 'TABLE';
656 ELSE
657 l_object_type := p_Object_type;
658 END IF;
659
660
661 IF (l_object_type not in ('TABLE','MV')) THEN
662 p_Object_row_count := null;
663 p_Object_space_usage := null;
664 p_Tablespace_name := null;
665 p_Free_tablespace_size := null;
666 ELSE
667 -- get tablespace name associated to the object
668
669 for cObjInfo_table_rec in cObjInfo_table loop
670 p_Tablespace_name := cObjInfo_table_rec.Tblsp;
671 end loop;
672
673 if (p_Tablespace_name is null) then -- if object is partitioned.
674 BIS_COLLECTION_UTILITIES.put_line(p_object_name ||' is a partitioned object');
675 for cObjInfo_part_rec in cObjInfo_part loop
676 p_Tablespace_name := cObjInfo_part_rec.TABLESPACE_NAME;
677
678 dbms_space.unused_space (BIS_CREATE_REQUESTSET.get_object_owner(p_object_name,l_object_type),
679 p_object_name,
680 'TABLE PARTITION',
681 p_total_blocks,
682 p_total_bytes,
683 p_unused_blocks,
684 p_unused_bytes,
685 p_last_used_extent_file_id,
686 p_last_used_extent_block_id,
687 p_last_used_block,
688 cObjInfo_part_rec.PARTITION_NAME);
689
690 p_Object_space_usage := p_Object_space_usage + p_total_bytes;
691 end loop;
692
693
694 else
695 -- get object free space
696 dbms_space.unused_space (BIS_CREATE_REQUESTSET.get_object_owner(p_object_name,l_object_type),
697 p_object_name,
698 'TABLE',
699 p_total_blocks,
700 p_total_bytes,
701 p_unused_blocks,
702 p_unused_bytes,
703 p_last_used_extent_file_id,
704 p_last_used_extent_block_id,
705 p_last_used_block);
706
707 p_Object_space_usage := p_total_bytes;
708 end if;
709
710 -- Bug#5195936 :: The query to find out the free table space is causing performance issue for product teams.
711 --get tablespace free space
712 /* for cFreeTableSpace_rec in cFreeTableSpace loop
713 p_Free_tablespace_size := cFreeTableSpace_rec.FreeTablespace;
714 end loop;
715 */
716 p_Free_tablespace_size := null;
717 begin
718 --get object row count
719 l_stmt := 'SELECT COUNT(*) FROM '|| p_object_name;
720 OPEN cRowCount FOR l_stmt;
721 FETCH cRowCount INTO l_row_count;
722 p_Object_row_count := l_row_count;
723 CLOSE cRowCount;
724 exception
725 when others then
726 p_Object_row_count := null;
727 end;
728
729
730 END IF;
731
732 EXCEPTION WHEN OTHERS THEN
733 BIS_COLLECTION_UTILITIES.put_line('Exception happens in get_space_usage_details for object '||p_object_name|| ' ' || sqlerrm);
734 raise;
735
736 end get_space_usage_details;
737
738 /*
739 Enh#3473874
740 This inserts the record in the BIS_RS_RUN_HISTORY table.
741 This is called from history collection program if request set is of type gather statistics only
742 for all the case it will be called from preparation program
743 */
744
745 PROCEDURE add_rsg_rs_run_record(p_request_set_id IN NUMBER,
746 p_request_set_appl_id IN NUMBER,
747 p_request_name IN VARCHAR2,
748 p_root_req_id IN NUMBER)
749 IS
750 l_refresh_mode varchar2(30);
751 l_force_full_refresh varchar2(30);
752 l_request_set_id number;
753 l_rs_phase_code fnd_concurrent_requests.PHASE_CODE%type;
754 l_rs_status_code fnd_concurrent_requests.STATUS_CODE%type;
755 l_completion_text fnd_concurrent_requests.completion_text%type;
756
757 cursor request_set_details is
758 select PHASE_CODE ,STATUS_CODE,completion_text from fnd_concurrent_requests
759 where request_id = p_root_req_id;
760
761 BEGIN
762 l_refresh_mode :=
763 CASE get_refresh_mode(p_request_set_id,p_request_set_appl_id)
764 WHEN 'ANAL' THEN 'GATHER_STATS'
765 WHEN 'INIT' THEN 'INIT_LOAD'
766 ELSE 'INCR_LOAD'
767 END;
768
769
770 open request_set_details;
771 fetch request_set_details into l_rs_phase_code,l_rs_status_code,l_completion_text;
772 close request_set_details;
773
774 BIS_RS_RUN_HISTORY_PKG.Insert_Row(
775 p_Request_set_id => p_request_set_id
776 , p_Set_app_id => p_request_set_appl_id
777 , p_request_set_name=> p_request_name
778 , p_Request_id => p_root_req_id
779 , p_rs_refresh_type => l_refresh_mode
780 , p_Start_date => sysdate
781 , p_Completion_date => null
782 , p_Status_code => l_rs_status_code
783 , p_Phase_code => l_rs_phase_code
784 , p_Creation_date => sysdate
785 , p_Created_by => g_current_user_id
786 , p_Last_update_date => sysdate
787 , p_Last_updated_by => g_current_user_id
788 , p_completion_Text => l_completion_text
789 );
790
791 EXCEPTION WHEN OTHERS THEN
792 BIS_COLLECTION_UTILITIES.put_line('Exception happens in add_rsg_rs_run_record ' || sqlerrm);
793 raise;
794
795 END add_rsg_rs_run_record;
796
797 /*
798 Enh#3473874
799 This gets the refresh mode of request set.
800 */
801
802 FUNCTION get_refresh_mode(p_request_set_id IN NUMBER,
803 p_request_set_appl_id IN NUMBER) RETURN VARCHAR2
804 IS
805
806 cursor refresh_mode is
807 select option_value
808 from bis_request_set_options
809 where request_set_name=( select request_set_name from fnd_request_sets
810 where request_set_id = p_request_set_id
811 and application_id = p_request_set_appl_id)
812 and SET_APP_ID=p_request_set_appl_id
813 and option_name='REFRESH_MODE';
814
815 cursor analyze_object is
816 select option_value
817 from bis_request_set_options
818 where request_set_name=( select request_set_name from fnd_request_sets
819 where request_set_id = p_request_set_id
820 and application_id = p_request_set_appl_id)
821 and SET_APP_ID=p_request_set_appl_id
822 and option_name='ANALYZE_OBJECT';
823
824 l_refresh_mode refresh_mode%rowtype;
825 l_analyze_object analyze_object%rowtype;
826 BEGIN
827 OPEN refresh_mode;
828 FETCH refresh_mode INTO l_refresh_mode;
829 CLOSE refresh_mode;
830
831 if (l_refresh_mode.option_value is null) then
832 OPEN analyze_object;
833 FETCH analyze_object INTO l_analyze_object;
834 CLOSE analyze_object;
835 if (l_analyze_object.option_value ='Y') then
836 RETURN 'ANAL';
837 end if;
838 end if;
839 RETURN l_refresh_mode.option_value;
840
841 EXCEPTION WHEN OTHERS THEN
842 BIS_COLLECTION_UTILITIES.put_line('Exception happens in get_refresh_mode ' || sqlerrm);
843 raise;
844 END get_refresh_mode;
845
846 /**********
847 Enh#3473874 This function will check is the data has been already collected for some particular request set.
848 *********/
849
850 FUNCTION if_program_already_ran(l_root_request_id IN NUMBER ) RETURN BOOLEAN IS
851 cursor if_program_ran is
852 select 1 from BIS_RS_RUN_HISTORY
853 where REQUEST_ID = l_root_request_id and PHASE_CODE = 'C';
854 prog_ran_rec if_program_ran%rowtype;
855
856 l_program_already_ran boolean := false;
857
858 BEGIN
859
860 for prog_ran_rec in if_program_ran loop
861 l_program_already_ran := true;
862 end loop;
863
864 return l_program_already_ran;
865
866 EXCEPTION WHEN OTHERS THEN
867 BIS_COLLECTION_UTILITIES.put_line('Exception happens in if_program_already_ran ' || sqlerrm);
868 raise;
869 END if_program_already_ran;
870
871
872 /**********
873 Enh#3473874 This procedure will give requestset id and appln id given a request id.
874 *********/
878 p_root_request_id IN NUMBER ) RETURN BOOLEAN IS
875 FUNCTION get_req_set_details(p_request_set_id OUT NOCOPY NUMBER,
876 p_request_set_appl_id OUT NOCOPY NUMBER,
877 p_request_set_name OUT NOCOPY VARCHAR2,
879
880
881 cursor is_req_valid(p_req_id number) is
882 select
883 req.argument1,
884 req.argument2,
885 req.status_code,
886 req.phase_code
887 from
888 fnd_concurrent_requests req
889 where
890 req.request_id = p_req_id
891 and req.argument4 is null;
892
893 cursor get_rs_details is
894 select request_set_name from
895 fnd_request_sets
896 where application_id = p_request_set_appl_id and request_set_id = p_request_set_id;
897
898 get_rs_details_rec get_rs_details%rowtype;
899
900 l_status_code fnd_concurrent_requests.status_code%type;
901 l_phase_code fnd_concurrent_requests.phase_code%type;
902
903 BEGIN
904
905 open is_req_valid(p_root_request_id);
906 fetch is_req_valid into p_request_set_appl_id,p_request_set_id,l_status_code,l_phase_code;
907 if(l_phase_code ='C' and l_status_code ='X') then
908 return false;
909 end if;
910 close is_req_valid;
911
912 --Added for bug 4184138
913 for get_rs_details_rec in get_rs_details loop
914 p_request_set_name := get_rs_details_rec.request_set_name;
915 return true;
916 end loop;
917
918 return false;
919
920 EXCEPTION
921 WHEN OTHERS THEN
922 return false;
923 END get_req_set_details;
924
925 /******
926 FND does not put Completion Text for the programs or stages which completed with warning
927 But we need to update the same in out tables otherwise it will N/A in out reports.
928 This procedure updted the completion text to "Completed with Error" for all the programs and stages
929 which comepleted with status as "Warning"
930 ********/
931 PROCEDURE update_warn_compl_txt(p_root_req_id IN NUMBER) IS
932
933 cursor check_in_progs(root_req_id number) is
934 select request_id ,STAGE_REQUEST_ID from BIS_RS_PROG_RUN_HISTORY
935 where SET_REQUEST_ID = root_req_id and
936 status_code = 'G' ;
937
938 check_in_progs_rec check_in_progs%rowtype;
939
940 cursor check_in_stages(root_req_id number) is
941 select request_id from BIS_RS_STAGE_RUN_HISTORY
942 where SET_REQUEST_ID = root_req_id and
943 status_code = 'G' ;
944 check_in_stages_rec check_in_stages%rowtype;
945
946 BEGIN
947 for check_in_progs_rec in check_in_progs(p_root_req_id) loop
948 if(BIS_RS_PROG_RUN_HISTORY_PKG.Update_Row
949 ( p_Set_request_id => p_root_req_id,
950 p_Stage_request_id => check_in_progs_rec.STAGE_REQUEST_ID,
951 p_Request_id => check_in_progs_rec.request_id,
952 p_Last_update_date => sysdate,
953 p_Last_updated_by => g_current_user_id,
954 p_completion_text => g_warning_status)) then
955
956 --BIS_COLLECTION_UTILITIES.put_line('Updated warning completion text ');
957 p_dummy_flag := 'Y';
958 else
959 BIS_COLLECTION_UTILITIES.put_line('Updating warning completion text for programs failed');
960 end if;
961 end loop;
962
963 for check_in_stages_rec in check_in_stages(p_root_req_id) loop
964 if(BIS_RS_STAGE_RUN_HISTORY_PKG.Update_Row
965 (p_Request_id => check_in_stages_rec.request_id ,
966 p_Set_request_id => p_root_req_id,
967 p_Last_update_date => sysdate,
968 p_Last_updated_by => g_current_user_id,
969 p_completion_text => g_warning_status) ) then
970
971 --BIS_COLLECTION_UTILITIES.put_line('Updated warning completion text for satges');
972 p_dummy_flag := 'Y';
973 else
974 BIS_COLLECTION_UTILITIES.put_line('Updating warning completion text for stages failed');
975 end if;
976 end loop;
977
978
979 EXCEPTION
980 WHEN OTHERS THEN
981 BIS_COLLECTION_UTILITIES.put_line('Exception happens in update_warning_completion_text ' || sqlerrm);
982 raise;
983 END update_warn_compl_txt;
984
985 /*
986 We can not update the status for the request sets which were terminated as
987 our history collection program will not be called at all. And the entry will be always "Running". This will
988 affect our Page refresh status report.
989 Hence we will call this API in preparation program and Page refresh status program.
990 This API will update status for the request set which were terminated.
991 */
992 PROCEDURE update_terminated_rs is
993
994 cursor terminate_rs is
995 select request_id from BIS_RS_RUN_HISTORY
996 where phase_code ='R' ;
997 terminate_rs_rec terminate_rs%rowtype;
998
999 cursor req_details(req_no number) is
1000 select ACTUAL_COMPLETION_DATE,
1001 phase_code,
1002 status_code,
1003 completion_text
1004 from
1005 fnd_concurrent_requests
1006 where
1007 request_id =req_no and
1008 phase_code ='C' and
1009 status_code ='X';
1010
1011 req_details_rec req_details%rowtype;
1012
1013 BEGIN
1014 for terminate_rs_rec in terminate_rs loop
1015 for req_details_rec in req_details(terminate_rs_rec.request_id) loop
1016 if(BIS_RS_RUN_HISTORY_PKG.Update_Row( p_Request_id => terminate_rs_rec.request_id
1017 ,p_Completion_date => req_details_rec.ACTUAL_COMPLETION_DATE
1018 ,p_Phase_code => req_details_rec.phase_code
1019 ,p_Status_code => req_details_rec.status_code
1020 ,p_Last_update_date => sysdate
1021 ,p_Last_updated_by => g_current_user_id
1025 else
1022 ,p_Completion_text => req_details_rec.completion_text)) then
1023 --BIS_COLLECTION_UTILITIES.put_line('*****Updated Terminated Req sets with request id ' || terminate_rs_rec.request_id);
1024 p_dummy_flag := 'Y';
1026 BIS_COLLECTION_UTILITIES.put_line('************Updation of Terminated Request sets failed*****************');
1027 end if;
1028 end loop;
1029 end loop;
1030 EXCEPTION
1031 WHEN OTHERS THEN
1032 BIS_COLLECTION_UTILITIES.put_line('Exception happens in update_terminated_rs ' || sqlerrm);
1033 raise;
1034 END update_terminated_rs;
1035
1036
1037 /* This procedure updates the correct start time and end time for all the stages and request set.*/
1038 PROCEDURE update_rs_stage_dates(p_root_req_id IN NUMBER) IS
1039
1040 --for updating request set status
1041 cursor c_request_status is
1042 select status_code from BIS_RS_PROG_RUN_HISTORY
1043 where set_request_id =p_root_req_id
1044 and (status_code ='E' or status_code ='G')
1045 --added for Bug 4173989
1046 union
1047 select status_code from BIS_RS_STAGE_RUN_HISTORY
1048 where set_request_id = p_root_req_id
1049 and (status_code ='E' or status_code ='G') ;
1050
1051 cursor c_get_rs_start_time is
1052 select min(stg.start_date) start_date
1053 from bis_rs_stage_run_history stg
1054 where SET_REQUEST_ID = p_root_req_id;
1055
1056 rs_start_date date;
1057
1058 cursor c_stages is
1059 select request_id
1060 from bis_rs_stage_run_history stg
1061 where SET_REQUEST_ID = p_root_req_id;
1062
1063 cursor c_stages_dates(stage_req_id number) is
1064 select min(START_DATE) stage_start_date, max(COMPLETION_DATE) stage_com_date
1065 from bis_rs_prog_run_history
1066 where STAGE_REQUEST_ID =stage_req_id;
1067
1068 is_request_err boolean :=false;
1069 is_request_warn boolean :=false;
1070 l_status_code BIS_RS_RUN_HISTORY.status_code%type;
1071 l_Completion_text varchar2(2000);
1072 l_stage_start_date Date;
1073 l_stage_end_date Date;
1074 BEGIN
1075
1076 --compute stage start and end time depending upon programs within it.
1077
1078 for c_stages_rec in c_stages loop
1079 for c_stages_dates_rec in c_stages_dates(c_stages_rec.request_id) loop
1080 l_stage_start_date := c_stages_dates_rec.stage_start_date;
1081 l_stage_end_date := c_stages_dates_rec.stage_com_date;
1082 end loop;
1083 if (BIS_RS_STAGE_RUN_HISTORY_PKG.Update_Row(p_Request_id => c_stages_rec.request_id
1084 , p_Set_request_id => p_root_req_id
1085 , p_start_date => l_stage_start_date
1086 , p_Completion_date => l_stage_end_date
1087 , p_Last_update_date => sysdate
1088 , p_Last_updated_by =>g_current_user_id)) then
1089 p_dummy_flag := 'Y';
1090 else
1091 BIS_COLLECTION_UTILITIES.put_line('Stage updation for start date and end date failed for stage with request id ' ||c_stages_rec.request_id );
1092 end if;
1093
1094
1095 end loop;
1096 --compute request status depending upon the porgram and stage status
1097 for c_request_status_rec in c_request_status loop
1098 if c_request_status_rec.status_code = 'E' then
1099 is_request_err := true;
1100 else
1101 is_request_warn := true;
1102 end if;
1103 end loop;
1104
1105 if(is_request_err) then
1106 l_status_code := 'E';
1107 l_Completion_text := g_error_status;
1108 else
1109 if( is_request_warn) then
1110 l_status_code := 'G';
1111 l_Completion_text := g_warning_status;
1112 else
1113 l_status_code := 'C';
1114 l_Completion_text := g_completion_status;
1115 end if;
1116 end if;
1117
1118 --compute request set start and end time depending upon stage start time and completion time.
1119 OPEN c_get_rs_start_time;
1120 FETCH c_get_rs_start_time INTO rs_start_date;
1121 CLOSE c_get_rs_start_time;
1122
1123 if(BIS_RS_RUN_HISTORY_PKG.Update_Row( p_Request_id => p_root_req_id
1124 ,p_start_date => rs_start_date
1125 ,p_Completion_date => sysdate
1126 ,p_Phase_code => 'C'
1127 ,p_Status_code => l_status_code
1128 ,p_Last_update_date => sysdate
1129 ,p_Last_updated_by =>g_current_user_id
1130 ,p_Completion_text => l_Completion_text)) then
1131 BIS_COLLECTION_UTILITIES.put_line('************History Data for this request set is collected sucessfully');
1132 else
1133 BIS_COLLECTION_UTILITIES.put_line('************Updation of Request Set status failed*****************');
1134 end if;
1135
1136 EXCEPTION
1137 WHEN OTHERS THEN
1138 BIS_COLLECTION_UTILITIES.put_line('Exception happens in update_rs_stage_dates ' || sqlerrm);
1139 raise;
1140 END update_rs_stage_dates;
1141
1142 /*
1143 We need to have last_update_date at the bottom of the report for all our reports.
1144 This API updates these seeded report's last_update_date in bis_obj_properties table.
1145 */
1146 PROCEDURE update_report_date IS
1147
1148 BEGIN
1149 bis_impl_dev_pkg.update_obj_last_refresh_date('REPORT','BIS_BIA_RSG_REQ_DETAILS_PGE',sysdate);
1150 bis_impl_dev_pkg.update_obj_last_refresh_date('REPORT','BIS_BIA_RSG_SETS_DET_PGE',sysdate);
1151 bis_impl_dev_pkg.update_obj_last_refresh_date('REPORT','BIS_BIA_RSG_SETS_LVL_PGE',sysdate);
1152 bis_impl_dev_pkg.update_obj_last_refresh_date('REPORT','BIS_BIA_RSG_SPACE_DET_PGE',sysdate);
1153 bis_impl_dev_pkg.update_obj_last_refresh_date('REPORT','BIS_BIA_RSG_SUB_REQS_PGE',sysdate);
1154 bis_impl_dev_pkg.update_obj_last_refresh_date('REPORT','BIS_BIA_RSG_TABLESPACE_PGE',sysdate);
1155
1156 EXCEPTION
1157 WHEN OTHERS THEN
1158 BIS_COLLECTION_UTILITIES.put_line('Exception happens in update_report_date ' || sqlerrm);
1159 raise;
1160 END update_report_date;
1161
1162 /*
1163 * Bug#5195936 - For performance reasons, free tablespace & Row Count is no longer calculated while
1164 * inserting object data. Hence this api to populate this information in BIS_OBJ_REFRESH_HISTORY
1165 */
1166 PROCEDURE capture_object_info(p_root_request_id IN NUMBER) IS
1167 CURSOR get_objects IS
1168 SELECT object_name, object_type, prog_Request_id
1169 FROM BIS_RS_PROG_RUN_HISTORY prog, BIS_OBJ_REFRESH_HISTORY obj
1170 WHERE set_request_id = p_root_request_id AND obj.prog_request_id = prog.request_id;
1171
1172 --for objects data
1173 l_Object_row_count NUMBER;
1174 l_Object_space_usage NUMBER;
1175 l_Tablespace_name dba_segments.TABLESPACE_NAME%type;
1176 l_Free_tablespace_size NUMBER;
1177 l_success BOOLEAN;
1178 BEGIN
1179 BIS_COLLECTION_UTILITIES.put_line('Started :: Updating Object Details');
1180 FOR get_objects_rec IN get_objects LOOP
1181 get_space_usage_details(p_object_name => get_objects_rec.object_name
1182 ,p_Object_type => get_objects_rec.object_type
1183 ,p_Object_row_count => l_Object_row_count
1184 ,p_Object_space_usage => l_Object_space_usage
1185 ,p_Tablespace_name => l_Tablespace_name
1186 ,p_Free_tablespace_size => l_Free_tablespace_size);
1187
1188 l_success := BIS_OBJ_REFRESH_HISTORY_PKG.UPDATE_ROW(p_Prog_request_id => get_objects_rec.prog_Request_id
1189 ,p_Object_type => get_objects_rec.object_type
1190 ,p_Object_name => get_objects_rec.object_name
1191 ,p_Object_row_count => l_Object_row_count
1192 ,p_Object_space_usage => l_Object_space_usage
1193 ,p_Tablespace_name => l_Tablespace_name
1194 ,p_Free_tablespace_size => l_Free_tablespace_size
1195 ,p_Last_update_date => sysdate
1196 ,p_Last_updated_by => g_current_user_id);
1197 IF (NOT l_success) THEN
1198 BIS_COLLECTION_UTILITIES.put_line('Failed to Update Row for Object Refresh History in capture_object_info()');
1199 END IF;
1200 END LOOP;
1201 BIS_COLLECTION_UTILITIES.put_line('Completed :: Updating Object Details');
1202 EXCEPTION
1203 WHEN OTHERS THEN
1204 BIS_COLLECTION_UTILITIES.put_line('Error in capture_object_info :: '||sqlerrm);
1205 RAISE;
1206 END capture_object_info;
1207
1208
1209 END BIS_COLL_RS_HISTORY;
[Home] [Help]