1 PACKAGE BODY XDP_PARAM_CACHE AS
2 /* $Header: XDPPACHB.pls 120.1 2005/06/16 02:15:16 appldev $ */
3
4
5 Procedure CheckInited;
6 Procedure setup_workitem_config(p_wi_instance_id in number);
7 Procedure setup_fa_config(p_fa_instance_id in number);
8
9 Procedure load_workitem_params;
10 Procedure load_workitem_params(p_wi_param_list in varchar2);
11 Procedure load_fa_params;
12
13 Function get_param_index(p_param_type in varchar2,
14 p_param_name in varchar2) return number;
15
16 Procedure get_wi_param_config(p_param_name in varchar2,
17 p_param_exists OUT NOCOPY varchar2,
18 p_log_flag OUT NOCOPY varchar2,
19 p_eval_mode OUT NOCOPY varchar2,
20 p_eval_proc OUT NOCOPY varchar2,
21 p_default_value OUT NOCOPY varchar2);
22
23 Procedure get_fa_param_config(p_param_name in varchar2,
24 p_param_exists OUT NOCOPY varchar2,
25 p_log_flag OUT NOCOPY varchar2,
26 p_eval_proc OUT NOCOPY varchar2,
27 p_default_value OUT NOCOPY varchar2);
28
29 Procedure Print;
30
31 -- Clear the Cache
32 Procedure clear_cache
33 is
34 begin
35 pv_workitem_instance_id := null;
36 pv_workitem_id := null;
37 pv_workitem_name := null;
38 pv_workitem_type := null;
39
40 pv_wi_eval_proc_name := null;
41
42 pv_wi_item_type := null;
43 pv_wi_process_name := null;
44 pv_wi_key_prefix := null;
45
46 pv_wi_param_list.delete;
47
48 pv_fa_instance_id := null;
49 pv_fa_id := null;
50 pv_fa_name := null;
51
52 pv_fe_routing_proc := null;
53
54 pv_fa_param_list.delete;
55 end clear_cache;
56
57
58 -- Init the Cache with Workitem Configuration alone
59 -- The Workitem Configurtion is gathered and the
60 -- Workitem Parameter configuration is loaded based
61 -- on the p_load_param_config flag
62 Procedure init_cache(p_wi_instance_id number,
63 p_load_param_config in boolean default TRUE)
64 is
65 begin
66 pv_workitem_instance_id := p_wi_instance_id;
67 setup_workitem_config(p_wi_instance_id);
68 if p_load_param_config then
69 load_workitem_params;
70 end if;
71 end init_cache;
72
73 -- Init the Cache with Workitem Configuration alone
74 -- The Workitem Configurtion is gathered and the
75 -- Workitem Parameter configuration is loaded based
76 -- on the list of parameters specified
77 Procedure init_cache(p_wi_instance_id number,
78 p_wi_param_list in varchar2,
79 p_load_param_config in boolean default TRUE)
80 is
81 begin
82 pv_workitem_instance_id := p_wi_instance_id;
83 setup_workitem_config(p_wi_instance_id);
84 if p_load_param_config then
85 load_workitem_params(p_wi_param_list);
86 end if;
87 end init_cache;
88
89 -- Init the Cache with Workitem and FA Configuration
90 -- Workitem Parameters and FA paramters configuration is loaded based
91 -- on the p_load_param_config flag
92 Procedure init_cache(p_wi_instance_id number,
93 p_fa_instance_id number,
94 p_load_param_config in boolean default TRUE)
95 is
96 begin
97 pv_workitem_instance_id := p_wi_instance_id;
98 pv_fa_instance_id := p_fa_instance_id;
99
100 setup_workitem_config(p_wi_instance_id);
101 if p_load_param_config then
102 load_workitem_params;
103 end if;
104
105 setup_fa_config(p_fa_instance_id);
106 if p_load_param_config then
107 load_fa_params;
108 end if;
109
110 end init_cache;
111
112
113 --
114 -- Add a Work Item paramter to the Cache
115 Procedure Add_wi_param_to_cache(p_param_name in varchar2,
116 p_param_value in varchar2,
117 p_param_ref_value in varchar2 default null,
118 p_log_flag in varchar2 default null,
119 p_evaluation_mode in varchar2 default null,
120 p_evaluation_proc in varchar2 default null,
121 p_default_value in varchar2 default null,
122 p_update_db in varchar2 default 'Y')
123
124 is
125 l_index number;
126 l_eval_mode varchar2(80);
127 l_log_flag varchar2(80);
128 l_eval_proc varchar2(80);
129 l_def_val varchar2(80);
130 l_exists_flag varchar2(1);
131
132 begin
133 CheckInited;
134
135 -- Check if the paramter is already in the Cache.
136 -- If so the cache is updated
137 -- Else the Parameter Configuratio is added
138 l_index := get_param_index(g_WIParam, p_param_name);
139 if l_index > 0 then
140 -- Parameter already found. Update required
141 -- dbms_output.put_line(' -- Need to Update the cache:(WI) ' || p_param_name);
142 pv_wi_param_list(l_index).parameter_value := p_param_value;
143 if p_param_ref_value is not null then
144 pv_wi_param_list(l_index).parameter_ref_value := p_param_ref_value;
145 end if;
146 if p_default_value is not null then
147 pv_wi_param_list(l_index).default_value := p_default_value;
148 end if;
149 else
150 -- Parameter not found. Need to add paramter configuration to
151 -- the cache.
152 -- Get the config and add it to the cache
153 -- dbms_output.put_line(' -- Need to add to cache: ' || p_param_name);
154 get_wi_param_config(p_param_name => Add_wi_param_to_cache.p_param_name,
155 p_param_exists => l_exists_flag,
156 p_log_flag => l_log_flag,
157 p_eval_mode => l_eval_mode,
158 p_eval_proc => l_eval_proc,
159 p_default_value => l_def_val);
160
161 if l_exists_flag = 'N' then
162 l_log_flag := Add_wi_param_to_cache.p_log_flag;
163 l_eval_mode := Add_wi_param_to_cache.p_evaluation_mode;
164 l_eval_proc := Add_wi_param_to_cache.p_evaluation_proc;
165 l_def_val := Add_wi_param_to_cache.p_default_value;
166 end if;
167
168 if pv_wi_param_list.count > 0 then
169 l_index := pv_wi_param_list.last;
170 else
171 l_index := 0;
172 end if;
173
174 pv_wi_param_list(l_index+1).parameter_name := Add_wi_param_to_cache.p_param_name;
175 pv_wi_param_list(l_index+1).parameter_value := Add_wi_param_to_cache.p_param_value;
176 pv_wi_param_list(l_index+1).parameter_ref_value := Add_wi_param_to_cache.p_param_ref_value;
177 pv_wi_param_list(l_index+1).log_flag := l_log_flag;
178 pv_wi_param_list(l_index+1).evaluation_mode := l_eval_mode;
179 pv_wi_param_list(l_index+1).evaluation_proc := l_eval_proc;
180 pv_wi_param_list(l_index+1).default_value := l_def_val;
181
182 end if;
183
184 -- This is for future.. The caching could be enhanced to update the DB
185 -- if p_update_db = 'Y' then
186 -- null;
187 -- xdp_engine.set_workitem_param_value
188 -- (p_wi_instance_id => pv_workitem_instance_id,
189 -- p_parameter_name => Add_wi_param_to_cache.p_param_name,
190 -- p_parameter_value => Add_wi_param_to_cache.p_param_value,
191 -- p_parameter_reference_value => Add_wi_param_to_cache.p_param_ref_value);
192 -- end if;
193
194 end Add_wi_param_to_cache;
195
196
197 --
198 -- Add a FA paramter to the Cache
199 Procedure Add_fa_param_to_cache(p_param_name in varchar2,
200 p_param_value in varchar2,
201 p_log_flag in varchar2 default null,
202 p_evaluation_proc in varchar2 default null,
203 p_default_value in varchar2 default null,
204 p_update_db in varchar2 default 'Y')
205
206 is
207 l_index number;
208 l_log_flag varchar2(80);
209 l_eval_proc varchar2(80);
210 l_def_val varchar2(80);
211 l_exists_flag varchar2(1);
212
213 begin
214 CheckInited;
215
216 -- Check if the paramter is already in the Cache.
217 -- If so the cache is updated
218 -- Else the Parameter Configuratio is added
219 l_index := get_param_index(g_FAParam, p_param_name);
220 if l_index > 0 then
221 -- Paramter already exists in the Cache. Needs an update
222 -- dbms_output.put_line(' -- Need to Update the cache: (FA) ' || p_param_name);
223 pv_fa_param_list(l_index).parameter_value := p_param_value;
224 if p_default_value is not null then
225 pv_fa_param_list(l_index).default_value := p_default_value;
226 end if;
227 else
228 -- Paramter does not exists in the Cache.
229 -- Needs to fetch and add the paramter config to the cache
230 -- dbms_output.put_line(' -- Need to add to cache:(FA) ' || p_param_name);
231 get_fa_param_config(
232 p_param_name => Add_fa_param_to_cache.p_param_name,
233 p_param_exists => l_exists_flag,
234 p_log_flag => l_log_flag,
235 p_eval_proc => l_eval_proc,
236 p_default_value => l_def_val);
237
238 if l_exists_flag = 'N' then
239 l_log_flag := Add_fa_param_to_cache.p_log_flag;
240 l_eval_proc := Add_fa_param_to_cache.p_evaluation_proc;
241 l_def_val := Add_fa_param_to_cache.p_default_value;
242 end if;
243
244 if pv_fa_param_list.count > 0 then
245 l_index := pv_fa_param_list.last;
246 else
247 l_index := 0;
248 end if;
249
250 pv_fa_param_list(l_index+1).parameter_name := Add_fa_param_to_cache.p_param_name;
251 pv_fa_param_list(l_index+1).parameter_value := Add_fa_param_to_cache.p_param_value;
252 pv_fa_param_list(l_index+1).log_flag := l_log_flag;
253 pv_fa_param_list(l_index+1).evaluation_proc := l_eval_proc;
254 pv_fa_param_list(l_index+1).default_value := l_def_val;
255
256 end if;
257
258 -- This is for future.. The caching could be enhanced to update the DB
259 -- if p_update_db = 'Y' then
260 -- xdp_engine.set_fa_param_value
261 -- (p_fa_instance_id => pv_fa_instance_id,
262 -- p_parameter_name => Add_fa_param_to_cache.p_param_name,
263 -- p_parameter_value => Add_fa_param_to_cache.p_param_value);
264 -- end if;
265
266 end Add_fa_param_to_cache;
267
268 --
269 -- Get the WI Paramter config information from the Cache
270 Procedure Get_WI_param_from_cache(p_param_name in varchar2,
271 p_exists_in_cache OUT NOCOPY varchar2,
272 p_param_value OUT NOCOPY varchar2,
273 p_param_ref_value OUT NOCOPY varchar2,
274 p_log_flag OUT NOCOPY varchar2,
275 p_evaluation_mode OUT NOCOPY varchar2,
276 p_evaluation_proc OUT NOCOPY varchar2,
277 p_default_value OUT NOCOPY varchar2)
278 is
279 i number;
280 begin
281 CheckInited;
282
283 p_exists_in_cache := 'N';
284
285 -- Start from the First entry in the cache
286 i := pv_wi_param_list.FIRST;
287 while i is not null loop
288 if pv_wi_param_list(i).parameter_name = Get_WI_param_from_cache.p_param_name then
289 -- Found!!
290 p_param_value := pv_wi_param_list(i).parameter_value;
291 p_param_ref_value := pv_wi_param_list(i).parameter_ref_value;
292 p_log_flag := pv_wi_param_list(i).log_flag;
293 p_evaluation_mode := pv_wi_param_list(i).evaluation_mode;
294 p_evaluation_proc := pv_wi_param_list(i).evaluation_proc;
295 p_default_value := pv_wi_param_list(i).default_value;
296
297 p_exists_in_cache := 'Y';
298 exit;
299 end if;
300
301 i := pv_wi_param_list.NEXT(i); -- get subscript of next element
302 end loop;
303
304 end Get_WI_param_from_cache;
305
306 --
307 -- Get the WI Paramter config information from the Cache
308 Procedure Get_FA_param_from_cache(p_param_name in varchar2,
309 p_exists_in_cache OUT NOCOPY varchar2,
310 p_param_value OUT NOCOPY varchar2,
311 p_log_flag OUT NOCOPY varchar2,
312 p_evaluation_proc OUT NOCOPY varchar2,
313 p_default_value OUT NOCOPY varchar2)
314 is
315
316 i number;
317 begin
318 CheckInited;
319
320 p_exists_in_cache := 'N';
321
322 -- Start from the First entry in the cache
323 i := pv_fa_param_list.FIRST;
324 while i is not null loop
325 if pv_fa_param_list(i).parameter_name = Get_fa_param_from_cache.p_param_name then
326 -- Found it!!
327 p_param_value := pv_fa_param_list(i).parameter_value;
328 p_log_flag := pv_fa_param_list(i).log_flag;
329 p_evaluation_proc := pv_fa_param_list(i).evaluation_proc;
330 p_default_value := pv_fa_param_list(i).default_value;
331
332 p_exists_in_cache := 'Y';
333 exit;
334 end if;
335
336 i := pv_fa_param_list.NEXT(i); -- get subscript of next element
337 end loop;
338
339 end Get_FA_param_from_cache;
340
341 --
342 -- Remove an element from the Cache
343 Procedure remove_from_cache(p_param_type in varchar2,
344 p_param_name in varchar2)
345 is
346 l_index number;
347 begin
348 CheckInited;
349
350 -- Check if the Paramter exists in the cache and
351 -- get the index to be deleted
352 l_index := get_param_index(p_param_type, p_param_name);
353
354 if l_index > 0 then
355 -- Paramter exists.. Based on the parameter type
356 -- delete the entry from the cache
357 if p_param_type = g_WIParam then
358 -- dbms_output.put_line('removing.. ' || l_index);
359 pv_wi_param_list.delete(l_index);
360 elsif p_param_type = g_FAparam then
361 pv_fa_param_list.delete(l_index);
362 end if;
363 end if;
364 end;
365
366
367 --
368 -- Get the paramter location in the cache.
369 -- If the paramter is not found return 0
370 Function get_param_index(p_param_type in varchar2,
371 p_param_name in varchar2) return number
372 is
373 l_index number := 0;
374 i number := 0;
375 begin
376
377 -- Hit the Work Item Cache
378 if p_param_type = g_WIParam then
379 i := pv_wi_param_list.FIRST;
380 while i is not null loop
381 if pv_wi_param_list(i).parameter_name = p_param_name then
382 -- Found it!!
383 l_index := i;
384 exit;
385 end if;
386
387 -- get subscript of next element
388 i := pv_wi_param_list.NEXT(i);
389 end loop;
390 elsif p_param_type = g_FAParam then
391 -- Hit the FA Cache
392 i := pv_fa_param_list.FIRST;
393 while i is not null loop
394 if pv_fa_param_list(i).parameter_name = p_param_name then
395 -- Found it!!
396 l_index := i;
397 exit;
398 end if;
399
400 -- get subscript of next element
401 i := pv_fa_param_list.NEXT(i);
402 end loop;
403
404 end if;
405
406 return (l_index);
407
408 end get_param_index;
409
410 --
411 -- For a given Workitem_instance_id get the workitem
412 -- Configuration information
413 -- Work Item Name, Work Item ID, Version, Type, Mapping Procedure
414 -- User Defined Workflow Item Type, Process name and User Key
415 Procedure setup_workitem_config(p_wi_instance_id in number)
416 is
417
418 begin
419 for v_GetWIConfig in c_GetWiConfig(p_wi_instance_id) loop
420
421 pv_workitem_id := v_GetWIConfig.workitem_id;
422 pv_workitem_name := v_GetWIConfig.workitem_name;
423 pv_workitem_type := v_GetWIConfig.wi_type_code;
424 pv_wi_item_type := v_GetWIConfig.user_wf_item_type;
425 pv_wi_process_name := v_GetWIConfig.user_wf_process_name;
426
427 if v_GetWIConfig.fa_exec_map_proc is not null then
428 pv_wi_eval_proc_name := v_GetWIConfig.fa_exec_map_proc;
429 end if;
430
431 if v_GetWIConfig.wf_exec_proc is not null then
432 pv_wi_eval_proc_name := v_GetWIConfig.wf_exec_proc;
433 end if;
434
435 pv_wi_key_prefix := v_GetWIConfig.user_wf_process_name;
436
437 end loop;
438
439 -- Print;
440 end setup_workitem_config;
441
442
443 --
447 is
444 -- For a given fa_instance_id get the FA Configuration information
445 -- FA Name, FA ID,Routing Procedure
446 Procedure setup_fa_config(p_fa_instance_id in number)
448 begin
449 for v_GetFAConfig in c_GetFAConfig(p_fa_instance_id) loop
450
451 pv_fa_id := v_GetFAConfig.fulfillment_action_id;
452 pv_fa_name := v_GetFAConfig.fulfillment_action;
453 pv_fe_routing_proc := v_GetFAConfig.fe_routing_proc;
454 end loop;
455
456 -- Print;
457 end setup_fa_config;
458
459 --
460 -- Get the configuration for particular workitem paramter
461 Procedure get_wi_param_config(p_param_name in varchar2,
462 p_param_exists OUT NOCOPY varchar2,
463 p_log_flag OUT NOCOPY varchar2,
464 p_eval_mode OUT NOCOPY varchar2,
465 p_eval_proc OUT NOCOPY varchar2,
466 p_default_value OUT NOCOPY varchar2)
467 is
468 begin
469 p_param_exists := 'N';
470
471 for v_GetWiParamConfig in c_GetWiParamConfig (p_param_name) loop
472 p_log_flag := v_GetWiParamConfig.log_in_audit_trail_flag;
473 p_eval_mode := v_GetWiParamConfig.evaluation_mode;
474 p_eval_proc := v_GetWiParamConfig.evaluation_procedure;
475 p_default_value := v_GetWiParamConfig.default_value;
476
477 p_param_exists := 'Y';
478 end loop;
479
480 end get_wi_param_config;
481
482 --
483 -- Get the configuration for particular FA paramter
484 Procedure get_fa_param_config(p_param_name in varchar2,
485 p_param_exists OUT NOCOPY varchar2,
486 p_log_flag OUT NOCOPY varchar2,
487 p_eval_proc OUT NOCOPY varchar2,
488 p_default_value OUT NOCOPY varchar2)
489 is
490 begin
491 p_param_exists := 'N';
492
493 for v_GetFAParamConfig in c_GetFAParamConfig (p_param_name) loop
494 p_log_flag := v_GetFAParamConfig.log_in_audit_trail_flag;
495 p_eval_proc := v_GetFAParamConfig.evaluation_procedure;
496 p_default_value := v_GetFAParamConfig.default_value;
497
498 p_param_exists := 'Y';
499 end loop;
500
501 end get_fa_param_config;
502
503
504 Procedure CheckInited
505 is
506 begin
507 if pv_workitem_instance_id is null or pv_fa_instance_id is null then
508 raise_application_error(-20012,'Cache Not Initialized');
509 end if;
510
511 end CheckInited;
512
513 Procedure load_workitem_params
514 is
515 begin
516 for v_GetWiParams in c_GetWiParams loop
517 Add_wi_param_to_cache( p_param_name => v_GetWiParams.parameter_name,
518 p_param_value => NULL,
519 p_param_ref_value => NULL,
520 p_log_flag => v_GetWiParams.log_in_audit_trail_flag,
521 p_evaluation_mode => v_GetWiParams.evaluation_mode,
522 p_evaluation_proc => v_GetWiParams.evaluation_procedure,
523 p_default_value => v_GetWiParams.default_value,
524 p_update_db => 'N');
525
526 end loop;
527
528 end load_workitem_params;
529
530 Procedure load_workitem_params(p_wi_param_list in varchar2)
531 is
532 begin
533 if p_wi_param_list is null then
534 load_workitem_params;
535 else
536 for v_GetWiParams in c_GetWiParams loop
537 if INSTR( upper(p_wi_param_list),
538 upper(v_GetWiParams.parameter_name), 1, 1 ) > 0 then
539 Add_wi_param_to_cache( p_param_name => v_GetWiParams.parameter_name,
540 p_param_value => NULL,
541 p_param_ref_value => NULL,
542 p_log_flag => v_GetWiParams.log_in_audit_trail_flag,
543 p_evaluation_mode => v_GetWiParams.evaluation_mode,
544 p_evaluation_proc => v_GetWiParams.evaluation_procedure,
545 p_default_value => v_GetWiParams.default_value,
546 p_update_db => 'N');
547 end if;
548
549 end loop;
550 end if;
551
552 end load_workitem_params;
553
554 Procedure load_fa_params
555 is
556 begin
557 for v_GetFAParams in c_GetFAParams loop
558 Add_fa_param_to_cache( p_param_name => v_GetFAParams.parameter_name,
559 p_param_value => NULL,
560 p_log_flag => v_GetFAParams.log_in_audit_trail_flag,
561 p_evaluation_proc => v_GetFAParams.evaluation_procedure,
562 p_default_value => v_GetFAParams.default_value,
563 p_update_db => 'N');
564
565 end loop;
566
567 end load_fa_params;
568
569
570 Procedure Print
571 is
572
573 begin
574 null;
575 -- dbms_output.put_line('WI Inst: ' || pv_workitem_instance_id || ':');
576 -- dbms_output.put_line('WI ID: ' || pv_workitem_id || ':');
577
578 -- dbms_output.put_line('Name: ' || pv_workitem_name || ':');
579 -- dbms_output.put_line('Type: ' || pv_workitem_type || ':');
580
581 -- dbms_output.put_line('Eval: ' || pv_wi_eval_proc_name|| ':');
582
583 -- dbms_output.put_line('WF Type: ' || pv_wi_item_type || ':');
584 -- dbms_output.put_line('WF Process: ' || pv_wi_process_name || ':');
585 -- dbms_output.put_line('WF Key: ' || pv_wi_key_prefix || ':');
586
587
588 -- dbms_output.put_line('FA Inst: ' || pv_fa_instance_id || ':');
589 -- dbms_output.put_line('FA ID: ' || pv_fa_id || ':');
590
591 -- dbms_output.put_line('Name: ' || pv_fa_name || ':');
592 -- dbms_output.put_line('Routing Proc: ' || pv_fe_routing_proc || ':');
593
594 end Print;
595
596
597 end XDP_PARAM_CACHE;