1 PACKAGE BODY fnd_cp_opp_req AS
2 /* $Header: AFCPOPRB.pls 120.11 2008/04/04 15:57:02 tkamiya 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 fnd_cp_opp_cmd.terminate_opp_request(reqid, cpid);
490
491 update_req_pp_status(reqid, PP_TIMEOUT);
492 errmsg := fnd_message.get_string('FND', 'CONC-PP TIMEOUT');
493 return;
494
495 end if;
496
497
498
499 exception
500 when others then
501 update_req_pp_status(reqid, PP_ERROR);
502 errmsg := sqlerrm;
503 return;
504
505 end;
506
507
508 -- Added for bug Bug 6275963
509 --
510 -- published_request
511 --
512 -- Used to determine whether the request is a published request. If the request is a
513 -- simple reprint request of a published request in that case parent request id is passed
514 -- as the published request in the out parameter pub_req_id
515 --
516 -- reqid - Concurrent request id
517 -- is_published - boolean variable to return whether the request is a published request
518 -- pub_reqid - Request id of the published request. Incase the request passed as reqid
519 -- is a simple reprint of a published request then the parent request id
520 -- will be passed as pub_reqid else it will be same as reqid
521
522 procedure published_request (reqid in number,
523 is_published out NOCOPY boolean,
524 pub_req_id out NOCOPY number) is
525
526
527 prog_name varchar2(30) := null;
528 appl_name varchar2(50) := null;
529 parent_req_id varchar2(240) :=null;
530 output_exists number;
531 action_type number;
532
533 begin
534
535 -- Select Parent_request_id (argument1) for the FNDREPRINT request
536
537 is_published := false;
538
539 select fcp.concurrent_program_name, a.application_short_name, argument1
540 into prog_name, appl_name, parent_req_id
541 from fnd_concurrent_requests fcr,
542 fnd_concurrent_programs fcp, fnd_application a
543 where fcr.request_id = reqid
544 and fcp.concurrent_program_id = fcr.concurrent_program_id
545 and fcp.application_id = fcr.program_application_id
546 and fcp.application_id = a.application_id
547 and rownum = 1;
548
549 if (prog_name = 'FNDREPRINT' and appl_name = 'FND') then
550
551
552 -- Check if the request itself is a published request, if not then check
553 -- whether the parent request is a published request.
554
555 select count(1) into action_type from fnd_conc_pp_actions
556 where concurrent_request_id=reqid and action_type=6;
557
558 select count(1) into output_exists from fnd_conc_req_outputs
559 where concurrent_request_id = decode(action_type, 1, reqid, to_number(parent_req_id))
560 and file_size>0;
561
562 if (action_type=1 and output_exists=1) then
563 is_published := true;
564 pub_req_id := reqid;
565 elsif(action_type=0 and output_exists=1) then
566 is_published := true;
567 pub_req_id := to_number(parent_req_id);
568 end if;
569
570 else
571 select count(1) into output_exists from fnd_conc_req_outputs
572 where concurrent_request_id = reqid
573 and file_size>0;
574
575 if ( output_exists=1) then
576 pub_req_id := reqid;
577 is_published := true;
578 end if;
579 end if;
580
581 end;
582 -- ============================
583 -- Reprint procedures
584 -- ============================
585
586
587 --
588 -- adjust_outfile
589 --
590 -- Used by the Republish/Reprint program to properly set its output file for
591 -- republishing and/or reprinting
592 --
593 -- cur_reqid - Current request id
594 -- prev_reqid - Request to reprint/republish
595 -- success_flag - Y if output file updated, N otherwise
596 -- errmsg - Reason for failure
597 --
598 procedure adjust_outfile(cur_reqid in number,
599 prev_reqid in number,
600 success_flag out NOCOPY varchar2,
601 errmsg out NOCOPY varchar2) is
602
603 pragma autonomous_transaction;
604
605 outfile varchar2(255) := NULL;
606 outnode varchar2(32) := NULL;
607 outtype varchar2(4) :=NULL; -- Bug 6040814. To copy output_file_type from parent request
608 cnt number;
609
610 begin
611
612 success_flag := 'N';
613
614 -- see if any publishing actions for this request
615 select count(*)
616 into cnt
617 from fnd_conc_pp_actions
618 where action_type = 6
619 and concurrent_request_id = cur_reqid;
620
621 if cnt = 0 then
622
623 -- reprinting only, check to see if the previous request has a published file
624 -- Bug 6040814. Select output_file_type to copy from parent request.
625 if published_request(prev_reqid) then
626 begin
627 select fcro.file_name, fcro.file_node_name, fcr.output_file_type
628 into outfile, outnode, outtype
629 from fnd_conc_req_outputs fcro, fnd_concurrent_requests fcr
630 where fcro.concurrent_request_id = prev_reqid
631 and fcro.concurrent_request_id = fcr.request_id;
632 exception
633 when no_data_found then
634 errmsg := 'Could not find published output file for previous request';
635 rollback;
636 return;
637 end;
638 end if;
639 end if;
640
641 -- Bug 6040814. Select output_file_type to copy from parent request.
642 if outfile is null then
643
644 begin
645 select outfile_name, outfile_node_name, output_file_type
646 into outfile, outnode, outtype
647 from fnd_concurrent_requests
648 where request_id = prev_reqid;
649 exception
650 when no_data_found then
651 errmsg := 'Could not find previous request: ' || prev_reqid;
652 rollback;
653 return;
654 end;
655
656 end if;
657
658 -- Bug 6040814. Update output_file_type from parent request.
659
660 update fnd_concurrent_requests
661 set outfile_name = outfile,
662 outfile_node_name = outnode,
663 output_file_type = outtype
664 where request_id = cur_reqid;
665
666 success_flag := 'Y';
667 commit;
668
669
670 exception
671 when others then
672 errmsg := sqlerrm;
673 rollback;
674
675 end;
676
677
678 END fnd_cp_opp_req;