[Home] [Help]
PACKAGE BODY: APPS.HXC_TPD_END
Source
1 PACKAGE BODY hxc_tpd_end AS
2 /* $Header: hxcendtp.pkb 120.8.12010000.3 2008/08/05 12:01:44 ubhat ship $ */
3
4 -- g_time_periods time_periods_table;
5 g_debug BOOLEAN := hr_utility.debug_enabled;
6
7 -- This change is in accordance to the changes in the report query
8 -- Q_Resource_Pref_Eval.
9 -- Since Application is one of the Sort Option, Application_Id needs to be
10 -- fetched from the query.
11 -- This function is called from the query to fetch the Application_Id by call
12 -- to Preference_Evaluation package.
13 FUNCTION appl_id (p_person_id IN NUMBER)
14 RETURN NUMBER
15 IS
16 l_appl_id NUMBER;
17 l_message VARCHAR2 (80);
18 BEGIN
19 l_appl_id :=
20 hxc_preference_evaluation.resource_pref_errcode
21 (p_person_id,
22 'TS_PER_APPLICATION_SET|1|',
23 l_message,
24 SYSDATE
25 );
26 RETURN (l_appl_id);
27 EXCEPTION
28 WHEN OTHERS
29 THEN
30 RETURN (NULL);
31 END;
32
33 --
34 -- ----------------------------------------------------------------------------
35 -- |-----------------------< get_supervisor_name >----------------------------|
36 -- ----------------------------------------------------------------------------
37 --
38 FUNCTION get_supervisor_name (
39 p_supervisor_id IN NUMBER,
40 p_effective_date IN DATE
41 )
42 RETURN VARCHAR2
43 IS
44 CURSOR c_supervisor_name (
45 cp_supervisor_id IN NUMBER,
46 cp_effective_date IN DATE
47 )
48 IS
49 SELECT ppf.full_name
50 FROM per_people_f ppf
51 WHERE ppf.person_id = cp_supervisor_id
52 AND cp_effective_date BETWEEN ppf.effective_start_date
53 AND ppf.effective_end_date;
54
55 supervisor_name per_people_f.full_name%TYPE;
56 BEGIN
57 OPEN c_supervisor_name (p_supervisor_id, p_effective_date);
58
59 FETCH c_supervisor_name
60 INTO supervisor_name;
61
62 CLOSE c_supervisor_name;
63
64 RETURN (supervisor_name);
65 END;
66
67 --
68 -- ----------------------------------------------------------------------------
69 -- |---------------------< populate_missing_time_periods >--------------------|
70 -- ----------------------------------------------------------------------------
71
72 -- An addition parameter p_assignment_id is included in this function.
73 -- This parameter is needed since a resource will have different assignments for
74 -- diff range of period mainly because of Hire-Terminate-ReHire action.
75 -- In such case, assignment_id valid for that date range is imp to display only
76 -- those time_periods valid for that range of time.
77 FUNCTION populate_missing_time_periods (
78 p_resource_id IN NUMBER,
79 p_assignment_id IN NUMBER,
80 p_start_date IN DATE,
81 p_end_date IN DATE
82 )
83 RETURN NUMBER
84 IS
85 p_period_end DATE;
86 p_period_start DATE;
87 l_recurring_period_id NUMBER (11);
88 l_number_per_fiscal_year NUMBER (15);
89 l_start_date DATE;
90 l_end_date DATE;
91 l_period_end_date DATE;
92 l_period_type VARCHAR2 (30);
93 l_duration_in_days NUMBER (10);
94 l_next_index BINARY_INTEGER := 0;
95 lv_end_date DATE := p_end_date;
96 lv_exists VARCHAR2 (6) := NULL;
97 l_add_to_start_date NUMBER (2);
98 l_pref_tc_period VARCHAR2 (80);
99 -- Bug 2900824 and 2801769
100 l_p_start_date DATE;
101 l_p_end_date DATE;
102
103 CURSOR c_period_exists (
104 cp_resource_id IN NUMBER,
105 cp_period_start IN DATE,
106 cp_period_end IN DATE
107 )
108 IS
109 SELECT htb.start_time, htb.stop_time
110 FROM hxc_timecard_summary htb
111 WHERE htb.resource_id = cp_resource_id
112 AND cp_period_start <= htb.stop_time
113 AND cp_period_end >= htb.start_time;
114
115 CURSOR c_period_exists_chk (
116 cp_resource_id IN NUMBER,
117 cp_period_start IN DATE,
118 cp_period_end IN DATE
119 )
120 IS
121 SELECT 'x'
122 FROM DUAL
123 WHERE EXISTS (
124 SELECT 'x'
125 FROM hxc_timecard_summary htb
126 WHERE htb.resource_id = cp_resource_id
127 AND cp_period_start <= htb.stop_time
128 AND cp_period_end >= htb.start_time);
129
130 p_message VARCHAR2 (30);
131 lv_start_time hxc_time_building_blocks.start_time%TYPE;
132 lv_stop_time hxc_time_building_blocks.stop_time%TYPE;
133 ld_period_start_date DATE;
134 ld_period_end_date DATE;
135 lv_row_found VARCHAR2 (1) := 'N';
136 lv_exists1 VARCHAR2 (1);
137 l_temp_periods hxc_period_evaluation.period_list;
138 l_index NUMBER;
139 l_period_list hxc_period_evaluation.period_list;
140 i BINARY_INTEGER := 1;
141
142 Function check_valid_period (p_resource_id IN NUMBER,
143 p_start_date IN DATE,
144 p_end_date IN DATE
145 )
146 RETURN BOOLEAN
147 IS
148 l_cnt NUMBER;
149 BEGIN
150
151 -- Bug: 5971387 - The following query has been modified to drive the person_type_id
152 -- thru the per_person_type_usages table as opposed to per_person_types table.
153
154 SELECT count(*)
155 INTO l_cnt
156 FROM per_person_types ppt,
157 per_person_type_usages_f ptu,
158 per_people_f per
159 WHERE per.person_id = p_resource_id
160 AND ptu.person_id = per.person_id
161 AND ptu.person_type_id = ppt.person_type_id
162 -- AND ppt.system_person_type NOT IN ('EMP', 'EMP_APL', 'CWK') -- Bug 6486974
163 -- AND per.effective_start_date <= p_end_date
164 -- AND per.effective_end_date >= p_start_date;
165 AND ppt.system_person_type IN ('EMP', 'EMP_APL', 'CWK')
166 AND ( p_end_date between per.effective_start_date
167 and per.effective_end_date
168 OR p_start_date between per.effective_start_date
169 and per.effective_end_date );
170
171
172 IF l_cnt > 0 THEN
173
174
175 RETURN TRUE;
176 ELSE
177 RETURN FALSE;
178 END IF;
179 END check_valid_period;
180
181 BEGIN
182 g_debug := hr_utility.debug_enabled;
183
184 IF g_debug
185 THEN
186 hr_utility.TRACE ('Inside populate missing timecard function');
187 hr_utility.set_location ('populate_missing_time_periods', 10);
188 END IF;
189
190 IF g_time_periods.COUNT > 0
191 THEN
192 IF g_debug
193 THEN
194 hr_utility.set_location ('populate_missing_time_periods', 20);
195 END IF;
196
197 -- Following piece of code not needed since g_time_periods.delete is sufficient.
198
199 -- FOR i in g_time_periods.first .. g_time_periods.last LOOP
200 -- g_time_periods(i).start_time := null;
201 -- g_time_periods(i).stop_time := null;
202 -- g_time_periods(i).resource_id := null;
203 -- END LOOP;
204 IF g_debug
205 THEN
206 hr_utility.TRACE ('Deleted PL/SQL table');
207 END IF;
208
209 g_time_periods.DELETE;
210 END IF;
211
212 IF g_debug
213 THEN
214 hr_utility.set_location ('populate_missing_time_periods', 25);
215 hr_utility.TRACE ( 'p_start_date:'
216 || TO_CHAR (p_start_date, 'DD-MON-YYYY HH24:MI:SS')
217 );
218 hr_utility.TRACE ( 'p_end_date :'
219 || TO_CHAR (p_end_date, 'DD-MON-YYYY HH24:MI:SS')
220 );
221 hr_utility.TRACE ('p_resource_id :' || p_resource_id);
222 END IF;
223
224 l_p_start_date := p_start_date;
225 l_p_end_date := p_end_date;
226
227 BEGIN
228 -- Incase Hiredate is between p_date_from and p_date_to,
229 -- then set starting date range as Hire date instead of p_start_from.
230
231 -- Also, here we need to consider assignment start and end date
232 -- for cases of Hire - Terminate - Re Hire where assignments are diff
233 -- before/after termination and re-hire.
234 /*
235 select min(ppf.effective_start_date), max(ppf.effective_end_date)
236 into l_p_start_date, l_p_end_date
237 from per_people_f ppf, per_assignments_f paf
238 where ppf.person_id = p_resource_id
239 and paf.person_id = ppf.person_id
240 and paf.assignment_id = p_assignment_id
241 and paf.effective_start_date between ppf.effective_start_date
242 and ppf.effective_end_date;
243 */
244
245 /* select min(ppf.effective_start_date), max(ppf.effective_end_date)
246 into l_p_start_date, l_p_end_date
247 from per_people_f ppf,
248 per_assignments_f paf,
249 per_person_types ppt,
250 per_person_type_usages_f ptu
251 where ppf.person_id = p_resource_id
252 and paf.person_id = ppf.person_id
253 and ptu.person_id = ppf.person_id
254 and ptu.person_type_id=ppt.person_type_id
255 and ppt.system_person_type in ('EMP','EMP_APL','CWK')
256 and paf.assignment_id = p_assignment_id
257 and paf.effective_start_date between ppf.effective_start_date
258 and ppf.effective_end_date
259 and ppf.effective_start_date between ptu.effective_start_date
260 and ptu.effective_end_date;
261 */
262 /*
263 SELECT
264 min(per.effective_start_date),
265 max(per.effective_end_date)
266 INTO
267 l_p_start_date, l_p_end_date
268 FROM
269 per_person_types ppt,
270 per_people_f per
271 WHERE
272 per.person_id = p_resource_id
273 AND ppt.person_type_id = per.person_type_id
274 AND ppt.system_person_type in ('EMP','EMP_APL','CWK')
275 AND per.effective_start_date =
276 (select min(perMin.effective_start_date)
277 from per_people_f perMin
278 where perMin.person_id = per.person_id
279 AND perMin.effective_start_date <= p_end_date
280 AND perMin.effective_end_date >= p_start_date)
281 AND EXISTS ( SELECT 'x'
282 FROM per_assignment_status_types ast,
283 per_assignments_f asm
284 WHERE asm.person_id = per.person_id
285 AND asm.primary_flag = 'Y'
286 AND
287 (
288 asm.effective_start_date <= per.effective_end_date
289 AND
290 asm.effective_end_date >= per.effective_start_date
291 )
292 AND ast.assignment_status_type_id
293 = asm.assignment_status_type_id
294 AND ast.pay_system_status = 'P' );
295 */
296
297 -- The above query changed as follows for bug 4687842. Basically the query has
298 -- been changed to drive the person_type_id thru the per_person_type_usages
299 -- table as opposed to per_person_types table as is the case in the above query
300 -- which causes bug 4687842.
301 SELECT MIN (per.effective_start_date), MAX (per.effective_end_date)
302 INTO l_p_start_date, l_p_end_date
303 FROM per_person_type_usages_f ptu,
304 per_person_types ppt,
305 per_people_f per
306 WHERE per.person_id = p_resource_id
307 AND ptu.person_id = per.person_id
308 AND ptu.person_type_id = ppt.person_type_id
309 AND ppt.system_person_type IN ('EMP', 'EMP_APL', 'CWK')
310 AND (per.effective_start_date =
311 (SELECT MIN (permin.effective_start_date)
312 FROM per_people_f permin
313 WHERE permin.person_id = per.person_id
314 AND permin.effective_start_date <= p_end_date
315 AND permin.effective_end_date >= p_start_date)
316 OR
317 per.effective_start_date =
318 (SELECT MAX(permin.effective_start_date)
319 FROM per_people_f permin
320 WHERE permin.person_id = per.person_id
321 AND permin.effective_start_date <= p_end_date
322 AND permin.effective_end_date >= p_start_date)
323 )
324 AND EXISTS (
325 SELECT 'x'
326 FROM per_assignment_status_types ast,
327 per_assignments_f asm
328 WHERE asm.person_id = per.person_id
329 AND asm.primary_flag = 'Y'
330 AND ( asm.effective_start_date <=
331 per.effective_end_date
332 AND asm.effective_end_date >=
333 per.effective_start_date
334 )
335 AND ast.assignment_status_type_id =
336 asm.assignment_status_type_id
337 AND NVL (ast.pay_system_status, 'P') =
338 DECODE (ast.per_system_status,
339 'ACTIVE_CWK', 'D',
340 'P'
341 ));
342
343 IF g_debug
344 THEN
345 hr_utility.TRACE ( 'l_p_start_date:'
346 || TO_CHAR (l_p_start_date,
347 'dd-mon-yyyy hh24:mi:ss'
348 )
349 );
350 hr_utility.TRACE ( 'l_p_end_date:'
351 || TO_CHAR (l_p_end_date,
352 'dd-mon-yyyy hh24:mi:ss'
353 )
354 );
355 END IF;
356
357 IF (l_p_start_date IS NULL OR l_p_end_date IS NULL)
358 THEN
359 RETURN (0);
360 -- Incase HireDate or Termianation Date of the resource is not between
361 -- p_date_from and p_date_to,
362 -- then the input date range is valid.
363 ELSE
364 IF (l_p_start_date < p_start_date)
365 THEN
366 l_p_start_date := p_start_date;
367 END IF;
368
369 IF (l_p_end_date > p_end_date)
370 THEN
371 l_p_end_date := p_end_date;
372 ELSE
373 lv_end_date := l_p_end_date;
374 END IF;
375 END IF;
376 END;
377
378 IF g_debug
379 THEN
380 hr_utility.set_location ('populate_missing_time_periods', 26);
381 END IF;
382
383 IF g_debug
384 THEN
385 hr_utility.TRACE ( 'l_p_start_date:'
386 || TO_CHAR (l_p_start_date,
387 'DD-MON-YYYY HH24:MI:SS'
388 )
389 );
390 hr_utility.TRACE ( 'l_p_end_date :'
391 || TO_CHAR (l_p_end_date, 'DD-MON-YYYY HH24:MI:SS')
392 );
393 hr_utility.TRACE ( 'lv_end_date :'
394 || TO_CHAR (lv_end_date, 'DD-MON-YYYY HH24:MI:SS')
395 );
396 END IF;
397
398 /* FOR i in c_people(p_business_group_id,p_resource_id) LOOP
399 lv_resource_id := i.person_id;
400 */
401 l_pref_tc_period :=
402 hxc_preference_evaluation.resource_pref_errcode
403 (p_resource_id,
404 'TC_W_TCRD_PERIOD|1|',
405 p_message
406 );
407
408 IF g_debug
409 THEN
410 hr_utility.set_location ('populate_missing_time_periods', 30);
411 hr_utility.TRACE ('l_pref_tc_period = ' || l_pref_tc_period);
412 END IF;
413
414 SELECT hrp.recurring_period_id, hrp.start_date, hrp.end_date,
415 hrp.period_type, hrp.duration_in_days
416 INTO l_recurring_period_id, l_start_date, l_end_date,
417 l_period_type, l_duration_in_days
418 FROM hxc_recurring_periods hrp
419 WHERE hrp.recurring_period_id = l_pref_tc_period;
420
421 IF g_debug
422 THEN
423 hr_utility.TRACE ('l_recurring_period_id:' || l_recurring_period_id);
424 hr_utility.TRACE ( 'l_start_date :'
425 || TO_CHAR (l_start_date, 'DD-MON-YYYY HH24:MI:SS')
426 );
427 hr_utility.TRACE ( 'l_end_date :'
428 || TO_CHAR (l_end_date, 'DD-MON-YYYY HH24:MI:SS')
429 );
430 hr_utility.TRACE ('l_period_type :' || l_period_type);
431 hr_utility.TRACE ('l_duration_in_days :' || l_duration_in_days);
432 hr_utility.set_location ('populate_missing_time_periods', 50);
433 END IF;
434
435 IF l_end_date IS NULL
436 THEN
437 l_end_date := hr_general.end_of_time;
438 --to_date('31/12/4712','DD/MM/YYYY');
439 END IF;
440
441 /* IF lv_end_date < l_start_date
442 OR lv_end_date > l_end_date THEN
443
444 p_error_message := 'The Timecard does not belong to this Period';
445
446 RETURN;
447 END IF;
448 */
449 IF g_debug
450 THEN
451 hr_utility.set_location ('populate_missing_time_periods', 60);
452 END IF;
453
454 l_temp_periods :=
455 hxc_period_evaluation.get_period_list
456 (p_current_date => SYSDATE,
457 p_recurring_period_type => l_period_type,
458 p_duration_in_days => l_duration_in_days,
459 p_rec_period_start_date => l_start_date,
460 p_max_date_in_futur => p_end_date,
461 p_max_date_in_past => p_start_date - 1
462 );
463
464 IF g_debug
465 THEN
466 hr_utility.TRACE ('l_temp_periods.count ' || l_temp_periods.COUNT);
467 END IF;
468
469 -- Since the l_temp_periods pl sql table index is not in sequence,
470 -- move these periods to l_period_list pl sql table with sequenced index
471 IF l_temp_periods.COUNT > 0
472 THEN
473 l_index := l_temp_periods.FIRST;
474
475 LOOP
476 IF g_debug
477 THEN
478 hr_utility.set_location ('populate_missing_time_periods', 65);
479 END IF;
480
481 EXIT WHEN NOT l_temp_periods.EXISTS (l_index);
482 l_period_list (i).start_date :=
483 l_temp_periods (l_index).start_date;
484 l_period_list (i).end_date := l_temp_periods (l_index).end_date;
485 l_index := l_temp_periods.NEXT (l_index);
486 i := i + 1;
487 END LOOP;
488 END IF;
489
490 IF g_debug
491 THEN
492 hr_utility.TRACE ('FYI');
493 END IF;
494
495 IF l_period_list.COUNT <> 0
496 THEN
497 IF g_debug
498 THEN
499 hr_utility.set_location ('populate_missing_time_periods', 70);
500 END IF;
501
502 FOR l_cnt IN l_period_list.FIRST .. l_period_list.LAST
503 LOOP
504 IF g_debug
505 THEN
506 hr_utility.TRACE ( 'l_period_list.start_date is:'
507 || TO_CHAR (l_period_list (l_cnt).start_date,
508 'DD-MON-YYYY'
509 )
510 );
511 hr_utility.TRACE ( 'l_period_list.end_date is:'
512 || TO_CHAR (l_period_list (l_cnt).end_date,
513 'DD-MON-YYYY'
514 )
515 );
516 END IF;
517 END LOOP;
518
519 IF g_debug
520 THEN
521 hr_utility.set_location ('populate_missing_time_periods', 75);
522 END IF;
523 END IF;
524
525 --------------------------------------------------------------------------------
526 IF g_debug
527 THEN
528 hr_utility.set_location ('populate_missing_time_periods', 80);
529 END IF;
530
531 IF l_period_list.COUNT <> 0 THEN /* Bug: 5484502 */
532
533 FOR i IN REVERSE l_period_list.FIRST .. l_period_list.LAST
534 LOOP
535 IF g_debug
536 THEN
537 hr_utility.set_location ('populate_missing_time_periods', 84);
538 hr_utility.TRACE ('i :' || i);
539 END IF;
540
541 EXIT WHEN NOT l_period_list.EXISTS (i);
542
543 IF g_debug
544 THEN
545 hr_utility.set_location ('populate_missing_time_periods', 85);
546 END IF;
547
548 l_start_date := l_period_list (i).start_date;
549 l_period_end_date := l_period_list (i).end_date;
550
551 IF( l_start_date < l_p_start_date)
552 THEN
553 l_start_date := l_p_start_date;
554 END IF;
555
556 IF g_debug
557 THEN
558 hr_utility.TRACE ( 'lv_end_date = '
559 || TO_CHAR (lv_end_date,
560 'DD-MON-YYYY HH24:MI:SS'
561 )
562 );
563 hr_utility.TRACE ( 'l_start_date = '
564 || TO_CHAR (l_start_date,
565 'DD-MON-YYYY HH24:MI:SS'
566 )
567 );
568 hr_utility.TRACE ( 'l_period_end_date = '
569 || TO_CHAR (l_period_end_date,
570 'DD-MON-YYYY HH24:MI:SS'
571 )
572 );
573 END IF;
574
575 IF lv_end_date >= l_start_date AND lv_end_date <= l_period_end_date
576 THEN
577 IF g_debug
578 THEN
579 hr_utility.set_location ('populate_missing_time_periods', 90);
580 hr_utility.TRACE ('p_resource_id = ' || p_resource_id);
581 hr_utility.TRACE ( 'lv_end_date = '
582 || TO_CHAR (lv_end_date,
583 'DD-MON-YYYY HH24:MI:SS'
584 )
585 );
586 hr_utility.TRACE ( 'l_start_date = '
587 || TO_CHAR (l_start_date,
588 'DD-MON-YYYY HH24:MI:SS'
589 )
590 );
591 hr_utility.TRACE ( 'l_period_end_date = '
592 || TO_CHAR (l_period_end_date,
593 'DD-MON-YYYY HH24:MI:SS'
594 )
595 );
596 END IF;
597
598 p_period_start := l_start_date;
599 p_period_end := l_period_end_date;
600 lv_end_date := p_period_start - 1;
601
602 IF g_debug
603 THEN
604 hr_utility.TRACE ( 'lv_end_date:'
605 || TO_CHAR (lv_end_date,
606 'DD-MON-YYYY HH24:MI:SS'
607 )
608 );
609 END IF;
610
611 ld_period_start_date := p_period_start;
612 ld_period_end_date := p_period_end;
613
614 IF g_debug
615 THEN
616 hr_utility.TRACE ( 'p_period_start :'
617 || TO_CHAR (p_period_start,
618 'DD-MON-YYYY HH24:MI:SS'
619 )
620 );
621 hr_utility.TRACE ( 'p_period_end :'
622 || TO_CHAR (p_period_end,
623 'DD-MON-YYYY HH24:MI:SS'
624 )
625 );
626 hr_utility.TRACE ( 'lv_end_date :'
627 || TO_CHAR (lv_end_date,
628 'DD-MON-YYYY HH24:MI:SS'
629 )
630 );
631 hr_utility.TRACE ( 'l_start_date :'
632 || TO_CHAR (l_start_date,
633 'DD-MON-YYYY HH24:MI:SS'
634 )
635 );
636 hr_utility.TRACE ( 'l_period_end_date :'
637 || TO_CHAR (l_period_end_date,
638 'DD-MON-YYYY HH24:MI:SS'
639 )
640 );
641 END IF;
642
643 OPEN c_period_exists (p_resource_id, p_period_start, p_period_end);
644
645 LOOP
646 IF g_debug
647 THEN
648 hr_utility.set_location ('populate_missing_time_periods',
649 95
650 );
651 END IF;
652
653 FETCH c_period_exists
654 INTO lv_start_time, lv_stop_time;
655
656 IF g_debug
657 THEN
658 hr_utility.TRACE ('p_resource_id :' || p_resource_id);
659 hr_utility.TRACE ('lv_row_found :' || lv_row_found);
660 hr_utility.TRACE ( 'lv_start_time :'
661 || TO_CHAR (lv_start_time,
662 'DD-MON-YYYY HH24:MI:SS'
663 )
664 );
665 hr_utility.TRACE ( 'lv_stop_time :'
666 || TO_CHAR (lv_stop_time,
667 'DD-MON-YYYY HH24:MI:SS'
668 )
669 );
670 hr_utility.TRACE ( 'ld_period_start_date:'
671 || TO_CHAR (ld_period_start_date,
672 'DD-MON-YYYY HH24:MI:SS'
673 )
674 );
675 hr_utility.TRACE ( 'ld_period_end_date :'
676 || TO_CHAR (ld_period_end_date,
677 'DD-MON-YYYY HH24:MI:SS'
678 )
679 );
680 hr_utility.TRACE ( 'p_period_start :'
681 || TO_CHAR (p_period_start,
682 'DD-MON-YYYY HH24:MI:SS'
683 )
684 );
685 hr_utility.TRACE ( 'p_period_end :'
686 || TO_CHAR (p_period_end,
687 'DD-MON-YYYY HH24:MI:SS'
688 )
689 );
690 END IF;
691
692 IF c_period_exists%NOTFOUND AND lv_row_found = 'N'
693 THEN
694 IF g_debug
695 THEN
696 hr_utility.set_location
697 ('populate_missing_time_periods',
698 100
699 );
700 END IF;
701
702 IF check_valid_period(p_resource_id, ld_period_start_date, ld_period_end_date)
703 THEN
704 l_next_index := g_time_periods.COUNT;
705 g_time_periods (l_next_index).start_time :=
706 ld_period_start_date;
707 g_time_periods (l_next_index).stop_time :=
708 ld_period_end_date;
709 g_time_periods (l_next_index).resource_id := p_resource_id;
710 END IF;
711 END IF;
712
713 IF c_period_exists%NOTFOUND
714 THEN
715 IF g_debug
716 THEN
717 hr_utility.set_location
718 ('populate_missing_time_periods',
719 110
720 );
721 END IF;
722
723 EXIT;
724 END IF;
725
726 lv_row_found := 'Y';
727
728 IF g_debug
729 THEN
730 hr_utility.set_location ('populate_missing_time_periods',
731 120
732 );
733 END IF;
734
735 IF TRUNC (p_period_start) < TRUNC (lv_start_time)
736 AND TRUNC (p_period_end) < TRUNC (lv_stop_time)
737 THEN
738 IF g_debug
739 THEN
740 hr_utility.set_location
741 ('populate_missing_time_periods',
742 130
743 );
744 END IF;
745
746 ld_period_start_date := p_period_start;
747 ld_period_end_date := lv_start_time - 1;
748
749 IF g_debug
750 THEN
751 hr_utility.TRACE ( 'ld_period_start_date:'
752 || TO_CHAR (ld_period_start_date,
753 'DD-MON-YYYY HH24:MI:SS'
754 )
755 );
756 hr_utility.TRACE ( 'ld_period_end_date :'
757 || TO_CHAR (ld_period_end_date,
758 'DD-MON-YYYY HH24:MI:SS'
759 )
760 );
761 hr_utility.set_location ('populate_missing_time_periods',
762 140
763 );
764 END IF;
765
766 OPEN c_period_exists_chk (p_resource_id,
767 ld_period_start_date,
768 ld_period_end_date
769 );
770
771 FETCH c_period_exists_chk
772 INTO lv_exists1;
773
774 IF c_period_exists_chk%NOTFOUND
775 THEN
776 IF g_debug
777 THEN
778 hr_utility.set_location
779 ('populate_missing_time_periods',
780 150
781 );
782 END IF;
783
784 IF check_valid_period(p_resource_id, ld_period_start_date, ld_period_end_date)
785 THEN
786 l_next_index := g_time_periods.COUNT;
787 g_time_periods (l_next_index).start_time :=
788 ld_period_start_date;
789 g_time_periods (l_next_index).stop_time :=
790 ld_period_end_date;
791 g_time_periods (l_next_index).resource_id :=
792 p_resource_id;
793 END IF;
794 END IF;
795
796 CLOSE c_period_exists_chk;
797 ELSIF TRUNC (p_period_start) > TRUNC (lv_start_time)
798 AND TRUNC (p_period_end) > TRUNC (lv_stop_time)
799 THEN
800 IF g_debug
801 THEN
802 hr_utility.set_location
803 ('populate_missing_time_periods',
804 160
805 );
806 END IF;
807
808 ld_period_start_date := lv_stop_time + 1;
809 ld_period_end_date := p_period_end;
810
811 IF g_debug
812 THEN
813 hr_utility.TRACE ( 'ld_period_start_date:'
814 || TO_CHAR (ld_period_start_date,
815 'DD-MON-YYYY HH24:MI:SS'
816 )
817 );
818 hr_utility.TRACE ( 'ld_period_end_date :'
819 || TO_CHAR (ld_period_end_date,
820 'DD-MON-YYYY HH24:MI:SS'
821 )
822 );
823 hr_utility.TRACE ('p_resource_id :'
824 || p_resource_id
825 );
826 END IF;
827
828 OPEN c_period_exists_chk (p_resource_id,
829 ld_period_start_date,
830 ld_period_end_date
831 );
832
833 FETCH c_period_exists_chk
834 INTO lv_exists1;
835
836 IF c_period_exists_chk%NOTFOUND
837 THEN
838 IF check_valid_period(p_resource_id, ld_period_start_date, ld_period_end_date)
839 THEN
840 l_next_index := g_time_periods.COUNT;
841 g_time_periods (l_next_index).start_time :=
842 ld_period_start_date;
843 g_time_periods (l_next_index).stop_time :=
844 ld_period_end_date;
845 g_time_periods (l_next_index).resource_id :=
846 p_resource_id;
847 END IF;
848 END IF;
849
850 CLOSE c_period_exists_chk;
851
852 IF g_debug
853 THEN
854 hr_utility.set_location
855 ('populate_missing_time_periods',
856 170
857 );
858 END IF;
859 END IF;
860
861 IF g_debug
862 THEN
863 hr_utility.set_location ('populate_missing_time_periods',
864 180
865 );
866 END IF;
867 END LOOP;
868
869 CLOSE c_period_exists;
870
871 lv_row_found := 'N';
872
873 IF g_debug
874 THEN
875 hr_utility.set_location ('populate_missing_time_periods', 190);
876 hr_utility.TRACE ( 'lv_end_date :'
877 || TO_CHAR (lv_end_date,
878 'DD-MON-YYYY HH24:MI:SS'
879 )
880 );
881 hr_utility.TRACE ( 'p_start_date :'
882 || TO_CHAR (p_start_date,
883 'DD-MON-YYYY HH24:MI:SS'
884 )
885 );
886 END IF;
887
888 -- EXIT when lv_end_date < p_start_date;
889 EXIT WHEN lv_end_date < l_p_start_date;
890 ELSE
891 IF g_debug
892 THEN
893 hr_utility.set_location ('populate_missing_time_periods', 200);
894 END IF;
895
896 p_period_start := '';
897 p_period_end := '';
898 l_start_date := '';
899 l_period_end_date := '';
900 lv_end_date := l_p_end_date;
901 lv_row_found := 'N';
902
903 IF g_debug
904 THEN
905 hr_utility.TRACE ( 'p_period_start :'
906 || TO_CHAR (p_period_start,
907 'DD-MON-YYYY HH24:MI:SS'
908 )
909 );
910 hr_utility.TRACE ( 'p_period_end :'
911 || TO_CHAR (p_period_end,
912 'DD-MON-YYYY HH24:MI:SS'
913 )
914 );
915 hr_utility.TRACE ( 'l_start_date :'
916 || TO_CHAR (l_start_date,
917 'DD-MON-YYYY HH24:MI:SS'
918 )
919 );
920 hr_utility.TRACE ( 'l_period_end_date :'
921 || TO_CHAR (l_period_end_date,
922 'DD-MON-YYYY HH24:MI:SS'
923 )
924 );
925 hr_utility.TRACE ( 'lv_end_date :'
926 || TO_CHAR (lv_end_date,
927 'DD-MON-YYYY HH24:MI:SS'
928 )
929 );
930 hr_utility.TRACE ('lv_row_found :' || lv_row_found);
931 END IF;
932 END IF;
933
934 IF g_debug
935 THEN
936 hr_utility.set_location ('populate_missing_time_periods', 210);
937 END IF;
938
939 l_index := l_period_list.NEXT (l_index);
940 END LOOP;
941 END IF; /* Bug: 5484502 */
942
943 IF g_debug
944 THEN
945 hr_utility.set_location ('populate_missing_time_periods', 220);
946 END IF;
947
948 IF g_time_periods.COUNT > 0
949 THEN
950 FOR i IN g_time_periods.FIRST .. g_time_periods.LAST
951 LOOP
952 IF g_debug
953 THEN
954 hr_utility.set_location ('populate_missing_time_periods', 230);
955 hr_utility.TRACE ('i ' || i);
956 hr_utility.TRACE ( 'Start time '
957 || TO_CHAR (g_time_periods (i).start_time,
958 'dd/mm/yyyy'
959 )
960 );
961 hr_utility.TRACE ( 'Stop time '
962 || TO_CHAR (g_time_periods (i).stop_time,
963 'dd/mm/yyyy'
964 )
965 );
966 END IF;
967 END LOOP;
968 END IF;
969
970 IF g_debug
971 THEN
972 hr_utility.TRACE ('g_time_periods.count ' || g_time_periods.COUNT);
973 END IF;
974
975 RETURN (g_time_periods.COUNT);
976 END populate_missing_time_periods;
977
978 --
979 -- ----------------------------------------------------------------------------
980 -- |---------------------< retrieve_missing_time_periods >--------------------|
981 -- ----------------------------------------------------------------------------
982
983 -- Adding additional parameter p_resource_id.
984 -- This is to retrieve missing TC periods of that resource only.
985 FUNCTION retrieve_missing_time_periods (
986 p_resource_id IN NUMBER,
987 p_rownum IN NUMBER
988 )
989 RETURN VARCHAR2
990 IS
991 --l_count number;
992 l_start_time DATE;
993 l_stop_time DATE;
994 l_resource_id NUMBER;
995 BEGIN
996 g_debug := hr_utility.debug_enabled;
997
998 --if p_rownum <= g_time_periods.count then
999 IF ( p_rownum <= g_time_periods.COUNT
1000 AND p_resource_id = g_time_periods (p_rownum - 1).resource_id
1001 )
1002 THEN
1003 IF g_debug
1004 THEN
1005 hr_utility.set_location ('retrieve_missing_time_periods', 10);
1006 hr_utility.TRACE ('p_rownum ' || p_rownum);
1007 --l_count := p_count - p_rownum;
1008 hr_utility.TRACE ( 'Start time '
1009 || TO_CHAR
1010 (g_time_periods (p_rownum - 1).start_time,
1011 'dd/mm/yyyy'
1012 )
1013 );
1014 hr_utility.TRACE ( 'Stop time '
1015 || TO_CHAR
1016 (g_time_periods (p_rownum - 1).stop_time,
1017 'dd/mm/yyyy'
1018 )
1019 );
1020 END IF;
1021
1022 l_start_time := TO_CHAR (g_time_periods (p_rownum - 1).start_time);
1023 l_stop_time := TO_CHAR (g_time_periods (p_rownum - 1).stop_time);
1024 l_resource_id := g_time_periods (p_rownum - 1).resource_id;
1025 -- if g_debug then
1026 -- for i in g_time_periods.first .. g_time_periods.last loop
1027 -- hr_utility.set_location('retrieve_missing_time_periods', 160);
1028 -- hr_utility.trace('i '|| i);
1029 -- hr_utility.trace('Start time '
1030 -- || to_char(g_time_periods(i).start_time, 'dd/mm/yyyy'));
1031 -- hr_utility.trace('Stop time '
1032 -- || to_char(g_time_periods(i).stop_time, 'dd/mm/yyyy'));
1033 -- end loop;
1034 -- hr_utility.trace('g_time_periods.count '|| g_time_periods.count);
1035 -- end if;
1036 RETURN (l_start_time || l_stop_time || l_resource_id);
1037 ELSE
1038 IF g_debug
1039 THEN
1040 hr_utility.TRACE ('in else for Deleted PL/SQL table');
1041 END IF;
1042
1043 IF g_time_periods.COUNT > 0
1044 THEN
1045 IF g_debug
1046 THEN
1047 hr_utility.set_location ('populate_missing_time_periods', 20);
1048 END IF;
1049
1050 FOR i IN g_time_periods.FIRST .. g_time_periods.LAST
1051 LOOP
1052 g_time_periods (i).start_time := NULL;
1053 g_time_periods (i).stop_time := NULL;
1054 g_time_periods (i).resource_id := NULL;
1055 END LOOP;
1056
1057 IF g_debug
1058 THEN
1059 hr_utility.TRACE ('Deleted PL/SQL table');
1060 END IF;
1061
1062 g_time_periods.DELETE;
1063 END IF;
1064
1065 RETURN NULL;
1066 END IF;
1067 END retrieve_missing_time_periods;
1068
1069 FUNCTION return_archived_status (p_date DATE)
1070 RETURN VARCHAR2
1071 IS
1072 CURSOR c_status
1073 IS
1074 SELECT 'Y'
1075 FROM hxc_data_sets
1076 WHERE status IN
1077 ('OFF_LINE', 'BACKUP_IN_PROGRESS', 'RESTORE_IN_PROGRESS')
1078 AND TRUNC (p_date) BETWEEN start_date AND end_date;
1079
1080 l_dummy VARCHAR2 (1);
1081 BEGIN
1082 OPEN c_status;
1083
1084 FETCH c_status
1085 INTO l_dummy;
1086
1087 IF (c_status%FOUND)
1088 THEN
1089 CLOSE c_status;
1090
1091 RETURN hr_bis.bis_decode_lookup ('YES_NO', 'Y');
1092 ELSE
1093 CLOSE c_status;
1094
1095 RETURN hr_bis.bis_decode_lookup ('YES_NO', 'N');
1096 END IF;
1097 END return_archived_status;
1098 END hxc_tpd_end;