1 package body FND_DCP as
2 /* $Header: AFCPDCPB.pls 120.5 2007/12/28 19:41:22 ckclark ship $ */
3
4 --
5 -- PRIVATE VARIABLES
6 --
7
8 DAY constant number := 86400; -- In secs
9 EON constant number := 86400000; -- 1000 days in secs
10 NTRUE constant number := 1;
11 NFALSE constant number := 0;
12 SYSADMIN constant number := 0;
13 MGRUSRID constant number := 4;
14 THRSHLD constant number := 2; -- PMON method
15 DEFSLEEP constant number := 60; -- PMON method
16 TSTAMP constant number := 1; -- PMON method
17 FNDCPLK constant varchar2(8) := 'FNDCPLK_';
18
19 my_node varchar2(30);
20 --
21 -- PRIVATE PROCEDURES, FUNCTIONS
22 --
23
24 /* Private -- inserts info in FND_CONCURRENT_DEBUG_INFO */
25
26 procedure debug_fnd_dcp (fn in varchar2,
27 msg in varchar2,
28 txn in number default NULL) is
29 pragma AUTONOMOUS_TRANSACTION;
30 userid number;
31 begin
32 select user_id into userid from user_users;
33
34 insert into fnd_concurrent_debug_info
35 (SESSION_ID, USER_ID, LOGIN_ID, FUNCTION, TIME, ACTION, MESSAGE)
36 values
37 (userenv('SESSIONID'), userid, fnd_global.login_id, fn,
38 sysdate, 'TXN NO '|| nvl(to_char(txn),'NULL'), msg);
39
40 commit;
41
42 end debug_fnd_dcp;
43
44 function get_icm_info (logf in out nocopy varchar2,
45 node in out nocopy varchar2,
46 inst in out nocopy varchar2)
47 return number is
48
49 cpid number(15) := null;
50
51 begin
52 select concurrent_process_id,
53 node_name,
54 logfile_name,
55 db_instance
56 into cpid,
57 node,
58 logf,
59 inst
60 from fnd_concurrent_processes
61 where concurrent_process_id in (
62 select max (concurrent_process_id)
63 from fnd_concurrent_processes
64 where queue_application_id = 0
65 and concurrent_queue_id = 1
66 and process_status_code in ('A', 'M', 'K'));
67
68 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
69 fnd_log.string(FND_LOG.LEVEL_EVENT, 'fnd.plsql.FND_DCP.GET_ICM_INFO',
70 'ICM info: cpid=' ||cpid||', node='||node||', inst=' ||inst);
71 end if;
72
73 return (cpid);
74
75 exception
76 when others then
77 return (null);
78 end get_icm_info;
79
80
81 function get_pid_time (ti in out nocopy number, -- PMON method
82 td in out nocopy number,
83 apid in number,
84 qid in number,
85 psc1 in varchar2 default null,
86 psc2 in varchar2 default null,
87 psc3 in varchar2 default null,
88 psc4 in varchar2 default null)
89 return number is
90
91 cpid number(15) := null;
92
93 begin
94 select nvl (sleep_seconds, DEFSLEEP)
95 into ti
96 from fnd_concurrent_queues
97 where application_id = apid
98 and concurrent_queue_id = qid;
99
100 select concurrent_process_id,
101 (sysdate - last_update_date) * DAY
102 into cpid,
103 td
104 from fnd_concurrent_processes
105 where concurrent_process_id in (
106 select max (concurrent_process_id)
107 from fnd_concurrent_processes
108 where queue_application_id = apid
109 and concurrent_queue_id = qid
110 and process_status_code in (
111 psc1, psc2, psc3, psc4))
112 for update nowait;
113
114 return (cpid);
115
116 exception
117 when others then
118 return (null);
119 end get_pid_time; -- PMON method
120
121 -- ### OVERLOADED ###
122 /*------------------------------------------------------------------|
123 | Private: For use by ATG Only. Please use Check_Process_Status or |
124 | Request_Session_Lock |
125 |------------------------------------------------------------------|
126 | Bug 2093806: The purpose of this overload for Request_Lock is |
127 | request a lock by a known handle and to offer more flexibility |
128 | for the call to DBMS_LOCK.Request. This procedure accepts a |
129 | timeout value, and lock mode value. Instead of interpreting the |
130 | return code from DBMS_LOCK.Request as the parameter "e_code" set|
131 | to NTRUE (1) or NFALSE (0), the parameter "result" is set to the |
132 | return code for interpretation by the calling function. If |
133 | result = NULL, indicates when others exception was raised. |
134 |------------------------------------------------------------------*/
135 procedure request_lock (hndl in varchar2,
136 lmode in number default null, -- lock mode
137 timeout in number default null,
138 result in out nocopy number) is
139 begin
140
141 result := NULL;
142
143 if ((timeout IS NULL) and (lmode IS NULL )) then
144 /*-------------------------------------------------------+
145 | When caller does not specify, use dbms_lock defaults, |
146 | currently MAXWAIT (32767 secs) X_MODE (6-Exclusive) |
147 +-------------------------------------------------------*/
148 result := dbms_lock.request ( lockhandle => hndl );
149 elsif (timeout IS NULL) then
150 result := dbms_lock.request ( lockhandle => hndl,
151 lockmode => lmode ); -- MAXWAIT
152 elsif (lmode IS NULL) then
153 result := dbms_lock.request ( lockhandle => hndl,
154 timeout => timeout ); -- ULX lock
155 else
156 result := dbms_lock.request ( lockhandle => hndl,
157 lockmode => lmode,
158 timeout => timeout);
159 end if;
160
161 /*-------------------------------------------------------+
162 | DBMS_LOCK.Request Result codes: |
163 | 0 - Success |
164 | 1 - Timeout |
165 | 2 - Deadlock |
166 | 3 - Parameter error |
167 | 4 - Already own lock specified by lockhandle |
168 | 5 - Illegal lock handle |
169 +-------------------------------------------------------*/
170
171 exception
172 when others then
173 fnd_message.set_name ('FND', 'CP-Generic oracle error');
174 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
175 fnd_message.set_token ('ROUTINE', 'REQUEST_LOCK (hndl)', FALSE);
176 end request_lock;
177
178 --
179 -- PUBLIC PROCEDURES, FUNCTIONS
180 --
181
182 procedure get_lk_handle (apid in number,
183 qid in number,
184 pid in number,
185 hndl in out nocopy varchar2,
186 e_code in out nocopy number,
187 exp_sec in number default 86400000) is
188
189 result number;
190 lk varchar2(128);
191
192 begin
193 e_code := NTRUE;
194
195 hndl := NULL;
196
197 if (apid = 0 and qid = 1 and pid = 0) then
198 lk := FNDCPLK || 'ICM';
199 else
200 lk := FNDCPLK || apid || '_' || qid || '_' || pid;
201 end if;
202
203 dbms_lock.allocate_unique (lk, hndl, exp_sec);
204
205 exception
206 when others then
207 e_code := NFALSE;
208 fnd_message.set_name ('FND', 'CP-Generic oracle error');
209 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
210 fnd_message.set_token ('ROUTINE', 'get_lk_handle', FALSE);
211 end get_lk_handle;
212
213 /*------------------------------------------------------------------|
214 | Bug 2093806: The purpose of Request_Session_Lock is to provide |
215 | an API which can be used for a manager process (ICM, CM, IM, CRM,|
216 | TM) to request it's own lock for the session. This procedure |
217 | is not meant to be used for a process (such as an Apps form, OAM |
218 | procedure or ICM in PMON cycle) to check the lock of another |
219 | process. This procedure will wait for the default of MAXWAIT |
220 | for the RDBMS to grant the requested lock. If the parameters "lk"|
221 | and "hndl" are returned as NULL, then there was a problem. The |
222 | calling process can analyze the "result" parameter to determine |
223 | why the request was not granted. |
224 |------------------------------------------------------------------*/
225 procedure request_session_lock (apid in number,
226 qid in number,
227 pid in number,
228 lk in out nocopy varchar2,
229 hndl in out nocopy varchar2,
230 result in out nocopy number) is
231 ecode number;
232 timeout number;
233 begin
234
235 result := null;
236
237 /*-------------------------------------------------------+
238 | Construct the lock name for out parameter "lk" and |
239 | set timeout for Request_Lock. |
240 +-------------------------------------------------------*/
241 if (apid = 0 and qid = 1 and pid = 0) then
242 lk := FNDCPLK || 'ICM';
243 timeout := 5; -- ICM only waits 5 secs for lock request.
244 else
245 lk := FNDCPLK || apid || '_' || qid || '_' || pid;
246 timeout := null; -- Others wait MAXWAIT for lock request.
247 end if;
248
249 /*-------------------------------------------------------+
250 | Get handle for the request. |
251 +-------------------------------------------------------*/
252 get_lk_handle (apid => apid,
253 qid => qid,
254 pid => pid,
255 hndl => hndl,
256 e_code => ecode);
257 if (ecode = NFALSE) then
258 /*-------------------------------------------------------+
259 | Message available in dictionary set by Get_Lk_Handle |
260 +-------------------------------------------------------*/
261 lk := null;
262 hndl := null;
263 return;
264 end if;
265
266 /*-------------------------------------------------------+
267 | Request with mode of exclusive (ULX), with timeout |
268 +-------------------------------------------------------*/
269 request_lock (hndl => hndl,
270 lmode => 6,
271 timeout => timeout,
272 result => result);
273
274 if (result in (0, 4)) then
275 return;
276 else
277 fnd_message.set_name ('FND', 'CONC-DBMS_LOCK.Request result');
278 fnd_message.set_token ('ROUTINE',
279 'FND_DCP.REQUEST_SESSION_LOCK', FALSE);
280 fnd_message.set_token ('RESULT',
281 nvl(to_char(result),'NULL'), FALSE);
282 lk := null;
283 hndl := null;
284 return;
285 end if;
286
287 end request_session_lock;
288
289 /*------------------------------------------------------------------|
290 | Bug 2093806: The purpose of Check_Process_Status_By_Handle is to |
291 | provide an API which can be used for a process (such as an Apps |
292 | form, OAM procedure or ICM in PMON cycle) to check the lock of |
293 | another process (such as ICM, CM, IM, CRM, TM). This procedure |
294 | will immediately time out if the RDBMS does not grant the lock. |
295 | If process is alive, alive = NTRUE(1). Otherwise, alive = |
296 | NFALSE(0). Calling process can analyze the "result" parameter to|
297 | determine why/why not the lock was granted, if desired. |
298 |------------------------------------------------------------------*/
299 procedure check_process_status_by_handle (hndl in varchar2,
300 result out nocopy number,
301 alive out nocopy number) is
302
303 dummy number;
304
305 begin
306
307 /*-------------------------------------------------------+
308 | Request with mode of row share (ULRS), timeout of 0 |
309 +-------------------------------------------------------*/
310 request_lock (hndl => hndl,
311 lmode => 2,
312 timeout => 0,
313 result => result);
314
315 if (result = 0) then
316 /*-------------------------------------------------------+
317 | Lock for handle was granted |
318 +-------------------------------------------------------*/
319 alive := NFALSE; -- manager not alive
320 release_lock(hndl, dummy);
321 return;
322 elsif (result = 4) then
323 /*-------------------------------------------------------+
324 | This process owns the lock, it must be alive! |
325 +-------------------------------------------------------*/
326 alive := NTRUE;
327 release_lock(hndl, dummy);
328 return;
329 elsif (result in (1, 2)) then
330 /*-------------------------------------------------------+
331 | Lock for handle not granted (2 should never occur) |
332 +-------------------------------------------------------*/
333 alive := NTRUE; -- manager alive
334 return;
335 else
336 /*-------------------------------------------------------+
337 | Lock for handle not granted: |
338 | result = (3 or 5 from DBMS_LOCK.Request or |
339 | still NULL from Request_Lock |
340 +-------------------------------------------------------*/
341 alive := NTRUE; -- assume manager alive
342 fnd_message.set_name ('FND', 'CONC-DBMS_LOCK.Request result');
343 fnd_message.set_token ('ROUTINE',
344 'FND_DCP.CHECK_PROCESS_STATUS_BY_HANDLE',
345 FALSE);
346 fnd_message.set_token ('RESULT',
347 nvl(to_char(result), 'NULL'), FALSE);
348
349 end if;
350
351 end check_process_status_by_handle;
352
353 /*------------------------------------------------------------------|
354 | Bug 2093806: The purpose of Check_Process_Status_By_Ids is to |
355 | provide an API which can be used for a process (such as an Apps |
356 | form, OAM procedure or ICM in PMON cycle) to check the lock of |
357 | another process (such as ICM, CM, IM, CRM, TM). This procedure |
358 | uses the Application Id, Queue Id, and O/S Pid to construct the |
359 | named lock for the request. The procedure will immediately time |
360 | out if the RDBMS does not grant the lock. If process is alive, |
361 | alive = NTRUE(1). Otherwise, alive = NFALSE(0). Calling process |
362 | can analyze the "result" parameter to determine why/why not the |
363 | lock was granted, if desired. |
364 |------------------------------------------------------------------*/
365 procedure check_process_status_by_ids (apid in number,
366 qid in number,
367 pid in number,
368 result out nocopy number,
369 alive out nocopy number) is
370
371 hndl varchar2(128) := NULL;
372 dummy number;
373
374 begin
375
376 /*-------------------------------------------------------+
377 | Get handle for the request |
378 +-------------------------------------------------------*/
379 get_lk_handle (apid => apid,
380 qid => qid,
381 pid => pid,
382 hndl => hndl,
383 e_code => dummy);
384
385 /*-------------------------------------------------------+
386 | Check the process' status by the handle |
387 +-------------------------------------------------------*/
388 check_process_status_by_handle (hndl => hndl,
389 result => result,
390 alive => alive);
391
392 end check_process_status_by_ids;
393
394
395 -- ### OVERLOADED ###
396 /*------------------------------------------------------------------|
397 | Obsolete: Please use Request_Session_Lock, Check_Process_Status |
398 | or Check_Process_Status_By_Handle |
399 +------------------------------------------------------------------*/
400 procedure request_lock (apid in number,
401 qid in number,
402 pid in number,
403 lk in out nocopy varchar2,
404 hndl in out nocopy varchar2,
405 e_code in out nocopy number) is
406
407 result number;
408 count number;
409
410 begin
411 e_code := NTRUE;
412
413 if (apid = 0 and qid = 1 and pid = 0) then
414 lk := FNDCPLK || 'ICM';
415 else
416 lk := FNDCPLK || apid || '_' || qid || '_' || pid;
417 end if;
418
419 dbms_lock.allocate_unique (lk, hndl, EON);
420 result := dbms_lock.request (hndl, 6, 0); -- ULX lock
421
422 if (result in (0, 4)) then -- Success or own lock
423 return;
424 elsif (result in (1, 2)) then -- Timeout or deadlock
425
426 /* ------------------------------------------------------- */
427 /* Bug 1967288: Timout occasionally returned when manager */
428 /* log backs into database with afpcsq(). So, try up to 2 */
429 /* more times with 5 second timeout. */
430 /* We cannot wait much longer since Forms and OAM use this */
431 /* version of Request_Lock to check each manager's status */
432 /* This is a short-term fix until more robust restructuring*/
436 for count in 1..2 loop
433 /* can be done with lock management procedures, functions. */
434 /* ------------------------------------------------------- */
435
437 result := dbms_lock.request (hndl, 6, 5);
438 exit when result in (0, 4);
439 end loop;
440
441 if (result in (0, 4)) then -- Success or own lock
442 return;
443 elsif (result in (1, 2)) then -- Timeout or deadlock
444 lk := null;
445 hndl := null;
446 else -- Bad lock handle or bad param
447 lk := null;
448 hndl := null;
449 e_code := NFALSE;
450 end if;
451
452 else -- Bad lock handle or bad param
453 lk := null;
454 hndl := null;
455 e_code := NFALSE;
456 end if;
457
458 exception
459 when others then
460 e_code := NFALSE;
461 fnd_message.set_name ('FND', 'CP-Generic oracle error');
462 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
463 fnd_message.set_token ('ROUTINE', 'REQUEST_LOCK', FALSE);
464 end request_lock;
465
466 -- ### OVERLOADED ###
467 /*------------------------------------------------------------------|
468 | Obsolete: Please use Request_Session_Lock, Check_Process_Status |
469 | or Check_Process_Status_By_Handle |
470 +------------------------------------------------------------------*/
471 procedure request_lock (hndl in varchar2,
472 status in out nocopy number,
473 e_code in out nocopy number) is
474
475 result number;
476
477 begin
478 e_code := NTRUE;
479
480 result := dbms_lock.request (hndl, 6, 0); -- ULX lock
481
482 if (result in (0, 4)) then -- Success or own lock
483 status := NTRUE;
484 elsif (result in (1, 2)) then -- Timeout or deadlock
485 status := NFALSE;
486 else -- Bad lock handle or bad param
487 e_code := NFALSE;
488 end if;
489
490 exception
491 when others then
492 e_code := NFALSE;
493 fnd_message.set_name ('FND', 'CP-Generic oracle error');
494 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
495 fnd_message.set_token ('ROUTINE', 'REQUEST_LOCK (hndl)', FALSE);
496 end request_lock;
497
498
499 procedure release_lock (hndl in varchar2,
500 e_code in out nocopy number) is
501
502 result number;
503
504 begin
505 result := dbms_lock.release (hndl);
506
507 if ((result = 0) or (result = 4)) then
508 e_code := NTRUE;
509 else
510 e_code := NFALSE;
511 end if;
512
513 exception
514 when others then
515 e_code := NFALSE;
516 fnd_message.set_name ('FND', 'CP-Generic oracle error');
517 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
518 fnd_message.set_token ('ROUTINE', 'release_lock', FALSE);
519 end release_lock;
520
521
522 procedure clean_mgrs (e_code in out nocopy number) is
523
524 begin
525 null;
526 end clean_mgrs;
527
528
529 procedure monitor_icm (hndl in out nocopy varchar2,
530 up in out nocopy number,
531 logf in out nocopy varchar2,
532 node in out nocopy varchar2,
533 inst in out nocopy varchar2,
534 cpid in out nocopy number,
535 mthd in number, -- PMON method
536 e_code in out nocopy number) is
537
538 lk varchar2(50);
539 result number;
540 bad_lock exception;
541 time_interval number; -- PMON method = TSTAMP
542 time_difference number; -- PMON method = TSTAMP
543 dummy number;
544
545 begin
546 e_code := NTRUE;
547
548 if (mthd = TSTAMP) then -- PMON method = TSTAMP
549 cpid := get_pid_time (time_interval,
550 time_difference,
551 0, 1, 'A', 'C', 'M', 'K');
552
553 if (time_difference < THRSHLD * time_interval) then
554 up := NTRUE;
555 rollback;
556 return;
557 end if;
558
559 else -- PMON method = DBLOCK
560 cpid := get_icm_info (logf, node, inst);
561
562 if (hndl is not null) then
563 result := dbms_lock.request (hndl, 6, 0); -- ULX lock
564
565 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
566 fnd_log.string(FND_LOG.LEVEL_EVENT,
567 'fnd.plsql.FND_DCP.MONITOR_ICM',
568 'lock request result='||to_char(result)||' for hndl='||hndl);
569 end if;
570
571
572 if (result in (1, 2)) then -- Timeout or deadlock
573 up := NTRUE;
574 return;
575 end if;
576
577 end if;
578
579 end if; -- PMON method TSTAMP/DBLOCK
580
581 if (cpid is null) then
582 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
583 fnd_log.string(FND_LOG.LEVEL_EVENT, 'fnd.plsql.FND_DCP.MONITOR_ICM',
584 'get_icm_info returned cpid NULL');
585 end if;
586
587 up := NTRUE;
588 hndl := null;
589 rollback;
590 return;
591
592 else
593 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
594 fnd_log.string(FND_LOG.LEVEL_EVENT, 'fnd.plsql.FND_DCP.MONITOR_ICM',
595 'get_icm_info returned cpid='||to_char(cpid));
596 end if;
597
598 lk := FNDCPLK || '0_1_' || cpid;
599 dbms_lock.allocate_unique (lk, hndl, EON);
600 result := dbms_lock.request (hndl, 6, 0); -- ULX lock
601
602 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
603 fnd_log.string(FND_LOG.LEVEL_EVENT, 'fnd.plsql.FND_DCP.MONITOR_ICM',
604 'new lock name = '||lk);
605 fnd_log.string(FND_LOG.LEVEL_EVENT, 'fnd.plsql.FND_DCP.MONITOR_ICM',
606 'lock request result='||to_char(result)||' for hndl='||hndl);
607 end if;
608
609 if (result = 0) then -- Success
610 select 1 into dummy
611 from fnd_concurrent_processes
612 where concurrent_process_id = cpid
613 for update nowait;
614
615 update fnd_concurrent_processes
616 set process_status_code = decode (process_status_code,
617 'A', 'K',
618 'M', 'S',
619 process_status_code)
620 where concurrent_process_id = cpid;
621 up := NFALSE;
622 commit;
623 elsif (result in (1, 2)) then -- Timeout or deadlock
624 up := NTRUE;
625 elsif (result = 4) then -- Own the lock (shouldn't happen)
626 up := NFALSE;
627 else -- Bad lock handle or bad param
628 raise bad_lock;
629 end if;
630
631 end if;
632
633 exception
634 when others then
635 e_code := NFALSE;
636 rollback;
637 fnd_message.set_name ('FND', 'CP-Generic oracle error');
638 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
639 fnd_message.set_token ('ROUTINE', 'monitor_icm', FALSE);
640 end monitor_icm;
641
642
643 procedure monitor_im (apid in number,
644 qid in number,
645 pid in number,
646 cnode in varchar2,
647 status in out nocopy number,
648 e_code in out nocopy number) is
649
650 max_procs number;
651 run_procs number;
652 ccode varchar2(1); -- Control code
653 tnode varchar2(30); -- Target node
654
655 begin
656 e_code := NTRUE;
657
658 select max_processes,
659 running_processes,
660 control_code,
661 target_node
662 into max_procs,
663 run_procs,
664 ccode,
665 tnode
666 from fnd_concurrent_queues
667 where application_id = apid
668 and concurrent_queue_id = qid;
669
670 if ((max_procs < run_procs) or
671 (cnode <> tnode)) then -- Migrate
672 status := NFALSE; -- Exit
673 else
674 status := NTRUE; -- Stay up
675 end if;
676
677 exception
678 when others then
679 e_code := NFALSE;
680 fnd_message.set_name ('FND', 'CP-Generic oracle error');
681 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
682 fnd_message.set_token ('ROUTINE', 'monitor_im', FALSE);
683 end monitor_im;
684
685
686 procedure reassign_lkh (e_code in out nocopy number) is
687
688 cursor c1 is
689 select queue_application_id,
690 concurrent_queue_id,
691 concurrent_process_id,
692 rowid
693 from fnd_concurrent_processes
694 where manager_type between 1 and 5
695 and process_status_code in ('A', 'C')
696 order by concurrent_process_id;
697
698 apid number(15);
699 qid number(15);
700 pid number(15);
701 rid ROWID;
702 hndl varchar2(128);
703
704 begin
705 e_code := NTRUE;
706
707 open c1;
708
709 loop
710 fetch c1 into apid, qid, pid, rid;
711 exit when c1%notfound;
712
713 dbms_lock.allocate_unique (FNDCPLK ||
714 apid || '_' || qid || '_' || pid,
715 hndl, EON);
716
717 update fnd_concurrent_processes
718 set lk_handle = hndl
719 where rowid = rid;
720
721 end loop;
722
723 close c1;
724 commit;
725
726 exception
727 when others then
728 e_code := NFALSE;
729 fnd_message.set_name ('FND', 'CP-Generic oracle error');
730 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
731 fnd_message.set_token ('ROUTINE', 'reassign_lkh', FALSE);
732 end reassign_lkh;
733
734
735 /* function get_inst_num
736 *
737 * This function is used to determine the OPS instance
738 * to which a manager should "specialize". For Parallel
739 * Concurrent Processing, we want the a manager to
740 * service requests only for the instance associated with
744 * the current instance number is retrieved from v$instance,
741 * its primary node.
742 *
743 * If the manager was started on its primary node, then
745 * stored in fnd_concurrent_queues, and returned to the caller.
746 *
747 * If the manager was started on its secondary node, then
748 * the instance number is retrieved from fnd_concurrent_queues
749 *
750 * Parameters:
751 * queue_appl_id - Concurrent queue application ID.
752 * queue_id - Concurrent queue ID.
753 * current_node - Node where manager is running.
754 *
755 * Returns:
756 * An OPS instance number.
757 *
758 * Alters:
759 * The table fnd_concurrent_queues may be updated.
760 *
761 * Assumptions:
762 * This function assumes that the node names stored in
763 * fnd_concurrent_queues exactly match those in the manager's
764 * sysinfo structure. (i.e. Both fully qualified, or both not
765 * fully qualified)
766 *
767 * Error conditions:
768 *
769 * All other exceptions are unhandled.
770 */
771
772 function get_inst_num (queue_appl_id in number,
773 queue_id in number,
774 current_node in varchar2)
775 return number is
776 primary_node fnd_concurrent_queues.node_name%type;
777 secondary_node fnd_concurrent_queues.node_name2%type;
778 inst_num number; /* OPS Instance Number */
779 begin
780 /* Are we on the primary node?*/
781 select upper(node_name), upper(node_name2)
782 into primary_node, secondary_node
783 from fnd_concurrent_queues
784 where concurrent_queue_id = queue_id
785 and application_id = queue_appl_id;
786
787
788 if (upper(current_node) = primary_node) then /* PCP Primary node */
789 /* Get inst number from v$instance */
790 select instance_number
791 into inst_num
792 from v$instance;
793
794 /* Store it into fnd_concurrent_queues. */
795
796 update fnd_concurrent_queues
797 set instance_number = inst_num
798 where application_id = queue_appl_id
799 and concurrent_queue_id = queue_id;
800
801 /* Update the global */
802 FND_CONC_GLOBAL.Override_OPS_INST_NUM(inst_num);
803
804 else /* we aren't on the primary node...maybe we still know the inst num*/
805 select INSTANCE_NUMBER
806 into inst_num
807 from fnd_concurrent_queues
808 where application_id = queue_appl_id
809 and concurrent_queue_id = queue_id;
810
811 /* Update the global...if not null */
812 if (inst_num is not null) then
813 FND_CONC_GLOBAL.Override_OPS_INST_NUM(inst_num);
814 end if;
815 end if;
816
817 return inst_num;
818 end;
819
820 /* function target_node_mgr_chk
821 * If a request is targeted to a specific node, the concurrent
822 * manager will use this function in his request query (afpgrq)
823 * to filter it out if it doesn't meet any of the following conditions:
824 * a) request's target node is the same as manager's current node
825 * b) request's target node is different from manager's current node, but the
826 * FND_NODES status is 'N' or node_mode is not 'O' (online).
827 * c) There are no managers specialized to run this request on request's
828 * target node
829 *
830 * Parameters:
831 * request_id - id of request that is targeted to a secific node
832 *
833 * Returns:
834 * NTRUE/TRUE/1 if this request can appear in query results
835 * NFALSE/FALSE/0 if this request should be filtered from query results
836 *
837 * Assumptions:
838 * The manager's target_node in fnd_concurrent_queues is it's current
839 * node. This should always be true for active managers in afpgrq.
840 *
841 * Error conditions:
842 *
843 * All other exceptions are unhandled.
844 */
845
846 function target_node_mgr_chk (req_id in number) return number is
847
848 cursor mgr_cursor (rid number, qappid number, qid number) is
849 select queue_application_id, concurrent_queue_id,
850 running_processes, max_processes,
851 decode(control_code,
852 'T','N', -- Abort
853 'X','N', -- Aborted
854 'D','N', -- Deactivate
855 'E','N', -- Deactivated
856 'Y') active,
857 target_node
858 from fnd_concurrent_worker_requests
859 where request_id = rid
860 and not((queue_application_id = 0)
861 and (concurrent_queue_id in (1,4)))
862 and not((queue_application_id = qappid)
863 and (concurrent_queue_id = qid ));
864
865 my_q_appl_id number := FND_GLOBAL.queue_appl_id ;
866 my_q_id number := FND_GLOBAL.conc_queue_id;
867 req_node varchar2(30) := null;
868 dummy number := 0;
869 retval number := NFALSE;
870
871 begin
872
873 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
874 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
875 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
879 end if;
876 'Enter TARGET_NODE_MGR_CHK for manager ('||
877 to_char(my_q_appl_id)|| '/'||my_q_id||
878 ') and request_id '|| to_char(req_id));
880
881 /* Retrieve the request's target node */
882 select node_name1
883 into req_node
884 from fnd_concurrent_requests
885 where request_id = req_id;
886
887 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
888 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
889 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
890 'Target node for request '||to_char(req_id)||' is '||
891 NVL(req_node,'NULL'));
892 end if;
893
894 /* If request has no target node, okay to run it */
895 if (req_node is null) then
896 retval := NTRUE;
897 end if;
898
899 /* Check if the request node matches my node */
900 if (retval = NFALSE) then
901 if (my_node is NULL) then
902 select target_node
903 into my_node
904 from fnd_concurrent_queues
905 where application_id = my_q_appl_id
906 and concurrent_queue_id = my_q_id;
907 end if;
908
909 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
910 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
911 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
912 'Manager node is '||NVL(my_node,'NULL'));
913 end if;
914
915 /* If request target node matches my node, okay to run it */
916 if (req_node = my_node) then
917 retval := NTRUE;
918 end if;
919 end if;
920
921 /* Check if the request node is down */
922 if (retval = NFALSE) then
923 select count(*)
924 into dummy
925 from fnd_nodes
926 where node_name = req_node
927 and (node_mode <> 'O'
928 or status <> 'Y');
929
930 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
931 if dummy >= 1 then
932 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
933 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
934 'Request target node '||req_node||' is DOWN');
935 else
936 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
937 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
938 'Request target node '||req_node||' is UP');
939 end if;
940 end if;
941
942 /* If request target node is down, okay to run it */
943 if (dummy >= 1) then
944 retval := NTRUE;
945 fnd_message.set_name ('FND', 'CONC-REQ NODE NOT HONORED');
946 fnd_message.set_token ('REQID', to_char(req_id), FALSE);
947 fnd_message.set_token ('NODE', req_node, FALSE);
948 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
949 fnd_log.message(FND_LOG.LEVEL_EVENT,
950 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
951 FALSE);
952 end if;
953 end if;
954 end if;
955
956 /* Check if a manager is available to run this request on target node */
957 /* The first available manager we find, exit and return NFALSE */
958 if (retval = NFALSE) then
959 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
960 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
961 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
962 'Enter loop to determine if another manager can run');
963 end if;
964 for mgr_rec in mgr_cursor(req_id, my_q_appl_id, my_q_id) loop
965 if ((mgr_rec.active = 'Y')
966 and (mgr_rec.max_processes > 0)
967 and (mgr_rec.running_processes > 0)
968 and mgr_rec.target_node = req_node) then
969 /* Here is an available manager, no need to continue. */
970 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
971 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
972 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
973 'Another manager ('||
974 to_char(mgr_rec.queue_application_id )||'/'||
975 to_char(mgr_rec.concurrent_queue_id)||
976 ') can run this request');
977 end if;
978 retval := NFALSE;
979 exit;
980 else
981 retval := NTRUE;
982 fnd_message.set_name ('FND', 'CONC-REQ NODE NOT HONORED');
983 fnd_message.set_token ('REQID', to_char(req_id), FALSE);
984 fnd_message.set_token ('NODE', req_node, FALSE);
985 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
986 fnd_log.message(FND_LOG.LEVEL_EVENT,
987 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
988 FALSE);
989 end if;
990 end if;
991 end loop;
992 end if;
993
994 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
995 if retval = NTRUE then
996 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
997 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
998 'Returning retval= NTRUE');
999 else
1000 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1001 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
1002 'Returning retval= NFALSE');
1003 end if;
1004 end if;
1005 return retval;
1006
1007 end target_node_mgr_chk;
1008
1009 --
1010 -- Name
1011 -- is_dcp
1012 -- Purpose
1013 -- Returns TRUE if the environment has multiple CP nodes,
1014 -- FALSE if not.
1015 --
1016 -- Parameters:
1017 -- None
1018 --
1019 -- Returns:
1020 -- NTRUE/TRUE/1 - environment is DCP
1021 -- NFALSE/FALSE/0 - environment is non-DCP
1022 --
1023 function is_dcp return number is
1024
1025 node_count number := 0;
1026 begin
1027
1028 select count(*)
1029 into node_count
1030 from fnd_nodes
1031 where node_name <> 'AUTHENTICATION'
1032 and support_cp = 'Y';
1033
1034 if (node_count > 1) then
1035 return NTRUE;
1036 else
1037 return NFALSE;
1038 end if;
1039
1040 end is_dcp;
1041
1042
1043 end FND_DCP;
[Home] [Help]