[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMECARD_SUMMARY_PKG
Source
1 package body hxc_timecard_summary_pkg as
2 /* $Header: hxctcsum.pkb 120.12.12020000.4 2013/02/11 12:35:11 asrajago ship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5 g_check_for_reasons varchar2(1) := null;
6
7 function get_migration_apr_status
8 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
9 ,p_timecard_ovn in hxc_time_building_blocks.object_version_number%type
10 ) return varchar2 is
11
12 begin
13
14 return hxc_timecard_search_pkg.get_timecard_status_code(p_timecard_id,p_timecard_ovn,c_migration_mode);
15
16 end get_migration_apr_status;
17
18 procedure get_recorded_hours
19 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
20 ,p_timecard_ovn in hxc_time_building_blocks.object_version_number%type
21 ,p_hours out nocopy number
22 ,p_details out nocopy details
23 ) is
24
25 cursor c_detail_info(p_id in hxc_time_building_blocks.time_building_block_id%type
26 ,p_ovn in hxc_time_building_blocks.object_version_number%type
27 ) is
28 select details.time_building_block_id
29 ,details.object_version_number
30 ,details.start_time
31 ,details.stop_time
32 ,details.measure
33 ,details.type
34 ,details.creation_date
35 from hxc_time_building_blocks days, hxc_time_building_blocks details
36 where days.parent_building_block_id = p_id
37 and days.parent_building_block_ovn = p_ovn
38 and details.parent_building_block_id = days.time_building_block_id
39 and details.parent_building_block_ovn = days.object_version_number
40 and days.date_to = hr_general.end_of_time
41 and details.date_to = hr_general.end_of_time;
42
43 CURSOR c_tc_resource_id(
44 p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE,
45 p_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE
46 ) IS
47 SELECT tbb.resource_id
48 FROM hxc_time_building_blocks tbb
49 WHERE tbb.time_building_block_id = p_timecard_id
50 AND tbb.object_version_number = p_timecard_ovn;
51
52 /* Bug fix for 5526281 */
53 CURSOR get_timecard_start_date(p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE,
54 p_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE
55 ) IS
56 SELECT tbb.start_time,tbb.stop_time
57 FROM hxc_time_building_blocks tbb
58 WHERE tbb.time_building_block_id = p_timecard_id
59 AND tbb.object_version_number = p_timecard_ovn;
60
61 cursor emp_hire_info(p_resource_id hxc_time_building_blocks.resource_id%TYPE) IS
62 select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;
63 /* end of bug fix for 5526281 */
64
65 l_index number :=1;
66 l_precision varchar2(4);
67 l_resource_id number;
68 l_rounding_rule varchar2(80);
69 l_tc_start_date date;
70
71 /* Bug fix for 5526281 */
72 l_tc_end_date date;
73 l_pref_eval_date date;
74 l_emp_hire_date date;
75 /* end of bug fix for 5526281 */
76
77 begin
78
79 open c_tc_resource_id(p_timecard_id, p_timecard_ovn);
80 fetch c_tc_resource_id into l_resource_id;
81 close c_tc_resource_id;
82
83 /* Bug fix for 5526281 */
84 OPEN get_timecard_start_date (p_timecard_id, p_timecard_ovn);
85 FETCH get_timecard_start_date into l_tc_start_date,l_tc_end_date;
86 CLOSE get_timecard_start_date;
87
88 OPEN emp_hire_info (l_resource_id);
89 FETCH emp_hire_info into l_emp_hire_date;
90 CLOSE emp_hire_info;
91
92 if trunc(l_emp_hire_date) >= trunc(l_tc_start_date) and trunc(l_emp_hire_date) <= trunc(l_tc_end_date) then
93 l_pref_eval_date := trunc(l_emp_hire_date);
94 else
95 l_pref_eval_date := trunc(l_tc_start_date);
96 end if;
97
98 l_precision := hxc_preference_evaluation.resource_preferences
99 (l_resource_id,
100 'TC_W_TCRD_UOM',
101 3,
102 l_pref_eval_date);
103
104
105 l_rounding_rule := hxc_preference_evaluation.resource_preferences
106 (l_resource_id,
107 'TC_W_TCRD_UOM',
108 4,
109 l_pref_eval_date);
110 /* end of bug fix for 5526281 */
111 if l_precision is null
112 then
113 l_precision := '2';
114 end if;
115
116 if l_rounding_rule is null
117 then
118 l_rounding_rule := 'ROUND_TO_NEAREST';
119 end if;
120 p_hours := 0;
121
122 for det_rec in c_detail_info(p_timecard_id,p_timecard_ovn) loop
123
124 p_details(l_index).time_building_block_id := det_rec.time_building_block_id;
125 p_details(l_index).time_building_block_ovn := det_rec.object_version_number;
126 p_details(l_index).creation_date := det_rec.creation_date;
127 if(det_rec.type=hxc_timecard.c_range_type) then
128 p_hours := p_hours + hxc_find_notify_aprs_pkg.apply_round_rule(
129 l_rounding_rule,
130 l_precision,
131 nvl((det_rec.stop_time - det_rec.start_time)*24,0)
132 );
133 else
134 -- in case of null measure we need to make sure this piece of code does not fail
135 -- and do not return null
136 -- 2029550 Implementation
137 p_hours := p_hours + hxc_find_notify_aprs_pkg.apply_round_rule(
138 l_rounding_rule,
139 l_precision,
140 nvl(det_rec.measure,0)
141 );
142 end if;
143
144 l_index := l_index +1;
145 end loop;
146
147 if(p_hours is null) then
148 p_hours := 0;
149 end if;
150
151 end get_recorded_hours;
152
153 function get_has_reasons(p_details in details) return varchar2 is
154
155 cursor c_reasons
156 (p_id in hxc_time_building_blocks.time_building_block_id%type
157 ) is
158 select 'Y'
159 from hxc_time_attribute_usages tau, hxc_time_attributes ta
160 where tau.time_building_block_id = p_id
161 and tau.time_Attribute_id = ta.time_attribute_Id
162 and ta.attribute_category = hxc_timecard.c_reason_attribute;
163
164 l_index number;
165 l_found boolean := false;
166 l_result varchar2(1) := 'N';
167
168 begin
169
170 l_index := p_details.first;
171
172 loop
173 exit when ((not p_details.exists(l_index)) or (l_found));
174
175 open c_reasons(p_details(l_index).time_building_block_id);
176 fetch c_reasons into l_result;
177 if(c_reasons%found) then
178 l_found := true;
179 end if;
180 close c_reasons;
181
182 l_index := p_details.next(l_index);
183
184 end loop;
185
186 return l_result;
187
188 end get_has_reasons;
189
190 function get_submission_date(p_details in details
191 ,p_tc_date in date)
192 return date is
193 l_submission_date date := p_tc_date;
194 l_index number;
195 begin
196
197 l_index := p_details.first;
198 loop
199 exit when not p_details.exists(l_index);
200 if(l_submission_date < p_details(l_index).creation_date) then
201 l_submission_date := p_details(l_index).creation_date;
202 end if;
203 l_index := p_details.next(l_index);
204 end loop;
205
206 return l_submission_date;
207
208 end get_submission_date;
209
210 procedure insert_summary_row(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
211 ,p_mode in varchar2 default 'NORMAL'
212 ,p_attribute_category in varchar2 default null
213 ,p_attribute1 in varchar2 default null
214 ,p_attribute2 in varchar2 default null
215 ,p_attribute3 in varchar2 default null
216 ,p_attribute4 in varchar2 default null
217 ,p_attribute5 in varchar2 default null
218 ,p_attribute6 in varchar2 default null
219 ,p_attribute7 in varchar2 default null
220 ,p_attribute8 in varchar2 default null
221 ,p_attribute9 in varchar2 default null
222 ,p_attribute10 in varchar2 default null
223 ,p_attribute11 in varchar2 default null
224 ,p_attribute12 in varchar2 default null
225 ,p_attribute13 in varchar2 default null
226 ,p_attribute14 in varchar2 default null
227 ,p_attribute15 in varchar2 default null
228 ,p_attribute16 in varchar2 default null
229 ,p_attribute17 in varchar2 default null
230 ,p_attribute18 in varchar2 default null
231 ,p_attribute19 in varchar2 default null
232 ,p_attribute20 in varchar2 default null
233 ,p_attribute21 in varchar2 default null
234 ,p_attribute22 in varchar2 default null
235 ,p_attribute23 in varchar2 default null
236 ,p_attribute24 in varchar2 default null
237 ,p_attribute25 in varchar2 default null
238 ,p_attribute26 in varchar2 default null
239 ,p_attribute27 in varchar2 default null
240 ,p_attribute28 in varchar2 default null
241 ,p_attribute29 in varchar2 default null
242 ,p_attribute30 in varchar2 default null
243 ,p_approval_item_type in varchar2
244 ,p_approval_process_name in varchar2
245 ,p_approval_item_key in varchar2
246 ,p_tk_audit_item_type in varchar2
247 ,p_tk_audit_process_name in varchar2
248 ,p_tk_audit_item_key in varchar2
249 ) is
250
251 cursor c_timecard_info(p_id in hxc_time_building_blocks.time_building_block_id%type) is
252 select resource_id
253 ,start_time
254 ,stop_time
255 ,object_version_number
256 ,approval_status
257 ,creation_date
258 ,data_set_id
259 from hxc_time_building_blocks
260 where time_building_block_id = p_id
261 and date_to = hr_general.end_of_time
262 and scope = 'TIMECARD';
263
264 cursor c_check_for_reasons is
265 select 'Y'
266 from hxc_time_attributes
267 where attribute_category = hxc_timecard.c_reason_attribute;
268
269 l_approval_status hxc_time_building_blocks.approval_status%type;
270 l_resource_id hxc_time_building_blocks.resource_id%type;
271 l_start_time hxc_time_building_blocks.start_time%type;
272 l_stop_time hxc_time_building_blocks.stop_time%type;
273 l_submission_date hxc_time_building_blocks.creation_date%type;
274 l_creation_date hxc_time_building_blocks.creation_date%type;
275 l_ovn hxc_time_building_blocks.object_version_number%type;
276 l_has_reasons varchar2(1);
277 l_recorded_hours hxc_timecard_summary.recorded_hours%type :=0;
278 l_details details;
279 l_data_set_id hxc_time_building_blocks.data_set_id%type;
280
281 l_approval_item_type hxc_timecard_summary.approval_item_type%TYPE;
282 l_approval_process_name hxc_timecard_summary.approval_process_name%TYPE;
283 l_approval_item_key hxc_timecard_summary.approval_item_key%TYPE;
284
285 l_abs_days NUMBER := 0; -- Added as part of OTL ABS Integration
286 l_abs_hours NUMBER := 0; -- Added as part of OTL ABS Integration
287
288 Begin
289
290 if(g_check_for_reasons is null) then
291 open c_check_for_reasons;
292 fetch c_check_for_reasons into g_check_for_reasons;
293 if(c_check_for_reasons%notfound) then
294 g_check_for_reasons := 'N';
295 end if;
296 close c_check_for_reasons;
297 end if;
298
299 open c_timecard_info(p_timecard_id);
300 fetch c_timecard_info
301 into l_resource_id,
302 l_start_time,
303 l_stop_time,
304 l_ovn,
305 l_approval_status,
306 l_creation_date,
307 l_data_set_id;
308
309 if(c_timecard_info%found) then
310
311 --
312 -- 1. Find the approval status
313 --
314
315 if(p_mode = c_migration_mode) then
316 l_approval_status := get_migration_apr_status(p_timecard_id,l_ovn);
317 else
318 null;
319 end if;
320
321 --
322 -- 2. Recorded Hours
323 --
324 get_recorded_hours(p_timecard_id,l_ovn,l_recorded_hours,l_details);
325 --
326 -- 3. Has Reasons
327 --
328 if(g_check_for_reasons = 'Y') then
329 l_has_reasons := get_has_reasons(l_details);
330 else
331 l_has_reasons := 'N';
332 end if;
333 --
334 -- 4. Submission Date
335 --
336 l_submission_date := sysdate;
337 --
338 -- Insert Summary Row
339 --
340
341 if(l_approval_status = hxc_timecard.c_working_status OR
342 l_approval_status = hxc_timecard.c_error) then
343 l_approval_item_type :=NULL;
344 l_approval_process_name :=NULL;
345 l_approval_item_key :=NULL;
346 Else
347 l_approval_item_type := p_approval_item_type;
348 l_approval_process_name := p_approval_process_name;
349 l_approval_item_key := p_approval_item_key;
350 END IF;
351
352 -- Added for OTL ABS Integration 8888902
353 -- OTL-ABS START
354 IF (NVL(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y')
355 THEN
356
357 IF g_debug THEN
358 hr_utility.trace('ABS> In hxc_timecard_summary_pkg.insert_summary_row');
359 hr_utility.trace('ABS> initial value of recorded hours ::'||l_recorded_hours);
360 END IF;
361
362 BEGIN
363 IF g_debug THEN
364 hr_utility.trace('ABS> initial value of l_abs_days ::'||l_abs_days);
365 hr_utility.trace('ABS> initial value of l_abs_hours ::'||l_abs_hours);
366 END IF;
367
368 SELECT nvl(absence_days,0),
369 nvl(absence_hours,0)
370 INTO l_abs_days,
371 l_abs_hours
372 FROM hxc_absence_summary_temp
373 WHERE resource_id = hxc_retrieve_absences.g_person_id
374 AND start_time = hxc_retrieve_absences.g_start_time
375 AND stop_time = hxc_retrieve_absences.g_stop_time;
376
377 EXCEPTION
378 WHEN NO_DATA_FOUND THEN
379 l_abs_days := 0;
380 l_abs_hours := 0;
381 END;
382
383 IF g_debug THEN
384 hr_utility.trace('ABS> Before calculation of recorded hours');
385 hr_utility.trace('ABS> initial value of recorded hours ::'||l_recorded_hours);
386 hr_utility.trace('ABS> initial value of l_abs_days ::'||l_abs_days);
387 hr_utility.trace('ABS> initial value of l_abs_hours ::'||l_abs_hours);
388 END IF;
389
390 l_recorded_hours := l_recorded_hours - (l_abs_days+l_abs_hours);
391
392 IF g_debug THEN
393 hr_utility.trace('ABS> final values before insert into timecard summary');
394 hr_utility.trace('ABS> l_abs_days ::'||l_abs_days);
395 hr_utility.trace('ABS> l_abs_hours ::'||l_abs_hours);
396 hr_utility.trace('ABS> l_recorded_hours ::'||l_recorded_hours);
397 END IF;
398
399 -- clear absence summary rows
400 IF g_debug THEN
401 hr_utility.trace('ABS> In hxc_timecard_summary_pkg.insert_summary_row');
402 hr_utility.trace('ABS> clear absence summary rows');
403 END IF;
404
405 hxc_retrieve_absences.clear_absence_summary_rows;
406
407 END IF;
408 -- OTL-ABS END
409
410 insert into hxc_timecard_summary
411 (timecard_id
412 ,timecard_ovn
413 ,approval_status
414 ,resource_id
415 ,start_time
416 ,stop_time
417 ,recorded_hours
418 ,has_reasons
419 ,submission_date
420 ,approval_item_type
421 ,approval_process_name
422 ,approval_item_key
423 ,attribute_category
424 ,attribute1
425 ,attribute2
426 ,attribute3
427 ,attribute4
428 ,attribute5
429 ,attribute6
430 ,attribute7
431 ,attribute8
432 ,attribute9
433 ,attribute10
434 ,attribute11
435 ,attribute12
436 ,attribute13
437 ,attribute14
438 ,attribute15
439 ,attribute16
440 ,attribute17
441 ,attribute18
442 ,attribute19
443 ,attribute20
444 ,attribute21
445 ,attribute22
446 ,attribute23
447 ,attribute24
448 ,attribute25
449 ,attribute26
450 ,attribute27
451 ,attribute28
452 ,attribute29
453 ,attribute30
454 ,tk_audit_item_type
455 ,tk_audit_process_name
456 ,tk_audit_item_key
457 ,data_set_id
458 ,absence_days
459 ,absence_hours
460 )
461 values
462 (p_timecard_id
463 ,l_ovn
464 ,l_approval_status
465 ,l_resource_id
466 ,l_start_time
467 ,l_stop_time
468 ,l_recorded_hours
469 ,l_has_reasons
470 ,l_submission_date
471 ,p_approval_item_type
472 ,p_approval_process_name
473 ,p_approval_item_key
474 ,p_attribute_category
475 ,p_attribute1
476 ,p_attribute2
477 ,p_attribute3
478 ,p_attribute4
479 ,p_attribute5
480 ,p_attribute6
481 ,p_attribute7
482 ,p_attribute8
483 ,p_attribute9
484 ,p_attribute10
485 ,p_attribute11
486 ,p_attribute12
487 ,p_attribute13
488 ,p_attribute14
489 ,p_attribute15
490 ,p_attribute16
491 ,p_attribute17
492 ,p_attribute18
493 ,p_attribute19
494 ,p_attribute20
495 ,p_attribute21
496 ,p_attribute22
497 ,p_attribute23
498 ,p_attribute24
499 ,p_attribute25
500 ,p_attribute26
501 ,p_attribute27
502 ,p_attribute28
503 ,p_attribute29
504 ,p_attribute30
505 ,p_tk_audit_item_type
506 ,p_tk_audit_process_name
507 ,p_tk_audit_item_key
508 ,l_data_set_id
509 ,l_abs_days -- Added as part of OTL ABS Integration
510 ,l_abs_hours --Added as part of OTL ABS Integration
511 );
512
513 else
514
515 FND_MESSAGE.set_name('HXC','HXC_NO_TIMECARD_ID');
516 FND_MESSAGE.set_token('TIMECARD_ID',to_char(p_timecard_id));
517 FND_MESSAGE.raise_error;
518
519 end if;
520
521 End insert_summary_row;
522
523 procedure update_summary_row(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
524 ,p_approval_item_type in hxc_timecard_summary.approval_item_type%type
525 ,p_approval_process_name in hxc_timecard_summary.approval_process_name%type
526 ,p_approval_item_key in hxc_timecard_summary.approval_item_key%type
527 ) is
528
529 l_item_key hxc_timecard_summary.approval_item_key%type;
530 l_dummy varchar2(1);
531
532 cursor c_is_wf_deferred(p_item_key in hxc_timecard_summary.approval_item_key%type)
533 is
534 select 'Y'
535 from wf_item_activity_statuses wias
536 where item_type = 'HXCEMP'
537 and item_key = l_item_key
538 and activity_status = 'DEFERRED';
539
540 cursor c_get_item_key(p_timecard_id in number)
541 is
542 select approval_item_key
543 from hxc_timecard_summary
544 where timecard_id = p_timecard_id;
545
546 Begin
547
548 open c_get_item_key(p_timecard_id);
549 fetch c_get_item_key into l_item_key;
550 close c_get_item_key;
551
552
553 If l_item_key is not null then
554
555 open c_is_wf_deferred(l_item_key);
556 fetch c_is_wf_deferred into l_dummy;
557 close c_is_wf_deferred;
558
559 If l_dummy = 'Y' then
560
561 wf_engine.AbortProcess(itemkey => l_item_key,
562 itemtype => 'HXCEMP');
563 end if;
564 end if;
565
566
567 UPDATE hxc_timecard_summary
568 SET approval_item_type = p_approval_item_type,
569 approval_process_name = p_approval_process_name,
570 approval_item_key =p_approval_item_key
571 WHERE TIMECARD_ID= p_timecard_id;
572
573
574 End update_summary_row;
575
576 procedure delete_summary_row(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
577
578 Begin
579
580 delete from hxc_timecard_summary where timecard_id = p_timecard_id;
581
582 Exception
583 When others then
584 FND_MESSAGE.set_name('HXC','HXC_NO_TIMECARD_ID');
585 FND_MESSAGE.raise_error;
586
587 End delete_summary_row;
588
589 procedure reject_timecard(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
590
591 Begin
592
593 update hxc_timecard_summary
594 set approval_status = hxc_timecard.c_rejected_status
595 where timecard_id = p_timecard_id;
596
597 End reject_timecard;
598
599 Procedure approve_timecard(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
600
601 CURSOR c_timecard_details (c_timecard_id NUMBER)
602 IS
603 SELECT resource_id,
604 start_time,
605 stop_time,
606 approval_status
607 FROM hxc_timecard_summary
608 WHERE timecard_id = c_timecard_id;
609
610 l_messages hxc_message_table_type := hxc_message_table_type();
611
612 l_resource_id NUMBER;
613 l_start_time DATE;
614 l_stop_time DATE;
615 l_approval_status VARCHAR2(20);
616
617 Begin
618
619 update hxc_timecard_summary
620 set approval_status = hxc_timecard.c_approved_status
621 where timecard_id = p_timecard_id;
622
623 -- OTL-Absences Integration (Bug 8779478)
624 IF (nvl(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y') THEN
625 IF g_debug THEN
626 hr_utility.trace('Initiated Online Retrieval from HXC_TIMECARD_SUMMARY_PKG.APPROVE_TIMECARD');
627 END IF;
628
629 OPEN c_timecard_details(p_timecard_id);
630 FETCH c_timecard_details INTO l_resource_id,
631 l_start_time,
632 l_stop_time,
633 l_approval_status;
634 CLOSE c_timecard_details;
635
636 HXC_ABS_RETRIEVAL_PKG.POST_ABSENCES(l_resource_id,
637 l_start_time,
638 l_stop_time,
639 l_approval_status,
640 l_messages);
641
642 IF g_debug THEN
643 hr_utility.trace('Completed Online Retrieval from HXC_TIMECARD_SUMMARY_PKG.APPROVE_TIMECARD');
644 END IF;
645
646 IF (l_messages.COUNT > 0) THEN
647 IF g_debug THEN
648 hr_utility.trace('ABS:EXCEPTION - retrieval_error during approval');
649 END IF;
650 hr_utility.set_message(809, l_messages(l_messages.FIRST).message_name);
651 hr_utility.raise_error;
652 END IF;
653
654 END IF;
655
656
657 End approve_timecard;
658
659 Procedure submit_timecard(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
660
661 Begin
662
663 update hxc_timecard_summary
664 set approval_status = hxc_timecard.c_submitted_status
665 where timecard_id = p_timecard_id;
666
667 End submit_timecard;
668
669
670 -- Bug 9747820
671 -- Transferred to column is populated using the below procedure
672 -- for a given retrieval process.
673
674 PROCEDURE update_transferred_to( p_timecard_id IN NUMBER,
675 p_timecard_ovn IN NUMBER,
676 p_process_id IN NUMBER )
677 IS
678
679 CURSOR get_details( p_timecard_id IN NUMBER,
680 p_timecard_ovn IN NUMBER,
681 p_process_id IN NUMBER )
682 IS SELECT 1
683 FROM hxc_timecard_summary sum,
684 hxc_time_building_blocks day,
685 hxc_time_building_blocks det,
686 hxc_latest_details hld
687 WHERE sum.timecard_id = p_timecard_id
688 AND sum.timecard_ovn = p_timecard_ovn
689 AND day.parent_building_block_id = sum.timecard_id
690 AND day.parent_building_block_ovn = sum.timecard_ovn
691 AND det.parent_building_block_id = day.time_building_block_id
692 AND det.parent_building_block_ovn = day.object_version_number
693 AND hld.time_building_block_id = det.time_building_block_id
694 AND hld.object_version_number = det.object_version_number
695 AND NOT EXISTS ( SELECT 1
696 FROM hxc_transactions ht,
697 hxc_transaction_details htd
698 WHERE htd.time_building_block_id = det.time_building_block_id
699 AND det.object_version_number = htd.time_building_block_ovn
700 AND htd.status = 'SUCCESS'
701 AND ht.transaction_id = htd.transaction_id
702 AND ht.type = 'RETRIEVAL'
703 AND ht.status = 'SUCCESS'
704 AND ht.transaction_process_id = p_process_id )
705 AND ( det.date_to = hr_general.end_of_time
706 OR ( det.date_to <> hr_general.end_of_time
707 AND EXISTS ( SELECT 1
708 FROM hxc_transactions ht,
709 hxc_transaction_details htd
710 WHERE htd.time_building_block_id = det.time_building_block_id
711 AND det.object_version_number > htd.time_building_block_ovn
712 AND htd.status = 'SUCCESS'
713 AND ht.transaction_id = htd.transaction_id
714 AND ht.type = 'RETRIEVAL'
715 AND ht.status = 'SUCCESS'
716 AND ht.transaction_process_id = p_process_id )
717 )
718 );
719
720 CURSOR get_recipient_name(p_process_id IN NUMBER)
721 IS SELECT htr.name
722 FROM hxc_retrieval_processes hrp,
723 hxc_time_recipients htr
724 WHERE hrp.retrieval_process_id = p_process_id
725 AND hrp.time_recipient_id = htr.time_recipient_id;
726
727 l_recipient_name VARCHAR2(100);
728 l_exists NUMBER := 0;
729
730 BEGIN
731
732 IF g_debug
733 THEN
734 hr_utility.trace('p_timecard_id = '||p_timecard_id);
735 hr_utility.trace('p_timecard_ovn = '||p_timecard_ovn);
736 hr_utility.trace('p_process_id = '||p_process_id);
737 END IF;
738
739 -- Pick up the recipient name.
740 -- If exists in cache, use it, else select with a cursor.
741 IF NOT g_recipient_name.EXISTS(p_process_id)
742 THEN
743 IF p_process_id <> -1
744 THEN
745 OPEN get_recipient_name(p_process_id);
746 FETCH get_recipient_name INTO l_recipient_name;
747 CLOSE get_recipient_name;
748 ELSE
749 l_recipient_name := 'Payroll';
750 END IF;
751 g_recipient_name(p_process_id) := l_recipient_name;
752 ELSE
753 l_recipient_name := g_recipient_name(p_process_id);
754 END IF;
755
756
757 -- Get the details which are unretrieved and eligible to be retrieved.
758 OPEN get_details(p_timecard_id,
759 p_timecard_ovn,
760 p_process_id);
761
762 FETCH get_details INTO l_exists;
763
764 CLOSE get_details;
765
766 IF g_debug
767 THEN
768 hr_utility.trace('l_exists = '||l_exists);
769 END IF;
770
771 -- If any such detail exists.
772 IF l_exists = 0
773 THEN
774
775 IF g_debug
776 THEN
777 hr_utility.trace('Updating Transferred To ');
778 END IF;
779
780 -- Update the transferred to column.
781 -- Do an LTRIM and RTRIM to avoid leading and trailing zeros.
782 -- Bug 16294903
783 -- Added a construct inside the NVL to remove showing up the same application twice.
784 UPDATE hxc_timecard_summary
785 SET transferred_to = RTRIM(LTRIM((NVL(REPLACE(transferred_to,l_recipient_name),',')||','||l_recipient_name),','),',')
786 WHERE timecard_id = p_timecard_id
787 AND timecard_ovn = p_timecard_ovn;
788
789
790 IF g_debug
791 THEN
792 SELECT transferred_to
793 INTO l_recipient_name
794 FROM hxc_timecard_summary
795 WHERE timecard_id = p_timecard_id
796 AND timecard_ovn = p_timecard_ovn;
797
798 hr_utility.trace('New value is '||l_recipient_name);
799 END IF;
800
801 END IF;
802
803 EXCEPTION
804 WHEN NO_DATA_FOUND
805 THEN
806 hr_utility.trace('There is a no data found ');
807 hr_utility.trace(dbms_utility.format_error_backtrace);
808
809 END update_transferred_to;
810
811 end hxc_timecard_summary_pkg;
812