1 PACKAGE BODY fnd_cp_opp_req AS
2 /* $Header: AFCPOPRB.pls 120.15 2011/05/10 21:27:17 pferguso ship $ */
3
4 -- Default timeout values for waiting on a response to a request (seconds)
5 TIMEOUT1 constant number := 120;
6 TIMEOUT2 constant number := 300;
7
8
9 -- Default wait time when OPP service is still initializing
10 DEFAULT_SLEEP constant number := 30;
11
12 --
13 -- published_request
14 --
15 -- Given a request id, determine if this request has publishing actions
16 --
17 function published_request(reqid in number) return boolean is
18
19 prog_name varchar2(30) := null;
20 appl_name varchar2(30) := null;
21
22 begin
23
24 select fcp.concurrent_program_name, a.application_short_name
25 into prog_name, appl_name
26 from fnd_concurrent_requests fcr,
27 fnd_concurrent_programs fcp, fnd_application a
28 where fcr.request_id = reqid
29 and fcp.concurrent_program_id = fcr.concurrent_program_id
30 and fcp.application_id = fcr.program_application_id
31 and fcp.application_id = a.application_id
32 and rownum = 1;
33
34
35 if prog_name = 'FNDREPRINT' and appl_name = 'FND' then
36 return TRUE;
37 end if;
38
39
40 return fnd_conc_sswa.layout_enabled(appl_name, prog_name);
41
42 end;
43
44
45
46 -- ============================
47 -- OPP service procedures
48 -- ============================
49
50
51 --
52 -- update_actions_table
53 --
54 -- Used by the OPP service to update the FND_CONC_PP_ACTIONS table
55 -- The table is only updated if it has not been previously updated by another process
56 --
57 -- reqid - Concurrent request id
58 -- procid - Concurrent process id of the service. FND_CONC_PP_ACTIONS.PROCESSOR_ID will be updated
59 -- with this value for all pp actions for this request
60 -- success - Y if the table was updated, N if the table has already been updated.
61 --
62 procedure update_actions_table(reqid in number, procid in number,
63 success out NOCOPY varchar2) is
64
65 cnt number;
66 begin
67 select count(*)
68 into cnt
69 from fnd_conc_pp_actions
70 where concurrent_request_id = reqid
71 and processor_id is not null;
72
73 if cnt > 0 then
74 success := 'N';
75 return;
76 end if;
77
78
79 update fnd_conc_pp_actions
80 set processor_id = procid
81 where concurrent_request_id = reqid;
82
83 success := 'Y';
84
85 end;
86
87
88
89
90
91 -- =======================================
92 -- Request-Processing Manager procedures
93 -- =======================================
94
95
96 --
97 -- wait_for_reply
98 -- Wait for a reply message on the OPP AQ
99 --
100 -- cpid - Concurrent process id of the receiver
101 -- timeout - timeout in seconds
102 -- result - SUCCESS if success message received from OPP service
103 -- TIMEOUT if timeout occurred
104 -- ERROR if error occurred receiving message, errmsg will contain error message
105 -- FAILED if failure message received from OPP service. errmsg will contain reason.
106 -- errmsg - Reason for failure
107 --
108 procedure wait_for_reply(cpid in number,
109 reqid in number,
110 timeout in number,
111 result out NOCOPY varchar,
112 errmsg out NOCOPY varchar) is
113
114 flag varchar2(1);
115 msgtype number;
116 message varchar2(240);
117 params varchar2(2000);
118 sender varchar2(30);
119 msggroup varchar2(30);
120 end_time date;
121
122 begin
123
124 end_time := sysdate + (timeout / (24 * 60 * 60));
125
126 loop
127
128 fnd_cp_opp_ipc.get_message(cpid, flag, msgtype, msggroup, message, params, sender, timeout, reqid);
129
130 -- if flag = N then a timeout or exception occurred
131 if flag <> 'Y' then
132
133 -- timed out waiting
134 if flag = 'T' then
135 result := 'TIMEOUT';
136 else
137 result := 'ERROR';
138 errmsg := message;
139 end if;
140
141 return;
142 end if;
143
144 -- flag == 'Y', received a message
145 if message = 'SUCCESS' then
146
147 -- make sure the request id matches
148 if params = reqid then
149 result := 'SUCCESS';
150 return;
151 else
152 -- Yikes, the request id does not match, log an error message
153 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
154 fnd_message.set_name('FND','CONC-OPP MESSAGE MISMATCH');
155 fnd_message.set_token('REQID', reqid, FALSE);
156 fnd_message.set_token('MGRID', cpid, FALSE);
157 fnd_log.message(FND_LOG.LEVEL_ERROR,
158 'fnd.plsql.fnd_cp_opp_req.wait_for_reply', TRUE);
159 end if;
160
161 -- debug
162 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
163 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
164 'fnd.plsql.fnd_cp_opp_req.wait_for_reply',
165 'Message mismatch in wait_for_reply, expected ' || reqid || ' but got ' || params);
166
167 end if;
168 end if;
169
170 else
171 -- otherwise the postprocessing failed
172 result := 'FAILED';
173 errmsg := params;
174 return;
175 end if;
176
177 -- If time still left continue the loop
178 if sysdate >= end_time then
179 result := 'TIMEOUT';
180 return;
181 end if;
182
183 end loop;
184
185 end;
186
187
188 --
189 -- update_req_pp_status
190 --
191 -- Helper procedure for postprocess
192 -- Updates FND_CONCURRENT_REQUESTS with the post-processing start date, end date and status
193 --
194 procedure update_req_pp_status(reqid in number, status in varchar2) is
195
196
197 begin
198
199 if status = PP_PENDING then
200
201 update fnd_concurrent_requests
202 set pp_start_date = sysdate, post_request_status = status
203 where request_id = reqid;
204
205 else
206
207 update fnd_concurrent_requests
208 set pp_end_date = sysdate, post_request_status = status
209 where request_id = reqid;
210
211 end if;
212
213
214
215 end;
216
217
218
219 --
220 -- update_pp_action
221 --
222 -- Helper procedure for postprocess
223 -- Updates fnd_conc_pp_actions, setting the processor_id for a request,
224 -- so the post-processor will not pick it up and process it
225 --
226 procedure update_pp_action(reqid in number, cpid in number) is
227
228 pragma autonomous_transaction;
229
230 begin
231 update fnd_conc_pp_actions
232 set processor_id = cpid
233 where concurrent_request_id = reqid;
234
235 commit;
236
237 end;
238
239
240
241 function get_one_subscriber(localnode in varchar2) return varchar2 is
242
243 node_name varchar2(30);
244 subscriber varchar2(30);
245
246 begin
247 -- Check to see if a service is running on the local node
248 if (fnd_cp_opp_ipc.check_group_subscribers(localnode) >= 1) then
249 return localnode;
250 end if;
251
252 -- if not, select a random service
253 subscriber := fnd_cp_opp_ipc.select_random_subscriber;
254 if subscriber is null then
255 return null;
256 end if;
257
258 -- use that service's node
259 begin
260 select fcp.node_name
261 into node_name
262 from fnd_concurrent_processes fcp
263 where fcp.concurrent_process_id = subscriber
264 and fcp.process_status_code in ('A', 'Z');
265 exception
266 when no_data_found then
267 return null;
268 end;
269 return node_name;
270
271
272 end;
273
274
275 --
276 -- select_postprocessor
277 --
278 -- Looks for a post-processor service to post-process a request
279 -- First uses the same node name the manager is running on.
280 -- If a PP service is running there, it returns that node name.
281 -- If one is not found, it picks a random PP service.
282 -- Errcode will be 0 if a post-processor was found.
283 -- If no post-processor is available, or an error occurs, errcode
284 -- will be < 0.
285 --
286 -- Note: Can only be called from a concurrent manager
287 --
288
289 --
290 -- bug6056627 - reimplementation of fix in 5358039
291 -- Instead of using session id from environment, use request_id
292 -- to find out the host name. Previous to this change, spawned
293 -- programs were not able to use OPP as it could not find which
294 -- manager was running the program. With this change, regardless
295 -- of the program type, it will always find the host. Now, all
296 -- types of programs can use OPP.
297 --
298
299 procedure select_postprocessor(opp_name out NOCOPY varchar2,
300 errcode out NOCOPY number,
301 requestid in number) is
302
303 node_name varchar2(30);
304 init_count number;
305 sleeptime number;
306 prof_buffer varchar2(32);
307
308 begin
309
310 -- Select our local node
311 begin
312 select fcr.outfile_node_name
313 into node_name
314 from fnd_concurrent_requests fcr
315 where request_id = requestid;
316 exception
317 when no_data_found then
318 opp_name := null;
319 errcode := -1;
320 return;
321 end;
322
323
324 -- Find a subscriber
325 opp_name := get_one_subscriber(node_name);
326 if opp_name is not null then
327 errcode := 0;
328 return;
329 end if;
330
331 -- No subscribers found right now. Check to see if the OPP service is still initializing
332 select count(*)
333 into init_count
334 from fnd_concurrent_processes fcp,
335 fnd_concurrent_queues fcq,
336 fnd_cp_services fcs
337 where fcs.service_handle = 'FNDOPP'
338 and fcs.service_id = fcq.manager_type
339 and fcq.concurrent_queue_id = fcp.concurrent_queue_id
340 and fcq.application_id = fcp.queue_application_id
341 and fcp.process_status_code = 'Z';
342
343 -- If no services are initializing, nothing we can do.
344 if init_count = 0 then
345 errcode := -2;
346 return;
347 end if;
348
349 -- At this point at least one OPP service is still initializing, wait a little while for it...
350 fnd_profile.get('CONC_PP_INIT_DELAY', prof_buffer);
351 if prof_buffer is null then
352 sleeptime := DEFAULT_SLEEP;
353 else
354 sleeptime := to_number(prof_buffer);
355 end if;
356
357 dbms_lock.sleep(sleeptime);
358
359 -- And try one more time...
360 opp_name := get_one_subscriber(node_name);
361 if opp_name is null then
362 errcode := -3;
363 end if;
364
365 end;
366
367
368
369 --
370 -- postprocess
371 --
372 -- Post-process a request
373 -- Used by request-processing managers to submit a request to the post-processor
374 --
375 -- reqid - Request id to postprocess
376 -- groupid - Group to send request to
377 -- success_flag - Y if request was postprocessed successfully, N otherwise
378 -- errmsg - Reason for failure
379 --
380 procedure postprocess(reqid in number,
381 groupid in varchar2,
382 success_flag out NOCOPY varchar2,
383 errmsg out NOCOPY varchar2) is
384
385
386 cpid number;
387 result varchar2(30);
388 pp_id number;
389 prof_buffer varchar2(32);
390 timeout number;
391
392 begin
393
394 success_flag := 'N';
395
396 update_req_pp_status(reqid, PP_PENDING);
397
398 --
399 -- bug6056627
400 -- Use request-id to find the controlling manager. Spawned programs
401 -- may call OPP. Depending on rquest_id, instead of session_id allows
402 -- controlling manager to be always derrived.
403 --
404 begin
405 select fcr.controlling_manager
406 into cpid
407 from fnd_concurrent_requests fcr
408 where request_id = reqid;
409 exception
410 when no_data_found then
411 errmsg := fnd_message.get_string('FND', 'CONC-PP CMGR ONLY');
412 return ;
413 end;
414
415
416
417
418 fnd_cp_opp_ipc.send_request(groupid, cpid, reqid, '');
419
420 fnd_profile.get('CONC_PP_RESPONSE_TIMEOUT', prof_buffer);
421 if prof_buffer is null then
422 timeout := TIMEOUT1;
423 else
424 timeout := to_number(prof_buffer);
425 end if;
426
427 wait_for_reply(cpid, reqid, timeout, result, errmsg);
428
429 if result = 'SUCCESS' then
430 update_req_pp_status(reqid, PP_COMPLETE);
431 success_flag := 'Y';
432 return;
433 end if;
434
435 if result = 'ERROR' or result = 'FAILED' then
436 update_req_pp_status(reqid, PP_ERROR);
437 return;
438 end if;
439
440
441
442 -- at this point we have a timeout
443 -- see if the postprocessor has started on it
444
445 select processor_id
446 into pp_id
447 from fnd_conc_pp_actions
448 where concurrent_request_id = reqid
449 and action_type = 6
450 and sequence = 1;
451
452 -- has not started yet
453 if pp_id is null then
454
455 -- ??? what to do here?
456 -- update the pp_actions table so the post-processor will not process this req
457 update_pp_action(reqid, cpid);
458 update_req_pp_status(reqid, PP_ERROR);
459 errmsg := fnd_message.get_string('FND', 'CONC-PP NO RESPONSE');
460 return;
461
462 else
463 -- processing has started, wait some more
464
465 prof_buffer := null;
466 fnd_profile.get('CONC_PP_PROCESS_TIMEOUT', prof_buffer);
467 if prof_buffer is null then
468 timeout := TIMEOUT2;
469 else
470 timeout := to_number(prof_buffer);
471 end if;
472
473 wait_for_reply(cpid, reqid, timeout, result, errmsg);
474
475
476 if result = 'SUCCESS' then
477 update_req_pp_status(reqid, PP_COMPLETE);
478 success_flag := 'Y';
479 return;
480 end if;
481
482 if result = 'ERROR' or result = 'FAILED' then
483 update_req_pp_status(reqid, PP_ERROR);
484 return;
485 end if;
486
487 -- timed out again??
488 -- send terminate command to OPP service
489 -- BUG 9062358 GSI:GLOBAL ENQUEUE SERVICES DEADLOCK DETECTED
490 -- fnd_cp_opp_cmd.terminate_opp_request(reqid, cpid);
491 fnd_cp_opp_cmd.terminate_opp_request_this_txn(reqid, cpid);
492
493
494 update_req_pp_status(reqid, PP_TIMEOUT);
495 errmsg := fnd_message.get_string('FND', 'CONC-PP TIMEOUT');
496 return;
497
498 end if;
499
500
501
502 exception
503 when others then
504 update_req_pp_status(reqid, PP_ERROR);
505 errmsg := sqlerrm;
506 return;
507
508 end;
509
510
511 -- Added for bug Bug 6275963
512 --
513 -- published_request
514 --
515 -- Used to determine whether the request is a published request. If the request is a
516 -- simple reprint request of a published request in that case parent request id is passed
517 -- as the published request in the out parameter pub_req_id
518 --
519 -- reqid - Concurrent request id
520 -- is_published - boolean variable to return whether the request is a published request
521 -- pub_reqid - Request id of the published request. Incase the request passed as reqid
522 -- is a simple reprint of a published request then the parent request id
523 -- will be passed as pub_reqid else it will be same as reqid
524
525 procedure published_request (reqid in number,
526 is_published out NOCOPY boolean,
527 pub_req_id out NOCOPY number) is
528
529
530 prog_name varchar2(30) := null;
531 appl_name varchar2(50) := null;
532 parent_req_id varchar2(240) :=null;
533 output_exists number;
534 action_type number;
535
536 begin
537
538 -- Select Parent_request_id (argument1) for the FNDREPRINT request
539
540 is_published := false;
541
542 select fcp.concurrent_program_name, a.application_short_name, argument1
543 into prog_name, appl_name, parent_req_id
544 from fnd_concurrent_requests fcr,
545 fnd_concurrent_programs fcp, fnd_application a
546 where fcr.request_id = reqid
547 and fcp.concurrent_program_id = fcr.concurrent_program_id
548 and fcp.application_id = fcr.program_application_id
549 and fcp.application_id = a.application_id
550 and rownum = 1;
551
552 if (prog_name = 'FNDREPRINT' and appl_name = 'FND') then
553
554
555 -- Check if the request itself is a published request, if not then check
556 -- whether the parent request is a published request.
557
558 select count(1) into action_type from fnd_conc_pp_actions
559 where concurrent_request_id=reqid and action_type=6;
560
561 select count(1) into output_exists from fnd_conc_req_outputs
562 where concurrent_request_id = decode(action_type, 1, reqid, to_number(parent_req_id))
563 and file_size>0;
564
565 if (action_type=1 and output_exists=1) then
566 is_published := true;
567 pub_req_id := reqid;
568 elsif(action_type=0 and output_exists=1) then
569 is_published := true;
570 pub_req_id := to_number(parent_req_id);
571 end if;
572
573 else
574 select count(1) into output_exists from fnd_conc_req_outputs
575 where concurrent_request_id = reqid
576 and file_size>0;
577
578 if ( output_exists=1) then
579 pub_req_id := reqid;
580 is_published := true;
581 end if;
582 end if;
583
584 end;
585 -- ============================
586 -- Reprint procedures
587 -- ============================
588
589
590 --
591 -- adjust_outfile
592 --
593 -- Used by the Republish/Reprint program to properly set its output file for
594 -- republishing and/or reprinting
595 --
596 -- cur_reqid - Current request id
597 -- prev_reqid - Request to reprint/republish
598 -- success_flag - Y if output file updated, N otherwise
599 -- errmsg - Reason for failure
600 --
601 procedure adjust_outfile(cur_reqid in number,
602 prev_reqid in number,
603 success_flag out NOCOPY varchar2,
604 errmsg out NOCOPY varchar2) is
605
606 pragma autonomous_transaction;
607
608 outfile varchar2(255) := NULL;
609 outnode varchar2(32) := NULL;
610 outtype varchar2(4) :=NULL; -- Bug 6040814. To copy output_file_type from parent request
611 cnt number;
612 nlschar varchar2(2) := NULL;
613 codeset varchar2(30) := NULL;
614
615 begin
616
617 success_flag := 'N';
618
619 -- see if any publishing actions for this request
620 select count(*)
621 into cnt
622 from fnd_conc_pp_actions
623 where action_type = 6
624 and concurrent_request_id = cur_reqid;
625
626 if cnt = 0 then
627
628 -- reprinting only, check to see if the previous request has a published file
629 -- Bug 6040814. Select output_file_type to copy from parent request.
630 if published_request(prev_reqid) then
631 begin
632 select fcro.file_name, fcro.file_node_name, fcro.file_type
633 into outfile, outnode, outtype
634 from fnd_conc_req_outputs fcro
635 where fcro.concurrent_request_id = prev_reqid;
636 exception
637 when no_data_found then
638 errmsg := 'Could not find published output file for previous request';
639 rollback;
640 return;
641 end;
642 end if;
643 end if;
644
645 -- Bug 6040814. Select output_file_type to copy from parent request.
646 if outfile is null then
647
648 begin
649 select outfile_name, outfile_node_name, output_file_type, nls_numeric_characters, nls_codeset
650 into outfile, outnode, outtype, nlschar, codeset
651 from fnd_concurrent_requests
652 where request_id = prev_reqid;
653 exception
654 when no_data_found then
655 errmsg := 'Could not find previous request: ' || prev_reqid;
656 rollback;
657 return;
658 end;
659
660 end if;
661
662 -- Bug 6040814. Update output_file_type from parent request.
663
664 update fnd_concurrent_requests
665 set outfile_name = outfile,
666 outfile_node_name = outnode,
667 output_file_type = outtype,
668 nls_numeric_characters = nlschar,
669 nls_codeset = codeset
670 where request_id = cur_reqid;
671
672 success_flag := 'Y';
673 commit;
674
675
676 exception
677 when others then
678 errmsg := sqlerrm;
679 rollback;
680
681 end;
682
683
684 END fnd_cp_opp_req;
[Home] [Help]