[Home] [Help]
159: p_generation_scope <> 'ALL_BUS_GRP')
160: and not exists
161: (select null
162: from per_person_type_usages_f ptuf,
163: per_person_types ppt
164: where ppt.system_person_type = 'IRC_REG_USER'
165: and ptuf.person_type_id = ppt.person_type_id
166: and ptuf.person_id = papf.person_id);
167: -- Fetch the security profiles that restrict by contacts, are
991: where pcr.contact_person_id = papf.person_id)
992: and not exists
993: (select null
994: from per_person_type_usages_f ptuf,
995: per_person_types ppt
996: where ppt.system_person_type = 'IRC_REG_USER'
997: and ptuf.person_type_id = ppt.person_type_id
998: and ptuf.person_id = papf.person_id)
999: and not exists
1024: where pcr.contact_person_id = papf.person_id)
1025: and not exists
1026: (select null
1027: from per_person_type_usages_f ptuf,
1028: per_person_types ppt
1029: where ppt.system_person_type = 'IRC_REG_USER'
1030: and ptuf.person_type_id = ppt.person_type_id
1031: and ptuf.person_id = papf.person_id)
1032: and not exists
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
1701: and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
1702: between ptu.effective_start_date
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
1727: and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
1728: between ptu.effective_start_date
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
1750: and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
1751:
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
1781: and :l_effective_date
1782: between ptu.effective_start_date
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
1811: and :l_effective_date
1812: between ptu.effective_start_date
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
1840: and :l_effective_date
1841: between ptu.effective_start_date
2050: /* Commented Bug6809753 - start
2051: cursor c_current_person is
2052: select ppf.person_id, greatest(min(ppf.effective_start_date), p_effective_date)
2053: from per_person_type_usages_f ppf,
2054: per_person_types ppt
2055: where ppf.person_id = p_person_id
2056: and ppf.person_type_id = ppt.person_type_id --taken out for Performance bug.
2057: -- Current person today
2058: and (( -- ppf.person_type_id = ppt.person_type_id and
2074: -- Bug6809753 - start
2075: cursor c_current_person is
2076: select ppf.person_id, greatest(min(ppf.effective_start_date), p_effective_date)
2077: from per_person_type_usages_f ppf,
2078: per_person_types ppt
2079: where ppf.person_id = p_person_id
2080: /*
2081: ** Current person today
2082: */
2102:
2103: cursor c_exclude_person is
2104: select ppf.person_id
2105: from per_person_type_usages_f ppf,
2106: per_person_types ppt
2107: where ppf.person_id = p_person_id
2108:
2109: and ( ppf.person_type_id = ppt.person_type_id
2110: and ppt.business_group_id =
2118: cursor c_former_person is
2119: select ppf.person_id, paf.assignment_type,
2120: least(max(paf.effective_end_date), p_effective_date) effective_date
2121: from per_person_type_usages_f ppf,
2122: per_person_types ppt,
2123: per_all_assignments_f paf
2124: where ppf.person_id = p_person_id
2125: and ppf.person_id = paf.person_id
2126: and paf.assignment_type in ('A','C','E')
2135: ** ...as an ex person on the effective date
2136: */
2137: and exists (select null
2138: from per_person_type_usages_f ppf1,
2139: per_person_types ppt1
2140: where ppf1.person_id = ppf.person_id
2141: and p_effective_date between ppf1.effective_start_date
2142: and ppf1.effective_end_date
2143: and ppf1.person_type_id = ppt1.person_type_id
2157: ** who are EX-EMP/EX-CWK but who are also APL
2158: */
2159: and not exists (select null
2160: from per_person_type_usages_f ppf2,
2161: per_person_types ppt2
2162: where ppf2.person_id = ppf.person_id
2163: and p_effective_date < ppf2.effective_end_date
2164: and ppf2.person_type_id = ppt2.person_type_id
2165: and ppt2.business_group_id = nvl(p_business_group_id,
2186:
2187: cursor c_is_current_apl is
2188: select 'Y'
2189: from per_person_type_usages_f ptu,
2190: per_person_types ppt
2191: where ptu.person_id = p_person_id
2192: and p_effective_date < ptu.effective_end_date
2193: and ptu.person_type_Id = ppt.person_type_id
2194: and ppt.system_person_type = 'APL';
2194: and ppt.system_person_type = 'APL';
2195: cursor c_is_former is
2196: select 'Y'
2197: from per_person_type_usages_f ptu,
2198: per_person_types ppt
2199: where ptu.person_id = p_person_id
2200: and p_effective_date between ptu.effective_start_date
2201: and ptu.effective_end_date
2202: and ptu.person_type_id = ppt.person_type_id
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
4395: and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
4396:
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
4583: and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
4584: between ptu.effective_start_date
6169: endperson number) is
6170: select distinct ppf.person_id
6171: from per_person_type_usages_f ppf
6172: ,pay_payroll_actions ppa
6173: ,per_person_types ppt
6174: where ppf.person_id between stperson and endperson
6175: and ppa.payroll_action_id = pactid
6176: -- and ppf.person_type_id = ppt.person_type_id --commented Bug6809753
6177: and (( l_business_group_id = ppt.business_group_id
6207: ** ...as an ex person on the effective date
6208: */
6209: and exists (select null
6210: from per_person_type_usages_f ppf1,
6211: per_person_types ppt1
6212: where ppf1.person_id = ppf.person_id
6213: and ppa.effective_date between ppf1.effective_start_date
6214: and ppf1.effective_end_date
6215: and ppf1.person_type_id = ppt1.person_type_id
6229: ** who are EX-EMP/EX-CWK but who are also APL
6230: */
6231: and not exists (select null
6232: from per_person_type_usages_f ppf2,
6233: per_person_types ppt2
6234: where ppf2.person_id = ppf.person_id
6235: and ppa.effective_date < ppf2.effective_end_date
6236: and ppf2.person_type_id = ppt2.person_type_id
6237: and ppt2.business_group_id = ppt.business_group_id