[Home] [Help]
PACKAGE BODY: APPS.FND_TRANSACTION_PIPE
Source
1 package body fnd_transaction_pipe as
2 /* $Header: AFCPTRPB.pls 120.1 2005/09/17 02:02:11 pferguso noship $ */
3
4 --
5 -- Constants
6 --
7 FNDCPRP constant varchar2(10) := 'FNDCPTM:R:'; -- R pipe prefix
8 FNDCPTP constant varchar2(10) := 'FNDCPTM:T:'; -- T pipe prefix
9
10 --
11 -- Private Variables
12 --
13 tm_pipe varchar2(80);
14 debug_flag boolean := FALSE;
15
16
17 -- get_manager
18 -- Purpose
19 -- Build the names of the request and token pipes
20 -- to which a request will be sent.
21 -- Arguments
22 -- IN:
23 -- application - program application short name
24 -- program - program short name
25 -- Returns
26 -- 0 on success.
27 -- 2 if no manager is available
28 -- 3 on failure.
29 -- Notes
30 -- Pipe names have the following format:
31 -- prefix:concurrent_queue_id
32 --
33 function get_manager(application in varchar2,
34 program in varchar2,
35 timeout in number) return number is
36
37 queue_id number;
38 status number;
39 all_busy exception;
40 error exception;
41 token_pipe varchar2(80);
42 resp_appl_id number;
43 resp_id number;
44
45 cursor tm(appl_short_name varchar2, prog_short_name varchar2,
46 resp_appl_id number, resp_id number) is
47 select /*+ ORDERED USE_NL (fa fcp fr fcpp fcq)
48 INDEX (fcq,FND_CONCURRENT_QUEUES_N1)
49 INDEX (fcpp,FND_CONC_PROCESSOR_PROGRAMS_U2) */
50 fcq.concurrent_queue_id
51 from fnd_application fa,
52 fnd_concurrent_programs fcp,
53 fnd_conc_processor_programs fcpp,
54 fnd_responsibility fr,
55 fnd_concurrent_queues fcq,
56 fnd_concurrent_processes fcpr
57 where fcq.processor_application_id = fcpp.processor_application_id
58 and fcq.concurrent_processor_id = fcpp.concurrent_processor_id
59 and fcpp.concurrent_program_id = fcp.concurrent_program_id
60 and fcpp.program_application_id = fcp.application_id
61 and fcp.application_id = fa.application_id
62 and fa.application_short_name = appl_short_name
63 and fcp.concurrent_program_name = prog_short_name
64 and fr.responsibility_id = resp_id
65 and fr.application_id = resp_appl_id
66 and fr.data_group_id = fcq.data_group_id
67 and fcq.manager_type = '3'
68 and fcpr.concurrent_queue_id = fcq.concurrent_queue_id
69 and fcpr.queue_application_id = fcq.application_id
70 and fcpr.process_status_code = 'A'
71 and fcpr.instance_number = userenv('instance')
72 order by DBMS_RANDOM.RANDOM;
73
74
75 begin
76
77 debug_flag := fnd_transaction.debug_flag;
78
79 resp_appl_id := fnd_global.resp_appl_id;
80 resp_id := fnd_global.resp_id;
81
82 if (debug_flag) then
83 fnd_transaction.debug_info('fnd_trn_pipe.get_manager',
84 'Searching for manager to run:',
85 application || ':' || program);
86 fnd_transaction.debug_info('fnd_trn_pipe.get_manager',
87 'RESP_APPL_ID:RESP_ID',
88 to_char(resp_appl_id) || ':' || to_char(resp_id));
89 end if;
90
91 open tm(application, program, resp_appl_id, resp_id);
92
93 -- Fetch ID of first manager
94 fetch tm into queue_id;
95 -- If cursor is empty, then no manager is defined for request.
96 if (tm%rowcount = 0 ) then
97 fnd_transaction.debug_info('fnd_trn_pipe.get_manager', 'No manager defined', NULL);
98
99 fnd_transaction.post_tm_event(1, application, program, -1);
100
101 fnd_message.set_name('FND', 'CONC-TM-No manager defined');
102 fnd_message.set_token('APPLICATION', application);
103 fnd_message.set_token('PROGRAM', program);
104 fnd_message.set_token('RESP_ID', resp_id);
105 fnd_message.set_token('RESP_APPL_ID', resp_appl_id);
106 raise error;
107 end if;
108
109 if (debug_flag) then
110 fnd_transaction.debug_info('fnd_trn_pipe.get_manager',
111 'Timeout for token pipe',
112 to_char(timeout));
113 end if;
114
115 loop
116 -- Attempt to get the token for the manager
117 token_pipe := FNDCPTP || to_char(queue_id);
118 if (debug_flag) then
119 fnd_transaction.debug_info('fnd_trn_pipe.get_manager',
120 'Trying token pipe',
121 token_pipe);
122 end if;
123 status := dbms_pipe.receive_message(token_pipe, timeout);
124
125 -- Exit loop if we got the token
126 exit when (status = 0);
127
128 -- Raise exception on error other than timeout.
129 if (status <> 1) then
130 fnd_transaction.debug_info('fnd_trn_pipe.get_manager', 'Token read error:', to_char(status));
131 fnd_message.set_name('FND', 'CONC-TM-Token read error');
132 raise error;
133 end if;
134
135 /* mark soft busy event (2) */
136 fnd_transaction.post_tm_event(2, application, program, queue_id);
137
138 -- Fetch next manager name
139 fetch tm into queue_id;
140 if tm%notfound then raise all_busy; end if; -- Exhausted the list
141 end loop;
142
143 -- bug 3623063
144 -- save queue_id to be used for logging msgs to fnd_concurrent_debug_info
145 fnd_transaction.conc_queue_id := queue_id;
146
147 -- Construct the manager pipe name and return with success.
148 tm_pipe := FNDCPRP || to_char(queue_id);
149 close tm;
150
151 if (debug_flag) then
152 fnd_transaction.debug_info('fnd_trn_pipe.get_manager',
153 'Got available TM process',
154 tm_pipe);
155 end if;
156
157 return fnd_transaction.E_SUCCESS;
158
159 exception
160 when all_busy then -- All managers capable of running the request
161 -- are busy or down.
162
163 /* mark all appropriate managers as hard busy events */
164 fnd_transaction.post_tm_event(3, application, program, -1 );
165
166 close tm;
167 fnd_transaction.debug_info('fnd_trn_pipe.get_manager', 'All managers busy or down', NULL);
168 return fnd_transaction.E_NOMGR;
169 when error then
170 if tm%isopen then
171 close tm;
172 end if;
173 return fnd_transaction.E_OTHER;
174 when others then
175 if tm%isopen then
176 close tm;
177 end if;
178 fnd_message.set_name ('FND', 'SQL-Generic error');
179 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
180 fnd_message.set_token ('REASON', sqlerrm, FALSE);
181 fnd_message.set_token ('ROUTINE', 'FND_TRN_PIPE.GET_MANAGER', FALSE);
182 fnd_transaction.debug_info('fnd_trn_pipe.get_manager', 'Caught exception', sqlerrm);
183
184 return fnd_transaction.E_OTHER;
185
186 end get_manager;
187
188
189
190
191 --
192 -- send_message
193 -- Purpose
194 -- Puts the transaction message on the pipe and waits for the return message
195 -- Returns
196 -- E_SUCCESS on success
197 -- E_TIMEOUT if return message times out
198 -- E_OTHER on failure
199 --
200 function send_message( timeout in number,
201 send_type in varchar2,
202 expiration_time in date,
203 request_id in number,
204 nls_lang in varchar2,
205 nls_num_chars in varchar2,
206 nls_date_lang in varchar2,
207 secgrpid in number,
208 enable_trace_flag in varchar2,
209 application in varchar2,
210 program in varchar2,
211 org_type in varchar2,
212 org_id in number,
213 outcome in out nocopy varchar2,
214 message in out nocopy varchar2,
215 arg_1 in varchar2,
216 arg_2 in varchar2,
217 arg_3 in varchar2,
218 arg_4 in varchar2,
219 arg_5 in varchar2,
220 arg_6 in varchar2,
221 arg_7 in varchar2,
222 arg_8 in varchar2,
223 arg_9 in varchar2,
224 arg_10 in varchar2,
225 arg_11 in varchar2,
226 arg_12 in varchar2,
227 arg_13 in varchar2,
228 arg_14 in varchar2,
229 arg_15 in varchar2,
230 arg_16 in varchar2,
231 arg_17 in varchar2,
232 arg_18 in varchar2,
233 arg_19 in varchar2,
234 arg_20 in varchar2) return number is
235
236 return_pipe varchar2(30) := dbms_pipe.unique_session_name;
237 argtotal number;
238 argslen_err exception;
239 tmpstr varchar2(480);
240 submit_time date;
241 remaining_time number;
242 status number;
243 return_val varchar2(480);
244 error exception;
245 return_request_id number;
246 counter1 number;
247 counter2 number;
248
249 begin
250
251 dbms_pipe.reset_buffer;
252
253 -- Pack message header
254 dbms_pipe.pack_message (send_type);
255 dbms_pipe.pack_message (expiration_time);
256 dbms_pipe.pack_message (request_id);
257 dbms_pipe.pack_message (return_pipe);
258 dbms_pipe.pack_message (nls_lang);
259 dbms_pipe.pack_message (nls_num_chars);
260 dbms_pipe.pack_message (nls_date_lang);
261 dbms_pipe.pack_message (secgrpid);
262 dbms_pipe.pack_message (FND_CONC_GLOBAL.OPS_INST_NUM);
263 dbms_pipe.pack_message (enable_trace_flag);
264 dbms_pipe.pack_message (fnd_global.user_id);
265 dbms_pipe.pack_message (fnd_global.resp_appl_id);
266 dbms_pipe.pack_message (fnd_global.resp_id);
267 dbms_pipe.pack_message (fnd_global.login_id);
268 dbms_pipe.pack_message (application);
269 dbms_pipe.pack_message (program);
270 dbms_pipe.pack_message (org_type);
271 dbms_pipe.pack_message (org_id);
272
273 -- Pack arguments
274 argtotal := 0;
275
276 if (arg_1 = CHR(0)) then goto end_pack; end if;
277 tmpstr := substr(arg_1, 1, fnd_transaction.ARGMAX);
278 argtotal := argtotal + length(tmpstr);
279 if argtotal > fnd_transaction.ARGSTOTAL then
280 raise argslen_err;
281 end if;
282 dbms_pipe.pack_message (tmpstr);
283 if (arg_2 = CHR(0)) then goto end_pack; end if;
284 tmpstr := substr(arg_2, 1, fnd_transaction.ARGMAX);
285 argtotal := argtotal + length(tmpstr);
286 if argtotal > fnd_transaction.ARGSTOTAL then
287 raise argslen_err;
288 end if;
289 dbms_pipe.pack_message (tmpstr);
290 if (arg_3 = CHR(0)) then goto end_pack; end if;
291 tmpstr := substr(arg_3, 1, fnd_transaction.ARGMAX);
292 argtotal := argtotal + length(tmpstr);
293 if argtotal > fnd_transaction.ARGSTOTAL then
294 raise argslen_err;
295 end if;
296 dbms_pipe.pack_message (tmpstr);
297 if (arg_4 = CHR(0)) then goto end_pack; end if;
298 tmpstr := substr(arg_4, 1, fnd_transaction.ARGMAX);
299 argtotal := argtotal + length(tmpstr);
300 if argtotal > fnd_transaction.ARGSTOTAL then
301 raise argslen_err;
302 end if;
303 dbms_pipe.pack_message (tmpstr);
304 if (arg_5 = CHR(0)) then goto end_pack; end if;
305 tmpstr := substr(arg_5, 1, fnd_transaction.ARGMAX);
306 argtotal := argtotal + length(tmpstr);
307 if argtotal > fnd_transaction.ARGSTOTAL then
308 raise argslen_err;
309 end if;
310 dbms_pipe.pack_message (tmpstr);
311 if (arg_6 = CHR(0)) then goto end_pack; end if;
312 tmpstr := substr(arg_6, 1, fnd_transaction.ARGMAX);
313 argtotal := argtotal + length(tmpstr);
314 if argtotal > fnd_transaction.ARGSTOTAL then
315 raise argslen_err;
316 end if;
317 dbms_pipe.pack_message (tmpstr);
318 if (arg_7 = CHR(0)) then goto end_pack; end if;
319 tmpstr := substr(arg_7, 1, fnd_transaction.ARGMAX);
320 argtotal := argtotal + length(tmpstr);
321 if argtotal > fnd_transaction.ARGSTOTAL then
322 raise argslen_err;
323 end if;
324 dbms_pipe.pack_message (tmpstr);
325 if (arg_8 = CHR(0)) then goto end_pack; end if;
326 tmpstr := substr(arg_8, 1, fnd_transaction.ARGMAX);
330 end if;
327 argtotal := argtotal + length(tmpstr);
328 if argtotal > fnd_transaction.ARGSTOTAL then
329 raise argslen_err;
331 dbms_pipe.pack_message (tmpstr);
332 if (arg_9 = CHR(0)) then goto end_pack; end if;
333 tmpstr := substr(arg_9, 1, fnd_transaction.ARGMAX);
334 argtotal := argtotal + length(tmpstr);
335 if argtotal > fnd_transaction.ARGSTOTAL then
336 raise argslen_err;
337 end if;
338 dbms_pipe.pack_message (tmpstr);
339 if (arg_10 = CHR(0)) then goto end_pack; end if;
340 tmpstr := substr(arg_10, 1, fnd_transaction.ARGMAX);
341 argtotal := argtotal + length(tmpstr);
342 if argtotal > fnd_transaction.ARGSTOTAL then
343 raise argslen_err;
344 end if;
345 dbms_pipe.pack_message (tmpstr);
346 if (arg_11 = CHR(0)) then goto end_pack; end if;
347 tmpstr := substr(arg_11, 1, fnd_transaction.ARGMAX);
348 argtotal := argtotal + length(tmpstr);
349 if argtotal > fnd_transaction.ARGSTOTAL then
350 raise argslen_err;
351 end if;
352 dbms_pipe.pack_message (tmpstr);
353 if (arg_12 = CHR(0)) then goto end_pack; end if;
354 tmpstr := substr(arg_12, 1, fnd_transaction.ARGMAX);
355 argtotal := argtotal + length(tmpstr);
356 if argtotal > fnd_transaction.ARGSTOTAL then
357 raise argslen_err;
358 end if;
359 dbms_pipe.pack_message (tmpstr);
360 if (arg_13 = CHR(0)) then goto end_pack; end if;
361 tmpstr := substr(arg_13, 1, fnd_transaction.ARGMAX);
362 argtotal := argtotal + length(tmpstr);
363 if argtotal > fnd_transaction.ARGSTOTAL then
364 raise argslen_err;
365 end if;
366 dbms_pipe.pack_message (tmpstr);
367 if (arg_14 = CHR(0)) then goto end_pack; end if;
368 tmpstr := substr(arg_14, 1, fnd_transaction.ARGMAX);
369 argtotal := argtotal + length(tmpstr);
370 if argtotal > fnd_transaction.ARGSTOTAL then
371 raise argslen_err;
372 end if;
373 dbms_pipe.pack_message (tmpstr);
374 if (arg_15 = CHR(0)) then goto end_pack; end if;
375 tmpstr := substr(arg_15, 1, fnd_transaction.ARGMAX);
376 argtotal := argtotal + length(tmpstr);
377 if argtotal > fnd_transaction.ARGSTOTAL then
378 raise argslen_err;
379 end if;
380 dbms_pipe.pack_message (tmpstr);
381 if (arg_16 = CHR(0)) then goto end_pack; end if;
382 tmpstr := substr(arg_16, 1, fnd_transaction.ARGMAX);
383 argtotal := argtotal + length(tmpstr);
384 if argtotal > fnd_transaction.ARGSTOTAL then
385 raise argslen_err;
386 end if;
387 dbms_pipe.pack_message (tmpstr);
388 if (arg_17 = CHR(0)) then goto end_pack; end if;
389 tmpstr := substr(arg_17, 1, fnd_transaction.ARGMAX);
390 argtotal := argtotal + length(tmpstr);
391 if argtotal > fnd_transaction.ARGSTOTAL then
392 raise argslen_err;
393 end if;
394 dbms_pipe.pack_message (tmpstr);
395 if (arg_18 = CHR(0)) then goto end_pack; end if;
396 tmpstr := substr(arg_18, 1, fnd_transaction.ARGMAX);
397 argtotal := argtotal + length(tmpstr);
398 if argtotal > fnd_transaction.ARGSTOTAL then
399 raise argslen_err;
400 end if;
401 dbms_pipe.pack_message (tmpstr);
402 if (arg_19 = CHR(0)) then goto end_pack; end if;
403 tmpstr := substr(arg_19, 1, fnd_transaction.ARGMAX);
404 argtotal := argtotal + length(tmpstr);
405 if argtotal > fnd_transaction.ARGSTOTAL then
406 raise argslen_err;
407 end if;
408 dbms_pipe.pack_message (tmpstr);
409 if (arg_20 = CHR(0)) then goto end_pack; end if;
410 tmpstr := substr(arg_20, 1, fnd_transaction.ARGMAX);
411 argtotal := argtotal + length(tmpstr);
412 if argtotal > fnd_transaction.ARGSTOTAL then
413 raise argslen_err;
414 end if;
415 dbms_pipe.pack_message (tmpstr);
416
417 <<end_pack>>
418
419
420 -- Set time stamps
421 submit_time := sysdate;
422 remaining_time := timeout;
423
424 -- Send the message. Exit on timeout or error.
425 status := dbms_pipe.send_message(tm_pipe, remaining_time);
426 if (status = 1) then
427 fnd_transaction.debug_info('fnd_trn_pipe.send_message', 'send_message timeout', NULL);
428
429 /* mark timeout event (4) */
430 fnd_transaction.post_tm_event(4, application, program, -1, timeout, tm_pipe );
431
432 return fnd_transaction.E_TIMEOUT;
433 elsif (status <> 0) then
434 fnd_transaction.debug_info('fnd_trn_pipe.send_message', 'send_message returned:', to_char(status));
435 raise error;
436 end if;
437
438 -- Loop until timeout or we recieve a message on the return pipe
439 -- with a matching request ID. Throw out all other received messages.
440 loop
441 -- calculate the remaining timeout
442 remaining_time := remaining_time - ((sysdate-submit_time)*fnd_transaction.SEC_PER_DAY);
443
444 -- 906219 - added call to reset_buffer here to clear the buffer
445 -- before receiving the message.
446 -- Leftover data in the buffer was causing messages to come out incorrect.
447 -- Also added calls in FNDCP_TMSRV
448 dbms_pipe.reset_buffer;
449
450 -- Wait on return pipe. Exit on timoeout or error
451 status := dbms_pipe.receive_message(return_pipe, remaining_time);
452 if (status = 1) then
453 fnd_transaction.debug_info('fnd_trn_pipe.send_message', 'receive_message timeout', NULL, 'U');
454
455 /* mark timeout event (4) */
456 fnd_transaction.post_tm_event(4, application, program, -1, timeout, tm_pipe);
457
458 return fnd_transaction.E_TIMEOUT;
459 elsif (status <> 0) then
463
460 fnd_transaction.debug_info('fnd_trn_pipe.send_message', 'receive_message returned:', to_char(status), 'U');
461 raise error;
462 end if;
464 -- Exit loop only if we got the proper response
465 if (dbms_pipe.next_item_type = 9) then
466 dbms_pipe.unpack_message(tmpstr); -- Discard return type
467 if (dbms_pipe.next_item_type = 6) then
468 dbms_pipe.unpack_message(return_request_id);
469 exit when (request_id = return_request_id);
470 end if;
471 end if;
472 end loop;
473
474 -- Unpack return arguments
475 dbms_pipe.unpack_message(outcome);
476 dbms_pipe.unpack_message(message);
477
478 -- Populate return value table
479 for counter in 1..21 loop
480 counter1 := counter;
481 exit when dbms_pipe.next_item_type <> 9;
482 dbms_pipe.unpack_message(return_val);
483 fnd_transaction.return_values(counter) := return_val;
484 end loop;
485
486 -- Null out the unused table elements
487 if (counter1 < 21) then
488 for counter2 in counter1..20 loop
489 fnd_transaction.return_values(counter2) := null;
490 end loop;
491 end if;
492 if (debug_flag) then
493 for counter1 in 1..20 loop
494 fnd_transaction.debug_info('fnd_trn_pipe.send_message',
495 'Return table entry #'||to_char(counter1),
496 fnd_transaction.return_values(counter1), 'U');
497 end loop;
498 end if;
499
500 return fnd_transaction.E_SUCCESS;
501
502 exception
503 when error then
504 return fnd_transaction.E_OTHER;
505 when argslen_err then
506 fnd_transaction.debug_info('fnd_trn_pipe.send_message', 'Total size of args too long', to_char(argtotal));
507 return fnd_transaction.E_ARGSIZE;
508 when others then
509 fnd_message.set_name ('FND', 'SQL-Generic error');
510 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
511 fnd_message.set_token ('REASON', sqlerrm, FALSE);
512 fnd_message.set_token (
513 'ROUTINE', 'FND_TRN_PIPE.SEND_MESSAGE', FALSE);
514 fnd_transaction.debug_info('fnd_trn_pipe.send_message', 'Caught exception', sqlerrm);
515 return fnd_transaction.E_OTHER;
516
517
518 end send_message;
519
520 end fnd_transaction_pipe;