1 PACKAGE BODY hr_person_type_usage_info AS
2 /* $Header: hrptuinf.pkb 120.0.12010000.4 2009/11/12 05:44:44 sidsaxen ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_person_type_usage_info.';
7 g_debug boolean := hr_utility.debug_enabled;
8 --
9 --
10 -- ------------------------------------------------------------------------------
11 -- |---------------------< get_user_person_type_separator >---------------------|
12 -- ------------------------------------------------------------------------------
13 FUNCTION get_user_person_type_separator
14 RETURN g_user_person_type_separator%TYPE
15 IS
16 BEGIN
17 RETURN g_user_person_type_separator;
18 END get_user_person_type_separator;
19 --
20 -- ------------------------------------------------------------------------------
21 -- |-----------------------< get_default_person_type_id >-----------------------|
22 -- ------------------------------------------------------------------------------
23 FUNCTION get_default_person_type_id
24 (p_person_type_id IN NUMBER
25 )
26 RETURN NUMBER
27 IS
28 CURSOR csr_person_types
29 (p_person_type_id IN NUMBER
30 )
31 IS
32 SELECT dft.person_type_id
33 FROM per_person_types dft
34 ,per_person_types typ
35 WHERE dft.active_flag = 'Y'
36 AND dft.default_flag = 'Y'
37 AND dft.business_group_id = typ.business_group_id
38 AND dft.system_person_type = typ.system_person_type
39 AND typ.person_type_id = p_person_type_id;
40 l_person_type csr_person_types%ROWTYPE;
41 BEGIN
42 OPEN csr_person_types
43 (p_person_type_id => p_person_type_id
44 );
45 FETCH csr_person_types INTO l_person_type;
46 CLOSE csr_person_types;
47 RETURN l_person_type.person_type_id;
48 END get_default_person_type_id;
49 --
50 -- ------------------------------------------------------------------------------
51 -- |-----------------------< get_default_person_type_id >-----------------------|
52 -- ------------------------------------------------------------------------------
53 FUNCTION get_default_person_type_id
54 (p_business_group_id IN NUMBER
55 ,p_system_person_type IN VARCHAR2
56 )
57 RETURN NUMBER
58 IS
59 CURSOR csr_person_types
60 (p_business_group_id IN NUMBER
61 ,p_system_person_type IN VARCHAR2
62 )
63 IS
64 SELECT dft.person_type_id
65 FROM per_person_types dft
66 WHERE dft.active_flag = 'Y'
67 AND dft.default_flag = 'Y'
68 AND dft.business_group_id = p_business_group_id
69 AND dft.system_person_type = p_system_person_type;
70 l_person_type csr_person_types%ROWTYPE;
71 BEGIN
72 OPEN csr_person_types
73 (p_business_group_id => p_business_group_id
74 ,p_system_person_type => p_system_person_type
75 );
76 FETCH csr_person_types INTO l_person_type;
77 CLOSE csr_person_types;
78 RETURN l_person_type.person_type_id;
79 END get_default_person_type_id;
80 --
81 -- ------------------------------------------------------------------------------
82 -- |--------------------------< get_user_person_type >--------------------------|
83 -- ------------------------------------------------------------------------------
84 FUNCTION get_user_person_type
85 (p_effective_date IN DATE
86 ,p_person_id IN NUMBER
87 )
88 RETURN VARCHAR2
89 IS
90 CURSOR csr_person_types
91 (p_effective_date IN DATE
92 ,p_person_id IN NUMBER
93 )
94 IS
95 SELECT ttl.user_person_type
96 FROM per_person_types_tl ttl
97 ,per_person_types typ
98 ,per_person_type_usages_f ptu
99 WHERE ttl.language = userenv('LANG')
100 AND ttl.person_type_id = typ.person_type_id
101 AND typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
102 AND typ.person_type_id = ptu.person_type_id
103 AND p_effective_date BETWEEN ptu.effective_start_date
104 AND ptu.effective_end_date
105 AND ptu.person_id = p_person_id
106 ORDER BY DECODE(typ.system_person_type
107 ,'EMP' ,1
108 ,'CWK' ,2
109 ,'APL' ,3
110 ,'EX_EMP',4
111 ,'EX_CWK',5
112 ,'EX_APL',6
113 ,7
114 );
115 l_user_person_type VARCHAR2(2000);
116 l_separator g_user_person_type_separator%TYPE;
117 BEGIN
118 l_separator := get_user_person_type_separator();
119 FOR l_person_type IN csr_person_types
120 (p_effective_date => p_effective_date
121 ,p_person_id => p_person_id
122 )
123 LOOP
124 IF (l_user_person_type IS NULL)
125 THEN
126 l_user_person_type := l_person_type.user_person_type;
127 ELSE
128 l_user_person_type := l_user_person_type
129 || l_separator
130 || l_person_type.user_person_type;
131 END IF;
132 END LOOP;
133 RETURN l_user_person_type;
134 END get_user_person_type;
135 --
136 -- -----------------------------------------------------------------------------
137 -- |----------------------< get_worker_user_person_type >----------------------|
138 -- -----------------------------------------------------------------------------
139 FUNCTION get_worker_user_person_type
140 (p_effective_date IN DATE
141 ,p_person_id IN NUMBER
142 )
143 RETURN VARCHAR2
144 IS
145 CURSOR csr_person_types
146 (p_effective_date IN DATE
147 ,p_person_id IN NUMBER
148 )
149 IS
150 SELECT ttl.user_person_type
151 FROM per_person_types_tl ttl
152 ,per_person_types typ
153 ,per_person_type_usages_f ptu
154 WHERE ttl.language = userenv('LANG')
155 AND ttl.person_type_id = typ.person_type_id
156 AND typ.system_person_type IN ('EMP','CWK')
157 AND typ.person_type_id = ptu.person_type_id
158 AND p_effective_date BETWEEN ptu.effective_start_date
159 AND ptu.effective_end_date
160 AND ptu.person_id = p_person_id
161 ORDER BY DECODE(typ.system_person_type
162 ,'EMP' ,1
163 ,'CWK' ,2
164 );
165 l_user_person_type VARCHAR2(2000);
166 l_separator g_user_person_type_separator%TYPE;
167 BEGIN
168 l_separator := get_user_person_type_separator();
169 FOR l_person_type IN csr_person_types
170 (p_effective_date => p_effective_date
171 ,p_person_id => p_person_id
172 )
173 LOOP
174 IF (l_user_person_type IS NULL)
175 THEN
176 l_user_person_type := l_person_type.user_person_type;
177 ELSE
178 l_user_person_type := l_user_person_type
179 || l_separator
180 || l_person_type.user_person_type;
181 END IF;
182 END LOOP;
183 RETURN l_user_person_type;
184 END get_worker_user_person_type;
185 --
186 -- -----------------------------------------------------------------------------
187 -- |----------------------< get_worker_number >--------------------------------|
188 -- -----------------------------------------------------------------------------
189 FUNCTION get_worker_number
190 (p_effective_date IN DATE
191 ,p_person_id IN NUMBER
192 )
193 RETURN VARCHAR2
194 IS
195
196 --
197 -- Fetch the person's worker number details.
198 --
199 CURSOR csr_get_person_details IS
200 SELECT papf.employee_number
201 ,papf.npw_number
202 ,papf.current_employee_flag
203 ,papf.current_npw_flag
204 FROM per_all_people_f papf
205 WHERE papf.person_id = p_person_id
206 AND p_effective_date BETWEEN
207 papf.effective_start_date AND papf.effective_end_date;
208
209 l_employee_number per_all_people_f.employee_number%TYPE;
210 l_npw_number per_all_people_f.npw_number%TYPE;
211 l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
212 l_current_npw_flag per_all_people_f.current_npw_flag%TYPE;
213 l_worker_number per_all_people_f.employee_number%TYPE;
214
215 BEGIN
216
217 OPEN csr_get_person_details;
218 FETCH csr_get_person_details INTO l_employee_number
219 ,l_npw_number
220 ,l_current_employee_flag
221 ,l_current_npw_flag;
222 CLOSE csr_get_person_details;
223
224 --
225 -- Set the worker number based on the status of the
226 -- current flags. If the person is not an active worker,
227 -- the worker number will not be set.
228 --
229 IF NVL(l_current_employee_flag, 'N') = 'Y' THEN
230 l_worker_number := l_employee_number;
231 ELSIF NVL(l_current_npw_flag, 'N') = 'Y' THEN
232 l_worker_number := l_npw_number;
233 END IF;
234
235 RETURN l_worker_number;
236
237 END get_worker_number;
238 --
239 -- -----------------------------------------------------------------------------
240 -- |--------------------------< get_apl_user_person_type >---------------------|
241 -- -----------------------------------------------------------------------------
242 FUNCTION get_apl_user_person_type
243 (p_effective_date IN DATE
244 ,p_person_id IN NUMBER
245 )
246 RETURN VARCHAR2
247 IS
248 CURSOR csr_person_types
249 IS
250 SELECT ttl.user_person_type
251 FROM per_person_types_tl ttl
252 ,per_person_types typ
253 ,per_person_type_usages_f ptu
254 WHERE ttl.language = userenv('LANG')
255 AND ttl.person_type_id = typ.person_type_id
256 AND typ.system_person_type IN ('APL','EX_APL')
257 AND typ.person_type_id = ptu.person_type_id
258 AND p_effective_date BETWEEN ptu.effective_start_date
259 AND ptu.effective_end_date
260 AND ptu.person_id = p_person_id;
261
262 l_user_person_type per_person_types_tl.user_person_type%type;
263 BEGIN
264 open csr_person_types;
265 fetch csr_person_types into l_user_person_type;
266 if csr_person_types%notfound then
267 close csr_person_types;
268 l_user_person_type:=null;
269 else
270 close csr_person_types;
271 end if;
272 RETURN l_user_person_type;
273 END get_apl_user_person_type;
274 --
275
276 FUNCTION get_emp_person_type_id
277 (p_effective_date IN DATE
278 ,p_person_id IN NUMBER
279 )
280 RETURN VARCHAR2
281 IS
282 CURSOR emp_person_type IS
283 SELECT ptu.person_type_id FROM
284 per_person_type_usages_f ptu, per_person_types ppt WHERE
285 ptu.person_id = p_person_id and
286 p_effective_date between ptu.effective_start_date and ptu.effective_end_date
287 and ptu.person_type_id = ppt.person_type_id and ppt.system_person_type='EMP';
288 l_emp_person_type_id number;
289 BEGIN
290 OPEN emp_person_type;
291 FETCH emp_person_type into l_emp_person_type_id;
292 CLOSE emp_person_type;
293 return l_emp_person_type_id;
294 END get_emp_person_type_id;
295
296 --
297 -- ------------------------------------------------------------------------------
298 -- |--------------------------< get_emp_user_person_type >----------------------|
299 -- ------------------------------------------------------------------------------
300 FUNCTION get_emp_user_person_type
301 (p_effective_date IN DATE
302 ,p_person_id IN NUMBER
303 )
304 RETURN VARCHAR2
305 IS
306 CURSOR csr_person_types
307 IS
308 SELECT ttl.user_person_type
309 FROM per_person_types_tl ttl
310 ,per_person_types typ
311 ,per_person_type_usages_f ptu
312 WHERE ttl.language = userenv('LANG')
313 AND ttl.person_type_id = typ.person_type_id
314 AND typ.system_person_type IN ('EMP','EX_EMP')
315 AND typ.person_type_id = ptu.person_type_id
316 AND p_effective_date BETWEEN ptu.effective_start_date
317 AND ptu.effective_end_date
318 AND ptu.person_id = p_person_id;
319
320 l_user_person_type per_person_types_tl.user_person_type%type;
321 BEGIN
322 open csr_person_types;
323 fetch csr_person_types into l_user_person_type;
324 if csr_person_types%notfound then
325 close csr_person_types;
326 l_user_person_type:=null;
327 else
328 close csr_person_types;
329 end if;
330 RETURN l_user_person_type;
331 END get_emp_user_person_type;
332 --
333 -- ------------------------------------------------------------------------------
334 -- |--------------------------< GetSystemPersonType >---------------------------|
335 -- ------------------------------------------------------------------------------
336 FUNCTION GetSystemPersonType
337 (p_person_type_id IN NUMBER)
338 RETURN VARCHAR2
339 IS
340 CURSOR c_system_person_type
341 (p_person_type_id IN NUMBER)
342 IS
343 SELECT ppt.system_person_type
344 FROM per_person_types ppt
345 WHERE ppt.person_type_id = p_person_type_id;
346
347 l_system_person_type per_person_types.system_person_type%type;
348
349 BEGIN
350
351 OPEN c_system_person_type(p_person_type_id);
352
353 FETCH c_system_person_type INTO l_system_person_type;
354
355 CLOSE c_system_person_type;
356
357 RETURN l_system_person_type;
358
359 END GetSystemPersonType;
360 --
361 -- ------------------------------------------------------------------------------
362 -- |--------------------------< IsNonCoreHRPersonType >--------------------------|
363 -- ------------------------------------------------------------------------------
364 FUNCTION IsNonCoreHRPersonType
365 (p_person_type_usage_id IN NUMBER
366 ,p_effective_date IN DATE)
367 RETURN BOOLEAN
368 IS
369 CURSOR c_corehr_person_type
370 (p_person_type_usage_id IN NUMBER,
371 p_effective_date IN DATE)
372 IS
373 SELECT ppt.system_person_type
374 FROM per_person_types ppt ,
375 per_person_type_usages_f ptu
376 WHERE ptu.person_type_usage_id = p_person_type_usage_id
377 AND p_effective_date between
378 ptu.effective_start_date and
379 nvl(ptu.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))
380 AND ptu.person_type_id = ppt.person_type_id;
381
382 l_system_person_type per_person_types.system_person_type%type;
383
384 BEGIN
385
386 OPEN c_corehr_person_type(p_person_type_usage_id,p_effective_date);
387
388 FETCH c_corehr_person_type INTO l_system_person_type;
389 if c_corehr_person_type%NOTFOUND then
390 CLOSE c_corehr_person_type;
391 hr_utility.set_message(801,'NO_PTU_RECORD_EXISTS');
392 hr_utility.raise_error;
393 end if;
394
395 CLOSE c_corehr_person_type;
396
397 if not hr_api.not_exists_in_hrstanlookups
398 (p_effective_date => p_effective_date
399 ,p_lookup_type => 'HR_SYS_PTU'
400 ,p_lookup_code => l_system_person_type
401 )
402 then return FALSE;
403 else
404 return TRUE;
405 end if;
406
407 END IsNonCoreHRPersonType;
408 --
409 -- ------------------------------------------------------------------------------|
410 -- |--------------------------< FutSamePerTypeChgExists >------------------------|
411 -- ------------------------------------------------------------------------------|
412 FUNCTION FutSamePerTypeChgExists
413 (p_person_type_usage_id IN NUMBER
414 ,p_effective_date IN DATE)
415 RETURN BOOLEAN
416 IS
417 CURSOR c_ptu_record
418 (p_person_type_usage_id IN NUMBER,
419 p_effective_date IN DATE)
420 IS
421 SELECT GetSystemPersonType(ptu.person_type_id),
422 ptu.effective_start_date,
423 ptu.effective_end_date
424 FROM per_person_type_usages_f ptu
425 WHERE ptu.person_type_usage_id = p_person_type_usage_id
426 AND ptu.effective_start_date > p_effective_date
427 order by ptu.effective_start_date ;
428
429 --start changes for bug 8628859
430 CURSOR csr_pop_back_to_back
431 ( p_date_start IN DATE
432 ,p_person_id IN NUMBER)
433 IS
434 SELECT pp.period_of_placement_id
435 FROM per_periods_of_placement pp
436 WHERE pp.person_id = p_person_id
437 AND pp.actual_termination_date = p_date_start -1;
438 --
439 CURSOR csr_pos_back_to_back
440 ( p_date_start IN DATE
441 ,p_person_id IN NUMBER)
442 IS
443 SELECT ps.period_of_service_id
444 FROM per_periods_of_service ps
445 WHERE ps.person_id = p_person_id
446 AND ps.actual_termination_date = p_date_start -1;
447
448 l_is_back2back_hire NUMBER;
449 l_person_id NUMBER;
450 --End changes for bug 8628859
451
452 l_person_type_id NUMBER(10);
453 l_effective_start_date DATE;
454 l_effective_end_date DATE;
455 l_current_system_person_type per_person_types.system_person_type%type;
456 l_future_system_person_type per_person_types.system_person_type%type;
457
458 BEGIN
459
460 SELECT GetSystemPersonType(ptu.person_type_id), ptu.person_id
461 INTO l_current_system_person_type, l_person_id
462 FROM per_person_type_usages_f ptu
463 WHERE ptu.person_type_usage_id = p_person_type_usage_id
464 AND p_effective_date between
465 ptu.effective_start_date and ptu.effective_end_date;
466
467 OPEN c_ptu_record(p_person_type_usage_id,p_effective_date);
468
469 loop
470
471 FETCH c_ptu_record INTO l_future_system_person_type,
472 l_effective_start_date,
473 l_effective_end_date;
474 if c_ptu_record%NOTFOUND then
475 CLOSE c_ptu_record;
476 return FALSE;
477 end if;
478
479 if l_current_system_person_type <> l_future_system_person_type
480 then
481 close c_ptu_record;
482 return TRUE;
483
484 --Start changes for bug 8628859
485 elsif l_current_system_person_type = 'EMP' then
486
487 open csr_pos_back_to_back(l_effective_start_date, l_person_id);
488 fetch csr_pos_back_to_back into l_is_back2back_hire;
489 if csr_pos_back_to_back%FOUND then
490 close c_ptu_record;
491 close csr_pos_back_to_back;
492 return TRUE;
493 end if;
494 close csr_pos_back_to_back;
495
496 elsif l_current_system_person_type = 'CWK' then
497
498 open csr_pop_back_to_back(l_effective_start_date, l_person_id);
499 fetch csr_pop_back_to_back into l_is_back2back_hire;
500 if csr_pop_back_to_back%FOUND then
501 close c_ptu_record;
502 close csr_pop_back_to_back;
503 return TRUE;
504 end if;
505 close csr_pop_back_to_back;
506 --End changes for bug 8628859
507
508 end if;
509
510 end loop;
511
512 END FutSamePerTypeChgExists;
513 --
514 -- ------------------------------------------------------------------------------
515 -- |--------------------------< FutSysPerTypeChgExists >--------------------------|
516 -- ------------------------------------------------------------------------------
517 FUNCTION FutSysPerTypeChgExists
518 (p_person_type_usage_id IN NUMBER
519 ,p_effective_date IN DATE)
520 RETURN BOOLEAN IS
521
522 l_result boolean := FALSE;
523 BEGIN
524
525 l_result := FutSamePerTypeChgExists(p_person_type_usage_id, p_effective_date);
526 return(l_result);
527
528 END FutSysPerTypeChgExists;
529 --
530 -- 3194314: Overloaded
531 -- ----------------------------------------------------------------------------|
532 -- |--------------------------< FutSysPerTypeChgExists >-----------------------|
533 -- ----------------------------------------------------------------------------|
534 FUNCTION FutSysPerTypeChgExists
535 (p_person_type_usage_id IN NUMBER
536 ,p_effective_date IN DATE
537 ,p_person_id IN NUMBER)
538 RETURN BOOLEAN
539 IS
540 CURSOR c_ptu_record
541 (p_person_type_usage_id IN NUMBER,
542 p_effective_date IN DATE
543 ,p_person_id IN NUMBER)
544 IS
545 SELECT GetSystemPersonType(ptu.person_type_id),
546 ptu.effective_start_date,
547 ptu.effective_end_date
548 FROM per_person_type_usages_f ptu
549 WHERE ptu.person_id = p_person_id
550 AND ptu.effective_start_date > p_effective_date
551 order by ptu.effective_start_date ;
552
553 CURSOR csr_pop_back_to_back
554 ( p_date_start IN DATE
555 ,p_person_id IN NUMBER)
556 IS
557 SELECT pp.period_of_placement_id
558 FROM per_periods_of_placement pp
559 WHERE pp.person_id = p_person_id
560 AND pp.actual_termination_date = p_date_start -1;
561 --
562 CURSOR csr_pos_back_to_back
563 ( p_date_start IN DATE
564 ,p_person_id IN NUMBER)
565 IS
566 SELECT ps.period_of_service_id
567 FROM per_periods_of_service ps
568 WHERE ps.person_id = p_person_id
569 AND ps.actual_termination_date = p_date_start -1;
570 --
571
572 l_person_type_id NUMBER(10);
573 l_effective_start_date DATE;
574 l_effective_end_date DATE;
575 l_current_system_person_type per_person_types.system_person_type%type;
576 l_future_system_person_type per_person_types.system_person_type%type;
577 l_current_end_date DATE;
578 l_is_back2back_hire number;
579 l_future_person BOOLEAN;
580
581
582 BEGIN
583
584 SELECT GetSystemPersonType(ptu.person_type_id), ptu.effective_end_date
585 INTO l_current_system_person_type, l_current_end_date
586 FROM per_person_type_usages_f ptu
587 WHERE ptu.person_type_usage_id = p_person_type_usage_id
588 AND ptu.person_id = p_person_id
589 AND p_effective_date between
590 ptu.effective_start_date and ptu.effective_end_date;
591
592 OPEN c_ptu_record(p_person_type_usage_id,p_effective_date,p_person_id);
593
594 LOOP -- Added for fix of 3285486
595 FETCH c_ptu_record INTO l_future_system_person_type,
596 l_effective_start_date,
597 l_effective_end_date;
598
599
600 if c_ptu_record%NOTFOUND then
601 CLOSE c_ptu_record;
602 return FALSE;
603
604 elsif l_current_system_person_type <> l_future_system_person_type
605 then
606 close c_ptu_record;
607 return TRUE;
608
609 elsif l_current_system_person_type = 'EMP' then
610
611 open csr_pos_back_to_back(l_effective_start_date, p_person_id);
612 fetch csr_pos_back_to_back into l_is_back2back_hire;
613 if csr_pos_back_to_back%FOUND then
614 close c_ptu_record;
615 close csr_pos_back_to_back;
616 return TRUE;
617 end if;
618 close csr_pos_back_to_back;
619
620 elsif l_current_system_person_type = 'CWK' then
621
622 open csr_pop_back_to_back(l_effective_start_date, p_person_id);
623 fetch csr_pop_back_to_back into l_is_back2back_hire;
624 if csr_pop_back_to_back%FOUND then
625 close c_ptu_record;
626 close csr_pop_back_to_back;
627 return TRUE;
628 end if;
629 close csr_pop_back_to_back;
630
631 end if;
632 End loop; -- Added for the fix of 3285486.
633 --Commented the following part for fix of 3285486.
634 /*
635 loop
636
637 FETCH c_ptu_record INTO l_future_system_person_type,
638 l_effective_start_date,
639 l_effective_end_date;
640
641
642 if c_ptu_record%NOTFOUND then
643 CLOSE c_ptu_record;
644 return FALSE;
645 end if;
646
647 if l_current_system_person_type <> l_future_system_person_type
648 then
649 close c_ptu_record;
650 return TRUE;
651 end if;
652
653 end loop;*/
654
655 END FutSysPerTypeChgExists;
656 --
657 --
658 -- ------------------------------------------------------------------------------
659 -- |--------------------------< is_person_of_type >-----------------------------|
660 -- ------------------------------------------------------------------------------
661 FUNCTION is_person_of_type
662 (p_effective_date IN DATE
663 ,p_person_id IN NUMBER
664 ,p_system_person_type IN VARCHAR2
665 )
666 RETURN BOOLEAN
667 IS
668
669 l_found BOOLEAN := FALSE;
670 l_dummy NUMBER;
671
672 CURSOR csr_person_type
673 IS
674 SELECT null
675 FROM per_person_types typ
676 ,per_person_type_usages_f ptu
677 WHERE typ.system_person_type = p_system_person_type
678 AND typ.person_type_id = ptu.person_type_id
679 AND p_effective_date BETWEEN ptu.effective_start_date
680 AND ptu.effective_end_date
681 AND ptu.person_id = p_person_id;
682
683 BEGIN
684
685 OPEN csr_person_type;
686 FETCH csr_person_type INTO l_dummy;
687
688 IF csr_person_type%FOUND THEN
689 l_found := TRUE;
690 ELSE
691 l_found := FALSE;
692 END IF;
693
694 CLOSE csr_person_type;
695
696 RETURN l_found;
697
698 END is_person_of_type;
699 --
700 -- ------------------------------------------------------------------------------
701 -- |-------------------------< is_person_a_worker >-----------------------------|
702 -- ------------------------------------------------------------------------------
703 --
704 FUNCTION is_person_a_worker
705 (p_effective_date IN DATE
706 ,p_person_id IN per_all_people_f.person_id%TYPE) RETURN BOOLEAN IS
707 --
708 -- Declare Local Variables
709 --
710 l_worker BOOLEAN := FALSE;
711 l_proc VARCHAR2(72);
712 --
713 BEGIN
714 --
715 g_debug := hr_utility.debug_enabled;
716 --
717 IF g_debug THEN
718 --
719 l_proc := g_package||'is_person_a_worker';
720 --
721 hr_utility.set_location('Entering : '||l_proc,10);
722 --
723 END IF;
724 --
725 l_worker := hr_person_type_usage_info.is_person_of_type
726 (p_effective_date => p_effective_date
727 ,p_person_id => p_person_id
728 ,p_system_person_type => 'EMP');
729 --
730 IF NOT l_worker THEN
731 --
732 IF g_debug THEN
733 --
734 hr_utility.set_location(l_proc,20);
735 --
736 END IF;
737 --
738 l_worker := hr_person_type_usage_info.is_person_of_type
739 (p_effective_date => p_effective_date
740 ,p_person_id => p_person_id
741 ,p_system_person_type => 'CWK');
742 --
743 ELSE
744 --
745 l_worker := FALSE;
746 --
747 END IF;
748 --
749 IF g_debug THEN
750 --
751 hr_utility.set_location('Leaving : '||l_proc,999);
752 --
753 END IF;
754 --
755 RETURN l_worker;
756 --
757 END is_person_a_worker;
758 --
759 -- ------------------------------------------------------------------------------
760 -- |--------------------------< get_person_actions >----------------------------|
761 -- ------------------------------------------------------------------------------
762 --
763 FUNCTION get_person_actions
764 (p_person_id IN NUMBER
765 ,p_effective_date IN DATE
766 ,p_customized_restriction_id IN NUMBER DEFAULT NULL)
767 RETURN g_actions_t IS
768
769 l_actions g_actions_t;
770 i number := 1;
771
772 /*This cursor fetches all actions that are available to the current
773 person subject to various limitations around their PTU records
774 (as specified in per_form_functions). For example, a person who is
775 just an EMP will have 'Create Applicant' whereas a person who is
776 already an EMP and APL will not.
777
778 If an action-based customized restriction exists the
779 action list is further restricted based on the CustomForm entrires.*/
780
781 CURSOR csr_get_actions IS
782 select distinct pff.result action
783 ,hr_general.decode_lookup('HR_PTU_ACTION_TYPES',pff.result) meaning
784 from per_form_functions pff
785 where pff.form = 'PERWSEPI'
786 and pff.function = 'ACTION_RESTRICTIONS'
787 and ((p_person_id is null and pff.input is null)
788 or (p_person_id is not null
789 and exists
790 (select null
791 from per_person_types ppt
792 ,per_person_type_usages_f ptu
793 where ptu.person_type_id = ppt.person_Type_id
794 and p_effective_date between
795 ptu.effective_start_date and ptu.effective_end_date
796 and ptu.person_id = p_person_id
797 and ppt.system_person_type = pff.input)
798 and not exists
799 (select null
800 from per_person_types ppt2
801 ,per_person_type_usages_f ptu2
802 where ptu2.person_type_id = ppt2.person_type_id
803 and p_effective_date between
804 ptu2.effective_start_date and ptu2.effective_end_date
805 and ptu2.person_id = p_person_id
806 and decode(pff.restriction_value, null, 0,
807 instr(pff.restriction_value,
808 ppt2.system_person_type)) > 0)))
809 and ((p_customized_restriction_id is null)
810 or (p_customized_restriction_id is not null
811 and (exists
812 (select null
813 from pay_restriction_values prv
814 where prv.customized_restriction_id = p_customized_restriction_id
815 and prv.restriction_code = 'PERSON_ACTION'
816 and prv.value = pff.result)
817 or not exists
818 (select null
819 from pay_restriction_values prv2
820 where prv2.customized_restriction_id = p_customized_restriction_id
821 and prv2.restriction_code = 'PERSON_ACTION'))))
822 order by 2;
823
824
825 BEGIN
826
827 IF l_actions.COUNT > 0 THEN
828 --
829 -- Flush the list.
830 --
831 FOR j IN l_actions.FIRST..l_actions.LAST LOOP
832 l_actions.DELETE(j);
833 END LOOP;
834
835 END IF;
836
837 --
838 -- Loop through the list of available actions and populate
839 -- the pl/sql table.
840 --
841 FOR csr_rec in csr_get_actions LOOP
842
843 l_actions(i).action := csr_rec.action;
844 l_actions(i).meaning := csr_rec.meaning;
845
846 i := i + 1;
847
848 END LOOP;
849
850 RETURN l_actions;
851
852 END get_person_actions;
853
854 --
855 END hr_person_type_usage_info;
[Home] [Help]