[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_SETUP_DIAGNOSTIC
Source
1 PACKAGE BODY hri_opl_setup_diagnostic AS
2 /* $Header: hripdgsp.pkb 120.7 2006/12/11 09:49:43 msinghai noship $ */
3
4 -- =========================================================================
5 --
6 -- OVERVIEW
7 -- --------
8 -- This package contains procedures to display the set up of DBI in
9 -- the system.
10 -- Checks is performed for the following:
11 -- (a) Profiles
12 -- (b) Fast Formulas
13 -- (c) Triggers
14 -- (d) Key DBI Tables
15 -- (e) Job Family and Job Function
16 -- (f) Geography
17 --
18 -- =========================================================================
19
20 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
21 TYPE g_varchar2_ind_tab_type IS TABLE OF VARCHAR2(240) INDEX BY VARCHAR2(30);
22
23 TYPE g_impact_msg_rec IS RECORD
24 (object_name VARCHAR2(240),
25 impact_msg VARCHAR2(32000),
26 doc_links_url VARCHAR2(3200));
27
28 TYPE g_impact_msg_tab_type IS TABLE OF g_impact_msg_rec INDEX BY BINARY_INTEGER;
29
30 TYPE TOKEN_REC IS record(
31 token varchar2(200)
32 );
33
34 g_empty_col_list hri_bpl_conc_log.col_list_tab_type;
35 g_section_names g_varchar2_ind_tab_type;
36 g_section_list g_varchar2_tab_type;
37 g_concurrent_logging BOOLEAN;
38 g_debugging BOOLEAN;
39
40 -- ----------------------------------------------------------------------------
41 -- Switches debugging messages on or off. Setting to on will
42 -- mean extra debugging information will be generated when the
43 -- process is run.
44 -- ----------------------------------------------------------------------------
45 PROCEDURE set_debugging(p_on IN BOOLEAN) IS
46
47 BEGIN
48
49 g_debugging := p_on;
50
51 END set_debugging;
52
53 -- ----------------------------------------------------------------------------
54 -- This procedure sets the global g_concurrent_logging to
55 -- the value passed in. If set log messages will be output
56 -- through fnd_file.put_line.
57 -- ----------------------------------------------------------------------------
58 PROCEDURE set_concurrent_logging(p_on IN BOOLEAN) IS
59
60 BEGIN
61
62 g_concurrent_logging := p_on;
63
64 END set_concurrent_logging;
65
66 -- ----------------------------------------------------------------------------
67 -- Prints a message to the concurrent process log and html output
68 -- ----------------------------------------------------------------------------
69 PROCEDURE output
70 (p_text IN VARCHAR2,
71 p_line_type IN VARCHAR2,
72 p_col_list IN hri_bpl_conc_log.col_list_tab_type DEFAULT g_empty_col_list,
73 p_format IN VARCHAR2 DEFAULT null) IS
74
75 BEGIN
76
77 hri_bpl_conc_log.output
78 (p_text => p_text,
79 p_col_list => p_col_list,
80 p_mode => 'HTML',
81 p_line_type => p_line_type,
82 p_format => p_format);
83
84 END output;
85
86 -- ----------------------------------------------------------------------------
87 -- Prints a message to the concurrent process log
88 -- ----------------------------------------------------------------------------
89 PROCEDURE output(p_text IN VARCHAR2) IS
90
91 BEGIN
92
93 hri_bpl_conc_log.output(p_text);
94
95 END output;
96
97 -- ----------------------------------------------------------------------------
98 -- Procedure dbg decides whether to log the passed in message
99 -- depending on whether debug mode is set.
100 -- ----------------------------------------------------------------------------
101 PROCEDURE dbg(p_text IN VARCHAR2) IS
102 --
103 BEGIN
104
105 -- Output the message if debugging is on
106 IF g_debugging THEN
107 output(p_text);
108 END IF;
109
110 END dbg;
111
112 -- ----------------------------------------------------------------------------
113 -- Function GET_MESSAGE takes the message name and returns back the
114 -- message text
115 --
116 -- INPUT PARAMETERS:
117 -- p_message: Name of the message.
118 -- ----------------------------------------------------------------------------
119 FUNCTION get_message(p_message IN VARCHAR2) RETURN VARCHAR2 IS
120
121 BEGIN
122
123 -- Return the message text
124 fnd_message.set_name('HRI', p_message);
125 RETURN fnd_message.get;
126
127 END get_message;
128
129 -- ----------------------------------------------------------------------------
130 -- Initialize global array
131 -- ----------------------------------------------------------------------------
132 PROCEDURE set_globals IS
133
134 BEGIN
135
136 g_section_names('PROFILE') := hr_bis.bis_decode_lookup
137 ('HRI_DGNSTC_SECTION', 'PROFILE');
138 g_section_names('FORMULA') := hr_bis.bis_decode_lookup
139 ('HRI_DGNSTC_SECTION', 'FORMULA');
140 g_section_names('TRIGGER') := hr_bis.bis_decode_lookup
141 ('HRI_DGNSTC_SECTION', 'TRIGGER');
142 g_section_names('TABLE') := hr_bis.bis_decode_lookup
143 ('HRI_DGNSTC_SECTION', 'TABLE');
144 g_section_names('JOB_HIERARCHY') := hr_bis.bis_decode_lookup
145 ('HRI_DGNSTC_SECTION', 'JOB_HIERARCHY');
146 g_section_names('GEOG_HIERARCHY') := hr_bis.bis_decode_lookup
147 ('HRI_DGNSTC_SECTION', 'GEOG_HIERARCHY');
148 g_section_names('BUCKET') := hr_bis.bis_decode_lookup
149 ('HRI_DGNSTC_SECTION', 'BUCKET');
150
151 g_section_list(1) := 'PROFILE';
152 g_section_list(2) := 'FORMULA';
153 g_section_list(3) := 'TRIGGER';
154 g_section_list(4) := 'TABLE';
155 g_section_list(5) := 'JOB_HIERARCHY';
156 g_section_list(6) := 'GEOG_HIERARCHY';
157 g_section_list(7) := 'BUCKET';
158
159 END set_globals;
160
161 -- ----------------------------------------------------------------------------
162 -- Gets objects
163 -- ----------------------------------------------------------------------------
164 FUNCTION get_objects(p_object_type IN VARCHAR2,
165 p_functional_area IN VARCHAR2,
166 p_object_name IN VARCHAR2 DEFAULT NULL)
167 RETURN g_varchar2_tab_type IS
168
169 -- Cursor returning all diagnostics for a given type
170 -- and functional area
171 CURSOR c_objects IS
172 SELECT
173 stp.object_name
174 FROM
175 hri_adm_dgnstc_setup stp
176 ,hri_adm_dgnstc_sbscrb sbs
177 WHERE stp.object_type= p_object_type
178 AND stp.object_name = sbs.object_name
179 AND stp.object_type = sbs.object_type
180 AND sbs.functional_area_cd = p_functional_area
181 AND (p_object_name IS NULL OR stp.object_name = p_object_name)
182 AND stp.enabled_flag='Y'
183 AND ((stp.foundation_hr_flag = 'Y' AND
184 hri_bpl_system.is_full_hr_installed = 'N')
185 OR hri_bpl_system.is_full_hr_installed = 'Y')
186 ORDER BY 1;
187
188 -- Cursor returning all diagnostics for a given type
189 CURSOR c_objects_all IS
190 SELECT stp.object_name
191 FROM
192 hri_adm_dgnstc_setup stp
193 WHERE stp.object_type= p_object_type
194 AND (p_object_name IS NULL OR stp.object_name = p_object_name)
195 AND stp.enabled_flag='Y'
196 AND ((stp.foundation_hr_flag = 'Y' AND
197 hri_bpl_system.is_full_hr_installed = 'N')
198 OR hri_bpl_system.is_full_hr_installed = 'Y')
199 ORDER BY 1;
200
201 l_object_tab g_varchar2_tab_type;
202
203 BEGIN
204
205 -- Open appropriate cursor for given functional area
206 IF (p_functional_area = 'ALL' OR
207 p_functional_area IS NULL) THEN
208
209 -- Populate object table with all objects
210 OPEN c_objects_all;
211 FETCH c_objects_all BULK COLLECT INTO
212 l_object_tab;
213 CLOSE c_objects_all;
214
215 ELSE
216
217 -- Populate object table with objects for functional area
218 OPEN c_objects;
219 FETCH c_objects BULK COLLECT INTO
220 l_object_tab;
221 CLOSE c_objects;
222
223 END IF;
224
225 -- Return object list
226 RETURN l_object_tab;
227
228 END get_objects;
229
230 -- ----------------------------------------------------------------------------
231 -- Gets sections
232 -- ----------------------------------------------------------------------------
233 FUNCTION get_sections(p_functional_area IN VARCHAR2)
234 RETURN g_varchar2_tab_type IS
235
236 l_section_tab g_varchar2_tab_type;
237 l_index PLS_INTEGER;
238 l_object_tab g_varchar2_tab_type;
239 l_object_name VARCHAR2(240);
240 l_object_type VARCHAR2(240);
241
242 BEGIN
243
244 -- Initialize number of sections found
245 l_index := 0;
246
247 -- Loop through possible sections
248 FOR i IN g_section_list.FIRST..g_section_list.LAST LOOP
249
250 -- Set object name and type for check
251 IF (g_section_list(i) = 'JOB_HIERARCHY') THEN
252 l_object_type := 'TABLE';
253 l_object_name := 'HRI_CS_JOBH_CT';
254 ELSIF (g_section_list(i) = 'GEOG_HIERARCHY') THEN
255 l_object_type := 'TABLE';
256 l_object_name := 'HRI_CS_GEO_LOCHR_CT';
257 ELSE
258 l_object_type := g_section_list(i);
259 l_object_name := NULL;
260 END IF;
261
262 -- Special case for fast formula
263 IF (g_section_list(i) = 'FORMULA') THEN
264
265 -- Get list of objects for section
266 l_object_tab := get_objects
267 (p_functional_area => p_functional_area,
268 p_object_type => 'USER_DEFN_FAST_FORMULA');
269
270 -- Check both types of formula if necessary
271 IF (NOT l_object_tab.EXISTS(1)) THEN
272 l_object_tab := get_objects
273 (p_functional_area => p_functional_area,
274 p_object_type => 'SEEDED_FAST_FORMULA');
275 END IF;
276
277 ELSE
278
279 -- Get list of objects for section
280 l_object_tab := get_objects
281 (p_functional_area => p_functional_area,
282 p_object_name => l_object_name,
283 p_object_type => l_object_type);
284 END IF;
285
286 -- If any objects to process in section, add to list
287 IF (l_object_tab.EXISTS(1)) THEN
288 l_index := l_index + 1;
289 l_section_tab(l_index) := g_section_list(i);
290 END IF;
291
292 END LOOP;
293
294 RETURN l_section_tab;
295
296 END get_sections;
297
298 -- ----------------------------------------------------------------------------
299 -- Prints a header for the output file
300 -- ----------------------------------------------------------------------------
301 PROCEDURE display_header(p_functional_area IN VARCHAR2) IS
302
303 l_header_msg VARCHAR2(32000);
304 l_title VARCHAR2(80);
305 l_section_tab g_varchar2_tab_type;
306
307 BEGIN
308
309 -- Print message to conc log
310 output(get_message('HRI_407465_LOG_TO_OUTPUT'));
311
312 -- Get title and header message
313 l_title := hr_bis.bis_decode_lookup('HRI_DGNSTC_TITLE', 'SYSTEM');
314 l_header_msg := get_message('HRI_407180_SETUP_MSG');
315
316 -- Print html header
317 output(p_text => l_title,
318 p_line_type => 'TITLE');
319
320 -- Display log header
321 output(p_text => l_title,
322 p_line_type => 'HEADER1');
323
324 output(p_text => l_header_msg,
325 p_line_type => 'PARAGRAPH');
326
327 -- Get list of all sections to be displayed
328 l_section_tab := get_sections(p_functional_area);
329
330 -- Print list
331 IF (l_section_tab.EXISTS(1)) THEN
332
333 output(p_text => '',
334 p_line_type => 'LIST_HEADER');
335
336 FOR i IN l_section_tab.FIRST..l_section_tab.LAST LOOP
337 output(p_text => '<a href="#' || l_section_tab(i) || '">' ||
338 g_section_names(l_section_tab(i)) || '</a>',
339 p_line_type => 'LIST_ITEM');
340 END LOOP;
341
342 output(p_text => '',
343 p_line_type => 'LIST_FOOTER');
344
345 END IF;
346
347 END display_header;
348
349 -- ----------------------------------------------------------------------------
350 -- Prints a footer for the output file
351 -- ----------------------------------------------------------------------------
352 PROCEDURE display_footer IS
353
354 BEGIN
355
356 -- Print html footer
357 output(p_text => null,
358 p_line_type => 'FOOTER');
359
360 END display_footer;
361
362 -- ----------------------------------------------------------------------------
363 -- Prints output messages
364 -- ----------------------------------------------------------------------------
365 PROCEDURE display_impact_messages
366 (p_impact_msg_tab IN g_impact_msg_tab_type,
367 p_index IN PLS_INTEGER) IS
368
369 BEGIN
370
371 -- Display the impact messages
372 IF p_index > 0 THEN
373
374 -- Print impact subheading
375 output(p_text => hr_bis.bis_decode_lookup('HRI_DGNSTC_SECTION', 'IMPACT'),
376 p_line_type => 'HEADER3');
377
378 -- Loop through impact messages
379 FOR i IN 1..p_index LOOP
380
381 -- Print impact messages
382 output(p_text => p_impact_msg_tab(i).object_name,
383 p_line_type => 'HEADER4');
384 output(p_text => p_impact_msg_tab(i).impact_msg,
385 p_line_type => 'TEXT');
386 output(p_text => p_impact_msg_tab(i).doc_links_url,
387 p_line_type => 'PARAGRAPH');
388
389 END LOOP;
390
391 END IF;
392
393 END display_impact_messages;
394
395
396 -- ----------------------------------------------------------------------------
397 -- Procedure display_section_header prints a section header
398 -- ----------------------------------------------------------------------------
399 PROCEDURE display_section_header(p_text IN VARCHAR2) IS
400
401 BEGIN
402
403 output(p_text => null,
404 p_line_type => 'SPACER BAR');
405
406 output(p_text => '<a name="' || p_text || '">' ||
407 g_section_names(p_text) || '</a>',
408 p_line_type => 'HEADER2');
409
410 END display_section_header;
411
412 -- ----------------------------------------------------------------------------
413 -- Procedure display_profiles checks the values for the profiles defined in the
414 -- set up diagnostics table.
415 -- ----------------------------------------------------------------------------
416 PROCEDURE display_profiles(p_functional_area IN VARCHAR2) IS
417
418 l_user_profile_name VARCHAR2(240);
419 l_profile_value VARCHAR2(240);
420 l_profile_tab g_varchar2_tab_type;
421 l_impact_msg_tab g_impact_msg_tab_type;
422 l_col_list hri_bpl_conc_log.col_list_tab_type;
423 l_impact BOOLEAN;
424 l_impact_msg VARCHAR2(32000);
425 l_doc_links_url VARCHAR2(32000);
426 l_impact_index PLS_INTEGER;
427
428 BEGIN
429
430 -- Get list of profile options to process
431 l_profile_tab := get_objects
432 (p_object_type => 'PROFILE',
433 p_functional_area => p_functional_area);
434
435 -- Check there is something to do
436 IF (l_profile_tab.EXISTS(1)) THEN
437
438 -- Output the section header
439 display_section_header('PROFILE');
440
441 -- Set up the column structure
442 l_col_list(1).column_value := hr_bis.bis_decode_lookup
443 ('HRI_DGNSTC_SECTION', 'PROFILE_NAME');
444 l_col_list(1).column_length := 54;
445 l_col_list(2).column_value := hr_bis.bis_decode_lookup
446 ('HRI_DGNSTC_SECTION', 'VALUE');
447 l_col_list(2).column_length := 20;
448
449 -- Output the table header
450 output(p_text => null,
451 p_line_type => 'TABLE_HEADER',
452 p_col_list => l_col_list);
453
454 -- Initialize the impact table
455 l_impact_index := 0;
456
457 -- Loop through profile diagnostics
458 FOR i IN l_profile_tab.FIRST..l_profile_tab.LAST LOOP
459
460 -- Get the profile option details
461 hri_bpl_setup_diagnostic.check_profile_option
462 (p_profile_name => l_profile_tab(i),
463 p_functional_area => p_functional_area,
464 p_user_profile_name => l_user_profile_name,
465 p_profile_value => l_profile_value,
466 p_impact => l_impact,
467 p_impact_msg => l_impact_msg,
468 p_doc_links_url => l_doc_links_url);
469
470 -- Display the profile name and value
471 l_col_list(1).column_value := l_user_profile_name;
472 l_col_list(2).column_value := l_profile_value;
473 output(p_text => null,
474 p_line_type => 'TABLE_ROW',
475 p_col_list => l_col_list);
476
477 -- Add impact to table
478 IF l_impact THEN
479 l_impact_index := l_impact_index + 1;
480 l_impact_msg_tab(l_impact_index).object_name := l_user_profile_name;
481 l_impact_msg_tab(l_impact_index).impact_msg := l_impact_msg;
482 l_impact_msg_tab(l_impact_index).doc_links_url := l_doc_links_url;
483 END IF;
484
485 END LOOP;
486
487 -- Output the table footer
488 output(p_text => null,
489 p_line_type => 'TABLE_FOOTER');
490
491 END IF;
492
493 -- Display any impact messages
494 display_impact_messages
495 (p_impact_msg_tab => l_impact_msg_tab,
496 p_index => l_impact_index);
497
498 EXCEPTION WHEN OTHERS THEN
499
500 output('Exception Raised in display_profiles');
501 RAISE;
502
503 END display_profiles;
504
505 -- ----------------------------------------------------------------------------
506 -- Procedure display_fast_formula checks the status of all the fast formulas
507 -- in the set up diagnostics table.
508 -- ----------------------------------------------------------------------------
509 PROCEDURE display_fast_formula(p_functional_area IN VARCHAR2) IS
510
511 l_formula_list g_varchar2_tab_type;
512 l_user_formula_list g_varchar2_tab_type;
513 l_impact_msg_tab g_impact_msg_tab_type;
514 l_col_list hri_bpl_conc_log.col_list_tab_type;
515 l_impact_index PLS_INTEGER;
516 l_formula_tab hri_bpl_setup_diagnostic.fast_formula_tab_type;
517 l_formula_impact hri_bpl_setup_diagnostic.impact_msg_tab_type;
518 l_msg_code VARCHAR2(240);
519
520 BEGIN
521
522 -- Initialize variables
523 l_impact_index := 0;
524
525 -- Get list of seeded fast formulas to process
526 l_formula_list := get_objects
527 (p_object_type => 'SEEDED_FAST_FORMULA',
528 p_functional_area => p_functional_area);
529
530 -- Get list of user defined fast formulas to process
531 l_user_formula_list := get_objects
532 (p_object_type => 'USER_DEFN_FAST_FORMULA',
533 p_functional_area => p_functional_area);
534
535 -- If there is any formulas to process
536 IF (l_formula_list.EXISTS(1) OR
537 l_user_formula_list.EXISTS(1)) THEN
538
539 -- Print section header
540 display_section_header('FORMULA');
541
542 END IF;
543
544 -- Display diagnostic output for any seeded fast formulas
545 IF (l_formula_list.EXISTS(1)) THEN
546
547 output(p_text => hr_bis.bis_decode_lookup('HRI_DGNSTC_SECTION', 'SYSTEM'),
548 p_line_type => 'HEADER3');
549
550 -- Set up the column structure
551 l_col_list(1).column_value := hr_bis.bis_decode_lookup
552 ('HRI_DGNSTC_SECTION', 'FORMULA_NAME');
553 l_col_list(1).column_length := 50;
554 l_col_list(2).column_value := hr_bis.bis_decode_lookup
555 ('HRI_DGNSTC_SECTION', 'STATUS');
556 l_col_list(2).column_length := 10;
557
558 -- Print table header
559 output(p_text => null,
560 p_line_type => 'TABLE_HEADER',
561 p_col_list => l_col_list);
562
563 -- Loop through the seeded fast formulas in the diagnostics table
564 FOR i IN l_formula_list.FIRST..l_formula_list.LAST LOOP
565
566 -- Get formula information
567 hri_bpl_setup_diagnostic.check_fast_formula
568 (p_ff_name => l_formula_list(i),
569 p_functional_area => p_functional_area,
570 p_type => 'SEEDED',
571 p_formula_tab => l_formula_tab,
572 p_impact_msg_tab => l_formula_impact);
573
574 -- Seeded fast formula expect only one record
575 IF (l_formula_tab.EXISTS(1)) THEN
576
577 -- Update the column structure
578 l_col_list(1).column_value := l_formula_list(i);
579 l_col_list(2).column_value := l_formula_tab(1).status;
580
581 -- Output the row with seeded formula info
582 output(p_text => null,
583 p_line_type => 'TABLE_ROW',
584 p_col_list => l_col_list);
585
586 -- Store any impact message
587 IF (l_formula_tab(1).impact_msg IS NOT NULL) THEN
588 l_impact_index := l_impact_index + 1;
589 l_impact_msg_tab(l_impact_index).object_name := l_formula_list(i);
590 l_impact_msg_tab(l_impact_index).impact_msg := l_formula_tab(1).impact_msg;
591 END IF;
592
593 END IF;
594
595 END LOOP;
596
597 -- Output the table footer
598 output(p_text => null,
599 p_line_type => 'TABLE_FOOTER');
600
601 END IF;
602
603 -- Display diagnostic output for any user defined fast formulas
604 IF (l_user_formula_list.EXISTS(1)) THEN
605
606 -- Output section heading for user defined formulas
607 output(p_text => hr_bis.bis_decode_lookup('HRI_DGNSTC_SECTION', 'USER'),
608 p_line_type => 'HEADER3');
609
610 -- Set up the column structure
611 l_col_list(1).column_value := hr_bis.bis_decode_lookup
612 ('HRI_DGNSTC_SECTION', 'FORMULA_NAME');
613 l_col_list(1).column_length := 30;
614 l_col_list(2).column_value := hr_bis.bis_decode_lookup
615 ('HRI_DGNSTC_SECTION', 'BUSINESS_GROUP');
616 l_col_list(2).column_length := 35;
617 l_col_list(3).column_value := hr_bis.bis_decode_lookup
618 ('HRI_DGNSTC_SECTION', 'STATUS');
619 l_col_list(3).column_length := 10;
620
621 -- Output table header
622 output(p_text => null,
623 p_line_type => 'TABLE_HEADER',
624 p_col_list => l_col_list);
625
626 -- Loop through the user defined fast formulas in the diagnostics table
627 FOR i IN l_user_formula_list.FIRST..l_user_formula_list.LAST LOOP
628
629 -- Get formula information
630 hri_bpl_setup_diagnostic.check_fast_formula
631 (p_ff_name => l_user_formula_list(i),
632 p_functional_area => p_functional_area,
633 p_type => 'USER',
634 p_formula_tab => l_formula_tab,
635 p_impact_msg_tab => l_formula_impact);
636
637 -- Loop through results
638 IF (l_formula_tab.EXISTS(1)) THEN
639 FOR j IN l_formula_tab.FIRST..l_formula_tab.LAST LOOP
640
641 -- Update the column record
642 l_col_list(1).column_value := l_user_formula_list(i);
643 l_col_list(2).column_value := l_formula_tab(j).business_group_name;
644 l_col_list(3).column_value := l_formula_tab(j).status;
645
646 -- Display the information about the user fast formula
647 output(p_text => null,
648 p_line_type => 'TABLE_ROW',
649 p_col_list => l_col_list);
650
651 -- Store any impact message
652 IF (l_formula_tab(1).impact_msg IS NOT NULL) THEN
653 l_impact_index := l_impact_index + 1;
654 l_impact_msg_tab(l_impact_index).object_name := l_user_formula_list(i);
655 l_impact_msg_tab(l_impact_index).impact_msg := l_formula_tab(1).impact_msg;
656 END IF;
657
658 END LOOP;
659 END IF;
660
661 -- Store any general impact messages
662 BEGIN
663 l_msg_code := l_formula_impact.FIRST;
664 WHILE l_msg_code IS NOT NULL LOOP
665 l_impact_index := l_impact_index + 1;
666 l_impact_msg_tab(l_impact_index).object_name := l_user_formula_list(i);
667 l_impact_msg_tab(l_impact_index).impact_msg :=
668 l_formula_impact(l_msg_code).impact_msg;
669 l_impact_msg_tab(l_impact_index).doc_links_url :=
670 l_formula_impact(l_msg_code).doc_links_url;
671 l_msg_code := l_formula_impact.NEXT(l_msg_code);
672 END LOOP;
673 EXCEPTION WHEN OTHERS THEN
674 null;
675 END;
676
677 END LOOP;
678
679 -- Output the table footer
680 output(p_text => null,
681 p_line_type => 'TABLE_FOOTER');
682
683 END IF;
684
685 -- Display any impact messages
686 display_impact_messages
687 (p_impact_msg_tab => l_impact_msg_tab,
688 p_index => l_impact_index);
689
690 EXCEPTION WHEN OTHERS THEN
691
692 output('Exception Raised in display_fast_formula');
693 RAISE;
694
695 END display_fast_formula;
696
697 -- ----------------------------------------------------------------------------
698 -- PROCEDURE display_triggers checks whether the triggers are generated and
699 -- enabled
700 -- ----------------------------------------------------------------------------
701 PROCEDURE display_triggers(p_functional_area IN VARCHAR2) IS
702
703 l_trigger_list g_varchar2_tab_type;
704 l_impact_msg_tab g_impact_msg_tab_type;
705 l_impact_index PLS_INTEGER;
706 l_col_list hri_bpl_conc_log.col_list_tab_type;
707 l_generated VARCHAR2(240);
708 l_enabled VARCHAR2(240);
709 l_status VARCHAR2(240);
710 l_impact BOOLEAN;
711 l_impact_msg VARCHAR2(32000);
712 l_doc_links_url VARCHAR2(32000);
713
714 BEGIN
715
716 -- Initialize variables
717 l_impact_index := 0;
718
719 -- Get list of triggers to process
720 l_trigger_list := get_objects
721 (p_object_type => 'TRIGGER',
722 p_functional_area => p_functional_area);
723
724 -- Display diagnostic output for any triggers
725 IF (l_trigger_list.EXISTS(1)) THEN
726
727 -- Print the section heading
728 display_section_header('TRIGGER');
729
730 -- Set up the column structure
731 l_col_list(1).column_value := hr_bis.bis_decode_lookup
732 ('HRI_DGNSTC_SECTION', 'TRIGGER_NAME');
733 l_col_list(1).column_length := 40;
734 l_col_list(2).column_value := hr_bis.bis_decode_lookup
735 ('HRI_DGNSTC_SECTION', 'GENERATED');
736 l_col_list(2).column_length := 10;
737 l_col_list(3).column_value := hr_bis.bis_decode_lookup
738 ('HRI_DGNSTC_SECTION', 'ENABLED');
739 l_col_list(3).column_length := 8;
740 l_col_list(4).column_value := hr_bis.bis_decode_lookup
741 ('HRI_DGNSTC_SECTION', 'STATUS');
742 l_col_list(4).column_length := 10;
743
744 -- Output the table header
745 output(p_text => null,
746 p_line_type => 'TABLE_HEADER',
747 p_col_list => l_col_list);
748
749 -- Loop through triggers to process
750 FOR i IN l_trigger_list.FIRST..l_trigger_list.LAST LOOP
751
752 -- Get the trigger info
753 hri_bpl_setup_diagnostic.check_triggers
754 (p_trigger_name => l_trigger_list(i),
755 p_functional_area => p_functional_area,
756 p_generated => l_generated,
757 p_enabled => l_enabled,
758 p_status => l_status,
759 p_impact => l_impact,
760 p_impact_msg => l_impact_msg,
761 p_doc_links_url => l_doc_links_url);
762
763 -- Update the column structure
764 l_col_list(1).column_value := l_trigger_list(i);
765 l_col_list(2).column_value := l_generated;
766 l_col_list(3).column_value := l_enabled;
767 l_col_list(4).column_value := l_status;
768
769 -- Display the trigger information
770 output(p_text => null,
771 p_line_type => 'TABLE_ROW',
772 p_col_list => l_col_list);
773
774 -- Store any impact messages
775 IF l_impact THEN
776 l_impact_index := l_impact_index + 1;
777 l_impact_msg_tab(l_impact_index).object_name := l_trigger_list(i);
778 l_impact_msg_tab(l_impact_index).impact_msg := l_impact_msg;
779 l_impact_msg_tab(l_impact_index).doc_links_url := l_doc_links_url;
780 END IF;
781
782 END LOOP;
783
784 -- Output the table footer
785 output(p_text => null,
786 p_line_type => 'TABLE_FOOTER');
787
788 END IF;
789
790 -- Display any impact messages
791 display_impact_messages
792 (p_impact_msg_tab => l_impact_msg_tab,
793 p_index => l_impact_index);
794
795 EXCEPTION WHEN OTHERS THEN
796
797 output('Exception Raised in display_triggers');
798 RAISE;
799
800 END display_triggers;
801
802 -- ----------------------------------------------------------------------------
803 -- PROCEDURE display_dbi_tables to check if the key DBI tables are populated
804 -- ----------------------------------------------------------------------------
805 PROCEDURE display_dbi_tables(p_functional_area IN VARCHAR2) IS
806
807 l_table_list g_varchar2_tab_type;
808 l_impact_msg_tab g_impact_msg_tab_type;
809 l_impact_index PLS_INTEGER;
810 l_col_list hri_bpl_conc_log.col_list_tab_type;
811 l_status VARCHAR2(240);
812 l_impact BOOLEAN;
813 l_impact_msg VARCHAR2(32000);
814 l_doc_links_url VARCHAR2(32000);
815
816 BEGIN
817
818 -- Initialize variables
819 l_impact_index := 0;
820
821 -- Get list of tables to process
822 l_table_list := get_objects
823 (p_object_type => 'TABLE',
824 p_functional_area => p_functional_area);
825
826 -- Display diagnostic output for any tables
827 IF (l_table_list.EXISTS(1)) THEN
828
829 -- Print the section heading
830 display_section_header('TABLE');
831
832 -- Set up the column structure
833 l_col_list(1).column_value := hr_bis.bis_decode_lookup
834 ('HRI_DGNSTC_SECTION', 'TABLE_NAME');
835 l_col_list(1).column_length := 40;
836 l_col_list(2).column_value := hr_bis.bis_decode_lookup
837 ('HRI_DGNSTC_SECTION', 'STATUS');
838 l_col_list(2).column_length := 10;
839
840 -- Output the table header
841 output(p_text => null,
842 p_line_type => 'TABLE_HEADER',
843 p_col_list => l_col_list);
844
845 -- Loop through tables to process
846 FOR i IN l_table_list.FIRST..l_table_list.LAST LOOP
847
848 -- Get the table info
849 hri_bpl_setup_diagnostic.check_dbi_tables
850 (p_table_name => l_table_list(i),
851 p_functional_area => p_functional_area,
852 p_status => l_status,
853 p_impact => l_impact,
854 p_impact_msg => l_impact_msg,
855 p_doc_links_url => l_doc_links_url);
856
857 -- Update the column structure
858 l_col_list(1).column_value := l_table_list(i);
859 l_col_list(2).column_value := l_status;
860
861 -- Display the table information
862 output(p_text => null,
863 p_line_type => 'TABLE_ROW',
864 p_col_list => l_col_list);
865
866 -- Only display first impact message to avoid repetition
867 IF (l_impact AND l_impact_index = 0) THEN
868 l_impact_index := l_impact_index + 1;
869 l_impact_msg_tab(l_impact_index).impact_msg := l_impact_msg;
870 l_impact_msg_tab(l_impact_index).doc_links_url := l_doc_links_url;
871 END IF;
872
873 END LOOP;
874
875 -- Output the table footer
876 output(p_text => null,
877 p_line_type => 'TABLE_FOOTER');
878
879 END IF;
880
881 -- Display any impact messages
882 display_impact_messages
883 (p_impact_msg_tab => l_impact_msg_tab,
884 p_index => l_impact_index);
885
886 EXCEPTION WHEN OTHERS THEN
887
888 output('Exception Raised in display_dbi_tables');
889 RAISE;
890
891 END display_dbi_tables;
892
893 -- ----------------------------------------------------------------------------
894 -- PROCEDURE display_job checks if the set up for Job Family and Job Function. It
895 -- checks all the structures in Job Flexfield for Job Family and Function
896 -- segments
897 -- ----------------------------------------------------------------------------
898 PROCEDURE display_job (p_functional_area IN VARCHAR2) IS
899
900 l_table_list g_varchar2_tab_type;
901 l_impact_msg_tab g_impact_msg_tab_type;
902 l_impact_index PLS_INTEGER;
903 l_index VARCHAR2(240);
904 l_col_list hri_bpl_conc_log.col_list_tab_type;
905 l_job_family_mode VARCHAR2(30);
906 l_job_function_mode VARCHAR2(30);
907 l_flex_structure_tab hri_bpl_setup_diagnostic.job_flex_tab_type;
908 l_status VARCHAR2(240);
909 l_impact BOOLEAN;
910 l_impact_msg VARCHAR2(32000);
911 l_doc_links_url VARCHAR2(32000);
912
913 BEGIN
914
915 -- Initialize variables
916 l_impact_index := 0;
917
918 -- Get list of tables to process
919 l_table_list := get_objects
920 (p_object_type => 'TABLE',
921 p_functional_area => p_functional_area,
922 p_object_name => 'HRI_CS_JOBH_CT');
923
924 -- Display diagnostic output if job table is in list
925 IF (l_table_list.EXISTS(1)) THEN
926
927 -- Print the section heading
928 display_section_header('JOB_HIERARCHY');
929
930 -- Set up the column structure
931 l_col_list(1).column_value := hr_bis.bis_decode_lookup
932 ('HRI_DGNSTC_SECTION', 'FLEXFIELD_STRUCTURE');
933 l_col_list(1).column_length := 28;
934 l_col_list(2).column_value := hr_bis.bis_decode_lookup
935 ('HRI_DGNSTC_SECTION', 'JOB_FAMILY');
936 l_col_list(2).column_length := 13;
937 l_col_list(3).column_value := hr_bis.bis_decode_lookup
938 ('HRI_DGNSTC_SECTION', 'JOB_FUNCTION');
939 l_col_list(3).column_length := 13;
940
941 -- Output the table header
942 output(p_text => null,
943 p_line_type => 'TABLE_HEADER',
944 p_col_list => l_col_list);
945
946 -- Get the job structure details
947 hri_bpl_setup_diagnostic.check_job
948 (p_job_family_mode => l_job_family_mode,
949 p_job_function_mode => l_job_function_mode,
950 p_flex_structure_tab => l_flex_structure_tab,
951 p_impact => l_impact,
952 p_impact_msg => l_impact_msg,
953 p_doc_links_url => l_doc_links_url);
954
955 -- Update the column structure
956 l_col_list(1).column_value := hr_bis.bis_decode_lookup
957 ('HRI_DGNSTC_SECTION', 'FLEXFIELD_TYPE');
958 l_col_list(2).column_value := l_job_family_mode;
959 l_col_list(3).column_value := l_job_function_mode;
960
961 -- Display the flexfield type information
962 output(p_text => null,
963 p_line_type => 'TABLE_ROW',
964 p_col_list => l_col_list);
965
966 -- Loop through all the structures returned
967 BEGIN
968
969 -- Set index to first structure
970 l_index := l_flex_structure_tab.FIRST;
971
972 -- Loop through structures
973 WHILE l_index IS NOT NULL LOOP
974
975 -- Update the column structure
976 l_col_list(1).column_value :=
977 l_flex_structure_tab(l_index).structure_name;
978 l_col_list(2).column_value :=
979 l_flex_structure_tab(l_index).job_family_defined_msg;
980 l_col_list(3).column_value :=
981 l_flex_structure_tab(l_index).job_function_defined_msg;
982
983 -- Display the flexfield structure information
984 output(p_text => null,
985 p_line_type => 'TABLE_ROW',
986 p_col_list => l_col_list);
987
988 -- Move index on
989 l_index := l_flex_structure_tab.NEXT(l_index);
990
991 END LOOP;
992
993 -- Trap exceptions when no structures are returned
994 EXCEPTION WHEN OTHERS THEN
995 null;
996 END;
997
998 -- Store any impact message
999 IF l_impact THEN
1000 l_impact_index := l_impact_index + 1;
1001 l_impact_msg_tab(l_impact_index).impact_msg := l_impact_msg;
1002 l_impact_msg_tab(l_impact_index).doc_links_url := l_doc_links_url;
1003 END IF;
1004
1005 -- Output the table footer
1006 output(p_text => null,
1007 p_line_type => 'TABLE_FOOTER');
1008
1009 END IF;
1010
1011 -- Display any impact messages
1012 display_impact_messages
1013 (p_impact_msg_tab => l_impact_msg_tab,
1014 p_index => l_impact_index);
1015
1016 EXCEPTION WHEN OTHERS THEN
1017
1018 output('Exception Raised in display_job');
1019 RAISE;
1020
1021 END display_job;
1022
1023 -- ----------------------------------------------------------------------------
1024 -- PROCEDURE display_geography checks if the set up for Region. It
1025 -- checks all the structures in Addistional Location Details for the Region
1026 -- segment.
1027 -- ----------------------------------------------------------------------------
1028 PROCEDURE display_geography(p_functional_area IN VARCHAR2) IS
1029
1030 l_table_list g_varchar2_tab_type;
1031 l_impact_msg_tab g_impact_msg_tab_type;
1032 l_impact_index PLS_INTEGER;
1033 l_col_list hri_bpl_conc_log.col_list_tab_type;
1034 l_context_name VARCHAR2(240);
1035 l_flex_column VARCHAR2(240);
1036 l_status VARCHAR2(240);
1037 l_impact BOOLEAN;
1038 l_impact_msg VARCHAR2(32000);
1039
1040 BEGIN
1041
1042 -- Initialize variables
1043 l_impact_index := 0;
1044
1045 -- Get list of tables to process
1046 l_table_list := get_objects
1047 (p_object_type => 'TABLE',
1048 p_functional_area => p_functional_area,
1049 p_object_name => 'HRI_CS_GEO_LOCHR_CT');
1050
1051 -- Display diagnostic output if geography table is in list
1052 IF (l_table_list.EXISTS(1)) THEN
1053
1054 -- Print the section heading
1055 display_section_header('GEOG_HIERARCHY');
1056
1057 -- Set up the column structure
1058 l_col_list(1).column_value := hr_bis.bis_decode_lookup
1059 ('HRI_DGNSTC_SECTION', 'FLEXFIELD_STRUCTURE');
1060 l_col_list(1).column_length := 25;
1061 l_col_list(2).column_value := hr_bis.bis_decode_lookup
1062 ('HRI_DGNSTC_SECTION', 'GEOG_REGION_SEGMENT');
1063 l_col_list(2).column_length := 16;
1064 l_col_list(3).column_value := hr_bis.bis_decode_lookup
1065 ('HRI_DGNSTC_SECTION', 'STATUS');
1066 l_col_list(3).column_length := 10;
1067
1068 -- Output the table header
1069 output(p_text => null,
1070 p_line_type => 'TABLE_HEADER',
1071 p_col_list => l_col_list);
1072
1073 -- Get the diagnostic information
1074 hri_bpl_setup_diagnostic.check_geography
1075 (p_context_name => l_context_name,
1076 p_flex_column => l_flex_column,
1077 p_status => l_status,
1078 p_impact => l_impact,
1079 p_impact_msg => l_impact_msg);
1080
1081 -- Update the column structure
1082 l_col_list(1).column_value := l_context_name;
1083 l_col_list(2).column_value := l_flex_column;
1084 l_col_list(3).column_value := l_status;
1085
1086 -- Display geography information
1087 output(p_text => null,
1088 p_line_type => 'TABLE_ROW',
1089 p_col_list => l_col_list);
1090
1091 -- Store any impact message
1092 IF l_impact THEN
1093 l_impact_index := l_impact_index + 1;
1094 l_impact_msg_tab(l_impact_index).impact_msg := l_impact_msg;
1095 END IF;
1096
1097 -- Output the table footer
1098 output(p_text => null,
1099 p_line_type => 'TABLE_FOOTER');
1100
1101 END IF;
1102
1103 -- Display any impact messages
1104 display_impact_messages
1105 (p_impact_msg_tab => l_impact_msg_tab,
1106 p_index => l_impact_index);
1107
1108 EXCEPTION WHEN OTHERS THEN
1109
1110 output('Exception Raised in display_geography');
1111 RAISE;
1112
1113 END display_geography;
1114
1115 -- ----------------------------------------------------------------------------
1116 -- PROCEDURE display_bucket checks if buckets have the correct number of ranges
1117 -- ----------------------------------------------------------------------------
1118 PROCEDURE display_bucket(p_functional_area IN VARCHAR2) IS
1119
1120 l_bucket_list g_varchar2_tab_type;
1121 l_impact_msg_tab g_impact_msg_tab_type;
1122 l_impact_index PLS_INTEGER;
1123 l_col_list hri_bpl_conc_log.col_list_tab_type;
1124 l_user_bucket_name VARCHAR2(240);
1125 l_status VARCHAR2(240);
1126 l_impact BOOLEAN;
1127 l_impact_msg VARCHAR2(32000);
1128 l_doc_links_url VARCHAR2(32000);
1129
1130 BEGIN
1131
1132 -- Initialize variables
1133 l_impact_index := 0;
1134
1135 -- Get list of tables to process
1136 l_bucket_list := get_objects
1137 (p_object_type => 'BUCKET',
1138 p_functional_area => p_functional_area);
1139
1140 -- Display diagnostic output for any buckets
1141 IF (l_bucket_list.EXISTS(1)) THEN
1142
1143 -- Print the section heading
1144 display_section_header('BUCKET');
1145
1146 -- Set up the column structure
1147 l_col_list(1).column_value := hr_bis.bis_decode_lookup
1148 ('HRI_DGNSTC_SECTION', 'BUCKET_NAME');
1149 l_col_list(1).column_length := 60;
1150 l_col_list(2).column_value := hr_bis.bis_decode_lookup
1151 ('HRI_DGNSTC_SECTION', 'STATUS');
1152 l_col_list(2).column_length := 10;
1153
1154 -- Output the table header
1155 output(p_text => null,
1156 p_line_type => 'TABLE_HEADER',
1157 p_col_list => l_col_list);
1158
1159 -- Loop through buckets to process
1160 FOR i IN l_bucket_list.FIRST..l_bucket_list.LAST LOOP
1161
1162 -- Get the bucket info
1163 hri_bpl_setup_diagnostic.check_buckets
1164 (p_bucket_name => l_bucket_list(i),
1165 p_functional_area => p_functional_area,
1166 p_user_bucket_name => l_user_bucket_name,
1167 p_status => l_status,
1168 p_impact => l_impact,
1169 p_impact_msg => l_impact_msg,
1170 p_doc_links_url => l_doc_links_url);
1171
1172 -- Update the column structure
1173 l_col_list(1).column_value := l_user_bucket_name;
1174 l_col_list(2).column_value := l_status;
1175
1176 -- Display the table information
1177 output(p_text => null,
1178 p_line_type => 'TABLE_ROW',
1179 p_col_list => l_col_list);
1180
1181 -- Store any impact messages
1182 IF l_impact THEN
1183 l_impact_index := l_impact_index + 1;
1184 l_impact_msg_tab(l_impact_index).object_name := l_user_bucket_name;
1185 l_impact_msg_tab(l_impact_index).impact_msg := l_impact_msg;
1186 l_impact_msg_tab(l_impact_index).doc_links_url := l_doc_links_url;
1187 END IF;
1188
1189 END LOOP;
1190
1191 -- Output the table footer
1192 output(p_text => null,
1193 p_line_type => 'TABLE_FOOTER');
1194
1195 END IF;
1196
1197 -- Display any impact messages
1198 display_impact_messages
1199 (p_impact_msg_tab => l_impact_msg_tab,
1200 p_index => l_impact_index);
1201
1202 EXCEPTION WHEN OTHERS THEN
1203
1204 output('Exception Raised in display_bucket');
1205 RAISE;
1206
1207 END display_bucket;
1208
1209 -- ----------------------------------------------------------------------------
1210 -- PROCEDURE display_setup is called from the concurrent manager which in
1211 -- turn calls the other procedures for the displaying dbi set up
1212 -- ----------------------------------------------------------------------------
1213 PROCEDURE display_setup (errbuf OUT NOCOPY VARCHAR2,
1214 retcode OUT NOCOPY VARCHAR2,
1215 p_functional_area IN VARCHAR2) IS
1216
1217 BEGIN
1218
1219 -- Set the global variables
1220 set_concurrent_logging(TRUE);
1221 set_debugging(FALSE);
1222 set_globals;
1223 HRI_BPL_SETUP_DIAGNOSTIC.pplt_obj_farea_tab;
1224 -- Print a header
1225 display_header
1226 (p_functional_area => p_functional_area);
1227
1228 -- Check the profiles
1229 display_profiles
1230 (p_functional_area => p_functional_area);
1231
1232 -- Check the fast formulas
1233 display_fast_formula
1234 (p_functional_area => p_functional_area);
1235
1236 -- Check the triggers
1237 display_triggers
1238 (p_functional_area => p_functional_area);
1239
1240 -- Check the tables
1241 display_dbi_tables
1242 (p_functional_area => p_functional_area);
1243
1244 -- Check job set up
1245 display_job
1246 (p_functional_area => p_functional_area);
1247
1248 -- Check geography set up
1249 display_geography
1250 (p_functional_area => p_functional_area);
1251
1252 -- Check the bucket setup
1253 display_bucket
1254 (p_functional_area => p_functional_area);
1255
1256 -- Print footer
1257 display_footer;
1258
1259 EXCEPTION WHEN OTHERS THEN
1260
1261 output('Exception Raised in display_setup');
1262 output(SQLERRM);
1263 output(SQLCODE);
1264 RAISE;
1265
1266 END display_setup;
1267
1268 END hri_opl_setup_diagnostic;