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