[Home] [Help]
448: hr_utility.set_location(l_proc, 10);
449: --
450: -- Insert all organizations in the hierarchy (excluding the top organization).
451: --
452: INSERT INTO per_organization_list
453: (security_profile_id,
454: organization_id,
455: request_id,
456: program_application_id,
477: --
478: -- Insert all organizations in the organization list that have their
479: -- include / exclude flag set to 'I'.
480: --
481: INSERT INTO per_organization_list
482: (security_profile_id,
483: request_id,
484: program_id,
485: program_application_id,
495: WHERE pso.entry_type = 'I'
496: AND pso.security_profile_id = l_security_profile_id
497: AND NOT EXISTS
498: (SELECT NULL
499: FROM per_organization_list pol
500: WHERE pol.security_profile_id = l_security_profile_id
501: AND pol.organization_id = pso.organization_id);
502: --
503: hr_utility.set_location(l_proc, 30);
509: IF l_organization_id IS NOT NULL THEN
510: --
511: hr_utility.set_location(l_proc, 40);
512: --
513: INSERT INTO per_organization_list
514: (security_profile_id,
515: organization_id,
516: request_id,
517: program_application_id,
526: FROM DUAL
527: /* Duplicate check. Required because of organization list Includes */
528: WHERE NOT EXISTS
529: (SELECT NULL
530: FROM per_organization_list pol
531: WHERE pol.security_profile_id = l_security_profile_id
532: AND pol.user_id IS NULL
533: AND pol.organization_id = l_organization_id);
534: --
542: NVL(l_exclude_business_groups_flag, 'N') = 'N' THEN
543: --
544: -- Include all business groups in the hierarchy for the GLOBAL sec prof.
545: --
546: INSERT INTO per_organization_list
547: (security_profile_id,
548: organization_id,
549: request_id,
550: program_application_id,
557: p_program_application_id,
558: p_program_id,
559: l_update_date
560: FROM hr_all_organization_units org
561: , per_organization_list lst
562: WHERE lst.security_profile_id = l_security_profile_id
563: AND lst.organization_id=org.organization_id
564: AND NOT EXISTS
565: (SELECT 1
562: WHERE lst.security_profile_id = l_security_profile_id
563: AND lst.organization_id=org.organization_id
564: AND NOT EXISTS
565: (SELECT 1
566: FROM per_organization_list lst2
567: WHERE lst2.organization_id = org.business_group_id
568: AND lst2.user_id IS NULL
569: AND lst2.security_profile_id = l_security_profile_id);
570: --
572: NVL(l_exclude_business_groups_flag, 'N') = 'N' THEN
573: --
574: -- Include the business group for a LOCAL security profile.
575: --
576: INSERT INTO per_organization_list
577: (security_profile_id,
578: organization_id,
579: request_id,
580: program_application_id,
589: FROM per_security_profiles s
590: WHERE s.security_profile_id = l_security_profile_id
591: AND NOT EXISTS
592: (SELECT NULL
593: FROM per_organization_list b
594: WHERE b.organization_id = s.business_group_id
595: AND b.user_id IS NULL
596: AND b.security_profile_id = l_security_profile_id);
597: --
599: --
600: -- Remove the organizations listed as 'Exclude' in the organization list.
601: --
602: DELETE
603: FROM per_organization_list
604: WHERE security_profile_id = l_security_profile_id
605: AND user_id IS NULL
606: AND organization_id IN
607: (SELECT organization_id
608: FROM per_security_organizations
609: WHERE security_profile_id = l_security_profile_id
610: AND entry_type = 'E');
611: --
612: -- BUSINESS_GROUP_ID's should be excluded from PER_ORGANIZATION_LIST for a
613: -- global security profile with EXCLUDE_BUSINESS_GROUPS_FLAG is set as 'Y'.
614: --
615: -- Here an exclusive DELETE command is used, because in global security
616: -- profile a business group can be a child of child of another business
623: IF p_business_group_mode = 'GLOBAL' AND
624: NVL(l_exclude_business_groups_flag, 'N') = 'Y' THEN
625: --
626: DELETE
627: FROM per_organization_list pol
628: WHERE pol.security_profile_id = l_security_profile_id
629: AND pol.user_id IS NULL
630: AND pol.organization_id IN
631: (SELECT org.business_group_id
692: FROM per_pos_structure_elements p
693: WHERE EXISTS
694: (SELECT NULL
695: FROM hr_all_positions_f pp,
696: per_organization_list ol
697: WHERE ol.organization_id = pp.organization_id
698: AND pp.position_id = p.subordinate_position_id
699: AND ol.security_profile_id= l_security_profile_id)
700: START WITH p.parent_position_id = l_position_id
730: FROM sys.dual
731: WHERE EXISTS
732: (SELECT NULL
733: FROM hr_all_positions_f pp,
734: per_organization_list ol
735: WHERE ol.organization_id = pp.organization_id
736: AND pp.position_id = l_position_id
737: AND ol.security_profile_id= l_security_profile_id);
738: --
1352: fnd_dsql.add_text(', per_all_people_f PERSON ');
1353: End if;
1354: --Commented for debug- 8533491
1355: /* if p_sec_rec.view_all_organizations_flag='N' then
1356: Fnd_dsql.add_text(', per_organization_list ol ');
1357: end if;*/
1358:
1359: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1360: Fnd_dsql.add_text(', per_person_type_usages_f PERSON_TYPE ');
1410: /* fnd_dsql.add_text(' and ol.security_profile_id = ');
1411: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1412: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');*/
1413:
1414: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_organization_list) */ 1 FROM per_organization_list');
1415: fnd_dsql.add_text(' WHERE security_profile_id = ');
1416: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1417: fnd_dsql.add_text(' AND organization_id=ASSIGNMENT.organization_id)');
1418: end if;
1969: --
1970: hr_utility.set_location('Entering : '||l_proc, 10);
1971: -- Clearing Organization static list
1972: delete
1973: from per_organization_list
1974: where security_profile_id = p_security_profile_id
1975: and user_id is null;
1976: --
1977: hr_utility.set_location(l_proc, 20);
2368: if g_debug then
2369: hr_utility.set_location(l_proc, 40);
2370: end if;
2371: l_from_str := ' from per_security_profiles sec,
2372: per_organization_list org ';
2373: -- Modified for bug 11737636
2374: l_cond_str := ' org.security_profile_id = sec.security_profile_id
2375: and org.organization_id = :l_organization_id
2376: and sec.view_all_organizations_flag = :l_Restrict
2613: hr_utility.set_location(l_proc, 130);
2614: end if;
2615: l_from_str := ' from per_security_profiles sec,
2616: pay_payroll_list pay,
2617: per_organization_list org ';
2618: l_cond_str := ' org.security_profile_id = sec.security_profile_id
2619: and pay.security_profile_id = sec.security_profile_id
2620: and pay.security_profile_id = org.security_profile_id
2621: and org.organization_id = :l_organization_id
2696: if g_debug then
2697: hr_utility.set_location(l_proc, 160);
2698: end if;
2699: l_from_str := ' from per_security_profiles sec,
2700: per_organization_list org ';
2701: l_cond_str := ' org.security_profile_id = sec.security_profile_id
2702: and org.organization_id = :l_organization_id
2703: and sec.view_all_organizations_flag = :l_Restrict
2704: and nvl(sec.top_organization_method, :l_S) <> :l_U
3019: hr_utility.set_location(l_proc, 280);
3020: end if;
3021: l_from_str := ' from per_security_profiles sec,
3022: per_position_list posl,
3023: per_organization_list org ';
3024: -- Modified for bug 11737636
3025: l_cond_str := ' org.security_profile_id = sec.security_profile_id
3026: and posl.security_profile_id = sec.security_profile_id
3027: and posl.security_profile_id = org.security_profile_id
3118: if g_debug then
3119: hr_utility.set_location(l_proc, 310);
3120: end if;
3121: l_from_str := ' from per_security_profiles sec,
3122: per_organization_list org ';
3123: -- Changed for bug 11737636
3124: l_cond_str := ' org.security_profile_id = sec.security_profile_id
3125: and org.organization_id = :l_organization_id
3126: and sec.view_all_organizations_flag = :l_Restrict
3465: end if;
3466: l_from_str := ' from per_security_profiles sec,
3467: pay_payroll_list pay,
3468: per_position_list posl,
3469: per_organization_list org ';
3470: l_cond_str := ' org.security_profile_id = sec.security_profile_id
3471: and posl.security_profile_id = sec.security_profile_id
3472: and pay.security_profile_id = sec.security_profile_id
3473: and pay.security_profile_id = posl.security_profile_id
3569: hr_utility.set_location(l_proc, 460);
3570: end if;
3571: l_from_str := ' from per_security_profiles sec,
3572: per_position_list posl,
3573: per_organization_list org ';
3574: l_cond_str := ' org.security_profile_id = sec.security_profile_id
3575: and posl.security_profile_id = sec.security_profile_id
3576: and posl.security_profile_id = org.security_profile_id
3577: and org.organization_id = :l_organization_id
3667: hr_utility.set_location(l_proc, 490);
3668: end if;
3669: l_from_str := ' from per_security_profiles sec,
3670: pay_payroll_list pay,
3671: per_organization_list org ';
3672: l_cond_str := ' org.security_profile_id = sec.security_profile_id
3673: and pay.security_profile_id = sec.security_profile_id
3674: and org.organization_id = :l_organization_id
3675: and pay.payroll_id = :l_payroll_id
3851: if g_debug then
3852: hr_utility.set_location(l_proc, 550);
3853: end if;
3854: l_from_str := ' from per_security_profiles sec,
3855: per_organization_list org ';
3856: l_cond_str := ' org.security_profile_id = sec.security_profile_id
3857: and org.organization_id = :l_organization_id
3858: and sec.view_all_organizations_flag = :l_Restrict
3859: and nvl(sec.top_organization_method, :l_S) <> :l_U