[Home] [Help]
37: program_update_date)
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
103: from per_all_assignments_f asg
104: where asg.person_id = papf.person_id)
105: and not exists
106: (select null
107: from per_contact_relationships pcr
108: where pcr.contact_person_id = papf.person_id)
109: and ((p_generation_scope = 'ALL_BUS_GRP' and
110: papf.business_group_id = p_business_group_id) or
111: p_generation_scope <> 'ALL_BUS_GRP')
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
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)
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
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)
862: from per_all_assignments_f asg
863: where asg.person_id = papf.person_id)
864: and not exists ---- Rever Commneted for for bug 4774264
865: (select null
866: from per_contact_relationships pcr
867: where pcr.contact_person_id = papf.person_id)
868: and not exists
869: (select null
870: from per_person_type_usages_f ptuf,
1436: and pspf.business_group_id = p_business_group_id
1437: )
1438: and ppl.person_id in (
1439: select pcr.contact_person_id
1440: from per_contact_relationships pcr,
1441: per_person_type_usages_f ptu,
1442: per_person_types ppt
1443: where pcr.person_id = p_person_id
1444: and pcr.contact_person_id = ptu.person_id
1462: and pspf.business_group_id is null
1463: )
1464: and ppl.person_id in (
1465: select pcr.contact_person_id
1466: from per_contact_relationships pcr,
1467: per_person_type_usages_f ptu,
1468: per_person_types ppt
1469: where pcr.person_id = p_person_id
1470: and pcr.contact_person_id = ptu.person_id
1485: pspf.view_all_candidates_flag = 'X'))
1486: )
1487: and ppl.person_id in (
1488: select pcr.contact_person_id
1489: from per_contact_relationships pcr,
1490: per_person_type_usages_f ptu,
1491: per_person_types ppt
1492: where pcr.person_id = p_person_id
1493: and pcr.contact_person_id = ptu.person_id
1512: pspf.view_all_candidates_flag = ''X'')) '
1513: ||l_sub_str||')
1514: and ppl.person_id in (
1515: select pcr.contact_person_id
1516: from per_contact_relationships pcr,
1517: per_person_type_usages_f ptu,
1518: per_person_types ppt
1519: where pcr.person_id = '||p_person_id||
1520: ' and pcr.contact_person_id = ptu.person_id
1572: from per_all_assignments_f asg
1573: where asg.person_id = ppl.person_id)
1574: and not exists
1575: (select null
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
1594: from per_all_assignments_f asg
1595: where asg.person_id = ppl.person_id)
1596: and not exists
1597: (select null
1598: from per_contact_relationships pcr
1599: where pcr.contact_person_id = ppl.person_id);
1600: end if;
1601: --
1602: hr_utility.set_location('Leaving : '||l_proc,20);