1 package body FND_AMP_PRIVATE as
2 /* $Header: AFCPAMPB.pls 120.6 2011/05/09 19:35:38 pferguso 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
208 begin
209
210 fnd_conc_request_pkg.get_phase_status(pcode, scode, hold, enbld,
211 null, stdate, rid,
212 req_phase, req_status);
213
214 end get_phase_and_status;
215
216
217
218 --
219 -- Name
220 -- get_status
221 -- Purpose
222 -- Returns a translated status description.
223 --
224 function get_phase (pcode in char,
225 scode in char,
226 hold in char,
227 enbld in char,
228 stdate in date,
229 rid in number) return varchar2 is
230 begin
231
232 /* Did we already run get_status for this request?
233 * If so, then return the cached phase value.
234 */
235 if (ran_get_status = rid) then
236 ran_get_status := -1;
237 return req_phase;
238 end if;
239
240 /* Get phase and status. Return phase. */
241 get_phase_and_status(pcode, scode, hold, enbld, stdate, rid);
242 ran_get_phase := rid;
243 return req_phase;
244
245 exception
246 when others then
247 return 'ORA'||SQLCODE;
248 end;
249
250
251
252 --
253 -- Name
254 -- get_status
255 -- Purpose
256 -- Returns a translated status description.
257 --
258 function get_status (pcode in char,
259 scode in char,
260 hold in char,
261 enbld in char,
262 stdate in date,
263 rid in number) return varchar2 is
264 begin
265 /* Did we already run get_phase for this request?
266 * If so, then return the cached status value.
267 */
268 if (ran_get_phase = rid) then
269 ran_get_phase := -1;
270 return req_status;
271 end if;
272
273 /* Get phase and status. Return status. */
274 get_phase_and_status(pcode, scode, hold, enbld, stdate, rid);
275 ran_get_status := rid;
276 return req_status;
277
278 exception
279 when others then
280 return 'ORA'||SQLCODE;
281 end;
282
283
284
285 --
286 -- Name
287 -- kill_session
288 -- Purpose
289 -- Kills a session given an audsid and instance id
290 --
291 -- Parameters:
292 -- audsid - ID of session to kill.
293 -- message - Oracle error message.
294 -- inst_id - Instance ID of session.
295 --
296 -- Returns:
297 -- 0 - Oracle error. Check message.
298 -- 1 - Session not found.
299 -- 2 - Success.
300 --
301 function kill_session (audsid in number,
302 message in out NOCOPY varchar2,
303 inst_id in number default 1) return number is
304 kcursor varchar2(75); /* Cursor string for dbms_sql */
305 cid number; /* Cursor ID for dbms_sql */
306 ssid number;
307 sserial# number;
308 dummy number;
309 begin
310 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
311 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
312 'fnd.plsql.FND_AMP_PRIVATE.kill_session',
313 'audsid: ' ||audsid||', inst_id=' ||inst_id);
314 end if;
315
316 begin
317 select sid, serial#
318 into ssid, sserial#
319 from gv$session
320 where kill_session.audsid = gv$session.audsid
321 and kill_session.inst_id = gv$session.inst_id;
322 exception
323 when no_data_found then
324 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
325 fnd_log.string(FND_LOG.LEVEL_ERROR,
326 'fnd.plsql.FND_AMP_PRIVATE.kill_session',
327 'Session (audsid) ' ||audsid||' in instance '||inst_id||' not found');
328 end if;
329 return 1;
330 when others then
331 raise;
332 end;
333
334 kcursor := 'alter system kill session '''|| to_char(ssid) || ',' ||
335 to_char(sserial#)||'''';
336 begin
337 cid := dbms_sql.open_cursor;
338 dbms_sql.parse(cid, kcursor, dbms_sql.v7);
339 dummy := dbms_sql.execute(cid);
340 dbms_sql.close_cursor(cid);
341 exception
342 when others then
343 if SQLCODE = -30 then
344 return 1;
345 else
346 raise;
347 end if;
348 end;
349
350 return 2;
351
352 exception
353 when others then
354 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
355 fnd_message.set_token('ROUTINE', 'FND_AMP_PRIVATE.KILL_SESSION');
356 fnd_message.set_token('ERRNO', SQLCODE);
357 fnd_message.set_token('REASON', SQLERRM);
358 message := fnd_message.get;
359 return 0;
360 end;
361
362
363 --
364 -- Name
365 -- cancel_request
366 -- Purpose
367 -- Cancel or terminate a request.
368 -- Make sure fnd_global.apps_initialize was called first.
369 --
370 -- Parameters:
371 -- req_id - ID of request to cancel.
372 -- message - Error message.
373 --
374 -- Returns:
375 -- 0 - Oracle error. Check message.
376 -- 1 - Could not lock request row
377 -- 2 - Request has already completed.
378 -- 3 - Cannot cancel. Manager dead.
379 -- 4 - Request cancelled.
380 -- 5 - Request marked for termination.
381 --
382 function cancel_request ( req_id in number,
383 message in out NOCOPY varchar2) return number is
387 new_status varchar2(1);
384 PRAGMA AUTONOMOUS_TRANSACTION;
385 req_phase varchar2(1);
386 req_status varchar2(1);
388 is_sub_req varchar2(1);
389 has_sub_req varchar2(1);
390 mgr_proc number;
391 current_user VARCHAR2(100);
392 who_cancelled varchar2(255);
393 par_req_id number;
394
395 begin
396
397 begin
398 select phase_code, status_code,
399 is_sub_request, has_sub_request,
400 controlling_manager, parent_request_id
401 into req_phase, req_status,
402 is_sub_req, has_sub_req,
403 mgr_proc, par_req_id
404 from fnd_concurrent_requests
405 where request_id = req_id
406 for update of phase_code nowait;
407 exception
408 when others then
409 if (SQLCODE = -54) then
410 return 1;
411 else
412 raise;
413 end if;
414 end;
415
416 if (req_phase = 'C') then
417 rollback;
418 return 2;
419 end if;
420
421 if ((req_phase = 'R') and
422 (not process_alive(mgr_proc))) then
423 rollback;
424 return 3;
425 end if;
426
427 if (req_status = 'R') then
428 new_status := 'T';
429 elsif (req_status in ('W', 'B')) then
430 new_status := 'X';
431 else
432 new_status := 'D';
433
434 -- Who cancelled the request
435 current_user := FND_PROFILE.VALUE('USERNAME');
436 fnd_message.set_name ('FND', 'CONC-Cancelled by');
437 fnd_message.set_token ('USER', current_user);
438 who_cancelled := fnd_message.get;
439 end if;
440
441
442 update fnd_concurrent_requests
443 set phase_code = decode(new_status, 'T', phase_code, 'C'),
444 status_code = new_status,
445 completion_text = who_cancelled,
446 last_update_date = sysdate,
447 last_updated_by = fnd_global.user_id
448 where request_id = req_id;
449
450 cancel_subrequests(req_id, par_req_id, is_sub_req, has_sub_req);
451
452 commit;
453
454 if (new_status = 'T') then
455 return 5;
456 else
457 return 4;
458 end if;
459
460 exception
461 when others then
462 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
463 fnd_message.set_token('ROUTINE', 'FND_AMP_PRIVATE.TERMINATE_REQUEST');
464 fnd_message.set_token('ERRNO', SQLCODE);
465 fnd_message.set_token('REASON', SQLERRM);
466 message := fnd_message.get;
467 rollback;
468 return 0;
469 end;
470
471
472 --
473 -- Name
474 -- toggle_hold
475 -- Purpose
476 -- Toggles the hold flag for a concurrent request.
477 -- Make sure fnd_global.apps_initialize was called first.
478 --
479 -- Parameters:
480 -- req_id - ID of request to toggle.
481 -- message - Error message.
482 --
483 -- Returns:
484 -- 0 - Oracle error. Check message.
485 -- 1 - Could not lock request row
486 -- 2 - Request has already started.
487 -- 3 - Request placed on hold.
488 -- 4 - Request hold removed.
489 --
490 function toggle_request_hold ( req_id in number,
491 message in out NOCOPY varchar2) return number is
492 req_phase varchar2(1);
493 req_hold varchar2(1);
494 req_type varchar2(1);
495 new_hold varchar2(1);
496 retval number;
497 begin
498 savepoint fnd_amp_private_hold_req;
499
500 begin
501 select phase_code, hold_flag, request_type
502 into req_phase, req_hold, req_type
503 from fnd_concurrent_requests
504 where request_id = req_id
505 for update of phase_code nowait;
506 exception
507 when others then
508 if (SQLCODE = -54) then
509 return 1;
510 else
511 raise;
512 end if;
513 end;
514
515 if (req_phase <> 'P' ) then
516 -- to fix bug # 4761862
517 -- Request Sets in Running phase can be hold.
518 if ( req_type <> 'M' or req_phase <> 'R') then
519 rollback to fnd_amp_private_hold_req;
520 return 2;
521 end if;
522 end if;
523
524 if (req_hold = 'Y') then
525 new_hold := 'N';
526 retval := 4;
527 else
528 new_hold := 'Y';
529 retval := 3;
530 end if;
531
532
533 update fnd_concurrent_requests
534 set hold_flag = new_hold,
535 last_update_date = sysdate,
536 last_updated_by = fnd_global.user_id
537 where request_id = req_id;
538
539 commit;
540
541 return retval;
542
543 exception
544 when others then
545 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
546 fnd_message.set_token('ROUTINE', 'FND_AMP_PRIVATE.TOGGLE_REQUEST_HOLD');
547 fnd_message.set_token('ERRNO', SQLCODE);
548 fnd_message.set_token('REASON', SQLERRM);
549 message := fnd_message.get;
550 rollback to fnd_amp_private_hold_req;
551 return 0;
552 end;
553
554
555 --
556 -- Name
557 -- alter_priority
558 -- Purpose
559 -- Alters the priority for a concurrent request.
560 -- Make sure fnd_global.apps_initialize was called first.
561 --
562 -- Parameters:
566 --
563 -- req_id - ID of request to toggle.
564 -- priority - New priority.
565 -- message - Error message.
567 -- Returns:
568 -- 0 - Oracle error. Check message.
569 -- 1 - Could not lock request row
570 -- 2 - Request has already started.
571 -- 3 - Request priority altered.
572 --
573 function alter_request_priority ( req_id in number,
574 new_priority in number,
575 message in out NOCOPY varchar2) return number is
576 req_phase varchar2(1);
577
578 begin
579 savepoint fnd_amp_private_alter_priority;
580
581 begin
582 select phase_code
583 into req_phase
584 from fnd_concurrent_requests
585 where request_id = req_id
586 for update of phase_code nowait;
587 exception
588 when others then
589 if (SQLCODE = -54) then
590 return 1;
591 else
592 raise;
593 end if;
594 end;
595
596 if (req_phase <> 'P') then
597 rollback to fnd_amp_private_alter_priority;
598 return 2;
599 end if;
600
601
602 update fnd_concurrent_requests
603 set priority = new_priority,
604 last_update_date = sysdate,
605 last_updated_by = fnd_global.user_id
606 where request_id = req_id;
607
608 commit;
609
610 return 3;
611
612 exception
613 when others then
614 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
615 fnd_message.set_token('ROUTINE', 'FND_AMP_PRIVATE.ALTER_REQUEST_PRIORITY');
616 fnd_message.set_token('ERRNO', SQLCODE);
617 fnd_message.set_token('REASON', SQLERRM);
618 message := fnd_message.get;
619 rollback to fnd_amp_private_alter_priority;
620 return 0;
621 end;
622
623 end FND_AMP_PRIVATE;
[Home] [Help]