[Home] [Help]
PACKAGE BODY: APPS.WF_AGENTS_PKG
Source
1 package body WF_AGENTS_PKG as
2 /* $Header: WFEVAGTB.pls 120.2 2005/09/02 15:20:58 vshanmug ship $ */
3 m_table_name varchar2(255) := 'WF_AGENTS';
4 m_package_version varchar2(30) := '1.0';
5 -----------------------------------------------------------------------------
6 procedure INSERT_ROW (
7 X_ROWID in out nocopy varchar2,
8 X_GUID in raw,
9 X_NAME in varchar2,
10 X_SYSTEM_GUID in raw,
11 X_PROTOCOL in varchar2,
12 X_ADDRESS in varchar2,
13 X_QUEUE_HANDLER in varchar2,
14 X_QUEUE_NAME in varchar2,
15 X_DIRECTION in varchar2,
16 X_STATUS in varchar2,
17 X_DISPLAY_NAME in varchar2,
18 X_DESCRIPTION in varchar2,
19 X_TYPE in varchar2,
20 X_JAVA_QUEUE_HANDLER in varchar2
21 ) is
22 cursor C is select rowid from wf_agents where guid = X_GUID;
23 begin
24 insert into wf_agents (
25 guid,
26 name,
27 system_guid,
28 protocol,
29 address,
30 queue_handler,
31 queue_name,
32 direction,
33 status,
34 display_name,
35 description,
36 type,
37 java_queue_handler
38 ) values (
39 X_GUID,
40 X_NAME,
41 X_SYSTEM_GUID,
42 X_PROTOCOL,
43 X_ADDRESS,
44 X_QUEUE_HANDLER,
45 X_QUEUE_NAME,
46 X_DIRECTION,
47 X_STATUS,
48 X_DISPLAY_NAME,
49 X_DESCRIPTION,
50 X_TYPE,
51 X_JAVA_QUEUE_HANDLER
52 );
53
54 open c;
55 fetch c into X_ROWID;
56 if (c%notfound) then
57 close c;
58 raise no_data_found;
59 else
60 wf_event.raise('oracle.apps.wf.event.agent.create',x_guid);
61 end if;
62 close c;
63
64 exception
65 when others then
66 wf_core.context('Wf_Agents_Pkg', 'Insert_Row', x_guid,
67 x_protocol );
68 raise;
69 end INSERT_ROW;
70 -----------------------------------------------------------------------------
71 procedure UPDATE_ROW (
72 X_GUID in raw,
73 X_NAME in varchar2,
74 X_SYSTEM_GUID in raw,
75 X_PROTOCOL in varchar2,
76 X_ADDRESS in varchar2,
77 X_QUEUE_HANDLER in varchar2,
78 X_QUEUE_NAME in varchar2,
79 X_DIRECTION in varchar2,
80 X_STATUS in varchar2,
81 X_DISPLAY_NAME in varchar2,
82 X_DESCRIPTION in varchar2,
83 X_TYPE in varchar2,
84 X_JAVA_QUEUE_HANDLER in varchar2
85 ) is
86 begin
87 if (x_type is null) then
88 update wf_agents set
89 name = X_NAME,
90 system_guid = X_SYSTEM_GUID,
91 protocol = X_PROTOCOL,
92 address = X_ADDRESS,
93 queue_handler = X_QUEUE_HANDLER,
94 queue_name = X_QUEUE_NAME,
95 direction = X_DIRECTION,
96 status = X_STATUS,
97 display_name = X_DISPLAY_NAME,
98 description = X_DESCRIPTION,
99 java_queue_handler = X_JAVA_QUEUE_HANDLER
100 where guid = X_GUID;
101 else
102 update wf_agents set
103 name = X_NAME,
104 system_guid = X_SYSTEM_GUID,
105 protocol = X_PROTOCOL,
106 address = X_ADDRESS,
107 queue_handler = X_QUEUE_HANDLER,
108 queue_name = X_QUEUE_NAME,
109 direction = X_DIRECTION,
110 status = X_STATUS,
111 display_name = X_DISPLAY_NAME,
112 description = X_DESCRIPTION,
113 type = X_TYPE,
114 java_queue_handler = X_JAVA_QUEUE_HANDLER
115 where guid = X_GUID;
116 end if;
117
118 if (sql%notfound) then
119 raise no_data_found;
120 else
121 wf_event.raise('oracle.apps.wf.event.agent.update',x_guid);
122 end if;
123
124 exception
125 when others then
126 wf_core.context('Wf_Agents_Pkg', 'Update_Row', x_guid,
127 x_protocol );
128 raise;
129 end UPDATE_ROW;
130 -----------------------------------------------------------------------------
131 procedure LOAD_ROW (
132 X_GUID in raw,
133 X_NAME in varchar2,
134 X_SYSTEM_GUID in raw,
135 X_PROTOCOL in varchar2,
136 X_ADDRESS in varchar2,
137 X_QUEUE_HANDLER in varchar2,
138 X_QUEUE_NAME in varchar2,
139 X_DIRECTION in varchar2,
140 X_STATUS in varchar2,
141 X_DISPLAY_NAME in varchar2,
142 X_DESCRIPTION in varchar2,
143 X_TYPE in varchar2,
144 X_JAVA_QUEUE_HANDLER in varchar2
145 ) is
146 row_id varchar2(64);
147 begin
148 begin
149 if (x_type is null) then
150 WF_AGENTS_PKG.UPDATE_ROW (
151 X_GUID => X_GUID,
152 X_NAME => X_NAME,
153 X_SYSTEM_GUID => X_SYSTEM_GUID,
154 X_PROTOCOL => X_PROTOCOL,
155 X_ADDRESS => X_ADDRESS,
156 X_QUEUE_HANDLER => X_QUEUE_HANDLER,
157 X_QUEUE_NAME => X_QUEUE_NAME,
158 X_DIRECTION => X_DIRECTION,
159 X_STATUS => X_STATUS,
160 X_DISPLAY_NAME => X_DISPLAY_NAME,
161 X_DESCRIPTION => X_DESCRIPTION,
162 X_JAVA_QUEUE_HANDLER => X_JAVA_QUEUE_HANDLER
163 );
164 else
165 WF_AGENTS_PKG.UPDATE_ROW (
166 X_GUID => X_GUID,
167 X_NAME => X_NAME,
168 X_SYSTEM_GUID => X_SYSTEM_GUID,
169 X_PROTOCOL => X_PROTOCOL,
170 X_ADDRESS => X_ADDRESS,
171 X_QUEUE_HANDLER => X_QUEUE_HANDLER,
172 X_QUEUE_NAME => X_QUEUE_NAME,
173 X_DIRECTION => X_DIRECTION,
174 X_STATUS => X_STATUS,
175 X_DISPLAY_NAME => X_DISPLAY_NAME,
176 X_DESCRIPTION => X_DESCRIPTION,
177 X_TYPE => X_TYPE,
178 X_JAVA_QUEUE_HANDLER => X_JAVA_QUEUE_HANDLER
179 );
180 end if;
181
182 -- Invalidate cache
183 wf_bes_cache.SetMetaDataUploaded();
184 exception
185 when no_data_found then
186 wf_core.clear;
187 if (x_type is null) then
188 WF_AGENTS_PKG.INSERT_ROW(
189 X_ROWID => row_id,
190 X_GUID => X_GUID,
191 X_NAME => X_NAME,
192 X_SYSTEM_GUID => X_SYSTEM_GUID,
193 X_PROTOCOL => X_PROTOCOL,
194 X_ADDRESS => X_ADDRESS,
195 X_QUEUE_HANDLER => X_QUEUE_HANDLER,
196 X_QUEUE_NAME => X_QUEUE_NAME,
197 X_DIRECTION => X_DIRECTION,
198 X_STATUS => X_STATUS,
199 X_DISPLAY_NAME => X_DISPLAY_NAME,
200 X_DESCRIPTION => X_DESCRIPTION,
201 X_JAVA_QUEUE_HANDLER => X_JAVA_QUEUE_HANDLER
202 );
203 else
204 WF_AGENTS_PKG.INSERT_ROW(
205 X_ROWID => row_id,
206 X_GUID => X_GUID,
207 X_NAME => X_NAME,
208 X_SYSTEM_GUID => X_SYSTEM_GUID,
209 X_PROTOCOL => X_PROTOCOL,
210 X_ADDRESS => X_ADDRESS,
211 X_QUEUE_HANDLER => X_QUEUE_HANDLER,
212 X_QUEUE_NAME => X_QUEUE_NAME,
213 X_DIRECTION => X_DIRECTION,
214 X_STATUS => X_STATUS,
215 X_DISPLAY_NAME => X_DISPLAY_NAME,
216 X_DESCRIPTION => X_DESCRIPTION,
217 X_TYPE => X_TYPE,
218 X_JAVA_QUEUE_HANDLER => X_JAVA_QUEUE_HANDLER
219 );
220 end if;
221 end;
222
223 exception
224 when others then
225 wf_core.context('Wf_Agents_Pkg', 'Load_Row', x_guid,
226 x_protocol );
227 raise;
228 end LOAD_ROW;
229 -----------------------------------------------------------------------------
230 procedure DELETE_ROW (
231 X_GUID in raw
232 ) is
233 begin
234 wf_event.raise('oracle.apps.wf.event.agent.delete',x_guid);
235
236 delete from wf_agents where guid = X_GUID;
237
238 if (sql%notfound) then
239 raise no_data_found;
240 end if;
241
242 -- Invalidate cache
243 wf_bes_cache.SetMetaDataUploaded();
244 exception
245 when others then
246 wf_core.context('Wf_Agents_Pkg', 'Delete_Row', x_guid);
247 raise;
248 end DELETE_ROW;
249 -----------------------------------------------------------------------------
250 function GENERATE (
251 X_GUID in raw
252 ) return varchar2 is
253 buf varchar2(32000);
254 l_doc xmldom.DOMDocument;
255 l_element xmldom.DOMElement;
256 l_root xmldom.DOMNode;
257 l_node xmldom.DOMNode;
258 l_header xmldom.DOMNode;
259
260 l_name varchar2(80);
261 l_system_guid raw(16);
262 l_protocol varchar2(30);
263 l_address varchar2(240);
264 l_queue_handler varchar2(240);
265 l_queue_name varchar2(80);
266 l_direction varchar2(8);
267 l_status varchar2(8);
268 l_display_name varchar2(80);
269 l_description varchar2(240);
270 l_type varchar2(8);
271 l_javaqhandler varchar2(240);
272
273 begin
274 select name, system_guid, protocol, address, queue_handler,
275 queue_name, direction, status, display_name, description,
276 type,java_queue_handler
277 into l_name, l_system_guid, l_protocol, l_address, l_queue_handler,
278 l_queue_name, l_direction, l_status, l_display_name, l_description,
279 l_type,l_javaqhandler
280 from wf_agents
281 where guid = x_guid;
282
283 l_doc := xmldom.newDOMDocument;
284 l_root := xmldom.makeNode(l_doc);
285 l_root := wf_event_xml.newtag(l_doc, l_root, wf_event_xml.masterTagName);
286 l_header := wf_event_xml.newtag(l_doc, l_root, m_table_name);
287
288 l_node := wf_event_xml.newtag(l_doc, l_header, wf_event_xml.versionTagName,
289 m_package_version);
290 l_node := wf_event_xml.newtag(l_doc, l_header, 'GUID',
291 rawtohex(x_guid));
292 l_node := wf_event_xml.newtag(l_doc, l_header, 'NAME',
293 l_name);
294 l_node := wf_event_xml.newtag(l_doc, l_header, 'SYSTEM_GUID',
295 rawtohex(l_system_guid));
296 l_node := wf_event_xml.newtag(l_doc, l_header, 'PROTOCOL',
297 l_protocol);
298 l_node := wf_event_xml.newtag(l_doc, l_header, 'ADDRESS',
299 l_address);
300 l_node := wf_event_xml.newtag(l_doc, l_header, 'QUEUE_HANDLER',
301 l_queue_handler);
302 --Bug 3328673
303 --Add the new tag for java queue handler <this is a nullable column>
304 l_node := wf_event_xml.newtag(l_doc, l_header, 'JAVA_QUEUE_HANDLER',
305 l_javaqhandler);
306
307 l_node := wf_event_xml.newtag(l_doc, l_header, 'QUEUE_NAME',
308 l_queue_name);
309 l_node := wf_event_xml.newtag(l_doc, l_header, 'DIRECTION',
310 l_direction);
311 l_node := wf_event_xml.newtag(l_doc, l_header, 'STATUS',
312 l_status);
313 l_node := wf_event_xml.newtag(l_doc, l_header, 'DISPLAY_NAME',
314 l_display_name);
315 l_node := wf_event_xml.newtag(l_doc, l_header, 'DESCRIPTION',
316 l_description);
317 l_node := wf_event_xml.newtag(l_doc, l_header, 'TYPE',
318 l_type);
319
320 xmldom.writeToBuffer(l_root, buf);
321
322 return buf;
323 exception
324 when others then
325 wf_core.context('Wf_Agents_Pkg', 'Generate', x_guid);
326 raise;
327 end GENERATE;
328 -----------------------------------------------------------------------------
329 procedure RECEIVE (
330 X_MESSAGE in varchar2
331 ) is
332 l_guid raw(16);
333 l_name varchar2(80);
334 l_system_guid raw(16);
335 l_protocol varchar2(30);
336 l_address varchar2(240);
337 l_queue_handler varchar2(240);
338 l_queue_name varchar2(80);
339 l_direction varchar2(8);
340 l_status varchar2(8);
341 l_display_name varchar2(80);
342 l_description varchar2(240);
343 l_version varchar2(80);
344 l_message varchar2(32000);
345 l_type varchar2(8);
346
347 l_node_name varchar2(255);
348 l_node xmldom.DOMNode;
349 l_child xmldom.DOMNode;
350 l_value varchar2(32000);
351 l_length integer;
352 l_node_list xmldom.DOMNodeList;
353
354 l_agent_guid varchar2(32);
355 l_javaqhandler varchar2(240);
356
357 /* Identical Row Cursor
358 ** A row is considered identical if it has the same agent name.
359 */
360 cursor identical_row is
361 select GUID
362 from WF_AGENTS
363 where NAME = l_name;
364
365 begin
366
367 l_message := x_message;
368 -- l_message := WF_EVENT_SYNCHRONIZE_PKG.SetGUID(l_message); -- update #NEW
369 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetSYSTEMGUID(l_message); -- update #LOCAL
370 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetSID(l_message); -- update #SID
371
372 l_node_list := wf_event_xml.findTable(l_message, m_table_name);
373 l_length := xmldom.getLength(l_node_list);
374
375 -- loop through elements that we received.
376 for i in 0..l_length-1 loop
377 l_node := xmldom.item(l_node_list, i);
378 l_node_name := xmldom.getNodeName(l_node);
379 if xmldom.hasChildNodes(l_node) then
380 l_child := xmldom.GetFirstChild(l_node);
381 l_value := xmldom.getNodevalue(l_child);
382 else
383 l_value := NULL;
384 end if;
385
386 if(l_node_name = 'GUID') then
387 -- l_guid := l_value;
388 l_agent_guid := l_value;
389 elsif(l_node_name = 'NAME') then
390 l_name := l_value;
391 elsif(l_node_name = 'SYSTEM_GUID') then
392 l_system_guid := l_value;
393 elsif(l_node_name = 'PROTOCOL') then
394 l_protocol := l_value;
395 elsif(l_node_name = 'ADDRESS') then
396 l_address := l_value;
397 elsif(l_node_name = 'QUEUE_HANDLER') then
398 l_queue_handler := l_value;
399 --Bug 3328673
400 --Add support for java q handler in loader
401 elsif(l_node_name = 'JAVA_QUEUE_HANDLER') then
402 l_javaqhandler := l_value;
403
404 elsif(l_node_name = 'QUEUE_NAME') then
405 l_queue_name := l_value;
406 elsif(l_node_name = 'DIRECTION') then
407 l_direction := l_value;
408 elsif(l_node_name = 'STATUS') then
409 l_status := l_value;
410 elsif(l_node_name = 'DISPLAY_NAME') then
411 l_display_name := l_value;
412 elsif(l_node_name = 'DESCRIPTION') then
413 l_description := l_value;
414 elsif(l_node_name = 'TYPE') then
415 l_type := l_value;
416 elsif(l_node_name = wf_event_xml.versionTagName) then
417 l_version := l_value;
418 else
419 Wf_Core.Token('REASON', 'Invalid column name found:' ||
420 l_node_name || ' with value:'||l_value);
421 Wf_Core.Raise('WFSQL_INTERNAL');
422 end if;
423 end loop;
424
425 if l_agent_guid = '#NEW' then
426 -- A row is consigered identical if it has the same agent name
430 l_guid := sys_guid();
427 open identical_row;
428 fetch identical_row into l_guid;
429 if (identical_row%notfound) then
431 end if;
432 close identical_row;
433 else
434 l_guid := hextoraw(l_agent_guid);
435 end if;
436
437 wf_agents_pkg.load_row(
438 X_GUID => l_guid,
439 X_NAME => l_name,
440 X_SYSTEM_GUID => l_system_guid,
441 X_PROTOCOL => l_protocol,
442 X_ADDRESS => l_address,
443 X_QUEUE_HANDLER => l_queue_handler,
444 X_QUEUE_NAME => l_queue_name,
445 X_DIRECTION => l_direction,
446 X_STATUS => l_status,
447 X_DISPLAY_NAME => l_display_name,
448 X_DESCRIPTION => l_description,
449 X_TYPE => l_type,
450 X_JAVA_QUEUE_HANDLER => l_javaqhandler);
451
452 exception
453 when others then
454 wf_core.context('Wf_Agents_Pkg', 'Receive', x_message);
455 raise;
456 end RECEIVE;
457 -------------------------------------------------------------------------
458 end WF_AGENTS_PKG;