[Home] [Help]
PACKAGE BODY: APPS.HR_PERSON_ABSENCE_API
Source
1 Package Body hr_person_absence_api as
2 /* $Header: peabsapi.pkb 120.35.12020000.2 2013/02/20 06:55:45 karthmoh ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_person_absence_api.';
7 --
8 procedure populate_ben_absence_rec
9 (p_absence_attendance_id in number,
10 p_rec_type in varchar2,
11 p_ben_rec out nocopy ben_abs_ler.g_abs_ler_rec) is
12
13 cursor c_current_absence is
14 select
15 absence_attendance_id
16 ,business_group_id
17 ,absence_attendance_type_id
18 ,abs_attendance_reason_id
19 ,person_id
20 ,authorising_person_id
21 ,replacement_person_id
22 ,period_of_incapacity_id
23 ,absence_days
24 ,absence_hours
25 ,comments
26 ,date_end
27 ,date_notification
28 ,date_projected_end
29 ,date_projected_start
30 ,date_start
31 ,occurrence
32 ,ssp1_issued
33 ,time_end
34 ,time_projected_end
35 ,time_projected_start
36 ,time_start
37 ,request_id
38 ,program_application_id
39 ,program_id
40 ,program_update_date
41 ,attribute_category
42 ,attribute1
43 ,attribute2
44 ,attribute3
45 ,attribute4
46 ,attribute5
47 ,attribute6
48 ,attribute7
49 ,attribute8
50 ,attribute9
51 ,attribute10
52 ,attribute11
53 ,attribute12
54 ,attribute13
55 ,attribute14
56 ,attribute15
57 ,attribute16
58 ,attribute17
59 ,attribute18
60 ,attribute19
61 ,attribute20
62 ,maternity_id
63 ,sickness_start_date
64 ,sickness_end_date
65 ,pregnancy_related_illness
66 ,reason_for_notification_delay
67 ,accept_late_notification_flag
68 ,linked_absence_id
69 ,abs_information_category
70 ,abs_information1
71 ,abs_information2
72 ,abs_information3
73 ,abs_information4
74 ,abs_information5
75 ,abs_information6
76 ,abs_information7
77 ,abs_information8
78 ,abs_information9
79 ,abs_information10
80 ,abs_information11
81 ,abs_information12
82 ,abs_information13
83 ,abs_information14
84 ,abs_information15
85 ,abs_information16
86 ,abs_information17
87 ,abs_information18
88 ,abs_information19
89 ,abs_information20
90 ,abs_information21
91 ,abs_information22
92 ,abs_information23
93 ,abs_information24
94 ,abs_information25
95 ,abs_information26
96 ,abs_information27
97 ,abs_information28
98 ,abs_information29
99 ,abs_information30
100 ,absence_case_id
101 ,batch_id
102 ,object_version_number
103 from per_absence_attendances
104 where absence_attendance_id = p_absence_attendance_id;
105 --
106 l_absence_rec per_abs_shd.g_rec_type;
107 l_proc varchar2(72) := g_package||'populate_ben_absence_rec';
108
109 begin
110
111 hr_utility.set_location('Entering: '|| l_proc, 10);
112
113 if (p_rec_type = 'O') then
114 l_absence_rec := per_abs_shd.g_old_rec;
115 else
116 open c_current_absence;
117 fetch c_current_absence into l_absence_rec;
118 close c_current_absence;
119 end if;
120
121 p_ben_rec.person_id := l_absence_rec.person_id;
122 p_ben_rec.business_group_id := l_absence_rec.business_group_id;
123 p_ben_rec.date_start := l_absence_rec.date_start;
124 p_ben_rec.date_end := l_absence_rec.date_end;
125 p_ben_rec.absence_attendance_type_id := l_absence_rec.absence_attendance_type_id;
126 p_ben_rec.abs_attendance_reason_id := l_absence_rec.abs_attendance_reason_id;
127 p_ben_rec.absence_attendance_id := l_absence_rec.absence_attendance_id;
128 p_ben_rec.authorising_person_id := l_absence_rec.authorising_person_id;
129 p_ben_rec.replacement_person_id := l_absence_rec.replacement_person_id;
130 p_ben_rec.period_of_incapacity_id := l_absence_rec.period_of_incapacity_id;
131 p_ben_rec.absence_days := l_absence_rec.absence_days;
132 p_ben_rec.absence_hours := l_absence_rec.absence_hours;
133 p_ben_rec.date_notification := l_absence_rec.date_notification;
134 p_ben_rec.date_projected_end := l_absence_rec.date_projected_end;
135 p_ben_rec.date_projected_start := l_absence_rec.date_projected_start;
136 p_ben_rec.occurrence := l_absence_rec.occurrence;
137 p_ben_rec.ssp1_issued := l_absence_rec.ssp1_issued;
138 p_ben_rec.time_end := l_absence_rec.time_end;
139 p_ben_rec.time_projected_end := l_absence_rec.time_projected_end;
140 p_ben_rec.time_projected_start := l_absence_rec.time_projected_start;
141 p_ben_rec.time_start := l_absence_rec.time_start;
142 p_ben_rec.attribute_category := l_absence_rec.attribute_category;
143 p_ben_rec.attribute1 := l_absence_rec.attribute1;
144 p_ben_rec.attribute2 := l_absence_rec.attribute2;
145 p_ben_rec.attribute3 := l_absence_rec.attribute3;
146 p_ben_rec.attribute4 := l_absence_rec.attribute4;
147 p_ben_rec.attribute5 := l_absence_rec.attribute5;
148 p_ben_rec.attribute6 := l_absence_rec.attribute6;
149 p_ben_rec.attribute7 := l_absence_rec.attribute7;
150 p_ben_rec.attribute8 := l_absence_rec.attribute8;
151 p_ben_rec.attribute9 := l_absence_rec.attribute9;
152 p_ben_rec.attribute10 := l_absence_rec.attribute10;
153 p_ben_rec.attribute11 := l_absence_rec.attribute11;
154 p_ben_rec.attribute12 := l_absence_rec.attribute12;
155 p_ben_rec.attribute13 := l_absence_rec.attribute13;
156 p_ben_rec.attribute14 := l_absence_rec.attribute14;
157 p_ben_rec.attribute15 := l_absence_rec.attribute15;
158 p_ben_rec.attribute16 := l_absence_rec.attribute16;
159 p_ben_rec.attribute17 := l_absence_rec.attribute17;
160 p_ben_rec.attribute18 := l_absence_rec.attribute18;
161 p_ben_rec.attribute19 := l_absence_rec.attribute19;
162 p_ben_rec.attribute20 := l_absence_rec.attribute20;
163 p_ben_rec.maternity_id := l_absence_rec.maternity_id;
164 p_ben_rec.sickness_start_date := l_absence_rec.sickness_start_date;
165 p_ben_rec.sickness_end_date := l_absence_rec.sickness_end_date;
166 p_ben_rec.pregnancy_related_illness := l_absence_rec.pregnancy_related_illness;
167 p_ben_rec.reason_for_notification_delay := l_absence_rec.reason_for_notification_delay;
168 p_ben_rec.accept_late_notification_flag := l_absence_rec.accept_late_notification_flag;
169 p_ben_rec.linked_absence_id := l_absence_rec.linked_absence_id;
170 p_ben_rec.batch_id := l_absence_rec.batch_id;
171 p_ben_rec.abs_information_category := l_absence_rec.abs_information_category;
172 p_ben_rec.abs_information1 := l_absence_rec.abs_information1;
173 p_ben_rec.abs_information2 := l_absence_rec.abs_information2;
174 p_ben_rec.abs_information3 := l_absence_rec.abs_information3;
175 p_ben_rec.abs_information4 := l_absence_rec.abs_information4;
176 p_ben_rec.abs_information5 := l_absence_rec.abs_information5;
177 p_ben_rec.abs_information6 := l_absence_rec.abs_information6;
178 p_ben_rec.abs_information7 := l_absence_rec.abs_information7;
179 p_ben_rec.abs_information8 := l_absence_rec.abs_information8;
180 p_ben_rec.abs_information9 := l_absence_rec.abs_information9;
181 p_ben_rec.abs_information10 := l_absence_rec.abs_information10;
182 p_ben_rec.abs_information11 := l_absence_rec.abs_information11;
183 p_ben_rec.abs_information12 := l_absence_rec.abs_information12;
184 p_ben_rec.abs_information13 := l_absence_rec.abs_information13;
185 p_ben_rec.abs_information14 := l_absence_rec.abs_information14;
186 p_ben_rec.abs_information15 := l_absence_rec.abs_information15;
187 p_ben_rec.abs_information16 := l_absence_rec.abs_information16;
188 p_ben_rec.abs_information17 := l_absence_rec.abs_information17;
189 p_ben_rec.abs_information18 := l_absence_rec.abs_information18;
190 p_ben_rec.abs_information19 := l_absence_rec.abs_information19;
191 p_ben_rec.abs_information20 := l_absence_rec.abs_information20;
192 p_ben_rec.abs_information21 := l_absence_rec.abs_information21;
193 p_ben_rec.abs_information22 := l_absence_rec.abs_information22;
194 p_ben_rec.abs_information23 := l_absence_rec.abs_information23;
195 p_ben_rec.abs_information24 := l_absence_rec.abs_information24;
196 p_ben_rec.abs_information25 := l_absence_rec.abs_information25;
197 p_ben_rec.abs_information26 := l_absence_rec.abs_information26;
198 p_ben_rec.abs_information27 := l_absence_rec.abs_information27;
199 p_ben_rec.abs_information28 := l_absence_rec.abs_information28;
200 p_ben_rec.abs_information29 := l_absence_rec.abs_information29;
201 p_ben_rec.abs_information30 := l_absence_rec.abs_information30;
202
203 hr_utility.set_location('Leaving: '|| l_proc, 15);
204 end;
205 --
206 -- ----------------------------------------------------------------------------
207 -- |----------------------< create_person_absence >---------------------------|
208 -- ----------------------------------------------------------------------------
209 --
210 procedure create_person_absence
211 (p_validate in boolean default false
212 ,p_effective_date in date
213 ,p_person_id in number
214 ,p_business_group_id in number
215 ,p_absence_attendance_type_id in number
216 ,p_abs_attendance_reason_id in number default null
217 ,p_comments in clob default null -- Bug#13362792
218 ,p_date_notification in date default null
219 ,p_date_projected_start in date default null
220 ,p_time_projected_start in varchar2 default null
221 ,p_date_projected_end in date default null
222 ,p_time_projected_end in varchar2 default null
223 ,p_date_start in date default null
224 ,p_time_start in varchar2 default null
225 ,p_date_end in date default null
226 ,p_time_end in varchar2 default null
227 ,p_absence_days in out nocopy number
228 ,p_absence_hours in out nocopy number
229 ,p_authorising_person_id in number default null
230 ,p_replacement_person_id in number default null
231 ,p_attribute_category in varchar2 default null
232 ,p_attribute1 in varchar2 default null
233 ,p_attribute2 in varchar2 default null
234 ,p_attribute3 in varchar2 default null
235 ,p_attribute4 in varchar2 default null
236 ,p_attribute5 in varchar2 default null
237 ,p_attribute6 in varchar2 default null
238 ,p_attribute7 in varchar2 default null
239 ,p_attribute8 in varchar2 default null
240 ,p_attribute9 in varchar2 default null
241 ,p_attribute10 in varchar2 default null
242 ,p_attribute11 in varchar2 default null
243 ,p_attribute12 in varchar2 default null
244 ,p_attribute13 in varchar2 default null
245 ,p_attribute14 in varchar2 default null
246 ,p_attribute15 in varchar2 default null
247 ,p_attribute16 in varchar2 default null
248 ,p_attribute17 in varchar2 default null
249 ,p_attribute18 in varchar2 default null
250 ,p_attribute19 in varchar2 default null
251 ,p_attribute20 in varchar2 default null
252 ,p_period_of_incapacity_id in number default null
253 ,p_ssp1_issued in varchar2 default 'N'
254 ,p_maternity_id in number default null
255 ,p_sickness_start_date in date default null
256 ,p_sickness_end_date in date default null
257 ,p_pregnancy_related_illness in varchar2 default 'N'
258 ,p_reason_for_notification_dela in varchar2 default null
259 ,p_accept_late_notification_fla in varchar2 default 'N'
260 ,p_linked_absence_id in number default null
261 ,p_batch_id in number default null
262 ,p_create_element_entry in boolean default true
263 ,p_abs_information_category in varchar2 default null
264 ,p_abs_information1 in varchar2 default null
265 ,p_abs_information2 in varchar2 default null
266 ,p_abs_information3 in varchar2 default null
267 ,p_abs_information4 in varchar2 default null
268 ,p_abs_information5 in varchar2 default null
269 ,p_abs_information6 in varchar2 default null
270 ,p_abs_information7 in varchar2 default null
271 ,p_abs_information8 in varchar2 default null
272 ,p_abs_information9 in varchar2 default null
273 ,p_abs_information10 in varchar2 default null
274 ,p_abs_information11 in varchar2 default null
275 ,p_abs_information12 in varchar2 default null
276 ,p_abs_information13 in varchar2 default null
277 ,p_abs_information14 in varchar2 default null
278 ,p_abs_information15 in varchar2 default null
279 ,p_abs_information16 in varchar2 default null
280 ,p_abs_information17 in varchar2 default null
281 ,p_abs_information18 in varchar2 default null
282 ,p_abs_information19 in varchar2 default null
283 ,p_abs_information20 in varchar2 default null
284 ,p_abs_information21 in varchar2 default null
285 ,p_abs_information22 in varchar2 default null
286 ,p_abs_information23 in varchar2 default null
287 ,p_abs_information24 in varchar2 default null
288 ,p_abs_information25 in varchar2 default null
289 ,p_abs_information26 in varchar2 default null
290 ,p_abs_information27 in varchar2 default null
291 ,p_abs_information28 in varchar2 default null
292 ,p_abs_information29 in varchar2 default null
293 ,p_abs_information30 in varchar2 default null
294 ,p_absence_case_id in number default null
295 ,p_program_application_id in number default 800
296 ,p_called_from in number default 800
297 ,p_absence_attendance_id out nocopy number
298 ,p_object_version_number out nocopy number
299 ,p_occurrence out nocopy number
300 ,p_dur_dys_less_warning out nocopy boolean
301 ,p_dur_hrs_less_warning out nocopy boolean
302 ,p_exceeds_pto_entit_warning out nocopy boolean
303 ,p_exceeds_run_total_warning out nocopy boolean
304 ,p_abs_overlap_warning out nocopy boolean
305 ,p_abs_day_after_warning out nocopy boolean
306 ,p_dur_overwritten_warning out nocopy boolean
307 ) is
308
309 --
310 -- Declare cursors and local variables
311 --
312
313 l_proc varchar2(72) := g_package||'create_person_absence';
314 l_exists number;
315 l_occurrence number;
316 l_input_value_id number;
317 l_date_projected_start date;
318 l_date_projected_end date;
319 l_date_start date;
320 l_date_end date;
321 l_date_notification date;
322 l_effective_date date;
323 l_old ben_abs_ler.g_abs_ler_rec;
324 l_new ben_abs_ler.g_abs_ler_rec;
325 --
326 -- Declare out parameters
327 --
328 l_absence_days number;
329 l_absence_hours number;
330 l_absence_attendance_id number;
331 l_assignment_id number;
332 l_element_entry_id number;
333 l_object_version_number number;
334 l_processing_type pay_element_types_f.processing_type%TYPE;
335 l_dur_dys_less_warning boolean;
336 l_dur_hrs_less_warning boolean;
337 l_exceeds_pto_entit_warning boolean;
338 l_exceeds_run_total_warning boolean;
339 l_abs_overlap_warning boolean;
340 l_abs_day_after_warning boolean;
341 l_dur_overwritten_warning boolean;
342 l_retvalue varchar2(10);
343 --
344
345 l_hours_or_days varchar2(2);
346 cursor csr_get_abstype is
347 select HOURS_OR_DAYS, INPUT_VALUE_ID
348 from per_absence_attendance_types
349 where absence_attendance_type_id= p_absence_attendance_type_id;
350 l_chk_datestart date;
351 l_chk_dateend date;
352 l_elm_entry_id number;
353 l_enty_efsd date;
354 l_entry_efed date;
355
356 begin
357 hr_utility.set_location('Entering:'|| l_proc, 10);
358
359 --
360 -- Pipe the main IN / IN OUT parameters for ease of debugging.
361 --
362 hr_utility.trace(' ');
363 hr_utility.trace(' --------------------------------'||
364 '---------------------------------');
365 hr_utility.trace(' IN / IN OUT NOCOPY PARAMETER '||
366 ' VALUE');
367 hr_utility.trace(' --------------------------------'||
368 '+--------------------------------');
369 hr_utility.trace(' p_effective_date '||
370 to_char(p_effective_date));
371 hr_utility.trace(' p_person_id '||
372 to_char(p_person_id));
373 hr_utility.trace(' p_business_group_id '||
374 to_char(p_business_group_id));
375 hr_utility.trace(' p_absence_attendance_type_id '||
376 to_char(p_absence_attendance_type_id));
377 hr_utility.trace(' p_abs_attendance_reason_id '||
378 to_char(p_abs_attendance_reason_id));
379 hr_utility.trace(' p_date_notification '||
380 to_char(p_date_notification));
381 hr_utility.trace(' p_date_projected_start '||
382 to_char(p_date_projected_start));
383 hr_utility.trace(' p_time_projected_start '||
384 p_time_projected_start);
385 hr_utility.trace(' p_date_projected_end '||
386 to_char(p_date_projected_end));
387 hr_utility.trace(' p_time_projected_end '||
388 p_time_projected_end);
389 hr_utility.trace(' p_date_start '||
390 to_char(p_date_start));
391 hr_utility.trace(' p_time_start '||
392 p_time_start);
393 hr_utility.trace(' p_date_end '||
394 to_char(p_date_end));
395 hr_utility.trace(' p_time_end '||
396 p_time_end);
397 hr_utility.trace(' p_absence_days '||
398 to_char(p_absence_days));
399 hr_utility.trace(' p_absence_hours '||
400 to_char(p_absence_hours));
401 hr_utility.trace(' p_authorising_person_id '||
402 to_char(p_authorising_person_id));
403 hr_utility.trace(' p_batch_id '||
404 to_char(p_batch_id));
405 if p_create_element_entry then
406 hr_utility.trace(' p_create_element_entry '||
407 'TRUE');
408 else
409 hr_utility.trace(' p_create_element_entry '||
410 'FALSE');
411 end if;
412 hr_utility.trace(' --------------------------------'||
413 '---------------------------------');
414 hr_utility.trace(' ');
415
416 --
417 -- Create a savepoint.
418 --
419 savepoint create_person_absence;
420 --
421 -- Truncate the time portion from all IN date parameters
422 --
423
424 if nvl(FND_PROFILE.Value('HR_ABS_OTL_INTEGRATION'),'N') = 'Y' then
425 hr_utility.set_location(' OTL ABS integration on ',10);
426
427 open csr_get_abstype;
428 fetch csr_get_abstype into l_hours_or_days,l_elm_entry_id;
429 close csr_get_abstype;
430
431 hr_utility.set_location(' l_inputvalue_id : '|| l_elm_entry_id,10);
432
433 if l_elm_entry_id is not null then
434
435
436 hr_utility.set_location(' l_hours_or_days ' ||l_hours_or_days,10);
437 if l_hours_or_days ='H' then
438 -- when the UOM attached to the element is Hours
439
440 if p_date_start is null then
441
442 if p_date_projected_start is null then
443 hr_utility.set_message (800,'HR_449868_START_END_DATES5');
444 hr_utility.raise_error;
445 end if;
446
447 if p_time_projected_start is null or p_date_projected_end is null or p_time_projected_end is null then
448 hr_utility.set_location(' l_hours_or_days 30 ' ||l_hours_or_days,30);
449 hr_utility.set_message(800, 'HR_449866_START_END_DATES3');
450 hr_utility.raise_error;
451 end if;
452
453 hr_utility.set_location(' l_hours_or_days ' ||l_hours_or_days,20);
454
455 else
456 hr_utility.set_location(' l_hours_or_days ' ||l_hours_or_days,40);
457
458 if p_date_projected_start is not null and
459 (p_time_projected_start is null or p_date_projected_end is null or p_time_projected_end is null ) then
460
461 hr_utility.set_message (800,'HR_449866_START_END_DATES3');
462 hr_utility.raise_error;
463 end if;
464
465 if p_date_end is null or p_time_end is null or p_time_start is null then
466
467 hr_utility.set_message (800,'HR_449867_START_END_DATES4');
468 hr_utility.raise_error;
469 end if;
470 end if;
471
472 else
473 -- when the UOM is Days
474 if p_date_start is null then
475
476 if p_date_projected_start is null then
477 hr_utility.set_message (800,'HR_449868_START_END_DATES5');
478 hr_utility.raise_error;
479
480 elsif p_date_projected_end is null then
481
482 hr_utility.set_message (800,'HR_449864_START_END_DATES1');
483 hr_utility.raise_error;
484 end if;
485
486 else
487
488 if p_date_projected_start is not null and p_date_projected_end is null then
489
490 hr_utility.set_message (800,'HR_449864_START_END_DATES1');
491 hr_utility.raise_error;
492 end if;
493
494 if p_date_end is null then
495
496 hr_utility.set_message (800,'HR_449865_START_END_DATES2');
497 hr_utility.raise_error;
498 end if;
499
500 end if;
501
502 end if;
503
504 l_chk_datestart := nvl(p_date_start,p_date_projected_start);
505 l_chk_dateend := nvl(p_date_end,p_date_projected_end);
506
507 if p_called_from <> 809 and l_chk_datestart is not null and l_chk_dateend is not null
508 then
509 hr_utility.set_location('inside otl hr check ', 10);
510
511 otl_hr_check
512 (
513 p_person_id => p_person_id,
514 p_date_start => l_chk_datestart,
515 p_date_end => l_chk_dateend,
516 p_scope => 'CREATE',
517 p_ret_value => l_retvalue );
518
519 hr_utility.set_location('after otl hr check ', 10);
520 end if;
521
522 end if;
523 end if;
524
525
526
527 l_effective_date := trunc(p_effective_date);
528 l_date_projected_start := trunc(p_date_projected_start);
529 l_date_projected_end := trunc(p_date_projected_end);
530 l_date_start := trunc(p_date_start);
531 l_date_end := trunc(p_date_end);
532 l_date_notification := trunc(p_date_notification);
533 l_absence_days := p_absence_days;
534 l_absence_hours := p_absence_hours;
535
536 --
537 -- Call Before Process User Hook
538 --
539 begin
540 hr_person_absence_bk1.create_person_absence_b
541 (p_effective_date => p_effective_date
542 ,p_person_id => p_person_id
543 ,p_business_group_id => p_business_group_id
544 ,p_absence_attendance_type_id => p_absence_attendance_type_id
545 ,p_abs_attendance_reason_id => p_abs_attendance_reason_id
546 ,p_comments => p_comments
547 ,p_date_notification => p_date_notification
548 ,p_date_projected_start => p_date_projected_start
549 ,p_time_projected_start => p_time_projected_start
550 ,p_date_projected_end => p_date_projected_end
551 ,p_time_projected_end => p_time_projected_end
552 ,p_date_start => p_date_start
553 ,p_time_start => p_time_start
554 ,p_date_end => p_date_end
555 ,p_time_end => p_time_end
556 ,p_absence_days => l_absence_days
557 ,p_absence_hours => l_absence_hours
558 ,p_authorising_person_id => p_authorising_person_id
559 ,p_replacement_person_id => p_replacement_person_id
560 ,p_attribute_category => p_attribute_category
561 ,p_attribute1 => p_attribute1
562 ,p_attribute2 => p_attribute2
563 ,p_attribute3 => p_attribute3
564 ,p_attribute4 => p_attribute4
565 ,p_attribute5 => p_attribute5
566 ,p_attribute6 => p_attribute6
567 ,p_attribute7 => p_attribute7
568 ,p_attribute8 => p_attribute8
569 ,p_attribute9 => p_attribute9
570 ,p_attribute10 => p_attribute10
571 ,p_attribute11 => p_attribute11
572 ,p_attribute12 => p_attribute12
573 ,p_attribute13 => p_attribute13
574 ,p_attribute14 => p_attribute14
575 ,p_attribute15 => p_attribute15
576 ,p_attribute16 => p_attribute16
577 ,p_attribute17 => p_attribute17
578 ,p_attribute18 => p_attribute18
579 ,p_attribute19 => p_attribute19
580 ,p_attribute20 => p_attribute20
581 ,p_period_of_incapacity_id => p_period_of_incapacity_id
582 ,p_ssp1_issued => p_ssp1_issued
583 ,p_maternity_id => p_maternity_id
584 ,p_sickness_start_date => p_sickness_start_date
585 ,p_sickness_end_date => p_sickness_end_date
586 ,p_pregnancy_related_illness => p_pregnancy_related_illness
587 ,p_reason_for_notification_dela => p_reason_for_notification_dela
588 ,p_accept_late_notification_fla => p_accept_late_notification_fla
589 ,p_linked_absence_id => p_linked_absence_id
590 ,p_absence_case_id => p_absence_case_id
591 ,p_batch_id => p_batch_id
592 ,p_create_element_entry => p_create_element_entry
593 ,p_abs_information_category => p_abs_information_category
594 ,p_abs_information1 => p_abs_information1
595 ,p_abs_information2 => p_abs_information2
596 ,p_abs_information3 => p_abs_information3
597 ,p_abs_information4 => p_abs_information4
598 ,p_abs_information5 => p_abs_information5
599 ,p_abs_information6 => p_abs_information6
600 ,p_abs_information7 => p_abs_information7
601 ,p_abs_information8 => p_abs_information8
602 ,p_abs_information9 => p_abs_information9
603 ,p_abs_information10 => p_abs_information10
604 ,p_abs_information11 => p_abs_information11
605 ,p_abs_information12 => p_abs_information12
606 ,p_abs_information13 => p_abs_information13
607 ,p_abs_information14 => p_abs_information14
608 ,p_abs_information15 => p_abs_information15
609 ,p_abs_information16 => p_abs_information16
610 ,p_abs_information17 => p_abs_information17
611 ,p_abs_information18 => p_abs_information18
612 ,p_abs_information19 => p_abs_information19
613 ,p_abs_information20 => p_abs_information20
614 ,p_abs_information21 => p_abs_information21
615 ,p_abs_information22 => p_abs_information22
616 ,p_abs_information23 => p_abs_information23
617 ,p_abs_information24 => p_abs_information24
618 ,p_abs_information25 => p_abs_information25
619 ,p_abs_information26 => p_abs_information26
620 ,p_abs_information27 => p_abs_information27
621 ,p_abs_information28 => p_abs_information28
622 ,p_abs_information29 => p_abs_information29
623 ,p_abs_information30 => p_abs_information30
624
625
626 );
627 exception
628 when hr_api.cannot_find_prog_unit then
629 hr_api.cannot_find_prog_unit_error
630 (p_module_name => 'CREATE_PERSON_ABSENCE'
631 ,p_hook_type => 'BP'
632 );
633 end;
634
635 hr_utility.set_location(l_proc, 30);
636 --
637 -- Insert Person Absence
638 per_abs_ins.ins
639 (p_effective_date => l_effective_date
640 ,p_business_group_id => p_business_group_id
641 ,p_absence_attendance_type_id => p_absence_attendance_type_id
642 ,p_person_id => p_person_id
643 ,p_abs_attendance_reason_id => p_abs_attendance_reason_id
644 ,p_authorising_person_id => p_authorising_person_id
645 ,p_replacement_person_id => p_replacement_person_id
646 ,p_absence_days => l_absence_days
647 ,p_absence_hours => l_absence_hours
648 ,p_comments => p_comments
649 ,p_date_end => l_date_end
650 ,p_date_notification => l_date_notification
651 ,p_date_projected_end => l_date_projected_end
652 ,p_date_projected_start => l_date_projected_start
653 ,p_date_start => l_date_start
654 ,p_occurrence => l_occurrence
655 ,p_time_end => p_time_end
656 ,p_time_projected_end => p_time_projected_end
657 ,p_time_projected_start => p_time_projected_start
658 ,p_time_start => p_time_start
659 ,p_attribute_category => p_attribute_category
660 ,p_attribute1 => p_attribute1
661 ,p_attribute2 => p_attribute2
662 ,p_attribute3 => p_attribute3
663 ,p_attribute4 => p_attribute4
664 ,p_attribute5 => p_attribute5
665 ,p_attribute6 => p_attribute6
666 ,p_attribute7 => p_attribute7
667 ,p_attribute8 => p_attribute8
668 ,p_attribute9 => p_attribute9
669 ,p_attribute10 => p_attribute10
670 ,p_attribute11 => p_attribute11
671 ,p_attribute12 => p_attribute12
672 ,p_attribute13 => p_attribute13
673 ,p_attribute14 => p_attribute14
674 ,p_attribute15 => p_attribute15
675 ,p_attribute16 => p_attribute16
676 ,p_attribute17 => p_attribute17
677 ,p_attribute18 => p_attribute18
678 ,p_attribute19 => p_attribute19
679 ,p_attribute20 => p_attribute20
680 ,p_period_of_incapacity_id => p_period_of_incapacity_id
681 ,p_ssp1_issued => p_ssp1_issued
682 ,p_maternity_id => p_maternity_id
683 ,p_sickness_start_date => p_sickness_start_date
684 ,p_sickness_end_date => p_sickness_end_date
685 ,p_pregnancy_related_illness => p_pregnancy_related_illness
686 ,p_reason_for_notification_dela => p_reason_for_notification_dela
687 ,p_accept_late_notification_fla => p_accept_late_notification_fla
688 ,p_linked_absence_id => p_linked_absence_id
689 ,p_absence_case_id => p_absence_case_id
690 ,p_batch_id => p_batch_id
691 ,p_abs_information_category => p_abs_information_category
692 ,p_abs_information1 => p_abs_information1
693 ,p_abs_information2 => p_abs_information2
694 ,p_abs_information3 => p_abs_information3
695 ,p_abs_information4 => p_abs_information4
696 ,p_abs_information5 => p_abs_information5
697 ,p_abs_information6 => p_abs_information6
698 ,p_abs_information7 => p_abs_information7
699 ,p_abs_information8 => p_abs_information8
700 ,p_abs_information9 => p_abs_information9
701 ,p_abs_information10 => p_abs_information10
702 ,p_abs_information11 => p_abs_information11
703 ,p_abs_information12 => p_abs_information12
704 ,p_abs_information13 => p_abs_information13
705 ,p_abs_information14 => p_abs_information14
706 ,p_abs_information15 => p_abs_information15
707 ,p_abs_information16 => p_abs_information16
708 ,p_abs_information17 => p_abs_information17
709 ,p_abs_information18 => p_abs_information18
710 ,p_abs_information19 => p_abs_information19
711 ,p_abs_information20 => p_abs_information20
712 ,p_abs_information21 => p_abs_information21
713 ,p_abs_information22 => p_abs_information22
714 ,p_abs_information23 => p_abs_information23
715 ,p_abs_information24 => p_abs_information24
716 ,p_abs_information25 => p_abs_information25
717 ,p_abs_information26 => p_abs_information26
718 ,p_abs_information27 => p_abs_information27
719 ,p_abs_information28 => p_abs_information28
720 ,p_abs_information29 => p_abs_information29
721 ,p_abs_information30 => p_abs_information30
722 ,p_program_application_id => p_program_application_id
723 ,p_absence_attendance_id => l_absence_attendance_id
724 ,p_object_version_number => l_object_version_number
725 ,p_dur_dys_less_warning => l_dur_dys_less_warning
726 ,p_dur_hrs_less_warning => l_dur_hrs_less_warning
727 ,p_exceeds_pto_entit_warning => l_exceeds_pto_entit_warning
728 ,p_exceeds_run_total_warning => l_exceeds_run_total_warning
729 ,p_abs_overlap_warning => l_abs_overlap_warning
730 ,p_abs_day_after_warning => l_abs_day_after_warning
731 ,p_dur_overwritten_warning => l_dur_overwritten_warning
732 );
733
734 p_dur_dys_less_warning := l_dur_dys_less_warning;
735 p_dur_hrs_less_warning := l_dur_hrs_less_warning;
736 p_exceeds_pto_entit_warning := l_exceeds_pto_entit_warning;
737 p_exceeds_run_total_warning := l_exceeds_run_total_warning;
738 p_abs_overlap_warning := l_abs_overlap_warning;
739 p_abs_day_after_warning := l_abs_day_after_warning;
740 p_dur_overwritten_warning := l_dur_overwritten_warning;
741
742 hr_utility.set_location('Start of absence element entry section', 40);
743
744 /* Start of Absence Element Entry Section */
745
746 if p_create_element_entry then
747 --
748 -- Insert the absence element element. First we check if the
749 -- absence type is linked to an element type.
750 --
751
752 if linked_to_element
753 (p_absence_attendance_id => l_absence_attendance_id)
754 then
755
756 --
757 -- Get the assignment_id and processing type for use later
758 --
759 l_assignment_id := get_primary_assignment
760 (p_person_id => p_person_id
761 ,p_effective_date => p_date_start);
762
763 l_processing_type := get_processing_type
764 (p_absence_attendance_type_id => p_absence_attendance_type_id);
765
766
767 if (l_processing_type = 'N'
768 and p_date_start is not null
769 and p_date_end is not null)
770 or (l_processing_type = 'R'
771 and p_date_start is not null)
772 then
773
774 insert_absence_element
775 (p_date_start => p_date_start
776 ,p_assignment_id => l_assignment_id
777 ,p_absence_attendance_id => l_absence_attendance_id
778 ,p_element_entry_id => l_element_entry_id);
779
780 if l_processing_type = 'R' and p_date_end is not null then
781 --
782 -- If this is a recurring element entry and we have the
783 -- absence end date, we date effectively delete the
784 -- element immediately, otherwise it remains open until
785 -- the end of time.
786 --
787
788 delete_absence_element
789 (p_dt_delete_mode => 'DELETE'
790 ,p_session_date => p_date_end
791 ,p_element_entry_id => l_element_entry_id);
792
793 end if;
794
795 end if;
796
797 end if;
798
799 end if;
800 /* End of Absence Element Entry Section */
801
802 hr_utility.set_location('End of absence element entry section', 50);
803
804 populate_ben_absence_rec
805 (p_absence_attendance_id => l_absence_attendance_id,
806 p_rec_type => 'N',
807 p_ben_rec => l_new);
808
809 --
810 -- Start of BEN call.
811 --
812 hr_utility.set_location('Start of BEN call', 52);
813
814 ben_abs_ler.ler_chk
815 (p_old => l_old
816 ,p_new => l_new
817 ,p_effective_date => l_effective_date);
818
819 hr_utility.set_location('End of BEN call', 54);
820
821 --
822 -- Call After Process User Hook
823 --
824
825 begin
826 hr_person_absence_bk1.create_person_absence_a
827 (p_effective_date => p_effective_date
828 ,p_person_id => p_person_id
829 ,p_business_group_id => p_business_group_id
830 ,p_absence_attendance_type_id => p_absence_attendance_type_id
831 ,p_abs_attendance_reason_id => p_abs_attendance_reason_id
832 ,p_comments => p_comments
833 ,p_date_notification => p_date_notification
834 ,p_date_projected_start => p_date_projected_start
835 ,p_time_projected_start => p_time_projected_start
836 ,p_date_projected_end => p_date_projected_end
837 ,p_time_projected_end => p_time_projected_end
838 ,p_date_start => p_date_start
839 ,p_time_start => p_time_start
840 ,p_date_end => p_date_end
841 ,p_time_end => p_time_end
842 ,p_absence_days => l_absence_days
843 ,p_absence_hours => l_absence_hours
844 ,p_authorising_person_id => p_authorising_person_id
845 ,p_replacement_person_id => p_replacement_person_id
846 ,p_attribute_category => p_attribute_category
847 ,p_attribute1 => p_attribute1
848 ,p_attribute2 => p_attribute2
849 ,p_attribute3 => p_attribute3
850 ,p_attribute4 => p_attribute4
851 ,p_attribute5 => p_attribute5
852 ,p_attribute6 => p_attribute6
853 ,p_attribute7 => p_attribute7
854 ,p_attribute8 => p_attribute8
855 ,p_attribute9 => p_attribute9
856 ,p_attribute10 => p_attribute10
857 ,p_attribute11 => p_attribute11
858 ,p_attribute12 => p_attribute12
859 ,p_attribute13 => p_attribute13
860 ,p_attribute14 => p_attribute14
861 ,p_attribute15 => p_attribute15
862 ,p_attribute16 => p_attribute16
863 ,p_attribute17 => p_attribute17
864 ,p_attribute18 => p_attribute18
865 ,p_attribute19 => p_attribute19
866 ,p_attribute20 => p_attribute20
867 ,p_occurrence => l_occurrence
868 ,p_period_of_incapacity_id => p_period_of_incapacity_id
869 ,p_ssp1_issued => p_ssp1_issued
870 ,p_maternity_id => p_maternity_id
871 ,p_sickness_start_date => p_sickness_start_date
872 ,p_sickness_end_date => p_sickness_end_date
873 ,p_pregnancy_related_illness => p_pregnancy_related_illness
874 ,p_reason_for_notification_dela => p_reason_for_notification_dela
875 ,p_accept_late_notification_fla => p_accept_late_notification_fla
876 ,p_linked_absence_id => p_linked_absence_id
877 ,p_absence_case_id => p_absence_case_id
878 ,p_batch_id => p_batch_id
879 ,p_create_element_entry => p_create_element_entry
880 ,p_abs_information_category => p_abs_information_category
881 ,p_abs_information1 => p_abs_information1
882 ,p_abs_information2 => p_abs_information2
883 ,p_abs_information3 => p_abs_information3
884 ,p_abs_information4 => p_abs_information4
885 ,p_abs_information5 => p_abs_information5
886 ,p_abs_information6 => p_abs_information6
887 ,p_abs_information7 => p_abs_information7
888 ,p_abs_information8 => p_abs_information8
889 ,p_abs_information9 => p_abs_information9
890 ,p_abs_information10 => p_abs_information10
891 ,p_abs_information11 => p_abs_information11
892 ,p_abs_information12 => p_abs_information12
893 ,p_abs_information13 => p_abs_information13
894 ,p_abs_information14 => p_abs_information14
895 ,p_abs_information15 => p_abs_information15
896 ,p_abs_information16 => p_abs_information16
897 ,p_abs_information17 => p_abs_information17
898 ,p_abs_information18 => p_abs_information18
899 ,p_abs_information19 => p_abs_information19
900 ,p_abs_information20 => p_abs_information20
901 ,p_abs_information21 => p_abs_information21
902 ,p_abs_information22 => p_abs_information22
903 ,p_abs_information23 => p_abs_information23
904 ,p_abs_information24 => p_abs_information24
905 ,p_abs_information25 => p_abs_information25
906 ,p_abs_information26 => p_abs_information26
907 ,p_abs_information27 => p_abs_information27
908 ,p_abs_information28 => p_abs_information28
909 ,p_abs_information29 => p_abs_information29
910 ,p_abs_information30 => p_abs_information30
911 ,p_absence_attendance_id => l_absence_attendance_id
912 ,p_object_version_number => l_object_version_number
913 ,p_dur_dys_less_warning => l_dur_dys_less_warning
914 ,p_dur_hrs_less_warning => l_dur_hrs_less_warning
915 ,p_exceeds_pto_entit_warning => l_exceeds_pto_entit_warning
916 ,p_exceeds_run_total_warning => l_exceeds_run_total_warning
917 ,p_abs_overlap_warning => l_abs_overlap_warning
918 ,p_abs_day_after_warning => l_abs_day_after_warning
919 ,p_dur_overwritten_warning => l_dur_overwritten_warning
920 );
921 exception
922 when hr_api.cannot_find_prog_unit then
923 hr_api.cannot_find_prog_unit_error
924 (p_module_name => 'CREATE_PERSON_ABSENCE'
925 ,p_hook_type => 'AP'
926 );
927 end;
928 --
929 -- When in validation only mode raise the Validate_Enabled exception
930 --
931 if p_validate then
932 raise hr_api.validate_enabled;
933 end if;
934 --
935 -- Set all output arguments
936 --
937 p_absence_attendance_id := l_absence_attendance_id;
938 p_object_version_number := l_object_version_number;
939 p_absence_days := l_absence_days;
940 p_absence_hours := l_absence_hours;
941 p_occurrence := l_occurrence;
942 --
943
944 --
945 -- Pipe the main IN OUT / OUT parameters for ease of debugging.
946 --
947 hr_utility.trace(' ');
948 hr_utility.trace(' --------------------------------'||
949 '---------------------------------');
950 hr_utility.trace(' IN OUT NOCOPY / OUT NOCOPY PARAMETER '||
951 ' VALUE');
952 hr_utility.trace(' --------------------------------'||
953 '+--------------------------------');
954 hr_utility.trace(' p_absence_days '||
955 to_char(p_absence_days));
956 hr_utility.trace(' p_absence_hours '||
957 to_char(p_absence_hours));
958 hr_utility.trace(' p_absence_attendance_id '||
959 to_char(p_absence_attendance_id));
960 hr_utility.trace(' p_object_version_number '||
961 to_char(p_object_version_number));
962 hr_utility.trace(' p_occurrence '||
963 to_char(p_occurrence));
964 hr_utility.trace(' --------------------------------'||
965 '---------------------------------');
966 hr_utility.trace(' ');
967
968 hr_utility.set_location(' Leaving:'||l_proc, 70);
969
970 exception
971 when hr_api.validate_enabled then
972 --
973 -- As the Validate_Enabled exception has been raised
974 -- we must rollback to the savepoint
975 --
976 rollback to create_person_absence;
977 --
978 -- Only set output warning arguments
979 -- (Any key or derived arguments must be set to null
980 -- when validation only mode is being used.)
981 --
982 p_absence_attendance_id := null;
983 p_object_version_number := null;
984 hr_utility.set_location(l_proc, 80);
985 when others then
986 --
987 -- A validation or unexpected error has occured
988 --
989 p_absence_attendance_id := null;
990 p_object_version_number := null;
991 p_occurrence := null;
992 p_dur_dys_less_warning := null;
993 p_dur_hrs_less_warning := null;
994 p_exceeds_pto_entit_warning := null;
995 p_exceeds_run_total_warning := null;
996 p_abs_overlap_warning := null;
997 p_abs_day_after_warning := null;
998 p_dur_overwritten_warning := null;
999
1000 rollback to create_person_absence;
1001 hr_utility.set_location(' Leaving:'||l_proc, 90);
1002 raise;
1003 end create_person_absence;
1004 --
1005 -- ----------------------------------------------------------------------------
1006 -- |----------------------< update_person_absence >---------------------------|
1007 -- ----------------------------------------------------------------------------
1008 --
1009 procedure update_person_absence
1010 (p_validate in boolean default false
1011 ,p_effective_date in date
1012 ,p_absence_attendance_id in number
1013 ,p_abs_attendance_reason_id in number default hr_api.g_number
1014 ,p_comments in clob default hr_api.g_varchar2 -- Bug#13362792
1015 ,p_date_notification in date default hr_api.g_date
1016 ,p_date_projected_start in date default hr_api.g_date
1017 ,p_time_projected_start in varchar2 default hr_api.g_varchar2
1018 ,p_date_projected_end in date default hr_api.g_date
1019 ,p_time_projected_end in varchar2 default hr_api.g_varchar2
1020 ,p_date_start in date default hr_api.g_date
1021 ,p_time_start in varchar2 default hr_api.g_varchar2
1022 ,p_date_end in date default hr_api.g_date
1023 ,p_time_end in varchar2 default hr_api.g_varchar2
1024 ,p_absence_days in out nocopy number
1025 ,p_absence_hours in out nocopy number
1026 ,p_authorising_person_id in number default hr_api.g_number
1027 ,p_replacement_person_id in number default hr_api.g_number
1028 ,p_attribute_category in varchar2 default hr_api.g_varchar2
1029 ,p_attribute1 in varchar2 default hr_api.g_varchar2
1030 ,p_attribute2 in varchar2 default hr_api.g_varchar2
1031 ,p_attribute3 in varchar2 default hr_api.g_varchar2
1032 ,p_attribute4 in varchar2 default hr_api.g_varchar2
1033 ,p_attribute5 in varchar2 default hr_api.g_varchar2
1034 ,p_attribute6 in varchar2 default hr_api.g_varchar2
1035 ,p_attribute7 in varchar2 default hr_api.g_varchar2
1036 ,p_attribute8 in varchar2 default hr_api.g_varchar2
1037 ,p_attribute9 in varchar2 default hr_api.g_varchar2
1038 ,p_attribute10 in varchar2 default hr_api.g_varchar2
1039 ,p_attribute11 in varchar2 default hr_api.g_varchar2
1040 ,p_attribute12 in varchar2 default hr_api.g_varchar2
1041 ,p_attribute13 in varchar2 default hr_api.g_varchar2
1042 ,p_attribute14 in varchar2 default hr_api.g_varchar2
1043 ,p_attribute15 in varchar2 default hr_api.g_varchar2
1044 ,p_attribute16 in varchar2 default hr_api.g_varchar2
1045 ,p_attribute17 in varchar2 default hr_api.g_varchar2
1046 ,p_attribute18 in varchar2 default hr_api.g_varchar2
1047 ,p_attribute19 in varchar2 default hr_api.g_varchar2
1048 ,p_attribute20 in varchar2 default hr_api.g_varchar2
1049 ,p_period_of_incapacity_id in number default hr_api.g_number
1050 ,p_ssp1_issued in varchar2 default hr_api.g_varchar2
1051 ,p_maternity_id in number default hr_api.g_number
1052 ,p_sickness_start_date in date default hr_api.g_date
1053 ,p_sickness_end_date in date default hr_api.g_date
1054 ,p_pregnancy_related_illness in varchar2 default hr_api.g_varchar2
1055 ,p_reason_for_notification_dela in varchar2 default hr_api.g_varchar2
1056 ,p_accept_late_notification_fla in varchar2 default hr_api.g_varchar2
1057 ,p_linked_absence_id in number default hr_api.g_number
1058 ,p_batch_id in number default hr_api.g_number
1059 ,p_abs_information_category in varchar2 default hr_api.g_varchar2
1060 ,p_abs_information1 in varchar2 default hr_api.g_varchar2
1061 ,p_abs_information2 in varchar2 default hr_api.g_varchar2
1062 ,p_abs_information3 in varchar2 default hr_api.g_varchar2
1063 ,p_abs_information4 in varchar2 default hr_api.g_varchar2
1064 ,p_abs_information5 in varchar2 default hr_api.g_varchar2
1065 ,p_abs_information6 in varchar2 default hr_api.g_varchar2
1066 ,p_abs_information7 in varchar2 default hr_api.g_varchar2
1067 ,p_abs_information8 in varchar2 default hr_api.g_varchar2
1068 ,p_abs_information9 in varchar2 default hr_api.g_varchar2
1069 ,p_abs_information10 in varchar2 default hr_api.g_varchar2
1070 ,p_abs_information11 in varchar2 default hr_api.g_varchar2
1071 ,p_abs_information12 in varchar2 default hr_api.g_varchar2
1072 ,p_abs_information13 in varchar2 default hr_api.g_varchar2
1073 ,p_abs_information14 in varchar2 default hr_api.g_varchar2
1074 ,p_abs_information15 in varchar2 default hr_api.g_varchar2
1075 ,p_abs_information16 in varchar2 default hr_api.g_varchar2
1076 ,p_abs_information17 in varchar2 default hr_api.g_varchar2
1077 ,p_abs_information18 in varchar2 default hr_api.g_varchar2
1078 ,p_abs_information19 in varchar2 default hr_api.g_varchar2
1079 ,p_abs_information20 in varchar2 default hr_api.g_varchar2
1080 ,p_abs_information21 in varchar2 default hr_api.g_varchar2
1081 ,p_abs_information22 in varchar2 default hr_api.g_varchar2
1082 ,p_abs_information23 in varchar2 default hr_api.g_varchar2
1083 ,p_abs_information24 in varchar2 default hr_api.g_varchar2
1084 ,p_abs_information25 in varchar2 default hr_api.g_varchar2
1085 ,p_abs_information26 in varchar2 default hr_api.g_varchar2
1086 ,p_abs_information27 in varchar2 default hr_api.g_varchar2
1087 ,p_abs_information28 in varchar2 default hr_api.g_varchar2
1088 ,p_abs_information29 in varchar2 default hr_api.g_varchar2
1089 ,p_abs_information30 in varchar2 default hr_api.g_varchar2
1090 ,p_absence_case_id in number default hr_api.g_number
1091 ,p_program_application_id in number default 800
1092 ,p_called_from in number default 800
1093 ,p_object_version_number in out nocopy number
1094 ,p_dur_dys_less_warning out nocopy boolean
1095 ,p_dur_hrs_less_warning out nocopy boolean
1096 ,p_exceeds_pto_entit_warning out nocopy boolean
1097 ,p_exceeds_run_total_warning out nocopy boolean
1098 ,p_abs_overlap_warning out nocopy boolean
1099 ,p_abs_day_after_warning out nocopy boolean
1100 ,p_dur_overwritten_warning out nocopy boolean
1101 ,p_del_element_entry_warning out nocopy boolean
1102 ) is
1103 --
1104 -- Declare cursors and local variables
1105 --
1106
1107 cursor c_get_absence_dates is
1108 select abs.date_start, abs.date_end,
1109 abs.DATE_PROJECTED_START,abs.DATE_PROJECTED_END,
1110 abs.TIME_START , abs.TIME_END,
1111 abs.TIME_PROJECTED_START ,abs.TIME_PROJECTED_END
1112 from per_absence_attendances abs
1113 where abs.absence_attendance_id = p_absence_attendance_id;
1114
1115 cursor c_get_absence_details is
1116 select abs.person_id,
1117 abs.absence_attendance_type_id,
1118 abs.date_start,
1119 abs.date_end
1120 from per_absence_attendances abs
1121 where abs.absence_attendance_id = p_absence_attendance_id;
1122
1123
1124 cursor c_get_person_id is
1125 select abs.person_id
1126 from per_absence_attendances abs
1127 where abs.absence_attendance_id = p_absence_attendance_id;
1128
1129 l_csrperson_id number;
1130 l_retvalue varchar2(10);
1131 l_datestart date;
1132 l_dateend date;
1133 l_prjdatestart date;
1134 l_prjdateend date;
1135 l_timestart varchar2(10);
1136 l_timeend varchar2(10);
1137 l_prjtimestart varchar2(10);
1138 l_prjtimeend varchar2(10);
1139
1140 ls_datestart date;
1141 ls_dateend date;
1142 ls_prjdatestart date;
1143 ls_prjdateend date;
1144 ls_timestart varchar2(10);
1145 ls_timeend varchar2(10);
1146 ls_prjtimestart varchar2(10);
1147 ls_prjtimeend varchar2(10);
1148 l_glb_date date :=to_date('01-01--4712', 'DD-MM-SYYYY');
1149 l_glb_var varchar2(10) :='$Sys_Def$';
1150
1151
1152
1153
1154 l_proc varchar2(72) := g_package||'update_person_absence';
1155 l_date_projected_start date;
1156 l_date_projected_end date;
1157 l_date_start date;
1158 l_date_end date;
1159 l_date_notification date;
1160 l_effective_date date;
1161 l_old ben_abs_ler.g_abs_ler_rec;
1162 l_new ben_abs_ler.g_abs_ler_rec;
1163 --
1164 lv_object_version_number number;
1165 lv_absence_days number;
1166 lv_absence_hours number;
1167
1168 -- For bug 5454141
1169
1170 l_date_start1 date;
1171 l_date_end1 date;
1172 l_date_start_for_absence date;
1173 l_date_end_for_absence date;
1174
1175 -- End of added parameters for bug 5454141
1176
1177 -- Declare out parameters
1178 --
1179 l_object_version_number number;
1180 l_absence_days number;
1181 l_absence_hours number;
1182 l_person_id number;
1183 l_absence_attendance_type_id number;
1184 l_assignment_id number;
1185 l_element_entry_id number;
1186 l_effective_start_date date;
1187 l_effective_end_date date;
1188 l_processing_type pay_element_types_f.processing_type%TYPE;
1189 l_dur_dys_less_warning boolean;
1190 l_dur_hrs_less_warning boolean;
1191 l_exceeds_pto_entit_warning boolean;
1192 l_exceeds_run_total_warning boolean;
1193 l_abs_overlap_warning boolean;
1194 l_abs_day_after_warning boolean;
1195 l_dur_overwritten_warning boolean;
1196 l_del_element_entry_warning boolean := FALSE;
1197
1198 --
1199
1200 l_hours_or_days varchar2(2);
1201
1202 cursor csr_get_abstype is
1203 select HOURS_OR_DAYS,INPUT_VALUE_ID
1204 from per_absence_attendance_types
1205 where absence_attendance_type_id= (select absence_attendance_type_id
1206 from per_absence_attendances where ABSENCE_ATTENDANCE_ID = p_absence_attendance_id);
1207
1208
1209
1210 l_chk_datestart date;
1211 l_chk_dateend date;
1212 l_enty_efsd date;
1213 l_entry_efed date;
1214 l_elm_entry_id number;
1215 l_chk_abs_type_id number;
1216
1217 --
1218
1219 begin
1220
1221 hr_utility.set_location('Entering:'|| l_proc, 10);
1222 --
1223 lv_object_version_number := p_object_version_number ;
1224 lv_absence_days := p_absence_days ;
1225 lv_absence_hours := p_absence_hours ;
1226
1227 -- Issue a savepoint
1228 --
1229 savepoint update_person_absence;
1230
1231 --
1232 -- Pipe the main IN / IN OUT parameters for ease of debugging.
1233 --
1234 hr_utility.trace(' ');
1235 hr_utility.trace(' --------------------------------'||
1236 '---------------------------------');
1237 hr_utility.trace(' IN / IN OUT NOCOPY PARAMETER '||
1238 ' VALUE');
1239 hr_utility.trace(' --------------------------------'||
1240 '+--------------------------------');
1241 hr_utility.trace(' p_effective_date '||
1242 to_char(p_effective_date));
1243 hr_utility.trace(' p_absence_attendance_id '||
1244 to_char(p_absence_attendance_id));
1245 hr_utility.trace(' p_abs_attendance_reason_id '||
1246 to_char(p_abs_attendance_reason_id));
1247 hr_utility.trace(' p_date_notification '||
1248 to_char(p_date_notification));
1249 hr_utility.trace(' p_date_projected_start '||
1250 to_char(p_date_projected_start));
1251 hr_utility.trace(' p_time_projected_start '||
1252 p_time_projected_start);
1253 hr_utility.trace(' p_date_projected_end '||
1254 to_char(p_date_projected_end));
1255 hr_utility.trace(' p_time_projected_end '||
1256 p_time_projected_end);
1257 hr_utility.trace(' p_date_start '||
1258 to_char(p_date_start));
1259 hr_utility.trace(' p_time_start '||
1260 p_time_start);
1261 hr_utility.trace(' p_date_end '||
1262 to_char(p_date_end));
1263 hr_utility.trace(' p_time_end '||
1264 p_time_end);
1265 hr_utility.trace(' p_absence_days '||
1266 to_char(p_absence_days));
1267 hr_utility.trace(' p_absence_hours '||
1268 to_char(p_absence_hours));
1269 hr_utility.trace(' p_authorising_person_id '||
1270 to_char(p_authorising_person_id));
1271 hr_utility.trace(' p_batch_id '||
1272 to_char(p_batch_id));
1273 hr_utility.trace(' p_object_version_number '||
1274 to_char(p_object_version_number));
1275 hr_utility.trace(' --------------------------------'||
1276 '---------------------------------');
1277 hr_utility.trace(' ');
1278
1279 --
1280 if nvl(FND_PROFILE.Value('HR_ABS_OTL_INTEGRATION'),'N') = 'Y' then
1281
1282
1283 open csr_get_abstype;
1284 fetch csr_get_abstype into l_hours_or_days,l_elm_entry_id;
1285 close csr_get_abstype;
1286
1287 if l_elm_entry_id is not null then
1288
1289 open c_get_person_id;
1290 fetch c_get_person_id into l_csrperson_id;
1291 close c_get_person_id;
1292
1293 l_datestart:= p_date_start;
1294 l_dateend := p_date_end;
1295 l_prjdatestart:=p_date_projected_start;
1296 l_prjdateend:=p_date_projected_end;
1297 l_timestart:=p_time_start;
1298 l_timeend:=p_time_end;
1299 l_prjtimestart:=p_time_projected_start;
1300 l_prjtimeend :=p_time_projected_end;
1301
1302
1303 hr_utility.set_location(' l_date_start ' ||l_datestart,20);
1304 hr_utility.set_location(' p_date_start ' ||p_date_start,22);
1305 hr_utility.set_location(' l_dateend '|| l_dateend,22);
1306 hr_utility.set_location(' l_prjdatestart ' || l_prjdatestart,22);
1307 hr_utility.set_location(' l_prjdateend ' || l_prjdateend,22);
1308 hr_utility.set_location(' l_timestart ' || l_timestart,22);
1309 hr_utility.set_location(' l_timeend ' || l_timeend,22);
1310 hr_utility.set_location(' l_prjtimestart ' || l_prjtimestart,22);
1311 hr_utility.set_location(' l_prjtimeend ' || l_prjtimeend,22);
1312
1313 if p_date_start =hr_api.g_date or p_date_end = hr_api.g_date
1314 or p_date_projected_start =hr_api.g_date or p_date_projected_end = hr_api.g_date
1315 or p_time_start =hr_api.g_varchar2 or p_time_end = hr_api.g_varchar2
1316 or p_time_projected_start =hr_api.g_varchar2 or p_time_projected_end = hr_api.g_varchar2 then
1317
1318 open c_get_absence_dates;
1319 fetch c_get_absence_dates into l_datestart,l_dateend,l_prjdatestart,
1320 l_prjdateend,l_timestart,l_timeend,l_prjtimestart,l_prjtimeend;
1321 close c_get_absence_dates;
1322 hr_utility.set_location(' l_date_start ' ||l_date_start,20);
1323 if p_date_start <> hr_api.g_date and l_datestart is not null then
1324 l_datestart :=p_date_start;
1325 end if;
1326
1327 if p_date_end <> hr_api.g_date and l_dateend is not null then
1328 l_dateend :=p_date_end;
1329 end if;
1330
1331
1332 if p_date_projected_start <> hr_api.g_date and l_prjdatestart is not null then
1333 l_prjdatestart :=p_date_projected_start;
1334 end if;
1335
1336 if p_date_projected_end <> hr_api.g_date and l_prjdateend is not null then
1337 l_prjdateend :=p_date_projected_end;
1338 end if;
1339
1340 if p_time_projected_start <> hr_api.g_varchar2 and l_timestart is not null then
1341 l_prjtimestart :=p_time_projected_start;
1342 end if;
1343
1344 if p_time_projected_end <> hr_api.g_varchar2 and l_prjtimeend is not null then
1345 l_prjtimeend :=p_time_projected_end;
1346 end if;
1347
1348
1349 if p_time_start <> hr_api.g_varchar2 and l_timestart is not null then
1350 l_timestart :=p_time_start;
1351 end if;
1352
1353 if p_time_end <> hr_api.g_varchar2 and l_timeend is not null then
1354 l_timeend :=p_time_end;
1355 end if;
1356
1357
1358 end if;
1359
1360 hr_utility.set_location(' l_hours_or_days ' ||l_hours_or_days,250);
1361 hr_utility.set_location(' l_datestart ' ||l_datestart,30);
1362
1363 hr_utility.set_location(' p_date_start ' ||p_date_start,30);
1364 hr_utility.set_location(' l_dateend '|| l_dateend,30);
1365 hr_utility.set_location(' l_prjdatestart ' || l_prjdatestart,30);
1366 hr_utility.set_location(' l_prjdateend ' || l_prjdateend,30);
1367 hr_utility.set_location(' l_timestart ' || l_timestart,30);
1368 hr_utility.set_location(' l_timeend ' || l_timeend,30);
1369 hr_utility.set_location(' l_prjtimestart ' || l_prjtimestart,30);
1370 hr_utility.set_location(' l_prjtimeend ' || l_prjtimeend,30);
1371
1372 if l_hours_or_days ='H' then
1373 -- when the UOM attached to the element is Hours
1374
1375 if ( l_datestart is null ) then
1376
1377 if ( l_prjdatestart is null ) then
1378 hr_utility.set_message (800,'HR_449868_START_END_DATES5');
1379 hr_utility.raise_error;
1380 end if;
1381
1382 --if p_time_projected_start is null or p_date_projected_end = hr_api.g_date or p_time_projected_end is null then
1383
1384 if (l_prjtimestart is null) or (l_prjdateend is null ) or (l_prjtimeend is null ) then
1385
1386 hr_utility.set_location(' l_hours_or_days 30 ' ||l_hours_or_days,30);
1387 hr_utility.set_message(800, 'HR_449866_START_END_DATES3');
1388 hr_utility.raise_error;
1389 end if;
1390
1391 hr_utility.set_location(' l_hours_or_days ' ||l_hours_or_days,20);
1392 hr_utility.set_location(' l_hours_or_days ' ||l_hours_or_days,40);
1393
1394 else
1395
1396 if (l_dateend is null ) or ( l_timeend is null) or(l_timestart is null ) then
1397 hr_utility.set_message (800,'HR_449867_START_END_DATES4');
1398 hr_utility.raise_error;
1399 end if;
1400
1401 end if;
1402
1403 else
1404
1405 -- when the UOM is Days
1406
1407 hr_utility.set_location(' l_hours_or_days ' ||l_hours_or_days,25);
1408
1409
1410 if l_datestart is null then
1411
1412
1413 if l_prjdatestart is null then
1414 hr_utility.set_message (800,'HR_449868_START_END_DATES5');
1415 hr_utility.raise_error;
1416
1417 elsif l_prjdateend is null then
1418
1419 hr_utility.set_message (800,'HR_449864_START_END_DATES1');
1420 hr_utility.raise_error;
1421 end if;
1422
1423 else
1424
1425 /* if (p_date_projected_start is null or p_date_projected_start= hr_api.g_date)
1426 and ( p_date_projected_end is null or p_date_projected_end = hr_api.g_date) then
1427
1428 hr_utility.set_message (800,'HR_449864_START_END_DATES1');
1429 hr_utility.raise_error;
1430 end if;
1431 */
1432
1433 if l_dateend is null then
1434
1435 hr_utility.set_message (800,'HR_449865_START_END_DATES2');
1436 hr_utility.raise_error;
1437 end if;
1438
1439 end if;
1440
1441 end if;
1442
1443 l_chk_datestart := nvl(l_datestart,l_prjdatestart);
1444 l_chk_dateend := nvl(l_dateend,l_prjdateend);
1445
1446 if p_called_from <> 809 and l_chk_datestart is not null and l_chk_dateend is not null
1447 then
1448 hr_utility.set_location('inside otl hr check ', 10);
1449
1450 otl_hr_check
1451 (
1452 p_person_id => l_csrperson_id,
1453 p_date_start => l_chk_datestart,
1454 p_date_end => l_chk_dateend,
1455 p_scope => 'UPDATE',
1456 p_ret_value => l_retvalue );
1457
1458
1459 hr_utility.set_location('after otl hr check ', 10);
1460
1461 end if;
1462
1463 -- to allow the projected leaves to get confirmed
1464 -- when elementry id is null
1465
1466 hr_utility.set_location('l_retvalue'||l_retvalue, 10);
1467
1468 if l_retvalue ='RESTRICT' THEN
1469
1470
1471 hr_utility.set_location(' p_date_projected_start'||p_date_projected_start, 10);
1472 hr_utility.set_location('p_time_projected_start'||p_time_projected_start, 10);
1473 hr_utility.set_location('p_date_projected_end'||p_date_projected_end, 10);
1474 hr_utility.set_location('p_time_projected_end'||p_time_projected_end, 10);
1475 hr_utility.set_location('p_date_start'||p_date_start, 10);
1476 hr_utility.set_location('p_date_end'||p_date_end, 10);
1477 hr_utility.set_location('p_time_start'||p_time_start, 10);
1478 hr_utility.set_location('p_time_end'||p_time_end, 10);
1479
1480 open c_get_absence_dates;
1481 fetch c_get_absence_dates into l_datestart,l_dateend,l_prjdatestart,
1482 l_prjdateend,l_timestart,l_timeend,l_prjtimestart,l_prjtimeend;
1483 close c_get_absence_dates;
1484
1485
1486 if p_date_projected_start is null or p_date_projected_start =hr_api.g_date then
1487 ls_prjdatestart := l_prjdatestart;
1488 else
1489 ls_prjdatestart :=p_date_projected_start;
1490 end if;
1491
1492 if p_date_projected_end is null or p_date_projected_end =hr_api.g_date then
1493 ls_prjdateend := l_prjdateend;
1494 else
1495 ls_prjdateend :=p_date_projected_end;
1496 end if;
1497
1498 if p_time_projected_start is null or p_time_projected_start = hr_api.g_varchar2 then
1499 ls_prjtimestart := l_prjtimestart;
1500 else
1501 ls_prjtimestart := p_time_projected_start;
1502 end if;
1503
1504 if p_time_projected_end is null or p_time_projected_end =hr_api.g_varchar2 then
1505 ls_prjtimeend := l_prjtimeend;
1506 else
1507 ls_prjtimeend :=p_time_projected_end;
1508 end if;
1509
1510 if p_date_start is null or p_date_start =hr_api.g_date then
1511 ls_datestart := l_datestart;
1512 else
1513 ls_datestart :=p_date_start;
1514 end if;
1515
1516 if p_date_end is null or p_date_end =hr_api.g_date then
1517 ls_dateend := l_dateend;
1518 else
1519 ls_dateend :=p_date_end;
1520 end if;
1521
1522 if p_time_start is null or p_time_start =hr_api.g_varchar2 then
1523 ls_timestart := l_timestart;
1524 else
1525 ls_timestart :=p_time_start;
1526 end if;
1527
1528 if p_time_end is null or p_time_end =hr_api.g_varchar2 then
1529 ls_timeend := l_timeend;
1530 else
1531 ls_timeend :=p_time_end;
1532 end if;
1533
1534
1535
1536 hr_utility.set_location(' l_datestart'|| l_datestart, 10);
1537 hr_utility.set_location(' l_dateend'|| l_dateend , 10);
1538 hr_utility.set_location(' l_prjdatestart'|| l_prjdatestart , 10);
1539 hr_utility.set_location(' l_prjdateend'|| l_prjdateend , 10);
1540 hr_utility.set_location(' l_timestart'|| l_timestart , 10);
1541 hr_utility.set_location(' l_timeend'|| l_timeend , 10);
1542 hr_utility.set_location(' l_prjtimestart'|| l_prjtimestart , 10);
1543 hr_utility.set_location(' l_prjtimeend'|| l_prjtimeend , 10);
1544
1545
1546 hr_utility.set_location(' ls_prjdatestart'|| ls_prjdatestart,20);
1547 hr_utility.set_location(' ls_prjdateend'|| ls_prjdateend , 20);
1548 hr_utility.set_location(' ls_prjtimestart'|| ls_prjtimestart , 20);
1549 hr_utility.set_location(' ls_prjtimeend'|| ls_prjtimeend ,20);
1550 hr_utility.set_location(' ls_datestart'|| ls_datestart , 20);
1551 hr_utility.set_location(' ls_dateend'|| ls_dateend , 20);
1552 hr_utility.set_location(' ls_timestart'|| ls_timestart , 20);
1553 hr_utility.set_location(' ls_timeend'|| ls_timeend ,20);
1554
1555 if l_datestart is null and p_date_start is not null then
1556 hr_utility.set_location('before comparing dates', 5);
1557
1558 IF nvl(ls_prjdatestart,l_glb_date) <> nvl(l_prjdatestart,l_glb_date)
1559 or nvl(ls_prjdateend,l_glb_date) <> nvl(l_prjdateend,l_glb_date)
1560 or nvl(ls_prjtimestart,l_glb_var)<> nvl(l_prjtimestart,l_glb_var)
1561 or nvl(ls_prjtimeend,l_glb_var)<> nvl(l_prjtimeend,l_glb_var)
1562 or nvl(ls_datestart,l_glb_date) <> nvl(p_date_start,l_glb_date)
1563 or nvl(ls_dateend,l_glb_date) <> nvl(p_date_end,l_glb_date)
1564 or nvl(ls_timestart,l_glb_var) <> nvl(p_time_start,l_glb_var)
1565 or nvl(ls_timeend,l_glb_var) <> nvl(p_time_end,l_glb_var)
1566 or nvl(p_date_start,l_prjdatestart) <> nvl (l_prjdatestart,p_date_start)
1567 or nvl(p_date_end,l_prjdateend) <> nvl(l_prjdateend,p_date_end)
1568 or nvl(p_time_start,l_prjtimestart) <> nvl (l_prjtimestart,p_time_start)
1569 or nvl(p_time_end,l_prjtimeend) <> nvl (l_prjtimeend,p_time_end)
1570
1571
1572 then
1573
1574 hr_utility.set_location('While confirming dates should not be changed', 10);
1575
1576 hr_utility.set_location('otl hr UPDATE check ', 60);
1577 hr_utility.set_message(800,'HR_50433_OTL_CARD_EXISTS');
1578 hr_utility.raise_error;
1579 END IF;
1580 end if;
1581 END IF;
1582
1583 end if;
1584
1585 end if;
1586 --
1587 --
1588
1589
1590 -- Truncate the time portion from all IN date parameters
1591 --
1592 l_effective_date := trunc(p_effective_date);
1593 l_date_notification := trunc(p_date_notification);
1594 l_date_projected_start := trunc(p_date_projected_start);
1595 l_date_projected_end := trunc(p_date_projected_end);
1596 l_date_start := trunc(p_date_start);
1597 l_date_end := trunc(p_date_end);
1598
1599 l_absence_days := p_absence_days;
1600 l_absence_hours := p_absence_hours;
1601
1602 hr_utility.trace('Old Dur Dys: '||to_char(l_absence_days));
1603 hr_utility.trace('Old Dur Hrs: '||to_char(l_absence_hours));
1604
1605 --
1606 -- Call Before Process User Hook
1607 --
1608 begin
1609 hr_person_absence_bk2.update_person_absence_b
1610 (p_effective_date => l_effective_date
1611 ,p_absence_attendance_id => p_absence_attendance_id
1612 ,p_object_version_number => p_object_version_number
1613 ,p_abs_attendance_reason_id => p_abs_attendance_reason_id
1614 ,p_comments => p_comments
1615 ,p_date_notification => l_date_notification
1616 ,p_date_projected_start => l_date_projected_start
1617 ,p_time_projected_start => p_time_projected_start
1618 ,p_date_projected_end => l_date_projected_end
1619 ,p_time_projected_end => p_time_projected_end
1620 ,p_date_start => l_date_start
1621 ,p_time_start => p_time_start
1622 ,p_date_end => l_date_end
1623 ,p_time_end => p_time_end
1624 ,p_absence_days => l_absence_days
1625 ,p_absence_hours => l_absence_hours
1626 ,p_authorising_person_id => p_authorising_person_id
1627 ,p_replacement_person_id => p_replacement_person_id
1628 ,p_attribute_category => p_attribute_category
1629 ,p_attribute1 => p_attribute1
1630 ,p_attribute2 => p_attribute2
1631 ,p_attribute3 => p_attribute3
1632 ,p_attribute4 => p_attribute4
1633 ,p_attribute5 => p_attribute5
1634 ,p_attribute6 => p_attribute6
1635 ,p_attribute7 => p_attribute7
1636 ,p_attribute8 => p_attribute8
1637 ,p_attribute9 => p_attribute9
1638 ,p_attribute10 => p_attribute10
1639 ,p_attribute11 => p_attribute11
1640 ,p_attribute12 => p_attribute12
1641 ,p_attribute13 => p_attribute13
1642 ,p_attribute14 => p_attribute14
1643 ,p_attribute15 => p_attribute15
1644 ,p_attribute16 => p_attribute16
1645 ,p_attribute17 => p_attribute17
1646 ,p_attribute18 => p_attribute18
1647 ,p_attribute19 => p_attribute19
1648 ,p_attribute20 => p_attribute20
1649 ,p_period_of_incapacity_id => p_period_of_incapacity_id
1650 ,p_ssp1_issued => p_ssp1_issued
1651 ,p_maternity_id => p_maternity_id
1652 ,p_sickness_start_date => p_sickness_start_date
1653 ,p_sickness_end_date => p_sickness_end_date
1654 ,p_pregnancy_related_illness => p_pregnancy_related_illness
1655 ,p_reason_for_notification_dela => p_reason_for_notification_dela
1656 ,p_accept_late_notification_fla => p_accept_late_notification_fla
1657 ,p_linked_absence_id => p_linked_absence_id
1658 ,p_absence_case_id => p_absence_case_id
1659 ,p_batch_id => p_batch_id
1660 ,p_abs_information_category => p_abs_information_category
1661 ,p_abs_information1 => p_abs_information1
1662 ,p_abs_information2 => p_abs_information2
1663 ,p_abs_information3 => p_abs_information3
1664 ,p_abs_information4 => p_abs_information4
1665 ,p_abs_information5 => p_abs_information5
1666 ,p_abs_information6 => p_abs_information6
1667 ,p_abs_information7 => p_abs_information7
1668 ,p_abs_information8 => p_abs_information8
1669 ,p_abs_information9 => p_abs_information9
1670 ,p_abs_information10 => p_abs_information10
1671 ,p_abs_information11 => p_abs_information11
1672 ,p_abs_information12 => p_abs_information12
1673 ,p_abs_information13 => p_abs_information13
1674 ,p_abs_information14 => p_abs_information14
1675 ,p_abs_information15 => p_abs_information15
1676 ,p_abs_information16 => p_abs_information16
1677 ,p_abs_information17 => p_abs_information17
1678 ,p_abs_information18 => p_abs_information18
1679 ,p_abs_information19 => p_abs_information19
1680 ,p_abs_information20 => p_abs_information20
1681 ,p_abs_information21 => p_abs_information21
1682 ,p_abs_information22 => p_abs_information22
1683 ,p_abs_information23 => p_abs_information23
1684 ,p_abs_information24 => p_abs_information24
1685 ,p_abs_information25 => p_abs_information25
1686 ,p_abs_information26 => p_abs_information26
1687 ,p_abs_information27 => p_abs_information27
1688 ,p_abs_information28 => p_abs_information28
1689 ,p_abs_information29 => p_abs_information29
1690 ,p_abs_information30 => p_abs_information30
1691 );
1692 exception
1693 when hr_api.cannot_find_prog_unit then
1694 hr_api.cannot_find_prog_unit_error
1695 (p_module_name => 'UPDATE_PERSON_ABSENCE'
1696 ,p_hook_type => 'BP'
1697 );
1698 end;
1699
1700 --
1701 hr_utility.set_location(l_proc, 30);
1702 --
1703 l_object_version_number := p_object_version_number;
1704 --
1705 -- Update Person Absence
1706 per_abs_upd.upd
1707 (p_effective_date => l_effective_date
1708 ,p_absence_attendance_id => p_absence_attendance_id
1709 ,p_abs_attendance_reason_id => p_abs_attendance_reason_id
1710 ,p_authorising_person_id => p_authorising_person_id
1711 ,p_replacement_person_id => p_replacement_person_id
1712 ,p_absence_days => l_absence_days
1713 ,p_absence_hours => l_absence_hours
1714 ,p_comments => p_comments
1715 ,p_date_notification => l_date_notification
1716 ,p_date_projected_start => l_date_projected_start
1717 ,p_date_projected_end => l_date_projected_end
1718 ,p_date_start => l_date_start
1719 ,p_date_end => l_date_end
1720 ,p_time_start => p_time_start
1721 ,p_time_end => p_time_end
1722 ,p_time_projected_start => p_time_projected_start
1723 ,p_time_projected_end => p_time_projected_end
1724 ,p_attribute_category => p_attribute_category
1725 ,p_attribute1 => p_attribute1
1726 ,p_attribute2 => p_attribute2
1727 ,p_attribute3 => p_attribute3
1728 ,p_attribute4 => p_attribute4
1729 ,p_attribute5 => p_attribute5
1730 ,p_attribute6 => p_attribute6
1731 ,p_attribute7 => p_attribute7
1732 ,p_attribute8 => p_attribute8
1733 ,p_attribute9 => p_attribute9
1734 ,p_attribute10 => p_attribute10
1735 ,p_attribute11 => p_attribute11
1736 ,p_attribute12 => p_attribute12
1737 ,p_attribute13 => p_attribute13
1738 ,p_attribute14 => p_attribute14
1739 ,p_attribute15 => p_attribute15
1740 ,p_attribute16 => p_attribute16
1741 ,p_attribute17 => p_attribute17
1742 ,p_attribute18 => p_attribute18
1743 ,p_attribute19 => p_attribute19
1744 ,p_attribute20 => p_attribute20
1745 ,p_period_of_incapacity_id => p_period_of_incapacity_id
1746 ,p_ssp1_issued => p_ssp1_issued
1747 ,p_maternity_id => p_maternity_id
1748 ,p_sickness_start_date => p_sickness_start_date
1749 ,p_sickness_end_date => p_sickness_end_date
1750 ,p_pregnancy_related_illness => p_pregnancy_related_illness
1751 ,p_reason_for_notification_dela => p_reason_for_notification_dela
1752 ,p_accept_late_notification_fla => p_accept_late_notification_fla
1753 ,p_linked_absence_id => p_linked_absence_id
1754 ,p_batch_id => p_batch_id
1755 ,p_abs_information_category => p_abs_information_category
1756 ,p_abs_information1 => p_abs_information1
1757 ,p_abs_information2 => p_abs_information2
1758 ,p_abs_information3 => p_abs_information3
1759 ,p_abs_information4 => p_abs_information4
1760 ,p_abs_information5 => p_abs_information5
1761 ,p_abs_information6 => p_abs_information6
1762 ,p_abs_information7 => p_abs_information7
1763 ,p_abs_information8 => p_abs_information8
1764 ,p_abs_information9 => p_abs_information9
1765 ,p_abs_information10 => p_abs_information10
1766 ,p_abs_information11 => p_abs_information11
1767 ,p_abs_information12 => p_abs_information12
1768 ,p_abs_information13 => p_abs_information13
1769 ,p_abs_information14 => p_abs_information14
1770 ,p_abs_information15 => p_abs_information15
1771 ,p_abs_information16 => p_abs_information16
1772 ,p_abs_information17 => p_abs_information17
1773 ,p_abs_information18 => p_abs_information18
1774 ,p_abs_information19 => p_abs_information19
1775 ,p_abs_information20 => p_abs_information20
1776 ,p_abs_information21 => p_abs_information21
1777 ,p_abs_information22 => p_abs_information22
1778 ,p_abs_information23 => p_abs_information23
1779 ,p_abs_information24 => p_abs_information24
1780 ,p_abs_information25 => p_abs_information25
1781 ,p_abs_information26 => p_abs_information26
1782 ,p_abs_information27 => p_abs_information27
1783 ,p_abs_information28 => p_abs_information28
1784 ,p_abs_information29 => p_abs_information29
1785 ,p_abs_information30 => p_abs_information30
1786 ,p_absence_case_id => p_absence_case_id
1787 ,p_program_application_id => p_program_application_id
1788 ,p_object_version_number => l_object_version_number
1789 ,p_dur_dys_less_warning => l_dur_dys_less_warning
1790 ,p_dur_hrs_less_warning => l_dur_hrs_less_warning
1791 ,p_exceeds_pto_entit_warning => l_exceeds_pto_entit_warning
1792 ,p_exceeds_run_total_warning => l_exceeds_run_total_warning
1793 ,p_abs_overlap_warning => l_abs_overlap_warning
1794 ,p_abs_day_after_warning => l_abs_day_after_warning
1795 ,p_dur_overwritten_warning => l_dur_overwritten_warning
1796 );
1797
1798 --
1799 -- Assign the out parameters.
1800 --
1801 p_object_version_number := l_object_version_number;
1802 p_dur_dys_less_warning := l_dur_dys_less_warning;
1803 p_dur_hrs_less_warning := l_dur_hrs_less_warning;
1804 p_exceeds_pto_entit_warning := l_exceeds_pto_entit_warning;
1805 p_exceeds_run_total_warning := l_exceeds_run_total_warning;
1806 p_abs_overlap_warning := l_abs_overlap_warning;
1807 p_abs_day_after_warning := l_abs_day_after_warning;
1808 p_dur_overwritten_warning := l_dur_overwritten_warning;
1809 p_del_element_entry_warning := l_del_element_entry_warning;
1810 p_absence_days := l_absence_days;
1811 p_absence_hours := l_absence_hours;
1812
1813 hr_utility.trace('New Dur Dys: '||to_char(l_absence_days));
1814 hr_utility.trace('New Dur Hrs: '||to_char(l_absence_hours));
1815
1816
1817 hr_utility.set_location('Start of absence element entry section', 40);
1818
1819 /* Start of Absence Element Entry Section */
1820 --
1821 -- Update or insert the absence element element. First we
1822 -- check if the absence type is linked to an element type.
1823 --
1824
1825 /* Level 1 */
1826 if linked_to_element
1827 (p_absence_attendance_id => p_absence_attendance_id)
1828 then
1829
1830 --
1831 -- Get the person_id, assignment_id, assignment_type_id
1832 -- and processing type for use later
1833 --
1834
1835 open c_get_absence_details;
1836 fetch c_get_absence_details into l_person_id,
1837 l_absence_attendance_type_id,
1838 l_date_start1,
1839 l_date_end1;
1840 close c_get_absence_details;
1841
1842 --
1843 -- Replace start date and end date by their db row
1844 -- values if they are defaulted to hr_api.g_date
1845 -- for correct element entry validations.
1846 -- This is done for bug 5454141
1847 --
1848
1849 if (p_date_start = hr_api.g_date)
1850 then
1851 l_date_start_for_absence := l_date_start1;
1852 else
1853 l_date_start_for_absence := p_date_start;
1854 end if;
1855
1856 if (p_date_end = hr_api.g_date)
1857 then
1858 l_date_end_for_absence := l_date_end1;
1859 else
1860 l_date_end_for_absence := p_date_end;
1861 end if;
1862
1863 -- End of additions for bug 5454141
1864
1865 l_assignment_id := get_primary_assignment
1866 (p_person_id => l_person_id
1867 ,p_effective_date => l_date_start_for_absence); --fix for bug 7191231
1868
1869 l_processing_type := get_processing_type
1870 (p_absence_attendance_type_id => l_absence_attendance_type_id);
1871
1872 --
1873 -- We determine if an entry already exists.
1874 --
1875 get_absence_element
1876 (p_absence_attendance_id => p_absence_attendance_id
1877 ,p_element_entry_id => l_element_entry_id
1878 ,p_effective_start_date => l_effective_start_date
1879 ,p_effective_end_date => l_effective_end_date);
1880
1881
1882 /* Level 2 */
1883 if l_element_entry_id is null then
1884 --
1885 -- Scenario 1.
1886 -- An entry does not already exist. Insert if we have
1887 -- the appropriate dates.
1888 --
1889 hr_utility.set_location('Scenario 1', 45);
1890
1891 if (l_processing_type = 'N'
1892 and l_date_start_for_absence is not null
1893 and l_date_end_for_absence is not null)
1894 or (l_processing_type = 'R'
1895 and l_date_start_for_absence is not null)
1896 then
1897
1898 insert_absence_element
1899 (p_date_start => l_date_start_for_absence
1900 ,p_assignment_id => l_assignment_id
1901 ,p_absence_attendance_id => p_absence_attendance_id
1902 ,p_element_entry_id => l_element_entry_id);
1903
1904 if l_processing_type = 'R' and l_date_end_for_absence is not null then
1905 --
1906 -- Scenario 2.
1907 -- If this is a recurring element entry and we have the
1908 -- absence end date, we date effectively delete the
1909 -- element immediately, otherwise it remains open until
1910 -- the end of time.
1911 --
1912 hr_utility.set_location('Scenario 2', 50);
1913
1914 delete_absence_element
1915 (p_dt_delete_mode => 'DELETE'
1916 ,p_session_date => l_date_end_for_absence
1917 ,p_element_entry_id => l_element_entry_id);
1918 end if;
1919
1920 end if;
1921
1922 else
1923 --
1924 -- An entry already exists. Update it as appropriate.
1925 --
1926 /* Level 3 */
1927 if (l_processing_type = 'R' and l_date_start_for_absence is null)
1928 or (l_processing_type = 'N' and (l_date_start_for_absence is null
1929 or l_date_end_for_absence is null)) then
1930 --
1931 -- Scenario 3.
1932 -- The element entry should be purged because the
1933 -- actual dates have been removed.
1934 --
1935 hr_utility.set_location('Scenario 3', 55);
1936
1937 --
1938 -- Warn the user before deleting.
1939 --
1940 l_del_element_entry_warning := TRUE;
1941
1942 delete_absence_element
1943 (p_dt_delete_mode => 'ZAP'
1944 ,p_session_date => l_effective_start_date
1945 ,p_element_entry_id => l_element_entry_id);
1946
1947 elsif l_processing_type = 'N' and l_date_start_for_absence not between
1948 l_effective_start_date and l_effective_end_date then
1949 --
1950 -- Scenario 4.
1951 -- The start date cannot be moved outside the entry's
1952 -- current period for non-recurring entries.
1953 --
1954 hr_utility.set_location('Scenario 4', 60);
1955
1956 fnd_message.set_name ('PAY', 'HR_6744_ABS_DET_ENTRY_PERIOD');
1957 /* Commented and added as a part of Bug#10239011 Starts
1958 fnd_message.set_token ('PERIOD_FROM',
1959 fnd_date.date_to_chardate(l_effective_start_date));
1960 fnd_message.set_token ('PERIOD_TO',
1961 fnd_date.date_to_chardate(l_effective_end_date)); */
1962 fnd_message.set_token ('PERIOD_FROM',
1963 fnd_date.date_to_chardate(l_effective_start_date, calendar_aware => FND_DATE.calendar_aware_alt));
1964 fnd_message.set_token ('PERIOD_TO',
1965 fnd_date.date_to_chardate(l_effective_end_date, calendar_aware => FND_DATE.calendar_aware_alt));
1966 /* Commented and added as a part of Bug#10239011 Ends */
1967 fnd_message.raise_error;
1968
1969 elsif l_processing_type = 'N' then
1970 --
1971 -- Scenario 5.
1972 -- Update the existing entry with the new input values.
1973 -- For simplicity, we make the update even if the value
1974 -- has not changed.
1975 --
1976 hr_utility.set_location('Scenario 5', 65);
1977
1978 update_absence_element
1979 (p_dt_update_mode => 'CORRECTION'
1980 ,p_session_date => l_effective_start_date
1981 ,p_element_entry_id => l_element_entry_id
1982 ,p_absence_attendance_id => p_absence_attendance_id);
1983
1984 elsif l_processing_type = 'R'
1985 and l_date_start_for_absence <> l_effective_start_date then
1986
1987 --
1988 -- Scenario 6.
1989 -- The start date has been moved. As this is part of the
1990 -- primary key we must delete the entry and re-insert it.
1991 --
1992 hr_utility.set_location('Scenario 6', 70);
1993
1994 delete_absence_element
1995 (p_dt_delete_mode => 'ZAP'
1996 ,p_session_date => l_effective_start_date
1997 ,p_element_entry_id => l_element_entry_id);
1998
1999 insert_absence_element
2000 (p_date_start => l_date_start_for_absence
2001 ,p_assignment_id => l_assignment_id
2002 ,p_absence_attendance_id => p_absence_attendance_id
2003 ,p_element_entry_id => l_element_entry_id);
2004
2005 if l_date_end_for_absence is not null then
2006 --
2007 -- We have the absence end date, we date effectively
2008 -- delete the element immediately, otherwise it
2009 -- remains open until the end of time.
2010 --
2011
2012 delete_absence_element
2013 (p_dt_delete_mode => 'DELETE'
2014 ,p_session_date => l_date_end_for_absence
2015 ,p_element_entry_id => l_element_entry_id);
2016 end if;
2017
2018 elsif l_processing_type = 'R' and
2019 (l_date_end_for_absence is null or
2020 l_date_end_for_absence <> l_effective_end_date) then
2021 --
2022 -- Scenario 7.
2023 -- The end date has:
2024 -- . changed
2025 -- . been removed
2026 -- . entered for the first time
2027 -- . still not been entered.
2028 --
2029 hr_utility.set_location('Scenario 7', 75);
2030
2031 if l_effective_end_date <> hr_api.g_eot then
2032 --
2033 -- End date has been changed or removed so we
2034 -- remove the end date so it continues through
2035 -- until the end of time.
2036 --
2037 hr_utility.set_location(l_proc, 76);
2038
2039 delete_absence_element
2040 (p_dt_delete_mode => 'DELETE_NEXT_CHANGE'
2041 ,p_session_date => l_effective_end_date
2042 ,p_element_entry_id => l_element_entry_id);
2043 end if;
2044
2045 if l_date_end_for_absence is not null then
2046 --
2047 -- End date has been changed or entered for
2048 -- the first time. We end the element entry
2049 -- at the end date.
2050 --
2051 hr_utility.set_location(l_proc, 78);
2052
2053 delete_absence_element
2054 (p_dt_delete_mode => 'DELETE'
2055 ,p_session_date => l_date_end_for_absence
2056 ,p_element_entry_id => l_element_entry_id);
2057 end if;
2058
2059 /* Level 3 */
2060 end if;
2061
2062 /* Level 2 */
2063 end if;
2064
2065 /* Level 1 */
2066 end if;
2067
2068
2069 /* End of Absence Element Entry Section */
2070
2071 hr_utility.set_location('End of absence element entry section', 80);
2072
2073 populate_ben_absence_rec
2074 (p_absence_attendance_id => p_absence_attendance_id,
2075 p_rec_type => 'O',
2076 p_ben_rec => l_old);
2077
2078 populate_ben_absence_rec
2079 (p_absence_attendance_id => p_absence_attendance_id,
2080 p_rec_type => 'N',
2081 p_ben_rec => l_new);
2082
2083 --
2084 -- Start of BEN call.
2085 --
2086 hr_utility.set_location('Start of BEN call', 82);
2087
2088 ben_abs_ler.ler_chk
2089 (p_old => l_old
2090 ,p_new => l_new
2091 ,p_effective_date => l_effective_date);
2092
2093 hr_utility.set_location('End of BEN call', 84);
2094
2095 --
2096 -- Call After Process User Hook
2097 --
2098 begin
2099 hr_person_absence_bk2.update_person_absence_a
2100 (p_effective_date => l_effective_date
2101 ,p_absence_attendance_id => p_absence_attendance_id
2102 ,p_object_version_number => l_object_version_number
2103 ,p_abs_attendance_reason_id => p_abs_attendance_reason_id
2104 ,p_comments => p_comments
2105 ,p_date_notification => l_date_notification
2106 ,p_date_projected_start => l_date_projected_start
2107 ,p_time_projected_start => p_time_projected_start
2108 ,p_date_projected_end => l_date_projected_end
2109 ,p_time_projected_end => p_time_projected_end
2110 ,p_date_start => l_date_start_for_absence -- 9877578
2111 ,p_time_start => p_time_start
2112 ,p_date_end => l_date_end_for_absence -- 9877578
2113 ,p_time_end => p_time_end
2114 ,p_absence_days => l_absence_days
2115 ,p_absence_hours => l_absence_hours
2116 ,p_authorising_person_id => p_authorising_person_id
2117 ,p_replacement_person_id => p_replacement_person_id
2118 ,p_attribute_category => p_attribute_category
2119 ,p_attribute1 => p_attribute1
2120 ,p_attribute2 => p_attribute2
2121 ,p_attribute3 => p_attribute3
2122 ,p_attribute4 => p_attribute4
2123 ,p_attribute5 => p_attribute5
2124 ,p_attribute6 => p_attribute6
2125 ,p_attribute7 => p_attribute7
2126 ,p_attribute8 => p_attribute8
2127 ,p_attribute9 => p_attribute9
2128 ,p_attribute10 => p_attribute10
2129 ,p_attribute11 => p_attribute11
2130 ,p_attribute12 => p_attribute12
2131 ,p_attribute13 => p_attribute13
2132 ,p_attribute14 => p_attribute14
2133 ,p_attribute15 => p_attribute15
2134 ,p_attribute16 => p_attribute16
2135 ,p_attribute17 => p_attribute17
2136 ,p_attribute18 => p_attribute18
2137 ,p_attribute19 => p_attribute19
2138 ,p_attribute20 => p_attribute20
2139 ,p_period_of_incapacity_id => p_period_of_incapacity_id
2140 ,p_ssp1_issued => p_ssp1_issued
2141 ,p_maternity_id => p_maternity_id
2142 ,p_sickness_start_date => p_sickness_start_date
2143 ,p_sickness_end_date => p_sickness_end_date
2144 ,p_pregnancy_related_illness => p_pregnancy_related_illness
2145 ,p_reason_for_notification_dela => p_reason_for_notification_dela
2146 ,p_accept_late_notification_fla => p_accept_late_notification_fla
2147 ,p_linked_absence_id => p_linked_absence_id
2148 ,p_absence_case_id => p_absence_case_id
2149 ,p_batch_id => p_batch_id
2150 ,p_abs_information_category => p_abs_information_category
2151 ,p_abs_information1 => p_abs_information1
2152 ,p_abs_information2 => p_abs_information2
2153 ,p_abs_information3 => p_abs_information3
2154 ,p_abs_information4 => p_abs_information4
2155 ,p_abs_information5 => p_abs_information5
2156 ,p_abs_information6 => p_abs_information6
2157 ,p_abs_information7 => p_abs_information7
2158 ,p_abs_information8 => p_abs_information8
2159 ,p_abs_information9 => p_abs_information9
2160 ,p_abs_information10 => p_abs_information10
2161 ,p_abs_information11 => p_abs_information11
2162 ,p_abs_information12 => p_abs_information12
2163 ,p_abs_information13 => p_abs_information13
2164 ,p_abs_information14 => p_abs_information14
2165 ,p_abs_information15 => p_abs_information15
2166 ,p_abs_information16 => p_abs_information16
2167 ,p_abs_information17 => p_abs_information17
2168 ,p_abs_information18 => p_abs_information18
2169 ,p_abs_information19 => p_abs_information19
2170 ,p_abs_information20 => p_abs_information20
2171 ,p_abs_information21 => p_abs_information21
2172 ,p_abs_information22 => p_abs_information22
2173 ,p_abs_information23 => p_abs_information23
2174 ,p_abs_information24 => p_abs_information24
2175 ,p_abs_information25 => p_abs_information25
2176 ,p_abs_information26 => p_abs_information26
2177 ,p_abs_information27 => p_abs_information27
2178 ,p_abs_information28 => p_abs_information28
2179 ,p_abs_information29 => p_abs_information29
2180 ,p_abs_information30 => p_abs_information30
2181 ,p_dur_dys_less_warning => l_dur_dys_less_warning
2182 ,p_dur_hrs_less_warning => l_dur_hrs_less_warning
2183 ,p_exceeds_pto_entit_warning => l_exceeds_pto_entit_warning
2184 ,p_exceeds_run_total_warning => l_exceeds_run_total_warning
2185 ,p_abs_overlap_warning => l_abs_overlap_warning
2186 ,p_abs_day_after_warning => l_abs_day_after_warning
2187 ,p_dur_overwritten_warning => l_dur_overwritten_warning
2188 ,p_del_element_entry_warning => l_del_element_entry_warning
2189 );
2190 exception
2191 when hr_api.cannot_find_prog_unit then
2192 hr_api.cannot_find_prog_unit_error
2193 (p_module_name => 'UPDATE_PERSON_ABSENCE'
2194 ,p_hook_type => 'AP'
2195 );
2196 end;
2197
2198 --
2199 -- Pipe the main IN OUT / OUT parameters for ease of debugging.
2200 --
2201 hr_utility.trace(' ');
2202 hr_utility.trace(' --------------------------------'||
2203 '---------------------------------');
2204 hr_utility.trace(' IN OUT NOCOPY / OUT NOCOPY PARAMETER '||
2205 ' VALUE');
2206 hr_utility.trace(' --------------------------------'||
2207 '+--------------------------------');
2208 hr_utility.trace(' p_absence_days '||
2209 to_char(p_absence_days));
2210 hr_utility.trace(' p_absence_hours '||
2211 to_char(p_absence_hours));
2212 hr_utility.trace(' p_object_version_number '||
2213 to_char(p_object_version_number));
2214 hr_utility.trace(' --------------------------------'||
2215 '---------------------------------');
2216 hr_utility.trace(' ');
2217
2218 --
2219 -- When in validation only mode raise the Validate_Enabled exception
2220 --
2221 if p_validate then
2222 raise hr_api.validate_enabled;
2223 end if;
2224 --
2225 hr_utility.set_location(' Leaving:'||l_proc, 90);
2226 exception
2227 when hr_api.validate_enabled then
2228 --
2229 -- As the Validate_Enabled exception has been raised
2230 -- we must rollback to the savepoint
2231 --
2232 rollback to update_person_absence;
2233 --
2234 -- Only set output warning arguments
2235 -- (Any key or derived arguments must be set to null
2236 -- when validation only mode is being used.)
2237 --
2238 p_object_version_number := null;
2239 hr_utility.set_location(' Leaving:'||l_proc, 100);
2240 when others then
2241 --
2242 -- A validation or unexpected error has occured
2243 --
2244 p_object_version_number := lv_object_version_number ;
2245 p_absence_days := lv_absence_days ;
2246 p_absence_hours := lv_absence_hours ;
2247
2248 p_dur_dys_less_warning := null;
2249 p_dur_hrs_less_warning := null;
2250 p_exceeds_pto_entit_warning := null;
2251 p_exceeds_run_total_warning := null;
2252 p_abs_overlap_warning := null;
2253 p_abs_day_after_warning := null;
2254 p_dur_overwritten_warning := null;
2255 p_del_element_entry_warning := null;
2256
2257 rollback to update_person_absence;
2258 hr_utility.set_location(' Leaving:'||l_proc, 110);
2259 raise;
2260 end update_person_absence;
2261 --
2262 --
2263 -- ----------------------------------------------------------------------------
2264 -- |----------------------< delete_person_absence >---------------------------|
2265 -- ----------------------------------------------------------------------------
2266 --
2267 procedure delete_person_absence
2268 (p_validate in boolean default false
2269 ,p_absence_attendance_id in number
2270 ,p_object_version_number in number
2271 ,p_called_from in number default 800
2272 ) is
2273
2274 CURSOR get_person_info IS
2275 select person_id
2276 from per_absence_attendances
2277 where ABSENCE_ATTENDANCE_ID = p_absence_attendance_id ;
2278 --
2279 -- Declare cursors and local variables
2280 --
2281 --
2282 l_old ben_abs_ler.g_abs_ler_rec;
2283 l_new ben_abs_ler.g_abs_ler_rec;
2284
2285 l_proc varchar2(72) := g_package||'delete_person_absence';
2286 l_exists number;
2287 l_element_entry_id number;
2288 l_effective_start_date date;
2289 l_effective_end_date date;
2290
2291 -- l_person_id number := -1;
2292 l_csrperson_id number;
2293
2294
2295 /*Fix for Bug # 13922178. Adjust the occurrence after the delete*/
2296 cursor c_occurrence is
2297 select business_group_id,
2298 absence_attendance_type_id,person_id,occurrence
2299 from per_absence_attendances
2300 where absence_attendance_id = p_absence_attendance_id;
2301
2302 l_absence_attendance_type_id per_absence_attendances.absence_attendance_type_id%TYPE;
2303 l_person_id per_absence_attendances.person_id%TYPE;
2304 l_business_group_id per_absence_attendances.business_group_id%TYPE;
2305 l_occurrence per_absence_attendances.occurrence%TYPE;
2306
2307 --
2308
2309 cursor csr_get_absdates is
2310 select nvl(date_start,DATE_PROJECTED_START) , nvl(date_end,DATE_PROJECTED_END)
2311
2312 from per_absence_attendances
2313 where ABSENCE_ATTENDANCE_ID = p_absence_attendance_id ;
2314 --
2315 l_chk_datestart date;
2316 l_chk_dateend date;
2317 l_PROGRAM_APPLICATION_ID number;
2318 l_retvalue varchar2(10);
2319 --
2320
2321 begin
2322 hr_utility.set_location('Entering:'|| l_proc, 10);
2323
2324 -- Issue a savepoint
2325 savepoint delete_person_absence;
2326
2327 OPEN get_person_info;
2328 FETCH get_person_info INTO l_person_id;
2329 CLOSE get_person_info;
2330
2331 if nvl(FND_PROFILE.Value('HR_ABS_OTL_INTEGRATION'),'N') = 'Y' then
2332 hr_utility.set_location(' OTL ABS integration on ',10);
2333
2334 open csr_get_absdates;
2335 fetch csr_get_absdates into l_chk_datestart,l_chk_dateend ;
2336 close csr_get_absdates;
2337
2338 if p_called_from <> 809 and l_chk_datestart is not null and l_chk_dateend is not null
2339
2340 then
2341
2342 hr_utility.set_location('inside otl hr check ', 10);
2343
2344 otl_hr_check
2345 (
2346 p_person_id => l_person_id,
2347 p_date_start => l_chk_datestart,
2348 p_date_end => l_chk_dateend,
2349 p_scope => 'DELETE',
2350 p_ret_value => l_retvalue );
2351
2352
2353 hr_utility.set_location('after otl hr check ', 10);
2354
2355 end if;
2356 END IF;
2357
2358 --
2359 --
2360 -- Call Before Process User Hook
2361 --
2362 begin
2363 hr_person_absence_bk3.delete_person_absence_b
2364 (p_absence_attendance_id => p_absence_attendance_id
2365 ,p_object_version_number => p_object_version_number
2366 );
2367 exception
2368 when hr_api.cannot_find_prog_unit then
2369 hr_api.cannot_find_prog_unit_error
2370 (p_module_name => 'DELETE_PERSON_ABSENCE'
2371 ,p_hook_type => 'BP'
2372 );
2373 end;
2374
2375 /*Fix for Bug # 13922178. Adjust the occurrence after the delete*/
2376 open c_occurrence;
2377 fetch c_occurrence into l_business_group_id,l_absence_attendance_type_id,l_person_id,l_occurrence;
2378 close c_occurrence;
2379 --
2380 hr_utility.set_location('Start of absence element deletion section', 30);
2381 --
2382 /* Start of Absence Element Deletion Section */
2383
2384 --
2385 -- Delete the absence element entry. First we check if an
2386 -- element has been created for this absence.
2387 -- Added Loop for bug fix 5392984
2388 Loop
2389 get_absence_element
2390 (p_absence_attendance_id => p_absence_attendance_id
2391 ,p_element_entry_id => l_element_entry_id
2392 ,p_effective_start_date => l_effective_start_date
2393 ,p_effective_end_date => l_effective_end_date);
2394
2395 if l_element_entry_id is not null then
2396 --
2397 -- An element entry exists so we delete it.
2398 --
2399
2400 delete_absence_element
2401 (p_dt_delete_mode => 'ZAP'
2402 ,p_session_date => l_effective_start_date
2403 ,p_element_entry_id => l_element_entry_id);
2404 else
2405 exit;
2406 end if;
2407 End loop;
2408 /* End of Absence Element Deletion Section */
2409
2410 hr_utility.set_location('End of absence element deletion section', 40);
2411 --
2412 -- Delete Person Absence
2413
2414 per_abs_del.del
2415 (p_absence_attendance_id => p_absence_attendance_id
2416 ,p_object_version_number => p_object_version_number
2417 );
2418
2419 hr_utility.set_location(l_proc, 50);
2420
2421 populate_ben_absence_rec
2422 (p_absence_attendance_id => p_absence_attendance_id,
2423 p_rec_type => 'O',
2424 p_ben_rec => l_old);
2425 -- fix for bug 4395727.
2426 ben_abs_ler.ler_chk(p_old => l_old,
2427 p_new => l_new,
2428 p_effective_date => l_effective_start_date);
2429
2430
2431 /*Fix for Bug # 13922178. Adjust the occurrence after the delete*/
2432 hr_utility.set_location(l_proc, 52);
2433 update per_absence_attendances set occurrence = occurrence - 1
2434 where business_group_id = l_business_group_id
2435 and absence_attendance_type_id = l_absence_attendance_type_id
2436 and person_id = l_person_id
2437 and occurrence > l_occurrence;
2438 hr_utility.set_location(l_proc, 53);
2439
2440 --
2441 -- Call After Process User Hook
2442 --
2443
2444 begin
2445 hr_person_absence_bk3.delete_person_absence_a
2446 (p_absence_attendance_id => p_absence_attendance_id
2447 ,p_object_version_number => p_object_version_number
2448 ,p_person_id => l_person_id
2449 );
2450 exception
2451 when hr_api.cannot_find_prog_unit then
2452 hr_api.cannot_find_prog_unit_error
2453 (p_module_name => 'DELETE_PERSON_ABSENCE'
2454 ,p_hook_type => 'AP'
2455 );
2456 end;
2457 --
2458 -- to delete all the pending for approval transactions data requested for OTL-HRAbsence.
2459 if nvl(FND_PROFILE.Value('HR_ABS_OTL_INTEGRATION'),'N') = 'Y' then
2460
2461 hr_utility.set_location('OTL HR ABS integration is ON ', 10);
2462 hr_absutil_ss.remove_absence_transaction(p_absence_attendance_id);
2463
2464 end if;
2465
2466 -- When in validation only mode raise the Validate_Enabled exception
2467 --
2468 if p_validate then
2469 raise hr_api.validate_enabled;
2470 end if;
2471
2472 --
2473 hr_utility.set_location(' Leaving:'||l_proc, 70);
2474 exception
2475 when hr_api.validate_enabled then
2476 --
2477 -- As the Validate_Enabled exception has been raised
2478 -- we must rollback to the savepoint
2479 --
2480 rollback to delete_person_absence;
2481 --
2482 -- Only set output warning arguments
2483 -- (Any key or derived arguments must be set to null
2484 -- when validation only mode is being used.)
2485 --
2486 hr_utility.set_location(' Leaving:'||l_proc, 80);
2487 when others then
2488 --
2489 -- A validation or unexpected error has occured
2490 --
2491 rollback to delete_person_absence;
2492 hr_utility.set_location(' Leaving:'||l_proc, 90);
2493 raise;
2494 --
2495 end delete_person_absence;
2496
2497 --
2498 -- ----------------------------------------------------------------------------
2499 -- |----------------------< get_primary_assignment >--------------------------|
2500 -- ----------------------------------------------------------------------------
2501 --
2502 function get_primary_assignment
2503 (p_person_id in number,
2504 p_effective_date in date) return number is
2505
2506
2507 -- It is acceptable for one person to have several simultaneous assignments
2508 -- with the benefits functionality so we exclude assignment types of 'B'
2509 -- to prevent the wrong assignment being picked up.
2510
2511 cursor c_get_primary_assignment is
2512 select asg.assignment_id
2513 from per_all_assignments_f asg
2514 where asg.person_id = p_person_id
2515 and p_effective_date between
2516 asg.effective_start_date and asg.effective_end_date
2517 and asg.primary_flag = 'Y'
2518 and asg.assignment_type <> 'B';
2519
2520
2521 l_proc varchar2(72) := g_package||'get_primary_assignment';
2522 l_assignment_id number;
2523
2524
2525 begin
2526
2527 hr_utility.set_location('Entering:'|| l_proc, 10);
2528
2529 open c_get_primary_assignment;
2530 fetch c_get_primary_assignment into l_assignment_id;
2531 close c_get_primary_assignment;
2532
2533
2534 hr_utility.set_location('Leaving:'|| l_proc, 20);
2535
2536 return l_assignment_id;
2537
2538 end get_primary_assignment;
2539
2540 --
2541 -- ----------------------------------------------------------------------------
2542 -- |----------------------< linked_to_element >-------------------------------|
2543 -- ----------------------------------------------------------------------------
2544 --
2545 function linked_to_element
2546 (p_absence_attendance_id in number) return boolean is
2547
2548 cursor c_linked_to_element is
2549 select abt.input_value_id
2550 from per_absence_attendances aba,
2551 per_absence_attendance_types abt
2552 where aba.absence_attendance_id = p_absence_attendance_id
2553 and aba.absence_attendance_type_id = abt.absence_attendance_type_id;
2554
2555
2556 l_proc varchar2(72) := g_package||'linked_to_element';
2557 l_input_value_id number;
2558 l_linked_to_element boolean;
2559
2560
2561 begin
2562
2563 hr_utility.set_location('Entering:'|| l_proc, 10);
2564
2565 open c_linked_to_element;
2566 fetch c_linked_to_element into l_input_value_id;
2567 close c_linked_to_element;
2568
2569
2570 if l_input_value_id is not null then
2571 l_linked_to_element := TRUE;
2572 else
2573 l_linked_to_element := FALSE;
2574 end if;
2575
2576
2577 hr_utility.set_location('Leaving:'|| l_proc, 20);
2578
2579 return l_linked_to_element;
2580
2581 end linked_to_element;
2582
2583 --
2584 -- ----------------------------------------------------------------------------
2585 -- |----------------------< get_absence_element >-----------------------------|
2586 -- ----------------------------------------------------------------------------
2587 --
2588 procedure get_absence_element
2589 (p_absence_attendance_id in number
2590 ,p_element_entry_id out nocopy number
2591 ,p_effective_start_date out nocopy date
2592 ,p_effective_end_date out nocopy date) is
2593
2594 --
2595 -- Bug 2782577. Performance tuned for the CBO.
2596 --
2597 cursor c_get_absence_element is
2598 select distinct pee.element_entry_id
2599 ,pee.effective_start_date
2600 ,pee.effective_end_date
2601 from per_absence_attendances abs
2602 ,per_all_assignments_f asg
2603 ,per_absence_attendance_types abt
2604 ,pay_input_values_f piv
2605 ,pay_element_types_f pet
2606 ,pay_element_links_f pel
2607 ,pay_element_entries_f pee
2608 where abs.absence_attendance_id = p_absence_attendance_id
2609 and abs.person_id = asg.person_id
2610 and abs.absence_attendance_type_id = abt.absence_attendance_type_id
2611 and abt.input_value_id is not null
2612 and abt.input_value_id = piv.input_value_id
2613 and piv.element_type_id = pet.element_type_id
2614 and pet.element_type_id = pel.element_type_id
2615 and pel.element_link_id = pee.element_link_id
2616 and pee.assignment_id = asg.assignment_id
2617 and pee.creator_id = abs.absence_attendance_id
2618 and pee.creator_type = 'A';
2619
2620 l_proc varchar2(72) := g_package||'get_absence_element';
2621
2622 begin
2623
2624 hr_utility.set_location('Entering:'|| l_proc, 10);
2625
2626 open c_get_absence_element;
2627 fetch c_get_absence_element into p_element_entry_id,
2628 p_effective_start_date,
2629 p_effective_end_date;
2630 close c_get_absence_element;
2631
2632
2633 hr_utility.set_location('Leaving:'|| l_proc, 20);
2634 exception
2635 when others then
2636 p_element_entry_id := null;
2637 p_effective_start_date := null;
2638 p_effective_end_date := null;
2639
2640 end get_absence_element;
2641
2642 -- ----------------------------------------------------------------------------
2643 -- |----------------------< get_processing_type >-----------------------------|
2644 -- ----------------------------------------------------------------------------
2645 --
2646 function get_processing_type
2647 (p_absence_attendance_type_id in number) return varchar2 is
2648
2649 cursor c_get_processing_type is
2650 select upper(pet.processing_type)
2651 from per_absence_attendance_types abt,
2652 pay_input_values_f piv,
2653 pay_element_types_f pet
2654 where abt.absence_attendance_type_id = p_absence_attendance_type_id
2655 and abt.input_value_id = piv.input_value_id
2656 and piv.element_type_id = pet.element_type_id;
2657
2658
2659 l_proc varchar2(72) := g_package||'get_processing_type';
2660 l_processing_type pay_element_types_f.processing_type%TYPE;
2661
2662
2663 begin
2664
2665 hr_utility.set_location('Entering:'|| l_proc, 10);
2666
2667 --
2668 -- Fetch the processing type. If no records are found, the absence type
2669 -- does not have an associated element so null is returned.
2670 --
2671 open c_get_processing_type;
2672 fetch c_get_processing_type into l_processing_type;
2673 close c_get_processing_type;
2674
2675 hr_utility.set_location('Leaving:'|| l_proc, 20);
2676
2677 return l_processing_type;
2678
2679 end get_processing_type;
2680
2681 --
2682 -- ----------------------------------------------------------------------------
2683 -- |----------------------< get_element_details >-----------------------------|
2684 -- ----------------------------------------------------------------------------
2685 --
2686 procedure get_element_details
2687 (p_absence_attendance_id in number
2688 ,p_element_type_id out nocopy number
2689 ,p_input_value_id out nocopy number
2690 ,p_entry_value out nocopy number
2691 ) is
2692
2693
2694 cursor c_get_element_details is
2695 select aba.absence_hours,
2696 aba.absence_days,
2697 abt.hours_or_days,
2698 abt.increasing_or_decreasing_flag,
2699 abt.input_value_id,
2700 pet.element_type_id,
2701 pet.processing_type
2702 from per_absence_attendances aba,
2703 per_absence_attendance_types abt,
2704 pay_input_values_f piv,
2705 pay_element_types_f pet
2706 where aba.absence_attendance_id = p_absence_attendance_id
2707 and aba.absence_attendance_type_id = abt.absence_attendance_type_id
2708 and abt.input_value_id = piv.input_value_id
2709 and piv.element_type_id = pet.element_type_id;
2710
2711
2712 l_proc varchar2(72) := g_package||'get_element_details';
2713 l_absence_hours per_absence_attendances.absence_hours%TYPE;
2714 l_absence_days per_absence_attendances.absence_days%TYPE;
2715 l_hours_or_days per_absence_attendance_types.hours_or_days%TYPE;
2716 l_processing_type pay_element_types_f.processing_type%TYPE;
2717 l_inc_or_dec_flag per_absence_attendance_types.increasing_or_decreasing_flag%TYPE;
2718
2719
2720 begin
2721
2722 hr_utility.set_location('Entering:'|| l_proc, 10);
2723
2724
2725 -- This should always return a row because this procedure is only
2726 -- called when the absence type has an associated element type.
2727
2728 open c_get_element_details;
2729 fetch c_get_element_details into l_absence_hours,
2730 l_absence_days,
2731 l_hours_or_days,
2732 l_inc_or_dec_flag,
2733 p_input_value_id,
2734 p_element_type_id,
2735 l_processing_type;
2736 close c_get_element_details;
2737
2738
2739 hr_utility.set_location('Setting entry value', 20);
2740
2741 if upper(l_processing_type) = 'N' then
2742 --
2743 -- p_entry_value is only set when the element type is
2744 -- non-recurring, otherwise it remains null.
2745 --
2746 if upper(l_hours_or_days) = 'H' then
2747
2748 if l_inc_or_dec_flag = 'D' then
2749 --
2750 -- Invert the absence duration for decreasing balances.
2751 --
2752 hr_utility.set_location(l_proc, 30);
2753 p_entry_value := l_absence_hours * -1;
2754
2755 else
2756
2757 hr_utility.set_location(l_proc, 40);
2758 p_entry_value := l_absence_hours;
2759
2760 end if;
2761
2762 else
2763
2764 if l_inc_or_dec_flag = 'D' then
2765 --
2766 -- Invert the absence duration for decreasing balances.
2767 --
2768 hr_utility.set_location(l_proc, 50);
2769 p_entry_value := l_absence_days * -1;
2770
2771 else
2772
2773 hr_utility.set_location(l_proc, 60);
2774 p_entry_value := l_absence_days;
2775
2776 end if;
2777
2778 end if;
2779
2780 end if;
2781
2782 hr_utility.set_location('Leaving:'|| l_proc, 70);
2783
2784 exception
2785 when others then
2786 p_element_type_id := null ;
2787 p_input_value_id := null ;
2788 p_entry_value := null ;
2789
2790 raise;
2791
2792 --
2793 end get_element_details;
2794 --
2795 -- ----------------------------------------------------------------------------
2796 -- |----------------------< insert_absence_element >--------------------------|
2797 -- ----------------------------------------------------------------------------
2798 --
2799 procedure insert_absence_element
2800 (p_date_start in date
2801 ,p_assignment_id in number
2802 ,p_absence_attendance_id in number
2803 ,p_element_entry_id out nocopy number
2804 ) is
2805
2806
2807 l_proc varchar2(72) := g_package||'insert_absence_element';
2808 l_date_start date := p_date_start;
2809 l_date_end date;
2810 l_element_type_id number;
2811 l_element_link_id number;
2812 l_input_value_id number;
2813 l_entry_value number;
2814
2815 begin
2816
2817
2818 hr_utility.set_location('Entering:'|| l_proc, 10);
2819
2820 get_element_details
2821 (p_absence_attendance_id => p_absence_attendance_id
2822 ,p_element_type_id => l_element_type_id
2823 ,p_input_value_id => l_input_value_id
2824 ,p_entry_value => l_entry_value);
2825
2826
2827 hr_utility.set_location('Checking element link', 20);
2828
2829 l_element_link_id := hr_entry_api.get_link
2830 (p_assignment_id => p_assignment_id
2831 ,p_element_type_id => l_element_type_id
2832 ,p_session_date => p_date_start);
2833
2834 If l_element_link_id is null then
2835 -- Assignment is not eligible for the element type
2836 -- associated with this absence.
2837 fnd_message.set_name ('PAY','HR_7448_ELE_PER_NOT_ELIGIBLE');
2838 hr_utility.raise_error;
2839 end if;
2840
2841
2842 hr_utility.set_location('Inserting element', 30);
2843
2844 -- We know the assignment is eligible for this element because
2845 -- we have the element_link_id. The entries API will handle
2846 -- all other validation (e.g., non-recurring entries must
2847 -- have a valid payroll).
2848
2849 hr_entry_api.insert_element_entry
2850 (p_effective_start_date => l_date_start
2851 ,p_effective_end_date => l_date_end
2852 ,p_element_entry_id => p_element_entry_id
2853 ,p_assignment_id => p_assignment_id
2854 ,p_element_link_id => l_element_link_id
2855 ,p_creator_type => 'A'
2856 ,p_entry_type => 'E'
2857 ,p_creator_id => p_absence_attendance_id
2858 ,p_input_value_id1 => l_input_value_id
2859 ,p_entry_value1 => l_entry_value);
2860
2861
2862 hr_utility.set_location('EE ID: '|| to_char(p_element_entry_id), 40);
2863 hr_utility.set_location('Leaving:'|| l_proc, 50);
2864
2865 exception
2866 when others then
2867 p_element_entry_id := null ;
2868 raise;
2869
2870 end insert_absence_element;
2871 --
2872 -- ----------------------------------------------------------------------------
2873 -- |----------------------< update_absence_element >--------------------------|
2874 -- ----------------------------------------------------------------------------
2875 --
2876 procedure update_absence_element
2877 (p_dt_update_mode in varchar2
2878 ,p_session_date in date
2879 ,p_element_entry_id in number
2880 ,p_absence_attendance_id in number
2881 ) is
2882
2883
2884 l_proc varchar2(72) := g_package||'update_absence_element';
2885 l_element_type_id number;
2886 l_input_value_id number;
2887 l_entry_value number;
2888
2889 begin
2890
2891
2892 hr_utility.set_location('Entering:'|| l_proc, 10);
2893
2894 get_element_details
2895 (p_absence_attendance_id => p_absence_attendance_id
2896 ,p_element_type_id => l_element_type_id
2897 ,p_input_value_id => l_input_value_id
2898 ,p_entry_value => l_entry_value);
2899
2900
2901 hr_utility.set_location('Updating element', 20);
2902
2903 -- We know the assignment is eligible for this element because
2904 -- we have the element_link_id. The entries API will handle
2905 -- all other validation (e.g., non-recurring entries must
2906 -- have a valid payroll).
2907
2908 hr_entry_api.update_element_entry
2909 (p_dt_update_mode => p_dt_update_mode
2910 ,p_session_date => p_session_date
2911 ,p_element_entry_id => p_element_entry_id
2912 ,p_creator_type => 'A'
2913 ,p_creator_id => p_absence_attendance_id
2914 ,p_input_value_id1 => l_input_value_id
2915 ,p_entry_value1 => l_entry_value);
2916
2917
2918 hr_utility.set_location('Leaving:'|| l_proc, 30);
2919
2920 end update_absence_element;
2921 --
2922 -- ----------------------------------------------------------------------------
2923 -- |----------------------< delete_absence_element >--------------------------|
2924 -- ----------------------------------------------------------------------------
2925 --
2926 procedure delete_absence_element
2927 (p_dt_delete_mode in varchar2
2928 ,p_session_date in date
2929 ,p_element_entry_id in number
2930 ) is
2931
2932
2933 l_proc varchar2(72) := g_package||'delete_absence_element';
2934 l_input_value_id number;
2935 l_entry_value number;
2936
2937 begin
2938
2939
2940 hr_utility.set_location('Entering:'|| l_proc, 10);
2941
2942 hr_entry_api.delete_element_entry
2943 (p_dt_delete_mode => p_dt_delete_mode
2944 ,p_session_date => p_session_date
2945 ,p_element_entry_id => p_element_entry_id);
2946
2947
2948 hr_utility.set_location('Leaving:'|| l_proc, 20);
2949
2950 end delete_absence_element;
2951 --
2952 --
2953 -- ----------------------------------------------------------------------------
2954 -- |----------------------< otl_hr_check >--------------------------|
2955 -- ----------------------------------------------------------------------------
2956 --
2957 procedure otl_hr_check
2958 (
2959 p_person_id number default null,
2960 p_date_start date default null,
2961 p_date_end date default null,
2962 p_scope varchar2 default null,
2963 p_ret_value out nocopy varchar2
2964 )
2965 is
2966 l_error_level NUMBER;
2967 l_error_code VARCHAR2(50);
2968 l_profile varchar2(1);
2969 g_debug boolean := hr_utility.debug_enabled;
2970
2971 begin
2972 p_ret_value :='NO';
2973 l_profile :='N';
2974
2975
2976 hr_utility.set_location('p_person_id : '|| p_person_id, 20);
2977 hr_utility.set_location('p_date_start :'|| p_date_start, 20);
2978 hr_utility.set_location('p_date_end :'|| p_date_end, 20);
2979 hr_utility.set_location('p_scope :'|| p_scope, 20);
2980
2981 if (NVL(FND_PROFILE.Value('HR_SCH_BASED_ABS_CALC'),'N')='Y' AND
2982 NVL(FND_PROFILE.Value('PER_ABSENCE_DURATION_AUTO_OVERWRITE'),'N')='Y')
2983
2984 then
2985
2986 l_profile :='Y';
2987
2988 if g_debug then
2989 hr_utility.set_location('CAC installed', 10);
2990 end if;
2991
2992 end if;
2993
2994
2995
2996 if p_person_id is not null and p_date_start is not null and p_date_end is not null then
2997 hr_utility.set_location('passed parameter are not null :', 10);
2998
2999 --if l_profile = 'Y' and nvl(FND_PROFILE.Value('HR_ABS_OTL_INTEGRATION'),'N') = 'Y' then
3000 if nvl(FND_PROFILE.Value('HR_ABS_OTL_INTEGRATION'),'N') = 'Y' then
3001
3002 if g_debug then
3003 hr_utility.set_location('OTL HR ABS integration is ON ', 10);
3004 end if;
3005 -- bug 8916489
3006 if not per_abs_bus.per_valid_for_absence
3007 (p_person_id => p_person_id
3008 ,p_business_group_id => hr_general.get_business_group_id
3009 ,p_date_projected_start => null
3010 ,p_date_projected_end => null
3011 ,p_date_start => p_date_start
3012 ,p_date_end => p_date_end)
3013 then
3014
3015 fnd_message.set_name('PER', 'PER_7715_ABS_TERM_PROJ_DATE');
3016 fnd_message.raise_error;
3017
3018 end if;
3019
3020 HXC_ABS_INTG_PKG.otl_timecard_chk(p_person_id => p_person_id,
3021 p_start_time => p_date_start,
3022 p_stop_time => p_date_end,
3023 p_error_code => l_error_code,
3024 p_error_level => l_error_level);
3025
3026 if g_debug then
3027 hr_utility.set_location('out of OTL call ', 10);
3028 hr_utility.set_location(' l_error_level ' ||l_error_level, 10);
3029 end if;
3030
3031 if p_scope IN ('CREATE','DELETE') THEN
3032 IF l_error_level = 0 THEN
3033 p_ret_value :='ALL';
3034
3035 if g_debug then
3036 hr_utility.set_location('otl hr Insert check - CREATE', 10);
3037 end if;
3038
3039 elsif l_error_level in (1,2) then
3040 -- raise
3041 hr_utility.set_location('OTL Check failed. Raise Error', 20);
3042 hr_utility.set_message(800,'HR_50433_OTL_CARD_EXISTS');
3043 hr_utility.raise_error;
3044
3045 else
3046 -- raise
3047 hr_utility.set_location('Unknown Exception raised from OTL. Raise Error', 30);
3048 hr_utility.set_message(800,'HR_50433_OTL_CARD_EXISTS');
3049 hr_utility.raise_error;
3050 end if;
3051
3052 end if;
3053
3054 if p_scope ='UPDATE' THEN
3055
3056 if g_debug then
3057 hr_utility.set_location('otl hr Insert check - UPDATE', 10);
3058 end if;
3059
3060 IF l_error_level =0 THEN
3061 p_ret_value :='ALL';
3062 hr_utility.set_location('otl hr UPDATE check', 40);
3063 elsif l_error_level =1 then
3064 p_ret_value:='RESTRICT';
3065 hr_utility.set_location('otl hr UPDATE check', 50);
3066 elsif l_error_level =2 then
3067 -- raise
3068 hr_utility.set_location('otl hr UPDATE check ', 60);
3069 hr_utility.set_message(800,'HR_50433_OTL_CARD_EXISTS');
3070 hr_utility.raise_error;
3071 else
3072 --raise;
3073 hr_utility.set_location('otl hr UPDATE check', 70);
3074 hr_utility.set_message(800,'HR_50433_OTL_CARD_EXISTS');
3075 hr_utility.raise_error;
3076
3077 end if;
3078 end if;
3079
3080 /* Code added for Query and then Disable of some fields in PUI screen */
3081 IF p_scope = 'QUERY' THEN
3082 IF l_error_level = 0 THEN
3083 p_ret_value :='ALL';
3084 hr_utility.set_location('Query for the period done. No disable', 40);
3085 elsif l_error_level =1 then
3086 p_ret_value:='RESTRICT';
3087 hr_utility.set_location('Query for the period done. Disable the fields', 50);
3088 end if;
3089 END IF ;
3090
3091 else
3092 p_ret_value :='ALL';
3093 end if;
3094
3095 else
3096 hr_utility.set_location('CAC check call ', 100);
3097 p_ret_value :='YES'; -- Profile for cac is set
3098 end if;
3099
3100
3101 end;
3102 --
3103 --
3104 -- ----------------------------------------------------------------------------
3105 -- |----------------------< get_absence_data >--------------------------|
3106 -- ----------------------------------------------------------------------------
3107 --
3108 procedure get_absence_data(p_person_id in number,
3109 p_start_date in date,
3110 p_end_date in date,
3111 absence_records out nocopy abs_data,
3112 absence_records_inv out nocopy abs_data_inv ) is
3113
3114 type absence_entries is record ( startdate date ,
3115 enddate date,
3116 TRANSACTION_ID number,
3117 TRANSACTION_DOCUMENT clob,
3118 absence_attendance_type_id number,
3119 ELEMENT_TYPE_ID number(10),
3120 hours_or_days varchar2(1));
3121
3122 type create_data is table of absence_entries INDEX BY binary_integer;
3123
3124 ss_create_data create_data;
3125
3126 l_confirmed_flag varchar2(2);
3127 ssmodes number:=1; -- 8941541
3128 p_ssmode varchar2(20); -- 8941541
3129 l_start_date date;
3130 l_end_date date;
3131 l_start_date_new date;
3132 l_end_date_new date;
3133 l_time_start varchar2(10);
3134 l_time_end varchar2(10);
3135 l_time_start_new varchar2(10);
3136 l_time_end_new varchar2(10);
3137 l_days number;
3138 l_hours number(6,2);
3139 l_abs_type number(10);
3140 l_ele_type number(10);
3141 l_assignment_id number;
3142 l_sch_based_dur VARCHAR2(1);
3143 l_hours_or_days varchar2(1);
3144 l_absence_attendance_id number(10);
3145
3146 -- for core data-
3147
3148 l_core_dstart date;
3149 l_core_dend date;
3150 l_core_tstart varchar2(10);
3151 l_core_tend varchar2(10);
3152 l_core_abs_attendance_id number(10);
3153 l_core_ovn number;
3154 --
3155 l_idx NUMBER;
3156 i number :=0;
3157 j number :=0;
3158 l_ref_date DATE;
3159 l_first_band BOOLEAN;
3160 l_day_start_time VARCHAR2(30);
3161 l_day_end_time VARCHAR2(30);
3162
3163 l_schedule cac_avlblty_time_varray;
3164 l_schedule_source VARCHAR2(10);
3165 l_return_status VARCHAR2(1);
3166 l_return_message VARCHAR2(2000);
3167 l_PROGRAM_APPLICATION_ID number(15);
3168
3169
3170 e_bad_time_format EXCEPTION;
3171
3172
3173 v_start_date date;
3174 v_end_date date;
3175
3176
3177 cursor csr_sshr_abs_records(p_mode varchar2) is
3178
3179 select
3180 hr_person_absence_swi.getStartDate(hat.transaction_id) ActualDateStart,
3181 hr_person_absence_swi.getEndDate(hat.transaction_id) ActualDateEnd,
3182 nvl(hr_xml_util.get_node_value(hat.transaction_id,'TimeStart','Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow',
3183 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
3184 hr_xml_util.get_node_value(hat.transaction_id,'TimeProjectedStart','Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow',
3185 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)),
3186 nvl(hr_xml_util.get_node_value(hat.transaction_id,'TimeEnd','Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow',
3187 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
3188 hr_xml_util.get_node_value(hat.transaction_id,'TimeProjectedEnd','Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow',
3189 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)),
3190 hat.TRANSACTION_ID,
3191 hat.TRANSACTION_DOCUMENT,
3192 -- hrtsteps.object_state, -- stores ovn
3193 hr_xml_util.get_node_value(hat.transaction_id,'ObjectVersionNumber','Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow',
3194 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
3195 abt.absence_attendance_type_id,
3196 hat.transaction_ref_id,
3197 pet.ELEMENT_TYPE_ID,
3198 abt.hours_or_days,
3199 decode (hrtsteps.INFORMATION9,'CONFIRMED','Y','N') CONFIRMED_FLAG
3200 from hr_api_transactions hat,
3201 HR_API_TRANSACTION_STEPS hrtsteps,
3202 per_absence_attendance_types abt,
3203 pay_input_values_f piv,
3204 pay_element_types_f pet
3205 where
3206 hat.SELECTED_PERSON_ID = p_person_id
3207 --hat.creator_person_id = 125
3208 and hat.TRANSACTION_REF_TABLE = 'PER_ABSENCE_ATTENDANCES'
3209 and hrtsteps.TRANSACTION_ID= hat.TRANSACTION_ID
3210 and hat.STATUS not in ('W','S','N','D','AC') -- bug9554066
3211 AND hr_person_absence_swi.getstartdate(hat.transaction_id) is NOT NULL
3212 AND hr_person_absence_swi.getenddate(hat.transaction_id) is NOT NULL
3213 and (p_start_date <= hr_person_absence_swi.getEndDate(hat.transaction_id) and
3214 p_end_date >= hr_person_absence_swi.getStartDate(hat.transaction_id) )
3215
3216 and hr_xml_util.get_node_value(hat.transaction_id,'AbsenceAction','Transaction/TransCtx/CNode',
3217 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)=p_mode
3218 and p_start_date between piv.EFFECTIVE_START_DATE and piv.EFFECTIVE_END_DATE
3219 and p_start_date between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE
3220 AND abt.absence_attendance_type_id= hrtsteps.Information5
3221 and abt.input_value_id = piv.input_value_id (+)
3222 and piv.element_type_id = pet.element_type_id(+) ;
3223
3224
3225 cursor csr_core_absences is
3226 select nvl(aba.date_start,aba.DATE_PROJECTED_START) datestart,
3227 nvl(aba.date_end,aba.DATE_PROJECTED_end) dateend,
3228 nvl(aba.TIME_START,aba.TIME_PROJECTED_START) timestart,
3229 nvl(aba.TIME_END,aba.TIME_PROJECTED_END) timeend,
3230 aba.ABSENCE_ATTENDANCE_TYPE_ID,
3231 pet.ELEMENT_TYPE_ID,
3232 aba.absence_attendance_id,
3233 abt.hours_or_days,
3234 aba.program_application_id,
3235 nvl(aba.ABSENCE_DAYS,aba.ABSENCE_HOURS) DURATION,
3236 decode (aba.date_start,'','N','Y') CONFIRMED_FLAG
3237
3238 from per_absence_attendances aba,
3239 per_absence_attendance_types abt,
3240 pay_input_values_f piv,
3241 pay_element_types_f pet
3242 where aba.person_id = p_person_id
3243 AND abt.absence_attendance_type_id= aba.absence_attendance_type_id
3244 and abt.input_value_id = piv.input_value_id (+)
3245 and piv.element_type_id = pet.element_type_id(+)
3246 and (p_start_date <= nvl(aba.date_end,aba.DATE_PROJECTED_end)
3247 and p_end_date >= nvl(aba.date_start,aba.DATE_PROJECTED_START))
3248
3249 and aba.absence_attendance_id not in
3250 ( select hat.transaction_ref_id
3251 from hr_api_transactions hat,
3252 HR_API_TRANSACTION_STEPS hrtsteps
3253 where
3254 hat.SELECTED_PERSON_ID = p_person_id
3255 --hat.creator_person_id = 125
3256 and hat.TRANSACTION_REF_TABLE = 'PER_ABSENCE_ATTENDANCES'
3257 and hrtsteps.TRANSACTION_ID= hat.TRANSACTION_ID
3258 and hat.STATUS not in ('W','S','N','D','AC') -- BUG 9554066
3259 AND hr_person_absence_swi.getstartdate(hat.transaction_id) is NOT NULL
3260 AND hr_person_absence_swi.getenddate(hat.transaction_id) is NOT NULL
3261 and (p_start_date <= hr_person_absence_swi.getEndDate(hat.transaction_id) and
3262 p_end_date >= hr_person_absence_swi.getStartDate(hat.transaction_id) ));
3263
3264
3265 l_commitElement xmldom.DOMElement;
3266 l_parser xmlparser.Parser;
3267 l_CommitNode xmldom.DOMNode;
3268 l_transactionid number(15,0);
3269 l_transactiondocument clob;
3270 rootNode xmldom.DOMNode;
3271 l_Attach_Node xmldom.DOMNode;
3272 l_Attach_NodeList1 xmldom.DOMNodeList;
3273 l_TransCtx_Node xmldom.DOMNode;
3274 l_TransCtx_NodeList xmldom.DOMNodeList;
3275 l_Attach_NodeList xmldom.DOMNodeList;
3276 l_ss_ovn number;
3277 l_rec_start_date date;
3278 l_rec_end_date date;
3279 l_duration number(9,4);
3280
3281 g_debug boolean := hr_utility.debug_enabled;
3282
3283
3284 begin
3285
3286 if g_debug then
3287 hr_utility.set_location('Entering .get_absence_data ',10);
3288 end if;
3289
3290 l_assignment_id := hr_person_absence_api.get_primary_assignment
3291 (p_person_id => p_person_id
3292 ,p_effective_date => p_start_date);
3293
3294 l_sch_based_dur := NVL(FND_PROFILE.Value('HR_SCH_BASED_ABS_CALC'),'N');
3295
3296 if nvl(FND_PROFILE.Value('HR_ABS_OTL_INTEGRATION'),'N') = 'Y' THEN
3297 if g_debug then
3298 hr_utility.set_location(' Profile HR OTL Integ in ON ',10);
3299 end if;
3300
3301 --- BLOCK 1 will provide the data from SSHR Transaction tables
3302 -- which are in Create Mode and in Pending for approval status
3303 ---------------------------
3304 ------ Block 1-------------
3305 ---------------------------
3306
3307 open csr_sshr_abs_records('CancelMode');
3308 fetch csr_sshr_abs_records into l_start_date,l_end_date,l_time_start ,l_time_end,l_transactionid,l_transactiondocument,
3309 l_ss_ovn,l_abs_type,l_absence_attendance_id,l_ele_type,l_hours_or_days,l_confirmed_flag;
3310 -- if any record exists in Delete mode and pending for approval then only pass this data
3311 -- so that OTL can take action accordingly
3312 if csr_sshr_abs_records%found then
3313 close csr_sshr_abs_records;
3314
3315 absence_records(i).transactionid := l_transactionid;
3316 absence_records(i).absence_type_id:=l_abs_type;
3317 absence_records(i).element_type_id:=l_ele_type;
3318 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
3319 absence_records(i).abs_startdate:=l_day_start_time;
3320 absence_records(i).abs_enddate:=l_day_end_time;
3321 absence_records(i).modetype:='DeleteMode';
3322 absence_records(i).PROGRAM_APPLICATION_ID :='800';
3323 absence_records(i).rec_start_date :=l_start_date;
3324 absence_records(i).rec_end_date :=l_end_date;
3325 absence_records(i).days_or_hours :=l_hours_or_days;
3326
3327 else
3328 close csr_sshr_abs_records;
3329
3330 IF l_sch_based_dur = 'Y' THEN
3331
3332 open csr_sshr_abs_records('CreateMode');
3333 if g_debug then
3334 hr_utility.set_location('Block 1 -Create Mode',110);
3335 end if;
3336 loop
3337 -- fetch csr_sshr_abs_records bulk collect into l_start_date,l_end_date,l_transactionid,l_transactiondocument,l_abs_type,l_ele_type,l_hours_or_days;
3338
3339 fetch csr_sshr_abs_records into l_start_date,l_end_date,l_time_start ,l_time_end,l_transactionid,l_transactiondocument,
3340 l_ss_ovn,l_abs_type,l_absence_attendance_id,l_ele_type,l_hours_or_days,l_confirmed_flag;
3341
3342 if g_debug then
3343 hr_utility.set_location('Entering .get_absence_data ',120);
3344 hr_utility.set_location(' l_start_date '||l_start_date,120);
3345 hr_utility.set_location(' l_end_date '|| l_end_date,120);
3346 hr_utility.set_location(' l_transactionid '||l_transactionid,120);
3347 hr_utility.set_location(' l_hours_or_days '||l_hours_or_days,120);
3348 hr_utility.set_location(' l_ele_type '||l_ele_type,120);
3349 hr_utility.set_location(' l_abs_type '||l_abs_type,120);
3350 end if;
3351
3352
3353 exit when csr_sshr_abs_records%notfound;
3354
3355 --l_time_start := hr_xml_util.get_node_value(l_transactionid,'TimeStart','Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow',
3356 -- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
3357 --l_time_end := hr_xml_util.get_node_value(l_transactionid,'TimeEnd','Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow',
3358 -- NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
3359
3360 -- as this is just in create mode so hours should be present if abs element is hours based
3361 /*
3362
3363 if l_hours_or_days ='H' and (l_time_start is null or l_time_end is null ) then
3364 select time_start , time_end into l_time_start , l_time_end
3365 from per_absence_attendances
3366 where person_id=p_person_id
3367 and date_start = l_start_date
3368 and date_end = l_end_date;
3369 end if;
3370 */
3371
3372 IF l_time_start IS NULL THEN
3373 l_time_start := '00:00';
3374 ELSE
3375 null;
3376 END IF;
3377
3378 IF l_time_end IS NULL THEN
3379
3380 IF l_hours_or_days = 'D' THEN
3381 l_time_end := '00:00';
3382 else
3383 l_time_end := '23:59';
3384 END IF;
3385
3386 ELSE
3387 null;
3388 END IF;
3389
3390
3391
3392 v_start_date := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start,'DD-MM-YYYY HH24:MI');
3393 v_end_date := TO_DATE(TO_CHAR(l_end_date,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
3394 if g_debug then
3395 hr_utility.set_location('v_start_date: '||v_start_date,130);
3396 hr_utility.set_location('v_end_date: '||v_end_date,130);
3397 end if;
3398
3399 IF l_hours_or_days = 'D' THEN
3400 v_end_date := l_end_date + 1;
3401 END IF;
3402
3403 if g_debug then
3404 hr_utility.set_location('before cac .get_absence_data ',140);
3405 end if;
3406
3407 hr_wrk_sch_pkg.get_per_asg_schedule
3408 ( p_person_assignment_id => l_assignment_id
3409 , p_period_start_date => v_start_date
3410 , p_period_end_date => v_end_date
3411 , p_schedule_category => NULL
3412 , p_include_exceptions => 'Y'
3413 , p_busy_tentative_as => 'FREE'
3414 , x_schedule_source => l_schedule_source
3415 , x_schedule => l_schedule
3416 , x_return_status => l_return_status
3417 , x_return_message => l_return_message
3418 );
3419
3420 if g_debug then
3421 hr_utility.set_location('after cac .get_absence_data ',150);
3422 end if;
3423
3424 IF l_return_status = '0' THEN
3425
3426 if g_debug then
3427 hr_utility.set_location('Entering sch found ',151);
3428 end if;
3429
3430 l_idx := l_schedule.first;
3431 IF l_hours_or_days = 'D' THEN
3432
3433 if g_debug then
3434 hr_utility.set_location('get_absence_data SS',152);
3435 end if;
3436
3437 l_first_band := TRUE;
3438 l_ref_date := NULL;
3439
3440 WHILE l_idx IS NOT NULL
3441 LOOP
3442
3443 if g_debug then
3444 hr_utility.set_location('get_absence_data SS',153);
3445 end if;
3446
3447 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
3448 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
3449 i:=i+1;
3450
3451 --l_day_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'DD-MM-YYYY HH24:MI');
3452 --l_day_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'DD-MM-YYYY HH24:MI');
3453
3454 l_day_start_time := fnd_date.date_to_canonical(l_schedule(l_idx).START_DATE_TIME);
3455 l_day_end_time := fnd_date.date_to_canonical(l_schedule(l_idx).END_DATE_TIME);
3456
3457 absence_records(i).transactionid := l_transactionid;
3458 absence_records(i).absence_type_id:=l_abs_type;
3459 absence_records(i).element_type_id:=l_ele_type;
3460 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
3461 absence_records(i).abs_startdate:=l_day_start_time;
3462 absence_records(i).abs_enddate:=l_day_end_time;
3463 absence_records(i).modetype:='CreateMode';
3464 absence_records(i).PROGRAM_APPLICATION_ID :='800';
3465 absence_records(i).rec_start_date :=v_start_date;
3466 absence_records(i).rec_end_date :=v_end_date -1;
3467 absence_records(i).days_or_hours :=l_hours_or_days;
3468 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
3469
3470 END IF;
3471 END IF;
3472 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
3473 end loop;
3474
3475 -- for hours
3476 else
3477 if g_debug then
3478 hr_utility.set_location(' indexloop FOR Hours get_absence_data ',160);
3479 end if;
3480
3481
3482 l_day_start_time := '00:00';
3483 l_day_end_time := '23:59';
3484 WHILE l_idx IS NOT NULL
3485 LOOP
3486 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
3487 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
3488
3489 i:=i+1;
3490
3491 --l_day_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'DD-MM-YYYY HH24:MI');
3492 --l_day_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'DD-MM-YYYY HH24:MI');
3493
3494 l_day_start_time := fnd_date.date_to_canonical(l_schedule(l_idx).START_DATE_TIME);
3495 l_day_end_time := fnd_date.date_to_canonical(l_schedule(l_idx).END_DATE_TIME);
3496
3497 absence_records(i).transactionid := l_transactionid;
3498 absence_records(i).absence_type_id:=l_abs_type;
3499 absence_records(i).element_type_id:=l_ele_type;
3500 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
3501 absence_records(i).abs_startdate:=l_day_start_time;
3502 absence_records(i).abs_enddate:=l_day_end_time;
3503 absence_records(i).modetype:='CreateMode';
3504 absence_records(i).PROGRAM_APPLICATION_ID :='800';
3505 absence_records(i).rec_start_date :=v_start_date;
3506 absence_records(i).rec_end_date :=v_end_date ;
3507 absence_records(i).days_or_hours :=l_hours_or_days;
3508 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
3509
3510 END IF;
3511 END IF;
3512
3513 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
3514 END LOOP;
3515
3516 end if;
3517 end if;
3518
3519 end loop;
3520
3521 close csr_sshr_abs_records;
3522
3523 if g_debug then
3524 hr_utility.set_location('Block 1 -Create Mode',170);
3525 end if;
3526
3527 else
3528 -- when cac is not in use then we will provide absences on day basis as we do not know the schedule of the person
3529
3530 if g_debug then
3531 hr_utility.set_location('Block 1 -CAC is not used',180);
3532 end if;
3533
3534 open csr_sshr_abs_records('CreateMode');
3535 if g_debug then
3536 hr_utility.set_location('Entering .get_absence_data',190);
3537 end if;
3538 loop
3539
3540
3541 fetch csr_sshr_abs_records into l_start_date,l_end_date,l_time_start ,l_time_end,l_transactionid,l_transactiondocument,
3542 l_ss_ovn,l_abs_type,l_absence_attendance_id,l_ele_type,l_hours_or_days,l_confirmed_flag;
3543
3544 if g_debug then
3545 hr_utility.set_location('Entering .get_absence_data ',190);
3546 hr_utility.set_location(' l_start_date '||l_start_date,190);
3547 hr_utility.set_location(' l_end_date '|| l_end_date,190);
3548 hr_utility.set_location(' l_transactionid '||l_transactionid,190);
3549 hr_utility.set_location(' l_hours_or_days '||l_hours_or_days,190);
3550 hr_utility.set_location(' l_ele_type '||l_ele_type,190);
3551 hr_utility.set_location(' l_abs_type '||l_abs_type,190);
3552 end if;
3553
3554 exit when csr_sshr_abs_records%notfound;
3555
3556 l_rec_start_date :=l_start_date;
3557 l_rec_end_date :=l_end_date;
3558 if g_debug then
3559 hr_utility.set_location('Entering .get_absence_data ',200);
3560 end if;
3561
3562 IF l_time_start IS NULL THEN
3563 l_time_start := '00:00';
3564 l_time_start_new := '00:00';
3565 else
3566 l_time_start_new :=l_time_start;
3567 end if;
3568
3569 IF l_time_end IS NULL THEN
3570 l_time_end_new := '23:59';
3571 l_time_end := '23:59';
3572 else
3573 l_time_end_new :=l_time_end;
3574
3575 END IF;
3576
3577 -- when startdate is equal to enddate-------
3578 if l_start_date = l_end_date then
3579 i:=i+1;
3580
3581 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
3582 l_end_date_new := TO_DATE(TO_CHAR(l_end_date,'DD-MM-YYYY')||' '||l_time_end_new,'DD-MM-YYYY HH24:MI');
3583
3584 absence_records(i).transactionid := l_transactionid;
3585 absence_records(i).absence_type_id:=l_abs_type;
3586 absence_records(i).element_type_id:=l_ele_type;
3587 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
3588 absence_records(i).abs_startdate:= FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
3589 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
3590 absence_records(i).modetype:='CreateMode';
3591 absence_records(i).PROGRAM_APPLICATION_ID :='800';
3592 absence_records(i).rec_start_date :=l_rec_start_date;
3593 absence_records(i).rec_end_date :=l_rec_end_date;
3594 absence_records(i).days_or_hours :=l_hours_or_days;
3595 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
3596
3597 else
3598
3599 --- CASE 1 for to first input the startdate data ---
3600 i:=i+1;
3601
3602 l_time_end_new := '23:59';
3603
3604 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
3605 l_end_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_end_new,'DD-MM-YYYY HH24:MI');
3606
3607 absence_records(i).transactionid := l_transactionid;
3608 absence_records(i).absence_type_id:=l_abs_type;
3609 absence_records(i).element_type_id:=l_ele_type;
3610 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
3611 absence_records(i).abs_startdate:= FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
3612 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
3613 absence_records(i).modetype:='CreateMode';
3614 absence_records(i).PROGRAM_APPLICATION_ID :='800';
3615 absence_records(i).rec_start_date :=l_rec_start_date;
3616 absence_records(i).rec_end_date :=l_rec_end_date;
3617 absence_records(i).days_or_hours :=l_hours_or_days;
3618 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
3619
3620 -- end of case 1----
3621 -- case 2 is to insert from startdate + 1 to enddate-1 ------
3622
3623 l_start_date:=l_start_date + 1;
3624
3625 while l_start_date < l_end_date
3626 loop
3627 i:=i+1;
3628
3629 l_time_start_new := '00:00';
3630 l_time_end_new := '23:59';
3631
3632 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
3633 l_end_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_end_new,'DD-MM-YYYY HH24:MI');
3634
3635 absence_records(i).transactionid := l_transactionid;
3636 absence_records(i).absence_type_id:=l_abs_type;
3637 absence_records(i).element_type_id:=l_ele_type;
3638 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
3639 absence_records(i).abs_startdate:= FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
3640 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
3641 absence_records(i).modetype:='CreateMode';
3642 absence_records(i).PROGRAM_APPLICATION_ID :='800';
3643 absence_records(i).rec_start_date :=l_rec_start_date;
3644 absence_records(i).rec_end_date :=l_rec_end_date;
3645 absence_records(i).days_or_hours :=l_hours_or_days;
3646 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
3647
3648 l_start_date :=l_start_date+1;
3649
3650 end loop;
3651
3652 -- end of case2 -----
3653 -- case 3 to insert only enddate data---
3654
3655 i:=i+1;
3656
3657 l_time_start_new := '00:00';
3658
3659 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
3660 l_end_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
3661
3662 absence_records(i).transactionid := l_transactionid;
3663 absence_records(i).absence_type_id:=l_abs_type;
3664 absence_records(i).element_type_id:=l_ele_type;
3665 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
3666 absence_records(i).abs_startdate:= FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
3667 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
3668 absence_records(i).modetype:='CreateMode';
3669 absence_records(i).PROGRAM_APPLICATION_ID :='800';
3670 absence_records(i).rec_start_date :=l_rec_start_date;
3671 absence_records(i).rec_end_date :=l_rec_end_date;
3672 absence_records(i).days_or_hours :=l_hours_or_days;
3673 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
3674
3675 -- END of case3 to insert only enddate ---
3676
3677
3678 end if;
3679 end loop;
3680 close csr_sshr_abs_records;
3681 end if; -- CAC Check
3682
3683 if g_debug then
3684 hr_utility.set_location('End of Create Mode Blk1 ',200);
3685 end if;
3686
3687 -----END OF BLOCK 1-----
3688
3689 -- Block 2 will provide data from SSHR transaction tables which are in update mode
3690 -- will retrieve the data and a check is made with Core data to see which is having higher OVN number
3691 -- high ovn data will be sent and if core is having high ovn then the sshr transaction data is invalid
3692
3693 ------------------------
3694 ----BLOCK 2-----
3695 ------------------------
3696 while (ssmodes < 3) loop
3697
3698 if ssmodes =1 then
3699 p_ssmode :='UpdateMode';
3700
3701 elsif ssmodes =2 then
3702 p_ssmode :='ConfirmMode';
3703
3704 end if;
3705
3706 open csr_sshr_abs_records(p_ssmode);
3707
3708 if g_debug then
3709 hr_utility.set_location('Start of BLock 2',210);
3710 hr_utility.set_location('Entering .get_absence_data',210);
3711
3712 end if;
3713 loop
3714 fetch csr_sshr_abs_records into l_start_date,l_end_date,l_time_start ,l_time_end,l_transactionid,l_transactiondocument,
3715 l_ss_ovn,l_abs_type,l_absence_attendance_id,l_ele_type,l_hours_or_days,l_confirmed_flag;
3716
3717 exit when csr_sshr_abs_records%notfound;
3718 hr_utility.set_location('Before sql stmt',220);
3719
3720 BEGIN
3721 l_core_ovn :=null;
3722 select nvl(date_start,DATE_PROJECTED_START) datestart,
3723 nvl(date_end,DATE_PROJECTED_end) dateend,
3724 nvl(TIME_START,TIME_PROJECTED_START) timestart,
3725 nvl(TIME_END,TIME_PROJECTED_END) timeend,
3726 ABSENCE_ATTENDANCE_TYPE_ID,
3727 OBJECT_VERSION_NUMBER
3728 INTO l_core_dstart ,l_core_dend, l_core_tstart, l_core_tend, l_core_abs_attendance_id , l_core_ovn
3729 from per_absence_attendances
3730 where ABSENCE_ATTENDANCE_ID = l_absence_attendance_id;
3731 if g_debug then
3732 hr_utility.set_location('after sql stmt',220);
3733 end if;
3734
3735 exception
3736 when no_data_found then
3737
3738 if g_debug then
3739 hr_utility.set_location('in the exception',220);
3740 end if;
3741
3742 j:=j+1;
3743 absence_records_inv(j).transactionid := l_transactionid;
3744 absence_records_inv(j).abs_startdate := FND_DATE.DATE_TO_CANONICAL(l_start_date); -- changed for 8844454
3745 absence_records_inv(j).abs_enddate := FND_DATE.DATE_TO_CANONICAL(l_end_date); -- changed for 8844454
3746
3747
3748 END; -- end of core sql
3749
3750 if l_core_ovn > l_ss_ovn then
3751 -- ss data is invalid hence insert core data in to pl sql table
3752 if g_debug then
3753 hr_utility.set_location('Core Data modified when SS is pending',220);
3754 end if;
3755
3756 j:=j+1;
3757 absence_records_inv(j).transactionid := l_transactionid;
3758 absence_records_inv(j).abs_startdate := FND_DATE.DATE_TO_CANONICAL(l_start_date); -- changed for 8844454
3759 absence_records_inv(j).abs_enddate := FND_DATE.DATE_TO_CANONICAL(l_end_date); -- changed for 8844454
3760
3761 -- assigning to old local vars so that rest of process remains same and we do not need to replace the
3762 -- variables with core ones.
3763 --
3764 l_time_start:=l_core_tstart;
3765 l_time_end :=l_core_tend;
3766 l_start_date:=l_core_dstart;
3767 l_end_date:=l_core_dend;
3768 --
3769
3770 IF l_sch_based_dur = 'Y' THEN
3771
3772 hr_utility.set_location('CAC profile set to Yes Block2',230);
3773 IF l_time_start IS NULL THEN
3774 l_time_start := '00:00';
3775 ELSE
3776 null;
3777 END IF;
3778
3779 IF l_time_end IS NULL THEN
3780
3781 IF l_hours_or_days = 'D' THEN
3782 l_time_end := '00:00';
3783 else
3784 l_time_end := '23:59';
3785 END IF;
3786
3787 ELSE
3788 null;
3789 END IF;
3790
3791
3792
3793 v_start_date := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start,'DD-MM-YYYY HH24:MI');
3794 v_end_date := TO_DATE(TO_CHAR(l_end_date,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
3795
3796 if g_debug then
3797 hr_utility.set_location('v_start_date: '||v_start_date,10);
3798 hr_utility.set_location('v_end_date: '||v_end_date,10);
3799 end if;
3800
3801 IF l_hours_or_days = 'D' THEN
3802 v_end_date := l_end_date + 1;
3803 END IF;
3804
3805 hr_wrk_sch_pkg.get_per_asg_schedule
3806 ( p_person_assignment_id => l_assignment_id
3807 , p_period_start_date => v_start_date
3808 , p_period_end_date => v_end_date
3809 , p_schedule_category => NULL
3810 , p_include_exceptions => 'Y'
3811 , p_busy_tentative_as => 'FREE'
3812 , x_schedule_source => l_schedule_source
3813 , x_schedule => l_schedule
3814 , x_return_status => l_return_status
3815 , x_return_message => l_return_message
3816 );
3817 hr_utility.set_location('after cac .get_absence_data ',230);
3818
3819
3820 IF l_return_status = '0' THEN
3821
3822 if g_debug then
3823 hr_utility.set_location('Entering sch found ',240);
3824 end if;
3825
3826 l_idx := l_schedule.first;
3827
3828 IF l_hours_or_days = 'D' THEN
3829
3830 if g_debug then
3831 hr_utility.set_location('.get_absence_data ',250);
3832 end if;
3833
3834 l_first_band := TRUE;
3835 l_ref_date := NULL;
3836
3837 WHILE l_idx IS NOT NULL
3838 LOOP
3839
3840 if g_debug then
3841 hr_utility.set_location('.get_absence_data',270);
3842 end if;
3843
3844 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
3845 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
3846 i:=i+1;
3847
3848 --l_day_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'DD-MM-YYYY HH24:MI');
3849 --l_day_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'DD-MM-YYYY HH24:MI');
3850
3851
3852 l_day_start_time := fnd_date.date_to_canonical(l_schedule(l_idx).START_DATE_TIME);
3853 l_day_end_time := fnd_date.date_to_canonical(l_schedule(l_idx).END_DATE_TIME);
3854 absence_records(i).absence_type_id:=l_abs_type;
3855 absence_records(i).element_type_id:=l_ele_type;
3856 absence_records(i).abs_startdate:=l_day_start_time;
3857 absence_records(i).abs_enddate:=l_day_end_time;
3858 absence_records(i).absence_attendance_id:= l_absence_attendance_id ;
3859 absence_records(i).PROGRAM_APPLICATION_ID :='800';
3860 absence_records(i).rec_start_date :=v_start_date;
3861 absence_records(i).rec_end_date :=v_end_date -1;
3862 absence_records(i).days_or_hours :=l_hours_or_days;
3863 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
3864
3865 END IF;
3866 END IF;
3867 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
3868 end loop;
3869
3870 -- for hours
3871 else
3872
3873 if g_debug then
3874 hr_utility.set_location(' indexloop hours .get_absence_data block2 ',280);
3875 end if;
3876
3877
3878 l_day_start_time := '00:00';
3879 l_day_end_time := '23:59';
3880 WHILE l_idx IS NOT NULL
3881 LOOP
3882 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
3883 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
3884
3885 i:=i+1;
3886
3887 --l_day_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'DD-MM-YYYY HH24:MI');
3888 --l_day_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'DD-MM-YYYY HH24:MI');
3889
3890 l_day_start_time := fnd_date.date_to_canonical(l_schedule(l_idx).START_DATE_TIME);
3891 l_day_end_time := fnd_date.date_to_canonical(l_schedule(l_idx).END_DATE_TIME);
3892 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
3893 absence_records(i).absence_type_id:=l_abs_type;
3894 absence_records(i).element_type_id:=l_ele_type;
3895 absence_records(i).abs_startdate:=l_day_start_time;
3896 absence_records(i).abs_enddate:=l_day_end_time;
3897 absence_records(i).PROGRAM_APPLICATION_ID :='800';
3898 absence_records(i).rec_start_date :=v_start_date;
3899 absence_records(i).rec_end_date :=v_end_date;
3900 absence_records(i).days_or_hours :=l_hours_or_days;
3901 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
3902
3903 END IF;
3904 END IF;
3905
3906 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
3907 END LOOP;
3908
3909 end if; -- hours or days
3910 end if; -- l_return_status
3911
3912
3913 else
3914 -- when cac is not in use then we will provide absences on day basis as we do not know the schedule of the person
3915
3916 if g_debug then
3917 hr_utility.set_location('CAC is not used -block2 ',290);
3918 end if;
3919 l_rec_start_date :=l_start_date;
3920 l_rec_end_date :=l_end_date;
3921
3922 /* -- old code
3923
3924 while l_start_date <= l_end_date
3925 loop
3926 i:=i+1;
3927
3928
3929 absence_records(i).absence_type_id:=l_abs_type;
3930 absence_records(i).element_type_id:=l_ele_type;
3931 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date); -- changed for 8844454
3932 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_start_date); -- changed for 8844454
3933 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
3934 absence_records(i).PROGRAM_APPLICATION_ID :='800';
3935 absence_records(i).rec_start_date :=l_rec_start_date;
3936 absence_records(i).rec_end_date :=l_rec_end_date;
3937 absence_records(i).days_or_hours :=l_hours_or_days;
3938
3939 l_start_date :=l_start_date+1;
3940
3941 */-- end of old code
3942
3943
3944 IF l_time_start IS NULL THEN
3945 l_time_start := '00:00';
3946 l_time_start_new := '00:00';
3947 else
3948 l_time_start_new :=l_time_start;
3949 end if;
3950
3951 IF l_time_end IS NULL THEN
3952 l_time_end_new := '23:59';
3953 l_time_end := '23:59';
3954 else
3955 l_time_end_new :=l_time_end;
3956
3957 END IF;
3958
3959 -- when startdate is equal to enddate-------
3960
3961 if l_start_date = l_end_date then -- start of blk2 ins if
3962 i:=i+1;
3963
3964 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
3965 l_end_date_new := TO_DATE(TO_CHAR(l_end_date,'DD-MM-YYYY')||' '||l_time_end_new,'DD-MM-YYYY HH24:MI');
3966
3967 absence_records(i).absence_type_id:=l_abs_type;
3968 absence_records(i).element_type_id:=l_ele_type;
3969 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
3970 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
3971 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
3972 absence_records(i).PROGRAM_APPLICATION_ID :='800';
3973 absence_records(i).rec_start_date :=l_rec_start_date;
3974 absence_records(i).rec_end_date :=l_rec_end_date;
3975 absence_records(i).days_or_hours :=l_hours_or_days;
3976 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
3977
3978 else
3979
3980 --- CASE 1 for to first input the startdate ---
3981 i:=i+1;
3982
3983 l_time_end_new := '23:59';
3984
3985 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
3986 l_end_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_end_new,'DD-MM-YYYY HH24:MI');
3987
3988 absence_records(i).absence_type_id:=l_abs_type;
3989 absence_records(i).element_type_id:=l_ele_type;
3990 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
3991 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
3992 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
3993 absence_records(i).PROGRAM_APPLICATION_ID :='800';
3994 absence_records(i).rec_start_date :=l_rec_start_date;
3995 absence_records(i).rec_end_date :=l_rec_end_date;
3996 absence_records(i).days_or_hours :=l_hours_or_days;
3997 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
3998
3999 -- end of case 1----
4000 -- case 2 is to insert from startdate + 1 to enddate-1 ------
4001
4002 l_start_date:=l_start_date + 1;
4003
4004 while l_start_date < l_end_date
4005 loop
4006 i:=i+1;
4007
4008 l_time_start_new := '00:00';
4009 l_time_end_new := '23:59';
4010
4011 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
4012 l_end_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_end_new,'DD-MM-YYYY HH24:MI');
4013
4014 absence_records(i).absence_type_id:=l_abs_type;
4015 absence_records(i).element_type_id:=l_ele_type;
4016 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
4017 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
4018 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4019 absence_records(i).PROGRAM_APPLICATION_ID :='800';
4020 absence_records(i).rec_start_date :=l_rec_start_date;
4021 absence_records(i).rec_end_date :=l_rec_end_date;
4022 absence_records(i).days_or_hours :=l_hours_or_days;
4023 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4024
4025 l_start_date :=l_start_date+1;
4026
4027 end loop;
4028 -- end of case2 -----
4029 -- case 3 to insert only enddate data---
4030 i:=i+1;
4031
4032 l_time_start_new := '00:00';
4033
4034 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
4035 l_end_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
4036
4037
4038 absence_records(i).absence_type_id:=l_abs_type;
4039 absence_records(i).element_type_id:=l_ele_type;
4040 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
4041 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
4042 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4043 absence_records(i).PROGRAM_APPLICATION_ID :='800';
4044 absence_records(i).rec_start_date :=l_rec_start_date;
4045 absence_records(i).rec_end_date :=l_rec_end_date;
4046 absence_records(i).days_or_hours :=l_hours_or_days;
4047 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4048
4049 -- END of case3 to insert only enddate ---
4050
4051
4052 end if; --end of blk2 ins if
4053
4054 end if; -- CAC Cheeck if condition
4055
4056 elsif l_core_ovn <= l_ss_ovn then -- " SSHR DATA is valid and insert into pl sql table. " --( bug fix 8881266 )
4057 ---------------
4058 -- " SSHR DATA is valid and insert into pl sql table. "
4059 ---------------
4060 if g_debug then
4061 hr_utility.set_location('SS Tran data is valid- block2 ',280);
4062 end if;
4063
4064
4065 if l_hours_or_days ='H' then
4066
4067 if (l_time_start is null ) then
4068 select nvl(TIME_START,TIME_PROJECTED_START)into l_time_start
4069 from per_absence_attendances
4070 where ABSENCE_ATTENDANCE_ID = l_absence_attendance_id;
4071 end if;
4072
4073 if ( l_time_end is null ) then
4074 select nvl(TIME_END,TIME_PROJECTED_END) into l_time_end
4075 from per_absence_attendances
4076 where ABSENCE_ATTENDANCE_ID = l_absence_attendance_id;
4077
4078 end if;
4079
4080 end if;
4081
4082 IF l_sch_based_dur = 'Y' THEN
4083
4084 if g_debug then
4085 hr_utility.set_location(' CAC profile set to Yes block2 ',280);
4086 end if;
4087
4088 -- as this is in update mode so hours may not be present in ss tran tables hence getting from core tables
4089
4090
4091 IF l_time_start IS NULL THEN
4092 l_time_start := '00:00';
4093 ELSE
4094 null;
4095 END IF;
4096
4097 IF l_time_end IS NULL THEN
4098
4099 IF l_hours_or_days = 'D' THEN
4100 l_time_end := '00:00';
4101 else
4102 l_time_end := '23:59';
4103 END IF;
4104
4105 ELSE
4106 null;
4107 END IF;
4108
4109
4110
4111 v_start_date := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start,'DD-MM-YYYY HH24:MI');
4112 v_end_date := TO_DATE(TO_CHAR(l_end_date,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
4113 if g_debug then
4114 hr_utility.set_location('v_start_date: '||v_start_date,290);
4115 hr_utility.set_location('v_end_date: '||v_end_date,290);
4116 end if;
4117
4118 IF l_hours_or_days = 'D' THEN
4119 v_end_date := l_end_date + 1;
4120 END IF;
4121
4122 if g_debug then
4123 hr_utility.set_location('before cac .get_absence_data ',300);
4124 end if;
4125 hr_wrk_sch_pkg.get_per_asg_schedule
4126 ( p_person_assignment_id => l_assignment_id
4127 , p_period_start_date => v_start_date
4128 , p_period_end_date => v_end_date
4129 , p_schedule_category => NULL
4130 , p_include_exceptions => 'Y'
4131 , p_busy_tentative_as => 'FREE'
4132 , x_schedule_source => l_schedule_source
4133 , x_schedule => l_schedule
4134 , x_return_status => l_return_status
4135 , x_return_message => l_return_message
4136 );
4137 if g_debug then
4138 hr_utility.set_location('after cac .get_absence_data ',310);
4139 end if;
4140
4141 IF l_return_status = '0' THEN
4142
4143 if g_debug then
4144 hr_utility.set_location('Entering sch found ',310);
4145 end if;
4146
4147 l_idx := l_schedule.first;
4148 IF l_hours_or_days = 'D' THEN
4149
4150 if g_debug then
4151 hr_utility.set_location('.get_absence_data ',320);
4152 end if;
4153
4154 l_first_band := TRUE;
4155 l_ref_date := NULL;
4156
4157 WHILE l_idx IS NOT NULL
4158 LOOP
4159
4160 if g_debug then
4161 hr_utility.set_location('.get_absence_data ',330);
4162 end if;
4163
4164 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
4165 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
4166 i:=i+1;
4167
4168 --l_day_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'DD-MM-YYYY HH24:MI');
4169 --l_day_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'DD-MM-YYYY HH24:MI');
4170
4171 l_day_start_time := fnd_date.date_to_canonical(l_schedule(l_idx).START_DATE_TIME);
4172 l_day_end_time := fnd_date.date_to_canonical(l_schedule(l_idx).END_DATE_TIME);
4173
4174 absence_records(i).transactionid := l_transactionid;
4175 absence_records(i).absence_type_id:=l_abs_type;
4176 absence_records(i).element_type_id:=l_ele_type;
4177 absence_records(i).abs_startdate:=l_day_start_time;
4178 absence_records(i).abs_enddate:=l_day_end_time;
4179 absence_records(i).modetype:='UPdateMode';
4180 absence_records(i).PROGRAM_APPLICATION_ID :='800';
4181 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4182 absence_records(i).rec_start_date :=v_start_date;
4183 absence_records(i).rec_end_date :=v_end_date -1;
4184 absence_records(i).days_or_hours :=l_hours_or_days;
4185 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4186
4187 END IF;
4188 END IF;
4189 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
4190 end loop;
4191
4192 -- for hours
4193 else
4194 if g_debug then
4195 hr_utility.set_location(' indexloop hours .get_absence_data blk2',340);
4196 end if;
4197
4198
4199 l_day_start_time := '00:00';
4200 l_day_end_time := '23:59';
4201 WHILE l_idx IS NOT NULL
4202 LOOP
4203 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
4204 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
4205
4206 i:=i+1;
4207
4208 --l_day_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'DD-MM-YYYY HH24:MI');
4209 --l_day_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'DD-MM-YYYY HH24:MI');
4210
4211 l_day_start_time := fnd_date.date_to_canonical(l_schedule(l_idx).START_DATE_TIME);
4212 l_day_end_time := fnd_date.date_to_canonical(l_schedule(l_idx).END_DATE_TIME);
4213
4214 absence_records(i).transactionid := l_transactionid;
4215 absence_records(i).absence_type_id:=l_abs_type;
4216 absence_records(i).element_type_id:=l_ele_type;
4217 absence_records(i).abs_startdate:=l_day_start_time;
4218 absence_records(i).abs_enddate:=l_day_end_time;
4219 absence_records(i).modetype:='UpdateMode';
4220 absence_records(i).PROGRAM_APPLICATION_ID :='800';
4221 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4222 absence_records(i).rec_start_date :=v_start_date;
4223 absence_records(i).rec_end_date :=v_end_date;
4224 absence_records(i).days_or_hours :=l_hours_or_days;
4225 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4226
4227 END IF;
4228 END IF;
4229
4230 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
4231 END LOOP;
4232
4233 end if; -- l_hours_days if
4234 end if; -- l_return_status if
4235
4236
4237 else
4238
4239 -- when cac is not in use then we will provide absences on day basis as we do not know the schedule of the person
4240
4241 if g_debug then
4242 hr_utility.set_location('CAC is not used -block2 ',290);
4243 end if;
4244 l_rec_start_date :=l_start_date;
4245 l_rec_end_date :=l_end_date;
4246
4247 /*
4248
4249 while l_start_date <= l_end_date
4250 loop
4251 i:=i+1;
4252
4253 absence_records(i).transactionid := l_transactionid;
4254 absence_records(i).absence_type_id:=l_abs_type;
4255 absence_records(i).element_type_id:=l_ele_type;
4256 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date); -- changed for 8844454
4257 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_start_date); -- changed for 8844454
4258 absence_records(i).modetype:='UpdateMode';
4259 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4260 absence_records(i).PROGRAM_APPLICATION_ID :='800';
4261 absence_records(i).rec_start_date :=l_rec_start_date;
4262 absence_records(i).rec_end_date :=l_rec_end_date;
4263 absence_records(i).days_or_hours :=l_hours_or_days;
4264
4265 l_start_date :=l_start_date+1;
4266 end loop;
4267 */
4268
4269
4270 IF l_time_start IS NULL THEN
4271 l_time_start := '00:00';
4272 l_time_start_new := '00:00';
4273 else
4274 l_time_start_new :=l_time_start;
4275 end if;
4276
4277 IF l_time_end IS NULL THEN
4278 l_time_end_new := '23:59';
4279 l_time_end := '23:59';
4280 else
4281 l_time_end_new :=l_time_end;
4282
4283 END IF;
4284
4285 -- when startdate is equal to enddate-------
4286
4287 if l_start_date = l_end_date then
4288 i:=i+1;
4289
4290 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
4291 l_end_date_new := TO_DATE(TO_CHAR(l_end_date,'DD-MM-YYYY')||' '||l_time_end_new,'DD-MM-YYYY HH24:MI');
4292
4293
4294 absence_records(i).transactionid := l_transactionid;
4295 absence_records(i).absence_type_id:=l_abs_type;
4296 absence_records(i).element_type_id:=l_ele_type;
4297 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
4298 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
4299 absence_records(i).modetype:='UpdateMode';
4300 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4301 absence_records(i).PROGRAM_APPLICATION_ID :='800';
4302 absence_records(i).rec_start_date :=l_rec_start_date;
4303 absence_records(i).rec_end_date :=l_rec_end_date;
4304 absence_records(i).days_or_hours :=l_hours_or_days;
4305 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4306
4307 else
4308
4309 --- CASE 1 for to first input the start date ---
4310 i:=i+1;
4311
4312 l_time_end_new := '23:59';
4313
4314 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
4315 l_end_date_new := TO_DATE(TO_CHAR(l_end_date,'DD-MM-YYYY')||' '||l_time_end_new,'DD-MM-YYYY HH24:MI');
4316
4317
4318 absence_records(i).transactionid := l_transactionid;
4319 absence_records(i).absence_type_id:=l_abs_type;
4320 absence_records(i).element_type_id:=l_ele_type;
4321 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
4322 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
4323 absence_records(i).modetype:='UpdateMode';
4324 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4325 absence_records(i).PROGRAM_APPLICATION_ID :='800';
4326 absence_records(i).rec_start_date :=l_rec_start_date;
4327 absence_records(i).rec_end_date :=l_rec_end_date;
4328 absence_records(i).days_or_hours :=l_hours_or_days;
4329 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4330
4331 -- end of case 1----
4332 -- case 2 is to insert from startdate + 1 to enddate-1 ------
4333
4334 l_start_date:=l_start_date + 1;
4335
4336 while l_start_date < l_end_date
4337 loop
4338 i:=i+1;
4339
4340 l_time_start_new := '00:00';
4341 l_time_end_new := '23:59';
4342
4343 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
4344 l_end_date_new := TO_DATE(TO_CHAR(l_end_date,'DD-MM-YYYY')||' '||l_time_end_new,'DD-MM-YYYY HH24:MI');
4345
4346
4347 absence_records(i).transactionid := l_transactionid;
4348 absence_records(i).absence_type_id:=l_abs_type;
4349 absence_records(i).element_type_id:=l_ele_type;
4350 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
4351 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
4352 absence_records(i).modetype:='UpdateMode';
4353 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4354 absence_records(i).PROGRAM_APPLICATION_ID :='800';
4355 absence_records(i).rec_start_date :=l_rec_start_date;
4356 absence_records(i).rec_end_date :=l_rec_end_date;
4357 absence_records(i).days_or_hours :=l_hours_or_days;
4358 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4359
4360 l_start_date :=l_start_date+1;
4361 end loop;
4362 -- end of case2 -----
4363 -- case 3 to insert only enddate data---
4364 i:=i+1;
4365
4366 l_time_start_new := '00:00';
4367
4368 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
4369 l_end_date_new := TO_DATE(TO_CHAR(l_end_date,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
4370
4371
4372 absence_records(i).transactionid := l_transactionid;
4373 absence_records(i).absence_type_id:=l_abs_type;
4374 absence_records(i).element_type_id:=l_ele_type;
4375 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
4376 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
4377 absence_records(i).modetype:='UpdateMode';
4378 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4379 absence_records(i).PROGRAM_APPLICATION_ID :='800';
4380 absence_records(i).rec_start_date :=l_rec_start_date;
4381 absence_records(i).rec_end_date :=l_rec_end_date;
4382 absence_records(i).days_or_hours :=l_hours_or_days;
4383 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4384
4385 -- END of case3 to insert only enddate ---
4386
4387 end if;
4388 end if; -- CAC check block 2
4389
4390 end if; -- core_ovn > ss_ovn if condition
4391 end loop;
4392 close csr_sshr_abs_records;
4393 if g_debug then
4394 hr_utility.set_location(' End of Blk2',340);
4395 end if;
4396 ssmodes :=ssmodes+1;
4397 end loop; -- new while loop to cover for "COnfirm mode" as well
4398 -----------------------
4399 ----End of Block 2-----
4400 -----------------------
4401
4402 ---Start of Block 3------
4403 ----------------
4404 -- This will provide only the data in CORE Tables for which no transaction is in update mode and
4405 -- pending for approval.
4406
4407 open csr_core_absences;
4408 loop
4409 fetch csr_core_absences into l_start_date,l_end_date,l_time_start ,l_time_end,
4410 l_abs_type,l_ele_type,l_absence_attendance_id,l_hours_or_days,l_program_application_id,l_duration,l_confirmed_flag;
4411
4412
4413 if g_debug then
4414 hr_utility.set_location(' Ony Core Data Blk3',400);
4415 end if;
4416
4417 if g_debug then
4418 hr_utility.set_location('Entering .get_absence_data',400);
4419 hr_utility.set_location(' l_start_date '||l_start_date,400);
4420 hr_utility.set_location(' l_end_date '|| l_end_date,400);
4421
4422 hr_utility.set_location(' l_hours_or_days '||l_hours_or_days,400);
4423 hr_utility.set_location(' l_ele_type '||l_ele_type,400);
4424 hr_utility.set_location(' l_abs_type '||l_abs_type,400);
4425 end if;
4426
4427 exit when csr_core_absences%notfound;
4428
4429 IF l_sch_based_dur = 'Y' THEN
4430
4431 if g_debug then
4432 hr_utility.set_location(' CAC Profile set yes Blk3',410);
4433 end if;
4434
4435
4436 IF l_time_start IS NULL THEN
4437 l_time_start := '00:00';
4438 ELSE
4439 null;
4440 END IF;
4441
4442 IF l_time_end IS NULL THEN
4443
4444 IF l_hours_or_days = 'D' THEN
4445 l_time_end := '00:00';
4446 else
4447 l_time_end := '23:59';
4448 END IF;
4449
4450 ELSE
4451 null;
4452 END IF;
4453
4454
4455
4456 v_start_date := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start,'DD-MM-YYYY HH24:MI');
4457 v_end_date := TO_DATE(TO_CHAR(l_end_date,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
4458 if g_debug then
4459 hr_utility.set_location('v_start_date: '||v_start_date,410);
4460 hr_utility.set_location('v_end_date: '||v_end_date,410);
4461 end if;
4462
4463 IF l_hours_or_days = 'D' THEN
4464 v_end_date := l_end_date + 1;
4465 END IF;
4466
4467 if g_debug then
4468 hr_utility.set_location('before cac .get_absence_data SS',420);
4469 end if;
4470 hr_wrk_sch_pkg.get_per_asg_schedule
4471 ( p_person_assignment_id => l_assignment_id
4472 , p_period_start_date => v_start_date
4473 , p_period_end_date => v_end_date
4474 , p_schedule_category => NULL
4475 , p_include_exceptions => 'Y'
4476 , p_busy_tentative_as => 'FREE'
4477 , x_schedule_source => l_schedule_source
4478 , x_schedule => l_schedule
4479 , x_return_status => l_return_status
4480 , x_return_message => l_return_message
4481 );
4482 if g_debug then
4483 hr_utility.set_location('after cac .get_absence_data ',430);
4484 end if;
4485
4486 IF l_return_status = '0' THEN
4487
4488 if g_debug then
4489 hr_utility.set_location('Entering sch found ',430);
4490 end if;
4491
4492 l_idx := l_schedule.first;
4493 IF l_hours_or_days = 'D' THEN
4494
4495 if g_debug then
4496 hr_utility.set_location('.get_absence_data',440);
4497 end if;
4498
4499 l_first_band := TRUE;
4500 l_ref_date := NULL;
4501
4502 WHILE l_idx IS NOT NULL
4503 LOOP
4504
4505 if g_debug then
4506 hr_utility.set_location('.get_absence_data',450);
4507 end if;
4508
4509 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
4510 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
4511 i:=i+1;
4512
4513 --l_day_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'DD-MM-YYYY HH24:MI');
4514 --l_day_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'DD-MM-YYYY HH24:MI');
4515
4516 l_day_start_time := fnd_date.date_to_canonical(l_schedule(l_idx).START_DATE_TIME);
4517 l_day_end_time := fnd_date.date_to_canonical(l_schedule(l_idx).END_DATE_TIME);
4518
4519 absence_records(i).absence_type_id:=l_abs_type;
4520 absence_records(i).element_type_id:=l_ele_type;
4521 absence_records(i).abs_startdate:=l_day_start_time;
4522 absence_records(i).abs_enddate:=l_day_end_time;
4523 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4524 absence_records(i).PROGRAM_APPLICATION_ID :=l_program_application_id;
4525 absence_records(i).rec_start_date :=v_start_date;
4526 absence_records(i).rec_end_date :=v_end_date -1;
4527 absence_records(i).days_or_hours :=l_hours_or_days;
4528 absence_records(i).rec_duration := l_duration;
4529 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4530
4531 END IF;
4532 END IF;
4533 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
4534 end loop;
4535
4536 -- for hours
4537 else
4538 if g_debug then
4539 hr_utility.set_location(' indexloop hours .get_absence_data blk3',460);
4540 end if;
4541
4542
4543 l_day_start_time := '00:00';
4544 l_day_end_time := '23:59';
4545 WHILE l_idx IS NOT NULL
4546 LOOP
4547 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
4548 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
4549
4550 i:=i+1;
4551
4552 -- l_day_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'DD-MM-YYYY HH24:MI');
4553 -- l_day_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'DD-MM-YYYY HH24:MI');
4554
4555 l_day_start_time := fnd_date.date_to_canonical(l_schedule(l_idx).START_DATE_TIME);
4556 l_day_end_time := fnd_date.date_to_canonical(l_schedule(l_idx).END_DATE_TIME);
4557
4558
4559
4560 absence_records(i).absence_type_id:=l_abs_type;
4561 absence_records(i).element_type_id:=l_ele_type;
4562 absence_records(i).abs_startdate:=l_day_start_time;
4563 absence_records(i).abs_enddate:=l_day_end_time;
4564 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4565 absence_records(i).PROGRAM_APPLICATION_ID := l_program_application_id ;
4566 absence_records(i).rec_start_date :=v_start_date;
4567 absence_records(i).rec_end_date :=v_end_date;
4568 absence_records(i).days_or_hours :=l_hours_or_days;
4569 absence_records(i).rec_duration := l_duration;
4570 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4571
4572 END IF;
4573 END IF;
4574
4575 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
4576 END LOOP;
4577
4578 end if;
4579 end if;
4580
4581
4582
4583 else
4584 -- when cac is not in use then we will provide absences on day basis as we do not know the schedule of the person
4585
4586
4587 if g_debug then
4588 hr_utility.set_location('CAC not is use Blk3',490);
4589 end if;
4590
4591 l_rec_start_date :=l_start_date;
4592 l_rec_end_date :=l_end_date;
4593
4594 /*
4595 while l_start_date <= l_end_date
4596 loop
4597 i:=i+1;
4598
4599
4600 absence_records(i).absence_type_id:=l_abs_type;
4601 absence_records(i).element_type_id:=l_ele_type;
4602 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date); -- changed for 8844454
4603 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_start_date); -- changed for 8844454
4604 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4605 absence_records(i).PROGRAM_APPLICATION_ID :=l_program_application_id;
4606 absence_records(i).rec_start_date :=l_rec_start_date;
4607 absence_records(i).rec_end_date :=l_rec_end_date;
4608 absence_records(i).days_or_hours :=l_hours_or_days;
4609 absence_records(i).rec_duration := l_duration;
4610
4611 l_start_date :=l_start_date+1;
4612 end loop;
4613 */
4614
4615
4616 IF l_time_start IS NULL THEN
4617 l_time_start := '00:00';
4618 l_time_start_new := '00:00';
4619 else
4620 l_time_start_new :=l_time_start;
4621 end if;
4622
4623 IF l_time_end IS NULL THEN
4624 l_time_end_new := '23:59';
4625 l_time_end := '23:59';
4626 else
4627 l_time_end_new :=l_time_end;
4628
4629 END IF;
4630
4631 -- when startdate is equal to enddate-------
4632
4633 if l_start_date = l_end_date then
4634 i:=i+1;
4635
4636 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
4637 l_end_date_new := TO_DATE(TO_CHAR(l_end_date,'DD-MM-YYYY')||' '||l_time_end_new,'DD-MM-YYYY HH24:MI');
4638
4639
4640
4641 absence_records(i).absence_type_id:=l_abs_type;
4642 absence_records(i).element_type_id:=l_ele_type;
4643 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
4644 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
4645 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4646 absence_records(i).PROGRAM_APPLICATION_ID :=l_program_application_id;
4647 absence_records(i).rec_start_date :=l_rec_start_date;
4648 absence_records(i).rec_end_date :=l_rec_end_date;
4649 absence_records(i).days_or_hours :=l_hours_or_days;
4650 absence_records(i).rec_duration := l_duration;
4651 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4652
4653 else
4654
4655 --- CASE 1 for to first input the start date ---
4656 i:=i+1;
4657
4658 l_time_end_new := '23:59';
4659
4660 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
4661 l_end_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_end_new,'DD-MM-YYYY HH24:MI');
4662
4663
4664 absence_records(i).absence_type_id:=l_abs_type;
4665 absence_records(i).element_type_id:=l_ele_type;
4666 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
4667 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
4668 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4669 absence_records(i).PROGRAM_APPLICATION_ID :=l_program_application_id;
4670 absence_records(i).rec_start_date :=l_rec_start_date;
4671 absence_records(i).rec_end_date :=l_rec_end_date;
4672 absence_records(i).days_or_hours :=l_hours_or_days;
4673 absence_records(i).rec_duration := l_duration;
4674 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4675
4676 -- end of case 1----
4677 -- case 2 is to insert from startdate + 1 to enddate-1 ------
4678
4679 l_start_date:=l_start_date + 1;
4680
4681 while l_start_date < l_end_date
4682 loop
4683 i:=i+1;
4684
4685 l_time_start_new := '00:00';
4686 l_time_end_new := '23:59';
4687
4688 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
4689 l_end_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_end_new,'DD-MM-YYYY HH24:MI');
4690
4691
4692 absence_records(i).absence_type_id:=l_abs_type;
4693 absence_records(i).element_type_id:=l_ele_type;
4694 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
4695 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
4696 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4697 absence_records(i).PROGRAM_APPLICATION_ID :=l_program_application_id;
4698 absence_records(i).rec_start_date :=l_rec_start_date;
4699 absence_records(i).rec_end_date :=l_rec_end_date;
4700 absence_records(i).days_or_hours :=l_hours_or_days;
4701 absence_records(i).rec_duration := l_duration;
4702 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4703
4704 l_start_date :=l_start_date+1;
4705 end loop;
4706 -- end of case2 -----
4707 -- case 3 to insert only enddate data---
4708 i:=i+1;
4709
4710 l_time_start_new := '00:00';
4711
4712 l_start_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_start_new,'DD-MM-YYYY HH24:MI');
4713 l_end_date_new := TO_DATE(TO_CHAR(l_start_date,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
4714
4715
4716 absence_records(i).absence_type_id:=l_abs_type;
4717 absence_records(i).element_type_id:=l_ele_type;
4718 absence_records(i).abs_startdate:=FND_DATE.DATE_TO_CANONICAL(l_start_date_new); -- changed for 8844454
4719 absence_records(i).abs_enddate:=FND_DATE.DATE_TO_CANONICAL(l_end_date_new); -- changed for 8844454
4720 absence_records(i).absence_attendance_id:= l_absence_attendance_id;
4721 absence_records(i).PROGRAM_APPLICATION_ID :=l_program_application_id;
4722 absence_records(i).rec_start_date :=l_rec_start_date;
4723 absence_records(i).rec_end_date :=l_rec_end_date;
4724 absence_records(i).days_or_hours :=l_hours_or_days;
4725 absence_records(i).rec_duration := l_duration;
4726 absence_records(i).confirmed_flag :=l_confirmed_flag;-- added
4727
4728 -- END of case3 to insert only enddate ---
4729
4730
4731 end if;
4732
4733
4734
4735
4736 end if; -- CAC Check block 3 closed
4737
4738 end loop; -- csr_core_absences loop
4739 close csr_core_absences;
4740 if g_debug then
4741 hr_utility.set_location('End of Blk3 ',500);
4742 end if;
4743
4744 end if; -- for Delete mode check
4745
4746 else
4747
4748 if g_debug then
4749 hr_utility.set_location(' Profile HR OTL Integ setto OFF ',10);
4750 end if;
4751
4752 end if; -- OTL Integration Check
4753
4754 EXCEPTION
4755 WHEN e_bad_time_format THEN
4756 hr_utility.set_location('Leaving coz exception '||'.get_absence_data',30);
4757 hr_utility.set_location(SQLERRM,35);
4758 RAISE;
4759 /*
4760 WHEN OTHERS THEN
4761 hr_utility.set_location('Leaving coz exception '||'.get_absence_data',40);
4762 hr_utility.set_location(SQLERRM,45);
4763 RAISE;
4764 */
4765
4766
4767 end get_absence_data;
4768 --
4769 --
4770 end hr_person_absence_api;