[Home] [Help]
PACKAGE BODY: APPS.HR_CONSTRAINT_CHECK
Source
1 PACKAGE BODY hr_constraint_check AS
2 /*$Header: hrconchk.pkb 115.3 2004/02/13 04:37:54 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_constraint_list >-------------------------|
36 -- ----------------------------------------------------------------------------
37 PROCEDURE build_constraint_list (p_prod IN varchar2
38 ,p_list IN OUT NOCOPY ConsList) IS
39 --
40 cursor csr_find_ben_constraints (c_per_owner in varchar2,
41 c_ben_owner in varchar2) is
42 select table_name,
43 constraint_name
44 from all_constraints
45 where table_name like 'BEN_%'
46 and constraint_name not like 'SYS_%'
47 and ((owner = c_per_owner) or
48 (owner = c_ben_owner))
49 order by table_name, constraint_name;
50 --
51 cursor csr_find_per_constraints (c_per_owner in varchar2) is
52 select table_name,
53 constraint_name
54 from all_constraints
55 where ((table_name like 'PER_%') or
56 ((table_name like 'HR_%') and
57 (table_name not like 'HRI_%')))
58 and constraint_name not like 'SYS_%'
59 and owner = c_per_owner
60 order by table_name, constraint_name;
61 --
62 cursor csr_find_hri_constraints (c_owner in varchar2) is
63 select table_name,
64 constraint_name
65 from all_constraints
66 where ((table_name like 'HR_EDW%') or
67 (table_name like 'HRI_%'))
68 and constraint_name not like 'SYS_%'
69 and owner = c_owner
70 order by table_name, constraint_name;
71 --
72 cursor csr_find_constraints(c_prod in varchar2,
73 c_owner in varchar2) is
74 select table_name,
75 constraint_name
76 from all_constraints
77 where table_name like c_prod
78 and constraint_name not like 'SYS_%'
79 and owner = c_owner
80 order by table_name, constraint_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_constraints(l_per_owner) LOOP
100 p_list(l_list).constraint_name := c1.constraint_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_constraints(l_owner) LOOP
109 p_list(l_list).constraint_name := c1.constraint_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_constraints(l_per_owner, l_owner) LOOP
119 p_list(l_list).constraint_name := c1.constraint_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_constraints(l_per_owner) LOOP
130 p_list(l_list).constraint_name := c1.constraint_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_constraints(l_owner) LOOP
138 p_list(l_list).constraint_name := c1.constraint_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_constraints(l_per_owner,l_owner) LOOP
146 p_list(l_list).constraint_name := c1.constraint_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_constraints(l_prod, l_owner) LOOP
155 p_list(l_list).constraint_name := c1.constraint_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_constraints(l_prod, l_owner) LOOP
167 p_list(l_list).constraint_name := c1.constraint_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).constraint_name := null;
175 --
176 END build_constraint_list;
177 --
178 -- ----------------------------------------------------------------------------
179 -- |-------------------------------< add_result >-----------------------------|
180 -- ----------------------------------------------------------------------------
181 --
182 -- Description:
183 -- Adds a result of the constraint checking to the results table, for later
184 -- output.
185 --
186 -- In Paremeters:
187 -- p_constraint - the name of the constraint.
188 -- p_table - the table upon which the constraint has been defined.
189 -- p_case - 'Y' if the constraint is defined in CASE, otherwise 'N'.
190 -- p_db - 'Y' if the constraint is defined in the Db, otherwise 'Y'.
191 --
192 -- ----------------------------------------------------------------------------
193 PROCEDURE add_result (p_constraint IN varchar2
194 ,p_table IN varchar2
195 ,p_case IN varchar2
196 ,p_db IN varchar2) IS
197 --
198 l_text varchar2(80);
199 l_number integer;
200 --
201 BEGIN
202 hr_utility.set_location('Adding to hr_api_user_hook_reports',99);
203 --
204 hr_utility.set_location('Adding for line: '||to_char(g_number),1);
205 --
206 IF p_constraint = 'HEADER' and p_table = 'HEADER' THEN
207 l_text :=
208 'TABLE CONSTRAINT CODE STATUS';
209 --
210 -- Insert report header
211 insert into hr_api_user_hook_reports
212 (session_id,
213 line,
214 text)
215 values
216 (userenv('SESSIONID'),
217 g_number,
218 l_text);
219 -- Increment line counter
220 g_number := g_number + 1;
221 l_text :=
222 '-----------------------------------------------------------------------------';
223 insert into hr_api_user_hook_reports
224 (session_id,
225 line,
226 text)
227 values
228 (userenv('SESSIONID'),
229 g_number,
230 l_text);
231 -- Increment line counter
232 ELSIF p_constraint = 'FOOTER' and p_table = 'FOOTER' THEN
233 l_text :=
234 'End of Report ';
235 insert into hr_api_user_hook_reports
236 (session_id,
237 line,
238 text)
239 values
240 (userenv('SESSIONID'),
241 g_number,
242 l_text);
243 g_number := g_number + 1;
244 insert into hr_api_user_hook_reports
245 (session_id,
246 line,
247 text)
248 values
249 (userenv('SESSIONID'),
250 g_number+1,
251 ' ');
252 ELSE
253 l_text := rpad(p_table,30) || ' ' || rpad(p_constraint,30) || ' ';
254 IF p_case = 'N' THEN
255 l_text := l_text || '0 DB Only';
256 END IF;
257 IF p_db = 'N' THEN
258 l_text := l_text || '1 Not in DB';
259 END IF;
260 -- Insert values into results table
261 --
262 insert into hr_api_user_hook_reports
263 (session_id,
264 line,
265 text)
266 values
267 (userenv('SESSIONID'),
268 g_number,
269 l_text);
270 --
271 END IF;
272 g_number := g_number + 1;
273 end add_result;
274 --
275 -- ----------------------------------------------------------------------------
276 -- |-----------------------< find_missing_constraints >-----------------------|
277 -- ----------------------------------------------------------------------------
278 PROCEDURE find_missing_constraints
279 (p_db_list IN hr_constraint_check.ConsList
280 ,p_case_list IN hr_constraint_check.ConsList
281 ) IS
282 --
283 l_db_index integer := 0;
284 l_case_index integer := 0;
285 BEGIN
286 --
287 WHILE ((p_db_list(l_db_index).constraint_name IS NOT NULL) or
288 (p_case_list(l_case_index).constraint_name IS NOT NULL)) LOOP
289 --
290 IF ((p_db_list(l_db_index).constraint_name
291 = p_case_list(l_case_index).constraint_name) and
292 (p_db_list(l_db_index).table_name
293 = p_case_list(l_case_index).table_name))THEN
294 -- Both constraints exist, advance both counters
295 l_db_index := l_db_index +1;
296 l_case_index := l_case_index +1;
297 ELSIF ((p_case_list(l_case_index).constraint_name IS NULL) and
298 (p_db_list(l_db_index).constraint_name IS NOT NULL)) or
299 ((p_db_list(l_db_index).table_name <
300 p_case_list(l_case_index).table_name)) THEN
301 -- Have found a db constraint that does not exist in CASE
302 -- because table does not exist in CASE ie. may be a development table
303 -- Advance counter
304 l_db_index := l_db_index + 1;
305 ELSIF ((p_db_list(l_db_index).constraint_name IS NULL) and
306 (p_case_list(l_case_index).constraint_name IS NOT NULL)) or
307 ((p_case_list(l_case_index).table_name =
308 p_db_list(l_db_index).table_name) and
309 (p_case_list(l_case_index).constraint_name <
310 p_db_list(l_db_index).constraint_name)) or
311 ((p_case_list(l_case_index).table_name <
312 p_db_list(l_db_index).table_name)) THEN
313 -- Have found a CASE constraint that does not exist in DB
314 add_result(p_constraint => p_case_list(l_case_index).constraint_name
315 ,p_table => p_case_list(l_case_index).table_name
316 ,p_case => 'Y'
317 ,p_db => 'N');
318 -- Advance counter
319 l_case_index := l_case_index + 1;
320 ELSIF ((p_case_list(l_case_index).table_name =
321 p_db_list(l_db_index).table_name) and
322 (p_db_list(l_db_index).constraint_name <
323 p_case_list(l_case_index).constraint_name)) THEN
324 -- Have found a db constraint that does not exist in CASE, but the
325 -- table does exist in CASE
326 add_result(p_constraint => p_db_list(l_db_index).constraint_name
327 ,p_table => p_db_list(l_db_index).table_name
328 ,p_case => 'N'
329 ,p_db => 'Y');
330 -- Advance counter
331 l_db_index := l_db_index + 1;
332 ELSE
333 add_result(p_constraint => 'Error...'
334 ,p_table => 'Error...'
335 ,p_case => 'X'
336 ,p_db => 'X');
337 END IF;
338 END LOOP;
339 --
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 null;
343 END find_missing_constraints;
344 --
345 -- ----------------------------------------------------------------------------
346 -- |-----------------------< add_case_constraint >----------------------------|
347 -- ----------------------------------------------------------------------------
348 PROCEDURE add_case_constraint
349 (p_case_list IN OUT NOCOPY hr_constraint_check.ConsList
350 ,p_constraint IN varchar2
351 ,p_table IN varchar2
352 ,p_index IN OUT NOCOPY integer) IS
353 --
354 BEGIN
355 p_case_list(p_index).constraint_name := p_constraint;
356 p_case_list(p_index).table_name := p_table;
357 p_index := p_index + 1;
358 p_case_list(p_index).constraint_name := null;
359 END add_case_constraint;
360 --
361 -- ----------------------------------------------------------------------------
362 -- |---------------------------< add_header >---------------------------------|
363 -- ----------------------------------------------------------------------------
364 PROCEDURE add_header IS
365 BEGIN
366 -- Add column headers
367 --
368 add_result(p_constraint => 'HEADER'
369 ,p_table => 'HEADER'
370 ,p_case => 'Y'
371 ,p_db => 'Y');
372 --
373 END add_header;
374 --
375 -- ----------------------------------------------------------------------------
376 -- |---------------------------< add_footer >---------------------------------|
377 -- ----------------------------------------------------------------------------
378 PROCEDURE add_footer IS
379 BEGIN
380 -- Add column headers
381 --
382 add_result(p_constraint => 'FOOTER'
383 ,p_table => 'FOOTER'
384 ,p_case => 'Y'
385 ,p_db => 'Y');
386 --
387 END add_footer;
388 --
389 END hr_constraint_check;