[Home] [Help]
31:
32: -- Added DISTINCT so that it handles multiple contact relationships
33: -- between the same two people. Do not insert if using user-based
34: -- security as this is assessed dynamically.
35: insert into per_person_list(security_profile_id, person_id, request_id,
36: program_application_id, program_id,
37: program_update_date)
38: select /*+ USE_NL(PSP) */
39: distinct ppl.security_profile_id, pcr.contact_person_id,
38: select /*+ USE_NL(PSP) */
39: distinct ppl.security_profile_id, pcr.contact_person_id,
40: l_req_id, l_appl_id, l_prog_id, l_upd_date
41: from per_contact_relationships pcr,
42: per_person_list ppl,
43: per_security_profiles psp
44: where ppl.person_id = p_person_id
45: and ppl.security_profile_id = psp.security_profile_id
46: and (psp.view_all_contacts_flag = 'N' or
61: where asg.person_id = pcr.contact_person_id
62: and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
63: and not exists
64: (select /*+ NO_MERGE */ null
65: from per_person_list ppl1
66: where ppl1.person_id = pcr.contact_person_id
67: and ppl1.granted_user_id is null
68: and ppl1.security_profile_id = ppl.security_profile_id);
69: --
201: then
202: begin
203:
204: select 'X' into t_varchar
205: from per_person_list p2
206: where p2.person_id = l_per_tbl(j)
207: and p2.security_profile_id = l_sp_tbl(i);
208:
209: exception
208:
209: exception
210: when no_Data_found then
211:
212: insert into per_person_list(security_profile_id, person_id,
213: request_id, program_application_id,
214: program_id, program_update_date)
215: values (l_sp_tbl(i), l_per_tbl(j), l_req_id,
216: l_appl_id, l_prog_id, l_upd_date);
776: -- --> Query #1 will populate these records.
777:
778:
779: -- Query #1
780: insert into per_person_list(security_profile_id, request_id, program_id
781: ,program_application_id, program_update_date
782: ,person_id)
783: select distinct p_security_profile_id, l_req_id, l_prog_id,
784: l_appl_id, l_upd_date, pcr.contact_person_id
782: ,person_id)
783: select distinct p_security_profile_id, l_req_id, l_prog_id,
784: l_appl_id, l_upd_date, pcr.contact_person_id
785: from per_contact_relationships pcr,
786: per_all_people_f ppl -- per_person_list ppl for bug (6376000/4774264)
787: where ppl.person_id = pcr.person_id
788: and (pcr.business_group_id = p_business_group_id or
789: p_business_group_id is null)
790: -- and ppl.security_profile_id = p_security_profile_id for bug (6376000/4774264)
794: where asg.person_id = pcr.contact_person_id
795: and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
796: and not exists
797: (select null
798: from per_person_list ppl1
799: where ppl1.person_id = pcr.contact_person_id
800: and ppl1.granted_user_id is null
801: and ppl1.security_profile_id = p_security_profile_id ); -- ppl.security_profile_id) for bug (6376000/4774264)
802: --
815: -- This way, all contacts who are also another type will be ignored,
816: -- it should be assumed that their assignments will be processed by an
817: -- earlier part of LISTGEN.
818: -- Query #2
819: insert into per_person_list(security_profile_id, request_id, program_id
820: ,program_application_id, program_update_date
821: ,person_id)
822: select distinct ppl.security_profile_id, l_req_id, l_prog_id,
823: l_appl_id, l_upd_date, pcr.contact_person_id
821: ,person_id)
822: select distinct ppl.security_profile_id, l_req_id, l_prog_id,
823: l_appl_id, l_upd_date, pcr.contact_person_id
824: from per_contact_relationships pcr,
825: per_person_list ppl
826: where ppl.person_id = pcr.person_id
827: and (pcr.business_group_id = p_business_group_id or
828: p_business_group_id is null)
829: and ppl.security_profile_id = p_security_profile_id
833: where asg.person_id = pcr.contact_person_id
834: and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
835: and not exists
836: (select null
837: from per_person_list ppl1
838: where ppl1.person_id = pcr.contact_person_id
839: and ppl1.granted_user_id is null
840: and ppl1.security_profile_id = ppl.security_profile_id);
841: --
846: -- If there are additional assignments these will be excluded from here
847: -- on the assumption that they would have been evaluated in the previous
848: -- stages of listgen.
849: -- Query #3
850: insert into per_person_list(security_profile_id, request_id,
851: program_application_id, program_id,
852: program_update_date, person_id)
853: select distinct psp.security_profile_id, l_req_id, l_appl_id,
854: l_prog_id, l_upd_date, papf.person_id
873: and ptuf.person_type_id = ppt.person_type_id
874: and ptuf.person_id = papf.person_id)
875: and not exists
876: (select null
877: from per_person_list ppl
878: where ppl.person_id = papf.person_id
879: and ppl.granted_user_id is null
880: and ppl.security_profile_id = psp.security_profile_id);
881: --
908: BEGIN
909: --
910: hr_utility.set_location('hr_listgen.add_person_list_changes',10);
911: --
912: INSERT INTO per_person_list
913: (security_profile_id,
914: person_id,
915: request_id,
916: program_application_id,
921: p_request_id,
922: p_program_application_id,
923: p_program_id,
924: l_update_date
925: FROM per_person_list_changes plc
926: WHERE plc.security_profile_id = l_security_profile_id
927: AND NOT EXISTS
928: (SELECT NULL
929: FROM per_all_assignments_f pos
938: WHERE pos.person_id = plc.person_id
939: AND l_effective_date > pos.effective_start_date)
940: AND NOT EXISTS
941: (SELECT NULL
942: FROM per_person_list ppl
943: WHERE ppl.person_id = plc.person_id
944: AND ppl.granted_user_Id IS NULL
945: AND ppl.security_profile_id = plc.security_profile_id);
946: --
952: /* =======================================================================
953: NAME
954: create_person_list
955: DESCRIPTION
956: populates the per_person_list using dynamic sql
957: PARAMETERS
958: Few parameters are needed due it inheriting from the parent function
959: sec_rec - Row in per_security_profiles for current profile
960: ========================================================================= */
1107: hr_utility.set_location('Entering init_statement',10);
1108: fnd_dsql.init;
1109:
1110: fnd_dsql.add_text(
1111: 'INSERT into per_person_list
1112: (security_profile_id,
1113: person_id,
1114: request_id,
1115: program_application_id,
1296: fnd_dsql.add_bind( to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1297: fnd_dsql.add_text(' ))))'); -- 5214715
1298:
1299: fnd_dsql.add_text(' and not exists(select 1
1300: from per_person_list ppl
1301: where ppl.security_profile_id = ');
1302: fnd_dsql.add_bind(sec_rec.security_profile_id);
1303: fnd_dsql.add_text(' and ppl.person_id = assignment.person_id
1304: and ppl.granted_user_id is null)');
1350: --
1351: -- Clear the records for this person.
1352:
1353: -- Bug6809753 - start
1354: l_del_str := ' delete from per_person_list ppl where
1355: ppl.person_id = '||p_person_id||'
1356: and ppl.granted_user_id is null and exists
1357: (select ''X'' from per_security_profiles pspf
1358: where pspf.security_profile_id = ppl.security_profile_id ';
1360:
1361: if p_generation_scope = 'ALL_BUS_GRP' then
1362:
1363: /* Commented Bug6809753 - start
1364: delete from per_person_list ppl where
1365: ppl.person_id = p_person_id
1366: and ppl.granted_user_id is null and exists
1367: (select 'X' from per_security_profiles pspf
1368: where pspf.security_profile_id = ppl.security_profile_id
1376: end if;
1377: if p_generation_scope = 'ALL_GLOBAL' then
1378:
1379: /* Commented Bug6809753 - start
1380: delete from per_person_list ppl where
1381: ppl.person_id = p_person_id
1382: and ppl.granted_user_id is null and exists
1383: (select 'X' from per_security_profiles pspf
1384: where pspf.security_profile_id = ppl.security_profile_id
1392: end if;
1393: if p_generation_scope = 'ALL_PROFILES' then
1394:
1395: /* Commented Bug6809753 - start
1396: delete from per_person_list ppl where
1397: ppl.person_id = p_person_id
1398: and ppl.granted_user_id is null and exists
1399: (select 'X' from per_security_profiles pspf
1400: where pspf.security_profile_id = ppl.security_profile_id);
1425: /* Commented Bug6809753 - start
1426:
1427: if p_generation_scope = 'ALL_BUS_GRP' then
1428:
1429: delete from per_person_list ppl
1430: where ppl.security_profile_id in
1431: (select pspf.security_profile_id
1432: from per_security_profiles pspf
1433: where (pspf.view_all_contacts_flag = 'N' or
1451:
1452:
1453: End if;
1454: if p_generation_scope = 'ALL_GLOBAL' then
1455: delete from per_person_list ppl
1456: where ppl.security_profile_id in
1457: (select pspf.security_profile_id
1458: from per_security_profiles pspf
1459: where (pspf.view_all_contacts_flag = 'N' or
1475: and ppt.system_person_type = 'OTHER')
1476: and ppl.granted_user_id is null;
1477: End if;
1478: if p_generation_scope = 'ALL_PROFILES' then
1479: delete from per_person_list ppl
1480: where ppl.security_profile_id in
1481: (select pspf.security_profile_id
1482: from per_security_profiles pspf
1483: where (pspf.view_all_contacts_flag = 'N' or
1502:
1503: Commented Bug6809753 - end */
1504: --
1505: -- Bug6809753 -start
1506: l_del_str := ' delete from per_person_list ppl
1507: where ppl.security_profile_id in
1508: (select pspf.security_profile_id
1509: from per_security_profiles pspf
1510: where (pspf.view_all_contacts_flag = ''N'' or
1552: hr_utility.set_location('Entering : '||l_proc,10);
1553: --
1554: if p_generation_scope <> 'ALL_BUS_GRP' then
1555:
1556: delete from per_person_list ppl
1557: where ppl.security_profile_id in
1558: (select pspf.security_profile_id
1559: from per_security_profiles pspf
1560: where (pspf.view_all_contacts_flag = 'N' or
1576: from per_contact_relationships pcr
1577: where pcr.contact_person_id = ppl.person_id);
1578:
1579: else
1580: delete from per_person_list ppl
1581: where ppl.security_profile_id in
1582: (select pspf.security_profile_id
1583: from per_security_profiles pspf
1584: where (pspf.view_all_contacts_flag = 'N' or
1636: -- Clearing the Person static list
1637: if p_clear_people_flag then
1638: --
1639: delete
1640: from per_person_list
1641: where security_profile_id = p_security_profile_id
1642: and granted_user_id is null;
1643: --
1644: end if;
3046: -- are not in use.
3047: --
3048: -- A condition with view_all_contacts_flag = All and
3049: -- view_all_candidates_flag = None, needs caching (ie: similar to
3050: -- record existing in per_person_list). The additional OR condition
3051: -- is included as part of Candidate Security enchancements.
3052: if(sec_rec.view_all_contacts_flag = 'N' or
3053: (sec_rec.view_all_contacts_flag = 'Y' and
3054: sec_rec.view_all_candidates_flag = 'X')) and
3481:
3482: l_dummy number;
3483: cursor c_per_in_profile is
3484: select 1
3485: from per_person_list
3486: where person_id = p_person_id
3487: and granted_user_id is null
3488: and security_profile_id = p_security_profile_id;
3489: