1 package body FND_EVENT as
2 /* $Header: AFAMEVTB.pls 120.2 2005/08/19 18:23:08 tkamiya ship $ */
3
4
5 -- Used to set the context for the event
6 g_application_id number;
7 g_source_appl_id number;
8 g_event_id number;
9 g_source_id number;
10 g_dest_type varchar2(1);
11 g_message_name varchar2(30);
12 g_severity varchar2(30);
13 g_msgset boolean;
14 g_message_appl varchar2(50);
15 g_source_type varchar2(1);
16 g_module varchar2(255);
17 g_user_id number;
18 g_resp_appl_id number;
19 g_responsibility_id number;
20 g_security_group_id number;
21 g_session_id number;
22 g_node varchar2(30);
23 g_db_instance varchar2(16);
24 g_audsid number;
25
26 g_total_events number := null;
27 g_remaining_events number := NULL;
28 previous_source_id number := NULL; -- used to store previous calls source_id
29 previous_source_type varchar2(1) := NULL; -- to store prev calls scr type
30
31 TYPE token_record_type is record
32 (token varchar2(30),
33 type varchar2(1),
34 value varchar2(2000));
35
36 TYPE token_tab_type is table of token_record_type
37 index by binary_integer;
38
39 TYPE events_record_type is record
40 (event_id number,
41 event varchar2(2000));
42
43 TYPE events_tab_type is table of events_record_type
44 index by binary_integer;
45
46 g_token_count number := 0;
47 g_tokens token_tab_type;
48 g_events events_tab_type;
49
50
51 --
52 -- Private Functions
53 --
54 --
55 -- Name
56 -- reset_vars
57 --
58 -- Purpose
59 -- Initializes all private variables.
60 --
61 procedure reset_vars is
62 empty_token token_tab_type;
63 begin
64 g_event_id := 0;
65 g_source_appl_id := null;
66 g_source_type := null;
67 g_source_id := null;
68 g_dest_type := null;
69 g_message_appl := null;
70 g_message_name := null;
71 g_severity := null;
72 g_module := null;
73 g_user_id := 0;
74 g_resp_appl_id := 0;
75 g_responsibility_id := 0;
76 g_security_group_id := 0;
77 g_session_id := 0;
78 g_node := null;
79 g_db_instance := null;
80 g_audsid := 0;
81
82 g_msgset := FALSE;
83 g_token_count := 0;
84 g_tokens := empty_token;
85 end;
86
87 -- Name : initialize
88 -- Description:
89 -- initialize sets the context for the event.
90 -- One has to call initialize before calling fnd_event.post.
91 -- Returns event_id if successfull otherwise 0.
92 -- Arguments:
93 -- source_application_id -
94 -- source_type - 'M'(manager)/'R'(Request)
95 -- source_id - concurrent_process_id/concurrent_request_id
96 -- dest_type - destination type
97 -- message_appl_short_name
98 -- - application short name of the message
99 -- name - message name
100 -- severity - ERROR/WARNING/FATAL
101 -- module - source module name
102 --
103
104 FUNCTION initialize(source_application_id IN NUMBER default 0,
105 source_type IN VARCHAR2,
106 source_id IN NUMBER,
107 dest_type IN VARCHAR2 default '0',
108 message_appl_short_name IN VARCHAR2,
109 name IN VARCHAR2,
110 severity IN VARCHAR2 default 'WARNING',
111 module IN VARCHAR2 default Null) return number is
112 empty_token token_tab_type;
113
114 dual_no_rows exception;
115 dual_too_many_rows exception;
116 BEGIN
117
118 reset_vars;
119
120 g_source_id := source_id;
121 g_source_appl_id := source_application_id;
122 g_source_type := source_type;
123 g_module := module;
124 g_dest_type := dest_type;
125 g_message_appl := message_appl_short_name;
126 g_message_name := name;
127 g_severity := severity;
128 g_msgset := TRUE;
129 g_token_count := 0;
130 g_tokens := empty_token;
131
132 -- get next event_id from sequence
133 begin
134 select fnd_events_s.nextval
135 into g_event_id
136 from sys.dual;
137
138 exception
139 when no_data_found then
140 raise dual_no_rows;
141 when too_many_rows then
142 raise dual_too_many_rows;
143 when others then
144 raise;
145 end;
146
147 return g_event_id;
148 exception
149 when dual_no_rows then
150 fnd_message.set_name ('FND', 'No Rows in Dual');
151 return(0);
152 when dual_too_many_rows then
153 fnd_message.set_name ('FND', 'Too many rows in Dual');
154 return(0);
155 when others then
156 fnd_message.set_name ('FND', 'SQL-Generic error');
157 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
158 fnd_message.set_token ('REASON', sqlerrm, FALSE);
159 fnd_message.set_token ('ROUTINE', 'FND_EVENT.initialize:others',
160 FALSE);
161
162 return(0);
163 END;
164
165 -- Name : set_token
166 -- Description:
167 -- It sets the token name and token value.
168 -- Call this procedure for each token you have for a event.
169 -- call initialize before calling set_token
170 -- Arguments:
171 -- event_id - event_id value for which you are setting the token.
172 -- token - token name
173 -- value - token value
174 -- type - 'C' = Constant. Value is used directly in the token
175 -- substitution.
176 -- 'S' = Select. Value is a SQL statement which returns a single
177 -- varchar2 value. (e.g. A translated concurrent
178 -- manager name.) This statement is run when the
179 -- even is retrieved, and the result is used in
180 -- the token substitution.
181 -- 'T' = Translate. Value is a message name. This message must
182 -- belong to the same application as the
183 -- message specified in the INITIALIZE function.
184 -- The message text will be used in the token
185 -- substitution.
186
187
188 PROCEDURE set_token(event_id IN number,
189 token IN VARCHAR2,
190 value IN VARCHAR2 default NULL,
191 type IN VARCHAR2 default 'C') is
192 BEGIN
193 -- if token is not null then keep it in table.
194 -- convert translate parameter to proper value to store in db.
195
196 if ( token is not null ) then
197 g_token_count := g_token_count + 1;
198 g_tokens(g_token_count).token := set_token.token;
199
200 if ( set_token.type in ('C','T','S')) then
201 g_tokens(g_token_count).type := set_token.type;
202 else
203 g_tokens(g_token_count).type := 'C';
204 end if;
205
206 g_tokens(g_token_count).value := set_token.value;
207 end if;
208
209 END;
210
211 -- Name : post
212 -- Description:
213 -- It inserts the cp_event into fnd_events table, fnd_event_tokens
214 -- Call this function after calling initialize and optionally set_token.
215 -- If successfull it returns TRUE else returns FALSE.
216 -- Arguments: event_id - event_id for which you want to post events.
217
218 FUNCTION post (event_id IN number )
219 return boolean is
220 PRAGMA AUTONOMOUS_TRANSACTION;
221
222 p_token_name varchar2(30);
223 p_token_value varchar2(2000);
224 p_token_translate varchar2(1);
225 i number;
226
227 has_tokens varchar2(1);
228
229 message_not_set exception;
230 dual_no_rows exception;
231 dual_too_many_rows exception;
232 app_not_found exception;
233 source_id_null exception;
234 insert_error exception;
235
236 BEGIN
237 -- if message is set and message is not null then process
238 if ( (g_message_name is null) and (not g_msgset) ) then
239 raise message_not_set;
240 end if;
241
242 if ( g_token_count = 0 ) then
243 has_tokens := 'N';
244 else
245 has_tokens := 'Y';
246 end if;
247
248
249 -- check source_id value
250 if ( g_source_id is null ) then
251 raise source_id_null;
252 end if;
253
254 -- get global values
255
256 g_user_id := FND_GLOBAL.user_id;
257 g_responsibility_id := FND_GLOBAL.resp_id;
258 g_resp_appl_id := FND_GLOBAL.resp_appl_id;
259 g_security_group_id := FND_GLOBAL.security_group_id;
260 g_db_instance := FND_CONC_GLOBAL.ops_inst_num;
261
262 begin
263 g_session_id := icx_sec.getsessioncookie();
264 exception
265 when others then
266 g_session_id := -1;
267 end;
268
269 select USERENV('SESSIONID')
270 into g_audsid
271 from sys.dual;
272
273 select MACHINE
274 into g_node
275 from v$session
276 where audsid = g_audsid;
277
278 if (g_module is null) then
279 select module
280 into g_module
281 from v$session
282 where audsid = g_audsid;
283 end if;
284
285 -- end global gets
286
287 -- validate source_id value.
288 -- If source_id is 0 or -1 then use session_id
289 if ( g_source_id <= 0 ) then
290 g_source_id := g_audsid;
291 g_source_type := 'O'; -- use source_type as Others
292 end if;
293
294
295 -- insert into fnd_events table
296 begin
297 insert into fnd_events
298 (event_id, source_application_id, source_id,
299 source_type, dest_type, message_appl_short_name,
300 message_name, module, user_id, resp_appl_id,
301 responsibility_id, security_group_id, session_id, node,
302 db_instance, audsid, time,
303 severity, processed, tokens)
304 values
305 (g_event_id, g_source_appl_id, g_source_id,
306 g_source_type, g_dest_type, g_message_appl,
307 g_message_name, g_module, g_user_id, g_resp_appl_id,
308 g_responsibility_id, g_security_group_id, g_session_id,
309 g_node, g_db_instance, g_audsid, sysdate,
310 g_severity, 'N', has_tokens);
311
312 if ( sql%rowcount = 0 ) then
313 raise insert_error;
314 end if;
315 end;
316
317 -- insert into fnd_event_tokens table
318 begin
319 for i in 1..g_token_count loop
320 p_token_name := g_tokens(i).token;
321 p_token_value := g_tokens(i).value;
322 p_token_translate := g_tokens(i).type;
323
324 insert into fnd_event_tokens
325 (event_id, token,
326 type, value)
327 values
328 ( post.event_id, p_token_name,
329 p_token_translate, p_token_value);
330 if (sql%rowcount = 0 ) then
331 raise insert_error;
332 end if;
333 end loop;
334 end;
335
336 reset_vars;
337
338 commit;
339
340 return TRUE;
341
342 exception
343 when message_not_set then
344 rollback;
345 return FALSE;
346 when source_id_null then
347 rollback;
348 return FALSE;
349 when insert_error then
350 fnd_message.set_name ('FND', 'SQL-Generic error');
351 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
352 fnd_message.set_token ('REASON', sqlerrm, FALSE);
353 fnd_message.set_token (
354 'ROUTINE', 'FND_EVENT.post: insert_error', FALSE);
355 rollback;
356 return FALSE;
357 when others then
358 fnd_message.set_name ('FND', 'SQL-Generic error');
359 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
360 fnd_message.set_token ('REASON', sqlerrm, FALSE);
361 fnd_message.set_token ('ROUTINE', 'FND_EVENT.post: others', FALSE);
362
363 rollback;
364 return FALSE;
365 end;
366
367 -- Name : set_processed
368 -- Description:
369 -- It sets the processed flag to given value for a given event_id.
370 --
371 -- Arguments:
372 -- event_id - number
373 -- flag - varchar2
374
375 PROCEDURE set_processed ( event_id number,
376 flag varchar2 default 'Y') is
377 PRAGMA AUTONOMOUS_TRANSACTION;
378 invalid_flag exception;
379 begin
380 if ( upper(flag) not in ('Y','N') ) then
381 raise invalid_flag;
382 end if;
383
384 update fnd_events
385 set processed = upper(flag)
386 where event_id = set_processed.event_id;
387
388 commit;
389
390 exception
391 when invalid_flag then
392 rollback;
393 when others then
394 rollback;
395 end;
396
397
398 -- Name : get
399 -- Description:
400 -- It gets the cp_event for a given source_id, source_type.
401 -- Call this function after calling set_name and optionally set_token and
402 -- log. It also gives the # of events still exists in the cp_events for
403 -- this souce_id and source_type.
404 -- Be causious while using this procedure in while or for loops.
405 -- It may lead to infinet loop.
406 -- Stop calling this procedure when you get remaining events = 0 for a
407 -- given source_id and source_type.
408 -- Arguments:
409 -- source_id - event source id, IN parameter
410 -- source_type - event source type, IN parameter
411 -- processed - TRUE/FLASE to set the processed flag, IN parameter
412 -- message - event message, OUT parameter
413 -- remaining - how many more events exists, OUT parameter
414 -- If the call is successfull then it returns TRUE otherwise FALSE;
415
416 FUNCTION get ( source_id IN number,
417 source_type IN varchar2,
418 processed IN boolean default FALSE,
419 message IN OUT NOCOPY varchar2,
420 remaining IN OUT NOCOPY number) return boolean is
421
422 cursor c_conc_events(p_source_id number, p_source_type varchar2) is
423 select /*+ index(FND_EVENTS) */ event_id, message_name, tokens
424 from fnd_events
425 where source_id = p_source_id
426 and source_type = p_source_type
427 order by event_id;
428
429 cursor c_conc_tokens( p_event_id number) is
430 select token, type, value
431 from fnd_event_tokens
432 where event_id = p_event_id;
433
434 l_event_id number;
435 l_message_name varchar2(30);
436 l_token varchar2(30);
437 l_translate boolean;
438 l_token_value varchar2(2000);
439 l_source_id number;
440 l_source_type varchar2(1);
441 i number;
442 empty_events events_tab_type;
443
444 routine varchar2(50) default 'FND_EVENT.GET';
445 begin
446
447 -- if the conc prog is running from OS then source_id will be either 0 or -1
448 -- if source_id is 0 or -1 then use the session id
449 if ( source_id <= 0 ) then
450 select USERENV('SESSIONID')
451 into l_source_id
452 from sys.dual;
453 l_source_type := 'O'; -- use source_type as Others
454 else
455 l_source_id := source_id;
456 l_source_type := source_type;
457 end if;
458
459 -- test for null to find out query has got executed once or not.
460 if (( g_total_events is null) and ((nvl(previous_source_id, 0) <> l_source_id)
461 or (nvl(previous_source_type,'0') <> l_source_type))) then
462 -- store these two values to find out caller needs for diff source
463 previous_source_id := l_source_id;
464 previous_source_type := l_source_type;
465
466 -- empty events plsql table
467 g_events := empty_events;
468
469 for c_events in c_conc_events(l_source_id, l_source_type) loop
470 g_total_events := nvl(g_total_events,0) + 1;
471 l_event_id := c_events.event_id;
472 l_message_name := c_events.message_name;
473 -- set the message in message stack
474 fnd_message.set_name('FND', l_message_name );
475
476 if ( c_events.tokens = 'Y' ) then
477 -- get all tokens for this event
478 for c_tokens in c_conc_tokens( l_event_id ) loop
479 l_token := c_tokens.token;
480 l_token_value := c_tokens.value;
481 if ( c_tokens.type = 'C' ) then
482 l_translate := FALSE;
483 elsif ( c_tokens.type = 'T' ) then
484 l_translate := TRUE;
485 elsif ( c_tokens.type = 'S' ) then
486 declare
487 token_text varchar2(2000);
488 begin
489 /* Set the routine name reported in exceptions, *
490 * so that the API isn't blamed for bad token SQL.*/
491 routine := 'FND_EVENT.GET (Token SQL)';
492 execute immediate c_tokens.value
493 into l_token_value;
494 routine := 'FND_EVENT.GET';
495 l_translate := FALSE;
496 end;
497 end if;
498
499 -- set this token in message stack
500 fnd_message.set_token(l_token, l_token_value, l_translate);
501 end loop;
502 end if; -- if there are any tokens
503
504 -- get the translated message and store it in plsql tables
505 g_events(g_total_events).event_id := l_event_id;
506 g_events(g_total_events).event := fnd_message.get;
507 g_remaining_events := g_total_events;
508 end loop;
509 end if;
510
511 -- get the events from plsql table and return to caller
512 if ( g_remaining_events > 0 ) then
513 i := g_total_events - g_remaining_events + 1;
514 message := g_events(i).event;
515 g_remaining_events := g_remaining_events - 1;
516 remaining := g_remaining_events;
517
518 -- set the processed flag if processed is passed
519 if ( processed ) then
520 set_processed(g_events(i).event_id, 'Y');
521 end if;
522
523 if ( g_remaining_events = 0 ) then
524 g_total_events := NULL;
525 end if;
526 RETURN TRUE;
527 else
528 remaining := 0;
529 message := NULL;
530 g_total_events := NULL;
531
532 RETURN TRUE;
533 end if;
534
535 exception
536 when others then
537 fnd_message.set_name ('FND', 'SQL-Generic error');
538 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
539 fnd_message.set_token ('REASON', sqlerrm, FALSE);
540 fnd_message.set_token ('ROUTINE', routine, FALSE);
541 message := fnd_message.get;
542 remaining := 0;
543 g_total_events := NULL;
544 rollback;
545 RETURN FALSE;
546 end;
547
548
549
550 --
551 -- Name
552 -- OEM_GET
553 --
554 -- Purpose
555 -- Retrieves the next unprocessed event with a destination type
556 -- of 'O' from the fnd_events table. The retrieved event is marked
557 -- as processed.
558 --
559 -- Arguments
560 -- event_text - Text of the event. (out)
561 -- Buffer must be at least 2000 bytes.
562 -- event_time - Date/time of event posting.
563 -- event_severity - 'WARNING' or 'ERROR' --
564 -- Returns
565 -- 0 - There are no unprocessed OEM events.
566 -- 1 - An event was successfully retrieved.
567 -- 2 - Error. The event_text parameter will contain
568 -- the error message.
569 --
570 -- Notes
571 -- Error messages are returned in the event_text paramter.
572 --
573 function oem_get ( event_text out NOCOPY varchar2,
574 event_time out NOCOPY date,
575 event_severity out NOCOPY varchar2 ) return number is
576 pragma AUTONOMOUS_TRANSACTION;
577
578 row_locked exception;
579 pragma exception_init(row_locked, -54);
580
581 cursor c1 is
582 select event_id
583 from fnd_events
584 where processed = 'N';
585
586 cursor c2 (instance_id number) is
587 select token, type, value
588 from fnd_event_tokens
589 where event_id = instance_id;
590
591 inst_id number;
592 msg_appl_sn varchar2(50);
593 msg_name varchar2(30) default null;
594 sev varchar2(30);
595 t date;
596 token_flag varchar2(1);
597 routine varchar2(50) default 'FND_EVENT.OEM_GET';
598 begin
599 /* Clear parameters */
600 event_text := null;
601 event_time := null;
602 event_severity := null;
603
604 for c1rec in c1 loop
605 inst_id := c1rec.event_id;
606
607 /* Make sure nobody else picked up the event. */
608 begin
609 select message_appl_short_name, message_name,
610 time, severity, tokens
611 into msg_appl_sn, msg_name, t, sev, token_flag
612 from fnd_events
613 where event_id = inst_id
614 and processed = 'N'
615 for update of processed nowait;
616 exception
617 when no_data_found then
618 goto loop_end;
619 when row_locked then
620 goto loop_end;
621 end;
622
623 /* We've got the lock. Mark the event has processed. */
624 update fnd_events
625 set processed = 'Y'
626 where event_id = inst_id;
627
628 commit;
629 exit;
630
631 <<loop_end>>
632 null;
633 end loop;
634
635 if (msg_name is null) then
636 /* Nothing was picked up from the queue */
637 return 0;
638 end if;
639
640 fnd_message.set_name(msg_appl_sn, msg_name);
641
642 if (token_flag = 'Y') then
643 for c2rec in c2(inst_id) loop
644 if (c2rec.type = 'C') then
645 fnd_message.set_token(c2rec.token, c2rec.value, FALSE);
646 elsif (c2rec.type = 'T') then
647 fnd_message.set_token(c2rec.token, c2rec.value, TRUE);
648 else /* Type S */
649 declare
650 token_text varchar2(2000);
651 begin
652 /* Set the routine name reported in exceptions, *
653 * so that the API isn't blamed for bad token SQL.*/
654 routine := 'FND_EVENT.OEM_GET (Token SQL)';
655 execute immediate c2rec.value
656 into token_text;
657 routine := 'FND_EVENT.OEM_GET';
658 fnd_message.set_token(c2rec.token, token_text, FALSE);
659 end;
660 end if;
661 end loop;
662 end if;
663
664 event_text := fnd_message.get;
665 event_time := t;
666 event_severity := sev;
667
668 return 1;
669
670
671 exception
672 when others then
673 fnd_message.set_name ('FND', 'SQL-Generic error');
674 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
675 fnd_message.set_token ('REASON', sqlerrm, FALSE);
676 fnd_message.set_token ('ROUTINE', routine, FALSE);
677 event_text := fnd_message.get;
678 rollback;
679 return 2;
680 end;
681
682
683 -- Name : oem_set_processed
684 -- Description:
685 -- It sets the processed flag to 'Y' for a given event_id.
686 -- Public wrapper function calling the private API
687 -- Arguments:
688 -- event_id - number
689
690 FUNCTION oem_set_processed ( event_id number ) return number is
691 begin
692 set_processed(event_id);
693 return 1;
694
695 exception
696 when others then
697 rollback;
698 RETURN 0;
699 end;
700
701 -- Name: oem_get_text
702 -- Description:
703 -- It gets the translated message text for a given event_id.
704 --
705 -- Arguments:
706 -- event_id - number
707 FUNCTION oem_get_text ( event_id number ) return varchar2 is
708
709 cursor c (instance_id number) is
710 select token, type, value
711 from fnd_event_tokens
712 where event_id = instance_id;
713
714 msg_appl_sn varchar2(50);
715 msg_name varchar2(30) default null;
716 token_flag varchar2(1);
717 routine varchar2(50) default 'FND_EVENT.OEM_GET_TEXT';
718 event_text varchar2(2000);
719 begin
720
721 select message_appl_short_name, message_name, tokens
722 into msg_appl_sn, msg_name, token_flag
723 from fnd_events
724 where event_id = oem_get_text.event_id;
725
726 if (msg_name is null) then
727 return null;
728 end if;
729
730 fnd_message.set_name(msg_appl_sn, msg_name);
731
732 if (token_flag = 'Y') then
733 for c2rec in c(oem_get_text.event_id) loop
734 if (c2rec.type = 'C') then
735 fnd_message.set_token(c2rec.token, c2rec.value, FALSE);
736 elsif (c2rec.type = 'T') then
737 fnd_message.set_token(c2rec.token, c2rec.value, TRUE);
738 else /* Type S */
739 declare
740 token_text varchar2(2000);
741 begin
742 /* Set the routine name reported in exceptions, *
743 * so that the API isn't blamed for bad token SQL.*/
744 routine := 'FND_EVENT.OEM_GET_TEXT (Token SQL)';
745 execute immediate c2rec.value
746 into token_text;
747 routine := 'FND_EVENT.OEM_GET_TEXT';
748 fnd_message.set_token(c2rec.token, token_text, FALSE);
749 end;
750 end if;
751 end loop;
752 end if;
753
754 event_text := fnd_message.get;
755
756 return event_text;
757
758 exception
759 when others then
760 fnd_message.set_name ('FND', 'SQL-Generic error');
761 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
762 fnd_message.set_token ('REASON', sqlerrm, FALSE);
763 fnd_message.set_token ('ROUTINE', routine, FALSE);
764 event_text := fnd_message.get;
765 rollback;
766 return event_text;
767 end;
768
769 end FND_EVENT;
[Home] [Help]