[Home] [Help]
384: hr_utility.set_location(l_proc, 10);
385: --
386: -- Insert all organizations in the hierarchy (excluding the top organization).
387: --
388: INSERT INTO per_organization_list
389: (security_profile_id,
390: organization_id,
391: request_id,
392: program_application_id,
413: --
414: -- Insert all organizations in the organization list that have their
415: -- include / exclude flag set to 'I'.
416: --
417: INSERT INTO per_organization_list
418: (security_profile_id,
419: request_id,
420: program_id,
421: program_application_id,
431: WHERE pso.entry_type = 'I'
432: AND pso.security_profile_id = l_security_profile_id
433: AND NOT EXISTS
434: (SELECT NULL
435: FROM per_organization_list pol
436: WHERE pol.security_profile_id = l_security_profile_id
437: AND pol.organization_id = pso.organization_id);
438: --
439: hr_utility.set_location(l_proc, 30);
445: IF l_organization_id IS NOT NULL THEN
446: --
447: hr_utility.set_location(l_proc, 40);
448: --
449: INSERT INTO per_organization_list
450: (security_profile_id,
451: organization_id,
452: request_id,
453: program_application_id,
462: FROM DUAL
463: /* Duplicate check. Required because of organization list Includes */
464: WHERE NOT EXISTS
465: (SELECT NULL
466: FROM per_organization_list pol
467: WHERE pol.security_profile_id = l_security_profile_id
468: AND pol.user_id IS NULL
469: AND pol.organization_id = l_organization_id);
470: --
478: NVL(l_exclude_business_groups_flag, 'N') = 'N' THEN
479: --
480: -- Include all business groups in the hierarchy for the GLOBAL sec prof.
481: --
482: INSERT INTO per_organization_list
483: (security_profile_id,
484: organization_id,
485: request_id,
486: program_application_id,
493: p_program_application_id,
494: p_program_id,
495: l_update_date
496: FROM hr_all_organization_units org
497: , per_organization_list lst
498: WHERE lst.security_profile_id = l_security_profile_id
499: AND lst.organization_id=org.organization_id
500: AND NOT EXISTS
501: (SELECT 1
498: WHERE lst.security_profile_id = l_security_profile_id
499: AND lst.organization_id=org.organization_id
500: AND NOT EXISTS
501: (SELECT 1
502: FROM per_organization_list lst2
503: WHERE lst2.organization_id = org.business_group_id
504: AND lst2.user_id IS NULL
505: AND lst2.security_profile_id = l_security_profile_id);
506: --
508: NVL(l_exclude_business_groups_flag, 'N') = 'N' THEN
509: --
510: -- Include the business group for a LOCAL security profile.
511: --
512: INSERT INTO per_organization_list
513: (security_profile_id,
514: organization_id,
515: request_id,
516: program_application_id,
525: FROM per_security_profiles s
526: WHERE s.security_profile_id = l_security_profile_id
527: AND NOT EXISTS
528: (SELECT NULL
529: FROM per_organization_list b
530: WHERE b.organization_id = s.business_group_id
531: AND b.user_id IS NULL
532: AND b.security_profile_id = l_security_profile_id);
533: --
535: --
536: -- Remove the organizations listed as 'Exclude' in the organization list.
537: --
538: DELETE
539: FROM per_organization_list
540: WHERE security_profile_id = l_security_profile_id
541: AND user_id IS NULL
542: AND organization_id IN
543: (SELECT organization_id
544: FROM per_security_organizations
545: WHERE security_profile_id = l_security_profile_id
546: AND entry_type = 'E');
547: --
548: -- BUSINESS_GROUP_ID's should be excluded from PER_ORGANIZATION_LIST for a
549: -- global security profile with EXCLUDE_BUSINESS_GROUPS_FLAG is set as 'Y'.
550: --
551: -- Here an exclusive DELETE command is used, because in global security
552: -- profile a business group can be a child of child of another business
559: IF p_business_group_mode = 'GLOBAL' AND
560: NVL(l_exclude_business_groups_flag, 'N') = 'Y' THEN
561: --
562: DELETE
563: FROM per_organization_list pol
564: WHERE pol.security_profile_id = l_security_profile_id
565: AND pol.user_id IS NULL
566: AND pol.organization_id IN
567: (SELECT org.business_group_id
628: FROM per_pos_structure_elements p
629: WHERE EXISTS
630: (SELECT NULL
631: FROM hr_all_positions_f pp,
632: per_organization_list ol
633: WHERE ol.organization_id = pp.organization_id
634: AND pp.position_id = p.subordinate_position_id
635: AND ol.security_profile_id= l_security_profile_id)
636: START WITH p.parent_position_id = l_position_id
666: FROM sys.dual
667: WHERE EXISTS
668: (SELECT NULL
669: FROM hr_all_positions_f pp,
670: per_organization_list ol
671: WHERE ol.organization_id = pp.organization_id
672: AND pp.position_id = l_position_id
673: AND ol.security_profile_id= l_security_profile_id);
674: --
1139: fnd_dsql.add_text(', per_all_people_f PERSON ');
1140: End if;
1141:
1142: if p_sec_rec.view_all_organizations_flag='N' then
1143: Fnd_dsql.add_text(', per_organization_list ol ');
1144: end if;
1145:
1146: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1147: Fnd_dsql.add_text(', per_person_type_usages_f PERSON_TYPE ');
1620: --
1621: hr_utility.set_location('Entering : '||l_proc, 10);
1622: -- Clearing Organization static list
1623: delete
1624: from per_organization_list
1625: where security_profile_id = p_security_profile_id
1626: and user_id is null;
1627: --
1628: hr_utility.set_location(l_proc, 20);