[Home] [Help]
PACKAGE BODY: APPS.IEC_SQL_LOGGER_PVT
Source
1 PACKAGE BODY IEC_SQL_LOGGER_PVT AS
2 /* $Header: IECVLGRB.pls 115.12 2004/05/18 19:38:17 minwang ship $ */
3
4 -- Get the next record id
5 FUNCTION GET_NEXT_RECORD_ID
6 RETURN NUMBER
7 IS
8
9 BEGIN
10 -- dbms_output.put_line('get_next_record_id: begin <'|| G_SEQ_NUM || '>' );
11
12 if( G_SEQ_NUM IS NULL OR G_SEQ_NUM = G_FETCH_SEQ_NUM )
13 then
14 -- dbms_output.put_line('get_next_record_id: After fetch id is <'|| G_SEQ_NUM || '>' );
15 select IEO_LNA_RECORDS_S1.nextval into G_SEQ_NUM from dual;
16 G_FETCH_SEQ_NUM := G_SEQ_NUM + 2000;
17 end if;
18
19 G_SEQ_NUM := G_SEQ_NUM + 1;
20 -- dbms_output.put_line('get_next_record_id: Returning ID <'|| G_SEQ_NUM || '>' );
21 return G_SEQ_NUM;
22 END;
23
24
25 -- Get the source id
26 PROCEDURE GET_SOURCE_ID
27 ( P_FACILITY_GUID IN VARCHAR2
28 , P_APP_ID IN VARCHAR2
29 , P_FACILITY_NAME_MSG_NAME IN VARCHAR2
30 , P_FACILITY_INSTANCE IN VARCHAR2
31 , P_FACILITY_INSTANCE_UID IN VARCHAR2
32 , P_IP_ADDRESS IN VARCHAR2
33 , P_HOSTNAME IN VARCHAR2
34 , P_OS_USER_NAME IN VARCHAR2
35 , P_LOG_LEVEL IN NUMBER
36 , X_SOURCE_ID IN OUT NOCOPY NUMBER
37 )
38 IS
39
40 PRAGMA AUTONOMOUS_TRANSACTION;
41 l_source_id IEO_LNA_SOURCES.SOURCE_ID%TYPE;
42 l_log_level IEO_LNA_SOURCES.LOG_LEVEL%TYPE;
43
44 l_last_updated_by NUMBER;
45 l_created_by NUMBER;
46 l_last_update_login NUMBER;
47
48 l_date1 DATE;
49 l_date2 DATE;
50
51 BEGIN
52 l_source_id := -1;
53 l_log_level := -1;
54
55 -- dbms_output.put_line('get_source Id: begin' );
56 -- Create a savepoint and then commit till this point.
57 SAVEPOINT log_source;
58
59 X_SOURCE_ID := 0;
60
61 BEGIN
62 l_last_updated_by := NVL(FND_GLOBAL.conc_login_id,-1);
63 l_created_by := NVL(FND_GLOBAL.user_id,-1);
64 l_last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
65
66 -- Check if a record already exists for this source.
67
68 Select SOURCE_ID, LOG_LEVEL into l_source_id, l_log_level
69 from IEO_LNA_SOURCES
70 where facility_guid = P_FACILITY_GUID
71 and facility_name_msg_name = P_FACILITY_NAME_MSG_NAME
72 and facility_instance = P_FACILITY_INSTANCE
73 and facility_instance_uid = P_FACILITY_INSTANCE_UID
74 and ip_address = P_IP_ADDRESS
75 and hostname = P_HOSTNAME
76 and os_user_name = P_OS_USER_NAME;
77
78 -- Update the log_level if that is different.
79 if ( l_log_level <> P_LOG_LEVEL )
80 then
81
82 -- Update the log_level for this source id;
83 update ieo_lna_sources
84 set log_level = P_LOG_LEVEL,
85 last_update_date = sysdate,
86 last_update_login = l_last_update_login
87 where source_id = l_source_id;
88
89 l_log_level := P_LOG_LEVEL;
90
91 end if;
92
93 EXCEPTION
94 WHEN NO_DATA_FOUND THEN
95 -- dbms_output.put_line('get_source Id: No data found.' );
96 l_date1 := sysdate;
97 l_date2 := l_date1;
98
99 insert into IEO_LNA_SOURCES (
100 SOURCE_ID,
101 CREATED_BY,
102 CREATION_DATE,
103 LAST_UPDATED_BY,
104 LAST_UPDATE_DATE,
105 LAST_UPDATE_LOGIN,
106 FACILITY_GUID,
107 FACILITY_NAME_MSG_NAME,
108 FACILITY_RESOURCE_GUID,
109 FACILITY_INSTANCE,
110 FACILITY_INSTANCE_UID,
111 IP_ADDRESS,
112 HOSTNAME,
113 OS_USER_NAME,
114 LOG_LEVEL
115 )
116 VALUES (
117 IEO_LNA_SOURCES_S1.NEXTVAL,
118 l_created_by,
119 l_date1,
120 l_last_updated_by,
121 l_date2,
122 l_last_update_login,
123 P_FACILITY_GUID,
124 P_FACILITY_NAME_MSG_NAME,
125 P_APP_ID,
126 P_FACILITY_INSTANCE,
127 P_FACILITY_INSTANCE_UID,
128 P_IP_ADDRESS,
129 P_HOSTNAME,
130 P_OS_USER_NAME,
131 P_LOG_LEVEL
132 )
133 RETURNING SOURCE_ID into l_source_id;
134 l_log_level := P_LOG_LEVEL;
135 END;
136
137 -- dbms_output.put_line('Returning source id <' || l_source_id || '> and log level <' || l_log_level || '>' );
138
139 commit;
140 X_SOURCE_ID := l_source_id;
141 return;
142 EXCEPTION
143 WHEN OTHERS THEN
144 rollback TO log_source;
145 commit;
146 RAISE;
147
148 END GET_SOURCE_ID;
149
150 -- This uses the format 'yyyy-MM-DD HH:MI:SS'
151 -- Log a message
152 PROCEDURE LOG
153 ( P_SOURCE_ID IN NUMBER
154 , P_LOG_LEVEL IN NUMBER
155 , P_TIMESTAMP IN VARCHAR2
156 , P_TIMESTAMP_MILLI IN NUMBER
157 , P_ACTION_ID IN NUMBER
158 , P_SEVERITY_ID IN NUMBER
159 , P_TITLE_MSG_NAME IN VARCHAR2
160 , P_TITLE_MSG_APP_NAME IN VARCHAR2
161 , P_MESSAGE IN VARCHAR2
162 , X_RECORD_ID IN OUT NOCOPY NUMBER
163 )
164 IS
165
166 BEGIN
167
168 LOG( P_SOURCE_ID, P_LOG_LEVEL, TO_DATE(P_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'), P_TIMESTAMP_MILLI, P_ACTION_ID, P_SEVERITY_ID, P_TITLE_MSG_NAME, P_TITLE_MSG_APP_NAME, P_MESSAGE, X_RECORD_ID );
169
170 END;
171
172 PROCEDURE LOG
173 ( P_SOURCE_ID IN NUMBER
174 , P_LOG_LEVEL IN NUMBER
175 , P_TIMESTAMP IN DATE
176 , P_TIMESTAMP_MILLI IN NUMBER
177 , P_ACTION_ID IN NUMBER
178 , P_SEVERITY_ID IN NUMBER
179 , P_TITLE_MSG_NAME IN VARCHAR2
180 , P_TITLE_MSG_APP_NAME IN VARCHAR2
181 , P_MESSAGE IN VARCHAR2
182 , X_RECORD_ID IN OUT NOCOPY NUMBER
183 )
184 IS
185
186 PRAGMA AUTONOMOUS_TRANSACTION;
187 l_log NUMBER(1);
188 l_record_id IEO_LNA_RECORDS.RECORD_ID%TYPE;
189 l_date1 DATE;
190 l_date2 DATE;
191
192 l_last_updated_by NUMBER;
193 l_created_by NUMBER;
194 l_last_update_login NUMBER;
195
196 BEGIN
197 l_log := -1;
198 l_record_id := -1;
199
200 X_RECORD_ID := -1;
201
202 -- dbms_output.put_line('log: begin' );
203
204 select 1 into l_log
205 from ieo_lna_sources
206 where log_level >= P_LOG_LEVEL
207 and source_id = P_SOURCE_ID;
208
209 BEGIN
210 -- Create a savepoint and then commit till this point.
211 SAVEPOINT log_record;
212
213 -- Log only if log_level is <= the specified level.
214 if( l_log = 1 )
215 then
216
217 l_date1 := sysdate;
218 l_date2 := l_date1;
219
220 l_last_updated_by := NVL(FND_GLOBAL.conc_login_id,-1);
221 l_created_by := NVL(FND_GLOBAL.user_id,-1);
222 l_last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
223
224 insert into ieo_lna_records ( record_id,
225 created_by,
226 creation_date,
227 last_updated_by,
228 last_update_date,
229 last_update_login,
230 source_id,
231 timestamp,
232 timestamp_milli,
233 action_id,
234 severity_id,
235 title_msg_name,
236 title_resource_guid,
237 xml_data )
238 values ( get_next_record_id,
239 l_created_by,
240 l_date1,
241 l_last_updated_by,
242 l_date2,
243 l_last_update_login,
244 P_SOURCE_ID,
245 P_TIMESTAMP,
246 P_TIMESTAMP_MILLI,
247 P_ACTION_ID,
248 P_SEVERITY_ID,
249 P_TITLE_MSG_NAME,
250 P_TITLE_MSG_APP_NAME,
251 P_MESSAGE )
252 RETURNING RECORD_ID into l_record_id;
253 end if;
254
255 EXCEPTION
256 WHEN OTHERS THEN
257 rollback to log_record;
258 commit;
259 RAISE;
260 END;
261
262 -- dbms_output.put_line('Send back <' || l_record_id || '>' );
263
264 commit;
265 X_RECORD_ID := l_record_id;
266 return;
267
268 EXCEPTION
269 WHEN NO_DATA_FOUND THEN
270 return;
271 END LOG;
272
273 -- =======================================================================
274 -- Provided for a single "log"
275 -- =======================================================================
276 PROCEDURE LOG_DESCRIPTION
277 ( P_RECORD_ID IN NUMBER
278 , P_DESC_POS IN NUMBER
279 , P_DESC_MSG_NAME IN VARCHAR2
280 , P_DESC_MSG_APP_NAME IN VARCHAR2
281 )
282 IS
283
284 PRAGMA AUTONOMOUS_TRANSACTION;
285 l_log NUMBER(1);
286 l_date1 DATE;
287 l_date2 DATE;
288
289 l_last_updated_by NUMBER;
290 l_created_by NUMBER;
291 l_last_update_login NUMBER;
292
293 BEGIN
294 l_log := -1;
295
296 -- dbms_output.put_line('log_description: begin' );
297 if( P_RECORD_ID < 0 )
298 then
299 return;
300 end if;
301
302 select 1 into l_log
303 from ieo_lna_records
304 where record_id = P_RECORD_ID;
305
306 -- Create a savepoint and then commit till this point.
307 SAVEPOINT log_description;
308
309 BEGIN
310 -- Log only if log_level is <= the specified level.
311 if( l_log = 1 )
312 then
313 l_date1 := sysdate;
314 l_date2 := l_date1;
315
316 l_last_updated_by := NVL(FND_GLOBAL.conc_login_id,-1);
317 l_created_by := NVL(FND_GLOBAL.user_id,-1);
318 l_last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
319
320 -- dbms_output.put_line('log_description: Adding the description' );
321 insert into ieo_lna_descriptions ( record_id,
322 desc_pos,
323 created_by,
324 creation_date,
325 last_updated_by,
326 last_update_date,
327 last_update_login,
328 desc_msg_name,
329 desc_resource_guid )
330 values ( P_record_id,
331 P_desc_pos,
332 l_created_by,
333 l_date1,
334 l_last_updated_by,
335 l_date2,
336 l_last_update_login,
337 P_desc_msg_name,
338 P_desc_msg_app_name );
339 end if;
340 EXCEPTION
341 WHEN OTHERS THEN
342 rollback to log_description;
343 commit;
344 RAISE;
345 END;
346 commit;
347
348 EXCEPTION
349 WHEN NO_DATA_FOUND THEN
350 return;
351
352 END LOG_DESCRIPTION;
353
354 -- =======================================================================
355 -- Provided for "logs" with multiple "descriptions"
356 -- =======================================================================
357 PROCEDURE LOG_DESCRIPTION
358 ( P_RECORD_ID IN NUMBER
359 , P_DESC_MSG_NAME IN VARCHAR2_TABLE
360 , P_DESC_MSG_APP_NAME IN VARCHAR2_TABLE
361 )
362 IS
363
364 PRAGMA AUTONOMOUS_TRANSACTION;
365
366 l_log NUMBER(1);
367
368 BEGIN
369 l_log := -1;
370
371 -- dbms_output.put_line('log_description || : begin count is <' || P_DESC_MSG_NAME.count || '>');
372
373 if( P_RECORD_ID <= 0 )
374 then
375 return;
376 end if;
377 select 1 into l_log
378 from ieo_lna_records
379 where record_id = P_RECORD_ID;
380
381 -- Create a savepoint and then commit till this point.
382 SAVEPOINT log_description_II;
383
384 BEGIN
385 -- Log only if log_level is <= the specified level.
386 if( l_log = 1 )
387 then
388 -- dbms_output.put_line('log_description || : Adding the log' );
389 for L in 1 .. P_DESC_MSG_NAME.count
390 loop
391 if ( P_DESC_MSG_NAME(L) IS NULL )
392 then
393 if( P_DESC_MSG_NAME(L+1) IS NULL )
394 then
395 exit;
396 end if;
397 else
398 -- dbms_output.put_line('log_description ||: id <' || P_RECORD_ID || '> pos <' || L || '> desc <' || P_DESC_MSG_NAME(L) || '>' );
399
400 log_description( P_RECORD_ID,
401 L,
402 P_DESC_MSG_NAME(L),
403 P_DESC_MSG_APP_NAME(L) );
404 end if;
405
406 end loop;
407 end if;
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 rollback to log_description_II;
412 commit;
413 RAISE;
414 END;
415
416 commit;
417 EXCEPTION
418 WHEN NO_DATA_FOUND THEN
419 return;
420
421 END LOG_DESCRIPTION;
422
423
424 -- =======================================================================
425 -- Provided for a description with a single param
426 -- =======================================================================
427 PROCEDURE DESCRIPTION_PARAMS
428 ( P_RECORD_ID IN NUMBER
429 , P_DESC_POS IN NUMBER
430 , P_PARAM_POS IN NUMBER
431 , P_PARAM_MSG_NAME IN VARCHAR2
432 , P_PARAM_MSG_APP_NAME IN VARCHAR2
433 , P_VALUE IN VARCHAR2
434 , P_VALUE_TYPE IN NUMBER
435 )
436 IS
437 PRAGMA AUTONOMOUS_TRANSACTION;
438 l_log NUMBER(1);
442 l_last_updated_by NUMBER;
439 l_date1 DATE;
440 l_date2 DATE;
441
443 l_created_by NUMBER;
444 l_last_update_login NUMBER;
445
446 BEGIN
447 l_log := -1;
448
449 -- dbms_output.put_line('description_params: begin' );
450 if( P_RECORD_ID <= 0 )
451 then
452 return;
453 end if;
454
455 select 1 into l_log
456 from ieo_lna_records
457 where record_id = P_RECORD_ID;
458
459 -- Create a savepoint and then commit till this point.
460 SAVEPOINT desc_params;
461
462 BEGIN
463 -- Log only if log_level is <= the specified level.
464 if( l_log = 1 )
465 then
466 l_date1 := sysdate;
467 l_date2 := l_date1;
468
469 l_last_updated_by := NVL(FND_GLOBAL.conc_login_id,-1);
470 l_created_by := NVL(FND_GLOBAL.user_id,-1);
471 l_last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
472
473 insert into ieo_lna_parameters ( record_id,
474 desc_pos,
475 param_pos,
476 created_by,
477 creation_date,
478 last_updated_by,
479 last_update_date,
480 last_update_login,
481 pname_msg_name,
482 pname_resource_guid,
483 value_type,
484 value )
485 values ( P_record_id,
486 P_desc_pos,
487 p_param_pos,
488 l_created_by,
489 l_date1,
490 l_last_updated_by,
491 l_date2,
492 l_last_update_login,
493 P_param_msg_name,
494 P_param_msg_app_name,
495 P_value_type,
496 P_value );
497 end if;
498 EXCEPTION
499 WHEN OTHERS THEN
500 rollback to desc_params;
501 commit;
502 RAISE;
503 END;
504
505 commit;
506
507 EXCEPTION
508 WHEN NO_DATA_FOUND THEN
509 return;
510
511 END DESCRIPTION_PARAMS;
512
513
514 -- =======================================================================
515 -- Provided for "descriptions" with multiple "params"
516 -- =======================================================================
517 PROCEDURE DESCRIPTION_PARAMS
518 ( P_RECORD_ID IN NUMBER
519 , P_DESC_POS IN NUMBER_TABLE
520 , P_PARAM_MSG_NAME IN VARCHAR2_TABLE
521 , P_PARAM_MSG_APP_NAME IN VARCHAR2_TABLE
522 , P_PARAM_VALUE IN VARCHAR2_TABLE
523 , P_PARAM_VALUE_TYPE IN NUMBER_TABLE
524 )
525 IS
526
527 PRAGMA AUTONOMOUS_TRANSACTION;
528
529 l_log NUMBER(1);
530
531 BEGIN
532 l_log := -1;
533
534 -- dbms_output.put_line('description_params II: begin' );
535 if( P_RECORD_ID < 0 )
536 then
537 return;
538 end if;
539
540 select 1 into l_log
541 from ieo_lna_records
542 where record_id = P_RECORD_ID;
543
544 -- Create a savepoint and then commit till this point.
545 SAVEPOINT desc_params_II;
546
547 BEGIN
548 -- Log only if log_level is <= the specified level.
549 if( l_log = 1 )
550 then
551 -- dbms_output.put_line('description_params ||: Adding params' );
552 for L in 1 .. P_DESC_POS.count
553 loop
554 if ( P_DESC_POS(L) IS NULL )
555 then
556 if( P_DESC_POS(L+1) IS NULL )
557 then
558 exit;
559 end if;
560 else
561 -- dbms_output.put_line('description_params || ||: id <' || P_RECORD_ID || '> pos <' || L || '> value <' || P_PARAM_VALUE(L) || '>' );
562 description_params( P_RECORD_ID,
563 P_DESC_POS(L),
564 L,
565 P_PARAM_MSG_NAME(L),
566 P_PARAM_MSG_APP_NAME(L),
567 P_PARAM_VALUE(L),
568 P_PARAM_VALUE_TYPE(L) );
569 end if;
570
571 end loop;
572 end if;
573
574 EXCEPTION
575 WHEN OTHERS THEN
576 rollback to desc_params_II;
577 commit;
578 RAISE;
579 END;
580
581 commit;
582 EXCEPTION
583 WHEN NO_DATA_FOUND THEN
584 return;
585
586 END DESCRIPTION_PARAMS;
587
588 END IEC_SQL_LOGGER_PVT;