[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMECARD_UTILITIES
Source
1 PACKAGE BODY hxc_timecard_utilities AS
2 /* $Header: hxctcutil.pkb 120.25.12010000.2 2008/11/13 12:20:37 bbayragi ship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5 g_assignment_periods periods;
6 g_past_date_limit DATE;
7 g_future_date_limit DATE;
8 g_rec_period_start_date hxc_recurring_periods.start_date%TYPE;
9 g_period_type hxc_recurring_periods.period_type%TYPE;
10 g_duration_in_days hxc_recurring_periods.duration_in_days%TYPE;
11 g_num_past_entries NUMBER := 10; --hardcoded
12 g_num_future_entries NUMBER := 10; --hardcoded
13 g_separator VARCHAR2(1) := '|';
14 g_date_format VARCHAR2(20) := 'YYYY/MM/DD';
15 g_initialized VARCHAR2(20) := 'N';
16 g_package VARCHAR2(30) := 'hxc_timecard_utilities.';
17
18 PROCEDURE get_period_by_duration(
19 p_rec_period_start_date IN DATE
20 ,p_duration_in_days IN NUMBER
21 ,p_current_date IN DATE
22 ,p_start_date OUT NOCOPY DATE
23 ,p_end_date OUT NOCOPY DATE
24 )
25 IS
26 l_start_date DATE;
27 l_end_date DATE;
28 BEGIN
29 --current period's start time
30 p_start_date := p_rec_period_start_date +
31 (p_duration_in_days * FLOOR(((p_current_date - p_rec_period_start_date)/p_duration_in_days)));
32
33 p_end_date := p_start_date + p_duration_in_days - 1;
34 END get_period_by_duration;
35
36 -- ----------------------------------------------------------------------------
37 -- |--------------------< get_more_period_value>----------------------|
38 -- this function is called from the get_periods procedure.. when the generate_periods
39 -- procedure is called for existing timecards in the time_building_blocks table, this
40 -- function helps in checking whether the particular period has to be flagged to
41 -- get displayed as More Periods in the timecard UI.
42 -- ----------------------------------------------------------------------------
43
44 FUNCTION get_more_period_value( p_periods in periods
45 ,p_start_date in date
46 ,p_end_date in date
47 ) RETURN varchar2 IS
48 l_period_index Number;
49
50 begin
51 l_period_index := p_periods.first;
52
53 WHILE p_periods.exists(l_period_index)
54 LOOP
55 if(trunc(p_periods(l_period_index).start_date) = trunc(p_start_date)) and
56 (trunc(p_periods(l_period_index).end_date) = trunc(p_end_date)) then
57 return p_periods(l_period_index).p_set_more_period;
58 end if;
59 l_period_index := p_periods.next(l_period_index);
60 END LOOP;
61 return NULL;
62 END get_more_period_value;
63
64
65 -- ----------------------------------------------------------------------------
66 -- |--------------------< check_period_archived>----------------------|
67 -- this function determines whether the timecard period has been archived or not.
68 -- ----------------------------------------------------------------------------
69
70 FUNCTION check_period_archived(p_stop_date IN date) RETURN BOOLEAN IS
71
72 CURSOR c_is_archived(p_stop_date IN date)
73 IS
74 SELECT 'Y'
75 FROM hxc_data_sets
76 WHERE p_stop_date BETWEEN START_DATE AND END_DATE
77 AND STATUS IN ( 'OFF_LINE', 'RESTORE_IN_PROGRESS', 'BACKUP_IN_PROGRESS' );
78
79 l_archived boolean;
80 l_dummy VARCHAR2(1);
81
82 BEGIN
83 l_archived := FALSE;
84
85 OPEN c_is_archived (p_stop_date);
86 Fetch c_is_archived into l_dummy;
87
88 IF (c_is_archived%FOUND) THEN
89 l_archived := TRUE;
90 END IF;
91 CLOSE c_is_archived;
92 RETURN l_archived;
93 END check_period_archived;
94
95 FUNCTION check_assignments(
96 p_period_start IN DATE
97 ,p_period_end IN DATE
98 ) RETURN BOOLEAN
99 IS
100 l_assignment_index NUMBER;
101
102 BEGIN
103 l_assignment_index := g_assignment_periods.first;
104
105 LOOP
106 EXIT WHEN NOT g_assignment_periods.exists(l_assignment_index);
107
108 IF p_period_start > g_assignment_periods(l_assignment_index).end_date
109 OR p_period_end < g_assignment_periods(l_assignment_index).start_date
110 THEN
111 NULL;
112 ELSE
113 RETURN TRUE;
114 END IF;
115
116 l_assignment_index := g_assignment_periods.next(l_assignment_index);
117 END LOOP;
118
119 RETURN FALSE;
120 END check_assignments;
121
122 -- ----------------------------------------------------------------------------
123 -- |--------------------< find_period_already_exist >----------------------|
124 -- Returns the index position of the period if it already exists.
125 -- ----------------------------------------------------------------------------
126
127 FUNCTION find_period_already_exist( p_period IN periods , p_start_date in date, p_end_date in date)
128 RETURN NUMBER is
129 l_index NUMBER;
130 BEGIN
131 l_index := p_period.first;
132 loop
133 EXIT WHEN NOT p_period.exists(l_index);
134 if(( trunc(p_period(l_index).start_date) = trunc(p_start_date))
135 AND
136 (trunc(p_period(l_index).end_date) = trunc(p_end_date))) THEN
137 return l_index;
138 END IF;
139 l_index := p_period.next(l_index);
140 END LOOP;
141 return -1;
142 END find_period_already_exist;
143
144 PROCEDURE process_assignments(
145 p_period IN time_period
146 ,p_assignment_periods IN periods
147 ,p_return_periods IN OUT NOCOPY periods
148 )
149 IS
150 l_return_index NUMBER;
151 l_found_index NUMBER;
152 l_start_date date;
153 l_end_date date;
154 BEGIN
155 IF p_return_periods.count = 0
156 THEN
157 l_return_index := 0;
158 ELSE
159 l_return_index := p_return_periods.last + 1;
160 END IF;
161
162 IF (p_period.exist_flag = hxc_timecard.c_existing_period_indicator) THEN
163
164 --Remove the entry if its already found. We need to keep the existing period
165 --in the list, rather than a open period.
166 l_found_index := find_period_already_exist(p_return_periods,
167 p_period.start_date,p_period.end_date);
168 if (l_found_index > 0) then
169 p_return_periods.delete(l_found_index);
170 ELSE
171 l_return_index := l_return_index + 1;
172 END IF;
173
174 p_return_periods(l_return_index).start_date := p_period.start_date;
175 p_return_periods(l_return_index).end_date := p_period.end_date;
176 p_return_periods(l_return_index).exist_flag := p_period.exist_flag;
177 p_return_periods(l_return_index).p_set_more_period := p_period.p_set_more_period;
178
179 IF (check_period_archived(p_period.end_date)) THEN
180 p_return_periods(l_return_index).exist_flag := hxc_timecard.c_archived_period_indicator;
181 ELSE
182 p_return_periods(l_return_index).exist_flag := p_period.exist_flag;
183 END IF;
184
185 RETURN;
186 END IF;
187
188
189 FOR l_assign_index in p_assignment_periods.first .. p_assignment_periods.last
190 LOOP
191
192 IF p_assignment_periods(l_assign_index).start_date <= p_period.end_date
193 AND p_assignment_periods(l_assign_index).end_date >= p_period.start_date
194 THEN
195 l_start_date := greatest(p_assignment_periods(l_assign_index).start_date,
196 p_period.start_date);
197 l_end_date := least(p_assignment_periods(l_assign_index).end_date, p_period.end_date);
198 if (find_period_already_exist(p_return_periods,l_start_date,l_end_date) < 0) then
199 l_return_index := l_return_index + 1;
200 p_return_periods(l_return_index).start_date
201 := l_start_date;
202 p_return_periods(l_return_index).end_date
203 := l_end_date;
204 p_return_periods(l_return_index).p_set_more_period := p_period.p_set_more_period;
205 p_return_periods(l_return_index).exist_flag := p_period.exist_flag;
206 end if;
207 END IF;
208 END LOOP;
209
210 END process_assignments;
211
212
213
214 PROCEDURE generate_periods(
215 p_periods IN OUT NOCOPY periods
216 ,p_start_date IN DATE
217 ,p_end_date IN DATE
218 ,p_last_period_end IN DATE
219 ,p_past_date_limit IN DATE
220 ,p_future_date_limit IN DATE
221 ,p_exists IN VARCHAR2
222 ,p_show_existing_timecard IN VARCHAR2 DEFAULT 'Y'
223 ,p_set_more_period IN VARCHAR2 DEFAULT NULL
224 )
225 IS
226 l_index NUMBER;
227 l_new_start DATE;
228 l_new_end DATE;
229 l_active BOOLEAN;
230 BEGIN
231
232 IF p_last_period_end IS NOT NULL
233 THEN
234 --find out if there is a transition period
235 IF p_last_period_end + 1 < p_start_date
236 THEN
237 l_index := NVL(p_periods.last, 0) + 1;
238
239 --we need to make sure a transition period is within an active
240 --assignment
241 l_new_start := p_last_period_end + 1;
242 l_new_end := p_start_date - 1;
243 l_active := check_assignments(l_new_start, l_new_end);
244 IF l_new_start <= p_future_date_limit
245 AND l_new_end >= p_past_date_limit
246 AND l_active
247 THEN
248 p_periods(l_index).start_date := l_new_start;
249 p_periods(l_index).end_date := l_new_end;
250 p_periods(l_index).p_set_more_period := p_set_more_period;
251 END IF;
252 END IF;
253 END IF;
254
255
256 --add this period
257 IF p_exists IS NULL
258 AND (NOT check_assignments(p_start_date, p_end_date))
259 THEN
260 RETURN;
261 END IF;
262
263 IF (trunc(p_start_date) <= trunc(p_future_date_limit)
264 AND trunc(p_end_date) >= trunc(p_past_date_limit))
265 THEN
266 l_index := NVL(p_periods.last, 0) + 1;
267 p_periods(l_index).start_date := p_start_date;
268 p_periods(l_index).end_date := p_end_date;
269 p_periods(l_index).exist_flag := p_exists;
270 p_periods(l_index).p_set_more_period := p_set_more_period;
271 END IF;
272
273 END generate_periods;
274
275
276
277 FUNCTION add_period(
278 p_periods IN OUT NOCOPY periods
279 ,p_start_date IN DATE
280 ,p_end_date IN DATE
281 ,p_position IN VARCHAR2 DEFAULT 'AFTER'
282 ,p_future_date_limit IN DATE DEFAULT NULL
283 ,p_assignment_end IN DATE DEFAULT NULL
284 ,p_set_more_period IN VARCHAR2 DEFAULT NULL
285 ) RETURN BOOLEAN
286 IS
287 l_index NUMBER;
288
289 BEGIN
290
291
292 IF g_debug THEN
293 hr_utility.trace('add_period start=' || to_char(p_start_date, 'YYYY/MM/DD'));
294 hr_utility.trace('add_period end=' || to_char(p_end_date, 'YYYY/MM/DD'));
295 END IF;
296
297 IF p_position = 'AFTER'
298 THEN
299 IF TRUNC(p_start_date) > TRUNC(p_future_date_limit)
300 OR TRUNC(p_start_date) > TRUNC(p_assignment_end)
301 THEN
302 IF g_debug THEN
303 hr_utility.trace('not added');
304 END IF;
305 RETURN FALSE;
306 END IF;
307
308 l_index := NVL(p_periods.last, 0) + 1;
309 ELSE
310 l_index := NVL(p_periods.first, 0) - 1;
311 END IF;
312
313 p_periods(l_index).start_date := p_start_date;
314 p_periods(l_index).end_date := p_end_date;
315 p_periods(l_index).p_set_more_period := p_set_more_period;
316
317 IF g_debug THEN
318 hr_utility.trace('added ok');
319 END IF;
320 RETURN TRUE;
321 END add_period;
322
323
324 PROCEDURE find_current_period(
325 p_rec_period_start_date IN DATE
326 ,p_period_type IN VARCHAR2
327 ,p_duration_in_days IN NUMBER
328 ,p_current_date IN DATE
329 ,p_period_start OUT NOCOPY DATE
330 ,p_period_end OUT NOCOPY DATE
331 )
332 IS
333 BEGIN
334 IF p_period_type IS NULL
335 THEN
336 get_period_by_duration(
337 p_rec_period_start_date => p_rec_period_start_date
338 ,p_duration_in_days => p_duration_in_days
339 ,p_current_date => p_current_date
340 ,p_start_date => p_period_start
341 ,p_end_date => p_period_end
342 );
343 ELSE
344 hxc_period_evaluation.period_start_stop(
345 p_current_date => p_current_date
346 ,p_rec_period_start_date => p_rec_period_start_date
347 ,l_period_start => p_period_start
348 ,l_period_end => p_period_end
349 ,l_base_period_type => p_period_type
350 );
351 END IF;
352 END find_current_period;
353
354
355 PROCEDURE find_empty_period(
356 p_future IN BOOLEAN
357 ,p_periods IN periods
358 ,p_empty_period IN OUT NOCOPY VARCHAR2
359 ,p_default_tc_period IN VARCHAR2
360 )
361 IS
362 l_index NUMBER;
363 BEGIN
364
365
366 p_empty_period := NULL;
367
368 IF p_future
369 THEN
370 l_index := p_periods.first;
371 ELSE
372 l_index := p_periods.last;
373 END IF;
374
375 LOOP
376 EXIT WHEN NOT p_periods.exists(l_index);
377
378 IF g_debug THEN
379 hr_utility.trace('start=' || p_periods(l_index).start_date
380 || ' end=' || p_periods(l_index).end_date
381 || 'exists=' || NVL(p_periods(l_index).exist_flag, 'N'));
382 END IF;
383
384 IF NVL(p_periods(l_index).exist_flag, 'N') <> hxc_timecard.c_existing_period_indicator AND NVL(p_periods(l_index).exist_flag, 'N') <> hxc_timecard.c_archived_period_indicator
385 THEN
386
387
388 IF not p_future and p_default_tc_period = 'EARLIEST' AND
389 TRUNC(SYSDATE) >= p_periods(l_index).start_date THEN
390
391 p_empty_period := TO_CHAR(p_periods(l_index).start_date, g_date_format)
392 || g_separator
393 || TO_CHAR(p_periods(l_index).end_date, g_date_format);
394 --RETURN;
395 END IF;
396
397 IF not p_future and p_default_tc_period = 'CLOSEST' and
398 TRUNC(SYSDATE) >= p_periods(l_index).start_date THEN
399
400 IF SYSDATE between p_periods(l_index).start_date and p_periods(l_index).end_date
401 THEN
402 null;
403 ELSE
404
405 p_empty_period := TO_CHAR(p_periods(l_index).start_date, g_date_format)
406 || g_separator
407 || TO_CHAR(p_periods(l_index).end_date, g_date_format);
408 RETURN;
409 END IF;
410
411 END IF;
412
413 IF( (p_future AND TRUNC(SYSDATE) <= p_periods(l_index).end_date) OR (NOT p_future))
414 AND p_default_tc_period = 'FUTURE'
415 THEN
416 p_empty_period := TO_CHAR(p_periods(l_index).start_date, g_date_format)
417 || g_separator
418 || TO_CHAR(p_periods(l_index).end_date, g_date_format);
419 RETURN;
420 END IF;
421 END IF;
422
423 IF p_future
424 THEN
425 l_index := p_periods.next(l_index);
426 ELSE
427 l_index := p_periods.prior(l_index);
428 END IF;
429
430 END LOOP;
431
432 END find_empty_period;
433
434
435
436 FUNCTION get_periods(
437 p_resource_id IN NUMBER
438 ,p_resource_type IN VARCHAR2
439 ,p_current_date IN DATE
440 ,p_show_existing_timecard IN VARCHAR2
441
442 )
443 RETURN periods
444 IS
445 l_start_date DATE;
446 l_end_date DATE;
447 l_current_date DATE;
448 l_period_index NUMBER;
449 l_last_period_end DATE;
450 l_new_periods periods;
451 l_periods periods;
452 l_period_count NUMBER;
453 l_assignment_index NUMBER;
454 l_added BOOLEAN;
455 l_processed_periods periods;
456 l_dummy varchar2(15);
457 l_proc VARCHAR2(50);
458
459 l_set_more_period VARCHAR2(1);
460 l_approval_status HXC_TIME_BUILDING_BLOCKS.APPROVAL_STATUS%TYPE;
461
462 -- New Fields.
463 l_assignment_processed_periods periods;
464 l_index number;
465
466 CURSOR c_timecards(
467 p_resource_id IN NUMBER
468 ,p_resource_type IN VARCHAR2
469 ,p_first_start_date IN DATE
470 ,p_last_end_date IN DATE
471 )
472 IS
473 SELECT START_TIME,
474 STOP_TIME,
475 APPROVAL_STATUS
476 FROM hxc_time_building_blocks
477 WHERE SCOPE = 'TIMECARD'
478 AND DATE_TO = hr_general.end_of_time
479 AND RESOURCE_ID = p_resource_id
480 AND RESOURCE_TYPE = p_resource_type
481 AND STOP_TIME >= p_first_start_date
482 AND START_TIME <= p_last_end_date
483 ORDER BY START_TIME;
484
485 BEGIN
486
487 g_debug := hr_utility.debug_enabled;
488
489 IF g_debug THEN
490 l_proc := 'get_periods';
491 hr_utility.set_location (g_package||l_proc, 10);
492 END IF;
493
494
495
496 --get current period
497 find_current_period(
498 p_rec_period_start_date => g_rec_period_start_date
499 ,p_period_type => g_period_type
500 ,p_duration_in_days => g_duration_in_days
501 ,p_current_date => p_current_date
502 ,p_period_start => l_start_date
503 ,p_period_end => l_end_date
504 );
505
506 IF g_debug THEN
507 hr_utility.set_location (g_package||l_proc, 20);
508 END IF;
509
510
511 /* Aug 23 always add current period
512 l_added := add_period(
513 p_periods => l_periods
514 ,p_start_date => l_start_date
515 ,p_end_date => l_end_date
516 ,p_future_date_limit => g_future_date_limit
517 ,p_assignment_end => g_assignment_periods(g_assignment_periods.last).end_date
518 );
519
520 IF g_debug THEN
521 hr_utility.set_location (g_package||l_proc, 30);
522 END IF;
523
524 -- this case only happens when we are looking for an empty period
525 -- in the future. Since we already know the period before the current
526 -- on are not empty, if the current one is already out of future boundary
527 -- (future date limit, assignment end date) it doesn't make sense to
528 -- continue looking at other periods beyond this one.
529
530 IF NOT l_added
531 THEN
532 RETURN l_periods;
533 END IF;
534 */
535
536 l_periods(1).start_date := l_start_date;
537 l_periods(1).end_date := l_end_date;
538
539 IF g_debug THEN
540 hr_utility.set_location (g_package||l_proc, 40);
541 END IF;
542 --get past periods
543
544 l_period_count := 0;
545 l_current_date := l_start_date - 1;
546 l_assignment_index := g_assignment_periods.last;
547
548 IF g_debug THEN
549 hr_utility.trace('l_period_count=' || l_period_count);
550 hr_utility.trace('g_num_past_entries=' || g_num_past_entries);
551 END IF;
552
553
554 WHILE l_period_count <= g_num_past_entries
555 LOOP
556 IF g_debug THEN
557 hr_utility.set_location (g_package||l_proc, 50);
558 END IF;
559
560 find_current_period(
561 p_rec_period_start_date => g_rec_period_start_date
562 ,p_period_type => g_period_type
563 ,p_duration_in_days => g_duration_in_days
564 ,p_current_date => l_current_date
565 ,p_period_start => l_start_date
566 ,p_period_end => l_end_date
567 );
568
569 IF g_debug THEN
570 hr_utility.set_location (g_package||l_proc, 60);
571 END IF;
572
573 IF l_end_date < g_past_date_limit
574 THEN
575 EXIT;
576 END IF;
577
578 IF g_debug THEN
579 hr_utility.set_location (g_package||l_proc, 61);
580 END IF;
581
582 IF TRUNC(l_end_date) >= TRUNC(g_assignment_periods(l_assignment_index).start_date)
583 THEN
584 -- only if there is atleast 1 period more than normally we show,
585 -- we will show the More Periods... option.
586 IF trunc(l_start_date) >= trunc(g_past_date_limit) THEN
587 IF ((l_period_count = g_num_past_entries) AND (p_show_existing_timecard = 'Y')) THEN
588 l_added := add_period(
589 p_periods => l_periods
590 ,p_start_date => l_start_date
591 ,p_end_date => l_end_date
592 ,p_position => 'BEFORE'
593 ,p_set_more_period => hxc_timecard.c_more_period_indicator
594 );
595 ELSE
596 l_added := add_period(
597 p_periods => l_periods
598 ,p_start_date => l_start_date
599 ,p_end_date => l_end_date
600 ,p_position => 'BEFORE'
601 );
602
603 END IF;
604 END IF;
605 l_period_count := l_period_count + 1;
606 l_current_date := l_start_date - 1;
607
608 IF g_debug THEN
609 hr_utility.set_location (g_package||l_proc, 70);
610 END IF;
611 ELSE
612 -- earlier than current assignment period, look at the assignment
613 -- following this one
614 l_assignment_index := g_assignment_periods.prior(l_assignment_index);
615
616 IF g_assignment_periods.exists(l_assignment_index)
617 THEN
618 -- this check is to eliminate duplicate entries when the previous
619 -- assignment end date is less than a period away from current
620 -- period start_date
621 IF g_assignment_periods(l_assignment_index).end_date <= l_end_date
622 THEN
623 IF g_debug THEN
624 hr_utility.set_location (g_package||l_proc, 80);
625 END IF;
626
627 l_current_date := g_assignment_periods(l_assignment_index).end_date;
628 ELSE
629 IF g_debug THEN
630 hr_utility.set_location (g_package||l_proc, 90);
631 END IF;
632
633 l_current_date := l_end_date;
634 END IF;
635 ELSE
636 IF g_debug THEN
637 hr_utility.set_location (g_package||l_proc, 100);
638 END IF;
639
640 EXIT;
641 END IF;
642 END IF;
643 END LOOP;
644
645
646
647 IF g_debug THEN
648 hr_utility.set_location (g_package||l_proc, 120);
649 END IF;
650
651 --get future periods
652 l_assignment_index := g_assignment_periods.last;
653
654 l_period_count := 0;
655 l_current_date := l_periods(1).end_date + 1; -- need work
656 WHILE l_period_count <= g_num_future_entries
657 LOOP
658 find_current_period(
659 p_rec_period_start_date => g_rec_period_start_date
660 ,p_period_type => g_period_type
661 ,p_duration_in_days => g_duration_in_days
662 ,p_current_date => l_current_date
663 ,p_period_start => l_start_date
664 ,p_period_end => l_end_date
665 );
666
667 IF ((l_period_count = g_num_future_entries) AND (p_show_existing_timecard = 'Y')) then
668 l_added := add_period(
669 p_periods => l_periods
670 ,p_start_date => l_start_date
671 ,p_end_date => l_end_date
672 ,p_future_date_limit => g_future_date_limit
673 ,p_assignment_end => g_assignment_periods(l_assignment_index).end_date
674 ,p_set_more_period =>hxc_timecard.c_more_period_indicator
675 );
676 ELSE
677 l_added := add_period(
678 p_periods => l_periods
679 ,p_start_date => l_start_date
680 ,p_end_date => l_end_date
681 ,p_future_date_limit => g_future_date_limit
682 ,p_assignment_end => g_assignment_periods(l_assignment_index).end_date
683 );
684 END IF;
685 IF NOT l_added
686 THEN
687 EXIT;
688 END IF;
689
690 l_period_count := l_period_count + 1;
691 l_current_date := l_end_date + 1;
692
693 END LOOP;
694
695 IF l_periods.count = 0
696 THEN
697 RETURN l_periods;
698 END IF;
699
700 l_period_index := l_periods.first;
701 l_last_period_end := l_periods(l_period_index).start_date - 1;
702
703 OPEN c_timecards(
704 p_resource_id => p_resource_id
705 ,p_resource_type => p_resource_type
706 ,p_first_start_date => l_periods(l_periods.first).start_date
707 ,p_last_end_date => l_periods(l_periods.last).end_date
708 );
709
710 LOOP
711 FETCH c_timecards INTO l_start_date, l_end_date,l_approval_status;
712 EXIT WHEN c_timecards%NOTFOUND;
713
714 WHILE l_periods.exists(l_period_index)
715 AND l_periods(l_period_index).end_date < l_end_date
716 LOOP
717
718 -- 115.34 change. To differentiate an archived time period from a normal period.
719
720
721 if(check_period_archived(l_periods(l_period_index).end_date)) then
722
723 generate_periods(
724 p_periods => l_new_periods
725 ,p_start_date => l_periods(l_period_index).start_date
726 ,p_end_date => l_periods(l_period_index).end_date
727 ,p_last_period_end => l_last_period_end
728 ,p_past_date_limit => g_past_date_limit
729 ,p_future_date_limit => g_future_date_limit
730 ,p_exists => hxc_timecard.c_archived_period_indicator
731 ,p_show_existing_timecard => p_show_existing_timecard
732 ,p_set_more_period => l_periods(l_period_index).p_set_more_period
733 );
734
735 ELSE
736 generate_periods(
737 p_periods => l_new_periods
738 ,p_start_date => l_periods(l_period_index).start_date
739 ,p_end_date => l_periods(l_period_index).end_date
740 ,p_last_period_end => l_last_period_end
741 ,p_past_date_limit => g_past_date_limit
742 ,p_future_date_limit => g_future_date_limit
743 ,p_exists => NULL
744 ,p_set_more_period => l_periods(l_period_index).p_set_more_period
745 );
746 END IF;
747
748
749 l_last_period_end := l_periods(l_period_index).end_date;
750 l_period_index := l_periods.next(l_period_index);
751 END LOOP;
752
753 IF g_debug THEN
754 hr_utility.set_location (g_package||l_proc, 70);
755 END IF;
756 -- For this period we need not check whether its archived or not, as this is an existing period.
757 -- Existing periods are found from hxc_time_building_blocks table, which means the data is
758 -- present in the online tables.
759
760 --add timecard row
761 IF (l_approval_status NOT IN ('ERROR')) THEN
762 generate_periods(
763 p_periods => l_new_periods
764 ,p_start_date => l_start_date
765 ,p_end_date => l_end_date
766 ,p_last_period_end => l_last_period_end
767 ,p_past_date_limit => g_past_date_limit
768 ,p_future_date_limit => g_future_date_limit
769 ,p_exists => hxc_timecard.c_existing_period_indicator
770 ,p_show_existing_timecard => p_show_existing_timecard
771 ,p_set_more_period => get_more_period_value(l_periods,l_start_date,l_end_date)
772 );
773 END IF;
774
775 l_last_period_end := l_end_date;
776
777 IF g_debug THEN
778 hr_utility.set_location ( g_package||l_proc, 80);
779 END IF;
780
781 -- ignore overlapping periods
782 WHILE l_periods.exists(l_period_index)
783 AND l_periods(l_period_index).start_date <= l_end_date
784 LOOP
785 l_period_index := l_periods.next(l_period_index);
786 END LOOP;
787
788 IF g_debug THEN
789 hr_utility.set_location ( g_package||l_proc, 90);
790 END IF;
791
792 END LOOP;
793
794 CLOSE c_timecards;
795
796
797 --add the rest of the periods
798 WHILE l_periods.exists(l_period_index)
799 LOOP
800 -- 115.34 change. To differentiate an archived time period from a normal period.
801
802 if(check_period_archived(l_periods(l_period_index).end_date)) then
803 generate_periods(
804 p_periods => l_new_periods
805 ,p_start_date => l_periods(l_period_index).start_date
806 ,p_end_date => l_periods(l_period_index).end_date
807 ,p_last_period_end => l_last_period_end
808 ,p_past_date_limit => g_past_date_limit
809 ,p_future_date_limit => g_future_date_limit
810 ,p_exists => hxc_timecard.c_archived_period_indicator
811 ,p_show_existing_timecard => p_show_existing_timecard
812 ,p_set_more_period => l_periods(l_period_index).p_set_more_period
813 );
814 ELSE
815 generate_periods(
816 p_periods => l_new_periods
817 ,p_start_date => l_periods(l_period_index).start_date
818 ,p_end_date => l_periods(l_period_index).end_date
819 ,p_last_period_end => l_last_period_end
820 ,p_past_date_limit => g_past_date_limit
821 ,p_future_date_limit => g_future_date_limit
822 ,p_exists => NULL
823 ,p_set_more_period => l_periods(l_period_index).p_set_more_period
824 );
825 END IF;
826
827
828 l_last_period_end := NULL;
829 l_period_index := l_periods.next(l_period_index);
830
831 END LOOP;
832
833 IF g_debug THEN
834 hr_utility.set_location ( g_package||l_proc, 100);
835 END IF;
836
837 --RETURN l_new_periods;
838
839
840 --below is added for mid period hiring
841 -- v115.31 changed to use indexed looping.
842 -- Fix for bug no. 3401914
843
844
845 l_period_index := l_new_periods.first;
846 while l_period_index is not null
847 loop
848 process_assignments(
849 l_new_periods(l_period_index)
850 ,g_assignment_periods
851 ,l_assignment_processed_periods -- changed here
852 );
853 l_period_index := l_new_periods.NEXT(l_period_index);
854 end loop;
855
856 --For import Timecard Page, only retain the open periods.
857 l_index :=0;
858 IF(p_show_existing_timecard = 'N') THEN
859 l_period_index := l_assignment_processed_periods.first;
860 while l_period_index is not null
861 loop
862 if((l_assignment_processed_periods(l_period_index).exist_flag is null) or
863 ((l_assignment_processed_periods(l_period_index).exist_flag <> hxc_timecard.c_existing_period_indicator) AND
864 (l_assignment_processed_periods(l_period_index).exist_flag <> hxc_timecard.c_archived_period_indicator))
865 ) then
866 l_processed_periods(l_index) := l_assignment_processed_periods(l_period_index);
867 l_index := l_index+1;
868 end if;
869 l_period_index := l_assignment_processed_periods.NEXT(l_period_index);
870 end loop;
871 RETURN l_processed_periods;
872 END IF;
873 RETURN l_assignment_processed_periods;
874
875 END get_periods;
876
877
878 PROCEDURE get_first_empty_period(
879 p_resource_id IN NUMBER
880 ,p_resource_type IN VARCHAR2
881 ,p_current_date IN DATE
882 ,p_show_existing_timecard IN VARCHAR2
883 ,p_periods OUT NOCOPY VARCHAR2
884 )
885 IS
886 l_current_date DATE;
887 l_index NUMBER;
888 l_periods periods;
889 l_previous_period_end DATE;
890 l_previous_period_start DATE;
891 l_default_tc_period VARCHAR2(20);
892 l_pref_table hxc_preference_evaluation.t_pref_table;
893
894 l_empty_period VARCHAR2(50);
895
896 BEGIN
897
898 hxc_preference_evaluation.resource_preferences(
899 p_resource_id => p_resource_id
900 , p_pref_code_list=> 'TC_W_TCRD_PERIOD'
901 , p_pref_table => l_pref_table
902 , p_resp_id => -101
903 );
904 --Get the Default Timecard period option from preference
905 If l_pref_table is not null then
906 l_default_tc_period := l_pref_table(l_pref_table.FIRST).attribute2;
907 end if;
908
909 --User can save the preference with out selecting any value, in this case we
910 --should retain the existing behavior
911
912 If l_default_tc_period is null then
913 l_default_tc_period := 'FUTURE';
914 end if;
915
916 --FUTURE - Period on or after system date - Current Behavior
917 --EARLIEST - Earliest Period prior to system date
918 --CLOSEST - Closest Period prior to system date
919
920 IF l_default_tc_period = 'EARLIEST' OR l_default_tc_period = 'CLOSEST' THEN
921
922 -- look for empty period in the past
923 l_current_date := p_current_date;
924 WHILE TRUE LOOP
925
926 l_periods :=
927 get_periods(
928 p_resource_id => p_resource_id
929 ,p_resource_type => p_resource_type
930 ,p_current_date => l_current_date
931 ,p_show_existing_timecard => p_show_existing_timecard
932 );
933
934 l_index := l_periods.first;
935
936 IF l_periods.count = 0
937 OR (l_previous_period_start IS NOT NULL
938 AND l_previous_period_start = l_periods(l_index).start_date)
939 THEN
940 -- can't find anything in the past, do not RETURN, search in the future
941 -- empty period
942 Exit;
943 ELSE
944 ----look for the empty period in the past
945 find_empty_period(
946 p_future => FALSE
947 ,p_periods => l_periods
948 ,p_empty_period => p_periods
949 ,p_default_tc_period => l_default_tc_period
950 );
951
952 -- In the case of CLOSEST, you should return as and when an empty period is
953 -- found in the past
954 -- But in the case of EARLIEST, we should continue searching till the first
955 -- period
956
957 IF p_periods IS NOT NULL AND l_default_tc_period = 'CLOSEST'
958 THEN
959 -- found an empty period in the past, return result
960 RETURN;
961 ELSIF p_periods IS NOT NULL
962 THEN
963 l_empty_period := p_periods;
964 l_current_date := l_periods(l_index).start_date - 1;
965 l_previous_period_start := l_periods(l_index).start_date;
966 ELSE
967 p_periods := l_empty_period;
968 l_current_date := l_periods(l_index).start_date - 1;
969 l_previous_period_start := l_periods(l_index).start_date;
970
971 END IF;
972
973 END IF;
974 END LOOP;
975
976 -- In the case of EARLIEST, we should return if any empty period found in the past
977 -- Otherwise we should search in the past
978
979 IF l_default_tc_period = 'EARLIEST' AND p_periods IS NOT NULL THEN
980 RETURN;
981 END IF;
982
983 END IF;
984
985 -- Search in the Feature starts!!
986
987 l_default_tc_period := 'FUTURE';
988
989 l_current_date := p_current_date;
990
991 WHILE TRUE LOOP
992
993 l_periods :=
994 get_periods(
995 p_resource_id => p_resource_id
996 ,p_resource_type => p_resource_type
997 ,p_current_date => l_current_date
998 ,p_show_existing_timecard => p_show_existing_timecard
999 );
1000
1001 -- Now we are looking for an empty period. The idea is we look for the
1002 -- earliest empty period in the future, if we can't find one within the
1003 -- future date limit and/or assignment end date, we will look in the
1004 -- past to find the latest empty period. If we can't find one within the
1005 -- past date limit and/or assignment start date, we will return null
1006
1007 IF l_periods.count = 0
1008 OR (l_previous_period_end IS NOT NULL
1009 AND l_previous_period_end = l_periods(l_periods.last).end_date)
1010 THEN
1011 -- can't find anything in the future
1012 EXIT;
1013
1014 ELSE
1015 ----look for the empty period in the future
1016 find_empty_period(
1017 p_future => TRUE
1018 ,p_periods => l_periods
1019 ,p_empty_period => p_periods
1020 ,p_default_tc_period => l_default_tc_period
1021 );
1022
1023 IF p_periods IS NOT NULL
1024 THEN
1025 -- found empty period in the future
1026 RETURN;
1027 ELSE
1028 l_index := l_periods.last;
1029
1030 l_current_date := l_periods(l_index).end_date + 1;
1031 l_previous_period_end := l_periods(l_index).end_date;
1032
1033 END IF;
1034 END IF;
1035 END LOOP;
1036
1037 -- You should search in the past only for scenario FUTURE,
1038 -- for the remaining two cases, past search is already completed
1039
1040 IF l_default_tc_period = 'FUTURE' THEN
1041 -- look for empty period in the past
1042 l_current_date := SYSDATE;
1043 WHILE TRUE LOOP
1044
1045 l_periods :=
1046 get_periods(
1047 p_resource_id => p_resource_id
1048 ,p_resource_type => p_resource_type
1049 ,p_current_date => l_current_date
1050 ,p_show_existing_timecard => p_show_existing_timecard
1051 );
1052
1053 l_index := l_periods.first;
1054
1055
1056 IF l_periods.count = 0
1057 OR (l_previous_period_start IS NOT NULL
1058 AND l_previous_period_start = l_periods(l_index).start_date)
1059 THEN
1060 -- can't find anything in the past
1061 RETURN;
1062 ELSE
1063 ----look for the empty period in the past
1064 find_empty_period(
1065 p_future => FALSE
1066 ,p_periods => l_periods
1067 ,p_empty_period => p_periods
1068 ,p_default_tc_period => l_default_tc_period
1069 );
1070
1071 IF p_periods IS NOT NULL
1072 THEN
1073 -- found an empty period in the past, return result
1074
1075 RETURN;
1076 ELSE
1077 l_current_date := l_periods(l_index).start_date - 1;
1078 l_previous_period_start := l_periods(l_index).start_date;
1079 END IF;
1080
1081 END IF;
1082 END LOOP;
1083 END IF;
1084
1085 END get_first_empty_period;
1086
1087 PROCEDURE periods_to_string(
1088 p_first_periods IN periods
1089 ,p_second_periods IN periods
1090 ,p_periods OUT NOCOPY VARCHAR2
1091 )
1092 IS
1093 l_index NUMBER;
1094 BEGIN
1095
1096 l_index := p_first_periods.first;
1097
1098 LOOP
1099 EXIT WHEN NOT p_first_periods.exists(l_index);
1100
1101 p_periods := NVL(p_periods, '')
1102 || g_separator
1103 || NVL(p_first_periods(l_index).exist_flag, '')
1104 || TO_CHAR(p_first_periods(l_index).start_date, g_date_format)
1105 || g_separator
1106 || TO_CHAR(p_first_periods(l_index).end_date, g_date_format)
1107 || NVL(p_first_periods(l_index).p_set_more_period,'');
1108
1109 l_index := p_first_periods.next(l_index);
1110
1111 END LOOP;
1112
1113 IF p_second_periods.count = 0
1114 OR p_second_periods(p_second_periods.last).start_date
1115 = p_first_periods(p_first_periods.last).start_date
1116 THEN
1117 RETURN;
1118 END IF;
1119
1120 l_index := p_second_periods.first;
1121 LOOP
1122 EXIT WHEN NOT p_second_periods.exists(l_index);
1123
1124 IF p_second_periods(l_index).start_date
1125 > p_first_periods(p_first_periods.last).start_date
1126 THEN
1127 p_periods := NVL(p_periods, '')
1128 || g_separator
1129 || NVL(p_second_periods(l_index).exist_flag, '')
1130 || TO_CHAR(p_second_periods(l_index).start_date, g_date_format)
1131 || g_separator
1132 || TO_CHAR(p_second_periods(l_index).end_date, g_date_format)
1133 || NVL(p_second_periods(l_index).p_set_more_period,'');
1134
1135 END IF;
1136
1137 l_index := p_second_periods.next(l_index);
1138 END LOOP;
1139
1140 END periods_to_string;
1141
1142 PROCEDURE get_period_list(
1143 p_resource_id IN NUMBER
1144 ,p_resource_type IN VARCHAR2
1145 ,p_current_date IN DATE
1146 ,p_show_existing_timecard IN VARCHAR2
1147 ,p_periods OUT NOCOPY VARCHAR2
1148 )
1149 IS
1150 l_index NUMBER;
1151 l_periods periods;
1152 l_temp_periods periods;
1153 l_current_date DATE;
1154 l_proc VARCHAR2(500);
1155 BEGIN
1156 g_debug := hr_utility.debug_enabled;
1157
1158 IF g_debug THEN
1159 l_proc := 'get_period_list';
1160 hr_utility.set_location(g_package||l_proc, 10);
1161 END IF;
1162
1163 l_current_date := p_current_date;
1164
1165 WHILE TRUE LOOP
1166 l_temp_periods :=
1167 get_periods(
1168 p_resource_id => p_resource_id
1169 ,p_resource_type => p_resource_type
1170 ,p_current_date => l_current_date
1171 ,p_show_existing_timecard => p_show_existing_timecard
1172 );
1173
1174 IF g_debug THEN
1175 hr_utility.set_location(g_package||l_proc, 20);
1176 END IF;
1177
1178 IF l_temp_periods.count = 0
1179 OR l_periods.count > 0
1180 THEN
1181 IF g_debug THEN
1182 hr_utility.set_location(g_package||l_proc, 30);
1183 END IF;
1184
1185 --if this list is empty, or this is the second list,
1186 --combine this list with the first list and return
1187 periods_to_string(
1188 p_first_periods => l_periods
1189 ,p_second_periods => l_temp_periods
1190 ,p_periods => p_periods
1191 );
1192 IF g_debug THEN
1193 hr_utility.set_location(g_package||l_proc, 40);
1194 END IF;
1195 RETURN;
1196 ELSE
1197 --this is the first list and it is not empty
1198 --if the last periods doesn't go beyong current date period, try
1199 --go forward one more day after current period. This is to avoid
1200 --the senario:
1201
1202 IF g_debug THEN
1203 hr_utility.set_location(g_package||l_proc, 50);
1204 END IF;
1205
1206 IF l_temp_periods(l_temp_periods.last).start_date = p_current_date
1207 THEN
1208 IF g_debug THEN
1209 hr_utility.set_location(g_package||l_proc, 60);
1210 END IF;
1211
1212 l_current_date := l_temp_periods(l_temp_periods.last).end_date + 1;
1213 l_periods := l_temp_periods;
1214 ELSE
1215 --this list is ready to return
1216 periods_to_string(
1217 p_first_periods => l_temp_periods
1218 ,p_second_periods => l_periods
1219 ,p_periods => p_periods
1220 );
1221
1222 IF g_debug THEN
1223 hr_utility.trace('start=' || to_char(l_temp_periods(l_temp_periods.last).start_date, 'YYYY/MM/DD'));
1224
1225 hr_utility.set_location(g_package||l_proc, 70);
1226 END IF;
1227
1228 RETURN;
1229 END IF;
1230 END IF;
1231 END LOOP;
1232
1233
1234 END get_period_list;
1235
1236
1237 FUNCTION get_assignment_periods(
1238 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
1239 )
1240 RETURN periods
1241 IS
1242 l_assignment_index NUMBER;
1243 l_start_date DATE;
1244 l_end_date DATE;
1245 l_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
1246 l_current_assignment PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
1247 l_assignment_periods periods;
1248 l_assign_period_limit NUMBER; -- 5922228
1249
1250
1251 CURSOR c_assignments(
1252 p_resource_id IN NUMBER,
1253 p_assign_period_limit IN NUMBER -- 5922228
1254 )
1255 IS
1256 SELECT pas.ASSIGNMENT_ID,
1257 pas.EFFECTIVE_START_DATE,
1258 NVL(pas.EFFECTIVE_END_DATE, hr_general.end_of_time)
1259 FROM PER_ALL_ASSIGNMENTS_F pas,
1260 per_assignment_status_types typ
1261 WHERE pas.PERSON_ID = p_resource_id
1262 AND pas.ASSIGNMENT_TYPE in ('E','C')
1263 AND pas.PRIMARY_FLAG = 'Y'
1264 AND pas.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
1265 -- AND typ.PER_SYSTEM_STATUS IN ( 'ACTIVE_ASSIGN','ACTIVE_CWK') -- 5922228
1266 AND DECODE(typ.PER_SYSTEM_STATUS,'ACTIVE_ASSIGN',1,
1267 'ACTIVE_CWK', 1,
1268 0 ) >= p_assign_period_limit
1269 -- AND pas.EFFECTIVE_START_DATE <= SYSDATE
1270 ORDER BY EFFECTIVE_START_DATE;
1271
1272
1273 BEGIN
1274
1275 -- get the all the primary assignment periods. We don't allow users
1276 -- to enter timecard for future assignment periods, thus we don't
1277 -- query up future assignment periods.
1278 l_assignment_index := 0;
1279 l_current_assignment := -1;
1280
1281
1282 -- 5922228 ( Fetching the preference for the given resource id for
1283 -- future time card periods )
1284
1285 IF hxc_preference_evaluation.resource_preferences( p_resource_id,
1286 'TC_W_TCRD_ST_ALW_EDITS',
1287 10,
1288 sysdate
1289 ,101
1290 ) = 'FIN_ASSGN'
1291 THEN
1292 l_assign_period_limit := 0;
1293 ELSE
1294 l_assign_period_limit := 1;
1295 END IF;
1296
1297
1298
1299 OPEN c_assignments(
1300 p_resource_id => p_resource_id,
1301 p_assign_period_limit => l_assign_period_limit -- 5922228
1302 );
1303
1304 LOOP
1305 FETCH c_assignments INTO l_assignment_id, l_start_date, l_end_date;
1306 EXIT WHEN c_assignments%NOTFOUND;
1307 /* jxtan fixed Aug23
1308 IF l_current_assignment <> l_assignment_id
1309 THEN
1310 IF l_start_date <= SYSDATE
1311 THEN
1312 l_assignment_index := l_assignment_index + 1;
1313 g_assignment_periods(l_assignment_index).start_date := l_start_date;
1314 g_assignment_periods(l_assignment_index).end_date := l_end_date;
1315 l_current_assignment := l_assignment_id;
1316
1317 ELSE
1318 EXIT;
1319 END IF;
1320 ELSE
1321 g_assignment_periods(l_assignment_index).end_date := l_end_date;
1322 END IF;
1323 */
1324
1325 --possible fix for LGE
1326 IF l_current_assignment = l_assignment_id
1327 AND TRUNC(l_assignment_periods(l_assignment_index).end_date) + 1 =
1328 TRUNC(l_start_date)
1329 THEN
1330 l_assignment_periods(l_assignment_index).end_date := l_end_date;
1331 ELSE
1332 IF l_current_assignment <> l_assignment_id
1333 AND l_start_date > SYSDATE
1334 THEN
1335 -- we don't allow user to enter time for future active assignment
1336 -- unless it is an assignment change
1337 EXIT;
1338 ELSE
1339 l_assignment_index := l_assignment_index + 1;
1340 l_assignment_periods(l_assignment_index).start_date := l_start_date;
1341 l_assignment_periods(l_assignment_index).end_date := l_end_date;
1342 l_current_assignment := l_assignment_id;
1343 END IF;
1344 END IF;
1345
1346
1347 END LOOP;
1348
1349 RETURN l_assignment_periods;
1350
1351 END get_assignment_periods;
1352
1353
1354
1355 PROCEDURE init_globals(
1356 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
1357 )
1358 IS
1359 l_assignment_index NUMBER;
1360 l_start_date DATE;
1361 l_end_date DATE;
1362 l_num_past_days NUMBER;
1363 l_num_future_days NUMBER;
1364 l_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
1365 l_current_assignment PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
1366 l_rec_period_id VARCHAR2(50);
1367
1368 l_index BINARY_INTEGER;
1369 l_pref_table hxc_preference_evaluation.t_pref_table;
1370
1371 /*
1372 CURSOR c_assignments(
1373 p_resource_id IN NUMBER
1374 )
1375 IS
1376 SELECT pas.ASSIGNMENT_ID,
1377 pas.EFFECTIVE_START_DATE,
1378 NVL(pas.EFFECTIVE_END_DATE, hr_general.end_of_time)
1379 FROM PER_ALL_ASSIGNMENTS_F pas,
1380 per_assignment_status_types typ
1381 WHERE pas.PERSON_ID = p_resource_id
1382 AND pas.ASSIGNMENT_TYPE = 'E'
1383 AND pas.PRIMARY_FLAG = 'Y'
1384 AND pas.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
1385 AND typ.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
1386 -- AND pas.EFFECTIVE_START_DATE <= SYSDATE
1387 ORDER BY EFFECTIVE_START_DATE;
1388 */
1389 /*
1390 CURSOR c_period_info(
1391 p_resource_id IN NUMBER
1392 )
1393 IS
1394 SELECT rp.period_type,
1395 rp.duration_in_days,
1396 rp.start_date
1397 FROM hxc_recurring_periods rp,
1398 per_time_period_types p
1399 WHERE p.period_type (+) = rp.period_type
1400 AND hxc_preference_evaluation.resource_preferences(
1401 p_resource_id,'TC_W_TCRD_PERIOD|1|') = rp.recurring_period_id;
1402 */
1403
1404 CURSOR c_period_info(p_recurring_period_id number)
1405 is
1406 select hrp.period_type,
1407 hrp.duration_in_days,
1408 hrp.start_date
1409 from hxc_recurring_periods hrp
1410 where hrp.recurring_period_id = p_recurring_period_id;
1411
1412 BEGIN
1413
1414 g_debug := hr_utility.debug_enabled;
1415
1416 g_assignment_periods.delete;
1417
1418 g_assignment_periods := get_assignment_periods(p_resource_id);
1419 -- Added check to see if there is atleast one Active Assignment
1420 -- ver 115.32
1421 if(g_assignment_periods.COUNT<1)
1422 then
1423 g_initialized := 'RETURN';
1424 RETURN;
1425 end if;
1426
1427 -- call the preference
1428
1429 hxc_preference_evaluation.resource_preferences(
1430 p_resource_id => p_resource_id
1431 , p_pref_code_list=> 'TC_W_TCRD_PERIOD,TC_W_TCRD_ST_ALW_EDITS'
1432 , p_pref_table => l_pref_table
1433 , p_resp_id => -101
1434 );
1435
1436 l_index := l_pref_table.FIRST;
1437
1438 WHILE ( l_index IS NOT NULL )
1439 LOOP
1440
1441 IF ( l_pref_table(l_index).preference_code = 'TC_W_TCRD_PERIOD' )
1442 THEN
1443 l_rec_period_id := l_pref_table(l_index).attribute1;
1444
1445 ELSIF ( l_pref_table(l_index).preference_code = 'TC_W_TCRD_ST_ALW_EDITS' )
1446 THEN
1447 l_num_future_days := l_pref_table(l_index).attribute11;
1448 l_num_past_days := l_pref_table(l_index).attribute6;
1449
1450 END IF;
1451 l_index := l_pref_table.NEXT(l_index);
1452
1453 END LOOP;
1454
1455
1456
1457 -- l_rec_period_id :=
1458 -- hxc_preference_evaluation.resource_preferences(
1459 -- p_resource_id,
1460 -- 'TC_W_TCRD_PERIOD|1|'
1461 -- );
1462
1463 --get the person's time period information
1464 OPEN c_period_info(
1465 p_recurring_period_id => TO_NUMBER(l_rec_period_id)
1466 );
1467
1468 FETCH c_period_info INTO g_period_type, g_duration_in_days, g_rec_period_start_date;
1469
1470 IF c_period_info%NOTFOUND
1471 THEN
1472 g_initialized := 'RETURN';
1473 RETURN;
1474 END IF;
1475
1476 CLOSE c_period_info;
1477
1478
1479 IF g_debug THEN
1480 hr_utility.trace('l_period_type=' || g_period_type);
1481 hr_utility.trace('l_duration_in_days=' || g_duration_in_days);
1482 hr_utility.trace('l_rec_period_start_date=' || g_rec_period_start_date);
1483 END IF;
1484
1485 -- l_num_past_days :=
1486 -- hxc_preference_evaluation.resource_preferences(
1487 -- p_resource_id,
1488 -- 'TC_W_TCRD_ST_ALW_EDITS',
1489 -- 6);
1490
1491 -- l_num_future_days :=
1492 -- hxc_preference_evaluation.resource_preferences(
1493 -- p_resource_id,
1494 -- 'TC_W_TCRD_ST_ALW_EDITS',
1495 -- 11);
1496
1497 IF g_debug THEN
1498 hr_utility.trace('l_num_past_days=' || l_num_past_days);
1499 hr_utility.trace('l_num_future_days=' || l_num_future_days);
1500 END IF;
1501
1502 IF l_num_past_days IS NOT NULL
1503 THEN
1504 g_past_date_limit := SYSDATE - TO_NUMBER(l_num_past_days);
1505 ELSE
1506 g_past_date_limit := hr_general.START_OF_TIME;
1507 END IF;
1508
1509 IF l_num_future_days IS NOT NULL
1510 THEN
1511 g_future_date_limit := SYSDATE + TO_NUMBER(l_num_future_days);
1512 ELSE
1513 g_future_date_limit := hr_general.END_OF_TIME;
1514 END IF;
1515 g_initialized := 'Y';
1516
1517 IF g_debug THEN
1518 hr_utility.trace(' l_past_date_limit =' || to_char(g_past_date_limit, 'YYYY/MM/DD'));
1519 hr_utility.trace(' l_future_date_limit=' ||to_char(g_future_date_limit, 'YYYY/MM/DD') );
1520 END IF;
1521
1522 END init_globals;
1523
1524
1525 /*=========================================================================
1526 * this new procedure evaluates period related preferences on the server
1527 * side. It should be the one to be called by the middle tier from now on.
1528 * However we keep the old one to be compatible with existing middle tier
1529 * code.
1530 *========================================================================*/
1531
1532 PROCEDURE get_time_periods(
1533 p_resource_id IN VARCHAR2
1534 ,p_resource_type IN VARCHAR2
1535 ,p_current_date IN VARCHAR2
1536 ,p_show_existing_timecard IN VARCHAR2
1537 ,p_first_empty_period IN VARCHAR2
1538 ,p_periods OUT NOCOPY VARCHAR2
1539 )
1540 IS
1541 l_assignment_index NUMBER;
1542 l_start_date DATE;
1543 l_end_date DATE;
1544 l_resource_id NUMBER := TO_NUMBER(p_resource_id);
1545
1546 BEGIN
1547 g_debug := hr_utility.debug_enabled;
1548
1549 /*
1550 IF l_resource_id = 10251
1551 THEN
1552 --hr_utility.trace_on(NULL, 'test');
1553 END IF;
1554 */
1555 -- mstewart 5/20/2002
1556 -- temporary fix to resolve pl/sql caching issues. For now force
1557 -- initialization of the globals every procedure call - need to
1558 -- identify the entry points to properly fix this problem.
1559 g_initialized := 'N';
1560
1561 IF g_initialized = 'N'
1562 THEN
1563 init_globals(
1564 p_resource_id => l_resource_id
1565 );
1566 END IF;
1567
1568 IF g_initialized = 'RETURN'
1569 THEN
1570 RETURN;
1571 END IF;
1572
1573 IF p_first_empty_period = 'Y'
1574 THEN
1575
1576 IF g_debug THEN
1577 hr_utility.trace('start empty');
1578 END IF;
1579
1580 get_first_empty_period(
1581 p_resource_id => l_resource_id
1582 ,p_resource_type => p_resource_type
1583 ,p_current_date => SYSDATE
1584 ,p_show_existing_timecard => 'Y'
1585 ,p_periods => p_periods
1586 );
1587 IF g_debug THEN
1588 hr_utility.trace('returned empty period=' || p_periods);
1589 END IF;
1590 ELSE
1591 IF g_debug THEN
1592 hr_utility.trace('start getting list');
1593 END IF;
1594
1595 get_period_list(
1596 p_resource_id => l_resource_id
1597 ,p_resource_type => p_resource_type
1598 ,p_current_date => TO_DATE(p_current_date, g_date_format)
1599 ,p_show_existing_timecard => p_show_existing_timecard
1600 ,p_periods => p_periods
1601 );
1602
1603 IF g_debug THEN
1604 hr_utility.trace('finished getting list');
1605 END IF;
1606 END IF;
1607
1608
1609
1610 END get_time_periods;
1611
1612
1613
1614 PROCEDURE get_time_periods(
1615 p_resource_id IN VARCHAR2
1616 ,p_resource_type IN VARCHAR2
1617 ,p_rec_period_start_date IN VARCHAR2
1618 ,p_period_type IN VARCHAR2
1619 ,p_duration_in_days IN VARCHAR2
1620 ,p_current_date IN VARCHAR2
1621 ,p_num_past_entries IN VARCHAR2
1622 ,p_num_future_entries IN VARCHAR2
1623 ,p_num_past_days IN VARCHAR2
1624 ,p_num_future_days IN VARCHAR2
1625 ,p_hire_date IN VARCHAR2
1626 ,p_show_existing_timecard IN VARCHAR2
1627 ,p_first_empty_period IN VARCHAR2
1628 ,p_periods OUT NOCOPY VARCHAR2
1629 )
1630 IS
1631 BEGIN
1632 get_time_periods(
1633 p_resource_id => p_resource_id
1634 ,p_resource_type => p_resource_type
1635 ,p_current_date => p_current_date
1636 ,p_show_existing_timecard => p_show_existing_timecard
1637 ,p_first_empty_period => p_first_empty_period
1638 ,p_periods => p_periods
1639 );
1640
1641
1642 END get_time_periods;
1643
1644 PROCEDURE get_current_period(
1645 p_rec_period_start_date IN VARCHAR2
1646 ,p_period_type IN VARCHAR2
1647 ,p_duration_in_days IN VARCHAR2
1648 ,p_current_date IN VARCHAR2
1649 ,p_period OUT NOCOPY VARCHAR2
1650 )
1651 IS
1652 l_start_date DATE;
1653 l_end_date DATE;
1654 l_proc VARCHAR2(50);
1655 BEGIN
1656
1657 g_debug := hr_utility.debug_enabled;
1658
1659 IF g_debug THEN
1660 l_proc := 'get_current_period';
1661 hr_utility.set_location(g_package||l_proc, 10);
1662 END IF;
1663
1664 find_current_period(
1665 p_rec_period_start_date => fnd_date.canonical_to_date
1666 (p_rec_period_start_date)
1667 ,p_period_type => p_period_type
1668 ,p_duration_in_days => p_duration_in_days
1669 ,p_current_date => TO_DATE(p_current_date, g_date_format)
1670 ,p_period_start => l_start_date
1671 ,p_period_end => l_end_date
1672 );
1673
1674 IF g_debug THEN
1675 hr_utility.set_location(g_package||l_proc, 20);
1676 END IF;
1677
1678 p_period := TO_CHAR(l_start_date, g_date_format)
1679 || g_separator
1680 || TO_CHAR(l_end_date, g_date_format);
1681
1682 IF g_debug THEN
1683 hr_utility.set_location(g_package||l_proc, 30);
1684 END IF;
1685
1686 END get_current_period;
1687
1688
1689 FUNCTION get_pto_balance
1690 (p_resource_id IN VARCHAR2
1691 ,p_assignment_id IN VARCHAR2
1692 ,p_start_time IN VARCHAR2
1693 ,p_plan_code IN VARCHAR2
1694 )
1695 RETURN VARCHAR2
1696 IS
1697 --
1698 CURSOR csr_get_plan
1699 (p_assignment_id NUMBER
1700 ,p_effective_date DATE
1701 ,p_plan_name VARCHAR2
1702 )
1703 IS
1704 SELECT pap.accrual_plan_id
1705 FROM pay_accrual_plans pap
1706 ,pay_element_types_f pet
1707 ,pay_element_links_f pel
1708 ,pay_element_entries_f pee
1709 WHERE pap.accrual_plan_element_type_id = pet.element_type_id
1710 AND pet.element_type_id = pel.element_type_id
1711 AND pee.effective_start_date BETWEEN pet.effective_start_date
1712 AND pet.effective_end_date
1713 AND pel.element_link_id = pee.element_link_id
1714 AND pee.effective_start_date BETWEEN pel.effective_start_date
1715 AND pel.effective_end_date
1716 AND pee.assignment_id = p_assignment_id
1717 AND p_effective_date BETWEEN pee.effective_start_date
1718 AND pee.effective_end_date
1719 AND pap.accrual_plan_name = p_plan_name;
1720 /*
1721 AND to_date(p_effective_date, 'YYYY/MM/DD HH24:MI:SS') BETWEEN pee.effective_start_date
1722 AND pee.effective_end_date;
1723 */
1724 l_pto_balance NUMBER;
1725 l_plan_id NUMBER;
1726 l_start_time DATE;
1727 l_plan_name VARCHAR2(80);
1728 --
1729 BEGIN
1730 --
1731 l_start_time := FND_DATE.CANONICAL_TO_DATE(p_start_time);
1732 --
1733 IF (p_plan_code = 'MONTHLY') THEN
1734 l_plan_name := 'LGE_TL_MonthlyLeave_AP';
1735 ELSE
1736 l_plan_name := 'LGE_TL_AnnualLeave_AP';
1737 END IF;
1738 --
1739 OPEN csr_get_plan(p_assignment_id, l_start_time, l_plan_name);
1740 --
1741 FETCH csr_get_plan INTO l_plan_id;
1742 --
1743 IF csr_get_plan%NOTFOUND THEN
1744 --
1745 CLOSE csr_get_plan;
1746 --
1747 l_plan_id := null;
1748 --
1749 RETURN '0|PTO';
1750 --
1751 ELSE
1752 --
1753 CLOSE csr_get_plan;
1754 --
1755 -- now call pay_us_pto_accrual.get_net_accrual to get the balance
1756 --
1757 l_pto_balance :=
1758 pay_us_pto_accrual.get_net_accrual
1759 (p_assignment_id
1760 ,l_start_time
1761 ,l_plan_id
1762 );
1763 --
1764 RETURN TO_CHAR(round(l_pto_balance, 2)) || '|PTO';
1765 --
1766 END IF;
1767 --
1768 END get_pto_balance;
1769
1770
1771 -- ----------------------------------------------------------------------------
1772 -- |--------------------< cla_summary_alias_translation>----------------------|
1773 -- this procedure is called in the cla project/payroll layout to alias translate
1774 -- for a particular timecard_id the entire history of the timecard
1775 -- ----------------------------------------------------------------------------
1776 PROCEDURE cla_summary_alias_translation(
1777 p_timecard_id IN NUMBER
1778 ,p_resource_id IN NUMBER
1779 ,p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
1780 ,p_blocks IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
1781 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
1782 )IS
1783
1784
1785 -- first we need to query the timecard info
1786 cursor crs_timecard is
1787 select
1788 TIME_BUILDING_BLOCK_ID
1789 ,TYPE
1790 ,MEASURE
1791 ,UNIT_OF_MEASURE
1792 ,START_TIME
1793 ,STOP_TIME
1794 ,PARENT_BUILDING_BLOCK_ID
1795 ,'N' PARENT_IS_NEW
1796 ,SCOPE
1797 ,OBJECT_VERSION_NUMBER
1798 ,APPROVAL_STATUS
1799 ,RESOURCE_ID
1800 ,RESOURCE_TYPE
1801 ,APPROVAL_STYLE_ID
1802 ,DATE_FROM
1803 ,DATE_TO
1804 ,COMMENT_TEXT
1805 ,PARENT_BUILDING_BLOCK_OVN
1806 ,'N' NEW
1807 ,'N' CHANGED
1808 ,'N' PROCESS
1809 ,APPLICATION_SET_ID
1810 ,TRANSLATION_DISPLAY_KEY
1811 FROM hxc_time_building_blocks
1812 where time_building_block_id = p_timecard_id
1813 and resource_id = p_resource_id
1814 and scope = 'TIMECARD';
1815 --and date_to = hr_general.end_of_time;
1816
1817
1818 CURSOR crs_day_info (
1819 p_resource_id IN NUMBER,
1820 p_parent_building_block_id IN NUMBER,
1821 p_parent_ovn IN NUMBER
1822 )
1823 IS
1824 SELECT
1825 TIME_BUILDING_BLOCK_ID
1826 ,TYPE
1827 ,MEASURE
1828 ,UNIT_OF_MEASURE
1829 ,START_TIME
1830 ,STOP_TIME
1831 ,PARENT_BUILDING_BLOCK_ID
1832 ,'N' PARENT_IS_NEW
1833 ,SCOPE
1834 ,OBJECT_VERSION_NUMBER
1835 ,APPROVAL_STATUS
1836 ,RESOURCE_ID
1837 ,RESOURCE_TYPE
1838 ,APPROVAL_STYLE_ID
1839 ,DATE_FROM
1840 ,DATE_TO
1841 ,COMMENT_TEXT
1842 ,PARENT_BUILDING_BLOCK_OVN
1843 ,'N' NEW
1844 ,'N' CHANGED
1845 ,'N' PROCESS
1846 ,APPLICATION_SET_ID
1847 ,TRANSLATION_DISPLAY_KEY
1848 FROM hxc_time_building_blocks
1849 WHERE resource_id = p_resource_id
1850 AND parent_building_block_id = p_parent_building_block_id
1851 AND parent_building_block_ovn = p_parent_ovn
1852 AND SCOPE = 'DAY';
1853
1854 CURSOR crs_detail_info (
1855 p_resource_id IN NUMBER,
1856 p_parent_building_block_id IN NUMBER,
1857 p_parent_ovn IN NUMBER
1858 )
1859 IS
1860 SELECT
1861 TIME_BUILDING_BLOCK_ID
1862 ,TYPE
1863 ,MEASURE
1864 ,UNIT_OF_MEASURE
1865 ,START_TIME
1866 ,STOP_TIME
1867 ,PARENT_BUILDING_BLOCK_ID
1868 ,'N' PARENT_IS_NEW
1869 ,SCOPE
1870 ,OBJECT_VERSION_NUMBER
1871 ,APPROVAL_STATUS
1872 ,RESOURCE_ID
1873 ,RESOURCE_TYPE
1874 ,APPROVAL_STYLE_ID
1875 ,DATE_FROM
1876 ,DATE_TO
1877 ,COMMENT_TEXT
1878 ,PARENT_BUILDING_BLOCK_OVN
1879 ,'N' NEW
1880 ,'N' CHANGED
1881 ,'N' PROCESS
1882 ,APPLICATION_SET_ID
1883 ,TRANSLATION_DISPLAY_KEY
1884 FROM hxc_time_building_blocks
1885 WHERE resource_id = p_resource_id
1886 AND parent_building_block_id = p_parent_building_block_id
1887 AND parent_building_block_ovn = p_parent_ovn
1888 AND SCOPE = 'DETAIL'
1889 order by OBJECT_VERSION_NUMBER;
1890 /*
1891 cursor crs_detail_attribute
1892 (timecard_id in number,timecard_ovn in number,l_resource_id in number) is
1893 select
1894 a.time_attribute_id
1895 ,au.time_building_block_id
1896 ,bbit.bld_blk_info_type
1897 ,a.attribute_category
1898 ,a.attribute1
1899 ,a.attribute2
1900 ,a.attribute3
1901 ,a.attribute4
1902 ,a.attribute5
1903 ,a.attribute6
1904 ,a.attribute7
1905 ,a.attribute8
1906 ,a.attribute9
1907 ,a.attribute10
1908 ,a.attribute11
1909 ,a.attribute12
1910 ,a.attribute13
1911 ,a.attribute14
1912 ,a.attribute15
1913 ,a.attribute16
1914 ,a.attribute17
1915 ,a.attribute18
1916 ,a.attribute19
1917 ,a.attribute20
1918 ,a.attribute21
1919 ,a.attribute22
1920 ,a.attribute23
1921 ,a.attribute24
1922 ,a.attribute25
1923 ,a.attribute26
1924 ,a.attribute27
1925 ,a.attribute28
1926 ,a.attribute29
1927 ,a.attribute30
1928 ,a.bld_blk_info_type_id
1929 ,a.object_version_number
1930 ,'N' NEW
1931 ,'N' CHANGED
1932 ,'N' PROCESS
1933 ,au.time_building_block_ovn BUILDING_BLOCK_OVN
1934 from hxc_bld_blk_info_types bbit,
1935 hxc_time_attribute_usages au,
1936 hxc_time_attributes a
1937 where a.time_attribute_id = au.time_attribute_id
1938 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
1939 and (au.time_building_block_id,au.time_building_block_ovn) in
1940 (select detail.time_building_block_id,detail.object_version_number
1941 from hxc_time_building_blocks detail,
1942 hxc_time_building_blocks day
1943 where day.time_building_block_id = detail.parent_building_block_id
1944 and day.object_version_number = detail.parent_building_block_ovn
1945 and day.scope = 'DAY'
1946 and detail.resource_id = l_resource_id
1947 and detail.scope = 'DETAIL'
1948 --and day.date_to = hr_general.end_of_time
1949 --and detail.date_to = hr_general.end_of_time
1950 and day.parent_building_block_id = timecard_id
1951 and day.parent_building_block_ovn = timecard_ovn
1952 and day.resource_id = l_resource_id)
1953 UNION
1954 select
1955 a.time_attribute_id
1956 ,au.time_building_block_id
1957 ,bbit.bld_blk_info_type
1958 ,a.attribute_category
1959 ,a.attribute1
1960 ,a.attribute2
1961 ,a.attribute3
1962 ,a.attribute4
1963 ,a.attribute5
1964 ,a.attribute6
1965 ,a.attribute7
1966 ,a.attribute8
1967 ,a.attribute9
1968 ,a.attribute10
1969 ,a.attribute11
1970 ,a.attribute12
1971 ,a.attribute13
1972 ,a.attribute14
1973 ,a.attribute15
1974 ,a.attribute16
1975 ,a.attribute17
1976 ,a.attribute18
1977 ,a.attribute19
1978 ,a.attribute20
1979 ,a.attribute21
1980 ,a.attribute22
1981 ,a.attribute23
1982 ,a.attribute24
1983 ,a.attribute25
1984 ,a.attribute26
1985 ,a.attribute27
1986 ,a.attribute28
1987 ,a.attribute29
1988 ,a.attribute30
1989 ,a.bld_blk_info_type_id
1990 ,a.object_version_number
1991 ,'N' NEW
1992 ,'N' CHANGED
1993 ,'N' PROCESS
1994 ,au.time_building_block_ovn BUILDING_BLOCK_OVN
1995 from hxc_bld_blk_info_types bbit,
1996 hxc_time_attribute_usages au,
1997 hxc_time_attributes a
1998 where a.time_attribute_id = au.time_attribute_id
1999 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
2000 and (au.time_building_block_id,au.time_building_block_ovn) in
2001 (select day.time_building_block_id,day.object_version_number
2002 from hxc_time_building_blocks day
2003 where -- day.date_to = hr_general.end_of_time
2004 day.scope = 'DAY'
2005 and day.parent_building_block_id = timecard_id
2006 and day.parent_building_block_ovn = timecard_ovn
2007 and day.resource_id = l_resource_id)
2008 UNION
2009 select
2010 a.time_attribute_id
2011 ,au.time_building_block_id
2012 ,bbit.bld_blk_info_type
2013 ,a.attribute_category
2014 ,a.attribute1
2015 ,a.attribute2
2016 ,a.attribute3
2017 ,a.attribute4
2018 ,a.attribute5
2019 ,a.attribute6
2020 ,a.attribute7
2021 ,a.attribute8
2022 ,a.attribute9
2023 ,a.attribute10
2024 ,a.attribute11
2025 ,a.attribute12
2026 ,a.attribute13
2027 ,a.attribute14
2028 ,a.attribute15
2029 ,a.attribute16
2030 ,a.attribute17
2031 ,a.attribute18
2032 ,a.attribute19
2033 ,a.attribute20
2034 ,a.attribute21
2035 ,a.attribute22
2036 ,a.attribute23
2037 ,a.attribute24
2038 ,a.attribute25
2039 ,a.attribute26
2040 ,a.attribute27
2041 ,a.attribute28
2042 ,a.attribute29
2043 ,a.attribute30
2044 ,a.bld_blk_info_type_id
2045 ,a.object_version_number
2046 ,'N' NEW
2047 ,'N' CHANGED
2048 ,'N' PROCESS
2049 ,au.time_building_block_ovn BUILDING_BLOCK_OVN
2050 from hxc_bld_blk_info_types bbit,
2051 hxc_time_attribute_usages au,
2052 hxc_time_attributes a
2053 where a.time_attribute_id = au.time_attribute_id
2054 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
2055 and (au.time_building_block_id,au.time_building_block_ovn) in
2056 (select time_building_block_id,object_version_number
2057 from hxc_time_building_blocks htbb
2058 where --htbb.date_to = hr_general.end_of_time
2059 htbb.scope = 'TIMECARD'
2060 and htbb.time_building_block_id = timecard_id
2061 and htbb.object_version_number = timecard_ovn
2062 and htbb.resource_id = l_resource_id)
2063 order by time_building_block_id;
2064 */
2065
2066 cursor crs_detail_attribute
2067 (detail_id in number,detail_ovn in number,l_resource_id in number) is
2068 select
2069 a.time_attribute_id
2070 ,au.time_building_block_id
2071 ,bbit.bld_blk_info_type
2072 ,a.attribute_category
2073 ,a.attribute1
2074 ,a.attribute2
2075 ,a.attribute3
2076 ,a.attribute4
2077 ,a.attribute5
2078 ,a.attribute6
2079 ,a.attribute7
2080 ,a.attribute8
2081 ,a.attribute9
2082 ,a.attribute10
2083 ,a.attribute11
2084 ,a.attribute12
2085 ,a.attribute13
2086 ,a.attribute14
2087 ,a.attribute15
2088 ,a.attribute16
2089 ,a.attribute17
2090 ,a.attribute18
2091 ,a.attribute19
2092 ,a.attribute20
2093 ,a.attribute21
2094 ,a.attribute22
2095 ,a.attribute23
2096 ,a.attribute24
2097 ,a.attribute25
2098 ,a.attribute26
2099 ,a.attribute27
2100 ,a.attribute28
2101 ,a.attribute29
2102 ,a.attribute30
2103 ,a.bld_blk_info_type_id
2104 ,a.object_version_number
2105 ,'N' NEW
2106 ,'N' CHANGED
2107 ,'N' PROCESS
2108 ,au.time_building_block_ovn BUILDING_BLOCK_OVN
2109 from hxc_bld_blk_info_types bbit,
2110 hxc_time_attribute_usages au,
2111 hxc_time_attributes a,
2112 hxc_time_building_blocks htbb
2113 where a.time_attribute_id = au.time_attribute_id
2114 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
2115 and au.time_building_block_id = htbb.time_building_block_id
2116 and au.time_building_block_ovn = htbb.object_version_number
2117 and htbb.scope = 'DETAIL'
2118 and htbb.time_building_block_id = detail_id
2119 and htbb.object_version_number = detail_ovn
2120 and htbb.resource_id = l_resource_id;
2121
2122
2123 l_timecard_block HXC_BLOCK_TABLE_TYPE;
2124 l_day_block HXC_BLOCK_TABLE_TYPE;
2125 l_detail_block HXC_BLOCK_TABLE_TYPE;
2126 l_detail_attribute HXC_ATTRIBUTE_TABLE_TYPE;
2127
2128 l_alias_block HXC_BLOCK_TABLE_TYPE;
2129
2130 l_index NUMBER := 1;
2131 l_att_index NUMBER := 1;
2132
2133 BEGIN
2134
2135
2136 l_timecard_block := HXC_BLOCK_TABLE_TYPE ();
2137 l_day_block := HXC_BLOCK_TABLE_TYPE ();
2138 l_detail_block := HXC_BLOCK_TABLE_TYPE ();
2139 l_detail_attribute := HXC_ATTRIBUTE_TABLE_TYPE();
2140
2141 l_alias_block := HXC_BLOCK_TABLE_TYPE ();
2142
2143 p_blocks := HXC_BLOCK_TABLE_TYPE ();
2144 p_attributes := HXC_ATTRIBUTE_TABLE_TYPE();
2145
2146 --l_index := l_block.first;
2147 --l_att_index := l_attribute.first;
2148
2149 FOR c_timecard in crs_timecard LOOP
2150
2151 -- reset all the indexes
2152 -- and temporary table
2153 l_timecard_block.delete;
2154 --l_attribute.delete;
2155 --l_index := 1;
2156 --l_att_index := 1;
2157
2158 l_timecard_block.extend;
2159 l_index := l_timecard_block.last;
2160 l_timecard_block(l_index) :=
2161 hxc_block_type (
2162 c_timecard.TIME_BUILDING_BLOCK_ID,
2163 c_timecard.TYPE,
2164 c_timecard.MEASURE,
2165 c_timecard.UNIT_OF_MEASURE,
2166 fnd_date.date_to_canonical(c_timecard.START_TIME),
2167 fnd_date.date_to_canonical(c_timecard.STOP_TIME),
2168 c_timecard.PARENT_BUILDING_BLOCK_ID,
2169 c_timecard.PARENT_IS_NEW,
2170 c_timecard.SCOPE,
2171 c_timecard.OBJECT_VERSION_NUMBER,
2172 c_timecard.APPROVAL_STATUS,
2173 c_timecard.RESOURCE_ID,
2174 c_timecard.RESOURCE_TYPE,
2175 c_timecard.APPROVAL_STYLE_ID,
2176 fnd_date.date_to_canonical(c_timecard.DATE_FROM),
2177 fnd_date.date_to_canonical(c_timecard.DATE_TO),
2178 c_timecard.COMMENT_TEXT,
2179 c_timecard.PARENT_BUILDING_BLOCK_OVN,
2180 c_timecard.NEW,
2181 c_timecard.CHANGED,
2182 c_timecard.PROCESS,
2183 c_timecard.APPLICATION_SET_ID,
2184 c_timecard.TRANSLATION_DISPLAY_KEY
2185 );
2186
2187 -- now we have a timecard block to work with
2188 -- we need to find the day attached
2189 FOR c_day_info in crs_day_info
2190 (c_timecard.resource_id
2191 ,c_timecard.TIME_BUILDING_BLOCK_ID
2192 ,c_timecard.OBJECT_VERSION_NUMBER) LOOP
2193
2194 -- we are on a new day so we are deleting
2195 -- the table
2196 l_day_block.delete;
2197
2198 l_day_block.extend;
2199 l_index := l_day_block.last;
2200 l_day_block(l_index) :=
2201 hxc_block_type (
2202 c_day_info.TIME_BUILDING_BLOCK_ID,
2203 c_day_info.TYPE,
2204 c_day_info.MEASURE,
2205 c_day_info.UNIT_OF_MEASURE,
2206 fnd_date.date_to_canonical(c_day_info.START_TIME),
2207 fnd_date.date_to_canonical(c_day_info.STOP_TIME),
2208 c_day_info.PARENT_BUILDING_BLOCK_ID,
2209 c_day_info.PARENT_IS_NEW,
2210 c_day_info.SCOPE,
2211 c_day_info.OBJECT_VERSION_NUMBER,
2212 c_day_info.APPROVAL_STATUS,
2213 c_day_info.RESOURCE_ID,
2214 c_day_info.RESOURCE_TYPE,
2215 c_day_info.APPROVAL_STYLE_ID,
2216 fnd_date.date_to_canonical(c_day_info.DATE_FROM),
2217 fnd_date.date_to_canonical(c_day_info.DATE_TO),
2218 c_day_info.COMMENT_TEXT,
2219 c_day_info.PARENT_BUILDING_BLOCK_OVN,
2220 c_day_info.NEW,
2221 c_day_info.CHANGED,
2222 c_day_info.PROCESS,
2223 c_day_info.APPLICATION_SET_ID,
2224 c_day_info.TRANSLATION_DISPLAY_KEY
2225 );
2226
2227
2228 -- now we have a timecard block to work with
2229 -- we need to find the day attached
2230 FOR c_detail_info in crs_detail_info(c_timecard.resource_id
2231 ,c_day_info.TIME_BUILDING_BLOCK_ID
2232 ,c_day_info.OBJECT_VERSION_NUMBER) LOOP
2233
2234
2235 l_detail_block.delete;
2236
2237 l_detail_block.extend;
2238 l_index := l_detail_block.last;
2239 l_detail_block(l_index) :=
2240 hxc_block_type (
2241 c_detail_info.TIME_BUILDING_BLOCK_ID,
2242 c_detail_info.TYPE,
2243 c_detail_info.MEASURE,
2244 c_detail_info.UNIT_OF_MEASURE,
2245 fnd_date.date_to_canonical(c_detail_info.START_TIME),
2246 fnd_date.date_to_canonical(c_detail_info.STOP_TIME),
2247 c_detail_info.PARENT_BUILDING_BLOCK_ID,
2248 c_detail_info.PARENT_IS_NEW,
2249 c_detail_info.SCOPE,
2250 c_detail_info.OBJECT_VERSION_NUMBER,
2251 c_detail_info.APPROVAL_STATUS,
2252 c_detail_info.RESOURCE_ID,
2253 c_detail_info.RESOURCE_TYPE,
2254 c_detail_info.APPROVAL_STYLE_ID,
2255 fnd_date.date_to_canonical(c_detail_info.DATE_FROM),
2256 fnd_date.date_to_canonical(c_detail_info.DATE_TO),
2257 c_detail_info.COMMENT_TEXT,
2258 c_detail_info.PARENT_BUILDING_BLOCK_OVN,
2259 c_detail_info.NEW,
2260 c_detail_info.CHANGED,
2261 c_detail_info.PROCESS,
2262 c_detail_info.APPLICATION_SET_ID,
2263 c_detail_info.TRANSLATION_DISPLAY_KEY);
2264
2265 -- now we are populating the attribute of this detail
2266 l_detail_attribute.delete;
2267 FOR c_detail_attribute in crs_detail_attribute
2268 (c_detail_info.TIME_BUILDING_BLOCK_ID
2269 ,c_detail_info.OBJECT_VERSION_NUMBER
2270 ,c_detail_info.resource_id) LOOP
2271
2272 l_detail_attribute.extend;
2273 l_att_index := l_detail_attribute.last;
2274 l_detail_attribute(l_att_index) :=
2275 hxc_attribute_type (
2276 c_detail_attribute.time_attribute_id,
2277 c_detail_attribute.time_building_block_id,
2278 c_detail_attribute.attribute_category,
2279 c_detail_attribute.attribute1,
2280 c_detail_attribute.attribute2,
2281 c_detail_attribute.attribute3,
2282 c_detail_attribute.attribute4,
2283 c_detail_attribute.attribute5,
2284 c_detail_attribute.attribute6,
2285 c_detail_attribute.attribute7,
2286 c_detail_attribute.attribute8,
2287 c_detail_attribute.attribute9,
2288 c_detail_attribute.attribute10,
2289 c_detail_attribute.attribute11,
2290 c_detail_attribute.attribute12,
2291 c_detail_attribute.attribute13,
2292 c_detail_attribute.attribute14,
2293 c_detail_attribute.attribute15,
2294 c_detail_attribute.attribute16,
2295 c_detail_attribute.attribute17,
2296 c_detail_attribute.attribute18,
2297 c_detail_attribute.attribute19,
2298 c_detail_attribute.attribute20,
2299 c_detail_attribute.attribute21,
2300 c_detail_attribute.attribute22,
2301 c_detail_attribute.attribute23,
2302 c_detail_attribute.attribute24,
2303 c_detail_attribute.attribute25,
2304 c_detail_attribute.attribute26,
2305 c_detail_attribute.attribute27,
2306 c_detail_attribute.attribute28,
2307 c_detail_attribute.attribute29,
2308 c_detail_attribute.attribute30,
2309 c_detail_attribute.bld_blk_info_type_id,
2310 c_detail_attribute.object_version_number,
2311 c_detail_attribute.NEW,
2312 c_detail_attribute.CHANGED,
2313 c_detail_attribute.bld_blk_info_type,
2314 c_detail_attribute.PROCESS,
2315 c_detail_attribute.BUILDING_BLOCK_OVN);
2316
2317 END LOOP;
2318
2319 -- before the next detail we are first calling the translator
2320
2321 -- we build the block table to send to the translator
2322 -- only if we have an attribute
2323 IF l_detail_attribute.count <> 0 THEN
2324
2325 l_alias_block.delete;
2326
2327 l_alias_block := l_timecard_block;
2328
2329 l_index := l_day_block.first;
2330 LOOP
2331 EXIT WHEN
2332 (NOT l_day_block.exists(l_index));
2333
2334 l_alias_block.extend;
2335 l_alias_block(l_alias_block.last) := l_day_block(l_index);
2336
2337 l_index := l_day_block.next(l_index);
2338
2339 END LOOP;
2340
2341 l_index := l_detail_block.first;
2342 LOOP
2343 EXIT WHEN
2344 (NOT l_detail_block.exists(l_index));
2345
2346 l_alias_block.extend;
2347 l_alias_block(l_alias_block.last) := l_detail_block(l_index);
2348
2349 l_index := l_detail_block.next(l_index);
2350
2351 END LOOP;
2352
2353
2354 hxc_alias_translator.do_retrieval_translation
2355 (p_attributes => l_detail_attribute
2356 ,p_blocks => l_alias_block
2357 ,p_start_time => c_timecard.start_time
2358 ,p_stop_time => c_timecard.stop_time
2359 ,p_resource_id => c_timecard.resource_id
2360 ,p_processing_mode => hxc_alias_utility.c_ss_processing
2361 ,p_add_alias_display_value => true
2362 ,p_messages => p_messages
2363 );
2364
2365 END IF;
2366
2367
2368 -- we need to append the
2369 -- block and attribute table that we want to return
2370 IF p_attributes.count = 0 THEN
2371 p_attributes := l_detail_attribute;
2372 ELSE
2373 l_index := l_detail_attribute.first;
2374 LOOP
2375 EXIT WHEN
2376 (NOT l_detail_attribute.exists(l_index));
2377
2378 p_attributes.extend;
2379 p_attributes(p_attributes.last) := l_detail_attribute(l_index);
2380
2381 l_index := l_detail_attribute.next(l_index);
2382
2383 END LOOP;
2384 END IF;
2385
2386 -- populate the detail info
2387 IF p_blocks.count = 0 THEN
2388 p_blocks := l_detail_block;
2389 ELSE
2390 l_index := l_detail_block.first;
2391 LOOP
2392 EXIT WHEN
2393 (NOT l_detail_block.exists(l_index));
2394
2395 p_blocks.extend;
2396 p_blocks(p_blocks.last) := l_detail_block(l_index);
2397
2398 l_index := l_detail_block.next(l_index);
2399
2400 END LOOP;
2401 END IF;
2402
2403 END LOOP; -- detail
2404
2405 -- populate the day info
2406 IF p_blocks.count = 0 THEN
2407 p_blocks := l_day_block;
2408 ELSE
2409 l_index := l_day_block.first;
2410 LOOP
2411 EXIT WHEN
2412 (NOT l_day_block.exists(l_index));
2413
2414 p_blocks.extend;
2415 p_blocks(p_blocks.last) := l_day_block(l_index);
2416
2417 l_index := l_day_block.next(l_index);
2418
2419 END LOOP;
2420 END IF;
2421
2422 END LOOP; -- day
2423
2424 -- populate the day info
2425 IF p_blocks.count = 0 THEN
2426 p_blocks := l_timecard_block;
2427 ELSE
2428 l_index := l_timecard_block.first;
2429 LOOP
2430 EXIT WHEN
2431 (NOT l_timecard_block.exists(l_index));
2432
2433 p_blocks.extend;
2434 p_blocks(p_blocks.last) := l_timecard_block(l_index);
2435
2436 l_index := l_timecard_block.next(l_index);
2437
2438 END LOOP;
2439 END IF;
2440
2441 END LOOP; -- timecard
2442
2443 END cla_summary_alias_translation;
2444
2445 END hxc_timecard_utilities;