[Home] [Help]
PACKAGE BODY: APPS.EDW_WH_DANG_RECOVERY
Source
1 PACKAGE BODY edw_wh_dang_recovery AS
2 /*$Header: EDWWHDRB.pls 115.10 2003/11/06 00:57:15 vsurendr noship $*/
3 version CONSTANT CHAR (80)
4 := '$Header: EDWWHDRB.pls 115.10 2003/11/06 00:57:15 vsurendr noship $';
5
6
7 PROCEDURE load_error_table (
8 p_fact_list IN VARCHAR2,
9 p_dim_list IN VARCHAR2,
10 p_op_tablespace IN VARCHAR2,
11 p_parallel IN NUMBER,
12 p_bis_owner IN VARCHAR2,
13 p_instance IN VARCHAR2,
14 p_debug IN BOOLEAN,
15 p_mode IN VARCHAR2,
16 p_called_from IN VARCHAR2,
17 p_fk_table IN VARCHAR2
18 )
19 IS
20 BEGIN
21 g_bis_owner := p_bis_owner;
22 g_op_tablespace := p_op_tablespace;
23 g_parallel := p_parallel;
24 g_instance := p_instance;
25 g_debug := p_debug;
26 g_mode := p_mode; --CDI or LOADER
27 g_called_from := p_called_from; --CDI or INSTANCE
28 g_fk_table := p_fk_table;
29
30
31 --if called from CDI, the fk table name is passed. so no need to recreate it.
32
33 --if g_instance is null, this call is from check data validity
34 --else its from a source instance
35 IF g_called_from = 'INSTANCE'
36 THEN
37 EDW_OWB_COLLECTION_UTIL.init_all('ABC',null,'bis.edw.loader');
38 END IF;
39
40 IF p_dim_list IS NULL
41 THEN
42 g_status := FALSE;
43 g_status_message :=
44 edw_owb_collection_util.get_message ('EDW_NO_DIM_FOUND');
45 g_status_varchar := 'ERROR';
46 write_to_log_file_n (g_status_message);
47 RETURN;
48 END IF;
49
50 IF p_fact_list IS NULL
51 THEN
52 g_status := FALSE;
53 g_status_message :=
54 edw_owb_collection_util.get_message ('EDW_NO_FACTS_FOUND');
55 g_status_varchar := 'ERROR';
56 write_to_log_file_n (g_status_message);
57 RETURN;
58 END IF;
59
60 init_all;
61
62 IF parse_facts_dims (p_fact_list, p_dim_list) = FALSE
63 THEN
64 g_status_varchar := 'ERROR';
65 RETURN;
66 END IF;
67
68 IF get_dim_ids = FALSE
69 THEN
70 g_status_varchar := 'ERROR';
71 RETURN;
72 END IF;
73
74 IF load_error_table = FALSE
75 THEN
76 g_status_varchar := 'ERROR';
77 RETURN;
78 END IF;
79 EXCEPTION
80 WHEN OTHERS
81 THEN
82 g_status := FALSE;
83 g_status_message := SQLERRM;
84 g_status_varchar := 'ERROR';
85 write_to_log_file_n (g_status_message);
86 END load_error_table;
87
88 FUNCTION load_error_table
89 RETURN BOOLEAN
90 IS
91 BEGIN
92 FOR i IN 1 .. g_number_fact_list
93 LOOP
94 IF read_metadata (g_fact_list (i), g_mode) = FALSE
95 THEN
96 RETURN FALSE;
97 END IF;
98
99 IF generate_op_fstg_table (g_fact_list (i), g_mode) = FALSE
100 THEN
101 RETURN FALSE;
102 END IF;
103
104 IF g_op_fstg_table_empty = TRUE
105 THEN
106 write_to_log_file_n ('No fresh data to be checked for bad keys');
107 END IF;
108
109 IF find_bad_fk_records (g_fstg_op_table) = FALSE
110 THEN
111 RETURN FALSE;
112 END IF;
113
114 IF load_fstg_error_table = FALSE
115 THEN
116 RETURN FALSE;
117 END IF;
118 END LOOP;
119
120 RETURN TRUE;
121 EXCEPTION
122 WHEN OTHERS
123 THEN
124 g_status := FALSE;
125 g_status_message := SQLERRM;
126 write_to_log_file_n (g_status_message);
127 RETURN FALSE;
128 END load_error_table;
129
130 FUNCTION parse_facts_dims (p_fact_list IN VARCHAR2, p_dim_list IN VARCHAR2)
131 RETURN BOOLEAN
132 IS
133 BEGIN
134 IF edw_owb_collection_util.parse_names (
135 p_fact_list,
136 g_fact_list,
137 g_number_fact_list
138 ) = FALSE
139 THEN
140 g_status_message := edw_owb_collection_util.g_status_message;
141 RETURN FALSE;
142 END IF;
143
144 IF edw_owb_collection_util.parse_names (
145 p_dim_list,
146 g_dim_list,
147 g_number_dim_list
148 ) = FALSE
149 THEN
150 g_status_message := edw_owb_collection_util.g_status_message;
151 RETURN FALSE;
152 END IF;
153
154 RETURN TRUE;
155 EXCEPTION
156 WHEN OTHERS
157 THEN
158 g_status := FALSE;
159 g_status_message := SQLERRM;
160 write_to_log_file_n (g_status_message);
161 RETURN FALSE;
162 END parse_facts_dims;
163
164 FUNCTION get_dim_ids
165 RETURN BOOLEAN
166 IS
167 BEGIN
168 FOR i IN 1 .. g_number_dim_list
169 LOOP
170 g_dim_id_list (i) :=
171 edw_owb_collection_util.get_object_id (g_dim_list (i));
172 END LOOP;
173
174 RETURN TRUE;
175 EXCEPTION
176 WHEN OTHERS
177 THEN
178 g_status := FALSE;
179 g_status_message := SQLERRM;
180 write_to_log_file_n (g_status_message);
181 RETURN FALSE;
182 END get_dim_ids;
183
184 FUNCTION read_metadata (p_fact IN VARCHAR2, p_mode IN VARCHAR2)
185 RETURN BOOLEAN
186 IS
187 l_stmt VARCHAR2 (10000);
188
189 TYPE curtyp IS REF CURSOR;
190
191 cv curtyp;
192 l_dim_list VARCHAR2 (30000);
193 l_fstg_fk edw_owb_collection_util.varchartabletype;
194 l_fstg_fk_id edw_owb_collection_util.numbertabletype;
195 l_fk_dim edw_owb_collection_util.varchartabletype;
196 l_number_fstg_fk NUMBER;
197 l_map_src_col edw_owb_collection_util.varchartabletype;
198 l_map_tgt_col edw_owb_collection_util.varchartabletype;
199 l_number_map_cols NUMBER;
200 l_skipped_cols edw_owb_collection_util.varchartabletype;
201 l_number_skipped_cols NUMBER;
202 l_index NUMBER;
203 BEGIN
204 IF g_debug
205 THEN
206 write_to_log_file_n ( 'In read_metadata'
207 || get_time);
208 END IF;
209
210 g_fact := p_fact;
211 l_stmt :=
212 'SELECT rel.relation_name, rel.relation_id, fact.fact_id '
213 || 'FROM edw_relations_md_v rel, edw_relationmapping_md_v map, edw_facts_md_v fact '
214 || 'WHERE fact.fact_name = :a '
215 || 'AND map.targetdataentity = fact.fact_id '
216 || 'AND rel.relation_id = map.sourcedataentity ';
217
218 IF g_debug
219 THEN
220 write_to_log_file_n (
221 'Going to execute '
222 || l_stmt
223 || ' using '
224 || p_fact
225 );
226 END IF;
227
228 OPEN cv FOR l_stmt USING p_fact;
229 FETCH cv INTO g_fstg_name, g_fstg_id, g_fact_id;
230 CLOSE cv;
231
232 IF g_debug
233 THEN
234 write_to_log_file_n (
235 'Results '
236 || g_fstg_name
237 || ','
238 || g_fstg_id
239 || ','
240 || g_fact_id
241 );
242 END IF;
243
244 l_stmt :=
245 'select uk.column_name from edw_unique_key_columns_md_v uk, '
246 || 'edw_all_columns_md_v col '
247 || 'where col.column_id = uk.column_id and '
248 || 'col.entity_id = :a ';
249
250 IF g_debug
251 THEN
252 write_to_log_file_n (
253 'Going to execute '
254 || l_stmt
255 || ' using '
256 || g_fstg_id
257 );
258 END IF;
259
260 OPEN cv FOR l_stmt USING g_fstg_id;
261 FETCH cv INTO g_fstg_pk;
262 CLOSE cv;
263
264 IF g_debug
265 THEN
266 write_to_log_file_n ( 'PK is '
267 || g_fstg_pk);
268 END IF;
269
270 IF g_fstg_pk IS NULL
271 THEN
272 g_status_message :=
273 edw_owb_collection_util.get_message (
274 'EDW_NO_PK_FOR_INTERFACE_TABLE'
275 );
276 RETURN FALSE;
277 END IF;
278
279 l_dim_list := NULL;
280
281 FOR i IN 1 .. g_number_dim_list
282 LOOP
283 l_dim_list := l_dim_list
284 || ''''
285 || g_dim_list (i)
286 || ''',';
287 END LOOP;
288
289 l_dim_list := SUBSTR (l_dim_list, 1, LENGTH (l_dim_list)
290 - 1);
291
292 IF g_debug
293 THEN
294 write_to_log_file_n ( 'Dim list='
295 || l_dim_list);
296 END IF;
297
298 l_stmt := 'SELECT fk.fk_column_name, fk.fk_column_id, dim.dim_name '
299 || 'FROM edw_dimensions_md_v dim, '
300 || 'edw_unique_keys_md_v uk, '
301 || 'edw_foreign_key_columns_md_v fk, '
302 || 'edw_all_columns_md_v col '
303 || 'WHERE dim.dim_name in ('
304 || l_dim_list
305 || ') '
306 || 'AND uk.entity_id = dim.dim_id '
307 || 'AND fk.pk_id = uk.key_id '
308 || 'AND col.column_id = fk.fk_column_id '
309 || 'AND col.entity_id = :a ';
310
311 IF g_debug
312 THEN
313 write_to_log_file_n (
314 'Going to execute '
315 || l_stmt
316 || ' using '
317 || g_fstg_id
318 );
319 END IF;
320
321 l_number_fstg_fk := 1;
322 OPEN cv FOR l_stmt USING g_fstg_id;
323
324 LOOP
325 FETCH cv INTO l_fstg_fk (l_number_fstg_fk),
326 l_fstg_fk_id (l_number_fstg_fk),
327 l_fk_dim (l_number_fstg_fk);
328 EXIT WHEN cv%NOTFOUND;
329 l_number_fstg_fk := l_number_fstg_fk
330 + 1;
331 END LOOP;
332
333 CLOSE cv;
334 l_number_fstg_fk := l_number_fstg_fk
335 - 1;
336
337 IF g_debug
338 THEN
339 write_to_log_file_n ('The FK of the fstg table');
340
341 FOR i IN 1 .. l_number_fstg_fk
342 LOOP
343 write_to_log_file (
344 l_fstg_fk (i)
345 || '('
346 || l_fstg_fk_id (i)
347 || ') '
348 || l_fk_dim (i)
349 );
350 END LOOP;
351 END IF;
352 --Fix for bug P1 2739489.
353 --get the mapping details
354 declare
355 l_mapping_id number;
356 l_fstgTableUsageId number;
357 l_fstgTableId number;
358 l_fstgTableName varchar2(200);
359 l_factTableUsageId number;
360 l_factTableId number;
361 l_factTableName varchar2(200);
362 l_fstgPKName varchar2(200);
363 l_factPKName varchar2(200);
364 l_dimTableName EDW_OWB_COLLECTION_UTIL.varcharTableType;
365 l_dim_row_count EDW_OWB_COLLECTION_UTIL.numberTableType;
366 l_dimTableId EDW_OWB_COLLECTION_UTIL.numberTableType;
367 l_dimUserPKName EDW_OWB_COLLECTION_UTIL.varcharTableType;
368 begin
369 --fix for bug 2847694
370 l_stmt:='select mapping_id '||
371 'from edw_pvt_map_properties_md_v,edw_relations_md_v '||
372 'where edw_relations_md_v.relation_name=:1 '||
373 'and edw_relations_md_v.relation_id=edw_pvt_map_properties_md_v.primary_target ';
374 if g_debug then
375 write_to_log_file_n(l_stmt||' '||p_fact);
376 end if;
377 open cv for l_stmt using p_fact;
378 fetch cv into l_mapping_id;
379 if EDW_OWB_COLLECTION_UTIL.get_stg_map_pk_params(
380 l_mapping_id,
381 l_fstgTableUsageId,
382 l_fstgTableId,
383 l_fstgTableName,
384 l_factTableUsageId,
385 l_factTableId,
386 l_factTableName,
387 l_fstgPKName,
388 l_factPKName
389 )=false then
390 return false;
391 end if;
392 if EDW_OWB_COLLECTION_UTIL.get_stg_map_fk_details(
393 l_fstgTableUsageId,
394 l_fstgTableId,
395 l_mapping_id,
396 1000002,
397 g_op_tablespace,
398 g_bis_owner,
399 l_dimTableName,
400 l_dim_row_count,
401 l_dimTableId,
402 l_dimUserPKName,
403 l_map_src_col,
404 l_map_tgt_col,
405 l_number_map_cols)=false then
406 return false;
407 end if;
408 end;
409 IF g_debug
410 THEN
411 write_to_log_file_n ('The Mapping relations between the keys');
412
413 FOR i IN 1 .. l_number_map_cols
414 LOOP
415 write_to_log_file (
416 l_map_src_col (i)
417 || ' -> '
418 || l_map_tgt_col (i)
419 );
420 END LOOP;
421 END IF;
422
423 IF edw_owb_collection_util.get_item_set_cols (
424 l_skipped_cols,
425 l_number_skipped_cols,
426 p_fact,
427 'SKIP_LOAD_SET'
428 ) = FALSE
429 THEN
430 RETURN FALSE;
431 END IF;
432
433 IF g_debug
434 THEN
435 write_to_log_file_n ('The skipped cols of the fact');
436
437 FOR i IN 1 .. l_number_skipped_cols
438 LOOP
439 write_to_log_file (l_skipped_cols (i));
440 END LOOP;
441 END IF;
442
443 g_number_fstg_fk := 0;
444
445 --l_fstg_fk(l_number_fstg_fk),l_fstg_fk_id(l_number_fstg_fk),l_fk_dim(l_number_fstg_fk);
446 FOR i IN 1 .. l_number_map_cols
447 LOOP
448 IF edw_owb_collection_util.value_in_table (
449 l_skipped_cols,
450 l_number_skipped_cols,
451 l_map_tgt_col (i)
452 ) = FALSE
453 THEN
454 l_index :=
455 edw_owb_collection_util.index_in_table (
456 l_fstg_fk,
457 l_number_fstg_fk,
458 l_map_src_col (i)
459 );
460
461 IF l_index > 0
462 THEN
463 g_number_fstg_fk := g_number_fstg_fk
464 + 1;
465 g_fstg_fk (g_number_fstg_fk) := l_fstg_fk (l_index);
466 g_fstg_fk_id (g_number_fstg_fk) := l_fstg_fk_id (l_index);
467 g_fk_dim (g_number_fstg_fk) := l_fk_dim (l_index);
468 END IF;
469 END IF;
470 END LOOP;
471
472 IF g_debug
473 THEN
474 write_to_log_file_n ('The final list of fks to consider');
475
476 FOR i IN 1 .. g_number_fstg_fk
477 LOOP
478 write_to_log_file (
479 g_fstg_fk (i)
480 || '('
481 || g_fstg_fk_id (i)
482 || ') '
483 || g_fk_dim (i)
484 );
485 END LOOP;
486 END IF;
487
488 l_stmt := 'SELECT fk.fk_column_name '
489 || 'FROM edw_dimensions_md_v dim, '
490 || 'edw_unique_keys_md_v uk, '
491 || 'edw_foreign_key_columns_md_v fk, '
492 || 'edw_all_columns_md_v col '
493 || 'WHERE dim.dim_name = ''EDW_INSTANCE_M'' '
494 || 'AND uk.entity_id = dim.dim_id '
495 || 'AND fk.pk_id = uk.key_id '
496 || 'AND col.column_id = fk.fk_column_id '
497 || 'AND col.entity_id = :a ';
498 OPEN cv FOR l_stmt USING g_fstg_id;
499 FETCH cv INTO g_instance_col;
500 CLOSE cv;
501
502 FOR i IN 1 .. g_number_fstg_fk
503 LOOP
504 IF p_mode = 'CDI'
505 THEN
506 g_bad_fk_tables (i) :=
507 g_bis_owner
508 || '.FBC_'
509 || g_fstg_fk_id (i)
510 || '_'
511 || g_fstg_id;
512 ELSE
513 g_bad_fk_tables (i) :=
514 g_bis_owner
515 || '.FBL_'
516 || g_fstg_fk_id (i)
517 || '_'
518 || g_fstg_id;
519 END IF;
520 END LOOP;
521
522 IF g_debug
523 THEN
524 write_to_log_file_n ( 'Instance col '
525 || g_instance_col);
526 END IF;
527
528 g_number_fstg_all_fk := 0;
529
530 IF edw_owb_collection_util.get_fks_for_table (
531 g_fstg_name,
532 g_fstg_all_fk,
533 g_number_fstg_all_fk
534 ) = FALSE
535 THEN
536 g_status_message := edw_owb_collection_util.g_status_message;
537 RETURN FALSE;
538 END IF;
539
540 IF g_debug
541 THEN
542 write_to_log_file_n ('All FKs of the staging table');
543
544 FOR i IN 1 .. g_number_fstg_all_fk
545 LOOP
546 write_to_log_file (g_fstg_all_fk (i));
547 END LOOP;
548 END IF;
549
550 g_number_fstg_cols := 0;
551
552 IF edw_owb_collection_util.get_columns_for_table (
553 g_fstg_name,
554 g_fstg_cols,
555 g_number_fstg_cols
556 ) = FALSE
557 THEN
558 g_status_message := edw_owb_collection_util.g_status_message;
559 RETURN FALSE;
560 END IF;
561
562 IF g_debug
563 THEN
564 write_to_log_file_n ('All Columns of the staging table');
565
566 FOR i IN 1 .. g_number_fstg_cols
567 LOOP
568 write_to_log_file (g_fstg_cols (i));
569 END LOOP;
570 END IF;
571
572 RETURN TRUE;
573 EXCEPTION
574 WHEN OTHERS
575 THEN
576 g_status := FALSE;
577 g_status_message := SQLERRM;
578 write_to_log_file_n (g_status_message);
579 RETURN FALSE;
580 END read_metadata;
581
582 FUNCTION generate_op_fstg_table (p_fact IN VARCHAR2, p_mode IN VARCHAR2)
583 RETURN BOOLEAN
584 IS
585 l_stmt VARCHAR2 (20000);
586 l_count NUMBER;
587 l_col VARCHAR2 (400);
588 BEGIN
589 IF g_debug
590 THEN
591 write_to_log_file_n ( 'In generate_op_fstg_table'
592 || get_time);
593 END IF;
594
595 IF g_fk_table IS NOT NULL
596 THEN
597 g_fstg_op_table := g_fk_table;
598 RETURN TRUE;
599 END IF;
600
601 IF p_mode = 'CDI'
602 THEN
603 g_fstg_op_table := g_bis_owner
604 || '.STG_'
605 || g_fstg_id;
606 ELSE
607 g_fstg_op_table := g_bis_owner
608 || '.S';
609
610 FOR i IN 1 .. g_number_dim_list
611 LOOP
612 g_fstg_op_table := g_fstg_op_table
613 || '_'
614 || g_dim_id_list (i);
615 END LOOP;
616 END IF;
617
618 IF edw_owb_collection_util.does_table_have_data (
619 g_fstg_name,
620 'LAST_UPDATE_DATE IS NOT NULL'
621 ) = 2
622 THEN
623 l_col := 'LAST_UPDATE_DATE';
624 ELSE
625 l_col := 'ROWNUM';
626 END IF;
627
628 l_stmt := 'create table '
629 || g_fstg_op_table
630 || ' tablespace '
631 || g_op_tablespace;
632
633 IF g_parallel IS NOT NULL
634 THEN
635 l_stmt := l_stmt
636 || ' parallel(degree '
637 || g_parallel
638 || ') ';
639 END IF;
640
641 l_stmt := l_stmt
642 || ' as select ';
643
644 IF g_parallel IS NOT NULL
645 THEN
646 l_stmt := l_stmt
647 || ' /*+PARALLEL('
648 || g_fstg_name
649 || ','
650 || g_parallel
651 || ')*/ ';
652 END IF;
653
654 FOR i IN 1 .. g_number_fstg_fk
655 LOOP
656 l_stmt := l_stmt
657 || g_fstg_fk (i)
658 || ',';
659 END LOOP;
660
661 l_stmt := l_stmt
662 || g_fstg_pk
663 || ',rowid row_id,'
664 || l_col
665 || ' col from '
666 || g_fstg_name
667 || ' where collection_status in (''READY'',''DANGLING'')';
668
669 IF edw_owb_collection_util.drop_table (g_fstg_op_table) = FALSE
670 THEN
671 NULL;
672 END IF;
673
674 IF g_debug
675 THEN
676 write_to_log_file_n ( 'Going to execute '
677 || l_stmt
678 || get_time);
679 END IF;
680
681 EXECUTE IMMEDIATE l_stmt;
682 l_count := SQL%ROWCOUNT;
683
684 IF g_debug
685 THEN
686 write_to_log_file_n (
687 'Created '
688 || g_fstg_op_table
689 || ' with '
690 || l_count
691 || ' rows '
692 || get_time
693 );
694 END IF;
695
696 IF l_count = 0
697 THEN
698 g_op_fstg_table_empty := TRUE;
699 END IF;
700
701 edw_owb_collection_util.analyze_table_stats (
702 SUBSTR (
703 g_fstg_op_table,
704 INSTR (g_fstg_op_table, '.')
705 + 1,
706 LENGTH (g_fstg_op_table)
707 ),
708 SUBSTR (g_fstg_op_table, 1, INSTR (g_fstg_op_table, '.')
709 - 1)
710 );
711 RETURN TRUE;
712 EXCEPTION
713 WHEN OTHERS
714 THEN
715 g_status := FALSE;
716 g_status_message := SQLERRM;
717 write_to_log_file_n (g_status_message);
718 RETURN FALSE;
719 END generate_op_fstg_table;
720
721
722 /*
723 each time a fact is processed, some table needs to hold this info. i another process comes along,
724 that one exits
725 */
726 FUNCTION find_bad_fk_records (p_fstg IN VARCHAR2)
727 RETURN BOOLEAN
728 IS
729 BEGIN
730 IF g_debug
731 THEN
732 write_to_log_file_n ( 'In find_bad_fk_records '
733 || get_time);
734 END IF;
735
736 FOR i IN 1 .. g_number_dim_list
737 LOOP
738 IF g_instance IS NULL
739 THEN
740 g_bad_key_tables (i) := g_bis_owner
741 || '.B_NOINS_'
742 || g_fact_id
743 || '_'
744 || g_dim_id_list (i); --this name has
745 --dependency with CDI code
746 ELSE
747 g_bad_key_tables (i) := g_bis_owner
748 || '.B_'
749 || g_instance
750 || '_'
751 || g_fact_id
752 || '_'
753 || g_dim_id_list (i);
754 --has dep with source pack
755 END IF;
756 END LOOP;
757
758 IF g_debug
759 THEN
760 write_to_log_file_n ('The bad key tables are ');
761
762 FOR i IN 1 .. g_number_dim_list
763 LOOP
764 write_to_log_file (
765 'Dim '
766 || g_dim_list (i)
767 || ' '
768 || g_bad_key_tables (i)
769 );
770 END LOOP;
771 END IF;
772
773 FOR i IN 1 .. g_number_dim_list
774 LOOP
775 IF create_bad_fk_tables (g_bad_key_tables (i), g_dim_list (i)) =
776 FALSE
777 THEN
778 RETURN FALSE;
779 END IF;
780 END LOOP;
781
782 FOR i IN 1 .. g_number_dim_list
783 LOOP
784 IF edw_owb_collection_util.drop_table (g_bad_key_tables (i)) = FALSE
785 THEN
786 NULL;
787 END IF;
788 END LOOP;
789
790 RETURN TRUE;
791 EXCEPTION
792 WHEN OTHERS
793 THEN
794 g_status := FALSE;
795 g_status_message := SQLERRM;
796 write_to_log_file_n (g_status_message);
797 RETURN FALSE;
798 END find_bad_fk_records;
799
800 FUNCTION create_bad_fk_tables (
801 p_bad_table IN VARCHAR2,
802 p_dimension IN VARCHAR2
803 )
804 RETURN BOOLEAN
805 IS
806 l_stmt VARCHAR2 (20000);
807 BEGIN
808 IF g_debug
809 THEN
810 write_to_log_file_n (
811 'In create_bad_fk_tables for dimension '
812 || p_dimension
813 || get_time
814 );
815 END IF;
816
817 FOR i IN 1 .. g_number_fstg_fk
818 LOOP
819 IF g_fk_dim (i) = p_dimension
820 THEN
821 l_stmt := 'create table '
822 || g_bad_fk_tables (i)
823 || ' tablespace '
824 || g_op_tablespace;
825
826 IF g_parallel IS NOT NULL
827 THEN
828 l_stmt := l_stmt
829 || ' parallel(degree '
830 || g_parallel
831 || ') ';
832 END IF;
833
834 l_stmt := l_stmt
835 || ' as select /*+ORDERED*/ ';
836
837 IF g_parallel IS NOT NULL
838 THEN
839 IF g_fstg_name = g_fstg_op_table
840 THEN
841 l_stmt :=
842 l_stmt
843 || ' /*+PARALLEL(A,'
844 || g_parallel
845 || ')*/ ';
846 END IF;
847 END IF;
848
849 IF g_fstg_name = g_fstg_op_table
850 THEN
851 l_stmt := l_stmt
852 || 'A.rowid row_id,';
853 ELSE
854 l_stmt := l_stmt
855 || 'A.row_id row_id,';
856 END IF;
857
858 l_stmt := l_stmt
859 || '''GN'' '
860 || g_fstg_fk (i)
861 || 'FL '
862 || ' from '
863 || g_fstg_op_table
864 || ' A,'
865 || p_bad_table
866 || ' B where A.'
867 || g_fstg_fk (i)
868 || '=B.KEY_VALUE'
869 || ' and A.'
870 || g_instance_col
871 || '=B.instance';
872
873 IF g_fstg_name = g_fstg_op_table
874 THEN
875 l_stmt :=
876 l_stmt
877 || ' and A.collection_status in (''READY'',''DANGLING'')';
878 END IF;
879
880 IF edw_owb_collection_util.drop_table (g_bad_fk_tables (i)) =
881 FALSE
882 THEN
883 NULL;
884 END IF;
885
886 IF g_debug
887 THEN
888 write_to_log_file_n (
889 'Going to execute '
890 || l_stmt
891 || get_time
892 );
893 END IF;
894
895 EXECUTE IMMEDIATE l_stmt;
896
897 IF g_debug
898 THEN
899 write_to_log_file_n (
900 'Created '
901 || g_bad_fk_tables (i)
902 || ' with '
903 || SQL%ROWCOUNT
904 || ' rows '
905 || get_time
906 );
907 END IF;
908 --EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_bad_fk_tables(i),instr(g_bad_fk_tables(i),'.')+1,
909 --length(g_bad_fk_tables(i))),substr(g_bad_fk_tables(i),1,instr(g_bad_fk_tables(i),'.')-1));
910 END IF;
911 END LOOP;
912
913 RETURN TRUE;
914 EXCEPTION
915 WHEN OTHERS
916 THEN
917 g_status := FALSE;
918 g_status_message := SQLERRM;
919 write_to_log_file_n (g_status_message);
920 RETURN FALSE;
921 END create_bad_fk_tables;
922
923 FUNCTION load_fstg_error_table
924 RETURN BOOLEAN
925 IS
926 l_table VARCHAR2 (400);
927 l_merge_table VARCHAR2 (400);
928 BEGIN
929 IF g_debug
930 THEN
931 write_to_log_file_n ( 'In load_fstg_error_table '
932 || get_time);
933 END IF;
934
935 g_fstg_error_table := g_bis_owner
936 || '.'
937 || SUBSTR (g_fact, 1, 28)
938 || 'BR';
939 l_table := SUBSTR (
940 g_fstg_error_table,
941 INSTR (g_fstg_error_table, '.')
942 + 1,
943 LENGTH (g_fstg_error_table)
944 );
945
946 IF merge_bad_fk_tables (l_merge_table) = FALSE
947 THEN
948 RETURN FALSE;
949 END IF;
950
951 IF create_fstg_error_table ('DATA', l_merge_table) = FALSE
952 THEN
953 RETURN FALSE;
954 END IF;
955
956 FOR i IN 1 .. g_number_fstg_fk
957 LOOP
958 IF edw_owb_collection_util.drop_table (g_bad_fk_tables (i)) = FALSE
959 THEN
960 NULL;
961 END IF;
962 END LOOP;
963
964 IF edw_owb_collection_util.drop_table (g_fstg_op_table) = FALSE
965 THEN
966 NULL;
967 END IF;
968
969 RETURN TRUE;
970 EXCEPTION
971 WHEN OTHERS
972 THEN
973 g_status := FALSE;
974 g_status_message := SQLERRM;
975 write_to_log_file_n (g_status_message);
976 RETURN FALSE;
977 END load_fstg_error_table;
978
979 FUNCTION create_fstg_error_table (p_mode IN VARCHAR2, p_table IN VARCHAR2)
980 RETURN BOOLEAN
981 IS
982 l_stmt VARCHAR2 (32000);
983 l_table VARCHAR2 (400);
984 l_index NUMBER;
985 l_count NUMBER;
986 BEGIN
987 IF g_debug
988 THEN
989 write_to_log_file_n ( 'In create_fstg_error_table '
990 || get_time);
991 END IF;
992
993 l_table := SUBSTR (
994 g_fstg_error_table,
995 INSTR (g_fstg_error_table, '.')
996 + 1,
997 LENGTH (g_fstg_error_table)
998 );
999
1000 IF p_mode = 'NO-DATA'
1001 THEN
1002 l_stmt := 'create table '
1003 || g_fstg_error_table
1004 || ' tablespace '
1005 || g_op_tablespace
1006 || ' as select '
1007 || ' * from '
1008 || g_fstg_name
1009 || ' where 1=2';
1010
1011 IF g_debug
1012 THEN
1013 write_to_log_file_n ( 'Going to execute '
1014 || l_stmt
1015 || get_time);
1016 END IF;
1017
1018 EXECUTE IMMEDIATE l_stmt;
1019
1020 IF g_debug
1021 THEN
1022 write_to_log_file_n (
1023 'Created '
1024 || g_fstg_error_table
1025 || ' with '
1026 || SQL%ROWCOUNT
1027 || ' rows '
1028 || get_time
1029 );
1030 END IF;
1031
1032 l_stmt := 'alter table '
1033 || g_fstg_error_table
1034 || ' add (';
1035
1036 FOR i IN 1 .. g_number_fstg_all_fk
1037 LOOP
1038 l_stmt := l_stmt
1039 || g_fstg_all_fk (i)
1040 || 'FL varchar2(2),';
1041 END LOOP;
1042
1043 l_stmt := SUBSTR (l_stmt, 1, LENGTH (l_stmt)
1044 - 1)
1045 || ')';
1046
1047 IF g_debug
1048 THEN
1049 write_to_log_file_n ( 'Going to execute '
1050 || l_stmt
1051 || get_time);
1052 END IF;
1053
1054 EXECUTE IMMEDIATE l_stmt;
1055
1056 IF edw_owb_collection_util.create_synonym (
1057 l_table,
1058 g_fstg_error_table
1059 ) = FALSE
1060 THEN
1061 RETURN FALSE;
1062 END IF;
1063 ELSE
1064 --p_table is the merged table
1065 IF edw_owb_collection_util.drop_table (g_fstg_error_table) = FALSE
1066 THEN
1067 NULL;
1068 END IF;
1069
1070 l_stmt := 'create table '
1071 || g_fstg_error_table
1072 || ' tablespace '
1073 || g_op_tablespace
1074 || ' as select '
1075 || ' /*+ORDERED*/ ';
1076
1077 IF g_parallel IS NOT NULL
1078 THEN
1079 l_stmt := l_stmt
1080 || ' /*+PARALLEL(A,'
1081 || g_parallel
1082 || ')*/ ';
1083 END IF;
1084
1085 FOR i IN 1 .. g_number_fstg_cols
1086 LOOP
1087 l_stmt := l_stmt
1088 || 'A.'
1089 || g_fstg_cols (i)
1090 || ',';
1091 END LOOP;
1092
1093 FOR i IN 1 .. g_number_fstg_all_fk
1094 LOOP
1095 l_index :=
1096 edw_owb_collection_util.index_in_table (
1097 g_fstg_fk,
1098 g_number_fstg_fk,
1099 g_fstg_all_fk (i)
1100 );
1101
1102 IF l_index = -1
1103 THEN
1104 g_status_message := edw_owb_collection_util.g_status_message;
1105 RETURN FALSE;
1106 END IF;
1107
1108 IF l_index = 0
1109 THEN
1110 l_stmt := l_stmt
1111 || '''GY'' '
1112 || g_fstg_all_fk (i)
1113 || 'FL,';
1114 ELSE
1115 l_stmt := l_stmt
1116 || p_table
1117 || '.'
1118 || g_fstg_all_fk (i)
1119 || 'FL,';
1120 END IF;
1121 END LOOP;
1122
1123 l_stmt := SUBSTR (l_stmt, 1, LENGTH (l_stmt)
1124 - 1);
1125 l_stmt := l_stmt
1126 || ' from '
1127 || p_table
1128 || ','
1129 || g_fstg_name
1130 || ' A where '
1131 || p_table
1132 || '.row_id=A.rowid ';
1133
1134 IF g_debug
1135 THEN
1136 write_to_log_file_n ( 'Going to execute '
1137 || l_stmt
1138 || get_time);
1139 END IF;
1140
1141 EXECUTE IMMEDIATE l_stmt;
1142 l_count := SQL%ROWCOUNT;
1143
1144 IF g_debug
1145 THEN
1146 write_to_log_file_n (
1147 'Created '
1148 || g_fstg_error_table
1149 || ' with '
1150 || l_count
1151 || ' rows '
1152 || get_time
1153 );
1154 END IF;
1155
1156 IF edw_owb_collection_util.create_synonym (
1157 l_table,
1158 g_fstg_error_table
1159 ) = FALSE
1160 THEN
1161 RETURN FALSE;
1162 END IF;
1163
1164 IF edw_owb_collection_util.drop_table (p_table) = FALSE
1165 THEN
1166 NULL;
1167 END IF;
1168 END IF;
1169
1170 RETURN TRUE;
1171 EXCEPTION
1172 WHEN OTHERS
1173 THEN
1174 g_status := FALSE;
1175 g_status_message := SQLERRM;
1176 write_to_log_file_n (g_status_message);
1177 RETURN FALSE;
1178 END create_fstg_error_table;
1179
1180 FUNCTION merge_bad_fk_tables (p_merge_table OUT NOCOPY VARCHAR2)
1181 RETURN BOOLEAN
1182 IS
1183 l_stmt VARCHAR2 (32000);
1184 l_start NUMBER;
1185 l_end NUMBER;
1186 l_table_name VARCHAR2 (200);
1187 l_max_count NUMBER;
1188 l_index_table edw_owb_collection_util.varchartabletype;
1189 l_number_index_table NUMBER;
1190 l_index_table_copy edw_owb_collection_util.varchartabletype;
1191 l_number_index_table_copy NUMBER;
1192 l_union_table VARCHAR2 (200);
1193 l_table VARCHAR2 (200);
1194 l_columns edw_owb_collection_util.varchartabletype;
1195 l_number_columns NUMBER;
1196 BEGIN
1197 IF g_debug
1198 THEN
1199 write_to_log_file_n ('In merge_bad_fk_tables');
1200 END IF;
1201
1202 l_table_name := g_bis_owner
1203 || '.MERGE_'
1204 || g_fact_id;
1205 l_union_table := g_bis_owner
1206 || '.UNION_'
1207 || g_fact_id;
1208 l_max_count := 0;
1209 l_number_index_table := 0;
1210
1211 FOR i IN 1 .. g_number_fstg_fk
1212 LOOP
1213 l_number_index_table := l_number_index_table
1214 + 1;
1215 l_index_table (l_number_index_table) := g_bad_fk_tables (i);
1216 END LOOP;
1217
1218 LOOP
1219 l_start := 1;
1220 EXIT WHEN l_number_index_table = 1;
1221 l_index_table_copy := l_index_table;
1222 l_number_index_table_copy := l_number_index_table;
1223 l_number_index_table := 0;
1224
1225 LOOP --through l_index_table. each loop creates a table
1226 l_end := l_start
1227 + 9;
1228
1229 IF l_end > l_number_index_table_copy
1230 THEN
1231 l_end := l_number_index_table_copy;
1232 ELSIF ( l_end
1233 + 1
1234 ) = l_number_index_table_copy
1235 THEN
1236 l_end := l_end
1237 + 1;
1238 END IF;
1239
1240 l_stmt := 'create table '
1241 || l_union_table
1242 || ' tablespace '
1243 || g_op_tablespace;
1244
1245 IF g_parallel IS NOT NULL
1246 THEN
1247 l_stmt := l_stmt
1248 || ' parallel (degree '
1249 || g_parallel
1250 || ') ';
1251 END IF;
1252
1253 l_stmt := l_stmt
1254 || ' ';
1255 l_stmt := l_stmt
1256 || ' as ';
1257
1258 FOR i IN l_start .. l_end
1259 LOOP
1260 l_stmt := l_stmt
1261 || ' select row_id from '
1262 || l_index_table_copy (i)
1263 || ' union ';
1264 END LOOP;
1265
1266 l_stmt := SUBSTR (l_stmt, 1, LENGTH (l_stmt)
1267 - 6);
1268
1269 IF edw_owb_collection_util.drop_table (l_union_table) = FALSE
1270 THEN
1271 NULL;
1272 END IF;
1273
1274 IF g_debug
1275 THEN
1276 write_to_log_file_n (
1277 'Going to execute '
1278 || l_stmt
1279 || get_time
1280 );
1281 END IF;
1282
1283 EXECUTE IMMEDIATE l_stmt;
1284
1285 IF g_debug
1286 THEN
1287 write_to_log_file_n (
1288 'Created '
1289 || l_union_table
1290 || ' with '
1291 || SQL%ROWCOUNT
1292 || ' rows '
1293 || get_time
1294 );
1295 END IF;
1296
1297 l_max_count := l_max_count
1298 + 1;
1299 l_table := l_table_name
1300 || '_O_'
1301 || l_max_count;
1302 l_number_index_table := l_number_index_table
1303 + 1;
1304 l_index_table (l_number_index_table) := l_table;
1305 l_number_columns := 0;
1306 l_stmt :=
1307 'create table '
1308 || l_table
1309 || ' tablespace '
1310 || g_op_tablespace;
1311
1312 IF g_parallel IS NOT NULL
1313 THEN
1314 l_stmt := l_stmt
1315 || ' parallel (degree '
1316 || g_parallel
1317 || ') ';
1318 END IF;
1319
1320 l_stmt := l_stmt
1321 || ' ';
1322 l_stmt := l_stmt
1323 || ' as select ';
1324
1325 FOR i IN l_start .. l_end
1326 LOOP
1327 IF edw_owb_collection_util.get_db_columns_for_table (
1328 SUBSTR (
1329 l_index_table_copy (i),
1330 INSTR (l_index_table_copy (i), '.')
1331 + 1,
1332 LENGTH (l_index_table_copy (i))
1333 ),
1334 l_columns,
1335 l_number_columns,
1336 g_bis_owner
1337 ) = FALSE
1338 THEN
1339 g_status_message :=
1340 edw_owb_collection_util.g_status_message;
1341 write_to_log_file_n (g_status_message);
1342 g_status := FALSE;
1343 RETURN FALSE;
1344 END IF;
1345
1346 FOR j IN 1 .. l_number_columns
1347 LOOP
1348 IF UPPER (l_columns (j)) <> 'ROW_ID'
1349 THEN
1350 l_stmt := l_stmt
1351 || 'decode('
1352 || l_index_table_copy (i)
1353 || '.rowid,null,''GY'','
1354 || l_columns (j)
1355 || ') '
1356 || l_columns (j)
1357 || ',';
1358 END IF;
1359 END LOOP;
1360 END LOOP; --for i in l_start..l_end loop
1361
1362 l_stmt := l_stmt
1363 || l_union_table
1364 || '.ROW_ID from ';
1365
1366 FOR i IN l_start .. l_end
1367 LOOP
1368 l_stmt := l_stmt
1369 || l_index_table_copy (i)
1370 || ',';
1371 END LOOP;
1372
1373 l_stmt := l_stmt
1374 || l_union_table;
1375 l_stmt := l_stmt
1376 || ' where ';
1377
1378 FOR i IN l_start .. l_end
1379 LOOP
1380 l_stmt := l_stmt
1381 || l_union_table
1382 || '.ROW_ID='
1383 || l_index_table_copy (i)
1384 || '.ROW_ID(+) and ';
1385 END LOOP;
1386
1387 l_stmt := SUBSTR (l_stmt, 1, LENGTH (l_stmt)
1388 - 4);
1389
1390 IF edw_owb_collection_util.drop_table (l_table) = FALSE
1391 THEN
1392 NULL;
1393 END IF;
1394
1395 IF g_debug
1396 THEN
1397 write_to_log_file_n (
1398 'Going to execute '
1399 || l_stmt
1400 || get_time
1401 );
1402 END IF;
1403
1404 BEGIN
1405 EXECUTE IMMEDIATE l_stmt;
1406
1407 IF g_debug
1408 THEN
1409 write_to_log_file_n (
1410 'Created '
1411 || l_table
1412 || ' with '
1413 || SQL%ROWCOUNT
1414 || ' rows '
1415 || get_time
1416 );
1417 END IF;
1418 EXCEPTION
1419 WHEN OTHERS
1420 THEN
1421 g_status_message := SQLERRM;
1422 write_to_log_file_n (g_status_message);
1423 g_status := FALSE;
1424 RETURN FALSE;
1425 END;
1426
1427 --drop the tables
1428 FOR i IN l_start .. l_end
1429 LOOP
1430 IF edw_owb_collection_util.drop_table (l_index_table_copy (i)) =
1431 FALSE
1432 THEN
1433 NULL;
1434 END IF;
1435 END LOOP;
1436
1437 l_start := l_end
1438 + 1;
1439 EXIT WHEN l_start > l_number_index_table_copy;
1440 END LOOP;
1441 END LOOP;
1442
1443 IF edw_owb_collection_util.drop_table (l_union_table) = FALSE
1444 THEN
1445 NULL;
1446 END IF;
1447
1448 p_merge_table := l_index_table (1);
1449 RETURN TRUE;
1450 EXCEPTION
1451 WHEN OTHERS
1452 THEN
1453 p_merge_table :='';
1454 g_status := FALSE;
1455 g_status_message := SQLERRM;
1456 write_to_log_file_n (g_status_message);
1457 RETURN FALSE;
1458 END merge_bad_fk_tables;
1459
1460 FUNCTION get_time
1461 RETURN VARCHAR2
1462 IS
1463 BEGIN
1464 RETURN ' Time '
1465 || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS');
1466 EXCEPTION
1467 WHEN OTHERS
1468 THEN
1469 write_to_log_file_n ( 'Error in get_time '
1470 || SQLERRM);
1471 END get_time;
1472
1473 PROCEDURE write_to_log_file (p_message IN VARCHAR2)
1474 IS
1475 BEGIN
1476 edw_owb_collection_util.write_to_log_file (p_message);
1477 EXCEPTION
1478 WHEN OTHERS
1479 THEN
1480 NULL;
1481 END write_to_log_file;
1482
1483 PROCEDURE write_to_log_file_n (p_message IN VARCHAR2)
1484 IS
1485 BEGIN
1486 write_to_log_file (' ');
1487 write_to_log_file (p_message);
1488 EXCEPTION
1489 WHEN OTHERS
1490 THEN
1491 NULL;
1492 END write_to_log_file_n;
1493
1494 PROCEDURE init_all
1495 IS
1496 BEGIN
1497 NULL;
1498 g_status := TRUE;
1499 g_op_fstg_table_empty := FALSE;
1500 g_inc_mode := FALSE;
1501 EXCEPTION
1502 WHEN OTHERS
1503 THEN
1504 write_to_log_file_n ( 'Error in init_all '
1505 || SQLERRM);
1506 END init_all;
1507
1508 FUNCTION get_g_status_varchar
1509 RETURN VARCHAR2
1510 IS
1511 BEGIN
1512 RETURN g_status_varchar;
1513 EXCEPTION
1514 WHEN OTHERS
1515 THEN
1516 write_to_log_file_n ( 'Error in get_g_status_varchar '
1517 || SQLERRM);
1518 END get_g_status_varchar;
1519 END edw_wh_dang_recovery;