1 PACKAGE BODY hr_mx_utility AS
2 /* $Header: hrmxutil.pkb 120.12.12020000.2 2012/09/05 08:49:11 rmugloo ship $ */
3
4 g_debug BOOLEAN;
5
6 TYPE wrip_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7
8 gt_wrip wrip_tab;
9
10 TYPE tax_subsidy_percent_tab IS TABLE OF NUMBER
11 INDEX BY BINARY_INTEGER;
12
13 gt_tax_subsidy_percent tax_subsidy_percent_tab;
14
15 FUNCTION per_mx_full_name(
16 p_first_name IN VARCHAR2
17 ,p_middle_names IN VARCHAR2
18 ,p_last_name IN VARCHAR2
19 ,p_known_as IN VARCHAR2
20 ,p_title IN VARCHAR2
21 ,p_suffix IN VARCHAR2
22 ,p_pre_name_adjunct IN VARCHAR2
23 ,p_per_information1 IN VARCHAR2
24 ,p_per_information2 IN VARCHAR2
25 ,p_per_information3 IN VARCHAR2
26 ,p_per_information4 IN VARCHAR2
27 ,p_per_information5 IN VARCHAR2
28 ,p_per_information6 IN VARCHAR2
29 ,p_per_information7 IN VARCHAR2
30 ,p_per_information8 IN VARCHAR2
31 ,p_per_information9 IN VARCHAR2
32 ,p_per_information10 IN VARCHAR2
33 ,p_per_information11 IN VARCHAR2
34 ,p_per_information12 IN VARCHAR2
35 ,p_per_information13 IN VARCHAR2
36 ,p_per_information14 IN VARCHAR2
37 ,p_per_information15 IN VARCHAR2
38 ,p_per_information16 IN VARCHAR2
39 ,p_per_information17 IN VARCHAR2
40 ,p_per_information18 IN VARCHAR2
41 ,p_per_information19 IN VARCHAR2
42 ,p_per_information20 IN VARCHAR2
43 ,p_per_information21 IN VARCHAR2
44 ,p_per_information22 IN VARCHAR2
45 ,p_per_information23 IN VARCHAR2
46 ,p_per_information24 IN VARCHAR2
47 ,p_per_information25 IN VARCHAR2
48 ,p_per_information26 IN VARCHAR2
49 ,p_per_information27 IN VARCHAR2
50 ,p_per_information28 IN VARCHAR2
51 ,p_per_information29 IN VARCHAR2
52 ,p_per_information30 IN VARCHAR2
53 ) RETURN VARCHAR2 IS
54 --
55 l_full_name VARCHAR2(240);
56 --
57 BEGIN
58 -------------------------------------------------------------------------
59 -- The Full Name format is:
60 -- "<Father's Last Name> <Mother's Last Name> <First Name> <Second Name>"
61 -------------------------------------------------------------------------
62 SELECT SUBSTR(LTRIM(RTRIM(
63 DECODE(p_last_name, NULL, '', ' ' || p_last_name)
64 ||DECODE(p_per_information1, NULL,'',' ' || p_per_information1)
65 ||DECODE(p_first_name,NULL, '', ' ' || p_first_name)
66 ||DECODE(p_middle_names,NULL, '', ' ' || p_middle_names)
67 )), 1, 240)
68 INTO l_full_name
69 FROM dual;
70
71 RETURN(l_full_name);
72 --
73 END per_mx_full_name;
74
75
76 --------------------------------------------------------------------------
77 -- --
78 -- Name : get_GRE_from_location --
79 -- Type : Function --
80 -- Access : Public --
81 -- Description : This function is used to fetch the GRE for the --
82 -- given location and BG from the Mexico specific --
83 -- Generic Hierarchy Structure. --
84 -- --
85 -- If the Location is part of more than 1 GRE, then --
86 -- p_is_ambiguous flag is set to TRUE. --
87 -- --
88 -- If the Location is missing from the Generic --
89 -- Hierarchy version which is active on p_session_date,--
90 -- then p_missing_gre is set to TRUE. --
91 -- --
92 -- Parameters : --
93 -- IN : p_location_id NUMBER --
94 -- p_business_group_id NUMBER --
95 -- p_session_date DATE --
96 -- OUT : p_is_ambiguous BOOLEAN --
97 -- p_missing_gre BOOLEAN --
98 -- RETURN : NUMBER --
99 -- --
100 ---------------------------------------------------------------------------
101
102 FUNCTION get_GRE_from_location(
103 p_location_id IN NUMBER,
104 p_business_group_id IN NUMBER, -- Bug 4129001
105 p_session_date IN DATE,
106 p_is_ambiguous OUT NOCOPY BOOLEAN,
107 p_missing_gre OUT NOCOPY BOOLEAN
108 ) RETURN NUMBER IS
109
110 CURSOR csr_get_GRE_from_loc IS
111 select distinct(pghn_gre.entity_id)
112 from per_gen_hierarchy pgh,
113 per_gen_hierarchy_versions pghv,
114 per_gen_hierarchy_nodes pghn_loc,
115 per_gen_hierarchy_nodes pghn_gre
116 where pgh.type = 'MEXICO HRMS'
117 and pghv.hierarchy_id = pgh.hierarchy_id
118 and p_session_date BETWEEN pghv.date_from
119 AND nvl(pghv.date_to, hr_general.end_of_time)
120 and pghv.status = 'A'
121 and pghn_loc.hierarchy_version_id = pghv.hierarchy_version_id
122 and pghn_loc.node_type = 'MX LOCATION'
123 and pghn_loc.entity_id = to_char(p_location_id) --9695736: added to_char
124 and pghn_gre.hierarchy_node_id = pghn_loc.parent_hierarchy_node_id
125 and pghn_gre.hierarchy_version_id = pghv.hierarchy_version_id
126 and pghn_gre.business_group_id = p_business_group_id -- Bug 4129001
127 and pghn_gre.node_type = 'MX GRE';
128
129 l_gre_id NUMBER;
130 BEGIN
131
132 IF p_location_id IS NULL THEN
133 p_is_ambiguous := FALSE;
134 p_missing_gre := FALSE;
135 return(null);
136 END IF;
137
138 OPEN csr_get_GRE_from_loc;
139 LOOP
140
141 FETCH csr_get_GRE_from_loc INTO l_gre_id;
142
143 ------------------------------------------------
144 -- The first row is fetched.
145 ------------------------------------------------
146 IF csr_get_GRE_from_loc%ROWCOUNT = 1 THEN
147 p_is_ambiguous := FALSE;
148 p_missing_gre := FALSE;
149
150 ------------------------------------------------
151 -- No rows are fetched by the cursor
152 ------------------------------------------------
153 ELSIF csr_get_GRE_from_loc%NOTFOUND and csr_get_GRE_from_loc%ROWCOUNT < 1 THEN
154 p_missing_gre := TRUE;
155 return(null);
156
157 ------------------------------------------------
158 -- More than 1 row is fetched by the cursor.
159 ------------------------------------------------
160 ELSE
161 p_is_ambiguous := TRUE;
162 return(null);
163
164 END IF;
165
166 EXIT WHEN csr_get_GRE_from_loc%NOTFOUND;
167
168 END LOOP;
169
170 ------------------------------------------------
171 -- Only 1 row is fetched. This is the GRE we need
172 ------------------------------------------------
173 return(l_gre_id);
174
175 END get_GRE_from_location;
176
177 --------------------------------------------------------------------------
178 -- --
179 -- Name : get_GRE_from_scl --
180 -- Type : Function --
181 -- Access : Public --
182 -- Description : This function is used to fetch the GRE from the --
183 -- Mexico Statutory Information tab (Soft Coded Key --
184 -- Flexfield). --
185 -- --
186 -- Parameters : --
187 -- IN : p_soft_coding_keyflex_id NUMBER --
188 -- OUT : N/A --
189 -- RETURN : NUMBER --
190 -- --
191 --------------------------------------------------------------------------
192
193 FUNCTION get_GRE_from_scl(p_soft_coding_keyflex_id IN NUMBER
194
195 ) RETURN NUMBER IS
196
197 CURSOR csr_get_GRE_from_scl IS
198 SELECT segment1
199 FROM hr_soft_coding_keyflex
200 WHERE soft_coding_keyflex_id = p_soft_coding_keyflex_id;
201
202 l_gre_id NUMBER := null;
203 BEGIN
204
205 OPEN csr_get_GRE_from_scl;
206 FETCH csr_get_GRE_from_scl INTO l_gre_id;
207 CLOSE csr_get_GRE_from_scl;
208
209 return(l_gre_id);
210
211 END get_GRE_from_scl;
212
213
214 --------------------------------------------------------------------------
215 -- --
216 -- Name : check_bus_grp --
217 -- Type : Procedure --
218 -- Access : Public --
219 -- Description : This procedure determines the agreement between --
220 -- specified business group and legislation. --
221 -- --
222 -- Parameters : --
223 -- IN : p_business_group_id NUMBER --
224 -- p_legislation_code VARCHAR2 --
225 -- OUT : N/A --
226 -- RETURN : Raises an error if a business group does not belong --
227 -- to the legislation specified. --
228 -- --
229 --------------------------------------------------------------------------
230
231
232 PROCEDURE check_bus_grp (p_business_group_id IN NUMBER
233 ,p_legislation_code IN VARCHAR2) AS
234
235 CURSOR csr_bg IS
236 SELECT legislation_code
237 FROM per_business_groups pbg
238 WHERE pbg.business_group_id = p_business_group_id;
239 --
240 l_legislation_code per_business_groups.legislation_code%type;
241 BEGIN
242
243 OPEN csr_bg;
244 --
245 FETCH csr_bg
246 INTO l_legislation_code;
247 --
248 IF csr_bg%NOTFOUND THEN
249 CLOSE csr_bg;
250 hr_utility.set_message(800, 'HR_7208_API_BUS_GRP_INVALID');
251 hr_utility.raise_error;
252 END IF;
253 CLOSE csr_bg;
254 --
255 IF l_legislation_code <> p_legislation_code THEN
256 hr_utility.set_message(800, 'HR_7961_PER_BUS_GRP_INVALID');
257 hr_utility.set_message_token('LEG_CODE', p_legislation_code);
258 hr_utility.raise_error;
259 END IF;
260 EXCEPTION
261 WHEN OTHERS THEN
262 IF csr_bg%ISOPEN THEN
263 CLOSE csr_bg;
264 END IF;
265 RAISE;
266
267 END check_bus_grp;
268
269
270 --------------------------------------------------------------------------
271 -- --
272 -- Name : get_bg_from_person --
273 -- Type : Function --
274 -- Access : Public --
275 -- Description : This function determines the business_group_id for a--
276 -- given person. --
277 -- Parameters : --
278 -- IN : p_person_id NUMBER --
279 -- --
280 -- OUT : N/A --
281 -- RETURN : business_group_id NUMBER --
282 -- --
283 --------------------------------------------------------------------------
284
285 FUNCTION GET_BG_FROM_PERSON (
286 p_person_id per_all_people_f.person_id%type)
287 RETURN per_all_people_f.business_group_id%type AS
288
289 CURSOR csr_fetch_bg IS
290 SELECT business_group_id
291 FROM per_people_f
292 WHERE person_id = p_person_id
293 AND rownum < 2;
294
295 l_bg_id per_all_people_f.business_group_id%type;
296
297 BEGIN
298 OPEN csr_fetch_bg;
299 FETCH csr_fetch_bg INTO l_bg_id;
300
301 IF csr_fetch_bg%NOTFOUND THEN
302 CLOSE csr_fetch_bg;
303 hr_utility.set_message(801, 'HR_7971_PER_PER_IN_PERSON');
304 hr_utility.raise_error;
305 END IF;
306 CLOSE csr_fetch_bg;
307
308 RETURN (l_bg_id);
309 END GET_BG_FROM_PERSON;
310
311 --------------------------------------------------------------------------
312 -- --
313 -- Name : get_bg_from_assignment --
314 -- Type : Function --
315 -- Access : Public --
316 -- Description : This function determines the business_group_id for a--
317 -- given assignment. --
318 -- Parameters : --
319 -- IN : p_assignment_id NUMBER --
320 -- OUT : N/A --
321 -- RETURN : business_group_id NUMBER --
322 -- --
323 --------------------------------------------------------------------------
324
325 FUNCTION GET_BG_FROM_ASSIGNMENT (
326 p_assignment_id per_all_assignments_f.assignment_id%TYPE)
327 RETURN per_all_assignments_f.business_group_id%TYPE AS
328
329 CURSOR csr_fetch_bg IS
330 SELECT business_group_id
331 FROM per_assignments_f
332 WHERE assignment_id = p_assignment_id
333 AND rownum < 2;
334
335 l_bg_id per_all_assignments_f.business_group_id%TYPE;
336
337 BEGIN
338 OPEN csr_fetch_bg;
339 FETCH csr_fetch_bg INTO l_bg_id;
340
341 IF csr_fetch_bg%NOTFOUND THEN
342 CLOSE csr_fetch_bg;
343 hr_utility.set_message(801, 'HR_7348_PPM_ASSIGNMENT_INVALID');
344 hr_utility.raise_error;
345 END IF;
346 CLOSE csr_fetch_bg;
347
348 RETURN (l_bg_id);
349 END GET_BG_FROM_ASSIGNMENT;
350
351 --------------------------------------------------------------------------
352 -- --
353 -- Name : get_legal_employer --
354 -- Type : Function --
355 -- Access : Public --
356 -- Description : Function to return the legal employer based on the --
357 -- Mexico Statutory Reporting Hierarchy for given GRE --
358 -- Note: The effective date is defaulted to that of --
359 -- the session. --
360 -- Parameters : --
361 -- IN : p_business_group_id NUMBER --
362 -- IN : p_tax_unit_id NUMBER --
363 -- OUT : N/A --
364 -- RETURN : NUMBER --
365 -- --
366 --------------------------------------------------------------------------
367 FUNCTION get_legal_employer(p_business_group_id NUMBER,
368 p_tax_unit_id NUMBER) RETURN NUMBER IS
369 --
370 r_legal_employer_id hr_organization_units.organization_id%TYPE;
371 lv_proc VARCHAR2(240);
372 ld_effective_date DATE;
373 BEGIN
374
375 lv_proc := 'hr_mx_utility.get_legal_employer';
376 r_legal_employer_id := -1;
377
378 IF (g_debug)
379 THEN
380 hr_utility.trace('Entering '|| lv_proc);
381 END IF;
382
383 SELECT effective_date
384 INTO ld_effective_date
385 FROM fnd_sessions
386 WHERE session_id = USERENV('sessionid');
387
388 IF (g_debug)
389 THEN
390 hr_utility.set_location(lv_proc, 10);
391 END IF;
392
393 r_legal_employer_id := get_legal_employer(p_business_group_id,
394 p_tax_unit_id,
395 ld_effective_date);
396
397 IF (g_debug)
398 THEN
399 hr_utility.trace('Leaving '|| lv_proc);
400 END IF;
401
402 RETURN r_legal_employer_id;
403
404 --
405 END get_legal_employer;
406
407 --------------------------------------------------------------------------
408 -- --
409 -- Name : get_legal_employer --
410 -- Type : Function --
411 -- Access : Public --
412 -- Description : Function to return the legal employer based on the --
413 -- Mexico Statutory Reporting Hierarchy for given GRE --
414 -- as on the specifed effeective date. --
415 -- Parameters : --
416 -- IN : p_business_group_id NUMBER --
417 -- IN : p_tax_unit_id NUMBER --
418 -- IN : p_effective_date DATE --
419 -- OUT : N/A --
420 -- RETURN : NUMBER --
421 -- --
422 --------------------------------------------------------------------------
423 FUNCTION get_legal_employer(p_business_group_id NUMBER,
424 p_tax_unit_id NUMBER,
425 p_effective_date DATE)
426 RETURN NUMBER IS
427 --
428 r_legal_employer_id hr_organization_units.organization_id%TYPE;
429 lv_proc VARCHAR2(240);
430 BEGIN
431
432 lv_proc := 'hr_mx_utility.get_legal_employer-2';
433 r_legal_employer_id := -1;
434
435 IF (g_debug)
436 THEN
437 hr_utility.trace('Entering '|| lv_proc);
438 END IF;
439
440 SELECT le_node.entity_id
441 INTO r_legal_employer_id
442 FROM per_gen_hierarchy_nodes gre_node,
443 per_gen_hierarchy_nodes le_node,
444 per_gen_hierarchy_versions hier_ver,
445 fnd_lookup_values lv
446 WHERE gre_node.node_type = 'MX GRE'
447 AND gre_node.entity_id = to_char(p_tax_unit_id)
448 AND gre_node.business_group_id = p_business_group_id
449 AND gre_node.hierarchy_version_id = le_node.hierarchy_version_id
450 AND le_node.hierarchy_node_id = gre_node.parent_hierarchy_node_id
451 AND gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id
452 AND status = lv.lookup_code
453 AND lv.meaning = 'Active'
454 AND lv.LANGUAGE = 'US'
455 AND lv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS'
456 AND p_effective_date BETWEEN hier_ver.date_from
457 AND NVL(hier_ver.date_to, hr_general.end_of_time);
458
459 IF (g_debug)
460 THEN
461 hr_utility.trace('Leaving '|| lv_proc);
462 END IF;
463
464 RETURN r_legal_employer_id;
465
466 EXCEPTION
467 WHEN OTHERS
468 THEN
469 hr_utility.set_message(800, 'HR_MX_INVALID_LE');
470 hr_utility.raise_error;
471
472 IF (g_debug)
473 THEN
474 hr_utility.trace('Leaving '|| lv_proc);
475 END IF;
476
477 RETURN r_legal_employer_id;
478 --
479 END get_legal_employer;
480
481 --------------------------------------------------------------------------
482 -- --
483 -- Name : get_tbl_value_local --
484 -- Type : Function --
485 -- Access : Private --
486 -- Description : Function to return value of the specified column --
487 -- of the user table specified. --
488 -- Parameters : --
489 -- IN : p_table_name VARCHAR2 --
490 -- IN : p_column_name VARCHAR2 --
491 -- IN : p_business_group_id NUMBER --
492 -- IN : p_organization_id NUMBER --
493 -- OUT : N/A --
494 -- RETURN : VARCHAR2 --
495 -- --
496 --------------------------------------------------------------------------
497 FUNCTION get_tbl_val_local (p_table_name VARCHAR2,
498 p_column_name VARCHAR2,
499 p_business_group_id NUMBER,
500 p_organization_id NUMBER)
501 RETURN VARCHAR2 IS
502
503 r_tbl_val NUMBER;
504 lv_row_name pay_user_rows_f.row_low_range_or_name%TYPE;
505 lv_proc VARCHAR2(240);
506
507 FUNCTION get_organization_name (p_organization_id NUMBER)
508 RETURN VARCHAR2 IS
509
510 lv_org_name hr_organization_units.name%TYPE;
511
512 BEGIN
513
514 lv_proc := 'get_organization_name';
515
516 IF (g_debug)
517 THEN
518 hr_utility.trace('Entering '|| lv_proc);
519 END IF;
520
521 SELECT name
522 INTO lv_org_name
523 FROM hr_all_organization_units
524 WHERE organization_id = p_organization_id;
525
526 IF (g_debug)
527 THEN
528 hr_utility.trace('Leaving '|| lv_proc);
529 END IF;
530
531 RETURN lv_org_name;
532
533 END get_organization_name;
534
535 BEGIN
536 lv_proc := 'get_tbl_val_local';
537
538 IF (g_debug)
539 THEN
540 hr_utility.trace('Entering '|| lv_proc);
541 END IF;
542
543
544 lv_row_name := get_organization_name(p_organization_id);
545
546 BEGIN
547
548 IF (g_debug)
549 THEN
550 hr_utility.trace('Getting Tax Subsidy Percent for '|| lv_row_name);
551 END IF;
552
553 r_tbl_val := FND_NUMBER.canonical_to_number(hruserdt.get_table_value(
554 p_bus_group_id => p_business_group_id,
555 p_table_name => p_table_name,
556 p_col_name => p_column_name,
557 p_row_value => lv_row_name));
558
559 /* Bug 4187012
560 gt_tax_subsidy_percent(p_organization_id) := r_tbl_val;
561 */
562
563 IF (g_debug)
564 THEN
565 hr_utility.trace('Found Tax Subsidy Percent : '|| r_tbl_val);
566 END IF;
567
568 EXCEPTION
569 WHEN OTHERS
570 THEN
571
572 IF (g_debug)
573 THEN
574 hr_utility.trace('Exception in '|| lv_proc);
575 END IF;
576
577 RETURN NULL;
578 END;
579
580 IF (g_debug)
581 THEN
582 hr_utility.trace('Leaving '|| lv_proc);
583 END IF;
584
585 RETURN r_tbl_val;
586
587 END get_tbl_val_local;
588 --------------------------------------------------------------------------
589 -- --
590 -- Name : get_wrip --
591 -- Type : Function --
592 -- Access : Public --
593 -- Description : Function to return the Work Risk Insurance Premium --
594 -- for the given tax unit id --
595 -- Parameters : --
596 -- IN : p_business_group_id NUMBER --
597 -- IN : p_tax_unit_id NUMBER --
598 -- OUT : N/A --
599 -- RETURN : NUMBER --
600 -- --
601 --------------------------------------------------------------------------
602 FUNCTION get_wrip(p_business_group_id NUMBER, p_tax_unit_id NUMBER)
603 RETURN NUMBER IS
604
605 r_wrip NUMBER;
606 lv_gre_name hr_organization_units.name%TYPE;
607 lv_proc VARCHAR2(240);
608
609 BEGIN
610
611 lv_proc := 'get_wrip';
612
613 IF (g_debug)
614 THEN
615 hr_utility.trace('Entering '|| lv_proc);
616 END IF;
617
618 IF (NOT gt_wrip.EXISTS(p_tax_unit_id))
619 THEN
620
621 r_wrip := get_tbl_val_local('Work Risk Insurance Premium',
622 'Percentage',
623 p_business_group_id,
624 p_tax_unit_id);
625 ELSE
626 r_wrip := gt_wrip(p_tax_unit_id);
627 END IF;
628
629 IF (r_wrip IS NULL)
630 THEN
631
632 IF (g_debug)
633 THEN
634 hr_utility.trace('Leaving '|| lv_proc);
635 END IF;
636
637 hr_utility.set_message(800,'HR_MX_NO_WRIP');
638 hr_utility.raise_error;
639
640 RETURN r_wrip;
641
642 /* Bug 4187012 */
643 ELSE
644 gt_wrip(p_tax_unit_id) := r_wrip;
645 END IF;
646
647 IF (g_debug)
648 THEN
649 hr_utility.trace('Leaving '|| lv_proc);
650 END IF;
651
652 RETURN r_wrip;
653
654 END get_wrip;
655
656 --------------------------------------------------------------------------
657 -- --
658 -- Name : get_tax_subsidy_percent --
659 -- Type : Function --
660 -- Access : Public --
661 -- Description : Function to return the Tax Subsidy Percentage. --
662 -- This function first looks at tax subsidy percentage --
663 -- defined
664 -- Mexico Statutory Reporting Hierarchy for given GRE --
665 -- Parameters : --
666 -- IN : p_business_group_id NUMBER --
667 -- IN : p_tax_unit_id NUMBER --
668 -- OUT : N/A --
669 -- RETURN : NUMBER --
670 -- --
671 --------------------------------------------------------------------------
672 FUNCTION get_tax_subsidy_percent(p_business_group_id NUMBER,
673 p_tax_unit_id NUMBER)
674 RETURN NUMBER IS
675
676 r_tax_subsidy_percentage NUMBER;
677 lv_gre_name hr_organization_units.name%TYPE;
678 lv_le_name hr_organization_units.name%TYPE;
679 ln_legal_employer_id hr_organization_units.organization_id%TYPE;
680 lv_proc VARCHAR2(240);
681
682 BEGIN
683
684 lv_proc := 'get_tax_subsidy_percent';
685
686 IF (g_debug)
687 THEN
688 hr_utility.trace('Entering '|| lv_proc);
689 END IF;
690
691 ln_legal_employer_id := get_legal_employer(p_business_group_id,
692 p_tax_unit_id);
693
694 IF (NOT gt_tax_subsidy_percent.EXISTS(p_tax_unit_id))
695 THEN
696
697 r_tax_subsidy_percentage := get_tbl_val_local('Tax Subsidy Percentage',
698 'Percentage',
699 p_business_group_id,
700 p_tax_unit_id);
701
702 IF (r_tax_subsidy_percentage is NULL)
703 THEN
704
705 IF (NOT gt_tax_subsidy_percent.EXISTS(ln_legal_employer_id))
706 THEN
707
708 r_tax_subsidy_percentage := get_tbl_val_local('Tax Subsidy Percentage',
709 'Percentage',
710 p_business_group_id,
711 ln_legal_employer_id);
712 gt_tax_subsidy_percent(ln_legal_employer_id) :=
713 r_tax_subsidy_percentage;
714 ELSE
715
716 r_tax_subsidy_percentage :=
717 gt_tax_subsidy_percent(ln_legal_employer_id);
718 END IF;
719
720 ELSE
721 /* Bug 4187012 */
722 gt_tax_subsidy_percent(p_tax_unit_id) := r_tax_subsidy_percentage;
723 END IF;
724
725 ELSE
726 r_tax_subsidy_percentage := gt_tax_subsidy_percent(p_tax_unit_id);
727 END IF;
728
729 IF (r_tax_subsidy_percentage IS NULL)
730 THEN
731
732 IF (g_debug)
733 THEN
734 hr_utility.trace('Leaving '|| lv_proc);
735 END IF;
736
737 hr_utility.set_message(800,'HR_MX_NO_SUBSIDY');
738 hr_utility.raise_error;
739
740 RETURN r_tax_subsidy_percentage;
741
742 END IF;
743
744 IF (g_debug)
745 THEN
746 hr_utility.trace('Leaving '|| lv_proc);
747 END IF;
748
749 RETURN r_tax_subsidy_percentage;
750
751 END get_tax_subsidy_percent;
752
753 --------------------------------------------------------------------------
754 -- --
755 -- Name : get_tax_subsidy_percent --
756 -- Type : Function --
757 -- Access : Public --
758 -- Description : Function to return the Tax Subsidy Percentage. --
759 -- This function first looks at tax subsidy percentage --
760 -- defined for given GRE and then at the LE. The LE is --
761 -- derived from get_legal_employer using the effective --
762 -- date specified. --
763 -- Parameters : --
764 -- IN : p_business_group_id NUMBER --
765 -- IN : p_tax_unit_id NUMBER --
766 -- IN : p_effective_date DATE --
767 -- OUT : N/A --
768 -- RETURN : NUMBER --
769 -- --
770 --------------------------------------------------------------------------
771 FUNCTION get_tax_subsidy_percent(p_business_group_id NUMBER,
772 p_tax_unit_id NUMBER,
773 p_effective_date DATE)
774 RETURN NUMBER IS
775
776 r_tax_subsidy_percentage NUMBER;
777 lv_gre_name hr_organization_units.name%TYPE;
778 lv_le_name hr_organization_units.name%TYPE;
779 ln_legal_employer_id hr_organization_units.organization_id%TYPE;
780 lv_proc VARCHAR2(240);
781
782 BEGIN
783
784 lv_proc := 'get_tax_subsidy_percent';
785
786 IF (g_debug)
787 THEN
788 hr_utility.trace('Entering '|| lv_proc);
789 END IF;
790
791 ln_legal_employer_id := get_legal_employer(p_business_group_id,
792 p_tax_unit_id,
793 p_effective_date);
794
795 IF (NOT gt_tax_subsidy_percent.EXISTS(p_tax_unit_id))
796 THEN
797
798 r_tax_subsidy_percentage := get_tbl_val_local('Tax Subsidy Percentage',
799 'Percentage',
800 p_business_group_id,
801 p_tax_unit_id);
802
803 IF (r_tax_subsidy_percentage is NULL)
804 THEN
805
806 IF (NOT gt_tax_subsidy_percent.EXISTS(ln_legal_employer_id))
807 THEN
808
809 r_tax_subsidy_percentage := get_tbl_val_local('Tax Subsidy Percentage',
810 'Percentage',
811 p_business_group_id,
812 ln_legal_employer_id);
813 gt_tax_subsidy_percent(ln_legal_employer_id) :=
814 r_tax_subsidy_percentage;
815 ELSE
816
817 r_tax_subsidy_percentage :=
818 gt_tax_subsidy_percent(ln_legal_employer_id);
819 END IF;
820
821 ELSE
822 /* Bug 4187012 */
823 gt_tax_subsidy_percent(p_tax_unit_id) := r_tax_subsidy_percentage;
824 END IF;
825
826 ELSE
827 r_tax_subsidy_percentage := gt_tax_subsidy_percent(p_tax_unit_id);
828 END IF;
829
830 IF (r_tax_subsidy_percentage IS NULL)
831 THEN
832
833 IF (g_debug)
834 THEN
835 hr_utility.trace('Leaving '|| lv_proc);
836 END IF;
837
838 hr_utility.set_message(800,'HR_MX_NO_SUBSIDY');
839 hr_utility.raise_error;
840
841 RETURN r_tax_subsidy_percentage;
842
843 END IF;
844
845 IF (g_debug)
846 THEN
847 hr_utility.trace('Leaving '|| lv_proc);
848 END IF;
849
850 RETURN r_tax_subsidy_percentage;
851
852 END get_tax_subsidy_percent;
853
854
855 --------------------------------------------------------------------------
856 -- --
857 -- Name : get_hire_anniversary --
858 -- Type : Function --
859 -- Access : Public --
860 -- Description : Function to return the date of hire of a person. --
861 -- Parameters : --
862 -- IN : p_person_id NUMBER --
863 -- IN : p_effective_date DATE --
864 -- OUT : N/A --
865 -- RETURN : DATE --
866 -- --
867 --------------------------------------------------------------------------
868 FUNCTION get_hire_anniversary(p_person_id NUMBER,
869 p_effective_date DATE) RETURN DATE IS
870 -- Bug 4650086
871 CURSOR c_get_adj_svc_date
872 IS
873 SELECT pps.adjusted_svc_date
874 FROM per_periods_of_service pps,
875 per_all_people_f pap
876 WHERE pap.person_id = p_person_id
877 AND pps.person_id = pap.person_id
878 AND p_effective_date BETWEEN pap.effective_start_date
879 AND pap.effective_end_date
880 AND pps.date_start = (SELECT MAX (pps1.date_start)
881 FROM per_periods_of_service pps1
882 WHERE pps1.person_id = pps.person_id
883 AND pps1.date_start <= p_effective_date);
884 -- ORDER BY pps.adjusted_svc_date DESC;
885
886 -- cursor to get the start_date or original_date_of_hire
887 CURSOR c_get_hire_date
888 IS
889 SELECT NVL(original_date_of_hire, start_date)
890 FROM per_all_people_f
891 WHERE person_id = p_person_id
892 ORDER BY 1 desc;
893
894 ld_adj_svc_date DATE;
895 ld_seniority_from DATE;
896 BEGIN
897 OPEN c_get_adj_svc_date;
898 FETCH c_get_adj_svc_date INTO ld_adj_svc_date ;
899 CLOSE c_get_adj_svc_date;
900
901 hr_utility.trace('ld_adj_svc_date = '|| TO_CHAR(ld_adj_svc_date));
902
903 IF ld_adj_svc_date IS NOT NULL THEN
904 ld_seniority_from := ld_adj_svc_date ;
905 ELSE
906 OPEN c_get_hire_date;
907 FETCH c_get_hire_date INTO ld_seniority_from;
908 CLOSE c_get_hire_date ;
909 END IF;
910
911 hr_utility.trace('ld_seniority_from = '|| TO_CHAR(ld_seniority_from));
912
913 IF ld_seniority_from IS NULL THEN
914 hr_utility.trace('Cannot determine the start of service!');
915 hr_utility.raise_error;
916 END IF;
917
918 RETURN (ld_seniority_from);
919 END get_hire_anniversary;
920
921 --------------------------------------------------------------------------
922 -- --
923 -- Name : get_seniority_social_security --
924 -- Type : Function --
925 -- Access : Public --
926 -- Description : Function to return the seniority of the person as --
927 -- on the effective date --
928 -- Parameters : --
929 -- IN : p_person_id NUMBER --
930 -- IN : p_effective_date DATE --
931 -- OUT : N/A --
932 -- RETURN : NUMBER --
933 -- --
934 --------------------------------------------------------------------------
935 FUNCTION get_seniority_social_security(p_person_id NUMBER,
936 p_effective_date DATE) RETURN NUMBER IS
937 --
938 ln_seniority_years NUMBER;
939
940 BEGIN
941 --
942 -- calculate seniority years
943 /*Bug#9664526: Changes starts */
944 /* -- should not be rounding off to the next year
945 SELECT CEIL((p_effective_date - get_hire_anniversary (
946 p_person_id,
947 p_effective_date))/365)
948 INTO ln_seniority_years
949 FROM DUAL ; */
950
951 SELECT (p_effective_date - get_hire_anniversary (
952 p_person_id,
953 p_effective_date))/365
954 INTO ln_seniority_years
955 FROM DUAL ;
956
957 IF ln_seniority_years < 0 THEN
958
959 ln_seniority_years := 0;
960
961 ELSE
962
963 ln_seniority_years := ROUND( ln_seniority_years,2);
964
965 END IF;
966 /*Bug#9664526: Changes ends */
967 hr_utility.trace('ln_seniority_years = '|| TO_CHAR(ln_seniority_years));
968
969 RETURN ln_seniority_years;
970
971 --
972 END get_seniority_social_security;
973
974 --------------------------------------------------------------------------
975 -- --
976 -- Name : get_seniority --
977 -- Type : Function --
978 -- Access : Public --
979 -- Description : Function to return the seniority of the person as --
980 -- on the effective date. --
981 -- The seniority for Amends should be computed as --
982 -- follows: --
983 -- Fractions from 0 to 6 months, Seniority = 0 --
984 -- Fractions from 6.1 to 12 months, Seniority = 1 --
985 -- 2 years 3 months = 2 seniority years --
986 -- 2 years 6 months 1 day = 3 seniority years --
987 -- --
988 -- Parameters : --
989 -- IN : p_business_group_id NUMBER --
990 -- IN : p_tax_unit_id NUMBER --
991 -- IN : p_payroll_id NUMBER --
992 -- IN : p_person_id NUMBER --
993 -- IN : p_effective_date DATE --
994 -- OUT : N/A --
995 -- RETURN : NUMBER --
996 -- --
997 --------------------------------------------------------------------------
998 FUNCTION get_seniority(p_business_group_id IN NUMBER
999 ,p_tax_unit_id IN NUMBER
1000 ,p_payroll_id IN NUMBER
1001 ,p_person_id IN NUMBER
1002 ,p_effective_date IN DATE)
1003 RETURN NUMBER IS
1004 --
1005 ln_seniority_years NUMBER;
1006 ld_hire_date DATE;
1007 ln_days_in_a_year NUMBER;
1008
1009 BEGIN
1010 --
1011 ln_days_in_a_year := pay_mx_utility.get_days_in_year(
1012 p_business_group_id => p_business_group_id
1013 ,p_tax_unit_id => p_tax_unit_id
1014 ,p_payroll_id => p_payroll_id);
1015
1016 --
1017 ld_hire_date := hr_mx_utility.get_hire_anniversary(
1018 p_person_id => p_person_id
1019 ,p_effective_date => p_effective_date);
1020 --
1021 -- calculate seniority years
1022
1023 SELECT ROUND( (p_effective_date - ld_hire_date) / ln_days_in_a_year )
1024 INTO ln_seniority_years
1025 FROM DUAL ;
1026
1027 IF ln_seniority_years < 0 THEN
1028
1029 ln_seniority_years := 0;
1030
1031 END IF;
1032
1033 hr_utility.trace('ln_seniority_years = '|| TO_CHAR(ln_seniority_years));
1034
1035 RETURN ln_seniority_years;
1036
1037 --
1038 END get_seniority;
1039
1040
1041 --------------------------------------------------------------------------
1042 -- --
1043 -- Name : get_IANA_charset --
1044 -- Type : Function --
1045 -- Access : Public --
1046 -- Description : Function to IANA charset equivalent of --
1047 -- NLS_CHARACTERSET --
1048 -- Parameters : --
1049 -- IN : N/A --
1050 -- OUT : N/A --
1051 -- RETURN : VARCHAR2 --
1052 -- --
1053 --------------------------------------------------------------------------
1054 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
1055 CURSOR csr_get_iana_charset IS
1056 SELECT tag
1057 FROM fnd_lookup_values
1058 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
1059 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
1060 INSTR(USERENV('LANGUAGE'), '.') + 1)
1061 AND language = 'US';
1062
1063 lv_iana_charset fnd_lookup_values.tag%type;
1064 BEGIN
1065 OPEN csr_get_iana_charset;
1066 FETCH csr_get_iana_charset INTO lv_iana_charset;
1067 CLOSE csr_get_iana_charset;
1068
1069 hr_utility.trace('IANA Charset = '||lv_iana_charset);
1070 RETURN (lv_iana_charset);
1071 END get_IANA_charset;
1072
1073 --------------------------------------------------------------------------------
1074 -- FUNCTION chk_entry_in_lookup
1075 --------------------------------------------------------------------------------
1076 FUNCTION chk_entry_in_lookup
1077 (p_lookup_type IN hr_lookups.lookup_type%TYPE
1078 ,p_entry_val IN hr_lookups.meaning%TYPE
1079 ,p_effective_date IN hr_lookups.start_date_active%TYPE
1080 ,p_message OUT NOCOPY VARCHAR2) RETURN VARCHAR2 AS
1081 --
1082 CURSOR c_entry_in_lookup IS
1083 SELECT 'Y'
1084 FROM hr_lookups hll
1085 WHERE hll.lookup_type = p_lookup_type
1086 AND hll.lookup_code = p_entry_val
1087 AND hll.enabled_flag = 'Y'
1088 AND p_effective_date BETWEEN NVL(hll.start_date_active, p_effective_date)
1089 AND NVL(hll.end_date_active, p_effective_date);
1090
1091 l_found_value_in_lookup VARCHAR2(240);
1092 -- There is 255 character limit on the error screen
1093 l_msg VARCHAR2(255);
1094 --
1095 BEGIN
1096 --
1097 l_msg := ' ';
1098 l_found_value_in_lookup := 'N';
1099
1100 -- Check if the value exists in the lookup
1101 OPEN c_entry_in_lookup;
1102 FETCH c_entry_in_lookup INTO l_found_value_in_lookup;
1103 CLOSE c_entry_in_lookup;
1104 --
1105
1106 IF l_found_value_in_lookup = 'N' THEN
1107
1108 IF p_lookup_type = 'PAY_MX_YES_NO' THEN
1109 l_msg := fnd_message.get_string('PAY','PAY_MX_INVALID_YES_NO_INPUT');
1110 ELSIF p_lookup_type = 'PAY_MX_PTU_CALC_METHODS' THEN
1111 l_msg :=
1112 fnd_message.get_string('PAY','PAY_MX_INVALID_PTU_CALC_METHOD');
1113 END IF;
1114
1115 END IF;
1116
1117 --
1118 -- Setup Out variables and Return statements
1119 p_message := l_msg;
1120 RETURN l_found_value_in_lookup;
1121 --
1122 EXCEPTION
1123 WHEN OTHERS THEN
1124 IF c_entry_in_lookup%ISOPEN THEN
1125 CLOSE c_entry_in_lookup;
1126 END IF;
1127 END chk_entry_in_lookup;
1128
1129 --
1130 procedure DERIVE_HR_LOC_ADDRESS
1131 (p_tax_name in varchar2,
1132 p_style in varchar2,
1133 p_address_line_1 in varchar2,
1134 p_address_line_2 in varchar2,
1135 p_address_line_3 in varchar2,
1136 p_town_or_city in varchar2,
1137 p_country in varchar2,
1138 p_postal_code in varchar2,
1139 p_region_1 in varchar2,
1140 p_region_2 in varchar2,
1141 p_region_3 in varchar2,
1142 p_telephone_number_1 in varchar2,
1143 p_telephone_number_2 in varchar2,
1144 p_telephone_number_3 in varchar2,
1145 p_loc_information13 in varchar2,
1146 p_loc_information14 in varchar2,
1147 p_loc_information15 in varchar2,
1148 p_loc_information16 in varchar2,
1149 p_loc_information17 in varchar2,
1150 p_attribute_category in varchar2,
1151 p_attribute1 in varchar2,
1152 p_attribute2 in varchar2,
1153 p_attribute3 in varchar2,
1154 p_attribute4 in varchar2,
1155 p_attribute5 in varchar2,
1156 p_attribute6 in varchar2,
1157 p_attribute7 in varchar2,
1158 p_attribute8 in varchar2,
1159 p_attribute9 in varchar2,
1160 p_attribute10 in varchar2,
1161 p_attribute11 in varchar2,
1162 p_attribute12 in varchar2,
1163 p_attribute13 in varchar2,
1164 p_attribute14 in varchar2,
1165 p_attribute15 in varchar2,
1166 p_attribute16 in varchar2,
1167 p_attribute17 in varchar2,
1168 p_attribute18 in varchar2,
1169 p_attribute19 in varchar2,
1170 p_attribute20 in varchar2,
1171 p_global_attribute_category in varchar2,
1172 p_global_attribute1 in varchar2,
1173 p_global_attribute2 in varchar2,
1174 p_global_attribute3 in varchar2,
1175 p_global_attribute4 in varchar2,
1176 p_global_attribute5 in varchar2,
1177 p_global_attribute6 in varchar2,
1178 p_global_attribute7 in varchar2,
1179 p_global_attribute8 in varchar2,
1180 p_global_attribute9 in varchar2,
1181 p_global_attribute10 in varchar2,
1182 p_global_attribute11 in varchar2,
1183 p_global_attribute12 in varchar2,
1184 p_global_attribute13 in varchar2,
1185 p_global_attribute14 in varchar2,
1186 p_global_attribute15 in varchar2,
1187 p_global_attribute16 in varchar2,
1188 p_global_attribute17 in varchar2,
1189 p_global_attribute18 in varchar2,
1190 p_global_attribute19 in varchar2,
1191 p_global_attribute20 in varchar2,
1192 p_loc_information18 in varchar2,
1193 p_loc_information19 in varchar2,
1194 p_loc_information20 in varchar2,
1195 p_derived_locale out nocopy varchar2
1196 ) is
1197 begin
1198 if (ltrim(p_town_or_city) is not null) then
1199 p_derived_locale := p_town_or_city || ', ';
1200 end if;
1201 if (ltrim(p_region_1) is not null) then
1202 p_derived_locale := p_derived_locale || p_region_1 || ', ';
1203 end if;
1204 if (ltrim(p_country) is null) then
1205 p_derived_locale := rtrim(p_derived_locale, ',');
1206 else
1207 p_derived_locale := p_derived_locale || p_country;
1208 end if;
1209 end;
1210 --
1211 procedure DERIVE_PER_ADD_ADDRESS
1212 (p_style in varchar2,
1213 p_address_line1 in varchar2,
1214 p_address_line2 in varchar2,
1215 p_address_line3 in varchar2,
1216 p_country in varchar2,
1217 p_date_to in date,
1218 p_postal_code in varchar2,
1219 p_region_1 in varchar2,
1220 p_region_2 in varchar2,
1221 p_region_3 in varchar2,
1222 p_telephone_number_1 in varchar2,
1223 p_telephone_number_2 in varchar2,
1224 p_telephone_number_3 in varchar2,
1225 p_town_or_city in varchar2,
1226 p_addr_attribute_category in varchar2,
1227 p_addr_attribute1 in varchar2,
1228 p_addr_attribute2 in varchar2,
1229 p_addr_attribute3 in varchar2,
1230 p_addr_attribute4 in varchar2,
1231 p_addr_attribute5 in varchar2,
1232 p_addr_attribute6 in varchar2,
1233 p_addr_attribute7 in varchar2,
1234 p_addr_attribute8 in varchar2,
1235 p_addr_attribute9 in varchar2,
1236 p_addr_attribute10 in varchar2,
1237 p_addr_attribute11 in varchar2,
1238 p_addr_attribute12 in varchar2,
1239 p_addr_attribute13 in varchar2,
1240 p_addr_attribute14 in varchar2,
1241 p_addr_attribute15 in varchar2,
1242 p_addr_attribute16 in varchar2,
1243 p_addr_attribute17 in varchar2,
1244 p_addr_attribute18 in varchar2,
1245 p_addr_attribute19 in varchar2,
1246 p_addr_attribute20 in varchar2,
1247 p_add_information13 in varchar2,
1248 p_add_information14 in varchar2,
1249 p_add_information15 in varchar2,
1250 p_add_information16 in varchar2,
1251 p_add_information17 in varchar2,
1252 p_add_information18 in varchar2,
1253 p_add_information19 in varchar2,
1254 p_add_information20 in varchar2,
1255 p_derived_locale out nocopy varchar2
1256 ) is
1257 begin
1258 if (ltrim(p_town_or_city) is not null) then
1259 p_derived_locale := p_town_or_city || ', ';
1260 end if;
1261 if (ltrim(p_region_1) is not null) then
1262 p_derived_locale := p_derived_locale || p_region_1 || ', ';
1263 end if;
1264 if (ltrim(p_country) is null) then
1265 p_derived_locale := rtrim(p_derived_locale, ',');
1266 else
1267 p_derived_locale := p_derived_locale || p_country;
1268 end if;
1269 end;
1270
1271 --
1272 --------------------------------------------------------------------------
1273 -- --
1274 -- Name : get_hire_date --
1275 -- Type : Function --
1276 -- Access : Public --
1277 -- Description : Function to return the date of hire of a person. --
1278 -- Parameters : --
1279 -- IN : p_assignment_id NUMBER --
1280 -- IN : p_effective_date DATE --
1281 -- OUT : N/A --
1282 -- RETURN : DATE --
1283 -- --
1284 --------------------------------------------------------------------------
1285 FUNCTION get_hire_date(p_assignment_id NUMBER,
1286 p_effective_date DATE) RETURN DATE IS
1287
1288 -- curosr to get adjusted service date
1289 CURSOR c_get_adj_svc_date
1290 IS
1291 SELECT pps.adjusted_svc_date
1292 FROM per_periods_of_service pps,
1293 per_all_assignments_f paa
1294 WHERE paa.assignment_id = p_assignment_id
1295 AND p_effective_date BETWEEN paa.effective_start_date
1296 AND paa.effective_end_date
1297 AND pps.person_id = paa.person_id
1298 AND pps.date_start = (SELECT MAX (pps1.date_start)
1299 FROM per_periods_of_service pps1
1300 WHERE pps1.person_id = pps.person_id
1301 AND pps1.date_start <= p_effective_date);
1302
1303
1304 -- cursor to get first hire date
1305 CURSOR c_get_first_hire_date
1306 IS
1307 SELECT original_date_of_hire
1308 FROM per_all_people_f pap,
1309 per_all_assignments_f paa
1310 WHERE paa.assignment_id = p_assignment_id
1311 AND p_effective_date BETWEEN paa.effective_start_date
1312 AND paa.effective_end_date
1313 AND pap.person_id = paa.person_id
1314 AND p_effective_date BETWEEN pap.effective_start_date
1315 AND pap.effective_end_date;
1316
1317 -- cursor to get the latest hire date
1318 CURSOR c_get_latest_hire_date
1319 IS
1320 SELECT max(pps.date_start)
1321 FROM per_all_assignments_f paa,
1322 per_periods_of_service pps
1323 WHERE paa.assignment_id = p_assignment_id
1324 AND p_effective_date BETWEEN paa.effective_start_date
1325 AND paa.effective_end_date
1326 AND pps.person_id = paa.person_id
1327 AND pps.period_of_service_id (+)= paa.period_of_service_id;
1328
1329 ld_adj_svc_date DATE;
1330 ld_seniority_from DATE;
1331 ld_first_hire_date DATE;
1332 BEGIN
1333 OPEN c_get_adj_svc_date;
1334 FETCH c_get_adj_svc_date INTO ld_adj_svc_date ;
1335 CLOSE c_get_adj_svc_date;
1336
1337 hr_utility.trace('ld_adj_svc_date = '|| TO_CHAR(ld_adj_svc_date));
1338
1339 IF ld_adj_svc_date IS NOT NULL THEN
1340 ld_seniority_from := ld_adj_svc_date ;
1341 ELSE
1342 OPEN c_get_first_hire_date;
1343 FETCH c_get_first_hire_date INTO ld_first_hire_date;
1344 CLOSE c_get_first_hire_date;
1345 hr_utility.trace('ld_first_hire_date = '|| TO_CHAR(ld_first_hire_date));
1346 IF ld_first_hire_date IS NOT NULL THEN
1347 ld_seniority_from := ld_first_hire_date ;
1348 ELSE
1349 OPEN c_get_latest_hire_date;
1350 FETCH c_get_latest_hire_date INTO ld_seniority_from;
1351 CLOSE c_get_latest_hire_date ;
1352 END IF;
1353 END IF;
1354
1355 hr_utility.trace('ld_seniority_from = '|| TO_CHAR(ld_seniority_from));
1356
1357 IF ld_seniority_from IS NULL THEN
1358 hr_utility.trace('Cannot determine the start of service!');
1359 hr_utility.raise_error;
1360 END IF;
1361
1362 RETURN (ld_seniority_from);
1363 END get_hire_date;
1364
1365 --
1366 BEGIN
1367 g_debug := hr_utility.debug_enabled;
1368 END hr_mx_utility;