1 PACKAGE hr_index_check AS
2 /*$Header: hrindchk.pkh 120.0 2005/05/31 00:51:36 appldev noship $*/
3 --
4 -- Types
5 TYPE IndRec IS RECORD
6 (index_name varchar2(30)
7 ,table_name varchar2(30)
8 );
9 TYPE IndList IS TABLE OF IndRec INDEX BY BINARY_INTEGER;
10 --
11 --
12 -- ----------------------------------------------------------------------------
13 -- |---------------------------< build_index_list >---------------------------|
14 -- ----------------------------------------------------------------------------
15 --
16 -- Description:
17 -- When called, this procedure will build up a list of indexes that exist
18 -- in the appropriate database, for the given product.
19 --
20 -- In Parameters:
21 -- p_prod - A 3-letter or 2-letter product code, which determines the
22 -- indexes added to the list.
23 --
24 -- Out Parameters:
25 -- p_list - A PL/SQL table making up a list of indexes for the particular
26 -- product.
27 --
28 -- ----------------------------------------------------------------------------
29 PROCEDURE build_index_list (p_prod IN varchar2
30 ,p_list IN OUT NOCOPY IndList);
31 --
32 -- ----------------------------------------------------------------------------
33 -- |-----------------------< find_missing_indexes >---------------------------|
34 -- ----------------------------------------------------------------------------
35 --
36 -- Description:
37 -- When called, this procedure will compare the list of indexes in the
38 -- current database, with the list of indexes built up from the SQL
39 -- script, and will generate a report identifying:
40 -- - an index which exists in CASE, but not on the current database
41 -- - an index which exists in the DB, but not within the CASE
42 -- definitions
43 --
44 -- In Parameters:
45 -- p_db_list - A list of indexes in the current database.
46 -- p_case_list - A list of indexes defined by HRMS.
47 --
48 -- ----------------------------------------------------------------------------
49 PROCEDURE find_missing_indexes
50 (p_db_list IN hr_index_check.IndList
51 ,p_case_list IN hr_index_check.IndList);
52 --
53 -- ----------------------------------------------------------------------------
54 -- |--------------------------< add_case_index >------------------------------|
55 -- ----------------------------------------------------------------------------
56 --
57 -- Description:
58 -- When called, this procedure will add a given index to a given list.
59 --
60 -- In Parameters:
61 -- p_case_list - A list of indexes.
62 -- p_index_name - An index to add to the database.
63 -- p_table - A table, upon which the index is defined.
64 -- p_index - The current number of indexes in the list.
65 --
66 -- Out Parameters:
67 -- p_index - The updated number of indexes in the list.
68 --
69 -- ----------------------------------------------------------------------------
70 PROCEDURE add_case_index
71 (p_case_list IN OUT NOCOPY hr_index_check.IndList
72 ,p_index_name IN varchar2
73 ,p_table IN varchar2
74 ,p_index IN OUT NOCOPY integer);
75 --
76 -- ----------------------------------------------------------------------------
77 -- |-----------------------< add_case_constraint >----------------------------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description:
81 -- When called, this procedure will add a given constraint to a given list.
82 --
83 -- ----------------------------------------------------------------------------
84 PROCEDURE add_case_constraint
85 (p_cons_list IN OUT NOCOPY hr_index_check.IndList
86 ,p_constraint_name IN varchar2
87 ,p_table IN varchar2
88 ,p_index IN OUT NOCOPY integer);
89 --
90 -- ----------------------------------------------------------------------------
91 -- |-------------------------< add_header >-----------------------------------|
92 -- ----------------------------------------------------------------------------
93 --
94 -- Adds a header to the results table, for the benefit of the final report.
95 --
96 -- ----------------------------------------------------------------------------
97 PROCEDURE add_header;
98 --
99 -- ----------------------------------------------------------------------------
100 -- |------------------------< add_fk_header >---------------------------------|
101 -- ----------------------------------------------------------------------------
102 PROCEDURE add_fk_header;
103 --
104 -- ----------------------------------------------------------------------------
105 -- |---------------------< build_fk_index_list >------------------------------|
106 -- ----------------------------------------------------------------------------
107 PROCEDURE build_fk_index_list(p_prod IN varchar2
108 ,p_list IN OUT NOCOPY hr_index_check.IndList );
109 --
110 -- ----------------------------------------------------------------------------
111 -- |--------------------< find_missing_cons_indexes >-------------------------|
112 -- ----------------------------------------------------------------------------
113 PROCEDURE find_missing_cons_indexes
114 (p_db_list IN hr_index_check.IndList
115 ,p_case_list IN hr_index_check.IndList);
116 --
117 -- ----------------------------------------------------------------------------
118 -- |-------------------------< add_footer >-----------------------------------|
119 -- ----------------------------------------------------------------------------
120 --
121 -- Adds a footer to the results table, for the benefit of the final report.
122 --
123 -- ----------------------------------------------------------------------------
124 PROCEDURE add_footer;
125 --
126 END hr_index_check;