[Home] [Help]
PACKAGE BODY: APPS.PER_DELETE_UNWANTED_SEC_PROF
Source
1 PACKAGE BODY PER_DELETE_UNWANTED_SEC_PROF AS
2 /* $Header: perdelsp.pkb 120.1.12020000.1 2012/07/04 11:33:43 sclakkar noship $ */
3 PROCEDURE concurrent_process
4 (errbuf IN OUT NOCOPY varchar2
5 ,retcode IN OUT NOCOPY number
6 ,p_report IN varchar2 DEFAULT 'Y'
7 ,p_del_all_sec_profile IN varchar2 DEFAULT NULL
8 ,p_security_profile_name IN per_security_profiles.security_profile_name%TYPE DEFAULT NULL) IS
9 l_strong_delete varchar2(1);
10 l_proc varchar2(100);
11 l_security_profile_id per_security_profiles.security_profile_id%TYPE;
12 CURSOR csr_security_prof_name IS
13 SELECT psp.security_profile_id
14 FROM per_security_profiles psp
15 WHERE psp.security_profile_name = p_security_profile_name;
16 l_wanted varchar2(1);
17 BEGIN
18 l_proc := 'per_delete_unwanted_sec_prof.concurrent_process';
19
20 hr_utility.set_location (l_proc
21 ,10);
22
23 l_strong_delete := 'Y';
24
25 errbuf := NULL;
26
27 retcode := 0;
28
29 IF p_report = 'Y' THEN
30 hr_utility.set_location (l_proc
31 ,20);
32
33 fnd_file.put_line (fnd_file.log
34 ,'Report - Shows Unused Security Profiles');
35
36 fnd_file.put_line (fnd_file.log
37 ,'------------------------------------------');
38
39 show_unwanted_sec_prof;
40 ELSIF p_report = 'N' THEN
41 hr_utility.set_location (l_proc
42 ,30);
43
44 IF p_del_all_sec_profile = 'Y' THEN
45 fnd_file.put_line (fnd_file.log
46 ,'Deleting All Unused Security Profiles');
47
48 fnd_file.put_line (fnd_file.log
49 ,'----------------------------------------');
50
51 del_all_unwanted_sec_prof (l_strong_delete);
52 ELSIF (nvl(p_del_all_sec_profile,'N') = 'N'
53 AND p_security_profile_name IS NOT NULL) THEN
54 hr_utility.set_location (l_proc
55 ,40);
56
57 OPEN csr_security_prof_name;
58
59 FETCH csr_security_prof_name
60 INTO l_security_profile_id;
61
62 IF csr_security_prof_name%NOTFOUND THEN
63 hr_utility.set_location (l_proc
64 ,50);
65
66 fnd_file.put_line (fnd_file.log
67 ,'Security Profile Name Invalid : '
68 || p_security_profile_name);
69 ELSE
70 hr_utility.set_location (l_proc
71 ,60);
72
73 l_wanted := check_unwanted_sec_prof (l_security_profile_id);
74
75 IF l_wanted = 'N' THEN
76 fnd_file.put_line (fnd_file.log
77 ,'Deleting Security Profile : '
78 || p_security_profile_name);
79
80 del_sec_prof (l_strong_delete
81 ,l_security_profile_id);
82 ELSE
83 fnd_file.put_line (fnd_file.log
84 ,'Not an unused Security Profile : '
85 || p_security_profile_name);
86
87 fnd_file.put_line (fnd_file.log
88 ,'Hence not deleted');
89 END IF;
90 END IF;
91 END IF;
92 END IF;
93
94 hr_utility.set_location (l_proc
95 ,70);
96 EXCEPTION
97 WHEN others THEN
98 hr_utility.set_location (l_proc
99 ,80);
100
101 fnd_file.put_line (fnd_file.log
102 ,'Exception Occurred: '
103 || sqlcode
104 || ' - '
105 || sqlerrm);
106 END concurrent_process;
107
108 PROCEDURE show_unwanted_sec_prof IS
109 CURSOR c_security_profile IS
110 SELECT security_profile_id
111 ,security_profile_name
112 FROM per_security_profiles
113 WHERE reporting_oracle_username IS NULL
114 AND view_all_flag <> 'Y';
115 CURSOR c_sec_profile_assignments
116 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
117 SELECT responsibility_id
118 FROM per_sec_profile_assignments
119 WHERE security_profile_id = p_security_profile_id;
120 CURSOR c_responsibility
121 (p_responsibility_id IN fnd_responsibility.responsibility_id%TYPE) IS
122 SELECT 'Y'
123 FROM fnd_responsibility
124 WHERE responsibility_id = p_responsibility_id
125 AND sysdate BETWEEN start_date
126 AND nvl (end_date
127 ,hr_general.end_of_time);
128 CURSOR c_user
129 (p_user_id IN fnd_user.user_id%TYPE) IS
130 SELECT 'Y'
131 FROM fnd_user
132 WHERE user_id = p_user_id
133 AND sysdate BETWEEN start_date
134 AND nvl (end_date
135 ,hr_general.end_of_time);
136 CURSOR c_sec_profile
137 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
138 SELECT level_value
139 FROM fnd_profile_option_values
140 WHERE profile_option_id =
141 (
142 SELECT profile_option_id
143 FROM fnd_profile_options
144 WHERE profile_option_name = 'PER_SECURITY_PROFILE_ID'
145 )
146 AND level_id = '10003'
147 AND profile_option_value = to_char (p_security_profile_id);
148 CURSOR c_sec_usr_profile
149 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
150 SELECT level_value
151 FROM fnd_profile_option_values
152 WHERE profile_option_id =
153 (
154 SELECT profile_option_id
155 FROM fnd_profile_options
156 WHERE profile_option_name = 'PER_SECURITY_PROFILE_ID'
157 )
158 AND level_id = '10004'
159 AND profile_option_value = to_char (p_security_profile_id);
160 CURSOR c_sec_profile_site
161 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
162 SELECT 'Y'
163 FROM fnd_profile_option_values
164 WHERE profile_option_id =
165 (
166 SELECT profile_option_id
167 FROM fnd_profile_options
168 WHERE profile_option_name = 'PER_SECURITY_PROFILE_ID'
169 )
170 AND level_id = '10001'
171 AND profile_option_value = to_char (p_security_profile_id);
172 CURSOR c_mo_profile
173 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
174 SELECT level_value
175 FROM fnd_profile_option_values
176 WHERE profile_option_id =
177 (
178 SELECT profile_option_id
179 FROM fnd_profile_options
180 WHERE profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
181 )
182 AND level_id = '10003'
183 AND profile_option_value = to_char (p_security_profile_id);
184 CURSOR c_mo_profile_site
185 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
186 SELECT 'Y'
187 FROM fnd_profile_option_values
188 WHERE profile_option_id =
189 (
190 SELECT profile_option_id
191 FROM fnd_profile_options
192 WHERE profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
193 )
194 AND level_id = '10001'
195 AND profile_option_value = to_char (p_security_profile_id);
196 l_security_profile_id per_security_profiles.security_profile_id%TYPE;
197 l_responsibility_id fnd_responsibility.responsibility_id%TYPE;
198 l_valid varchar2(1);
199 l_active varchar2(1);
200 l_profile_counter positive DEFAULT 1;
201 l_proc varchar2(100);
202 BEGIN
203 l_proc := 'per_delete_unwanted_sec_prof.show_unwanted_sec_prof';
204
205 hr_utility.set_location (l_proc
206 ,10);
207
208 fnd_file.put_line (fnd_file.log
209 ,' Inactive Security Profile Names ');
210
211 fnd_file.put_line (fnd_file.log
212 ,'-------+---------------------');
213
214 fnd_file.put_line (fnd_file.log
215 ,'Row# | Security Profile Name');
216
217 fnd_file.put_line (fnd_file.log
218 ,'-------+---------------------');
219
220 FOR csr_security_profile IN c_security_profile LOOP
221 l_valid := 'N';
222
223 hr_utility.set_location (l_proc
224 ,20);
225
226 FOR csr_sec_prf_assign IN c_sec_profile_assignments (csr_security_profile.security_profile_id) LOOP
227 OPEN c_responsibility (csr_sec_prf_assign.responsibility_id);
228
229 FETCH c_responsibility
230 INTO l_active;
231
232 IF c_responsibility%FOUND THEN
233 l_valid := 'Y';
234 END IF;
235
236 CLOSE c_responsibility;
237 END LOOP;
238
239 hr_utility.set_location (l_proc
240 ,30);
241
242 FOR csr_sec_profile IN c_sec_profile (csr_security_profile.security_profile_id) LOOP
243 OPEN c_responsibility (csr_sec_profile.level_value);
244
245 FETCH c_responsibility
246 INTO l_active;
247
248 IF c_responsibility%FOUND THEN
249 l_valid := 'Y';
250 END IF;
251
252 CLOSE c_responsibility;
253 END LOOP;
254
255 hr_utility.set_location (l_proc
256 ,40);
257
258 FOR csr_sec_usr_profile IN c_sec_usr_profile (csr_security_profile.security_profile_id) LOOP
259 OPEN c_user (csr_sec_usr_profile.level_value);
260
261 FETCH c_user
262 INTO l_active;
263
264 IF c_user%FOUND THEN
265 l_valid := 'Y';
266 END IF;
267
268 CLOSE c_user;
269 END LOOP;
270
271 OPEN c_sec_profile_site (csr_security_profile.security_profile_id);
272
273 FETCH c_sec_profile_site
274 INTO l_active;
275
276 IF c_sec_profile_site%FOUND THEN
277 l_valid := 'Y';
278 END IF;
279
280 CLOSE c_sec_profile_site;
281
282 hr_utility.set_location (l_proc
283 ,50);
284
285 FOR csr_mo_profile IN c_mo_profile (csr_security_profile.security_profile_id) LOOP
286 OPEN c_responsibility (csr_mo_profile.level_value);
287
288 FETCH c_responsibility
289 INTO l_active;
290
291 IF c_responsibility%FOUND THEN
292 l_valid := 'Y';
293 END IF;
294
295 CLOSE c_responsibility;
296 END LOOP;
297
298 OPEN c_mo_profile_site (csr_security_profile.security_profile_id);
299
300 FETCH c_mo_profile_site
301 INTO l_active;
302
303 IF c_mo_profile_site%FOUND THEN
304 l_valid := 'Y';
305 END IF;
306
307 CLOSE c_mo_profile_site;
308
309 IF nvl (l_valid
310 ,'N') = 'N' THEN
311 fnd_file.put_line (fnd_file.log
312 ,rpad (l_profile_counter
313 ,7
314 ,' ')
315 || '| '
316 || csr_security_profile.security_profile_name);
317
318 l_profile_counter := l_profile_counter + 1;
319 END IF;
320 END LOOP;
321
322 hr_utility.set_location (l_proc
323 ,60);
324
325 fnd_file.put_line (fnd_file.log
326 ,'-----------------------------------------------------');
327 END show_unwanted_sec_prof;
328
329 PROCEDURE del_sec_prof
330 (p_strong_delete IN varchar2
331 ,p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
332 CURSOR c_security_profile
333 (l_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
334 SELECT rowid
335 ,security_profile_id
336 ,view_all_flag
337 FROM per_security_profiles
338 WHERE security_profile_id = l_security_profile_id;
339 l_secgen_warn varchar2(1) DEFAULT 'N';
340 l_proc varchar2(100);
341 BEGIN
342 l_proc := 'per_delete_unwanted_sec_prof.del_sec_prof';
343
344 hr_utility.set_location (l_proc
345 ,10);
346
347 FOR csr_security_profile IN c_security_profile (p_security_profile_id) LOOP
348 hr_utility.set_location (l_proc
349 ,20);
350
351 IF nvl (csr_security_profile.view_all_flag
352 ,'N') = 'Y' THEN
353 hr_utility.set_message (800
354 ,'HR_6797_SECURITY_NO_DEL');
355
356 hr_utility.raise_error;
357 END IF;
358
359 IF p_strong_delete <> 'Y' THEN
360 hr_utility.set_location (l_proc
361 ,30);
362
363 per_security_profiles_pkg.pre_delete_validation (csr_security_profile.security_profile_id
364 ,csr_security_profile.view_all_flag
365 ,l_secgen_warn);
366
367 hr_utility.set_location (l_proc
368 ,40);
369
370 IF l_secgen_warn = 'Y' THEN
371 hr_utility.set_message (800
372 ,'HR_SECURITY_IN_USE');
373
374 hr_utility.raise_error;
375 END IF;
376
377 hr_utility.set_location (l_proc
378 ,100);
379
380 per_security_profiles_pkg.check_assigned_sec_profile (csr_security_profile.security_profile_id);
381 ELSE
382 hr_utility.set_location (l_proc
383 ,60);
384
385 strong_delete_sec_prof (p_security_profile_id);
386
387 hr_utility.set_location (l_proc
388 ,70);
389 END IF;
390
391 hr_utility.set_location (l_proc
392 ,80);
393
394 per_security_profiles_pkg.delete_row (csr_security_profile.rowid);
395
396 hr_utility.set_location (l_proc
397 ,90);
398 END LOOP;
399
400 hr_utility.set_location (l_proc
401 ,90);
402 END del_sec_prof;
403
404 PROCEDURE strong_delete_sec_prof
405 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
406 l_security_profile_id per_security_profiles.security_profile_id%TYPE;
407 l_proc varchar2(100);
408 BEGIN
409 l_proc := 'per_delete_unwanted_sec_prof.strong_delete_sec_prof';
410
411 hr_utility.set_location (l_proc
412 ,10);
413
414 l_security_profile_id := p_security_profile_id;
415
416 BEGIN
417 DELETE
418 FROM per_person_list
419 WHERE security_profile_id = l_security_profile_id;
420 EXCEPTION
421 WHEN no_data_found THEN
422 NULL;
423 END;
424
425 hr_utility.set_location (l_proc
426 ,20);
427
428 BEGIN
429 DELETE
430 FROM pay_payroll_list
431 WHERE security_profile_id = l_security_profile_id;
432 EXCEPTION
433 WHEN no_data_found THEN
434 NULL;
435 END;
436
437 hr_utility.set_location (l_proc
438 ,30);
439
440 BEGIN
441 DELETE
442 FROM per_organization_list
443 WHERE security_profile_id = l_security_profile_id;
444 EXCEPTION
445 WHEN no_data_found THEN
446 NULL;
447 END;
448
449 hr_utility.set_location (l_proc
450 ,40);
451
452 BEGIN
453 DELETE
454 FROM per_position_list
455 WHERE security_profile_id = l_security_profile_id;
456 EXCEPTION
457 WHEN no_data_found THEN
458 NULL;
459 END;
460
461 hr_utility.set_location (l_proc
462 ,100);
463
464 BEGIN
465 DELETE
466 FROM per_assignment_list
467 WHERE security_profile_id = l_security_profile_id;
468 EXCEPTION
469 WHEN no_data_found THEN
470 NULL;
471 END;
472
473 hr_utility.set_location (l_proc
474 ,60);
475
476 BEGIN
477 DELETE
478 FROM per_security_users
479 WHERE security_profile_id = l_security_profile_id;
480 EXCEPTION
481 WHEN no_data_found THEN
482 NULL;
483 END;
484
485 hr_utility.set_location (l_proc
486 ,70);
487
488 BEGIN
489 DELETE
490 FROM pay_security_payrolls
491 WHERE security_profile_id = l_security_profile_id;
492 EXCEPTION
493 WHEN no_data_found THEN
494 NULL;
495 END;
496
497 hr_utility.set_location (l_proc
498 ,80);
499
500 BEGIN
501 DELETE
502 FROM per_security_organizations
503 WHERE security_profile_id = l_security_profile_id;
504 EXCEPTION
505 WHEN no_data_found THEN
506 NULL;
507 END;
508
509 hr_utility.set_location (l_proc
510 ,90);
511
512 BEGIN
513 DELETE
514 FROM per_person_list_changes
515 WHERE security_profile_id = l_security_profile_id;
516 EXCEPTION
517 WHEN no_data_found THEN
518 NULL;
519 END;
520
521 hr_utility.set_location (l_proc
522 ,100);
523
524 BEGIN
525 DELETE
526 FROM per_sec_profile_assignments
527 WHERE security_profile_id = l_security_profile_id;
528 EXCEPTION
529 WHEN no_data_found THEN
530 NULL;
531 END;
532
533 hr_utility.set_location (l_proc
534 ,110);
535
536 BEGIN
537 UPDATE fnd_profile_option_values
538 SET profile_option_value = NULL
539 WHERE profile_option_id =
540 (
541 SELECT profile_option_id
542 FROM fnd_profile_options
543 WHERE profile_option_name = 'PER_SECURITY_PROFILE_ID'
544 )
545 AND profile_option_value = to_char (l_security_profile_id);
546 EXCEPTION
547 WHEN no_data_found THEN
548 NULL;
549 END;
550
551 hr_utility.set_location (l_proc
552 ,120);
553 END strong_delete_sec_prof;
554
555 PROCEDURE del_all_unwanted_sec_prof
556 (p_strong_delete IN varchar2) IS
557 CURSOR c_security_profile IS
558 SELECT security_profile_id
559 ,security_profile_name
560 FROM per_security_profiles
561 WHERE reporting_oracle_username IS NULL
562 AND view_all_flag <> 'Y';
563 CURSOR c_sec_profile_assignments
564 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
565 SELECT responsibility_id
566 FROM per_sec_profile_assignments
567 WHERE security_profile_id = p_security_profile_id;
568 CURSOR c_responsibility
569 (p_responsibility_id IN fnd_responsibility.responsibility_id%TYPE) IS
570 SELECT 'Y'
571 FROM fnd_responsibility
572 WHERE responsibility_id = p_responsibility_id
573 AND sysdate BETWEEN start_date
574 AND nvl (end_date
575 ,hr_general.end_of_time);
576 CURSOR c_user
577 (p_user_id IN fnd_user.user_id%TYPE) IS
578 SELECT 'Y'
579 FROM fnd_user
580 WHERE user_id = p_user_id
581 AND sysdate BETWEEN start_date
582 AND nvl (end_date
583 ,hr_general.end_of_time);
584 CURSOR c_sec_profile
585 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
586 SELECT level_value
587 FROM fnd_profile_option_values
588 WHERE profile_option_id =
589 (
590 SELECT profile_option_id
591 FROM fnd_profile_options
592 WHERE profile_option_name = 'PER_SECURITY_PROFILE_ID'
593 )
594 AND level_id = '10003'
595 AND profile_option_value = to_char (p_security_profile_id);
596 CURSOR c_sec_usr_profile
597 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
598 SELECT level_value
599 FROM fnd_profile_option_values
600 WHERE profile_option_id =
601 (
602 SELECT profile_option_id
603 FROM fnd_profile_options
604 WHERE profile_option_name = 'PER_SECURITY_PROFILE_ID'
605 )
606 AND level_id = '10004'
607 AND profile_option_value = to_char (p_security_profile_id);
608 CURSOR c_sec_profile_site
609 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
610 SELECT 'Y'
611 FROM fnd_profile_option_values
612 WHERE profile_option_id =
613 (
614 SELECT profile_option_id
615 FROM fnd_profile_options
616 WHERE profile_option_name = 'PER_SECURITY_PROFILE_ID'
617 )
618 AND level_id = '10001'
619 AND profile_option_value = to_char (p_security_profile_id);
620 CURSOR c_mo_profile
621 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
622 SELECT level_value
623 FROM fnd_profile_option_values
624 WHERE profile_option_id =
625 (
626 SELECT profile_option_id
627 FROM fnd_profile_options
628 WHERE profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
629 )
630 AND level_id = '10003'
631 AND profile_option_value = to_char (p_security_profile_id);
632 CURSOR c_mo_profile_site
633 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
634 SELECT 'Y'
635 FROM fnd_profile_option_values
636 WHERE profile_option_id =
637 (
638 SELECT profile_option_id
639 FROM fnd_profile_options
640 WHERE profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
641 )
642 AND level_id = '10001'
643 AND profile_option_value = to_char (p_security_profile_id);
644 l_security_profile_id per_security_profiles.security_profile_id%TYPE;
645 l_responsibility_id fnd_responsibility.responsibility_id%TYPE;
646 l_valid varchar2(1);
647 l_active varchar2(1);
648 l_profile_counter positive DEFAULT 1;
649 l_proc varchar2(100);
650 BEGIN
651 l_proc := 'per_delete_unwanted_sec_prof.strong_delete_sec_prof';
652
653 hr_utility.set_location (l_proc
654 ,10);
655
656 fnd_file.put_line (fnd_file.log
657 ,' Inactive Security Profile Names ');
658
659 fnd_file.put_line (fnd_file.log
660 ,'-------+---------------------');
661
662 fnd_file.put_line (fnd_file.log
663 ,'Row# | Security Profile Name');
664
665 fnd_file.put_line (fnd_file.log
666 ,'-------+---------------------');
667
668 FOR csr_security_profile IN c_security_profile LOOP
669 l_valid := 'N';
670
671 hr_utility.set_location (l_proc
672 ,20);
673
674 FOR csr_sec_prf_assign IN c_sec_profile_assignments (csr_security_profile.security_profile_id) LOOP
675 OPEN c_responsibility (csr_sec_prf_assign.responsibility_id);
676
677 FETCH c_responsibility
678 INTO l_active;
679
680 IF c_responsibility%FOUND THEN
681 l_valid := 'Y';
682 END IF;
683
684 CLOSE c_responsibility;
685 END LOOP;
686
687 hr_utility.set_location (l_proc
688 ,30);
689
690 FOR csr_sec_profile IN c_sec_profile (csr_security_profile.security_profile_id) LOOP
691 OPEN c_responsibility (csr_sec_profile.level_value);
692
693 FETCH c_responsibility
694 INTO l_active;
695
696 IF c_responsibility%FOUND THEN
697 l_valid := 'Y';
698 END IF;
699
700 CLOSE c_responsibility;
701 END LOOP;
702
703 hr_utility.set_location (l_proc
704 ,40);
705
706 FOR csr_sec_usr_profile IN c_sec_usr_profile (csr_security_profile.security_profile_id) LOOP
707 OPEN c_user (csr_sec_usr_profile.level_value);
708
709 FETCH c_user
710 INTO l_active;
711
712 IF c_user%FOUND THEN
713 l_valid := 'Y';
714 END IF;
715
716 CLOSE c_user;
717 END LOOP;
718
719 OPEN c_sec_profile_site (csr_security_profile.security_profile_id);
720
721 FETCH c_sec_profile_site
722 INTO l_active;
723
724 IF c_sec_profile_site%FOUND THEN
725 l_valid := 'Y';
726 END IF;
727
728 CLOSE c_sec_profile_site;
729
730 hr_utility.set_location (l_proc
731 ,50);
732
733 FOR csr_mo_profile IN c_mo_profile (csr_security_profile.security_profile_id) LOOP
734 OPEN c_responsibility (csr_mo_profile.level_value);
735
736 FETCH c_responsibility
737 INTO l_active;
738
739 IF c_responsibility%FOUND THEN
740 l_valid := 'Y';
741 END IF;
742
743 CLOSE c_responsibility;
744 END LOOP;
745
746 OPEN c_mo_profile_site (csr_security_profile.security_profile_id);
747
748 FETCH c_mo_profile_site
749 INTO l_active;
750
751 IF c_mo_profile_site%FOUND THEN
752 l_valid := 'Y';
753 END IF;
754
755 CLOSE c_mo_profile_site;
756
757 IF nvl (l_valid
758 ,'N') = 'N' THEN
759 fnd_file.put_line (fnd_file.log
760 ,rpad (l_profile_counter
761 ,7
762 ,' ')
763 || '| Deleting Security Profile :'
764 || csr_security_profile.security_profile_name);
765
766 del_sec_prof (p_strong_delete
767 ,csr_security_profile.security_profile_id);
768
769 l_profile_counter := l_profile_counter + 1;
770 END IF;
771 END LOOP;
772
773 fnd_file.put_line (fnd_file.log
774 ,'All unused Security Profiles Deleted');
775
776 fnd_file.put_line (fnd_file.log
777 ,'-------+-------------------------------');
778
779 hr_utility.set_location (l_proc
780 ,60);
781 END del_all_unwanted_sec_prof;
782
783 FUNCTION check_unwanted_sec_prof
784 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) RETURN varchar2 IS
785 CURSOR c_security_profile IS
786 SELECT security_profile_id
787 FROM per_security_profiles
788 WHERE reporting_oracle_username IS NULL
789 AND view_all_flag <> 'Y'
790 AND security_profile_id = p_security_profile_id;
791 CURSOR c_sec_profile_assignments
792 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
793 SELECT responsibility_id
794 FROM per_sec_profile_assignments
795 WHERE security_profile_id = p_security_profile_id;
796 CURSOR c_responsibility
797 (p_responsibility_id IN fnd_responsibility.responsibility_id%TYPE) IS
798 SELECT 'Y'
799 FROM fnd_responsibility
800 WHERE responsibility_id = p_responsibility_id
801 AND sysdate BETWEEN start_date
802 AND nvl (end_date
803 ,hr_general.end_of_time);
804 CURSOR c_user
805 (p_user_id IN fnd_user.user_id%TYPE) IS
806 SELECT 'Y'
807 FROM fnd_user
808 WHERE user_id = p_user_id
809 AND sysdate BETWEEN start_date
810 AND nvl (end_date
811 ,hr_general.end_of_time);
812 CURSOR c_sec_profile
813 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
814 SELECT level_value
815 FROM fnd_profile_option_values
816 WHERE profile_option_id =
817 (
818 SELECT profile_option_id
819 FROM fnd_profile_options
820 WHERE profile_option_name = 'PER_SECURITY_PROFILE_ID'
821 )
822 AND level_id = '10003'
823 AND profile_option_value = to_char (p_security_profile_id);
824 CURSOR c_sec_usr_profile
825 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
826 SELECT level_value
827 FROM fnd_profile_option_values
828 WHERE profile_option_id =
829 (
830 SELECT profile_option_id
831 FROM fnd_profile_options
832 WHERE profile_option_name = 'PER_SECURITY_PROFILE_ID'
833 )
834 AND level_id = '10004'
835 AND profile_option_value = to_char (p_security_profile_id);
836 CURSOR c_sec_profile_site
837 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
838 SELECT 'Y'
839 FROM fnd_profile_option_values
840 WHERE profile_option_id =
841 (
842 SELECT profile_option_id
843 FROM fnd_profile_options
844 WHERE profile_option_name = 'PER_SECURITY_PROFILE_ID'
845 )
846 AND level_id = '10001'
847 AND profile_option_value = to_char (p_security_profile_id);
848 CURSOR c_mo_profile
849 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
850 SELECT level_value
851 FROM fnd_profile_option_values
852 WHERE profile_option_id =
853 (
854 SELECT profile_option_id
855 FROM fnd_profile_options
856 WHERE profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
857 )
858 AND level_id = '10003'
859 AND profile_option_value = to_char (p_security_profile_id);
860 CURSOR c_mo_profile_site
861 (p_security_profile_id IN per_security_profiles.security_profile_id%TYPE) IS
862 SELECT 'Y'
863 FROM fnd_profile_option_values
864 WHERE profile_option_id =
865 (
866 SELECT profile_option_id
867 FROM fnd_profile_options
868 WHERE profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
869 )
870 AND level_id = '10001'
871 AND profile_option_value = to_char (p_security_profile_id);
872 l_security_profile_id per_security_profiles.security_profile_id%TYPE;
873 l_responsibility_id fnd_responsibility.responsibility_id%TYPE;
874 l_valid varchar2(1);
875 l_active varchar2(1);
876 l_proc varchar2(100);
877 BEGIN
878 l_proc := 'per_delete_unwanted_sec_prof.check_unwanted_sec_prof';
879
880 hr_utility.set_location (l_proc
881 ,10);
882
883 l_valid := 'Y';
884
885 OPEN c_security_profile;
886
887 FETCH c_security_profile
888 INTO l_security_profile_id;
889
890 IF c_security_profile%FOUND THEN
891 l_valid := 'N';
892
893 FOR csr_sec_prf_assign IN c_sec_profile_assignments (l_security_profile_id) LOOP
894 OPEN c_responsibility (l_security_profile_id);
895
896 FETCH c_responsibility
897 INTO l_active;
898
899 IF c_responsibility%FOUND THEN
900 l_valid := 'Y';
901 END IF;
902
903 CLOSE c_responsibility;
904 END LOOP;
905
906 FOR csr_sec_usr_profile IN c_sec_usr_profile (l_security_profile_id) LOOP
907 OPEN c_user (csr_sec_usr_profile.level_value);
908
909 FETCH c_user
910 INTO l_active;
911
912 IF c_user%FOUND THEN
913 l_valid := 'Y';
914 END IF;
915
916 CLOSE c_user;
917 END LOOP;
918
919 OPEN c_sec_profile_site (l_security_profile_id);
920
921 FETCH c_sec_profile_site
922 INTO l_active;
923
924 IF c_sec_profile_site%FOUND THEN
925 l_valid := 'Y';
926 END IF;
927
928 CLOSE c_sec_profile_site;
929
930 FOR csr_mo_profile IN c_mo_profile (l_security_profile_id) LOOP
931 OPEN c_responsibility (csr_mo_profile.level_value);
932
933 FETCH c_responsibility
934 INTO l_active;
935
936 IF c_responsibility%FOUND THEN
937 l_valid := 'Y';
938 END IF;
939
940 CLOSE c_responsibility;
941 END LOOP;
942
943 OPEN c_mo_profile_site (l_security_profile_id);
944
945 FETCH c_mo_profile_site
946 INTO l_active;
947
948 IF c_mo_profile_site%FOUND THEN
949 l_valid := 'Y';
950 END IF;
951
952 CLOSE c_mo_profile_site;
953 END IF;
954
955 CLOSE c_security_profile;
956
957 RETURN l_valid;
958 END check_unwanted_sec_prof;
959 END per_delete_unwanted_sec_prof;