[Home] [Help]
110: papf.business_group_id = p_business_group_id) or
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);
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,
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)
1143: Fnd_dsql.add_text(', per_organization_list ol ');
1144: end if;
1145:
1146: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1147: Fnd_dsql.add_text(', per_person_type_usages_f PERSON_TYPE ');
1148: end if;
1149:
1150: if p_sec_rec.view_all_positions_flag='N' then
1151: fnd_dsql.add_text(', per_position_list pl ');
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
1445: and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
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
1471: and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
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
1494: and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
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
1521: and to_date('''||to_char(p_effective_date,
1689: */
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
1713:
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
1741: /* ** To exclude Current person today */
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
1757:
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
1774: /*
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
1796: ** APL and as EX-EMP/EX-CWK therefore this cursor can see people
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
1825: paf.effective_start_date)));
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
1833: and ptu.person_type_Id = ppt.person_type_id
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
3170: cursor c_actions(pactid number,
3171: stperson number,
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
3209: /*
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
3231: ** APL and as EX-EMP/EX-CWK therefore this cursor can see people
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