[Home] [Help]
PACKAGE BODY: APPS.HXT_TIMECARD_INFO
Source
1 PACKAGE BODY hxt_timecard_info AS
2 /* $Header: hxctimotm.pkb 120.2 2005/09/23 09:37:04 nissharm noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 PROCEDURE get_holiday_info (
7 p_date IN DATE,
8 p_hcl_id IN NUMBER,
9 p_elt_id OUT NOCOPY NUMBER,
10 p_hours OUT NOCOPY NUMBER
11 );
12
13 FUNCTION convert_time (
14 p_date DATE,
15 p_time_in NUMBER,
16 p_time_out NUMBER DEFAULT NULL
17 )
18 RETURN DATE;
19
20 FUNCTION convert_timecard_time (
21 p_date DATE,
22 p_time_in NUMBER,
23 p_time_out NUMBER DEFAULT NULL
24 )
25 RETURN DATE;
26
27
28 -- Get all assignment information for the resource_id
29 CURSOR g_asg_cur (p_resource_id NUMBER, p_start_time DATE, p_stop_time DATE)
30 IS
31 SELECT paf.person_id resource_id, paf.assignment_id,
32 paf.business_group_id, paf.assignment_number,
33 aeiv.hxt_rotation_plan rtp_id, aeiv.hxt_earning_policy egp_id,
34 egp.hcl_id, aeiv.hxt_rotation_plan
35 FROM per_assignments_f paf,
36 hxt_per_aei_ddf_v aeiv,
37 hxt_earning_policies egp,
38 per_assignment_status_types typ
39 WHERE aeiv.assignment_id = paf.assignment_id
40 AND egp.id = aeiv.hxt_earning_policy
41 AND paf.effective_start_date <= p_stop_time
42 AND paf.effective_end_date >= p_start_time
43 AND aeiv.effective_start_date <= p_stop_time
44 AND aeiv.effective_end_date >= p_start_time
45 AND paf.person_id = p_resource_id
46 AND paf.primary_flag = 'Y'
47 AND paf.assignment_status_type_id = typ.assignment_status_type_id
48 AND typ.per_system_status = 'ACTIVE_ASSIGN';
49
50
51 --smummini : Added above three checks for getting active primary assignment
52 -- Bug num : 2397763
53
54 g_asg_rec g_asg_cur%ROWTYPE;
55
56
57 --------------------------------------------------------------------------------
58 -- PUBLIC --
59 --------------------------------------------------------------------------------
60 PROCEDURE generate_time (
61 p_resource_id IN NUMBER,
62 p_start_time IN DATE,
63 p_stop_time IN DATE,
64 p_app_attributes OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
65 p_timecard OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
66 p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
67 )
68 IS
69 -- Declare local variables
70 l_person_id per_people_f.person_id%TYPE := p_resource_id;
71 l_start_time DATE := p_start_time;
72 l_stop_time DATE := p_stop_time;
73 l_row_num NUMBER := 0;
74 l_error EXCEPTION;
75 l_rtp_err EXCEPTION;
76 l_egp_err EXCEPTION;
77
78 BEGIN
79 g_debug := hr_utility.debug_enabled;
80
81 if g_debug then
82 hr_utility.set_location ('Generate_Time', 10);
83 end if;
84 IF p_app_attributes.COUNT > 0
85 THEN
86 if g_debug then
87 hr_utility.set_location ('Generate_Time', 20);
88 end if;
89 FOR l IN p_app_attributes.FIRST .. p_app_attributes.LAST
90 LOOP
91 p_app_attributes (l).time_attribute_id := NULL;
92 p_app_attributes (l).building_block_id := NULL;
93 p_app_attributes (l).attribute_name := NULL;
94 p_app_attributes (l).attribute_value := NULL;
95 p_app_attributes (l).bld_blk_info_type := NULL;
96 p_app_attributes (l).CATEGORY := NULL;
97 p_app_attributes (l).updated := NULL;
98 p_app_attributes (l).changed := NULL;
99 END LOOP;
100 p_app_attributes.DELETE;
101 END IF;
102 IF p_timecard.COUNT > 0
103 THEN
104 if g_debug then
105 hr_utility.set_location ('Generate_Time', 30);
106 end if;
107 FOR l IN p_timecard.FIRST .. p_timecard.LAST
108 LOOP
109 p_timecard (l).time_building_block_id := NULL;
110 p_timecard (l).TYPE := NULL;
111 p_timecard (l).measure := NULL;
112 p_timecard (l).unit_of_measure := NULL;
113 p_timecard (l).start_time := NULL;
114 p_timecard (l).stop_time := NULL;
115 p_timecard (l).parent_building_block_id := NULL;
116 p_timecard (l).parent_is_new := NULL;
117 p_timecard (l).SCOPE := NULL;
118 p_timecard (l).object_version_number := NULL;
119 p_timecard (l).approval_status := NULL;
120 p_timecard (l).resource_id := NULL;
121 p_timecard (l).resource_type := NULL;
122 p_timecard (l).approval_style_id := NULL;
123 p_timecard (l).date_from := NULL;
124 p_timecard (l).date_to := NULL;
125 p_timecard (l).comment_text := NULL;
126 p_timecard (l).parent_building_block_ovn := NULL;
127 p_timecard (l).NEW := NULL;
128 p_timecard (l).changed := NULL;
129 END LOOP;
130 p_timecard.DELETE;
131 END IF;
132 /* Start of Main Cursor */
133
134 if g_debug then
135 hr_utility.trace('l_person_id :'||l_person_id);
136 hr_utility.trace('l_start_time:'||l_start_time);
137 hr_utility.trace('l_stop_time :'||l_stop_time);
138 end if;
139
140 FOR asg_rec IN g_asg_cur (l_person_id, l_start_time, l_stop_time)
141 LOOP
142 if g_debug then
143 hr_utility.set_location ('Generate_Time', 40);
144 end if;
145 g_asg_rec := asg_rec;
146 l_row_num := l_row_num
147 + 1;
148 if g_debug then
149 hr_utility.TRACE ( 'l_row_num :'
150 || l_row_num);
151 end if;
152 IF g_asg_rec.egp_id IS NULL
153 THEN
154 if g_debug then
155 hr_utility.set_location ('Generate_Time', 50);
156 end if;
157 RAISE l_egp_err;
158 END IF;
159 IF g_asg_rec.rtp_id IS NULL
160 THEN
161 if g_debug then
162 hr_utility.set_location ('Generate_Time', 60);
163 end if;
164 RAISE l_rtp_err;
165 END IF;
166 if g_debug then
167 hr_utility.set_location ('Generate_Time', 70);
168
169 hr_utility.trace('l_start_time :'||l_start_time);
170 hr_utility.trace('l_stop_time :'||l_stop_time);
171 hr_utility.trace('g_asg_rec.rtp_id :'||g_asg_rec.rtp_id);
172 end if;
173
174 gen_rot_plan (
175 l_start_time,
176 l_stop_time,
177 g_asg_rec.rtp_id,
178 p_app_attributes,
179 p_timecard
180 );
181
182 if g_debug then
183 hr_utility.set_location ('Generate_Time', 80);
184 end if;
185 FOR l_cnt IN
186 p_timecard.FIRST .. p_timecard.LAST
187 LOOP
188 if g_debug then
189 hr_utility.TRACE (
190 'p_timecard BB ID is : '
191 || TO_CHAR (
192 p_timecard (l_cnt).time_building_block_id
193 )
194 );
195 hr_utility.TRACE (
196 'p_timecard Scope is : '
197 || (
198 p_timecard (l_cnt).scope
199 )
200 );
201 hr_utility.TRACE (
202 'p_timecard start_time is : '
203 || TO_CHAR (p_timecard (l_cnt).start_time,
204 'dd-mon-yyyy hh24:mi:ss')
205 );
206 hr_utility.TRACE (
207 'p_timecard stop_time is : '
208 || TO_CHAR (p_timecard (l_cnt).stop_time,
209 'dd-mon-yyyy hh24:mi:ss')
210 );
211 end if;
212 END LOOP;
213
214 if g_debug then
215 hr_utility.set_location ('Generate_Time', 90);
216 end if;
217
218 END LOOP;
219
220 if g_debug then
221 hr_utility.set_location ('Generate_Time', 100);
222 end if;
223
224 IF l_row_num = 0
225 THEN
226 if g_debug then
227 hr_utility.set_location ('Generate_Time', 110);
228 end if;
229 RAISE l_error;
230 END IF;
231
232
233 EXCEPTION
234 WHEN l_error
235 THEN
236 if g_debug then
237 hr_utility.set_location ('Generate_Time', 100);
238 end if;
239 --if g_debug then
240 --hr_utility.TRACE ( 'l_row_num:'
241 -- || l_row_num);
242 --end if;
243 --fnd_message.set_name ('HXC', 'HXC_366279_NO_WRKPLAN_ERR');
244 --fnd_message.raise_error;
245 if g_debug then
246 hr_utility.trace('Adding Up message NoWorkplan');
247 end if;
248 hxc_timecard_message_helper.adderrortocollection (
249 p_messages => p_messages
250 , p_message_name => 'HXC_366279_NO_WRKPLAN_ERR'
251 , p_message_level => 'ERROR'
252 , p_message_field => NULL
253 , p_message_tokens => NULL
254 , p_application_short_name => 'HXC'
255 , p_time_building_block_id => NULL
256 , p_time_building_block_ovn => NULL
257 , p_time_attribute_id => NULL
258 , p_time_attribute_ovn => NULL );
259 if g_debug then
260 hr_utility.trace('The count of p_messages is: ' || p_messages.count);
261 end if;
262 WHEN l_egp_err
263 THEN
264 if g_debug then
265 hr_utility.set_location ('Generate_Time', 110);
266 end if;
267 --fnd_message.set_name ('HXC', 'HXC_366280_ERR_NO_ERN_POL');
268 --fnd_message.raise_error;
269 hxc_timecard_message_helper.adderrortocollection (
270 p_messages => p_messages
271 , p_message_name => 'HXC_366280_ERR_NO_ERN_POL'
272 , p_message_level => 'ERROR'
273 , p_message_field => NULL
274 , p_message_tokens => NULL
275 , p_application_short_name => 'HXC'
276 , p_time_building_block_id => NULL
277 , p_time_building_block_ovn => NULL
278 , p_time_attribute_id => NULL
279 , p_time_attribute_ovn => NULL );
280 if g_debug then
281 hr_utility.trace('The count of p_messages is: ' || p_messages.count);
282 end if;
283 WHEN l_rtp_err
284 THEN
285 if g_debug then
286 hr_utility.set_location ('Generate_Time', 120);
287 end if;
288 --if g_debug then
289 --hr_utility.TRACE ( 'ROT_PLAN_ERR:'
290 --|| g_asg_rec.rtp_id);
291 --end if;
292 --fnd_message.set_name ('HXC', 'HXC_366281_ERR_NO_ROT_PLAN');
293 --fnd_message.raise_error;
294 hxc_timecard_message_helper.adderrortocollection (
295 p_messages => p_messages
296 , p_message_name => 'HXC_366281_ERR_NO_ROT_PLAN'
297 , p_message_level => 'ERROR'
298 , p_message_field => NULL
299 , p_message_tokens => NULL
300 , p_application_short_name => 'HXC'
301 , p_time_building_block_id => NULL
302 , p_time_building_block_ovn => NULL
303 , p_time_attribute_id => NULL
304 , p_time_attribute_ovn => NULL );
305 if g_debug then
306 hr_utility.trace('The count of p_messages is: ' || p_messages.count);
307 end if;
308
309 WHEN OTHERS
310 THEN
311 if g_debug then
312 hr_utility.set_location ('Generate_Time', 130);
313 end if;
314 --fnd_message.set_name ('HXC', 'HXC_366282_AUTOGEN_ERR');
315 --fnd_message.raise_error;
316 hxc_timecard_message_helper.adderrortocollection (
317 p_messages => p_messages
318 , p_message_name => 'HXC_366282_AUTOGEN_ERR'
319 , p_message_level => 'ERROR'
320 , p_message_field => NULL
321 , p_message_tokens => NULL
322 , p_application_short_name => 'HXC'
323 , p_time_building_block_id => NULL
324 , p_time_building_block_ovn => NULL
325 , p_time_attribute_id => NULL
326 , p_time_attribute_ovn => NULL );
327 if g_debug then
328 hr_utility.trace('The count of p_messages is: ' || p_messages.count);
329 end if;
330 END;
331 --------------------------------------------------------------------------------
332 PROCEDURE get_work_day (
333 p_date IN DATE,
334 p_work_id IN NUMBER,
335 p_standard_start OUT NOCOPY NUMBER,
336 p_standard_stop OUT NOCOPY NUMBER,
337 p_hours OUT NOCOPY NUMBER
338 )
339 IS
340 --
341 -- Procedure GET_WORK_DAY
342 -- Purpose: Gets shift start and stop time,and shift hours for the person's
343 -- assigned shift on an input date
344
345 CURSOR work_day (p_wp_id NUMBER, p_date DATE) --, p_weekday VARCHAR2)
346 IS
347 SELECT sht.standard_start, sht.standard_stop, sht.hours
348 FROM hxt_shifts sht,
349 hxt_weekly_work_schedules wws,
350 hxt_work_shifts wsh
351 WHERE wsh.week_day = hxt_util.get_week_day(p_date)--p_weekday --to_char(p_date,'DY')
352 AND wws.id = wsh.tws_id
353 AND p_date BETWEEN wws.date_from AND NVL (wws.date_to, p_date)
354 AND wws.id = p_work_id
355 AND sht.id = wsh.sht_id;
356
357 --l_lookup_code VARCHAR2 (30);
358 BEGIN
359 g_debug := hr_utility.debug_enabled;
360
361 if g_debug then
362 hr_utility.set_location ('Get_Work_Day', 10);
363 end if;
364
365 -- Select the lookup cod efo rthe weekday from hr_lookups in order to
366 -- avoid translation issues
367
368 /*SELECT lookup_code
369 INTO l_lookup_code
370 FROM fnd_lookup_values --hr_lookups
371 WHERE lookup_type = 'HXT_DAY_OF_WEEK'
372 AND UPPER (RTRIM (meaning)) = UPPER (RTRIM (TO_CHAR (p_date, 'DAY')));
373
374 if g_debug then
375 hr_utility.TRACE ( 'l_lookup_code :'
376 || l_lookup_code);
377 hr_utility.TRACE ( 'p_work_id :'
378 || p_work_id);
379 hr_utility.TRACE ( 'p_date :'
380 || p_date);
381 hr_utility.set_location ('Get_Work_Day', 20);
382 end if;
383 */
384 OPEN work_day (p_work_id, p_date); --, l_lookup_code);
385 FETCH work_day INTO p_standard_start, p_standard_stop, p_hours;
386 CLOSE work_day;
387 if g_debug then
388 hr_utility.TRACE ( 'p_standard_start :'
389 || p_standard_start);
390 hr_utility.TRACE ( 'p_standard_stop :'
391 || p_standard_stop);
392 end if;
393 EXCEPTION
394 WHEN OTHERS
395 THEN
396 if g_debug then
397 hr_utility.set_location ('Get_Work_Day', 30);
398 end if;
399 fnd_message.set_name ('HXC', 'HXC_366283_GET_WRK_DAY_ERR');
400 END get_work_day;
401
402
403 --------------------------------------------------------------------------------
404 PROCEDURE gen_work_plan (
405 p_start DATE,
406 p_end DATE,
407 p_tws_id NUMBER,
408 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
409 p_timecard IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info
410 )
411 IS
412 -- Purpose
413 -- Generate hours worked records FOR the employee who has a work plan.
414 l_days NUMBER;
415 l_elt_id NUMBER;
416 p_elt_id NUMBER;
417 l_time_in DATE;
418 l_time_out DATE;
419 l_time_in_timecard DATE;
420 l_time_out_timecard DATE;
421 l_time_in_day DATE;
422 l_time_out_day DATE;
423 l_standard_start NUMBER;
424 l_standard_stop NUMBER;
425 l_hours NUMBER;
426 p_hours NUMBER;
427 l_hcl_hours NUMBER;
428 l_type VARCHAR2 (9);
429 l_next_index BINARY_INTEGER := 0;
430 l_time_building_block_id NUMBER := 0;
431 l_time_attribute_id NUMBER := 0;
432 l_parent_building_block_id NUMBER;
433 l_parent_id NUMBER;
434
435 CURSOR c_get_base_hours_type (p_earning_policy_id NUMBER)
436 IS
437 SELECT egr.element_type_id
438 FROM hxt_earning_rules egr, hxt_add_elem_info_f aei
439 WHERE egr.egp_id = p_earning_policy_id
440 AND aei.element_type_id = egr.element_type_id
441 AND aei.earning_category = 'REG';
442 l_update_flag varchar2(1) := 'N';
443
444 BEGIN
445 g_debug := hr_utility.debug_enabled;
446
447 if g_debug then
448 hr_utility.set_location ('Gen_Work_Plan', 10);
449 end if;
450 l_update_flag := 'N';
451
452 -- Get number of days to be generated
453 l_days := p_end
454 - p_start;
455
456 if g_debug then
457 hr_utility.TRACE ( 'l_days :'
458 || l_days);
459 end if;
460 IF l_days < 0
461 THEN
462 if g_debug then
463 hr_utility.set_location ('Gen_Work_Plan', 20);
464 end if;
465 fnd_message.set_name ('HXC', 'HXC_366284_NUM_DAYS_ERR');
466 fnd_message.raise_error;
467 END IF;
468
469 if g_debug then
470 hr_utility.TRACE ( 'l_days:'
471 || l_days);
472 end if;
473 --Loop through number of days passed
474 FOR i IN 0 .. l_days
475 LOOP
476 get_work_day (
477 p_start
478 + i,
479 p_tws_id,
480 l_standard_start,
481 l_standard_stop,
482 l_hours
483 );
484 --Calculate Start Date and End Date for TIMECARD and DAY Scope
485 l_time_in_timecard := convert_timecard_time (p_start, 000000);
486
487 if g_debug then
488 hr_utility.trace('l_time_in_timecard:'||l_time_in_timecard);
489 hr_utility.set_location ('Gen_Work_Plan', 21);
490 end if;
491
492 l_time_out_timecard := convert_timecard_time (p_end, 000000, 000000);
493 if g_debug then
494 hr_utility.trace('l_time_out_timecard:'||l_time_out_timecard);
495 hr_utility.trace('FYI...........');
496 end if;
497
498 IF p_timecard.count > 0 THEN
499 if g_debug then
500 hr_utility.set_location ('Gen_Work_Plan', 22);
501 end if;
502 FOR l_cnt IN
503 p_timecard.FIRST .. p_timecard.LAST
504 LOOP
505 if g_debug then
506 hr_utility.TRACE (
507 'p_timecard BB ID is : '
508 || TO_CHAR (
509 p_timecard (l_cnt).time_building_block_id
510 )
511 );
512 hr_utility.TRACE (
513 'p_timecard Scope is : '
514 || (
515 p_timecard (l_cnt).scope
516 )
517 );
518 hr_utility.TRACE (
519 'p_timecard start_time is : '
520 || TO_CHAR (p_timecard (l_cnt).start_time,
521 'dd-mon-yyyy hh24:mi:ss')
522 );
523 hr_utility.TRACE (
524 'p_timecard stop_time is : '
525 || TO_CHAR (p_timecard (l_cnt).stop_time,
526 'dd-mon-yyyy hh24:mi:ss')
527 );
528 end if;
529 END LOOP;
530 END IF;
531 if g_debug then
532 hr_utility.trace('END FYI..............');
533 end if;
534
535 -- Check if a row with the TIMECARD Scope has already been populated in
536 -- the pl/sql building block table. If so, then we do not need to create
537 -- a new row with TIMECARD Scope, instead extend the existing Timecard
538 -- scope block if you come accross another one with the end date of the
539 -- later Timecard.
540 IF p_timecard.count > 0 THEN
541 if g_debug then
542 hr_utility.set_location ('Gen_Work_Plan', 23);
543 end if;
544 FOR l_timecard IN
545 p_timecard.FIRST .. p_timecard.LAST
546 LOOP
547 if g_debug then
548 hr_utility.set_location ('Gen_Work_Plan', 25);
549 end if;
550
551 IF (p_timecard (l_timecard).SCOPE = 'TIMECARD')
552 THEN
553 l_parent_id := p_timecard (l_timecard).time_building_block_id;
554 if g_debug then
555 hr_utility.set_location ('Gen_Work_Plan', 26);
556 hr_utility.trace('p_timecard(l_timecard).stop_time:'
557 ||p_timecard(l_timecard).stop_time);
558 hr_utility.trace('l_time_out_timecard:'
559 ||l_time_out_timecard);
560 end if;
561
562 IF p_timecard(l_timecard).stop_time < l_time_out_timecard
563 THEN
564 if g_debug then
565 hr_utility.set_location ('Gen_Work_Plan', 26.5);
566 end if;
567 p_timecard(l_timecard).stop_time := l_time_out_timecard;
568 l_update_flag := 'Y';
569 END IF;
570
571 if g_debug then
572 hr_utility.set_location ('Gen_Work_Plan', 27);
573 end if;
574 EXIT;
575 if g_debug then
576 hr_utility.set_location ('Gen_Work_Plan', 28);
577 end if;
578 END IF;
579 END LOOP;
580 END IF;
581
582 l_time_in_day := convert_timecard_time ( p_start
583 + i, 000000);
584 l_time_out_day :=
585 convert_timecard_time ( p_start
586 + i, 000000, 235959);
587 if g_debug then
588 hr_utility.TRACE ( 'hours from get_work_day:'
589 || l_hours);
590 end if;
591 -- Create summary record
592 IF (l_hours IS NULL)
593 THEN
594 l_type := 'RANGE';
595 l_time_in := convert_time ( p_start
596 + i, l_standard_start);
597 l_time_out := convert_time (
598 p_start
599 + i,
600 l_standard_start,
601 l_standard_stop
602 );
603 -- l_hours := NULL;--24*(l_time_out - l_time_in);
604 ELSE
605 l_type := 'MEASURE';
606 l_time_in := NULL; --p_start + i ;
607 l_time_out := NULL; --p_start + i ;
608 END IF;
609 l_elt_id := NULL;
610 p_hours := l_hours;
611 if g_debug then
612 hr_utility.TRACE ( 'p_hours:'
613 || p_hours);
614 end if;
615 get_holiday_info (
616 p_start
617 + i,
618 g_asg_rec.hcl_id,
619 l_elt_id,
620 l_hcl_hours
621 );
622 IF l_elt_id IS NOT NULL
623 THEN
624 p_elt_id := l_elt_id;
625 --l_time_in := p_start + i;
626 --l_time_out := p_start + i;
627 IF (fnd_profile.VALUE ('HXT_HOL_HOURS_FROM_HOL_CAL') = 'Y')
628 THEN
629 l_type := 'MEASURE';
630 p_hours := l_hcl_hours;
631 l_time_in := NULL;
632 l_time_out := NULL;
633 END IF;
634 ELSE
635 OPEN c_get_base_hours_type (g_asg_rec.egp_id);
636 FETCH c_get_base_hours_type INTO p_elt_id;
637 CLOSE c_get_base_hours_type;
638 END IF;
639
640 if g_debug then
641 hr_utility.TRACE ( 'p_hours after getting holday info:'
642 || p_hours);
643 end if;
644
645 IF i = 0 and l_update_flag = 'N'
646 THEN
647 if g_debug then
648 hr_utility.TRACE (
649 '---------- Entering TIMECARD Scope -------------------'
650 );
651 end if;
652 -- Enter the TIMECARD Scope in the pl/sql table
653 l_next_index := p_timecard.COUNT
654 + 1;
655 /* Fix for bug 2397763 */
656 /*l_time_building_block_id := l_time_building_block_id + 1;*/
657 l_time_building_block_id := p_timecard.COUNT;
658 l_parent_id := l_time_building_block_id;
659 p_timecard (l_next_index).time_building_block_id :=
660 l_time_building_block_id;
661 p_timecard (l_next_index).TYPE := 'RANGE';
662 p_timecard (l_next_index).measure := NULL;
663 p_timecard (l_next_index).unit_of_measure := 'HOURS';
664 p_timecard (l_next_index).start_time := l_time_in_timecard;
665 p_timecard (l_next_index).stop_time := l_time_out_timecard;
666 p_timecard (l_next_index).parent_building_block_id := NULL;
667 p_timecard (l_next_index).parent_is_new := NULL;
668 p_timecard (l_next_index).SCOPE := 'TIMECARD';
669 p_timecard (l_next_index).object_version_number := NULL;
670 p_timecard (l_next_index).approval_status := NULL;
671 p_timecard (l_next_index).resource_id := g_asg_rec.resource_id;
672 p_timecard (l_next_index).resource_type := 'PERSON';
673 p_timecard (l_next_index).approval_style_id := NULL;
674 p_timecard (l_next_index).date_from := NULL;
675 p_timecard (l_next_index).date_to := NULL;
676 p_timecard (l_next_index).comment_text := NULL;
677 p_timecard (l_next_index).parent_building_block_ovn := NULL;
678 p_timecard (l_next_index).NEW := 'Y';
679 p_timecard (l_next_index).changed := NULL;
680 if g_debug then
681 hr_utility.TRACE (
682 'TIME_BUILDING_BLOCK_ID:'
683 || p_timecard (l_next_index).time_building_block_id
684 );
685 hr_utility.TRACE (
686 'MEASURE :'
687 || p_timecard (l_next_index).measure
688 );
689 hr_utility.TRACE (
690 'START_TIME :'
691 || p_timecard (l_next_index).start_time
692 );
693 hr_utility.TRACE (
694 'STOP_TIME :'
695 || p_timecard (l_next_index).stop_time
696 );
697 hr_utility.TRACE (
698 'PARENT_BUILDING_BLOCK_ID :'
699 || p_timecard (l_next_index).parent_building_block_id
700 );
701 hr_utility.TRACE ( 'SCOPE :'
702 || p_timecard (l_next_index).SCOPE);
703 hr_utility.TRACE (
704 'RESOURCE_ID :'
705 || p_timecard (l_next_index).resource_id
706 );
707 hr_utility.TRACE ( 'NEW:'
708 || p_timecard (l_next_index).NEW);
709 end if;
710 END IF;
711
712
713 if g_debug then
714 hr_utility.TRACE (
715 '---------- Entering DAY Scope -------------------'
716 );
717 end if;
718 l_next_index := p_timecard.COUNT
719 + 1;
720 if g_debug then
721 hr_utility.trace('l_next_index:'||l_next_index);
722 end if;
723 l_time_building_block_id := p_timecard.COUNT-- l_time_building_block_id
724 + 1;
725 if g_debug then
726 hr_utility.trace('l_time_building_block_id:'
727 ||l_time_building_block_id);
728 end if;
729 l_parent_building_block_id := l_time_building_block_id;
730 p_timecard (l_next_index).time_building_block_id :=
731 l_time_building_block_id;
732 p_timecard (l_next_index).TYPE := 'RANGE';
733 p_timecard (l_next_index).measure := NULL;
734 p_timecard (l_next_index).unit_of_measure := 'HOURS';
735 p_timecard (l_next_index).start_time := l_time_in_day;
736 p_timecard (l_next_index).stop_time := l_time_out_day;
737 p_timecard (l_next_index).parent_building_block_id := l_parent_id;
738 p_timecard (l_next_index).parent_is_new := NULL;
739 p_timecard (l_next_index).SCOPE := 'DAY';
740 p_timecard (l_next_index).object_version_number := NULL;
741 p_timecard (l_next_index).approval_status := NULL;
742 p_timecard (l_next_index).resource_id := g_asg_rec.resource_id;
743 p_timecard (l_next_index).resource_type := 'PERSON';
744 p_timecard (l_next_index).approval_style_id := NULL;
745 p_timecard (l_next_index).date_from := NULL;
746 p_timecard (l_next_index).date_to := NULL;
747 p_timecard (l_next_index).comment_text := NULL;
748 p_timecard (l_next_index).parent_building_block_ovn := NULL;
749 p_timecard (l_next_index).NEW := 'Y';
750 p_timecard (l_next_index).changed := NULL;
751 if g_debug then
752 hr_utility.TRACE (
753 'TIME_BUILDING_BLOCK_ID:'
754 || p_timecard (l_next_index).time_building_block_id
755 );
756 hr_utility.TRACE ( 'MEASURE :'
757 || p_timecard (l_next_index).measure);
758 hr_utility.TRACE (
759 'START_TIME :'
760 || p_timecard (l_next_index).start_time
761 );
762 hr_utility.TRACE (
763 'STOP_TIME :'
764 || p_timecard (l_next_index).stop_time
765 );
766 hr_utility.TRACE (
767 'PARENT_BUILDING_BLOCK_ID :'
768 || p_timecard (l_next_index).parent_building_block_id
769 );
770 hr_utility.TRACE ( 'SCOPE :'
771 || p_timecard (l_next_index).SCOPE);
772 hr_utility.TRACE (
773 'RESOURCE_ID :'
774 || p_timecard (l_next_index).resource_id
775 );
776 hr_utility.TRACE ( 'NEW:'
777 || p_timecard (l_next_index).NEW);
778 end if;
779 IF ( (NVL (l_standard_start, 0) <> 0)
780 OR (NVL (l_standard_stop, 0) <> 0)
781 OR (NVL (l_hours, 0) <> 0)
782 )
783 THEN
784 l_next_index := p_timecard.COUNT
785 + 1;
786 l_time_building_block_id := l_time_building_block_id
787 + 1;
788 /* Fix for bug 2397763 */
789 /*l_time_attribute_id := l_time_attribute_id + 1;*/
790 l_time_attribute_id := p_app_attributes.COUNT
791 + 1;
792 if g_debug then
793 hr_utility.TRACE (
794 '---------- Entering DETAIL Scope -------------------'
795 );
796 end if;
797 p_timecard (l_next_index).time_building_block_id :=
798 l_time_building_block_id;
799 p_timecard (l_next_index).TYPE := l_type;
800 p_timecard (l_next_index).measure := p_hours;
801 p_timecard (l_next_index).unit_of_measure := 'HOURS';
802 p_timecard (l_next_index).start_time := l_time_in;
803 p_timecard (l_next_index).stop_time := l_time_out;
804 p_timecard (l_next_index).parent_building_block_id :=
805 l_parent_building_block_id;
806 p_timecard (l_next_index).parent_is_new := NULL;
807 p_timecard (l_next_index).SCOPE := 'DETAIL';
808 p_timecard (l_next_index).object_version_number := NULL;
809 p_timecard (l_next_index).approval_status := NULL;
810 p_timecard (l_next_index).resource_id := g_asg_rec.resource_id;
811 p_timecard (l_next_index).resource_type := 'PERSON';
812 p_timecard (l_next_index).approval_style_id := NULL;
813 p_timecard (l_next_index).date_from := NULL;
814 p_timecard (l_next_index).date_to := NULL;
815 p_timecard (l_next_index).comment_text := NULL;
816 p_timecard (l_next_index).parent_building_block_ovn := NULL;
817 p_timecard (l_next_index).NEW := 'Y';
818 p_timecard (l_next_index).changed := NULL;
819 p_app_attributes (l_next_index).time_attribute_id :=
820 l_time_attribute_id;
821 p_app_attributes (l_next_index).building_block_id :=
822 l_time_building_block_id;
823 p_app_attributes (l_next_index).attribute_name :=
824 'Dummy Element Context';
825 p_app_attributes (l_next_index).attribute_value :=
826 'ELEMENT'
827 || ' '
828 || '-'
829 || ' '
830 || p_elt_id;
831 p_app_attributes (l_next_index).bld_blk_info_type :=
832 'Dummy Element Context';
833 p_app_attributes (l_next_index).CATEGORY := 'ELEMENT';
834 p_app_attributes (l_next_index).updated := NULL;
835 p_app_attributes (l_next_index).changed := NULL;
836 if g_debug then
837 hr_utility.TRACE (
838 '---------- DETAIL Scope timecard info-------------------'
839 );
840 hr_utility.TRACE (
841 'TIME_BUILDING_BLOCK_ID:'
842 || p_timecard (l_next_index).time_building_block_id
843 );
844 hr_utility.TRACE ( 'TYPE:'
845 || p_timecard (l_next_index).TYPE);
846 hr_utility.TRACE (
847 'MEASURE :'
848 || p_timecard (l_next_index).measure
849 );
850 hr_utility.TRACE (
851 'START_TIME :'
852 || p_timecard (l_next_index).start_time
853 );
854 hr_utility.TRACE (
855 'STOP_TIME :'
856 || p_timecard (l_next_index).stop_time
857 );
858 hr_utility.TRACE (
859 'PARENT_BUILDING_BLOCK_ID :'
860 || p_timecard (l_next_index).parent_building_block_id
861 );
862 hr_utility.TRACE ( 'SCOPE :'
863 || p_timecard (l_next_index).SCOPE);
864 hr_utility.TRACE (
865 'RESOURCE_ID :'
866 || p_timecard (l_next_index).resource_id
867 );
868 hr_utility.TRACE ( 'NEW:'
869 || p_timecard (l_next_index).NEW);
870 hr_utility.TRACE (
871 '---------- DETAIL Scope attributes info-------------------'
872 );
873 hr_utility.TRACE (
874 'TIME_ATTRIBUTE_ID:'
875 || p_app_attributes (l_next_index).time_attribute_id
876 );
877 hr_utility.TRACE (
878 'BUILDING_BLOCK_ID :'
879 || p_app_attributes (l_next_index).building_block_id
880 );
881 hr_utility.TRACE (
882 'ATTRIBUTE_NAME :'
883 || p_app_attributes (l_next_index).attribute_name
884 );
885 hr_utility.TRACE (
886 'ATTRIBUTE_VALUE :'
887 || p_app_attributes (l_next_index).attribute_value
888 );
889 hr_utility.TRACE (
890 'BLD_BLK_INFO_TYPE:'
891 || p_app_attributes (l_next_index).bld_blk_info_type
892 );
893 hr_utility.TRACE (
894 'CATEGORY :'
895 || p_app_attributes (l_next_index).CATEGORY
896 );
897 end if;
898 END IF;
899 END LOOP;
900
901
902 EXCEPTION
903 WHEN OTHERS
904 THEN
905 fnd_message.set_name ('HXC', 'HXC_366285_GEN_WRK_PLAN_ERR');
906 END gen_work_plan;
907
908 --------------------------------------------------------------------------------
909 PROCEDURE gen_rot_plan (
910 p_start DATE,
911 p_end DATE,
912 p_rtp_id NUMBER,
913 p_app_attributes OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
914 p_timecard OUT NOCOPY hxc_self_service_time_deposit.timecard_info
915 )
916 IS
917
918 -- Purpose
919 -- Generate hours worked records FOR employees who have a work plan
920 -- and rotation plan.
921 CURSOR cur_hrs (p_start DATE, p_end DATE, p_rtp_id NUMBER)
922 IS
923 SELECT rt1.tws_id tws_id,
924 -- Use the latest of rotation plan start dates or assignment start date
925 TRUNC (
926 DECODE (
927 SIGN ( rt1.start_date
928 - p_start),
929 -1, p_start,
930 rt1.start_date
931 )
932 ) start_date,
933 -- simplification of the above code as follows:
934 -- reverting back this change since teh cursor returns wrong
935 -- start_date when rt1.start_date is NULL
936 /* greatest(rt1.start_date,p_start) start_date,*/
937 -- Use the earliest of rotation plan end dates or assignment end date
938 NVL (
939 TRUNC (
940 DECODE (
941 SIGN ( MIN ( rt2.start_date
942 - 1)
943 - p_end),
944 -1, MIN ( rt2.start_date
945 - 1),
946 p_end
947 )
948 ),
949 hr_general.end_of_time
950 ) end_date
951 -- simplification of the above code as follows:
952 -- reverting back this change since the cursor returns wrong
953 -- end_date when rt2.start_date is NULL
954 /* NVL(least(min(rt2.start_date-1),p_end)
955 ,hr_general.end_of_time ) end_date*/
956 FROM hxt_rotation_schedules rt1, hxt_rotation_schedules rt2
957 WHERE rt1.rtp_id = rt2.rtp_id(+)
958 AND rt2.start_date(+) > rt1.start_date
959 AND rt1.rtp_id = p_rtp_id
960 AND p_end >= rt1.start_date
961 GROUP BY rt1.tws_id, rt1.start_date
962 HAVING p_start <=
963 NVL ( MIN (rt2.start_date)
964 - 1, hr_general.end_of_time)
965 ORDER BY rt1.start_date;
966
967 l_next_index BINARY_INTEGER;
968 l_proc varchar2(100);
969
970 BEGIN
971 g_debug := hr_utility.debug_enabled;
972
973 if g_debug then
974 l_proc := 'HXT_TIMECARD_INFO.gen_rot_plan';
975 hr_utility.set_location(l_proc,10);
976
977 hr_utility.trace('p_start :'||p_start);
978 hr_utility.trace('p_end :'||p_end);
979 hr_utility.trace('p_rtp_id :'||p_rtp_id);
980 end if;
981
982 FOR hrs_rec IN cur_hrs (p_start, p_end, p_rtp_id)
983 LOOP
984 if g_debug then
985 hr_utility.set_location(l_proc,20);
986 end if;
987
988 gen_work_plan (
989 hrs_rec.start_date,
990 hrs_rec.end_date,
991 hrs_rec.tws_id,
992 p_app_attributes,
993 p_timecard
994 );
995
996 if g_debug then
997 hr_utility.set_location(l_proc,30);
998 end if;
999
1000 END LOOP;
1001 if g_debug then
1002 hr_utility.set_location(l_proc,40);
1003 end if;
1004
1005 EXCEPTION
1006 WHEN OTHERS
1007 THEN
1008 fnd_message.set_name ('HXC', 'HXC_366286_GEN_ROT_PLAN_ERR');
1009 fnd_message.raise_error;
1010 END;
1011
1012
1013 --------------------------------------------------------------------------------
1014 PROCEDURE get_holiday_info (
1015 p_date IN DATE,
1016 p_hcl_id IN NUMBER,
1017 p_elt_id OUT NOCOPY NUMBER,
1018 p_hours OUT NOCOPY NUMBER
1019 )
1020 IS
1021
1022 -- Procedure
1023 -- Get_Holiday_Info
1024 -- Purpose
1025 -- Return holiday earning and default hours for input holiday
1026 -- calendar if input day is holiday.
1027 -- Arguments
1028 -- p_date The date being checked.
1029 -- p_hcl_id The Holiday Calendar to be checked.
1030 -- Returns:
1031 -- p_elt_id - holiday earning ID(element_type_id) or null
1032 -- p_hours - paid hours for holiday
1033
1034 CURSOR cur_hcl (p_date DATE, p_hcl_id NUMBER)
1035 IS
1036 SELECT DECODE (hhd.hours, NULL, NULL, hcl.element_type_id), hhd.hours
1037 FROM hxt_holiday_calendars hcl, hxt_holiday_days hhd
1038 WHERE hhd.holiday_date = p_date
1039 AND hcl.id = hhd.hcl_id
1040 AND p_date BETWEEN hcl.effective_start_date
1041 AND hcl.effective_end_date
1042 AND hcl.id = p_hcl_id;
1043 BEGIN
1044 OPEN cur_hcl (p_date, p_hcl_id);
1045 FETCH cur_hcl INTO p_elt_id, p_hours;
1046 CLOSE cur_hcl;
1047 EXCEPTION
1048 WHEN OTHERS
1049 THEN
1050 fnd_message.set_name ('HXC', 'HXC_366287_GET_HOL_ERR');
1051 END get_holiday_info;
1052
1053
1054 ------------------------------------------------------------------
1055 FUNCTION convert_time (
1056 p_date DATE,
1057 p_time_in NUMBER,
1058 p_time_out NUMBER DEFAULT NULL
1059 )
1060 RETURN DATE
1061 IS
1062 l_date DATE := p_date;
1063 l_convert NUMBER := NVL (p_time_out, p_time_in);
1064 BEGIN
1065 IF (p_time_out IS NOT NULL)
1066 AND ( p_time_out < p_time_in
1067 OR (p_time_out = p_time_in AND p_time_in <> 0)
1068 )
1069 THEN
1070 l_date := l_date
1071 + 1; -- use next day if past midnight
1072 END IF;
1073
1074 RETURN (TO_DATE (
1075 TO_CHAR (l_date, 'MMDDYYYY')
1076 || TO_CHAR (l_convert, '0009'),
1077 'MMDDYYYYHH24MI'
1078 )
1079 );
1080 EXCEPTION
1081 WHEN OTHERS
1082 THEN
1083 fnd_message.set_name ('HXC', 'HXC_366288_CONVERT_TIME_ERR');
1084 END convert_time;
1085
1086
1087 ------------------------------------------------------------------------
1088 FUNCTION convert_timecard_time (
1089 p_date DATE,
1090 p_time_in NUMBER,
1091 p_time_out NUMBER DEFAULT NULL
1092 )
1093 RETURN DATE
1094 IS
1095 l_date DATE := p_date;
1096 l_convert NUMBER := NVL (p_time_out, p_time_in);
1097 BEGIN
1098 IF (p_time_out IS NOT NULL)
1099 AND ( p_time_out < p_time_in
1100 OR (p_time_out = p_time_in AND p_time_in <> 0)
1101 )
1102 THEN
1103 l_date := l_date
1104 + 1; -- use next day if past midnight
1105 END IF;
1106
1107 RETURN (TO_DATE (
1108 TO_CHAR (l_date, 'MMDDYYYY')
1109 || TO_CHAR (l_convert, '000009'),
1110 'MMDDYYYYHH24MISS'
1111 )
1112 );
1113 EXCEPTION
1114 WHEN OTHERS
1115 THEN
1116 fnd_message.set_name ('HXC', 'HXC_366288_CONVERT_TIME_ERR');
1117 END convert_timecard_time;
1118 ------------------------------------------------------------------------
1119
1120 --begin
1121
1122
1123 END hxt_timecard_info;