[Home] [Help]
PACKAGE BODY: APPS.PER_ABSENCE_ATTENDANCES_PKG3
Source
1 PACKAGE BODY PER_ABSENCE_ATTENDANCES_PKG3 as
2 /* $Header: peaba03t.pkb 120.0 2005/05/31 04:40:30 appldev noship $ */
3 --
4 /*
5 NAME
6 peaba03t.pkb -- procedure Absence API
7 --
8 DESCRIPTION
9 This package is used as an interface between the PayMIX form and the
10 Absence api.
11 --
12 MODIFIED (DD-MON-YYYY) COMMENT
13 btailor 28-Jun-95 Created
14 rfine 70.3 12-Jul-95 Populated the notification date with the
15 session date - it's used to select a single
16 row from datetracked join tables in the
17 base view for PERWSEAD, so should not be left
18 NULL.
19 rfine 70.5 23-Nov-95 Added new SSP-related args to call to
20 per_absence_attendances_pkg.insert_row. They
21 are defined as DEFAULTs, which means they
22 shouldn't really be necessary, but they appear
23 to be.
24 ctredwin 110.2 16-Aug-99 Bug 953648. Handle null value from cursor
25 when fetching occurrence number during
26 insert.
27 ctredwin 110.3 01-Oct-99 Added insert_abs_for_bee,
28 insert_validate_for_bee, and validation
29 procedures.
30 ctredwin 115.5 07-Feb-00 Bug 1184545. Use derived start and end times
31 in validation.
32 ctredwin 115.6 24-Jul-99 Bug 1337672. Altered message name in check
33 duration procedure.
34 dcasemor 115.7 28-Aug-01 Bug 1668275. Replaced the table handler call
35 'insert_row' with the create absence API.
36 This was only changed for insert_abs_for_bee
37 because paymix is no longer used in 11i.
38 dcasemor 115.8 21-Dec-01 Passed out nocopy an additional warning to indicate
39 that the absence API has already created the
40 element entry.
41 dcasemor 115.9 28-Dec-01 Added check_dates_entered procedure.
42 dcasemor 115.10 21-May-02 Bug 2377104. Passed p_create_element_entry
43 to the absence API as false so that BEE
44 creates the element entries. Warnings of
45 EE_CREATED_BY_ABSENCE are no longer passed
46 back to BEE (so it always creates the EE).
47 dcasemor 115.11 14-Aug-02 GSCC compliance - added WHENEVER OSERROR...
48 adudekul 115.13 18-FEB-04 Bug 3307340. Modified procedure INSER_VALIDATE_FOR_BEE
49 to treat the errors as warnings which are raised
50 for sickness overlap.
51 kjagadee 115.14 23-FEB-04 Added overloaded proc for insert_abs_for_bee
52 kjagadee 115.15 05-APR-04 Bug 3506133, Modified procedure insert_abs_for_bee
53 (one which is called from BEE)
54 Added new private proc insert_absence_element.
55 Added package variable g_package.
56 kjagadee 115.16 19-MAY-04 Bug 3626565, Modified CHK_ABSENCE_INPUT to relax
57 the input validation, so that user can enter
58 negative absence duration through BEE.
59 SuSivasu 115.17 20-Aug-04 Bug 3812684. Assed support for sickness start and
60 end date for UK's sickness attendance types.
61 smparame 115.18 05-Oct-04 Bug 3900409. Replaced call to hr_cal_abs_dur_pkg.
62 calculate_absence_duration in check_duration with
63 per_abs_bus.calculate_absence_duration.
64
65 */
66 --
67 -- Package Variables
68 --
69 g_package varchar2(33) := ' per_absence_attendances_pkg3.';
70 --
71 procedure check_dates_entered(p_date_end In DATE,
72 p_absence_days In NUMBER,
73 p_absence_hours In NUMBER,
74 p_message out nocopy VARCHAR2) is
75
76 l_message varchar2(30) := null;
77
78 begin
79 --
80 IF p_date_end is null and
81 (p_absence_days is not null or p_absence_hours is not null) THEN
82
83 l_message := 'PER_7714_ABS_CALC_DURATION';
84
85 END IF;
86
87 p_message := l_message;
88
89 --
90 end check_dates_entered;
91
92
93 procedure check_absence_dates(p_date_start In DATE,
94 p_date_end In DATE,
95 p_message out nocopy VARCHAR2) is
96
97 l_message varchar2(30) := null;
98
99 begin
100 --
101 IF p_date_start > p_date_end THEN
102 --
103 l_message := 'PAY_7616_EMP_ABS_DATE_AFTER';
104 --
105 END IF;
106
107 p_message := l_message;
108 --
109 end check_absence_dates;
110
111 procedure check_absence_type(p_abs_type_id In NUMBER,
112 p_date_start IN DATE,
113 p_eot IN DATE,
114 p_date_end IN DATE,
115 p_message OUT NOCOPY VARCHAR2) is
116
117 l_exists VARCHAR2(1);
118 l_message VARCHAR2(30) := null;
119
120 cursor c5 is
121 select 'x'
122 from per_absence_attendance_types
123 where absence_attendance_type_id = p_abs_type_id
124 and date_effective <= p_date_start
125 and (nvl(date_end,p_eot) >= p_date_end or
126 p_date_end is null);
127 --
128 begin
129 --
130 open c5;
131 --
132 fetch c5 into l_exists;
133
134 IF c5%notfound THEN
135 --
136 l_message := 'HR_6847_ABS_DET_RANGE_CHECK';
137 --
138 END IF;
139 --
140 close c5;
141 p_message := l_message;
142 --
143 end check_absence_type;
144
145 procedure check_duration(p_date_start in date,
146 p_date_end in date,
147 p_time_start in varchar2,
148 p_time_end in varchar2,
149 p_business_group_id in number,
150 p_session_date in date,
151 p_assignment_id in number,
152 p_person_id in number, -- Bug 3900409
153 p_absence_days in number,
154 p_absence_hours in number,
155 p_abs_type_id in number,
156 p_error out nocopy varchar2,
157 p_warning out nocopy varchar2) is
158
159 l_use_formula boolean;
160 l_duration number;
161 l_invalid_message varchar2(240);
162 l_element_type_id number;
163 l_legislation_code varchar2(30);
164 l_days_or_hours varchar2(30);
165 l_hours_default number;
166 l_days_in_hours number;
167 l_days_default number;
168 l_warning varchar2(30) := null;
169 l_error varchar2(30) := null;
170 l_absence_days number;
171 l_absence_hours number;
172
173 cursor c1 is
174 select piv.element_type_id,
175 abt.hours_or_days
176 from per_absence_attendance_types abt,
177 pay_input_values_f piv
178 where abt.absence_attendance_type_id = p_abs_type_id
179 and abt.input_value_id = piv.input_value_id
180 and p_date_start between piv.effective_start_date
181 and piv.effective_end_date;
182
183 cursor c2 is
184 select legislation_code
185 from per_business_groups
186 where business_group_id = p_business_group_id;
187
188
189 begin
190
191 open c1;
192 fetch c1 into l_element_type_id, l_days_or_hours;
193 close c1;
194
195 open c2;
196 fetch c2 into l_legislation_code;
197 close c2;
198
199 -- bug fix 3900409.
200 -- Replace the call with per_abs_bus.calculate_absence_duration.
201
202 /*hr_cal_abs_dur_pkg.calculate_absence_duration (
203 p_days_or_hours => l_days_or_hours,
204 p_date_start => p_date_start,
205 p_date_end => p_date_end,
206 p_time_start => null,
207 p_time_end => null,
208 p_business_group_id => p_business_group_id,
209 p_legislation_code => l_legislation_code,
210 p_session_date => p_session_date,
211 p_assignment_id => p_assignment_id,
212 p_element_type_id => l_element_type_id,
213 p_invalid_message => l_invalid_message,
214 p_duration => l_duration,
215 p_use_formula => l_use_formula
216 );*/
217
218 per_abs_bus.calculate_absence_duration
219 (p_absence_attendance_id => NULL
220 ,p_absence_attendance_type_id => p_abs_type_id
221 ,p_business_group_id => p_business_group_id
222 ,p_object_version_number => NULL
223 ,p_effective_date => p_session_date
224 ,p_person_id => p_person_id
225 ,p_date_start => p_date_start
226 ,p_date_end => p_date_end
227 ,p_time_start => p_time_start
228 ,p_time_end => p_time_end
229 ,p_absence_days => l_absence_days
230 ,p_absence_hours => l_absence_hours
231 ,p_use_formula => l_use_formula);
232
233 if l_use_formula then
234 --
235 /*if nvl(p_absence_days, p_absence_hours) <> l_duration then
236 --
237 l_warning := 'HR_EMP_ABS_DURATION_FORMULA';
238 --
239 end if;*/
240 if ( l_days_or_hours = 'H' and p_absence_hours <> l_absence_hours )
241 OR ( l_days_or_hours = 'D' and p_absence_days <> l_absence_days )then
242
243 l_warning := 'HR_EMP_ABS_DURATION_FORMULA';
244 end if;
245 --
246 else
247 --
248
249 per_absence_attendances_pkg.get_defaults(p_time_end,
250 p_time_start,
251 p_date_end,
252 p_date_start,
253 l_hours_default,
254 l_days_in_hours,
255 l_days_default);
256
257 if l_days_or_hours = 'D' then
258 --
259 if l_days_default > p_absence_days then
260 --
261 l_warning := 'HR_EMP_ABS_SHORT_DURATION';
262 --
263 elsif l_days_default < p_absence_days then
264 --
265 l_error := 'PER_7622_EMP_ABS_LONG_DURATION';
266 --
267 end if;
268 --
269 elsif l_days_or_hours = 'H' then
270 --
271 if nvl(l_hours_default, l_days_in_hours) > p_absence_hours then
272 --
273 l_warning := 'HR_EMP_ABS_SHORT_DURATION';
274 --
275 elsif nvl(l_hours_default, l_days_in_hours) < p_absence_hours then
276 --
277 l_error := 'PER_7623_EMP_ABS_LONG_DURATION';
278 --
279 end if;
280 --
281 end if;
282 --
283 end if;
284
285 p_warning := l_warning;
286 p_error := l_error;
287
288 end check_duration;
289
290 procedure check_sickness_overlap(p_person_id IN NUMBER,
291 p_abs_type_id IN NUMBER,
292 p_date_start IN DATE,
293 p_date_end IN DATE,
294 p_eot IN DATE,
295 p_message1 OUT NOCOPY VARCHAR2,
296 p_message2 OUT NOCOPY VARCHAR2) is
297
298 l_exists VARCHAR2(1);
299 l_exists2 VARCHAR2(1);
300
301 cursor c1 is
302 select 'x'
303 from per_absence_attendances t,
304 per_absence_attendance_types a,
305 per_absence_attendance_types b
306 where t.person_id = p_person_id
307 and p_abs_type_id = a.absence_attendance_type_id
308 and t.absence_attendance_type_id = b.absence_attendance_type_id
309 and a.absence_category = 'S'
310 and b.absence_category = 'S'
311 and p_date_end is null
312 and t.date_end is null;
313 --
314 cursor c2 is
315 select 'x'
316 from per_absence_attendances t,
317 per_absence_attendance_types a,
318 per_absence_attendance_types b
319 where t.person_id = p_person_id
320 and p_abs_type_id = b.absence_attendance_type_id
321 and t.absence_attendance_type_id = a.absence_attendance_type_id
322 and a.absence_category = 'S'
323 and b.absence_category = 'S'
324 and ((p_date_start between t.date_start and nvl(t.date_end,p_eot))
325 or (t.date_start between p_date_start and nvl(p_date_end,p_eot)));
326 --
327 begin
328 --
329 open c1;
330 fetch c1 into l_exists;
331
332 IF c1%found THEN
333 --
334 p_message1 := 'SSP_35217_DEF_ONLY_ONE_ABS';
335 --
336 END IF;
337 --
338 close c1;
339
340 open c2;
341 fetch c2 into l_exists2;
342
343 IF c2%found THEN
344 --
345 p_message2 := 'SSP_35216_DEF_OVERLAP_ABS';
346 --
347 END IF;
348
349 close c2;
350 --
351 end check_sickness_overlap;
352 --
353 -- ----------------------------------------------------------------------------
354 -- |----------------------< insert_absence_element >--------------------------|
355 -- ----------------------------------------------------------------------------
356 --
357 procedure insert_absence_element(
358 p_line_record in pay_batch_lines%Rowtype,
359 p_asg_act_id in number,
360 p_absence_attendance_id in number,
361 p_absence_att_type_id in number,
362 p_entry_values_count in number,
363 p_date_start in date,
364 p_date_end in date,
365 p_passed_inp_tbl in hr_entry.number_table,
366 p_passed_val_tbl in hr_entry.varchar2_table
367 ) is
368 --
369 l_proc varchar2(72) := g_package||'insert_absence_element';
370 -- Local variables
371 l_effective_end_date date;
372 l_effective_start_date date := p_line_record.effective_date;
373 l_count number := 1;
374 l_entry_value number;
375 l_element_type_id number;
376 l_input_value_id number;
377 l_reason hr_lookups.lookup_code%Type;
378 l_processing_type pay_element_types_f.processing_type%Type;
379 l_element_link_id pay_element_links_f.element_link_id%Type;
380 l_element_entry_id pay_element_entries_f.element_entry_id%Type;
381 l_passed_inp_tbl hr_entry.number_table;
382 l_passed_val_tbl hr_entry.varchar2_table;
383 -- Cursor to pickup the lookup code
384 cursor csr_lookup_code(p_meaning hr_lookups.meaning%Type) is
385 select hl.lookup_code
386 from hr_lookups hl
387 where hl.lookup_type = 'ELE_ENTRY_REASON'
388 and hl.meaning = p_meaning;
389 --
390 begin
391 --
392 hr_utility.set_location('Entering:'|| l_proc, 10);
393 --
394 -- Insert the absence element element. First we check if the
395 -- absence type is linked to an element type.
396 if hr_person_absence_api.linked_to_element(
397 p_absence_attendance_id => p_absence_attendance_id) then
398 --
399 hr_utility.set_location(l_proc, 20);
400 -- Getting the processing type of the absence element
401 l_processing_type := hr_person_absence_api.get_processing_type(
402 p_absence_attendance_type_id => p_absence_att_type_id);
403 --
404 if (l_processing_type = 'N' and p_date_start is not null
405 and p_date_end is not null) or
406 (l_processing_type = 'R' and p_date_start is not null) then
407 --
408 hr_utility.set_location(l_proc, 30);
409 -- Getting the element details
410 hr_person_absence_api.get_element_details(
411 p_absence_attendance_id => p_absence_attendance_id,
412 p_element_type_id => l_element_type_id,
413 p_input_value_id => l_input_value_id,
414 p_entry_value => l_entry_value);
415 -- Checking element link
416 hr_utility.set_location(l_proc, 40);
417 --
418 l_element_link_id := hr_entry_api.get_link(
419 p_assignment_id => p_line_record.assignment_id,
420 p_element_type_id => l_element_type_id,
421 p_session_date => p_date_start);
422 --
423 if l_element_link_id is null then
424 -- Assignment is not eligible for the element type
425 -- associated with this absence.
426 hr_utility.set_message(801,'HR_7448_ELE_PER_NOT_ELIGIBLE');
427 hr_utility.raise_error;
428 --
429 end if;
430 -- We know the assignment is eligible for this element because
431 -- we have the element_link_id. The entries API will handle
432 -- all other validation (e.g., non-recurring entries must
433 -- have a valid payroll).
434 if p_line_record.reason is not null then
435 --
436 open csr_lookup_code(p_line_record.reason);
437 fetch csr_lookup_code into l_reason;
438 close csr_lookup_code;
439 --
440 end if;
441 --
442 for i in 1..p_entry_values_count loop
443 --
444 if p_passed_val_tbl.exists(i)
445 and p_passed_val_tbl(i) is not null then
446 --
447 l_passed_inp_tbl(l_count) := p_passed_inp_tbl(i);
448 l_passed_val_tbl(l_count) := p_passed_val_tbl(i);
449 l_count := l_count + 1;
450 --
451 end if;
452 --
453 end loop;
454 --
455 hr_utility.set_location(l_proc, 50);
456 --
457 l_count := l_passed_val_tbl.count;
458 --
459 -- Calling the API to create EE
460 hr_entry_api.insert_element_entry(
461 p_effective_start_date => l_effective_start_date,
462 p_effective_end_date => l_effective_end_date,
463 p_element_entry_id => l_element_entry_id,
464 p_assignment_id => p_line_record.assignment_id,
465 p_element_link_id => l_element_link_id,
466 p_creator_type => 'A',
467 p_creator_id => p_absence_attendance_id,
468 p_entry_type => 'E',
469 p_cost_allocation_keyflex_id => p_line_record.cost_allocation_keyflex_id,
470 p_reason => l_reason,
471 p_subpriority => p_line_record.subpriority,
472 p_date_earned => p_line_record.date_earned,
473 p_personal_payment_method_id => p_line_record.personal_payment_method_id,
474 p_attribute_category => p_line_record.attribute_category,
475 p_attribute1 => p_line_record.attribute1,
476 p_attribute2 => p_line_record.attribute2,
477 p_attribute3 => p_line_record.attribute3,
478 p_attribute4 => p_line_record.attribute4,
479 p_attribute5 => p_line_record.attribute5,
480 p_attribute6 => p_line_record.attribute6,
481 p_attribute7 => p_line_record.attribute7,
482 p_attribute8 => p_line_record.attribute8,
483 p_attribute9 => p_line_record.attribute9,
484 p_attribute10 => p_line_record.attribute10,
485 p_attribute11 => p_line_record.attribute11,
486 p_attribute12 => p_line_record.attribute12,
487 p_attribute13 => p_line_record.attribute13,
488 p_attribute14 => p_line_record.attribute14,
489 p_attribute15 => p_line_record.attribute15,
490 p_attribute16 => p_line_record.attribute16,
491 p_attribute17 => p_line_record.attribute17,
492 p_attribute18 => p_line_record.attribute18,
493 p_attribute19 => p_line_record.attribute19,
494 p_attribute20 => p_line_record.attribute20,
495 p_entry_information_category => p_line_record.entry_information_category,
496 p_entry_information1 => p_line_record.entry_information1,
497 p_entry_information2 => p_line_record.entry_information2,
498 p_entry_information3 => p_line_record.entry_information3,
499 p_entry_information4 => p_line_record.entry_information4,
500 p_entry_information5 => p_line_record.entry_information5,
501 p_entry_information6 => p_line_record.entry_information6,
502 p_entry_information7 => p_line_record.entry_information7,
503 p_entry_information8 => p_line_record.entry_information8,
504 p_entry_information9 => p_line_record.entry_information9,
505 p_entry_information10 => p_line_record.entry_information10,
506 p_entry_information11 => p_line_record.entry_information11,
507 p_entry_information12 => p_line_record.entry_information12,
508 p_entry_information13 => p_line_record.entry_information13,
509 p_entry_information14 => p_line_record.entry_information14,
510 p_entry_information15 => p_line_record.entry_information15,
511 p_entry_information16 => p_line_record.entry_information16,
512 p_entry_information17 => p_line_record.entry_information17,
513 p_entry_information18 => p_line_record.entry_information18,
514 p_entry_information19 => p_line_record.entry_information19,
515 p_entry_information20 => p_line_record.entry_information20,
516 p_entry_information21 => p_line_record.entry_information21,
517 p_entry_information22 => p_line_record.entry_information22,
518 p_entry_information23 => p_line_record.entry_information23,
519 p_entry_information24 => p_line_record.entry_information24,
520 p_entry_information25 => p_line_record.entry_information25,
521 p_entry_information26 => p_line_record.entry_information26,
522 p_entry_information27 => p_line_record.entry_information27,
523 p_entry_information28 => p_line_record.entry_information28,
524 p_entry_information29 => p_line_record.entry_information29,
525 p_entry_information30 => p_line_record.entry_information30,
526 p_num_entry_values => l_count,
527 p_input_value_id_tbl => l_passed_inp_tbl,
528 p_entry_value_tbl => l_passed_val_tbl);
529 --
530 hr_utility.set_location(l_proc, 60);
531 -- Set the origin of the entry as the batch and its assignment action.
532 update pay_element_entries_f
533 set source_id = p_asg_act_id
534 where element_entry_id = l_element_entry_id;
535 -- Needs to end date the recurring Absence EE, if user has supplied an
536 -- absence end date through batch line.
537 if l_processing_type = 'R' and p_date_end is not null
538 and l_element_entry_id is not null then
539 --
540 hr_utility.set_location(l_proc, 70);
541 --
542 hr_entry_api.delete_element_entry(
543 p_dt_delete_mode => 'DELETE',
544 p_session_date => p_date_end,
545 p_element_entry_id => l_element_entry_id);
546 --
547 end if;
548 end if; -- End of procesing type and date check
549 end if; -- End of absence type and element type link check
550 --
551 hr_utility.set_location(' Leaving:'||l_proc, 99);
552 --
553 end insert_absence_element;
554 --
555 procedure check_absence_overlap(p_date_start In DATE,
556 p_date_end In DATE,
557 p_person_id In NUMBER,
558 p_abs_type_id In NUMBER,
559 p_message out nocopy VARCHAR2) is
560
561 l_message varchar2(30) := null;
562
563 begin
564 --
565 IF per_absence_attendances_pkg.chkab3(null,
566 p_person_id,
567 p_abs_type_id,
568 p_date_start,
569 p_date_end,
570 hr_general.end_of_time) THEN
571 --
572 l_message := 'HR_ABS_DET_OVERLAP';
573 --
574 END IF;
575
576 p_message := l_message;
577 --
578 end check_absence_overlap;
579
580 procedure check_pto_entitlement(p_date_start In DATE,
581 p_assignment_id In NUMBER,
582 p_abs_type_id In NUMBER,
583 p_absence_days In NUMBER,
584 p_absence_hours In NUMBER,
585 p_message out nocopy VARCHAR2) is
586
587 l_message varchar2(30) := null;
588
589 begin
590 --
591 IF not (per_absence_attendances_pkg.is_emp_entitled (
592 p_abs_type_id,
593 p_assignment_id,
594 p_date_start,
595 p_absence_days,
596 p_absence_hours)) THEN
597 --
598 l_message := 'HR_EMP_NOT_ENTITLED';
599 --
600 END IF;
601
602 p_message := l_message;
603 --
604 end check_pto_entitlement;
605
606 procedure check_absence_balance(p_session_date In DATE,
607 p_date_start In DATE,
608 p_abs_type_id In NUMBER,
609 p_assignment_id In NUMBER,
610 p_absence_days In NUMBER,
611 p_absence_hours In NUMBER,
612 p_message out nocopy VARCHAR2) is
613
614 l_message varchar2(30) := null;
615 l_balance number;
616 l_flag varchar2(30);
617
618 cursor c1 is
619 select increasing_or_decreasing_flag
620 from per_absence_attendance_types
621 where absence_attendance_type_id = p_abs_type_id;
622
623 begin
624 --
625 open c1;
626 fetch c1 into l_flag;
627 close c1;
628
629 if l_flag = 'D' then
630 --
631 l_balance := per_absence_attendances_pkg.get_annual_balance(
632 p_session_date => p_date_start,
633 p_abs_type_id => p_abs_type_id,
634 p_ass_id => p_assignment_id
635 );
636
637 if l_balance < nvl(p_absence_days, l_balance) or
638 l_balance < nvl(p_absence_hours, l_balance) then
639 --
640 l_message := 'HR_ABS_DET_RUNNING_ZERO';
641 --
642 end if;
643 --
644 end if;
645
646 p_message := l_message;
647 --
648 end check_absence_balance;
649
650 procedure check_previous_absence(p_date_start In DATE,
651 p_person_id In NUMBER,
652 p_abs_type_id In NUMBER,
653 p_message out nocopy VARCHAR2) is
654
655 l_message varchar2(30) := null;
656
657 begin
658 --
659 IF per_absence_attendances_pkg.chkab1(null,
660 p_person_id,
661 p_abs_type_id,
662 p_date_start) THEN
663 --
664 l_message := 'HR_ABS_DET_ABS_DAY_AFTER';
665 --
666 END IF;
667
668 p_message := l_message;
669 --
670 end check_previous_absence;
671
672
673
674 PROCEDURE insert_validate_for_bee(p_session_date in date,
675 p_date_start in date,
676 p_date_end in date,
677 p_time_start in varchar2,
678 p_time_end in varchar2,
679 p_absence_days in number,
680 p_absence_hours in number,
681 p_abs_type_id in number,
682 p_person_id in number,
683 p_assignment_id in number,
684 p_business_group_id in number,
685 p_warning_table out nocopy t_message_table,
686 p_error_table out nocopy t_message_table
687 ) IS
688
689 l_warning varchar2(30) := null;
690 l_error varchar2(30) := null;
691 l_error_count number := 1;
692 l_warning_count number := 1;
693 l_error_table t_message_table;
694 l_warning_table t_message_table;
695
696 begin
697
698 check_dates_entered(p_date_end => p_date_end,
699 p_absence_days => p_absence_days,
700 p_absence_hours => p_absence_hours,
701 p_message => l_error);
702
703 if l_error is not null then
704 --
705 l_error_table(l_error_count) := l_error;
706 l_error_count := l_error_count + 1;
707 --
708 end if;
709
710 check_previous_absence(p_date_start => p_date_start,
711 p_person_id => p_person_id,
712 p_abs_type_id => p_abs_type_id,
713 p_message => l_warning);
714
715 if l_warning is not null then
716 --
717 l_warning_table(l_warning_count) := l_warning;
718 l_warning_count := l_warning_count + 1;
719 --
720 end if;
721
722 check_absence_balance(p_session_date => p_session_date,
723 p_date_start => p_date_start,
724 p_abs_type_id => p_abs_type_id,
725 p_assignment_id => p_assignment_id,
726 p_absence_days => p_absence_days,
727 p_absence_hours => p_absence_hours,
728 p_message => l_warning);
729
730 if l_warning is not null then
731 --
732 l_warning_table(l_warning_count) := l_warning;
733 l_warning_count := l_warning_count + 1;
734 --
735 end if;
736
737 check_pto_entitlement(p_date_start => p_date_start,
738 p_assignment_id => p_assignment_id,
739 p_abs_type_id => p_abs_type_id,
740 p_absence_days => p_absence_days,
741 p_absence_hours => p_absence_hours,
742 p_message => l_warning);
743
744 if l_warning is not null then
745 --
746 l_warning_table(l_warning_count) := l_warning;
747 l_warning_count := l_warning_count + 1;
748 --
749 end if;
750
751 check_absence_overlap(p_date_start => p_date_start,
752 p_date_end => p_date_end,
753 p_person_id => p_person_id,
754 p_abs_type_id => p_abs_type_id,
755 p_message => l_warning);
756
757 if l_warning is not null then
758 --
759 l_warning_table(l_warning_count) := l_warning;
760 l_warning_count := l_warning_count + 1;
761 --
762 end if;
763
764 check_duration(p_date_start => p_date_start,
765 p_date_end => p_date_end,
766 p_time_start => p_time_start,
767 p_time_end => p_time_end,
768 p_business_group_id => p_business_group_id,
769 p_session_date => p_session_date,
770 p_assignment_id => p_assignment_id,
771 p_person_id => p_person_id, -- bug fix 3900409
772 p_absence_days => p_absence_days,
773 p_absence_hours => p_absence_hours,
774 p_abs_type_id => p_abs_type_id,
775 p_error => l_error,
776 p_warning => l_warning);
777
778 if l_warning is not null then
779 --
780 l_warning_table(l_warning_count) := l_warning;
781 l_warning_count := l_warning_count + 1;
782 --
783 end if;
784
785 if l_error is not null then
786 --
787 l_error_table(l_error_count) := l_error;
788 l_error_count := l_error_count + 1;
789 --
790 end if;
791
792 check_absence_type(p_abs_type_id => p_abs_type_id,
793 p_date_start => p_date_start,
794 p_eot => hr_general.end_of_time,
795 p_date_end => p_date_end,
796 p_message => l_error);
797
798 if l_error is not null then
799 --
800 l_error_table(l_error_count) := l_error;
801 l_error_count := l_error_count + 1;
802 --
803 end if;
804
805 check_absence_dates(p_date_start => p_date_start,
806 p_date_end => p_date_end,
807 p_message => l_error);
808
809 if l_error is not null then
810 --
811 l_error_table(l_error_count) := l_error;
812 l_error_count := l_error_count + 1;
813 --
814 end if;
815
816 check_sickness_overlap(p_person_id => p_person_id,
817 p_abs_type_id => p_abs_type_id,
818 p_date_start => p_date_start,
819 p_date_end => p_date_end,
820 p_eot => hr_general.end_of_time,
821 p_message1 => l_error,
822 p_message2 => l_warning);
823
824 --
825 -- If either of the above two messages are returned, they will
826 -- be errors. We use l_warning to save the definition of
827 -- another variable
828 --
829 -- Fix for bug 3307340 starts here.
830 -- For BEE, treat the errors as warnings. This is needed for retro BEE proccess.
831 --
832
833 if l_error is not null then
834 --
835 -- l_error_table(l_error_count) := l_error;
836 -- l_error_count := l_error_count + 1;
837 l_warning_table(l_warning_count) := l_error;
838 l_warning_count := l_warning_count + 1;
839 --
840 end if;
841
842 if l_warning is not null then
843 --
844 -- l_error_table(l_error_count) := l_warning;
845 -- l_error_count := l_error_count + 1;
846 l_warning_table(l_warning_count) := l_warning;
847 l_warning_count := l_warning_count + 1;
848 --
849 end if;
850 --
851 -- Fix for bug 3307340 ends here.
852 --
853 p_error_table := l_error_table;
854 p_warning_table := l_warning_table;
855
856 --
857 end insert_validate_for_bee;
858
859 PROCEDURE insert_abs_for_paymix(p_session_date in date,
860 p_absence_att_type_id in number,
861 p_assignment_id in number,
862 p_absence_days in number,
863 p_absence_hours in number,
864 p_date_start in date,
865 p_date_end in date) IS
866 --
867 -- Retrieves additional data from per_assignments_f table.
868 --
869 CURSOR C1 IS
870 SELECT a.business_group_id,
871 a.person_id,
872 a.payroll_id
873 FROM per_assignments_f a
874 WHERE a.assignment_id = p_assignment_id
875 AND p_session_date between a.effective_start_date
876 AND a.effective_end_date;
877 --
878 /*
879 -- Derives Element_type, input_value_id, the hours_or_days_flag which
880 -- determines whether the entry value is in days or hours and the
881 -- increment_or_decrement_flag which determines the sign of the entry value.
882 --
883 -- All element entry maintenance is now done directly by the PayMIX
884 -- form. Therefore it is commented out from the API, but preserved in
885 -- case the decision is reversed later.
886 --
887 CURSOR C2 IS
888 select iv.element_type_id,
889 a.hours_or_days,
890 a.increasing_or_decreasing_flag,
891 a.input_value_id
892 FROM pay_input_values_f iv,
893 per_absence_attendance_types a
894 WHERE a.absence_attendance_type_id = p_absence_att_type_id
895 AND a.input_value_id = iv.input_value_id (+);
896 */
897 --
898 l_max_occurrence number;
899 l_absence_att_id number;
900 l_row_id VARCHAR2(30);
901 l_person_id number;
902 l_business_group_id number;
903 l_assignment_id number;
904 l_date_start date;
905 l_date_end date;
906 l_payroll_id number;
907 --l_element_entry_id number;
908 --l_entry_value varchar2(30);
909 --l_element_link_id number;
910 --l_pay_id number;
911 --l_test varchar2(1);
912 --l_element_type_id number;
913 --l_hours_or_days varchar2(1);
914 --l_inc_or_dec_flag varchar2(1);
915 --l_ele_exists_flag varchar2(1);
916 --l_input_value_id number;
917 --
918 -- Finds the maximum absence occurrence for the required person.
919 -- has to be declared after l_person_id.
920 --
921 CURSOR C3 IS
922 SELECT max(occurrence)
923 FROM per_absence_attendances
924 WHERE person_id = l_person_id
925 AND absence_attendance_type_id = p_absence_att_type_id;
926 --
927 --
928 BEGIN
929 OPEN C1;
930 FETCH C1 INTO
931 l_business_group_id,
932 l_person_id,
933 l_payroll_id;
934 --
935 IF (C1%NOTFOUND) THEN
936 CLOSE C1;
937 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
938 hr_utility.set_message_token('PROCEDURE','insert_abs_for_paymix');
939 hr_utility.set_message_token('STEP','1');
940 hr_utility.raise_error;
941 END IF;
942 CLOSE C1;
943 --
944 l_assignment_id := p_assignment_id;
945 l_date_start := p_date_start;
946 l_date_end := p_date_end;
947 --
948 -- If either the payroll_id or the p_date_start is null then dont want
949 -- to continue.
950 --
951 IF l_payroll_id IS NULL OR p_date_start IS NULL THEN
952 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
953 hr_utility.raise_error;
954 END IF;
955 --
956 /*
957 -- Get Element type and input value id.
958 --
959 OPEN C2;
960 FETCH C2 INTO
961 l_element_type_id,
962 l_hours_or_days,
963 l_inc_or_dec_flag,
964 l_input_value_id;
965 --
966 IF (C2%NOTFOUND) THEN
967 l_ele_exists_flag := 'N';
968 ELSE
969 l_ele_exists_flag := 'Y';
970 END IF;
971 CLOSE C2;
972 --
973 -- If absence has no associated element then dont get element link.
974 --
975 IF l_ele_exists_flag = 'Y' THEN
976 --
977 -- Derive p_element_link
978 --
979 per_absence_attendances_pkg.get_ele_det1
980 (p_bgroup_id => l_business_group_id,
981 p_eltype => l_element_type_id,
982 p_per_id => l_person_id,
983 p_dstart => p_date_start,
984 p_sess => p_session_date,
985 p_ass_id => l_assignment_id,
986 p_ele_link => l_element_link_id,
987 p_pay_id => l_pay_id,
988 p_test => l_test);
989 --
990 -- If element link does not exist then discontinue.
991 --
992 IF l_element_link_id IS NULL THEN
993 hr_utility.set_message('PAY','HR_7448_ELE_PER_NOT_ELIGIBLE');
994 hr_utility.raise_error;
995 END IF;
996 --
997 END IF;
998 --
999 */
1000 -- Get the maximum absence occurrence for that person.
1001 --
1002 OPEN C3;
1003 FETCH C3 INTO l_max_occurrence;
1004
1005 IF l_max_occurrence is null THEN
1006 l_max_occurrence := 0;
1007 END IF;
1008
1009 CLOSE C3;
1010 --
1011 -- Increment the maximum occurrence by 1
1012 --
1013 l_max_occurrence := l_max_occurrence + 1;
1014 --
1015 --
1016 -- Insert the new absence from PayMIX into per_absence_attendances
1017 -- table. All values are set to null except for the mandatory ones
1018 -- and those set up by PayMIX.
1019 --
1020 per_absence_attendances_pkg.insert_row
1021 (X_Rowid => l_row_id,
1022 X_Absence_Attendance_Id => l_absence_att_id,
1023 X_Business_Group_Id => l_business_group_id,
1024 X_Absence_Attendance_Type_Id => p_absence_att_type_id,
1025 X_Abs_Attendance_Reason_Id => null,
1026 X_Person_Id => l_person_id,
1027 X_Authorising_Person_Id => null,
1028 X_Replacement_Person_Id => null,
1029 X_Period_Of_Incapacity_Id => null,
1030 X_Absence_Days => p_absence_days,
1031 X_Absence_Hours => p_absence_hours,
1032 X_Comments => null,
1033 X_Date_End => p_date_end,
1034 X_Date_Notification => p_session_date,
1035 X_Date_Projected_End => null,
1036 X_Date_Projected_Start => null,
1037 X_Date_Start => p_date_start,
1038 X_Occurrence => l_max_occurrence,
1039 X_Ssp1_Issued => null,
1040 X_Time_End => null,
1041 X_Time_Projected_End => null,
1042 X_Time_Projected_Start => null,
1043 X_Time_Start => null,
1044 X_Attribute_Category => null,
1045 X_Attribute1 => null,
1046 X_Attribute2 => null,
1047 X_Attribute3 => null,
1048 X_Attribute4 => null,
1049 X_Attribute5 => null,
1050 X_Attribute6 => null,
1051 X_Attribute7 => null,
1052 X_Attribute8 => null,
1053 X_Attribute9 => null,
1054 X_Attribute10 => null,
1055 X_Attribute11 => null,
1056 X_Attribute12 => null,
1057 X_Attribute13 => null,
1058 X_Attribute14 => null,
1059 X_Attribute15 => null,
1060 X_Attribute16 => null,
1061 X_Attribute17 => null,
1062 X_Attribute18 => null,
1063 X_Attribute19 => null,
1064 X_Attribute20 => null,
1065 X_Linked_Absence_id => null,
1066 X_Sickness_Start_Date => null,
1067 X_Sickness_End_Date => null,
1068 X_Accept_Late_Notif_Flag => null,
1069 X_reason_for_late_notification => null,
1070 X_Pregnancy_Related_Illness => null,
1071 X_Maternity_Id => null,
1072 X_Abs_Information_Category => null,
1073 X_Abs_Information1 => null,
1074 X_Abs_Information2 => null,
1075 X_Abs_Information3 => null,
1076 X_Abs_Information4 => null,
1077 X_Abs_Information5 => null,
1078 X_Abs_Information6 => null,
1079 X_Abs_Information7 => null,
1080 X_Abs_Information8 => null,
1081 X_Abs_Information9 => null,
1082 X_Abs_Information10 => null,
1083 X_Abs_Information11 => null,
1084 X_Abs_Information12 => null,
1085 X_Abs_Information13 => null,
1086 X_Abs_Information14 => null,
1087 X_Abs_Information15 => null,
1088 X_Abs_Information16 => null,
1089 X_Abs_Information17 => null,
1090 X_Abs_Information18 => null,
1091 X_Abs_Information19 => null,
1092 X_Abs_Information20 => null,
1093 X_Abs_Information21 => null,
1094 X_Abs_Information22 => null,
1095 X_Abs_Information23 => null,
1096 X_Abs_Information24 => null,
1097 X_Abs_Information25 => null,
1098 X_Abs_Information26 => null,
1099 X_Abs_Information27 => null,
1100 X_Abs_Information28 => null,
1101 X_Abs_Information29 => null,
1102 X_Abs_Information30 => null);
1103 --
1104 /*
1105 -- Determine the entry value and its sign. It is +ve if
1106 -- increasing_or_decreasing_flag set to 'I' else -ve.
1107 --
1108 IF l_inc_or_dec_flag = 'I' THEN
1109 IF l_hours_or_days = 'H' THEN
1110 l_entry_value := p_absence_hours;
1111 ELSE
1112 l_entry_value := p_absence_days;
1113 END IF;
1114 ELSE
1115 IF l_hours_or_days = 'H' THEN
1116 l_entry_value := p_absence_hours * -1;
1117 ELSE
1118 l_entry_value := p_absence_days * -1;
1119 END IF;
1120 END IF;
1121 --
1122 -- Insert element only if absence has an element associated to it.
1123 --
1124 IF l_ele_exists_flag = 'Y' THEN
1125 --
1126 -- Insert the element entry.
1127 --
1128 per_absence_attendances_pkg2.insert_element
1129 (p_effective_start_date => l_date_start,
1130 p_effective_end_date => l_date_end,
1131 p_element_entry_id => l_element_entry_id,
1132 p_assignment_id => p_assignment_id,
1133 p_element_link_id => l_element_link_id,
1134 p_creator_id => l_absence_att_id,
1135 p_creator_type => 'A',
1136 p_entry_type => 'E',
1137 p_input_value_id1 => l_input_value_id,
1138 p_entry_value1 => l_entry_value);
1139 --
1140 END IF;
1141 */
1142 --
1143 END insert_abs_for_paymix;
1144 --
1145 --
1146 PROCEDURE insert_abs_for_bee(p_session_date in date,
1147 p_absence_att_type_id in number,
1148 p_assignment_id in number,
1149 p_batch_id in number,
1150 p_absence_days in number,
1151 p_absence_hours in number,
1152 p_date_start in date,
1153 p_date_end in date,
1154 p_absence_attendance_id out nocopy number,
1155 p_warning_table out nocopy t_message_table,
1156 p_error_table out nocopy t_message_table
1157 ) is
1158 --
1159 l_absence_att_id number;
1160 l_row_id varchar2(30);
1161 l_person_id number;
1162 l_business_group_id number;
1163 l_payroll_id number;
1164 l_time_start varchar2(5);
1165 l_time_end varchar2(5);
1166 l_error_table t_message_table;
1167 l_warning_table t_message_table;
1168 l_absence_days number;
1169 l_absence_hours number;
1170 l_object_version_number number;
1171 l_occurrence number;
1172 l_dur_dys_less_warning boolean;
1173 l_dur_hrs_less_warning boolean;
1174 l_exceeds_pto_entit_warning boolean;
1175 l_exceeds_run_total_warning boolean;
1176 l_abs_overlap_warning boolean;
1177 l_abs_day_after_warning boolean;
1178 l_dur_overwritten_warning boolean;
1179
1180 --
1181 -- Retrieves additional data from per_assignments_f table.
1182 --
1183 CURSOR C1 IS
1184 SELECT a.business_group_id,
1185 a.person_id,
1186 a.payroll_id
1187 FROM per_assignments_f a
1188 WHERE a.assignment_id = p_assignment_id
1189 AND p_session_date between a.effective_start_date
1190 AND a.effective_end_date;
1191
1192 --
1193 -- Find normal working hours of employee
1194 --
1195 CURSOR C2 IS
1196 SELECT nvl(nvl(asg.time_normal_start, pbg.default_start_time), '00:00'),
1197 nvl(nvl(asg.time_normal_finish, pbg.default_end_time), '23:59')
1198 FROM per_assignments_f asg,
1199 per_business_groups pbg
1200 WHERE asg.assignment_id = p_assignment_id
1201 AND asg.business_group_id = pbg.business_group_id
1202 AND p_session_date between asg.effective_start_date
1203 and asg.effective_end_date;
1204
1205 --
1206
1207 -- ### Bug fix for 3812684.
1208 --
1209 l_sickness_start_date date := p_date_start;
1210 l_sickness_end_date date := p_date_end;
1211 l_dummy varchar2(1);
1212 --
1213 cursor C3 IS
1214 SELECT null
1215 FROM per_absence_attendance_types paat,
1216 per_business_groups_perf pbg
1217 WHERE pbg.business_group_id = l_business_group_id
1218 AND pbg.legislation_code = 'GB'
1219 AND paat.absence_attendance_type_id = p_absence_att_type_id
1220 AND paat.absence_category='S';
1221 --
1222 -- ###
1223
1224 begin
1225
1226 OPEN C1;
1227 FETCH C1 INTO
1228 l_business_group_id,
1229 l_person_id,
1230 l_payroll_id;
1231 --
1232 IF (C1%NOTFOUND) THEN
1233 CLOSE C1;
1234 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1235 hr_utility.set_message_token('PROCEDURE','insert_abs_for_bee');
1236 hr_utility.set_message_token('STEP','1');
1237 hr_utility.raise_error;
1238 END IF;
1239 CLOSE C1;
1240
1241 --
1242 -- If either the payroll_id or the p_date_start is null then dont want
1243 -- to continue.
1244 --
1245 IF l_payroll_id IS NULL OR p_date_start IS NULL THEN
1246 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1247 hr_utility.raise_error;
1248 END IF;
1249
1250 --
1251 -- Fetch the default start and end times for an
1252 -- hours absence
1253 --
1254 if p_absence_hours is not null
1255 and p_date_start is not null
1256 and p_date_end is not null then
1257 --
1258 open C2;
1259 fetch C2 into l_time_start, l_time_end;
1260 close C2;
1261 --
1262 end if;
1263
1264 --
1265 -- Carry out validation on new data before creating new
1266 -- absence record
1267 --
1268
1269 insert_validate_for_bee(p_session_date => p_session_date,
1270 p_date_start => p_date_start,
1271 p_date_end => p_date_end,
1272 p_time_start => l_time_start,
1273 p_time_end => l_time_end,
1274 p_absence_days => p_absence_days,
1275 p_absence_hours => p_absence_hours,
1276 p_abs_type_id => p_absence_att_type_id,
1277 p_person_id => l_person_id,
1278 p_assignment_id => p_assignment_id,
1279 p_business_group_id => l_business_group_id,
1280 p_warning_table => l_warning_table,
1281 p_error_table => l_error_table
1282 );
1283
1284 if l_error_table.count = 0 then
1285
1286 --
1287 -- The absence duration in days and or hours is an IN OUT parameter
1288 -- in the API so a local variable must be used.
1289 --
1290 l_absence_hours := p_absence_hours;
1291 l_absence_days := p_absence_days;
1292
1293 -- ### Bug fix for 3812684.
1294 --
1295 OPEN C3;
1296 FETCH C3 INTO l_dummy;
1297 if C3%notfound then
1298 l_sickness_start_date := null;
1299 l_sickness_end_date := null;
1300 end if;
1301 CLOSE C3;
1302 --
1303 -- ###
1304
1305 --
1306 -- Call the absence API to insert the new absence from BEE into
1307 -- per_absence_attendances table.
1308 -- All values are set to null except for the mandatory ones
1309 -- and those set up by BEE.
1310 --
1311 hr_person_absence_api.create_person_absence
1312 (p_validate => FALSE
1313 ,p_effective_date => p_session_date
1314 ,p_person_id => l_person_id
1315 ,p_business_group_id => l_business_group_id
1316 ,p_absence_attendance_type_id => p_absence_att_type_id
1317 ,p_date_notification => p_session_date
1318 ,p_date_start => p_date_start
1319 ,p_time_start => l_time_start
1320 ,p_date_end => p_date_end
1321 ,p_time_end => l_time_end
1322 ,p_absence_days => l_absence_days
1323 ,p_absence_hours => l_absence_hours
1324 ,p_batch_id => p_batch_id
1325 ,p_create_element_entry => FALSE
1326 ,p_absence_attendance_id => l_absence_att_id
1327 ,p_object_version_number => l_object_version_number
1328 ,p_occurrence => l_occurrence
1329 ,p_dur_dys_less_warning => l_dur_dys_less_warning
1330 ,p_dur_hrs_less_warning => l_dur_hrs_less_warning
1331 ,p_exceeds_pto_entit_warning => l_exceeds_pto_entit_warning
1332 ,p_exceeds_run_total_warning => l_exceeds_run_total_warning
1333 ,p_abs_overlap_warning => l_abs_overlap_warning
1334 ,p_abs_day_after_warning => l_abs_day_after_warning
1335 ,p_dur_overwritten_warning => l_dur_overwritten_warning
1336 -- ### Bug fix for 3812684.
1337 --
1338 ,p_sickness_start_date => l_sickness_start_date
1339 ,p_sickness_end_date => l_sickness_end_date
1340 --
1341 -- ###
1342 );
1343 --
1344 /*
1345 Create a warning message stating that the EE has already been created.
1346
1347 Bug 2377104. The below is now commented out. Setting this warning means
1348 thats BEE will not create the element entry (it assumes the absence API
1349 has already created it). However, any non-absence entry values specified
1350 for the BEE line are not passed to the absence API and so never
1351 get saved to the database. Commenting out nocopy this line ensures that BEE
1352 creates the element entry. The absence API is prevented from creating
1353 the EE by passing FALSE to p_create_element_entry.
1354
1355 l_warning_table(l_warning_table.count+1) := 'EE_CREATED_BY_ABSENCE_API';
1356 */
1357
1358 end if;
1359
1360 --
1361 -- Set the out parameters
1362 --
1363
1364 p_absence_attendance_id := l_absence_att_id;
1365 p_warning_table := l_warning_table;
1366 p_error_table := l_error_table;
1367 --
1368 END insert_abs_for_bee;
1369 --
1370 -- ----------------------------------------------------------------------------
1371 -- |------------------------< insert_abs_for_bee >----------------------------|
1372 -- ----------------------------------------------------------------------------
1373 -- Overloaded procedure
1374 procedure insert_abs_for_bee(
1375 p_absence_att_type_id in number,
1376 p_batch_id in number,
1377 p_asg_act_id in number,
1378 p_entry_values_count in number,
1379 p_hours_or_days in varchar2,
1380 p_format in varchar2,
1381 p_value in varchar2,
1382 p_date_start in date,
1383 p_date_end in date,
1384 p_line_record in pay_batch_lines%Rowtype,
1385 p_passed_inp_tbl in hr_entry.number_table,
1386 p_passed_val_tbl in hr_entry.varchar2_table,
1387 p_absence_attendance_id out nocopy number,
1388 p_warning_table out nocopy t_message_table,
1389 p_error_table out nocopy t_message_table
1390 ) is
1391 --
1392 l_proc varchar2(72) := g_package||'insert_abs_for_bee';
1393 --
1394 l_value number;
1395 l_absence_days number;
1396 l_absence_hours number;
1397 l_error_count number := 1;
1398 l_error varchar2(30) := null;
1399 l_result boolean;
1400 l_error_table t_message_table;
1401 --
1402 procedure chk_absence_input(
1403 p_value in varchar2, -- the input value to be checked/formatted.
1404 p_format in varchar2, -- the specific format.
1405 p_output out nocopy number, -- the formatted value on output.
1406 p_result out nocopy boolean -- success or failure flag.
1407 ) is
1408 --
1409 l_decplace pls_integer; -- number of decimal places.
1410 --
1411 begin
1412 --
1413 p_result := true; -- start by assuming success.
1414 --
1415 if(p_format = 'H_HH') then
1416 -- check is number and integer.
1417 if(trunc(p_value) <> p_value) then
1418 p_result := false;
1419 return;
1420 else
1421 p_output := to_number(trunc(p_value));
1422 end if;
1423 elsif(p_format = 'H_DECIMAL1' or p_format = 'H_DECIMAL2'
1424 or p_format = 'H_DECIMAL3' or p_format = 'HOURS') then
1425 p_output := to_number(p_value); --uses session NLS settings.
1426 -- can get dec places from the last character of the format:
1427 if p_format = 'HOURS' then
1428 l_decplace := 3; -- for backwards compatability.
1429 else
1430 l_decplace := to_number(substrb(p_format, -1, 1 ));
1431 end if;
1432 -- round the number.
1433 p_output := round(p_output, l_decplace);
1434 else -- Including the format type ND
1435 p_output := to_number(p_value); --uses session NLS settings.
1436 end if;
1437 --
1438 exception
1439 --
1440 when others then --when varchar2 conversion to number fails.
1441 --
1442 p_result := false;
1443 --
1444 end chk_absence_input;
1445 --
1446 begin
1447 --
1448 hr_utility.set_location('Entering:'||l_proc, 10);
1449 --
1450 chk_absence_input(p_value => p_value,
1451 p_format => p_format,
1452 p_output => l_value,
1453 p_result => l_result);
1454 --
1455 hr_utility.set_location(l_proc, 10);
1456 --
1457 if l_result = true then
1458 if p_hours_or_days = 'D' then
1459 l_absence_days := l_value;
1460 l_absence_hours := null;
1461 else
1462 l_absence_days := null;
1463 l_absence_hours := l_value;
1464 end if;
1465 --
1466 hr_utility.set_location(l_proc, 20);
1467 -- Calling the absence API after validation
1468 per_absence_attendances_pkg3.insert_abs_for_bee(
1469 p_session_date => p_line_record.effective_date,
1470 p_absence_att_type_id => p_absence_att_type_id,
1471 p_absence_attendance_id => p_absence_attendance_id,
1472 p_batch_id => p_batch_id,
1473 p_assignment_id => p_line_record.assignment_id,
1474 p_absence_days => l_absence_days,
1475 p_absence_hours => l_absence_hours,
1476 p_date_start => p_date_start,
1477 p_date_end => p_date_end,
1478 p_warning_table => p_warning_table,
1479 p_error_table => p_error_table);
1480 --
1481 hr_utility.set_location(l_proc, 30);
1482 -- Creating absence element entry.
1483 insert_absence_element(
1484 p_line_record => p_line_record,
1485 p_asg_act_id => p_asg_act_id,
1486 p_absence_attendance_id => p_absence_attendance_id,
1487 p_absence_att_type_id => p_absence_att_type_id,
1488 p_entry_values_count => p_entry_values_count,
1489 p_date_start => p_date_start,
1490 p_date_end => p_date_end,
1491 p_passed_inp_tbl => p_passed_inp_tbl,
1492 p_passed_val_tbl => p_passed_val_tbl);
1493 -- Create a warning message stating that the EE has already been created.
1494 p_warning_table(p_warning_table.count + 1) := 'EE_CREATED_BY_ABSENCE_API';
1495 --
1496 else
1497 l_error := 'HR_51153_INVAL_NUM_FORMAT';
1498 l_error_table(l_error_count) := l_error;
1499 p_error_table := l_error_table;
1500 --
1501 hr_utility.set_location(l_proc, 40);
1502 --
1503 end if;
1504 --
1505 hr_utility.set_location(' Leaving:'||l_proc, 99);
1506 --
1507 end insert_abs_for_bee;
1508 --
1509 END PER_ABSENCE_ATTENDANCES_PKG3;