[Home] [Help]
PACKAGE BODY: APPS.PER_GB_ABSENCE_EVIDENCE_SWI
Source
1 PACKAGE body per_gb_absence_evidence_swi As
2 /* $Header: pergbabsswi.pkb 120.3 2011/11/29 15:43:07 npannamp ship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'per_gb_absence_evidence_swi.';
7
8 function is_gb_leg_and_category_s(p_absence_attendance_type_id IN NUMBER ,
9 p_business_group_id IN NUMBER)
10 return boolean is
11
12 l_proc varchar2(200) := g_package || 'is_gb_leg_and_category_s';
13
14 populate_sickness_dates boolean := true;
15 l_absence_category per_absence_attendance_types.absence_category%type ;
16 l_legislation_code varchar2(150);
17
18 cursor get_category_code (p_absence_attendance_type_id number) is
19 select absence_category
20 from per_absence_attendance_types
21 where absence_attendance_type_id = p_absence_attendance_type_id;
22
23 begin
24 hr_utility.set_location(' Entering:' || l_proc,5);
25
26 open get_category_code(p_absence_attendance_type_id);
27 fetch get_category_code into l_absence_category ;
28 close get_category_code ;
29
30 IF l_absence_category = 'S' THEN
31 l_legislation_code := hr_api.return_legislation_code(p_business_group_id);
32 IF l_legislation_code = 'GB' THEN
33 hr_utility.set_location(l_proc,10);
34
35 populate_sickness_dates := true;
36 END IF;
37 END IF;
38 hr_utility.set_location(' Leaving:' || l_proc,15);
39
40 return populate_sickness_dates;
41 END is_gb_leg_and_category_s ;
42 --
43 --
44 -- ----------------------------------------------------------------------------
45 -- |--------------------< create_gb_absence_evidence >------------------------|
46 -- ----------------------------------------------------------------------------
47 PROCEDURE create_gb_absence_evidence
48 (p_validate in number default hr_api.g_false_num
49 ,p_effective_date in date default null
50 ,p_person_id in number default null
51 ,p_business_group_id in number default null
52 ,p_absence_attendance_type_id in number default null
53 ,p_evidence_date in date default null
54 ,p_evidence_source in varchar2 default null
55 ,p_absence_attendance_id in number default null
56 ,p_maternity_id in number default null
57 ,p_medical_type in varchar2 default null
58 ,p_self_certificate in varchar2 default null
59 ,p_predicted_fitness_date in date default null
60 ,p_evidence_status in varchar2 default null
61 ,p_evidence_details in varchar2 default null
62 ,p_accept_late_evidence_flag in varchar2 default null
63 ,p_reason_for_late_evidence in varchar2 default null
64 ,p_evidence_received_date in date default null
65 ,p_attribute_category in varchar2 default null
66 ,p_attribute1 in varchar2 default null
67 ,p_attribute2 in varchar2 default null
68 ,p_attribute3 in varchar2 default null
69 ,p_attribute4 in varchar2 default null
70 ,p_attribute5 in varchar2 default null
71 ,p_attribute6 in varchar2 default null
72 ,p_attribute7 in varchar2 default null
73 ,p_attribute8 in varchar2 default null
74 ,p_attribute9 in varchar2 default null
75 ,p_attribute10 in varchar2 default null
76 ,p_attribute11 in varchar2 default null
77 ,p_attribute12 in varchar2 default null
78 ,p_attribute13 in varchar2 default null
79 ,p_attribute14 in varchar2 default null
80 ,p_attribute15 in varchar2 default null
81 ,p_attribute16 in varchar2 default null
82 ,p_attribute17 in varchar2 default null
83 ,p_attribute18 in varchar2 default null
84 ,p_attribute19 in varchar2 default null
85 ,p_attribute20 in varchar2 default null
86 ,p_medical_id in out nocopy number
87 ,p_object_version_number out nocopy number
88 ,p_return_status out nocopy varchar2
89 ) is
90 --
91 -- Variables for API Boolean parameters
92 l_validate boolean;
93 --
94 -- Variables for IN/OUT parameters
95 l_populate_sickness_dates boolean := false;
96 l_error_text varchar2(2000);
97 l_sqlerrm varchar2(2000);
98
99 cursor chk_evidence_status(p_abs_attd_id number, p_evid_date date) is
100 select 1 from ssp_medicals
101 where absence_attendance_id = p_abs_attd_id
102 and evidence_date > p_evid_date;
103
104 l_recs number :=0;
105 l_evidence_status varchar2(20);
106 --
107 -- Other variables
108 l_proc varchar2(72) := g_package ||'create_gb_absence_evidence';
109 Begin
110 hr_utility.set_location(' Entering:' || l_proc,10);
111 --
112 -- Issue a savepoint
113 --
114 savepoint create_gb_absence_evidence_swi;
115 --
116 -- Initialise Multiple Message Detection
117 --
118 hr_multi_message.enable_message_list;
119
120 --
121 -- Convert constant values to their corresponding boolean value
122 --
123 l_validate :=
124 hr_api.constant_to_boolean
125 (p_constant_value => p_validate);
126
127
128 l_populate_sickness_dates := is_gb_leg_and_category_s(p_absence_attendance_type_id , p_business_group_id);
129
130 l_evidence_status := p_evidence_status;
131 open chk_evidence_status (p_absence_attendance_id, p_evidence_date);
132 fetch chk_evidence_status into l_recs;
133 if chk_evidence_status%found then
134 l_evidence_status :='SUPERCEDED';
135 elsif chk_evidence_status%notfound then
136 l_evidence_status :='CURRENT';
137 end if;
138 close chk_evidence_status;
139
140 update ssp_medicals
141 set evidence_status='SUPERCEDED'
142 where absence_attendance_id = p_absence_attendance_id
143 and evidence_status = 'CURRENT'
144 and evidence_date < p_evidence_date;
145
146 -- IF l_populate_sickness_dates THEN
147
148 insert into ssp_medicals
149 (MEDICAL_ID,
150 OBJECT_VERSION_NUMBER,
151 EVIDENCE_DATE,
152 EVIDENCE_SOURCE,
153 ABSENCE_ATTENDANCE_ID,
154 MATERNITY_ID,
155 MEDICAL_TYPE,
156 SELF_CERTIFICATE,
157 PREDICTED_FITNESS_DATE,
158 EVIDENCE_STATUS,
159 EVIDENCE_DETAILS,
160 ACCEPT_LATE_EVIDENCE_FLAG,
161 REASON_FOR_LATE_EVIDENCE,
162 EVIDENCE_RECEIVED_DATE,
163 ATTRIBUTE_CATEGORY,
164 ATTRIBUTE1,
165 ATTRIBUTE2,
166 ATTRIBUTE3,
167 ATTRIBUTE4,
168 ATTRIBUTE5,
169 ATTRIBUTE6,
170 ATTRIBUTE7,
171 ATTRIBUTE8,
172 ATTRIBUTE9,
173 ATTRIBUTE10,
174 ATTRIBUTE11,
175 ATTRIBUTE12,
176 ATTRIBUTE13,
177 ATTRIBUTE14,
178 ATTRIBUTE15,
179 ATTRIBUTE16,
180 ATTRIBUTE17,
181 ATTRIBUTE18,
182 ATTRIBUTE19,
183 ATTRIBUTE20,
184 CREATED_BY,
185 CREATION_DATE,
186 LAST_UPDATED_BY,
187 LAST_UPDATE_DATE,
188 LAST_UPDATE_LOGIN)
189 values
190 (p_medical_id, -- ssp_medicals_s.nextval,
191 p_object_version_number,
192 trunc(p_evidence_date),
193 p_evidence_source,
194 p_absence_attendance_id,
195 null,
196 p_medical_type,
197 p_self_certificate,
198 trunc(p_predicted_fitness_date),
199 l_evidence_status,
200 p_evidence_details,
201 p_accept_late_evidence_flag,
202 p_reason_for_late_evidence,
203 trunc(p_evidence_received_date),
204 p_attribute_category,
205 p_attribute1,
206 p_attribute2,
207 p_attribute3,
208 p_attribute4,
209 p_attribute5,
210 p_attribute6,
211 p_attribute7,
212 p_attribute8,
213 p_attribute9,
214 p_attribute10,
215 p_attribute11,
216 p_attribute12,
217 p_attribute13,
218 p_attribute14,
219 p_attribute15,
220 p_attribute16,
221 p_attribute17,
222 p_attribute18,
223 p_attribute19,
224 p_attribute20,
225 -1,
226 trunc(sysdate),
227 -1,
228 trunc(sysdate),
229 -1);
230
231
232 -- END IF;
233 --
234 -- Convert API warning boolean parameter values to specific
235 -- messages and add them to Multiple Message List
236 --
237 if l_validate then
238 null;
239 end if;
240
241 p_return_status := 'S';
242 hr_utility.set_location(' Leaving:' || l_proc,20);
243 --
244 exception
245 when DUP_VAL_ON_INDEX then
246 rollback to create_gb_absence_evidence_swi;
247 hr_utility.set_message(804, 'SSP_35020_NON_UNQ_MED_EVID');
248 hr_multi_message.add(p_message_type => hr_multi_message.g_error_msg);
249 p_return_status := 'E';
250 when others then
251 --
252 -- When Multiple Message Detection is enabled catch
253 -- any Application specific or other unexpected
254 -- exceptions. Adding appropriate details to the
255 -- Multiple Message List. Otherwise re-raise the
256 -- error.
257 --
258 rollback to create_gb_absence_evidence_swi;
259 if hr_multi_message.unexpected_error_add(l_proc) then
260 hr_utility.set_location(' Leaving:' || l_proc,40);
261 raise;
262 end if;
263 l_sqlerrm := sqlerrm;
264 l_error_text := hr_utility.get_message;
265 if l_error_text is null then
266 l_error_text := fnd_message.get;
267 end if;
268
269 if (((l_error_text is not null) OR (l_sqlerrm is not null)) and (p_validate = hr_api.g_false_num)) then
270 hr_utility.set_location(' Leaving:' || l_proc,45);
271 raise;
272 end if;
273 --
274 -- Reset IN OUT and set OUT parameters
275 --
276 p_object_version_number := null;
277 p_return_status := 'E';
278 hr_utility.set_location(' Leaving:' || l_proc,50);
279 end create_gb_absence_evidence;
280 -- ----------------------------------------------------------------------------
281 -- |---------------------< update_gb_absence_evidence >-----------------------|
282 -- ----------------------------------------------------------------------------
283 PROCEDURE update_gb_absence_evidence
284 (p_validate in number default hr_api.g_false_num
285 ,p_effective_date in date default hr_api.g_date
286 ,p_person_id in number default hr_api.g_number
287 ,p_business_group_id in number default hr_api.g_number
288 ,p_absence_attendance_type_id in number default hr_api.g_number
289 ,p_evidence_date in date default hr_api.g_date
290 ,p_evidence_source in varchar2 default hr_api.g_varchar2
291 ,p_absence_attendance_id in number default hr_api.g_number
292 ,p_maternity_id in number default hr_api.g_number
293 ,p_medical_type in varchar2 default hr_api.g_varchar2
294 ,p_self_certificate in varchar2 default hr_api.g_varchar2
295 ,p_predicted_fitness_date in date default hr_api.g_date
296 ,p_evidence_status in varchar2 default hr_api.g_varchar2
297 ,p_evidence_details in varchar2 default hr_api.g_varchar2
298 ,p_accept_late_evidence_flag in varchar2 default hr_api.g_varchar2
299 ,p_reason_for_late_evidence in varchar2 default hr_api.g_varchar2
300 ,p_evidence_received_date in date default hr_api.g_date
301 ,p_attribute_category in varchar2 default hr_api.g_varchar2
302 ,p_attribute1 in varchar2 default hr_api.g_varchar2
303 ,p_attribute2 in varchar2 default hr_api.g_varchar2
304 ,p_attribute3 in varchar2 default hr_api.g_varchar2
305 ,p_attribute4 in varchar2 default hr_api.g_varchar2
306 ,p_attribute5 in varchar2 default hr_api.g_varchar2
307 ,p_attribute6 in varchar2 default hr_api.g_varchar2
308 ,p_attribute7 in varchar2 default hr_api.g_varchar2
309 ,p_attribute8 in varchar2 default hr_api.g_varchar2
310 ,p_attribute9 in varchar2 default hr_api.g_varchar2
311 ,p_attribute10 in varchar2 default hr_api.g_varchar2
312 ,p_attribute11 in varchar2 default hr_api.g_varchar2
313 ,p_attribute12 in varchar2 default hr_api.g_varchar2
314 ,p_attribute13 in varchar2 default hr_api.g_varchar2
315 ,p_attribute14 in varchar2 default hr_api.g_varchar2
316 ,p_attribute15 in varchar2 default hr_api.g_varchar2
317 ,p_attribute16 in varchar2 default hr_api.g_varchar2
318 ,p_attribute17 in varchar2 default hr_api.g_varchar2
319 ,p_attribute18 in varchar2 default hr_api.g_varchar2
320 ,p_attribute19 in varchar2 default hr_api.g_varchar2
321 ,p_attribute20 in varchar2 default hr_api.g_varchar2
322 ,p_medical_id in out nocopy number
323 ,p_object_version_number out nocopy number
324 ,p_return_status out nocopy varchar2
325 ) is
326 --
327 -- Variables for API Boolean parameters
328 l_validate boolean;
329 l_populate_sickness_dates boolean := false;
330 --
331 -- Variables for IN/OUT parameters
332 l_object_version_number number;
333
334 l_absence_attendance_type_id per_absence_attendances.absence_attendance_type_id%type;
335 l_business_group_id per_absence_attendances.business_group_id%type;
336
337 Cursor c_is_gb_leg is
338 select absence_attendance_type_id, business_group_id
339 from per_absence_attendances
340 where absence_attendance_id = p_absence_attendance_id;
341
342 --
343 -- Other variables
344 l_proc varchar2(72) := g_package ||'update_gb_absence_evidence';
345 l_error_text varchar2(2000);
346 l_sqlerrm varchar2(2000);
347 l_date_start Date;
348
349 cursor c_ssp_medicals is
350 select * from ssp_medicals
351 where medical_id = p_medical_id;
352
353 ssp_rec c_ssp_medicals%rowtype;
354
355 Begin
356 hr_utility.set_location(' Entering:' || l_proc,10);
357 --
358 -- Issue a savepoint
359 --
360 savepoint update_gb_absence_swi;
361 --
362 -- Initialise Multiple Message Detection
363 --
364 hr_multi_message.enable_message_list;
365 --
366 l_object_version_number := p_object_version_number;
367 --
368 -- Convert constant values to their corresponding boolean value
369 --
370 l_validate :=
371 hr_api.constant_to_boolean
372 (p_constant_value => p_validate);
373
374 hr_utility.set_location(' Stage:' || l_proc,12);
375 open c_ssp_medicals;
376 fetch c_ssp_medicals into ssp_rec;
377
378 if c_ssp_medicals%found then
379 hr_utility.set_location(' Stage:' || l_proc,13);
380 if p_evidence_source <> hr_api.g_varchar2 then
381 ssp_rec.evidence_source := p_evidence_source;
382 end if;
383 if p_evidence_date <> hr_api.g_date then
384 ssp_rec.evidence_date := p_evidence_date;
385 end if;
386 if p_evidence_received_date <> hr_api.g_date then
387 ssp_rec.evidence_received_date := p_evidence_received_date;
388 end if;
389 if p_predicted_fitness_date <> hr_api.g_date then
390 ssp_rec.predicted_fitness_date := p_predicted_fitness_date;
391 end if;
392 if p_evidence_details <> hr_api.g_varchar2 then
393 ssp_rec.evidence_details := p_evidence_details;
394 end if;
395 if p_evidence_status <> hr_api.g_varchar2 then
396 ssp_rec.evidence_status := p_evidence_status;
397 end if;
398 if p_self_certificate <> hr_api.g_varchar2 then
399 ssp_rec.self_certificate := p_self_certificate;
400 end if;
401 if p_attribute_category <> hr_api.g_varchar2 then
402 ssp_rec.attribute_category := p_attribute_category;
403 end if;
404 if p_attribute1 <> hr_api.g_varchar2 then
405 ssp_rec.attribute1 := p_attribute1;
406 end if;
407 if p_attribute2 <> hr_api.g_varchar2 then
408 ssp_rec.attribute2 := p_attribute2;
409 end if;
410 if p_attribute3 <> hr_api.g_varchar2 then
411 ssp_rec.attribute3 := p_attribute3;
412 end if;
413 if p_attribute4 <> hr_api.g_varchar2 then
414 ssp_rec.attribute4 := p_attribute4;
415 end if;
416 if p_attribute5 <> hr_api.g_varchar2 then
417 ssp_rec.attribute5 := p_attribute5;
418 end if;
419 if p_attribute6 <> hr_api.g_varchar2 then
420 ssp_rec.attribute6 := p_attribute6;
421 end if;
422 if p_attribute7 <> hr_api.g_varchar2 then
423 ssp_rec.attribute7 := p_attribute7;
424 end if;
425 if p_attribute8 <> hr_api.g_varchar2 then
426 ssp_rec.attribute8 := p_attribute8;
427 end if;
428 if p_attribute9 <> hr_api.g_varchar2 then
429 ssp_rec.attribute9 := p_attribute9;
430 end if;
431 if p_attribute10 <> hr_api.g_varchar2 then
432 ssp_rec.attribute10 := p_attribute10;
433 end if;
434 if p_attribute11 <> hr_api.g_varchar2 then
435 ssp_rec.attribute11 := p_attribute11;
436 end if;
437 if p_attribute12 <> hr_api.g_varchar2 then
438 ssp_rec.attribute12 := p_attribute12;
439 end if;
440 if p_attribute13 <> hr_api.g_varchar2 then
441 ssp_rec.attribute13 := p_attribute13;
442 end if;
443 if p_attribute14 <> hr_api.g_varchar2 then
444 ssp_rec.attribute14 := p_attribute14;
445 end if;
446 if p_attribute15 <> hr_api.g_varchar2 then
447 ssp_rec.attribute15 := p_attribute15;
448 end if;
449 if p_attribute16 <> hr_api.g_varchar2 then
450 ssp_rec.attribute16 := p_attribute16;
451 end if;
452 if p_attribute17 <> hr_api.g_varchar2 then
453 ssp_rec.attribute17 := p_attribute17;
454 end if;
455 if p_attribute18 <> hr_api.g_varchar2 then
456 ssp_rec.attribute18 := p_attribute18;
457 end if;
458 if p_attribute19 <> hr_api.g_varchar2 then
459 ssp_rec.attribute19 := p_attribute19;
460 end if;
461 if p_attribute20 <> hr_api.g_varchar2 then
462 ssp_rec.attribute20 := p_attribute20;
463 end if;
464 end if;
465 close c_ssp_medicals;
466
467
468 open c_is_gb_leg;
469 fetch c_is_gb_leg into l_absence_attendance_type_id, l_business_group_id;
470 hr_utility.set_location(' Stage:' || l_proc,14);
471 IF c_is_gb_leg%FOUND THEN
472 l_populate_sickness_dates := is_gb_leg_and_category_s(l_absence_attendance_type_id , l_business_group_id);
473 END IF;
474 close c_is_gb_leg;
475
476 -- IF l_populate_sickness_dates THEN
477 hr_utility.set_location(' Stage:' || l_proc,15);
478
479 update ssp_medicals set
480 evidence_source = ssp_rec.evidence_source
481 ,evidence_date = ssp_rec.evidence_date
482 ,evidence_details = ssp_rec.evidence_details
483 ,evidence_received_date = ssp_rec.evidence_received_date
484 ,evidence_status = ssp_rec.evidence_status
485 ,predicted_fitness_date = ssp_rec.predicted_fitness_date
486 ,self_certificate = ssp_rec.self_certificate
487 ,attribute_category = ssp_rec.attribute_category
488 ,attribute1 = ssp_rec.attribute1
489 ,attribute2 = ssp_rec.attribute2
490 ,attribute3 = ssp_rec.attribute3
491 ,attribute4 = ssp_rec.attribute4
492 ,attribute5 = ssp_rec.attribute5
493 ,attribute6 = ssp_rec.attribute6
494 ,attribute7 = ssp_rec.attribute7
495 ,attribute8 = ssp_rec.attribute8
496 ,attribute9 = ssp_rec.attribute9
497 ,attribute10 = ssp_rec.attribute10
498 ,attribute11 = ssp_rec.attribute11
499 ,attribute12 = ssp_rec.attribute12
500 ,attribute13 = ssp_rec.attribute13
501 ,attribute14 = ssp_rec.attribute14
502 ,attribute15 = ssp_rec.attribute15
503 ,attribute16 = ssp_rec.attribute16
504 ,attribute17 = ssp_rec.attribute17
505 ,attribute18 = ssp_rec.attribute18
506 ,attribute19 = ssp_rec.attribute19
507 ,attribute20 = ssp_rec.attribute20
508 where MEDICAL_ID=p_medical_id;
509 -- END IF;
510
511 hr_utility.set_location(' Stage:' || l_proc,16);
512
513
514 -- Convert API non-warning boolean parameter values
515 --
516 --
517 -- Derive the API return status value based on whether
518 -- messages of any type exist in the Multiple Message List.
519 -- Also disable Multiple Message Detection.
520
521 p_return_status := 'S';
522 hr_utility.set_location(' Leaving:' || l_proc,20);
523 --
524 exception
525 when DUP_VAL_ON_INDEX then
526 rollback to create_gb_absence_evidence_swi;
527 hr_utility.set_message(804, 'SSP_35020_NON_UNQ_MED_EVID');
528 hr_multi_message.add(p_message_type => hr_multi_message.g_error_msg);
529 p_return_status := 'E';
530 when others then
531 --
532 -- When Multiple Message Detection is enabled catch
533 -- any Application specific or other unexpected
534 -- exceptions. Adding appropriate details to the
535 -- Multiple Message List. Otherwise re-raise the
536 -- error.
537 --
538 rollback to update_gb_absence_swi;
539 if hr_multi_message.unexpected_error_add(l_proc) then
540 hr_utility.set_location(' Leaving:' || l_proc,40);
541 raise;
542 end if;
543 l_sqlerrm := sqlerrm;
544 l_error_text := hr_utility.get_message;
545 if l_error_text is null then
546 l_error_text := fnd_message.get;
547 end if;
548
549 if (((l_error_text is not null) OR (l_sqlerrm is not null)) and (p_validate = hr_api.g_false_num)) then
550 hr_utility.set_location(' Leaving:' || l_proc,45);
551 raise;
552 end if;
553
554 p_return_status := 'E';
555 hr_utility.set_location(' Leaving:' || l_proc,50);
556 end update_gb_absence_evidence;
557 -- ----------------------------------------------------------------------------
558 -- |----------------------< delete_gb_absence_evidence >----------------------|
559 -- ----------------------------------------------------------------------------
560 PROCEDURE delete_gb_absence_evidence
561 (p_validate in number default hr_api.g_false_num
562 ,p_absence_attendance_id in number
563 ,p_medical_id in number
564 ,p_object_version_number in number
565 ,p_return_status out nocopy varchar2
566 ) is
567 --
568 -- Variables for API Boolean parameters
569 l_validate boolean;
570 --
571 -- Variables for IN/OUT parameters
572 --
573 -- Other variables
574 l_proc varchar2(72) := g_package ||'delete_gb_absence_evidence';
575 Begin
576
577
578 hr_utility.set_location(' Entering:' || l_proc,10);
579 --
580 -- Issue a savepoint
581 --
582 savepoint delete_gb_absence_swi;
583 --
584 -- Initialise Multiple Message Detection
585 --
586 hr_multi_message.enable_message_list;
587 --
588 -- Remember IN OUT parameter IN values
589 --
590 --
591 -- Convert constant values to their corresponding boolean value
592 --
593 l_validate :=
594 hr_api.constant_to_boolean
595 (p_constant_value => p_validate);
596 --
597 if not(l_validate) then
598 delete from ssp_medicals where medical_id = p_medical_id;
599 end if;
600 --
601 -- Call API
602 --
603 /* hr_person_absence_api.delete_person_absence
604 (p_validate => l_validate
605 ,p_absence_attendance_id => p_absence_attendance_id
606 ,p_object_version_number => p_object_version_number
607 );*/
608 --
609 -- Convert API warning boolean parameter values to specific
610 -- messages and add them to Multiple Message List
611 --
612 --
613 -- Convert API non-warning boolean parameter values
614 --
615 --
616 -- Derive the API return status value based on whether
617 -- messages of any type exist in the Multiple Message List.
618 -- Also disable Multiple Message Detection.
619 --
620 p_return_status := 'S';
621 hr_utility.set_location(' Leaving:' || l_proc,20);
622 --
623 exception
624 when others then
625 --
626 -- When Multiple Message Detection is enabled catch
627 -- any Application specific or other unexpected
628 -- exceptions. Adding appropriate details to the
629 -- Multiple Message List. Otherwise re-raise the
630 -- error.
631 --
632 rollback to delete_gb_absence_swi;
633 if hr_multi_message.unexpected_error_add(l_proc) then
634 hr_utility.set_location(' Leaving:' || l_proc,40);
635 raise;
636 end if;
637 --
638 -- Reset IN OUT and set OUT parameters
639 --
640 p_return_status := 'E';
641 hr_utility.set_location(' Leaving:' || l_proc,50);
642 end delete_gb_absence_evidence;
643
644
645 -- ----------------------------------------------------------------------------
646 -- |---------------------------< process_api >--------------------------------|
647 -- ----------------------------------------------------------------------------
648
649 Procedure process_api
650 ( p_document in CLOB
651 ,p_return_status out nocopy VARCHAR2
652 ,p_validate in number default hr_api.g_false_num
653 ,p_effective_date in date default null
654 )
655 IS
656 l_postState VARCHAR2(2);
657 l_return_status VARCHAR2(1);
658 l_commitElement xmldom.DOMElement;
659 l_object_version_number number;
660 l_parser xmlparser.Parser;
661 l_CommitNode xmldom.DOMNode;
662 l_proc varchar2(72) := g_package || 'process_api';
663
664 l_absence_attendance_id number;
665 l_medical_id number;
666 lv_action varchar2(30);
667
668 Cursor c_get_dur(p_absence_attendance_id number) is
669 select absence_days, absence_hours
670 from per_absence_attendances
671 where absence_attendance_id = p_absence_attendance_id;
672
673
674 BEGIN
675 hr_utility.set_location(' Entering:' || l_proc,10);
676 hr_utility.set_location(' CLOB --> xmldom.DOMNode:' || l_proc,15);
677 savepoint gb_abs_evidence_process_api;
678 l_parser := xmlparser.newParser;
679 xmlparser.ParseCLOB(l_parser,p_document);
680 l_CommitNode := xmldom.makeNode(xmldom.getDocumentElement(xmlparser.getDocument(l_parser)));
681
682 hr_utility.set_location('Extracting the PostState:' || l_proc,20);
683
684 l_commitElement := xmldom.makeElement(l_CommitNode);
685 l_postState := xmldom.getAttribute(l_commitElement, 'PS');
686 l_object_version_number := hr_transaction_swi.getNumberValue(l_CommitNode,'ObjectVersionNumber');
687
688 -- overiding for poststate
689 -- CancelMode
690 if(l_postState = '2') then
691 -- Check if the transaction is for delete
692 begin
693 /*lv_action:= hr_xml_util.get_node_value(hr_transaction_swi.g_txn_ctx.TRANSACTION_ID,'AbsenceAction','Transaction/TransCtx/CNode',
694 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);*/
695 null;
696 end;
697 if(lv_action='CancelMode') then
698 -- reset the poststate
699 l_postState:= '3';
700 end if;
701 end if;
702
703
704 if l_postState = '0' then
705
706 l_absence_attendance_id := hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceAttendanceId',null);
707 l_medical_id := hr_transaction_swi.getNumberValue(l_CommitNode,'MedicalId',null);
708
709 create_gb_absence_evidence
710 (p_validate => p_validate
711 ,p_effective_date => p_effective_date
712 ,p_person_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PersonId',null)
713 ,p_business_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'BusinessGroupId',null)
714 ,p_medical_id => l_medical_id
715 ,p_absence_attendance_id => l_absence_attendance_id
716 ,p_evidence_source => hr_transaction_swi.getVarchar2Value(l_CommitNode,'EvidenceSource',null)
717 ,p_evidence_status => hr_transaction_swi.getVarchar2Value(l_CommitNode,'EvidenceStatus',null)
718 ,p_medical_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'MedicalType',null)
719 ,p_evidence_details => hr_transaction_swi.getVarchar2Value(l_CommitNode,'EvidenceDetails',null)
720 ,p_evidence_date => hr_transaction_swi.getDateValue(l_CommitNode,'EvidenceDate',null)
721 ,p_evidence_received_date => hr_transaction_swi.getDateValue(l_CommitNode,'EvidenceReceivedDate',null)
722 ,p_predicted_fitness_date => hr_transaction_swi.getDateValue(l_CommitNode,'PredictedFitnessDate',null)
723 ,p_self_certificate => hr_transaction_swi.getVarchar2Value(l_CommitNode,'SelfCertificate',null)
724 ,p_attribute_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AttributeCategory',null)
725 ,p_attribute1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute1',null)
726 ,p_attribute2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute2',null)
727 ,p_attribute3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute3',null)
728 ,p_attribute4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute4',null)
729 ,p_attribute5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute5',null)
730 ,p_attribute6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute6',null)
731 ,p_attribute7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute7',null)
732 ,p_attribute8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute8',null)
733 ,p_attribute9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute9',null)
734 ,p_attribute10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute10',null)
735 ,p_attribute11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute11',null)
736 ,p_attribute12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute12',null)
737 ,p_attribute13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute13',null)
738 ,p_attribute14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute14',null)
739 ,p_attribute15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute15',null)
740 ,p_attribute16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute16',null)
741 ,p_attribute17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute17',null)
742 ,p_attribute18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute18',null)
743 ,p_attribute19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute19',null)
744 ,p_attribute20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute20',null)
745 ,p_object_version_number => l_object_version_number
746 ,p_return_status => l_return_status
747 );
748
749 elsif l_postState = '2' then
750
751 l_medical_id := hr_transaction_swi.getNumberValue(l_CommitNode,'MedicalId',null);
752
753 update_gb_absence_evidence
754 (p_validate => p_validate
755 ,p_effective_date => p_effective_date
756 ,p_person_id => null
757 ,p_business_group_id => null
758 ,p_absence_attendance_type_id => null
759 ,p_evidence_date => hr_transaction_swi.getDateValue(l_CommitNode,'EvidenceDate')
760 ,p_evidence_source => hr_transaction_swi.getVarchar2Value(l_CommitNode,'EvidenceSource')
761 ,p_absence_attendance_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceAttendanceId')
762 ,p_maternity_id => null
763 ,p_medical_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'MedicalType')
764 ,p_self_certificate => hr_transaction_swi.getVarchar2Value(l_CommitNode,'SelfCertificate')
765 ,p_predicted_fitness_date => hr_transaction_swi.getDateValue(l_CommitNode,'PredictedFitnessDate')
766 ,p_evidence_status => hr_transaction_swi.getVarchar2Value(l_CommitNode,'EvidenceStatus')
767 ,p_evidence_details => hr_transaction_swi.getVarchar2Value(l_CommitNode,'EvidenceDetails')
768 ,p_accept_late_evidence_flag => null
769 ,p_reason_for_late_evidence => null
770 ,p_evidence_received_date => hr_transaction_swi.getDateValue(l_CommitNode,'EvidenceReceivedDate')
771 ,p_attribute_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AttributeCategory')
772 ,p_attribute1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute1')
773 ,p_attribute2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute2')
774 ,p_attribute3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute3')
775 ,p_attribute4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute4')
776 ,p_attribute5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute5')
777 ,p_attribute6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute6')
778 ,p_attribute7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute7')
779 ,p_attribute8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute8')
780 ,p_attribute9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute9')
781 ,p_attribute10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute10')
782 ,p_attribute11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute11')
783 ,p_attribute12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute12')
784 ,p_attribute13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute13')
785 ,p_attribute14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute14')
786 ,p_attribute15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute15')
787 ,p_attribute16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute16')
788 ,p_attribute17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute17')
789 ,p_attribute18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute18')
790 ,p_attribute19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute19')
791 ,p_attribute20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute20')
792 ,p_medical_id => l_medical_id
793 ,p_object_version_number => l_object_version_number
794 ,p_return_status => l_return_status
795 );
796
797 elsif l_postState = '3' then
798
799 delete_gb_absence_evidence
800 (p_validate => p_validate
801 ,p_absence_attendance_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AbsenceAttendanceId')
802 ,p_medical_id => hr_transaction_swi.getNumberValue(l_CommitNode,'MedicalId')
803 ,p_object_version_number => l_object_version_number
804 ,p_return_status => l_return_status
805 );
806
807 end if;
808
809 p_return_status := l_return_status;
810 hr_utility.set_location('Exiting:' || l_proc,40);
811 EXCEPTION
812 WHEN OTHERS THEN
813 rollback to gb_abs_evidence_process_api;
814 hr_utility.trace('Exception in .process_api:' || SQLERRM );
815 hr_utility.set_location(' Leaving:' || l_proc,50);
816
817 raise;
818
819 END process_api;
820
821 end PER_GB_ABSENCE_EVIDENCE_SWI;