1 Package body hxc_timekeeper_group_api as
2 /* $Header: hxctkgapi.pkb 120.2 2005/09/23 09:38:52 nissharm noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hxc_timekeeper_group_api.';
7
8 g_debug boolean := hr_utility.debug_enabled;
9
10 -- ----------------------------------------------------------------------------
11 -- |--------------------------< create_timekeeper_group >---------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 --
15 -- Description:
16 --
17 -- This API creates a timekeeper group with a given name
18 --
19 -- Prerequisites:
20 --
21 -- None
22 --
23 -- In Parameters:
24 -- Name Reqd Type Description
25 --
26 -- p_validate No boolean If TRUE then the database
27 -- remains unchanged. If FALSE
28 -- then a new data_approval_rule
29 -- is created. Default is FALSE.
30 -- p_tk_group_id No number Primary Key for timekeeper group group
31 -- p_object_version_number No number Object Version Number
32 -- p_tk_Group_name Yes varchar2 tk group Name for the timekeeper_group
33 -- p_tk_resource_id Yes number Resource id for the person creating the
34 -- timekeeper group
35 -- p_business_group_id Yes number Business Group ID
36 -- Post Success:
37 --
38 -- when the timekeeper_group has been created successfully the following
39 -- out parameters are set.
40 --
41 -- Name Type Description
42 --
43 -- p_tk_group_id Number Primary Key for the new rule
44 -- p_object_version_number Number Object version number for the
45 -- new tk group
46 --
47 -- Post Failure:
48 --
49 -- The timekeeper group will not be inserted and an application error raised
50 --
51 -- Access Status:
52 -- Public.
53 --
54 --
55 procedure create_timekeeper_group
56 (p_validate in boolean default false
57 ,p_tk_group_id in out nocopy number
58 ,p_object_version_number in out nocopy number
59 ,p_tk_group_name in varchar2
60 ,p_tk_resource_id in number
61 ,p_business_group_id in number
62 ) is
63 --
64 -- Declare cursors and local variables
65 --
66 l_proc varchar2(72);
67 l_object_version_number hxc_tk_groups.object_version_number%TYPE;
68 l_tk_group_id hxc_tk_groups.tk_group_id%TYPE;
69 --
70 begin
71 g_debug := hr_utility.debug_enabled;
72 --
73
74 --
75 if g_debug then
76 l_proc := g_package||' create_timekeeper_group';
77 hr_utility.set_location('Entering:'|| l_proc, 10);
78 end if;
79 --
80 -- Issue a savepoint if operating in validation only mode
81 --
82 savepoint create_timekeeper_group;
83 --
84 hxc_timekeeper_group_api.chk_name
85 ( p_tk_group_name => p_tk_group_name
86 , p_tk_group_id => p_tk_group_id
87 , p_tk_resource_id => p_tk_resource_id
88 , p_business_group_id =>p_business_group_id
89 );
90
91 hxc_timekeeper_group_api.chk_tk_resource_id ( p_tk_resource_id => p_tk_resource_id );
92
93 if g_debug then
94 hr_utility.set_location(l_proc, 20);
95 end if;
96 --
97 -- Call Before Process User Hook
98 --
99 begin
100 hxc_timekeeper_group_BK_1.create_timekeeper_group_b
101 (p_tk_group_id => p_tk_group_id
102 ,p_object_version_number => p_object_version_number
103 ,p_tk_group_name => p_tk_group_name
104 ,p_tk_resource_id => p_tk_resource_id
105 ,p_business_group_id => p_business_group_id
106 );
107 exception
108 when hr_api.cannot_find_prog_unit then
109 hr_api.cannot_find_prog_unit_error
110 (p_module_name => 'create_timekeeper_group'
111 ,p_hook_type => 'BP'
112 );
113 end;
114 --
115 if g_debug then
116 hr_utility.set_location(l_proc, 30);
117 end if;
118 --
119 -- Process Logic
120 --
121 --
122 if g_debug then
123 hr_utility.set_location(l_proc, 40);
124 end if;
125 --
126 -- call row handler
127 --
128 hxc_tkg_ins.ins (
129 p_tk_group_name => p_tk_group_name
130 ,p_tk_group_id => l_tk_group_id
131 ,p_tk_resource_id => p_tk_resource_id
132 ,p_object_version_number => l_object_version_number
133 ,p_business_group_id => p_business_group_id);
134 --
135 if g_debug then
136 hr_utility.set_location(l_proc, 50);
137 end if;
138 --
139 -- Call After Process User Hook
140 --
141 begin
142 hxc_timekeeper_group_BK_1.create_timekeeper_group_a
143 (p_tk_group_id => l_tk_group_id
144 ,p_object_version_number => l_object_version_number
145 ,p_tk_group_name => p_tk_group_name
146 ,p_tk_resource_id => p_tk_resource_id
147 ,p_business_group_id => p_business_group_id
148 );
149 exception
150 when hr_api.cannot_find_prog_unit then
151 hr_api.cannot_find_prog_unit_error
155 end;
152 (p_module_name => 'create_timekeeper_group'
153 ,p_hook_type => 'AP'
154 );
156 --
157 if g_debug then
158 hr_utility.set_location(l_proc, 60);
159 end if;
160 --
161 -- When in validation only mode raise the Validate_Enabled exception
162 --
163 if p_validate then
164 raise hr_api.validate_enabled;
165 end if;
166 --
167 if g_debug then
168 hr_utility.set_location(' Leaving:'||l_proc, 70);
169 end if;
170 --
171 -- Set all output arguments
172 --
173 p_tk_group_id := l_tk_group_id;
174 p_object_version_number := l_object_version_number;
175 --
176 exception
177 --
178 when hr_api.validate_enabled then
179 --
180 -- As the Validate_Enabled exception has been raised
181 -- we must rollback to the savepoint
182 --
183 ROLLBACK TO create_timekeeper_group;
184 --
185 -- Only set output warning arguments
186 -- (Any key or derived arguments must be set to null
187 -- when validation only mode is being used.)
188 --
189 p_tk_group_id := null;
190 p_object_version_number := null;
191 --
192 if g_debug then
193 hr_utility.set_location(' Leaving:'||l_proc, 80);
194 end if;
195 --
196 when others then
197 --
198 -- A validation or unexpected error has occured
199 --
200 if g_debug then
201 hr_utility.trace('In exeception');
202 end if;
203 ROLLBACK TO create_timekeeper_group;
204 raise;
205 --
206 END create_timekeeper_group;
207
208
209
210 -- ----------------------------------------------------------------------------
211 -- |-------------------------<update_timekeeper_group>------------------------|
212 -- ----------------------------------------------------------------------------
213 --
214 --
215 -- Description:
216 --
217 -- This API updates an existing Timekeeper_Group with a given name
218 --
219 -- Prerequisites:
220 --
221 -- None
222 --
223 -- In Parameters:
224 -- Name Reqd Type Description
225 --
226 -- p_validate No boolean If TRUE then the database
227 -- remains unchanged. If FALSE
228 -- then the data_approval_rule
229 -- is updated. Default is FALSE.
230 -- p_tk_group_id Yes number Primary Key for entity
231 -- p_object_version_number Yes number Object Version Number
232 -- p_tk_group_name Yes varchar2 tk group Name for the timekeeper group
233 -- p_tk_resource_id Yes number resource id for the person
234 -- p_business_group_id Yes number business group id
235 -- Post Success:
236 --
237 -- when the timekeeper group has been updated successfully the following
238 -- out parameters are set.
239 --
240 -- Name Type Description
241 --
242 -- p_object_version_number Number Object version number for the
243 -- updated rule
244 --
245 -- Post Failure:
246 --
247 -- The timekeeper_group will not be updated and an application error raised
248 --
249 -- Access Status:
250 -- Public.
251 --
252 --
253 procedure update_timekeeper_group
254 (p_validate in boolean default false
255 ,p_tk_group_id in number
256 ,p_object_version_number in out nocopy number
257 ,p_tk_group_name in varchar2
258 ,p_tk_resource_id in number
259 ,p_business_group_id in number)
260 IS
261 --
262 -- Declare cursors and local variables
263 --
264 l_proc varchar2(72);
265 l_object_version_number hxc_tk_groups.object_version_number%TYPE := p_object_version_number;
266 --
267 begin
268 g_debug := hr_utility.debug_enabled;
269 --
270 if g_debug then
271 l_proc := g_package||' update_timekeeper_group';
272 hr_utility.set_location('Entering:'|| l_proc, 10);
273 end if;
274 --
275 -- Issue a savepoint if operating in validation only mode
276 --
277 savepoint update_timekeeper_group;
278 --
279 hxc_timekeeper_group_api.chk_name
280 ( p_tk_group_name => p_tk_group_name
281 , p_tk_group_id => p_tk_group_id
282 , p_tk_resource_id => p_tk_resource_id
283 , p_business_group_id => p_business_group_id
284 );
285
286 hxc_timekeeper_group_api.chk_tk_resource_id ( p_tk_resource_id => p_tk_resource_id );
287
288 if g_debug then
289 hr_utility.set_location(l_proc, 20);
290 end if;
291 --
292 -- Call Before Process User Hook
293 --
294 begin
295 hxc_timekeeper_group_BK_2.update_timekeeper_group_b
296 (p_tk_group_id => p_tk_group_id
297 ,p_object_version_number => p_object_version_number
298 ,p_tk_group_name => p_tk_group_name
299 ,p_tk_resource_id => p_tk_resource_id
300 ,p_business_group_id => p_business_group_id
301 );
302 exception
303 when hr_api.cannot_find_prog_unit then
304 hr_api.cannot_find_prog_unit_error
305 (p_module_name => 'update_timekeeper_group'
306 ,p_hook_type => 'BP'
307 );
308 end;
309 --
310 if g_debug then
311 hr_utility.set_location(l_proc, 30);
312 end if;
313 --
314 -- Process Logic
315 --
316 -- call row handler
317 --
318 hxc_tkg_upd.upd (
319 p_tk_group_name => p_tk_group_name
320 ,p_tk_group_id => p_tk_group_id
321 ,p_tk_resource_id => p_tk_resource_id
322 ,p_object_version_number => l_object_version_number
323 ,p_business_group_id => p_business_group_id
324 );
325 --
326 --
327 if g_debug then
328 hr_utility.set_location(l_proc, 40);
329 end if;
330 --
331 -- Call After Process User Hook
332 --
333 begin
334 hxc_timekeeper_group_BK_2.update_timekeeper_group_a
335 (p_tk_group_id => p_tk_group_id
336 ,p_object_version_number => l_object_version_number
337 ,p_tk_group_name => p_tk_group_name
338 ,p_tk_resource_id => p_tk_resource_id
339 ,p_business_group_id => p_business_group_id
340 );
341 exception
342 when hr_api.cannot_find_prog_unit then
343 hr_api.cannot_find_prog_unit_error
344 (p_module_name => 'update_timekeeper_group'
345 ,p_hook_type => 'AP'
346 );
347 end;
348 --
349 if g_debug then
350 hr_utility.set_location(l_proc, 50);
351 end if;
352 --
353 -- When in validation only mode raise the Validate_Enabled exception
354 --
355 if p_validate then
356 raise hr_api.validate_enabled;
357 end if;
358 --
359 if g_debug then
360 hr_utility.set_location(' Leaving:'||l_proc, 60);
361 end if;
362 --
363 -- Set all output arguments
364 --
365 p_object_version_number := l_object_version_number;
366 --
367 exception
368 --
369 when hr_api.validate_enabled then
370 --
371 -- As the Validate_Enabled exception has been raised
372 -- we must rollback to the savepoint
373 --
374 ROLLBACK TO update_timekeeper_group;
375 --
376 -- Only set output warning arguments
377 -- (Any key or derived arguments must be set to null
378 -- when validation only mode is being used.)
379 --
380 p_object_version_number := null;
381 --
382 if g_debug then
383 hr_utility.set_location(' Leaving:'||l_proc, 60);
384 end if;
385 --
386 when others then
387 --
388 -- A validation or unexpected error has occured
389 --
390 if g_debug then
391 hr_utility.trace('In exeception');
392 end if;
393 ROLLBACK TO update_timekeeper_group;
394 raise;
395
396 end update_timekeeper_group;
397 --
398 -- ----------------------------------------------------------------------------
399 -- |----------------------< delete_timekeeper_group >-------------------------|
400 -- ----------------------------------------------------------------------------
401 --
402 --
403 -- Description:
404 --
405 -- This API deletes an existing Timekeeper_Group
406 --
407 -- Prerequisites:
408 --
409 -- None
410 --
411 -- In Parameters:
412 -- Name Reqd Type Description
413 --
414 -- p_validate No boolean If TRUE then the database
415 -- remains unchanged. If FALSE
416 -- then the timekeeper_group
417 -- is deleted. Default is FALSE.
418 -- p_timekeeper_group_id Yes number Primary Key for entity
419 -- p_object_version_number Yes number Object Version Number
420 --
421 -- Post Success:
422 --
423 -- when the timekeeper group has been deleted successfully the process
424 -- completes with success.
425 --
426 -- Post Failure:
427 --
428 -- The timekeeper_group will not be deleted and an application error raised
429 --
430 -- Access Status:
431 -- Public.
432 --
433 --
434 procedure delete_timekeeper_group
435 (p_validate in boolean default false
436 ,p_tk_group_id in number
437 ,p_object_version_number in number
438 ) is
439 --
440 -- Declare cursors and local variables
441 --
442 l_proc varchar2(72);
443 --
444 begin
445 g_debug := hr_utility.debug_enabled;
446 --
447
448 if g_debug then
449 l_proc := g_package||'delete_timekeeper_group';
450 hr_utility.set_location('Entering:'|| l_proc, 10);
451 end if;
452 --
453 -- Issue a savepoint if operating in validation only mode
454 --
455 savepoint delete_timekeeper_group;
456 --
457 hxc_timekeeper_group_api.chk_delete (
458 p_tk_group_id => p_tk_group_id );
459
460 if g_debug then
461 hr_utility.set_location(l_proc, 20);
462 end if;
463 --
464 -- Call Before Process User Hook
465 --
466 begin
467 --
468 hxc_timekeeper_group_BK_3.delete_timekeeper_group_b
469 (p_tk_group_id => p_tk_group_id
470 ,p_object_version_number => p_object_version_number
471 );
472 exception
473 when hr_api.cannot_find_prog_unit then
474 hr_api.cannot_find_prog_unit_error
475 (p_module_name => 'delete_timekeeper_group'
476 ,p_hook_type => 'BP'
477 );
478 end;
479 --
480 if g_debug then
481 hr_utility.set_location(l_proc, 30);
482 end if;
483 --
484 -- Process Logic
485 --
486 hxc_tkg_del.del
487 (
488 p_tk_group_id => p_tk_group_id
489 ,p_object_version_number => p_object_version_number
490 );
491 --
492 if g_debug then
493 hr_utility.set_location(l_proc, 40);
494 end if;
495 --
496 -- Call After Process User Hook
497 --
498 begin
499 --
500 hxc_timekeeper_group_BK_3.delete_timekeeper_group_a
501 (p_tk_group_id => p_tk_group_id
502 ,p_object_version_number => p_object_version_number
503 );
504 exception
505 when hr_api.cannot_find_prog_unit then
506 hr_api.cannot_find_prog_unit_error
507 (p_module_name => 'delete_timekeeper_group'
508 ,p_hook_type => 'AP'
509 );
510 end;
511 --
512 -- When in validation only mode raise the Validate_Enabled exception
513 --
514 if p_validate then
515 raise hr_api.validate_enabled;
516 end if;
517 --
518 if g_debug then
519 hr_utility.set_location(' Leaving:'||l_proc, 50);
520 end if;
521 --
522 exception
523 --
524 when hr_api.validate_enabled then
525 --
526 -- As the Validate_Enabled exception has been raised
527 -- we must rollback to the savepoint
528 --
529 ROLLBACK TO delete_timekeeper_group;
530 --
531 when others then
532 --
533 -- A validation or unexpected error has occured
534 --
535 ROLLBACK TO delete_timekeeper_group;
536 raise;
537 --
538 end delete_timekeeper_group;
539 --
540 -- ----------------------------------------------------------------------------
541 -- |-----------------------< chk_name >---------------------------------------|
542 -- ----------------------------------------------------------------------------
543 --
544 -- Description:
545 --
546 -- SEE DESCRIPTION IN PACKAGE HEADER
547 --
548 -- Note:
549 -- This procedure is called from the client
550 --
551 -- ----------------------------------------------------------------------------
552 Procedure chk_name
553 (
554 p_tk_group_name in varchar2
555 ,p_tk_group_id in number
556 ,p_tk_resource_id in number
557 ,p_business_group_id in number
558 ) IS
559
560 l_proc varchar2(72);
561 --
562 -- cursor to check name is unique
563 --
564 CURSOR csr_chk_name IS
565 SELECT 'error'
566 FROM sys.dual
567 WHERE EXISTS (
568 SELECT 'x'
569 FROM hxc_tk_groups teg
570 WHERE teg.tk_group_name = p_tk_group_name
571 AND teg.tk_resource_id = p_tk_resource_id
572 AND
573 ( teg.tk_group_id <> p_tk_group_id OR
574 p_tk_group_id IS NULL )
575 AND teg.business_group_id = p_business_group_id
576 );
577 --
578 l_dup_name varchar2(5) := NULL;
579 --
580 BEGIN
581 g_debug := hr_utility.debug_enabled;
582
583 if g_debug then
584 l_proc := g_package||'chk_name';
585 hr_utility.set_location('Entering:'||l_proc, 5);
586 end if;
587 --
588 -- check that the name has been entered
589 --
590 IF ( p_tk_group_name IS NULL )
591 THEN
592 --
593 hr_utility.set_message(809, 'HXC_TEG_NAME_MAND');
594 hr_utility.raise_error;
595 --
596 END IF;
597 if g_debug then
598 hr_utility.set_location('Processing:'||l_proc, 10);
599 end if;
600 --
601 -- check that the name is unique
602 --
603 OPEN csr_chk_name;
604 FETCH csr_chk_name INTO l_dup_name;
605 CLOSE csr_chk_name;
606 --
607 IF ( l_dup_name IS NOT NULL )
608 THEN
609 --
610 hr_utility.set_message(809, 'HXC_TEG_NAME_DUP');
611 hr_utility.raise_error;
612 --
613 END IF;
614 --
615 if g_debug then
616 hr_utility.set_location('Leaving:'||l_proc, 20);
617 end if;
618 --
619 END chk_name;
620 --
621 -- ----------------------------------------------------------------------------
622 -- |-----------------------< chk_tk_resource_id>---------------------------------|
623 -- ----------------------------------------------------------------------------
624 --
625 -- Description:
626 -- SEE DESCRIPTION IN HEADER
627 --
628 -- ----------------------------------------------------------------------------
629 Procedure chk_tk_resource_id
630 (
631 p_tk_resource_id in number
632 ) IS
633
634 l_proc varchar2(72) := 'chk_tk_resource_id';
635 L_dummy varchar2(1) := NULL;
636
637 CURSOR csr_chk_tk_resource_id IS
638 SELECT 'x'
639 FROM dual
640 WHERE EXISTS ( select 'x'
641 FROM per_people_f p
642 WHERE p.person_id = p_tk_resource_id );
643
644 BEGIN
645
646 hr_api.mandatory_arg_error
647 (p_api_name => l_proc
648 ,p_argument => 'TK_RESOURCE_ID'
649 ,p_argument_value => p_tk_resource_id
650 );
651
652 OPEN csr_chk_tk_resource_id;
653 FETCH csr_chk_tk_resource_id INTO l_dummy;
654
655 IF csr_chk_tk_resource_id%NOTFOUND
656 THEN
657 hr_utility.set_message(809, 'HXC_TEG_INVALID_TK_RESOURCE_ID');
658 hr_utility.raise_error;
659 END IF;
660
661 CLOSE csr_chk_tk_resource_id;
662
663 END chk_tk_resource_id;
664 --
665 -- ----------------------------------------------------------------------------
666 -- |-----------------------< chk_delete >-------------------------------------|
667 -- ----------------------------------------------------------------------------
668 --
669 -- Description:
670 --
671 -- SEE DESCRIPTION IN PACKAGE HEADER
672 --
673 -- ----------------------------------------------------------------------------
674 Procedure chk_delete
675 (
676 p_tk_group_id in number
677 ) IS
678 BEGIN
679 null;
680 END chk_delete;
681
682
683 -- ----------------------------------------------------------------------------
684 -- |-------------------------< get_employee >---------------------------------|
685 -- ----------------------------------------------------------------------------
686 --
687 -- Description:
688 --
689 -- SEE DESCRIPTION IN PACKAGE HEADER
690 --
691 -- ----------------------------------------------------------------------------
692 PROCEDURE get_employee ( p_employee_id IN OUT NOCOPY NUMBER
693 , p_full_name IN OUT NOCOPY VARCHAR2
694 , p_employee_number IN OUT NOCOPY varchar2
695 , p_override IN OUT NOCOPY VARCHAR2 ) IS
696
697 l_proc varchar2(72);
698
699 l_user_person_id number(15) := NULL;
700 l_override varchar2(1) := NULL;
701
702 CURSOR csr_get_full_name ( p_person_id NUMBER ) IS
703 SELECT p.full_name
704 , nvl(p.employee_number, p.npw_number) employee_number
705 FROM per_people p
706 WHERE p.person_id = p_person_id;
707
708
709
710 l_emp_rec csr_get_full_name%ROWTYPE;
711
712 BEGIN
713
714 g_debug := hr_utility.debug_enabled;
715
716 if g_debug then
717 l_proc := 'get_user_profiles';
718 hr_utility.set_location('Entering:'||l_proc, 10);
719 end if;
720
721 l_user_person_id := fnd_global.employee_id;
722
723 if g_debug then
724 hr_utility.trace('gaz - person id is '||to_char(l_user_person_id));
725 end if;
726
727 IF ( l_user_person_id IS NULL OR l_user_person_id = -1 )
728 THEN
729 hr_utility.set_message(809, 'HXC_TEG_NO_PERSON_FOR_USER');
730 hr_utility.raise_error;
731 END IF;
732
733 l_override := fnd_profile.value('HXC_TIMEKEEPER_OVERRIDE');
734
735 IF ( l_override = 'N' )
736 THEN
737 l_override := NULL;
738 END IF;
739
740 -- now get the full name
741
742 OPEN csr_get_full_name ( l_user_person_id );
743 FETCH csr_get_full_name INTO l_emp_rec;
744 CLOSE csr_get_full_name;
745
746 p_employee_id := l_user_person_id;
747 p_full_name := l_emp_rec.full_name;
748 p_employee_number := l_emp_rec.employee_number;
749 p_override := l_override;
750
751 END get_employee;
752
753
754
755 FUNCTION get_people ( p_populate_id NUMBER
756 , p_populate_type VARCHAR2
757 , p_person_type VARCHAR2
758 ) RETURN t_people
759 IS
760
761 CURSOR csr_get_asg_people IS
762 SELECT DISTINCT
763 p.person_id
764 , p.full_name
765 , nvl(p.employee_number, p.npw_number) employee_number
766 , SUBSTR(hxc_tk_grp_query_criteria_api.get_tc_period ( p.person_id ),1,80) tc_period_name
767 , hr_person_type_usage_info.get_user_person_type(p.effective_start_date, p.person_id) person_type
768 FROM
769 per_people p
770 , per_assignments asg
771 , hr_assignment_set_amendments asa
772 , hr_assignment_sets ass
773 , per_person_types ppt --added 2943706
774 , per_person_type_usages pptu
775
776 WHERE ass.assignment_set_id = p_populate_id
777 AND asa.assignment_set_id = ass.assignment_set_id
778 AND asa.include_or_exclude = 'I'
779 AND asg.assignment_id = asa.assignment_id
780 AND p.person_id = asg.person_id
781 AND pptu.person_id = p.person_id
782 AND ppt.person_type_id = pptu.person_type_id
783 AND ppt.system_person_type in ('EMP','EMP_APL','CWK')
784 AND ( p_person_type IS NULL OR (p_person_type IS NOT NULL AND decode(ppt.SYSTEM_PERSON_TYPE,'EMP_APL','EMP',ppt.SYSTEM_PERSON_TYPE)=p_person_type));
785
786 CURSOR csr_get_org_people IS
787 SELECT DISTINCT
788 p.person_id
789 , p.full_name
790 , nvl(p.employee_number, p.npw_number) employee_number
791 , SUBSTR(hxc_tk_grp_query_criteria_api.get_tc_period ( p.person_id ),1,80) tc_period_name
792 , hr_person_type_usage_info.get_user_person_type(p.effective_start_date, p.person_id) person_type
793 FROM
794 per_people p
795 , per_assignments asg
796 , per_person_types ppt --added 2943706
797 , per_person_type_usages pptu
798 WHERE asg.organization_id = p_populate_id
799 AND p.person_id = asg.person_id
800 AND pptu.person_id = p.person_id
801 AND ppt.person_type_id = pptu.person_type_id
802 AND ppt.system_person_type in ('EMP','EMP_APL','CWK')
803 AND asg.assignment_type in ('A','E','C')
804 AND ( p_person_type IS NULL OR (p_person_type IS NOT NULL AND decode(ppt.SYSTEM_PERSON_TYPE,'EMP_APL','EMP',ppt.SYSTEM_PERSON_TYPE)=p_person_type));
805
806
807
808 l_people_rec r_people;
809 l_people_tab t_people;
810 l_index BINARY_INTEGER := 1;
811
812 l_proc varchar2(72);
813
814 BEGIN
815
816 g_debug := hr_utility.debug_enabled;
817
818 if g_debug then
819 l_proc := 'get_people';
820 hr_utility.set_location('Entering:'||l_proc, 10);
821 end if;
822
823 IF ( p_populate_type = 'ASG_SET' )
824 THEN
825
826 if g_debug then
827 hr_utility.set_location('Entering:'||l_proc, 20);
828 end if;
829
830 OPEN csr_get_asg_people;
831 FETCH csr_get_asg_people INTO l_people_rec;
832
833 WHILE csr_get_asg_people%FOUND
834 LOOP
835
836 l_people_tab(l_index) := l_people_rec;
837
838 l_index := l_index + 1;
839
840 FETCH csr_get_asg_people INTO l_people_rec;
841
842 END LOOP;
843
844 CLOSE csr_get_asg_people;
845
846 if g_debug then
847 hr_utility.set_location('Entering:'||l_proc, 30);
848 end if;
849
850 ELSE -- p_populate_type must be ORGANIZATION
851
852 if g_debug then
853 hr_utility.set_location('Entering:'||l_proc, 40);
854 end if;
855
856 OPEN csr_get_org_people;
857 FETCH csr_get_org_people INTO l_people_rec;
858
859
860 WHILE csr_get_org_people%FOUND
861 LOOP
862
863 l_people_tab(l_index) := l_people_rec;
864
865 l_index := l_index + 1;
866
867 FETCH csr_get_org_people INTO l_people_rec;
868
869 END LOOP;
870
871 CLOSE csr_get_org_people;
872
873 if g_debug then
874 hr_utility.set_location('Entering:'||l_proc, 50);
875 end if;
876
877 END IF; -- p_populate_type
878
879 if g_debug then
880 hr_utility.set_location('Entering:'||l_proc, 60);
881 end if;
882
883 RETURN l_people_tab;
884
885 END get_people;
886 --
887 --
888 END hxc_timekeeper_group_api;
[Home] [Help]