[Home] [Help]
PACKAGE BODY: APPS.FND_CONC
Source
1 package body FND_CONC as
2 /* $Header: AFCPDIGB.pls 120.4 2006/03/14 15:16:08 rckalyan ship $ */
3 --
4 -- Private variables
5
6 -- These statuses represent "internal" states, i.e. these
7 -- status codes will never be returned or used outside this package.
8 -- They are used to tell apart different reasons why a pending
9 -- request would be inactive. These states will all be
10 -- mapped externally to the 'Inactive/No Manager' state.
11 STATUS_INACTIVE_RUNALONE constant varchar2(1) := '1';
12 STATUS_INACTIVE_WKSHIFT constant varchar2(1) := '2';
13 STATUS_INACTIVE_MGR_DOWN constant varchar2(1) := '3';
14 STATUS_INACTIVE_MGR_TROUBLE constant varchar2(1) := '4';
15 STATUS_INACTIVE_CP_DOWN constant varchar2(1) := '5';
16
17
18
19 P_USER_STATUS_CODE varchar2(1);
20 P_USER_PHASE_CODE varchar2(1);
21 P_CONTROLLING_MANAGER number;
22 P_REQUEST_ID number;
23 P_ACTUAL_START_DATE date;
24 P_LAST_UPDATE_DATE date;
25 P_REQUESTOR varchar2(100);
26 P_PARENT_REQUEST_ID number;
27 P_UPDATED_BY_NAME varchar2(100);
28 P_APPLICATION_NAME varchar2(240);
29 P_USER_CONC_PROG_NAME varchar2(240);
30 P_QUEUE_CONTROL_FLAG varchar2(1);
31 P_CD_ID number;
32 P_ACTUAL_COMPLETION_DATE date;
33 P_COMPLETION_TEXT varchar2(1000);
34 P_HOLD_FLAG varchar2(1);
35 P_PHASE varchar2(80);
36 P_STATUS varchar2(80);
37 P_PHASE_CODE varchar2(1);
38 P_STATUS_CODE varchar2(1);
39 P_ENABLED varchar2(1);
40 -- Increased size to P_PROGRAM to to fix the bug 4097622.
41 -- This is required because we are storing
42 -- R.Description||' ('||CP.User_Concurrent_Program_Name||')' into
43 -- the P_PROGRAM in diagnose method.(240+240+1+1+1)
44 P_PROGRAM varchar2(483);
45 P_REQUESTED_START_DATE date;
46 P_REQUEST_DATE date;
47 P_QUEUE_METHOD_CODE varchar2(1);
48 P_RUN_ALONE_FLAG varchar2(1);
49 P_SINGLE_THREAD_FLAG varchar2(1);
50 P_REQLIMIT_FLAG varchar2(1);
51
52
53 date_fmt varchar2(24):= 'DD-MON-YYYY HH24:MI:SS';
54
55
56 -- ================================================
57 -- PRIVATE FUNCTIONS/PROCEDURES
58 -- ================================================
59
60 PROCEDURE init_pvt_vars is
61 begin
62 P_USER_STATUS_CODE := null;
63 P_USER_PHASE_CODE := null;
64 P_CONTROLLING_MANAGER := null;
65 P_REQUEST_ID := null;
66 P_ACTUAL_START_DATE := null;
67 P_LAST_UPDATE_DATE := null;
68 P_REQUESTOR := null;
69 P_PARENT_REQUEST_ID := null;
70 P_UPDATED_BY_NAME := null;
71 P_APPLICATION_NAME := null;
72 P_USER_CONC_PROG_NAME := null;
73 P_QUEUE_CONTROL_FLAG := null;
74 P_CD_ID := null;
75 P_ACTUAL_COMPLETION_DATE := null;
76 P_COMPLETION_TEXT := null;
77 P_HOLD_FLAG := null;
78 P_PHASE := null;
79 P_STATUS := null;
80 P_PHASE_CODE := null;
81 P_STATUS_CODE := null;
82 P_ENABLED := null;
83 P_PROGRAM := null;
84 P_REQUESTED_START_DATE := null;
85 P_REQUEST_DATE := null;
86 P_QUEUE_METHOD_CODE := null;
87 P_RUN_ALONE_FLAG := null;
88 P_SINGLE_THREAD_FLAG := null;
89 P_REQLIMIT_FLAG := null;
90
91 end;
92
93 --
94 -- PROCEDURE
95 -- date_text
96 --
97 function date_text(date_in date) return varchar2 is
98 text varchar2(240);
99 begin
100 text := to_char(date_in, date_fmt);
101 return text;
102 end;
103
104
105
106 --
107 -- PROCEDURE
108 -- request_stats (reqid, pcode, help_text)
109 -- Purpose
110 -- Put in request statistics of request.
111 -- Arguments
112 -- reqid - request id.
113 -- pcode - phase code.
114 -- help_text - accumulate the help text message.
115 -- Notes
116 -- For now, we only give the average time of previous runs and
117 -- if pcode == 'R', when the request is expected to finish.
118
119 PROCEDURE request_stats (reqid in number,
120 pcode in char,
121 help_text in out nocopy varchar2) is
122
123 avg_mins number; -- Average run times
124 avg_hrs number;
125 avg_days number;
126 expctd_finish varchar2(22); -- Expected finish date in
127 -- DD-MON-YYYY HH24:MI:SS format
128
129 begin
130 -- Select only statistics# == -5 and n > 0
131 select mod (floor (minval/60), 60),
132 mod (floor (minval/3600), 24),
133 floor (minval/86400),
134 decode (pcode, 'R', decode (sign (actual_start_date + minval/86400
135 - sysdate),
136 -1, null,
137 to_char (
138 actual_start_date + minval/86400,
139 date_fmt)),
140 null)
141 into avg_mins,
142 avg_hrs,
143 avg_days,
144 expctd_finish
145 from fnd_conc_stat_summary,
146 fnd_concurrent_requests r,
147 fnd_concurrent_programs p
148 where request_id = reqid
149 and program_application_id = application_id
150 and p.concurrent_program_id = r.concurrent_program_id
151 and concurrent_program_name = program_name
152 and statistic# = -5
153 and daily = 'F'
154 and n > 0
155 and minval > 0;
156
157 -- Average runs are less than a minute.
158 if avg_mins < 1 and avg_hrs < 1 and avg_days < 1 then
159 fnd_message.set_name ('FND', 'CONC-DG-STAT-LESS THAN A MIN');
160 help_text := help_text || fnd_message.get;
161 return;
162 else
163 -- Average runs are more than a minute.
164 fnd_message.set_name ('FND', 'CONC-DG-STAT-PREVIOUS RUNS');
165 help_text := fnd_message.get;
166 end if;
167
168 if avg_days > 0 then
169 fnd_message.set_name ('FND', 'CONC-DG-STAT-AVG DAYS');
170 fnd_message.set_token ('AVG_DAYS', to_char (avg_days));
171 help_text := help_text || fnd_message.get;
172 end if;
173
174 if avg_hrs > 0 then
175 fnd_message.set_name ('FND', 'CONC-DG-STAT-AVG_HRS');
176 fnd_message.set_token ('AVG_HRS', to_char (avg_hrs));
177 help_text := help_text || fnd_message.get;
178 end if;
179
180 if avg_mins > 0 then
181 fnd_message.set_name ('FND', 'CONC-DG-STAT-AVG MINS');
182 fnd_message.set_token ('AVG_MINS', to_char (avg_mins));
183 help_text := help_text || fnd_message.get;
184 end if;
185
186 -- Expected finish date and time.
187 if pcode = 'R' and expctd_finish is not null then
188 fnd_message.set_name ('FND', 'CONC-DG-STAT-EXPCTD FINISH');
189 fnd_message.set_token ('EXPCTD_FINISH', expctd_finish);
190 help_text := help_text || fnd_message.get;
191 fnd_message.set_name ('FND', 'CONC-DG-STAT-DISCLAIMER');
192 help_text := help_text || fnd_message.get;
193 end if;
194
195 -- Punt on any exception
196 exception
197 when others then
198 return;
199
200 end request_stats;
201
202
203
204
205 --
206 -- PROCEDURE
207 -- diag_running
208 -- Purpose
209 -- Diagnostics for running requests.
210 -- Arguments
211 -- help_text - return statistics from request_stats. Not currently used.
212 -- Notes
213 -- *none*
214 --
215 PROCEDURE diag_running (help_text in out nocopy varchar2) is
216 dummy boolean;
217 child_count number;
218 pp_cnt number;
219 proc_id number;
220 complete varchar2(1);
221
222 begin
223 if (P_USER_STATUS_CODE = STATUS_RUNNING_NORMAL) then
224 if (process_alive(P_CONTROLLING_MANAGER)) then -- mgr alive
225
226 -- se if the request has any pp actions
227 select count(*)
228 into pp_cnt
229 from fnd_conc_pp_actions
230 where concurrent_request_id = P_REQUEST_ID
231 and action_type = 6; -- REMOVE FOR PHASE 2
232
233
234 if pp_cnt > 0 then
235
236 select processor_id, completed
237 into proc_id, complete
238 from fnd_conc_pp_actions
239 where concurrent_request_id = P_REQUEST_ID
240 and action_type = 6; -- REMOVE FOR PHASE 2
241
242 -- if processor_id has been updated, post-processing has begun
243 if proc_id is not null then
244
245 -- if completed != Y then the request is currently in post-processing
246 if complete <> 'Y' then
247 request_stats (P_REQUEST_ID, 'R', help_text);
248 help_text := help_text || fnd_message.get;
249 fnd_message.set_name('FND', 'CONC-DG-PP ONGOING');
250
251 else
252 request_stats (P_REQUEST_ID, 'R', help_text);
253 help_text := help_text || fnd_message.get;
254 fnd_message.set_name('FND', 'CONC-DG-PP COMPLETE');
255 end if;
256
257 end if;
258
259 end if;
260
261 -- If the request has no post-processing actions, or
262 -- post-processing has not begun, the request is running normal
263 if pp_cnt = 0 or proc_id is null then
264 request_stats (P_REQUEST_ID, 'R', help_text);
265 help_text := help_text || fnd_message.get;
266 fnd_message.set_name('FND', 'CONC-DG-RUNNING NORMAL');
267 fnd_message.set_token('START_DATE', date_text(P_ACTUAL_START_DATE));
268 end if;
269
270
271 else -- Manager process died
272 fnd_message.set_name('FND', 'CONC-DG-RUNNING DEAD');
273 end if;
274
275 elsif (P_USER_STATUS_CODE = STATUS_TERMINATING) then
276 fnd_message.set_name('FND','CONC-DG-RUNNING ABORTING');
277 fnd_message.set_token('ABORT_DATE', date_text(P_LAST_UPDATE_DATE));
278 fnd_message.set_token('USER', P_REQUESTOR);
279
280 elsif (P_USER_STATUS_CODE = STATUS_PAUSED) then
281
282 dummy := icm_alive(TRUE);
283
284 -- check to see if it has running children
285 select count(*)
286 into child_count
287 from fnd_concurrent_requests
288 where parent_request_id = P_REQUEST_ID
289 and phase_code in (PHASE_PENDING, PHASE_RUNNING);
290
291 if (child_count < 1) then
292 fnd_message.set_name('FND', 'CONC-DG-WAITING NO CHILDREN');
293 else
294 fnd_message.set_name('FND', 'CONC-DG-RUNNING WAITING');
295 end if;
296
297 fnd_message.set_token('START_DATE',date_text(P_ACTUAL_START_DATE));
298
299 elsif (P_USER_STATUS_CODE = STATUS_RESUMING) then
300 dummy := icm_alive(TRUE);
301 fnd_message.set_name('FND', 'CONC-DG-RUNNING RESUMING');
302 fnd_message.set_token('START_DATE', date_text(P_ACTUAL_START_DATE));
303 fnd_message.set_token('PARENT_REQUEST_ID',
304 P_PARENT_REQUEST_ID);
305 else
306 fnd_message.set_name('FND', 'CONC-DG-BAD STATUS');
307 end if;
308 end diag_running;
309
310
311 --
312 -- PROCEDURE
313 -- diag_inactive
314 -- Purpose
315 -- Diagnostics for running requests.
316 -- Arguments
317 -- *none*
318 -- Notes
319 -- *none*
320 --
321 PROCEDURE diag_inactive is
322 ra_reqid number;
323 cd_name fnd_conflicts_domain.user_cd_name%TYPE;
324 begin
325 if (P_USER_STATUS_CODE = STATUS_HOLD) then
326 fnd_message.set_name('FND', 'CONC-DG-INACTIVE HOLD');
327 fnd_message.set_token('USER', P_UPDATED_BY_NAME);
328 fnd_message.set_token('HOLD_DATE', date_text(P_LAST_UPDATE_DATE));
329
330 elsif (P_USER_STATUS_CODE = STATUS_DISABLED) then
331 fnd_message.set_name('FND', 'CONC-DG-INACTIVE DISABLED');
332 fnd_message.set_token('APPLICATION_NAME', P_APPLICATION_NAME);
333 fnd_message.set_token('PROGRAM_NAME', P_USER_CONC_PROG_NAME);
334
335 elsif (P_USER_STATUS_CODE = STATUS_NO_MANAGER) then
336 fnd_message.set_name('FND', 'CONC-DG-INACTIVE NO MANAGER');
337
338 elsif (P_USER_STATUS_CODE = STATUS_INACTIVE_RUNALONE) then
339
340 select user_cd_name into cd_name from fnd_conflicts_domain
341 where cd_id = P_CD_ID;
342
343 begin
344 /* changed query for BUG#5007915 SQLID#14602696 */
345 select request_id
346 into ra_reqid
347 from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
348 where fcp.run_alone_flag = 'Y'
349 and fcp.concurrent_program_id = fcr.concurrent_program_id
350 and fcp.application_id = fcr.program_application_id
351 and fcr.phase_code = 'R'
352 and fcr.cd_id = P_CD_ID;
353
354
355 fnd_message.set_name('FND', 'CONC-DG-INACTIVE RUNALONE');
356 fnd_message.set_token('RA_REQID', ra_reqid);
357 fnd_message.set_token('CD_NAME', cd_name);
358
359 exception
360 when NO_DATA_FOUND then
361 fnd_message.set_name('FND', 'CONC-DG-INACTIVE NO RUNALONE');
362 fnd_message.set_token('CD_NAME', cd_name);
363 end;
364
365 elsif (P_USER_STATUS_CODE = STATUS_INACTIVE_WKSHIFT) then
366 fnd_message.set_name('FND', 'CONC-DG-INACTIVE WRONG SHIFT');
367
368 elsif (P_USER_STATUS_CODE = STATUS_INACTIVE_MGR_DOWN) then
369 fnd_message.set_name('FND', 'CONC-DG-INACTIVE MANAGER DOWN');
370
371 elsif (P_USER_STATUS_CODE = STATUS_INACTIVE_MGR_TROUBLE) then
372 fnd_message.set_name('FND', 'CONC-DG-INACTIVE TROUBLED');
373
374 elsif (P_USER_STATUS_CODE = STATUS_INACTIVE_CP_DOWN) then
375 fnd_message.set_name('FND', 'CONC-DG-INACTIVE ICM DOWN');
376
377 else
378 fnd_message.set_name('FND', 'CONC-DG-BAD STATUS');
379 end if;
380 end diag_inactive;
381
382
383 --
387 -- Diagnostics for pending requests.
384 -- PROCEDURE
385 -- diag_pending
386 -- Purpose
388 -- Arguments
389 -- help_text - return statistics from request_stats. Not currently used.
390 -- Notes
391 -- *none*
392 --
393 PROCEDURE diag_pending (help_text in out nocopy varchar2) is
394
395 dummy boolean;
396 parent_reqid number;
397 cnt number;
398 begin
399
400 -- check for invalid queue_method_code
401 if (P_QUEUE_METHOD_CODE not in ('I', 'B')) then
402 fnd_message.set_name('FND', 'CONC-DG-INVALID QM CODE');
403 return;
404 end if;
405
406 if (P_USER_STATUS_CODE in (STATUS_NORMAL, STATUS_STANDBY)) then
407 if (P_QUEUE_CONTROL_FLAG = 'Y') then
408 if ( icm_alive(FALSE)) then
409 fnd_message.set_name('FND', 'CONC-DG-PENDING NORMAL CONTROL');
410 else
411 fnd_message.set_name('FND', 'CONC-DG-INACTIVE QUEUE CONTROL');
412 end if;
413 fnd_message.set_token('USER', P_UPDATED_BY_NAME);
414 fnd_message.set_token('SUBMIT_DATE', date_text(P_LAST_UPDATE_DATE));
415 return;
416 end if;
417
418 request_stats (P_REQUEST_ID, 'P', help_text);
419
420 -- Pending Normal
421 if (P_USER_STATUS_CODE = STATUS_NORMAL) then
422 fnd_message.set_name('FND', 'CONC-DG-PENDING NORMAL');
423
424 else
425 -- Pending Standby
426 -- Check for unconstrained req in Standby
427 if (P_QUEUE_METHOD_CODE = 'I') then
428 fnd_message.set_name('FND', 'CONC-DG-STANDBY UNCONSTRAINED');
429
430 -- runalone request
431 elsif (P_RUN_ALONE_FLAG = 'Y') then
432 fnd_message.set_name('FND', 'CONC-DG-STANDBY RUNALONE');
433
434 -- normal standby request
435 elsif (P_SINGLE_THREAD_FLAG <> 'Y' and P_REQLIMIT_FLAG <> 'Y') then
436 fnd_message.set_name('FND', 'CONC-DG-PENDING STANDBY');
437
438 else
439 -- Here is a special case. If the user is invalid or end-dated,
440 -- this request will never be released by the CRM.
441 -- Check the user in FND_USER
442 select count(*)
443 into cnt
444 from fnd_user fu, fnd_concurrent_requests fcr
445 where fcr.request_id = P_REQUEST_ID
446 and fu.user_id = fcr.requested_by
447 and (fu.end_date is null or fu.end_date > sysdate);
448
449 if (cnt = 0) then
450 fnd_message.set_name('FND', 'CONC-DG-STANDBY INVALID USER');
451
452 -- single thread flag
453 elsif (P_SINGLE_THREAD_FLAG = 'Y') then
454 fnd_message.set_name('FND', 'CONC-DG-STANDBY SEQREQ');
455
456 -- Active request limit flag
457 elsif (P_REQLIMIT_FLAG = 'Y') then
458 fnd_message.set_name('FND', 'CONC-DG-STANDBY REQLIMIT');
459
460 -- normal standby request
461 else
462 fnd_message.set_name('FND', 'CONC-DG-PENDING STANDBY');
463 end if;
464 end if;
465 end if;
466
467 fnd_message.set_token('USER', P_REQUESTOR);
468 fnd_message.set_token('SUBMIT_DATE', date_text(P_LAST_UPDATE_DATE));
469
470
471 elsif (P_USER_STATUS_CODE = STATUS_SCHEDULED) then
472 request_stats (P_REQUEST_ID, 'P', help_text);
473 fnd_message.set_name('FND', 'CONC-DG-PENDING SCHEDULED');
474 fnd_message.set_token('START_DATE', date_text(P_REQUESTED_START_DATE));
475 fnd_message.set_token('USER', P_REQUESTOR);
476
477 elsif (P_USER_STATUS_CODE = STATUS_WAITING) then
478
479 -- check for a running or pending parent request
480 select count(*)
481 into parent_reqid
482 from fnd_concurrent_requests
483 where request_id = P_PARENT_REQUEST_ID
484 and phase_code in ('P', 'R');
485
486
487 if (P_PARENT_REQUEST_ID is null or parent_reqid = 0) then
488 fnd_message.set_name('FND', 'CONC-DG-WAITING NO PARENT');
489 else
490 fnd_message.set_name('FND', 'CONC-DG-PENDING WAITING');
491 fnd_message.set_token('PARENT_REQUEST_ID', P_PARENT_REQUEST_ID);
492 end if;
493
494 else
495 fnd_message.set_name('FND', 'CONC-DG-BAD STATUS');
496 end if;
497
498 end diag_pending;
499
500
501 --
502 -- PROCEDURE
503 -- diag_completed
504 -- Purpose
505 -- Diagnostics for pending requests.
506 -- Arguments
507 -- *none*
508 -- Notes
509 -- *none*
510 --
511 PROCEDURE diag_completed is
512 begin
513 if (P_USER_STATUS_CODE = STATUS_COMPLETED_NORMAL) then
514 fnd_message.set_name('FND', 'CONC-DG-COMPLETED NORMAL');
515 fnd_message.set_token('START_DATE', date_text(P_ACTUAL_START_DATE));
516 fnd_message.set_token('COMPLETION_DATE', date_text(P_ACTUAL_COMPLETION_DATE));
517
518 elsif (P_USER_STATUS_CODE = STATUS_ERROR) then
519 if (P_COMPLETION_TEXT is not null) then
520 fnd_message.set_name('FND', 'CONC-DG-COMPLETED ERROR');
521 fnd_message.set_token('COMPLETION_TEXT', P_COMPLETION_TEXT);
522 else
526 fnd_message.set_token('COMPLETION_DATE', date_text(P_ACTUAL_COMPLETION_DATE));
523 fnd_message.set_name('FND', 'CONC-DG-COMPLETED ERROR NO MSG');
524 end if;
525 fnd_message.set_token('START_DATE', date_text(P_ACTUAL_START_DATE));
527
528 elsif (P_USER_STATUS_CODE = STATUS_WARNING) then
529 if (P_COMPLETION_TEXT is not null) then
530 fnd_message.set_name('FND', 'CONC-DG-COMPLETED WARNING');
531 fnd_message.set_token('COMPLETION_TEXT', P_COMPLETION_TEXT);
532 else
533 fnd_message.set_name('FND', 'CONC-DG-COMPLETED WARN NO MSG');
534 end if;
535 fnd_message.set_token('START_DATE', date_text(P_ACTUAL_START_DATE));
536 fnd_message.set_token('COMPLETION_DATE', date_text(P_ACTUAL_COMPLETION_DATE));
537
538 elsif (P_USER_STATUS_CODE = STATUS_TERMINATED) then
539 fnd_message.set_name('FND', 'CONC-DG-COMPLETED ABORTED');
540 fnd_message.set_token('ABORT_DATE', date_text(P_LAST_UPDATE_DATE));
541 fnd_message.set_token('USER', P_UPDATED_BY_NAME);
542
543 elsif (P_USER_STATUS_CODE = STATUS_CANCELLED) then
544 fnd_message.set_name('FND', 'CONC-DG-COMPLETED DELETED');
545 fnd_message.set_token('USER', P_UPDATED_BY_NAME);
546 fnd_message.set_token('DELETE_DATE', date_text(P_LAST_UPDATE_DATE));
547 else
548 fnd_message.set_name('FND', 'CONC-DG-BAD STATUS');
549 end if;
550
551 end diag_completed;
552
553
554
555 --
556 -- PROCEDURE
557 -- get_phase_status
558 -- Purpose
559 -- Calculate the user phase and status codes
560 -- from the request information.
561 -- Arguments
562 -- IN:
563 -- pcode -- The DB phase code
564 -- scode -- The DB status code
565 -- hold -- Hold flag
566 -- enbld -- Enabled flag
567 -- stdate -- Start date
568 -- rid -- Request_id
569 -- OUT:
570 -- phase -- User phase meaning
571 -- status -- User status meaning
572 -- upcode -- User phase code
573 -- uscode -- User status code
574 -- Notes
575 -- Private procedure only. May return special 'internal' status codes.
576 --
577 PROCEDURE get_phase_status (pcode in varchar2,
578 scode in varchar2,
579 hold in varchar2,
580 enbld in varchar2,
581 stdate in date,
582 rid in number,
583 phase out nocopy varchar2,
584 status out nocopy varchar2,
585 upcode in out nocopy varchar2,
586 uscode in out nocopy varchar2) is
587
588 defined boolean;
589 active boolean;
590 workshift boolean;
591 running boolean;
592 run_alone boolean;
593
594 begin
595 if (pcode is NULL) then
596 phase := NULL;
597 return;
598 end if;
599
600 upcode := pcode;
601 uscode := scode;
602
603 -- For Pending requests,
604 -- check to see if phase and status needs to be modified
605 if (pcode = PHASE_PENDING) then
606
607 upcode := PHASE_INACTIVE;
608
609 -- Check for Hold, Disabled, and Scheduled requests
610 if (hold = 'Y') then
611 uscode := STATUS_HOLD;
612
613 elsif (enbld = 'N') then
614 uscode := STATUS_DISABLED;
615
616 elsif ((stdate > sysdate) or (scode = STATUS_SCHEDULED)) then
617 upcode := PHASE_PENDING;
618 uscode := STATUS_SCHEDULED;
619 else
620
621 -- See if the request needs to marked Inactive
622 manager_check(rid,
623 P_CD_ID,
624 defined,
625 active,
626 workshift,
627 running,
628 run_alone);
629
630 if (not defined) then
631 uscode := STATUS_NO_MANAGER; -- No manager defined
632 elsif (not active) then
633 uscode := STATUS_INACTIVE_MGR_DOWN; -- Manager deactivated
634 elsif (not workshift) then
635 if not running and not icm_alive(false) then
636 uscode := STATUS_INACTIVE_CP_DOWN; -- All managers down
637 else
638 uscode := STATUS_INACTIVE_WKSHIFT; -- Out of workshift
639 end if;
640 elsif (not running) then
641 uscode := STATUS_INACTIVE_MGR_TROUBLE; -- Manager troubled/dead
642 elsif (run_alone) then
643 uscode := STATUS_INACTIVE_RUNALONE; -- Waiting for run alone
644 else
645 upcode := PHASE_PENDING; -- Normal pending request
646 end if;
647 end if;
648 end if;
649
650
651 phase := get_phase(upcode);
652 status := get_status(uscode);
653
654
655 end get_phase_status;
656
657
658
659
660 -- ================================================
661 -- PUBLIC FUNCTIONS/PROCEDURES
662 -- ================================================
663
664
665
666 --
667 -- PROCEDURE
668 -- diagnose
669 -- Purpose
673 -- phase -- returns text string describing the phase
670 -- Perform diagnostics on a given request.
671 -- Arguments
672 -- request_id
674 -- status -- returns text string describing the status
675 -- help_text -- returns translated diagnostic text
676 --
677 PROCEDURE diagnose ( request_id in number,
678 phase out nocopy varchar2,
679 status out nocopy varchar2,
680 help_text in out nocopy varchar2
681 ) is
682
683 l_phase varchar2(80);
684 l_status varchar2(80);
685 user_phase_code varchar2(1);
686 user_status_code varchar2(1);
687
688 diagnose_error exception;
689 begin
690 -- Initialize the private variables before using them
691 init_pvt_vars;
692 help_text := '';
693
694 -- Get the information about the request from the database and fill the
695 -- private variables
696
697 begin
698 select R.request_id, R.phase_code, R.status_code, R.request_date,
699 R.requested_start_date, R.hold_flag, R.parent_request_id,
700 R.last_update_date, U1.user_name updated_by_name,
701 R.actual_start_date, R.completion_text,
702 R.actual_completion_date, U2.user_name requestor,
703 FA.application_name application_name,
704 CP.enabled_flag enabled, R.controlling_manager,
705 Decode (R.Description,
706 NULL, CP.User_Concurrent_Program_Name,
707 R.Description||' ('||CP.User_Concurrent_Program_Name||')')
708 program_name, Queue_Control_Flag,
709 R.queue_method_code, CP.run_alone_flag,
710 R.single_thread_flag, R.request_limit, R.cd_id
711 into P_REQUEST_ID, P_PHASE_CODE, P_STATUS_CODE, P_REQUEST_DATE,
712 P_REQUESTED_START_DATE, P_HOLD_FLAG, P_PARENT_REQUEST_ID,
713 P_LAST_UPDATE_DATE, P_UPDATED_BY_NAME,
714 P_ACTUAL_START_DATE, P_COMPLETION_TEXT,
715 P_ACTUAL_COMPLETION_DATE, P_REQUESTOR,
716 P_APPLICATION_NAME,
717 P_ENABLED, P_CONTROLLING_MANAGER,
718 P_PROGRAM, P_QUEUE_CONTROL_FLAG,
719 P_QUEUE_METHOD_CODE, P_RUN_ALONE_FLAG,
720 P_SINGLE_THREAD_FLAG, P_REQLIMIT_FLAG, P_CD_ID
721 from fnd_concurrent_requests R, fnd_concurrent_programs_vl CP,
722 fnd_user U1, fnd_user U2,
723 fnd_application_vl FA
724 where R.request_id = diagnose.request_id
725 and R.program_application_id = FA.application_id
726 and R.program_application_id = CP.application_id (+)
727 and R.concurrent_program_id = CP.concurrent_program_id (+)
728 and R.last_updated_by = U1.user_id (+)
729 and R.requested_by = U2.user_id (+);
730
731 exception
732 when no_data_found then
733 fnd_message.set_name('FND','CONC-Missing Request');
734 fnd_message.set_token('ROUTINE', 'FND_CONC.DIAGNOSE');
735 fnd_message.set_token('REQUEST', to_char(request_id));
736 raise diagnose_error;
737 when others then
738 fnd_message.set_name ('FND', 'SQL-Generic error');
739 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
740 fnd_message.set_token ('REASON', sqlerrm, FALSE);
741 fnd_message.set_token (
742 'ROUTINE', 'FND_CONC.DIAGNOSE', FALSE);
743 raise diagnose_error;
744 end;
745
746 get_phase_status(
747 P_PHASE_CODE,
748 P_STATUS_CODE,
749 P_HOLD_FLAG,
750 P_ENABLED,
751 P_REQUESTED_START_DATE,
752 P_REQUEST_ID,
753 l_phase,
754 l_status,
755 user_phase_code,
756 user_status_code);
757
758 P_PHASE := l_phase;
759 P_STATUS := l_status;
760 phase := l_phase;
761 status := l_status;
762 P_USER_PHASE_CODE := user_phase_code;
763 P_USER_STATUS_CODE := user_status_code;
764
765 if (P_PROGRAM is NULL) then
766 fnd_message.set_name('FND', 'CONC-DG-BAD PROGRAM ID');
767 elsif (P_USER_PHASE_CODE = PHASE_RUNNING) then
768 diag_running (help_text);
769 elsif (P_USER_PHASE_CODE = PHASE_PENDING) then
770 diag_pending (help_text);
771 elsif (P_USER_PHASE_CODE = PHASE_COMPLETED) then
772 diag_completed;
773 elsif (P_USER_PHASE_CODE = PHASE_INACTIVE) then
774 diag_inactive;
775 -- if status was changed to one of the Inactive status codes,
776 -- change it back to 'No Manager'
777 if status is null then
778 status := get_status(STATUS_NO_MANAGER);
779 end if;
780 else
781 fnd_message.set_name('FND', 'CONC-DG-BAD PHASE');
782 end if;
783
784 help_text := fnd_message.get;
785
786 exception
787 when diagnose_error then
788 help_text := fnd_message.get;
789
790 end diagnose;
791
792
793
794 --
795 -- Function
796 -- process_alive
797 -- Purpose
798 -- Return TRUE if the process is alive,
799 -- FALSE otherwise.
803 -- Return FALSE on error.
800 -- Arguments
801 -- pid - concurrent process ID
802 -- Notes
804 --
805 function process_alive(pid number) return boolean is
806 manager_id number;
807 appl_id number;
808 result number;
809 alive number;
810
811 begin
812 select queue_application_id, concurrent_queue_id
813 into appl_id, manager_id
814 from fnd_concurrent_processes
815 where concurrent_process_id = pid;
816
817 --
818 -- Lock PMON method
819 -- Bug 2093806: use fnd_dcp.check_process_status_by_ids
820 --
821 fnd_dcp.check_process_status_by_ids(
822 appl_id, manager_id, pid, result, alive);
823
824 if (alive = 0) then
825 -- got the lock handle for the process, process not alive.
826 return FALSE;
827 elsif ( (alive = 1) AND (result in (1, 2)) ) then
828 -- lock not granted, process is alive
829 return TRUE;
830 else
831 -- alive is 1 and result is 3, 5, or null. This implies a
832 -- problem in call to DMBS_LOCK. In order to continue, we assume
833 -- manager is alive, since we did not get the lock. There is a
834 -- message that was set in the dictionary by fnd_dcp.
835 return TRUE;
836 end if;
837
838 exception
839 when no_data_found then
840 return FALSE;
841 end process_alive;
842
843
844 --
845 -- Function
846 -- icm_alive
847 -- Purpose
848 -- If the ICM is dead, put the appropriate
849 -- message on the stack and return FALSE.
850 -- If the ICM is alive, TRUE is returned
851 -- Arguments
852 -- print -- if FALSE, no message is put on the stack
853 --
854 function icm_alive(print boolean) return boolean is
855 pid number;
856
857 begin
858 select max(concurrent_process_id)
859 into pid
860 from fnd_concurrent_processes
861 where concurrent_process_id in
862 (select concurrent_process_id
863 from fnd_concurrent_processes
864 where queue_application_id = 0
865 and concurrent_queue_id = 1
866 and process_status_code in ('A','M'));
867
868 if (not process_alive(pid)) then
869 raise no_data_found;
870 end if;
871 return TRUE;
872 exception
873 when no_data_found then
874 if (print) then
875 fnd_message.set_name('FND','CONC-DG-IM INACTIVE');
876 end if;
877 return FALSE;
878 end icm_alive;
879
880
881 --
882 -- Function
883 -- service_alive
884 -- Purpose
885 -- Checks to see if any one of a service's processes are alive.
886 -- Returns TRUE if one or more is alive, if none are alive returns FALSE.
887 -- Arguments
888 -- queue_id -- concurrent queue id of the service
889 -- app_id -- application id of the service
890 -- Notes
891 -- Calls process_alive for each process id.
892 --
893 function service_alive(queue_id in number,
894 app_id in number) return boolean is
895
896 cursor service_curs(qid number, appid number) is
897 select concurrent_process_id
898 from fnd_concurrent_processes
899 where concurrent_queue_id = qid
900 and queue_application_id = appid
901 and process_status_code in ('A', 'C');
902
903 begin
904
905 for serv in service_curs(queue_id, app_id) loop
906 if process_alive(serv.concurrent_process_id) then
907 return true;
908 end if;
909
910 end loop;
911
912 return false;
913
914
915 end service_alive;
916
917
918
919
920 --
921 -- PROCEDURE
922 -- manager_check
923 -- Purpose
924 -- Checks status of managers that can run a request.
925 --
926 -- Arguments
927 -- IN:
928 -- req_id -- request ID
929 -- cd_id -- Conflict Domain ID
930 -- OUT:
931 -- mgr_defined -- Is there a manager defined that will run
932 -- the request?
933 -- mgr_active -- Is there an active manager to run it?
934 -- mgr_workshift -- Will the request run in a current workshift?
935 -- mgr_running -- Is there a manager running that can
936 -- process the request?
937 -- run_alone -- Is request waiting for run alone request?
938 -- to complete.
939 --
940 PROCEDURE manager_check (req_id in number,
941 cd_id in number,
942 mgr_defined out nocopy boolean,
943 mgr_active out nocopy boolean,
944 mgr_workshift out nocopy boolean,
945 mgr_running out nocopy boolean,
946 run_alone out nocopy boolean) is
947
948 cursor mgr_cursor (rid number) is
949 select running_processes, max_processes,
950 concurrent_queue_id, queue_application_id,
951 decode(control_code,
952 'T','N', -- Abort
953 'X','N', -- Aborted
957 from fnd_concurrent_worker_requests
954 'D','N', -- Deactivate
955 'E','N', -- Deactivated
956 'Y') active
958 where request_id = rid
959 and not((queue_application_id = 0)
960 and (concurrent_queue_id in (1,4)));
961
962 run_alone_flag varchar2(1);
963
964 begin
965 mgr_defined := FALSE;
966 mgr_active := FALSE;
967 mgr_workshift := FALSE;
968 mgr_running := FALSE;
969
970 for mgr_rec in mgr_cursor(req_id) loop
971 mgr_defined := TRUE;
972 if (mgr_rec.active = 'Y') then
973 mgr_active := TRUE;
974 if (mgr_rec.max_processes > 0) then
975 mgr_workshift := TRUE;
976 end if;
977 if (mgr_rec.running_processes > 0) then
978 -- says it has active processes, but does it really?
979 mgr_running := service_alive(mgr_rec.concurrent_queue_id,
980 mgr_rec.queue_application_id);
981 end if;
982 end if;
983 end loop;
984
985 if (cd_id is null) then -- However, I think we're changing the column to
986 run_alone_flag := 'N'; -- NOT NULL in 11.0.2...
987 else
988 select runalone_flag
989 into run_alone_flag
990 from fnd_conflicts_domain d
991 where d.cd_id = manager_check.cd_id;
992 end if;
993 if (run_alone_flag = 'Y') then
994 run_alone := TRUE;
995 else
996 run_alone := FALSE;
997 end if;
998
999 end manager_check;
1000
1001
1002 --
1003 -- FUNCTION
1004 -- get_phase
1005 -- Purpose
1006 -- Lookup meaning of a request phase_code.
1007 --
1008 function get_phase(pcode in varchar2) return varchar2 is
1009 ret_value varchar2(80);
1010 ltype varchar2(32);
1011 begin
1012
1013 ltype := 'CP_PHASE_CODE';
1014 select meaning into ret_value
1015 from fnd_lookups
1016 where lookup_type = ltype
1017 and lookup_code = pcode;
1018
1019 return ret_value;
1020
1021 exception
1022 when no_data_found then
1023 return null;
1024 when others then
1025 return null;
1026 end;
1027
1028
1029 --
1030 -- FUNCTION
1031 -- get_status
1032 -- Purpose
1033 -- Lookup meaning of a request status_code.
1034 --
1035 function get_status(scode in varchar2) return varchar2 is
1036 ret_value varchar2(80);
1037 ltype varchar2(32);
1038 begin
1039
1040 ltype := 'CP_STATUS_CODE';
1041 select meaning into ret_value
1042 from fnd_lookups
1043 where lookup_type = ltype
1044 and lookup_code = scode;
1045
1046 return ret_value;
1047
1048 exception
1049 when no_data_found then
1050 return null;
1051 when others then
1052 return null;
1053 end;
1054
1055
1056
1057 end FND_CONC;