1 package body FND_DCP as
2 /* $Header: AFCPDCPB.pls 120.5.12020000.3 2012/10/11 22:31:51 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 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
235 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
236 'fnd.plsql.FND_DCP.REQUEST_SESSION_LOCK',
237 'request_session_lock called for apid='||to_char(apid)||
238 ', qid='||to_char(qid)||', pid='||to_char(pid));
239 end if;
240
241 result := null;
242
243 /*-------------------------------------------------------+
244 | Construct the lock name for out parameter "lk" and |
245 | set timeout for Request_Lock. |
246 +-------------------------------------------------------*/
247 if (apid = 0 and qid = 1 and pid = 0) then
248 lk := FNDCPLK || 'ICM';
249 timeout := 5; -- ICM only waits 5 secs for lock request.
250 else
251 lk := FNDCPLK || apid || '_' || qid || '_' || pid;
252 timeout := null; -- Others wait MAXWAIT for lock request.
253 end if;
254 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
255 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
256 'fnd.plsql.FND_DCP.REQUEST_SESSION_LOCK',
257 'lock name='||lk);
258 end if;
259
260 /*-------------------------------------------------------+
261 | Get handle for the request. |
262 +-------------------------------------------------------*/
263 get_lk_handle (apid => apid,
264 qid => qid,
265 pid => pid,
266 hndl => hndl,
267 e_code => ecode);
268 if (ecode = NFALSE) then
269 /*-------------------------------------------------------+
270 | Message available in dictionary set by Get_Lk_Handle |
271 +-------------------------------------------------------*/
272 lk := null;
273 hndl := null;
274 return;
275 else
276 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
277 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
278 'fnd.plsql.FND_DCP.REQUEST_SESSION_LOCK',
279 'for lock name='||lk||' hndl='||hndl);
280 end if;
281 end if;
282
283 /*-------------------------------------------------------+
284 | Request with mode of exclusive (ULX), with timeout |
285 +-------------------------------------------------------*/
286 request_lock (hndl => hndl,
287 lmode => 6,
288 timeout => timeout,
289 result => result);
290 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
291 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
292 'fnd.plsql.FND_DCP.REQUEST_SESSION_LOCK',
293 'result of request_lock: '||to_char(result));
294 end if;
295
296 if (result in (0, 4)) then
297 return;
298 else
299 fnd_message.set_name ('FND', 'CONC-DBMS_LOCK.Request result');
300 fnd_message.set_token ('ROUTINE',
301 'FND_DCP.REQUEST_SESSION_LOCK', FALSE);
302 fnd_message.set_token ('RESULT',
303 nvl(to_char(result),'NULL'), FALSE);
304 lk := null;
305 hndl := null;
306 return;
307 end if;
308
309 end request_session_lock;
310
311 /*------------------------------------------------------------------|
312 | Bug 2093806: The purpose of Check_Process_Status_By_Handle is to |
313 | provide an API which can be used for a process (such as an Apps |
314 | form, OAM procedure or ICM in PMON cycle) to check the lock of |
315 | another process (such as ICM, CM, IM, CRM, TM). This procedure |
316 | will immediately time out if the RDBMS does not grant the lock. |
317 | If process is alive, alive = NTRUE(1). Otherwise, alive = |
318 | NFALSE(0). Calling process can analyze the "result" parameter to|
319 | determine why/why not the lock was granted, if desired. |
320 |------------------------------------------------------------------*/
321 procedure check_process_status_by_handle (hndl in varchar2,
322 result out nocopy number,
323 alive out nocopy number) is
324
325 dummy number;
326
327 begin
328 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
332 end if;
329 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
330 'fnd.plsql.FND_DCP.CHECK_PROCESS_STATUS_BY_HANDLE',
331 'check_process_status_by_handle called for hndl='||hndl);
333
334 /*-------------------------------------------------------+
335 | Request with mode of row share (ULRS), timeout of 0 |
336 +-------------------------------------------------------*/
337 request_lock (hndl => hndl,
338 lmode => 2,
339 timeout => 0,
340 result => result);
341 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
342 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
343 'fnd.plsql.FND_DCP.CHECK_PROCESS_STATUS_BY_HANDLE',
344 'result of request_lock: '||to_char(result));
345 end if;
346
347 if (result = 0) then
348 /*-------------------------------------------------------+
349 | Lock for handle was granted |
350 +-------------------------------------------------------*/
351 alive := NFALSE; -- manager not alive
352 release_lock(hndl, dummy);
353 return;
354 elsif (result = 4) then
355 /*-------------------------------------------------------+
356 | This process owns the lock, it must be alive! |
357 +-------------------------------------------------------*/
358 alive := NTRUE;
359 release_lock(hndl, dummy);
360 return;
361 elsif (result in (1, 2)) then
362 /*-------------------------------------------------------+
363 | Lock for handle not granted (2 should never occur) |
364 +-------------------------------------------------------*/
365 alive := NTRUE; -- manager alive
366 return;
367 else
368 /*-------------------------------------------------------+
369 | Lock for handle not granted: |
370 | result = (3 or 5 from DBMS_LOCK.Request or |
371 | still NULL from Request_Lock |
372 +-------------------------------------------------------*/
373 alive := NTRUE; -- assume manager alive
374 fnd_message.set_name ('FND', 'CONC-DBMS_LOCK.Request result');
375 fnd_message.set_token ('ROUTINE',
376 'FND_DCP.CHECK_PROCESS_STATUS_BY_HANDLE',
377 FALSE);
378 fnd_message.set_token ('RESULT',
379 nvl(to_char(result), 'NULL'), FALSE);
380
381 end if;
382
383 end check_process_status_by_handle;
384
385 /*------------------------------------------------------------------|
386 | Bug 2093806: The purpose of Check_Process_Status_By_Ids is to |
387 | provide an API which can be used for a process (such as an Apps |
388 | form, OAM procedure or ICM in PMON cycle) to check the lock of |
389 | another process (such as ICM, CM, IM, CRM, TM). This procedure |
390 | uses the Application Id, Queue Id, and O/S Pid to construct the |
391 | named lock for the request. The procedure will immediately time |
392 | out if the RDBMS does not grant the lock. If process is alive, |
393 | alive = NTRUE(1). Otherwise, alive = NFALSE(0). Calling process |
394 | can analyze the "result" parameter to determine why/why not the |
395 | lock was granted, if desired. |
396 |------------------------------------------------------------------*/
397 procedure check_process_status_by_ids (apid in number,
398 qid in number,
399 pid in number,
400 result out nocopy number,
401 alive out nocopy number) is
402
403 hndl varchar2(128) := NULL;
404 dummy number;
405
406 begin
407
408 /*-------------------------------------------------------+
409 | Get handle for the request |
410 +-------------------------------------------------------*/
411 get_lk_handle (apid => apid,
412 qid => qid,
413 pid => pid,
414 hndl => hndl,
415 e_code => dummy);
416
417 /*-------------------------------------------------------+
418 | Check the process' status by the handle |
419 +-------------------------------------------------------*/
420 check_process_status_by_handle (hndl => hndl,
421 result => result,
422 alive => alive);
423
424 end check_process_status_by_ids;
425
426
427 -- ### OVERLOADED ###
428 /*------------------------------------------------------------------|
429 | Obsolete: Please use Request_Session_Lock, Check_Process_Status |
430 | or Check_Process_Status_By_Handle |
431 +------------------------------------------------------------------*/
432 procedure request_lock (apid in number,
433 qid in number,
434 pid in number,
435 lk in out nocopy varchar2,
436 hndl in out nocopy varchar2,
437 e_code in out nocopy number) is
438
439 result number;
440 count number;
441
442 begin
443 e_code := NTRUE;
444
445 if (apid = 0 and qid = 1 and pid = 0) then
446 lk := FNDCPLK || 'ICM';
447 else
451 dbms_lock.allocate_unique (lk, hndl, EON);
448 lk := FNDCPLK || apid || '_' || qid || '_' || pid;
449 end if;
450
452 result := dbms_lock.request (hndl, 6, 0); -- ULX lock
453
454 if (result in (0, 4)) then -- Success or own lock
455 return;
456 elsif (result in (1, 2)) then -- Timeout or deadlock
457
458 /* ------------------------------------------------------- */
459 /* Bug 1967288: Timout occasionally returned when manager */
460 /* log backs into database with afpcsq(). So, try up to 2 */
461 /* more times with 5 second timeout. */
462 /* We cannot wait much longer since Forms and OAM use this */
463 /* version of Request_Lock to check each manager's status */
464 /* This is a short-term fix until more robust restructuring*/
465 /* can be done with lock management procedures, functions. */
466 /* ------------------------------------------------------- */
467
468 for count in 1..2 loop
469 result := dbms_lock.request (hndl, 6, 5);
470 exit when result in (0, 4);
471 end loop;
472
473 if (result in (0, 4)) then -- Success or own lock
474 return;
475 elsif (result in (1, 2)) then -- Timeout or deadlock
476 lk := null;
477 hndl := null;
478 else -- Bad lock handle or bad param
479 lk := null;
480 hndl := null;
481 e_code := NFALSE;
482 end if;
483
484 else -- Bad lock handle or bad param
485 lk := null;
486 hndl := null;
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', FALSE);
496 end request_lock;
497
498 -- ### OVERLOADED ###
499 /*------------------------------------------------------------------|
500 | Obsolete: Please use Request_Session_Lock, Check_Process_Status |
501 | or Check_Process_Status_By_Handle |
502 +------------------------------------------------------------------*/
503 procedure request_lock (hndl in varchar2,
504 status in out nocopy number,
505 e_code in out nocopy number) is
506
507 result number;
508
509 begin
510 e_code := NTRUE;
511
512 result := dbms_lock.request (hndl, 6, 0); -- ULX lock
513
514 if (result in (0, 4)) then -- Success or own lock
515 status := NTRUE;
516 elsif (result in (1, 2)) then -- Timeout or deadlock
517 status := NFALSE;
518 else -- Bad lock handle or bad param
519 e_code := NFALSE;
520 end if;
521
522 exception
523 when others then
524 e_code := NFALSE;
525 fnd_message.set_name ('FND', 'CP-Generic oracle error');
526 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
527 fnd_message.set_token ('ROUTINE', 'REQUEST_LOCK (hndl)', FALSE);
528 end request_lock;
529
530
531 procedure release_lock (hndl in varchar2,
532 e_code in out nocopy number) is
533
534 result number;
535
536 begin
537 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
538 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
539 'fnd.plsql.FND_DCP.RELEASE_LOCK',
540 'release_lock called for hndl='||hndl);
541 end if;
542 result := dbms_lock.release (hndl);
543
544 if ((result = 0) or (result = 4)) then
545 e_code := NTRUE;
546 else
547 e_code := NFALSE;
548 end if;
549 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
550 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
551 'fnd.plsql.FND_DCP.RELEASE_LOCK',
552 'release result='||to_char(result));
553 end if;
554 exception
555 when others then
556 e_code := NFALSE;
557 fnd_message.set_name ('FND', 'CP-Generic oracle error');
558 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
559 fnd_message.set_token ('ROUTINE', 'release_lock', FALSE);
560 end release_lock;
561
562
563 procedure clean_mgrs (e_code in out nocopy number) is
564
565 begin
566 null;
567 end clean_mgrs;
568
569
570 procedure monitor_icm (hndl in out nocopy varchar2,
571 up in out nocopy number,
572 logf in out nocopy varchar2,
573 node in out nocopy varchar2,
574 inst in out nocopy varchar2,
575 cpid in out nocopy number,
576 mthd in number, -- PMON method
577 e_code in out nocopy number) is
578
579 lk varchar2(50);
580 result number;
581 bad_lock exception;
582 time_interval number; -- PMON method = TSTAMP
583 time_difference number; -- PMON method = TSTAMP
584 dummy number;
585
586 begin
587 e_code := NTRUE;
588
589 if (mthd = TSTAMP) then -- PMON method = TSTAMP
590 cpid := get_pid_time (time_interval,
591 time_difference,
592 0, 1, 'A', 'C', 'M', 'K');
593
594 if (time_difference < THRSHLD * time_interval) then
595 up := NTRUE;
596 rollback;
597 return;
598 end if;
599
600 else -- PMON method = DBLOCK
601 cpid := get_icm_info (logf, node, inst);
602
603 if (hndl is not null) then
604 result := dbms_lock.request (hndl, 6, 0); -- ULX lock
605
606 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
610 end if;
607 fnd_log.string(FND_LOG.LEVEL_EVENT,
608 'fnd.plsql.FND_DCP.MONITOR_ICM',
609 'lock request result='||to_char(result)||' for hndl='||hndl);
611
612
613 if (result in (1, 2)) then -- Timeout or deadlock
614 up := NTRUE;
615 return;
616 end if;
617
618 end if;
619
620 end if; -- PMON method TSTAMP/DBLOCK
621
622 if (cpid is null) then
623 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
624 fnd_log.string(FND_LOG.LEVEL_EVENT, 'fnd.plsql.FND_DCP.MONITOR_ICM',
625 'get_icm_info returned cpid NULL');
626 end if;
627
628 up := NTRUE;
629 hndl := null;
630 rollback;
631 return;
632
633 else
634 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
635 fnd_log.string(FND_LOG.LEVEL_EVENT, 'fnd.plsql.FND_DCP.MONITOR_ICM',
636 'get_icm_info returned cpid='||to_char(cpid));
637 end if;
638
639 lk := FNDCPLK || '0_1_' || cpid;
640 dbms_lock.allocate_unique (lk, hndl, EON);
641 result := dbms_lock.request (hndl, 6, 0); -- ULX lock
642
643 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
644 fnd_log.string(FND_LOG.LEVEL_EVENT, 'fnd.plsql.FND_DCP.MONITOR_ICM',
645 'new lock name = '||lk);
646 fnd_log.string(FND_LOG.LEVEL_EVENT, 'fnd.plsql.FND_DCP.MONITOR_ICM',
647 'lock request result='||to_char(result)||' for hndl='||hndl);
648 end if;
649
650 if (result = 0) then -- Success
651 select 1 into dummy
652 from fnd_concurrent_processes
653 where concurrent_process_id = cpid
654 for update nowait;
655
656 update fnd_concurrent_processes
657 set process_status_code = decode (process_status_code,
658 'A', 'K',
659 'M', 'S',
660 process_status_code)
661 where concurrent_process_id = cpid;
662 up := NFALSE;
663 commit;
664 elsif (result in (1, 2)) then -- Timeout or deadlock
665 up := NTRUE;
666 elsif (result = 4) then -- Own the lock (shouldn't happen)
667 up := NFALSE;
668 else -- Bad lock handle or bad param
669 raise bad_lock;
670 end if;
671
672 end if;
673
674 exception
675 when others then
676 e_code := NFALSE;
677 rollback;
678 fnd_message.set_name ('FND', 'CP-Generic oracle error');
679 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
680 fnd_message.set_token ('ROUTINE', 'monitor_icm', FALSE);
681 end monitor_icm;
682
683
684 procedure monitor_im (apid in number,
685 qid in number,
686 pid in number,
687 cnode in varchar2,
688 status in out nocopy number,
689 e_code in out nocopy number) is
690
691 max_procs number;
692 run_procs number;
693 ccode varchar2(1); -- Control code
694 tnode varchar2(30); -- Target node
695
696 begin
697 e_code := NTRUE;
698
699 select max_processes,
700 running_processes,
701 control_code,
702 target_node
703 into max_procs,
704 run_procs,
705 ccode,
706 tnode
707 from fnd_concurrent_queues
708 where application_id = apid
709 and concurrent_queue_id = qid;
710
711 if ((max_procs < run_procs) or
712 (cnode <> tnode)) then -- Migrate
713 status := NFALSE; -- Exit
714 else
715 status := NTRUE; -- Stay up
716 end if;
717
718 exception
719 when others then
720 e_code := NFALSE;
721 fnd_message.set_name ('FND', 'CP-Generic oracle error');
722 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
723 fnd_message.set_token ('ROUTINE', 'monitor_im', FALSE);
724 end monitor_im;
725
726
727 procedure reassign_lkh (e_code in out nocopy number) is
728
729 cursor c1 is
730 select queue_application_id,
731 concurrent_queue_id,
732 concurrent_process_id,
733 rowid
734 from fnd_concurrent_processes
735 where manager_type between 1 and 5
736 and process_status_code in ('A', 'C')
737 order by concurrent_process_id;
738
739 apid number(15);
740 qid number(15);
741 pid number(15);
742 rid ROWID;
743 hndl varchar2(128);
744
745 begin
746 e_code := NTRUE;
747
748 open c1;
749
750 loop
751 fetch c1 into apid, qid, pid, rid;
752 exit when c1%notfound;
753
754 dbms_lock.allocate_unique (FNDCPLK ||
755 apid || '_' || qid || '_' || pid,
756 hndl, EON);
757
758 update fnd_concurrent_processes
759 set lk_handle = hndl
760 where rowid = rid;
761
762 end loop;
763
764 close c1;
765 commit;
766
767 exception
768 when others then
769 e_code := NFALSE;
770 fnd_message.set_name ('FND', 'CP-Generic oracle error');
771 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
772 fnd_message.set_token ('ROUTINE', 'reassign_lkh', FALSE);
773 end reassign_lkh;
774
775
776 /* function get_inst_num
777 *
778 * This function is used to determine the OPS instance
779 * to which a manager should "specialize". For Parallel
780 * Concurrent Processing, we want the a manager to
784 * If the manager was started on its primary node, then
781 * service requests only for the instance associated with
782 * its primary node.
783 *
785 * the current instance number is retrieved from v$instance,
786 * stored in fnd_concurrent_queues, and returned to the caller.
787 *
788 * If the manager was started on its secondary node, then
789 * the instance number is retrieved from fnd_concurrent_queues
790 *
791 * Parameters:
792 * queue_appl_id - Concurrent queue application ID.
793 * queue_id - Concurrent queue ID.
794 * current_node - Node where manager is running.
795 *
796 * Returns:
797 * An OPS instance number.
798 *
799 * Alters:
800 * The table fnd_concurrent_queues may be updated.
801 *
802 * Assumptions:
803 * This function assumes that the node names stored in
804 * fnd_concurrent_queues exactly match those in the manager's
805 * sysinfo structure. (i.e. Both fully qualified, or both not
806 * fully qualified)
807 *
808 * Error conditions:
809 *
810 * All other exceptions are unhandled.
811 */
812
813 function get_inst_num (queue_appl_id in number,
814 queue_id in number,
815 current_node in varchar2)
816 return number is
817 primary_node fnd_concurrent_queues.node_name%type;
818 secondary_node fnd_concurrent_queues.node_name2%type;
819 inst_num number; /* OPS Instance Number */
820 begin
821 /* Are we on the primary node?*/
822 select upper(node_name), upper(node_name2)
823 into primary_node, secondary_node
824 from fnd_concurrent_queues
825 where concurrent_queue_id = queue_id
826 and application_id = queue_appl_id;
827
828
829 if (upper(current_node) = primary_node) then /* PCP Primary node */
830 /* Get inst number from v$instance */
831 select instance_number
832 into inst_num
833 from v$instance;
834
835 /* Store it into fnd_concurrent_queues. */
836
837 update fnd_concurrent_queues
838 set instance_number = inst_num
839 where application_id = queue_appl_id
840 and concurrent_queue_id = queue_id;
841
842 /* Update the global */
843 FND_CONC_GLOBAL.Override_OPS_INST_NUM(inst_num);
844
845 else /* we aren't on the primary node...maybe we still know the inst num*/
846 select INSTANCE_NUMBER
847 into inst_num
848 from fnd_concurrent_queues
849 where application_id = queue_appl_id
850 and concurrent_queue_id = queue_id;
851
852 /* Update the global...if not null */
853 if (inst_num is not null) then
854 FND_CONC_GLOBAL.Override_OPS_INST_NUM(inst_num);
855 end if;
856 end if;
857
858 return inst_num;
859 end;
860
861 /* function target_node_mgr_chk
862 * If a request is targeted to a specific node, the concurrent
863 * manager will use this function in his request query (afpgrq)
864 * to filter it out if it doesn't meet any of the following conditions:
865 * a) request's target node is the same as manager's current node
866 * b) request's target node is different from manager's current node, but the
867 * FND_NODES status is 'N' or node_mode is not 'O' (online).
868 * c) There are no managers specialized to run this request on request's
869 * target node
870 *
871 * Parameters:
872 * request_id - id of request that is targeted to a secific node
873 *
874 * Returns:
875 * NTRUE/TRUE/1 if this request can appear in query results
876 * NFALSE/FALSE/0 if this request should be filtered from query results
877 *
878 * Assumptions:
879 * The manager's target_node in fnd_concurrent_queues is it's current
880 * node. This should always be true for active managers in afpgrq.
881 *
882 * Error conditions:
883 *
884 * All other exceptions are unhandled.
885 */
886
887 function target_node_mgr_chk (req_id in number) return number is
888
889 cursor mgr_cursor (rid number, qappid number, qid number) is
890 select queue_application_id, concurrent_queue_id,
891 running_processes, max_processes,
892 decode(control_code,
893 'T','N', -- Abort
894 'X','N', -- Aborted
895 'D','N', -- Deactivate
896 'E','N', -- Deactivated
897 'Y') active,
898 target_node
899 from fnd_concurrent_worker_requests
900 where request_id = rid
901 and not((queue_application_id = 0)
902 and (concurrent_queue_id in (1,4)))
903 and not((queue_application_id = qappid)
904 and (concurrent_queue_id = qid ));
905
906 my_q_appl_id number := FND_GLOBAL.queue_appl_id ;
907 my_q_id number := FND_GLOBAL.conc_queue_id;
908 req_node varchar2(30) := null;
909 dummy number := 0;
910 retval number := NFALSE;
911
912 begin
913
914 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
915 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
916 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
917 'Enter TARGET_NODE_MGR_CHK for manager ('||
918 to_char(my_q_appl_id)|| '/'||my_q_id||
919 ') and request_id '|| to_char(req_id));
920 end if;
921
922 /* Retrieve the request's target node */
923 select node_name1
924 into req_node
925 from fnd_concurrent_requests
929 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
926 where request_id = req_id;
927
928 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
930 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
931 'Target node for request '||to_char(req_id)||' is '||
932 NVL(req_node,'NULL'));
933 end if;
934
935 /* If request has no target node, okay to run it */
936 if (req_node is null) then
937 retval := NTRUE;
938 end if;
939
940 /* Check if the request node matches my node */
941 if (retval = NFALSE) then
942 if (my_node is NULL) then
943 select target_node
944 into my_node
945 from fnd_concurrent_queues
946 where application_id = my_q_appl_id
947 and concurrent_queue_id = my_q_id;
948 end if;
949
950 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
951 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
952 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
953 'Manager node is '||NVL(my_node,'NULL'));
954 end if;
955
956 /* If request target node matches my node, okay to run it */
957 if (req_node = my_node) then
958 retval := NTRUE;
959 end if;
960 end if;
961
962 /* Check if the request node is down */
963 if (retval = NFALSE) then
964 select count(*)
965 into dummy
966 from fnd_nodes
967 where node_name = req_node
968 and (node_mode <> 'O'
969 or status <> 'Y');
970
971 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
972 if dummy >= 1 then
973 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
974 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
975 'Request target node '||req_node||' is DOWN');
976 else
977 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
978 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
979 'Request target node '||req_node||' is UP');
980 end if;
981 end if;
982
983 /* If request target node is down, okay to run it */
984 if (dummy >= 1) then
985 retval := NTRUE;
986 fnd_message.set_name ('FND', 'CONC-REQ NODE NOT HONORED');
987 fnd_message.set_token ('REQID', to_char(req_id), FALSE);
988 fnd_message.set_token ('NODE', req_node, FALSE);
989 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
990 fnd_log.message(FND_LOG.LEVEL_EVENT,
991 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
992 FALSE);
993 end if;
994 end if;
995 end if;
996
997 /* Check if a manager is available to run this request on target node */
998 /* The first available manager we find, exit and return NFALSE */
999 if (retval = NFALSE) then
1000 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1001 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1002 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
1003 'Enter loop to determine if another manager can run');
1004 end if;
1005 for mgr_rec in mgr_cursor(req_id, my_q_appl_id, my_q_id) loop
1006 if ((mgr_rec.active = 'Y')
1007 and (mgr_rec.max_processes > 0)
1008 and (mgr_rec.running_processes > 0)
1009 and mgr_rec.target_node = req_node) then
1010 /* Here is an available manager, no need to continue. */
1011 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1012 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1013 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
1014 'Another manager ('||
1015 to_char(mgr_rec.queue_application_id )||'/'||
1016 to_char(mgr_rec.concurrent_queue_id)||
1017 ') can run this request');
1018 end if;
1019 retval := NFALSE;
1020 exit;
1021 else
1022 retval := NTRUE;
1023 fnd_message.set_name ('FND', 'CONC-REQ NODE NOT HONORED');
1024 fnd_message.set_token ('REQID', to_char(req_id), FALSE);
1025 fnd_message.set_token ('NODE', req_node, FALSE);
1026 if( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1027 fnd_log.message(FND_LOG.LEVEL_EVENT,
1028 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
1029 FALSE);
1030 end if;
1031 end if;
1032 end loop;
1033 end if;
1034
1035 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1036 if retval = NTRUE then
1037 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1038 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
1039 'Returning retval= NTRUE');
1040 else
1041 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1042 'fnd.plsql.FND_DCP.TARGET_NODE_MGR_CHK',
1043 'Returning retval= NFALSE');
1044 end if;
1045 end if;
1046 return retval;
1047
1048 end target_node_mgr_chk;
1049
1050 --
1051 -- Name
1052 -- is_dcp
1053 -- Purpose
1054 -- Returns TRUE if the environment has multiple CP nodes,
1055 -- FALSE if not.
1056 --
1057 -- Parameters:
1058 -- None
1059 --
1060 -- Returns:
1061 -- NTRUE/TRUE/1 - environment is DCP
1062 -- NFALSE/FALSE/0 - environment is non-DCP
1063 --
1064 function is_dcp return number is
1065
1066 node_count number := 0;
1067 begin
1068
1069 select count(*)
1070 into node_count
1071 from fnd_nodes
1072 where node_name <> 'AUTHENTICATION'
1073 and support_cp = 'Y';
1074
1075 if (node_count > 1) then
1076 return NTRUE;
1077 else
1078 return NFALSE;
1079 end if;
1080
1081 end is_dcp;
1082
1083
1084 end FND_DCP;
[Home] [Help]