1 PACKAGE BODY hri_bpl_setup_diagnostic AS
2 /* $Header: hribdgsp.pkb 120.9 2006/12/11 09:55:24 msinghai noship $ */
3
4 -- =========================================================================
5 --
6 -- OVERVIEW
7 -- --------
8 -- This package contains procedures to test the set up of DBI on the system.
9 -- Checks is performed for the following:
10 -- (a) Profiles
11 -- (b) Fast Formulas
12 -- (c) Triggers
13 -- (d) Key DBI Tables
14 -- (e) Job Family and Job Function
15 -- (f) Geography
16 --
17 -- DOCUMENT REFERENCE
18 -- ------------------
19 -- http://files.oraclecorp.com/content/AllPublic/SharedFolders/HRMS%20
20 -- Intelligence%20%28HRMSi%29%20-%20Documents-Public/Design%20Specifications
21 -- /hri_lld_dgn_system_stup.doc
22 --
23 -- =========================================================================
24
25
26 TYPE g_prd_type IS RECORD(
27 dbi_ind NUMBER(10),
28 obiee_ind NUMBER(10),
29 all_ind NUMBER(10));
30
31 TYPE g_prd_type_tab IS TABLE OF g_prd_type INDEX BY VARCHAR2(100);
32
33 g_prd_type_tab_v g_prd_type_tab;
34
35 g_object_name VARCHAR2(100);
36
37 TYPE g_flex_structure_rec_type IS RECORD
38 (structure_name VARCHAR2(240),
39 job_family_defined_msg VARCHAR2(240),
40 job_function_defined_msg VARCHAR2(240));
41
42 TYPE g_flex_structure_tab_type IS TABLE OF g_flex_structure_rec_type
43 INDEX BY VARCHAR2(80);
44
45 g_debugging BOOLEAN;
46 g_setup_rec NUMBER;
47 g_global_start_date DATE;
48 g_functional_area VARCHAR2(30);
49
50 -- Cursor to fetch records from the Diagnostics Table
51 CURSOR c_objects
52 (v_object_name VARCHAR2,
53 v_object_type VARCHAR2,
54 v_functional_area VARCHAR2) IS
55 SELECT
56 stp.*
57 FROM
58 hri_adm_dgnstc_setup stp
59 ,hri_adm_dgnstc_sbscrb sbs
60 WHERE stp.object_type= v_object_type
61 AND stp.object_name = sbs.object_name
62 AND stp.object_type = sbs.object_type
63 AND sbs.functional_area_cd = v_functional_area
64 AND (v_object_name IS NULL
65 OR stp.object_name = v_object_name)
66 AND stp.enabled_flag = 'Y'
67 AND ((stp.foundation_hr_flag = 'Y' AND
68 hri_bpl_system.is_full_hr_installed = 'N')
69 OR hri_bpl_system.is_full_hr_installed = 'Y')
70 UNION ALL
71 SELECT
72 stp.*
73 FROM
74 hri_adm_dgnstc_setup stp
75 WHERE stp.object_type= v_object_type
76 AND v_functional_area = 'ALL'
77 AND (v_object_name IS NULL
78 OR stp.object_name = v_object_name)
79 AND stp.enabled_flag = 'Y'
80 AND ((stp.foundation_hr_flag = 'Y' AND
81 hri_bpl_system.is_full_hr_installed = 'N')
82 OR hri_bpl_system.is_full_hr_installed = 'Y')
83 ORDER BY 1;
84
85 -- ----------------------------------------------------------------------------
86 -- Switches debugging messages on or off. Setting to on will
87 -- mean extra debugging information will be generated when the
88 -- process is run.
89 -- ----------------------------------------------------------------------------
90 PROCEDURE set_debugging(p_on IN BOOLEAN) IS
91
92 BEGIN
93
94 g_debugging := p_on;
95
96 END set_debugging;
97
98 -- ----------------------------------------------------------------------------
99 -- Procedure msg logs a message
100 -- ----------------------------------------------------------------------------
101 PROCEDURE output(p_text IN VARCHAR2) IS
102
103 BEGIN
104
105 hri_bpl_conc_log.output(p_text);
106
107 END output;
108
109 -- ----------------------------------------------------------------------------
110 -- Procedure dbg decides whether to log the passed in message
111 -- depending on whether debug mode is set.
112 -- ----------------------------------------------------------------------------
113 PROCEDURE dbg(p_text IN VARCHAR2) IS
114
115 BEGIN
116
117 IF g_debugging THEN
118 output(p_text);
119 END IF;
120
121 END dbg;
122
123 -- ----------------------------------------------------------------------------
124 -- PROCEDURE trim_msg removes blank spaces and enter characters from the string
125 -- ----------------------------------------------------------------------------
126 FUNCTION trim_msg(p_text IN VARCHAR2)
127 RETURN VARCHAR2 IS
128
129 l_text VARCHAR2(20000);
130
131 BEGIN
132
133 -- Remove blank spaces
134 l_text := TRIM(both ' ' FROM p_text);
135
136 -- Remove Enter characters
137 l_text := TRIM(both fnd_global.local_chr(10) FROM l_text);
138
139 RETURN l_text;
140
141 END trim_msg;
142
143 -- ----------------------------------------------------------------------------
144 -- Function GET_MESSAGE takes the message name and returns back the
145 -- message text
146 -- ----------------------------------------------------------------------------
147 FUNCTION get_message(p_message IN VARCHAR2)
148 RETURN VARCHAR2 IS
149
150 BEGIN
151 --
152 fnd_message.set_name('HRI', p_message);
153 IF is_token_exist(p_message,'PRODUCT_NAME')
154 THEN
155 fnd_message.set_token('PRODUCT_NAME'
156 ,get_product_name(g_object_name));
157 END IF;
158
159 RETURN trim_msg(fnd_message.get);
160 --
161 END get_message;
162
163 -------------------------------------------------------------------------------
164 --
165 -------------------------------------------------------------------------------
166 FUNCTION is_token_exist(p_message IN VARCHAR2,
167 p_token_name IN VARCHAR2)
168 RETURN BOOLEAN IS
169 --
170 l_exists BOOLEAN := FALSE;
171 --
172 BEGIN
173 --
174 IF instr(fnd_message.get_string('HRI',p_message),p_token_name,1) > 0 THEN
175 --
176 l_exists := TRUE ;
177 --
178 END IF;
179 --
180 RETURN l_exists;
181 --
182 END is_token_exist;
183
184 -------------------------------------------------------------------------------
185 -- Get the product name being called by the Concurrect program.
186 -------------------------------------------------------------------------------
187
188 FUNCTION get_product_name(p_object_name IN VARCHAR2)
189 RETURN VARCHAR2 IS
190
191 l_product_name varchar2(1000);
192
193 BEGIN
194 dbg('Entering get_product_name');
195
196 IF ((g_prd_type_tab_v(p_object_name).dbi_ind = 1
197 AND g_prd_type_tab_v(p_object_name).obiee_ind = 0
198 AND g_prd_type_tab_v(p_object_name).all_ind = 0)
199 OR ( p_object_name = 'HRI_IMPL_DBI')) THEN
200 --
201 l_product_name :=fnd_message.GET_STRING('HRI', 'HRI_407494_HRI_PRODUCT_NAME');
202 --
203 ELSIF ((g_prd_type_tab_v(p_object_name).dbi_ind = 0
204 AND g_prd_type_tab_v(p_object_name).obiee_ind = 1
205 AND g_prd_type_tab_v(p_object_name).all_ind = 0)
206 OR (p_object_name = 'HRI_IMPL_OBIEE')) THEN
207 --
208 l_product_name :=fnd_message.GET_STRING('HRI', 'HRI_407493_OBIEE_PRODUCT_NAME');
209 --
210 ELSIF g_prd_type_tab_v(p_object_name).all_ind = 1 then
211
212 l_product_name :=fnd_message.GET_STRING('HRI', 'HRI_407493_OBIEE_PRODUCT_NAME')||' & '||
213 fnd_message.GET_STRING('HRI', 'HRI_407494_HRI_PRODUCT_NAME');
214 ELSE
215 l_product_name :=fnd_message.GET_STRING('HRI', 'HRI_407493_OBIEE_PRODUCT_NAME')||'/'||
216 fnd_message.GET_STRING('HRI', 'HRI_407494_HRI_PRODUCT_NAME');
217 END IF;
218
219 dbg('Exiting get_product_name');
220 RETURN l_product_name;
221
222 END get_product_name;
223
224 -- ----------------------------------------------------------------------------
225 -- Function get_profile_message takes the message name and returns back the
226 -- message text for a profile
227 -- ----------------------------------------------------------------------------
228 FUNCTION get_profile_message(p_message IN VARCHAR2,
229 p_user_profile_name IN VARCHAR2)
230 RETURN VARCHAR2 IS
231
232 l_product_name VARCHAR2(1000);
233 BEGIN
234
235 fnd_message.set_name('HRI', p_message);
236 fnd_message.set_token('PROFILE_NAME',p_user_profile_name);
237
238 IF is_token_exist(p_message,'PRODUCT_NAME') THEN
239 l_product_name:= get_product_name(p_object_name => g_object_name);
240 fnd_message.set_token('PRODUCT_NAME',l_product_name);
241 END IF;
242
243 RETURN trim_msg(fnd_message.get);
244
245 END get_profile_message;
246
247
248 -- ----------------------------------------------------------------------------
249 -- Function get_ff_message takes the message name and returns back the
250 -- message text for a fast formula
251 -- ----------------------------------------------------------------------------
252 FUNCTION get_ff_message(p_message IN VARCHAR2,
253 p_ff_name IN VARCHAR2)
254 RETURN VARCHAR2 IS
255
256 BEGIN
257
258 fnd_message.set_name('HRI', p_message);
259
260 -- Set the Fast Formula name
261 fnd_message.set_token('FF_NAME',p_ff_name);
262
263 IF is_token_exist(p_message,'PRODUCT_NAME')
264 THEN
265 fnd_message.set_token('PRODUCT_NAME',get_product_name(p_object_name => g_object_name));
266 END IF;
267
268
269 RETURN trim_msg(fnd_message.get);
270
271 END get_ff_message;
272
273 -- ----------------------------------------------------------------------------
274 -- Function is_dbi_date_format_correct checks the string format of DBI Global
275 -- Start Date. If the format is correct, then the global variable for DBI
276 -- Global Start Date is set to the date given in the string otherwise it is
277 -- set to sysdate
278 -- ----------------------------------------------------------------------------
279 FUNCTION is_dbi_date_format_correct(p_date_value IN VARCHAR2)
280 RETURN VARCHAR2 IS
281
282 BEGIN
283
284 -- If the value is null return wrong format
285 IF p_date_value IS NULL THEN
286 RETURN 'N';
287 END IF;
288
289 -- Set the global start date
290 g_global_start_date := TRUNC(TO_DATE(p_date_value,'MM/DD/YYYY'));
291
292 RETURN 'Y';
293
294 EXCEPTION WHEN OTHERS THEN
295
296 RETURN 'N';
297
298 END is_dbi_date_format_correct;
299
300 -- ----------------------------------------------------------------------------
301 -- FUNCTION get_user_profile_name is used to fetch the user profile name of a
302 -- profile.
303 -- ----------------------------------------------------------------------------
304 FUNCTION get_user_profile_name(p_profile_name IN VARCHAR2)
305 RETURN VARCHAR2 IS
306
307 -- Cursor to fetch the user name of the profile
308 CURSOR c_user_profile_name IS
309 SELECT user_profile_option_name
310 FROM fnd_profile_options_vl
311 WHERE profile_option_name = p_profile_name;
312
313 l_user_profile_name VARCHAR2(1000);
314
315 BEGIN
316
317 OPEN c_user_profile_name;
318 FETCH c_user_profile_name INTO l_user_profile_name;
319 CLOSE c_user_profile_name;
320
321 RETURN l_user_profile_name;
322
323 END get_user_profile_name;
324
325 -- ----------------------------------------------------------------------------
326 -- Procedure check_profiles checks the values for the profiles defined in the
327 -- set up diagnostics table.
328 -- ----------------------------------------------------------------------------
329 PROCEDURE check_profile_option
330 (p_profile_name IN VARCHAR2,
331 p_functional_area IN VARCHAR2,
332 p_user_profile_name OUT NOCOPY VARCHAR2,
333 p_profile_value OUT NOCOPY VARCHAR2,
334 p_impact OUT NOCOPY BOOLEAN,
335 p_impact_msg OUT NOCOPY VARCHAR2,
336 p_doc_links_url OUT NOCOPY VARCHAR2) IS
337
338 l_value_code VARCHAR2(240);
339 l_dynamic_sql VARCHAR2(32000);
340
341 BEGIN
342 g_functional_area:= p_functional_area;
343 -- Loop through all the profiles in the set up table
344 FOR l_profile IN c_objects(p_profile_name, 'PROFILE', p_functional_area) LOOP
345
346 -- Get the user name of the profile
347 p_user_profile_name := get_user_profile_name(l_profile.object_name);
348 g_object_name := l_profile.object_name ;
349
350 -- Get the profile option value
351 l_value_code := fnd_profile.value(l_profile.object_name);
352
353 -- If the profile value is the exception value stored in the set up table,
354 -- store the impact message for this
355 IF (l_value_code = l_profile.exception_value) THEN
356
357 -- Set impact flag
358 p_impact := TRUE;
359
360 -- Store the specific impact message
361 p_impact_msg := get_profile_message
362 (p_message => l_profile.impact_msg_name,
363 p_user_profile_name => p_user_profile_name);
364
365 -- Add the URL if provided
366 IF (l_profile.add_info_url IS NOT NULL) THEN
367 p_doc_links_url := l_profile.add_info_url;
368 END IF;
369
370 -- Store the null impact message if
371 -- The value for the profile is not set
372 -- It is the DBI Global Start Date profile and
373 -- the profile value is in an incorrect format
374 ELSIF (l_value_code IS NULL OR
375 (l_profile.object_name = 'BIS_GLOBAL_START_DATE' AND
376 is_dbi_date_format_correct(l_value_code) = 'N')) THEN
377
378 -- Set impact flag
379 p_impact := TRUE;
380
381 -- Store the specific impact message
382 p_impact_msg := get_profile_message
383 (p_message => l_profile.null_impact_msg_name,
384 p_user_profile_name => p_user_profile_name);
385
386 -- Add the URL if provided
387 IF (l_profile.add_info_url IS NOT NULL) THEN
388 p_doc_links_url := l_profile.add_info_url;
389 END IF;
390
391 END IF;
392
393 -- If the dynamic SQL is available for finding the profile value then use it else
394 -- use the existing profile value
395 l_dynamic_sql := hri_bpl_data_setup_dgnstc.get_dynamic_sql
396 (p_dyn_sql_type => l_profile.dynamic_sql_type,
397 p_dyn_sql => l_profile.dynamic_sql);
398
399 -- Run the SQL if given
400 IF l_dynamic_sql IS NOT NULL AND l_value_code IS NOT NULL THEN
401 EXECUTE IMMEDIATE l_dynamic_sql INTO p_profile_value USING l_value_code;
402 ELSE
403 p_profile_value := l_value_code;
404 END IF;
405
406 END LOOP;
407
408 EXCEPTION WHEN OTHERS THEN
409
410 output('Exception Raised in check_profiles');
411 RAISE;
412
413 END check_profile_option;
414
415 -- ----------------------------------------------------------------------------
416 -- Procedure check_fast_formula checks the status of all the fast formulas
417 -- in the set up diagnostics table.
418 -- ----------------------------------------------------------------------------
419 PROCEDURE check_fast_formula
420 (p_ff_name IN VARCHAR2,
421 p_functional_area IN VARCHAR2,
422 p_type IN VARCHAR2,
423 p_formula_tab OUT NOCOPY fast_formula_tab_type,
424 p_impact_msg_tab OUT NOCOPY impact_msg_tab_type) IS
425
426 l_formula_tab fast_formula_tab_type;
427 l_impact_msg_tab impact_msg_tab_type;
428 l_status VARCHAR2(240);
429 l_bg_index PLS_INTEGER;
430 l_bg_name VARCHAR2(100);
431 l_bg_id NUMBER;
432 l_bg_without_ff BOOLEAN;
433 l_ff_name VARCHAR2(100);
434 l_dynamic_sql VARCHAR2(32000);
435
436 -- Reference type cursor is used when the Dynamic SQL fetches more than one records
437 TYPE ref_cursor_type IS REF CURSOR;
438 c_user_defn_records ref_cursor_type;
439
440 -- Cursor to check the existence of the seeded fast formulas as on sysdate
441 CURSOR c_seeded_ff_nm(p_obj_nm VARCHAR2) IS
442 SELECT 'FFP'||formula_id||'_'||TO_CHAR(effective_start_date,'DDMMYYYY') ff_name
443 FROM ff_formulas_f ff
444 WHERE ff.formula_name = p_obj_nm
445 AND ff.business_group_id IS NULL
446 AND trunc(SYSDATE) BETWEEN ff.effective_start_date AND ff.effective_end_date
447 ORDER BY ff_name;
448
449 -- Cursor to find the status of the fast formula
450 CURSOR c_status(p_ff_nm VARCHAR2) IS
451 SELECT CASE WHEN COUNT(DISTINCT status)=1 THEN
452 'Valid'
453 ELSE
454 'Invalid'
455 END status
456 FROM user_objects
457 WHERE object_name = p_ff_nm
458 AND object_type IN ('PACKAGE','PACKAGE BODY');
459
460 BEGIN
461
462 dbg('Checking ff');
463
464 -- Initialize variables
465 l_bg_index := 0;
466
467 -- Only process one type - SEEDED or USER
468 IF (p_type = 'SEEDED') THEN
469
470 -- Loop through the seeded fast formulas in the diagnostics table
471 FOR l_ff IN c_objects(p_ff_name,'SEEDED_FAST_FORMULA',p_functional_area) LOOP
472 g_object_name := l_ff.object_name ;
473
474 -- Find the current existing formulas
475 FOR l_seeded_ff_nm IN c_seeded_ff_nm(l_ff.object_name) LOOP
476
477 -- Increment the counter
478 l_bg_index := l_bg_index + 1;
479
480 -- Check the status of each formula
481 OPEN c_status(l_seeded_ff_nm.ff_name);
482 FETCH c_status INTO l_status;
483 CLOSE c_status;
484
485 -- Check if the fast formula is compiled
486 IF l_status = 'Invalid' THEN
487
488 -- Fetch the exception status message
489 l_formula_tab(l_bg_index).status :=
490 get_message(l_ff.exception_status_msg_cd);
491
492 -- Fetch the impact message
493 l_formula_tab(l_bg_index).impact_msg :=
494 get_ff_message('HRI_407170_FF_UCMP_IMPCT',l_ff.object_name);
495 ELSE
496
497 -- Store the valid status message
498 l_formula_tab(l_bg_index).status :=
499 get_message(l_ff.valid_status_msg_cd);
500 END IF;
501
502 END LOOP;
503
504 END LOOP;
505
506 -- USER formula
507 ELSE
508
509 -- Loop through the user defined fast formulas in the diagnostics table
510 FOR l_ff IN c_objects(p_ff_name,'USER_DEFN_FAST_FORMULA',p_functional_area) LOOP
511 g_object_name := l_ff.object_name ;
512
513 -- Fetch the information for all business groups for this formula
514 l_dynamic_sql := hri_bpl_data_setup_dgnstc.get_dynamic_sql
515 (p_dyn_sql_type => l_ff.dynamic_sql_type,
516 p_dyn_sql => l_ff.dynamic_sql);
517
518 -- Execute dynamic sql
519 OPEN c_user_defn_records
520 FOR l_dynamic_sql
521 USING l_ff.object_name;
522
523 -- Loop through all the business groups
524 LOOP
525
526 -- Fetch a record
527 FETCH c_user_defn_records
528 INTO l_bg_name, l_bg_id, l_ff_name;
529
530 -- Exit when no further records found
531 EXIT WHEN (c_user_defn_records%NOTFOUND OR
532 c_user_defn_records%NOTFOUND IS NULL);
533
534 -- Increment the business group count
535 l_bg_index := l_bg_index + 1;
536
537 -- Add record for the business group
538 l_formula_tab(l_bg_index).business_group_name := l_bg_name;
539
540 -- If the formula is not defined for the business group
541 IF l_ff_name = 'FFP_' THEN
542
543 l_formula_tab(l_bg_index).status :=
544 get_message(l_ff.null_impact_msg_name);
545 l_bg_without_ff := true;
546
547 -- Formula is defined for the business group
548 ELSE
549
550 -- Fetch the status of the user defined fast formula
551 OPEN c_status(l_ff_name);
552 FETCH c_status INTO l_status;
553 CLOSE c_status;
554
555 -- Check if the fast formula is compiled
556 IF l_status = 'Invalid' THEN
557
558 -- This fast formula has an invalid status
559 l_formula_tab(l_bg_index).status :=
560 get_message(l_ff.exception_status_msg_cd);
561
562 l_formula_tab(l_bg_index).impact_msg :=
563 get_ff_message('HRI_407170_FF_UCMP_IMPCT',
564 l_ff.object_name);
565
566 ELSIF l_status = 'Valid' THEN
567
568 -- This fast formula has an invalid status
569 l_formula_tab(l_bg_index).status :=
570 get_message(l_ff.valid_status_msg_cd);
571
572 -- If the formula is not defined in Setup Business Group and should be
573 -- record this information
574 IF (l_bg_id <> 0 AND
575 (l_ff.object_name = 'HR_MOVE_TYPE' OR
576 l_ff.object_name = 'NORMALIZE_REVIEW_RATING' OR
577 l_ff.object_name = 'CATEGORIZE_PERSON_TYPE')) THEN
578
579 -- This bit needs "genericising"
580 IF (l_ff.object_name = 'HR_MOVE_TYPE') THEN
581
582 l_impact_msg_tab('HRI_407168_USR_LVRSN_IMPCT').impact_msg :=
583 get_message('HRI_407168_USR_LVRSN_IMPCT');
584
585 ELSIF (l_ff.object_name = 'NORMALIZE_REVIEW_RATING') THEN
586
587 l_impact_msg_tab('HRI_407267_REVRTG_WRNGBG_IMPCT').impact_msg :=
588 get_message('HRI_407267_REVRTG_WRNGBG_IMPCT');
589
590 ELSIF (l_ff.object_name = 'CATEGORIZE_PERSON_TYPE') THEN
591
592 l_impact_msg_tab('HRI_407284_CTRPRN_WRNGBG_IMPCT').impact_msg :=
593 get_message('HRI_407284_CTRPRN_WRNGBG_IMPCT');
594 END IF;
595
596 END IF;
597
598 END IF; -- Compile Status
599
600 END IF; -- Formula exists for BG
601
602 END LOOP; -- BG Loop
603
604 -- If a business group is found without a formula then
605 IF l_bg_without_ff THEN
606
607 -- Store impact message
608 l_impact_msg_tab('HRI_407269_APPRTG_UNDEF_IMPCT').impact_msg :=
609 get_message('HRI_407269_APPRTG_UNDEF_IMPCT');
610
611 -- If the number of BG are 0 then store a message
612 ELSIF (l_bg_index = 0 AND
613 l_ff.object_name <> 'NORMALIZE_APPRAISAL_RATING' AND
614 l_ff.impact_msg_name IS NOT NULL) THEN
615
616 l_impact_msg_tab(l_ff.impact_msg_name).impact_msg :=
617 get_message(l_ff.impact_msg_name);
618 l_impact_msg_tab(l_ff.impact_msg_name).doc_links_url :=
619 l_ff.add_info_url;
620
621 END IF;
622
623 END LOOP;
624
625 END IF;
626
627 -- Assign tables to output
628 p_formula_tab := l_formula_tab;
629 p_impact_msg_tab := l_impact_msg_tab;
630
631 EXCEPTION WHEN OTHERS THEN
632
633 output('Exception Raised in check_fast_formula');
634 RAISE;
635
636 END check_fast_formula;
637
638 -- ----------------------------------------------------------------------------
639 -- PROCEDURE check_triggers checks whether the triggers are generated and
640 -- enabled
641 -- ----------------------------------------------------------------------------
642 PROCEDURE check_triggers(p_trigger_name IN VARCHAR2,
643 p_functional_area IN VARCHAR2,
644 p_generated OUT NOCOPY VARCHAR2,
645 p_enabled OUT NOCOPY VARCHAR2,
646 p_status OUT NOCOPY VARCHAR2,
647 p_impact OUT NOCOPY BOOLEAN,
648 p_impact_msg OUT NOCOPY VARCHAR2,
649 p_doc_links_url OUT NOCOPY VARCHAR2) IS
650
651 -- Cursor to check if the trigger is enabled and generated
652 CURSOR c_check_trigger(p_trigger_nm VARCHAR2) IS
653 SELECT generated_flag generated,
654 enabled_flag enabled
655 FROM pay_trigger_events
656 WHERE short_name = p_trigger_nm;
657
658 l_generated VARCHAR2(5);
659 l_enabled VARCHAR2(5);
660
661 BEGIN
662
663 dbg('Checking trigger');
664
665 -- Loop through all the triggers in the set up table
666 FOR l_trigger IN c_objects(p_trigger_name,'TRIGGER',p_functional_area) LOOP
667 g_object_name := l_trigger.object_name;
668
669 -- Fetch the values for the generated and enabled flag
670 OPEN c_check_trigger(l_trigger.object_name);
671 FETCH c_check_trigger INTO l_generated, l_enabled;
672 CLOSE c_check_trigger;
673
674 -- Set the messages
675 IF (l_generated = 'Y' AND l_enabled = 'Y') THEN
676
677 -- If the trigger is generated and enabled then there is no problem
678 p_status := get_message(l_trigger.valid_status_msg_cd);
679 p_impact := FALSE;
680
681 ELSE
682
683 -- Otherwise exception message has to be shown
684 p_status := get_message(l_trigger.exception_status_msg_cd);
685 p_impact := TRUE;
686
687 -- To show impact message since a trigger is not enabled or not generated.
688 p_impact_msg := get_message('HRI_407171_TRGGR_IMPCT');
689 p_doc_links_url := get_message('HRI_407182_TRGGR_LINK');
690
691 END IF;
692
693 -- Find the lookup value for generated and enabled
694 p_generated := hr_bis.bis_decode_lookup('YES_NO',l_generated);
695 p_enabled := hr_bis.bis_decode_lookup('YES_NO',l_enabled);
696
697 END LOOP;
698
699 EXCEPTION WHEN OTHERS THEN
700
701 output('Exception Raised in check_triggers');
702 RAISE;
703
704 END check_triggers;
705
706 -- ----------------------------------------------------------------------------
707 -- PROCEDURE check_dbi_tables to check if the key DBI tables are populated
708 -- ----------------------------------------------------------------------------
709 PROCEDURE check_dbi_tables(p_table_name IN VARCHAR2,
710 p_functional_area IN VARCHAR2,
711 p_status OUT NOCOPY VARCHAR2,
712 p_impact OUT NOCOPY BOOLEAN,
713 p_impact_msg OUT NOCOPY VARCHAR2,
714 p_doc_links_url OUT NOCOPY VARCHAR2) IS
715
716 l_records NUMBER;
717 l_stmt VARCHAR2(100);
718
719 BEGIN
720
721 dbg('Checking tables');
722
723 -- Get the table information
724 FOR l_tables IN c_objects(p_table_name,'TABLE',p_functional_area) LOOP
725 g_object_name := l_tables.object_name;
726
727 -- Check whether data is present in the table
728 l_stmt := 'SELECT COUNT(*) FROM ' || UPPER(l_tables.object_name) ||
729 ' WHERE ROWNUM = 1';
730 EXECUTE IMMEDIATE l_stmt INTO l_records;
731
732 -- If there is no data in the table
733 IF (l_records = 0) THEN
734
735 -- Store the exception staus since the table is empty
736 p_impact := TRUE;
737 p_status := get_message(l_tables.exception_status_msg_cd);
738 p_impact_msg := get_message('HRI_407184_TABLE_IMPCT');
739 p_doc_links_url := get_message('HRI_407182_TRGGR_LINK');
740
741 -- Table is populated
742 ELSE
743
744 -- No impact
745 p_impact := FALSE;
746 p_status := get_message(l_tables.valid_status_msg_cd);
747
748 END IF;
749
750 END LOOP;
751
752 EXCEPTION WHEN OTHERS THEN
753
754 output('Exception Raised in check_dbi_tables');
755 RAISE;
756
757 END check_dbi_tables;
758
759 -- ------------------------------------
760 -- Checks details for a given job group
761 -- ------------------------------------
762 PROCEDURE check_job_group
763 (p_value_set_id IN NUMBER,
764 p_job_type IN VARCHAR2,
765 p_flex_structure_tab IN OUT NOCOPY job_flex_tab_type,
766 p_flex_type OUT NOCOPY VARCHAR2) IS
767
768 -- Cursor to find all the job keyflex structure codes that are
769 -- linked to a business group but do not have a segment linked
770 -- with the given value set
771 CURSOR check_keyflex_csr(v_valueset_id NUMBER) IS
772 SELECT DISTINCT
773 flx.id_flex_num
774 ,flx.id_flex_structure_code
775 ,flx.id_flex_structure_name
776 FROM
777 hr_organization_information hoi
778 ,fnd_id_flex_structures_vl flx
779 WHERE hoi.org_information_context = 'Business Group Information'
780 AND flx.id_flex_num = hoi.org_information6
781 AND flx.id_flex_code= 'JOB'
782 AND flx.application_id = 800
783 AND NOT EXISTS
784 (SELECT /*+ NO_UNNEST */ null
785 FROM fnd_id_flex_segments_vl seg
786 WHERE seg.id_flex_code = 'JOB'
787 AND seg.application_id = 800
788 AND seg.id_flex_num = hoi.org_information6
789 AND seg.flex_value_set_id = v_valueset_id)
790 AND EXISTS
791 (SELECT /*+ NO_UNNEST */ null
792 FROM per_periods_of_service pps
793 WHERE pps.business_group_id = hoi.organization_id
794 AND NVL(pps.actual_termination_date, g_global_start_date) >= g_global_start_date);
795
796 -- Cursor to find all non global job descriptive flexfield contexts that
797 -- do not have a segment linked with the given value set and do not have
798 -- a global segment available
799 CURSOR check_dscflex_csr(v_valueset_id NUMBER) IS
800 SELECT
801 ctxt.descriptive_flex_context_code
802 ,ctxt.descriptive_flex_context_name
803 FROM
804 fnd_descr_flex_contexts_vl ctxt
805 WHERE ctxt.descriptive_flexfield_name = 'PER_JOBS'
806 AND ctxt.application_id = 800
807 AND ctxt.enabled_flag = 'Y'
808 AND ctxt.global_flag = 'N'
809 AND NOT EXISTS
810 (SELECT NULL
811 FROM
812 fnd_descr_flex_col_usage_vl col
813 WHERE ctxt.application_id = col.application_id
814 AND ctxt.descriptive_flexfield_name = col.descriptive_flexfield_name
815 AND ctxt.descriptive_flex_context_code = col.descriptive_flex_context_code
816 AND col.flex_value_set_id = v_valueset_id)
817 AND NOT EXISTS
818 (SELECT NULL
819 FROM
820 fnd_descr_flex_contexts_vl ctxt2
821 ,fnd_descr_flex_col_usage_vl col2
822 WHERE ctxt2.application_id = ctxt.application_id
823 AND ctxt2.descriptive_flexfield_name = ctxt.descriptive_flexfield_name
824 AND ctxt2.application_id = col2.application_id
825 AND ctxt2.descriptive_flexfield_name = col2.descriptive_flexfield_name
826 AND ctxt2.descriptive_flex_context_code = col2.descriptive_flex_context_code
827 AND ctxt2.global_flag = 'Y'
828 AND col2.flex_value_set_id = v_valueset_id);
829
830 l_index VARCHAR2(240);
831
832 BEGIN
833
834 -- Get the flexfield type
835 p_flex_type := hri_opl_jobh.get_flexfield_type
836 (p_job_type => p_job_type,
837 p_value_set_id => p_value_set_id);
838
839 -- Load structure table with the undefined structures from the relevant cursor
840 IF (p_flex_type = 'KEY') THEN
841
842 FOR keyflex_rec IN check_keyflex_csr(p_value_set_id) LOOP
843 -- g_object_name := l_tables.object_name;
844
845 l_index := p_flex_type || '|' || keyflex_rec.id_flex_structure_code;
846
847 p_flex_structure_tab(l_index).structure_name :=
848 keyflex_rec.id_flex_structure_name;
849
850 IF (p_job_type = 'JOB_FAMILY') THEN
851 p_flex_structure_tab(l_index).job_family_defined_msg :=
852 get_message('HRI_407177_JOB_UNDEF_STTS');
853 ELSIF (p_job_type = 'JOB_FUNCTION') THEN
854 p_flex_structure_tab(l_index).job_function_defined_msg :=
855 get_message('HRI_407177_JOB_UNDEF_STTS');
856 END IF;
857
858 END LOOP;
859
860 ELSIF (p_flex_type = 'DESCRIPTIVE') THEN
861
862 FOR dscflex_rec IN check_dscflex_csr(p_value_set_id) LOOP
863
864 l_index := p_flex_type || '|' || dscflex_rec.descriptive_flex_context_code;
865
866 p_flex_structure_tab(l_index).structure_name :=
867 dscflex_rec.descriptive_flex_context_name;
868
869 IF (p_job_type = 'JOB_FAMILY') THEN
870 p_flex_structure_tab(l_index).job_family_defined_msg :=
871 get_message('HRI_407177_JOB_UNDEF_STTS');
872 ELSIF (p_job_type = 'JOB_FUNCTION') THEN
873 p_flex_structure_tab(l_index).job_function_defined_msg :=
874 get_message('HRI_407177_JOB_UNDEF_STTS');
875 END IF;
876
877 END LOOP;
878
879 END IF;
880
881 END check_job_group;
882
883 -- ----------------------------------------------------------------------------
884 -- PROCEDURE check_job checks the set up for Job Family and Job Function.
885 -- ----------------------------------------------------------------------------
886 PROCEDURE check_job(p_job_family_mode OUT NOCOPY VARCHAR2,
887 p_job_function_mode OUT NOCOPY VARCHAR2,
888 p_flex_structure_tab OUT NOCOPY job_flex_tab_type,
889 p_impact OUT NOCOPY BOOLEAN,
890 p_impact_msg OUT NOCOPY VARCHAR2,
891 p_doc_links_url OUT NOCOPY VARCHAR2) IS
892
893 l_family_vl VARCHAR2(100);
894 l_function_vl VARCHAR2(100);
895 l_job_family_impact BOOLEAN;
896 l_job_function_impact BOOLEAN;
897
898 BEGIN
899
900 -- Do not check job set up for foundation HR
901 IF hri_bpl_system.is_full_hr_installed = 'Y' THEN
902
903 -- If the global start date, job family or job function profile is not set correctly
904 -- then store an impact message and return
905 IF (fnd_profile.value('HR_BIS_JOB_FAMILY') IS NULL AND
906 fnd_profile.value('HR_BIS_JOB_FUNCTION') IS NULL) THEN
907
908 -- Set the message up
909 fnd_message.set_name
910 ('HRI','HRI_407183_UNSET_JOB_PRF_IMPCT');
911
912 -- Set the Profile names
913 fnd_message.set_token
914 ('PROFILE_NAME1',get_user_profile_name('BIS_GLOBAL_START_DATE'));
915 fnd_message.set_token
916 ('PROFILE_NAME2',get_user_profile_name('HR_BIS_JOB_FAMILY'));
917 fnd_message.set_token
918 ('PROFILE_NAME3',get_user_profile_name('HR_BIS_JOB_FUNCTION'));
919
920 -- Store the general impact message
921 p_impact := TRUE;
922 p_impact_msg := trim_msg(fnd_message.get);
923
924 ELSE
925
926 -- Find the profile value for Job Family and Job Function
927 l_family_vl := fnd_profile.value('HR_BIS_JOB_FAMILY');
928 l_function_vl := fnd_profile.value('HR_BIS_JOB_FUNCTION');
929
930 -- Get the setup information for job family
931 check_job_group(p_value_set_id => l_family_vl,
932 p_job_type => 'JOB_FAMILY',
933 p_flex_structure_tab => p_flex_structure_tab,
934 p_flex_type => p_job_family_mode);
935
936 -- Get the setup information for job function
937 check_job_group(p_value_set_id => l_function_vl,
938 p_job_type => 'JOB_FUNCTION',
939 p_flex_structure_tab => p_flex_structure_tab,
940 p_flex_type => p_job_function_mode);
941
942 -- If any structures are returned in the table then there may be impact
943 BEGIN
944 IF (p_flex_structure_tab.FIRST IS NOT NULL) THEN
945
946 -- Store the general impact message
947 p_impact := TRUE;
948 p_impact_msg := get_message('HRI_407172_JOB_IMPCT');
949 p_doc_links_url := get_message('HRI_407181_JOB_LINK');
950
951 ELSE
952
953 -- No impact
954 p_impact := FALSE;
955
956 END IF;
957
958 -- Trap exceptions if the structure table is empty
959 EXCEPTION WHEN OTHERS THEN
960 null;
961 END;
962
963 END IF; -- Profile setup correct
964
965 END IF; -- Shared HR
966
967 EXCEPTION WHEN OTHERS THEN
968
969 output('Exception Raised in check_job');
970 RAISE;
971
972 END check_job;
973
974 -- ----------------------------------------------------------------------------
975 -- PROCEDURE check_geography checks if the set up for Region. It
976 -- checks all the structures in Additional Location Details for the Region
977 -- segment.
978 -- ----------------------------------------------------------------------------
979 PROCEDURE check_geography(p_context_name OUT NOCOPY VARCHAR2,
980 p_flex_column OUT NOCOPY VARCHAR2,
981 p_status OUT NOCOPY VARCHAR2,
982 p_impact OUT NOCOPY BOOLEAN,
983 p_impact_msg OUT NOCOPY VARCHAR2) IS
984
985 -- Cursor to find the attribute for Region
986 CURSOR c_reg_attr IS
987 SELECT bfm.application_column_name
988 FROM
989 bis_flex_mappings_v bfm
990 ,bis_dimensions bd
991 WHERE bfm.dimension_id = bd.dimension_id
992 AND bd.short_name = 'GEOGRAPHY'
993 AND bfm.level_short_name = 'REGION'
994 AND bfm.application_id = 800;
995
996 -- Cursor which finds the structures in the descriptive flexfield
997 -- 'Additional Location Details' that have a segment that uses
998 -- an attribute defined for Region
999 CURSOR c_reg_structures(p_attribute VARCHAR2) IS
1000 SELECT
1001 cntxt.descriptive_flex_context_name
1002 FROM
1003 fnd_descr_flex_col_usage_vl col
1004 ,fnd_descr_flex_contexts_vl cntxt
1005 WHERE col.application_id = 800
1006 AND col.descriptive_flexfield_name = 'HR_LOCATIONS'
1007 AND application_column_name = p_attribute
1008 AND cntxt.descriptive_flexfield_name = col.descriptive_flexfield_name
1009 AND cntxt.descriptive_flex_context_code = col.descriptive_flex_context_code
1010 AND col.application_id = cntxt.application_id;
1011
1012 BEGIN
1013
1014 -- Do not check geography set up for foundation HR
1015 IF hri_bpl_system.is_full_hr_installed = 'Y' THEN
1016
1017 -- Get the column the region flex field segment is mapped to
1018 OPEN c_reg_attr;
1019 FETCH c_reg_attr INTO p_flex_column;
1020 CLOSE c_reg_attr;
1021
1022 -- If the region segment attribute is not null then find the
1023 -- flexfield structures using the region atribute
1024 IF p_flex_column IS NOT NULL THEN
1025
1026 -- Find whether the location flexfield has the region column
1027 OPEN c_reg_structures(p_flex_column);
1028 FETCH c_reg_structures INTO p_context_name;
1029 CLOSE c_reg_structures;
1030
1031 END IF;
1032
1033 -- If the location structure has no region attribute then set status
1034 -- to undefined
1035 IF (p_context_name IS NULL) THEN
1036
1037 p_impact := TRUE;
1038 p_status := get_message('HRI_407179_GEO_UNDEF_STTS');
1039 p_impact_msg := get_message('HRI_407173_GEO_IMPCT');
1040
1041 -- Otherwise the geography setup is fine
1042 ELSE
1043
1044 p_impact := FALSE;
1045 p_status := get_message('HRI_407178_GEO_DEF_STTS');
1046
1047 END IF;
1048
1049 END IF;
1050
1051 EXCEPTION WHEN OTHERS THEN
1052
1053 output('Exception Raised in check_geography');
1054 RAISE;
1055
1056 END check_geography;
1057
1058 -- ----------------------------------------------------------------------------
1059 -- PROCEDURE check_buckets to check if the number of ranges in the HRI buckets
1060 -- are correct.
1061 -- ----------------------------------------------------------------------------
1062 PROCEDURE check_buckets(p_bucket_name IN VARCHAR2,
1063 p_functional_area IN VARCHAR2,
1064 p_user_bucket_name OUT NOCOPY VARCHAR2,
1065 p_status OUT NOCOPY VARCHAR2,
1066 p_impact OUT NOCOPY BOOLEAN,
1067 p_impact_msg OUT NOCOPY VARCHAR2,
1068 p_doc_links_url OUT NOCOPY VARCHAR2) IS
1069
1070 l_range VARCHAR2(1);
1071 l_bucket_name VARCHAR2(240);
1072 l_dynamic_sql VARCHAR2(32000);
1073
1074 CURSOR bucket_name_csr(v_bucket_name VARCHAR2) IS
1075 SELECT name
1076 FROM bis_bucket_vl
1077 WHERE short_name = v_bucket_name;
1078
1079 BEGIN
1080
1081 dbg('Checking buckets');
1082
1083 -- Get the bucket metadata
1084 FOR l_buckets IN c_objects(p_bucket_name, 'BUCKET', p_functional_area) LOOP
1085
1086 -- Execute the dynamic sql to get the ranges defined for the bucket
1087 l_dynamic_sql := hri_bpl_data_setup_dgnstc.get_dynamic_sql
1088 (p_dyn_sql_type => l_buckets.dynamic_sql_type,
1089 p_dyn_sql => l_buckets.dynamic_sql);
1090 EXECUTE IMMEDIATE l_dynamic_sql
1091 INTO l_range;
1092
1093 -- If the bucket is invalid
1094 IF l_range = 'N' THEN
1095
1096 -- Store the impact message that is to be displayed since the bucket
1097 -- does not have correct number of ranges
1098 p_impact := TRUE;
1099 p_impact_msg := get_message(l_buckets.impact_msg_name);
1100 p_doc_links_url := l_buckets.add_info_url;
1101 p_status := get_message(l_buckets.exception_status_msg_cd);
1102
1103 ELSE
1104
1105 -- No impact
1106 p_impact := FALSE;
1107 p_status := get_message(l_buckets.valid_status_msg_cd);
1108
1109 END IF;
1110
1111 -- Get the bucket name
1112 OPEN bucket_name_csr(l_buckets.object_name);
1113 FETCH bucket_name_csr INTO p_user_bucket_name;
1114 CLOSE bucket_name_csr;
1115
1116 -- Use bucket code if the name is not found
1117 IF (p_user_bucket_name IS NULL) THEN
1118 p_user_bucket_name := p_bucket_name;
1119 END IF;
1120
1121 END LOOP;
1122
1123 EXCEPTION WHEN OTHERS THEN
1124
1125 output('Exception Raised in check_buckets');
1126 RAISE;
1127
1128 END check_buckets;
1129
1130 -- ----------------------------------------------------------------------------
1131 -- PROCEDURE pplt_obj_farea_tab is populate PRODUCT NAME(DBI/OBIEE/ALL)
1132 -- flags for all the objects based on functional area.
1133 -- ----------------------------------------------------------------------------
1134 PROCEDURE pplt_obj_farea_tab IS
1135
1136 l_obiee_flag VARCHAR2(10) := 'N';
1137 l_dbi_flag VARCHAR2(10) := 'N';
1138 l_all_flag VARCHAR2(10) := 'N';
1139
1140 -- Cursor to load product name table
1141 CURSOR prd_name_csr IS
1142 SELECT sub.object_name, sub.functional_area_cd
1143 FROM hri_adm_dgnstc_sbscrb sub
1144 ORDER BY sub.object_name;
1145
1146 l_object_name VARCHAR2(100);
1147 l_functional_area_cd VARCHAR2(100);
1148 l_object_name_prv VARCHAR2(100);
1149
1150 BEGIN
1151
1152 OPEN prd_name_csr;
1153 LOOP
1154
1155 FETCH prd_name_csr
1156 INTO l_object_name,l_functional_area_cd;
1157
1158 EXIT WHEN prd_name_csr % NOTFOUND;
1159
1160 IF NOT(g_prd_type_tab_v.EXISTS(l_object_name)) THEN
1161 --
1162 g_prd_type_tab_v(l_object_name).obiee_ind := 0;
1163 g_prd_type_tab_v(l_object_name).dbi_ind := 0;
1164 g_prd_type_tab_v(l_object_name).all_ind := 0;
1165 --
1166 END IF;
1167
1168 IF(l_functional_area_cd LIKE 'OBIEE%') THEN
1169 --
1170 g_prd_type_tab_v(l_object_name).obiee_ind := 1;
1171 --
1172 END IF;
1173
1174 IF(l_functional_area_cd IN('BENEFITS', 'WRKFC_BDGT_MNGMNT') OR(l_functional_area_cd LIKE 'PPL%')) THEN
1175 --
1176 g_prd_type_tab_v(l_object_name).dbi_ind := 1;
1177 --
1178 END IF;
1179
1180 IF(g_prd_type_tab_v(l_object_name).obiee_ind = 1)
1181 AND(g_prd_type_tab_v(l_object_name).dbi_ind = 1) THEN
1182 --
1183 g_prd_type_tab_v(l_object_name).all_ind := 1;
1184 --
1185 END IF;
1186 --
1187 END LOOP;
1188 CLOSE prd_name_csr;
1189 END pplt_obj_farea_tab;
1190 --
1191 --
1192
1193 END hri_bpl_setup_diagnostic;
[Home] [Help]