[Home] [Help]
PACKAGE BODY: APPS.EDW_CHECK_DATA_INTEGRITY
Source
1 PACKAGE BODY edw_check_data_integrity AS
2 /*$Header: EDWCHDTB.pls 120.1 2005/10/18 04:18:59 amitgupt noship $*/
3 version CONSTANT CHAR (80)
4 := '$Header: EDWCHDTB.pls 120.1 2005/10/18 04:18:59 amitgupt noship $';
5
6 PROCEDURE check_dimensions_data (
7 errbuf OUT NOCOPY VARCHAR2,
8 retcode OUT NOCOPY VARCHAR2,
9 p_dim_string1 IN VARCHAR2,
10 p_check_against_ltc IN VARCHAR2,
11 p_check_tot_recs IN VARCHAR2,
12 p_detailed_check IN VARCHAR2,
13 p_sample_size IN NUMBER
14 )
15 IS
16 BEGIN
17 retcode := '0';
18
19 IF p_detailed_check = 'Y'
20 THEN
21 g_detailed_check := TRUE;
22 ELSE
23 g_detailed_check := FALSE;
24 END IF;
25
26 -- init_all;
27 g_number_names := 1;
28 g_names (g_number_names) := p_dim_string1;
29
30 --get the long names
31 IF get_long_names = FALSE
32 THEN
33 fnd_message.set_name ('BIS', 'EDW_CDI_NO_LONG_DIM');
34 errbuf := fnd_message.get;
35 write_to_out (errbuf);
36 --errbuf:='Could not get long names for the dimensions. Stopping Data Check';
37 retcode := '2';
38 RETURN;
39 END IF;
40
41 g_number_sample := p_sample_size;
42 write_to_log_n ( 'Sample size='
43 || g_number_sample);
44
45 IF p_check_against_ltc = 'Y'
46 THEN
47 g_check_against_ltc := TRUE;
48 write_to_log_n ('check against level tables on');
49 ELSE
50 write_to_log_n ('check against level tables off');
51 END IF;
52
53 IF p_check_tot_recs = 'Y'
54 THEN
55 g_check_hier := TRUE;
56 write_to_log_n ('check of total records making into wh on');
57 ELSE
58 write_to_log_n ('check of total records making into wh off');
59 END IF;
60
61 errbuf := NULL;
62
63 FOR i IN 1 .. g_number_names
64 LOOP
65 g_check_dimension := FALSE;
66 g_object_name := g_names (i);
67 g_object_id := g_ids (i);
68 g_object_type := 'DIMENSION';
69
70 IF check_dimension (g_names (i)) = FALSE
71 THEN
72 errbuf := g_status_message;
73 retcode := '2';
74 END IF;
75 END LOOP;
76 EXCEPTION
77 WHEN g_stg_tables_not_found
78 THEN
79 errbuf := NULL;
80 WHEN OTHERS
81 THEN
82 write_to_out_log_n (SQLERRM);
83 errbuf := SQLERRM;
84 retcode := '2';
85 END check_dimensions_data;
86
87 FUNCTION check_dimension (p_dim_name IN VARCHAR2)
88 RETURN BOOLEAN
89 IS
90 l_dim_long_name VARCHAR2 (200);
91 BEGIN
92 g_number_lstg_tables := 0;
93 g_lstg_fk_number := 0;
94 g_number_hier_distinct := 0;
95 g_number_ltc_tables := 0;
96 g_bottom_records := 0;
97 init_all (p_dim_name);
98
99 IF g_results_table_flag
100 THEN
101 IF delete_cdi_results_table (p_dim_name) = FALSE
102 THEN
103 RETURN FALSE;
104 END IF;
105 END IF;
106
107 l_dim_long_name := get_long_for_short_name (p_dim_name);
108
109 IF g_debug
110 THEN
111 write_to_log_n (
112 'long name for '
113 || p_dim_name
114 || ' got is '
115 || l_dim_long_name
116 );
117 END IF;
118
119 write_to_out_log ('--------------------------------------------------');
120 write_to_out_log ( ' '
121 || l_dim_long_name);
122 write_to_out_log ('--------------------------------------------------');
123
124 IF get_lstg_ltc_keys (p_dim_name) = FALSE
125 THEN
126 write_to_log_n (
127 'Error in getting lstg, ltc and key info. cannot check this dimension '
128 || p_dim_name
129 || ' Time '
130 || get_time
131 );
132 RETURN FALSE;
133 END IF;
134
135 write_to_log_n ( 'get_lstg_ltc_keys done '
136 || get_time);
137
138 IF make_sql_statements = FALSE
139 THEN
140 write_to_log_n (
141 'Error in making sql statements. cannot check this dimension '
142 || p_dim_name
143 || ' Time '
144 || get_time
145 );
146 RETURN FALSE;
147 END IF;
148
149 write_to_log_n ( 'make_sql_statements done '
150 || get_time);
151
152 IF g_exec_flag
153 THEN
154 IF execute_dim_check (p_dim_name) = FALSE
155 THEN
156 write_to_log_n (
157 'Error in executing dim data check. cannot check this dimension '
158 || p_dim_name
159 || ' Time '
160 || get_time
161 );
162 RETURN FALSE;
163 END IF;
164
165 write_to_log_n (
166 'execute_dim_check done for '
167 || p_dim_name
168 || ' '
169 || get_time
170 );
171 ELSE
172 write_to_log_n ('Execute option turned off. No check done');
173 END IF;
174
175 write_to_out_log_n (
176 '--------------------------------------------------'
177 );
178 fnd_message.set_name ('BIS', 'EDW_CDI_END_DATA_CHECK');
179 write_to_out_log ( fnd_message.get
180 || ' '
181 || l_dim_long_name);
182 write_to_out_log ('--------------------------------------------------');
183 write_to_out_log (' ');
184 RETURN TRUE;
185 EXCEPTION
186 WHEN g_stg_tables_not_found
187 THEN
188 RAISE;
189 WHEN OTHERS
190 THEN
191 write_to_out_log_n (SQLERRM);
192 g_status_message := SQLERRM;
193 RETURN FALSE;
194 END check_dimension;
195
196 FUNCTION get_lstg_ltc_keys (p_dim_name IN VARCHAR2)
197 RETURN BOOLEAN
198 IS
199 BEGIN
200 IF get_lstg_ltc_pk (p_dim_name) = FALSE
201 THEN
202 RETURN FALSE;
203 END IF;
204
205 IF get_lstg_ltc_fk (p_dim_name) = FALSE
206 THEN
207 RETURN FALSE;
208 END IF;
209
210 RETURN TRUE;
211 EXCEPTION
212 WHEN g_stg_tables_not_found
213 THEN
214 RAISE;
215 WHEN OTHERS
216 THEN
217 write_to_out_log_n (SQLERRM);
218 g_status_message := SQLERRM;
219 RETURN FALSE;
220 END get_lstg_ltc_keys;
221
222 FUNCTION get_lstg_ltc_pk (p_dim_name IN VARCHAR2)
223 RETURN BOOLEAN
224 IS
225 l_stmt VARCHAR2 (30000);
226 l_var VARCHAR2 (400);
227
228 TYPE curtyp IS REF CURSOR;
229
230 cv curtyp;
231 BEGIN
232 l_stmt :=
233 'SELECT rel.relation_id, rel.relation_name, rel.relation_long_name, pk.column_name, '
234 || 'pk.column_id, lvl.level_table_name, tbl.long_name '
235 || 'from '
236 || 'edw_levels_md_v lvl, '
237 || 'edw_relations_md_v rel, '
238 || 'edw_relationmapping_md_v map, '
239 || 'edw_unique_key_columns_md_v pk, '
240 || 'edw_unique_keys_md_v uk, '
241 || 'edw_tables_md_v tbl '
242 || 'where lvl.dim_name =:s '
243 || 'AND map.targetdataentity = lvl.level_table_id '
244 || 'AND rel.relation_id = map.sourcedataentity '
245 || 'AND lvl.level_table_name = lvl.level_name || ''_LTC'' '
246 || 'AND uk.entity_id = rel.relation_id '
247 || 'AND pk.key_id = uk.key_id '
248 || 'AND lvl.level_table_id = tbl.elementid ';
249
250 IF g_debug
251 THEN
252 write_to_log_n (l_stmt);
253 END IF;
254
255 g_number_lstg_tables := 1;
256 g_number_ltc_tables := 1;
257 OPEN cv FOR l_stmt USING p_dim_name;
258
259 LOOP
260 FETCH cv INTO g_lstg_tables_id (g_number_lstg_tables),
261 g_lstg_tables (g_number_lstg_tables),
262 g_lstg_table_long_name (g_number_lstg_tables),
263 g_lstg_pk (g_number_lstg_tables),
264 g_lstg_pk_id (g_number_lstg_tables),
265 g_ltc_tables (g_number_ltc_tables),
266 g_ltc_tables_long (g_number_ltc_tables);
267 --g_ltc_pk(g_number_ltc_tables);
268 EXIT WHEN cv%NOTFOUND;
269 g_number_lstg_tables := g_number_lstg_tables
270 + 1;
271 g_number_ltc_tables := g_number_ltc_tables
272 + 1;
273 END LOOP;
274
275 g_number_lstg_tables := g_number_lstg_tables
276 - 1;
277 g_number_ltc_tables := g_number_ltc_tables
278 - 1;
279 CLOSE cv;
280
281 IF g_number_lstg_tables = 0
282 THEN
283 fnd_message.set_name ('BIS', 'EDW_CDI_NO_IT_TABLE_FOUND');
284 write_to_out_log_n (fnd_message.get);
285 --write_to_out_log_n('No Interface tables found. Aborting Data Check.');
286 RAISE g_stg_tables_not_found;
287 -- RETURN TRUE;
288 END IF;
289
290 --get the instance column
291 FOR i IN 1 .. g_number_lstg_tables
292 LOOP
293 g_lstg_instance_col (i) :=
294 edw_owb_collection_util.get_instance_col (g_lstg_tables (i));
295 END LOOP;
296
297 IF g_debug
298 THEN
299 write_to_log_n ('The instance column of the staging tables');
300
301 FOR i IN 1 .. g_number_lstg_tables
302 LOOP
303 write_to_log (
304 g_lstg_tables (i)
305 || '('
306 || g_lstg_instance_col (i)
307 || ')'
308 );
309 END LOOP;
310 END IF;
311
312 /*
313 we need to check for all level because when we are looking at
314 the top but one level fk, we need to make sure its "ALL"...
315 there is no interface table for all level
316 */
317 l_stmt :=
318 'SELECT rel.relation_long_name longname '
319 || 'FROM edw_levels_md_v lvl, '
320 || 'edw_relations_md_v rel '
321 || 'WHERE lvl.dim_name = :s '
322 || 'AND lvl.level_name = SUBSTR (dim_name, 1, INSTR (dim_name, ''_M'', -1) - 1) || ''_A'' '
323 || 'AND rel.relation_name = lvl.level_name || ''_LTC''';
324 l_var := NULL;
325 g_all_level_exists := FALSE;
326 g_all_level := '';
327 OPEN cv FOR l_stmt USING p_dim_name;
328 FETCH cv INTO l_var;
329 CLOSE cv;
330
331 IF l_var IS NOT NULL
332 THEN
333 g_number_ltc_tables := g_number_ltc_tables
334 + 1;
335 g_ltc_tables (g_number_ltc_tables) := l_var;
336 --g_ltc_pk(g_number_ltc_tables):='ALL_PK';
337 g_all_level_exists := TRUE;
338 g_all_level := l_var;
339 END IF;
340
341 IF g_debug
342 THEN
343 write_to_log_n ('The lstg table, PK, ltc table');
344
345 FOR i IN 1 .. g_number_lstg_tables
346 LOOP
347 write_to_log (
348 g_lstg_table_long_name (i)
349 || '('
350 || g_lstg_tables (i)
351 || ') '
352 || g_lstg_pk (i)
353 || '('
354 || g_lstg_pk_id (i)
355 || ') '
356 || g_ltc_tables (i)
357 );
358 END LOOP;
359 END IF;
360 --code fix for 4596697
361 FOR i IN 1 .. g_number_lstg_tables
362 LOOP
363 g_lstg_pk_table (i) := g_bis_owner
364 || '.'
365 || substr(g_lstg_tables (i),1,28)
366 || 'CP';
367 g_lstg_dup_pk_table (i) :=
368 g_bis_owner
369 || '.'
370 || substr(g_lstg_tables (i),1,28)
371 || 'CD';
372 END LOOP;
373
374 RETURN TRUE;
375 EXCEPTION
376 WHEN g_stg_tables_not_found
377 THEN
378 RAISE;
379 WHEN OTHERS
380 THEN
381 write_to_out_log_n (SQLERRM);
382 g_status_message := SQLERRM;
383 RETURN FALSE;
384 END get_lstg_ltc_pk;
385
386 FUNCTION get_lstg_ltc_fk (p_dim_name IN VARCHAR2)
387 RETURN BOOLEAN
388 IS
389 l_stmt VARCHAR2 (30000);
390 l_str VARCHAR2 (20000);
391
395 l_ltc_child edw_owb_collection_util.varchartabletype;
392 TYPE curtyp IS REF CURSOR;
393
394 cv curtyp;
396 l_ltc_parent edw_owb_collection_util.varchartabletype;
397 l_lstg_ltc_parent_pk edw_owb_collection_util.varchartabletype;
398 l_lstg_ltc_parent_pk_long edw_owb_collection_util.varchartabletype;
399 l_found BOOLEAN;
400
401 /*
402 l_lstg_child
403 l_lstg_child_fk
404 l_lstg_ltc_parent
405 l_number_lstg
406 used for getting the lstg, fk and parent ltcs
407 */
408 l_lstg_child edw_owb_collection_util.varchartabletype;
409 l_lstg_child_fk edw_owb_collection_util.varchartabletype;
410 l_lstg_ltc_parent edw_owb_collection_util.varchartabletype;
411 l_lstg_child_fk_long edw_owb_collection_util.varchartabletype;
412 l_lstg_ltc_parent_long edw_owb_collection_util.varchartabletype;
413 l_number_lstg NUMBER;
414 BEGIN
415 /*
416 we nede to reuse the lstg-ltc info we found out earlier
417 first get all the names of ltcs involved in the relationships
418 */
419 /*
420 we order by child_level.name, parent_level.name so that if there is
421 caes like the res dim where there are 2 hiers running through the
422 same levels, we need to be able to check both the keys.
423 used in get_lstg_fk_for_ltc;
424 */
425 /*
426 the below id reqd for hierarchy information...its important...
427 */
428 l_stmt :=
429 'SELECT hrc.hier_long_name, chil_lvltbl_name, parent_lvltbl_name '
430 || 'FROM edw_level_relations_md_v lrl, edw_hierarchies_md_v hrc '
431 || 'WHERE lrl.dim_name = :s '
432 || 'AND lrl.hier_id = hrc.hier_id '
433 || 'ORDER BY chil_lvltbl_name, parent_lvltbl_name';
434 g_lstg_fk_number := 1;
435 OPEN cv FOR l_stmt USING p_dim_name;
436
437 LOOP
438 FETCH cv INTO g_hier (g_lstg_fk_number),
439 l_ltc_child (g_lstg_fk_number),
440 l_ltc_parent (g_lstg_fk_number);
441 EXIT WHEN cv%NOTFOUND;
442 g_lstg_fk_number := g_lstg_fk_number
443 + 1;
444 END LOOP;
445
446 g_lstg_fk_number := g_lstg_fk_number
447 - 1;
448 CLOSE cv;
449
450 IF g_debug
451 THEN
452 write_to_log_n ('The hierarchies, child ltc and parent ltc ');
453
454 FOR i IN 1 .. g_lstg_fk_number
455 LOOP
456 write_to_log (
457 g_hier (i)
458 || ' '
459 || l_ltc_child (i)
460 || ' '
461 || l_ltc_parent (i)
462 );
463 END LOOP;
464 END IF;
465
466 /*
467 find the bottom level
468 */
469 l_found := FALSE;
470
471 FOR i IN 1 .. g_lstg_fk_number
472 LOOP
473 l_found := FALSE;
474
475 FOR j IN 1 .. g_lstg_fk_number
476 LOOP
477 IF l_ltc_child (i) = l_ltc_parent (j)
478 THEN
479 l_found := TRUE;
480 EXIT;
481 END IF;
482 END LOOP;
483
484 IF l_found = FALSE
485 THEN
486 g_bottom_level := l_ltc_child (i);
487 EXIT;
488 END IF;
489 END LOOP;
490
491 IF g_debug
492 THEN
493 write_to_log_n ( 'Bottom level is '
494 || g_bottom_level);
495 END IF;
496
497 l_str := '';
498
499 FOR i IN 1 .. g_number_lstg_tables
500 LOOP
501 IF i = 1
502 THEN
503 l_str := l_str
504 || g_lstg_tables_id (i);
505 ELSE
506 l_str := l_str
507 || ','
508 || g_lstg_tables_id (i);
509 END IF;
510 END LOOP;
511
512 --get the lstgs, fks and the parent ltcs
513 l_stmt :=
514 'SELECT lstg.relation_name, fk.fk_column_name, fk_col.business_name, '
515 || 'uk.entity_name, rel.relation_name, pk.column_name, pk_col.business_name '
516 || 'FROM edw_relations_md_v lstg, '
517 || 'edw_foreign_key_columns_md_v fk, '
518 || 'edw_unique_keys_md_v uk, '
519 || 'edw_unique_key_columns_md_v pk, '
520 || 'edw_all_columns_md_v fk_col, '
521 || 'edw_all_columns_md_v pk_col, '
522 || 'edw_relations_md_v rel '
523 || 'WHERE lstg.relation_id IN ('
524 || l_str
525 || ') '
526 || 'AND fk.entity_id = lstg.relation_id '
527 || 'AND uk.key_id = fk.pk_id '
528 || 'AND pk.key_id = uk.key_id '
529 || 'AND fk.entity_id = fk_col.entity_id '
530 || 'AND fk.fk_column_id = fk_col.column_id '
531 || 'AND pk.column_id = pk_col.column_id '
535 IF g_debug
532 || 'AND rel.relation_id = uk.entity_id '
533 || 'AND pk_col.entity_id = uk.entity_id';
534
536 THEN
537 write_to_log_n (l_stmt);
538 END IF;
539
540 l_number_lstg := 1;
541 OPEN cv FOR l_stmt;
542
543 LOOP
544 FETCH cv INTO l_lstg_child (l_number_lstg),
545 l_lstg_child_fk (l_number_lstg),
546 l_lstg_child_fk_long (l_number_lstg),
547 l_lstg_ltc_parent (l_number_lstg),
548 l_lstg_ltc_parent_long (l_number_lstg),
549 l_lstg_ltc_parent_pk (l_number_lstg),
550 l_lstg_ltc_parent_pk_long (l_number_lstg);
551 EXIT WHEN cv%NOTFOUND;
552 l_number_lstg := l_number_lstg
553 + 1;
554 END LOOP;
555
556 l_number_lstg := l_number_lstg
557 - 1;
558
559 IF g_debug
560 THEN
561 write_to_log_n ('The lstg table, fk and parent ltc table and pk');
562
563 FOR i IN 1 .. l_number_lstg
564 LOOP
565 write_to_log (
566 l_lstg_child (i)
567 || ' '
568 || l_lstg_child_fk (i)
569 || ' '
570 || l_lstg_ltc_parent (i)
571 || ' '
572 || l_lstg_ltc_parent_pk (i)
573 );
574 END LOOP;
575 END IF;
576
577
578 /*
579 g_lstg_fk_table EDW_OWB_COLLECTION_UTIL.varcharTableType;
580 g_parent_lstg_fk_table EDW_OWB_COLLECTION_UTIL.varcharTableType;
581 g_parent_ltc_fk_table EDW_OWB_COLLECTION_UTIL.varcharTableType;
582 g_lstg_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
583 g_lstg_fk_position EDW_OWB_COLLECTION_UTIL.numberTableType; --??
584 */
585
586 FOR i IN 1 .. g_lstg_fk_number
587 LOOP
588 g_lstg_fk_table (i) := get_lstg_given_ltc (l_ltc_child (i));
589 --if l_ltc_parent(i) is the all level ltc then get_lstg_given_ltc
590 -- returns ALL
591 g_parent_lstg_fk_table (i) := get_lstg_given_ltc (l_ltc_parent (i));
592 g_parent_lstg_fk_table_pk (i) :=
593 get_pk_for_lstg (g_parent_lstg_fk_table (i));
594 g_parent_ltc_fk_table (i) := l_ltc_parent (i);
595 g_parent_ltc_fk_table_long (i) :=
596 get_parent_ltc_long (
597 g_parent_ltc_fk_table (i),
598 l_lstg_ltc_parent,
599 l_lstg_ltc_parent_long,
600 l_number_lstg
601 );
602 g_parent_ltc_fk_table_pk (i) :=
603 get_pk_for_ltc (
604 g_parent_ltc_fk_table (i),
605 l_lstg_ltc_parent,
606 l_lstg_ltc_parent_pk,
607 l_number_lstg
608 );
609 g_parent_ltc_fk_table_pk_long (i) :=
610 get_ltc_pk_long (
611 g_parent_ltc_fk_table_pk (i),
612 l_lstg_ltc_parent_pk,
613 l_lstg_ltc_parent_pk_long,
614 l_number_lstg
615 );
616
617 IF i > 1
618 THEN
619 /*
620 we pass the previous lstg, fk etc so that if there is a repeat,
621 get the more later one...like res dimension
622 */
623 g_lstg_fk (i) := get_lstg_fk_for_ltc (
624 l_lstg_child,
625 l_lstg_child_fk,
626 l_lstg_ltc_parent,
627 l_number_lstg,
628 g_lstg_fk_table (i),
629 g_parent_ltc_fk_table (i),
630 g_lstg_fk_table ( i
631 - 1),
632 g_parent_ltc_fk_table ( i
633 - 1),
634 g_lstg_fk ( i
635 - 1)
636 );
637 ELSE
638 g_lstg_fk (i) := get_lstg_fk_for_ltc (
639 l_lstg_child,
640 l_lstg_child_fk,
641 l_lstg_ltc_parent,
642 l_number_lstg,
643 g_lstg_fk_table (i),
644 g_parent_ltc_fk_table (i),
645 NULL,
646 NULL,
647 NULL
648 );
649 END IF;
650
651 g_lstg_fk_long (i) := get_fk_long (
652 g_lstg_fk (i),
653 l_lstg_child_fk,
654 l_lstg_child_fk_long,
655 l_number_lstg
656 );
657 g_lstg_fk_id (i) :=
658 edw_owb_collection_util.get_column_id (
659 g_lstg_fk (i),
660 g_lstg_fk_table (i)
664 g_bottom_level := get_lstg_given_ltc (g_bottom_level);
661 );
662 END LOOP;
663
665
666 FOR i IN 1 .. g_lstg_fk_number
667 LOOP
668 g_lstg_fk_table_id (i) :=
669 edw_owb_collection_util.get_object_id (g_lstg_fk_table (i));
670
671 IF g_parent_lstg_fk_table (i) IS NOT NULL
672 THEN
673 g_parent_lstg_fk_table_id (i) :=
674 edw_owb_collection_util.get_object_id (
675 g_parent_lstg_fk_table (i)
676 );
677 ELSE
678 g_parent_lstg_fk_table_id (i) := NULL;
679 END IF;
680
681 IF g_parent_ltc_fk_table (i) IS NOT NULL
682 THEN
683 g_parent_ltc_fk_table_id (i) :=
684 edw_owb_collection_util.get_object_id (
685 g_parent_ltc_fk_table (i)
686 );
687 ELSE
688 g_parent_ltc_fk_table_id (i) := NULL;
689 END IF;
690 END LOOP;
691
692 IF g_debug
693 THEN
694 write_to_log_n ('The globals...');
695
696 FOR i IN 1 .. g_lstg_fk_number
697 LOOP
698 write_to_log (
699 g_lstg_fk_table (i)
700 || '('
701 || g_lstg_fk_table_id (i)
702 || ') '
703 || g_lstg_fk (i)
704 || '('
705 || g_lstg_fk_id (i)
706 || ') '
707 || g_parent_lstg_fk_table (i)
708 || '('
709 || g_parent_lstg_fk_table_id (i)
710 || ') '
711 || g_parent_lstg_fk_table_pk (i)
712 || ' '
713 || g_parent_ltc_fk_table (i)
714 || '('
715 || g_parent_ltc_fk_table_id (i)
716 || ') '
717 || g_parent_ltc_fk_table_pk (i)
718 );
719 END LOOP;
720 END IF;
721
722 /*
723 get the distinct hier names
724 */
725 l_str := g_hier (1);
726 g_number_hier_distinct := 1;
727 g_hier_distinct (1) := g_hier (1);
728 l_found := FALSE;
729
730 FOR i IN 1 .. g_lstg_fk_number
731 LOOP
732 l_found := FALSE;
733
734 FOR j IN 1 .. g_number_hier_distinct
735 LOOP
736 IF g_hier (i) = g_hier_distinct (j)
737 THEN
738 l_found := TRUE;
739 EXIT;
740 END IF;
741 END LOOP;
742
743 IF l_found = FALSE
744 THEN
745 g_number_hier_distinct := g_number_hier_distinct
746 + 1;
747 g_hier_distinct (g_number_hier_distinct) := g_hier (i);
748 END IF;
749 END LOOP;
750
751 IF g_debug
752 THEN
753 write_to_log_n ('The distinct hierarchies');
754
755 FOR i IN 1 .. g_number_hier_distinct
756 LOOP
757 write_to_log (g_hier_distinct (i));
758 END LOOP;
759 END IF;
760
761 --name the tables
762 DECLARE
763 l_name VARCHAR2 (400);
764 BEGIN
765 FOR i IN 1 .. g_lstg_fk_number
766 LOOP
767 l_name := SUBSTR (
768 g_lstg_fk_table (i),
769 1,
770 LENGTH (g_lstg_fk_table (i))
771 - 4
772 );
773 g_lstg_fk_hold_table (i) :=
774 g_bis_owner
775 || '.'
776 || l_name
777 || 'CF'
778 || i;
779 g_lstg_ok_table (i) := g_bis_owner
780 || '.'
781 || l_name
782 || 'CK'
783 || i;
784 g_lstg_dang_table (i) := g_bis_owner
785 || '.'
786 || l_name
787 || 'SD'
788 || i;
789 g_lstg_dang_rowid_table (i) :=
790 g_bis_owner
791 || '.'
792 || l_name
793 || 'SR'
794 || i;
795 g_ltc_ok_table (i) := g_bis_owner
796 || '.'
797 || l_name
798 || 'LK'
799 || i;
800 --not used now
801 g_ltc_dang_table (i) := g_bis_owner
802 || '.'
803 || l_name
804 || 'TD'
805 || i;
809 || '.'
806 --not used now
807 g_ltc_dang_rowid_table (i) :=
808 g_bis_owner
810 || l_name
811 || 'TR'
812 || i;
813 --not used now
814 g_main_lstg_fk_table (i) :=
815 g_bis_owner
816 || '.'
817 || l_name
818 || 'MF'
819 || i;
820 END LOOP;
821
822 IF g_debug
823 THEN
824 FOR i IN 1 .. g_lstg_fk_number
825 LOOP
826 write_to_log (
827 'g_main_lstg_fk_table('
828 || i
829 || ')='
830 || g_main_lstg_fk_table (i)
831 );
832 END LOOP;
833 END IF;
834 END;
835
836 RETURN TRUE;
837 EXCEPTION
838 WHEN OTHERS
839 THEN
840 write_to_out_log_n (SQLERRM);
841 g_status_message := SQLERRM;
842 RETURN FALSE;
843 END get_lstg_ltc_fk;
844
845 FUNCTION get_pk_for_lstg (p_lstg IN VARCHAR2)
846 RETURN VARCHAR2
847 IS
848 BEGIN
849 FOR i IN 1 .. g_number_lstg_tables
850 LOOP
851 IF g_lstg_tables (i) = p_lstg
852 THEN
853 RETURN g_lstg_pk (i);
854 END IF;
855 END LOOP;
856
857 RETURN NULL;
858 EXCEPTION
859 WHEN OTHERS
860 THEN
861 write_to_out_log_n (SQLERRM);
862 g_status_message := SQLERRM;
863 RETURN NULL;
864 END get_pk_for_lstg;
865
866 FUNCTION get_pk_for_ltc (
867 p_ltc IN VARCHAR2,
868 l_lstg_ltc_parent IN edw_owb_collection_util.varchartabletype,
869 l_lstg_ltc_parent_pk IN edw_owb_collection_util.varchartabletype,
870 l_number_lstg IN NUMBER
871 )
872 RETURN VARCHAR2
873 IS
874 BEGIN
875 FOR i IN 1 .. l_number_lstg
876 LOOP
877 IF l_lstg_ltc_parent (i) = p_ltc
878 THEN
879 RETURN l_lstg_ltc_parent_pk (i);
880 END IF;
881 END LOOP;
882
883 /*
884 for i in 1..g_number_ltc_tables loop
885 if g_ltc_tables(i)=p_ltc then
886 return g_ltc_pk(i);
887 end if;
888 end loop;
889 */
890 RETURN NULL;
891 EXCEPTION
892 WHEN OTHERS
893 THEN
894 write_to_out_log_n (SQLERRM);
895 g_status_message := SQLERRM;
896 RETURN NULL;
897 END get_pk_for_ltc;
898
899 FUNCTION get_lstg_given_ltc (p_ltc IN VARCHAR2)
900 RETURN VARCHAR2
901 IS
902 BEGIN
903 IF p_ltc = g_all_level
904 THEN
905 RETURN 'ALL';
906 END IF;
907
908 FOR i IN 1 .. g_number_lstg_tables
909 LOOP
910 IF g_ltc_tables (i) = p_ltc
911 THEN
912 RETURN g_lstg_tables (i);
913 END IF;
914 END LOOP;
915
916 RETURN NULL;
917 EXCEPTION
918 WHEN OTHERS
919 THEN
920 write_to_out_log_n (SQLERRM);
921 g_status_message := SQLERRM;
922 RETURN NULL;
923 END get_lstg_given_ltc;
924
925 FUNCTION get_lstg_fk_for_ltc (
926 p_lstg_child IN edw_owb_collection_util.varchartabletype,
927 p_lstg_child_fk IN edw_owb_collection_util.varchartabletype,
928 p_lstg_ltc_parent IN edw_owb_collection_util.varchartabletype,
929 p_lstg_number IN NUMBER,
930 p_lstg_fk_table IN VARCHAR2,
931 p_parent_ltc_fk_table IN VARCHAR2,
932 p_lstg_fk_table_prev IN VARCHAR2,
933 p_parent_ltc_fk_table_prev IN VARCHAR2,
934 p_lstg_fk_prev IN VARCHAR2
935 )
936 RETURN VARCHAR2
937 IS
938 l_key VARCHAR2 (400);
939 BEGIN
940 /*
941 the prev are for dims like resource...
942 */
943 l_key := NULL;
944
945 FOR i IN 1 .. p_lstg_number
946 LOOP
947 IF p_lstg_child (i) = p_lstg_fk_table
948 AND p_lstg_ltc_parent (i) = p_parent_ltc_fk_table
949 THEN
950 l_key := p_lstg_child_fk (i);
951
952 IF p_lstg_fk_table_prev IS NULL
953 OR p_parent_ltc_fk_table_prev IS NULL
954 OR p_lstg_fk_prev IS NULL
955 THEN
956 RETURN p_lstg_child_fk (i);
957 ELSIF p_lstg_child (i) <> p_lstg_fk_table_prev
958 OR p_lstg_ltc_parent (i) <> p_parent_ltc_fk_table_prev
959 OR p_lstg_child_fk (i) <> p_lstg_fk_prev
960 THEN
964 END LOOP;
961 RETURN p_lstg_child_fk (i);
962 END IF;
963 END IF;
965
966 IF l_key IS NOT NULL
967 THEN
968 RETURN l_key;
969 END IF;
970
971 RETURN NULL;
972 EXCEPTION
973 WHEN OTHERS
974 THEN
975 write_to_out_log_n (SQLERRM);
976 g_status_message := SQLERRM;
977 RETURN NULL;
978 END get_lstg_fk_for_ltc;
979
980 FUNCTION make_sql_statements
981 RETURN BOOLEAN
982 IS
983 BEGIN
984 IF make_hier_count_stmt = FALSE
985 THEN
986 RETURN FALSE;
987 END IF;
988
989 RETURN TRUE;
990 EXCEPTION
991 WHEN OTHERS
992 THEN
993 write_to_out_log_n (SQLERRM);
994 g_status_message := SQLERRM;
995 RETURN FALSE;
996 END make_sql_statements;
997
998 FUNCTION make_hier_count_stmt
999 RETURN BOOLEAN
1000 IS
1001 BEGIN
1002 g_hier_stmt_num := 'select nvl(count(1),0) from ';
1003
1004 FOR i IN 1 .. g_number_lstg_tables
1005 LOOP
1006 IF i = 1
1007 THEN
1008 g_hier_stmt_num :=
1009 g_hier_stmt_num
1010 || ' '
1011 || g_lstg_tables (i)
1012 || ' A_'
1013 || i;
1014 ELSE
1015 g_hier_stmt_num :=
1016 g_hier_stmt_num
1017 || ','
1018 || g_lstg_tables (i)
1019 || ' A_'
1020 || i;
1021 END IF;
1022 END LOOP;
1023
1024 g_hier_stmt_num := g_hier_stmt_num
1025 || ' where 1=1 ';
1026
1027 FOR i IN 1 .. g_number_lstg_tables
1028 LOOP
1029 g_hier_stmt_num :=
1030 g_hier_stmt_num
1031 || ' And A_'
1032 || i
1033 || '.collection_status in (''READY'',''DANGLING'',''DUPLICATE'') ';
1034 END LOOP;
1035
1036 FOR i IN 1 .. g_number_lstg_tables
1037 LOOP
1038 g_hier_stmt_num := g_hier_stmt_num
1039 || ' and A_'
1040 || i
1041 || '.'
1042 || g_lstg_pk (i)
1043 || ' in (select '
1044 || g_lstg_pk (i)
1045 || ' from '
1046 || g_lstg_tables (i)
1047 || ' having
1048 count('
1049 || g_lstg_pk (i)
1050 || ') =1 group by '
1051 || g_lstg_pk (i)
1052 || ' ) ';
1053 END LOOP;
1054
1055 FOR i IN 1 .. g_lstg_fk_number
1056 LOOP
1057 IF g_parent_lstg_fk_table (i) = 'ALL'
1058 THEN
1059 g_hier_stmt_num := g_hier_stmt_num
1060 || ' and '
1061 || get_table_alias (g_lstg_fk_table (i))
1062 || '.'
1063 || g_lstg_fk (i)
1064 || ' = ''ALL'' ';
1065 ELSE
1066 g_hier_stmt_num := g_hier_stmt_num
1067 || ' and '
1068 || get_table_alias (g_lstg_fk_table (i))
1069 || '.'
1070 || g_lstg_fk (i)
1071 || ' = '
1072 || get_table_alias (
1073 g_parent_lstg_fk_table (i)
1074 )
1075 || '.'
1076 || g_parent_lstg_fk_table_pk (i)
1077 || ' ';
1078 END IF;
1079 END LOOP;
1080
1081 IF g_debug
1082 THEN
1083 write_to_log_n ('The total records making in counter');
1084 write_to_log_n (g_hier_stmt_num);
1085 END IF;
1086
1087 RETURN TRUE;
1088 EXCEPTION
1089 WHEN OTHERS
1090 THEN
1091 write_to_out_log_n (SQLERRM);
1092 g_status_message := SQLERRM;
1093 RETURN FALSE;
1094 END make_hier_count_stmt;
1095
1096 FUNCTION get_table_alias (p_table IN VARCHAR2)
1097 RETURN VARCHAR2
1098 IS
1099 BEGIN
1100 FOR i IN 1 .. g_number_lstg_tables
1104 RETURN 'A_'
1101 LOOP
1102 IF p_table = g_lstg_tables (i)
1103 THEN
1105 || i;
1106 END IF;
1107 END LOOP;
1108
1109 RETURN NULL;
1110 EXCEPTION
1111 WHEN OTHERS
1112 THEN
1113 write_to_out_log_n (SQLERRM);
1114 g_status_message := SQLERRM;
1115 RETURN NULL;
1116 END get_table_alias;
1117
1118 FUNCTION execute_dim_check (p_dim_name IN VARCHAR2)
1119 RETURN BOOLEAN
1120 IS
1121 BEGIN
1122 IF execute_dim_all_records (p_dim_name) = FALSE
1123 THEN
1124 RETURN FALSE;
1125 END IF;
1126
1127 IF g_check_dimension
1128 THEN
1129 IF g_duplicate_check = TRUE
1130 THEN
1131 IF execute_dim_duplicate_check (p_dim_name) = FALSE
1132 THEN
1133 RETURN FALSE;
1134 ELSE
1135 write_to_log_n (
1136 'execute_dim_duplicate_check done...'
1137 || get_time
1138 );
1139 END IF;
1140 END IF;
1141
1142 IF execute_dim_dangling_check (p_dim_name) = FALSE
1143 THEN
1144 RETURN FALSE;
1145 END IF;
1146
1147 IF g_check_hier
1148 THEN
1149 --if execute_hier_count(p_dim_name)= false then
1150 --return false;
1151 --end if;
1152 NULL;
1153 END IF;
1154
1155 IF drop_lstg_fk_tables = FALSE
1156 THEN
1157 NULL;
1158 END IF;
1159
1160 IF drop_lstg_pk_tables = FALSE
1161 THEN
1162 NULL;
1163 END IF;
1164 END IF;
1165
1166 RETURN TRUE;
1167 EXCEPTION
1168 WHEN OTHERS
1169 THEN
1170 g_status_message := SQLERRM;
1171 write_to_out_log_n (SQLERRM);
1172 RETURN FALSE;
1173 END execute_dim_check;
1174
1175 FUNCTION execute_dim_all_records (p_dim_name IN VARCHAR2)
1176 RETURN BOOLEAN
1177 IS
1178 l_stmt VARCHAR2 (3000);
1179
1180 TYPE curtyp IS REF CURSOR;
1181
1182 l_owner VARCHAR2 (400);
1183 BEGIN
1184 write_to_out (' ');
1185 fnd_message.set_name ('BIS', 'EDW_CDI_TOTAL_RECORDS');
1186 write_to_out (fnd_message.get);
1187 --write_to_out('Total number of records in the interface tables with status of ');
1188 --write_to_out('''READY'',''DANGLING'' or ''DUPLICATE''');
1189 write_to_out (' ');
1190 l_owner := edw_owb_collection_util.get_table_owner (p_dim_name);
1191
1192 FOR i IN 1 .. g_number_lstg_tables
1193 LOOP
1194 --EDW_OWB_COLLECTION_UTIL.analyze_table_stats(g_lstg_tables(i), l_owner);
1195 g_lstg_total_records (i) := 0;
1196 l_stmt := 'create table '
1197 || g_lstg_pk_table (i)
1198 || ' tablespace '
1199 || g_op_table_space;
1200
1201 IF g_parallel IS NULL
1202 THEN
1203 l_stmt :=
1204 l_stmt
1205 || ' as select '
1206 || g_lstg_pk (i)
1207 || ',rowid row_id from '
1208 || g_lstg_tables (i)
1209 || ' where collection_status in (''READY'',''DANGLING'',''DUPLICATE'') ';
1210 ELSE
1211 l_stmt := l_stmt
1212 || ' parallel (degree '
1213 || g_parallel
1214 || ') ';
1215 l_stmt := l_stmt
1216 || ' as select /*+PARALLEL('
1217 || g_lstg_tables (i)
1218 || ','
1219 || g_parallel
1220 || ')*/ '
1221 || g_lstg_pk (i)
1222 || ','
1223 || ' rowid row_id from '
1224 || g_lstg_tables (i)
1225 || ' where collection_status in '
1226 || '(''READY'',''DANGLING'',''DUPLICATE'') ';
1227 END IF;
1228
1229 IF edw_owb_collection_util.drop_table (g_lstg_pk_table (i)) = FALSE
1230 THEN
1231 NULL;
1232 END IF;
1233
1234 IF g_debug
1235 THEN
1236 write_to_log_n ( 'going to execute '
1237 || l_stmt
1238 || get_time);
1239 END IF;
1240
1241 EXECUTE IMMEDIATE l_stmt;
1242 g_lstg_total_records (i) := SQL%ROWCOUNT;
1243
1244 IF g_lstg_total_records (i) > 0
1245 THEN
1246 g_check_dimension := TRUE;
1247 END IF;
1248
1249 IF g_debug
1250 THEN
1251 write_to_log_n (
1252 'Created '
1253 || g_lstg_pk_table (i)
1254 || ' with '
1255 || g_lstg_total_records (i)
1256 || ' rows'
1260
1257 || get_time
1258 );
1259 END IF;
1261 fnd_message.set_name ('BIS', 'EDW_CDI_TABLE_AND_RECORDS');
1262 fnd_message.set_token ('TABLE', g_lstg_table_long_name (i));
1263 fnd_message.set_token ('RECORDS', g_lstg_total_records (i));
1264 write_to_out (fnd_message.get);
1265 --write_to_out('Table '||g_lstg_table_long_name(i)||' has '||g_lstg_total_records(i)||' records');
1266 edw_owb_collection_util.analyze_table_stats (
1267 SUBSTR (
1268 g_lstg_pk_table (i),
1269 INSTR (g_lstg_pk_table (i), '.')
1270 + 1,
1271 LENGTH (g_lstg_pk_table (i))
1272 ),
1273 SUBSTR (
1274 g_lstg_pk_table (i),
1275 1,
1276 INSTR (g_lstg_pk_table (i), '.')
1277 - 1
1278 )
1279 );
1280 END LOOP;
1281
1282 g_bottom_records := get_num_recs_lstg (g_bottom_level);
1283 RETURN TRUE;
1284 EXCEPTION
1285 WHEN OTHERS
1286 THEN
1287 write_to_out_log_n (SQLERRM);
1288 g_status_message := SQLERRM;
1289 RETURN FALSE;
1290 END execute_dim_all_records;
1291
1292 FUNCTION execute_dim_duplicate_check (p_dim_name IN VARCHAR2)
1293 RETURN BOOLEAN
1294 IS
1295 l_stmt VARCHAR2 (30000);
1296
1297 TYPE curtyp IS REF CURSOR;
1298
1299 cv curtyp;
1300 l_num_dup NUMBER;
1301 l_num_dup_log NUMBER;
1302 l_dup_str edw_owb_collection_util.varchartabletype;
1303 l_dup_count edw_owb_collection_util.numbertabletype;
1304 l_number_dup_str NUMBER;
1305 BEGIN
1306 write_to_out (' ');
1307 fnd_message.set_name ('BIS', 'EDW_CDI_DUPLICATE_DATA_CHECK');
1308 write_to_out (fnd_message.get);
1309
1310 --write_to_out('Duplicate Data Check ');
1311 FOR i IN 1 .. g_number_lstg_tables
1312 LOOP
1313 IF g_lstg_total_records (i) = 0
1314 THEN
1315 fnd_message.set_name ('BIS', 'EDW_CDI_NO_READY_RECORDS');
1316 fnd_message.set_token ('TABLE', g_lstg_table_long_name (i));
1317 write_to_out (fnd_message.get);
1318 --write_to_out('Table '||g_lstg_table_long_name(i)||' has no records with status of ');
1319 --write_to_out('''READY'',''DANGLING'' or ''DUPLICATE''.');
1320 fnd_message.set_name ('BIS', 'EDW_CDI_NO_DUP_DATA_CHECK');
1321 write_to_out (fnd_message.get);
1322 --write_to_out('No duplicate check done');
1323 RETURN TRUE;
1324 END IF;
1325
1326 l_stmt := 'create table '
1327 || g_lstg_dup_pk_table (i)
1328 || ' tablespace '
1329 || g_op_table_space;
1330
1331 IF g_parallel IS NOT NULL
1332 THEN
1333 l_stmt := l_stmt
1334 || ' parallel (degree '
1335 || g_parallel
1336 || ') ';
1337 END IF;
1338
1339 l_stmt := l_stmt
1340 || ' as select '
1341 || g_lstg_pk (i)
1342 || ' PK ,count(1) dup_count from '
1343 || g_lstg_pk_table (i)
1344 || ' having count('
1345 || g_lstg_pk (i)
1346 || ')>1 group by '
1347 || g_lstg_pk (i);
1348
1349 IF edw_owb_collection_util.drop_table (g_lstg_dup_pk_table (i)) =
1350 FALSE
1351 THEN
1352 NULL;
1353 END IF;
1354
1355 IF g_debug
1356 THEN
1357 write_to_log_n ( 'going to execute '
1358 || l_stmt
1359 || get_time);
1360 END IF;
1361
1362 EXECUTE IMMEDIATE l_stmt;
1363
1364 IF g_debug
1365 THEN
1366 write_to_log_n (
1367 'Created '
1368 || g_lstg_dup_pk_table (i)
1369 || ' with '
1370 || SQL%ROWCOUNT
1371 || ' rows'
1372 || get_time
1373 );
1374 END IF;
1375
1376 edw_owb_collection_util.analyze_table_stats (
1377 SUBSTR (
1378 g_lstg_dup_pk_table (i),
1379 INSTR (g_lstg_dup_pk_table (i), '.')
1380 + 1,
1381 LENGTH (g_lstg_dup_pk_table (i))
1382 ),
1383 SUBSTR (
1384 g_lstg_dup_pk_table (i),
1385 1,
1386 INSTR (g_lstg_dup_pk_table (i), '.')
1387 - 1
1388 )
1389 );
1390 l_stmt := 'select sum(dup_count) from '
1391 || g_lstg_dup_pk_table (i);
1392 l_num_dup := NULL;
1393
1394 IF g_debug
1395 THEN
1396 write_to_log_n ( 'going to execute '
1397 || l_stmt
1398 || get_time);
1399 END IF;
1400
1404 fnd_message.set_name ('BIS', 'EDW_CDI_RECORDS_DUPLICATE');
1401 OPEN cv FOR l_stmt;
1402 FETCH cv INTO l_num_dup;
1403 CLOSE cv;
1405 fnd_message.set_token ('TABLE', g_lstg_table_long_name (i));
1406 fnd_message.set_token ('DUPLICATE', NVL (l_num_dup, 0));
1407 fnd_message.set_token ('TOTAL', g_lstg_total_records (i));
1408 write_to_out (fnd_message.get);
1409
1410 --if l_num_dup is null then
1411 --write_to_out('Table '||g_lstg_table_long_name(i)||' 0 records are duplicate ');
1412 --else
1413 --write_to_out('Table '||g_lstg_table_long_name(i)||' '||l_num_dup||' records out of '||
1414 --g_lstg_total_records(i)||' are duplicate ');
1415 --end if;
1416 IF g_results_table_flag
1417 THEN
1418 IF l_num_dup IS NULL
1419 THEN
1420 l_num_dup_log := 0;
1421 ELSE
1422 l_num_dup_log := l_num_dup;
1423 END IF;
1424
1425 IF log_into_cdi_results_table (
1426 g_object_name,
1427 g_object_type,
1428 g_object_id,
1429 g_lstg_tables (i),
1430 g_lstg_tables_id (i),
1431 g_lstg_pk (i),
1432 g_lstg_pk_id (i),
1433 NULL,
1434 NULL,
1435 NULL,
1436 NULL,
1437 NULL,
1438 NULL,
1439 NULL,
1440 l_num_dup_log,
1441 NULL,
1442 g_lstg_total_records (i),
1443 'DUPLICATE'
1444 ) = FALSE
1445 THEN
1446 RETURN FALSE;
1447 END IF;
1448 END IF;
1449
1450 IF l_num_dup > 0 AND g_sample_on
1451 THEN
1452 l_number_dup_str := 1;
1453 l_stmt := 'select PK,dup_count from '
1454 || g_lstg_dup_pk_table (i);
1455
1456 IF g_debug
1457 THEN
1458 write_to_log_n ( 'going to execute '
1459 || l_stmt
1460 || get_time);
1461 END IF;
1462
1463 write_to_out (' ');
1464 fnd_message.set_name ('BIS', 'EDW_CDI_SAMPLE_DUPLICATE');
1465 write_to_out (fnd_message.get);
1466 --write_to_out('Sample duplicate records and their count');
1467 OPEN cv FOR l_stmt;
1468
1469 LOOP
1470 FETCH cv INTO l_dup_str (1), l_dup_count (1);
1471 EXIT WHEN cv%NOTFOUND;
1472
1473 IF g_number_max_sample IS NOT NULL
1474 AND l_number_dup_str > g_number_max_sample
1475 THEN
1476 EXIT;
1477 END IF;
1478
1479 IF log_into_cdi_dang_table (
1480 g_lstg_pk_id (i),
1481 g_lstg_tables_id (i),
1482 NULL,
1483 l_dup_str (1),
1484 l_dup_count (1),
1485 NULL
1486 ) = FALSE
1487 THEN
1488 RETURN FALSE;
1489 END IF;
1490
1491 IF l_number_dup_str <= g_number_sample
1492 THEN
1493 write_to_out ( l_dup_str (1)
1494 || ' ('
1495 || l_dup_count (1)
1496 || ')');
1497 END IF;
1498
1499 l_number_dup_str := l_number_dup_str
1500 + 1;
1501 END LOOP;
1502
1503 CLOSE cv;
1504 l_number_dup_str := l_number_dup_str
1505 - 1;
1506 write_to_out (' ');
1507 END IF;
1508
1509 IF edw_owb_collection_util.drop_table (g_lstg_dup_pk_table (i)) =
1510 FALSE
1511 THEN
1512 NULL;
1513 END IF;
1514 END LOOP;
1515
1516 RETURN TRUE;
1517 EXCEPTION
1518 WHEN OTHERS
1519 THEN
1520 write_to_out_log_n (SQLERRM);
1521 g_status_message := SQLERRM;
1522 RETURN FALSE;
1523 END execute_dim_duplicate_check;
1524
1525 FUNCTION execute_dim_dangling_check (p_dim_name IN VARCHAR2)
1526 RETURN BOOLEAN
1527 IS
1528 BEGIN
1529 IF create_main_lstg_fk_tables = FALSE
1530 THEN
1531 RETURN FALSE;
1532 END IF;
1533
1534 IF create_lstg_fk_tables = FALSE
1535 THEN
1536 RETURN FALSE;
1537 END IF;
1538
1539 IF execute_dim_dang_check_lstg (p_dim_name) = FALSE
1540 THEN
1541 RETURN FALSE;
1542 END IF;
1543
1544 IF g_check_against_ltc = TRUE
1545 THEN
1546 IF execute_dim_dang_check_ltc (p_dim_name) = FALSE
1547 THEN
1548 RETURN FALSE;
1549 END IF;
1550 END IF;
1551
1552 RETURN TRUE;
1553 EXCEPTION
1557 g_status_message := SQLERRM;
1554 WHEN OTHERS
1555 THEN
1556 write_to_out_log_n (SQLERRM);
1558 RETURN FALSE;
1559 END execute_dim_dangling_check;
1560
1561 FUNCTION create_main_lstg_fk_tables
1562 RETURN BOOLEAN
1563 IS
1564 l_lstg_index NUMBER;
1565 l_stmt VARCHAR2 (20000);
1566 l_fk edw_owb_collection_util.varchartabletype;
1567 l_number_fk NUMBER;
1568 l_table_considered edw_owb_collection_util.varchartabletype;
1569 l_number_table_considered NUMBER;
1570 BEGIN
1571 IF g_debug
1572 THEN
1573 write_to_log_n ( 'In create_main_lstg_fk_tables '
1574 || get_time);
1575 END IF;
1576
1577 g_number_main_lstg_fk_table := 0;
1578 l_number_table_considered := 0;
1579
1580 FOR i IN 1 .. g_lstg_fk_number
1581 LOOP
1582 --g_main_lstg_fk_table
1583 --g_number_main_lstg_fk_table
1584 l_number_fk := 0;
1585
1586 IF edw_owb_collection_util.value_in_table (
1587 l_table_considered,
1588 l_number_table_considered,
1589 g_lstg_fk_table (i)
1590 ) = FALSE
1591 THEN
1592 l_number_table_considered := l_number_table_considered
1593 + 1;
1594 l_table_considered (l_number_table_considered) :=
1595 g_lstg_fk_table (i);
1596 l_lstg_index :=
1597 edw_owb_collection_util.index_in_table (
1598 g_lstg_tables,
1599 g_number_lstg_tables,
1600 g_lstg_fk_table (i)
1601 );
1602
1603 IF g_debug
1604 THEN
1605 write_to_log ( 'l_lstg_index='
1606 || l_lstg_index);
1607 END IF;
1608
1609 FOR j IN 1 .. g_lstg_fk_number
1610 LOOP
1611 IF g_lstg_fk_table (j) = g_lstg_fk_table (i)
1612 THEN
1613 IF edw_owb_collection_util.value_in_table (
1614 l_fk,
1615 l_number_fk,
1616 g_lstg_fk (j)
1617 ) = FALSE
1618 THEN
1619 l_number_fk := l_number_fk
1620 + 1;
1621 l_fk (l_number_fk) := g_lstg_fk (j);
1622 END IF;
1623 END IF;
1624 END LOOP;
1625
1626 g_number_main_lstg_fk_table := g_number_main_lstg_fk_table
1627 + 1;
1628 g_main_lstg_fk_table (g_number_main_lstg_fk_table) :=
1629 g_bis_owner
1630 || '.'
1631 || SUBSTR (
1632 g_lstg_fk_table (i),
1633 1,
1634 LENGTH (g_lstg_fk_table (i))
1635 - 4
1636 )
1637 || 'MF'
1638 || i;
1639 g_main_lstg_fk_table_lstg (g_number_main_lstg_fk_table) :=
1640 g_lstg_fk_table (i);
1641 l_stmt := 'create table '
1642 || g_main_lstg_fk_table (g_number_main_lstg_fk_table)
1643 || ' tablespace '
1644 || g_op_table_space;
1645
1646 IF g_parallel IS NOT NULL
1647 THEN
1648 l_stmt := l_stmt
1649 || ' parallel (degree '
1650 || g_parallel
1651 || ') ';
1652 l_stmt := l_stmt
1653 || ' as select /*+ORDERED*/ /*+PARALLEL('
1654 || g_lstg_fk_table (i)
1655 || ','
1656 || g_parallel
1657 || ')*/ ';
1658 ELSE
1659 l_stmt := l_stmt
1660 || ' as select /*+ORDERED*/ ';
1661 END IF;
1662
1663 FOR j IN 1 .. l_number_fk
1664 LOOP
1665 l_stmt := l_stmt
1666 || g_lstg_fk_table (i)
1667 || '.'
1668 || l_fk (j)
1669 || ',';
1670 END LOOP;
1671
1672 IF g_lstg_instance_col (l_lstg_index) IS NOT NULL
1673 THEN
1674 l_stmt := l_stmt
1675 || g_lstg_fk_table (i)
1676 || '.'
1677 || g_lstg_instance_col (l_lstg_index)
1678 || ',';
1679 END IF;
1680
1681 l_stmt := l_stmt
1682 || g_lstg_fk_table (i)
1683 || '.rowid row_id from '
1684 || g_lstg_pk_table (l_lstg_index)
1685 || ','
1686 || g_lstg_fk_table (i)
1687 || ' where '
1691 || '.row_id';
1688 || g_lstg_fk_table (i)
1689 || '.rowid='
1690 || g_lstg_pk_table (l_lstg_index)
1692
1693 IF edw_owb_collection_util.drop_table (
1694 g_main_lstg_fk_table (g_number_main_lstg_fk_table)
1695 ) = FALSE
1696 THEN
1697 NULL;
1698 END IF;
1699
1700 IF g_debug
1701 THEN
1702 write_to_log_n ( 'going to execute '
1703 || l_stmt
1704 || get_time);
1705 END IF;
1706
1707 EXECUTE IMMEDIATE l_stmt;
1708
1709 IF g_debug
1710 THEN
1711 write_to_log_n (
1712 'Created '
1713 || g_main_lstg_fk_table (g_number_main_lstg_fk_table)
1714 || ' with '
1715 || SQL%ROWCOUNT
1716 || ' rows'
1717 || get_time
1718 );
1719 END IF;
1720
1721 /*
1722 l_stmt:='create unique index '||g_main_lstg_fk_table(g_number_main_lstg_fk_table)||'u on '||
1723 g_main_lstg_fk_table(g_number_main_lstg_fk_table)||'(row_id)';
1724 if g_debug then
1725 write_to_log_n('going to execute '||l_stmt||get_time);
1726 end if;
1727 execute immediate l_stmt;
1728 if g_debug then
1729 write_to_log_n('Created unique index on '||g_main_lstg_fk_table(g_number_main_lstg_fk_table)||get_time);
1730 end if;*/
1731
1732 edw_owb_collection_util.analyze_table_stats (
1733 SUBSTR (
1734 g_main_lstg_fk_table (g_number_main_lstg_fk_table),
1735 INSTR (
1736 g_main_lstg_fk_table (g_number_main_lstg_fk_table),
1737 '.'
1738 )
1739 + 1,
1740 LENGTH (g_main_lstg_fk_table (g_number_main_lstg_fk_table))
1741 ),
1742 SUBSTR (
1743 g_main_lstg_fk_table (g_number_main_lstg_fk_table),
1744 1,
1745 INSTR (
1746 g_main_lstg_fk_table (g_number_main_lstg_fk_table),
1747 '.'
1748 )
1749 - 1
1750 )
1751 );
1752 END IF;
1753 END LOOP;
1754
1755 RETURN TRUE;
1756 EXCEPTION
1757 WHEN OTHERS
1758 THEN
1759 write_to_out_log_n (SQLERRM);
1760 g_status_message := SQLERRM;
1761 RETURN FALSE;
1762 END create_main_lstg_fk_tables;
1763
1764 FUNCTION create_lstg_fk_tables
1765 RETURN BOOLEAN
1766 IS
1767 l_lstg_index NUMBER;
1768 BEGIN
1769 IF g_debug
1770 THEN
1771 write_to_log_n ( 'In create_lstg_fk_tables '
1772 || get_time);
1773 END IF;
1774
1775 FOR i IN 1 .. g_lstg_fk_number
1776 LOOP
1777 l_lstg_index :=
1778 edw_owb_collection_util.index_in_table (
1779 g_main_lstg_fk_table_lstg,
1780 g_number_main_lstg_fk_table,
1781 g_lstg_fk_table (i)
1782 );
1783 g_lstg_fk_hold_table (i) := g_main_lstg_fk_table (l_lstg_index);
1784
1785 IF g_debug
1786 THEN
1787 write_to_log (
1788 g_lstg_fk_table (i)
1789 || ' '
1790 || g_lstg_fk_hold_table (i)
1791 );
1792 END IF;
1793 END LOOP;
1794
1795 RETURN TRUE;
1796 EXCEPTION
1797 WHEN OTHERS
1798 THEN
1799 write_to_out_log_n (SQLERRM);
1800 g_status_message := SQLERRM;
1801 RETURN FALSE;
1802 END create_lstg_fk_tables;
1803
1804 FUNCTION drop_lstg_fk_tables
1805 RETURN BOOLEAN
1806 IS
1807 BEGIN
1808 IF g_debug
1809 THEN
1810 write_to_log_n ( 'In drop_lstg_fk_tables '
1811 || get_time);
1812 END IF;
1813
1814 FOR i IN 1 .. g_lstg_fk_number
1815 LOOP
1816 IF edw_owb_collection_util.drop_table (g_lstg_fk_hold_table (i)) =
1817 FALSE
1818 THEN
1819 NULL;
1820 END IF;
1821 END LOOP;
1822
1823 RETURN TRUE;
1824 EXCEPTION
1825 WHEN OTHERS
1826 THEN
1827 write_to_out_log_n (SQLERRM);
1828 g_status_message := SQLERRM;
1829 RETURN FALSE;
1830 END drop_lstg_fk_tables;
1831
1832 FUNCTION drop_lstg_pk_tables
1833 RETURN BOOLEAN
1834 IS
1835 BEGIN
1836 IF g_debug
1837 THEN
1838 write_to_log_n ( 'In drop_lstg_pk_tables '
1839 || get_time);
1840 END IF;
1844 IF edw_owb_collection_util.drop_table (g_lstg_pk_table (i)) = FALSE
1841
1842 FOR i IN 1 .. g_number_lstg_tables
1843 LOOP
1845 THEN
1846 NULL;
1847 END IF;
1848 END LOOP;
1849
1850 RETURN TRUE;
1851 EXCEPTION
1852 WHEN OTHERS
1853 THEN
1854 write_to_out_log_n (SQLERRM);
1855 g_status_message := SQLERRM;
1856 RETURN FALSE;
1857 END drop_lstg_pk_tables;
1858
1859 FUNCTION execute_dim_dang_check_lstg (p_dim_name IN VARCHAR2)
1860 RETURN BOOLEAN
1861 IS
1862 l_stmt VARCHAR2 (30000);
1863
1864 TYPE curtyp IS REF CURSOR;
1865
1866 cv curtyp;
1867 l_num_dang NUMBER;
1868 l_number_dang_str NUMBER;
1869 l_total_recs NUMBER;
1870 l_dang_str edw_owb_collection_util.varchartabletype;
1871 l_dang_count edw_owb_collection_util.numbertabletype;
1872 l_dang_instance edw_owb_collection_util.varchartabletype;
1873 l_lstg_fk_table_long edw_owb_collection_util.varchartabletype;
1874 l_lstg_index NUMBER;
1875 l_lstg_parent_index NUMBER;
1876 l_fk_ok_number NUMBER;
1877 BEGIN
1878 FOR i IN 1 .. g_lstg_fk_number
1879 LOOP
1880 l_lstg_fk_table_long (i) := get_lstg_long_name (g_lstg_fk_table (i));
1881 END LOOP;
1882
1883 IF g_debug
1884 THEN
1885 write_to_log_n ('The long names for the interface tables');
1886
1887 FOR i IN 1 .. g_lstg_fk_number
1888 LOOP
1889 write_to_log (
1890 l_lstg_fk_table_long (i)
1891 || '('
1892 || g_lstg_fk_table (i)
1893 || ')'
1894 );
1895 END LOOP;
1896 END IF;
1897
1898 write_to_out (' ');
1899 fnd_message.set_name ('BIS', 'EDW_CDI_DANGLING_CHECK_IT');
1900 write_to_out (fnd_message.get);
1901 --write_to_out('Dangling Records Check against parent LSTG Tables');
1902 write_to_out (' ');
1903
1904 FOR i IN 1 .. g_number_hier_distinct
1905 LOOP
1906 write_to_out (' ');
1907 write_to_out (' ');
1908 fnd_message.set_name ('BIS', 'EDW_CDI_HIERARCHY');
1909 fnd_message.set_token ('HIER', g_hier_distinct (i));
1910 write_to_out (fnd_message.get);
1911
1912 --write_to_out('Hierarchy '||g_hier_distinct(i));
1913 FOR j IN 1 .. g_lstg_fk_number
1914 LOOP
1915 IF g_hier (j) = g_hier_distinct (i)
1916 THEN
1917 l_total_recs := get_num_recs_lstg (g_lstg_fk_table (j));
1918
1919 IF g_debug
1920 THEN
1921 write_to_log (
1922 'Table '
1923 || g_lstg_fk_table (j)
1924 || ' '
1925 || l_total_recs
1926 || ' total recs returned'
1927 );
1928 END IF;
1929
1930 IF l_total_recs > 0
1931 THEN
1932 l_lstg_index :=
1933 edw_owb_collection_util.index_in_table (
1934 g_lstg_tables,
1935 g_number_lstg_tables,
1936 g_lstg_fk_table (j)
1937 );
1938 --join with the parent lstg pk table and create the ok table
1939 l_lstg_parent_index :=
1940 edw_owb_collection_util.index_in_table (
1941 g_lstg_tables,
1942 g_number_lstg_tables,
1943 g_parent_lstg_fk_table (j)
1944 );
1945
1946 IF g_parent_lstg_fk_table (j) <> 'ALL'
1947 AND l_lstg_parent_index IS NULL
1948 THEN
1949 write_to_log_n (
1950 'Parent lstg table not found for '
1951 || g_lstg_fk_table (j)
1952 );
1953 RETURN FALSE;
1954 END IF;
1955
1956 IF g_parent_lstg_fk_table (j) <> 'ALL'
1957 THEN
1958 l_stmt := 'create table '
1959 || g_lstg_ok_table (j)
1960 || ' tablespace '
1961 || g_op_table_space;
1962
1963 IF g_parallel IS NOT NULL
1964 THEN
1965 l_stmt := l_stmt
1966 || ' parallel (degree '
1967 || g_parallel
1968 || ') ';
1969 END IF;
1970
1971 l_stmt := l_stmt
1972 || ' as select /*+ORDERED*/ '
1973 || g_lstg_fk_hold_table (j)
1974 || '.row_id ';
1975 l_stmt := l_stmt
1976 || ' from '
1980 || ' where '
1977 || g_lstg_fk_hold_table (j)
1978 || ','
1979 || g_lstg_pk_table (l_lstg_parent_index)
1981 || g_lstg_fk_hold_table (j)
1982 || '.'
1983 || g_lstg_fk (j)
1984 || '='
1985 || g_lstg_pk_table (l_lstg_parent_index)
1986 || '.'
1987 || g_lstg_pk (l_lstg_parent_index)
1988 || ' union select '
1989 || g_lstg_fk_hold_table (j)
1990 || '.row_id ';
1991 l_stmt := l_stmt
1992 || ' from '
1993 || g_lstg_fk_hold_table (j)
1994 || ' where '
1995 || g_lstg_fk_hold_table (j)
1996 || '.'
1997 || g_lstg_fk (j)
1998 || '=''NA_EDW''';
1999
2000 IF edw_owb_collection_util.drop_table (
2001 g_lstg_ok_table (j)
2002 ) = FALSE
2003 THEN
2004 NULL;
2005 END IF;
2006
2007 IF g_debug
2008 THEN
2009 write_to_log_n (
2010 'going to execute '
2011 || l_stmt
2012 || get_time
2013 );
2014 END IF;
2015
2016 EXECUTE IMMEDIATE l_stmt;
2017 l_fk_ok_number := SQL%ROWCOUNT;
2018
2019 IF g_debug
2020 THEN
2021 write_to_log_n (
2022 'Created '
2023 || g_lstg_ok_table (j)
2024 || ' with '
2025 || l_fk_ok_number
2026 || ' rows'
2027 || get_time
2028 );
2029 END IF;
2030
2031 edw_owb_collection_util.analyze_table_stats (
2032 SUBSTR (
2033 g_lstg_ok_table (j),
2034 INSTR (g_lstg_ok_table (j), '.')
2035 + 1,
2036 LENGTH (g_lstg_ok_table (j))
2037 ),
2038 SUBSTR (
2039 g_lstg_ok_table (j),
2040 1,
2041 INSTR (g_lstg_ok_table (j), '.')
2042 - 1
2043 )
2044 );
2045 l_num_dang := l_total_recs
2046 - l_fk_ok_number;
2047 ELSE
2048 l_stmt := 'select count(1) from '
2049 || g_lstg_fk_hold_table (j)
2050 || ' where '
2051 || g_lstg_fk (j)
2052 || '=''ALL''';
2053
2054 IF g_debug
2055 THEN
2056 write_to_log_n (
2057 'going to execute '
2058 || l_stmt
2059 || get_time
2060 );
2061 END IF;
2062
2063 OPEN cv FOR l_stmt;
2064 FETCH cv INTO l_fk_ok_number;
2065 CLOSE cv;
2066 l_num_dang := l_total_recs
2067 - l_fk_ok_number;
2068
2069 IF g_debug
2070 THEN
2071 write_to_log_n (get_time);
2072 END IF;
2073 END IF;
2074
2075 IF l_num_dang <= 0
2076 THEN
2077 IF g_parent_lstg_fk_table (j) = 'ALL'
2078 THEN
2079 write_to_out (' ');
2080 fnd_message.set_name ('BIS', 'EDW_CDI_TABLE_AND_FK');
2081 fnd_message.set_token (
2082 'TABLE',
2083 l_lstg_fk_table_long (j)
2084 );
2085 fnd_message.set_token ('FK', g_lstg_fk_long (j));
2086 write_to_out (fnd_message.get);
2087
2088 --write_to_out('Table '||l_lstg_fk_table_long(j)||', Foreign Key '||g_lstg_fk_long(j));
2089 IF g_results_table_flag
2090 THEN
2091 IF log_into_cdi_results_table (
2092 g_object_name,
2093 g_object_type,
2094 g_object_id,
2095 g_lstg_fk_table (j),
2096 g_lstg_fk_table_id (j),
2100 g_lstg_fk_id (j),
2097 NULL,
2098 NULL,
2099 g_lstg_fk (j), --p_interface_table_fk
2101 NULL, --p_parent_table
2102 NULL, --p_parent_table_id
2103 NULL, --p_parent_table_pk
2104 NULL,
2105 l_num_dang, --p_number_dangling
2106 NULL,
2107 NULL,
2108 l_total_recs,
2109 'DANGLING'
2110 ) = FALSE
2111 THEN
2112 RETURN FALSE;
2113 END IF;
2114 END IF;
2115 ELSE
2116 write_to_out (' ');
2117 fnd_message.set_name ('BIS', 'EDW_CDI_TABLE_AND_FK');
2118 fnd_message.set_token (
2119 'TABLE',
2120 l_lstg_fk_table_long (j)
2121 );
2122 fnd_message.set_token ('FK', g_lstg_fk_long (j));
2123 write_to_out (fnd_message.get);
2124 --write_to_out('Table '||l_lstg_fk_table_long(j)||', Foreign Key '||g_lstg_fk_long(j));
2125 fnd_message.set_name (
2126 'BIS',
2127 'EDW_CDI_PARENT_TABLE_AND_PK'
2128 );
2129 fnd_message.set_token (
2130 'TABLE',
2131 get_lstg_long_name (g_parent_lstg_fk_table (j))
2132 );
2133 fnd_message.set_token (
2134 'PK',
2135 get_lstg_pk (g_parent_lstg_fk_table (j))
2136 );
2137 write_to_out (fnd_message.get);
2138
2139 --write_to_out('Parent Table '||get_lstg_long_name(g_parent_lstg_fk_table(j))||
2140 --', Primary Key '||get_lstg_pk(g_parent_lstg_fk_table(j)));
2141 IF g_results_table_flag
2142 THEN
2143 IF log_into_cdi_results_table (
2144 g_object_name,
2145 g_object_type,
2146 g_object_id,
2147 g_lstg_fk_table (j),
2148 g_lstg_fk_table_id (j),
2149 NULL,
2150 NULL,
2151 g_lstg_fk (j), --p_interface_table_fk
2152 g_lstg_fk_id (j),
2153 g_parent_lstg_fk_table (j), --p_parent_table
2154 g_parent_lstg_fk_table_id (j),
2155 --p_parent_table_id
2156 g_lstg_pk (l_lstg_parent_index),
2157 --p_parent_table_pk
2158 NULL,
2159 l_num_dang, --p_number_dangling
2160 NULL,
2161 NULL,
2162 l_total_recs,
2163 'DANGLING'
2164 ) = FALSE
2165 THEN
2166 RETURN FALSE;
2167 END IF;
2168 END IF;
2169 END IF;
2170
2171 fnd_message.set_name ('BIS', 'EDW_CDI_NO_RECS_DANGLING');
2172 write_to_out (fnd_message.get);
2173 --write_to_out(' 0 records are dangling');
2174 ELSE
2175 IF g_parent_lstg_fk_table (j) = 'ALL'
2176 THEN
2177 write_to_out (' ');
2178 fnd_message.set_name ('BIS', 'EDW_CDI_TABLE_AND_FK');
2179 fnd_message.set_token (
2180 'TABLE',
2181 l_lstg_fk_table_long (j)
2182 );
2183 fnd_message.set_token ('FK', g_lstg_fk_long (j));
2184 write_to_out (fnd_message.get);
2185
2186 --write_to_out('Table '||l_lstg_fk_table_long(j)||', Foreign Key '||g_lstg_fk_long(j));
2187 IF g_results_table_flag
2188 THEN
2189 IF log_into_cdi_results_table (
2190 g_object_name,
2191 g_object_type,
2192 g_object_id,
2193 g_lstg_fk_table (j),
2194 g_lstg_fk_table_id (j),
2195 NULL,
2196 NULL,
2197 g_lstg_fk (j), --p_interface_table_fk
2198 g_lstg_fk_id (j),
2199 NULL, --p_parent_table
2200 NULL, --p_parent_table_id
2204 NULL,
2201 NULL, --p_parent_table_pk
2202 NULL, --p_parent_table_pk_id
2203 l_num_dang, --p_number_dangling
2205 NULL,
2206 l_total_recs,
2207 'DANGLING'
2208 ) = FALSE
2209 THEN
2210 RETURN FALSE;
2211 END IF;
2212 END IF;
2213 ELSE
2214 write_to_out (' ');
2215 fnd_message.set_name ('BIS', 'EDW_CDI_TABLE_AND_FK');
2216 fnd_message.set_token (
2217 'TABLE',
2218 l_lstg_fk_table_long (j)
2219 );
2220 fnd_message.set_token ('FK', g_lstg_fk_long (j));
2221 write_to_out (fnd_message.get);
2222 --write_to_out('Table '||l_lstg_fk_table_long(j)||', Foreign Key '||g_lstg_fk_long(j));
2223 fnd_message.set_name ('BIS', 'EDW_CDI_NO_LONG_DIM');
2224 fnd_message.set_token (
2225 'TABLE',
2226 get_lstg_long_name (g_parent_lstg_fk_table (j))
2227 );
2228 fnd_message.set_token (
2229 'PK',
2230 get_lstg_pk (g_parent_lstg_fk_table (j))
2231 );
2232 write_to_out (fnd_message.get);
2233
2234 --write_to_out('Parent Table '||get_lstg_long_name(g_parent_lstg_fk_table(j))
2235 --||', Primary Key '||get_lstg_pk(g_parent_lstg_fk_table(j)));
2236 IF g_results_table_flag
2237 THEN
2238 IF log_into_cdi_results_table (
2239 g_object_name,
2240 g_object_type,
2241 g_object_id,
2242 g_lstg_fk_table (j),
2243 g_lstg_fk_table_id (j),
2244 NULL,
2245 NULL,
2246 g_lstg_fk (j), --p_interface_table_fk
2247 g_lstg_fk_id (j),
2248 g_parent_lstg_fk_table (j), --p_parent_table
2249 g_parent_lstg_fk_table_id (j),
2250 --p_parent_table_id
2251 g_lstg_pk (l_lstg_parent_index),
2252 --p_parent_table_pk
2253 NULL,
2254 l_num_dang, --p_number_dangling
2255 NULL,
2256 NULL,
2257 l_total_recs,
2258 'DANGLING'
2259 ) = FALSE
2260 THEN
2261 RETURN FALSE;
2262 END IF;
2263 END IF;
2264 END IF;
2265
2266 fnd_message.set_name ('BIS', 'EDW_CDI_RECS_DANGLING');
2267 fnd_message.set_token ('DANGLING', l_num_dang);
2268 fnd_message.set_token ('TOTAL', l_total_recs);
2269 write_to_out (fnd_message.get);
2270 --write_to_out(l_num_dang||' records out of '||l_total_recs||' are dangling');
2271 END IF;
2272
2273 IF l_num_dang > 0 AND g_sample_on
2274 THEN
2275 --make the dang table with MINUS
2276 IF g_parent_lstg_fk_table (j) <> 'ALL'
2277 THEN
2278 l_stmt := 'create table '
2279 || g_lstg_dang_rowid_table (j)
2280 || ' tablespace '
2281 || g_op_table_space;
2282
2283 IF g_parallel IS NOT NULL
2284 THEN
2285 l_stmt := l_stmt
2286 || ' parallel (degree '
2287 || g_parallel
2288 || ') ';
2289 END IF;
2290
2291 l_stmt := l_stmt
2292 || ' as select row_id from '
2293 || g_lstg_fk_hold_table (j)
2294 || ' MINUS select row_id from '
2295 || g_lstg_ok_table (j);
2296
2297 IF edw_owb_collection_util.drop_table (
2298 g_lstg_dang_rowid_table (j)
2299 ) = FALSE
2300 THEN
2301 NULL;
2302 END IF;
2303
2304 IF g_debug
2305 THEN
2306 write_to_log_n (
2307 'going to execute '
2308 || l_stmt
2312
2309 || get_time
2310 );
2311 END IF;
2313 EXECUTE IMMEDIATE l_stmt;
2314
2315 IF g_debug
2316 THEN
2317 write_to_log_n (
2318 'Created '
2319 || g_lstg_dang_rowid_table (j)
2320 || ' with '
2321 || SQL%ROWCOUNT
2322 || ' rows'
2323 || get_time
2324 );
2325 END IF;
2326
2327 edw_owb_collection_util.analyze_table_stats (
2328 SUBSTR (
2329 g_lstg_dang_rowid_table (j),
2330 INSTR (g_lstg_dang_rowid_table (j), '.')
2331 + 1,
2332 LENGTH (g_lstg_dang_rowid_table (j))
2333 ),
2334 SUBSTR (
2335 g_lstg_dang_rowid_table (j),
2336 1,
2337 INSTR (g_lstg_dang_rowid_table (j), '.')
2338 - 1
2339 )
2340 );
2341 --create the dang table
2342 l_stmt := 'select /*+ORDERED*/ '
2343 || g_lstg_fk_hold_table (j)
2344 || '.'
2345 || g_lstg_fk (j);
2346
2347 IF g_lstg_instance_col (l_lstg_index) IS NOT NULL
2348 THEN
2349 l_stmt := l_stmt
2350 || ','
2351 || g_lstg_fk_hold_table (j)
2352 || '.'
2353 || g_lstg_instance_col (l_lstg_index);
2354 END IF;
2355
2356 l_stmt := l_stmt
2357 || ',count(1) from '
2358 || g_lstg_dang_rowid_table (j)
2359 || ','
2360 || g_lstg_fk_hold_table (j)
2361 || ' where '
2362 || g_lstg_fk_hold_table (j)
2363 || '.row_id='
2364 || g_lstg_dang_rowid_table (j)
2365 || '.row_id group by '
2366 || g_lstg_fk_hold_table (j)
2367 || '.'
2368 || g_lstg_fk (j);
2369
2370 IF g_lstg_instance_col (l_lstg_index) IS NOT NULL
2371 THEN
2372 l_stmt := l_stmt
2373 || ','
2374 || g_lstg_fk_hold_table (j)
2375 || '.'
2376 || g_lstg_instance_col (l_lstg_index);
2377 END IF;
2378
2379 l_stmt := l_stmt
2380 || ' order by count(1) desc';
2381 write_to_out (' ');
2382 fnd_message.set_name ('BIS', 'EDW_CDI_SAMPLE_DANGLING');
2383 write_to_out (fnd_message.get);
2384 --write_to_out('Sample dangling records and their count ');
2385 l_number_dang_str := 1;
2386
2387 IF g_debug
2388 THEN
2389 write_to_log_n (
2390 'going to execute '
2391 || l_stmt
2392 || get_time
2393 );
2394 END IF;
2395
2396 OPEN cv FOR l_stmt;
2397 l_dang_instance (1) := NULL;
2398
2399 LOOP
2400 IF g_lstg_instance_col (l_lstg_index) IS NOT NULL
2401 THEN
2402 FETCH cv INTO l_dang_str (1),
2403 l_dang_instance (1),
2404 l_dang_count (1);
2405 ELSE
2406 FETCH cv INTO l_dang_str (1), l_dang_count (1);
2407 END IF;
2408
2409 EXIT WHEN cv%NOTFOUND;
2410
2411 IF g_number_max_sample IS NOT NULL
2412 AND l_number_dang_str > g_number_max_sample
2413 THEN
2414 EXIT;
2415 END IF;
2416
2417 IF log_into_cdi_dang_table (
2418 g_lstg_fk_id (j),
2419 g_lstg_fk_table_id (j),
2420 g_parent_lstg_fk_table_id (j),
2421 l_dang_str (1),
2422 l_dang_count (1),
2426 RETURN FALSE;
2423 l_dang_instance (1)
2424 ) = FALSE
2425 THEN
2427 END IF;
2428
2429 IF l_number_dang_str <= g_number_sample
2430 THEN
2431 IF g_lstg_instance_col (l_lstg_index) IS NOT NULL
2432 THEN
2433 write_to_out (
2434 l_dang_str (1)
2435 || ' ('
2436 || l_dang_instance (1)
2437 || ') ('
2438 || l_dang_count (1)
2439 || ')'
2440 );
2441 ELSE
2442 write_to_out (
2443 l_dang_str (1)
2444 || ' ('
2445 || l_dang_count (1)
2446 || ')'
2447 );
2448 END IF;
2449 END IF;
2450
2451 l_number_dang_str := l_number_dang_str
2452 + 1;
2453 END LOOP;
2454
2455 write_to_out (' ');
2456
2457 IF g_debug
2458 THEN
2459 write_to_log_n (get_time);
2460 END IF;
2461
2462 CLOSE cv;
2463 l_number_dang_str := l_number_dang_str
2464 - 1;
2465
2466 IF edw_owb_collection_util.drop_table (
2467 g_lstg_dang_rowid_table (j)
2468 ) = FALSE
2469 THEN
2470 NULL;
2471 END IF;
2472 ELSE --parent is ALL
2473 write_to_out (' ');
2474 fnd_message.set_name (
2475 'BIS',
2476 'EDW_CDI_SAMPLE_DANGLING'
2477 );
2478 write_to_out (fnd_message.get);
2479 --write_to_out('Sample dangling records and their count ');
2480 write_to_out ( 'ALL('
2481 || l_num_dang
2482 || ')');
2483 write_to_out (' ');
2484 l_dang_instance (1) := NULL;
2485
2486 IF log_into_cdi_dang_table (
2487 g_lstg_fk_id (j),
2488 g_lstg_fk_table_id (j),
2489 NULL,
2490 'ALL',
2491 l_num_dang,
2492 l_dang_instance (1)
2493 ) = FALSE
2494 THEN
2495 RETURN FALSE;
2496 END IF;
2497 END IF;
2498 END IF; --if l_fk_number>l_fk_ok_number and g_sample_on then
2499
2500 IF edw_owb_collection_util.drop_table (g_lstg_ok_table (j)) =
2501 FALSE
2502 THEN
2503 NULL;
2504 END IF;
2505 ELSE --if number of recs in the interface table > 0
2506 write_to_log_n (
2507 'Interface table '
2508 || g_lstg_fk_table (j)
2509 || ' has no records. No dangling check done'
2510 );
2511 END IF;
2512 END IF;
2513 END LOOP;
2514 END LOOP;
2515
2516 RETURN TRUE;
2517 EXCEPTION
2518 WHEN OTHERS
2519 THEN
2520 write_to_out_log_n (SQLERRM);
2521 g_status_message := SQLERRM;
2522 RETURN FALSE;
2523 END execute_dim_dang_check_lstg;
2524
2525 FUNCTION execute_dim_dang_check_ltc (p_dim_name IN VARCHAR2)
2526 RETURN BOOLEAN
2527 IS
2528 l_stmt VARCHAR2 (30000);
2529
2530 TYPE curtyp IS REF CURSOR;
2531
2532 cv curtyp;
2533 l_num_dang NUMBER;
2534 l_total_recs NUMBER;
2535 l_number_dang_str NUMBER;
2536 l_fk_ok_number NUMBER;
2537 l_lstg_index NUMBER;
2538 l_lstg_parent_index NUMBER;
2539 l_dang_str edw_owb_collection_util.varchartabletype;
2540 l_dang_count edw_owb_collection_util.numbertabletype;
2541 l_dang_instance edw_owb_collection_util.varchartabletype;
2542 l_lstg_fk_table_long edw_owb_collection_util.varchartabletype;
2543 BEGIN
2544 FOR i IN 1 .. g_lstg_fk_number
2545 LOOP
2546 l_lstg_fk_table_long (i) := get_lstg_long_name (g_lstg_fk_table (i));
2547 END LOOP;
2548
2552 --write_to_out('Dangling Records Check against Parent Level Tables');
2549 write_to_out ('-------------------------------------------------------');
2550 fnd_message.set_name ('BIS', 'EDW_CDI_DANGLING_CHECK_LTC');
2551 write_to_out (fnd_message.get);
2553 write_to_out ('-------------------------------------------------------');
2554
2555 FOR i IN 1 .. g_number_hier_distinct
2556 LOOP
2557 write_to_out (' ');
2558 write_to_out (' ');
2559 fnd_message.set_name ('BIS', 'EDW_CDI_HIERARCHY');
2560 fnd_message.set_token ('HIER', g_hier_distinct (i));
2561 write_to_out (fnd_message.get);
2562
2563 --write_to_out('Hierarchy '||g_hier_distinct(i));
2564 FOR j IN 1 .. g_lstg_fk_number
2565 LOOP
2566 IF g_hier (j) = g_hier_distinct (i)
2567 THEN
2568 l_total_recs := get_num_recs_lstg (g_lstg_fk_table (j));
2569
2570 IF g_debug
2571 THEN
2572 write_to_log_n (
2573 'Table '
2574 || g_lstg_fk_table (j)
2575 || ' '
2576 || l_total_recs
2577 || ' total recs returned'
2578 );
2579 END IF;
2580
2581 IF l_total_recs > 0
2582 THEN
2583 l_lstg_index :=
2584 edw_owb_collection_util.index_in_table (
2585 g_lstg_tables,
2586 g_number_lstg_tables,
2587 g_lstg_fk_table (j)
2588 );
2589 --join with the parent lstg pk table and create the ok table
2590 l_lstg_parent_index :=
2591 edw_owb_collection_util.index_in_table (
2592 g_lstg_tables,
2593 g_number_lstg_tables,
2594 g_parent_lstg_fk_table (j)
2595 );
2596 l_stmt := 'create table '
2597 || g_lstg_ok_table (j)
2598 || ' tablespace '
2599 || g_op_table_space;
2600
2601 IF g_parallel IS NOT NULL
2602 THEN
2603 l_stmt :=
2604 l_stmt
2605 || ' parallel (degree '
2606 || g_parallel
2607 || ') ';
2608 END IF;
2609
2610 l_stmt := l_stmt
2611 || ' as select /*+ORDERED*/ '
2612 || g_lstg_fk_hold_table (j)
2613 || '.row_id from '
2614 || g_lstg_fk_hold_table (j)
2615 || ','
2616 || g_parent_ltc_fk_table (j)
2617 || ' where '
2618 || g_lstg_fk_hold_table (j)
2619 || '.'
2620 || g_lstg_fk (j)
2621 || '='
2622 || g_parent_ltc_fk_table (j)
2623 || '.'
2624 || g_parent_ltc_fk_table_pk (j);
2625
2626 IF edw_owb_collection_util.drop_table (g_lstg_ok_table (j)) =
2627 FALSE
2628 THEN
2629 NULL;
2630 END IF;
2631
2632 IF g_debug
2633 THEN
2634 write_to_log_n (
2635 'going to execute '
2636 || l_stmt
2637 || get_time
2638 );
2639 END IF;
2640
2641 EXECUTE IMMEDIATE l_stmt;
2642 l_fk_ok_number := SQL%ROWCOUNT;
2643
2644 IF g_debug
2645 THEN
2646 write_to_log_n (
2647 'Created '
2648 || g_lstg_ok_table (j)
2649 || ' with '
2650 || l_fk_ok_number
2651 || ' rows'
2652 || get_time
2653 );
2654 END IF;
2655
2656 edw_owb_collection_util.analyze_table_stats (
2657 SUBSTR (
2658 g_lstg_ok_table (j),
2659 INSTR (g_lstg_ok_table (j), '.')
2660 + 1,
2661 LENGTH (g_lstg_ok_table (j))
2662 ),
2663 SUBSTR (
2664 g_lstg_ok_table (j),
2665 1,
2666 INSTR (g_lstg_ok_table (j), '.')
2667 - 1
2668 )
2669 );
2670 l_num_dang := l_total_recs
2671 - l_fk_ok_number;
2672
2673 IF l_num_dang <= 0
2674 THEN
2678 'TABLE',
2675 write_to_out (' ');
2676 fnd_message.set_name ('BIS', 'EDW_CDI_TABLE_AND_FK');
2677 fnd_message.set_token (
2679 l_lstg_fk_table_long (j)
2680 );
2681 fnd_message.set_token ('FK', g_lstg_fk_long (j));
2682 write_to_out (fnd_message.get);
2683 --write_to_out('Table '||l_lstg_fk_table_long(j)||', Foreign Key '||g_lstg_fk_long(j));
2684 fnd_message.set_name (
2685 'BIS',
2686 'EDW_CDI_PARENT_TABLE_AND_PK'
2687 );
2688 fnd_message.set_token (
2689 'TABLE',
2690 g_parent_ltc_fk_table_long (j)
2691 );
2692 fnd_message.set_token (
2693 'PK',
2694 g_parent_ltc_fk_table_pk_long (j)
2695 );
2696 write_to_out (fnd_message.get);
2697 --write_to_out('Parent Level Table '||g_parent_ltc_fk_table_long(j)||' with Primary Key '||
2698 --g_parent_ltc_fk_table_pk_long(j));
2699 fnd_message.set_name ('BIS', 'EDW_CDI_NO_RECS_DANGLING');
2700 write_to_out (fnd_message.get);
2701 --write_to_out(' 0 records are dangling');
2702 ELSE
2703 write_to_out (' ');
2704 fnd_message.set_name ('BIS', 'EDW_CDI_TABLE_AND_FK');
2705 fnd_message.set_token (
2706 'TABLE',
2707 l_lstg_fk_table_long (j)
2708 );
2709 fnd_message.set_token ('FK', g_lstg_fk_long (j));
2710 write_to_out (fnd_message.get);
2711 --write_to_out('Table '||l_lstg_fk_table_long(j)||', Foreign Key '||g_lstg_fk_long(j));
2712 fnd_message.set_name (
2713 'BIS',
2714 'EDW_CDI_PARENT_TABLE_AND_PK'
2715 );
2716 fnd_message.set_token (
2717 'TABLE',
2718 g_parent_ltc_fk_table_long (j)
2719 );
2720 fnd_message.set_token (
2721 'PK',
2722 g_parent_ltc_fk_table_pk_long (j)
2723 );
2724 write_to_out (fnd_message.get);
2725 --write_to_out('Parent Level Table '||g_parent_ltc_fk_table_long(j)||' with Primary Key '||
2726 --g_parent_ltc_fk_table_pk_long(j));
2727 fnd_message.set_name ('BIS', 'EDW_CDI_RECS_DANGLING');
2728 fnd_message.set_token ('DANGLING', l_num_dang);
2729 fnd_message.set_token ('TOTAL', l_total_recs);
2730 write_to_out (fnd_message.get);
2731 --write_to_out(l_num_dang||' records out of '||l_total_recs||' are dangling');
2732 END IF;
2733
2734 IF g_results_table_flag
2735 THEN
2736 IF log_into_cdi_results_table (
2737 g_object_name,
2738 g_object_type,
2739 g_object_id,
2740 g_lstg_fk_table (j),
2741 g_lstg_fk_table_id (j),
2742 NULL,
2743 NULL,
2744 g_lstg_fk (j), --p_interface_table_fk
2745 g_lstg_fk_id (j),
2746 g_parent_ltc_fk_table (j), --p_parent_table
2747 g_parent_ltc_fk_table_id (j), --p_parent_table_id
2748 g_parent_ltc_fk_table_pk (j), --p_parent_table_pk
2749 NULL,
2750 l_num_dang, --p_number_dangling
2751 NULL,
2752 NULL,
2753 l_total_recs,
2754 'DANGLING'
2755 ) = FALSE
2756 THEN
2757 RETURN FALSE;
2758 END IF;
2759 END IF;
2760
2761 IF l_num_dang > 0 AND g_sample_on
2762 THEN
2763 --make the dang table with MINUS
2764 l_stmt := 'create table '
2765 || g_lstg_dang_rowid_table (j)
2766 || ' tablespace '
2767 || g_op_table_space;
2768
2769 IF g_parallel IS NOT NULL
2770 THEN
2771 l_stmt := l_stmt
2772 || ' parallel (degree '
2773 || g_parallel
2774 || ') ';
2775 END IF;
2776
2777 l_stmt := l_stmt
2778 || ' as select row_id from '
2779 || g_lstg_fk_hold_table (j)
2780 || ' MINUS select row_id from '
2784 g_lstg_dang_rowid_table (j)
2781 || g_lstg_ok_table (j);
2782
2783 IF edw_owb_collection_util.drop_table (
2785 ) = FALSE
2786 THEN
2787 NULL;
2788 END IF;
2789
2790 IF g_debug
2791 THEN
2792 write_to_log_n (
2793 'going to execute '
2794 || l_stmt
2795 || get_time
2796 );
2797 END IF;
2798
2799 EXECUTE IMMEDIATE l_stmt;
2800
2801 IF g_debug
2802 THEN
2803 write_to_log_n (
2804 'Created '
2805 || g_lstg_dang_rowid_table (j)
2806 || ' with '
2807 || SQL%ROWCOUNT
2808 || ' rows'
2809 || get_time
2810 );
2811 END IF;
2812
2813 edw_owb_collection_util.analyze_table_stats (
2814 SUBSTR (
2815 g_lstg_dang_rowid_table (j),
2816 INSTR (g_lstg_dang_rowid_table (j), '.')
2817 + 1,
2818 LENGTH (g_lstg_dang_rowid_table (j))
2819 ),
2820 SUBSTR (
2821 g_lstg_dang_rowid_table (j),
2822 1,
2823 INSTR (g_lstg_dang_rowid_table (j), '.')
2824 - 1
2825 )
2826 );
2827 --create the dang table
2828 l_stmt := 'select /*+ORDERED*/ '
2829 || g_lstg_fk_hold_table (j)
2830 || '.'
2831 || g_lstg_fk (j);
2832
2833 IF g_lstg_instance_col (l_lstg_index) IS NOT NULL
2834 THEN
2835 l_stmt := l_stmt
2836 || ','
2837 || g_lstg_fk_hold_table (j)
2838 || '.'
2839 || g_lstg_instance_col (l_lstg_index);
2840 END IF;
2841
2842 l_stmt := l_stmt
2843 || ' ,count(1) from '
2844 || g_lstg_dang_rowid_table (j)
2845 || ','
2846 || g_lstg_fk_hold_table (j)
2847 || ' where '
2848 || g_lstg_fk_hold_table (j)
2849 || '.row_id='
2850 || g_lstg_dang_rowid_table (j)
2851 || '.row_id group by '
2852 || g_lstg_fk_hold_table (j)
2853 || '.'
2854 || g_lstg_fk (j);
2855
2856 IF g_lstg_instance_col (l_lstg_index) IS NOT NULL
2857 THEN
2858 l_stmt := l_stmt
2859 || ','
2860 || g_lstg_fk_hold_table (j)
2861 || '.'
2862 || g_lstg_instance_col (l_lstg_index);
2863 END IF;
2864
2865 l_stmt := l_stmt
2866 || ' order by count(1) desc';
2867 write_to_out (' ');
2868 fnd_message.set_name ('BIS', 'EDW_CDI_SAMPLE_DANGLING');
2869 write_to_out (fnd_message.get);
2870 --write_to_out('Sample dangling records and their count ');
2871 l_number_dang_str := 1;
2872
2873 IF g_debug
2874 THEN
2875 write_to_log_n (
2876 'going to execute '
2877 || l_stmt
2878 || get_time
2879 );
2880 END IF;
2881
2882 OPEN cv FOR l_stmt;
2883 l_dang_instance (1) := NULL;
2884
2885 LOOP
2886 IF g_lstg_instance_col (l_lstg_index) IS NOT NULL
2887 THEN
2888 FETCH cv INTO l_dang_str (1),
2889 l_dang_instance (1),
2890 l_dang_count (1);
2891 ELSE
2892 FETCH cv INTO l_dang_str (1), l_dang_count (1);
2893 END IF;
2894
2895 EXIT WHEN cv%NOTFOUND;
2896
2897 IF g_number_max_sample IS NOT NULL
2898 AND l_number_dang_str > g_number_max_sample
2899 THEN
2903 IF log_into_cdi_dang_table (
2900 EXIT;
2901 END IF;
2902
2904 g_lstg_fk_id (j),
2905 g_lstg_fk_table_id (j),
2906 g_parent_ltc_fk_table_id (j),
2907 l_dang_str (1),
2908 l_dang_count (1),
2909 l_dang_instance (1)
2910 ) = FALSE
2911 THEN
2912 RETURN FALSE;
2913 END IF;
2914
2915 IF l_number_dang_str <= g_number_sample
2916 THEN
2917 IF g_lstg_instance_col (l_lstg_index) IS NOT NULL
2918 THEN
2919 write_to_out (
2920 l_dang_str (1)
2921 || ' ('
2922 || l_dang_instance (1)
2923 || ') ('
2924 || l_dang_count (1)
2925 || ')'
2926 );
2927 ELSE
2928 write_to_out (
2929 l_dang_str (1)
2930 || ' ('
2931 || l_dang_count (1)
2932 || ')'
2933 );
2934 END IF;
2935 END IF;
2936
2937 l_number_dang_str := l_number_dang_str
2938 + 1;
2939 END LOOP;
2940
2941 CLOSE cv;
2942
2943 IF g_debug
2944 THEN
2945 write_to_log_n (get_time);
2946 END IF;
2947
2948 write_to_out (' ');
2949 l_number_dang_str := l_number_dang_str
2950 - 1;
2951
2952 IF edw_owb_collection_util.drop_table (
2953 g_lstg_dang_rowid_table (j)
2954 ) = FALSE
2955 THEN
2956 NULL;
2957 END IF;
2958 END IF; --if l_fk_number>l_fk_ok_number and g_sample_on then
2959
2960 IF edw_owb_collection_util.drop_table (g_lstg_ok_table (j)) =
2961 FALSE
2962 THEN
2963 NULL;
2964 END IF;
2965 END IF;
2966 END IF;
2967 END LOOP;
2968 END LOOP;
2969
2970 RETURN TRUE;
2971 EXCEPTION
2972 WHEN OTHERS
2973 THEN
2974 write_to_out_log_n (SQLERRM);
2975 g_status_message := SQLERRM;
2976 RETURN FALSE;
2977 END execute_dim_dang_check_ltc;
2978
2979 FUNCTION execute_hier_count (p_dim_name IN VARCHAR2)
2980 RETURN BOOLEAN
2981 IS
2982 TYPE curtyp IS REF CURSOR;
2983
2984 cv curtyp;
2985 l_hier_number NUMBER;
2986 BEGIN
2987 IF g_debug
2988 THEN
2989 write_to_log_n ( 'Going to execute '
2990 || g_hier_stmt_num);
2991 END IF;
2992
2993 write_to_out (' ');
2994 write_to_out (
2995 'Total number of records making into the WH'
2996 || get_time
2997 );
2998
2999 IF g_debug
3000 THEN
3001 write_to_log_n (
3002 'going to execute '
3003 || g_hier_stmt_num
3004 || get_time
3005 );
3006 END IF;
3007
3008 OPEN cv FOR g_hier_stmt_num;
3009 FETCH cv INTO l_hier_number;
3010
3011 IF l_hier_number IS NULL
3012 OR l_hier_number = 0
3013 THEN
3014 write_to_out ('0 total records will make it into the WH from');
3015 write_to_out ('the Level Interface Tables');
3016 ELSE
3017 write_to_out (
3018 l_hier_number
3019 || ' out of '
3020 || g_bottom_records
3021 || ' total records will make it into the WH based on'
3022 );
3023 write_to_out ('the LSTG tables');
3024 END IF;
3025
3026 IF g_debug
3027 THEN
3028 write_to_log_n (get_time);
3029 END IF;
3030
3031 write_to_out (' ');
3032 RETURN TRUE;
3033 EXCEPTION
3034 WHEN OTHERS
3035 THEN
3036 BEGIN
3037 CLOSE cv;
3038 EXCEPTION
3039 WHEN OTHERS
3040 THEN
3041 NULL;
3042 END;
3043
3044 g_status_message := SQLERRM;
3045 write_to_out_log_n (SQLERRM);
3046 RETURN FALSE;
3047 END execute_hier_count;
3048
3052 BEGIN
3049 FUNCTION get_num_recs_lstg (p_lstg IN VARCHAR2)
3050 RETURN NUMBER
3051 IS
3053 FOR i IN 1 .. g_number_lstg_tables
3054 LOOP
3055 IF g_lstg_tables (i) = p_lstg
3056 THEN
3057 RETURN g_lstg_total_records (i);
3058 END IF;
3059 END LOOP;
3060
3061 RETURN 0;
3062 EXCEPTION
3063 WHEN OTHERS
3064 THEN
3065 write_to_out_log_n (SQLERRM);
3066 g_status_message := SQLERRM;
3067 RETURN 0;
3068 END get_num_recs_lstg;
3069
3070 PROCEDURE init_all (p_object_name IN VARCHAR2)
3071 IS
3072 l_status BOOLEAN;
3073 l_option_value VARCHAR2(500);
3074 BEGIN
3075 g_number_names := 0;
3076 g_log_name := 'CHECK_DATA_INT';
3077 g_debug := FALSE; --make this false
3078 g_duplicate_check := TRUE;
3079 l_status := TRUE;
3080 --edw_owb_collection_util.setup_conc_program_log (g_log_name);
3081 edw_owb_collection_util.init_all(g_log_name,null,'bis.edw.check_data_validity');
3082 IF edw_option.get_warehouse_option (
3083 p_object_name,
3084 NULL,
3085 'TRACE',
3086 l_option_value
3087 )
3088 THEN
3089 IF l_option_value = 'Y'
3090 THEN
3091 write_to_log_n ('Trace turned ON');
3092 edw_owb_collection_util.alter_session ('TRACE');
3093 ELSE
3094 write_to_log_n ('Trace turned OFF');
3095 END IF;
3096 ELSE
3097 l_status := FALSE;
3098 END IF;
3099
3100 IF edw_option.get_warehouse_option (
3101 p_object_name,
3102 NULL,
3103 'DEBUG',
3104 l_option_value
3105 )
3106 THEN
3107 IF l_option_value = 'Y'
3108 THEN
3109 g_debug := TRUE;
3110 ELSE
3111 g_debug := FALSE;
3112 END IF;
3113
3114 write_to_log_n ( 'EDW_DEBUG the value is '
3115 || l_option_value);
3116 ELSE
3117 l_status := FALSE;
3118 END IF;
3119 edw_owb_collection_util.set_debug(g_debug);
3120 IF edw_option.get_warehouse_option (
3121 p_object_name,
3122 NULL,
3123 'OPTABLESPACE',
3124 l_option_value
3125 )
3126 THEN
3127 g_op_table_space :=
3128 NVL (
3129 l_option_value,
3130 edw_owb_collection_util.get_table_space (g_bis_owner)
3131 );
3132 write_to_log_n (
3133 'EDW_Operation tablespace is '
3134 || g_op_table_space
3135 );
3136 ELSE
3137 l_status := FALSE;
3138 END IF;
3139
3140 IF edw_option.get_warehouse_option (
3141 p_object_name,
3142 NULL,
3143 'DUPLICATE',
3144 l_option_value
3145 )
3146 THEN
3147 IF l_option_value = 'Y'
3148 THEN
3149 g_duplicate_check := FALSE;
3150 ELSE
3151 g_duplicate_check := TRUE;
3152 END IF;
3153
3154 write_to_log_n (
3155 'EDW_DUPLICATE_COLLECT set to true, the value is '
3156 || l_option_value
3157 );
3158 ELSE
3159 l_status := FALSE;
3160 END IF;
3161
3162 IF edw_option.get_warehouse_option (
3163 p_object_name,
3164 NULL,
3165 'PARALLELISM',
3166 l_option_value
3167 )
3168 THEN
3169 g_parallel := l_option_value;
3170
3171 --for Bug #2886705
3172 IF g_parallel <= 0 THEN
3173 g_parallel := NULL;
3174 END IF;
3175
3176 IF g_parallel IS NOT NULL
3177 THEN
3178 edw_owb_collection_util.alter_session ('PARALLEL');
3179 edw_owb_collection_util.set_parallel (g_parallel);
3180 COMMIT;
3181 END IF;
3182 ELSE
3183 l_status := FALSE;
3184 END IF;
3185
3186 IF edw_option.get_warehouse_option (
3187 p_object_name,
3188 NULL,
3189 'SORTAREA',
3190 l_option_value
3191 )
3192 THEN
3193 write_to_log_n ( 'l_sort_area_size='
3194 || l_option_value);
3195
3196 IF l_option_value IS NOT NULL
3197 THEN
3198 EXECUTE IMMEDIATE 'alter session set hash_area_size='
3199 || l_option_value;
3200 END IF;
3201 ELSE
3202 l_status := FALSE;
3203 END IF;
3204
3205 IF edw_option.get_warehouse_option (
3206 p_object_name,
3207 NULL,
3208 'HASHAREA',
3209 l_option_value
3210 )
3211 THEN
3212 write_to_log_n ( 'l_hash_area_size='
3213 || l_option_value);
3214
3218 || l_option_value;
3215 IF l_option_value IS NOT NULL
3216 THEN
3217 EXECUTE IMMEDIATE 'alter session set sort_area_size='
3219 END IF;
3220 ELSE
3221 l_status := FALSE;
3222 END IF;
3223
3224 IF l_status = FALSE
3225 THEN
3226 write_to_log_n (
3227 'Error. Reading of Object Settings Failed. Object: '
3228 || p_object_name
3229 );
3230 RAISE g_read_object_settings_failure;
3231 END IF;
3232
3233 edw_owb_collection_util.set_debug (g_debug);
3234
3235 /********for bug 2966892 *****/
3236 --g_number_sample := 10;
3237 --g_check_against_ltc := FALSE;
3238 --g_check_hier := FALSE;
3239 /****************************/
3240
3241 g_sample_on := g_detailed_check;
3242
3243 IF g_sample_on
3244 THEN
3245 write_to_log_n ('Sample ON');
3246 ELSE
3247 write_to_log_n ('Sample OFF');
3248 END IF;
3249
3250 g_exec_flag := TRUE;
3251 g_bis_owner := edw_owb_collection_util.get_db_user ('BIS');
3252 g_number_fk_to_check := 0;
3253 g_results_table := 'EDW_CDI_RESULTS';
3254
3255 IF edw_owb_collection_util.check_table (g_results_table) = TRUE
3256 THEN
3257 g_results_table_flag := TRUE;
3258 ELSE
3259 g_results_table_flag := FALSE;
3260 END IF;
3261
3262 g_request_id := fnd_global.conc_request_id;
3263 g_number_max_sample := fnd_profile.VALUE ('EDW_MAX_SAMPLE_SIZE');
3264 write_to_log_n ( 'Max sample size(tables)='
3265 || g_number_max_sample);
3266 g_process_dang_keys := TRUE;
3267 END init_all;
3268
3269 PROCEDURE close_all
3270 IS
3271 BEGIN
3272 NULL;
3273 END close_all;
3274
3275 PROCEDURE write_to_log (p_message IN VARCHAR2)
3276 IS
3277 BEGIN
3278 edw_owb_collection_util.write_to_log_file (p_message);
3279 END write_to_log;
3280
3281 PROCEDURE write_to_log_n (p_message IN VARCHAR2)
3282 IS
3283 BEGIN
3284 write_to_log (' ');
3285 write_to_log (p_message);
3286 END write_to_log_n;
3287
3288 PROCEDURE write_to_out (p_message IN VARCHAR2)
3289 IS
3290 BEGIN
3291 edw_owb_collection_util.write_to_out_file (p_message);
3292 END write_to_out;
3293
3294 PROCEDURE write_to_out_n (p_message IN VARCHAR2)
3295 IS
3296 BEGIN
3297 write_to_out (' ');
3298 write_to_out (p_message);
3299 END write_to_out_n;
3300
3301 PROCEDURE write_to_out_log (p_message IN VARCHAR2)
3302 IS
3303 BEGIN
3304 write_to_out (p_message);
3305 write_to_log (p_message);
3306 END write_to_out_log;
3307
3308 PROCEDURE write_to_out_log_n (p_message IN VARCHAR2)
3309 IS
3310 BEGIN
3311 write_to_out_n (p_message);
3312 write_to_log_n (p_message);
3313 END write_to_out_log_n;
3314
3315 FUNCTION get_time
3316 RETURN VARCHAR2
3317 IS
3318 BEGIN
3319 RETURN ' '
3320 || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS');
3321 END get_time;
3322
3323 FUNCTION parse_names (
3324 p_dim_string1 IN VARCHAR2,
3325 p_dim_string2 IN VARCHAR2,
3326 p_dim_string3 IN VARCHAR2,
3327 p_dim_string4 IN VARCHAR2,
3328 p_dim_string5 IN VARCHAR2
3329 )
3330 RETURN BOOLEAN
3331 IS
3332 l_start NUMBER;
3333 l_end NUMBER;
3334 l_len NUMBER;
3335 l_dim_string VARCHAR2 (200);
3336 BEGIN
3337 FOR i IN 1 .. 5
3338 LOOP
3339 IF i = 1
3340 THEN
3341 l_dim_string := p_dim_string1;
3342 ELSIF i = 2
3343 THEN
3344 l_dim_string := p_dim_string2;
3345 ELSIF i = 3
3346 THEN
3347 l_dim_string := p_dim_string3;
3348 ELSIF i = 4
3349 THEN
3350 l_dim_string := p_dim_string4;
3351 ELSE
3352 l_dim_string := p_dim_string5;
3353 END IF;
3354
3355 IF l_dim_string IS NULL
3356 THEN
3357 RETURN TRUE;
3358 END IF;
3359
3360 l_start := 1;
3361 l_end := 1;
3362 l_len := LENGTH (l_dim_string);
3363 g_number_names := 0;
3364
3365 LOOP
3366 l_end := INSTR (l_dim_string, ':', l_start);
3367
3368 IF l_end = -1
3369 THEN
3370 EXIT;
3371 END IF;
3372
3373 g_number_names := g_number_names
3374 + 1;
3375 g_names (g_number_names) :=
3376 SUBSTR (l_dim_string, l_start, l_end
3377 - l_start);
3378 l_start := l_end
3379 + 1;
3380
3381 IF l_start >= l_len
3382 THEN
3383 EXIT;
3384 END IF;
3385 END LOOP;
3389
3386 END LOOP;
3387
3388 write_to_log_n ('Objects to check ');
3390 FOR i IN 1 .. g_number_names
3391 LOOP
3392 write_to_log (g_names (i));
3393 END LOOP;
3394
3395 RETURN TRUE;
3396 EXCEPTION
3397 WHEN OTHERS
3398 THEN
3399 write_to_out_log_n (SQLERRM);
3400 g_status_message := SQLERRM;
3401 RETURN FALSE;
3402 END parse_names;
3403
3404 FUNCTION get_long_names
3405 RETURN BOOLEAN
3406 IS
3407 l_stmt VARCHAR2 (1000);
3408
3409 TYPE curtyp IS REF CURSOR;
3410
3411 cv curtyp;
3412 BEGIN
3413 write_to_log_n ('Finding the long names');
3414
3415 FOR i IN 1 .. g_number_names
3416 LOOP
3417 l_stmt :=
3418 'select relation_id, relation_long_name from edw_relations_md_v where relation_name =:s';
3419 OPEN cv FOR l_stmt USING g_names (i);
3420 FETCH cv INTO g_ids (i), g_names_long (i);
3421 write_to_log (
3422 g_names_long (i)
3423 || '('
3424 || g_names (i)
3425 || ') '
3426 || g_ids (i)
3427 );
3428 CLOSE cv;
3429 END LOOP;
3430
3431 RETURN TRUE;
3432 EXCEPTION
3433 WHEN OTHERS
3434 THEN
3435 write_to_log_n (
3436 'Error in function get_long_names '
3437 || SQLERRM
3438 || ', Time '
3439 || get_time
3440 );
3441 g_status_message := SQLERRM;
3442 RETURN FALSE;
3443 END get_long_names;
3444
3445 FUNCTION get_long_for_short_name (p_name IN VARCHAR2)
3446 RETURN VARCHAR2
3447 IS
3448 BEGIN
3449 FOR i IN 1 .. g_number_names
3450 LOOP
3451 IF g_names (i) = p_name
3452 THEN
3453 RETURN g_names_long (i);
3454 EXIT;
3455 END IF;
3456 END LOOP;
3457
3458 RETURN NULL;
3459 EXCEPTION
3460 WHEN OTHERS
3461 THEN
3462 write_to_log_n (
3463 'Error in function get_long_for_short_name for '
3464 || p_name
3465 || ' '
3466 || SQLERRM
3467 );
3468 g_status_message := SQLERRM;
3469 RETURN NULL;
3470 END get_long_for_short_name;
3471
3472 FUNCTION get_lstg_long_name (p_table IN VARCHAR2)
3473 RETURN VARCHAR2
3474 IS
3475 BEGIN
3476 FOR i IN 1 .. g_number_lstg_tables
3477 LOOP
3478 IF g_lstg_tables (i) = p_table
3479 THEN
3480 RETURN g_lstg_table_long_name (i);
3481 END IF;
3482 END LOOP;
3483
3484 RETURN NULL;
3485 EXCEPTION
3486 WHEN OTHERS
3487 THEN
3488 write_to_log_n (
3489 'Error in function get_lstg_long_name for '
3490 || p_table
3491 || ' '
3492 || SQLERRM
3493 );
3494 g_status_message := SQLERRM;
3495 RETURN NULL;
3496 END get_lstg_long_name;
3497
3498 FUNCTION get_lstg_pk (p_table IN VARCHAR2)
3499 RETURN VARCHAR2
3500 IS
3501 BEGIN
3502 FOR i IN 1 .. g_number_lstg_tables
3503 LOOP
3504 IF g_lstg_tables (i) = p_table
3505 THEN
3506 RETURN g_lstg_pk (i);
3507 END IF;
3508 END LOOP;
3509
3510 RETURN NULL;
3511 EXCEPTION
3512 WHEN OTHERS
3513 THEN
3514 write_to_log_n (
3515 'Error in function get_lstg_pk for '
3516 || p_table
3517 || ' '
3518 || SQLERRM
3519 );
3520 g_status_message := SQLERRM;
3521 RETURN NULL;
3522 END get_lstg_pk;
3523
3524 FUNCTION get_fk_long (
3525 p_fk IN VARCHAR2,
3526 fk_table_long IN edw_owb_collection_util.varchartabletype,
3527 fk_table IN edw_owb_collection_util.varchartabletype,
3528 fk_table_number IN NUMBER
3529 )
3530 RETURN VARCHAR2
3531 IS
3532 BEGIN
3533 FOR i IN 1 .. fk_table_number
3534 LOOP
3535 IF fk_table (i) = p_fk
3536 THEN
3537 RETURN fk_table_long (i);
3538 END IF;
3539 END LOOP;
3540
3541 RETURN NULL;
3542 EXCEPTION
3543 WHEN OTHERS
3544 THEN
3545 write_to_log_n (
3546 'Error in function get_fk_long for '
3547 || p_fk
3548 || ' '
3549 || SQLERRM
3550 );
3551 g_status_message := SQLERRM;
3552 RETURN NULL;
3553 END get_fk_long;
3554
3555 FUNCTION get_parent_ltc_long (
3556 p_ltc IN VARCHAR2,
3557 p_lstg_ltc_parent IN edw_owb_collection_util.varchartabletype,
3558 p_lstg_ltc_parent_long IN edw_owb_collection_util.varchartabletype,
3559 p_number_lstg IN NUMBER
3560 )
3561 RETURN VARCHAR2
3562 IS
3563 BEGIN
3564 FOR i IN 1 .. p_number_lstg
3568 RETURN p_lstg_ltc_parent_long (i);
3565 LOOP
3566 IF p_ltc = p_lstg_ltc_parent (i)
3567 THEN
3569 END IF;
3570 END LOOP;
3571
3572 RETURN NULL;
3573 EXCEPTION
3574 WHEN OTHERS
3575 THEN
3576 write_to_log_n (
3577 'Error in function get_parent_ltc_long for '
3578 || p_ltc
3579 || ' '
3580 || SQLERRM
3581 );
3582 g_status_message := SQLERRM;
3583 RETURN NULL;
3584 END get_parent_ltc_long;
3585
3586 FUNCTION get_ltc_pk_long (
3587 p_parent_ltc_fk_table_pk IN VARCHAR2,
3588 p_lstg_ltc_parent_pk IN edw_owb_collection_util.varchartabletype,
3589 p_lstg_ltc_parent_pk_long IN edw_owb_collection_util.varchartabletype,
3590 p_number_lstg IN NUMBER
3591 )
3592 RETURN VARCHAR2
3593 IS
3594 BEGIN
3595 FOR i IN 1 .. p_number_lstg
3596 LOOP
3597 IF p_parent_ltc_fk_table_pk = p_lstg_ltc_parent_pk (i)
3598 THEN
3599 RETURN p_lstg_ltc_parent_pk_long (i);
3600 END IF;
3601 END LOOP;
3602
3603 RETURN NULL;
3604 EXCEPTION
3605 WHEN OTHERS
3606 THEN
3607 write_to_log_n (
3608 'Error in function get_ltc_pk_long for '
3609 || p_parent_ltc_fk_table_pk
3610 || ' '
3611 || SQLERRM
3612 );
3613 g_status_message := SQLERRM;
3614 RETURN NULL;
3615 END get_ltc_pk_long;
3616
3617
3618 /*
3619 -------------------------------------------------------
3620 Procedures to check facts
3621 -------------------------------------------------------
3622 */
3623 PROCEDURE check_facts_data (
3624 errbuf OUT NOCOPY VARCHAR2,
3625 retcode OUT NOCOPY VARCHAR2,
3626 p_fact_string1 IN VARCHAR2,
3627 p_check_tot_recs IN VARCHAR2,
3628 p_detailed_check IN VARCHAR2,
3629 p_sample_size IN NUMBER,
3630 p_fk_to_check IN VARCHAR2
3631 )
3632 IS
3633 BEGIN
3634 retcode := '0';
3635
3636 IF p_detailed_check = 'Y'
3637 THEN
3638 g_detailed_check := TRUE;
3639 ELSE
3640 g_detailed_check := FALSE;
3641 END IF;
3642
3643 -- init_all;
3644 g_number_names := 1;
3645 g_names (g_number_names) := p_fact_string1;
3646 g_number_fk_to_check := 0;
3647
3648 IF get_fk_to_check (p_fk_to_check) = FALSE
3649 THEN
3650 fnd_message.set_name ('BIS', 'EDW_CDI_ERROR_METADATA_READ');
3651 errbuf := fnd_message.get;
3652 write_to_out (errbuf);
3653 retcode := '2';
3654 RETURN;
3655 END IF;
3656
3657 IF get_long_names = FALSE
3658 THEN
3659 fnd_message.set_name ('BIS', 'EDW_CDI_NO_FACT_LONG_NAME');
3660 write_to_log_n (
3661 'Could not get long name for the fact. Stopping Data Check, Time '
3662 || get_time
3663 );
3664 errbuf := fnd_message.get;
3665 write_to_out_n (errbuf);
3666 retcode := '2';
3667 RETURN;
3668 END IF;
3669
3670 g_number_sample := p_sample_size;
3671 write_to_log_n ( 'Sample size='
3672 || g_number_sample);
3673 g_check_hier := FALSE;
3674 /*if p_check_tot_recs = 'Y' then
3675 g_check_hier :=true;
3676 write_to_log_n('check total records making into wh on');
3677 else
3678 write_to_log_n('check total records making into wh off');
3679 end if;*/
3680
3681 errbuf := NULL;
3682
3683 FOR i IN 1 .. g_number_names
3684 LOOP
3685 g_object_name := g_names (i);
3686 g_object_id := g_ids (i);
3687 g_object_type := 'FACT';
3688
3689 IF check_fact (g_names (i), g_names_long (i)) = FALSE
3690 THEN
3691 errbuf := g_status_message;
3692 retcode := '2';
3693 END IF;
3694 END LOOP;
3695
3696 clean_up;
3697 EXCEPTION
3698 WHEN OTHERS
3699 THEN
3700 write_to_out_log_n (SQLERRM);
3701 g_status_message := SQLERRM;
3702 errbuf := SQLERRM;
3703 retcode := '2';
3704 END check_facts_data;
3705
3706 FUNCTION check_fact (p_fact_name IN VARCHAR2, p_fact_name_long IN VARCHAR2)
3707 RETURN BOOLEAN
3708 IS
3709 BEGIN
3710 write_to_out_log_n (
3711 '--------------------------------------------------'
3712 );
3713 fnd_message.set_name ('BIS', 'EDW_CDI_CHECKING_FACT');
3714 fnd_message.set_token ('FACT', p_fact_name_long);
3715 write_to_out (fnd_message.get);
3716 write_to_log (
3717 ' Checking fact '
3718 || p_fact_name_long
3719 || '('
3720 || p_fact_name
3721 || ')'
3722 );
3723 write_to_out_log ('--------------------------------------------------');
3724 write_to_out_log (' ');
3728 THEN
3725 init_all (p_fact_name);
3726
3727 IF g_results_table_flag
3729 IF delete_cdi_results_table (p_fact_name) = FALSE
3730 THEN
3731 RETURN FALSE;
3732 END IF;
3733 END IF;
3734
3735 IF get_fstg_dim_keys (p_fact_name) = FALSE
3736 THEN
3737 write_to_log_n (
3738 'Error in getting fstg, dim and key info. cannot check this fact '
3739 || p_fact_name
3740 );
3741 fnd_message.set_name ('BIS', 'EDW_CDI_END_DATA_CHECK');
3742 write_to_out (fnd_message.get);
3743 --write_to_out('Error in reading metadata for fact. Stopping Data Check');
3744 RETURN FALSE;
3745 END IF;
3746
3747 IF generate_fk_table (p_fact_name) = FALSE
3748 THEN
3749 write_to_log_n ( 'Error in generate_fk_table '
3750 || p_fact_name);
3751 fnd_message.set_name ('BIS', 'EDW_CDI_ERROR_GENERATE_TABLE');
3752 write_to_out (fnd_message.get);
3753 --write_to_out('Error in generating fk table. Stopping Data Check');
3754 RETURN FALSE;
3755 END IF;
3756
3757 /*if make_sql_statements_fact(p_fact_name)=false then
3758 write_to_log_n('Error in making sql statements. cannot check this fact '||p_fact_name);
3759 write_to_out('Error in reading metadata for fact. Stopping Data Check');
3760 return false;
3761 end if;
3762 write_to_log_n('make_sql_statements_fact done '||get_time);*/
3763
3764 IF g_exec_flag
3765 THEN
3766 IF execute_fact_check (p_fact_name) = FALSE
3767 THEN
3768 write_to_log_n (
3769 'Error in executing fact data check. cannot check this fact '
3770 || p_fact_name
3771 );
3772 fnd_message.set_name ('BIS', 'EDW_CDI_ERROR_FACT_CHECK');
3773 write_to_out (fnd_message.get);
3774 --write_to_out('Error in executing Data Check for fact. Stopping Data Check');
3775 RETURN FALSE;
3776 END IF;
3777
3778 write_to_log_n (
3779 'execute_fact_check done for '
3780 || p_fact_name
3781 || ' '
3782 || get_time
3783 );
3784
3785 /*
3786 if drop_fstg_fk_tables(p_fact_name)=false then
3787 null;
3788 end if;*/
3789 IF drop_fstg_pk_table (p_fact_name) = FALSE
3790 THEN
3791 NULL;
3792 END IF;
3793 ELSE
3794 write_to_log_n ('Execute option turned off. No check done');
3795 END IF;
3796
3797 IF g_fstg_total_records > 0
3798 THEN
3799 IF g_process_dang_keys
3800 THEN
3801 IF process_dang_keys (p_fact_name) = FALSE
3802 THEN
3803 RETURN FALSE;
3804 END IF;
3805 END IF;
3806 END IF;
3807
3808 --drop the fk tabke
3809 IF drop_fk_table (p_fact_name) = FALSE
3810 THEN
3811 NULL;
3812 END IF;
3813
3814 write_to_out_log_n (
3815 '--------------------------------------------------'
3816 );
3817 fnd_message.set_name ('BIS', 'EDW_CDI_END_DATA_CHECK');
3818 write_to_out (fnd_message.get);
3819 write_to_log ( ' End Check for fact '
3820 || p_fact_name_long);
3821 write_to_out_log ('--------------------------------------------------');
3822 write_to_out_log (' ');
3823 RETURN TRUE;
3824 EXCEPTION
3825 WHEN OTHERS
3826 THEN
3827 write_to_out_log_n (SQLERRM);
3828 g_status_message := SQLERRM;
3829 RETURN FALSE;
3830 END check_fact;
3831
3832 FUNCTION get_fstg_dim_keys (p_fact_name IN VARCHAR2)
3833 RETURN BOOLEAN
3834 IS
3835 l_stmt VARCHAR2 (30000);
3836
3837 TYPE curtyp IS REF CURSOR;
3838
3839 cv curtyp;
3840 l_fstg_fk edw_owb_collection_util.varchartabletype;
3841 l_fstg_fk_id edw_owb_collection_util.numbertabletype;
3842 l_fstg_fk_long edw_owb_collection_util.varchartabletype;
3843 l_fact_dims_id edw_owb_collection_util.numbertabletype;
3844 l_fact_dims edw_owb_collection_util.varchartabletype;
3845 l_fact_dims_long edw_owb_collection_util.varchartabletype;
3846 l_fact_dims_pk edw_owb_collection_util.varchartabletype;
3847 l_fact_dims_pk_long edw_owb_collection_util.varchartabletype;
3848 l_number_fstg_fk NUMBER;
3849 l_skipped_cols edw_owb_collection_util.varchartabletype;
3850 l_number_skipped_cols NUMBER;
3851 l_map_src_col edw_owb_collection_util.varchartabletype;
3852 l_map_tgt_col edw_owb_collection_util.varchartabletype;
3853 l_number_map_cols NUMBER;
3854 l_index NUMBER;
3855 BEGIN
3856 l_stmt :=
3857 'SELECT fstg.relation_id, fstg.relation_name, fstg.relation_long_name, '
3858 || 'pk.column_name, pk.column_id, pk_col.business_name '
3859 || 'FROM edw_relations_md_v fstg, edw_relationmapping_md_v map, edw_facts_md_v fact, '
3860 || 'edw_unique_key_columns_md_v pk, '
3864 || 'AND map.targetdataentity = fact.fact_id '
3861 || 'edw_unique_keys_md_v uk, '
3862 || 'edw_all_columns_md_v pk_col '
3863 || 'WHERE fact.fact_name = :s '
3865 || 'AND fstg.relation_id = map.sourcedataentity '
3866 || 'AND uk.entity_id = fstg.relation_id '
3867 || 'AND pk.key_id = uk.key_id '
3868 || 'AND pk.column_id = pk_col.column_id '
3869 || 'AND uk.entity_id = pk_col.entity_id ';
3870
3871 IF g_debug
3872 THEN
3873 write_to_log_n ( 'going to execute '
3874 || l_stmt);
3875 END IF;
3876
3877 OPEN cv FOR l_stmt USING p_fact_name;
3878 FETCH cv INTO g_fstg_id,
3879 g_fstg_name,
3880 g_fstg_name_long,
3881 g_fstg_pk,
3882 g_fstg_pk_id,
3883 g_fstg_pk_long;
3884 CLOSE cv;
3885 write_to_log_n (
3886 'FSTG table for fact '
3887 || g_fstg_name_long
3888 || '('
3889 || g_fstg_name
3890 || ')'
3891 || ' with pk '
3892 || g_fstg_pk_long
3893 || '('
3894 || g_fstg_pk
3895 || ')'
3896 );
3897
3898 IF g_fstg_name IS NULL
3899 THEN
3900 write_to_log_n ('No staging table for fact found.');
3901 RETURN FALSE;
3902 END IF;
3903
3904 g_fact_pk_table := g_bis_owner
3905 || '.'
3906 || p_fact_name
3907 || 'CP';
3908 g_fact_dup_pk_table := g_bis_owner
3909 || '.'
3910 || p_fact_name
3911 || 'PD';
3912 l_stmt :=
3913 'SELECT fk_col.fk_column_name, fk_col.fk_column_id, fcol.business_name, '
3914 || 'dim.dim_id, dim.dim_name, dim.dim_long_name, uk_col.column_name, '
3915 || 'ucol.business_name '
3916 || 'FROM edw_relations_md_v fact, '
3917 || 'edw_foreign_key_columns_md_v fk_col, '
3918 || 'edw_unique_key_columns_md_v uk_col, '
3919 || 'edw_dimensions_md_v dim, '
3920 || 'edw_unique_keys_md_v uk, '
3921 || 'edw_all_columns_md_v fcol, '
3922 || 'edw_all_columns_md_v ucol '
3923 || 'WHERE fact.relation_name = :a '
3924 || 'and fact.relation_id = fk_col.entity_id '
3925 || 'and fk_col.pk_id = uk_col.key_id '
3926 || 'and uk_col.key_id = uk.key_id '
3927 || 'and uk.entity_id = dim.dim_id '
3928 || 'and fcol.column_id = fk_col.fk_column_id '
3929 || 'and fcol.entity_id = fact.relation_id '
3930 || 'and ucol.column_id = uk_col.column_id '
3931 || 'and ucol.entity_id = dim.dim_id ';
3932 l_number_fstg_fk := 1;
3933 OPEN cv FOR l_stmt USING g_fstg_name;
3934
3935 LOOP
3936 FETCH cv INTO l_fstg_fk (l_number_fstg_fk),
3937 l_fstg_fk_id (l_number_fstg_fk),
3938 l_fstg_fk_long (l_number_fstg_fk),
3939 l_fact_dims_id (l_number_fstg_fk),
3940 l_fact_dims (l_number_fstg_fk),
3941 l_fact_dims_long (l_number_fstg_fk),
3942 l_fact_dims_pk (l_number_fstg_fk),
3943 l_fact_dims_pk_long (l_number_fstg_fk);
3944 EXIT WHEN cv%NOTFOUND;
3945 l_number_fstg_fk := l_number_fstg_fk
3946 + 1;
3947 END LOOP;
3948
3949 l_number_fstg_fk := l_number_fstg_fk
3950 - 1;
3951
3952 IF l_number_fstg_fk = 0
3953 THEN
3954 write_to_log_n ( 'No FKs found for fact '
3955 || p_fact_name);
3956 RETURN FALSE;
3957 END IF;
3958
3959 --get the skipped fks
3960 IF edw_owb_collection_util.get_item_set_cols (
3961 l_skipped_cols,
3962 l_number_skipped_cols,
3963 p_fact_name,
3964 'SKIP_LOAD_SET'
3965 ) = FALSE
3966 THEN
3967 RETURN FALSE;
3968 END IF;
3969
3970 IF g_debug
3971 THEN
3972 write_to_log_n ('The skipped cols of the fact');
3973
3974 FOR i IN 1 .. l_number_skipped_cols
3975 LOOP
3976 write_to_log (l_skipped_cols (i));
3977 END LOOP;
3978 END IF;
3979 --Fix for bug P1 2739489.
3980 --get the mapping details
3981 declare
3982 l_mapping_id number;
3983 l_fstgTableUsageId number;
3984 l_fstgTableId number;
3985 l_fstgTableName varchar2(200);
3986 l_factTableUsageId number;
3987 l_factTableId number;
3988 l_factTableName varchar2(200);
3989 l_fstgPKName varchar2(200);
3990 l_factPKName varchar2(200);
3991 l_dimTableName EDW_OWB_COLLECTION_UTIL.varcharTableType;
3992 l_dim_row_count EDW_OWB_COLLECTION_UTIL.numberTableType;
3993 l_dimTableId EDW_OWB_COLLECTION_UTIL.numberTableType;
3994 l_dimUserPKName EDW_OWB_COLLECTION_UTIL.varcharTableType;
3995 begin
3996 --fix for bug 2847694
3997 l_stmt:='select mapping_id '||
4001 if g_debug then
3998 'from edw_pvt_map_properties_md_v,edw_relations_md_v '||
3999 'where edw_relations_md_v.relation_name=:1 '||
4000 'and edw_relations_md_v.relation_id=edw_pvt_map_properties_md_v.primary_target ';
4002 write_to_log_n(l_stmt||' '||p_fact_name);
4003 end if;
4004 open cv for l_stmt using p_fact_name;
4005 fetch cv into l_mapping_id;
4006 if EDW_OWB_COLLECTION_UTIL.get_stg_map_pk_params(
4007 l_mapping_id,
4008 l_fstgTableUsageId,
4009 l_fstgTableId,
4010 l_fstgTableName,
4011 l_factTableUsageId,
4012 l_factTableId,
4013 l_factTableName,
4014 l_fstgPKName,
4015 l_factPKName
4016 )=false then
4017 return false;
4018 end if;
4019 --4063140. we now pass null for job id.
4020 if EDW_OWB_COLLECTION_UTIL.get_stg_map_fk_details(
4021 l_fstgTableUsageId,
4022 l_fstgTableId,
4023 l_mapping_id,
4024 null,
4025 g_op_table_space,
4026 g_bis_owner,
4027 l_dimTableName,
4028 l_dim_row_count,
4029 l_dimTableId,
4030 l_dimUserPKName,
4031 l_map_src_col,
4032 l_map_tgt_col,
4033 l_number_map_cols)=false then
4034 return false;
4035 end if;
4036 end;
4037
4038 IF g_debug
4039 THEN
4040 write_to_log_n ('The Mapping relations between the keys');
4041
4042 FOR i IN 1 .. l_number_map_cols
4043 LOOP
4044 write_to_log ( l_map_src_col (i)
4045 || ' -> '
4046 || l_map_tgt_col (i));
4047 END LOOP;
4048 END IF;
4049
4050 g_number_fstg_fk := 0;
4051
4052 FOR i IN 1 .. l_number_map_cols
4053 LOOP
4054 IF edw_owb_collection_util.value_in_table (
4055 l_skipped_cols,
4056 l_number_skipped_cols,
4057 l_map_tgt_col (i)
4058 ) = FALSE
4059 THEN
4060 l_index :=
4061 edw_owb_collection_util.index_in_table (
4062 l_fstg_fk,
4063 l_number_fstg_fk,
4064 l_map_src_col (i)
4065 );
4066 g_number_fstg_fk := g_number_fstg_fk
4067 + 1;
4068 g_fstg_fk (g_number_fstg_fk) := l_fstg_fk (l_index);
4069 g_fstg_fk_id (g_number_fstg_fk) := l_fstg_fk_id (l_index);
4070 g_fstg_fk_long (g_number_fstg_fk) := l_fstg_fk_long (l_index);
4071 g_fact_dims_id (g_number_fstg_fk) := l_fact_dims_id (l_index);
4072 g_fact_dims (g_number_fstg_fk) := l_fact_dims (l_index);
4073 g_fact_dims_long (g_number_fstg_fk) := l_fact_dims_long (l_index);
4074 g_fact_dims_pk (g_number_fstg_fk) := l_fact_dims_pk (l_index);
4075 g_fact_dims_pk_long (g_number_fstg_fk) :=
4076 l_fact_dims_pk_long (l_index);
4077 END IF;
4078 END LOOP;
4079
4080 --get the instance column
4081 g_fstg_instance_col :=
4082 edw_owb_collection_util.get_instance_col (g_fstg_name);
4083
4084 IF edw_owb_collection_util.value_in_table (
4085 g_fstg_fk,
4086 g_number_fstg_fk,
4087 g_fstg_instance_col
4088 ) = FALSE
4089 THEN
4090 l_index := NULL;
4091 l_index :=
4092 edw_owb_collection_util.index_in_table (
4093 l_fstg_fk,
4094 l_number_fstg_fk,
4095 g_fstg_instance_col
4096 );
4097
4098 IF l_index IS NOT NULL
4099 THEN
4100 g_number_fstg_fk := g_number_fstg_fk
4101 + 1;
4102 g_fstg_fk (g_number_fstg_fk) := l_fstg_fk (l_index);
4103 g_fstg_fk_id (g_number_fstg_fk) := l_fstg_fk_id (l_index);
4104 g_fstg_fk_long (g_number_fstg_fk) := l_fstg_fk_long (l_index);
4105 g_fact_dims_id (g_number_fstg_fk) := l_fact_dims_id (l_index);
4106 g_fact_dims (g_number_fstg_fk) := l_fact_dims (l_index);
4107 g_fact_dims_long (g_number_fstg_fk) := l_fact_dims_long (l_index);
4108 g_fact_dims_pk (g_number_fstg_fk) := l_fact_dims_pk (l_index);
4109 g_fact_dims_pk_long (g_number_fstg_fk) :=
4110 l_fact_dims_pk_long (l_index);
4111 END IF;
4112 END IF;
4113
4114 FOR i IN 1 .. g_number_fstg_fk
4115 LOOP
4116 g_fk_check_flag (i) := TRUE; --default
4117 END LOOP;
4118
4119 IF g_number_fk_to_check > 0
4120 THEN
4121 FOR i IN 1 .. g_number_fstg_fk
4122 LOOP
4123 IF edw_owb_collection_util.value_in_table (
4124 g_fk_to_check,
4125 g_number_fk_to_check,
4126 g_fstg_fk (i)
4127 )
4128 THEN
4129 g_fk_check_flag (i) := TRUE;
4130 ELSE
4131 g_fk_check_flag (i) := FALSE;
4132 END IF;
4133 END LOOP;
4134 END IF;
4135
4136 IF g_debug
4140 FOR i IN 1 .. g_number_fstg_fk
4137 THEN
4138 write_to_log_n ('The fact FKs, parent dims and dim pks');
4139
4141 LOOP
4142 write_to_log (
4143 g_fstg_fk (i)
4144 || ' '
4145 || g_fact_dims (i)
4146 || ' '
4147 || g_fact_dims_pk (i)
4148 );
4149 END LOOP;
4150
4151 write_to_log_n ('The long names:-');
4152
4153 FOR i IN 1 .. g_number_fstg_fk
4154 LOOP
4155 write_to_log (
4156 g_fstg_fk_long (i)
4157 || ' '
4158 || g_fact_dims_long (i)
4159 || ' '
4160 || g_fact_dims_pk_long (i)
4161 );
4162 END LOOP;
4163
4164 IF g_number_fk_to_check > 0
4165 THEN
4166 write_to_log_n ('The FKs that will be checked');
4167
4168 FOR i IN 1 .. g_number_fstg_fk
4169 LOOP
4170 IF g_fk_check_flag (i)
4171 THEN
4172 write_to_log (g_fstg_fk (i));
4173 END IF;
4174 END LOOP;
4175 END IF;
4176
4177 write_to_log_n ( 'The instance column '
4178 || g_fstg_instance_col);
4179 END IF;
4180
4181 FOR i IN 1 .. g_number_fstg_fk
4182 LOOP
4183 g_fact_fk_table (i) := g_bis_owner
4184 || '.'
4185 || p_fact_name
4186 || 'CF'
4187 || i;
4188 g_fact_fk_ok_table (i) :=
4189 g_bis_owner
4190 || '.'
4191 || p_fact_name
4192 || 'CO'
4193 || i;
4194 g_fact_fk_dang_rowid_table (i) :=
4195 g_bis_owner
4196 || '.'
4197 || p_fact_name
4198 || 'CR'
4199 || i;
4200 END LOOP;
4201
4202 RETURN TRUE;
4203 EXCEPTION
4204 WHEN OTHERS
4205 THEN
4206 write_to_out_log_n (SQLERRM);
4207 g_status_message := SQLERRM;
4208 RETURN FALSE;
4209 END get_fstg_dim_keys;
4210
4211 FUNCTION make_sql_statements_fact (p_fact_name IN VARCHAR2)
4212 RETURN BOOLEAN
4213 IS
4214 BEGIN
4215 g_fact_dup_stmt_num := 'select nvl(sum(count(1)),0) from '
4216 || g_fk_table
4217 || ' '
4218 || ' having count('
4219 || g_fstg_pk
4220 || ') > 1 group by '
4221 || g_fstg_pk;
4222 g_fact_dup_stmt_str := 'select distinct '
4223 || g_fstg_pk
4224 || ' from '
4225 || g_fk_table
4226 || ' having count('
4227 || g_fstg_pk
4228 || ') > 1 group by '
4229 || g_fstg_pk;
4230 g_number_dang_stmt := 0;
4231
4232 FOR i IN 1 .. g_number_fstg_fk
4233 LOOP
4234 g_fact_dang_stmt_num (i) :=
4235 'select nvl(count(1),0) from '
4236 || g_fk_table
4237 || ' abc where
4238 not exists (select 1 from '
4239 || g_fact_dims (i)
4240 || ' where '
4241 || g_fact_dims (i)
4242 || '.'
4243 || g_fact_dims_pk (i)
4244 || ' = abc.'
4245 || g_fstg_fk (i)
4246 || ') ';
4247 g_fact_dang_stmt_str (i) :=
4248 'select distinct abc.'
4249 || g_fstg_fk (i)
4250 || ' from '
4251 || g_fk_table
4252 || ' abc where not
4253 exists (select 1 from '
4254 || g_fact_dims (i)
4255 || ' where '
4256 || g_fact_dims (i)
4257 || '.'
4258 || g_fact_dims_pk (i)
4259 || ' = abc.'
4260 || g_fstg_fk (i)
4261 || ') ';
4262 g_number_dang_stmt := g_number_dang_stmt
4263 + 1;
4264 END LOOP;
4265
4266 RETURN TRUE;
4267 EXCEPTION
4268 WHEN OTHERS
4269 THEN
4270 write_to_out_log_n (SQLERRM);
4271 g_status_message := SQLERRM;
4272 RETURN FALSE;
4273 END make_sql_statements_fact;
4274
4275 FUNCTION execute_fact_check (p_fact_name IN VARCHAR2)
4276 RETURN BOOLEAN
4277 IS
4278 BEGIN
4279 IF execute_fact_total_records (p_fact_name) = FALSE
4280 THEN
4281 RETURN FALSE;
4282 END IF;
4283
4284 write_to_log_n ('Executed execute_fact_total_records');
4285 fnd_message.set_name ('BIS', 'EDW_CDI_TABLE_AND_RECORDS');
4286 fnd_message.set_token ('TABLE', g_fstg_name);
4287 fnd_message.set_token ('RECORDS', g_fstg_total_records);
4291 --write_to_out('''READY'' ''DANGLING'' or ''DUPLICATE''');
4288 write_to_out (fnd_message.get);
4289
4290 --write_to_out('Fstg table '||g_fstg_name||' has '||g_fstg_total_records||' total records with collection status of');
4292 IF g_fstg_total_records = 0
4293 THEN
4294 fnd_message.set_name ('BIS', 'EDW_CDI_END_DATA_CHECK');
4295 write_to_out (fnd_message.get);
4296 --write_to_out('No data to check.');
4297 RETURN TRUE;
4298 END IF;
4299
4300 --get the duplicates;
4301 IF g_duplicate_check = TRUE
4302 THEN
4303 IF execute_fact_duplicate_check (p_fact_name) = FALSE
4304 THEN
4305 RETURN FALSE;
4306 END IF;
4307
4308 write_to_log_n ('Executed execute_fact_duplicate_check...');
4309 END IF;
4310
4311 IF execute_fact_dangling_check (p_fact_name) = FALSE
4312 THEN
4313 RETURN FALSE;
4314 END IF;
4315
4316 write_to_log_n ('Executed execute_fact_dangling_check');
4317
4318 IF g_check_hier
4319 THEN
4320 write_to_log_n (
4321 'Checking the total number of records making into the wh'
4322 );
4323
4324 IF execute_fstg_makeit_stmt (p_fact_name) = FALSE
4325 THEN
4326 RETURN FALSE;
4327 END IF;
4328
4329 write_to_log_n ('Executed execute_fstg_makeit_stmt');
4330 END IF;
4331
4332 RETURN TRUE;
4333 EXCEPTION
4334 WHEN OTHERS
4335 THEN
4336 write_to_out_log_n (SQLERRM);
4337 g_status_message := SQLERRM;
4338 RETURN FALSE;
4339 END execute_fact_check;
4340
4341 FUNCTION execute_fact_total_records (p_fact_name IN VARCHAR2)
4342 RETURN BOOLEAN
4343 IS
4344 l_stmt VARCHAR2 (2000);
4345 BEGIN
4346 --make the rowid table
4347 l_stmt := 'create table '
4348 || g_fact_pk_table
4349 || ' tablespace '
4350 || g_op_table_space;
4351
4352 IF g_parallel IS NOT NULL
4353 THEN
4354 l_stmt := l_stmt
4355 || ' parallel (degree '
4356 || g_parallel
4357 || ') ';
4358 END IF;
4359
4360 l_stmt := l_stmt
4361 || ' as select ';
4362 l_stmt := l_stmt
4363 || g_fstg_pk
4364 || ',row_id from '
4365 || g_fk_table;
4366
4367 IF edw_owb_collection_util.drop_table (g_fact_pk_table) = FALSE
4368 THEN
4369 NULL;
4370 END IF;
4371
4372 IF g_debug
4373 THEN
4374 write_to_log_n ( 'going to execute '
4375 || l_stmt
4376 || get_time);
4377 END IF;
4378
4379 EXECUTE IMMEDIATE l_stmt;
4380 g_fstg_total_records := SQL%ROWCOUNT;
4381
4382 IF g_debug
4383 THEN
4384 write_to_log_n (
4385 'Created '
4386 || g_fact_pk_table
4387 || ' with '
4388 || g_fstg_total_records
4389 || ' rows'
4390 || get_time
4391 );
4392 END IF;
4393
4394 edw_owb_collection_util.analyze_table_stats (
4395 SUBSTR (
4396 g_fact_pk_table,
4397 INSTR (g_fact_pk_table, '.')
4398 + 1,
4399 LENGTH (g_fact_pk_table)
4400 ),
4401 SUBSTR (g_fact_pk_table, 1, INSTR (g_fact_pk_table, '.')
4402 - 1)
4403 );
4404 RETURN TRUE;
4405 EXCEPTION
4406 WHEN OTHERS
4407 THEN
4408 write_to_out_log_n (SQLERRM);
4409 g_status_message := SQLERRM;
4410 RETURN FALSE;
4411 END execute_fact_total_records;
4412
4413 FUNCTION execute_fact_duplicate_check (p_fact_name IN VARCHAR2)
4414 RETURN BOOLEAN
4415 IS
4416 l_stmt VARCHAR2 (30000);
4417
4418 TYPE curtyp IS REF CURSOR;
4419
4420 cv curtyp;
4421 l_num_dup NUMBER := 0;
4422 l_dup_str edw_owb_collection_util.varchartabletype;
4423 l_dup_count edw_owb_collection_util.numbertabletype;
4424 l_number_dup_str NUMBER;
4425 BEGIN
4426 IF g_debug
4427 THEN
4428 write_to_log_n ( 'going to execute '
4429 || g_fact_dup_stmt_num);
4430 END IF;
4431
4432 fnd_message.set_name ('BIS', 'EDW_CDI_DUPLICATE_DATA_CHECK');
4433 write_to_out ( '----------'
4434 || fnd_message.get
4435 || '----------');
4436
4437 --write_to_out('----------- Duplicate Check -------------');
4438 write_to_out (' ');
4439 l_stmt := 'create table '
4440 || g_fact_dup_pk_table
4441 || ' tablespace '
4442 || g_op_table_space;
4443
4444 IF g_parallel IS NOT NULL
4445 THEN
4446 l_stmt := l_stmt
4447 || ' parallel (degree '
4451
4448 || g_parallel
4449 || ') ';
4450 END IF;
4452 l_stmt := l_stmt
4453 || ' as select '
4454 || g_fstg_pk
4455 || ' PK,count(1) dup_count from '
4456 || g_fact_pk_table
4457 || ' having count('
4458 || g_fstg_pk
4459 || ')>1 group by '
4460 || g_fstg_pk;
4461
4462 IF edw_owb_collection_util.drop_table (g_fact_dup_pk_table) = FALSE
4463 THEN
4464 NULL;
4465 END IF;
4466
4467 IF g_debug
4468 THEN
4469 write_to_log_n ( 'going to execute '
4470 || l_stmt
4471 || get_time);
4472 END IF;
4473
4474 EXECUTE IMMEDIATE l_stmt;
4475 l_num_dup := SQL%ROWCOUNT;
4476
4477 IF g_debug
4478 THEN
4479 write_to_log_n (
4480 'Created '
4481 || g_fact_dup_pk_table
4482 || ' with '
4483 || l_num_dup
4484 || ' rows'
4485 || get_time
4486 );
4487 END IF;
4488
4489 IF l_num_dup <= 0
4490 THEN
4491 fnd_message.set_name ('BIS', 'EDW_CDI_RECORDS_DUPLICATE');
4492 fnd_message.set_token ('TABLE', p_fact_name);
4493 fnd_message.set_token ('DUPLICATE', 0);
4494 fnd_message.set_token ('TOTAL', g_fstg_total_records);
4495 write_to_out (fnd_message.get);
4496 --write_to_out_log_n('No Duplicate records found');
4497 RETURN TRUE;
4498 END IF;
4499
4500 --EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_fact_dup_pk_table,instr(g_fact_dup_pk_table,'.')+1,
4501 --length(g_fact_dup_pk_table)),substr(g_fact_dup_pk_table,1,instr(g_fact_dup_pk_table,'.')-1));
4502 --write_to_out_log_n('Number of duplicate records in interface table '||l_num_dup);
4503
4504 l_stmt := 'select sum(dup_count) from '
4505 || g_fact_dup_pk_table;
4506
4507 IF g_debug
4508 THEN
4509 write_to_log_n ( 'going to execute '
4510 || l_stmt
4511 || get_time);
4512 END IF;
4513
4514 OPEN cv FOR l_stmt;
4515 FETCH cv INTO l_num_dup;
4516 CLOSE cv;
4517 fnd_message.set_name ('BIS', 'EDW_CDI_RECORDS_DUPLICATE');
4518 fnd_message.set_token ('TABLE', p_fact_name);
4519 fnd_message.set_token ('DUPLICATE', l_num_dup);
4520 fnd_message.set_token ('TOTAL', g_fstg_total_records);
4521 write_to_out (fnd_message.get);
4522 write_to_log_n (
4523 'Number of duplicate records in interface table '
4524 || l_num_dup
4525 );
4526
4527 IF g_results_table_flag
4528 THEN
4529 IF log_into_cdi_results_table (
4530 g_object_name,
4531 g_object_type,
4532 g_object_id,
4533 g_fstg_name,
4534 g_fstg_id,
4535 g_fstg_pk,
4536 g_fstg_pk_id,
4537 NULL,
4538 NULL,
4539 NULL,
4540 NULL,
4541 NULL,
4542 NULL,
4543 NULL,
4544 l_num_dup,
4545 NULL,
4546 g_fstg_total_records,
4547 'DUPLICATE'
4548 ) = FALSE
4549 THEN
4550 RETURN FALSE;
4551 END IF;
4552 END IF;
4553
4554 IF g_sample_on
4555 THEN
4556 l_stmt := 'select PK,dup_count from '
4557 || g_fact_dup_pk_table;
4558
4559 IF g_debug
4560 THEN
4561 write_to_log_n ( 'going to execute '
4562 || l_stmt
4563 || get_time);
4564 END IF;
4565
4566 l_number_dup_str := 1;
4567 write_to_out (' ');
4568 fnd_message.set_name ('BIS', 'EDW_CDI_SAMPLE_DUPLICATE');
4569 write_to_out (fnd_message.get);
4570
4571 --write_to_out('Sample Duplicate Records and their Count');
4572 IF g_debug
4573 THEN
4574 write_to_log_n ( 'going to execute '
4575 || l_stmt
4576 || get_time);
4577 END IF;
4578
4579 OPEN cv FOR l_stmt;
4580
4581 LOOP
4582 FETCH cv INTO l_dup_str (1), l_dup_count (1);
4583 EXIT WHEN cv%NOTFOUND;
4584
4585 IF g_number_max_sample IS NOT NULL
4586 AND l_number_dup_str > g_number_max_sample
4587 THEN
4588 EXIT;
4589 END IF;
4590
4591 IF log_into_cdi_dang_table (
4592 g_fstg_pk_id,
4593 g_fstg_id,
4594 NULL,
4595 l_dup_str (1),
4596 l_dup_count (1),
4597 NULL
4598 ) = FALSE
4599 THEN
4600 RETURN FALSE;
4601 END IF;
4602
4606 || ' ('
4603 IF l_number_dup_str <= g_number_sample
4604 THEN
4605 write_to_out ( l_dup_str (1)
4607 || l_dup_count (1)
4608 || ')');
4609 END IF;
4610
4611 l_number_dup_str := l_number_dup_str
4612 + 1;
4613 END LOOP;
4614
4615 l_number_dup_str := l_number_dup_str
4616 - 1;
4617 CLOSE cv;
4618
4619 IF g_debug
4620 THEN
4621 write_to_log_n (get_time);
4622 END IF;
4623
4624 write_to_out (' ');
4625 END IF;
4626
4627 IF edw_owb_collection_util.drop_table (g_fact_dup_pk_table) = FALSE
4628 THEN
4629 NULL;
4630 END IF;
4631
4632 RETURN TRUE;
4633 EXCEPTION
4634 WHEN OTHERS
4635 THEN
4636 write_to_out_log_n (SQLERRM);
4637 g_status_message := SQLERRM;
4638 RETURN FALSE;
4639 END execute_fact_duplicate_check;
4640
4641 FUNCTION drop_fstg_fk_tables (p_fact_name IN VARCHAR2)
4642 RETURN BOOLEAN
4643 IS
4644 BEGIN
4645 FOR i IN 1 .. g_number_fstg_fk
4646 LOOP
4647 IF edw_owb_collection_util.drop_table (g_fact_fk_table (i)) = FALSE
4648 THEN
4649 NULL;
4650 END IF;
4651 END LOOP;
4652
4653 RETURN TRUE;
4654 EXCEPTION
4655 WHEN OTHERS
4656 THEN
4657 write_to_out_log_n (SQLERRM);
4658 g_status_message := SQLERRM;
4659 RETURN FALSE;
4660 END drop_fstg_fk_tables;
4661
4662 FUNCTION drop_fstg_pk_table (p_fact_name IN VARCHAR2)
4663 RETURN BOOLEAN
4664 IS
4665 BEGIN
4666 IF edw_owb_collection_util.drop_table (g_fact_pk_table) = FALSE
4667 THEN
4668 NULL;
4669 END IF;
4670
4671 RETURN TRUE;
4672 EXCEPTION
4673 WHEN OTHERS
4674 THEN
4675 write_to_out_log_n (SQLERRM);
4676 g_status_message := SQLERRM;
4677 RETURN FALSE;
4678 END drop_fstg_pk_table;
4679
4680 FUNCTION drop_fk_table (p_fact_name IN VARCHAR2)
4681 RETURN BOOLEAN
4682 IS
4683 BEGIN
4684 IF edw_owb_collection_util.drop_table (g_fk_table) = FALSE
4685 THEN
4686 NULL;
4687 END IF;
4688
4689 RETURN TRUE;
4690 EXCEPTION
4691 WHEN OTHERS
4692 THEN
4693 write_to_out_log_n (SQLERRM);
4694 g_status_message := SQLERRM;
4695 RETURN FALSE;
4696 END drop_fk_table;
4697
4698 FUNCTION create_fstg_fk_tables (p_fact_name IN VARCHAR2)
4699 RETURN BOOLEAN
4700 IS
4701 l_stmt VARCHAR2 (30000);
4702 BEGIN
4703 IF g_debug
4704 THEN
4705 write_to_log_n ('In create_fstg_fk_tables');
4706 END IF;
4707
4708 FOR i IN 1 .. g_number_fstg_fk
4709 LOOP
4710 l_stmt := 'create table '
4711 || g_fact_fk_table (i)
4712 || ' tablespace '
4713 || g_op_table_space;
4714
4715 IF g_parallel IS NOT NULL
4716 THEN
4717 l_stmt := l_stmt
4718 || ' parallel (degree '
4719 || g_parallel
4720 || ') ';
4721 l_stmt := l_stmt
4722 || ' as select /*+PARELLEL('
4723 || g_fstg_name
4724 || ','
4725 || g_parallel
4726 || ')*/ ';
4727 ELSE
4728 l_stmt := l_stmt
4729 || ' as select ';
4730 END IF;
4731
4732 l_stmt := l_stmt
4733 || g_fstg_fk (i)
4734 || ' FK,'
4735 || g_fstg_name
4736 || '.rowid row_id from '
4737 || g_fstg_name
4738 || ','
4739 || g_fact_pk_table
4740 || ' where '
4741 || g_fstg_name
4742 || '.rowid='
4743 || g_fact_pk_table
4744 || '.row_id';
4745
4746 IF edw_owb_collection_util.drop_table (g_fact_fk_table (i)) = FALSE
4747 THEN
4748 NULL;
4749 END IF;
4750
4751 IF g_debug
4752 THEN
4753 write_to_log_n ( 'going to execute '
4754 || l_stmt
4755 || get_time);
4756 END IF;
4757
4758 EXECUTE IMMEDIATE l_stmt;
4759 g_number_fact_fk_table (i) := SQL%ROWCOUNT;
4760
4761 IF g_debug
4762 THEN
4763 write_to_log_n (
4764 'Created '
4765 || g_fact_fk_table (i)
4766 || ' with '
4767 || g_number_fact_fk_table (i)
4771 END IF;
4768 || ' rows'
4769 || get_time
4770 );
4772
4773 edw_owb_collection_util.analyze_table_stats (
4774 SUBSTR (
4775 g_fact_fk_table (i),
4776 INSTR (g_fact_fk_table (i), '.')
4777 + 1,
4778 LENGTH (g_fact_fk_table (i))
4779 ),
4780 SUBSTR (
4781 g_fact_fk_table (i),
4782 1,
4783 INSTR (g_fact_fk_table (i), '.')
4784 - 1
4785 )
4786 );
4787 END LOOP;
4788
4789 RETURN TRUE;
4790 EXCEPTION
4791 WHEN OTHERS
4792 THEN
4793 write_to_out_log_n (SQLERRM);
4794 g_status_message := SQLERRM;
4795 RETURN FALSE;
4796 END create_fstg_fk_tables;
4797
4798 FUNCTION execute_fact_dangling_check (p_fact_name IN VARCHAR2)
4799 RETURN BOOLEAN
4800 IS
4801 l_stmt VARCHAR2 (30000);
4802
4803 TYPE curtyp IS REF CURSOR;
4804
4805 cv curtyp;
4806 l_num_dang NUMBER;
4807 l_dang_str edw_owb_collection_util.varchartabletype;
4808 l_dang_count edw_owb_collection_util.numbertabletype;
4809 l_dang_instance edw_owb_collection_util.varchartabletype;
4810 l_number_dang_str NUMBER;
4811 l_fk_ok_number NUMBER;
4812 BEGIN
4813 fnd_message.set_name ('BIS', 'EDW_CDI_DANGLING_CHECK');
4814 write_to_out ( '-----------'
4815 || fnd_message.get
4816 || '-----------');
4817
4818 --write_to_out('----------- Dangling Check -------------');
4819 write_to_out (' ');
4820
4821 /*
4822 if create_fstg_fk_tables(p_fact_name)=false then
4823 return false;
4824 end if;*/
4825 IF g_debug
4826 THEN
4827 write_to_log_n ('In execute_fact_dangling_check');
4828 END IF;
4829
4830 FOR i IN 1 .. g_number_fstg_fk
4831 LOOP
4832 IF g_fk_check_flag (i)
4833 THEN
4834 l_stmt := 'create table '
4835 || g_fact_fk_ok_table (i)
4836 || ' tablespace '
4837 || g_op_table_space;
4838
4839 IF g_parallel IS NOT NULL
4840 THEN
4841 l_stmt := l_stmt
4842 || ' parallel (degree '
4843 || g_parallel
4844 || ') ';
4845 l_stmt := l_stmt
4846 || ' as select /*+ORDERED*/ /*+PARELLEL('
4847 || g_fact_dims (i)
4848 || ','
4849 || g_parallel
4850 || ')*/ ';
4851 ELSE
4852 l_stmt := l_stmt
4853 || ' as select /*+ORDERED*/ ';
4854 END IF;
4855
4856 l_stmt := l_stmt
4857 || g_fk_table
4858 || '.row_id from '
4859 || g_fk_table
4860 || ','
4861 || g_fact_dims (i)
4862 || ' where '
4863 || g_fact_dims (i)
4864 || '.'
4865 || g_fact_dims_pk (i)
4866 || '='
4867 || g_fk_table
4868 || '.'
4869 || g_fstg_fk (i);
4870
4871 IF edw_owb_collection_util.drop_table (g_fact_fk_ok_table (i)) =
4872 FALSE
4873 THEN
4874 NULL;
4875 END IF;
4876
4877 IF g_debug
4878 THEN
4879 write_to_log_n ( 'going to execute '
4880 || l_stmt
4881 || get_time);
4882 END IF;
4883
4884 EXECUTE IMMEDIATE l_stmt;
4885 l_fk_ok_number := SQL%ROWCOUNT;
4886
4887 IF g_debug
4888 THEN
4889 write_to_log_n (
4890 'Created '
4891 || g_fact_fk_ok_table (i)
4892 || ' with '
4893 || l_fk_ok_number
4894 || ' rows'
4895 || get_time
4896 );
4897 END IF;
4898
4899 edw_owb_collection_util.analyze_table_stats (
4900 SUBSTR (
4901 g_fact_fk_ok_table (i),
4902 INSTR (g_fact_fk_ok_table (i), '.')
4903 + 1,
4904 LENGTH (g_fact_fk_ok_table (i))
4905 ),
4906 SUBSTR (
4907 g_fact_fk_ok_table (i),
4908 1,
4909 INSTR (g_fact_fk_ok_table (i), '.')
4910 - 1
4911 )
4912 );
4913 l_num_dang := g_fstg_total_records
4914 - l_fk_ok_number;
4915
4916 IF g_results_table_flag
4917 THEN
4921 g_object_id,
4918 IF log_into_cdi_results_table (
4919 g_object_name,
4920 g_object_type,
4922 g_fstg_name,
4923 g_fstg_id,
4924 NULL,
4925 NULL,
4926 g_fstg_fk (i), --p_interface_table_fk
4927 g_fstg_fk_id (i),
4928 g_fact_dims (i), --p_parent_table
4929 g_fact_dims_id (i), --p_parent_table_id
4930 g_fact_dims_pk (i), --p_parent_table_pk
4931 NULL,
4932 l_num_dang, --p_number_dangling
4933 NULL,
4934 NULL,
4935 g_fstg_total_records,
4936 'DANGLING'
4937 ) = FALSE
4938 THEN
4939 RETURN FALSE;
4940 END IF;
4941 END IF;
4942
4943 IF l_num_dang <= 0
4944 THEN
4945 write_to_out (' ');
4946 fnd_message.set_name ('BIS', 'EDW_CDI_NO_DANGLING_KEYS');
4947 fnd_message.set_token ('FK', g_fstg_fk_long (i));
4948 write_to_out (fnd_message.get);
4949 --write_to_out('No Dangling Records found for Foreign Key '||g_fstg_fk_long(i));
4950 ELSE
4951 write_to_out (' ');
4952 fnd_message.set_name ('BIS', 'EDW_CDI_FACT_DANGLING_KEYS');
4953 fnd_message.set_token ('FK', g_fstg_fk_long (i));
4954 fnd_message.set_token ('DIM', g_fact_dims_long (i));
4955 fnd_message.set_token ('PK', g_fact_dims_pk_long (i));
4956 fnd_message.set_token ('DANG', l_num_dang);
4957 fnd_message.set_token ('TOTAL', g_fstg_total_records);
4958 write_to_out (fnd_message.get);
4959
4960 --write_to_out('For Foreign Key '||g_fstg_fk_long(i));
4961 --write_to_out('Parent Dimension '||g_fact_dims_long(i));
4962 --write_to_out('Primary Key '||g_fact_dims_pk_long(i));
4963 --write_to_out(l_num_dang||' out of '||g_fstg_total_records||' records are dangling');
4964 IF g_sample_on
4965 THEN
4966 l_stmt := 'create table '
4967 || g_fact_fk_dang_rowid_table (i)
4968 || ' tablespace '
4969 || g_op_table_space;
4970
4971 IF g_parallel IS NOT NULL
4972 THEN
4973 l_stmt :=
4974 l_stmt
4975 || ' parallel (degree '
4976 || g_parallel
4977 || ') ';
4978 END IF;
4979
4980 l_stmt := l_stmt
4981 || ' as select row_id from '
4982 || g_fact_pk_table
4983 || ' MINUS select row_id from '
4984 || g_fact_fk_ok_table (i);
4985
4986 IF edw_owb_collection_util.drop_table (
4987 g_fact_fk_dang_rowid_table (i)
4988 ) = FALSE
4989 THEN
4990 NULL;
4991 END IF;
4992
4993 IF g_debug
4994 THEN
4995 write_to_log_n (
4996 'going to execute '
4997 || l_stmt
4998 || get_time
4999 );
5000 END IF;
5001
5002 EXECUTE IMMEDIATE l_stmt;
5003
5004 IF g_debug
5005 THEN
5006 write_to_log_n (
5007 'Created '
5008 || g_fact_fk_dang_rowid_table (i)
5009 || ' with '
5010 || SQL%ROWCOUNT
5011 || ' rows'
5012 || get_time
5013 );
5014 END IF;
5015
5016 edw_owb_collection_util.analyze_table_stats (
5017 SUBSTR (
5018 g_fact_fk_dang_rowid_table (i),
5019 INSTR (g_fact_fk_dang_rowid_table (i), '.')
5020 + 1,
5021 LENGTH (g_fact_fk_dang_rowid_table (i))
5022 ),
5023 SUBSTR (
5024 g_fact_fk_dang_rowid_table (i),
5025 1,
5026 INSTR (g_fact_fk_dang_rowid_table (i), '.')
5027 - 1
5028 )
5029 );
5030 l_stmt := 'select /*+ORDERED*/ '
5031 || g_fk_table
5032 || '.'
5033 || g_fstg_fk (i);
5034
5035 IF g_fstg_instance_col IS NOT NULL
5036 THEN
5037 l_stmt := l_stmt
5038 || ','
5039 || g_fk_table
5040 || '.'
5044 l_stmt := l_stmt
5041 || g_fstg_instance_col;
5042 END IF;
5043
5045 || ', count(1) from '
5046 || g_fact_fk_dang_rowid_table (i)
5047 || ','
5048 || g_fk_table
5049 || ' where '
5050 || g_fk_table
5051 || '.row_id='
5052 || g_fact_fk_dang_rowid_table (i)
5053 || '.row_id group by '
5054 || g_fk_table
5055 || '.'
5056 || g_fstg_fk (i);
5057
5058 IF g_fstg_instance_col IS NOT NULL
5059 THEN
5060 l_stmt := l_stmt
5061 || ','
5062 || g_fk_table
5063 || '.'
5064 || g_fstg_instance_col;
5065 END IF;
5066
5067 l_stmt := l_stmt
5068 || ' order by count(1) desc';
5069
5070 IF g_debug
5071 THEN
5072 write_to_log_n (
5073 'going to execute '
5074 || l_stmt
5075 || get_time
5076 );
5077 END IF;
5078
5079 OPEN cv FOR l_stmt;
5080 l_number_dang_str := 1;
5081 write_to_out (' ');
5082 fnd_message.set_name ('BIS', 'EDW_CDI_SAMPLE_DANGLING');
5083 write_to_out (fnd_message.get);
5084 --write_to_out('Sample Dangling Records and their count ');
5085 l_dang_instance (1) := NULL;
5086
5087 LOOP
5088 IF g_fstg_instance_col IS NOT NULL
5089 THEN
5090 FETCH cv INTO l_dang_str (1),
5091 l_dang_instance (1),
5092 l_dang_count (1);
5093 ELSE
5094 FETCH cv INTO l_dang_str (1), l_dang_count (1);
5095 END IF;
5096
5097 EXIT WHEN cv%NOTFOUND;
5098
5099 IF g_number_max_sample IS NOT NULL
5100 AND l_number_dang_str > g_number_max_sample
5101 THEN
5102 EXIT;
5103 END IF;
5104
5105 IF log_into_cdi_dang_table (
5106 g_fstg_fk_id (i),
5107 g_fstg_id,
5108 g_fact_dims_id (i),
5109 l_dang_str (1),
5110 l_dang_count (1),
5111 l_dang_instance (1)
5112 ) = FALSE
5113 THEN
5114 RETURN FALSE;
5115 END IF;
5116
5117 IF l_number_dang_str <= g_number_sample
5118 THEN
5119 IF g_fstg_instance_col IS NOT NULL
5120 THEN
5121 write_to_out (
5122 l_dang_str (1)
5123 || ' ('
5124 || l_dang_instance (1)
5125 || ') ('
5126 || l_dang_count (1)
5127 || ')'
5128 );
5129 ELSE
5130 write_to_out (
5131 l_dang_str (1)
5132 || ' ('
5133 || l_dang_count (1)
5134 || ')'
5135 );
5136 END IF;
5137 END IF;
5138
5139 l_number_dang_str := l_number_dang_str
5140 + 1;
5141 END LOOP;
5142
5143 write_to_out (' ');
5144 CLOSE cv;
5145
5146 IF g_debug
5147 THEN
5148 write_to_log_n (get_time);
5149 END IF;
5150
5151 IF edw_owb_collection_util.drop_table (
5152 g_fact_fk_dang_rowid_table (i)
5153 ) = FALSE
5154 THEN
5155 NULL;
5156 END IF;
5157 END IF; --if g_sample_on
5158 END IF;
5159
5160 --create the dang rowid table
5161 IF edw_owb_collection_util.drop_table (g_fact_fk_ok_table (i)) =
5162 FALSE
5163 THEN
5164 NULL;
5165 END IF;
5166 END IF; --if g_fk_check_flag(i) then
5167 END LOOP;
5168
5169 RETURN TRUE;
5170 EXCEPTION
5171 WHEN OTHERS
5172 THEN
5173 write_to_out_log_n (SQLERRM);
5177
5174 g_status_message := SQLERRM;
5175 RETURN FALSE;
5176 END execute_fact_dangling_check;
5178 FUNCTION execute_fstg_makeit_stmt (p_fact_name IN VARCHAR2)
5179 RETURN BOOLEAN
5180 IS
5181 l_stmt VARCHAR2 (30000);
5182
5183 TYPE curtyp IS REF CURSOR;
5184
5185 cv curtyp;
5186 l_number_makeit NUMBER;
5187 BEGIN
5188 l_stmt := 'select nvl(count(1),0) from '
5189 || g_fk_table
5190 || ' A ';
5191
5192 FOR i IN 1 .. g_number_fstg_fk
5193 LOOP
5194 l_stmt := l_stmt
5195 || ','
5196 || g_fact_dims (i)
5197 || ' B'
5198 || i
5199 || ' ';
5200 END LOOP;
5201
5202 l_stmt := l_stmt
5203 || ' where 1=1 ';
5204
5205 IF g_duplicate_check
5206 THEN
5207 l_stmt := l_stmt
5208 || ' and A.'
5209 || g_fstg_pk
5210 || ' in (select '
5211 || g_fstg_pk
5212 || ' from '
5213 || g_fk_table
5214 || ' having count('
5215 || g_fstg_pk
5216 || ') = 1 group by '
5217 || g_fstg_pk
5218 || ') ';
5219 END IF;
5220
5221 FOR i IN 1 .. g_number_fstg_fk
5222 LOOP
5223 l_stmt := l_stmt
5224 || ' and A.'
5225 || g_fstg_fk (i)
5226 || ' = B'
5227 || i
5228 || '.'
5229 || g_fact_dims_pk (i)
5230 || ' ';
5231 END LOOP;
5232
5233 IF g_debug
5234 THEN
5235 write_to_log_n ( 'Going to execute '
5236 || l_stmt
5237 || get_time);
5238 END IF;
5239
5240 OPEN cv FOR l_stmt;
5241 FETCH cv INTO l_number_makeit;
5242 CLOSE cv;
5243
5244 IF g_debug
5245 THEN
5246 write_to_log_n (get_time);
5247 END IF;
5248
5249 write_to_out (' ');
5250 fnd_message.set_name ('BIS', 'EDW_CDI_RECORDS_INTO_FACT');
5251 fnd_message.set_token ('RECORDS', l_number_makeit);
5252 fnd_message.set_token ('TOTAL', g_fstg_total_records);
5253 write_to_out (fnd_message.get);
5254 --write_to_out_n('The total number of records in the fact interface');
5255 --write_to_out('table that will make it into the WH');
5256 --write_to_out(l_number_makeit||' out of '||g_fstg_total_records);
5257 RETURN TRUE;
5258 EXCEPTION
5259 WHEN OTHERS
5260 THEN
5261 write_to_out_log_n (SQLERRM);
5262 g_status_message := SQLERRM;
5263 RETURN FALSE;
5264 END execute_fstg_makeit_stmt;
5265
5266 FUNCTION generate_fk_table (p_fact_name IN VARCHAR2)
5267 RETURN BOOLEAN
5268 IS
5269 l_stmt VARCHAR2 (30000);
5270 l_owner VARCHAR2 (400);
5271 l_col VARCHAR2 (400);
5272 BEGIN
5273 l_owner := edw_owb_collection_util.get_table_owner (g_fstg_name);
5274 g_fk_table := g_bis_owner
5275 || '.'
5276 || SUBSTR (p_fact_name, 1, 26)
5277 || 'CS';
5278
5279 IF edw_owb_collection_util.drop_table (g_fk_table) = FALSE
5280 THEN
5281 NULL;
5282 END IF;
5283
5284 IF edw_owb_collection_util.does_table_have_data (
5285 g_fstg_name,
5286 'LAST_UPDATE_DATE IS NOT NULL'
5287 ) = 2
5288 THEN
5289 l_col := 'LAST_UPDATE_DATE';
5290 ELSE
5291 l_col := 'ROWNUM';
5292 END IF;
5293
5294 l_stmt :=
5295 'create table '
5296 || g_fk_table
5297 || ' tablespace '
5298 || g_op_table_space;
5299
5300 IF g_parallel IS NOT NULL
5301 THEN
5302 l_stmt := l_stmt
5303 || ' parallel (degree '
5304 || g_parallel
5305 || ') ';
5306 END IF;
5307
5308 l_stmt := l_stmt
5309 || ' as select ';
5310
5311 IF g_parallel IS NOT NULL
5312 THEN
5313 l_stmt := l_stmt
5314 || ' /*+PARELLEL('
5315 || g_fstg_name
5316 || ','
5317 || g_parallel
5318 || ')*/ ';
5319 END IF;
5320
5321 FOR i IN 1 .. g_number_fstg_fk
5322 LOOP
5323 IF g_fk_check_flag (i)
5324 THEN
5325 l_stmt := l_stmt
5326 || g_fstg_fk (i)
5327 || ',';
5328 END IF;
5329 END LOOP;
5330
5331 l_stmt := l_stmt
5332 || g_fstg_pk
5333 || ',rowid row_id,'
5334 || l_col
5335 || ' col ';
5339 || g_fstg_name
5336 l_stmt :=
5337 l_stmt
5338 || ' from '
5340 || ' where collection_status in (''READY'',''DANGLING'',''DUPLICATE'')';
5341
5342 BEGIN
5343 IF g_debug
5344 THEN
5345 write_to_log_n ( 'Going to execute '
5346 || l_stmt
5347 || get_time);
5348 END IF;
5349
5350 EXECUTE IMMEDIATE l_stmt;
5351
5352 IF g_debug
5353 THEN
5354 write_to_log_n (
5355 'Created '
5356 || g_fk_table
5357 || ' with '
5358 || SQL%ROWCOUNT
5359 || ' rows'
5360 || get_time
5361 );
5362 END IF;
5363 EXCEPTION
5364 WHEN OTHERS
5365 THEN
5366 write_to_log_n (
5367 'Error creating table '
5368 || g_fk_table
5369 || ' '
5370 || SQLERRM
5371 );
5372 g_status_message := SQLERRM;
5373 RETURN FALSE;
5374 END;
5375
5376 edw_owb_collection_util.analyze_table_stats (
5377 SUBSTR (g_fk_table, INSTR (g_fk_table, '.')
5378 + 1, LENGTH (g_fk_table)),
5379 SUBSTR (g_fk_table, 1, INSTR (g_fk_table, '.')
5380 - 1)
5381 );
5382 RETURN TRUE;
5383 EXCEPTION
5384 WHEN OTHERS
5385 THEN
5386 write_to_out_log_n (SQLERRM);
5387 g_status_message := SQLERRM;
5388 RETURN FALSE;
5389 END generate_fk_table;
5390
5391 PROCEDURE clean_up
5392 IS
5393 BEGIN
5394 IF edw_owb_collection_util.drop_table (g_fk_table) = FALSE
5395 THEN
5396 NULL;
5397 END IF;
5398 EXCEPTION
5399 WHEN OTHERS
5400 THEN
5401 write_to_out_log_n (SQLERRM);
5402 g_status_message := SQLERRM;
5403 END clean_up;
5404
5405 FUNCTION get_fk_to_check (p_fk_to_check IN VARCHAR2)
5406 RETURN BOOLEAN
5407 IS
5408 l_start NUMBER;
5409 l_end NUMBER;
5410 l_len NUMBER;
5411 BEGIN
5412 IF g_debug
5413 THEN
5414 write_to_log_n (
5415 'In get_fk_to_check p_fk_to_check='
5416 || p_fk_to_check
5417 );
5418 END IF;
5419
5420 IF p_fk_to_check IS NULL
5421 THEN
5422 RETURN TRUE;
5423 END IF;
5424
5425 l_len := LENGTH (p_fk_to_check);
5426
5427 IF INSTR (p_fk_to_check, ',') = 0
5428 THEN
5429 g_number_fk_to_check := 1;
5430 g_fk_to_check (1) := p_fk_to_check;
5431 RETURN TRUE;
5432 END IF;
5433
5434 l_start := 1;
5435 l_end := 1;
5436
5437 LOOP
5438 l_end := INSTR (p_fk_to_check, ',', l_start);
5439
5440 IF l_end = 0
5441 THEN
5442 l_end := LENGTH (p_fk_to_check)
5443 + 1;
5444 END IF;
5445
5446 g_number_fk_to_check := g_number_fk_to_check
5447 + 1;
5448 g_fk_to_check (g_number_fk_to_check) :=
5449 SUBSTR (p_fk_to_check, l_start, l_end
5450 - l_start);
5451 l_start := l_end
5452 + 1;
5453
5454 IF l_start > l_len
5455 THEN
5456 EXIT;
5457 END IF;
5458 END LOOP;
5459
5460 IF g_debug
5461 THEN
5462 write_to_log_n ('FKs to check');
5463
5464 FOR i IN 1 .. g_number_fk_to_check
5465 LOOP
5466 write_to_log (g_fk_to_check (i));
5467 END LOOP;
5468 END IF;
5469
5470 RETURN TRUE;
5471 EXCEPTION
5472 WHEN OTHERS
5473 THEN
5474 write_to_out_log_n (SQLERRM);
5475 g_status_message := SQLERRM;
5476 RETURN FALSE;
5477 END get_fk_to_check;
5478
5479 FUNCTION log_into_cdi_results_table (
5480 p_object IN VARCHAR2,
5481 p_object_type IN VARCHAR2,
5482 p_object_id IN NUMBER,
5483 p_interface_table IN VARCHAR2,
5484 p_interface_table_id IN NUMBER,
5485 p_interface_table_pk IN VARCHAR2,
5486 p_interface_table_pk_id IN NUMBER,
5487 p_interface_table_fk IN VARCHAR2,
5488 p_interface_table_fk_id IN NUMBER,
5489 p_parent_table IN VARCHAR2,
5490 p_parent_table_id IN NUMBER,
5491 p_parent_table_pk IN VARCHAR2,
5492 p_parent_table_pk_id IN NUMBER,
5493 p_number_dangling IN NUMBER,
5494 p_number_duplicate IN NUMBER,
5495 p_number_error IN NUMBER,
5496 p_total_records IN NUMBER,
5500 IS
5497 p_error_type IN VARCHAR2
5498 )
5499 RETURN BOOLEAN
5501 BEGIN
5502 IF edw_owb_collection_util.log_into_cdi_results_table (
5503 p_object,
5504 p_object_type,
5505 p_object_id,
5506 p_interface_table,
5507 p_interface_table_id,
5508 p_interface_table_pk,
5509 p_interface_table_pk_id,
5510 p_interface_table_fk,
5511 p_interface_table_fk_id,
5512 p_parent_table,
5513 p_parent_table_id,
5514 p_parent_table_pk,
5515 p_parent_table_pk_id,
5516 p_number_dangling,
5517 p_number_duplicate,
5518 p_number_error,
5519 p_total_records,
5520 p_error_type
5521 ) = FALSE
5522 THEN
5523 RETURN FALSE;
5524 END IF;
5525
5526 RETURN TRUE;
5527 EXCEPTION
5528 WHEN OTHERS
5529 THEN
5530 write_to_log_n ( 'Error in log_into_cdi_results_table '
5531 || SQLERRM);
5532 g_status_message := SQLERRM;
5533 RETURN FALSE;
5534 END log_into_cdi_results_table;
5535
5536 FUNCTION delete_cdi_results_table (p_object_name IN VARCHAR2)
5537 RETURN BOOLEAN
5538 IS
5539 l_stmt VARCHAR2 (2000);
5540 l_table_id NUMBER;
5541
5542 TYPE curtyp IS REF CURSOR;
5543
5544 cv curtyp;
5545 BEGIN
5546 l_stmt :=
5547 'select distinct interface_table_id from EDW_CDI_RESULTS where object_name=:a';
5548
5549 IF g_debug
5550 THEN
5551 write_to_log_n (
5552 'Going to execute '
5553 || l_stmt
5554 || ' using '
5555 || p_object_name
5556 );
5557 END IF;
5558
5559 OPEN cv FOR l_stmt USING p_object_name;
5560
5561 LOOP
5562 FETCH cv INTO l_table_id;
5563 EXIT WHEN cv%NOTFOUND;
5564 l_stmt := 'delete EDW_CDI_KEY_VALUES where table_id=:a';
5565
5566 IF g_debug
5567 THEN
5568 write_to_log_n (
5569 'Going to execute '
5570 || l_stmt
5571 || ' using '
5572 || l_table_id
5573 );
5574 END IF;
5575
5576 EXECUTE IMMEDIATE l_stmt USING l_table_id;
5577 COMMIT;
5578 END LOOP;
5579
5580 l_stmt := 'delete '
5581 || g_results_table
5582 || ' where object_name=:a';
5583
5584 IF g_debug
5585 THEN
5586 write_to_log_n (
5587 'Going to execute '
5588 || l_stmt
5589 || ' using '
5590 || p_object_name
5591 );
5592 END IF;
5593
5594 EXECUTE IMMEDIATE l_stmt USING p_object_name;
5595 COMMIT;
5596 l_table_id := edw_owb_collection_util.get_object_id (p_object_name);
5597 l_stmt := 'delete edw_cdi_dim_missing_keys where fact_id=:a';
5598
5599 IF g_debug
5600 THEN
5601 write_to_log_n (
5602 'Going to execute '
5603 || l_stmt
5604 || ' using '
5605 || l_table_id
5606 );
5607 END IF;
5608
5609 EXECUTE IMMEDIATE l_stmt USING l_table_id;
5610 COMMIT;
5611 RETURN TRUE;
5612 EXCEPTION
5613 WHEN OTHERS
5614 THEN
5615 write_to_log_n ( 'Error in delete_cdi_results_table '
5616 || SQLERRM);
5617 g_status_message := SQLERRM;
5618 RETURN FALSE;
5619 END delete_cdi_results_table;
5620
5621 FUNCTION log_into_cdi_dang_table (
5622 p_key_id IN NUMBER,
5623 p_table_id IN NUMBER,
5624 p_parent_table_id IN NUMBER,
5625 p_key_value IN VARCHAR2,
5626 p_number_key_value IN NUMBER,
5627 p_instance IN VARCHAR2
5628 )
5629 RETURN BOOLEAN
5630 IS
5631 BEGIN
5632 IF edw_owb_collection_util.log_into_cdi_dang_table (
5633 p_key_id,
5634 p_table_id,
5635 p_parent_table_id,
5636 p_key_value,
5637 p_number_key_value,
5638 p_instance,
5639 'N'
5640 ) = FALSE
5641 THEN
5642 RETURN FALSE;
5643 END IF;
5644
5645 RETURN TRUE;
5646 EXCEPTION
5647 WHEN OTHERS
5648 THEN
5649 write_to_log_n ( 'Error in log_into_cdi_dang_table '
5650 || SQLERRM);
5651 g_status_message := SQLERRM;
5652 RETURN FALSE;
5653 END log_into_cdi_dang_table;
5654
5655
5656 /*
5657 this is meant to consolidate all dang dim values when there are multiple keys pointing to the same dim
5658 */
5659 FUNCTION create_g_dim_missing_keys_op (p_object_name IN VARCHAR2)
5660 RETURN BOOLEAN
5661 IS
5665 BEGIN
5662 l_stmt VARCHAR2 (10000);
5663 l_object VARCHAR2 (100);
5664 l_count NUMBER;
5666 IF g_debug
5667 THEN
5668 write_to_log_n ( 'In create_g_dim_missing_keys_op'
5669 || get_time);
5670 END IF;
5671
5672 l_object := SUBSTR (p_object_name, 1, 28);
5673 g_dim_missing_keys_op := g_bis_owner
5674 || '.'
5675 || l_object
5676 || 'M';
5677 l_stmt := 'create table '
5678 || g_dim_missing_keys_op
5679 || ' tablespace '
5680 || g_op_table_space;
5681
5682 IF g_parallel IS NOT NULL
5683 THEN
5684 l_stmt := l_stmt
5685 || ' parallel (degree '
5686 || g_parallel
5687 || ') ';
5688 END IF;
5689
5690 l_stmt := l_stmt
5691 || ' as select ';
5692
5693 IF g_parallel IS NOT NULL
5694 THEN
5695 l_stmt := l_stmt
5696 || '/*+PARALLEL(B,'
5697 || g_parallel
5698 || ')*/ ';
5699 END IF;
5700
5701 l_stmt :=
5702 l_stmt
5703 || ' A.parent_table_id,B.key_value,sum(B.number_key_value) number_key_value,B.instance '
5704 || 'from edw_cdi_results A,edw_cdi_key_values B '
5705 || 'where '
5706 || 'A.interface_table_fk_id=B.key_id '
5707 || 'and A.parent_table_id=B.parent_table_id '
5708 || 'and A.object_name='''
5709 || p_object_name
5710 || ''' '
5711 || 'group by A.parent_table_id,B.key_value,B.instance ';
5712
5713 IF edw_owb_collection_util.drop_table (g_dim_missing_keys_op) = FALSE
5714 THEN
5715 NULL;
5716 END IF;
5717
5718 IF g_debug
5719 THEN
5720 write_to_log_n ( 'Going to execute '
5721 || l_stmt
5722 || get_time);
5723 END IF;
5724
5725 EXECUTE IMMEDIATE l_stmt;
5726 l_count := SQL%ROWCOUNT;
5727
5728 IF g_debug
5729 THEN
5730 write_to_log_n (
5731 'Created '
5732 || g_dim_missing_keys_op
5733 || ' with '
5734 || l_count
5735 || ' rows '
5736 || get_time
5737 );
5738 END IF;
5739
5740 edw_owb_collection_util.analyze_table_stats (
5741 SUBSTR (
5742 g_dim_missing_keys_op,
5743 INSTR (g_dim_missing_keys_op, '.')
5744 + 1,
5745 LENGTH (g_dim_missing_keys_op)
5746 ),
5747 SUBSTR (
5748 g_dim_missing_keys_op,
5749 1,
5750 INSTR (g_dim_missing_keys_op, '.')
5751 - 1
5752 )
5753 );
5754 RETURN TRUE;
5755 EXCEPTION
5756 WHEN OTHERS
5757 THEN
5758 g_status_message := SQLERRM;
5759 write_to_log_n (g_status_message);
5760 RETURN FALSE;
5761 END create_g_dim_missing_keys_op;
5762
5763 FUNCTION process_dang_keys (p_fact IN VARCHAR2)
5764 RETURN BOOLEAN
5765 IS
5766 l_stmt VARCHAR2 (8000);
5767
5768 TYPE curtyp IS REF CURSOR;
5769
5770 cv curtyp;
5771 l_dang_dim edw_owb_collection_util.varchartabletype;
5772 l_dang_dim_id edw_owb_collection_util.numbertabletype;
5773 l_number_dang_dim NUMBER;
5774 l_fact_id NUMBER;
5775 l_instances edw_owb_collection_util.varchartabletype;
5776 l_instances_name edw_owb_collection_util.l_varchartabletype;
5777 l_wh_apps_links edw_owb_collection_util.varchartabletype;
5778 l_number_instances NUMBER;
5779 l_dang_dim_instance_id edw_owb_collection_util.numbertabletype;
5780 --which dims in which instances have dang values
5781 l_dang_instances edw_owb_collection_util.varchartabletype;
5782 --the instances where dims have dang
5783 l_number_dang_instances NUMBER;
5784 l_index NUMBER;
5785 l_fact_list VARCHAR2 (1000);
5786 l_dim_list VARCHAR2 (32000);
5787 l_status NUMBER;
5788 l_prev_instance VARCHAR2 (400);
5789 BEGIN
5790 IF g_debug
5791 THEN
5792 write_to_log_n ( 'In process_dang_keys '
5793 || get_time);
5794 END IF;
5795
5796 IF create_g_dim_missing_keys_op (p_fact) = FALSE
5797 THEN
5798 RETURN FALSE;
5799 END IF;
5800
5801 l_stmt :=
5802 'select NAME,INSTANCE_CODE,WAREHOUSE_TO_INSTANCE_LINK from edw_source_instances '
5803 || 'where ENABLED_FLAG=''Y''';
5804
5805 IF g_debug
5806 THEN
5807 write_to_log_n ( 'Going to execute '
5808 || l_stmt
5809 || get_time);
5813 OPEN cv FOR l_stmt;
5810 END IF;
5811
5812 l_number_instances := 1;
5814
5815 LOOP
5816 FETCH cv INTO l_instances_name (l_number_instances),
5817 l_instances (l_number_instances),
5818 l_wh_apps_links (l_number_instances);
5819 EXIT WHEN cv%NOTFOUND;
5820 l_number_instances := l_number_instances
5821 + 1;
5822 END LOOP;
5823
5824 l_number_instances := l_number_instances
5825 - 1;
5826 CLOSE cv;
5827
5828 IF g_debug
5829 THEN
5830 write_to_log_n ('The instances');
5831
5832 FOR i IN 1 .. l_number_instances
5833 LOOP
5834 write_to_log (
5835 l_instances_name (i)
5836 || '('
5837 || l_instances (i)
5838 || ' with link '
5839 || l_wh_apps_links (i)
5840 || ')'
5841 );
5842 END LOOP;
5843 END IF;
5844
5845 l_number_dang_dim := 1;
5846 l_stmt :=
5847 'select distinct parent_table,parent_table_id from edw_cdi_results where number_dangling>0 '
5848 || 'and object_name=:a';
5849
5850 IF g_debug
5851 THEN
5852 write_to_log_n ( 'Going to execute '
5853 || l_stmt
5854 || get_time);
5855 END IF;
5856
5857 OPEN cv FOR l_stmt USING p_fact;
5858
5859 LOOP
5860 FETCH cv INTO l_dang_dim (l_number_dang_dim),
5861 l_dang_dim_id (l_number_dang_dim);
5862 EXIT WHEN cv%NOTFOUND;
5863 l_number_dang_dim := l_number_dang_dim
5864 + 1;
5865 END LOOP;
5866
5867 CLOSE cv;
5868 l_number_dang_dim := l_number_dang_dim
5869 - 1;
5870
5871 IF g_debug
5872 THEN
5873 write_to_log_n ('The dimension with dang rows ');
5874
5875 FOR i IN 1 .. l_number_dang_dim
5876 LOOP
5877 write_to_log ( l_dang_dim (i)
5878 || '('
5879 || l_dang_dim_id (i)
5880 || ')');
5881 END LOOP;
5882 END IF;
5883
5884 --find which dims from which instances are dangling
5885 l_number_dang_instances := 1;
5886 l_stmt := 'select distinct parent_table_id,instance from '
5887 || g_dim_missing_keys_op
5888 || ' order by instance';
5889
5890 IF g_debug
5891 THEN
5892 write_to_log_n ( 'Going to execute '
5893 || l_stmt
5894 || get_time);
5895 END IF;
5896
5897 OPEN cv FOR l_stmt;
5898
5899 LOOP
5900 FETCH cv INTO l_dang_dim_instance_id (l_number_dang_instances),
5901 l_dang_instances (l_number_dang_instances);
5902 EXIT WHEN cv%NOTFOUND;
5903 l_number_dang_instances := l_number_dang_instances
5904 + 1;
5905 END LOOP;
5906
5907 l_number_dang_instances := l_number_dang_instances
5908 - 1;
5909
5910 IF g_debug
5911 THEN
5912 write_to_log_n ( 'Results'
5913 || get_time);
5914
5915 FOR i IN 1 .. l_number_dang_instances
5916 LOOP
5917 write_to_log (
5918 l_dang_dim_instance_id (i)
5919 || ' '
5920 || l_dang_instances (i)
5921 );
5922 END LOOP;
5923 END IF;
5924
5925 l_fact_id := edw_owb_collection_util.get_object_id (p_fact);
5926
5927 FOR j IN 1 .. l_number_dang_instances
5928 LOOP
5929 l_index :=
5930 edw_owb_collection_util.index_in_table (
5931 l_instances,
5932 l_number_instances,
5933 l_dang_instances (j)
5934 );
5935
5936 IF l_index > 0
5937 THEN
5938 IF l_prev_instance IS NULL
5939 OR l_prev_instance <> l_dang_instances (j)
5940 THEN
5941 fnd_message.set_name ('BIS', 'EDW_CDI_MDR_INSTANCE');
5942 fnd_message.set_token ('INSTANCE', l_instances_name (l_index));
5943 write_to_out_n (fnd_message.get);
5944 write_to_log_n (
5945 'Missing Date Range for Instance '
5946 || l_instances_name (l_index)
5947 || '('
5948 || l_dang_instances (j)
5949 || ')'
5950 );
5951 l_prev_instance := l_dang_instances (j);
5952 END IF;
5953
5954 FOR i IN 1 .. l_number_dang_dim
5955 LOOP
5956 IF l_dang_dim_instance_id (j) = l_dang_dim_id (i)
5957 THEN
5958 IF find_missing_date_range (
5959 p_fact,
5960 l_fact_id,
5961 l_dang_dim (i),
5965 ) = FALSE
5962 l_dang_dim_id (i),
5963 l_dang_instances (j),
5964 l_wh_apps_links (l_index)
5966 THEN
5967 RETURN FALSE;
5968 END IF;
5969 END IF;
5970 END LOOP;
5971 ELSE
5972 write_to_log_n (
5973 'Instance '
5974 || l_dang_instances (j)
5975 || ' is not present in source instances'
5976 );
5977 END IF;
5978 END LOOP;
5979
5980 l_fact_list := p_fact;
5981 l_dim_list := NULL;
5982
5983 --find the bad records
5984 FOR i IN 1 .. l_number_dang_dim
5985 LOOP
5986 l_status := create_bad_key_tables (
5987 p_fact,
5988 l_fact_id,
5989 l_dang_dim (i),
5990 l_dang_dim_id (i),
5991 l_dang_dim_instance_id,
5992 l_dang_instances,
5993 l_number_dang_instances
5994 );
5995
5996 IF l_status = -1
5997 THEN
5998 RETURN FALSE;
5999 END IF;
6000
6001 IF l_status = 1
6002 THEN
6003 l_dim_list := l_dim_list
6004 || l_dang_dim (i)
6005 || ',';
6006 END IF;
6007 --if l_status=0 then do not pass this dim for checking error tables
6008 END LOOP;
6009
6010 IF edw_owb_collection_util.drop_table (g_dim_missing_keys_op) = FALSE
6011 THEN
6012 NULL;
6013 END IF;
6014
6015 IF l_status = 1
6016 THEN
6017 edw_wh_dang_recovery.load_error_table (
6018 l_fact_list,
6019 l_dim_list,
6020 g_op_table_space,
6021 g_parallel,
6022 g_bis_owner,
6023 NULL, --p_instance,
6024 g_debug,
6025 'CDI', --p_mode
6026 'CDI', --p_called_from
6027 g_fk_table
6028 );
6029
6030 IF edw_wh_dang_recovery.g_status = FALSE
6031 THEN
6032 g_status_message := edw_wh_dang_recovery.g_status_message;
6033 RETURN FALSE;
6034 END IF;
6035 END IF;
6036
6037 RETURN TRUE;
6038 EXCEPTION
6039 WHEN OTHERS
6040 THEN
6041 g_status_message := SQLERRM;
6042 write_to_log_n (g_status_message);
6043 RETURN FALSE;
6044 END process_dang_keys;
6045
6046 FUNCTION find_missing_date_range (
6047 p_fact IN VARCHAR2,
6048 p_fact_id IN NUMBER,
6049 p_dim IN VARCHAR2,
6050 p_dim_id IN NUMBER,
6051 p_instance IN VARCHAR2,
6052 p_instance_link IN VARCHAR2
6053 )
6054 RETURN BOOLEAN
6055 IS
6056 l_view VARCHAR2 (200);
6057 l_min_date DATE;
6058 l_max_date DATE;
6059 l_long_name VARCHAR2 (400);
6060 BEGIN
6061 l_view := edw_owb_collection_util.get_pk_view (p_dim, p_instance_link);
6062 l_long_name := edw_owb_collection_util.get_logical_name (p_dim_id);
6063
6064 IF l_view IS NULL
6065 THEN
6066 --write_to_out_n('Dimension '||l_long_name);
6067 fnd_message.set_name ('BIS', 'EDW_CDI_MDR_PK_VIEW_NOT_FOUND');
6068 fnd_message.set_token ('DIM', l_long_name);
6069 fnd_message.set_token ('PK_VIEW', l_view);
6070 write_to_out (fnd_message.get);
6071 --write_to_out('Primary Key View '||l_view||' not found in source database');
6072 RETURN TRUE;
6073 END IF;
6074
6075 IF find_missing_date_range (
6076 p_fact,
6077 p_fact_id,
6078 p_dim,
6079 p_dim_id,
6080 p_instance,
6081 p_instance_link,
6082 l_view,
6083 l_min_date,
6084 l_max_date
6085 ) = FALSE
6086 THEN
6087 RETURN FALSE;
6088 END IF;
6089
6090 fnd_message.set_name ('BIS', 'EDW_CDI_DIM_NAME');
6091 fnd_message.set_token ('DIM', l_long_name);
6092 write_to_out_n (fnd_message.get);
6093 write_to_log_n ( 'Dimension '
6094 || l_long_name);
6095
6096 IF l_min_date IS NOT NULL
6097 THEN
6098 fnd_message.set_name ('BIS', 'EDW_CDI_DATE_RANGE');
6099 fnd_message.set_token (
6100 'FROM',
6101 TO_CHAR (l_min_date, 'MM/DD/YYYY HH24:MI:SS')
6102 );
6103 fnd_message.set_token (
6104 'TO',
6105 TO_CHAR (l_max_date, 'MM/DD/YYYY HH24:MI:SS')
6106 );
6107 write_to_out (fnd_message.get);
6108 write_to_log (
6109 'From '
6110 || TO_CHAR (l_min_date, 'MM/DD/YYYY HH24:MI:SS')
6111 || ' To '
6112 || TO_CHAR (l_max_date, 'MM/DD/YYYY HH24:MI:SS')
6113 );
6114 ELSE
6118 'From and To Date could not be determined as no match was found with'
6115 fnd_message.set_name ('BIS', 'EDW_CDI_NO_DATE_RANGE');
6116 write_to_out (fnd_message.get);
6117 write_to_log (
6119 );
6120 write_to_log ('the source Primary Key View');
6121 END IF;
6122
6123 RETURN TRUE;
6124 EXCEPTION
6125 WHEN OTHERS
6126 THEN
6127 g_status_message := SQLERRM;
6128 write_to_log_n (g_status_message);
6129 RETURN FALSE;
6130 END find_missing_date_range;
6131
6132 FUNCTION find_missing_date_range (
6133 p_fact IN VARCHAR2,
6134 p_fact_id IN NUMBER,
6135 p_dim IN VARCHAR2,
6136 p_dim_id IN NUMBER,
6137 p_instance IN VARCHAR2,
6138 p_instance_link IN VARCHAR2,
6139 p_view IN VARCHAR2,
6140 p_min_date OUT NOCOPY DATE,
6141 p_max_date OUT NOCOPY DATE
6142 )
6143 RETURN BOOLEAN
6144 IS
6145 l_stmt VARCHAR2 (8000);
6146
6147 TYPE curtyp IS REF CURSOR;
6148
6149 cv curtyp;
6150 l_table VARCHAR2 (200);
6151 l_count NUMBER;
6152 BEGIN
6153 IF g_debug
6154 THEN
6155 write_to_log_n (
6156 'In find_missing_date_range p_fact='
6157 || p_fact
6158 || ',p_dim='
6159 || p_dim
6160 || ',p_instance='
6161 || p_instance
6162 || ',link='
6163 || p_instance_link
6164 || ',view='
6165 || p_view
6166 );
6167 END IF;
6168
6169 l_table := g_bis_owner
6170 || '.D_'
6171 || p_instance
6172 || '_'
6173 || p_fact_id
6174 || '_'
6175 || p_dim_id;
6176 --there are dependencies to this name
6177 l_stmt :=
6178 'create table '
6179 || l_table
6180 || ' tablespace '
6181 || g_op_table_space;
6182
6183 IF g_parallel IS NOT NULL
6184 THEN
6185 l_stmt := l_stmt
6186 || ' parallel (degree '
6187 || g_parallel
6188 || ') ';
6189 END IF;
6190
6191 l_stmt :=
6192 l_stmt
6193 || ' as select A.rowid row_id,B.dim_pk_date pk_date,A.key_value,A.number_key_value '
6194 || 'from '
6195 || g_dim_missing_keys_op
6196 || ' A,'
6197 || p_view
6198 || '@'
6199 || p_instance_link
6200 || ' B where '
6201 || 'A.parent_table_id='
6202 || p_dim_id
6203 || ' and A.instance='''
6204 || p_instance
6205 || ''' and B.dim_pk=A.key_value';
6206
6207 IF edw_owb_collection_util.drop_table (l_table) = FALSE
6208 THEN
6209 NULL;
6210 END IF;
6211
6212 IF g_debug
6213 THEN
6214 write_to_log_n ( 'Going to execute '
6215 || l_stmt
6216 || get_time);
6217 END IF;
6218
6219 EXECUTE IMMEDIATE l_stmt;
6220 l_count := SQL%ROWCOUNT;
6221
6222 IF g_debug
6223 THEN
6224 write_to_log_n (
6225 'Created '
6226 || l_table
6227 || ' with '
6228 || l_count
6229 || ' rows '
6230 || get_time
6231 );
6232 END IF;
6233
6234 l_stmt := 'select min(pk_date),max(pk_date) from '
6235 || l_table;
6236
6237 IF g_debug
6238 THEN
6239 write_to_log_n ( 'Going to execute '
6240 || l_stmt
6241 || get_time);
6242 END IF;
6243
6244 OPEN cv FOR l_stmt;
6245 FETCH cv INTO p_min_date, p_max_date;
6246 CLOSE cv;
6247
6248 IF g_debug
6249 THEN
6250 write_to_log (
6251 'Results '
6252 || TO_CHAR (p_min_date, 'MM/DD/YYYY HH24:MI:SS')
6253 || ' '
6254 || TO_CHAR (p_max_date, 'MM/DD/YYYY HH24:MI:SS')
6255 );
6256 END IF;
6257
6258 l_stmt :=
6259 'insert into edw_cdi_dim_missing_keys(dim_id,fact_id,instance,key_value,number_key_value,'
6260 || 'missing_date) select '
6261 || p_dim_id
6262 || ','
6263 || p_fact_id
6264 || ','''
6265 || p_instance
6266 || ''',a.key_value,a.number_key_value,'
6267 || 'b.day_pk_key from '
6268 || l_table
6269 || ' a,edw_time_day_ltc b where to_char(a.pk_date,''DD-MM-YYYY'')=b.day_pk';
6270
6271 IF g_debug
6272 THEN
6273 write_to_log_n ( 'Going to execute '
6274 || l_stmt
6278 EXECUTE IMMEDIATE l_stmt;
6275 || get_time);
6276 END IF;
6277
6279
6280 IF g_debug
6281 THEN
6282 write_to_log_n ( 'Inserted '
6283 || SQL%ROWCOUNT
6284 || ' rows '
6285 || get_time);
6286 END IF;
6287
6288 COMMIT;
6289 RETURN TRUE;
6290 EXCEPTION
6291 WHEN OTHERS
6292 THEN
6293 g_status_message := SQLERRM;
6294 write_to_log_n (g_status_message);
6295 RETURN FALSE;
6296 END find_missing_date_range;
6297
6298
6299 /*
6300 return:
6301 -1 error
6302 0 : bad key table not created
6303 1: all ok
6304 */
6305 FUNCTION create_bad_key_tables (
6306 p_fact IN VARCHAR2,
6307 p_fact_id IN NUMBER,
6308 p_dang_dim IN VARCHAR2,
6309 p_dang_dim_id IN NUMBER,
6310 p_dang_dim_instance_id IN edw_owb_collection_util.numbertabletype,
6311 p_dang_instances IN edw_owb_collection_util.varchartabletype,
6312 p_number_dang_instances IN NUMBER
6313 )
6314 RETURN NUMBER
6315 IS
6316 l_stmt VARCHAR2 (20000);
6317 l_tables edw_owb_collection_util.varchartabletype;
6318 l_number_tables NUMBER;
6319 l_op_table VARCHAR2 (200);
6320 l_rowid_table VARCHAR2 (200);
6321 l_bad_table VARCHAR2 (200);
6322 BEGIN
6323 IF g_debug
6324 THEN
6325 write_to_log_n (
6326 'In create_bad_key_tables fact='
6327 || p_fact
6328 || ',dim='
6329 || p_dang_dim
6330 || get_time
6331 );
6332 END IF;
6333
6334 l_number_tables := 0;
6335 l_op_table :=
6336 g_bis_owner
6337 || '.CBKTO_'
6338 || p_fact_id
6339 || '_'
6340 || p_dang_dim_id;
6341 l_rowid_table :=
6342 g_bis_owner
6343 || '.CBKTR_'
6344 || p_fact_id
6345 || '_'
6346 || p_dang_dim_id;
6347 l_bad_table :=
6348 g_bis_owner
6349 || '.B_NOINS_'
6350 || p_fact_id
6351 || '_'
6352 || p_dang_dim_id;
6353
6354 FOR i IN 1 .. p_number_dang_instances
6355 LOOP
6356 IF p_dang_dim_instance_id (i) = p_dang_dim_id
6357 THEN
6358 l_number_tables := l_number_tables
6359 + 1;
6360 l_tables (l_number_tables) := g_bis_owner
6361 || '.D_'
6362 || p_dang_instances (i)
6363 || '_'
6364 || p_fact_id
6365 || '_'
6366 || p_dang_dim_id;
6367
6368 IF edw_owb_collection_util.check_table (
6369 l_tables (l_number_tables)
6370 ) = FALSE
6371 THEN
6372 l_number_tables := l_number_tables
6373 - 1;
6374 END IF;
6375 --there are dependencies to this name
6376 END IF;
6377 END LOOP;
6378
6379 IF l_number_tables = 0
6380 THEN
6381 IF g_debug
6382 THEN
6383 write_to_log_n ('There are no tables to find bad keys');
6384 END IF;
6385
6386 RETURN 0;
6387 END IF;
6388
6389 l_stmt :=
6390 'create table '
6391 || l_op_table
6392 || ' tablespace '
6393 || g_op_table_space;
6394
6395 IF g_parallel IS NOT NULL
6396 THEN
6397 l_stmt := l_stmt
6398 || ' parallel (degree '
6399 || g_parallel
6400 || ') ';
6401 END IF;
6402
6403 l_stmt := l_stmt
6404 || ' as ';
6405
6406 FOR i IN 1 .. l_number_tables
6407 LOOP
6408 l_stmt :=
6409 l_stmt
6410 || 'select row_id from '
6411 || l_tables (i)
6412 || ' UNION ALL ';
6413 END LOOP;
6414
6415 l_stmt := SUBSTR (l_stmt, 1, LENGTH (l_stmt)
6416 - 10);
6417
6418 IF edw_owb_collection_util.drop_table (l_op_table) = FALSE
6419 THEN
6420 NULL;
6421 END IF;
6422
6423 IF g_debug
6424 THEN
6425 write_to_log_n ( 'Going to execute '
6426 || l_stmt
6427 || get_time);
6428 END IF;
6429
6430 EXECUTE IMMEDIATE l_stmt;
6431
6432 IF g_debug
6433 THEN
6434 write_to_log_n (
6435 'Created '
6436 || l_op_table
6437 || ' with '
6438 || SQL%ROWCOUNT
6439 || ' rows '
6440 || get_time
6441 );
6442 END IF;
6443
6444 l_stmt := 'create table '
6445 || l_rowid_table
6446 || ' tablespace '
6447 || g_op_table_space;
6448
6449 IF g_parallel IS NOT NULL
6450 THEN
6451 l_stmt := l_stmt
6452 || ' parallel (degree '
6453 || g_parallel
6454 || ') ';
6455 END IF;
6456
6457 l_stmt := l_stmt
6458 || ' as select rowid row_id from '
6459 || g_dim_missing_keys_op
6460 || ' where '
6461 || 'parent_table_id='
6462 || p_dang_dim_id
6463 || ' MINUS select row_id from '
6464 || l_op_table;
6465
6466 IF edw_owb_collection_util.drop_table (l_rowid_table) = FALSE
6467 THEN
6468 NULL;
6469 END IF;
6470
6471 IF g_debug
6472 THEN
6473 write_to_log_n ( 'Going to execute '
6474 || l_stmt
6475 || get_time);
6476 END IF;
6477
6478 EXECUTE IMMEDIATE l_stmt;
6479
6480 IF g_debug
6481 THEN
6482 write_to_log_n (
6483 'Created '
6484 || l_rowid_table
6485 || ' with '
6486 || SQL%ROWCOUNT
6487 || ' rows '
6488 || get_time
6489 );
6490 END IF;
6491
6492 IF edw_owb_collection_util.drop_table (l_op_table) = FALSE
6493 THEN
6494 NULL;
6495 END IF;
6496
6497 l_stmt :=
6498 'create table '
6499 || l_bad_table
6500 || ' tablespace '
6501 || g_op_table_space;
6502
6503 IF g_parallel IS NOT NULL
6504 THEN
6505 l_stmt := l_stmt
6506 || ' parallel (degree '
6507 || g_parallel
6508 || ') ';
6509 END IF;
6510
6511 l_stmt :=
6512 l_stmt
6513 || ' as select /*+ORDERED*/ A.key_value,A.instance from '
6514 || l_rowid_table
6515 || ' B, '
6516 || g_dim_missing_keys_op
6517 || ' A where B.row_id=A.rowid';
6518
6519 IF g_debug
6520 THEN
6521 write_to_log_n ( 'Going to execute '
6522 || l_stmt
6523 || get_time);
6524 END IF;
6525
6526 IF edw_owb_collection_util.drop_table (l_bad_table) = FALSE
6527 THEN
6528 NULL;
6529 END IF;
6530
6531 EXECUTE IMMEDIATE l_stmt;
6532
6533 IF g_debug
6534 THEN
6535 write_to_log_n (
6536 'Created '
6537 || l_bad_table
6538 || ' with '
6539 || SQL%ROWCOUNT
6540 || ' rows '
6541 || get_time
6542 );
6543 END IF;
6544
6545 IF edw_owb_collection_util.drop_table (l_rowid_table) = FALSE
6546 THEN
6547 NULL;
6548 END IF;
6549
6550 FOR i IN 1 .. l_number_tables
6551 LOOP
6552 IF edw_owb_collection_util.drop_table (l_tables (i)) = FALSE
6553 THEN
6554 NULL;
6555 END IF;
6556 END LOOP;
6557
6558 RETURN 1;
6559 EXCEPTION
6560 WHEN OTHERS
6561 THEN
6562 g_status_message := SQLERRM;
6563 write_to_log_n (g_status_message);
6564 RETURN -1;
6565 END create_bad_key_tables;
6566 END edw_check_data_integrity;