[Home] [Help]
PACKAGE BODY: APPS.IBC_AUDIT_LOG_GRP
Source
1 PACKAGE BODY IBC_AUDIT_LOG_GRP as
2 /* $Header: ibcalogb.pls 120.1 2005/05/31 23:18:35 appldev $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBC_AUDIT_LOG_GRP';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ibcalogb.pls';
6
7 -- Cursor to retrieve all information about a particular audit_log
8 CURSOR c_audit_log(p_audit_log_id NUMBER) IS
9 SELECT *
10 FROM IBC_AUDIT_LOGS
11 WHERE audit_log_id = p_audit_log_id;
12
13
14 -- --------------------------------------------------------------------
15 -- FUNCTION: Get_Lookup_Meaning
16 -- DESCRIPTION: Given a Lookup Type, code and Language; returns the
17 -- meaning in FND_LOOKUP_VALUES.
18 -- PARAMETERS:
19 -- p_lookup_type => Lookup Type
20 -- p_lookup_code => Lookup Code
21 -- p_language => language
22 -- --------------------------------------------------------------------
23 FUNCTION Get_Lookup_Meaning(p_lookup_type IN VARCHAR2,
24 p_lookup_code IN VARCHAR2,
25 p_language IN VARCHAR2 DEFAULT USERENV('LANG'))
26 RETURN VARCHAR2
27 IS
28 l_result VARCHAR2(80);
29 CURSOR c_lookup_meaning(p_lookup_type IN VARCHAR2,
30 p_lookup_code IN VARCHAR2,
31 p_language IN VARCHAR2) IS
32 SELECT meaning
33 FROM FND_LOOKUP_VALUES
34 WHERE lookup_type = p_lookup_type
35 AND lookup_code = p_lookup_code
36 AND language = p_language;
37 BEGIN
38 l_result := NULL;
39 OPEN c_lookup_meaning(p_lookup_type, p_lookup_code, p_language);
40 FETCH c_lookup_meaning INTO l_Result;
41 CLOSE c_lookup_meaning;
42 RETURN l_result;
43 END Get_Lookup_Meaning;
44
45
46 FUNCTION Object_Lookup_Value(p_object_value VARCHAR2)
47 RETURN VARCHAR2
48 IS
49 l_result VARCHAR2(4000);
50 l_pos NUMBER;
51 l_lookup_type VARCHAR2(4000);
52 l_lookup_code VARCHAR2(4000);
53 BEGIN
54
55 l_result := p_object_value;
56 l_pos := INSTR(p_object_value, ':');
57
58 IF l_pos > 0 THEN
59 l_lookup_type := SUBSTR(l_result, 1, l_pos - 1);
60 l_lookup_code := SUBSTR(l_result, l_pos + 1);
61
62 IF l_lookup_type = 'USER' THEN
63 SELECT user_name
64 INTO l_result
65 FROM fnd_user
66 WHERE user_id = l_lookup_code;
67 ELSIF l_lookup_type = 'GROUP' THEN
68 SELECT group_name resource_name
69 INTO l_result
70 FROM jtf_rs_groups_vl
71 WHERE group_id = TO_NUMBER(l_lookup_code);
72 ELSIF l_lookup_type = 'RESPONSIBILITY' THEN
73 SELECT responsibility_name
74 INTO l_result
75 FROM fnd_responsibility_vl
76 WHERE sysdate BETWEEN start_date and NVL(end_date, sysdate)
77 AND responsibility_id = l_lookup_code;
78 ELSIF l_lookup_type = 'GRANT_LEVEL' THEN
79 SELECT meaning
80 INTO l_result
81 FROM ibc_lookups
82 WHERE lookup_type = 'IBC_GRANTEE_TYPES'
83 AND lookup_code = l_lookup_code;
84 ELSIF l_lookup_type = 'GRANT_ACTION' THEN
85 SELECT meaning
86 INTO l_result
87 FROM ibc_lookups
88 WHERE lookup_type = 'IBC_SECURITY_ACTIONS'
89 AND lookup_code = l_lookup_code;
90 END IF;
91
92 END IF;
93
94 RETURN l_result;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 IF l_lookup_code IS NOT NULL AND
99 l_lookup_type IN ('USER', 'GROUP', 'RESPONSIBIILTY',
100 'GRANT_LEVEL', 'GRANT_ACTION')
101 THEN
102 RETURN l_lookup_code;
103 ELSE
104 RETURN l_result;
105 END IF;
106 END Object_Lookup_Value;
107
108 -- --------------------------------------------------------------------
109 -- PROCEDURE: Replace_Tokens
110 -- DESCRIPTION: It sets tokens for a particular audit log message record
111 -- PARAMETERS:
112 -- p_audit_log => Record holding the audit log row
113 -- p_language => language
114 -- --------------------------------------------------------------------
115 PROCEDURE Replace_Tokens(p_audit_log c_audit_log%ROWTYPE,
116 p_language VARCHAR2 DEFAULT USERENV('LANG'))
117 IS
118 l_orig_msg VARCHAR2(4000);
119 CURSOR c_citem_ver (p_citem_Ver_id NUMBER)
120 IS
121 SELECT *
122 FROM ibc_citems_v
123 WHERE citem_Ver_id = p_citem_Ver_id
124 AND language = p_language;
125 CURSOR c_citem (p_citem_id NUMBER)
126 IS
127 SELECT *
128 FROM ibc_content_items
129 WHERE content_item_id = p_citem_id;
130 BEGIN
131 l_orig_msg := FND_MESSAGE.get_string(NVL(p_audit_log.MESSAGE_APPLICATION, 'IBC'),
132 NVL(p_audit_log.MESSAGE_NAME, 'IBC_DFLT_AUDIT_MSG'));
133 l_orig_msg := UPPER(l_orig_msg);
134
135 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'AUDIT_LOG_ID') > 0 THEN
136 FND_MESSAGE.set_token('AUDIT_LOG_ID', p_audit_log.audit_log_id);
137 END IF;
138
139 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'ACTIVITY ') > 0 THEN
140 FND_MESSAGE.set_token('ACTIVITY', p_audit_log.activity);
141 END IF;
142
143 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'ACTIVITY_MEANING') > 0 THEN
144 FND_MESSAGE.set_token('ACTIVITY_MEANING',
145 Get_Lookup_Meaning('IBC_AUDIT_ACTIVITIES',
146 p_audit_log.ACTIVITY,
147 p_language));
148 END IF;
149
150 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'AUDIT_USER_ID') > 0 THEN
151 FND_MESSAGE.set_token('AUDIT_USER_ID', p_audit_log.user_id);
152 END IF;
153
154 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'AUDIT_TIME_STAMP') > 0 THEN
155 FND_MESSAGE.set_token('AUDIT_TIME_STAMP', p_audit_log.time_stamp);
156 END IF;
157
158 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'AUDIT_OBJECT_TYPE') > 0 THEN
159 FND_MESSAGE.set_token('AUDIT_OBJECT_TYPE', p_audit_log.object_type);
160 END IF;
161
162 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'AUDIT_OBJECT_VALUE1') > 0 THEN
163 FND_MESSAGE.set_token('AUDIT_OBJECT_VALUE1', Object_Lookup_Value(p_audit_log.object_value1));
164 END IF;
165
166 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'AUDIT_OBJECT_VALUE2') > 0 THEN
167 FND_MESSAGE.set_token('AUDIT_OBJECT_VALUE2', Object_Lookup_Value(p_audit_log.object_value2));
168 END IF;
169
170 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'AUDIT_OBJECT_VALUE3') > 0 THEN
171 FND_MESSAGE.set_token('AUDIT_OBJECT_VALUE3', Object_Lookup_Value(p_audit_log.object_value3));
172 END IF;
173
174 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'AUDIT_OBJECT_VALUE4') > 0 THEN
175 FND_MESSAGE.set_token('AUDIT_OBJECT_VALUE4', Object_Lookup_Value(p_audit_log.object_value4));
176 END IF;
177
178 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'AUDIT_OBJECT_VALUE5') > 0 THEN
179 FND_MESSAGE.set_token('AUDIT_OBJECT_VALUE5', Object_Lookup_Value(p_audit_log.object_value5));
180 END IF;
181
182 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'AUDIT_PARENT_VALUE') > 0 THEN
183 FND_MESSAGE.set_token('AUDIT_PARENT_VALUE', p_audit_log.parent_value);
184 END IF;
185
186 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'AUDIT_OBJECT_STATUS') > 0 THEN
187 FND_MESSAGE.set_token('AUDIT_OBJECT_STATUS', p_audit_log.object_status);
188 END IF;
189
190 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'EXTRA_INFO1_VALUE') > 0 THEN
191 FND_MESSAGE.set_token('EXTRA_INFO1_VALUE',
192 get_extra_info(p_audit_log_id => p_audit_log.audit_log_id,
193 p_info_number => 1));
194 END IF;
195
196 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'EXTRA_INFO2_VALUE') > 0 THEN
197 FND_MESSAGE.set_token('EXTRA_INFO2_VALUE',
198 get_extra_info(p_audit_log_id => p_audit_log.audit_log_id,
199 p_info_number => 2));
200 END IF;
201
202 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'EXTRA_INFO3_VALUE') > 0 THEN
203 FND_MESSAGE.set_token('EXTRA_INFO3_VALUE',
204 get_extra_info(p_audit_log_id => p_audit_log.audit_log_id,
205 p_info_number => 3));
206 END IF;
207
208 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'EXTRA_INFO4_VALUE') > 0 THEN
209 FND_MESSAGE.set_token('EXTRA_INFO4_VALUE',
210 get_extra_info(p_audit_log_id => p_audit_log.audit_log_id,
211 p_info_number => 4));
212 END IF;
213
214 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'EXTRA_INFO5_VALUE') > 0 THEN
215 FND_MESSAGE.set_token('EXTRA_INFO5_VALUE',
216 get_extra_info(p_audit_log_id => p_audit_log.audit_log_id,
217 p_info_number => 5));
218 END IF;
219
220 IF p_audit_log.object_type = G_CONTENT_ITEM THEN
221 FOR r_citem IN c_citem(p_audit_log.object_value1) LOOP
222
223 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'ITEM_REFERENCE_CODE') > 0 THEN
224 FND_MESSAGE.set_token('ITEM_REFERENCE_CODE', r_citem.item_reference_code);
225 END IF;
226
227 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'CONTENT_TYPE_CODE') > 0 THEN
228 FND_MESSAGE.set_token('CONTENT_TYPE_CODE', r_citem.content_type_code);
229 END IF;
230
231 END LOOP;
232 END IF;
233
234
235 IF p_audit_log.object_type = G_CITEM_VERSION THEN
236 FOR r_citem_ver IN c_citem_Ver(p_audit_log.object_value1) LOOP
237
238 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'CONTENT_ITEM_NAME') > 0 THEN
239 FND_MESSAGE.set_token('CONTENT_ITEM_NAME', r_citem_Ver.name);
240 END IF;
241
242 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'CONTENT_TYPE_CODE') > 0 THEN
243 FND_MESSAGE.set_token('CONTENT_TYPE_CODE', r_citem_Ver.ctype_code);
244 END IF;
245
246 IF INSTR(l_orig_msg, FND_GLOBAL.local_chr(38) || 'CONTENT_TYPE_NAME') > 0 THEN
247 FND_MESSAGE.set_token('CONTENT_TYPE_NAME', r_citem_Ver.ctype_name);
248 END IF;
249
250 END LOOP;
251 END IF;
252
253 END Replace_Tokens;
254
255 -- --------------------------------------------------------------------
256 -- PROCEDURE: Log_Action
257 -- DESCRIPTION: It stores an audit log
258 -- PARAMETERS:
259 -- p_activity => Activity Code
260 -- p_object_type => Object Type
261 -- p_object_value[1..5] => Primary Key for object being audited
262 -- p_parent_value => Parent Value
263 -- p_message_application => Application owner of audit message
264 -- p_message_name => Message Name (FND_MESSAGES)
265 -- p_extra_info[1..5]_type => Extra Information segment type
266 -- i.e. CONSTANT, LOOKUP or MESSAGE
267 -- p_extra_info[1..5]_ref_type => Lookup Type (in case of LOOKUP)
268 -- p_extra_info[1..5]_value => Value (Constant, lookup code or
269 -- message name).
270 -- <STANDARD API Parms>
271 -- --------------------------------------------------------------------
272 PROCEDURE log_action(
273 p_activity IN VARCHAR2
274 ,p_object_type IN VARCHAR2
275 ,p_object_value1 IN VARCHAR2
276 ,p_object_value2 IN VARCHAR2
277 ,p_object_value3 IN VARCHAR2
278 ,p_object_value4 IN VARCHAR2
279 ,p_object_value5 IN VARCHAR2
280 ,p_parent_value IN VARCHAR2
281 ,p_message_application IN VARCHAR2
282 ,p_message_name IN VARCHAR2
283 ,p_extra_info1_type IN VARCHAR2
284 ,p_extra_info1_ref_type IN VARCHAR2
285 ,p_extra_info1_value IN VARCHAR2
286 ,p_extra_info2_type IN VARCHAR2
287 ,p_extra_info2_ref_type IN VARCHAR2
288 ,p_extra_info2_value IN VARCHAR2
289 ,p_extra_info3_type IN VARCHAR2
290 ,p_extra_info3_ref_type IN VARCHAR2
291 ,p_extra_info3_value IN VARCHAR2
292 ,p_extra_info4_type IN VARCHAR2
293 ,p_extra_info4_ref_type IN VARCHAR2
294 ,p_extra_info4_value IN VARCHAR2
295 ,p_extra_info5_type IN VARCHAR2
296 ,p_extra_info5_ref_type IN VARCHAR2
297 ,p_extra_info5_value IN VARCHAR2
298 -- Standard API parms
299 ,p_commit IN VARCHAR2
300 ,p_api_version IN NUMBER
301 ,p_init_msg_list IN VARCHAR2
302 ,x_return_status OUT NOCOPY VARCHAR2
303 ,x_msg_count OUT NOCOPY NUMBER
304 ,x_msg_data OUT NOCOPY VARCHAR2
305 ) IS
306 --******************* BEGIN REQUIRED VARIABLES *************************
307 l_api_name CONSTANT VARCHAR2(30) := 'log_action'; --|**|
308 l_api_version_number CONSTANT NUMBER := G_API_VERSION_DEFAULT; --|**|
309 --******************* END REQUIRED VARIABLES ****************************
310 temp_rowid VARCHAR2(100);
311 audit_log_id NUMBER;
312 l_message_application VARCHAR2(50);
313 l_object_status VARCHAR2(30);
314 BEGIN
315
316 --DBMS_OUTPUT.put_line('----- ' || l_api_name || ' -----');
317 --******************* BEGIN REQUIRED AREA ******************************
318 SAVEPOINT svpt_log_action; --|**|
319 IF (p_init_msg_list = FND_API.g_true) THEN --|**|
320 FND_MSG_PUB.initialize; --|**|
321 END IF; --|**|
322 --|**|
323 -- Standard call to check for call compatibility. --|**|
324 IF NOT FND_API.Compatible_API_Call ( --|**|
325 L_API_VERSION_NUMBER --|**|
326 ,p_api_version --|**|
327 ,L_API_NAME --|**|
328 ,G_PKG_NAME) --|**|
329 THEN --|**|
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; --|**|
331 END IF; --|**|
332 --|**|
333 -- Initialize API return status to SUCCESS --|**|
334 x_return_status := FND_API.G_RET_STS_SUCCESS; --|**|
335 --******************* END REQUIRED AREA ********************************
336
337 IF NVL(FND_PROFILE.value('IBC_ENABLE_AUDIT_LOG'), 'Y') = 'Y' THEN
338
339 IF p_message_name IS NOT NULL THEN
340 l_message_application := NVL(p_message_application, 'IBC');
341 ELSE
342 l_message_application := NULL;
343 END IF;
344
345 --- *** LOGIC for OBJECT_STATUS
346 IF p_object_type = G_CONTENT_ITEM THEN
347 BEGIN
348 SELECT content_item_status
349 INTO l_object_status
350 FROM ibc_content_items
351 WHERE content_item_id = p_object_value1;
352 EXCEPTION WHEN OTHERS THEN
353 NULL;
354 END;
355 ELSIF p_object_type = G_CITEM_VERSION THEN
356 -- Bug# 3731956
357 -- When the version is hard-deleted then this SQL
358 -- will not return any value
359 --
360 BEGIN
361 SELECT citem_version_status
362 INTO l_object_status
363 FROM ibc_citem_versions_b
364 WHERE citem_version_id = p_object_value1;
365 EXCEPTION WHEN OTHERS THEN
366 NULL;
367 END;
368
369 END IF;
370
371 Ibc_Audit_Logs_Pkg.insert_row(
372 px_audit_log_id => audit_log_id
373 ,p_activity => p_activity
374 ,p_parent_value => p_parent_value
375 ,p_user_id => Fnd_Global.user_id
376 ,p_time_stamp => SYSDATE
377 ,p_object_type => p_object_type
378 ,p_object_value1 => p_object_value1
379 ,p_object_value2 => p_object_value2
380 ,p_object_value3 => p_object_value3
381 ,p_object_value4 => p_object_value4
382 ,p_object_value5 => p_object_value5
383 ,p_object_status => l_object_status
384 ,p_internal_flag => FND_API.G_FALSE
385 ,p_message_application => l_message_application
386 ,p_message_name => p_message_name
387 ,p_extra_info1_type => p_extra_info1_type
388 ,p_extra_info1_ref_type => p_extra_info1_ref_type
389 ,p_extra_info1_value => p_extra_info1_value
390 ,p_extra_info2_type => p_extra_info2_type
391 ,p_extra_info2_ref_type => p_extra_info2_ref_type
392 ,p_extra_info2_value => p_extra_info2_value
393 ,p_extra_info3_type => p_extra_info3_type
394 ,p_extra_info3_ref_type => p_extra_info3_ref_type
395 ,p_extra_info3_value => p_extra_info3_value
396 ,p_extra_info4_type => p_extra_info4_type
397 ,p_extra_info4_ref_type => p_extra_info4_ref_type
398 ,p_extra_info4_value => p_extra_info4_value
399 ,p_extra_info5_type => p_extra_info5_type
400 ,p_extra_info5_ref_type => p_extra_info5_ref_type
401 ,p_extra_info5_value => p_extra_info5_value
402 ,p_object_version_number => 1
403 ,x_rowid => temp_rowid
404 );
405
406 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
407 RAISE FND_API.G_EXC_ERROR;
408 END IF;
409
410 END IF; -- If enable audit log
411
412 -- COMMIT?
413 IF (p_commit = FND_API.g_true) THEN
414 COMMIT;
415 END IF;
416
417 -- Standard call to get message count and if count is 1, get message info.
418 FND_MSG_PUB.Count_And_Get(
419 p_count => x_msg_count,
420 p_data => x_msg_data
421 );
422
423 EXCEPTION
424 WHEN FND_API.G_EXC_ERROR THEN
425 --DBMS_OUTPUT.put_line('Expected Error');
426 ROLLBACK TO svpt_log_action;
427 Ibc_Utilities_Pvt.handle_exceptions(
428 p_api_name => L_API_NAME
429 ,p_pkg_name => G_PKG_NAME
430 ,p_exception_level => FND_MSG_PUB.G_MSG_LVL_ERROR
431 ,p_package_type => Ibc_Utilities_Pvt.G_PVT
432 ,p_sqlcode => SQLCODE
433 ,p_sqlerrm => SQLERRM
434 ,x_msg_count => x_msg_count
435 ,x_msg_data => x_msg_data
436 ,x_return_status => x_return_status
437 );
438 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
439 --DBMS_OUTPUT.put_line('Unexpected error');
440 ROLLBACK TO svpt_log_action;
441 Ibc_Utilities_Pvt.handle_exceptions(
442 p_api_name => L_API_NAME
443 ,p_pkg_name => G_PKG_NAME
444 ,p_exception_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
445 ,p_package_type => Ibc_Utilities_Pvt.G_PVT
446 ,p_sqlcode => SQLCODE
447 ,p_sqlerrm => SQLERRM
448 ,x_msg_count => x_msg_count
449 ,x_msg_data => x_msg_data
450 ,x_return_status => x_return_status
451 );
452 WHEN OTHERS THEN
453 --DBMS_OUTPUT.put_line('Other error');
454 ROLLBACK TO svpt_log_action;
455 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
456 p_api_name => L_API_NAME
457 ,p_pkg_name => G_PKG_NAME
458 ,p_exception_level => Ibc_Utilities_Pvt.G_EXC_OTHERS
459 ,p_package_type => Ibc_Utilities_Pvt.G_PVT
460 ,p_sqlcode => SQLCODE
461 ,p_sqlerrm => SQLERRM
462 ,x_msg_count => x_msg_count
463 ,x_msg_data => x_msg_data
464 ,x_return_status => x_return_status
465 );
466 END log_action;
467
468 -- --------------------------------------------------------------------
469 -- FUNCTION: Get_Audit_Message
470 -- DESCRIPTION: Given an Audit Log Id it resolves the appropriate
471 -- message (substituting tokens,etc.)
472 -- PARAMETERS:
473 -- p_audit_log_id => Audit Log Id
474 -- --------------------------------------------------------------------
475 FUNCTION get_audit_message(
476 p_audit_log_id IN NUMBER
477 ) RETURN VARCHAR2 IS
478 l_result VARCHAR2(4000);
479 r_audit_log c_audit_log%ROWTYPE;
480 BEGIN
481
482 l_result := NULL;
483
484 OPEN c_audit_log(p_audit_log_id);
485 FETCH c_audit_log INTO r_audit_log;
486 IF c_audit_log%FOUND THEN
487 FND_MESSAGE.set_name(NVL(r_audit_log.MESSAGE_APPLICATION, 'IBC'),
488 NVL(r_audit_log.MESSAGE_NAME, 'IBC_DFLT_AUDIT_MSG'));
489 Replace_Tokens(r_audit_log);
490 l_result := FND_MESSAGE.get();
491 FND_MESSAGE.clear;
492 END IF;
493 CLOSE c_audit_log;
494
495 RETURN l_result;
496
497 END get_audit_message;
498
499 -- --------------------------------------------------------------------
500 -- FUNCTION: Get_Extra_Info
501 -- DESCRIPTION: Given an Audit Log Id it returns the extra information
502 -- segment (based on p_info_number).
503 -- PARAMETERS:
504 -- p_audit_log_id => Audit Log Id
505 -- p_info_number => Indicates which segment to return
506 -- --------------------------------------------------------------------
507 FUNCTION get_extra_info(
508 p_audit_log_id IN NUMBER
509 ,p_info_number IN NUMBER
510 ) RETURN VARCHAR2 IS
511 TYPE cursorType IS REF CURSOR;
512 l_result VARCHAR2(4000);
513 l_extra_info_type IBC_AUDIT_LOGS.extra_info1_type%TYPE;
514 l_extra_info_ref_type IBC_AUDIT_LOGS.extra_info1_ref_type%TYPE;
515 l_extra_info_value IBC_AUDIT_LOGS.extra_info1_value%TYPE;
516 l_colon_pos NUMBER;
517 l_comma_pos NUMBER;
518 l_curr_value_list VARCHAR2(4000);
519 l_curr_value VARCHAR2(80);
520 l_msg_application VARCHAR2(50);
521 l_msg_name VARCHAR2(80);
522 l_before_message VARCHAR2(2000);
523 BEGIN
524 l_result := NULL;
525 IF p_info_number BETWEEN 1 AND 5 THEN
526
527 -- Getting rid of dynamic execution
528 IF p_info_number = 1 THEN
529 SELECT EXTRA_INFO1_TYPE,
530 EXTRA_INFO1_REF_TYPE,
531 EXTRA_INFO1_VALUE
532 INTO l_extra_info_type, l_extra_info_ref_type, l_extra_info_value
533 FROM IBC_AUDIT_LOGS
534 WHERE audit_log_id = p_audit_log_id;
535 ELSIF p_info_number = 2 THEN
536 SELECT EXTRA_INFO2_TYPE,
537 EXTRA_INFO2_REF_TYPE,
538 EXTRA_INFO2_VALUE
539 INTO l_extra_info_type, l_extra_info_ref_type, l_extra_info_value
540 FROM IBC_AUDIT_LOGS
541 WHERE audit_log_id = p_audit_log_id;
542 ELSIF p_info_number = 3 THEN
543 SELECT EXTRA_INFO3_TYPE,
544 EXTRA_INFO3_REF_TYPE,
545 EXTRA_INFO3_VALUE
546 INTO l_extra_info_type, l_extra_info_ref_type, l_extra_info_value
547 FROM IBC_AUDIT_LOGS
548 WHERE audit_log_id = p_audit_log_id;
549 ELSIF p_info_number = 4 THEN
550 SELECT EXTRA_INFO4_TYPE,
551 EXTRA_INFO4_REF_TYPE,
552 EXTRA_INFO4_VALUE
553 INTO l_extra_info_type, l_extra_info_ref_type, l_extra_info_value
554 FROM IBC_AUDIT_LOGS
555 WHERE audit_log_id = p_audit_log_id;
556 ELSE
557 SELECT EXTRA_INFO5_TYPE,
558 EXTRA_INFO5_REF_TYPE,
559 EXTRA_INFO5_VALUE
560 INTO l_extra_info_type, l_extra_info_ref_type, l_extra_info_value
561 FROM IBC_AUDIT_LOGS
562 WHERE audit_log_id = p_audit_log_id;
563 END IF;
564
565 IF l_extra_info_type = G_EI_LOOKUP THEN
566 l_result := Get_Lookup_Meaning(p_lookup_type => NVL(l_extra_info_ref_type,
567 'IBC_AUDIT_LOOKUPS'),
568 p_lookup_code => l_extra_info_value);
569 ELSIF l_extra_info_type = G_EI_MESSAGE THEN
570 l_colon_pos := INSTR(l_extra_info_value, ':');
571 IF l_colon_pos > 0 THEN
572 l_msg_application := SUBSTR(l_extra_info_value, 1, l_colon_pos - 1);
573 l_msg_name := SUBSTR(l_extra_info_value, l_colon_pos + 1);
574 ELSE
575 l_msg_application := 'IBC';
576 l_msg_name := l_extra_info_value;
577 END IF;
578 l_before_message := FND_MESSAGE.get_encoded();
579 FND_MESSAGE.clear;
580 FND_MESSAGE.set_name(l_msg_application,l_msg_name);
581 l_result := FND_MESSAGE.get();
582 FND_MESSAGE.clear;
583 FND_MESSAGE.set_encoded(l_before_message);
584 ELSIF l_extra_info_type = G_EI_CS_LOOKUP THEN
585
586 l_curr_value_list := l_extra_info_value;
587 WHILE l_curr_value_list IS NOT NULL LOOP
588 l_comma_pos := INSTR(l_curr_value_list, ',');
589 IF l_comma_pos > 0 THEN
590 l_curr_value := SUBSTR(l_curr_value_list, 1, l_comma_pos - 1);
591 l_curr_value_list := SUBSTR(l_curr_value_list, l_comma_pos + 1);
592 ELSE
593 l_curr_value := l_curr_value_list;
594 l_curr_value_list := NULL;
595 END IF;
596 IF l_curr_value IS NOT NULL THEN
597 IF l_result IS NULL THEN
598 l_result := Get_Lookup_Meaning(p_lookup_type => NVL(l_extra_info_ref_type,
599 'IBC_AUDIT_LOOKUPS'),
600 p_lookup_code => l_curr_value);
601 ELSE
602 l_result := l_result || ',' || Get_Lookup_Meaning(p_lookup_type => NVL(l_extra_info_ref_type,
603 'IBC_AUDIT_LOOKUPS'),
604 p_lookup_code => l_curr_value);
605
606 END IF;
607 END IF;
608 END LOOP;
609 ELSE
610 -- By default treated as CONSTANT
611 l_result := l_extra_info_value;
612 END IF;
613 END IF;
614 RETURN l_result;
615 END get_extra_info;
616
617
618
619 END IBC_AUDIT_LOG_GRP;