[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMECARD_UTILITIES
Source
1 PACKAGE BODY hxc_timecard_utilities AS
2 /* $Header: hxctcutil.pkb 120.30.12020000.4 2013/05/16 09:35:43 pravesk 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 g_debug THEN
298 hr_utility.trace('> TRUNC(p_future_date_limit)=' || TRUNC(p_future_date_limit));
299 hr_utility.trace('> TRUNC(p_assignment_end)=' || TRUNC(p_assignment_end));
300 hr_utility.trace('> p_position=' || p_position);
301 hr_utility.trace('> IF TRUNC(p_start_date) > TRUNC(p_future_date_limit) '
302 || ' OR TRUNC(p_start_date) > TRUNC(p_assignment_end)');
303 END IF;
304
305 IF p_position = 'AFTER'
306 THEN
307 IF TRUNC(p_start_date) > TRUNC(p_future_date_limit)
308 OR TRUNC(p_start_date) > TRUNC(p_assignment_end)
309 THEN
310 IF g_debug THEN
311 hr_utility.trace('not added');
312 END IF;
313 RETURN FALSE;
314 END IF;
315
316 l_index := NVL(p_periods.last, 0) + 1;
317 ELSE
318 l_index := NVL(p_periods.first, 0) - 1;
319 END IF;
320
321 p_periods(l_index).start_date := p_start_date;
322 p_periods(l_index).end_date := p_end_date;
323 p_periods(l_index).p_set_more_period := p_set_more_period;
324
325 IF g_debug THEN
326 hr_utility.trace('added ok');
327 END IF;
328 RETURN TRUE;
329 END add_period;
330
331
332 PROCEDURE find_current_period(
333 p_rec_period_start_date IN DATE
334 ,p_period_type IN VARCHAR2
335 ,p_duration_in_days IN NUMBER
336 ,p_current_date IN DATE
337 ,p_period_start OUT NOCOPY DATE
338 ,p_period_end OUT NOCOPY DATE
339 )
340 IS
341 BEGIN
342 IF p_period_type IS NULL
343 THEN
344 get_period_by_duration(
345 p_rec_period_start_date => p_rec_period_start_date
346 ,p_duration_in_days => p_duration_in_days
347 ,p_current_date => p_current_date
348 ,p_start_date => p_period_start
349 ,p_end_date => p_period_end
350 );
351 ELSE
352 hxc_period_evaluation.period_start_stop(
353 p_current_date => p_current_date
354 ,p_rec_period_start_date => p_rec_period_start_date
355 ,l_period_start => p_period_start
356 ,l_period_end => p_period_end
357 ,l_base_period_type => p_period_type
358 );
359 END IF;
360 END find_current_period;
361
362
363 PROCEDURE find_empty_period(
364 p_future IN BOOLEAN
365 ,p_periods IN periods
366 ,p_empty_period IN OUT NOCOPY VARCHAR2
367 ,p_default_tc_period IN VARCHAR2
368 )
369 IS
370 l_index NUMBER;
371 BEGIN
372
373
374 p_empty_period := NULL;
375
376 IF p_future
377 THEN
378 l_index := p_periods.first;
379 ELSE
380 l_index := p_periods.last;
381 END IF;
382
383 LOOP
384 EXIT WHEN NOT p_periods.exists(l_index);
385
386 IF g_debug THEN
387 hr_utility.trace('start=' || p_periods(l_index).start_date
388 || ' end=' || p_periods(l_index).end_date
389 || 'exists=' || NVL(p_periods(l_index).exist_flag, 'N'));
390 END IF;
391
392 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
393 THEN
394
395
396 IF not p_future and p_default_tc_period = 'EARLIEST' AND
397 TRUNC(SYSDATE) >= p_periods(l_index).start_date THEN
398
399 p_empty_period := TO_CHAR(p_periods(l_index).start_date, g_date_format)
400 || g_separator
401 || TO_CHAR(p_periods(l_index).end_date, g_date_format);
402 --RETURN;
403 END IF;
404
405 IF not p_future and p_default_tc_period = 'CLOSEST' and
406 TRUNC(SYSDATE) >= p_periods(l_index).start_date THEN
407
408 IF SYSDATE between p_periods(l_index).start_date and p_periods(l_index).end_date
409 THEN
410 null;
411 ELSE
412
413 p_empty_period := TO_CHAR(p_periods(l_index).start_date, g_date_format)
414 || g_separator
415 || TO_CHAR(p_periods(l_index).end_date, g_date_format);
416 RETURN;
417 END IF;
418
419 END IF;
420
421 IF( (p_future AND TRUNC(SYSDATE) <= p_periods(l_index).end_date) OR (NOT p_future))
422 AND p_default_tc_period = 'FUTURE'
423 THEN
424 p_empty_period := TO_CHAR(p_periods(l_index).start_date, g_date_format)
425 || g_separator
426 || TO_CHAR(p_periods(l_index).end_date, g_date_format);
427 RETURN;
428 END IF;
429 END IF;
430
431 IF p_future
432 THEN
433 l_index := p_periods.next(l_index);
434 ELSE
435 l_index := p_periods.prior(l_index);
436 END IF;
437
438 END LOOP;
439
440 END find_empty_period;
441
442
443
444 FUNCTION get_periods(
445 p_resource_id IN NUMBER
446 ,p_resource_type IN VARCHAR2
447 ,p_current_date IN DATE
448 ,p_show_existing_timecard IN VARCHAR2
449
450 )
451 RETURN periods
452 IS
453 l_start_date DATE;
454 l_end_date DATE;
455 l_current_date DATE;
456 l_period_index NUMBER;
457 l_last_period_end DATE;
458 l_new_periods periods;
459 l_periods periods;
460 l_period_count NUMBER;
461 l_assignment_index NUMBER;
462 l_added BOOLEAN;
463 l_processed_periods periods;
464 l_dummy varchar2(15);
465 l_proc VARCHAR2(50);
466
467 l_set_more_period VARCHAR2(1);
468 l_approval_status HXC_TIME_BUILDING_BLOCKS.APPROVAL_STATUS%TYPE;
469
470 -- New Fields.
471 l_assignment_processed_periods periods;
472 l_index number;
473
474 CURSOR c_timecards(
475 p_resource_id IN NUMBER
476 ,p_resource_type IN VARCHAR2
477 ,p_first_start_date IN DATE
478 ,p_last_end_date IN DATE
479 )
480 IS
481 SELECT START_TIME,
482 STOP_TIME,
483 APPROVAL_STATUS
484 FROM hxc_time_building_blocks
485 WHERE SCOPE = 'TIMECARD'
486 AND DATE_TO = hr_general.end_of_time
487 AND RESOURCE_ID = p_resource_id
488 AND RESOURCE_TYPE = p_resource_type
489 AND STOP_TIME >= p_first_start_date
490 AND START_TIME <= p_last_end_date
491 ORDER BY START_TIME;
492
493
494 -- Bug 14595660
495 -- Picks up all continuous timecards starting from the
496 -- given date.
497 -- The following cursor connects timecards from END DATE ->
498 -- END DATE+1 = START_DATE of next timecard.
499
500 CURSOR get_leading_timecards( p_resource_id IN NUMBER,
501 p_start_time IN DATE)
502 IS SELECT COUNT(1)
503 FROM hxc_timecard_summary
504 START WITH resource_id = p_resource_id
505 AND start_time = p_start_time
506 CONNECT BY resource_id = p_resource_id
507 AND PRIOR TRUNC(stop_time) + 1 = start_time;
508
509
510 CURSOR get_lagging_timecards( p_resource_id IN NUMBER,
511 p_stop_time IN DATE)
512 IS SELECT COUNT(1)
513 FROM hxc_timecard_summary
514 START WITH resource_id = p_resource_id
515 AND start_time < p_stop_time
516 AND stop_time = p_stop_time
517 CONNECT BY resource_id = p_resource_id
518 AND start_time < PRIOR stop_time
519 AND PRIOR start_time - (1/(24*60*60)) = stop_time;
520
521
522 l_leading_count NUMBER;
523 l_lagging_count NUMBER;
524
525
526 BEGIN
527
528 g_debug := hr_utility.debug_enabled;
529 g_num_future_entries := 10;
530 g_num_past_entries := 10;
531
532 IF g_debug THEN
533 l_proc := 'get_periods';
534 hr_utility.set_location (g_package||l_proc, 10);
535 END IF;
536
537 IF g_debug THEN
538 hr_utility.trace('> In hxc_timecard_utilities.get_periods.');
539 hr_utility.trace('> p_resource_id ::'||p_resource_id);
540 hr_utility.trace('> p_resource_type ::'||p_resource_type);
541 hr_utility.trace('> p_current_date ::'||p_current_date);
542 hr_utility.trace('> p_show_existing_timecard ::'||p_show_existing_timecard);
543 END IF;
544
545 IF g_debug THEN
546 hr_utility.trace('> calling find_current_period');
547 hr_utility.trace('> g_rec_period_start_date ::'||g_rec_period_start_date);
548 hr_utility.trace('> g_period_type ::'||g_period_type);
549 hr_utility.trace('> g_duration_in_days ::'||g_duration_in_days);
550 hr_utility.trace('> p_current_date ::'||p_current_date);
551 END IF;
552
553
554 --get current period
555 find_current_period(
556 p_rec_period_start_date => g_rec_period_start_date
557 ,p_period_type => g_period_type
558 ,p_duration_in_days => g_duration_in_days
559 ,p_current_date => p_current_date
560 ,p_period_start => l_start_date
561 ,p_period_end => l_end_date
562 );
563
564 IF g_debug THEN
565 hr_utility.trace('> returned from find_current_period');
566 hr_utility.trace('> l_start_date ::'||l_start_date);
567 hr_utility.trace('> l_end_date ::'||l_end_date);
568 END IF;
569
570 IF g_debug THEN
571 hr_utility.set_location (g_package||l_proc, 20);
572 END IF;
573
574
575 /* Aug 23 always add current period
576 l_added := add_period(
577 p_periods => l_periods
578 ,p_start_date => l_start_date
579 ,p_end_date => l_end_date
580 ,p_future_date_limit => g_future_date_limit
581 ,p_assignment_end => g_assignment_periods(g_assignment_periods.last).end_date
582 );
583
584 IF g_debug THEN
585 hr_utility.set_location (g_package||l_proc, 30);
586 END IF;
587
588 -- this case only happens when we are looking for an empty period
589 -- in the future. Since we already know the period before the current
590 -- on are not empty, if the current one is already out of future boundary
591 -- (future date limit, assignment end date) it doesn't make sense to
592 -- continue looking at other periods beyond this one.
593
594 IF NOT l_added
595 THEN
596 RETURN l_periods;
597 END IF;
598 */
599
600 IF g_debug THEN
601 hr_utility.trace('> l_start_date = '||l_start_date);
602 hr_utility.trace('> g_past_date_limit = '||g_past_date_limit);
603 hr_utility.trace('> l_end_date = '||l_end_date);
604 hr_utility.trace('> g_future_date_limit = '||g_future_date_limit);
605 hr_utility.trace('> IF TRUNC(l_start_date) < TRUNC(g_past_date_limit) THEN');
606 END IF;
607
608 IF TRUNC(l_start_date) < TRUNC(g_past_date_limit) -- ADDED FOR BUG 8996217
609 THEN
610
611 IF g_debug THEN
612 hr_utility.trace('> IF CONDITION SUCCESS RETURN l_periods');
613 END IF;
614
615 RETURN l_periods;
616
617 ELSE
618
619 IF g_debug THEN
620 hr_utility.trace('> ELSE CONDITION SUCCESS ');
621 hr_utility.trace('> check the condition >> IF TRUNC(l_end_date) <= TRUNC(g_future_date_limit) THEN');
622 END IF;
623
624 IF TRUNC(l_end_date) <= TRUNC(g_future_date_limit) THEN
625
626 IF g_debug THEN
627 hr_utility.trace('> IF condition is true add l_start_date and l_end_date to l_periods');
628 END IF;
629
630 l_periods(1).start_date := l_start_date;
631 l_periods(1).end_date := l_end_date;
632 ELSE
633
634 IF g_debug THEN
635 hr_utility.trace('> IF condition is false RETURN l_periods');
636 END IF;
637
638 RETURN l_periods;
639
640 END IF;
641
642 END IF;
643
644 IF g_debug THEN
645 hr_utility.set_location (g_package||l_proc, 40);
646 END IF;
647
648
649 IF p_show_existing_timecard = 'N'
650 THEN
651
652 -- Bug 14595660
653 -- This condition means that we are in Upload Timecard page, not in Create timecard page.
654
655 -- The logic followed is.
656 -- For a given from date
657 ---- Pick up 10 future time periods.
658 ---- Pick up 10 past time periods
659 ---- If any of these have existing timecards, delete these periods.
660
661 -- The issue there is that if the given FROM date has 11 following timecards, we later dalete all
662 -- these timecards.
663 -- Meaning upload timecard's choice list does not show MORE periods.
664 -- To get over this, we select more than 10 periods if there are continuous timecards.
665
666 OPEN get_leading_timecards(p_resource_id,
667 trunc(l_end_date)+1);
668 FETCH get_leading_timecards INTO l_leading_count;
669 CLOSE get_leading_timecards;
670
671 IF l_leading_count > 11
672 THEN
673 g_num_future_entries := g_num_future_entries + (l_leading_count - 10);
674
675 END IF;
676
677 IF g_debug
678 THEN
679 hr_utility.trace('Future '||g_num_future_entries);
680 END IF;
681
682
683 OPEN get_lagging_timecards(p_resource_id,
684 l_start_date - (1/(24*60*60)));
685 FETCH get_lagging_timecards INTO l_lagging_count;
686 CLOSE get_lagging_timecards;
687
688 IF l_lagging_count > 11
689 THEN
690 g_num_past_entries := g_num_past_entries + (l_lagging_count - 10);
691
692 END IF;
693
694 IF g_debug
695 THEN
696 hr_utility.trace('Past '||g_num_past_entries);
697 END IF;
698
699
700 END IF;
701
702
703 --get past periods
704
705
706
707 l_period_count := 0;
708 l_current_date := l_start_date - 1;
709 l_assignment_index := g_assignment_periods.last;
710
711 IF g_debug THEN
712 hr_utility.trace('l_period_count=' || l_period_count);
713 hr_utility.trace('g_num_past_entries=' || g_num_past_entries);
714 END IF;
715
716 IF g_debug THEN
717 hr_utility.trace('> l_period_count=' || l_period_count);
718 hr_utility.trace('> l_current_date=' || l_current_date);
719 hr_utility.trace('> l_assignment_index=' || l_assignment_index);
720 hr_utility.trace('> In WHILE l_period_count <= g_num_past_entries LOOP');
721 END IF;
722
723
724 WHILE l_period_count <= g_num_past_entries
725 LOOP
726 IF g_debug THEN
727 hr_utility.set_location (g_package||l_proc, 50);
728 END IF;
729
730 IF g_debug THEN
731 hr_utility.trace('> ****************************************************');
732 hr_utility.trace('> calling find_current_period');
733 hr_utility.trace('> g_rec_period_start_date ::'||g_rec_period_start_date);
734 hr_utility.trace('> g_period_type ::'||g_period_type);
735 hr_utility.trace('> g_duration_in_days ::'||g_duration_in_days);
736 hr_utility.trace('> l_current_date ::'||l_current_date);
737 END IF;
738
739 find_current_period(
740 p_rec_period_start_date => g_rec_period_start_date
741 ,p_period_type => g_period_type
742 ,p_duration_in_days => g_duration_in_days
743 ,p_current_date => l_current_date
744 ,p_period_start => l_start_date
745 ,p_period_end => l_end_date
746 );
747
748 IF g_debug THEN
749 hr_utility.trace('> returned from find_current_period');
750 hr_utility.trace('> l_start_date ::'||l_start_date);
751 hr_utility.trace('> l_end_date ::'||l_end_date);
752 END IF;
753
754 IF g_debug THEN
755 hr_utility.set_location (g_package||l_proc, 60);
756 END IF;
757
758 IF g_debug THEN
759 hr_utility.trace('> g_past_date_limit ::'||g_past_date_limit);
760 hr_utility.trace('> IF l_end_date < g_past_date_limit');
761 END IF;
762
763 IF l_end_date < g_past_date_limit
764 THEN
765 EXIT;
766 END IF;
767
768 IF g_debug THEN
769 hr_utility.set_location (g_package||l_proc, 61);
770 END IF;
771
772 IF g_debug THEN
773 hr_utility.trace('> TRUNC(l_end_date) ::'||TRUNC(l_end_date));
774 hr_utility.trace('> TRUNC(g_assignment_periods(l_assignment_index).start_date ::'
775 ||TRUNC(g_assignment_periods(l_assignment_index).start_date));
776 hr_utility.trace('> trunc(l_start_date) ::'||trunc(l_start_date));
777 hr_utility.trace('> trunc(g_past_date_limit) ::'||trunc(g_past_date_limit));
778 hr_utility.trace('> IF TRUNC(l_end_date) >= '||
779 'TRUNC(g_assignment_periods(l_assignment_index).start_date');
780
781 END IF;
782
783 IF TRUNC(l_end_date) >= TRUNC(g_assignment_periods(l_assignment_index).start_date)
784 THEN
785 -- only if there is atleast 1 period more than normally we show,
786 -- we will show the More Periods... option.
787 IF TRUNC(l_start_date) >= TRUNC(g_past_date_limit)
788 AND TRUNC(l_end_date) <= TRUNC(g_future_date_limit) -- ADDED FOR BUG 8996217
789 THEN
790 IF ((l_period_count = g_num_past_entries) AND (p_show_existing_timecard = 'Y')) THEN
791 l_added := add_period(
792 p_periods => l_periods
793 ,p_start_date => l_start_date
794 ,p_end_date => l_end_date
795 ,p_position => 'BEFORE'
796 ,p_set_more_period => hxc_timecard.c_more_period_indicator
797 );
798 ELSE
799 l_added := add_period(
800 p_periods => l_periods
801 ,p_start_date => l_start_date
802 ,p_end_date => l_end_date
803 ,p_position => 'BEFORE'
804 );
805
806 END IF;
807 END IF;
808 l_period_count := l_period_count + 1;
809 l_current_date := l_start_date - 1;
810
811 IF g_debug THEN
812 hr_utility.set_location (g_package||l_proc, 70);
813 END IF;
814 ELSE
815 -- earlier than current assignment period, look at the assignment
816 -- following this one
817 l_assignment_index := g_assignment_periods.prior(l_assignment_index);
818
819 IF g_assignment_periods.exists(l_assignment_index)
820 THEN
821 -- this check is to eliminate duplicate entries when the previous
822 -- assignment end date is less than a period away from current
823 -- period start_date
824 IF g_assignment_periods(l_assignment_index).end_date <= l_end_date
825 THEN
826 IF g_debug THEN
827 hr_utility.set_location (g_package||l_proc, 80);
828 END IF;
829
830 l_current_date := g_assignment_periods(l_assignment_index).end_date;
831 ELSE
832 IF g_debug THEN
833 hr_utility.set_location (g_package||l_proc, 90);
834 END IF;
835
836 l_current_date := l_end_date;
837 END IF;
838 ELSE
839 IF g_debug THEN
840 hr_utility.set_location (g_package||l_proc, 100);
841 END IF;
842
843 EXIT;
844 END IF;
845 END IF;
846 END LOOP;
847
848
849
850 IF g_debug THEN
851 hr_utility.set_location (g_package||l_proc, 120);
852 END IF;
853
854 --get future periods
855
856 IF g_debug THEN
857 hr_utility.trace('> get future periods');
858 hr_utility.trace('> l_assignment_index ::'||l_assignment_index);
859 hr_utility.trace('> l_period_count ::'||l_period_count);
860 hr_utility.trace('> l_current_date ::'||l_current_date);
861 hr_utility.trace('> While l_period_count <= g_num_future_entries ');
862 END IF;
863
864 l_assignment_index := g_assignment_periods.last;
865
866 l_period_count := 0;
867 l_current_date := l_periods(1).end_date + 1; -- need work
868 WHILE l_period_count <= g_num_future_entries
869 LOOP
870
871 IF g_debug THEN
872 hr_utility.trace('> calling find_current_period');
873 hr_utility.trace('> g_rec_period_start_date ::'||g_rec_period_start_date);
874 hr_utility.trace('> g_period_type ::'||g_period_type);
875 hr_utility.trace('> g_duration_in_days ::'||g_duration_in_days);
876 hr_utility.trace('> l_current_date ::'||l_current_date);
877 END IF;
878
879 find_current_period(
880 p_rec_period_start_date => g_rec_period_start_date
881 ,p_period_type => g_period_type
882 ,p_duration_in_days => g_duration_in_days
883 ,p_current_date => l_current_date
884 ,p_period_start => l_start_date
885 ,p_period_end => l_end_date
886 );
887
888 IF g_debug THEN
889 hr_utility.trace('> returned from find_current_period');
890 hr_utility.trace('> l_start_date ::'||l_start_date);
891 hr_utility.trace('> l_end_date ::'||l_end_date);
892 hr_utility.trace('> l_period_count ::'||l_period_count);
893 hr_utility.trace('> g_num_future_entries ::'||g_num_future_entries);
894 hr_utility.trace('> p_show_existing_timecard ::'||p_show_existing_timecard);
895 hr_utility.trace('> IF TRUNC(l_start_date) >= TRUNC(g_past_date_limit) AND '
896 || 'TRUNC(l_end_date) <= TRUNC(g_future_date_limit) THEN');
897 END IF;
898
899 IF TRUNC(l_start_date) >= TRUNC(g_past_date_limit) -- ADDED FOR BUG 8996217
900 AND TRUNC(l_end_date) <= TRUNC(g_future_date_limit)
901 THEN
902
903 IF ((l_period_count = g_num_future_entries) AND (p_show_existing_timecard = 'Y')) then
904
905 IF g_debug THEN
906 hr_utility.trace('> IF for future periods');
907 END IF;
908
909 l_added := add_period(
910 p_periods => l_periods
911 ,p_start_date => l_start_date
912 ,p_end_date => l_end_date
913 ,p_future_date_limit => g_future_date_limit
914 ,p_assignment_end => g_assignment_periods(l_assignment_index).end_date
915 ,p_set_more_period =>hxc_timecard.c_more_period_indicator
916 );
917 ELSE
918
919 IF g_debug THEN
920 hr_utility.trace('> ELSE for future periods');
921 END IF;
922
923 l_added := add_period(
924 p_periods => l_periods
925 ,p_start_date => l_start_date
926 ,p_end_date => l_end_date
927 ,p_future_date_limit => g_future_date_limit
928 ,p_assignment_end => g_assignment_periods(l_assignment_index).end_date
929 );
930 END IF;
931 END IF;
932
933 IF NOT l_added
934 THEN
935 EXIT;
936 END IF;
937
938 l_period_count := l_period_count + 1;
939 l_current_date := l_end_date + 1;
940
941 END LOOP;
942
943 IF l_periods.count = 0
944 THEN
945 RETURN l_periods;
946 END IF;
947
948 l_period_index := l_periods.first;
949 l_last_period_end := l_periods(l_period_index).start_date - 1;
950
951 OPEN c_timecards(
952 p_resource_id => p_resource_id
953 ,p_resource_type => p_resource_type
954 ,p_first_start_date => l_periods(l_periods.first).start_date
955 ,p_last_end_date => l_periods(l_periods.last).end_date
956 );
957
958 LOOP
959 FETCH c_timecards INTO l_start_date, l_end_date,l_approval_status;
960 EXIT WHEN c_timecards%NOTFOUND;
961
962 WHILE l_periods.exists(l_period_index)
963 AND l_periods(l_period_index).end_date < l_end_date
964 LOOP
965
966 -- 115.34 change. To differentiate an archived time period from a normal period.
967
968
969 if(check_period_archived(l_periods(l_period_index).end_date)) then
970
971 generate_periods(
972 p_periods => l_new_periods
973 ,p_start_date => l_periods(l_period_index).start_date
974 ,p_end_date => l_periods(l_period_index).end_date
975 ,p_last_period_end => l_last_period_end
976 ,p_past_date_limit => g_past_date_limit
977 ,p_future_date_limit => g_future_date_limit
978 ,p_exists => hxc_timecard.c_archived_period_indicator
979 ,p_show_existing_timecard => p_show_existing_timecard
980 ,p_set_more_period => l_periods(l_period_index).p_set_more_period
981 );
982
983 ELSE
984 generate_periods(
985 p_periods => l_new_periods
986 ,p_start_date => l_periods(l_period_index).start_date
987 ,p_end_date => l_periods(l_period_index).end_date
988 ,p_last_period_end => l_last_period_end
989 ,p_past_date_limit => g_past_date_limit
990 ,p_future_date_limit => g_future_date_limit
991 ,p_exists => NULL
992 ,p_set_more_period => l_periods(l_period_index).p_set_more_period
993 );
994 END IF;
995
996
997 l_last_period_end := l_periods(l_period_index).end_date;
998 l_period_index := l_periods.next(l_period_index);
999 END LOOP;
1000
1001 IF g_debug THEN
1002 hr_utility.set_location (g_package||l_proc, 70);
1003 END IF;
1004 -- For this period we need not check whether its archived or not, as this is an existing period.
1005 -- Existing periods are found from hxc_time_building_blocks table, which means the data is
1006 -- present in the online tables.
1007
1008 --add timecard row
1009 IF (l_approval_status NOT IN ('ERROR')) THEN
1010 generate_periods(
1011 p_periods => l_new_periods
1012 ,p_start_date => l_start_date
1013 ,p_end_date => l_end_date
1014 ,p_last_period_end => l_last_period_end
1015 ,p_past_date_limit => g_past_date_limit
1016 ,p_future_date_limit => g_future_date_limit
1017 ,p_exists => hxc_timecard.c_existing_period_indicator
1018 ,p_show_existing_timecard => p_show_existing_timecard
1019 ,p_set_more_period => get_more_period_value(l_periods,l_start_date,l_end_date)
1020 );
1021 END IF;
1022
1023 l_last_period_end := l_end_date;
1024
1025 IF g_debug THEN
1026 hr_utility.set_location ( g_package||l_proc, 80);
1027 END IF;
1028
1029 -- ignore overlapping periods
1030 WHILE l_periods.exists(l_period_index)
1031 AND l_periods(l_period_index).start_date <= l_end_date
1032 LOOP
1033 l_period_index := l_periods.next(l_period_index);
1034 END LOOP;
1035
1036 IF g_debug THEN
1037 hr_utility.set_location ( g_package||l_proc, 90);
1038 END IF;
1039
1040 END LOOP;
1041
1042 CLOSE c_timecards;
1043
1044
1045 --add the rest of the periods
1046 WHILE l_periods.exists(l_period_index)
1047 LOOP
1048 -- 115.34 change. To differentiate an archived time period from a normal period.
1049
1050 if(check_period_archived(l_periods(l_period_index).end_date)) then
1051 generate_periods(
1052 p_periods => l_new_periods
1053 ,p_start_date => l_periods(l_period_index).start_date
1054 ,p_end_date => l_periods(l_period_index).end_date
1055 ,p_last_period_end => l_last_period_end
1056 ,p_past_date_limit => g_past_date_limit
1057 ,p_future_date_limit => g_future_date_limit
1058 ,p_exists => hxc_timecard.c_archived_period_indicator
1059 ,p_show_existing_timecard => p_show_existing_timecard
1060 ,p_set_more_period => l_periods(l_period_index).p_set_more_period
1061 );
1062 ELSE
1063 generate_periods(
1064 p_periods => l_new_periods
1065 ,p_start_date => l_periods(l_period_index).start_date
1066 ,p_end_date => l_periods(l_period_index).end_date
1067 ,p_last_period_end => l_last_period_end
1068 ,p_past_date_limit => g_past_date_limit
1069 ,p_future_date_limit => g_future_date_limit
1070 ,p_exists => NULL
1071 ,p_set_more_period => l_periods(l_period_index).p_set_more_period
1072 );
1073 END IF;
1074
1075
1076 l_last_period_end := NULL;
1077 l_period_index := l_periods.next(l_period_index);
1078
1079 END LOOP;
1080
1081 IF g_debug THEN
1082 hr_utility.set_location ( g_package||l_proc, 100);
1083 END IF;
1084
1085 --RETURN l_new_periods;
1086
1087
1088 --below is added for mid period hiring
1089 -- v115.31 changed to use indexed looping.
1090 -- Fix for bug no. 3401914
1091
1092
1093 l_period_index := l_new_periods.first;
1094 while l_period_index is not null
1095 loop
1096 process_assignments(
1097 l_new_periods(l_period_index)
1098 ,g_assignment_periods
1099 ,l_assignment_processed_periods -- changed here
1100 );
1101 l_period_index := l_new_periods.NEXT(l_period_index);
1102 end loop;
1103
1104 --For import Timecard Page, only retain the open periods.
1105 l_index :=0;
1106 IF(p_show_existing_timecard = 'N') THEN
1107 l_period_index := l_assignment_processed_periods.first;
1108 while l_period_index is not null
1109 loop
1110 if((l_assignment_processed_periods(l_period_index).exist_flag is null) or
1111 ((l_assignment_processed_periods(l_period_index).exist_flag <> hxc_timecard.c_existing_period_indicator) AND
1112 (l_assignment_processed_periods(l_period_index).exist_flag <> hxc_timecard.c_archived_period_indicator))
1113 ) then
1114 l_processed_periods(l_index) := l_assignment_processed_periods(l_period_index);
1115 l_index := l_index+1;
1116 end if;
1117 l_period_index := l_assignment_processed_periods.NEXT(l_period_index);
1118 end loop;
1119 RETURN l_processed_periods;
1120 END IF;
1121 RETURN l_assignment_processed_periods;
1122
1123 END get_periods;
1124
1125
1126 PROCEDURE get_first_empty_period(
1127 p_resource_id IN NUMBER
1128 ,p_resource_type IN VARCHAR2
1129 ,p_current_date IN DATE
1130 ,p_show_existing_timecard IN VARCHAR2
1131 ,p_periods OUT NOCOPY VARCHAR2
1132 )
1133 IS
1134 l_current_date DATE;
1135 l_index NUMBER;
1136 l_periods periods;
1137 l_previous_period_end DATE;
1138 l_previous_period_start DATE;
1139 l_default_tc_period VARCHAR2(20);
1140 l_pref_table hxc_preference_evaluation.t_pref_table;
1141
1142 l_empty_period VARCHAR2(50);
1143
1144 l_index_temp NUMBER;
1145
1146 BEGIN
1147
1148 hxc_preference_evaluation.resource_preferences(
1149 p_resource_id => p_resource_id
1150 , p_pref_code_list=> 'TC_W_TCRD_PERIOD'
1151 , p_pref_table => l_pref_table
1152 , p_resp_id => -101
1153 );
1154 --Get the Default Timecard period option from preference
1155 If l_pref_table is not null then
1156 l_default_tc_period := l_pref_table(l_pref_table.FIRST).attribute2;
1157 end if;
1158
1159 --User can save the preference with out selecting any value, in this case we
1160 --should retain the existing behavior
1161
1162 If l_default_tc_period is null then
1163 l_default_tc_period := 'FUTURE';
1164 end if;
1165
1166 --FUTURE - Period on or after system date - Current Behavior
1167 --EARLIEST - Earliest Period prior to system date
1168 --CLOSEST - Closest Period prior to system date
1169
1170 IF l_default_tc_period = 'EARLIEST' OR l_default_tc_period = 'CLOSEST' THEN
1171
1172 -- look for empty period in the past
1173 l_current_date := p_current_date;
1174
1175 IF g_debug THEN
1176 hr_utility.trace('> In hxc_timecard_utilities.get_first_empty_period.');
1177 hr_utility.trace('> p_resource_id ::'||p_resource_id);
1178 hr_utility.trace('> p_resource_type ::'||p_resource_type);
1179 hr_utility.trace('> l_current_date ::'||l_current_date);
1180 hr_utility.trace('> p_show_existing_timecard ::'||p_show_existing_timecard);
1181 END IF;
1182
1183 WHILE TRUE LOOP
1184
1185 l_periods :=
1186 get_periods(
1187 p_resource_id => p_resource_id
1188 ,p_resource_type => p_resource_type
1189 ,p_current_date => l_current_date
1190 ,p_show_existing_timecard => p_show_existing_timecard
1191 );
1192
1193 IF g_debug THEN
1194
1195 hr_utility.trace('> AFTER CALL TO GET_PERIODS');
1196 l_index_temp := l_periods.first;
1197 LOOP
1198 EXIT WHEN NOT l_periods.exists(l_index_temp);
1199
1200 hr_utility.trace(
1201 '> start_date ::'||l_periods(l_index_temp).start_date
1202 ||' - end_date ::'||l_periods(l_index_temp).end_date
1203 ||' - exist_flag ::'||l_periods(l_index_temp).exist_flag
1204 ||' - p_set_more_period ::'||l_periods(l_index_temp).p_set_more_period
1205 );
1206
1207 l_index_temp := l_periods.next(l_index_temp);
1208 END LOOP;
1209
1210 END IF;
1211
1212
1213 l_index := l_periods.first;
1214
1215 IF l_periods.count = 0
1216 OR (l_previous_period_start IS NOT NULL
1217 AND l_previous_period_start = l_periods(l_index).start_date)
1218 THEN
1219 -- can't find anything in the past, do not RETURN, search in the future
1220 -- empty period
1221 Exit;
1222 ELSE
1223 ----look for the empty period in the past
1224 find_empty_period(
1225 p_future => FALSE
1226 ,p_periods => l_periods
1227 ,p_empty_period => p_periods
1228 ,p_default_tc_period => l_default_tc_period
1229 );
1230
1231 -- In the case of CLOSEST, you should return as and when an empty period is
1232 -- found in the past
1233 -- But in the case of EARLIEST, we should continue searching till the first
1234 -- period
1235
1236 IF p_periods IS NOT NULL AND l_default_tc_period = 'CLOSEST'
1237 THEN
1238 -- found an empty period in the past, return result
1239 RETURN;
1240 ELSIF p_periods IS NOT NULL
1241 THEN
1242 l_empty_period := p_periods;
1243 l_current_date := l_periods(l_index).start_date - 1;
1244 l_previous_period_start := l_periods(l_index).start_date;
1245 ELSE
1246 p_periods := l_empty_period;
1247 l_current_date := l_periods(l_index).start_date - 1;
1248 l_previous_period_start := l_periods(l_index).start_date;
1249
1250 END IF;
1251
1252 END IF;
1253 END LOOP;
1254
1255 -- In the case of EARLIEST, we should return if any empty period found in the past
1256 -- Otherwise we should search in the past
1257
1258 IF l_default_tc_period = 'EARLIEST' AND p_periods IS NOT NULL THEN
1259 RETURN;
1260 END IF;
1261
1262 END IF;
1263
1264 -- Search in the Feature starts!!
1265
1266 l_default_tc_period := 'FUTURE';
1267
1268 l_current_date := p_current_date;
1269
1270 IF g_debug THEN
1271 hr_utility.trace('> In hxc_timecard_utilities.get_first_empty_period.');
1272 hr_utility.trace('> p_resource_id ::'||p_resource_id);
1273 hr_utility.trace('> p_resource_type ::'||p_resource_type);
1274 hr_utility.trace('> l_current_date ::'||l_current_date);
1275 hr_utility.trace('> p_show_existing_timecard ::'||p_show_existing_timecard);
1276 END IF;
1277
1278 WHILE TRUE LOOP
1279
1280 l_periods :=
1281 get_periods(
1282 p_resource_id => p_resource_id
1283 ,p_resource_type => p_resource_type
1284 ,p_current_date => l_current_date
1285 ,p_show_existing_timecard => p_show_existing_timecard
1286 );
1287
1288 IF g_debug THEN
1289
1290 hr_utility.trace('> AFTER CALL TO GET_PERIODS');
1291 l_index_temp := l_periods.first;
1292 LOOP
1293 EXIT WHEN NOT l_periods.exists(l_index_temp);
1294
1295 hr_utility.trace(
1296 '> start_date ::'||l_periods(l_index_temp).start_date
1297 ||' - end_date ::'||l_periods(l_index_temp).end_date
1298 ||' - exist_flag ::'||l_periods(l_index_temp).exist_flag
1299 ||' - p_set_more_period ::'||l_periods(l_index_temp).p_set_more_period
1300 );
1301
1302 l_index_temp := l_periods.next(l_index_temp);
1303 END LOOP;
1304
1305 END IF;
1306
1307 -- Now we are looking for an empty period. The idea is we look for the
1308 -- earliest empty period in the future, if we can't find one within the
1309 -- future date limit and/or assignment end date, we will look in the
1310 -- past to find the latest empty period. If we can't find one within the
1311 -- past date limit and/or assignment start date, we will return null
1312
1313 IF l_periods.count = 0
1314 OR (l_previous_period_end IS NOT NULL
1315 AND l_previous_period_end = l_periods(l_periods.last).end_date)
1316 THEN
1317 -- can't find anything in the future
1318 EXIT;
1319
1320 ELSE
1321 ----look for the empty period in the future
1322 find_empty_period(
1323 p_future => TRUE
1324 ,p_periods => l_periods
1325 ,p_empty_period => p_periods
1326 ,p_default_tc_period => l_default_tc_period
1327 );
1328
1329 IF p_periods IS NOT NULL
1330 THEN
1331 -- found empty period in the future
1332 RETURN;
1333 ELSE
1334 l_index := l_periods.last;
1335
1336 l_current_date := l_periods(l_index).end_date + 1;
1337 l_previous_period_end := l_periods(l_index).end_date;
1338
1339 END IF;
1340 END IF;
1341 END LOOP;
1342
1343 -- You should search in the past only for scenario FUTURE,
1344 -- for the remaining two cases, past search is already completed
1345
1346 IF l_default_tc_period = 'FUTURE' THEN
1347 -- look for empty period in the past
1348 l_current_date := SYSDATE;
1349
1350 IF g_debug THEN
1351 hr_utility.trace('> In hxc_timecard_utilities.get_first_empty_period.');
1352 hr_utility.trace('> p_resource_id ::'||p_resource_id);
1353 hr_utility.trace('> p_resource_type ::'||p_resource_type);
1354 hr_utility.trace('> l_current_date ::'||l_current_date);
1355 hr_utility.trace('> p_show_existing_timecard ::'||p_show_existing_timecard);
1356 END IF;
1357
1358 WHILE TRUE LOOP
1359
1360 l_periods :=
1361 get_periods(
1362 p_resource_id => p_resource_id
1363 ,p_resource_type => p_resource_type
1364 ,p_current_date => l_current_date
1365 ,p_show_existing_timecard => p_show_existing_timecard
1366 );
1367
1368 IF g_debug THEN
1369
1370 hr_utility.trace('> AFTER CALL TO GET_PERIODS');
1371 l_index_temp := l_periods.first;
1372 LOOP
1373 EXIT WHEN NOT l_periods.exists(l_index_temp);
1374
1375 hr_utility.trace(
1376 '> start_date ::'||l_periods(l_index_temp).start_date
1377 ||' - end_date ::'||l_periods(l_index_temp).end_date
1378 ||' - exist_flag ::'||l_periods(l_index_temp).exist_flag
1379 ||' - p_set_more_period ::'||l_periods(l_index_temp).p_set_more_period
1380 );
1381
1382 l_index_temp := l_periods.next(l_index_temp);
1383 END LOOP;
1384
1385 END IF;
1386
1387 l_index := l_periods.first;
1388
1389
1390 IF l_periods.count = 0
1391 OR (l_previous_period_start IS NOT NULL
1392 AND l_previous_period_start = l_periods(l_index).start_date)
1393 THEN
1394 -- can't find anything in the past
1395 RETURN;
1396 ELSE
1397 ----look for the empty period in the past
1398 find_empty_period(
1399 p_future => FALSE
1400 ,p_periods => l_periods
1401 ,p_empty_period => p_periods
1402 ,p_default_tc_period => l_default_tc_period
1403 );
1404
1405 IF p_periods IS NOT NULL
1406 THEN
1407 -- found an empty period in the past, return result
1408
1409 RETURN;
1410 ELSE
1411 l_current_date := l_periods(l_index).start_date - 1;
1412 l_previous_period_start := l_periods(l_index).start_date;
1413 END IF;
1414
1415 END IF;
1416 END LOOP;
1417 END IF;
1418
1419 END get_first_empty_period;
1420
1421 PROCEDURE periods_to_string(
1422 p_first_periods IN periods
1423 ,p_second_periods IN periods
1424 ,p_periods OUT NOCOPY VARCHAR2
1425 )
1426 IS
1427 l_index NUMBER;
1428 BEGIN
1429
1430 l_index := p_first_periods.first;
1431
1432 LOOP
1433 EXIT WHEN NOT p_first_periods.exists(l_index);
1434
1435 p_periods := NVL(p_periods, '')
1436 || g_separator
1437 || NVL(p_first_periods(l_index).exist_flag, '')
1438 || TO_CHAR(p_first_periods(l_index).start_date, g_date_format)
1439 || g_separator
1440 || TO_CHAR(p_first_periods(l_index).end_date, g_date_format)
1441 || NVL(p_first_periods(l_index).p_set_more_period,'');
1442
1443 l_index := p_first_periods.next(l_index);
1444
1445 END LOOP;
1446
1447 IF p_second_periods.count = 0
1448 OR p_second_periods(p_second_periods.last).start_date
1449 = p_first_periods(p_first_periods.last).start_date
1450 THEN
1451 RETURN;
1452 END IF;
1453
1454 l_index := p_second_periods.first;
1455 LOOP
1456 EXIT WHEN NOT p_second_periods.exists(l_index);
1457
1458 IF p_second_periods(l_index).start_date
1459 > p_first_periods(p_first_periods.last).start_date
1460 THEN
1461 p_periods := NVL(p_periods, '')
1462 || g_separator
1463 || NVL(p_second_periods(l_index).exist_flag, '')
1464 || TO_CHAR(p_second_periods(l_index).start_date, g_date_format)
1465 || g_separator
1466 || TO_CHAR(p_second_periods(l_index).end_date, g_date_format)
1467 || NVL(p_second_periods(l_index).p_set_more_period,'');
1468
1469 END IF;
1470
1471 l_index := p_second_periods.next(l_index);
1472 END LOOP;
1473
1474 END periods_to_string;
1475
1476 PROCEDURE get_period_list(
1477 p_resource_id IN NUMBER
1478 ,p_resource_type IN VARCHAR2
1479 ,p_current_date IN DATE
1480 ,p_show_existing_timecard IN VARCHAR2
1481 ,p_periods OUT NOCOPY VARCHAR2
1482 )
1483 IS
1484 l_index NUMBER;
1485 l_periods periods;
1486 l_temp_periods periods;
1487 l_current_date DATE;
1488 l_proc VARCHAR2(500);
1489 l_index_temp NUMBER;
1490
1491 BEGIN
1492 g_debug := hr_utility.debug_enabled;
1493
1494 IF g_debug THEN
1495 l_proc := 'get_period_list';
1496 hr_utility.set_location(g_package||l_proc, 10);
1497 END IF;
1498
1499 l_current_date := p_current_date;
1500
1501
1502 IF g_debug THEN
1503 hr_utility.trace('> In hxc_timecard_utilities.get_period_list.');
1504 hr_utility.trace('> p_resource_id ::'||p_resource_id);
1505 hr_utility.trace('> p_resource_type ::'||p_resource_type);
1506 hr_utility.trace('> l_current_date ::'||l_current_date);
1507 hr_utility.trace('> p_show_existing_timecard ::'||p_show_existing_timecard);
1508 END IF;
1509
1510 WHILE TRUE LOOP
1511 l_temp_periods :=
1512 get_periods(
1513 p_resource_id => p_resource_id
1514 ,p_resource_type => p_resource_type
1515 ,p_current_date => l_current_date
1516 ,p_show_existing_timecard => p_show_existing_timecard
1517 );
1518
1519 IF g_debug THEN
1520
1521 hr_utility.trace('> AFTER CALL TO GET_PERIODS');
1522 l_index_temp := l_temp_periods.first;
1523 LOOP
1524 EXIT WHEN NOT l_temp_periods.exists(l_index_temp);
1525
1526 hr_utility.trace(
1527 '> start_date ::'||l_temp_periods(l_index_temp).start_date
1528 ||' - end_date ::'||l_temp_periods(l_index_temp).end_date
1529 ||' - exist_flag ::'||l_temp_periods(l_index_temp).exist_flag
1530 ||' - p_set_more_period ::'||l_temp_periods(l_index_temp).p_set_more_period
1531 );
1532
1533 l_index_temp := l_temp_periods.next(l_index_temp);
1534 END LOOP;
1535
1536 hr_utility.trace('> l_temp_periods.count ::'||l_temp_periods.count);
1537 hr_utility.trace('> l_periods.count ::'||l_periods.count);
1538
1539 END IF;
1540
1541 IF g_debug THEN
1542 hr_utility.set_location(g_package||l_proc, 20);
1543 END IF;
1544
1545 IF l_temp_periods.count = 0
1546 OR l_periods.count > 0
1547 THEN
1548 IF g_debug THEN
1549 hr_utility.set_location(g_package||l_proc, 30);
1550 END IF;
1551
1552 --if this list is empty, or this is the second list,
1553 --combine this list with the first list and return
1554 periods_to_string(
1555 p_first_periods => l_periods
1556 ,p_second_periods => l_temp_periods
1557 ,p_periods => p_periods
1558 );
1559 IF g_debug THEN
1560 hr_utility.set_location(g_package||l_proc, 40);
1561 END IF;
1562 RETURN;
1563 ELSE
1564 --this is the first list and it is not empty
1565 --if the last periods doesn't go beyong current date period, try
1566 --go forward one more day after current period. This is to avoid
1567 --the senario:
1568
1569 IF g_debug THEN
1570 hr_utility.set_location(g_package||l_proc, 50);
1571 END IF;
1572
1573 IF g_debug THEN
1574
1575 hr_utility.trace('> l_temp_periods(l_temp_periods.last).start_date ::'||
1576 l_temp_periods(l_temp_periods.last).start_date );
1577 hr_utility.trace('> p_current_date::'||p_current_date);
1578
1579 END IF;
1580
1581 IF l_temp_periods(l_temp_periods.last).start_date = p_current_date
1582 THEN
1583 IF g_debug THEN
1584 hr_utility.set_location(g_package||l_proc, 60);
1585 END IF;
1586
1587 l_current_date := l_temp_periods(l_temp_periods.last).end_date + 1;
1588 l_periods := l_temp_periods;
1589 ELSE
1590 --this list is ready to return
1591 periods_to_string(
1592 p_first_periods => l_temp_periods
1593 ,p_second_periods => l_periods
1594 ,p_periods => p_periods
1595 );
1596
1597 IF g_debug THEN
1598 hr_utility.trace('start=' || to_char(l_temp_periods(l_temp_periods.last).start_date, 'YYYY/MM/DD'));
1599
1600 hr_utility.set_location(g_package||l_proc, 70);
1601 END IF;
1602
1603 RETURN;
1604 END IF;
1605 END IF;
1606 END LOOP;
1607
1608
1609 END get_period_list;
1610
1611
1612 FUNCTION get_assignment_periods(
1613 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE,
1614 p_period_start_date IN DATE DEFAULT SYSDATE -- 12387815
1615 )
1616 RETURN periods
1617 IS
1618 l_assignment_index NUMBER;
1619 l_start_date DATE;
1620 l_end_date DATE;
1621 l_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
1622 l_current_assignment PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
1623 l_assignment_periods periods;
1624 l_assign_period_limit NUMBER; -- 5922228
1625
1626
1627 /*CURSOR c_assignments(
1628 p_resource_id IN NUMBER,
1629 p_assign_period_limit IN NUMBER -- 5922228
1630 )
1631 IS
1632 SELECT pas.ASSIGNMENT_ID,
1633 pas.EFFECTIVE_START_DATE,
1634 NVL(pas.EFFECTIVE_END_DATE, hr_general.end_of_time)
1635 FROM PER_ALL_ASSIGNMENTS_F pas,
1636 per_assignment_status_types typ
1637 ,per_periods_of_service ppos --14596859
1638 WHERE pas.PERSON_ID = p_resource_id
1639 AND pas.ASSIGNMENT_TYPE in ('E','C')
1640 AND pas.PRIMARY_FLAG = 'Y'
1641 AND pas.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
1642 -- AND typ.PER_SYSTEM_STATUS IN ( 'ACTIVE_ASSIGN','ACTIVE_CWK') -- 5922228
1643 AND DECODE(typ.PER_SYSTEM_STATUS,'ACTIVE_ASSIGN',1,
1644 'ACTIVE_CWK', 1,
1645 0 ) >= p_assign_period_limit
1646 -- AND pas.EFFECTIVE_START_DATE <= SYSDATE
1647 AND ppos.person_id = pas.person_id
1648 AND sysdate between trunc(DATE_START)
1649 and trunc(NVL(ppos.ACTUAL_TERMINATION_DATE,hr_general.end_of_time))
1650 AND pas.period_of_service_id = ppos.period_of_service_id
1651 ORDER BY EFFECTIVE_START_DATE;*/
1652
1653 --Bug 16771643
1654 CURSOR c_assignments
1655 (p_resource_id IN number
1656 , p_assign_period_limit IN number) IS
1657 SELECT pas.assignment_id
1658 , pas.effective_start_date
1659 , nvl (pas.effective_end_date, hr_general.end_of_time) end_date
1660 FROM per_all_assignments_f pas
1661 , per_assignment_status_types typ
1662 WHERE pas.person_id = p_resource_id
1663 AND pas.assignment_type IN ('E', 'C')
1664 AND pas.primary_flag = 'Y'
1665 AND pas.assignment_status_type_id = typ.assignment_status_type_id
1666 AND decode (typ.per_system_status, 'ACTIVE_ASSIGN'
1667 , 1, 'ACTIVE_CWK'
1668 , 1, 0) >= p_assign_period_limit
1669 AND (
1670 EXISTS
1671 (
1672 SELECT 1
1673 FROM per_periods_of_service ppos
1674 WHERE ppos.person_id = pas.person_id
1675 AND trunc (sysdate)
1676 BETWEEN trunc (date_start)
1677 AND decode (p_assign_period_limit, 0
1678 , trunc (nvl (ppos.final_process_date, hr_general.end_of_time)), trunc (nvl (ppos.actual_termination_date, hr_general.end_of_time)))
1679 AND pas.period_of_service_id = ppos.period_of_service_id
1680 )
1681 OR EXISTS
1682 (
1683 SELECT 1
1684 FROM per_periods_of_placement ppop
1685 WHERE ppop.person_id = pas.person_id
1686 AND trunc (sysdate)
1687 BETWEEN date_start
1688 AND decode (p_assign_period_limit, 0
1689 , nvl (ppop.final_process_date, hr_general.end_of_time), nvl (ppop.actual_termination_date, hr_general.end_of_time))
1690 AND pas.period_of_placement_date_start
1691 BETWEEN ppop.date_start
1692 AND decode (p_assign_period_limit, 0
1693 , nvl (ppop.final_process_date, hr_general.end_of_time), nvl (ppop.actual_termination_date, hr_general.end_of_time))
1694 )
1695 )
1696 ORDER BY effective_start_date;
1697
1698 BEGIN
1699
1700 -- get the all the primary assignment periods. We don't allow users
1701 -- to enter timecard for future assignment periods, thus we don't
1702 -- query up future assignment periods.
1703 l_assignment_index := 0;
1704 l_current_assignment := -1;
1705
1706
1707 -- 5922228 ( Fetching the preference for the given resource id for
1708 -- future time card periods )
1709
1710 -- Bug 12387815
1711 -- Added p_period_end_date parameter in preference evaluation call
1712 IF hxc_preference_evaluation.resource_preferences( p_resource_id,
1713 'TC_W_TCRD_ST_ALW_EDITS',
1714 10,
1715 p_period_start_date
1716 ,101
1717 ) = 'FIN_ASSGN'
1718 THEN
1719 l_assign_period_limit := 0;
1720 ELSE
1721 l_assign_period_limit := 1;
1722 END IF;
1723
1724
1725
1726 OPEN c_assignments(
1727 p_resource_id => p_resource_id,
1728 p_assign_period_limit => l_assign_period_limit -- 5922228
1729 );
1730
1731 LOOP
1732 FETCH c_assignments INTO l_assignment_id, l_start_date, l_end_date;
1733 EXIT WHEN c_assignments%NOTFOUND;
1734 /* jxtan fixed Aug23
1735 IF l_current_assignment <> l_assignment_id
1736 THEN
1737 IF l_start_date <= SYSDATE
1738 THEN
1739 l_assignment_index := l_assignment_index + 1;
1740 g_assignment_periods(l_assignment_index).start_date := l_start_date;
1741 g_assignment_periods(l_assignment_index).end_date := l_end_date;
1742 l_current_assignment := l_assignment_id;
1743
1744 ELSE
1745 EXIT;
1746 END IF;
1747 ELSE
1748 g_assignment_periods(l_assignment_index).end_date := l_end_date;
1749 END IF;
1750 */
1751
1752 --possible fix for LGE
1753 IF l_current_assignment = l_assignment_id
1754 AND TRUNC(l_assignment_periods(l_assignment_index).end_date) + 1 =
1755 TRUNC(l_start_date)
1756 THEN
1757 l_assignment_periods(l_assignment_index).end_date := l_end_date;
1758 ELSE
1759 IF l_current_assignment <> l_assignment_id
1760 AND l_start_date > SYSDATE
1761 THEN
1762 -- we don't allow user to enter time for future active assignment
1763 -- unless it is an assignment change
1764 EXIT;
1765 ELSE
1766 l_assignment_index := l_assignment_index + 1;
1767 l_assignment_periods(l_assignment_index).start_date := l_start_date;
1768 l_assignment_periods(l_assignment_index).end_date := l_end_date;
1769 l_current_assignment := l_assignment_id;
1770 END IF;
1771 END IF;
1772
1773
1774 END LOOP;
1775
1776 RETURN l_assignment_periods;
1777
1778 END get_assignment_periods;
1779
1780
1781
1782 PROCEDURE init_globals(
1783 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
1784 )
1785 IS
1786 l_assignment_index NUMBER;
1787 l_start_date DATE;
1788 l_end_date DATE;
1789 l_num_past_days NUMBER;
1790 l_num_future_days NUMBER;
1791 l_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
1792 l_current_assignment PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
1793 l_rec_period_id VARCHAR2(50);
1794
1795 l_index BINARY_INTEGER;
1796 l_pref_table hxc_preference_evaluation.t_pref_table;
1797
1798 /*
1799 CURSOR c_assignments(
1800 p_resource_id IN NUMBER
1801 )
1802 IS
1803 SELECT pas.ASSIGNMENT_ID,
1804 pas.EFFECTIVE_START_DATE,
1805 NVL(pas.EFFECTIVE_END_DATE, hr_general.end_of_time)
1806 FROM PER_ALL_ASSIGNMENTS_F pas,
1807 per_assignment_status_types typ
1808 WHERE pas.PERSON_ID = p_resource_id
1809 AND pas.ASSIGNMENT_TYPE = 'E'
1810 AND pas.PRIMARY_FLAG = 'Y'
1811 AND pas.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
1812 AND typ.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
1813 -- AND pas.EFFECTIVE_START_DATE <= SYSDATE
1814 ORDER BY EFFECTIVE_START_DATE;
1815 */
1816 /*
1817 CURSOR c_period_info(
1818 p_resource_id IN NUMBER
1819 )
1820 IS
1821 SELECT rp.period_type,
1822 rp.duration_in_days,
1823 rp.start_date
1824 FROM hxc_recurring_periods rp,
1825 per_time_period_types p
1826 WHERE p.period_type (+) = rp.period_type
1827 AND hxc_preference_evaluation.resource_preferences(
1828 p_resource_id,'TC_W_TCRD_PERIOD|1|') = rp.recurring_period_id;
1829 */
1830
1831 CURSOR c_period_info(p_recurring_period_id number)
1832 is
1833 select hrp.period_type,
1834 hrp.duration_in_days,
1835 hrp.start_date
1836 from hxc_recurring_periods hrp
1837 where hrp.recurring_period_id = p_recurring_period_id;
1838
1839 BEGIN
1840
1841 g_debug := hr_utility.debug_enabled;
1842
1843 g_assignment_periods.delete;
1844
1845 g_assignment_periods := get_assignment_periods(p_resource_id);
1846 -- Added check to see if there is atleast one Active Assignment
1847 -- ver 115.32
1848 if(g_assignment_periods.COUNT<1)
1849 then
1850 g_initialized := 'RETURN';
1851 RETURN;
1852 end if;
1853
1854 -- call the preference
1855
1856 hxc_preference_evaluation.resource_preferences(
1857 p_resource_id => p_resource_id
1858 , p_pref_code_list=> 'TC_W_TCRD_PERIOD,TC_W_TCRD_ST_ALW_EDITS'
1859 , p_pref_table => l_pref_table
1860 , p_resp_id => -101
1861 );
1862
1863 l_index := l_pref_table.FIRST;
1864
1865 WHILE ( l_index IS NOT NULL )
1866 LOOP
1867
1868 IF ( l_pref_table(l_index).preference_code = 'TC_W_TCRD_PERIOD' )
1869 THEN
1870 l_rec_period_id := l_pref_table(l_index).attribute1;
1871
1872 ELSIF ( l_pref_table(l_index).preference_code = 'TC_W_TCRD_ST_ALW_EDITS' )
1873 THEN
1874 l_num_future_days := l_pref_table(l_index).attribute11;
1875 l_num_past_days := l_pref_table(l_index).attribute6;
1876
1877 END IF;
1878 l_index := l_pref_table.NEXT(l_index);
1879
1880 END LOOP;
1881
1882
1883
1884 -- l_rec_period_id :=
1885 -- hxc_preference_evaluation.resource_preferences(
1886 -- p_resource_id,
1887 -- 'TC_W_TCRD_PERIOD|1|'
1888 -- );
1889
1890 --get the person's time period information
1891 OPEN c_period_info(
1892 p_recurring_period_id => TO_NUMBER(l_rec_period_id)
1893 );
1894
1895 FETCH c_period_info INTO g_period_type, g_duration_in_days, g_rec_period_start_date;
1896
1897 IF c_period_info%NOTFOUND
1898 THEN
1899 g_initialized := 'RETURN';
1900 RETURN;
1901 END IF;
1902
1903 CLOSE c_period_info;
1904
1905
1906 IF g_debug THEN
1907 hr_utility.trace('l_period_type=' || g_period_type);
1908 hr_utility.trace('l_duration_in_days=' || g_duration_in_days);
1909 hr_utility.trace('l_rec_period_start_date=' || g_rec_period_start_date);
1910 END IF;
1911
1912 -- l_num_past_days :=
1913 -- hxc_preference_evaluation.resource_preferences(
1914 -- p_resource_id,
1915 -- 'TC_W_TCRD_ST_ALW_EDITS',
1916 -- 6);
1917
1918 -- l_num_future_days :=
1919 -- hxc_preference_evaluation.resource_preferences(
1920 -- p_resource_id,
1921 -- 'TC_W_TCRD_ST_ALW_EDITS',
1922 -- 11);
1923
1924 IF g_debug THEN
1925 hr_utility.trace('l_num_past_days=' || l_num_past_days);
1926 hr_utility.trace('l_num_future_days=' || l_num_future_days);
1927 END IF;
1928
1929 IF l_num_past_days IS NOT NULL
1930 THEN
1931 g_past_date_limit := SYSDATE - TO_NUMBER(l_num_past_days);
1932 ELSE
1933 g_past_date_limit := hr_general.START_OF_TIME;
1934 END IF;
1935
1936 IF l_num_future_days IS NOT NULL
1937 THEN
1938 g_future_date_limit := SYSDATE + TO_NUMBER(l_num_future_days);
1939 ELSE
1940 g_future_date_limit := hr_general.END_OF_TIME;
1941 END IF;
1942 g_initialized := 'Y';
1943
1944 IF g_debug THEN
1945 hr_utility.trace(' l_past_date_limit =' || to_char(g_past_date_limit, 'YYYY/MM/DD'));
1946 hr_utility.trace(' l_future_date_limit=' ||to_char(g_future_date_limit, 'YYYY/MM/DD') );
1947 END IF;
1948
1949 END init_globals;
1950
1951
1952 /*=========================================================================
1953 * this new procedure evaluates period related preferences on the server
1954 * side. It should be the one to be called by the middle tier from now on.
1955 * However we keep the old one to be compatible with existing middle tier
1956 * code.
1957 *========================================================================*/
1958
1959 PROCEDURE get_time_periods(
1960 p_resource_id IN VARCHAR2
1961 ,p_resource_type IN VARCHAR2
1962 ,p_current_date IN VARCHAR2
1963 ,p_show_existing_timecard IN VARCHAR2
1964 ,p_first_empty_period IN VARCHAR2
1965 ,p_periods OUT NOCOPY VARCHAR2
1966 )
1967 IS
1968 l_assignment_index NUMBER;
1969 l_start_date DATE;
1970 l_end_date DATE;
1971 l_resource_id NUMBER := TO_NUMBER(p_resource_id);
1972
1973 BEGIN
1974 g_debug := hr_utility.debug_enabled;
1975
1976 IF g_debug THEN
1977 hr_utility.trace('> In hxc_timecard_utilities.get_time_periods');
1978 END IF;
1979
1980 IF g_debug THEN
1981 hr_utility.trace('> p_resource_id ::'||p_resource_id);
1982 hr_utility.trace('> p_resource_type ::'||p_resource_type);
1983 hr_utility.trace('> p_current_date ::'||p_current_date);
1984 hr_utility.trace('> p_show_existing_timecard ::'||p_show_existing_timecard);
1985 hr_utility.trace('> p_first_empty_period ::'||p_first_empty_period);
1986 END IF;
1987
1988 /*
1989 IF l_resource_id = 10251
1990 THEN
1991 --hr_utility.trace_on(NULL, 'test');
1992 END IF;
1993 */
1994 -- mstewart 5/20/2002
1995 -- temporary fix to resolve pl/sql caching issues. For now force
1996 -- initialization of the globals every procedure call - need to
1997 -- identify the entry points to properly fix this problem.
1998 g_initialized := 'N';
1999
2000 IF g_initialized = 'N'
2001 THEN
2002 init_globals(
2003 p_resource_id => l_resource_id
2004 );
2005 END IF;
2006
2007 IF g_initialized = 'RETURN'
2008 THEN
2009 RETURN;
2010 END IF;
2011
2012 IF p_first_empty_period = 'Y'
2013 THEN
2014
2015 IF g_debug THEN
2016 hr_utility.trace('start empty');
2017 END IF;
2018
2019 get_first_empty_period(
2020 p_resource_id => l_resource_id
2021 ,p_resource_type => p_resource_type
2022 ,p_current_date => SYSDATE
2023 ,p_show_existing_timecard => 'Y'
2024 ,p_periods => p_periods
2025 );
2026 IF g_debug THEN
2027 hr_utility.trace('returned empty period=' || p_periods);
2028 END IF;
2029 ELSE
2030 IF g_debug THEN
2031 hr_utility.trace('start getting list');
2032 END IF;
2033
2034 get_period_list(
2035 p_resource_id => l_resource_id
2036 ,p_resource_type => p_resource_type
2037 ,p_current_date => TO_DATE(p_current_date, g_date_format)
2038 ,p_show_existing_timecard => p_show_existing_timecard
2039 ,p_periods => p_periods
2040 );
2041
2042 IF g_debug THEN
2043 hr_utility.trace('> In hxc_timecard_utilities.get_time_periods AFTER CALL TO get_period_list');
2044 hr_utility.trace('> p_periods'||p_periods);
2045 END IF;
2046
2047 IF g_debug THEN
2048 hr_utility.trace('finished getting list');
2049 END IF;
2050 END IF;
2051
2052
2053
2054 END get_time_periods;
2055
2056
2057
2058 PROCEDURE get_time_periods(
2059 p_resource_id IN VARCHAR2
2060 ,p_resource_type IN VARCHAR2
2061 ,p_rec_period_start_date IN VARCHAR2
2062 ,p_period_type IN VARCHAR2
2063 ,p_duration_in_days IN VARCHAR2
2064 ,p_current_date IN VARCHAR2
2065 ,p_num_past_entries IN VARCHAR2
2066 ,p_num_future_entries IN VARCHAR2
2067 ,p_num_past_days IN VARCHAR2
2068 ,p_num_future_days IN VARCHAR2
2069 ,p_hire_date IN VARCHAR2
2070 ,p_show_existing_timecard IN VARCHAR2
2071 ,p_first_empty_period IN VARCHAR2
2072 ,p_periods OUT NOCOPY VARCHAR2
2073 )
2074 IS
2075 BEGIN
2076 get_time_periods(
2077 p_resource_id => p_resource_id
2078 ,p_resource_type => p_resource_type
2079 ,p_current_date => p_current_date
2080 ,p_show_existing_timecard => p_show_existing_timecard
2081 ,p_first_empty_period => p_first_empty_period
2082 ,p_periods => p_periods
2083 );
2084
2085
2086 END get_time_periods;
2087
2088 PROCEDURE get_current_period(
2089 p_rec_period_start_date IN VARCHAR2
2090 ,p_period_type IN VARCHAR2
2091 ,p_duration_in_days IN VARCHAR2
2092 ,p_current_date IN VARCHAR2
2093 ,p_period OUT NOCOPY VARCHAR2
2094 )
2095 IS
2096 l_start_date DATE;
2097 l_end_date DATE;
2098 l_proc VARCHAR2(50);
2099 BEGIN
2100
2101 g_debug := hr_utility.debug_enabled;
2102
2103 IF g_debug THEN
2104 l_proc := 'get_current_period';
2105 hr_utility.set_location(g_package||l_proc, 10);
2106 END IF;
2107
2108 find_current_period(
2109 p_rec_period_start_date => fnd_date.canonical_to_date
2110 (p_rec_period_start_date)
2111 ,p_period_type => p_period_type
2112 ,p_duration_in_days => p_duration_in_days
2113 ,p_current_date => TO_DATE(p_current_date, g_date_format)
2114 ,p_period_start => l_start_date
2115 ,p_period_end => l_end_date
2116 );
2117
2118 IF g_debug THEN
2119 hr_utility.set_location(g_package||l_proc, 20);
2120 END IF;
2121
2122 p_period := TO_CHAR(l_start_date, g_date_format)
2123 || g_separator
2124 || TO_CHAR(l_end_date, g_date_format);
2125
2126 IF g_debug THEN
2127 hr_utility.set_location(g_package||l_proc, 30);
2128 END IF;
2129
2130 END get_current_period;
2131
2132
2133 FUNCTION get_pto_balance
2134 (p_resource_id IN VARCHAR2
2135 ,p_assignment_id IN VARCHAR2
2136 ,p_start_time IN VARCHAR2
2137 ,p_plan_code IN VARCHAR2
2138 )
2139 RETURN VARCHAR2
2140 IS
2141 --
2142 CURSOR csr_get_plan
2143 (p_assignment_id NUMBER
2144 ,p_effective_date DATE
2145 ,p_plan_name VARCHAR2
2146 )
2147 IS
2148 SELECT pap.accrual_plan_id
2149 FROM pay_accrual_plans pap
2150 ,pay_element_types_f pet
2151 ,pay_element_links_f pel
2152 ,pay_element_entries_f pee
2153 WHERE pap.accrual_plan_element_type_id = pet.element_type_id
2154 AND pet.element_type_id = pel.element_type_id
2155 AND pee.effective_start_date BETWEEN pet.effective_start_date
2156 AND pet.effective_end_date
2157 AND pel.element_link_id = pee.element_link_id
2158 AND pee.effective_start_date BETWEEN pel.effective_start_date
2159 AND pel.effective_end_date
2160 AND pee.assignment_id = p_assignment_id
2161 AND p_effective_date BETWEEN pee.effective_start_date
2162 AND pee.effective_end_date
2163 AND pap.accrual_plan_name = p_plan_name;
2164 /*
2165 AND to_date(p_effective_date, 'YYYY/MM/DD HH24:MI:SS') BETWEEN pee.effective_start_date
2166 AND pee.effective_end_date;
2167 */
2168 l_pto_balance NUMBER;
2169 l_plan_id NUMBER;
2170 l_start_time DATE;
2171 l_plan_name VARCHAR2(80);
2172 --
2173 BEGIN
2174 --
2175 l_start_time := FND_DATE.CANONICAL_TO_DATE(p_start_time);
2176 --
2177 IF (p_plan_code = 'MONTHLY') THEN
2178 l_plan_name := 'LGE_TL_MonthlyLeave_AP';
2179 ELSE
2180 l_plan_name := 'LGE_TL_AnnualLeave_AP';
2181 END IF;
2182 --
2183 OPEN csr_get_plan(p_assignment_id, l_start_time, l_plan_name);
2184 --
2185 FETCH csr_get_plan INTO l_plan_id;
2186 --
2187 IF csr_get_plan%NOTFOUND THEN
2188 --
2189 CLOSE csr_get_plan;
2190 --
2191 l_plan_id := null;
2192 --
2193 RETURN '0|PTO';
2194 --
2195 ELSE
2196 --
2197 CLOSE csr_get_plan;
2198 --
2199 -- now call pay_us_pto_accrual.get_net_accrual to get the balance
2200 --
2201 l_pto_balance :=
2202 pay_us_pto_accrual.get_net_accrual
2203 (p_assignment_id
2204 ,l_start_time
2205 ,l_plan_id
2206 );
2207 --
2208 RETURN TO_CHAR(round(l_pto_balance, 2)) || '|PTO';
2209 --
2210 END IF;
2211 --
2212 END get_pto_balance;
2213
2214
2215 -- ----------------------------------------------------------------------------
2216 -- |--------------------< cla_summary_alias_translation>----------------------|
2217 -- this procedure is called in the cla project/payroll layout to alias translate
2218 -- for a particular timecard_id the entire history of the timecard
2219 -- ----------------------------------------------------------------------------
2220 PROCEDURE cla_summary_alias_translation(
2221 p_timecard_id IN NUMBER
2222 ,p_resource_id IN NUMBER
2223 ,p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
2224 ,p_blocks IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
2225 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
2226 )IS
2227
2228
2229 -- first we need to query the timecard info
2230 cursor crs_timecard is
2231 select
2232 TIME_BUILDING_BLOCK_ID
2233 ,TYPE
2234 ,MEASURE
2235 ,UNIT_OF_MEASURE
2236 ,START_TIME
2237 ,STOP_TIME
2238 ,PARENT_BUILDING_BLOCK_ID
2239 ,'N' PARENT_IS_NEW
2240 ,SCOPE
2241 ,OBJECT_VERSION_NUMBER
2242 ,APPROVAL_STATUS
2243 ,RESOURCE_ID
2244 ,RESOURCE_TYPE
2245 ,APPROVAL_STYLE_ID
2246 ,DATE_FROM
2247 ,DATE_TO
2248 ,COMMENT_TEXT
2249 ,PARENT_BUILDING_BLOCK_OVN
2250 ,'N' NEW
2251 ,'N' CHANGED
2252 ,'N' PROCESS
2253 ,APPLICATION_SET_ID
2254 ,TRANSLATION_DISPLAY_KEY
2255 FROM hxc_time_building_blocks
2256 where time_building_block_id = p_timecard_id
2257 and resource_id = p_resource_id
2258 and scope = 'TIMECARD';
2259 --and date_to = hr_general.end_of_time;
2260
2261
2262 CURSOR crs_day_info (
2263 p_resource_id IN NUMBER,
2264 p_parent_building_block_id IN NUMBER,
2265 p_parent_ovn IN NUMBER
2266 )
2267 IS
2268 SELECT
2269 TIME_BUILDING_BLOCK_ID
2270 ,TYPE
2271 ,MEASURE
2272 ,UNIT_OF_MEASURE
2273 ,START_TIME
2274 ,STOP_TIME
2275 ,PARENT_BUILDING_BLOCK_ID
2276 ,'N' PARENT_IS_NEW
2277 ,SCOPE
2278 ,OBJECT_VERSION_NUMBER
2279 ,APPROVAL_STATUS
2280 ,RESOURCE_ID
2281 ,RESOURCE_TYPE
2282 ,APPROVAL_STYLE_ID
2283 ,DATE_FROM
2284 ,DATE_TO
2285 ,COMMENT_TEXT
2286 ,PARENT_BUILDING_BLOCK_OVN
2287 ,'N' NEW
2288 ,'N' CHANGED
2289 ,'N' PROCESS
2290 ,APPLICATION_SET_ID
2291 ,TRANSLATION_DISPLAY_KEY
2292 FROM hxc_time_building_blocks
2293 WHERE resource_id = p_resource_id
2294 AND parent_building_block_id = p_parent_building_block_id
2295 AND parent_building_block_ovn = p_parent_ovn
2296 AND SCOPE = 'DAY';
2297
2298 CURSOR crs_detail_info (
2299 p_resource_id IN NUMBER,
2300 p_parent_building_block_id IN NUMBER,
2301 p_parent_ovn IN NUMBER
2302 )
2303 IS
2304 SELECT
2305 TIME_BUILDING_BLOCK_ID
2306 ,TYPE
2307 ,MEASURE
2308 ,UNIT_OF_MEASURE
2309 ,START_TIME
2310 ,STOP_TIME
2311 ,PARENT_BUILDING_BLOCK_ID
2312 ,'N' PARENT_IS_NEW
2313 ,SCOPE
2314 ,OBJECT_VERSION_NUMBER
2315 ,APPROVAL_STATUS
2316 ,RESOURCE_ID
2317 ,RESOURCE_TYPE
2318 ,APPROVAL_STYLE_ID
2319 ,DATE_FROM
2320 ,DATE_TO
2321 ,COMMENT_TEXT
2322 ,PARENT_BUILDING_BLOCK_OVN
2323 ,'N' NEW
2324 ,'N' CHANGED
2325 ,'N' PROCESS
2326 ,APPLICATION_SET_ID
2327 ,TRANSLATION_DISPLAY_KEY
2328 FROM hxc_time_building_blocks
2329 WHERE resource_id = p_resource_id
2330 AND parent_building_block_id = p_parent_building_block_id
2331 AND parent_building_block_ovn = p_parent_ovn
2332 AND SCOPE = 'DETAIL'
2333 order by OBJECT_VERSION_NUMBER;
2334 /*
2335 cursor crs_detail_attribute
2336 (timecard_id in number,timecard_ovn in number,l_resource_id in number) is
2337 select
2338 a.time_attribute_id
2339 ,au.time_building_block_id
2340 ,bbit.bld_blk_info_type
2341 ,a.attribute_category
2342 ,a.attribute1
2343 ,a.attribute2
2344 ,a.attribute3
2345 ,a.attribute4
2346 ,a.attribute5
2347 ,a.attribute6
2348 ,a.attribute7
2349 ,a.attribute8
2350 ,a.attribute9
2351 ,a.attribute10
2352 ,a.attribute11
2353 ,a.attribute12
2354 ,a.attribute13
2355 ,a.attribute14
2356 ,a.attribute15
2357 ,a.attribute16
2358 ,a.attribute17
2359 ,a.attribute18
2360 ,a.attribute19
2361 ,a.attribute20
2362 ,a.attribute21
2363 ,a.attribute22
2364 ,a.attribute23
2365 ,a.attribute24
2366 ,a.attribute25
2367 ,a.attribute26
2368 ,a.attribute27
2369 ,a.attribute28
2370 ,a.attribute29
2371 ,a.attribute30
2372 ,a.bld_blk_info_type_id
2373 ,a.object_version_number
2374 ,'N' NEW
2375 ,'N' CHANGED
2376 ,'N' PROCESS
2377 ,au.time_building_block_ovn BUILDING_BLOCK_OVN
2378 from hxc_bld_blk_info_types bbit,
2379 hxc_time_attribute_usages au,
2380 hxc_time_attributes a
2381 where a.time_attribute_id = au.time_attribute_id
2382 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
2383 and (au.time_building_block_id,au.time_building_block_ovn) in
2384 (select detail.time_building_block_id,detail.object_version_number
2385 from hxc_time_building_blocks detail,
2386 hxc_time_building_blocks day
2387 where day.time_building_block_id = detail.parent_building_block_id
2388 and day.object_version_number = detail.parent_building_block_ovn
2389 and day.scope = 'DAY'
2390 and detail.resource_id = l_resource_id
2391 and detail.scope = 'DETAIL'
2392 --and day.date_to = hr_general.end_of_time
2393 --and detail.date_to = hr_general.end_of_time
2394 and day.parent_building_block_id = timecard_id
2395 and day.parent_building_block_ovn = timecard_ovn
2396 and day.resource_id = l_resource_id)
2397 UNION
2398 select
2399 a.time_attribute_id
2400 ,au.time_building_block_id
2401 ,bbit.bld_blk_info_type
2402 ,a.attribute_category
2403 ,a.attribute1
2404 ,a.attribute2
2405 ,a.attribute3
2406 ,a.attribute4
2407 ,a.attribute5
2408 ,a.attribute6
2409 ,a.attribute7
2410 ,a.attribute8
2411 ,a.attribute9
2412 ,a.attribute10
2413 ,a.attribute11
2414 ,a.attribute12
2415 ,a.attribute13
2416 ,a.attribute14
2417 ,a.attribute15
2418 ,a.attribute16
2419 ,a.attribute17
2420 ,a.attribute18
2421 ,a.attribute19
2422 ,a.attribute20
2423 ,a.attribute21
2424 ,a.attribute22
2425 ,a.attribute23
2426 ,a.attribute24
2427 ,a.attribute25
2428 ,a.attribute26
2429 ,a.attribute27
2430 ,a.attribute28
2431 ,a.attribute29
2432 ,a.attribute30
2433 ,a.bld_blk_info_type_id
2434 ,a.object_version_number
2435 ,'N' NEW
2436 ,'N' CHANGED
2437 ,'N' PROCESS
2438 ,au.time_building_block_ovn BUILDING_BLOCK_OVN
2439 from hxc_bld_blk_info_types bbit,
2440 hxc_time_attribute_usages au,
2441 hxc_time_attributes a
2442 where a.time_attribute_id = au.time_attribute_id
2443 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
2444 and (au.time_building_block_id,au.time_building_block_ovn) in
2445 (select day.time_building_block_id,day.object_version_number
2446 from hxc_time_building_blocks day
2447 where -- day.date_to = hr_general.end_of_time
2448 day.scope = 'DAY'
2449 and day.parent_building_block_id = timecard_id
2450 and day.parent_building_block_ovn = timecard_ovn
2451 and day.resource_id = l_resource_id)
2452 UNION
2453 select
2454 a.time_attribute_id
2455 ,au.time_building_block_id
2456 ,bbit.bld_blk_info_type
2457 ,a.attribute_category
2458 ,a.attribute1
2459 ,a.attribute2
2460 ,a.attribute3
2461 ,a.attribute4
2462 ,a.attribute5
2463 ,a.attribute6
2464 ,a.attribute7
2465 ,a.attribute8
2466 ,a.attribute9
2467 ,a.attribute10
2468 ,a.attribute11
2469 ,a.attribute12
2470 ,a.attribute13
2471 ,a.attribute14
2472 ,a.attribute15
2473 ,a.attribute16
2474 ,a.attribute17
2475 ,a.attribute18
2476 ,a.attribute19
2477 ,a.attribute20
2478 ,a.attribute21
2479 ,a.attribute22
2480 ,a.attribute23
2481 ,a.attribute24
2482 ,a.attribute25
2483 ,a.attribute26
2484 ,a.attribute27
2485 ,a.attribute28
2486 ,a.attribute29
2487 ,a.attribute30
2488 ,a.bld_blk_info_type_id
2489 ,a.object_version_number
2490 ,'N' NEW
2491 ,'N' CHANGED
2492 ,'N' PROCESS
2493 ,au.time_building_block_ovn BUILDING_BLOCK_OVN
2494 from hxc_bld_blk_info_types bbit,
2495 hxc_time_attribute_usages au,
2496 hxc_time_attributes a
2497 where a.time_attribute_id = au.time_attribute_id
2498 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
2499 and (au.time_building_block_id,au.time_building_block_ovn) in
2500 (select time_building_block_id,object_version_number
2501 from hxc_time_building_blocks htbb
2502 where --htbb.date_to = hr_general.end_of_time
2503 htbb.scope = 'TIMECARD'
2504 and htbb.time_building_block_id = timecard_id
2505 and htbb.object_version_number = timecard_ovn
2506 and htbb.resource_id = l_resource_id)
2507 order by time_building_block_id;
2508 */
2509
2510 cursor crs_detail_attribute
2511 (detail_id in number,detail_ovn in number,l_resource_id in number) is
2512 select
2513 a.time_attribute_id
2514 ,au.time_building_block_id
2515 ,bbit.bld_blk_info_type
2516 ,a.attribute_category
2517 ,a.attribute1
2518 ,a.attribute2
2519 ,a.attribute3
2520 ,a.attribute4
2521 ,a.attribute5
2522 ,a.attribute6
2523 ,a.attribute7
2524 ,a.attribute8
2525 ,a.attribute9
2526 ,a.attribute10
2527 ,a.attribute11
2528 ,a.attribute12
2529 ,a.attribute13
2530 ,a.attribute14
2531 ,a.attribute15
2532 ,a.attribute16
2533 ,a.attribute17
2534 ,a.attribute18
2535 ,a.attribute19
2536 ,a.attribute20
2537 ,a.attribute21
2538 ,a.attribute22
2539 ,a.attribute23
2540 ,a.attribute24
2541 ,a.attribute25
2542 ,a.attribute26
2543 ,a.attribute27
2544 ,a.attribute28
2545 ,a.attribute29
2546 ,a.attribute30
2547 ,a.bld_blk_info_type_id
2548 ,a.object_version_number
2549 ,'N' NEW
2550 ,'N' CHANGED
2551 ,'N' PROCESS
2552 ,au.time_building_block_ovn BUILDING_BLOCK_OVN
2553 from hxc_bld_blk_info_types bbit,
2554 hxc_time_attribute_usages au,
2555 hxc_time_attributes a,
2556 hxc_time_building_blocks htbb
2557 where a.time_attribute_id = au.time_attribute_id
2558 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
2559 and au.time_building_block_id = htbb.time_building_block_id
2560 and au.time_building_block_ovn = htbb.object_version_number
2561 and htbb.scope = 'DETAIL'
2562 and htbb.time_building_block_id = detail_id
2563 and htbb.object_version_number = detail_ovn
2564 and htbb.resource_id = l_resource_id;
2565
2566
2567 l_timecard_block HXC_BLOCK_TABLE_TYPE;
2568 l_day_block HXC_BLOCK_TABLE_TYPE;
2569 l_detail_block HXC_BLOCK_TABLE_TYPE;
2570 l_detail_attribute HXC_ATTRIBUTE_TABLE_TYPE;
2571
2572 l_alias_block HXC_BLOCK_TABLE_TYPE;
2573
2574 l_index NUMBER := 1;
2575 l_att_index NUMBER := 1;
2576
2577 BEGIN
2578
2579
2580 l_timecard_block := HXC_BLOCK_TABLE_TYPE ();
2581 l_day_block := HXC_BLOCK_TABLE_TYPE ();
2582 l_detail_block := HXC_BLOCK_TABLE_TYPE ();
2583 l_detail_attribute := HXC_ATTRIBUTE_TABLE_TYPE();
2584
2585 l_alias_block := HXC_BLOCK_TABLE_TYPE ();
2586
2587 p_blocks := HXC_BLOCK_TABLE_TYPE ();
2588 p_attributes := HXC_ATTRIBUTE_TABLE_TYPE();
2589
2590 --l_index := l_block.first;
2591 --l_att_index := l_attribute.first;
2592
2593 FOR c_timecard in crs_timecard LOOP
2594
2595 -- reset all the indexes
2596 -- and temporary table
2597 l_timecard_block.delete;
2598 --l_attribute.delete;
2599 --l_index := 1;
2600 --l_att_index := 1;
2601
2602 l_timecard_block.extend;
2603 l_index := l_timecard_block.last;
2604 l_timecard_block(l_index) :=
2605 hxc_block_type (
2606 c_timecard.TIME_BUILDING_BLOCK_ID,
2607 c_timecard.TYPE,
2608 c_timecard.MEASURE,
2609 c_timecard.UNIT_OF_MEASURE,
2610 fnd_date.date_to_canonical(c_timecard.START_TIME),
2611 fnd_date.date_to_canonical(c_timecard.STOP_TIME),
2612 c_timecard.PARENT_BUILDING_BLOCK_ID,
2613 c_timecard.PARENT_IS_NEW,
2614 c_timecard.SCOPE,
2615 c_timecard.OBJECT_VERSION_NUMBER,
2616 c_timecard.APPROVAL_STATUS,
2617 c_timecard.RESOURCE_ID,
2618 c_timecard.RESOURCE_TYPE,
2619 c_timecard.APPROVAL_STYLE_ID,
2620 fnd_date.date_to_canonical(c_timecard.DATE_FROM),
2621 fnd_date.date_to_canonical(c_timecard.DATE_TO),
2622 c_timecard.COMMENT_TEXT,
2623 c_timecard.PARENT_BUILDING_BLOCK_OVN,
2624 c_timecard.NEW,
2625 c_timecard.CHANGED,
2626 c_timecard.PROCESS,
2627 c_timecard.APPLICATION_SET_ID,
2628 c_timecard.TRANSLATION_DISPLAY_KEY
2629 );
2630
2631 -- now we have a timecard block to work with
2632 -- we need to find the day attached
2633 FOR c_day_info in crs_day_info
2634 (c_timecard.resource_id
2635 ,c_timecard.TIME_BUILDING_BLOCK_ID
2636 ,c_timecard.OBJECT_VERSION_NUMBER) LOOP
2637
2638 -- we are on a new day so we are deleting
2639 -- the table
2640 l_day_block.delete;
2641
2642 l_day_block.extend;
2643 l_index := l_day_block.last;
2644 l_day_block(l_index) :=
2645 hxc_block_type (
2646 c_day_info.TIME_BUILDING_BLOCK_ID,
2647 c_day_info.TYPE,
2648 c_day_info.MEASURE,
2649 c_day_info.UNIT_OF_MEASURE,
2650 fnd_date.date_to_canonical(c_day_info.START_TIME),
2651 fnd_date.date_to_canonical(c_day_info.STOP_TIME),
2652 c_day_info.PARENT_BUILDING_BLOCK_ID,
2653 c_day_info.PARENT_IS_NEW,
2654 c_day_info.SCOPE,
2655 c_day_info.OBJECT_VERSION_NUMBER,
2656 c_day_info.APPROVAL_STATUS,
2657 c_day_info.RESOURCE_ID,
2658 c_day_info.RESOURCE_TYPE,
2659 c_day_info.APPROVAL_STYLE_ID,
2660 fnd_date.date_to_canonical(c_day_info.DATE_FROM),
2661 fnd_date.date_to_canonical(c_day_info.DATE_TO),
2662 c_day_info.COMMENT_TEXT,
2663 c_day_info.PARENT_BUILDING_BLOCK_OVN,
2664 c_day_info.NEW,
2665 c_day_info.CHANGED,
2666 c_day_info.PROCESS,
2667 c_day_info.APPLICATION_SET_ID,
2668 c_day_info.TRANSLATION_DISPLAY_KEY
2669 );
2670
2671
2672 -- now we have a timecard block to work with
2673 -- we need to find the day attached
2674 FOR c_detail_info in crs_detail_info(c_timecard.resource_id
2675 ,c_day_info.TIME_BUILDING_BLOCK_ID
2676 ,c_day_info.OBJECT_VERSION_NUMBER) LOOP
2677
2678
2679 l_detail_block.delete;
2680
2681 l_detail_block.extend;
2682 l_index := l_detail_block.last;
2683 l_detail_block(l_index) :=
2684 hxc_block_type (
2685 c_detail_info.TIME_BUILDING_BLOCK_ID,
2686 c_detail_info.TYPE,
2687 c_detail_info.MEASURE,
2688 c_detail_info.UNIT_OF_MEASURE,
2689 fnd_date.date_to_canonical(c_detail_info.START_TIME),
2690 fnd_date.date_to_canonical(c_detail_info.STOP_TIME),
2691 c_detail_info.PARENT_BUILDING_BLOCK_ID,
2692 c_detail_info.PARENT_IS_NEW,
2693 c_detail_info.SCOPE,
2694 c_detail_info.OBJECT_VERSION_NUMBER,
2695 c_detail_info.APPROVAL_STATUS,
2696 c_detail_info.RESOURCE_ID,
2697 c_detail_info.RESOURCE_TYPE,
2698 c_detail_info.APPROVAL_STYLE_ID,
2699 fnd_date.date_to_canonical(c_detail_info.DATE_FROM),
2700 fnd_date.date_to_canonical(c_detail_info.DATE_TO),
2701 c_detail_info.COMMENT_TEXT,
2702 c_detail_info.PARENT_BUILDING_BLOCK_OVN,
2703 c_detail_info.NEW,
2704 c_detail_info.CHANGED,
2705 c_detail_info.PROCESS,
2706 c_detail_info.APPLICATION_SET_ID,
2707 c_detail_info.TRANSLATION_DISPLAY_KEY);
2708
2709 -- now we are populating the attribute of this detail
2710 l_detail_attribute.delete;
2711 FOR c_detail_attribute in crs_detail_attribute
2712 (c_detail_info.TIME_BUILDING_BLOCK_ID
2713 ,c_detail_info.OBJECT_VERSION_NUMBER
2714 ,c_detail_info.resource_id) LOOP
2715
2716 l_detail_attribute.extend;
2717 l_att_index := l_detail_attribute.last;
2718 l_detail_attribute(l_att_index) :=
2719 hxc_attribute_type (
2720 c_detail_attribute.time_attribute_id,
2721 c_detail_attribute.time_building_block_id,
2722 c_detail_attribute.attribute_category,
2723 c_detail_attribute.attribute1,
2724 c_detail_attribute.attribute2,
2725 c_detail_attribute.attribute3,
2726 c_detail_attribute.attribute4,
2727 c_detail_attribute.attribute5,
2728 c_detail_attribute.attribute6,
2729 c_detail_attribute.attribute7,
2730 c_detail_attribute.attribute8,
2731 c_detail_attribute.attribute9,
2732 c_detail_attribute.attribute10,
2733 c_detail_attribute.attribute11,
2734 c_detail_attribute.attribute12,
2735 c_detail_attribute.attribute13,
2736 c_detail_attribute.attribute14,
2737 c_detail_attribute.attribute15,
2738 c_detail_attribute.attribute16,
2739 c_detail_attribute.attribute17,
2740 c_detail_attribute.attribute18,
2741 c_detail_attribute.attribute19,
2742 c_detail_attribute.attribute20,
2743 c_detail_attribute.attribute21,
2744 c_detail_attribute.attribute22,
2745 c_detail_attribute.attribute23,
2746 c_detail_attribute.attribute24,
2747 c_detail_attribute.attribute25,
2748 c_detail_attribute.attribute26,
2749 c_detail_attribute.attribute27,
2750 c_detail_attribute.attribute28,
2751 c_detail_attribute.attribute29,
2752 c_detail_attribute.attribute30,
2753 c_detail_attribute.bld_blk_info_type_id,
2754 c_detail_attribute.object_version_number,
2755 c_detail_attribute.NEW,
2756 c_detail_attribute.CHANGED,
2757 c_detail_attribute.bld_blk_info_type,
2758 c_detail_attribute.PROCESS,
2759 c_detail_attribute.BUILDING_BLOCK_OVN);
2760
2761 END LOOP;
2762
2763 -- before the next detail we are first calling the translator
2764
2765 -- we build the block table to send to the translator
2766 -- only if we have an attribute
2767 IF l_detail_attribute.count <> 0 THEN
2768
2769 l_alias_block.delete;
2770
2771 l_alias_block := l_timecard_block;
2772
2773 l_index := l_day_block.first;
2774 LOOP
2775 EXIT WHEN
2776 (NOT l_day_block.exists(l_index));
2777
2778 l_alias_block.extend;
2779 l_alias_block(l_alias_block.last) := l_day_block(l_index);
2780
2781 l_index := l_day_block.next(l_index);
2782
2783 END LOOP;
2784
2785 l_index := l_detail_block.first;
2786 LOOP
2787 EXIT WHEN
2788 (NOT l_detail_block.exists(l_index));
2789
2790 l_alias_block.extend;
2791 l_alias_block(l_alias_block.last) := l_detail_block(l_index);
2792
2793 l_index := l_detail_block.next(l_index);
2794
2795 END LOOP;
2796
2797
2798 hxc_alias_translator.do_retrieval_translation
2799 (p_attributes => l_detail_attribute
2800 ,p_blocks => l_alias_block
2801 ,p_start_time => c_timecard.start_time
2802 ,p_stop_time => c_timecard.stop_time
2803 ,p_resource_id => c_timecard.resource_id
2804 ,p_processing_mode => hxc_alias_utility.c_ss_processing
2805 ,p_add_alias_display_value => true
2806 ,p_messages => p_messages
2807 );
2808
2809 END IF;
2810
2811
2812 -- we need to append the
2813 -- block and attribute table that we want to return
2814 IF p_attributes.count = 0 THEN
2815 p_attributes := l_detail_attribute;
2816 ELSE
2817 l_index := l_detail_attribute.first;
2818 LOOP
2819 EXIT WHEN
2820 (NOT l_detail_attribute.exists(l_index));
2821
2822 p_attributes.extend;
2823 p_attributes(p_attributes.last) := l_detail_attribute(l_index);
2824
2825 l_index := l_detail_attribute.next(l_index);
2826
2827 END LOOP;
2828 END IF;
2829
2830 -- populate the detail info
2831 IF p_blocks.count = 0 THEN
2832 p_blocks := l_detail_block;
2833 ELSE
2834 l_index := l_detail_block.first;
2835 LOOP
2836 EXIT WHEN
2837 (NOT l_detail_block.exists(l_index));
2838
2839 p_blocks.extend;
2840 p_blocks(p_blocks.last) := l_detail_block(l_index);
2841
2842 l_index := l_detail_block.next(l_index);
2843
2844 END LOOP;
2845 END IF;
2846
2847 END LOOP; -- detail
2848
2849 -- populate the day info
2850 IF p_blocks.count = 0 THEN
2851 p_blocks := l_day_block;
2852 ELSE
2853 l_index := l_day_block.first;
2854 LOOP
2855 EXIT WHEN
2856 (NOT l_day_block.exists(l_index));
2857
2858 p_blocks.extend;
2859 p_blocks(p_blocks.last) := l_day_block(l_index);
2860
2861 l_index := l_day_block.next(l_index);
2862
2863 END LOOP;
2864 END IF;
2865
2866 END LOOP; -- day
2867
2868 -- populate the day info
2869 IF p_blocks.count = 0 THEN
2870 p_blocks := l_timecard_block;
2871 ELSE
2872 l_index := l_timecard_block.first;
2873 LOOP
2874 EXIT WHEN
2875 (NOT l_timecard_block.exists(l_index));
2876
2877 p_blocks.extend;
2878 p_blocks(p_blocks.last) := l_timecard_block(l_index);
2879
2880 l_index := l_timecard_block.next(l_index);
2881
2882 END LOOP;
2883 END IF;
2884
2885 END LOOP; -- timecard
2886
2887 END cla_summary_alias_translation;
2888
2889
2890 -- Added for Bug 13416941
2891
2892 PROCEDURE copy_attachments_to_ap (
2893 p_resource_id IN VARCHAR2,
2894 p_tc_start_date IN VARCHAR2,
2895 p_tc_end_date IN VARCHAR2
2896 ) is
2897
2898 CURSOR c_get_tc_attahcments_for_ap(c_tc_start_date date,
2899 c_tc_end_date date)
2900 IS
2901 SELECT distinct
2902 to_char(app.resource_id) app_resource_id
2903 , regexp_replace(fnd_date.date_to_canonical(trunc(app.start_time)),' .*') app_start_date
2904 , regexp_replace(fnd_date.date_to_canonical(trunc(app.stop_time)),' .*') app_end_date
2905 , fad.entity_name
2906 , fad.pk1_value
2907 , fad.pk2_value
2908 , fad.pk3_value
2909 , fad.created_by
2910 , fad.last_update_login
2911 , fad.program_application_id
2912 , fad.program_id
2913 , fad.request_id
2914 , fad.automatically_added_flag
2915 , fad.category_id
2916 FROM hxc_time_building_blocks app
2917 ,hxc_tc_ap_links htal
2918 ,hxc_timecard_summary hts
2919 ,fnd_attached_documents fad
2920 WHERE app.scope = 'APPLICATION_PERIOD'
2921 AND app.date_to = hr_general.end_of_time
2922 AND app.resource_id = to_number(p_resource_id)
2923 AND trunc(app.start_time) < c_tc_end_date
2924 AND trunc(app.stop_time) > c_tc_start_date
2925 AND trunc(app.start_time) <> c_tc_start_date
2926 AND trunc(app.stop_time) <> c_tc_end_date
2927 AND app.time_building_block_id = htal.application_period_id
2928 AND htal.timecard_id = hts.timecard_id
2929 AND hts.resource_id = to_number(fad.pk1_value)
2930 AND regexp_replace(fnd_date.date_to_canonical(trunc(hts.start_time)),' .*') = fad.pk2_value
2931 AND regexp_replace(fnd_date.date_to_canonical(trunc(hts.stop_time)),' .*') = fad.pk3_value
2932 AND fad.entity_name = 'HXC_TIMECARD_SUMMARY'
2933 ;
2934
2935 TYPE num_tab IS TABLE OF NUMBER;
2936 TYPE varchar_tab IS TABLE OF VARCHAR2(200);
2937 TYPE date_tab IS TABLE OF DATE;
2938
2939 l_app_resource_id_tab varchar_tab;
2940 l_app_start_date_tab varchar_tab;
2941 l_app_end_date_tab varchar_tab;
2942 l_to_entity_name_tab varchar_tab;
2943
2944 l_from_created_by_tab num_tab;
2945 l_from_last_update_login_tab num_tab;
2946 l_from_entity_name_tab varchar_tab;
2947 l_from_pk1_value_tab varchar_tab;
2948 l_from_pk2_value_tab varchar_tab;
2949 l_from_pk3_value_tab varchar_tab;
2950 l_from_program_app_id_tab num_tab;
2951 l_from_program_id_tab num_tab;
2952 l_from_request_id_tab num_tab;
2953 l_automatically_added_flag_tab varchar_tab;
2954 l_from_category_id_tab num_tab;
2955
2956
2957
2958 BEGIN
2959
2960 IF g_debug
2961 THEN
2962 hr_utility.trace('In hxc_timecard_utilities.copy_attachments_to_ap');
2963 hr_utility.trace('Timecard Attachments Enabled ::'||
2964 NVL(fnd_profile.value('HXC_TIMECARD_ATTACHMENT_ENABLED'), 'N'));
2965 END IF;
2966
2967 IF (NVL(fnd_profile.value('HXC_TIMECARD_ATTACHMENT_ENABLED'), 'N') = 'N') THEN
2968 RETURN;
2969 END IF;
2970
2971
2972 OPEN c_get_tc_attahcments_for_ap(FND_DATE.CANONICAL_TO_DATE(p_tc_start_date),
2973 FND_DATE.CANONICAL_TO_DATE(p_tc_end_date)
2974 );
2975 FETCH c_get_tc_attahcments_for_ap BULK COLLECT
2976 INTO l_app_resource_id_tab
2977 , l_app_start_date_tab
2978 , l_app_end_date_tab
2979 , l_from_entity_name_tab
2980 , l_from_pk1_value_tab
2981 , l_from_pk2_value_tab
2982 , l_from_pk3_value_tab
2983 , l_from_created_by_tab
2984 , l_from_last_update_login_tab
2985 , l_from_program_app_id_tab
2986 , l_from_program_id_tab
2987 , l_from_request_id_tab
2988 , l_automatically_added_flag_tab
2989 , l_from_category_id_tab;
2990 CLOSE c_get_tc_attahcments_for_ap;
2991
2992 IF g_debug
2993 THEN
2994 hr_utility.trace('l_app_resource_id_tab.COUNT ::'||l_app_resource_id_tab.COUNT);
2995 END IF;
2996
2997 IF l_app_resource_id_tab.COUNT >0
2998 THEN
2999
3000 IF g_debug
3001 THEN
3002 hr_utility.trace('Approval Resource Id ::'||l_app_resource_id_tab(l_app_resource_id_tab.FIRST));
3003 hr_utility.trace('Approval Start Date ::'||l_app_start_date_tab(l_app_resource_id_tab.FIRST));
3004 hr_utility.trace('Approval End Date ::'||l_app_end_date_tab(l_app_resource_id_tab.FIRST));
3005 hr_utility.trace('call to delete approval period attachments...fnd_attached_documents2_pkg.delete_attachments');
3006 END IF;
3007
3008 fnd_attached_documents2_pkg.delete_attachments
3009 ( X_entity_name => 'HXC_TIMECARD_SUMMARY'
3010 , X_pk1_value => l_app_resource_id_tab(l_app_resource_id_tab.FIRST)
3011 , X_pk2_value => l_app_start_date_tab(l_app_resource_id_tab.FIRST)
3012 , X_pk3_value => l_app_end_date_tab(l_app_resource_id_tab.FIRST)
3013 , X_delete_document_flag => 'Y'
3014 );
3015
3016 IF g_debug
3017 THEN
3018 hr_utility.trace('call to copy timecard attachments to approval period');
3019 END IF;
3020
3021 l_to_entity_name_tab := l_from_entity_name_tab;
3022 FOR i IN l_app_resource_id_tab.FIRST..l_app_resource_id_tab.LAST
3023 Loop
3024 fnd_attached_documents2_pkg.copy_attachments(
3025 X_from_entity_name => l_from_entity_name_tab(i),
3026 X_from_pk1_value => l_from_pk1_value_tab(i),
3027 X_from_pk2_value => l_from_pk2_value_tab(i),
3028 X_from_pk3_value => l_from_pk3_value_tab(i),
3029 X_to_entity_name => l_to_entity_name_tab(i),
3030 X_to_pk1_value => l_app_resource_id_tab(i),
3031 X_to_pk2_value => l_app_start_date_tab(i),
3032 X_to_pk3_value => l_app_end_date_tab(i),
3033 X_created_by => l_from_created_by_tab(i),
3034 X_last_update_login => l_from_last_update_login_tab(i),
3035 X_program_application_id => l_from_program_app_id_tab(i),
3036 X_program_id => l_from_program_id_tab(i),
3037 X_request_id => l_from_request_id_tab(i),
3038 X_automatically_added_flag => l_automatically_added_flag_tab(i),
3039 X_from_category_id => l_from_category_id_tab(i),
3040 X_to_category_id => l_from_category_id_tab(i)
3041 );
3042 END LOOP;
3043
3044 END IF; --IF l_app_resource_id_tab.COUNT >0
3045
3046 END copy_attachments_to_ap;
3047
3048
3049
3050 END hxc_timecard_utilities;