[Home] [Help]
47: program_update_date)
48: select /*+ USE_NL(PSP) */
49: distinct ppl.security_profile_id, pcr.contact_person_id,
50: l_req_id, l_appl_id, l_prog_id, l_upd_date
51: from per_contact_relationships pcr,
52: per_person_list_stage ppl,
53: per_security_profiles psp
54: where ppl.person_id = p_person_id
55: and ppl.security_profile_id = psp.security_profile_id
84: program_update_date)
85: select /*+ USE_NL(PSP) */
86: distinct ppl.security_profile_id, pcr.contact_person_id,
87: l_req_id, l_appl_id, l_prog_id, l_upd_date
88: from per_contact_relationships pcr,
89: per_person_list ppl,
90: per_security_profiles psp
91: where ppl.person_id = p_person_id
92: and ppl.security_profile_id = psp.security_profile_id
151: from per_all_assignments_f asg
152: where asg.person_id = papf.person_id)
153: and not exists
154: (select null
155: from per_contact_relationships pcr
156: where pcr.contact_person_id = papf.person_id)
157: and ((p_generation_scope = 'ALL_BUS_GRP' and
158: papf.business_group_id = p_business_group_id) or
159: p_generation_scope <> 'ALL_BUS_GRP')
848: ,program_application_id, program_update_date
849: ,person_id)
850: select distinct p_security_profile_id, l_req_id, l_prog_id,
851: l_appl_id, l_upd_date, pcr.contact_person_id
852: from per_contact_relationships pcr,
853: per_all_people_f ppl -- per_person_list ppl for bug (6376000/4774264)
854: where ppl.person_id = pcr.person_id
855: and (pcr.business_group_id = p_business_group_id or
856: p_business_group_id is null)
872: ,program_application_id, program_update_date
873: ,person_id)
874: select distinct p_security_profile_id, l_req_id, l_prog_id,
875: l_appl_id, l_upd_date, pcr.contact_person_id
876: from per_contact_relationships pcr,
877: per_all_people_f ppl -- per_person_list ppl for bug (6376000/4774264)
878: where ppl.person_id = pcr.person_id
879: and (pcr.business_group_id = p_business_group_id or
880: p_business_group_id is null)
916: ,program_application_id, program_update_date
917: ,person_id)
918: select distinct ppl.security_profile_id, l_req_id, l_prog_id,
919: l_appl_id, l_upd_date, pcr.contact_person_id
920: from per_contact_relationships pcr,
921: per_person_list_stage ppl
922: where ppl.person_id = pcr.person_id
923: and (pcr.business_group_id = p_business_group_id or
924: p_business_group_id is null)
940: ,program_application_id, program_update_date
941: ,person_id)
942: select distinct ppl.security_profile_id, l_req_id, l_prog_id,
943: l_appl_id, l_upd_date, pcr.contact_person_id
944: from per_contact_relationships pcr,
945: per_person_list ppl
946: where ppl.person_id = pcr.person_id
947: and (pcr.business_group_id = p_business_group_id or
948: p_business_group_id is null)
986: from per_all_assignments_f asg
987: where asg.person_id = papf.person_id)
988: and not exists ---- Rever Commneted for for bug 4774264
989: (select null
990: from per_contact_relationships pcr
991: where pcr.contact_person_id = papf.person_id)
992: and not exists
993: (select null
994: from per_person_type_usages_f ptuf,
1019: from per_all_assignments_f asg
1020: where asg.person_id = papf.person_id)
1021: and not exists ---- Rever Commneted for for bug 4774264
1022: (select null
1023: from per_contact_relationships pcr
1024: where pcr.contact_person_id = papf.person_id)
1025: and not exists
1026: (select null
1027: from per_person_type_usages_f ptuf,
1692: and pspf.business_group_id = p_business_group_id
1693: )
1694: and ppl.person_id in (
1695: select pcr.contact_person_id
1696: from per_contact_relationships pcr,
1697: per_person_type_usages_f ptu,
1698: per_person_types ppt
1699: where pcr.person_id = p_person_id
1700: and pcr.contact_person_id = ptu.person_id
1718: and pspf.business_group_id is null
1719: )
1720: and ppl.person_id in (
1721: select pcr.contact_person_id
1722: from per_contact_relationships pcr,
1723: per_person_type_usages_f ptu,
1724: per_person_types ppt
1725: where pcr.person_id = p_person_id
1726: and pcr.contact_person_id = ptu.person_id
1741: pspf.view_all_candidates_flag = 'X'))
1742: )
1743: and ppl.person_id in (
1744: select pcr.contact_person_id
1745: from per_contact_relationships pcr,
1746: per_person_type_usages_f ptu,
1747: per_person_types ppt
1748: where pcr.person_id = p_person_id
1749: and pcr.contact_person_id = ptu.person_id
1772: pspf.view_all_candidates_flag = ''X''))
1773: and pspf.business_group_id = :p_business_group_id )
1774: and ppl.person_id in (
1775: select pcr.contact_person_id
1776: from per_contact_relationships pcr,
1777: per_person_type_usages_f ptu,
1778: per_person_types ppt
1779: where pcr.person_id = :p_person_id
1780: and pcr.contact_person_id = ptu.person_id
1802: pspf.view_all_candidates_flag = ''X''))
1803: and pspf.business_group_id is null )
1804: and ppl.person_id in (
1805: select pcr.contact_person_id
1806: from per_contact_relationships pcr,
1807: per_person_type_usages_f ptu,
1808: per_person_types ppt
1809: where pcr.person_id = :p_person_id
1810: and pcr.contact_person_id = ptu.person_id
1831: (pspf.view_all_contacts_flag = ''Y'' and
1832: pspf.view_all_candidates_flag = ''X'')))
1833: and ppl.person_id in (
1834: select pcr.contact_person_id
1835: from per_contact_relationships pcr,
1836: per_person_type_usages_f ptu,
1837: per_person_types ppt
1838: where pcr.person_id = :p_person_id
1839: and pcr.contact_person_id = ptu.person_id
1896: from per_all_assignments_f asg
1897: where asg.person_id = ppl.person_id)
1898: and not exists
1899: (select null
1900: from per_contact_relationships pcr
1901: where pcr.contact_person_id = ppl.person_id);
1902: else
1903: hr_utility.set_location(l_proc||' using actual table',18);
1904: delete from per_person_list ppl
1920: from per_all_assignments_f asg
1921: where asg.person_id = ppl.person_id)
1922: and not exists
1923: (select null
1924: from per_contact_relationships pcr
1925: where pcr.contact_person_id = ppl.person_id);
1926: end if;
1927:
1928: else
1943: from per_all_assignments_f asg
1944: where asg.person_id = ppl.person_id)
1945: and not exists
1946: (select null
1947: from per_contact_relationships pcr
1948: where pcr.contact_person_id = ppl.person_id);
1949: end if;
1950: --
1951: hr_utility.set_location('Leaving : '||l_proc,20);
4386: pspf.view_all_candidates_flag = 'X'))
4387: )
4388: and ppl.person_id in (
4389: select pcr.contact_person_id
4390: from per_contact_relationships pcr,
4391: per_person_type_usages_f ptu,
4392: per_person_types ppt
4393: where pcr.person_id = p_person_id
4394: and pcr.contact_person_id = ptu.person_id
4574: pspf.view_all_candidates_flag = 'X'))
4575: )
4576: and ppl.person_id in (
4577: select pcr.contact_person_id
4578: from per_contact_relationships pcr,
4579: per_person_type_usages_f ptu,
4580: per_person_types ppt
4581: where pcr.person_id = p_person_id
4582: and pcr.contact_person_id = ptu.person_id