[Home] [Help]
PACKAGE BODY: APPS.FND_AUDIT_REPORT
Source
1 package body fnd_audit_report as
2 /* $Header: AFATRPTB.pls 120.3.12020000.4 2013/03/05 17:32:03 jwsmith ship $ */
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 and b.application_id = a.application_id;
98
99 cursor c2 (p_table_name varchar2, p_appl_short_name varchar2) is
100 select o.object_name, o.status
101 from dba_objects o
102 where object_name = p_table_name||'_A'
103 and object_type = 'TABLE'
104 and owner = p_appl_short_name;
105
106 cursor c3 (p_table_name varchar2, p_appl_short_name varchar2) is
107 select o.object_name, o.status
108 from dba_objects o
109 where object_name = p_table_name||'_AI'
110 and object_type = 'TRIGGER'
111 and owner = p_appl_short_name;
112
113 cursor c4 (p_table_name varchar2, p_appl_short_name varchar2) is
114 select o.object_name, o.status
115 from dba_objects o
116 where object_name = p_table_name||'_AU'
117 and object_type = 'TRIGGER'
118 and owner = p_appl_short_name;
119
120 cursor c5 (p_table_name varchar2, p_appl_short_name varchar2) is
121 select o.object_name, o.status
122 from dba_objects o
123 where object_name = p_table_name||'_AD'
124 and object_type = 'TRIGGER'
125 and owner = p_appl_short_name;
126
127 cursor c6 (p_table_name varchar2, p_appl_short_name varchar2) is
128 select o.object_name, o.status
129 from dba_objects o
130 where object_name = p_table_name||'_AIP'
131 and object_type = 'PROCEDURE'
132 and owner = p_appl_short_name;
133
134 cursor c7 (p_table_name varchar2, p_appl_short_name varchar2) is
135 select o.object_name, o.status
136 from dba_objects o
137 where object_name = p_table_name||'_AUP'
138 and object_type = 'PROCEDURE'
139 and owner = p_appl_short_name;
140
141 cursor c8 (p_table_name varchar2, p_appl_short_name varchar2) is
142 select o.object_name, o.status
143 from dba_objects o
144 where object_name = p_table_name||'_ADP'
145 and object_type = 'PROCEDURE'
146 and owner = p_appl_short_name;
147
148 cursor c9 (p_table_name varchar2, p_appl_short_name varchar2) is
149 select o.object_name, o.status
150 from dba_objects o
151 where object_name like p_table_name||'_AV%'
152 and object_type = 'VIEW'
153 and owner = p_appl_short_name;
154
155 cursor c10 (p_table_name varchar2, p_appl_short_name varchar2) is
156 select o.object_name, o.status
157 from dba_objects o
158 where object_name like p_table_name||'_AC%'
159 and object_type = 'VIEW'
160 and owner = p_appl_short_name;
161
162 cursor compare_cols (p_table_name varchar2, p_appl_short_name varchar2) is
163 select column_name
164 from dba_tab_columns c
165 where table_name = p_table_name||'_A'
166 and column_name not like 'AUDIT%'
167 and column_name not like 'ROW_KEY%'
168 and column_name not in (
169 select col.column_name
170 from user_synonyms syn, all_tab_columns col
171 where syn.synonym_name = p_table_name
172 and col.owner = syn.table_owner
173 and col.table_name = syn.table_name
174 and col.owner = p_appl_short_name)
175 and owner = p_appl_short_name;
176
177 cursor find_audit_groups
178 (p_group_name varchar2, p_table_name varchar2) is
179 select g.group_name, b.table_name,
180 decode(g.state,
181 'R','Enable Requested',
182 'N','Disable - Interrupt Audit',
183 'G','Disable - Prepare for Archive',
184 'D','Disable - Purge Table',
185 'E','Enabled',
186 g.state) state
187 from fnd_audit_groups g, fnd_audit_tables t , fnd_tables b
188 where t.audit_group_id = g.audit_group_id
189 and t.table_id = b.table_id
190 and t.table_id in (
191 select t2.table_id
192 from fnd_audit_tables t2
193 where t2.table_id = t.table_id
194 and t2.audit_group_id <> t.audit_group_id)
195 and g.group_name <> p_group_name
196 and b.table_name = p_table_name;
197
198 cursor find_apps_user is
199 select user from dual;
200
201 cursor c13 (p_table_name varchar2, p_appl_short_name varchar2) is
202 select o.object_name, o.status
203 from dba_objects o
204 where object_name = p_table_name
205 and object_type = 'TABLE'
206 and owner = p_appl_short_name;
207
208 cursor c14 (p_table_name varchar2, p_appl_short_name varchar2) is
209 select o.object_name, o.status
210 from dba_objects o
211 where object_name = p_table_name
212 and owner = p_appl_short_name
213 and object_type = 'SYNONYM';
214
215 cursor c15 (p_table_name varchar2, p_appl_short_name varchar2) is
216 select o.object_name, o.status
217 from dba_objects o
218 where object_name = p_table_name||'_A'
219 and owner = p_appl_short_name
220 and object_type = 'SYNONYM';
221
222 cursor c16 (p_audit_group_id number, p_table_id number) is
223 select u.oracle_username, decode(d.state,'R','Audit Enabled',
224 'X','Audit Disabled', d.state) state
225 from fnd_audit_tables t, sys.dba_tables a, fnd_tables b,
226 fnd_audit_schemas d, fnd_oracle_userid u
227 where u.oracle_id = d.oracle_id
228 and a.owner = u.oracle_username
229 and t.table_id = b.table_id
230 and d.oracle_id = u.oracle_id
231 and b.table_name=a.table_name
232 and t.table_id = p_table_id
233 and t.audit_group_id = p_audit_group_id;
234
235 cursor c17 (p_table_name varchar2) is
236 select count(*)+1 cnt
237 from fnd_audit_columns c,
238 fnd_tables b
239 where c.state = 'N'
240 and c.table_id = b.table_id
241 and c.schema_id <> -1
242 and b.table_name = p_table_name;
243
244 begin
245
246
247 flag1 := 'N';
248
249
250 profvalue := fnd_profile.value('AuditTrail:Activate');
251
252 print_output(log, 'Profile Option AuditTrail:Activate is ' || profvalue);
253
254 open find_apps_user;
255 fetch find_apps_user into p_apps_user;
256 close find_apps_user;
257
258 for c0_record in c0(group_name) loop
259
260 print_output(log, '------------------------------------------------------------------------');
261 print_output(log, 'Audit Group Name is ' || c0_record.group_name);
262 for c1_record in c1(c0_record.group_name) loop
263
264 flag2 := 'N';
265 flag3 := 'N';
266 flag4 := 'N';
267 flag5 := 'N';
268 flag6 := 'N';
269 flag7 := 'N';
270 flag8 := 'N';
271 flag9 := 'N';
272 cnt9 := 0;
273 flag10 := 'N';
274 flag11 := 'N';
275 flag12 := 'N';
276 flag13 := 'N';
277 flag14 := 'N';
278 flag15 := 'N';
279 flag16 := 'N';
280 flag17 := 'N';
281
282 print_output(log,
283 '-'|| ' Audit Group Table Name : ' || c1_record.table_name);
284
285 flag1 := 'Y';
286
287 lv_return := fnd_installation.get_app_info(c1_record.application_short_name,lv_status,lv_industry,lv_schema);
288
289 for c16_record in c16(c1_record.audit_group_id, c1_record.table_id) loop
290 print_output(log,
291 '-'|| ' Audit Group Oracle Username : '
292 || c16_record.oracle_username || ' is ' ||
293 c16_record.state);
294 flag16 := 'Y';
295 end loop;
296
297 if flag16 = 'N' then
298 print_output(log,
299 '-'|| ' Audit Group Oracle Username for : ' ||
300 c1_record.table_name || ' is not Audit Enabled ');
301 end if;
302
303 for c13_record in c13(c1_record.table_name, lv_schema) loop
304 print_output(log,
305 '-'|| ' Audit Group Table : '
306 || c13_record.object_name || ' is ' || c13_record.status);
307 flag13 := 'Y';
308 end loop;
309
310 if flag13 = 'N' then
311 print_output(log,
312 '-'|| ' Audit Group Table : ' ||
313 c1_record.table_name || ' is missing ');
314 end if;
315
316 for c14_record in c14(c1_record.table_name,p_apps_user) loop
317 print_output(log,
318 '-'|| ' Audit Group Table Synonym : '
319 || c14_record.object_name || ' is ' || c14_record.status);
320 flag14 := 'Y';
321 end loop;
322
323 if flag14 = 'N' then
324 print_output(log,
325 '-'|| ' Audit Group Table Synonym : ' ||
326 c1_record.table_name || ' is missing ');
327 end if;
328
329 for c2_record in c2(substr(c1_record.table_name,1,24), lv_schema) loop
330 print_output(log,
331 '-'|| ' Audit Group Shadow Table : '
332 || c2_record.object_name || ' is ' || c2_record.status);
333
334 flag2 := 'Y';
335 end loop;
336
337 if flag2 = 'N' then
338 print_output(log,
339 '-'|| ' Audit Group Shadow Table : ' ||
340 substr(c1_record.table_name,1,24) || '_A is missing ');
341 end if;
342
343 for c15_record in c15(substr(c1_record.table_name,1,24),p_apps_user) loop
344 print_output(log,
345 '-'|| ' Audit Group Shadow Table Synonym: '
346 || substr(c15_record.object_name,1,24) || '_A is ' || c15_record.status);
347
348 flag15 := 'Y';
349 end loop;
350
351 if flag15 = 'N' then
352 print_output(log,
353 '-'|| ' Audit Group Shadow Table Synonym: ' ||
354 substr(c1_record.table_name,1,24) || '_A is missing ');
355 end if;
356
357 for c3_record in c3(substr(c1_record.table_name,1,24),p_apps_user) loop
358 print_output(log,
359 '-'|| ' Audit Group Trigger : ' ||
360 c3_record.object_name || ' is ' || c3_record.status);
361 flag3 := 'Y';
362 end loop;
363
364 if flag3 = 'N' then
365 print_output(log,
366 '-'|| ' Audit Group Trigger : ' ||
367 substr(c1_record.table_name,1,24) || '_AI is missing ');
368 end if;
369
370 for c4_record in c4(substr(c1_record.table_name,1,24),p_apps_user) loop
371 print_output(log,
372 '-'|| ' Audit Group Trigger : '
373 || c4_record.object_name || ' is ' || c4_record.status);
374 flag4 := 'Y';
375 end loop;
376
377 if flag4 = 'N' then
378 print_output(log,
379 '-'|| ' Audit Group Trigger : ' ||
380 substr(c1_record.table_name,1,24) || '_AU is missing ');
381 end if ;
382
383 for c5_record in c5(substr(c1_record.table_name,1,24),p_apps_user) loop
384 print_output(log,
385 '-'|| ' Audit Group Trigger : ' ||
386 c5_record.object_name
387 || ' is ' || c5_record.status);
388 flag5 := 'Y';
389 end loop;
390
391 if flag5 = 'N' then
392 print_output(log,
393 '-'|| ' Audit Group Trigger : ' ||
394 substr(c1_record.table_name,1,24) || '_AD is missing ');
395 end if;
396
397 for c6_record in c6(substr(c1_record.table_name,1,24),p_apps_user) loop
398 print_output(log,
399 '-'|| ' Audit Group Procedure : '
400 || c6_record.object_name || ' is ' || c6_record.status);
401 flag6 := 'Y';
402 end loop;
403
404 if flag6 = 'N' then
405 print_output(log,
406 '-'|| ' Audit Group Procedure : ' ||
407 substr(c1_record.table_name,1,24) || '_AIP is missing ');
408 end if;
409
413 || c7_record.object_name || ' is ' || c7_record.status);
410 for c7_record in c7(substr(c1_record.table_name,1,24),p_apps_user) loop
411 print_output(log,
412 '-'|| ' Audit Group Procedure : '
414 flag7 := 'Y';
415 end loop;
416
417 if flag7 = 'N' then
418 print_output(log,
419 '-'|| ' Audit Group Procedure : ' ||
420 substr(c1_record.table_name,1,24) || '_AUP is missing ');
421 end if;
422
423 for c8_record in c8(substr(c1_record.table_name,1,24),p_apps_user) loop
424 print_output(log,
425 '-'|| ' Audit Group Procedure : ' ||
426 c8_record.object_name
427 || ' is ' || c8_record.status);
428 flag8 := 'Y';
429 end loop;
430
431 if flag8 = 'N' then
432 print_output(log,
433 '-'|| ' Audit Group Procedure : ' ||
434 substr(c1_record.table_name,1,24) || '_ADP is missing ');
435 end if;
436
437 for c9_record in c9(substr(c1_record.table_name,1,24),p_apps_user) loop
438 print_output(log,
439 '-'|| ' Audit Group View : ' ||
440 c9_record.object_name
441 || ' is ' || c9_record.status);
442 flag9 := 'Y';
443 cnt9 := cnt9 + 1;
444 end loop;
445
446 if flag9 = 'N' then
447 print_output(log,
448 '-'|| ' Audit Group View : All Views like ' ||
449 substr(c1_record.table_name,1,24) || '_AV% are missing ');
450 end if;
451
452 for c17_record in c17(substr(c1_record.table_name,1,24)) loop
453 print_output(log,
454 '-'|| ' Audit Group View AV% : ' || cnt9
455 || ' out of ' || c17_record.cnt ||
456 ' View(s) are present in the database.');
457 end loop;
458
459 for c10_record in c10(substr(c1_record.table_name,1,24),p_apps_user) loop
460 print_output(log,
461 '-'|| ' Audit Group View : ' ||
462 c10_record.object_name || ' is ' || c10_record.status);
463 flag10 := 'Y';
464 end loop;
465
466 if flag10 = 'N' then
467 print_output(log,
468 '-'|| ' Audit Group View (one view AC1) : ' ||
469 substr(c1_record.table_name,1,24) || ' is missing ');
470 end if;
471
472 /* If there is a missing Audit Table or Shadow Table then skip
473 this next comparison statement */
474 if flag2 = 'N' or flag13 = 'N' then
475 null;
476 else
477
478 for compare_cols_record in compare_cols (c1_record.table_name, lv_schema) loop
479 print_output(log,
480 '-'|| ' Audit Group Table ' || c1_record.table_name ||
481 ' is missing column present in shadow table ' ||
482 substr(c1_record.table_name,1,24) || '_A : ' ||
483 compare_cols_record.column_name );
484 flag11 := 'Y';
485 end loop;
486
487 if flag11 = 'N' then
488 print_output(log,
489 '-'|| ' Audit Group Table ' || c1_record.table_name ||
490 ' is not missing any columns present in shadow table ' ||
491 substr(c1_record.table_name,1,24) || '_A ' );
492 end if;
493 end if;
494
495 for find_audit_groups_record in find_audit_groups (c1_record.group_name,
496 c1_record.table_name) loop
497 print_output(log,
498 '-'|| ' Audit Group Table ' || c1_record.table_name ||
499 ' is also present in audit group ' ||
500 find_audit_groups_record.group_name || ' with a state of ' ||
501 find_audit_groups_record.state);
502 flag12 := 'Y';
503 end loop;
504
505 if flag12 = 'N' then
506 print_output(log,
507 '-'|| ' Audit Group Table ' || c1_record.table_name ||
508 ' is not present in any other Audit Groups ' );
509 end if;
510
511 end loop;
512 end loop;
513
514 if flag1 = 'N' then
515 print_output(log,
516 '-'|| 'Audit Group : ' ||
517 group_name || ' is missing ');
518 end if;
519
520 print_output('Exiting '||PROGNM);
521 retcode := 0;
522
523 exception
524 when others then
525 retcode := 2;
526 end audit_group_validation;
527
528
529 end fnd_audit_report;