1 PACKAGE BODY hr_index_check AS
2 /*$Header: hrindchk.pkb 115.2 2004/02/13 04:36:24 mroberts ship $*/
3 --
4 -- Package variables
5 g_number number default 0;
6 --
7 -- |--------------------------------------------------------------------------|
8 -- |------------------------------< get_schema >------------------------------|
9 -- |--------------------------------------------------------------------------|
10 FUNCTION get_schema(p_product IN VARCHAR2) RETURN VARCHAR2 IS
11 --
12 l_value BOOLEAN;
13 l_out_status VARCHAR2(30);
14 l_out_industry VARCHAR2(30);
15 l_out_oracle_schema VARCHAR2(30);
16
17 --
18 BEGIN
19 --
20
21 l_value := FND_INSTALLATION.GET_APP_INFO (p_product, l_out_status,
22 l_out_industry, l_out_oracle_schema);
23
24
25 RETURN(l_out_oracle_schema);
26
27 -- error handling
28 EXCEPTION
29 WHEN OTHERS THEN
30 RAISE;
31
32 END get_schema;
33 --
34 -- ----------------------------------------------------------------------------
35 -- |---------------------------< build_index_list >---------------------------|
36 -- ----------------------------------------------------------------------------
37 PROCEDURE build_index_list (p_prod IN varchar2
38 ,p_list IN OUT NOCOPY IndList) IS
39 --
40 cursor csr_find_ben_indexes (c_per_owner in varchar2,
41 c_ben_owner in varchar2) is
42 select table_name,
43 index_name
44 from all_indexes
45 where table_name like 'BEN_%'
46 and index_name not like 'SYS_%'
47 and ((owner = c_per_owner) or
48 (owner = c_ben_owner))
49 order by table_name, index_name;
50 --
51 cursor csr_find_per_indexes (c_per_owner in varchar2) is
52 select table_name,
53 index_name
54 from all_indexes
55 where ((table_name like 'PER_%') or
56 ((table_name like 'HR_%') and
57 (table_name not like 'HRI_%')))
58 and index_name not like 'SYS_%'
59 and owner = c_per_owner
60 order by table_name, index_name;
61 --
62 cursor csr_find_hri_indexes (c_owner in varchar2) is
63 select table_name,
64 index_name
65 from all_indexes
66 where ((table_name like 'HR_EDW%') or
67 (table_name like 'HRI_%'))
68 and index_name not like 'SYS_%'
69 and owner = c_owner
70 order by table_name, index_name;
71 --
72 cursor csr_find_indexes(c_prod in varchar2,
73 c_owner in varchar2) is
74 select table_name,
75 index_name
76 from all_indexes
77 where table_name like c_prod
78 and index_name not like 'SYS_%'
79 and owner = c_owner
80 order by table_name, index_name;
81 --
82 cursor csr_find_applications is
83 select application_id, application_short_name
84 from fnd_application
85 order by application_short_name;
86 --
87 l_list integer := 0;
88 l_owner varchar2(30);
89 l_per_owner varchar2(30);
90 l_prod varchar2(50);
91 --
92 BEGIN
93 --
94 -- Get PER schema now as it is used in multiple places
95 l_per_owner := get_schema('PER');
96 --
97 IF p_prod = 'PER' THEN
98 -- PER is a special case - involves HR and PER tables
99 FOR c1 IN csr_find_per_indexes(l_per_owner) LOOP
100 p_list(l_list).index_name := c1.index_name;
101 p_list(l_list).table_name := c1.table_name;
102 -- Increment list counter
103 l_list := l_list + 1;
104 END LOOP;
105 ELSIF p_prod = 'HRI' THEN
106 -- HRI is a special case as they deliver HR_EDW tables
107 l_owner := get_schema('HRI');
108 FOR c1 IN csr_find_hri_indexes(l_owner) LOOP
109 p_list(l_list).index_name := c1.index_name;
110 p_list(l_list).table_name := c1.table_name;
111 -- Increment list counter
112 l_list := l_list + 1;
113 END LOOP;
114 ELSIF p_prod = 'BEN' THEN
115 -- BEN is a special case as they deliver tables in both BEN and PER
116 -- schema
117 l_owner := get_schema('BEN');
118 FOR c1 IN csr_find_ben_indexes(l_per_owner, l_owner) LOOP
119 p_list(l_list).index_name := c1.index_name;
120 p_list(l_list).table_name := c1.table_name;
121 -- Increment list counter
122 l_list := l_list + 1;
123 END LOOP;
124 ELSIF p_prod = 'ALL' THEN
125 -- Find all products
126 FOR c1 in csr_find_applications LOOP
127 IF hr_general.chk_application_id(c1.application_id) = 'TRUE' THEN
128 IF c1.application_short_name = 'PER' THEN
129 FOR c1 IN csr_find_per_indexes(l_per_owner) LOOP
130 p_list(l_list).index_name := c1.index_name;
131 p_list(l_list).table_name := c1.table_name;
132 -- Increment list counter
133 l_list := l_list + 1;
134 END LOOP;
135 ELSIF c1.application_short_name = 'HRI' THEN
136 l_owner := get_schema('HRI');
137 FOR c1 IN csr_find_hri_indexes(l_owner) LOOP
138 p_list(l_list).index_name := c1.index_name;
139 p_list(l_list).table_name := c1.table_name;
140 -- Increment list counter
141 l_list := l_list + 1;
142 END LOOP;
143 ELSIF c1.application_short_name = 'BEN' THEN
144 l_owner := get_schema('BEN');
145 FOR c1 IN csr_find_ben_indexes(l_per_owner, l_owner) LOOP
146 p_list(l_list).index_name := c1.index_name;
147 p_list(l_list).table_name := c1.table_name;
148 -- Increment list counter
149 l_list := l_list + 1;
150 END LOOP;
151 ELSE
152 l_owner := get_schema(c1.application_short_name);
153 l_prod := c1.application_short_name || '_%';
154 FOR c1 IN csr_find_indexes(l_prod, l_owner) LOOP
155 p_list(l_list).index_name := c1.index_name;
156 p_list(l_list).table_name := c1.table_name;
157 -- Increment list counter
158 l_list := l_list + 1;
159 END LOOP;
160 END IF;
161 END IF;
162 END LOOP;
163 ELSE
164 l_owner := get_schema(p_prod);
165 l_prod := p_prod || '_%';
166 FOR c1 IN csr_find_indexes(l_prod, l_owner) LOOP
167 p_list(l_list).index_name := c1.index_name;
168 p_list(l_list).table_name := c1.table_name;
169 -- Increment list counter
170 l_list := l_list + 1;
171 END LOOP;
172 END IF;
173 --
174 p_list(l_list).index_name := null;
175 --
176 END build_index_list;
177 --
178 -- ---------------------------------------------------------------------------
179 -- |--------------------------< insert_line >--------------------------------|
180 -- ---------------------------------------------------------------------------
181 PROCEDURE insert_line(p_line IN varchar2) IS
182 BEGIN
183 insert into hr_api_user_hook_reports
184 (session_id,
185 line,
186 text)
187 values
188 (userenv('SESSIONID'),
189 g_number,
190 p_line);
191 -- Increment line counter
192 g_number := g_number + 1;
193 END insert_line;
194 --
195 -- ---------------------------------------------------------------------------
196 -- |---------------------------< insert_legend >-----------------------------|
197 -- ---------------------------------------------------------------------------
198 --
199 -- Description:
200 -- This procedure inserts the legend to the top of the report file.
201 --
202 -- ---------------------------------------------------------------------------
203 PROCEDURE insert_legend IS
204 l_line varchar2(80);
205 BEGIN
206 l_line := '***************************************************************************** ';
207 insert_line(l_line);
208 l_line := '* The following information is relevant to this report: * ';
209 insert_line(l_line);
210 l_line := '* * ';
211 insert_line(l_line);
212 l_line := '* CODE MEANING/ACTION * ';
213 insert_line(l_line);
214 l_line := '* ---------------------------------------------------------------------- * ';
215 insert_line(l_line);
216 l_line := '* 0 The corresponding index was not defined by Oracle HRMS, and * ';
217 insert_line(l_line);
218 l_line := '* may have been inserted by the customer, or created by the * ';
219 insert_line(l_line);
220 l_line := '* database as a result of creating a unique or primary key. * ';
221 insert_line(l_line);
222 l_line := '* 1 The corresponding index is one used by Oracle HRMS, but is not * ';
223 insert_line(l_line);
224 l_line := '* present on the current database. Check with your System * ';
225 insert_line(l_line);
226 l_line := '* Administrator that the index, and also any corresponding * ';
227 insert_line(l_line);
228 l_line := '* constraint, has not been accidentally removed. * ';
229 insert_line(l_line);
230 l_line := '* 2 Foreign Key constraints should have an associated non-unique * ';
231 insert_line(l_line);
232 l_line := '* index, but in these examples, the appropriate index has not been * ';
233 insert_line(l_line);
234 l_line := '* detected on the current database. Check with your System * ';
235 insert_line(l_line);
236 l_line := '* Administrator whether or not the index should exist. * ';
237 insert_line(l_line);
238 l_line := '* * ';
239 insert_line(l_line);
240 l_line := '***************************************************************************** ';
241 insert_line(l_line);
242 l_line := ' ';
243 insert_line(l_line);
244 END insert_legend;
245 --
246 -- ----------------------------------------------------------------------------
247 -- |-------------------------------< add_result >-----------------------------|
248 -- ----------------------------------------------------------------------------
249 --
250 -- Description:
251 -- Adds a result of the index checking to the results table, for later
252 -- output.
253 --
254 -- In Paremeters:
255 -- p_index - the name of the index.
256 -- p_table - the table upon which the index has been defined.
257 -- p_case - 'Y' if the index is defined in CASE, otherwise 'N'.
258 -- p_db - 'Y' if the index is defined in the Db, otherwise 'Y'.
259 --
260 -- ----------------------------------------------------------------------------
261 PROCEDURE add_result (p_index IN varchar2
262 ,p_table IN varchar2
263 ,p_case IN varchar2
264 ,p_db IN varchar2) IS
265 --
266 l_text varchar2(80);
267 l_number integer;
268 --
269 BEGIN
270 hr_utility.set_location('Adding to hr_api_user_hook_reports',99);
271 --
272 hr_utility.set_location('Adding for line: '||to_char(g_number),1);
273 --
274 IF p_index = 'HEADER' and p_table = 'HEADER' THEN
275 insert_legend;
276 l_text :=
277 'TABLE INDEX CODE STATUS';
278 --
279 -- Insert report header
280 insert into hr_api_user_hook_reports
281 (session_id,
282 line,
283 text)
284 values
285 (userenv('SESSIONID'),
286 g_number,
287 l_text);
288 -- Increment line counter
289 g_number := g_number + 1;
290 l_text :=
291 '-----------------------------------------------------------------------------';
292 insert into hr_api_user_hook_reports
293 (session_id,
294 line,
295 text)
296 values
297 (userenv('SESSIONID'),
298 g_number,
299 l_text);
300 -- Increment line counter
301 ELSIF p_index = 'FOOTER' and p_table = 'FOOTER' THEN
302 l_text :=
303 'End of Report ';
304 insert into hr_api_user_hook_reports
305 (session_id,
306 line,
307 text)
308 values
309 (userenv('SESSIONID'),
310 g_number,
311 l_text);
312 g_number := g_number + 1;
313 insert into hr_api_user_hook_reports
314 (session_id,
315 line,
316 text)
317 values
318 (userenv('SESSIONID'),
319 g_number,
320 ' ');
321 ELSE
322 l_text := rpad(p_table,30) || ' ' || rpad(p_index,30) || ' ';
323 IF p_case = 'N' THEN
324 l_text := l_text || '0 DB Only';
325 ELSIF p_case = '*' THEN
326 l_text := l_text || '2 Undefined';
327 END IF;
328 IF ((p_db = 'N') and (p_case <> '*')) THEN
329 l_text := l_text || '1 Not in DB';
330 END IF;
331 -- Insert values into results table
332 --
333 insert into hr_api_user_hook_reports
334 (session_id,
335 line,
336 text)
337 values
338 (userenv('SESSIONID'),
339 g_number,
340 l_text);
341 --
342 END IF;
343 g_number := g_number + 1;
344 end add_result;
345 --
346 -- ----------------------------------------------------------------------------
347 -- |------------------------< find_missing_indexes >--------------------------|
348 -- ----------------------------------------------------------------------------
349 PROCEDURE find_missing_indexes
350 (p_db_list IN hr_index_check.IndList
351 ,p_case_list IN hr_index_check.IndList
352 ) IS
353 --
354 l_db_index integer := 0;
355 l_case_index integer := 0;
356 BEGIN
357 --
358 WHILE ((p_db_list(l_db_index).index_name IS NOT NULL) or
359 (p_case_list(l_case_index).index_name IS NOT NULL)) LOOP
360 --
361 IF ((p_db_list(l_db_index).index_name
362 = p_case_list(l_case_index).index_name) and
363 (p_db_list(l_db_index).table_name
364 = p_case_list(l_case_index).table_name))THEN
365 -- Both indexes exist, advance both counters
366 l_db_index := l_db_index +1;
367 l_case_index := l_case_index +1;
368 ELSIF ((p_case_list(l_case_index).index_name IS NULL) and
369 (p_db_list(l_db_index).index_name IS NOT NULL)) or
370 ((p_db_list(l_db_index).table_name <
371 p_case_list(l_case_index).table_name)) THEN
372 -- Have found a db index that does not exist in CASE
373 -- because table does not exist in CASE ie. may be a development table
374 -- Advance counter
375 l_db_index := l_db_index + 1;
376 ELSIF ((p_db_list(l_db_index).index_name IS NULL) and
377 (p_case_list(l_case_index).index_name IS NOT NULL)) or
378 ((p_case_list(l_case_index).table_name =
379 p_db_list(l_db_index).table_name) and
380 (p_case_list(l_case_index).index_name <
381 p_db_list(l_db_index).index_name)) or
382 ((p_case_list(l_case_index).table_name <
383 p_db_list(l_db_index).table_name)) THEN
384 -- Have found a CASE index that does not exist in DB
385 add_result(p_index => p_case_list(l_case_index).index_name
386 ,p_table => p_case_list(l_case_index).table_name
387 ,p_case => 'Y'
388 ,p_db => 'N');
389 -- Advance counter
390 l_case_index := l_case_index + 1;
391 ELSIF ((p_case_list(l_case_index).table_name =
392 p_db_list(l_db_index).table_name) and
393 (p_db_list(l_db_index).index_name <
394 p_case_list(l_case_index).index_name)) THEN
395 -- Have found a db index that does not exist in CASE, but the
396 -- table does exist in CASE
397 add_result(p_index => p_db_list(l_db_index).index_name
398 ,p_table => p_db_list(l_db_index).table_name
399 ,p_case => 'N'
400 ,p_db => 'Y');
401 -- Advance counter
402 l_db_index := l_db_index + 1;
403 ELSE
404 add_result(p_index => 'Error...'
405 ,p_table => 'Error...'
406 ,p_case => 'X'
407 ,p_db => 'X');
408 END IF;
409 END LOOP;
410 --
411 EXCEPTION
412 WHEN NO_DATA_FOUND THEN
413 null;
414 END find_missing_indexes;
415 --
416 -- ----------------------------------------------------------------------------
417 -- |--------------------------< add_case_index >------------------------------|
418 -- ----------------------------------------------------------------------------
419 PROCEDURE add_case_index
420 (p_case_list IN OUT NOCOPY hr_index_check.IndList
421 ,p_index_name IN varchar2
422 ,p_table IN varchar2
423 ,p_index IN OUT NOCOPY integer) IS
424 --
425 BEGIN
426 p_case_list(p_index).index_name := p_index_name;
427 p_case_list(p_index).table_name := p_table;
428 p_index := p_index + 1;
429 p_case_list(p_index).index_name := null;
430 END add_case_index;
431 --
432 -- ----------------------------------------------------------------------------
433 -- |------------------------< add_case_constraint >---------------------------|
434 -- ----------------------------------------------------------------------------
435 PROCEDURE add_case_constraint
436 (p_cons_list IN OUT NOCOPY hr_index_check.IndList
437 ,p_constraint_name IN varchar2
438 ,p_table IN varchar2
439 ,p_index IN OUT NOCOPY integer) IS
440 --
441 BEGIN
442 p_cons_list(p_index).index_name := p_constraint_name;
443 p_cons_list(p_index).table_name := p_table;
444 p_index := p_index + 1;
445 p_cons_list(p_index).index_name := null;
446 END add_case_constraint;
447 --
448 -- ----------------------------------------------------------------------------
449 -- |---------------------------< add_header >---------------------------------|
450 -- ----------------------------------------------------------------------------
451 PROCEDURE add_header IS
452 BEGIN
453 -- Add column headers
454 --
455 add_result(p_index => 'HEADER'
456 ,p_table => 'HEADER'
457 ,p_case => 'Y'
458 ,p_db => 'Y');
459 --
460 END add_header;
461 --
462 -- ----------------------------------------------------------------------------
463 -- |--------------------------< add_fk_header >-------------------------------|
464 -- ----------------------------------------------------------------------------
465 PROCEDURE add_fk_header IS
466 --
467 l_proc varchar2(72) := 'hr_index_check.add_fk_header';
468 l_line varchar2(80);
469 --
470 BEGIN
471 hr_utility.set_location('Entering:'||l_proc,10);
472 --
473 l_line := rpad('TABLE',30) || ' ' || rpad('INDEX',30)
474 || ' ' || 'CODE STATUS';
475 --
476 insert into hr_api_user_hook_reports
477 (session_id,
478 line,
479 text)
480 values
481 (userenv('SESSIONID'),
482 g_number,
483 l_line);
484 -- Increment line counter
485 g_number := g_number + 1;
486 --
487 l_line := '-----------------------------------------------------------------------------';
488 --
489 insert into hr_api_user_hook_reports
490 (session_id,
491 line,
492 text)
493 values
494 (userenv('SESSIONID'),
495 g_number,
496 l_line);
497 -- Increment line counter
498 g_number := g_number + 1;
499 --
500 hr_utility.set_location('Leaving:'||l_proc,20);
501 END add_fk_header;
502 --
503 -- ----------------------------------------------------------------------------
504 -- |---------------------------< build_fk_index_list >------------------------|
505 -- ----------------------------------------------------------------------------
506 PROCEDURE build_fk_index_list(p_prod IN varchar2
507 ,p_list IN OUT NOCOPY hr_index_check.IndList) IS
508 --
509 l_proc varchar2(72) := 'hr_index_check.build_fk_index_list';
510 l_prod varchar2(50);
511 l_list integer := 0;
512 --
513 CURSOR csr_find_ben_indexes (c_per_owner in varchar2,
514 c_ben_owner in varchar2) IS
515 select table_name,
516 index_name
517 from all_indexes
518 where table_name like 'BEN_%'
519 and index_name not like 'SYS_%'
520 and index_name like '%FK%'
521 and ((owner = c_per_owner) or
522 (owner = c_ben_owner))
523 order by table_name, index_name;
524 --
525 CURSOR csr_find_per_indexes (c_per_owner in varchar2) IS
526 select table_name,
527 index_name
528 from all_indexes
529 where ((table_name like 'PER_%') or
530 ((table_name like 'HR_%') and
531 (table_name not like 'HRI_%')))
532 and index_name not like 'SYS_%'
533 and index_name like '%FK%'
534 and owner = c_per_owner
535 order by table_name, index_name;
536 --
537 CURSOR csr_find_hri_indexes (c_owner in varchar2) IS
538 select table_name,
539 index_name
540 from all_indexes
541 where ((table_name like 'HRI_%') or
542 (table_name like 'HRI_EDW%'))
543 and index_name not like 'SYS_%'
544 and index_name like '%FK%'
545 and owner = c_owner
546 order by table_name, index_name;
547 --
548 CURSOR csr_find_indexes(c_prod in varchar2,
549 c_owner in varchar2) IS
550 select table_name,
551 index_name
552 from all_indexes
553 where table_name like c_prod
554 and index_name not like 'SYS_%'
555 and index_name like '%FK%'
556 and owner = c_owner
557 order by table_name, index_name;
558 --
559 CURSOR csr_find_applications IS
560 select application_id, application_short_name
561 from fnd_application
562 order by application_short_name;
563 --
564 l_owner varchar2(30);
565 l_per_owner varchar2(30);
566 --
567 BEGIN
568 hr_utility.set_location('Entering:'||l_proc,10);
569 l_per_owner := get_schema('PER');
570 --
571 IF p_prod = 'PER' THEN
572 -- PER is a special case - involves HR and PER tables
573 FOR c1 IN csr_find_per_indexes(l_per_owner) LOOP
574 p_list(l_list).index_name := c1.index_name;
575 p_list(l_list).table_name := c1.table_name;
576 -- Increment list counter
577 l_list := l_list + 1;
578 END LOOP;
579 ELSIF p_prod = 'HRI' THEN
580 -- HRI is a special case as they deliver HR_EDW tables
581 l_owner := get_schema('HRI');
582 FOR c1 IN csr_find_hri_indexes(l_owner) LOOP
583 p_list(l_list).index_name := c1.index_name;
584 p_list(l_list).table_name := c1.table_name;
585 -- Increment list counter
586 l_list := l_list + 1;
587 END LOOP;
588 ELSIF p_prod = 'BEN' THEN
589 -- BEN is a special case as they deliver tables in both BEN and PER
590 -- schema
591 l_owner := get_schema('BEN');
592 FOR c1 IN csr_find_ben_indexes(l_per_owner, l_owner) LOOP
593 p_list(l_list).index_name := c1.index_name;
594 p_list(l_list).table_name := c1.table_name;
595 -- Increment list counter
596 l_list := l_list + 1;
597 END LOOP;
598 ELSIF p_prod = 'ALL' THEN
599 -- Find all products
600 FOR c1 in csr_find_applications LOOP
601 IF hr_general.chk_application_id(c1.application_id) = 'TRUE' THEN
602 IF c1.application_short_name = 'PER' THEN
603 FOR c1 IN csr_find_per_indexes(l_per_owner) LOOP
604 p_list(l_list).index_name := c1.index_name;
605 p_list(l_list).table_name := c1.table_name;
606 -- Increment list counter
607 l_list := l_list + 1;
608 END LOOP;
609 ELSIF c1.application_short_name = 'HRI' THEN
610 l_owner := get_schema('HRI');
611 FOR c1 IN csr_find_hri_indexes(l_owner) LOOP
612 p_list(l_list).index_name := c1.index_name;
613 p_list(l_list).table_name := c1.table_name;
614 -- Increment list counter
615 l_list := l_list + 1;
616 END LOOP;
617 ELSIF c1.application_short_name = 'BEN' THEN
618 l_owner := get_schema('BEN');
619 FOR c1 IN csr_find_ben_indexes(l_per_owner, l_owner) LOOP
620 p_list(l_list).index_name := c1.index_name;
621 p_list(l_list).table_name := c1.table_name;
622 -- Increment list counter
623 l_list := l_list + 1;
624 END LOOP;
625 ELSE
626 l_owner := get_schema(c1.application_short_name);
627 l_prod := c1.application_short_name || '_%';
628 FOR c1 IN csr_find_indexes(l_prod, l_owner) LOOP
629 p_list(l_list).index_name := c1.index_name;
630 p_list(l_list).table_name := c1.table_name;
631 -- Increment list counter
632 l_list := l_list + 1;
633 END LOOP;
634 END IF;
635 END IF;
636 END LOOP;
637 ELSE
638 l_owner := get_schema(p_prod);
639 l_prod := p_prod || '_%';
640 FOR c1 IN csr_find_indexes(l_prod, l_owner) LOOP
641 p_list(l_list).index_name := c1.index_name;
642 p_list(l_list).table_name := c1.table_name;
643 -- Increment list counter
644 l_list := l_list + 1;
645 END LOOP;
646 END IF;
647 --
648 p_list(l_list).index_name := null;
649 --
650 hr_utility.set_location('Leaving:'||l_proc,20);
651 END build_fk_index_list;
652 --
653 -- ----------------------------------------------------------------------------
654 -- |----------------------< find_missing_cons_indexes >-----------------------|
655 -- ----------------------------------------------------------------------------
656 PROCEDURE find_missing_cons_indexes
657 (p_db_list IN hr_index_check.IndList
658 ,p_case_list IN hr_index_check.IndList
659 ) IS
660 --
661 l_proc varchar2(72) := 'hr_index_check.find_missing_cons_indexes';
662 l_db_index integer := 0;
663 l_case_index integer := 0;
664 --
665 BEGIN
666 hr_utility.set_location('Entering:'||l_proc,10);
667 --
668 WHILE ((p_db_list(l_db_index).index_name IS NOT NULL) or
669 (p_case_list(l_case_index).index_name IS NOT NULL)) LOOP
670 --
671 IF ((p_db_list(l_db_index).index_name
672 = p_case_list(l_case_index).index_name) and
673 (p_db_list(l_db_index).table_name
674 = p_case_list(l_case_index).table_name))THEN
675 -- Both indexes exist, advance both counters
676 l_db_index := l_db_index +1;
677 l_case_index := l_case_index +1;
678 ELSIF ((p_case_list(l_case_index).index_name IS NULL) and
679 (p_db_list(l_db_index).index_name IS NOT NULL)) or
680 ((p_db_list(l_db_index).table_name <
681 p_case_list(l_case_index).table_name)) THEN
682 -- Have found a db index that does not exist in CASE
683 -- because table does not exist in CASE ie. may be a development table
684 -- Advance counter
685 l_db_index := l_db_index + 1;
686 ELSIF ((p_db_list(l_db_index).index_name IS NULL) and
687 (p_case_list(l_case_index).index_name IS NOT NULL)) or
688 ((p_case_list(l_case_index).table_name =
689 p_db_list(l_db_index).table_name) and
690 (p_case_list(l_case_index).index_name <
691 p_db_list(l_db_index).index_name)) or
692 ((p_case_list(l_case_index).table_name <
693 p_db_list(l_db_index).table_name)) THEN
694 -- Have found a CASE index that does not exist in DB
695 add_result(p_index => p_case_list(l_case_index).index_name
696 ,p_table => p_case_list(l_case_index).table_name
697 ,p_case => '*'
698 ,p_db => 'N');
699 -- Advance counter
700 l_case_index := l_case_index + 1;
701 ELSIF ((p_case_list(l_case_index).table_name =
702 p_db_list(l_db_index).table_name) and
703 (p_db_list(l_db_index).index_name <
704 p_case_list(l_case_index).index_name)) THEN
705 -- Have found a db index that does not exist in CASE, but the
706 -- table does exist in CASE
707 -- For this type, we ignore...
708 l_db_index := l_db_index + 1;
709 ELSE
710 add_result(p_index => 'Error...'
711 ,p_table => 'Error...'
712 ,p_case => 'X'
713 ,p_db => 'X');
714 END IF;
715 END LOOP;
716 --
717 EXCEPTION
718 WHEN NO_DATA_FOUND THEN
719 null;
720 END find_missing_cons_indexes;
721 --
722 -- ----------------------------------------------------------------------------
723 -- |-------------------------------< add_footer >-----------------------------|
724 -- ----------------------------------------------------------------------------
725 PROCEDURE add_footer IS
726 BEGIN
727 -- Add column headers
728 --
729 add_result(p_index => 'FOOTER'
730 ,p_table => 'FOOTER'
731 ,p_case => 'Y'
732 ,p_db => 'Y');
733 --
734 END add_footer;
735 --
736 END hr_index_check;
[Home] [Help]