1 PACKAGE BODY "FND_DIAG_REQUEST_ANALYZER" AS
2 /* $Header: AFCPDRAB.pls 120.0.12010000.1 2009/06/19 16:16:28 ggupta noship $*/
3 str_error VARCHAR2(2000);
4 str_fix_info VARCHAR2(2000);
5
6 FUNCTION get_status(p_status_code VARCHAR2) RETURN VARCHAR2 AS
7 c_status fnd_lookups.meaning%TYPE;
8 BEGIN
9 SELECT nvl(meaning, 'UNKNOWN')
10 INTO c_status
11 FROM fnd_lookups
12 WHERE lookup_type = 'CP_STATUS_CODE'
13 AND lookup_code = p_status_code;
14 RETURN RTRIM(c_status);
15 END get_status;
16 FUNCTION get_phase(p_phase_code VARCHAR2) RETURN VARCHAR2 AS
17 c_phase fnd_lookups.meaning%TYPE;
18 BEGIN
19 SELECT nvl(meaning, 'UNKNOWN')
20 INTO c_phase
21 FROM fnd_lookups
22 WHERE lookup_type = 'CP_PHASE_CODE'
23 AND lookup_code = p_phase_code;
24 RETURN RTRIM(c_phase);
25 END get_phase;
26 PROCEDURE manager_check(req_id IN NUMBER, cd_id IN NUMBER, mgr_defined OUT nocopy boolean, mgr_active OUT nocopy boolean, mgr_workshift OUT nocopy boolean, mgr_running OUT nocopy boolean, run_alone OUT nocopy boolean) IS
27 CURSOR mgr_cursor(rid NUMBER) IS
28 SELECT running_processes,
29 max_processes,
30 decode(control_code, 'T', 'N', -- Abort
31 'X', 'N', -- Aborted
32 'D', 'N', -- Deactivate
33 'E', 'N', -- Deactivated
34 'Y') active
35 FROM fnd_concurrent_worker_requests
36 WHERE request_id = rid
37 AND NOT((queue_application_id = 0)
38 AND(concurrent_queue_id IN(1, 4)));
39 run_alone_flag VARCHAR2(1);
40 BEGIN
41 mgr_defined := FALSE;
42 mgr_active := FALSE;
43 mgr_workshift := FALSE;
44 mgr_running := FALSE;
45 FOR mgr_rec IN mgr_cursor(req_id)
46 LOOP
47 mgr_defined := TRUE;
48
49 IF(mgr_rec.active = 'Y') THEN
50 mgr_active := TRUE;
51
52 IF(mgr_rec.max_processes > 0) THEN
53 mgr_workshift := TRUE;
54 END IF;
55
56 IF(mgr_rec.running_processes > 0) THEN
57 mgr_running := TRUE;
58 END IF;
59
60 END IF;
61
62 END LOOP;
63
64 IF(cd_id IS NULL) THEN
65 run_alone_flag := 'N';
66 ELSE
67 SELECT runalone_flag
68 INTO run_alone_flag
69 FROM fnd_conflicts_domain d
70 WHERE d.cd_id = manager_check.cd_id;
71 END IF;
72
73 IF(run_alone_flag = 'Y') THEN
74 run_alone := TRUE;
75 ELSE
76 run_alone := FALSE;
77 END IF;
78
79 END manager_check;
80
81 PROCEDURE print_mgrs(p_req_id IN NUMBER, inner_section IN OUT nocopy jtf_diag_section, reportcontext IN jtf_diag_report_context) AS
82 note jtf_diag_note;
83 CURSOR c_mgrs(rid NUMBER) IS
84 SELECT user_concurrent_queue_name name,
85 fcwr.running_processes active,
86 decode(fcwr.control_code, 'A', fl.meaning, 'D', fl.meaning, 'E', fl.meaning, 'N', fl.meaning, 'R', fl.meaning, 'T',
87 fl.meaning, 'U', fl.meaning, 'V', fl.meaning, 'X', fl.meaning, NULL, 'Running', '** Unknown Status **') status
88 FROM fnd_concurrent_queues_vl fcqv,
89 fnd_concurrent_worker_requests fcwr,
90 fnd_lookups fl
91 WHERE fcwr.request_id = rid
92 AND fcwr.concurrent_queue_id = fcqv.concurrent_queue_id
93 AND fcwr.concurrent_queue_id NOT IN(1, 4)
94 AND fl.lookup_code(+) = fcwr.control_code
95 AND fl.lookup_type(+) = 'CP_CONTROL_CODE';
96
97 BEGIN
98 FOR mgr_rec IN c_mgrs(p_req_id)
99 LOOP
100 note := jtf_diag_report_factory.create_note('- ' || mgr_rec.name || ' | Status: ' || mgr_rec.status || ' (' || mgr_rec.active || ' active processes)', reportcontext);
101 inner_section.add_note(note);
102 END LOOP;
103
104 END print_mgrs;
105
106 PROCEDURE runtest(exec_obj IN OUT jtf_diag_execution_obj, result OUT nocopy VARCHAR2) IS
107 -- its a collection of inputs for a particular execution of the test
108 allinputs jtf_diag_inputtbl;
109 -- a report object provided by the framework to produce a well managed report
110 -- of the test execution
111 report jtf_diag_report_obj;
112 -- this context is needed for creation of different component in the report
113 reportcontext jtf_diag_report_context;
114 -- section component which will be a container of other component in the report
115 SECTION jtf_diag_section;
116 inner_section jtf_diag_section;
117 -- message of type info, warning, error can be put inside a report
118 message jtf_diag_message;
119 -- note component
120 note jtf_diag_note;
121 -- hide show component to be displayed in a report
122 hideshow jtf_diag_hide_show;
123 -- table component to be displayed in report
124 table1 jtf_diag_table;
125 -- footer in a report
126 footer jtf_diag_footer;
127 -- header to be displayed in a report
128 header1 jtf_diag_header;
129 --form view component
130 form_view jtf_diag_form;
131 form_keys jtf_varchar2_table_4000;
132 form_values jtf_varchar2_table_4000;
133 str_query VARCHAR2(2000);
134 str VARCHAR2(2000);
135 -- CP Request Analyser
136 p_req_id NUMBER;
137 reqinfo fnd_concurrent_requests % rowtype;
138 proginfo fnd_concurrent_programs_vl % rowtype;
139 c_status fnd_lookups.meaning%TYPE;
140 m_buf fnd_lookups.meaning%TYPE;
144 tmp_id NUMBER(15);
141 conc_prog_name fnd_concurrent_programs.concurrent_program_name%TYPE;
142 exe_method_code fnd_concurrent_programs_vl.execution_method_code%TYPE;
143 conc_app_name fnd_application_vl.application_name%TYPE;
145 tmp_status fnd_concurrent_requests.status_code%TYPE;
146 tmp_date DATE;
147 conc_app_id fnd_concurrent_requests.program_application_id%TYPE;
148 conc_id fnd_concurrent_requests.concurrent_program_id%TYPE;
149 conc_cd_id fnd_concurrent_requests.cd_id%TYPE;
150 v_enabled_flag fnd_concurrent_programs.enabled_flag%TYPE;
151 conflict_domain fnd_conflicts_domain.user_cd_name%TYPE;
152 parent_id NUMBER(15);
153 resp_name VARCHAR2(100);
154 rclass_name fnd_concurrent_request_class.request_class_name%TYPE;
155 exe_file_name fnd_executables.execution_file_name%TYPE;
156 c_user fnd_user.user_name%TYPE;
157 last_user fnd_user.user_name%TYPE;
158 fcd_phase VARCHAR2(48);
159 fcd_status VARCHAR2(48);
160 traid fnd_concurrent_requests.program_application_id%TYPE;
161 trcpid fnd_concurrent_requests.concurrent_program_id%TYPE;
162 icount NUMBER;
163 ireqid fnd_concurrent_requests.request_id%TYPE;
164 pcode fnd_concurrent_requests.phase_code%TYPE;
165 scode fnd_concurrent_requests.status_code%TYPE;
166 live_child boolean;
167 mgr_defined boolean;
168 mgr_active boolean;
172 reqlimit boolean := FALSE;
169 mgr_workshift boolean;
170 mgr_running boolean;
171 run_alone boolean;
173 mgrname fnd_concurrent_queues_vl.user_concurrent_queue_name%TYPE;
174 filename VARCHAR2(255);
175 qcf fnd_concurrent_programs.queue_control_flag%TYPE;
176 req_notfound
177
178 EXCEPTION;
179
180 CURSOR c_wait IS
181 SELECT request_id,
182 phase_code,
183 status_code
184 FROM fnd_concurrent_requests
185 WHERE parent_request_id = p_req_id;
186 CURSOR c_inc IS
187 SELECT to_run_application_id,
188 to_run_concurrent_program_id
189 FROM fnd_concurrent_program_serial
190 WHERE running_application_id = conc_app_id
191 AND running_concurrent_program_id = conc_id;
192 CURSOR c_ireqs IS
193 SELECT request_id,
194 phase_code,
195 status_code
196 FROM fnd_concurrent_requests
197 WHERE phase_code = 'R'
198 AND program_application_id = traid
199 AND concurrent_program_id = trcpid
200 AND cd_id = conc_cd_id;
201 CURSOR c_userreqs(uid NUMBER, s DATE) IS
202 SELECT request_id,
203 to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') start_date,
204 phase_code,
205 status_code
206 FROM fnd_concurrent_requests
207 WHERE phase_code IN('R', 'P')
208 AND requested_by = uid
209 AND requested_start_date < s
210 AND hold_flag = 'N';
211 BEGIN
212 -- report obj
213 report := exec_obj.get_report;
214 -- To get the report context to create other components for the report
215 reportcontext := report.get_report_context;
216 -- Creating a header of the report
217 header1 := jtf_diag_report_factory.create_header('CP Diagnostic Request Analyzer', reportcontext);
218 -- Adding the header to the report
219 report.add_header(header1);
220
221 SECTION := jtf_diag_report_factory.create_section('Analyzing request ' || p_req_id || ':', reportcontext);
222
223 -- Taking request id from the user
224 allinputs := exec_obj.inputset;
225 p_req_id := to_number(jtf_diagnostic_adaptutil.getinputvalue('RequestId', allinputs));
226
227 BEGIN
228 SELECT *
229 INTO reqinfo
230 FROM fnd_concurrent_requests
231 WHERE request_id = p_req_id;
232
233 EXCEPTION
234 WHEN no_data_found THEN
235 result := 'false';
236 -- Creating a message component and adding it to a section
237 str_error := 'Request ' || p_req_id || ' not found.';
238 str_fix_info := 'Please Enter the valid Request Id';
239 message := jtf_diag_report_factory.create_message(str_error, 'error', reportcontext);
240 SECTION.add_message(message);
241 GOTO endoffile;
242 END;
243
244 SELECT fvl.*
245 INTO proginfo
246 FROM fnd_concurrent_programs_vl fvl,
247 fnd_concurrent_requests fcr
248 WHERE fcr.request_id = p_req_id
249 AND fcr.concurrent_program_id = fvl.concurrent_program_id
250 AND fcr.program_application_id = fvl.application_id;
251
252 SELECT nvl(application_name, '-- UNKNOWN APPLICATION --')
253 INTO conc_app_name
254 FROM fnd_application_vl fvl,
255 fnd_concurrent_requests fcr
256 WHERE fcr.request_id = p_req_id
257 AND fcr.program_application_id = fvl.application_id;
258
259 SELECT nvl(meaning, 'UNKNOWN')
263 AND lookup_code = proginfo.execution_method_code;
260 INTO m_buf
261 FROM fnd_lookups
262 WHERE lookup_type = 'CP_EXECUTION_METHOD_CODE'
264
265 SELECT nvl(execution_file_name, 'NONE')
266 INTO exe_file_name
267 FROM fnd_executables
268 WHERE application_id = proginfo.executable_application_id
269 AND executable_id = proginfo.executable_id;
270
271 form_keys := jtf_varchar2_table_4000('Program', 'Application', 'Executable type', 'Executable file name or procedure',
272 'Run alone flag', 'SRS flag', 'NLS compliant', 'Output file type');
273 form_values := jtf_varchar2_table_4000(proginfo.user_concurrent_program_name || ' (' || proginfo.concurrent_program_name || ')', conc_app_name,
274 m_buf || ' (' || proginfo.execution_method_code || ')', exe_file_name, proginfo.run_alone_flag, proginfo.srs_flag, proginfo.nls_compliant, proginfo.output_file_type);
275
276 IF proginfo.concurrent_class_id IS NOT NULL THEN
277 SELECT request_class_name
278 INTO rclass_name
279 FROM fnd_concurrent_request_class
280 WHERE application_id = proginfo.class_application_id
281 AND request_class_id = proginfo.concurrent_class_id;
282 form_keys.extend;
283 form_keys(form_keys.COUNT) := 'Request type';
284 form_values.extend;
285 form_values(form_values.COUNT) := rclass_name;
286 END IF;
287
288 IF proginfo.execution_options IS NOT NULL THEN
289 form_keys.extend;
290 form_keys(form_keys.COUNT) := 'Execution options';
291 form_values.extend;
292 form_values(form_values.COUNT) := proginfo.execution_options;
293 END IF;
294
295 form_view := jtf_diag_report_factory.create_form('Program information', form_keys, form_values, reportcontext);
296 SECTION.add_form(form_view);
297
298 IF proginfo.enable_trace = 'Y' THEN
299 inner_section := jtf_diag_report_factory.create_section('Note:', reportcontext);
300 note := jtf_diag_report_factory.create_note('SQL Trace has been enabled for this program.', reportcontext);
301 inner_section.add_note(note);
302 SECTION.add_section(inner_section);
303 END IF;
304
305 -- Submission information
306 BEGIN
307 SELECT user_name
308 INTO c_user
309 FROM fnd_user
310 WHERE user_id = reqinfo.requested_by;
311
312 EXCEPTION
313 WHEN no_data_found THEN
314 c_user := '-- UNKNOWN USER --';
315 END;
316
317 BEGIN
318 SELECT user_name
319 INTO last_user
320 FROM fnd_user
321 WHERE user_id = reqinfo.last_updated_by;
322
323 EXCEPTION
324 WHEN no_data_found THEN
325 last_user := '-- UNKNOWN USER --';
326 END;
327
328 SELECT responsibility_name
329 INTO resp_name
330 FROM fnd_responsibility_vl
331 WHERE responsibility_id = reqinfo.responsibility_id
332 AND application_id = reqinfo.responsibility_application_id;
333
334 form_keys := jtf_varchar2_table_4000('It was submitted by user', 'Using responsibility', 'It was submitted on', 'It was requested to start on', 'Parent request id', 'Language', 'Territory', 'Priority', 'Arguments');
338 SECTION.add_form(form_view);
335 form_values := jtf_varchar2_table_4000(c_user, resp_name, to_char(reqinfo.request_date, 'DD-MON-RR HH24:MI:SS'), to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'),
336 reqinfo.parent_request_id, reqinfo.nls_language, reqinfo.nls_territory, to_char(reqinfo.priority), '(' || reqinfo.number_of_arguments || '): ' || reqinfo.argument_text);
337 form_view := jtf_diag_report_factory.create_form('Submission information', form_keys, form_values, reportcontext);
339
340 c_status := get_status(reqinfo.status_code);
341
342 inner_section := jtf_diag_report_factory.create_section('Analysis', reportcontext);
343
344 IF reqinfo.phase_code = 'C' THEN
345 note := jtf_diag_report_factory.create_note('Request ' || p_req_id || ' has completed with status "' || c_status || '".', reportcontext);
346 inner_section.add_note(note);
347 note := jtf_diag_report_factory.create_note('It began running on: ' || nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'), '-- NO START DATE --'), reportcontext);
348 inner_section.add_note(note);
349 note := jtf_diag_report_factory.create_note('It completed on: ' || nvl(to_char(reqinfo.actual_completion_date, 'DD-MON-RR HH24:MI:SS'), '-- NO COMPLETION DATE --'), reportcontext);
350 inner_section.add_note(note);
351
352 BEGIN
353 SELECT user_concurrent_queue_name
354 INTO mgrname
355 FROM fnd_concurrent_queues_vl
359
356 WHERE concurrent_queue_id = reqinfo.controlling_manager;
357 note := jtf_diag_report_factory.create_note('It was run by manager: ' || mgrname, reportcontext);
358 inner_section.add_note(note);
360 EXCEPTION
361 WHEN no_data_found THEN
362 SELECT queue_control_flag
363 INTO qcf
364 FROM fnd_concurrent_programs
365 WHERE concurrent_program_id = reqinfo.concurrent_program_id
366 AND application_id = reqinfo.program_application_id;
367
368 IF qcf = 'Y' THEN
369 note := jtf_diag_report_factory.create_note('This request is a queue control request, it was run by the ICM', reportcontext);
370 ELSE
371 note := jtf_diag_report_factory.create_note('It was run by an unknown manager.', reportcontext);
372 END IF;
373
374 inner_section.add_note(note);
375 END;
376
377 SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
378 INTO filename
379 FROM dual;
380 note := jtf_diag_report_factory.create_note('Logfile: ' || filename, reportcontext);
381 inner_section.add_note(note);
382 SELECT nvl(reqinfo.outfile_name, '-- No output file --')
383 INTO filename
384 FROM dual;
385 note := jtf_diag_report_factory.create_note('Output file: ' || filename, reportcontext);
386 inner_section.add_note(note);
387 note := jtf_diag_report_factory.create_note('It produced completion message: ' || nvl(reqinfo.completion_text, '-- NO COMPLETION MESSAGE --'), reportcontext);
388 inner_section.add_note(note);
389 ELSIF reqinfo.phase_code = 'R' THEN
390 note := jtf_diag_report_factory.create_note('Request ' || p_req_id || ' is currently running with status "' || c_status || '".', reportcontext);
391 inner_section.add_note(note);
392 note := jtf_diag_report_factory.create_note('It began running on: ' || nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'), '-- NO START DATE --'), reportcontext);
393 inner_section.add_note(note);
394 BEGIN
395 SELECT user_concurrent_queue_name
396 INTO mgrname
397 FROM fnd_concurrent_queues_vl
398 WHERE concurrent_queue_id = reqinfo.controlling_manager;
399 note := jtf_diag_report_factory.create_note('It is being run by manager: ' || mgrname, reportcontext);
400 inner_section.add_note(note);
401
402 EXCEPTION
403 WHEN no_data_found THEN
404 NULL;
405 END;
406
407 SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
408 INTO filename
409 FROM dual;
410 note := jtf_diag_report_factory.create_note('Logfile: ' || filename, reportcontext);
411 inner_section.add_note(note);
412 SELECT nvl(reqinfo.outfile_name, '-- No output file --')
413 INTO filename
414 FROM dual;
415 note := jtf_diag_report_factory.create_note('Output file: ' || filename, reportcontext);
416 inner_section.add_note(note);
417
418 IF reqinfo.os_process_id IS NOT NULL THEN
419 note := jtf_diag_report_factory.create_note('OS process id: ' || reqinfo.os_process_id, reportcontext);
420 inner_section.add_note(note);
421 END IF;
422
423 IF reqinfo.status_code = 'Z' THEN
424
425 -- Waiting request, See what it is waiting on
426 FOR child IN c_wait
427 LOOP
428
429 note := jtf_diag_report_factory.create_note('It is waiting on request ' || child.request_id || ' phase = ' || get_phase(child.phase_code) || ' status = ' || get_status(child.status_code), reportcontext);
430 inner_section.add_note(note);
431 END LOOP;
432
433 ELSIF reqinfo.status_code = 'W' THEN
434
435 -- Paused, check and see if it is a request set, and if its children are running
436 SELECT nvl(concurrent_program_name, 'UNKNOWN')
437 INTO conc_prog_name
438 FROM fnd_concurrent_programs
439 WHERE concurrent_program_id = reqinfo.concurrent_program_id;
440
441 note := jtf_diag_report_factory.create_note('A Running/Paused request is waiting on one or more child requests to complete.', reportcontext);
442 inner_section.add_note(note);
443
444 IF conc_prog_name = 'FNDRSSTG' THEN
445 note := jtf_diag_report_factory.create_note('This program appears to be a Request Set Stage.', reportcontext);
446 inner_section.add_note(note);
447 END IF;
448
449 IF instr(conc_prog_name, 'RSSUB') > 0 THEN
450 note := jtf_diag_report_factory.create_note('This program appears to be a Request Set parent program.', reportcontext);
451 inner_section.add_note(note);
452 END IF;
453
454 live_child := FALSE;
455 FOR child IN c_wait
456 LOOP
457
458 note := jtf_diag_report_factory.create_note('It has a child request: ' || child.request_id || ' (phase = ' || get_phase(child.phase_code) || ' - status = ' || get_status(child.status_code) || ')', reportcontext);
459 inner_section.add_note(note);
460
461 IF child.phase_code <> 'C' THEN
462 live_child := TRUE;
463 END IF;
464
465 END LOOP;
466
467 IF live_child = FALSE THEN
468 str_error := 'This request has no child requests that are still running.';
472 result := 'false';
469 str_fix_info := 'You need to wake this request up manually.';
470 message := jtf_diag_report_factory.create_message(str_error || str_fix_info, 'error', reportcontext);
471 SECTION.add_message(message);
473 GOTO endoffile;
474 END IF;
475
476 END IF;
477
478 -- Pending Requests
479 -------------------------------------------------------------------------------------------------------------
480 ELSIF reqinfo.phase_code = 'P' THEN
481
482 note := jtf_diag_report_factory.create_note('Request ' || p_req_id || ' is in phase "Pending" with status "' || c_status || '".', reportcontext);
483 inner_section.add_note(note);
484 note := jtf_diag_report_factory.create_note(' (phase_code = P) (status_code = ' || reqinfo.status_code || ')', reportcontext);
485 inner_section.add_note(note);
486
487 -- could be a queue control request
488 SELECT queue_control_flag
489 INTO qcf
490 FROM fnd_concurrent_programs
491 WHERE concurrent_program_id = reqinfo.concurrent_program_id
492 AND application_id = reqinfo.program_application_id;
493
494 IF qcf = 'Y' THEN
495 note := jtf_diag_report_factory.create_note('This request is a queue control request', reportcontext);
496 inner_section.add_note(note);
497 note := jtf_diag_report_factory.create_note('It will be run by the ICM on its next sleep cycle', reportcontext);
498 inner_section.add_note(note);
499 GOTO diagnose;
500 END IF;
501
502 -- why is it pending?
503
504 -- could be scheduled
505
506 IF reqinfo.requested_start_date > sysdate OR reqinfo.status_code = 'P' THEN
507 note := jtf_diag_report_factory.create_note('This is a scheduled request.', reportcontext);
508 inner_section.add_note(note);
509 note := jtf_diag_report_factory.create_note('It is currently scheduled to start running on ' || to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'), reportcontext);
510 inner_section.add_note(note);
511 note := jtf_diag_report_factory.create_note('This should show on the form as Pending/Scheduled', reportcontext);
512 inner_section.add_note(note);
513 GOTO diagnose;
514 END IF;
515
516 -- could be on hold
517
518 IF reqinfo.hold_flag = 'Y' THEN
519 note := jtf_diag_report_factory.create_note('This request is currently on hold. It will not run until the hold is released.', reportcontext);
520 inner_section.add_note(note);
521 note := jtf_diag_report_factory.create_note('It was placed on hold by: ' || last_user || ' on ' || to_char(reqinfo.last_update_date, 'DD-MON-RR HH24:MI:SS'), reportcontext);
522 inner_section.add_note(note);
523 note := jtf_diag_report_factory.create_note('This should show on the form as Inactive/On Hold', reportcontext);
524 inner_section.add_note(note);
525 GOTO diagnose;
526 END IF;
527
528 -- could be disabled
529
530 IF proginfo.enabled_flag = 'N' THEN
531 note := jtf_diag_report_factory.create_note('This request is currently disabled.', reportcontext);
532 inner_section.add_note(note);
533 note := jtf_diag_report_factory.create_note('The concurrent_program ' || proginfo.user_concurrent_program_name || ' needs to be enabled for this request to run.', reportcontext);
534 inner_section.add_note(note);
535 note := jtf_diag_report_factory.create_note('This should show on the form as Inactive/Disabled', reportcontext);
536 inner_section.add_note(note);
537 GOTO diagnose;
538 END IF;
539
540 -- check queue_method_code
541 -- unconstrained requests
542
543 IF reqinfo.queue_method_code = 'I' THEN
544 note := jtf_diag_report_factory.create_note('This request is an unconstrained request. (queue_method_code = I)', reportcontext);
545 inner_section.add_note(note);
546
547 IF reqinfo.status_code = 'I' THEN
548 note := jtf_diag_report_factory.create_note('It is in a "Pending/Normal" status, ready to be run by the next available manager.', reportcontext);
549 inner_section.add_note(note);
550 ELSIF reqinfo.status_code = 'Q' THEN
551 note := jtf_diag_report_factory.create_note('It has a status of "Standby" even though it is unconstrained. It will not be run by any manager.', reportcontext);
552 inner_section.add_note(note);
553 ELSIF reqinfo.status_code IN('A', 'Z') THEN
554 note := jtf_diag_report_factory.create_note('It is in a "Waiting" status. This usually indicates a child request waiting for the parent to release it.', reportcontext);
555 inner_section.add_note(note);
556 SELECT nvl(parent_request_id, -1)
557 INTO parent_id
558 FROM fnd_conc_req_summary_v
559 WHERE request_id = p_req_id;
560
561 IF parent_id = -1 THEN
562 note := jtf_diag_report_factory.create_note('** Unable to find a parent request for this request', reportcontext);
563 inner_section.add_note(note);
564 ELSE
568
565 note := jtf_diag_report_factory.create_note('It''s parent request id is: ' || to_char(parent_id), reportcontext);
566 inner_section.add_note(note);
567 END IF;
569 ELSE
570 str_error := 'Unexpected status of ' || reqinfo.status_code || ' occurred';
571 str_fix_info := ' Please contact System Administrator';
572 message := jtf_diag_report_factory.create_message(str_error || str_fix_info, 'error', reportcontext);
573 SECTION.add_message(message);
574 result := 'false';
575 GOTO endoffile;
576 END IF;
577
578 -- constrained requests
579 ELSIF reqinfo.queue_method_code = 'B' THEN
580 note := jtf_diag_report_factory.create_note('This request is a constrained request. (queue_method_code = B)', reportcontext);
581 inner_section.add_note(note);
582
583 IF reqinfo.status_code = 'I' THEN
584 note := jtf_diag_report_factory.create_note('The Conflict Resolution manager has released this request, and it is in a "Pending/Normal" status.', reportcontext);
585 inner_section.add_note(note);
586 note := jtf_diag_report_factory.create_note('It is ready to be run by the next available manager.', reportcontext);
587 inner_section.add_note(note);
588 ELSIF reqinfo.status_code = 'Q' THEN
589 note := jtf_diag_report_factory.create_note('It is in a "Pending/Standby" status. The Conflict Resolution manager will need to release it before it can be run.', reportcontext);
590 inner_section.add_note(note);
591 ELSIF reqinfo.status_code IN('A', 'Z') THEN
592 note := jtf_diag_report_factory.create_note('It is in a "Waiting" status. This usually indicates a child request waiting for the parent to release it.', reportcontext);
593 inner_section.add_note(note);
594 SELECT nvl(parent_request_id, -1)
595 INTO parent_id
596 FROM fnd_conc_req_summary_v
597 WHERE request_id = p_req_id;
598
599 IF parent_id = -1 THEN
600 note := jtf_diag_report_factory.create_note('** Unable to find a parent request for this request', reportcontext);
601 inner_section.add_note(note);
602 ELSE
603 note := jtf_diag_report_factory.create_note('It''s parent request id is: ' || to_char(parent_id), reportcontext);
604 inner_section.add_note(note);
605 END IF;
606
607 ELSE
608 note := jtf_diag_report_factory.create_note('Hmmm. A status of ' || reqinfo.status_code || '. I was not really expecting to see this status.', reportcontext);
609 inner_section.add_note(note);
610 END IF;
611
612 -- incompatible programs
613 SELECT program_application_id,
614 concurrent_program_id,
615 cd_id
616 INTO conc_app_id,
617 conc_id,
618 conc_cd_id
619 FROM fnd_concurrent_requests
620 WHERE request_id = p_req_id;
621
622 icount := 0;
623 FOR progs IN c_inc
624 LOOP
625
626 traid := progs.to_run_application_id;
627 trcpid := progs.to_run_concurrent_program_id;
628
629 OPEN c_ireqs;
630 LOOP
631
632 FETCH c_ireqs
633 INTO ireqid,
634 pcode,
635 scode;
636 EXIT
637 WHEN c_ireqs % NOTFOUND;
638
639 note := jtf_diag_report_factory.create_note('Request ' || p_req_id || ' is waiting, or will have to wait, on an incompatible request: ' || ireqid, reportcontext);
640 inner_section.add_note(note);
641 note := jtf_diag_report_factory.create_note('which has phase = ' || pcode || ' and status = ' || scode, reportcontext);
642 inner_section.add_note(note);
643 icount := icount + 1;
644
645 END LOOP;
646
647 CLOSE c_ireqs;
648
649 END LOOP;
650
651 IF icount = 0 THEN
652 note := jtf_diag_report_factory.create_note('No running incompatible requests were found for request ' || p_req_id, reportcontext);
653 inner_section.add_note(note);
654 END IF;
655
656 -- could be a runalone itself
657
658 IF proginfo.run_alone_flag = 'Y' THEN
659 note := jtf_diag_report_factory.create_note('This request is constrained because it is a runalone request.', reportcontext);
660 inner_section.add_note(note);
661 END IF;
662
663 -- single threaded
664
665 IF reqinfo.single_thread_flag = 'Y' THEN
666 note := jtf_diag_report_factory.create_note('This request is constrained because the profile option Concurrent: Sequential Requests is set.', reportcontext);
670
667 inner_section.add_note(note);
668 reqlimit := TRUE;
669 END IF;
671 -- request limit
672
673 IF reqinfo.request_limit = 'Y' THEN
674 note := jtf_diag_report_factory.create_note('This request is constrained because the profile option Concurrent: Active Request Limit is set.', reportcontext);
675 inner_section.add_note(note);
676 reqlimit := TRUE;
677 END IF;
678
679 IF reqlimit = TRUE THEN
680 note := jtf_diag_report_factory.create_note('This request may have to wait on these requests:', reportcontext);
681 inner_section.add_note(note);
682 FOR progs IN c_userreqs(reqinfo.requested_by, reqinfo.requested_start_date)
683 LOOP
684 note := jtf_diag_report_factory.create_note('Request id: ' || progs.request_id || ' Requested start date: ' || progs.start_date, reportcontext);
685 inner_section.add_note(note);
686 note := jtf_diag_report_factory.create_note(' Phase: ' || get_phase(progs.phase_code) || ' Status: ' || get_status(progs.status_code), reportcontext);
687 inner_section.add_note(note);
688 END LOOP;
689 END IF;
690
691 -- error, invalid queue_method_code
692 ELSE
693 str_error := 'This request has an invalid queue_method_code of ' || reqinfo.queue_method_code || ' This request will not be run.';
694 str_fix_info := 'You may need to apply patch 739644';
695 message := jtf_diag_report_factory.create_message(str_error, 'error', reportcontext);
696 inner_section.add_message(message);
700 SECTION.add_section(inner_section);
697 message := jtf_diag_report_factory.create_message(str_fix_info, 'info', reportcontext);
698 inner_section.add_message(message);
699 result := 'false';
701 GOTO endoffile;
702 END IF;
703
704 note := jtf_diag_report_factory.create_note('Checking managers available to run this request...', reportcontext);
705 inner_section.add_note(note);
706
707 -- check the managers
708 manager_check(p_req_id, reqinfo.cd_id, mgr_defined, mgr_active, mgr_workshift, mgr_running, run_alone);
709
710 -- could be a runalone ahead of it
711
712 IF run_alone = TRUE THEN
713 note := jtf_diag_report_factory.create_note('There is a runalone request running ahead of this request', reportcontext);
714 inner_section.add_note(note);
715 note := jtf_diag_report_factory.create_note('This should show on the form as Inactive/No Manager', reportcontext);
716 inner_section.add_note(note);
717
718 SELECT user_cd_name
719 INTO conflict_domain
720 FROM fnd_conflicts_domain
721 WHERE cd_id = reqinfo.cd_id;
722
723 note := jtf_diag_report_factory.create_note('Conflict domain = ' || conflict_domain, reportcontext);
724 inner_section.add_note(note);
725 -- see what is running
726 BEGIN
727 SELECT request_id,
728 status_code,
729 actual_start_date
730 INTO tmp_id,
731 tmp_status,
732 tmp_date
733 FROM fnd_concurrent_requests fcr,
734 fnd_concurrent_programs fcp
735 WHERE fcp.run_alone_flag = 'Y'
736 AND fcp.concurrent_program_id = fcr.concurrent_program_id
737 AND fcr.phase_code = 'R'
738 AND fcr.cd_id = reqinfo.cd_id;
739
740 note := jtf_diag_report_factory.create_note('This request is waiting for request ' || tmp_id || ', which is running with status ' || get_status(tmp_status), reportcontext);
741 inner_section.add_note(note);
742 note := jtf_diag_report_factory.create_note('It has been running since: ' || nvl(to_char(tmp_date, 'DD-MON-RR HH24:MI:SS'), '-- NO START DATE --'), reportcontext);
743 inner_section.add_note(note);
744
745 EXCEPTION
749 message := jtf_diag_report_factory.create_message(str_error || str_fix_info, 'error', reportcontext);
746 WHEN no_data_found THEN
747 str_error := 'The runalone flag is set for conflict domain ' || conflict_domain || ' but there is no runalone request running. ';
748 str_fix_info := 'Please contact System Administrator';
750 SECTION.add_message(message);
751 result := 'false';
752 GOTO endoffile;
753 END;
754
755 ELSIF mgr_defined = FALSE THEN
756 str_error := 'There is no manager defined that can run this request. This should show on the form as Inactive/No Manager. ';
757 str_fix_info := 'Check the specialization rules for each manager to make sure they are defined correctly.';
758 message := jtf_diag_report_factory.create_message(str_error || str_fix_info, 'error', reportcontext);
759 SECTION.add_message(message);
760 result := 'false';
761 GOTO endoffile;
762 ELSIF mgr_active = FALSE THEN
763 note := jtf_diag_report_factory.create_note('There are one or more managers defined that can run this request, but none of them are currently active', reportcontext);
764 inner_section.add_note(note);
765 note := jtf_diag_report_factory.create_note('This should show on the form as Inactive/No Manager', reportcontext);
766 inner_section.add_note(note);
767 -- print out which managers can run it and their status
768 note := jtf_diag_report_factory.create_note('These managers are defined to run this request:', reportcontext);
769 inner_section.add_note(note);
770 print_mgrs(p_req_id, inner_section, reportcontext);
771
772 str_error := 'Inactive/No Manager. ';
773 str_fix_info := 'Please contact the System Administrator.';
774 message := jtf_diag_report_factory.create_message(str_error || str_fix_info, 'error', reportcontext);
775 SECTION.add_message(message);
776 result := 'false';
777 GOTO endoffile;
778
779 ELSIF mgr_workshift = FALSE THEN
780 note := jtf_diag_report_factory.create_note('Right now, there is no manager running in an active workshift that can run this request', reportcontext);
781 inner_section.add_note(note);
782 note := jtf_diag_report_factory.create_note('This should show on the form as Inactive/No Manager', reportcontext);
783 inner_section.add_note(note);
784 -- display details about the workshifts
785 ELSIF mgr_running = FALSE THEN
786 note := jtf_diag_report_factory.create_note('There is one or more managers available to run this request, but none of them are running', reportcontext);
787 inner_section.add_note(note);
788 note := jtf_diag_report_factory.create_note('This should show on the form as Inactive/No Manager', reportcontext);
789 inner_section.add_note(note);
790 -- print out which managers can run it and their status
791 print_mgrs(p_req_id, inner_section, reportcontext);
792
793 str_error := 'Inactive/No Manager. ';
794 str_fix_info := 'Please contact the System Administrator.';
795 message := jtf_diag_report_factory.create_message(str_error || str_fix_info, 'error', reportcontext);
796 SECTION.add_message(message);
797 result := 'false';
798 GOTO endoffile;
799
800 ELSE
801 -- print out the managers available to run it
802 note := jtf_diag_report_factory.create_note('These managers are available to run this request:', reportcontext);
803 inner_section.add_note(note);
804 print_mgrs(p_req_id, inner_section, reportcontext);
805
806 END IF;
807
808 -- invalid phase code
809 ELSE
810 str_error := 'Request ' || p_req_id || ' has an invalid phase_code of "' || reqinfo.phase_code || '" ';
811 str_fix_info := '';
812 message := jtf_diag_report_factory.create_message(str_error || str_fix_info, 'error', reportcontext);
813 SECTION.add_message(message);
814 result := 'false';
815 GOTO endoffile;
816
817 END IF;
818
819 << diagnose >> SECTION.add_section(inner_section);
820 -- Setting the out parameter 'result' to true to indicate the test is successfull
821 result := 'true';
822
823 << endoffile >> ---------------XYZ-----------------------
824 -- Adding the section to the report after constructing it completely.
825 report.add_section(SECTION);
826
827 -- Creating a custom footer for the report
828 str := 'Oracle corporation.';
832
829 footer := jtf_diag_report_factory.create_footer(str, reportcontext);
830 -- Adding the footer to the report
831 report.add_footer(footer);
833 EXCEPTION
834 WHEN others THEN
835 str_error := 'Error number ' || SQLCODE || ' has occurred.' || ' Cause: ' || sqlerrm;
836 str_fix_info := 'Please contact system Administrator';
837 result := 'false';
838 END runtest;
839 -------------------------------------------------------------------------------------------------------------
840 -- procedure to set the description for the test.Framework will supply this information to the ui
841 -- before executing the test
842 -------------------------------------------------------------------------------------------------------------
843 PROCEDURE gettestdesc(str OUT nocopy VARCHAR2) IS
844 BEGIN
845 str := 'Analyze a concurrent request';
846 END gettestdesc;
847 -------------------------------------------------------------------------------------------------------------
848 -- procedure to set the name of the test
849 -------------------------------------------------------------------------------------------------------------
850 PROCEDURE gettestname(str OUT nocopy VARCHAR2) IS
851 BEGIN
852 str := 'Concurrent Request Analyzer';
853 END gettestname;
854 -------------------------------------------------------------------------------------------------------------
855 -- procedure to provide/populate the default parameters for the test case.
856 -- The input name used must comply with the XML id specification--namely it must begin with a [a-z][A-z]
857 -- and after that can contain as many of [a-z][A-Z][0-9][._-:] as desired.
858 -------------------------------------------------------------------------------------------------------------
859 PROCEDURE getdefaulttestparams(defaultinputvalues OUT nocopy jtf_diag_test_inputs) IS
860 tempinput jtf_diag_test_inputs;
861 BEGIN
862 tempinput := jtf_diagnostic_adaptutil.initialiseinput;
863 tempinput := jtf_diagnostic_adaptutil.addinput(tempinput, 'RequestId', NULL, 'FALSE', NULL, 'This is a Number input', 'FALSE', 'FALSE', 'TRUE');
864 defaultinputvalues := tempinput;
865
866 EXCEPTION
867 WHEN others THEN
868 defaultinputvalues := jtf_diagnostic_adaptutil.initialiseinput;
869 END getdefaulttestparams;
870 -------------------------------------------------------------------------------------------------------------
871 -- Procedure to report the framwork about the error has occured while running the test
872 -------------------------------------------------------------------------------------------------------------
873 PROCEDURE geterror(str OUT nocopy VARCHAR2) IS
874 BEGIN
875 str := str_error;
876 END geterror;
877 -------------------------------------------------------------------------------------------------------------
878 -- Procedure to report the framwork about the fix information if the test has failed
879 -------------------------------------------------------------------------------------------------------------
880 PROCEDURE getfixinfo(str OUT nocopy VARCHAR2) IS
881 BEGIN
882 str := str_fix_info;
883 END getfixinfo;
884 -------------------------------------------------------------------------------------------------------------
885 -- Procedure to report the framwork about the warning if any has occured while running the test
886 -------------------------------------------------------------------------------------------------------------
887 PROCEDURE iswarning(str OUT nocopy VARCHAR2) IS
888 BEGIN
889 str := 'false';
890 END iswarning;
891 -------------------------------------------------------------------------------------------------------------
892 -- Procedure to report the framwork about the sever error cases if any occured while running the test
893 -------------------------------------------------------------------------------------------------------------
897 END isfatal;
894 PROCEDURE isfatal(str OUT nocopy VARCHAR2) IS
895 BEGIN
896 str := 'false';
898 END fnd_diag_request_analyzer;
[Home] [Help]