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