1 package body WF_EVENT as
2 /* $Header: WFEVENTB.pls 120.12.12010000.3 2009/01/08 11:03:31 skandepu ship $ */
3 --------------------------------------------------------------------------
4 /*
5 ** PRIVATE global variables
6 */
7
8 /*
9 ** Cache Variables
10 ** pv_last_agent_name - the value of agent name
11 ** pv_last_queue_name - the value of queue name, no schema information
12 ** pv_last_schema_name - the value of schema name
13 ** pv_last_recipients - the value of last recipients
14 */
15 pv_last_agent_name varchar2(30);
16 pv_last_queue_name varchar2(80);
17 pv_last_schema_name varchar2(30);
18 pv_last_recipients varchar2(30);
19 pv_last_dequeue_enabled varchar2(7);
20 procedure GetAgentDetails(agent_name in varchar2);
21
22 java_sub varchar2(240) := 'java://';
23
24 NO_SAVEPOINT exception; /* Bug 1840819 */
25 pragma EXCEPTION_INIT(NO_SAVEPOINT,-1086);
26
27 DISPATCH_ERROR exception;
28
29
30 --Cursor to get the group members of one agent group.
31 --We assume that group members are of the same system as agent group.
32 CURSOR agent_group_members(agent_name varchar2,system_name varchar2) is
33 select agt2.name as agt_name,
34 agt2.queue_handler as queue_handler
35 from wf_agent_groups agp ,
36 wf_agents agt1 ,
37 wf_agents agt2 ,
38 wf_systems sys
39 where agt1.name = agent_name
40 and agt1.type = 'GROUP'
41 and agt1.status = 'ENABLED'
42 and agt1.system_guid = sys.guid
43 and sys.name = system_name
44 and agp.group_guid = agt1.guid
45 and agp.member_guid = agt2.guid
46 and agt2.system_guid = sys.guid
47 and agt2.status = 'ENABLED';
48 --------------------------------------------------------------------------
49 /*
50 ** setMessage (PRIVATE) - Generate the Message for this event
51 ** if necessary
52 */
53 PROCEDURE setMessage(p_event in out nocopy wf_event_t)
54 is
55 msg clob;
56 func varchar2(240);
57 cmd varchar2(1000);
58 ename varchar2(240) := p_event.getEventName();
59 ekey varchar2(240) := p_event.getEventKey();
60 eplist wf_parameter_list_t := p_event.getParameterList();
61 executed boolean;
62 -- Note that ORA-06550 is a generic PL/SQL compilation error
63 -- Here it is most likely caused by "Wrong Number of Arguments".
64 plsql_error EXCEPTION;
65 PRAGMA EXCEPTION_INIT(plsql_error, -06550);
66
67 -- bes caching implementation
68 l_event_obj wf_event_obj;
69 begin
70 /*
71 ** mjc We are now going to call this from the dispatcher
72 ** if the subscription rule data is MESSAGE
73 */
74 --if (wf_event.test(ename) = 'MESSAGE') then
75
76 l_event_obj := wf_bes_cache.GetEventByName(ename);
77
78 if (l_event_obj is null) then
79 wf_core.context('Wf_Event', 'setMessage', ename, ekey);
80 wf_core.raise('WFE_EVENT_NOTEXIST');
81 end if;
82
83 func := l_event_obj.GENERATE_FUNCTION;
84
85 if (func is not null) then
86
87 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
88 wf_log_pkg.string2(wf_log_pkg.level_statement,
89 'wf.plsql.wf_event.SetMessage.genfunc_callout',
90 'Start executing generate function - '||func, true);
91 end if;
92
93 WF_BES_DYN_FUNCS.Generate(func,
94 ename,
95 ekey,
96 eplist,
97 msg,
98 executed);
99 if (not executed) then
100 /** The Generate Function has been extended to support the passing
101 of the parameter list as well. To make sure we are compatible
102 with 2.6.0, we catch any error if there are too many parameters
103 and try again with the old API signature (eventname, eventkey)
104 **/
105 -- func came from WF_EVENTS.GENERATE_FUNCTION
106 -- BINDVAR_SCAN_IGNORE
107 cmd := 'begin :v1 := '||func||'(:v2, :v3, :v4); end;';
108 begin
109 execute immediate cmd using in out msg, in ename, in ekey, in eplist;
110 exception
111 when plsql_error then
112 -- BINDVAR_SCAN_IGNORE
113 cmd := 'begin :v1 := '||func||'(:v2, :v3); end;';
114 execute immediate cmd using in out msg, in ename, in ekey;
115 end;
116 end if;
117
118 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
119 wf_log_pkg.string2(wf_log_pkg.level_statement,
120 'wf.plsql.wf_event.SetMessage.genfunc_callout',
121 'End executing generate function - '||func, false);
122 end if;
123
124 else
125 cmd := '<default><event_name>'||ename||'</event_name><event_key>'||
126 p_event.getEventKey()||'</event_key></default>';
127 dbms_lob.createtemporary(msg, FALSE, DBMS_LOB.CALL);
128 dbms_lob.write(msg, length(cmd), 1, cmd);
129 end if;
130
131 p_event.event_data := msg ;
132 -- p_event.setEventData(msg);
133 --end if;
134 exception
135 when others then
136 if (Wf_Core.Error_Name = 'WFE_EVENT_NOTEXIST') then
137 raise;
138 else
139 wf_core.context('Wf_Event', 'setMessage', ename, ekey, func);
140 WF_CORE.Token('ENAME', p_event.event_name);
141 wf_core.token('FUNCTION_NAME', func);
142 WF_CORE.Token('SQLCODE', to_char(sqlcode));
143 WF_CORE.Token('SQLERRM', sqlerrm);
144 WF_CORE.Raise('WFE_DISPATCH_GEN_ERR');
145 end if;
146 end;
147 -----------------------------------------------------------------------
148 /*
149 ** setErrorInfo - <described in WFEVENTS.pls>
150 */
151 PROCEDURE setErrorInfo(p_event in out nocopy wf_event_t,
152 p_type in varchar2)
153 is
154 err_name varchar2(30);
155 err_msg varchar2(2000);
156 err_stack varchar2(2000);
157 begin
158
159 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
160 wf_log_pkg.string(wf_log_pkg.level_procedure,
161 'wf.plsql.WF_EVENT.setErrorInfo.begin',
162 'Setting Error Info');
163 end if;
164
165 -- First look for a standard WF_CORE exception.
166 wf_core.get_error(err_name, err_msg, err_stack, 2000);
167
168 if (err_name is null) then
169 -- If no WF_CORE exception, look for an Oracle error.
170 err_name := to_char(sqlcode);
171 err_msg := sqlerrm;
172 end if;
173
174 -- set error information into the event --
175 p_event.setErrorMessage(err_msg);
176 p_event.setErrorStack(err_stack);
177 p_event.addParameterToList('ERROR_NAME', err_name);
178 p_event.addParameterToList('ERROR_TYPE', p_type);
179 exception
180 when others then
181 wf_core.context('Wf_Event', 'setErrorInfo', p_event.getEventName());
182 raise;
183 end;
184 -----------------------------------------------------------------------
185 /*
186 ** saveErrorToQueue (PRIVATE) - Save the event to the WF_ERROR queue.
187 */
188 PROCEDURE saveErrorToQueue(p_event in out nocopy wf_event_t)
189 is
190 erragt wf_agent_t;
191 cmd varchar2(1000);
192 begin
193
194 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
195 wf_log_pkg.string(wf_log_pkg.level_procedure,
196 'wf.plsql.WF_EVENT.saveErrorToQueue.Begin',
197 'saving event to WF_ERROR on '|| wf_event.local_system_name);
198 end if;
199
200 erragt := wf_agent_t('WF_ERROR', wf_event.local_system_name);
201
202 --
203 -- mjc - lets just call the API directly
204 --
205 wf_error_qh.enqueue(p_event, erragt);
206
207 --cmd := 'begin WF_ERROR_QH.enqueue(:v1, :v2); end;';
208 --execute immediate cmd using in p_event,
209 -- in erragt;
210 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
211 wf_log_pkg.string(wf_log_pkg.level_procedure,
212 'wf.plsql.WF_EVENT.saveErrorToQueue.End',
213 'error info saved');
214 end if;
215 exception
216 when others then
217 wf_core.context('Wf_Event', 'saveErrorToQueue', p_event.getEventName());
218 wf_core.token('FUNCTION_NAME', 'WF_ERROR_QH.enqueue()');
219 --wf_core.raise('WF_EXT_FUNCTION');
220 raise;
221 end;
222 --------------------------------------------------------------------------
223 -----------------------------------------------------------------------
224 /*
225 ** saveErrorToJavaQueue (PRIVATE) - Save the event to the WF_JAVA_ERROR queue.
226 */
227 PROCEDURE saveErrorToJavaQueue(p_event in out nocopy wf_event_t)
228 is
229 erragt wf_agent_t;
230 cmd varchar2(1000);
231 begin
232
233 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
234 wf_log_pkg.string(wf_log_pkg.level_procedure,
235 'wf.plsql.WF_EVENT.saveErrorToJavaQueue.Begin',
236 'saving event to WF_JAVA_ERROR on '|| wf_event.local_system_name);
237 end if;
238
239 erragt := wf_agent_t('WF_JAVA_ERROR', wf_event.local_system_name);
240
241 --
242 -- mjc - lets just call the API directly
243 --
244 WF_EVENT_OJMSTEXT_QH.enqueue(p_event, erragt);
245
246 --cmd := 'begin WF_ERROR_QH.enqueue(:v1, :v2); end;';
247 --execute immediate cmd using in p_event,
248 -- in erragt;
249 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
250 wf_log_pkg.string(wf_log_pkg.level_procedure,
251 'wf.plsql.WF_EVENT.saveErrorToJavaQueue.End',
252 'error info saved');
253 end if;
254 exception
255 when others then
256 wf_core.context('Wf_Event', 'saveErrorToQueue', p_event.getEventName());
257 wf_core.token('FUNCTION_NAME', 'WF_EVENT_OJMSTEXT_QH.enqueue()');
258 --wf_core.raise('WF_EXT_FUNCTION');
259 raise;
260 end;
261 --------------------------------------------------------------------------
262 /*
263 ** isDeferToJava (PRIVATE)
264 * returns true : if current subscription is Java or the event has a
265 * Java generate function or there exists a subsequent
266 * Java subscription when max threshold is reached
267 * returns false: otherwise
268 */
269 FUNCTION isDeferToJava(max_threshold_reached in boolean,
270 p_event_name in varchar2,
271 p_source_type in varchar2,
272 p_rule_func in varchar2,
273 p_rule_data in varchar2,
274 p_phase in pls_integer)
275 return boolean
276 is
277 -- bes caching implementation
278 l_event_obj wf_event_obj;
279 l_subs_list wf_event_subs_tab;
280 l_java_defer boolean;
281 l_java_gen boolean;
282 l_phase number;
283 l_rule_func varchar2(240);
284 l_rule_data varchar2(8);
285 begin
286 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
287 wf_log_pkg.string(wf_log_pkg.level_procedure,
288 'wf.plsql.WF_EVENT.isDeferToJava.begin',
289 'Checking for Java subscription/generate');
290 end if;
291
292 if (p_rule_func is not null AND
293 UPPER(substr(p_rule_func, 0, length(java_sub))) = UPPER(java_sub)) then
294 -- this is a Java subscription. Return true
295 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
296 wf_log_pkg.string(wf_log_pkg.level_statement,
297 'wf.plsql.WF_EVENT.isDeferToJava.Subscription',
298 'found a Java subscription');
299 end if;
300 return true;
301 end if;
302
303 -- event should be in cache by now
304 l_event_obj := wf_bes_cache.GetEventByName(p_event_name);
305
306 if (l_event_obj is null) then
307 return false;
308 end if;
309
310 -- set flag to true if java generate function is not null
311 if (l_event_obj.JAVA_GENERATE_FUNC is not null) then
312 l_java_gen := true;
313 else
314 l_java_gen := false;
315 end if;
316
317 -- Now Checking Generate Functions
318 if (not max_threshold_reached) then
319
320 if (l_java_gen AND p_rule_data is not null AND
321 p_rule_data = 'MESSAGE' and p_source_type = 'LOCAL') then
322 -- this is a Java generate function.
323 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
324 wf_log_pkg.string(wf_log_pkg.level_statement,
325 'wf.plsql.WF_EVENT.isDeferToJava.Subscription',
326 'found a Java generate function');
327 end if;
328 return true;
329 else
330 return false;
331 end if;
332 else
333
334 -- we have event and subscription list from the cache
335 -- o if a subsequent subscription has a java rule func
336 -- o if the event has a java generate function and a subsequent
337 -- subscription has MESSAGE rule data
338
339 l_java_defer := false;
340 l_subs_list := wf_bes_cache.GetSubscriptions(p_event_name, p_source_type, null);
341
342 if (l_subs_list is not null) then
343 for i in 1..l_subs_list.COUNT loop
344 l_phase := l_subs_list(i).PHASE;
345 l_rule_func := l_subs_list(i).RULE_FUNCTION;
346 l_rule_data := l_subs_list(i).RULE_DATA;
347
348 if ((l_phase is null OR l_phase > p_phase) AND
349 ((l_rule_data = 'MESSAGE' AND l_java_gen) OR
350 (UPPER(substr(l_rule_func, 0, length(java_sub))) = UPPER(java_sub)))) then
351 l_java_defer := true;
352 exit;
353 end if;
354
355 end loop;
356 end if;
357
358 if(l_java_defer) then
359 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
360 wf_log_pkg.string(wf_log_pkg.level_statement,
361 'wf.plsql.WF_EVENT.isDeferToJava.Subscription',
362 'found subsequent Java sub or java generate');
363 end if;
364 return true;
365 else
366 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
367 wf_log_pkg.string(wf_log_pkg.level_statement,
368 'wf.plsql.WF_EVENT.isDeferToJava.Subscription',
369 'No Java sub found or Java Generate Found');
370 end if;
371 return false;
372 end if;
373 end if;
374
375 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
376 wf_log_pkg.string(wf_log_pkg.level_procedure,
377 'wf.plsql.WF_EVENT.isDeferToJava.end',
378 'Checked for Java subscription/generate');
379 end if;
380
381 return false;
382 exception
383 when others then
384 wf_core.context('Wf_Event', 'isDeferToJava', p_event_name);
385 raise;
386 end isDeferToJava;
387
388 --------------------------------------------------------------------------
389 /*
390 ** isSaveToJavaError (PRIVATE)
391 * returns true : if current subscription is Java and source type is ERROR
392 * save to Java Error
393 * returns false: otherwise
394 */
395 FUNCTION isSaveToJavaError(p_event_name in varchar2,
396 p_rule_func in varchar2)
397 return boolean is
398
399 begin
400 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
401 wf_log_pkg.string(wf_log_pkg.level_procedure,
402 'wf.plsql.WF_EVENT.isSaveToJavaError.begin',
403 'Checking Java Error subscription for ' || p_event_name);
404 end if;
405
406 if (p_rule_func is not null AND
407 UPPER(substr(p_rule_func, 0, length(java_sub))) = UPPER(java_sub)) then
408 -- this is a Java subscription. Return true
409 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
410 wf_log_pkg.string(wf_log_pkg.level_statement,
411 'wf.plsql.WF_EVENT.isSaveToJavaError.Subscription',
412 'found a Java subscription');
413 end if;
414 return true;
415 end if;
416 return false;
417 end isSaveToJavaError;
418 /*
419 ** GetSkippedSub - utility function to get the subscription has skip onerror
420 ** type
421 */
422 function GetSkippedSub(p_event in wf_event_t)
423 return RAW is
424 l_skip_sub_str VARCHAR2(300);
425 begin
426 l_skip_sub_str := p_event.GETVALUEFORPARAMETER('SKIP_ERROR_SUB');
427 if (l_skip_sub_str is not null) then
428 return hextoraw(l_skip_sub_str);
429 end if;
430 return null;
431 end GetSkippedSub;
432
433 ------------------------------------------------------------------------
434 /*
435 ** dispatch_internal (PRIVATE)
436 */
437 FUNCTION dispatch_internal(p_source_type in varchar2,
438 p_rule_data in varchar2,
439 p_rule_func in varchar2,
440 p_sub_guid in raw,
441 p_source_agent_guid in raw,
442 p_phase in number,
443 p_priority in number,
444 p_event in out nocopy wf_event_t,
445 p_on_error in varchar2)
446 return varchar2
447 is
448 res varchar2(20);
449 cmd varchar2(1000);
450 stat varchar2(10);
451 myfunc varchar2(240);
452 saved boolean := FALSE;
453 eguid raw(16);
454 defagent wf_agent_t;
455 subphase number;
456 genmsg boolean := FALSE;
457 max_threshold_reached boolean := FALSE;
458 defer_to_java boolean := FALSE;
459 save_to_java_error boolean := FALSE;
460 l_skip_sub raw(16);
461 trig_savepoint exception;
462 pragma exception_init(trig_savepoint, -04092);
463 empty_lob_locator exception;
464 pragma exception_init (empty_lob_locator, -22275);
465 msg clob;
466 -- Bug2786192
467 l_rule_func VARCHAR2(240);
468 executed boolean;
469 begin
470 -- Verify if the subscription is eligible for execution.
471 if ((wf_event.local_system_status = 'ENABLED'
472 OR wf_event.local_system_status = p_source_type)
473 OR (wf_event.local_system_status <> 'DISABLED'
474 AND p_source_type = 'ERROR'))
475 AND (p_phase >= wf_event.phase_minthreshold) then
476
477 --Bug 2519183
478 --If we are in the deferred processing of an
479 --event we will set back the minthreshold so that its
480 --possible to raise active events within this subscription.
481 --This is after all processed subscriptions have been
482 --discarded hence its fine to set the phase_minthreshold
483 --to zero.
484
485 if ((wf_event.phase_minthreshold > 0) AND
486 (p_event.from_agent is NOT NULL)) then
487 --Bug 3451981
488 --In the case of EXTERNAL events the from_agent will
489 --not be WF_DEFERRED , in this case no further event
490 --executions are possible with any lower phases
491 --(though logically there is No DEFERRED within DEFERRED
492 --and correspondingly here phase of > 100 or not works the
493 --same way so u can always workaround with higher phases
494 --but not good on design)
495 --So we can possibly check if it is 'EXTERNAL'
496 --and as we know from the check of min_threshold
497 --that we are deferprocessing lets re-set it.
498 if ((p_event.from_agent.getName = 'WF_DEFERRED') OR (p_source_type = 'EXTERNAL')) then
499 wf_event.phase_minthreshold := 0;
500 end if ;
501 end if;
502
503 --
504 -- mjc Check if reached the phase threshold and should defer
505 -- If we have encountered a deferred subscription, we will
506 -- get the hell out of Dodge City (aka exiting the loop)
507 -- We should not defer any messages being processed from
508 -- the deferred queue.
509 --
510 -- YOHUANG Bug 4227307
511 -- Error Subscription should never be deferred.
512 if (wf_event.phase_maxthreshold is not null) AND
513 (p_phase >= wf_event.phase_maxthreshold) AND
514 (p_source_type <> 'ERROR')
515 then
516 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
517 wf_log_pkg.string(wf_log_pkg.level_statement,
518 'wf.plsql.WF_EVENT.dispatch_internal.Subscription',
519 'max threshold reached');
520 end if;
521 max_threshold_reached := true;
522 end if;
523
524 -- check if event needs to be deferred to WF_JAVA_DEFERRED queue
525 if (p_source_type <> 'ERROR') then
526 defer_to_java := isDeferToJava(max_threshold_reached,
527 p_event.getEventName(),
528 p_source_type, p_rule_func,
529 p_rule_data,p_phase);
530 else
531 save_to_java_error := isSaveToJavaError(p_event.getEventName(),
532 p_rule_func);
533 end if;
534
535 if(defer_to_java OR max_threshold_reached ) then
536 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
537 wf_log_pkg.string(wf_log_pkg.level_statement,
538 'wf.plsql.WF_EVENT.dispatch_internal.Subscription',
539 'detected as deferred');
540 end if;
541 --
542 -- mjc Set action priority now, if we defer, this can be used
543 -- as the priority of the dequeue off the deferred queue
544 --
545 p_event.priority := p_priority;
546
547 -- Set the Deferred Subscription into the event
548 -- We will use this to figure out which phase we will start
549 -- off with during deferred processing
550 p_event.error_subscription := p_sub_guid;
551
552 -- set #CURRENT_PHASE <<sstomar : bug 5870400 >>
553 -- When Last Subscription GUID exist in Event payload but that Subscription
554 -- is not Active/Enabled now. So in such case, Agent Processor doesn't know
555 -- what phase value it should set for Dispatcher to start subscription processing
556 -- (i.e. from which subscription).
557 -- So in such case, Value of #CURRENT_PHASE will be used as starting phase.
558 p_event.AddParameterToList('#CURRENT_PHASE', p_phase);
559
560 if(defer_to_java) then
561 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
562 wf_log_pkg.string(wf_log_pkg.level_statement,
563 'wf.plsql.WF_EVENT.dispatch_internal.Subscription',
564 'deferring to WF_JAVA_DEFERRED');
565 end if;
566 wf_event.DeferEventToJava(p_source_type, p_event);
567 else
568 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
569 wf_log_pkg.string(wf_log_pkg.level_statement,
570 'wf.plsql.WF_EVENT.dispatch_internal.Subscription',
571 'deferring to WF_DEFERRED');
572 end if;
573 wf_event.deferevent(p_source_type, p_event);
574 end if;
575 res := 'DEFER';
576
577 elsif (save_to_java_error) then
578 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
579 wf_log_pkg.string(wf_log_pkg.level_statement,
580 'wf.plsql.WF_EVENT.dispatch_internal.Subscription',
581 'save to java error queue');
582 end if;
583 p_event.error_subscription := p_sub_guid;
584 wf_event.saveErrorToJavaQueue(p_event);
585 res := 'DEFER';
586 else
587 -- Generate Method is irrelavant to subscription.
588 -- Exception happened in Generate Method should be thrown
589 -- up.
590 --
591 -- mjc Check if we are required to Generate Message
592 -- Use dbms_lob.istemporary to see if any clob
593 --
594 begin
595 if (NOT genmsg) AND
596 (p_rule_data = 'MESSAGE') AND
597 (p_source_type = 'LOCAL') then
598 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
599 wf_log_pkg.string(wf_log_pkg.level_statement,
600 'wf.plsql.WF_EVENT.dispatch_internal.Rule_Data',
601 'Rule Data is MESSAGE and Source is LOCAL');
602 end if;
603 -- if dbms_lob.istemporary(p_event.GetEventData()) = 0 then
604 -- Bug Fix for 4286207
605 begin
606 if (p_event.GetEventData() is null) or
607 (p_event.GetEventData() is not null and dbms_lob.getlength(p_event.GetEventData()) = 0) then
608 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
609 wf_log_pkg.string(wf_log_pkg.level_statement,
610 'wf.plsql.WF_EVENT.dispatch_internal.generate',
611 'Need to Generate Message');
612 end if;
613 wf_event.setMessage(p_event);
614 genmsg := TRUE;
615 end if;
616 exception
617 when empty_lob_locator then
618 -- The lob locator is invalid, probably pointing to a empty_clob();
619 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
620 wf_log_pkg.string(wf_log_pkg.level_statement,
621 'wf.plsql.WF_EVENT.dispatch_internal.generate',
622 'Invalid Lob Locator Passed, Generate the Message');
623 end if;
624 wf_event.setMessage(p_event);
625 genmsg := TRUE;
626 end;
627 end if;
628 exception
629 when others then
630 rollback to wf_dispatch_savepoint;
631 p_event.setErrorSubscription(p_sub_guid);
632 wf_event.wf_exception_source := 'RULE';
633 raise;
634 end;
635
636 --If we came here now we can start processing the
637 --subscription
638 --Set the savepoint here for the SKIP mode and not for error
639 --subscriptions
640 if (p_on_error = 'SKIP' and p_source_type <> 'ERROR') then
641 -- trig_savepoint shall not happen otherwise
642 -- the event level savepoint can't be created.
643 savepoint wf_dispatch_internal;
644 end if;
645
646 -- If we are in the process of only executing the skipped
647 -- subscription, we should remove this parameter because
648 -- otherwise there will be indefinite loop in case of
649 -- nested raise.
650 l_skip_sub := GetSkippedSub(p_event);
651
652 if (l_skip_sub is not null AND
653 p_source_type = 'LOCAL') then
654 p_event.AddParameterToList('SKIP_ERROR_SUB', null);
655 end if;
656
657 --
658 -- If there's a rule function defined, run it. Otherwise
659 -- just execute the default dispatch functionality
660 --
661 begin
662
663 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
664 wf_log_pkg.string2(wf_log_pkg.level_statement,
665 'wf.plsql.wf_event.dispatch_internal.rulefunc_callout',
666 'Start executing rule function - '||p_rule_func, true);
667 end if;
668
669 if (p_rule_func is not null) then
670 WF_BES_DYN_FUNCS.RuleFunction(p_rule_func,
671 p_sub_guid,
672 p_event,
673 res,
674 executed);
675 if (not executed) then
676 -- p_rule_func came from WF_EVENT_SUBSCRIPTIONS.Rule_Function or
677 -- WF_EVENT_SUBSCRIPTIONS.Java_Rule_Func
678 myfunc := p_rule_func;
679 -- BINDVAR_SCAN_IGNORE
680 cmd := 'begin :v1 := '||myfunc||'(:v2, :v3); end;';
681 execute immediate cmd using in out res,
682 in p_sub_guid,
683 in out p_event;
684 end if;
685 end if;
686
687 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
688 wf_log_pkg.string2(wf_log_pkg.level_statement,
689 'wf.plsql.wf_event.dispatch_internal.rulefunc_callout',
690 'End executing rule function - '||p_rule_func, false);
691 end if;
692
693 exception
694 when others then
695 if (p_on_error = 'SKIP') then
696 -- Unexpected Exception is treated the same as ERROR
697 -- if the subscription is marked as SKIP
698 res := 'ERROR';
699 else
700 rollback to wf_dispatch_savepoint;
701 p_event.setErrorSubscription(p_sub_guid);
702 p_event.addParameterToList('ERROR_TYPE', 'UNEXPECTED');
703 wf_event.wf_exception_source := 'RULE';
704 WF_CORE.Token('ENAME', p_event.event_name);
705 WF_CORE.Token('EKEY', p_event.event_key);
706 WF_CORE.Token('RULE', myfunc);
707 WF_CORE.Token('SQLCODE', to_char(sqlcode));
708 WF_CORE.Token('SQLERRM', sqlerrm);
709 WF_CORE.Raise('WFE_DISPATCH_RULE_ERR');
710 end if;
711
712 end;
713
714 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
715 -- BINDVAR_SCAN_IGNORE[3]
716 wf_log_pkg.string(wf_log_pkg.level_statement,
717 'wf.plsql.WF_EVENT.dispatch_internal.rule_function',
718 'Executed Rule Function '||myfunc||' and returned '||res);
719 end if;
720
721 end if; -- End of "defer_to_java OR max_threshold_reached"
722 else
723 -- Bug 4227307
724 -- Handle the subscriptions that are not eligible for execution.
725 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
726 wf_log_pkg.string(wf_log_pkg.level_statement,
727 'wf.plsql.wf_event.dispatch_internal.subs_ignore',
728 'Subscription not executed. Sub Phase='||p_phase||
729 ', Min threshold='||wf_event.phase_minthreshold);
730 end if;
731 return 'SUCCESS';
732 end if;
733
734 if (res = 'DEFER' OR res = 'SUCCESS') then
735 return res;
736 end if;
737
738 p_event.addParameterToList('ERROR_TYPE', res);
739 p_event.setErrorSubscription(p_sub_guid);
740
741 if (p_source_type = 'ERROR') then
742 -- If subscription returns error when listener listens on error agent.
743 -- Bug 4207885: Set the exception source to Rule when
744 -- Raising exceptions.
745 wf_event.wf_exception_source := 'RULE';
746 wf_core.token('ENAME', p_event.event_name);
747 wf_core.token('EKEY', p_event.event_key);
748 wf_core.token('SQLERRM', null);
749 wf_core.token('SQLCODE', null);
750 wf_core.raise('WFE_DISPATCH_RULE_ERR');
751 end if;
752
753 if (res = 'ERROR') then
754
755 if (WF_EVENT.g_message_grouping = 'TRANSACTIONAL') then
756 raise dispatch_error;
757 end if;
758
759 begin
760 if (p_on_error = 'SKIP' and p_source_type <> 'ERROR') then
761 rollback to wf_dispatch_internal;
762 p_event.AddParameterToList('SKIP_ERROR_SUB', rawtohex(p_sub_guid));
763 res := 'SKIP_ERROR';
764 else
765 rollback to wf_dispatch_savepoint;
766 end if;
767 exception
768 when NO_SAVEPOINT then
769 -- Bug 1840819
770 -- Catch the no savepoint exception incase commit has happened.
771 -- In this case, the subsequent subscriptions can't be executed
772 -- because is savepoint no longer valid.
773 wf_core.token('EVENT',p_event.getEventName());
774 p_event.setErrorMessage(wf_core.substitute('WFERR',
775 'WFE_COMMIT_IN_DISPATCH'));
776 res := 'TRANSACTION_COMMITED';
777 end;
778 end if;
779
780
781 wf_event.saveErrorToQueue(p_event);
782 p_event.AddParameterToList('SKIP_ERROR_SUB', null);
783 return (res);
784
785 exception
786 when others then
787 wf_core.context('Wf_Event', 'dispatch_internal');
788 raise;
789 end;
790 ---------------------------------------------------------------------------
791 /*
792 ** newAgent - <described in WFEVENTS.pls>
793 */
794 FUNCTION newAgent(p_agent_guid in raw) return wf_agent_t
795 is
796 agt_name varchar2(30);
797 sys_name varchar2(30);
798
799 -- wf bes cache implementation
800 l_agent_obj wf_agent_obj;
801 begin
802 if (p_agent_guid is null) then
803 return null;
804 end if;
805
806 l_agent_obj := wf_bes_cache.GetAgentByGUID(p_agent_guid);
807
808 if (l_agent_obj is not null) then
809 return wf_agent_t(l_agent_obj.NAME, l_agent_obj.SYSTEM_NAME);
810 else
811 wf_core.raise('WFE_AGENT_NOTEXIST');
812 end if;
813 exception
814 when others then
815 wf_core.context('Wf_Event', 'newAgent', p_agent_guid);
816 raise;
817 end;
818 ---------------------------------------------------------------------------
819 /*
820 ** test - <Described in WFEVENTS.pls>
821 */
822 FUNCTION test(p_event_name in varchar2) return varchar2
823 is
824 event_guid raw(16);
825 result varchar2(10) := 'NONE';
826
827 -- bes caching implementation
828 l_event_obj wf_event_obj;
829 l_subs_list wf_event_subs_tab;
830
831 begin
832
833 l_event_obj := wf_bes_cache.GetEventByName(p_event_name);
834
835 -- if event is not found or no subscriptions to the event, return NONE
836 result := 'NONE';
837 if (l_event_obj is not null) then
838 l_subs_list := wf_bes_cache.GetSubscriptions(p_event_name, 'LOCAL', null);
839
840 if (l_subs_list is not null) then
841 result := 'KEY';
842 for i in 1..l_subs_list.COUNT loop
843 if (l_subs_list(i).RULE_DATA = 'MESSAGE') then
844 result := 'MESSAGE';
845 exit;
846 end if;
847 end loop;
848 end if;
849 end if;
850 return result;
851
852 exception
853 when others then
854 wf_core.context('Wf_Event', 'Test', p_event_name);
855 raise;
856 end;
857 ---------------------------------------------------------------------------
858 /*
859 ** send - <Described in WFEVENTS.pls>
860 */
861 PROCEDURE send(p_event in out nocopy wf_event_t) is
862 outguid WF_AGENTS.GUID%TYPE;
863 toagtname WF_AGENTS.NAME%TYPE;
864 toagtsys WF_SYSTEMS.NAME%TYPE;
865 outagtname WF_AGENTS.NAME%TYPE;
866 outagtsys WF_SYSTEMS.NAME%TYPE;
867 l_to_type WF_AGENTS.TYPE%TYPE;
868 -- l_mem_agt_name WF_AGENTS.NAME%TYPE;
869 l_to_queue_handler WF_AGENTS.QUEUE_HANDLER%TYPE;
870
871 -- wf bes cache implementation
872 l_to_agt_obj wf_agent_obj;
873 l_out_agt_obj wf_agent_obj;
874 begin
875 --
876 -- if from_agent is null, pick one from the local system that uses
877 -- the same queue handler (i.e.: same event type)
878 --
879 -- Note: someday, when we support agent groups, we'd loop through
880 -- the group members and derive a list of proper out agents.
881 --
882 if (p_event.GetToAgent() is null AND p_event.GetFromAgent() is null) then
883 -- Either source or destination must be defined.
884 -- Raise Error.
885 wf_core.context('Wf_Event', 'Send', p_event.getEventName());
886 wf_core.raise('Either source or destination must be defined.'); -- wfsql.msg
887 end if;
888
889 if p_event.GetToAgent() is not null then
890 toagtname := p_event.getToAgent().getName();
891 toagtsys := p_event.getToAgent().getSystem();
892 end if;
893
894 if p_event.GetFromAgent() is not null then
895 outagtname := p_event.getFromAgent().getName();
896 outagtsys := p_event.getFromAgent().getSystem();
897 end if;
898
899 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
900 wf_log_pkg.string(wf_log_pkg.level_statement,
901 'wf.plsql.WF_EVENT.Send.info',
902 'To Agt: '||toagtname||' To Agt Sys:'||toagtsys||
903 'Out Agt: '||outagtname||' Out Agt Sys:'||outagtsys);
904 end if;
905
906 -- enqueue() requires that FromAgent not null.
907 -- pick one from the local system that uses the same queue handler (i.e.: same event type)
908 -- as toAgent uses.
909 -- If the toAgent is an agent group, we assume that agent group
910 -- only contain one type of agents.
911 if (p_event.getFromAgent() is null OR
912 p_event.getFromAgent().getName() is null OR
913 p_event.getFromAgent().getSystem() is null) then
914
915 l_to_agt_obj := wf_bes_cache.GetAgentByName(toagtname, toagtsys);
916 if (l_to_agt_obj is not null and l_to_agt_obj.STATUS = 'ENABLED') then
917 l_to_type := l_to_agt_obj.TYPE;
918 l_to_queue_handler := l_to_agt_obj.QUEUE_HANDLER;
919 else
920 raise no_data_found;
921 end if;
922
923 IF (l_to_type = 'GROUP') THEN
924 l_to_queue_handler := NULL;
925 for r_group_members in agent_group_members(toagtname, toagtsys) loop
926 l_to_queue_handler := r_group_members.queue_handler;
927 exit;
928 end loop;
929 if (l_to_queue_handler = null) then
930 raise no_data_found;
931 end if;
932 END IF;
933
934 l_out_agt_obj := wf_bes_cache.GetAgentByQH(l_to_queue_handler, 'OUT');
935
936 if (l_out_agt_obj is not null and l_out_agt_obj.STATUS = 'ENABLED') then
937 outguid := l_out_agt_obj.GUID;
938 outagtname := l_out_agt_obj.NAME;
939 else
940 raise no_data_found;
941 end if;
942
943 p_event.setFromAgent(wf_event.newAgent(outguid));
944 end if;
945
946 if (p_event.getSendDate() is NULL) then
947 p_event.setSendDate(sysdate);
948 end if;
949
950 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
951 wf_log_pkg.string(wf_log_pkg.level_statement,
952 'wf.plsql.WF_EVENT.send.enqueue',
953 'Sending from '||outagtname||' to '||toagtname);
954 end if;
955
956 wf_event.enqueue(p_event);
957 exception
958 when no_data_found then
959 wf_core.context('Wf_Event', 'Send', p_event.getEventName());
960 wf_core.token('AGENT', toagtname);
961 wf_core.raise('WFE_AGENT_NOMATCH');
962 when others then
963 wf_core.context('Wf_Event', 'Send', p_event.getEventName());
964 raise;
965 end;
966 ---------------------------------------------------------------------------
967 /*
968 ** Given out agent and to agent info,
969 ** Figure out the recipient address.
970 ** PRIVATE: Only called by Set_Recipient_List.
971 ** Assumption: There will be no group under group, so the to agent
972 ** passed here must have type AGENT.
973 */
974 FUNCTION Get_Recipient(p_out_agent_name in varchar2,
975 p_out_system_name in varchar2,
976 p_to_agent_name in varchar2,
977 p_to_system_name in varchar2,
978 p_out_queue_handler in varchar2)
979 return sys.aq$_agent is
980 l_to_address WF_AGENTS.ADDRESS%TYPE;
981 l_to_protocol WF_AGENTS.PROTOCOL%TYPE;
982 l_to_queue_name WF_AGENTS.QUEUE_NAME%TYPE;
983 l_to_protocol_number NUMBER := 0; -- Hard code as SQLNET
984
985 -- wf bes cache implementation
986 l_to_agt_obj wf_agent_obj;
987 BEGIN
988
989 -- MJC: We need to make sure the recipient address is in the correct
990 -- format otherwise dequeue will not work.
991 --
992 -- Rule 1: Local consumer dequeues from same queue as enqueued
993 -- --> Address must be null
994 -- Rule 2: Propagating to local queue
995 -- --> Address must be <schema>.<queue_name>
996 -- Rule 3: Propagating to local database
997 -- --> Address must be <schema>.<queue_name>@dblink
998
999 -- YOHUANG: Different Sql queries for different case to
1000 -- improve performance.
1001
1002 l_to_agt_obj := wf_bes_cache.GetAgentByName(p_to_agent_name, p_to_system_name);
1003 if (l_to_agt_obj is not null and l_to_agt_obj.STATUS = 'ENABLED') then
1004 l_to_protocol := l_to_agt_obj.PROTOCOL;
1005 l_to_queue_name := l_to_agt_obj.QUEUE_NAME;
1006 else
1007 return null;
1008 end if;
1009
1010 if (p_to_agent_name = p_out_agent_name and
1011 p_to_system_name = p_out_system_name) then
1012 l_to_address := null;
1013 elsif (p_to_agent_name <> p_out_agent_name and
1014 p_to_system_name = p_out_system_name) then
1015 l_to_address := l_to_queue_name;
1016 -- Bug 7671184 - If message is intended for a remote system use
1017 -- the address of To Agent as To Address for msg propagation
1018 elsif (p_to_system_name <> p_out_system_name) then
1019 l_to_address := l_to_agt_obj.ADDRESS;
1020 end if;
1021
1022 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1023 wf_log_pkg.string(wf_log_pkg.level_statement,
1024 'wf.plsql.WF_EVENT.Get_Recipient',
1025 'Recipient is name: ' || p_to_agent_name ||
1026 ' address: ' || l_to_address || ' protocol: ' ||l_to_protocol);
1027 end if;
1028
1029 -- Here is where we will add additional protocol mappings as AQ
1030 -- supports new protocols. This bit will form a hardcoded mapping
1031 -- from protocol names as used in the event manager and the
1032 -- protocol numbers used by AQ.
1033
1034 if((l_to_protocol is null) or (l_to_protocol not in ('SQLNET'))) then
1035 wf_core.context('WF_EVENT', 'Get_Recipient', 'Bad Protocol',
1036 l_to_protocol, l_to_queue_name);
1037 end if;
1038
1039 return sys.aq$_agent(p_to_agent_name,
1040 l_to_address,
1041 l_to_protocol_number);
1042 exception
1043 WHEN no_data_found THEN
1044 -- It means that to agent and out agent are not of same type
1045 RETURN null;
1046 end Get_Recipient;
1047
1048 /*
1049 ** If the ToAgent of event is not null(send), set the recipient list
1050 ** address.
1051 ** Only be called if the event.getToAgent() is not null.
1052 */
1053 PROCEDURE Set_Recipient_List(p_event in wf_event_t,
1054 p_out_agent_name in varchar2,
1055 p_out_system_name in varchar2,
1056 x_message_properties in out nocopy dbms_aq.message_properties_t) IS
1057 l_to_type WF_AGENTS.TYPE%TYPE;
1058 l_to_agent_name WF_AGENTS.NAME%TYPE;
1059 l_to_system_name WF_SYSTEMS.NAME%TYPE;
1060 l_out_queue_handler WF_AGENTS.QUEUE_HANDLER%TYPE;
1061 l_recipient_agent sys.aq$_agent;
1062 --Bug 2676549
1063 i number := 0;
1064
1065 -- bes cache implementation
1066 l_to_agt_obj wf_agent_obj;
1067 l_out_agt_obj wf_agent_obj;
1068 BEGIN
1069
1070 -- Ignore if to agent is not set or the out agent is DEFERRED.
1071 if ((p_event.getToAgent() is null) or (p_out_agent_name = 'WF_DEFERRED')) then
1072 return;
1073 end if;
1074
1075 -- if there is a to queue, we need to set the recipient list address
1076 l_to_agent_name := p_event.getToAgent().getName();
1077 l_to_system_name := p_event.getToAgent().getSystem();
1078
1079 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1080 wf_log_pkg.string(wf_log_pkg.level_statement,
1081 'wf.plsql.WF_EVENT.setRecieptList',
1082 'Setting Recipient List: ' || l_to_agent_name);
1083 end if;
1084
1085 l_to_agt_obj := wf_bes_cache.GetAgentByName(l_to_agent_name, l_to_system_name);
1086 if (l_to_agt_obj is not null and l_to_agt_obj.STATUS = 'ENABLED') then
1087 l_to_type := l_to_agt_obj.TYPE;
1088 else
1089 raise no_data_found;
1090 end if;
1091
1092 l_out_agt_obj := wf_bes_cache.GetAgentByName(p_out_agent_name, p_out_system_name);
1093 if (l_out_agt_obj is not null and l_out_agt_obj.STATUS = 'ENABLED') then
1094 l_out_queue_handler := l_out_agt_obj.QUEUE_HANDLER;
1095 else
1096 raise no_data_found;
1097 end if;
1098
1099 if (l_to_type = 'GROUP') then
1100 --Get the agent / agent group from the cursor
1101 for to_agent in agent_group_members(l_to_agent_name,l_to_system_name) loop
1102 l_recipient_agent := Get_Recipient(p_out_agent_name,
1103 p_out_system_name,
1104 to_agent.agt_name,
1105 l_to_system_name,
1106 l_out_queue_handler);
1107 if (l_recipient_agent IS NOT NULL) then
1108 i:= i + 1;
1109 x_message_properties.recipient_list(i) := l_recipient_agent;
1110 end if;
1111 end loop;
1112 if (i = 0) then
1113 -- There is no qualified agents under this agent group
1114 raise no_data_found;
1115 end if;
1116 else
1117 l_recipient_agent := Get_Recipient(p_out_agent_name,
1118 p_out_system_name,
1119 l_to_agent_name,
1120 l_to_system_name,
1121 l_out_queue_handler);
1122 if (l_recipient_agent IS NOT NULL) then
1123 x_message_properties.recipient_list(1) := l_recipient_agent;
1124 else
1125 -- No qualified agent
1126 raise no_data_found;
1127 end if;
1128 end if ;
1129
1130 END Set_Recipient_List;
1131
1132
1133
1134 /*
1135 ** raise - <Described in WFEVENTS.pls>
1136 */
1137 PROCEDURE raise(p_event_name in varchar2,
1138 p_event_key in varchar2,
1139 p_event_data in clob,
1140 p_parameters in wf_parameter_list_t,
1141 p_send_date in date) is
1142
1143
1144 l_parameters wf_parameter_list_t;
1145
1146 begin
1147 --If parameter list is null then raise the event
1148 --with a dummy parameter list as raise3 requires
1149 --the parameterlist to be input.Else use the input
1150 --parameter list.
1151 if (p_parameters is NOT NULL) then
1152 l_parameters := p_parameters;
1153 else
1154 l_parameters := wf_parameter_list_t();
1155 end if;
1156 --Raise the event
1157 wf_event.raise3(p_event_name,
1158 p_event_key,
1159 p_event_data,
1160 l_parameters,
1161 p_send_date);
1162 exception
1163 when others then
1164 raise;
1165 end;
1166 ---------------------------------------------------------------------------
1167 /*
1168 ** listen - <Described in WFEVENTS.pls>
1169 */
1170 PROCEDURE listen(p_agent_name in varchar2,
1171 p_wait in binary_integer,
1172 p_correlation in varchar2,
1173 p_deq_condition in varchar2) is
1174
1175 l_msg_count NUMBER := 0;
1176 l_err_count NUMBER := 0;
1177 begin
1178
1179 listen(p_agent_name,
1180 p_wait,
1181 p_correlation,
1182 p_deq_condition,
1183 l_msg_count,
1184 l_err_count);
1185
1186 end;
1187 /*
1188 ** listen - <Described in WFEVENTS.pls>
1189 */
1190 PROCEDURE listen(p_agent_name in varchar2,
1191 p_wait in binary_integer,
1192 p_correlation in varchar2,
1193 p_deq_condition in varchar2,
1194 p_message_count in out nocopy number,
1195 p_max_error_count in out nocopy number) is
1196
1197 from_agt_name varchar2(30);
1198 from_agt_sys varchar2(30);
1199 st varchar2(10) := 'LOCAL';
1200 dir varchar2(10);
1201 qh varchar2(240);
1202 stat varchar2(10);
1203 agt raw(16);
1204 sagt raw(16);
1205 evt wf_event_t;
1206 evt_name VARCHAR2(240); /* Bug 2032654 */
1207 evt_errmsg VARCHAR2(4000);
1208 l_lsn_msg_count NUMBER := 0; -- Local Variable, reset everytime the method is called.
1209 l_error_count NUMBER := 0;
1210
1211 l_queue_name varchar2(80);
1212 q_name VARCHAR2(80);
1213 l_enqueue_enabled VARCHAR2(30);
1214 l_dequeue_enabled VARCHAR2(30);
1215 l_queueTable VARCHAR2(30); --<rwunderl:2749563/)
1216
1217 -- bes caching implementation
1218 l_agent_obj wf_agent_obj;
1219
1220 begin
1221
1222 -- return an error if listen is invoked on WF_JAVA_DEFERRED or
1223 -- WF_JAVA_ERROR agents
1224 if (p_agent_name = 'WF_JAVA_DEFERRED') OR
1225 (p_agent_name = 'WF_JAVA_ERROR') then
1226 /**
1227 * ToDo: This message should be translated
1228 */
1229 return;
1230 end if;
1231
1232 -- Validate the msg count and error count parameters
1233 if (p_message_count is null or p_message_count < 0) then
1234 p_message_count := 0;
1235 end if;
1236
1237 if (p_max_error_count is null or p_max_error_count < 0) then
1238 p_max_error_count := 0;
1239 end if;
1240
1241 if ((p_correlation is not NULL) and (p_deq_condition is not NULL)) then
1242 WF_CORE.Context('WF_EVENT', 'Listen', p_agent_name, p_correlation,
1243 p_deq_condition);
1244 WF_CORE.Raise('WFE_CORRID_VS_CONDITION');
1245
1246 end if;
1247
1248 -- lookup agent info --
1249 --<rwunderl:2749563> Tuned and separated sql statement for better performance
1250 --<rwunderl:2792298> restricting Listen() to local system.
1251 if (WF_EVENT.g_local_system_guid is NULL) then
1252 g_local_system_guid := hextoraw(WF_CORE.Translate('WF_SYSTEM_GUID'));
1253 end if;
1254
1255 -- get the agent information for the local system
1256 l_agent_obj := wf_bes_cache.GetAgentByName(p_agent_name, null);
1257 if (l_agent_obj is not null) then
1258 agt := l_agent_obj.GUID;
1259 dir := upper(l_agent_obj.DIRECTION);
1260 else
1261 raise no_data_found;
1262 end if;
1263
1264 --</rwunderl:2749563>
1265 --Bug 2307433, 3271311, Use StartAgent as single Source of truth
1266 -- StartAgent becomes the single source to start a particular agent
1267 -- and can be called without worrying about fixed name queues.
1268 StartAgent(p_agent_name);
1269
1270 if (WF_EVENT.g_message_grouping = 'TRANSACTIONAL') then
1271 if ((p_correlation is NULL) and (p_deq_condition is NULL)) then
1272 --This is a transactional queue, we will go ahead and call
1273 --the proper api.
1274 WF_EVENT.Listen_GRP(p_agent_name, p_wait);
1275 return;
1276
1277 else
1278 --This is a transactional queue, but since there was a correlation id
1279 --passed, we cannot call Listen_GRP, so we will raise an error to the
1280 --caller to resolve.
1281 wf_core.context('Wf_Event', 'Listen', p_agent_name);
1282 wf_core.token('AGENT', p_agent_name);
1283 wf_core.token('API', 'WF_EVENT.Listen_GRP');
1284 wf_core.raise('WFE_TRXN_QUEUE');
1285
1286 end if;
1287 end if;
1288
1289 -- set source type --
1290 if (p_agent_name = 'WF_ERROR') then
1291 st := 'ERROR';
1292 elsif (dir = 'IN') then
1293 st := 'EXTERNAL';
1294 end if;
1295
1296 -- check system status --
1297 -- stat := wf_core.translate('WF_SYSTEM_STATUS');
1298
1299 -- Set the account name - only need this for WF_DEFERRED
1300 wf_event.SetAccountName;
1301
1302 if (wf_event.local_system_status = 'DISABLED') then
1303 return;
1304 end if;
1305
1306 if (wf_event.local_system_status in ('LOCAL','EXTERNAL')) then
1307 if (st = wf_event.local_system_status
1308 OR st = 'ERROR'
1309 OR p_agent_name = 'WF_DEFERRED') then
1310 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1311 wf_log_pkg.string(wf_log_pkg.level_statement,
1312 'wf.plsql.WF_EVENT.listen.Check1',
1313 'Source type is equal to system status '||
1314 'or ERROR or Deferred Processing');
1315 end if;
1316 else
1317 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1318 wf_log_pkg.string(wf_log_pkg.level_statement,
1319 'wf.plsql.WF_EVENT.listen.Check2',
1320 'Source type not valid for current system status');
1321 end if;
1322 return;
1323 end if;
1324 end if;
1325
1326 -- Set the dequeue navigation option to be DBMS_AQ.FIRST_MESSAGE
1327 wf_event.navigation := dbms_aq.first_message;
1328 /*
1329 ** We need to explicitly make sure that if someones Queue Handler
1330 ** blows up we rollback the transaction, just in case they don't
1331 */
1332
1333 begin
1334 savepoint bes_before_dequeue_qh;
1335 wf_event.dequeue(agt, evt, qh, p_wait, p_correlation, p_deq_condition);
1336
1337 exception
1338 when others then
1339 wf_event.wf_exception_source := 'QH';
1340 rollback to bes_before_dequeue_qh;
1341 raise;
1342 end;
1343
1344 -- YOHUANG
1345 -- Add support MAX_LSN_MSG_COUNT and MAX_ERROR_COUNT
1346 -- to mimic JAVA GSC Framework implementation
1347 -- Listen will return back to caller if either MAX NUMBER
1348 -- of messages are read or max number of errors happened.
1349 while (evt is not null) loop
1350 l_lsn_msg_count := l_lsn_msg_count + 1;
1351 if (st <> 'ERROR') then
1352 if (evt.getFromAgent() is null) then
1353 sagt := NULL;
1354 else
1355 from_agt_name := evt.getFromAgent().getName();
1356 from_agt_sys := evt.getFromAgent().getSystem();
1357
1358 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1359 wf_log_pkg.string(wf_log_pkg.level_statement,
1360 'wf.plsql.WF_EVENT.listen',
1361 'Event '||evt.getEventName()||
1362 ', hailing from '||from_agt_name||'@'||from_agt_sys||
1363 ' was dequeued from '||p_agent_name);
1364 end if;
1365 -- get the from agent guid
1366 GetSourceAgentGUID(agent_name => from_agt_name,
1367 agent_system => from_agt_sys,
1368 agent_guid => sagt);
1369 end if;
1370 else
1371 sagt := NULL;
1372
1373 /* Bug 2032654
1374 1. Determine if the event name is null
1375 2. If the event name is NULL, it should be defaulted to
1376 UNEXPECTED_ERROR.
1377 3. The text Event Name is NULL is appended to the error message. */
1378
1379 evt_name := evt.getEventName;
1380
1381 IF evt_name IS NULL THEN
1382 evt.setEventName('UNEXPECTED_ERROR');
1383 evt.setEventKey('UNEXPERR');
1384 evt_errmsg := evt.getErrorMessage || wf_core.newline ||
1385 wf_core.translate('WF_EVTNAME_NULL');
1386 evt.setErrorMessage(wf_core.translate('WF_EVTNAME_NULL'));
1387 END IF;
1388 end if;
1389
1390 --
1391 -- mjc Check if we are doing deferred processing
1392 --
1393
1394 -- Bug 2210085 - starting a new block to capture exceptions thrown
1395 -- by GetDeferEventCtx
1396 begin
1397 if p_agent_name = 'WF_DEFERRED' then
1398 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1399 wf_log_pkg.string(wf_log_pkg.level_statement,
1400 'wf.plsql.WF_EVENT.listen.Deferred',
1401 'Processing Deferred Event');
1402 end if;
1403
1404 wf_event.GetDeferEventCtx(st,from_agt_name,from_agt_sys,evt);
1405 -- dispatcher should process all subscriptions
1406 wf_event.phase_maxthreshold := null;
1407 end if;
1408
1409 begin
1410 -- Dispatcher will throw exception in the following unexpected errors
1411 -- Defer Event Failed
1412 -- Generate Message Failed
1413 -- Rule Function Failed
1414 -- Unable to Save to Error Queue
1415 -- Unable to rollback to savepoint.
1416 -- Listen will swallow all of these exceptions unless the unexpected
1417 -- exception happened when processing error queue
1418
1419 wf_event.dispatch(st, sagt, evt);
1420 l_error_count := 0; -- We only count consecutive unexpected errors
1421 commit;
1422 exception
1423 when others then
1424 if (wf_event.wf_exception_source = 'RULE') then
1425 if (st = 'ERROR') then
1426 if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
1427 wf_log_pkg.string(wf_log_pkg.level_error,
1428 'wf.plsql.WF_EVENT.listen.dispatch_error',
1429 'Rule Function with Source Error Exception');
1430 end if;
1431 raise;
1432 else
1433 if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
1434 wf_log_pkg.string(wf_log_pkg.level_error,
1435 'wf.plsql.WF_EVENT.listen.dispatch_error',
1436 'Rule Function Error');
1437 end if;
1438 wf_event.setErrorInfo(evt, 'ERROR');
1439 wf_event.saveErrorToQueue(evt);
1440 commit;
1441 l_error_count := l_error_count + 1;
1442 end if;
1443 else
1444 if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
1445 wf_log_pkg.string(wf_log_pkg.level_error,
1446 'wf.plsql.WF_EVENT.listen.dispatch_error',
1447 'Non Rule Function Error');
1448 end if;
1449 -- Bug 4207885: Set the exception source to be WF.
1450 wf_event.wf_exception_source := 'WF';
1451 commit;
1452 raise;
1453 end if;
1454 end;
1455 /* Bug 2210085 */
1456 exception
1457 when others then
1458 -- Bug 2608037
1459 -- If the execution of rule function had failed and if the
1460 -- agent is the error queue then there is no point in enqueueing
1461 -- the event again into the error queue.
1462 -- In this case we just rollback the dequeue and raise the
1463 -- exception to the user.
1464 if (( wf_event.wf_exception_source = 'RULE') and (st = 'ERROR')) then
1465 if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
1466 wf_log_pkg.string(wf_log_pkg.level_error,
1467 'wf.plsql.WF_EVENT.listen.dispatch_error',
1468 'Error executing Rule Function with Source Type Error');
1469 end if;
1470 rollback to bes_before_dequeue_qh;
1471
1472 -- Error WFE_DISPATCH_RULE_ERR already has sufficient info on the error. Just raise it
1473 if (wf_core.error_name = 'WFE_DISPATCH_RULE_ERR') then
1474 raise;
1475 else
1476 wf_core.token('ENAME', evt.event_name);
1477 wf_core.token('EKEY', evt.event_key);
1478 wf_core.token('SQLERRM', sqlerrm);
1479 wf_core.token('SQLCODE', sqlcode);
1480 wf_core.raise('WFE_UNHANDLED_ERROR');
1481 end if;
1482 elsif (wf_event.wf_exception_source = 'WF') then
1483 -- Bug 4207885: Add the handler of exception with source WF
1484 if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
1485 wf_log_pkg.string(wf_log_pkg.level_error,
1486 'wf.plsql.WF_EVENT.listen.dispatch_error',
1487 'Unexpected Function Error');
1488 end if;
1489 wf_core.token('ENAME', evt.event_name);
1490 wf_core.token('EKEY', evt.event_key);
1491 wf_core.token('SQLERRM', sqlerrm);
1492 wf_core.token('SQLCODE', sqlcode);
1493 wf_core.raise('WFE_UNHANDLED_ERROR');
1494 else
1495 if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
1496 wf_log_pkg.string(wf_log_pkg.level_error,
1497 'wf.plsql.WF_EVENT.listen.error',
1498 'GetDeferEventCtx Error');
1499 end if;
1500 wf_event.setErrorInfo(evt,'ERROR');
1501 wf_event.saveErrorToQueue(evt);
1502 commit;
1503 l_error_count := l_error_count + 1;
1504 end if;
1505 end;
1506
1507 -- check system status --
1508 stat := wf_core.translate('WF_SYSTEM_STATUS');
1509 if ((stat <> 'ENABLED') AND (st <> stat)) then
1510 exit;
1511 end if;
1512
1513 evt := null;
1514 wf_event.InitPhaseMinThreshold;
1515 wf_event.SetDispatchMode('SYNC');
1516
1517 if (p_message_count > 0 AND l_lsn_msg_count >= p_message_count ) then
1518 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1519 wf_log_pkg.string(wf_log_pkg.level_statement,
1520 'wf.plsql.WF_EVENT.listen.maxMsgCount',
1521 'Read the specified maximum number of messages');
1522 end if;
1523 exit;
1524 end if;
1525
1526 if (p_max_error_count > 0 AND l_error_count >= p_max_error_count ) then
1527 if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
1528 wf_log_pkg.string(wf_log_pkg.level_error,
1529 'wf.plsql.WF_EVENT.listen.maxErrCount',
1530 'Encountered the specified maximum number of errors');
1531 end if;
1532 exit;
1533 end if;
1534
1535 /*
1536 ** We need to explicitly make sure that if someones Queue Handler
1537 ** blows up we rollback the transaction, just in case they don't
1538 */
1539
1540 begin
1541 savepoint bes_before_dequeue_qh;
1542 wf_event.dequeue(agt, evt, qh, p_wait, p_correlation, p_deq_condition);
1543 exception
1544 when others then
1545 wf_event.wf_exception_source := 'QH';
1546 rollback to bes_before_dequeue_qh;
1547 raise;
1548 end;
1549 end loop;
1550 p_message_count := l_lsn_msg_count ; -- return back the actual read message count
1551 p_max_error_count := l_error_count; -- return back the actual error message count
1552 -- Normally l_lsn_msg_count >= l_error_count;
1553 exception
1554 when no_data_found then
1555 if (wf_event.wf_exception_source = 'WF'
1556 OR wf_event.wf_exception_source = 'QH') then
1557 raise;
1558 else
1559 wf_core.context('Wf_Event', 'Listen', p_agent_name);
1560 wf_core.raise('WFE_AGENT_NOTEXIST');
1561 end if;
1562 when others then
1563 wf_core.context('Wf_Event', 'Listen', p_agent_name);
1564 raise;
1565 end;
1566 ---------------------------------------------------------------------------
1567 /*
1568 ** listen_concurrent - <Described in WFEVENTS.pls>
1569 */
1570 --Bug 2505487
1571 --Included the AQ wait parameter for the listen_concurrent
1572
1573 PROCEDURE listen_concurrent(errbuf out nocopy varchar2,
1574 retcode out nocopy varchar2,
1575 p_agent_name in varchar2,
1576 p_correlation in varchar2,
1577 p_deq_condition in varchar2,
1578 p_wait in binary_integer) is
1579 errname varchar2(30);
1580 errmsg varchar2(2000);
1581 errstack varchar2(4000);
1582 l_p_wait binary_integer;
1583 l_correlation varchar2(128);
1584
1585 begin
1586 --Bug 2505487
1587 --Any -ve number for the parameter p_wait in the forms
1588 --is accepted as wait forever.
1589 if (p_wait < 0) then
1590 l_p_wait := dbms_aq.forever ;
1591 else
1592 l_p_wait := p_wait;
1593 end if;
1594
1595 --<rwunderl:2751674>
1596 if (UPPER(p_correlation) = 'NULL') then
1597 l_correlation := NULL;
1598
1599 else
1600 l_correlation := p_correlation;
1601
1602 end if;
1603 --</rwunderl:2751674>
1604
1605 --Bug 2649327
1606 --The deq condition is not used at present for dequeuing
1607 -- Hence setting it to NULL.
1608 wf_event.listen(p_agent_name => p_agent_name,
1609 p_wait => l_p_wait,
1610 p_correlation => l_correlation,
1611 p_deq_condition=>NULL);
1612
1613 -- Return 0 for successful completion --
1614 errbuf := '';
1615 retcode := '0';
1616 exception
1617 when others then
1618 wf_core.get_error(errname, errmsg, errstack);
1619 if (errmsg is not null) then
1620 errbuf := errmsg;
1621 else
1622 errbuf := sqlerrm;
1623 end if;
1624
1625 -- Return 2 for error --
1626 retcode := '2';
1627 end;
1628
1629 ---------------------------------------------------------------------------
1630 /*
1631 ** listen_grp - <Described in WFEVENTS.pls>
1632 */
1633 PROCEDURE listen_grp(p_agent_name in varchar2,
1634 p_wait in binary_integer) is
1635
1636 from_agt_name varchar2(30);
1637 from_agt_sys varchar2(30);
1638 st varchar2(10) := 'EXTERNAL';
1639 qh varchar2(240);
1640 stat varchar2(10);
1641 agt raw(16);
1642 sagt raw(16);
1643 evt wf_event_t;
1644 err_evt wf_event_t;
1645 evt_errmsg VARCHAR2(4000);
1646 l_queueTable VARCHAR2(30); --<rwunderl:2749563/>
1647 end_of_transaction exception;
1648 pragma exception_init (end_of_transaction, -25235);
1649
1650 -- bes caching implementation
1651 l_agent_obj wf_agent_obj;
1652 begin
1653
1654 -- Confirm that p_agent_name includes a transactional queue.
1655 --<rwunderl:2749563> Tuned and separated sql statement for better performance
1656 --<rwunderl:2792298> restricting Listen() to local system.
1657 if (WF_EVENT.g_local_system_guid is NULL) then
1658 g_local_system_guid := hextoraw(WF_CORE.Translate('WF_SYSTEM_GUID'));
1659 end if;
1660
1661 GetAgentDetails(p_agent_name);
1662
1663 -- get agent details for local system
1664 l_agent_obj := wf_bes_cache.GetAgentByName(p_agent_name, null);
1665 if (l_agent_obj is null) then
1666 wf_core.context('Wf_Event', 'Listen_GRP', p_agent_name);
1667 wf_core.raise('WFE_AGENT_NOTEXIST');
1668 end if;
1669
1670 if (WF_EVENT.g_message_grouping <> 'TRANSACTIONAL') then
1671 --This is not a transactional queue.
1672 WF_CORE.Context('Wf_Event', 'Listen_GRP', p_agent_name);
1673 WF_CORE.Token('AGENT', p_agent_name);
1674 WF_CORE.Token('API', 'WF_EVENT.Listen');
1675 WF_CORE.Raise('WFE_NONTRXN_QUEUE');
1676 end if;
1677
1678 --Verifying that the system is not disabled.
1679 if (wf_event.local_system_status = 'DISABLED') then
1680 return;
1681 end if;
1682
1683 -- Set the dequeue navigation option to be DBMS_AQ.FIRST_MESSAGE
1684 wf_event.navigation := dbms_aq.first_message;
1685 /*
1686 ** We need to explicitly make sure that if someones Queue Handler
1687 ** blows up we rollback the transaction, just in case they don't
1688 */
1689 loop --Outer loop to process all transactions.
1690 WF_CORE.Clear; --Clear any tokens that were set from the previous
1691 --transaction dequeue.
1692
1693 begin --We will begin processing a transaction (message group).
1694 savepoint trxn_start;
1695
1696 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1697 wf_log_pkg.string(wf_log_pkg.level_statement,
1698 'wf.plsql.WF_EVENT.Listen_GRP.processing',
1699 'Begin processing transaction');
1700 end if;
1701
1702 begin
1703
1704 -- Dequeue the first message in the transaction
1705 savepoint bes_before_dequeue_qh;
1706 wf_event.dequeue(agt, evt, qh,p_wait);
1707
1708 exception
1709 when end_of_transaction then
1710 if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
1711 wf_log_pkg.string(wf_log_pkg.level_event,
1712 'wf.plsql.WF_EVENT.Listen_GRP.complete',
1713 'End of the transaction');
1714 end if;
1715 wf_event.navigation := dbms_aq.next_transaction;
1716 commit;
1717
1718 when others then
1719 wf_event.wf_exception_source := 'QH';
1720 rollback to bes_before_dequeue_qh;
1721 raise;
1722
1723 end;
1724
1725 while (evt is not null) loop
1726
1727 if (evt.getFromAgent() is null) then
1728 sagt := NULL;
1729 else
1730 from_agt_name := evt.getFromAgent().getName();
1731 from_agt_sys := evt.getFromAgent().getSystem();
1732
1733 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1734 wf_log_pkg.string(wf_log_pkg.level_statement,
1735 'wf.plsql.WF_EVENT.Listen_GRP.event_dequeued',
1736 'Event '||evt.getEventName()||', hailing from '||from_agt_name||'@'||
1737 from_agt_sys|| ' was dequeued from '|| p_agent_name);
1738 end if;
1739
1740 -- get the from agent guid
1741 GetSourceAgentGUID(agent_name => from_agt_name,
1742 agent_system => from_agt_sys,
1743 agent_guid => sagt);
1744 end if;
1745
1746 /* Bug 2032654
1747 1. Determine if the event name is null
1748 2. If the event name is NULL, it should be defaulted to
1749 UNEXPECTED_ERROR.
1750 3. The text Event Name is NULL is appended to the error
1751 message. */
1752
1753
1754 if (evt.getEventName) is NULL then
1755 evt.setEventName('UNEXPECTED_ERROR');
1756 evt.setEventKey('UNEXPERR');
1757 evt_errmsg := evt.getErrorMessage || wf_core.newline ||
1758 wf_core.translate('WF_EVTNAME_NULL');
1759 evt.setErrorMessage(wf_core.translate('WF_EVTNAME_NULL'));
1760 END IF;
1761
1762
1763 -- Begin Dispatching the event message.
1764
1765 begin
1766 wf_event.dispatch(st, sagt, evt);
1767
1768 exception
1769 when others then
1770 if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
1771 wf_log_pkg.string(wf_log_pkg.level_error,
1772 'wf.plsql.WF_EVENT.Listen_GRP.dispatch_error',
1773 'Dispatch Error when dispatching Event '||evt.getEventName);
1774 end if;
1775 raise dispatch_error;
1776
1777 end;
1778
1779 -- check system status --
1780 stat := wf_core.translate('WF_SYSTEM_STATUS');
1781 if ((stat <> 'ENABLED') AND (st <> stat)) then
1782 exit;
1783 end if;
1784
1785 evt := null;
1786 wf_event.InitPhaseMinThreshold;
1787 wf_event.SetDispatchMode('SYNC');
1788
1789 begin
1790 savepoint bes_before_dequeue_qh;
1791 wf_event.dequeue(agt, evt, qh,p_wait);
1792
1793 exception
1794 when end_of_transaction then
1795 if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
1796 wf_log_pkg.string(wf_log_pkg.level_event,
1797 'wf.plsql.WF_EVENT.Listen_GRP.complete',
1798 'End of the transaction');
1799 end if;
1800
1801 wf_event.navigation := dbms_aq.next_transaction;
1802 commit;
1803
1804 when others then
1805 wf_event.wf_exception_source := 'QH';
1806 rollback to bes_before_dequeue_qh;
1807 raise;
1808
1809 end;
1810
1811 end loop;
1812
1813
1814
1815 exception
1816 when dispatch_error then
1817 --Dequeue the rest of the transaction
1818 begin
1819 loop
1820 wf_event.dequeue(agt, err_evt, qh,p_wait);
1821
1822 end loop;
1823
1824 exception
1825 when end_of_transaction then
1826 if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
1827 wf_log_pkg.string(wf_log_pkg.level_event,
1828 'wf.plsql.WF_EVENT.Listen_GRP.complete',
1829 'End of the transaction after dispatch error');
1830 end if;
1831
1832 wf_event.navigation := dbms_aq.next_transaction;
1833
1834 end;
1835
1836 --Rollback the whole transaction, which will update the retry count
1837 --on each message.
1838 rollback to trxn_start;
1839
1840 --We will save the current message to the Error Queue.
1841 if ((evt.getValueForParameter('ERROR_NAME')) is NULL) then
1842 wf_event.setErrorInfo(evt, 'ERROR');
1843
1844 end if;
1845
1846 evt.addParameterToList('DEQUEUE_MODE', 'TRANSACTIONAL');
1847 wf_event.saveErrorToQueue(evt);
1848
1849
1850 when others then
1851 raise;
1852
1853 end;
1854 if (wf_event.navigation = dbms_aq.first_message) then
1855 exit; --Outer Loop.
1856 --The queue handler must have reached the last message and
1857 --reset the navigation back to the beginning.
1858
1859 end if;
1860
1861 end loop; --Outer loop to process all transactions in the queue.
1862 exception
1863 when no_data_found then
1864 if (wf_event.wf_exception_source = 'WF'
1865 OR wf_event.wf_exception_source = 'QH') then
1866 raise;
1867 else
1868 wf_core.context('Wf_Event', 'Listen_GRP', p_agent_name);
1869 wf_core.raise('WFE_AGENT_NOTEXIST');
1870 end if;
1871 when others then
1872 wf_core.context('Wf_Event', 'Listen_GRP', p_agent_name);
1873 raise;
1874 end;
1875
1876 ---------------------------------------------------------------------------
1877 /*
1878 ** listen__grp_concurrent - <Described in WFEVENTS.pls>
1879 */
1880 PROCEDURE listen_grp_concurrent(errbuf out nocopy varchar2,
1881 retcode out nocopy varchar2,
1882 p_agent_name in varchar2) is
1883 errname varchar2(30);
1884 errmsg varchar2(2000);
1885 errstack varchar2(4000);
1886 begin
1887 wf_event.listen_grp(p_agent_name);
1888
1889 -- Return 0 for successful completion --
1890 errbuf := '';
1891 retcode := '0';
1892 exception
1893 when others then
1894 wf_core.get_error(errname, errmsg, errstack);
1895 if (errmsg is not null) then
1896 errbuf := errmsg;
1897 else
1898 errbuf := sqlerrm;
1899 end if;
1900
1901 -- Return 2 for error --
1902 retcode := '2';
1903 end;
1904
1905 ---------------------------------------------------------------------------
1906 /*
1907 ** dispatch - <Described in WFEVENTS.pls>
1908 */
1909 PROCEDURE dispatch(p_source_type in varchar2,
1910 p_source_agent_guid in raw,
1911 p_event in out nocopy wf_event_t)
1912 is
1913 res varchar2(20);
1914 cmd varchar2(1000);
1915 stat varchar2(10);
1916 myfunc varchar2(240);
1917 ename varchar2(240);
1918 saved boolean := FALSE;
1919 subs_found boolean :=FALSE;
1920 eguid raw(16);
1921 l_skip_sub raw(16);
1922 event_count NUMBER;
1923 l_rule_func varchar2(300);
1924 l_source_type wf_event_subscriptions.source_type%type;
1925 l_phase wf_event_subscriptions.phase%type;
1926 l_rule_data wf_event_subscriptions.rule_data%type;
1927 l_priority wf_event_subscriptions.priority%type;
1928 l_on_error wf_event_subscriptions.on_error_code%type;
1929 --Bug 2437354
1930 trig_savepoint exception;
1931 pragma exception_init(trig_savepoint, -04092);
1932
1933 -- bes caching implementation
1934 l_event_name varchar2(240);
1935 l_event_obj wf_event_obj;
1936 l_subs_list wf_event_subs_tab;
1937 l_sub wf_event_subs_obj;
1938 begin
1939 l_event_name := p_event.Event_Name;
1940
1941 -- Deleting any previous Event parameter indexes.
1942 WF_EVENT.evt_param_index.DELETE;
1943
1944 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1945 wf_log_pkg.string(wf_log_pkg.level_procedure,
1946 'wf.plsql.WF_EVENT.dispatch.Begin',
1947 'Dispatching event '||l_event_name);
1948 end if;
1949
1950 l_event_obj := wf_bes_cache.GetEventByName(l_event_name);
1951 if (l_event_obj is null) then
1952 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1953 wf_log_pkg.string(wf_log_pkg.level_statement,
1954 'wf.plsql.WF_EVENT.dispatch.no_event',
1955 'Event is either disabled or product not licensed '||
1956 'or event not registered');
1957 end if;
1958 end if;
1959
1960 wf_event.wf_exception_source := 'NONE';
1961
1962 --
1963 -- mjc If Source Type is LOCAL and Phase Threshold is less than zero,
1964 -- the raise is to be deferred. Then we immediately return to
1965 -- calling subprogram
1966 --
1967 -- The wf_event.deferevent is called within a new block
1968 -- Make sure only defer event when event exists.
1969 begin
1970 if (p_source_type = 'LOCAL' AND
1971 (wf_event.phase_maxthreshold < 0 OR p_event.send_date > sysdate) AND
1972 l_event_obj is not null) then
1973
1974 if(isDeferToJava(true, l_event_name, p_source_type,
1975 null, null, null)) then
1976 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1977 wf_log_pkg.string(wf_log_pkg.level_statement,
1978 'wf.plsql.WF_EVENT.dispatch.Defer',
1979 'Detected as deferred raise. Deferring to ' ||
1980 'WF_JAVA_DEFERRED');
1981 end if;
1982 wf_event.DeferEventToJava(p_source_type, p_event);
1983 else
1984 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1985 wf_log_pkg.string(wf_log_pkg.level_statement,
1986 'wf.plsql.WF_EVENT.dispatch.Defer',
1987 'Detected as deferred raise. Deferring to ' ||
1988 'WF_DEFERRED');
1989 end if;
1990 wf_event.deferevent(p_source_type, p_event);
1991 end if;
1992 return;
1993 end if;
1994 exception
1995 when others then
1996 wf_event.setErrorInfo(p_event, 'ERROR');
1997 wf_event.saveErrorToQueue(p_event);
1998 wf_core.context('Wf_Event', 'Dispatch', l_event_name);
1999 raise;
2000 end;
2001
2002 --
2003 -- mjc We have 4 things we need to check for when we are dipatching:
2004 -- 1. Are there any subscriptions for the actual event.
2005 -- 2. Are there any subscriptions to an event group the event belongs to.
2006 -- 3. Are there any subscriptions to the any event.
2007 -- 4. If no subscriptions found in 1,2,3 then check for the unexpected event.
2008 --
2009
2010 -- YOHUANG
2011 -- TODO
2012 -- Since we know whether this event exist or not, if the event doesn't exist
2013 -- We should execute those expense query such as active_subs, we should instead
2014 -- directly execute active_event_subs for ANY event.
2015 /*
2016 ** Events, Event Groups, Any Event
2017 */
2018 --The savepoint is set if the event has not been deferred
2019 begin
2020 savepoint wf_dispatch_savepoint;
2021 exception
2022 when trig_savepoint then
2023 --If the event has not been deferred, defer the event now.
2024 begin
2025 wf_event.deferevent(p_source_type,p_event);
2026 return;
2027 exception
2028 --Incase deferreing of event fails, save the error to queue.
2029 --Should we save the error to queue since it is WF error?
2030 when others then
2031 wf_event.setErrorInfo(p_event, 'ERROR');
2032 wf_event.saveErrorToQueue(p_event);
2033 wf_core.context('Wf_Event', 'Dispatch', l_event_name);
2034 raise;
2035 end;
2036 end;
2037
2038 -- Subscriptions dispatch block
2039 begin
2040 -- Only the skipped subscription should be executed when
2041 -- WFERROR DEFAULT_EVENT_ERROR re-raise the event again.
2042 l_skip_sub := GetSkippedSub(p_event);
2043
2044 if (l_skip_sub is not null AND p_source_type = 'LOCAL') then
2045
2046 l_sub := wf_bes_cache.GetSubscriptionByGUID(l_event_name, l_skip_sub);
2047 if (l_sub is not null) then
2048 res := wf_event.dispatch_internal(p_source_type => l_sub.SOURCE_TYPE,
2049 p_rule_data => l_sub.RULE_DATA,
2050 p_rule_func => trim(l_sub.RULE_FUNCTION),
2051 p_sub_guid => l_skip_sub,
2052 p_source_agent_guid => l_sub.SOURCE_AGENT_GUID,
2053 p_phase => l_sub.PHASE,
2054 p_priority => l_sub.PRIORITY,
2055 p_event => p_event,
2056 p_on_error => l_sub.ON_ERROR_CODE);
2057 end if;
2058 -- Since this is the only sub to be executed, no matter
2059 -- what result it reports, we stop here.
2060 return;
2061 end if;
2062
2063 -- get and dispatch subscriptions to the even only if the event is
2064 -- valid, product is licensed etc. else directly dispatch Any event
2065 if (l_event_obj is not null) then
2066 -- all subscriptions to the event and Any event in the order of phase
2067 l_subs_list := wf_bes_cache.GetSubscriptions(p_event_name => l_event_name,
2068 p_source_type => dispatch.p_source_type,
2069 p_source_agent => dispatch.p_source_agent_guid);
2070 -- dispatching all matching subscriptions for the event
2071 if (l_subs_list is not null) then
2072
2073 for i in 1..l_subs_list.COUNT loop
2074 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2075 wf_log_pkg.string(wf_log_pkg.level_statement,
2076 'wf.plsql.WF_EVENT.dispatch.Subscriptions',
2077 'Dispatching '||l_subs_list(i).SOURCE_TYPE||' subscription '||
2078 'with Phase '||l_subs_list(i).PHASE);
2079 end if;
2080
2081 eguid := l_subs_list(i).GUID;
2082 subs_found := TRUE;
2083
2084 res := wf_event.dispatch_internal(p_source_type => l_subs_list(i).SOURCE_TYPE,
2085 p_rule_data => l_subs_list(i).RULE_DATA,
2086 p_rule_func => trim(l_subs_list(i).RULE_FUNCTION),
2087 p_sub_guid => l_subs_list(i).GUID,
2088 p_source_agent_guid => p_source_agent_guid,
2089 p_phase => l_subs_list(i).PHASE,
2090 p_priority => l_subs_list(i).PRIORITY,
2091 p_event => p_event,
2092 p_on_error => l_subs_list(i).ON_ERROR_CODE);
2093
2094 if res in ('ERROR', 'DEFER', 'TRANSACTION_COMMITED') then
2095 exit;
2096 end if;
2097 end loop;
2098 end if;
2099
2100 -- Event object is null, dispatch only Any event subscriptions
2101 else
2102
2103 -- dispatch subscriptions to Any event. this call does not execute the
2104 -- cursor with union all
2105 l_subs_list := wf_bes_cache.GetSubscriptions(p_event_name => 'oracle.apps.wf.event.any',
2106 p_source_type => dispatch.p_source_type,
2107 p_source_agent => dispatch.p_source_agent_guid);
2108 -- dispatching all matching subscriptions for Any event
2109 if (l_subs_list is not null) then
2110
2111 for i in 1..l_subs_list.COUNT loop
2112 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2113 wf_log_pkg.string(wf_log_pkg.level_statement,
2114 'wf.plsql.WF_EVENT.dispatch.Any',
2115 'Dispatching '||l_subs_list(i).SOURCE_TYPE||' subscription '||
2116 'with Phase '||l_subs_list(i).PHASE);
2117 end if;
2118
2119 eguid := l_subs_list(i).GUID;
2120 subs_found := TRUE;
2121
2122 res := wf_event.dispatch_internal(p_source_type => l_subs_list(i).SOURCE_TYPE,
2123 p_rule_data => l_subs_list(i).RULE_DATA,
2124 p_rule_func => trim(l_subs_list(i).RULE_FUNCTION),
2125 p_sub_guid => l_subs_list(i).GUID,
2126 p_source_agent_guid => p_source_agent_guid,
2127 p_phase => l_subs_list(i).PHASE,
2128 p_priority => l_subs_list(i).PRIORITY,
2129 p_event => p_event,
2130 p_on_error => l_subs_list(i).ON_ERROR_CODE);
2131
2132 if res in ('ERROR', 'DEFER', 'TRANSACTION_COMMITED') then
2133 exit;
2134 end if;
2135 end loop;
2136 end if;
2137 end if;
2138
2139 -- If no subscriptions dispatched, dispatch subscriptions for Unexpected event.
2140 -- Unexpected event is dispatched only for non-workflow events
2141 if ((not subs_found) and (l_event_name not like 'oracle.apps.wf%')) then
2142
2143 l_subs_list := wf_bes_cache.GetSubscriptions(p_event_name => 'oracle.apps.wf.event.unexpected',
2144 p_source_type => dispatch.p_source_type,
2145 p_source_agent => dispatch.p_source_agent_guid);
2146 if (l_subs_list is not null) then
2147
2148 for i in 1..l_subs_list.COUNT loop
2149 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2150 wf_log_pkg.string(wf_log_pkg.level_statement,
2151 'wf.plsql.WF_EVENT.dispatch.Unexpected',
2152 'Dispatching '||l_subs_list(i).SOURCE_TYPE||' subscription '||
2153 'with Phase '||l_subs_list(i).PHASE);
2154 end if;
2155
2156 eguid := l_subs_list(i).GUID;
2157 res := wf_event.dispatch_internal(p_source_type => l_subs_list(i).SOURCE_TYPE,
2158 p_rule_data => l_subs_list(i).RULE_DATA,
2159 p_rule_func => trim(l_subs_list(i).RULE_FUNCTION),
2160 p_sub_guid => l_subs_list(i).GUID,
2161 p_source_agent_guid => p_source_agent_guid,
2162 p_phase => l_subs_list(i).PHASE,
2163 p_priority => l_subs_list(i).PRIORITY,
2164 p_event => p_event,
2165 p_on_error => l_subs_list(i).ON_ERROR_CODE);
2166
2167 if res in ('ERROR', 'DEFER', 'TRANSACTION_COMMITED') then
2168 exit;
2169 end if;
2170 end loop;
2171 end if;
2172 end if;
2173 exception
2174 when others then
2175 -- Unexpected Error happened in dispatch_internal
2176 if (WF_EVENT.g_message_grouping = 'TRANSACTIONAL') then
2177 WF_CORE.Context('Wf_Event', 'Dispatch', l_event_name);
2178 else
2179 rollback to wf_dispatch_savepoint;
2180 p_event.setErrorSubscription(eguid);
2181 if (wf_event.wf_exception_source <> 'RULE') then
2182 wf_event.wf_exception_source := 'WF';
2183 wf_event.setErrorInfo(p_event, 'UNEXPECTED');
2184 -- Unexpected Exception should be thrown up instead of
2185 -- being enqueued to error queue.
2186 -- wf_event.saveErrorToQueue(p_event);
2187 wf_core.context('Wf_Event', 'Dispatch', l_event_name);
2188 end if;
2189 end if;
2190 raise;
2191 end;
2192 end dispatch;
2193
2194
2195 ---------------------------------------------------------------------------
2196 /*
2197 ** enqueue - <Described in WFEVENTS.pls>
2198 */
2199 PROCEDURE enqueue(p_event in wf_event_t,
2200 p_out_agent_override in wf_agent_t)
2201 is
2202 cmd varchar2(1000);
2203 qh varchar2(240);
2204 agtname varchar2(30);
2205 sysname varchar2(30);
2206
2207 -- bes caching implementation
2208 l_agent_obj wf_agent_obj;
2209 p_executed boolean;
2210 begin
2211 /*
2212 ** mjc If an override agent is defined, we should
2213 ** use that agents queue handler
2214 */
2215 if p_out_agent_override is null then
2216 agtname := p_event.From_Agent.Name;
2217 sysname := p_event.From_Agent.System;
2218 else
2219 agtname := p_out_agent_override.GetName();
2220 sysname := p_out_agent_override.GetSystem();
2221 end if;
2222
2223 l_agent_obj := wf_bes_cache.GetAgentByName(agtname, sysname);
2224 if (l_agent_obj is not null) then
2225 qh := l_agent_obj.QUEUE_HANDLER;
2226 end if;
2227
2228 -- Call the static calls implementation first
2229 p_executed := false;
2230
2231 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2232 wf_log_pkg.string2(wf_log_pkg.level_statement,
2233 'wf.plsql.wf_event.enqueue.qhandler_callout',
2234 'Start executing queue handler - '||qh, true);
2235 end if;
2236
2237 WF_AGT_DYN_FUNCS.StaticEnqueue(qh, p_event, p_out_agent_override, p_executed);
2238
2239 if (not p_executed) then
2240 -- qh is from WF_AGENTS.QUEUE_HANDLER
2241 -- BINDVAR_SCAN_IGNORE
2242 cmd := 'begin '||qh||'.enqueue(:v1, :v2); end;';
2243 execute immediate cmd using in p_event,
2244 in p_out_agent_override;
2245 end if;
2246
2247 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2248 wf_log_pkg.string2(wf_log_pkg.level_statement,
2249 'wf.plsql.wf_event.enqueue.qhandler_callout',
2250 'End executing queue handler - '||qh, false);
2251 end if;
2252 exception
2253 when others then
2254 wf_core.context('Wf_Event', 'Enqueue', p_event.getEventName(), qh);
2255 wf_core.token('FUNCTION_NAME', qh||'.enqueue()');
2256 --wf_core.raise('WF_EXT_FUNCTION');
2257 raise;
2258 end;
2259 ---------------------------------------------------------------------------
2260 /*
2261 ** dequeue - <Described in WFEVENTS.pls>
2262 */
2263 PROCEDURE dequeue(p_agent_guid in raw,
2264 p_event out nocopy wf_event_t,
2265 p_queue_handler in out nocopy varchar2,
2266 p_wait in binary_integer,
2267 p_correlation in varchar2,
2268 p_deq_condition in varchar2)
2269 is
2270 qh varchar2(240);
2271 cmd varchar2(1000);
2272 evt_errmsg varchar2(2000);
2273
2274 -- bes caching implementation
2275 l_agent_obj wf_agent_obj;
2276 p_executed boolean;
2277 begin
2278 if (p_queue_handler is null) then
2279 -- get queue details from cache for given agent GUID
2280 l_agent_obj := wf_bes_cache.GetAgentByGUID(p_agent_guid);
2281 if (l_agent_obj is not null) then
2282 qh := l_agent_obj.QUEUE_HANDLER;
2283 end if;
2284 else
2285 -- ### add verification in the future
2286 qh := p_queue_handler;
2287 end if;
2288
2289 -- Set globals so the queue handlers can reference them.
2290 WF_EVENT.g_correlation := p_correlation;
2291 WF_EVENT.g_deq_condition := p_deq_condition;
2292
2293 -- Call the static calls implementation first
2294 p_executed := false;
2295
2296 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2297 wf_log_pkg.string2(wf_log_pkg.level_statement,
2298 'wf.plsql.wf_event.dequeue.qhandler_callout',
2299 'Start executing queue handler - '||qh, true);
2300 end if;
2301
2302 WF_AGT_DYN_FUNCS.StaticDequeue(qh, p_agent_guid, p_event, p_wait, p_executed);
2303
2304 if (not p_executed) then
2305 -- p_queue_handler is normally null, or control by us
2306 -- BINDVAR_SCAN_IGNORE
2307 cmd := 'begin '||qh||'.dequeue(:v1, :v2); end;';
2308 execute immediate cmd using in p_agent_guid,
2309 out p_event;
2310 end if;
2311
2312 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2313 wf_log_pkg.string2(wf_log_pkg.level_statement,
2314 'wf.plsql.wf_event.dequeue.qhandler_callout',
2315 'End executing queue handler - '||qh, false);
2316 end if;
2317
2318 p_queue_handler := qh;
2319 exception
2320 when others then
2321 wf_core.context('Wf_Event', 'Dequeue', p_agent_guid);
2322 wf_core.token('FUNCTION_NAME', qh||'.dequeue()');
2323 --wf_core.raise('WF_EXT_FUNCTION');
2324 raise;
2325 end;
2326 ---------------------------------------------------------------------------
2327 /*
2328 ** AddParameterToList - <described in WFEVENTS.pls>
2329 */
2330 -- YOHUANG 3571176
2331 -- Share the same logic as WF_EVENT_T.AddParameterToList
2332 -- If there is any change of logic in this method, the change
2333 -- should be propagated over to WF_EVENT_T.
2334 PROCEDURE AddParameterToList(p_name in varchar2,
2335 p_value in varchar2,
2336 p_parameterlist in out nocopy wf_parameter_list_t)
2337 is
2338 j number;
2339 found boolean := FALSE;
2340 begin
2341 if (p_ParameterList is null) then
2342 --
2343 -- Initialize Parameter List and set value
2344 --
2345 p_ParameterList := wf_parameter_list_t(null);
2346 p_ParameterList(1) := wf_parameter_t(p_Name, p_Value);
2347 else
2348 --
2349 -- parameter list exists, add parameter to list
2350 --
2351 j := 1;
2352 while (NOT found AND j <= p_parameterlist.COUNT ) LOOP
2353 -- YOHUANG 3566991, make sure myList(j) is not null
2354 -- to avoid 36025 ora error
2355 IF (p_parameterlist(j) IS NOT NULL) THEN
2356 if (p_parameterlist(j).getName() = p_name) then
2357 found := TRUE;
2358 p_parameterlist(j).setValue(p_Value);
2359 END if;
2360 END IF;
2361 j := j+1;
2362 end loop;
2363
2364 -- otherwise, add new parameter to list --
2365
2366 if (NOT found) then
2367 p_ParameterList.EXTEND;
2368 j := p_ParameterList.COUNT;
2369 p_ParameterList(j) := wf_parameter_t(p_name, p_Value);
2370 end if;
2371 end if;
2372 end AddParameterToList;
2373 ---------------------------------------------------------------------------
2374 /*
2375 ** AddParameterToListPos - <described in WFEVENTS.pls>
2376 */
2377 PROCEDURE AddParameterToListPos(p_name in varchar2,
2378 p_value in varchar2,
2379 p_position out nocopy integer,
2380 p_parameterlist in out nocopy wf_parameter_list_t)
2381 is
2382 j integer;
2383
2384 begin
2385 if (p_ParameterList is null) then
2386 --
2387 -- Initialize Parameter List and set value
2388 --
2389 p_ParameterList := wf_parameter_list_t(null);
2390 p_position := 1;
2391 p_ParameterList(1) := wf_parameter_t(p_Name, p_Value);
2392 else
2393 --
2394 -- parameter list exists, add parameter to list
2395 --
2396 p_ParameterList.EXTEND;
2397 j := p_ParameterList.COUNT;
2398 p_position := j;
2399 p_ParameterList(j) := wf_parameter_t(p_Name, p_Value);
2400 end if;
2401
2402 end AddParameterToListPos;
2403 ---------------------------------------------------------------------------
2404 /*
2405 ** GetValueForParameter - <described in WFEVENTS.pls>
2406 */
2407 FUNCTION getValueForParameter(p_Name in varchar2,
2408 p_ParameterList in wf_parameter_list_t )
2409 return varchar2 is
2410 myList wf_parameter_list_t;
2411 pos number := 1;
2412 begin
2413 myList := p_ParameterList;
2414 if (myList is null) then
2415 return NULL;
2416 end if;
2417
2418 pos := myList.LAST;
2419 --while(pos <= myList.COUNT) loop
2420 while(pos is not null) loop
2421 if (myList(pos).getName() = p_Name) then
2422
2423 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2424 wf_log_pkg.string(wf_log_pkg.level_statement,
2425 'wf.plsql.WF_EVENT.getValueForParam.get',
2426 'Name:'||p_Name||' Value:'||myList(pos).getValue());
2427 end if;
2428
2429 return myList(pos).getValue();
2430 end if;
2431 --pos := pos + 1;
2432 pos := myList.PRIOR(pos);
2433 end loop;
2434 return NULL;
2435 end getValueForParameter;
2436 ---------------------------------------------------------------------------
2437 /*
2438 ** GetValueForParameterPos - Gets value for position from wf_parameter_list_t
2439 */
2440 FUNCTION getValueForParameterPos(p_position in integer,
2441 p_parameterlist in wf_parameter_list_t)
2442 return varchar2 is
2443 myList wf_parameter_list_t;
2444 begin
2445 myList := p_ParameterList;
2446 if (myList is null) then
2447 return NULL;
2448 end if;
2449
2450 return myList(p_position).getValue();
2451
2452 end getValueForParameterPos;
2453 ---------------------------------------------------------------------------
2454 /*
2455 ** SetDispatchMode - <described in WFEVENTS.pls>
2456 */
2457 PROCEDURE SetDispatchMode (p_mode in varchar2)
2458 is
2459 begin
2460 if p_mode = 'ASYNC' then
2461 wf_event.phase_maxthreshold := -1;
2462 else
2463 wf_event.phase_maxthreshold := 100;
2464 end if;
2465 exception
2466 when others then
2467 wf_core.context('Wf_Event', 'SetDispatchMode', p_mode);
2468 raise;
2469 end SetDispatchMode;
2470 ---------------------------------------------------------------------------
2471 /*
2472 ** InitPhaseMinThreshold <described in WFEVENTS.pls>
2473 */
2474 PROCEDURE InitPhaseMinThreshold
2475 is
2476 begin
2477 wf_event.phase_minthreshold := 0;
2478 end InitPhaseMinThreshold;
2479 ---------------------------------------------------------------------------
2480 /*
2481 ** DeferEvent - <described in WFEVENTS.pls>
2482 */
2483 PROCEDURE DeferEvent(p_source_type in varchar2,
2484 p_event in out nocopy wf_event_t)
2485 is
2486 defagent wf_agent_t;
2487
2488 begin
2489
2490 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2491 wf_log_pkg.string(wf_log_pkg.level_procedure,
2492 'wf.plsql.WF_EVENT.DeferEvent.Begin',
2493 'Deferring Event: '||p_event.getEventName());
2494 end if;
2495 --
2496 -- Get the Local System Name and set the deferred agent
2497 --
2498
2499 defagent := wf_agent_t('WF_DEFERRED',wf_event.local_system_name);
2500
2501 --
2502 -- If the defer is for a local event, set the
2503 -- Deferred Agent/System into the message for
2504 -- reference when we process the event
2505 --
2506 if p_source_type = 'LOCAL' then
2507 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2508 wf_log_pkg.string(wf_log_pkg.level_statement,
2509 'wf.plsql.WF_EVENT.DeferEvent.Local',
2510 'Detected as Local, setting From Agent:'||
2511 'WF_DEFERRED@'||wf_event.local_system_name);
2512 end if;
2513 p_event.From_Agent := defagent;
2514 end if;
2515
2516 --
2517 -- Enqueue onto the deferred agent
2518 --
2519 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2520 wf_log_pkg.string(wf_log_pkg.level_statement,
2521 'wf.plsql.WF_EVENT.DeferEvent.done',
2522 'Enqueuing on Deferred Queue');
2523 end if;
2524
2525 wf_event.enqueue(p_event,defagent);
2526
2527 exception
2528 when others then
2529 wf_core.context('Wf_Event', 'DeferEvent', p_event.getEventName(),
2530 p_event.getEventKey());
2531 raise;
2532 end DeferEvent;
2533 ---------------------------------------------------------------------------
2534 /*
2535 ** DeferEventToJava - <described in WFEVENTS.pls>
2536 */
2537 PROCEDURE DeferEventToJava(p_source_type in varchar2,
2538 p_event in out nocopy wf_event_t)
2539 is
2540 defagent wf_agent_t;
2541
2542 begin
2543 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2544 wf_log_pkg.string(wf_log_pkg.level_procedure,
2545 'wf.plsql.WF_EVENT.DeferEventToJava.Begin',
2546 'Deferring Event: '|| p_event.getEventName());
2547 end if;
2548 --
2549 -- Get the Local System Name and set the deferred agent
2550 --
2551 defagent := wf_agent_t('WF_JAVA_DEFERRED', wf_event.local_system_name);
2552
2553 --
2554 -- If the defer is for a local event, set the
2555 -- Deferred Agent/System into the message for
2556 -- reference when we process the event
2557 --
2558 if p_source_type = 'LOCAL' then
2559 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2560 wf_log_pkg.string(wf_log_pkg.level_statement,
2561 'wf.plsql.WF_EVENT.DeferEventToJava.Local',
2562 'Detected as Local, setting From Agent:'||
2563 'WF_JAVA_DEFERRED@'|| wf_event.local_system_name);
2564 end if;
2565 p_event.From_Agent := defagent;
2566 end if;
2567
2568 --
2569 -- Enqueue onto the deferred agent
2570 --
2571
2572 --
2573 -- NOTE: Since we know that we need to defer to WF_JAVA_DEFERRED, we will
2574 -- directly invoke enqueue on the PL/SQL queue handler
2575 --
2576 wf_event_ojmstext_qh.enqueue(p_event, defagent);
2577
2578 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2579 wf_log_pkg.string(wf_log_pkg.level_statement,
2580 'wf.plsql.WF_EVENT.DeferEventToJava.done',
2581 'Enqueuing on Java Deferred Queue');
2582 end if;
2583
2584 exception
2585 when others then
2586 wf_core.context('Wf_Event', 'DeferEventToJava', p_event.getEventName(),
2587 p_event.getEventKey());
2588 raise;
2589 end DeferEventToJava;
2590 ---------------------------------------------------------------------------
2591 /*
2592 ** GetDeferEventCtx - <described in WFEVENTS.pls>
2593 */
2594 PROCEDURE GetDeferEventCtx (p_source_type in out nocopy varchar2,
2595 p_agent_name in varchar2,
2596 p_system_name in varchar2,
2597 p_event in wf_event_t)
2598 is
2599 subguid raw(16);
2600 lphasestart number;
2601 lsrc varchar2(10);
2602
2603 -- bes caching implementation
2604 l_sub wf_event_subs_obj;
2605 l_event_name varchar2(240);
2606 begin
2607
2608 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2609 wf_log_pkg.string(wf_log_pkg.level_procedure,
2610 'wf.plsql.WF_EVENT.DeferEventCtx.Begin',
2611 'Getting Defer Event Ctx');
2612 end if;
2613 --
2614 -- Determine the Start Phase, and sourec type
2615 --
2616 subguid := p_event.Error_Subscription;
2617 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2618 wf_log_pkg.string(wf_log_pkg.level_statement,
2619 'wf.plsql.WF_EVENT.DeferEventCtx.sub_guid',
2620 'Sub Guid is '||subguid);
2621 end if;
2622
2623 l_event_name := p_event.GetEventName();
2624 if subguid is not null then
2625 l_sub := wf_bes_cache.GetSubscriptionByGUID(l_event_name, subguid);
2626
2627 if (l_sub is not null) then
2628 lsrc := l_sub.SOURCE_TYPE;
2629 lphasestart := l_sub.PHASE;
2630 else
2631 -- << sstomar : bug 5870400>>
2632 lphasestart := p_event.GetValueForParameter('#CURRENT_PHASE');
2633
2634 if(lphasestart is null) then
2635 raise no_data_found;
2636 END if;
2637
2638 end if;
2639
2640 wf_event.phase_minthreshold := lphasestart;
2641 else
2642 wf_event.phase_minthreshold := 0; -- for deferred raise
2643 end if;
2644
2645 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2646 wf_log_pkg.string(wf_log_pkg.level_statement,
2647 'wf.plsql.WF_EVENT.DeferEventCtx.phase',
2648 'Start Phase is '||to_char(wf_event.phase_minthreshold));
2649 end if;
2650
2651 if lsrc is null then
2652 --
2653 -- Derive the Source Type
2654 --
2655 if ((p_agent_name = 'WF_DEFERRED') AND (p_system_name = wf_event.local_system_name)) then
2656 p_source_type := 'LOCAL';
2657 elsif ((p_system_name is null) OR (p_system_name <> wf_event.local_system_name))then
2658 p_source_type := 'EXTERNAL';
2659 elsif ((p_agent_name <> 'WF_DEFERRED') AND (p_system_name = wf_event.local_system_name)) then
2660 p_source_type := 'EXTERNAL';
2661 end if;
2662 else
2663 p_source_type := lsrc;
2664 end if;
2665
2666 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2667 wf_log_pkg.string(wf_log_pkg.level_procedure,
2668 'wf.plsql.WF_EVENT.DeferEventCtx.End',
2669 'Source Type is '||p_source_type);
2670 end if;
2671
2672 exception
2673 /* Bug 2210085 */
2674 when no_data_found then
2675 /*
2676 wf_core.context('Wf_Event', 'GetDeferEventCtx','Sub guid is ' || subguid);
2677 wf_core.token('SGUID', subguid);
2678 wf_core.raise('WFE_SUB_DELETED');
2679 */
2680 raise;
2681
2682 when others then
2683 wf_core.context('Wf_Event', 'GetDeferEventCtx', p_event.getEventName(),
2684 p_event.getEventKey());
2685 raise;
2686 end GetDeferEventCtx;
2687 ---------------------------------------------------------------------------
2688 /*
2689 ** SetAccountName - <described in WFEVENTS.pls>
2690 */
2691 PROCEDURE SetAccountName
2692 is
2693 begin
2694 -- get the account name - only need this for WF_DEFERRED
2695 select sys_context('USERENV', 'CURRENT_SCHEMA')
2696 into wf_event.account_name
2697 from sys.dual;
2698 exception
2699 when others then
2700 wf_core.context('Wf_Event', 'SetAccountName');
2701 raise;
2702 end SetAccountName;
2703 ---------------------------------------------------------------------------
2704 --
2705 -- Bug# 2211719 - New API raise2 for calls that donot understand
2706 -- Oracle data types`
2707 /*
2708 ** raise2 - <Described in WFEVENTS.pls>
2709 */
2710
2711 PROCEDURE raise2(p_event_name in varchar2,
2712 p_event_key in varchar2,
2713 p_event_data in clob,
2714 p_parameter_name1 in varchar2,
2715 p_parameter_value1 in varchar2,
2716 p_parameter_name2 in varchar2,
2717 p_parameter_value2 in varchar2,
2718 p_parameter_name3 in varchar2,
2719 p_parameter_value3 in varchar2,
2720 p_parameter_name4 in varchar2,
2721 p_parameter_value4 in varchar2,
2722 p_parameter_name5 in varchar2,
2723 p_parameter_value5 in varchar2,
2724 p_parameter_name6 in varchar2,
2725 p_parameter_value6 in varchar2,
2726 p_parameter_name7 in varchar2,
2727 p_parameter_value7 in varchar2,
2728 p_parameter_name8 in varchar2,
2729 p_parameter_value8 in varchar2,
2730 p_parameter_name9 in varchar2,
2731 p_parameter_value9 in varchar2,
2732 p_parameter_name10 in varchar2,
2733 p_parameter_value10 in varchar2,
2734 p_parameter_name11 in varchar2,
2735 p_parameter_value11 in varchar2,
2736 p_parameter_name12 in varchar2,
2737 p_parameter_value12 in varchar2,
2738 p_parameter_name13 in varchar2,
2739 p_parameter_value13 in varchar2,
2740 p_parameter_name14 in varchar2,
2741 p_parameter_value14 in varchar2,
2742 p_parameter_name15 in varchar2,
2743 p_parameter_value15 in varchar2,
2744 p_parameter_name16 in varchar2,
2745 p_parameter_value16 in varchar2,
2746 p_parameter_name17 in varchar2,
2747 p_parameter_value17 in varchar2,
2748 p_parameter_name18 in varchar2,
2749 p_parameter_value18 in varchar2,
2750 p_parameter_name19 in varchar2,
2751 p_parameter_value19 in varchar2,
2752 p_parameter_name20 in varchar2,
2753 p_parameter_value20 in varchar2,
2754 p_send_date in date) is
2755 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
2756 i number := 1;
2757 begin
2758
2759 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2760 wf_log_pkg.string(wf_log_pkg.level_procedure,
2761 'wf.plsql.WF_EVENT.raise2.Begin',
2762 'Event Name:'||p_event_name||' Event Key:'||p_event_key);
2763 end if;
2764
2765 if (p_parameter_name1 is not NULL) then
2766 l_parameter_list.extend;
2767 l_parameter_list(i) := CreateParameter(p_parameter_name1, p_parameter_value1);
2768 i := i + 1;
2769 end if;
2770
2771 if (p_parameter_name2 is not NULL) then
2772 l_parameter_list.extend;
2773 l_parameter_list(i) := CreateParameter(p_parameter_name2, p_parameter_value2);
2774 i := i + 1;
2775 end if;
2776
2777 if (p_parameter_name3 is not NULL) then
2778 l_parameter_list.extend;
2779 l_parameter_list(i) := CreateParameter(p_parameter_name3, p_parameter_value3);
2780 i := i + 1;
2781 end if;
2782
2783 if (p_parameter_name4 is not NULL) then
2784 l_parameter_list.extend;
2785 l_parameter_list(i) := CreateParameter(p_parameter_name4, p_parameter_value4);
2786 i := i + 1;
2787 end if;
2788
2789 if (p_parameter_name5 is not NULL) then
2790 l_parameter_list.extend;
2791 l_parameter_list(i) := CreateParameter(p_parameter_name5, p_parameter_value5);
2792 i := i + 1;
2793 end if;
2794
2795 if (p_parameter_name6 is not NULL) then
2796 l_parameter_list.extend;
2797 l_parameter_list(i) := CreateParameter(p_parameter_name6, p_parameter_value6);
2798 i := i + 1;
2799 end if;
2800
2801 if (p_parameter_name7 is not NULL) then
2802 l_parameter_list.extend;
2803 l_parameter_list(i) := CreateParameter(p_parameter_name7, p_parameter_value7);
2804 i := i + 1;
2805 end if;
2806
2807 if (p_parameter_name8 is not NULL) then
2808 l_parameter_list.extend;
2809 l_parameter_list(i) := CreateParameter(p_parameter_name8, p_parameter_value8);
2810 i := i + 1;
2811 end if;
2812
2813 if (p_parameter_name9 is not NULL) then
2814 l_parameter_list.extend;
2815 l_parameter_list(i) := CreateParameter(p_parameter_name9, p_parameter_value9);
2816 i := i + 1;
2817 end if;
2818
2819 if (p_parameter_name10 is not NULL) then
2820 l_parameter_list.extend;
2821 l_parameter_list(i) := CreateParameter(p_parameter_name10, p_parameter_value10);
2822 i := i + 1;
2823 end if;
2824
2825 if (p_parameter_name11 is not NULL) then
2826 l_parameter_list.extend;
2827 l_parameter_list(i) := CreateParameter(p_parameter_name11, p_parameter_value11);
2828 i := i + 1;
2829 end if;
2830
2831 if (p_parameter_name12 is not NULL) then
2832 l_parameter_list.extend;
2833 l_parameter_list(i) := CreateParameter(p_parameter_name12, p_parameter_value12);
2834 i := i + 1;
2835 end if;
2836
2837 if (p_parameter_name13 is not NULL) then
2838 l_parameter_list.extend;
2839 l_parameter_list(i) := CreateParameter(p_parameter_name13, p_parameter_value13);
2840 i := i + 1;
2841 end if;
2842
2843 if (p_parameter_name14 is not NULL) then
2844 l_parameter_list.extend;
2845 l_parameter_list(i) := CreateParameter(p_parameter_name14, p_parameter_value14);
2846 i := i + 1;
2847 end if;
2848
2849 if (p_parameter_name15 is not NULL) then
2850 l_parameter_list.extend;
2851 l_parameter_list(i) := CreateParameter(p_parameter_name15, p_parameter_value15);
2852 i := i + 1;
2853 end if;
2854
2855 if (p_parameter_name16 is not NULL) then
2856 l_parameter_list.extend;
2857 l_parameter_list(i) := CreateParameter(p_parameter_name16, p_parameter_value16);
2858 i := i + 1;
2859 end if;
2860
2861 if (p_parameter_name17 is not NULL) then
2862 l_parameter_list.extend;
2863 l_parameter_list(i) := CreateParameter(p_parameter_name17, p_parameter_value17);
2864 i := i + 1;
2865 end if;
2866
2867 if (p_parameter_name18 is not NULL) then
2868 l_parameter_list.extend;
2869 l_parameter_list(i) := CreateParameter(p_parameter_name18, p_parameter_value18);
2870 i := i + 1;
2871 end if;
2872
2873 if (p_parameter_name19 is not NULL) then
2874 l_parameter_list.extend;
2875 l_parameter_list(i) := CreateParameter(p_parameter_name19, p_parameter_value19);
2876 i := i + 1;
2877 end if;
2878
2879 if (p_parameter_name20 is not NULL) then
2880 l_parameter_list.extend;
2881 l_parameter_list(i) := CreateParameter(p_parameter_name20, p_parameter_value20);
2882 end if;
2883
2884 wf_event.raise(p_event_name, p_event_key, p_event_data, l_parameter_list, p_send_date);
2885
2886 exception
2887 when others then
2888 raise;
2889 end raise2;
2890 ---------------------------------------------------------------------------
2891 /*
2892 ** CreateParameter - <Described in WFEVENTS.pls>
2893 */
2894 FUNCTION CreateParameter( p_name in varchar2,
2895 p_value in varchar2)
2896 return wf_parameter_t
2897 is
2898 l_parameter wf_parameter_t := wf_parameter_t(null, null);
2899 begin
2900 l_parameter.SetName(p_name);
2901 l_parameter.SetValue(p_value);
2902 return l_parameter;
2903 exception
2904 when others then
2905 raise;
2906 end CreateParameter;
2907
2908 --------------------------------------------------------------------
2909 --Bug 2375902
2910 --New API raise3 which has the parameters of the event
2911 --as in out parameters and hence can be used by the calling program.
2912 --------------------------------------------------------------------
2913 PROCEDURE raise3(p_event_name in varchar2,
2914 p_event_key in varchar2,
2915 p_event_data in clob,
2916 p_parameter_list in out nocopy wf_parameter_list_t,
2917 p_send_date in date)
2918 is
2919 o_value varchar2(200);
2920 event wf_event_t;
2921 begin
2922
2923 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2924 wf_log_pkg.string(wf_log_pkg.level_procedure,
2925 'wf.plsql.WF_EVENT.raise3.Begin',
2926 'Event Name:'||p_event_name||' Event Key:'||p_event_key ||
2927 'Maximum nested raise count:'|| wf_event.max_nested_raises||
2928 'Nested raise count: '|| wf_event.nested_raise_count);
2929 end if;
2930
2931 wf_event.nested_raise_count := wf_event.nested_raise_count + 1;
2932 if (wf_event.nested_raise_count > wf_event.max_nested_raises) then
2933 --Bug 2620834
2934 --The nested count is reset to the initial value that was set before the
2935 --recursion error occurs.
2936 --In future if we allow the user to set the nested-raises -count
2937 --we could think of restting it to that value instead of zero.
2938 wf_event.nested_raise_count := 0;
2939
2940 if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
2941 wf_log_pkg.string(wf_log_pkg.level_error,
2942 'wf.plsql.WF_EVENT.raise3.recursion_error',
2943 'Recursion error raised. Nested raise count exceeded threshold');
2944 end if;
2945
2946 wf_core.context('Wf_Event', 'raise', p_event_name, p_event_key);
2947 wf_core.raise('WFE_RECURSION');
2948 end if;
2949
2950 --Create the event that is to be raised
2951 wf_event_t.initialize(event);
2952 event.Send_Date := nvl(p_send_date,sysdate);
2953 event.Event_Name := p_event_name;
2954 event.Event_Key := p_event_key;
2955 event.Parameter_List := p_parameter_list;
2956 if (p_event_data is not null) then
2957 event.event_data := p_event_data ;
2958 end if;
2959
2960 wf_event.dispatch('LOCAL', null, event);
2961
2962 --Output the parameterlist which may have been
2963 --modified
2964 p_parameter_list := event.getParameterList();
2965
2966 if (wf_event.nested_raise_count >0) then
2967 wf_event.nested_raise_count := wf_event.nested_raise_count - 1;
2968 end if;
2969
2970 event := null;
2971
2972 exception
2973 when others then
2974 raise;
2975 end raise3;
2976
2977 --------------------------------------------------------------------------------
2978 -- Sets the correlation for dequeuing.
2979 --
2980 -- NOTE: This has been done because we did not want to change the signature of
2981 -- dequeue in the queue handlers
2982 --
2983 -- p_correlation - the correlation
2984 --------------------------------------------------------------------------------
2985 PROCEDURE Set_Correlation(p_correlation in varchar2)
2986 IS
2987 BEGIN
2988 WF_EVENT.g_correlation := p_correlation;
2989 WF_EVENT.navigation := dbms_aq.first_message;
2990 END Set_Correlation;
2991
2992 ---------------------------------------------------------------------------
2993 /*
2994 ** SetMaxNestedRaise - Populates Global Variable : max_nested_raises
2995 ** with the value specified in the input parameter.
2996 */
2997 PROCEDURE SetMaxNestedRaise (maxcount in number)
2998 is
2999 begin
3000 wf_event.max_nested_raises := maxcount;
3001 end;
3002 ---------------------------------------------------------------------------
3003 /*
3004 ** SetNestedRaiseCount - Populates Global Variable : nested_raises_count
3005 ** with the value specified in the input parameter.
3006 ** PRIVATE PROCEDURE
3007 */
3008 PROCEDURE SetNestedRaiseCount (nestedcount in number)
3009 is
3010 begin
3011 wf_event.nested_raise_count := nestedcount;
3012 --This private variable P_NESTED_RAISE_COUNT is updated
3013 --in sync with the setting of nested_raise_count global variable
3014 --It is used when the recursion error occurs inorder to reset the
3015 --value of wf_event.nested_raise_count.
3016 --P_NESTED_RAISE_COUNT := wf_event.nested_raise_count;
3017 end;
3018 ---------------------------------------------------------------------------
3019 /*
3020 ** GetMaxNestedRaise - Get the value of the Global Variable max_nested_raises
3021 */
3022
3023 FUNCTION GetMaxNestedRaise
3024 return number
3025 is
3026 begin
3027 return wf_event.max_nested_raises;
3028 end;
3029
3030 ---------------------------------------------------------------------------
3031 /*
3032 ** GetNestedRaiseCount - Get the value of the Global Variable
3033 ** nested_raises_count
3034 */
3035
3036 FUNCTION GetNestedRaiseCount
3037 return number
3038 is
3039 begin
3040 return wf_event.nested_raise_count;
3041 end;
3042
3043 ---------------------------------------------------------------------------
3044
3045 FUNCTION Get_MsgId
3046 return varchar2
3047 is
3048 begin
3049 return wf_event.g_msgid;
3050 end;
3051
3052 ---------------------------------------------------------------------------
3053 /*
3054 ** GetLocalSystemInfo - <described in WFEVENTS.pls>
3055 */
3056 PROCEDURE GetLocalSystemInfo(system_guid out nocopy raw,
3057 system_name out nocopy varchar2,
3058 system_status out nocopy varchar2)
3059 is
3060 begin
3061 system_guid := wf_event.local_system_guid;
3062 system_name := wf_event.local_system_name;
3063 system_status := wf_event.local_system_status;
3064 exception
3065 when others then
3066 wf_core.context('Wf_Event', 'GetLocalSystemName');
3067 end GetLocalSystemInfo;
3068 ---------------------------------------------------------------------------
3069 /*
3070 ** GetSourceAgentGUID - <described in WFEVENTS.pls>
3071 */
3072 PROCEDURE GetSourceAgentGUID(agent_name in varchar2,
3073 agent_system in varchar2,
3074 agent_guid out nocopy raw)
3075 is
3076 -- bes caching implementation
3077 l_agent_obj wf_agent_obj;
3078 begin
3079
3080 l_agent_obj := wf_bes_cache.GetAgentByName(agent_name, agent_system);
3081
3082 if (l_agent_obj is not null) then
3083 agent_guid := l_agent_obj.GUID;
3084 else
3085 if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
3086 wf_log_pkg.string(wf_log_pkg.level_error,
3087 'wf.plsql.WF_EVENT.GetSourceAgentGUID.Error',
3088 'Specified source agent row does '||
3089 'not exist in database. Setting to NULL.');
3090 end if;
3091 agent_guid := null;
3092 end if;
3093 exception
3094 when others then
3095 wf_core.context('Wf_Event', 'GetSourceAgentGUID', agent_name);
3096 raise;
3097 end GetSourceAgentGUID;
3098 ---------------------------------------------------------------------------
3099 /*
3100 ** GetAgentDetails (PRIVATE) - Gets the agents details such as the queue name
3101 ** schema name and recepients
3102 */
3103 procedure GetAgentDetails(agent_name in varchar2)
3104 is
3105
3106 l_queue_name varchar2(80);
3107 l_pos number := 0;
3108 l_name varchar2(30);
3109 l_owner varchar2(30);
3110 l_queue_table varchar2(30);
3111
3112 -- bes caching implementation
3113 l_agent_obj wf_agent_obj;
3114 begin
3115 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
3116 wf_log_pkg.string(wf_log_pkg.level_procedure,
3117 'wf.plsql.WF_EVENT.GetAgentDetails.Begin',
3118 'Get Agent Details');
3119 end if;
3120
3121 if(agent_name is not null and agent_name <> wf_event.pv_last_agent_name) then
3122 -- for a given agent name Query wf_agents to get the agent info
3123
3124 l_agent_obj := wf_bes_cache.GetAgentByName(agent_name, null);
3125 if (l_agent_obj is not null) then
3126 l_queue_name := l_agent_obj.QUEUE_NAME;
3127 end if;
3128
3129 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
3130 wf_log_pkg.string(wf_log_pkg.level_statement,
3131 'wf.plsql.WF_EVENT.GetAgentDetails',
3132 'l_queue_name = ' || l_queue_name);
3133 end if;
3134
3135 -- since since queue_name is a nullable col. l_queue_name could be null
3136 -- let the caller program handle the case of null queue_name
3137 if(l_queue_name is not null) then
3138 -- derive the queue name and the schema
3139
3140 l_pos := instr(l_queue_name,'.');
3141 l_name := substr(l_queue_name, l_pos + 1);
3142
3143 if (l_pos > 0) then
3144 l_owner := substr(l_queue_name, 1, l_pos - 1);
3145 else
3146 -- if queue_name does not contain schema we will look in WF_SCHEMA
3147 l_owner := wf_event.schema_name;
3148 end if;
3149
3150 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
3151 wf_log_pkg.string(wf_log_pkg.level_statement,
3152 'wf.plsql.WF_EVENT.GetAgentDetails',
3153 'l_name = ' || l_name ||
3154 ' l_owner = ' || l_owner);
3155 end if;
3156
3157 -- retrieve recipients (MULTIPLE or SINGLE) for AQ
3158 -- 3659756 When get agent details, check if the enqueue and dequeue are enabled.
3159 select aq.queue_type, aq.QUEUE_TABLE, trim(dequeue_enabled)
3160 into WF_EVENT.g_queueType, l_queue_table, WF_EVENT.pv_last_dequeue_enabled
3161 from all_queues aq
3162 where aq.owner = l_owner
3163 and aq.name = l_name;
3164
3165 select aqt.recipients, aqt.message_grouping
3166 into WF_EVENT.pv_last_recipients, WF_EVENT.g_message_grouping
3167 from all_queue_tables aqt
3168 where aqt.queue_table = l_queue_table
3169 and aqt.owner = l_owner;
3170
3171 -- update package variables
3172 wf_event.pv_last_agent_name := agent_name;
3173 -- YOHUANG, l_queue_name shouldn't have owner information
3174 wf_event.pv_last_queue_name := l_name; --l_queue_name;
3175 wf_event.pv_last_schema_name := l_owner;
3176 else
3177 raise no_data_found;
3178 end if;
3179 end if;
3180 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
3181 wf_log_pkg.string(wf_log_pkg.level_procedure,
3182 'wf.plsql.WF_EVENT.GetAgentDetails.END',
3183 'Get Agent Details');
3184 end if;
3185 exception
3186 when no_data_found then
3187 -- let the caller handle it
3188 raise;
3189 when others then
3190 -- let the caller handle it
3191 raise;
3192 end GetAgentDetails;
3193 ---------------------------------------------------------------------------
3194 /*
3195 ** StartAgent - <described in WFEVENTS.pls>
3196 ** Though We cache the agent details, we still check if the agent and
3197 ** fixed name queues are started or not every time when listen() or
3198 ** listen_to_agent is called, just be conservative
3199 ** Bug 3659756
3200 ** Assume the fixed name queues are always started (if not, the component listener
3201 ** will error out and sys admin will fix it)
3202 ** Only start the agent that is being listened.
3203 ** Only start the agent once per session if agent name is not changed in the same session.
3204 */
3205 procedure StartAgent(agent_name in varchar2)
3206 is
3207 -- l_enqueue_enabled VARCHAR2(30);
3208 -- l_dequeue_enabled VARCHAR2(30);
3209
3210 --Bug 2307433
3211 --Cursor for obtaining the names of the
3212 --deferred and error queue.
3213 --Bug 3271311, add the WF_JAVA queues for start.
3214 --Bug 3659756, no longer start fixed name queues.
3215 /**
3216 CURSOR q_disabled (schema varchar2) is
3217 SELECT name
3218 FROM all_queues
3219 WHERE name in ('WF_DEFERRED', 'WF_ERROR', 'WF_JAVA_DEFERRED','WF_JAVA_ERROR')
3220 AND owner =schema
3221 AND ((trim(enqueue_enabled) = 'NO') OR (trim(dequeue_enabled) = 'NO'));
3222 */
3223
3224 begin
3225 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
3226 wf_log_pkg.string(wf_log_pkg.level_procedure,
3227 'wf.plsql.WF_EVENT.StartAgent.Begin',
3228 'Starting Agents');
3229 end if;
3230 --Bug 2307433
3231 --Enable the deferred and error queues for
3232 --enqueue and dequeue incase they are disabled
3233 --In any cases, the WF_DEFERRED, WF_ERROR, WF_JAVA_ERROR and WF_JAVA_DEFERRED
3234 --must be started.
3235 --schema := wf_core.translate('WF_SCHEMA');
3236 --Bug 3659756, no longer start fixed name queues.
3237 /*
3238 for q_name in q_disabled (wf_event.schema_name) loop
3239 DBMS_AQADM.START_QUEUE(wf_event.schema_name||'.'||q_name.name);
3240 end loop;
3241 */
3242 -- The agent details must be retrieved even for seeded queue.
3243 GetAgentDetails(agent_name);
3244
3245 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
3246 wf_log_pkg.string(wf_log_pkg.level_statement,
3247 'wf.plsql.WF_EVENT.StartAgent',
3248 'dequeue_enabled = ' || WF_EVENT.pv_last_dequeue_enabled);
3249 end if;
3250
3251 if (WF_EVENT.pv_last_dequeue_enabled = 'NO') then
3252 -- If the user has disabled the queue for enqueue don't override it.
3253 -- So we only enable the dequeue if dequeue is not enabled and we don't change
3254 -- the current setting of enqueue.
3255
3256 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
3257 wf_log_pkg.string(wf_log_pkg.level_statement,
3258 'wf.plsql.WF_EVENT.StartAgent',
3259 'starting queue = ' || wf_event.pv_last_schema_name
3260 || '.' || wf_event.pv_last_agent_name);
3261 end if;
3262
3263 DBMS_AQADM.START_QUEUE(wf_event.pv_last_schema_name || '.' || wf_event.pv_last_queue_name, FALSE);
3264
3265 WF_EVENT.pv_last_dequeue_enabled := 'YES';
3266 end if;
3267
3268 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
3269 wf_log_pkg.string(wf_log_pkg.level_procedure,
3270 'wf.plsql.WF_EVENT.StartAgent.End',
3271 'Starting Agents');
3272 end if;
3273 exception
3274 when no_data_found then
3275 wf_core.context('Wf_Event', 'StartAgent', agent_name);
3276 wf_core.raise('WFE_AGENT_NOTEXIST');
3277 when others then
3278 wf_core.context('Wf_Event', 'StartAgent', agent_name);
3279 raise;
3280 end StartAgent;
3281 ---------------------------------------------------------------------------
3282 /*
3283 ** Peek_Agent - <Described in WFEVENTS.pls>
3284 */
3285 FUNCTION Peek_Agent (p_agent_name IN VARCHAR2)
3286 RETURN VARCHAR2
3287 AS
3288 -- NOTE: This must be done in PL/SQL because in 8i DBMS_AQ.LISTEN is not
3289 -- supported in Java
3290
3291 l_agent sys.aq$_agent;
3292 l_agent_list dbms_aq.aq$_agent_list_t;
3293
3294 LISTEN_EXCEPTION exception;
3295
3296 pragma exception_init(LISTEN_EXCEPTION, -25254);
3297 BEGIN
3298
3299 if p_agent_name is null then
3300 return 'N';
3301 end if;
3302
3303 -- Perf bug 2987857
3304 -- If p_agent_name is same as pkg var, then details are not required
3305 -- to be retrieved again
3306 -- YOHUANG, move the cache validation logic into getAgentDetails
3307
3308 -- Retrieve queue name for the agent
3309 -- Only local agents should be checked for - bug 2902048
3310 -- GetAgentDetails becomes the single source to start a particular agent
3311 -- and can be called without worrying about fixed name queues
3312 GetAgentDetails(p_agent_name);
3313
3314 -- bug 2897326, agent_list is initialized differently for
3315 -- multi or single consumer queue
3316
3317 IF wf_event.pv_last_recipients = 'MULTIPLE' THEN
3318 l_agent_list(1) := sys.aq$_agent(wf_event.pv_last_agent_name,
3319 wf_event.pv_last_schema_name || '.' || wf_event.pv_last_queue_name, null);
3320 ELSE
3321 l_agent_list(1) := sys.aq$_agent(null,
3322 wf_event.pv_last_schema_name || '.' || wf_event.pv_last_queue_name, null);
3323 END IF;
3324
3325 -- "Listen" for messages on this queue
3326 BEGIN
3327 DBMS_AQ.Listen(agent_list => l_agent_list,
3328 wait => 0,
3329 agent => l_agent);
3330 EXCEPTION
3331 WHEN LISTEN_EXCEPTION THEN
3332 RETURN 'N';
3333 END;
3334
3335 RETURN 'Y';
3336 END Peek_Agent ;
3337
3338 -- GetParamListFromString
3339 -- Takes a space delimited NAME=VALUE pairs of Subscription Parameters
3340 -- string and returns a WF_PARAMETER_LIST_T
3341 -- IN
3342 -- p_parameters A string with space delimited name=value pairs
3343 function GetParamListFromString(p_parameters in varchar2)
3344 return wf_parameter_list_t
3345 is
3346 l_parameters varchar2(5000);
3347 l_start pls_integer := 0;
3348 l_end pls_integer := 0;
3349 l_endposition number;
3350 l_namevalue varchar2(4000);
3351 l_value varchar2(4000);
3352 l_name varchar2(4000);
3353 l_equalpos number;
3354 l_param_list wf_parameter_list_t;
3355 begin
3356 l_parameters := p_parameters;
3357
3358 if (l_parameters is not null) then
3359 l_parameters := replace(l_parameters, wf_core.newline,' ');
3360 l_parameters := replace(l_parameters, wf_core.tab,' ');
3361 l_parameters := replace(l_parameters, wf_core.cr,'');
3362 l_parameters := l_parameters||' ';
3363
3364 l_start:= 1;
3365 l_end := length(l_parameters);
3366
3367 while (l_start < l_end) loop
3368 l_endposition := instr(l_parameters, ' ', l_start, 1);
3369 l_namevalue := rtrim(ltrim(substr(l_parameters, l_start, l_endposition-l_start)));
3370
3371 l_equalpos := instr(l_namevalue, '=', 1, 1);
3372 l_name := substr(l_namevalue,1,l_equalpos-1);
3373 l_value := substr(l_namevalue,l_equalpos+1,length(l_namevalue));
3374
3375 wf_event.AddParameterToList(l_name, l_value, l_param_list);
3376 l_start := l_endposition+1;
3377 end loop;
3378 end if;
3379 return l_param_list;
3380 exception
3381 when others then
3382 wf_core.Context('WF_EVENT', 'GetParamListFromString');
3383 raise;
3384 end GetParamListFromString;
3385
3386 ---------------------------------------------------------------------------
3387 /*
3388 ** SetSystemGlobals - Populates System Global Variables
3389 */
3390 begin
3391 wf_event.local_system_guid := hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
3392
3393 wf_event.local_system_status := wf_core.translate('WF_SYSTEM_STATUS');
3394
3395 select name into wf_event.local_system_name
3396 from wf_systems
3397 where guid = wf_event.local_system_guid;
3398 ---------------------------------------------------------------------------
3399 wf_event.schema_name := wf_core.translate('WF_SCHEMA');
3400
3401 wf_event.pv_last_agent_name := ' ';
3402 wf_event.pv_last_queue_name := ' ';
3403 wf_event.pv_last_schema_name := ' ';
3404 wf_event.pv_last_recipients := ' ';
3405 wf_event.pv_last_dequeue_enabled := ' ';
3406
3407
3408 end WF_EVENT;