[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMECARD_SUMMARY_PKG
Source
1 package body hxc_timecard_summary_pkg as
2 /* $Header: hxctcsum.pkb 120.4.12010000.3 2008/08/05 12:06:11 ubhat ship $ */
3
4 g_check_for_reasons varchar2(1) := null;
5
6 function get_migration_apr_status
7 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
8 ,p_timecard_ovn in hxc_time_building_blocks.object_version_number%type
9 ) return varchar2 is
10
11 begin
12
13 return hxc_timecard_search_pkg.get_timecard_status_code(p_timecard_id,p_timecard_ovn,c_migration_mode);
14
15 end get_migration_apr_status;
16
17 procedure get_recorded_hours
18 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
19 ,p_timecard_ovn in hxc_time_building_blocks.object_version_number%type
20 ,p_hours out nocopy number
21 ,p_details out nocopy details
22 ) is
23
24 cursor c_detail_info(p_id in hxc_time_building_blocks.time_building_block_id%type
25 ,p_ovn in hxc_time_building_blocks.object_version_number%type
26 ) is
27 select details.time_building_block_id
28 ,details.object_version_number
29 ,details.start_time
30 ,details.stop_time
31 ,details.measure
32 ,details.type
33 ,details.creation_date
34 from hxc_time_building_blocks days, hxc_time_building_blocks details
35 where days.parent_building_block_id = p_id
36 and days.parent_building_block_ovn = p_ovn
37 and details.parent_building_block_id = days.time_building_block_id
38 and details.parent_building_block_ovn = days.object_version_number
39 and days.date_to = hr_general.end_of_time
40 and details.date_to = hr_general.end_of_time;
41
42 CURSOR c_tc_resource_id(
43 p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE,
44 p_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE
45 ) IS
46 SELECT tbb.resource_id
47 FROM hxc_time_building_blocks tbb
48 WHERE tbb.time_building_block_id = p_timecard_id
49 AND tbb.object_version_number = p_timecard_ovn;
50
51 /* Bug fix for 5526281 */
52 CURSOR get_timecard_start_date(p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE,
53 p_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE
54 ) IS
55 SELECT tbb.start_time,tbb.stop_time
56 FROM hxc_time_building_blocks tbb
57 WHERE tbb.time_building_block_id = p_timecard_id
58 AND tbb.object_version_number = p_timecard_ovn;
59
60 cursor emp_hire_info(p_resource_id hxc_time_building_blocks.resource_id%TYPE) IS
61 select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;
62 /* end of bug fix for 5526281 */
63
64 l_index number :=1;
65 l_precision varchar2(4);
66 l_resource_id number;
67 l_rounding_rule varchar2(80);
68 l_tc_start_date date;
69
70 /* Bug fix for 5526281 */
71 l_tc_end_date date;
72 l_pref_eval_date date;
73 l_emp_hire_date date;
74 /* end of bug fix for 5526281 */
75
76 begin
77
78 open c_tc_resource_id(p_timecard_id, p_timecard_ovn);
79 fetch c_tc_resource_id into l_resource_id;
80 close c_tc_resource_id;
81
82 /* Bug fix for 5526281 */
83 OPEN get_timecard_start_date (p_timecard_id, p_timecard_ovn);
84 FETCH get_timecard_start_date into l_tc_start_date,l_tc_end_date;
85 CLOSE get_timecard_start_date;
86
87 OPEN emp_hire_info (l_resource_id);
88 FETCH emp_hire_info into l_emp_hire_date;
89 CLOSE emp_hire_info;
90
91 if trunc(l_emp_hire_date) >= trunc(l_tc_start_date) and trunc(l_emp_hire_date) <= trunc(l_tc_end_date) then
92 l_pref_eval_date := trunc(l_emp_hire_date);
93 else
94 l_pref_eval_date := trunc(l_tc_start_date);
95 end if;
96
97 l_precision := hxc_preference_evaluation.resource_preferences
98 (l_resource_id,
99 'TC_W_TCRD_UOM',
100 3,
101 l_pref_eval_date);
102
103
104 l_rounding_rule := hxc_preference_evaluation.resource_preferences
105 (l_resource_id,
106 'TC_W_TCRD_UOM',
107 4,
108 l_pref_eval_date);
109 /* end of bug fix for 5526281 */
110 if l_precision is null
111 then
112 l_precision := '2';
113 end if;
114
115 if l_rounding_rule is null
116 then
117 l_rounding_rule := 'ROUND_TO_NEAREST';
118 end if;
119 p_hours := 0;
120
121 for det_rec in c_detail_info(p_timecard_id,p_timecard_ovn) loop
122
123 p_details(l_index).time_building_block_id := det_rec.time_building_block_id;
124 p_details(l_index).time_building_block_ovn := det_rec.object_version_number;
125 p_details(l_index).creation_date := det_rec.creation_date;
126 if(det_rec.type=hxc_timecard.c_range_type) then
127 p_hours := p_hours + hxc_find_notify_aprs_pkg.apply_round_rule(
128 l_rounding_rule,
129 l_precision,
130 nvl((det_rec.stop_time - det_rec.start_time)*24,0)
131 );
132 else
133 -- in case of null measure we need to make sure this piece of code does not fail
134 -- and do not return null
135 -- 2029550 Implementation
136 p_hours := p_hours + hxc_find_notify_aprs_pkg.apply_round_rule(
137 l_rounding_rule,
138 l_precision,
139 nvl(det_rec.measure,0)
140 );
141 end if;
142
143 l_index := l_index +1;
144 end loop;
145
146 if(p_hours is null) then
147 p_hours := 0;
148 end if;
149
150 end get_recorded_hours;
151
152 function get_has_reasons(p_details in details) return varchar2 is
153
154 cursor c_reasons
155 (p_id in hxc_time_building_blocks.time_building_block_id%type
156 ) is
157 select 'Y'
158 from hxc_time_attribute_usages tau, hxc_time_attributes ta
159 where tau.time_building_block_id = p_id
160 and tau.time_Attribute_id = ta.time_attribute_Id
161 and ta.attribute_category = hxc_timecard.c_reason_attribute;
162
163 l_index number;
164 l_found boolean := false;
165 l_result varchar2(1) := 'N';
166
167 begin
168
169 l_index := p_details.first;
170
171 loop
172 exit when ((not p_details.exists(l_index)) or (l_found));
173
174 open c_reasons(p_details(l_index).time_building_block_id);
175 fetch c_reasons into l_result;
176 if(c_reasons%found) then
177 l_found := true;
178 end if;
179 close c_reasons;
180
181 l_index := p_details.next(l_index);
182
183 end loop;
184
185 return l_result;
186
187 end get_has_reasons;
188
189 function get_submission_date(p_details in details
190 ,p_tc_date in date)
191 return date is
192 l_submission_date date := p_tc_date;
193 l_index number;
194 begin
195
196 l_index := p_details.first;
197 loop
198 exit when not p_details.exists(l_index);
199 if(l_submission_date < p_details(l_index).creation_date) then
200 l_submission_date := p_details(l_index).creation_date;
201 end if;
202 l_index := p_details.next(l_index);
203 end loop;
204
205 return l_submission_date;
206
207 end get_submission_date;
208
209 procedure insert_summary_row(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
210 ,p_mode in varchar2 default 'NORMAL'
211 ,p_attribute_category in varchar2 default null
212 ,p_attribute1 in varchar2 default null
213 ,p_attribute2 in varchar2 default null
214 ,p_attribute3 in varchar2 default null
215 ,p_attribute4 in varchar2 default null
216 ,p_attribute5 in varchar2 default null
217 ,p_attribute6 in varchar2 default null
218 ,p_attribute7 in varchar2 default null
219 ,p_attribute8 in varchar2 default null
220 ,p_attribute9 in varchar2 default null
221 ,p_attribute10 in varchar2 default null
222 ,p_attribute11 in varchar2 default null
223 ,p_attribute12 in varchar2 default null
224 ,p_attribute13 in varchar2 default null
225 ,p_attribute14 in varchar2 default null
226 ,p_attribute15 in varchar2 default null
227 ,p_attribute16 in varchar2 default null
228 ,p_attribute17 in varchar2 default null
229 ,p_attribute18 in varchar2 default null
230 ,p_attribute19 in varchar2 default null
231 ,p_attribute20 in varchar2 default null
232 ,p_attribute21 in varchar2 default null
233 ,p_attribute22 in varchar2 default null
234 ,p_attribute23 in varchar2 default null
235 ,p_attribute24 in varchar2 default null
236 ,p_attribute25 in varchar2 default null
237 ,p_attribute26 in varchar2 default null
238 ,p_attribute27 in varchar2 default null
239 ,p_attribute28 in varchar2 default null
240 ,p_attribute29 in varchar2 default null
241 ,p_attribute30 in varchar2 default null
242 ,p_approval_item_type in varchar2
243 ,p_approval_process_name in varchar2
244 ,p_approval_item_key in varchar2
245 ,p_tk_audit_item_type in varchar2
246 ,p_tk_audit_process_name in varchar2
247 ,p_tk_audit_item_key in varchar2
248 ) is
249
250 cursor c_timecard_info(p_id in hxc_time_building_blocks.time_building_block_id%type) is
251 select resource_id
252 ,start_time
253 ,stop_time
254 ,object_version_number
255 ,approval_status
256 ,creation_date
257 ,data_set_id
258 from hxc_time_building_blocks
259 where time_building_block_id = p_id
260 and date_to = hr_general.end_of_time
261 and scope = 'TIMECARD';
262
263 cursor c_check_for_reasons is
264 select 'Y'
265 from hxc_time_attributes
266 where attribute_category = hxc_timecard.c_reason_attribute;
267
268 l_approval_status hxc_time_building_blocks.approval_status%type;
269 l_resource_id hxc_time_building_blocks.resource_id%type;
270 l_start_time hxc_time_building_blocks.start_time%type;
271 l_stop_time hxc_time_building_blocks.stop_time%type;
272 l_submission_date hxc_time_building_blocks.creation_date%type;
273 l_creation_date hxc_time_building_blocks.creation_date%type;
274 l_ovn hxc_time_building_blocks.object_version_number%type;
275 l_has_reasons varchar2(1);
276 l_recorded_hours hxc_timecard_summary.recorded_hours%type :=0;
277 l_details details;
278 l_data_set_id hxc_time_building_blocks.data_set_id%type;
279
280 l_approval_item_type hxc_timecard_summary.approval_item_type%TYPE;
281 l_approval_process_name hxc_timecard_summary.approval_process_name%TYPE;
282 l_approval_item_key hxc_timecard_summary.approval_item_key%TYPE;
283
284 Begin
285
286 if(g_check_for_reasons is null) then
287 open c_check_for_reasons;
288 fetch c_check_for_reasons into g_check_for_reasons;
289 if(c_check_for_reasons%notfound) then
290 g_check_for_reasons := 'N';
291 end if;
292 close c_check_for_reasons;
293 end if;
294
295 open c_timecard_info(p_timecard_id);
296 fetch c_timecard_info
297 into l_resource_id,
298 l_start_time,
299 l_stop_time,
300 l_ovn,
301 l_approval_status,
302 l_creation_date,
303 l_data_set_id;
304
305 if(c_timecard_info%found) then
306
307 --
308 -- 1. Find the approval status
309 --
310
311 if(p_mode = c_migration_mode) then
312 l_approval_status := get_migration_apr_status(p_timecard_id,l_ovn);
313 else
314 null;
315 end if;
316
317 --
318 -- 2. Recorded Hours
319 --
320 get_recorded_hours(p_timecard_id,l_ovn,l_recorded_hours,l_details);
321 --
322 -- 3. Has Reasons
323 --
324 if(g_check_for_reasons = 'Y') then
325 l_has_reasons := get_has_reasons(l_details);
326 else
327 l_has_reasons := 'N';
328 end if;
329 --
330 -- 4. Submission Date
331 --
332 if(l_approval_status = hxc_timecard.c_working_status) then
333 l_submission_date := null;
334 else
335 --
336 -- 115.5 Change. Submission date for normal process
337 -- is always sysdate.
338 -- See:
339 -- http://www-apps.us.oracle.com:1100/~arundell/tasks/analysis/bug3531289.html
340 --
341 if(p_mode = c_migration_mode) then
342 l_submission_date := get_submission_date(l_details,l_creation_date);
343 else
344 l_submission_date := sysdate;
345 end if;
346 end if;
347 --
348 -- Insert Summary Row
349 --
350
351 if(l_approval_status = hxc_timecard.c_working_status OR
352 l_approval_status = hxc_timecard.c_error) then
353 l_approval_item_type :=NULL;
354 l_approval_process_name :=NULL;
355 l_approval_item_key :=NULL;
356 Else
357 l_approval_item_type := p_approval_item_type;
358 l_approval_process_name := p_approval_process_name;
359 l_approval_item_key := p_approval_item_key;
360 END IF;
361
362 insert into hxc_timecard_summary
363 (timecard_id
364 ,timecard_ovn
365 ,approval_status
366 ,resource_id
367 ,start_time
368 ,stop_time
369 ,recorded_hours
370 ,has_reasons
371 ,submission_date
372 ,approval_item_type
373 ,approval_process_name
374 ,approval_item_key
375 ,attribute_category
376 ,attribute1
377 ,attribute2
378 ,attribute3
379 ,attribute4
380 ,attribute5
381 ,attribute6
382 ,attribute7
383 ,attribute8
384 ,attribute9
385 ,attribute10
386 ,attribute11
387 ,attribute12
388 ,attribute13
389 ,attribute14
390 ,attribute15
391 ,attribute16
392 ,attribute17
393 ,attribute18
394 ,attribute19
395 ,attribute20
396 ,attribute21
397 ,attribute22
398 ,attribute23
399 ,attribute24
400 ,attribute25
401 ,attribute26
402 ,attribute27
403 ,attribute28
404 ,attribute29
405 ,attribute30
406 ,tk_audit_item_type
407 ,tk_audit_process_name
408 ,tk_audit_item_key
409 ,data_set_id
410 )
411 values
412 (p_timecard_id
413 ,l_ovn
414 ,l_approval_status
415 ,l_resource_id
416 ,l_start_time
417 ,l_stop_time
418 ,l_recorded_hours
419 ,l_has_reasons
420 ,l_submission_date
421 ,p_approval_item_type
422 ,p_approval_process_name
423 ,p_approval_item_key
424 ,p_attribute_category
425 ,p_attribute1
426 ,p_attribute2
427 ,p_attribute3
428 ,p_attribute4
429 ,p_attribute5
430 ,p_attribute6
431 ,p_attribute7
432 ,p_attribute8
433 ,p_attribute9
434 ,p_attribute10
435 ,p_attribute11
436 ,p_attribute12
437 ,p_attribute13
438 ,p_attribute14
439 ,p_attribute15
440 ,p_attribute16
441 ,p_attribute17
442 ,p_attribute18
443 ,p_attribute19
444 ,p_attribute20
445 ,p_attribute21
446 ,p_attribute22
447 ,p_attribute23
448 ,p_attribute24
449 ,p_attribute25
450 ,p_attribute26
451 ,p_attribute27
452 ,p_attribute28
453 ,p_attribute29
454 ,p_attribute30
455 ,p_tk_audit_item_type
456 ,p_tk_audit_process_name
457 ,p_tk_audit_item_key
458 ,l_data_set_id
459 );
460
461 else
462
463 FND_MESSAGE.set_name('HXC','HXC_NO_TIMECARD_ID');
464 FND_MESSAGE.set_token('TIMECARD_ID',to_char(p_timecard_id));
465 FND_MESSAGE.raise_error;
466
467 end if;
468
469 End insert_summary_row;
470
471 procedure update_summary_row(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
472 ,p_approval_item_type in hxc_timecard_summary.approval_item_type%type
473 ,p_approval_process_name in hxc_timecard_summary.approval_process_name%type
474 ,p_approval_item_key in hxc_timecard_summary.approval_item_key%type
475 ) is
476
477 l_item_key hxc_timecard_summary.approval_item_key%type;
478 l_dummy varchar2(1);
479
480 cursor c_is_wf_deferred(p_item_key in hxc_timecard_summary.approval_item_key%type)
481 is
482 select 'Y'
483 from wf_item_activity_statuses wias
484 where item_type = 'HXCEMP'
485 and item_key = l_item_key
486 and activity_status = 'DEFERRED';
487
488 cursor c_get_item_key(p_timecard_id in number)
489 is
490 select approval_item_key
491 from hxc_timecard_summary
492 where timecard_id = p_timecard_id;
493
494 Begin
495
496 open c_get_item_key(p_timecard_id);
497 fetch c_get_item_key into l_item_key;
498 close c_get_item_key;
499
500
501 If l_item_key is not null then
502
503 open c_is_wf_deferred(l_item_key);
504 fetch c_is_wf_deferred into l_dummy;
505 close c_is_wf_deferred;
506
507 If l_dummy = 'Y' then
508
509 wf_engine.AbortProcess(itemkey => l_item_key,
510 itemtype => 'HXCEMP');
511 end if;
512 end if;
513
514
515 UPDATE hxc_timecard_summary
516 SET approval_item_type = p_approval_item_type,
517 approval_process_name = p_approval_process_name,
518 approval_item_key =p_approval_item_key
519 WHERE TIMECARD_ID= p_timecard_id;
520
521
522 End update_summary_row;
523
524 procedure delete_summary_row(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
525
526 Begin
527
528 delete from hxc_timecard_summary where timecard_id = p_timecard_id;
529
530 Exception
531 When others then
532 FND_MESSAGE.set_name('HXC','HXC_NO_TIMECARD_ID');
533 FND_MESSAGE.raise_error;
534
535 End delete_summary_row;
536
537 procedure reject_timecard(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
538
539 Begin
540
541 update hxc_timecard_summary
542 set approval_status = hxc_timecard.c_rejected_status
543 where timecard_id = p_timecard_id;
544
545 End reject_timecard;
546
547 Procedure approve_timecard(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
548
549 Begin
550
551 update hxc_timecard_summary
552 set approval_status = hxc_timecard.c_approved_status
553 where timecard_id = p_timecard_id;
554
555 End approve_timecard;
556
557 Procedure submit_timecard(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
558
559 Begin
560
561 update hxc_timecard_summary
562 set approval_status = hxc_timecard.c_submitted_status
563 where timecard_id = p_timecard_id;
564
565 End submit_timecard;
566
567 end hxc_timecard_summary_pkg;