[Home] [Help]
PACKAGE BODY: APPS.FND_CP_TMSRV_QUEUE
Source
1 package body FND_CP_TMSRV_QUEUE as
2 /* $Header: AFCPTMQB.pls 120.1 2005/09/17 02:01:13 pferguso noship $ */
3
4
5
6 --
7 -- Constants
8 --
9
10 -- Name of the TM AQ
11 QUEUE_NAME constant VARCHAR2(30) := 'FND_CP_TM_AQ';
12
13 -- Name of the TM Return AQ
14 RETURN_QUEUE_NAME constant VARCHAR2(30) := 'FND_CP_TM_RET_AQ';
15
16 -- Prefix added to all recipient and consumer names
17 TMPREFIX constant VARCHAR2(3) := 'TM';
18
19 -- Largest increment to wait for dequeue
20 TIMEOUT_INCREMENT constant number := 10;
21
22 -- Consumer name for all managers
23 TMQID constant varchar2(30) := 'TMSRV';
24
25
26
27 --
28 -- Private variables
29 --
30
31 P_DEBUG varchar2(1) := FNDCP_TMSRV.DBG_OFF;
32 Q_Name varchar2(64) := null;
33 RetQ_Name varchar2(64) := null;
34 P_SENDER_ID varchar2(30) := null;
35 P_SENDER_AGENT sys.aq$_agent;
36 P_ENQ_OPTS DBMS_AQ.enqueue_options_t;
37 P_DEQ_OPTS DBMS_AQ.dequeue_options_t;
38 P_CORRELATION_ID varchar2(32);
39
40
41 --
42 -- Queue initialization
43 --
44
45 procedure initialize (e_code in out nocopy number,
46 qid in number,
47 pid in number) is
48
49 status varchar2(1);
50 industry varchar2(1);
51 schema varchar2(30);
52 r boolean;
53
54 pragma AUTONOMOUS_TRANSACTION;
55 begin
56
57
58 P_SENDER_ID := TMPREFIX || pid;
59 P_SENDER_AGENT := sys.aq$_agent(P_SENDER_ID, NULL, NULL);
60
61 P_ENQ_OPTS.visibility := DBMS_AQ.IMMEDIATE;
62 P_ENQ_OPTS.sequence_deviation := NULL;
63
64 select PROCESSOR_APPLICATION_ID || '.' || CONCURRENT_PROCESSOR_ID
65 into P_CORRELATION_ID
66 from fnd_concurrent_queues
67 where concurrent_queue_id = qid;
68
69 P_DEQ_OPTS.dequeue_mode := DBMS_AQ.REMOVE;
70 P_DEQ_OPTS.navigation := DBMS_AQ.FIRST_MESSAGE;
71 P_DEQ_OPTS.visibility := DBMS_AQ.IMMEDIATE;
72 P_DEQ_OPTS.consumer_name := TMQID;
73 P_DEQ_OPTS.correlation := P_CORRELATION_ID;
74
75 r := fnd_installation.get_app_info('FND', status, industry, schema);
76
77 Q_Name := schema || '.' || QUEUE_NAME;
78 RetQ_Name := schema || '.' || RETURN_QUEUE_NAME;
79
80 e_code := FNDCP_TMSRV.E_SUCCESS;
81
82 commit;
83
84 end initialize;
85
86
87
88 procedure set_debug(dbgtype in varchar2) is
89 begin
90 P_DEBUG := dbgtype;
91 FNDCP_TMSRV.P_DEBUG := dbgtype;
92 end set_debug;
93
94
95
96
97 procedure read_message (e_code in out nocopy number,
98 timeout in number,
99 pktyp in out nocopy varchar2,
100 enddate in out nocopy varchar2,
101 reqid in out nocopy number,
102 return_id in out nocopy varchar2,
103 nlslang in out nocopy varchar2,
104 nls_num_chars in out nocopy varchar2,
105 nls_date_lang in out nocopy varchar2,
106 secgrpid in out nocopy number,
107 usrid in out nocopy number,
108 rspapid in out nocopy number,
109 rspid in out nocopy number,
110 logid in out nocopy number,
111 apsname in out nocopy varchar2,
112 program in out nocopy varchar2,
113 numargs in out nocopy number,
114 org_type in out nocopy varchar2,
115 org_id in out nocopy number,
116 arg_1 in out nocopy varchar2,
117 arg_2 in out nocopy varchar2,
118 arg_3 in out nocopy varchar2,
119 arg_4 in out nocopy varchar2,
120 arg_5 in out nocopy varchar2,
121 arg_6 in out nocopy varchar2,
122 arg_7 in out nocopy varchar2,
123 arg_8 in out nocopy varchar2,
124 arg_9 in out nocopy varchar2,
125 arg_10 in out nocopy varchar2,
126 arg_11 in out nocopy varchar2,
127 arg_12 in out nocopy varchar2,
128 arg_13 in out nocopy varchar2,
129 arg_14 in out nocopy varchar2,
130 arg_15 in out nocopy varchar2,
131 arg_16 in out nocopy varchar2,
132 arg_17 in out nocopy varchar2,
133 arg_18 in out nocopy varchar2,
134 arg_19 in out nocopy varchar2,
135 arg_20 in out nocopy varchar2) is
136
137 payload system.FND_CP_TM_AQ_PAYLOAD;
138 msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
139 msgid raw(16);
140 queue_timeout exception;
141 enable_trace varchar2(255);
142 sql_stmt varchar2(255);
143
144
145 pragma exception_init(queue_timeout, -25228);
146 begin
147
148 payload := system.FND_CP_TM_AQ_PAYLOAD(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
149 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
150 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
151
152
153 P_DEQ_OPTS.MSGID := NULL;
154 P_DEQ_OPTS.WAIT := timeout;
155
156 begin
157
158 DBMS_AQ.DEQUEUE(QUEUE_NAME => Q_Name,
159 DEQUEUE_OPTIONS => P_DEQ_OPTS,
160 MESSAGE_PROPERTIES => msg_props,
161 PAYLOAD => payload,
162 MSGID => msgid);
163
164 e_code := FNDCP_TMSRV.E_SUCCESS;
165
166 exception
167 when queue_timeout then
168 e_code := FNDCP_TMSRV.E_TIMEOUT;
169 return;
170
171 when others then
172 fndcp_tmsrv.debug_info('tmsrv_queue.read_message', 'Caught exception in DQ', sqlerrm);
173 return;
174 end;
175
176
177 pktyp := payload.msgtype;
178 if (pktyp not in (FNDCP_TMSRV.PK_TRN, FNDCP_TMSRV.PK_TRN_D1, FNDCP_TMSRV.PK_TRN_D2)) then
179 e_code := FNDCP_TMSRV.E_OTHER;
180 return;
181 end if;
182
183 set_debug(pktyp);
184
185 enddate := payload.enddate;
186 reqid := payload.reqid;
187 nlslang := payload.nlslang;
188 nls_num_chars := payload.nls_num_chars;
189 nls_date_lang := payload.nls_date_lang;
190 secgrpid := payload.secgrpid;
191 enable_trace := payload.enable_trace;
192 usrid := payload.userid;
193 rspapid := payload.rspapid;
194 rspid := payload.rspid;
195 logid := payload.logid;
196 apsname := payload.apsname;
197 program := payload.program;
198 org_type := payload.org_type;
199 org_id := payload.org_id;
200
201 numargs := 0;
202 arg_1 := payload.arg1;
203 if arg_1 <> chr(0) then numargs := numargs + 1; end if;
204 arg_2 := payload.arg2;
205 if arg_2 <> chr(0) then numargs := numargs + 1; end if;
206 arg_3 := payload.arg3;
207 if arg_3 <> chr(0) then numargs := numargs + 1; end if;
208 arg_4 := payload.arg4;
209 if arg_4 <> chr(0) then numargs := numargs + 1; end if;
210 arg_5 := payload.arg5;
211 if arg_5 <> chr(0) then numargs := numargs + 1; end if;
212 arg_6 := payload.arg6;
213 if arg_6 <> chr(0) then numargs := numargs + 1; end if;
214 arg_7 := payload.arg7;
215 if arg_7 <> chr(0) then numargs := numargs + 1; end if;
216 arg_8 := payload.arg8;
217 if arg_8 <> chr(0) then numargs := numargs + 1; end if;
218 arg_9 := payload.arg9;
219 if arg_9 <> chr(0) then numargs := numargs + 1; end if;
220 arg_10 := payload.arg10;
221 if arg_10 <> chr(0) then numargs := numargs + 1; end if;
222 arg_11 := payload.arg11;
223 if arg_11 <> chr(0) then numargs := numargs + 1; end if;
224 arg_12 := payload.arg12;
225 if arg_12 <> chr(0) then numargs := numargs + 1; end if;
226 arg_13 := payload.arg13;
227 if arg_13 <> chr(0) then numargs := numargs + 1; end if;
228 arg_14 := payload.arg14;
229 if arg_14 <> chr(0) then numargs := numargs + 1; end if;
230 arg_15 := payload.arg15;
231 if arg_15 <> chr(0) then numargs := numargs + 1; end if;
232 arg_16 := payload.arg16;
233 if arg_16 <> chr(0) then numargs := numargs + 1; end if;
234 arg_17 := payload.arg17;
235 if arg_17 <> chr(0) then numargs := numargs + 1; end if;
236 arg_18 := payload.arg18;
237 if arg_18 <> chr(0) then numargs := numargs + 1; end if;
238 arg_19 := payload.arg19;
239 if arg_19 <> chr(0) then numargs := numargs + 1; end if;
240 arg_20 := payload.arg20;
241 if arg_20 <> chr(0) then numargs := numargs + 1; end if;
242
243 if ( P_DEBUG <> FNDCP_TMSRV.DBG_OFF ) then
244 fndcp_tmsrv.debug_info('TMSRV_QUEUE.read_message',
245 'Unpacked request details', NULL, 'M');
246
247 end if;
248
249 sql_stmt := 'ALTER SESSION SET SQL_TRACE = '|| enable_trace;
250 EXECUTE IMMEDIATE sql_stmt ;
251
252 if ( P_DEBUG <> FNDCP_TMSRV.DBG_OFF ) then
253 fndcp_tmsrv.debug_info('TMSRV_QUEUE.read_message',
254 'SQL_TRACE:', enable_trace, 'M');
255
256 end if;
257
258
259 exception
260
261 when OTHERS then
262 fnd_message.set_name ('FND', 'SQL-Generic error');
263 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
264 fnd_message.set_token ('REASON', sqlerrm, FALSE);
265 fnd_message.set_token ('ROUTINE', 'TMSRV_QUEUE.READ_MESSAGE', FALSE);
266 fndcp_tmsrv.debug_info('tmsrv_queue.read_message', 'Caught exception', sqlerrm);
267 e_code := FNDCP_TMSRV.E_OTHER;
268 end read_message;
269
270
271
272
273 procedure write_message (e_code in out nocopy number,
274 return_id in varchar2,
275 pktyp in varchar2,
276 reqid in number,
277 outcome in varchar2,
278 message in varchar2) is
279
280 msg_props DBMS_AQ.message_properties_t;
281 msg_id raw(16);
282 msg system.FND_CP_TM_AQ_PAYLOAD;
283
284 begin
285
286 e_code := FNDCP_TMSRV.E_SUCCESS;
287
288 FNDCP_TMSRV.P_RETVALCOUNT := 0; -- Reset the return values table.
289
290 if ( P_DEBUG <> FNDCP_TMSRV.DBG_OFF ) then
291 fndcp_tmsrv.debug_info('TMSRV_QUEUE.write_message',
292 'Packing return message' ,
293 reqid, 'S');
294
295 end if;
296
297 msg := system.FND_CP_TM_AQ_PAYLOAD(reqid,
298 pktyp,
299 null,
300 null,
301 null,
302 null,
303 null,
304 null,
305 null,
306 null,
307 null,
308 null,
309 null,
310 null,
311 outcome,
312 message,
313 null,
314 null,
315 FNDCP_TMSRV.P_RETURN_VALS(1),
316 FNDCP_TMSRV.P_RETURN_VALS(2),
317 FNDCP_TMSRV.P_RETURN_VALS(3),
318 FNDCP_TMSRV.P_RETURN_VALS(4),
319 FNDCP_TMSRV.P_RETURN_VALS(5),
320 FNDCP_TMSRV.P_RETURN_VALS(6),
321 FNDCP_TMSRV.P_RETURN_VALS(7),
322 FNDCP_TMSRV.P_RETURN_VALS(8),
323 FNDCP_TMSRV.P_RETURN_VALS(9),
324 FNDCP_TMSRV.P_RETURN_VALS(10),
325 FNDCP_TMSRV.P_RETURN_VALS(11),
326 FNDCP_TMSRV.P_RETURN_VALS(12),
327 FNDCP_TMSRV.P_RETURN_VALS(13),
328 FNDCP_TMSRV.P_RETURN_VALS(14),
329 FNDCP_TMSRV.P_RETURN_VALS(15),
330 FNDCP_TMSRV.P_RETURN_VALS(16),
331 FNDCP_TMSRV.P_RETURN_VALS(17),
332 FNDCP_TMSRV.P_RETURN_VALS(18),
333 FNDCP_TMSRV.P_RETURN_VALS(19),
334 FNDCP_TMSRV.P_RETURN_VALS(20)
335 );
336
337 msg_props.delay := DBMS_AQ.NO_DELAY;
338
339 msg_props.sender_id := P_SENDER_AGENT;
340
341 msg_props.recipient_list(0) := sys.aq$_agent(TMPREFIX || reqid, NULL, NULL);
342
343 -- don't let the message stay on the queue forever, but don't make the queue monitor
344 -- work too hard...
345 msg_props.expiration := 600;
346
347 DBMS_AQ.Enqueue( queue_name => RetQ_Name,
348 enqueue_options => P_ENQ_OPTS,
349 message_properties => msg_props,
350 Payload => msg,
351 msgid => msg_id);
352
353 if ( P_DEBUG <> FNDCP_TMSRV.DBG_OFF ) then
354 fndcp_tmsrv.debug_info('TMSRV_QUEUE.write_message',
355 'Sent Message' ,
356 reqid, 'S');
357
358 end if;
359
360 -- Turn off debug.
361 set_debug(FNDCP_TMSRV.DBG_OFF);
362
363 -- Reset all the return values
364 for counter in 1..20 loop
365 FNDCP_TMSRV.P_RETURN_VALS(counter) := null;
366 end loop;
367
368 exception
369 when OTHERS then
370 e_code := FNDCP_TMSRV.E_OTHER;
371 fnd_message.set_name ('FND', 'SQL-Generic error');
372 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
373 fnd_message.set_token ('REASON', sqlerrm, FALSE);
374 fnd_message.set_token ('ROUTINE', 'TMSRV_QUEUE.COMPLETE_TRANS', FALSE);
375 fndcp_tmsrv.debug_info('tmsrv_queue.complete_trans', 'Caught exception', sqlerrm);
376 raise;
377
378 end write_message;
379
380
381
382 end fnd_cp_tmsrv_queue;