1 PACKAGE BODY hxc_tpd_end AS
2 /* $Header: hxcendtp.pkb 120.17 2011/04/19 09:12:56 asrajago ship $ */
3
4 -- g_time_periods time_periods_table;
5 g_debug BOOLEAN := hr_utility.debug_enabled;
6
7 -- Bug 6998662
8 -- Added these global variables to manipulate the
9 -- global tables for missing periods. Used in
10 -- populate_missing_time_periods
11 g_resource_id NUMBER := 0;
12 g_tim_rec_id NUMBER := 0 ;
13 g_appln_set_id NUMBER := 0 ;
14 g_assignment_id NUMBER := 0;
15
16
17 -- This change is in accordance to the changes in the report query
18 -- Q_Resource_Pref_Eval.
19 -- Since Application is one of the Sort Option, Application_Id needs to be
20 -- fetched from the query.
21 -- This function is called from the query to fetch the Application_Id by call
22 -- to Preference_Evaluation package.
23 FUNCTION appl_id (p_person_id IN NUMBER)
24 RETURN NUMBER
25 IS
26 l_appl_id NUMBER;
27 l_message VARCHAR2 (80);
28 BEGIN
29 l_appl_id :=
30 hxc_preference_evaluation.resource_pref_errcode
31 (p_person_id,
32 'TS_PER_APPLICATION_SET|1|',
33 l_message,
34 SYSDATE
35 );
36 RETURN (l_appl_id);
37 EXCEPTION
38 WHEN OTHERS
39 THEN
40 RETURN (NULL);
41 END;
42
43 --
44 -- ----------------------------------------------------------------------------
45 -- |-----------------------< get_supervisor_name >----------------------------|
46 -- ----------------------------------------------------------------------------
47 --
48 FUNCTION get_supervisor_name (
49 p_supervisor_id IN NUMBER,
50 p_effective_date IN DATE
51 )
52 RETURN VARCHAR2
53 IS
54 CURSOR c_supervisor_name (
55 cp_supervisor_id IN NUMBER,
56 cp_effective_date IN DATE
57 )
58 IS
59 SELECT ppf.full_name
60 FROM per_people_f ppf
61 WHERE ppf.person_id = cp_supervisor_id
62 AND cp_effective_date BETWEEN ppf.effective_start_date
63 AND ppf.effective_end_date;
64
65 supervisor_name per_people_f.full_name%TYPE;
66 BEGIN
67 OPEN c_supervisor_name (p_supervisor_id, p_effective_date);
68
69 FETCH c_supervisor_name
70 INTO supervisor_name;
71
72 CLOSE c_supervisor_name;
73
74 RETURN (supervisor_name);
75 END;
76
77 --
78 -- ----------------------------------------------------------------------------
79 -- |---------------------< populate_missing_time_periods >--------------------|
80 -- ----------------------------------------------------------------------------
81
82 -- An addition parameter p_assignment_id is included in this function.
83 -- This parameter is needed since a resource will have different assignments for
84 -- diff range of period mainly because of Hire-Terminate-ReHire action.
85 -- In such case, assignment_id valid for that date range is imp to display only
86 -- those time_periods valid for that range of time.
87
88 -- Bug 6998662
89 -- Added new paramters time recipient id and application_set_id
90 -- We need these two to correctly process complex application sets
91 -- like Projects and Payroll.
92
93 FUNCTION populate_missing_time_periods (
94 p_resource_id IN NUMBER,
95 p_assignment_id IN NUMBER,
96 p_start_date IN DATE,
97 p_end_date IN DATE,
98 p_appln_set_id IN NUMBER,
99 p_tim_rec_id IN NUMBER
100 )
101 RETURN NUMBER
102 IS
103 p_period_end DATE;
104 p_period_start DATE;
105 l_recurring_period_id NUMBER (11);
106 l_number_per_fiscal_year NUMBER (15);
107 l_start_date DATE;
108 l_end_date DATE;
109 l_period_end_date DATE;
110 l_period_type VARCHAR2 (30);
111 l_duration_in_days NUMBER (10);
112 l_next_index BINARY_INTEGER := 0;
113 lv_end_date DATE := p_end_date;
114 lv_exists VARCHAR2 (6) := NULL;
115 l_add_to_start_date NUMBER (2);
116 l_pref_tc_period VARCHAR2 (80);
117 -- Bug 2900824 and 2801769
118 l_p_start_date DATE;
119 l_p_end_date DATE;
120
121 CURSOR c_period_exists (
122 cp_resource_id IN NUMBER,
123 cp_period_start IN DATE,
124 cp_period_end IN DATE
125 )
126 IS
127 SELECT htb.start_time, htb.stop_time
128 FROM hxc_timecard_summary htb
129 WHERE htb.resource_id = cp_resource_id
130 AND cp_period_start <= htb.stop_time
131 AND cp_period_end >= htb.start_time;
132
133 CURSOR c_period_exists_chk (
134 cp_resource_id IN NUMBER,
135 cp_period_start IN DATE,
136 cp_period_end IN DATE
137 )
138 IS
139 SELECT 'x'
140 FROM DUAL
141 WHERE EXISTS (
142 SELECT 'x'
143 FROM hxc_timecard_summary htb
144 WHERE htb.resource_id = cp_resource_id
145 AND cp_period_start <= htb.stop_time
146 AND cp_period_end >= htb.start_time);
147
148 p_message VARCHAR2 (30);
149 lv_start_time hxc_time_building_blocks.start_time%TYPE;
150 lv_stop_time hxc_time_building_blocks.stop_time%TYPE;
151 ld_period_start_date DATE;
152 ld_period_end_date DATE;
153 lv_row_found VARCHAR2 (1) := 'N';
154 lv_exists1 VARCHAR2 (1);
155 l_temp_periods hxc_period_evaluation.period_list;
156 l_index NUMBER;
157 l_period_list hxc_period_evaluation.period_list;
158 i BINARY_INTEGER := 1;
159
160
161 -- Bug 6998662
162 -- This variable ensures the termination dates display
163 -- instead of period end dates
164 l_asg_end_date DATE;
165
166 -- Bug 10084099
167 -- New variable to hold the params.
168 l_param_index VARCHAR2(200);
169
170
171
172 -- Bug 6998662
173 -- Added conditions to check for Application period also
174 -- in the following condition -- a period is valid in the
175 -- current call only if the application set given is correct
176 -- and the time recipient is valid.
177
178 Function check_valid_period (p_resource_id IN NUMBER,
179 p_start_date IN DATE,
180 p_end_date IN DATE,
181 p_appln_set_id IN NUMBER,
182 p_tim_rec_id IN NUMBER,
183 p_range_start IN DATE,
184 p_range_stop IN DATE
185 )
186 RETURN BOOLEAN
187 IS
188 l_cnt NUMBER;
189 l_pref NUMBER := 0;
190
191 BEGIN
192
193 -- Bug: 5971387 - The following query has been modified to drive the person_type_id
194 -- thru the per_person_type_usages table as opposed to per_person_types table.
195
196 SELECT count(*)
197 INTO l_cnt
198 FROM per_person_types ppt,
199 per_person_type_usages_f ptu,
200 per_people_f per
201 WHERE per.person_id = p_resource_id
202 AND ptu.person_id = per.person_id
203 AND ptu.person_type_id = ppt.person_type_id
204 -- AND ppt.system_person_type NOT IN ('EMP', 'EMP_APL', 'CWK') -- Bug 6486974
205 -- AND per.effective_start_date <= p_end_date
206 -- AND per.effective_end_date >= p_start_date;
207 AND ppt.system_person_type IN ('EMP', 'EMP_APL', 'CWK')
208 AND ( p_end_date between per.effective_start_date
209 and per.effective_end_date
210 OR p_start_date between per.effective_start_date
211 and per.effective_end_date );
212
213
214 -- Bug 6998662
215 -- The following if condition looks at the global pref
216 -- tables already loaded by Load_preferences call.
217 -- The given period is valid only if a valid application
218 -- set and a valid time recipient is being passed.
219
220 IF ( check_appln_set(p_resource_id,
221 p_range_start,
222 p_range_stop,
223 p_start_date,
224 p_end_date ) = p_appln_set_id)
225 AND ( check_tc_required(p_resource_id => p_resource_id,
226 p_start_date => p_range_start,
227 p_stop_date => p_range_stop,
228 p_evaluation_start_date => p_start_date,
229 p_evaluation_stop_date => p_end_date,
230 p_time_rec_id => p_tim_rec_id ) = 'Y' )
231 THEN
232 l_pref := 1;
233 END IF;
234
235 -- Return valid only if the above conditions are satisfied.
236 IF (l_cnt > 0 ) AND (l_pref = 1)
237 THEN
238 RETURN TRUE;
239 ELSE
240 RETURN FALSE;
241 END IF;
242 END check_valid_period;
243
244 BEGIN
245 g_debug := hr_utility.debug_enabled;
246
247
248 -- Bug 10084099
249 -- Build a concatenated list of all the params passed now.
250
251 l_param_index := p_resource_id||'-'||
252 p_assignment_id||'-'||
253 p_appln_set_id||'-'||
254 p_tim_rec_id||'-'||
255 TO_CHAR(p_start_date,'YYYYMMDD')||'-'||
256 TO_CHAR(p_end_date,'YYYYMMDD');
257
258 IF g_debug
259 THEN
260
261 hr_utility.trace(' L_param_index ');
262 hr_utility.trace(l_param_index);
263 END IF;
264
265 -- Check if the same set of params came in before.
266 -- If yes, return -- do not process further.
267 IF g_param_list.EXISTS(l_param_index)
268 THEN
269 hr_utility.trace('This list is already processed ');
270 RETURN(g_param_list(l_param_index));
271 END IF;
272
273
274 IF g_debug
275 THEN
276 hr_utility.TRACE ('Inside populate missing timecard function');
277 hr_utility.set_location ('populate_missing_time_periods', 10);
278 END IF;
279
280 IF g_time_periods.COUNT > 0
281 THEN
282
283
284
285 IF g_debug
286 THEN
287 hr_utility.set_location ('populate_missing_time_periods', 25);
288 hr_utility.TRACE ( 'p_start_date:'
289 || TO_CHAR (p_start_date, 'DD-MON-YYYY HH24:MI:SS')
290 );
291 hr_utility.TRACE ( 'p_end_date :'
292 || TO_CHAR (p_end_date, 'DD-MON-YYYY HH24:MI:SS')
293 );
294
295 hr_utility.TRACE ('p_resource_id :'|| p_resource_id);
296 hr_utility.TRACE ('p_assignment_id :'||p_assignment_id);
297 hr_utility.TRACE ('p_appln_set_id :'||p_appln_set_id);
298 hr_utility.TRACE ('p_tim_rec_id :'||p_tim_rec_id);
299
300 hr_utility.set_location ('populate_missing_time_periods', 20);
301 END IF;
302
303 -- Following piece of code not needed since g_time_periods.delete is sufficient.
304
305 -- FOR i in g_time_periods.first .. g_time_periods.last LOOP
306 -- g_time_periods(i).start_time := null;
307 -- g_time_periods(i).stop_time := null;
308 -- g_time_periods(i).resource_id := null;
309 -- END LOOP;
310 IF g_debug
311 THEN
312 hr_utility.TRACE ('Deleted PL/SQL table');
313 END IF;
314
315 -- Bug 6998662
316 -- Need all the three below conditions
317 -- It might be the same person, but might have a
318 -- different time recipient or assignment, so delete
319 -- the global table if anything is different.
320 IF p_resource_id <> g_resource_id
321 OR p_tim_rec_id <> g_tim_rec_id
322 OR p_assignment_id <> g_assignment_id
323 --AND p_appln_set_id <> g_appln_set_id
324 THEN
325 hr_utility.TRACE ('Deleted PL/SQL table2');
326 g_time_periods.DELETE;
327 -- Get the current values into the global variables to
328 -- compare with the next iteration.
329 g_resource_id := p_resource_id;
330 g_tim_rec_id := p_tim_rec_id;
331 g_appln_set_id := p_appln_set_id;
332 g_assignment_id := p_assignment_id;
333 END IF;
334
335
336 END IF;
337
338 IF g_debug
339 THEN
340 hr_utility.set_location ('populate_missing_time_periods', 25);
341 hr_utility.TRACE ( 'p_start_date:'
342 || TO_CHAR (p_start_date, 'DD-MON-YYYY HH24:MI:SS')
343 );
344 hr_utility.TRACE ( 'p_end_date :'
345 || TO_CHAR (p_end_date, 'DD-MON-YYYY HH24:MI:SS')
346 );
347 hr_utility.TRACE ('p_resource_id :'|| p_resource_id);
348 hr_utility.TRACE ('p_assignment_id :'||p_assignment_id);
349 hr_utility.TRACE ('p_appln_set_id :'||p_appln_set_id);
350 hr_utility.TRACE ('p_tim_rec_id :'||p_tim_rec_id);
351
352 END IF;
353
354 l_p_start_date := p_start_date;
355 l_p_end_date := p_end_date;
356
357 BEGIN
358 -- Incase Hiredate is between p_date_from and p_date_to,
359 -- then set starting date range as Hire date instead of p_start_from.
360
361 -- Also, here we need to consider assignment start and end date
362 -- for cases of Hire - Terminate - Re Hire where assignments are diff
363 -- before/after termination and re-hire.
364 /*
365 select min(ppf.effective_start_date), max(ppf.effective_end_date)
366 into l_p_start_date, l_p_end_date
367 from per_people_f ppf, per_assignments_f paf
368 where ppf.person_id = p_resource_id
369 and paf.person_id = ppf.person_id
370 and paf.assignment_id = p_assignment_id
371 and paf.effective_start_date between ppf.effective_start_date
372 and ppf.effective_end_date;
373 */
374
375 /* select min(ppf.effective_start_date), max(ppf.effective_end_date)
376 into l_p_start_date, l_p_end_date
377 from per_people_f ppf,
378 per_assignments_f paf,
379 per_person_types ppt,
380 per_person_type_usages_f ptu
381 where ppf.person_id = p_resource_id
382 and paf.person_id = ppf.person_id
383 and ptu.person_id = ppf.person_id
384 and ptu.person_type_id=ppt.person_type_id
385 and ppt.system_person_type in ('EMP','EMP_APL','CWK')
386 and paf.assignment_id = p_assignment_id
387 and paf.effective_start_date between ppf.effective_start_date
388 and ppf.effective_end_date
389 and ppf.effective_start_date between ptu.effective_start_date
390 and ptu.effective_end_date;
391 */
392 /*
393 SELECT
394 min(per.effective_start_date),
395 max(per.effective_end_date)
396 INTO
397 l_p_start_date, l_p_end_date
398 FROM
399 per_person_types ppt,
400 per_people_f per
401 WHERE
402 per.person_id = p_resource_id
403 AND ppt.person_type_id = per.person_type_id
404 AND ppt.system_person_type in ('EMP','EMP_APL','CWK')
405 AND per.effective_start_date =
406 (select min(perMin.effective_start_date)
407 from per_people_f perMin
408 where perMin.person_id = per.person_id
409 AND perMin.effective_start_date <= p_end_date
410 AND perMin.effective_end_date >= p_start_date)
411 AND EXISTS ( SELECT 'x'
412 FROM per_assignment_status_types ast,
413 per_assignments_f asm
414 WHERE asm.person_id = per.person_id
415 AND asm.primary_flag = 'Y'
416 AND
417 (
418 asm.effective_start_date <= per.effective_end_date
419 AND
420 asm.effective_end_date >= per.effective_start_date
421 )
422 AND ast.assignment_status_type_id
423 = asm.assignment_status_type_id
424 AND ast.pay_system_status = 'P' );
425 */
426
427 -- The above query changed as follows for bug 4687842. Basically the query has
428 -- been changed to drive the person_type_id thru the per_person_type_usages
429 -- table as opposed to per_person_types table as is the case in the above query
430 -- which causes bug 4687842.
431 SELECT MIN (per.effective_start_date), MAX (per.effective_end_date)
432 INTO l_p_start_date, l_p_end_date
433 FROM per_person_type_usages_f ptu,
434 per_person_types ppt,
435 per_people_f per
436 WHERE per.person_id = p_resource_id
437 AND ptu.person_id = per.person_id
438 AND ptu.person_type_id = ppt.person_type_id
439 AND ppt.system_person_type IN ('EMP', 'EMP_APL', 'CWK')
440 AND (per.effective_start_date =
441 (SELECT MIN (permin.effective_start_date)
442 FROM per_people_f permin
443 WHERE permin.person_id = per.person_id
444 AND permin.effective_start_date <= p_end_date
445 AND permin.effective_end_date >= p_start_date)
446 OR
447 per.effective_start_date =
448 (SELECT MAX(permin.effective_start_date)
449 FROM per_people_f permin
450 WHERE permin.person_id = per.person_id
451 AND permin.effective_start_date <= p_end_date
452 AND permin.effective_end_date >= p_start_date)
453 )
454 AND EXISTS (
455 SELECT 'x'
456 FROM per_assignment_status_types ast,
457 per_assignments_f asm
458 WHERE asm.person_id = per.person_id
459 AND asm.primary_flag = 'Y'
460 -- Bug 6998662 -- Added the assignment check here.
461 AND asm.assignment_id = p_assignment_id
462 AND ( asm.effective_start_date <=
463 per.effective_end_date
464 AND asm.effective_end_date >=
465 per.effective_start_date
466 )
467 AND ast.assignment_status_type_id =
468 asm.assignment_status_type_id
469 AND NVL (ast.pay_system_status, 'P') =
470 DECODE (ast.per_system_status,
471 'ACTIVE_CWK', 'D',
472 'P'
473 ));
474
475 IF g_debug
476 THEN
477 hr_utility.TRACE ( 'l_p_start_date:'|| TO_CHAR (l_p_start_date,'dd-mon-yyyy hh24:mi:ss'));
478 hr_utility.TRACE ( 'l_p_end_date:' || TO_CHAR (l_p_end_date,'dd-mon-yyyy hh24:mi:ss'));
479 END IF;
480
481 IF (l_p_start_date IS NULL OR l_p_end_date IS NULL)
482 THEN
483 RETURN (0);
484 -- Incase HireDate or Termianation Date of the resource is not between
485 -- p_date_from and p_date_to,
486 -- then the input date range is valid.
487 ELSE
488 IF (l_p_start_date < p_start_date)
489 THEN
490 l_p_start_date := p_start_date;
491 END IF;
492
493 IF (l_p_end_date > p_end_date)
494 THEN
495 l_p_end_date := p_end_date;
496 ELSE
497 lv_end_date := l_p_end_date;
498 END IF;
499 END IF;
500 END;
501
502 -- Bug 6998662
503 l_asg_end_date := l_p_end_date;
504
505 IF g_debug
506 THEN
507 hr_utility.set_location ('populate_missing_time_periods', 26);
508 END IF;
509
510 IF g_debug
511 THEN
512 hr_utility.TRACE ( 'l_p_start_date:'
513 || TO_CHAR (l_p_start_date,
514 'DD-MON-YYYY HH24:MI:SS'
515 )
516 );
517 hr_utility.TRACE ( 'l_p_end_date :'
518 || TO_CHAR (l_p_end_date, 'DD-MON-YYYY HH24:MI:SS')
519 );
520 hr_utility.TRACE ( 'lv_end_date :'
521 || TO_CHAR (lv_end_date, 'DD-MON-YYYY HH24:MI:SS')
522 );
523 END IF;
524
525 /* FOR i in c_people(p_business_group_id,p_resource_id) LOOP
526 lv_resource_id := i.person_id;
527 */
528 l_pref_tc_period :=
529 hxc_preference_evaluation.resource_pref_errcode
530 (p_resource_id,
531 'TC_W_TCRD_PERIOD|1|',
532 p_message,
533 l_p_start_date -- Bug 11853415
534 );
535
536 IF g_debug
537 THEN
538 hr_utility.set_location ('populate_missing_time_periods', 30);
539 hr_utility.TRACE ('l_pref_tc_period = ' || l_pref_tc_period);
540 END IF;
541
542 SELECT hrp.recurring_period_id, hrp.start_date, hrp.end_date,
543 hrp.period_type, hrp.duration_in_days
544 INTO l_recurring_period_id, l_start_date, l_end_date,
545 l_period_type, l_duration_in_days
546 FROM hxc_recurring_periods hrp
547 WHERE hrp.recurring_period_id = l_pref_tc_period;
548
549 IF g_debug
550 THEN
551 hr_utility.TRACE ('l_recurring_period_id:' || l_recurring_period_id);
552 hr_utility.TRACE ( 'l_start_date :'
553 || TO_CHAR (l_start_date, 'DD-MON-YYYY HH24:MI:SS')
554 );
555 hr_utility.TRACE ( 'l_end_date :'
556 || TO_CHAR (l_end_date, 'DD-MON-YYYY HH24:MI:SS')
557 );
558 hr_utility.TRACE ('l_period_type :' || l_period_type);
559 hr_utility.TRACE ('l_duration_in_days :' || l_duration_in_days);
560 hr_utility.set_location ('populate_missing_time_periods', 50);
561 END IF;
562
563 IF l_end_date IS NULL
564 THEN
565 l_end_date := hr_general.end_of_time;
566 --to_date('31/12/4712','DD/MM/YYYY');
567 END IF;
568
569 /* IF lv_end_date < l_start_date
570 OR lv_end_date > l_end_date THEN
571
572 p_error_message := 'The Timecard does not belong to this Period';
573
574 RETURN;
575 END IF;
576 */
577 IF g_debug
578 THEN
579 hr_utility.set_location ('populate_missing_time_periods', 60);
580 END IF;
581
582 l_temp_periods :=
583 hxc_period_evaluation.get_period_list
584 (p_current_date => SYSDATE,
585 p_recurring_period_type => l_period_type,
586 p_duration_in_days => l_duration_in_days,
587 p_rec_period_start_date => l_start_date,
588 p_max_date_in_futur => p_end_date,
589 p_max_date_in_past => p_start_date - 1
590 );
591
592 IF g_debug
593 THEN
594 hr_utility.TRACE ('l_temp_periods.count ' || l_temp_periods.COUNT);
595 END IF;
596
597
598
599 -- Since the l_temp_periods pl sql table index is not in sequence,
600 -- move these periods to l_period_list pl sql table with sequenced index
601 IF l_temp_periods.COUNT > 0
602 THEN
603 l_index := l_temp_periods.FIRST;
604
605 -- Bug 6998662
606 -- Looping thru and printing out the values, nothing more.
607 IF g_debug
608 THEN
609 hr_utility.trace('Printing l_temp_periods ');
610 FOR i IN l_temp_periods.FIRST..l_temp_periods.LAST
611 LOOP
612 IF l_temp_periods.EXISTS(i)
613 THEN
614 hr_utility.trace(TO_CHAR (l_temp_periods(i).start_date,'DD-MON-YYYY')||
615 '--'||TO_CHAR (l_temp_periods(i).end_date,'DD-MON-YYYY'));
616 END IF;
617 END LOOP;
618 END IF;
619
620
621 LOOP
622 IF g_debug
623 THEN
624 hr_utility.set_location ('populate_missing_time_periods', 65);
625 END IF;
626
627 EXIT WHEN NOT l_temp_periods.EXISTS (l_index);
628 l_period_list (i).start_date :=
629 l_temp_periods (l_index).start_date;
630 l_period_list (i).end_date := l_temp_periods (l_index).end_date;
631 l_index := l_temp_periods.NEXT (l_index);
632 i := i + 1;
633 END LOOP;
634 END IF;
635
636 IF g_debug
637 THEN
638 hr_utility.TRACE ('FYI');
639 END IF;
640
641 IF l_period_list.COUNT <> 0
642 THEN
643 IF g_debug
644 THEN
645 hr_utility.set_location ('populate_missing_time_periods', 70);
646 END IF;
647
648 FOR l_cnt IN l_period_list.FIRST .. l_period_list.LAST
649 LOOP
650 IF g_debug
651 THEN
652 hr_utility.TRACE ( 'l_period_list.start_date is:'
653 || TO_CHAR (l_period_list (l_cnt).start_date,
654 'DD-MON-YYYY'
655 )
656 );
657 hr_utility.TRACE ( 'l_period_list.end_date is:'
658 || TO_CHAR (l_period_list (l_cnt).end_date,
659 'DD-MON-YYYY'
660 )
661 );
662 END IF;
663 END LOOP;
664
665 IF g_debug
666 THEN
667 hr_utility.set_location ('populate_missing_time_periods', 75);
668 END IF;
669 END IF;
670
671 --------------------------------------------------------------------------------
672 IF g_debug
673 THEN
674 hr_utility.set_location ('populate_missing_time_periods', 80);
675 END IF;
676
677 IF l_period_list.COUNT <> 0 THEN /* Bug: 5484502 */
678
679 FOR i IN REVERSE l_period_list.FIRST .. l_period_list.LAST
680 LOOP
681 IF g_debug
682 THEN
683 hr_utility.set_location ('populate_missing_time_periods', 84);
684 hr_utility.TRACE ('i :' || i);
685 END IF;
686
687 EXIT WHEN NOT l_period_list.EXISTS (i);
688
689 IF g_debug
690 THEN
691 hr_utility.set_location ('populate_missing_time_periods', 85);
692 END IF;
693
694 l_start_date := l_period_list (i).start_date;
695 l_period_end_date := l_period_list (i).end_date;
696
697 IF( l_start_date < l_p_start_date)
698 THEN
699 l_start_date := l_p_start_date;
700 END IF;
701
702 IF g_debug
703 THEN
704 hr_utility.TRACE ( 'lv_end_date = '
705 || TO_CHAR (lv_end_date,
706 'DD-MON-YYYY HH24:MI:SS'
707 )
708 );
709 hr_utility.TRACE ( 'l_start_date = '
710 || TO_CHAR (l_start_date,
711 'DD-MON-YYYY HH24:MI:SS'
712 )
713 );
714 hr_utility.TRACE ( 'l_period_end_date = '
715 || TO_CHAR (l_period_end_date,
716 'DD-MON-YYYY HH24:MI:SS'
717 )
718 );
719 END IF;
720
721 IF lv_end_date >= l_start_date AND lv_end_date <= l_period_end_date
722 THEN
723 IF g_debug
724 THEN
725 hr_utility.set_location ('populate_missing_time_periods', 90);
726 hr_utility.TRACE ('p_resource_id = ' || p_resource_id);
727 hr_utility.TRACE ( 'lv_end_date = '
728 || TO_CHAR (lv_end_date,
729 'DD-MON-YYYY HH24:MI:SS'
730 )
731 );
732 hr_utility.TRACE ( 'l_start_date = '
733 || TO_CHAR (l_start_date,
734 'DD-MON-YYYY HH24:MI:SS'
735 )
736 );
737 hr_utility.TRACE ( 'l_period_end_date = '
738 || TO_CHAR (l_period_end_date,
739 'DD-MON-YYYY HH24:MI:SS'
740 )
741 );
742 END IF;
743
744 p_period_start := l_start_date;
745 p_period_end := l_period_end_date;
746 lv_end_date := p_period_start - 1;
747
748 IF g_debug
749 THEN
750 hr_utility.TRACE ( 'lv_end_date:'
751 || TO_CHAR (lv_end_date,
752 'DD-MON-YYYY HH24:MI:SS'
753 )
754 );
755 END IF;
756
757 ld_period_start_date := p_period_start;
758 ld_period_end_date := p_period_end;
759
760 IF g_debug
761 THEN
762 hr_utility.TRACE ( 'p_period_start :'
763 || TO_CHAR (p_period_start,
764 'DD-MON-YYYY HH24:MI:SS'
765 )
766 );
767 hr_utility.TRACE ( 'p_period_end :'
768 || TO_CHAR (p_period_end,
769 'DD-MON-YYYY HH24:MI:SS'
770 )
771 );
772 hr_utility.TRACE ( 'lv_end_date :'
773 || TO_CHAR (lv_end_date,
774 'DD-MON-YYYY HH24:MI:SS'
775 )
776 );
777 hr_utility.TRACE ( 'l_start_date :'
778 || TO_CHAR (l_start_date,
779 'DD-MON-YYYY HH24:MI:SS'
780 )
781 );
782 hr_utility.TRACE ( 'l_period_end_date :'
783 || TO_CHAR (l_period_end_date,
784 'DD-MON-YYYY HH24:MI:SS'
785 )
786 );
787 END IF;
788
789 OPEN c_period_exists (p_resource_id, p_period_start, p_period_end);
790
791 LOOP
792 IF g_debug
793 THEN
794 hr_utility.set_location ('populate_missing_time_periods',
795 95
796 );
797 END IF;
798
799 FETCH c_period_exists
800 INTO lv_start_time, lv_stop_time;
801
802 IF g_debug
803 THEN
804 hr_utility.TRACE ('p_resource_id :' || p_resource_id);
805 hr_utility.TRACE ('lv_row_found :' || lv_row_found);
806 hr_utility.TRACE ( 'lv_start_time :'
807 || TO_CHAR (lv_start_time,
808 'DD-MON-YYYY HH24:MI:SS'
809 )
810 );
811 hr_utility.TRACE ( 'lv_stop_time :'
812 || TO_CHAR (lv_stop_time,
813 'DD-MON-YYYY HH24:MI:SS'
814 )
815 );
816 hr_utility.TRACE ( 'ld_period_start_date:'
817 || TO_CHAR (ld_period_start_date,
818 'DD-MON-YYYY HH24:MI:SS'
819 )
820 );
821 hr_utility.TRACE ( 'ld_period_end_date :'
822 || TO_CHAR (ld_period_end_date,
823 'DD-MON-YYYY HH24:MI:SS'
824 )
825 );
826 hr_utility.TRACE ( 'p_period_start :'
827 || TO_CHAR (p_period_start,
828 'DD-MON-YYYY HH24:MI:SS'
829 )
830 );
831 hr_utility.TRACE ( 'p_period_end :'
832 || TO_CHAR (p_period_end,
833 'DD-MON-YYYY HH24:MI:SS'
834 )
835 );
836 END IF;
837
838 IF c_period_exists%NOTFOUND AND lv_row_found = 'N'
839 THEN
840 IF g_debug
841 THEN
842 hr_utility.set_location
843 ('populate_missing_time_periods',
844 100
845 );
846 END IF;
847
848 -- Bug 6998662
849 -- Changed the below call to pass the new values.
850 IF check_valid_period(p_resource_id,
851 ld_period_start_date,
852 ld_period_end_date,
853 p_appln_set_id,
854 p_tim_rec_id,
855 p_start_date,
856 p_end_date)
857 THEN
858 l_next_index := g_time_periods.COUNT;
859 g_time_periods (l_next_index).start_time :=
860 ld_period_start_date;
861 g_time_periods (l_next_index).stop_time :=
862 -- to take care of assig end dates.
863 --ld_period_end_date;
864 LEAST(ld_period_end_date,l_asg_end_date);
865 g_time_periods (l_next_index).resource_id := p_resource_id;
866 END IF;
867 END IF;
868
869 IF c_period_exists%NOTFOUND
870 THEN
871 IF g_debug
872 THEN
873 hr_utility.set_location
874 ('populate_missing_time_periods',
875 110
876 );
877 END IF;
878
879 EXIT;
880 END IF;
881
882 lv_row_found := 'Y';
883
884 IF g_debug
885 THEN
886 hr_utility.set_location ('populate_missing_time_periods',
887 120
888 );
889 END IF;
890
891 IF TRUNC (p_period_start) < TRUNC (lv_start_time)
892 AND TRUNC (p_period_end) < TRUNC (lv_stop_time)
893 THEN
894 IF g_debug
895 THEN
896 hr_utility.set_location
897 ('populate_missing_time_periods',
898 130
899 );
900 END IF;
901
902 ld_period_start_date := p_period_start;
903 ld_period_end_date := lv_start_time - 1;
904
905 IF g_debug
906 THEN
907 hr_utility.TRACE ( 'ld_period_start_date:'
908 || TO_CHAR (ld_period_start_date,
909 'DD-MON-YYYY HH24:MI:SS'
910 )
911 );
912 hr_utility.TRACE ( 'ld_period_end_date :'
913 || TO_CHAR (ld_period_end_date,
914 'DD-MON-YYYY HH24:MI:SS'
915 )
916 );
917 hr_utility.set_location ('populate_missing_time_periods',
918 140
919 );
920 END IF;
921
922 OPEN c_period_exists_chk (p_resource_id,
923 ld_period_start_date,
924 ld_period_end_date
925 );
926
927 FETCH c_period_exists_chk
928 INTO lv_exists1;
929
930 IF c_period_exists_chk%NOTFOUND
931 THEN
932 IF g_debug
933 THEN
934 hr_utility.set_location
935 ('populate_missing_time_periods',
936 150
937 );
938 END IF;
939
940 -- Bug 6998662
941 -- Changed the below call to pass the new values.
942 IF check_valid_period(p_resource_id,
943 ld_period_start_date,
944 ld_period_end_date,
945 p_appln_set_id,
946 p_tim_rec_id,
947 p_start_date,
948 p_end_date )
949 THEN
950 l_next_index := g_time_periods.COUNT;
951 g_time_periods (l_next_index).start_time :=
952 ld_period_start_date;
953 g_time_periods (l_next_index).stop_time :=
954 -- to take care of assig end dates.
955 --ld_period_end_date;
956 LEAST(ld_period_end_date,l_asg_end_date);
957 g_time_periods (l_next_index).resource_id :=
958 p_resource_id;
959 END IF;
960 END IF;
961
962 CLOSE c_period_exists_chk;
963 ELSIF TRUNC (p_period_start) > TRUNC (lv_start_time)
964 AND TRUNC (p_period_end) > TRUNC (lv_stop_time)
965 THEN
966 IF g_debug
967 THEN
968 hr_utility.set_location
969 ('populate_missing_time_periods',
970 160
971 );
972 END IF;
973
974 ld_period_start_date := lv_stop_time + 1;
975 ld_period_end_date := p_period_end;
976
977 IF g_debug
978 THEN
979 hr_utility.TRACE ( 'ld_period_start_date:'
980 || TO_CHAR (ld_period_start_date,
981 'DD-MON-YYYY HH24:MI:SS'
982 )
983 );
984 hr_utility.TRACE ( 'ld_period_end_date :'
985 || TO_CHAR (ld_period_end_date,
986 'DD-MON-YYYY HH24:MI:SS'
987 )
988 );
989 hr_utility.TRACE ('p_resource_id :'
990 || p_resource_id
991 );
992 END IF;
993
994 OPEN c_period_exists_chk (p_resource_id,
995 ld_period_start_date,
996 ld_period_end_date
997 );
998
999 FETCH c_period_exists_chk
1000 INTO lv_exists1;
1001
1002 IF c_period_exists_chk%NOTFOUND
1003 THEN
1004 -- Bug 6998662
1005 -- Changed the below call to pass the new values.
1006 IF check_valid_period(p_resource_id,
1007 ld_period_start_date,
1008 ld_period_end_date,
1009 p_appln_set_id,
1010 p_tim_rec_id,
1011 p_start_date,
1012 p_end_date )
1013 THEN
1014 l_next_index := g_time_periods.COUNT;
1015 g_time_periods (l_next_index).start_time :=
1016 ld_period_start_date;
1017 g_time_periods (l_next_index).stop_time :=
1018 -- to take care of assig end dates.
1019 --ld_period_end_date;
1020 LEAST(ld_period_end_date,l_asg_end_date);
1021 g_time_periods (l_next_index).resource_id :=
1022 p_resource_id;
1023 END IF;
1024 END IF;
1025
1026 CLOSE c_period_exists_chk;
1027
1028 IF g_debug
1029 THEN
1030 hr_utility.set_location
1031 ('populate_missing_time_periods',
1032 170
1033 );
1034 END IF;
1035 END IF;
1036
1037 IF g_debug
1038 THEN
1039 hr_utility.set_location ('populate_missing_time_periods',
1040 180
1041 );
1042 END IF;
1043 END LOOP;
1044
1045 CLOSE c_period_exists;
1046
1047 lv_row_found := 'N';
1048
1049 IF g_debug
1050 THEN
1051 hr_utility.set_location ('populate_missing_time_periods', 190);
1052 hr_utility.TRACE ( 'lv_end_date :'
1053 || TO_CHAR (lv_end_date,
1054 'DD-MON-YYYY HH24:MI:SS'
1055 )
1056 );
1057 hr_utility.TRACE ( 'p_start_date :'
1058 || TO_CHAR (p_start_date,
1059 'DD-MON-YYYY HH24:MI:SS'
1060 )
1061 );
1062 END IF;
1063
1064 -- EXIT when lv_end_date < p_start_date;
1065 EXIT WHEN lv_end_date < l_p_start_date;
1066 ELSE
1067 IF g_debug
1068 THEN
1069 hr_utility.set_location ('populate_missing_time_periods', 200);
1070 END IF;
1071
1072 p_period_start := '';
1073 p_period_end := '';
1074 l_start_date := '';
1075 l_period_end_date := '';
1076 lv_end_date := l_p_end_date;
1077 lv_row_found := 'N';
1078
1079 IF g_debug
1080 THEN
1081 hr_utility.TRACE ( 'p_period_start :'
1082 || TO_CHAR (p_period_start,
1083 'DD-MON-YYYY HH24:MI:SS'
1084 )
1085 );
1086 hr_utility.TRACE ( 'p_period_end :'
1087 || TO_CHAR (p_period_end,
1088 'DD-MON-YYYY HH24:MI:SS'
1089 )
1090 );
1091 hr_utility.TRACE ( 'l_start_date :'
1092 || TO_CHAR (l_start_date,
1093 'DD-MON-YYYY HH24:MI:SS'
1094 )
1095 );
1096 hr_utility.TRACE ( 'l_period_end_date :'
1097 || TO_CHAR (l_period_end_date,
1098 'DD-MON-YYYY HH24:MI:SS'
1099 )
1100 );
1101 hr_utility.TRACE ( 'lv_end_date :'
1102 || TO_CHAR (lv_end_date,
1103 'DD-MON-YYYY HH24:MI:SS'
1104 )
1105 );
1106 hr_utility.TRACE ('lv_row_found :' || lv_row_found);
1107 END IF;
1108 END IF;
1109
1110 IF g_debug
1111 THEN
1112 hr_utility.set_location ('populate_missing_time_periods', 210);
1113 END IF;
1114
1115 l_index := l_period_list.NEXT (l_index);
1116 END LOOP;
1117 END IF; /* Bug: 5484502 */
1118
1119 IF g_debug
1120 THEN
1121 hr_utility.set_location ('populate_missing_time_periods', 220);
1122 END IF;
1123
1124 IF g_time_periods.COUNT > 0
1125 THEN
1126 FOR i IN g_time_periods.FIRST .. g_time_periods.LAST
1127 LOOP
1128 IF g_debug
1129 THEN
1130 hr_utility.set_location ('populate_missing_time_periods', 230);
1131 hr_utility.TRACE ('i ' || i);
1132 hr_utility.TRACE ( 'Start time '
1133 || TO_CHAR (g_time_periods (i).start_time,
1134 'dd/mm/yyyy'
1135 )
1136 );
1137 hr_utility.TRACE ( 'Stop time '
1138 || TO_CHAR (g_time_periods (i).stop_time,
1139 'dd/mm/yyyy'
1140 )
1141 );
1142 END IF;
1143 END LOOP;
1144 END IF;
1145
1146 IF g_debug
1147 THEN
1148 hr_utility.TRACE ('g_time_periods.count ' || g_time_periods.COUNT);
1149 END IF;
1150
1151
1152 -- Bug 10084099
1153 -- Store the number of timecards missing in to this global table.
1154 g_param_list(l_param_index) := g_time_periods.COUNT;
1155
1156
1157 RETURN (g_time_periods.COUNT);
1158 END populate_missing_time_periods;
1159
1160 --
1161 -- ----------------------------------------------------------------------------
1162 -- |---------------------< retrieve_missing_time_periods >--------------------|
1163 -- ----------------------------------------------------------------------------
1164
1165 -- Adding additional parameter p_resource_id.
1166 -- This is to retrieve missing TC periods of that resource only.
1167 -- Bug 6998662
1168 -- Added a new parameter assignment id.
1169 -- As of now, not being used, but since we added the data link,
1170 -- using this now.
1171 FUNCTION retrieve_missing_time_periods (
1172 p_resource_id IN NUMBER,
1173 p_assignment_id IN NUMBER DEFAULT NULL,
1174 p_rownum IN NUMBER
1175 )
1176 RETURN VARCHAR2
1177 IS
1178 --l_count number;
1179 l_start_time DATE;
1180 l_stop_time DATE;
1181 l_resource_id NUMBER;
1182 BEGIN
1183 g_debug := hr_utility.debug_enabled;
1184
1185 --if p_rownum <= g_time_periods.count then
1186 IF ( p_rownum <= g_time_periods.COUNT
1187 AND p_resource_id = g_time_periods (p_rownum - 1).resource_id
1188 )
1189 THEN
1190 IF g_debug
1191 THEN
1192 hr_utility.set_location ('retrieve_missing_time_periods', 10);
1193 hr_utility.TRACE ('p_rownum ' || p_rownum);
1194 --l_count := p_count - p_rownum;
1195 hr_utility.TRACE ( 'Start time '
1196 || TO_CHAR
1197 (g_time_periods (p_rownum - 1).start_time,
1198 'dd/mm/yyyy'
1199 )
1200 );
1201 hr_utility.TRACE ( 'Stop time '
1202 || TO_CHAR
1203 (g_time_periods (p_rownum - 1).stop_time,
1204 'dd/mm/yyyy'
1205 )
1206 );
1207 END IF;
1208
1209 l_start_time := TO_CHAR (g_time_periods (p_rownum - 1).start_time);
1210 l_stop_time := TO_CHAR (g_time_periods (p_rownum - 1).stop_time);
1211 l_resource_id := g_time_periods (p_rownum - 1).resource_id;
1212 -- if g_debug then
1213 -- for i in g_time_periods.first .. g_time_periods.last loop
1214 -- hr_utility.set_location('retrieve_missing_time_periods', 160);
1215 -- hr_utility.trace('i '|| i);
1216 -- hr_utility.trace('Start time '
1217 -- || to_char(g_time_periods(i).start_time, 'dd/mm/yyyy'));
1218 -- hr_utility.trace('Stop time '
1219 -- || to_char(g_time_periods(i).stop_time, 'dd/mm/yyyy'));
1220 -- end loop;
1221 -- hr_utility.trace('g_time_periods.count '|| g_time_periods.count);
1222 -- end if;
1223 RETURN (l_start_time || l_stop_time || l_resource_id);
1224 ELSE
1225 IF g_debug
1226 THEN
1227 hr_utility.TRACE ('Passed values did not match, abt to delete the table.');
1228 END IF;
1229
1230 IF g_time_periods.COUNT > 0
1231 THEN
1232 IF g_debug
1233 THEN
1234 hr_utility.set_location ('populate_missing_time_periods', 20);
1235 END IF;
1236
1237 -- Bug 6998662
1238 -- Commented out the below loop -- when you are deleting,
1239 -- its pretty pointless to have the values NULLed out first.
1240 /*
1241 FOR i IN g_time_periods.FIRST .. g_time_periods.LAST
1242 LOOP
1243 g_time_periods (i).start_time := NULL;
1244 g_time_periods (i).stop_time := NULL;
1245 g_time_periods (i).resource_id := NULL;
1246 END LOOP;
1247 */
1248
1249 IF g_debug
1250 THEN
1251 hr_utility.TRACE ('Deleted PL/SQL table');
1252 END IF;
1253
1254 g_time_periods.DELETE;
1255 END IF;
1256
1257 RETURN NULL;
1258 END IF;
1259 END retrieve_missing_time_periods;
1260
1261 FUNCTION return_archived_status (p_date DATE)
1262 RETURN VARCHAR2
1263 IS
1264 CURSOR c_status
1265 IS
1266 SELECT 'Y'
1267 FROM hxc_data_sets
1268 WHERE status IN
1269 ('OFF_LINE', 'BACKUP_IN_PROGRESS', 'RESTORE_IN_PROGRESS')
1270 AND TRUNC (p_date) BETWEEN start_date AND end_date;
1271
1272 l_dummy VARCHAR2 (1);
1273 BEGIN
1274 OPEN c_status;
1275
1276 FETCH c_status
1277 INTO l_dummy;
1278
1279 IF (c_status%FOUND)
1280 THEN
1281 CLOSE c_status;
1282
1283 RETURN hr_bis.bis_decode_lookup ('YES_NO', 'Y');
1284 ELSE
1285 CLOSE c_status;
1286
1287 RETURN hr_bis.bis_decode_lookup ('YES_NO', 'N');
1288 END IF;
1289 END return_archived_status;
1290
1291
1292
1293 -- Bug 6998662
1294 -- All the below functions added for the above bug number.
1295 -- Originally logged for duplicate records issue for
1296 -- rehired employees with a name change, but also includes
1297 -- a complete preferences processing -- earlier, the report
1298 -- only looked at the SYSDATE's preferences.
1299
1300
1301 -- Sorts the nested table passed in, in the order of dates.
1302
1303 PROCEDURE sort_pref_table( p_in_table IN MISTC_PREF_TABLE,
1304 p_out_table OUT NOCOPY MISTC_PREF_TABLE)
1305 IS
1306
1307 TYPE DATE_ASSOC_ARRAY IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
1308
1309 temp_pref_table MISTC_PREF_TABLE;
1310 temp_array DATE_ASSOC_ARRAY;
1311 ind NUMBER := 0;
1312 idx VARCHAR2(10);
1313
1314 BEGIN
1315
1316 -- This is a tweak around algorithm to sort, but I am more
1317 -- than sure this works faster than any other sort algorithm
1318 -- implemented in plsql. Read thru the comments inline.
1319
1320 -- I have tableA of varchar2, indexed by number, which I want to sort.
1321 -- Create an associative array of type Number, indexed by Varchar2,
1322 -- Lets call this temp array.
1323 -- Loop thru tableA, and copy the index of tableA as the value of
1324 -- temp array, and the value as the index.
1325 -- Loop thru temp array from First to Last index -- this would come sorted.
1326 -- Assign tableA(temp array value) to a tableB.
1327
1328 -- Now, tableB is a sorted copy of tableA.
1329 -- The same algorithm is followed in the below construct, only sorted
1330 -- for date in YYYYMMDD format.
1331
1332
1333
1334 -- Copy the structure into a temporary table.
1335 temp_pref_table := p_in_table;
1336 -- Loop thru the table to sort.
1337 FOR i IN temp_pref_table.FIRST..temp_pref_table.LAST
1338 LOOP
1339 -- In the above temp assoc array, copy the index as the value
1340 -- and the value as the index. Watch out the format conversion.
1341 temp_array(TO_CHAR(temp_pref_table(i).start_date,'yyyymmdd')) :=
1342 i;
1343 END LOOP;
1344
1345 -- Loop thru the associative array.
1346 idx := temp_array.FIRST;
1347 LOOP
1348 ind := ind+1;
1349 -- Copy to out table, using the value from the temp array as the
1350 -- index of the original table..
1351 p_out_table(ind) := temp_pref_table(temp_array(idx));
1352 idx := temp_array.NEXT(idx);
1353 EXIT WHEN idx IS NULL;
1354 END LOOP;
1355
1356 -- You have the sorted out table now.
1357
1358 END sort_pref_table ;
1359
1360
1361
1362 -- Just calls up procedure Load_preferences.
1363 -- This function is created to call from the report query,
1364 -- to load preferences.
1365 FUNCTION load_preferences( p_resource_id IN NUMBER,
1366 p_start_date IN DATE,
1367 p_stop_date IN DATE )
1368 RETURN NUMBER
1369 IS
1370
1371 BEGIN
1372 load_preferences(p_resource_id,
1373 p_start_date,
1374 p_stop_date );
1375 RETURN 1;
1376 END load_preferences;
1377
1378
1379
1380 -- Loads preferences for the person passed for the given
1381 -- date range.
1382 PROCEDURE load_preferences( p_resource_id IN NUMBER,
1383 p_start_date IN DATE,
1384 p_stop_date IN DATE )
1385 IS
1386
1387 pref_table hxc_preference_evaluation.t_pref_table;
1388 tc_req_table MISTC_PREF_TABLE;
1389 appln_set_table MISTC_PREF_TABLE;
1390 tc_idx NUMBER := 0;
1391 app_idx NUMBER := 0;
1392
1393
1394 -- Description on the data structures.
1395
1396 -- We want a two dimensional table, but since that is not possible, we
1397 -- created a table of tables.
1398 -- g_mistc_pref_list is a table of tables.
1399 -- It has three members. -- resource_id
1400 -- tcard_req_table
1401 -- appln_set_table
1402
1403 -- tcard_req_table and appln_set_table are both tables of
1404 -- resource_id
1405 -- start_date
1406 -- end_date
1407 -- attributelist.
1408
1409 -- So effectively you are managing two tables for each resource.
1410
1411 BEGIN
1412
1413 -- Pick up all preferences, because if you pass the pref code,
1414 -- evaluation happens twice.
1415 hxc_preference_evaluation.resource_preferences(p_resource_id => p_resource_id,
1416 p_start_evaluation_date => p_start_date,
1417 p_end_evaluation_date => p_stop_date,
1418 p_pref_table => pref_table);
1419
1420 IF pref_table.COUNT > 0
1421 THEN
1422 -- Loop thru the pref table, and find out only the required
1423 -- preferences.
1424 FOR i IN pref_table.FIRST..pref_table.LAST
1425 LOOP
1426 -- Timestore timecard required pref should be a collection of
1427 -- time recipients. Copy the attributes to the table structure.
1428 IF pref_table(i).preference_code = 'TS_PER_TCARD_REQUIRED'
1429 THEN
1430 tc_idx := tc_idx + 1;
1431 tc_req_table(tc_idx).resource_id := p_resource_id ;
1432 tc_req_table(tc_idx).start_date := pref_table(i).start_date;
1433 tc_req_table(tc_idx).stop_date := pref_table(i).end_date;
1434 tc_req_table(tc_idx).attributelist :=
1435 '-'||pref_table(i).attribute1||
1436 '-'||pref_table(i).attribute2||
1437 '-'||pref_table(i).attribute3||
1438 '-'||pref_table(i).attribute4||
1439 '-'||pref_table(i).attribute5||
1440 '-'||pref_table(i).attribute6||
1441 '-'||pref_table(i).attribute7||
1442 '-'||pref_table(i).attribute8||
1443 '-'||pref_table(i).attribute9||
1444 '-'||pref_table(i).attribute10||
1445 '-'||pref_table(i).attribute11||
1446 '-'||pref_table(i).attribute12||
1447 '-'||pref_table(i).attribute13||
1448 '-'||pref_table(i).attribute14||
1449 '-'||pref_table(i).attribute15||'-' ;
1450 -- Time store application set is just an application set id
1451 -- Copy this value into the table structure.
1452 ELSIF pref_table(i).preference_code = 'TS_PER_APPLICATION_SET'
1453 THEN
1454 app_idx := app_idx + 1;
1455 appln_set_table(app_idx).resource_id := p_resource_id ;
1456 appln_set_table(app_idx).start_date := pref_table(i).start_date;
1457 appln_set_table(app_idx).stop_date := pref_table(i).end_date;
1458 appln_set_table(app_idx).attributelist :=
1459 pref_table(i).attribute1;
1460 END IF;
1461 END LOOP;
1462
1463 -- Put up the values into the master table.
1464 g_mistc_pref_list(p_resource_id).resource_id := p_resource_id;
1465
1466 -- Put the sorted pref values into the master table.for this resource.
1467 sort_pref_table(tc_req_table,g_mistc_pref_list(p_resource_id).tcard_req_table);
1468 sort_pref_table(appln_set_table,g_mistc_pref_list(p_resource_id).appln_set_table);
1469 END IF;
1470 END load_preferences;
1471
1472
1473
1474 FUNCTION check_tc_required ( p_resource_id IN NUMBER,
1475 p_start_date IN DATE DEFAULT NULL,
1476 p_stop_date IN DATE DEFAULT NULL,
1477 p_evaluation_start_date IN DATE,
1478 p_evaluation_stop_date IN DATE,
1479 p_time_rec_id IN NUMBER )
1480 RETURN VARCHAR2
1481 IS
1482
1483 BEGIN
1484
1485 -- If the preference does not exist, load it.
1486 IF NOT g_mistc_pref_list.EXISTS(p_resource_id)
1487 AND p_start_date IS NOT NULL
1488 AND p_stop_date IS NOT NULL
1489 THEN
1490 load_preferences(p_resource_id,
1491 p_start_date,
1492 p_stop_date) ;
1493 END IF;
1494
1495 -- Loop thru the preference values.
1496 FOR i IN g_mistc_pref_list(p_resource_id).tcard_req_table.FIRST..
1497 g_mistc_pref_list(p_resource_id).tcard_req_table.LAST
1498 LOOP
1499 -- Are there any more values ?? If yes go to the one where the given
1500 -- range fits in .
1501 IF g_mistc_pref_list(p_resource_id).tcard_req_table.EXISTS(i+1)
1502 THEN
1503 -- The below condition would stop where the ranges coincide.
1504 IF g_mistc_pref_list(p_resource_id).tcard_req_table(i+1).start_date
1505 > p_evaluation_start_date
1506 AND g_mistc_pref_list(p_resource_id).tcard_req_table(i).start_date
1507 <= p_evaluation_start_date
1508 THEN
1509 -- If the given time recipient id is in the list, return N
1510 -- Else Y.
1511 IF INSTR(g_mistc_pref_list(p_resource_id).tcard_req_table(i).attributelist,
1512 '-'||p_time_rec_id||'-') <> 0
1513 THEN
1514 RETURN 'N';
1515 ELSE
1516 RETURN 'Y' ;
1517 END IF;
1518 ELSIF g_mistc_pref_list(p_resource_id).tcard_req_table(i).start_date
1519 <= p_evaluation_stop_date
1520 AND g_mistc_pref_list(p_resource_id).tcard_req_table(i+1).start_date
1521 > p_evaluation_start_date
1522 THEN
1523 -- If the given time recipient id is in the list, return N
1524 -- Else Y.
1525 IF INSTR(g_mistc_pref_list(p_resource_id).tcard_req_table(i).attributelist,
1526 '-'||p_time_rec_id||'-') <> 0
1527 THEN
1528 RETURN 'N';
1529 ELSE
1530 RETURN 'Y' ;
1531 END IF;
1532 ELSE
1533 NULL;
1534 END IF;
1535 -- If there are no multiple ranges to check against, check if the date
1536 -- falls in here.
1537 ELSIF g_mistc_pref_list(p_resource_id).tcard_req_table(i).start_date
1538 <= p_evaluation_start_date
1539 OR g_mistc_pref_list(p_resource_id).tcard_req_table(i).start_date
1540 <= p_evaluation_start_date
1541 THEN
1542 -- Check if the time recipient exists here.
1543 IF INSTR(g_mistc_pref_list(p_resource_id).tcard_req_table(i).attributelist,
1544 '-'||p_time_rec_id||'-') <> 0
1545 THEN
1546 RETURN 'N';
1547 ELSE
1548 RETURN 'Y' ;
1549 END IF;
1550 ELSE
1551 -- The pref range is completely outside the given range, so
1552 -- return Y anyway.
1553 RETURN 'Y';
1554 END IF;
1555 END LOOP;
1556
1557 END check_tc_required;
1558
1559
1560
1561 FUNCTION check_appln_set ( p_resource_id IN NUMBER,
1562 p_start_date IN DATE DEFAULT NULL,
1563 p_stop_date IN DATE DEFAULT NULL,
1564 p_evaluation_start_date IN DATE,
1565 p_evaluation_stop_date IN DATE )
1566 RETURN varchar2
1567 IS
1568 BEGIN
1569
1570 -- If the preference does not exist, load it.
1571 IF NOT g_mistc_pref_list.EXISTS(p_resource_id)
1572 AND p_start_date IS NOT NULL
1573 AND p_stop_date IS NOT NULL
1574 THEN
1575 load_preferences(p_resource_id,
1576 p_start_date,
1577 p_stop_date) ;
1578 END IF;
1579
1580 -- Loop thru the table and find out if the preference matches.
1581 FOR i IN g_mistc_pref_list(p_resource_id).appln_set_table.FIRST..
1582 g_mistc_pref_list(p_resource_id).appln_set_table.LAST
1583 LOOP
1584 IF g_mistc_pref_list(p_resource_id).appln_set_table.EXISTS(i+1)
1585 THEN
1586 IF g_mistc_pref_list(p_resource_id).appln_set_table(i+1).start_date
1587 > p_evaluation_start_date
1588 AND g_mistc_pref_list(p_resource_id).appln_set_table(i).start_date
1589 <= p_evaluation_start_date
1590 THEN
1591 RETURN NVL(g_mistc_pref_list(p_resource_id).appln_set_table(i).attributelist,'0');
1592
1593 ELSIF g_mistc_pref_list(p_resource_id).appln_set_table(i).start_date
1594 <= p_evaluation_stop_date
1595 AND g_mistc_pref_list(p_resource_id).appln_set_table(i+1).start_date
1596 > p_evaluation_stop_date
1597 THEN
1598 RETURN NVL(g_mistc_pref_list(p_resource_id).appln_set_table(i).attributelist,'0');
1599
1600 ELSE
1601 NULL;
1602
1603 END IF;
1604
1605 ELSIF g_mistc_pref_list(p_resource_id).appln_set_table(i).start_date
1606 <= p_evaluation_start_date
1607 OR g_mistc_pref_list(p_resource_id).appln_set_table(i).start_date
1608 <= p_evaluation_stop_date
1609 THEN
1610 RETURN NVL(g_mistc_pref_list(p_resource_id).appln_set_table(i).attributelist,'0');
1611
1612 ELSE
1613 RETURN 0;
1614
1615 END IF;
1616
1617 END LOOP;
1618
1619 END check_appln_set;
1620
1621
1622 PROCEDURE clear_global_tables
1623 IS
1624
1625 BEGIN
1626 g_mistc_pref_list.DELETE;
1627 g_time_periods.DELETE;
1628 -- Bug 10084099
1629 -- New global table added.
1630 g_param_list.DELETE;
1631 END clear_global_tables;
1632
1633
1634
1635
1636 -- Function returns a Yes if the application set is ever
1637 -- in the person's preference.
1638 FUNCTION check_appln_set_id (p_resource_id IN NUMBER,
1639 p_start_date IN DATE,
1640 p_stop_date IN DATE,
1641 p_appln_set_id IN NUMBER )
1642 RETURN VARCHAR2
1643 IS
1644
1645 BEGIN
1646
1647 IF NOT g_mistc_pref_list.EXISTS(p_resource_id)
1648 AND p_start_date IS NOT NULL
1649 AND p_stop_date IS NOT NULL
1650 THEN
1651 load_preferences(p_resource_id,
1652 p_start_date,
1653 p_stop_date) ;
1654 END IF;
1655
1656 -- Loop thru the preferences and find out if you ever
1657 -- have the given application set id, for any date range.
1658 -- Used the report queries.
1659 FOR i IN g_mistc_pref_list(p_resource_id).appln_set_table.FIRST..
1660 g_mistc_pref_list(p_resource_id).appln_set_table.LAST
1661 LOOP
1662 IF g_mistc_pref_list(p_resource_id).appln_set_table(i).attributelist
1663 = to_char(p_appln_set_id)
1664 THEN
1665 RETURN 'Y';
1666 END IF;
1667 END LOOP;
1668
1669 RETURN 'N';
1670 END ;
1671
1672
1673 -- Returns a full name for the given date, no big deal.
1674 FUNCTION get_full_name(p_resource_id IN NUMBER,
1675 p_date IN DATE )
1676 RETURN VARCHAR2
1677 IS
1678
1679 l_full_name varchar2(255);
1680 BEGIN
1681 SELECT full_name
1682 INTO l_full_name
1683 FROM per_all_people_f ppf
1684 WHERE person_id = p_resource_id
1685 AND p_date BETWEEN effective_start_date
1686 AND effective_end_date;
1687
1688 RETURN l_full_name;
1689
1690 END;
1691
1692
1693 -- Calls hr_person_type_usage_info.get_user_person_type.
1694 -- Just a wrapper function, because it threw errors in the report
1695 -- queries because of the length.
1696 FUNCTION person_type(p_date IN DATE,
1697 p_resource_id IN NUMBER)
1698 RETURN VARCHAR2
1699 IS
1700
1701 BEGIN
1702
1703 RETURN hr_person_type_usage_info.get_user_person_type(p_date,p_resource_id);
1704
1705 END person_type;
1706
1707
1708 -- Bug 9272316
1709 -- Added the new functions to pick and cache the following values.
1710 -- Used in the Report queries.
1711
1712 FUNCTION get_payroll_name(p_payroll_id IN NUMBER)
1713 RETURN VARCHAR2
1714 IS
1715
1716 l_value VARCHAR2(400);
1717
1718 BEGIN
1719
1720 IF p_payroll_id IS NULL
1721 THEN
1722 RETURN NULL;
1723 END IF;
1724
1725 IF g_payroll_name.EXISTS(TO_CHAR(p_payroll_id))
1726 THEN
1727 RETURN g_payroll_name(TO_CHAR(p_payroll_id));
1728 END IF;
1729
1730 SELECT payroll_name
1731 INTO l_value
1732 FROM pay_payrolls_f
1733 WHERE payroll_id = p_payroll_id
1734 AND ROWNUM < 2 ;
1735
1736 g_payroll_name(TO_CHAR(p_payroll_id)) := l_value;
1737
1738 RETURN g_payroll_name(TO_CHAR(p_payroll_id));
1739
1740 EXCEPTION
1741 WHEN NO_DATA_FOUND
1742 THEN
1743 RETURN NULL;
1744
1745 END get_payroll_name;
1746
1747 FUNCTION get_org_name(p_org_id IN NUMBER)
1748 RETURN VARCHAR2
1749 IS
1750
1751 l_value VARCHAR2(400);
1752
1753 BEGIN
1754
1755 IF p_org_id IS NULL
1756 THEN
1757 RETURN NULL;
1758 END IF;
1759
1760 IF g_org_name.EXISTS(TO_CHAR(p_org_id))
1761 THEN
1762 RETURN g_org_name(TO_CHAR(p_org_id));
1763 END IF;
1764
1765 SELECT name
1766 INTO l_value
1767 FROM hr_organization_units
1768 WHERE organization_id = p_org_id
1769 AND ROWNUM < 2 ;
1770
1771 g_org_name(TO_CHAR(p_org_id)) := l_value;
1772
1773 RETURN g_org_name(TO_CHAR(p_org_id));
1774
1775 EXCEPTION
1776 WHEN NO_DATA_FOUND
1777 THEN
1778 RETURN NULL;
1779
1780 END get_org_name;
1781
1782
1783 FUNCTION get_locn_name(p_locn_id IN NUMBER)
1784 RETURN VARCHAR2
1785 IS
1786
1787 l_value VARCHAR2(400);
1788
1789 BEGIN
1790
1791 IF p_locn_id IS NULL
1792 THEN
1793 RETURN NULL;
1794 END IF;
1795
1796 IF g_locn_name.EXISTS(TO_CHAR(p_locn_id))
1797 THEN
1798 RETURN g_locn_name(TO_CHAR(p_locn_id));
1799 END IF;
1800
1801 SELECT location_code
1802 INTO l_value
1803 FROM hr_locations
1804 WHERE location_id = p_locn_id
1805 AND ROWNUM < 2 ;
1806
1807 g_locn_name(TO_CHAR(p_locn_id)) := l_value;
1808
1809 RETURN g_locn_name(TO_CHAR(p_locn_id));
1810
1811 EXCEPTION
1812 WHEN NO_DATA_FOUND
1813 THEN
1814 RETURN NULL;
1815
1816 END get_locn_name;
1817
1818
1819 END hxc_tpd_end;
[Home] [Help]