[Home] [Help]
PACKAGE BODY: APPS.HRI_APL_DIAGNOSTICS
Source
1 PACKAGE BODY hri_apl_diagnostics AS
2 /* $Header: hriadiag.pkb 120.2 2005/10/17 22:32:00 anmajumd noship $ */
3 --
4 e_data_error Exception;
5 --
6
7 PROCEDURE output(p_text IN VARCHAR2)
8 IS
9 --
10 l_stmnt VARCHAR2(100);
11 --
12 BEGIN
13 --
14 l_stmnt := 'BEGIN dbms_output.put_line(:v); END;';
15 --
16 EXECUTE IMMEDIATE l_stmnt USING p_text;
17 --
18 END output;
19 --
20 PROCEDURE check_object_existence
21 (p_object_name IN VARCHAR2,
22 p_object_type IN VARCHAR2,
23 p_subscr_or_diag IN VARCHAR2 DEFAULT 'D')
24 IS
25 --
26 l_obj_exists NUMBER:= 0;
27 --
28 CURSOR cur_chk_object
29 IS
30 SELECT 1
31 FROM hri_adm_dgnstc_setup
32 WHERE object_name = p_object_name
33 AND object_type = p_object_type;
34 --
35 BEGIN
36 --
37 OPEN cur_chk_object;
38 FETCH cur_chk_object INTO l_obj_exists;
39 CLOSE cur_chk_object;
40 --
41 IF (l_obj_exists <> 1) AND (p_subscr_or_diag = 'S') THEN
42 --
43 output('Entry for object ' || p_object_name || ' does not exist in diagnostics metadata table.');
44 --
45 RAISE e_data_error;
46 --
47 END IF;
48
49 IF (l_obj_exists = 1) AND (p_subscr_or_diag = 'D') THEN
50 --
51 output('Object ' || p_object_name || ' already exists.');
52 --
53 RAISE e_data_error;
54 --
55 END IF;
56 --
57 EXCEPTION
58 --
59 WHEN e_data_error THEN
60 --
61 RAISE;
62 --
63 WHEN OTHERS THEN
64 --
65 RAISE;
66 --
67 END check_object_existence;
68 --
69 -- This procedure is used to check a message has been cerated
70 --
71 PROCEDURE check_message(p_msg_name IN VARCHAR2)
72 IS
73 --
74 l_exist_msg NUMBER;
75 --
76 BEGIN
77 --
78 IF (p_msg_name IS NULL) THEN
79 --
80 RETURN;
81 --
82 END IF;
83 --
84 l_exist_msg := FND_MESSAGE.GET_NUMBER('HRI',p_msg_name);
85 --
86 IF l_exist_msg IS NULL THEN
87 --
88 -- If the message does not exist then show output and raise error
89 --
90 output('The message ' || p_msg_name || ' does not exist.');
91 --
92 RAISE e_data_error;
93 --
94 END IF;
95 --
96 EXCEPTION
97 --
98 WHEN e_data_error THEN
99 --
100 RAISE;
101 --
102 WHEN OTHERS THEN
103 --
104 RAISE;
105 --
106 END check_message;
107 --
108 -- This procedure is used to check the existence of functional area
109 --
110 PROCEDURE check_functional_area(p_functional_area_cd IN VARCHAR2)
111 IS
112 --
113 l_chk_func_area NUMBER:=0;
114 --
115 CURSOR cur_chk_func_area
116 IS
117 SELECT 1
118 FROM hr_lookups
119 WHERE lookup_type = 'HRI_FUNCTIONAL_AREA'
120 AND lookup_code = p_functional_area_cd;
121 --
122 BEGIN
123 --
124 OPEN cur_chk_func_area;
125 FETCH cur_chk_func_area INTO l_chk_func_area;
126 CLOSE cur_chk_func_area;
127 --
128 IF (l_chk_func_area <> 1) THEN
129 --
130 output('Functional area ' || p_functional_area_cd || ' does not exists.');
131 --
132 RAISE e_data_error;
133 --
134 END IF;
135 --
136 EXCEPTION
137 --
138 WHEN e_data_error THEN
139 --
140 RAISE;
141 --
142 WHEN OTHERS THEN
143 --
144 RAISE;
145 --
146 END check_functional_area;
147 --
148 -- This procedure is used to check if the profile exists
149 --
150 PROCEDURE check_profile_existence(p_object_name IN VARCHAR2)
151 IS
152 --
153 l_chk_profile NUMBER:= 0;
154 --
155 CURSOR cur_chk_profile
156 IS
157 SELECT 1
158 FROM fnd_profile_options
159 WHERE profile_option_name = p_object_name;
160 --
161 BEGIN
162 --
163 OPEN cur_chk_profile;
164 FETCH cur_chk_profile INTO l_chk_profile;
165 CLOSE cur_chk_profile;
166 --
167 IF (l_chk_profile <> 1) THEN
168 --
169 output('Profile ' || p_object_name || ' does not exist.');
170 --
171 RAISE e_data_error;
172 --
173 END IF;
174 --
175 EXCEPTION
176 --
177 WHEN e_data_error THEN
178 --
179 RAISE;
180 --
181 WHEN OTHERS THEN
182 --
183 RAISE;
184 --
185 END check_profile_existence;
186 --
187 -- This procedure is used to check if the bucket has been defined
188 --
189 PROCEDURE check_bucket_existence(p_object_name IN VARCHAR2)
190 IS
191 --
192 l_chk_bucket NUMBER:=0;
193 --
194 CURSOR cur_chk_bucket
195 IS
196 SELECT 1
197 FROM bis_bucket
198 WHERE short_name = p_object_name;
199 --
200 BEGIN
201 --
202 OPEN cur_chk_bucket;
203 FETCH cur_chk_bucket INTO l_chk_bucket;
204 CLOSE cur_chk_bucket;
205 --
206 IF (l_chk_bucket <> 1) THEN
207 --
208 output('Bucket ' || p_object_name || ' does not exist.');
209 --
210 RAISE e_data_error;
211 --
212 END IF;
213 --
214 END check_bucket_existence;
215 --
216 -- This procedure is used to check the existsnce of triggers
217 --
218 PROCEDURE check_trigger_existence(p_object_name IN VARCHAR2)
219 IS
220 --
221 l_chk_trigger NUMBER:= 0;
222 --
223 CURSOR cur_chk_trigger
224 IS
225 SELECT 1
226 FROM pay_trigger_events
227 WHERE short_name = p_object_name;
228 BEGIN
229 --
230 OPEN cur_chk_trigger;
231 FETCH cur_chk_trigger INTO l_chk_trigger;
232 CLOSE cur_chk_trigger;
233 --
234 IF (l_chk_trigger <> 1) THEN
235 --
236 output('Trigger ' || p_object_name || ' does not exist.');
237 --
238 RAISE e_data_error;
239 --
240 END IF;
241 --
242 END check_trigger_existence;
243 --
244 --This procedure is used to check the existence of tables
245 --
246 PROCEDURE check_table_existence(p_object_name IN VARCHAR2, p_object_owner IN VARCHAR2)
247 IS
248 --
249 l_chk_table NUMBER:=0;
250 --
251 CURSOR cur_chk_table
252 IS
253 SELECT 1
254 FROM all_tables
255 WHERE table_name = p_object_name
256 AND owner = p_object_owner;
257 --
258 BEGIN
259 --
260 OPEN cur_chk_table;
261 FETCH cur_chk_table INTO l_chk_table;
262 CLOSE cur_chk_table;
263 --
264 IF (l_chk_table <> 1 ) THEN
265 --
266 output('Table ' || p_object_name || ' does not exist.');
267 --
268 RAISE e_data_error;
269 --
270 END IF;
271 --
272 END check_table_existence;
273 --
274 -- This procedure is used to check the existence of seeded fast formulas
275 --
276 PROCEDURE check_seeded_ff_existence(p_object_name IN VARCHAR2)
277 IS
278 --
279 l_chk_ff NUMBER:= 0;
280 --
281 CURSOR cur_chk_seeded_ff
282 IS
283 SELECT 1
284 FROM ff_formulas_f
285 WHERE formula_name = p_object_name;
286 --
287 BEGIN
288 --
289 OPEN cur_chk_seeded_ff;
290 FETCH cur_chk_seeded_ff INTO l_chk_ff;
291 CLOSE cur_chk_seeded_ff;
292 --
293 IF (l_chk_ff <> 1) THEN
294 --
295 output('Fast Formula ' || p_object_name || ' does not exist.');
296 --
297 RAISE e_data_error;
298 --
299 END IF;
300 --
301 EXCEPTION
302 --
303 WHEN e_data_error THEN
304 --
305 RAISE;
306 --
307 WHEN OTHERS THEN
308 --
309 RAISE;
310 --
311 END check_seeded_ff_existence;
312
313 --
314 -- This procedure is used to create the subscritpion for objects
315 -- Every diagnostic should have a subscription
316 --
317 PROCEDURE create_subscription(
318 p_object_name IN VARCHAR2,
319 p_object_type IN VARCHAR2,
320 p_functional_area_cd IN VARCHAR2)
321 IS
322 --
323 l_chk_cursors NUMBER:=0;
324 --
325 CURSOR cur_chk_duplication
326 IS
327 SELECT 1
328 FROM hri_adm_dgnstc_sbscrb
329 WHERE object_name = p_object_name
330 AND object_type = p_object_type
331 AND functional_area_cd = p_functional_area_cd;
332 --
333 BEGIN
334 --
335 check_functional_area(p_functional_area_cd);
336 --
337 check_object_existence(p_object_name, p_object_type,'S');
338 --
339 l_chk_cursors := 0;
340 --
341 OPEN cur_chk_duplication;
342 FETCH cur_chk_duplication INTO l_chk_cursors;
343 CLOSE cur_chk_duplication;
344 --
345 IF (l_chk_cursors = 1) THEN
346 --
347 -- If the subscription already exists, then show the output and raise error
348 --
349 output('Subscription already exists for this functional area for object ' || p_object_name || ' and object type ' || p_object_type );
350 --
351 RAISE e_data_error;
352 --
353 END IF;
354 --
355 -- Insert into the subscription table
356 --
357 INSERT INTO hri_adm_dgnstc_sbscrb
358 (object_name,
359 object_type,
360 functional_area_cd)
361 VALUES
362 (p_object_name,
363 p_object_type,
364 p_functional_area_cd);
365 --
366 COMMIT;
367 --
368 EXCEPTION
369 WHEN e_data_error THEN
370 RAISE;
371 WHEN OTHERS THEN
372 RAISE;
373 END create_subscription;
374 --
375 PROCEDURE delete_subscription
376 (p_object_name IN VARCHAR2,
377 p_object_type IN VARCHAR2,
378 p_functional_area_cd IN VARCHAR2)
379 IS
380 BEGIN
381 --
382 DELETE FROM hri_adm_dgnstc_sbscrb
383 WHERE object_name = p_object_name
384 AND object_type = p_object_type
385 AND functional_area_cd = p_functional_area_cd;
386 --
387 output(SQL%ROWCOUNT || ' row deleted.');
388 --
389 COMMIT;
390 --
391 EXCEPTION
392 --
393 WHEN OTHERS THEN
394 --
395 RAISE;
396 --
397 END delete_subscription;
398 --
399 -- This procedure is used to create diagnostics associated with profiles
400 --
401 PROCEDURE create_profile_sys_setup(
402 p_object_name IN VARCHAR2,
403 p_dynamic_sql IN VARCHAR2,
404 p_dynamic_sql_type IN VARCHAR2,
405 p_exception_value IN VARCHAR2,
406 p_impact_msg_name IN VARCHAR2,
407 p_add_info_URL IN VARCHAR2,
408 p_enabled_flag IN VARCHAR2,
409 p_foundation_HR_FLAG IN VARCHAR2,
410 p_null_impact_msg_name IN VARCHAR2,
411 p_functional_area_cd IN VARCHAR2) IS
412 --
413 BEGIN
414 --
415 -- Check for duplication
416 --
417 check_object_existence(p_object_name,'PROFILE');
418 --
419 -- Check if the profile exists
420 --
421 check_profile_existence(p_object_name);
422 --
423 -- Check if the functional area is valid
424 --
425 check_functional_area(p_functional_area_cd);
426 --
427 --
428 -- Check the messages have been created
429 --
430 check_message(p_impact_msg_name);
431 check_message(p_null_impact_msg_name);
432 --
433 -- Insert into the diagnostic meta data table
434 --
435 INSERT INTO hri_adm_dgnstc_setup
436 (object_name,
437 object_type,
438 dynamic_sql,
439 dynamic_sql_type,
440 exception_value,
441 impact_msg_name,
442 add_info_URL,
443 enabled_flag,
444 foundation_HR_FLAG,
445 null_impact_msg_name,
446 report_type,
447 functional_area_cd
448 )
449 VALUES
450 (p_object_name,
451 'PROFILE',
452 p_dynamic_sql,
453 p_dynamic_sql_type,
454 p_exception_value,
455 p_impact_msg_name,
456 p_add_info_URL,
457 p_enabled_flag,
458 p_foundation_HR_FLAG,
459 p_null_impact_msg_name,
460 'SYSTEM',
461 p_functional_area_cd);
462 --
463 COMMIT;
464 --
465 EXCEPTION
466 --
467 WHEN e_data_error THEN
468 RAISE;
469 WHEN OTHERS THEN
470 RAISE;
471 END create_profile_sys_setup;
472 --
473 PROCEDURE create_trigger_sys_setup(
474 p_object_name IN VARCHAR2,
475 p_exception_status_msg_cd IN VARCHAR2,
476 p_valid_status_msg_cd IN VARCHAR2,
477 p_enabled_flag IN VARCHAR2,
478 p_foundation_hr_flag IN VARCHAR2,
479 p_functional_area_cd IN VARCHAR2) IS
480
481 BEGIN
482 --
483 -- Check for duplication
484 --
485 check_object_existence(p_object_name,'TRIGGER');
486 --
487 -- Check if the trigger exists
488 --
489 check_trigger_existence(p_object_name);
490 --
491 -- Check if the functional area is valid
492 --
493 check_functional_area(p_functional_area_cd);
494 --
495 --
496 -- Check that the message are created
497 --
498 check_message(p_exception_status_msg_cd);
499 check_message(p_valid_status_msg_cd);
500 --
501 -- Insert into the diagnostic metadata table
502 --
503 INSERT INTO hri_adm_dgnstc_setup
504 (object_name,
505 object_type,
506 exception_status_msg_cd,
507 valid_status_msg_cd,
508 enabled_flag,
509 foundation_hr_flag,
510 report_type,
511 functional_area_cd)
512 VALUES
513 (p_object_name,
514 'TRIGGER',
515 p_exception_status_msg_cd,
516 p_valid_status_msg_cd,
517 p_enabled_flag,
518 p_foundation_hr_flag,
519 'SYSTEM',
520 p_functional_area_cd);
521 --
522 COMMIT;
523 --
524 EXCEPTION
525 --
526 WHEN OTHERS THEN
527 --
528 RAISE;
529 --
530 END create_trigger_sys_setup;
531
532
533 PROCEDURE create_table_sys_setup
534 (p_object_name IN VARCHAR2,
535 p_object_owner IN VARCHAR2,
536 p_exception_status_msg_cd IN VARCHAR2,
537 p_valid_status_msg_cd IN VARCHAR2,
538 p_enabled_flag IN VARCHAR2,
539 p_foundation_hr_flag IN VARCHAR2,
540 p_functional_area_cd IN VARCHAR2) IS
541
542 BEGIN
543 --
544 -- Check for duplication
545 --
546 check_object_existence(p_object_name,'TABLE');
547 --
548 -- Check if the table exists
549 --
550 check_table_existence(p_object_name, p_object_owner);
551 --
552 -- Check if the functional area is valid
553 --
554 check_functional_area(p_functional_area_cd);
555 --
556 --
557 -- Check that the messages have been created
558 --
559 check_message(p_exception_status_msg_cd);
560 check_message(p_valid_status_msg_cd);
561 --
562 --
563 -- Insert into the diagnostic metacata table
564 --
565 INSERT INTO hri_adm_dgnstc_setup
566 (object_name,
567 object_type,
568 exception_status_msg_cd,
569 valid_status_msg_cd,
570 enabled_flag,
571 foundation_hr_flag,
572 report_type,
573 functional_area_cd)
574 VALUES
575 (p_object_name,
576 'TABLE',
577 p_exception_status_msg_cd,
578 p_valid_status_msg_cd,
579 p_enabled_flag,
580 p_foundation_hr_flag,
581 'SYSTEM',
582 p_functional_area_cd);
583 --
584 COMMIT;
585 --
586 EXCEPTION
587 --
588 WHEN OTHERS THEN
589 --
590 RAISE;
591 --
592 END create_table_sys_setup;
593
594
595 PROCEDURE create_seeded_ff_sys_setup
596 (p_object_name IN VARCHAR2,
597 p_exception_status_msg_cd IN VARCHAR2,
598 p_valid_status_msg_cd IN VARCHAR2,
599 p_add_info_url IN VARCHAR2,
600 p_enabled_flag IN VARCHAR2,
601 p_foundation_hr_flag IN VARCHAR2,
602 p_functional_area_cd IN VARCHAR2) IS
603 --
604 BEGIN
605 --
606 -- Check for duplication
607 --
608 check_object_existence(p_object_name,'SEEDED_FAST_FORMULA');
609 --
610 -- Check if the seeded fast formulas exists
611 --
612 check_seeded_ff_existence(p_object_name);
613 --
614 -- Check if the functional area is valid
615 --
616 check_functional_area(p_functional_area_cd);
617 --
618 -- Check messaged have been created
619 --
620 check_message(p_exception_status_msg_cd);
621 check_message(p_valid_status_msg_cd);
622 --
623 -- Insert into the diagnostic metadata table
624 --
625 INSERT INTO hri_adm_dgnstc_setup
626 (object_name,
627 object_type,
628 exception_status_msg_cd,
629 valid_status_msg_cd,
630 add_info_url,
631 enabled_flag,
632 foundation_hr_flag,
633 report_type,
634 functional_area_cd)
635 VALUES
636 (p_object_name,
637 'SEEDED_FAST_FORMULA',
638 p_exception_status_msg_cd,
639 p_valid_status_msg_cd,
640 p_add_info_url,
641 p_enabled_flag,
642 p_foundation_hr_flag,
643 'SYSTEM',
644 p_functional_area_cd);
645 --
646 COMMIT;
647 --
648 EXCEPTION
649 --
650 WHEN OTHERS THEN
651 --
652 RAISE;
653 --
654 END create_seeded_ff_sys_setup;
655
656 PROCEDURE create_usr_def_ff_sys_setup
657 (p_object_name IN VARCHAR2,
658 p_dynamic_sql IN VARCHAR2,
659 p_dynamic_sql_type IN VARCHAR2,
660 p_exception_status_msg_cd IN VARCHAR2,
661 p_valid_status_msg_cd IN VARCHAR2,
662 p_impact_msg_name IN VARCHAR2,
663 p_add_info_url IN VARCHAR2,
664 p_enabled_flag IN VARCHAR2,
665 p_foundation_hr_flag IN VARCHAR2,
666 p_functional_area_cd IN VARCHAR2) IS
667
668 BEGIN
669 --
670 -- Check for duplication
671 --
672 check_object_existence(p_object_name,'USER_DEFN_FAST_FORMULA');
673 --
674 -- Check if the functional area is valid
675 --
676 check_functional_area(p_functional_area_cd);
677 --
678 -- Check that the messages have been cerated
679 --
680 check_message(p_exception_status_msg_cd);
681 check_message(p_valid_status_msg_cd);
682 check_message(p_impact_msg_name);
683 --
684 -- Insert the data into the diagnostic metadata table
685 --
686 INSERT INTO hri_adm_dgnstc_setup
687 (object_name,
688 object_type,
689 dynamic_sql,
690 dynamic_sql_type,
691 exception_status_msg_cd,
692 valid_status_msg_cd,
693 impact_msg_name,
694 add_info_url,
695 enabled_flag,
696 foundation_hr_flag,
697 report_type,
698 functional_area_cd)
699 VALUES
700 (p_object_name,
701 'USER_DEFN_FAST_FORMULA',
702 p_dynamic_sql,
703 p_dynamic_sql_type,
704 p_exception_status_msg_cd,
705 p_valid_status_msg_cd,
706 p_impact_msg_name,
707 p_add_info_url,
708 p_enabled_flag,
709 p_foundation_hr_flag,
710 'SYSTEM',
711 p_functional_area_cd);
712 --
713 COMMIT;
714 --
715 EXCEPTION
716 --
717 WHEN OTHERS THEN
718 --
719 RAISE;
720 --
721 END create_usr_def_ff_sys_setup;
722 --
723 -- This procedure is used to create diagnostics associated with buckets
724 --
725 PROCEDURE create_bucket_sys_setup
726 (p_object_name IN VARCHAR2,
727 p_dynamic_sql IN VARCHAR2,
728 p_dynamic_sql_type IN VARCHAR2,
729 p_exception_status_msg_cd IN VARCHAR2,
730 p_valid_status_msg_cd IN VARCHAR2,
731 p_impact_msg_name IN VARCHAR2,
732 p_enabled_flag IN VARCHAR2,
733 p_foundation_hr_flag IN VARCHAR2,
734 p_functional_area_cd IN VARCHAR2
735 )
736 IS
737 BEGIN
738 --
739 --
740 -- Check for duplication
741 --
742 check_object_existence(p_object_name,'BUCKET');
743 --
744 -- Check for existence of the bucket
745 --
746 check_bucket_existence(p_object_name);
747 --
748 -- Check if the functional area is valid
749 --
750 check_functional_area(p_functional_area_cd);
751 --
752 -- Check that the messages have been cerated
753 --
754 check_message(p_exception_status_msg_cd);
755 check_message(p_valid_status_msg_cd);
756 check_message(p_impact_msg_name);
757 --
758 -- Insert the data into the diagnostic metadata table
759 --
760 INSERT INTO hri_adm_dgnstc_setup
761 (object_name,
762 object_type,
763 dynamic_sql,
764 dynamic_sql_type,
765 exception_status_msg_cd,
766 valid_status_msg_cd,
767 impact_msg_name,
768 enabled_flag,
769 foundation_hr_flag,
770 report_type,
771 functional_area_cd)
772 VALUES
773 (p_object_name,
774 'BUCKET',
775 p_dynamic_sql,
776 p_dynamic_sql_type,
777 p_exception_status_msg_cd,
778 p_valid_status_msg_cd,
779 p_impact_msg_name,
780 p_enabled_flag,
781 p_foundation_hr_flag,
782 'SYSTEM',
783 p_functional_area_cd);
784 --
785 COMMIT;
786 --
787 EXCEPTION
788 --
789 WHEN OTHERS THEN
790 --
791 RAISE;
792 --
793 END create_bucket_sys_setup;
794
795 --
796 -- This procedure is used to create data diagnostics
797 --
798 PROCEDURE create_data_diagnostics
799 (p_object_name IN VARCHAR2,
800 p_object_type IN VARCHAR2,
801 p_dynamic_sql IN VARCHAR2,
802 p_dynamic_sql_type IN VARCHAR2,
803 p_impact_msg_name IN VARCHAR2,
804 p_enabled_flag IN VARCHAR2,
805 p_foundation_hr_flag IN VARCHAR2,
806 --
807 -- object_type_msg_name
808 -- Section heading
809 --
810 p_section_heading IN VARCHAR2,
811 --
812 -- object_type_desc
813 -- Description for COUNT section
814 --
815 p_section_count_desc IN VARCHAR2,
816 --
817 -- object_type_dtl_desc_msg_name
818 -- Description for DETAIL section
819 --
820 p_section_detail_desc IN VARCHAR2,
821 --
822 -- object_name_msg_name
823 -- Subsection heading
824 --
825 p_sub_section_heading IN VARCHAR2,
826 --
827 -- object_name_desc
828 -- Description for COUNT sub-section
829 --
830 p_sub_section_count_desc IN VARCHAR2,
831 --
832 -- object_name_dtl_desc_msg_name
833 -- Description for DETAIL sub section
834 --
835 p_sub_section_detail_desc IN VARCHAR2,
836 --
837 -- Heading of count column
838 --
839 p_heading_for_count IN VARCHAR2,
840 --
841 -- Heading of columns in detail mode as ordered in the dynamic SQL
842 --
843 p_heading_for_column1 IN VARCHAR2,
844 p_heading_for_column2 IN VARCHAR2,
845 p_heading_for_column3 IN VARCHAR2,
846 p_heading_for_column4 IN VARCHAR2,
847 p_heading_for_column5 IN VARCHAR2,
848 --
849 p_default_sql_mode IN VARCHAR2,
850 --
851 p_seq_num IN NUMBER,
852 p_functional_area_cd IN VARCHAR2
853 )
854 IS
855 --
856 l_prev_heading VARCHAR2(100);
857 --
858 CURSOR cur_prev_heading
859 IS
860 SELECT object_type_msg_name
861 FROM hri_adm_dgnstc_setup
862 WHERE seq_num = (
863 SELECT max(seq_num)
864 FROM hri_adm_dgnstc_setup
865 WHERE report_type = 'DATA'
866 AND seq_num < p_seq_num);
867
868
869
870 BEGIN
871 --
872 -- Check the nessages have been created
873 --
874 check_message(p_impact_msg_name);
875 check_message(p_section_heading);
876 check_message(p_section_count_desc);
877 check_message(p_section_detail_desc);
878 check_message(p_sub_section_heading);
879 check_message(p_sub_section_count_desc);
880 check_message(p_sub_section_detail_desc);
881 check_message(p_heading_for_count);
882 check_message(p_heading_for_column1);
883 check_message(p_heading_for_column2);
884 check_message(p_heading_for_column3);
885 check_message(p_heading_for_column4);
886 check_message(p_heading_for_column5);
887 --
888 -- Check if the functional area is valid
889 --
890 check_functional_area(p_functional_area_cd);
891 --
892 IF (p_sub_section_heading IS NOT NULL) THEN
893 --
894 OPEN cur_prev_heading;
895 FETCH cur_prev_heading INTO l_prev_heading;
896 CLOSE cur_prev_heading;
897 --
898 IF l_prev_heading <> p_section_heading THEN
899 --
900 output('Warning: The previous diagnostics does not have the same section heading');
901 output('This must be the first sub-section of many diagnostics belonging to this section');
902 --
903 END IF;
904 --
905 END IF;
906 --
907 INSERT INTO hri_adm_dgnstc_setup
908 (object_name,
909 object_type,
910 dynamic_sql,
911 dynamic_sql_type,
912 impact_msg_name,
913 enabled_flag,
914 foundation_hr_flag,
915 --
916 -- Section heading
917 --
918 object_type_msg_name,
919 --
920 -- Description for COUNT section
921 --
922 object_type_desc,
923 --
924 -- Description for DETAIL section
925 --
926 object_type_dtl_desc_msg_name,
927 --
928 -- Sub section heading
929 --
930 object_name_msg_name,
931 --
932 -- Description for COUNT sub section
933 --
934 object_name_desc,
935 --
936 -- Description for DETAIL sub section
937 --
938 object_name_dtl_desc_msg_name,
939 --
940 -- Heading for count column
941 --
942 count_heading,
943 --
944 -- Heading of columns in detail mode as ordered in the dynamic SQL
945 --
946 col_heading1,
947 col_heading2,
948 col_heading3,
949 col_heading4,
950 col_heading5,
951 --
952 default_mode,
953 seq_num,
954 report_type,
955 functional_area_cd
956 )
957 VALUES
958 (p_object_name,
959 p_object_type,
960 p_dynamic_sql,
961 p_dynamic_sql_type,
962 p_impact_msg_name,
963 p_enabled_flag,
964 p_foundation_hr_flag,
965 p_section_heading,
966 p_section_count_desc,
967 p_section_detail_desc,
968 p_sub_section_heading,
969 p_sub_section_count_desc,
970 p_sub_section_detail_desc,
971 p_heading_for_count,
972 p_heading_for_column1,
973 p_heading_for_column2,
974 p_heading_for_column3,
975 p_heading_for_column4,
976 p_heading_for_column5,
977 p_default_sql_mode,
978 p_seq_num,
979 'DATA',
980 p_functional_area_cd
981 );
982 --
983 COMMIT;
984 --
985 EXCEPTION
986 WHEN e_data_error THEN
987 --
988 RAISE;
989 --
990 WHEN OTHERS THEN
991 --
992 RAISE;
993 --
994 END create_data_diagnostics;
995 --
996 -- This procedure is used to delete an object from the diagnostics metadata table
997 --
998 PROCEDURE delete_object
999 (p_object_name IN VARCHAR2,
1000 p_object_type IN VARCHAR2,
1001 p_report_type IN VARCHAR2,
1002 p_functional_area_cd IN VARCHAR2)
1003 IS
1004 --
1005 BEGIN
1006 --
1007 DELETE FROM hri_adm_dgnstc_setup
1008 WHERE object_name = p_object_name
1009 AND object_type = p_object_type
1010 AND report_type = p_report_type
1011 AND functional_area_cd = p_functional_area_cd;
1012 --
1013 output(SQL%ROWCOUNT || ' row deleted.');
1014 --
1015 COMMIT;
1016 --
1017 EXCEPTION
1018 --
1019 WHEN OTHERS THEN
1020 --
1021 RAISE;
1022 --
1023 END delete_object;
1024 --
1025 END hri_apl_diagnostics;