1 Package Body per_abs_bus as
2 /* $Header: peabsrhi.pkb 120.17.12010000.3 2008/09/12 11:33:01 pvelugul ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_abs_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_absence_attendance_id number default null;
15
16 --
17 -- ---------------------------------------------------------------------------
18 -- |---------------------< get_running_totals >----------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 -- Description:
22 -- This procedure gets the year to date totals and running totals for
23 -- both days and hours.
24 --
25 -- Pre-conditions:
26 -- None.
27 --
28 -- In Arguments:
29 -- p_person_id
30 -- p_absence_attendance_type_id
31 -- p_effective_date
32 --
33 -- Out Arguments:
34 -- p_running_total_hours
35 -- p_running_total_days
36 -- p_year_to_date_hours
37 -- p_year_to_date_days
38 --
39 -- Post Success:
40 -- If validation passes, processing continues.
41 --
42 -- Post Failure:
43 -- If validation fails, an error is raised and processing stops.
44 --
45 -- Access Status:
46 -- Internal Table Handler Use Only. API updating is not required as this
47 -- is called from other chk procedures that use API updating.
48 --
49 -- {End Of Comments}
50 -- ----------------------------------------------------------------------------
51 --
52 procedure get_running_totals
53 (p_person_id in number
54 ,p_absence_attendance_type_id in number
55 ,p_effective_date in date
56 ,p_running_total_hours out nocopy number
57 ,p_running_total_days out nocopy number
58 ,p_year_to_date_hours out nocopy number
59 ,p_year_to_date_days out nocopy number)
60 is
61
62 l_proc varchar2(72) := g_package||'get_running_totals';
63 l_absence_hours number;
64 l_absence_days number;
65 l_absence_year date;
66 l_effective_year date;
67 l_hours_or_days varchar2(1);
68 l_increasing_or_decreasing varchar2(1);
69 l_screen_entry_value number;
70 l_effective_start_date date;
71 l_effective_end_date date;
72
73 cursor c_get_running_totals is
74 select nvl(abs.absence_hours, 0),
75 nvl(abs.absence_days, 0),
76 to_date('01/01/'||
77 to_char(abs.date_end,'YYYY'),'DD/MM/YYYY'),
78 abt.hours_or_days,
79 abt.increasing_or_decreasing_flag
80 from per_absence_attendances abs,
81 per_absence_attendance_types abt
82 where abs.person_id = p_person_id
83 and abs.absence_attendance_type_id = abt.absence_attendance_type_id
84 and abs.date_end is not null
85 and abs.date_end <= p_effective_date
86 and abt.input_value_id is not null
87 and abt.input_value_id = (select abt2.input_value_id
88 from per_absence_attendance_types abt2
89 where abt2.absence_attendance_type_id
90 = p_absence_attendance_type_id);
91
92 cursor c_get_hours_or_days is
93 select abt.hours_or_days
94 from per_absence_attendance_types abt
95 where abt.absence_attendance_type_id = p_absence_attendance_type_id;
96
97 cursor c_get_upload_elements is
98 select /*+ leading(PAA) */ nvl(fnd_number.canonical_to_number(pev.screen_entry_value), 0),
99 pev.effective_start_date,
100 pev.effective_end_date
101 from pay_element_entry_values_f pev,
102 pay_element_entries_f pee,
103 per_all_assignments_f paa,
104 per_absence_attendance_types abt
105 where pev.element_entry_id = pee.element_entry_id
106 and pev.input_value_id = abt.input_value_id
107 and pee.assignment_id = paa.assignment_id
108 and paa.person_id = p_person_id
109 and abt.absence_attendance_type_id = p_absence_attendance_type_id
110 -- and pee.creator_type <> 'A' -- Bug 4422696
111 and pee.creator_type not in('A','EE','NR','PR','R','RR') -- Bug 4422696
112 and pee.element_type_id =
113 (select pet.element_type_id
114 from pay_element_types_f pet,
115 pay_input_values_f piv
116 where abt.input_value_id = piv.input_value_id
117 and piv.element_type_id = pet.element_type_id
118 and p_effective_date between piv.effective_start_date
119 and piv.effective_end_date
120 and p_effective_date between pet.effective_start_date
121 and pet.effective_end_date)
122 and p_effective_date between paa.effective_start_date
123 and paa.effective_end_date
124 and paa.primary_flag = 'Y';
125
126 begin
127
128 hr_utility.set_location('Entering:'|| l_proc, 10);
129
130 --
131 -- Initialise all out parameters to zero.
132 --
133 p_running_total_hours := 0;
134 p_running_total_days := 0;
135 p_year_to_date_hours := 0;
136 p_year_to_date_days := 0;
137
138 --
139 -- Set the effective year.
140 --
141 l_effective_year := to_date('01/01/'||
142 to_char(p_effective_date,'YYYY'), 'DD/MM/YYYY');
143
144 hr_utility.set_location(l_proc, 20);
145
146 --
147 -- Get the running totals for this element type for this person.
148 -- Both the year to dates and running totals are collected in the
149 -- same loop construct.
150 -- A loop is required because for each absence, its type could
151 -- have a different unit of measure and a different increasing
152 -- or decreasing flag.
153 --
154 open c_get_running_totals;
155 loop
156
157 fetch c_get_running_totals into l_absence_hours,
158 l_absence_days,
159 l_absence_year,
160 l_hours_or_days,
161 l_increasing_or_decreasing;
162
163 exit when c_get_running_totals%notfound;
164
165 if l_hours_or_days = 'H'
166 and l_increasing_or_decreasing = 'D' then
167
168 hr_utility.set_location(l_proc, 25);
169
170 p_running_total_hours := p_running_total_hours - l_absence_hours;
171
172 --
173 -- If the current absence is in the same year as the effective
174 -- date, the year to date balance is adjusted.
175 --
176 if l_absence_year = l_effective_year then
177 p_year_to_date_hours := p_year_to_date_hours - l_absence_hours;
178 end if;
179
180 elsif l_hours_or_days = 'H'
181 and l_increasing_or_decreasing = 'I' then
182
183 hr_utility.set_location(l_proc, 30);
184
185 p_running_total_hours := p_running_total_hours + l_absence_hours;
186
187 --
188 -- If the current absence is in the same year as the effective
189 -- date, the year to date balance is adjusted.
190 --
191 if l_absence_year = l_effective_year then
192 p_year_to_date_hours := p_year_to_date_hours + l_absence_hours;
193 end if;
194
195 elsif l_hours_or_days is null
196 and l_increasing_or_decreasing = 'I' then
197
198 hr_utility.set_location(l_proc, 35);
199
200 p_running_total_hours := p_running_total_hours + l_absence_hours;
201 p_running_total_days := p_running_total_days + l_absence_days;
202
203 --
204 -- If the current absence is in the same year as the effective
205 -- date, the year to date balance is adjusted.
206 --
207 if l_absence_year = l_effective_year then
208 p_year_to_date_hours := p_year_to_date_hours + l_absence_hours;
209 p_year_to_date_days := p_year_to_date_days + l_absence_days;
210 end if;
211
212 elsif l_hours_or_days = 'D'
213 and l_increasing_or_decreasing = 'D' then
214
215 hr_utility.set_location(l_proc, 40);
216
217 p_running_total_days := p_running_total_days - l_absence_days;
218
219 --
220 -- If the current absence is in the same year as the effective
221 -- date, the year to date balance is adjusted.
222 --
223 if l_absence_year = l_effective_year then
224 p_year_to_date_days := p_year_to_date_days - l_absence_days;
225 end if;
226
227 elsif l_hours_or_days = 'D'
228 and l_increasing_or_decreasing = 'I' then
229
230 hr_utility.set_location(l_proc, 45);
231
232 p_running_total_days := p_running_total_days + l_absence_days;
233
234 --
235 -- If the current absence is in the same year as the effective
236 -- date, the year to date balance is adjusted.
237 --
238 if l_absence_year = l_effective_year then
239 p_year_to_date_days := p_year_to_date_days + l_absence_days;
240 end if;
241
242 elsif l_hours_or_days is null
243 and l_increasing_or_decreasing = 'D' then
244
245 hr_utility.set_location(l_proc, 50);
246
247 p_running_total_hours := p_running_total_hours - l_absence_hours;
248 p_running_total_days := p_running_total_days - l_absence_days;
249
250 --
251 -- If the current absence is in the same year as the effective
252 -- date, the year to date balance is adjusted.
253 --
254 if l_absence_year = l_effective_year then
255 p_year_to_date_hours := p_year_to_date_hours - l_absence_hours;
256 p_year_to_date_days := p_year_to_date_days - l_absence_days;
257 end if;
258
259 end if;
260
261 end loop;
262 close c_get_running_totals;
263
264 hr_utility.set_location(l_proc, 55);
265
266 --
267 -- Is this absence type in hours or days?
268 --
269 open c_get_hours_or_days;
270 fetch c_get_hours_or_days into l_hours_or_days;
271 close c_get_hours_or_days;
272
273 hr_utility.set_location(l_proc, 60);
274
275 --
276 -- Add any upload elements to the balance. Upload elements
277 -- are elements linked to the absence type but do not
278 -- have an associated absence record. We ADD the upload
279 -- elements to the balance regardless of whether the
280 -- absence type is increasing or decreasing. This is
281 -- because the element could be linked to n number of absence
282 -- types (1/2 could be increasing and 1/2 could be decreasing).
283 --
284 open c_get_upload_elements;
285 loop
286
287 fetch c_get_upload_elements into l_screen_entry_value
288 ,l_effective_start_date
289 ,l_effective_end_date;
290
291 exit when c_get_upload_elements%notfound;
292
293 --
294 -- Add to the running total balance.
295 --
296 if l_hours_or_days = 'D' then
297
298 hr_utility.set_location(l_proc, 65);
299 p_running_total_days := p_running_total_days + l_screen_entry_value;
300
301 elsif l_hours_or_days = 'H' then
302
303 hr_utility.set_location(l_proc, 70);
304 p_running_total_hours := p_running_total_hours + l_screen_entry_value;
305
306 end if;
307
308 --
309 -- If its in the current year add the year to date balance.
310 --
311 if to_char(l_effective_start_date, 'YYYY') = to_char(p_effective_date, 'YYYY')
312 and to_char(l_effective_end_date, 'YYYY') = to_char(p_effective_date, 'YYYY')
313 then
314
315 hr_utility.set_location(l_proc, 75);
316
317 if l_hours_or_days = 'D' then
318 p_year_to_date_days := p_year_to_date_days + l_screen_entry_value;
319
320 elsif l_hours_or_days = 'H' then
321 p_year_to_date_hours := p_year_to_date_hours + l_screen_entry_value;
322
323 end if;
324
325 end if;
326
327 end loop;
328
329 hr_utility.set_location(l_proc, 80);
330 --
331 -- Null out any irrelevant balances.
332 --
333 if l_hours_or_days = 'D' then
334 p_running_total_hours := null;
335 p_year_to_date_hours := null;
336
337 elsif l_hours_or_days = 'H' then
338 p_running_total_days := null;
339 p_year_to_date_days := null;
340
341 else
342 p_running_total_hours := null;
343 p_year_to_date_hours := null;
344 p_running_total_days := null;
345 p_year_to_date_days := null;
346
347 end if;
348
349 hr_utility.set_location('Leaving:'|| l_proc, 85);
350
351 end get_running_totals;
352 --
353 -- ---------------------------------------------------------------------------
354 -- |---------------------< per_valid_for_absence >-------------------------|
355 -- ---------------------------------------------------------------------------
356 --
357 -- Description:
358 -- This function validates that the person exists and that they have
359 -- a valid period of service or period of placement for the entire absence
360 -- duration.
361 --
362 -- Pre-conditions:
363 -- None.
364 --
365 -- In Arguments:
366 -- p_person_id
367 -- p_business_group_id
368 -- p_person_type
369 -- p_date_projected_start
370 -- p_date_projected_end
371 -- p_date_start
372 -- p_date_end
373 --
374 -- Post Success:
375 -- If validation passes, the function returns TRUE.
376 --
377 -- Post Failure:
378 -- IF validation fails, the function returns FALSE.
379 --
380 -- Access Status:
381 -- Internal Table Handler Use Only. API updating is not required as this
382 -- is called from other chk procedures that use API updating.
383 --
384 -- {End Of Comments}
385 -- ----------------------------------------------------------------------------
386 function per_valid_for_absence
387 (p_person_id in number
388 ,p_business_group_id in number
389 ,p_date_projected_start in date
390 ,p_date_projected_end in date
391 ,p_date_start in date
392 ,p_date_end in date) return boolean
393 is
394
395 --
396 -- Check the person is valid for the entire absence term.
397 -- For contingent workers there is a join to per_all_workforce_v
398 -- so that contingent workers are only included when the profile
399 -- option HR_TREAT_CWK_AS_EMP is Yes.
400 --
401 cursor c_per_valid_for_absence is
402 select null
403 from per_all_people_f ppf,
404 per_periods_of_service pos
405 where ppf.person_id = p_person_id
406 and ppf.person_id = pos.person_id
407 and ppf.current_employee_flag = 'Y'
408 and ((nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'N' and
409 ppf.business_group_id = p_business_group_id)
410 or nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'Y')
411 and (p_date_projected_start is null or p_date_projected_start
412 between pos.date_start and nvl(pos.actual_termination_date,hr_api.g_eot))
413 and (p_date_projected_end is null or p_date_projected_end
414 between pos.date_start and nvl(pos.actual_termination_date,hr_api.g_eot))
415 and (p_date_start is null or p_date_start
416 between pos.date_start and nvl(pos.actual_termination_date,hr_api.g_eot))
417 and (p_date_end is null or p_date_end
418 between pos.date_start and nvl(pos.actual_termination_date,hr_api.g_eot))
419 union select null
420 from per_all_people_f ppf,
421 per_periods_of_placement pop,
422 per_all_workforce_v pawv
423 where ppf.person_id = p_person_id
424 and ppf.person_id = pop.person_id
425 and ppf.person_id = pawv.person_id
426 and ppf.current_npw_flag = 'Y'
427 and ((nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'N' and
428 ppf.business_group_id = p_business_group_id)
429 or nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'Y')
430 and (p_date_projected_start is null or p_date_projected_start
431 between pop.date_start and nvl(pop.actual_termination_date,hr_api.g_eot))
432 and (p_date_projected_end is null or p_date_projected_end
433 between pop.date_start and nvl(pop.actual_termination_date,hr_api.g_eot))
434 and (p_date_start is null or p_date_start
435 between pop.date_start and nvl(pop.actual_termination_date,hr_api.g_eot))
436 and (p_date_end is null or p_date_end
437 between pop.date_start and nvl(pop.actual_termination_date,hr_api.g_eot));
438
439 --
440 l_proc varchar2(72) := g_package||'per_valid_for_absence';
441 l_exists varchar2(1);
442 --
443
444 begin
445
446 hr_utility.set_location('Entering:'|| l_proc, 10);
447
448 --
449 -- Check that the person exists and that they have a valid period of
450 -- service for the entire absence duration.
451 --
452 open c_per_valid_for_absence;
453 fetch c_per_valid_for_absence into l_exists;
454
455 if c_per_valid_for_absence%found then
456 --
457 -- Person is found and they have a valid period of service.
458 --
459 return TRUE;
460 else
461 --
462 -- Person is invalid or the period of service is not valid
463 -- for the duration of the absence.
464 --
465 return FALSE;
466 end if;
467
468 close c_per_valid_for_absence;
469
470 --
471 hr_utility.set_location('Leaving:'|| l_proc, 20);
472
473 end per_valid_for_absence;
474 --
475 -- ---------------------------------------------------------------------------
476 -- |---------------------------< convert_to_minutes >-----------------------|
477 -- ---------------------------------------------------------------------------
478 --
479 -- Description:
480 -- Converts two times into duration minutes.
481 --
482 -- Pre-conditions:
483 --
484 -- In Arguments:
485 -- p_time_start
486 -- p_time_end
487 --
488 -- Post Success:
489 -- The function returns duration minutes and processing continues.
490 --
491 -- Post Failure:
492 -- The function errors and processing stops.
493 --
494 -- Access Status:
495 -- Internal Development Use Only.
496 --
497 -- {End Of Comments}
498 -- ----------------------------------------------------------------------------
499 function convert_to_minutes
500 (p_time_start in varchar2
501 ,p_time_end in varchar2) return number
502 is
503 --
504 l_proc varchar2(72) := g_package||'convert_to_minutes';
505 l_time_duration number;
506
507 cursor c_get_time_duration is
508 select ((substr(p_time_end,1,2) * 60) + substr(p_time_end,4,2)) -
509 ((substr(p_time_start,1,2) * 60) + substr(p_time_start,4,2))
510 from dual;
511 --
512
513 begin
514
515 hr_utility.set_location('Entering:'|| l_proc, 10);
516
517 --
518 -- Calculate the total time in minutes between the start time
519 -- and the end time.
520 --
521 open c_get_time_duration;
522 fetch c_get_time_duration into l_time_duration;
523 close c_get_time_duration;
524
525 hr_utility.set_location(' Leaving:'|| l_proc, 20);
526
527 return l_time_duration;
528
529 end convert_to_minutes;
530 --
531 -- ---------------------------------------------------------------------------
532 -- |---------------------------< chk_time_format >--------------------------|
533 -- ---------------------------------------------------------------------------
534 --
535 -- Description:
536 -- Checks that the time format is valid.
537 --
538 -- Pre-conditions:
539 --
540 -- In Arguments:
541 -- p_time
542 --
543 -- Post Success:
544 -- If the time format is valid, processing continues.
545 --
546 -- Post Failure:
547 -- If the time format is invalid processing stops and an error is raised.
548 --
549 -- Access Status:
550 -- Internal Development Use Only.
551 --
552 -- {End Of Comments}
553 -- ----------------------------------------------------------------------------
554 --
555 procedure chk_time_format
556 (p_time in varchar2)
557 is
558 --
559 l_proc varchar2(72) := g_package||'chk_time_format';
560 --
561
562 begin
563 hr_utility.set_location('Entering:'|| l_proc, 10);
564 --
565
566 if p_time is not null then
567 if not (substr(p_time,1,2) between '00' and '23'
568 and substr(p_time,4,2) between '00' and '59'
569 and substr(p_time,3,1) = ':'
570 and length(p_time) = 5) then
571 fnd_message.set_name('PAY','HR_6004_ALL_FORMAT_HHMM');
572 fnd_message.raise_error;
573 end if;
574 end if;
575
576 --
577 hr_utility.set_location(' Leaving:'|| l_proc, 50);
578 end chk_time_format;
579 --
580 -- +-------------------------------------------------------------------------+
581 -- |-----------------< good_time_format >-------------------------|
582 -- +-------------------------------------------------------------------------+
583 -- Description:
584 -- Tests CHAR values for valid time.
585 --
586 -- Pre-conditions:
587 -- None.
588 --
589 -- In Arguments:
590 -- p_time VARCHAR2
591 --
592 -- Out Arguments:
593 -- BOOLEAN
594 --
595 -- Post Success:
596 -- Returns TRUE or FALSE depending on valid time or not.
597 --
598 -- Post Failure:
599 -- Returns FALSE for invalid time.
600 --
601 -- Access Status:
602 -- Internal Development Use Only.
603 --
604 -- {End Of Comments}
605 -- ----------------------------------------------------------------------------
606 --
607 FUNCTION good_time_format ( p_time IN VARCHAR2 ) RETURN BOOLEAN IS
608 --
609 BEGIN
610 --
611 IF p_time IS NOT NULL THEN
612 --
613 IF NOT (SUBSTR(p_time,1,2) BETWEEN '00' AND '23' AND
614 SUBSTR(p_time,4,2) BETWEEN '00' AND '59' AND
615 SUBSTR(p_time,3,1) = ':' AND
616 LENGTH(p_time) = 5) THEN
617 RETURN FALSE;
618 ELSE
619 RETURN TRUE;
620 END IF;
621 --
622 ELSE
623 RETURN FALSE;
624 END IF;
625 --
626 EXCEPTION
627 --
628 WHEN OTHERS THEN
629 RETURN FALSE;
630 --
631 END good_time_format;
632 --
633 -- +-------------------------------------------------------------------------+
634 -- |-----------------< calc_sch_based_dur >-------------------------|
635 -- +-------------------------------------------------------------------------+
636 -- Description:
637 -- Calculate the absence duration in hours/days based on the work schedule.
638 --
639 -- Pre-conditions:
640 -- None.
641 --
642 -- In Arguments:
643 -- p_days_or_hours VARCHAR2
644 -- p_date_start DATE
645 -- p_date_end DATE
646 -- p_time_start VARCHAR2
647 -- p_time_end VARCHAR2
648 -- p_assignment_id NUMBER
649 --
650 -- Out Arguments:
651 -- p_duration NUMBER
652 --
653 -- Post Success:
654 -- Value returned for absence duration.
655 --
656 -- Post Failure:
657 -- If a failure occurs, an application error is raised and
658 -- processing terminates.
659 --
660 -- Access Status:
661 -- Internal Development Use Only.
662 --
663 -- {End Of Comments}
664 -- ----------------------------------------------------------------------------
665 --
666 PROCEDURE calc_sch_based_dur ( p_days_or_hours IN VARCHAR2,
667 p_date_start IN DATE,
668 p_date_end IN DATE,
669 p_time_start IN VARCHAR2,
670 p_time_end IN VARCHAR2,
671 p_assignment_id IN NUMBER,
672 p_duration IN OUT NOCOPY NUMBER
673 ) IS
674 --
675 l_idx NUMBER;
676 l_ref_date DATE;
677 l_first_band BOOLEAN;
678 l_day_start_time VARCHAR2(5);
679 l_day_end_time VARCHAR2(5);
680 l_start_time VARCHAR2(5);
681 l_end_time VARCHAR2(5);
682 --
683 l_start_date DATE;
684 l_end_date DATE;
685 l_schedule cac_avlblty_time_varray;
686 l_schedule_source VARCHAR2(10);
687 l_return_status VARCHAR2(1);
688 l_return_message VARCHAR2(2000);
689 --
690 l_time_start VARCHAR2(5);
691 l_time_end VARCHAR2(5);
692 --
693 e_bad_time_format EXCEPTION;
694 --
695 BEGIN
696 hr_utility.set_location('Entering '||g_package||'.calc_sch_based_dur',10);
697 p_duration := 0;
698 l_time_start := p_time_start;
699 l_time_end := p_time_end;
700 --
701 IF l_time_start IS NULL THEN
702 l_time_start := '00:00';
703 ELSE
704 IF NOT good_time_format(l_time_start) THEN
705 RAISE e_bad_time_format;
706 END IF;
707 END IF;
708 /*
709 IF l_time_end IS NULL THEN
710 l_time_end := '23:59'; --changed for bug #6274821
711 ELSE
712 IF NOT good_time_format(l_time_end) THEN
713 RAISE e_bad_time_format;
714 END IF;
715 END IF;
716 */
717 -- fix for the bug 6711896
718 IF l_time_end IS NULL THEN
719
720 IF p_days_or_hours = 'D' THEN
721 l_time_end := '00:00';
722 else
723 l_time_end := '23:59';
724 -- l_time_end := '00:00';
725 END IF;
726
727 ELSE
728 IF NOT good_time_format(l_time_end) THEN
729 RAISE e_bad_time_format;
730 END IF;
731 END IF;
732 --fix for the bug 6711896
733 l_start_date := TO_DATE(TO_CHAR(p_date_start,'DD-MM-YYYY')||' '||l_time_start,'DD-MM-YYYY HH24:MI');
734 l_end_date := TO_DATE(TO_CHAR(p_date_end,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
735 IF p_days_or_hours = 'D' THEN
736 l_end_date := l_end_date + 1;
737 END IF;
738 --
739 -- Fetch the work schedule
740 --
741 hr_wrk_sch_pkg.get_per_asg_schedule
742 ( p_person_assignment_id => p_assignment_id
743 , p_period_start_date => l_start_date
744 , p_period_end_date => l_end_date
745 , p_schedule_category => NULL
746 , p_include_exceptions => 'Y'
747 , p_busy_tentative_as => 'FREE'
748 , x_schedule_source => l_schedule_source
749 , x_schedule => l_schedule
750 , x_return_status => l_return_status
751 , x_return_message => l_return_message
752 );
753 --
754 IF l_return_status = '0' THEN
755 --
756 -- Calculate duration
757 --
758 l_idx := l_schedule.first;
759 --
760 IF p_days_or_hours = 'D' THEN
761 --
762 l_first_band := TRUE;
763 l_ref_date := NULL;
764 WHILE l_idx IS NOT NULL
765 LOOP
766 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
767 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
768 IF l_first_band THEN
769 l_first_band := FALSE;
770 l_ref_date := TRUNC(l_schedule(l_idx).START_DATE_TIME);
771 IF (TRUNC(l_schedule(l_idx).END_DATE_TIME) = TRUNC(l_schedule(l_idx).START_DATE_TIME)) THEN
772 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
773 ELSE
774 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
775 END IF;
776 ELSE -- not first time
777 IF TRUNC(l_schedule(l_idx).START_DATE_TIME) = l_ref_date THEN
778 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
779 ELSE
780 l_ref_date := TRUNC(l_schedule(l_idx).END_DATE_TIME);
781 IF (TRUNC(l_schedule(l_idx).END_DATE_TIME) = TRUNC(l_schedule(l_idx).START_DATE_TIME)) THEN
782 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
783 ELSE
784 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
785 END IF;
786 END IF;
787 END IF;
788 END IF;
789 END IF;
790 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
791 END LOOP;
792 --
793 ELSE -- p_days_or_hours is 'H'
794 --
795 l_day_start_time := '00:00';
796 l_day_end_time := '23:59';
797 WHILE l_idx IS NOT NULL
798 LOOP
799 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
800 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
801 IF l_schedule(l_idx).END_DATE_TIME < l_schedule(l_idx).START_DATE_TIME THEN
802 -- Skip this invalid slot which ends before it starts
803 NULL;
804 ELSE
805 IF TRUNC(l_schedule(l_idx).END_DATE_TIME) > TRUNC(l_schedule(l_idx).START_DATE_TIME) THEN
806 -- Start and End on different days
807 --
808 -- Get first day hours
809 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
810 SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
811 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
812 INTO p_duration
813 FROM DUAL;
814 --
815 -- Get last day hours
816 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
817 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
818 (SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
819 INTO p_duration
820 FROM DUAL;
821 --
822 -- Get between full day hours
823 SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
824 INTO p_duration
825 FROM DUAL;
826 ELSE
827 -- Start and End on same day
828 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
829 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
830 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
831 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
832 INTO p_duration
833 FROM DUAL;
834 END IF;
835 END IF;
836 END IF;
837 END IF;
838 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
839 END LOOP;
840 p_duration := ROUND(p_duration,2);
841 --
842 END IF;
843 END IF;
844 --
845 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',20);
846 EXCEPTION
847 --
848 WHEN e_bad_time_format THEN
849 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',30);
850 hr_utility.set_location(SQLERRM,35);
851 RAISE;
852 --
853 WHEN OTHERS THEN
854 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',40);
855 hr_utility.set_location(SQLERRM,45);
856 RAISE;
857 --
858 END calc_sch_based_dur;
859 --
860 -- ---------------------------------------------------------------------------
861 -- |-----------------< calculate_absence_duration >-------------------------|
862 -- ---------------------------------------------------------------------------
863 --
864 -- Description:
865 -- Calculates the absence duration in hours and / or days and sets
866 -- the duration.
867 --
868 -- Pre-conditions:
869 -- None.
870 --
871 -- In Arguments:
872 -- p_absence_attendance_id
873 -- p_absence_attendance_type_id
874 -- p_business_group_id
875 -- p_object_version_number
876 -- p_effective_date
877 -- p_person_id
878 -- p_date_start
879 -- p_date_end
880 -- p_time_start
881 -- p_time_end
882 --
883 -- Out Arguments:
884 -- p_absence_days
885 -- p_absence_hours
886 -- p_use_formula
887 --
888 -- Post Success:
889 -- The absence duration in days and hours is returned.
890 --
891 -- Post Failure:
892 -- If a failure occurs, an application error is raised and
893 -- processing terminates.
894 --
895 -- Access Status:
896 -- Internal Development Use Only.
897 --
898 -- {End Of Comments}
899 -- ----------------------------------------------------------------------------
900 --
901 procedure calculate_absence_duration
902 (p_absence_attendance_id in number
903 ,p_absence_attendance_type_id in number
904 ,p_business_group_id in number
905 ,p_object_version_number in number
906 ,p_effective_date in date
907 ,p_person_id in number
908 ,p_date_start in date
909 ,p_date_end in date
910 ,p_time_start in varchar2
911 ,p_time_end in varchar2
912 ,p_absence_days out nocopy number
913 ,p_absence_hours out nocopy number
914 ,p_use_formula out nocopy boolean)
915 is
916
917 l_proc varchar2(72) := g_package||
918 'calculate_absence_duration';
919 l_exists varchar2(1);
920 l_api_updating boolean;
921 l_assignment_id number;
922 l_hours_or_days varchar2(1);
923 l_element_type_id number;
924 l_legislation_code varchar2(150);
925 l_formula_id number;
926 l_inputs ff_exec.inputs_t;
927 l_outputs ff_exec.outputs_t;
928 l_user_message varchar2(1) := 'N';
929 l_invalid_message fnd_new_messages.message_text%TYPE;
930
931 /*Added for the bug 6790565 - starts here*/
932 l_invalid_message_txt fnd_new_messages.message_text%TYPE;
933 l_invalid_message_num number;
934 /*Added for the bug 6790565 - ends here*/
935
936 wrong_parameters exception;
937 l_normal_time_start varchar2(5);
938 l_normal_time_end varchar2(5);
939 l_normal_day_minutes number;
940 l_first_day_minutes number;
941 l_last_day_minutes number;
942 l_same_day_minutes number;
943 l_absence_days number;
944 l_absence_hours number;
945
946 -- For schedule based calculation
947 l_sch_based_dur VARCHAR2(1);
948 l_sch_based_dur_found BOOLEAN;
949 l_absence_duration NUMBER;
950
951 --3093970 starts here. comment out the code introduced by 2820155.
952 --2820155 change starts
953 -- l_eff_time_start varchar2(5);
954 -- l_eff_time_end varchar2(5);
955 --2820155 change ends
956 --3093970 ends here.
957
958 cursor c_get_absence_info is
959 select abt.hours_or_days,
960 piv.element_type_id
961 from per_absence_attendance_types abt,
962 pay_input_values_f piv
963 where abt.absence_attendance_type_id = p_absence_attendance_type_id
964 and abt.input_value_id = piv.input_value_id(+);
965 --
966 cursor c_get_normal_hours (p_assignment_id in number) is
967 select nvl(nvl(asg.time_normal_start, pbg.default_start_time), '00:00'),
968 nvl(nvl(asg.time_normal_finish, pbg.default_end_time), '23:59')
969 FROM per_all_assignments_f asg,
970 per_business_groups pbg
971 WHERE asg.assignment_id = p_assignment_id
972 AND asg.business_group_id = pbg.business_group_id
973 AND p_effective_date between asg.effective_start_date
974 and asg.effective_end_date;
975
976 --
977 begin
978
979 hr_utility.set_location('Entering:'|| l_proc, 10);
980 --
981 -- Only proceed with validation if :
982 -- a) The current g_old_rec is current and
983 -- b) The date values have changed
984 --
985 l_api_updating := per_abs_shd.api_updating
986 (p_absence_attendance_id => p_absence_attendance_id
987 ,p_object_version_number => p_object_version_number);
988 --
989 if (l_api_updating
990 and nvl(per_abs_shd.g_old_rec.date_start, hr_api.g_date)
991 = nvl(p_date_start, hr_api.g_date)
992 and nvl(per_abs_shd.g_old_rec.date_end, hr_api.g_date)
993 = nvl(p_date_end, hr_api.g_date)
994 and nvl(per_abs_shd.g_old_rec.time_start, hr_api.g_varchar2)
995 = nvl(p_time_start, hr_api.g_varchar2)
996 and nvl(per_abs_shd.g_old_rec.time_end, hr_api.g_varchar2)
997 = nvl(p_time_end, hr_api.g_varchar2)
998 and nvl(per_abs_shd.g_old_rec.absence_days, hr_api.g_number)
999 = nvl(p_absence_days, hr_api.g_number)
1000 and nvl(per_abs_shd.g_old_rec.absence_hours, hr_api.g_number)
1001 = nvl(p_absence_hours, hr_api.g_number)) then
1002 return;
1003 end if;
1004
1005 hr_utility.set_location(l_proc, 15);
1006
1007 --
1008 -- See if a Fast Formula exists. Here the Fast Formula names
1009 -- are hard-coded. Fast Formulas with these exact names can
1010 -- be defined at one of three levels to default the absence
1011 -- duration:
1012 --
1013 -- 1. Business group (customer-definable)
1014 -- 2. Legislation (Oracle internal legislation-specific)
1015 -- 3. Core (Oracle internal core product)
1016 --
1017
1018 --
1019 -- Get the varous additional values that are required for use later.
1020 --
1021
1022 l_assignment_id := hr_person_absence_api.get_primary_assignment
1023 (p_person_id => p_person_id
1024 ,p_effective_date => p_effective_date);
1025
1026 l_legislation_code := hr_api.return_legislation_code
1027 (p_business_group_id => p_business_group_id);
1028
1029 open c_get_absence_info;
1030 fetch c_get_absence_info into l_hours_or_days,
1031 l_element_type_id;
1032 close c_get_absence_info;
1033
1034 l_sch_based_dur := NVL(FND_PROFILE.Value('HR_SCH_BASED_ABS_CALC'),'N');
1035 l_sch_based_dur_found := FALSE;
1036 --
1037 IF l_sch_based_dur = 'Y' THEN
1038 --
1039 hr_utility.set_location(l_proc, 16);
1040 p_use_formula := TRUE; -- set to display
1041 --
1042 calc_sch_based_dur (p_days_or_hours => l_hours_or_days,
1043 p_date_start => p_date_start,
1044 p_date_end => p_date_end,
1045 p_time_start => p_time_start,
1046 p_time_end => p_time_end,
1047 p_assignment_id => l_assignment_id,
1048 p_duration => l_absence_duration
1049 );
1050 --
1051 IF l_absence_duration IS NOT NULL THEN
1052 --
1053 l_sch_based_dur_found := TRUE;
1054 --
1055 IF l_hours_or_days = 'H' THEN
1056 hr_utility.set_location(l_proc, 17);
1057 p_absence_hours := l_absence_duration;
1058 ELSIF l_hours_or_days = 'D' THEN
1059 hr_utility.set_location(l_proc, 18);
1060 p_absence_days := l_absence_duration;
1061 ELSE
1062 hr_utility.set_location(l_proc, 19);
1063 l_sch_based_dur_found := FALSE;
1064 END IF;
1065 --
1066 END IF;
1067 --
1068 END IF; -- sch_based_dur is 'Y'
1069
1070 IF l_sch_based_dur <> 'Y' OR (l_sch_based_dur = 'Y' AND NOT l_sch_based_dur_found) THEN
1071 --
1072 hr_utility.set_location(l_proc, 20);
1073
1074 begin
1075 --
1076 -- Look for a customer-defined formula
1077 --
1078 select ff.formula_id
1079 into l_formula_id
1080 from ff_formulas_f ff
1081 where ff.formula_name = 'BG_ABSENCE_DURATION'
1082 and ff.business_group_id = p_business_group_id
1083 and p_effective_date between ff.effective_start_date and
1084 ff.effective_end_date;
1085 exception
1086
1087 when no_data_found then
1088 --
1089 -- There is no customer defined formula so look for
1090 -- a legislative formula.
1091 --
1092 begin
1093
1094 hr_utility.set_location(l_proc, 25);
1095
1096 select ff.formula_id
1097 into l_formula_id
1098 from ff_formulas_f ff
1099 where ff.formula_name = 'LEGISLATION_ABSENCE_DURATION'
1100 and ff.legislation_code = l_legislation_code
1101 and ff.business_group_id is null
1102 and p_effective_date between ff.effective_start_date and
1103 ff.effective_end_date;
1104
1105 exception
1106
1107 when no_data_found then
1108 --
1109 -- If none of the two above then select the core formula
1110 --
1111 begin
1112
1113 hr_utility.set_location(l_proc, 30);
1114
1115 select ff.formula_id
1116 into l_formula_id
1117 from ff_formulas_f ff
1118 where ff.formula_name = 'CORE_ABSENCE_DURATION'
1119 and ff.legislation_code is null
1120 and ff.business_group_id is null
1121 and p_effective_date between ff.effective_start_date and
1122 ff.effective_end_date;
1123
1124 exception
1125
1126 when no_data_found then
1127 --
1128 -- No formula is found. We capture the error and do nothing.
1129 --
1130 null;
1131
1132 end;
1133 end;
1134 end;
1135
1136 hr_utility.set_location(l_proc, 35);
1137
1138 if l_formula_id is not null then
1139 --
1140 -- An absence duration Fast Formula should be used so the
1141 -- formula is called. First, the formula is initialised.
1142 --
1143 p_use_formula := TRUE;
1144
1145 hr_utility.set_location(l_proc, 40);
1146
1147 --
1148 -- Initalise the formula.
1149 --
1150 ff_exec.init_formula
1151 (p_formula_id => l_formula_id
1152 ,p_effective_date => p_effective_date
1153 ,p_inputs => l_inputs
1154 ,p_outputs => l_outputs);
1155
1156 hr_utility.set_location(l_proc, 45);
1157
1158 --
1159 -- Assign the inputs.
1160 --
1161 for i_input in l_inputs.first..l_inputs.last
1162 loop
1163
1164 if l_inputs(i_input).name = 'DAYS_OR_HOURS' then
1165 l_inputs(i_input).value := l_hours_or_days;
1166 elsif l_inputs(i_input).name = 'DATE_START' then
1167 l_inputs(i_input).value := fnd_date.date_to_canonical(p_date_start);
1168 elsif l_inputs(i_input).name = 'DATE_END' then
1169 l_inputs(i_input).value := fnd_date.date_to_canonical(p_date_end);
1170 elsif l_inputs(i_input).name = 'TIME_START' then
1171 l_inputs(i_input).value := p_time_start;
1172 elsif l_inputs(i_input).name = 'TIME_END' then
1173 l_inputs(i_input).value := p_time_end;
1174 elsif l_inputs(i_input).name = 'DATE_EARNED' then
1175 l_inputs(i_input).value := fnd_date.date_to_canonical
1176 (p_effective_date);
1177 elsif l_inputs(i_input).name = 'BUSINESS_GROUP_ID' then
1178 l_inputs(i_input).value := p_business_group_id;
1179 elsif l_inputs(i_input).name = 'LEGISLATION_CODE' then
1180 l_inputs(i_input).value := l_legislation_code;
1181 elsif l_inputs(i_input).name = 'ASSIGNMENT_ID' then
1182 l_inputs(i_input).value := l_assignment_id;
1183 elsif l_inputs(i_input).name = 'ELEMENT_TYPE_ID' then
1184 l_inputs(i_input).value := l_element_type_id;
1185 elsif l_inputs(i_input).name = 'ABSENCE_ATTENDANCE_TYPE_ID' then
1186 l_inputs(i_input).value := p_absence_attendance_type_id;
1187 else
1188 raise wrong_parameters;
1189 end if;
1190
1191 end loop;
1192
1193 hr_utility.set_location(l_proc, 50);
1194
1195 --
1196 -- Run the formula.
1197 --
1198 ff_exec.run_formula(l_inputs, l_outputs);
1199
1200 hr_utility.set_location(l_proc, 55);
1201
1202 --
1203 -- Assign the outputs.
1204 --
1205 for i_output in l_outputs.first..l_outputs.last
1206 loop
1207
1208 if l_outputs(i_output).name = 'DURATION' then
1209
1210 if l_outputs(i_output).value = 'FAILED' then
1211 l_user_message := 'Y';
1212 else
1213 --
1214 -- The absence hours / days out parameter is set. If no UOM
1215 -- is set but the start or end time have been entered, the output
1216 -- is returned in hours.
1217 --
1218 if l_hours_or_days = 'H'
1219 or (p_time_start is not null and p_time_end is not null) then
1220 p_absence_hours := round(fnd_number.canonical_to_number(l_outputs(i_output).value),2);
1221 else
1222 p_absence_days := round(fnd_number.canonical_to_number(l_outputs(i_output).value),2);
1223 end if;
1224 end if;
1225
1226 elsif l_outputs(i_output).name = 'INVALID_MSG' then
1227 --
1228 -- Here any customer-defined messages are set and
1229 -- raised after this loop.
1230 --
1231 l_invalid_message := l_outputs(i_output).value;
1232
1233 null;
1234 else
1235 raise wrong_parameters;
1236 end if;
1237
1238 end loop;
1239
1240 hr_utility.set_location(l_proc, 60);
1241 hr_utility.trace('l_user_message: '||l_user_message);
1242 hr_utility.trace('l_invalid_message: '||l_invalid_message);
1243
1244
1245 /*Added for the bug 6790565 - starts here*/
1246 -- Displays the error message text if text is given
1247 -- directly in the fast formula
1248 -- Displays the error message text if the error message
1249 -- name is given
1250 -- This is done as SSHR supports both text as well as name
1251 select instr(l_invalid_message,' ',1,1)
1252 into l_invalid_message_num from dual;
1253
1254 if l_invalid_message_num = 0
1255 then
1256 l_invalid_message_txt := fnd_message.get_string('PER',l_invalid_message);
1257 else
1258 l_invalid_message_txt := l_invalid_message;
1259 end if;
1260
1261 /*Added for the bug 6790565 - ends here*/
1262 --
1263 -- If the Fast Formula raises a user-defined error message,
1264 -- raise the error back to the user.
1265 --
1266 if l_user_message = 'Y' then
1267 -- Start of fix 3553741
1268 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1269 /*Added for the bug 6790565 - starts here*/
1270 -- hr_utility.set_message_token('FORMULA_TEXT', l_invalid_message);
1271 hr_utility.set_message_token('FORMULA_TEXT', l_invalid_message_txt);
1272 /*Added for the bug 6790565 - ends here*/
1273 hr_utility.raise_error;
1274 -- End of fix 3553741
1275 end if;
1276
1277 hr_utility.set_location(l_proc, 63);
1278
1279 else
1280 --
1281 -- No formula could be located so we calculate based on the
1282 -- standard hours of the assignment or business group.
1283 --
1284 p_use_formula := FALSE;
1285
1286 hr_utility.set_location(l_proc, 65);
1287
1288 --
1289 -- Get the default start and end times. First check the assignment, then
1290 -- the business group. If neither of these, assume 24 hours a day.
1291 --
1292 open c_get_normal_hours (l_assignment_id);
1293 fetch c_get_normal_hours into l_normal_time_start,
1294 l_normal_time_end;
1295 close c_get_normal_hours;
1296
1297 -- #2734822: verify the time format
1298
1299 hr_dbchkfmt.is_db_format(p_value => l_normal_time_start
1300 ,p_formatted_output => l_normal_time_start
1301 ,p_arg_name => 'time_normal_start'
1302 ,p_format => 'TIMES');
1303
1304
1305 hr_dbchkfmt.is_db_format(p_value => l_normal_time_end
1306 ,p_formatted_output => l_normal_time_end
1307 ,p_arg_name => 'time_normal_finish'
1308 ,p_format => 'TIMES');
1309
1310 -- end #2734822
1311
1312 hr_utility.set_location(l_proc, 70);
1313
1314 --
1315 -- Calculate the number of minutes in each day.
1316 --
1317 -- 3093970 starts here. comment out code introduced by 2820155.
1318 --2820155 changes start
1319 -- Start time and end time should be adjusted to fall between
1320 -- normal start time and end time.
1321 /*
1322 l_eff_time_start :=p_time_start;
1323 l_eff_time_end :=p_time_end;
1324
1325 IF (
1326 ( (substr(p_time_start,1,2) * 60) + substr(p_time_start,4,2)) <
1327 ( (substr(l_normal_time_start,1,2) * 60) + substr(l_normal_time_start,4,2))
1328 ) THEN
1329 l_eff_time_start := l_normal_time_start;
1330
1331 END IF;
1332
1333 IF (
1334 ( (substr(p_time_start,1,2) * 60) + substr(p_time_start,4,2)) >
1335 ( (substr(l_normal_time_end,1,2) * 60) + substr(l_normal_time_end,4,2))
1336 ) THEN
1337 l_eff_time_start := l_normal_time_end;
1338
1339 END IF;
1340
1341
1342 IF (
1343 ( (substr(p_time_end,1,2) * 60) + substr(p_time_end,4,2)) <
1344 ( (substr(l_normal_time_start,1,2) * 60) + substr(l_normal_time_start,4,2))
1345 ) THEN
1346 l_eff_time_end := l_normal_time_start;
1347
1348 END IF;
1349
1350
1351 IF (
1352 ( (substr(p_time_end,1,2) * 60) + substr(p_time_end,4,2)) >
1353 ( (substr(l_normal_time_end,1,2) * 60) + substr(l_normal_time_end,4,2))
1354 ) THEN
1355 l_eff_time_end := l_normal_time_end;
1356
1357 END IF;
1358
1359 -- Pass effective start and end time instead of received start and end time
1360
1361 l_normal_day_minutes := convert_to_minutes(l_normal_time_start,
1362 l_normal_time_end);
1363 l_first_day_minutes := convert_to_minutes(nvl(l_eff_time_start,
1364 l_normal_time_start),
1365 l_normal_time_end);
1366 l_last_day_minutes := convert_to_minutes(l_normal_time_start,
1367 nvl(l_eff_time_end,
1368 l_normal_time_end));
1369 l_same_day_minutes := convert_to_minutes(nvl(l_eff_time_start,
1370 l_normal_time_start),
1371 nvl(l_eff_time_end,
1372 l_normal_time_end));
1373
1374 --2820155 changes end
1375 */
1376 --
1377 -- Calculate the number of minutes in each day.
1378 --
1379 l_normal_day_minutes := convert_to_minutes(l_normal_time_start,
1380 l_normal_time_end);
1381 l_first_day_minutes := convert_to_minutes(nvl(p_time_start,
1382 l_normal_time_start),
1383 l_normal_time_end);
1384 l_last_day_minutes := convert_to_minutes(l_normal_time_start,
1385 nvl(p_time_end,
1386 l_normal_time_end));
1387 --
1388 -- Bug3093970 starts here.
1389 --
1390 if l_first_day_minutes <= 0 OR l_first_day_minutes > l_normal_day_minutes
1391 OR l_last_day_minutes <= 0 OR l_last_day_minutes > l_normal_day_minutes THEN
1392 --
1393 -- The leave timings are out off the standard timings.
1394 -- So use 24 hours rule to calculate the first day and last day minutes.
1395 --
1396 hr_utility.set_location(l_proc, 72);
1397 l_first_day_minutes := convert_to_minutes(nvl(p_time_start,
1398 l_normal_time_start),
1399 '24:00');
1400 l_last_day_minutes := convert_to_minutes('00:00', nvl(p_time_end,
1401 l_normal_time_end));
1402
1403 end if;
1404 --
1405 -- Bug3093970 ends here.
1406 --
1407 l_same_day_minutes := convert_to_minutes(nvl(p_time_start,
1408 l_normal_time_start),
1409 nvl(p_time_end,
1410 l_normal_time_end));
1411 --2943479 changes start
1412 if l_normal_time_end = '23:59'
1413 then
1414 l_normal_day_minutes := l_normal_day_minutes +1;
1415 l_first_day_minutes := l_first_day_minutes +1;
1416 --3075512 changes start
1417 if (p_time_end is null or p_time_end = '') then
1418 l_last_day_minutes := l_last_day_minutes +1;
1419 l_same_day_minutes := l_same_day_minutes +1;
1420 end if;
1421 --3075512 changes end
1422 end if;
1423 --2943479 changes end
1424
1425 hr_utility.trace('Normal Day Minutes: ' || to_char(l_normal_day_minutes));
1426 hr_utility.trace('First Day Minutes: ' || to_char(l_first_day_minutes));
1427 hr_utility.trace('Last Day Minutes: ' || to_char(l_last_day_minutes));
1428 hr_utility.trace('Same Day Minutes: ' || to_char(l_same_day_minutes));
1429
1430 hr_utility.set_location(l_proc, 75);
1431
1432 --
1433 -- Calculate the absence days.
1434 --
1435 l_absence_days := (p_date_end - p_date_start) + 1;
1436
1437 hr_utility.trace('Absence Days: ' || to_char(l_absence_days));
1438
1439 --
1440 -- Calculate the absence hours.
1441 --
1442 if l_absence_days = 1 then
1443 --
1444 -- The absence starts and ends on the same day.
1445 --
1446 l_absence_hours := l_same_day_minutes / 60;
1447
1448 elsif l_absence_days = 2 then
1449 --
1450 -- The absence ends the day after another.
1451 --
1452 l_absence_hours := (l_first_day_minutes + l_last_day_minutes) / 60;
1453
1454 else
1455 --
1456 -- The absence is n number of days.
1457 --
1458 l_absence_hours := (l_first_day_minutes + l_last_day_minutes +
1459 ((l_absence_days - 2) * l_normal_day_minutes)) / 60;
1460
1461 end if;
1462
1463 hr_utility.set_location(l_proc, 80);
1464
1465 --
1466 -- Check that the absence hours are not less than zero. This could
1467 -- happen if the entered start time is after the normal start time or
1468 -- the entered end time is after the normal end time.
1469 --
1470 If l_absence_hours < 0 then
1471 l_absence_hours := 0;
1472 end if;
1473
1474 --
1475 -- Set the absence days and hours out parameters.
1476 --
1477 if l_hours_or_days = 'H' then
1478 -- Start of fix 3156665
1479 /* If the standard working hours is not defined at Assignment or
1480 Organization level, then system will take the default start time
1481 as 00:00 and end time as 23:59. Duration of this times will reach
1482 upto 23.983333' only. So if system is using default timings then
1483 rounding the calculated hours to get the default as 24 hours.
1484 Else rounding with 2 decimal places.
1485 */
1486 --
1487 --p_absence_hours := round(l_absence_hours,2);
1488 if p_time_start = '00:00' and p_time_end = '23:59' then
1489 p_absence_hours := round(l_absence_hours);
1490 else
1491 p_absence_hours := round(l_absence_hours,2);
1492 end if;
1493 -- End of fix 3156665
1494 elsif l_hours_or_days = 'D' then
1495 p_absence_days := round(l_absence_days,2);
1496
1497 else
1498 p_absence_hours := round(l_absence_hours,2);
1499 p_absence_days := round(l_absence_days,2);
1500
1501 end if;
1502
1503 end if;
1504
1505 END IF; -- Schedule based calculation not used
1506
1507 hr_utility.set_location(' Leaving:'|| l_proc, 85);
1508
1509 exception
1510
1511 when wrong_parameters then
1512 --
1513 -- The inputs / outputs of the Fast Formula are incorrect
1514 -- so raise an error.
1515 --
1516 hr_utility.set_location(l_proc, 90);
1517
1518 hr_utility.set_message(800,'HR_34964_BAD_FF_DEFINITION');
1519 hr_utility.raise_error;
1520
1521 end calculate_absence_duration;
1522 --
1523 -- ---------------------------------------------------------------------------
1524 -- |---------------------------< chk_person_id >----------------------------|
1525 -- ---------------------------------------------------------------------------
1526 --
1527 -- Description:
1528 -- Validates that the person exists, that they have a valid period of
1529 -- service and that they match the business group id being passed.
1530 --
1531 -- Pre-conditions:
1532 --
1533 -- In Arguments:
1534 -- p_absence_attendance_id
1535 -- p_person_id
1536 -- p_business_group_id
1537 --
1538 -- Post Success:
1539 -- If the person and their period of service are valid, processing
1540 -- continues.
1541 --
1542 -- Post Failure:
1543 -- An application error will be raised and processing is terminated.
1544 --
1545 -- Access Status:
1546 -- Internal Development Use Only.
1547 --
1548 -- {End Of Comments}
1549 -- ----------------------------------------------------------------------------
1550 --
1551 procedure chk_person_id
1552 (p_absence_attendance_id in number
1553 ,p_person_id in number
1554 ,p_business_group_id in number
1555 ,p_object_version_number in number
1556 ,p_date_projected_start in date
1557 ,p_date_projected_end in date
1558 ,p_date_start in date
1559 ,p_date_end in date)
1560 is
1561
1562 --
1563 l_proc varchar2(72) := g_package||'chk_person_id';
1564 l_api_updating boolean;
1565 --
1566
1567 begin
1568
1569 hr_utility.set_location('Entering:'|| l_proc, 10);
1570 --
1571 hr_api.mandatory_arg_error
1572 (p_api_name => l_proc
1573 ,p_argument => 'p_person_id'
1574 ,p_argument_value => p_person_id
1575 );
1576 hr_api.mandatory_arg_error
1577 (p_api_name => l_proc
1578 ,p_argument => 'p_business_group_id'
1579 ,p_argument_value => p_business_group_id
1580 );
1581
1582 --
1583 -- Only proceed with validation if :
1584 -- a) The current g_old_rec is current and
1585 -- b) The date values have changed
1586 --
1587 l_api_updating := per_abs_shd.api_updating
1588 (p_absence_attendance_id => p_absence_attendance_id
1589 ,p_object_version_number => p_object_version_number);
1590 --
1591 if (l_api_updating
1592 and nvl(per_abs_shd.g_old_rec.date_projected_start, hr_api.g_date)
1593 = nvl(p_date_projected_start, hr_api.g_date)
1594 and nvl(per_abs_shd.g_old_rec.date_projected_end, hr_api.g_date)
1595 = nvl(p_date_projected_end, hr_api.g_date)
1596 and nvl(per_abs_shd.g_old_rec.date_start, hr_api.g_date)
1597 = nvl(p_date_start, hr_api.g_date)
1598 and nvl(per_abs_shd.g_old_rec.date_end, hr_api.g_date)
1599 = nvl(p_date_end, hr_api.g_date)) then
1600 return;
1601 end if;
1602
1603 --
1604 -- Check that the person exists and that their period of service
1605 -- is valid for the entire absence duration.
1606 --
1607 if not per_valid_for_absence
1608 (p_person_id => p_person_id
1609 ,p_business_group_id => p_business_group_id
1610 ,p_date_projected_start => p_date_projected_start
1611 ,p_date_projected_end => p_date_projected_end
1612 ,p_date_start => p_date_start
1613 ,p_date_end => p_date_end)
1614 then
1615
1616 fnd_message.set_name('PER', 'PER_7715_ABS_TERM_PROJ_DATE');
1617 fnd_message.raise_error;
1618
1619 end if;
1620
1621 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1622
1623 end chk_person_id;
1624 --
1625 -- ---------------------------------------------------------------------------
1626 -- |-----------------< chk_absence_attendance_type_id >---------------------|
1627 -- ---------------------------------------------------------------------------
1628 --
1629 -- Description:
1630 -- Validates that the absence_attendance_type_id exists in
1631 -- per_absence_attendance_types for the same business group and that it
1632 -- is effective for the entire absence duration.
1633 --
1634 -- Pre-conditions:
1635 -- None.
1636 --
1637 -- In Arguments:
1638 -- p_absence_attendance_id
1639 -- p_business_group_id
1640 -- p_absence_attendance_type_id
1641 -- p_object_version_number
1642 --
1643 -- Post Success:
1644 -- If absence_attendance_type_id exists and is valid,
1645 -- processing continues.
1646 --
1647 -- Post Failure:
1648 -- If absence_attendance_type_id is invalid,
1649 -- an application error is raised and processing terminates.
1650 --
1651 -- Access Status:
1652 -- Internal Development Use Only.
1653 --
1654 -- {End Of Comments}
1655 -- ----------------------------------------------------------------------------
1656 --
1657 procedure chk_absence_attendance_type_id
1658 (p_absence_attendance_id in number
1659 ,p_absence_attendance_type_id in number
1660 ,p_business_group_id in number
1661 ,p_object_version_number in number
1662 ,p_date_projected_start in date
1663 ,p_date_projected_end in date
1664 ,p_date_start in date
1665 ,p_date_end in date)
1666 is
1667
1668 l_exists varchar2(1);
1669 l_proc varchar2(72) := g_package||'chk_absence_attendance_type_id';
1670 l_api_updating boolean;
1671 --
1672
1673 cursor c_absence_within_type_dates is
1674 select null
1675 from per_absence_attendance_types abt
1676 where abt.absence_attendance_type_id = p_absence_attendance_type_id
1677 and abt.business_group_id = p_business_group_id
1678 and (p_date_projected_start is null or p_date_projected_start
1679 between abt.date_effective and nvl(abt.date_end,hr_api.g_eot))
1680 and (p_date_projected_end is null or p_date_projected_end
1681 between abt.date_effective and nvl(abt.date_end,hr_api.g_eot))
1682 and (p_date_start is null or p_date_start
1683 between abt.date_effective and nvl(abt.date_end,hr_api.g_eot))
1684 and (p_date_end is null or p_date_end
1685 between abt.date_effective and nvl(abt.date_end,hr_api.g_eot));
1686
1687 --
1688 begin
1689
1690 hr_utility.set_location('Entering:'|| l_proc, 10);
1691 --
1692 --
1693 -- Check mandatory parameters have been set
1694 --
1695 hr_api.mandatory_arg_error
1696 (p_api_name => l_proc
1697 ,p_argument => 'p_absence_attendance_type_id'
1698 ,p_argument_value => p_absence_attendance_type_id
1699 );
1700
1701 --
1702 -- Only proceed with validation if :
1703 -- a) The current g_old_rec is current and
1704 -- b) The date values have changed
1705 --
1706 l_api_updating := per_abs_shd.api_updating
1707 (p_absence_attendance_id => p_absence_attendance_id
1708 ,p_object_version_number => p_object_version_number);
1709 --
1710 if (l_api_updating
1711 and nvl(per_abs_shd.g_old_rec.date_projected_start, hr_api.g_date)
1712 = nvl(p_date_projected_start, hr_api.g_date)
1713 and nvl(per_abs_shd.g_old_rec.date_projected_end, hr_api.g_date)
1714 = nvl(p_date_projected_end, hr_api.g_date)
1715 and nvl(per_abs_shd.g_old_rec.date_start, hr_api.g_date)
1716 = nvl(p_date_start, hr_api.g_date)
1717 and nvl(per_abs_shd.g_old_rec.date_end, hr_api.g_date)
1718 = nvl(p_date_end, hr_api.g_date)) then
1719 return;
1720 end if;
1721
1722 hr_utility.set_location(l_proc, 15);
1723
1724 --
1725 -- Check that all the dates are within the effective dates of the
1726 -- absence type.
1727 --
1728 open c_absence_within_type_dates;
1729 fetch c_absence_within_type_dates into l_exists;
1730
1731 if c_absence_within_type_dates%notfound then
1732 fnd_message.set_name('PER', 'HR_6457_ABS_DET_DATES');
1733 fnd_message.raise_error;
1734 end if;
1735
1736 close c_absence_within_type_dates;
1737
1738 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1739
1740 end chk_absence_attendance_type_id;
1741 --
1742 -- ---------------------------------------------------------------------------
1743 -- |-----------------< chk_abs_attendance_reason_id >-----------------------|
1744 -- ---------------------------------------------------------------------------
1745 --
1746 -- Description:
1747 -- Validates that an abs_attendance_reason_id exists in table
1748 -- per_abs_attendance_reasons, also valid in hr_lookups
1749 -- where lookup_type is 'ABSENCE_REASON' and enabled_flag is 'Y'
1750 -- and effective_date is between the active dates (if they are not null).
1751 --
1752 -- Pre-conditions:
1753 -- absence_attendance_type_id must be valid.
1754 -- business_group_id must be valid.
1755 -- effective_date must be valid.
1756 --
1757 -- In Arguments:
1758 -- p_absence_attendance_id
1759 -- p_absence_attendance_type_id
1760 -- p_abs_attendance_reason_id
1761 -- p_business_group_id
1762 -- p_object_version_number
1763 -- p_effective_date
1764 --
1765 -- Post Success:
1766 -- If a row does exist; processing continues.
1767 --
1768 -- Post Failure:
1769 -- If a row does not exist in per_abs_attendance_reason and hr_lookups for
1770 -- a given reason id then an error will be raised and processing terminated.
1771 --
1772 -- Access Status:
1773 -- Internal Table Handler Use Only.
1774 --
1775 -- {End Of Comments}
1776 -- ----------------------------------------------------------------------------
1777 --
1778 procedure chk_abs_attendance_reason_id
1779 (p_absence_attendance_id in number
1780 ,p_absence_attendance_type_id in number
1781 ,p_abs_attendance_reason_id in number
1782 ,p_business_group_id in number
1783 ,p_object_version_number in number
1784 ,p_effective_date in date
1785 )
1786 is
1787 --
1788 l_exists varchar2(1);
1789 l_proc varchar2(72) := g_package||'chk_abs_attendance_reason_id';
1790 --
1791 l_api_updating boolean;
1792 l_business_group_id number;
1793 --
1794 cursor csr_valid_abs_reason is
1795 select null
1796 from per_abs_attendance_reasons abr,
1797 hr_lookups hrl
1798 where abr.business_group_id = p_business_group_id
1799 and abr.absence_attendance_type_id = p_absence_attendance_type_id
1800 and abr.abs_attendance_reason_id = p_abs_attendance_reason_id
1801 and abr.name = hrl.lookup_code
1802 and hrl.lookup_type = 'ABSENCE_REASON'
1803 and p_effective_date between
1804 nvl(hrl.start_date_active,hr_api.g_sot)
1805 and nvl(hrl.end_date_active,hr_api.g_eot)
1806 and hrl.enabled_flag = 'Y';
1807 --
1808 begin
1809
1810 hr_utility.set_location('Entering:'|| l_proc, 10);
1811
1812 if p_abs_attendance_reason_id is null then
1813 return;
1814 end if;
1815
1816 --
1817 -- Only proceed with validation if :
1818 -- a) The current g_old_rec is current and
1819 -- b) The value for abs_attendance_reason_id has changed
1820 --
1821 l_api_updating := per_abs_shd.api_updating
1822 (p_absence_attendance_id => p_absence_attendance_id
1823 ,p_object_version_number => p_object_version_number);
1824 --
1825 if (l_api_updating and nvl(per_abs_shd.g_old_rec.abs_attendance_reason_id,
1826 hr_api.g_number) = nvl(p_abs_attendance_reason_id, hr_api.g_number)) then
1827 return;
1828 end if;
1829
1830 open csr_valid_abs_reason;
1831 fetch csr_valid_abs_reason into l_exists;
1832 if csr_valid_abs_reason%notfound then
1833 --
1834 fnd_message.set_name('PER', 'PER_52749_ABS_REASON_INVALID');
1835 fnd_message.raise_error;
1836 --
1837 end if;
1838 close csr_valid_abs_reason;
1839
1840 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1841
1842 end chk_abs_attendance_reason_id;
1843 --
1844 -- ---------------------------------------------------------------------------
1845 -- |-----------------< chk_absence_period >---------------------------------|
1846 -- ---------------------------------------------------------------------------
1847 --
1848 -- Description:
1849 -- Validates the projected dates, actual dates, times and the duration.
1850 --
1851 -- Pre-conditions:
1852 -- absence_attendance_type_id must be valid.
1853 -- business_group_id must be valid.
1854 -- effective_date must be valid.
1855 --
1856 -- In Arguments:
1857 -- p_absence_attendance_id
1858 -- p_absence_attendance_type_id
1859 -- p_business_group_id
1860 -- p_object_version_number
1861 -- p_effective_date
1862 -- p_person_id
1863 -- p_date_projected_start
1864 -- p_time_projected_start
1865 -- p_date_projected_end
1866 -- p_time_projected_end
1867 -- p_date_start
1868 -- p_time_start
1869 -- p_date_end
1870 -- p_time_end
1871 --
1872 -- In Out Arguments:
1873 -- p_absence_days
1874 -- p_absence_hours
1875 --
1876 -- Post Success:
1877 -- If validation passes, processing continues.
1878 --
1879 -- Post Failure:
1880 -- IF validation fails, the appropriate error or warning is raised.
1881 --
1882 -- Access Status:
1883 -- Internal Table Handler Use Only.
1884 --
1885 -- {End Of Comments}
1886 -- ----------------------------------------------------------------------------
1887 --
1888 procedure chk_absence_period
1889 (p_absence_attendance_id in number
1890 ,p_absence_attendance_type_id in number
1891 ,p_business_group_id in number
1892 ,p_object_version_number in number
1893 ,p_effective_date in date
1894 ,p_person_id in number
1895 ,p_date_projected_start in date
1896 ,p_time_projected_start in varchar2
1897 ,p_date_projected_end in date
1898 ,p_time_projected_end in varchar2
1899 ,p_date_start in date
1900 ,p_time_start in varchar2
1901 ,p_date_end in date
1902 ,p_time_end in varchar2
1903 ,p_absence_days in out nocopy number
1904 ,p_absence_hours in out nocopy number
1905 ,p_dur_dys_less_warning out nocopy boolean
1906 ,p_dur_hrs_less_warning out nocopy boolean
1907 ,p_exceeds_pto_entit_warning out nocopy boolean
1908 ,p_exceeds_run_total_warning out nocopy boolean
1909 ,p_abs_overlap_warning out nocopy boolean
1910 ,p_abs_day_after_warning out nocopy boolean
1911 ,p_dur_overwritten_warning out nocopy boolean)
1912 is
1913
1914 cursor c_get_absence_type_info is
1915 select abt.hours_or_days,
1916 abt.increasing_or_decreasing_flag,
1917 abt.absence_overlap_flag
1918 from per_absence_attendance_types abt
1919 where abt.absence_attendance_type_id = p_absence_attendance_type_id;
1920
1921 cursor c_abs_overlap_another is
1922 select null
1923 from per_absence_attendances abs
1924 where abs.person_id = p_person_id
1925 and (p_absence_attendance_id is null or
1926 p_absence_attendance_id <> abs.absence_attendance_id)
1927 and abs.date_start is not null
1928 and p_date_start is not null
1929 and ((
1930 to_date(to_char(nvl(abs.date_start,hr_api.g_eot),'YYYY-MM-DD')|| ' ' || -- Bug 4163165
1931 nvl(abs.time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
1932 between
1933 to_date(to_char(nvl(p_date_start,hr_api.g_eot),'YYYY-MM-DD')|| ' ' ||
1934 nvl(p_time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
1935 AND
1936 to_date(to_char(nvl(p_date_end,hr_api.g_eot),'YYYY-MM-DD')|| ' ' ||
1937 nvl(p_time_end,'23:59'),'YYYY-MM-DD HH24:MI:SS')) OR
1938 (
1939 to_date(to_char(nvl(p_date_start,hr_api.g_eot),'YYYY-MM-DD')|| ' ' ||
1940 nvl(p_time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
1941 between
1942 to_date(to_char(nvl(abs.date_start,hr_api.g_eot),'YYYY-MM-DD')|| ' ' ||
1943 nvl(abs.time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
1944 AND
1945 to_date(to_char(nvl(abs.date_end,hr_api.g_eot),'YYYY-MM-DD')|| ' ' ||
1946 nvl(abs.time_end,'23:59'),'YYYY-MM-DD HH24:MI:SS')
1947 ));
1948
1949 cursor c_abs_day_after_another is
1950 select null
1951 from per_absence_attendances abs,
1952 per_absence_attendance_types abt
1953 where abs.person_id = p_person_id
1954 and abs.absence_attendance_type_id = abt.absence_attendance_type_id
1955 and (p_absence_attendance_id is null or
1956 p_absence_attendance_id <> abs.absence_attendance_id)
1957 and abs.date_end = p_date_end -1
1958 and abt.absence_category = 'S';
1959
1960 cursor c_get_accrual_plans (p_assignment_id in number) is
1961 select pap.accrual_plan_id, asg.payroll_id
1962 from pay_element_entries_f pee,
1963 pay_element_links_f pel,
1964 pay_element_types_f pet,
1965 pay_input_values_f piv,
1966 per_all_assignments_f asg,
1967 per_absence_attendance_types abt,
1968 pay_accrual_plans pap
1969 where abt.absence_attendance_type_id = p_absence_attendance_type_id
1970 and abt.input_value_id = piv.input_value_id
1971 and piv.input_value_id = pap.pto_input_value_id
1972 and asg.assignment_id = p_assignment_id
1973 and pee.assignment_id = asg.assignment_id
1974 and pee.element_link_id = pel.element_link_id
1975 and pel.element_type_id = pet.element_type_id
1976 and pet.element_type_id = pap.accrual_plan_element_type_id
1977 and p_effective_date between asg.effective_start_date and
1978 asg.effective_end_date
1979 and p_effective_date between pee.effective_start_date and
1980 pee.effective_end_date
1981 and p_effective_date between pel.effective_start_date and
1982 pel.effective_end_date
1983 and p_effective_date between pet.effective_start_date and
1984 pet.effective_end_date
1985 and p_effective_date between piv.effective_start_date and
1986 piv.effective_end_date;
1987
1988 --
1989 l_proc varchar2(72) := g_package||'chk_absence_period';
1990 l_api_updating boolean;
1991 l_exists varchar2(1);
1992 l_hours_or_days varchar2(1);
1993 l_increasing_or_decreasing varchar2(1);
1994 l_absence_days number;
1995 l_absence_hours number;
1996 l_use_formula boolean;
1997 l_auto_overwrite varchar(30);
1998 l_assignment_id number;
1999 l_payroll_id number;
2000 l_accrual_plan_id number;
2001 l_dummy_date date;
2002 l_dummy_number number;
2003 l_net_entitlement number;
2004 l_accrual_msg boolean;
2005 l_running_total_hours number;
2006 l_running_total_days number;
2007 l_year_to_date_hours number;
2008 l_year_to_date_days number;
2009 l_absence_overlap_flag varchar2(1);
2010 --
2011 -- ************* Added for Bug 272978 *************
2012 -- ********* Added for AU,NZ by APAC team *********
2013 l_legislation_code varchar2(3);
2014 l_apac_dummy_number number;
2015 l_apac_entitlement number;
2016 l_apac_accrual number;
2017
2018 cursor csr_legislation(p_business_group_id number) is
2019 select pbg.legislation_code
2020 from per_business_groups pbg
2021 where pbg.business_group_id = p_business_group_id;
2022
2023 -- ********* End Bug 272978 *************
2024
2025
2026 begin
2027
2028 hr_utility.set_location('Entering:'|| l_proc, 5);
2029
2030 --
2031 -- Only proceed with validation if :
2032 -- a) The current g_old_rec is current and
2033 -- b) The date and time values have changed
2034 --
2035 l_api_updating := per_abs_shd.api_updating
2036 (p_absence_attendance_id => p_absence_attendance_id
2037 ,p_object_version_number => p_object_version_number);
2038 --
2039 if (l_api_updating
2040 and nvl(per_abs_shd.g_old_rec.date_projected_start, hr_api.g_date)
2041 = nvl(p_date_projected_start, hr_api.g_date)
2042 and nvl(per_abs_shd.g_old_rec.time_projected_start, hr_api.g_varchar2)
2043 = nvl(p_time_projected_start, hr_api.g_varchar2)
2044 and nvl(per_abs_shd.g_old_rec.date_projected_end, hr_api.g_date)
2045 = nvl(p_date_projected_end, hr_api.g_date)
2046 and nvl(per_abs_shd.g_old_rec.time_projected_end, hr_api.g_varchar2)
2047 = nvl(p_time_projected_end, hr_api.g_varchar2)
2048 and nvl(per_abs_shd.g_old_rec.date_start, hr_api.g_date)
2049 = nvl(p_date_start, hr_api.g_date)
2050 and nvl(per_abs_shd.g_old_rec.time_start, hr_api.g_varchar2)
2051 = nvl(p_time_start, hr_api.g_varchar2)
2052 and nvl(per_abs_shd.g_old_rec.date_end, hr_api.g_date)
2053 = nvl(p_date_end, hr_api.g_date)
2054 and nvl(per_abs_shd.g_old_rec.time_end, hr_api.g_varchar2)
2055 = nvl(p_time_end, hr_api.g_varchar2)
2056 and nvl(per_abs_shd.g_old_rec.absence_days, hr_api.g_number)
2057 = nvl(p_absence_days, hr_api.g_number)
2058 and nvl(per_abs_shd.g_old_rec.absence_hours, hr_api.g_number)
2059 = nvl(p_absence_hours, hr_api.g_number)) then
2060 return;
2061 end if;
2062
2063 --
2064 -- Initialise the warning messages to false.
2065 --
2066 p_dur_dys_less_warning := FALSE;
2067 p_dur_hrs_less_warning := FALSE;
2068 p_exceeds_pto_entit_warning := FALSE;
2069 p_exceeds_run_total_warning := FALSE;
2070 p_abs_overlap_warning := FALSE;
2071 p_abs_day_after_warning := FALSE;
2072 p_dur_overwritten_warning := FALSE;
2073
2074 --
2075 -- Get the absence type values for use later.
2076 --
2077 open c_get_absence_type_info;
2078 fetch c_get_absence_type_info into l_hours_or_days,
2079 l_increasing_or_decreasing,
2080 l_absence_overlap_flag;
2081 close c_get_absence_type_info;
2082
2083 hr_utility.set_location(l_proc, 10);
2084
2085 --
2086 -- Check the time formats
2087 --
2088 chk_time_format (p_time => p_time_projected_start);
2089 chk_time_format (p_time => p_time_projected_end);
2090 chk_time_format (p_time => p_time_start);
2091 chk_time_format (p_time => p_time_end);
2092
2093 --
2094 -- Check that the start dates are entered if the end dates are entered.
2095 --
2096 if (p_date_projected_start is null and p_date_projected_end is not null)
2097 or (p_date_start is null and p_date_end is not null) then
2098 fnd_message.set_name('PER','HR_289294_ABS_SD_NOT_ENTERED');
2099 fnd_message.raise_error;
2100 end if;
2101
2102 hr_utility.set_location(l_proc, 15);
2103
2104 --
2105 -- Check that the end dates are after the start dates. If they are the same
2106 -- day, check that the end time is after the start time.
2107 --
2108 if p_date_projected_end < p_date_projected_start then
2109 fnd_message.set_name('PER','PAY_7617_EMP_ABS_DATE_AFTER');
2110 fnd_message.raise_error;
2111
2112 elsif p_date_end < p_date_start then
2113 fnd_message.set_name('PER','PAY_7616_EMP_ABS_DATE_AFTER');
2114 fnd_message.raise_error;
2115
2116 elsif p_date_projected_end = p_date_projected_start
2117 and p_time_projected_end < p_time_projected_start then
2118 fnd_message.set_name('PER','PER_7619_EMP_ABS_END_TIME');
2119 fnd_message.raise_error;
2120
2121 elsif p_date_end = p_date_start
2122 and p_time_end < p_time_start then
2123 fnd_message.set_name('PER','PER_7618_EMP_ABS_END_TIME');
2124 fnd_message.raise_error;
2125
2126 end if;
2127
2128 hr_utility.set_location(l_proc, 20);
2129
2130 --
2131 -- Check that times have / have not been entered depending on the
2132 -- UOM of the absence type.
2133 --
2134 if l_hours_or_days = 'D' and
2135 (p_time_projected_start is not null or
2136 p_time_projected_end is not null or
2137 p_time_start is not null or
2138 p_time_end is not null) then
2139 --
2140 -- Times should not have been entered.
2141 --
2142 fnd_message.set_name('PER','HR_289299_ABS_TIME_DISALLOWED');
2143 fnd_message.raise_error;
2144
2145 else
2146 --
2147 -- The unit of measure is either just hours or both days and hours
2148 -- so check that the times have only been entered when the dates have.
2149 --
2150 if p_time_projected_start is not null
2151 and p_date_projected_start is null then
2152 fnd_message.set_name('PER','HR_289297_ABS_PROJ_START_DATE'); -- Fix 2647747
2153 fnd_message.raise_error;
2154
2155 elsif p_time_projected_end is not null
2156 and p_date_projected_end is null then
2157 fnd_message.set_name('PER','PER_7621_EMP_ABS_END_TIME');
2158 fnd_message.raise_error;
2159
2160 elsif p_time_start is not null and p_date_start is null then
2161 fnd_message.set_name('PER','PER_7143_EMP_ABS_START_TIME');
2162 fnd_message.raise_error;
2163
2164 elsif p_time_end is not null and p_date_end is null then
2165 fnd_message.set_name('PER','PER_7620_EMP_ABS_END_TIME');
2166 fnd_message.raise_error;
2167
2168 end if;
2169
2170 end if;
2171
2172 hr_utility.set_location(l_proc, 25);
2173
2174 --
2175 -- If the end date is entered, the duration in days and / or hours
2176 -- must be entered. This can be calculated automatically in some
2177 -- circumstances.
2178 --
2179 if p_date_end is not null then
2180 --
2181 -- Calculate the absence duration.
2182 --
2183 calculate_absence_duration
2184 (p_absence_attendance_id => p_absence_attendance_id
2185 ,p_absence_attendance_type_id => p_absence_attendance_type_id
2186 ,p_business_group_id => p_business_group_id
2187 ,p_object_version_number => p_object_version_number
2188 ,p_effective_date => p_effective_date
2189 ,p_person_id => p_person_id
2190 ,p_date_start => p_date_start
2191 ,p_date_end => p_date_end
2192 ,p_time_start => p_time_start
2193 ,p_time_end => p_time_end
2194 ,p_absence_days => l_absence_days
2195 ,p_absence_hours => l_absence_hours
2196 ,p_use_formula => l_use_formula);
2197
2198 hr_utility.trace ('Calc dys: '||to_char(l_absence_days));
2199 hr_utility.trace ('Calc hrs: '||to_char(l_absence_hours));
2200
2201 --
2202 -- The absence duration is only set if the results returned are
2203 -- from the Fast Formula and the durations are null or
2204 -- auto-overwrite is set to Yes.
2205 --
2206 l_auto_overwrite :=
2207 nvl(fnd_profile.value('PER_ABSENCE_DURATION_AUTO_OVERWRITE'),'N');
2208
2209 hr_utility.set_location(l_proc, 30);
2210
2211 If (l_use_formula)
2212 and ((l_auto_overwrite = 'Y') or
2213 (p_absence_days is null and p_absence_hours is null)) then
2214 --
2215 -- Set the absence duration in days and hours. If the UOM is set
2216 -- to days, only days are populated, if the UOM is hours, only
2217 -- hours are populated and if no UOM is set, both days and hours
2218 -- are set.
2219 --
2220
2221 hr_utility.trace ('Use Formula = TRUE');
2222
2223 p_absence_days := l_absence_days;
2224 p_absence_hours := l_absence_hours;
2225 p_dur_overwritten_warning := TRUE;
2226
2227 end if;
2228
2229 hr_utility.set_location(l_proc, 35);
2230
2231
2232 --
2233 -- Check that the duration days and / or hours have been entered if the
2234 -- element type is not recurring (recurring entries do not have an
2235 -- input value so do not require the duration days / hours).
2236 --
2237 if hr_person_absence_api.get_processing_type
2238 (p_absence_attendance_type_id) <> 'R' then
2239
2240 if l_hours_or_days = 'D' then
2241 --
2242 -- The UOM is Days so the days duration should be entered, but not
2243 -- the hours duration.
2244 --
2245 if p_absence_days is null then
2246 fnd_message.set_name('PER','HR_51059_ABS_DUR_NOT_ENTERED');
2247 fnd_message.raise_error;
2248
2249 elsif p_absence_hours is not null then
2250 fnd_message.set_name('PER','HR_289298_ABS_HRS_DISALLOWED');
2251 fnd_message.raise_error;
2252
2253 end if;
2254
2255 elsif l_hours_or_days = 'H' then
2256 --
2257 -- The UOM is Hours so the hours duration should be entered, but not
2258 -- the days duration.
2259 --
2260 if p_absence_hours is null then
2261 fnd_message.set_name('PER','HR_51059_ABS_DUR_NOT_ENTERED');
2262 fnd_message.raise_error;
2263
2264 elsif p_absence_days is not null then
2265 fnd_message.set_name('PER','HR_289300_ABS_DYS_DISALLOWED');
2266 fnd_message.raise_error;
2267
2268 end if;
2269
2270 else
2271 --
2272 -- No UOM is set so either days or hours can be entered (or both).
2273 --
2274 if p_absence_hours is null and p_absence_days is null then
2275 fnd_message.set_name('PER','HR_51059_ABS_DUR_NOT_ENTERED');
2276 fnd_message.raise_error;
2277 end if;
2278
2279 end if;
2280
2281 end if;
2282
2283 end if;
2284
2285 hr_utility.set_location(l_proc, 40);
2286
2287 --
2288 -- Check that the dates and / or times have been entered if the duration
2289 -- has been entered.
2290 --
2291 if p_absence_days is not null
2292 and (p_date_start is null or p_date_end is null) then
2293 fnd_message.set_name('PER','PER_7714_ABS_CALC_DURATION');
2294 fnd_message.raise_error;
2295 /*
2296 elsif p_absence_hours is not null
2297 and (p_time_start is null or p_time_end is null) then
2298 fnd_message.set_name('PER','PER_7145_EMP_ABS_UNPAID_HOURS');
2299 fnd_message.raise_error;
2300 */
2301 end if;
2302
2303 hr_utility.set_location(l_proc, 45);
2304
2305 --
2306 -- Check if the absence duration in days differs from
2307 -- the amount of time absent.
2308 --
2309 if (p_absence_days <> l_absence_days) then
2310 --
2311 -- Set the warning message.
2312 --
2313 p_dur_dys_less_warning := TRUE;
2314
2315
2316 -- Commented as a fix for the bug 4606467
2317 /*
2318 elsif p_absence_days > l_absence_days then
2319 --
2320 -- Raise the error message. The duration cannot be greater than
2321 -- the system calculated duration.
2322 --
2323 fnd_message.set_name('PER','PER_7622_EMP_ABS_LONG_DURATION');
2324 fnd_message.raise_error;*/
2325
2326 end if;
2327
2328 --
2329 -- Check if the absence duration in hours differs from
2330 -- the amount of time absent.
2331 --
2332 if (p_absence_hours <> l_absence_hours) then
2333 --
2334 -- Set the warning message.
2335 --
2336 p_dur_hrs_less_warning := TRUE;
2337
2338
2339 -- Commented as a fix for the bug 4606467
2340 /* elsif p_absence_hours > l_absence_hours then
2341 --
2342 -- Raise the error message. The duration cannot be greater than
2343 -- the system calculated duration.
2344 --
2345 fnd_message.set_name('PER','PER_7623_EMP_ABS_LONG_DURATION');
2346 fnd_message.raise_error;*/
2347
2348 end if;
2349
2350 hr_utility.set_location(l_proc, 50);
2351
2352 --
2353 -- Check if this absence exceeds the net entitlement on any of the
2354 -- employee's accrual plans. First the assignment id and plan id(s)
2355 -- are required.
2356 --
2357 if p_absence_days is not null or p_absence_hours is not null then
2358
2359 l_assignment_id := hr_person_absence_api.get_primary_assignment
2360 (p_person_id => p_person_id
2361 ,p_effective_date => p_effective_date);
2362
2363 declare
2364
2365 l_message_text fnd_new_messages.message_text%TYPE;
2366
2367 begin
2368
2369 open c_get_accrual_plans (l_assignment_id);
2370 loop
2371
2372 fetch c_get_accrual_plans into l_accrual_plan_id, l_payroll_id;
2373 exit when c_get_accrual_plans%notfound;
2374
2375 --
2376 -- Get the net entitlement of each plan.
2377 --
2378
2379 -- ****************** Added for Bug 2729784 ***************
2380 -- ************** Added for AU,NZ by APAC team ************
2381
2382 open csr_legislation(p_business_group_id);
2383 fetch csr_legislation into l_legislation_code;
2384 if csr_legislation%notfound
2385 then
2386 close csr_legislation;
2387 --
2388 -- The primary key is invalid therefore we must error
2389 --
2390 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
2391 fnd_message.raise_error;
2392 end if;
2393 close csr_legislation;
2394
2395 if l_legislation_code = 'AU'
2396 then
2397 hr_utility.set_location('APAC code - '||l_proc,52);
2398 l_net_entitlement := hr_au_holidays.get_net_accrual
2399 (p_assignment_id => l_assignment_id
2400 ,p_payroll_id => l_payroll_id
2401 ,p_business_group_id => p_business_group_id
2402 ,p_plan_id => l_accrual_plan_id
2403 ,p_calculation_date => nvl(p_date_start, p_effective_date)
2404 );
2405 elsif l_legislation_code = 'NZ'
2406 then
2407 hr_utility.set_location(l_proc,53);
2408 l_net_entitlement := hr_nz_holidays.get_net_accrual
2409 (p_assignment_id => l_assignment_id
2410 ,p_payroll_id => l_payroll_id
2411 ,p_business_group_id => p_business_group_id
2412 ,p_plan_id => l_accrual_plan_id
2413 ,p_calculation_date => nvl(p_date_start, p_effective_date)
2414 );
2415
2416 else
2417 hr_utility.set_location('APAC code - '||l_proc,54);
2418 -- ********** existing code wrapped by if statement from bug 2729784 *************
2419 per_accrual_calc_functions.get_net_accrual
2420 (p_assignment_id => l_assignment_id
2421 ,p_plan_id => l_accrual_plan_id
2422 ,p_payroll_id => l_payroll_id
2423 ,p_business_group_id => p_business_group_id
2424 ,p_calculation_date => nvl(p_date_start, p_effective_date)
2425 ,p_start_date => l_dummy_date
2426 ,p_end_date => l_dummy_date
2427 ,p_accrual_end_date => l_dummy_date
2428 ,p_accrual => l_dummy_number
2429 ,p_net_entitlement => l_net_entitlement);
2430 -- ********** end existing code ***************
2431
2432 end if;
2433 hr_utility.trace('Ent = '||to_char(l_net_entitlement));
2434
2435 -- ****************** End Bug 2729784 *********************
2436
2437
2438
2439 --
2440 -- The duration value in g_old_rec is first added to the net
2441 -- entitlement. This prevents absence records that are being
2442 -- updated from being double counted (get_net_accrual has already
2443 -- included the absence in the calculation of the net entitlement).
2444 --
2445 if l_hours_or_days = 'H' then
2446 l_net_entitlement := l_net_entitlement
2447 + nvl(per_abs_shd.g_old_rec.absence_hours, 0)
2448 - p_absence_hours;
2449
2450 elsif l_hours_or_days = 'D' then
2451 l_net_entitlement := l_net_entitlement
2452 + nvl(per_abs_shd.g_old_rec.absence_days, 0)
2453 - p_absence_days;
2454 end if;
2455
2456 --
2457 -- Check if the new net entitlement is less than zero.
2458 --
2459 if l_net_entitlement < 0 then
2460 --
2461 -- Instead of raising the message here, the boolean value is set.
2462 -- This prevents the message appearing multiple times.
2463 --
2464 l_accrual_msg := TRUE;
2465 end if;
2466
2467 end loop;
2468 close c_get_accrual_plans;
2469
2470 exception
2471
2472 when others then
2473
2474 l_message_text := fnd_message.get;
2475 hr_utility.trace('Unable to execute the PTO formula. '||l_message_text);
2476
2477 end;
2478
2479 if (l_accrual_msg) then
2480 p_exceeds_pto_entit_warning := TRUE;
2481 end if;
2482
2483 end if;
2484
2485 hr_utility.set_location(l_proc, 55);
2486
2487 --
2488 -- Get the running totals and check that the values do not
2489 -- decrease to less than zero.
2490 --
2491 get_running_totals
2492 (p_person_id => p_person_id
2493 ,p_absence_attendance_type_id => p_absence_attendance_type_id
2494 ,p_effective_date => p_effective_date
2495 ,p_running_total_hours => l_running_total_hours
2496 ,p_running_total_days => l_running_total_days
2497 ,p_year_to_date_hours => l_year_to_date_hours
2498 ,p_year_to_date_days => l_year_to_date_days);
2499
2500 --
2501 -- Here the value of g_old_rec is subtracted first. This prevents
2502 -- records already included in l_running_total_hours / days
2503 -- being double counted (this would occur during update only).
2504 --
2505 if l_increasing_or_decreasing = 'D'
2506 and (l_running_total_hours +
2507 nvl(per_abs_shd.g_old_rec.absence_hours, 0) - p_absence_hours < 0
2508 or l_running_total_days +
2509 nvl(per_abs_shd.g_old_rec.absence_days, 0) - p_absence_days < 0)
2510 then
2511
2512 p_exceeds_run_total_warning := TRUE;
2513
2514 hr_utility.set_location(l_proc, 57);
2515
2516 end if;
2517
2518 hr_utility.set_location(l_proc, 60);
2519
2520 --
2521 -- Check if this absence overlaps another absence for the same person.
2522 --
2523 open c_abs_overlap_another;
2524 fetch c_abs_overlap_another into l_exists;
2525
2526 if c_abs_overlap_another%found then
2527 if l_absence_overlap_flag = 'N' then
2528 --
2529 -- Set the warning message
2530 --
2531 p_abs_overlap_warning := TRUE;
2532
2533 end if;
2534 end if;
2535
2536 hr_utility.set_location(l_proc, 65);
2537
2538 --
2539 -- Check if this is a sickness absence that starts the day after another
2540 -- sickness absence for this person.
2541 --
2542 open c_abs_day_after_another;
2543 fetch c_abs_day_after_another into l_exists;
2544
2545 if c_abs_day_after_another%found then
2546 --
2547 -- Set the warning message
2548 --
2549 p_abs_day_after_warning := TRUE;
2550
2551 end if;
2552
2553 --
2554 hr_utility.set_location('Leaving:'|| l_proc, 70);
2555
2556 end chk_absence_period;
2557 --
2558 -- ---------------------------------------------------------------------------
2559 -- |----------------------< chk_replacement_person_id >---------------------|
2560 -- ---------------------------------------------------------------------------
2561 --
2562 -- Description:
2563 -- Validates that the person exists, that they have a valid period of
2564 -- service and that they match the business group id being passed.
2565 --
2566 -- Pre-conditions:
2567 --
2568 -- In Arguments:
2569 -- p_absence_attendance_id
2570 -- p_replacement_person_id
2571 -- p_business_group_id
2572 --
2573 -- Post Success:
2574 -- If the person and their period of service are valid, processing
2575 -- continues.
2576 --
2577 -- Post Failure:
2578 -- An application error will be raised and processing is terminated.
2579 --
2580 -- Access Status:
2581 -- Internal Development Use Only.
2582 --
2583 -- {End Of Comments}
2584 -- ----------------------------------------------------------------------------
2585 --
2586 procedure chk_replacement_person_id
2587 (p_absence_attendance_id in number
2588 ,p_replacement_person_id in number
2589 ,p_business_group_id in number
2590 ,p_object_version_number in number
2591 ,p_date_projected_start in date
2592 ,p_date_projected_end in date
2593 ,p_date_start in date
2594 ,p_date_end in date)
2595 is
2596
2597 --
2598 l_proc varchar2(72) := g_package||'chk_replacement_person_id';
2599 l_api_updating boolean;
2600 --
2601
2602 begin
2603
2604 hr_utility.set_location('Entering:'|| l_proc, 10);
2605 --
2606 hr_api.mandatory_arg_error
2607 (p_api_name => l_proc
2608 ,p_argument => 'p_business_group_id'
2609 ,p_argument_value => p_business_group_id
2610 );
2611
2612 --
2613 -- Only proceed with validation if :
2614 -- a) The current g_old_rec is current and
2615 -- b) The date values have changed
2616 --
2617 l_api_updating := per_abs_shd.api_updating
2618 (p_absence_attendance_id => p_absence_attendance_id
2619 ,p_object_version_number => p_object_version_number);
2620 --
2621 if (l_api_updating
2622 and nvl(per_abs_shd.g_old_rec.replacement_person_id, hr_api.g_number)
2623 = nvl(p_replacement_person_id, hr_api.g_number)
2624 and nvl(per_abs_shd.g_old_rec.date_projected_start, hr_api.g_date)
2625 = nvl(p_date_projected_start, hr_api.g_date)
2626 and nvl(per_abs_shd.g_old_rec.date_projected_end, hr_api.g_date)
2627 = nvl(p_date_projected_end, hr_api.g_date)
2628 and nvl(per_abs_shd.g_old_rec.date_start, hr_api.g_date)
2629 = nvl(p_date_start, hr_api.g_date)
2630 and nvl(per_abs_shd.g_old_rec.date_end, hr_api.g_date)
2631 = nvl(p_date_end, hr_api.g_date)) then
2632 return;
2633 end if;
2634
2635 if p_replacement_person_id is not null then
2636 --
2637 -- Check that the replacement exists and that their period of service
2638 -- is valid for the entire absence duration.
2639 --
2640 if not per_valid_for_absence
2641 (p_person_id => p_replacement_person_id
2642 ,p_business_group_id => p_business_group_id
2643 ,p_date_projected_start => p_date_projected_start
2644 ,p_date_projected_end => p_date_projected_end
2645 ,p_date_start => p_date_start
2646 ,p_date_end => p_date_end)
2647 then
2648
2649 fnd_message.set_name('PER', 'HR_7553_ASS_REP_INVALID');
2650 fnd_message.raise_error;
2651
2652 end if;
2653
2654 end if;
2655
2656 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2657
2658 end chk_replacement_person_id;
2659 --
2660 --
2661 -- ---------------------------------------------------------------------------
2662 -- |----------------------< chk_authorising_person_id >---------------------|
2663 -- ---------------------------------------------------------------------------
2664 --
2665 -- Description:
2666 -- Validates that the person exists, that they have a valid period of
2667 -- service and that they match the business group id being passed.
2668 --
2669 -- Pre-conditions:
2670 --
2671 -- In Arguments:
2672 -- p_absence_attendance_id
2673 -- p_replacement_person_id
2674 -- p_business_group_id
2675 --
2676 -- Post Success:
2677 -- If the person and their period of service are valid, processing
2678 -- continues.
2679 --
2680 -- Post Failure:
2681 -- An application error will be raised and processing is terminated.
2682 --
2683 -- Access Status:
2684 -- Internal Development Use Only.
2685 --
2686 -- {End Of Comments}
2687 -- ----------------------------------------------------------------------------
2688 --
2689 procedure chk_authorising_person_id
2690 (p_absence_attendance_id in number
2691 ,p_authorising_person_id in number
2692 ,p_business_group_id in number
2693 ,p_object_version_number in number
2694 ,p_date_projected_start in date
2695 ,p_date_projected_end in date
2696 ,p_date_start in date
2697 ,p_date_end in date)
2698 is
2699
2700 --
2701 l_proc varchar2(72) := g_package||'chk_authorising_person_id';
2702 l_api_updating boolean;
2703 --
2704
2705 begin
2706
2707 hr_utility.set_location('Entering:'|| l_proc, 10);
2708 --
2709 hr_api.mandatory_arg_error
2710 (p_api_name => l_proc
2711 ,p_argument => 'p_business_group_id'
2712 ,p_argument_value => p_business_group_id
2713 );
2714
2715 --
2716 -- Only proceed with validation if :
2717 -- a) The current g_old_rec is current and
2718 -- b) The date values have changed
2719 --
2720 l_api_updating := per_abs_shd.api_updating
2721 (p_absence_attendance_id => p_absence_attendance_id
2722 ,p_object_version_number => p_object_version_number);
2723 --
2724 if (l_api_updating
2725 and nvl(per_abs_shd.g_old_rec.authorising_person_id, hr_api.g_number)
2726 = nvl(p_authorising_person_id, hr_api.g_number)
2727 and nvl(per_abs_shd.g_old_rec.date_projected_start, hr_api.g_date)
2728 = nvl(p_date_projected_start, hr_api.g_date)
2729 and nvl(per_abs_shd.g_old_rec.date_projected_end, hr_api.g_date)
2730 = nvl(p_date_projected_end, hr_api.g_date)
2731 and nvl(per_abs_shd.g_old_rec.date_start, hr_api.g_date)
2732 = nvl(p_date_start, hr_api.g_date)
2733 and nvl(per_abs_shd.g_old_rec.date_end, hr_api.g_date)
2734 = nvl(p_date_end, hr_api.g_date)) then
2735 return;
2736 end if;
2737
2738 if p_authorising_person_id is not null then
2739 --
2740 -- Check that the authorisor exists and that their period of service
2741 -- is valid for the entire absence duration.
2742 --
2743 if not per_valid_for_absence
2744 (p_person_id => p_authorising_person_id
2745 ,p_business_group_id => p_business_group_id
2746 ,p_date_projected_start => p_date_projected_start
2747 ,p_date_projected_end => p_date_projected_end
2748 ,p_date_start => p_date_start
2749 ,p_date_end => p_date_end)
2750 then
2751
2752 fnd_message.set_name('PER', 'HR_7552_ASS_AUTH_INVALID');
2753 fnd_message.raise_error;
2754
2755 end if;
2756
2757 end if;
2758
2759 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2760
2761 end chk_authorising_person_id;
2762 --
2763 -- ---------------------------------------------------------------------------
2764 -- |----------------------< set_security_group_id >--------------------------|
2765 -- ---------------------------------------------------------------------------
2766 --
2767 Procedure set_security_group_id
2768 (p_absence_attendance_id in number
2769 ) is
2770 --
2771 -- Declare cursor
2772 --
2773 cursor csr_sec_grp is
2774 select pbg.security_group_id
2775 from per_business_groups pbg
2776 , per_absence_attendances abs
2777 where abs.absence_attendance_id = p_absence_attendance_id
2778 and pbg.business_group_id = abs.business_group_id;
2779 --
2780 -- Declare local variables
2781 --
2782 l_security_group_id number;
2783 l_proc varchar2(72) := g_package||'set_security_group_id';
2784 --
2785 begin
2786 --
2787 hr_utility.set_location('Entering:'|| l_proc, 10);
2788 --
2789 -- Ensure that all the mandatory parameter are not null
2790 --
2791 hr_api.mandatory_arg_error
2792 (p_api_name => l_proc
2793 ,p_argument => 'absence_attendance_id'
2794 ,p_argument_value => p_absence_attendance_id
2795 );
2796 --
2797 open csr_sec_grp;
2798 fetch csr_sec_grp into l_security_group_id;
2799 --
2800 if csr_sec_grp%notfound then
2801 --
2802 close csr_sec_grp;
2803 --
2804 -- The primary key is invalid therefore we must error
2805 --
2806 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
2807 fnd_message.raise_error;
2808 --
2809 end if;
2810 close csr_sec_grp;
2811 --
2812 -- Set the security_group_id in CLIENT_INFO
2813 --
2814 hr_api.set_security_group_id
2815 (p_security_group_id => l_security_group_id
2816 );
2817 --
2818 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2819 --
2820 end set_security_group_id;
2821 --
2822 -- ---------------------------------------------------------------------------
2823 -- |---------------------< return_legislation_code >-------------------------|
2824 -- ---------------------------------------------------------------------------
2825 --
2826 Function return_legislation_code
2827 (p_absence_attendance_id in number
2828 )
2829 Return Varchar2 Is
2830 --
2831 -- Declare cursor
2832 --
2833 cursor csr_leg_code is
2834 select pbg.legislation_code
2835 from per_business_groups pbg
2836 , per_absence_attendances abs
2837 where abs.absence_attendance_id = p_absence_attendance_id
2838 and pbg.business_group_id = abs.business_group_id;
2839 --
2840 -- Declare local variables
2841 --
2842 l_legislation_code varchar2(150);
2843 l_proc varchar2(72) := g_package||'return_legislation_code';
2844 --
2845 Begin
2846 --
2847 hr_utility.set_location('Entering:'|| l_proc, 10);
2848 --
2849 -- Ensure that all the mandatory parameter are not null
2850 --
2851 hr_api.mandatory_arg_error
2852 (p_api_name => l_proc
2853 ,p_argument => 'absence_attendance_id'
2854 ,p_argument_value => p_absence_attendance_id
2855 );
2856 --
2857 if ( nvl(per_abs_bus.g_absence_attendance_id, hr_api.g_number)
2858 = p_absence_attendance_id) then
2859 --
2860 -- The legislation code has already been found with a previous
2861 -- call to this function. Just return the value in the global
2862 -- variable.
2863 --
2864 l_legislation_code := per_abs_bus.g_legislation_code;
2865 hr_utility.set_location(l_proc, 20);
2866 else
2867 --
2868 -- The ID is different to the last call to this function
2869 -- or this is the first call to this function.
2870 --
2871 open csr_leg_code;
2872 fetch csr_leg_code into l_legislation_code;
2873 --
2874 if csr_leg_code%notfound then
2875 --
2876 -- The primary key is invalid therefore we must error
2877 --
2878 close csr_leg_code;
2879 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
2880 fnd_message.raise_error;
2881 end if;
2882 hr_utility.set_location(l_proc,30);
2883 --
2884 -- Set the global variables so the values are
2885 -- available for the next call to this function.
2886 --
2887 close csr_leg_code;
2888 per_abs_bus.g_absence_attendance_id:= p_absence_attendance_id;
2889 per_abs_bus.g_legislation_code := l_legislation_code;
2890 end if;
2891 hr_utility.set_location(' Leaving:'|| l_proc, 40);
2892 return l_legislation_code;
2893 end return_legislation_code;
2894 --
2895 -- ----------------------------------------------------------------------------
2896 -- |-----------------------------< chk_ddf >----------------------------------|
2897 -- ----------------------------------------------------------------------------
2898 --
2899 -- Description:
2900 -- Validates all the Developer Descriptive Flexfield values.
2901 --
2902 -- Prerequisites:
2903 -- All other columns have been validated. Must be called as the
2904 -- last step from insert_validate and update_validate.
2905 --
2906 -- In Arguments:
2907 -- p_rec
2908 --
2909 -- Post Success:
2910 -- If the Developer Descriptive Flexfield structure column and data values
2911 -- are all valid this procedure will end normally and processing will
2912 -- continue.
2913 --
2914 -- Post Failure:
2915 -- If the Developer Descriptive Flexfield structure column value or any of
2916 -- the data values are invalid then an application error is raised as
2917 -- a PL/SQL exception.
2918 --
2919 -- Access Status:
2920 -- Internal Row Handler Use Only.
2921 --
2922 -- ----------------------------------------------------------------------------
2923 --
2924 procedure chk_ddf
2925 (p_rec in per_abs_shd.g_rec_type
2926 ) is
2927 --
2928 l_proc varchar2(72) := g_package || 'chk_ddf';
2929 --
2930 begin
2931 hr_utility.set_location('Entering:'||l_proc,10);
2932 --
2933 if ((p_rec.absence_attendance_id is not null) and (
2934 nvl(per_abs_shd.g_old_rec.abs_information_category, hr_api.g_varchar2) <>
2935 nvl(p_rec.abs_information_category, hr_api.g_varchar2) or
2936 nvl(per_abs_shd.g_old_rec.abs_information1, hr_api.g_varchar2) <>
2937 nvl(p_rec.abs_information1, hr_api.g_varchar2) or
2938 nvl(per_abs_shd.g_old_rec.abs_information2, hr_api.g_varchar2) <>
2939 nvl(p_rec.abs_information2, hr_api.g_varchar2) or
2940 nvl(per_abs_shd.g_old_rec.abs_information3, hr_api.g_varchar2) <>
2941 nvl(p_rec.abs_information3, hr_api.g_varchar2) or
2942 nvl(per_abs_shd.g_old_rec.abs_information4, hr_api.g_varchar2) <>
2943 nvl(p_rec.abs_information4, hr_api.g_varchar2) or
2944 nvl(per_abs_shd.g_old_rec.abs_information5, hr_api.g_varchar2) <>
2945 nvl(p_rec.abs_information5, hr_api.g_varchar2) or
2946 nvl(per_abs_shd.g_old_rec.abs_information6, hr_api.g_varchar2) <>
2947 nvl(p_rec.abs_information6, hr_api.g_varchar2) or
2948 nvl(per_abs_shd.g_old_rec.abs_information7, hr_api.g_varchar2) <>
2949 nvl(p_rec.abs_information7, hr_api.g_varchar2) or
2950 nvl(per_abs_shd.g_old_rec.abs_information8, hr_api.g_varchar2) <>
2951 nvl(p_rec.abs_information8, hr_api.g_varchar2) or
2952 nvl(per_abs_shd.g_old_rec.abs_information9, hr_api.g_varchar2) <>
2953 nvl(p_rec.abs_information9, hr_api.g_varchar2) or
2954 nvl(per_abs_shd.g_old_rec.abs_information10, hr_api.g_varchar2) <>
2955 nvl(p_rec.abs_information10, hr_api.g_varchar2) or
2956 nvl(per_abs_shd.g_old_rec.abs_information11, hr_api.g_varchar2) <>
2957 nvl(p_rec.abs_information11, hr_api.g_varchar2) or
2958 nvl(per_abs_shd.g_old_rec.abs_information12, hr_api.g_varchar2) <>
2959 nvl(p_rec.abs_information12, hr_api.g_varchar2) or
2960 nvl(per_abs_shd.g_old_rec.abs_information13, hr_api.g_varchar2) <>
2961 nvl(p_rec.abs_information13, hr_api.g_varchar2) or
2962 nvl(per_abs_shd.g_old_rec.abs_information14, hr_api.g_varchar2) <>
2963 nvl(p_rec.abs_information14, hr_api.g_varchar2) or
2964 nvl(per_abs_shd.g_old_rec.abs_information15, hr_api.g_varchar2) <>
2965 nvl(p_rec.abs_information15, hr_api.g_varchar2) or
2966 nvl(per_abs_shd.g_old_rec.abs_information16, hr_api.g_varchar2) <>
2967 nvl(p_rec.abs_information16, hr_api.g_varchar2) or
2968 nvl(per_abs_shd.g_old_rec.abs_information17, hr_api.g_varchar2) <>
2969 nvl(p_rec.abs_information17, hr_api.g_varchar2) or
2970 nvl(per_abs_shd.g_old_rec.abs_information18, hr_api.g_varchar2) <>
2971 nvl(p_rec.abs_information18, hr_api.g_varchar2) or
2972 nvl(per_abs_shd.g_old_rec.abs_information19, hr_api.g_varchar2) <>
2973 nvl(p_rec.abs_information19, hr_api.g_varchar2) or
2974 nvl(per_abs_shd.g_old_rec.abs_information20, hr_api.g_varchar2) <>
2975 nvl(p_rec.abs_information20, hr_api.g_varchar2) or
2976 nvl(per_abs_shd.g_old_rec.abs_information21, hr_api.g_varchar2) <>
2977 nvl(p_rec.abs_information21, hr_api.g_varchar2) or
2978 nvl(per_abs_shd.g_old_rec.abs_information22, hr_api.g_varchar2) <>
2979 nvl(p_rec.abs_information22, hr_api.g_varchar2) or
2980 nvl(per_abs_shd.g_old_rec.abs_information23, hr_api.g_varchar2) <>
2981 nvl(p_rec.abs_information23, hr_api.g_varchar2) or
2982 nvl(per_abs_shd.g_old_rec.abs_information24, hr_api.g_varchar2) <>
2983 nvl(p_rec.abs_information24, hr_api.g_varchar2) or
2984 nvl(per_abs_shd.g_old_rec.abs_information25, hr_api.g_varchar2) <>
2985 nvl(p_rec.abs_information25, hr_api.g_varchar2) or
2986 nvl(per_abs_shd.g_old_rec.abs_information26, hr_api.g_varchar2) <>
2987 nvl(p_rec.abs_information26, hr_api.g_varchar2) or
2988 nvl(per_abs_shd.g_old_rec.abs_information27, hr_api.g_varchar2) <>
2989 nvl(p_rec.abs_information27, hr_api.g_varchar2) or
2990 nvl(per_abs_shd.g_old_rec.abs_information28, hr_api.g_varchar2) <>
2991 nvl(p_rec.abs_information28, hr_api.g_varchar2) or
2992 nvl(per_abs_shd.g_old_rec.abs_information29, hr_api.g_varchar2) <>
2993 nvl(p_rec.abs_information29, hr_api.g_varchar2) or
2994 nvl(per_abs_shd.g_old_rec.abs_information30, hr_api.g_varchar2) <>
2995 nvl(p_rec.abs_information30, hr_api.g_varchar2) ))
2996 or (p_rec.absence_attendance_id is null) then
2997 --
2998 -- Only execute the validation if absolutely necessary:
2999 -- a) During update, the structure column value or any
3000 -- of the attribute values have actually changed.
3001 -- b) During insert.
3002 --
3003 hr_dflex_utility.ins_or_upd_descflex_attribs
3004 (p_appl_short_name => 'PER'
3005 ,p_descflex_name => 'PER_ABS_DEVELOPER_DF'
3006 ,p_attribute_category => p_rec.abs_information_category
3007 ,p_attribute1_name => 'ABS_INFORMATION1'
3008 ,p_attribute1_value => p_rec.abs_information1
3009 ,p_attribute2_name => 'ABS_INFORMATION2'
3010 ,p_attribute2_value => p_rec.abs_information2
3011 ,p_attribute3_name => 'ABS_INFORMATION3'
3012 ,p_attribute3_value => p_rec.abs_information3
3013 ,p_attribute4_name => 'ABS_INFORMATION4'
3014 ,p_attribute4_value => p_rec.abs_information4
3015 ,p_attribute5_name => 'ABS_INFORMATION5'
3016 ,p_attribute5_value => p_rec.abs_information5
3017 ,p_attribute6_name => 'ABS_INFORMATION6'
3018 ,p_attribute6_value => p_rec.abs_information6
3019 ,p_attribute7_name => 'ABS_INFORMATION7'
3020 ,p_attribute7_value => p_rec.abs_information7
3021 ,p_attribute8_name => 'ABS_INFORMATION8'
3022 ,p_attribute8_value => p_rec.abs_information8
3023 ,p_attribute9_name => 'ABS_INFORMATION9'
3024 ,p_attribute9_value => p_rec.abs_information9
3025 ,p_attribute10_name => 'ABS_INFORMATION10'
3026 ,p_attribute10_value => p_rec.abs_information10
3027 ,p_attribute11_name => 'ABS_INFORMATION11'
3028 ,p_attribute11_value => p_rec.abs_information11
3029 ,p_attribute12_name => 'ABS_INFORMATION12'
3030 ,p_attribute12_value => p_rec.abs_information12
3031 ,p_attribute13_name => 'ABS_INFORMATION13'
3032 ,p_attribute13_value => p_rec.abs_information13
3033 ,p_attribute14_name => 'ABS_INFORMATION14'
3034 ,p_attribute14_value => p_rec.abs_information14
3035 ,p_attribute15_name => 'ABS_INFORMATION15'
3036 ,p_attribute15_value => p_rec.abs_information15
3037 ,p_attribute16_name => 'ABS_INFORMATION16'
3038 ,p_attribute16_value => p_rec.abs_information16
3039 ,p_attribute17_name => 'ABS_INFORMATION17'
3040 ,p_attribute17_value => p_rec.abs_information17
3041 ,p_attribute18_name => 'ABS_INFORMATION18'
3042 ,p_attribute18_value => p_rec.abs_information18
3043 ,p_attribute19_name => 'ABS_INFORMATION19'
3044 ,p_attribute19_value => p_rec.abs_information19
3045 ,p_attribute20_name => 'ABS_INFORMATION20'
3046 ,p_attribute20_value => p_rec.abs_information20
3047 ,p_attribute21_name => 'ABS_INFORMATION21'
3048 ,p_attribute21_value => p_rec.abs_information21
3049 ,p_attribute22_name => 'ABS_INFORMATION22'
3050 ,p_attribute22_value => p_rec.abs_information22
3051 ,p_attribute23_name => 'ABS_INFORMATION23'
3052 ,p_attribute23_value => p_rec.abs_information23
3053 ,p_attribute24_name => 'ABS_INFORMATION24'
3054 ,p_attribute24_value => p_rec.abs_information24
3055 ,p_attribute25_name => 'ABS_INFORMATION25'
3056 ,p_attribute25_value => p_rec.abs_information25
3057 ,p_attribute26_name => 'ABS_INFORMATION26'
3058 ,p_attribute26_value => p_rec.abs_information26
3059 ,p_attribute27_name => 'ABS_INFORMATION27'
3060 ,p_attribute27_value => p_rec.abs_information27
3061 ,p_attribute28_name => 'ABS_INFORMATION28'
3062 ,p_attribute28_value => p_rec.abs_information28
3063 ,p_attribute29_name => 'ABS_INFORMATION29'
3064 ,p_attribute29_value => p_rec.abs_information29
3065 ,p_attribute30_name => 'ABS_INFORMATION30'
3066 ,p_attribute30_value => p_rec.abs_information30
3067 );
3068 end if;
3069 --
3070 hr_utility.set_location(' Leaving:'||l_proc,20);
3071 end chk_ddf;
3072 --
3073 -- ----------------------------------------------------------------------------
3074 -- |------------------------------< chk_df >----------------------------------|
3075 -- ----------------------------------------------------------------------------
3076 --
3077 -- Description:
3078 -- Validates all the Descriptive Flexfield values.
3079 --
3080 -- Prerequisites:
3081 -- All other columns have been validated. Must be called as the
3082 -- last step from insert_validate and update_validate.
3083 --
3084 -- In Arguments:
3085 -- p_rec
3086 --
3087 -- Post Success:
3088 -- If the Descriptive Flexfield structure column and data values are
3089 -- all valid this procedure will end normally and processing will
3090 -- continue.
3091 --
3092 -- Post Failure:
3093 -- If the Descriptive Flexfield structure column value or any of
3094 -- the data values are invalid then an application error is raised as
3095 -- a PL/SQL exception.
3096 --
3097 -- Access Status:
3098 -- Internal Row Handler Use Only.
3099 --
3100 -- ----------------------------------------------------------------------------
3101 --
3102 procedure chk_df
3103 (p_rec in per_abs_shd.g_rec_type
3104 ) is
3105 --
3106 l_proc varchar2(72) := g_package || 'chk_df';
3107 --
3108 begin
3109 hr_utility.set_location('Entering:'||l_proc,10);
3110 --
3111 if ((p_rec.absence_attendance_id is not null) and (
3112 nvl(per_abs_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
3113 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
3114 nvl(per_abs_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
3115 nvl(p_rec.attribute1, hr_api.g_varchar2) or
3116 nvl(per_abs_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
3117 nvl(p_rec.attribute2, hr_api.g_varchar2) or
3118 nvl(per_abs_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
3119 nvl(p_rec.attribute3, hr_api.g_varchar2) or
3120 nvl(per_abs_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
3121 nvl(p_rec.attribute4, hr_api.g_varchar2) or
3122 nvl(per_abs_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
3123 nvl(p_rec.attribute5, hr_api.g_varchar2) or
3124 nvl(per_abs_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
3125 nvl(p_rec.attribute6, hr_api.g_varchar2) or
3126 nvl(per_abs_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
3127 nvl(p_rec.attribute7, hr_api.g_varchar2) or
3128 nvl(per_abs_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
3129 nvl(p_rec.attribute8, hr_api.g_varchar2) or
3130 nvl(per_abs_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
3131 nvl(p_rec.attribute9, hr_api.g_varchar2) or
3132 nvl(per_abs_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
3133 nvl(p_rec.attribute10, hr_api.g_varchar2) or
3134 nvl(per_abs_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
3135 nvl(p_rec.attribute11, hr_api.g_varchar2) or
3136 nvl(per_abs_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
3137 nvl(p_rec.attribute12, hr_api.g_varchar2) or
3138 nvl(per_abs_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
3139 nvl(p_rec.attribute13, hr_api.g_varchar2) or
3140 nvl(per_abs_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
3141 nvl(p_rec.attribute14, hr_api.g_varchar2) or
3142 nvl(per_abs_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
3143 nvl(p_rec.attribute15, hr_api.g_varchar2) or
3144 nvl(per_abs_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
3145 nvl(p_rec.attribute16, hr_api.g_varchar2) or
3146 nvl(per_abs_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
3147 nvl(p_rec.attribute17, hr_api.g_varchar2) or
3148 nvl(per_abs_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
3149 nvl(p_rec.attribute18, hr_api.g_varchar2) or
3150 nvl(per_abs_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
3151 nvl(p_rec.attribute19, hr_api.g_varchar2) or
3152 nvl(per_abs_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
3153 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
3154 or (p_rec.absence_attendance_id is null) then
3155 --
3156 -- Only execute the validation if absolutely necessary:
3157 -- a) During update, the structure column value or any
3158 -- of the attribute values have actually changed.
3159 -- b) During insert.
3160 --
3161 hr_dflex_utility.ins_or_upd_descflex_attribs
3162 (p_appl_short_name => 'PER'
3163 ,p_descflex_name => 'PER_ABSENCE_ATTENDANCES'
3164 ,p_attribute_category => p_rec.attribute_category
3165 ,p_attribute1_name => 'ATTRIBUTE1'
3166 ,p_attribute1_value => p_rec.attribute1
3167 ,p_attribute2_name => 'ATTRIBUTE2'
3168 ,p_attribute2_value => p_rec.attribute2
3169 ,p_attribute3_name => 'ATTRIBUTE3'
3170 ,p_attribute3_value => p_rec.attribute3
3171 ,p_attribute4_name => 'ATTRIBUTE4'
3172 ,p_attribute4_value => p_rec.attribute4
3173 ,p_attribute5_name => 'ATTRIBUTE5'
3174 ,p_attribute5_value => p_rec.attribute5
3175 ,p_attribute6_name => 'ATTRIBUTE6'
3176 ,p_attribute6_value => p_rec.attribute6
3177 ,p_attribute7_name => 'ATTRIBUTE7'
3178 ,p_attribute7_value => p_rec.attribute7
3179 ,p_attribute8_name => 'ATTRIBUTE8'
3180 ,p_attribute8_value => p_rec.attribute8
3181 ,p_attribute9_name => 'ATTRIBUTE9'
3182 ,p_attribute9_value => p_rec.attribute9
3183 ,p_attribute10_name => 'ATTRIBUTE10'
3184 ,p_attribute10_value => p_rec.attribute10
3185 ,p_attribute11_name => 'ATTRIBUTE11'
3186 ,p_attribute11_value => p_rec.attribute11
3187 ,p_attribute12_name => 'ATTRIBUTE12'
3188 ,p_attribute12_value => p_rec.attribute12
3189 ,p_attribute13_name => 'ATTRIBUTE13'
3190 ,p_attribute13_value => p_rec.attribute13
3191 ,p_attribute14_name => 'ATTRIBUTE14'
3192 ,p_attribute14_value => p_rec.attribute14
3193 ,p_attribute15_name => 'ATTRIBUTE15'
3194 ,p_attribute15_value => p_rec.attribute15
3195 ,p_attribute16_name => 'ATTRIBUTE16'
3196 ,p_attribute16_value => p_rec.attribute16
3197 ,p_attribute17_name => 'ATTRIBUTE17'
3198 ,p_attribute17_value => p_rec.attribute17
3199 ,p_attribute18_name => 'ATTRIBUTE18'
3200 ,p_attribute18_value => p_rec.attribute18
3201 ,p_attribute19_name => 'ATTRIBUTE19'
3202 ,p_attribute19_value => p_rec.attribute19
3203 ,p_attribute20_name => 'ATTRIBUTE20'
3204 ,p_attribute20_value => p_rec.attribute20
3205 );
3206 end if;
3207 --
3208 hr_utility.set_location(' Leaving:'||l_proc,20);
3209 end chk_df;
3210 --
3211 -- ----------------------------------------------------------------------------
3212 -- |-----------------------< chk_non_updateable_args >------------------------|
3213 -- ----------------------------------------------------------------------------
3214 -- {Start Of Comments}
3215 --
3216 -- Description:
3217 -- This procedure is used to ensure that non updateable attributes have
3218 -- not been updated. If an attribute has been updated an error is generated.
3219 --
3220 -- Pre Conditions:
3221 -- g_old_rec has been populated with details of the values currently in
3222 -- the database.
3223 --
3224 -- In Arguments:
3225 -- p_rec has been populated with the updated values the user would like the
3226 -- record set to.
3227 --
3228 -- Post Success:
3229 -- Processing continues if all the non updateable attributes have not
3230 -- changed.
3231 --
3232 -- Post Failure:
3233 -- An application error is raised if any of the non updatable attributes
3234 -- have been altered.
3235 --
3236 -- {End Of Comments}
3237 -- ----------------------------------------------------------------------------
3238 Procedure chk_non_updateable_args
3239 (p_effective_date in date
3240 ,p_rec in per_abs_shd.g_rec_type
3241 ) IS
3242 --
3243 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
3244 l_error EXCEPTION;
3245 l_argument varchar2(30);
3246 --
3247 Begin
3248 --
3249 -- Only proceed with the validation if a row exists for the current
3250 -- record in the HR Schema.
3251 --
3252 IF NOT per_abs_shd.api_updating
3253 (p_absence_attendance_id => p_rec.absence_attendance_id
3254 ,p_object_version_number => p_rec.object_version_number
3255 ) THEN
3256 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
3257 fnd_message.set_token('PROCEDURE ', l_proc);
3258 fnd_message.set_token('STEP ', '5');
3259 fnd_message.raise_error;
3260 END IF;
3261 --
3262 hr_utility.set_location(l_proc, 2);
3263 if nvl(p_rec.absence_attendance_id,hr_api.g_number) <>
3264 per_abs_shd.g_old_rec.absence_attendance_id then
3265 l_argument := 'absence_attendance_id';
3266 raise l_error;
3267 end if;
3268 --
3269 hr_utility.set_location(l_proc, 3);
3270 if nvl(p_rec.business_group_id, hr_api.g_number) <>
3271 per_abs_shd.g_old_rec.business_group_id then
3272 l_argument := 'business_group_id';
3273 raise l_error;
3274 end if;
3275 --
3276 hr_utility.set_location(l_proc, 4);
3277 if nvl(p_rec.person_id, hr_api.g_number) <>
3278 per_abs_shd.g_old_rec.person_id then
3279 l_argument := 'person_id';
3280 raise l_error;
3281 end if;
3282 --
3283 hr_utility.set_location(l_proc, 5);
3284 if nvl(p_rec.absence_attendance_type_id,hr_api.g_number) <>
3285 per_abs_shd.g_old_rec.absence_attendance_type_id then
3286 l_argument := 'absence_attendance_type_id';
3287 raise l_error;
3288 end if;
3289 --
3290 hr_utility.set_location(l_proc, 6);
3291 if nvl(p_rec.occurrence,hr_api.g_number) <>
3292 per_abs_shd.g_old_rec.occurrence then
3293 l_argument := 'occurrence';
3294 raise l_error;
3295 end if;
3296 --
3297 EXCEPTION
3298 WHEN l_error THEN
3299 hr_api.argument_changed_error
3300 (p_api_name => l_proc
3301 ,p_argument => l_argument);
3302 WHEN OTHERS THEN
3303 RAISE;
3304 End chk_non_updateable_args;
3305 --
3306 -- ----------------------------------------------------------------------------
3307 -- |---------------------------< insert_validate >----------------------------|
3308 -- ----------------------------------------------------------------------------
3309 Procedure insert_validate
3310 (p_effective_date in date
3311 ,p_rec in per_abs_shd.g_rec_type
3312 ,p_dur_dys_less_warning out nocopy boolean
3313 ,p_dur_hrs_less_warning out nocopy boolean
3314 ,p_exceeds_pto_entit_warning out nocopy boolean
3315 ,p_exceeds_run_total_warning out nocopy boolean
3316 ,p_abs_overlap_warning out nocopy boolean
3317 ,p_abs_day_after_warning out nocopy boolean
3318 ,p_dur_overwritten_warning out nocopy boolean
3319 ) is
3320 --
3321 l_proc varchar2(72) := g_package||'insert_validate';
3322 l_effective_date date; -- Added for bug 3371960
3323
3324 ----Check the gender of the person in case of maternity leave bug# 6505054
3325
3326 cursor c_absence_cat(p_absence_attendance_type_id in number) is
3327 select absence_category from per_absence_attendance_types
3328 where absence_attendance_type_id = p_absence_attendance_type_id;
3329
3330 l_ssp_installed boolean;
3331 l_absence_category varchar2(30);
3332
3333 --
3334 Begin
3335
3336 hr_utility.set_location('Entering:'||l_proc, 5);
3337
3338 --
3339 -- Assign the durations to the global variables.
3340 --
3341 per_abs_shd.g_absence_days := p_rec.absence_days;
3342 per_abs_shd.g_absence_hours := p_rec.absence_hours;
3343
3344 --
3345 -- Fix for bug 3371960 starts here. Use the l_effective_date
3346 -- in chk procedures.
3347 --
3348 l_effective_date := NVL(p_rec.date_start, p_effective_date);
3349 --
3350 -- Fix for bug 3371960 ends here.
3351 --
3352 -- Check the business group id.
3353 --
3354 hr_api.validate_bus_grp_id(p_rec.business_group_id);
3355
3356 hr_utility.set_location(l_proc, 10);
3357
3358 ----Check the gender of the person in case of maternity leave bug# 6505054
3359
3360 open c_absence_cat(p_rec.absence_attendance_type_id);
3361 fetch c_absence_cat into l_absence_category;
3362 close c_absence_cat;
3363
3364 l_ssp_installed := ssp_ssp_pkg.ssp_is_installed;
3365
3366 if l_ssp_installed and l_absence_category = 'M' then
3367
3368 ssp_mat_bus.validate_female_sex(p_rec.person_id);
3369
3370 end if;
3371
3372 -- end for bug# 6505054
3373
3374 --
3375 -- Check person ID.
3376 --
3377 chk_person_id
3378 (p_absence_attendance_id => p_rec.absence_attendance_id
3379 ,p_person_id => p_rec.person_id
3380 ,p_business_group_id => p_rec.business_group_id
3381 ,p_object_version_number => p_rec.object_version_number
3382 ,p_date_projected_start => p_rec.date_projected_start
3383 ,p_date_projected_end => p_rec.date_projected_end
3384 ,p_date_start => p_rec.date_start
3385 ,p_date_end => p_rec.date_end);
3386
3387 --
3388 -- Check absence attendance type ID
3389 --
3390 chk_absence_attendance_type_id
3391 (p_absence_attendance_id => p_rec.absence_attendance_id
3392 ,p_absence_attendance_type_id => p_rec.absence_attendance_type_id
3393 ,p_business_group_id => p_rec.business_group_id
3394 ,p_object_version_number => p_rec.object_version_number
3395 ,p_date_projected_start => p_rec.date_projected_start
3396 ,p_date_projected_end => p_rec.date_projected_end
3397 ,p_date_start => p_rec.date_start
3398 ,p_date_end => p_rec.date_end);
3399
3400 --
3401 -- Check the absence reason ID.
3402 --
3403 chk_abs_attendance_reason_id
3404 (p_absence_attendance_id => p_rec.absence_attendance_id
3405 ,p_absence_attendance_type_id => p_rec.absence_attendance_type_id
3406 ,p_abs_attendance_reason_id => p_rec.abs_attendance_reason_id
3407 ,p_business_group_id => p_rec.business_group_id
3408 ,p_object_version_number => p_rec.object_version_number
3409 ,p_effective_date => l_effective_date);
3410
3411 --
3412 -- Check absence period.
3413 --
3414 -- Check the absence period (this includes all duration validation).
3415 -- The durations are in out parameters and are assigned to global
3416 -- variables. This is because they will be over-written during
3417 -- pre insert / pre update if the Fast Formula Auto-Overwrite duration
3418 -- profile option is set to yes during pre_insert.
3419 --
3420 chk_absence_period
3421 (p_absence_attendance_id => p_rec.absence_attendance_id
3422 ,p_absence_attendance_type_id => p_rec.absence_attendance_type_id
3423 ,p_business_group_id => p_rec.business_group_id
3424 ,p_object_version_number => p_rec.object_version_number
3425 ,p_effective_date => l_effective_date
3426 ,p_person_id => p_rec.person_id
3427 ,p_date_projected_start => p_rec.date_projected_start
3428 ,p_time_projected_start => p_rec.time_projected_start
3429 ,p_date_projected_end => p_rec.date_projected_end
3430 ,p_time_projected_end => p_rec.time_projected_end
3431 ,p_date_start => p_rec.date_start
3432 ,p_time_start => p_rec.time_start
3433 ,p_date_end => p_rec.date_end
3434 ,p_time_end => p_rec.time_end
3435 ,p_absence_days => per_abs_shd.g_absence_days
3436 ,p_absence_hours => per_abs_shd.g_absence_hours
3437 ,p_dur_dys_less_warning => p_dur_dys_less_warning
3438 ,p_dur_hrs_less_warning => p_dur_hrs_less_warning
3439 ,p_exceeds_pto_entit_warning => p_exceeds_pto_entit_warning
3440 ,p_exceeds_run_total_warning => p_exceeds_run_total_warning
3441 ,p_abs_overlap_warning => p_abs_overlap_warning
3442 ,p_abs_day_after_warning => p_abs_day_after_warning
3443 ,p_dur_overwritten_warning => p_dur_overwritten_warning);
3444
3445 --
3446 -- Check the replacement person ID
3447 --
3448 chk_replacement_person_id
3449 (p_absence_attendance_id => p_rec.absence_attendance_id
3450 ,p_replacement_person_id => p_rec.replacement_person_id
3451 ,p_business_group_id => p_rec.business_group_id
3452 ,p_object_version_number => p_rec.object_version_number
3453 ,p_date_projected_start => p_rec.date_projected_start
3454 ,p_date_projected_end => p_rec.date_projected_end
3455 ,p_date_start => p_rec.date_start
3456 ,p_date_end => p_rec.date_end);
3457
3458 --
3459 -- Check the authorising person ID
3460 --
3461 chk_authorising_person_id
3462 (p_absence_attendance_id => p_rec.absence_attendance_id
3463 ,p_authorising_person_id => p_rec.authorising_person_id
3464 ,p_business_group_id => p_rec.business_group_id
3465 ,p_object_version_number => p_rec.object_version_number
3466 ,p_date_projected_start => p_rec.date_projected_start
3467 ,p_date_projected_end => p_rec.date_projected_end
3468 ,p_date_start => p_rec.date_start
3469 ,p_date_end => p_rec.date_end);
3470
3471
3472 hr_utility.set_location(l_proc, 24);
3473 --
3474 per_abs_bus.chk_ddf(p_rec);
3475 --
3476 per_abs_bus.chk_df(p_rec);
3477 --
3478 hr_utility.set_location(' Leaving:'||l_proc, 10);
3479 End insert_validate;
3480 --
3481 -- ----------------------------------------------------------------------------
3482 -- |---------------------------< update_validate >----------------------------|
3483 -- ----------------------------------------------------------------------------
3484 Procedure update_validate
3485 (p_effective_date in date
3486 ,p_rec in per_abs_shd.g_rec_type
3487 ,p_dur_dys_less_warning out nocopy boolean
3488 ,p_dur_hrs_less_warning out nocopy boolean
3489 ,p_exceeds_pto_entit_warning out nocopy boolean
3490 ,p_exceeds_run_total_warning out nocopy boolean
3491 ,p_abs_overlap_warning out nocopy boolean
3492 ,p_abs_day_after_warning out nocopy boolean
3493 ,p_dur_overwritten_warning out nocopy boolean
3494 ) is
3495 --
3496 l_proc varchar2(72) := g_package||'update_validate';
3497 l_effective_date date; -- Added for bug 3371960.
3498 --
3499 Begin
3500
3501 hr_utility.set_location('Entering:'||l_proc, 5);
3502 --
3503 -- Fix for bug 3371960 starts here.
3504 -- Use l_effective_date in different chk proceudre.
3505 --
3506 l_effective_date := NVL(p_rec.date_start, p_effective_date);
3507 --
3508 -- Fix for bug 3371960 ends here.
3509 --
3510 -- Assign the durations to the global variables.
3511 --
3512 per_abs_shd.g_absence_days := p_rec.absence_days;
3513 per_abs_shd.g_absence_hours := p_rec.absence_hours;
3514
3515 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
3516 --
3517 chk_non_updateable_args
3518 (p_effective_date => p_effective_date
3519 ,p_rec => p_rec
3520 );
3521 --
3522 hr_utility.set_location(l_proc, 14);
3523
3524 --
3525 -- Check person ID. This procedure is called during update_validate
3526 -- because the person must be valid for the entire absence period
3527 -- (which can be updated).
3528 --
3529 chk_person_id
3530 (p_absence_attendance_id => p_rec.absence_attendance_id
3531 ,p_person_id => p_rec.person_id
3532 ,p_business_group_id => p_rec.business_group_id
3533 ,p_object_version_number => p_rec.object_version_number
3534 ,p_date_projected_start => p_rec.date_projected_start
3535 ,p_date_projected_end => p_rec.date_projected_end
3536 ,p_date_start => p_rec.date_start
3537 ,p_date_end => p_rec.date_end);
3538
3539 --
3540 -- Check absence attendance type ID. This is called from
3541 -- update_validate because the absence type must be valid for
3542 -- the entire absence period (which can be updated).
3543 --
3544 chk_absence_attendance_type_id
3545 (p_absence_attendance_id => p_rec.absence_attendance_id
3546 ,p_absence_attendance_type_id => p_rec.absence_attendance_type_id
3547 ,p_business_group_id => p_rec.business_group_id
3548 ,p_object_version_number => p_rec.object_version_number
3549 ,p_date_projected_start => p_rec.date_projected_start
3550 ,p_date_projected_end => p_rec.date_projected_end
3551 ,p_date_start => p_rec.date_start
3552 ,p_date_end => p_rec.date_end);
3553
3554 --
3555 -- Check the absence reason ID.
3556 --
3557 chk_abs_attendance_reason_id
3558 (p_absence_attendance_id => p_rec.absence_attendance_id
3559 ,p_absence_attendance_type_id => p_rec.absence_attendance_type_id
3560 ,p_abs_attendance_reason_id => p_rec.abs_attendance_reason_id
3561 ,p_business_group_id => p_rec.business_group_id
3562 ,p_object_version_number => p_rec.object_version_number
3563 ,p_effective_date => l_effective_date);
3564
3565 --
3566 -- Check absence period.
3567 --
3568 -- Check the absence period (this includes all duration validation).
3569 -- The durations are in out parameters and are assigned to global
3570 -- variables. This is because they will be over-written during
3571 -- pre insert / pre update if the Fast Formula Auto-Overwrite duration
3572 -- profile option is set to yes during pre-update.
3573 --
3574 chk_absence_period
3575 (p_absence_attendance_id => p_rec.absence_attendance_id
3576 ,p_absence_attendance_type_id => p_rec.absence_attendance_type_id
3577 ,p_business_group_id => p_rec.business_group_id
3578 ,p_object_version_number => p_rec.object_version_number
3579 ,p_effective_date => l_effective_date
3580 ,p_person_id => p_rec.person_id
3581 ,p_date_projected_start => p_rec.date_projected_start
3582 ,p_time_projected_start => p_rec.time_projected_start
3583 ,p_date_projected_end => p_rec.date_projected_end
3584 ,p_time_projected_end => p_rec.time_projected_end
3585 ,p_date_start => p_rec.date_start
3586 ,p_time_start => p_rec.time_start
3587 ,p_date_end => p_rec.date_end
3588 ,p_time_end => p_rec.time_end
3589 ,p_absence_days => per_abs_shd.g_absence_days
3590 ,p_absence_hours => per_abs_shd.g_absence_hours
3591 ,p_dur_dys_less_warning => p_dur_dys_less_warning
3592 ,p_dur_hrs_less_warning => p_dur_hrs_less_warning
3593 ,p_exceeds_pto_entit_warning => p_exceeds_pto_entit_warning
3594 ,p_exceeds_run_total_warning => p_exceeds_run_total_warning
3595 ,p_abs_overlap_warning => p_abs_overlap_warning
3596 ,p_abs_day_after_warning => p_abs_day_after_warning
3597 ,p_dur_overwritten_warning => p_dur_overwritten_warning);
3598
3599 --
3600 -- Check the replacement person ID
3601 --
3602 chk_replacement_person_id
3603 (p_absence_attendance_id => p_rec.absence_attendance_id
3604 ,p_replacement_person_id => p_rec.replacement_person_id
3605 ,p_business_group_id => p_rec.business_group_id
3606 ,p_object_version_number => p_rec.object_version_number
3607 ,p_date_projected_start => p_rec.date_projected_start
3608 ,p_date_projected_end => p_rec.date_projected_end
3609 ,p_date_start => p_rec.date_start
3610 ,p_date_end => p_rec.date_end);
3611
3612 --
3613 -- Check the authorising person ID
3614 --
3615 chk_authorising_person_id
3616 (p_absence_attendance_id => p_rec.absence_attendance_id
3617 ,p_authorising_person_id => p_rec.authorising_person_id
3618 ,p_business_group_id => p_rec.business_group_id
3619 ,p_object_version_number => p_rec.object_version_number
3620 ,p_date_projected_start => p_rec.date_projected_start
3621 ,p_date_projected_end => p_rec.date_projected_end
3622 ,p_date_start => p_rec.date_start
3623 ,p_date_end => p_rec.date_end);
3624
3625
3626 hr_utility.set_location(l_proc, 24);
3627 --
3628 per_abs_bus.chk_ddf(p_rec);
3629 --
3630 per_abs_bus.chk_df(p_rec);
3631 --
3632 hr_utility.set_location(' Leaving:'||l_proc, 10);
3633 End update_validate;
3634 --
3635 -- ----------------------------------------------------------------------------
3636 -- |---------------------------< delete_validate >----------------------------|
3637 -- ----------------------------------------------------------------------------
3638 Procedure delete_validate
3639 (p_rec in per_abs_shd.g_rec_type
3640 ) is
3641 --
3642 l_proc varchar2(72) := g_package||'delete_validate';
3643 --
3644 Begin
3645 hr_utility.set_location('Entering:'||l_proc, 5);
3646 --
3647 -- Call all supporting business operations
3648 --
3649 hr_utility.set_location(' Leaving:'||l_proc, 10);
3650 End delete_validate;
3651 --
3652 end per_abs_bus;
[Home] [Help]