[Home] [Help]
PACKAGE BODY: APPS.FND_AMP_PRIVATE
Source
1 package body FND_AMP_PRIVATE as
2 /* $Header: AFCPAMPB.pls 120.3.12000000.3 2007/10/08 19:51:16 ckclark ship $ */
3
4 --
5 -- Package
6 -- FND_AMP_PRIVATE
7 -- Purpose
8 -- Utilities for the Applications Management Pack
9 -- History
10 --
11 -- PRIVATE VARIABLES
12 --
13
14 req_phase varchar2(80);
15 req_status varchar2(80);
16 ran_get_phase number := -1;
17 ran_get_status number := -1;
18
19 -- Exceptions
20
21 -- Exception Pragmas
22
23 --
24 -- PRIVATE FUNCTIONS
25 --
26
27 --
28 -- Function
29 -- process_alive
30 -- Purpose
31 -- Return TRUE if the process is alive,
32 -- FALSE otherwise.
33 -- Arguments
34 -- pid - process ID
35 -- Notes
36 -- Return FALSE on error.
37 --
38 --
39 function process_alive(pid in number) return boolean is
40
41 alive boolean;
42
43 begin
44
45 alive := FND_CONC.process_alive(pid);
46 return alive;
47
48 end process_alive;
49
50
51 -- This routine handles the cases of restarting the parent
52 -- request (if necessary) of cancelled child requests, and
53 -- canceling child jobs.
54 -- Used in cancel_request routine.
55 -- This is a local routine
56 procedure cancel_subrequests( request_id in number,
57 parent_request_id in number,
58 is_sub_request in varchar2,
59 has_sub_request in varchar2
60 ) is
61 begin
62 -- When a request is deleted, restart parent (if this
63 -- is the last subrequest) and terminate subrequests
64 -- (if this is a parent)
65 if (is_sub_request = 'Y') then
66 -- Lock the parent so that no other process can
67 -- perform the same actions to follow (including CM).
68 -- If parent status is W (Paused), no need to update.
69 if (fnd_conc_request_pkg.lock_parent (parent_request_id)) then
70 -- If request to delete is the last pending child,
71 -- set paused parent request to pending for restart
72 -- Need to maintain the parent-child order of
73 -- request locking, so update parent first before
74 -- deleting child jobs.
75 -- Status codes between 'I' and 'T' are pending or
76 -- running. They include 'I' Pending Normal,
77 -- 'Q' Pending Standby, 'R' Running Normal, and
78 -- 'T' Running Terminating.
79 if (fnd_conc_request_pkg.restart_parent (
80 request_id,
81 parent_request_id,
82 fnd_global.user_id)) then
83 fnd_message.set_name (
84 'FND',
85 'CONC-Restart parent request');
86 end if;
87 end if;
88 end if;
89
90 if (has_sub_request = 'Y') then
91 -- Update status of children to terminating,
92 -- terminated or cancelled unless they are already
93 -- complete or terminating.
94 fnd_conc_request_pkg.delete_children (
95 request_id,
96 fnd_global.user_id);
97 end if;
98 end cancel_subrequests;
99
100
101 --
102 -- PUBLIC FUNCTIONS
103 --
104
105 --
106 -- Name
107 -- why_wait
108 -- Purpose
109 -- Returns a translated string describing the reaons why request1
110 -- is waiting on request2. If request1 is not waiting on request2,
111 -- then null is returned.
112 --
113 -- Request2 must be a pending or running program queued ahead of
114 -- request1, in the same conflict domain, with a queue_method_code of
115 -- 'B'. No checks are made on these constraints,
116 -- since this procedure is part of a data gatherer select
117 -- statement that must run fast.
118 --
119 --
120 function why_wait (request_id1 in number,
121 single_thread_flag1 in varchar2,
122 request_limit1 in varchar2,
123 requested_by1 in number,
124 program_appl1 in number,
125 program_id1 in number,
126 status_code1 in varchar2,
127 request_id2 in number,
128 single_thread_flag2 in varchar2,
129 request_limit2 in varchar2,
130 requested_by2 in number,
131 run_alone_flag2 in varchar2,
132 program_appl2 in number,
133 program_id2 in number,
134 is_sub_request2 in varchar2,
135 parent_request2 in number) return varchar2 is
136 c number;
137 begin
138
139 /* Is request 2 a run alone program? */
140 if (run_alone_flag2 = 'Y') then
141 fnd_message.set_name('FND', 'CONC-RUN ALONE AHEAD');
142 return fnd_message.get;
143 end if;
144
145 /* Are they single threaded? */
146 if (single_thread_flag1 = 'Y' and single_thread_flag2 = 'Y' and
147 requested_by1 = requested_by2) then
148 fnd_message.set_name('FND', 'CONC-SINGLE THREAD AHEAD');
149 fnd_message.set_token('REQUEST_ID', request_id1);
150 return fnd_message.get;
151 end if;
152
153 /* Are they incompatible? */
154 select count(*) into c
155 from fnd_concurrent_program_serial
156 where running_application_id = program_appl2
157 and running_concurrent_program_id = program_id2
158 and to_run_application_id = program_appl1
159 and to_run_concurrent_program_id = program_id1;
160
161 if (c > 0) then
162 fnd_message.set_name('FND', 'CONC-INCOMPATIBLE AHEAD');
163 fnd_message.set_token('REQUEST_ID', request_id1);
164 return fnd_message.get;
165 end if;
166
167 /* Is this the wating request1 the parent of request2? */
168 if (status_code1 = 'W' and is_sub_request2 = 'Y'
169 and parent_request2 = request_id1) then
170 fnd_message.set_name('FND', 'CONC-SUB-REQUEST');
171 fnd_message.set_token('REQUEST_ID', request_id1);
172 return fnd_message.get;
173 end if;
174
175
176 /* Finally check user limit */
177 if (request_limit1 = 'Y' and request_limit2 = 'Y' and
178 requested_by1 = requested_by2) then
179 fnd_message.set_name('FND', 'CONC-LIMITED REQUESTS');
180 return fnd_message.get;
181 end if;
182
183 return NULL;
184 exception
185 when others then
186 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
187 fnd_message.set_token('ROUTINE', 'FND_AMP_PRIVATE.WHY_WAIT');
188 fnd_message.set_token('ERRNO', SQLCODE);
189 fnd_message.set_token('REASON', SQLERRM);
190 return fnd_message.get;
191 end why_wait;
192
193
194 --
195 -- Name
196 -- get_phase_and_status
197 -- Purpose
198 -- Used by get_phase and get_status to get the
199 -- phase and status descriptions.
200 --
201 procedure get_phase_and_status(pcode in char,
202 scode in char,
203 hold in char,
204 enbld in char,
205 stdate in date,
206 rid in number) is
207 upcode varchar2(1);
208 uscode varchar2(1);
209 ltype varchar2(32);
210 begin
211
212 upcode := pcode;
213 uscode := scode;
214
215 if ((pcode = 'P') and (hold = 'Y')) then
216 upcode := 'I';
217 uscode := 'H';
218 elsif ((pcode = 'P') and (enbld = 'N')) then
219 upcode := 'I';
220 uscode := 'U';
221 elsif ((pcode = 'P') and (scode = 'A')) then
222 upcode := 'P';
223 uscode := 'A';
224 elsif (pcode = 'P') then
225 if ((stdate > SYSDATE) or (scode = 'P')) then
226 upcode := 'P';
227 uscode := 'P';
228 else
229 select 'I',
230 'M'
231 into upcode,
232 uscode
233 from sys.dual
234 where not exists (select null
235 from fnd_concurrent_worker_requests
236 where request_id = rid
237 and running_processes > 0
238 and (not (queue_application_id = 0
239 and concurrent_queue_id in (1,4))
240 or queue_control_flag = 'Y'));
241 end if;
242 end if;
243
244 raise no_data_found;
245
246 exception
247 when no_data_found THEN
248
249 ltype := 'CP_PHASE_CODE';
250 select meaning into req_phase
251 from fnd_lookups
252 where lookup_code = upcode
253 and lookup_type = ltype;
254
255 ltype := 'CP_STATUS_CODE';
256 select meaning into req_status
257 from fnd_lookups
258 where lookup_code = uscode
259 and lookup_type = ltype;
260
261 end get_phase_and_status;
262
263
264
265 --
266 -- Name
267 -- get_status
268 -- Purpose
269 -- Returns a translated status description.
270 --
271 function get_phase (pcode in char,
272 scode in char,
273 hold in char,
274 enbld in char,
275 stdate in date,
276 rid in number) return varchar2 is
277 begin
278
279 /* Did we already run get_status for this request?
280 * If so, then return the cached phase value.
281 */
282 if (ran_get_status = rid) then
283 ran_get_status := -1;
284 return req_phase;
285 end if;
286
287 /* Get phase and status. Return phase. */
288 get_phase_and_status(pcode, scode, hold, enbld, stdate, rid);
289 ran_get_phase := rid;
290 return req_phase;
291
292 exception
293 when others then
294 return 'ORA'||SQLCODE;
295 end;
296
297
298
299 --
300 -- Name
301 -- get_status
302 -- Purpose
303 -- Returns a translated status description.
304 --
305 function get_status (pcode in char,
306 scode in char,
307 hold in char,
308 enbld in char,
309 stdate in date,
310 rid in number) return varchar2 is
311 begin
312 /* Did we already run get_phase for this request?
313 * If so, then return the cached status value.
314 */
315 if (ran_get_phase = rid) then
316 ran_get_phase := -1;
317 return req_status;
318 end if;
319
320 /* Get phase and status. Return status. */
321 get_phase_and_status(pcode, scode, hold, enbld, stdate, rid);
322 ran_get_status := rid;
323 return req_status;
324
325 exception
326 when others then
327 return 'ORA'||SQLCODE;
328 end;
329
330
331
332 --
333 -- Name
334 -- kill_session
335 -- Purpose
336 -- Kills a session given an audsid and instance id
337 --
338 -- Parameters:
339 -- audsid - ID of session to kill.
340 -- message - Oracle error message.
341 -- inst_id - Instance ID of session.
342 --
343 -- Returns:
344 -- 0 - Oracle error. Check message.
345 -- 1 - Session not found.
346 -- 2 - Success.
347 --
348 function kill_session (audsid in number,
349 message in out NOCOPY varchar2,
350 inst_id in number default 1) return number is
351 kcursor varchar2(75); /* Cursor string for dbms_sql */
352 cid number; /* Cursor ID for dbms_sql */
353 ssid number;
354 sserial# number;
355 dummy number;
356 begin
357 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
358 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
359 'fnd.plsql.FND_AMP_PRIVATE.kill_session',
360 'audsid: ' ||audsid||', inst_id=' ||inst_id);
361 end if;
362
363 begin
364 select sid, serial#
365 into ssid, sserial#
366 from gv$session
367 where kill_session.audsid = gv$session.audsid
368 and kill_session.inst_id = gv$session.inst_id;
369 exception
370 when no_data_found then
371 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
372 fnd_log.string(FND_LOG.LEVEL_ERROR,
373 'fnd.plsql.FND_AMP_PRIVATE.kill_session',
374 'Session (audsid) ' ||audsid||' in instance '||inst_id||' not found');
375 end if;
376 return 1;
377 when others then
378 raise;
379 end;
380
381 kcursor := 'alter system kill session '''|| to_char(ssid) || ',' ||
382 to_char(sserial#)||'''';
383 begin
384 cid := dbms_sql.open_cursor;
385 dbms_sql.parse(cid, kcursor, dbms_sql.v7);
386 dummy := dbms_sql.execute(cid);
387 dbms_sql.close_cursor(cid);
388 exception
389 when others then
390 if SQLCODE = -30 then
391 return 1;
392 else
393 raise;
394 end if;
395 end;
396
397 return 2;
398
399 exception
400 when others then
401 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
402 fnd_message.set_token('ROUTINE', 'FND_AMP_PRIVATE.KILL_SESSION');
403 fnd_message.set_token('ERRNO', SQLCODE);
404 fnd_message.set_token('REASON', SQLERRM);
405 message := fnd_message.get;
406 return 0;
407 end;
408
409
410 --
411 -- Name
412 -- cancel_request
413 -- Purpose
414 -- Cancel or terminate a request.
415 -- Make sure fnd_global.apps_initialize was called first.
416 --
417 -- Parameters:
418 -- req_id - ID of request to cancel.
419 -- message - Error message.
420 --
421 -- Returns:
422 -- 0 - Oracle error. Check message.
423 -- 1 - Could not lock request row
424 -- 2 - Request has already completed.
425 -- 3 - Cannot cancel. Manager dead.
426 -- 4 - Request cancelled.
427 -- 5 - Request marked for termination.
428 --
429 function cancel_request ( req_id in number,
430 message in out NOCOPY varchar2) return number is
431 PRAGMA AUTONOMOUS_TRANSACTION;
432 req_phase varchar2(1);
433 req_status varchar2(1);
434 new_status varchar2(1);
435 is_sub_req varchar2(1);
436 has_sub_req varchar2(1);
437 mgr_proc number;
438 current_user VARCHAR2(100);
439 who_cancelled varchar2(255);
440 par_req_id number;
441
442 begin
443
444 begin
445 select phase_code, status_code,
446 is_sub_request, has_sub_request,
447 controlling_manager, parent_request_id
448 into req_phase, req_status,
449 is_sub_req, has_sub_req,
450 mgr_proc, par_req_id
451 from fnd_concurrent_requests
452 where request_id = req_id
456 if (SQLCODE = -54) then
453 for update of phase_code nowait;
454 exception
455 when others then
457 return 1;
458 else
459 raise;
460 end if;
461 end;
462
463 if (req_phase = 'C') then
464 rollback;
465 return 2;
466 end if;
467
468 if ((req_phase = 'R') and
469 (not process_alive(mgr_proc))) then
470 rollback;
471 return 3;
472 end if;
473
474 if (req_status = 'R') then
475 new_status := 'T';
476 elsif (req_status in ('W', 'B')) then
477 new_status := 'X';
478 else
479 new_status := 'D';
480
481 -- Who cancelled the request
482 current_user := FND_PROFILE.VALUE('USERNAME');
483 fnd_message.set_name ('FND', 'CONC-Cancelled by');
484 fnd_message.set_token ('USER', current_user);
485 who_cancelled := fnd_message.get;
486 end if;
487
488
489 update fnd_concurrent_requests
490 set phase_code = decode(new_status, 'T', phase_code, 'C'),
491 status_code = new_status,
492 completion_text = who_cancelled,
493 last_update_date = sysdate,
494 last_updated_by = fnd_global.user_id
495 where request_id = req_id;
496
497 cancel_subrequests(req_id, par_req_id, is_sub_req, has_sub_req);
498
499 commit;
500
501 if (new_status = 'T') then
502 return 5;
503 else
504 return 4;
505 end if;
506
507 exception
508 when others then
509 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
510 fnd_message.set_token('ROUTINE', 'FND_AMP_PRIVATE.TERMINATE_REQUEST');
511 fnd_message.set_token('ERRNO', SQLCODE);
512 fnd_message.set_token('REASON', SQLERRM);
513 message := fnd_message.get;
514 rollback;
515 return 0;
516 end;
517
518
519 --
520 -- Name
521 -- toggle_hold
522 -- Purpose
523 -- Toggles the hold flag for a concurrent request.
524 -- Make sure fnd_global.apps_initialize was called first.
525 --
526 -- Parameters:
527 -- req_id - ID of request to toggle.
528 -- message - Error message.
529 --
530 -- Returns:
531 -- 0 - Oracle error. Check message.
532 -- 1 - Could not lock request row
533 -- 2 - Request has already started.
534 -- 3 - Request placed on hold.
535 -- 4 - Request hold removed.
536 --
537 function toggle_request_hold ( req_id in number,
538 message in out NOCOPY varchar2) return number is
539 req_phase varchar2(1);
540 req_hold varchar2(1);
541 req_type varchar2(1);
542 new_hold varchar2(1);
543 retval number;
544 begin
545 savepoint fnd_amp_private_hold_req;
546
547 begin
548 select phase_code, hold_flag, request_type
549 into req_phase, req_hold, req_type
550 from fnd_concurrent_requests
551 where request_id = req_id
552 for update of phase_code nowait;
553 exception
554 when others then
555 if (SQLCODE = -54) then
556 return 1;
557 else
558 raise;
559 end if;
560 end;
561
562 if (req_phase <> 'P' ) then
563 -- to fix bug # 4761862
564 -- Request Sets in Running phase can be hold.
565 if ( req_type <> 'M' or req_phase <> 'R') then
566 rollback to fnd_amp_private_hold_req;
567 return 2;
568 end if;
569 end if;
570
571 if (req_hold = 'Y') then
572 new_hold := 'N';
573 retval := 4;
574 else
575 new_hold := 'Y';
576 retval := 3;
577 end if;
578
579
580 update fnd_concurrent_requests
581 set hold_flag = new_hold,
582 last_update_date = sysdate,
583 last_updated_by = fnd_global.user_id
584 where request_id = req_id;
585
586 commit;
587
588 return retval;
589
590 exception
591 when others then
592 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
593 fnd_message.set_token('ROUTINE', 'FND_AMP_PRIVATE.TOGGLE_REQUEST_HOLD');
594 fnd_message.set_token('ERRNO', SQLCODE);
595 fnd_message.set_token('REASON', SQLERRM);
596 message := fnd_message.get;
597 rollback to fnd_amp_private_hold_req;
598 return 0;
599 end;
600
601
602 --
603 -- Name
604 -- alter_priority
605 -- Purpose
606 -- Alters the priority for a concurrent request.
607 -- Make sure fnd_global.apps_initialize was called first.
608 --
609 -- Parameters:
610 -- req_id - ID of request to toggle.
611 -- priority - New priority.
612 -- message - Error message.
613 --
614 -- Returns:
615 -- 0 - Oracle error. Check message.
616 -- 1 - Could not lock request row
617 -- 2 - Request has already started.
621 new_priority in number,
618 -- 3 - Request priority altered.
619 --
620 function alter_request_priority ( req_id in number,
622 message in out NOCOPY varchar2) return number is
623 req_phase varchar2(1);
624
625 begin
626 savepoint fnd_amp_private_alter_priority;
627
628 begin
629 select phase_code
630 into req_phase
631 from fnd_concurrent_requests
632 where request_id = req_id
633 for update of phase_code nowait;
634 exception
635 when others then
636 if (SQLCODE = -54) then
637 return 1;
638 else
639 raise;
640 end if;
641 end;
642
643 if (req_phase <> 'P') then
644 rollback to fnd_amp_private_alter_priority;
645 return 2;
646 end if;
647
648
649 update fnd_concurrent_requests
650 set priority = new_priority,
651 last_update_date = sysdate,
652 last_updated_by = fnd_global.user_id
653 where request_id = req_id;
654
655 commit;
656
657 return 3;
658
659 exception
660 when others then
661 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
662 fnd_message.set_token('ROUTINE', 'FND_AMP_PRIVATE.ALTER_REQUEST_PRIORITY');
663 fnd_message.set_token('ERRNO', SQLCODE);
664 fnd_message.set_token('REASON', SQLERRM);
665 message := fnd_message.get;
666 rollback to fnd_amp_private_alter_priority;
667 return 0;
668 end;
669
670 end FND_AMP_PRIVATE;