1 PACKAGE BODY CN_MESSAGE_PKG as
2 /* $Header: cnsymsgb.pls 120.2 2006/02/17 11:57:01 ymao ship $ */
3 /*
4 Date Name Description
5 ----------------------------------------------------------------------------+
6 21-NOV-94 P Cook Created
7 24-MAY-95 P Cook Revised message stacking procedures in preparation for
8 testing.
9 23-JUN-95 P Cook populate audit_lines.message_type_code not message_type
10 07-JUL-95 P Cook Use CN_DEBUG_MODE profile to determine whether debug
11 messages are written to the table
12 20-JUL-95 P Cook Modified calls to begin batch
13 07-AUG-95 P Cook Renamed CN_DEBUG_MODE to CN_DEBUG.
14 31-AUG-95 P Cook Changed end_batch to set the completion timestamp.
15 19-FEB-96 P Cook Debugginf messages now only written if profile is set.
16
17 */
18
19 /*------------------------------ DATA TYPES ---------------------------------*/
20 TYPE message_table_type IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
21 TYPE code_table_type IS TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER;
22 TYPE date_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
23
24 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
25
26 -- Text is indented in 3 character increments
27 g_indent0 VARCHAR2(14) := '' ;
28 g_indent1 VARCHAR2(14) := ' ' ;
29 g_indent2 VARCHAR2(14) := ' ' ;
30 g_indent3 VARCHAR2(14) := ' ' ;
31 g_indent4 VARCHAR2(14) := ' ' ;
32 g_indent5 VARCHAR2(14) := ' ';
33
34 g_msg_stack MESSAGE_TABLE_TYPE; -- Message Stack
35 g_msg_stack_empty MESSAGE_TABLE_TYPE; -- Empty Stack for clearing memory
36 g_msg_type_stack CODE_TABLE_TYPE; -- Message Type Stack in sync with
37 -- message stack
38 g_msg_type_stack_empty CODE_TABLE_TYPE; -- Emtpy Type Stack
39 g_msg_date_stack DATE_TABLE_TYPE; -- Message Date Stack in sync with
40 -- message stack
41 g_msg_date_stack_empty DATE_TABLE_TYPE; -- Emtpy Date Stack
42
43
44 g_msg_count NUMBER := 0; -- Num of Messages on stack
45 g_msg_ptr NUMBER := 1; -- Points to next Message
46 -- on stack to retreive.
47
48 g_user_id NUMBER := FND_GLOBAL.User_Id;
49 g_conc_request_id NUMBER := 0;
50 g_batch_id NUMBER NULL;
51 g_process_audit_id NUMBER;
52 g_org_id NUMBER;
53
54 g_f_log utl_file.file_type;
55 g_log_file_open VARCHAR2(1):='N';
56
57 g_cn_debug VARCHAR2(1);
58
59
60 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
61 -- NAME
62 --
63 --
64 -- PURPOSE
65 -- Cover for set_name and set_token
66 -- NOTES
67 -- Whenever either the fornm or batch program encounters validation
68 -- problems we push the corresponding message onto the stack.
69 -- At the the end of batch processing we will dump these messages into a
70 -- table, we never interrupt the processing to issue messages.
71 -- If validation fails during form processing we must either raise
72 -- an error thus halting processing or raise a warning.
73 -- These forms messages are handlerd by fnd_set_message but we alos
74 -- write the messages to the stack so that if no application_error is
75 -- not raised we have the option of pushing them back into a form window
76 -- ate the end of the comit cycle.
77 --
78 PROCEDURE Set_Message( Appl_Short_Name IN VARCHAR2
79 ,Message_Name IN VARCHAR2
80 ,Token_Name1 IN VARCHAR2
81 ,Token_Value1 IN VARCHAR2
82 ,Token_Name2 IN VARCHAR2
83 ,Token_Value2 IN VARCHAR2
84 ,Token_Name3 IN VARCHAR2
85 ,Token_Value3 IN VARCHAR2
86 ,Token_Name4 IN VARCHAR2
87 ,Token_Value4 IN VARCHAR2
88 ,Translate IN BOOLEAN ) IS
89
90 BEGIN
91
92 -- Always set the passed message in case we want to fail processing
93 -- and issue the message in the form.
94
95 fnd_message.set_name (Appl_Short_Name,Message_Name);
96
97 -- protecting unused tokens prevents display of an "=" character in the
98 -- message
99
100 if token_name1 is not null then
101 fnd_message.set_token(token_name1, token_value1);
102 end if;
103 if token_name2 is not null then
104 fnd_message.set_token(token_name2, token_value2);
105 end if;
106 if token_name3 is not null then
107 fnd_message.set_token(token_name3, token_value3);
108 end if;
109 if token_name4 is not null then
110 fnd_message.set_token(token_name4, token_value4);
111 end if;
112
113 /* Set_Name ( Appl_Short_Name
114 ,Message_Name);
115 Set_Token(
116 Token_Name1
117 ,Token_Value1
118 Token_Name2
119 ,Token_Value2
120 Token_Name3
121 ,Token_Value3
122 Token_Name4
123 ,Token_Value4
124 ,Translate);
125 */
126
127 END Set_Message;
128
129 PROCEDURE ins_audit_line( x_message_text VARCHAR2
130 ,x_message_type VARCHAR2) IS
131 BEGIN
132
133 INSERT INTO cn_process_audit_lines_all
134 ( process_audit_id
135 ,process_audit_line_id
136 ,message_text
137 ,message_type_code
138 ,org_id)
139 VALUES( g_process_audit_id
140 ,cn_process_audit_lines_s1.nextval
141 ,substrb(x_message_text,1, 239)
142 ,x_message_type
143 ,g_org_id)
144 ;
145
146 EXCEPTION
147 WHEN OTHERS THEN
148 rollback_errormsg_commit('cn_message.insert_audit_line');
149 raise;
150 END ins_audit_line;
151
152 PROCEDURE push( x_message_text VARCHAR2
153 ,x_message_type VARCHAR2) IS
154 BEGIN
155
156 IF (g_msg_count > 1000) THEN
157
158 flush;
159
160 END IF;
161
162 g_msg_count := g_msg_count + 1;
163 g_msg_stack(g_msg_count) := Substrb(x_message_text, 1, 254);
164 g_msg_type_stack(g_msg_count) := x_message_type;
165 g_msg_date_stack(g_msg_count) := Sysdate;
166
167 EXCEPTION
168
169 WHEN others THEN
170
171 flush;
172
173 g_msg_count := g_msg_count + 1;
174 g_msg_stack(g_msg_count) := x_message_text;
175 g_msg_type_stack(g_msg_count) := x_message_type;
176 g_msg_date_stack(g_msg_count) := Sysdate;
177
178 END push;
179
180
181 PROCEDURE open_file(x_sequence IN NUMBER,
182 x_process_type IN VARCHAR2 ) IS
183
184 x_file_name VARCHAR2(100);
185 x_request_id NUMBER(15);
186
187 x_log_file VARCHAR2(1);
188 x_log_file_dir VARCHAR2(100);
189
190
191 BEGIN
192
193 x_log_file := fnd_profile.value('CN_LOG_FILE');
194 x_log_file_dir := fnd_profile.value('UTL_FILE_LOG');
195
196 IF ((x_log_file='Y') AND (x_log_file_dir IS NOT NULL) AND (g_log_file_open <>'Y')) THEN
197 x_request_id := fnd_global.conc_request_id;
198
199 IF ((x_request_id <> -1) AND (x_process_type = 'CALCULATION')) THEN
200 /*concurrent request case */
201 x_file_name := 'cn' || Lpad(x_request_id, 7, '0') || '.log';
202 ELSE
203 x_file_name := 'cn' || Lpad(x_sequence, 7, '0') || '.log';
204 END IF;
205
206 push( x_message_text => 'Concurrent request ID: '||x_request_id
207 ,x_message_type => 'DEBUG');
208 push( x_message_text => 'Log file name: '||x_file_name
209 ,x_message_type => 'DEBUG');
210 push( x_message_text => 'Log file directory: '||x_log_file_dir
211 ,x_message_type => 'DEBUG');
212 g_f_log := utl_file.fopen(x_log_file_dir, x_file_name, 'w');
213
214 g_log_file_open:='Y';
215
216 END IF;
217
218 EXCEPTION
219
220 WHEN UTL_FILE.INVALID_PATH THEN
221
222 push( x_message_text => 'UTL_FILE open failed. Invalid path'
223 ,x_message_type => 'DEBUG');
224
225 WHEN UTL_FILE.INVALID_MODE THEN
226
227 push( x_message_text => 'UTL_FILE open failed. Invalid mode'
228 ,x_message_type => 'DEBUG');
229
230 WHEN UTL_FILE.INVALID_OPERATION THEN
231
232 push( x_message_text => 'UTL_FILE open failed. Invalid operation'
233 ,x_message_type => 'DEBUG');
234
235 WHEN others THEN
236
237 push( x_message_text => 'UTL_FILE open failed. others'
238 ,x_message_type => 'DEBUG');
239
240
241
242 END open_file;
243
244 PROCEDURE ins_audit_batch( x_parent_proc_audit_id NUMBER
245 ,x_process_audit_id IN OUT NOCOPY NUMBER
246 ,x_request_id NUMBER
247 ,x_process_type VARCHAR2)
248 IS
249 PRAGMA AUTONOMOUS_TRANSACTION;
250 BEGIN
251
252 SELECT cn_process_audits_s.nextval
253 INTO g_process_audit_id
254 FROM sys.dual;
255
256 x_process_audit_id := g_process_audit_id;
257
258 INSERT INTO cn_process_audits_all
259 ( process_audit_id
260 ,parent_process_audit_id
261 ,concurrent_request_id
262 ,process_type
263 ,timestamp_start
264 ,org_id)
265 VALUES( x_process_audit_id
266 ,nvl(x_parent_proc_audit_id,x_process_audit_id)
267 ,x_request_id
268 ,x_process_type
269 ,sysdate
270 ,g_org_id) ;
271
272 COMMIT;
273
274 open_file(g_process_audit_id, x_process_type);
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 rollback_errormsg_commit('cn_message.insert_audit_batch');
279 raise;
280
281 END ins_audit_batch;
282
283 --
284 -- NAME
285 -- push
286 --
287 -- PURPOSE
288 -- Writes a debugging message to the Message Stack only if
289 -- the profile option value for CN_DEBUG = 'Y'.
290
291 PROCEDURE put_line (message_text VARCHAR2) IS
292
293 BEGIN
294
295 IF (g_log_file_open = 'Y') THEN
296
297 utl_file.put_line(g_f_log, message_text);
298 utl_file.fflush(g_f_log);
299
300 END IF;
301
302 EXCEPTION
303
304 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
305
306 push( x_message_text => 'UTL_FILE write failed. Invalid file handle'
307 ,x_message_type => 'DEBUG');
308
309 WHEN UTL_FILE.INVALID_OPERATION THEN
310
311 push( x_message_text => 'UTL_FILE write failed. Invalid operation'
312 ,x_message_type => 'DEBUG');
313
314 WHEN UTL_FILE.WRITE_ERROR THEN
315
316 push( x_message_text => 'UTL_FILE write failed. Write error'
317 ,x_message_type => 'DEBUG');
318
319
320
321 END put_line;
322
323 PROCEDURE close_file IS
324
325 BEGIN
326
327 g_log_file_open := 'N';
328 utl_file.fclose(g_f_log);
329
330 EXCEPTION
331
332 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
333
334 push( x_message_text => 'UTL_FILE close failed. Invalid file handle'
335 ,x_message_type => 'DEBUG');
336
337
338 END close_file;
339
340
341 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
342 --
343 -- NAME
344 -- debug
345 --
346 -- PURPOSE
347 -- Writes a debug message to the stack only if profile CN_DEBUG = 'Y'.
348
349 PROCEDURE debug(message_text VARCHAR2) IS
350 profile NUMBER;
351 BEGIN
352
353 put_line(message_text);
354
355 IF g_cn_debug = 'Y' THEN
356 push( x_message_text => message_text
357 ,x_message_type => 'DEBUG');
358 END IF;
359
360
361 -- Replaced with the above code for Performance concern
362 --IF fnd_profile.value('CN_DEBUG') = 'Y' THEN
363 -- push( x_message_text => message_text
364 -- ,x_message_type => 'DEBUG');
365 --END IF;
366
367
368
369 END debug;
370
371 --
372 -- NAME
373 -- write
374 --
375 -- PURPOSE
376 -- Writes a message to the output buffer regardless
377 -- the value for profile option AS_DEBUG
378 --
379 PROCEDURE write(p_message_text IN VARCHAR2,p_message_type IN VARCHAR2)
380 IS
381 profile NUMBER;
382 BEGIN
383
384 put_line(p_message_text);
385
386 push( x_message_text => p_message_text
387 ,x_message_type => p_message_type );
388
389 END write;
390
391 --
392 -- NAME
393 -- Set_Name
394 --
395 -- PURPOSE
396 -- Puts an "encoded" message name on the stack, marked for translation
397 --
398
399 PROCEDURE Set_Name( appl_short_name VARCHAR2
400 ,message_name VARCHAR2
401 ,indent NUMBER ) IS
402
403 indent_value NUMBER(2);
404 BEGIN
405
406 g_msg_count := g_msg_count + 1;
407 g_msg_stack(g_msg_count) := appl_short_name || ' ' || message_name;
408 g_msg_type_stack(g_msg_count) := 'TRANSLATE';
409 g_msg_date_stack(g_msg_count) := Sysdate;
410
411 IF indent IS NOT NULL THEN
412
413 IF indent = 0 THEN
414 indent_value := g_indent0;
415 ELSIF indent = 1 THEN
416 indent_value := g_indent1;
417 ELSIF indent = 2 THEN
418 indent_value := g_indent2;
419 ELSIF indent = 3 THEN
420 indent_value := g_indent3;
421 ELSIF indent = 4 THEN
422 indent_value := g_indent4;
423 ELSIF indent = 5 THEN
424 indent_value := g_indent5;
425 END IF;
426
427 set_token('INDENT', indent_value, FALSE);
428
429 END IF;
430
431 END set_name;
432
433 --
434 -- NAME
435 -- Set_Token
436 --
437 -- PURPOSE
441 -- although no serious errors will occur.
438 -- Append Token Information to the current message on the stack.
439 -- The current message must be of type 'TRANSLATE' for this
440 -- to work properly when the message is translated on the client,
442 --
443 PROCEDURE set_token(token_name IN VARCHAR2,
444 token_value IN VARCHAR2,
445 translate IN BOOLEAN ) IS
446 trans_label VARCHAR2(5);
447 BEGIN
448 IF translate THEN
449 trans_label := 'TRUE';
450 ELSE
451 trans_label := 'FALSE';
452 END IF;
453 g_msg_stack(g_msg_count)
454 := g_msg_stack(g_msg_count)|| ' ' ||
455 token_name|| ' \"' ||token_value|| '\" ' ||trans_label;
456
457 END set_token;
458
459 --
460 -- NAME
461 --
462 --
463 -- PURPOSE
464 -- Flush all messages (debug and translatable) off the stack(FIFO) into
465 -- the table
466 --
467
468 PROCEDURE flush IS
469 PRAGMA AUTONOMOUS_TRANSACTION;
470 TYPE numlist IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
471 seq_key numlist;
472 counter NUMBER := 1;
473
474 BEGIN
475 -- Build sequence collection
476 FOR i IN 1 .. g_msg_count LOOP
477 SELECT CN_PROCESS_AUDIT_LINES_S1.NEXTVAL
478 INTO seq_key(i) FROM dual;
479 END LOOP;
480
481 forall i IN 1 .. g_msg_count
482 INSERT INTO cn_process_audit_lines
483 ( process_audit_id
484 ,process_audit_line_id
485 ,message_text
486 ,message_type_code
487 ,creation_date
488 ,org_id)
489 VALUES( g_process_audit_id
490 ,seq_key(i)
491 ,substrb(g_msg_stack(i),1, 239)
492 ,g_msg_type_stack(i)
493 ,g_msg_date_stack(i)
494 ,g_org_id);
495
496 COMMIT;
497
498 /*
499 WHILE counter <= g_msg_count LOOP
500
501 ins_audit_line( g_msg_stack(counter)
502 ,g_msg_type_stack(counter));
503
504 counter := counter+1;
505
506 END LOOP;
507 */
508
509
510 clear; -- We've flushed the messages into the table so clear the stack
511 EXCEPTION
512 WHEN OTHERS THEN
513 ROLLBACK;
514 debug('cn_message.insert_audit_line : '||SQLCODE||SQLERRM);
515 COMMIT;
516
517 END flush;
518
519 --
520 -- NAME
521 --
522 --
523 -- PURPOSE
524 --
525 PROCEDURE end_batch(x_process_audit_id NUMBER) IS
526 PRAGMA AUTONOMOUS_TRANSACTION;
527 BEGIN
528 flush;
529
530 UPDATE cn_process_audits_all
531 SET timestamp_end = sysdate
532 WHERE process_audit_id = x_process_audit_id;
533
534 close_file;
535
536 COMMIT;
537
538 END end_batch;
539
540 --
541 -- NAME
542 -- Set_Error
543 -- Purpose
544 --
545
546 PROCEDURE set_error( routine IN VARCHAR2
547 ,context IN VARCHAR2 ) IS
548 delimiter1 VARCHAR2(3);
549 delimiter2 VARCHAR2(3);
550
551 BEGIN
552 IF routine IS NOT NULL THEN
553 delimiter1 := ' : ';
554 END IF;
555 IF context IS NOT NULL THEN
556 delimiter2 := ' : ';
557 END IF;
558
559 push( x_message_text => routine||delimiter1||context||delimiter2
560 ||SQLCODE||SQLERRM
561 ,x_message_type => 'ERROR');
562
563 END set_error;
564
565 --
566 -- NAME
567 -- Clear
568 --
569 -- PURPOSE
570 -- Frees memory used the the Message Stacks and resets the
571 -- the Message Stack counter and pointer variables.
572 --
573 PROCEDURE Clear IS
574 BEGIN
575 g_msg_stack := g_msg_stack_empty;
576 g_msg_type_stack := g_msg_type_stack_empty;
577 g_msg_date_stack := g_msg_date_stack_empty;
578 g_msg_count := 0;
579 g_msg_ptr := 1;
580 END clear;
581
582 --
583 -- NAME
584 -- Purge
585 --
586 -- PURPOSE
587 -- Delete the contents of cn_messages by batch_id or by creation date.
588 --
589 PROCEDURE purge( x_process_audit_id NUMBER
590 ,x_creation_date DATE) IS
591 BEGIN
592 IF x_process_audit_id IS NOT NULL THEN
593 DELETE FROM cn_process_audit_lines_all
594 WHERE process_audit_id = x_process_audit_id;
595
596 DELETE FROM cn_process_audits_all
597 WHERE process_audit_id = x_process_audit_id;
598
599 ELSIF x_creation_date IS NOT NULL THEN
600 DELETE FROM cn_process_audit_lines
601 WHERE creation_date <= x_creation_date;
602
603 DELETE FROM cn_process_audits
604 WHERE creation_date <= x_creation_date;
605 END IF;
606
607 END purge;
608
609 --
610 -- NAME
611 -- start
612 --
613 -- PURPOSE
614 -- Prepare the stacks and insert the batch record. Retrive the batch id
615 -- to be passe
616 --
617 PROCEDURE begin_batch( x_parent_proc_audit_id NUMBER
618 ,x_process_audit_id IN OUT NOCOPY NUMBER
619 ,x_request_id NUMBER
620 ,x_process_type VARCHAR2
621 ,p_org_id IN NUMBER) IS
622 BEGIN
623 clear;
624 g_cn_debug := fnd_profile.value('CN_DEBUG');
625 g_org_id := p_org_id;
626
627 ins_audit_batch( x_parent_proc_audit_id
628 ,x_process_audit_id
629 ,x_request_id
630 ,x_process_type);
631 END begin_batch;
632
633
634 PROCEDURE rollback_errormsg_commit (x_error_context VARCHAR2) IS
635 delimiter VARCHAR2(3) := ' : ';
636 BEGIN
637 rollback;
638 debug(x_error_context||delimiter||SQLCODE||SQLERRM);
639 flush;
640 -- commit; -- comment out since flush will do commit
641 END rollback_errormsg_commit;
642
643 END CN_MESSAGE_PKG;
[Home] [Help]