[Home] [Help]
111: p_generation_scope <> 'ALL_BUS_GRP')
112: and not exists
113: (select null
114: from per_person_type_usages_f ptuf,
115: per_person_types ppt
116: where ppt.system_person_type = 'IRC_REG_USER'
117: and ptuf.person_type_id = ppt.person_type_id
118: and ptuf.person_id = papf.person_id);
119: -- Fetch the security profiles that restrict by contacts, are
867: where pcr.contact_person_id = papf.person_id)
868: and not exists
869: (select null
870: from per_person_type_usages_f ptuf,
871: per_person_types ppt
872: where ppt.system_person_type = 'IRC_REG_USER'
873: and ptuf.person_type_id = ppt.person_type_id
874: and ptuf.person_id = papf.person_id)
875: and not exists
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
1445: and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
1446: between ptu.effective_start_date
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
1471: and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
1472: between ptu.effective_start_date
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
1494: and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
1495:
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
1521: and to_date('''||to_char(p_effective_date,
1522: 'dd/mm/yyyy')||''', ''dd/mm/yyyy'')
1690: /* Commented Bug6809753 - start
1691: cursor c_current_person is
1692: select ppf.person_id, greatest(min(ppf.effective_start_date), p_effective_date)
1693: from per_person_type_usages_f ppf,
1694: per_person_types ppt
1695: where ppf.person_id = p_person_id
1696: and ppf.person_type_id = ppt.person_type_id --taken out for Performance bug.
1697: -- Current person today
1698: and (( -- ppf.person_type_id = ppt.person_type_id and
1714: -- Bug6809753 - start
1715: cursor c_current_person is
1716: select ppf.person_id, greatest(min(ppf.effective_start_date), p_effective_date)
1717: from per_person_type_usages_f ppf,
1718: per_person_types ppt
1719: where ppf.person_id = p_person_id
1720: /*
1721: ** Current person today
1722: */
1742:
1743: cursor c_exclude_person is
1744: select ppf.person_id
1745: from per_person_type_usages_f ppf,
1746: per_person_types ppt
1747: where ppf.person_id = p_person_id
1748:
1749: and ( ppf.person_type_id = ppt.person_type_id
1750: and ppt.business_group_id =
1758: cursor c_former_person is
1759: select ppf.person_id, paf.assignment_type,
1760: least(max(paf.effective_end_date), p_effective_date) effective_date
1761: from per_person_type_usages_f ppf,
1762: per_person_types ppt,
1763: per_all_assignments_f paf
1764: where ppf.person_id = p_person_id
1765: and ppf.person_id = paf.person_id
1766: and paf.assignment_type in ('A','C','E')
1775: ** ...as an ex person on the effective date
1776: */
1777: and exists (select null
1778: from per_person_type_usages_f ppf1,
1779: per_person_types ppt1
1780: where ppf1.person_id = ppf.person_id
1781: and p_effective_date between ppf1.effective_start_date
1782: and ppf1.effective_end_date
1783: and ppf1.person_type_id = ppt1.person_type_id
1797: ** who are EX-EMP/EX-CWK but who are also APL
1798: */
1799: and not exists (select null
1800: from per_person_type_usages_f ppf2,
1801: per_person_types ppt2
1802: where ppf2.person_id = ppf.person_id
1803: and p_effective_date < ppf2.effective_end_date
1804: and ppf2.person_type_id = ppt2.person_type_id
1805: and ppt2.business_group_id = nvl(p_business_group_id,
1826:
1827: cursor c_is_current_apl is
1828: select 'Y'
1829: from per_person_type_usages_f ptu,
1830: per_person_types ppt
1831: where ptu.person_id = p_person_id
1832: and p_effective_date < ptu.effective_end_date
1833: and ptu.person_type_Id = ppt.person_type_id
1834: and ppt.system_person_type = 'APL';
1834: and ppt.system_person_type = 'APL';
1835: cursor c_is_former is
1836: select 'Y'
1837: from per_person_type_usages_f ptu,
1838: per_person_types ppt
1839: where ptu.person_id = p_person_id
1840: and p_effective_date between ptu.effective_start_date
1841: and ptu.effective_end_date
1842: and ptu.person_type_id = ppt.person_type_id
3172: endperson number) is
3173: select distinct ppf.person_id
3174: from per_person_type_usages_f ppf
3175: ,pay_payroll_actions ppa
3176: ,per_person_types ppt
3177: where ppf.person_id between stperson and endperson
3178: and ppa.payroll_action_id = pactid
3179: -- and ppf.person_type_id = ppt.person_type_id --commented Bug6809753
3180: and (( l_business_group_id = ppt.business_group_id
3210: ** ...as an ex person on the effective date
3211: */
3212: and exists (select null
3213: from per_person_type_usages_f ppf1,
3214: per_person_types ppt1
3215: where ppf1.person_id = ppf.person_id
3216: and ppa.effective_date between ppf1.effective_start_date
3217: and ppf1.effective_end_date
3218: and ppf1.person_type_id = ppt1.person_type_id
3232: ** who are EX-EMP/EX-CWK but who are also APL
3233: */
3234: and not exists (select null
3235: from per_person_type_usages_f ppf2,
3236: per_person_types ppt2
3237: where ppf2.person_id = ppf.person_id
3238: and ppa.effective_date < ppf2.effective_end_date
3239: and ppf2.person_type_id = ppt2.person_type_id
3240: and ppt2.business_group_id = ppt.business_group_id