1 PACKAGE BODY hr_person_type_usage_info AS
2 /* $Header: hrptuinf.pkb 115.15 2004/01/23 06:45:05 njaladi noship $ */
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 -- ------------------------------------------------------------------------------
277 -- |--------------------------< get_emp_user_person_type >----------------------|
278 -- ------------------------------------------------------------------------------
279 FUNCTION get_emp_user_person_type
280 (p_effective_date IN DATE
281 ,p_person_id IN NUMBER
282 )
283 RETURN VARCHAR2
284 IS
285 CURSOR csr_person_types
286 IS
287 SELECT ttl.user_person_type
288 FROM per_person_types_tl ttl
289 ,per_person_types typ
290 ,per_person_type_usages_f ptu
291 WHERE ttl.language = userenv('LANG')
292 AND ttl.person_type_id = typ.person_type_id
293 AND typ.system_person_type IN ('EMP','EX_EMP')
294 AND typ.person_type_id = ptu.person_type_id
295 AND p_effective_date BETWEEN ptu.effective_start_date
296 AND ptu.effective_end_date
297 AND ptu.person_id = p_person_id;
298
299 l_user_person_type per_person_types_tl.user_person_type%type;
300 BEGIN
301 open csr_person_types;
302 fetch csr_person_types into l_user_person_type;
303 if csr_person_types%notfound then
304 close csr_person_types;
305 l_user_person_type:=null;
306 else
307 close csr_person_types;
308 end if;
309 RETURN l_user_person_type;
310 END get_emp_user_person_type;
311 --
312 -- ------------------------------------------------------------------------------
313 -- |--------------------------< GetSystemPersonType >---------------------------|
314 -- ------------------------------------------------------------------------------
315 FUNCTION GetSystemPersonType
316 (p_person_type_id IN NUMBER)
317 RETURN VARCHAR2
318 IS
319 CURSOR c_system_person_type
320 (p_person_type_id IN NUMBER)
321 IS
322 SELECT ppt.system_person_type
323 FROM per_person_types ppt
324 WHERE ppt.person_type_id = p_person_type_id;
325
326 l_system_person_type per_person_types.system_person_type%type;
327
328 BEGIN
329
330 OPEN c_system_person_type(p_person_type_id);
331
332 FETCH c_system_person_type INTO l_system_person_type;
333
334 CLOSE c_system_person_type;
335
336 RETURN l_system_person_type;
337
338 END GetSystemPersonType;
339 --
340 -- ------------------------------------------------------------------------------
341 -- |--------------------------< IsNonCoreHRPersonType >--------------------------|
342 -- ------------------------------------------------------------------------------
343 FUNCTION IsNonCoreHRPersonType
344 (p_person_type_usage_id IN NUMBER
345 ,p_effective_date IN DATE)
346 RETURN BOOLEAN
347 IS
348 CURSOR c_corehr_person_type
349 (p_person_type_usage_id IN NUMBER,
350 p_effective_date IN DATE)
351 IS
352 SELECT ppt.system_person_type
353 FROM per_person_types ppt ,
354 per_person_type_usages_f ptu
355 WHERE ptu.person_type_usage_id = p_person_type_usage_id
356 AND p_effective_date between
357 ptu.effective_start_date and
358 nvl(ptu.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))
359 AND ptu.person_type_id = ppt.person_type_id;
360
361 l_system_person_type per_person_types.system_person_type%type;
362
363 BEGIN
364
365 OPEN c_corehr_person_type(p_person_type_usage_id,p_effective_date);
366
367 FETCH c_corehr_person_type INTO l_system_person_type;
368 if c_corehr_person_type%NOTFOUND then
369 CLOSE c_corehr_person_type;
370 hr_utility.set_message(801,'NO_PTU_RECORD_EXISTS');
371 hr_utility.raise_error;
372 end if;
373
374 CLOSE c_corehr_person_type;
375
376 if not hr_api.not_exists_in_hrstanlookups
377 (p_effective_date => p_effective_date
378 ,p_lookup_type => 'HR_SYS_PTU'
379 ,p_lookup_code => l_system_person_type
380 )
381 then return FALSE;
382 else
383 return TRUE;
384 end if;
385
386 END IsNonCoreHRPersonType;
387 --
388 -- ------------------------------------------------------------------------------|
389 -- |--------------------------< FutSamePerTypeChgExists >------------------------|
390 -- ------------------------------------------------------------------------------|
391 FUNCTION FutSamePerTypeChgExists
392 (p_person_type_usage_id IN NUMBER
393 ,p_effective_date IN DATE)
394 RETURN BOOLEAN
395 IS
396 CURSOR c_ptu_record
397 (p_person_type_usage_id IN NUMBER,
398 p_effective_date IN DATE)
399 IS
400 SELECT GetSystemPersonType(ptu.person_type_id),
401 ptu.effective_start_date,
402 ptu.effective_end_date
403 FROM per_person_type_usages_f ptu
404 WHERE ptu.person_type_usage_id = p_person_type_usage_id
405 AND ptu.effective_start_date > p_effective_date
406 order by ptu.effective_start_date ;
407
408 l_person_type_id NUMBER(10);
409 l_effective_start_date DATE;
410 l_effective_end_date DATE;
411 l_current_system_person_type per_person_types.system_person_type%type;
412 l_future_system_person_type per_person_types.system_person_type%type;
413
414 BEGIN
415
416 SELECT GetSystemPersonType(ptu.person_type_id)
417 INTO l_current_system_person_type
418 FROM per_person_type_usages_f ptu
419 WHERE ptu.person_type_usage_id = p_person_type_usage_id
420 AND p_effective_date between
421 ptu.effective_start_date and ptu.effective_end_date;
422
423 OPEN c_ptu_record(p_person_type_usage_id,p_effective_date);
424
425 loop
426
427 FETCH c_ptu_record INTO l_future_system_person_type,
428 l_effective_start_date,
429 l_effective_end_date;
430 if c_ptu_record%NOTFOUND then
431 CLOSE c_ptu_record;
432 return FALSE;
433 end if;
434
435 if l_current_system_person_type <> l_future_system_person_type
436 then
437 close c_ptu_record;
438 return TRUE;
439 end if;
440
441 end loop;
442
443 END FutSamePerTypeChgExists;
444 --
445 -- ------------------------------------------------------------------------------
446 -- |--------------------------< FutSysPerTypeChgExists >--------------------------|
447 -- ------------------------------------------------------------------------------
448 FUNCTION FutSysPerTypeChgExists
449 (p_person_type_usage_id IN NUMBER
450 ,p_effective_date IN DATE)
451 RETURN BOOLEAN IS
452
453 l_result boolean := FALSE;
454 BEGIN
455
456 l_result := FutSamePerTypeChgExists(p_person_type_usage_id, p_effective_date);
457 return(l_result);
458
459 END FutSysPerTypeChgExists;
460 --
461 -- 3194314: Overloaded
462 -- ----------------------------------------------------------------------------|
463 -- |--------------------------< FutSysPerTypeChgExists >-----------------------|
464 -- ----------------------------------------------------------------------------|
465 FUNCTION FutSysPerTypeChgExists
466 (p_person_type_usage_id IN NUMBER
467 ,p_effective_date IN DATE
468 ,p_person_id IN NUMBER)
469 RETURN BOOLEAN
470 IS
471 CURSOR c_ptu_record
472 (p_person_type_usage_id IN NUMBER,
473 p_effective_date IN DATE
474 ,p_person_id IN NUMBER)
475 IS
476 SELECT GetSystemPersonType(ptu.person_type_id),
477 ptu.effective_start_date,
478 ptu.effective_end_date
479 FROM per_person_type_usages_f ptu
480 WHERE ptu.person_id = p_person_id
481 AND ptu.effective_start_date > p_effective_date
482 order by ptu.effective_start_date ;
483
484 CURSOR csr_pop_back_to_back
485 ( p_date_start IN DATE
486 ,p_person_id IN NUMBER)
487 IS
488 SELECT pp.period_of_placement_id
489 FROM per_periods_of_placement pp
490 WHERE pp.person_id = p_person_id
491 AND pp.actual_termination_date = p_date_start -1;
492 --
493 CURSOR csr_pos_back_to_back
494 ( p_date_start IN DATE
495 ,p_person_id IN NUMBER)
496 IS
497 SELECT ps.period_of_service_id
498 FROM per_periods_of_service ps
499 WHERE ps.person_id = p_person_id
500 AND ps.actual_termination_date = p_date_start -1;
501 --
502
503 l_person_type_id NUMBER(10);
504 l_effective_start_date DATE;
505 l_effective_end_date DATE;
506 l_current_system_person_type per_person_types.system_person_type%type;
507 l_future_system_person_type per_person_types.system_person_type%type;
508 l_current_end_date DATE;
509 l_is_back2back_hire number;
510 l_future_person BOOLEAN;
511
512
513 BEGIN
514
515 SELECT GetSystemPersonType(ptu.person_type_id), ptu.effective_end_date
516 INTO l_current_system_person_type, l_current_end_date
517 FROM per_person_type_usages_f ptu
518 WHERE ptu.person_type_usage_id = p_person_type_usage_id
519 AND ptu.person_id = p_person_id
520 AND p_effective_date between
521 ptu.effective_start_date and ptu.effective_end_date;
522
523 OPEN c_ptu_record(p_person_type_usage_id,p_effective_date,p_person_id);
524
525 LOOP -- Added for fix of 3285486
526 FETCH c_ptu_record INTO l_future_system_person_type,
527 l_effective_start_date,
528 l_effective_end_date;
529
530
531 if c_ptu_record%NOTFOUND then
532 CLOSE c_ptu_record;
533 return FALSE;
534
535 elsif l_current_system_person_type <> l_future_system_person_type
536 then
537 close c_ptu_record;
538 return TRUE;
539
540 elsif l_current_system_person_type = 'EMP' then
541
542 open csr_pos_back_to_back(l_effective_start_date, p_person_id);
543 fetch csr_pos_back_to_back into l_is_back2back_hire;
544 if csr_pos_back_to_back%FOUND then
545 close c_ptu_record;
546 close csr_pos_back_to_back;
547 return TRUE;
548 end if;
549 close csr_pos_back_to_back;
550
551 elsif l_current_system_person_type = 'CWK' then
552
553 open csr_pop_back_to_back(l_effective_start_date, p_person_id);
554 fetch csr_pop_back_to_back into l_is_back2back_hire;
555 if csr_pop_back_to_back%FOUND then
556 close c_ptu_record;
557 close csr_pop_back_to_back;
558 return TRUE;
559 end if;
560 close csr_pop_back_to_back;
561
562 end if;
563 End loop; -- Added for the fix of 3285486.
564 --Commented the following part for fix of 3285486.
565 /*
566 loop
567
568 FETCH c_ptu_record INTO l_future_system_person_type,
569 l_effective_start_date,
570 l_effective_end_date;
571
572
573 if c_ptu_record%NOTFOUND then
574 CLOSE c_ptu_record;
575 return FALSE;
576 end if;
577
578 if l_current_system_person_type <> l_future_system_person_type
579 then
580 close c_ptu_record;
581 return TRUE;
582 end if;
583
584 end loop;*/
585
586 END FutSysPerTypeChgExists;
587 --
588 --
589 -- ------------------------------------------------------------------------------
590 -- |--------------------------< is_person_of_type >-----------------------------|
591 -- ------------------------------------------------------------------------------
592 FUNCTION is_person_of_type
593 (p_effective_date IN DATE
594 ,p_person_id IN NUMBER
595 ,p_system_person_type IN VARCHAR2
596 )
597 RETURN BOOLEAN
598 IS
599
600 l_found BOOLEAN := FALSE;
601 l_dummy NUMBER;
602
603 CURSOR csr_person_type
604 IS
605 SELECT null
606 FROM per_person_types typ
607 ,per_person_type_usages_f ptu
608 WHERE typ.system_person_type = p_system_person_type
609 AND typ.person_type_id = ptu.person_type_id
610 AND p_effective_date BETWEEN ptu.effective_start_date
611 AND ptu.effective_end_date
612 AND ptu.person_id = p_person_id;
613
614 BEGIN
615
616 OPEN csr_person_type;
617 FETCH csr_person_type INTO l_dummy;
618
619 IF csr_person_type%FOUND THEN
620 l_found := TRUE;
621 ELSE
622 l_found := FALSE;
623 END IF;
624
625 CLOSE csr_person_type;
626
627 RETURN l_found;
628
629 END is_person_of_type;
630 --
631 -- ------------------------------------------------------------------------------
632 -- |-------------------------< is_person_a_worker >-----------------------------|
633 -- ------------------------------------------------------------------------------
634 --
635 FUNCTION is_person_a_worker
636 (p_effective_date IN DATE
637 ,p_person_id IN per_all_people_f.person_id%TYPE) RETURN BOOLEAN IS
638 --
639 -- Declare Local Variables
640 --
641 l_worker BOOLEAN := FALSE;
642 l_proc VARCHAR2(72);
643 --
644 BEGIN
645 --
646 g_debug := hr_utility.debug_enabled;
647 --
648 IF g_debug THEN
649 --
650 l_proc := g_package||'is_person_a_worker';
651 --
652 hr_utility.set_location('Entering : '||l_proc,10);
653 --
654 END IF;
655 --
656 l_worker := hr_person_type_usage_info.is_person_of_type
657 (p_effective_date => p_effective_date
658 ,p_person_id => p_person_id
659 ,p_system_person_type => 'EMP');
660 --
661 IF NOT l_worker THEN
662 --
663 IF g_debug THEN
664 --
665 hr_utility.set_location(l_proc,20);
666 --
667 END IF;
668 --
669 l_worker := hr_person_type_usage_info.is_person_of_type
670 (p_effective_date => p_effective_date
671 ,p_person_id => p_person_id
672 ,p_system_person_type => 'CWK');
673 --
674 ELSE
675 --
676 l_worker := FALSE;
677 --
678 END IF;
679 --
680 IF g_debug THEN
681 --
682 hr_utility.set_location('Leaving : '||l_proc,999);
683 --
684 END IF;
685 --
686 RETURN l_worker;
687 --
688 END is_person_a_worker;
689 --
690 -- ------------------------------------------------------------------------------
691 -- |--------------------------< get_person_actions >----------------------------|
692 -- ------------------------------------------------------------------------------
693 --
694 FUNCTION get_person_actions
695 (p_person_id IN NUMBER
696 ,p_effective_date IN DATE
697 ,p_customized_restriction_id IN NUMBER DEFAULT NULL)
698 RETURN g_actions_t IS
699
700 l_actions g_actions_t;
701 i number := 1;
702
703 /*This cursor fetches all actions that are available to the current
704 person subject to various limitations around their PTU records
705 (as specified in per_form_functions). For example, a person who is
706 just an EMP will have 'Create Applicant' whereas a person who is
707 already an EMP and APL will not.
708
709 If an action-based customized restriction exists the
710 action list is further restricted based on the CustomForm entrires.*/
711
712 CURSOR csr_get_actions IS
713 select distinct pff.result action
714 ,hr_general.decode_lookup('HR_PTU_ACTION_TYPES',pff.result) meaning
715 from per_form_functions pff
716 where pff.form = 'PERWSEPI'
717 and pff.function = 'ACTION_RESTRICTIONS'
718 and ((p_person_id is null and pff.input is null)
719 or (p_person_id is not null
720 and exists
721 (select null
722 from per_person_types ppt
723 ,per_person_type_usages_f ptu
724 where ptu.person_type_id = ppt.person_Type_id
725 and p_effective_date between
726 ptu.effective_start_date and ptu.effective_end_date
727 and ptu.person_id = p_person_id
728 and ppt.system_person_type = pff.input)
729 and not exists
730 (select null
731 from per_person_types ppt2
732 ,per_person_type_usages_f ptu2
733 where ptu2.person_type_id = ppt2.person_type_id
734 and p_effective_date between
735 ptu2.effective_start_date and ptu2.effective_end_date
736 and ptu2.person_id = p_person_id
737 and decode(pff.restriction_value, null, 0,
738 instr(pff.restriction_value,
739 ppt2.system_person_type)) > 0)))
740 and ((p_customized_restriction_id is null)
741 or (p_customized_restriction_id is not null
742 and (exists
743 (select null
744 from pay_restriction_values prv
745 where prv.customized_restriction_id = p_customized_restriction_id
746 and prv.restriction_code = 'PERSON_ACTION'
747 and prv.value = pff.result)
748 or not exists
749 (select null
750 from pay_restriction_values prv2
751 where prv2.customized_restriction_id = p_customized_restriction_id
752 and prv2.restriction_code = 'PERSON_ACTION'))))
753 order by 2;
754
755
756 BEGIN
757
758 IF l_actions.COUNT > 0 THEN
759 --
760 -- Flush the list.
761 --
762 FOR j IN l_actions.FIRST..l_actions.LAST LOOP
763 l_actions.DELETE(j);
764 END LOOP;
765
766 END IF;
767
768 --
769 -- Loop through the list of available actions and populate
770 -- the pl/sql table.
771 --
772 FOR csr_rec in csr_get_actions LOOP
773
774 l_actions(i).action := csr_rec.action;
775 l_actions(i).meaning := csr_rec.meaning;
776
777 i := i + 1;
778
779 END LOOP;
780
781 RETURN l_actions;
782
783 END get_person_actions;
784
785 --
786 END hr_person_type_usage_info;
[Home] [Help]