1 PACKAGE BODY HRI_OPL_DATA_SETUP_DGNSTC AS
2 /* $Header: hripdgdp.pkb 120.9 2006/12/11 09:54:35 msinghai noship $ */
3
4 -- =========================================================================
5 --
6 -- OVERVIEW
7 -- --------
8 -- This package contains procedures to display the data set up of DBI in
9 -- the system.
10 --
11 -- http://files.oraclecorp.com/content/AllPublic/SharedFolders/HRMS%20
12 -- Intelligence%20%28HRMSi%29%20-%20Documents-Public/Design%20
13 -- Specifications/hri_lld_dgn_data_stup.doc
14 --
15 -- =========================================================================
16
17 TYPE g_sql_rec_type IS RECORD
18 (sql_stmt VARCHAR2(32000),
19 section_code VARCHAR2(240),
20 section_name VARCHAR2(240));
21
22 TYPE g_sql_tab_type IS TABLE OF g_sql_rec_type
23 INDEX BY BINARY_INTEGER;
24
25 g_concurrent_logging BOOLEAN;
26 g_debugging BOOLEAN;
27 g_empty_col_list hri_bpl_conc_log.col_list_tab_type;
28 g_rtn VARCHAR2(5) := '
29 ';
30 g_functional_area varchar2(100);
31 g_object_name VARCHAR2(100);
32 -- ----------------------------------------------------------------------------
33 -- Switches debugging messages on or off. Setting to on will
34 -- mean extra debugging information will be generated when the
35 -- process is run.
36 -- ----------------------------------------------------------------------------
37 PROCEDURE set_debugging(p_on IN BOOLEAN) IS
38
39 BEGIN
40
41 g_debugging := p_on;
42
43 END set_debugging;
44
45 -- ----------------------------------------------------------------------------
46 -- This procedure sets the global g_concurrent_logging to
47 -- the value passed in. If set log messages will be output
48 -- through fnd_file.put_line.
49 -- ----------------------------------------------------------------------------
50 PROCEDURE set_concurrent_logging(p_on IN BOOLEAN) IS
51
52 BEGIN
53
54 g_concurrent_logging := p_on;
55
56 END set_concurrent_logging;
57
58 -- ----------------------------------------------------------------------------
59 -- Prints a message to the concurrent process log
60 -- ----------------------------------------------------------------------------
61 PROCEDURE output(p_text IN VARCHAR2) IS
62
63 BEGIN
64
65 hri_bpl_conc_log.output(p_text);
66
67 END output;
68
69 -- ----------------------------------------------------------------------------
70 -- Prints a message to the concurrent process log and html output
71 -- ----------------------------------------------------------------------------
72 PROCEDURE output
73 (p_text IN VARCHAR2,
74 p_line_type IN VARCHAR2,
75 p_col_list IN hri_bpl_conc_log.col_list_tab_type DEFAULT g_empty_col_list,
76 p_format IN VARCHAR2 DEFAULT null) IS
77
78 BEGIN
79
80 hri_bpl_conc_log.output
81 (p_text => p_text,
82 p_col_list => p_col_list,
83 p_mode => 'HTML',
84 p_line_type => p_line_type,
85 p_format => p_format);
86
87 END output;
88
89 -- ----------------------------------------------------------------------------
90 -- Procedure dbg decides whether to log the passed in message
91 -- depending on whether debug mode is set.
92 -- ----------------------------------------------------------------------------
93 PROCEDURE dbg(p_text IN VARCHAR2) IS
94
95 BEGIN
96
97 IF g_debugging THEN
98 output(p_text);
99 END IF;
100
101 END dbg;
102
103 -- ----------------------------------------------------------------------------
104 -- Function GET_MESSAGE takes the message name and returns back the
105 -- message text
106 -- ----------------------------------------------------------------------------
107 FUNCTION get_message(p_message IN VARCHAR2,
108 p_start_date IN VARCHAR2,
109 p_end_date IN VARCHAR2)
110 RETURN VARCHAR2 IS
111
112 BEGIN
113
114 fnd_message.set_name('HRI', p_message);
115
116 -- Set the start and end date
117 fnd_message.set_token('START_DATE',p_start_date);
118 fnd_message.set_token('END_DATE',p_end_date);
119
120 RETURN fnd_message.get;
121
122 END get_message;
123
124 -- ----------------------------------------------------------------------------
125 -- Function GET_MESSAGE takes the message name and returns back the
126 -- message text
127 --
128 --
129 -- Modified by : Saurav Mohapatra
130 -- Date : 16-Nov-2006
131 -- Description : Function made scalable to parse tokens in messages
132 --
133 --
134 -- ----------------------------------------------------------------------------
135 FUNCTION get_message(p_message IN VARCHAR2)
136 RETURN VARCHAR2 IS
137
138 BEGIN
139
140 fnd_message.set_name('HRI', p_message);
141 --
142 IF HRI_BPL_SETUP_DIAGNOSTIC.is_token_exist(p_message,'PRODUCT_NAME') THEN
143 --
144 fnd_message.set_token('PRODUCT_NAME'
145 ,HRI_BPL_SETUP_DIAGNOSTIC.get_product_name(p_object_name => g_object_name));
146 --
147 END IF;
148 --
149 RETURN fnd_message.get;
150 --
151 END get_message;
152
153 -- ----------------------------------------------------------------------------
154 -- Function GET_MESSAGE_ALERT takes the message name and returns back the
155 -- message text
156 -- ----------------------------------------------------------------------------
157 FUNCTION get_message_alert(p_message IN VARCHAR2)
158 RETURN VARCHAR2 IS
159
160 BEGIN
161
162 fnd_message.set_name('HRI', p_message);
163
164 RETURN fnd_message.get;
165
166 END get_message_alert;
167
168 -- ----------------------------------------------------------------------------
169 -- PROCEDURE trim_msg removes blank spaces and enter characters from the string
170 -- ----------------------------------------------------------------------------
171 FUNCTION trim_msg(p_text IN VARCHAR2)
172 RETURN VARCHAR2 IS
173
174 l_text VARCHAR2(20000);
175
176 BEGIN
177
178 -- Remove blank spaces
179 l_text := TRIM(both ' ' FROM p_text);
180
181 RETURN l_text;
182
183 END trim_msg;
184
185 -- ----------------------------------------------------------------------------
186 -- PROCEDURE display_parameters outputs the parameters
187 -- ----------------------------------------------------------------------------
188 PROCEDURE display_parameters(p_start_date IN VARCHAR2,
189 p_end_date IN VARCHAR2,
190 p_mode IN VARCHAR2,
191 p_section IN VARCHAR2,
192 p_subsection IN VARCHAR2,
193 p_show_data IN VARCHAR2) IS
194
195 l_col_list hri_bpl_conc_log.col_list_tab_type;
196
197 BEGIN
198
199 -- Display the parameters passed to the concurrent program
200 output(p_text => 'Parameters',
201 p_line_type => 'HEADER3');
202
203 -- Set up parameter table
204 l_col_list(1).column_value := 'Parameter';
205 l_col_list(2).column_value := 'Value';
206
207 -- Output the table header
208 output(p_text => null,
209 p_line_type => 'TABLE_HEADER',
210 p_col_list => l_col_list);
211
212 -- Update parameter table
213 l_col_list(1).column_value := 'Start Date';
214 l_col_list(2).column_value := p_start_date;
215
216 -- Output the row
217 output(p_text => null,
218 p_line_type => 'TABLE_ROW',
219 p_col_list => l_col_list);
220
221 -- Update parameter table
222 l_col_list(1).column_value := 'End Date';
223 l_col_list(2).column_value := p_end_date;
224
225 -- Output the row
226 output(p_text => null,
227 p_line_type => 'TABLE_ROW',
228 p_col_list => l_col_list);
229
230 -- Update parameter table
231 l_col_list(1).column_value := 'Mode';
232 l_col_list(2).column_value := hr_general.decode_lookup
233 ('HRI_MODE',p_mode);
234
235 -- Output the row
236 output(p_text => null,
237 p_line_type => 'TABLE_ROW',
238 p_col_list => l_col_list);
239
240 -- Update parameter table
241 l_col_list(1).column_value := 'Section';
242 l_col_list(2).column_value := hr_general.decode_lookup
243 ('HRI_SECTION',p_section);
244
245 -- Output the row
246 output(p_text => null,
247 p_line_type => 'TABLE_ROW',
248 p_col_list => l_col_list);
249
250 -- Update parameter table
251 l_col_list(1).column_value := 'Subsection';
252 l_col_list(2).column_value := hr_general.decode_lookup
253 ('HRI_SUBSECTION',p_subsection);
254
255 -- Output the row
256 output(p_text => null,
257 p_line_type => 'TABLE_ROW',
258 p_col_list => l_col_list);
259
260 -- Output the table footer
261 output(p_text => null,
262 p_line_type => 'TABLE_FOOTER');
263
264 END display_parameters;
265
266 -- ----------------------------------------------------------------------------
267 -- PROCEDURE display_rpt_heading is used for dispalying the parameters passed
268 -- by the concurrent program
269 -- ----------------------------------------------------------------------------
270 PROCEDURE display_rpt_heading (p_start_date IN VARCHAR2,
271 p_end_date IN VARCHAR2,
272 p_mode IN VARCHAR2,
273 p_section IN VARCHAR2,
274 p_subsection IN VARCHAR2,
275 p_show_data IN VARCHAR2) IS
276
277 l_header_msg VARCHAR2(32000);
278 l_title VARCHAR2(80);
279 l_start_date VARCHAR2(80);
280 l_end_date VARCHAR2(80);
281
282 BEGIN
283
284 -- Print message to conc log
285 output(get_message('HRI_407465_LOG_TO_OUTPUT'));
286
287 -- Get the start/end dates
288 l_start_date := fnd_date.date_to_displaydt
289 (fnd_date.canonical_to_date(p_start_date));
290 l_end_date := fnd_date.date_to_displaydt
291 (fnd_date.canonical_to_date(p_end_date));
292
293 -- Data diagnostics mode
294 IF p_show_data = 'Y' THEN
295
296 -- Output parameters in debug mode
297 IF (g_debugging) THEN
298
299 display_parameters
300 (p_start_date => l_start_date,
301 p_end_date => l_end_date,
302 p_mode => p_mode,
303 p_section => p_section,
304 p_subsection => p_subsection,
305 p_show_data => p_show_data);
306
307 END IF;
308
309 -- Get the title
310 l_title := hr_bis.bis_decode_lookup
311 ('HRI_DGNSTC_TITLE', 'DATA');
312
313 -- Display the page heading
314 output(p_text => l_title,
315 p_line_type => 'TITLE');
316
317 -- Display the report heading
318 output(p_text => l_title,
319 p_line_type => 'HEADER1');
320
321 -- Display the message that the system was diagnosed for DBI set up
322 l_header_msg := get_message('HRI_407224_DATA_SETUP_MSG',
323 l_start_date,
324 l_end_date);
325 output(p_text => l_header_msg,
326 p_line_type => 'PARAGRAPH');
327 ELSE
328
329 -- Get the title
330 l_title := hr_bis.bis_decode_lookup
331 ('HRI_DGNSTC_TITLE', 'COLLECTION');
332
333 -- Display the page heading
334 output(p_text => l_title,
335 p_line_type => 'TITLE');
336
337 -- Display the report heading
338 output(p_text => l_title,
339 p_line_type => 'HEADER1');
340
341 -- Display the message that the system was diagnosed for DBI set up
342 l_header_msg := get_message_alert('HRI_407285_COLL_SETUP_MSG');
343
344 output(p_text => l_header_msg,
345 p_line_type => 'PARAGRAPH');
346 END IF;
347
348 END display_rpt_heading;
349
350 -- ----------------------------------------------------------------------------
351 -- PROCEDURE display_title_desc displays the title and description. The
352 -- title can be of a section heading or subsection heading or the column heading
353 -- in COUNT mode
354 -- ----------------------------------------------------------------------------
355
356 PROCEDURE display_title_desc(p_title IN VARCHAR2,
357 p_desc IN VARCHAR2,
358 p_type IN VARCHAR2) IS
359
360 BEGIN
361
362 -- Split by type
363 IF p_type = 'SECTION' THEN
364
365 -- Spacer Line
366 output(p_text => null,
367 p_line_type => 'SPACER BAR');
368
369 -- Section title
370 output(p_text => p_title,
371 p_line_type => 'HEADER2');
372
373 -- Section description
374 IF p_desc IS NOT NULL THEN
375 output(p_text => p_desc,
376 p_line_type => 'PARAGRAPH');
377 END IF;
378
379 ELSIF p_type = 'SUBSECTION' THEN
380
381 -- Subsection title
382 output(p_text => p_title,
383 p_line_type => 'HEADER3');
384
385 -- Subsection description
386 IF p_title IS NOT NULL THEN
387 output(p_text => p_desc,
388 p_line_type => 'PARAGRAPH');
389 END IF;
390
391 END IF;
392
393 END display_title_desc;
394
395 -- ----------------------------------------------------------------------------
396 -- PROCEDURE debug_sup_loops prints out the person details for every person
397 -- in a supervisor loop
398 -- ----------------------------------------------------------------------------
399 PROCEDURE debug_sup_loops
400 (p_results_tab IN hri_bpl_data_setup_dgnstc.data_results_tab_type) IS
401
402 l_loop_tab hri_bpl_data_setup_dgnstc.loop_results_tab_type;
403 l_text VARCHAR2(2000);
404
405 BEGIN
406
407 -- Process only if results exist (from collection)
408 IF (p_results_tab.EXISTS(1)) THEN
409
410 -- Print title
411 output(p_text => hr_bis.bis_decode_lookup
412 ('HRI_DGNSTC_SECTION','SUP_LOOP_DETAIL'),
413 p_line_type => 'HEADER3');
414
415 -- Loop through instances of supervisor loops
416 FOR i IN p_results_tab.FIRST..p_results_tab.LAST LOOP
417
418 -- Get details of supervisor loop
419 hri_bpl_data_setup_dgnstc.debug_sup_loop
420 (p_person_id => p_results_tab(i)(5),
421 p_effective_date => p_results_tab(i)(4),
422 p_loop_tab => l_loop_tab);
423
424 -- Process only if loop exists (on system now)
425 IF (l_loop_tab.EXISTS(1)) THEN
426
427 -- Print loop heading
428 output(p_text => p_results_tab(i)(1),
429 p_line_type => 'HEADER4');
430
431 -- Print loop details
432 FOR j IN l_loop_tab.FIRST..l_loop_tab.LAST LOOP
433
434 l_text := l_loop_tab(j).person_name || ' (' ||
435 l_loop_tab(j).person_number || ') -> ' ||
436 l_loop_tab(j).supervisor_name || ' (' ||
437 l_loop_tab(j).supervisor_number || ')';
438
439 output(p_text => l_text,
440 p_line_type => 'TEXT');
441
442 END LOOP;
443
444 END IF; -- loop still exists on system
445
446 END LOOP;
447
448 END IF; -- loop exists from collection
449
450 END debug_sup_loops;
451
452 -- ----------------------------------------------------------------------------
453 -- PROCEDURE display_sql prints out all the SQL statements used
454 -- ----------------------------------------------------------------------------
455 PROCEDURE display_sql(p_sql_list IN g_sql_tab_type) IS
456
457 l_sql_stmt VARCHAR2(32000);
458
459 BEGIN
460
461 IF p_sql_list.EXISTS(1) THEN
462
463 -- Spacer Line
464 output(p_text => null,
465 p_line_type => 'SPACER BAR');
466
467 -- Section title
468 output(p_text => hr_bis.bis_decode_lookup
469 ('HRI_DGNSTC_SECTION', 'SQL'),
470 p_line_type => 'HEADER2');
471
472 -- Loop through sql table
473 FOR i IN p_sql_list.FIRST..p_sql_list.LAST LOOP
474
475 -- Format sql
476 l_sql_stmt := p_sql_list(i).sql_stmt;
477
478 -- Check there is a SQL stmt
479 IF (l_sql_stmt IS NOT NULL) THEN
480
481 -- Output section reference
482 output(p_text => '<a name="SQL_' || p_sql_list(i).section_code || '">' ||
483 get_message(p_sql_list(i).section_name) || '</a>',
484 p_line_type => 'HEADER3');
485
486 -- Output sql
487 output(p_text => l_sql_stmt,
488 p_line_type => 'PREFORMAT');
489
490 END IF;
491
492 END LOOP;
493
494 END IF;
495
496 END display_sql;
497
498 -- ----------------------------------------------------------------------------
499 -- PROCEDURE display_setup is called from the concurrent manager. This
500 -- procedure is used for displaying the Diagnostics Data Setup information as
501 -- is returned by the BPL layer.
502 --
503 -- INPUT PARAMETERS:
504 -- p_start_date : The date from which the setup is to be checked
505 -- p_end_date : The date to which the setup is to be checked
506 -- p_mode : The mode in which the report should run
507 -- p_section : The section that has to be shown in te detail format
508 -- p_subsection : The subsection that has to be shown in detail format
509 -- ----------------------------------------------------------------------------
510 PROCEDURE display_data_setup(errbuf OUT NOCOPY VARCHAR2,
511 retcode OUT NOCOPY VARCHAR2,
512 p_functional_area IN VARCHAR2,
513 p_start_date IN VARCHAR2,
514 p_end_date IN VARCHAR2,
515 p_mode IN VARCHAR2,
516 p_section IN VARCHAR2,
517 p_subsection IN VARCHAR2,
518 p_show_alerts IN VARCHAR2,
519 p_show_data IN VARCHAR2) IS
520
521 -- Cursor to get all diagnostics to run
522 CURSOR diagnostic_csr IS
523 SELECT
524 stp.object_type section_code
525 ,stp.object_type_msg_name section_heading
526 ,CASE WHEN p_mode = 'COUNT'
527 THEN NVL(stp.object_type_desc,
528 DECODE(stp.object_name_msg_name,
529 NULL, stp.object_name_desc, NULL))
530 ELSE NVL(stp.object_type_dtl_desc_msg_name,
531 DECODE(stp.object_name_msg_name,
532 NULL, stp.object_name_dtl_desc_msg_name, NULL))
533 END section_description
534 ,stp.object_name subsection_code
535 ,stp.object_name_msg_name subsection_heading
536 ,CASE WHEN p_mode = 'COUNT'
537 THEN stp.object_name_desc
538 ELSE stp.object_name_dtl_desc_msg_name
539 END subsection_description
540 ,stp.default_mode
541 ,stp.report_type
542 ,stp.count_heading
543 ,stp.col_heading1
544 ,stp.col_heading2
545 ,stp.col_heading3
546 ,stp.col_heading4
547 ,stp.col_heading5
548 ,stp.object_type_msg_name
549 ,stp.object_name_msg_name
550 ,stp.object_type_dtl_desc_msg_name
551 ,stp.object_type_desc
552 ,stp.object_name_dtl_desc_msg_name
553 ,stp.object_name_desc
554 ,stp.dynamic_sql
555 ,stp.dynamic_sql_type
556 ,stp.impact_msg_name
557 ,stp.seq_num
558 FROM
559 hri_adm_dgnstc_setup stp
560 WHERE stp.enabled_flag = 'Y'
561 AND ((stp.report_type = 'DATA' AND p_show_data = 'Y')
562 OR (stp.report_type = 'ALERT' AND p_show_alerts = 'Y' AND
563 NVL(fnd_profile.value('HRI_LOG_PRCSS_INFO'),'Y') = 'Y'))
564 AND ((stp.foundation_hr_flag ='Y' AND hri_bpl_system.is_full_hr_installed='N')
565 OR hri_bpl_system.is_full_hr_installed='Y' )
566 AND EXISTS
567 (SELECT null
568 FROM dual
569 WHERE (p_functional_area = 'ALL'
570 OR EXISTS
571 (SELECT null
572 FROM hri_adm_dgnstc_sbscrb sbs
573 WHERE sbs.functional_area_cd = p_functional_area
574 AND sbs.object_name = stp.object_name
575 AND sbs.object_type = stp.object_type)))
576 ORDER BY
577 DECODE(stp.functional_area_cd,
578 'PPL_MNGMNT', null,
579 stp.functional_area_cd) ASC NULLS FIRST
580 ,stp.report_type desc
581 ,stp.seq_num;
582
583 l_col_list hri_bpl_conc_log.col_list_tab_type;
584 l_no_cols PLS_INTEGER;
585 l_row_limit PLS_INTEGER;
586 l_count PLS_INTEGER;
587 l_previous_section VARCHAR2(240);
588 l_sql_list g_sql_tab_type;
589 l_results_tab hri_bpl_data_setup_dgnstc.data_results_tab_type;
590 l_impact BOOLEAN;
591 l_impact_msg VARCHAR2(32000);
592 l_doc_links_url VARCHAR2(32000);
593 l_subsection_code VARCHAR2(240);
594 l_section_code VARCHAR2(240);
595 l_show_detail BOOLEAN;
596
597 BEGIN
598
599 -- Set the global varibales
600 set_concurrent_logging(TRUE);
601 set_debugging(FALSE);
602 l_count := 0;
603
604 g_functional_area := p_functional_area;
605 -- Populate the global table to carry the flags for the
606 -- Present objects in Diagnostics Subscription table.
607 --
608
609 HRI_BPL_SETUP_DIAGNOSTIC.pplt_obj_farea_tab; -- Procedure call to populate the Global table.
610
611 -- Get the section code from the parameters
612 IF (p_section IS NULL OR
613 p_section = 'NA_EDW') THEN
614 l_section_code := NULL;
615 ELSE
616 l_section_code := SUBSTR(p_section, INSTR(p_section, '|') + 1);
617 END IF;
618
619
620
621 -- Get the subsection code from the parameters
622 IF (p_subsection IS NULL OR
623 p_subsection = 'NA_EDW') THEN
624 l_subsection_code := NULL;
625 ELSE
626 l_subsection_code := p_subsection;
627 END IF;
628
629 -- Display the parameters passed to the concurrent program
630 display_rpt_heading(p_start_date,
631 p_end_date,
632 p_mode,
633 p_section,
634 p_subsection,
635 p_show_data);
636
637 -- Display section navigation list
638 output(p_text => '',
639 p_line_type => 'LIST_HEADER');
640
641 -- Loop through the diagnostics to run
642 FOR diag_rec IN diagnostic_csr LOOP
643 -- Set the Global Object Name
644 g_object_name := diag_rec.subsection_code ;
645
646 -- If section or subsection is specified filter out unwanted diagnostics
647 IF ((l_section_code IS NULL OR
648 l_section_code = diag_rec.section_code) AND
649 (l_subsection_code IS NULL OR
650 l_subsection_code = diag_rec.subsection_code)) THEN
651
652 -- Print section heading if a new section is encountered
653 IF (diag_rec.section_heading IS NOT NULL AND
654 (l_previous_section IS NULL OR
655 l_previous_section <> diag_rec.section_code)) THEN
656
657 output(p_text => '<a href="#' || diag_rec.section_code || '">' ||
658 get_message(diag_rec.section_heading) || '</a>',
659 p_line_type => 'LIST_ITEM');
660
661 END IF;
662
663 -- Keep track of section
664 l_previous_section := diag_rec.section_code;
665
666 END IF;
667
668 END LOOP;
669
670
671 output(p_text => '',
672 p_line_type => 'LIST_FOOTER');
673
674 -- Reset variables
675 l_previous_section := NULL;
676
677 -- Loop through the diagnostics to run
678 FOR diag_rec IN diagnostic_csr LOOP
679 -- Set the Global Object Name
680 g_object_name := diag_rec.subsection_code ;
681 -- If section or subsection is specified filter out unwanted diagnostics
682 IF ((l_section_code IS NULL OR
683 l_section_code = diag_rec.section_code) AND
684 (l_subsection_code IS NULL OR
685 l_subsection_code = diag_rec.subsection_code)) THEN
686
687 -- Initialize variables
688 l_col_list := g_empty_col_list;
689 l_count := l_count + 1;
690 l_sql_list(l_count).section_name := 'SECTION NOT FOUND';
691 l_sql_list(l_count).section_code := 'NA_EDW';
692
693 -- Set the detail level based on whether the section is specified
694 IF (l_section_code = diag_rec.section_code AND
695 (l_section_code = diag_rec.subsection_code OR
696 l_subsection_code = diag_rec.subsection_code)) THEN
697 l_show_detail := TRUE;
698 l_row_limit := 2000;
699 ELSE
700 l_show_detail := FALSE;
701 l_row_limit := 50;
702 END IF;
703
704 -- Print section heading if a new section is encountered
705 IF (diag_rec.section_heading IS NOT NULL AND
706 (l_previous_section IS NULL OR
707 l_previous_section <> diag_rec.section_code)) THEN
708
709 display_title_desc
710 (p_title => '<a name="' || diag_rec.section_code || '">' ||
711 get_message(diag_rec.section_heading) || '</a>',
712 p_desc => get_message(diag_rec.section_description),
713 p_type => 'SECTION');
714
715 -- Store the current section
716 l_sql_list(l_count).section_name := diag_rec.section_heading;
717 l_sql_list(l_count).section_code := diag_rec.section_code;
718
719 END IF;
720
721 -- Keep track of section
722 l_previous_section := diag_rec.section_code;
723
724 -- Print subsection heading
725 IF (diag_rec.subsection_heading IS NOT NULL) THEN
726
727 display_title_desc
728 (p_title => get_message(diag_rec.subsection_heading),
729 p_desc => get_message(diag_rec.subsection_description),
730 p_type => 'SUBSECTION');
731
732
733 -- Store the current subsection
734 l_sql_list(l_count).section_name := diag_rec.subsection_heading;
735 l_sql_list(l_count).section_code := diag_rec.subsection_code;
736
737 END IF;
738
739 -- Print collection alert message
740 IF (diag_rec.report_type = 'ALERT') THEN
741 output(p_text => get_message('HRI_407417_DGN_CLLCTN_ALRT'),
742 p_line_type => 'PARAGRAPH');
743 END IF;
744
745 -- Set up the output table
746 IF (diag_rec.count_heading IS NOT NULL AND
747 (diag_rec.default_mode = 'COUNT' OR
748 (diag_rec.default_mode = 'DETAIL_RESTRICT_COUNT' AND
749 p_mode = 'COUNT'))) THEN
750
751 -- Set up the column structure
752 l_col_list(1).column_value := get_message(diag_rec.count_heading);
753
754 ELSE
755
756 -- The first column heading must be provided
757 l_col_list(1).column_value := get_message(diag_rec.col_heading1);
758
759 -- Check the rest of the columns
760 IF (diag_rec.col_heading2 IS NOT NULL) THEN
761 l_col_list(2).column_value := get_message(diag_rec.col_heading2);
762 END IF;
763 IF (diag_rec.col_heading3 IS NOT NULL) THEN
764 l_col_list(3).column_value := get_message(diag_rec.col_heading3);
765 END IF;
766 IF (diag_rec.col_heading4 IS NOT NULL) THEN
767 l_col_list(4).column_value := get_message(diag_rec.col_heading4);
768 END IF;
769 IF (diag_rec.col_heading5 IS NOT NULL) THEN
770 l_col_list(5).column_value := get_message(diag_rec.col_heading5);
771 END IF;
772
773 END IF;
774
775 -- Get number of columns to display
776 l_no_cols := l_col_list.LAST;
777
778 -- Output the table header
779 output(p_text => null,
780 p_line_type => 'TABLE_HEADER',
781 p_col_list => l_col_list);
782
783 -- Get the diagnostic results
784 hri_bpl_data_setup_dgnstc.run_diagnostic
785 (p_object_name => diag_rec.subsection_code,
786 p_object_type => diag_rec.section_code,
787 p_mode => p_mode,
788 p_start_date => fnd_date.canonical_to_date(p_start_date),
789 p_end_date => fnd_date.canonical_to_date(p_end_date),
790 p_row_limit => l_row_limit,
791 p_results_tab => l_results_tab,
792 p_impact => l_impact,
793 p_impact_msg => l_impact_msg,
794 p_doc_links_url => l_doc_links_url,
795 p_sql_stmt => l_sql_list(l_count).sql_stmt);
796
797
798 -- Output the results
799 BEGIN
800
801 -- Loop through the rows returned
802 FOR i IN l_results_tab.FIRST..l_results_tab.LAST LOOP
803
804 -- Update the column array
805 FOR j IN 1..l_no_cols LOOP
806 l_col_list(j).column_value := l_results_tab(i)(j);
807 END LOOP;
808
809 -- Output the row
810 output(p_text => null,
811 p_line_type => 'TABLE_ROW',
812 p_col_list => l_col_list);
813
814 END LOOP;
815
816 -- Trap errors when no data is returned
817 EXCEPTION WHEN OTHERS THEN
818 null;
819 END;
820
821 -- Output the table footer
822 output(p_text => null,
823 p_line_type => 'TABLE_FOOTER');
824
825 -- Print link to sql
826 IF (l_sql_list(l_count).sql_stmt IS NOT NULL) THEN
827
828 output(p_text => '<a href="#SQL_' || l_sql_list(l_count).section_code || '">' ||
829 get_message('HRI_407464_SQL_LINK') || '</a>',
830 p_line_type => 'TEXT');
831
832 END IF;
833
834 -- Output any impact message
835 IF l_impact THEN
836
837 -- Print impact subheading
838 output(p_text => hr_bis.bis_decode_lookup
839 ('HRI_DGNSTC_SECTION', 'IMPACT'),
840 p_line_type => 'HEADER4');
841
842 -- Print impact messages
843 output(p_text => l_impact_msg,
844 p_line_type => 'TEXT');
845 output(p_text => l_doc_links_url,
846 p_line_type => 'PARAGRAPH');
847 END IF;
848
849 -- Special case, supervisor loops
850 IF (diag_rec.subsection_code = 'SUP_LOOP' AND
851 l_show_detail) THEN
852 debug_sup_loops
853 (p_results_tab => l_results_tab);
854
855 END IF;
856
857 END IF; -- Diagnostics to process
858
859 END LOOP; -- All diagnostics for area
860
861
862 -- Output message if no diagnostics were found
863 IF (l_count = 0) THEN
864 output(p_text => get_message('HRI_407416_NO_DIAG_FOUND'),
865 p_line_type => 'PARAGRAPH');
866
867 -- Otherwise output all the SQL statements
868 ELSE
869
870 display_sql
871 (p_sql_list => l_sql_list);
872
873 END IF;
874
875 -- Output footer
876 output(p_text => null,
877 p_line_type => 'FOOTER');
878
879 EXCEPTION WHEN OTHERS THEN
880
881 output('Exception Raised in display_setup');
882 output(SQLERRM);
883 output(SQLCODE);
884 RAISE;
885
886 END display_data_setup;
887
888 END hri_opl_data_setup_dgnstc;
[Home] [Help]