[Home] [Help]
PACKAGE BODY: APPS.PER_ASP_BUS
Source
1 Package Body per_asp_bus as
2 /* $Header: peasprhi.pkb 115.15 2002/12/02 14:20:06 apholt ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_asp_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_sec_profile_assignment_id >-----------------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- sec_profile_assignment_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_sec_profile_assignment_id(p_sec_profile_assignment_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_sec_profile_assignment_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := per_asp_shd.api_updating
47 (p_sec_profile_assignment_id => p_sec_profile_assignment_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_sec_profile_assignment_id,hr_api.g_number)
52 <> per_asp_shd.g_old_rec.sec_profile_assignment_id) then
53 --
54 -- raise error as PK has changed
55 --
56 per_asp_shd.constraint_error('PER_SEC_PROFILE_ASSIGNMENTS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_sec_profile_assignment_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 per_asp_shd.constraint_error('PER_SEC_PROFILE_ASSIGNMENTS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_sec_profile_assignment_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_security_profile_id >-----------------------------------------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_sec_profile_assignment_id PK
89 -- p_security_profile_id ID of FK column
90 -- p_object_version_number object version number
91 --
92 -- Post Success
93 -- Processing continues
94 --
95 -- Post Failure
96 -- Error raised.
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 --
101 Procedure chk_security_profile_id (p_sec_profile_assignment_id in number,
102 p_security_profile_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_security_profile_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from per_security_profiles a
112 where a.security_profile_id = p_security_profile_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := per_asp_shd.api_updating
119 (p_sec_profile_assignment_id => p_sec_profile_assignment_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_security_profile_id,hr_api.g_number)
124 <> nvl(per_asp_shd.g_old_rec.security_profile_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if security_profile_id value exists in per_security_profiles table
128 --
129 open c1;
130 --
131 fetch c1 into l_dummy;
132 if c1%notfound then
133 --
134 close c1;
135 --
136 -- raise error as FK does not relate to PK in per_security_profiles
137 -- table.
138 --
139 per_asp_shd.constraint_error('PER_SEC_PROFILE_ASSIGNMENTS_FK');
140 --
141 end if;
142 --
143 close c1;
144 --
145 end if;
146 --
147 hr_utility.set_location('Leaving:'||l_proc,10);
148 --
149 End chk_security_profile_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_business_group_id >-------------------------------------------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description
156 -- This procedure checks that a referenced foreign key actually exists
157 -- in the referenced table.
158 --
159 -- Pre-Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- p_sec_profile_assignment_id PK
164 -- p_business_group_id ID of FK column
165 -- p_object_version_number object version number
166 --
167 -- Post Success
168 -- Processing continues
169 --
170 -- Post Failure
171 -- Error raised.
172 --
173 -- Access Status
174 -- Internal table handler use only.
175 --
176 Procedure chk_business_group_id (p_sec_profile_assignment_id in number,
177 p_business_group_id in number,
178 p_object_version_number in number) is
179 --
180 l_proc varchar2(72) := g_package||'chk_security_profile_id';
181 l_api_updating boolean;
182 l_dummy varchar2(1);
183 --
184 cursor c1 is
185 select null
186 from hr_organization_information h1
187 where h1.org_information_context = 'Business Group Information'
188 and h1.organization_id = p_business_group_id;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc,5);
193 --
194 l_api_updating := per_asp_shd.api_updating
195 (p_sec_profile_assignment_id => p_sec_profile_assignment_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_business_group_id,hr_api.g_number)
200 <> nvl(per_asp_shd.g_old_rec.business_group_id,hr_api.g_number)
201 or not l_api_updating) then
202 --
203 -- check if business_group_id value exists in per_business_groups view
204 --
205 open c1;
206 --
207 fetch c1 into l_dummy;
208 if c1%notfound then
209 --
210 close c1;
211 --
212 -- raise error as FK does not relate to PK in per_business_groups
213 -- view
214 --
215 per_asp_shd.constraint_error('PER_SEC_PROFILE_ASSIGNMENTS_FK');
216 --
217 end if;
218 --
219 close c1;
220 --
221 end if;
222 --
223 hr_utility.set_location('Leaving:'||l_proc,10);
224 --
225 End chk_business_group_id;
226 --
227 -- ----------------------------------------------------------------------------
228 -- |-< chk_non_updateable_args >----------------------------------------------|
229 -- ----------------------------------------------------------------------------
230 -- {Start Of Comments}
231 --
232 -- Description:
233 -- This procedure is used to ensure that non updateable attributes have not
234 -- been updated. If an attribute has been updated an error is generated.
235 --
236 -- Prerequisites:
237 --
238 --
239 -- In Parameters:
240 -- A Pl/Sql record structre.
241 --
242 -- Post Success:
243 -- Processing continues if all the non updateable attributes have not been
244 -- changed.
245 --
246 -- Post Failure:
247 -- An application error is raised if any of the non updateable attributes
248 -- (listed below) have been changed.
249 --
250 -- sec_profile_assignment_id
251 -- user_id
252 -- security_group_id
253 -- business_group_id
254 -- security_profile_id
255 -- responsibility_id
256 -- responsibility_application_id
257 --
258 -- Developer Implementation Notes:
259 -- None.
260 --
261 -- Access Status:
262 -- Internal Row Handler Use Only.
263 --
264 -- {End Of Comments}
265 -- ----------------------------------------------------------------------------
266 --
267 PROCEDURE chk_non_updateable_args
268 (p_rec IN per_asp_shd.g_rec_type
269 )
270 IS
271 --
272 l_proc VARCHAR2 (72) := g_package||'chk_non_updateable_args';
273 l_error EXCEPTION;
274 l_argument VARCHAR2 (30);
275 --
276 BEGIN
277 hr_utility.set_location('Entering:'||l_proc, 10);
278 --
279 -- Only proceed with validation if a row exists for
280 -- the current record in the HR Schema
281 --
282 IF NOT per_asp_shd.api_updating
283 (p_sec_profile_assignment_id => p_rec.sec_profile_assignment_id
284 ,p_object_version_number => p_rec.object_version_number
285 )
286 THEN
287 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
288 hr_utility.set_message_token('PROCEDURE', l_proc);
289 hr_utility.set_message_token('STEP', '20');
290 END IF;
291 --
292 hr_utility.set_location(l_proc, 30);
293 --
294 IF p_rec.sec_profile_assignment_id
295 <> per_asp_shd.g_old_rec.sec_profile_assignment_id
296 THEN
297 --
298 l_argument := 'sec_profile_assignment_id';
299 raise l_error;
300 --
301 END IF;
302 --
303 hr_utility.set_location(l_proc, 40);
304 --
305 IF p_rec.user_id
306 <> per_asp_shd.g_old_rec.user_id
307 THEN
308 --
309 l_argument := 'user_id';
310 raise l_error;
311 --
312 END IF;
313 --
314 hr_utility.set_location(l_proc, 50);
315 --
316 IF p_rec.security_group_id
317 <> per_asp_shd.g_old_rec.security_group_id
318 THEN
319 --
320 l_argument := 'security_group_id';
321 raise l_error;
322 --
323 END IF;
324 --
325 hr_utility.set_location(l_proc, 55);
326 --
327 IF p_rec.business_group_id
328 <> per_asp_shd.g_old_rec.business_group_id
329 THEN
330 --
331 l_argument := 'business_group_id';
332 raise l_error;
333 --
334 END IF;
335 --
336 hr_utility.set_location(l_proc, 60);
337 --
338 IF p_rec.security_profile_id
339 <> per_asp_shd.g_old_rec.security_profile_id
340 THEN
341 --
342 l_argument := 'security_profile_id';
343 raise l_error;
344 --
345 END IF;
346 --
347 hr_utility.set_location(l_proc, 70);
348 --
349 IF p_rec.responsibility_id
350 <> per_asp_shd.g_old_rec.responsibility_id
351 THEN
352 --
353 l_argument := 'responsibility_id';
354 raise l_error;
355 --
356 END IF;
357 --
358 hr_utility.set_location(l_proc, 80);
359 --
360 IF p_rec.responsibility_application_id
361 <> per_asp_shd.g_old_rec.responsibility_application_id
362 THEN
363 --
364 l_argument := 'responsibility_application_id';
365 raise l_error;
366 --
367 END IF;
368 --
369 hr_utility.set_location(l_proc, 90);
370 --
371 EXCEPTION
372 WHEN l_error THEN
373 hr_api.argument_changed_error
374 (p_api_name => l_proc
375 ,p_argument => l_argument
376 );
377 WHEN OTHERS THEN
378 RAISE;
379 hr_utility.set_location(' Leaving:'||l_proc, 100);
380 END chk_non_updateable_args;
381 --
382 -- ----------------------------------------------------------------------------
383 -- |-< chk_assignment_dates >-------------------------------------------------|
384 -- ----------------------------------------------------------------------------
385 --
386 PROCEDURE chk_assignment_dates
387 (p_user_id
388 IN per_sec_profile_assignments.user_id%TYPE
389 ,p_responsibility_id
390 IN per_sec_profile_assignments.responsibility_id%TYPE
391 ,p_application_id
392 IN per_sec_profile_assignments.responsibility_application_id%TYPE
393 ,p_security_group_id
394 IN per_sec_profile_assignments.security_group_id%TYPE
395 ,p_start_date
396 IN per_sec_profile_assignments.start_date%TYPE
397 ,p_end_date
398 IN per_sec_profile_assignments.end_date%TYPE
399 )
400 IS
401 --
402 CURSOR c_get_assignment_dates
403 IS
404 SELECT start_date
405 ,end_date
406 FROM fnd_user_resp_groups
407 WHERE user_id = p_user_id
408 AND responsibility_id = p_responsibility_id
409 AND responsibility_application_id = p_application_id
410 AND security_group_id = p_security_group_id;
411 --
412 BEGIN
413 --
414 -- check that the start date is not null
415 --
416 IF p_start_date IS NULL THEN
417 --
418 hr_utility.set_message
419 (800
420 ,'PER_52528_ASP_START_DATE_NULL'
421 );
422 hr_utility.raise_error;
423 --
424 END IF;
425 --
426 -- check that the start date is not on or more than the end date
427 --
428 IF p_start_date > NVL(p_end_date, hr_general.END_OF_TIME) THEN
429 --
430 hr_utility.set_message
431 (800
432 ,'PER_52525_ASP_DATE_ERROR'
433 );
434 hr_utility.raise_error;
435 --
436 END IF;
437 --
438 END chk_assignment_dates;
439 --
440 -- ----------------------------------------------------------------------------
441 -- |-< chk_invalid_dates >----------------------------------------------------|
442 -- ----------------------------------------------------------------------------
443 -- {Start Of Comments}
444 --
445 -- Description:
446 -- This procedure is used to enforce the business rule that the start/end
447 -- dates of new/updated records cannot overlap both the start and the end
448 -- dates of existing records.
449 --
450 -- Prerequisites:
451 -- None.
452 --
453 -- In Parameters:
454 --
455 -- Post Success:
456 -- Processing continues.
457 --
458 -- Post Failure:
459 -- An exception is raised.
460 --
461 -- Developer Implementation Notes:
462 -- None.
463 --
464 -- Access Status:
465 -- Internal Row Handler Use Only.
466 --
467 -- {End Of Comments}
468 -- ----------------------------------------------------------------------------
469 --
473 DEFAULT NULL
470 PROCEDURE chk_invalid_dates
471 (p_sec_profile_assignment_id
472 IN per_sec_profile_assignments.sec_profile_assignment_id%TYPE
474 ,p_user_id
475 IN per_sec_profile_assignments.user_id%TYPE
476 ,p_responsibility_id
477 IN per_sec_profile_assignments.responsibility_id%TYPE
478 ,p_application_id
479 IN per_sec_profile_assignments.responsibility_application_id%TYPE
480 ,p_security_group_id
481 IN per_sec_profile_assignments.security_group_id%TYPE
482 ,p_business_group_id
483 IN per_sec_profile_assignments.business_group_id%TYPE
484 ,p_security_profile_id
485 IN per_sec_profile_assignments.security_profile_id%TYPE
486 ,p_start_date
487 IN per_sec_profile_assignments.start_date%TYPE
488 ,p_end_date
489 IN per_sec_profile_assignments.end_date%TYPE
490 )
491 IS
492 --
493 CURSOR c_chk_invalid_dates
494 IS
495 SELECT 'Y'
496 FROM per_sec_profile_assignments s
497 WHERE s.user_id = p_user_id
498 AND s.responsibility_id = p_responsibility_id
499 AND s.responsibility_application_id = p_application_id
500 AND s.security_group_id = p_security_group_id
501 AND s.business_group_id = p_business_group_id
502 AND s.security_profile_id = p_security_profile_id
503 AND s.start_date >= p_start_date
504 AND NVL(s.end_date, hr_general.END_OF_TIME) <= NVL(p_end_date, hr_general.END_OF_TIME)
505 AND (p_sec_profile_assignment_id IS NULL
506 OR s.sec_profile_assignment_id <> p_sec_profile_assignment_id);
507 --
508 l_result VARCHAR2 (1);
509 --
510 BEGIN
511 --
512 OPEN c_chk_invalid_dates;
513 --
514 FETCH c_chk_invalid_dates INTO l_result;
515 --
516 IF c_chk_invalid_dates%FOUND THEN
517 --
518 CLOSE c_chk_invalid_dates;
519 --
520 -- record found - raise an exception
521 --
522 hr_utility.set_message
523 (800
524 ,'PER_52529_ASP_ASN_DATE_ERROR'
525 );
526 hr_utility.raise_error;
527 END IF;
528 --
529 CLOSE c_chk_invalid_dates;
530 --
531 END chk_invalid_dates;
532 --
533 -- ----------------------------------------------------------------------------
534 -- |-< chk_duplicate_assignments >--------------------------------------------|
535 -- ----------------------------------------------------------------------------
536 --
537 PROCEDURE chk_duplicate_assignments
538 (p_user_id
539 IN per_sec_profile_assignments.user_id%TYPE
540 ,p_responsibility_id
541 IN per_sec_profile_assignments.responsibility_id%TYPE
542 ,p_application_id
543 IN per_sec_profile_assignments.responsibility_application_id%TYPE
544 ,p_security_group_id
545 IN per_sec_profile_assignments.security_group_id%TYPE
546 ,p_business_group_id
547 IN per_sec_profile_assignments.business_group_id%TYPE
548 ,p_security_profile_id
549 IN per_sec_profile_assignments.security_profile_id%TYPE
550 ,p_start_date
551 IN per_sec_profile_assignments.start_date%TYPE
552 ,p_end_date
553 IN per_sec_profile_assignments.end_date%TYPE
554 )
555 IS
556 --
557 CURSOR c_exists_duplicate_assignment
558 IS
559 SELECT 'Y'
560 FROM per_sec_profile_assignments
561 WHERE user_id = p_user_id
562 AND responsibility_id = p_responsibility_id
563 AND responsibility_application_id = p_application_id
564 AND security_group_id = p_security_group_id
565 AND business_group_id = p_business_group_id
566 AND security_profile_id <> p_security_profile_id
567 AND ( (start_date BETWEEN p_start_date
568 AND NVL(p_end_date, hr_general.END_OF_TIME))
569 OR (NVL(end_date, hr_general.END_OF_TIME)
570 BETWEEN p_start_date
571 AND NVL(p_end_date, hr_general.END_OF_TIME))
572 OR ( start_date < p_start_date
573 AND NVL(end_date, hr_general.END_OF_TIME)
574 > NVL(p_end_date, hr_general.END_OF_TIME)));
575 --
576 l_exists VARCHAR2(1);
577 --
578 BEGIN
579 --
580 OPEN c_exists_duplicate_assignment;
581 --
582 FETCH c_exists_duplicate_assignment INTO l_exists;
583 --
584 IF c_exists_duplicate_assignment%NOTFOUND THEN
585 --
586 CLOSE c_exists_duplicate_assignment;
587 --
588 ELSE
589 --
590 CLOSE c_exists_duplicate_assignment;
591 --
592 hr_utility.set_message
593 (800
594 ,'PER_52551_ASP_DUP_ASN_ERROR'
595 );
596 hr_utility.raise_error;
597 --
598 END IF;
599 --
600 END chk_duplicate_assignments;
601 --
602 -- ----------------------------------------------------------------------------
603 -- |-< chk_overlapping_dates >------------------------------------------------|
604 -- ----------------------------------------------------------------------------
605 --
606 PROCEDURE chk_overlapping_dates
607 (p_sec_profile_assignment_id
611 IN per_sec_profile_assignments.user_id%TYPE
608 IN per_sec_profile_assignments.sec_profile_assignment_id%TYPE
609 DEFAULT NULL
610 ,p_user_id
612 ,p_responsibility_id
613 IN per_sec_profile_assignments.responsibility_id%TYPE
614 ,p_application_id
615 IN per_sec_profile_assignments.responsibility_application_id%TYPE
616 ,p_security_group_id
617 IN per_sec_profile_assignments.security_group_id%TYPE
618 ,p_business_group_id
619 IN per_sec_profile_assignments.business_group_id%TYPE
620 ,p_security_profile_id
621 IN per_sec_profile_assignments.security_profile_id%TYPE
622 ,p_start_date
623 IN per_sec_profile_assignments.start_date%TYPE
624 ,p_end_date
625 IN per_sec_profile_assignments.end_date%TYPE
626 )
627 IS
628 --
629 l_id per_sec_profile_assignments.sec_profile_assignment_id%TYPE DEFAULT NULL;
630 l_ovn per_sec_profile_assignments.object_version_number%TYPE DEFAULT NULL;
631 l_start_date per_sec_profile_assignments.start_date%TYPE DEFAULT NULL;
632 l_end_date per_sec_profile_assignments.end_date%TYPE DEFAULT NULL;
633 --
634 BEGIN
635 --
636 -- call the other chk_overlapping_dates procedure and raise the relavent exception
637 -- if it returns anything
638 --
639 chk_overlapping_dates
640 (p_sec_profile_assignment_id => p_sec_profile_assignment_id
641 ,p_user_id => p_user_id
642 ,p_responsibility_id => p_responsibility_id
643 ,p_application_id => p_application_id
644 ,p_security_group_id => p_security_group_id
645 ,p_business_group_id => p_business_group_id
646 ,p_security_profile_id => p_security_profile_id
647 ,p_start_date => p_start_date
648 ,p_end_date => p_end_date
649 ,p_clashing_id => l_id
650 ,p_clashing_ovn => l_ovn
651 ,p_clashing_start_date => l_start_date
652 ,p_clashing_end_date => l_end_date
653 );
654 --
655 IF l_id IS NOT NULL THEN
656 -- we need to work out which exception to raise....
657 IF p_start_date >= l_start_date AND p_start_date <= l_end_date THEN
658 --
659 -- The start date of the inserted/updated record is in error
660 --
661 hr_utility.set_message
662 (800
663 ,'PER_52526_ASP_START_DATE_ERROR'
664 );
665 hr_utility.raise_error;
666 --
667 ELSIF p_end_date >= l_start_date AND p_end_date <= l_end_date THEN
668 --
669 -- The end date of the inserted/updated record is in error
670 --
671 hr_utility.set_message
672 (800
673 ,'PER_52527_ASP_END_DATE_ERROR'
674 );
675 hr_utility.raise_error;
676 --
677 END IF;
678 END IF;
679 --
680 END chk_overlapping_dates;
681 --
682 -- ----------------------------------------------------------------------------
683 -- |-< chk_overlapping_dates >------------------------------------------------|
684 -- ----------------------------------------------------------------------------
685 --
686 PROCEDURE chk_overlapping_dates
687 (p_sec_profile_assignment_id
688 IN per_sec_profile_assignments.sec_profile_assignment_id%TYPE
689 DEFAULT NULL
690 ,p_user_id
691 IN per_sec_profile_assignments.user_id%TYPE
692 ,p_responsibility_id
693 IN per_sec_profile_assignments.responsibility_id%TYPE
694 ,p_application_id
695 IN per_sec_profile_assignments.responsibility_application_id%TYPE
696 ,p_security_group_id
697 IN per_sec_profile_assignments.security_group_id%TYPE
698 ,p_business_group_id
699 IN per_sec_profile_assignments.business_group_id%TYPE
700 ,p_security_profile_id
701 IN per_sec_profile_assignments.security_profile_id%TYPE
702 ,p_start_date
703 IN per_sec_profile_assignments.start_date%TYPE
704 ,p_end_date
705 IN per_sec_profile_assignments.end_date%TYPE
706 ,p_clashing_id
707 OUT NOCOPY per_sec_profile_assignments.sec_profile_assignment_id%TYPE
708 ,p_clashing_ovn
709 OUT NOCOPY per_sec_profile_assignments.object_version_number%TYPE
710 ,p_clashing_start_date
711 OUT NOCOPY per_sec_profile_assignments.start_date%TYPE
712 ,p_clashing_end_date
713 OUT NOCOPY per_sec_profile_assignments.end_date%TYPE
714 )
715 IS
716 --
717 CURSOR c_chk_overlapping_dates
718 IS
719 SELECT s.sec_profile_assignment_id
720 ,s.object_version_number
721 ,s.start_date
722 ,s.end_date
723 FROM per_sec_profile_assignments s
724 WHERE s.user_id = p_user_id
725 AND s.responsibility_id = p_responsibility_id
726 AND s.responsibility_application_id = p_application_id
727 AND s.security_group_id = p_security_group_id
728 AND s.business_group_id = p_business_group_id
729 AND s.security_profile_id = p_security_profile_id
730 AND NOT ( (s.start_date < p_start_date
734 )
731 AND NVL(s.end_date, hr_general.END_OF_TIME) < p_start_date)
732 OR (s.start_date > p_end_date
733 AND NVL(s.end_date, hr_general.END_OF_TIME) > NVL(p_end_date, hr_general.END_OF_TIME))
735 AND (p_sec_profile_assignment_id IS NULL
736 OR s.sec_profile_assignment_id <> p_sec_profile_assignment_id);
737 --
738 BEGIN
739 --
740 OPEN c_chk_overlapping_dates;
741 --
742 FETCH c_chk_overlapping_dates INTO p_clashing_id
743 ,p_clashing_ovn
744 ,p_clashing_start_date
745 ,p_clashing_end_date;
746 --
747 IF c_chk_overlapping_dates%NOTFOUND THEN
748 p_clashing_id := NULL;
749 END IF;
750 --
751 CLOSE c_chk_overlapping_dates;
752 --
753 END chk_overlapping_dates;
754 --
755 -- ----------------------------------------------------------------------------
756 -- |-< chk_assignment_exists >------------------------------------------------|
757 -- ----------------------------------------------------------------------------
758 --
759 PROCEDURE chk_assignment_exists
760 (p_user_id
761 IN per_sec_profile_assignments.user_id%TYPE
762 ,p_responsibility_id
763 IN per_sec_profile_assignments.responsibility_id%TYPE
764 ,p_application_id
765 IN per_sec_profile_assignments.responsibility_application_id%TYPE
766 ,p_security_group_id
767 IN per_sec_profile_assignments.security_group_id%TYPE
768 )
769 IS
770 --
771 BEGIN
772 --
773 IF NOT chk_assignment_exists
774 (p_user_id
775 ,p_responsibility_id
776 ,p_application_id
777 ,p_security_group_id
778 )
779 THEN
780 --
781 hr_utility.set_message
782 (800
783 ,'PER_52524_ASP_ASN_NOT_EXIST'
784 );
785 hr_utility.raise_error;
786 --
787 END IF;
788 --
789 END chk_assignment_exists;
790 --
791 -- ----------------------------------------------------------------------------
792 -- |-< chk_assignment_exists >------------------------------------------------|
793 -- ----------------------------------------------------------------------------
794 --
795 FUNCTION chk_assignment_exists
796 (p_user_id
797 IN per_sec_profile_assignments.user_id%TYPE
798 ,p_responsibility_id
799 IN per_sec_profile_assignments.responsibility_id%TYPE
800 ,p_application_id
801 IN per_sec_profile_assignments.responsibility_application_id%TYPE
802 ,p_security_group_id
803 IN per_sec_profile_assignments.security_group_id%TYPE
804 ) RETURN BOOLEAN
805 IS
806 --
807 BEGIN
808 --
809 RETURN fnd_user_resp_groups_api.Assignment_Exists
810 (user_id => p_user_id
811 ,responsibility_id => p_responsibility_id
812 ,responsibility_application_id => p_application_id
813 ,security_group_id => p_security_group_id
814 );
815 --
816 END chk_assignment_exists;
817 --
818 -- ----------------------------------------------------------------------------
819 -- |-< get_security_group_id >------------------------------------------------|
820 -- ----------------------------------------------------------------------------
821 --
822 FUNCTION get_security_group_id
823 (p_business_group_id IN NUMBER
824 ) RETURN NUMBER
825 IS
826 --
827 CURSOR c_get_sg_id
828 IS
829 SELECT org_information14 security_group_id
830 FROM hr_organization_information h1
831 WHERE org_information_context = 'Business Group Information'
832 AND h1.organization_id = p_business_group_id;
833 --
834 l_security_group_id NUMBER;
835 l_exception EXCEPTION;
836 --
837 BEGIN
838 IF fnd_profile.value('ENABLE_SECURITY_GROUPS') = 'Y' THEN
839 --
840 -- Retrieve the security_group_id by querying the per_business_groups
841 -- view with the business_group_id supplied.
842 --
843 OPEN c_get_sg_id;
844 FETCH c_get_sg_id INTO l_security_group_id;
845 IF c_get_sg_id%NOTFOUND THEN
846 --
847 -- Security group does not exist! Raise an exception...
848 --
849 CLOSE c_get_sg_id;
850 RAISE l_exception;
851 END IF;
852 --
853 CLOSE c_get_sg_id;
854 ELSE
855 --
856 -- if security groups are not enabled then just return 0 (ie. the
857 -- standard security group)
858 --
859 l_security_group_id := 0;
860 END IF;
861 --
862 RETURN l_security_group_id;
863 --
864 END get_security_group_id;
865 --
866 -- ----------------------------------------------------------------------------
867 -- |---------------------------< insert_validate >----------------------------|
868 -- ----------------------------------------------------------------------------
869 Procedure insert_validate(p_rec in per_asp_shd.g_rec_type) is
870 --
874 hr_utility.set_location('Entering:'||l_proc, 5);
871 l_proc varchar2(72) := g_package||'insert_validate';
872 --
873 Begin
875 --
876 -- Call all supporting business operations
877 --
878 chk_sec_profile_assignment_id
879 (p_sec_profile_assignment_id => p_rec.sec_profile_assignment_id,
880 p_object_version_number => p_rec.object_version_number);
881 --
882 chk_security_profile_id
883 (p_sec_profile_assignment_id => p_rec.sec_profile_assignment_id,
884 p_security_profile_id => p_rec.security_profile_id,
885 p_object_version_number => p_rec.object_version_number);
886 --
887 chk_business_group_id
888 (p_sec_profile_assignment_id => p_rec.sec_profile_assignment_id,
889 p_business_group_id => p_rec.business_group_id,
890 p_object_version_number => p_rec.object_version_number);
891 --
892 chk_duplicate_assignments
893 (p_user_id => p_rec.user_id
894 ,p_responsibility_id => p_rec.responsibility_id
895 ,p_application_id => p_rec.responsibility_application_id
896 ,p_security_group_id => p_rec.security_group_id
897 ,p_business_group_id => p_rec.business_group_id
898 ,p_security_profile_id => p_rec.security_profile_id
899 ,p_start_date => p_rec.start_date
900 ,p_end_date => p_rec.end_date
901 );
902 --
903 -- Do some checks on the date of the assignment
904 --
905 chk_assignment_dates
906 (p_user_id => p_rec.user_id
907 ,p_responsibility_id => p_rec.responsibility_id
908 ,p_application_id => p_rec.responsibility_application_id
909 ,p_security_group_id => p_rec.security_group_id
910 ,p_start_date => p_rec.start_date
911 ,p_end_date => p_rec.end_date
912 );
913 --
914 chk_invalid_dates
915 (p_user_id => p_rec.user_id
916 ,p_responsibility_id => p_rec.responsibility_id
917 ,p_application_id => p_rec.responsibility_application_id
918 ,p_security_group_id => p_rec.security_group_id
919 ,p_business_group_id => p_rec.business_group_id
920 ,p_security_profile_id => p_rec.security_profile_id
921 ,p_start_date => p_rec.start_date
922 ,p_end_date => p_rec.end_date
923 );
924 --
925 chk_overlapping_dates
926 (p_user_id => p_rec.user_id
927 ,p_responsibility_id => p_rec.responsibility_id
928 ,p_application_id => p_rec.responsibility_application_id
929 ,p_security_group_id => p_rec.security_group_id
930 ,p_business_group_id => p_rec.business_group_id
931 ,p_security_profile_id => p_rec.security_profile_id
932 ,p_start_date => p_rec.start_date
933 ,p_end_date => p_rec.end_date
934 );
935 --
936 --
937 IF NOT chk_assignment_exists
938 (p_user_id => p_rec.user_id
939 ,p_responsibility_id => p_rec.responsibility_id
940 ,p_application_id => p_rec.responsibility_application_id
941 ,p_security_group_id => p_rec.security_group_id
942 )
943 THEN
944 --
945 -- The assignment does not exist, so create it....
946 --
947 fnd_user_resp_groups_api.Insert_Assignment
948 (user_id => p_rec.user_id
949 ,responsibility_id => p_rec.responsibility_id
950 ,responsibility_application_id => p_rec.responsibility_application_id
951 ,security_group_id => p_rec.security_group_id
952 ,start_date => p_rec.start_date
953 ,end_date => p_rec.end_date
954 ,description => ' ' -- ### description was supposed to default
955 -- to null... but does not look like it has
956 );
957 END IF;
958 --
959 hr_utility.set_location(' Leaving:'||l_proc, 10);
960 End insert_validate;
961 --
962 -- ----------------------------------------------------------------------------
963 -- |---------------------------< update_validate >----------------------------|
964 -- ----------------------------------------------------------------------------
965 Procedure update_validate(p_rec in per_asp_shd.g_rec_type) is
966 --
967 l_proc varchar2(72) := g_package||'update_validate';
968 --
969 Begin
970 hr_utility.set_location('Entering:'||l_proc, 5);
971 --
972 -- Call all supporting business operations
973 --
974 chk_non_updateable_args
975 (p_rec
976 );
977 --
978 chk_sec_profile_assignment_id
979 (p_sec_profile_assignment_id => p_rec.sec_profile_assignment_id,
980 p_object_version_number => p_rec.object_version_number);
981 --
982 chk_security_profile_id
983 (p_sec_profile_assignment_id => p_rec.sec_profile_assignment_id,
984 p_security_profile_id => p_rec.security_profile_id,
985 p_object_version_number => p_rec.object_version_number);
986 --
987 chk_business_group_id
988 (p_sec_profile_assignment_id => p_rec.sec_profile_assignment_id,
989 p_business_group_id => p_rec.business_group_id,
990 p_object_version_number => p_rec.object_version_number);
991 --
992 chk_duplicate_assignments
993 (p_user_id => p_rec.user_id
994 ,p_responsibility_id => p_rec.responsibility_id
995 ,p_application_id => p_rec.responsibility_application_id
996 ,p_security_group_id => p_rec.security_group_id
1000 ,p_end_date => p_rec.end_date
997 ,p_business_group_id => p_rec.business_group_id
998 ,p_security_profile_id => p_rec.security_profile_id
999 ,p_start_date => p_rec.start_date
1001 );
1002 --
1003 -- Do some checks on the date of the assignment
1004 --
1005 chk_assignment_dates
1006 (p_user_id => p_rec.user_id
1007 ,p_responsibility_id => p_rec.responsibility_id
1008 ,p_application_id => p_rec.responsibility_application_id
1009 ,p_security_group_id => p_rec.security_group_id
1010 ,p_start_date => p_rec.start_date
1011 ,p_end_date => p_rec.end_date
1012 );
1013 --
1014 chk_invalid_dates
1015 (p_sec_profile_assignment_id => p_rec.sec_profile_assignment_id
1016 ,p_user_id => p_rec.user_id
1017 ,p_responsibility_id => p_rec.responsibility_id
1018 ,p_application_id => p_rec.responsibility_application_id
1019 ,p_security_group_id => p_rec.security_group_id
1020 ,p_business_group_id => p_rec.business_group_id
1021 ,p_security_profile_id => p_rec.security_profile_id
1022 ,p_start_date => p_rec.start_date
1023 ,p_end_date => p_rec.end_date
1024 );
1025 --
1026 chk_overlapping_dates
1027 (p_sec_profile_assignment_id => p_rec.sec_profile_assignment_id
1028 ,p_user_id => p_rec.user_id
1029 ,p_responsibility_id => p_rec.responsibility_id
1030 ,p_application_id => p_rec.responsibility_application_id
1031 ,p_security_group_id => p_rec.security_group_id
1032 ,p_business_group_id => p_rec.business_group_id
1033 ,p_security_profile_id => p_rec.security_profile_id
1034 ,p_start_date => p_rec.start_date
1035 ,p_end_date => p_rec.end_date
1036 );
1037 --
1038 chk_assignment_exists
1039 (p_user_id => p_rec.user_id
1040 ,p_responsibility_id => p_rec.responsibility_id
1041 ,p_application_id => p_rec.responsibility_application_id
1042 ,p_security_group_id => p_rec.security_group_id
1043 );
1044 --
1045 hr_utility.set_location(' Leaving:'||l_proc, 10);
1046 End update_validate;
1047 --
1048 -- ----------------------------------------------------------------------------
1049 -- |-< Synchronize_Assignment_Dates >-----------------------------------------|
1050 -- ----------------------------------------------------------------------------
1051 --
1052 PROCEDURE Synchronize_Assignment_Dates
1053 (p_user_id
1054 IN per_sec_profile_assignments.user_id%TYPE
1055 ,p_responsibility_id
1056 IN per_sec_profile_assignments.responsibility_id%TYPE
1057 ,p_application_id
1058 IN per_sec_profile_assignments.responsibility_application_id%TYPE
1059 ,p_security_group_id
1060 IN per_sec_profile_assignments.security_group_id%TYPE
1061 ,p_business_group_id
1062 IN per_sec_profile_assignments.business_group_id%TYPE
1063 )
1064 IS
1065 --
1066 CURSOR c_get_minmax_dates
1067 IS
1068 SELECT MIN(s.start_date), MAX(s.end_date)
1069 FROM per_sec_profile_assignments s
1070 WHERE s.user_id = p_user_id
1071 AND s.responsibility_id = p_responsibility_id
1072 AND s.responsibility_application_id = p_application_id
1073 AND s.security_group_id = p_security_group_id
1074 AND s.business_group_id = p_business_group_id;
1075 --
1076 CURSOR c_chk_null_end_date
1077 IS
1078 SELECT 'Y'
1079 FROM per_sec_profile_assignments s
1080 WHERE s.user_id = p_user_id
1081 AND s.responsibility_id = p_responsibility_id
1082 AND s.responsibility_application_id = p_application_id
1083 AND s.security_group_id = p_security_group_id
1084 AND s.business_group_id = p_business_group_id
1085 AND s.end_date IS NULL;
1086 --
1087 l_exists VARCHAR2 (1);
1088 l_min_date DATE;
1089 l_max_date DATE;
1090 l_exception EXCEPTION;
1091 --
1092 BEGIN
1093 --
1094 OPEN c_get_minmax_dates;
1095 --
1096 FETCH c_get_minmax_dates INTO l_min_date, l_max_date;
1097 --
1098 IF c_get_minmax_dates%NOTFOUND THEN
1099 --
1100 -- Panic!
1101 --
1102 RAISE l_exception;
1103 END IF;
1104 --
1105 CLOSE c_get_minmax_dates;
1106 --
1107 --
1108 -- Commented the code out below for bug 1305436; end date should be
1109 -- kept as null rather than changed to EOT. If changed to EOT then
1110 -- the date 31-DEC-4712 shows up on FND User form IJH 8/6/00
1111 --
1112 -- This code has been uncommented due to the fact that eot must be
1113 -- stored in fnd_user_resp_groups if end_date is null or the security
1114 -- group will not be displayed in the responsibilities form.
1115 --
1116 OPEN c_chk_null_end_date;
1117 --
1118 FETCH c_chk_null_end_date INTO l_exists;
1119 --
1120 IF c_chk_null_end_date%FOUND THEN
1121 --
1122 -- A record that has not been end-dated exists..
1123 -- So set the end date in the fnd_user_resp_groups table to be the
1124 -- end of time...
1125 l_max_date := hr_general.END_OF_TIME;
1126 END IF;
1127
1128 CLOSE c_chk_null_end_date;
1129 --
1130 --
1131 -- Now we have got the start and end dates, so lets update the
1132 -- fnd_user_resp_groups table.
1133 --
1134 fnd_user_resp_groups_api.Update_Assignment
1135 (user_id => p_user_id
1136 ,responsibility_id => p_responsibility_id
1140 ,end_date => l_max_date
1137 ,responsibility_application_id => p_application_id
1138 ,security_group_id => p_security_group_id
1139 ,start_date => l_min_date
1141 ,description => ' ' -- ### description was supposed to default
1142 -- to null... but does not look like it has
1143 );
1144 --
1145 END Synchronize_Assignment_Dates;
1146 --
1147 end per_asp_bus;