1 PACKAGE BODY HR_ABSUTIL_SS AS
2 /* $Header: hrabsutlss.pkb 120.7 2007/12/03 16:58:09 rachakra 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
79 return fnd_date.canonical_to_date(lv_EndDate);
80 else
81 return null;
82 end if;
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 function getAbsenceHoursDuration(p_transaction_id in number,
197 p_absence_attendance_id in number) return number
198
199 IS
200 c_proc constant varchar2(30) := 'getAbsenceHoursDuration';
201 lv_AbsenceHoursDuration hr_api_transaction_steps.Information7%type;
202 begin
203 g_debug := hr_utility.debug_enabled;
204 if g_debug then
205 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
206 end if;
207 if(p_transaction_id is not null) then
208 begin
209 select Information7
210 into lv_AbsenceHoursDuration
211 from hr_api_transaction_steps
212 where transaction_id=p_transaction_id;
213 exception
214 when others then
215 lv_AbsenceHoursDuration:=null;
216 end;
217 end if;
218 return fnd_number.number_to_canonical(nvl(lv_AbsenceHoursDuration,0));
219
220 if g_debug then
221 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
222 end if;
223
224 exception
225 when others then
226 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
227 Wf_Core.Context(g_package, c_proc, p_transaction_id);
228 raise;
229 end getAbsenceHoursDuration;
230
231
232 function getAbsenceDaysDuration(p_transaction_id in number,
233 p_absence_attendance_id in number) return number
234
235 IS
236 c_proc constant varchar2(30) := 'getAbsenceDaysDuration';
237 lv_AbsenceDaysDuration hr_api_transaction_steps.Information8%type;
238 begin
239 g_debug := hr_utility.debug_enabled;
240 if g_debug then
241 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
242 end if;
243 if(p_transaction_id is not null) then
244 begin
245 select Information8
246 into lv_AbsenceDaysDuration
247 from hr_api_transaction_steps
248 where transaction_id=p_transaction_id;
249 exception
250 when others then
251 lv_AbsenceDaysDuration:=null;
252 end;
253 end if;
254 return lv_AbsenceDaysDuration;
255
256 if g_debug then
257 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
258 end if;
259
260 exception
261 when others then
262 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
263 Wf_Core.Context(g_package, c_proc, p_transaction_id);
264 raise;
265 end getAbsenceDaysDuration;
266
267
268
269 function getApprovalStatus(p_transaction_id in number,
270 p_absence_attendance_id in number) return varchar2
271
272 IS
273 c_proc constant varchar2(30) := 'getApprovalStatus';
274 lv_approvalStatus fnd_lookup_values.meaning%type;
275 begin
276 g_debug := hr_utility.debug_enabled;
277 if g_debug then
278 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
279 end if;
280 /*decode(hat.status,'RI',hr_general.decode_lookup('PQH_SS_TRANSACTION_STATUS','C'),
281 'RO',hr_general.decode_lookup('PQH_SS_TRANSACTION_STATUS','C'),
282 hrl.meaning)*/
283 if(p_transaction_id is not null) then
284 begin
285 select hr_general.decode_lookup('PQH_SS_TRANSACTION_STATUS',
286 decode(status,'RI','C',
287 'RIS','S',
288 'RO','Y',
289 'ROS','Y',
290 'YS','Y',
291 status))
292 into lv_approvalStatus
293 from hr_api_transactions
294 where transaction_id=p_transaction_id;
295 exception
296 when others then
297 lv_approvalStatus:=null;
298 end;
299 end if;
300 return lv_approvalStatus;
301
302 if g_debug then
303 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
304 end if;
305
306 exception
307 when others then
308 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
309 Wf_Core.Context(g_package, c_proc, p_transaction_id);
310 raise;
311 end getApprovalStatus;
312
313
314 function getApprovalStatusCode(p_transaction_id in number,
315 p_absence_attendance_id in number) return varchar2
316
317 IS
318 c_proc constant varchar2(30) := 'getApprovalStatusCode';
319 lv_approvalStatusCode varchar2(30);
320 begin
321 g_debug := hr_utility.debug_enabled;
322 if g_debug then
323 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
324 end if;
325 /*decode(hat.status,'RI',hr_general.decode_lookup('PQH_SS_TRANSACTION_STATUS','C'),
326 'RO',hr_general.decode_lookup('PQH_SS_TRANSACTION_STATUS','C'),
327 hrl.meaning)*/
328 if(p_transaction_id is not null) then
329 begin
330 select decode(status,'RI','C',
331 'RIS','S',
332 'RO','Y',
333 'ROS','Y',
334 'YS','Y',
335 status)
336 into lv_approvalStatusCode
337 from hr_api_transactions
338 where transaction_id=p_transaction_id;
339
340 exception
341 when others then
342 lv_approvalStatusCode:=null;
343 end;
344 end if;
345 return lv_approvalStatusCode;
346
347 if g_debug then
348 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
349 end if;
350
351 exception
352 when others then
353 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
354 Wf_Core.Context(g_package, c_proc, p_transaction_id);
355 raise;
356 end getApprovalStatusCode;
357
358
359 function getAbsenceStatus(p_transaction_id in number,
360 p_absence_attendance_id in number) return varchar2
361
362 IS
363 c_proc constant varchar2(30) := 'getAbsenceStatus';
364 lv_AbsenceStatus hr_api_transaction_steps.Information9%type;
365 begin
366 g_debug := hr_utility.debug_enabled;
367 if g_debug then
368 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
369 end if;
370 if(p_transaction_id is not null) then
371 begin
372 select (SELECT meaning
373 from fnd_lookup_values
374 where lookup_type ='ABSENCE_STATUS'
375 and fnd_lookup_values.lookup_code=Information9
376 and language = userenv('LANG')
377 )
378 into lv_AbsenceStatus
379 from hr_api_transaction_steps
380 where transaction_id=p_transaction_id;
381 exception
382 when others then
383 lv_AbsenceStatus:=null;
384 end;
385 end if;
386 return lv_AbsenceStatus;
387
388 if g_debug then
389 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
390 end if;
391
392 exception
393 when others then
394 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
395 Wf_Core.Context(g_package, c_proc, p_transaction_id);
396 raise;
397 end getAbsenceStatus;
398
399 function isUpdateAllowed(p_transaction_id in number,
400 p_absence_attendance_id in number,
401 p_transaction_status in varchar2) return varchar2
402
403 IS
404 c_proc constant varchar2(30) := 'isUpdateAllowed';
405 lv_UpdateAllowed varchar2(30);
406 begin
407 g_debug := hr_utility.debug_enabled;
408 if g_debug then
409 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
410 end if;
411 /*
412 decode (hat.status,'W', 'HrUpdateEnabled',
413 'S','HrUpdateEnabled',
414 'RI','HrUpdateEnabled',
415 'HrUpdateDisabled')
416 */
417 -- need to revisit with the common code for handling update
418 -- based on the current transaction owner
419
420 -- for now this will only allow for transaction owner to update
421
422 if(p_transaction_id is not null) then
423 if(hr_transaction_swi.istxnowner(p_transaction_id,fnd_global.employee_id)
424 and p_transaction_status in ('W','S','RI','RIS')) then
425 lv_UpdateAllowed := 'HrUpdateEnabled';
426 else
430
427 lv_UpdateAllowed := 'HrUpdateDisabled';
428 end if;
429 end if;
431 return lv_UpdateAllowed;
432
433 if g_debug then
434 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
435 end if;
436
437 exception
438 when others then
439 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
440 Wf_Core.Context(g_package, c_proc, p_transaction_id);
441 raise;
442 end isUpdateAllowed;
443
444 function isConfirmAllowed(p_transaction_id in number,
445 p_absence_attendance_id in number) return varchar2
446
447 IS
448 c_proc constant varchar2(30) := 'isConfirmAllowed';
449 lv_item_type wf_item_activity_statuses.item_type%type;
450 lv_item_key wf_item_activity_statuses.item_key%type;
451 begin
452 g_debug := hr_utility.debug_enabled;
453 if g_debug then
454 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
455 end if;
456
457 return 'HrConfirmDisabled';
458
459 if g_debug then
460 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
461 end if;
462
463 exception
464 when others then
465 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
466 Wf_Core.Context(g_package, c_proc, p_transaction_id);
467 raise;
468 end isConfirmAllowed;
469
470
471 function isCancelAllowed(p_transaction_id in number,
472 p_absence_attendance_id in number,
473 p_transaction_status in varchar2) return varchar2
474
475 IS
476 c_proc constant varchar2(30) := 'isCancelAllowed';
477 lv_CancelAllowed varchar2(30);
478 begin
479 g_debug := hr_utility.debug_enabled;
480 if g_debug then
481 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
482 end if;
483 /*decode(hat.status,'W','HrCancelEnabled',
484 'S','HrCancelEnabled',
485 'RI','HrCancelEnabled',
486 'HrCancelDisabled')*/
487 if(p_transaction_id is not null) then
488 if(hr_transaction_swi.istxnowner(p_transaction_id,fnd_global.employee_id)
489 and p_transaction_status in ('W','S','RI','RIS')) then
490 lv_CancelAllowed := 'HrCancelEnabled';
491 else
492 lv_CancelAllowed := 'HrCancelDisabled';
493 end if;
494 end if;
495
496 return lv_CancelAllowed;
497
498 if g_debug then
499 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
500 end if;
501
502 exception
503 when others then
504 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
505 Wf_Core.Context(g_package, c_proc, p_transaction_id);
506 raise;
507 end isCancelAllowed;
508
509 function hasSupportingDocuments(p_transaction_id in number,
510 p_absence_attendance_id in number) return varchar2
511
512 IS
513 c_proc constant varchar2(30) := 'hasSupportingDocuments';
514 lv_item_type wf_item_activity_statuses.item_type%type;
515 lv_item_key wf_item_activity_statuses.item_key%type;
516 lv_entity_name constant varchar2(50) := 'PER_ABSENCE_ATTENDANCES';
517 lv_pkey1 fnd_attached_documents.pk1_value%type;
518 l_exists VARCHAR2(1);
519
520 begin
521 g_debug := hr_utility.debug_enabled;
522 if g_debug then
523 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
524 end if;
525
526 if(p_transaction_id is not null) then
527 lv_pkey1 :=p_absence_attendance_id||'_'||p_transaction_id;
528 else
529 lv_pkey1 :=p_absence_attendance_id;
530 end if;
531
532 begin
533 SELECT 'Y'
534 INTO l_exists
535 from fnd_attached_documents
536 where entity_name=lv_entity_name
537 and pk1_value=lv_pkey1
538 AND ROWNUM = 1;
539 exception
540 when no_data_found then
541 l_exists := 'N';
542 end;
543
544 IF (l_exists<>'Y') THEN
545 return('N');
546 ELSE
547 return('Y');
548 END IF;
549
550 if g_debug then
551 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
552 end if;
553
554 exception
555 when others then
556 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
557 Wf_Core.Context(g_package, c_proc, p_transaction_id);
558 raise;
559 end hasSupportingDocuments;
560
561
562 procedure getAbsenceNotificationDetails(p_transaction_id in number
563 ,p_notification_subject out nocopy varchar2)
564
565
566 IS
567 c_proc constant varchar2(30) := 'getAbsenceNotificationDetails';
568 lv_item_type wf_item_activity_statuses.item_type%type;
569 lv_item_key wf_item_activity_statuses.item_key%type;
570 lv_status hr_api_transactions.status%type;
571 ln_notification_id wf_notifications.notification_id%type;
572 begin
573 g_debug := hr_utility.debug_enabled;
574 if g_debug then
575 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
576 end if;
577
581 from hr_api_transactions
578 begin
579 select status,item_type,item_key
580 into lv_status,lv_item_type,lv_item_key
582 where transaction_id=p_transaction_id;
583 exception
584 when others then
585 null;
586 end;
587
588 if(lv_status in ('S','RIS'))then
589 begin
590 select item_type, item_key
591 into lv_item_type,lv_item_key
592 from wf_items
593 where user_key=p_transaction_id
594 and rownum<2;
595 exception
596 when no_data_found then
597 null;
598 end;
599
600 end if;
601
602
603
604 -- get the ntf id
605 begin
606 select notification_id
607 into ln_notification_id
608 FROM WF_ITEM_ACTIVITY_STATUSES IAS
609 WHERE ias.item_type = lv_item_type
610 and ias.item_key = lv_item_key
611 and ias.activity_status = 'NOTIFIED'
612 and ias.notification_id is not null
613 and rownum<=1;
614 exception
615 when others then
616 null;
617 end;
618
619 if(ln_notification_id is not null) then
620 p_notification_subject:= wf_notification.getsubject(ln_notification_id);
621 end if;
622
623 if g_debug then
624 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
625 end if;
626
627 exception
628 when others then
629 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
630 Wf_Core.Context(g_package, c_proc, p_transaction_id);
631 raise;
632 end getAbsenceNotificationDetails;
633
634 function getAbsDurDays(
635 p_absence_attendance_type_id in number
636 ,p_business_group_id in number
637 ,p_effective_date in date
638 ,p_person_id in number
639 ,p_date_start in date
640 ,p_date_end in date
641 ,p_time_start in varchar2
642 ,p_time_end in varchar2)
643 return number
644 is
645 c_proc constant varchar2(30) := 'getAbsDurDays';
646 p_absence_days number;
647 p_absence_hours number;
648 p_use_formula number;
649 p_min_max_failure varchar2(1);
650 p_warning_or_error varchar2(1);
651 p_page_error_msg fnd_new_messages.message_text%TYPE;
652
653
654 begin
655
656 g_debug := hr_utility.debug_enabled;
657 if g_debug then
658 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
659 end if;
660
661 hr_loa_ss.calculate_absence_duration
662 (p_absence_attendance_type_id
663 ,p_business_group_id
664 ,p_effective_date
665 ,p_person_id
666 ,p_date_start
667 ,p_date_end
668 ,p_time_start
669 ,p_time_end
670 ,p_absence_days
671 ,p_absence_hours
672 ,p_use_formula
673 ,p_min_max_failure
674 ,p_warning_or_error
675 ,p_page_error_msg );
676
677
678 return p_absence_days;
679
680
681 exception
682 when others then
683 raise;
684
685 end getAbsDurDays;
686
687
688 function getAbsDurHours(
689 p_absence_attendance_type_id in number
690 ,p_business_group_id in number
691 ,p_effective_date in date
692 ,p_person_id in number
693 ,p_date_start in date
694 ,p_date_end in date
695 ,p_time_start in varchar2
696 ,p_time_end in varchar2)
697 return number
698 is
699 c_proc constant varchar2(30) := 'getAbsDurHours';
700 p_absence_days number;
701 p_absence_hours number;
702 p_use_formula number;
703 p_min_max_failure varchar2(1);
704 p_warning_or_error varchar2(1);
705 p_page_error_msg fnd_new_messages.message_text%TYPE;
706
707 Begin
708 g_debug := hr_utility.debug_enabled;
709 if g_debug then
710 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
711 end if;
712
713 hr_loa_ss.calculate_absence_duration
714 (p_absence_attendance_type_id
715 ,p_business_group_id
716 ,p_effective_date
717 ,p_person_id
718 ,p_date_start
719 ,p_date_end
720 ,p_time_start
721 ,p_time_end
722 ,p_absence_days
723 ,p_absence_hours
724 ,p_use_formula
725 ,p_min_max_failure
726 ,p_warning_or_error
727 ,p_page_error_msg );
728
729
730 return p_absence_hours;
731
732
733 exception
734 when others then
735 raise;
736
737 end getAbsDurHours;
738
739 function getAbsenceStatusValue(p_transaction_id in Varchar2) return varchar2
740
741 IS
742 c_proc constant varchar2(30) := 'getAbsenceStatusValue';
743 lv_AbsenceStatus hr_api_transaction_steps.Information9%type;
744 begin
745 g_debug := hr_utility.debug_enabled;
746 if g_debug then
747 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
748 end if;
749 if(p_transaction_id is not null) then
750 begin
751 select (SELECT meaning
752 from fnd_lookup_values
753 where lookup_type ='ABSENCE_STATUS'
754 and fnd_lookup_values.lookup_code=Information9
755 and language = userenv('LANG')
756 )
757 into lv_AbsenceStatus
758 from hr_api_transaction_steps
759 where transaction_id=p_transaction_id;
760 exception
761 when others then
762 lv_AbsenceStatus:=null;
763 end;
764 end if;
765 return lv_AbsenceStatus;
766
767 if g_debug then
768 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
769 end if;
770
771 exception
772 when others then
773 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
774 Wf_Core.Context(g_package, c_proc, p_transaction_id);
775 raise;
776 end getAbsenceStatusValue;
777
778 END HR_ABSUTIL_SS;
[Home] [Help]