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