1 package body WF_BES_CACHE as
2 /* $Header: WFBECACB.pls 120.7.12020000.2 2012/10/30 13:14:25 alsosa ship $ */
3
4 /*---------+
5 | Globals |
6 +---------*/
7 g_Event_Idx number := 1;
8 g_Agent_Idx number := 2;
9 g_System_Idx number := 3;
10
11 g_event_const varchar2(30) := 'EVENTS';
12 g_agent_const varchar2(30) := 'AGENTS';
13 g_system_const varchar2(30) := 'SYSTEMS';
14
15 g_src_type_local varchar2(8) := 'LOCAL';
16 g_src_type_external varchar2(8) := 'EXTERNAL';
17 g_src_type_error varchar2(8) := 'ERROR';
18
19 g_event_any varchar2(240) := 'oracle.apps.wf.event.any';
20 g_event_unexpected varchar2(240) := 'oracle.apps.wf.event.unexpected';
21
22 g_java_sub varchar2(240) := 'java://';
23
24 g_status_yes varchar2(1) := 'Y';
25 g_status_no varchar2(1) := 'N';
26
27 g_date_mask varchar2(26) := 'YYYY/MM/DD HH24:MI:SS';
28
29 g_initialized boolean := false;
30
31 -- Initialize (PRIVATE)
32 -- Procedure to initialize the cache management related variables like
33 -- cache size, hash size etc.
34 procedure Initialize
35 is
36 l_cache_size number;
37 begin
38 if (g_initialized) then
39 return;
40 end if;
41
42 -- the token value may not have been loaded, null or may be a non-numeric
43 -- value specified. in such cases, default it to 50.
44 begin
45 l_cache_size := to_number(wf_core.translate('WFBES_MAX_CACHE_SIZE'));
46 exception
47 when others then
48 l_cache_size := 50;
49 end;
50
51 wf_object_cache.SetCacheSize(l_cache_size);
52
53 g_local_system_guid := hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
54 g_local_system_status := wf_core.translate('WF_SYSTEM_STATUS');
55 g_schema_name := wf_core.translate('WF_SCHEMA');
56 begin
57 SELECT name
58 INTO g_local_system_name
59 FROM wf_systems
60 WHERE guid = g_local_system_guid;
61 exception
62 when no_data_found then
63 g_local_system_name := null;
64 end;
65 g_initialized := true;
66 end Initialize;
67
68 -- SetMetaDataUploaded
69 -- This procedure is called from the BES table handlers when meta-data
70 -- is being uploaded to the database tables. This procedure sets a BES
71 -- caching context with the sysdate when the meta-data is loaded.
72 procedure SetMetaDataUploaded
73 is
74 --Bug 14602624. Deferred agent listener runs by under the SYSADMIN schema. It
75 --needs to be set for name spaces not to get lost
76 l_username FND_USER.USER_NAME%TYPE := nvl(FND_GLOBAL.user_name,'SYSADMIN');
77 begin
78 dbms_session.set_context(namespace => 'WFBES_CACHE_CTX',
79 attribute => 'WFBES_METADATA_UPLOADED',
80 value => to_char(sysdate, g_date_mask),
81 client_id => l_username);
82 exception
83 when others then
84 wf_core.context('WF_BES_CACHE', 'MetaDataUploaded');
85 raise;
86 end SetMetaDataUploaded;
87
88 -- GetMetaDataUploaded (PRIVATE)
89 -- This function returns the date at which BES meta-data was last uploaded
90 -- to the database after the session's cache was initialized.
91 function GetMetaDataUploaded
92 return date
93 is
94 l_value varchar2(4000);
95 begin
96 l_value := sys_context('WFBES_CACHE_CTX', 'WFBES_METADATA_UPLOADED');
97 return to_date(l_value, g_date_mask);
98 exception
99 when others then
100 wf_core.context('WF_BES_CACHE', 'GetMetaDataUploaded');
101 raise;
102 end GetMetaDataUploaded;
103
104 -- SetMetaDataCached (PRIVATE)
105 -- This procedure is called when cache is created to set the timestamp
106 -- at which the cache is initialized for this session
107 procedure SetMetaDataCached(p_date in date)
108 is
109 begin
110 -- If the date is already set in this session, do not set it again
111 -- or if the p_date is null, we want to reset the last cache update
112 if (g_LastCacheUpdate is null or p_date is null) then
113 g_LastCacheUpdate := p_date;
114 end if;
115 end SetMetaDataCached;
116
117 -- CacheValid
118 -- This function validates if the current session's cache is valid or
119 -- not. This procedure compares the time at which the meta-data was
120 -- loaded into the database with the time at which the sessions cache
121 -- was initialized. If the former is greater than the latter, new data
122 -- has been uploaded to the database and cache is invalid.
123 -- RETURN
124 -- Boolean status of whether cache is valid or not
125 function CacheValid
126 return boolean
127 is
128 l_lastMetaUpload date;
129 begin
130 l_lastMetaUpload := GetMetaDataUploaded();
131
132 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
133 wf_log_pkg.string(wf_log_pkg.level_statement,
134 'wf.plsql.WF_BES_CACHE.CacheValid.Check',
135 'Last upload time {'||to_char(l_lastMetaUpload, g_date_mask)||
136 '} Last cache time {'||to_char(g_LastCacheUpdate, g_date_mask)||'}');
137 end if;
138
139 if (l_lastMetaUpload is not null and g_LastCacheUpdate is not null and
140 l_lastMetaUpload > g_LastCacheUpdate) then
141 return false;
142 end if;
143 return true;
144 end CacheValid;
145
146 -- ClearCache
147 -- Clears the cached objects from memory and resets requires variables
148 -- given the name of the cache.
149 procedure ClearCache
150 is
151 begin
152 wf_object_cache.Clear();
153 g_initialized := false;
154 SetMetaDataCached(null);
155 end ClearCache;
156
157 -- ValidateCache (PRIVATE)
158 -- This procedure validates the cache and clears if found invalid
159 procedure ValidateCache
160 is
161 begin
162 -- if cache is invalid, clear it so that it can be rebuilt from database
163 if (not CacheValid()) then
164 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
165 wf_log_pkg.string(wf_log_pkg.level_statement,
166 'wf.plsql.WF_BES_CACHE.ValidateCache.Cache_Invalid',
167 'Cache is invalid. Clearing the cache');
168 end if;
169 ClearCache();
170 end if;
171 end ValidateCache;
172
173 --
174 -- EVENTS and SUBSCRIPTIONS caching routines
175 --
176
177 -- Get_Event_Object (PRIVATE)
178 -- This procedure sets the given event object to the Object cache. This
179 -- procedure set the event object to the appropriate cache created for
180 -- events
181 procedure Get_Event_Object(p_event_name in varchar2,
182 p_event_obj in out nocopy WF_Event_Obj)
183 is
184 l_any_data AnyData;
185 l_dummy pls_integer;
186 begin
187 -- Get the event from the object cache
188 l_any_data := wf_object_cache.GetObject(g_Event_Idx, p_event_name);
189 if (l_any_data is not null) then
190 l_dummy := l_any_data.getObject(p_event_obj);
191 else
192 p_event_obj := null;
193 end if;
194 end Get_Event_Object;
195
196 -- Set_Event_Object (PRIVATE)
197 -- This procedure gets the event object from the Object cache for the given
198 -- event name.
199 procedure Set_Event_Object(p_event_name in varchar2,
200 p_event_obj in WF_Event_Obj)
201 is
202 l_any_data AnyData;
203 begin
204 -- Store this object in the cache for future use
205 l_any_data := sys.AnyData.ConvertObject(p_event_obj);
206 wf_object_cache.SetObject(g_Event_Idx, l_any_data, p_event_name);
207 end Set_Event_Object;
208
209 -- Load_Event (PRIVATE)
210 -- Given the event name, this procedure loads the event information to the
211 -- wf_event_obj instance
212 procedure Load_Event(p_event_name in varchar2,
213 p_event_obj in out nocopy wf_event_obj)
214 is
215
216 CURSOR c_get_event (cp_event_name varchar2) IS
217 SELECT guid, name, type, status, generate_function,
218 java_generate_func, licensed_flag
219 FROM wf_events
220 WHERE name = cp_event_name;
221
222 begin
223 if (p_event_obj is null) then
224 wf_event_obj.Initialize(p_event_obj);
225 end if;
226
227 open c_get_event(p_event_name);
228 fetch c_get_event into p_event_obj.guid, p_event_obj.name, p_event_obj.type,
229 p_event_obj.status, p_event_obj.generate_function,
230 p_event_obj.java_generate_func, p_event_obj.licensed_flag;
231 if (c_get_event%NOTFOUND) then
232 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
233 wf_log_pkg.string(wf_log_pkg.level_statement,
234 'wf.plsql.WF_BES_CACHE.Load_Event.Not_Found',
235 'Event {'||p_event_name||'} not found in the database');
236 end if;
237 -- If event not found in the set wf_event_obj to null
238 p_event_obj := null;
239 end if;
240 close c_get_event;
241
242 end Load_Event;
243
244 -- Load_Subscriptions (PRIVATE)
245 -- Given the event name this procedure loads all the valid subscriptions
246 -- into the wf_event_obj
247 procedure Load_Subscriptions(p_event_name in varchar2,
248 p_source_type in varchar2,
249 p_source_agent in raw,
250 p_subs_count in out nocopy number,
251 p_event_obj in out nocopy wf_event_obj)
252 is
253
254 -- Cursor from wf_event.Dispatch that fetches all the valid subscriptions to the
255 -- event and the event group to which the event belongs
256 cursor active_subs (cp_event_name varchar2,
257 cp_source_type varchar2,
258 cp_source_agent_guid raw)
259 is
260 select subscription_guid,
261 system_guid,
262 subscription_source_type,
263 subscription_source_agent_guid,
264 subscription_phase,
265 subscription_rule_data,
266 subscription_out_agent_guid,
267 subscription_to_agent_guid,
268 subscription_priority,
269 subscription_rule_function,
270 wf_process_type,
271 wf_process_name,
272 subscription_parameters,
273 '',
274 '',
275 '',
276 '',
277 '',
278 subscription_on_error_type,
279 ''
280 from wf_active_subscriptions_v
281 where event_name in(cp_event_name, g_event_any)
282 and subscription_source_type = cp_source_type
283 and ((subscription_source_agent_guid is NOT NULL AND
284 subscription_source_agent_guid = nvl(cp_source_agent_guid, subscription_source_agent_guid))
285 OR subscription_source_agent_guid is NULL)
286 and system_guid = wf_bes_cache.g_local_system_guid
287 order by 5;
288
289 cursor active_event_subs(cp_event_name varchar2,
290 cp_source_type varchar2,
291 cp_source_agent_guid raw)
292 is
293 (select sub.guid sub_guid,
294 sub.system_guid sys_guid,
295 sub.source_type src_type,
296 sub.source_agent_guid src_agt,
297 nvl(sub.phase,0) phase,
298 sub.rule_data rule_data,
299 sub.out_agent_guid out_agt,
300 sub.to_agent_guid to_agt,
301 sub.priority priority,
302 DECODE(sub.rule_function, NULL,
303 DECODE(sub.java_rule_func, NULL, NULL,
304 g_java_sub||sub.java_rule_func),
305 sub.rule_function) rule_func,
306 sub.wf_process_type proc_type,
307 sub.wf_process_name proc_name,
308 sub.parameters params,
309 sub.expression exp,
310 sub.invocation_id inv_id,
311 sub.map_code map_code,
312 sub.standard_type std_type,
313 sub.standard_code std_code,
314 sub.on_error_code on_error,
315 sub.action_code action
316 from wf_event_subscriptions sub,
317 wf_events evt
318 where sub.system_guid = wf_event.local_system_guid
319 and sub.status = 'ENABLED'
320 and sub.licensed_flag = 'Y'
321 and sub.source_type = cp_source_type
322 and ((sub.source_agent_guid is NOT NULL AND
323 sub.source_agent_guid = nvl(cp_source_agent_guid, sub.source_agent_guid))
324 OR sub.source_agent_guid is NULL)
325 and sub.event_filter_guid = evt.guid
326 and evt.name = cp_event_name
327 and evt.type = 'EVENT'
328 and evt.status = 'ENABLED'
329 and evt.licensed_flag = 'Y')
330 order by 5;
331
332 l_subscription WF_Event_Subs_Obj;
333 l_count number;
334 begin
335
336 -- Check if subscriptions are already loaded for this source type
337 if (upper(p_event_obj.GetSubsLoaded(p_source_type)) = g_status_yes) then
338 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
339 wf_log_pkg.string(wf_log_pkg.level_statement,
340 'wf.plsql.WF_BES_CACHE.Load_Subscriptions.Check',
341 p_source_type||' subscriptions for event {'||
342 p_event_name||'} are already loaded.');
343 end if;
344 return;
345 end if;
346
347 WF_Event_Subs_Obj.Initialize(l_subscription);
348 l_count := 0;
349
350 -- Load all LOCAL subscriptions from the Database
351 if (lower(p_event_name) in (g_event_unexpected, g_event_any)) then
352 open active_event_subs(p_event_name, p_source_type, p_source_agent);
353 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
354 wf_log_pkg.string(wf_log_pkg.level_procedure,
355 'wf.plsql.WF_BES_CACHE.Load_Subscriptions.active_event_subs',
356 'Using cursor active_event_subs for event {'||p_event_name||'}');
357 end if;
358
359 loop
360 fetch active_event_subs into l_subscription.GUID,
361 l_subscription.SYSTEM_GUID,
362 l_subscription.SOURCE_TYPE,
363 l_subscription.SOURCE_AGENT_GUID,
364 l_subscription.PHASE,
365 l_subscription.RULE_DATA,
366 l_subscription.OUT_AGENT_GUID,
367 l_subscription.TO_AGENT_GUID,
368 l_subscription.PRIORITY,
369 l_subscription.RULE_FUNCTION,
370 l_subscription.WF_PROCESS_TYPE,
371 l_subscription.WF_PROCESS_NAME,
372 l_subscription.PARAMETERS,
373 l_subscription.EXPRESSION,
374 l_subscription.INVOCATION_ID,
375 l_subscription.MAP_CODE,
376 l_subscription.STANDARD_TYPE,
377 l_subscription.STANDARD_CODE,
378 l_subscription.ON_ERROR_CODE,
379 l_subscription.ACTION_CODE;
380
381 exit when active_event_subs%NOTFOUND;
382
383 -- This is an optimization flag to indicate that the subcription list has
384 -- non-null source agent For an agent listener session, if this flag is Y,
385 -- the list will be searched. Else the complete subscription list is returned
386 if (l_subscription.SOURCE_AGENT_GUID is not null) then
387 p_event_obj.SetSourceAgentAvl(p_source_type, g_status_yes);
388 end if;
389
390 p_event_obj.AddSubscriptionToList(l_subscription, p_source_type);
391 l_count := l_count+1;
392 end loop;
393 close active_event_subs;
394 else
395 open active_subs(p_event_name, p_source_type, p_source_agent);
396 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
397 wf_log_pkg.string(wf_log_pkg.level_procedure,
398 'wf.plsql.WF_BES_CACHE.Load_Subscriptions.active_subs',
399 'Using cursor active_subs for event {'||p_event_name||'}');
400 end if;
401
402 loop
403 fetch active_subs into l_subscription.GUID,
404 l_subscription.SYSTEM_GUID,
405 l_subscription.SOURCE_TYPE,
406 l_subscription.SOURCE_AGENT_GUID,
407 l_subscription.PHASE,
408 l_subscription.RULE_DATA,
409 l_subscription.OUT_AGENT_GUID,
410 l_subscription.TO_AGENT_GUID,
411 l_subscription.PRIORITY,
412 l_subscription.RULE_FUNCTION,
413 l_subscription.WF_PROCESS_TYPE,
414 l_subscription.WF_PROCESS_NAME,
415 l_subscription.PARAMETERS,
416 l_subscription.EXPRESSION,
417 l_subscription.INVOCATION_ID,
418 l_subscription.MAP_CODE,
419 l_subscription.STANDARD_TYPE,
420 l_subscription.STANDARD_CODE,
421 l_subscription.ON_ERROR_CODE,
422 l_subscription.ACTION_CODE;
423
424 exit when active_subs%NOTFOUND;
425
426 -- This is an optimization flag to indicate that the subcription list has
427 -- non-null source agent For an agent listener session, if this flag is Y,
428 -- the list will be searched. Else the complete subscription list is returned
429 if (l_subscription.SOURCE_AGENT_GUID is not null) then
430 p_event_obj.SetSourceAgentAvl(p_source_type, g_status_yes);
431 end if;
432
433 p_event_obj.AddSubscriptionToList(l_subscription, p_source_type);
434 l_count := l_count+1;
435 end loop;
436 close active_subs;
437 end if;
438
439 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
440 wf_log_pkg.string(wf_log_pkg.level_procedure,
441 'wf.plsql.WF_BES_CACHE.Load_Subscriptions.End',
442 'Loaded {'||l_count||'} '||p_source_type||' subscriptions '||
443 'for event {'||p_event_name||'}');
444 end if;
445
446 p_event_obj.SetSubsLoaded(p_source_type, g_status_yes);
447 p_subs_count := l_count;
448 end Load_Subscriptions;
449
450 -- GetEventByName
451 -- This function returns an instance of WF_Event_Obj object type which
452 -- contains the complete event information along with valid subscriptions
453 -- to the event. The information could come from the cache memory or from
454 -- the database.
455 -- IN
456 -- p_event_name - Event name whose information is required
457 function GetEventByName(p_event_name in varchar2)
458 return wf_event_obj
459 is
460 l_event_obj wf_event_obj;
461 l_any_data anyData;
462 l_dummy pls_integer;
463 begin
464 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
465 wf_log_pkg.string(wf_log_pkg.level_procedure,
466 'wf.plsql.WF_BES_CACHE.GetEventByName.Begin',
467 'Getting event details for {'||p_event_name||'}');
468 end if;
469
470 l_event_obj := null;
471
472 if (not g_initialized) then
473 Initialize();
474 end if;
475
476 ValidateCache();
477
478 -- Each object type being cached is identified by a number within
479 -- the session
480 if (not wf_object_cache.IsCacheCreated(g_Event_Idx)) then
481 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
482 wf_log_pkg.string(wf_log_pkg.level_statement,
483 'wf.plsql.WF_BES_CACHE.GetEventByName.Create_Cache',
484 'Cache is not created for EVENTS index {'||g_Event_Idx||'}');
485 end if;
486 wf_object_cache.CreateCache(g_Event_Idx);
487 SetMetaDataCached(sysdate);
488 else
489 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
490 wf_log_pkg.string(wf_log_pkg.level_statement,
491 'wf.plsql.WF_BES_CACHE.GetEventByName.Cache_Get',
492 'Getting the event from Cache');
493 end if;
494 Get_Event_Object(p_event_name, l_event_obj);
495 end if;
496
497 -- If the event is not in the object cache, initialize the object and
498 -- retrieve the details from the database
499 if (l_event_obj is null) then
500 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
501 wf_log_pkg.string(wf_log_pkg.level_statement,
502 'wf.plsql.WF_BES_CACHE.GetEventByName.Load_Event',
503 'Event not found in cache. Loading from Database');
504 end if;
505 -- Load event from the database to the object
506 Load_Event(p_event_name, l_event_obj);
507
508 if (l_event_obj is not null) then
509 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
510 wf_log_pkg.string(wf_log_pkg.level_statement,
511 'wf.plsql.WF_BES_CACHE.GetEventByName.Set_Cache',
512 'Setting event {'||p_event_name||'} to cache');
513 end if;
514 Set_Event_Object(p_event_name, l_event_obj);
515 end if;
516 end if;
517
518 -- return the event object only if the event is ENABLED and product licensed
519 if (l_event_obj is not null and
520 l_event_obj.STATUS = 'ENABLED' and
521 l_event_obj.LICENSED_FLAG = 'Y') then
522 return l_event_obj;
523 else
524 return null;
525 end if;
526
527 exception
528 when others then
529 wf_core.context('WF_BES_CACHE', 'GetEventByName', p_event_name);
530 raise;
531 end GetEventByName;
532
533 -- GetSubscriptions
534 -- This function returns a table of WF_EVENT_SUBS_OBJ that are the valid
535 -- subscriptions to the given event.
536 function GetSubscriptions(p_event_name in varchar2,
537 p_source_type in varchar2,
538 p_source_agent in raw)
539 return wf_event_subs_tab
540 is
541 l_event_obj wf_event_obj;
542 l_any_data anyData;
543 l_dummy pls_integer;
544 l_subs_count number;
545 begin
546 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
547 wf_log_pkg.string(wf_log_pkg.level_procedure,
548 'wf.plsql.WF_BES_CACHE.GetSubscriptions.Begin',
549 'Getting subscriptions for event {'||p_event_name||'}'||
550 ' Source Type {'||p_source_type||'} Source Agt {'||p_source_agent||'}');
551 end if;
552
553 -- Get the event information from the cache or the database
554 l_event_obj := GetEventByName(p_event_name);
555
556 -- Event not found in the cache as well as in database or disabled
557 if (l_event_obj is null) then
558 return null;
559 end if;
560
561 -- Load subscriptions for a given source type if not already loaded
562 if (upper(l_event_obj.GetSubsLoaded(p_source_type)) = g_status_no) then
563
564 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
565 wf_log_pkg.string(wf_log_pkg.level_statement,
566 'wf.plsql.WF_BES_CACHE.GetSubscriptions.Load_Subs',
567 'Subscriptions for {'||p_event_name||'} is not already loaded.'||
568 ' Loading from DB for Source Type {'||p_source_type||
569 '} Source Agt {'||p_source_agent||'}');
570 end if;
571
572 -- NOTE: within an agent listener's session we would only require subscriptions
573 -- with the same source_agent_guid. so it should be ok to cache only subscriptions
574 -- with that source_agent_guid and with null source_agent_guid
575 -- TODO!! currently we are loading for all source_agent_guid
576
577 -- load subscriptions for all source_agents if not already loaded
578 Load_Subscriptions(p_event_name, p_source_type, null, l_subs_count, l_event_obj);
579 Set_Event_Object(p_event_name, l_event_obj);
580 else
581 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
582 wf_log_pkg.string(wf_log_pkg.level_statement,
583 'wf.plsql.WF_BES_CACHE.GetSubscriptions.Subs_Loaded',
584 'Subscriptions for {'||p_event_name||'} is already loaded to cache');
585 end if;
586 end if;
587
588 -- If p_source_agent is not null, dispatcher needs only subscriptions
589 -- with the specified SOURCE_AGENT_GUID
590 if (p_source_agent is not null and
591 l_event_obj.GetSourceAgentAvl(p_source_type) = g_status_yes) then
592 return l_event_obj.GetSubscriptionBySrcAgtGUID(p_source_type, p_source_agent);
593 else
594 -- All subscriptions for a given event are loaded for the given source type
595 return l_event_obj.GetSubscriptionList(p_source_type);
596 end if;
597 exception
598 when others then
599 wf_core.context('WF_BES_CACHE', 'GetSubscriptions', p_event_name, p_source_type);
600 raise;
601 end GetSubscriptions;
602
603 -- GetSubscriptionByGUID
604 -- This function returns a WF_EVENT_SUBS_OBJ that contains the subscription
605 -- to the given event and mathing the given subscription GUID.
606 function GetSubscriptionByGUID(p_event_name in varchar2,
607 p_sub_guid in raw)
608 return wf_event_subs_obj
609 is
610 l_event_obj WF_Event_Obj;
611 l_subs_count number;
612 l_subs_loaded boolean;
613 begin
614 -- If either of the parameter values are null, no info can be returned
615 if (p_event_name is null or p_sub_guid is null) then
616 return null;
617 end if;
618
619 l_event_obj := wf_bes_cache.GetEventByName(p_event_name);
620
621 if (l_event_obj is null) then
622 return null;
623 end if;
624
625 l_subs_loaded := false;
626 -- Load subscriptions if not already loaded
627 if (upper(l_event_obj.GetSubsLoaded(g_src_type_local)) = g_status_no) then
628 Load_Subscriptions(p_event_name, g_src_type_local, null, l_subs_count, l_event_obj);
629 l_subs_loaded := true;
630 end if;
631 if (upper(l_event_obj.GetSubsLoaded(g_src_type_external)) = g_status_no) then
632 Load_Subscriptions(p_event_name, g_src_type_external, null, l_subs_count, l_event_obj);
633 l_subs_loaded := true;
634 end if;
635 if (upper(l_event_obj.GetSubsLoaded(g_src_type_error)) = g_status_no) then
636 Load_Subscriptions(p_event_name, g_src_type_error, null, l_subs_count, l_event_obj);
637 l_subs_loaded := true;
638 end if;
639
640 if (l_subs_loaded) then
641 -- Set the event object to cache if in case some subscriptions were loaded
642 Set_Event_Object(p_event_name, l_event_obj);
643 end if;
644
645 return l_event_obj.GetSubscriptionByGUID(p_sub_guid);
646 exception
647 when others then
648 wf_core.context('WF_BES_CACHE', 'GetSubscriptionByGUID', p_event_name, p_sub_guid);
649 raise;
650 end GetSubscriptionByGUID;
651
652 --
653 -- AGENTS caching routines
654 --
655
656 -- Get_Agent_Object (PRIVATE)
657 -- This procedure sets the given agent object to the Object cache. This
658 -- procedure sets the agent object to the appropriate cache created for
659 -- agents
660 procedure Get_Agent_Object(p_agent_key in varchar2,
661 p_agent_obj in out nocopy WF_Agent_Obj)
662 is
663 l_any_data AnyData;
664 l_dummy pls_integer;
665 begin
666 -- Get the agent from the object cache
667 l_any_data := wf_object_cache.GetObject(g_Agent_Idx, p_agent_key);
668 if (l_any_data is not null) then
669 l_dummy := l_any_data.getObject(p_agent_obj);
670 else
671 p_agent_obj := null;
672 end if;
673 end Get_Agent_Object;
674
675 -- Set_Agent_Object (PRIVATE)
676 -- This procedure gets the agent object from the Object cache for the given
677 -- agent name + system name.
678 procedure Set_Agent_Object(p_agent_key in varchar2,
679 p_agent_obj in WF_Agent_Obj)
680 is
681 l_any_data AnyData;
682 begin
683 -- Store this object in the cache for future use
684 l_any_data := sys.AnyData.ConvertObject(p_agent_obj);
685 wf_object_cache.SetObject(g_Agent_Idx, l_any_data, p_agent_key);
686 end Set_Agent_Object;
687
688 -- Load_Agent (PRIVATE)
689 -- Given the agent name and system name, this procedure loads the agent
690 -- information to the wf_agent_obj instance. This procedure takes agent
691 -- name, system name and agent guid as inputs.
692 -- o if agent name and system name are not null, it uses this to get info
693 -- o if agent name and/or system name are null and agent guid is not null
694 -- it agent guid to get the info
695 procedure Load_Agent(p_agent_guid in raw,
696 p_agent_name in varchar2,
697 p_system_name in varchar2,
698 p_agent_obj in out nocopy wf_agent_obj)
699 is
700
701 CURSOR c_get_agent_n (cp_agent_name varchar2, cp_system_name varchar2) IS
702 SELECT a.guid, a.name, a.system_guid, s.name, a.protocol, a.address,
703 a.queue_handler, a.queue_name, a.direction, a.status,
704 a.display_name, a.type, a.java_queue_handler
705 FROM wf_agents a, wf_systems s
706 WHERE a.name = cp_agent_name
707 AND a.system_guid = s.guid
708 AND s.name = cp_system_name;
709
710 CURSOR c_get_agent_g (cp_agent_guid raw) IS
711 SELECT a.guid, a.name, a.system_guid, s.name, a.protocol, a.address,
712 a.queue_handler, a.queue_name, a.direction, a.status,
713 a.display_name, a.type, a.java_queue_handler
714 FROM wf_agents a, wf_systems s
715 WHERE a.guid = cp_agent_guid
716 AND s.guid = a.system_guid;
717
718 begin
719 if (p_agent_obj is null) then
720 wf_agent_obj.Initialize(p_agent_obj);
721 end if;
722
723 if (p_agent_name is not null and p_system_name is not null) then
724 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
725 wf_log_pkg.string(wf_log_pkg.level_statement,
726 'wf.plsql.WF_BES_CACHE.Load_Agent.Cursor',
727 'Loading agent by name '||p_agent_name||'+'||p_system_name);
728 end if;
729 open c_get_agent_n(p_agent_name, p_system_name);
730 fetch c_get_agent_n into p_agent_obj.GUID,
731 p_agent_obj.NAME,
732 p_agent_obj.SYSTEM_GUID,
733 p_agent_obj.SYSTEM_NAME,
734 p_agent_obj.PROTOCOL,
735 p_agent_obj.ADDRESS,
736 p_agent_obj.QUEUE_HANDLER,
737 p_agent_obj.QUEUE_NAME,
738 p_agent_obj.DIRECTION,
739 p_agent_obj.STATUS,
740 p_agent_obj.DISPLAY_NAME,
741 p_agent_obj.TYPE,
742 p_agent_obj.JAVA_QUEUE_HANDLER;
743
744 if (c_get_agent_n%NOTFOUND) then
745 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
746 wf_log_pkg.string(wf_log_pkg.level_statement,
747 'wf.plsql.WF_BES_CACHE.Load_Agent.Not_Found',
748 'Agent not found in the database.');
749 end if;
750 -- If agent not found in the set wf_agent_obj to null
751 p_agent_obj := null;
752 end if;
753 close c_get_agent_n;
754 elsif (p_agent_guid is not null) then
755 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
756 wf_log_pkg.string(wf_log_pkg.level_statement,
757 'wf.plsql.WF_BES_CACHE.Load_Agent.Not_Found',
758 'Loading agent by GUID '||p_agent_guid);
759 end if;
760 open c_get_agent_g(p_agent_guid);
761 fetch c_get_agent_g into p_agent_obj.GUID,
762 p_agent_obj.NAME,
763 p_agent_obj.SYSTEM_GUID,
764 p_agent_obj.SYSTEM_NAME,
765 p_agent_obj.PROTOCOL,
766 p_agent_obj.ADDRESS,
767 p_agent_obj.QUEUE_HANDLER,
768 p_agent_obj.QUEUE_NAME,
769 p_agent_obj.DIRECTION,
770 p_agent_obj.STATUS,
771 p_agent_obj.DISPLAY_NAME,
772 p_agent_obj.TYPE,
773 p_agent_obj.JAVA_QUEUE_HANDLER;
774
775 if (c_get_agent_g%NOTFOUND) then
776 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
777 wf_log_pkg.string(wf_log_pkg.level_statement,
778 'wf.plsql.WF_BES_CACHE.Load_Agent.Not_Found',
779 'Agent not found in the database');
780 end if;
781 -- If agent not found in the set wf_agent_obj to null
782 p_agent_obj := null;
783 end if;
784 close c_get_agent_g;
785 else
786 p_agent_obj := null;
787 end if;
788
789 end Load_Agent;
790
791 -- Load_Agent_QH (PRIVATE)
792 -- Given the queue handler name, this procedure loads the agent
793 -- information to the wf_agent_obj instance of the first agent whose
794 -- queue handler matches with this queue handler
795 procedure Load_Agent_QH(p_agent_qh in varchar2,
796 p_direction in varchar2,
797 p_agent_obj in out nocopy wf_agent_obj)
798 is
799
800 CURSOR c_get_agent (cp_agent_qh varchar2, cp_direction varchar2) IS
801 SELECT a.guid, a.name, a.system_guid, s.name, a.protocol, a.address,
802 a.queue_handler, a.queue_name, a.direction, a.status,
803 a.display_name, a.type, a.java_queue_handler
804 FROM wf_agents a, wf_systems s
805 WHERE a.system_guid = wf_event.local_system_guid
806 AND a.system_guid = s.guid
807 AND a.queue_handler = cp_agent_qh
808 AND a.direction = cp_direction;
809
810 begin
811 if (p_agent_obj is null) then
812 wf_agent_obj.Initialize(p_agent_obj);
813 end if;
814
815 if (p_agent_qh is null) then
816 return;
817 end if;
818
819 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
820 wf_log_pkg.string(wf_log_pkg.level_statement,
821 'wf.plsql.WF_BES_CACHE.Load_Agent_QH.Cursor',
822 'Loading from DB for Queue Handler '||p_agent_qh);
823 end if;
824
825 open c_get_agent(p_agent_qh, p_direction);
826 fetch c_get_agent into p_agent_obj.GUID,
827 p_agent_obj.NAME,
828 p_agent_obj.SYSTEM_GUID,
829 p_agent_obj.SYSTEM_NAME,
830 p_agent_obj.PROTOCOL,
831 p_agent_obj.ADDRESS,
832 p_agent_obj.QUEUE_HANDLER,
833 p_agent_obj.QUEUE_NAME,
834 p_agent_obj.DIRECTION,
835 p_agent_obj.STATUS,
836 p_agent_obj.DISPLAY_NAME,
837 p_agent_obj.TYPE,
838 p_agent_obj.JAVA_QUEUE_HANDLER;
839
840 if (c_get_agent%NOTFOUND) then
841 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
842 wf_log_pkg.string(wf_log_pkg.level_statement,
843 'wf.plsql.WF_BES_CACHE.Load_Agent.Not_Found',
844 'No Agent exists for the given queue handler');
845 end if;
846 -- If agent not found in the set wf_agent_obj to null
847 p_agent_obj := null;
848 end if;
849 close c_get_agent;
850
851 end Load_Agent_QH;
852
853 -- GetAgentByName
854 -- This function returns an instance of WF_AGENT_OBJ that contains the
855 -- information about the specified Agent name + System Name. If null
856 -- system name is provided, local system is assumed.
857 function GetAgentByName(p_agent_name in varchar2,
858 p_system_name in varchar2)
859 return wf_agent_obj
860 is
861 l_agent_obj wf_agent_obj;
862 l_any_data anyData;
863 l_dummy pls_integer;
864 l_agent_key varchar2(60);
865 l_system_name varchar2(30);
866 begin
867 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
868 wf_log_pkg.string(wf_log_pkg.level_procedure,
869 'wf.plsql.WF_BES_CACHE.GetAgentByName.Begin',
870 'Getting agent details for {'||p_agent_name||
871 '+'||p_system_name||'}');
872 end if;
873
874 -- Initialize cache variables if not already initialized
875 if (not g_initialized) then
876 Initialize();
877 end if;
878
879 -- if provided system name is null, default to local system name
880 if (p_system_name is null) then
881 l_system_name := g_local_system_name;
882 else
883 l_system_name := p_system_name;
884 end if;
885
886 if (p_agent_name is null) then
887 return null;
888 end if;
889
890 l_agent_key := p_agent_name||l_system_name;
891 l_agent_obj := null;
892
893 ValidateCache();
894
895 -- Each object type being cached is identified by a number within
896 -- the session
897 if (not wf_object_cache.IsCacheCreated(g_Agent_Idx)) then
898 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
899 wf_log_pkg.string(wf_log_pkg.level_statement,
900 'wf.plsql.WF_BES_CACHE.GetAgentByName.Create_Cache',
901 'Cache is not created for AGENTS index {'||g_Agent_Idx||'}');
902 end if;
903 wf_object_cache.CreateCache(g_Agent_Idx);
904 SetMetaDataCached(sysdate);
905 else
906 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
907 wf_log_pkg.string(wf_log_pkg.level_statement,
908 'wf.plsql.WF_BES_CACHE.GetAgentByName.Cache_Get',
909 'Getting the Agent from Cache');
910 end if;
911 Get_Agent_Object(l_agent_key, l_agent_obj);
912 end if;
913
914 -- If the Agent is not in the object cache, initialize the object and
915 -- retrieve the details from the database
916 if (l_agent_obj is null) then
917 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
918 wf_log_pkg.string(wf_log_pkg.level_statement,
919 'wf.plsql.WF_BES_CACHE.GetAgentByName.Load_Agent',
920 'Agent not found in cache. Loading from Database');
921 end if;
922 -- Load agent from the database to the object
923
924 Load_Agent(null, p_agent_name, l_system_name, l_agent_obj);
925
926 if (l_agent_obj is not null) then
927 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
928 wf_log_pkg.string(wf_log_pkg.level_statement,
929 'wf.plsql.WF_BES_CACHE.GetAgentByName.Set_Cache',
930 'Setting Agent {'||l_agent_key||'} to cache');
931 end if;
932 Set_Agent_Object(l_agent_key, l_agent_obj);
933 end if;
934 end if;
935
936 return l_agent_obj;
937
938 end GetAgentByName;
939
940 -- GetAgentByGUID
941 -- This function returns an instance of WF_AGENT_OBJ that contains the
942 -- information about the specified Agent guid
943 function GetAgentByGUID(p_agent_guid in raw)
944 return wf_agent_obj
945 is
946 l_all_agents wf_object_cache.wf_objects_t;
947 l_agent_key varchar2(60);
948 l_agent_obj wf_agent_obj;
949 l_dummy pls_integer;
950 l_agent_name varchar2(30);
951 l_system_name varchar2(30);
952 l_found boolean;
953 l_loc number;
954 begin
955
956 if (p_agent_guid is null) then
957 return null;
958 end if;
959
960 -- Initialize cache if not already initialized
961 if (not g_initialized) then
962 Initialize();
963 end if;
964
965 l_agent_obj := null;
966
967 ValidateCache();
968
969 -- check if the Agent cache is created. if created get all agent objects
970 if (not wf_object_cache.IsCacheCreated(g_Agent_Idx)) then
971 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
972 wf_log_pkg.string(wf_log_pkg.level_statement,
973 'wf.plsql.WF_BES_CACHE.GetAgentByGUID.Create_Cache',
974 'Cache is not created for AGENTS index {'||g_Agent_Idx||'}');
975 end if;
976 wf_object_cache.CreateCache(g_Agent_Idx);
977 SetMetaDataCached(sysdate);
978 else
979 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
980 wf_log_pkg.string(wf_log_pkg.level_statement,
981 'wf.plsql.WF_BES_CACHE.GetAgentByGUID.Get_All',
982 'Getting all cached agent objects');
983 end if;
984 l_all_agents := wf_object_cache.GetAllObjects(g_Agent_Idx);
985 end if;
986
987 -- check if at least one agent is cached. then look for the agent with GUID
988 if (l_all_agents is not null) then
989
990 l_found := false;
991
992 l_loc := l_all_agents.FIRST;
993 while (l_loc is not null) loop
994 l_dummy := l_all_agents(l_loc).getObject(l_agent_obj);
995 if (l_agent_obj.GUID = p_agent_guid) then
996 l_found := true;
997 exit;
998 end if;
999 l_loc := l_all_agents.NEXT(l_loc);
1000 end loop;
1001
1002 if (l_found) then
1003 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1004 wf_log_pkg.string(wf_log_pkg.level_statement,
1005 'wf.plsql.WF_BES_CACHE.GetAgentByGUID.Cache_Hit',
1006 'Agent found in cache for GUID '||p_agent_guid);
1007 end if;
1008 return l_agent_obj;
1009 end if;
1010 end if;
1011
1012 -- agent not in cache. add the agent to cache from database
1013 Load_Agent(p_agent_guid, null, null, l_agent_obj);
1014
1015 -- set it to cache if found in database
1016 if (l_agent_obj is not null) then
1017 l_agent_name := l_agent_obj.NAME;
1018 l_system_name := l_agent_obj.SYSTEM_NAME;
1019 l_agent_key := l_agent_name||l_system_name;
1020 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1021 wf_log_pkg.string(wf_log_pkg.level_statement,
1022 'wf.plsql.WF_BES_CACHE.GetAgentByGUID.Set_Object',
1023 'Agent loaded from Database for GUID '||p_agent_guid||
1024 '. Setting to cache for '||l_agent_key);
1025 end if;
1026 Set_Agent_Object(l_agent_key, l_agent_obj);
1027 end if;
1028 return l_agent_obj;
1029
1030 exception
1031 when others then
1032 wf_core.context('WF_BES_CACHE', 'GetAgentByGUID', p_agent_guid);
1033 raise;
1034 end GetAgentByGUID;
1035
1036 -- GetAgentByQH
1037 -- This function returns an instance of WF_AGENT_OBJ that contains the
1038 -- information about first agent that matches the specified Queue Handler
1039 function GetAgentByQH(p_agent_qh in varchar2,
1040 p_direction in varchar2)
1041 return wf_agent_obj
1042 is
1043 l_all_agents wf_object_cache.wf_objects_t;
1044 l_agent_key varchar2(60);
1045 l_agent_obj wf_agent_obj;
1046 l_dummy pls_integer;
1047 l_agent_name varchar2(30);
1048 l_system_name varchar2(30);
1049 l_found boolean;
1050 l_loc number;
1051 begin
1052
1053 if (p_agent_qh is null) then
1054 return null;
1055 end if;
1056
1057 -- Initialize cache if not already initialized
1058 if (not g_initialized) then
1059 Initialize();
1060 end if;
1061
1062 l_agent_obj := null;
1063
1064 ValidateCache();
1065
1066 -- check if the Agent cache is created. if created get all agent objects
1067 if (not wf_object_cache.IsCacheCreated(g_Agent_Idx)) then
1068 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1069 wf_log_pkg.string(wf_log_pkg.level_statement,
1070 'wf.plsql.WF_BES_CACHE.GetAgentByQH.Create_Cache',
1071 'Cache is not created for AGENTS index {'||g_Agent_Idx||'}');
1072 end if;
1073 wf_object_cache.CreateCache(g_Agent_Idx);
1074 SetMetaDataCached(sysdate);
1075 else
1076 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1077 wf_log_pkg.string(wf_log_pkg.level_statement,
1078 'wf.plsql.WF_BES_CACHE.GetAgentByQH.Get_All',
1079 'Getting all cached agent objects');
1080 end if;
1081 l_all_agents := wf_object_cache.GetAllObjects(g_Agent_Idx);
1082 end if;
1083
1084 -- check if at least one agent is cached. then look for the agent with GUID
1085 if (l_all_agents is not null) then
1086
1087 l_found := false;
1088
1089 l_loc := l_all_agents.FIRST;
1090 while (l_loc is not null) loop
1091 l_dummy := l_all_agents(l_loc).getObject(l_agent_obj);
1092 if (l_agent_obj.DIRECTION = p_direction and
1093 l_agent_obj.QUEUE_HANDLER = p_agent_qh) then
1094 l_found := true;
1095 exit;
1096 end if;
1097 l_loc := l_all_agents.NEXT(l_loc);
1098 end loop;
1099
1100 if (l_found) then
1101 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1102 wf_log_pkg.string(wf_log_pkg.level_statement,
1103 'wf.plsql.WF_BES_CACHE.GetAgentByQH.Cache_Hit',
1104 'Agent found in cache for Queue Handler '||p_agent_qh);
1105 end if;
1106 return l_agent_obj;
1107 end if;
1108 end if;
1109
1110 -- agent not in cache. add the agent to cache from database
1111 Load_Agent_QH(p_agent_qh, p_direction, l_agent_obj);
1112
1113 -- set it to cache if found in database
1114 if (l_agent_obj is not null) then
1115 l_agent_name := l_agent_obj.NAME;
1116 l_system_name := l_agent_obj.SYSTEM_NAME;
1117 l_agent_key := l_agent_name||l_system_name;
1118 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1119 wf_log_pkg.string(wf_log_pkg.level_statement,
1120 'wf.plsql.WF_BES_CACHE.GetAgentByQH.Set_Object',
1121 'Agent loaded from Database for Queue Hander '||p_agent_qh||
1122 '. Setting to cache for '||l_agent_key);
1123 end if;
1124 Set_Agent_Object(l_agent_key, l_agent_obj);
1125 end if;
1126 return l_agent_obj;
1127
1128 exception
1129 when others then
1130 wf_core.context('WF_BES_CACHE', 'GetAgentByQH', p_agent_qh, p_direction);
1131 raise;
1132 end GetAgentByQH;
1133
1134 --
1135 -- SYSTEMS caching routines
1136 --
1137
1138 -- GetSystemByName
1139 -- This function returns an instance of WF_SYSTEM_OBJ that contains the
1140 -- information about the specified System name.
1141 function GetSystemByName(p_system_name in varchar2)
1142 return wf_system_obj
1143 is
1144 begin
1145 return null;
1146 end GetSystemByName;
1147
1148 end WF_BES_CACHE;
[Home] [Help]