[Home] [Help]
PACKAGE BODY: APPS.WF_EVENT_SYNCHRONIZE_PKG
Source
1 package body WF_EVENT_SYNCHRONIZE_PKG as
2 /* $Header: WFEVSYNB.pls 120.6 2006/08/21 13:37:06 nravindr ship $ */
3 ------------------------------------------------------------------------------
4 /*
5 ** PRIVATE global variable
6 */
7 -- g_begin_clob varchar2(100) := '<oracle.apps.wf.event.all.sync>'||wf_core.newline;
8 g_begin_clob varchar2(100) := '<oracle.apps.wf.event.all.sync>';
9 g_end_clob varchar2(100) := '</oracle.apps.wf.event.all.sync>';
10 g_begin_string varchar2(100) := '<WF_TABLE_DATA>';
11 g_end_string varchar2(100) := '</WF_TABLE_DATA>';
12 g_system varchar2(100) := '<WF_SYSTEMS>';
13 g_agent varchar2(100) := '<WF_AGENTS>';
14 g_agent_group varchar2(100) := '<WF_AGENT_GROUPS>';
15 g_event varchar2(100) := '<WF_EVENTS>';
16 g_event_group varchar2(100) := '<WF_EVENT_GROUPS>';
17 g_event_sub varchar2(100) := '<WF_EVENT_SUBSCRIPTIONS>';
18 g_objecttype varchar2(100);
19 g_qowner varchar2(30);
20
21 ------------------------------------------------------------------------------
22 function SYNCHRONIZE (
23 P_SUBSCRIPTION_GUID in raw,
24 P_EVENT in out nocopy wf_event_t
25 ) return varchar2 is
26 /*
27 ** Synchronize - Rule Function for Local Sync Event, return varchar2
28 ** Parameters: p_Subscription_Guid
29 ** p_Event
30 **
31 */
32 l_clob clob;
33 l_result varchar2(100);
34 begin
35
36 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
37 wf_event_synchronize_pkg.CreateSyncClob(p_eventdata => l_clob);
38
39 p_event.SetEventData(l_clob);
40
41 l_result := wf_rule.default_rule(p_subscription_guid, p_event);
42
43 return (l_result);
44
45 exception
46 when others then
47 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'SYNCHRONIZE', p_event.event_name,
48 p_event.event_key,
49 'ERROR'); raise;
50 return('ERROR');
51 end;
52 ------------------------------------------------------------------------------
53 /*
54 ** SynchronizeUpload - Rule Function for External Sync Event, return varchar2
55 ** Parameters: p_Subscription_Guid
56 ** p_Event
57 **
58 */
59 function SYNCHRONIZEUPLOAD (
60 P_SUBSCRIPTION_GUID in raw,
61 P_EVENT in out nocopy wf_event_t
62 ) return varchar2 is
63
64 l_result varchar2(100);
65 begin
66
67 wf_event_synchronize_pkg.uploadsyncclob(p_event.event_data);
68
69 l_result := wf_rule.default_rule(p_subscription_guid, p_event);
70
71 return (l_result);
72
73 exception
74 when others then
75 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'SYNCHRONIZEUPLOAD', p_event.event_name,
76 p_event.event_key,
77 'ERROR'); raise;
78 return('ERROR');
79 end;
80 ------------------------------------------------------------------------------
81 /*
82 ** CreateSyncClob - Called by CreateFile or by Sync Event, returns CLOB
83 ** Parameters: ObjectType <may be null>
84 ** ObjectKey <may be null>
85 */
86 procedure CREATESYNCCLOB (
87 P_OBJECTTYPE in varchar2,
88 P_OBJECTKEY in varchar2,
89 P_ISEXACTNUM in integer,
90 P_OWNERTAG in varchar2,
91 P_EVENTDATA out nocopy clob
92 ) is
93
94 syncclob clob;
95 l_ObjectKey varchar2(100);
96 p_isexact boolean;
97
98 begin
99 g_objecttype := upper(p_objecttype);
100
101 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
102 wf_log_pkg.string(wf_log_pkg.level_procedure,
103 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.CREATESYNCCLOB.Begin',
104 'Entered Create Sync Clob');
105 end if;
106
107 IF (p_isexactnum = 1) THEN
108 p_isexact := true;
109 ELSE
110 p_isexact := false;
111 END IF;
112
113 IF p_ObjectKey IS NOT NULL THEN
114 l_ObjectKey := '%'||p_ObjectKey||'%';
115 END IF;
116
117 dbms_lob.createtemporary(p_eventdata, FALSE, DBMS_LOB.CALL);
118
119 dbms_lob.writeappend(p_eventdata, length(g_begin_clob), g_begin_clob);
120
121 --
122 -- Might have to change these to constants for MLS
123 --
124 -- Bug 2558446: Events, Subscriptions and Agents/Systems downloaded in separate
125 -- Files.
126 IF g_objecttype in ('SYSTEMS', 'AGENTS', 'AGENTGROUPS') THEN
127 -- Systems, Agents, Agent Groups in one file
128 dbms_lob.append(p_eventdata,
129 wf_event_synchronize_pkg.GetSystems(l_ObjectKey));
130 dbms_lob.append(p_eventdata,
131 wf_event_synchronize_pkg.GetAgents(l_ObjectKey,p_isexact));
132 dbms_lob.append(p_eventdata,
133 wf_event_synchronize_pkg.GetAgentGroups(l_ObjectKey));
134 ELSIF g_objecttype = 'EVENTS' THEN
135 -- Download event and event groups
136 dbms_lob.append(p_eventdata,
137 wf_event_synchronize_pkg.GetEvents(l_ObjectKey, p_ownertag));
138
139 dbms_lob.append(p_eventdata,
140 wf_event_synchronize_pkg.GetEventGroups(
141 l_ObjectKey,p_ownertag));
142 ELSIF g_objecttype = 'EVENT_GROUPS' THEN
143 -- Download event groups
144 dbms_lob.append(p_eventdata,
145 wf_event_synchronize_pkg.GetGroups(l_ObjectKey, p_ownertag));
146
147 -- EVENT_GROUPS option now downloads only GROUP type objects. Not members
148 -- dbms_lob.append(p_eventdata,
149 -- wf_event_synchronize_pkg.GetEventGroupByGroup(l_ObjectKey,p_ownertag));
150 ELSIF g_objecttype = 'SUBSCRIPTIONS' THEN
151 -- Download subscriptions in one file
152 dbms_lob.append(p_eventdata,
153 wf_event_synchronize_pkg.GetSubscriptions(l_ObjectKey,
154 p_isexact, p_ownertag));
155
156 ELSE -- including ALL
157
158 dbms_lob.append(p_eventdata,
159 wf_event_synchronize_pkg.GetSystems(l_ObjectKey));
160 dbms_lob.append(p_eventdata,
161 wf_event_synchronize_pkg.GetAgents(l_ObjectKey,p_isexact));
162 dbms_lob.append(p_eventdata,
163 wf_event_synchronize_pkg.GetAgentGroups(l_ObjectKey));
164 dbms_lob.append(p_eventdata,
165 wf_event_synchronize_pkg.GetEvents(l_ObjectKey, p_ownertag));
166 dbms_lob.append(p_eventdata,
167 wf_event_synchronize_pkg.GetSubscriptions(l_ObjectKey,p_isexact, p_ownertag));
168 dbms_lob.append(p_eventdata,
169 wf_event_synchronize_pkg.GetEventGroups(l_ObjectKey, p_ownertag));
170
171 END IF;
172
173 dbms_lob.writeappend(p_eventdata, length(g_end_clob), g_end_clob);
174
175 --return (syncclob);
176
177 exception
178 when others then
179 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'CREATESYNCCLOB', g_objecttype,
180 p_ObjectKey ,null);
181 raise;
182 end;
183 ------------------------------------------------------------------------------
184 /*
185 ** CreateFile - Called from SQL*Plus, creates Sync File
186 ** Parameters: Directory
187 ** Filename
188 ** ObjectType
189 ** ObjectKey
190 **
191 */
192 procedure CREATEFILE (
193 P_DIRECTORY in varchar2,
194 P_FILENAME in varchar2,
195 P_OBJECTTYPE in varchar2,
196 P_OBJECTKEY in varchar2,
197 P_ISEXACT in boolean
198 ) is
199
200 l_clob clob;
201 l_clobsize integer := 0;
202 l_isExactNum integer := 1;
203
204 begin
205 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
206 wf_log_pkg.string(wf_log_pkg.level_procedure,
207 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.CREATEFILE.Begin',
208 'Entered Create File');
209 end if;
210
211 if (p_isexact) then
212 l_isExactNum := 1;
213 else
214 l_isExactNum := 0;
215 end if;
216
217 dbms_lob.createtemporary( l_clob, FALSE, DBMS_LOB.CALL);
218
219 wf_event_synchronize_pkg.CreateSyncClob(p_ObjectType, p_ObjectKey, l_isExactNum, null, l_clob);
220
221 if (dbms_lob.getlength(l_clob) = 0) then
222 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
223 wf_log_pkg.string(wf_log_pkg.level_statement,
224 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.CREATEFILE.Clob_Size',
225 'l_clob null');
226 end if;
227 end if;
228
229 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
230 wf_log_pkg.string(wf_log_pkg.level_statement,
231 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.CREATEFILE.Create',
232 'Calling to CreateClob File');
233 end if;
234
235 wf_event_synchronize_pkg.CreateClobFile(p_Directory, p_Filename, l_clob);
236
237 exception
238 when others then
239 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'CREATEFILE', p_Directory||
240 '*'||p_Filename,p_ObjectType||'*'||p_ObjectKey ,null);
241 raise;
242 end;
243 ------------------------------------------------------------------------------
244 /*
245 ** CreateClobFile Given a Clob, we create a file
246 ** Parameters: Directory Path
247 ** Filename
248 ** Clob
249 */
250 procedure CREATECLOBFILE (
251 P_DIRECTORY in varchar2,
252 P_FILENAME in varchar2,
253 P_CLOB in clob
254 ) is
255
256 l_filehandle UTL_FILE.FILE_TYPE;
257 l_clob clob;
258
259 l_current_position integer := 1;
260 l_amount_to_read integer := 0;
261 l_messagedata varchar2(32000);
262 l_length_end_string integer := 16; -- Length of end tag
263 l_counter integer := 0;
264 l_begin_position integer := 0;
265 l_end_position integer := 0;
266
267 begin
268 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
269 wf_log_pkg.string(wf_log_pkg.level_procedure,
270 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.CREATECLOBFILE.Begin',
271 'Entered Create Clob File: '||p_Directory||'-'||p_Filename);
272 end if;
273
274 l_filehandle := UTL_FILE.FOPEN(p_Directory, p_Filename,'w');
275
276 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
277 wf_log_pkg.string(wf_log_pkg.level_statement,
278 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.CREATECLOBFILE.file_handle',
279 'Got file handle');
280 end if;
281
282 --
283 -- At in Begin Sync Tag
284 --
285 utl_file.putf(l_filehandle, g_begin_clob);
286 utl_file.new_line(l_filehandle, 1);
287
288 --
289 LOOP
290 --
291 -- We look through the CLOB for a each Object until there
292 -- are none
293 --
294 l_counter := l_counter + 1;
295
296 l_begin_position := dbms_lob.instr(p_clob, g_begin_string,
297 1, l_counter);
298
299 EXIT when l_begin_position = 0; -- No More Event Objects left
300
301 l_end_position := dbms_lob.instr(p_clob , g_end_string,
302 1, l_counter);
303
304 --
305 -- Figure out the amount to read out of the CLOB, and subst
306 --
307 l_end_position := l_end_position + l_length_end_string;
308
309 l_amount_to_read := l_end_position - l_begin_position;
310
311 l_messagedata := dbms_lob.substr(p_clob, l_amount_to_read,
312 l_begin_position);
313
314 utl_file.putf(l_filehandle, l_messagedata);
315
316 utl_file.new_line(l_filehandle, 1);
317
318 END LOOP;
319
320 /**
321 LOOP
322
323 l_messagedata := dbms_lob.substr(p_clob, l_splice_size,
324 l_current_position);
325
326 utl_file.putf(l_filehandle, l_messagedata);
327
328 wf_log_pkg.string(6, 'WF_EVENT_SYNCHRONIZE_PKG.CREATEFILE',
329 substr(l_messagedata,1,l_splice_size));
330
331 l_current_position := l_current_position + l_splice_size;
332
333 EXIT WHEN l_current_position = l_clobsize;
334
335 IF l_current_position + l_splice_size > l_clobsize THEN
336 l_splice_size := l_clobsize - l_current_position;
337 END IF;
338
339 END LOOP;
340 **/
341 --
342 -- Add in End Sync Tag
343 --
344 utl_file.putf(l_filehandle, g_end_clob);
345
346 utl_file.new_line(l_filehandle, 1);
347
348 utl_file.fclose(l_filehandle);
349
350 exception
351 when others then
352 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'CREATECLOBFILE', p_Directory||
353 '*'||p_Filename,null);
354 raise;
355 end;
356 ------------------------------------------------------------------------------
357 /*
358 ** UploadFile - Called from SQL*Plus, uploads file into Event System
359 ** Parameters: Directory
360 ** Filename
361 **
362 */
363 procedure UPLOADFILE (
364 P_DIRECTORY in varchar2,
365 P_FILENAME in varchar2
366 ) is
367
368 l_filehandle UTL_FILE.FILE_TYPE;
369 l_workingclob clob;
370 l_clob clob;
371 l_buffer varchar2(32000);
372 l_clobsize integer;
373
374 begin
375
376 if (p_directory is null or p_filename is null) then
377 raise utl_file.invalid_path;
378 end if;
379
380 l_filehandle := UTL_FILE.FOPEN(p_Directory, p_Filename,'r');
381
382 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
383
384 LOOP
385 begin
386 dbms_lob.createtemporary( l_workingclob, FALSE, DBMS_LOB.CALL);
387
388 utl_file.get_line(l_filehandle, l_buffer);
389
390 if length(l_buffer) > 0 then
391 dbms_lob.write(l_workingclob, length(l_buffer), 1, l_buffer);
392 dbms_lob.append(l_clob,l_workingclob);
393 end if;
394
395 l_workingclob := null;
396 l_buffer := '';
397
398 exception
399 when no_data_found then
400 exit;
401 end;
402 END LOOP;
403
404 --
405 -- We have the Clob
406 --
407 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
408 wf_log_pkg.string(wf_log_pkg.level_statement,
409 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.UPLOADFILE.file_size',
410 'Clob Size is:'||l_clobsize);
411 wf_log_pkg.string(wf_log_pkg.level_statement,
412 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.UPLOADFILE.upload',
413 'About to Upload Clob');
414 end if;
415
416 wf_event_synchronize_pkg.uploadsyncclob( l_clob);
417
418 exception
419 when utl_file.invalid_path then
420 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'UPLOADFILE', p_Directory,
421 p_Filename,null);
422 wf_core.raise('WFE_INVALID_PATH');
423 when utl_file.invalid_mode then
424 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'UPLOADFILE', p_Directory,
425 p_Filename,null);
426 wf_core.raise('WFE_INVALID_MODE');
427 when utl_file.invalid_operation then
428 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'UPLOADFILE', p_Directory,
429 p_Filename,null);
430 wf_core.raise('WFE_INVALID_OPERATION');
431 when utl_file.read_error then
432 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'UPLOADFILE', p_Directory,
433 p_Filename,null);
434 wf_core.raise('WFE_READ_ERROR');
435 when utl_file.internal_error then
436 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'UPLOADFILE', p_Directory,
437 p_Filename,null);
438 wf_core.raise('WFE_INTERNAL_ERROR');
439 when utl_file.invalid_filehandle then
440 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'UPLOADFILE', p_Directory,
441 p_Filename,null);
442 wf_core.raise('WFE_INVALID_FILEHANDLE');
443 when others then
444 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'UPLOADFILE', p_Directory,
445 p_Filename,null);
446 raise;
447 end;
448 ------------------------------------------------------------------------------
449 /*
450 ** UploadSyncClob - Called by UploadFile, takes a CLOB, splices it, and
451 ** inserts objects into Event System
452 ** Parameters: EventData
453 **
454 */
455 procedure UPLOADSYNCCLOB (
456 P_EVENTDATA in clob
457 ) is
458
459 l_length_end_string integer := 16;
460 l_amount_to_read integer := 0;
461 l_counter integer := 0;
462 l_begin_position integer := 0;
463 l_end_position integer := 0;
464
465 l_messagedata varchar2(32000);
466 l_objecttype varchar2(100);
467 l_clobsize integer;
468 l_splice varchar2(4000);
469 l_error varchar2(4000);
470
471 begin
472 --
473 LOOP
474 --
475 -- We look through the CLOB for a each Object until there
476 -- are none
477 --
478 l_counter := l_counter + 1;
479
480 l_begin_position := dbms_lob.instr(p_eventdata, g_begin_string,
481 1, l_counter);
482
483 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
484 -- This is for logging only
485 -- BINDVAR_SCAN_IGNORE[3]
486 wf_log_pkg.string(wf_log_pkg.level_statement,
487 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.UPLOADSYNCCLOB.pos',
488 'Begin Pos '||l_begin_position);
489 end if;
490
491 EXIT when l_begin_position = 0; -- No More Event Objects left
492
493 l_end_position := dbms_lob.instr(p_eventdata, g_end_string,
494 1, l_counter);
495
496 --
497 -- Figure out the amount to read out of the CLOB, and subst
498 --
499 l_end_position := l_end_position + l_length_end_string;
500
501 l_amount_to_read := l_end_position - l_begin_position;
502
503 l_messagedata := dbms_lob.substr(p_eventdata, l_amount_to_read,
504 l_begin_position);
505
506 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
507 wf_log_pkg.string(wf_log_pkg.level_statement,
508 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.UPLOADSYNCCLOB.data',
509 'Message Data: '||substr(l_messagedata,1,100));
510 end if;
511
512 --
513 -- Get Object Type, and then call to UploadObject
514 --
515 l_objecttype := wf_event_synchronize_pkg.GetObjectType(l_messagedata);
516
517 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
518 wf_log_pkg.string(wf_log_pkg.level_statement,
519 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.UPLOADSYNCCLOB.object',
520 'Object Type: '||l_objecttype);
521 end if;
522
523 wf_event_synchronize_pkg.UploadObject(l_objecttype, l_messagedata,l_error);
524
525 END LOOP;
526
527 exception
528 when others then
529 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'UPLOADSYNCCLOB', l_counter,
530 substr(l_messagedata,1,100)
531 ,null);
532 raise;
533 end;
534 ------------------------------------------------------------------------------
535 /*
536 ** GetSystems - Get's all systems that match the key, returns CLOB
537 **
538 */
539 function GETSYSTEMS (
540 P_KEY in varchar2
541 ) return clob is
542
543 l_clob clob;
544 returnclob clob;
545
546 cursor systems is
547 select guid from wf_systems
548 where name like nvl(p_key,'%');
549
550 begin
551
552 dbms_lob.createtemporary(returnclob, FALSE, DBMS_LOB.CALL);
553
554 FOR g IN systems LOOP
555
556 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
557
558 -- Get our XML document
559 l_clob := wf_event_functions_pkg.generate('oracle.apps.wf.event.system.update', g.guid);
560
561 -- Add this to our return CLOB
562 dbms_lob.append(returnclob, l_clob);
563
564 -- Kill the Loop CLOB
565 l_clob := null;
566
567 END LOOP;
568
569 return (returnclob);
570
571 exception
572 when others then
573 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'GetSystems', p_key,null,
574 'ERROR'); raise;
575 end;
576 ------------------------------------------------------------------------------
577 /*
578 ** GetAgents - Get's all systems that match the key, returns CLOB
579 **
580 */
581 function GETAGENTS (
582 P_KEY in varchar2,
583 P_ISEXACT in boolean
584 ) return clob is
585
586 l_clob clob;
587 l_clob_len integer := 0;
588 returnclob clob;
589 l_tmpStr varchar2(32000);
590 l_tmpStrLen integer := 0;
591 l_guid raw(16);
592 l_searchPos number default 1;
593
594 cursor agents(xguid raw) is
595 select guid from wf_agents
596 where name like nvl(p_key,'%')
597 and (xguid is null or system_guid=xguid);
598
599 begin
600 -- Download local agents only when ObjectType is SYSTEMS, AGENTS, or EVENT
601 IF (upper(g_ObjectType) = 'SYSTEMS' OR
602 upper(g_ObjectType) = 'AGENTS' OR
603 -- upper(g_ObjectType) = 'AGENTGROUPS' OR
604 -- upper(g_ObjectType) = 'EVENT' OR
605 -- upper(g_ObjectType) = 'SUBSCRIPTIONS' OR
606 -- upper(g_ObjectType) = 'GROUPS' OR
607 upper(g_ObjectType) = 'EVENTS') THEN
608 l_guid := hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
609 ELSE
610 l_guid := hextoraw(null);
611 END IF;
612
613 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
614 wf_log_pkg.string(wf_log_pkg.level_procedure,
615 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.GETAGENTS.Begin',
616 'Entered GetAgents');
617 end if;
618
619 dbms_lob.createtemporary(returnclob, FALSE, DBMS_LOB.CALL);
620
621 FOR g IN agents(l_guid) LOOP
622
623 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
624
625 -- Get our XML document
626 l_clob := wf_event_functions_pkg.generate('oracle.apps.wf.event.agent.update', g.guid);
627
628 if (p_isexact = false) then
629 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
630 wf_log_pkg.string(wf_log_pkg.level_statement,
631 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.GETAGENTS.set_pound',
632 'Substitute with pounds.');
633 end if;
634
635 l_clob_len := dbms_lob.getlength(l_clob);
636 dbms_lob.read(l_clob, l_clob_len, 1, l_tmpStr);
637
638 -- # replacement in <GUID>, <SYSTEM_GUID>, and <ADDRESS> field
639 l_tmpStr := SetPound(1,l_tmpStr,'<GUID>','</GUID>','NEW',null);
640 l_tmpStr := SetPound(1,l_tmpStr,'<SYSTEM_GUID>','</SYSTEM_GUID>','LOCAL',null);
641 l_tmpStr := SetPound(1,l_tmpStr,'<ADDRESS>','.','OWNER',null);
642 l_searchPos := instr(l_tmpStr, '<ADDRESS>');
643 l_tmpStr := SetPound(l_searchPos,l_tmpStr,'@','</ADDRESS>','SID',null);
644 l_tmpStr := SetPound(1,l_tmpStr,'<QUEUE_NAME>','.','OWNER',null);
645 l_tmpStrLen := length(l_tmpStr);
646
647 dbms_lob.erase(l_clob, l_clob_len, 1);
648 dbms_lob.write(l_clob, l_tmpStrLen, 1, l_tmpStr);
649 end if;
650
651 -- Add this to our return CLOB
652 dbms_lob.append(returnclob, l_clob);
653
654 -- Kill the Loop CLOB
655 l_clob := null;
656
657 END LOOP;
658
659 return (returnclob);
660
661
662 exception
663 when others then
664 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'GetAgents', p_key,null,
665 'ERROR'); raise;
666 end;
667 ------------------------------------------------------------------------------
668 /*
669 ** GetAgentGroups - Get's all agent groups that match the key, returns CLOB
670 **
671 */
672 function GETAGENTGROUPS (
673 P_KEY in varchar2
674 ) return clob is
675
676 l_clob clob;
677 returnclob clob;
678
679 cursor agent_groups is
680 select g.name||'/'||a.name GUID
681 from wf_agents g, wf_agents a,wf_agent_groups ag
682 where g.guid=ag.group_guid
683 and a.guid=ag.member_guid
684 and (p_key is null or a.name like p_key);
685
686 /*select wag.group_guid||'/'||wag.member_guid GUID from wf_agent_groups wag
687 where exists
688 ( select 'x'
689 from wf_agents
690 where guid = wag.member_guid
691 and name like nvl(p_key,'%')
692 );*/
693
694 begin
695
696 dbms_lob.createtemporary(returnclob, FALSE, DBMS_LOB.CALL);
697
698 FOR g IN agent_groups LOOP
699
700 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
701
702 -- Get our XML document
703 l_clob := wf_event_functions_pkg.generate('oracle.apps.wf.event.agentgroup.update', g.guid);
704
705 -- Add this to our return CLOB
706 dbms_lob.append(returnclob, l_clob);
707
708 -- Kill the Loop CLOB
709 l_clob := null;
710
711 END LOOP;
712
713 return (returnclob);
714
715 exception
716 when others then
717 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'GetAgentGroups', p_key,null,
718 'ERROR'); raise;
719 end;
720
721 ------------------------------------------------------------------------------
722 /*
723 ** GetEvents - Get's all events that match the key, returns CLOB
724 **
725 */
726
727 function GETEVENTS (
728 P_KEY in varchar2,
729 P_OWNERTAG in varchar2
730 ) return clob is
731
732 l_clob clob;
733 returnclob clob;
734
735 -- we want to get only EVENT type objects here
736 cursor events is
737 select guid
738 from wf_events
739 where type = 'EVENT'
740 and name like p_key
741 and owner_tag like nvl(p_ownertag, '%');
742
743 cursor events_all is
744 select guid
745 from wf_events
746 where type = 'EVENT'
747 and owner_tag like nvl(p_ownertag, '%');
748
749 begin
750
751 dbms_lob.createtemporary(returnclob, FALSE, DBMS_LOB.CALL);
752 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
753
754 if (p_key is not null OR p_key <> '') then
755
756 FOR g IN events LOOP
757 -- Get our XML document
758 l_clob := wf_event_functions_pkg.generate('oracle.apps.wf.event.event.update', g.guid);
759 -- Add this to our return CLOB
760 dbms_lob.append(returnclob, l_clob);
761 -- Kill the Loop CLOB
762 dbms_lob.trim(l_clob, 0);
763 END LOOP;
764
765 else
766
767 FOR g IN events_all LOOP
768 -- Get our XML document
769 l_clob := wf_event_functions_pkg.generate('oracle.apps.wf.event.event.update', g.guid);
770 -- Add this to our return CLOB
771 dbms_lob.append(returnclob, l_clob);
772 -- Kill the Loop CLOB
773 dbms_lob.trim(l_clob, 0);
774 END LOOP;
775
776 end if;
777
778 return (returnclob);
779
780 exception
781 when others then
782 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'GetEvents', p_key,null,
783 'ERROR'); raise;
784 end;
785 ------------------------------------------------------------------------------
786 /*
787 ** GetEventGroups - Get's all events that match the key, returns CLOB
788 **
789 */
790 function GETEVENTGROUPS (
791 P_KEY in varchar2,
792 P_OWNERTAG in varchar2
793 ) return clob is
794
795 l_clob clob;
796 returnclob clob;
797
798 -- Download all groups to which the given event or key belongs to
799 cursor event_groups is
800 select g.name||'/'||e.name names
801 from wf_events g, wf_events e, wf_event_groups eg
802 where g.guid = eg.group_guid
803 and g.type = 'GROUP'
804 and e.guid = eg.member_guid
805 and e.name like p_key
806 and e.owner_tag like nvl(p_ownertag, '%')
807 order by e.name;
808
809 cursor event_groups_all is
810 select g.name||'/'||e.name names
811 from wf_events g, wf_events e, wf_event_groups eg
812 where g.guid = eg.group_guid
813 and g.type = 'GROUP'
814 and e.guid = eg.member_guid
815 and e.owner_tag like nvl(p_ownertag, '%')
816 order by e.name;
817
818 begin
819
820 dbms_lob.createtemporary(returnclob, FALSE, DBMS_LOB.CALL);
821 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
822
823 if (p_key is not null OR p_key <> '') then
824
825 FOR g IN event_groups LOOP
826 -- Get our XML document
827 l_clob := wf_event_functions_pkg.generate('oracle.apps.wf.event.group.update', g.names);
828 -- Add this to our return CLOB
829 dbms_lob.append(returnclob, l_clob);
830 -- Kill the Loop CLOB
831 dbms_lob.trim(l_clob, 0);
832 END LOOP;
833
834 else
835
836 FOR g IN event_groups_all LOOP
837 -- Get our XML document
838 l_clob := wf_event_functions_pkg.generate('oracle.apps.wf.event.group.update', g.names);
839 -- Add this to our return CLOB
840 dbms_lob.append(returnclob, l_clob);
841 -- Kill the Loop CLOB
842 dbms_lob.trim(l_clob, 0);
843 END LOOP;
844
845 end if;
846
847 return (returnclob);
848
849 exception
850 when others then
851 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'GetEventGroups', p_key,null,
852 'ERROR'); raise;
853 end;
854 ------------------------------------------------------------------------------
855 /*
856 ** GetGroups - Get's all events of type GROUP that match the key, returns CLOB
857 **
858 */
859
860 function GETGROUPS (
861 P_KEY in varchar2,
862 P_OWNERTAG in varchar2
863 ) return clob is
864
865 l_clob clob;
866 returnclob clob;
867
868 cursor events is
869 select guid
870 from wf_events
871 where type = 'GROUP'
872 and name like p_key
873 and owner_tag like nvl(p_ownertag, '%');
874
875 cursor events_all is
876 select guid
877 from wf_events
878 where type = 'GROUP'
879 and owner_tag like nvl(p_ownertag, '%');
880
881 begin
882
883 dbms_lob.createtemporary(returnclob, FALSE, DBMS_LOB.CALL);
884 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
885
886 if (p_key is not null OR p_key <> '') then
887
888 FOR g IN events LOOP
889 -- Get our XML document
890 l_clob := wf_event_functions_pkg.generate('oracle.apps.wf.event.event.update', g.guid);
891 -- Add this to our return CLOB
892 dbms_lob.append(returnclob, l_clob);
893 -- Kill the Loop CLOB
894 dbms_lob.trim(l_clob, 0);
895 END LOOP;
896
897 else
898
899 FOR g IN events_all LOOP
900 -- Get our XML document
901 l_clob := wf_event_functions_pkg.generate('oracle.apps.wf.event.event.update', g.guid);
902 -- Add this to our return CLOB
903 dbms_lob.append(returnclob, l_clob);
904 -- Kill the Loop CLOB
905 dbms_lob.trim(l_clob, 0);
906 END LOOP;
907
908 end if;
909
910 return (returnclob);
911
912 exception
913 when others then
914 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'GetGroups', p_key,null,
915 'ERROR'); raise;
916 end;
917 ------------------------------------------------------------------------------
918 function GETEVENTGROUPBYGROUP (
919 P_KEY in varchar2,
920 P_OWNERTAG in varchar2
921 ) return clob is
922
923 l_clob clob;
924 returnclob clob;
925
926 cursor event_groups is
927 select g.name||'/'||e.name names
928 from wf_events g, wf_events e, wf_event_groups eg
929 where g.guid = eg.group_guid
930 and g.type = 'GROUP'
931 and e.guid = eg.member_guid
932 and (p_key is null or g.name like p_key )
933 and (p_ownertag is null or g.owner_tag like p_ownertag)
934 order by g.name;
935
936 begin
937
938 dbms_lob.createtemporary(returnclob, FALSE, DBMS_LOB.CALL);
939
940 FOR g IN event_groups LOOP
941
942 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
943
944 -- Get our XML document
945 l_clob := wf_event_functions_pkg.generate('oracle.apps.wf.event.group.update', g.names);
946
947 -- Add this to our return CLOB
948 dbms_lob.append(returnclob, l_clob);
949
950 -- Kill the Loop CLOB
951 l_clob := null;
952
953 END LOOP;
954
955 return (returnclob);
956
957 exception
958 when others then
959 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'GetEventGroupByGroup', p_key,null,
960 'ERROR'); raise;
961 end;
962
963 ------------------------------------------------------------------------------
964 /*
965 ** GetSubscriptions - Get's all subscriptions that match the key, returns CLOB
966 **
967 */
968 function GETSUBSCRIPTIONS (
969 P_KEY in varchar2,
970 P_ISEXACT in boolean,
971 P_OWNERTAG in varchar2
972 ) return clob is
973
974 l_clob clob;
975 l_clob_len integer := 0;
976 returnclob clob;
977 l_tmpStr varchar2(32000);
978 l_tmpStrLen integer := 0;
979 l_guid raw(16);
980 strGuid varchar2(100) default null;
981
982 cursor event_subscriptions(xguid raw) is
983 select distinct(wes.guid) GUID from wf_event_subscriptions wes
984 where owner_tag like NVL(p_ownertag, '%')
985 and exists
986 ( select 'x'
987 from wf_events
988 where guid = wes.event_filter_guid
989 and name like nvl(p_key,'%')
990 and (xguid is null or system_guid=xguid)
991 );
992
993 cursor agents(wfagt varchar2) is
994 select guid from wf_agents
995 where name = wfagt;
996
997 begin
998 -- Download local event subscriptions only when ObjectType is SYSTEMS, AGENTS, or EVENT
999 IF (upper(g_ObjectType) = 'SYSTEMS' OR
1000 upper(g_ObjectType) = 'AGENTS' OR
1001 -- upper(g_ObjectType) = 'EVENT' OR
1002 -- upper(g_ObjectType) = 'SUBSCRIPTIONS' OR
1003 -- upper(g_ObjectType) = 'GROUPS' OR
1004 upper(g_ObjectType) = 'EVENTS') THEN
1005 l_guid := hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
1006 ELSE
1007 l_guid := hextoraw(null);
1008 END IF;
1009
1010 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1011 wf_log_pkg.string(wf_log_pkg.level_procedure,
1012 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.GETSUBSCRIPTIONS.Begin',
1013 'Entered GetSubscriptions');
1014 end if;
1015
1016 dbms_lob.createtemporary(returnclob, FALSE, DBMS_LOB.CALL);
1017
1018 FOR g IN event_subscriptions(l_guid) LOOP
1019
1020 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
1021
1022 -- Get our XML document
1023 l_clob := wf_event_functions_pkg.generate('oracle.apps.wf.event.subscription.update', g.guid);
1024
1025 if (p_isexact = false) then
1026 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1027 wf_log_pkg.string(wf_log_pkg.level_statement,
1028 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.GETSUBSCRIPTIONS.set_pound',
1029 'Substitute with pounds.');
1030 end if;
1031
1032 l_clob_len := dbms_lob.getlength(l_clob);
1033 dbms_lob.read(l_clob, l_clob_len, 1, l_tmpStr);
1034
1035 -- # replacement in <GUID> and <SYSTEM_GUID> field
1036 l_tmpStr := SetPound(1,l_tmpStr,'<GUID>','</GUID>','NEW',null);
1037 l_tmpStr := SetPound(1,l_tmpStr,'<SYSTEM_GUID>','</SYSTEM_GUID>','LOCAL',null);
1038
1039 -- set <OUT_AGENT_GUID/> and <TO_AGENT_GUID/>
1040 -- l_tmpStr := SetNull(1,l_tmpStr,'OUT_AGENT_GUID');
1041 -- l_tmpStr := SetNull(1,l_tmpStr,'TO_AGENT_GUID');
1042
1043 l_tmpStr := getAgent('<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>',l_tmpStr);
1044 l_tmpStr := getAgent('<OUT_AGENT_GUID>','</OUT_AGENT_GUID>',l_tmpStr);
1045 l_tmpStr := getAgent('<TO_AGENT_GUID>','</TO_AGENT_GUID>',l_tmpStr);
1046
1047 /**
1048 Bug 3191978
1049 The above code will genericall replace all agent_guids with
1050 their corresponding agent name
1051
1052 FOR a IN agents('WF_IN') LOOP
1053 strGuid := a.guid; -- rawtohex(a.guid);
1054 l_tmpStr := SetPound(1,l_tmpStr,'<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','WF_IN',strGuid);
1055 l_tmpStr := SetPound(1,l_tmpStr,'<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','WF_IN',strGuid);
1056 l_tmpStr := SetPound(1,l_tmpStr,'<TO_AGENT_GUID>','</TO_AGENT_GUID>','WF_IN',strGuid);
1057 END LOOP;
1058
1059 FOR b IN agents('WF_OUT') LOOP
1060 strGuid := b.guid; -- rawtohex(b.guid);
1061 l_tmpStr := SetPound(1,l_tmpStr,'<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','WF_OUT',strGuid);
1062 l_tmpStr := SetPound(1,l_tmpStr,'<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','WF_OUT',strGuid);
1063 l_tmpStr := SetPound(1,l_tmpStr,'<TO_AGENT_GUID>','</TO_AGENT_GUID>','WF_OUT',strGuid);
1064 END LOOP;
1065
1066 FOR c IN agents('WF_ERROR') LOOP
1067 strGuid := c.guid; -- rawtohex(c.guid);
1068 l_tmpStr := SetPound(1,l_tmpStr,'<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','WF_ERROR',strGuid);
1069 l_tmpStr := SetPound(1,l_tmpStr,'<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','WF_ERROR',strGuid);
1070 l_tmpStr := SetPound(1,l_tmpStr,'<TO_AGENT_GUID>','</TO_AGENT_GUID>','WF_ERROR',strGuid);
1071 END LOOP;
1072
1073 FOR c IN agents('WF_REPLAY_OUT') LOOP
1074 strGuid := c.guid; -- rawtohex(c.guid);
1075 l_tmpStr := SetPound(1,l_tmpStr,'<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','WF_REPLAY_OUT',strGuid);
1076 l_tmpStr := SetPound(1,l_tmpStr,'<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','WF_REPLAY_OUT',strGuid);
1077 l_tmpStr := SetPound(1,l_tmpStr,'<TO_AGENT_GUID>','</TO_AGENT_GUID>','WF_REPLAY_OUT',strGuid);
1078 END LOOP;
1079
1080 FOR c IN agents('WF_CONTROL') LOOP
1081 strGuid := c.guid; -- rawtohex(c.guid);
1082 l_tmpStr := SetPound(1,l_tmpStr,'<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','WF_CONTROL',strGuid);
1083 l_tmpStr := SetPound(1,l_tmpStr,'<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','WF_CONTROL',strGuid);
1084 l_tmpStr := SetPound(1,l_tmpStr,'<TO_AGENT_GUID>','</TO_AGENT_GUID>','WF_CONTROL',strGuid);
1085 END LOOP;
1086
1087 FOR c IN agents('WF_JMS_IN') LOOP
1088 strGuid := c.guid; -- rawtohex(c.guid);
1089 l_tmpStr := SetPound(1,l_tmpStr,'<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','WF_JMS_IN',strGuid);
1090 l_tmpStr := SetPound(1,l_tmpStr,'<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','WF_JMS_IN',strGuid);
1091 l_tmpStr := SetPound(1,l_tmpStr,'<TO_AGENT_GUID>','</TO_AGENT_GUID>','WF_JMS_IN',strGuid);
1092 END LOOP;
1093
1094 FOR c IN agents('WF_JMS_OUT') LOOP
1095 strGuid := c.guid; -- rawtohex(c.guid);
1096 l_tmpStr := SetPound(1,l_tmpStr,'<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','WF_JMS_OUT',strGuid);
1097 l_tmpStr := SetPound(1,l_tmpStr,'<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','WF_JMS_OUT',strGuid);
1098 l_tmpStr := SetPound(1,l_tmpStr,'<TO_AGENT_GUID>','</TO_AGENT_GUID>','WF_JMS_OUT',strGuid);
1099 END LOOP;
1100
1101 FOR c IN agents('WF_NOTIFICATION_IN') LOOP
1102 strGuid := c.guid; -- rawtohex(c.guid);
1103 l_tmpStr := SetPound(1,l_tmpStr,'<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','WF_NOTIFICATION_IN',strGuid);
1104 l_tmpStr := SetPound(1,l_tmpStr,'<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','WF_NOTIFICATION_IN',strGuid);
1105 l_tmpStr := SetPound(1,l_tmpStr,'<TO_AGENT_GUID>','</TO_AGENT_GUID>','WF_NOTIFICATION_IN',strGuid);
1106 END LOOP;
1107
1108 FOR c IN agents('WF_NOTIFICATION_OUT') LOOP
1109 strGuid := c.guid; -- rawtohex(c.guid);
1110 l_tmpStr := SetPound(1,l_tmpStr,'<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','WF_NOTIFICATION_OUT',strGuid);
1111 l_tmpStr := SetPound(1,l_tmpStr,'<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','WF_NOTIFICATION_OUT',strGuid);
1112 l_tmpStr := SetPound(1,l_tmpStr,'<TO_AGENT_GUID>','</TO_AGENT_GUID>','WF_NOTIFICATION_OUT',strGuid);
1113 END LOOP;
1114 **/
1115 l_tmpStrLen := length(l_tmpStr);
1116
1117 dbms_lob.erase(l_clob, l_clob_len, 1);
1118 dbms_lob.write(l_clob, l_tmpStrLen, 1, l_tmpStr);
1119 end if;
1120
1121 -- Add this to our return CLOB
1122 dbms_lob.append(returnclob, l_clob);
1123
1124 -- Kill the Loop CLOB
1125 l_clob := null;
1126
1127 END LOOP;
1128
1129 return (returnclob);
1130
1131 exception
1132 when others then
1133 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'GetSubscriptions', p_key,null,
1134 'ERROR'); raise;
1135 end;
1136 ------------------------------------------------------------------------------
1137 /*
1138 ** GetObjectType - Receives a string and determines what event object type
1139 ** it is.
1140 **
1141 */
1142 function GETOBJECTTYPE(
1143 P_MESSAGEDATA in varchar2
1144 ) return varchar2 is
1145
1146 l_return varchar2(100);
1147
1148 begin
1149
1150 IF instr(p_messagedata, g_system, 1, 1) > 0 THEN
1151 l_return := g_system;
1152 ELSIF instr(p_messagedata, g_agent, 1, 1) > 0 THEN
1153 l_return := g_agent;
1154 ELSIF instr(p_messagedata, g_agent_group, 1, 1) > 0 THEN
1155 l_return := g_agent_group;
1156 ELSIF instr(p_messagedata, g_event, 1, 1) > 0 THEN
1157 l_return := g_event;
1158 ELSIF instr(p_messagedata, g_event_group, 1, 1) > 0 THEN
1159 l_return := g_event_group;
1160 ELSIF instr(p_messagedata, g_event_sub, 1, 1) > 0 THEN
1161 l_return := g_event_sub;
1162 END IF;
1163
1164 return (l_return);
1165
1166 exception
1167 when others then
1168 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'GetObjectType',
1169 substr(p_messagedata,1,100),null,
1170 'ERROR'); raise;
1171 end;
1172 ------------------------------------------------------------------------------
1173 /*
1174 ** UploadObject - Receives a string and calls appropriate table handler
1175 **
1176 **
1177 */
1178 procedure UploadObject(
1179 P_OBJECTTYPE in varchar2,
1180 P_MESSAGEDATA in varchar2,
1181 P_ERROR out nocopy varchar2
1182 ) is
1183
1184 begin
1185
1186 IF p_objecttype = g_system THEN
1187 wf_systems_pkg.receive(p_messagedata);
1188 ELSIF p_objecttype = g_agent THEN
1189 wf_agents_pkg.receive(p_messagedata);
1190 ELSIF p_objecttype = g_agent_group THEN
1191 wf_agent_groups_pkg.receive(p_messagedata);
1192 ELSIF p_objecttype = g_event THEN
1193 wf_events_pkg.receive(p_messagedata);
1194 ELSIF p_objecttype = g_event_group THEN
1195 wf_event_groups_pkg.receive2(p_messagedata,p_error);
1196 ELSIF p_objecttype = g_event_sub THEN
1197 wf_event_subscriptions_pkg.receive(p_messagedata);
1198 END IF;
1199
1200 /*exception
1201 when others then
1202 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'UploadObject',
1203 substr(p_messagedata,1,100),
1204 p_objecttype,
1205 'ERROR'); raise;*/
1206 end;
1207 ------------------------------------------------------------------------------
1208 /*
1209 ** UpdateGUID- Update GUID in WF_RESOURCES table
1210 ** returns varchar2
1211 ** Parameters: <can be null>
1212 */
1213 procedure UpdateGUID (
1214 g_guid in varchar2
1215 ) is
1216 ret number default 0; -- 0 means value didn't get update
1217 l_guid raw(16) default null;
1218 l_count number;
1219 begin
1220 if g_guid is not null then
1221 select count(*)
1222 into l_count
1223 from WF_SYSTEMS;
1224 if (l_count = 0) then
1225 update WF_RESOURCES
1226 set text=g_guid
1227 where name='WF_SYSTEM_GUID';
1228 end if;
1229 end if;
1230 exception
1231 when others then
1232 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'UpdateGUID');
1233 raise;
1234 end;
1235 ------------------------------------------------------------------------------
1236 /*
1237 ** ReplaceContent - Replace contant of a given tag, returns varchar2
1238 */
1239 function ReplaceContent (
1240 begTag in varchar2,
1241 endTag in varchar2,
1242 replaceTarget in varchar2,
1243 newData in varchar2,
1244 dataStr in varchar2
1245 ) return varchar2 is
1246 retStr varchar2(32000) default null;
1247 beg_pos number default 1;
1248 end_pos number default 1;
1249 l_pos number default 1;
1250 l_amount_to_read number default 0;
1251 l_str varchar2(32000) default null;
1252 l_str_new varchar2(32000) default null;
1253
1254 begin
1255 if dataStr is not null then
1256 retStr := dataStr;
1257 beg_pos := instr(dataStr, begTag);
1258 end_pos := instr(dataStr, endTag);
1259 l_amount_to_read := end_pos - beg_pos;
1260 if ((beg_pos <> 0) and
1261 (end_pos <> 0) and
1262 (l_amount_to_read > 0)) then
1263 l_str := substr(dataStr,beg_pos,l_amount_to_read);
1264 l_pos := instr(l_str, replaceTarget);
1265 if (l_pos > 1) then
1266 l_str_new := replace(l_str,replaceTarget,newData);
1267 retStr := replace(retStr,l_str,l_str_new);
1268 end if;
1269 end if;
1270 else
1271 retStr := dataStr;
1272 end if;
1273 return (retStr);
1274 exception
1275 when others then
1276 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'ReplaceContent');
1277 raise;
1278 end;
1279 ------------------------------------------------------------------------------
1280 /*
1281 ** SetGUID - Generate new GUID when encounter #NEW tag, returns varchar2
1282 ** Parameters: dataStr <can be null>
1283 */
1284 function SetGUID (
1285 dataStr in varchar2
1286 ) return varchar2 is
1287
1288 g_guid varchar2(100) := '<GUID>#NEW</GUID>';
1289 g_guid2 varchar2(100) := '<MEMBER_GUID>#NEW</MEMBER_GUID>';
1290 g_beg_system varchar2(100) := '<WF_SYSTEMS>';
1291 g_end_system varchar2(100) := '</WF_SYSTEMS>';
1292 retStr varchar2(32000) default null;
1293 l_guid raw(16) default null;
1294
1295 begin
1296 if dataStr is not null then
1297 l_guid := sys_guid();
1298 retStr := ReplaceContent(g_beg_system,g_end_system,g_guid,'<GUID>'||l_guid||'</GUID>',dataStr);
1299
1300 if (retStr <> dataStr) then
1301 UpdateGUID(l_guid);
1302 end if;
1303 -- check the rest, including Agents
1304 retStr := replace(retStr,g_guid,'<GUID>'||l_guid||'</GUID>');
1305 retStr := replace(retStr,g_guid2,'<MEMBER_GUID>'||l_guid||'</MEMBER_GUID>');
1306 else
1307 retStr := dataStr;
1308 end if;
1309 return (retStr);
1310 exception
1311 when others then
1312 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'SetGUID');
1313 raise;
1314 end;
1315 ------------------------------------------------------------------------------
1316 /*
1317 ** SetSYSTEMGUID - Set SYSTEM_GUID when encounter #LOCAL tag,
1318 ** returns varchar2
1319 ** Parameters: dataStr <can be null>
1320 */
1321 function SetSYSTEMGUID (
1322 dataStr in varchar2
1323 ) return varchar2 is
1324
1325 g_sys_guid varchar2(100) := '<SYSTEM_GUID>#LOCAL</SYSTEM_GUID>';
1326 retStr varchar2(32000) default null;
1327 -- beg_pos number default 0;
1328 l_sys_guid raw(16);
1329
1330 begin
1331 if dataStr is not null then
1332 -- beg_pos := instr(dataStr, g_guid);
1333 l_sys_guid := hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
1334 retStr := replace(dataStr,g_sys_guid,'<SYSTEM_GUID>'||l_sys_guid||'</SYSTEM_GUID>');
1335 else
1336 retStr := dataStr;
1337 end if;
1338 return (retStr);
1339 exception
1340 when others then
1341 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'SetSYSTEMGUID');
1342 raise;
1343 end;
1344 ------------------------------------------------------------------------------
1345 /*
1346 ** GetSID - Get SID from database, returns varchar2
1347 */
1348 function GetSID return varchar2 is
1349
1350 l_sid varchar2(1000);
1351
1352 begin
1353 -- get database sid
1354 begin
1355 /* We will get this from GLOBAL_NAME table instead of v$parameter
1356 select value
1357 into l_sid
1358 from v$parameter
1359 where name='db_name';
1360 */
1361 select global_name
1362 into l_sid from global_name;
1363 exception
1364 when no_data_found then
1365 l_sid := 'EVENTSYSTEM';
1366 end;
1367
1368 return upper(substr(l_sid,1,30));
1369
1370 exception
1371 when others then
1372 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'GetSID');
1373 raise;
1374 end;
1375 ------------------------------------------------------------------------------
1376 /*
1377 ** GetQOwner - Get Queue Owner from database, returns varchar2
1378 */
1379 function GetQOwner return varchar2 is
1380
1381 -- l_owner varchar2(1000);
1382
1383 begin
1384 -- get queue owner
1385 begin
1386 /*
1387 select owner
1388 into l_owner
1389 from all_queues
1390 where name='WF_IN';
1391 */
1392 --don't do this costly query substr it
1393 --off from wf_agents or since WF_IN is seeded
1394 --it should always be the schema
1395 /* Bug3628261 - if no data found here NOSUCHTHING was returned
1396 instead we will just cache the WF_SCHEMA.
1397 select substr(queue_name,1,instr(queue_name,'.')-1)
1398 into l_owner
1399 from wf_agents
1400 where name = 'WF_IN';
1401 */
1402 if (g_qowner is null) then
1403 g_qowner := upper(wf_core.translate('WF_SCHEMA'));
1404 end if;
1405
1406 end;
1407
1408 return (g_qowner);
1409 exception
1410 when others then
1411 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'GetQOwner');
1412 raise;
1413 end;
1414 ------------------------------------------------------------------------------
1415 /*
1416 ** SetSID - Set SID when encounter #SID tag,
1417 ** returns varchar2
1418 ** Parameters: dataStr <can be null>
1419 */
1420 function SetSID (
1421 dataStr in varchar2
1422 ) return varchar2 is
1423 g_sid_name varchar2(100) := '<NAME>#SID</NAME>';
1424 g_sid_disp varchar2(100) := '<DISPLAY_NAME>#SID</DISPLAY_NAME>';
1425 g_sid_desc varchar2(100) := '<DESCRIPTION>#SID</DESCRIPTION>';
1426 g_beg_addr varchar2(100) := '<ADDRESS>';
1427 g_end_addr varchar2(100) := '</ADDRESS>';
1428 g_beg_qname varchar2(100) := '<QUEUE_NAME>';
1429 g_end_qname varchar2(100) := '</QUEUE_NAME>';
1430 tmpStr varchar2(32000) default null;
1431 retStr varchar2(32000) default null;
1432 l_sid varchar2(1000) default null;
1433 l_owner varchar2(1000) default null;
1434
1435
1436 begin
1437 if dataStr is not null then
1438 l_sid := GetSID();
1439 l_owner := GetQOwner();
1440 if l_sid is not null then
1441 tmpStr := dataStr;
1442 tmpStr := replace(tmpStr,g_sid_name,'<NAME>'||l_sid||'</NAME>');
1443 tmpStr := replace(tmpStr,g_sid_disp,'<DISPLAY_NAME>'||l_sid||'</DISPLAY_NAME>');
1444 tmpStr := replace(tmpStr,g_sid_desc,'<DESCRIPTION>'||l_sid||'</DESCRIPTION>');
1445 tmpStr := ReplaceContent(g_beg_addr,g_end_addr,'#SID',l_sid,tmpStr);
1446 tmpStr := ReplaceContent(g_beg_addr,g_end_addr,'#OWNER',l_owner,tmpStr);
1447 -- tmpStr := ReplaceContent(g_beg_qname,g_end_qname,'#SID',l_sid,tmpStr);
1448 tmpStr := ReplaceContent(g_beg_qname,g_end_qname,'#OWNER',l_owner,tmpStr);
1449 retStr := tmpStr;
1450 else
1451 retStr := dataStr;
1452 end if;
1453 end if;
1454
1455 return (retStr);
1456
1457 exception
1458 when others then
1459 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'SetSID');
1460 raise;
1461 end;
1462 ------------------------------------------------------------------------------
1463 /*
1464 ** SetAgent - Set Agent SID when encounter #WF_IN, #WF_OUT, #WF_ERROR tag,
1465 ** returns varchar2
1466 ** Parameters: dataStr <can be null>
1467 */
1468 function SetAgent (
1469 dataStr in varchar2
1470 ) return varchar2 is
1471 tmpStr varchar2(32000) default null;
1472 retStr varchar2(32000) default null;
1473 l_wfin varchar2(1000) default null;
1474 l_wfout varchar2(1000) default null;
1475 l_wferror varchar2(1000) default null;
1476 strGuid varchar2(100) default null;
1477
1478 cursor agent(str varchar2) is
1479 select guid from wf_agents
1480 where name=str
1481 and system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
1482
1483 begin
1484 if dataStr is not null then
1485 tmpStr := dataStr;
1486 FOR a IN agent('WF_IN') LOOP
1487 strGuid := a.guid;
1488 tmpStr := ReplaceContent('<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','#WF_IN',strGuid,tmpStr);
1489 tmpStr := ReplaceContent('<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','#WF_IN',strGuid,tmpStr);
1490 tmpStr := ReplaceContent('<TO_AGENT_GUID>','</TO_AGENT_GUID>','#WF_IN',strGuid,tmpStr);
1491 END LOOP;
1492 FOR b IN agent('WF_OUT') LOOP
1493 strGuid := b.guid; -- rawtohex(b.guid);
1494 tmpStr := ReplaceContent('<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','#WF_OUT',strGuid,tmpStr);
1495 tmpStr := ReplaceContent('<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','#WF_OUT',strGuid,tmpStr);
1496 tmpStr := ReplaceContent('<TO_AGENT_GUID>','</TO_AGENT_GUID>','#WF_OUT',strGuid,tmpStr);
1497 END LOOP;
1498 FOR c IN agent('WF_ERROR') LOOP
1499 strGuid := c.guid; -- rawtohex(c.guid);
1500 tmpStr := ReplaceContent('<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','#WF_ERROR',strGuid,tmpStr);
1501 tmpStr := ReplaceContent('<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','#WF_ERROR',strGuid,tmpStr);
1502 tmpStr := ReplaceContent('<TO_AGENT_GUID>','</TO_AGENT_GUID>','#WF_ERROR',strGuid,tmpStr);
1503 END LOOP;
1504 FOR c IN agent('WF_REPLAY_OUT') LOOP
1505 strGuid := c.guid; -- rawtohex(c.guid);
1506 tmpStr := ReplaceContent('<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','#WF_REPLAY_OUT',strGuid,tmpStr);
1507 tmpStr := ReplaceContent('<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','#WF_REPLAY_OUT',strGuid,tmpStr);
1508 tmpStr := ReplaceContent('<TO_AGENT_GUID>','</TO_AGENT_GUID>','#WF_REPLAY_OUT',strGuid,tmpStr);
1509 END LOOP;
1510 FOR c IN agent('WF_CONTROL') LOOP
1511 strGuid := c.guid; -- rawtohex(c.guid);
1512 tmpStr := ReplaceContent('<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','#WF_CONTROL',strGuid,tmpStr);
1513 tmpStr := ReplaceContent('<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','#WF_CONTROL',strGuid,tmpStr);
1514 tmpStr := ReplaceContent('<TO_AGENT_GUID>','</TO_AGENT_GUID>','#WF_CONTROL',strGuid,tmpStr);
1515 END LOOP;
1516 FOR c IN agent('WF_JMS_IN') LOOP
1517 strGuid := c.guid; -- rawtohex(c.guid);
1518 tmpStr := ReplaceContent('<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','#WF_JMS_IN',strGuid,tmpStr);
1519 tmpStr := ReplaceContent('<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','#WF_JMS_IN',strGuid,tmpStr);
1520 tmpStr := ReplaceContent('<TO_AGENT_GUID>','</TO_AGENT_GUID>','#WF_JMS_IN',strGuid,tmpStr);
1521 END LOOP;
1522 FOR c IN agent('WF_JMS_OUT') LOOP
1523 strGuid := c.guid; -- rawtohex(c.guid);
1524 tmpStr := ReplaceContent('<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','#WF_JMS_OUT',strGuid,tmpStr);
1525 tmpStr := ReplaceContent('<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','#WF_JMS_OUT',strGuid,tmpStr);
1526 tmpStr := ReplaceContent('<TO_AGENT_GUID>','</TO_AGENT_GUID>','#WF_JMS_OUT',strGuid,tmpStr);
1527 END LOOP;
1528 FOR c IN agent('WF_NOTIFICATION_IN') LOOP
1529 strGuid := c.guid; -- rawtohex(c.guid);
1530 tmpStr := ReplaceContent('<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','#WF_NOTIFICATION_IN',strGuid,tmpStr);
1531 tmpStr := ReplaceContent('<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','#WF_NOTIFICATION_IN',strGuid,tmpStr);
1532 tmpStr := ReplaceContent('<TO_AGENT_GUID>','</TO_AGENT_GUID>','#WF_NOTIFICATION_IN',strGuid,tmpStr);
1533 END LOOP;
1534 FOR c IN agent('WF_NOTIFICATION_OUT') LOOP
1535 strGuid := c.guid; -- rawtohex(c.guid);
1536 tmpStr := ReplaceContent('<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>','#WF_NOTIFICATION_OUT',strGuid,tmpStr);
1537 tmpStr := ReplaceContent('<OUT_AGENT_GUID>','</OUT_AGENT_GUID>','#WF_NOTIFICATION_OUT',strGuid,tmpStr);
1538 tmpStr := ReplaceContent('<TO_AGENT_GUID>','</TO_AGENT_GUID>','#WF_NOTIFICATION_OUT',strGuid,tmpStr);
1539 END LOOP;
1540
1541 retStr := tmpStr;
1542 else
1543 retStr := dataStr;
1544 end if;
1545 return (retStr);
1546
1547 exception
1548 when others then
1549 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'SetAgent');
1550 raise;
1551 end;
1552
1553 ------------------------------------------------------------------------------
1554 /*
1555 ** SetPound - Generate #xxx when encounter right tag, returns varchar2
1556 ** Parameters: startPos
1557 ** dataStr
1558 ** begTag
1559 ** endTag
1560 ** pound
1561 ** matchStr
1562 */
1563 function SetPound (
1564 startPos in number,
1565 dataStr in varchar2,
1566 begTag in varchar2,
1567 endTag in varchar2,
1568 pound in varchar2,
1569 matchStr in varchar2
1570 ) return varchar2 is
1571
1572 l_read_amt integer := 0;
1573 l_cont_amt integer := 0;
1574 l_str varchar2(32000) default null;
1575 retStr varchar2(32000) default null;
1576 l_str_new varchar2(32000) default null;
1577 l_content varchar2(1000) default null;
1578 l_pos number default 1;
1579 beg_pos number default 0;
1580 end_pos number default 0;
1581
1582 begin
1583 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1584 wf_log_pkg.string(wf_log_pkg.level_procedure,
1585 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.SETPOUND.Begin',
1586 'Entered SetPound.');
1587 end if;
1588
1589 if dataStr is not null then
1590 retStr := dataStr;
1591 beg_pos := instr(dataStr, begTag, startPos);
1592 end_pos := instr(dataStr, endTag, beg_pos);
1593 l_read_amt := end_pos - beg_pos;
1594
1595 if ((beg_pos <> 0) and
1596 (end_pos <> 0) and
1597 (l_read_amt > 0)) then
1598 l_str := substr(dataStr,beg_pos,l_read_amt);
1599
1600 l_pos := instr(dataStr, l_str);
1601 if (l_pos > 1) then
1602 if matchStr is not null then
1603 -- check if matchStr matches the content within the tags
1604 l_cont_amt := l_read_amt - length(begTag);
1605 l_content := substr(dataStr,beg_pos+length(begTag),l_cont_amt);
1606 if (l_content = matchStr) then
1607 l_str_new := begTag||'#'||pound;
1608 retStr := replace(retStr,l_str,l_str_new);
1609 end if;
1610 else
1611 l_str_new := begTag||'#'||pound;
1612 retStr := replace(retStr,l_str,l_str_new);
1613 end if;
1614 end if;
1615 end if;
1616 else
1617 retStr := dataStr;
1618 end if;
1619 return (retStr);
1620 exception
1621 when others then
1622 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'SetPound');
1623 raise;
1624 end;
1625
1626 ------------------------------------------------------------------------------
1627 /*
1628 ** SetNull - Return a null tag (in <tag\> format), returns varchar2
1629 ** Parameters: startPos
1630 ** dataStr
1631 ** tag
1632 */
1633 function SetNull (
1634 startPos in number,
1635 dataStr in varchar2,
1636 tag in varchar2
1637 ) return varchar2 is
1638
1639 l_read_amt integer := 0;
1640 l_str varchar2(32000) default null;
1641 retStr varchar2(32000) default null;
1642 l_str_new varchar2(32000) default null;
1643 l_pos number default 1;
1644 beg_pos number default 0;
1645 end_pos number default 0;
1646 endTagLen number default 3; -- '</>'
1647
1648 begin
1649 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1650 wf_log_pkg.string(wf_log_pkg.level_procedure,
1651 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.SETNULL.Begin',
1652 'Entered SetNull.');
1653 end if;
1654
1655 if dataStr is not null then
1656 retStr := dataStr;
1657 beg_pos := instr(dataStr, '<'||tag||'>', startPos);
1658 end_pos := instr(dataStr, '</'||tag||'>', beg_pos);
1659 l_read_amt := end_pos + endTagLen + length(tag) - beg_pos;
1660
1661 if ((beg_pos <> 0) and
1662 (end_pos <> 0) and
1663 (l_read_amt > 0)) then
1664 l_str := substr(dataStr,beg_pos,l_read_amt);
1665 l_pos := instr(dataStr, l_str);
1666 if (l_pos > 1) then
1667 l_str_new := '<'||tag||'/>';
1668 retStr := replace(retStr,l_str,l_str_new);
1669 end if;
1670 end if;
1671 else
1672 retStr := dataStr;
1673 end if;
1674 return (retStr);
1675 exception
1676 when others then
1677 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'SetNull');
1678 raise;
1679 end;
1680
1681 ------------------------------------------------------------------------------
1682 /*
1683 ** CreateEmptyClob - Creates a empty clob for Java to use, returns CLOB
1684 */
1685 procedure CREATEEMPTYCLOB (
1686 P_OUTCLOB out nocopy clob
1687 ) is
1688 begin
1689
1690 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1691 wf_log_pkg.string(wf_log_pkg.level_procedure,
1692 'wf.plsql.WF_EVENT_SYNCHRONIZE_PKG.CREATEEMPTYCLOB.Begin',
1693 'Entered Create Empty Clob');
1694 end if;
1695
1696 dbms_lob.createtemporary(p_outclob, FALSE, DBMS_LOB.CALL);
1697 exception
1698 when others then
1699 wf_core.context('WF_EVENT_SYNCHRONIZE_PKG', 'CREATEEMPTYCLOB');
1700 raise;
1701 end;
1702 ------------------------------------------------------------------------------
1703 /*
1704 ** GetAgent - Returns a string replacing the agent GUID with the
1705 ** #<AGENT_NAME> .
1706 */
1707 function GetAgent (
1708 begTag in varchar2,
1709 endTag in varchar2,
1710 dataStr in varchar2
1711 ) return varchar2 is
1712 retStr varchar2(32000) default null;
1713 beg_pos number default 1;
1714 end_pos number default 1;
1715 l_pos number default 1;
1716 l_amount_to_read number default 0;
1717 l_str varchar2(32000) default null;
1718 l_str_new varchar2(32000) default null;
1719 l_agtguid varchar2(4000);
1720 l_replaceTarget varchar2(32000);
1721
1722 begin
1723 if dataStr is not null then
1724 retStr := dataStr;
1725 beg_pos := instr(dataStr, begTag);
1726 end_pos := instr(dataStr, endTag);
1727 l_amount_to_read := end_pos - beg_pos;
1728 if ((beg_pos <> 0) and
1729 (end_pos <> 0) and
1730 (l_amount_to_read > 0)) then
1731 l_str := substr(dataStr,beg_pos,l_amount_to_read);
1732 l_pos := length(begTag) +1 ;
1733 l_agtguid := substr(l_str,l_pos);
1734
1735 begin
1736 --Get the agent name from the guid given
1737 select name
1738 into l_replaceTarget
1739 from wf_agents
1740 where guid = l_agtguid;
1741 exception
1742 when others then
1743 wf_core.token('GUID', l_agtguid);
1744 wf_core.raise('WFE_AGENT_NOTRESOLVE');
1745 end;
1746
1747 l_replaceTarget := '#'||l_replaceTarget;
1748
1749 if (l_pos > 1) then
1750 l_str_new := replace(l_str,l_agtguid,l_replaceTarget);
1751 retStr := replace(retStr,l_str,l_str_new);
1752 end if;
1753 end if;
1754 else
1755 retStr := dataStr;
1756 end if;
1757 return (retStr);
1758 exception
1759 when others then
1760 wf_core.context('Wf_Event_Synchronize_Pkg', 'GetAgent');
1761 raise;
1762 end;
1763 --------------------------------------------------------------------------------------------
1764 /*
1765 ** SetAgent2 - Returns a string replacing the agent the #<AGENT_NAME>
1766 ** with the guid of the agent in the db.
1767 */
1768 function SetAgent2 (
1769 begTag in varchar2,
1770 endTag in varchar2,
1771 dataStr in varchar2
1772 ) return varchar2 is
1773 retStr varchar2(32000) default null;
1774 beg_pos number default 1;
1775 end_pos number default 1;
1776 l_pos number default 1;
1777 l_amount_to_read number default 0;
1778 l_str varchar2(32000) default null;
1779 l_str_new varchar2(32000) default null;
1780 l_agtname varchar2(40);
1781 l_replaceTarget varchar2(32000);
1782 l_agt varchar2(30);
1783
1784 begin
1785 if dataStr is not null then
1786 retStr := dataStr;
1787 beg_pos := instr(dataStr, begTag);
1788 end_pos := instr(dataStr, endTag);
1789 l_amount_to_read := end_pos - beg_pos;
1790 if ((beg_pos <> 0) and
1791 (end_pos <> 0) and
1792 (l_amount_to_read > 0)) then
1793 l_str := substr(dataStr,beg_pos,l_amount_to_read);
1794 l_pos := length(begTag) +1 ;
1795 l_agtname := substr(l_str,l_pos);
1796 --Check if we have the # and strip it off
1797 --We check explicilty that its the first char as we
1798 --have not put any restriction on agent naming.
1799 --Else case we just passback the string
1800 if (instr(l_agtname , '#') = 1 ) then
1801 l_agt := substr(l_agtname,2);
1802 --Get the agent name from the guid given
1803 select guid
1804 into l_replaceTarget
1805 from wf_agents
1806 where name = l_agt;
1807
1808 if (l_pos > 1) then
1809 l_str_new := replace(l_str,l_agtname,l_replaceTarget);
1810 retStr := replace(retStr,l_str,l_str_new);
1811 end if;
1812 end if;
1813 end if;
1814 else
1815 retStr := dataStr;
1816 end if;
1817 return (retStr);
1818 exception
1819 when no_data_found then
1820 wf_core.token('AGENT',l_agtname);
1821 wf_core.raise('WFE_SEEDAGT_NOTFOUND');
1822 when others then
1823 raise;
1824 end;
1825 --------------------------------------------------------------------------------------------
1826 procedure CREATESYNCCLOB2 (
1827 P_OBJECTTYPE in varchar2 DEFAULT NULL,
1828 P_OBJECTKEY in varchar2 DEFAULT NULL,
1829 P_ISEXACTNUM in integer DEFAULT 1,
1830 P_OWNERTAG in varchar2 DEFAULT NULL,
1831 P_EVENTDATA out nocopy clob,
1832 P_ERROR_CODE out nocopy varchar2,
1833 P_ERROR_MSG out nocopy varchar2
1834 )
1835 is
1836 begin
1837 Wf_Event_Synchronize_Pkg.CreateSyncClob(P_OBJECTTYPE, P_OBJECTKEY, P_ISEXACTNUM,
1838 P_OWNERTAG, P_EVENTDATA);
1839 p_error_code := null;
1840 p_error_msg := null;
1841 exception
1842 when others then
1843 if (wf_core.error_name is not null) then
1844 p_error_code := wf_core.error_name;
1845 p_error_msg := wf_core.error_message;
1846 else
1847 raise;
1848 end if;
1849 end CREATESYNCCLOB2;
1850 --------------------------------------------------------------------------------------------
1851
1852 end WF_EVENT_SYNCHRONIZE_PKG;