[Home] [Help]
PACKAGE BODY: APPS.BIS_MV_REFRESH
Source
1 PACKAGE BODY BIS_MV_REFRESH AS
2 /* $Header: BISMVRFB.pls 120.10.12000000.5 2007/10/10 10:13:30 phattarg ship $ */
3
4 G_ERRBUF VARCHAR2(2000) := NULL;
5 G_ERRCODE NUMBER := 0;
6 g_program_status boolean :=true;
7 g_program_status_var varchar2(100) := 'NORMAL';
8
9
10 g_apps_schema_name varchar2(30);
11
12
13
14 PROCEDURE DEBUG(P_TEXT VARCHAR2, P_IDENT NUMBER DEFAULT 0)
15 IS
16 BEGIN
17 BIS_COLLECTION_UTILITIES.debug(P_TEXT, P_IDENT);
18 END;
19
20 procedure logmsg(P_TEXT in VARCHAR2) is
21 begin
22
23 BIS_COLLECTION_UTILITIES.put_line(P_TEXT);
24 -- dbms_output.put_line(substr(P_TEXT,1,255));
25 end;
26
27 FUNCTION duration(
28 p_duration number) return VARCHAR2 IS
29 BEGIN
30 return(to_char(floor(p_duration)) ||' Days '||
31 to_char(mod(floor(p_duration*24), 24))||':'||
32 to_char(mod(floor(p_duration*24*60), 60))||':'||
33 to_char(mod(floor(p_duration*24*60*60), 60)));
34 END duration;
35
36 PROCEDURE setTimer(
37 p_log_timstamp in out NOCOPY date)
38 IS
39 BEGIN
40 p_log_timstamp := sysdate;
41 END;
42
43
44 PROCEDURE logTime(
45 p_process varchar2,
46 p_log_timstamp date)
47 IS
48 l_duration number := null;
49 BEGIN
50 l_duration := sysdate - p_log_timstamp;
51 BIS_COLLECTION_UTILITIES.put_line('Process Time for '|| p_process || ' : ' || duration(l_duration));
52 BIS_COLLECTION_UTILITIES.put_line('');
53 END;
54
55 FUNCTION get_Table_size(p_obj_owner IN VARCHAR2
56 ,p_log_table IN VARCHAR2)
57 RETURN NUMBER
58 IS
59 op1 NUMBER;
60 total_bytes NUMBER;
61 op3 NUMBER;
62 op4 NUMBER;
63 op5 NUMBER;
64 op6 NUMBER;
65 op7 NUMBER;
66 BEGIN
67 total_bytes := 0;
68 BEGIN
69 Dbms_Space.Unused_Space(p_obj_owner, p_log_table, 'TABLE',op1,total_bytes,op3,op4,op5,op6,op7);
70 EXCEPTION
71 WHEN OTHERS THEN
72 BIS_COLLECTION_UTILITIES.put_line('Error in get_Table_size('|| p_obj_owner || ',' || p_log_table || sqlerrm);
73 END;
74 return total_bytes;
75 END;
76
77 FUNCTION get_apps_schema_name RETURN VARCHAR2 IS
78
79 l_apps_schema_name VARCHAR2(30);
80
81 CURSOR c_apps_schema_name IS
82 SELECT oracle_username
83 FROM fnd_oracle_userid WHERE oracle_id
84 BETWEEN 900 AND 999 AND read_only_flag = 'U';
85 BEGIN
86
87 OPEN c_apps_schema_name;
88 FETCH c_apps_schema_name INTO l_apps_schema_name;
89 CLOSE c_apps_schema_name;
90 RETURN l_apps_schema_name;
91
92 EXCEPTION
93 WHEN OTHERS THEN
94 RETURN NULL;
95 END get_apps_schema_name;
96
97 PROCEDURE RECOMPILE_MV(
98 P_MVNAME IN VARCHAR2
99 ) IS
100 l_stmt varchar2(2000);
101 l_errbuf varchar2(2000);
102 l_retcode number;
103 l_compile_state all_mviews.compile_state%type;
104
105 CURSOR C_MV_CSTATE ( P_MVNAME bis_obj_dependency.object_name%TYPE, p_apps_schema_name varchar2 )
106 IS
107 select NVL(compile_state, 'NA')
108 from all_mviews
109 where mview_name = p_mvname
110 AND owner = p_apps_schema_name
111 UNION ALL
112 SELECT NVL(compile_state, 'NA')
113 FROM all_mviews mvs, user_synonyms s
114 WHERE mvs.owner = s.table_owner
115 AND mvs.mview_name = s.table_name
116 AND mview_name = p_mvname;
117
118 BEGIN
119 DEBUG('Compiling MV ' || P_MVNAME);
120 l_stmt := 'alter materialized view ' || P_MVNAME || ' compile';
121 execute immediate l_stmt;
122
123 IF (g_apps_schema_name IS NULL) THEN
124 g_apps_schema_name := get_apps_schema_name;
125 END IF;
126
127 OPEN C_MV_CSTATE(p_mvname, g_apps_schema_name);
128 fetch C_MV_CSTATE into l_compile_state;
129 CLOSE C_MV_CSTATE;
130 BIS_COLLECTION_UTILITIES.put_line('MV compile state : ' || l_compile_state || '.');
131
132 EXCEPTION WHEN OTHERS THEN
133 l_errbuf :=sqlerrm;
134 l_retcode:=sqlcode;
135 DEBUG('Failed MV compiling, ' || l_errbuf);
136
137 END;
138
139 -- added for enhancement 3022739, MV threshold at runtime.
140 FUNCTION GET_CUSTOMAPI (p_mvname VARCHAR2) RETURN VARCHAR2
141 IS
142 L_CUSTOM_API bis_obj_properties.custom_api%type := NULL;
143 CURSOR C_CUSTOM_API ( P_MVNAME bis_obj_dependency.object_name%type )
144 IS
145 select CUSTOM_API from bis_obj_properties
146 where object_name = p_mvname;
147 BEGIN
148 OPEN C_CUSTOM_API(p_mvname);
149 fetch C_CUSTOM_API into L_CUSTOM_API;
150 CLOSE C_CUSTOM_API;
151 RETURN L_CUSTOM_API;
152 END;
153
154 -- added for enhancement 3022739, MV threshold at runtime.
155 FUNCTION GET_MV_THRESHOLDED_AT_RUNTIME (p_mvname VARCHAR2) RETURN VARCHAR2
156 IS
157 l_rtnbuf VARCHAR2(32767);
158 l_retcode VARCHAR2(32767);
159 L_CUSTOM_API bis_obj_properties.custom_api%type := NULL;
160 BEGIN
161 L_CUSTOM_API:= GET_CUSTOMAPI (p_mvname);
162 IF (L_CUSTOM_API is NOT NULL) THEN
163 BIS_COLLECTION_UTILITIES.put_line('Before invoke custom API for MV threshold management: (' || p_mvname || ')');
164 -- Now call the stored program
165 l_rtnbuf:=BIS_BIA_RSG_CUSTOM_API_MGMNT.METHOD_FAST;--added for enhancement 4423644
166 BIS_BIA_RSG_CUSTOM_API_MGMNT.INVOKE_CUSTOM_API(
167 l_rtnbuf, l_retcode,
168 L_CUSTOM_API, BIS_BIA_RSG_CUSTOM_API_MGMNT.TYPE_MV_THRESHOLD,
169 p_mvname, 'MV', BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_BEFORE);
170 --- BIS_BIA_RSG_CUSTOM_API_MGMNT.log( 'Got refresh threshold for ' || p_mvname || ': '|| l_rtnbuf);
171 BIS_COLLECTION_UTILITIES.put_line('After invoke custom API for MV threshold management : (' || p_mvname ||','||l_rtnbuf|| ')');
172
173 IF ( l_rtnbuf = BIS_BIA_RSG_CUSTOM_API_MGMNT.METHOD_COMPLETE) THEN
174 RETURN 'INIT';
175 ELSIF ( l_rtnbuf = BIS_BIA_RSG_CUSTOM_API_MGMNT.METHOD_FAST) THEN
176 RETURN 'INCR';
177 END IF;
178 RETURN l_rtnbuf;
179 ELSE
180 RETURN NULL; -- meaning that no CUSTOME_API defined, and therefore no THRESHOLDING
181 END IF;
182 EXCEPTION WHEN OTHERS THEN
183 BIS_COLLECTION_UTILITIES.put_line('Errored in BIS_MV_REFRESH.GET_MV_THRESHOLDED_AT_RUNTIME');
184 RAISE; -- raise whatever exceptions, and fails the process.
185 END;
186
187 -- added for enhancement 3034322, MV index management at runtime.
188 PROCEDURE MV_INDEX_MANAGE(p_mvname VARCHAR2, p_mode VARCHAR2)
189 IS
190 l_rtnbuf VARCHAR2(32767);
191 l_retcode VARCHAR2(32767);
192 L_CUSTOM_API bis_obj_properties.custom_api%type := NULL;
193 BEGIN
194 L_CUSTOM_API:= GET_CUSTOMAPI (p_mvname);
195 IF (L_CUSTOM_API is NOT NULL) THEN
196 -- Now call the stored program
197 BIS_COLLECTION_UTILITIES.put_line('Before invoke custom API for MV index management : (' || p_mvname || ', ' || p_mode || ')');
198 l_rtnbuf:= BIS_BIA_RSG_CUSTOM_API_MGMNT.METHOD_COMPLETE;
199 BIS_BIA_RSG_CUSTOM_API_MGMNT.INVOKE_CUSTOM_API(
200 l_rtnbuf, l_retcode,
201 L_CUSTOM_API, BIS_BIA_RSG_CUSTOM_API_MGMNT.TYPE_MV_INDEX_MGT,
202 p_mvname, 'MV', p_mode);
203 BIS_COLLECTION_UTILITIES.put_line('After invoke custom API for MV index management: (' || p_mvname || ', ' || p_mode || ')');
204 END IF;
205 EXCEPTION WHEN OTHERS THEN
206 BIS_COLLECTION_UTILITIES.put_line('Errored in BIS_MV_REFRESH.MV_INDEX_MANAGE');
207 RAISE; -- raise whatever exceptions, and fails the process.
208 END;
209
210
211 -- added for enhancement 3748713, BIS MV Log Management at runtime.
212 PROCEDURE MV_LOG_MANAGE(p_mvname VARCHAR2, p_mode VARCHAR2)
213 IS
214 l_root_request_id INTEGER;
215 l_rs_name varchar2(500);
216 l_is_force_full varchar2(10) := null;
217 l_schema varchar2(500);
218 cursor c_force_full(p_request_id INTEGER ) IS
219 -- aguwalan
220 select distinct
221 sets.request_set_name,
222 opt.option_value
223 from
224 fnd_run_requests req,
225 fnd_request_sets sets,
226 bis_request_set_options opt
227 where
228 req.parent_request_id = p_request_id
229 and sets.request_set_id = req.request_set_id
230 and sets.application_id = req.set_application_id
231 /* remove the following predicates for the assumptin on
232 * application id = 191 might not be true*/
233 --and req.application_id = 191
234 --and sets.application_id = 191
235 --and req.application_id = sets.application_id
236 and opt.request_set_name = sets.request_set_name
237 and opt.option_name = 'FORCE_FULL';
238
239 BEGIN
240 l_root_request_id := FND_GLOBAL.CONC_PRIORITY_REQUEST;
241 BIS_COLLECTION_UTILITIES.put_line('FND_GLOBAL.CONC_PRIORITY_REQUEST: ' || l_root_request_id);
242 open c_force_full(l_root_request_id);
243 fetch c_force_full into l_rs_name, l_is_force_full;
244 close c_force_full;
245 BIS_COLLECTION_UTILITIES.put_line('Request Set: ' || l_rs_name || ', ' || 'FORCE_FULL option: ' || l_is_force_full);
246 l_schema := BIS_CREATE_REQUESTSET.get_object_owner(p_mvname, 'MV');
247
248 if( p_mode = 'INIT' and l_is_force_full='Y' ) then
249 BIS_BIA_RSG_LOG_MGMNT.create_mv_log(p_mvname, l_schema, 'MV');
250 else
251 BIS_COLLECTION_UTILITIES.put_line('No action for MV Log recreation.');
252 end if;
253
254 EXCEPTION WHEN OTHERS THEN
255 close c_force_full;
256 BIS_COLLECTION_UTILITIES.put_line('Errored in BIS_MV_REFRESH.MV_LOG_RECREATE');
257 RAISE; -- raise whatever exceptions, and fails the process.
258 END;
259
260
261 -- for 3761132
262 -- l_atomic_refresh := false for 10g complete refresh only.
263 FUNCTION IS_ATOMIC_REFRESH_BY_DBVERSION RETURN BOOLEAN
264 IS
265 db_versn varchar2(100);
266 l_atomic_refresh boolean;
267 BEGIN
268 select version into db_versn from v$instance;
269 BIS_COLLECTION_UTILITIES.put_line('DB Version: ' || db_versn);
270
271 select substr(replace(substr(version,1,instr(version,'.',1,2)-1),'.'),1,2)
272 into db_versn from v$instance;
273
274
275 if (( db_versn > 80) and (db_versn < 90)) -- 8i
276 then
277 l_atomic_refresh := true;
278 BIS_COLLECTION_UTILITIES.put_line('Atomic refresh: true');
279 elsif ( db_versn >= 90 ) -- 9i
280 then
281 l_atomic_refresh := true;
282 BIS_COLLECTION_UTILITIES.put_line('Atomic refresh: true');
283 else -- 10g
284 l_atomic_refresh := false;
285 BIS_COLLECTION_UTILITIES.put_line('Atomic refresh: false');
286 end if;
287 return l_atomic_refresh;
288 END;
289
290
291 --------added for enhancement 4423644
292 PROCEDURE custom_api_other(p_mvname VARCHAR2, p_mode VARCHAR2,p_mv_refresh_method varchar2)
293 IS
294 l_rtnbuf VARCHAR2(32767);
295 l_retcode VARCHAR2(32767);
296 L_CUSTOM_API bis_obj_properties.custom_api%type := NULL;
297 BEGIN
298 L_CUSTOM_API:= GET_CUSTOMAPI (p_mvname);
299 IF (L_CUSTOM_API is NOT NULL) THEN
300 -- Now call the stored program
301 if p_mv_refresh_method='F' then
302 l_rtnbuf:=BIS_BIA_RSG_CUSTOM_API_MGMNT.METHOD_FAST;
303 else
304 l_rtnbuf:=BIS_BIA_RSG_CUSTOM_API_MGMNT.METHOD_COMPLETE;
305 end if;
306 BIS_COLLECTION_UTILITIES.put_line('Before INVOKE_CUSTOM_API for other custom logic : (' || p_mvname || ', ' || p_mode || ','||l_rtnbuf||')');
307 BIS_BIA_RSG_CUSTOM_API_MGMNT.INVOKE_CUSTOM_API(
308 l_rtnbuf, l_retcode,
309 L_CUSTOM_API, BIS_BIA_RSG_CUSTOM_API_MGMNT.TYPE_MV_OTHER_CUSTOM,
310 p_mvname, 'MV', p_mode);
311 BIS_COLLECTION_UTILITIES.put_line('After INVOKE_CUSTOM_API for other custom logic: (' || p_mvname || ', ' || p_mode ||','||l_rtnbuf||')');
312 END IF;
313 EXCEPTION WHEN OTHERS THEN
314 BIS_COLLECTION_UTILITIES.put_line('Errored in BIS_MV_REFRESH.CUSTOM_API_OTHER');
315 RAISE; -- raise whatever exceptions, and fails the process.
316 END;
317
318
319 /*
320 * For bug 3140731
321 * Created to be published and used by product teams refresh programs..
322 */
323 PROCEDURE REFRESH_WRAPPER (
324 mvname IN VARCHAR2,
325 method IN VARCHAR2,
326 parallelism IN BINARY_INTEGER := 0)
327 IS
328 l_stmt varchar2(1000);
329 l_atomic_refresh boolean := true;
330 l_method varchar2(20) := 'INCREMENTAL'; --Bug 3626375
331 l_schema varchar2(30); --Bug 3999642
332 l_index_flag varchar2(1); --Bug 3999642
333 -- Debug :: Performance Testing
334 -- temp_start_refresh date;
335 -- l_star_transformation varchar2(1000);
336 BEGIN
337 IF (method = 'C') THEN
338 l_atomic_refresh := IS_ATOMIC_REFRESH_BY_DBVERSION;
339
340 if (l_atomic_refresh) then -- NOT TO INVOKE MV_INDEX_MANAGE IN 10G
341 MV_INDEX_MANAGE(mvname, BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_BEFORE);
342 l_schema := BIS_CREATE_REQUESTSET.get_object_owner(mvname, 'MV'); --Bug 3999642
343 l_index_flag := BIS_BIA_RSG_INDEX_MGMT.is_Index_Mgmt_Enabled(mvname, l_schema); --Bug 3999642
344 if (l_index_flag = 'Y' or l_index_flag = 'y') then --Bug 3999642
345 BIS_BIA_RSG_INDEX_MGMT.Capture_and_drop_index_by_mv(mvname, l_schema);
346 end if;
347 end if;
348 l_stmt := 'alter session force parallel query';
349 execute immediate l_stmt;
350 l_stmt := 'alter session enable parallel dml';
351 execute immediate l_stmt;
352 ---bug 4149264
353 l_stmt:='alter table '||mvname||' parallel ';
354 execute immediate l_stmt ;
355 BIS_COLLECTION_UTILITIES.put_line(mvname||' was altered to parallel');
356 ----end of bug 4149264
357
358 -- for bug 3668562, move before the alter session calls
359 -- MV_INDEX_MANAGE(mvname, BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_BEFORE);
360
361 l_method := 'INITIAL'; --BUG 3626375
362 ELSE
363 IF (method = 'F') THEN
364 l_stmt := 'ALTER SESSION SET star_transformation_enabled=TEMP_DISABLE ';
365 execute immediate l_stmt;
366 BIS_COLLECTION_UTILITIES.put_line('Setting star_transformation_enabled=TEMP_DISABLE');
367 END IF;
368 END IF;
369
370 ---added for enhancement 4423644
371 custom_api_other(mvname, BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_BEFORE,method);
372
373 -- for bug3404338, never use the parallelism param for now.
374 -- for bug3617762, SET ATOMIC_REFRESH TO FALSE ONLY FOR COMPLETE REFRESH
375
376 -- Debug for performance check; remove after testing
377 -- temp_start_refresh := sysdate;
378 -- select value into l_star_transformation from v$parameter where name = 'star_transformation_enabled';
379 -- BIS_COLLECTION_UTILITIES.put_line('TESTING :: Value of star_transformation_enabled in V$parameter = '|| l_star_transformation);
380 DBMS_MVIEW.REFRESH(
381 list => mvname,
382 method => method,
383 parallelism => 0,
384 atomic_refresh => l_atomic_refresh);
385
386 -- Debugging for performance check; remove after testing
387 --logtime('TESTING :: Timing for MV Refresh With STAR_TRANSFORMATION_ENABLED='|| l_star_transformation,temp_start_refresh);
388
389 -- Added for bug 3626375 Log Messages
390 BIS_COLLECTION_UTILITIES.put_line(mvname||' was refreshed using '|| l_method || ' refresh method');
391 -- code end for bug 3626375
392
393 ------added for enhancement 4423644
394 custom_api_other(mvname, BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_AFTER,method);
395
396 IF (method = 'C') THEN
397
398 ---bug 4149264
399 l_stmt:='alter table '||mvname||' noparallel ';
400 execute immediate l_stmt;
401 BIS_COLLECTION_UTILITIES.put_line(mvname||' was altered to nonparallel');
402 ----end of bug 4149264
403
404 l_stmt := 'alter session disable parallel query';
405 execute immediate l_stmt;
406 commit;
407 l_stmt := 'alter session disable parallel dml';
408 execute immediate l_stmt;
409 MV_LOG_MANAGE(mvname, 'INIT');
410 if (l_atomic_refresh) then -- NOT TO INVOKE MV_INDEX_MANAGE IN 10G
411 MV_INDEX_MANAGE(mvname, BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_AFTER);
412 if (l_index_flag = 'Y' or l_index_flag = 'y') then --Bug 3999642
413 BIS_BIA_RSG_INDEX_MGMT.recreate_indexes_by_mv_wrapper(mvname, l_schema);
414 end if;
415 end if;
416 ELSE
417 IF (method = 'F') THEN
418 l_stmt := 'ALTER SESSION SET star_transformation_enabled=FALSE ';
419 execute immediate l_stmt;
420 BIS_COLLECTION_UTILITIES.put_line('Resetting star_transformation_enabled=FALSE');
421 -- Debug
422 -- select value into l_star_transformation from v$parameter where name = 'star_transformation_enabled';
423 -- BIS_COLLECTION_UTILITIES.put_line('TESTING :: Value of star_transformation_enabled in V$parameter = '|| l_star_transformation);
424 END IF;
425 END IF;
426
427 EXCEPTION
428 WHEN OTHERS THEN
429 IF (method = 'C') THEN
430 l_stmt := 'alter session disable parallel query';
431 execute immediate l_stmt;
432 commit;
433 l_stmt := 'alter session disable parallel dml';
434 execute immediate l_stmt;
435 if (l_atomic_refresh) then -- NOT TO INVOKE MV_INDEX_MANAGE IN 10G
436 MV_INDEX_MANAGE(mvname, BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_AFTER);
437 if (l_index_flag = 'Y' or l_index_flag = 'y') then
438 BIS_BIA_RSG_INDEX_MGMT.recreate_indexes_by_mv_wrapper(mvname, l_schema);
439 end if;
440 end if;
441 ELSE
442 IF (method = 'F') THEN
443 l_stmt := 'ALTER SESSION SET star_transformation_enabled=FALSE ';
444 execute immediate l_stmt;
445 BIS_COLLECTION_UTILITIES.put_line('Resetting star_transformation_enabled=FALSE');
446 -- Debug
447 -- select value into l_star_transformation from v$parameter where name = 'star_transformation_enabled';
448 -- BIS_COLLECTION_UTILITIES.put_line('TESTING :: Value of star_transformation_enabled in V$parameter = '|| l_star_transformation);
449 END IF;
450 END IF;
451 RAISE;
452 END REFRESH_WRAPPER;
453
454
455 /*
456 * For bug 3140731
457 * call REFRESH_WRAPPER
458 */
459 PROCEDURE REFRESH_WRAP(p_mvname VARCHAR2, p_method VARCHAR2, p_degree BINARY_INTEGER)
460 IS
461 l_time_stamp date;
462 l_method varchar2(100);
463 l_stmt varchar2(1000);
464 BEGIN
465 IF ( p_method = 'C') THEN
466 l_method := 'complete';
467 ELSIF ( p_method = 'F') THEN
468 l_method := 'incremental';
469 END IF;
470
471 setTimer(l_time_stamp);
472 REFRESH_WRAPPER( p_mvname, p_method, p_degree);
473 logTime(p_mvname || ' ' || l_method || ' refreshing', l_time_stamp);
474 END REFRESH_WRAP;
475
476
477 /*
478 FUNCTION IS_BIS_FORCE_REFRESH RETURN BOOLEAN
479 IS
480
481 BEGIN
482 if fnd_profile.value('BIS_MV_FORCE_REFRESH') = 'Y' then
483 BIS_COLLECTION_UTILITIES.put_line('BIS Force Refresh is starting');
484 BIS_COLLECTION_UTILITIES.put_line(' ');
485 RETURN TRUE;
486 else
487 BIS_COLLECTION_UTILITIES.put_line('BIS Force Refresh will not start');
488 BIS_COLLECTION_UTILITIES.put_line(' ');
489 RETURN FALSE;
490 end if;
491
492 END IS_BIS_FORCE_REFRESH;
493 */
494
495 FUNCTION VALIDATE(P_MVNAME IN VARCHAR2)
496 RETURN BOOLEAN IS
497 l_count integer := 0;
498 BEGIN
499 IF (g_apps_schema_name IS NULL) THEN
500 g_apps_schema_name := get_apps_schema_name;
501 END IF;
502 SELECT count(*) INTO l_count FROM ALL_MVIEWS
503 WHERE OWNER = g_apps_schema_name AND MVIEW_NAME = p_mvname;
504 IF (l_count = 0) THEN
505 -- G_ERRBUF := 'Not a valid MV Name: ' || P_MVNAME;
506 -- G_ERRCODE := -1;
507 BIS_COLLECTION_UTILITIES.put_line('Not a valid MV Name: ' || P_MVNAME);
508 -- BIS_COLLECTION_UTILITIES.put_line(' ');
509 return FALSE;
510 ELSE
511 return TRUE;
512 END IF;
513 END VALIDATE;
514
515
516 FUNCTION IS_FAST_REFRESHABLE(P_MVNAME IN VARCHAR2)
517 RETURN BOOLEAN IS
518 l_fast_refreshable VARCHAR2(100) := NULL;
519 l_refresh_method VARCHAR2(50) := NULL;
520
521 BEGIN
522 IF (g_apps_schema_name IS NULL) THEN
523 g_apps_schema_name := get_apps_schema_name;
524 END IF;
525
526 SELECT FAST_REFRESHABLE, REFRESH_METHOD
527 INTO l_fast_refreshable, l_refresh_method
528 FROM ALL_MVIEWS
529 WHERE OWNER = g_apps_schema_name AND MVIEW_NAME = p_mvname;
530
531 IF( l_refresh_method = 'COMPLETE' OR l_fast_refreshable = 'NO' ) THEN
532 BIS_COLLECTION_UTILITIES.put_line( p_mvname || ' is not Fast Refreshable!');
533 BIS_COLLECTION_UTILITIES.put_line(' ');
534 return FALSE;
535 ELSE
536 BIS_COLLECTION_UTILITIES.put_line(p_mvname || ' is Fast Refreshable!');
537 BIS_COLLECTION_UTILITIES.put_line(' ');
538 return TRUE;
539 END IF;
540 END IS_FAST_REFRESHABLE;
541
542
543 -- Try Fast Refresh First,
544 -- if fail due to ignorable exceptions, try complete refresh immediately.
545 -- if fail due to in-ignorable exceptions, get profile option to determine if
546 -- complete refresh is needed.
547 FUNCTION BIS_FAST_REFRESH(p_mvname VARCHAR2, p_degree NUMBER)
548 RETURN NUMBER IS
549 l_errbuf varchar2(2000);
550 l_retcode number;
551 BEGIN
552
553 BEGIN
554 BIS_COLLECTION_UTILITIES.put_line('Performing Fast Refresh for ' || p_mvname ||', mode Incremental Loading');
555 REFRESH_WRAP( p_mvname, 'F', p_degree);
556 BIS_COLLECTION_UTILITIES.put_line(' ');
557 RETURN 1;
558 EXCEPTION
559 -- ignore the following exceptions,
560 -- as they are proven to be able to be resolved by a COMPLETE REFRESH.
561 WHEN OTHERS THEN
562 l_errbuf :=sqlerrm;
563 l_retcode:=sqlcode;
564 IF( l_retcode = -12034 OR -- materialized view log on OWNER.MVIEW_NAME younger than last refresh
565 l_retcode = -12057 OR -- materialized view OWNER.MVIEW_NAME is INVALID and must complete refresh
566 l_retcode = -12004 OR -- "REFRESH FAST cannot be used for materialized view \"%s\".\"%s\""
567 l_retcode = -12033 OR -- "cannot use filter columns from materialized view log on \"%s\".\"%s\""
568 l_retcode = -12034 OR -- "materialized view log on \"%s\".\"%s\" younger than last refresh"
569 l_retcode = -12035 OR -- "could not use materialized view log on \"%s\".\"%s\""
570 l_retcode = -12052 OR -- "cannot fast refresh materialized view %s.%s"
571 l_retcode = -12057 OR -- "materialized view \"%s\".\"%s\" is INVALID and must complete refresh"
572 l_retcode = -32313 OR -- "REFRESH FAST of \"%s\".\"%s\" unsupported after PMOPs"
573 l_retcode = -32314 OR -- "REFRESH FAST of \"%s\".\"%s\" unsupported after deletes/updates"
574 l_retcode = -32315 OR -- "REFRESH FAST of \"%s\".\"%s\" unsupported after mixed DML and Direct Load"
575 l_retcode = -32316 OR -- "REFRESH FAST of \"%s\".\"%s\" unsupported after mixed DML"
576 l_retcode = -32320 OR -- "REFRESH FAST of \"%s\".\"%s\" unsupported after cointainer table PMOPs"
577 l_retcode = -32321 -- "REFRESH FAST of \"%s\".\"%s\" unsupported after detail table TRUNCATE"
578 --l_retcode = -23413 -- table OWNER.TABLE_NAME does not have a materialized view log
579 ) THEN
580 BIS_COLLECTION_UTILITIES.put_line('Ignored the following error while fast-refreshing '|| p_mvname || ':');
581 BIS_COLLECTION_UTILITIES.put_line(sqlerrm);
582 BIS_COLLECTION_UTILITIES.put_line(' ');
583 g_program_status_var := 'WARNING';
584 ELSE
585 BIS_COLLECTION_UTILITIES.put_line('The following error while fast-refreshing '|| p_mvname || ' is not ignorable: ');
586 BIS_COLLECTION_UTILITIES.put_line(sqlerrm);
587 BIS_COLLECTION_UTILITIES.put_line(' ');
588
589 RAISE;
590 END IF;
591 END; -- END OF FIRST ROUND, FAST REFRESH
592 BIS_COLLECTION_UTILITIES.put_line('Performing Complete Refresh for ' || p_mvname ||', mode Incremental Loading');
593 REFRESH_WRAP( p_mvname, 'C', p_degree);
594 BIS_COLLECTION_UTILITIES.put_line(' ');
595 return 1;
596 END BIS_FAST_REFRESH;
597
598
599 FUNCTION REFRESH_MV(p_mvname VARCHAR2,
600 p_refreshmode VARCHAR2,
601 p_final_refresh_mode OUT NOCOPY VARCHAR2)
602
603 RETURN NUMBER IS
604 l_row_count NUMBER;
605 l_sql_stmt VARCHAR2(2000);
606 l_degree NUMBER := 0;
607 l_refreshmode VARCHAR2(100) := NULL;
608 BEGIN
609
610 -- for bug3404338.
611 -- hardcode l_degree to be 0 for now; in the future it will be used again.
612 --l_degree := bis_common_parameters.get_degree_of_parallelism;
613 l_degree := 0;
614 DEBUG('Degree of Parallelism ' || l_degree);
615 -- added for enhancement 3022739, MV threshold at runtime.
616
617 IF (p_refreshmode = 'INCR') THEN
618 l_refreshmode := GET_MV_THRESHOLDED_AT_RUNTIME(p_mvname);
619 --- BIS_COLLECTION_UTILITIES.put_line('Got refresh threshold for ' || p_mvname || ': ' || l_refreshmode );
620 IF (l_refreshmode is NULL ) THEN
621 l_refreshmode := p_refreshmode;
622 END IF;
623 ELSE
624 l_refreshmode := p_refreshmode;
625 END IF;
626
627 --changed following code to get actual refresh mode enh #3473874
628 -- 'INIT' LOADING MODE indicates initial loading,
629 -- meaning a COMPLETE REFRESH is necessary.
630 -- the other possible vaue is 'INCR'
631 IF (l_refreshmode = 'INIT') THEN
632 BIS_COLLECTION_UTILITIES.put_line('Performing Initial loading');
633 REFRESH_WRAP(p_mvname, 'C', l_degree);
634 p_final_refresh_mode := 'INIT';
635 RETURN 1;
636 END IF;
637
638 -- INCREMENTAL LOADING LOGIC FOR NON_FAST_REFRESHABLES
639 DEBUG('Try performing incremental loading');
640 IF ( NOT IS_FAST_REFRESHABLE(p_mvname)) THEN
641 BIS_COLLECTION_UTILITIES.put_line('Performing Complete Loading for ' || p_mvname );
642 REFRESH_WRAP( p_mvname, 'C', l_degree);
643 p_final_refresh_mode := 'INIT';
644
645 RETURN 1;
646 END IF;
647
648 -- INCREMENTAL LOADING LOGIC FOR FAST_REFRESHABLES
649 p_final_refresh_mode := 'INCR';
650 RETURN BIS_FAST_REFRESH(p_mvname, l_degree);
651
652 EXCEPTION
653 WHEN OTHERS THEN
654 g_errbuf := sqlerrm;
655 g_errcode := sqlcode;
656 RETURN(-1);
657 END REFRESH_MV;
658
659 PROCEDURE updCacheByTopPortlet(
660 P_MVNAME IN VARCHAR2
661 ) IS
662 l_portlet_name bis_obj_dependency.object_name%type;
663 l_function_id fnd_form_functions.function_id%type;
664 CURSOR C_PORTLETS ( P_MVNAME bis_obj_dependency.object_name%type )
665 IS
666
667 select distinct object_name, function_id
668 from bis_obj_dependency, fnd_form_functions
669 where depend_object_type = 'MV'
670 and object_type = 'PORTLET'
671 and function_name(+) = depend_object_name
672 and depend_object_name = P_MVNAME;
673
674 /*
675 select distinct object_name
676 from bis_obj_dependency
677 where depend_object_type = 'MV'
678 and object_type = 'PORTLET'
679 and depend_object_name = P_MVNAME;
680 */
681
682
683 BEGIN
684 open C_PORTLETS(P_MVNAME);
685 loop
686 fetch C_PORTLETS into l_portlet_name, l_function_id;
687 exit when C_PORTLETS%NOTFOUND;
688 IF ( l_function_id is null) THEN
689 DEBUG( 'No Form Function defined for ' || l_portlet_name || '
690 , skip calling icx_portlet.updCacheByFuncName');
691 ELSE
692 DEBUG('Calling icx_portlet.updCacheByFuncName on ' || l_portlet_name);
693 icx_portlet.updCacheByFuncName(l_portlet_name);
694 DEBUG('Done icx_portlet.updCacheByFuncName on ' || l_portlet_name);
695 END IF;
696 end loop;
697 close C_PORTLETS;
698
699 END updCacheByTopPortlet;
700
701 FUNCTION IS_IMPLEMENTED(
702 P_MVNAME IN VARCHAR2
703 ) RETURN BOOLEAN
704 IS
705 l_impl_flag bis_obj_properties.implementation_flag%type;
706 CURSOR C_IMPLEMENTATION_FLAG ( P_MVNAME bis_obj_dependency.object_name%type )
707 IS
708 select NVL( implementation_flag, 'N')
709 from bis_obj_properties
710 where object_type = 'MV'
711 and object_name = P_MVNAME;
712 BEGIN
713 open C_IMPLEMENTATION_FLAG(P_MVNAME);
714 fetch C_IMPLEMENTATION_FLAG into l_impl_flag;
715 close C_IMPLEMENTATION_FLAG;
716 BIS_COLLECTION_UTILITIES.put_line( P_MVNAME || ' implemented: ' || l_impl_flag);
717 BIS_COLLECTION_UTILITIES.put_line( '');
718 RETURN (l_impl_flag = 'Y');
719 END;
720
721
722 procedure purge_log (p_mview_id in number) is
723 l_stmt varchar2(1000);
724 l_prefix varchar2(30):=' ';
725 begin
726 l_stmt:=' begin dbms_mview.purge_mview_from_log(:1); end;';
727 -- logmsg(l_prefix||'Begin time '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
728 execute immediate l_stmt using p_mview_id;
729 -- logmsg(l_prefix||'End time '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
730 EXCEPTION WHEN OTHERS THEN
731 if( sqlcode = -23425 ) then
732 logmsg(l_prefix||'This MV had already been dissociated from the MV log.');
733 else
734 logmsg(l_prefix||'Error happened for ' || to_char(p_mview_id) || ': ' || sqlerrm);
735 end if;
736 end;
737
738 /**
739 function get_mv_name_by_id(p_mview_id in number) return varchar2
740 is
741 l_mv_name varchar2(30);
742 begin
743 IF (g_apps_schema_name IS NULL) THEN
744 g_apps_schema_name := get_apps_schema_name;
745 END IF;
746 execute immediate ' select name from all_registered_mviews where owner=:1 and mview_id=:2' into l_mv_name using get_apps_schema_name,p_mview_id;
747 return l_mv_name;
748 exception
749 when no_data_found then
750 -- return null;
751 begin
752 execute immediate 'select vname from sys.snap$ where snapid=:1' into l_mv_name using p_mview_id;
753 return l_mv_name;
754 exception
755 when no_data_found then
756 return null;
757 when others then
758 logmsg(sqlerrm);
759 end;
760 when others then
761 logmsg(sqlerrm);
762 end;
763 **/
764
765 function check_dissociated(p_mview_name varchar2,p_mview_owner varchar2,p_master varchar2,p_master_owner varchar2) return varchar2
766 is
767 l_dummy varchar2(30);
768 l_stmt varchar2(1000):='
769 select ''Dissociated ''
770 from dual
771 where not exists
772 (
773 select ''Y''
774 from all_registered_mviews a,
775 all_base_table_mviews b
776 where a.name=:1
777 and a.owner=:2
778 and a.mview_id=b.mview_id
779 and b.master=:3
780 and b.owner=:4)
781 ';
782 begin
783 execute immediate l_stmt into l_dummy using p_mview_name,p_mview_owner,p_master,p_master_owner;
784
785 return l_dummy;
786 exception
787 when no_data_found then
788 return null;
789 when others then
790 logmsg(sqlerrm);
791 return null;
792 end;
793
794 function check_last_mv(p_mview_id number,p_master varchar2,p_master_owner varchar2) return varchar2 is
795 l_stmt varchar2(1000):='
796 select ''Y''
797 from dual
798 where not exists
799 (
800 select ''Y''
801 from
802 all_base_table_mviews
803 where owner=:1
804 and master=:2
805 and mview_id<>:3 )
806 ';
807 l_dummy varchar2(1);
808 begin
809 execute immediate l_stmt into l_dummy using p_master_owner,p_master,p_mview_id;
810 return l_dummy;
811 exception
812 when no_data_found then
813 return 'N';
814 when others then
815 logmsg(sqlerrm);
816 end;
817
818
819 function get_root_req_id(p_prog_request_id number) return number is
820 cursor c_root_req_id is
821 select a.priority_request_id
822 from fnd_concurrent_requests a
823 where a.request_id =
824 (select b.parent_request_id from fnd_concurrent_requests b where b.request_id=p_prog_request_id );
825 l_root_req_id number;
826 begin
827 open c_root_req_id;
828 fetch c_root_req_id into l_root_req_id;
829 close c_root_req_id;
830 return l_root_req_id;
831 exception
832 when others then
833 logmsg(sqlerrm);
834 return null;
835 end;
836
837 procedure insert_into_history(p_mv_name varchar2,p_prog_req_id number)
838 is
839 l_root_req_id number;
840 begin
841 -- logmsg('Inserting '||p_mv_name|| ' into RSG history table');
842 -- logmsg('p_prog_req_id '||p_prog_req_id);
843 l_root_req_id:=get_root_req_id(p_prog_req_id);
844 -- logmsg('l_root_req_id '||l_root_req_id);
845
846 if (p_prog_req_id is not null and l_root_req_id is not null )then
847 --Enh#4418520-aguwalan
848 IF (BIS_CREATE_REQUESTSET.is_history_collect_on(l_root_req_id)) then
849 BIS_COLL_RS_HISTORY.insert_program_object_data
850 (
851 x_request_id => p_prog_req_id,
852 x_stage_req_id => null,
853 x_object_name => p_mv_name ,
854 x_object_type => 'MV',
855 x_refresh_type => 'CONSIDER_REFRESH',
856 x_set_request_id =>l_root_req_id );
857 ELSE
858 BIS_COLLECTION_UTILITIES.put_line('------------------------------------------------------------------');
859 BIS_COLLECTION_UTILITIES.put_line('Request Set History Collection Option is off for this Request Set.');
860 BIS_COLLECTION_UTILITIES.put_line('No History Collection will happen for this request set.');
861 BIS_COLLECTION_UTILITIES.put_line('------------------------------------------------------------------');
862 END IF;
863 end if;
864 exception
865 when others then
866 logmsg('Error happened in BIS_COLL_RS_HISTORY.insert_program_object_data: '||sqlerrm);
867 end;
868
869 ---This procedure is added for bug 4406144
870 --It will compile MVs in RSG with status INVALID
871 procedure compile_mvs is
872 cursor c_mvs_from_bop is
873 SELECT OBJECT_NAME from BIS_OBJ_PROPERTIES where OBJECT_TYPE = 'MV';
874 l_mv_rec_bop c_mvs_from_bop%ROWTYPE;
875 cursor c_mvs_to_compile(mvNameFromBOP varchar2)
876 is
877 select
878 distinct
879 c.object_name mview_name
880 from
881 all_objects c
882 where c.object_name=mvNameFromBOP
883 and c.owner=g_apps_schema_name
884 and c.object_type='MATERIALIZED VIEW'
885 and c.status='INVALID';
886
887 l_mv_rec c_mvs_to_compile%rowtype;
888
889 begin
890 bis_collection_utilities.put_line(' ');
891 bis_collection_utilities.put_line('Re-compiling INVALID MVs in RSG');
892 IF (g_apps_schema_name IS NULL) THEN
893 g_apps_schema_name := get_apps_schema_name;
894 END IF;
895 for l_mv_rec_bop in c_mvs_from_bop loop
896 open c_mvs_to_compile(l_mv_rec_bop.object_name);
897 fetch c_mvs_to_compile into l_mv_rec;
898 execute immediate 'alter materialized view '||l_mv_rec.mview_name||' compile ';
899 bis_collection_utilities.put_line('Compiled '||l_mv_rec.mview_name);
900 end loop;
901 bis_collection_utilities.put_line('Compiled all INVALID MVs in RSG');
902 exception
903 when others then
904 bis_collection_utilities.put_line(sqlerrm);
905 RAISE;
906 end;
907
908
909
910 PROCEDURE CONSIDER_REFRESH(
911 errbuf OUT NOCOPY VARCHAR2,
912 retcode OUT NOCOPY VARCHAR2
913 ) IS
914
915
916 TYPE curType IS REF CURSOR ;
917 C_CONFREF_LOG_DETAIL curType;
918 c_mv_log_for_unimpl_mvs curType;
919 c_mv_id curType;
920 c_check_mv_in_rsg curType;
921 c_check_mv_valid curType;
922
923
924 l_row_count number;
925 l_mv_name ALL_MVIEWS.MVIEW_NAME%type;
926 l_mv_owner ALL_MVIEWS.OWNER%type;
927 l_mv_last_refresh date;
928 l_mv_implementation_flag varchar2(1);
929
930 ---this cursor fetches all MV logs that have
931 ---at least one unimplemented MV on top
932 l_mv_log_for_unimpl_mvs varchar2(2000):='
933 select
934 distinct
935 l.log_table log_table,
936 l.log_owner log_owner,
937 l.master master_name
938 from
939 bis_obj_properties a,
940 all_mviews b,
941 all_mview_refresh_times t,
942 all_mview_logs l
943 where a.object_type=''MV''
944 and nvl(a.implementation_flag,''N'')=''N''
945 and a.object_name=b.mview_name
946 and b.owner=:1
947 and b.mview_name=t.name
948 and b.owner=t.owner
949 and t.master=l.master
950 and t.master_owner=l.log_owner
951 order by l.log_owner,l.log_table
952 ';
953
954 l_master_name varchar2(30);
955
956 ---this cursor fetches mview_ids from all_base_table_mviews
957 l_mv_id varchar2(2000):='
958 select distinct mview_id
959 from all_base_table_mviews
960 where owner=:1
961 and master=:2'
962 ;
963
964 /**
965 ---this cursor checks hanging MV
966 -- not using sys.snap$ because it is against apps standard
967 l_check_hanging_mv varchar2(2000):='
968 select ''Y''
969 from dual
970 where not exists
971 (select mview_id
972 from all_registered_mviews
973 where mview_id=:1
974 and owner=:2)
975 OR not exists
976 (select snapid
977 from sys.snap$
978 where snapid =:3
979 and master=:4
980 and mowner=:5) '
981 ;
982 **/
983
984
985
986 /**
987 l_check_hanging_mv varchar2(2000):='
988 select distinct a.name
989 from all_registered_mviews a,
990 all_dependencies b
991 where a.mview_id=:1
992 and a.owner=:2
993 and a.name=b.name
994 and a.owner=b.owner
995 and b.type=''MATERIALIZED VIEW''
996 and b.REFERENCED_OWNER=:3
997 and b.REFERENCED_NAME=:4'
998 ;
999 **/
1000
1001 ---modify the cursor for bug 4704633 to improve performance
1002 ---in the past we use all_dependencies
1003 l_check_mv_valid varchar2(2000):='
1004 select distinct a.name
1005 from all_registered_mviews a,
1006 all_mview_refresh_times b
1007 where a.mview_id=:1
1008 and a.owner=:2
1009 and a.name=b.name
1010 and a.owner=b.owner
1011 and b.master_owner=:3
1012 and b.master=:4'
1013 ;
1014
1015 l_valid_mv varchar2(1);
1016
1017 ---if the MV doesn't exist in RSG, we need to report it
1018 ---as required by BISREL in 4247289
1019 l_check_mv_in_rsg varchar2(2000):='
1020 select nvl(b.implementation_flag,''N'') impl_flag
1021 from
1022 bis_obj_properties b
1023 where b.object_name =:1
1024 and b.object_type=''MV'''
1025 ;
1026
1027 l_dummy number;
1028 l_mview_name varchar2(30);
1029
1030
1031 l_impl_flag varchar2(1);
1032 l_log_table ALL_SNAPSHOT_LOGS.LOG_TABLE%type;
1033 l_log_owner ALL_SNAPSHOT_LOGS.LOG_OWNER%TYPE;
1034 l_mview_id number;
1035 l_prefix varchar2(30):=' ';
1036 l_prog_req_id number;
1037
1038
1039 BEGIN
1040
1041 errbuf := NULL;
1042 retcode := '0';
1043 IF (Not BIS_COLLECTION_UTILITIES.setup('BIS_MV_REFRESH.CONSIDER_REFRESH')) THEN
1044 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
1045 return;
1046 END IF;
1047
1048 l_prog_req_id:=fnd_global.CONC_REQUEST_ID;
1049
1050
1051 logmsg('This program is for RSG internal use only. ');
1052 logmsg('The purpose of this program is to make sure ');
1053 logmsg('that shared MV logs by unimplemented MVs can be cleaned up! ');
1054 logmsg(' ');
1055 logmsg('Please note that if a MV had already been dissociated from the MV log, it will not be processed again.');
1056 logmsg(' ');
1057
1058 IF (g_apps_schema_name IS NULL) THEN
1059 g_apps_schema_name := get_apps_schema_name;
1060 END IF;
1061
1062 open c_mv_log_for_unimpl_mvs for l_mv_log_for_unimpl_mvs using g_apps_schema_name;
1063 loop
1064 fetch c_mv_log_for_unimpl_mvs into l_log_table, l_log_owner,l_master_name;
1065 exit when c_mv_log_for_unimpl_mvs%notfound;
1066 logmsg('***********MV log: '||l_log_owner||'.'||l_log_table||' Size: '||get_Table_size(l_log_owner,l_log_table)||' Bytes *******');
1067 logmsg(l_prefix||'==========Processing MVs ============== ');
1068
1069 /**
1070 ----debug
1071 execute immediate 'select count(*) from '|| l_log_owner||'.'||l_log_table into l_row_count;
1072 logmsg(l_prefix||'rows in mv log: '||to_char(l_row_count));
1073 -----end debug
1074 **/
1075
1076 open c_mv_id for l_mv_id using l_log_owner,l_master_name;
1077 loop
1078 fetch c_mv_id into l_mview_id;
1079 exit when c_mv_id%notfound;
1080 logmsg(l_prefix||'mview_id: '||l_mview_id);
1081 l_dummy:=0;
1082 open c_check_mv_valid for l_check_mv_valid
1083 using l_mview_id,g_apps_schema_name,l_log_owner,l_master_name;
1084 -- using l_mview_id,g_apps_schema_name,l_mview_id,l_master_name,l_log_owner;
1085 loop
1086 fetch c_check_mv_valid into l_mview_name;
1087 exit when c_check_mv_valid%notfound;
1088 l_dummy:=l_dummy+1;
1089 begin
1090 execute immediate l_check_mv_in_rsg into l_impl_flag using l_mview_name;
1091 if l_impl_flag ='Y' then
1092 logmsg(l_prefix||l_mview_name||'. Implemented. No action');
1093 else
1094 if check_last_mv(l_mview_id,l_master_name,l_log_owner)='Y' then
1095 logmsg(l_prefix||'Last MV associated to the MV log.');
1096 end if;
1097 logmsg(l_prefix||l_mview_name||'. Unimplemented. Dissociating it');
1098 purge_log(l_mview_id);
1099 insert_into_history(l_mview_name,l_prog_req_id);
1100 end if;
1101 exception
1102 when no_data_found then
1103 logmsg(l_prefix||l_mview_name||'. Not in RSG. No action');
1104 when others then
1105 logmsg(l_prefix||sqlerrm);
1106 end;
1107 end loop;
1108 close c_check_mv_valid;
1109
1110 if l_dummy=0 then --The given mview_id is not valid. It doesn't exist
1111 ------all_registered_mviews or it is defined on the given MV log.
1112 if check_last_mv(l_mview_id,l_master_name,l_log_owner)='Y' then
1113 logmsg(l_prefix||'Last MV associated to the MV log.');
1114 end if;
1115 logmsg(l_prefix||'No name found. Invalid MV. Dissociating it');
1116 purge_log(l_mview_id);
1117 end if;
1118 end loop;
1119 close c_mv_id;
1120 logmsg(' ');
1121 end loop;
1122 close c_mv_log_for_unimpl_mvs;
1123
1124 ---added for bug 4406144
1125 --compile_mvs; --bug fix 5750596
1126 g_program_status := fnd_concurrent.set_completion_status(g_program_status_var ,NULL);
1127
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 ROLLBACK;
1131 errbuf := sqlerrm;
1132 retcode := sqlcode;
1133 BIS_COLLECTION_UTILITIES.put_line(' ');
1134 BIS_COLLECTION_UTILITIES.put_line('Error occurred:');
1135 BIS_COLLECTION_UTILITIES.put_line(errbuf);
1136 g_program_status := fnd_concurrent.set_completion_status('ERROR' ,NULL);
1137
1138 BIS_COLLECTION_UTILITIES.WRAPUP(
1139 FALSE,
1140 0,
1141 errbuf,
1142 null,
1143 null
1144 );
1145
1146 END;
1147
1148
1149 PROCEDURE LOG_DETAIL(
1150 errbuf OUT NOCOPY VARCHAR2,
1151 retcode OUT NOCOPY VARCHAR2
1152 )
1153 is
1154
1155
1156 TYPE curType IS REF CURSOR ;
1157 C_CONFREF_LOG_DETAIL curType;
1158 c_mv_log_for_unimpl_mvs curType;
1159 c_mv_id curType;
1160 c_check_mv_in_rsg curType;
1161
1162
1163 l_cur_stmt_log_detail varchar2(2000):='
1164 select
1165 distinct
1166 t.name,
1167 t.owner,
1168 t.last_refresh
1169 from
1170 all_mview_refresh_times t,
1171 all_mview_logs l
1172 where t.master=l.master
1173 and t.master_owner=l.log_owner
1174 and l.log_table=:1
1175 and l.log_owner=:2
1176 order by t.name,t.owner,t.last_refresh
1177 ';
1178
1179
1180 l_row_count number;
1181 l_mv_name ALL_MVIEWS.MVIEW_NAME%type;
1182 l_mv_owner ALL_MVIEWS.OWNER%type;
1183 l_mv_last_refresh date;
1184 l_mv_implementation_flag varchar2(1);
1185
1186 ---this cursor fetches all MV logs that have
1187 ---at least one unimplemented MV on top
1188 l_mv_log_for_unimpl_mvs varchar2(2000):='
1189 select
1190 distinct
1191 l.log_table log_table,
1192 l.log_owner log_owner,
1193 l.master master_name
1194 from
1195 bis_obj_properties a,
1196 all_mviews b,
1197 all_mview_refresh_times t,
1198 all_mview_logs l
1199 where a.object_type=''MV''
1200 and nvl(a.implementation_flag,''N'')=''N''
1201 and a.object_name=b.mview_name
1202 and b.owner=:1
1203 and b.mview_name=t.name
1204 and b.owner=t.owner
1205 and t.master=l.master
1206 and t.master_owner=l.log_owner
1207 order by l.log_owner,l.log_table
1208 ';
1209
1210 l_master_name varchar2(30);
1211
1212 ---this cursor fetches mview_ids from all_base_table_mviews
1213 l_mv_id varchar2(2000):='
1214 select distinct a.mview_id
1215 from all_base_table_mviews a,
1216 all_snapshot_logs b
1217 where a.owner=:1
1218 and a.master=b.master
1219 and a.owner=b.LOG_OWNER
1220 and b.log_table=:2'
1221 ;
1222
1223
1224 l_dummy number;
1225 l_mview_name varchar2(30);
1226
1227
1228 l_impl_flag varchar2(1);
1229 l_log_table ALL_SNAPSHOT_LOGS.LOG_TABLE%type;
1230 l_log_owner ALL_SNAPSHOT_LOGS.LOG_OWNER%TYPE;
1231 l_mview_id number;
1232 l_prefix varchar2(30):=' ';
1233 l_check_dissociated varchar2(30);
1234 BEGIN
1235
1236 errbuf := NULL;
1237 retcode := '0';
1238 IF (Not BIS_COLLECTION_UTILITIES.setup('BIS_MV_REFRESH.LOG_DETAIL')) THEN
1239 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
1240 return;
1241 END IF;
1242
1243
1244 logmsg('This is a utility program to print out MV log size,MVs and last refresh time on top of MV logs that have unimplemented MVs on top.');
1245 logmsg(' ');
1246
1247 IF (g_apps_schema_name IS NULL) THEN
1248 g_apps_schema_name := get_apps_schema_name;
1249 END IF;
1250
1251 open c_mv_log_for_unimpl_mvs for l_mv_log_for_unimpl_mvs using g_apps_schema_name;
1252 loop
1253 fetch c_mv_log_for_unimpl_mvs into l_log_table, l_log_owner,l_master_name;
1254 exit when c_mv_log_for_unimpl_mvs%notfound;
1255 logmsg('***********MV log: '||l_log_owner||'.'||l_log_table||'************');
1256 logmsg(l_prefix||l_log_owner||'.'||l_log_table||': '||get_Table_size(l_log_owner,l_log_table) || ' Bytes');
1257 open C_CONFREF_LOG_DETAIL for l_cur_stmt_log_detail using l_log_table,l_log_owner;
1258 loop
1259 fetch C_CONFREF_LOG_DETAIL into l_mv_name,l_mv_owner,l_mv_last_refresh;
1260 exit when C_CONFREF_LOG_DETAIL%notfound;
1261 l_check_dissociated:=check_dissociated(l_mv_name,l_mv_owner,l_master_name, l_log_owner);
1262 logmsg(l_prefix||l_mv_owner||'.'||l_mv_name||' '||to_char(l_mv_last_refresh,'DD-MON-YYYY HH24:MI:SS')||' '||l_check_dissociated);
1263 end loop;
1264 close C_CONFREF_LOG_DETAIL;
1265 logmsg(' ');
1266 end loop;
1267 close c_mv_log_for_unimpl_mvs;
1268
1269 g_program_status := fnd_concurrent.set_completion_status(g_program_status_var ,NULL);
1270
1271 EXCEPTION
1272 WHEN OTHERS THEN
1273 ROLLBACK;
1274 errbuf := sqlerrm;
1275 retcode := sqlcode;
1276 BIS_COLLECTION_UTILITIES.put_line(' ');
1277 BIS_COLLECTION_UTILITIES.put_line('Error occurred:');
1278 BIS_COLLECTION_UTILITIES.put_line(errbuf);
1279 g_program_status := fnd_concurrent.set_completion_status('ERROR' ,NULL);
1280
1281 BIS_COLLECTION_UTILITIES.WRAPUP(
1282 FALSE,
1283 0,
1284 errbuf,
1285 null,
1286 null
1287 );
1288
1289 END;
1290
1291
1292
1293
1294 PROCEDURE STANDALONE_REFRESH(
1295 errbuf OUT NOCOPY VARCHAR2,
1296 retcode OUT NOCOPY VARCHAR2,
1297 P_REFRESHMODE IN VARCHAR2,
1298 P_MVNAME IN VARCHAR2
1299 ) IS
1300 BEGIN
1301 errbuf := NULL;
1302 retcode := '0';
1303
1304 DBMS_MVIEW.REFRESH(
1305 list => p_mvname,
1306 method => P_REFRESHMODE);
1307
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310 errbuf := sqlerrm;
1311 retcode := sqlcode;
1312 RAISE;
1313 END STANDALONE_REFRESH;
1314
1315
1316
1317 PROCEDURE REFRESH(
1318 errbuf OUT NOCOPY VARCHAR2,
1319 retcode OUT NOCOPY VARCHAR2,
1320 P_REFRESHMODE IN VARCHAR2,
1321 P_MVNAME IN VARCHAR2
1322 ) IS
1323 l_start DATE := NULL;
1324 l_end DATE := NULL;
1325 l_period_from DATE := NULL;
1326 l_from_date DATE := NULL;
1327 l_to_date DATE := NULL;
1328 l_failure EXCEPTION;
1329 l_refresh NUMBER := 0;
1330 l_row_count NUMBER := 0;
1331 l_sql_stmt VARCHAR2(200);
1332
1333 prog_request_id number ;
1334 l_mv_refresh_type varchar2(30);
1335 p_final_refresh_mode varchar2(30);
1336 l_request_id number;
1337 BEGIN
1338 errbuf := NULL;
1339 retcode := '0';
1340
1341 -- get current request id information from FND to put in Summary report table.
1342 prog_request_id := FND_GLOBAL.conc_request_id;
1343
1344 IF (Not BIS_COLLECTION_UTILITIES.setup(P_MVNAME)) THEN
1345 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || sqlerrm);
1346 return;
1347 END IF;
1348
1349 BIS_COLLECTION_UTILITIES.put_line('Start refreshing for ' || p_mvname || ', mode ' || P_REFRESHMODE);
1350 BIS_COLLECTION_UTILITIES.get_last_refresh_dates(
1351 p_mvname,
1352 l_start,
1353 l_end,
1354 l_period_from,
1355 l_from_date);
1356 l_to_date := sysdate;
1357
1358 IF ( NOT VALIDATE(p_mvname)) THEN
1359 BIS_COLLECTION_UTILITIES.put_line('Skipped refreshing for ' || P_MVNAME);
1360 ELSE
1361
1362 /** Comment out check implementation flag for bug 4532066
1363 IF( NOT IS_IMPLEMENTED(P_MVNAME)) THEN
1364 -- never call CONSIDER_REFRESH for individul MV,
1365 -- a batch task at the end of resquest set will do it for
1366 -- MVs not implemented.
1367 -- CONSIDER_REFRESH(P_MVNAME);
1368 -- Added for bug 3626375 Log Messages
1369 BIS_COLLECTION_UTILITIES.put_line(p_mvname||' was not refreshed because it was not implemented. It will be marked as refreshed');
1370 BIS_COLLECTION_UTILITIES.put_line('in ''BIS Materialized View Batch Refresh Program for MVs not to implement'' at the last stage of the request set.');
1371 -- code end for bug 3626375
1372
1373 ---- l_mv_refresh_type := 'CONSIDER_REFRESH';
1374
1375 ELSE **/
1376
1377 -- compile the MV before refresh logic,
1378 -- added for enhancement 2958485.
1379 RECOMPILE_MV( p_mvname);
1380 l_refresh := REFRESH_MV(p_mvname, p_refreshmode ,p_final_refresh_mode);
1381 IF (l_refresh = -1) THEN
1382 RAISE l_failure;
1383 else
1384 l_mv_refresh_type := p_final_refresh_mode;
1385
1386 END IF;
1387
1388 -- for bug3724431, removing select count(*)
1389 -- and use 0 for l_row_count always.
1390 --l_sql_stmt := 'SELECT count(*) FROM ' || p_mvname ;
1391 --EXECUTE IMMEDIATE l_sql_stmt INTO l_row_count;
1392 l_row_count := 0;
1393
1394 updCacheByTopPortlet(p_mvname);
1395
1396 BIS_COLLECTION_UTILITIES.put_line('End refreshing for ' || p_mvname);
1397
1398 -----Here we only record the MV refreshed by BIS MV refresh program
1399 -----For MVs processed by "BIS Materialized View Batch Refresh Program for MVs not to implement",
1400 -----we will record them to history table in procedure CONSIDER_REFRESH
1401
1402 -- Enh#4418520-aguwalan
1403 IF (BIS_CREATE_REQUESTSET.is_history_collect_on(FND_GLOBAL.CONC_PRIORITY_REQUEST)) THEN
1404 BIS_COLL_RS_HISTORY.insert_program_object_data
1405 (
1406 x_request_id => prog_request_id,
1407 x_stage_req_id => null,
1408 x_object_name => P_MVNAME ,
1409 x_object_type => 'MV',
1410 x_refresh_type => l_mv_refresh_type,
1411 x_set_request_id => FND_GLOBAL.CONC_PRIORITY_REQUEST);
1412 ELSE
1413 BIS_COLLECTION_UTILITIES.put_line('------------------------------------------------------------------');
1414 BIS_COLLECTION_UTILITIES.put_line('Request Set History Collection Option is off for this Request Set.');
1415 BIS_COLLECTION_UTILITIES.put_line('No History Collection will happen for this request set.');
1416 BIS_COLLECTION_UTILITIES.put_line('------------------------------------------------------------------');
1417 END IF;
1418
1419 --- END IF; -- end NOT IS_IMPLEMENTED comment out check for impl flag for bug 4532066
1420 END IF; -- end NOT VALIDATE
1421
1422 g_program_status := fnd_concurrent.set_completion_status(g_program_status_var ,NULL);
1423
1424
1425 BIS_COLLECTION_UTILITIES.WRAPUP(
1426 TRUE, -- status
1427 l_row_count, -- count
1428 NULL, -- message
1429 l_from_date, -- period_from
1430 l_to_date); -- period_to
1431
1432 BIS_COLLECTION_UTILITIES.put_line(' ');
1433
1434 EXCEPTION
1435 WHEN L_FAILURE THEN
1436 ROLLBACK;
1437 BIS_COLLECTION_UTILITIES.put_line(' ');
1438 BIS_COLLECTION_UTILITIES.put_line('Error occurred:');
1439 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
1440 retcode := to_char(g_errcode);
1441 errbuf := g_errbuf;
1442
1443 BIS_COLLECTION_UTILITIES.WRAPUP(
1444 FALSE,
1445 l_row_count,
1446 g_errbuf,
1447 l_from_date,
1448 l_to_date
1449 );
1450
1451 WHEN OTHERS THEN
1452 ROLLBACK;
1453 errbuf := sqlerrm;
1454 retcode := sqlcode;
1455 BIS_COLLECTION_UTILITIES.put_line(' ');
1456 BIS_COLLECTION_UTILITIES.put_line('Error occurred:');
1457 BIS_COLLECTION_UTILITIES.put_line(errbuf);
1458
1459 BIS_COLLECTION_UTILITIES.WRAPUP(
1460 FALSE,
1461 l_row_count,
1462 errbuf,
1463 l_from_date,
1464 l_to_date
1465 );
1466 END REFRESH;
1467 /*
1468 PROCEDURE collect_mv_refresh_info(
1469 errbuf OUT NOCOPY VARCHAR2,
1470 retcode OUT NOCOPY VARCHAR2,
1471 p_request_id IN NUMBER,
1472 p_object_name IN VARCHAR2,
1473 p_refresh_type IN VARCHAR2,
1474 p_set_request_id IN NUMBER
1475 )
1476 IS
1477 BEGIN
1478 BIS_COLLECTION_UTILITIES.put_line('Program to collect the data about MV refresh required by RSG Analysis Report');
1479 BIS_COLLECTION_UTILITIES.put_line('MV Refresh Program Request Id # '|| p_request_id);
1480 BIS_COLLECTION_UTILITIES.put_line('MV Name '|| p_object_name);
1481 BIS_COLLECTION_UTILITIES.put_line('Refresh Type '|| p_refresh_type);
1482
1483 BIS_COLL_RS_HISTORY.insert_program_object_data(x_request_id => p_request_id,
1484 x_stage_req_id => null,
1485 x_object_name => p_object_name ,
1486 x_object_type => 'MV',
1487 x_refresh_type => p_refresh_type,
1488 x_set_request_id => p_set_request_id);
1489
1490 BIS_COLLECTION_UTILITIES.put_line('Completed data collection about MV refresh required by RSG Analysis Report');
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 errbuf := sqlerrm;
1494 retcode := sqlcode;
1495 BIS_COLLECTION_UTILITIES.put_line('Exception in collect_mv_refresh_info :: '|| sqlcode || ' :: ' ||sqlerrm);
1496 RAISE;
1497 END;
1498 */
1499 PROCEDURE COMPILE_INVALID_MVS(
1500 errbuf OUT NOCOPY VARCHAR2,
1501 retcode OUT NOCOPY VARCHAR2
1502 ) IS
1503 l_refresh_progs number;
1504 e_refresh_prog_running EXCEPTION;
1505 l_program_status boolean := true;
1506 BEGIN
1507 BIS_COLLECTION_UTILITIES.put_line('Starting Compiling Invalid MVs');
1508 BIS_COLLECTION_UTILITIES.put_line(' ');
1509 l_refresh_progs := BIS_TRUNCATE_EMPTY_MV_LOG_PKG.Check_Refresh_Prog_running;
1510 if(l_refresh_progs <> 0) then
1511 raise e_refresh_prog_running;
1512 end if;
1513 compile_mvs;
1514 BIS_COLLECTION_UTILITIES.put_line(' ');
1515 BIS_COLLECTION_UTILITIES.put_line('Completing Compiling Invalid MVs');
1516 EXCEPTION
1517 WHEN e_refresh_prog_running THEN
1518 BIS_COLLECTION_UTILITIES.put_line(' ');
1519 IF (l_refresh_progs = 1) THEN
1520 BIS_COLLECTION_UTILITIES.put_line('Error in Compiling Invalid MVs- DBI Refresh program is running' );
1521 ELSE
1522 BIS_COLLECTION_UTILITIES.put_line('Error in Compiling Invalid MVs - MV is being refreshed' );
1523 END IF;
1524 BIS_COLLECTION_UTILITIES.put_line('Please run Compiling Invalid MVs when there are no Refresh request-set/programs running');
1525 l_program_status := fnd_concurrent.set_completion_status('Error' ,NULL);
1526 errbuf := 'DBI Refresh Program Running';
1527 WHEN OTHERS THEN
1528 BIS_COLLECTION_UTILITIES.put_line('Error in Compiling Invalid MVs '|| sqlerrm);
1529 l_program_status := fnd_concurrent.set_completion_status('Error' ,NULL);
1530 errbuf := sqlerrm;
1531 retcode := sqlcode;
1532 END;
1533
1534 END BIS_MV_REFRESH;