[Home] [Help]
PACKAGE BODY: APPS.HR_PERSON_ABSENCE_SWI
Source
1 Package body hr_person_absence_swi As
2 /* $Header: hrabsswi.pkb 120.20.12020000.2 2012/11/06 08:33:22 aammoham ship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'hr_person_absence_swi.';
7
8 g_date_format constant varchar2(10):='RRRR-MM-DD';
9 g_usr_day_time_fmt varchar(40) := g_date_format|| ' HH24:MI:SS';
10
11 function getEndDate(p_transaction_id in number) return date
12 IS
13 c_proc constant varchar2(30) := 'getEndDate';
14 lv_EndDate hr_api_transaction_steps.Information1%type;
15 begin
16
17 if(p_transaction_id is not null) then
18 begin
19 select nvl(Information2,Information4)
20 into lv_EndDate
21 from hr_api_transaction_steps
22 where transaction_id=p_transaction_id;
23
24 exception
25 when others then
26 null;
27 lv_EndDate:=null;
28 end;
29 end if;
30 if(lv_EndDate is not null) then
31 return fnd_date.canonical_to_date(lv_EndDate);
32 else
33 return null;
34 end if;
35
36
37
38 exception
39 when others then
40 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
41 Wf_Core.Context(g_package, c_proc, p_transaction_id);
42 -- raise;
43 return null;
44 end getEndDate;
45
46 function getStartDate(p_transaction_id in number) return date
47
48 IS
49 c_proc constant varchar2(30) := 'getStartDate';
50 lv_startDate hr_api_transaction_steps.Information1%type;
51 begin
52
53 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
54
55
56 if(p_transaction_id is not null) then
57 begin
58 select nvl(Information1,Information3)
59 into lv_startDate
60 from hr_api_transaction_steps
61 where transaction_id=p_transaction_id;
62
63 exception
64 when others then
65 null;
66 lv_startDate:=null;
67 end;
68 end if;
69 if(lv_startDate is not null) then
70 return fnd_date.canonical_to_date(lv_startDate);
71 else
72 return null;
73 end if;
74
75 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
76
77
78 exception
79 when others then
80 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
81 Wf_Core.Context(g_package, c_proc, p_transaction_id);
82 -- raise;
83 return null;
84 end getStartDate;
85
86 function chk_overlap(
87 p_person_id IN NUMBER
88 ,p_business_group_id IN NUMBER
89 ,p_date_start IN DATE
90 ,p_date_end IN DATE
91 ,p_time_start IN VARCHAR2
92 ,p_time_end IN VARCHAR2
93 ) return boolean
94 IS
95 --
96 --
97 l_proc varchar2(250) := g_package||'chk_overlap';
98 l_exists varchar2(250) ;
99 l_abs_overlap_warning boolean;
100 --
101 --
102
103 -- Fix 2706099
104 CURSOR c_abs_overlap(p_person_id IN NUMBER
105 ,p_business_grroup_id IN NUMBER
106 ,p_date_start IN DATE
107 ,p_date_end IN DATE
108 ,p_time_start IN VARCHAR2
109 ,p_time_end IN VARCHAR2
110 ) IS
111 SELECT 1
112 from hr_api_transactions hat
113 where
114 hat.selected_person_id = p_person_id
115 and hat.TRANSACTION_IDENTIFIER = 'ABSENCES'
116 and hat.STATUS in ('Y','C')
117 --Code for Bug 10218346
118 and hat.transaction_id <>
119 nvl(hr_transaction_swi.g_txn_ctx.transaction_id,-1)
120 --End of Code for Bug 10218346
121 and hr_person_absence_swi.getStartDate(hat.transaction_id) is not null
122 and hr_person_absence_swi.getEndDate(hat.transaction_id) is not null
123 and (
124 (
125 to_date (to_char(hr_person_absence_swi.getStartDate(hat.transaction_id), g_date_format) ||' ' ||
126 nvl((hr_xml_util.get_node_value(hat.transaction_id,'TimeProjectedStart','Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)),'00:00'),g_usr_day_time_fmt)
127 BETWEEN to_date (to_char(p_date_start, g_date_format)||' ' || nvl(null,'00:00'),g_usr_day_time_fmt)
128 AND to_date(to_char(nvl(p_date_end,p_date_start), g_date_format) || ' '|| nvl(null,'00:00') , g_usr_day_time_fmt)
129 )
130 or
131 (
132 to_date (to_char(p_date_start, g_date_format) ||' ' || nvl(null,'00:00'),g_usr_day_time_fmt)
133 BETWEEN
134 to_date (to_char(hr_person_absence_swi.getStartDate(hat.transaction_id), g_date_format) ||' ' ||
135 nvl((hr_xml_util.get_node_value(hat.transaction_id,'TimeProjectedStart','Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)),'00:00'),g_usr_day_time_fmt)
136 AND
137 to_date (to_char(hr_person_absence_swi.getEndDate(hat.transaction_id), g_date_format) ||' ' ||
138 nvl((hr_xml_util.get_node_value(hat.transaction_id,'TimeProjectedEnd','Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)),'00:00'),g_usr_day_time_fmt)
139 )
140
141 );
142 --
143
144 BEGIN
145 --
146
147 hr_utility.set_location('Entering:'|| l_proc, 10);
148
149 --
150 -- check if this absence overlaps another absence for the same person.
151 --
152
153
154 open c_abs_overlap(p_person_id,p_business_group_id,p_date_start,p_date_end,p_time_start,p_time_end);
155 fetch c_abs_overlap into l_exists;
156
157 if c_abs_overlap%found then
158
159 --
160 -- Set the warning message
161 --
162 l_abs_overlap_warning := TRUE;
163 close c_abs_overlap;
164 --
165 else
166
167 l_abs_overlap_warning := FALSE;
168
169 end if;
170 --
171 return l_abs_overlap_warning;
172
173 --
174 EXCEPTION
175 WHEN OTHERS THEN
176 hr_utility.trace(' .chk_overlap: ' || SQLERRM );
177 hr_utility.set_location('Leaving:'|| l_proc, 555);
178 raise ;
179 end chk_overlap;
180
181 function is_gb_leg_and_category_s(p_absence_attendance_type_id IN NUMBER ,
182 p_business_group_id IN NUMBER)
183 return boolean is
184
185 l_proc varchar2(200) := g_package || 'is_gb_leg_and_category_s';
186
187 populate_sickness_dates boolean := false;
188 l_absence_category per_absence_attendance_types.absence_category%type ;
189 l_legislation_code varchar2(150);
190
191 cursor get_category_code (p_absence_attendance_type_id number) is
192 select absence_category
193 from per_absence_attendance_types
194 where absence_attendance_type_id = p_absence_attendance_type_id;
195
196 begin
197 hr_utility.set_location(' Entering:' || l_proc,5);
198
199 open get_category_code(p_absence_attendance_type_id);
200 fetch get_category_code into l_absence_category ;
201 close get_category_code ;
202
203 IF l_absence_category = 'S' THEN
204 l_legislation_code := hr_api.return_legislation_code(p_business_group_id);
205 IF l_legislation_code = 'GB' THEN
206 hr_utility.set_location(l_proc,10);
207
208 populate_sickness_dates := true;
209 END IF;
210 END IF;
211 hr_utility.set_location(' Leaving:' || l_proc,15);
212
213 return populate_sickness_dates;
214 END is_gb_leg_and_category_s ;
215 --
216 --
217 -- ----------------------------------------------------------------------------
218 -- |-------------------------< create_person_absence >------------------------|
219 -- ----------------------------------------------------------------------------
220 PROCEDURE create_person_absence
221 (p_validate in number default hr_api.g_false_num
222 ,p_effective_date in date
223 ,p_person_id in number
224 ,p_business_group_id in number
225 ,p_absence_attendance_type_id in number
226 ,p_abs_attendance_reason_id in number default null
227 ,p_comments in clob default null --Bug#13362792
228 ,p_date_notification in date default null
229 ,p_date_projected_start in date default null
230 ,p_time_projected_start in varchar2 default null
231 ,p_date_projected_end in date default null
232 ,p_time_projected_end in varchar2 default null
233 ,p_date_start in date default null
234 ,p_time_start in varchar2 default null
235 ,p_date_end in date default null
236 ,p_time_end in varchar2 default null
237 ,p_absence_days in out nocopy number
238 ,p_absence_hours in out nocopy number
239 ,p_authorising_person_id in number default null
240 ,p_replacement_person_id in number default null
241 ,p_attribute_category in varchar2 default null
242 ,p_attribute1 in varchar2 default null
243 ,p_attribute2 in varchar2 default null
244 ,p_attribute3 in varchar2 default null
245 ,p_attribute4 in varchar2 default null
246 ,p_attribute5 in varchar2 default null
247 ,p_attribute6 in varchar2 default null
248 ,p_attribute7 in varchar2 default null
249 ,p_attribute8 in varchar2 default null
250 ,p_attribute9 in varchar2 default null
251 ,p_attribute10 in varchar2 default null
252 ,p_attribute11 in varchar2 default null
253 ,p_attribute12 in varchar2 default null
254 ,p_attribute13 in varchar2 default null
255 ,p_attribute14 in varchar2 default null
256 ,p_attribute15 in varchar2 default null
257 ,p_attribute16 in varchar2 default null
258 ,p_attribute17 in varchar2 default null
259 ,p_attribute18 in varchar2 default null
260 ,p_attribute19 in varchar2 default null
261 ,p_attribute20 in varchar2 default null
262 ,p_period_of_incapacity_id in number default null
263 ,p_ssp1_issued in varchar2 default null
264 ,p_maternity_id in number default null
265 ,p_sickness_start_date in date default null
266 ,p_sickness_end_date in date default null
267 ,p_pregnancy_related_illness in varchar2 default null
268 ,p_reason_for_notification_dela in varchar2 default null
269 ,p_accept_late_notification_fla in varchar2 default null
270 ,p_linked_absence_id in number default null
271 ,p_batch_id in number default null
272 ,p_create_element_entry in number default null
273 ,p_abs_information_category in varchar2 default null
274 ,p_abs_information1 in varchar2 default null
275 ,p_abs_information2 in varchar2 default null
276 ,p_abs_information3 in varchar2 default null
277 ,p_abs_information4 in varchar2 default null
278 ,p_abs_information5 in varchar2 default null
279 ,p_abs_information6 in varchar2 default null
280 ,p_abs_information7 in varchar2 default null
281 ,p_abs_information8 in varchar2 default null
282 ,p_abs_information9 in varchar2 default null
283 ,p_abs_information10 in varchar2 default null
284 ,p_abs_information11 in varchar2 default null
285 ,p_abs_information12 in varchar2 default null
286 ,p_abs_information13 in varchar2 default null
287 ,p_abs_information14 in varchar2 default null
288 ,p_abs_information15 in varchar2 default null
289 ,p_abs_information16 in varchar2 default null
290 ,p_abs_information17 in varchar2 default null
291 ,p_abs_information18 in varchar2 default null
292 ,p_abs_information19 in varchar2 default null
293 ,p_abs_information20 in varchar2 default null
294 ,p_abs_information21 in varchar2 default null
295 ,p_abs_information22 in varchar2 default null
296 ,p_abs_information23 in varchar2 default null
297 ,p_abs_information24 in varchar2 default null
298 ,p_abs_information25 in varchar2 default null
299 ,p_abs_information26 in varchar2 default null
300 ,p_abs_information27 in varchar2 default null
301 ,p_abs_information28 in varchar2 default null
302 ,p_abs_information29 in varchar2 default null
303 ,p_abs_information30 in varchar2 default null
304 ,p_absence_case_id in number default null
305 ,p_absence_attendance_id in out nocopy number
306 ,p_object_version_number out nocopy number
307 ,p_occurrence out nocopy number
308 ,p_return_status out nocopy varchar2
309 ) is
310 --
311 -- Variables for API Boolean parameters
312 l_validate boolean;
313 l_create_element_entry boolean;
314 l_dur_dys_less_warning boolean;
315 l_dur_hrs_less_warning boolean;
316 l_exceeds_pto_entit_warning boolean;
317 l_exceeds_run_total_warning boolean;
318 l_abs_overlap_warning boolean;
319 l_abs_day_after_warning boolean;
320 l_dur_overwritten_warning boolean;
321 --
322 -- Variables for IN/OUT parameters
323 l_absence_days number;
324 l_absence_hours number;
325
326 l_populate_sickness_dates boolean := false;
327 l_sickness_start_date date := p_sickness_start_date;
328 l_sickness_end_date date := p_sickness_end_date;
329 l_date_notification date := p_date_notification;
330 l_abs_information_category varchar2(25) := p_abs_information_category;
331
332 l_date_start date := p_date_start;
333 l_date_end date := p_date_end;
334 l_date_projected_start date := p_date_projected_start;
335 l_date_projected_end date := p_date_projected_end;
336 l_ssp1_issued varchar2(1) := p_ssp1_issued;
337 l_pregnancy_related_illness varchar2(1) := p_pregnancy_related_illness;
338 l_accept_late_notification_fla varchar2(1) := p_accept_late_notification_fla;
339 l_error_text varchar2(2000);
340 l_sqlerrm varchar2(2000);
341
342 --
343 -- Other variables
344 l_proc varchar2(72) := g_package ||'create_person_absence';
345 Begin
346 hr_utility.set_location(' Entering:' || l_proc,10);
347 --
348 -- Issue a savepoint
349 --
350 savepoint create_person_absence_swi;
351 --
352 -- Initialise Multiple Message Detection
353 --
354 hr_multi_message.enable_message_list;
355 --
356 -- Remember IN OUT parameter IN values
357 --
358 -- Bug 6347488 fix starts
359
360 if p_date_start is null then
361 p_absence_days := null;
362 p_absence_hours := null;
363 end if;
364
365 -- Bug 6347488 fix ends
366
367 l_absence_days := p_absence_days;
368 l_absence_hours := p_absence_hours;
369
370 --
371 -- Convert constant values to their corresponding boolean value
372 --
373 l_validate :=
374 hr_api.constant_to_boolean
375 (p_constant_value => p_validate);
376 l_create_element_entry :=
377 hr_api.constant_to_boolean
378 (p_constant_value => p_create_element_entry);
379 --
380 -- Register Surrogate ID or user key values
381 --
382 per_abs_ins.set_base_key_value
383 (p_absence_attendance_id => p_absence_attendance_id
384 );
385
386 l_populate_sickness_dates := is_gb_leg_and_category_s(p_absence_attendance_type_id , p_business_group_id);
387
388 IF l_populate_sickness_dates THEN
389 IF p_date_start IS NULL AND p_date_projected_start IS NOT NULL THEN
390 l_date_start := l_date_projected_start;
391 l_date_projected_start := NULL;
392 l_date_end := l_date_projected_end;
393 l_date_projected_end := NULL;
394 END IF;
395 IF l_date_start > SYSDATE THEN
396 fnd_message.set_name('SSP', 'SSP_35036_INV_NOTIF_DATE');
397 fnd_message.raise_error;
398 END IF;
399 l_sickness_start_date := l_date_start;
400 l_sickness_end_date := l_date_end;
401 l_date_notification := sysdate;
402 --9562057 Begin
403 -- l_abs_information_category := 'GB_PQP_OSP_OMP_PART_DAYS';
404 IF pqp_gb_osp_functions.exists_in_gap_lookup
405 (p_business_group_id=> p_business_group_id
406 ,p_lookup_code=> p_absence_attendance_type_id
407 ,p_effective_date=> sysdate)
408 THEN
409 -- Enable the context
410 -- fnd_message.debug('Enable the context');
411 l_abs_information_category := 'GB_PQP_OSP_OMP_PART_DAYS';
412 END IF;
413 --9562057 End
414 l_ssp1_issued := 'N';
415 l_pregnancy_related_illness := 'N';
416 l_accept_late_notification_fla := 'N';
417 END IF;
418 --
419 -- Call API
420 --
421 hr_person_absence_api.create_person_absence
422 (p_validate => l_validate
423 ,p_effective_date => p_effective_date
424 ,p_person_id => p_person_id
425 ,p_business_group_id => p_business_group_id
426 ,p_absence_attendance_type_id => p_absence_attendance_type_id
427 ,p_abs_attendance_reason_id => p_abs_attendance_reason_id
428 ,p_comments => p_comments
429 ,p_date_notification => l_date_notification
430 ,p_date_projected_start => l_date_projected_start
431 ,p_time_projected_start => p_time_projected_start
432 ,p_date_projected_end => l_date_projected_end
433 ,p_time_projected_end => p_time_projected_end
434 ,p_date_start => l_date_start
435 ,p_time_start => p_time_start
436 ,p_date_end => l_date_end
437 ,p_time_end => p_time_end
438 ,p_absence_days => p_absence_days
439 ,p_absence_hours => p_absence_hours
440 ,p_authorising_person_id => p_authorising_person_id
441 ,p_replacement_person_id => p_replacement_person_id
442 ,p_attribute_category => p_attribute_category
443 ,p_attribute1 => p_attribute1
444 ,p_attribute2 => p_attribute2
445 ,p_attribute3 => p_attribute3
446 ,p_attribute4 => p_attribute4
447 ,p_attribute5 => p_attribute5
448 ,p_attribute6 => p_attribute6
449 ,p_attribute7 => p_attribute7
450 ,p_attribute8 => p_attribute8
451 ,p_attribute9 => p_attribute9
452 ,p_attribute10 => p_attribute10
453 ,p_attribute11 => p_attribute11
454 ,p_attribute12 => p_attribute12
455 ,p_attribute13 => p_attribute13
456 ,p_attribute14 => p_attribute14
457 ,p_attribute15 => p_attribute15
458 ,p_attribute16 => p_attribute16
459 ,p_attribute17 => p_attribute17
460 ,p_attribute18 => p_attribute18
461 ,p_attribute19 => p_attribute19
462 ,p_attribute20 => p_attribute20
463 ,p_period_of_incapacity_id => p_period_of_incapacity_id
464 ,p_ssp1_issued => l_ssp1_issued
465 ,p_maternity_id => p_maternity_id
466 ,p_sickness_start_date => l_sickness_start_date
467 ,p_sickness_end_date => l_sickness_end_date
468 ,p_pregnancy_related_illness => l_pregnancy_related_illness
469 ,p_reason_for_notification_dela => p_reason_for_notification_dela
470 ,p_accept_late_notification_fla => l_accept_late_notification_fla
471 ,p_linked_absence_id => p_linked_absence_id
472 ,p_batch_id => p_batch_id
473 ,p_create_element_entry => l_create_element_entry
474 ,p_abs_information_category => l_abs_information_category
475 ,p_abs_information1 => p_abs_information1
476 ,p_abs_information2 => p_abs_information2
477 ,p_abs_information3 => p_abs_information3
478 ,p_abs_information4 => p_abs_information4
479 ,p_abs_information5 => p_abs_information5
480 ,p_abs_information6 => p_abs_information6
481 ,p_abs_information7 => p_abs_information7
482 ,p_abs_information8 => p_abs_information8
483 ,p_abs_information9 => p_abs_information9
484 ,p_abs_information10 => p_abs_information10
485 ,p_abs_information11 => p_abs_information11
486 ,p_abs_information12 => p_abs_information12
487 ,p_abs_information13 => p_abs_information13
488 ,p_abs_information14 => p_abs_information14
489 ,p_abs_information15 => p_abs_information15
490 ,p_abs_information16 => p_abs_information16
491 ,p_abs_information17 => p_abs_information17
492 ,p_abs_information18 => p_abs_information18
493 ,p_abs_information19 => p_abs_information19
494 ,p_abs_information20 => p_abs_information20
495 ,p_abs_information21 => p_abs_information21
496 ,p_abs_information22 => p_abs_information22
497 ,p_abs_information23 => p_abs_information23
498 ,p_abs_information24 => p_abs_information24
499 ,p_abs_information25 => p_abs_information25
500 ,p_abs_information26 => p_abs_information26
501 ,p_abs_information27 => p_abs_information27
502 ,p_abs_information28 => p_abs_information28
503 ,p_abs_information29 => p_abs_information29
504 ,p_abs_information30 => p_abs_information30
505 ,p_absence_attendance_id => p_absence_attendance_id
506 ,p_absence_case_id => p_absence_case_id
507 ,p_object_version_number => p_object_version_number
508 ,p_occurrence => p_occurrence
509 ,p_dur_dys_less_warning => l_dur_dys_less_warning
510 ,p_dur_hrs_less_warning => l_dur_hrs_less_warning
511 ,p_exceeds_pto_entit_warning => l_exceeds_pto_entit_warning
512 ,p_exceeds_run_total_warning => l_exceeds_run_total_warning
513 ,p_abs_overlap_warning => l_abs_overlap_warning
514 ,p_abs_day_after_warning => l_abs_day_after_warning
515 ,p_dur_overwritten_warning => l_dur_overwritten_warning
516 );
517 --
518 -- Convert API warning boolean parameter values to specific
519 -- messages and add them to Multiple Message List
520 --
521 if l_validate then
522 if l_dur_dys_less_warning then
523 hr_utility.set_message(800, 'HR_EMP_ABS_SHORT_DURATION');
524 hr_multi_message.add
525 (p_message_type => hr_multi_message.g_warning_msg
526 );
527 end if;
528 if l_dur_hrs_less_warning then
529 hr_utility.set_message(800,'HR_ABS_HOUR_LESS_DURATION');
530 hr_multi_message.add
531 (p_message_type => hr_multi_message.g_warning_msg
532 );
533 end if;
534 if l_exceeds_pto_entit_warning then
535 hr_utility.set_message(800, 'HR_LOA_EMP_NOT_ENTITLED');
536 hr_multi_message.add
537 (p_message_type => hr_multi_message.g_warning_msg
538 );
539 end if;
540 if l_exceeds_run_total_warning then
541 hr_utility.set_message(800, 'HR_LOA_DET_RUNNING_ZERO');
542 hr_multi_message.add
543 (p_message_type => hr_multi_message.g_warning_msg
544 );
545 end if;
546 if l_abs_overlap_warning then
547 hr_utility.set_message(800, 'HR_LOA_ABSENCE_OVERLAP');
548 hr_multi_message.add
549 (p_message_type => hr_multi_message.g_warning_msg
550 );
551 end if;
552 if l_abs_day_after_warning then
553 hr_utility.set_message(800, 'HR_LOA_DET_ABS_DAY_AFTER');
554 hr_multi_message.add
555 (p_message_type => hr_multi_message.g_warning_msg
556 );
557 end if;
558
559 if (l_dur_dys_less_warning OR
560 l_dur_hrs_less_warning OR
561 l_exceeds_pto_entit_warning OR
562 l_exceeds_run_total_warning OR
563 l_dur_dys_less_warning OR
564 l_abs_overlap_warning OR
565 l_abs_day_after_warning) then
566
567 hr_utility.set_location(l_proc, 40);
568
569 else
570 l_abs_overlap_warning := chk_overlap(p_person_id,p_business_group_id,nvl(p_date_start,p_date_projected_start),nvl(p_date_end,p_date_projected_end),nvl(p_time_start,p_time_projected_start),nvl(p_time_end,p_time_projected_end));
571
572 if l_abs_overlap_warning then
573 hr_utility.set_message(800, 'HR_LOA_ABSENCE_OVERLAP');
574 hr_multi_message.add(p_message_type => hr_multi_message.g_warning_msg);
575 end if;
576 end if;
577
578 end if;
579 --
580 -- We don't raise overwritten warning from SSHR
581 --
582 --if l_dur_overwritten_warning then
583 -- hr_utility.set_message(800, 'EDIT_HERE: MESSAGE_NAME ');
584 -- hr_multi_message.add
585 -- (p_message_type => hr_multi_message.g_warning_msg
586 -- );
587 --end if; --
588 -- Convert API non-warning boolean parameter values
589 --
590 --
591 -- Derive the API return status value based on whether
592 -- messages of any type exist in the Multiple Message List.
593 -- Also disable Multiple Message Detection.
594 --
595 p_return_status := hr_multi_message.get_return_status_disable;
596 hr_utility.set_location(' Leaving:' || l_proc,20);
597 --
598 exception
599 when hr_multi_message.error_message_exist then
600 --
601 -- Catch the Multiple Message List exception which
602 -- indicates API processing has been aborted because
603 -- at least one message exists in the list.
604 --
605 rollback to create_person_absence_swi;
606 --
607 -- Reset IN OUT parameters and set OUT parameters
608 --
609 p_absence_days := l_absence_days;
610 p_absence_hours := l_absence_hours;
611 p_absence_attendance_id := null;
612 p_object_version_number := null;
613 p_occurrence := null;
614 p_return_status := hr_multi_message.get_return_status_disable;
615 hr_utility.set_location(' Leaving:' || l_proc, 30);
616 when others then
617 --
618 -- When Multiple Message Detection is enabled catch
619 -- any Application specific or other unexpected
620 -- exceptions. Adding appropriate details to the
621 -- Multiple Message List. Otherwise re-raise the
622 -- error.
623 --
624 rollback to create_person_absence_swi;
625 if hr_multi_message.unexpected_error_add(l_proc) then
626 hr_utility.set_location(' Leaving:' || l_proc,40);
627 raise;
628 end if;
629 l_sqlerrm := sqlerrm;
630 l_error_text := hr_utility.get_message;
631 if l_error_text is null then
632 l_error_text := fnd_message.get;
633 end if;
634
635 if (((l_error_text is not null) OR (l_sqlerrm is not null)) and (p_validate = hr_api.g_false_num)) then
636 hr_utility.set_location(' Leaving:' || l_proc,45);
637 raise;
638 end if;
639 --
640 -- Reset IN OUT and set OUT parameters
641 --
642 p_absence_days := l_absence_days;
643 p_absence_hours := l_absence_hours;
644 p_absence_attendance_id := null;
645 p_object_version_number := null;
646 p_occurrence := null;
647 p_return_status := hr_multi_message.get_return_status_disable;
648 hr_utility.set_location(' Leaving:' || l_proc,50);
649 end create_person_absence;
650 -- ----------------------------------------------------------------------------
651 -- |-------------------------< update_person_absence >------------------------|
652 -- ----------------------------------------------------------------------------
653 PROCEDURE update_person_absence
654 (p_validate in number default hr_api.g_false_num
655 ,p_effective_date in date
656 ,p_absence_attendance_id in number
657 ,p_abs_attendance_reason_id in number default hr_api.g_number
658 ,p_comments in clob default hr_api.g_varchar2 --Bug#13362792
659 ,p_date_notification in date default hr_api.g_date
660 ,p_date_projected_start in date default hr_api.g_date
661 ,p_time_projected_start in varchar2 default hr_api.g_varchar2
662 ,p_date_projected_end in date default hr_api.g_date
663 ,p_time_projected_end in varchar2 default hr_api.g_varchar2
664 ,p_date_start in date default hr_api.g_date
665 ,p_time_start in varchar2 default hr_api.g_varchar2
666 ,p_date_end in date default hr_api.g_date
667 ,p_time_end in varchar2 default hr_api.g_varchar2
668 ,p_absence_days in out nocopy number
669 ,p_absence_hours in out nocopy number
670 ,p_authorising_person_id in number default hr_api.g_number
671 ,p_replacement_person_id in number default hr_api.g_number
672 ,p_attribute_category in varchar2 default hr_api.g_varchar2
673 ,p_attribute1 in varchar2 default hr_api.g_varchar2
674 ,p_attribute2 in varchar2 default hr_api.g_varchar2
675 ,p_attribute3 in varchar2 default hr_api.g_varchar2
676 ,p_attribute4 in varchar2 default hr_api.g_varchar2
677 ,p_attribute5 in varchar2 default hr_api.g_varchar2
678 ,p_attribute6 in varchar2 default hr_api.g_varchar2
679 ,p_attribute7 in varchar2 default hr_api.g_varchar2
680 ,p_attribute8 in varchar2 default hr_api.g_varchar2
681 ,p_attribute9 in varchar2 default hr_api.g_varchar2
682 ,p_attribute10 in varchar2 default hr_api.g_varchar2
683 ,p_attribute11 in varchar2 default hr_api.g_varchar2
684 ,p_attribute12 in varchar2 default hr_api.g_varchar2
685 ,p_attribute13 in varchar2 default hr_api.g_varchar2
686 ,p_attribute14 in varchar2 default hr_api.g_varchar2
687 ,p_attribute15 in varchar2 default hr_api.g_varchar2
688 ,p_attribute16 in varchar2 default hr_api.g_varchar2
689 ,p_attribute17 in varchar2 default hr_api.g_varchar2
690 ,p_attribute18 in varchar2 default hr_api.g_varchar2
691 ,p_attribute19 in varchar2 default hr_api.g_varchar2
692 ,p_attribute20 in varchar2 default hr_api.g_varchar2
693 ,p_period_of_incapacity_id in number default hr_api.g_number
694 ,p_ssp1_issued in varchar2 default hr_api.g_varchar2
695 ,p_maternity_id in number default hr_api.g_number
696 ,p_sickness_start_date in date default hr_api.g_date
697 ,p_sickness_end_date in date default hr_api.g_date
698 ,p_pregnancy_related_illness in varchar2 default hr_api.g_varchar2
699 ,p_reason_for_notification_dela in varchar2 default hr_api.g_varchar2
700 ,p_accept_late_notification_fla in varchar2 default hr_api.g_varchar2
701 ,p_linked_absence_id in number default hr_api.g_number
702 ,p_batch_id in number default hr_api.g_number
703 ,p_abs_information_category in varchar2 default hr_api.g_varchar2
704 ,p_abs_information1 in varchar2 default hr_api.g_varchar2
705 ,p_abs_information2 in varchar2 default hr_api.g_varchar2
706 ,p_abs_information3 in varchar2 default hr_api.g_varchar2
707 ,p_abs_information4 in varchar2 default hr_api.g_varchar2
708 ,p_abs_information5 in varchar2 default hr_api.g_varchar2
709 ,p_abs_information6 in varchar2 default hr_api.g_varchar2
710 ,p_abs_information7 in varchar2 default hr_api.g_varchar2
711 ,p_abs_information8 in varchar2 default hr_api.g_varchar2
712 ,p_abs_information9 in varchar2 default hr_api.g_varchar2
713 ,p_abs_information10 in varchar2 default hr_api.g_varchar2
714 ,p_abs_information11 in varchar2 default hr_api.g_varchar2
715 ,p_abs_information12 in varchar2 default hr_api.g_varchar2
716 ,p_abs_information13 in varchar2 default hr_api.g_varchar2
717 ,p_abs_information14 in varchar2 default hr_api.g_varchar2
718 ,p_abs_information15 in varchar2 default hr_api.g_varchar2
719 ,p_abs_information16 in varchar2 default hr_api.g_varchar2
720 ,p_abs_information17 in varchar2 default hr_api.g_varchar2
721 ,p_abs_information18 in varchar2 default hr_api.g_varchar2
722 ,p_abs_information19 in varchar2 default hr_api.g_varchar2
723 ,p_abs_information20 in varchar2 default hr_api.g_varchar2
724 ,p_abs_information21 in varchar2 default hr_api.g_varchar2
725 ,p_abs_information22 in varchar2 default hr_api.g_varchar2
726 ,p_abs_information23 in varchar2 default hr_api.g_varchar2
727 ,p_abs_information24 in varchar2 default hr_api.g_varchar2
728 ,p_abs_information25 in varchar2 default hr_api.g_varchar2
729 ,p_abs_information26 in varchar2 default hr_api.g_varchar2
730 ,p_abs_information27 in varchar2 default hr_api.g_varchar2
731 ,p_abs_information28 in varchar2 default hr_api.g_varchar2
732 ,p_abs_information29 in varchar2 default hr_api.g_varchar2
733 ,p_abs_information30 in varchar2 default hr_api.g_varchar2
734 ,p_absence_case_id in number default hr_api.g_number
735 ,p_object_version_number in out nocopy number
736 ,p_return_status out nocopy varchar2
737 ) is
738 --
739 -- Variables for API Boolean parameters
740 l_validate boolean;
741 l_dur_dys_less_warning boolean;
742 l_dur_hrs_less_warning boolean;
743 l_exceeds_pto_entit_warning boolean;
744 l_exceeds_run_total_warning boolean;
745 l_abs_overlap_warning boolean;
746 l_abs_day_after_warning boolean;
747 l_dur_overwritten_warning boolean;
748 l_del_element_entry_warning boolean;
749 --
750 -- Variables for IN/OUT parameters
751 l_absence_days number;
752 l_absence_hours number;
753 l_object_version_number number;
754
755 --7382975 begin
756 l_populate_sickness_dates boolean := false;
757 l_sickness_start_date date := p_sickness_start_date;
758 l_sickness_end_date date := p_sickness_end_date;
759 l_absence_attendance_type_id per_absence_attendances.absence_attendance_type_id%type;
760 l_business_group_id per_absence_attendances.business_group_id%type;
761
762 Cursor c_is_gb_leg is
763 select absence_attendance_type_id, business_group_id
764 from per_absence_attendances
765 where absence_attendance_id = p_absence_attendance_id;
766 --7382975 End
767
768 lv_PERSON_ID number;
769 lv_BUSINESS_GROUP_ID number;
770 lv_DATE_START Date;
771 lv_DATE_END Date;
772 lv_TIME_START VARCHAR2(200);
773 lv_TIME_END VARCHAR2(200);
774 lv_DATE_PROJECTED_START Date;
775 lv_DATE_PROJECTED_END Date;
776 lv_TIME_PROJECTED_START VARCHAR2(200);
777 lv_TIME_PROJECTED_END VARCHAR2(200);
778
779
780 --
781 -- Other variables
782 l_proc varchar2(72) := g_package ||'update_person_absence';
783 l_error_text varchar2(2000);
784 l_sqlerrm varchar2(2000);
785 l_date_start Date;
786
787 Begin
788 hr_utility.set_location(' Entering:' || l_proc,10);
789 --
790 -- Issue a savepoint
791 --
792 savepoint update_person_absence_swi;
793 --
794 -- Initialise Multiple Message Detection
795 --
796 hr_multi_message.enable_message_list;
797 --
798
799 -- Bug 6347488,8671547 fix starts
800
801 if p_date_start is null or p_date_start = hr_api.g_date then
802
803 select date_start into l_date_start from per_absence_attendances where
804 absence_attendance_id = p_absence_attendance_id;
805
806 if(l_date_start) is null then
807 p_absence_days := null;
808 p_absence_hours := null;
809 end if;
810 end if;
811
812 -- Bug 6347488,8671547 fix ends
813
814 -- Remember IN OUT parameter IN values
815 --
816 l_absence_days := p_absence_days;
817 l_absence_hours := p_absence_hours;
818 l_object_version_number := p_object_version_number;
819 --
820 -- Convert constant values to their corresponding boolean value
821 --
822 l_validate :=
823 hr_api.constant_to_boolean
824 (p_constant_value => p_validate);
825 --
826 -- Register Surrogate ID or user key values
827 --
828 --7382975 begin
829 open c_is_gb_leg;
830 fetch c_is_gb_leg into l_absence_attendance_type_id, l_business_group_id;
831
832 IF c_is_gb_leg%FOUND THEN
833 l_populate_sickness_dates := is_gb_leg_and_category_s(l_absence_attendance_type_id , l_business_group_id);
834
835 IF l_populate_sickness_dates THEN
836 IF (p_date_start <> hr_api.g_date) THEN
837 IF p_date_start > SYSDATE THEN
838 fnd_message.set_name('SSP', 'SSP_35036_INV_NOTIF_DATE');
839 fnd_message.raise_error;
840 END IF;
841 l_sickness_start_date := p_date_start;
842 END IF;
843 IF (p_date_end <> hr_api.g_date) THEN
844 l_sickness_end_date := p_date_end;
845 END IF;
846 END IF;
847 END IF;
848
849 close c_is_gb_leg;
850 --7382975 end
851
852
853
854 --
855 -- Call API
856 --
857 hr_person_absence_api.update_person_absence
858 (p_validate => l_validate
859 ,p_effective_date => p_effective_date
860 ,p_absence_attendance_id => p_absence_attendance_id
861 ,p_abs_attendance_reason_id => p_abs_attendance_reason_id
862 ,p_comments => p_comments
863 ,p_date_notification => p_date_notification
864 ,p_date_projected_start => p_date_projected_start
865 ,p_time_projected_start => p_time_projected_start
866 ,p_date_projected_end => p_date_projected_end
867 ,p_time_projected_end => p_time_projected_end
868 ,p_date_start => p_date_start
869 ,p_time_start => p_time_start
870 ,p_date_end => p_date_end
871 ,p_time_end => p_time_end
872 ,p_absence_days => p_absence_days
873 ,p_absence_hours => p_absence_hours
874 ,p_authorising_person_id => p_authorising_person_id
875 ,p_replacement_person_id => p_replacement_person_id
876 ,p_attribute_category => p_attribute_category
877 ,p_attribute1 => p_attribute1
878 ,p_attribute2 => p_attribute2
879 ,p_attribute3 => p_attribute3
880 ,p_attribute4 => p_attribute4
881 ,p_attribute5 => p_attribute5
882 ,p_attribute6 => p_attribute6
883 ,p_attribute7 => p_attribute7
884 ,p_attribute8 => p_attribute8
885 ,p_attribute9 => p_attribute9
886 ,p_attribute10 => p_attribute10
887 ,p_attribute11 => p_attribute11
888 ,p_attribute12 => p_attribute12
889 ,p_attribute13 => p_attribute13
890 ,p_attribute14 => p_attribute14
891 ,p_attribute15 => p_attribute15
892 ,p_attribute16 => p_attribute16
893 ,p_attribute17 => p_attribute17
894 ,p_attribute18 => p_attribute18
895 ,p_attribute19 => p_attribute19
896 ,p_attribute20 => p_attribute20
897 ,p_period_of_incapacity_id => p_period_of_incapacity_id
898 ,p_ssp1_issued => p_ssp1_issued
899 ,p_maternity_id => p_maternity_id
900 ,p_sickness_start_date => l_sickness_start_date --7382975
901 ,p_sickness_end_date => l_sickness_end_date --7382975
902 ,p_pregnancy_related_illness => p_pregnancy_related_illness
903 ,p_reason_for_notification_dela => p_reason_for_notification_dela
904 ,p_accept_late_notification_fla => p_accept_late_notification_fla
905 ,p_linked_absence_id => p_linked_absence_id
906 ,p_batch_id => p_batch_id
907 ,p_abs_information_category => p_abs_information_category
908 ,p_abs_information1 => p_abs_information1
909 ,p_abs_information2 => p_abs_information2
910 ,p_abs_information3 => p_abs_information3
911 ,p_abs_information4 => p_abs_information4
912 ,p_abs_information5 => p_abs_information5
913 ,p_abs_information6 => p_abs_information6
914 ,p_abs_information7 => p_abs_information7
915 ,p_abs_information8 => p_abs_information8
916 ,p_abs_information9 => p_abs_information9
917 ,p_abs_information10 => p_abs_information10
918 ,p_abs_information11 => p_abs_information11
919 ,p_abs_information12 => p_abs_information12
920 ,p_abs_information13 => p_abs_information13
921 ,p_abs_information14 => p_abs_information14
922 ,p_abs_information15 => p_abs_information15
923 ,p_abs_information16 => p_abs_information16
924 ,p_abs_information17 => p_abs_information17
925 ,p_abs_information18 => p_abs_information18
926 ,p_abs_information19 => p_abs_information19
927 ,p_abs_information20 => p_abs_information20
928 ,p_abs_information21 => p_abs_information21
929 ,p_abs_information22 => p_abs_information22
930 ,p_abs_information23 => p_abs_information23
931 ,p_abs_information24 => p_abs_information24
932 ,p_abs_information25 => p_abs_information25
933 ,p_abs_information26 => p_abs_information26
934 ,p_abs_information27 => p_abs_information27
935 ,p_abs_information28 => p_abs_information28
936 ,p_abs_information29 => p_abs_information29
937 ,p_abs_information30 => p_abs_information30
938 ,p_absence_case_id => p_absence_case_id
939 ,p_object_version_number => p_object_version_number
940 ,p_dur_dys_less_warning => l_dur_dys_less_warning
941 ,p_dur_hrs_less_warning => l_dur_hrs_less_warning
942 ,p_exceeds_pto_entit_warning => l_exceeds_pto_entit_warning
943 ,p_exceeds_run_total_warning => l_exceeds_run_total_warning
944 ,p_abs_overlap_warning => l_abs_overlap_warning
945 ,p_abs_day_after_warning => l_abs_day_after_warning
946 ,p_dur_overwritten_warning => l_dur_overwritten_warning
947 ,p_del_element_entry_warning => l_del_element_entry_warning
948 );
949 --
950 -- Convert API warning boolean parameter values to specific
951 -- messages and add them to Multiple Message List
952 --
953 if l_validate then
954 if l_dur_dys_less_warning then
955 hr_utility.set_message(800, 'HR_EMP_ABS_SHORT_DURATION');
956 hr_multi_message.add
957 (p_message_type => hr_multi_message.g_warning_msg
958 );
959 end if;
960 if l_dur_hrs_less_warning then
961 hr_utility.set_message(800, 'HR_ABS_HOUR_LESS_DURATION');
962 hr_multi_message.add
963 (p_message_type => hr_multi_message.g_warning_msg
964 );
965 end if;
966 if l_exceeds_pto_entit_warning then
967 hr_utility.set_message(800, 'HR_LOA_EMP_NOT_ENTITLED');
968 hr_multi_message.add
969 (p_message_type => hr_multi_message.g_warning_msg
970 );
971 end if;
972 if l_exceeds_run_total_warning then
973 hr_utility.set_message(800, 'HR_LOA_DET_RUNNING_ZERO');
974 hr_multi_message.add
975 (p_message_type => hr_multi_message.g_warning_msg
976 );
977 end if;
978 if l_abs_overlap_warning then
979 hr_utility.set_message(800, 'HR_LOA_ABSENCE_OVERLAP');
980 hr_multi_message.add
981 (p_message_type => hr_multi_message.g_warning_msg
982 );
983 end if;
984 if l_abs_day_after_warning then
985 hr_utility.set_message(800, 'HR_LOA_DET_ABS_DAY_AFTER');
986 hr_multi_message.add
987 (p_message_type => hr_multi_message.g_warning_msg
988 );
989 end if;
990 --
991 -- We don't raise overwritten warning from SSHR
992 --
993 --if l_dur_overwritten_warning then
994 -- hr_utility.set_message(800, 'EDIT_HERE: MESSAGE_NAME ');
995 -- hr_multi_message.add
996 -- (p_message_type => hr_multi_message.g_warning_msg
997 -- );
998 --end if; --
999 if l_del_element_entry_warning then
1000 fnd_message.set_name('EDIT HERE: APP_CODE', 'EDIT_HERE: MESSAGE_NAME ');
1001 hr_multi_message.add
1002 (p_message_type => hr_multi_message.g_warning_msg
1003 );
1004 end if;
1005 if (l_dur_dys_less_warning OR
1006 l_dur_hrs_less_warning OR
1007 l_exceeds_pto_entit_warning OR
1008 l_exceeds_run_total_warning OR
1009 l_abs_overlap_warning OR
1010 l_abs_day_after_warning OR
1011 l_del_element_entry_warning) then
1012
1013 hr_utility.set_location(l_proc, 40);
1014
1015 else
1016
1017 begin
1018
1019 select PERSON_ID,BUSINESS_GROUP_ID,DATE_START,DATE_END,TIME_START,TIME_END,DATE_PROJECTED_START,DATE_PROJECTED_END,TIME_PROJECTED_START,TIME_PROJECTED_END
1020 into
1021 lv_PERSON_ID,lv_BUSINESS_GROUP_ID,lv_DATE_START,lv_DATE_END,lv_TIME_START,lv_TIME_END,lv_DATE_PROJECTED_START,lv_DATE_PROJECTED_END,lv_TIME_PROJECTED_START,lv_TIME_PROJECTED_END
1022 from per_absence_attendances
1023 where ABSENCE_ATTENDANCE_ID = p_absence_attendance_id;
1024 end;
1025
1026 l_abs_overlap_warning := chk_overlap(lv_PERSON_ID,lv_BUSINESS_GROUP_ID,nvl(nvl(p_date_start,p_date_projected_start),nvl(lv_DATE_START,lv_DATE_PROJECTED_START)),nvl(nvl(p_date_end,p_date_projected_end),
1027 nvl(lv_DATE_END,lv_DATE_PROJECTED_END)),nvl(nvl(p_time_start,p_time_projected_start),nvl(lv_TIME_START,lv_TIME_PROJECTED_START)),nvl(nvl(p_time_end,p_time_projected_end),nvl(lv_TIME_END,lv_TIME_PROJECTED_END)));
1028
1029
1030
1031 if l_abs_overlap_warning then
1032 hr_utility.set_message(800, 'HR_LOA_ABSENCE_OVERLAP');
1033 hr_multi_message.add(p_message_type => hr_multi_message.g_warning_msg);
1034 end if;
1035 end if;
1036
1037 end if; --
1038 -- Convert API non-warning boolean parameter values
1039 --
1040 --
1041 -- Derive the API return status value based on whether
1042 -- messages of any type exist in the Multiple Message List.
1043 -- Also disable Multiple Message Detection.
1044 --
1045 p_return_status := hr_multi_message.get_return_status_disable;
1046 hr_utility.set_location(' Leaving:' || l_proc,20);
1047 --
1048 exception
1049 when hr_multi_message.error_message_exist then
1050 --
1051 -- Catch the Multiple Message List exception which
1052 -- indicates API processing has been aborted because
1053 -- at least one message exists in the list.
1054 --
1055 rollback to update_person_absence_swi;
1056 --
1057 -- Reset IN OUT parameters and set OUT parameters
1058 --
1059 p_absence_days := l_absence_days;
1060 p_absence_hours := l_absence_hours;
1061 p_object_version_number := l_object_version_number;
1062 p_return_status := hr_multi_message.get_return_status_disable;
1063 hr_utility.set_location(' Leaving:' || l_proc, 30);
1064 when others then
1065 --
1066 -- When Multiple Message Detection is enabled catch
1067 -- any Application specific or other unexpected
1068 -- exceptions. Adding appropriate details to the
1069 -- Multiple Message List. Otherwise re-raise the
1070 -- error.
1071 --
1072 rollback to update_person_absence_swi;
1073 if hr_multi_message.unexpected_error_add(l_proc) then
1074 hr_utility.set_location(' Leaving:' || l_proc,40);
1075 raise;
1076 end if;
1077 l_sqlerrm := sqlerrm;
1078 l_error_text := hr_utility.get_message;
1079 if l_error_text is null then
1080 l_error_text := fnd_message.get;
1081 end if;
1082
1083 if (((l_error_text is not null) OR (l_sqlerrm is not null)) and (p_validate = hr_api.g_false_num)) then
1084 hr_utility.set_location(' Leaving:' || l_proc,45);
1085 raise;
1086 end if;
1087
1088 --
1089 -- Reset IN OUT and set OUT parameters
1090 --
1091 p_absence_days := l_absence_days;
1092 p_absence_hours := l_absence_hours;
1093 p_object_version_number := l_object_version_number;
1094 p_return_status := hr_multi_message.get_return_status_disable;
1095 hr_utility.set_location(' Leaving:' || l_proc,50);
1096 end update_person_absence;
1097 -- ----------------------------------------------------------------------------
1098 -- |-------------------------< delete_person_absence >------------------------|
1099 -- ----------------------------------------------------------------------------
1100 PROCEDURE delete_person_absence
1101 (p_validate in number default hr_api.g_false_num
1102 ,p_absence_attendance_id in number
1103 ,p_object_version_number in number
1104 ,p_return_status out nocopy varchar2
1105 ) is
1106 --
1107 -- Variables for API Boolean parameters
1108 l_validate boolean;
1109 --
1110 -- Variables for IN/OUT parameters
1111 --
1112 -- Other variables
1113 l_proc varchar2(72) := g_package ||'delete_person_absence';
1114 Begin
1115
1116
1117 hr_utility.set_location(' Entering:' || l_proc,10);
1118 --
1119 -- Issue a savepoint
1120 --
1121 savepoint delete_person_absence_swi;
1122 --
1123 -- Initialise Multiple Message Detection
1124 --
1125 hr_multi_message.enable_message_list;
1126 --
1127 -- Remember IN OUT parameter IN values
1128 --
1129 --
1130 -- Convert constant values to their corresponding boolean value
1131 --
1132 l_validate :=
1133 hr_api.constant_to_boolean
1134 (p_constant_value => p_validate);
1135 --
1136 -- Register Surrogate ID or user key values
1137 --
1138 --
1139 -- Call API
1140 --
1141 hr_person_absence_api.delete_person_absence
1142 (p_validate => l_validate
1143 ,p_absence_attendance_id => p_absence_attendance_id
1144 ,p_object_version_number => p_object_version_number
1145 );
1146 --
1147 -- Convert API warning boolean parameter values to specific
1148 -- messages and add them to Multiple Message List
1149 --
1150 --
1151 -- Convert API non-warning boolean parameter values
1152 --
1153 --
1154 -- Derive the API return status value based on whether
1155 -- messages of any type exist in the Multiple Message List.
1156 -- Also disable Multiple Message Detection.
1157 --
1158 p_return_status := hr_multi_message.get_return_status_disable;
1159 hr_utility.set_location(' Leaving:' || l_proc,20);
1160 --
1161 exception
1162 when hr_multi_message.error_message_exist then
1163 --
1164 -- Catch the Multiple Message List exception which
1165 -- indicates API processing has been aborted because
1166 -- at least one message exists in the list.
1167 --
1168 rollback to delete_person_absence_swi;
1169 --
1170 -- Reset IN OUT parameters and set OUT parameters
1171 --
1172 p_return_status := hr_multi_message.get_return_status_disable;
1173 hr_utility.set_location(' Leaving:' || l_proc, 30);
1174 when others then
1175 --
1176 -- When Multiple Message Detection is enabled catch
1177 -- any Application specific or other unexpected
1178 -- exceptions. Adding appropriate details to the
1179 -- Multiple Message List. Otherwise re-raise the
1180 -- error.
1181 --
1182 rollback to delete_person_absence_swi;
1183 if hr_multi_message.unexpected_error_add(l_proc) then
1184 hr_utility.set_location(' Leaving:' || l_proc,40);
1185 raise;
1186 end if;
1187 --
1188 -- Reset IN OUT and set OUT parameters
1189 --
1190 p_return_status := hr_multi_message.get_return_status_disable;
1191 hr_utility.set_location(' Leaving:' || l_proc,50);
1192 end delete_person_absence;
1193
1194
1195 -- ----------------------------------------------------------------------------
1196 -- |--------------------------< update_attachment >----------------------------|
1197 -- ----------------------------------------------------------------------------
1198 --
1199 procedure update_attachment
1200 (p_entity_name in varchar2 default null
1201 ,p_pk1_value in varchar2 default null
1202 ,p_rowid in varchar2 ) is
1203
1204
1205
1206 l_proc varchar2(72) := g_package ||'update_attachment';
1207 l_rowid varchar2(50);
1208 l_language varchar2(30) ;
1209 data_error exception;
1210 --
1211
1212 cursor csr_get_attached_doc is
1213 select *
1214 from fnd_attached_documents
1215 where rowid = p_rowid;
1216 --
1217 cursor csr_get_doc(csr_p_document_id in number) is
1218 select *
1219 from fnd_documents
1220 where document_id = csr_p_document_id;
1221 --
1222 cursor csr_get_doc_tl (csr_p_lang in varchar2
1223 ,csr_p_document_id in number) is
1224 select *
1225 from fnd_documents_tl
1226 where document_id = csr_p_document_id
1227 and language = csr_p_lang;
1228 --
1229 l_attached_doc_pre_upd csr_get_attached_doc%rowtype;
1230 l_doc_pre_upd csr_get_doc%rowtype;
1231 l_doc_tl_pre_upd csr_get_doc_tl%rowtype;
1232 --
1233 --
1234 Begin
1235 hr_utility.set_location(' Entering:' || l_proc,10);
1236 --
1237 -- Get language
1238 select userenv('LANG') into l_language from dual;
1239 --
1240 -- Get the before update nullable fields which are not used by the
1241 -- Web page to ensure the values are propagated.
1242 Open csr_get_attached_doc;
1243 fetch csr_get_attached_doc into l_attached_doc_pre_upd;
1244 IF csr_get_attached_doc%NOTFOUND THEN
1245 close csr_get_attached_doc;
1246 raise data_error;
1247 END IF;
1248
1249 Open csr_get_doc(l_attached_doc_pre_upd.document_id);
1250 fetch csr_get_doc into l_doc_pre_upd;
1251 IF csr_get_doc%NOTFOUND then
1252 close csr_get_doc;
1253 raise data_error;
1254 END IF;
1255
1256 Open csr_get_doc_tl (csr_p_lang => l_language
1257 ,csr_p_document_id => l_attached_doc_pre_upd.document_id);
1258 fetch csr_get_doc_tl into l_doc_tl_pre_upd;
1259 IF csr_get_doc_tl%NOTFOUND then
1260 close csr_get_doc_tl;
1261 raise data_error;
1262 END IF;
1263
1264 hr_utility.set_location(' before fnd_attached_documents_pkg.lock_row :' || l_proc,20);
1265 -- Now, lock the rows.
1266 fnd_attached_documents_pkg.lock_row
1267 (x_rowid => p_rowid
1268 ,x_attached_document_id =>
1269 l_attached_doc_pre_upd.attached_document_id
1270 ,x_document_id => l_doc_pre_upd.document_id
1271 ,x_seq_num => l_attached_doc_pre_upd.seq_num
1272 ,x_entity_name => l_attached_doc_pre_upd.entity_name
1273 ,x_column1 => l_attached_doc_pre_upd.column1
1274 ,x_pk1_value => l_attached_doc_pre_upd.pk1_value
1275 ,x_pk2_value => l_attached_doc_pre_upd.pk2_value
1276 ,x_pk3_value => l_attached_doc_pre_upd.pk3_value
1277 ,x_pk4_value => l_attached_doc_pre_upd.pk4_value
1278 ,x_pk5_value => l_attached_doc_pre_upd.pk5_value
1279 ,x_automatically_added_flag =>
1280 l_attached_doc_pre_upd.automatically_added_flag
1281 ,x_attribute_category =>
1282 l_attached_doc_pre_upd.attribute_category
1283 ,x_attribute1 => l_attached_doc_pre_upd.attribute1
1284 ,x_attribute2 => l_attached_doc_pre_upd.attribute2
1285 ,x_attribute3 => l_attached_doc_pre_upd.attribute3
1286 ,x_attribute4 => l_attached_doc_pre_upd.attribute4
1287 ,x_attribute5 => l_attached_doc_pre_upd.attribute5
1288 ,x_attribute6 => l_attached_doc_pre_upd.attribute6
1289 ,x_attribute7 => l_attached_doc_pre_upd.attribute7
1290 ,x_attribute8 => l_attached_doc_pre_upd.attribute8
1291 ,x_attribute9 => l_attached_doc_pre_upd.attribute9
1292 ,x_attribute10 => l_attached_doc_pre_upd.attribute10
1293 ,x_attribute11 => l_attached_doc_pre_upd.attribute11
1294 ,x_attribute12 => l_attached_doc_pre_upd.attribute12
1295 ,x_attribute13 => l_attached_doc_pre_upd.attribute13
1296 ,x_attribute14 => l_attached_doc_pre_upd.attribute14
1297 ,x_attribute15 => l_attached_doc_pre_upd.attribute15
1298 ,x_datatype_id => l_doc_pre_upd.datatype_id
1299 ,x_category_id => l_doc_pre_upd.category_id
1300 ,x_security_type => l_doc_pre_upd.security_type
1301 ,x_security_id => l_doc_pre_upd.security_id
1302 ,x_publish_flag => l_doc_pre_upd.publish_flag
1303 ,x_image_type => l_doc_pre_upd.image_type
1304 ,x_storage_type => l_doc_pre_upd.storage_type
1305 ,x_usage_type => l_doc_pre_upd.usage_type
1306 ,x_start_date_active => l_doc_pre_upd.start_date_active
1307 ,x_end_date_active => l_doc_pre_upd.end_date_active
1308 ,x_language => l_doc_tl_pre_upd.language
1309 ,x_description => l_doc_tl_pre_upd.description
1310 ,x_file_name => l_doc_pre_upd.file_name
1311 ,x_media_id => l_doc_pre_upd.media_id
1312 ,x_doc_attribute_category =>
1313 l_doc_tl_pre_upd.doc_attribute_category
1314 ,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
1315 ,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
1316 ,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
1317 ,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
1318 ,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
1319 ,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
1320 ,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
1321 ,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
1322 ,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
1323 ,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
1324 ,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
1325 ,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
1326 ,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
1327 ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
1328 ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15
1329 ,x_url => l_doc_pre_upd.url
1330 ,x_title => l_doc_tl_pre_upd.title
1331 );
1332
1333
1334 -- Update document to fnd_attached_documents, fnd_documents,
1335 -- fnd_documents_tl
1336 --
1337 hr_utility.set_location(' before fnd_attached_documents_pkg.update_row :' || l_proc,30);
1338 fnd_attached_documents_pkg.update_row
1339 (x_rowid => p_rowid
1340 ,x_attached_document_id =>
1341 l_attached_doc_pre_upd.attached_document_id
1342 ,x_document_id => l_doc_pre_upd.document_id
1343 ,x_last_update_date => trunc(sysdate)
1344 ,x_last_updated_by => l_attached_doc_pre_upd.last_updated_by
1345 ,x_seq_num => l_attached_doc_pre_upd.seq_num
1346 ,x_entity_name => p_entity_name
1347 ,x_column1 => l_attached_doc_pre_upd.column1
1348 ,x_pk1_value => p_pk1_value
1349 ,x_pk2_value => l_attached_doc_pre_upd.pk2_value
1350 ,x_pk3_value => l_attached_doc_pre_upd.pk3_value
1351 ,x_pk4_value => l_attached_doc_pre_upd.pk4_value
1352 ,x_pk5_value => l_attached_doc_pre_upd.pk5_value
1353 ,x_automatically_added_flag =>
1354 l_attached_doc_pre_upd.automatically_added_flag
1355 ,x_attribute_category =>
1356 l_attached_doc_pre_upd.attribute_category
1357 ,x_attribute1 => l_attached_doc_pre_upd.attribute1
1358 ,x_attribute2 => l_attached_doc_pre_upd.attribute2
1359 ,x_attribute3 => l_attached_doc_pre_upd.attribute3
1360 ,x_attribute4 => l_attached_doc_pre_upd.attribute4
1361 ,x_attribute5 => l_attached_doc_pre_upd.attribute5
1362 ,x_attribute6 => l_attached_doc_pre_upd.attribute6
1363 ,x_attribute7 => l_attached_doc_pre_upd.attribute7
1364 ,x_attribute8 => l_attached_doc_pre_upd.attribute8
1365 ,x_attribute9 => l_attached_doc_pre_upd.attribute9
1366 ,x_attribute10 => l_attached_doc_pre_upd.attribute10
1367 ,x_attribute11 => l_attached_doc_pre_upd.attribute11
1368 ,x_attribute12 => l_attached_doc_pre_upd.attribute12
1369 ,x_attribute13 => l_attached_doc_pre_upd.attribute13
1370 ,x_attribute14 => l_attached_doc_pre_upd.attribute14
1371 ,x_attribute15 => l_attached_doc_pre_upd.attribute15
1372 /* columns necessary for creating a document on the fly */
1373 ,x_datatype_id => l_doc_pre_upd.datatype_id
1374 ,x_category_id => l_doc_pre_upd.category_id
1375 ,x_security_type => l_doc_pre_upd.security_type
1376 ,x_security_id => l_doc_pre_upd.security_id
1377 ,x_publish_flag => l_doc_pre_upd.publish_flag
1378 ,x_image_type => l_doc_pre_upd.image_type
1379 ,x_storage_type => l_doc_pre_upd.storage_type
1380 ,x_usage_type => l_doc_pre_upd.usage_type
1381 ,x_start_date_active => trunc(sysdate)
1382 ,x_end_date_active => l_doc_pre_upd.end_date_active
1383 ,x_language => l_language
1384 ,x_description => l_doc_tl_pre_upd.description
1385 ,x_file_name => l_doc_pre_upd.file_name
1386 ,x_media_id => l_doc_pre_upd.media_id
1387 ,x_doc_attribute_category =>
1388 l_doc_tl_pre_upd.doc_attribute_category
1389 ,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
1390 ,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
1391 ,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
1392 ,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
1393 ,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
1394 ,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
1395 ,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
1396 ,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
1397 ,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
1398 ,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
1399 ,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
1400 ,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
1401 ,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
1402 ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
1403 ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15
1404 ,x_url => l_doc_pre_upd.url
1405 ,x_title => l_doc_tl_pre_upd.title
1406 );
1407
1408 hr_utility.set_location(' after fnd_attached_documents_pkg.update_row :' || l_proc,40);
1409 hr_utility.set_location(' Leaving:' || l_proc,50);
1410
1411 EXCEPTION
1412 when others then
1413 hr_utility.set_location(' Error in :' || l_proc,60);
1414 raise;
1415 --
1416 End update_attachment;
1417
1418
1419 procedure merge_attachments(p_transaction_id in number,
1420 p_absence_attendance_id in number,
1421 p_return_status in out nocopy varchar2)
1422 is
1423 -- Other variables
1424 l_proc varchar2(72) := g_package ||'merge_attachments';
1425 l_rowid varchar2(50);
1426 lv_pk1_value varchar2(72) := p_absence_attendance_id||'_'||p_transaction_id ;
1427 data_error exception;
1428 lv_entity_name constant varchar2(30) := 'PER_ABSENCE_ATTENDANCES';
1429
1430 cursor csr_get_attached_doc is
1431 select *
1432 from fnd_attached_documents
1433 where entity_name=lv_entity_name
1434 and pk1_value=lv_pk1_value;
1435
1436 CURSOR C (X_attached_document_id in number) IS
1437 SELECT rowid
1438 FROM fnd_attached_documents
1439 WHERE attached_document_id = X_attached_document_id;
1440 --
1441 Begin
1442 hr_utility.set_location(' Entering:' || l_proc,10);
1443 --
1444 -- Issue a savepoint
1445 --
1446 savepoint attachments_person_absence_swi;
1447 --
1448 -- Initialise Multiple Message Detection
1449 --
1450 hr_multi_message.enable_message_list;
1451
1452 for attached_documents_rec in csr_get_attached_doc loop
1453 OPEN C (attached_documents_rec.attached_document_id);
1454 FETCH C INTO l_rowid;
1455 if (C%NOTFOUND) then
1456 CLOSE C;
1457 RAISE NO_DATA_FOUND;
1458 end if;
1459 CLOSE C;
1460 -- call the update_attachement for each attached doc
1461 update_attachment
1462 (p_entity_name=>lv_entity_name
1463 ,p_pk1_value=> p_absence_attendance_id
1464 ,p_rowid=>l_rowid);
1465
1466 end loop;
1467
1468 p_return_status := hr_multi_message.get_return_status_disable;
1469
1470 hr_utility.set_location(' Leaving:' || l_proc,20);
1471 --
1472 exception
1473 when others then
1474 --
1475 -- When Multiple Message Detection is enabled catch
1476 -- any Application specific or other unexpected
1477 -- exceptions. Adding appropriate details to the
1478 -- Multiple Message List. Otherwise re-raise the
1479 -- error.
1480 --
1481 rollback to attachments_person_absence_swi;
1482 if hr_multi_message.unexpected_error_add(l_proc) then
1483 hr_utility.set_location(' Leaving:' || l_proc,40);
1484 raise;
1485 end if;
1486 --
1487 -- Reset IN OUT and set OUT parameters
1488 p_return_status := hr_multi_message.get_return_status_disable;
1489
1490 hr_utility.set_location(' Leaving:' || l_proc,50);
1491 end merge_attachments;
1492
1493
1494 -- ----------------------------------------------------------------------------
1495 -- |---------------------------< process_api >--------------------------------|
1496 -- ----------------------------------------------------------------------------
1497
1498 Procedure process_api
1499 ( p_document in CLOB
1500 ,p_return_status out nocopy VARCHAR2
1501 ,p_validate in number default hr_api.g_false_num
1502 ,p_effective_date in date default null
1503 )
1504 IS
1505 l_postState VARCHAR2(2);
1506 l_return_status VARCHAR2(1);
1507 l_commitElement xmldom.DOMElement;
1508 l_object_version_number number;
1509 l_parser xmlparser.Parser;
1510 l_CommitNode xmldom.DOMNode;
1511 l_proc varchar2(72) := g_package || 'process_api';
1512
1513 l_absence_days number;
1514 l_absence_hours number;
1515 lv_absence_days number;
1516 lv_absence_hours number;
1517 l_absence_attendance_id number;
1518 l_occurrence number;
1519 lv_action varchar2(30);
1520
1521 Cursor c_get_dur(p_absence_attendance_id number) is
1522 select absence_days, absence_hours
1523 from per_absence_attendances
1524 where absence_attendance_id = p_absence_attendance_id;
1525
1526 --Fix for Bug 14560350 start
1527 l_msg_count number;
1528 lw_msg_count number;
1529 msg_details varchar2(4000);
1530 --Fix for Bug 14560350 end
1531
1532
1533 BEGIN
1534
1535 hr_utility.set_location(' Entering:' || l_proc,10);
1536 hr_utility.set_location(' CLOB --> xmldom.DOMNode:' || l_proc,15);
1537 savepoint absence_process_api;
1538 l_parser := xmlparser.newParser;
1539 xmlparser.ParseCLOB(l_parser,p_document);
1540 l_CommitNode := xmldom.makeNode(xmldom.getDocumentElement(xmlparser.getDocument(l_parser)));
1541
1542 hr_utility.set_location('Extracting the PostState:' || l_proc,20);
1543
1544 l_commitElement := xmldom.makeElement(l_CommitNode);
1545 l_postState := xmldom.getAttribute(l_commitElement, 'PS');
1546 l_object_version_number := hr_transaction_swi.getNumberValue(l_CommitNode,'ObjectVersionNumber');
1547
1548 -- overiding for poststate
1549 -- CancelMode
1550 if(l_postState = '2') then
1551 -- Check if the transaction is for delete
1552 begin
1553 lv_action:= hr_xml_util.get_node_value(hr_transaction_swi.g_txn_ctx.TRANSACTION_ID,'AbsenceAction','Transaction/TransCtx/CNode',
1554 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1555 end;
1556 if(lv_action='CancelMode') then
1557 -- reset the poststate
1558 l_postState:= '3';
1559 end if;
1560 end if;
1561
1562
1563 if l_postState = '0' then
1564
1565 l_absence_days := hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceDays',null);
1566 l_absence_hours := hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceHours',null);
1567 l_absence_attendance_id := hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceAttendanceId',null);
1568 l_occurrence := hr_transaction_swi.getNumberValue(l_CommitNode,'Occurrence',null);
1569
1570 create_person_absence
1571 (p_validate => p_validate
1572 ,p_effective_date => p_effective_date
1573 ,p_person_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PersonId',null)
1574 ,p_business_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'BusinessGroupId',null)
1575 ,p_absence_attendance_type_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceAttendanceTypeId',null)
1576 ,p_abs_attendance_reason_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AbsAttendanceReasonId',null)
1577 ,p_comments => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Comments',null)
1578 ,p_date_notification => hr_transaction_swi.getDateValue(l_CommitNode,'DateNotification',null)
1579 ,p_date_projected_start => hr_transaction_swi.getDateValue(l_CommitNode,'DateProjectedStart',null)
1580 ,p_time_projected_start => hr_transaction_swi.getVarchar2Value(l_CommitNode,'TimeProjectedStart',null)
1581 ,p_date_projected_end => hr_transaction_swi.getDateValue(l_CommitNode,'DateProjectedEnd',null)
1582 ,p_time_projected_end => hr_transaction_swi.getVarchar2Value(l_CommitNode,'TimeProjectedEnd',null)
1583 ,p_date_start => hr_transaction_swi.getDateValue(l_CommitNode,'DateStart',null)
1584 ,p_time_start => hr_transaction_swi.getVarchar2Value(l_CommitNode,'TimeStart',null)
1585 ,p_date_end => hr_transaction_swi.getDateValue(l_CommitNode,'DateEnd',null)
1586 ,p_time_end => hr_transaction_swi.getVarchar2Value(l_CommitNode,'TimeEnd',null)
1587 ,p_absence_days => l_absence_days
1588 ,p_absence_hours => l_absence_hours
1589 ,p_authorising_person_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AuthorisingPersonId',null)
1590 ,p_replacement_person_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ReplacementPersonId',null)
1591 ,p_attribute_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AttributeCategory',null)
1592 ,p_attribute1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute1',null)
1593 ,p_attribute2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute2',null)
1594 ,p_attribute3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute3',null)
1595 ,p_attribute4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute4',null)
1596 ,p_attribute5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute5',null)
1597 ,p_attribute6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute6',null)
1598 ,p_attribute7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute7',null)
1599 ,p_attribute8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute8',null)
1600 ,p_attribute9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute9',null)
1601 ,p_attribute10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute10',null)
1602 ,p_attribute11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute11',null)
1603 ,p_attribute12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute12',null)
1604 ,p_attribute13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute13',null)
1605 ,p_attribute14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute14',null)
1606 ,p_attribute15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute15',null)
1607 ,p_attribute16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute16',null)
1608 ,p_attribute17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute17',null)
1609 ,p_attribute18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute18',null)
1610 ,p_attribute19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute19',null)
1611 ,p_attribute20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute20',null)
1612 ,p_period_of_incapacity_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PeriodOfIncapacityId',null)
1613 ,p_ssp1_issued => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Ssp1Issued',null)
1614 ,p_maternity_id => hr_transaction_swi.getNumberValue(l_CommitNode,'MaternityId',null)
1615 ,p_sickness_start_date => hr_transaction_swi.getDateValue(l_CommitNode,'SicknessStartDate',null)
1616 ,p_sickness_end_date => hr_transaction_swi.getDateValue(l_CommitNode,'SicknessEndDate',null)
1617 ,p_pregnancy_related_illness => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PregnancyRelatedIllness',null)
1618 ,p_reason_for_notification_dela => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ReasonForNotificationDela',null)
1619 ,p_accept_late_notification_fla => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AcceptLateNotificationFla',null)
1620 ,p_linked_absence_id => hr_transaction_swi.getNumberValue(l_CommitNode,'LinkedAbsenceId',null)
1621 ,p_batch_id => hr_transaction_swi.getNumberValue(l_CommitNode,'BatchId',null)
1622 ,p_create_element_entry => hr_transaction_swi.getNumberValue(l_CommitNode,'CreateElementEntry',null)
1623 ,p_abs_information_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformationCategory',null)
1624 ,p_abs_information1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation1',null)
1625 ,p_abs_information2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation2',null)
1626 ,p_abs_information3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation3',null)
1627 ,p_abs_information4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation4',null)
1628 ,p_abs_information5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation5',null)
1629 ,p_abs_information6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation6',null)
1630 ,p_abs_information7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation7',null)
1631 ,p_abs_information8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation8',null)
1632 ,p_abs_information9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation9',null)
1633 ,p_abs_information10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation10',null)
1634 ,p_abs_information11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation11',null)
1635 ,p_abs_information12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation12',null)
1636 ,p_abs_information13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation13',null)
1637 ,p_abs_information14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation14',null)
1638 ,p_abs_information15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation15',null)
1639 ,p_abs_information16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation16',null)
1640 ,p_abs_information17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation17',null)
1641 ,p_abs_information18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation18',null)
1642 ,p_abs_information19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation19',null)
1643 ,p_abs_information20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation20',null)
1644 ,p_abs_information21 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation21',null)
1645 ,p_abs_information22 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation22',null)
1646 ,p_abs_information23 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation23',null)
1647 ,p_abs_information24 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation24',null)
1648 ,p_abs_information25 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation25',null)
1649 ,p_abs_information26 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation26',null)
1650 ,p_abs_information27 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation27',null)
1651 ,p_abs_information28 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation28',null)
1652 ,p_abs_information29 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation29',null)
1653 ,p_abs_information30 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation30',null)
1654 ,p_absence_attendance_id => l_absence_attendance_id
1655 ,p_object_version_number => l_object_version_number
1656 ,p_occurrence => l_occurrence
1657 ,p_return_status => l_return_status);
1658
1659 elsif l_postState = '2' then
1660
1661
1662 l_absence_days := hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceDays',null);
1663 l_absence_hours := hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceHours',null);
1664 l_absence_attendance_id := hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceAttendanceId',null);
1665
1666 if( l_absence_days is null and l_absence_hours is null) then
1667
1668 open c_get_dur(l_absence_attendance_id);
1669 fetch c_get_dur into lv_absence_days, lv_absence_hours;
1670
1671 if c_get_dur%found then
1672 l_absence_days := lv_absence_days;
1673 l_absence_hours := lv_absence_hours;
1674 end if;
1675
1676 close c_get_dur;
1677 end if;
1678
1679 update_person_absence
1680 (p_validate => p_validate
1681 ,p_effective_date => p_effective_date
1682 ,p_absence_attendance_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceAttendanceId')
1683 ,p_abs_attendance_reason_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AbsAttendanceReasonId')
1684 ,p_comments => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Comments')
1685 ,p_date_notification => hr_transaction_swi.getDateValue(l_CommitNode,'DateNotification')
1686 ,p_date_projected_start => hr_transaction_swi.getDateValue(l_CommitNode,'DateProjectedStart')
1687 ,p_time_projected_start => hr_transaction_swi.getVarchar2Value(l_CommitNode,'TimeProjectedStart')
1688 ,p_date_projected_end => hr_transaction_swi.getDateValue(l_CommitNode,'DateProjectedEnd')
1689 ,p_time_projected_end => hr_transaction_swi.getVarchar2Value(l_CommitNode,'TimeProjectedEnd')
1690 ,p_date_start => hr_transaction_swi.getDateValue(l_CommitNode,'DateStart')
1691 ,p_time_start => hr_transaction_swi.getVarchar2Value(l_CommitNode,'TimeStart')
1692 ,p_date_end => hr_transaction_swi.getDateValue(l_CommitNode,'DateEnd')
1693 ,p_time_end => hr_transaction_swi.getVarchar2Value(l_CommitNode,'TimeEnd')
1694 ,p_absence_days => l_absence_days
1695 ,p_absence_hours => l_absence_hours
1696 ,p_authorising_person_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AuthorisingPerson_id')
1697 ,p_replacement_person_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ReplacementPersonId')
1698 ,p_attribute_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AttributeCategory')
1699 ,p_attribute1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute1')
1700 ,p_attribute2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute2')
1701 ,p_attribute3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute3')
1702 ,p_attribute4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute4')
1703 ,p_attribute5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute5')
1704 ,p_attribute6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute6')
1705 ,p_attribute7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute7')
1706 ,p_attribute8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute8')
1707 ,p_attribute9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute9')
1708 ,p_attribute10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute10')
1709 ,p_attribute11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute11')
1710 ,p_attribute12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute12')
1711 ,p_attribute13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute13')
1712 ,p_attribute14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute14')
1713 ,p_attribute15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute15')
1714 ,p_attribute16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute16')
1715 ,p_attribute17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute17')
1716 ,p_attribute18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute18')
1717 ,p_attribute19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute19')
1718 ,p_attribute20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute20')
1719 ,p_period_of_incapacity_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PeriodOfIncapacityId')
1720 ,p_ssp1_issued => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Ssp1Issued')
1721 ,p_maternity_id => hr_transaction_swi.getNumberValue(l_CommitNode,'MaternityId')
1722 ,p_sickness_start_date => hr_transaction_swi.getDateValue(l_CommitNode,'SicknessStartDate')
1723 ,p_sickness_end_date => hr_transaction_swi.getDateValue(l_CommitNode,'SicknessEndDate')
1724 ,p_pregnancy_related_illness => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PregnancyRelatedIllness')
1725 ,p_reason_for_notification_dela => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ReasonForNotificationDela')
1726 ,p_accept_late_notification_fla => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AcceptLateNotificationFla')
1727 ,p_linked_absence_id => hr_transaction_swi.getNumberValue(l_CommitNode,'LinkedAbsenceId')
1728 ,p_batch_id => hr_transaction_swi.getNumberValue(l_CommitNode,'BatchId')
1729 ,p_abs_information_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformationCategory')
1730 ,p_abs_information1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation1')
1731 ,p_abs_information2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation2')
1732 ,p_abs_information3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation3')
1733 ,p_abs_information4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation4')
1734 ,p_abs_information5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation5')
1735 ,p_abs_information6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation6')
1736 ,p_abs_information7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation7')
1737 ,p_abs_information8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation8')
1738 ,p_abs_information9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation9')
1739 ,p_abs_information10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation10')
1740 ,p_abs_information11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation11')
1741 ,p_abs_information12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation12')
1742 ,p_abs_information13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation13')
1743 ,p_abs_information14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation14')
1744 ,p_abs_information15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation15')
1745 ,p_abs_information16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation16')
1746 ,p_abs_information17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation17')
1747 ,p_abs_information18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation18')
1748 ,p_abs_information19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation19')
1749 ,p_abs_information20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation20')
1750 ,p_abs_information21 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation21')
1751 ,p_abs_information22 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation22')
1752 ,p_abs_information23 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation23')
1753 ,p_abs_information24 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation24')
1754 ,p_abs_information25 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation25')
1755 ,p_abs_information26 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation26')
1756 ,p_abs_information27 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation27')
1757 ,p_abs_information28 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation28')
1758 ,p_abs_information29 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation29')
1759 ,p_abs_information30 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AbsInformation30')
1760 ,p_object_version_number => l_object_version_number
1761 ,p_return_status => l_return_status);
1762
1763
1764 elsif l_postState = '3' then
1765
1766 delete_person_absence
1767 ( p_validate => p_validate
1768 ,p_absence_attendance_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceAttendanceId')
1769 ,p_object_version_number => l_object_version_number
1770 ,p_return_status => l_return_status
1771 );
1772
1773 end if;
1774
1775
1776 --Fix for Bug 14560350 start
1777 l_msg_count := fnd_msg_pub.Count_Msg;
1778 lw_msg_count := 0;
1779 hr_utility.trace('msg count:'||l_msg_count);
1780
1781 FOR i IN 1 .. l_msg_count LOOP
1782 -- get the actual message from fnd_msg_pub
1783 msg_details :=fnd_msg_pub.Get_Detail(p_msg_index=>i,p_encoded=>'F');
1784 hr_utility.trace('msg_details:'||msg_details);
1785 if(instr(msg_details, '(FND_MESSAGE_TYPE=W)')) > 0 then
1786
1787 lw_msg_count := lw_msg_count + 1;
1788 hr_utility.trace('Warning message:'||lw_msg_count);
1789 end if;
1790
1791 hr_utility.set_location('EO Error : ' || msg_details, 20);
1792 --
1793 END LOOP;
1794 if l_msg_count = lw_msg_count then
1795 l_return_status := 'S';
1796 hr_utility.trace('l_return_status:'||l_return_status);
1797 end if;
1798 --Fix for Bug 14560350 end
1799
1800
1801 -- finally call the attachements update
1802 if( l_return_status <> 'E') then
1803 merge_attachments(hr_transaction_swi.g_txn_ctx.TRANSACTION_ID,
1804 hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceAttendanceId'),
1805 l_return_status);
1806 end if;
1807
1808 p_return_status := l_return_status;
1809 hr_utility.set_location('Exiting:' || l_proc,40);
1810 EXCEPTION
1811 WHEN OTHERS THEN
1812 rollback to absence_process_api;
1813 hr_utility.trace('Exception in .process_api:' || SQLERRM );
1814 hr_utility.set_location(' Leaving:' || l_proc,50);
1815
1816 raise;
1817
1818 END process_api;
1819
1820 procedure delete_absences_in_tt
1821 (p_transaction_id in number)
1822 is
1823
1824 begin
1825
1826 hr_absutil_ss.delete_transaction(p_transaction_id);
1827
1828 exception
1829 when others then
1830 raise;
1831 end delete_absences_in_tt;
1832
1833
1834 procedure otl_hr_check
1835 (
1836 p_person_id number default null,
1837 p_date_start date default null,
1838 p_date_end date default null,
1839 p_scope varchar2 default null,
1840 p_ret_value out nocopy varchar2,
1841 p_error_name out nocopy varchar2
1842 ) is
1843
1844 begin
1845
1846 hr_multi_message.enable_message_list;
1847 hr_person_absence_api.otl_hr_check (
1848 p_person_id => p_person_id,
1849 p_date_start => p_date_start ,
1850 p_date_end => p_date_end ,
1851 p_scope => p_scope ,
1852 p_ret_value => p_ret_value);
1853
1854 exception
1855
1856 when others then
1857 p_error_name := fnd_message.get();
1858
1859 end otl_hr_check;
1860
1861
1862 end hr_person_absence_swi;