[Home] [Help]
PACKAGE BODY: APPS.FND_AUDIT_REPORT
Source
1 package body fnd_audit_report as
2 /* $Header: AFATRPTB.pls 120.3 2005/10/31 15:50:31 jwsmith noship $ */
3
4 --
5 -- Procedure
6 -- PRINT_OUTPUT
7 --
8 -- Purpose
9 -- Print to a concurrent manager log file or to dbms_output
10 --
11 -- Arguments:
12 -- IN:
13 -- LOG - send to cm log file if 'Y' and dbms_output if 'N'
14 -- DATA - string to print
15 --
16 procedure print_output( LOG IN VARCHAR2,
17 DATA IN VARCHAR2) is
18 begin
19
20 if log = 'Y' then
21 fnd_file.put_line(fnd_file.log,data);
22 else
23 /* For debugging purposes replace null with
24 dbms_output.put_line(data);
25 Call with audit_group_validation with log = 'N' as well.
26 */
27 null;
28 end if;
29
30 end print_output;
31 --
32 -- Procedure
33 -- AUDIT_GROUP_VALIDATION
34 --
35 -- Purpose
36 -- Simple PL/SQL stored procedure concurrent program which creates
37 -- an exception report for audit schema validation.
38 --
39 -- Arguments:
40 -- IN:
41 -- GROUP_NAME - name of the audit group
42 -- PROGNM - name of this program, written to logfile for tagging
43 -- LOG - send to cm logfile if 'Y' and dbms_output if 'N'
44 -- OUT:
45 -- ERRBUF - standard CP output
46 -- RETCODE - 0 if successful
47 --
48 procedure audit_group_validation(ERRBUF OUT NOCOPY VARCHAR2,
49 RETCODE OUT NOCOPY NUMBER,
50 GROUP_NAME IN VARCHAR2,
51 PROGNM IN VARCHAR2,
52 LOG IN VARCHAR2) is
53
54 p_apps_user VARCHAR2(30);
55 -- Local variables to use the fnd_installation.get_app_info
56 lv_status VARCHAR2(5);
57 lv_industry VARCHAR2(5);
58 lv_schema VARCHAR2(30);
59 lv_return BOOLEAN;
60
61 -- If the flag = 'N' then there are no rows found, the object is missing
62 -- If the flag = 'Y' then the object is present
63 flag0 varchar2(1) := 'N';
64 flag1 varchar2(1) := 'N';
65 flag2 varchar2(1) := 'N';
66 flag3 varchar2(1) := 'N';
67 flag4 varchar2(1) := 'N';
68 flag5 varchar2(1) := 'N';
69 flag6 varchar2(1) := 'N';
70 flag7 varchar2(1) := 'N';
71 flag8 varchar2(1) := 'N';
72 flag9 varchar2(1) := 'N';
73 cnt9 number := 0;
74 flag10 varchar2(1) := 'N';
75 flag11 varchar2(1) := 'N';
76 flag12 varchar2(1) := 'N';
77 flag13 varchar2(1) := 'N';
78 flag14 varchar2(1) := 'N';
79 flag15 varchar2(1) := 'N';
80 flag16 varchar2(1) := 'N';
81 flag17 varchar2(1) := 'N';
82
83 profvalue varchar2(255) := NULL;
84
85 cursor c0 (p_group_name varchar2) is
86 select g.group_name
87 from fnd_audit_groups g
88 where g.group_name = nvl(p_group_name, g.group_name);
89
90 cursor c1 (p_group_name varchar2) is
91 select g.group_name, b.table_name, g.audit_group_id, t.table_id, a.application_short_name
92 from fnd_audit_groups g, fnd_audit_tables t, fnd_tables b, fnd_application a
93 where g.audit_group_id = t.audit_group_id
94 and t.table_id = b.table_id
95 and g.group_name = p_group_name
96 and t.table_app_id = a.application_id;
97
98 cursor c2 (p_table_name varchar2, p_appl_short_name varchar2) is
99 select o.object_name, o.status
100 from dba_objects o
101 where object_name = p_table_name||'_A'
102 and object_type = 'TABLE'
103 and owner = p_appl_short_name;
104
105 cursor c3 (p_table_name varchar2, p_appl_short_name varchar2) is
106 select o.object_name, o.status
107 from dba_objects o
108 where object_name = p_table_name||'_AI'
109 and object_type = 'TRIGGER'
110 and owner = p_appl_short_name;
111
112 cursor c4 (p_table_name varchar2, p_appl_short_name varchar2) is
113 select o.object_name, o.status
114 from dba_objects o
115 where object_name = p_table_name||'_AU'
116 and object_type = 'TRIGGER'
117 and owner = p_appl_short_name;
118
119 cursor c5 (p_table_name varchar2, p_appl_short_name varchar2) is
120 select o.object_name, o.status
121 from dba_objects o
122 where object_name = p_table_name||'_AD'
123 and object_type = 'TRIGGER'
124 and owner = p_appl_short_name;
125
126 cursor c6 (p_table_name varchar2, p_appl_short_name varchar2) is
127 select o.object_name, o.status
128 from dba_objects o
129 where object_name = p_table_name||'_AIP'
130 and object_type = 'PROCEDURE'
131 and owner = p_appl_short_name;
132
133 cursor c7 (p_table_name varchar2, p_appl_short_name varchar2) is
134 select o.object_name, o.status
135 from dba_objects o
136 where object_name = p_table_name||'_AUP'
137 and object_type = 'PROCEDURE'
138 and owner = p_appl_short_name;
139
140 cursor c8 (p_table_name varchar2, p_appl_short_name varchar2) is
141 select o.object_name, o.status
142 from dba_objects o
143 where object_name = p_table_name||'_ADP'
144 and object_type = 'PROCEDURE'
145 and owner = p_appl_short_name;
146
147 cursor c9 (p_table_name varchar2, p_appl_short_name varchar2) is
148 select o.object_name, o.status
149 from dba_objects o
150 where object_name like p_table_name||'_AV%'
151 and object_type = 'VIEW'
152 and owner = p_appl_short_name;
153
154 cursor c10 (p_table_name varchar2, p_appl_short_name varchar2) is
155 select o.object_name, o.status
156 from dba_objects o
157 where object_name like p_table_name||'_AC%'
158 and object_type = 'VIEW'
159 and owner = p_appl_short_name;
160
161 cursor compare_cols (p_table_name varchar2, p_appl_short_name varchar2) is
162 select column_name
163 from dba_tab_columns c
164 where table_name = p_table_name||'_A'
165 and column_name not like 'AUDIT%'
166 and column_name not like 'ROW_KEY%'
167 and column_name not in (
168 select column_name
169 from dba_tab_columns
170 where table_name = p_table_name
171 and owner = p_appl_short_name)
172 and owner = p_appl_short_name;
173
174 cursor find_audit_groups
175 (p_group_name varchar2, p_table_name varchar2) is
176 select g.group_name, b.table_name,
177 decode(g.state,
178 'R','Enable Requested',
179 'N','Disable - Interrupt Audit',
180 'G','Disable - Prepare for Archive',
181 'D','Disable - Purge Table',
182 'E','Enabled',
183 g.state) state
184 from fnd_audit_groups g, fnd_audit_tables t , fnd_tables b
185 where t.audit_group_id = g.audit_group_id
186 and t.table_id = b.table_id
187 and t.table_id in (
188 select t2.table_id
189 from fnd_audit_tables t2
190 where t2.table_id = t.table_id
191 and t2.audit_group_id <> t.audit_group_id)
192 and g.group_name <> p_group_name
193 and b.table_name = p_table_name;
194
195 cursor find_apps_user is
196 select user from dual;
197
198 cursor c13 (p_table_name varchar2, p_appl_short_name varchar2) is
199 select o.object_name, o.status
200 from dba_objects o
201 where object_name = p_table_name
202 and object_type = 'TABLE'
203 and owner = p_appl_short_name;
204
205 cursor c14 (p_table_name varchar2, p_appl_short_name varchar2) is
206 select o.object_name, o.status
207 from dba_objects o
208 where object_name = p_table_name
209 and owner = p_appl_short_name
210 and object_type = 'SYNONYM';
211
212 cursor c15 (p_table_name varchar2, p_appl_short_name varchar2) is
213 select o.object_name, o.status
214 from dba_objects o
215 where object_name = p_table_name||'_A'
216 and owner = p_appl_short_name
217 and object_type = 'SYNONYM';
218
219 cursor c16 (p_audit_group_id number, p_table_id number) is
220 select u.oracle_username, decode(d.state,'R','Audit Enabled',
221 'X','Audit Disabled', d.state) state
222 from fnd_audit_tables t, sys.dba_tables a, fnd_tables b,
223 fnd_audit_schemas d, fnd_oracle_userid u
224 where u.oracle_id = d.oracle_id
225 and a.owner = u.oracle_username
226 and t.table_id = b.table_id
227 and d.oracle_id = u.oracle_id
228 and b.table_name=a.table_name
229 and t.table_id = p_table_id
230 and t.audit_group_id = p_audit_group_id;
231
232 cursor c17 (p_table_name varchar2) is
233 select count(*)+1 cnt
234 from fnd_audit_columns c,
235 fnd_tables b
236 where c.state = 'N'
237 and c.table_id = b.table_id
238 and c.schema_id <> -1
239 and b.table_name = p_table_name;
240
241 begin
242
243
244 flag1 := 'N';
245
246
247 profvalue := fnd_profile.value('AuditTrail:Activate');
248
249 print_output(log, 'Profile Option AuditTrail:Activate is ' || profvalue);
250
251 open find_apps_user;
252 fetch find_apps_user into p_apps_user;
253 close find_apps_user;
254
255 for c0_record in c0(group_name) loop
256
257 print_output(log, '------------------------------------------------------------------------');
258 print_output(log, 'Audit Group Name is ' || c0_record.group_name);
259 for c1_record in c1(c0_record.group_name) loop
260
261 flag2 := 'N';
262 flag3 := 'N';
263 flag4 := 'N';
264 flag5 := 'N';
265 flag6 := 'N';
266 flag7 := 'N';
267 flag8 := 'N';
268 flag9 := 'N';
269 cnt9 := 0;
270 flag10 := 'N';
271 flag11 := 'N';
272 flag12 := 'N';
273 flag13 := 'N';
274 flag14 := 'N';
275 flag15 := 'N';
276 flag16 := 'N';
277 flag17 := 'N';
278
279 print_output(log,
280 '-'|| ' Audit Group Table Name : ' || c1_record.table_name);
281
282 flag1 := 'Y';
283
284 lv_return := fnd_installation.get_app_info(c1_record.application_short_name,lv_status,lv_industry,lv_schema);
285
286 for c16_record in c16(c1_record.audit_group_id, c1_record.table_id) loop
287 print_output(log,
288 '-'|| ' Audit Group Oracle Username : '
289 || c16_record.oracle_username || ' is ' ||
290 c16_record.state);
291 flag16 := 'Y';
292 end loop;
293
294 if flag16 = 'N' then
295 print_output(log,
296 '-'|| ' Audit Group Oracle Username for : ' ||
297 c1_record.table_name || ' is not Audit Enabled ');
298 end if;
299
300 for c13_record in c13(c1_record.table_name, lv_schema) loop
301 print_output(log,
302 '-'|| ' Audit Group Table : '
303 || c13_record.object_name || ' is ' || c13_record.status);
304 flag13 := 'Y';
305 end loop;
306
307 if flag13 = 'N' then
308 print_output(log,
309 '-'|| ' Audit Group Table : ' ||
310 c1_record.table_name || ' is missing ');
311 end if;
312
313 for c14_record in c14(c1_record.table_name,p_apps_user) loop
314 print_output(log,
315 '-'|| ' Audit Group Table Synonym : '
316 || c14_record.object_name || ' is ' || c14_record.status);
317 flag14 := 'Y';
318 end loop;
319
320 if flag14 = 'N' then
321 print_output(log,
322 '-'|| ' Audit Group Table Synonym : ' ||
323 c1_record.table_name || ' is missing ');
324 end if;
325
326 for c2_record in c2(substr(c1_record.table_name,1,24), lv_schema) loop
327 print_output(log,
328 '-'|| ' Audit Group Shadow Table : '
329 || c2_record.object_name || ' is ' || c2_record.status);
330
331 flag2 := 'Y';
332 end loop;
333
334 if flag2 = 'N' then
335 print_output(log,
336 '-'|| ' Audit Group Shadow Table : ' ||
337 substr(c1_record.table_name,1,24) || '_A is missing ');
338 end if;
339
340 for c15_record in c15(substr(c1_record.table_name,1,24),p_apps_user) loop
341 print_output(log,
342 '-'|| ' Audit Group Shadow Table Synonym: '
343 || substr(c15_record.object_name,1,24) || '_A is ' || c15_record.status);
344
345 flag15 := 'Y';
346 end loop;
347
348 if flag15 = 'N' then
349 print_output(log,
350 '-'|| ' Audit Group Shadow Table Synonym: ' ||
351 substr(c1_record.table_name,1,24) || '_A is missing ');
352 end if;
353
354 for c3_record in c3(substr(c1_record.table_name,1,24),p_apps_user) loop
355 print_output(log,
356 '-'|| ' Audit Group Trigger : ' ||
357 c3_record.object_name || ' is ' || c3_record.status);
358 flag3 := 'Y';
359 end loop;
360
361 if flag3 = 'N' then
362 print_output(log,
363 '-'|| ' Audit Group Trigger : ' ||
364 substr(c1_record.table_name,1,24) || '_AI is missing ');
365 end if;
366
367 for c4_record in c4(substr(c1_record.table_name,1,24),p_apps_user) loop
368 print_output(log,
369 '-'|| ' Audit Group Trigger : '
370 || c4_record.object_name || ' is ' || c4_record.status);
371 flag4 := 'Y';
372 end loop;
373
374 if flag4 = 'N' then
375 print_output(log,
379
376 '-'|| ' Audit Group Trigger : ' ||
377 substr(c1_record.table_name,1,24) || '_AU is missing ');
378 end if ;
380 for c5_record in c5(substr(c1_record.table_name,1,24),p_apps_user) loop
381 print_output(log,
382 '-'|| ' Audit Group Trigger : ' ||
383 c5_record.object_name
384 || ' is ' || c5_record.status);
385 flag5 := 'Y';
386 end loop;
387
388 if flag5 = 'N' then
389 print_output(log,
390 '-'|| ' Audit Group Trigger : ' ||
391 substr(c1_record.table_name,1,24) || '_AD is missing ');
392 end if;
393
394 for c6_record in c6(substr(c1_record.table_name,1,24),p_apps_user) loop
395 print_output(log,
396 '-'|| ' Audit Group Procedure : '
397 || c6_record.object_name || ' is ' || c6_record.status);
398 flag6 := 'Y';
399 end loop;
400
401 if flag6 = 'N' then
402 print_output(log,
403 '-'|| ' Audit Group Procedure : ' ||
404 substr(c1_record.table_name,1,24) || '_AIP is missing ');
405 end if;
406
407 for c7_record in c7(substr(c1_record.table_name,1,24),p_apps_user) loop
408 print_output(log,
409 '-'|| ' Audit Group Procedure : '
410 || c7_record.object_name || ' is ' || c7_record.status);
411 flag7 := 'Y';
412 end loop;
413
414 if flag7 = 'N' then
415 print_output(log,
416 '-'|| ' Audit Group Procedure : ' ||
417 substr(c1_record.table_name,1,24) || '_AUP is missing ');
418 end if;
419
420 for c8_record in c8(substr(c1_record.table_name,1,24),p_apps_user) loop
421 print_output(log,
422 '-'|| ' Audit Group Procedure : ' ||
423 c8_record.object_name
424 || ' is ' || c8_record.status);
425 flag8 := 'Y';
426 end loop;
427
428 if flag8 = 'N' then
429 print_output(log,
430 '-'|| ' Audit Group Procedure : ' ||
431 substr(c1_record.table_name,1,24) || '_ADP is missing ');
432 end if;
433
434 for c9_record in c9(substr(c1_record.table_name,1,24),p_apps_user) loop
435 print_output(log,
436 '-'|| ' Audit Group View : ' ||
437 c9_record.object_name
438 || ' is ' || c9_record.status);
439 flag9 := 'Y';
440 cnt9 := cnt9 + 1;
441 end loop;
442
443 if flag9 = 'N' then
444 print_output(log,
445 '-'|| ' Audit Group View : All Views like ' ||
446 substr(c1_record.table_name,1,24) || '_AV% are missing ');
447 end if;
448
449 for c17_record in c17(substr(c1_record.table_name,1,24)) loop
450 print_output(log,
451 '-'|| ' Audit Group View AV% : ' || cnt9
452 || ' out of ' || c17_record.cnt ||
453 ' View(s) are present in the database.');
454 end loop;
455
456 for c10_record in c10(substr(c1_record.table_name,1,24),p_apps_user) loop
457 print_output(log,
458 '-'|| ' Audit Group View : ' ||
459 c10_record.object_name || ' is ' || c10_record.status);
460 flag10 := 'Y';
461 end loop;
462
463 if flag10 = 'N' then
464 print_output(log,
465 '-'|| ' Audit Group View (one view AC1) : ' ||
466 substr(c1_record.table_name,1,24) || ' is missing ');
467 end if;
468
469 /* If there is a missing Audit Table or Shadow Table then skip
470 this next comparison statement */
471 if flag2 = 'N' or flag13 = 'N' then
472 null;
473 else
474
475 for compare_cols_record in compare_cols (c1_record.table_name, lv_schema) loop
476 print_output(log,
477 '-'|| ' Audit Group Table ' || c1_record.table_name ||
478 ' is missing column present in shadow table ' ||
479 substr(c1_record.table_name,1,24) || '_A : ' ||
480 compare_cols_record.column_name );
481 flag11 := 'Y';
482 end loop;
483
484 if flag11 = 'N' then
485 print_output(log,
486 '-'|| ' Audit Group Table ' || c1_record.table_name ||
487 ' is not missing any columns present in shadow table ' ||
488 substr(c1_record.table_name,1,24) || '_A ' );
489 end if;
490 end if;
491
492 for find_audit_groups_record in find_audit_groups (c1_record.group_name,
493 c1_record.table_name) loop
494 print_output(log,
495 '-'|| ' Audit Group Table ' || c1_record.table_name ||
496 ' is also present in audit group ' ||
497 find_audit_groups_record.group_name || ' with a state of ' ||
498 find_audit_groups_record.state);
499 flag12 := 'Y';
500 end loop;
501
502 if flag12 = 'N' then
503 print_output(log,
504 '-'|| ' Audit Group Table ' || c1_record.table_name ||
505 ' is not present in any other Audit Groups ' );
506 end if;
507
508 end loop;
509 end loop;
510
511 if flag1 = 'N' then
512 print_output(log,
513 '-'|| 'Audit Group : ' ||
514 group_name || ' is missing ');
515 end if;
516
517 print_output('Exiting '||PROGNM);
518 retcode := 0;
519
520 exception
521 when others then
522 retcode := 2;
523 end audit_group_validation;
524
525
526 end fnd_audit_report;