1 PACKAGE BODY HR_ABSUTIL_SS AS
2 /* $Header: hrabsutlss.pkb 120.13.12020000.2 2012/09/04 10:06:13 narpalan ship $ */
3
4 -- Package Variables
5 --
6 -- Package Variables
7 --
8 g_package constant varchar2(14) := 'HR_ABSUTIL_SS.';
9 g_debug boolean ;
10
11
12 function getStartDate(p_transaction_id in number,
13 p_absence_attendance_id in number) return date
14
15 IS
16 c_proc constant varchar2(30) := 'getStartDate';
17 lv_startDate hr_api_transaction_steps.Information1%type;
18 begin
19 g_debug := hr_utility.debug_enabled;
20 if g_debug then
21 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
22 end if;
23
24 if(p_transaction_id is not null) then
25 begin
26 select nvl(Information1,Information3)
27 into lv_startDate
28 from hr_api_transaction_steps
29 where transaction_id=p_transaction_id;
30
31 exception
32 when others then
33 null;
34 lv_startDate:=null;
35 end;
36 end if;
37 if(lv_startDate is not null) then
38 return fnd_date.canonical_to_date(lv_startDate);
39 else
40 return null;
41 end if;
42 if g_debug then
43 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
44 end if;
45
46 exception
47 when others then
48 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
49 Wf_Core.Context(g_package, c_proc, p_transaction_id);
50 -- raise;
51 return null;
52 end getStartDate;
53
54 function getEndDate(p_transaction_id in number,
55 p_absence_attendance_id in number) return date
56
57 IS
58 c_proc constant varchar2(30) := 'getEndDate';
59 lv_EndDate hr_api_transaction_steps.Information1%type;
60 begin
61 g_debug := hr_utility.debug_enabled;
62 if g_debug then
63 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
64 end if;
65 if(p_transaction_id is not null) then
66 begin
67 select nvl(Information2,Information4)
68 into lv_EndDate
69 from hr_api_transaction_steps
70 where transaction_id=p_transaction_id;
71
72 exception
73 when others then
74 null;
75 lv_EndDate:=null;
76 end;
77 end if;
78 if(lv_EndDate is not null) then
82 end if;
79 return fnd_date.canonical_to_date(lv_EndDate);
80 else
81 return null;
83
84 if g_debug then
85 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
86 end if;
87
88 exception
89 when others then
90 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
91 Wf_Core.Context(g_package, c_proc, p_transaction_id);
92 -- raise;
93 return null;
94 end getEndDate;
95
96 function getAbsenceType(p_transaction_id in number,
97 p_absence_attendance_id in number) return varchar2
98
99 IS
100 c_proc constant varchar2(30) := 'getAbsenceType';
101 lv_AbsenceTypeId PER_ABSENCE_ATTENDANCE_TYPES.absence_attendance_type_id%type;
102 lv_AbsenceType PER_ABSENCE_ATTENDANCE_TYPES.name%type;
103 begin
104 g_debug := hr_utility.debug_enabled;
105 if g_debug then
106 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
107 end if;
108 -- hr_general.decode_lookup('ABSENCE_CATEGORY',hats.Information4)
109 if(p_transaction_id is not null) then
110 begin
111 select Information5
112 into lv_AbsenceTypeId
113 from hr_api_transaction_steps
114 where transaction_id=p_transaction_id;
115
116 exception
117 when others then
118 null;
119 lv_AbsenceTypeId:=null;
120 end;
121 if(lv_AbsenceTypeId is not null) then
122 begin
123 select name
124 into lv_AbsenceType
125 from PER_ABS_ATTENDANCE_TYPES_TL
126 where ABSENCE_ATTENDANCE_TYPE_ID=lv_AbsenceTypeId
127 and language = userenv('LANG');
128 exception
129 when others then
130 lv_AbsenceType:=null;
131 end;
132 end if;
133 end if;
134
135 return lv_AbsenceType;
136
137 if g_debug then
138 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
139 end if;
140
141 exception
142 when others then
143 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
144 Wf_Core.Context(g_package, c_proc, p_transaction_id);
145 raise;
146 end getAbsenceType;
147
148
149
150 function getAbsenceCategory(p_transaction_id in number,
151 p_absence_attendance_id in number) return varchar2
152
153 IS
154 c_proc constant varchar2(30) := 'getAbsenceCategory';
155 lv_AbsenceCategory hr_api_transaction_steps.Information6%type;
156 begin
157 g_debug := hr_utility.debug_enabled;
158 if g_debug then
159 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
160 end if;
161
162 /*and paat.absence_attendance_type_id =FND_NUMBER.canonical_to_number(nvl(hats.Information3,'0'))
163 and fcl.lookup_type(+) = 'ABSENCE_CATEGORY'
164 and paat.absence_category = fcl.lookup_code(+)
165 and ((hr_api.return_legislation_code(paat.business_group_id) = 'GB'
166 and paat.absence_category not in
167 ('M','GB_PAT_ADO','GB_PAT_BIRTH','GB_ADO'))
168 or
169 (hr_api.return_legislation_code(paat.business_group_id) <> 'GB'
170 and paat.absence_category not in
171 ('GB_PAT_ADO','GB_PAT_BIRTH','GB_ADO')))*/
172
173 if(p_transaction_id is not null) then
174 begin
175 select Information6
176 into lv_AbsenceCategory
177 from hr_api_transaction_steps
178 where transaction_id=p_transaction_id;
179 exception
180 when others then
181 lv_AbsenceCategory:=null;
182 end;
183 end if;
184 return lv_AbsenceCategory;
185 if g_debug then
186 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
187 end if;
188
189 exception
190 when others then
191 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
192 Wf_Core.Context(g_package, c_proc, p_transaction_id);
193 raise;
194 end getAbsenceCategory;
195
196
197 -- Added for the BUG#14327573
198 function getAbsenceCategoryCode(p_transaction_id in number,
199 p_absence_attendance_id in number) return varchar2
200
201 IS
202 c_proc constant varchar2(30) := 'getAbsenceCategoryCode';
203 lv_AbsenceCategory hr_api_transaction_steps.Information6%type;
204 l_lookup_code_val varchar2(10);
205
206 Cursor c_lookup_code(AbsenceCat varchar2) is select LOOKUP_CODE from fnd_lookup_values flv
207 where flv.MEANING = AbsenceCat and flv.lookup_type = 'ABSENCE_CATEGORY';
208 begin
209 g_debug := hr_utility.debug_enabled;
210 if g_debug then
211 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
212 end if;
213
214 if(p_transaction_id is not null) then
215 begin
216 select Information6
217 into lv_AbsenceCategory
218 from hr_api_transaction_steps
219 where transaction_id=p_transaction_id;
220 exception
221 when others then
222 lv_AbsenceCategory:=null;
223 end;
224 end if;
225
226 open c_lookup_code(lv_AbsenceCategory);
227 fetch c_lookup_code into l_lookup_code_val;
228 close c_lookup_code;
229
230 if g_debug then
231 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
232 end if;
233 return l_lookup_code_val;
234 exception
235 when others then
239 end getAbsenceCategoryCode;
236 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
237 Wf_Core.Context(g_package, c_proc, p_transaction_id);
238 raise;
240
241
242 function getAbsenceHoursDuration(p_transaction_id in number,
243 p_absence_attendance_id in number) return number
244
245 IS
246 c_proc constant varchar2(30) := 'getAbsenceHoursDuration';
247 lv_AbsenceHoursDuration hr_api_transaction_steps.Information7%type;
248 begin
249 g_debug := hr_utility.debug_enabled;
250 if g_debug then
251 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
252 end if;
253 if(p_transaction_id is not null) then
254 begin
255 select Information7
256 into lv_AbsenceHoursDuration
257 from hr_api_transaction_steps
258 where transaction_id=p_transaction_id;
259 exception
260 when others then
261 lv_AbsenceHoursDuration:=null;
262 end;
263 end if;
264 -- Fix for bug 7712861
265 return fnd_number.canonical_to_number(lv_AbsenceHoursDuration);
266
267 if g_debug then
268 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
269 end if;
270
271 exception
272 when others then
273 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
274 Wf_Core.Context(g_package, c_proc, p_transaction_id);
275 raise;
276 end getAbsenceHoursDuration;
277
278
279 function getAbsenceDaysDuration(p_transaction_id in number,
280 p_absence_attendance_id in number) return number
281
282 IS
283 c_proc constant varchar2(30) := 'getAbsenceDaysDuration';
284 lv_AbsenceDaysDuration hr_api_transaction_steps.Information8%type;
285 begin
286 g_debug := hr_utility.debug_enabled;
287 if g_debug then
288 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
289 end if;
290 if(p_transaction_id is not null) then
291 begin
292 select Information8
293 into lv_AbsenceDaysDuration
294 from hr_api_transaction_steps
295 where transaction_id=p_transaction_id;
296 exception
297 when others then
298 lv_AbsenceDaysDuration:=null;
299 end;
300 end if;
301 --Fix for Bug 13076997
302 return fnd_number.canonical_to_number(lv_AbsenceDaysDuration);
303
304
305 if g_debug then
306 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
307 end if;
308
309 exception
310 when others then
311 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
312 Wf_Core.Context(g_package, c_proc, p_transaction_id);
313 raise;
314 end getAbsenceDaysDuration;
315
316
317
318 function getApprovalStatus(p_transaction_id in number,
319 p_absence_attendance_id in number) return varchar2
320
321 IS
322 c_proc constant varchar2(30) := 'getApprovalStatus';
323 lv_approvalStatus fnd_lookup_values.meaning%type;
324 begin
325 g_debug := hr_utility.debug_enabled;
326 if g_debug then
327 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
328 end if;
329 /*decode(hat.status,'RI',hr_general.decode_lookup('PQH_SS_TRANSACTION_STATUS','C'),
330 'RO',hr_general.decode_lookup('PQH_SS_TRANSACTION_STATUS','C'),
331 hrl.meaning)*/
332 if(p_transaction_id is not null) then
333 begin
334 select hr_general.decode_lookup('PQH_SS_TRANSACTION_STATUS',
335 decode(status,'RI','C',
336 'RIS','S',
337 'RO','Y',
338 'ROS','Y',
339 'YS','Y',
340 status))
341 into lv_approvalStatus
342 from hr_api_transactions
343 where transaction_id=p_transaction_id;
344 exception
345 when others then
346 lv_approvalStatus:=null;
347 end;
348 end if;
349 return lv_approvalStatus;
350
351 if g_debug then
352 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
353 end if;
354
355 exception
356 when others then
357 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
358 Wf_Core.Context(g_package, c_proc, p_transaction_id);
359 raise;
360 end getApprovalStatus;
361
362
363 function getApprovalStatusCode(p_transaction_id in number,
364 p_absence_attendance_id in number) return varchar2
365
366 IS
367 c_proc constant varchar2(30) := 'getApprovalStatusCode';
368 lv_approvalStatusCode varchar2(30);
369 begin
370 g_debug := hr_utility.debug_enabled;
371 if g_debug then
372 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
373 end if;
374 /*decode(hat.status,'RI',hr_general.decode_lookup('PQH_SS_TRANSACTION_STATUS','C'),
375 'RO',hr_general.decode_lookup('PQH_SS_TRANSACTION_STATUS','C'),
376 hrl.meaning)*/
377 if(p_transaction_id is not null) then
378 begin
379 select decode(status,'RI','C',
380 'RIS','S',
381 'RO','Y',
382 'ROS','Y',
383 'YS','Y',
384 status)
385 into lv_approvalStatusCode
386 from hr_api_transactions
390 when others then
387 where transaction_id=p_transaction_id;
388
389 exception
391 lv_approvalStatusCode:=null;
392 end;
393 end if;
394 return lv_approvalStatusCode;
395
396 if g_debug then
397 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
398 end if;
399
400 exception
401 when others then
402 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
403 Wf_Core.Context(g_package, c_proc, p_transaction_id);
404 raise;
405 end getApprovalStatusCode;
406
407
408 function getAbsenceStatus(p_transaction_id in number,
409 p_absence_attendance_id in number) return varchar2
410
411 IS
412 c_proc constant varchar2(30) := 'getAbsenceStatus';
413 lv_AbsenceStatus hr_api_transaction_steps.Information9%type;
414 begin
415 g_debug := hr_utility.debug_enabled;
416 if g_debug then
417 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
418 end if;
419 if(p_transaction_id is not null) then
420 begin
421 select (SELECT meaning
422 from fnd_lookup_values
423 where lookup_type ='ABSENCE_STATUS'
424 and fnd_lookup_values.lookup_code=Information9
425 and language = userenv('LANG')
426 )
427 into lv_AbsenceStatus
428 from hr_api_transaction_steps
429 where transaction_id=p_transaction_id;
430 exception
431 when others then
432 lv_AbsenceStatus:=null;
433 end;
434 end if;
435 return lv_AbsenceStatus;
436
437 if g_debug then
438 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
439 end if;
440
441 exception
442 when others then
443 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
444 Wf_Core.Context(g_package, c_proc, p_transaction_id);
445 raise;
446 end getAbsenceStatus;
447
448 function isUpdateAllowed(p_transaction_id in number,
449 p_absence_attendance_id in number,
450 p_transaction_status in varchar2) return varchar2
451
452 IS
453 c_proc constant varchar2(30) := 'isUpdateAllowed';
454 lv_UpdateAllowed varchar2(30);
455 begin
456 g_debug := hr_utility.debug_enabled;
457 if g_debug then
458 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
459 end if;
460 /*
461 decode (hat.status,'W', 'HrUpdateEnabled',
462 'S','HrUpdateEnabled',
463 'RI','HrUpdateEnabled',
464 'HrUpdateDisabled')
465 */
466 -- need to revisit with the common code for handling update
467 -- based on the current transaction owner
468
469 -- for now this will only allow for transaction owner to update
470
471 if(p_transaction_id is not null) then
472 if(hr_transaction_swi.istxnowner(p_transaction_id,fnd_global.employee_id)
473 and p_transaction_status in ('W','S','RI','RIS')) then
474 lv_UpdateAllowed := 'HrUpdateEnabled';
475 else
476 lv_UpdateAllowed := 'HrUpdateDisabled';
477 end if;
478 end if;
479
480 return lv_UpdateAllowed;
481
482 if g_debug then
483 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
484 end if;
485
486 exception
487 when others then
488 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
489 Wf_Core.Context(g_package, c_proc, p_transaction_id);
490 raise;
491 end isUpdateAllowed;
492
493 function isConfirmAllowed(p_transaction_id in number,
494 p_absence_attendance_id in number) return varchar2
495
496 IS
497 c_proc constant varchar2(30) := 'isConfirmAllowed';
498 lv_item_type wf_item_activity_statuses.item_type%type;
499 lv_item_key wf_item_activity_statuses.item_key%type;
500 begin
501 g_debug := hr_utility.debug_enabled;
502 if g_debug then
503 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
504 end if;
505
506 return 'HrConfirmDisabled';
507
508 if g_debug then
509 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
510 end if;
511
512 exception
513 when others then
514 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
515 Wf_Core.Context(g_package, c_proc, p_transaction_id);
516 raise;
517 end isConfirmAllowed;
518
519
520 function isCancelAllowed(p_transaction_id in number,
521 p_absence_attendance_id in number,
522 p_transaction_status in varchar2) return varchar2
523
524 IS
525 c_proc constant varchar2(30) := 'isCancelAllowed';
526 lv_CancelAllowed varchar2(30);
527 begin
528 g_debug := hr_utility.debug_enabled;
529 if g_debug then
530 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
531 end if;
532 /*decode(hat.status,'W','HrCancelEnabled',
533 'S','HrCancelEnabled',
534 'RI','HrCancelEnabled',
535 'HrCancelDisabled')*/
536 if(p_transaction_id is not null) then
537 if(hr_transaction_swi.istxnowner(p_transaction_id,fnd_global.employee_id)
538 and p_transaction_status in ('W','S','RI','RIS')) then
542 end if;
539 lv_CancelAllowed := 'HrCancelEnabled';
540 else
541 lv_CancelAllowed := 'HrCancelDisabled';
543 end if;
544
545 return lv_CancelAllowed;
546
547 if g_debug then
548 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
549 end if;
550
551 exception
552 when others then
553 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
554 Wf_Core.Context(g_package, c_proc, p_transaction_id);
555 raise;
556 end isCancelAllowed;
557
558 function hasSupportingDocuments(p_transaction_id in number,
559 p_absence_attendance_id in number) return varchar2
560
561 IS
562 c_proc constant varchar2(30) := 'hasSupportingDocuments';
563 lv_item_type wf_item_activity_statuses.item_type%type;
567 l_exists VARCHAR2(1);
564 lv_item_key wf_item_activity_statuses.item_key%type;
565 lv_entity_name constant varchar2(50) := 'PER_ABSENCE_ATTENDANCES';
566 lv_pkey1 fnd_attached_documents.pk1_value%type;
568
569 begin
570 g_debug := hr_utility.debug_enabled;
571 if g_debug then
572 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
573 end if;
574
575 if(p_transaction_id is not null) then
576 lv_pkey1 :=p_absence_attendance_id||'_'||p_transaction_id;
577 else
578 lv_pkey1 :=p_absence_attendance_id;
579 end if;
580
581 begin
582 SELECT 'Y'
583 INTO l_exists
584 from fnd_attached_documents
585 where entity_name=lv_entity_name
586 and pk1_value=lv_pkey1
587 AND ROWNUM = 1;
588 exception
589 when no_data_found then
590 l_exists := 'N';
591 end;
592
593 IF (l_exists<>'Y') THEN
594 return('N');
595 ELSE
596 return('Y');
597 END IF;
598
599 if g_debug then
600 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
601 end if;
602
603 exception
604 when others then
605 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
606 Wf_Core.Context(g_package, c_proc, p_transaction_id);
607 raise;
608 end hasSupportingDocuments;
609
610
611 procedure getAbsenceNotificationDetails(p_transaction_id in number
612 ,p_notification_subject out nocopy varchar2)
613
614
615 IS
616 c_proc constant varchar2(30) := 'getAbsenceNotificationDetails';
617 lv_item_type wf_item_activity_statuses.item_type%type;
618 lv_item_key wf_item_activity_statuses.item_key%type;
619 lv_status hr_api_transactions.status%type;
620 ln_notification_id wf_notifications.notification_id%type;
621 begin
622 g_debug := hr_utility.debug_enabled;
623 if g_debug then
624 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
625 end if;
626
627 begin
628 select status,item_type,item_key
629 into lv_status,lv_item_type,lv_item_key
630 from hr_api_transactions
631 where transaction_id=p_transaction_id;
632 exception
633 when others then
634 null;
635 end;
636
637 if(lv_status in ('S','RIS'))then
638 begin
639 select item_type, item_key
640 into lv_item_type,lv_item_key
641 from wf_items
642 where user_key=p_transaction_id
643 and rownum<2;
644 exception
645 when no_data_found then
646 null;
647 end;
648
649 end if;
650
651
652
653 -- get the ntf id
654 begin
655 select notification_id
656 into ln_notification_id
657 FROM WF_ITEM_ACTIVITY_STATUSES IAS
658 WHERE ias.item_type = lv_item_type
659 and ias.item_key = lv_item_key
660 and ias.activity_status = 'NOTIFIED'
661 and ias.notification_id is not null
662 and rownum<=1;
663 exception
664 when others then
665 null;
666 end;
667
668 if(ln_notification_id is not null) then
669 p_notification_subject:= wf_notification.getsubject(ln_notification_id);
670 end if;
671
672 if g_debug then
673 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
674 end if;
675
676 exception
677 when others then
678 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
679 Wf_Core.Context(g_package, c_proc, p_transaction_id);
680 raise;
684 p_absence_attendance_type_id in number
681 end getAbsenceNotificationDetails;
682
683 function getAbsDurDays(
685 ,p_business_group_id in number
686 ,p_effective_date in date
687 ,p_person_id in number
688 ,p_date_start in date
689 ,p_date_end in date
690 ,p_time_start in varchar2
691 ,p_time_end in varchar2)
692 return number
693 is
694 c_proc constant varchar2(30) := 'getAbsDurDays';
695 p_absence_days number;
696 p_absence_hours number;
697 p_use_formula number;
698 p_min_max_failure varchar2(1);
699 p_warning_or_error varchar2(1);
700 p_page_error_msg fnd_new_messages.message_text%TYPE;
701
702
703 begin
704
705 g_debug := hr_utility.debug_enabled;
706 if g_debug then
707 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
708 end if;
709
710 hr_loa_ss.calculate_absence_duration
711 (p_absence_attendance_type_id
712 ,p_business_group_id
713 ,p_effective_date
714 ,p_person_id
715 ,p_date_start
716 ,p_date_end
717 ,p_time_start
718 ,p_time_end
719 ,p_absence_days
720 ,p_absence_hours
721 ,p_use_formula
722 ,p_min_max_failure
723 ,p_warning_or_error
724 ,p_page_error_msg );
725
726
727 return p_absence_days;
728
729
730 exception
731 when others then
732 raise;
733
734 end getAbsDurDays;
735
736
737 function getAbsDurHours(
738 p_absence_attendance_type_id in number
739 ,p_business_group_id in number
740 ,p_effective_date in date
741 ,p_person_id in number
742 ,p_date_start in date
743 ,p_date_end in date
744 ,p_time_start in varchar2
745 ,p_time_end in varchar2)
746 return number
747 is
748 c_proc constant varchar2(30) := 'getAbsDurHours';
749 p_absence_days number;
750 p_absence_hours number;
751 p_use_formula number;
752 p_min_max_failure varchar2(1);
753 p_warning_or_error varchar2(1);
754 p_page_error_msg fnd_new_messages.message_text%TYPE;
755
756 Begin
757 g_debug := hr_utility.debug_enabled;
758 if g_debug then
759 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
760 end if;
761
762 hr_loa_ss.calculate_absence_duration
763 (p_absence_attendance_type_id
764 ,p_business_group_id
765 ,p_effective_date
766 ,p_person_id
767 ,p_date_start
768 ,p_date_end
769 ,p_time_start
770 ,p_time_end
771 ,p_absence_days
775 ,p_warning_or_error
772 ,p_absence_hours
773 ,p_use_formula
774 ,p_min_max_failure
776 ,p_page_error_msg );
777
778
779 return p_absence_hours;
780
781
782 exception
783 when others then
784 raise;
785
786 end getAbsDurHours;
787
791 c_proc constant varchar2(30) := 'getAbsenceStatusValue';
788 function getAbsenceStatusValue(p_transaction_id in Varchar2) return varchar2
789
790 IS
792 lv_AbsenceStatus hr_api_transaction_steps.Information9%type;
793 begin
794 g_debug := hr_utility.debug_enabled;
795 if g_debug then
796 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
797 end if;
798 if(p_transaction_id is not null) then
799 begin
800 select (SELECT meaning
801 from fnd_lookup_values
802 where lookup_type ='ABSENCE_STATUS'
803 and fnd_lookup_values.lookup_code=Information9
804 and language = userenv('LANG')
805 )
806 into lv_AbsenceStatus
807 from hr_api_transaction_steps
808 where transaction_id=p_transaction_id;
809 exception
810 when others then
811 lv_AbsenceStatus:=null;
812 end;
813 end if;
814 return lv_AbsenceStatus;
815
816 if g_debug then
817 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
818 end if;
819
820 exception
821 when others then
822 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
823 Wf_Core.Context(g_package, c_proc, p_transaction_id);
824 raise;
825 end getAbsenceStatusValue;
826
827 procedure delete_transaction
828 (p_transaction_id in number)
829 is
830
831
832 lv_result varchar2(100);
833 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
834 lr_wf_items_rec wf_items%rowtype := null;
835 l_user_key varchar2(50) := null;
836
837 begin
838
839 if(p_transaction_id is not null) then
840 begin
841 select * into lr_hr_api_transaction_rec
842 from hr_api_transactions
843 where transaction_id=p_transaction_id;
844 exception
845 when others then
846 raise;
847 end;
848
849 if(lr_hr_api_transaction_rec.item_type is not null and
850 lr_hr_api_transaction_rec.item_key is not null) then
851
852 hr_sflutil_ss.closesflnotifications(p_transaction_id
853 ,lr_hr_api_transaction_rec.item_type
854 ,lr_hr_api_transaction_rec.item_key);
855
856 hr_transaction_ss.rollback_transaction(lr_hr_api_transaction_rec.item_type,
857 lr_hr_api_transaction_rec.item_key,
858 null,
859 wf_engine.eng_run,
860 lv_result);
861 wf_engine.abortprocess(itemtype => lr_hr_api_transaction_rec.item_type
862 ,itemkey => lr_hr_api_transaction_rec.item_key
863 ,process =>null
864 ,result => wf_engine.eng_force
865 ,verify_lock => true
866 ,cascade => true);
867 else
868
869 hr_sflutil_ss.closesflnotifications(p_transaction_id,null,null);
870 hr_transaction_api.rollback_transaction
871 (p_transaction_id => p_transaction_id);
872
873 end if;
874 end if;
875
876 exception
877 when others then
878 raise;
879 end delete_transaction;
880
881 procedure remove_absence_transaction(p_absence_attendance_id in number)
882 is
883
884 cursor c_trans_rec(p_absence_attendance_id number) is
885
886 select transaction_id from hr_api_transactions hat
887 where hat.transaction_ref_id = p_absence_attendance_id
888 and hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
889 and hat.status not in ('AC','Y');
890
891 trans_record c_trans_rec%ROWTYPE;
892 begin
893
894 OPEN c_trans_rec(p_absence_attendance_id);
895 LOOP
896 FETCH c_trans_rec into trans_record;
897 EXIT WHEN c_trans_rec%NOTFOUND;
898
899 delete_transaction(trans_record.transaction_id);
900
901 END LOOP;
902 CLOSE c_trans_rec;
903
904
905 exception
906
907 when others then
908 raise;
909
910 end remove_absence_transaction;
911
912 END HR_ABSUTIL_SS;
[Home] [Help]