1 package body wf_bes_funcs as
2 /* $Header: WFBESFNB.pls 120.3 2006/07/21 14:35:44 nravindr noship $ */
3
4 --Directory where the generated files will be stored
5 utl_dir varchar2(512);
6 g_amp varchar2(1):='&';
7
8 --Function to get the list of items from a comma separated string
9 FUNCTION Tokenize(cslist VARCHAR2) return varchar_array;
10
11 --
12 -- Procedure : GenerateStatic
13 --
14 -- Purpose : Main Procedure which will be called by the admin script/
15 -- concurrent program. Depending on the value in p_key,
16 -- the files for the corresponding packages are generated
17 --
18 -- Parameters: retcode - used by the concurrent program
19 -- errbuf - used by the concurrent program
20 -- p_object_type - used by the concurrent program
21 -- p_key - comma separated string of the
22 -- correlationid/agent name
23 --
24 Procedure GenerateStatic(retcode out nocopy varchar2,
25 errbuf out nocopy varchar2,
26 p_object_type in varchar2,
27 p_key in varchar2) AS
28
29 l_var_list varchar_array;
30
31 l_key varchar2(512);
32
33 begin
34 --Get the directory where the output file is to be generated
35 select value
36 into utl_dir
37 from v$parameter
38 where name = 'utl_file_dir';
39
40 --The value can be a comma separeated list, So take the first location and
41 --use that as the output directory.
42 if(instr(utl_dir, ',') <> 0) then
43 utl_dir:=trim(substr(utl_dir,1,instr(utl_dir,',')-1));
44 else
45 utl_dir:=trim(utl_dir);
46 end if;
47
48 l_key:=upper(trim(p_key));
49 if(upper(p_object_type)='EVENTS') then
50
51 --check if the default wf and fnd packages are included
52 --if it is not there, then add it.
53 if(instr(l_key,'ORACLE.APPS.WF.')=0) then
54 l_key:=l_key||',ORACLE.APPS.WF.';
55 end if;
56 if(instr(l_key,'ORACLE.APPS.FND.')=0) then
57 l_key:=l_key||',ORACLE.APPS.FND.';
58 end if;
59 WF_BES_FUNCS.StaticGenerateRule(Tokenize(l_key));
60
61 elsif(upper(p_object_type)='AGENTS') then
62
63 WF_BES_FUNCS.StaticQH(Tokenize(l_key));
64
65 end if;
66 end;
67
68 --
69 -- Procedure : StaticQH
70 --
71 -- Purpose : Procedure to generate the static Enqueue/Dequeue
72 -- procedures
73 --
74 Procedure StaticQH(p_agent_names in varchar_array) AS
75
76 fh utl_file.file_type;
77 l_generated boolean := FALSE;
78 l_atleast_one boolean;
79 l_first boolean;
80
81 l_mesg varchar2(4000);
82 l_timestamp varchar2(20);
83
84 cursor all_Queue_handlers(p_agent_name in varchar2) is
85 select distinct upper(trim(v.queue_handler)) qhandler
86 from (
87 select queue_handler
88 from wf_agents
89 where queue_handler is not null and
90 instr(upper(name),p_agent_name)=1
91 )v, user_objects uo
92 where uo.object_name = v.queue_handler
93 and uo.object_type = 'PACKAGE BODY'
94 and uo.status = 'VALID';
95
96 l_filename varchar2(100);
97
98 Begin
99
100 select to_char(sysdate,'DDMONYYYYHH24MISS')
101 into l_timestamp
102 from dual;
103
104 l_filename := 'WFAGTDFNB'||l_timestamp||'.pls';
105
106 fh := utl_file.fopen(utl_dir, l_filename, 'w', 32767);
107
108 utl_file.put_line(fh, 'REM dbdrv: sql ~PROD ~PATH ~FILE none none none package '||g_amp||'phase=plb \');
109 utl_file.put_line(fh, 'REM dbdrv: checkfile(115.2=120.3):~PROD:~PATH:~FILE');
110 utl_file.put_line(fh, '/*=======================================================================*');
111 utl_file.put_line(fh, '| Copyright (c) 2005, Oracle. All Rights Reserved |');
112 utl_file.put_line(fh, '+========================================================================+');
113 utl_file.put_line(fh, '| NAME |');
114 utl_file.put_line(fh, '| WFAGTDFNB'||l_timestamp||'.pls |');
115 utl_file.put_line(fh, '| |');
116 utl_file.put_line(fh, '| DESCRIPTION |');
117 utl_file.put_line(fh, '| This is a generated file to provide static calls for |');
118 utl_file.put_line(fh, '| Enqueue/Dequeue functions. |');
119 utl_file.put_line(fh, '| |');
120 utl_file.put_line(fh, '*========================================================================*/ ');
121 utl_file.put_line(fh,'SET VERIFY OFF;');
122 utl_file.put_line(fh,'WHENEVER SQLERROR EXIT FAILURE ROLLBACK;');
123 utl_file.put_line(fh,'WHENEVER OSERROR EXIT FAILURE ROLLBACK;');
124
125 utl_file.put_line(fh,'create or replace package body WF_AGT_DYN_FUNCS as');
126
127 utl_file.put_line(fh,'');
128 utl_file.put_line(fh,'--');
129 utl_file.put_line(fh,'-- Static enqueue procedure calls ');
130 utl_file.put_line(fh,'--');
131 utl_file.put_line(fh,'PROCEDURE StaticEnqueue(p_qh_name in varchar2,');
132 utl_file.put_line(fh,' p_event in wf_event_t,');
133 utl_file.put_line(fh,' p_out_agent_override in wf_agent_t,');
134 utl_file.put_line(fh,' p_executed out nocopy boolean)');
135 utl_file.put_line(fh,'as');
136 utl_file.put_line(fh,' l_qh_name varchar2(240);');
137 utl_file.put_line(fh,'begin');
138 utl_file.put_line(fh,' p_executed := FALSE;');
139 utl_file.put_line(fh,' l_qh_name := upper(trim(p_qh_name));');
140
141 if (p_agent_names is not null) then
142 for i in p_agent_names.FIRST..p_agent_names.LAST loop
143 l_first := TRUE;
144 l_atleast_one := FALSE;
145 for qhandler_rec in all_Queue_handlers(upper(p_agent_names(i))) loop
146 l_atleast_one := TRUE;
147 l_generated := TRUE;
148 utl_file.put_line(fh,' if (l_qh_name = '''
149 || qhandler_rec.qhandler || ''') then');
150 utl_file.put_line(fh,' '||qhandler_rec.qhandler
151 || '.Enqueue(p_event, p_out_agent_override);');
152 utl_file.put_line(fh,' p_executed := TRUE;');
153 utl_file.put_line(fh,' return;');
154 utl_file.put_line(fh,' end if; ');
155 end loop;
156 end loop; -- p_agent_names
157 end if; -- p_agent_names
158
159 -- give a message within the generated file regarding the failure
160 if (not l_generated) then
161 utl_file.put_line(fh, '');
162 utl_file.put_line(fh, ' -- Package body could not be generated for the agent names given.');
163 utl_file.put_line(fh, ' -- The reason could be because the procedure(s) referred to by the');
164 utl_file.put_line(fh, ' -- agent name(s) was invalid or the agent name(s) specified do not exist');
165 utl_file.put_line(fh, '');
166 end if;
167
168 utl_file.put_line(fh,'end StaticEnqueue;');
169
170 utl_file.put_line(fh,'');
171 utl_file.put_line(fh,'--');
172 utl_file.put_line(fh,'-- Static dequeue procedure calls ');
173 utl_file.put_line(fh,'--');
174
175 utl_file.put_line(fh,'PROCEDURE StaticDequeue(p_qh_name in varchar2,');
176 utl_file.put_line(fh,' p_agent_guid in raw,');
177 utl_file.put_line(fh,' p_event in out nocopy wf_event_t,');
178 utl_file.put_line(fh,' p_wait in binary_integer,');
179 utl_file.put_line(fh,' p_executed out nocopy boolean)');
180
181 utl_file.put_line(fh,'as');
182 utl_file.put_line(fh,' l_qh_name varchar2(240);');
183 utl_file.put_line(fh,'begin');
184 utl_file.put_line(fh,' p_executed := FALSE;');
185 utl_file.put_line(fh,' l_qh_name := upper(trim(p_qh_name));');
186
187 if (p_agent_names is not null) then
188 for i in p_agent_names.FIRST..p_agent_names.LAST loop
189 l_first := TRUE;
190 l_atleast_one := FALSE;
191 for qhandler_rec in all_Queue_handlers(upper(p_agent_names(i))) loop
192 l_atleast_one := TRUE;
193 l_generated := TRUE;
194 utl_file.put_line(fh,' if (l_qh_name = '''
195 || qhandler_rec.qhandler || ''') then');
196 utl_file.put_line(fh,' '||qhandler_rec.qhandler
197 || '.Dequeue(p_agent_guid, p_event, p_wait);');
198 utl_file.put_line(fh,' p_executed := TRUE;');
199 utl_file.put_line(fh,' return;');
200 utl_file.put_line(fh,' end if; ');
201 end loop;
202 end loop; -- p_agent_names
203 end if; -- p_agent_names
204
205 -- give a message within the generated file regarding the failure
206 if (not l_generated) then
207 utl_file.put_line(fh, '');
208 utl_file.put_line(fh, ' -- Package body could not be generated for the agent name given.');
209 utl_file.put_line(fh, ' -- The reason could be because the procedure(s) referred to by the');
210 utl_file.put_line(fh, ' -- agent name(s) was invalid or the agent name(s) specified do not exist');
211 utl_file.put_line(fh, '');
212 end if;
213
214 utl_file.put_line(fh,'end StaticDequeue;');
215 utl_file.put_line(fh,' ');
216 utl_file.put_line(fh,'end WF_AGT_DYN_FUNCS;');
217 utl_file.put_line(fh,'/');
218 utl_file.put_line(fh,'commit;');
219 utl_file.put_line(fh,'exit;');
220 utl_file.put_line(fh,' ');
221 utl_file.fclose(fh);
222
223 dbms_output.put_line('File generated is '||utl_dir||'/'||l_filename);
224 exception
225 when others then
226 if (utl_file.is_open(fh)) then
227 utl_file.fclose(fh);
228 end if;
229 raise;
230
231 end StaticQH;
232
233 --
234 -- Procedure : StaticGenerateRule
235 --
236 -- Purpose : Procedure to create the generate and rule functions
237 --
238 --
239
240 Procedure StaticGenerateRule(p_correlation_ids in varchar_array) AS
241
242 fh utl_file.file_type;
243 l_generated boolean := FALSE;
244 l_atleast_one boolean;
245 l_first boolean;
246
247 l_generated_r boolean := FALSE;
248 l_atleast_one_r boolean;
249 l_first_r boolean;
250
251 l_mesg varchar2(4000);
252 l_timestamp varchar2(20);
253
254 cursor all_generate_funcs(p_corrid in varchar2) is
255 select distinct upper(trim(v.function)) function
256 from (
257 select upper(substr(we.generate_function,1,instr(we.generate_function, '.')-1)) package_name,
258 we.generate_function function
259 from wf_events we
260 where we.generate_function is not null and
261 instr(upper(we.name),p_corrid)=1
262 ) v, user_objects uo
263 where uo.object_name = nvl(v.package_name,v.function) and
264 uo.object_type = decode(v.package_name,null,'FUNCTION','PACKAGE BODY') and
265 uo.status = 'VALID';
266
267 cursor all_rule_funcs(p_corrid in varchar2) is
268 select distinct upper(trim(v.function)) function
269 from (
270 select upper(substr(wes.rule_function,1,instr(wes.rule_function, '.')-1)) package_name,
271 wes.rule_function function
272 from wf_events we,wf_event_subscriptions wes
273 where we.guid = wes.event_filter_guid and
274 instr(upper(we.name),p_corrid)=1 and
275 wes.rule_function is not null and
276 upper(wes.rule_function) not like 'WF_RULE%' and
277 upper(wes.rule_function) not like 'WF_XML%'
278 ) v, user_objects uo
279 where uo.object_name = nvl(v.package_name,v.function) and
280 uo.object_type = decode(v.package_name,null,'FUNCTION','PACKAGE BODY') and
281 uo.status = 'VALID';
282
283 cursor seeded_rule_funcs is
284 select distinct upper(trim(v.function)) function
285 from (
286 select upper(substr(wes.rule_function,1,instr(wes.rule_function, '.')-1)) package_name,
287 wes.rule_function function
288 from wf_events we,wf_event_subscriptions wes
289 where we.guid = wes.event_filter_guid and
290 wes.rule_function is not null and
291 (
292 upper(wes.rule_function) like 'WF_RULE%' or
293 upper(wes.rule_function) like 'WF_XML%'
294 )
295 ) v, user_objects uo
296 where uo.object_name = nvl(v.package_name,v.function) and
297 uo.object_type = decode(v.package_name,null,'FUNCTION','PACKAGE BODY') and
298 uo.status = 'VALID';
299
300 l_filename varchar2(100);
301 Begin
302
303 select to_char(sysdate,'DDMONYYYYHH24MISS')
304 into l_timestamp
305 from dual;
306
307 l_filename := 'WFBESDFNB'||l_timestamp||'.pls';
308
309 fh := utl_file.fopen(utl_dir, l_filename, 'w', 32767);
310
311 utl_file.put_line(fh, 'REM dbdrv: sql ~PROD ~PATH ~FILE none none none package '||g_amp||'phase=plb \');
312 utl_file.put_line(fh, 'REM dbdrv: checkfile(115.2=120.3):~PROD:~PATH:~FILE');
313 utl_file.put_line(fh, '/*=======================================================================*');
314 utl_file.put_line(fh, '| Copyright (c) 2005, Oracle. All Rights Reserved |');
315 utl_file.put_line(fh, '+========================================================================+');
316 utl_file.put_line(fh, '| NAME |');
317 utl_file.put_line(fh, '| WFBESDFNB'||l_timestamp||'.pls |');
318 utl_file.put_line(fh, '| |');
319 utl_file.put_line(fh, '| DESCRIPTION |');
320 utl_file.put_line(fh, '| PL/SQL body for package WF_BES_DYN_FUNCS |');
321 utl_file.put_line(fh, '| This is a generated file to provide static calls for |');
322 utl_file.put_line(fh, '| generate and rule functions |');
323 utl_file.put_line(fh, '| |');
324 utl_file.put_line(fh, '| NOTES |');
325 utl_file.put_line(fh, '| This package body has static function calls for following event |');
326 utl_file.put_line(fh, '| names or corrlation ids |');
327 utl_file.put_line(fh, '| oracle.apps.wf.% |');
328 utl_file.put_line(fh, '| oracle.apps.fnd.% |');
329 utl_file.put_line(fh, '| |');
330 utl_file.put_line(fh, '*========================================================================*/ ');
331 utl_file.put_line(fh,'SET VERIFY OFF;');
332 utl_file.put_line(fh,'WHENEVER SQLERROR EXIT FAILURE ROLLBACK;');
333 utl_file.put_line(fh,'WHENEVER OSERROR EXIT FAILURE ROLLBACK;');
334
335 utl_file.put_line(fh,'create or replace package body WF_BES_DYN_FUNCS as');
336
337 utl_file.put_line(fh,'--');
338 utl_file.put_line(fh,'-- Generate functions');
339 utl_file.put_line(fh,'--');
340
341 utl_file.put_line(fh,'PROCEDURE Generate(p_func_name in varchar2,');
342 utl_file.put_line(fh,' p_event_name in varchar2,');
343 utl_file.put_line(fh,' p_event_key in varchar2,');
344 utl_file.put_line(fh,' p_parameter_list in wf_parameter_list_t,');
345 utl_file.put_line(fh,' x_msg in out nocopy clob,');
346 utl_file.put_line(fh,' x_executed out nocopy boolean)');
347 utl_file.put_line(fh,'as');
348 utl_file.put_line(fh,' l_funcname varchar2(240);');
349 utl_file.put_line(fh,'begin');
350 utl_file.put_line(fh,' x_executed := FALSE;');
351 utl_file.put_line(fh,' x_msg := null;');
352 utl_file.put_line(fh,' l_funcname := upper(trim(p_func_name));');
353
354 if (p_correlation_ids is not null) then
355 for i in p_correlation_ids.FIRST..p_correlation_ids.LAST loop
356 l_first := TRUE;
357 l_atleast_one := FALSE;
358 for genfunc_rec in all_generate_funcs(p_correlation_ids(i)) loop
359
360 l_atleast_one := TRUE;
361 l_generated := TRUE;
362 if (l_first) then
363 utl_file.put_line(fh, '');
364 utl_file.put_line(fh, ' -- Function calls for corrid ' || p_correlation_ids(i) || '%');
365 utl_file.put_line(fh, ' if (upper(p_event_name) like '''
366 || p_correlation_ids(i) || '%'') then ');
367 l_first := FALSE;
368 end if;
369
370 utl_file.put_line(fh,' if (l_funcname = '''
371 || genfunc_rec.function || ''') then');
372 utl_file.put_line(fh,' ' || 'x_msg := '||genfunc_rec.function
373 || '(p_event_name, p_event_key,p_parameter_list);');
374 utl_file.put_line(fh,' x_executed := TRUE;');
375 utl_file.put_line(fh,' return;');
376 utl_file.put_line(fh,' end if; ');
377
378 end loop;--for loop
379
380 if (l_atleast_one) then
381 utl_file.put_line(fh, ' end if;');
382 end if;
383
384 end loop; --for loop
385
386 end if;
387
388 -- give a message within the generated file regarding the failure
389 if (not l_generated) then
390 utl_file.put_line(fh, '');
391 utl_file.put_line(fh, ' -- Package body could not be generated for the corrids given.');
392 utl_file.put_line(fh, ' -- The reason could be because the procedure(s) referred to by the');
393 utl_file.put_line(fh, ' -- corrid(s) was invalid or the corrid(s) specified do not exist');
394 utl_file.put_line(fh, '');
395 end if;
396
397 utl_file.put_line(fh,'end Generate;');
398 utl_file.put_line(fh, '');
399 utl_file.put_line(fh,'--');
400 utl_file.put_line(fh,'-- Rule functions');
401 utl_file.put_line(fh,'--');
402
403 utl_file.put_line(fh,'PROCEDURE RuleFunction(p_func_name in varchar2,');
404 utl_file.put_line(fh,' p_subscription_guid in raw,');
405 utl_file.put_line(fh,' p_event in out nocopy wf_event_t,');
406 utl_file.put_line(fh,' x_result in out nocopy varchar2,');
407 utl_file.put_line(fh,' x_executed out nocopy boolean)');
408 utl_file.put_line(fh,'as');
409 utl_file.put_line(fh,' l_funcname varchar2(240);');
410 utl_file.put_line(fh,' l_event_name varchar2(240);');
411 utl_file.put_line(fh,'begin');
412 utl_file.put_line(fh,' x_executed := FALSE;');
413 utl_file.put_line(fh,' l_funcname := upper(trim(p_func_name));');
414 utl_file.put_line(fh,' l_event_name := upper(p_event.event_name);');
415
416
417 l_first_r := TRUE;
418 for seeded_rulefunc_rec in seeded_rule_funcs() loop
419 if (l_first_r) then
420 utl_file.put_line(fh, '');
421 utl_file.put_line(fh, ' -- Seeded Rule Functions');
422 l_first_r := FALSE;
423 end if;
424
425 utl_file.put_line(fh,' if (l_funcname = '''
429 utl_file.put_line(fh,' x_executed := TRUE;');
426 || seeded_rulefunc_rec.function || ''') then');
427 utl_file.put_line(fh,' ' || 'x_result := '||seeded_rulefunc_rec.function
428 || '(p_subscription_guid, p_event);');
430 utl_file.put_line(fh,' return;');
431 utl_file.put_line(fh,' end if; ');
432 end loop;
433
434
435 if (p_correlation_ids is not null) then
436 for i in p_correlation_ids.FIRST..p_correlation_ids.LAST loop
437
438 l_first_r := TRUE;
439 l_atleast_one_r := FALSE;
440 for rulefunc_rec in all_rule_funcs(p_correlation_ids(i)) loop
441 l_atleast_one_r := TRUE;
442 l_generated_r := TRUE;
443 if (l_first_r) then
444 utl_file.put_line(fh, '');
445 utl_file.put_line(fh, ' -- Function calls for correlation id ' || p_correlation_ids(i) ||'%');
446 utl_file.put_line(fh, ' if (l_event_name like '''
447 || p_correlation_ids(i) || '%'') then ');
448 l_first_r := FALSE;
449 end if;
450
451 utl_file.put_line(fh,' if (l_funcname = '''
452 || rulefunc_rec.function || ''') then');
453 utl_file.put_line(fh,' ' || 'x_result := '||rulefunc_rec.function
454 || '(p_subscription_guid, p_event);');
455 utl_file.put_line(fh,' x_executed := TRUE;');
456 utl_file.put_line(fh,' return;');
457 utl_file.put_line(fh,' end if; ');
458 end loop;
459 if (l_atleast_one_r) then
460 utl_file.put_line(fh, ' end if;');
461 end if;
462 end loop; -- p_correlation_ids
463 end if; -- p_correlation_ids
464
465 -- give a message within the generated file regarding the failure
466 if (not l_generated_r) then
467 utl_file.put_line(fh, '');
468 utl_file.put_line(fh, ' -- Package body could not be generated for the corrid given.');
469 utl_file.put_line(fh, ' -- The reason could be because the procedure(s) referred to by the');
470 utl_file.put_line(fh, ' -- corrid(s) was invalid or the corrid(s) specified do not exist');
471 utl_file.put_line(fh, '');
472 end if;
473
474 utl_file.put_line(fh,'end RuleFunction;');
475
476
477 utl_file.put_line(fh,'end WF_BES_DYN_FUNCS;');
478 utl_file.put_line(fh,'/');
479 utl_file.put_line(fh,'commit;');
480 utl_file.put_line(fh,'exit;');
481 utl_file.put_line(fh,' ');
482 utl_file.fclose(fh);
483
484 dbms_output.put_line('File generated is '||utl_dir||'/'||l_filename);
485 exception
486 when others then
487 if (utl_file.is_open(fh)) then
488 utl_file.fclose(fh);
489 end if;
490 raise;
491 end StaticGenerateRule;
492
493
494 --
495 -- Procedure : Tokenize
496 --
497 -- Purpose : Process the comma separated string into tokens
498 -- and put the value in the varray
499 --
500 -- Return : Varray containing the tokens
501 --
502
503 FUNCTION Tokenize(cslist VARCHAR2) return varchar_array AS
504
505 -- pointer where to start the search for comma
506 l_ptr int;
507
508 --count to keep track of tokens added to varray
509 l_count int :=1;
510
511 --the token separated from the input
512 l_token varchar2(100);
513
514 --position of comma
515 l_pos int;
516
517 -- the array of tokens to be returned
518 l_var_list varchar_array:=varchar_array();
519
520 begin
521
522 l_ptr := 1;
523 --loop till no more commas are found
524 loop
525 l_pos := instr(cslist,',',l_ptr,1);
526
527 --if no comma is found, then the next token
528 --is the last token. add it to the array and exit
529 if(l_pos=0) then
530
531 l_token := substr(cslist,l_ptr,length(cslist));
532 l_var_list.extend;
533 --Remove any % character found
534 l_var_list(l_count):=replace(trim(l_token),'%','');
535 exit;
536 end if;
537 --if a comma is found, get the substring till the index
538 --and add it to varray
539 l_token:= substr(cslist,l_ptr,(l_pos-l_ptr));
540 l_var_list.extend;
541 --Remove any % character found
542 l_var_list(l_count):=replace(trim(l_token),'%','');
543
544 l_ptr:=l_pos+1;
545 l_count:=l_count+1;
546
547 end loop;
548 return l_var_list;
549 end;
550
551 end WF_BES_FUNCS;
[Home] [Help]