[Home] [Help]
PACKAGE BODY: APPS.FND_TRANSACTION_QUEUE
Source
1 package body fnd_transaction_queue as
2 /* $Header: AFCPTRQB.pls 120.1 2005/09/17 02:03:29 pferguso noship $ */
3
4
5 --
6 -- Constants
7 --
8
9 -- Name of the TM AQ
10 QUEUE_NAME constant VARCHAR2(30) := 'FND_CP_TM_AQ';
11
12 -- Name of the TM Return AQ
13 RETURN_QUEUE_NAME constant VARCHAR2(30) := 'FND_CP_TM_RET_AQ';
14
15 -- Prefix added to all recipient and consumer names
16 TMPREFIX constant VARCHAR2(3) := 'TM';
17
18 -- Consumer name for all managers
19 TMQID constant varchar2(30) := 'TMSRV';
20
21 -- Largest increment to wait for dequeue
22 TIMEOUT_INCREMENT constant number := 5;
23
24
25 --
26 -- Private Variables
27 --
28 processor_id varchar2(32);
29 Q_Name varchar2(64) := null;
30 RetQ_Name varchar2(64) := null;
31
32
33
34 --
35 -- get_manager
36 -- Purpose
37 -- Find an available manager process to run the transaction program
38 -- Arguments
39 -- IN:
40 -- application - program application short name
41 -- program - program short name
42 -- timeout - timeout in seconds
43 -- Returns
44 -- E_SUCCESS on success.
45 -- E_NOMGR if no manager is available
46 -- E_OTHER on failure.
47 --
48 -- Notes
49 -- If successful, processor_id will be set
50 --
51 function get_manager(application in varchar2,
52 program in varchar2,
53 timeout in number) return number is
54
55 status number;
56 all_busy exception;
57 resp_appl_id number;
58 resp_id number;
59
60 cursor tm(appl_short_name varchar2, prog_short_name varchar2,
61 resp_appl_id number, resp_id number) is
62 select /*+ ORDERED USE_NL (fa fcp fr fcpp fcq fcpr)
63 INDEX (fcq,FND_CONCURRENT_QUEUES_N1)
64 INDEX (fcpp,FND_CONC_PROCESSOR_PROGRAMS_U2) */
65 fcq.processor_application_id || '.' || fcq.concurrent_processor_id
66 from fnd_application fa,
67 fnd_concurrent_programs fcp,
68 fnd_conc_processor_programs fcpp,
69 fnd_responsibility fr,
70 fnd_concurrent_queues fcq,
71 fnd_concurrent_processes fcpr
72 where fcq.processor_application_id = fcpp.processor_application_id
73 and fcq.concurrent_processor_id = fcpp.concurrent_processor_id
74 and fcpp.concurrent_program_id = fcp.concurrent_program_id
75 and fcpp.program_application_id = fcp.application_id
76 and fcp.application_id = fa.application_id
77 and fa.application_short_name = appl_short_name
78 and fcp.concurrent_program_name = prog_short_name
79 and fr.responsibility_id = resp_id
80 and fr.application_id = resp_appl_id
81 and fr.data_group_id = fcq.data_group_id
82 and fcq.manager_type = '3'
83 and fcpr.concurrent_queue_id = fcq.concurrent_queue_id
84 and fcpr.queue_application_id = fcq.application_id
85 and fcpr.process_status_code = 'A';
86
87 begin
88 resp_appl_id := fnd_global.resp_appl_id;
89 resp_id := fnd_global.resp_id;
90
91 if (fnd_transaction.debug_flag) then
92 fnd_transaction.debug_info('fnd_trn_queue.get_manager',
93 'Searching for manager to run:',
94 application || ':' || program);
95 fnd_transaction.debug_info('fnd_trn_queue.get_manager',
96 'RESP_APPL_ID:RESP_ID',
97 to_char(resp_appl_id) || ':' || to_char(resp_id));
98 end if;
99
100 open tm(application, program, resp_appl_id, resp_id);
101
102 -- Fetch ID of first manager
103 fetch tm into processor_id;
104 -- If cursor is empty, then no manager is defined for request.
105 if (tm%rowcount = 0 ) then
106 fnd_transaction.debug_info('fnd_trn_queue.get_manager', 'No manager available', NULL);
107
108 fnd_transaction.post_tm_event(1, application, program, -1);
109
110 fnd_message.set_name('FND', 'CONC-TM-No manager defined');
111 fnd_message.set_token('APPLICATION', application);
112 fnd_message.set_token('PROGRAM', program);
113 fnd_message.set_token('RESP_ID', resp_id);
114 fnd_message.set_token('RESP_APPL_ID', resp_appl_id);
115 close tm;
116 return fnd_transaction.E_OTHER;
117 end if;
118
119 if (fnd_transaction.debug_flag) then
120 fnd_transaction.debug_info('fnd_trn_queue.get_manager',
121 'Got available TM process',
122 processor_id);
123 end if;
124
125 close tm;
126 return fnd_transaction.E_SUCCESS;
127
128 exception
129 when others then
130 if tm%isopen then
131 close tm;
132 end if;
133 fnd_message.set_name ('FND', 'SQL-Generic error');
134 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
135 fnd_message.set_token ('REASON', sqlerrm, FALSE);
136 fnd_message.set_token ('ROUTINE', 'FND_TRANSACTION_QUEUE.GET_MANAGER', FALSE);
137 fnd_transaction.debug_info('fnd_trn_queue.get_manager', 'Caught exception', sqlerrm);
138
139 return fnd_transaction.E_OTHER;
140
141 end get_manager;
142
143
144
145 --
146 -- send_message
147 -- Purpose
148 -- Puts the transaction message on the queue and waits for the return message
149 -- Returns
150 -- E_SUCCESS on success
151 -- E_TIMEOUT if return message times out
152 -- E_OTHER on failure
153 --
154 --
155 function send_message( timeout in number,
156 send_type in varchar2,
157 expiration_time in date,
158 request_id in number,
159 nls_lang in varchar2,
160 nls_num_chars in varchar2,
161 nls_date_lang in varchar2,
162 secgrpid in number,
163 enable_trace_flag in varchar2,
164 application in varchar2,
165 program in varchar2,
166 org_type in varchar2,
167 org_id in number,
168 outcome in out nocopy varchar2,
169 message in out nocopy varchar2,
170 arg_1 in varchar2,
171 arg_2 in varchar2,
172 arg_3 in varchar2,
173 arg_4 in varchar2,
174 arg_5 in varchar2,
175 arg_6 in varchar2,
176 arg_7 in varchar2,
177 arg_8 in varchar2,
178 arg_9 in varchar2,
179 arg_10 in varchar2,
180 arg_11 in varchar2,
181 arg_12 in varchar2,
182 arg_13 in varchar2,
183 arg_14 in varchar2,
184 arg_15 in varchar2,
185 arg_16 in varchar2,
186 arg_17 in varchar2,
187 arg_18 in varchar2,
188 arg_19 in varchar2,
189 arg_20 in varchar2) return number is
190
191
192 status varchar2(1);
193 industry varchar2(1);
194 retval number;
195 schema varchar2(30);
196 dq_opts DBMS_AQ.DEQUEUE_OPTIONS_T;
197 queue_timeout exception;
198 enq_opts DBMS_AQ.enqueue_options_t;
199 msg_props DBMS_AQ.message_properties_t;
200 msg system.FND_CP_TM_AQ_PAYLOAD;
201 msg_id raw(16);
202 time_left number;
203 end_time date;
204 retval number;
205 debug_flag boolean := fnd_transaction.debug_flag;
206 r boolean;
207 argmax number := fnd_transaction.ARGMAX;
208
209 pragma exception_init(queue_timeout, -25228);
210
211 begin
212
213 r := fnd_installation.get_app_info('FND', status, industry, schema);
214
215 Q_Name := schema || '.' || QUEUE_NAME;
216 RetQ_Name := schema || '.' || RETURN_QUEUE_NAME;
217
218 -- Create the transaction message
219 msg := system.FND_CP_TM_AQ_PAYLOAD(request_id,
220 send_type,
221 expiration_time,
222 nls_lang,
223 nls_num_chars,
224 nls_date_lang,
225 secgrpid,
226 enable_trace_flag,
227 fnd_global.user_id,
228 fnd_global.resp_appl_id,
229 fnd_global.resp_id,
230 fnd_global.login_id,
231 application,
232 program,
233 NULL,
234 NULL,
235 org_type,
236 org_id,
237 substr(arg_1, 1, argmax),
238 substr(arg_2, 1, argmax),
239 substr(arg_3, 1, argmax),
240 substr(arg_4, 1, argmax),
241 substr(arg_5, 1, argmax),
242 substr(arg_6, 1, argmax),
243 substr(arg_7, 1, argmax),
244 substr(arg_8, 1, argmax),
245 substr(arg_9, 1, argmax),
246 substr(arg_10, 1, argmax),
247 substr(arg_11, 1, argmax),
248 substr(arg_12, 1, argmax),
249 substr(arg_13, 1, argmax),
250 substr(arg_14, 1, argmax),
251 substr(arg_15, 1, argmax),
252 substr(arg_16, 1, argmax),
253 substr(arg_17, 1, argmax),
254 substr(arg_18, 1, argmax),
255 substr(arg_19, 1, argmax),
256 substr(arg_20, 1, argmax)
257 );
258
259 enq_opts.visibility := DBMS_AQ.IMMEDIATE;
260 enq_opts.sequence_deviation := NULL;
261 msg_props.delay := DBMS_AQ.NO_DELAY;
262
263 msg_props.sender_id := sys.aq$_agent(TMPREFIX || request_id, NULL, NULL);
264
265 msg_props.recipient_list(0) := sys.aq$_agent(TMQID, NULL, NULL);
266
267 msg_props.correlation := processor_id;
268
269
270 -- Queue the transaction message
271 DBMS_AQ.Enqueue( queue_name => Q_Name,
272 enqueue_options => enq_opts,
273 message_properties => msg_props,
274 Payload => msg,
275 msgid => msg_id);
276
277
278 if (debug_flag) then
279 fnd_transaction.debug_info('fnd_trn_queue.send_message', 'Waiting for return message', request_id, 'U');
280 end if;
281
282 msg := system.FND_CP_TM_AQ_PAYLOAD(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
283 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
284 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
285
286 dq_opts.DEQUEUE_MODE := DBMS_AQ.REMOVE;
287 dq_opts.NAVIGATION := DBMS_AQ.FIRST_MESSAGE;
288 dq_opts.VISIBILITY := DBMS_AQ.IMMEDIATE;
289 dq_opts.MSGID := NULL;
290
291
292
293 -- Use the request id as our consumer name.
294 -- The TM will address the return message to this id
295 dq_opts.consumer_name := TMPREFIX || request_id;
296
297 time_left := timeout;
298 end_time := sysdate + (timeout * fnd_transaction.DAY_PER_SEC);
299
300
301 -- Loop until the return message arrives or the timeout expires,
302 -- but do not wait on any single dequeue call more than TIMEOUT_INCREMENT seconds
303 loop
304 if time_left > TIMEOUT_INCREMENT then
305 dq_opts.WAIT := TIMEOUT_INCREMENT;
306 else
307 dq_opts.WAIT := time_left;
308 end if;
309
310 begin
311
312 -- Listen for the return message
313 DBMS_AQ.DEQUEUE(QUEUE_NAME => RetQ_Name,
314 DEQUEUE_OPTIONS => dq_opts,
315 MESSAGE_PROPERTIES => msg_props,
316 PAYLOAD => msg,
317 MSGID => msg_id);
318 if (debug_flag) then
319 fnd_transaction.debug_info('fnd_trn_queue.send_message', 'Got return message', request_id, 'U');
320 end if;
321 exit;
322
323 exception
324 when queue_timeout then
325 if (debug_flag) then
326 fnd_transaction.debug_info('fnd_trn_queue.send_message', 'Dequeue timeout', request_id, 'U');
327 end if;
328
329 if sysdate >= end_time then
330
331 -- Timed out waiting for return message
332 begin
333 /* remove our message from the queue */
334 dq_opts.MSGID := msg_id;
335 dq_opts.WAIT := DBMS_AQ.NO_WAIT;
336 dq_opts.consumer_name := TMQID;
337 DBMS_AQ.DEQUEUE(QUEUE_NAME => Q_Name,
338 DEQUEUE_OPTIONS => dq_opts,
339 MESSAGE_PROPERTIES => msg_props,
340 PAYLOAD => msg,
341 MSGID => msg_id);
342 exception
343 when others then
344 fnd_transaction.debug_info('fnd_trn_queue.send_message',
345 'Unable to remove timed-out message', sqlerrm, 'U');
346 end;
347
348 if (debug_flag) then
349 fnd_transaction.debug_info('fnd_trn_queue.send_message', 'Return message timed out', request_id, 'U');
350 end if;
351 fnd_transaction.post_tm_event(4, application, program, -1, timeout);
352 return fnd_transaction.E_TIMEOUT;
353
354 end if;
355
356 -- Time is not up yet, keep waiting
357 time_left := (end_time - sysdate) * fnd_transaction.SEC_PER_DAY;
358 end;
359
360 end loop;
361
362 outcome := msg.outcome;
363 message := msg.message;
364
365 if (debug_flag) then
366 fnd_transaction.debug_info('fnd_trn_queue.send_message', 'Outcome', outcome, 'U');
367 fnd_transaction.debug_info('fnd_trn_queue.send_message', 'Message', message, 'U');
368 end if;
369
370 fnd_transaction.return_values(1) := msg.arg1;
371 fnd_transaction.return_values(2) := msg.arg2;
372 fnd_transaction.return_values(3) := msg.arg3;
373 fnd_transaction.return_values(4) := msg.arg4;
374 fnd_transaction.return_values(5) := msg.arg5;
375 fnd_transaction.return_values(6) := msg.arg6;
376 fnd_transaction.return_values(7) := msg.arg7;
377 fnd_transaction.return_values(8) := msg.arg8;
378 fnd_transaction.return_values(9) := msg.arg9;
379 fnd_transaction.return_values(10) := msg.arg10;
380 fnd_transaction.return_values(11) := msg.arg11;
381 fnd_transaction.return_values(12) := msg.arg12;
382 fnd_transaction.return_values(13) := msg.arg13;
383 fnd_transaction.return_values(14) := msg.arg14;
384 fnd_transaction.return_values(15) := msg.arg15;
385 fnd_transaction.return_values(16) := msg.arg16;
386 fnd_transaction.return_values(17) := msg.arg17;
387 fnd_transaction.return_values(18) := msg.arg18;
388 fnd_transaction.return_values(19) := msg.arg19;
389 fnd_transaction.return_values(20) := msg.arg20;
390
391 if (debug_flag) then
392 for counter1 in 1..20 loop
393 fnd_transaction.debug_info('fnd_trn_queue.send_message',
394 'Return table entry #'||to_char(counter1),
395 fnd_transaction.return_values(counter1), 'U');
396 end loop;
397 end if;
398
399 if (debug_flag) then
400 fnd_transaction.debug_info('fnd_trn_queue.send_message', 'Transaction complete', '', 'U');
401 end if;
402
403 return fnd_transaction.E_SUCCESS;
404
405 exception
406 when OTHERS then
407 fnd_message.set_name ('FND', 'SQL-Generic error');
408 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
409 fnd_message.set_token ('REASON', sqlerrm, FALSE);
410 fnd_message.set_token ('ROUTINE', 'FND_TRANSACTION_QUEUE.SEND_MESSAGE', FALSE);
411 fnd_transaction.debug_info('fnd_trn_queue.send_message', 'Caught exception', sqlerrm);
412 return fnd_transaction.E_OTHER;
413
414 end send_message;
415
416
417 end fnd_transaction_queue;